[Bizgres-general] Off-list Re: [ENG] Re: Statement Queuing take II - Resource Scheduling (Running with cost and cursors)

[Jim C. Nasby]

On Mon, Aug 07, 2006 at 12:04:50PM +1000, Gavin Sherry wrote:
> The long running bit is maybe the hardest to solve. What that implies is
> holding locks for a long time. Thankfully, PostgreSQL is MVCC so that
> problem isn't as bad as it could be. But, if you suspend a process while

Maybe, maybe not. Unfortunately, thanks to vacuum, long-running
transactions in a mixed environment are nearly as fatal as they are in a
non-MVCC database. This is why I'd very much like to see the ability for
a long-running transaction to constrain itself to only touching a
specific set of tables, which means that vacuum would be able to process
other tables normally. Or I suppose another alternative would be to use
a hybrid of our vacuum and Oracles... tuples that are older than a
certain age get moved to a seperate storage area so that the heap and
indexes aren't getting horribly clogged. Neither approach will be easy,
but I think we're hitting the point where we have to rethink how vacuum
operates.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby at pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461