Postgres unique index checking and atomic transactions

On 24 Jul 2003, Greg Stark wrote:

So I have to adjust a primary key by adding one to every existing record.
Obviously this isn't a routine operation, my data model isn't that messed up.
It's a one-time manual operation.

However when I tried to do the equivalent of:

update tab set pk = pk + 1

I got

ERROR: Cannot insert a duplicate key into unique index tab_pkey

Is that right? Obviously after completing the query there would be no
duplicate keys. Is this a case where I would need deferred constraints to
allow this? Even for immediate constraints shouldn't a single sql update be
able to go ahead as long as it leaves things in a consistent state?

I've run into this before. If your pks are in the range of say 1 to 1000,
then just add 50000 to them, then subtract 49999...

Greg Stark wrote:

So I have to adjust a primary key by adding one to every existing record.
Obviously this isn't a routine operation, my data model isn't that messed up.
It's a one-time manual operation.

However when I tried to do the equivalent of:

update tab set pk = pk + 1

I got

ERROR: Cannot insert a duplicate key into unique index tab_pkey

Is that right? Obviously after completing the query there would be no
duplicate keys. Is this a case where I would need deferred constraints to
allow this? Even for immediate constraints shouldn't a single sql update be
able to go ahead as long as it leaves things in a consistent state?

It's a bug. It's even a bug tested for by mySQL's crashme script under
the title "atomic updates". Joe Celko's workaround for "eary SQL
implementations" is:

BEGIN;
UPDATE tab SET pk = - (pk + 1);
UPDATE tab SET pk = - (pk);
END;

Hope that helps,

Mike Mascari
mascarm@mascari.com

Greg Stark wrote:

So I have to adjust a primary key by adding one to every existing record.
Obviously this isn't a routine operation, my data model isn't that messed up.
It's a one-time manual operation.

However when I tried to do the equivalent of:

update tab set pk = pk + 1

I got

ERROR: Cannot insert a duplicate key into unique index tab_pkey

Is that right? Obviously after completing the query there would be no
duplicate keys. Is this a case where I would need deferred constraints to
allow this? Even for immediate constraints shouldn't a single sql update be
able to go ahead as long as it leaves things in a consistent state?

I tend to agree with you, that that's how it should be... I don't know
what the standards have to say about it though.
You cannot have unique constraints deferred either - only FKs, because
the uniqueness is checked right when you attempt to insert the key into
the index, and that cannot wait till the end of transaction, because
then your current transaction would not be able to use that index (it
would be nice to be able to postpone the insertin till the end of the
statement though - for performance reasons - but that's not the way it
works) :-(

The good news though is that, if you drop (or disable) your pk index
before the update, and recreate (reindex) afterwards, your update
statement should actually perform better ...

Dima

Dmitry Tkach <dmitry@openratings.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.

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.

--
greg

Greg Stark wrote:

Dmitry Tkach <dmitry@openratings.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.

That sounds like a feature that would bring Postgres closer to Oracle in power and flexibility, (mind you, by an increment, not enough to do an advertisement campaign!)

Greg Stark wrote:

On 24 Jul 2003, Greg Stark wrote:

Dmitry Tkach <dmitry@openratings.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.

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.

We probably should do this, but there are some issues. If you violate a
constraint while it's disabled and then do not check the constraint again
the system may not do what you want in the future for that or related
constraints (like if you violate a primary key that is referenced by a
foreign key). Also, in a strict sense, if you disable a constraint and
later enable it again without checking, you no longer have that
same constraint.

Also, for foreign keys, this might involve rechecking the fk table at
enable time. Right now our implementation of that type of check is pretty
poor (I really need to fix that eventually... :( ).

Why not give unique constraints a hook that determines, each time the constraint is called, whether to check the constraint or not?

Something like....

CREATE TABLE (
...
myValue int UNIQUE WITH HOOK myValueHook()
...
);

myValueHook() would return TRUE or FALSE, based on whether, at that moment, you wanted the constraint checked.

We do something similar in our databases to solve constraints and replication problems. We do not use UNIQUE indexes, but rather write before triggers for checking the uniqueness of a field. We can turn this off if we are in a replication cycle. The function looks something like:

BEGIN
IF duringReplication() THEN
RETURN NEW;
END IF;
IF (SELECT COUNT(*) FROM table WHERE cond) <> 0 THEN
RAISE EXCEPTION ''Uniqueness constraint'';
END IF;
RETURN NEW;
END;