We've hacked up pg_dump so that it won't dump objects inherited from
template1.  Unfortunately I have realized there are a couple of serious
problems:

1. What if the inherited object is a table or a sequence?  Its state may
no longer be the same as it was in template1 (eg, a table may contain
more or different rows than it did when copied from template1).  It
seems a perfectly natural use of the template1 functionality to store,
say, a table definition in template1 and then add rows to it in
inherited databases --- that's exactly what the system does with
pg_proc, for example.

2. For that matter, even function definitions might change once we
support ALTER FUNCTION, which we surely will someday.  Or, template1
might contain data which was not present when some other database was
created.  In this case, reloading template1 first will not reproduce
the prior state of that database.

3. What if the OID counter wraps around?  I've been telling people
that's not a fatal scenario ... but it sure is if pg_dump silently omits
data on the basis of ordered OID comparisons.

A solution that would work for pg_dumpall is to dump all the user items
in each database and dump template1 *last*.  This won't help much for
piecemeal pg_dump and restore, however.

Thoughts?  At the moment I'm afraid that the functionality we have is
worse than the way prior versions behaved --- not least because anyone
who was putting user data in template1 has probably gotten used to the
prior behavior.  Maybe we should give up the whole idea of user data
in template1.

			regards, tom lane

Thu, 02 Nov 2000 19:35:26 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Unhappy thoughts about pg_dump and objects inherited from template1
At 19:35 2/11/00 -0500, Tom Lane wrote:
>We've hacked up pg_dump so that it won't dump objects inherited from
>template1.  Unfortunately I have realized there are a couple of serious
>problems:
>
>1. What if the inherited object is a table or a sequence?
>2. For that matter, even function definitions might change 

The only solution I can think of for this would be to use lastsysoid from
template1; this is the value set when initdb runs.


>3. What if the OID counter wraps around?

Can the code that wraps the OID restart it at 'select max(lastsysoid) from
pg_database'? Is that too complex?


>Maybe we should give up the whole idea of user data
>in template1.

I'm leaning a little this way, but local mods are useful.

There's also a problem if a db drops a function created by template1, then
creates its own version (eg. via (mythical) ALTER FUNCTION). If we restore
template1 then the db, we get a problem.

Perhaps, for pg_dumpall:

1. Restore vanilla template1 (this is probably not necessary?)
2. Restore all DBs (dumped using template1->lastsysoid)
3. Restore local mods to template1


And for single-db dump we dump using db->lastsysoid (the assumption being
that the DB will be restored in the 'right' template1 context). This would
be the default behaviour of pg_dump.

This requires a way of asking pg_dump to use a 'system' (ie. template1) or
'local' (ie. from the specific database) lastsysoid...( --last-oid
{S,D}/-L{S,D}). I think this fixes it, but perhaps I'm hallucinating.

Does this sound OK?


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

Fri, 03 Nov 2000 12:00:48 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Philip Warner <pjw@rhyme.com.au> writes:
>> 1. What if the inherited object is a table or a sequence?

> The only solution I can think of for this would be to use lastsysoid from
> template1; this is the value set when initdb runs.

How does that help?  It won't tell you anything about updated or deleted
rows, nor about sequence advances, nor ALTER FUNCTION changes.  You
could detect inserted rows, and that's about it.

>> 3. What if the OID counter wraps around?

> Can the code that wraps the OID restart it at 'select max(lastsysoid) from
> pg_database'? Is that too complex?

What if lastsysoid is MAXINT minus just a little?  Not very workable,
even if it were possible for the OID counter to work that way, which
I don't think it is (the OID allocator is way too low-level to go off
invoking arbitrary queries with safety).

			regards, tom lane

Thu, 02 Nov 2000 20:12:33 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 20:12 2/11/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>>> 1. What if the inherited object is a table or a sequence?
>
>> The only solution I can think of for this would be to use lastsysoid from
>> template1; this is the value set when initdb runs.
>
>How does that help?  It won't tell you anything about updated or deleted
>rows, nor about sequence advances, nor ALTER FUNCTION changes.  You
>could detect inserted rows, and that's about it.

In template1, lastsysoid is based on entries in pg_description. So it is
very close to restoring the original behaviour of pg_dump. I agree it won't
fix everything, but it will ensure it is no worse than before.

