Re: BUG #6286: Table Partitioning - SQL/MED - interaction broken

On Thu, Nov 3, 2011 at 10:57 AM, Greg Fausak <greg(at)named(dot)com> wrote:
>
> I create a table partition:
>
> table M
>
> table A (check range) inherits M
> table B (check range) inherits M
> ...
> table X (check range) inherits M
>
> ASSERT: a query with constraint exclusions=on, performed on M will select
> the correct partitioned tables.  Partitioning seems to work fine.
>
> Second part, create a foreign table that is a file:
> create server ES foreign data wrapper file_fdw;
> create foreign table Y ( columns like table M ) server ES options (format
> 'csv', file '/tmp/x', ...);
>
> ASSERT: a query against table Y (which has the same columns as table M)
> shows data file the file /tmp/x.  Perfect, SQL/MED works.
>
> Here is the bug when trying to use the two together with a rule to bind
> them:
>
> create rule "_RETURN" as on select to X do instead select * from Y;
>
> If I select * from X that works fine.  But, when I do a select from the
> partition table M, the EXPLAIN shows that table X is queried....but, the
> rule for select is not being fired in this context, and no data is returned
> from this partition.
>
> In other words, how do I partition a table when partitions can be external
> in the SQL/MED sense?
>
> Thanks,
>
> ---greg

Tom has already answered your question, but looking at it a bit
differently could you get away with creating a view over the M tables
UNIONed with the X table, where X has been created using LIKE instead
of INHERITS (so it is decoupled), and then querying the view? Sort of
how DIY partitioning used to work, where you just created a view over
a series of UNIONed tables.