a trigger question

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

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

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.

I tried a few things as shown below, I did not have any luck.

Thank you for any further help!

Lixin Zhou

================== A PostgreSQL Session ========================

Script started on Wed Jun 5 13:46:16 2002
$ cat test.sql
create database test_trigger;

\c test_trigger

create table first_tbl(
v varchar(20) not null default 'abc',
s text,
primary key(v));

create table second_tbl(
v varchar(20) not null default 'abc',
s text,
primary key(v),
foreign key(v) references first_tbl(v));

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';

create trigger t_init_second_tbl
after insert
on first_tbl
for each row
execute procedure init_second_tbl();
$ psql -p 5556 -f test.sql
CREATE DATABASE
You are now connected to database test_trigger.
psql:test.sql:8: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'first_tbl_pkey' for table 'first_tbl'
CREATE
psql:test.sql:14: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index 'second_tbl_pkey' for table 'second_tbl'
psql:test.sql:14: NOTICE: CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
CREATE
CREATE
$ psql -p 5556 test_trigger
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

test_trigger=> \dt
List of relations
Name | Type | Owner
------------+-------+-------
first_tbl | table | lzhou
second_tbl | table | lzhou
(2 rows)

test_trigger=> \d first_tbl
                     Table "first_tbl"
 Attribute |         Type          |        Modifier        
-----------+-----------------------+------------------------
 v         | character varying(20) | not null default 'abc'
 s         | text                  | 
Index: first_tbl_pkey

test_trigger=> insert into first_tbl(v,s) values('v', 's');
ERROR: <unnamed> referential integrity violation - key referenced from
second_tbl not found in first_tbl
test_trigger=> \q
$ exit
exit

Script done on Wed Jun 5 13:47:15 2002

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

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 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.

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)

||

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

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.

1) Fire the trigger BEFORE INSERT does work
2) If you make the constraint deferred
3) And let the trigger return NEW instead of NULL;

I've tested above, it does work as expected for PK/FKs that are integers.

It also works for all other types of keys.

create table first_tbl(
v varchar(20) not null default 'abc',
s text,
primary key (v)
);

create table second_tbl(
v varchar(20) not null default 'abc',
s text,
primary key (v),
foreign key (v) references first_tbl (v) initially deferred
);

create function init_second_tbl() returns opaque as '
declare
begin
insert into second_tbl (v) values (new.v);
-- 1. this is wrong!
-- insert into second_tbl (v) values (quote_literal(new.v));
return new;
end;
' language 'plpgsql';

create trigger t_init_second_tbl
before insert
on first_tbl
for each row
execute procedure init_second_tbl();

insert into first_tbl (v, s) values ('v', 's');

Works as expected with v7.2.1. So what did you do? Let me
guess, you specified the constraint DEFERRABLE and then
forgot to actually put it into deferred mode, right? Well,
specifying it INITIALLY DEFERRED does the trick.

Jan

--

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

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 #

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

Thanks Jan!

1) Fire the trigger AFTER INSERT. BEFORE INSERT won't work.
2) Deferrable does not seem to be important.

Jan 1) Fire the trigger BEFORE INSERT does work
Jan 2) If you make the constraint deferred
Jan 3) And let the trigger return NEW instead of NULL;

This is a really interesting trick!

I've tested above, it does work as expected for PK/FKs that are integers.

Jan It also works for all other types of keys.

Yes, it should as I pointed out in another email.

I had troubles because I used quot_literal on a new.varchar value within the
PLPGSQL function. This should cause SQL syntax error (eg: insert into
tbl(a_string) values (''this is a string to be inserted but it will fail to
insert.'') but I got the error message as "referential integrity error"
instead of SQL syntax error.

Thanks again, Jan! I learnt a lot.

Lixin Zhou

Zhou, Lixin wrote:

Thanks Jan!

1) Fire the trigger AFTER INSERT. BEFORE INSERT won't work.
2) Deferrable does not seem to be important.

Jan 1) Fire the trigger BEFORE INSERT does work
Jan 2) If you make the constraint deferred
Jan 3) And let the trigger return NEW instead of NULL;

This is a really interesting trick!

To return NEW? The documented behaviour of a trigger fired
BEFORE INSERT and returning NULL is, that the INSERT doesn't
take place. That pretty much guarantees that the key will not
be there, wouldn't it?

I've tested above, it does work as expected for PK/FKs that are integers.

Jan It also works for all other types of keys.

Yes, it should as I pointed out in another email.

I had troubles because I used quot_literal on a new.varchar value within the
PLPGSQL function. This should cause SQL syntax error (eg: insert into
tbl(a_string) values (''this is a string to be inserted but it will fail to
insert.'') but I got the error message as "referential integrity error"
instead of SQL syntax error.

It should not, because those statements don't get parsed that
way. PL/pgSQL is not a string substitution/reevaluation
system like some other scripting languages. Strings in
variables are handled as datum, no matter if there are
special characters in them or not.

The statement

INSERT INTO second_tbl (v) VALUES (new.v);

Get's internally modified by the PL/pgSQL parser into

INSERT INTO second_tbl (v) VALUES ( $1 );

This $1 notation is only available via the internal server
programming interface (SPI) and PL/pgSQL specifies the
datatype of that "parameter" explicitly in an array that has
to be passed to SPI_prepare().

The datatype (varchar(20) in our case) is known, because all
this happens on the first trigger invocation and the trigger
system passes not only the NEW row for first_tbl in, but a
row descriptor as well.

Surrounding new.v now with the function call quote_literal()
just add's the quoting to the string and inserts that result.
Which is of course different from the key originally
inserted, and if you started off with empty tables it's
nearly impossible that this quoted string exists as a key
(it's insertion should've failed because of the double qouted
key missing ... and so forth ... maybe we could start off
with a key consisting of single quotes only and work down
from there, but that's academic and not exactly what your
business modell requires).

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)

Jan,

Thank you for implementing PL/pgSQL itself and being a tutor of its internal
for the PostgreSQL community!

You guys did terrific work and made PostgreSQL a very reliable, dependable
and useful product!

Lixin Zhou