prepared statement "__pgdog_1" does not exist #184

denysvitali · 2025-05-23T21:44:10Z

When used with Immich, pgdog fails to use the prepared statement (?)

PostgresError: prepared statement "__pgdog_1" does not exist
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '26000',
  file: 'prepare.c',
  line: '451',
  routine: 'FetchPreparedStatement'
}

`pgdog.toml`

[general]
host = "0.0.0.0"
port = 6432
workers = 4
default_pool_size = 5
min_pool_size = 3
passthrough_auth = "disabled"
query_timeout = 30000
connect_timeout = 30000
checkout_timeout = 5000
pooler_mode = "session"
prepared_statements = "extended"
ban_timeout = 50

[[databases]]
name = "immich"
host = "immich-rw"
port = 5432
role = "primary"

[[databases]]
name = "immich"
host = "immich-ro"
port = 5432

Mind that I'm running pgdog in Kubernetes (3 replicas) - but this seems to happen also with a single replica.

The text was updated successfully, but these errors were encountered:

levkk · 2025-05-23T22:58:21Z

Thanks for the report. Could you please run pgdog with export RUST_LOG=trace and attach the log to this report? I'm assuming you're not using it in production yet. This will generate a lot of logs. Thank you!

denysvitali · 2025-06-01T21:17:28Z

This could be due to the configuration of the load balanced service - I'm seeing similar issues in my server logs:

[Nest] 19  - 06/01/2025, 9:15:16 PM   ERROR [Api:ErrorInterceptor~1iugbjnn] Unknown error: PostgresError: unnamed prepared statement does not exist
PostgresError: unnamed prepared statement does not exist
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

I've also had an error complaining that the prepared statement used a different amount of paramters than the one expected. I'll try to have just one pgdog replica and report back

denysvitali · 2025-06-01T21:24:14Z

(assuming this is the same error)

Setup:

1x pgdog instance
2x immich-server

Config:

[general]
host = "0.0.0.0"
port = 6432
workers = 4
default_pool_size = 5
min_pool_size = 3
passthrough_auth = "disabled"
query_timeout = 30000
connect_timeout = 30000
checkout_timeout = 5000
pooler_mode = "transaction"
prepared_statements = "disabled"
ban_timeout = 50

[[databases]]
name = "immich"
host = "immich-rw"
port = 5432
role = "primary"

[[databases]]
name = "immich"
host = "immich-ro"
port = 5432

Immich fails to start with:

Query failed : {
  durationMs: 34.44214400000055,
  error: PostgresError: unnamed prepared statement does not exist
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:518:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
      at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '26000',
    file: 'postgres.c',
    line: '1673',
    routine: 'exec_bind_message'
  },
  sql: 'SELECT indexdef, indexname FROM pg_indexes WHERE indexname = ANY(ARRAY[$1, $2])',
  params: [ 'clip_index', 'face_index' ]
}
[Nest] 19  - 06/01/2025, 9:20:41 PM    WARN [Api:DatabaseService] Could not run vector reindexing checks. If the extension was updated, please restart the Postgres instance. If you are upgrading direct
PostgresError: unnamed prepared statement does not exist
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '26000',
  file: 'postgres.c',
  line: '1673',
  routine: 'exec_bind_message'
}
api worker exited with code 1

Attached you can find pgdog's logs:

1.log

levkk · 2025-06-02T03:19:24Z

Hmm. Try prepared_statements = "extended". I have a feeling it's because we break the extended protocol to make it work with load balancing. I suspect I need to enable prepared statements at all times to make sure it works for all use cases.

Edit: Yes, this is it. Looking at the logs this is what happens:

Client sends anonymous Parse, Describe and then Flush. PgDog checks out a connection, prepares the statement to make sure it's syntactically valid and then checks the connection back into the pool.
This happens because when the client sends Bind and Execute, the decision which server this should go to is dependent on the contents of the Bind message. This is done for sharding, since Bind with values X can go to shard 0, while Bind with values Y can go to shard 1, etc. This breaks the extended protocol expectations.
We work around that in PgDog by giving all prepared statements a name and a mapping in the client cache. So, if prepared statements are enabled, what will happen here is the prepared statement will be renamed to __pgdog_x and saved in the global cache, while the mapping between "" (empty, anonymous) and __pgdog_x will be stored in the client cache. So the bind will be associated with the correct statement.
Since prepared statements are disabled, we don't do this renaming and mapping, and this is why there is a mismatch.

I think setting prepared_statements = "extended" should fix this for you. Let me know if it doesn't.

...Although, I'm still trying to understand how you got the original error in the first place.

denysvitali · 2025-06-02T16:14:35Z

Seems to work with prepared_statements = "extended". Mind that I have to run this with pooler_mode = "transaction" becasue otherwise Immich fails with:

