Re: [GENERAL] Isolation of schema renames - Mailing list pgsql-general
| From | Ben Leslie |
|---|---|
| Subject | Re: [GENERAL] Isolation of schema renames |
| Date | |
| Msg-id | CABZ0LtD=r4hwXbdeeosoJ-ivotSV5i3_0tf+U5SeU_nk2DA2gQ@mail.gmail.com Whole thread Raw |
| In response to | Re: [GENERAL] Isolation of schema renames (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-general |
On 10 August 2017 at 12:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
Ben Leslie <benno@benno.id.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)
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
pgsql-general by date: