Обсуждение: Why isn't there a IF NOT EXISTS clause on constraint creation?
I find myself having to check a couple of hundred tables and add the primary keys back in, if they don't exist.
While there's a IF NOT EXISTS clause on the DROP statement to drop a constraint, like a primary key, inexplicably there isn't one on the ALTER TABLE statement that creates them.
The internet is littered with people asking about this, some going back years. The responses range from manually checking first, using a DROP IF EXISTS statement first, to writing various blocks of PL/pgSQL code.
Why doesn't ALTER TABLE ... ADD CONSTRAINT not come with an ALTER TABLE ... ADD CONSTRAINT IF NOT EXISTS variant?
Could one be added?
Just wondering,
rik.
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> Why doesn't ALTER TABLE ... ADD CONSTRAINT not come with an ALTER TABLE
> ... ADD CONSTRAINT IF NOT EXISTS variant?
There's a general allergy in this project to CREATE IF NOT EXISTS,
because it's a foot-gun. If the command succeeds, you know that
an object (constraint or whatever) by that name exists, but you
have no right to assume anything whatsoever about its properties.
CREATE OR REPLACE semantics are a lot less squishy. Would a
command of that form solve your problem? (I'm not sure about
a pleasing way to write that within ALTER TABLE, but this gripe
is about semantics not syntax.)
regards, tom lane
Tom,
Thanks, but a CREATE OR REPLACE is functionally equivalent to a DROP IF NOT EXISTS; ALTER TABLE pairing.
In both cases you would be recreating an identical constraint to the one that exists if it was already there. If you have large tables, and/or a composite key, that's a non-trivial amount of time and resources wasted.
Typically my case is that I have a large number of tables that have been move/recreated from another source. Perhaps they we done at different times, by different people, or as in my present case they were copied programmatically and outside forces caused a random number of table to be built without their indices and constraints. For the indices the solution's trivial. I just rerun the script that creates the indices with the IF NOT EXISTS clause in the CREATE INDEX command. Those that already have the index are skipped, those that are missing it get it created. For the primary keys, there's no such option. Other than writing my own function to effectively wrap the existing ALTER TABLE command, I have to either manually check the PKey status of hundreds of tables and then modify the script that adds the PKeys, or rewrite the alter statement to take into account that it might already exist, or just drop any that exist and add them all back in again.
There are literally dozens of places in PostgreSQL where the inexplicable lack of IF EXISTS / IF NOT EXISTS semantics causes untold manual rechecking (trivial for a couple of tables not so much for hundreds or thousands of tables), the writing of ad hoc PL/pgSQL DO blocks, or creating custom functions whose sole purpose is to wrap the existing command in order to add the missing IF EXISTS/IF NOT EXISTS functionality.
We can't be so afraid of a 'foot-gun' that we force users to go hunting big game with a NERF gun instead.
I hope that helps explain things better,
rik.
On Wed, Aug 30, 2023 at 5:26 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
richard coleman <rcoleman.ascentgl@gmail.com> writes:
> Why doesn't ALTER TABLE ... ADD CONSTRAINT not come with an ALTER TABLE
> ... ADD CONSTRAINT IF NOT EXISTS variant?
There's a general allergy in this project to CREATE IF NOT EXISTS,
because it's a foot-gun. If the command succeeds, you know that
an object (constraint or whatever) by that name exists, but you
have no right to assume anything whatsoever about its properties.
CREATE OR REPLACE semantics are a lot less squishy. Would a
command of that form solve your problem? (I'm not sure about
a pleasing way to write that within ALTER TABLE, but this gripe
is about semantics not syntax.)
regards, tom lane
On Thu, Aug 31, 2023 at 5:03 PM richard coleman <rcoleman.ascentgl@gmail.com> wrote: > > Those that already have the index are skipped, those that are missing it get it created. For the primary keys, there'sno such option. I don't see why such an option should exist for primary keys, since it seems to me a problem of design within your schema. At the moment, you have to manually check against primary keys, and that should not be that hard. In the future, a deployment tool like sqitch could help you get rid of this kind of panic. Luca
Luca,
Thanks, but I don't think you understand the scope of the matter.
The schema is just fine. When loading large amounts of data, some tables are greater than a terabyte in size, you typically don't have keys or indices until after it finishes as that slows things down.
I am not talking about two or three tables, but hundreds of tables. It's a rather labor intensive option for want of a IF NOT EXISTS clause.
There's no panic here, just a puzzlement at why the devs would force users to have to recreate IF NOT EXITS functionality, either via PL/pgSQL anonymous blocks or custom wrapper functions, again and again and again for each user instead of offering that functionality. It's not like it's a novel thing. Many commands, seemingly at random, expose that functionality.
Seems like something that could be rectified once for all of PostgreSQL rather than forcing users to continually recreate this wheel.
that's all.
rik.
On Fri, Sep 1, 2023 at 7:14 AM Luca Ferrari <fluca1978@gmail.com> wrote:
On Thu, Aug 31, 2023 at 5:03 PM richard coleman
<rcoleman.ascentgl@gmail.com> wrote:
>
> Those that already have the index are skipped, those that are missing it get it created. For the primary keys, there's no such option.
I don't see why such an option should exist for primary keys, since it
seems to me a problem of design within your schema.
At the moment, you have to manually check against primary keys, and
that should not be that hard.
In the future, a deployment tool like sqitch could help you get rid of
this kind of panic.
Luca
On Thu, 31 Aug 2023 at 14:00, richard coleman <rcoleman.ascentgl@gmail.com> wrote: > Thanks, but a CREATE OR REPLACE is functionally equivalent to a DROP IF NOT EXISTS; ALTER TABLE pairing. > In both cases you would be recreating an identical constraint to the one that exists if it was already there. If you havelarge tables, and/or a composite key, that's a non-trivial amount of time and resources wasted. It absolutely does not have to be. CREATE OR REPLACE should be smart enough to do nothing if the existing object were the same as the new one. Geoff
On Fri, Sep 1, 2023 at 2:58 PM richard coleman <rcoleman.ascentgl@gmail.com> wrote: > > The schema is just fine. When loading large amounts of data, some tables are greater than a terabyte in size, you typicallydon't have keys or indices until after it finishes as that slows things down. > I am not talking about two or three tables, but hundreds of tables. It's a rather labor intensive option for want of aIF NOT EXISTS clause. Well, I see the scope for tools like sqitch here. Also because testing the existence of the index, in your particular case, is one small step. What happens if the index has been invalidated somehow? You will see an existing index that is unusable, and this could happen also with a fantomatic "IF NOT EXISTS" behavior, so it sounds to me you need something even more smarter. Luca