One more bugfix - Mailing list 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@debis.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@IL;VU]$??K7VK-=_[F#;;8\_J[3
MZ50!T_B#+LA[)R:C(1D.3J;#D_&(#&>SX^]ZO5X)CDS?\<GXQ<E@P/MVLO\8
M,:,C:SPD[!)!X^6(P+?>=X3\S?4=;[.@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@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@]+<>3(VMZ
MQ#DFC`;XHW;;C00FV"9IH#P:2$)$7I)W']^^M?!N@]\:G>(%RH5=HVC8G;QT
MV$W7CZ&?1WTA\(:[)*WSZ-55$,9T<1G:?@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@`X'$UV&P65V3<.-1$MU',;TAG3XVDA2#6ACCHDN+L<\MB,,Z
M]P$,)8X=41(L"=CP^;L/EV]>_4S>O;_\Y_F[?U@DIIY'[)AXU`;YQ-?PE6-!
MUA5,8`'G;]OXX#-^-*@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.A?U-F@ECE%0`L)?$H8CZP'^^AG1SU</6K<
M0W\F6,V@ZZJ#KEL8=%W=H.MF!AV;4E#@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.=N\L@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@R3L?NH9C2$\JX>R:YP:$)]YV;=4OE
M5O+N,=Y)\R49M$67S^)O3FK?1^19KEN"9*O,>).'K43G%%$F9JTMC<&6QL>*
M+>%@;+D@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`@.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,%@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@QVR/ABQ#UC!-C?06:FJM
M,=69WE0K01B?#,8G,*L9;79R;$VGRB(#D0$+-3JN#S&CO9@O/7O%!"6B%?:4
MS0:7.(Z9XSJ'.6XAU(F:QZ$>X[S"ICM^%6V@]2WUHOEF78!GQ^!97&UB6@42
M-/8#+=!>)I'"37(.YLBLL0"5/Z\`"((J8-<,*$N>3^^J\,T@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@=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&.NPP#?$L`2S@%<G%^P]&;$+OO921NN:L__T/<S_,\2
M)=B<I2\_OGV+#])<9(_@973GQLXU:W9IK]C?=IMW;[#$Z>7\D@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@`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@\: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@-+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@P&^2)(NABD)>M(<=+4Y#7-01Y
M73[1='<*\C@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^@C22V&/9DYOP)2
M$8B8&XA`Q-R`!R(9NF4@(@DU!B(DZ5-%[+J@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#@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@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@7UI*S:JR_T54NR8;8'`I>@VS94M7[BKZ8J[#8`F:
MHE)L7:E8.&VX<Z'H@]C]BWM]P7KOKL'%)\XF#''/WB*@D?]C3.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@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@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@OD1S+TLQ7_NM+]GFP1G2)7O5@E&:2Y26Y-"\^X0<P
MZ#*]XGR&9C[32_297L(3%O((AAP',IMK)<0;SS+(Y6XS=(C<+2;O\^G;$@*4
M%&T9"?SU!"OEXG9\B1,43I,PZ5Y16)I@]H2O.(G)(T`VN"&#L#<Y#DO*1FSI
MDC!@-L`H*4IN]%5>,YG?#,.9+"^#7LS]EG`N?<86ZR]SO545P5.[&:I8:A>5
M@!I5TKM5*<%N/)E;F0J>NLU0P5*W2$4N>UN5BFRJMKIARL1LUC23Q"Q25,S-
M5B5*EXA52*O664VQ[MRY5B>9%JTL1I8$S4@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@KAG,6O;"7LOWFBX_R"!Y*2H["2QI#07S,.7=`&Q3
M-NZ733L%8.V,>6EH_@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*_@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@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/R",WOKZML
MV#LA4'?25<$@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@PJ>(A)++
M75]]%"O)Q4^@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