1

I have a table "tst" which has a "status" column using which we process records in that table. Once the records are processed we set the status to 'Y';

    #\d tst
                 Table "core.tst"
     Column |  Type   | Modifiers
    --------+---------+-----------
     id     | integer | not null
     status | text    |
    Indexes:
            "amn2_pkey" PRIMARY KEY, btree (id)

Issue: At the start of the long(approx 20 mins) transaction I have 3 records in tst table as shown below

    #select * from tst;
     id | status
    ----+--------
        1 | N
        2 | N
        3 | N
    (3 rows)

But during the transaction I have couple of more records being inserted into the tst table, but I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing.

Session 1:

    DO $$ 
    BEGIN 
        perform pg_sleep(20) ; 
        UPDATE amn2 SET des = 'Y' ; 
    END $$;

Session 2:

    INSERT INTO tst VALUES ( 4, 'N');

Post the Session 1, all the records were updated to status 'Y' including id 4.

Is this expected? Or some DB/table serializable set up missing?

3

I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing.

That's not the case in the default READ COMMITTED isolation. Changes from committed transactions become visible at the start of the next statement in a transaction. Each statement still has a snapshot, so you can't have rows appear within a statement.

You can use SERIALIZABLE isolation to get that behaviour, but it has a performance impact and can lead to table bloat on fast-changing tables.

See the documentation on transaction isolation to understand what is going on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.