pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpersistent locks
2+ messages / 2 participants
[nested] [flat]
* persistent locks
@ 2012-01-20 13:08 paddy carroll <paddy.carroll@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: paddy carroll @ 2012-01-20 13:08 UTC (permalink / raw)
To: pgsql-admin
The implementation is a database firewall where postgres gets used between our corporate and production network environments on a Linux (RHEL 4.8) platform.
Were transitioning from 8.3.7 to 9.x. and were also implementing a link to postgres from SQL server 2005 using the PGNP OLE DB driver from pgoledb, we have,for the present, switched to the opensource ODBC driver because of continual issues with PGNP over SSL (they are working on it).
Our applications are base on Windows Server and Tomcat and use the Hibernate ORM with MSDTC (Microsoft Distributed Transaction Coordinator), I expect this to be able to manage transaction commits across both Postgres and SQL Server however.
1) PGNP has been leaking connections, not closing them, from SQL server
2) postgres 8.3.7 and to a lesser extent 9.0.3 has been shown to hang onto the open connections and maintain locks on the table in question.
3) For 8.3.7 the locks persist a database shutdown and startup cycle - I have had to trash and rebuild the database in question.
4) For 8.3.7 I have demonstrated that I can remedy the problem by stopping the database, deleting the contents of pg_twophase and pg_subtrans and restarting the database
My questions are:
a) Is it safe to delete the contents of pg_twophase and pg_subtrans
b) Are there any other steps I should take to mitigate unnecessary side effects of this action
c) Is there a better way?
d) Is this a bug that has been fixed and if so when, i.e. will it be Ok to deploy 9.1.x in production without support advice to follow this strategy should we get abandoned transactions in future.
Thanks
paddy carroll=
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: persistent locks
@ 2012-01-20 15:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: paddy carroll <paddy.carroll@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2012-01-20 15:01 UTC (permalink / raw)
To: paddy carroll <paddy.carroll@gmail.com>; +Cc: pgsql-admin
paddy carroll <paddy.carroll@gmail.com> writes:
> 3) For 8.3.7 the locks persist a database shutdown and startup cycle - I have had to trash and rebuild the database in question.
> 4) For 8.3.7 I have demonstrated that I can remedy the problem by stopping the database, deleting the contents of pg_twophase and pg_subtrans and restarting the database
Egad. Please read about prepared transactions and how to commit them or
roll them back; or else fix your applications to not use them.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2012-01-20 15:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-01-20 13:08 persistent locks paddy carroll <paddy.carroll@gmail.com>
2012-01-20 15:01 ` Tom Lane <tgl@sss.pgh.pa.us>
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