Re: Postgres unique index checking and atomic transactions

Greg Stark wrote:

>Dmitry Tkach <dmitry(at)openratings(dot)com> writes:
>
>
>
>>The good news though is that, if you drop (or disable) your pk index
>>
>>
>
>That's what I did, except I had to cascade to the foreign keys and then
>recreate them too. And you can't really recreate a primary key constraint, you
>just get a unique index which I think is equivalent.
>
You could avoid dropping/recreating the constraints with something like:

update pg_class set relhasindex=false, reltriggers = 0 where relname =
'mytable';
update mytable set pk=pk+1;
update pg_class set relhasindex=true, reltriggers = (select count (*)
FROM pg_trigger where pg_class.oid = tgrelid) where relname = 'mytable';
reindex table mytable;

>
>And that's another wishlist item. It would be nice to be able to disable
>constraints without dropping them and without poking around in catalog tables
>manually.
>
>Ie, it would be nice to be able to do
>
> alter table foo disable constraint "$1"
>
>and then later do
>
> alter table foo enable constraint "$1"
>
>and have postgres optionally recheck the constraint or not. It would be a lot
>safer than potentially accidentally recreating the constraint incorrectly. And
>a lot safer than poking around in the catalog tables.
>
>
>
Sure... and it would also be nice for the table owner to be able to do
that without having to reconnect as postgres...
I have written a bunch of "C" functions (set_triggers(), set_indexes(),
set_constraints()) that do that - for example, in your situation, I
would do:

select set_triggers ('mytable', false), set_indexes('mytable', false);
update mytable set pk=pk+1;
select set_triggers ('mytable', true), set_indexes ('mytable', true);

... a little more convenient (and safer), but still, it would certainly
be much better to be able to do that with a dedicated sql command...

Dima.