Re: Alter index rename concurrently to

On Thu, Aug 2, 2018 at 4:02 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>> Inserting AcceptInvalidationMessages() in some location that
>> guarantees it will be executed at least once per SQL statement. I
>> tentatively propose the beginning of parse_analyze(), but I am open to
>> suggestions.
>
> I'm inclined to think that that doesn't really actually solve anything,
> but makes locking issues harder to find, because the window is smaller,
> but decidedly non-zero. Can you describe why this'd make things more
> "predictable" precisely?

Sure. I'd like to be able to explain in the documentation in simple
words when a given DDL change is likely to take effect. For changes
made under AccessExclusiveLock, there's really nothing to say: the
change will definitely take effect immediately. If you're able to do
anything at all with the relevant table, you must have got some kind
of lock on it, and that means you must have done
AcceptInvalidationMessages(), and so you will definitely see the
change. With DDL changes made under less than AccessExclusiveLock,
the situation is more complicated. Right now, we can say that a new
transaction will definitely see the changes, because it will have to
acquire a lock on that relation which it doesn't already have and will
thus have to do AcceptInvalidationMessages(). A new statement within
the same transaction may see the changes, or it may not. If it
mentions any relations not previously mentioned or if it does
something like UPDATE a relation where we previously only did a
SELECT, thus triggering a new lock acquisition, it will see the
changes. If a catchup interrupt gets sent to it, it will see the
changes. Otherwise, it won't. It's even possible that we'll start to
see the changes in the middle of a statement, because of a sinval
reset or something. To summarize, at present, all we can really say
is that changes made by concurrent DDL which doesn't take AEL may or
may not affect already-running queries, may or may not affect new
queries, and will affect new transactions.

With this change, we'd be able to say that new statements will
definitely see the results of concurrent DDL. That's a clear, easy to
understand rule which I think users will like. It would be even
better if we could say something stronger, e.g. "Concurrent DDL will
affect new SQL statements, but not those already in progress." Or
"Concurrent DDL will affect new SQL statements, but SQL statements
that are already running may take up to 10 seconds to react to the
changes". Or whatever. I'm not sure there's really a way to get to a
really solid guarantee, but being able to tell users that, at a
minimum, the next SQL statement will notice that things have changed
would be good. Otherwise, we'll have conversations like this:

User: I have a usage pattern where I run a DDL command to rename an
object, and then in another session I tried to refer to it by the new
name, and it sometimes it works and sometimes it doesn't. Why does
that happen?

Postgres Expert: Well, there are several possible reasons. If you
already had a transaction in progress in the second window and it
already had a lock on the object strong enough for the operation you
attempted to perform and no sinval resets occurred and nothing else
triggered invalidation processing, then it would still know that
object under the old name. Otherwise it would know about it under the
new name.

User: Well, I did have a transaction open and it may have had some
kind of lock on that object already, but how do I know whether
invalidation processing happened?

Postgres Expert: There's really know way to know. If something else
on the system were doing a lot of DDL operations, then it might fill
up the invalidation queue enough to trigger catchup interrupts or
sinval resets, but if not, it could be deferred for an arbitrarily
long period of time.

User: So you're saying that if I have two PostgreSQL sessions, and I
execute the same commands in those sessions in the same order, just
like the isolationtester does, I can get different answers depending
on whether some third session creates a lot of unrelated temporary
tables in a different database while that's happening?

Postgres Expert: Yes.

I leave it to your imagination to fill in what this imaginary user
will say next, but I bet it will be snarky.

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