can long-lived transactions cause problems?

От:

David Powell <david@drp.id.au>

Дата:

4 августа 2009 г. в 21:51:59

Greetings,

I have an application which is using long idle transactions.  Whenever
a transaction is commited, a new transaction is started which may then
sit idle until the next client access.  This was working fine until I
attempted to use pg_reorg to cluster a table in the database.

It seems that pg_reorg blocks indefinitely if there is an open
transaction on the database.  I assume this is because pg_reorg is
attempting to grab some type of lock?  I can probably avoid this
particular problem, but does anyone known if I'm likely to run into
any other problems by having long lived transactions?

Thanks,

-- David

Re: can long-lived transactions cause problems?

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

4 августа 2009 г. в 22:13:07

David Powell  writes:
> I have an application which is using long idle transactions.  Whenever
> a transaction is commited, a new transaction is started which may then
> sit idle until the next client access.

This is generally considered bad application design.  Long-lived
transactions are a bad idea and definitely can hurt the performance of
other stuff.

Depending on which client library and PG version you are using,
issuing a BEGIN and then doing nothing for a long time might not hurt
you, but I can't recommend it.

			regards, tom lane