Thread: strange behavior on 8.1

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.

Ok, I found a fix which works for me: don't use ON COMMIT DELETE ROWS on
the temporary tables, but explicitly delete the rows once processed.
However, I think it should work with ON COMMIT DELETE ROWS too, and it
works fine indeed in 8.0.3.

Cheers,
Csaba.

On Thu, 2005-11-17 at 16:22, Csaba Nagy wrote:
> 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.
>
>
> ______________________________________________________________________
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

Csaba Nagy <nagy@ecircle-ag.com> writes:
> I have observed a strange behavior on 8.1 of an insert statement into a
> temporary table done from a delete trigger.

In an assert-enabled build this dumps core, so I'd say you've found a
bug ...

            regards, tom lane

Csaba Nagy <nagy@ecircle-ag.com> writes:
> Ok, I found a fix which works for me: don't use ON COMMIT DELETE ROWS on
> the temporary tables, but explicitly delete the rows once processed.
> However, I think it should work with ON COMMIT DELETE ROWS too, and it
> works fine indeed in 8.0.3.

I found the bug, and indeed ON COMMIT DELETE ROWS is the only case where
there's a significant chance of observing a problem.  Thanks for the
report!

            regards, tom lane