RE: BUG #15361: Add column if not exists create duplicate constraint
От | Olivier Leprêtre |
---|---|
Тема | RE: BUG #15361: Add column if not exists create duplicate constraint |
Дата | |
Msg-id | 02bd01d4429e$3ca99640$b5fcc2c0$@Lepretre@noetika.com обсуждение исходный текст |
Ответ на | Re: BUG #15361: Add column if not exists create duplicate constraint (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-bugs |
Hi Tom, Thanks for your point of view. I'm far from being a skilled postgres user so I could not discuss it much. From a "lambda" point of you, I found interesting to use those IF EXISTS because it saved me a few lines of code to check the information_table.columns and add the test accordingly. I appreciate it was doing that for me, code contains already so much lines just to check if everything is ok/not ok and rather few for the algorithm by itself). I expected that if column was not added, it was obvious that references should not, sort of transaction rollback. Thinking about backward compatibility, I can't figure out who could have used ADD COLUMN IF NOT EXISTS ...REFERENCES ... , expecting that the REFERENCES will be created anyway, but I do not have your experience. If this behaviour is not changed, perhaps would it be enough to modify docs which states "IF NOT EXISTS is specified and a column already exists with this name, no error is thrown" adding something like "but others statements like REFERENCES will be executed anyway." BR Olivier -----Message d'origine----- De : Tom Lane [mailto:tgl@sss.pgh.pa.us] Envoyé : samedi 1 septembre 2018 20:25 À : postgresql@noetika.com Cc : pgsql-bugs@lists.postgresql.org Objet : Re: BUG #15361: Add column if not exists create duplicate constraint =?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes: > I have a patching script that is supposed to add column if not existing : > ALTER TABLE myschem.table1 > ADD COLUMN IF NOT EXISTS col1 VARCHAR(254) REFERENCES > myschem.table2(col2) > When col1 already exists, I expected that nothing would happen. > [ but actually, it creates an FK constraint anyway ] As I've said many times before, I hate CREATE IF NOT EXISTS with a passion, because its semantics are so squishy. This is a perfect example of that: it's impossible to make a principled decision whether this is a bug or not, or what the correct behavior is if you think it's a bug. Should the command do nothing at all if col1 exists, regardless of whether there's an FK constraint or not? Should it avoid creating a duplicate constraint, and if so how picky are we to be about what "duplicate" means? What happens if myschem.table2(col2) doesn't exist? Not to mention whether we should change the behavior for other secondary objects that might be shown in the command, such as UNIQUE or CHECK constraints. Right now all of those get added, possibly redundantly, just like FK constraints. I believe this exact issue was debated when ADD COLUMN IF NOT EXISTS was added, and the camp that wanted it thought this behavior was fine. Even if we were now to conclude that this is a bug and agree on what'd be better semantics, there would be a pretty strong backwards-compatibility argument against changing it; some people's scripts might expect the constraint(s) to get added. The short answer is that IF NOT EXISTS gives you no guarantees whatsoever about the subsequent properties of the object, only that something by that name will exist. If you don't like that, don't use IF NOT EXISTS. regards, tom lane
В списке pgsql-bugs по дате отправления:
Следующее
От: Stephen FrostДата:
Сообщение: Re: BUG #15361: Add column if not exists create duplicate constraint