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(at)Yahoo(dot)com #