pg_dump and more

Started by Nonameover 27 years ago4 messages
#1Noname
jwieck@debis.com

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

#2Terry Mackintosh
terry@terrym.com
In reply to: Noname (#1)
Re: [HACKERS] pg_dump and more

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!

#3Bruce Momjian
maillist@candle.pha.pa.us
In reply to: Noname (#1)
Re: [HACKERS] pg_dump and more

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
#4Bruce Momjian
maillist@candle.pha.pa.us
In reply to: Terry Mackintosh (#2)
Re: [HACKERS] pg_dump and more

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