dropdb and dropuser: IF EXISTS

Started by Josh Kupershmidtover 14 years ago6 messages

schmiddy@gmail.com

over 14 years ago

1 attachment(s)

I noticed a few places where it would be handy if dropdb took a flag
like "--if-exists" which would basically just add in the 'IF EXISTS'
clause to the DROP DATABASE statement. For example, scripts like
find_static or mbregress.sh use dropdb && createdb, but they generate
noisy errors from dropdb when run for the first time since there's no
--if-exists flag. (They could just pipe 'DROP DATABASE IF EXISTS ...'
to psql, but what's the point of having dropdb if it's not used?)

Attached is a very quick patch implementing the "--if-exists" or "-X"
option for dropdb and dropuser. I didn't bother adding in a check to
make sure the server version was 8.2+ since we're not even supporting
8.1 nowadays, though that'd be easy enough to add in.

Josh

Attachments:

dropdb_user_ifexists.v1.patchtext/x-patch; charset=US-ASCII; name=dropdb_user_ifexists.v1.patchDownload

diff --git a/doc/src/sgml/ref/dropdb.sgml b/doc/src/sgml/ref/dropdb.sgml
index e20bcdb..2092bb6 100644
*** a/doc/src/sgml/ref/dropdb.sgml
--- b/doc/src/sgml/ref/dropdb.sgml
*************** PostgreSQL documentation
*** 87,92 ****
--- 87,102 ----
       </varlistentry>
  
       <varlistentry>
+       <term><option>-X</></term>
+       <term><option>--if-exists</></term>
+       <listitem>
+        <para>
+        Don't report an error if the specified database does not exist.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
+      <varlistentry>
         <term><option>-V</></term>
         <term><option>--version</></term>
         <listitem>
diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml
index c158103..22580a4 100644
*** a/doc/src/sgml/ref/dropuser.sgml
--- b/doc/src/sgml/ref/dropuser.sgml
*************** PostgreSQL documentation
*** 89,94 ****
--- 89,104 ----
       </varlistentry>
  
       <varlistentry>
+       <term><option>-X</></term>
+       <term><option>--if-exists</></term>
+       <listitem>
+        <para>
+         Don't report an error if the specified user does not exist.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
+      <varlistentry>
         <term><option>-V</></term>
         <term><option>--version</></term>
         <listitem>
diff --git a/src/bin/scripts/dropdb.c b/src/bin/scripts/dropdb.c
index 4cec63e..187bf6c 100644
*** a/src/bin/scripts/dropdb.c
--- b/src/bin/scripts/dropdb.c
*************** main(int argc, char *argv[])
*** 29,34 ****
--- 29,35 ----
  		{"password", no_argument, NULL, 'W'},
  		{"echo", no_argument, NULL, 'e'},
  		{"interactive", no_argument, NULL, 'i'},
+ 		{"if-exists", no_argument, NULL, 'X'},
  		{NULL, 0, NULL, 0}
  	};
  
*************** main(int argc, char *argv[])
*** 43,48 ****
--- 44,50 ----
  	enum trivalue prompt_password = TRI_DEFAULT;
  	bool		echo = false;
  	bool		interactive = false;
+ 	bool		if_exists = false;
  
  	PQExpBufferData sql;
  
*************** main(int argc, char *argv[])
*** 54,60 ****
  
  	handle_help_version_opts(argc, argv, "dropdb", help);
  
