One more bugfix

From: jwieck(at)debis(dot)com (Jan Wieck)
To: pgsql-hackers(at)postgreSQL(dot)org (PostgreSQL HACKERS)
Subject: One more bugfix
Date: 1998-10-21 12:18:50
Message-ID: m0zVxE3-000EBPC@orion.SAPserv.Hamburg.dsh.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hope this is the last one,

Bruce, please apply.

The patch does 2 things:

Fixes a bug in the rule system that caused a crashing
backend when a join-view with calculated column is used
in subselect.

Modifies EXPLAIN to explain rewritten queries instead of
the plain SeqScan on a view. Rules can produce very deep
nested plans, so I lowered the indentation a little.

The patch is regression tested. Now the rule system handles
this:

DELETE FROM shoelace WHERE EXISTS
(SELECT * FROM shoelace_candelete
WHERE sl_name = shoelace.sl_name);

Doesn't look that complicated. But:

View shoelace is a join of 2 tables with a calculated column.

View shoelace_candelete is a qualified select from
shoelace_obsolete.

View shoelace_obsolete is a select from shoelace with a
subselect qualification from shoe. The subselect has a
qualification that compares calculated columns from shoe and
shoelace.

View shoe is a join of 2 tables with 2 calculated columns.

AND IT WORKS! The generated plan is:

Nested Loop
-> Merge Join
-> Seq Scan
-> Sort
-> Seq Scan on shoelace_data
-> Seq Scan
-> Sort
-> Seq Scan on shoelace_data s
SubPlan
-> Merge Join
-> Seq Scan
-> Sort
-> Seq Scan on shoelace_data s
-> Seq Scan
-> Sort
-> Seq Scan on unit u
SubPlan
-> Merge Join
-> Seq Scan
-> Sort
-> Seq Scan on shoe_data sh
-> Seq Scan
-> Sort
-> Seq Scan on unit un
-> Seq Scan on unit u

What a plan. 6 scans over nested subselects for one DELETE.
I think that is close to the rule system we wanted.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #

