a trigger question

Give two tables A and B. B has a field that references A's primary key.

For example:

create table A(
i int not null,
s text,
primary key(i));

create table B(
i int not null,
s text,
primary key(i),
foreign key(i) references A(i));

I like to create a trigger on table A. When a new row is inserted into A
(ex: with i = 5), I like to have the trigger inserts a new row in table B
whose field "i" has the same value as that of the A's (ex: i = 5).

As I do this, the error message is something like: "referential integration
violation - key referenced in B not found in A". This makes sense to me
since at the time the trigger inserts in B, A's new row is not visible yet
-- not committed yet.

How can I solve this problem using trigger?

Thanks!

Lixin Zhou

On Tue, 4 Jun 2002, Zhou, Lixin wrote:

> Give two tables A and B. B has a field that references A's primary key.
>
> For example:
>
> create table A(
> i int not null,
> s text,
> primary key(i));
>
> create table B(
> i int not null,
> s text,
> primary key(i),
> foreign key(i) references A(i));
>
> I like to create a trigger on table A. When a new row is inserted into A
> (ex: with i = 5), I like to have the trigger inserts a new row in table B
> whose field "i" has the same value as that of the A's (ex: i = 5).
>
> As I do this, the error message is something like: "referential integration
> violation - key referenced in B not found in A". This makes sense to me
> since at the time the trigger inserts in B, A's new row is not visible yet
> -- not committed yet.

Actually, I'd think that should work since it should be post statement
that the constraint runs. Can you send the full info on the tables and
triggers you were using?

As a workaround, you could see if making the constraint deferrable and
initially deferred works.

On Tue, 2002-06-04 at 21:21, Zhou, Lixin wrote:

> I like to create a trigger on table A. When a new row is inserted into A
> (ex: with i = 5), I like to have the trigger inserts a new row in table B
> whose field "i" has the same value as that of the A's (ex: i = 5).
>
> As I do this, the error message is something like: "referential integration
> violation - key referenced in B not found in A". This makes sense to me
> since at the time the trigger inserts in B, A's new row is not visible yet
> -- not committed yet.
>
> How can I solve this problem using trigger?

Declare the foreign key constraints deferrable and defer them in the
session; they will be checked only at the end of the transaction.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C

"Let your conversation be without covetousness; and be
content with such things as ye have. For he hath said,
I will never leave thee, nor forsake thee."
Hebrews 13:5

Zhou, Lixin wrote:
> Give two tables A and B. B has a field that references A's primary key.
>
> For example:
>
> create table A(
> i int not null,
> s text,
> primary key(i));
>
> create table B(
> i int not null,
> s text,
> primary key(i),
> foreign key(i) references A(i));
>
> I like to create a trigger on table A. When a new row is inserted into A
> (ex: with i = 5), I like to have the trigger inserts a new row in table B
> whose field "i" has the same value as that of the A's (ex: i = 5).
>
> As I do this, the error message is something like: "referential integration
> violation - key referenced in B not found in A". This makes sense to me
> since at the time the trigger inserts in B, A's new row is not visible yet
> -- not committed yet.
>
> How can I solve this problem using trigger?

You either make the trigger fire AFTER the insert or you make
the constraint deferred.

Jan

--

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

On Tue, 4 Jun 2002 13:21:36 -0700 , "Zhou, Lixin" <LZhou(at)illumina(dot)com>
wrote:
>I like to create a trigger on table A. When a new row is inserted into A
>(ex: with i = 5), I like to have the trigger inserts a new row in table B
>whose field "i" has the same value as that of the A's (ex: i = 5).
>
>As I do this, the error message is something like: "referential integration
>violation - key referenced in B not found in A". This makes sense to me
>since at the time the trigger inserts in B, A's new row is not visible yet
>-- not committed yet.
Lixin,

your trigger should fire AFTER INSERT instead of BEFORE INSERT.

HTH.
Servus
Manfred

Hi,
i have a problem with "cache lookup failled" when an insert is made in a
table with a trigger.
Error message is : "ERROR : fmgr_info : function 16586 : cache lookup
failed"
Quest.: where this problem is ?
and where the documentation is on this subject ?.

I made :
GRANT ALL PRIVILEGES ON all table and trigger,
trigger is a very simple program for cut string (OK when stand alone).

