BUG #19064: Trigger allows creation with invalid column references but fails at runtime
The following bug has been logged on the website:
Bug reference: 19064
Logged by: Ketan Bhatiya
Email address: ketan.bhatiya@lenditt.com
PostgreSQL version: Unsupported/Unknown
Operating system: Windows
Description:
Example Function & Trigger
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
-- Wrong column reference: "wrong_column" does not exist in "Orders"
UPDATE Orders
SET total = NEW.wrong_column
WHERE id = NEW.id;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE TRIGGER trg_update_order_total
AFTER UPDATE ON Orders
FOR EACH ROW
EXECUTE FUNCTION update_order_total();
Steps to Reproduce
Run the above function and trigger creation.
✅ They are created successfully.
Update a row in Orders.
Expected Result
If the trigger contains a reference to a non-existing column, PostgreSQL
should throw an error at creation time.
Actual Result
The function and trigger creation succeed.
❌ At runtime, when an update happens, it fails with:
ERROR: record "new" has no field "wrong_column"
Suggestion / Proposed Improvement
PostgreSQL should validate column references at trigger creation time, not
just at runtime.
If a column does not exist in the target table (Orders in this example),
trigger creation should fail immediately with a clear error message.
On Friday, September 26, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:
The following bug has been logged on the website:
Bug reference: 19064
Logged by: Ketan Bhatiya
Email address: ketan.bhatiya@lenditt.com
PostgreSQL version: Unsupported/Unknown
Operating system: Windows
Description:Suggestion / Proposed Improvement
PostgreSQL should validate column references at trigger creation time, not
just at runtime.
If a column does not exist in the target table (Orders in this example),
trigger creation should fail immediately with a clear error message.
The bug reporting list should not be used for feature requests. Those
should be directed to -general.
It seems unlikely anyone is going to spend time on this though. You are
expected to test what you write and this fails immediately and obviously.
David J.
Hi
Suggestion / Proposed Improvement
PostgreSQL should validate column references at trigger creation time, not
just at runtime.
If a column does not exist in the target table (Orders in this example),
trigger creation should fail immediately with a clear error message.
This behaviour is by design in PostgreSQL. The CreateTrigger() is
responsible
for creating a dependency between trigger and its underlying pl/pgsql
function.
The pl/pgsql function is only parsed when it is executed for the first time.
This approach allows for flexibility, such as adding columns to a table
after a trigger
has been created. On the other hand, if a column existing at the time of
trigger creation
is later removed, validating at creation time would not offer much
advantage in these
situations.
Thank you,
Rahila Syed
From my point of view, this extension can help detect these types of
problems:
https://github.com/okbob/plpgsql_check
Regards
El vie, 26 sept 2025 a las 10:00, Rahila Syed (<rahilasyed90@gmail.com>)
escribió:
Show quoted text
Hi
Suggestion / Proposed Improvement
PostgreSQL should validate column references at trigger creation time, not
just at runtime.
If a column does not exist in the target table (Orders in this example),
trigger creation should fail immediately with a clear error message.This behaviour is by design in PostgreSQL. The CreateTrigger() is
responsible
for creating a dependency between trigger and its underlying pl/pgsql
function.
The pl/pgsql function is only parsed when it is executed for the first
time.This approach allows for flexibility, such as adding columns to a table
after a trigger
has been created. On the other hand, if a column existing at the time of
trigger creation
is later removed, validating at creation time would not offer much
advantage in these
situations.Thank you,
Rahila Syed