Hi all,
I have observed a strange behavior on 8.1 of an insert statement into a
temporary table done from a delete trigger.
I've attached a test case.
Observe that the NOTICE saying the rows were inserted occurs all 5 times
for the 8.0.3 server and only for the first 2 times for the 8.1
installation, and all further runs of the flush_test function yield no
successful insert...
Might worth to note that the 8.0.3 installation has 2 patches, one to
disallow the locking of parent keys in foreign key triggers, and another
one which changes time-stamp rounding behavior to "truncate".
Any ideas why this happens ?
Cheers,
Csaba.
CREATE TABLE test(col TEXT);
CREATE OR REPLACE FUNCTION sp_test_delete()
RETURNS trigger AS '
DECLARE
BEGIN
INSERT INTO temp_test (col) VALUES (OLD.col);
RAISE NOTICE ''Inserting: col=%'', OLD.col;
RETURN NEW;
END; ' language 'plpgsql';
CREATE TRIGGER tr_test_delete AFTER DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE sp_test_delete();
CREATE OR REPLACE FUNCTION flush_test()
RETURNS VOID AS '
DECLARE
rec_debug RECORD;
BEGIN
BEGIN
CREATE TEMPORARY TABLE temp_test (col TEXT) ON COMMIT DELETE ROWS;
EXCEPTION
WHEN duplicate_table THEN
END;
-- delete processed
DELETE FROM test;
FOR rec_debug IN
SELECT * FROM temp_test
LOOP
RAISE NOTICE ''Row in temp_test: col=%'', rec_debug.col;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
INSERT INTO test VALUES ('1');
SELECT flush_test();
INSERT INTO test VALUES ('2');
SELECT flush_test();
INSERT INTO test VALUES ('3');
SELECT flush_test();
INSERT INTO test VALUES ('4');
SELECT flush_test();
INSERT INTO test VALUES ('5');
SELECT flush_test();
*** 8.1.0 behavior ***
cnagy=> INSERT INTO test VALUES ('1');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=1
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
flush_test
------------
(1 row)
cnagy=> INSERT INTO test VALUES ('2');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=2
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
flush_test
------------
(1 row)
cnagy=> INSERT INTO test VALUES ('3');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=3
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
flush_test
------------
(1 row)
cnagy=> INSERT INTO test VALUES ('4');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=4
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
flush_test
------------
(1 row)
cnagy=> INSERT INTO test VALUES ('5');
INSERT 0 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=5
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
flush_test
------------
(1 row)
*** 8.0.3 behavior ***
cnagy=> INSERT INTO test VALUES ('1');
INSERT 1216290363 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=1
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE: Row in temp_test: col=1
flush_test
------------
(1 row)
cnagy=> INSERT INTO test VALUES ('2');
INSERT 1216290370 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=2
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE: Row in temp_test: col=2
flush_test
------------
(1 row)
cnagy=> INSERT INTO test VALUES ('3');
INSERT 1216290372 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=3
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE: Row in temp_test: col=3
flush_test
------------
(1 row)
cnagy=> INSERT INTO test VALUES ('4');
INSERT 1216290374 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=4
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE: Row in temp_test: col=4
flush_test
------------
(1 row)
cnagy=> INSERT INTO test VALUES ('5');
INSERT 1216290376 1
cnagy=> SELECT flush_test();
NOTICE: Inserting: col=5
CONTEXT: SQL statement "DELETE FROM test"
PL/pgSQL function "flush_test" line 12 at SQL statement
NOTICE: Row in temp_test: col=5
flush_test
------------
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
