From: Erik Wasser <erik.wasser@iquer.net>
To: pgsql-sql@postgresql.org
Subject: Are long term never commited SELECT statements are a problem?
Date: Thu, 21 Jul 2005 15:57:56 +0200
User-Agent: KMail/1.8.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200507211557.58822.erik.wasser@iquer.net>

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?

Thanks for your help!

-- 
So long... Fuzz