Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add --section option to pg_dump and pg_restore.

  2. Add --exclude-table-data option to pg_dump.

  1. pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-27T22:19:18Z

    hiu
I have weird situation.
pg 9.1.2, compilet by our own script from source, on 10+ machines.
on fours machines, pg_dump -s <database_name> - dumps with data!:

postgres@machine:~$ pg_dump --verbose  --schema-only dbname > q
...
pg_dump: creating TABLE x1
pg_dump: restoring data for table "x2"
pg_dump: dumping contents of table x2
pg_dump: restoring data for table "x3"
pg_dump: dumping contents of table x3
...

What could be wrong?

Same pg_dump call on the same host, but for different database dumps just schema!?

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  2. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-27T23:00:24Z

    On 01/27/2012 02:19 PM, hubert depesz lubaczewski wrote:
> hiu
> I have weird situation.
> pg 9.1.2, compilet by our own script from source, on 10+ machines.
> on fours machines, pg_dump -s<database_name>  - dumps with data!:

Are those 4 machines different from the other 6+?
What does the script do?
I am guessing you have not seen this in previous versions of postgres?

>
> postgres@machine:~$ pg_dump --verbose  --schema-only dbname>  q
> ...
> pg_dump: creating TABLE x1
> pg_dump: restoring data for table "x2"
> pg_dump: dumping contents of table x2
> pg_dump: restoring data for table "x3"
> pg_dump: dumping contents of table x3
> ...
>
> What could be wrong?
>
> Same pg_dump call on the same host, but for different database dumps just schema!?
>
> Best regards,
>
> depesz
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

  3. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-27T23:05:30Z

    On Fri, Jan 27, 2012 at 03:00:24PM -0800, Adrian Klaver wrote:
> On 01/27/2012 02:19 PM, hubert depesz lubaczewski wrote:
> >hiu
> >I have weird situation.
> >pg 9.1.2, compilet by our own script from source, on 10+ machines.
> >on fours machines, pg_dump -s<database_name>  - dumps with data!:
> 
> Are those 4 machines different from the other 6+?

no idea. same os, same installation of pg.

> What does the script do?

the compilation? just runs ./conmfigure with some options, make and make
install.

> I am guessing you have not seen this in previous versions of postgres?

that's the first time I saw this. and we never had older pg on thess
machines.

there is some suggestion that it might be related to extensions ... but
I am not sure what/how to check.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  4. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-27T23:11:32Z

    On 01/27/2012 03:05 PM, hubert depesz lubaczewski wrote:
> On Fri, Jan 27, 2012 at 03:00:24PM -0800, Adrian Klaver wrote:
>> On 01/27/2012 02:19 PM, hubert depesz lubaczewski wrote:
>>> hiu
>>> I have weird situation.
>>> pg 9.1.2, compilet by our own script from source, on 10+ machines.
>>> on fours machines, pg_dump -s<database_name>   - dumps with data!:
>>
>> Are those 4 machines different from the other 6+?
>
> no idea. same os, same installation of pg.

So much for that idea:)

>
>> What does the script do?
>
> the compilation? just runs ./conmfigure with some options, make and make
> install.

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?

>
>> I am guessing you have not seen this in previous versions of postgres?
>
> that's the first time I saw this. and we never had older pg on thess
> machines.
>
> there is some suggestion that it might be related to extensions ... but
> I am not sure what/how to check.

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?

>
> Best regards,
>
> depesz
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

  5. Re: pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-01-28T01:03:06Z

    hubert depesz lubaczewski <depesz@depesz.com> writes:
> I have weird situation.
> pg 9.1.2, compilet by our own script from source, on 10+ machines.
> on fours machines, pg_dump -s <database_name> - dumps with data!:

> postgres@machine:~$ pg_dump --verbose  --schema-only dbname > q
> ...
> pg_dump: creating TABLE x1
> pg_dump: restoring data for table "x2"
> pg_dump: dumping contents of table x2
> pg_dump: restoring data for table "x3"
> pg_dump: dumping contents of table x3
> ...

> What could be wrong?

Do the command lines actually look exactly like that?

Some platforms are forgiving about violation of the switch-then-argument
order (ie, putting switches after the database name) and some are not.
I seem to recall that Solaris is particularly strange about this,
so what platform(s) are we talking about anyway?

			regards, tom lane

  6. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-28T09:29:22Z

    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).

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  7. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-28T09:30:11Z

    On Fri, Jan 27, 2012 at 08:03:06PM -0500, Tom Lane wrote:
> Do the command lines actually look exactly like that?

Yes. Exactly.

This is ubuntu, and --schema is parsed - more details in mail i sent
a minute ago as reply to Adrians mail.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  8. Re: pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-01-28T18:10:39Z

    hubert depesz lubaczewski <depesz@depesz.com> writes:
> is it by design that tables listed there (in extconfig column of
> pg_extension) will be dumped with data, even for pg_dump --schema?

Um, yes.  Read the manual.
http://www.postgresql.org/docs/9.1/static/extend-extensions.html#AEN51966

			regards, tom lane

  9. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-28T18:11:03Z

    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.klaver@gmail.com

  10. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-30T13:08:41Z

    On Sat, Jan 28, 2012 at 01:10:39PM -0500, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > is it by design that tables listed there (in extconfig column of
> > pg_extension) will be dumped with data, even for pg_dump --schema?
> 
> Um, yes.  Read the manual.
> http://www.postgresql.org/docs/9.1/static/extend-extensions.html#AEN51966

Yes, but:

If you create table using extensions, and the you insert data to it
- this data will *not* be dumped using pg_dump.
If you mark the table with pg_extension_config_dump() - the data fro the
table will be dumped *always* - even for -s dump of another table.
I.e. pg_dump -s -t a will dump data of table b!

So long story short - with currently released versions it is either:
- you will never get data for given table in dumps
or
- you will get data for this table in dump, always, even for schema-only
dumps of unrelated tables.

Now.

I have since made some tests, and it looks like the dumping thing is
fixed in 9.2devel from git HEAD, which seems to suggest that it will
work sanely in 9.1.3 - so the point is kind of moot.

But the fact that is the newest released Pg provides mechanism that
completely breaks pg_dump functionality.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  11. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-30T15:34:49Z

    On Monday, January 30, 2012 5:08:41 am hubert depesz lubaczewski wrote:
> On Sat, Jan 28, 2012 at 01:10:39PM -0500, Tom Lane wrote:
> > hubert depesz lubaczewski <depesz@depesz.com> writes:
> > > is it by design that tables listed there (in extconfig column of
> > > pg_extension) will be dumped with data, even for pg_dump --schema?
> > 
> > Um, yes.  Read the manual.
> > http://www.postgresql.org/docs/9.1/static/extend-extensions.html#AEN51966
> 
> Yes, but:
> 
> If you create table using extensions, and the you insert data to it
> - this data will *not* be dumped using pg_dump.
> If you mark the table with pg_extension_config_dump() - the data fro the
> table will be dumped *always* - even for -s dump of another table.
> I.e. pg_dump -s -t a will dump data of table b!