begin 644 rule_fix.diff.gz
M'XL("(7)+38"`W)U;&5?9FEX+F1I9F8`Y1UK<]LV\K/Z*Z#<M-6#BMZ.93>>
MR:69.]^D2>LX;;]I:`J2>:%)'4G9]67\WV\7`$&0!$2*EIQD+M/*(@GL&\#N
M<@$MW.62])R01*'S/`C=5?_*=CY1?]%W(at)IL;VU]$??K7VK-=_[F#;;8\_J[3
MZ50!T_B#+LA[)R:C(1D.3J;#D_&(#&>SX^]ZO5X)CDS?\<GXQ<E(at)P/MVLO\8
M,:,C:SPD[!)!X^6(P+?>=X3\S?4=;[(dot)(at)Y*=@';LW[G]IV`<DOD_#Y]=GF0:V
MX]`HZO]E.W'^T29VO:@?4L^QG6N*3[O*TY#>A6Y,^^+O/X$7+P$/_\7W:[J@
M2Q+%X098>L.9_!#;,86GG[4LC5]8DU'*TOC8FDP3EAXR($XYD(at)B^NPYQKNV0
M=,3S^:_`Z&7P(0Y=?]7""])!WJT,`-*A4?L4&!(@;@-WD31X[]/?-C2\;[%/
MTOD/_K'(51!XY):&5T%$+?*:J^]G&L5D`1\,&*>JWX$/TDG`<2(at)]+<>3(VMZ
MQ#DFC`;XHW;;C00FV"9IH#P:2$)$7I)W']^^M?!N(at)]\:G>(%RH5=HVC8G;QT
MV$W7CZ&?1WTA\(:[)*WSZ-55$,9T<1G:?(at)2&XP;^W[W`^<3ZMMI(1^,S4^%D
MAOP)%>Z-O[=N%#<ZW/!BRLGG][P:=.KT<C2QCF:I)<+E[#AAH]$(:;P)F4@:
M#XBO2QK]#KG(at)`X'$UV&P65V3<.-1$MU',;TAG3XVDA2#6ACCHDN+L<\MB,,Z
M]P$,)8X=41(L"=CP^;L/EV]>_4S>O;_\Y_F[?U(at)DIIY'[)AXU`;YQ-?PE6-!
MUA5,8`'G;]OXX#-^-*(at)7K%H`Y?SU&XL\8YB)&,,1B0/B!_$UC)QGC)R4UR[R
M*ND3.B3T%KNOV2!CR)=!2&&V:'D6D30PY(W\X/*6;AC%+:]MI1J7`TF@@I'4
M5492TA<&$]X&^UW#((])L(F9N(dot)A?U-F(at)ECE%0`L)?$H8CZP'^^AG1SU</6K<
M0W\F6,V(at)ZZJ#KEL8=%W=H(dot)MF!AV;4E#(at)C"'D$8ER05.M/__\D]S1'V\I<5<^
M2'V1:+'9;".3T(]U>DG$S"^M#"Q6NZ`,1M9H,$MF(R#A,__3N+#]%;V\\M[X
M,<H%AA1`]>/K5JOUP4'5MQF(dot)=N\L(at)DM8,&`N[9&A16C4.PMC^\JC'"V;A1H1
MT]JR=;59@@6"BLCWT3.PEYA":[KT[1MLSMO:ZS4LEGPR/_>700O6$U#)9LD`
M0H,'_N<*S.X3OP6KCKWQXA.^+(Z'UF(at)R3L?NH9C2$\JX>R:YP:$)]YV;=4OE
M5O+N,=Y)\R49M$67S^)O3FK?1^19KEN"9*O,>).'K43G%%$F9JTMC<&6QL>*
M+>%@;+D(at)XL$I<<E/Q/47U(_A>[?;%AHQB9`)D#4QMB"D=T9200N7:@X3P[M@
M07DST.OFBOL#;9+,/C"`0</</^`DD2Z9H)(%3LYXJG/@(+)OZ6+.+\4BP#VP
M(VOT8J28VK?$\VAGGA?EWG5('7=-3<YU\K3$MTZ:U7"MM5V'X%:?#$9FSWH\
M'5CCZ0O%>&$)\BF`(at)(dot)4Q6"XCRI>;6_`Z?1!U!%(4<[,-2[(/[>QP16,/7!+9
M_XHZ]B9"[X#>$SND3/)_`4P`R#HC\ZX/`+B0DXY]KMWW#.WO=HBZC5HM_(,Z
M=?WU)OZM=X:H<>3.8=E8Q=>)491UXX2B[Y3MS)VH!JX\W/X8B2%.F821%Y%E
M&-QP)^7:]18AN!I,%(at)V^](#CD)B.[P2^T^*7%L<\E_,G\_.%O-.!\\W)VX()
M^S$BY_V?3.H5ANZM2^],`Y<_*QFVO%&-0:OIR(;L:+HE&![.K-$H76]8"(9F
MP*:\Q(AL\)A3([JZ)R/>BNG<I#<DYU<[C&A6=2.N[UPOI3&WD)'T$<PMK^U;
MF-M_4]L3X??QA44PEXR/)V!.VG,I?])RJ[.H&+M(at)QVR/ABQ#UC!-C?06:FJM
M,=69WE0K01B?#,8G,*L9;79R;$VGRB(#D0$+-3JN#S&CO9(at)O/7O%!"6B%?:4
MS0:7.(Z9XSJ'.6XAU(F:QZ$>X[S"ICM^%6V(at)]2WUHOEF78!GQ^!97&UB6(at)42
M-/8#+=!>)I'"37(.YLBLL0"5/Z\`"((J8-<,*$N>3^^J\,T(at)WP1@=/>O5JN0
MKCZN63L%^M;FKQE1OV=I8MU^19]K?W3]'`9K'#,%%/P:K9J+`L+($,)'^!1+
M*MJ6"'ITIC<<O+"&0\7X7'`LT=N#A8.O/*WD#I+,OFT\.N<YE&SXDW9'-IFF
M`,JO%Q]&\W=O_IC__NKBW7OP+^52QY?)W%2AP:'W:,RK:P&`R;?9CKZ(%AF4
M*B]'RP"($8W!/I/$ZX\7%V_>71:E82FF,M(at)=F<(CT_MP,`7%*N'NUZ_8U'5Z
MK&Z-D,SJK27T+Z9AW5">3H?6;#A)AK+,0/<P;Y:;ATF/W89EBKO7-N;Z:(C)
MK0BS9L&2Y"37)G?7X&(dot)NPP#?$L`2S(at)%<G%^P]&;$+OO921NN:L__T/<S_,\2
M)=B<I2\_OGV+#])<9(_(at)973GQLXU:W9IK]C?=IMW;[#$Z>7\D(at)F/+90G['Z#
M/VXTE">-3LP\YI9R#Y2!\!)4\"_/DKC=:"3*:P&4WAF;@RWY4#`JKPN+'4NM
M])34BD`H&.#K0(YV?K/1L5<K))M?UJ$8`"0&MD>:_Q$&F_5K#Z.O'.'*DT9G
M%:Z1>N5>'18`"@@=5;9'#G`YS9&.MQJXPB+-?+G=G5CHWCL#<4=[I/4\IGE:
M\5:C`XYIB,3B51UBL7_O##_W;=-A:-]?T&7>JL5M?+&S9'8M;M0A'D"P%.8&
MUKR03;X[L[`#%B^X>P(LAX5N1]'*W[.F81G)*1GN-#H8I8)^?[<U=HD3/SSO
MG<$'Q[59XR*004T2<(VD*;@FW9=B63DM/@R\1?'Y0SD#$+W:-R=;&KP.(%;;
MU(at)`7[IP,Y)M*R;1X:4;PK5D+'PNYM!-.C&J4>DS?IJ7/\NK;67\?-E=O7?]3
MCGYQM]$!<*A'<5UGF`*$WIE'ES'X0XM=#&\W#/`148\Z55<Y<-^&5>3#WA/F
MI,/N-3K_`1\"9"/>).XN&>BO^GX'D0W#@<[LX:"G>9_#X5BE'L0^YJ[T]2%[
MJY)Y6[[Q/_G!'<^YDMA>D>\7F(O.$]I^9I&L6WJJ(at)\:X<*,3_OJ3=4C*5C*]
M5/KP#I+]("\37S^7*DE\?4R/)WY^+NQHDS@(#![\_A(O7Z]+G^>QFDLO.9=W
M4NZ_B)]?SH;>SS\((WMT_LOYTCK_!V&K7D10SH$N(C@(`S7#A'(.C&'"8<;)
MHV.'<HY*8H?:?.V`VA!0/`7J+X!2%WH<Q'H.%(^0'WZ0)"D!";1*N-!'+"_3
M==,0LVA:?$-1B]$"MD8M1<WK5/_4L4RY-9MBF7J6O!M:78"S,]Z#1SWES)BC
MG(at)-+41<*/05*;7ST%(CU0=-^9]T:D52>^J>.I%C=ON;5,.FQ1PU1EPU2\>Z)
M`]!BNF!K`4X:6.YG^\1F';&@UPF\S8U/\,4Z,,<A)'5+[M)U;'S%8Y$[2FXV
M,'W:BW]O(EGJ)%<;$`O6=L!M]HIOBC7UT\F6E[M'TZDU/#H:I/7KP]$,;DV&
MZ6X*\:HHN_\!+NWUVKN?7YQ?S#DU\T3;A0(at)P&^2)(NABD)>M(<=+4Y#7-01Y
M73[1='<*\C(at)J7CYI8$@T,(1[7;VU=[FU=Q5K$JARD5R&ZNV17"U:LS%=76HS
MX5J&Y*KA6BWB,X%;7=IY3)8AVAR3U2)3C<[J4LD#KPR56P*O6F060K#:MBNC
MJZSUED97M<@VQ%E&XA^!(A=/'0+%`4&K\5%=U;+0)Z-54^(at)C22V&/9DYOP)2
M$8B8&XA`Q-R`!R(9NF4@(@DU!B(DZ5-%[+J(at)I&N(-KHB%#-+0/H^W<J^3ZX2
M3^?Z=&NY/@7RDLU<8I=5M@(-KE)I80EG28%;<1^-1;@26$$V;]YA/I5+%SK?
MP;0==/AB-K#(at)0]V2Q#<G)-_Y3.\$Z_OW5_^&\`=G['0?C#9V/R,#C->;,K84
M(L:>Z+6PH=(F<MM-HR5&!S;(CX<\=(GZ09*8B`U:`X13>3L1"-R_O/CX)GG`
MMY(,IQ/PUZ93=3/)/CAO2Z[,`P(`607&VJ>U^=%J]O(at)8^#N>C13-5ALC6<O4
MC1%2:XR00G@@-G<RH3.='(V/P:V>;"NKG&&3V20UV#66/\<AI<I6FG03#;;0
ME-\I;4!=^9=+LEA,J6<CFB*X[?TX1MT.!+Y_.!.%Z5#*FN4=R]]*P0O*ZL)/
MM_^&U&,!UIP9<5LH\7@&4\I@(`?6'C1DJELL4=#6;EOU8RH?W(]ZMD(_F':T
MNSI&DR-K-)HJTX2$#"K(at)7UI*S:JR_T54NR8;8'`I>@VS94M7[BKZ8J[#8`F:
MHE)L7:E8.&VX<Z'H(at)]C]BWM]P7KOKL'%)\XF#''/WB*(at)D?]C3(dot)Z"D.<>Q+Z!
M9`?W<_A'[BG;%LYL?S9[88T&`Z4Z^*N6IK%<]FL0J'[3Z_$$=[TJ!P>,AN.I
M-1H>R86<#QPV`%31B<U/E_=KECIY_<O/<['1/]DHZ(at)1^[/H;Q4'O=WH[_Q-;
MTCOD#\J<!U)(1T58!8QWY<0H^\0!>\!I9OON;EV'LNIA^SGY$*#,G+3YM1V)
M='I$@&'<B<]!W^`CXL;/DZ:RRRN&_>CY!%I[]RD)>1(=VU?0W-(D!1<])^>(
MPHU$)C%*LVLW]B>%%6QB<^_""X)/X![9X(at)P!'[>OB+WTJ/0"D?]*D==70)]_
M04-05B!59#_\0)H9&U$>MK,AD=(LZQ0J#V348H(I'3<UJ$'ZFF8"*])G`)!#
MF.Y^K"'69-OGAYBNR?"D^HZR7VS?76_?3R::5-I-)MK6WDNFZP^=QR?#+4<!
MX69[=9&$95F)V0R;CK(at)OD1S+TLQ7_NM+]GFP1G2)7O5(at)E&:2Y26Y-"\^X0<P
MZ#*]XGR&9C[32_297L(3%O((AAP',IMK)<0;SS+(Y6XS=(C<+2;O\^G;$(at)*4
M%&T9"?SU!"OEXG9\B1,43I,PZ5Y16)I(at)]H2O(dot)(G)(T`VN"&#L#<Y#DO*1FSI
MDC!(at)-L`H*4IN]%5>,YG?#,.9+"^#7LS]EG`N?<86ZR]SO545P5.[&:I8:A>5
M(at)!I5TKM5*<%N/)E;F0J>NLU0P5*W2$4N>UN5BFRJMKIARL1LUC23Q"Q25,S-
M5B5*EXA52*O664VQ[MRY5B>9%JTL1I8$S4(at)0DZ!,>IE$:%YP^BKS_+-<D?EV
M4GAB,T,+2VPB+9Z"6CE/*4EM$EYD(3(Y.2DIIRI5%(HLC,@0DQ1"L-.+Q/O-
M3'U$7D8P=_$OW+FTO2C(at)KAG,6O;"7LOWFBX_R"!Y*2H["2QI#07S,.7=`&Q3
M-NZ733L%8.V,>6EH_(at)5GUF@3LA,CQ,YZF"TV-Y0M?EE"R#J`-1$GYQ0`=HOL
M&WY,!4S<17XPOB"L$O?.!='?T;3SG>MYX$WYN-2"EPV3IA`2SO#RA7)$<$T!
M*V3-(2I)`<`:@8<`.`&$,D[LW;<+LA*6T9(3I[B180P/AF$OO,C+Q,#RS:1$
MRT\&:HK#+/@!5?S4#60_2M8BI==#^E9:."2YJ*_(at)D'@+GD)*2Y"5RV*&^2MU
M6G)<JDY+RJ#"7,&/.Z1#DR,NZ]#4)N];=W9,";*"LZ.34/KF`11025K[\8M,
M1&?\HL=;W"-\)N.FZ+S/M(=QL1=_RI(at)HU?M35<BN!CCG:^T/\-X!JC[:X]56
MUW]37X?QPEQ)C/(NL'3;8?Y$OD(I+_M^6GR<EO**%D0MY=WRMJZG]?'2TWML
MD.N85_\]D;N94[CJ;CY>OX]S19M*)2JVY4>;*N6HJ4LFZV4KC&8&)CFU8K<I
M7534?ED?V;A::7WD79>L_P=?.B-2#;LU/>NTI\ZW3G$>TLLFY-5BP8X8$H-$
MIFJ30X98430_IF]DC6=*1<(.648F+>!73F3)%L4FX43NDH-\A(.?^OC%\M-=
M_?NMY:>FG$M:5:*K.6WF]JXV]<5>&H/HDJINOZ$&M3K!V<+3^B1G/=M*'OK6
M0M3J'&2J3_<E<Y-KKJU&K4ZK6H*Z+U)-/KJ^)+4ZK84ZU+W9<XF[;JQ+K4Z[
MH1BU(@<[(LF5HQX&R4&!JR6I^]*RWKO7E:AJ'?MMIX4TMYT6TBPY+20Y,EO/
MP./+72NXZ3H?7;/O3I\23MTW385K,W]82--0\EJJOJW.NW&#7;/R",WOKZML
MV#LA4'?25<$(at)7/PF24]5%](1B+.;YIJJ;+9MFJM.=G'/W-XEH^Z..PSPS#ZX
MRA/*3BBR.]X>/6EEW>]*Q:OYL_4TQ:O-1Q>O)A7=J:N?^26-;#0H?DBC7SGI
MOL.Q'U^M2YYCL9I+GNZ,;!9W1GX)/[V4"[V?OB<^#N:\E[*E==X/PE4]C[Z4
M`9U'?Q#KJNGFES)@=/,/,T8>[?N7,E3B^]=A:T?,AH#@"3`_/49=Z'`0RSE0
M/*'N'C*?]M'<?MI'L_2T#_*M11TF`]@:=105K]/\$\4BU6<,4RQ2;_CLA%47
MH.R,UARUD/U$+:6\F*.6P\I0%\H\`49M?/,$>/5!SWZGVQJ14/X(at)PJ>(A)++
M75]]%"O)Q4^(at)ZDNWKUEU]_8FV4KR[6V+E>33HVPE^6[]QR>C+;]),CRV1O(L
M>?E;K\],O_7Z+#EN/OGQRZ2:GKTJ0S$7HG-#7?FI4$OMJB^Q`X#H7T-Q4BHI
<G/^`!A=$_N=T#R\(RTS_UR":_P%H_<@64GD`````
`
end

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Hartwig 1998-10-21 12:39:53 Re: [HACKERS] Re: [INTERFACES] using indexes with the OR clause
Previous Message Vince Vielhaber 1998-10-21 11:13:13 Re: [HACKERS] New INET and CIDR types