Postgres Pro
Downloads
Home   >   mailing lists

Thread: Analyze using savepoints?

Analyze using savepoints?

From
Christopher Kings-Lynne
Date:
I read this in the release notes:

---
# Database-wide ANALYZE does not hold locks across tables (Tom)

This reduces the potential for deadlocks against other backends that 
want exclusive locks on tables. To get the benefit of this change, do 
not execute database-wide ANALYZE inside a transaction block (BEGIN 
block); it must be able to commit and start a new transaction for each 
table.
---

Does that mean that now if we used savepoints internally, analyze can 
still be run in a transaction and still not hold lots of locks?

Chris

Re: Analyze using savepoints?

From
Alvaro Herrera Munoz
Date:
On Mon, Aug 09, 2004 at 04:44:58PM +0800, Christopher Kings-Lynne wrote:
> I read this in the release notes:
> 
> ---
> # Database-wide ANALYZE does not hold locks across tables (Tom)
> 
> This reduces the potential for deadlocks against other backends that 
> want exclusive locks on tables. To get the benefit of this change, do 
> not execute database-wide ANALYZE inside a transaction block (BEGIN 
> block); it must be able to commit and start a new transaction for each 
> table.
> ---
> 
> Does that mean that now if we used savepoints internally, analyze can 
> still be run in a transaction and still not hold lots of locks?

No, because savepoints do not release locks on successful completion, only
on rollback.

I think both VACUUM and ANALYZE could be best served by appropiate use
of short-lived ResourceOwners.  It needs some thought though.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"El realista sabe lo que quiere; el idealista quiere lo que sabe" (Anónimo)

Re: Analyze using savepoints?

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> I read this in the release notes:
> ---
> # Database-wide ANALYZE does not hold locks across tables (Tom)

> This reduces the potential for deadlocks against other backends that 
> want exclusive locks on tables. To get the benefit of this change, do 
> not execute database-wide ANALYZE inside a transaction block (BEGIN 
> block); it must be able to commit and start a new transaction for each 
> table.
> ---

> Does that mean that now if we used savepoints internally, analyze can 
> still be run in a transaction and still not hold lots of locks?

No.
        regards, tom lane

Re: Analyze using savepoints?

From
Tom Lane
Date:
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> writes:
> I think both VACUUM and ANALYZE could be best served by appropiate use
> of short-lived ResourceOwners.  It needs some thought though.

At least for VACUUM FULL, this is pretty much a nonstarter: it needs a
real live genuine COMMIT in the middle.  No half measures unless you are
willing to lose your data on crash.

ANALYZE could possibly get away with simply releasing the table lock early.
I haven't thought about it in detail.
        regards, tom lane