I am so sorry but beginers in english + beg. in trigger proc. + project'
stress = problem.
Thanks
--
+-----------------------------------------------+
| Pierre Blunat - CRIP Santé |
| Université Joseph Fourier - Grenoble - France |
| Domaine de La Merci F 38706 - La Tronche |
| Tél. : 33 476 63 74 07 Fax : 33 476 63 74 09 |
| Mobile / Texto : www.sfr.fr - 33 603 08 81 40 |
+-----------------------------------------------+

Sounds like you did a CREATE FUNCTION, then a CREATE TRIGGER, then needed to
DROP and reCREATE your function. The trigger is still looking for the old
function, try DROP TRIGGER and CREATE TRIGGER again to make sure it points
to the current version.

Greg

----- Original Message -----
From: "pblunat" <pblunat(at)ujf-grenoble(dot)fr>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, June 06, 2002 1:22 PM
Subject: [GENERAL] a trigger question

> Hi,
> i have a problem with "cache lookup failled" when an insert is made in a
> table with a trigger.
> Error message is : "ERROR : fmgr_info : function 16586 : cache lookup
> failed"
> Quest.: where this problem is ?
> and where the documentation is on this subject ?.
>
> I made :
> GRANT ALL PRIVILEGES ON all table and trigger,
> trigger is a very simple program for cut string (OK when stand alone).
>
> I am so sorry but beginers in english + beg. in trigger proc. + project'
> stress = problem.
> Thanks
> --
> +-----------------------------------------------+
> | Pierre Blunat - CRIP Santé |
> | Université Joseph Fourier - Grenoble - France |
> | Domaine de La Merci F 38706 - La Tronche |
> | Tél. : 33 476 63 74 07 Fax : 33 476 63 74 09 |
> | Mobile / Texto : www.sfr.fr - 33 603 08 81 40 |
> +-----------------------------------------------+
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

pblunat wrote:
> Hi,
> i have a problem with "cache lookup failled" when an insert is made in a
> table with a trigger.
> Error message is : "ERROR : fmgr_info : function 16586 : cache lookup
> failed"
> Quest.: where this problem is ?

You have dropped and recreated a trigger function without
dropping and redefining the trigger itself. The trigger on
the table has a dangling reference to the functions old OID.

Whenever you do DROP FUNCTION for a trigger, be sure to do
the DROP/CREATE TRIGGER as well.

In v7.2 you could alternatively use CREATE OR REPLACE
FUNCTION to avoid this problem.

> and where the documentation is on this subject ?.

Well hidden :-)

> I made :
> GRANT ALL PRIVILEGES ON all table and trigger,
> trigger is a very simple program for cut string (OK when stand alone).
>
> I am so sorry but beginers in english + beg. in trigger proc. + project'
> stress = problem.

And a voice out of the chaos spoke to me and said "smile and
be happy, it could be worse". And I smiled. And I was happy.
And It went worse.

Jan

--

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

On Thu, 6 Jun 2002, pblunat wrote:

> Hi,
> i have a problem with "cache lookup failled" when an insert is made in a
> table with a trigger.
> Error message is : "ERROR : fmgr_info : function 16586 : cache lookup
> failed"
> Quest.: where this problem is ?
> and where the documentation is on this subject ?.

The problem would seem to be that your trigger is using a function that has
been dropped and then possibly recreated.

I've not seen this error myself but I think the solution is to drop the trigger
and recreate it once you are certain the function exists. That's right isn't it
folks? As to the documentation, I don't know a direct link but the main site
http://www.postresql.org/ can lead you through to several places for
documentation. There should be answers to this question in the list archive and
I expect in the FAQ as well.

>
> I made :
> GRANT ALL PRIVILEGES ON all table and trigger,
> trigger is a very simple program for cut string (OK when stand alone).

The GRANT is irrelevent I think. Perhaps you could post a summary of your
schema, the trigger a function(s) if you are still having problems. The version
of postgres you are using would be useful as well.

>
> I am so sorry but beginers in english + beg. in trigger proc. + project'
> stress = problem.

No problem, it's a lot better than my French,

--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

El Jun 6, Gregory Wood escribio:

> Sounds like you did a CREATE FUNCTION, then a CREATE TRIGGER, then needed to
> DROP and reCREATE your function. The trigger is still looking for the old
> function, try DROP TRIGGER and CREATE TRIGGER again to make sure it points
> to the current version.

Also remember that you can just CREATE OR REPLACE the function so that
you don't have to drop and recreate the trigger afterwards.

--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Pido que me den el Nobel por razones humanitarias" (Nicanor Parra)