Strange.. solved
By comparing backups, I found
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "person" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'person', 'subject', 'UNSPECIFIED', 'subjectid', 'id');
Don't know where that came from, but probably operator error.. There isn't
an easy way of scrubbing an unnamed trigger is there? (I dump/edit/reloaded)
Cheers,
Patrick
Actually, if you look in pg_trigger, <unnamed> is technically the
constraint name and it should have a system generated constraint name
which you probably can use drop trigger on. It looks like part of
a FK constraint so I'm not sure how you got just 1/2 of it since
dropping subject should have dropped it (unless you did a partial
dump and restore).
On Mon, 22 Jan 2001, Patrick Welche wrote:
Show quoted text
By comparing backups, I found
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "person" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'person', 'subject', 'UNSPECIFIED', 'subjectid', 'id');
Don't know where that came from, but probably operator error.. There isn't
an easy way of scrubbing an unnamed trigger is there? (I dump/edit/reloaded)Cheers,
Patrick
There seems to be a bug in the 'REFERENCES' statement. You can create
foreign key references to fields that do not exist, that then cause odd (ie.
hard to resolve) error messages.
The operator error below (that should not be possible) is in creating a
reference to a column that does not exist users(id).
My example:
test=# select version();
version
-----------------------------------------------------------------
PostgreSQL 7.0.3 on i386-unknown-freebsdelf4.2, compiled by cc
(1 row)
test=# create table users(userid int4);
CREATE
test=# create table newsletter(user_id int4 references users(id));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
test=# insert into newsletter values (4);
ERROR: constraint <unnamed>: table users does not have an attribute id
test=#
When we got this error message we spent an hour trying to figure out what
the heck the problem was! In the end we simply deleted the bad trigger by
oid and just recreated it using CREATE CONSTRAINT TRIGGER.
I have not yet checked whether table foreign key constraints, or the CREATE
CONSTRAINT TRIGGER functionality has the same bug.
Chris
On Thu, 25 Jan 2001, Christopher Kings-Lynne wrote:
There seems to be a bug in the 'REFERENCES' statement. You can create
foreign key references to fields that do not exist, that then cause odd (ie.
hard to resolve) error messages.The operator error below (that should not be possible) is in creating a
reference to a column that does not exist users(id).My example:
test=# select version();
version
-----------------------------------------------------------------
PostgreSQL 7.0.3 on i386-unknown-freebsdelf4.2, compiled by cc
(1 row)test=# create table users(userid int4);
CREATE
test=# create table newsletter(user_id int4 references users(id));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
test=# insert into newsletter values (4);
ERROR: constraint <unnamed>: table users does not have an attribute id
test=#When we got this error message we spent an hour trying to figure out what
the heck the problem was! In the end we simply deleted the bad trigger by
oid and just recreated it using CREATE CONSTRAINT TRIGGER.I have not yet checked whether table foreign key constraints, or the CREATE
CONSTRAINT TRIGGER functionality has the same bug.
They all did. In 7.1 you should be safe from invalid column names in the
actual constraint definitions but create constraint trigger doesn't check
(because it has no real way of knowing what its parameters are supposed to
mean).