Re: pg_dump -s dumps data?!

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(dot)klaver(at)gmail(dot)com