> That doesn't matter if any _other_ module is performing UPDATEs (or > inserts and deletes) to the tables your module operates on. Your > module's open transactions may still prevent PostgreSQL from reclaiming > space, because it needs to keep the old versions of tuples around. There are no updates to the underlying tables. The tables are updated as part of a release only. They contain static data only. The data is used for calculations only and is thus read-only. > (I'm struggling, right now, to explain why this is so - if this is so - > for READ COMMITTED transactions, though it certainly is for SERIALIZABLE > transactions. For READ COMMITTED the transaction's own uncommitted > changes can't be discarded vacuumed, but these are presumably read-only > transactions anyway - and they don't need to be able to see any old > tuples other than any they may have inserted/modified. Will open READ > COMMITTED transactions actually impair VACUUM? VACUUM FULL or CLUSTER, > sure, but plain VACUUM?) I had no idea what vacuum was till I read your post and googled. Yup I'm a true blue newbie. I guess I still know very little. But if vacuum is a compaction and stats gathering utility and is table specific, then I guess I'm not worried even if it cannot run on my tables because the data itself is static. > Your uncommitted transactions will also make it very hard to make schema > changes such as adding or altering columns to your tables. We never do this. I'm not worried about this. > If your module is the only one active on these tables, and it's only > doing read activity, then it doesn't really matter very much that the > transactions are left open. It's kind of ugly, though. I do intend to diagnose and fix the issue. I was trying to ascertain the urgency of the issue to figure out whether to forfeit a spectacular summer weekend. It sounds like that a catastrophe is not imminent and can wait for module version 1.1 in about a month. Cape Cod here I come. > Yes, it should. Are you certain your app is using autocommit across the > board, though, and that something else (say, your connection pooler) > isn't "helping" you by turning it back off again? I re-checked the code and auto commit mode is not being turned on. So my bad there! I know what to do to fix the issue. Thanks for all your help Sid -----Original Message----- From: Craig Ringer [mailto:craig@xxxxxxxxxxxxxxxxxxxxx] Sent: Friday, July 17, 2009 2:55 PM To: Sharma, Sid Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Idle in transaction On Fri, 2009-07-17 at 12:09 -0400, Sharma, Sid wrote: > I'm sure it is outdated. When I suggested a new application I should > have said a new module within an existing application. In other words, > the db pre-exists. We are rewriting our entire application(s) and moving > our database to DB2 and Oracle starting pretty soon. So postgres is end > of life here and so there is no stomach or interest to upgrade. But you're developing new code targeted to Pg? > > That's bad. It means your client program is starting a transaction > and > > leaving it running without doing anything with it. This is an issue > with > > the way the client is programmed, or with the client drivers, not with > > the server. The server is doing what it's told. > > > > The reason this is bad is that PG can't properly complete maintenance > if > > there are transactions that are left open constantly. Eventually your > > DB will fill up the entire disk with old data that can't be cleaned > up. > > The module/application is read-only and so only does SELECTs. That doesn't matter if any _other_ module is performing UPDATEs (or inserts and deletes) to the tables your module operates on. Your module's open transactions may still prevent PostgreSQL from reclaiming space, because it needs to keep the old versions of tuples around. (I'm struggling, right now, to explain why this is so - if this is so - for READ COMMITTED transactions, though it certainly is for SERIALIZABLE transactions. For READ COMMITTED the transaction's own uncommitted changes can't be discarded vacuumed, but these are presumably read-only transactions anyway - and they don't need to be able to see any old tuples other than any they may have inserted/modified. Will open READ COMMITTED transactions actually impair VACUUM? VACUUM FULL or CLUSTER, sure, but plain VACUUM?) Your uncommitted transactions will also make it very hard to make schema changes such as adding or altering columns to your tables. If your module is the only one active on these tables, and it's only doing read activity, then it doesn't really matter very much that the transactions are left open. It's kind of ugly, though. > So there is no imminent danger then of catastrophic failure such as > memory full or disk full or cpu spikes or db slowness given my > application only does queries? I'm trying to figure out whether I should > chill this weekend on the beach or kiss that good-bye and work this > issue? :-) > I am setting auto-commit to on, so shouldn't a commit be issued once the > select executes (or a rollback on a sql exception)? Yes, it should. Are you certain your app is using autocommit across the board, though, and that something else (say, your connection pooler) isn't "helping" you by turning it back off again? If you turn autocommit back off and issue a rollback before handing the connection back to the connection pooler, does the issue go away? Personally I prefer to explicitly manage transactions anyway. Most Java code/libraries seem to expect autocommit to be off, it makes it easier to guarantee that read-only code is exactly that (by issuing a rollback when finished), and for read/write queries I like to know for certain when data has been committed to the database. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