Query failed : {
  durationMs: 23.187476999824867,
  error: PostgresError: cannot execute UPDATE in a read-only transaction
      at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26)
      at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6)
      at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
      at Socket.emit (node:events:518:28)
      at addChunk (node:internal/streams/readable:561:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
      at Readable.push (node:internal/streams/readable:392:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
      at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
    severity_local: 'ERROR',
    severity: 'ERROR',
    code: '25006',
    file: 'utility.c',
    line: '414',
    routine: 'PreventCommandIfReadOnly'
  },
  sql: 'update "sessions" set "id" = $1, "updatedAt" = $2 where "sessions"."id" = $3::uuid returning *',
  params: [
    'd6e16b33-b60f-49da-a076-1108762f1216',
    2025-06-02T16:08:50.603Z,
    'd6e16b33-b60f-49da-a076-1108762f1216'
  ]
}
[Nest] 19  - 06/02/2025, 4:08:50 PM   ERROR [Api:GlobalExceptionFilter~nbpgap53] Unknown error: PostgresError: cannot execute UPDATE in a read-only transaction
PostgresError: cannot execute UPDATE in a read-only transaction
    at ErrorResponse (/usr/src/app/node_modules/postgres/cjs/src/connection.js:790:26)
    at handle (/usr/src/app/node_modules/postgres/cjs/src/connection.js:476:6)
    at Socket.data (/usr/src/app/node_modules/postgres/cjs/src/connection.js:315:9)
    at Socket.emit (node:events:518:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:130:17)

Should I open a separate issue to track this one?

levkk · 2025-06-03T02:35:30Z

No it's fine, we can keep working off of this issue. I think the problem is two-fold:

Documentation around what works in transaction vs session pooling mode. For instance, load balancing, read/write split and sharding doesn't - it only works in transaction mode. I need to document that.
There are interesting interactions between settings, and I need some kind of warning system that tells you that setting A will affect setting B, for example.

denysvitali · 2025-06-03T06:34:16Z

No worries, thank you for your help 🎉

denysvitali · 2025-06-03T20:38:33Z

It happened again with the latest version from master.
My config:

pgdog:
  replicaCount: 1
  workers: 4
  defaultPoolSize: 5
  minPoolSize: 3
  passthroughAuth: disabled
  queryTimeout: 30000
  connectTimeout: 30000
  checkoutTimeout: 5000
  logLevel: trace
  poolerMode: session
  banTimeout: 50 # 50ms
  preparedStatements: extended

Attached you can see the full trace of pgdog, including the error (prepared statement "__pgdog_1" does not exist).

2.txt

levkk · 2025-06-03T21:09:14Z

Enable transaction pooling:

pooler_mode = "transaction"

Most features around load balancing that you're using don't work in session mode. Curious, do you need session pooling for something? I'm guessing it's for advisory locks?

denysvitali · 2025-06-04T05:57:35Z

Oh, sorry, you're right. I switched back the pooler_mode to the wrong setting (again!).

The reason why I switched to that was because I wanted to try to solve another issue in the pgdog / immich integration. I'll open a new issue as soon as I face the other issue again - in the meantime I added to my configuration a note to not use session mode ever again 😀

levkk · 2025-06-04T13:57:12Z

No problem! I'm going to add a warning on PgDog startup to let people know that session mode basically doesn't have any of the cool features.

levkk mentioned this issue May 24, 2025

More LB tests' #186

Merged

denysvitali closed this as completed Jun 4, 2025

levkk reopened this Jun 4, 2025

levkk closed this as completed Jun 4, 2025

levkk mentioned this issue Jun 5, 2025

Add warning for session mode #208

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

prepared statement "__pgdog_1" does not exist #184

prepared statement "__pgdog_1" does not exist #184

denysvitali commented May 23, 2025

levkk commented May 23, 2025

Uh oh!

denysvitali commented Jun 1, 2025

Uh oh!

denysvitali commented Jun 1, 2025 •

edited

Loading

Uh oh!

levkk commented Jun 2, 2025 •

edited

Loading

Uh oh!

denysvitali commented Jun 2, 2025

Uh oh!

levkk commented Jun 3, 2025 •

edited

Loading

Uh oh!

denysvitali commented Jun 3, 2025

Uh oh!

denysvitali commented Jun 3, 2025

Uh oh!

levkk commented Jun 3, 2025

Uh oh!

denysvitali commented Jun 4, 2025

Uh oh!

levkk commented Jun 4, 2025

Uh oh!

prepared statement "__pgdog_1" does not exist #184

prepared statement "__pgdog_1" does not exist #184

Comments

denysvitali commented May 23, 2025

pgdog.toml

levkk commented May 23, 2025

Uh oh!

denysvitali commented Jun 1, 2025

Uh oh!

denysvitali commented Jun 1, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

levkk commented Jun 2, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

denysvitali commented Jun 2, 2025

Uh oh!

levkk commented Jun 3, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

denysvitali commented Jun 3, 2025

Uh oh!

denysvitali commented Jun 3, 2025

Uh oh!

levkk commented Jun 3, 2025

Uh oh!

denysvitali commented Jun 4, 2025

Uh oh!

levkk commented Jun 4, 2025

Uh oh!

`pgdog.toml`

denysvitali commented Jun 1, 2025 •

edited

Loading

levkk commented Jun 2, 2025 •

edited

Loading

levkk commented Jun 3, 2025 •

edited

Loading