Well this is a different problem report from your original post:) In your 
original report you where asking why data was dumped in a complete database 
pg_dump -s

> 
> So long story short - with currently released versions it is either:
> - you will never get data for given table in dumps
> or
> - you will get data for this table in dump, always, even for schema-only
> dumps of unrelated tables.

Well that is spelled out as such in the docs, except for the part about dumping 
data from an unrelated  table.

Short version, it is up to the extension to take care of table creation.

Long version:

"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..."

> 
> Now.
> 
> I have since made some tests, and it looks like the dumping thing is
> fixed in 9.2devel from git HEAD, which seems to suggest that it will
> work sanely in 9.1.3 - so the point is kind of moot.
> 
> But the fact that is the newest released Pg provides mechanism that
> completely breaks pg_dump functionality.

Breaks certain cases when using pg_dump -s.  Some of what you highlight above is 
designed behavior. What is happening is covered by my second rule of life 'Easy 
is difficult'. In this case it is the desire for a built in 'packaging' system 
that makes extending Postgres easier for the end user.  To get that leads to 
more complexity in the backend and a new learning curve for those that have to 
deal with it.

> 
> Best regards,
> 
> depesz

-- 
Adrian Klaver
adrian.klaver@gmail.com

  12. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-30T15:39:13Z

    On Mon, Jan 30, 2012 at 07:34:49AM -0800, Adrian Klaver wrote:
> Breaks certain cases when using pg_dump -s.  Some of what you highlight above is 
> designed behavior. What is happening is covered by my second rule of life 'Easy 
> is difficult'. In this case it is the desire for a built in 'packaging' system 
> that makes extending Postgres easier for the end user.  To get that leads to 
> more complexity in the backend and a new learning curve for those that have to 
> deal with it.

not sure what "learning curve" you have in mind, since it simply
cripples functionality of pg_dump.
how steep is the learning curve, to learn that you no longer can make
sensible "pg_dump -s"?

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  13. Re: pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-01-30T16:12:09Z

    hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Sat, Jan 28, 2012 at 01:10:39PM -0500, Tom Lane wrote:
>> Um, yes.  Read the manual.
>> http://www.postgresql.org/docs/9.1/static/extend-extensions.html#AEN51966

> Yes, but:

> If you create table using extensions, and the you insert data to it
> - this data will *not* be dumped using pg_dump.
> If you mark the table with pg_extension_config_dump() - the data fro the
> table will be dumped *always* - even for -s dump of another table.

Yes, that's the intended behavior.  The purpose of the
configuration-table feature is to dump data that is needed for an
extension to operate properly --- for instance postgis has some
auxiliary tables that are best treated as part of the schema.  If you
think the contents of a table are not effectively schema information,
then you shouldn't mark it as pg_extension_config_dump.

> I have since made some tests, and it looks like the dumping thing is
> fixed in 9.2devel from git HEAD, which seems to suggest that it will
> work sanely in 9.1.3 - so the point is kind of moot.

What tests were those exactly?  I'm not aware of any agreed changes in
this area.

			regards, tom lane

  14. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-30T16:20:15Z

    On Monday, January 30, 2012 7:39:13 am hubert depesz lubaczewski wrote:
> On Mon, Jan 30, 2012 at 07:34:49AM -0800, Adrian Klaver wrote:
> > Breaks certain cases when using pg_dump -s.  Some of what you highlight
> > above is designed behavior. What is happening is covered by my second
> > rule of life 'Easy is difficult'. In this case it is the desire for a
> > built in 'packaging' system that makes extending Postgres easier for the
> > end user.  To get that leads to more complexity in the backend and a new
> > learning curve for those that have to deal with it.
> 
> not sure what "learning curve" you have in mind, since it simply
> cripples functionality of pg_dump.
> how steep is the learning curve, to learn that you no longer can make
> sensible "pg_dump -s"?

I am not sure I understand crippled. There is a bug that you acknowledge has 
been dealt with. The rest is documented behavior having to do with extension 
packaging. Extensions exist as packages and are put into the database and pulled 
from the database as such, by the extension mechanism.  Whether data is included 
in that process is up to the discretion of the extension creator. So on that 
particular point you probably need to talk to the folks that created the 
extension. The learning curve exists because now a db admin has to understand 
that the extension mechanism exists and the ways it interacts with the rest of 
the database.

> 
> Best regards,
> 
> depesz

-- 
Adrian Klaver
adrian.klaver@gmail.com

  15. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-30T16:24:06Z

    On Mon, Jan 30, 2012 at 11:12:09AM -0500, Tom Lane wrote:
> Yes, that's the intended behavior.  The purpose of the
> configuration-table feature is to dump data that is needed for an
> extension to operate properly --- for instance postgis has some
> auxiliary tables that are best treated as part of the schema.  If you
> think the contents of a table are not effectively schema information,
> then you shouldn't mark it as pg_extension_config_dump.

but the it is *NOT POSSIBLE* do dump data of the table with pg_dump. AT
ALL. regardless of options.

> > I have since made some tests, and it looks like the dumping thing is
> > fixed in 9.2devel from git HEAD, which seems to suggest that it will
> > work sanely in 9.1.3 - so the point is kind of moot.
> What tests were those exactly?  I'm not aware of any agreed changes in
> this area.

1. create table with extension
2. try to dump it's content with pg_dump
3. try to dump schema of database
4. try to dump schema of another table (not from extension)
5. drop extension, load another extension, this time, with marking table
   with pg_extension_config_dump
repeat steps 2,3,4.

the way pg works in git HEAD is much more sensible.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  16. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-30T16:25:54Z

    On Mon, Jan 30, 2012 at 08:20:15AM -0800, Adrian Klaver wrote:
> I am not sure I understand crippled. There is a bug that you acknowledge has 
> been dealt with. The rest is documented behavior having to do with extension 
> packaging. Extensions exist as packages and are put into the database and pulled 
> from the database as such, by the extension mechanism.  Whether data is included 
> in that process is up to the discretion of the extension creator. So on that 
> particular point you probably need to talk to the folks that created the 
> extension. The learning curve exists because now a db admin has to understand 
> that the extension mechanism exists and the ways it interacts with the rest of 
> the database.

crippled in this way:
it is not possible to have table from extension that will have its data
dumped in normal pg_dump (or pg_dump -a), and will *not* have its data
dumped when doing pg_dump -s or pg_dump -s -t other_table.

you either have data for extension-based-tables in *all* dumps
or
you don't have data for extension-based-tables in *any* dumps.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  17. Re: pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-01-30T16:30:51Z

    hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Mon, Jan 30, 2012 at 11:12:09AM -0500, Tom Lane wrote:
>> What tests were those exactly?  I'm not aware of any agreed changes in
>> this area.

> 1. create table with extension
> 2. try to dump it's content with pg_dump
> 3. try to dump schema of database
> 4. try to dump schema of another table (not from extension)
> 5. drop extension, load another extension, this time, with marking table
>    with pg_extension_config_dump
> repeat steps 2,3,4.

That is way too vague for my taste, as you have not shown the pg_dump
options you're using, for example.

> the way pg works in git HEAD is much more sensible.

I'm not aware that the way it works has been changed intentionally,
so if you're seeing a difference here it's more likely that a bug has
been added than removed.  Please provide an exact test case.

			regards, tom lane

  18. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-30T17:03:47Z

    On Monday, January 30, 2012 8:25:54 am hubert depesz lubaczewski wrote:
> On Mon, Jan 30, 2012 at 08:20:15AM -0800, Adrian Klaver wrote:
> > I am not sure I understand crippled. There is a bug that you acknowledge
> > has been dealt with. The rest is documented behavior having to do with
> > extension packaging. Extensions exist as packages and are put into the
> > database and pulled from the database as such, by the extension
> > mechanism.  Whether data is included in that process is up to the
> > discretion of the extension creator. So on that particular point you
> > probably need to talk to the folks that created the extension. The
> > learning curve exists because now a db admin has to understand that the
> > extension mechanism exists and the ways it interacts with the rest of
> > the database.
> 
> crippled in this way:
> it is not possible to have table from extension that will have its data
> dumped in normal pg_dump (or pg_dump -a), and will *not* have its data
> dumped when doing pg_dump -s or pg_dump -s -t other_table.

Well the only part I see as broken is if you do pg_dump -s -t some_table and 
that causes data to be dumped from extension table(s) with out asking. This in 
the case where some_table has nothing to do with the extension. If you do 
pg_dump -s for an entire database I would expect the data to be dumped if it was 
configured by pg_catalog.pg_extension_config_dump().   When that function is run 
by the extension creator, they are essentially saying that data is needed to set 
up the extension and therefore it is schema.  Otherwise you would not be able to 
replicate an extensions state in the dump/restore cycle in those cases where 
that is important.  At least that is how I see it. 

> 
> you either have data for extension-based-tables in *all* dumps
> or
> you don't have data for extension-based-tables in *any* dumps.
> 
> Best regards,
> 
> depesz

-- 
Adrian Klaver
adrian.klaver@gmail.com

  19. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-30T17:23:15Z

    On Mon, Jan 30, 2012 at 11:30:51AM -0500, Tom Lane wrote:
> That is way too vague for my taste, as you have not shown the pg_dump
> options you're using, for example.

OK.
i tried to explain that the options don't matter, but here we go. full
example:

I have two diferent databases: 9.1.2 and 9.2devel, built TODAY from
TODAYs gir head (pulled ~ 90 minutes ago).


On both systems, in correct places, I create 2 files:

depesz--1.0.sql:
-- complain IF script IS sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION depesz" to load this file. \quit

CREATE TABLE users (
        username TEXT PRIMARY KEY,
        password TEXT
    );

depesz.control:
comment = 'test extension'
default_version = '1.0'
relocatable = true

on both machines, I create empty test database (template1 is empty, can I skip
proving this?):
=$ createdb test

afterwards, on both systems, I do:
psql -d test
create extension depesz;
create table z (i int4);
insert into users (username) values ('anything');
insert into z (i) values (1);

Results expected:
1. two tables exist (z, users).
2. table z has one row with i == 1
3. table users contains 1 row with username == anything.

Results on both 9.1.2 and 9.2 are as expected (I hope I can skip proving this,
or will this be a problem?)

Now. Let's try some dumps.

First - let's get schema dump of whole database:
Command to be used: pg_dump -s test

expected:
1. create extension depesz
2. create table 
3. no data for neither "users" nor "z" tables

results: both 9.1.2 and 9.2 pass

Now. let's get dump of table "users", just schema:

expected: no data for this table, and create table as sql or, alternatively - create extension statement.

command used: pg_dump -s -t users test

result: both 9.1.2 and 9.2 fail - there is neither create table nor create extension statement.

now. let's try the same with table "z" - command pg_dump -s -t z test

results: as expected normal create table exists in dump.

Now, let's try data dumps.

first - database wide pg_dump -a test.

expected results:

data for users table and data for z table.

result:

both 9.1.2 and 9.2 *do not* show the data for users table. data for "z" table is dumped without problem.

Now, let's try to dump data specifically for users table:

pg_dump -a -t users test

expected result: data for users table.

result: no data dumped.

table z data dump, with pg_dump -a -t z test

expected result: data for z table.

result: data for z table dumped, and nothing else.

So, as I showed above, if the table is *not* marked with
pg_catalog.pg_extension_config_dump, but the table structure comes from
extension, it is not possible, using no options, to get it's data in dump.

Is is also not possible to get table structure as "create table", or even "create extension" with pg_dump.

Now. Let's see what changes where I do use this pg_catalog.pg_extension_config_dump.

I dropped test database, changed depesz--1.0.sql to contain:

-- complain IF script IS sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION depesz" to load this file. \quit

CREATE TABLE users (
        username TEXT PRIMARY KEY,
        password TEXT
    );

SELECT pg_catalog.pg_extension_config_dump('users', '');

And recreated test, loaded extension, created z table, and inserted rows.

State before tests:
$ \d
        List of relations
 Schema │ Name  │ Type  │ Owner  
────────┼───────┼───────┼────────
 public │ users │ table │ depesz
 public │ z     │ table │ depesz
(2 rows)

(depesz@localhost:5910) 18:16:06 [test] 
$ select * from users;
 username │ password 
──────────┼──────────
 anything │ [null]
(1 row)

(depesz@localhost:5910) 18:16:12 [test] 
$ select * from z;
 i 
───
 1
(1 row)

(depesz@localhost:5910) 18:16:14 [test] 
$ \dx
                 List of installed extensions
  Name   │ Version │   Schema   │         Description          
─────────┼─────────┼────────────┼──────────────────────────────
 depesz  │ 1.0     │ public     │ test extension
 plpgsql │ 1.0     │ pg_catalog │ PL/pgSQL procedural language
(2 rows)

(depesz@localhost:5910) 18:16:15 [test] 
$ select * from pg_extension ;
 extname │ extowner │ extnamespace │ extrelocatable │ extversion │ extconfig │ extcondition 
─────────┼──────────┼──────────────┼────────────────┼────────────┼───────────┼──────────────
 plpgsql │       10 │           11 │ f              │ 1.0        │ [null]    │ [null]
 depesz  │    16387 │         2200 │ t              │ 1.0        │ {162414}  │ {""}
