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

On 1/4/19 4:20 AM, Kristjan Tammekivi wrote:
> 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.

I can confirm:

select version();
version

------------------------------------------------------------------------------------
PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.5, 64-bit

INSERT INTO _tmp_test1 (val) VALUES (5);
INSERT 0 1

select * from _tmp_test1_changes ;
id | changes
----+-------------------------
1 | "id"=>NULL, "val"=>NULL
(1 row)

I would file a bug report:

https://www.postgresql.org/account/submitbug/

>
> On Fri, Jan 4, 2019 at 12:56 PM Charles Clavadetscher
> <clavadetscher(at)swisspug(dot)org <mailto:clavadetscher(at)swisspug(dot)org>> wrote:
>
> Hello____
>
> __ __
>
> *From:*Kristjan Tammekivi <kristjantammekivi(at)gmail(dot)com
> <mailto:kristjantammekivi(at)gmail(dot)com>>
> *Sent:* Freitag, 4. Januar 2019 11:46
> *To:* pgsql-general(at)postgresql(dot)org <mailto: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 and
> 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____*
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com