Re: Potentially undocumented behaviour change in Postgres 11 concerning OLD record in an after insert trigger

Hi,
I've read the documentation, that's why I said this might be undocumented.
Try the SQL in Postgres 11 and see that it works for yourself.
I have an analogous trigger in production from yesterday and I've tested it
in local environment as well.

On Fri, Jan 4, 2019 at 12:56 PM Charles Clavadetscher <
clavadetscher(at)swisspug(dot)org> wrote:

> Hello
>
>
>
> *From:* Kristjan Tammekivi <kristjantammekivi(at)gmail(dot)com>
> *Sent:* Freitag, 4. Januar 2019 11:46
> *To:* pgsql-general(at)postgresql(dot)org
> *Subject:* Potentially undocumented behaviour change in Postgres 11
> concerning OLD record in an after insert trigger
>
>
>
> Hi,
>
>
>
> I've noticed a change in the behaviour in triggers / hstores in Postgres
> 11.1 when compared to Postgres 10.5.
>
> The following won't work on Postgres 10.5 but in Postgres 11.1 it works
> just fine:
>
>
>
> CREATE EXTENSION hstore;
>
> CREATE TABLE _tmp_test1 (id serial PRIMARY KEY, val INTEGER);
> CREATE TABLE _tmp_test1_changes (id INTEGER, changes HSTORE);
>
> CREATE FUNCTION test1_trigger ()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS
> $BODY$
> BEGIN
> INSERT INTO _tmp_test1_changes (id, changes) VALUES (NEW.id, hstore(OLD) -
> hstore(NEW));
> RETURN NEW;
> END
> $BODY$;
>
> CREATE TRIGGER table_update AFTER INSERT OR UPDATE ON _tmp_test1
> FOR EACH ROW EXECUTE PROCEDURE test1_trigger();
>
>
>
> INSERT INTO _tmp_test1 (val) VALUES (5);
>
> ERROR: record "old" is not assigned yet
>
> DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
>
> CONTEXT: SQL statement "INSERT INTO _tmp_test1_changes (id, changes)
> VALUES (NEW.id, hstore(OLD) - hstore(NEW))"
>
> PL/pgSQL function test1_trigger() line 3 at SQL statement
>
>
>
> I couldn't find anything about this in the release notes (
> https://www.postgresql.org/docs/11/release-11.html) but maybe I just
> didn't know what to look for.
>
>
>
> *I doubt that this works on any PG version for INSERT.*
>
>
>
> *According to the documentation:*
>
>
>
> *https://www.postgresql.org/docs/10/plpgsql-trigger.html
> <https://www.postgresql.org/docs/10/plpgsql-trigger.html> and
> https://www.postgresql.org/docs/11/plpgsql-trigger.html
> <https://www.postgresql.org/docs/11/plpgsql-trigger.html>*
>
>
>
> *OLD: **Data type **RECORD**; variable holding the old database row for *
> *UPDATE**/**DELETE** operations in row-level triggers. This variable is
> unassigned in statement-level triggers and for **INSERT** operations.*
>
>
>
> *Regards*
>
> *Charles*
>