Re: [HACKERS] Idempotency for all DDL statements

[Craig Ringer]

On 18 October 2016 at 10:26, Peter Eisentraut
 wrote:
> On 10/17/16 2:05 PM, Kenaniah Cerny wrote:
>> Could I request DDL idempotency as an addition to the development roadmap?
>
> There is not really a roadmap, but I think there is general interest in
> this.  If you want to make it happen faster, however, you will need to
> start coding it yourself.

Yeah. The way to make that happen is to start submitting patches.

There have been a number of "IF NOT EXISTS" options added lately so
take a look at those patches for guidance.

Be aware that not all cases are simple, and some community members are
less than fond of the whole concept so you'll need to do some
convincing.

I'm ambivalent myself. I'm certain that every DROP should support it.
I'm less sure about CREATE and ALTER, since assuming it is in fact
idempotent is questionable at best. All that IF NOT EXISTS does is
skip acting if the target already exists. If the target exists but is
entirely different to what would result from running the CREATE
statement it silently does nothing.

CREATE TABLE fred (id integer);

CREATE TABLE IF NOT EXISTS fred(id text);

The latter statement is not truly idempotent. To achieve that, we'd
have to implicitly transform it into an ALTER and magically figure out
what the correct way to preserve/transform user data is, which is not
possible since there are so many possibilities and there's no way to
choose between them. Second-best would be to detect that the target
exists, but does not match the results of executing the current
statement and ERROR appropriately.

Personally I think use of CREATE / ALTER ... IF NOT EXISTS is almost
always a mistake, and you should instead use a schema versioning
system that handles schema changes in a managed way. But I'm still in
favour of IF NOT EXISTS for convenience, easy development, and user
friendliness, though they need warnings in the docs really.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Idempotency for all DDL statements

[Peter Eisentraut]

On 10/17/16 2:05 PM, Kenaniah Cerny wrote:
> Could I request DDL idempotency as an addition to the development roadmap?

There is not really a roadmap, but I think there is general interest in
this.  If you want to make it happen faster, however, you will need to
start coding it yourself.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Idempotency for all DDL statements

[Kenaniah Cerny]

Hello everyone. This is my first time posting in -hackers, so please keep
this in mind.

I would like to formally request the addition of "IF EXISTS", "IF NOT
EXISTS", "OR REPLACE", and any other CINE variants to DDL statements that
currently do not feature an idempotent construct.

The lack of an idempotent option in statements such as "CREATE TRIGGER" for
example is particularly painful when performing upgrades on sizable
schemas.

I understand that there may be some difficulties in providing this to
various database objects. That said, I believe IMHO that this would be a
huge win for the community at large. Could I request DDL idempotency as an
addition to the development roadmap?

Thanks,

Kenaniah