Thread: 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.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
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 #
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
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';
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';
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@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
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
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 #
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)