Re: Patch to add support of "IF NOT EXISTS" to others "CREATE" statements

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> * Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
>> Except if I am missing something, the second query means that it is
>> going to replace the existing user test with a new one, with the
>> settings specified in the 2nd query, all being default values. As the
>> default for login is NOLOGIN, the user test should not be able to log
>> in the server.

> That's more-or-less the behavior we're trying to work out. I've been
> meaning to go back and look at what we've been doing with the existing
> COR cases and just haven't gotten to it yet. The pertinent question
> being if we assume the user intended for the values not specified to be
> reset to their defaults, or not.

Yes, it has to be that way. The entire argument for COR hinges on the
assumption that if you execute the statement, and it succeeds, the
properties of the object are equivalent to what they'd be if there had
been no predecessor object. Otherwise it's just the same as CINE,
which offers no guarantees worth mentioning about the object's
properties.

I'm willing to bend that to the extent of saying that COR leaves in place
subsidiary properties that you might add *with additional statements* ---
for example, foreign keys for a table, or privilege grants for a role.
But the properties of the role itself have to be predictable from the COR
statement, or it's useless.

> Where this is a bit more interesting is in the case of sequences, where
> resetting the sequence to zero may cause further inserts into an
> existing table to fail.

Yeah. Sequences do have contained data, which makes COR harder to define
--- that's part of the reason why we have CINE not COR for tables, and
maybe we have to do the same for sequences. The point being exactly
that if you use CINE, you're implicitly accepting that you don't know
the ensuing state fully.

regards, tom lane