SELECT ... LIMIT (trial implementation)

From: jwieck(at)debis(dot)com (Jan Wieck)
To: pgsql-hackers(at)postgreSQL(dot)org (PostgreSQL HACKERS)
Subject: SELECT ... LIMIT (trial implementation)
Date: 1998-10-16 15:35:39
Message-ID: m0zUBum-000EBQC@orion.SAPserv.Hamburg.dsh.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Here we go,

this is up to now only for discussion, do not apply to CVS!

Those involved into the LIMIT discussion please comment.

Here is what I had in mind for the SELECT ... LIMIT. It adds

SELECT ... [LIMIT count [, offset]]

to the parser and arranges that these values are passed down
to the executor.

It is a clean implementation of LIMIT (regression tested) and
the open items on it are to enable parameters and handle it
in SQL functions and SPI stuff (currently ignored in both).
Optimizing the executor would require the other sort node
stuff discussion first to come to a conclusion. For now it
skips final result rows - but that's already one step forward
since it reduces the rows sent to the frontend to exactly
that what LIMIT requested.

I've seen the queryLimit by SET variable stuff and that
really can break rewrite rules, triggers or functions. This
is because the query limit will be inherited by any query
(inserts, updates, deletes too) done by them. Have a rule for
constraint deletes of referencing tuples

CREATE RULE del_table1 AS ON DELETE TO table1 DO
DELETE FROM table2 WHERE ref = OLD.key;

If the user now sets the query limit to 1 via SET and deletes
a row from table1, only the first found record in table2 will
be constraint deleted, not all of them.

