ON DELETE triggers don't work as documented

From: Mike Glover <mpg4(at)mdi(dot)cc>
To: pgsql-bugs(at)postgresql(dot)org
Subject: ON DELETE triggers don't work as documented
Date: 2002-12-10 05:33:23
Message-ID: 20021209213323.A21052@mdi.cc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports(at)postgresql(dot)org(dot)

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs(at)postgresql(dot)org(dot)

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches(at)postgresql(dot)org instead. Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Mike Glover
Your email address : mpg4(at)mdi(dot)cc

System Configuration
---------------------
Architecture (example: Intel Pentium) : Mixed ix86

Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.x

PostgreSQL version (example: PostgreSQL-7.2.1): PostgreSQL-7.2.1

Compiler used (example: gcc 2.95.2) : gcc 2.96

Please enter a FULL description of your problem:
------------------------------------------------
The docs state (section 23.9):

If a non-NULL value is returned then the operation proceeds with that row value. Note that returning a row value different from the original value of NEW alters the row that will be inserted or updated.

the above suggests that returning NEW for a delete should cause the delete to proceed. In fact, I've found it necessary to return a record with the row format of the table and all empty fields.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
db=# create table test (field1 int, field2 text);
CREATE
db=# create or replace function test_trigger () returns opaque as '
mdisys'# DECLARE
mdisys'# BEGIN
mdisys'# return NEW;
mdisys'# END
mdisys'# ' language plpgsql;
CREATE
db=# create trigger run_test_trigger before delete on test for each row execute procedure test_trigger();
CREATE
db=# insert into test values (1, 'foo');
INSERT 123700 1
db=# insert into test values (2, 'bar');
INSERT 123701 1
db=# delete from test where field1=1;
DELETE 0
db=# select * from test;
field1 | field2
--------+--------
1 | foo
2 | bar
(2 rows)

db=#

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

I believe this is a documentation bug.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Nathan Mueller 2002-12-10 06:00:32 Re: Bug #838: SSL problems in 7.3
Previous Message Bruce Momjian 2002-12-10 04:50:56 Re: Bug #838: SSL problems in 7.3