Re: VACUUM and open transactions

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 10/19/06 15:22, Martijn van Oosterhout wrote:
> On Thu, Oct 19, 2006 at 04:18:50PM -0400, Joseph Shraibman wrote:
>> I'm running postgres 8.0.8. I have a table that is updated very
>> rapidly, so I vacuum it every 10 minutes. The problem is that I
>> sometimes have transactions that hang out for a long time without doing
>> anything. These transactions are preventing VACUUM from cleaning up
>> tuples that were created and then deleted in transactions that started
>> way after the hanging one. Is there any way to fix this?
>
> Sure, don't keep transactions open for so long. Is there a particular
> reason you do that?

Not every system is OLTP.

If these are long-open online transactions, then as MvO implies, you
definitely have a problem that needs fixing.

OTOH, if these are validly long-running updaters, you (the OP) will
have to think outside the box for techniques to break that million-
row UPDATE statement into short-time committable chunks.

"Canditate key" tables and PL/pgSQL or a scripting language are one
possibility.

- --
Ron Johnson, Jr.
Jefferson LA USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQFFOEc4S9HxQb37XmcRAke6AJ4hh6YOxEoWPNyczc5/ajkWH7fqxwCfXJy3
izu264kElNxESaC0qjdPc68=
=E8tr
-----END PGP SIGNATURE-----