Thread: Re: a trigger question
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.
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 #
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 #
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
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