Re: create if not exists (CINE) - Mailing list pgsql-hackers

On Wed, May 6, 2009 at 9:04 AM, Dawid Kuroczko <qnex42@gmail.com> wrote:
> On Wed, May 6, 2009 at 7:22 AM, Asko Oja <ascoja@gmail.com> wrote:
>> It was just yesterday when i wondering why we don't have this feature (i was
>> trying to use it and it wasn't there :).
>> The group of people who think it's unsafe should not use the feature.
>> Clearly this feature would be useful when managing large amounts of servers
>> and would simplify our release process.
>>
>> On Wed, May 6, 2009 at 5:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [...]
>>> Yes, I did.  I'm not any more convinced than I was before.  In
>>> particular, the example you give is handled reasonably well without
>>> *any* new features, if one merely ignores "object already exists"
>>> errors.
>>
>> It sounds pretty amazing. Ignoring errors as a suggested way to use
>> PostgreSQL.
>> We run our release scripts inside transactions (with exception of concurrent
>> index creation). So if something unexpected happens we are left still in
>> working state.
>> PostgreSQL ability to do DDL changes inside transaction was one of biggest
>> surprises/improvements when switching from Oracle. Now you try to bring us
>> down back to the level of Oracle :)
>
> Hm, You can do it easily today with help of PL/PgSQL, say like this:
>
> CREATE OR REPLACE FUNCTION foo_upgrade() RETURNS VOID AS $$
> BEGIN
>  BEGIN
>    CREATE TABLE foo(i int, t text);
>  EXCEPTION
>    WHEN duplicate_table THEN RAISE NOTICE 'Table foo already exists';

That's kinda like, when parallel parking, determining that it's time
to pull forward when you hit the car behind you.  If you are going
through the trouble of making a function to do schema upgrades, you
would definitely want to query the information schema first to
determine if you needed to create table, add columns, etc.
Subtransactions should be used to handled _unexpected_ errors.

> Personally I don't like 'CREATE IF NOT EXISTS'.  I find it 'messy'. :-)
>
> What I wish PostgreSQL would have is ability to do "conditional
> rollback to savepoint".
> This way one could write a PostgreSQL SQL script that would contain conditional
> behaviour similar to exceptions handling above.  For instance backend could
> handle sort of EXCEPTION clause:

I've griped endlessly about this...I think the 'savepoint' command is
worthless without additional functionality.  In the early drafts of
subtransactions, this wasn't the case...you could push and pop
transactions without using plpgsql.  I don't know how to fix the
current behavior though...maybe:

begin;
savepoint x;
<stuff>
recover;
commit;

Where recover rolls back to last substransaction if there's an error
else its a NOP.  (this idea may have already failed to passed
muster...i've floated several ideas over the years).  With proper
subtransaction support in sql, $SUBJECT wouldn't be necessary, because
we could use the car-smack method (you could make the same case for
drop..if exists which we already have).

merlin