pg_dump and more - Mailing list pgsql-hackers

Hi,

    Bruce: Would you please apply the patch at the end?

    Terry: Sorry, but dumping views is covered completely by this
    approach for dumping rewrite rules.

    Playing around with pg_dump for  a  while  resulted  in  some
    fixes,  enhancements and some found bugs not yet fixed. After
    all I was able to get useful results  when  dumping/reloading
    the regression database.

    The  reload  didn't  recreated  the full regression database.
    Table f_star couldn't be restored correctly (see below) and I
    don't  know  if  the operators <% and >=% for the widget type
    have been recreated correctly.  Anything else except for some
    datetime data went in successful :-).

    Bugs first:

      o Something in the datetime type seems to be broken.
            regression=> select 'Tue Feb 11 02:32:01.00 1997 MET'::datetime;
            ?column?
            ----------------------------
            Tue Feb 11 02:32:01 1997 MET
            (1 row)

            Is it O.K. that '.00' after time is omitted?

            regression=> select 'Sun May 11 10:59:12 1947 MET DDST'::datetime;
            ?column?
            ---------------------------------
            Sun May 11 12:59:12 1947 MET DDST
            (1 row)

            But this is definitely 2 hours ahead!

      o Dumping  inheritance  doesn't produce the correct queries
        to recreate the tables. After the regression test,  table
        f_star  has  attributes  (aa,  cc,  ee, ff, f, e, a). But
        after recreation from dump file it reads (aa, a, cc,  ee,
        e,  ff,  f).  Then,  the  copy to reinsert the data fails
        (pg_atoi fails to parse ((1,3),(2,4)) as data for  column
        ee ,-).

      o Dumping  operators  needs  to  be  checked. It outputs an
        operators commutator in CREATE OPERATOR  before  that  is
        defined. I haven't checked if that is legal, but remember
        that   there   is   something   in    the    code    that
        commutator/negator  should  only be defined on the second
        one of an operator pair.

        During reload  of  the  regression  dump,  first  the  <%
        operator  is  created  with telling COMMUTATOR = >=%. The
        following CREATE OPERATOR for >=% fails then with

        ERROR:  OperatorDef: operator ">=%" already defined

        The two dumps from a dump/reload/dump  sequence  show  up
        the  same CREATE OPERATOR statements. So it might be O.K.


    Fixes in the patch below:

      o rewriteDefine  now  checks  that  view  rules  are  named
        _RETviewname.  pg_dump depends on that when deciding if a
        table is a view or not (to omit the data in the dump).

      o The rule backparsing utility functions now  double  quote
        all  identifiers.   This  makes the system views a little
        lesser readable. But pg_dump'ed  rules  succeed  even  if
        identifiers contain upper case.


    Enhancements to pg_dump:

      o User  defined  procedural  languages are dumped as CREATE
        PROCEDURAL LANGUAGE statements.

      o In functional indexes the function names get formatted by
        fmtId() to support upper case function names.

      o The  check  for  ClanguageId  in  the  lookup for trigger
        procedures is removed.  Triggers could also be defined in
        procedural languages!

      o User defined functions in procedural languages are dumped
        correctly.

      o All views and rewrite rules get  dumped  after  triggers.
        Views  are installed first as regular tables without data
        and later turned into real views via CREATE RULE.


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 dump_rules.diff.gz
M'XL("`4`&38"`V1U;7!?<G5L97,N9&EF9@#-7&E3&TG2_BS_BK(B;.L"U+H%
M"QL,R![%BP468F<W#*%HU"71MFC)?=@0L_[O;V9=7=TJ7?A@'3.H55U'9E96
MYI-957+<\9CLC'P2^*/=F>].]F[MT6?J.7L^_>:[(96?IW3L>G1WA!77U7E1
M*!0V[C#S?N:1\U%(2)U8UGZUNE\O$ZO=;KW8V=G99+1$!_7]2GF_WN(=%)+_
M&%F56K54J;4(*\`1>$&;P//."T(RF;<S_WXXGPSM,/3=VRBD&7PZ8.]&=[:?
MR60*4.+9]Y07NEX(9>[!BV)<@S[,Z2BDSA"K?>P=O^^<'@^.SSH]4B3U&VS'
MFNX5V`<ID,L9">^H3\G(]KQ92&XIZ?8N!YWC4](['_S9[;TKD=W=73-/C7:I
MTJQSGA9ZAE;8-0EM?T+#J1N$9#9F)9>=L\[)@-BCT`41WD=!^.(E;T,?H'#Z
M2.[M<'3'ZM*OU`N)3Z<VJXRD\/[WV"=[/52O#\D=M>?#V9QZ?HZ_F]U^VCF"
M"BB0O!#<F.32#0])[^KL+,^GIED&OAIR:GX#7Z5?SYAQ`MOU4K5<D1.8R7S7
MV'T9LVM[#@&EMZ="5Y`U(K3%AD\H).<]P;]LZ$=3>#OUJ>T\)GE;)!,8<8;8
M("`O!<'D;U8W,Y[Y).>"`,H'Q"7_(,L:[AQYT?W9;/0Y@'K%(K9G<]EN`(O5
M>)DM8?&)C"W,V^_BS32=U7*[5+5JL8VI6N52M5(S,,^T:32=!31%;1[-";,H
M()^B005`BU%_F1"8N$!4J((.&?8[@Z\N_8;?=D7;/?89S'TP5N-<PCJ52!9;
MO`JR)6+4Z*(09Q#ZH_MYNG$0WH=0&\A@U5&V910,MLK0Z6R2Z_3[YWT8!6GB
M]**\7P4ILI$`WBJS2`:0I@\KJ$(Q*EF"G)REWBP*W6FP9SOA'A+`OJ5\F;&&
MV9,9JQK\6,WLQ]8W;^Z#(ZM6EWNQFE4OU2J6,ACH0,`.ADPE7,^A#\1!]^BB
M*HD*.^H?+]C#=20UXC8:PPR=]#O'@P[,1+=WVODW3A$L.OA[=0G^!Q]R,$5L
M=3D//AWM',%0;A!Y[I>(DG^2[%6O^^&J0[)DGV1%39RM613F7N=8FREK)G4K
MOU#'<PQU4("2XYC^7\'Q=?95<)U%KL73,W-NG/MJNU0#P**L"RNHQZ)!UH\=
M)Y8,19-!IPY;:+)&4CBQB8!%;H>YS_21"^@ZF]6<FL8]U/@(_WNS&_1MY[>?
M8'5VG6,)FGK1_2T8[1UBY;F-37<\<QW9,YT&U%@)_'H,P]C"URD`.;D.ER/^
M,]*6%Y9B&5O"`RU*@W3'S`'99!QY#$B`UV72+!%;"!=0@&^'8,M&4SL(N'2-
M,]:HE&J-=NS>00G=81C-P>-<7G3Q*;1OIW3GZ*L]#3Z6;PZT6N%HH18\.308
M:;7&WDS4&GM,\#G1%@4]'R%ER.]+TT00.0WI-]@=S`#0%-&<H!CLO>Q8C)L7
MK6-;S'2R42O5FI58)_^W6(ZU^NE,%TT]QQW'\C"J1`M4HJ6K1,)5CNP1Z-=T
M-OL,$D-O.?=G(_(*UK'M3JD#GEK3=GP7*S-^NPS]"%S)(<G)8(;5(>\Z@\M!
M_^IDD,/OP)F@57#!6(A-4MS3SA$\<YLD1*JWR()Q-'3$96)XD<T+E>.:T@+#
MWM:`X;-)HIBF,Y[,'Q30]=-%9%2>=JM4+VOXLEZNE.I6.9;A1HMMH[6VQ5+;
M4'[;K3!SAVQQ2;>1--\F@34JK5*CWM#ATA^1.W44@@XT\$!"^A"N1!!`TOPQ
M"2#Z5V<=$KL4(&D@P3F*1T0E.C,*,1_(/N/U='R)$&2A.QZK*M3O!N#6?73N
ML"S$`-]<#&PAEAB&CW/*7"^/<49V0,D;Z\U^PB+R\42\/#B/7<$M1%6?U4KB
MC2O&QE<7IRB`=8VKQL;=WF6GOW[DFK'Q*9"]<F284SN:AOMLC32JM5*C4=$!
MY*_0`1U8_)@67#^C'FC^\2F:L*;Y.EU8TWR=-BQMKO3!:",:5JG1+.M)-#E_
M,H>S*'$H7)Q31*VR#0>M,`T,(@JLQ',3#-F2(XB6^>PL>(U=D_4T0&+9.X;O
M[%T^-I*"C>XX)O[.#HCM"27Z$ME3=^R.1/8+D:T;(D]LR32:()*6GG_[)2(I
MDB5F?E-!F1W-IJ):[6:>+$&3DK5K5JDML\\B],",CC,CP&LPT[-7(9U.AW;`
MPZ>_#1J/YB$-8]F;$+VX3P-G=L\^M<QD$J6W:^U2N]Z,<<./DK.(JU<15%SH
M8U/XW`;-;#>;B8QWLJN`2G`+Y121$$#%[(&AIA_2=/XVD>_2WZO:8RW9)9H(
MF:2EH@1B''6<&/4[__PN),"FJ-DHM5OM1`Y\,TZ+"S4U\:X4P9J6/T\V>J^K
MI%,T--;;IN1FTABKW+1*\*>E@\!%UPX?,#;&"WW;F]#![;3CA?XC*>2)%][E
MOD34?V0J#'ZD+XW@#K%*1+YB(#IVYPHD"/?6[<5X9:46"KLC$SE\EX.P;0YA
M7_E<0V1QP#3%*K>JP&$[D2-[+@[5[*Q6,^.,;L6Y>:XANH8_VBX?R*5>LJQX
M`V+!6\3+*+V(-HAI5ABX%0',YA&?9=6`(ZO63EB\]>1O2.=B1'](EGE4Z&)(
M'^9^[LM=K!();8E3<ID,CE@B$&EW%"AATV'5F\!/H_Y;I\,4E_]"1LT3V6P#
MXZW&<YDA`9/?]L_?;VN&;/+7GYU^![.=$>!P<`,3%R"0H!>]@A@<(9':8Q/S
MC<NOHNW_/2/;/\DV/5$<1JVH5&%Y5ZIZ/E!L36:.)Q.?3C*$D((]F:!L>`D3
MRLRA!V94`'5WCN"/IOHIY<^9L0"#ZI&N^WXXY(EO3=TS)->#P9&('!N*6^E\
MB7RU_3EX;O<A;^P^F\=AF4Y4`'U:%1U^;L?T%@!GO32NGU4>1J6H5EHEJUIM
M:DJ!9TU(P!)[T"_F\_PIA"%3*-+TX-N=.Z4Y*`,I8]A$OMQ!@R]W.T=SVX>8
M!>3/ZJU::[)[;R86V!TJ<G*1P8`O%2*,NY=G'<CKUPM4\C!.@,$-QR^N&#]&
MI"^3D'0LMK8+O<Y?A6P^;T*968]^VU7J6*TU0-QU/1/]L\2M!/43)/YS.#8K
M7*V*$JCI"J>6Y)-PO]Y\%<!_::H3)T`TC!\_?%^R6%,AOR35=4IJ$O'XEMBX
M$IWX-(Q\C\RA,R>:Y^*LO`0LU3H8JVHSH1T_+INBD>]$6/0$N5VOE=P6IG-;
MD:[H>@-9&_6R5@6]K-7*B_E[FT`GKC?!DU]B!R8'AI=G*3;)W<IMF\7TJW0'
M`GYR,#V>^=0>W>6F[#P*<*X&XZ><K%H=''FM45Y,,_\PJ4*,*3J7,W"]/0M&
MZ=<;$#C7&[$;H@^X>8-V#`\[,I=7&-]/_.'8=AP_]WKL>N/9<!:%\RC,P\O1
MS`NPQ<Z1?(IHPG?BO]=NX$53(&K'4IY%'M5`!CE/^_OLF%*\!8?9;;D%!\]J
M"TX@,+$D]0XB[[,W^^9!O'6H%J-X_[%\`UR]N2Z_X6)+G!1Y\RIXP\](,?8Y
M$5QWV<S76RBF5HQFGE5,_/C*_YZH#">T7&]O/ADZT?U\;S2[OY]Y\F"6X47J
M/):AAN$85CMU#&M=J_I^N;9?JRP_?54M-TM5JZ*=[\."2KSHF7=F/IMM^P`.
M&L,<:+LP6&$R_$K]VUE`\]QFCH4,@]"AO@]B?!40))+9C"@D$&3X.VR+BCIL
M%YPZD0\*-@4P$=D3&A"8<D^=H<M,AL@='J0+\*`46')50#U'6&H<X`+Z.H-.
M@AQ262),,6'2HONWD3<*()Q6!0/0H8`W_2[.)R[CM4B$?TOQ2K;F51[`40R2
M#1DD@D'&QN;,&:?<JL,,QP?NMIWAE[^?:XB8)-,0D2@N>2!EA]3(N\K05"M5
M8+C]9)7>GF%;$?;[.5YMEL2GP2ZI-\L-DZJRG64R-ZOOUYO[H(9+35.K4FHU
M8\.$7]4<@O1"=T2^SER'S<+QR5GN;?>L0PI<9@.<W2[(AX2W*"8FG'2C`?`W
MH7Z0:,D<%N%N3NF(F#^MVT)X.^7RQ[@*YP!?<7N2'J>/._*;#%+<>!#%#.]C
M=$='G]_._`_1+*0YO?=@@?/1E-J^&B.GC<:&,4KJW*-H8A(\8`%O-XYI=,WG
M/*UVN62UZUH.NPW8LIW8CI5^E\6)L+X".J4C=IV!K=*Q/[LGH$EBSQ.>Q-T9
MDN63D_W&CMO+*KM(^R&1NZ10BTDWBWN"<>-=<:H``8#U1G6%E>1Q!GS%#Y6_
M`1B0SB?ZL,0/R<4'^D!'.5S,GB<R>$NL;Z/=+#7+FL-MELM0H&Z&"!.$4K^$
M:;VWN\X#=,QE'NL*4:J15`O>4FGVI@VE_^2JNL5P<EMY/)Y&P9UHN2PHKS?*
M"+\;>E#.[BN`!CD/_,X"?_R'\FJBI%B,]]^$+=60'M/`CZSB#4X\T#M!I\/1
M'EAXD26"X?2:"#>PQHG$'5T'ZQ)370]C"]6=K&(:'_`,A(6H5:#%Y03BS,3G
M.#C0;M10(,*L&:_3E$$]X(^V=N!;%8K:"J.).6"7*R#Z2N`@7B3^,94/F+L*
M9\SIL5TI5%<7RJ',IR,@#Q;56H3&.F9_\+`U6@KX2$*PM=9"(1=^LX.@'Q.&
M+VGVF'^#2@R%7;SC6?-,I@`/3'?%336V[CZ^/_[WAZM._S]GG=X->\LOM7EA
M-`^T[^X0>.$WWY)E\RE$%D%(G<47(WLZ'0.KZ3?@P^?NE/HZ,06]>ZUL65US
M[Q("CUWG05ZC,=J<["T%F"#.+C*$@]7^^U_6_N(#%]DEV/4HR,$7EK^Y>-?O
M7`Y/SM^_/^Z=#L__3T.Z"W#GC\Z[;H\@6&'7=MB)U6LY'@2(;CCTW!&=/@J*
MDL@Z;=S%\:P"W\4`>RSU2MEIOB$!Q6XPGZK2\_YIIT_^^`\11^Z+:RSPMK(8
M7%V<P<=J422U/+\O+^EM(Q2NCHQP?$*KZPM+S(B6RIG!&O*HJH^P+RO>R*E.
MJ*RYNGJ=;"/U;5DC^5YO);77W$:^S2I&TM?1^"J4-\Y02HFAF$#4B5K6KPO_
M)>D5XA6>(W8<"WZ#NXTB4>E)/=4L;;9R&<DQ1"0KS36_HB5C0#;6T:$:+#'(
M>GVY@R4$4E)VE=\B4TL`(@N\K@&&TT-M*BT5"<][B#SE,FU3:S"C([E$!(GL
MF$-C.4,"""5QYB9D:4JQ<7O9(!^3(&7*R8ZO.EWTST\ZIU?]XS-R=MQ[=W7\
MKD,P9:,,QI]@V<[`9(!,H0*8NHLN?L4Z!W&&(;=<Z<3*R;,L$4#!\$T>+TD-
M^E>7@\ZINB7%NA&\BV_C^[#K)-1,)CCCZHK5F%.?TIP4XD&B*%%93>O%!X;H
M$^;#["$@PA1+.=U&],4N)`L$P?2:?<UL!1Q@_;!WY@0`ZW`)T*DT`=54-61<
ML:Q*"?ZHJ(]!/^XROZ3</)$>\Y/F5PLX[A"(T,I8$2ZUU$UV2]UDYPL<9\V]
MV451P.PS],:3_0=+R*_6@7R9V.?D5Y#\.(V*G=Q2F$5Z!<+1ER".!`*+(XMB
MB@R)5+N]0:??.SZ+`:ONJ`2W,/6J(:@<A./Q:9/Y8TY)@)USXOUE-<^$=Q&(
M<?23;8:%N'OIL">;C7?YX:<Q"EVEQXS[4XA2`4JN3!XVU@N2F%&J(D.=E7KC
M1BWB]0AM`99LA=%6NAIIXL'1K`9MJUU&QFA9EB$YZ2.6X3D)Z+B/?^5D8V"K
M3;H8?AVDVU)X25"WJ>PDJ)OYIOAG:V%R75H*^!A+H@X0;FU%JI$^H%N:W45$
MH2\;_6#J>A"AE@!9@TR?N%+3GKB\`"J6Z^8ZM[?0BK.E)Y]B</'VJG<RZ/)+
MXSD4F>;.-<'EY4XDR7WB0/03H%!5C:4-H$SD+_Y>XCSJ&.0W+"TQ\D7D'.,1
MP?T$-&3H`Y1S</XV<44[AAL.PK<_'L]=)Y>"=,EY%TD*O)O],CG2"F\#HZ<F
MDNPO:WZRO!TX!]&.K+7[IB%)5F[H;4E\[/$VIAN\U?*Z"Z0R3Z,3J%*%XWD4
M!JAA(D4F+BY4K#8`'_T\X7-,OERH):+6XBK"S3]'A+_=4VLV-`C4L*Q2I:%M
M)[+T':=YKT`N/[MS\J]NYR]U"R>0E_?EK^/PN_K!OUSZ#9.3.9&(Y#(1/RX@
M<FNPWH'G2#CG##N\R%_QPTM]\/JP1+4>XG)Q:@.$=<'*4A7C\B7PKUD&1IME
M_8`;@S0@3$9C\DR@;FY>!<S"O`*/!9]Y'I7(PS!?^'SP'*P=LI\&DJ:(Y;79
M;S*5;^(#>2Q84,,F2A,-$D>"*JT:H->6_L,+/XU\KIFRHU_%A?FGB-H`P-OM
MIO[K-=4&;GYJ"KD(R;5YUT$Y<\2X$K3WH4BT?_QTHRT/X?._QY%5<E=(9$JW
MVQ=:ORV43I!"K!4C6@YHV>70^+M,+X9:@&1.HL9-AO'5T@-MWSQU!&#==BF_
MI9K8Y=]@DQ]K\M\0PM0A^%P\_L.W*<@WR@;@+_=4[BGD3CGDJ2$N*?@J$D/Q
MMGYB/UBDB.1$A\K6:'LB,D.4M#K/AOQ_`=2/?ZWI'0U98*_=+19W=,,[-^`R
M,?XZ4RI*@,!`GD&&3G+:/ES\HTLJ8#B^U&\QR\!"-"BIC;YTA*$V`.7.X6$B
M(P3]]DY3.X"\G\/DWJ&JKW+.6B/,BDJ]7=229P]CN&5)9Z;YA+'%DCQ>LY2#
M#>(!;E*,<4W26!CBA?AEUJ1UI\`)YI+NT6(DU&LAJ\SM&DLKFX0CTH:<:W,Z
M-:8EGV;Q9\0:8JN4;=5M=D+C;NDYBKOU)S3NGG9"(]FLME^U]BLK#H]5,,%5
MT=PK+U#Y.OH04M\C7<_A?@O$#L^@34$./5<!E$%\9Q(2U>/S&0A;M]E%)!OL
M(N+DI`?Z95N6)J[X":Z?SM7B0.P(D3X.%/`>U(DBT8=VJNC%_P,9I`P-1E4`
!````
`
end