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:[EMAIL PROTECTED] |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 | ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
