CODEWITHSUNDEEP
postgresqltriggers
Wiktor
Wiktor

Reputation: 3059

PostgreSQL trigger raises error 55000

after migrating from PostgreSQL server version 9 to 8.4 I have encountered very strange error.

Short description:
If there is a trigger on a given table for each row before insert or update and one uses in conditional statement (if-else) TG_OP value check and OLD object, following error raises when doinng INSERT:

ERROR:  record "old" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.

Detailed description:
There is following DB structure:

CREATE TABLE table1
(
  id serial NOT NULL,
  name character varying(256),
  CONSTRAINT table1_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

CREATE OR REPLACE FUNCTION exemplary_function()
RETURNS trigger AS
$BODY$    BEGIN
IF TG_OP = 'INSERT' OR OLD.name <> NEW.name THEN
    NEW.name = 'someName';
    END IF;

RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;

CREATE TRIGGER trigger1
  BEFORE INSERT OR UPDATE
  ON table1
FOR EACH ROW EXECUTE PROCEDURE exemplary_function();

and following SQL query that triggers error:

INSERT INTO table1 (name) VALUES ('other name')

It seems like parser is not stopping on TG_OP = 'INSERT' condition (and it should, because it is true) but checks another one and that triggers an error. What's interesting, I was only able to reproduce it on version 8.4.

Upvotes: 1

Views: 12177

Answers (1)

Gary - Stand with Ukraine
Gary - Stand with Ukraine

Reputation: 7541

Postgres doesn't officially do short cuts on boolean statements (Unlike C for example)

It does say it that sometimes it can decide to short cut (see docs) but it might just easily decide to short cut on the second expression rather than the first.

It basically looks at how complicated the expressions on each side are before deciding the evaluation order. Then if that is TRUE it can decide not to bother with the other side.

In this case, it looks like its trying to interpret OLD while its still trying to decide the best order in which to evaluate the expression.

You should be able get around this by using a CASE to split the expressions eg.

IF (CASE WHEN TG_OP = 'INSERT' THEN TRUE ELSE OLD.name <> NEW.name END) THEN

Upvotes: 2

Related Questions