debugging the reset_db for postgres backends

[GoogleCodeExporter]

What steps will reproduce the problem?

1. Make a django project with a postgres db, postgresql_psycopg2 as your db
engine in settings.py

2. run ./manage.py syncdb

3. run ./manage.py reset_db

What is the expected output? What do you see instead?

Expected output is successful resetting of the db, actual output is an
error stating that the value of settings.DATABASE_USER is not a valid
DATABASE name, indicating an error in the connection method. 

Here is a more in depth look at this issue and other related issues:

I've been trying to get the reset_db command extension to work with a
postgres database.  I'm using postgres 8.3 and django revision 9177 on
Ubuntu Hardy.  I've found a couple of issues and written a patch (see
below), but I've been stumped by the following issue, which is:

How can you drop a database programatically using psycopg2?

The current ``reset_db.py`` command attempts to execute "DROP DATABASE
my_precious_database" with the open connection cursor.  The first problem I
notice here is that there is no terminating semicolon on the command
string.  I added the semicolon and ran this manually in the interactive
interpreter, and ran into the second problem, a ProgrammingError:

    ProgrammingError: DROP DATABASE cannot run inside a transaction block

Any ideas how to drop this database on the given connection?  Or is this
beyond the scope of the ``psycopg2`` library?  Is this happening because I
specifically connected to the database I'm trying to drop?  If so, how do
you make a connection with ``psycopg2`` independent of a specific database?
 When I tried a creating a ``psycopg2`` connection with only a
``user=myusername`` and ``password=mypassword``, the connection failed,
saying that there was no database named "myusername".

A patch to get ``reset_db.py`` to connect to a postgres db in the first
place is attached.

This should get you to the same place that I got to: how to drop a database
programatically within a psycopg2 connection.

Any help would be greatly appreciated!

What version of the product are you using? On what operating system?
Ubuntu Hardy Heron, Django 9177, d-c-e rev. 132

Please provide any additional information below.

Original issue reported on code.google.com by wiley.kestner on 4 Nov 2008 at 3:31

Attachments:

• reset_db_postgres.diff

[GoogleCodeExporter]

I had some script to programatically create and destroy in postgresql a long 
time
ago, it connected to the 'template1' database cause you cannot have the same 
database
open that you want to destroy. Then issued the "DROP DATABASE some_db;" sql 
command.
This wasn't with Django btw.

So try it with dbname='template1' instead of settings.DATABASE_NAME and make 
sure
that the user your connecting with has the rights to remove the database. (This 
is
something i'll never do, the connecting database user for Django should not have
superuser-type rights on the database IMO it's a matter of security)

Some additional information:

http://www.postgresql.org/docs/8.3/interactive/sql-dropdatabase.html

Which states: "DROP DATABASE drops a database. It removes the catalog entries 
for the
database and deletes the directory containing the data. It can only be executed 
by
the database owner. Also, it cannot be executed while you or anyone else are
connected to the target database. (Connect to postgres or any other database to 
issue
this command"

Notes

DROP DATABASE cannot be executed inside a transaction block. 

Original comment by v.oostv...@gmail.com on 4 Nov 2008 at 9:52

[GoogleCodeExporter]

fixed in
http://github.com/django-extensions/django-extensions/commit/5b68593daa5a08bd46d
1093545ece7534a5bd755

thanks for the bug report!

Original comment by v.oostv...@gmail.com on 11 May 2009 at 8:41

• Changed state: Done

[GoogleCodeExporter]

You need this patch
[http://django-command-extensions.googlecode.com/issues/attachment?aid=569982265
9788255001&name=path_issues_100.diff
path_issues_100.diff] for revision 196.

Original comment by rodrigopmatias on 23 Jun 2009 at 4:59

[GoogleCodeExporter]

Please supply this patch in English instead of Brazilian.
Also please change the naming of _postgresql_destroy to something more sensible.
thanks!

Original comment by v.oostv...@gmail.com on 16 Jul 2009 at 10:11

• Changed state: Started
• Added labels: Needs-better-patch