Re: ALTER TABLE lock strength reduction patch is unsafe Reply-To:

On 7 March 2014 09:04, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> The right thing to do here is to not push to the extremes. If we mess
> too much with the ruleutil stuff it will just be buggy. A more
> considered analysis in a later release is required for a full and
> complete approach. As I indicated earlier, an 80/20 solution is better
> for this release.
>
> Slimming down the patch, I've removed changes to lock levels for
> almost all variants. The only lock levels now reduced are those for
> VALIDATE, plus setting of relation and attribute level options.
>
> VALIDATE is implemented by calling pg_get_constraintdef_mvcc(), a
> slightly modified variant of pg_get_constraintdef that uses the
> transaction snapshot. I propose this rather than Noah's solution
> solely because this will allow any user to request the MVCC data,
> rather than implement a hack that only works for pg_dump. I will post
> the patch later today.

Implemented in attached patch, v22

The following commands (only) are allowed with
ShareUpdateExclusiveLock, patch includes doc changes.

ALTER TABLE ... VALIDATE CONSTRAINT constraint_name
covered by isolation test, plus verified manually with pg_dump

ALTER TABLE ... ALTER COLUMN ... SET STATISTICS
ALTER TABLE ... ALTER COLUMN ... SET (...)
ALTER TABLE ... ALTER COLUMN ... RESET (...)

ALTER TABLE ... CLUSTER ON ...
ALTER TABLE ... SET WITHOUT CLUSTER
ALTER TABLE ... SET (...)
covered by isolation test

ALTER TABLE ... RESET (...)

ALTER INDEX ... SET (...)
ALTER INDEX ... RESET (...)

All other ALTER commands take AccessExclusiveLock

I commend this patch to you for final review; I would like to commit
this in a few days.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services