pg.ddx.io pgdogdev/pgdog GitHub issues and pull requests
help / color / mirror / Atom feed[pgdogdev/pgdog] issue #2057: Failed cross-shard sharding key UPDATE sends an extra ReadyForQuery to the client
2+ messages / 2 participants
[nested] [flat]
* [pgdogdev/pgdog] issue #2057: Failed cross-shard sharding key UPDATE sends an extra ReadyForQuery to the client
@ 2026-11-18 01:08 "scottjacobsen (@scottjacobsen)" <noreply+scottjacobsen@github.com>
0 siblings, 0 replies; 2+ messages in thread
From: scottjacobsen (@scottjacobsen) @ 2026-11-18 01:08 UTC (permalink / raw)
To: pgdogdev/pgdog <noreply+pgdogdev-pgdog@github.com>
**PgDog version**
`v0.1.64` (`1deb1c6`, image `ghcr.io/pgdogdev/pgdog:v0.1.64`). Same behavior on `v0.1.65` (`ed86b8c`).
**Description**
With `rewrite.shard_key = "rewrite"`, an `UPDATE` that moves the sharding key to another shard is rewritten into `DELETE ... RETURNING` on the source shard plus `INSERT` on the destination shard. The application pattern is: try the move inside a `SAVEPOINT`; if the destination already holds the row (unique violation), `ROLLBACK TO` the savepoint and delete the stale source row instead; `COMMIT`.
When the rewritten `INSERT` fails, PgDog forwards the error to the client and then sends one extra `ReadyForQuery`, so the client connection is out of sync for the statements that follow inside the transaction. libpq reports it as:
```
message type 0x5a arrived from server while idle
```
With psql the stray message is discarded with that warning and the transaction completes. Drivers that consume the extended protocol asynchronously (Ruby `pg` under Rails, for example) intermittently attach the stray message to the `DELETE` that follows and get a nil result for it (`undefined method 'cmd_tuples' for nil` in Rails), which fails the transaction.
What should happen: the client sees one `ErrorResponse` followed by one `ReadyForQuery` (in-transaction state), and the `ROLLBACK TO` / `DELETE` / `COMMIT` replies start cleanly, as they do against a single Postgres (output below).
Both shards have the same table:
```sql
create table items (
id bigint primary key,
tenant_id bigint not null,
name text not null,
unique (tenant_id, name)
);
```
Reproduction (files below):
```sh
docker compose up -d
PGPASSWORD=postgres psql -h 127.0.0.1 -p 6432 -U postgres -d app -f repro.sql
```
Output through PgDog:
```
INSERT 0 1
INSERT 0 1
BEGIN
SAVEPOINT
psql:repro.sql:9: ERROR: ERROR: 23505 duplicate key value violates unique constraint "items_tenant_id_name_key"
Key (tenant_id, name)=(3, widget) already exists.
DETAIL: update items set tenant_id = 3 where id = 1 and tenant_id = 1;
psql:repro.sql:9: message type 0x5a arrived from server while idle
ROLLBACK
DELETE 1
COMMIT
id | tenant_id | name
----+-----------+--------
2 | 3 | widget
(1 row)
```
Same script against one of the Postgres databases directly:
```
INSERT 0 1
INSERT 0 1
BEGIN
SAVEPOINT
psql:<stdin>:9: ERROR: duplicate key value violates unique constraint "items_tenant_id_name_key"
DETAIL: Key (tenant_id, name)=(3, widget) already exists.
ROLLBACK
DELETE 1
COMMIT
id | tenant_id | name
----+-----------+--------
2 | 3 | widget
(1 row)
```
The same flow over the extended protocol (`repro.rb`, Ruby `pg` gem) prints the same libpq warning:
```
libpq: message type 0x5a arrived from server while idle
update raised PG::SystemError: ERROR: ERROR: 23505 duplicate key value violates unique constraint "items_tenant_id_name_key"
delete: cmd_status="DELETE 1" cmd_tuples=1
[["2", "3", "widget"]]
```
**Logs**
`RUST_LOG=trace`, from the rewritten `INSERT` to the client's `DELETE`. `>>>` is a message sent, `<<<` a message received; `[192.168.65.1:24799]` is the psql client.
```
2026-11-18T01:05:07.032916Z DEBUG [update] executing multi-shard insert/delete
2026-11-18T01:05:07.032918Z TRACE Parse >>> [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.032925Z TRACE Describe >>> [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.032927Z TRACE Bind >>> [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.032935Z TRACE Execute >>> [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.032936Z TRACE Sync >>> [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033114Z TRACE ParseComplete <<< [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033120Z TRACE ParameterDescription <<< [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033122Z TRACE Message <<< [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033124Z TRACE BindComplete <<< [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033126Z TRACE ErrorResponse <<< [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033144Z ERROR "ERROR: 23505 duplicate key value violates unique constraint \"items_tenant_id_name_key\"\nKey (tenant_id, name)=(3,
2026-11-18T01:05:07.033164Z TRACE ReadyForQuery <<< [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033168Z TRACE ReadyForQuery >>> [192.168.65.1:47077]
2026-11-18T01:05:07.033498Z DEBUG rollback to savepoint move;
2026-11-18T01:05:07.033528Z DEBUG already connected to [postgres@postgres:5432/shard_0,postgres@postgres:5432/shard_1] using route [shard=all, role=pr
2026-11-18T01:05:07.033532Z TRACE Query >>> [postgres@postgres:5432/shard_0]
2026-11-18T01:05:07.033546Z TRACE Query >>> [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033615Z TRACE CommandComplete <<< [postgres@postgres:5432/shard_0]
2026-11-18T01:05:07.033622Z TRACE CommandComplete <<< [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033624Z TRACE CommandComplete >>> [192.168.65.1:47077]
2026-11-18T01:05:07.033625Z TRACE ReadyForQuery <<< [postgres@postgres:5432/shard_0]
2026-11-18T01:05:07.033626Z TRACE ReadyForQuery <<< [postgres@postgres:5432/shard_1]
2026-11-18T01:05:07.033627Z TRACE ReadyForQuery >>> [192.168.65.1:47077]
2026-11-18T01:05:07.033901Z DEBUG delete from items where id = 1 and tenant_id = 1;
```
**Configuration**
`pgdog.toml`:
```toml
[general]
host = "0.0.0.0"
port = 6432
passthrough_auth = "enabled_plain"
[rewrite]
enabled = true
shard_key = "rewrite"
split_inserts = "rewrite"
primary_key = "rewrite"
[[databases]]
name = "app"
host = "postgres"
database_name = "shard_0"
shard = 0
[[databases]]
name = "app"
host = "postgres"
database_name = "shard_1"
shard = 1
[[sharded_tables]]
database = "app"
column = "tenant_id"
```
No `users.toml`.
`compose.yml`:
```yaml
services:
postgres:
image: postgres:17
environment:
POSTGRES_PASSWORD: postgres
volumes:
- ./init:/docker-entrypoint-initdb.d:ro
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres"]
interval: 1s
retries: 30
pgdog:
image: ghcr.io/pgdogdev/pgdog:v0.1.64
environment:
NODE_ID: repro-0
volumes:
- ./pgdog.toml:/pgdog/pgdog.toml:ro
ports:
- "6432:6432"
depends_on:
postgres:
condition: service_healthy
```
`init/01-shards.sh` (creates both shard databases and the table in each):
```sh
#!/bin/sh
set -e
for db in shard_0 shard_1; do
psql -v ON_ERROR_STOP=1 -U postgres -c "create database $db"
psql -v ON_ERROR_STOP=1 -U postgres -d "$db" <<'SQL'
create table items (
id bigint primary key,
tenant_id bigint not null,
name text not null,
unique (tenant_id, name)
);
SQL
done
```
`repro.sql`:
```sql
\set ON_ERROR_STOP off
-- tenant_id 1 hashes to shard 0, tenant_id 3 to shard 1
insert into items (id, tenant_id, name) values (1, 1, 'widget');
insert into items (id, tenant_id, name) values (2, 3, 'widget');
-- move row 1 to tenant 3; if tenant 3 already has the row, drop the stale one instead
begin;
savepoint move;
update items set tenant_id = 3 where id = 1 and tenant_id = 1;
rollback to savepoint move;
delete from items where id = 1 and tenant_id = 1;
commit;
select id, tenant_id, name from items order by id;
```
`repro.rb`:
```ruby
require "pg"
conn = PG.connect(host: "127.0.0.1", port: ENV.fetch("PGPORT", "6432"), user: "postgres", password: "postgres", dbname: ENV.fetch("PGDATABASE", "app"))
conn.set_notice_receiver { |notice| puts "libpq: #{notice.error_message.strip}" }
conn.exec_params("insert into items (id, tenant_id, name) values ($1, $2, $3)", [1, 1, "widget"])
conn.exec_params("insert into items (id, tenant_id, name) values ($1, $2, $3)", [2, 3, "widget"])
conn.exec("begin")
conn.exec("savepoint move")
begin
conn.exec_params("update items set tenant_id = $1 where id = $2 and tenant_id = $3", [3, 1, 1])
rescue PG::Error => e
puts "update raised #{e.class}: #{e.message.lines.first.strip}"
conn.exec("rollback to savepoint move")
deleted = conn.exec_params("delete from items where id = $1 and tenant_id = $2", [1, 1])
puts "delete: cmd_status=#{deleted.cmd_status.inspect} cmd_tuples=#{deleted.cmd_tuples}"
end
conn.exec("commit")
puts conn.exec("select id, tenant_id, name from items order by id").values.inspect
```
^ permalink raw [nested|flat] 2+ messages in thread
* Re: [pgdogdev/pgdog] issue #2057: Failed cross-shard sharding key UPDATE sends an extra ReadyForQuery to the client
@ 2026-11-18 22:25 "levkk (@levkk)" <noreply+levkk@github.com>
0 siblings, 0 replies; 2+ messages in thread
From: levkk (@levkk) @ 2026-11-18 22:25 UTC (permalink / raw)
To: pgdogdev/pgdog <noreply+pgdogdev-pgdog@github.com>
@levkk closed this.
^ permalink raw [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-11-18 22:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-18 01:08 [pgdogdev/pgdog] issue #2057: Failed cross-shard sharding key UPDATE sends an extra ReadyForQuery to the client "scottjacobsen (@scottjacobsen)" <noreply+scottjacobsen@github.com>
2026-11-18 22:25 Re: [pgdogdev/pgdog] issue #2057: Failed cross-shard sharding key UPDATE sends an extra ReadyForQuery to the client "levkk (@levkk)" <noreply+levkk@github.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox