Re: Are long term never commited SELECT statements are a pr

You may get problems. At least we did.
Having a long term transaction which seemingly just was one Begin with
nothing, we encountered a siginifficant decrease of performance after some
days (70 tx/sec)
During that the pg_subtrans dir filled up with files and the IO-reads of the
disk as well. After closing the particular connection the normal performance
reappeared.
And the files in pg_subtrans became one  file again.

So far.

BTW. You will see the DB with a glasses that shows you the time when the
transaction started.

|-----Original Message-----
|From: Erik Wasser [mailto:erik.wasser@iquer.net]
|Sent: Donnerstag, 21. Juli 2005 15:58
|To: pgsql-sql@postgresql.org
|Subject: [SQL] Are long term never commited SELECT statements are a
|problem?
|
|
|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
|
|---------------------------(end of 
|broadcast)---------------------------
|TIP 2: Don't 'kill -9' the postmaster
|