Thread: Unhappy thoughts about pg_dump and objects inherited from template1
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
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 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 |/
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 fromthe 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 |/
Re: Unhappy thoughts about pg_dump and objects inherited from template1
From
"Ross J. Reedstrom"
Date:
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 #
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 #
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 |/
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
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
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 |/
Re: Unhappy thoughts about pg_dump and objects inherited from template1
From
selkovjr@mcs.anl.gov
Date:
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
Re: Unhappy thoughts about pg_dump and objects inherited from template1
From
selkovjr@mcs.anl.gov
Date:
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
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 |/
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
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/
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
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
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 |/
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 |/
Re: Unhappy thoughts about pg_dump and objects inherited from template1
From
selkovjr@mcs.anl.gov
Date:
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
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 #