Re: pg_restore -t should match views, matviews, and foreign tables

On 8 April 2015 at 05:05, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Tue, Apr 7, 2015 at 1:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> > On 3/31/15 11:01 PM, Craig Ringer wrote:
>> >> this patch adds support for views, foreign tables, and materialised
>> >> views to the pg_restore -t flag.
>>
>> > I think this is a good change. Any concerns?
>>
>> Are we happy with pg_dump/pg_restore not distinguishing these objects
>> by type? It seems rather analogous to letting ALTER TABLE work on views
>> etc. Personally I'm fine with this, but certainly some people have
>> complained about that approach so far as ALTER is concerned. (But the
>> implication would be that we'd need four distinct switches, which is
>> not an outcome I favor.)
>>
>
> ​The pg_dump documentation for the equivalent "-t" switch states:
>
> ​"Dump only tables (or views or sequences or foreign tables) matching
> table"
>
> Does pg_dump need to be updated to address materialized views here?
>

The pg_dump code handles materialized views, the docs weren't updated. I
added mention of them in the next rev of the patch to pg_restore.

> Does pg_restore need to be updated to address sequences here?
>

I'd be against that if pg_dump didn't already behave the same way. Given
that, yes, I think so.

> ISTM that the two should mirror each other.
>

Ideally, yes, but the differences go much deeper than this.

to get the equivalent of:

pg_restore -n myschema -t sometable

in pg_dump you need:

pg_dump -t "\"myschema\".\"sometable\""

pg_dump -n myschema -t sometable is **not** equivalent. In fact, the -n is
ignored, and -t will match using the search_path.

so they're never really going to be the same, just similar enough to catch
people out most of the time.

I think you're right that sequences should be included by pg_restore since
they are by pg_dump, though. So v3 patch attached.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services