DROP database fails after migrating to 2.9.1

[JulienHoullier]

Hello,

I upgrade from psycopg2 2.8.6 to 2.9.1 and i got a regression in my script which try to DROP a database.

I get back the error about DROP cannot be run into a transaction, which i had fixed by applying
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)

But it seems that it has no effect anymore, even if the doc still says it should.

I prefer using a discussion before creating an issue to ensure it is not my usage which is wrong.

Thanks in advance,
Julien

[dvarrazzo]

Don't use with psycopg.connect()

[JulienHoullier]

So you confirm the code example from this stackoverflow :
https://stackoverflow.com/questions/34484066/create-a-postgres-database-using-python
Does not work anymore in the 2.9.1 ?

I found some issues which looks alike (#1214) but if in 2.9.1 we must not use connect() even the code from this issue won't work, am i correct ?

Can we consider this as a regression, since the version is not a 3.0.0, it should not break existing code. Is there an issue already existing for this ?

[dvarrazzo]

So you confirm the code example from this stackoverflow :
https://stackoverflow.com/questions/34484066/create-a-postgres-database-using-python
Does not work anymore in the 2.9.1 ?

No: that code works.

I found some issues which looks alike (#1214) but if in 2.9.1 we must not use connect() even the code from this issue won't work, am i correct ?

I don't think you are, not. Also, there is no other way to connect than to use conn = connect(). What you don't want to use is with connect() as conn.

Can we consider this as a regression, since the version is not a 3.0.0, it should not break existing code. Is there an issue already existing for this ?

No. You are either using with connection, which has been documented to start a transaction since it was introduced, and by mistake it wasn't doing it on autocommit connections, which was a bug that has been fixed in 2.9, or, show us the code that doesn't work.

[JulienHoullier]

OK thanks, i misunderstood the with in your first answer.

For info, the code which was working in the 2.8.6 use with :

        try:
            # connection to default database with default user in order to create the different databases
            # use with to close automatically
            with psycopg2.connect(database=postgres_database,
                                  user=postgres_user,
                                  host=host,
                                  port=port,
                                  password=postgres_password) as conn:

                # database drop/create must be in autocommit so no transaction needed
                conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
                with conn.cursor() as cursor:
                    if drop:
                        for licenser_db in licenser_db_names:
                            logging.debug("delete db {} if exists".format(licenser_db))
                            cursor.execute("DROP DATABASE IF EXISTS {}".format(licenser_db))

I refactor without with to ensure compatibility with 2.9.1

Thks again