PostgreSQL-Error "Relation xxx does not exist"

Databases
21

The framework should do it, so no need. Once a PostgreSQLPreparedStatement is released and destroyed, the framework destructor should take care of it. If you want to retain it around, assign it to something in a wider scope, like a global property or anything, nullify it later to release it.

22

As stated earlier: If the prepared statement is intended to be used only once, there is no point in creating it at all. The Xojo framework should simply call PQexecParams in the parameterised SelectSQL / ExecuteSQL methods. Explicitly created PostgreSQLPreparedStatements could be automatically deallocated in the destructor. But DEALLOCATE does only exist as an SQL command, there is no equivalent in libpq like PQprepare for PREPARE. So we are dependent on transaction state of the connection at the time the object is destructed. Even if prepared statements are session wide, a DEALLOCATE would fail in a aborted transaction:

[code]bussmann=# PREPARE test(integer) AS SELECT 2 + $1;
PREPARE
bussmann=# EXECUTE test(1);
?column?

    3

(1 row)

bussmann=# BEGIN;
BEGIN
bussmann=# EXECUTE test(2);
?column?

    4

(1 row)

bussmann=# SELECT invalid;
ERROR: column “invalid” does not exist
LINE 1: SELECT invalid;
^
bussmann=# EXECUTE test(2);
ERROR: current transaction is aborted, commands ignored until end of transaction block
bussmann=# DEALLOCATE test;
ERROR: current transaction is aborted, commands ignored until end of transaction block
bussmann=# ROLLBACK;
ROLLBACK
bussmann=# DEALLOCATE test;
DEALLOCATE
[/code]

23

Again, as I said, if you intend to use it n times, is your job holding it around.
Your in-server example is not applicable to a Xojo application case.
The usual case will be an out of scope firing the deallocation AFTER a commit or rollback.

24

“Prepared statements for use with PQexecPrepared can also be created by executing SQL PREPARE statements. Also, although there is no libpq function for deleting a prepared statement, the SQL DEALLOCATE statement can be used for that purpose.”

https://www.postgresql.org/docs/9.1/libpq-exec.html

25

I totally agree.

[quote=470781:@Rick Araujo]Your in-server example is not applicable to a Xojo application case.
The usual case will be an out of scope firing the deallocation AFTER a commit or rollback.[/quote]
I don’t see how this would not be applicable. The point I’m trying to make is that an potential automatic DEALLOCATE on destruction of PostgreSQLPreparedStatements could happen in unlucky situations and can fail, the framework would then have to deal with it somehow.

sure. Obviously I don’t understand your point here.

To sum it up:

  • Developers should cache PostgreSQLPreparedStatement for reuse
  • Xojo could DEALLOCATE them in destructor
  • Xojo should not create server side prepared statements for implicit use. The minimal fix would be to continue to use PQprepare and PQexecPrepared but pass an empty stmtName in the former (thus constantly overwriting an anonymous ps) or better switch to PQexecParams
26

The only possibility I see, that a DEALLOCATE could fail at destruction time, is that the user made some unusual weird intervention bypassing the system and deallocated it manually ahead of time. The solution is simple, when the PostgreSQLPreparedStatement is destroyed, internally ignore the error reported by your “ExecDeallocation(PrepStatementName)” not finding it, and gracefully continue.

27

There are downsides using PQExecParams against PQexecPrepared. Xojo engineers must to analyze the case, PQexecPrepared can accept multiple statements separated by semi-colons, and PQexecParams is a simplified version designed for one statement only to avoid sql injection. But I’ll let such questions open for the engineers to decide.