Rules: first fix on monday
Hi,
got a lot of things working up to now. Most things on the
relation level are fixed now, including qualified instead
rules. Update rules can now correctly refer to *new* and
*current*.
Must check if instead nothing with/without qualification is
O.K. and if the regression tests are still happy with the
changes.
What I haven't touched yet are rules on the attribute level
and the update new stuff. If regression tests walk through
and the instead nothing is O.K. I'll send a first patch with
some test queries on monday. Next todo is then update new.
Have a nice wieckend :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Jan,
Whilst you are working on the rules system it would be nice if
you could look for an oportunity to store the plain text rule
definition at creation time.
If the definition were stored in a table column it would allow us
to dump and restore databases in a more complete way.
I looked at this some while ago myself but never got close to
making it work.
Keith.
jwieck@debis.com (Jan Wieck)
Show quoted text
Hi,
got a lot of things working up to now. Most things on the
relation level are fixed now, including qualified instead
rules. Update rules can now correctly refer to *new* and
*current*.Must check if instead nothing with/without qualification is
O.K. and if the regression tests are still happy with the
changes.What I haven't touched yet are rules on the attribute level
and the update new stuff. If regression tests walk through
and the instead nothing is O.K. I'll send a first patch with
some test queries on monday. Next todo is then update new.Have a nice wieckend :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Import Notes
Resolved by subject fallback
Jan,
Whilst you are working on the rules system it would be nice if
you could look for an oportunity to store the plain text rule
definition at creation time.If the definition were stored in a table column it would allow us
to dump and restore databases in a more complete way.I looked at this some while ago myself but never got close to
making it work.Keith.
Yes, that would really be nice and I had something the like
already in mind.
I'm not really sure if it's a good thing to save the rules
definition text instead of the parsetree. The advantages of
doing so would be that the rule could easily be dumped and
(more important) that the rules can have more complicated
actions (remember that the currently stored parsetrees can
quickly override the size limit of a text type).
Storing the definition text only would require to parse at
least the actions any time, a rule should be fired on a
query. Bad for performance - but must check if significant
bad.
At least I can't do this for 6.4 I think. But I'm willing to
take a look at it after.
On the other hand wouldn't it be too complicated to
reconstruct a command from the parsetree, that exactly
creates the rule. Reading a parsetree isn't fun, but after
all I did on the rewrite system I'm somewhat familiar with it
now (sometimes I see the cup in a targetlist, the coffee
machine in the rangetable and all the buttons in the
qualification when pulling a coffee out of it - think I
should I consult a psychist when the rule system is fixed
:-).
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Whilst you are working on the rules system it would be nice if
you could look for an oportunity to store the plain text rule
definition at creation time.
If the definition were stored in a table column it would allow us
to dump and restore databases in a more complete way.Yes, that would really be nice and I had something the like
already in mind.
I'm not really sure if it's a good thing to save the rules
definition text instead of the parsetree. The advantages of
doing so would be that the rule could easily be dumped and
(more important) that the rules can have more complicated
actions (remember that the currently stored parsetrees can
quickly override the size limit of a text type).Storing the definition text only would require to parse at
least the actions any time, a rule should be fired on a
query. Bad for performance - but must check if significant
bad.
How about storing both an internal form and the plain text source? That
way you can use the fast form internally and dump the plain text...
- Tom
Tom, Jan,
These were my thought too.
We could add another column to "pg_rewrite" which contained the
source for the rule. This could be used by pg_dump to dump the
rule creation statement, or by the user to see what the rule
actually does.
Perhaps someone who knows how to graft in new columns could do
that now before we finalise 6.4, even if we don't use it straight
away it would serve as a marker.
I believe a dump/restore is required for 6.3 to 6.4 so we might as
well get the catalog change in sooner rather than later.
Keith.
Thomas G. Lockhart <lockhart@alumni.caltech.edu>
Show quoted text
Whilst you are working on the rules system it would be nice if
you could look for an oportunity to store the plain text rule
definition at creation time.
If the definition were stored in a table column it would allow us
to dump and restore databases in a more complete way.Yes, that would really be nice and I had something the like
already in mind.
I'm not really sure if it's a good thing to save the rules
definition text instead of the parsetree. The advantages of
doing so would be that the rule could easily be dumped and
(more important) that the rules can have more complicated
actions (remember that the currently stored parsetrees can
quickly override the size limit of a text type).Storing the definition text only would require to parse at
least the actions any time, a rule should be fired on a
query. Bad for performance - but must check if significant
bad.How about storing both an internal form and the plain text source? That
way you can use the fast form internally and dump the plain text...- Tom
Import Notes
Resolved by subject fallback
We could add another column to "pg_rewrite" which contained the
source for the rule. This could be used by pg_dump to dump the
rule creation statement, or by the user to see what the rule
actually does.Perhaps someone who knows how to graft in new columns could do
that now before we finalise 6.4, even if we don't use it straight
away it would serve as a marker.I believe a dump/restore is required for 6.3 to 6.4 so we might as
well get the catalog change in sooner rather than later.
Adding a column will take away from the already-tight space needed
to keep the plan.
Perhaps a better way is to have a new non-cached system table that
would be joined to pg_rewrite to show the plain-text plan when needed.
Rather than require the user to know this join, postgres could (or
should) have some system views (ala Oracle) to hide the underlying
table structures and prevent any user except the superuser from
modifying a system table without using a postgres command.
darrenk
[Charset iso-8859-1 unsupported, filtering to ASCII...]
We could add another column to "pg_rewrite" which contained the
source for the rule. This could be used by pg_dump to dump the
rule creation statement, or by the user to see what the rule
actually does.Perhaps someone who knows how to graft in new columns could do
that now before we finalise 6.4, even if we don't use it straight
away it would serve as a marker.I believe a dump/restore is required for 6.3 to 6.4 so we might as
well get the catalog change in sooner rather than later.Adding a column will take away from the already-tight space needed
to keep the plan.Perhaps a better way is to have a new non-cached system table that
would be joined to pg_rewrite to show the plain-text plan when needed.Rather than require the user to know this join, postgres could (or
should) have some system views (ala Oracle) to hide the underlying
table structures and prevent any user except the superuser from
modifying a system table without using a postgres command.
Obviously the real fix is for larger block sizes.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
We could add another column to "pg_rewrite" which contained the
source for the rule. This could be used by pg_dump to dump the
rule creation statement, or by the user to see what the rule
actually does.Perhaps someone who knows how to graft in new columns could do
that now before we finalise 6.4, even if we don't use it straight
away it would serve as a marker.I believe a dump/restore is required for 6.3 to 6.4 so we might as
well get the catalog change in sooner rather than later.Adding a column will take away from the already-tight space needed
to keep the plan.Perhaps a better way is to have a new non-cached system table that
would be joined to pg_rewrite to show the plain-text plan when needed.Rather than require the user to know this join, postgres could (or
should) have some system views (ala Oracle) to hide the underlying
table structures and prevent any user except the superuser from
modifying a system table without using a postgres command.darrenk
I don't think we should save plans in persistant storage at all. We did this
at Sybase and it was a major headache. Suddenly you can't dump/restore across
architectures. Minor changes to the structure of a plan require wholesale
upgrades or boatloads of compatibility code. Much more shared update access to
catalogs. Somehow the plan read and write code got very complex and tended
to screw up the saved plans which was no fun at all to debug (since the crash
comes a lot later than the damage) All this to save a bit of parsing.
You can get pretty much the same benefits without most of the problems by just
caching the plans in memory and reusing them. If we had a sharable cache that
would be even better.
-dg
David Gould dg@illustra.com 510.628.3783 or 510.305.9468
Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612
- If simplicity worked, the world would be overrun with insects. -
I wrote:
Jan,
Whilst you are working on the rules system it would be nice if
you could look for an oportunity to store the plain text rule
definition at creation time.If the definition were stored in a table column it would allow us
to dump and restore databases in a more complete way.I looked at this some while ago myself but never got close to
making it work.Keith.
Yes, that would really be nice and I had something the like
already in mind.[...]
On the other hand wouldn't it be too complicated to
reconstruct a command from the parsetree, that exactly
creates the rule. Reading a parsetree isn't fun, but after
all I did on the rewrite system I'm somewhat familiar with it
now (sometimes I see the cup in a targetlist, the coffee
machine in the rangetable and all the buttons in the
qualification when pulling a coffee out of it - think I
should I consult a psychist when the rule system is fixed
:-).Jan
To demonstrate that it really isn't that complicated as it
looks, here is a C function that if defined in the backend as
CREATE FUNCTION get_ruledef(name)
RETURNS text AS '.../get_ruledef.so'
LANGUAGE 'C';
can be used to see a textual representation of the rule given
as argument.
It is able to handle ALL the rules I defined in the test
suite for my first fix (sent yesterday). In addition I tested
a
... WHERE (att, att) IN (SELECT ...)
in the definition of a view. Worked too. The output can
easily be fed back into a psql and produces exactly the same
pg_rewrite entries that where there before.
It is not complete (aggregates are not handled up to now and
there might be some other expression constructs, that could
occur). But it's a starting point I think. At least we could
produce a view
CREATE VIEW pg_rule AS
SELECT rulename, get_ruledef(rulename) AS definition
FROM pg_rewrite;
Now pg_dump could output views as commands for regular tables
and add commands for the rules. It must omit the rule
_retpg_user in the dump, but that's all. All views and rules
are restored.
pg_dump should not select all rules at once, because if only
one of the rules cannot be converted back to it's definition
string, none could be dumped. Instead it should get a list of
all the rule names in pg_rewrite (without _retpg_user) and
then loop over it getting one at a time.
As soon as anybody reports an error (pg_dump cannot save rule
...) we know what case isn't handled by get_ruledef() and
could implement it.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
begin 644 get_ruledef.tar.gz
M'XL(`,:UV34"`^P\^7?;-M+YE?HK$#6I)44^<[2U-]FG2'+,_63)E61WNVV?
M'T5!-C<4R?*(H^W+__[-#``2I*C#3=)CMWIY,0D",X.9P5P`></CZS!Q^93/
M]A]\IA][=O#5\^?L`8/?TP/Z>W@D_LK?`6,OGK]X\?3IT8L7<'UX^/3I\P?L
M^8/?X)=$L14R]B"XB7YVU_2;3JP'_X6_&TW^Y]9;/G-<_JEQ'!X<O'CV;*7\
M#[]Z_I64_]'1BV='T/+T\.#I`W;PE_P_^^^+W4_UJWQ1^8(I%8)+^*D[-O-#
MIFE:K<YFB6?'CN_1*+/3[8_-4[/=&IN#OAC[Z(Q;4QX>L_TD"O==W[;<_2BT
M]TE.XLJ%?]GML#U*-;CYCAWN';'#;[[Y>O_@Z_W#I^SPQ?'SI\?/OF8T@'7?
M!^P1XOYTTZ>9C+GKLCE0P>YN><A9?,M9X$?Q3<@1;>0GH<TCYCKO@$65T;#=
M,8<O5TY1P#0]VTVF`M:-ZT\LEUG>%,"&,8L";CLSQTY9'<$01PYX5!,(ZBE?
M]L3X=3T0;*72/NVUWHR>O&2[YJ-:SWQ]\2WVHCLU0L(`&MDIB'<VOPGW;O,#
M5=>)9;_EWK12Z75:G0X^[.6@NL'/8JJ=WN#U/Y@3T5RG"\^:.\`4=['K^J`,
M4^9/_LWMF!&A,("ZO]0UZU&MTQM=GIZ:_ZQ7*O!P]-+0GN[YE4JKUWMI8#=X
M6A=87R>..R64T:T5IFC@&>`^!B;!&.BJ!AW#<`0M1[==;GD,+FV\.*X8X9SM
MSM0H>==`U(X'M@8APG\5`&+V1V/LQ,1E[QI8<CVX&(^P1>#"B^YHC&PBCA$[
M,^I+2,I-]S@W>]"GM?8_U_<WL_\O7CP[?)[Z_R/R_\\@`OC+_O\6O_W&)_E5
M6(/EU,?89:?2Q+/8QV?,8O@T8O#<\1SQA+^/8:AA&(SY2<S\&7/B'5C^20`^
MA!'8@G.`EA7>09C-$J\0N->W8#%='N[9Y!D.4\]P\!4$F\=/OP'GD/,,`C7\
MQK=@C2)_%M^!94#+9/O!(G1N;F.P$Y,%^P>L_>\<;K]EN^S,FD^2\&9/'\V9
ME<2W8!_1'4#_F]#RXH@%/)P[480\0/:P).)-1K#AS]R?.K-%4\*8.E$<.I,D
MQAYH^,%[V-R+T"'HM.$C!T!/?3N9<R^VR+T*&.A_+6_!@B0$;P2`@M!_YZ!%
MC6^MF/'W@,/Q;K+),<^/'?14`%G""'EL.1X,80X0#2:,Z'6@CZ"*(`F:Q&#D
MEG024PGC'0\G0-<<02`]Z=R`U&B/]7UV%SIQS.$I>$R.TVBJZ2I^P%18Z"\L
M-U[`Q#AGB"?D/R<.FFXU4]"D.&6^\Q]X`BR.]B2,<^0PN!;"BP+(LW)N+=B$
MLYRL4=@(T@DE#"E7,7F/8Q>8-_+:=?T[ZBM)1\[&('!4_<B&^:;\0*4H"@/'
M>?Q.CD!R&'J6T%T@YZ=(->)*98O]9TX8Q2RP;CA.G%OV+?G(+`A9,"L(W(6F
MFF:?]0>L>P6+BXW.P`VQ\5F7M2['9X,A@W\=<S0>FJ\OQX/AB+WNLI[9>MWK
MLO&`M?K?2Q@7K>'X>\9.H3OT'W;;XR9!SJY'%]VVV>J)YC8M97$W&$H8[4%_
MU/WV$M=XJP?WG=9YZTUWQ%AK:([,_AOH>CF&_TX9$<@N1UUU9XXDC-'@=/Q=
M:]AM,G,\8IU!^_(<$:&]:.)<@&+6Z0[-*VBZ`M@`:-@=G#9I^HH?"H%D`3MK
M775AXMT^:W6NS%&W@UBQQ\5@-#)?FST3Y@Y-H\OVF80A:-?7OPZPU>^P/%L%
M>]"T]7K?,WK8[K7,<\8T)L/$ABW@3G?4)";V+CO`%KA^C7SI#^"_GGENCH%`
MD$Y3(C7/+WIFM[,$0_#NO#MLGT%#2TP#!IV:XWYW-)+";`G9FNW+7DL)ZN)R
M"#,''M-$^H/^KMD_'0(I763VGA!(*@H&UQ?#P16('!BGF-SJLVIK!,^J['5K
M9,*,$):F>&5,(D$`0@EC\+IGOB'9(H,'2)G`@^NZ9?;'W7ZKW^Z2]EU>7`R&
MJ(B7%YW6&#@H873[9]@'"1^1AIP/.JF+&4D!?I+?/@2)*MK^6Q1/'7_O]E6^
MR74F2VT6N)%<6^*!J9SFVV:V%[O%H2%8&VS+&JO\/;>3V`_WH\#9NZUJ3VQ_
M/@?[%>W#L)L;\(^YIV"6W6B?N_Y-6?L$HF9P&E'^F>>#C=NG__,/_`#L/ACB
M<-]V+;+%)3"C162#\>)ES]SRAV`.+:`0O/\U`(ZBE4_C15`8*E*6:J52V6\P
M+9,#'7DC\JPI#,?;W,/]2H3.U68V9`NL@4$-)"K\1#6_\YTI:P2NY5U#V(./
MV4O6O^SU3O(#?TYXN-"Z5$?='IA-0'<Z')Q#.'(=<G2'G'V']HHI1-#UT6'U
M9`W9*KF.2FC'D(LU]&2\#S`[,%&8"H*OET+N88BU%G!N;I@"IP@@5@O&&-'1
M%""V:S*Z[8`W%$VQ72\P!PD4#$((W^*5Y%A9SXB[D+#=8P#H+0_O,R`)0!?X
M/09,@:1[#4!&7//W05CKP5('8<36Q(7HP/%B%B+%4_Z^"2$2:&X#UU>337S?
M9>^L,`@AHGY?!A/EM1DFA+S0'[MM`S/>ALPQ&#`>=[T8IQR[6Q%K@U[%`G0;
M+UF#6MY9;BF[N$OAVS7J;&T`2PY:G&E95RN6`72A+Q%]Q."QE\RS@40IV!G[
M[;4SNPYA&)BK:6VU'/!)E`3E"T=;:9#F[+*.#U$ZA<\8.$)0G800[6J9D"%#
M:=M/W"F&H8B=61`*,B20@N*LIP\@[)!#.P6"B&GEHJ^L6_657RH434)/PVC(
M7B?4!MV2N6&`2*,?#G\2;<A=P_`2UXU^.))MP`<@"IQ,:(N&=.$;AESYHCU=
M_;(]MG6@C7@>Z`#!Z@%G\;[`7/PUV'=<1-]J^I!^H)&,_#D7X>^4\P"RHCM/
MC5B"L4]_-?N*?R`?K:4&<25VT%0/2U(@B-&%"9F#!T%XN`&3,V,UZ(T:CX-K
M=?;P)0Z_'OS?-<3#??`"]8K:&T'_6^L.AX-ADU4U"1XSV_(PX[!+2:BN)7O@
M:7D#EM@@">&!I2J6X+L0G.O[;Y,`/1&D*/:>QG'0N*EJ9SZD=31.0\^PV(6=
M(!]),`-2@ZG,)CTYX@`%I[4F<S<";-F0>D:0XBRA7L/0O,,5'K?.?JD8L-Y1
M=]'_"_TUT$,;Y*&!1]FS@Y_04;?.NP.S<Y+RGY@AQ"-Y5,MY[B8[;#(%`YAN
M*&)2(BK&DAQU:'4VLR!9$_/_L?HX^K%:;;(<#@1;""@00F2]X]A,Z.J"Y`]K
MI/Z&B_12"RVHTD*8*046]F,MK\D,$*LN?%B@/`2H9"%J:@%)2H1QH)ZU;&TI
MP?R=[7@[[)CML!V]^R%VW_GQ0#:2,9&SQ3`VR(F9^!XUQ5"0@T2,`A`#LV4E
M8BO4A])U)24@*U6E_'D<@50*4_R06\ZHJ:"W``?P'BI<AGJ,5:_H-K?83\V^
M.3HK4Y#<0L^-%J16ZYF&RGY*26$U^W;MJC4\ZPQ'_V)/B"\&W(_,?W5KLG,=
M>NM=1`](DEII#R&ZG5T0A2']5,XM2$V3EEUBARL*"'9?@=]&Z9]DG6)[J1-<
M84WD9`NE72H>TB9($J,[!3WTF4IS(C;G<S]<;-!C\#)`3BY03</3+"C%GB!R
MZ`FI%5S48%@=&*9XETUO20+06P(HX3TY-7P&M-K!HE9D?A/I:Q+JW1396I/>
M<2+E"6:A/_\5[FB-@FYV1BMT=#6]72M:P-R<R-M!&?Y]DW<N:."'LF!+ER9%
M6Q`<80P9)K90HO:PVQIWV?"RUV4R^4UC*2H=LAOG'?>6+,"F?*=R_X1'!EP4
M\S0FR4P+@?@[RE9%"[HO;*'L-HV+CK`)`UO1@C&KX437TNMJL!K0[^?$<@M-
M%N5QHA$#>:,A6B)T@69/B[]FGI_#@<9VG5Q;J/P8CT:!92O7PJ7\P#:2_UK:
M`U@K>^`.D"67U=>'WQS5MS$8:=@/:8>;@.V@99&9$BG8M9AA\G)1SR!'F/"P
M)L4'ZB^EI"RQO$5OAURNXR!8(1/'`_1+IJ6)H)OL2\%/-9\-Z$@%-'QTCPA!
M1SX'/N2@A@YO$1NJWR='ERFOPIBU(%+4OL\Q1UP;VASQ5G8'1*0XY7BT6$/B
MTX:+:L]6+*95IS-9[)G]>A)2`(((U2-[\C"+C959S]:^S.KKHI`X]C'/S<9N
M$6-FF^DK=OO6KC=`B]X0%CRP1S?6NB\92^`BT=-&6K$868C2]$=5UAJQ07\)
M'@>KG\49N*4T2_>3%(8[)[9OB9.XT"FWL*V(LYW#G6,CAT16\L8#P@/>90)Y
MS5M,-L2`H^(`49]>/>!I<8#9'W6':S`\*P[H`$FE&)0.@*"LQ(V/#7BXVLT3
M=QY'[-:*6.)%28#G18!/DG]H`!]/(5!&#(:20U,91RF0%'E>F4BNLJQ3(@IH
M+)'U<BU(&<9Z/:_]:+U>L0-=[W,<VB/&%&$7BD<*>%,9Q/K2FC!G&?'()L@$
M!7?0.*3;CI"[3''#5@_"4@.B5FAF4:I5+;<1D:A\6"]."I>LT1`^WZ"JHR&J
MCJ@=$EQQ>1.@$WR,14HP`[):67>I1%"3!J*NZ4M^58D2=2D'L\(F0=]]I4J&
M!TUB"01'P\MN@8]YZ)W!TH+%$S/=5H>]Y8L[/YR""9RQR*_K[,P<B`A@EU80
M`=`!GSH>'C?2S)>RC!I8X/U-?)NR!-A/N9ZAAU$EG,\AKQ&GP+C07JV$U60I
MS%]$2KA>'`BB;#ED-6=1;"[K5ST1POI0(*Q^HGS1!PCVP9+\(@H:Q4F#BAZD
M9.8@]`?C,[/_YD2`3X%@OR)+-D\Q^K@YRBEN\EGC6PM/G3CQ=CD(!>L?5A5[
M4\HH`;FP0I@_'H`#^\6E@"D2E7F'(7;P`^P7AYSCT0B*7M.-%MG+!?7:F(:L
MV3:1^8;R8G(MRB1HG'-H[?..+)@<"Z[*B9=NM:@-#>HH[3I=:\"$>UL&MK2K
ML@TPX?J6@2WMZ6P#3+C%96!+VS>K@2F#F,*4*R`/5*V+:AY$Q5!^=WT1".NH
MCZ-CN5RDU)2_I8,GSAS2&=P<`%>YX'&U*0#J7KA$YGEJ]+*.(%L$D2N5O:@-
M19VWF`R%4OW>2H77[^?IF;,!B_$'3`ES^Q*-B*NMAFPC"G<67)E4#RWOAH\G
MKGC0@!!&RW'QGO9\]%T(:!(VL-`(03UMT(`]U$&$<C_+]URT;NCA](&.EGD;
MC;7Y]"G5Z._D+H?8ZO#96\^_8W>W#BQE5(1;N)MGYY[4V!"GR<C=,M",$$]K
M2?LB%`DK_D2^;(V)7=+6"!@0`&-?')H+8#;92L4O4>Q"YY%S_]++2$YGZ7WD
M_(?[,Y'NX0P4&-D?X_&:0]QF#OM;]AQNGSS1PR#T6B6[>%CL;+*:V+^K*YK$
MO%$<=8A+ZJEGD^3]X/R4"E&U2\$_>;+DYNZ%F2*O',Y5>(T"TKR3+AUYVNJ-
M%,D?MO&$?=`C(ATGH9VC(QF1-A64"1(\9S;C(6U*"AA49FGTN]\U2#4;[<OA
ML-L?-_8P,HY\5&681,S\N2,R+CKN($Z%*!@0S_-PXZY/MNC22,I-K9Q4,Q7`
M/$P9\^3)3[+B#BLT=KR$:U$MB`N#D9Q]R((25^WO/,0X8Q[4H#M@XC-A7ZLT
MZVI]"?S:,8H_)>,TLG+F1,FU-(U:E9,G@;;P10+`=N46(,@DLA;26-\G1Q<C
M"L&YA_!B?!7BCDXT^DP8=%;+;`R:F#1:!W.-.0ZKKI2FMD#5ODI,^V"UW%&#
MG*#T6!`P4)-`A+%A93E3B5<G*G2&H:;9>XSZ2W,_9:E]F&LH;&\D=O3%FL""
M@A(YY8"%):"G&@]S<O_R2Y8C0`3@^4P)`9$T#+DZ#(VWQOIEHK#F5@H^,#:O
M"NBTU;HH*KBQ_=)8'KHD8*;+-_]<@G9E74C@+<B3YKJ<?8J"R5*12:.5.GQ8
M:6=;4LHB299B!OQBKT&3M^83RLITGRCE3G%LH]%E1QZ"Q;(EH2TX2];^I[+X
MKQUQV3:Y"@#>-`EP+O4U@6<QV%\*/#TF2V3WBSS7'PS[WXH\TY,U,MP$#2T:
M-:_$J*GAQ=B26$A!`*0K3?&B1R:FO;T]=M7J7=(A71EY(K([)^*%;L+G[/T5
M@/ZY`]"_PK<MP[<1*$>L6=P[!Y1:K@>S/QXPZ=8V+HA2WGC:PN!1XL9#58+?
M174I6S.Y"%`GI%JRXU)PO$6?J&6<Q6BPMFTXN#$4+/6@)1&#_CAVB>YHZL_I
M[]*QGWS=-%='5I,3!HW)+7!UOCOS]\7P3O?_+S?X?PE;5)+E+%;$>`5NT>F7
M31PSMF!766'VU\30RU7HY2)T$<^*.N2?)7XI5CY+XA>Y)WB_^&7].?7/&+]L
M$U"4&C(Y36DA8)&,?Q\[)NG8PH0MA^)`=.GZITHKKADK)*TB*^?/V`Z>.1='
MC6E_?Z=@^CYI'EPQ/M[J%;+,E^5IQZJU7RJ6)FX1DTDHV?[3]N(P1;;^`*G3
MZDGD,JKRR?Q!K5!QEV.Y?"^/#=S/"*U_]V6=$2JS+O>T)Y)BJJC\KH&13L@]
M`R/K#U@JH*#U3Z/9Z;1*'"LV<WKY?BMM_N@7LW2%U_0]%24.REX62'.&_'RK
MJ7]!$`?9,?G5@K@,,'7'S-UD4Q^/V:HT'E_XH/?,$3.]N,`FW/7OU$@;N`2N
M!\VO;]L)OLU1.`^!DLMM!T5[V6XZOG4SI\\(3,2+..S.#]]"%CZ+-V7M8GN:
M6#*V;NAO/=N8'E^W;FY"?G,,9A=K=48`NA7/:M4?O<?1CQ[X,QKHCTCGY&@Z
M[93;5Q5`:-=TP>G=+7&*J:KJ>(735./K-Z&?!&U:C1^)6X-T'P+P';T4<Y?T
MFM[80\,EWM^K(T;"MV19Z;A706N@*:\T>,B@(=X%I-=WV:WU3KVA(SZ?@79I
MXL1LBE^"@%LYI`AC'_^DA^4`WNXK/T@/&,@R+LYJ<''=_>?%\)C.BJVV2VJ]
MI6NM(D]$L+26@0-0N_R`$-:U+MDR%,(PRFJ[R^C]@(Y[U6J#@"-SU3QX6,<_
MGG\_</>>#7UV8IOIE)I%0S\0J'-\N(+C\D#2[R:'.KZ$7_OMN;><=6[#4PE-
M'"71V-OJ=_[B[V?D;W\P7L5?_`[&[\WCZJ]9DJ>7_78VJ8+SS[_#O8YXS0>4
M$IQC:H&.[`32?;Q:WJU)OU'JSXP/NCC+O-N5E3FW*RI#`.7HVJZLG&=;3@]6
M!>XP'E]!"SU?1.MIF(Y@MB@!9ZXJ)V./W^VI2:6E*2,MNF]1(\X&+`&'0`O/
M42@$^?J7)#N5*)8+M4E""J#40J+8O&%9,C^%FZ2DJO;R;L/):)7.X'OUBC`L
M<Y"GK*Q>%F7Z@('V1\99%*O?(\`:)9.>X[U-L<I[K(@E$Q>N4--DHZZ2XDRK
M/-2JGV.MR7&[K^!"5"L%F5FE+(W51-5-J68Z$,T0AEYZ@E<FM9JF,5HE6#3H
M1:0ER&O4L6Q#_5>85B.M1#67[&>J7R7E=+.O@JD/E<HJ;[K-J>`R*WU/=:3/
M4:2:H=EF[9,5ZIL5I!E+D(HO652V5^M-)T(3#[,Z+\OE\$!H7C3U*@DC.PB:
M3Z]6OY0AIRKWJU8FVZF+6JX?X4<`"=U6J?9'?Z]D76U)^QH%OJF=?HWBU`_G
MU_)#`_1D1.^+%LO:935RG!W<(]FX]&FRL/*U9&&;%Q73K^JD7U38YM5$.0=\
M4XQ;H7T[6D1M_*@"3;%V,1P,S$Y32G9`'S0UI^FK^HAR]Q7^[TSK5.O!?]H+
M,P]3;IG1%:3YTYK$5Y?O4N344GS-07XN`G?(:1:@A_)%X";+(91,R7A-S-/$
M4&=ONN/1>'C9'J=X3S8><+-4K0E6!HPBKX0?"*C?XT";^O+'E[6,NMU7$EIZ
M/&;9&*TOW@N%(%I6U.@VE.4WF%JVRLBNVJVL;OBHB:S@*<T4GPCY?2IY<5;(
M`^/2RFT89Y4\/(E*U8GLA8GT8*HXD6J!&0KGEJL-VLLZRY>V)'#;=Y.Y)]_>
MRCI9'KY4##9V[D.0D[ZV$;(D2K!8EO4,DACI":SI%%BVNV\G\6Z.F1K0T$^\
M_V_O6GK:B(%PK_17Y$+9H`"BXD1+)0YMA=2B"J5"/:&E;$(D("B[E/;?US/C
ML<?K?3AT2WN8[P)YK#VVQ^.Q9_R%PD6R.95Q&6:[H_/"?Y,39T'"U>**V%(G
M/DU&-/WA]G[/^&++_&H/_H>LF4?B$P)NRH?[\/*)KY<8,GOM]#`<4-98/\DN
MBR.PR!33&[@>H[$VJ_35A1&F4^4YZ9GNJL-Q/U_><T-.%*M!@KT<,9E!3]J)
M34?%K(U]+QU#/0QG!7AK<SZ%"])E%;`46B?E3C!D+F%?P'T9^1_HQ''0RFK'
ME:)"<4]3'!>"&&YW/)@D9)MUP?ZW"_9)1:;LLI"3`^-WU>,2:'V0P[@4-/JX
MPO'S9E+9FPDEA-'1[<U+031!<76<L?@M-`+F,9><"!.V.D@BEJH[`F89!]U\
M/?A4K-5DBJ[L/*A](BBK_K80@CD+!#DYG1X8/1UV.K;<>Z+X?T8AB&I9X<LR
MGQ5C.::L*UZA7!0+=V2@`K<Y!"/@(5(+:ZO)SI?>PO=&I]Q:`A/`[?BJZVP_
M</+>1-:53Q=]"=;,8B&##R)P=!R,,U\;W/HC,L6'`FMN7F&>85S/X:[:SF@.
M5Q:K7;QQ)!P:<JK0:_*_$9+D3B:ZR&*40KE;'5"_R4</%'XIPV\U5@4X`()N
MG7TU-&77UC1]SR'OAPCN<_),&OPLX6!U<&'&7A-4X;D5S0M\'\PS?B3=I@^W
M\]7)W6RYL3%;F#\71B;CJ@:;V^(G5",SRIGSIW%G35Q:)$'+TC;]]J5K:>.6
M615%GHBT%<Y6"XFW[:L;*)4]%7&+6U.5HBE^C0MZ4RYR7#6'\;&+S!.UD@.Z
M).`;OH!NSWPE.^_,_S0*D]$K.2A,"8/#P:Q"X*QL8SDS,TE66?C$N-Z7.-VY
MXRTOSF2TL\]BE_80BG:%6YOEUN$A\MQ1K7([7!,YV`ZOF9HAV3IP2GVD7X?@
M]!K,C+O\!<Q7:0D:+52P\4Q!ZHYH@X'OTF<\5TC7[;=;U/KL_:<NM289TA29
M:^KWU8PVNWZ2&LWDMU[PV%$C5A2AQ*[:8!C]F(MR?*92U]"&(0$_MG>"C(M'
M%^Y_>'O)C2IAU.UO66R5EILW20O26'YCG8#%GS7BF,OP%M1\'"@%?;U%)8ZG
MT].OGWM5(L.78Q(I03VHSB3E\/V\B1=O&I4%#H.Y\D!Q7&-!;>+.D+IC96K1
M'%>066HIU;55;Z*+*J@VM!_/Y78:+Z+GXN0$$IEL8ESM:K(XB3$[!;Z$M.C@
MSX"5[N532)^M0O7F<=DS]*0D)W%RXJ(.,AU[NS'EET)2(D(1-\<W!8,S[BJ0
MWXI/+)-U<R"DEGU%+XW/D,_1(;9Y52(ZMK8DIB1.<NZ1I2DE2[RWL3U?P33-
M9,+5GTAFBC-]!'W=(]@ZJ5IK2Q&XKUU2I(;4K00R8'[D8\DRRGQ3_"AN2K!]
M1YTU!\%;<2W`O))GXLPTA]H?AM&[ND.<=)?D^M%SW!"^JT8QZW#R/5/D-P[\
M^MAN5]-:;N9!EP.)KVMJT-*U"H[CO[5>C(I.Z4`7'JU_N2/Z^:2HYJ)II@P8
MW'RA4"@4"H5"H5`H%`J%0J%0*!0*A4*A4"@4"H5"H5`H%`J%0O'_XS>B9>)Y
$`*``````
`
end
BTW: get_ruledef() would be useful for psql's \d too.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Stupor Genius <stuporg@erols.com>
We could add another column to "pg_rewrite" which contained the
source for the rule. This could be used by pg_dump to dump the
rule creation statement, or by the user to see what the rule
actually does.Perhaps someone who knows how to graft in new columns could do
that now before we finalise 6.4, even if we don't use it straight
away it would serve as a marker.I believe a dump/restore is required for 6.3 to 6.4 so we might as
well get the catalog change in sooner rather than later.Adding a column will take away from the already-tight space needed
to keep the plan.
Ah, yes, I remember, the 8k limit.
Perhaps a better way is to have a new non-cached system table that
would be joined to pg_rewrite to show the plain-text plan when needed.
Another table would be the answer, and no need to have it cached.
Rather than require the user to know this join, postgres could (or
should) have some system views (ala Oracle) to hide the underlying
table structures and prevent any user except the superuser from
modifying a system table without using a postgres command.
I use Oracle at work and I must admit I find the views useful.
It seems quite natural to use SQL to to look at these things
and views to combine information.
Keith.
Import Notes
Resolved by subject fallback
Jan,
I'm absolutely amazed by this piece of magic.
I updated postrgreSQL from CVS, built postgres and the get_ruledef()
function and gave it a try.
A simple VIEW I have goes in as:-
SELECT t.artist, t.song, t.trackno, d.cdname
FROM disks d, tracks t
WHERE d.diskid = t.diskid
And comes out as :-
disks=> select get_ruledef('_RETsongs');
get_ruledef
--------------------------------------------------------------------------------
---------------------------------------------------------------------
CREATE RULE _RETsongs AS ON SELECT TO songs DO INSTEAD SELECT t.artist, t.song,
t.trackno, d.cdname FROM disks d, tracks t WHERE d.diskid = t.diskid;
(1 row)
disks=>
Absolutely perfect rule definition for the VIEW.
One slight bug I found was that ANDs come out as ORs but that's
easily fixed with the following patch.
What can I say.....
Thanks,
Keith.
*** get_ruledef/get_ruledef.c.orig Tue Aug 18 19:34:34 1998
--- get_ruledef/get_ruledef.c Tue Aug 18 19:34:53 1998
***************
*** 738,744 ****
strcat(buf, get_rule_expr(rtable, rt_index,
(Node *)get_leftop(expr),
varprefix));
! strcat(buf, ") OR (");
strcat(buf, get_rule_expr(rtable, rt_index,
(Node *)get_rightop(expr),
varprefix));
--- 738,744 ----
strcat(buf, get_rule_expr(rtable, rt_index,
(Node *)get_leftop(expr),
varprefix));
! strcat(buf, ") AND (");
strcat(buf, get_rule_expr(rtable, rt_index,
(Node *)get_rightop(expr),
varprefix));jwieck@debis.com (Jan Wieck)
Show quoted text
emkxp01@mtcc.demon.co.uk
Jan,Whilst you are working on the rules system it would be nice if
you could look for an oportunity to store the plain text rule
definition at creation time.If the definition were stored in a table column it would allow us
to dump and restore databases in a more complete way.I looked at this some while ago myself but never got close to
making it work.Keith.
Yes, that would really be nice and I had something the like
already in mind.[...]
On the other hand wouldn't it be too complicated to
reconstruct a command from the parsetree, that exactly
creates the rule. Reading a parsetree isn't fun, but after
all I did on the rewrite system I'm somewhat familiar with it
now (sometimes I see the cup in a targetlist, the coffee
machine in the rangetable and all the buttons in the
qualification when pulling a coffee out of it - think I
should I consult a psychist when the rule system is fixed
:-).Jan
To demonstrate that it really isn't that complicated as it
looks, here is a C function that if defined in the backend asCREATE FUNCTION get_ruledef(name)
RETURNS text AS '.../get_ruledef.so'
LANGUAGE 'C';can be used to see a textual representation of the rule given
as argument.
Import Notes
Resolved by subject fallback
Jan,
I'm absolutely amazed by this piece of magic.
Written just to check if I understand parsetrees now.
:-)
A simple VIEW I have goes in as:-
SELECT t.artist, t.song, t.trackno, d.cdname
FROM disks d, tracks t
WHERE d.diskid = t.diskidAnd comes out as :-
disks=> select get_ruledef('_RETsongs');
get_ruledef
--------------------------------------------------------------------------------
---------------------------------------------------------------------
CREATE RULE _RETsongs AS ON SELECT TO songs DO INSTEAD SELECT t.artist, t.song,
t.trackno, d.cdname FROM disks d, tracks t WHERE d.diskid = t.diskid;
(1 row)disks=>
Absolutely perfect rule definition for the VIEW.
One slight bug I found was that ANDs come out as ORs but that's
easily fixed with the following patch.
cut-n-paste error, tnx.
Another one might be ISNULL (I think I missed it). But as I said,
it's just a point to start from.
What can I say.....
Thanks,
Keith.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
We could add another column to "pg_rewrite" which contained the
source for the rule. This could be used by pg_dump to dump the
rule creation statement, or by the user to see what the rule
actually does.[...]
Adding a column will take away from the already-tight space needed
to keep the plan.[...]
darrenk
The limited space for the querytrees and event qualification
is really a problem, that we should fix. And since we now
really support multiple action rewrite rules, it will become
more important.
I had something different in mind to make the space
unlimited. I could add an Oid field to pg_rewrite, pointing
to an extension record.
If a rule does not fit into one tuple, it is splitted into
multiple ones in rewriteDefine.c. All the extension tuples
have the event realtion set to the invalid Oid value to not
get fired everywhere.
I don't think that reading some extension records is too much
overhead compared against the rewrite work and the actions,
those MONSTER-rules will result in.
Comments?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
I had something different in mind to make the space
unlimited. I could add an Oid field to pg_rewrite, pointing
to an extension record.If a rule does not fit into one tuple, it is splitted into
multiple ones in rewriteDefine.c. All the extension tuples
have the event realtion set to the invalid Oid value to not
get fired everywhere.
I think it would actually be better to allow postgresql data pages to be chained,
so allowing all rows to be larger than 8k.
Usual limits are 32k independent of page size.
The trick is to add pointers to followup pages if the record does not fit in.
This will usually be the following pages, but might be somwhere completely different.
In this case performance will be bad, but all major players suffer from this.
I really think doing tricks for system tables to overcome the 8k limit
is putting work into the wrong direction.
Andreas
Import Notes
Resolved by subject fallback
I really think doing tricks for system tables to overcome the 8k limit
is putting work into the wrong direction.Andreas
Right!
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
I have fixed the parser to all update/insert queries will have a
resdomno equal to the attribute number of the base relation.
At least, I hope so. Can you remove the rewrite renumbering code and
let me know. Thanks.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)