Re: ALTER TABLE lock strength reduction patch is unsafe

On 03/06/2014 10:47 AM, Simon Riggs wrote:
> On 5 March 2014 09:28, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
>> So that returns us to solving the catalog consistency problem in
>> pg_dump and similar applications.
> No answer, guys, and time is ticking away here.

Sorry, I've accumulated several days of backlog (and it'll only get
worse in the coming week) so I haven't had time to look at all this in
the detail I wanted to.

> I'd like to get a communal solution to this rather than just punting
> the whole patch.
>
> If we have to strip it down to the bar essentials, so be it. For me,
> the biggest need here is to make VALIDATE CONSTRAINT take only a
> ShareUpdateExclusiveLock while it runs. Almost everything else needs a
> full AccessExclusiveLock anyway, or doesn't run for very long so isn't
> a critical problem. (Perhaps we can then wrap ADD CONSTRAINT ... NOT
> VALID and VALIDATE into a single command using the CONCURRENTLY
> keyword so it runs two transactions to complete the task).
>
> Validating FKs on big tables can take hours and it really isn't
> acceptable for us to lock out access while we do that. FKs are
> *supposed* to be a major reason people use RDBMS, so keeping them in a
> state where they are effectively unusable is a major debilitating
> point against adoption of PostgreSQL.
>
> If there are issues with pg_dump we can just document them.
>
> Guide me with your thoughts.

I think committing VALIDATE CONSTRAINT is essential for 9.4; the rest
can be delayed until 9.5. None of the discussion in this thread has
been about that subcommand, and I don't personally see a problem with it.

I don't care much about ADD CONSTRAINT CONCURRENTLY. If it's there,
fine. If it's not, that's fine, too.

My personal use case for this, and I even started writing a patch before
I realized I was re-writing this one, is adding a CHECK constraint NOT
VALID so that future commits respect it, then UPDATEing the existing
rows to "fix" them, and then VALIDATE CONSTRAINTing it. There is zero
need for an AccessExclusiveLock on that last step.

My original idea was to concurrently create a partial index on the "bad"
rows, and then validate the constraint using that index. The AEL would
only be held long enough to check if the index is empty or not.
Obviously, reducing the lock level is a cleaner solution, so I'd like to
see that happen.

--
Vik