(2 rows)

(depesz@localhost:5910) 18:16:20 [test] 
$ select 162414::regclass;
 regclass 
──────────
 users
(1 row)

oid in 9.2 is different, but I hope it will not make the mail useless.

Now. let's try again with the dumps.

1. pg_dump test
   expected: create extension depesz; create table z; data for users;
   data for z;
   result: passed. all as expected

2. pg_dump -s test
   expected: create extension depesz; create table z; data for users;
   all as expected.

3. pg_dump -s -t z test
   expected: create table z;
   result:
     on 9.2: create table z;
     on 9.1.2: create table z + data for users table

4. pg_dump -a -t z test
   expected: data for table z
   result:
     on 9.2: data for table z
     on 9.1.2: data for both table z and table users

I hope that this time I got my point through, and frankly - if not,
I just give up.

It is *not* possible to have table come from extension, and have it's
data dumped in *some* of the dumps.

It either shows in *no* of the dumps (in case of tables without
pg_extension_config_dump()), or in *all* dumps - including dumps of
other tables, just schema dumps.

I think I explained it in previous mails, and if not - sorry, but
I clearly can't explain good enough - the point is that with the way how
extensions now work, they are useless for providing way to create
tables that will store data, in case you would ever want dump without
this data.

Best regards,

depesz


-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  20. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-30T17:43:46Z

    On 01/30/2012 09:23 AM, hubert depesz lubaczewski wrote:

>
> I think I explained it in previous mails, and if not - sorry, but
> I clearly can't explain good enough - the point is that with the way how
> extensions now work, they are useless for providing way to create
> tables that will store data, in case you would ever want dump without
> this data.

So in summary; if an extension creates a user table you want access to 
that table(schema and data) via pg_dump, outside the extension 
mechanism, without resorting to marking it as a configuration table. Is 
that correct ?


>
> Best regards,
>
> depesz
>
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

  21. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-30T17:45:09Z

    On Mon, Jan 30, 2012 at 09:43:46AM -0800, Adrian Klaver wrote:
> On 01/30/2012 09:23 AM, hubert depesz lubaczewski wrote:
> 
> >
> >I think I explained it in previous mails, and if not - sorry, but
> >I clearly can't explain good enough - the point is that with the way how
> >extensions now work, they are useless for providing way to create
> >tables that will store data, in case you would ever want dump without
> >this data.
> 
> So in summary; if an extension creates a user table you want access
> to that table(schema and data) via pg_dump, outside the extension
> mechanism, without resorting to marking it as a configuration table.
> Is that correct ?

no.
I want to be able to do both:
1. dump the data for the table
2. dump structure of other tables
but not in the same file.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  22. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-30T17:51:58Z

    On 01/30/2012 09:45 AM, hubert depesz lubaczewski wrote:
> On Mon, Jan 30, 2012 at 09:43:46AM -0800, Adrian Klaver wrote:
>> On 01/30/2012 09:23 AM, hubert depesz lubaczewski wrote:
>>
>>>
>>> I think I explained it in previous mails, and if not - sorry, but
>>> I clearly can't explain good enough - the point is that with the way how
>>> extensions now work, they are useless for providing way to create
>>> tables that will store data, in case you would ever want dump without
>>> this data.
>>
>> So in summary; if an extension creates a user table you want access
>> to that table(schema and data) via pg_dump, outside the extension
>> mechanism, without resorting to marking it as a configuration table.
>> Is that correct ?
>
> no.
> I want to be able to do both:
> 1. dump the data for the table
> 2. dump structure of other tables
> but not in the same file.

Actually that was what I was saying:)

"..via pg_dump, outside the extension mechanism.."

"..without resorting to marking it as a configuration table.."

Currently the extension mechanism is getting in the way of 1 & 2 above. 
What you want is for pg_dump to ignore the extension dependency process 
when you explicitly name a table and the operation on it.


>
> Best regards,
>
> depesz
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

  23. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-30T17:53:45Z

    On Mon, Jan 30, 2012 at 09:51:58AM -0800, Adrian Klaver wrote:
> On 01/30/2012 09:45 AM, hubert depesz lubaczewski wrote:
> >On Mon, Jan 30, 2012 at 09:43:46AM -0800, Adrian Klaver wrote:
> >>On 01/30/2012 09:23 AM, hubert depesz lubaczewski wrote:
> >>
> >>>
> >>>I think I explained it in previous mails, and if not - sorry, but
> >>>I clearly can't explain good enough - the point is that with the way how
> >>>extensions now work, they are useless for providing way to create
> >>>tables that will store data, in case you would ever want dump without
> >>>this data.
> >>
> >>So in summary; if an extension creates a user table you want access
> >>to that table(schema and data) via pg_dump, outside the extension
> >>mechanism, without resorting to marking it as a configuration table.
> >>Is that correct ?
> >
> >no.
> >I want to be able to do both:
> >1. dump the data for the table
> >2. dump structure of other tables
> >but not in the same file.
> 
> Actually that was what I was saying:)
> 
> "..via pg_dump, outside the extension mechanism.."
> 
> "..without resorting to marking it as a configuration table.."
> 
> Currently the extension mechanism is getting in the way of 1 & 2
> above. What you want is for pg_dump to ignore the extension
> dependency process when you explicitly name a table and the
> operation on it.

no.
marking is irrelevant. marking lets you do one thing, but breaks the
other.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  24. Re: pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-30T22:20:24Z

    On 01/30/2012 09:53 AM, hubert depesz lubaczewski wrote:
> On Mon, Jan 30, 2012 at 09:51:58AM -0800, Adrian Klaver wrote:
>> On 01/30/2012 09:45 AM, hubert depesz lubaczewski wrote:
>>> On Mon, Jan 30, 2012 at 09:43:46AM -0800, Adrian Klaver wrote:
>>>> On 01/30/2012 09:23 AM, hubert depesz lubaczewski wrote:
>>>>
>>>>>
>>>>> I think I explained it in previous mails, and if not - sorry, but
>>>>> I clearly can't explain good enough - the point is that with the way how
>>>>> extensions now work, they are useless for providing way to create
>>>>> tables that will store data, in case you would ever want dump without
>>>>> this data.
>>>>
>>>> So in summary; if an extension creates a user table you want access
>>>> to that table(schema and data) via pg_dump, outside the extension
>>>> mechanism, without resorting to marking it as a configuration table.
>>>> Is that correct ?
>>>
>>> no.
>>> I want to be able to do both:
>>> 1. dump the data for the table
>>> 2. dump structure of other tables
>>> but not in the same file.
>>
>> Actually that was what I was saying:)
>>
>> "..via pg_dump, outside the extension mechanism.."
>>
>> "..without resorting to marking it as a configuration table.."
>>
>> Currently the extension mechanism is getting in the way of 1&  2
>> above. What you want is for pg_dump to ignore the extension
>> dependency process when you explicitly name a table and the
>> operation on it.
>
> no.
> marking is irrelevant. marking lets you do one thing, but breaks the
> other.

