Details for planned template0/template1 change

Tom Lane wrote:

1. pg_database will have two new boolean columns, dbistemplate and
dballowconn --- if dballowconn is false then no new connections to that
database will be accepted. (An even better solution would be to make
a "dbisreadonly" attribute, but implementing such a thing would be a
bigger change than I have time for now.)

I like the dballowconn, but an eventual dbreadonly might prove useful
for static data sources.

2. CREATE DATABASE will accept a new option "TEMPLATE = name", where the
name is the name of an existing database to be cloned. To clone

Reasonable implementation.

5. pg_dump should ignore objects with OID <= lastsysoid of the target
database. pg_dumpall should ignore databases not marked dballowconn,
and should save/restore the setting of dbistemplate. All databases
created by a pg_dumpall script will be created WITH TEMPLATE template0.

Finally, a good backup of user data in template1. I've documented the
fact that template1 data wasn't dumpable in the RPM README, as of last
year -- I'll be rather happy to remove that paragraph. :-) Since
upgrading involves dump/restore, and the dump of template1 user data
didn't work, template1 data was unupgradeable.

dbistemplate is intended to avoid hard-wiring any notion about which
database(s) can be the template for CREATE DATABASE. I started out
intending to allow templates named "templateSOMETHING", but a flag
column seems a better idea.

The less that is hardwired, the better. That is, after all, part of the
Postgres Paradigm.

restriction like that looks like too much work for 7.1. I think that
a dballowconn flag might have other uses anyway, such as temporarily
disallowing new connections to a database you are doing major work in.

This could potentially be a very useful feature -- I know of more than
one instance I could have used such (instead of stopping postmaster,
then restarting on another port in order to do major work of various
sorts).

With this scheme, template1 is actually not special except for being
the default CREATE DATABASE template and the default connection target
for various scripts like createdb. You could drop it and recreate it
from template0, if you were so inclined --- this could be a recovery
method if template1 got messed up.

That is a great side-effect. Now, if there were a way to initdb just
template0, leaving everything else in place, then rebuilding template1
-- of course, a similar ability is there now, but the two-stage initdb
this implies could make pg_upgrade work smoother, in cases where the
system catalogs are the only change from one major version to the next.

All in all, sounds like you;ve done your homework again, Tom.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Lamar Owen <lamar.owen@wgcr.org> writes:

That is a great side-effect. Now, if there were a way to initdb just
template0, leaving everything else in place, then rebuilding template1
-- of course, a similar ability is there now, but the two-stage initdb
this implies could make pg_upgrade work smoother, in cases where the
system catalogs are the only change from one major version to the next.

I'm missing something --- I don't see how this affects pg_upgrade one
way or the other, except of course that it should be prepared to cope
with user data in template1 (not sure if it does or not now).

pg_upgrade won't be usable for the 7.1 transition anyway, because of WAL
changes (page header format is changing). I dunno whether it will be
usable at all under WAL --- Vadim will have to comment on that.

regards, tom lane

At 11:48 13/11/00 -0500, Tom Lane wrote:

5. pg_dump should ignore objects with OID <= lastsysoid of the target
database.

I think it should ignore objects with OID <= lastsysoid of template0; then
when it does a restore, it should use 'WITH TEMPLATE template0'. At least
this should be an option.

I think that
a dballowconn flag might have other uses anyway, such as temporarily
disallowing new connections to a database you are doing major work in.

eg. while a restore script is running...

BTW: are there any nice ways to:

- set the flag
- kick current users off without corrupting memory

(ie. shutdown a single database).

lastsysoid will probably always be the same for all databases in an
installation, since they'll all inherit the value from template0 or
template1. However, there is the possibility of changing it to exclude
some items from backup, so I'm continuing to treat it as a per-database
value.

Sounds fine; but we need to use lastsysoid of template0 in pg_dump. Consider:

- add function foo() to template1, OID=100000
- create db1 (inherits foo())
- update function foo() in template1, and also update it in db1. New OID >
100000
- dump db1 - will dump foo().
- restore db1 using template1->crash

However, dumping based oi lastsysoid of template0, then restoring based on
template0 works...

Otherwise sounds good!

----------------------------------------------------------------
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 |/

Philip Warner <pjw@rhyme.com.au> writes:

5. pg_dump should ignore objects with OID <= lastsysoid of the target
database.

I think it should ignore objects with OID <= lastsysoid of template0; then
when it does a restore, it should use 'WITH TEMPLATE template0'.

Restores will be with template template0 anyway, but it seems to me that
dump should look to lastsysoid of the database being dumped (sorry if my
term "target database" confused you). I'm not sure whether there's any
value in altering lastsysoid of individual databases to suppress dumping
of some items therein, but it can't hurt to retain the flexibility.

BTW: are there any nice ways to:
(ie. shutdown a single database).

Not at the moment.

Sounds fine; but we need to use lastsysoid of template0 in pg_dump. Consider:

- add function foo() to template1, OID=100000
- create db1 (inherits foo())
- update function foo() in template1, and also update it in db1. New OID >
100000
- dump db1 - will dump foo().
- restore db1 using template1->crash

No, because you aren't ever going to restore db1 using template1 as
template. That's specifically what template0 is for.

regards, tom lane

Tom Lane wrote:

Lamar Owen <lamar.owen@wgcr.org> writes:

That is a great side-effect. Now, if there were a way to initdb just
template0, leaving everything else in place, then rebuilding template1

I'm missing something --- I don't see how this affects pg_upgrade one
way or the other, except of course that it should be prepared to cope
with user data in template1 (not sure if it does or not now).

Maybe I spoke too soon....

pg_upgrade won't be usable for the 7.1 transition anyway, because of WAL
changes (page header format is changing). I dunno whether it will be
usable at all under WAL --- Vadim will have to comment on that.

Of course, the upgrade from 7.0 to 7.1 involves a physical on disk
format change (implying pg_upgrade's uselessness in doing its job
there). You know, our version numbers aren't at all consistent WRT disk
format. ISTM that 6.5 should have been 7.0 due to its format change,
and 7.1 should be 8.0.

Changing from 6.5 to 7.0 is less of a format change than 6.4 to 6.5 or
7.0 to 7.1.

But, what's in a version number.... :-) They don't _have_ to be
consistent, really.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

At 12:38 13/11/00 -0500, Tom Lane wrote:

I think it should ignore objects with OID <= lastsysoid of template0; then
when it does a restore, it should use 'WITH TEMPLATE template0'.

Restores will be with template template0 anyway, but it seems to me that
dump should look to lastsysoid of the database being dumped

I may have been a little unclear in my message (it was late), or I may have
misundertsood your response (it's now a little early), but I don't think
this will work; dump & restore must use the same baseline, and I think that
has to be template0.

----------------------------------------------------------------
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 |/

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?

regards, tom lane

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?

There's no such think as pg_backup, but pg_restore should work if pg_dump
is working. I'll have a look...

----------------------------------------------------------------
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 |/

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 |/

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

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 |/

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

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 |/

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 |/