I did the following in a 9.01 database:

CREATE TABLE t
( x integer,
y integer,
z integer);


CREATE TRIGGER t_buffer
BEFORE UPDATE
ON t
FOR EACH ROW
EXECUTE PROCEDURE t_buffer();


The trigger function is extremely simple and it only displays the values:

CREATE OR REPLACE FUNCTION t_buffer()
RETURNS trigger AS
$BODY$
begin
raise notice 'old.x = ',OLD.x,',old.y=',OLD.y;
raise notice 'old.x = ',NEW.x,',old.y=',NEW.y;
return NEW;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

I inserted the triple (1,1,1) into the table and then opened 2 sessions.
The 1st session executes like this:

BEGIN
Time: 0.108 ms
scott=# update t set x = x+1;
NOTICE: old.x = 1,old.y=1
NOTICE: new.x = 2,new.y=1
UPDATE 1
Time: 0.683 ms

Here I waited and opened another session.


scott=# commit;
COMMIT
Time: 16.187 ms

The problem occurs when I executed the 2nd transaction:

BEGIN
Time: 0.117 ms
scott=# update t set x = x+2 where x > 0;

The transaction was blocked until I didn't commit the 1st transaction.

NOTICE: old.x = 2,old.y=1
NOTICE: new.x = 4,new.y=1
UPDATE 1
Time: 7780.669 ms
scott=# commit;
COMMIT
Time: 28.557 ms



Where is the problem? The problem lies in the fact that the 2nd
transaction should have only seen the changes committed before it has
begun, ie, x=1. The 1st transaction has committed AFTER the 2nd
transaction has begun but the 2nd transaction still sees x=2. The only
logical explanation is that the 2nd transaction has restarted when it
discovered the changed block, but the trigger did not fire twice.
Anybody care to sched some light on this? If that looks familiar, here
is the motivation for such an exotic test:

http://tkyte.blogspot.com/2005/08/part-ii-seeing-restart.html


The result is exactly the same, but the trigger behavior isn't.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions

Search Discussions

  • Tom Lane at Oct 12, 2010 at 8:42 pm

    Mladen Gogala writes:
    Where is the problem? The problem lies in the fact that the 2nd
    transaction should have only seen the changes committed before it has
    begun, ie, x=1.
    You might want to go reread this:
    http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED
    If you don't like that behavior, you might want SERIALIZABLE mode
    instead.

    regards, tom lane
  • Mladen Gogala at Oct 12, 2010 at 8:59 pm

    Tom Lane wrote:
    Mladen Gogala <mladen.gogala@vmsinfo.com> writes:
    Where is the problem? The problem lies in the fact that the 2nd
    transaction should have only seen the changes committed before it has
    begun, ie, x=1.
    You might want to go reread this:
    http://www.postgresql.org/docs/9.0/static/transaction-iso.html#XACT-READ-COMMITTED
    If you don't like that behavior, you might want SERIALIZABLE mode
    instead.

    regards, tom lane
    No, it wasn't about liking or not liking, I was only trying to get to
    the bottom of this behavior. In particular, I translated the phrase "
    The search condition of the command (the WHERE clause) is re-evaluated "
    as the transaction restart and have fully expected the triggers to fire
    twice, which didn't happen. I am comparing Postgres to Oracle, to find
    out where should I expect different behavior and where should I expect
    exactly the same behavior. So far, I must say, the changes aren't too
    big. One of the biggest is the lack of the %ROWCOUNT attribute with
    cursors and a different set of exceptions. Also, there is no
    DBMS_OUTPUT, I have to use RAISE NOTICE, which feels strange but does
    the trick. As a DBA, I should be able to help developers when they do
    run into problems. And they will.

    --

    Mladen Gogala
    Sr. Oracle DBA
    1500 Broadway
    New York, NY 10036
    (212) 329-5251
    http://www.vmsinfo.com
    The Leader in Integrated Media Intelligence Solutions
  • Richard Broersma at Oct 12, 2010 at 9:07 pm

    On Tue, Oct 12, 2010 at 1:59 PM, Mladen Gogala wrote:

    Also, there is no DBMS_OUTPUT, I have to use RAISE
    NOTICE, which feels strange but does the trick.
    I don't know much about Oracle or DBMS_OUTPUT, but would the LISTEN
    and NOTIFY mechanism not work for this?

    http://www.postgresql.org/docs/9.0/interactive/sql-listen.html
    http://www.postgresql.org/docs/9.0/interactive/sql-notify.html

    --
    Regards,
    Richard Broersma Jr.

    Visit the Los Angeles PostgreSQL Users Group (LAPUG)
    http://pugs.postgresql.org/lapug

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedOct 12, '10 at 8:21p
activeOct 12, '10 at 9:07p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase