`information_schema.triggers` is empty while the trigger exists and fires
- Dominant language
- Go
- Stars
- 2.1k
- Forks
- 73
- Avg merge
- 1d 10h
- Merged PRs (30d)
- 129
Description
On DoltgreSQL 1.3.1, `information_schema.triggers` answers `(0 rows)` for a trigger that exists: the trigger
fires on `INSERT`, and `pg_trigger` lists it. PostgreSQL 18.6 lists the same trigger in both catalogs: the
view answers one row with trigger `t_report`, event `INSERT` and table `t`.
## Reproduction
[`repro.sql`](https://github.com/Reliable-Collaboration/repro-doltgresql-bug-information-schema-triggers/blob/main/repro.sql):
```sql
-- One table, one trigger function, and one trigger.
CREATE TABLE t (a int);
CREATE FUNCTION report_row() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE 'trigger fired for a = %', NEW.a;
RETURN NEW;
END;
$$;
CREATE TRIGGER t_report BEFORE INSERT ON t
FOR EACH ROW EXECUTE FUNCTION report_row();
-- The trigger fires, and prints its notice.
INSERT INTO t VALUES (1);
-- The trigger in information_schema.triggers.
SELECT trigger_name, event_manipulation,
event_object_table
FROM information_schema.triggers;
-- The trigger in pg_trigger.
SELECT tgname FROM pg_trigger
WHERE tgrelid = 't'::regclass;
```
## Expected behavior
The trigger fires and prints its notice, and both catalogs list it: `information_schema.triggers` answers
one row with the trigger's name, its event and its table, and `pg_trigger` answers its name. This is what
PostgreSQL 18.6 does:
```
-- The trigger fires, and prints its notice.
INSERT INTO t VALUES (1);
psql:/tmp/repro.sql:16: NOTICE: trigger fired for a = 1
INSERT 0 1
-- The trigger in information_schema.triggers.
SELECT trigger_name, event_manipulation,
event_object_table
FROM information_schema.triggers;
trigger_name | event_manipulation | event_object_table
--------------+--------------------+--------------------
t_report | INSERT | t
(1 row)
-- The trigger in pg_trigger.
SELECT tgname FROM pg_trigger
WHERE tgrelid = 't'::regclass;
tgname
----------
t_report
(1 row)
```
## Actual behavior
The trigger fires and prints the same notice, and `pg_trigger` lists it, but `information_schema.triggers`
answers `(0 rows)`. The view's column names also come back in upper case. This is what DoltgreSQL 1.3.1
does:
```
-- The trigger fires, and prints its notice.
INSERT INTO t VALUES (1);
psql:/tmp/repro.sql:16: NOTICE: trigger fired for a = 1
INSERT 0 1
-- The trigger in information_schema.triggers.
SELECT trigger_name, event_manipulation,
event_object_table
FROM information_schema.triggers;
TRIGGER_NAME | EVENT_MANIPULATION | EVENT_OBJECT_TABLE
--------------+--------------------+--------------------
(0 rows)
-- The trigger in pg_trigger.
SELECT tgname FROM pg_trigger
WHERE tgrelid = 't'::regclass;
tgname
----------
t_report
(1 row)
```
## Run it
A runnable reproduction is at https://github.com/Reliable-Collaboration/repro-doltgresql-bug-information-schema-triggers. 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-information-schema-triggers.git
cd repro-doltgresql-bug-information-schema-triggers
./repro.sh
```
## Other observations
Each was checked on DoltgreSQL 1.3.1 and PostgreSQL 18.6 with the same kind of test:
- Every trigger tried is missing from the view: `BEFORE INSERT`, `AFTER INSERT`, `AFTER UPDATE`,
`BEFORE DELETE` and `AFTER INSERT OR UPDATE` row triggers. With eight triggers, `pg_trigger` lists all
eight on both servers, and the view counts 0 on DoltgreSQL and 8 on PostgreSQL.
- The view is also empty for a trigger on a table in another schema, for a trigger in a second database,
from a new connection, and, on DoltgreSQL, after `SELECT dolt_commit('-Am', 'triggers')`.
- On DoltgreSQL, `SELECT * FROM information_schema.triggers` has 22 columns named in upper case, among them
`SQL_MODE`, `DEFINER`, `CHARACTER_SET_CLIENT`, `COLLATION_CONNECTION` and `DATABASE_COLLATION`.
PostgreSQL's view has 17 columns, none of those.
- `information_schema.triggered_update_columns` does not exist on DoltgreSQL
(`ERROR: table not found: triggered_update_columns`). PostgreSQL answers `(0 rows)`.
- `pg_get_triggerdef()` returns the `CREATE TRIGGER` statement as it was typed, with its line break and an
unqualified table name, where PostgreSQL returns one line with `ON public.t`.
- The test's trigger function prints a notice because on DoltgreSQL 1.3.1 a trigger function that assigns
to a field of `NEW`, such as `NEW.b := NEW.a + 1;`, makes the `INSERT` fail with
`ERROR: receiveMessage recovered panic: runtime error: index out of range [1] with length 1`, a separate
bug. PostgreSQL runs it.
## Possibly related
None found.
## Environment
- DoltgreSQL 1.3.1, the newest release when this was written: image `dolthub/doltgresql:1.3.1`, digest
`sha256:6c85cb1f35beabf47f094336a420255130b841b1645f36d79ef046276af36851`, built for linux/amd64 and
linux/arm64. Its bundled `psql` is 17.11.
- PostgreSQL 18.6: image `postgres:18.6-bookworm`, digest
`sha256:1c59e2c3c818eaa0f0628f695b36e7c9e362d6b219b36a54a32df645cbd7e1af`. Its `psql` is 18.6.
- Reproduced on 2026-09-11 (UTC) with Docker 29.7.2 on Ubuntu 26.04.1 LTS under WSL2 (Linux 6.18.33.2,
x86_64).
Contributor guide
Assessment
This issue has not been assessed yet.