Postgres prepared statements are not deallocated

Do I understand it correctly, that the client uses only prepared statements?

No, there are statements that are simple, quick and no need of preparing. But, why does that matter?

Also, creating shared properties, caching the prepared statements… That’s a HUGE amount of work because they have to be cached per user/per database connection. In some situations, on long standing report generation for example, a new database connection is even made for a given user as to not tie up the database connection they are currently using for normal work. So, for 10 minutes, stuff might churn in the background, in another thread, all the while that exact same user is performing other tasks in their “main” connection.

And then you are doing that work, taking up resources in the middleware server just because the connector does not DEALLOCATE the statement. Many messages may only be called once or twice a day. Why keep the statement around on the server AND the client (middleware server)?

Has it been reported yet? It wouldn’t surprise me if all of the Database plugins have the same problem.

No, Kem is creating a report.

Well, when no prepared statements are created on the client side, there are none created on the database server. But if you mean, that you create them in the middleware, then you have access to the database instance.

We are speaking about multiple terms here. n-tier (1=database, 2=middleware, 3=client). In this case, if you are talking about the database client, then that is 2 (middleware). and middleware maintains multiple connections to the database, each for each user and some times multiple connections per user. So, yes, middleware of course has a database connection, and that instance. The user client never has access to the database.

A prepared statements needs only to be sent once per database connection (directly after the successful connect) for all querying and all users until the connection is closed. On closing the database connection these prepared statements on the server side are destroyed.

Yes, I am aware of this. How do you propose tracking which prepared statements have been prepared already or not?

Never mind, you are suggesting something that is not practical or beneficial in our case. Again, we have hundreds of prepared statements, many of which are dynamically generated. Also, many of which will never be executed by a user. So, preparing each statement at database connect is time consuming and wastes resources.

For example, User A is a billing admin. They will run billing reports all day long. User B is a clerk. They could care less about billing reports. User C is a doctor. They could care less about client setups. There are many such roles, many users hold multiple roles but in a given day may have nothing to do with one role or the other.

The preparing of a single statement is very little time and we will prepare statements that are about to be executed many times, then execute it many times. It does not matter that at the end of a method, that prepared statement is then forgotten about. It has served it’s purpose. It may never be run again for that user.

See

<https://xojo.com/issue/41633>

There is an example project attached. Bob, perhaps you can modify that to test other database types?

Lets agree that we disagree what the real purpose of prepared statements is.

I’m off now, I have a doctor’s appointment (no, it’s not about my brain…).

[quote=231124:@Eli Ott]Lets agree that we disagree what the real purpose of prepared statements is.

I’m off now, I have a doctor’s appointment (no, it’s not about my brain…).[/quote]

I believe we have the same view of a prepared statement. I do not think you have a good view of our application structure though, that’s the communication disconnect. The bottom line is that we have hundreds, if not thousands, of prepared statements in our middleware server. The vast majority of which a given user will NEVER use. So preparing all of those statements on database connection is not a good idea.

The preparing of those statements are done when we need them, and when we use them in a tight loop. We may prepare a statement for a user once or twice a day, but in the tight loop execute it many times. To us, the resources the prepared statement consumes while not being used, many never used again for the entire connection is more important to us than the few milliseconds it takes to prepare the statement before we use it hundreds of times, then destroy it.

hmmm…

I’ve got a feeling that the mysql version is doing the same thing. its possible its my code though :wink: i only have one mysql web app and it definitely ends up with thousands of sleeping processes.

due to my lack of knowledge on how to debug problems in mySQL i had been ignoring it and hoping it wouldn’t become a big problem.

[quote=231152:@Russ Lunn]hmmm…

I’ve got a feeling that the mysql version is doing the same thing. its possible its my code though :wink: i only have one mysql web app and it definitely ends up with thousands of sleeping processes.

due to my lack of knowledge on how to debug problems in mySQL i had been ignoring it and hoping it wouldn’t become a big problem.[/quote]

I believe this is a different problem. Having a prepared statement not deallocate should not cause additional sleeping processes. Maybe you are connecting multiple times to the database and keeping the database handles around?

@Bob Keeney , never mind. I just confirmed that the same problem does not exist with MySQL. I’ll attach that project to the Feedback report.

have you checked MS SQL, Oracle or SQLite? (mainly the last one)

I can confirm the MySQL issue I had was nothing to do with this issue, just my half-arsed coding :wink:

we all have done that from time to time.

Scott,

I don’t have access to the first two, and can’t find a way to check the open prepared statements in SQLite.

The difference here may be that there is no C function to close the prepared statement in Postgres. Instead, you are expected to call “DEALLOCATE ‘name’”, so perhaps there is some technical reason the engineers avoided that. We’ll see what the response is to the Feedback, I guess.