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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: multi column foreign key for implicitly unique columns

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: multi column foreign key for implicitly unique columns

В Втр, 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
);
=20=
=20=
=20=
=20=
=20=
=20
CREATE TABLE class_fields (
class_name TEXT REFERENCES classes(name),
field_name TEXT,
PRIMARY KEY(class_name, field_name)
);
=20=
=20=
=20=
=20=
=20=
=20
CREATE TABLE objects (
name TEXT PRIMARY KEY,
class_name TEXT REFERENCES classes(name)
);
=20=
=20=
=20=
=20=
=20=
=20
CREATE TABLE object_versions (
object_name TEXT REFERENCES objects(name),
object_version DATE,
PRIMARY KEY(object_name, object_version)
);
=20=
=20=
=20=
=20=
=20=
=20
CREATE TABLE object_version_property_values (
object_name TEXT REFERENCES objects(name),
object_version DATE,
class_name TEXT,
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 really is 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 keys
for referenced table "objects"

I need the fk on the columns.

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: multi column foreign key for implicitly unique columns

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: multi column foreign key for implicitly unique columns

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 shall be
equal to the set of column names in the unique columns of a unique
constraint of the referenced table."

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: multi column foreign key for implicitly unique columns

Stephan Szabo 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 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 specifies a column list>, then the set of s contained
in that shall be equal to the
set of s contained in the 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: multi column foreign key for implicitly unique columns

В Втр, 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
>=20
> create table a (f1 int unique, f2 int unique);
>=20
> create table b (f1 int, f2 int,
> foreign key (f1,f2) references a(f1,f2));
>=20
> How would you decide which constraint to make the FK depend on?

Either way, the semantics are the same, right?

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: multi column foreign key for implicitly unique columns

On Tue, 17 Aug 2004, Markus Bertheau wrote:

> В Втр, 17.08.2004, в 16:46, Tom Lane п=D0=
¸ÑˆÐµÑ=82:
>
> > 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).

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: multi column foreign key for implicitly unique columns

В Втр, 17.08.2004, в 17:06, Stephan Szabo п=
ишет:
> On Tue, 17 Aug 2004, Markus Bertheau wrote:
>=20
> > В Втр, 17.08.2004, в 16:46, Tom Lane п=D0=
¸ÑˆÐµÑ=82:
> >
> > > 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?
>=20
> 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

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: multi column foreign key for implicitly unique columns

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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: multi column foreign key for implicitly unique columns

Stephan Szabo 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 whose referenced 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 effective execution of the
specified in the General Rules of this
Subclause.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: multi column foreign key for implicitly unique columns

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: multi column foreign key for implicitly unique columns

В Срд, 18.08.2004, в 04:45, Josh Berkus п=D0=
¸ÑˆÐµÑ=82:
> Markus,

Hi Josh,

> Hey, I see you figured out a workaround to writing a trigger for this. L=
et's=20
> see if we can make it work.
>=20
> ERROR: there is no unique constraint matching given keys
> for referenced table "objects"
>=20
> The reason for this is that CASCADE behavior gets quite odd when there is=
an=20
> FK reference to a non-unique column. We used to allow it, in 7.1, and I=
was=20
> responsible for a number of bug reports that led to us disallowing it. =
It=20
> should be theoretically implementable and relationally sound but will req=
uire=20
> a *lot* of troubleshooting to make work. So far, nobody's really intere=
sted=20
> enough.
>=20
> However, you have an easy way out:
>=20
> 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

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: multi column foreign key for implicitly unique columns

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 the referenced 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 #

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: multi column foreign key for implicitly unique columns

В Срд, 18.08.2004, в 15:33, Jan Wieck пи=
шет:

> Meaning that not enforcing the uniqueness of those columns isn't an=20
> 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.

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: multi column foreign key for implicitly unique columns

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 #

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: multi column foreign key for implicitly unique columns

В Срд, 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.ph p

for the schema and an explanation. I'm not denormalizing it as far as I
can tell.

Thanks

--=20
Markus Bertheau


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: multi column foreign key for implicitly unique columns

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.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: multi column foreign key for implicitly unique columns

Jan Wieck 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: multi column foreign key for implicitly unique columns

> 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.



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: multi column foreign key for implicitly unique columns

Tom Lane wrote:
> Jan Wieck 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: multi column foreign key for implicitly unique columns

Richard Huxton 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: multi column foreign key for implicitly unique columns

On 8/18/2004 12:18 PM, Tom Lane wrote:

> Richard Huxton 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
* 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.


Jan

--
#=========================================================== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: multi column foreign key for implicitly unique columns

On Wed, 2004-08-18 at 12:27, Jan Wieck wrote:
> On 8/18/2004 12:18 PM, Tom Lane wrote:
>
> > Richard Huxton 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)...



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: multi column foreign key for implicitly unique columns

Jan Wieck 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: multi column foreign key for implicitly unique columns

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 (
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.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: multi column foreign key for implicitly unique columns

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.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: multi column foreign key for implicitly unique columns

Tom Lane wrote:
> Jan Wieck 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: multi column foreign key for implicitly unique columns

On 8/18/2004 12:46 PM, Tom Lane wrote:

> Jan Wieck 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 #

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: multi column foreign key for implicitly unique columns

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: multi column foreign key for implicitly unique columns

On Wed, Aug 18, 2004 at 10:05:13 -0700,
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.

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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: multi column foreign key for implicitly unique columns

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: multi column foreign key for implicitly unique columns

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 the constraint.


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 #

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: multi column foreign key for implicitly unique columns

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: multi column foreign key for implicitly unique columns

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




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: multi column foreign key for implicitly unique columns

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 #

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: multi column foreign key for implicitly unique columns

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: multi column foreign key for implicitly unique columns

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's the original
command, in which case things like update cascade will still error, or
is it delete because the row is disappearing from the table created with
the 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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: multi column foreign key for implicitly unique columns

>> the uniqueness of referenced columns is NOT a requirement of
Relational Algebra. So why does SQL require it? Maybe I'll ask Joe
Celko after he finishes moving to Austin. <<

Things are still a mess and I am using the library internet access
because my laptop did not fare well in the move. Arrrgh!

The rule are that the referencing table has to go to a UNIQUE() set of
columns in the referenced table. The referencing and referenced table
can be the same table. The problems can come when you have DRI
actions. I cannot set up one of these off the top of my head, but I
recall that you could get situations where more than one result would
meet the conditions in the DRI actions if you allowed a reference to
more than one row in the referenced table.

FOREIGN KEY and AccessExclusiveLock

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 | t
pg_locks | AccessShareLock | t
pg_class | AccessShareLock | t
parent | AccessExclusiveLock | f
parent | AccessShareLock | t
pg_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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: FOREIGN KEY and AccessExclusiveLock

O Tom Lane Ýãñáøå óôéò Sep 28, 2004 :

> Achilleus Mantzios 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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: FOREIGN KEY and AccessExclusiveLock

Achilleus Mantzios writes:
> Whats the purpose of the AccessExclusiveLock on parent table?

We're adding a trigger to it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: FOREIGN KEY and AccessExclusiveLock

O Tom Lane Ýãñáøå óôéò Sep 28, 2004 :

> Achilleus Mantzios 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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: FOREIGN KEY and AccessExclusiveLock

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-lock ing.html
--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: FOREIGN KEY and AccessExclusiveLock

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 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-lock ing.html


--
-Achilleus


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org