Re: inherit support for foreign tables - Mailing list pgsql-hackers

Hello,

> By the way, Can I have a simple script to build an environment to
> run this on?

I built test environment and ran the simple test using
postgres_fdw and got parameterized path from v3 patch on the
following operation as shown there, and v6 also gives one, but I
haven't seen the reparameterization of v6 patch work.

# How could I think to have got it work before?

Do you have any idea to make postgreReparameterizeForeignPath on
foreign (child) tables works effectively?

regards,

====
### on pg1/pg2:
create table pu1 (a int not null, b int not null, c int, d text);
create unique index i_pu1_ab on pu1 (a, b);
create unique index i_pu1_c  on pu1 (c);
create table cu11 (like pu1 including all) inherits (pu1);
create table cu12 (like pu1 including all) inherits (pu1);
insert into cu11 (select a / 5, 4 - (a % 5), a, 'cu11' from generate_series(000000, 099999) a);
insert into cu12 (select a / 5, 4 - (a % 5), a, 'cu12' from generate_series(100000, 199999) a);

### on pg1:
create extension postgres_fdw;
create server pg2 foreign data wrapper postgres_fdw options (host '/tmp', port '5433', dbname 'postgres');
create user mapping for current_user server pg2 options (user 'horiguti');
create foreign table _cu11 (a int, b int, c int, d text) server pg2 options (table_name 'cu11', use_remote_estimate
'true');
create foreign table _cu12 (a int, b int, c int, d text) server pg2 options (table_name 'cu12', use_remote_estimate
'true');
create table rpu1 (a int, b int, c int, d text);
alter foreign table _cu11 inherit rpu1;
alter foreign table _cu12 inherit rpu1;
analyze rpu1;

=# explain analyze select pu1.*   from pu1 join rpu1 on (pu1.c = rpu1.c) where pu1.a = 3;
    QUERY PLAN
 
-------------------------------------------------------------------------------Nested Loop  (cost=0.00..2414.57 rows=11
width=19)            (actual time=0.710..7.167 rows=5 loops=1)  ->  Append  (cost=0.00..30.76 rows=11 width=19)
<localside.. ommitted>  ->  Append  (cost=0.00..216.68 rows=3 width=4)              (actual time=0.726..1.419 rows=1
loops=5)       ->  Seq Scan on rpu1  (cost=0.00..0.00 rows=1 width=4)                              (actual
time=0.000..0.000rows=0 loops=5)              Filter: (pu1.c = c)        ->  Foreign Scan on _cu11
(cost=100.30..108.34rows=1 width=4)                                   (actual time=0.602..0.603 rows=1 loops=5)
-> Foreign Scan on _cu12  (cost=100.30..108.34 rows=1 width=4)                                   (actual
time=0.566..0.566rows=0 loops=5)Planning time: 4.452 msTotal runtime: 7.663 ms
 
(15 rows)

-- 
Kyotaro Horiguchi
NTT Open Source Software Center