On Mon, 18 Apr 2022 at 22:05, Simon Riggs <simon.riggs@enterprisedb.com> wrote:
>
> On Mon, 18 Apr 2022 at 21:48, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >
> > "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > > On Mon, Apr 18, 2022 at 1:00 PM Simon Riggs <simon.riggs@enterprisedb.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:
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;