Re: Postgres unique index checking and atomic transactions

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;

> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo(at)megazone(dot)bigpanda(dot)com]
> Sent: Thursday, July 24, 2003 1:41 PM
> To: Greg Stark
> Cc: Dmitry Tkach; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Postgres unique index checking and atomic
> transactions
>
>
>
> On 24 Jul 2003, 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.
> >
> > 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... :( ).
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>