At 13:39 14/11/00 -0500, Tom Lane wrote:
>I've committed the template0/template1 changes we were discussing
>earlier.  Plain pg_dump and pg_dumpall are changed to behave properly,
>but I didn't touch pg_backup or pg_restore; can you deal with those?

I still think that pg_dump needs to use the lastoid in template0 - did you
fail to implement this because you disagree, or because you think it should
use the current db lastsysoid?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 15 Nov 2000 13:54:21 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Details for planned template0/template1 change 
Philip Warner <pjw@rhyme.com.au> writes:
> I still think that pg_dump needs to use the lastoid in template0 - did you
> fail to implement this because you disagree, or because you think it should
> use the current db lastsysoid?

I think it should use the current DB's lastsysoid, which is how I
left the code.

Given the present backend coding, all the DBs in an installation will
have the same lastsysoid as template0 anyway, barring manual
intervention.  The only reason they'd be different is if the dbadmin
deliberately changed one in order to prevent pg_dump from dumping part
of that database.  Now offhand I don't see a good *reason* for someone
to do that, but I don't see any reason to make it impossible to do it,
either.

			regards, tom lane

Tue, 14 Nov 2000 23:20:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Details for planned template0/template1 change 
At 23:20 14/11/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> I still think that pg_dump needs to use the lastoid in template0 - did you
>> fail to implement this because you disagree, or because you think it should
>> use the current db lastsysoid?
>
>I think it should use the current DB's lastsysoid, which is how I
>left the code.
>
>Given the present backend coding, all the DBs in an installation will
>have the same lastsysoid as template0 anyway, barring manual
>intervention.

Not the way the current 'CREATE DATABASE' code works - remember the changes
to set the OID at create time? AFAICT, that's still there.

If we want dump files to be portable, then we can not do anything other
than backup & restore relative to template0. If we *do* backup relative to
the template database used to create the DB, then we should be recording
the template name in the backups as well, but this makes things even less
portable.

You never addressed the scenario I described in an earlier mail:

- create function template1.foo
- create db mydb from template1
- drop mydb.foo
- create a new mydb.foo

Both pg_dump & pg_dumpall will now dump the foo function, which means that
they both have to be restored against template0, not 1. If the function
were never dropped, then they would both need to be restored against
template1. I really don't think this is ideal.

There are only two solutions I can think of:

- Set datlastsysoid to the same value as in template1 for all databases

- Always dump (perhaps unless asked otherwise) using datlastsysoid of
template1.

Personally I would prefer the second.


>The only reason they'd be different is if the dbadmin
>deliberately changed one in order to prevent pg_dump from dumping part
>of that database.  Now offhand I don't see a good *reason* for someone
>to do that, but I don't see any reason to make it impossible to do it,
>either.

The second solution above does allow this, but I am not even sure the
option should be there. It is likely to cause more confusion & trouble than
it's worth. It will probably never function the way people expect
(especially in the absence of ALTER FUNCTION), and pg_dump/restore already
provides a cleaner method to remove specific items at restore time.

Going for what looks like a solution that will never work properly, when we
already have a potential better solution in mind (the 'I am a system
object' flag), seems ill-advised.




----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 15 Nov 2000 15:45:57 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Details for planned template0/template1 change 
Philip Warner <pjw@rhyme.com.au> writes:
>> Given the present backend coding, all the DBs in an installation will
>> have the same lastsysoid as template0 anyway, barring manual
>> intervention.

> Not the way the current 'CREATE DATABASE' code works - remember the changes
> to set the OID at create time? AFAICT, that's still there.

Look again.  lastsysoid is now inherited from the database being cloned.

AFAICT all your subsequent comments are based on that
misunderstanding...

			regards, tom lane

Tue, 14 Nov 2000 23:48:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Details for planned template0/template1 change 
At 23:48 14/11/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>>> Given the present backend coding, all the DBs in an installation will
>>> have the same lastsysoid as template0 anyway, barring manual
>>> intervention.
>
>> Not the way the current 'CREATE DATABASE' code works - remember the changes
>> to set the OID at create time? AFAICT, that's still there.
>
>Look again.  lastsysoid is now inherited from the database being cloned.

It looks to me like that was the intent of the code; but there is still:

        tuple = heap_formtuple(pg_database_dsc, new_record, new_record_nulls);

        tuple->t_data->t_oid = dboid;   /* override heap_insert's OID
selection */

        heap_insert(pg_database_rel, tuple);

which I think stuffs up everything, since dboid was allocated earlier in
the same call. But I agree, the code looks like it tries to do what you said.

I could have missed something, though.


>AFAICT all your subsequent comments are based on that
>misunderstanding...

Pretty much. Sorry. Is there a smiley for embarrasment?


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 15 Nov 2000 15:59:38 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Details for planned template0/template1 change 
At 15:59 15/11/00 +1100, Philip Warner wrote:
>
>It looks to me like that was the intent of the code; but there is still:
>
...
>
>Pretty much. Sorry. Is there a smiley for embarrasment?
>

I really need that smiley now. Just saw my mistake.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Wed, 15 Nov 2000 16:03:05 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Details for planned template0/template1 change 