pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: DefineQueryRewrite: rule plan string too big.
7+ messages / 5 participants
[nested] [flat]
* ERROR: DefineQueryRewrite: rule plan string too big.
@ 1970-01-01 00:00 Haller Christoph <ch@rodos.fzk.de>
0 siblings, 1 reply; 7+ messages in thread
From: Haller Christoph @ 1970-01-01 00:00 UTC (permalink / raw)
To: pgsql-sql
I tried to create a view as follows
create view sesql_userindexes as
SELECT c.relname AS TBL_NAME, i.relname AS IDX_NAME, x.indisunique AS UNIQUE_FLAG, 1+
(CASE WHEN x.indkey[1]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[2]=0 THEN 0 ELSE 1 END)+
(CASE WHEN x.indkey[3]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[4]=0 THEN 0 ELSE 1 END)+
(CASE WHEN x.indkey[5]=0 THEN 0 ELSE 1 END)+(CASE WHEN x.indkey[6]=0 THEN 0 ELSE 1 END)+
(CASE WHEN x.indkey[7]=0 THEN 0 ELSE 1 END) AS IDXCOL_TOTAL, x.indkey AS COL_SEQ
FROM pg_index x, pg_class c, pg_class i
WHERE ((c.oid = x.indrelid) AND (i.oid = x.indexrelid))
and not (c.relname ~* 'pg_') ;
I received the error message
ERROR: DefineQueryRewrite: rule plan string too big.
If do the SELECT without creating the view, it works as intended.
I have to mention I am still working on version 6.1 (shame on me).
Is this fixed in the upgrade versions?
Or does anybody have an idea to word it smaller?
Thanks in advance, Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
* ERROR: DefineQueryRewrite: rule plan string too big.
@ 1999-07-23 20:45 John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
0 siblings, 1 reply; 7+ messages in thread
From: John M. Flinchbaugh @ 1999-07-23 20:45 UTC (permalink / raw)
To: pgsql-sql
ERROR: DefineQueryRewrite: rule plan string too big.
i get this error trying to build this massive view to join about 15 tables
of data together. the error sounds reasonable enough, but i'm getting
confused trying to break it down into smaller views and then combining
them.
the view basically just pulls together 15 tables of data while renaming
columns to be meaningful when viewed together, and of course associating
foreign keys with their primaries.
here is the query if it should prove useful:
create view allinfo as select b.name as building_name,b.description as
building_description,b.location as building_location,b.address as
building_address,f.location as floor,r.name as room_name,r.location as
room_location,r.dept as room_department,rt.name as room_type,h.mac as
mac,h.name as hardware_name,h.ip as ip,h.mutag as mutag,rack.location as
rack,rack.description as rack_description,h.pos as pos,h.serial as
serial,h.inuse as inuse,m.name as model_name,m.description as
model_description,m.model as model,c.name as model_class,manf.name as
manufacturer from building as b,floor as f,room as r,roomtype as
rt,hardware as h,dept as d,model as m,class as c,manufacturer as manf,rack
where b.number=f.building and f.number=r.floor and r.number=rack.room and
rack.number=h.rack and m.class=c.number and m.manufacturer=manf.number and
r.dept=d.number;
how would be the best way to build smaller views and combine them into
larger views?
thanks.
____________________}John Flinchbaugh{______________________
| -> glynis@hjsoft.com <- john@cs.millersv.edu |
| glynis@netrax.net http://www.hjsoft.com/~glynis/ |
~~Powered by Linux: Reboots are for hardware upgrades only~~
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] ERROR: DefineQueryRewrite: rule plan string too big.
@ 1999-07-25 12:54 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
0 siblings, 1 reply; 7+ messages in thread
From: Herouth Maoz @ 1999-07-25 12:54 UTC (permalink / raw)
To: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>; pgsql-sql
At 23:45 +0300 on 23/07/1999, John M. Flinchbaugh wrote:
> create view allinfo as select b.name as building_name,b.description as
> building_description,b.location as building_location,b.address as
> building_address,f.location as floor,r.name as room_name,r.location as
> room_location,r.dept as room_department,rt.name as room_type,h.mac as
> mac,h.name as hardware_name,h.ip as ip,h.mutag as mutag,rack.location as
> rack,rack.description as rack_description,h.pos as pos,h.serial as
> serial,h.inuse as inuse,m.name as model_name,m.description as
> model_description,m.model as model,c.name as model_class,manf.name as
> manufacturer from building as b,floor as f,room as r,roomtype as
> rt,hardware as h,dept as d,model as m,class as c,manufacturer as manf,rack
> where b.number=f.building and f.number=r.floor and r.number=rack.room and
> rack.number=h.rack and m.class=c.number and m.manufacturer=manf.number and
> r.dept=d.number;
First, I do believe that in the FROM clause, you don't use "as" to create
table aliases. This is done only in the target list. For table aliases you
just put the new name:
FROM building b, floor f, room r, roomtype rt, hardware h, dept d etc.
Now, here is a (braindead) workaround which doesn't require you to plan a
lot, and may help you reduce this plan into two views.
View number 1 - this same view, only with short field names:
CREATE VIEW internal1 AS
SELECT b.name as f1, b.description as f2, ....
FROM ....
WHERE ....;
View number 2 - straight select from internal1, only renaming the fields to
their longer names:
CREATE VIEW allinfo AS
SELECT f1 as building_name, f2 as building_description, f3 as ...
FROM internal1;
This may get you just under the threshold and save you a lot of thinking.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] ERROR: DefineQueryRewrite: rule plan string too big.
@ 1999-07-25 16:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 1999-07-25 16:00 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>; pgsql-sql
Herouth Maoz <herouth@oumail.openu.ac.il> writes:
> At 23:45 +0300 on 23/07/1999, John M. Flinchbaugh wrote:
>> [ how to avoid "rule plan string too big" ? ]
> First, I do believe that in the FROM clause, you don't use "as" to create
> table aliases. This is done only in the target list.
"AS" is an allowed noise word in FROM, see the production for table_expr
in gram.y. However, this sort of hacking on the text source of the
query isn't going to make much if any difference to the length of the
resulting parse tree, which is what John's actually running into trouble
with. The real problem is that a parse tree for a ten-way join with
this many output columns just plain has a lot of nodes :-(, and the way
that they're being stored in rules is quite verbose. (I think Jan is
going to introduce compression of rule plan strings shortly, but that's
not going to help John right now.)
He might have some luck by splitting the query into two subqueries,
one that joins half the tables and the other one joining the other half,
then a top query that joins those two. (The trick here is figuring
out how to split the tables into two groups that can be joined without
reference to the other group and without producing too many useless
tuples.) The top query is still going to be tight, because the number
of targetlist nodes (output columns) won't change, but reducing the
number of tables it references to two views and simplifying its WHERE
condition down to one or so clauses will both help eliminate nodes.
I originally thought that this wouldn't work because the final parsetree
for the top query would incorporate the other two parsetrees and still
be too big. But it looks like it doesn't --- when I experimented just
now, the stored parsetree for a view referencing another view treated
the sub-view as just a table with no inner structure. I suppose the
expansion doesn't happen until plan/optimize time.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] ERROR: DefineQueryRewrite: rule plan string too big.
@ 1999-07-26 09:28 Jan Wieck <wieck@debis.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: wieck@debis.com @ 1999-07-26 09:28 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: herouth@oumail.openu.ac.il; glynis@butterfly.hjsoft.com; pgsql-sql
Tom Lane wrote:
> I originally thought that this wouldn't work because the final parsetree
> for the top query would incorporate the other two parsetrees and still
> be too big. But it looks like it doesn't --- when I experimented just
> now, the stored parsetree for a view referencing another view treated
> the sub-view as just a table with no inner structure. I suppose the
> expansion doesn't happen until plan/optimize time.
It happens during rewrite time. The querytree stored for
rules is just the UNrewritten output of the parser. That's
why the rewriter is recursively calling itself with it's own
output.
So splitting up such a huge view into a tree of views (if
possible) is a solution that must not result in a different
final querytree (after applying all view rules). It just
takes a little more time for the rewrite step - but I cannot
estimate how much more.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] ERROR: DefineQueryRewrite: rule plan string too big.
@ 1999-07-28 09:29 John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: John M. Flinchbaugh @ 1999-07-28 09:29 UTC (permalink / raw)
To: pgsql-sql
On Sun, 25 Jul 1999, Tom Lane wrote:
> > At 23:45 +0300 on 23/07/1999, John M. Flinchbaugh wrote:
> >> [ how to avoid "rule plan string too big" ? ]
> He might have some luck by splitting the query into two subqueries,
> one that joins half the tables and the other one joining the other half,
> then a top query that joins those two. (The trick here is figuring
> out how to split the tables into two groups that can be joined without
> reference to the other group and without producing too many useless
> tuples.) The top query is still going to be tight, because the number
> of targetlist nodes (output columns) won't change, but reducing the
> number of tables it references to two views and simplifying its WHERE
> condition down to one or so clauses will both help eliminate nodes.
i broke that huge view into a bunch of smaller ones successfully, but the
top view which pulled together 2 smaller views (which each pulled together
smaller parts,etc), and i got the same error. it seems to still get hung
up on the number of columns the view was returning, so i had to drop a few
of the less useful columns to get it to create the view successfully.
____________________}John Flinchbaugh{______________________
| -> glynis@hjsoft.com <- john@cs.millersv.edu |
| glynis@netrax.net http://www.hjsoft.com/~glynis/ |
~~Powered by Linux: Reboots are for hardware upgrades only~~
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: ERROR: DefineQueryRewrite: rule plan string too big.
@ 2001-09-07 14:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Haller Christoph <ch@rodos.fzk.de>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2001-09-07 14:19 UTC (permalink / raw)
To: Haller Christoph <ch@rodos.fzk.de>; +Cc: pgsql-sql
Haller Christoph <ch@rodos.fzk.de> writes:
> I received the error message
> ERROR: DefineQueryRewrite: rule plan string too big.
> If do the SELECT without creating the view, it works as intended.
> I have to mention I am still working on version 6.1 (shame on me).
Indeed.
> Is this fixed in the upgrade versions?
Yes.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-09-07 14:19 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1970-01-01 00:00 ERROR: DefineQueryRewrite: rule plan string too big. Haller Christoph <ch@rodos.fzk.de>
2001-09-07 14:19 ` Re: ERROR: DefineQueryRewrite: rule plan string too big. Tom Lane <tgl@sss.pgh.pa.us>
1999-07-23 20:45 ERROR: DefineQueryRewrite: rule plan string too big. John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
1999-07-25 12:54 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-07-25 16:00 ` Re: [SQL] ERROR: DefineQueryRewrite: rule plan string too big. Tom Lane <tgl@sss.pgh.pa.us>
1999-07-26 09:28 ` Re: [SQL] ERROR: DefineQueryRewrite: rule plan string too big. Jan Wieck <wieck@debis.com>
1999-07-28 09:29 ` Re: [SQL] ERROR: DefineQueryRewrite: rule plan string too big. John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox