Hello,
PGsql v7.3.2
I'm trying to:
1) determine if a database exists;
2) delete if it does; and
3) create a new database of the same name.
The code performs a PQsetdbLogin(... dbname='somedb' ...); (I would prefer
to use PQconnectdb(), since PQsetdbLogin appears to be deprecated; but there
is no libpq accessor function to obtain addr from a PGconn.)
If the login status returns OK, then I know that the database exists. So,
first the PGconn needs to be closed, via a call to PQfinish(). Next, I use a
different connection (one opened against the "template1" database) to
execute a "DROP DATABASE somedb".
This succeeds if, and only if, I am running gdb (ddd front-end) in single
step mode. If just executing without any time lag between calls (i.e. no
human interaction), then I consistently and repeatably receive the error
"Drop database failed : ERROR: DROP DATABASE: database \"somedb\" is being
accessed by other users\n".
The "other user" is the PGconn that was just released using a PGfinish!
The code is not using any asynchronous calls, so I would expect that when
PQfinish() has been called, any lock that my application has on the database
would be released. How is an application to know when a resource that it is
no longer using will be freed for it to use again? It appears quite
problematic if this cannot be reliably determined.
-----------------------------
As a someone related issue, it's not clear from the documentation exactly
HOW to use the libpq functions for some basic cases.
I consider it a reasonable expectation to connect to the PGsql server
without opening any database, and being able to perform some basic
operations that do not rely on having an open database (CREATE USER, ALTER
USER, DROP USER, CREATE DATABASE, DROP DATABASE, etc). Unfortunately, if
<dbname=> is not provided, PQconnectdb seems to utilize the supplied <user=>
as the database to attempt to open (at least, that's what the error
reports). Typically, the user name will not be the name of a database in the
system, and of course the attempt will fail.
The only way to access a freshly created cluster to create some tables seems
to be to perform a PQconnectdb with dbname='template1'. Is this guaranteed
to always exist? And is it really the design intent to force the application
to connect to template1 in order to create a database? Both seem like bugs
to me; or at the very least, undesirable (and apparently undocumented)
"features".
Thanks,
Dietmar May
