PostgreSQL: prepared statement "sqlx_s_2" already exists

[marco-ni]

Hi, I'm connecting to a remote PostgreSQL database using the following sqlpage.json file. Actual parameters are omitted but it connects normally:

{
    "database_url":	"postgres://<user>:<password>:<port>/<dbname>",
	"listen_on": "127.0.0.1:9001"
}

This is the index.json file:

SELECT 'hero' AS component,
    'Testing SQLPage' AS title,
    'connected to PostgreSQL' AS description_md;

SELECT 'list' AS component,
    'Users' AS title;
select a.name as title from assets a;

When launching sqlpage.exe from a command window it reports some warnings that seem to be related to the default "it works" page:

[2023-11-16T13:27:35Z INFO  sqlpage::webserver::database::connect] Connecting to database: postgres://<my_database>
[2023-11-16T13:27:35Z WARN  sqlpage::webserver::database::sql] Failed to prepare "SELECT 'hero' AS component, 'It works !' AS title, 'If you can see this, then SQLPage v' || CAST($1 AS TEXT) || ' is running correctly on your server. Congratulations! ' AS description": Failed to prepare SQL statement:
    SELECT 'hero' AS component, 'It works !' AS title, 'If you can see this, then SQLPage v' || CAST($1 AS TEXT) || ' is running correctly on your server. Congratulations! ' AS description: error returned from database: prepared statement "sqlx_s_1" already exists: prepared statement "sqlx_s_1" already exists
[2023-11-16T13:27:35Z WARN  sqlpage::webserver::database::sql] Failed to prepare "SELECT CAST($1 AS TEXT) AS contents, 1 AS code": Failed to prepare SQL statement:
    SELECT CAST($1 AS TEXT) AS contents, 1 AS code: error returned from database: prepared statement "sqlx_s_2" already exists: prepared statement "sqlx_s_2" already exists
[2023-11-16T13:27:35Z WARN  sqlpage::webserver::database::sql] Failed to prepare "SELECT 'You can customize your server''s [configuration](https://github.com/lovasoa/SQLpage/blob/main/configuration.md)\r\nby creating a file in `' || CAST($1 AS TEXT) || '/sqlpage/sqlpage.json`.' AS contents_md": Failed to prepare SQL statement:
    SELECT 'You can customize your server''s [configuration](https://github.com/lovasoa/SQLpage/blob/main/configuration.md): error returned from database: prepared statement "sqlx_s_2" already exists: prepared statement "sqlx_s_2" already exists
[2023-11-16T13:27:35Z INFO  sqlpage::webserver::database::migrations] Applying migrations from 'C:\Users\...\sqlpage/migrations'
[2023-11-16T13:27:35Z INFO  sqlpage::webserver::database::migrations] No migration found. You can specify database operations to apply when the server first starts by creating files in sqlpage/migrations/<VERSION>_<DESCRIPTION>.sql where <VERSION> is a number and <DESCRIPTION> is a short string.
[2023-11-16T13:27:35Z INFO  sqlpage] Starting server on 127.0.0.1:9001
[2023-11-16T13:27:35Z INFO  actix_server::builder] starting 8 workers
[2023-11-16T13:27:35Z INFO  actix_server::server] Actix runtime found; starting in Actix runtime
[2023-11-16T13:27:35Z INFO  sqlpage] Server started successfully.
        SQLPage is now running on http://127.0.0.1:9001/
        You can write your website's code in .sql files in C:\Users\...\sqlpage-windows.

When I open the page (using Chrome) I see this error:

Error in query number 1:
Failed to execute SQL statement:
SELECT a.name AS title FROM assets AS a
Backtrace
error returned from database: prepared statement "sqlx_s_3" already exists
prepared statement "sqlx_s_3" already exists

What am I doing wrong?

[lovasoa]

Hi ! This indeed sounds like a bug ! Do you use pgbouncer ? Does the problem also occurs when connecting to the database directly ? SQLPage has its own connection pool.

