Re: Bug in the information_schema.referential_constraints view - Mailing list pgsql-bugs

From Tom Lane
Subject Re: Bug in the information_schema.referential_constraints view
Date
Msg-id 16792.1066348717@sss.pgh.pa.us
Whole thread Raw
In response to Re: Bug in the information_schema.referential_constraints  (Peter Eisentraut <peter_e@gmx.net>)
List pgsql-bugs
Peter Eisentraut <peter_e@gmx.net> writes:
> Tom Lane writes:
>> No, because that would entail a genuine loss of capability: FK
>> constraints couldn't be built using indexes that were made by CREATE
>> UNIQUE INDEX rather than through the unique/pk constraint syntax.
>> In particular this would mean that non-btree indexes could not be used.

> But that means the deficiency is elsewhere, namely that you cannot build
> non-btree indexes for primary key or unique constraints.

So are you proposing we extend the constraint syntax instead?  I think
it's better to keep our nonstandard index support in a separate,
nonstandard statement (CREATE INDEX) rather than mash it together with
spec-mandated syntax.  That seems like a recipe for getting stuck when
the spec adds extensions.

> Isn't the whole unique index thing a dead end anyway?  How are we ever
> going to get deferrable unique constraints that way?

The way that was just discussed --- with a deferrable constraint, you
don't elog immediately when the index detects a collision, but make a
note to recheck that particular key value at the time the constraint
should be enforced.  I can't imagine that we'd want to do unique
constraints without any index support.  How would you avoid having to
check lots and lots of uninteresting rows?

            regards, tom lane

pgsql-bugs by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Bug in the information_schema.referential_constraints
Next
From: "Ivan E. Rivera Uria"
Date:
Subject: data forma error in pgsql 7.1