In the longer term, OID wrapping will be a problem for *any* oid-based
restoration scheme, as will ALTER FUNCTION. This is true for old & new
pg_dump alike.

The only real solution is to go away from OID-based restore, but I can't
see how. An 'add-or-update' method of restoration for everything, including
system tables, would be a disaster for version upgrades.

Any suggestions?


>> Can the code that wraps the OID restart it at 'select max(lastsysoid) from
>> pg_database'? Is that too complex?
>
>(the OID allocator is way too low-level to go off
>invoking arbitrary queries with safety).

Thought that might be the case :-(


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

Fri, 03 Nov 2000 12:24:56 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Tom Lane wrote:
> We've hacked up pg_dump so that it won't dump objects inherited from
> template1.  Unfortunately I have realized there are a couple of serious
> problems:
>
> 1. What if the inherited object is a table or a sequence?  Its state may
> no longer be the same as it was in template1 (eg, a table may contain
> more or different rows than it did when copied from template1).  It
> seems a perfectly natural use of the template1 functionality to store,
> say, a table definition in template1 and then add rows to it in
> inherited databases --- that's exactly what the system does with
> pg_proc, for example.
>
> 2. For that matter, even function definitions might change once we
> support ALTER FUNCTION, which we surely will someday.  Or, template1
> might contain data which was not present when some other database was
> created.  In this case, reloading template1 first will not reproduce
> the prior state of that database.
>
> 3. What if the OID counter wraps around?  I've been telling people
> that's not a fatal scenario ... but it sure is if pg_dump silently omits
> data on the basis of ordered OID comparisons.
>
> A solution that would work for pg_dumpall is to dump all the user items
> in each database and dump template1 *last*.  This won't help much for
> piecemeal pg_dump and restore, however.
>
> Thoughts?  At the moment I'm afraid that the functionality we have is
> worse than the way prior versions behaved --- not least because anyone
> who was putting user data in template1 has probably gotten used to the
> prior behavior.  Maybe we should give up the whole idea of user data
> in template1.

    FWIW,  what  about having another "template0" database, where
    nobody can add user data. Initially, template0 and  template1
    are identically.  CREATE DATABASE get's a new switch (used by
    the pg_dump output) that tells to create it from the  vanilla
    template0  DB  (generalized, so someone can setup a couple of
    template<n>'s)  and  all  objects  inherited  from  template1
    (those not in template0) are regularly dumped per database.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Tue, 7 Nov 2000 14:04:11 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 14:04 7/11/00 -0500, Jan Wieck wrote:
>> Thoughts?  At the moment I'm afraid that the functionality we have is
>> worse than the way prior versions behaved --- not least because anyone
>> who was putting user data in template1 has probably gotten used to the
>> prior behavior.  Maybe we should give up the whole idea of user data
>> in template1.
>
>    FWIW,  what  about having another "template0" database, where
>    nobody can add user data. Initially, template0 and  template1
>    are identically.  CREATE DATABASE get's a new switch (used by
>    the pg_dump output) that tells to create it from the  vanilla
>    template0  DB  (generalized, so someone can setup a couple of
>    template<n>'s)  and  all  objects  inherited  from  template1
>    (those not in template0) are regularly dumped per database.

All pg_dump really needs is the abilty to ask for a 'vanilla' database from
'CREATE DATABASE' or createdb. It can use lastsysoid for template1/0 do
dump all database definitions. Any altered system objects will not be
dumped, which is probably OK (and may even be the Right Thing).

The command to create the new database needs to ask for a vanilla database
somehow, but extending the SQL doesn't seem like a good idea. *Maybe* we
can use a new 'set' command to define the template database for the current
session:

    set pg_template <db-name>
    create database...

or

    createdb --template=<db-name>

It would also be good to allow some kind of installation-wide default
template (not necessarily template1/0), which is overridden temporarily by
the 'set' command.

If we can do this, then we create template0 & 1 in the same way we create
template1 now, then set template1 as the default template.


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

Thu, 09 Nov 2000 00:33:21 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
> At 14:04 7/11/00 -0500, Jan Wieck wrote:
>> FWIW,  what  about having another "template0" database, where
>> nobody can add user data. Initially, template0 and  template1
>> are identically.  CREATE DATABASE get's a new switch (used by
>> the pg_dump output) that tells to create it from the  vanilla
>> template0  DB  (generalized, so someone can setup a couple of
>> template<n>'s)  and  all  objects  inherited  from  template1
>> (those not in template0) are regularly dumped per database.

I like that a lot.  Solves the whole problem at a stroke, and even
adds some extra functionality (alternate templates).

Do we need an actual enforcement mechanism for "don't modify template0"?
I think we could live without that for now.  If you're worried about it,
one way would be to not allow connections of any sort to template0...
in fact template0 needn't be a real database at all, just a $PGDATA/base
subdirectory with no pg_database entry.  initdb would set it up via
cp -r from template1, and thereafter it'd just sit there.

Philip Warner <pjw@rhyme.com.au> writes:
> The command to create the new database needs to ask for a vanilla database
> somehow, but extending the SQL doesn't seem like a good idea.

Why not?  CREATE DATABASE isn't a standard command in the first place,
and it's got a couple of even-less-standard options already.  I like

	CREATE DATABASE foo WITH TEMPLATE 'template0'

better than a SET command.

> It would also be good to allow some kind of installation-wide default
> template (not necessarily template1/0),

Maybe, but let's not go overboard here.  For one thing, where are you
going to keep that default setting?  I think a hard-wired default of
template1 is a perfectly good choice.

			regards, tom lane

Wed, 08 Nov 2000 10:15:08 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 10:15 8/11/00 -0500, Tom Lane wrote:
>I like
>
>	CREATE DATABASE foo WITH TEMPLATE 'template0'
>
>better than a SET command.

Just seems like we'd be forcing non-standard syntax on ourselves when/if
CREATE DATABASE becomes CREATE SCHEMA; I would assume that the two
statements would become synonymous? Since this code is only for pg_dump,
polluting CREATE DATABASE even further seems like a bad idea. No big deal,
though. 

[Minor aside: would 'FROM TEMPLATE' be better?]

Question: if I issue a "CREATE DATABASE foo WITH TEMPLATE 'my-favorite-db'"
will I just get a copy of the specified database, including data?


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

Thu, 09 Nov 2000 02:48:50 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
Philip Warner <pjw@rhyme.com.au> writes:
> Just seems like we'd be forcing non-standard syntax on ourselves when/if
> CREATE DATABASE becomes CREATE SCHEMA; I would assume that the two
> statements would become synonymous?

No, I don't think so --- we already have WITH LOCATION and WITH
ENCODING, neither of which look like schema-level properties to me.

> [Minor aside: would 'FROM TEMPLATE' be better?]

WITH is already embedded in the CREATE DATABASE syntax.

> Question: if I issue a "CREATE DATABASE foo WITH TEMPLATE 'my-favorite-db'"
> will I just get a copy of the specified database, including data?

If we allow it, that's what would happen.  Seems like a potential
security hole though ... should we restrict the set of clonable
templates somehow?

It occurs to me that the current implementation of CREATE DATABASE
assumes that no changes are actively going on in the cloned database;
for example, you'd miss copying any pages that are sitting in dirty
buffers in shared memory.  So trying to copy an active database this
way is a recipe for trouble.  Probably better restrict it to identified
template databases.  Maybe only allow cloning from DBs that are named
templateNNN?

			regards, tom lane

Wed, 08 Nov 2000 10:56:23 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 10:56 8/11/00 -0500, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
>> Just seems like we'd be forcing non-standard syntax on ourselves when/if
>> CREATE DATABASE becomes CREATE SCHEMA; I would assume that the two
>> statements would become synonymous?
>
>No, I don't think so --- we already have WITH LOCATION and WITH
>ENCODING, neither of which look like schema-level properties to me.

CREATE SCHEMA supports character set specification, so I'd guess 'WITH
ENCODING' will apply in some form. It also support a 'schema path name',
which may or may not map to locations.


>> Question: if I issue a "CREATE DATABASE foo WITH TEMPLATE 'my-favorite-db'"
>> will I just get a copy of the specified database, including data?
>
>If we allow it, that's what would happen.  Seems like a potential
>security hole though ... should we restrict the set of clonable
>templates somehow?

It would be nice to have a 'supported' COPY DATABASE (which is what we're
talking about, really), so I'd vote for being able to use any DB as a
template, if possible.

Can we restrict the command to databases that have only one active backend?
Or add an 'istemplate' flag set in pg_database? I don't really like relying
on specific name formats, if we can avoid it.


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

Thu, 09 Nov 2000 03:06:30 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
On Thu, Nov 09, 2000 at 02:48:50AM +1100, Philip Warner wrote:
> At 10:15 8/11/00 -0500, Tom Lane wrote:
> >I like
> >
> >	CREATE DATABASE foo WITH TEMPLATE 'template0'
> >
> >better than a SET command.
> 
> Just seems like we'd be forcing non-standard syntax on ourselves when/if
> CREATE DATABASE becomes CREATE SCHEMA; I would assume that the two
> statements would become synonymous? Since this code is only for pg_dump,
> polluting CREATE DATABASE even further seems like a bad idea. No big deal,
> though. 

Nope, we'll still have databases, with schema inside them. Schema are
essentially a logical namespace, while a database encompasses all the data
objects accessible to one session (via standard SQL), i.e. one backend.

As Tom said, creating and maintaining those are 'implementation defined'
in the standard.

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.

Wed, 8 Nov 2000 10:11:17 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Philip Warner <pjw@rhyme.com.au> writes:
> It would be nice to have a 'supported' COPY DATABASE (which is what we're
> talking about, really), so I'd vote for being able to use any DB as a
> template, if possible.

> Can we restrict the command to databases that have only one active backend?

No active backends would be more like it.  The problem here is that
there's a race condition much like the one for DROP DATABASE --- there
may be no one connected when you look, but that's no guarantee someone
can't connect right after you look.

We're already overdue for beta, so I really don't want to start
designing/implementing a generalized COPY DATABASE.  (We're not
officially in feature freeze yet, but inventing new features off the
top of our heads doesn't seem like the thing to be doing now.)
I'd like to see a proper fix for the inherited-data problem, though,
since that's clearly a bug in an existing feature.

> Or add an 'istemplate' flag set in pg_database? I don't really like relying
> on specific name formats, if we can avoid it.

That's reasonable I guess.

Do we still need the lastsysoid column in pg_database if we do things
this way?  Seems like what you really want is to suppress all the
objects that are in template0, so you really only need one lastsysoid
value, namely template0's.  The other entries are useless AFAICS.

			regards, tom lane

Wed, 08 Nov 2000 11:13:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 11:13 8/11/00 -0500, Tom Lane wrote:
>
>Do we still need the lastsysoid column in pg_database if we do things
>this way?  Seems like what you really want is to suppress all the
>objects that are in template0, so you really only need one lastsysoid
>value, namely template0's.  The other entries are useless AFAICS.

That sounds reasonable; although there may be some value in allowing dumps
relative to template0 OR template1. Not sure.

Where would you store the value if not in pg_database?


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

Thu, 09 Nov 2000 03:24:05 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
Philip Warner <pjw@rhyme.com.au> writes:
> Where would you store the value if not in pg_database?

No other ideas at the moment.  I was just wondering whether there was any
way to delete it entirely, but seems like we want to have the value for
template0 available.  The old way of hardwiring knowledge into pg_dump
was definitely not as good.

			regards, tom lane

Wed, 08 Nov 2000 11:42:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Tom Lane wrote:
>
> Do we still need the lastsysoid column in pg_database if we do things
> this way?  Seems like what you really want is to suppress all the
> objects that are in template0, so you really only need one lastsysoid
> value, namely template0's.  The other entries are useless AFAICS.
>
>              regards, tom lane

    Right. All we dump after having a non-accessible template0 is
    the difference to that. So  that  a  dump  will  create  it's
    database  from  that  template0  (no  matter  wherever it was
    created from originally) and "patch" it (i.e.  restoring  all
    diffs) to look like at dump time.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Thu, 9 Nov 2000 09:36:20 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 09:47 9/11/00 -0500, Jan Wieck wrote:
>
>    To  make  pg_dump  failsafe,  we'd  IMHO  need  to freeze all
>    objects that come with template0 copying.
>
>    For now we have oid's 1-16383 hardwired from the  bki  files.
>    Some  16384-xxxxx get allocated by initdb after bootstrap, so
>    we just need to bump the oid counter at the end of initdb (by
>    some  bootstrap  interface  command)  to  lets  say 32768 and
>    reject any attempt to touch an object with a lower oid.
>

I'd still like to see this number stored in the pgsql catalog somewhere
(not just header files).


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

Fri, 10 Nov 2000 01:36:46 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
Tom Lane wrote:
> Philip Warner <pjw@rhyme.com.au> writes:
> > Where would you store the value if not in pg_database?
>
> No other ideas at the moment.  I was just wondering whether there was any
> way to delete it entirely, but seems like we want to have the value for
> template0 available.  The old way of hardwiring knowledge into pg_dump
> was definitely not as good.

    To  make  pg_dump  failsafe,  we'd  IMHO  need  to freeze all
    objects that come with template0 copying.

    For now we have oid's 1-16383 hardwired from the  bki  files.
    Some  16384-xxxxx get allocated by initdb after bootstrap, so
    we just need to bump the oid counter at the end of initdb (by
    some  bootstrap  interface  command)  to  lets  say 32768 and
    reject any attempt to touch an object with a lower oid.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Thu, 9 Nov 2000 09:47:29 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Jan Wieck <janwieck@Yahoo.com> writes:
>     For now we have oid's 1-16383 hardwired from the  bki  files.
>     Some  16384-xxxxx get allocated by initdb after bootstrap, so
>     we just need to bump the oid counter at the end of initdb (by
>     some  bootstrap  interface  command)  to  lets  say 32768 and
>     reject any attempt to touch an object with a lower oid.

What do you mean by "touch"?  The system catalogs certainly can't
be made read-only in general.

AFAIK we already have sufficient defenses against unwanted hackery on
the system catalogs, and so I don't really see a need for another level
of checking.

			regards, tom lane

Thu, 09 Nov 2000 10:50:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Mark Hollomon <mhh@mindspring.com> writes:
> How does this solve the 'ALTER FUNCTION' problem?

What's that got to do with it?

			regards, tom lane

Thu, 09 Nov 2000 21:33:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 22:24 9/11/00 -0500, Mark Hollomon wrote:
>On Wednesday 08 November 2000 10:15, Tom Lane wrote:
>> > At 14:04 7/11/00 -0500, Jan Wieck wrote:
>> >> FWIW,  what  about having another "template0" database, where
>> >> nobody can add user data. Initially, template0 and  template1
>> >> are identically.  CREATE DATABASE get's a new switch (used by
>> >> the pg_dump output) that tells to create it from the  vanilla
>> >> template0  DB  (generalized, so someone can setup a couple of
>> >> template<n>'s)  and  all  objects  inherited  from  template1
>> >> (those not in template0) are regularly dumped per database.
>>
>> I like that a lot.  Solves the whole problem at a stroke, and even
>> adds some extra functionality (alternate templates).
>>
>
>How does this solve the 'ALTER FUNCTION' problem?
>

I think both this and the OID-wrap problem will be permanent features until
we have a non-oid-based dump procedure. Pretty much every piece of metadata
needs some kind of 'I am a system object, don't dump me' flag. 

Relying of values of numeric OIDs is definitely clunky, but it's all we can
do at the moment.


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

Fri, 10 Nov 2000 13:35:34 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
On Wednesday 08 November 2000 10:15, Tom Lane wrote:
> > At 14:04 7/11/00 -0500, Jan Wieck wrote:
> >> FWIW,  what  about having another "template0" database, where
> >> nobody can add user data. Initially, template0 and  template1
> >> are identically.  CREATE DATABASE get's a new switch (used by
> >> the pg_dump output) that tells to create it from the  vanilla
> >> template0  DB  (generalized, so someone can setup a couple of
> >> template<n>'s)  and  all  objects  inherited  from  template1
> >> (those not in template0) are regularly dumped per database.
>
> I like that a lot.  Solves the whole problem at a stroke, and even
> adds some extra functionality (alternate templates).
>

How does this solve the 'ALTER FUNCTION' problem?

-- 
Mark Hollomon

Thu, 9 Nov 2000 22:24:10 -0500
Mark Hollomon <mhh@mindspring.com>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Jan Wieck wrote:
> Tom Lane wrote:
> > Philip Warner <pjw@rhyme.com.au> writes:
> > > Where would you store the value if not in pg_database?
> >
> > No other ideas at the moment.  I was just wondering whether there was any
> > way to delete it entirely, but seems like we want to have the value for
> > template0 available.  The old way of hardwiring knowledge into pg_dump
> > was definitely not as good.
> 
>     To  make  pg_dump  failsafe,  we'd  IMHO  need  to freeze all
>     objects that come with template0 copying.

Here's another (somewhat) unhappy thought: what if there are objects
in template1 or other databases that one doesn't want to dump or
restore?

This is very much the case for user-defined types that usually consist
of multiple dozens of components. Currently, pg_dump picks them up
based on their oid, whether or not they are sitting in template1, and
dumps them in a non-restorable and non-portable manner along with the
user data. Consequently, I have to write filters to pluck the type
code out from the dump. The filters are ugly, unreliable and have to
be maintained in sync with the types. 

Picture this, though: if int and float where user-defined types --
would anyone be happy seeing them in every dump? Or, even worse,
responding to "object already exists" kind of problems during restore?

Not that I couldn't get by like this; but since everybody seems
unhappy too, maybe it's a good moment to consider a special 'dump'
attribute for every object in the schema? The attribute could be
looked at by dump and restore tools and set by whatever rules one may
find appropriate.

--Gene

Thu, 09 Nov 2000 22:59:53 -0600
selkovjr@mcs.anl.gov
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1

Philip Warner wrote:

> I think both this and the OID-wrap problem will be permanent features until
> we have a non-oid-based dump procedure. Pretty much every piece of metadata
> needs some kind of 'I am a system object, don't dump me' flag. 

Curiously enough, Philip, you seem to have been ahead of me by just a
few keystrokes, so I didn't read your observation until I sent mine.

> Relying of values of numeric OIDs is definitely clunky, but it's all we can
> do at the moment.

I held that one up, but now I am wondering: would checking a "don't
dump me" flag involve any more code or or would it be any more
difficult than the current (oid > n)? Seems like a straightforward
change to me, so what's the reason for this "all we can do" sentiment?

--Gene


Thu, 09 Nov 2000 23:23:38 -0600
selkovjr@mcs.anl.gov
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 23:23 9/11/00 -0600, selkovjr@mcs.anl.gov wrote:
>
>Philip Warner wrote:
>> Relying of values of numeric OIDs is definitely clunky, but it's all we can
>> do at the moment.
>
>I held that one up, but now I am wondering: would checking a "don't
>dump me" flag involve any more code or or would it be any more
>difficult than the current (oid > n)? Seems like a straightforward
>change to me, so what's the reason for this "all we can do" sentiment?

The imminent release of 7.1, the fact that I am not totally sold on the
idea myself, and the fact that it would require a new attribute on many
system tables. It is *a* solution to the problem, but I'd very much like to
find a different one if possible.

I have also mentioned this on two occasions now, and each has met with
total silence. I have come to interpret this to mean either (a) the idea is
too stupid to rate a comment, or (b) go ahead with the proposal. Since I am
not really proposing anything, I assume the correct interpretation is (a).
:-(.


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

Fri, 10 Nov 2000 16:51:19 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
Philip Warner <pjw@rhyme.com.au> writes:
> I have also mentioned this on two occasions now, and each has met with
> total silence. I have come to interpret this to mean either (a) the idea is
> too stupid to rate a comment, or (b) go ahead with the proposal.

More like "oof ..."

You're right, it's *a* solution, but it'd involve a lot of tedious work.
It's not just adding a column to all the system tables.  If I interpret
correctly what Mark and Gene are concerned about, it'd also mean
changing the code so that any update to a system-table row would
automatically clear the "I'm a standard item" flag.  That's not just
tedious, it's also the sort of thing that will break because someone
forgets to do it someplace.

I think everyone is keeping quiet until they can think of a better
idea...

			regards, tom lane

Fri, 10 Nov 2000 01:21:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Philip Warner writes:

> I think both this and the OID-wrap problem will be permanent features until
> we have a non-oid-based dump procedure. Pretty much every piece of metadata
> needs some kind of 'I am a system object, don't dump me' flag. 

When we implement schemas, then all objects belonging to the
DEFINITION_SCHEMA will not be dumped, all other objects will be.  At least
I imagine that this might be something to work with.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Fri, 10 Nov 2000 17:24:54 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: Unhappy thoughts about pg_dump and objects  inherited
	from template1
Peter Eisentraut <peter_e@gmx.net> writes:
> When we implement schemas, then all objects belonging to the
> DEFINITION_SCHEMA will not be dumped, all other objects will be.  At least
> I imagine that this might be something to work with.

That's a thought, although it still doesn't cope with the issue of
"what if I've altered a standard system object?" ... which is what
I think Mark was getting at yesterday.  I'm not sure there's any
reasonable way to handle that, though, short of diff'ing against a
dump of template1 :-(

To bring this back from future nice solutions to the reality of what
to do today, do people like the "template0" solution for now (7.1)?
I can work on it if so.

			regards, tom lane

Fri, 10 Nov 2000 11:39:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
On Friday 10 November 2000 11:39, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > When we implement schemas, then all objects belonging to the
> > DEFINITION_SCHEMA will not be dumped, all other objects will be.  At
> > least I imagine that this might be something to work with.
>
> That's a thought, although it still doesn't cope with the issue of
> "what if I've altered a standard system object?" ... which is what
> I think Mark was getting at yesterday.  I

Correct. I don't know why anyone would want to change the definition of
(say) int48eq, but if we are going to allow them to do so, we should be
careful to allow them to backup and restore such a change.

The template0 solution is at least better than what we have. And since I
have no other more brilliant suggestions, I would vote for it.
-- 
Mark Hollomon

Fri, 10 Nov 2000 21:52:06 -0500
Mark Hollomon <mhh@mindspring.com>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
At 11:39 10/11/00 -0500, Tom Lane wrote:
>
>To bring this back from future nice solutions to the reality of what
>to do today, do people like the "template0" solution for now (7.1)?
>I can work on it if so.
>

Being able to create a vanilla DB is essential to make pg_dump work with
customized templates, and I can't think if a better solution. So yes, it's
definitely a good idea.


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

Sat, 11 Nov 2000 14:44:29 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
At 01:21 10/11/00 -0500, Tom Lane wrote:
>
>You're right, it's *a* solution, but it'd involve a lot of tedious work.
>It's not just adding a column to all the system tables.  If I interpret
>correctly what Mark and Gene are concerned about, it'd also mean
>changing the code so that any update to a system-table row would
>automatically clear the "I'm a standard item" flag. 

I appreciate that (I think) I have said the opposite before, but I'd
actually vote against this; once something is defined as a 'system item',
it should not be the job of pg_dump to restore it, even if a DBA has
changed it. This is the correct behaviour since system objects will, almost
by definition, depend on the version of PG, and the dumped database needs
to be as close as possible to version-agnostic. In fact, the reason for the
restore may be to go back to a vanilla system after corrupting the old
system catalog...

As previously observed, we have three things to restore:

1. The base system. This is done by initdb, which creates template0/1.

2. The local extensions to the template database.

3. The local databases. We need to be able to restore these one at a time
in the presence of a localized template1 as well as in the presence of a
vanilla template1.

Implementing template0 will suffice for the moment, and maybe later we need
to consider some kind of 'isSystemObject' flag.



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

Sat, 11 Nov 2000 15:26:11 +1100
Philip Warner <pjw@rhyme.com.au>
Re: Unhappy thoughts about pg_dump and objects
	inherited from template1
Mark Hollomon wrote:

> Correct. I don't know why anyone would want to change the definition of
> (say) int48eq, but if we are going to allow them to do so, we should be
> careful to allow them to backup and restore such a change.

Yes, and it is also important that if such weirdos exist, they are
allowed to backup this type of change separately from the databases.

--Gene

Fri, 10 Nov 2000 23:09:15 -0600
selkovjr@mcs.anl.gov
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1
Tom Lane wrote:
> To bring this back from future nice solutions to the reality of what
> to do today, do people like the "template0" solution for now (7.1)?
> I can work on it if so.

    Go for it.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Wed, 15 Nov 2000 04:31:28 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: Unhappy thoughts about pg_dump and objects inherited from
	template1