pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy isn't there a IF NOT EXISTS clause on constraint creation?
7+ messages / 4 participants
[nested] [flat]
* Why isn't there a IF NOT EXISTS clause on constraint creation?
@ 2023-08-30 20:38 richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-30 21:26 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: richard coleman @ 2023-08-30 20:38 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why isn't there a IF NOT EXISTS clause on constraint creation?
2023-08-30 20:38 Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-08-30 21:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-31 13:00 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2023-08-30 21:26 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why isn't there a IF NOT EXISTS clause on constraint creation?
2023-08-30 20:38 Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-30 21:26 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-08-31 13:00 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-09-01 11:14 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Luca Ferrari <fluca1978@gmail.com>
2023-09-01 13:14 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Geoff Winkless <pgsqladmin@geoff.dj>
0 siblings, 2 replies; 7+ messages in thread
From: richard coleman @ 2023-08-31 13:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why isn't there a IF NOT EXISTS clause on constraint creation?
2023-08-30 20:38 Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-30 21:26 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Tom Lane <tgl@sss.pgh.pa.us>
2023-08-31 13:00 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-09-01 11:14 ` Luca Ferrari <fluca1978@gmail.com>
2023-09-01 12:58 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Luca Ferrari @ 2023-09-01 11:14 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why isn't there a IF NOT EXISTS clause on constraint creation?
2023-08-30 20:38 Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-30 21:26 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Tom Lane <tgl@sss.pgh.pa.us>
2023-08-31 13:00 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
2023-09-01 11:14 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Luca Ferrari <fluca1978@gmail.com>
@ 2023-09-01 12:58 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-09-04 08:30 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Luca Ferrari <fluca1978@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: richard coleman @ 2023-09-01 12:58 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why isn't there a IF NOT EXISTS clause on constraint creation?
2023-08-30 20:38 Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-30 21:26 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Tom Lane <tgl@sss.pgh.pa.us>
2023-08-31 13:00 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
2023-09-01 11:14 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Luca Ferrari <fluca1978@gmail.com>
2023-09-01 12:58 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-09-04 08:30 ` Luca Ferrari <fluca1978@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Luca Ferrari @ 2023-09-04 08:30 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 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.
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why isn't there a IF NOT EXISTS clause on constraint creation?
2023-08-30 20:38 Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-30 21:26 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? Tom Lane <tgl@sss.pgh.pa.us>
2023-08-31 13:00 ` Re: Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
@ 2023-09-01 13:14 ` Geoff Winkless <pgsqladmin@geoff.dj>
1 sibling, 0 replies; 7+ messages in thread
From: Geoff Winkless @ 2023-09-01 13:14 UTC (permalink / raw)
To: richard coleman <rcoleman.ascentgl@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 have large 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-09-04 08:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-30 20:38 Why isn't there a IF NOT EXISTS clause on constraint creation? richard coleman <rcoleman.ascentgl@gmail.com>
2023-08-30 21:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-31 13:00 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-09-01 11:14 ` Luca Ferrari <fluca1978@gmail.com>
2023-09-01 12:58 ` richard coleman <rcoleman.ascentgl@gmail.com>
2023-09-04 08:30 ` Luca Ferrari <fluca1978@gmail.com>
2023-09-01 13:14 ` Geoff Winkless <pgsqladmin@geoff.dj>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox