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
Message-ID: <200507211557.58822.erik.wasser@iquer.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik.wasser@iquer.net
Subject: Re: Are long term never commited SELECT statements are a problem?
In-Reply-To: <200507211557.58822.erik.wasser@iquer.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox