Re: allow LIMIT in UPDATE and DELETE

On Tue, May 23, 2006 at 10:30:35AM +0200, Csaba Nagy wrote:
> On Tue, 2006-05-23 at 00:04, Jim C. Nasby wrote:
> > I can't imagine how bad this would be if the database actually had
> > hour-long reports that had to run... and luckily the system is quiet at
> > night when pg_dump runs.
>
> BTW, pg_dump is the only thing that stays in my way with the CLUSTER
> strategy against long running transactions, because it locks (in shared
> mode, but that's just enough) all dumped tables at the beginning. It
> would be nice to have an "exclude-table" option on it. I actually
> started working on a patch to allow that, I will make it just good
> enough for my purpose (very poor C skills here). Would that be
> interesting for others ?

Well, being able to have finer control over what you're dumping is on
the TODO list, and I think there was even consensus reached on -hackers
as to how the syntax should work.

But that's only a partial fix, because generally you'd want a complete
dump of your database anyway. What would be better is if pg_dump could
release locks as it no longer needs them, namely as it dumps each table.
Though this might require pg_dump remembering some state information
about each object since certain things are dumped after all the COPY
commands, such as RI.

This also doesn't address the issue of long-running transactions
preventing dead rows from being vacuumed.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461