Problems with db.transaction() failing

[bchiavarini]

Hi, I' m having some problems with a db.transaction.\ I am using a web service based on dballe 8.10 Python API to inspect a dballe database. When i start the service, the db.transaction to build a dballe.Explorer works well, but after a while (about ten minutes before the service has been started) the transaction fails because of this error:

ERROR executing prepare:SELECT id FROM station WHERE rep=$1::int4 AND lat=$2::int4 AND lon=$3::int4 AND ident IS NULL: ERROR:  prepared statement "v7_station_select_fixed" already exists

and, after that, all the following ones fails because of this error:

ERROR executing BEGIN ISOLATION LEVEL REPEATABLE READ READ WRITE: ERROR:  current transaction is aborted, commands ignored until end of transaction block

So the issue is double:

• what's the meaning of the first error? what can i do to fix it or prevent it?

• Is there a way to close a transaction if it has raised an error? the only way i have found is the rollback() method but it can be called only on a db.Transaction object that, because of the previous failure, has not been created so it does not exist.

Thanks for your help

[pat1]

@bchiavarini we need some information to replicate the problem

[spanezz]

@bchiavarini could you give examples of the code of the web services, to see how it connects to the database and opens/tracks transactions?

[spanezz]

The first error happens when some transaction initialization code is called twice on the same database connection, but the code has guards explicitly to prevent this from happening, and I cannot at the moment reproduce this, so more information would be needed. Are you in a multi-threaded environment? Are you opening a dballe.DB(), then forking, and using the resulting db from multiple different processes, that would then end up sharing the same postgresql connection?

[spanezz]

I managed to reproduce a case of the second error, I'll now work on a fix

[bchiavarini]

The first error happens when some transaction initialization code is called twice on the same database connection, but the code has guards explicitly to prevent this from happening, and I cannot at the moment reproduce this, so more information would be needed. Are you in a multi-threaded environment? Are you opening a dballe.DB(), then forking, and using the resulting db from multiple different processes, that would then end up sharing the same postgresql connection?

yes, the environment is a multi-threaded one, so what are you supposing maybe is what is actually happening. I'll try to open the postgresql connection in an other way to see if it fixes the issue.

[spanezz]

Ok, to be sure: mulththreading or multiprocessing?

If you could condense the problem you're seeing to a small test case I could try, I'd be interested in investigating.

[bchiavarini]

multithreading. Create a small test case is not so quick due to the infrastructure we have. As further information, as web server we are using Flask with UWSGI.\ We are opening the Dballe db connection in this way in a global variable\ DB = dballe.DB.connect("{engine}://{user}:{pw}@{host}:{port}/DBALLE") \ that is used to open a transaction with this method:

    def build_explorer():
        explorer = dballe.Explorer()
        with explorer.rebuild() as update:
            with DB.transaction() as tr:
                update.add_db(tr)
        return explorer

but now we are going to change the way we connect to dballe

[spanezz]

Is the global variable created at module level perhaps?

Also, would the code be somewhere where I can see it?

[bchiavarini]

yes, the variable is created at module level. The code is on our gitlab here

[spanezz]

I guess as a first try you could instantiate the dballe.DB object only when needed, like you do with Explorer, so hopefully the connection is made in the worker process instead of at the time wsgi loads the sources

[spanezz]

I did some research and sqlite, postgresql, and mysql, all don't support reusing a database connection after a fork(). I added code to detect the case and issue a clear error in case it happens.

I also fixed the error you're getting when starting a transaction fails.

It looks like this is the best that one can do, and with multiprocessing, the only thing that works is to open the dballe.DB connection in the child processes.

[brancomat]

Fixes have been released in v8.11 (https://github.com/ARPA-SIMC/dballe/releases/tag/v8.11-1)