Closed marco-ni closed 9 months ago
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.
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 ?
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
Yes, I meant to 1, of course, sorry ! Are you indeed using pgbouncer ? Which version ? pgbouncer did not support prepared statement tracking until recently: https://github.com/pgbouncer/pgbouncer/pull/845
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
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
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
encountered a similar issue. use pgouncer in prod
@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 ?
i'll try. but set up is basicly your auth example + postgres with pgbouncer on port 6432
Hi, I'm connecting to a remote PostgreSQL database using the following sqlpage.json file. Actual parameters are omitted but it connects normally:
This is the index.json file:
When launching sqlpage.exe from a command window it reports some warnings that seem to be related to the default "it works" page:
When I open the page (using Chrome) I see this error:
What am I doing wrong?