Thread: BUG #6286: Table Partitioning - SQL/MED - interaction broken
The following bug has been logged online: Bug reference: 6286 Logged by: Greg Fausak Email address: greg@named.com PostgreSQL version: 9.1.1 Operating system: Linux Description: Table Partitioning - SQL/MED - interaction broken Details: 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
"Greg Fausak" <greg@named.com> writes:
> 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;
Creating rules on inheritance children isn't supported, and you should
not hold your breath waiting for it to be.
We probably ought to have something in there to throw an error ...
regards, tom lane
On Thu, Nov 3, 2011 at 10:57 AM, Greg Fausak <greg@named.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=3Don, performed on M will sele= ct > the correct partitioned tables. =C2=A0Partitioning 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. =C2=A0Perfect, 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. =C2=A0But, 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 return= ed > 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.
On Thu, Nov 3, 2011 at 5:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Greg Fausak" <greg@named.com> writes: >> 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; > > Creating rules on inheritance children isn't supported, and you should > not hold your breath waiting for it to be. > > We probably ought to have something in there to throw an error ... Probably not for rules in general, but we shouldn't let people turn tables into views if they are involved in table inheritance, as either a parent or a child. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Right. Thanks for all the feedback guys. I was just trying to figure out how to do sql/med with partitioned tables. Can't go there, got it. -g On Fri, Nov 4, 2011 at 11:14 AM, Robert Haas <robertmhaas@gmail.com> wrote: > On Thu, Nov 3, 2011 at 5:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Greg Fausak" <greg@named.com> writes: > >> 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; > > > > Creating rules on inheritance children isn't supported, and you should > > not hold your breath waiting for it to be. > > > > We probably ought to have something in there to throw an error ... > > Probably not for rules in general, but we shouldn't let people turn > tables into views if they are involved in table inheritance, as either > a parent or a child. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Nov 3, 2011 at 5:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> We probably ought to have something in there to throw an error ...
> Probably not for rules in general, but we shouldn't let people turn
> tables into views if they are involved in table inheritance, as either
> a parent or a child.
Well, what I had in mind was disallowing any rules to be attached to an
inheritance child, because they won't get expanded. However, you have a
point I guess: someone could conceivably want to have a rule that only
takes effect when a child is accessed directly.
regards, tom lane
On Fri, Nov 4, 2011 at 1:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Thu, Nov 3, 2011 at 5:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> We probably ought to have something in there to throw an error ... > >> Probably not for rules in general, but we shouldn't let people turn >> tables into views if they are involved in table inheritance, as either >> a parent or a child. > > Well, what I had in mind was disallowing any rules to be attached to an > inheritance child, because they won't get expanded. =A0However, you have a > point I guess: someone could conceivably want to have a rule that only > takes effect when a child is accessed directly. Right. We've occasionally talked about deprecating non-SELECT rules anyway, on the grounds that the results are often surprisingly and almost never what you actually wanted. But that problem goes far beyond inheritance hierarchies. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company