BUG #15238: Sequence owner not updated when owning table is foreign
The following bug has been logged on the website:
Bug reference: 15238
Logged by: Christoph Berg
Email address: christoph.berg@credativ.de
PostgreSQL version: 10.4
Operating system: Debian
Description:
If a foreign table has a sequence attached (e.g. if the foreign table has a
"serial" column), and the foreign table owner is updated, the sequence owner
is not updated, leading to errors on restore:
create extension postgres_fdw;
create server pg foreign data wrapper postgres_fdw;
create foreign table a (a serial) server pg;
alter table a owner to postgres; -- some owner that is not the current
user
\d
List of relations
Schema │ Name │ Type │ Owner
────────┼─────────┼───────────────┼──────────
public │ a │ foreign table │ postgres
public │ a_a_seq │ sequence │ cbe -- original owner
pg_dump -s then emits a dump that cannot be restored:
...
CREATE FOREIGN TABLE public.a (
a integer NOT NULL
)
SERVER pg;
ALTER FOREIGN TABLE public.a OWNER TO postgres;
CREATE SEQUENCE public.a_a_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.a_a_seq OWNER TO cbe;
ALTER SEQUENCE public.a_a_seq OWNED BY public.a.a
-- ERROR: 55000: sequence must have same owner as table it is linked to
On 6/12/18 09:21, PG Bug reporting form wrote:
If a foreign table has a sequence attached (e.g. if the foreign table has a
"serial" column), and the foreign table owner is updated, the sequence owner
is not updated, leading to errors on restore:
Yup, it only recurses to sequences for regular tables and materialized
views. I suggest to remove the relkind check altogether. See attached
patch.
Can materialized views even have serial columns? I suspect materialized
views entered this conditional because of the toast table check nearby.
Also, can partitioned tables have serial columns? Are there more omissions?
So fewer relkind enumerations are probably better.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
0001-Recurse-to-sequences-on-ownership-change-for-all-rel.patchtext/plain; charset=UTF-8; name=0001-Recurse-to-sequences-on-ownership-change-for-all-rel.patch; x-mac-creator=0; x-mac-type=0Download+6-11
On 2018-Jun-14, Peter Eisentraut wrote:
Also, can partitioned tables have serial columns? Are there more omissions?
You're right, this is wrong for partitioned tables too.
create table part (a serial) partition by range (a);
create table part2 partition of part for values from (1) to (1000);
create user foo;
alter table part owner to foo;
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018/06/15 12:29, Peter Eisentraut wrote:
On 6/12/18 09:21, PG Bug reporting form wrote:
If a foreign table has a sequence attached (e.g. if the foreign table has a
"serial" column), and the foreign table owner is updated, the sequence owner
is not updated, leading to errors on restore:Yup, it only recurses to sequences for regular tables and materialized
views. I suggest to remove the relkind check altogether. See attached
patch.Can materialized views even have serial columns? I suspect materialized
views entered this conditional because of the toast table check nearby.Also, can partitioned tables have serial columns? Are there more omissions?
So fewer relkind enumerations are probably better.
+1 to recursing without a relkind check.
Thanks,
Amit