Thread: RewriteDefine.c : Rule size
Hello, I'm using Postgres 6.4.2. I want to create a view, but a message appear :ERROR: DefineQueryRewrite: rule plan string too big. What could be the solution to avoid the problem? In fact, I have 31 columns in a table, and i want to make a view on this table (with a line restrict = WHERE clause). I watched the RewriteDefine.c code and saw : #define RULE_PLAN_SIZE BLCKSZ I thought changing the value of this size, but I don't find the place to modify the BLCKSZ value. But, before doing the modification and recompile, I would like to have your opinion on the problem...perhaps is there an easier way to resolve the problem. Thank you for answering -- ERTZ Olivier - ertz@illite.u-strasbg.fr - Tel 03 88 35 85 87 ------------------------------------------------------------ Etudiant DESS Informatique | Stagiaire EOST/ULP-CGS UniversiteLouis Pasteur | Ecole et Observatoire des STRASBOURG - France | Sciences de la Terre ------------------------------------------------------------ World Wide Web : http://dess-info.u-strasbg.fr/~ertz ------------------------------------------------------------
>
> Hello,
>
> I'm using Postgres 6.4.2.
> I want to create a view, but a message appear :
> ERROR: DefineQueryRewrite: rule plan string too big.
> What could be the solution to avoid the problem? In fact, I have 31
> columns in a table, and i want to make a view on this table (with
> a line restrict = WHERE clause).
That's a well known, long standing problem which is still not
fixed in v6.5. I'm planning to fix it for v6.6 - though that
doesn't help you.
> I watched the RewriteDefine.c code and saw :
> #define RULE_PLAN_SIZE BLCKSZ
> I thought changing the value of this size, but I don't find the
> place to modify the BLCKSZ value.
> But, before doing the modification and recompile, I would like to
> have your opinion on the problem...perhaps is there an easier way to
> resolve the problem.
Never did it, but the BLCKSZ is defined in include/config.h.
That is a file generated from config.h.in, so be sure to
change it there and reconfigure before recompiling.
And if you change the blocksize, be sure to run initdb after
because your databases will not be binary compatible any
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) #
>> I'm using Postgres 6.4.2. I want to create a view, but a message appear : >> ERROR: DefineQueryRewrite: rule plan string too big. What could be the >> solution to avoid the problem? In fact, I have 31 columns in a table, and >> i want to make a view on this table (with a line restrict = WHERE clause). I get the same error from 6.5 when I create rules that are apparently too complex. My table has 25 or so columns.. >> I watched the RewriteDefine.c code and saw : #define RULE_PLAN_SIZE BLCKSZ >> I thought changing the value of this size, but I don't find the place to >> modify the BLCKSZ value. But, before doing the modification and >> recompile, I would like to have your opinion on the problem...perhaps is >> there an easier way to resolve the problem. wieck> Never did it, but the BLCKSZ is defined in include/config.h. That wieck> is a file generated from config.h.in, so be sure to change it there wieck> and reconfigure before recompiling. Be careful here. We changed BLCKSZ and found some scary results: First we multiplied it * 4. This improved the ability to handle complex rules, but didn't go far enough... (it still wouldn't handle all of what we wanted it to). So we multiplied it * 8. Initdb complained about "float4 not defined". (As I recall.. I'm not where the error message is and thus this may not be 100% accurate...). We quickly changed it back to the original size and are implementing alternative rules... I can understand if it doesn't have enough working space to do what I ask (or whatever the details are behind "rule plan string too big") but if changing BLCKSZ breaks builtin types -- I don't want to mess with it!!!
Jim Rowan <jmr@computing.com> writes:
> First we multiplied it * 4. This improved the ability to handle complex
> rules, but didn't go far enough... (it still wouldn't handle all of what we
> wanted it to).
> So we multiplied it * 8. Initdb complained about "float4 not
> defined".
There is a poorly-documented restriction that BLCKSZ can't exceed 32K
(IIRC, this is because block offsets are stored in signed shorts somewhere).
Probably would be a good idea to mention this in the comments in
config.h.in.
For the long run it would be nice to remove the restriction. I have no
idea whether that would be easy or hard. Ideally we could do something
like
#if BLCKSZ > 32767typedef int BlockOffset;
#elsetypedef short BlockOffset;
#endif
but finding all the places that need to use the typedef might be a pain.
regards, tom lane
> There is a poorly-documented restriction that BLCKSZ can't exceed 32K > (IIRC, this is because block offsets are stored in signed shorts somewhere). > Probably would be a good idea to mention this in the comments in > config.h.in. > > For the long run it would be nice to remove the restriction. I have no > idea whether that would be easy or hard. Ideally we could do something > like > > #if BLCKSZ > 32767 > typedef int BlockOffset; > #else > typedef short BlockOffset; > #endif > > but finding all the places that need to use the typedef might be a pain. Added comment to config.h.in, and TODO item: * Allow BLCKSZ <= 64k, not <= 32k -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> For the long run it would be nice to remove the restriction. I have no > idea whether that would be easy or hard. Ideally we could do something > like > > #if BLCKSZ > 32767 > typedef int BlockOffset; > #else > typedef short BlockOffset; > #endif > > but finding all the places that need to use the typedef might be a pain. We could easily to to unsigned short on this. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
>> I'm using Postgres 6.4.2. I want to create a view, but a message appear : >> ERROR: DefineQueryRewrite: rule plan string too big. What could be the >> solution to avoid the problem? In fact, I have 31 columns in a table, and >> i want to make a view on this table (with a line restrict = WHERE clause). I get the same error from 6.5 when I create rules that are apparently too complex. My table has 25 or so columns.. >> I watched the RewriteDefine.c code and saw : #define RULE_PLAN_SIZE BLCKSZ >> I thought changing the value of this size, but I don't find the place to >> modify the BLCKSZ value. But, before doing the modification and >> recompile, I would like to have your opinion on the problem...perhaps is >> there an easier way to resolve the problem. wieck> Never did it, but the BLCKSZ is defined in include/config.h. That wieck> is a file generated from config.h.in, so be sure to change it there wieck> and reconfigure before recompiling. Be careful here. We changed BLCKSZ and found some scary results: First we multiplied it * 4. This improved the ability to handle complex rules, but didn't go far enough... (it still wouldn't handle all of what we wanted it to). So we multiplied it * 8. Initdb complained about "float4 not defined". (As I recall.. I'm not where the error message is and thus this may not be 100% accurate...). We quickly changed it back to the original size and are implementing alternative rules... I can understand if it doesn't have enough working space to do what I ask (or whatever the details are behind "rule plan string too big") but if changing BLCKSZ breaks builtin types -- I don't want to mess with it!!!