Re: pg_restore -1 vs -C and -c

On 12 jan 2009, at 17.46, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Magnus Hagander <magnus@hagander.net> writes:
>> It should be possible to make it compatible with -C by moving the  
>> CREATE
>> DATABASE command to outside of the transaction. I have only had a  
>> quick
>> look at the code wrt how much work this would be. One thing that  
>> hit me
>> quickly: do we support multiple CREATE DATABASE statements in a  
>> single
>> dump file? I think not, but the format seems to allow it? If not, it
>> should be "fairly simple" to do.
>
> We don't, and a single transaction couldn't restore into multiple
> databases anyway.  So in principle there's no reason we shouldn't  
> allow
> it to do the CREATE DATABASE, switch into the new DB, and then start  
> the
> transaction.
>
> However, one of the properties -1 is supposed to have is that any
> failure aborts the whole restore; it's not immediately clear how to
> preserve that with CREATE DATABASE issued separately.

Good point. Declare as incompatible it is, then :) it's not like it's  
hard do create the database before restoring.


> Also you need to think about whether this might impact pg_dumpall.

Dumps from pg_dumpall aren't restored through pg_restore...


>
>> As for -c, the solution would be to issue DROP IF EXISTS  
>> statements. Is
>> there any particular reason why we don't?
>
> I think we did that to avoid damaging portability and backwards
> compatibility of the dump files.  The backwards compatibility argument
> is pretty weak by now, but the "it's not standard SQL" argument still
> has force.
>

IIRC the drop statements are generated by pg_restore and not stored in  
the archive. So we could do the if exists by default and have a switch  
to turn it off for a compatible dump, perhaps?

/Magnus