Learning PostgreSql: Fun with REPEATABLE READ

In this post we shall run some examples under REPEATABLE READ, and see how they behave differently. 

Setting up test data

On SQL Server, run the following:

CREATE TABLE Tickets(
   ID INT NOT NULL,
   Problem VARCHAR(100) NOT NULL,
   SpaceFiller CHAR(200) NOT NULL
);
INSERT INTO Tickets(
   ID ,
   Problem,
   SpaceFiller)SELECT Number*10, 'Problem '+CAST([Number] AS VARCHAR(10))+'0', 'Space Filler'FROM data.Numbers;
ALTER TABLE Tickets ADD PRIMARY KEY(ID);
UPDATE Tickets SET Problem = 'Add 16Gb Ram to Kenny''s workstation'WHERE ID = 90090;

On PostgreSql, we do not need a Numbers table - there is a built in function generate_series, which is very useful:

CREATE TABLE Tickets(
   ID INT NOT NULL,
   Problem VARCHAR NOT NULL
);
TRUNCATE TABLE Tickets;
INSERT INTO Tickets(
   ID ,
   Problem)SELECT generate_series*10, 'Problem ' || CAST(generate_series AS VARCHAR) FROM generate_series(1, 16000);
   ALTER TABLE Tickets ADD PRIMARY KEY(ID);
UPDATE Tickets SET Problem = 'Add 16Gb Ram to Kenny''s workstation'WHERE ID = 90090; 

Identical selects running under REPEATABLE READ on Sql Server may return different results 

The following scripts demonstrate that reads are not completely repeatable, despite the isolation level's name being REPEATABLE READ and as such claiming otherwise.

In one tab, run this:

BEGIN TRANSACTION;UPDATE Tickets SET Problem = 'Replace printer in NW corner'WHERE ID = 49000;

