Обсуждение: Looks heap_create_with_catalog ignored the if_not_exists options
for a createStmt, it will call transformCreateStmt, and then heap_create_with_catalog.
but looks it just check the if_not_exists in transformCreateStmt.
so there is a chance that when the transformCreateStmt is called, the table is not created, but before the heap_create_with_catalog is called, the table was created. if so, the "if not exits" will raise error "ERROR: relation "xxxx" already exists"
I can reproduce this with gdb,
demo=# create table if not exists dddd2 (a int);
ERROR: relation "dddd2" already exists
is it designed as this on purpose or is it a bug?
I am using the lates commit on github now.
On Fri, Mar 01, 2019 at 07:17:04PM +0800, Andy Fan wrote: > for a createStmt, it will call transformCreateStmt, and then > heap_create_with_catalog. > but looks it just check the if_not_exists in transformCreateStmt. > > is it designed as this on purpose or is it a bug? That's a bug. Andreas Karlsson and I have been discussing it a couple of days ago actually: https://www.postgresql.org/message-id/20190215081451.GD2240@paquier.xyz Fixing this is not as straight-forward as it seems, as it requires shuffling a bit the code related to a CTAS creation so as all code paths check at the same time for an existing relation. Based on my first impressions, I got the feeling that it would be rather invasive and not worth a back-patch. -- Michael
Вложения
Thank you Michael!
What can I do if I'm sure I will not use the CTAS creation ? Take a look at the "heap_create_with_catalog" function, it check it and raise error. Even I change it to "check it && if_not_existing", raise error, it is still be problematic since we may some other session create between the check and the real creation end.
Looks we need some locking there, but since PG is processes model, I even don't know how to sync some code among processes in PG (any hint on this will be pretty good as well).
On Fri, Mar 1, 2019 at 8:35 PM Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Mar 01, 2019 at 07:17:04PM +0800, Andy Fan wrote:
> for a createStmt, it will call transformCreateStmt, and then
> heap_create_with_catalog.
> but looks it just check the if_not_exists in transformCreateStmt.
>
> is it designed as this on purpose or is it a bug?
That's a bug. Andreas Karlsson and I have been discussing it a couple
of days ago actually:
https://www.postgresql.org/message-id/20190215081451.GD2240@paquier.xyz
Fixing this is not as straight-forward as it seems, as it requires
shuffling a bit the code related to a CTAS creation so as all code
paths check at the same time for an existing relation. Based on my
first impressions, I got the feeling that it would be rather invasive
and not worth a back-patch.
--
Michael
On Sat, Mar 02, 2019 at 12:15:19AM +0800, Andy Fan wrote: > Looks we need some locking there, but since PG is processes model, I even > don't know how to sync some code among processes in PG (any hint on this > will be pretty good as well). No, you shouldn't need any kind of extra locking here. -- Michael