Thread: [WIP] ALTER COLUMN IF EXISTS

Hi,

I'm interested in adding more ergonomics to DDL commands, in
particular supporting IF EXISTS for ALTER TABLE … ALTER COLUMN, so
that if a column doesn't exist the command is skipped.

IF EXISTS is already supported in various places (e.g. ALTER TABLE …
ADD COLUMN IF NOT EXISTS, and ALTER TABLE … DROP COLUMN IF EXISTS),
but it's not available for any of the ALTER COLUMN sub commands.

The motivation is to make it easier to write idempotent migrations
that can be incrementally authored, such that they can be re-executed
multiple times without having to write an "up" and "down" migration.
https://github.com/graphile/migrate#idempotency elaborates a bit more
on the approach.

The current approach I see is to write something like:

DO $$
  BEGIN
    IF EXISTS (SELECT 1
     FROM information_schema.columns
     WHERE table_schema = 'myschema' AND table_name = 'mytable' AND
column_name = 'mycolume')
    THEN
      ALTER TABLE myschema.mytable RENAME mycolume TO mycolumn;
    END IF;
  END
$$;

I think ideally the IF EXISTS would be added to all of the ALTER
COLUMN commands, however for the moment I have only added it to the {
SET | DROP } NOT NULL command to demonstrate the approach and see if
there's in-principle support for such a change.

Questions:

1. I assume this is not part of the SQL specification, so this would
introduce more deviation to PostgreSQL. Is that accurate? Is that
problematic?
2. I believe I'm missing some code paths for table inheritance, is that correct?
3. I haven't updated the documentation—is it correct to do that in
doc/src/sgml/ref/alter_table.sgml?
4. This is my first time attempting to contribute to PostgreSQL, have
I missed anything?

--
Cheers,
Brad

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Mar 31, 2022 at 4:39 PM Bradley Ayers <bradley.ayers@gmail.com>
> wrote:
>> I'm interested in adding more ergonomics to DDL commands, in
>> particular supporting IF EXISTS for ALTER TABLE … ALTER COLUMN, so
>> that if a column doesn't exist the command is skipped.

> At present the project seems to largely consider the IF EXISTS/IF NOT
> EXISTS features to have been largely a mistake and while removing it is not
> going to happen the desire to change or extend it is not strong.

That might be an overstatement.  There's definitely a camp that
doesn't like CREATE IF NOT EXISTS, precisely on the grounds that it's
not idempotent --- success of the command tells you very little about
the state of the object, beyond the fact that some object of that name
now exists.  (DROP IF EXISTS, by comparison, *is* idempotent: success
guarantees that the object now does not exist.  CREATE OR REPLACE
is also idempotent, or at least much closer than IF NOT EXISTS.)
It's not entirely clear to me whether ALTER IF EXISTS could escape any
of that concern, but offhand it seems like it's close to the CREATE
problem.  I do kind of wonder what the use-case for it is, anyway.

One thing to keep in mind is that unlike some other DBMSes, you
can script pretty much any conditional DDL you want in Postgres.
This considerably reduces the pressure to provide conditionalization
built right into the DDL commands.  As a result, we (or at least I)
prefer to offer only the most clearly useful, best-defined cases
as built-in DDL features.  So there's definitely a hurdle that
an ALTER IF EXISTS patch would have to clear before having a chance
of being accepted.

            regards, tom lane

On Thu, Mar 31, 2022 at 8:02 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> At present the project seems to largely consider the IF EXISTS/IF NOT EXISTS features to have been largely a mistake
andwhile removing it is not going to happen the desire to change or extend it is not strong.
 

I like the IF [NOT] EXISTS stuff quite a bit. I wish it had existed
back when I was doing application programming with PostgreSQL. I would
have used it for exactly the sorts of things that Bradley mentions.

I don't know how far it's worth taking this stuff. I dislike the fact
that when you get beyond what you can do with IF [NOT] EXISTS, you're
suddenly thrown into having to write SQL against system catalog
contents which, if you're the sort of person who really likes the IF
[NOT] EXISTS commands, may well be something you don't feel terribly
comfortable doing. It's almost tempting to propose new SQL functions
just for these kinds of scripts. Like instead of adding support
for....

      ALTER TABLE myschema.mytable IF EXISTS RENAME IF EXISTS this TO that;

...and I presume you need IF EXISTS twice, once for the table and once
for the column, we could instead make it possible for people to write:

IF pg_table_exists('myschema.mytable') AND
pg_table_has_column('myschema.mytable', 'this') THEN
    ALTER TABLE myschema.mytable RENAME this TO that;
END IF;

An  advantage of that approach is that you could also do more
complicated things that are never going to work with any number of
IF-EXISTS clauses. For example, imagine you want to rename foo to bar
and bar to baz, unless that's been done already. Well with these
functions you can just do this:

IF pg_table_has_column('mytab', 'foo') THEN
    ALTER TABLE mytab RENAME bar TO baz;
    ALTER TABLE mytab RENAME foo TO bar;
END;

There's no way to get there with just IF EXISTS.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

> On 1 Apr 2022, at 02:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:

>> At present the project seems to largely consider the IF EXISTS/IF NOT
>> EXISTS features to have been largely a mistake and while removing it is not
>> going to happen the desire to change or extend it is not strong.
>
> That might be an overstatement.

ISTR that patches which have been rejected have largely added support for the
syntax for the sake of adding support for the syntax, not because there was a
need or usecase for it.  When the patch is accompanied with an actual usecase
it's also easier to reason about.

Now, the usecase of "I wanted to to start working on PostgreSQL and this seemed
like a good first patch" is clearly also very important.

--
Daniel Gustafsson        https://vmware.com/