Re: [WIP] ALTER COLUMN IF EXISTS
On Thu, Mar 31, 2022 at 4:39 PM Bradley Ayers 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. > > 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. > 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. If you want to make a go at this I would suggest not writing any new code at first but instead take inventory of what is already implemented, how it is implemented, what gaps there are, and proposals to fill those gaps. Write the theory/rules that we follow in our existing (or future) implementation of this idempotence feature. Then get agreement to implement the proposals from enough important people that a well-written patch would be considered acceptable to commit. I don't know if any amount of planning and presentation will convince everyone this is a good idea in theory, let alone one that we want to maintain while the author goes off to other projects (this being your first patch that seems like a reasonable assumption). I can say you have some community support in the endeavor but, and maybe this is biasing me, my (fairly recent) attempt at what I considered bug-fixing in this area was not accepted. On that note, as part of your research, you should find the previous email threads on this topic (there are quite a few I am sure), and make you own judgements from those. Aside from it being my opinion I don't have any information at hand that isn't in the email archives. David J.
Re: [WIP] ALTER COLUMN IF EXISTS
"David G. Johnston" writes: > On Thu, Mar 31, 2022 at 4:39 PM Bradley Ayers > 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
Re: [WIP] ALTER COLUMN IF EXISTS
On Thu, Mar 31, 2022 at 8:02 PM David G. Johnston
wrote:
> 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.
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
Re: [WIP] ALTER COLUMN IF EXISTS
> On 1 Apr 2022, at 02:30, Tom Lane wrote: > "David G. Johnston" 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/
