Re: pg_dump -s dumps data?!

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(at)localhost:5910) 18:16:06 [test]
$ select * from users;
username │ password
──────────┼──────────
anything │ [null]
(1 row)

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

(depesz(at)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(at)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(at)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/