Обсуждение: pg_dump and more

Поиск
Список
Период
Сортировка

pg_dump and more

От
jwieck@debis.com (Jan Wieck)
Дата:
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

Re: [HACKERS] pg_dump and more

От
Terry Mackintosh
Дата:
Hi Jan and all

On Mon, 5 Oct 1998, Jan Wieck wrote:

> 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.

Sounds good, I really don't mind that what I did does not get used.
For it seems that I blow on the right coal and a fire started.
Dumping of views and such has been lacking for WAY TOO LONG.
And it sounds like even more came out of all of this then any of us would
have thought.

Also good because I have not had time to do any more with it:-)

Thanks for all you've done, sounds like you've done a greate job.

Terry Mackintosh <terry@terrym.com>          http://www.terrym.com
sysadmin/owner  Please! No MIME encoded or HTML mail, unless needed.

Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!


Re: [HACKERS] pg_dump and more

От
Bruce Momjian
Дата:
> 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.

I have backed out Terry's patch, and applied this.

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026


Re: [HACKERS] pg_dump and more

От
Bruce Momjian
Дата:
> Hi Jan and all
>
> On Mon, 5 Oct 1998, Jan Wieck wrote:
>
> > 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.
>
> Sounds good, I really don't mind that what I did does not get used.
> For it seems that I blow on the right coal and a fire started.

I like this sentence.

> Dumping of views and such has been lacking for WAY TOO LONG.
> And it sounds like even more came out of all of this then any of us would
> have thought.

Yes, though I knew Jan had this up his sleeve, and it was on the open
items list.

I have removed the mention of the rules limitation in pg_dump man pages
and sgml.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026