Re: [GENERAL] constraint trigger

[Tom Lane]

"Komaravolu, Satya"  writes:
>   The SQL file is failing on the following error. I gave you that
> message as it was the first one for the list
>   of CONSTRAINT TRIGGER statements.
  
>  NOTICE:  converting trigger group into constraint "" FOREIGN
> KEY jn_fuel(transaction_id) REFERENCES jn_transaction_header(record_id)
> DETAIL:  Found referencing table's trigger.
> psql:cfn4.dbinstall.sql:2585: ERROR:  there is no unique constraint
> matching given keys for referenced table "jn_transaction_header"

Hmm, is it possible that the dump file creates a unique or pkey
constraint on jn_transaction_header(record_id) further down?

What is the dump file from, anyway (which PG version exactly)?
How old is the foreign key constraint --- it could have been
propagated through several PG versions, because we didn't start
trying to clean up these obsolete trigger declarations until recently.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] constraint trigger

[Tom Lane]

"Komaravolu, Satya"  writes:
> I'm running into the following error when i create a CONSTRAINT
> TRIGGER.

>  NOTICE:  ignoring incomplete trigger group for constraint ""
> FOREIGN KEY cd_card(tender) REFERENCES cd_tender(id)
> DETAIL:  Found referenced table's DELETE trigger.

That is not an "error".

> CREATE CONSTRAINT TRIGGER "" AFTER DELETE ON "cd_tender"  NOT
> DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
> "RI_FKey_noaction_del" ('', 'cd_card', 'cd_tender',
> 'UNSPECIFIED', 'tender', 'id');

This is one out of a group of three related triggers that ancient
Postgres versions used to use to implement foreign key constraints.
(Actually, modern versions still use related triggers, but they're
a bit better hidden now --- you shouldn't see them in pg_dump output,
for sure.)

The message is telling you that the system is waiting for the other two
matching trigger definitions so that it will have enough information to
create a regular FOREIGN KEY constraint.

If you are doing this as part of loading an old dump file, just ignore
the notice and keep going; everything should be fine.  If you are doing
this manually, maybe you had better explain why.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general