On P, 2005-05-08 at 18:03 -0400, Christopher Browne wrote:
> Here is a patch to remote_worker.c which comes very near to dropping
> and restoring indices on tables during the COPY_SET event.
> 
> The notion: Build a query (it's pretty big, but that cuts way down on
> the C "string slinging") that calculates two things for each table:
> 
> 1.  A query to drop each PK constraint/other index
> 
> 2.  A query to restore each PK constraint/other index
> 
> Do the "drops" just before copying all the data, and then generate the
> indices from scratch afterwards, and this should improve performance
> of COPY_SET quite a bit.
> 
> It finds the queries, but I'm not yet sure why it doesn't work.
> 
> On "pgbench" samples, it gripes, upon recreating the primary key
> constraint, that there already is one.
> 
> I'm seeing in logs that the PK _was_ just dropped, via the statement:
>   "alter table public.accounts drop constraint accounts_pkey;
> 
> It is quite unclear why it then breaks :-(.

Are you sure that the drop and restore are in the same transaction ?

Or maybe it is just this ?:

hannu=# alter table a drop constraint a_pkey;
NOTICE:  constraint $1 on table b depends on index a_pkey
ERROR:  cannot drop constraint a_pkey on table a because other objects
depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

This is similar to why you cant use TRUNCATE and probably something that
requires some new features in backend. 

> This is an eminently tempting change to get in; it would be very
> helpful for those that have really big replication sets...

-- 
Hannu Krosing <hannu at skype.net>