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

On Tue, Apr 1, 2014 at 1:34 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Michael Paquier (michael(dot)paquier(at)gmail(dot)com) wrote:
>> On Tue, Apr 1, 2014 at 7:28 AM, Fabrízio de Royes Mello
>> <fabriziomello(at)gmail(dot)com> wrote:
>> > Think about the statements below:
>> >
>> > CREATE ROLE test NOLOGIN;
>> > CREATE OR REPLACE ROLE test;
>> >
>> > If we execute the statements above the result should be the role 'test' can
>> > login. Correct?
>
>> 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.

For example, on views, COR fails if it the new view does not contain
the old list of columns, same order and same data type, and can be
completed with new columns. The ownership of the view remains the same
as well. For functions, the argument types and return type need to
remain the same. As I understand, COR are useful because they
guarantee that no objects depending on it would be broken and are made
when a user wants to extend an object or redefine its internals. For
example, we should not allow that IMO:
CREATE ROLE foo LOGIN REPLICATION; -- ok
CREATE OR REPLACE ROLE foo NOREPLICATION; --error
Because with the 2nd query replication would break replication.

For roles, I am not completely sure how you would to that, but I would
imagine that you would need to keep track of all the parameters are
using non-default settings and specified directly by the user in
CREATE ROLE/USER. Then COR would fail if user tries to change some of
those parameters to values that do not map the non-default ones in the
first query (by tracking them in a new pg_authid column, berk, without
thinking about complications induced by IN ROLE, IN GROUP and
friends...). Perhaps I am thinking too much though.

> The pertinent question being if we assume the user intended for the
> values not specified to be reset to their defaults, or not.
Isn't it what ALTER ROLE aims at?

> 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. Of course, were a user to use 'drop if exists'
> followed by a 'create', they'd get the same behavior.. However, 'create
> if not exists' would leave the sequence alone, but in a potentially
> unknown state.
You could face failures on a serial column as well by changing the
increment sign of its sequence with a COR, so you would need more
guarantees than a min value.
Regards,
--
Michael