Re: pg_dump -s dumps data?!

On Saturday, January 28, 2012 1:29:22 am hubert depesz lubaczewski wrote:
> On Fri, Jan 27, 2012 at 03:11:32PM -0800, Adrian Klaver wrote:
> > Not sure that it makes a difference, but on the chance it does, what
> > are the options and are they the same for all machines?
>
> $ pg_config --configure
> '--prefix=/opt/pgbrew/9.1.2' '--with-pgport=5910' '--enable-debug'
> '--with-perl' '--enable-integer-datetimes' '--enable-thread-safety'
> '--without-krb5' '--without-pam' '--without-bonjour' '--with-openssl'
> '--with-readline' '--with-libxml' '--with-zlib'
>
> and yes - the same on all machines.
>
> but the databases are not the same - so i'm more inclined to think that
> it's something wrong (or mistaken) on the db level.
>
> > I am not going to much help here, as I am still learning the
> > extension mechanism. For the sake of others that might have a clue,
> > what are the extensions involved?
> > Also, are all the tables having their data dumped or only those that
> > relate to extensions?
>
> this is custom extension - basically it's application database loaded as
> extension.
>
> aside from normal pg_catalog and information_schema, this database has
> two schemata:
> o
> and
> f
> (names changed to protect the guilty).
> o schema has 1 table.
> f schema has 7 tables.
>
> pg_dump -s dumps *data* for 6 tables from f schema. not all of them?!
>
> \dx+ fextension
> shows all 7 tables from f schema.
> *but*
> select * from pg_extension ;
> in extconfig column lists 6 oids.
>
> and these are oids of tables that got dumped with data?!
>
> is it by design that tables listed there (in extconfig column of
> pg_extension) will be dumped with data, even for pg_dump --schema?
> (i didn't make the extension, and up to yesterday I wasn't aware that
> they used extensions to load schema to these databases).

If I am following correctly then yes. I am getting on thin ice here as I am
still learning this but from here:

http://www.postgresql.org/docs/9.1/interactive/extend-extensions.html
"
35.15.3. Extension Configuration Tables

Some extensions include configuration tables, which contain data that might be
added or changed by the user after installation of the extension. Ordinarily, if
a table is part of an extension, neither the table's definition nor its content
will be dumped by pg_dump. But that behavior is undesirable for a configuration
table; any data changes made by the user need to be included in dumps, or the
extension will behave differently after a dump and reload.

To solve this problem, an extension's script file can mark a table it has created
as a configuration table, which will cause pg_dump to include the table's
contents (not its definition) in dumps. To do that, call the function
pg_extension_config_dump(regclass, text) after creating the table, for example

CREATE TABLE my_config (key text, value text);

SELECT pg_catalog.pg_extension_config_dump('my_config', '');
Any number of tables can be marked this way.

When the second argument of pg_extension_config_dump is an empty string, the
entire contents of the table are dumped by pg_dump. This is usually only correct
if the table is initially empty as created by the extension script. If there is
a mixture of initial data and user-provided data in the table, the second
argument of pg_extension_config_dump provides a WHERE condition that selects the
data to be dumped. For example, you might do

CREATE TABLE my_config (key text, value text, standard_entry boolean);

SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT
standard_entry');
and then make sure that standard_entry is true only in the rows created by the
extension's script.

More complicated situations, such as initially-provided rows that might be
modified by users, can be handled by creating triggers on the configuration table
to ensure that modified rows are marked correctly.

"
and from here:
http://www.postgresql.org/docs/9.1/interactive/catalog-pg-extension.html
"
extconfig oid[] pg_class.oid Array of regclass OIDs for the extension's
configuration table(s), or NULL if none
"

What is not explicitly stated is whether any of the above is supposed to respect
the -s switch. From the evidence it is not and I can understand that behavior.
If an extension has config tables and needs that info to load then it should be
carried along.

>
> Best regards,
>
> depesz

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com