Re: Idempotency for all DDL statements

On 18 October 2016 at 10:26, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> 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