Postgresql
無法更改分區表以設置已記錄
我有一個我創建的分區表
UNLOGGED
。我正在使用 pg_partman 來管理分區。然後我繼續使用 COPY 命令將數據載入到其中。之後,我在父表上執行了一個更改表集,但該表仍然
UNLOGGED
與其所有子表一起顯示。這是壞了嗎?
您可以執行以下命令來查看問題:
CREATE UNLOGGED TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text, cust_country text)PARTITION BY LIST(cust_country); CREATE UNLOGGED TABLE customer_ind PARTITION OF customers FOR VALUES IN ('ind'); CREATE UNLOGGED TABLE customer_jap PARTITION OF customers FOR VALUES IN ('jap'); CREATE UNLOGGED table customers_def PARTITION OF customers DEFAULT; INSERT INTO customers VALUES (2039,'Puja','Hyderabad','ind'); INSERT INTO customers VALUES (4499,'Tony','Arizona','USA'); \d+ customers checkpoint; alter table customers set logged ; \d+ customers;
在表詳細資訊的整個顯示過程中,當對父表
UNLOGGED
執行命令時,狀態不會改變。ALTER TABLE
但是,即使所有子表都已存在SET LOGGED
,我仍然無法在父表上更改它。像 pg_partman 這樣的工具會查看父表來創建具有“正確”屬性的子表。
我最近受到這個錯誤的影響。我能夠通過分離分區、重新定義父表以啟用日誌記錄,然後重新附加分區來解決問題。繼續 OP 範例:
postgres=> SELECT COUNT(1) FROM customers; count ------- 2 (1 row) postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%'; relpersistence | relname ----------------+--------------- u | customer_ind u | customer_jap u | customers u | customers_def (4 rows)
啟用
LOGGED
:ALTER TABLE customer_ind SET LOGGED; ALTER TABLE customer_jap SET LOGGED; ALTER TABLE customers_def SET LOGGED; ALTER TABLE customers SET LOGGED;
請注意,父表仍然存在
UNLOGGED
:postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%'; relpersistence | relname ----------------+--------------- p | customer_ind p | customer_jap u | customers p | customers_def (4 rows)
手動分離/附加分區:
ALTER TABLE customers DETACH PARTITION customer_ind; ALTER TABLE customers DETACH PARTITION customer_jap; ALTER TABLE customers DETACH PARTITION customers_def; DROP TABLE customers; CREATE TABLE customers(cust_id bigint NOT NULL,cust_name varchar(32) NOT NULL,cust_address text, cust_country text) PARTITION BY LIST(cust_country); ALTER TABLE customers ATTACH PARTITION customer_ind FOR VALUES IN ('ind'); ALTER TABLE customers ATTACH PARTITION customer_jap FOR VALUES IN ('jap'); ALTER TABLE customers ATTACH PARTITION customers_def DEFAULT;
驗證更改是否有效:
postgres=> SELECT COUNT(1) FROM customers; count ------- 2 (1 row) postgres=> SELECT relpersistence, relname FROM pg_class WHERE relname LIKE 'customer%'; relpersistence | relname ----------------+--------------- p | customer_ind p | customer_jap p | customers p | customers_def (4 rows)
這顯然是一個錯誤。
在錯誤執行緒之後,我傾向於同意 David Rowley 的觀點:
不應允許創建
UNLOGGED
分區表。您始終可以創建UNLOGGED
表並將其作為分區附加到表。因此,您的解決方案是將所有分區設置為
LOGGED
.