Re: pg_dump with tables created in schemas created by extensions

2016-08-26 19:37 GMT-03:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> =?UTF-8?B?TWFydMOtbiBNYXJxdcOpcw==?= <martin(at)2ndquadrant(dot)com> writes:
>> Looking at this issue today, I found that we are not setting a
>> dependency for an index created inside an extension.
>
> Surely the index has a dependency on a table, which depends on the
> extension?
>
> If you mean that you want an extension to create an index on a table that
> doesn't belong to it, but it's assuming pre-exists, I think that's just
> stupid and we need not support it.

Well, there's still the second pattern I mentioned before (which
actually came up while trying to fix this patch).

Extension creates a table and an index over one of the columns:

CREATE TABLE regress_pg_dump_schema.test_table (
col1 int,
col2 int
);

CREATE INDEX test_extension_index ON regress_pg_dump_schema.test_table (col2);

Later, some application (or a user, doesn't matter really) creates a
second index over col1:

CREATE INDEX test_index ON regress_pg_dump_schema.test_table (col1);

What we are doing (or at least it's what I understand from the code)
is checking if the table depends on an extension, and so we don't dump
it.

We should be able to use the same procedure (and reuse the code we
already have) to decide if an index should be dumped or not. But we
are missing the dependency, and so it's not possible to know that
regress_pg_dump_schema.test_extension_index depends on the extension
and regress_pg_dump_schema.test_index doesn't.

Or is this something we shouldn't support (in that case we should document it).

--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services