pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feeddrop before create in pg_dump
6+ messages / 4 participants
[nested] [flat]
* drop before create in pg_dump
@ 1999-01-15 21:08 Brook Milligan <brook@trillium.NMSU.Edu>
0 siblings, 2 replies; 6+ messages in thread
From: Brook Milligan @ 1999-01-15 21:08 UTC (permalink / raw)
To: hackers@postgresql.org; patches@postgresql.org
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
===========================================================================
inline patch 1: bin/pg_dump/pg_dump.c (+45/-0 lines) download inline-1.patch
--- 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
@ 1999-01-15 23:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brook Milligan <brook@trillium.NMSU.Edu>
1 sibling, 2 replies; 6+ messages in thread
From: Tom Lane @ 1999-01-15 23:44 UTC (permalink / raw)
To: Brook Milligan <brook@trillium.NMSU.Edu>; +Cc: hackers@postgreSQL.org; patches@postgreSQL.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [HACKERS] drop before create in pg_dump
@ 1999-01-16 02:29 Roland Roberts <roberts@panix.com>
parent: Brook Milligan <brook@trillium.NMSU.Edu>
1 sibling, 0 replies; 6+ messages in thread
From: Roland Roberts @ 1999-01-16 02:29 UTC (permalink / raw)
To: hackers@postgreSQL.org; patches@postgreSQL.org
-----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-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [HACKERS] drop before create in pg_dump
@ 1999-01-16 03:50 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 1999-01-16 03:50 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; +Cc: brook@trillium.NMSU.Edu; hackers@postgreSQL.org; patches@postgreSQL.org
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [HACKERS] drop before create in pg_dump
@ 1999-01-17 22:11 Brook Milligan <brook@trillium.NMSU.Edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Brook Milligan @ 1999-01-17 22:11 UTC (permalink / raw)
To: hackers@postgreSQL.org
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?
Cheers,
Brook
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [HACKERS] drop before create in pg_dump
@ 1999-01-18 04:51 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Brook Milligan <brook@trillium.NMSU.Edu>
0 siblings, 0 replies; 6+ messages in thread
From: Bruce Momjian @ 1999-01-18 04:51 UTC (permalink / raw)
To: brook@trillium.NMSU.Edu; +Cc: hackers@postgreSQL.org
> 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, Pennsylvania 19026
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-01-18 04:51 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-15 21:08 drop before create in pg_dump Brook Milligan <brook@trillium.NMSU.Edu>
1999-01-15 23:44 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-01-16 03:50 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-01-17 22:11 ` Brook Milligan <brook@trillium.NMSU.Edu>
1999-01-18 04:51 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-01-16 02:29 ` Roland Roberts <roberts@panix.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox