strange behavior on 8.1

[Csaba Nagy <nagy@xxxxxxxxxxxxxx>]

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