Re: Dump/Restore of non-default PKs

On Tue, Apr 19, 2022 at 9:14 AM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
wrote:

> On Mon, 18 Apr 2022 at 22:05, Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>
> wrote:
> >
> > On Mon, 18 Apr 2022 at 21:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > >
> > > "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > > > On Mon, Apr 18, 2022 at 1:00 PM Simon Riggs <
> simon(dot)riggs(at)enterprisedb(dot)com>
> > > > wrote:
> > > >> I propose that we change pg_dump so that when it creates a PK it
> does
> > > >> so in 2 commands:
> > > >> 1. CREATE [UNIQUE] INDEX iname ...
> > > >> 2. ALTER TABLE .. ADD PRIMARY KEY USING INDEX iname;
> > >
> > > > Why not just get rid of the limitation that constraint definitions
> don't
> > > > support non-default methods?
> > >
> > > That approach would be doubling down on the assumption that we can
> always
> > > shoehorn more custom options into SQL-standard constraint clauses, and
> > > we'll never fall foul of shift/reduce problems or future spec
> additions.
> > > I think for example that USING INDEX TABLESPACE is a blot on humanity,
> > > and I'd be very glad to see pg_dump stop using it in favor of doing
> > > things as Simon suggests.
> >
> > Sigh, agreed. It's more work, but its cleaner in the longer term to
> > separate indexes from constraints.
> >
> > I'll look in more detail and come back here later.
> >
> > Thanks both.
>
> Anyway, the main question is how should the code be structured?
>
>
I don't have a good answer to that question but the patch presently
produces the dump below for a partitioned table with one partition.

After manually adjusting the order of operations you end up with:

psql:/vagrant/pg_dump_indexattach.v1.txt:67: ERROR: index "parent_pkey" is
not valid
LINE 2: ADD CONSTRAINT parent_pkey PRIMARY KEY USING INDEX paren...
^
Because:

https://www.postgresql.org/docs/current/sql-altertable.html
ADD table_constraint_using_index
...This form is not currently supported on partitioned tables.

David J.

===== pg_dump with manual re-ordering of create/alter index before alter
table

CREATE TABLE public.parent (
id integer NOT NULL,
class text NOT NULL
)
PARTITION BY LIST (class);

CREATE TABLE public.parent_a (
id integer NOT NULL,
class text NOT NULL
);

ALTER TABLE public.parent_a OWNER TO vagrant;

ALTER TABLE ONLY public.parent ATTACH PARTITION public.parent_a FOR VALUES
IN ('a');

CREATE UNIQUE INDEX parent_pkey ON ONLY public.parent USING btree (id,
class);

ALTER TABLE ONLY public.parent
ADD CONSTRAINT parent_pkey PRIMARY KEY USING INDEX parent_pkey;

CREATE UNIQUE INDEX parent_a_pkey ON public.parent_a USING btree (id,
class);

ALTER INDEX public.parent_pkey ATTACH PARTITION public.parent_a_pkey;

ALTER TABLE ONLY public.parent_a
ADD CONSTRAINT parent_a_pkey PRIMARY KEY USING INDEX parent_a_pkey;