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

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/