I am going to have to go Cool Hand Luke(1) here:)

"What we have here is a failure to communicate"

I think we are actually saying the same thing, just coming from 
different directions. Bear with me as I try to organize what I know of 
extensions and how it relates to the issue at hand. The docs snippets 
below come from:

http://www.postgresql.org/docs/9.1/interactive/extend-extensions.html

1) An Extension is basically a set of templates with dependency tracking.

Said templates and the files that specify the dependencies are stored on 
disk outside the database storage. This means a pg_dump only finds the 
Extensions in pg_extension and does the minimum necessary to push the 
information into the dump file i.e do CREATE EXTENSION some_extension. 
Or to quote the docs:
"
Even more useful, pg_dump knows that it should not dump the individual 
member objects of the extension — it will just include a CREATE 
EXTENSION command in dumps, instead. This vastly simplifies migration to 
a new version of the extension that might contain more or different 
objects than the old version. Note however that you must have the 
extension's control, script, and other files available when loading such 
a dump into a new database.
"
So by default pg_dump does not care about the actual objects referred to 
by the Extension. As far as it is concerned they are invisible.

2) The exception that breaks the rule. It is possible to designate 
tables as being configuration tables and as such are essential to the 
working of the Extension. In this case the tables data(or some subset of 
it) is dumped, though not the table schema as that is still the 
responsibility of the Extension.

3) You have tables created by the Extension that either:
a) do not fall into that exception and for which you want to recover 
that data and/or schema by using pg_dump.
or
b) do but you want to grab the schema without going to the schema 
information stored outside the database.

4) Problem 3a is as far as I can see not handled by the Extension system.
Problem 3b is also not handled, though the schema information is 
available in the files stored outside the database storage.

5) The above is currently complicated by what looks to be a bug that 
dumps the data for tables designated as configuration tables even when 
you are specifically doing a pg_dump -s(a) -t some_table on a table that 
is not part of an Extension. So along with the schema/data for the table 
you desire you get the data for those Extension tables designated as 
configuration tables.

6) Given the above and from what you have said I am going to go out on a 
limb and say what you want is as I stated before:

For pg_dump not to ignore the objects referred to by an Extension. 
Indeed what you want is for pg_dump to see them as 'ordinary' objects 
and dump them 'normally' according to the switches you provide to pg_dump.

>
> Best regards,
>
> depesz
>

(1) http://www.imdb.com/title/tt0061512/
-- 
Adrian Klaver
adrian.klaver@gmail.com

  25. Re: pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-01-31T04:18:31Z

    [ Note: please follow-up to pgsql-hackers not pgsql-general; I think
  this discussion needs to move there ]

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Mon, Jan 30, 2012 at 11:30:51AM -0500, Tom Lane wrote:
>> That is way too vague for my taste, as you have not shown the pg_dump
>> options you're using, for example.

