Re: Any work on better parallelization of pg_dump?

Jehan-Guillaume de Rorthais wrote:

> > Yeah.  I recall there being some stupid limitation in ALTER TABLE .. ADD
> > CONSTRAINT USING INDEX to create a primary key from a previously
> > existing unique index, which would be very good to fix (I don't recall
> > what it was, but it was something infuriatingly silly).
>
> Could you elaborate? I already had to implement some custom scripts to
> restore some tables using this method. The scripts were using psql and
> "xargs -P" to restore the indexes and the PK outside of pg_restore.

Ahh, nevermind.  What I remembered was that if you have an UNIQUE
constraint, you cannot update its index to be a primary key, and you
cannot remove the associated constraint without dropping the index; you
have to make a new unique index instead.  If you have a plain UNIQUE
index, it works fine.  In other words,

-- this fails: the unique index is already associated with a constraint
create table ioguix (a int unique);
alter table ioguix add primary key using index ioguix_a_key ;

-- this works
create unique index ioguix_2_idx on ioguix (a);
alter table ioguix add primary key using index ioguix_2_idx ;

> > I suggest you start coding that ASAP.
>
> I did start, but with no success so far as the code in pg_dump/pg_restore is
> quite obscure at a first look. The few attempt I did to catch the PK creation
> statement and split it in 2 distincts statements failed to be processed in
> parallel IIRC. I probably dropped the patch in the meantime though.

pg_dump is *really* obscure, and its parallel processing even more so.
That just says that we need more people to try and figure it out!

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services