Re: Compared MS SQL 2000 to Postgresql 9.0 on Windows

On 12/7/2010 1:22 PM, Justin Pitts wrote:
>>
>> Also, as a fair warning: mssql doesn't really care about transactions, but
>> PG really does.  Make sure all your code is properly starting and commiting
>> transactions.
>>
>> -Andy
>
> I do not understand that statement. Can you explain it a bit better?

In mssql you can write code that connects to the db, fire off updates 
and inserts, and then disconnects.  I believe mssql will keep all your 
changes, and the transaction stuff is done for you.

In PG the first statement you fire off (like an "insert into" for 
example) will start a transaction.  If you dont commit before you 
disconnect that transaction will be rolled back.  Even worse, if your 
program does not commit, but keeps the connection to the db open, the 
transaction will stay open too.

There are differences in the way mssql and pg do transactions.  mssql 
uses a transaction log and keeps current data in the table.  In mssql if 
you open a transaction and write a bunch of stuff, the table contains 
that new stuff.  Everyone can see it.  (I think default transaction 
isolation level is read commited).  But if you set your isolation level 
to something with repeatable read, then your program will block and have 
to wait on every little change to the table.  (or, probably page.. I 
think mssql has page level locking?)

anyway, in PG, multiple versions of the same row are kept, and when you 
open, and keep open a transaction, PG has to keep a version of the row 
for every change that other people make.  So a long lasting transaction 
could create hundreds of versions of one row.  Then when somebody goes 
to select against that table, it has to scan not only the rows, but 
every version of every row!

So my point is, in PG, use transactions as they were meant to be used, 
as single atomic operations.  Start, do some work, commit.

mssql made it easy to ignore transactions by doing it for you.  Ignoring 
transaction in PG will hurt you.

you can google MVCC and "postgres idle in transaction" for more.

-Andy