! 	while ((c = getopt_long(argc, argv, "h:p:U:wWei", long_options, &optindex)) != -1)
  	{
  		switch (c)
  		{
--- 56,62 ----
  
  	handle_help_version_opts(argc, argv, "dropdb", help);
  
! 	while ((c = getopt_long(argc, argv, "h:p:U:wWeiX", long_options, &optindex)) != -1)
  	{
  		switch (c)
  		{
*************** main(int argc, char *argv[])
*** 79,84 ****
--- 81,89 ----
  			case 'i':
  				interactive = true;
  				break;
+ 			case 'X':
+ 				if_exists = true;
+ 				break;
  			default:
  				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
  				exit(1);
*************** main(int argc, char *argv[])
*** 110,117 ****
  
  	initPQExpBuffer(&sql);
  
! 	appendPQExpBuffer(&sql, "DROP DATABASE %s;\n",
! 					  fmtId(dbname));
  
  	/*
  	 * Connect to the 'postgres' database by default, except have the
--- 115,122 ----
  
  	initPQExpBuffer(&sql);
  
! 	appendPQExpBuffer(&sql, "DROP DATABASE %s%s;\n",
! 					  (if_exists ? "IF EXISTS " : ""), fmtId(dbname));
  
  	/*
  	 * Connect to the 'postgres' database by default, except have the
*************** help(const char *progname)
*** 146,151 ****
--- 151,157 ----
  	printf(_("\nOptions:\n"));
  	printf(_("  -e, --echo                show the commands being sent to the server\n"));
  	printf(_("  -i, --interactive         prompt before deleting anything\n"));
+ 	printf(_("  -X, --if-exists           don't report error if database doesn't exist\n"));
  	printf(_("  --help                    show this help, then exit\n"));
  	printf(_("  --version                 output version information, then exit\n"));
  	printf(_("\nConnection options:\n"));
diff --git a/src/bin/scripts/dropuser.c b/src/bin/scripts/dropuser.c
index 0949a5e..bf5196f 100644
*** a/src/bin/scripts/dropuser.c
--- b/src/bin/scripts/dropuser.c
*************** main(int argc, char *argv[])
*** 29,34 ****
--- 29,35 ----
  		{"password", no_argument, NULL, 'W'},
  		{"echo", no_argument, NULL, 'e'},
  		{"interactive", no_argument, NULL, 'i'},
+ 		{"if-exists", no_argument, NULL, 'X'},
  		{NULL, 0, NULL, 0}
  	};
  
*************** main(int argc, char *argv[])
*** 43,48 ****
--- 44,50 ----
  	enum trivalue prompt_password = TRI_DEFAULT;
  	bool		echo = false;
  	bool		interactive = false;
+ 	bool		if_exists = false;
  
  	PQExpBufferData sql;
  
*************** main(int argc, char *argv[])
*** 54,60 ****
  
  	handle_help_version_opts(argc, argv, "dropuser", help);
  
! 	while ((c = getopt_long(argc, argv, "h:p:U:wWei", long_options, &optindex)) != -1)
  	{
  		switch (c)
  		{
--- 56,62 ----
  
  	handle_help_version_opts(argc, argv, "dropuser", help);
  
! 	while ((c = getopt_long(argc, argv, "h:p:U:wWeiX", long_options, &optindex)) != -1)
  	{
  		switch (c)
  		{
*************** main(int argc, char *argv[])
*** 79,84 ****
--- 81,89 ----
  			case 'i':
  				interactive = true;
  				break;
+ 			case 'X':
+ 				if_exists = true;
+ 				break;
  			default:
  				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
  				exit(1);
*************** main(int argc, char *argv[])
*** 110,116 ****
  	}
  
  	initPQExpBuffer(&sql);
! 	appendPQExpBuffer(&sql, "DROP ROLE %s;\n", fmtId(dropuser));
  
  	conn = connectDatabase("postgres", host, port, username, prompt_password, progname);
  
--- 115,122 ----
  	}
  
  	initPQExpBuffer(&sql);
! 	appendPQExpBuffer(&sql, "DROP ROLE %s%s;\n",
! 					  (if_exists ? "IF EXISTS " : ""), fmtId(dropuser));
  
  	conn = connectDatabase("postgres", host, port, username, prompt_password, progname);
  
*************** help(const char *progname)
*** 141,146 ****
--- 147,153 ----
  	printf(_("\nOptions:\n"));
  	printf(_("  -e, --echo                show the commands being sent to the server\n"));
  	printf(_("  -i, --interactive         prompt before deleting anything\n"));
+ 	printf(_("  -X, --if-exists           don't report error if user doesn't exist\n"));
  	printf(_("  --help                    show this help, then exit\n"));
  	printf(_("  --version                 output version information, then exit\n"));
  	printf(_("\nConnection options:\n"));

Robert Haas

robertmhaas@gmail.com

over 14 years ago

In reply to: Josh Kupershmidt (#1)

Re: dropdb and dropuser: IF EXISTS

On Fri, Aug 26, 2011 at 12:08 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote:

I noticed a few places where it would be handy if dropdb took a flag
like "--if-exists" which would basically just add in the 'IF EXISTS'
clause to the DROP DATABASE statement. For example, scripts like
find_static or mbregress.sh use dropdb && createdb, but they generate
noisy errors from dropdb when run for the first time since there's no
--if-exists flag. (They could just pipe 'DROP DATABASE IF EXISTS ...'
to psql, but what's the point of having dropdb if it's not used?)

Attached is a very quick patch implementing the "--if-exists" or "-X"
option for dropdb and dropuser. I didn't bother adding in a check to
make sure the server version was 8.2+ since we're not even supporting
8.1 nowadays, though that'd be easy enough to add in.

+1 for --if-exists, but -X isn't doing a lot for me, especially since
we've used -X for other purposes in other commands. I'd just skip
having a short form for this one.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Josh Kupershmidt

schmiddy@gmail.com

over 14 years ago

In reply to: Robert Haas (#2)

1 attachment(s)

Re: dropdb and dropuser: IF EXISTS

On Fri, Aug 26, 2011 at 10:42 PM, Robert Haas <robertmhaas@gmail.com> wrote:

+1 for --if-exists, but -X isn't doing a lot for me, especially since
we've used -X for other purposes in other commands. I'd just skip
having a short form for this one.

Fine by me. Updated patch attached.

Josh

Attachments:

dropdb_user_ifexists.v2a.patchtext/x-patch; charset=US-ASCII; name=dropdb_user_ifexists.v2a.patchDownload

diff --git a/doc/src/sgml/ref/dropdb.sgml b/doc/src/sgml/ref/dropdb.sgml
index e20bcdb..509b41e 100644
--- a/doc/src/sgml/ref/dropdb.sgml
+++ b/doc/src/sgml/ref/dropdb.sgml
@@ -87,6 +87,15 @@ PostgreSQL documentation
      </varlistentry>
 
      <varlistentry>
+      <term><option>--if-exists</></term>
+      <listitem>
+       <para>
+       Don't report an error if the specified database does not exist.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
        <term><option>-V</></term>
        <term><option>--version</></term>
        <listitem>
diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml
index c158103..0fb917d 100644
--- a/doc/src/sgml/ref/dropuser.sgml
+++ b/doc/src/sgml/ref/dropuser.sgml
@@ -89,6 +89,15 @@ PostgreSQL documentation
      </varlistentry>
 
      <varlistentry>
+      <term><option>--if-exists</></term>
+      <listitem>
+       <para>
+        Don't report an error if the specified user does not exist.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
        <term><option>-V</></term>
        <term><option>--version</></term>
        <listitem>
diff --git a/src/bin/scripts/dropdb.c b/src/bin/scripts/dropdb.c
index 4cec63e..b4b10b8 100644
--- a/src/bin/scripts/dropdb.c
+++ b/src/bin/scripts/dropdb.c
@@ -29,6 +29,7 @@ main(int argc, char *argv[])
 		{"password", no_argument, NULL, 'W'},
 		{"echo", no_argument, NULL, 'e'},
 		{"interactive", no_argument, NULL, 'i'},
+		{"if-exists", no_argument, NULL, 'X'},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -43,6 +44,7 @@ main(int argc, char *argv[])
 	enum trivalue prompt_password = TRI_DEFAULT;
 	bool		echo = false;
 	bool		interactive = false;
+	bool		if_exists = false;
 
 	PQExpBufferData sql;
 
@@ -79,6 +81,9 @@ main(int argc, char *argv[])
 			case 'i':
 				interactive = true;
 				break;
+			case 'X':
+				if_exists = true;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -110,8 +115,8 @@ main(int argc, char *argv[])
 
 	initPQExpBuffer(&sql);
 
-	appendPQExpBuffer(&sql, "DROP DATABASE %s;\n",
-					  fmtId(dbname));
+	appendPQExpBuffer(&sql, "DROP DATABASE %s%s;\n",
+					  (if_exists ? "IF EXISTS " : ""), fmtId(dbname));
 
 	/*
 	 * Connect to the 'postgres' database by default, except have the
@@ -146,6 +151,7 @@ help(const char *progname)
 	printf(_("\nOptions:\n"));
 	printf(_("  -e, --echo                show the commands being sent to the server\n"));
 	printf(_("  -i, --interactive         prompt before deleting anything\n"));
+	printf(_("  --if-exists               don't report error if database doesn't exist\n"));
 	printf(_("  --help                    show this help, then exit\n"));
 	printf(_("  --version                 output version information, then exit\n"));
 	printf(_("\nConnection options:\n"));
diff --git a/src/bin/scripts/dropuser.c b/src/bin/scripts/dropuser.c
index 0949a5e..13abb54 100644
--- a/src/bin/scripts/dropuser.c
+++ b/src/bin/scripts/dropuser.c
@@ -29,6 +29,7 @@ main(int argc, char *argv[])
 		{"password", no_argument, NULL, 'W'},
 		{"echo", no_argument, NULL, 'e'},
 		{"interactive", no_argument, NULL, 'i'},
+		{"if-exists", no_argument, NULL, 'X'},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -43,6 +44,7 @@ main(int argc, char *argv[])
 	enum trivalue prompt_password = TRI_DEFAULT;
 	bool		echo = false;
 	bool		interactive = false;
+	bool		if_exists = false;
 
 	PQExpBufferData sql;
 
@@ -79,6 +81,9 @@ main(int argc, char *argv[])
 			case 'i':
 				interactive = true;
 				break;
+			case 'X':
+				if_exists = true;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -110,7 +115,8 @@ main(int argc, char *argv[])
 	}
 
 	initPQExpBuffer(&sql);
-	appendPQExpBuffer(&sql, "DROP ROLE %s;\n", fmtId(dropuser));
+	appendPQExpBuffer(&sql, "DROP ROLE %s%s;\n",
+					  (if_exists ? "IF EXISTS " : ""), fmtId(dropuser));
 
 	conn = connectDatabase("postgres", host, port, username, prompt_password, progname);
 
@@ -141,6 +147,7 @@ help(const char *progname)
 	printf(_("\nOptions:\n"));
 	printf(_("  -e, --echo                show the commands being sent to the server\n"));
 	printf(_("  -i, --interactive         prompt before deleting anything\n"));
+	printf(_("  --if-exists               don't report error if user doesn't exist\n"));
 	printf(_("  --help                    show this help, then exit\n"));
 	printf(_("  --version                 output version information, then exit\n"));
 	printf(_("\nConnection options:\n"));

Robert Haas

robertmhaas@gmail.com

over 14 years ago

In reply to: Josh Kupershmidt (#3)

Re: dropdb and dropuser: IF EXISTS

On Mon, Aug 29, 2011 at 7:34 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:

On Fri, Aug 26, 2011 at 10:42 PM, Robert Haas <robertmhaas@gmail.com> wrote:

+1 for --if-exists, but -X isn't doing a lot for me, especially since
we've used -X for other purposes in other commands. I'd just skip
having a short form for this one.

Fine by me. Updated patch attached.

Committed with some edits. I stole the documentation language from
the DROP DATABASE and DROP USER pages and just copied it over, instead
of saying the same thing in different words. And I rearranged the
options handling to be more consistent with what we do elsewhere.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Josh Kupershmidt

schmiddy@gmail.com

over 14 years ago

In reply to: Robert Haas (#4)

Re: dropdb and dropuser: IF EXISTS

On Tue, Aug 30, 2011 at 11:14 AM, Robert Haas <robertmhaas@gmail.com> wrote:

Committed with some edits. I stole the documentation language from
the DROP DATABASE and DROP USER pages and just copied it over, instead
of saying the same thing in different words. And I rearranged the
options handling to be more consistent with what we do elsewhere.

Thanks. I think you accidentally copied the DROP DATABASE snippet into
dropuser.sgml as well.

Josh

Robert Haas

robertmhaas@gmail.com

over 14 years ago

In reply to: Josh Kupershmidt (#5)

Re: dropdb and dropuser: IF EXISTS

On Tue, Aug 30, 2011 at 12:44 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:

On Tue, Aug 30, 2011 at 11:14 AM, Robert Haas <robertmhaas@gmail.com> wrote:

Committed with some edits. I stole the documentation language from
the DROP DATABASE and DROP USER pages and just copied it over, instead
of saying the same thing in different words. And I rearranged the
options handling to be more consistent with what we do elsewhere.

Thanks. I think you accidentally copied the DROP DATABASE snippet into
dropuser.sgml as well.

D'oh. Fixed.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company