Обсуждение: 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

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

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

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