Date: Thu, 21 Jul 2005 17:21:05 +0300 (EEST)
From: Achilleus Mantzios <achill@matrix.gatewaynet.com>
To: Erik Wasser <erik.wasser@iquer.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Are long term never commited SELECT statements are a
In-Reply-To: <200507211557.58822.erik.wasser@iquer.net>
Message-ID: <Pine.LNX.4.44.0507211655210.2935-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-7
Content-Transfer-Encoding: 8BIT

O Erik Wasser έγραψε στις Jul 21, 2005 :

> Hello List,
> 
> I've written an application in perl using DBI with MySQL (no transaction 
> support). Then we decide to migrate it to postgresql 
> (postgresql-8.0.1-r4).
> 
> At first we were using 'AutoCommit => 1' with the application. That 
> means that every statement will be commited right away.
> 
> Then I discovered the 'magic of transactions' and set AutoCommit to 0. 
> Then I rewrite many UPDATE and INSERT statements with support for 
> commit and rollback. BUT: the SELECT statements were untouched (and 
> that was mistake I think).
> 
> Now I've got here a blocking problem. Severel SQL statements (like 
> renaming a field or UPDATE of a field) are blocked until I kill a 
> certain task. This task DOES only the INSERTS and UPDATES with a 
> transaction and the SELECT statements are not within an transaction. 
> And this task is a long term running task (some kind of daemon) so the 
> SELECT transactions will never be commited. Are long term never 
> commited SELECT statements are a problem and could that lead to 
> blocking other queries? 
> 
> To put it in annother way: what kind of thing I produced with the 
> following pseudocode?
> 
> # open database
> $DBH = DBI->connect(...,USERNAME,PASSWORD, { RaiseError => 1, AutoCommit 
> => 0 });
> 
> while (true)
> {
>    # do some select
>    SELECT ... FROM ...
>    # do some more 
>    SELECT ... FROM ...
> 
>    if (condition)
>       # do an UPDATE/INSERT
>       eval {
>          UPDATE/INSERT/...
>          $DBH->commit;
>       };
>       if ($@) {
>          warn "Transaction aborted: $@";
>          eval { $DBH->rollback };
>       }
>    }
> }
> 
> Is this some kind of nested transaction? Can there be a problem with 
> this code?

You mean savepoints?
In 8.x there is the feature of nested xactions.
But apparrently in your script you dont use them.

In general when working with BEGIN/COMMIT/ROLLBACK blocks
always be sure that you either rollback or commit
your transaction.
The need for this is more visible when using connection pools.
I am not familiar with the DBI semantics, but 
in your case it would be quite possible for
some job to block if another job's xaction has
already managed to update a row which the 1st job's xaction
tries to update too.

In any case, pure selects dont need to be in a xaction
unless you want to lock these rows, in which case
you use "FOR UPDATE".

In general you must dig a little deeper into PostgreSQL's
xaction mechanisms and policies,
since migrating from mysql requires some effort
regarding all new (to you) postgresql features.
The documentation (in the usual url) is superb.

> 
> Thanks for your help!
> 
> 

-- 
-Achilleus