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

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

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

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 #

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

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

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

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

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

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.

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

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

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

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 #

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

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 #

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

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

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