Thread: a trigger question

a trigger question

From
"Zhou, Lixin"
Date:
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




Re: a trigger question

From
Stephan Szabo
Date:
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.



Re: a trigger question

From
Oliver Elphick
Date:
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.Elphick@lfix.co.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

Attachment

Re: a trigger question

From
Jan Wieck
Date:
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@Yahoo.com #



Re: a trigger question

From
Manfred Koizar
Date:
On Tue, 4 Jun 2002 13:21:36 -0700 , "Zhou, Lixin" <LZhou@illumina.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

Re: a trigger question

From
Stephan Szabo
Date:
On Wed, 5 Jun 2002, Zhou, Lixin wrote:

> Thank all who answered and helped!
>
> Here is what I learnt so far:
>
> 1) Fire the trigger AFTER INSERT.  BEFORE INSERT won't work.
> 2) Deferrable does not seem to be important.
>
> I've tested above, it does work as expected for PK/FKs that are integers.
>
> (As Stephan pointed out, it should work.  And he's right, it works.)
>
> However, my specific problem is still not solved.  My original SQL
> actually has varchar(25) as PK/FK (in the original post, I used int as
> example because I did not realize that would make a difference).  I made
> a simple test and it does exactly reproduce my problem.  So, I post it
> below.
>
> The problem is actually, I guess, the SQL statement somehow does not
> insert the correct value to the second table.  For example, it may not
> quote the varchar string correctly.

In the below, you seem to be getting an extra set of quote marks (what
it's inserting is the string 'v' (where the single quotes are in the
string). I removed the foreign key constraint to see what was being
inserted.

It worked for me on 7.2 when I just did the (new.v) insert with
the constraint.  You might want to turn on query logging and such to
see what's going on.

> create function init_second_tbl() returns opaque as '
> declare
> begin
>     insert into second_tbl(v) values(quote_literal(new.v));
>     -- 1. this does not work
>     -- insert into second_tbl(v) values(new.v);
>     -- 2. this does not work
>     -- s := ''insert into second_tbl(v) values('' || quote_literal(new.v) ||
> '')'';
>     -- execute s;
>     -- 3. this does not work
>     -- s := ''insert into second_tbl(v) values('' || quote_literal(new.v) ||
> '')'';
>     -- perform s;
>     return null;
> end;
> ' language 'plpgsql';


Re: a trigger question

From
"Zhou, Lixin"
Date:
Hmmm...

The following does work actually:

insert into second_tbl(v) values(new.v);

The trick is as you pointed, new.v does have single quotes associated with
already.  I tested many combinations -- with and without DEFERRABLE and with
BEFORE INSERT and AFTER INSERT.  I probably ran the above statement with
BEFORE INSERT in the trigger so I wrongly stated the above did not work.

Never quote_literal any new.varchar!

Problem solved!  Thank you all for the help!  I really appreciate it.

Lixin Zhou


-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Wednesday, June 05, 2002 3:23 PM
To: Zhou, Lixin
Cc: 'pgsql-general@postgresql.org'
Subject: RE: [GENERAL] a trigger question



On Wed, 5 Jun 2002, Zhou, Lixin wrote:

> Thank all who answered and helped!
>
> Here is what I learnt so far:
>
> 1) Fire the trigger AFTER INSERT.  BEFORE INSERT won't work.
> 2) Deferrable does not seem to be important.
>
> I've tested above, it does work as expected for PK/FKs that are integers.
>
> (As Stephan pointed out, it should work.  And he's right, it works.)
>
> However, my specific problem is still not solved.  My original SQL
> actually has varchar(25) as PK/FK (in the original post, I used int as
> example because I did not realize that would make a difference).  I made
> a simple test and it does exactly reproduce my problem.  So, I post it
> below.
>
> The problem is actually, I guess, the SQL statement somehow does not
> insert the correct value to the second table.  For example, it may not
> quote the varchar string correctly.

In the below, you seem to be getting an extra set of quote marks (what
it's inserting is the string 'v' (where the single quotes are in the
string). I removed the foreign key constraint to see what was being
inserted.

It worked for me on 7.2 when I just did the (new.v) insert with
the constraint.  You might want to turn on query logging and such to
see what's going on.

> create function init_second_tbl() returns opaque as '
> declare
> begin
>     insert into second_tbl(v) values(quote_literal(new.v));
>     -- 1. this does not work
>     -- insert into second_tbl(v) values(new.v);
>     -- 2. this does not work
>     -- s := ''insert into second_tbl(v) values('' || quote_literal(new.v)
||
> '')'';
>     -- execute s;
>     -- 3. this does not work
>     -- s := ''insert into second_tbl(v) values('' || quote_literal(new.v)
||
> '')'';
>     -- perform s;
>     return null;
> end;
> ' language 'plpgsql';

a trigger question

From
pblunat
Date:
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 |
+-----------------------------------------------+

Re: a trigger question

From
"Gregory Wood"
Date:
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@ujf-grenoble.fr>
Cc: <pgsql-general@postgresql.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


Re: a trigger question

From
"Nigel J. Andrews"
Date:
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


Re: a trigger question

From
Jan Wieck
Date:
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@Yahoo.com #



Re: a trigger question

From
Alvaro Herrera
Date:
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)