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