Re: Isolation of schema renames

On 10 August 2017 at 12:14, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ben Leslie <benno(at)benno(dot)id(dot)au> writes:

Thanks for the quick response, I appreciate it.

> I'm wondering if I can/should expect schema renames to be isolated.

Nope, you should not.

That's fine. I think I can achieve what I want using an alternative
approach.

> This is not an especially easy thing to fix, because to have the system
> behave as you wish it did, your second transaction would have to be
> ignoring already-committed DDL changes, and it is very easy to show
> examples where that would be fatal. For example, consider
>

This is a bit confusing to me, as it seems like for other similar DDL
changes it does behave as I expect, and it's just the "ALTER SCHEMA" is a
bit of an outlier.

> Now, in practice this specific example doesn't go through anyway, because
> after its first insert, S2's transaction is holding a lock on t that
> precludes the ALTER TABLE steps.

Right, this was I guess my expectation, that ALTER SCHEMA would block in a
similar way to how ALTER TABLE blocks.

But I only wrote it this way for
> pedagogic purposes. If S2 had done some things but not touched t2 yet,
> the concurrent ALTERs would succeed, and then S2 has no choice but to
> respect the results of that DDL.
>

If I have:

S1 S2
begin; begin;

select * from test.test; (takes AccessShareLock
on test.test)

alter table test.test rename to test_old; <--- blocks getting
AccessExclusiveLock on test.test

select * from test.test; (returns same data as
first select)

By contrast:

S1 S2
begin; begin;

select * from test.test; (takes AccessShareLock
on test.test)

alter schema test rename to test_old; <---- does not block
commit;
select * from test.test; (fails )

I'm not saying anything should change, but from a naive point of view, I
feel like scenario one above is "doing the right thing" and S2 is getting
repeatable reads as it would expect, and the second scenario seems to be
"doing the wrong thing" because it is no longer getting a repeatable read.

From my point of view it seems like some DDL changes are isolated, but
schema changes are not isolated. [Maybe I'm not using 'isolated' in a
formally correct way here, in this specific case I just mean that S2
doesn't see the changes done in S1, not that both S1 and S2 can make
progress].

I don't know the PostgreSQL internals, but I'm guessing that there must not
be locks on schemas as there are on tables, and/or taking schemas would be
too much overhead to avoid the case shown above (which is fine, I just
wanted to verify this is intended behaviour and make sure I wasn't doing
something too stupid).

Cheers,

Ben