Unable to alter partition table to set logged

Question

I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command.

Afterwards, I ran an alter table set logged on the parent table but the table still shows as UNLOGGED along with all its children.

Is this broken?

You can execute these commands to see the issue:

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;

Throughout the display of the table details the UNLOGGED status doesn't change when running the ALTER TABLE command against the parent table. But even when all the child tables have been SET LOGGED, I'm still not able to change this on the parent table. Tools like pg_partman will look to the parent table to create the child tables with the "correct" properties.

Answer 1

I was recently affected by this bug. I was able to work around by detaching the partitions, re-defining the parent table to enable logging, and then re-attaching the partitions. Continuing with the OP example:

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)

Enable LOGGED:

ALTER TABLE customer_ind SET LOGGED;
ALTER TABLE customer_jap SET LOGGED;
ALTER TABLE customers_def SET LOGGED;
ALTER TABLE customers SET LOGGED;

Notice that the parent table remains 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)

Manually, detach/attach partitions:

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;

Verify that change worked:

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)

Answer 2

This is obviously a bug.

Following the bug thread, I tend to concur with David Rowley's opinion:

It should not be allowed to create UNLOGGED partitioned tables. You can always create an UNLOGGED table and attach it as partition to a table.

So your solution would be to set all partitions to LOGGED.