Re: Alter index rename concurrently to

On Thu, Aug 2, 2018 at 4:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> [ reasons why DDL under less than AEL sucks ]
>
> Unfortunately, none of these problems are made to go away with an
> AcceptInvalidationMessages at statement start. That just makes the
> window smaller. But DDL effects could still be seen - or not -
> partway through a statement, with just as much ensuing hilarity
> as in your example. Maybe more.

Making the window a lot smaller -- so that it's a statement rather
than a transaction -- has a lot of value, I think. What's
particularly obnoxious about the transaction thing is that in many
cases the next statement will see the changes, but sometimes (if it
only references previously-referenced tables in previously-acquired
lock modes) it won't. I can't see any good reason to keep that
inconsistency around. It's true that the changes might or might not
see by a concurrently-running transaction, and it would be nice to do
something about that, too, but it's not clear that there is any
straightforward change that would accomplish that. OTOH, ensuring
that AcceptInvalidationMessages() runs at least once per statement
seems pretty straightforward.

> The real issue here, and the reason why I'm very unhappy with the mad rush
> in certain quarters to try to reduce locking levels for DDL, is exactly
> that it generally results in uncertainty about when the effects will be
> seen. I do not think your proposal does much more than put a fig leaf
> over that problem.

Let's not treat wanting to reduce locking levels for DDL as a
character flaw. As Andres says, it's a huge operational problem. I
don't think we should be irresponsible about lowering DDL lock levels
and accept random breakage along the way, but I do think we need,
rather desperately actually, to reduce lock levels. We need to figure
out which things are problems and what can be done about them, not
just say "no" to everything.

> Barring somebody having a great idea about resolving that, I think we
> just need to be very clear that any DDL done with less than AEL has
> exactly this issue. In the case at hand, couldn't we just document
> that "the effects of RENAME CONCURRENTLY may not be seen in other
> sessions right away; at worst, not till they begin a new transaction"?
> If you don't like that, don't use CONCURRENTLY.

Sure. On the other hand, we could ALSO insert an
AcceptInvalidationMessages() call once per statement and document that
they might not be seen until you begin a new statement, which seems
like it has basically no downside but is much better for users,
particularly because the name of an object tends -- in the great
majority of cases -- to matter only at the beginning of statement
processing. You can construct SQL queries that perform name lookups
midway through, but that's not typical.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company