pg_dump and more

From: jwieck(at)debis(dot)com (Jan Wieck)
To: pgsql-hackers(at)postgreSQL(dot)org (PostgreSQL HACKERS)
Subject: pg_dump and more
Date: 1998-10-05 17:27:14
Message-ID: m0zQEPj-000EBQC@orion.SAPserv.Hamburg.dsh.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)debis(dot)com (Jan Wieck) #

begin 644 dump_rules.diff.gz
M'XL("`4`&38"`V1U;7!?<G5L97,N9&EF9(at)#-7&E3&TG2_BS_BK(B;.L"U+H%
M"QL,R![%BP468F<W#*%HU"71MFC)?=(at)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[(at)Z\N_8;?=D7;/?89S'TP5N-<PCJ52!9;
MO`JR)6+4Z*(09Q#ZH_MYNG$0WH=0&\A(at)U5&V910,MLK0Z6R2Z_3[YWT8!6GB
M]**\7P4ILI$`WBJS2`:0I(at)\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"^(at)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*(at)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%(at)4]'R%ER(dot)]+TT00(dot)0WI-](at)=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(at)\M!
M_^IDD,/OP)F(at)57#!6(A-4MS3SA$\<YLD1*JWR()Q-'3$96)XD<T+E>.:T(at)+#
MWM:`X;-)HIBF,Y[,'Q30]=-%9%2>=JM4+VOXLEZNE.I6.9;A1HMMH[6VQ5+;
M4'[;K3!SAVQQ2;>1--\F(at)34JK5*CWM#ATA^1(dot)W44@@XT\$!"^A"N1!!`TOPQ
M"2#Z5V<=$KL4(&D(at)P3F*1T0E(dot)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(at)1B''6<&/4[__PN),"FJ-DHM5OM1`Y\,TZ+"S4U\:X4P9J6/T\V>J^K
MI%,T--;;IN1FTABKW+1*\*>E(at)\!%UPX?,#;&"WW;F]#![;3CA?XC*>2)%][E
MOD34?V0J#'ZD+XW(at)#K%*1+YB(#IVYPHD"/?6[<5X9:46"KLC$SE\EX.P;0YA
M7_E<0V1QP#3%*K>JP&$[D2-[+(at)[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(at)B$&EW%"AATV'5F\!/H_Y;I\,4E_]"1LT3V6P#
MXZW&<YDA`9/?]L_?;VN&;/+7GYU^![.=$>!P<`,3%R"0H!>](at)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%(at)!GO32NGU4>1J6H5EHEJUIM
M:DJ!9TU(P!)[T"_F\_PIA"%3*-+TX-N=.Z4Y*`,I8]A$OMQ!(at)R]W(dot)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(at)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(at)^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(at)]B?!40))+9C"@D$&3X.VR+BCIL
M%YPZD0\*-(at)4P$=D3&A"8<D^=H<M,AL(at)='J0+\*`46')50#U'6&H<X`+Z.H-.
M(at)AQ262),,6'2HONWD3<*()Q6!0/0H8`W_2[.)R[CM4B$?TOQ2K;F51[`40R2
M#1DD(at)D'&QN;,&:?<JL,,QP?NMIWAE[^?:XB8)-,0D2(at)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(at)(dot)<W2[(AX2W*"8FG'2C`?`W
MH7Z0:,D<%N%N3NF(F#^MVT)X(dot)^7RQ[(at)*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(at)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`(at)7B3^,94/F+L*
M9\SIL5TI5%<7RJ',IR,@#Q;56H3&(dot)F9_\+`U6(at)KX2$*PM=9"(1=^LX.@'Q.&
M+VGVF'^#2(at)R%7;SC6?-,I(at)`/3'?%336V[CZ^/_[WAZM._S]GG=X->\LOM7EA
M-`^T[^X0>.$WWY)E\RE$%D%(G<47(WLZ'0(dot)KZ3?(at)P^?NE/HZ,06]>ZUL65US
M[Q("CUWG05ZC,=J<["T%F"#(dot)+C*$(at)]7^^U_6_N(#%]DEV/4HR,$7EK^Y>-?O
M7`Y/SM^_/^Z=#L__3T.Z"W#GC\Z[;H\(at)6&'7=MB)U6LY'@2(;CCTW!&=/@J*
MDL(at)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(at)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](at)V<[`9(!,H0*8NHLN?L4Z!W&&(;=<Z<3*R;,L$4#!\$T>+TD-
M^E>7(at)\ZINB7%NA&\BV_C^[#K)-1,)CCCZHK5F%.?TIP4XD&B*%%93>O%!X;H
M$^;#["$(at)PA1+(dot)=U&],4N)`L$P?2:?<UL!1Q(at)_;!WY(at)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(at)7R9V(dot)?D5Y#\(dot)(V*G=Q2F$5Z!<+1ER".!`*+(XMB
MB(at)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(at)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(at)X0;FU%JI$^H%N:W45$
MH2\;_6#J>A"AE(at)!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(at)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(at)YHV>70^+M,+X9:@&1.HL9-AO'5T(at)-MWSQU!&#==BF_
MI9K8Y=](at)DQ]K\M\0PM0A^%P\_L(dot)W*<@WR@;@+_=4[BGD3CGDJ2$N*?(at)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?(at)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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Patrick Welche 1998-10-05 17:31:07 Re: [HACKERS] need help with csh
Previous Message Thomas G. Lockhart 1998-10-05 17:15:38 Re: [HACKERS] vacuum analyze problem