This is a feature where users can get around rules that
ensure data integrity.

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 opt_limit.diff.gz
M'XL(`$]=)S8"`^4\:W?B1K*?R:_H82=98&1;$F^<>`^+F1DV&!S`N<DGCBP:
M6SM"(I*PQSOQ?[]5_9!:(`$S<1Y[+V<&I.[JZJ[NZGIUM1?.<DE.[("$(at)7WJ
M!\[=V:UE?Z#>XLSV5RO+6X3RX=1&F#W57U4JE6/0%-X&#AG;$3$:Q#`Z]6JG
MVB)&N]WZZN3DY$`?J;9FI][HU&J\;27]88.IMC6H9J^(&E_K!)Y.OB+D;XYG
MNYL%)<5-Y+CAV<K^&)W>%S-JUGX066YV7?(at)4VI9]3['VC5(;1H'CW?$V\.^L
MPKI5/U!**H7"-4/>_TCM3>0'[ZFUOJ(K/W(at)B8;19+C.I,G1#,_1&0I>AFU#0
MDI05'"\J%`I+:D6;@)YCR0\;&CQ=TM"&/G^1SZR&]];SO8A^C(C-?\^!E(at)*4
MA8#'=5:VO_&B<TY)(9N4`M`26IX3/1&8#?M#F#URHZH91DL9N5'7C&HM'OGS
MGD[>L$YZ`9!%B85##2/B^3#;R\!?D>B>DCOG(at)7J$#9<\6.Z&\D8YN,[P9T57
M(8U*WT(at)R-:)K)'3^0_UE29:5RVQ"Y.MI]+2FA<)W9#9G<Y2N9.-B$-^1P6A6
M&P\N,P!<ZD&]Z`?6JU;.`&(D`%CI$E9R51;+L`/FA-[&=0'N;7<X[6<`W#X!
M)ASOY*:?V1YF8$]S!U(at)\3`$@3#X?W-&(K0;?-\GJB'?/6E$.F8/@+)MWF(at)W-
M:)F<=P`N=^/`?*68NHS0`OP=C1+($A]0F7'V*U*0N"8;KQ1O$HW$+:<1<)YL
MI!&QOS3.;V6Y/YPE*2THL.9WWY&1[]$R;$68JZOQCWTD#<>R7@:4)EU`4[87
MVK"+V\DN_NL06!KA+JN41S?#8?(6;YG?1OHB5P%1,5SV<&4YWI8&RJC/5D$9
M@,?KH$.-&QU#[]1;^4JH4=,:[43DP6M3EXL<PHP[-GGPG07I>XMKU_)*^$4J
M:_C62)\M":E0!*1LHD63V6;MTIEUZ]*IZT>DPM>6,@SI5K#`"4:V"%P=0(D5
MA'0&"Z*1WFHQ`YE%_#4-`+\/D&\0$D03\9=+$!"LPU`(at)P&)OL[JE(at)2@F4]OR
M+IV`VMB8+.23@&<$EBIK4(K1<N/995(JJ]3P"0`2)A04)WV(at)I6WZ0OC62!8>
M+5M<-$#5R'E'5;LC;#[WP[`(at)%V\/#;:6NK,275M1-MGVFN`,QAL,7_A6`O2?
M<,K$>A0*\8(D>KR``XG73@@/8"LCX:N_-KE<@(#XX)RE%'SY)&2K##"4FJJA
MU`01VVS'(G:;M("&&Y=9.F#],(OS$L49RC16B.Q7*)0J6.!X;$3(@4QG,J.+
M$P026D_*N#G"BJ1QL_,YWH)J`TEME:1V33/K"4E\PD\N:#A?![Y-0;<O9.=J
MI0LJ'3<3&"D>6`?(dot)8NPLV`#?9`^06U\(at)X;F-)76\(-A?LK?AX&HP([9K;4)A
M>N4(at)8J87JHN8"<@KT!F(at)7\I8\XD)'V&`5FP.P684QL9^KJW`6D'=6JUC:-E3
M^.A$]CTIH7DXL^Z2;LH,O^B(at)`'8-E59<AQ<5['@-2ZP<-%W<^ES`)""PPN62
M:')RD5AM90EZ"_;J!VDSQ3VRX<L>J>O?E?J3R7BBD>(::VA$`V*%!#IVI/P%
M4S<BS(at)H8=D5![2_($XV*>=TLZ-("3L[L8>/1CVN0SH""6<]HJY*O%]O]%362
M,7N\OV?9$2ZA&-ZW1.=SF^XM10+H"*3BEA*/WL'V>:""@N?8J#R:\_BV^BV,
MQS'LX;O$X-YA.Z5J+]<)_^$(at)TTDAH?*<TD<B103'L=??B^%X7W\8O['NTNRF
MNEUI;N-CV\ML'&0_KX$PE*LEU!*SHE'MY,C=MF;J]=C[$!^B&%6B(+:IU%+%
MK'J5E':'P_GLYGK8GZ:`W_K!HQ4L4A95"D!5/^>\0JP':@2S86IF(]$(+S)4
MH:G5(NXNO]#`L^;<U%N::50376>"^C:;K1>@[,TN9>0%*<NDQH3!FTV%FE93
M,]O5/X&:?X)K\YO)J<+H:Z827-.K6E5OORPY>@IF/.KS_?+2:],`JZJA6E7-
M>DMK-FH*,8=]*%[\6QTI7OP%WE0F8<V:J35K"M.U#%UKF<HJ?>(HN>5+N"Y,
M;\V4",Y1T*BA81U`A#^21]3-J#<BZCX1[CY3'@#".02CFH0?G+5L)GJ.&/D$
M1'Q`P8\(45W</@G%SB?N-.X)5M,%W-!3>.]OW`6)8"DBGRQ\`O+?(at)1FC%(dot):?
MN.!)NJ%L!KC7?A(at)Z8(dot)Z3TF*#`5J`7="/)(3I+DNP$_(ORQ,O><2>;5D_?/'(
MA51+0M6?G*C50G=BC-7Q-CPP&T<\,V?VB-@(KC^R1I(at)7'%$`#D1'%,@O"(_D
MM:YV=*.CF_GQ$5/70;4VI&K%R1`J&<V=87<*ULMX.OOAIC^<]\975]W198F&
MY3+Y!^G_U._-)_T9"`;2$6\W(QGN"B@@\5+(dot)*(at)U/+GY9:`1_I6L:NZ4ZTTEB
M093`!)?68I1BX_QYH\P.QC$3-F?X1W!1N';R^(=5'>`<!I/!,^T#/+/;#KBE
MU3';^=S2!%'=3(EJ5M",)=JM[[NP-4,921IXD3^A+I/%L%1+RPWYUK/OK8!%
M$4"*^B(dot)P(at)57IQX]0`K%I<XY"I`49RWIA0V:-NV76M5955R2QV=!:R7$4ZXUQ
MPO1Z,&<&[7Q\?3/Z?C3^'\8K7%*\(?*3?402?RK\YXCCDBA]7J)@V(O_3'D[
M_A!%?E)G*0E6DCI4V0&.SU8DL'+`D(at)WL`H$2>/NT);L%FP?10AR\1+$GEMV$
M'\#P)O$9238H.XJ1E(KCF#RDZ#$?A90?T&R!RH`\DQ\L(H\B(at)D?DF2)>!Q1]
M/[8O1'1><"!C>SSK7()?Q_D1A-;-#%AR.NO.IMD,7@,&KQN)AZ2P\_C[>>]F
M,AU/$D;./XX0`H_)R;?HUD7I4X8Y8K0W04#1`U:#7!FAKW,Y#?$>Q:GH75W.
MI_UAOS<CWWQ#XKY/+N3I!711YKNT`?92HY'LTA>CZMA#E3^,W'PU(at)5(#S^#7
M3ZCDMRV-G=IL9;$#MJLO:GJVOMC?%/2,V:G6\U6&46^V-*/>4CQ)6:0LK$<?
ML9^3BXT'T]9CH228Y(BNUG/7$0%@(8,+N%H(at)I]9/)12F&A&--1[1&/-(at)V?EA
MR)Y8:KY=!6\)D%B;'UH8?Q/EKTM2N6]9$JC/796<EK`H>J>N[['ZZFW-;"C)
M&;P@<>ZM]1IZF;(\BH&W]$&0!1J/K9U<W%OA=',[=+P/(1A8Q2C8T"+85D6F
MW8O\:"D'`\"I*USDL'(dot)(at)`Y>*P^PP`E_+7(3*JA,19<I"N,,<^Q$RYCB(KQ=+
MB\\R_?CJ(at)76PR(dot)<=I78?\RA(at)G\L]>4V!?8Q.K;IG3[/C/363!`N:B<<>^=`-
M[%\W7,-CB4O9;\`<N(ONR^=,"Z(7FF8&K(at)9=W[;<>98PP+()#+F$#%>6QR5'
M=Z6R"7<AU:[4VMVNWGQ!5YR!<GKJB8!R1D>)*$I:'2&-6"@D.+/`.7_Z#]WB
MJ.W*;(;:ALK@)R.;G_:VK()+T3&;>]BII6M&2SU6PX*VKKI[,I0^I2ZUHVFT
MBCHBA@/Z]E6IE)2#'D^B7&50VBRC`O->1-!:!%V2J$L46%ZX](-5(at)J2T%B9#
M+F(9B^=8MAEH:T#J>!3`3!R2-0ZAZ"D'%,^<)`KR-Y>XWB8(_>!8XM08U$&F
MNPNLU>E3%D?PFKWLQD$R>,W<RVL9S>H=L[HW(Q&,Y+KB!.*KL9N1:%O`+_[=
M&?PBUV2G'N+)QW:-9:,=>/;1LJ/MC$2)<WTW1P\J3DT45O[5S7`V^.?/LWX*
MW^H6X1_15<8&1R1:%`JPQ-&<'\6!V0LOMT_,@-)XL&W.#IQ"^<9KD$K^R%"@
MJ12C8"\<S%]'<RL(K*?Y+;#>(D1S*MPJ8(at)CHQW4@VEA1%``@<.,\LH([&HGR
MK0*3GVW`5"A]P1#CT"NY"_S-.A[5#/CHC(at)9O07EU@[N0S^77S#?]%N7E10'U
MVIS+T:0*HQ/4\BX*L/?FT(62O&$DMNC_RRGDK;%-R&3"G,F8+Y[8[%,\3+%K
M;F5/Z(8BY(at)4Z!]QTZ(E&]OW\WG^<KRSOB:O[U%#4D<YE!"7!(at)M-P41BOHRG]
M90C/20U,$G7CNKY+5WDGCV:CJ9G-:K*_F(<_O];(V_ZL]QY^AN/N#'[0J82O
M_N#="!XFXROX1ON`-7HWZ8X`Z-UD?`,MWW=_'(S>P>_X9(at)*8&,0`6(at)U&HSX6
MP,.T/YH.9H,?^_QE,L/?67_R8W?(GL;P/>5+]J\QMOV^_[-&AMW1NYON(dot)V(at)T
M['<O61_#_EMH.QQ\CX7C7E<,Z*K+!G\U&-W,&#57X]'L/3Z,NE?R0'34G0W&
M(^P1GFXF[*'WO(at)N$COH_`=;1&/_C`Q#*VMY<]2>#'F\]?JN1\0C_#V%HXVO$
M!;\3_'_9QQ_HFM'/#Q<-F.<DCOA?/<]X:O+7G>X<?]#`1*9&RB(dot)L(at)TNH*X*Q
M0(15H8(at)W\GA/`RI>!)0J:,B]]0#^51J`V?420!&WW)@A&5*HH-AM!=5J07M\
M97V(at)S#M+*J0%4RAXS(WX98,>Q&LSYSBUH>.IO:Z>#\,;%)EFZDC50^<7Z8F=
MDM>MK9X4A^5U6ZU#.I,J0Y?&'[-(at)7QL&RX49#,NR^%-\%.OMN"?1?4G7`(D1
MFZ$)5.Q:`)"Q!?0L'YBE>+(at)C'I'/ZR%5^RQ)??T:C5<14?/D#/#JO.G'_(ZF
MKL8CF@;HAJ8:H?^5=*<]>:Q<^$18/\5OB^?D6=1?]C,`+A2`LTK_ZGKV<^4L
M"\M91>315,X(GR?A]VVS8Z?`ST5WU0_YN_9WDJF59$\N#SJ4Y"\L."Q1K0Q?
M9OE<]/LKR>O(at)$"869=["!5XIHUJXHJ(Y@)^GR=SMK5,8L`BW=)OX2O/CF/3&
M8V4QFWDB-2KC'H92FW,)0X'8NH'QVM@&R+A[H=3N7+Q(M=R^=9&JW+YRD<77
MPO'BD\PRO$9^>J9R,KW`&1.Y5]SBVYOK)?>-N,ATV!?[0)\>_6`19D<`DMJ]
M/ED"]ME^64[3.K3KF'M"D(at)9L>,-,3C`^%9E]740]?-G_Z5F3A:!OG"ADY>]!
M_\VF295'@V*LWZ'XA!?#L**BU/`)=$B]T&&I:2E30`&(J+7(at)E3-0]$D%K"*P
M5C$Q%I0JGQ>/GS5QQXJ3)<782Y!U</QO]M/]&\G*7#VSI1E5-<;/"FH*W>`@
M+$!S%F.K26)WZ3(J<A,J+G(^(%5H3TERV(XI"NLJ(at)0-B/%8*M(SB8I\1"";C
M95)D,]*850:%^?LHH(^P$E3^7M*EXVW'TW)@LO=4#G#&SFID[ZQC$$#K5J>^
MYQRF:IA:U5`L'%9(at)*AEP]]1"J\T/:8D^@"0"+\]-L(at)5%FHQ(P1$:RO'(@T,?
MB<.%6+A98[2-+G82==BQ5]JZ$*F_Y-=?B5K;V\D+WA*D:K9QNE<YG+`8AVQQ
MQ(2/^/3TE*4_+47^$I[!!QN7DM4FY)FJ()\7!'-%$`D+_8BV_'PVY"E?)9E0
M.T<0&!"V^!IW+I\U__;?)Q<P=2SBN"]X%MG^^FS-:-]BKU1--E.E0#)8J9G-
M2OG-:AW=[%0;>X)GNE:+\X24R-;V+6$9VA(G(1B](A6>"3&6YZ*8:,QR\N("
M,!5(at)LEZEKB1=\X/5^(Y+YFT7R9S9]WP_^]X-BPLR2G?C(at)G\RI8>O[KSL5&2G
MBX%3:!B_Q\4N]>3LU9%K+^\J95Y*PEN<KN7Q8G(at)0"=J"@M_AKM874'#\=:PC
M2<P4_55=JU;CO8MIEL2RHPT3(at)\'&B]-#3T\/7`T^+L-B<C.2^7SB.GEH/5"0
MSDL_9-9OWUN(&UY"MMZL%]!<E+&SV"2#E^Q<F]*RDB_$/5Y)JW(C^O>G55F]
MG0R2/XSZS)5O5K5JLYF$+YD*FR/])7['E3%3%)_Y,'>#!0<PYK7#PFFI^\QG
M7/01VQ$OU8?2,&4Y[):KI_"[^E8(\/1=8GPXO6>Z<4]]6O?N`=S5P/4M8^[8
MQF9'KW6,/:>EK:K64F_6`(at)]$/KK:(<\MC%(dot)!T1Q2+F=+*44_(at)IWO\8NE3!I)
M+I]&5A`==:.4Z2\5S\Y5Z!>XC'N>C#6^H(R(8?L<(dot)\(at)8`=[*9P-E6%BT(H!9
MB<(25]K@&[(at)TT$B<J(at)KVFZ:T8OGQ924M[X>;_N3G.3-%>:8:6Y1$I_S?6I1C
M[([_BJ7*%PX\(X8)%O:X)1MVJ[-%PRY<AF1H94N&`VVKG6J]4]V3RM_0M49=
M^5,'\-I44RCP#(Q9"DJ4^ASU$WL/B1504%_>!W""-MZ"!EQ5!O3!\3>A>J6G
MPATVX=\A+S"T:F(#HOT;W(at)]-7S")?)XAD]62YS,4\EN*K!BV7X1F93$+<.>P
M&G6-!Z/F.C4F]M8**7JR/(T0&V$)D;XMP>*\OWK2J+:UAOHWC!HU`PJ2-+DX
MBSU),N$4*']QI62SQ(LRCM'F">%\B#Q7_A;<T>#IG*<&6X2_LHNFC+*R0/./
J5".V8K`1>&?LC3PZT;V_B8(at)X[<#CAC]SA9Y)<AHC_9+_!=<!%F+O2(at)``
`
end

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jan Wieck 1998-10-16 15:39:25 where to get stylesheet?
Previous Message Bruce Momjian 1998-10-16 15:21:09 Re: [HACKERS] Did the inet type get backed out?