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(dot)wasser(at)iquer(dot)net]
|Sent: Donnerstag, 21. Juli 2005 15:58
|To: pgsql-sql(at)postgresql(dot)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
|