Re: ANALYZE locks pg_listener in EXCLUSIVE for long time?

"Zeugswetter Andreas SB SD" <ZeugswetterA(at)spardat(dot)at> writes:
>> * Is it really a good idea for database-wide ANALYZE to run as a single
>> transaction? Holding all those locks is a recipe for deadlocks, even
>> if they're as inoffensive as AccessShareLocks normally are.

> Wasn't one idea behind that change also to not make the planner create a plan
> from mixed old and new statistics ?

I don't recall that that was part of the discussion. IIRC all we were
after was to let someone invoke ANALYZE from inside a BEGIN/COMMIT block.

A possible compromise is to hack ANALYZE so that if it is invoked when
*not* within a BEGIN block, it runs a separate transaction for each
table. This seems pretty crufty but might satisfy all the requirements.

> I guess that could later be accomplished with
> "begin work; analyze; commit work;" (with subtransactions) though.

AFAICS, locks taken by a (committed) subtransaction can't be released
until top-level commit anyhow. Otherwise they fail to perform one of
the essential functions of locking in an MVCC environment: to delay
another process until the changes you've made are visible to him.

regards, tom lane