Thread: drop before create in pg_dump

drop before create in pg_dump

From
Brook Milligan
Date:
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 "


Re: [HACKERS] drop before create in pg_dump

From
Tom Lane
Date:
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


Re: [HACKERS] drop before create in pg_dump

From
Roland Roberts
Date:
-----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-----


Re: [HACKERS] drop before create in pg_dump

From
Bruce Momjian
Date:
> 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
 


Re: [HACKERS] drop before create in pg_dump

From
Brook Milligan
Date:
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


Re: [HACKERS] drop before create in pg_dump

From
Bruce Momjian
Date:
>    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