dolthub / dolthub/doltgresql

A trigger with `WHEN (old.* IS DISTINCT FROM new.*)` makes every UPDATE of its table fail

Open
#3,336 0 comments 0 reactions 1 assignee Claimed by @Hydrocharged View on GitHub
customer issue
Dominant language
Go
Stars
2.1k
Forks
73
Avg merge
1d 10h
Merged PRs (30d)
129

Description

On DoltgreSQL 1.3.1, a row trigger whose `WHEN` clause compares the whole old and new rows,
`WHEN (old.* IS DISTINCT FROM new.*)`, is created without complaint, but from then on every `UPDATE`
that reaches a row of the table fails, and the row keeps its old value:

```
ERROR: record "old" has no field "*"
```

PostgreSQL 18.6 evaluates the same clause, runs the trigger, and updates the row.

## Reproduction

[`repro.sql`](https://github.com/Reliable-Collaboration/repro-doltgresql-bug-trigger-when-whole-row/blob/main/repro.sql):

```sql
CREATE TABLE t (a int);
INSERT INTO t VALUES (1);

CREATE FUNCTION f() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE 'the trigger ran';
RETURN NEW;
END $$;

-- A trigger that fires only when an update changes a row.
CREATE TRIGGER tr BEFORE UPDATE ON t FOR EACH ROW
WHEN (old.* IS DISTINCT FROM new.*)
EXECUTE FUNCTION f();

-- An update that changes the row.
UPDATE t SET a = 2;

SELECT * FROM t;
```

## Expected behavior

The update changes the row, so the `WHEN` clause is true: the trigger function runs and prints its
notice, and the row is updated. This is what PostgreSQL 18.6 does, from the update on:

```
-- An update that changes the row.
UPDATE t SET a = 2;
psql:/tmp/repro.sql:16: NOTICE: the trigger ran
UPDATE 1
SELECT * FROM t;
a
---
2
(1 row)
```

## Actual behavior

The `CREATE TRIGGER` succeeds, but the `UPDATE` fails, no notice is printed, and the row keeps its old
value. This is what DoltgreSQL 1.3.1 does, from the update on:

```
-- An update that changes the row.
UPDATE t SET a = 2;
psql:/tmp/repro.sql:16: ERROR: record "old" has no field "*"
SELECT * FROM t;
a
---
1
(1 row)
```

## Run it

A runnable reproduction is at https://github.com/Reliable-Collaboration/repro-doltgresql-bug-trigger-when-whole-row. Its script runs the test on PostgreSQL and DoltgreSQL in throwaway containers and prints the two outputs side by side:

```sh
git clone https://github.com/Reliable-Collaboration/repro-doltgresql-bug-trigger-when-whole-row.git
cd repro-doltgresql-bug-trigger-when-whole-row
./repro.sh
```

## Other observations

Each variant was run on DoltgreSQL 1.3.1 and on PostgreSQL 18.6, and PostgreSQL ran every one without
an error:

- `WHEN (old.* IS NOT DISTINCT FROM new.*)`, `WHEN (ROW(old.*) IS DISTINCT FROM ROW(new.*))`,
`WHEN (old.* <> new.*)` and `WHEN ((OLD.* IS DISTINCT FROM NEW.*))` fail with the same error.
- An `AFTER UPDATE` trigger, a table with a primary key and a second column, and a trigger function
whose body is only `RETURN NEW;` fail the same way.
- An `UPDATE` that sets a column to its own value fails too. An `UPDATE` whose `WHERE` matches no row
answers `UPDATE 0` without an error.
- The comparison inside the function body, `IF OLD.* IS DISTINCT FROM NEW.* THEN` or
`IF ROW(OLD.*) IS DISTINCT FROM ROW(NEW.*) THEN`, with no `WHEN` clause, fails with the same error.
- Other events fail the same way: a `BEFORE INSERT` trigger with `WHEN (new.* IS NOT NULL)` refuses
the `INSERT` with `ERROR: record "new" has no field "*"`, and an `AFTER DELETE` trigger with
`WHEN (old.* IS NOT NULL)` refuses the `DELETE`.
- Without the star, `WHEN (old IS DISTINCT FROM new)` also refuses the `UPDATE`, with a different error
that begins `ERROR: receiveMessage recovered panic: cannot find function:` and carries a Go stack
trace. `IF OLD IS DISTINCT FROM NEW THEN` in the function body does the same.
- A per-column `WHEN (old.a IS DISTINCT FROM new.a)` works: the trigger runs and the row is updated.
- `INSERT` and `DELETE` on the table work while the `UPDATE` trigger exists, and after `DROP TRIGGER`
updates work again.

## Possibly related

None found. #2936 and #2197 involve record comparisons but are different bugs.

## Environment

- DoltgreSQL 1.3.1, the newest release when this was written: image `dolthub/doltgresql:1.3.1`, digest
`sha256:6c85cb1f35beabf47f094336a420255130b841b1645f36d79ef046276af36851`. Its bundled `psql` is 17.11.
- PostgreSQL 18.6: image `postgres:18.6-bookworm`, digest
`sha256:1c59e2c3c818eaa0f0628f695b36e7c9e362d6b219b36a54a32df645cbd7e1af`. Its bundled `psql` is 18.6.
- Reproduced on 2026-09-10 with Docker 29.7.2 on Linux x86_64 (Ubuntu 26.04.1 LTS under WSL 2).

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.