Skip site navigation (1) Skip section navigation (2)

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

From: bricklen <bricklen(at)gmail(dot)com>
To: Greg Fausak <greg(at)named(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6286: Table Partitioning - SQL/MED - interaction broken
Date: 2011-11-03 21:35:59
Message-ID: CAGrpgQ8H+ZqfpDBKbEOWOKMQKYNV6f9CWEh7KN6X9fJQ4vOzEA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

In response to

pgsql-bugs by date

Next:From: Johann 'Myrkraverk' OskarssonDate: 2011-11-03 23:37:37
Subject: Re: Fwd: [BUGS] BUG #6285: PreparedStatement#executeUpdate gets syntax error on apostrophe
Previous:From: Tom LaneDate: 2011-11-03 21:07:25
Subject: Re: BUG #6286: Table Partitioning - SQL/MED - interaction broken

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group