[lovasoa]

If you are indeed connecting SQLPage to a connection pooler instead of directly to the database, can you try setting max_database_pool_connections to 1 and report whether or not the problem persists ?

[marco-ni]

The issue seems to have been fixed by setting max_database_pool_connections to 1 or greater. When I tried to set the value to 0 another error occurred:

thread 'main' panicked at C:\Users\runneradmin\.cargo\registry\src\index.crates.io-6f17d22bba15001f\crossbeam-queue-0.3.8\src\array_queue.rs:94:9:
capacity must be non-zero
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

[lovasoa]

Yes, I meant to 1, of course, sorry ! Are you indeed using pgbouncer ? Which version ? pgbouncer did not support prepared statement tracking until recently: pgbouncer/pgbouncer#845

[marco-ni]

As far as I know I'm not using pgbouncer. The remote database is owned by a supplier which gave us access to create custom reports, I don't know if pgbouncer is running on server side.

Anyway, this morning the warnings came out again:

[2023-11-17T07:06:48Z WARN  sqlpage::webserver::database::sql] Failed to prepare "SELECT 'hero' AS component, 'It works !' AS title, 'If you can see this, then SQLPage v' || CAST($1 AS TEXT) || ' is running correctly on your server. Congratulations! ' AS description": Failed to prepare SQL statement:
    SELECT 'hero' AS component, 'It works !' AS title, 'If you can see this, then SQLPage v' || CAST($1 AS TEXT) || ' is running correctly on your server. Congratulations! ' AS description: error returned from database: prepared statement "sqlx_s_2" already exists: prepared statement "sqlx_s_2" already exists
[2023-11-17T07:06:48Z WARN  sqlpage::webserver::database::sql] Failed to prepare "SELECT CAST($1 AS TEXT) AS contents, 1 AS code": Failed to prepare SQL statement:
    SELECT CAST($1 AS TEXT) AS contents, 1 AS code: error returned from database: prepared statement "sqlx_s_3" already exists: prepared statement "sqlx_s_3" already exists
[2023-11-17T07:06:48Z WARN  sqlpage::webserver::database::sql] Failed to prepare "SELECT 'You can customize your server''s [configuration](https://github.com/lovasoa/SQLpage/blob/main/configuration.md)\r\nby creating a file in `' || CAST($1 AS TEXT) || '/sqlpage/sqlpage.json`.' AS contents_md": Failed to prepare SQL statement:
    SELECT 'You can customize your server''s [configuration](https://github.com/lovasoa/SQLpage/blob/main/configuration.md): error returned from database: prepared statement "sqlx_s_4" already exists: prepared statement "sqlx_s_4" already exists

the errors during query preparation appeared as well:

Error in query number 1:
Failed to execute SQL statement:
SELECT a.name AS title FROM assets AS a
Backtrace
error returned from database: prepared statement "sqlx_s_3" already exists
prepared statement "sqlx_s_3" already exists

After relaunching the program a couple of times now seems to be working, even if the warning messages reported above are still present

[lovasoa]

It indeed looks like an old pgbouncer that does not track prepared statements correctly. I'll see about adding an option not to use prepared statements. The performance would be worse, but you would get rid of these errors

[lovasoa]

I updated our postgres database driver to randomize prepared statement names, which should prevent name collisions when using a connection pooler. Can you try and confirm this fixes your issue ?

The easiest way to try the latest changes is by using the docker image with lovasoa/sqlpage:main

docker run -it --name sqlpage -p 8080:8080 -e 'DATABASE_URL=postgres://your-server' --volume "/your/sql/website:/var/www" --rm lovasoa/sqlpage:main

[vks2]

encountered a similar issue.
use pgouncer in prod

[lovasoa]

@vks2 : can you open a new issue, ideally with a way to reliably reproduce the problem, and the exact error message and logs you are seeing ?

[vks2]

i'll try. but set up is basicly your auth example + postgres with pgbouncer on port 6432