TRIGGER: Can not cancel query by returning NULL

[GoogleCodeExporter]

What steps will reproduce the problem?

With PL/pgSQL, returning NULL by BEFORE TRIGGER cancels query. However, PLV8 
doesn't cancel.

CREATE TABLE test (
  id SERIAL NOT NULL,
  txt TEXT,
  deleted BOOLEAN DEFAULT FALSE NOT NULL
);

CREATE OR REPLACE FUNCTION test_delete_trigger() RETURNS trigger AS $$
  return null; // Do not have any effect with PLV8
  // return; return ''; return 0; return false; are the same
$$ LANGUAGE plv8;

CREATE TRIGGER test_del BEFORE DELETE ON test FOR EACH ROW EXECUTE PROCEDURE 
test_delete_trigger();

insert into test (txt) values ('a');
insert into test (txt) values ('a');
insert into test (txt) values ('a');

delete from test where id = 1;
delete from test where id = 2;
delete from test where id = 3;

What is the expected output? What do you see instead?

Canceled query, but rows are deleted.

What version of the product are you using? On what operating system?

 MomongaLinux7 x86_64
   - gcc/g++ 4.4.4
   - glibc 2.12.90

 PostgreSQL 9.2.1 (ソース配布版）
 V8 3.15.6.0 (src/version.cc) 
 PL/V8 1.3.0devel (plv8_config.h)

Please provide any additional information below.

Following PL/pgSQL function cancels query.

CREATE OR REPLACE FUNCTION test_delete_trigger() RETURNS trigger AS $$
  DECLARE
  BEGIN
  UPDATE test SET deleted = true WHERE id = OLD.id;
  return null; // Returning null cancel query with PL/pgSQL
  END;
$$ LANGUAGE plpgsql;

Original issue reported on code.google.com by yohg...@gmail.com on 27 Nov 2012 at 5:35

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I'm not familar with PL codes, but I take a look at the source.
It seems

plv8.cc
CallTrigger(PG_FUNCTION_ARGS, plv8_exec_env *xenv)

do not handle null return properly. While PL/pgSQL's 

plpgsql_exec_trigger(PLpgSQL_function *func,
                     TriggerData *trigdata)

returning NULL. (I suppose returning NULL is correct, not sure, though)

I guess returning NULL form the CallTrigger may solve the problem.

Original comment by yohg...@gmail.com on 27 Nov 2012 at 7:12

[GoogleCodeExporter]

Good point.  Also, I noticed INSERT trigger cannot change the value.  Fixed in 
afb7f62

Original comment by umi.tan...@gmail.com on 29 Nov 2012 at 8:15

• Changed state: Fixed