Thread: oid not "UNIQUE" for use as FOREIGN KEY?
Hi!
Imagine I have tables like those in PostgreSQL 7.1.3:
CREATE TABLE rabattgruppe (
gruppe_oid OID,
produktgruppe VARCHAR(256)
);
CREATE TABLE gruppe (
obergruppe_oid OID,
name VARCHAR(32)
);
Then I try to establish integrity check
ALTER TABLE gruppe ADD
FOREIGN KEY (obergruppe_oid) REFERENCES gruppe (oid);
But:
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit
trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced
table "gruppe" not found
I thought the problem was I was referring to the same table,
but also this doesn't work:
ALTER TABLE rabattgruppe ADD
FOREIGN KEY (gruppe_oid) REFERENCES gruppe (oid);
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit
trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced
table "gruppe" not found
Now, what is the problem? "oid" for sure should be an UNIQUE
field in each table, doesn't it? Is it a bug?
Thanks,
Ernesto
--
Ernesto Baschny <ernst@baschny.de>
http://www.baschny.de - PGP Key:
http://www.baschny.de/pgp.txt
Sao Paulo/Brasil - Stuttgart/Germany
Ernst@IRCnet - ICQ# 2955403
On Tue, 23 Oct 2001, Ernesto Baschny wrote: > Now, what is the problem? "oid" for sure should be an UNIQUE > field in each table, doesn't it? Is it a bug? To your last two questions, no and yes. Unless you place a unique index on oid, it is not actually guaranteed unique (due to potential wraparound), however the alter code also doesn't yet handle referencing to non-user columns.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Tue, 23 Oct 2001, Ernesto Baschny wrote:
>> Now, what is the problem? "oid" for sure should be an UNIQUE
>> field in each table, doesn't it? Is it a bug?
> To your last two questions, no and yes. Unless you place a unique index
> on oid, it is not actually guaranteed unique (due to potential
> wraparound), however the alter code also doesn't yet handle referencing to
> non-user columns.
As of yesterday, I think this is a bug, because you can now create a
unique constraint on the OID column ... but I see it still doesn't work:
regression=# CREATE TABLE gruppe (
regression(# obergruppe_oid OID,
regression(# name VARCHAR(32),
regression(# unique(oid));
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'gruppe_oid_key' for table 'gruppe'
CREATE
regression=# ALTER TABLE gruppe ADD
regression-# FOREIGN KEY (obergruppe_oid) REFERENCES gruppe (oid);
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ERROR: UNIQUE constraint matching given keys for referenced table "gruppe" not found
regression=#
I wonder why the ALTER code doesn't notice the index on oid?
regards, tom lane
On Tue, 23 Oct 2001, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > On Tue, 23 Oct 2001, Ernesto Baschny wrote: > >> Now, what is the problem? "oid" for sure should be an UNIQUE > >> field in each table, doesn't it? Is it a bug? > > > To your last two questions, no and yes. Unless you place a unique index > > on oid, it is not actually guaranteed unique (due to potential > > wraparound), however the alter code also doesn't yet handle referencing to > > non-user columns. > > As of yesterday, I think this is a bug, because you can now create a > unique constraint on the OID column ... but I see it still doesn't work: The yes was to it being a bug. I've always treated it as such since you could make the "constraint" by making the index manually, just a low priority one since such uses of oid should be discouraged anyway. :) > regression=# CREATE TABLE gruppe ( > regression(# obergruppe_oid OID, > regression(# name VARCHAR(32), > regression(# unique(oid)); > NOTICE: CREATE TABLE/UNIQUE will create implicit index 'gruppe_oid_key' for table 'gruppe' > CREATE > regression=# ALTER TABLE gruppe ADD > regression-# FOREIGN KEY (obergruppe_oid) REFERENCES gruppe (oid); > NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > ERROR: UNIQUE constraint matching given keys for referenced table "gruppe" not found > regression=# > > I wonder why the ALTER code doesn't notice the index on oid? I think it's the use of rel_attrs[<number>] to get the attribute name rather than the function you mentioned to Christopher (I think) because I didn't know of its existance at the time. It should be easy, I'll send a fix since it's now easier to run into.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I think it's the use of rel_attrs[<number>] to get the attribute
> name rather than the function you mentioned to Christopher (I
> think) because I didn't know of its existance at the time. It should be
> easy, I'll send a fix since it's now easier to run into.
I'm on it already ...
regards, tom lane
On 23 Oct 2001 at 9:18, Stephan Szabo wrote: > > (...) > > As of yesterday, I think this is a bug, because you can now create a > > unique constraint on the OID column ... but I see it still doesn't work: > The yes was to it being a bug. I've always treated it as such since you > could make the "constraint" by making the index manually, just a low > priority one since such uses of oid should be discouraged anyway. :) Thats interesting, as I thought that would be an "elegant" way of doing that. I've got the idea from Bruce Momjian's book, here: http://www.ca.postgresql.org/docs/aw_pgsql_book/node71.html Should I instead stick to separate INTEGER PRIMARY KEY _id fields instead of using the OID's for it? What use would one want to make of an OID then? -- Ernesto Baschny <ernst@baschny.de> http://www.baschny.de - PGP Key: http://www.baschny.de/pgp.txt Sao Paulo/Brasil - Stuttgart/Germany Ernst@IRCnet - ICQ# 2955403
On Tue, 23 Oct 2001, Ernesto Baschny wrote: > On 23 Oct 2001 at 9:18, Stephan Szabo wrote: > > > > (...) > > > As of yesterday, I think this is a bug, because you can > now create a > > > unique constraint on the OID column ... but I see it still > doesn't work: > > > The yes was to it being a bug. I've always treated it as > such since you > > could make the "constraint" by making the index manually, > just a low > > priority one since such uses of oid should be discouraged > anyway. :) > > Thats interesting, as I thought that would be an "elegant" > way of doing that. I've got the idea from Bruce Momjian's > book, here: Well, the smiley was meant to be indicative of the fact that it's just my opinion on the whole matter (I'll give some more detail below) In general, oids aren't quite as unique as the book makes them out to be since they are only effectively an int4 AFAIK. > http://www.ca.postgresql.org/docs/aw_pgsql_book/node71.html > > Should I instead stick to separate INTEGER PRIMARY KEY _id > fields instead of using the OID's for it? What use would one > want to make of an OID then? Well, it's going to work soon since Tom's on it, but in general, I think assuming an oid is actually unique is dangerous since in large systems it may wrap (although this will be less bad in 7.2) and at that point you either have to deal with: 1) You've made a unique index on oid to make it really unique. Now some inserts fail due to the unique constraint when you run into an oid that already exists in the table. You have to realize that this is a transitory problem and that if you try again enough times it'll work. Heaven help you if you are doing large insert ... select queries. 2) You don't make a unique index. Now you have two rows with the same oid value. The foreign key stuff won't like that, nor will subqueries that expect a single row, etc... With your own key, while it may still wrap around, it's much more within your control. This is of course just an opinion and I'm sure someone will jump in with a pro oid use one too. :) Of course, most of this is academic for most people who aren't likely to run into oid conflicts of this sort, but...