this is an old Bug in the Xojo PostgreSQLDatabase Plugin. You cannot schema-specify the table name when using RecordSet.Edit / .Update or now RowSet.EditRow / .SaveRow - see <https://xojo.com/issue/11597>
Workaround is to set your searchpath in a way you don’t need to specify a schema. This does not work for cross-schema queries with ambiguous table names - but such queries would likely not be updatable using the RecordSet.Edit interface anyhow…
A word of caution: in PostgreSQL, Xojo orphans prepared statements even with the new API. The only way to clear them out is to disconnect and reconnect.
Interestingly, this SQLITE <https://xojo.com/issue/58739> is also presenting leaks never seem before, is the entire Xojo DB framework presenting malfunctions after the API2.0 modifications?
The postgreSQL one predates API 2 by a lot and relates specifically to prepard statements we use programmatically
The SQLITE one seems to be the implicit prepared statements that the API 2 version of the DB API uses
If you use the API 1.0 version the leak goes away
I looked at the bug report 41633 and was a bit surprised you expected the server to deallocate the prepared statement just because your PostgreSQLPreparedStatement instance is gone. I would not expect that, they are supposed to last for as long as your session is alive. Have you tried calling “Deallocate” via SQLExecute? That should work (haven’t tried though). I doubt you’d really have to disconnect to dispose of them.
I don’t know why you’re surprised, I’d expect the Destructor to do that. To use DEALLOCATE directly, I’d need the reference to the prepared statement, and Xojo doesn’t give us that. In API 2, it doesn’t even give us an instance.
Ah, hmm, well, given that we can’t get access to the name the statement was created as (they get created as “statement1”,statement2" etc. by the plugin btw.) I guess it sort of makes little sense to keep them around on the server once the prepared statements instance is gone, so I see where you are coming from. But I think the plugin should just tell us the name, and not just deallocate the statement on the server once my local instance is gone. Anyway, I usually keep instances of my prepared statements around in a dictionary (using the sql as key).
[quote=470300:@Tobias Bussmann]this is an old Bug in the Xojo PostgreSQLDatabase Plugin. You cannot schema-specify the table name when using RecordSet.Edit / .Update or now RowSet.EditRow / .SaveRow - see <https://xojo.com/issue/11597>
Workaround is to set your searchpath in a way you don’t need to specify a schema. This does not work for cross-schema queries with ambiguous table names - but such queries would likely not be updatable using the RecordSet.Edit interface anyhow…[/quote]
Thanks al lot for your answers. I never would have guessed it was a software bug of the PG Plugin
I moved all tables to the public schema and it works fine.
The name is not the problem, you can figure it out with the help of pg_prepared_statements but the issue is what to do with it then. AFAIK you have no way to bind parameters for a second execution through the Xojo interface. All you can do is pass them inline which defeats the one of the purposes of PreparedStatements or deallocate it.
Some code to play around with:
Dim db As New PostgreSQLDatabase
db.Connect
Dim rs As rowset
Dim sql As String = "SELECT $1 * 2;"
rs = db.SelectSQL(sql, 1)
System.DebugLog "2*1=" + rs.ColumnAt(0).StringValue
Dim stmt_name As String
rs = db.SelectSQL("SELECT * FROM pg_prepared_statements WHERE statement = $1;", sql)
stmt_name = rs.Column("name").StringValue
rs = db.SelectSQL("EXECUTE """ + stmt_name + """( 2 );")
System.DebugLog "2*2=" + rs.ColumnAt(0).StringValue
db.ExecuteSQL("DEALLOCATE """ + stmt_name + """;")