In another tab, run this:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;BEGIN TRANSACTION;SELECT COUNT(*) AS TotalProblems, SUM(CASE WHEN Problem LIKE '%Kenny%' THEN 1 END) AS [Kenny's Problems],SUM(CASE WHEN Problem LIKE '%printer%' THEN 1 END) AS [Printer Problems]
FROM Tickets WHERE ID BETWEEN 0 AND 100000;

This select is blocked by the uncommitted update in the first tab and as such it does not complete. Let us get back to the first tab and run the following:

UPDATE Tickets SET ID=101WHERE ID = 90090;
COMMIT;

This update physically moves Kenny's ticket from a page that has not been read by the select yet to a page which has already been read. Once the transaction in the first tab commits, the select in the second tab completes with the following results: 

TotalProblems Kenny's Problems Printer Problems------------- ---------------- ----------------10000         NULL             1

Clearly Kenny's ticket has not been read at all. If we rerun this select, in the same transaction, we shall get different results, as follows:

TotalProblems Kenny's Problems Printer Problems------------- ---------------- ----------------10001         1            1

Note that Kenny's problem is included in the second result set, and the total in TotalProblems column is now correct as well.

As we have seen,

• when we rerun queries in the same transaction, we may get different results 
• selects may return rows inserted after the select started - such as the only ticket for "Printer problem"
• selects may fail to return some rows that were committed before the transaction started - note that the ticket for "Kenny's problem" is completely missing from the first result set. This effect may also cause incorrect totals. Note that TotalProblems column is different in the first and second result sets.

If we rerun this example on PostgreSql, the first select is not blocked by the modification, and consequent selects return the same results as the first one.

REPEATABLE READ on Sql Server does not prevent lost updates

The following scenario demonstrates how an update can be lost. To begin the scenario, run the following script in one tab:

BEGIN TRANSACTION;
UPDATE TicketsSET Problem = 'Water cooler making funny noises'WHERE ID = 4010;

In another tab, run this:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;BEGIN TRANSACTION;
UPDATE TicketsSET Problem = 'Printer problem ' + CAST(ID AS VARCHAR(5))WHERE ID BETWEEN 4000 AND 4100; 

The update is waiting on the uncommitted change. As soon as we commit the transaction in the first tab, the update completes. Note that it overwrites the change from the first tab:

SELECT Problem FROM TicketsWHERE ID BETWEEN 4000 AND 4100;
Problem----------------------------------------------------------------------------------------------------(snip)Printer problem 4010(snip) 

Similarly, a modification running under REPEATABLE READ can update or delete rows that were inserted after the transaction started. 

To reproduce, let us begin archiving tickets in one tab: 

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;BEGIN TRANSACTION;
SELECT * INTO ArchivedTickersFROM TicketsWHERE ID BETWEEN 4000 AND 4100;

In another tab, let us add a new ticket: 

INSERT Tickets(ID, Problem, SpaceFiller)VALUES(4005, 'Water cooler making funny noises', 'Test value'); 

Let us get back to the first tab and complete the archiving:

DELETE FROM TicketsWHERE ID BETWEEN 4000 AND 4100;COMMIT;
SELECT COUNT(*) FROM TicketsWHERE ID BETWEEN 4000 AND 4100;
-----------0

Clearly the new ticket number 4005 was deleted along with the archived ones. This is a textbook example of a lost update.

REPEATABLE READ on PostgreSql prevents lost updates

Let us rerun the previous scenario - we shall observe a very different behavior. In one session, let us start archiving a range of tickets:

BEGIN ISOLATION LEVEL REPEATABLE READ;;
CREATE TABLE archived_ticketsAS 
SELECT *FROM TicketsWHERE ID BETWEEN 4000 AND 4100;

In another session, let us add a new ticket:

INSERT INTO Tickets(ID, Problem)VALUES(4005, 'Water cooler making funny noises'); 

In the first session, let us complete the archiving. The unarchived ticket number 4005 has not been deleted:

DELETE FROM TicketsWHERE ID BETWEEN 4000 AND 4100;
COMMIT;
SELECT Problem FROM TicketsWHERE ID BETWEEN 4000 AND 4100;
"Water cooler making funny noises"

Let us rerun the scenario that updates an existing ticket. In one session, run this:

BEGIN TRANSACTION;
UPDATE TicketsSET Problem = 'Add index on Shipments.Height'WHERE ID = 4010;

In another session run this:

BEGIN ISOLATION LEVEL REPEATABLE READ;;
UPDATE TicketsSET Problem = 'Printer problem ' || CAST(ID AS VARCHAR(5))WHERE ID BETWEEN 4000 AND 4100;

Commit the transaction in the first session, 

and the second transaction detects a lost update and blows up, as follows: "ERROR:  could not serialize access due to concurrent update". 

As we have seen, PostgreSql has detected and prevented both lost updates. As such, we do not need to troubleshoot two subtle bugs that are rather difficult to reproduce.

Conclusion

As we have seen REPEATABLE READ is implemented very differently on PostgreSql, so we need to be very careful when we port T-SQL that uses REPEATABLE READ. As we develop against PostgreSql, we can and should take advantage of its optimistic implementation of isolation levels, rather than trying to exactly replicate Sql Server's behavior.

Learning PostgreSql: READ COMMITTED and Data Integrity

As we have just discussed, READ COMMITTED isolation level behaves very much like Sql Server's READ_COMMITTED_SNAPSHOT. As such, we need to be very careful with data integrity - lots of code that just works on Sql Server under its default isolation level, READ COMMITTED, does not work on PostgreSql under its default isolation level, which is also READ COMMITTED, but behaves differently.

Note: whenever we use READ_COMMITTED_SNAPSHOT on Sql Server, we need to be just as careful with data integrity  - otherwise we can introduce lots of subtle bugs. Hugo Kornelis described these bugs in his blog series entitled "Snapshot isolation: a threat for integrity". Also I described the same effects in the book entitled "Defensive Database Programming". This is why we are going to discuss how to enforce data integrity when readers do not block writers.

I am well aware that there is a lot of advice to just enable READ_COMMITTED_SNAPSHOT and boost performance, without mentioning the side effects impacting data integrity. We need to be very careful with such advice: performance is important, of course, but I am usually working on systems where sacrificing data integrity to boost performance is not acceptable. 

Demonstrating the problem.

Suppose that we have the following business rule: high priority tickets cannot be assigned to developers who are on vacation. Also suppose that to enforce this business rule, we are using subqueries whenever we modify data in Developers and Tickets tables. It does not matter if we add subqueries directly to our DML commands or wrap them in triggers - the problem stays the same.

Let us set up the tables and some test data:

CREATE TABLE Developers(
  Name VARCHAR NOT NULL PRIMARY KEY,
  Status VARCHAR NOT NULL
  );INSERT INTO Developers(Name, Status)VALUES('Jeff', 'Active');  CREATE TABLE Tickets(
  Problem VARCHAR NOT NULL PRIMARY KEY,
  Priority VARCHAR NOT NULL,
  AssignedTo VARCHAR NULL,
  CONSTRAINT FK_devlopers FOREIGN KEY(AssignedTo)
  REFERENCES Developers(Name),
  CONSTRAINT CHK_high_priority_always_assigned
    CHECK((Priority='high' AND AssignedTo IS NOT NULL)
      OR Priority<>'high')
  );INSERT INTO Tickets(Problem, Priority, AssignedTo)VALUES('TPS report hangs','low','Jeff'); 

We are ready to reproduce the problem. In one session, let us begin a transaction, update Jeff's status to "Vacation", but not commit it yet:

BEGIN TRANSACTION;UPDATE Developers SET Status='Vacation'WHERE Name='Jeff'AND NOT EXISTS(SELECT * FROM Tickets AS t
WHERE t.AssignedTo = Developers.Name
   AND t.Priority='high'); 

In another session, let us begin a transaction, increase the ticket's priority to high, but not commit it yet:

BEGIN TRANSACTION;UPDATE Tickets SET Priority='high'WHERE Problem='TPS report hangs'AND (SELECT Status FROM Developers WHERE Name=Tickets.AssignedTo)<>'Vacation'; 

Apparently the update succeeds:

SELECT * FROM Tickets;

"TPS report hangs";"high";"Jeff" 

The reason why the update succeeded is simple: this session does not see the uncommitted data from the other session, and it is not blocked, because writers do not block readers. This session read Jeff's status, which is still active:

SELECT * FROM Developers ;

"Jeff";"Active" 

We can commit both transactions. Now we have data that violates our data integrity rules: a high priority ticket is assigned to a developer who is on vacation. Let us restore the original data:

UPDATE Developers SET Status='Active'WHERE Name='Jeff';
UPDATE Tickets SET Priority='low'WHERE Problem='TPS report hangs'; 

Let us try out a higher isolation level. Our attempts to use REPEATABLE READ and even SERIALIZABLE make no difference. However, after a short investigation, we realize that the following script works differently on PostgreSql and Sql Server:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

BEGIN TRANSACTION;

On PostgreSql, the SET command does not affect the isolation level of the following transaction. 
The following section explains why.

SET TRANSACTION works differently

Unlike in SQL Server, a standalone SET TRANSACTION command has no effect whatsoever. The following script shows that:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;select current_setting('transaction_isolation'); 

"read committed" 

The reason is simple: in PostgreSql, SET TRANSACTION command affects only the current transaction. The standalone SET TRANSACTION runs in its own transaction, so when it completes, its transaction completes too, restoring the default isolation level. The correct way to set isolation level is as follows:

BEGIN TRANSACTION;SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;select current_setting('transaction_isolation');     

"repeatable read" 

Also we can use an equivalent, but more concise, method:

BEGIN ISOLATION LEVEL REPEATABLE READ;select current_setting('transaction_isolation');     

"repeatable read"

Trying out higher isolation levels

Now that we know how to set isolation levels properly, let us rerun the same scenario under higher isolation levels.

Under REPEATABLE READ, we still get the same problem, and we can commit both transactions.

After restoring the original data, let us replay the scenario under SERIALIZABLE isolation level. As under lower isolation levels, both modifications complete - this is completely different from Sql Server, where one modification would be blocked. However, when we try to commit both transactions, only one will succeed, while another will fail with the following error message:

ERROR:  could not serialize access due to read/write dependencies among transactions

So, essentially PostreSql implementation is optimistic - it only detects conflicts at commit time. 

Mimicking Sql Server's behavior

We can make sure that subqueries that enforce data integrity are blocked by writers, which mimics Sql Server's implementation of isolation levels. The following scripts shows how to do that - note the FOR UPDATE clause in the subquery. In one session, run this:

--restore original dataUPDATE Developers SET Status='Active'WHERE Name='Jeff';
UPDATE Tickets SET Priority='low'WHERE Problem='TPS report hangs';
-- enforce data integrity Sql Server styleBEGIN TRANSACTION;UPDATE Developers SET Status='Vacation'WHERE Name='Jeff'AND NOT EXISTS(SELECT * FROM Tickets AS t WHERE t.AssignedTo = Developers.Name
   AND t.Priority='high' FOR UPDATE);  

In another session, run this:

BEGIN TRANSACTION;UPDATE Tickets SET Priority='high'WHERE Problem='TPS report hangs'AND (SELECT Status FROM Developers WHERE Name=Tickets.AssignedTo FOR UPDATE)<>'Vacation';  

This command hangs, exactly like it does on SQL Server. Let us return to the first session and commit. The update in the second session immediately completes, but the ticket's priority is not updated:

SELECT * FROM Developers;"Jeff""Vacation" SELECT * FROM Tickets;"TPS report hangs""low""Jeff"  

As we have seen, FOR UPDATE clause allows us to replicate SQL Server's pessimistic implementation of isolation levels on PostgreSql.

Conclusion

As we have seen, PostgreSql implementation of isolation levels is substantially different. As such, we need to be very careful whenever we port DML that uses subqueries to enforce data integrity - otherwise we may end up with dirty data, just like when we turn on READ_COMMITTED_SNAPSHOT on Sql Server. There is much more about the differences in the implementation of isolation levels - we shall continue next time.

Note that we could use constraints to enforce this business rule and save ourselves a lot of trouble. I've described the solution in my free eBook "Defensive Database Programming", in the chapter entitled "Advanced Use of Constraints".

Learning PostgreSql: different behavior with READ UNCOMMITTED/COMMITTED

Because of multi-valued concurrency control aka MVCC, there are many differences in queries' behavior with different isolation levels. Before running examples, let us set up test data.

Setting up test data

The following script sets up the data we shall be playing with:

DROP TABLE test;CREATE TABLE test(ID INT NOT NULL, CONSTRAINT PK_test PRIMARY KEY(ID),col2 INT NOT NULL, col3 FLOAT);INSERT INTO test(ID, col2, col3)VALUES(1,0,0),(8,1,1); 

There are no dirty reads.

In one session, let us insert an uncommitted row:

BEGIN TRANSACTION;
INSERT INTO test(ID, col2, col3)VALUES(3,1,1); 

In another session let us try to read uncommitted data:

BEGIN ISOLATION LEVEL READ UNCOMMITTED;SELECT * FROM test;1;0;0
8;1;1 

Clearly, we do not see the uncommitted row inserted by another session. This is exactly as documented:

"When you select the level Read Uncommitted you really get Read Committed, and phantom reads are not possible in the PostgreSQL implementation of Repeatable Read, so the actual isolation level might be stricter than what you select"

READ COMMITTED behaves like READ_COMMITTED_SNAPSHOT

At this point we still have an uncommitted row. Let up keep the inserting transaction open, commit the selecting one, and begin another selecting transaction:

BEGIN ISOLATION LEVEL READ COMMITTED;SELECT * FROM test;1;0;0
8;1;1  

This SELECT is working exactly as the previous one, exactly as documented. This behavior is different from ow SQL Server executes SELECT with READ COMMITTED, where the SELECT would be blocked. However, this is consistent with how SQL Server executes SELECTs under READ_COMMITTED_SNAPSHOT.

If we commit the insert and rerun the select without committing the selecting transaction, we shall see the new row:

1;0;0
8;1;1
3;1;1 

Again, this is consistent with how SQL Server executes SELECTs under READ_COMMITTED_SNAPSHOT.

Multi-statement functions and READ COMMITTED

When multi-statement functions run under READ COMMITTED isolation level, each statement can see the committed data as of the beginning of the statement, and not as of the beginning of function execution. This is consistent with how SQL Server executes SELECTs under READ_COMMITTED_SNAPSHOT.

The following function selects two snapshots of data ten seconds apart:

CREATE OR REPLACE FUNCTION two_snapshots()RETURNS TABLE(test_id int, snapshot_name varchar) AS $BODY$BEGIN
    CREATE TEMP TABLE before_sleep AS
    SELECT ID from test;
    PERFORM pg_sleep(10);
    RETURN QUERY SELECT ID, CAST('before sleep' AS VARCHAR) FROM before_sleep
        UNION ALL
                 SELECT ID, 'after sleep' FROM test;
    DROP TABLE before_sleep;END;$BODY$
LANGUAGE plpgsql; 

Let us open a window and cut and paste the following modification, but not run it yet:  

INSERT INTO test(ID, col2, col3)VALUES(16,1,1);

In another session, let us invoke the function:

SELECT * FROM two_snapshots(); 

We have ten seconds to run the insert - that should be more than enough. Once the function completes, it returns the following result, including the row inserted after the function has been invoked:

1;"before sleep"
8;"before sleep"
3;"before sleep"
1;"after sleep"
8;"after sleep"
3;"after sleep"
16;"after sleep" 

As we have seen, the data modified after the function has been invoked is still visible to commands in the function, as long as the data has been committed before the command started.

There is much more to discuss about isolation levels in PostgreSql - we shall continue later.

Learning PostgreSql: XACT_ABORT is Always ON

PostgreSql has simple and consistent error handling, which can be roughly explained in Sql Server terms as follows: XACT_ABORT is Always ON. In other words, error handling in PostgreSql has substantially less features, but it does have all the features which we actually use in the project being migrated.

Simplicity of error handling in PostgreSql is very good news - we need to learn much less before we are productive, and there are less chances to make a mistake. Let us consider some examples.

On errors, transactions roll back.

This behavior is similar to what Sql Server does when XACT_ABORT is set ON.

Let us add a test table, and add one row of test data:

CREATE TABLE test(ID INT NOT NULL, col2 INT NOT NULL, col3 FLOAT);INSERT INTO test(ID, col2, col3)VALUES(1,0,0);

Let us run a transaction that is supposed to fail:

BEGIN TRANSACTION;INSERT INTO test(ID, col2, col3)VALUES(2,1,1);UPDATE test SET col3=ID/col2;COMMIT; 

ERROR: division by zero
SQL state: 22012 

After the transaction fails, there is only one row visible from the same session:

SELECT * FROM test;

1;0;0 

Also the transaction is rolled back, so there is nothing to commit:

COMMIT;WARNING:  there is no transaction in progress   

Function bodies are always run in transaction context.

We have already discussed that in the previous post. As such, any error occurring inside a function causes the function to abort, and the whole transaction to roll back. The following example shows it:

DROP TABLE test;

CREATE TABLE test(ID INT NOT NULL, CONSTRAINT PK_test PRIMARY KEY(ID),col2 INT NOT NULL, col3 FLOAT);INSERT INTO test(ID, col2, col3)VALUES(1,0,0);
CREATE OR REPLACE FUNCTION Add_Test_Rows()RETURNS VARCHAR AS$BODY$
BEGIN-- this insert succeedsINSERT INTO test(ID, col2, col3)VALUES(2,0,0);-- the first row violates the PKINSERT INTO test(ID, col2, col3)VALUES(1,0,0),(3,0,0);RETURN 'Success';END;$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT Add_Two_Test_Rows();
ERROR:  duplicate key value violates unique constraint "pk_test" DETAIL:  Key (id)=(1) already exists.

SELECT * FROM test;1;0;0

COMMIT;WARNING:  there is no transaction in progress 

Note that the error message clearly indicates the offending row - this is very convenient.

If we catch exceptions, the transaction has already rolled back.

When we've caught an exception, we can still get some diagnostics information, but the all the changes are gone. See for yourself:

CREATE OR REPLACE FUNCTION Add_Test_Rows()RETURNS VARCHAR AS$BODY$
DECLARE
  err_msg VARCHAR;
  err_detail VARCHAR;
  cnt INT;BEGIN-- this row insertsINSERT INTO test(ID, col2, col3)VALUES(2,0,0);-- this row violates the PKINSERT INTO test(ID, col2, col3)VALUES(1,0,0);RETURN 'Success';EXCEPTION
    WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
       err_detail = PG_EXCEPTION_DETAIL;
     cnt := (SELECT COUNT(*) FROM test);
     RETURN err_detail || ' Visible rows: ' || cnt;END;$BODY$
LANGUAGE plpgsql VOLATILE;
SELECT Add_Test_Rows();
"Key (id)=(1) already exists. Visible rows: 1"   

As we have seen, only one row is visible when we catch the exception: the row that existed before the transaction started.