Thread: BUG #15238: Sequence owner not updated when owning table is foreign
BUG #15238: Sequence owner not updated when owning table is foreign
From
PG Bug reporting form
Date:
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
Attachment
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
Re: Peter Eisentraut 2018-06-15 <e3cf9eb8-add9-f523-b3f8-de657e091a14@2ndquadrant.com> > 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. Did that ever get applied? I can't find it in the git log. Christoph -- Senior Berater, Tel.: +49 2166 9901 187 credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer Datenschutzerklärung: https://www.credativ.de/datenschutz
On Tue, Sep 25, 2018 at 02:06:35PM +0200, Christoph Berg wrote: > Did that ever get applied? I can't find it in the git log. Not that I can see. I have added an entry for old bugs on the v11 list of open items. Peter, could you finish wrapping it or do you need some help? -- Michael
Attachment
On 26/09/2018 02:09, Michael Paquier wrote: > On Tue, Sep 25, 2018 at 02:06:35PM +0200, Christoph Berg wrote: >> Did that ever get applied? I can't find it in the git log. > > Not that I can see. I have added an entry for old bugs on the v11 list > of open items. Peter, could you finish wrapping it or do you need some > help? Committed and backpatched now. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Wed, Sep 26, 2018 at 08:53:44PM +0200, Peter Eisentraut wrote: > Committed and backpatched now. Thanks, Peter. -- Michael
Attachment
Michael Paquier <michael@paquier.xyz> writes:
> On Wed, Sep 26, 2018 at 08:53:44PM +0200, Peter Eisentraut wrote:
>> Committed and backpatched now.
> Thanks, Peter.
Should the entry on the v11 Open Items list be closed now?
regards, tom lane
On Wed, Sep 26, 2018 at 07:52:04PM -0400, Tom Lane wrote: > Should the entry on the v11 Open Items list be closed now? Done already (not by me, by Justin). -- Michael