Thread: Strange.. solved

By comparing backups, I found

CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "person"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH
ROWEXECUTE 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:

> By comparing backups, I found
> 
> CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "person"  NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH
ROWEXECUTE 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,
compiledby 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).