> i tried to explain that the options don't matter, but here we go. full
> example:
> [ example showing pg_dump's odd behavior for extension config tables ]

[ traces through that with gdb... ]

As I suspected, the behavioral change from 9.1 to HEAD is not
intentional.  It is an artifact of commit
7b070e896ca835318c90b02c830a5c4844413b64, which is almost, but not
quite, entirely broken.  I won't enumerate its shortcomings here,
because they're not really relevant, but it does seem appropriate to
discuss exactly what we think *should* happen for tables created inside
extensions.

The original design intention for non-config tables was, per the manual:

	Ordinarily, if a table is part of an extension, neither the
	table's definition nor its content will be dumped by pg_dump.

the assumption being that both the definition and the content would be
re-loaded by executing the extension's SQL script.  The purpose of
pg_extension_config_dump() is to allow part or all of the data in the
table to be treated as user data and thus dumped; this is assumed to
be data not supplied by the extension script but by subsequent user
insertions.

I don't recall that we thought very hard about what should happen when
pg_dump switches are used to produce a selective dump, but ISTM
reasonable that if it's "user data" then it should be dumped only if
data in a regular user table would be.  So I agree it's pretty broken
that "pg_dump -t foo" will dump data belonging to a config table not
selected by the -t switch.  I think this should be changed in both HEAD
and 9.1 (note that HEAD will presumably return to the 9.1 behavior once
that --exclude-table-data patch gets fixed).

What's not apparent to me is whether there's an argument for doing more
than that.  It strikes me that the current design is not very friendly
towards the idea of an extension that creates a table that's meant
solely to hold user data --- you'd have to mark it as "config" which
seems a bit unfortunate terminology for that case.  Is it important to
do something about that, and if so what?

			regards, tom lane

  26. Re: [GENERAL] pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-01-31T10:30:14Z

    On Mon, Jan 30, 2012 at 11:18:31PM -0500, Tom Lane wrote:
> I don't recall that we thought very hard about what should happen when
> pg_dump switches are used to produce a selective dump, but ISTM
> reasonable that if it's "user data" then it should be dumped only if
> data in a regular user table would be.  So I agree it's pretty broken
> that "pg_dump -t foo" will dump data belonging to a config table not
> selected by the -t switch.  I think this should be changed in both HEAD
> and 9.1 (note that HEAD will presumably return to the 9.1 behavior once
> that --exclude-table-data patch gets fixed).
> 
> What's not apparent to me is whether there's an argument for doing more
> than that.  It strikes me that the current design is not very friendly
> towards the idea of an extension that creates a table that's meant
> solely to hold user data --- you'd have to mark it as "config" which
> seems a bit unfortunate terminology for that case.  Is it important to
> do something about that, and if so what?

Currently we are migrating away from using extensions. But - recently
on planet.postgresql.org there were some (more than 2) posts about
schema versioning.
EXTENSIONS, with their versions, upgrades, dependency tracking, would be
*perfect* for storing application structures, if:
1. we could use them from arbitrary location, and not only
   <install-root>/share/postgresql/extension/ which usually shouldn't be
   writtable by users
2. they do not interfere with pg_dump

2nd point means that I still need to be able to get:
1. full database schema dump - which can use "create extension"
2. single table schema dump - which, in my opinion, should use create
   table, and only schema of requested table(s) should be shown, no
   schema or data for other tables should be dumped.
3. full database data dump
4. single table data dump - in which case neither structure, nor data of
   other tables (than requested) should be emitted.

personally, I think that the marking of extension tables should be
reversed - by default they should normally dump data - just like any
other table. Just in case of some specific tables you'd mark them with
"do not dump data by default" which would exclude their data from normal
database wide pg_dump.

that's how I envision working extensions, and that's how I'd like them
to work. of course your needs/opinions can be different - especially in
case when we consider extensions to be only a tool to simplify
dump/restore of contrib modules.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  27. Re: [HACKERS] pg_dump -s dumps data?!

    Robert Haas <robertmhaas@gmail.com> — 2012-01-31T12:36:46Z

    On Mon, Jan 30, 2012 at 11:18 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't recall that we thought very hard about what should happen when
> pg_dump switches are used to produce a selective dump, but ISTM
> reasonable that if it's "user data" then it should be dumped only if
> data in a regular user table would be.

Yep.

> What's not apparent to me is whether there's an argument for doing more
> than that.  It strikes me that the current design is not very friendly
> towards the idea of an extension that creates a table that's meant
> solely to hold user data --- you'd have to mark it as "config" which
> seems a bit unfortunate terminology for that case.  Is it important to
> do something about that, and if so what?

Is this anything more than a naming problem?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  28. Re: [GENERAL] pg_dump -s dumps data?!

    Andrew Dunstan <andrew@dunslane.net> — 2012-01-31T15:54:28Z

    
On 01/30/2012 11:18 PM, Tom Lane wrote:
> [ example showing pg_dump's odd behavior for extension config tables ]
> [ traces through that with gdb... ]
>
> As I suspected, the behavioral change from 9.1 to HEAD is not
> intentional.  It is an artifact of commit
> 7b070e896ca835318c90b02c830a5c4844413b64, which is almost, but not
> quite, entirely broken.  I won't enumerate its shortcomings here,
> because they're not really relevant, but it does seem appropriate to
> discuss exactly what we think *should* happen for tables created inside
> extensions.
I'm perplexed about what you thing the patch does wrong or how it affects this. If I've broken something I'd like to know how, exactly, so I have a chance to fix it.

cheers

andrew

  29. Re: pg_dump -s dumps data?!

    Martijn van Oosterhout <kleptog@svana.org> — 2012-01-31T20:02:59Z

    On Mon, Jan 30, 2012 at 11:18:31PM -0500, Tom Lane wrote:
> I don't recall that we thought very hard about what should happen when
> pg_dump switches are used to produce a selective dump, but ISTM
> reasonable that if it's "user data" then it should be dumped only if
> data in a regular user table would be.  So I agree it's pretty broken
> that "pg_dump -t foo" will dump data belonging to a config table not
> selected by the -t switch.  I think this should be changed in both HEAD
> and 9.1 (note that HEAD will presumably return to the 9.1 behavior once
> that --exclude-table-data patch gets fixed).

Perhaps a better way of dealing with this is providing a way of dumping
extensions explicitly. Then you could say:

pg_dump --extension=postgis -s

to get the data. And you can use all the normal pg_dump options for
controlling the output. The flag currently used to seperate the table
schema from the table content could then interact logically. Another
way perhaps:

pg_dump --extension-postgis=data-only
pg_dump --extension-postgis=schema
pg_dump --extension-postgis=all
pg_dump --extension-postgis=none

The last being the default.

Just throwing out some completely different ideas.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

  30. Re: [GENERAL] pg_dump -s dumps data?!

    Andrew Dunstan <andrew@dunslane.net> — 2012-01-31T20:23:02Z

    
On 01/31/2012 03:02 PM, Martijn van Oosterhout wrote:
> On Mon, Jan 30, 2012 at 11:18:31PM -0500, Tom Lane wrote:
>> I don't recall that we thought very hard about what should happen when
>> pg_dump switches are used to produce a selective dump, but ISTM
>> reasonable that if it's "user data" then it should be dumped only if
>> data in a regular user table would be.  So I agree it's pretty broken
>> that "pg_dump -t foo" will dump data belonging to a config table not
>> selected by the -t switch.  I think this should be changed in both HEAD
>> and 9.1 (note that HEAD will presumably return to the 9.1 behavior once
>> that --exclude-table-data patch gets fixed).
> Perhaps a better way of dealing with this is providing a way of dumping
> extensions explicitly. Then you could say:
>
> pg_dump --extension=postgis -s
>
> to get the data. And you can use all the normal pg_dump options for
> controlling the output. The flag currently used to seperate the table
> schema from the table content could then interact logically. Another
> way perhaps:
>
> pg_dump --extension-postgis=data-only
> pg_dump --extension-postgis=schema
> pg_dump --extension-postgis=all
> pg_dump --extension-postgis=none
>

This one won't fly. We use option processing that requires that the 
option names to be known at compile time, so you can't embed an 
arbitrary extension name in there.

cheers

andrew

  31. Re: [HACKERS] pg_dump -s dumps data?!

    Adrian Klaver <adrian.klaver@gmail.com> — 2012-01-31T22:37:45Z

    On 01/31/2012 04:36 AM, Robert Haas wrote:
> On Mon, Jan 30, 2012 at 11:18 PM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>> I don't recall that we thought very hard about what should happen when
>> pg_dump switches are used to produce a selective dump, but ISTM
>> reasonable that if it's "user data" then it should be dumped only if
>> data in a regular user table would be.
>
> Yep.
>
>> What's not apparent to me is whether there's an argument for doing more
>> than that.  It strikes me that the current design is not very friendly
>> towards the idea of an extension that creates a table that's meant
>> solely to hold user data --- you'd have to mark it as "config" which
>> seems a bit unfortunate terminology for that case.  Is it important to
>> do something about that, and if so what?
>
> Is this anything more than a naming problem?

Seems to me that would be dependent on what the future plans are for the 
extension mechanism. There is also the issue of backward compatibility 
for those people that are using configuration tables in their extensions 
and would like to maintain that separation. I could see adding another 
function that is similar and would be used to identify strictly user 
data tables.



-- 
Adrian Klaver
adrian.klaver@gmail.com

  32. Re: [GENERAL] pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-01-31T22:48:06Z

    Andrew Dunstan <andrew@dunslane.net> writes:
> On 01/30/2012 11:18 PM, Tom Lane wrote:
>> As I suspected, the behavioral change from 9.1 to HEAD is not
>> intentional.  It is an artifact of commit
>> 7b070e896ca835318c90b02c830a5c4844413b64, which is almost, but not
>> quite, entirely broken.  I won't enumerate its shortcomings here,

> I'm perplexed about what you thing the patch does wrong or how it affects this. If I've broken something I'd like to know how, exactly, so I have a chance to fix it.

Well, it adds a new field to all instances of DumpableObject and then
leaves it uninitialized in most cases, which is bad style (and unlike in
the backend, there is no forced zeroing to ensure a consistent value);
but the proximate cause of the bug is that you put the filtering in the
wrong place.  The way this is supposed to work, or at least used to
work, is that dump-the-data-or-not is determined by whether
a TableDataInfo DumpableObject gets created --- see the callers of
makeTableDataInfo.  You didn't follow that convention but instead
inserted an extra filter test in dumpTableData.  The reason that
depesz's example is not dumping the unwanted config table is that the
code path in which getExtensionMembership calls makeTableDataInfo isn't
ever setting the dumpdata flag.  Unfortunately that means that config
table data won't get dumped when it *is* wanted, either.  Or worse,
it means that the data might or might not get dumped depending on
whether the pg_malloc in makeTableDataInfo is allocating new or recycled
memory and what happens to be in that memory in the latter case.

Now I'm not entirely sure that we ought to preserve the old code
structure as-is; it might be more future-proof if we always made
TableDataInfo objects and then used their dump flags to control whether
to dump them.  (The main potential advantage of that is that we'd deal
more sanely with dependency chains linking through TableDataInfo
objects; but I'm not sure there are any such at the moment.)  But what
we've got at the moment is a mess.  In any case I think we'd be better
off without the separate dumpdata field: if we need a flag we should use
the "dump" flag of the TableDataInfo object.

As far as depesz's actual complaint is concerned, I think the core of
the problem is that getExtensionMembership is unconditionally asking for
a config table's data to be dumped, without any consideration of whether
pg_dump switches ought to filter that.  I'm unsure whether we should
just add more logic there, or instead put the policy logic into
makeTableDataInfo.  The latter might result in less duplicative code,
but would require more rethinking of getTableData() --- which conditions
tested there ought to move into makeTableDataInfo?

			regards, tom lane

  33. Re: [HACKERS] pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-01-31T23:07:51Z

    Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 01/31/2012 04:36 AM, Robert Haas wrote:
>> On Mon, Jan 30, 2012 at 11:18 PM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>>> What's not apparent to me is whether there's an argument for doing more
>>> than that.  It strikes me that the current design is not very friendly
>>> towards the idea of an extension that creates a table that's meant
>>> solely to hold user data --- you'd have to mark it as "config" which
>>> seems a bit unfortunate terminology for that case.  Is it important to
>>> do something about that, and if so what?

>> Is this anything more than a naming problem?

> Seems to me that would be dependent on what the future plans are for the 
> extension mechanism.

My thought exactly --- maybe it's only a minor cosmetic issue that will
affect few people, or maybe this will someday be a major use-case.
I don't know.  I was hoping Dimitri had an opinion.

			regards, tom lane

  34. Re: [GENERAL] pg_dump -s dumps data?!

    Andrew Dunstan <andrew@dunslane.net> — 2012-02-01T04:10:32Z

    
On 01/31/2012 05:48 PM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net>  writes:
>> On 01/30/2012 11:18 PM, Tom Lane wrote:
>>> As I suspected, the behavioral change from 9.1 to HEAD is not
>>> intentional.  It is an artifact of commit
>>> 7b070e896ca835318c90b02c830a5c4844413b64, which is almost, but not
>>> quite, entirely broken.  I won't enumerate its shortcomings here,
>> I'm perplexed about what you thing the patch does wrong or how it affects this. If I've broken something I'd like to know how, exactly, so I have a chance to fix it.
> Well, it adds a new field to all instances of DumpableObject and then
> leaves it uninitialized in most cases, which is bad style (and unlike in
> the backend, there is no forced zeroing to ensure a consistent value);
> but the proximate cause of the bug is that you put the filtering in the
> wrong place.  The way this is supposed to work, or at least used to
> work, is that dump-the-data-or-not is determined by whether
> a TableDataInfo DumpableObject gets created --- see the callers of
> makeTableDataInfo.  You didn't follow that convention but instead
> inserted an extra filter test in dumpTableData.  The reason that
> depesz's example is not dumping the unwanted config table is that the
> code path in which getExtensionMembership calls makeTableDataInfo isn't
> ever setting the dumpdata flag.  Unfortunately that means that config
> table data won't get dumped when it *is* wanted, either.  Or worse,
> it means that the data might or might not get dumped depending on
> whether the pg_malloc in makeTableDataInfo is allocating new or recycled
> memory and what happens to be in that memory in the latter case.
>
> Now I'm not entirely sure that we ought to preserve the old code
> structure as-is; it might be more future-proof if we always made
> TableDataInfo objects and then used their dump flags to control whether
> to dump them.  (The main potential advantage of that is that we'd deal
> more sanely with dependency chains linking through TableDataInfo
> objects; but I'm not sure there are any such at the moment.)  But what
> we've got at the moment is a mess.  In any case I think we'd be better
> off without the separate dumpdata field: if we need a flag we should use
> the "dump" flag of the TableDataInfo object.
>
> As far as depesz's actual complaint is concerned, I think the core of
> the problem is that getExtensionMembership is unconditionally asking for
> a config table's data to be dumped, without any consideration of whether
> pg_dump switches ought to filter that.  I'm unsure whether we should
> just add more logic there, or instead put the policy logic into
> makeTableDataInfo.  The latter might result in less duplicative code,
> but would require more rethinking of getTableData() --- which conditions
> tested there ought to move into makeTableDataInfo?
>
> 			


Here's a possible patch for the exclude-table-data problem along the 
lines you suggest.

cheers

andrew

  35. Re: pg_dump -s dumps data?!

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2012-02-01T21:02:14Z

    Hi,

Sorry to be late in the thread, I'm too busy right now.  Cédric called
it to my immediate attention though.

Martijn van Oosterhout <kleptog@svana.org> writes:
> Perhaps a better way of dealing with this is providing a way of dumping
> extensions explicitly. Then you could say:
>
> pg_dump --extension=postgis -s

That's something I'm working on in this commit fest under the “inline
extensions” topic, and we should have that facility in 9.2 baring major
obstacles (consensus is made).

Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> On Mon, Jan 30, 2012 at 11:18 PM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>>>> What's not apparent to me is whether there's an argument for doing more
>>>> than that.  It strikes me that the current design is not very friendly
>>>> towards the idea of an extension that creates a table that's meant
>>>> solely to hold user data --- you'd have to mark it as "config" which
>>>> seems a bit unfortunate terminology for that case.  Is it important to
>>>> do something about that, and if so what?
>
> My thought exactly --- maybe it's only a minor cosmetic issue that will
> affect few people, or maybe this will someday be a major use-case.
> I don't know.  I was hoping Dimitri had an opinion.

So, being able to stuff data into an extension has been made possible to
address two use cases:

 - postgis
 - (sql only) data extensions

The former is very specific and as we didn't hear back from them I guess
we addressed it well enough, the latter is still WIP. It's about being
able to ship data as an extension (think timezone updates, geo ip, bank
cards database, exchange rates, etc). You need to be able to easily ship
those (CSV isn't the best we can do here, as generally it doesn't
include the schema nor the COPY recipe that can be non-trivial) and to
easily update those.

The case for a table that is partly user data and partly extension data
is very thin, I think that if I had this need I would use inheritance
and a CHECK(user_data is true/false) constraint to filter the data.

So I sure would appreciate being able to call that data rather than
config, and to mark any table at once. If that doesn't need any pg_dump
stretching I think providing that in 9.2 would be great.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  36. Re: pg_dump -s dumps data?!

    hubert depesz lubaczewski <depesz@depesz.com> — 2012-02-02T11:24:00Z

    On Wed, Feb 01, 2012 at 10:02:14PM +0100, Dimitri Fontaine wrote:
> The case for a table that is partly user data and partly extension data
> is very thin, I think that if I had this need I would use inheritance
> and a CHECK(user_data is true/false) constraint to filter the data.

definitely agree. i.e. i don't really see a case when we'd have data
from both extension, and normal usage, in the same table.
and the overhead of tracking source of data seems to be excessive.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

  37. Re: [GENERAL] pg_dump -s dumps data?!

    Andrew Dunstan <andrew@dunslane.net> — 2012-02-06T17:18:19Z

    
On 01/31/2012 11:10 PM, Andrew Dunstan wrote:
>
>
> Here's a possible patch for the exclude-table-data problem along the 
> lines you suggest.


Should I apply this?

cheers

andrew

  38. Re: [GENERAL] pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-02-07T19:56:07Z

    Andrew Dunstan <andrew@dunslane.net> writes:
> On 01/31/2012 11:10 PM, Andrew Dunstan wrote:
>> Here's a possible patch for the exclude-table-data problem along the 
>> lines you suggest.

> Should I apply this?

I'm not happy with this yet.  My core complaint is that pg_dump used to
consider that creation of a TableDataInfo object for a table happens
if and only if we're going to dump the table's data.  And the comments
(eg in pg_dump.h) still say that.  But the previous patch left us in a
halfway zone where sometimes we'd create a TableDataInfo object and then
choose not to dump the data, and this patch doesn't get us out of that.
I think we should either revert to the previous definition, or go over
to a design wherein we always create TableDataInfo objects for all
tables (but probably still excluding data-less relations such as views)
and the whether-to-dump decision is expressed only by setting or not
setting the object's dump flag.

I worked a little bit on a patch to do the latter but found that it was
more invasive than I'd hoped.  Given the lack of any immediate payoff
I think it'd probably make more sense to do the former.  We could still
centralize the decision making into makeTableDataInfo a bit more than
now, but it should take the form of not creating the object at all,
rather than creating it and then clearing its dump flag.

			regards, tom lane

  39. Re: [GENERAL] pg_dump -s dumps data?!

    Andrew Dunstan <andrew@dunslane.net> — 2012-02-07T22:00:13Z

    
On 02/07/2012 02:56 PM, Tom Lane wrote:
> Andrew Dunstan<andrew@dunslane.net>  writes:
>> On 01/31/2012 11:10 PM, Andrew Dunstan wrote:
>>> Here's a possible patch for the exclude-table-data problem along the
>>> lines you suggest.
>> Should I apply this?
> I'm not happy with this yet.  My core complaint is that pg_dump used to
> consider that creation of a TableDataInfo object for a table happens
> if and only if we're going to dump the table's data.  And the comments
> (eg in pg_dump.h) still say that.  But the previous patch left us in a
> halfway zone where sometimes we'd create a TableDataInfo object and then
> choose not to dump the data, and this patch doesn't get us out of that.
> I think we should either revert to the previous definition, or go over
> to a design wherein we always create TableDataInfo objects for all
> tables (but probably still excluding data-less relations such as views)
> and the whether-to-dump decision is expressed only by setting or not
> setting the object's dump flag.
>
> I worked a little bit on a patch to do the latter but found that it was
> more invasive than I'd hoped.  Given the lack of any immediate payoff
> I think it'd probably make more sense to do the former.  We could still
> centralize the decision making into makeTableDataInfo a bit more than
> now, but it should take the form of not creating the object at all,
> rather than creating it and then clearing its dump flag.
>
> 			


OK, in this version we simply suppress creation of the TableDataInfo 
object if it's not wanted. I actually removed the code from 
makeTableDataInfo - there are only two places it gets called and doing 
it in those two spots seemed a bit cleaner.

cheers

andrew

  40. Re: [GENERAL] pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-02-08T20:31:18Z

    Andrew Dunstan <andrew@dunslane.net> writes:
> OK, in this version we simply suppress creation of the TableDataInfo 
> object if it's not wanted.

I applied this with some further adjustments.

> I actually removed the code from 
> makeTableDataInfo - there are only two places it gets called and doing 
> it in those two spots seemed a bit cleaner.

After study it seemed to me that this was the wrong direction to take.
It was already the case that the config-table path was skipping the
filters in getTableData(), none of which seem to be good for it to skip
other than the one on the table's schema-dump flag.  So I've refactored
the code to put all those filters into makeTableDataInfo where they
will be applied to both the normal and config-table cases.

Now, back to the original subject of this thread: both HEAD and 9.1 are
now operating as designed, in that they will dump the (user-provided
portion of the) contents of an extension config table whenever that
extension is dumped, even if --schema is specified.  Do we want to
change that?  I'm not convinced that it's something to mess with
lightly.  I could possibly support a definition that says that we omit
such data in --schema mode, except that I'm not sure what is sensible
for rows that were modified (not inserted) by user actions.  Also, we
probably ought to get some input from the postgis people, because after
all this entire feature was designed for their schema auxiliary tables.

			regards, tom lane

  41. Re: [GENERAL] pg_dump -s dumps data?!

    Tom Lane <tgl@sss.pgh.pa.us> — 2012-02-10T19:54:27Z

    I wrote:
> Now, back to the original subject of this thread: both HEAD and 9.1 are
> now operating as designed, in that they will dump the (user-provided
> portion of the) contents of an extension config table whenever that
> extension is dumped, even if --schema is specified.

Or so I thought, anyway.  Further experimentation with despez's example
shows that in HEAD, --schema is still able to block dumping of extension
config table contents, and the reason appears to be commit
a4cd6abcc901c1a8009c62a27f78696717bb8fe1, which added yet another set
of filtering conditions in a poorly chosen place; or possibly I should
say it made arbitrary changes in the definition of the --schema switch.
That patch needs some rethinking too, though I'm not sure what yet.

I also note that his example shows that if you have a selective dump
(say, with a -t switch), config table contents will be dumped even when
the owning extension is not.  This seems like a pretty clear bug:
getExtensionMembership should not be creating TableDataInfo objects for
extension config tables if the owning extension is not to be dumped.
Barring objections, I'll go fix and back-patch that.

			regards, tom lane