Multi-parent inherited table with mixed storage options cannot be restored

From: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
To: Pg Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Multi-parent inherited table with mixed storage options cannot be restored
Date: 2020-05-14 10:31:11
Message-ID: d6be600a-9590-24f6-e144-7f363aece0ab@iki.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

CREATE TABLE parent1 (
col text
);

CREATE TABLE parent2 (
col text
);

CREATE TABLE child () INHERITS (parent1, parent2);

ALTER TABLE ONLY parent1 ALTER COLUMN col SET STORAGE EXTERNAL;
ALTER TABLE ONLY parent2 ALTER COLUMN col SET STORAGE MAIN;

pg_dump dumps those commands in different order (dump attached), with
the SET STORAGE commands before creating the child table. But that's not
allowed:

psql:mixed-storage-inheritance.sql:53: ERROR: inherited column "col"
has a storage parameter conflict
DETAIL: EXTERNAL versus MAIN

That's not good.

What's the best way to fix it? Perhaps pg_dump should leave out the
INHERITS clause from the CREATE TABLE statement, and dump separate
"ALTER TABLE child INHERIT" commands to make it inherited? But then
'attislocal' is wrongly to false for the columns. Or perhaps relax the
check for mixed storage options, so that the CREATE TABLE won't throw
that error.

- Heikki

Attachment Content-Type Size
mixed-storage-inheritance.sql application/sql 1.5 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Pavel Stehule 2020-05-14 11:12:48 Re: BUG #16436: Functions with input parameter table type failing from application
Previous Message Konstantin Davydenka 2020-05-14 08:13:11 Re:BUG #16434: some data lost