Thread: drop before create in pg_dump
pg_dump won't drop stuff before trying to create it; this makes
dropping a single table (say) and recreating it difficult to automate
since it is subject to error if all the stuff doesn't get dropped
properly. The following patch causes pg_dump to emit DROP ...
statements prior to emitting CREATE ... statements.
I don't have experience with things like aggregates, languages, types,
functions, ... so I'm not quite sure I got the DROP ... syntax correct
in all instances. I tried to follow the man pages and the fields put
into the CREATE ... statements, so it should be ok. Help testing this
would be great. (Do our regression tests test pg_dump?)
At this point, I don't understand how the CREATE TRIGGER statements
are constructed; they don't follow the same style as anything else in
pg_dump. As a result, I'm not clear on how to implement the DROP
TRIGGER statements. Help appreciated here.
Finally, this patch should go in after the earlier one I submitted to
create SERIAL sequencies.
Cheers,
Brook
===========================================================================
--- bin/pg_dump/pg_dump.c.orig Fri Jan 15 12:26:34 1999
+++ bin/pg_dump/pg_dump.c Fri Jan 15 13:02:34 1999
@@ -1630,6 +1630,13 @@ exit_nicely(g_conn); } tgfunc =
finfo[findx].proname;
+
+#if 0
+ /* XXX - how to emit this DROP TRIGGER? */
+ sprintf(query, "DROP TRIGGER %s;\n", fmtId(PQgetvalue(res2, i2, i_tgname), force_quotes));
+ fputs(query, fout);
+#endif
+ sprintf(query, "CREATE TRIGGER %s ", fmtId(PQgetvalue(res2, i2, i_tgname), force_quotes));
/* Trigger type */ findx = 0;
@@ -2026,6 +2033,9 @@ becomeUser(fout, tinfo[i].usename);
+ sprintf(q, "DROP TYPE %s;\n", fmtId(tinfo[i].typname, force_quotes));
+ fputs(q, fout);
+ sprintf(q, "CREATE TYPE %s " "( internallength = %s, externallength = %s, input
=%s, "
@@ -2122,6 +2132,8 @@ lanname = checkForQuote(PQgetvalue(res, i, i_lanname)); lancompiler =
checkForQuote(PQgetvalue(res,i, i_lancompiler));
+ fprintf(fout, "DROP PROCEDURAL LANGUAGE '%s';\n", lanname);
+ fprintf(fout, "CREATE %sPROCEDURAL LANGUAGE '%s' " "HANDLER %s LANCOMPILER '%s';\n",
(PQgetvalue(res,i, i_lanpltrusted)[0] == 't') ? "TRUSTED " : "",
@@ -2237,6 +2249,20 @@ PQclear(res); }
+ sprintf(q, "DROP FUNCTION %s (", fmtId(finfo[i].proname, force_quotes));
+ for (j = 0; j < finfo[i].nargs; j++)
+ {
+ char *typname;
+
+ typname = findTypeByOid(tinfo, numTypes, finfo[i].argtypes[j]);
+ sprintf(q, "%s%s%s",
+ q,
+ (j > 0) ? "," : "",
+ fmtId(typname, false));
+ }
+ sprintf (q, "%s);\n", q);
+ fputs(q, fout);
+ sprintf(q, "CREATE FUNCTION %s (", fmtId(finfo[i].proname, force_quotes)); for (j = 0; j < finfo[i].nargs; j++)
{
@@ -2347,6 +2373,11 @@ becomeUser(fout, oprinfo[i].usename);
+ sprintf(q, "DROP OPERATOR %s (%s, %s);\n", oprinfo[i].oprname,
+ fmtId(findTypeByOid(tinfo, numTypes, oprinfo[i].oprleft), false),
+ fmtId(findTypeByOid(tinfo, numTypes, oprinfo[i].oprright), false));
+ fputs(q, fout);
+ sprintf(q, "CREATE OPERATOR %s " "(PROCEDURE = %s %s %s %s %s %s %s %s %s);\n
",
@@ -2442,6 +2473,10 @@ becomeUser(fout, agginfo[i].usename);
+ sprintf(q, "DROP AGGREGATE %s %s;\n", agginfo[i].aggname,
+ fmtId(findTypeByOid(tinfo, numTypes, agginfo[i].aggbasetype), false));
+ fputs(q, fout);
+ sprintf(q, "CREATE AGGREGATE %s ( %s %s%s %s%s %s );\n", agginfo[i].aggname,
basetype,
@@ -2641,6 +2676,9 @@ becomeUser(fout, tblinfo[i].usename);
+ sprintf(q, "DROP TABLE %s;\n", fmtId(tblinfo[i].relname, force_quotes));
+ fputs(q, fout);
+ sprintf(q, "CREATE TABLE %s (\n\t", fmtId(tblinfo[i].relname, force_quotes)); actual_atts = 0;
for (j = 0; j < tblinfo[i].numatts; j++)
@@ -2857,6 +2895,10 @@ strcpy(id1, fmtId(indinfo[i].indexrelname, force_quotes)); strcpy(id2,
fmtId(indinfo[i].indrelname,force_quotes));
+
+ sprintf(q, "DROP INDEX %s;\n", id1);
+ fputs(q, fout);
+ fprintf(fout, "CREATE %s INDEX %s on %s using %s (", (strcmp(indinfo[i].indisunique, "t") ==
0)? "UNIQUE" : "", id1,
@@ -3116,6 +3158,9 @@ called = *t; PQclear(res);
+
+ sprintf(query, "DROP SEQUENCE %s;\n", fmtId(tbinfo.relname, force_quotes));
+ fputs(query, fout); sprintf(query, "CREATE SEQUENCE %s start %d increment %d maxvalue %d "
Brook Milligan <brook@trillium.NMSU.Edu> writes:
> pg_dump won't drop stuff before trying to create it; this makes
> dropping a single table (say) and recreating it difficult to automate
> since it is subject to error if all the stuff doesn't get dropped
> properly. The following patch causes pg_dump to emit DROP ...
> statements prior to emitting CREATE ... statements.
I think that ought to be driven by a switch to pg_dump ...
much of the time, I would *want* pg_dump's script to fail
if there's already an existing item of the given name.
I see your concern, I just don't think it's the only scenario.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- >>>>> "bm" == Brook Milligan <brook@trillium.NMSU.Edu> writes: bm> The following patch causes pg_dump to emit DROP ... statements bm> prior to emitting CREATE ... statements. I'm not sure I want pg_dump output to automatically drop tables. I'd certainly prefer having to ask for this behavior.... roland - -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD Custom Software Solutions roberts@panix.com 101 West 15th St #4NN rbroberts@acm.org New York, NY 10011 -----BEGIN PGP SIGNATURE----- Version: 2.6.2 Comment: Processed by Mailcrypt 3.4, an Emacs/PGP interface iQCVAwUBNp/5duoW38lmvDvNAQGJ4QP9EmXQWyUtgJi+5EkykZVU5IW9IMj/YcFa yGXF6eGP1SfLsW7rQPHgIljPvbT9B6WobYpGrluqA00XoW0/Wsm4TmjXuGQxRiI0 VxveN127wFsKrA0oJb8g9IfziyR2oeUba9oPNK3Yg2sYAXvd8/schYf6Ub2KUH5Q FS7ylJV1awY= =y2FK -----END PGP SIGNATURE-----
> Brook Milligan <brook@trillium.NMSU.Edu> writes: > > pg_dump won't drop stuff before trying to create it; this makes > > dropping a single table (say) and recreating it difficult to automate > > since it is subject to error if all the stuff doesn't get dropped > > properly. The following patch causes pg_dump to emit DROP ... > > statements prior to emitting CREATE ... statements. > > I think that ought to be driven by a switch to pg_dump ... > much of the time, I would *want* pg_dump's script to fail > if there's already an existing item of the given name. > > I see your concern, I just don't think it's the only scenario. I agree. I don't like it removing stuff before creating it. pg_dump already does a lot. But I do agree it is difficult to automate destroy/create. -- 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, Pennsylvania19026
Brook Milligan <brook@trillium.NMSU.Edu> writes: > pg_dump won't drop stuff before trying to create it; this makes >dropping a single table (say) and recreating it difficult to automate > since it is subject to error if all the stuff doesn'tget dropped > properly. The following patch causes pg_dump to emit DROP ... > statements prior to emitting CREATE... statements. I think that ought to be driven by a switch to pg_dump ... much of the time, I would *want* pg_dump's script to fail if there's already an existing item of the given name. A switch is fine. Is there any concensus as to what it should be, since the two that come to mind (-D and -d) are both used? Cheers, Brook
> Brook Milligan <brook@trillium.NMSU.Edu> writes: > > pg_dump won't drop stuff before trying to create it; this makes > > dropping a single table (say) and recreating it difficult to automate > > since it is subject to error if all the stuff doesn't get dropped > > properly. The following patch causes pg_dump to emit DROP ... > > statements prior to emitting CREATE ... statements. > > I think that ought to be driven by a switch to pg_dump ... > much of the time, I would *want* pg_dump's script to fail > if there's already an existing item of the given name. > > A switch is fine. Is there any concensus as to what it should be, > since the two that come to mind (-D and -d) are both used? -c for clean. -- 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, Pennsylvania19026