Thread: multi column foreign key for implicitly unique columns
Hi, PostgreSQL doesn't allow the creation of a foreign key to a combination of fields that has got no dedicated unique key but is unique nonetheless because a subset of the combination of fields has a unique constraint. Example: CREATE TABLE p ( name TEXT PRIMARY KEY, "type" TEXT ); CREATE TABLE f ( name TEXT, "type" TEXT, FOREIGN KEY(name, "type") REFERENCES p(name, "type") ); ERROR: there is no unique constraint matching given keys for referenced table "p" Is this on purpose? I think the foreign key should be allowed. Creating an extra unique key only has a negative impact on performance, right? Thanks -- Markus Bertheau <twanger@bluetwanger.de>
On Tue, 2004-08-17 at 10:25, Markus Bertheau wrote: > Hi, > > PostgreSQL doesn't allow the creation of a foreign key to a combination > of fields that has got no dedicated unique key but is unique nonetheless > because a subset of the combination of fields has a unique constraint. > Example: > > CREATE TABLE p ( > name TEXT PRIMARY KEY, > "type" TEXT > ); > > CREATE TABLE f ( > name TEXT, > "type" TEXT, > FOREIGN KEY(name, "type") REFERENCES p(name, "type") > ); > ERROR: there is no unique constraint matching given keys for referenced table "p" What's the point of this? p.name is the primary key and is therefore unique in p, so your foreign key should simply reference p.name. Having f.type as a repetition of p.type violates normalisation principles, since name is completely derivable by a join of f to p on name. > Is this on purpose? I think the foreign key should be allowed. Creating > an extra unique key only has a negative impact on performance, right? If there is no unique key, how does the foreign key trigger find the referenced row except by doing a sequential scan? Bad news! And when one of the duplicate referenced rows changes, what should happen with ON UPDATE or ON DELETE? -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== "If ye abide in me, and my words abide in you, ye shall ask what ye will,and it shall be done unto you." John 15:7
В Втр, 17.08.2004, в 11:39, Oliver Elphick пишет:
> What's the point of this? p.name is the primary key and is therefore
> unique in p, so your foreign key should simply reference p.name. Having
> f.type as a repetition of p.type violates normalisation principles,
> since name is completely derivable by a join of f to p on name.
The real situation is a little more complicated:
CREATE TABLE classes ( name TEXT PRIMARY KEY
);
CREATE TABLE class_fields ( class_name TEXT REFERENCES classes(name), field_name TEXT, PRIMARY KEY(class_name,
field_name)
);
CREATE TABLE objects ( name TEXT PRIMARY KEY, class_name TEXT REFERENCES classes(name)
);
CREATE TABLE object_versions ( object_name TEXT REFERENCES objects(name), object_version DATE, PRIMARY
KEY(object_name,object_version)
);
CREATE TABLE object_version_property_values ( object_name TEXT REFERENCES objects(name), object_version DATE,
class_nameTEXT, field_name TEXT, value TEXT, FOREIGN KEY(object_name, object_version)REFERENCES
object_versions(object_name,object_version), -- this fk is needed to make sure that the the object in -- question
reallyis of the class that field_name is a field of FOREIGN KEY(object_name, class_name)REFERENCES objects(name,
class_name), FOREIGN KEY(class_name, field_name)REFERENCES class_fields(class_name, field_name)
);
ERROR: there is no unique constraint matching given keysfor referenced table "objects"
I need the fk on the columns.
--
Markus Bertheau <twanger@bluetwanger.de>
Markus Bertheau wrote: > Hi, > > PostgreSQL doesn't allow the creation of a foreign key to a combination > of fields that has got no dedicated unique key but is unique nonetheless > because a subset of the combination of fields has a unique constraint. [snip example] > Is this on purpose? I think the foreign key should be allowed. Creating > an extra unique key only has a negative impact on performance, right? As you say, the uniqueness is guaranteed so there's no good reason why it couldn't be made to work. It's probably more of an implementation issue. Unique constraints are implemented with an index, so I'm guessing the FK code assumes there is an index there to check against. -- Richard Huxton Archonet Ltd
On Tue, 17 Aug 2004, Richard Huxton wrote: > Markus Bertheau wrote: > > Hi, > > > > PostgreSQL doesn't allow the creation of a foreign key to a combination > > of fields that has got no dedicated unique key but is unique nonetheless > > because a subset of the combination of fields has a unique constraint. > [snip example] > > Is this on purpose? I think the foreign key should be allowed. Creating > > an extra unique key only has a negative impact on performance, right? > > As you say, the uniqueness is guaranteed so there's no good reason why > it couldn't be made to work. It's probably more of an implementation > issue. Unique constraints are implemented with an index, so I'm guessing No, actually, it's that the SQL92 (at least) spec says explicitly that there must be a unique constraint across all of the columns specified, not merely across a subset. "then the set of column names of that <reference column list> shall be equal to the set of column names in the unique columns of a unique constraint of the referenced table."
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> No, actually, it's that the SQL92 (at least) spec says explicitly that
> there must be a unique constraint across all of the columns specified, not
> merely across a subset.
> "then the set of column names of that <reference column list> shall be
> equal to the set of column names in the unique columns of a unique
> constraint of the referenced table."
SQL99 says the same. 11.8 syntax rule 3a:
a) If the <referenced table and columns> specifies a <reference column list>, then the set of
<columnname>s contained in that <reference column list> shall be equal to the set of <column
name>scontained in the <unique column list> of a unique constraint of the referenced table.
I think one reason for this is that otherwise it's not clear which
unique constraint the FK constraint depends on. Consider
create table a (f1 int unique, f2 int unique);
create table b (f1 int, f2 int, foreign key (f1,f2) references a(f1,f2));
How would you decide which constraint to make the FK depend on?
It'd be purely arbitrary.
regards, tom lane
В Втр, 17.08.2004, в 16:46, Tom Lane пишет: > I think one reason for this is that otherwise it's not clear which > unique constraint the FK constraint depends on. Consider > > create table a (f1 int unique, f2 int unique); > > create table b (f1 int, f2 int, > foreign key (f1,f2) references a(f1,f2)); > > How would you decide which constraint to make the FK depend on? Either way, the semantics are the same, right? -- Markus Bertheau <twanger@bluetwanger.de>
On Tue, 17 Aug 2004, Markus Bertheau wrote: > В Втр, 17.08.2004, в 16:46, Tom Lane пишет: > > > I think one reason for this is that otherwise it's not clear which > > unique constraint the FK constraint depends on. Consider > > > > create table a (f1 int unique, f2 int unique); > > > > create table b (f1 int, f2 int, > > foreign key (f1,f2) references a(f1,f2)); > > > > How would you decide which constraint to make the FK depend on? > > Either way, the semantics are the same, right? Unfortunately, not in the case of dropping the chosen constraint. Theoretically in that case, you'd probably have to extend the spec there as well to say that you check any dependent objects again to see if they would still be valid rather than dropping them (on cascade) or erroring (on restrict).
В Втр, 17.08.2004, в 17:06, Stephan Szabo пишет: > On Tue, 17 Aug 2004, Markus Bertheau wrote: > > > В Втр, 17.08.2004, в 16:46, Tom Lane пишет: > > > > > I think one reason for this is that otherwise it's not clear which > > > unique constraint the FK constraint depends on. Consider > > > > > > create table a (f1 int unique, f2 int unique); > > > > > > create table b (f1 int, f2 int, > > > foreign key (f1,f2) references a(f1,f2)); > > > > > > How would you decide which constraint to make the FK depend on? > > > > Either way, the semantics are the same, right? > > Unfortunately, not in the case of dropping the chosen constraint. Can't you choose at fk check time rather than fk creation time? > Theoretically in that case, you'd probably have to extend the spec there > as well to say that you check any dependent objects again to see if they > would still be valid rather than dropping them (on cascade) or erroring > (on restrict). That also makes sense and is more efficient as I see it. Thanks -- Markus Bertheau <twanger@bluetwanger.de>
On Tue, 17 Aug 2004, Markus Bertheau wrote: > В Втр, 17.08.2004, в 17:06, Stephan Szabo пишет: > > On Tue, 17 Aug 2004, Markus Bertheau wrote: > > > > > В Втр, 17.08.2004, в 16:46, Tom Lane пишет: > > > > > > > I think one reason for this is that otherwise it's not clear which > > > > unique constraint the FK constraint depends on. Consider > > > > > > > > create table a (f1 int unique, f2 int unique); > > > > > > > > create table b (f1 int, f2 int, > > > > foreign key (f1,f2) references a(f1,f2)); > > > > > > > > How would you decide which constraint to make the FK depend on? > > > > > > Either way, the semantics are the same, right? > > > > Unfortunately, not in the case of dropping the chosen constraint. > > Can't you choose at fk check time rather than fk creation time? > > > Theoretically in that case, you'd probably have to extend the spec there > > as well to say that you check any dependent objects again to see if they > > would still be valid rather than dropping them (on cascade) or erroring > > (on restrict). > > That also makes sense and is more efficient as I see it. I'm not seeing what you're seeing then. Right now, at creation, we can say object A depends on object B. When you go to drop object B, we can easily lookup up which objects (A) depend on it. When you go to drop object C, we can easily lookup up which objects () depend on it. If instead you put it off to drop time, when you drop object B, you need to figure out which objects might potentially depend on be (lets say (A,C)) and then determine which objects those do depend on and see if B is among those sets. If we do the in-between one, we could say that object A partially depends on B (because something else can fufill the requirement as well potentially). When you go to drop object B, we can see that A partially depends on B and then check only A's dependencies to see whether any other thing that might fufill the requirement still exists. In general, such a system would need to be able to make sure that it worked properly with multiple concurrent drops of objects that an object partially dependended on (even though the constraint case is probably safe.) It sounds like it'd be a pain at best.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> ... It sounds like it'd be a pain at best.
Also, that would directly violate the SQL spec's model of dependencies,
with possibly unpleasant consequences. The current implementation does
exactly what SQL says to do. I cite from SQL99 11.99 DROP CONSTRAINT:
3) If TC is a unique constraint and RC is a referential constraint whose referenced table is T and
whosereferenced columns are the unique columns of TC, then RC is said to be dependent on TC.
...
6) If RESTRICT is specified, then:
a) No table constraint shall be dependent on TC.
NOTE 195 - If CASCADE is specified, then any such dependent object will be dropped by the
effectiveexecution of the <alter table statement> specified in the General Rules of this
Subclause.
regards, tom lane
Markus, Hey, I see you figured out a workaround to writing a trigger for this. Let's see if we can make it work. ERROR: there is no unique constraint matching given keys for referenced table "objects" The reason for this is that CASCADE behavior gets quite odd when there is an FK reference to a non-unique column. We used to allow it, in 7.1, and I was responsible for a number of bug reports that led to us disallowing it. It should be theoretically implementable and relationally sound but will require a *lot* of troubleshooting to make work. So far, nobody's really interested enough. However, you have an easy way out: ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name); This will add the unique constraint that Postgres wants without changing your data at all. -- Josh Berkus Aglio Database Solutions San Francisco
В Срд, 18.08.2004, в 04:45, Josh Berkus пишет: > Markus, Hi Josh, > Hey, I see you figured out a workaround to writing a trigger for this. Let's > see if we can make it work. > > ERROR: there is no unique constraint matching given keys > for referenced table "objects" > > The reason for this is that CASCADE behavior gets quite odd when there is an > FK reference to a non-unique column. We used to allow it, in 7.1, and I was > responsible for a number of bug reports that led to us disallowing it. It > should be theoretically implementable and relationally sound but will require > a *lot* of troubleshooting to make work. So far, nobody's really interested > enough. > > However, you have an easy way out: > > ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name); I was worried about the performance hit because (name, class_name) will always be unique, yet they will be checked for uniqueness. Thanks -- Markus Bertheau <twanger@bluetwanger.de>
On 8/17/2004 10:45 PM, Josh Berkus wrote:
> Markus,
>
> Hey, I see you figured out a workaround to writing a trigger for this. Let's
> see if we can make it work.
>
> ERROR: there is no unique constraint matching given keys
> for referenced table "objects"
>
> The reason for this is that CASCADE behavior gets quite odd when there is an
> FK reference to a non-unique column. We used to allow it, in 7.1, and I was
> responsible for a number of bug reports that led to us disallowing it. It
> should be theoretically implementable and relationally sound but will require
> a *lot* of troubleshooting to make work. So far, nobody's really interested
> enough.
SQL92 4.10 Integrity constraints:
...
In the case that a table constraint is a referential constraint, the table is referred to as the referencing
table.The referenced columns of a referential constraint shall be the unique columns of some unique constraint of
thereferenced table.
...
Meaning that not enforcing the uniqueness of those columns isn't an
option. PostgreSQL is currently happy with a UNIQUE constraint that
covers those columns in any order, which is to the letter spec
compliant. "Really interested" will not do here.
Jan
>
> However, you have an easy way out:
>
> ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name);
>
> This will add the unique constraint that Postgres wants without changing your
> data at all.
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
В Срд, 18.08.2004, в 15:33, Jan Wieck пишет: > Meaning that not enforcing the uniqueness of those columns isn't an > option. The thing is that the columns _are_ unique, there's just no unique constraint on them. They are unique because there's a unique constraint on a subset of these columns. So no additional uniqueness enforcing needed. -- Markus Bertheau <twanger@bluetwanger.de>
On 8/18/2004 9:49 AM, Markus Bertheau wrote:
> В Срд, 18.08.2004, в 15:33, Jan Wieck пишет:
>
>> Meaning that not enforcing the uniqueness of those columns isn't an
>> option.
>
> The thing is that the columns _are_ unique, there's just no unique
> constraint on them. They are unique because there's a unique constraint
> on a subset of these columns. So no additional uniqueness enforcing
> needed.
>
Yes, you are right, a superset of columns of a unique constraint is
allways unique as well.
I assume it is performance why you are denormalizing your data?
However, Bruce, this should be on the TODO list:
* Allow foreign key to reference a superset of the columns covered by a unique constraint on the referenced
table.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
В Срд, 18.08.2004, в 16:06, Jan Wieck пишет: > I assume it is performance why you are denormalizing your data? Please have a look at http://archives.postgresql.org/pgsql-sql/2004-08/msg00157.php for the schema and an explanation. I'm not denormalizing it as far as I can tell. Thanks -- Markus Bertheau <twanger@bluetwanger.de>
On Wed, 18 Aug 2004, Jan Wieck wrote: > On 8/18/2004 9:49 AM, Markus Bertheau wrote: > > > В Срд, 18.08.2004, в 15:33, Jan Wieck пишет: > > > >> Meaning that not enforcing the uniqueness of those columns isn't an > >> option. > > > > The thing is that the columns _are_ unique, there's just no unique > > constraint on them. They are unique because there's a unique constraint > > on a subset of these columns. So no additional uniqueness enforcing > > needed. > > > > Yes, you are right, a superset of columns of a unique constraint is > allways unique as well. True, but the spec explicitly asks for the columns to be the members of a unique constraint, not that the columns be provably unique. See the other portion of the thread related to dropping constraints for other spec extensions doing this implies. I think the actual specific change to make it look for the subset should be trivial, but we'd really need to work out those dependency issues at the very least and I'm afraid there are more such little corners.
Jan Wieck <JanWieck@Yahoo.com> writes:
> However, Bruce, this should be on the TODO list:
> * Allow foreign key to reference a superset of the columns
> covered by a unique constraint on the referenced table.
See the followup discussion as to why this is a bad idea.
regards, tom lane
> However, Bruce, this should be on the TODO list: > > * Allow foreign key to reference a superset of the columns > covered by a unique constraint on the referenced table. It would probably be more beneficial to be able to create a unique constraint without requiring the fields be indexed. Gets rid of most of the overhead from double uniques.
Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: > >>However, Bruce, this should be on the TODO list: >> * Allow foreign key to reference a superset of the columns >> covered by a unique constraint on the referenced table. > > > See the followup discussion as to why this is a bad idea. Maybe an alternative todo? * Allow multiple unique constraints to share an index where one is a superset of the others' columns. That way you can mark it unique without having the overhead of multiple indexes. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> * Allow multiple unique constraints to share an index where one is a
> superset of the others' columns.
> That way you can mark it unique without having the overhead of multiple
> indexes.
That just moves the uncertain-dependency problem over one spot, ie, it's
the fabricated unique constraint that you can't pin down the
requirements for.
regards, tom lane
On 8/18/2004 12:18 PM, Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
>> * Allow multiple unique constraints to share an index where one is a
>> superset of the others' columns.
>
>> That way you can mark it unique without having the overhead of multiple
>> indexes.
>
> That just moves the uncertain-dependency problem over one spot, ie, it's
> the fabricated unique constraint that you can't pin down the
> requirements for.
If we allow for a unique index, that
* it is NOT maintained (no index tuples in there) * depends on another index that has a subset of columns *
ifthat subset-index is dropped, the index becomes maintained
then the uncertainty is gone. At the time someone drops the other
constraint or unique index, the data is unique with respect to the
superset of columns. So building the unique index data at that time will
succeed.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
On Wed, 2004-08-18 at 12:27, Jan Wieck wrote: > On 8/18/2004 12:18 PM, Tom Lane wrote: > > > Richard Huxton <dev@archonet.com> writes: > >> * Allow multiple unique constraints to share an index where one is a > >> superset of the others' columns. > > > >> That way you can mark it unique without having the overhead of multiple > >> indexes. > > > > That just moves the uncertain-dependency problem over one spot, ie, it's > > the fabricated unique constraint that you can't pin down the > > requirements for. > > If we allow for a unique index, that Silly question, but why does UNIQUE require an index at all? Yes, current implementation does, and agreed that checks will be mighty slow without an index (so are CASCADES to a non-indexed column)...
Jan Wieck <JanWieck@Yahoo.com> writes:
> If we allow for a unique index, that
> * it is NOT maintained (no index tuples in there)
> * depends on another index that has a subset of columns
> * if that subset-index is dropped, the index becomes maintained
> then the uncertainty is gone. At the time someone drops the other
> constraint or unique index, the data is unique with respect to the
> superset of columns. So building the unique index data at that time will
> succeed.
My goodness this is getting ugly. The notion of having to invoke an
index build as a side-effect of a DROP sounds like a recipe for trouble.
(Sample problem: what you're actually trying to do is drop the entire
table ... but because the subset-index happens to get visited first,
you go off and build the superset-index before you let the DROP finish.
User will be unhappy, if table is large. Or try this one: the superset-
index build actually fails because you've already dropped something it
depends on. This seems quite possible in cases involving cascading from
a drop of an individual column or datatype, for instance.)
I'd like to see more than one person needing it, before we go to that
kind of trouble to do something that's not in the spec.
regards, tom lane
Jan, > In the case that a table constraint is a referential constraint, > the table is referred to as the referencing table. The referenced > columns of a referential constraint shall be the unique columns of > some unique constraint of the referenced table. Missed that one. Interesting. AFAIK, the uniqueness of referenced columns is NOT a requirement of Relaitonal Algebra. So why does SQL require it? Maybe I'll ask Joe Celko after he finishes moving to Austin. I have my own issue that forced me to use triggers. Given: table users (namelogin PKstatusetc. ) table status (statusrelationlabeldefinitionPK status, relation ) the relationship is: users.status = status.status AND status.relation = 'users'; This is a mathematically definable constraint, but there is no way in standard SQL to create an FK for it. This is one of the places I point to whenever we have the "SQL is imperfectly relational" discussion. -- Josh Berkus Aglio Database Solutions San Francisco
On Wed, 18 Aug 2004, Josh Berkus wrote: > > In the case that a table constraint is a referential constraint, > > the table is referred to as the referencing table. The referenced > > columns of a referential constraint shall be the unique columns of > > some unique constraint of the referenced table. > > Missed that one. Interesting. AFAIK, the uniqueness of referenced columns is > NOT a requirement of Relaitonal Algebra. So why does SQL require it? > > Maybe I'll ask Joe Celko after he finishes moving to Austin. > > I have my own issue that forced me to use triggers. Given: > > table users ( > name > login PK > status > etc. ) > > table status ( > status > relation > label > definition > PK status, relation ) > > the relationship is: > users.status = status.status AND status.relation = 'users'; > > This is a mathematically definable constraint, but there is no way in standard > SQL to create an FK for it. This is one of the places I point to whenever > we have the "SQL is imperfectly relational" discussion. Well, I think SQL does give a way of specifying that constraint through assertions and check constraints with subselects. We just don't support either of those constructs.
Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: > >>If we allow for a unique index, that >> * it is NOT maintained (no index tuples in there) >> * depends on another index that has a subset of columns >> * if that subset-index is dropped, the index becomes maintained >>then the uncertainty is gone. At the time someone drops the other >>constraint or unique index, the data is unique with respect to the >>superset of columns. So building the unique index data at that time will >>succeed. > > > My goodness this is getting ugly. The notion of having to invoke an > index build as a side-effect of a DROP sounds like a recipe for trouble. I'm not sure it needs to be as clever as Jan suggested (but then I'm not as clever as Jan :-). I'd have thought a reference that forces you to use DROP...CASCADE would be enough. In those cases where you're dropping a whole table, presumably that's already implied. -- Richard Huxton Archonet Ltd
On 8/18/2004 12:46 PM, Tom Lane wrote: > Jan Wieck <JanWieck@Yahoo.com> writes: >> If we allow for a unique index, that >> * it is NOT maintained (no index tuples in there) >> * depends on another index that has a subset of columns >> * if that subset-index is dropped, the index becomes maintained >> then the uncertainty is gone. At the time someone drops the other >> constraint or unique index, the data is unique with respect to the >> superset of columns. So building the unique index data at that time will >> succeed. > > My goodness this is getting ugly. The notion of having to invoke an > index build as a side-effect of a DROP sounds like a recipe for trouble. The idea sure needs some refinement :-) > I'd like to see more than one person needing it, before we go to that > kind of trouble to do something that's not in the spec. Actually, the whole thing strikes me more as a sign for a denormalized database schema. If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring that the redundant copy of y in b.y stays in sync with a.y. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan, > > If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring > that the redundant copy of y in b.y stays in sync with a.y. So? What's denormalized about that? His other choice is to use a trigger. What he's trying to do is ensure that the class selected for the FK class_name, field_name relates to the same class_name in objects. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On Wed, Aug 18, 2004 at 10:05:13 -0700, Josh Berkus <josh@agliodbs.com> wrote: > > I have my own issue that forced me to use triggers. Given: > > table users ( > name > login PK > status > etc. ) > > table status ( > status > relation > label > definition > PK status, relation ) > > the relationship is: > users.status = status.status AND status.relation = 'users'; > > This is a mathematically definable constraint, but there is no way in standard > SQL to create an FK for it. This is one of the places I point to whenever > we have the "SQL is imperfectly relational" discussion. If users is supposed to reference status you can do this by adding a relation column to users, using a constraint to force relation to always be 'users' and then having (status, relation) being a foreign key.
Bruno, > If users is supposed to reference status you can do this by adding a > relation column to users, using a constraint to force relation to always be > 'users' and then having (status, relation) being a foreign key. But that requires the addition of an extra, indexed Text column to the table. And the "status" reference table is not the only place I need this construct; I have other relationships to reference tables which are similar. It would amount to adding probably a total of 25-40 columns to various tables in my database overall; maybe 100MB of completely redundant data. :-( I'll wait for ASSERTIONS, I think. -- --Josh Josh Berkus Aglio Database Solutions San Francisco
On 8/18/2004 2:55 PM, Josh Berkus wrote: > Jan, >> >> If a.x is unique, then (b.x, b.y) references (a.x, a.y) is only ensuring >> that the redundant copy of y in b.y stays in sync with a.y. > > So? What's denormalized about that? His other choice is to use a trigger. Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same value and he even wants to ensure this with theconstraint. Jan > > What he's trying to do is ensure that the class selected for the FK > class_name, field_name relates to the same class_name in objects. > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan, > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same > value and he even wants to ensure this with the constraint. And in the absence of that constraint, what ensures that b.y = a.y, exactly? -- Josh Berkus Aglio Database Solutions San Francisco
On Thu, 2004-08-19 at 17:21, Josh Berkus wrote: > Jan, > > > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same > > value and he even wants to ensure this with the constraint. > > And in the absence of that constraint, what ensures that b.y = a.y, exactly? In the absence of b.y, it would be impossible for it to be anything else. Isn't that the point? It seems to me that he was trying to use the database to show errors in his source data, but since his constraint would reject the data, he wouldn't be able to enter it; all he could do would be to see the error. So he might as well turn it round, normalise the data properly and use the database to tell the rest of the system what the data ought to be. Oliver Elphick
On 8/19/2004 12:52 PM, Oliver Elphick wrote: > On Thu, 2004-08-19 at 17:21, Josh Berkus wrote: >> Jan, >> >> > Because the value in b.y is redundant. b.x->a.x->a.y is exactly the same >> > value and he even wants to ensure this with the constraint. >> >> And in the absence of that constraint, what ensures that b.y = a.y, exactly? > > In the absence of b.y, it would be impossible for it to be anything > else. Isn't that the point? Precisely. I meant that the entire column is redundant and obsolete. Without the column, no need for any constraint. > It seems to me that he was trying to use the database to show errors in > his source data, but since his constraint would reject the data, he > wouldn't be able to enter it; all he could do would be to see the > error. So he might as well turn it round, normalise the data properly > and use the database to tell the rest of the system what the data ought > to be. I assumed he often queries b, and to avoid joining a all the time he duplicates values from a into b and then tries to ensure that they stay in sync with constraints. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Josh Berkus wrote: > I have my own issue that forced me to use triggers. Given: > > table users ( > name > login PK > status > etc. ) > > table status ( > status > relation > label > definition > PK status, relation ) > > the relationship is: > users.status = status.status AND status.relation = 'users'; > > This is a mathematically definable constraint, but there is no way in standard > SQL to create an FK for it. This is one of the places I point to whenever > we have the "SQL is imperfectly relational" discussion. It'd be nice to say something like: ALTER TABLE status ADD CONSTRAINT user_status_fk FOREIGN KEY (status) WHERE relation = 'users' REFERENCES users(status); And the flip-side so you can have: ALTER TABLE cheque_details ADD CONSTRAINT chq_trans_fk FOREIGN KEY (trans_id) REFERENCES transactions(trans_id) WHERE trans_type='CHQ'; Actually, since we can have a "unique index with where" this second form should be do-able shouldn't it? -- Richard Huxton Archonet Ltd
On Fri, 20 Aug 2004, Richard Huxton wrote: > It'd be nice to say something like: > > ALTER TABLE status ADD CONSTRAINT user_status_fk > FOREIGN KEY (status) WHERE relation = 'users' > REFERENCES users(status); > > And the flip-side so you can have: > > ALTER TABLE cheque_details ADD CONSTRAINT chq_trans_fk > FOREIGN KEY (trans_id) > REFERENCES transactions(trans_id) WHERE trans_type='CHQ'; > > Actually, since we can have a "unique index with where" this second form > should be do-able shouldn't it? Maybe, but there are some issues about how the feature would be defined. What is legal in those WHERE clauses? Can it refer to columns of the other table? Does the condition need to be immutable? If not, can it contain subselects? Can one use referentials actions on the constraint? If so, which rule is used for the second if a row is updated from having'CHQ' to something else? Is it update because that'sthe originalcommand, in which case things like update cascade will still error, oris it delete because the row is disappearingfrom the table created withthe where clause? SQL has assertions which would presumably be able to handle the general constraints above which should have questions like this defined (and doesn't have referential actions I believe). It might be better to implement those if one was going to do it.
Hi, all the below are for PostgreSQL 7.4.2. I noticed that during ALTER TABLE kid ADD CONSTRAINT "parcon" FOREIGN KEY (parid) on parent(id) pgsql tries to acquire an AccessExclusiveLock on *both* kid (which is pretty natural since it adheres with the docs, and it is an alter command) *and* parent. Whats the purpose of the AccessExclusiveLock on parent table? Is there a way this alter command will affect parent's data or schema? Lets see a simple example: dynacom=# CREATE TABLE parent(id int PRIMARY KEY); CREATE TABLE dynacom=# CREATE TABLE kid(parid int); CREATE TABLE dynacom=# Then in session 1: dynacom=# BEGIN ; BEGIN dynacom=# SELECT * from parent ;id ---- (0 rows) dynacom=# In Session 2: dynacom=# BEGIN ; BEGIN dynacom=# ALTER TABLE kid ADD CONSTRAINT "parcon" FOREIGN KEY (parid) references parent(id); *here Session 2 is deadlocked* In Session 1: dynacom=# SELECT c.relname,l.mode,l.granted from pg_locks l,pg_class c where l.relation=c.oid; relname | mode | granted --------------+---------------------+---------kid | AccessExclusiveLock | tpg_locks | AccessShareLock |tpg_class | AccessShareLock | tparent | AccessExclusiveLock | fparent | AccessShareLock | tpg_namespace| AccessShareLock | t (6 rows) dynacom=# Again in Session 1: dynacom=# end; COMMIT dynacom=# In Session 2: ALTER TABLE dynacom=# Now imagine that session 2 is "called" by session 1, with commiting after session 2 is done, we have clearly a deadlock situation. The question is why an AccessExclusiveLock must be created for the FK table? Actually it puzzled me alot, since for me Session 1 is a java program "executing" XML in various forms, one of them being plain UNIX (exec()) commands, which in turn sometimes are psql commands. It was hard to imagine that an innocent select on the parent table in the java program and an alter table on a child table as a pgsql UNIX command would cause a deadlock situation. The natural workaround was to immediately commit in the java program after select and before UNIX command (psql) is executed. Thanx. -- -Achilleus
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> Whats the purpose of the AccessExclusiveLock on parent table?
We're adding a trigger to it.
regards, tom lane
O Tom Lane έγραψε στις Sep 28, 2004 : > Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > > Whats the purpose of the AccessExclusiveLock on parent table? > > We're adding a trigger to it. From the docs: Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly. Now is the lock in question created explicitly with LOCK TABLE? Since the docs dont say a thing about triggers acquiring locks. > > regards, tom lane > -- -Achilleus
O Tom Lane έγραψε στις Sep 28, 2004 : > Achilleus Mantzios <achill@matrix.gatewaynet.com> writes: > > Whats the purpose of the AccessExclusiveLock on parent table? > > We're adding a trigger to it. From the docs: Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly. Now is the lock in question created explicitly with LOCK TABLE? Since the docs dont say a thing about triggers acquiring locks. > > regards, tom lane > -- -Achilleus
Hmm, (something went wrong with some mailer) Tom Lane wrote: > We're adding a trigger to it. From the docs: ============================================================================ ACCESS EXCLUSIVE Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transaction accessing the table in any way. Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly. Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE) statement. ============================================================================ Now, is the lock acquired for the CREATE TRIGGER an explicit LOCK TABLE? Because nothing is mentioned about triggers in http://www.postgresql.org/docs/7.4/interactive/explicit-locking.html -- -Achilleus
O Achilleus Mantzios έγραψε στις Sep 29, 2004 : > > Hmm, (something went wrong with some mailer) And again.... hope it gets right this time... > > Tom Lane wrote: > > We're adding a trigger to it. > From the docs: ============================================================================ ACCESS EXCLUSIVEConflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE,SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transactionaccessing the table in any way. Acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands.This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly. Tip: Only anACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE) statement. ============================================================================Now,is the lock acquired for the CREATE TRIGGERan explicitLOCK TABLE?Because nothing is mentioned about triggers in http://www.postgresql.org/docs/7.4/interactive/explicit-locking.html -- -Achilleus