public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Peter Eisentraut <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: table inheritance versus column compression and storage settings
Date: Fri, 16 Feb 2024 13:24:16 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAExHW5uH0q810ZZ7ZP2Bvz_aF33yPTUsZ5NNC=yvA1MsVVtDTw@mail.gmail.com>
<[email protected]>
<CAExHW5uAqaO7RcDgBiwm2pVO58evFY8AKzvVks-2dn3FKyWUkQ@mail.gmail.com>
<CAExHW5sVAMNBNeD8-nih8HY8n4ySJWe9Uf+QNRTMOD7u-De-CQ@mail.gmail.com>
<CAExHW5v+Au_kadPJd7ifmKLzFu+fbtkDp5duuzuS6bZdLJsD1w@mail.gmail.com>
<[email protected]>
<CAExHW5tgaiJynSWA25ufWwnXex8s9475G23+7DNnhifP9NPNvw@mail.gmail.com>
<[email protected]>
<[email protected]>
I wrote:
> I find it surprising that the committed patch does not touch
> pg_dump. Is it really true that pg_dump dumps situations with
> differing compression/storage settings accurately already?
It's worse than I thought. Run "make installcheck" with
today's HEAD, then:
$ pg_dump -Fc regression >r.dump
$ createdb r2
$ pg_restore -d r2 r.dump
pg_restore: error: could not execute query: ERROR: column "a" inherits conflicting storage methods
HINT: To resolve the conflict, specify a storage method explicitly.
Command was: CREATE TABLE public.stchild4 (
a text
)
INHERITS (public.stparent1, public.stparent2);
ALTER TABLE ONLY public.stchild4 ALTER COLUMN a SET STORAGE MAIN;
pg_restore: error: could not execute query: ERROR: relation "public.stchild4" does not exist
Command was: ALTER TABLE public.stchild4 OWNER TO postgres;
pg_restore: error: could not execute query: ERROR: relation "public.stchild4" does not exist
Command was: COPY public.stchild4 (a) FROM stdin;
pg_restore: warning: errors ignored on restore: 3
What I'd intended to compare was the results of the query added to the
regression tests:
regression=# SELECT attrelid::regclass, attname, attstorage FROM pg_attribute
WHERE (attrelid::regclass::name like 'stparent%'
OR attrelid::regclass::name like 'stchild%')
and attname = 'a'
ORDER BY 1, 2;
attrelid | attname | attstorage
-----------+---------+------------
stparent1 | a | p
stparent2 | a | x
stchild1 | a | p
stchild3 | a | m
stchild4 | a | m
stchild5 | a | x
stchild6 | a | m
(7 rows)
r2=# SELECT attrelid::regclass, attname, attstorage FROM pg_attribute
WHERE (attrelid::regclass::name like 'stparent%'
OR attrelid::regclass::name like 'stchild%')
and attname = 'a'
ORDER BY 1, 2;
attrelid | attname | attstorage
-----------+---------+------------
stparent1 | a | p
stchild1 | a | p
stchild3 | a | m
stparent2 | a | x
stchild5 | a | p
stchild6 | a | m
(6 rows)
So not only does stchild4 fail to restore altogether, but stchild5
ends with the wrong attstorage.
This patch definitely needs more work.
regards, tom lane
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: table inheritance versus column compression and storage settings
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox