Thread: 8.3 PL/pgSQL comparing arbitrary records

8.3 PL/pgSQL comparing arbitrary records

From
Josh Kupershmidt
Date:
Hi all,

Short version of my question:
What's the best way to compare arbitrary records (OLD and NEW, in my
case) using PL/pgSQL in Postgres 8.3, without knowing anything about
the structure of the records? If the answer is to cast OLD and NEW to
text, and then compare, as suggested in [1], what false equalities
could be produced, assuming the records have identical structure and
NULLs are allowed?

Long version:
I am porting a trigger function written in PL/Python to PL/pgSQL. The
function, called track_updates() is declared as a BEFORE INSERT OR
UPDATE trigger on many tables in our database; simplifying a bit, its
job is to bump up an "updated" timestamp whenever a row has been
modified.

This simple plpython snippet tests whether the old and new rows are
identical (i.e. an update with no effect), and ignores if so:

if TD["new"] == TD["old"]:
    return "SKIP"

I'd like to perform a similar test in plpgsql, but the straightforward:

IF NEW IS NOT DISTINCT FROM OLD THEN
    RETURN NEW;

doesn't work -- I get:

    ERROR:  operator does not exist: [my table name] = [my table name]
    LINE 1: SELECT   $1  IS NOT DISTINCT FROM  $2
    HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.

According to discussion[1] ("8.3 PLpgSQL Can't Compare Records?") a
few months ago, the workaround for < 8.4 is to cast OLD and NEW to
text, and then compare the two text values. A comment there suggests
that comparing NULL and the empty string in this way might incorrectly
result in a true equality test, which is a little worrying for my
purposes. However, I'm unable to reproduce NULL and '' equating to
each other when cast to text (see example code below). Are there any
false equalities or other gotchas I should be worried about when
comparing OLD::text and NEW::text? I can safely assume for my purposes
that the old and new records will have the same structure (i.e. no
ALTER TABLEs to worry about).

I'm using Postgres 8.3.4, compiled from source on Linux.

Thanks,
Josh


CREATE TEMPORARY TABLE test_trg (
        colA  int,
        colB  text,
        colC  text,
        updated timestamp with time zone NOT NULL
) ON COMMIT DROP;

CREATE OR REPLACE FUNCTION pg_temp.track_updates()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
    IF TG_OP = 'UPDATE' THEN
        IF NEW::text IS NOT DISTINCT FROM OLD::text THEN
            RAISE NOTICE 'OLD and NEW are the same!';
        ELSE
            RAISE NOTICE 'OLD and NEW are different!';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER "track_updates_trg"
BEFORE INSERT OR UPDATE ON "pg_temp"."test_trg"
FOR EACH ROW EXECUTE PROCEDURE pg_temp.track_updates();

INSERT INTO pg_temp.test_trg (colA, colB, colC, updated)
VALUES (1, '', NULL, CURRENT_TIMESTAMP);

-- Each of these UPDATEs say 'OLD and NEW are different!'
UPDATE test_trg SET colB = NULL;
UPDATE test_trg SET colC = '';
UPDATE test_trg SET colC = NULL;
UPDATE test_trg SET colB = '', colC = '';

--
Footnotes:
[1] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00040.php

Re: 8.3 PL/pgSQL comparing arbitrary records

From
Pavel Stehule
Date:
Hello

NEW and OLD is comparable in 8.4. In 8.3 and older you have to use
little bit different syntax

http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body

Regards
Pavel Stehule

2009/12/15 Josh Kupershmidt <schmiddy@gmail.com>:
> Hi all,
>
> Short version of my question:
> What's the best way to compare arbitrary records (OLD and NEW, in my
> case) using PL/pgSQL in Postgres 8.3, without knowing anything about
> the structure of the records? If the answer is to cast OLD and NEW to
> text, and then compare, as suggested in [1], what false equalities
> could be produced, assuming the records have identical structure and
> NULLs are allowed?
>
> Long version:
> I am porting a trigger function written in PL/Python to PL/pgSQL. The
> function, called track_updates() is declared as a BEFORE INSERT OR
> UPDATE trigger on many tables in our database; simplifying a bit, its
> job is to bump up an "updated" timestamp whenever a row has been
> modified.
>
> This simple plpython snippet tests whether the old and new rows are
> identical (i.e. an update with no effect), and ignores if so:
>
> if TD["new"] == TD["old"]:
>    return "SKIP"
>
> I'd like to perform a similar test in plpgsql, but the straightforward:
>
> IF NEW IS NOT DISTINCT FROM OLD THEN
>    RETURN NEW;
>
> doesn't work -- I get:
>
>    ERROR:  operator does not exist: [my table name] = [my table name]
>    LINE 1: SELECT   $1  IS NOT DISTINCT FROM  $2
>    HINT:  No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>
> According to discussion[1] ("8.3 PLpgSQL Can't Compare Records?") a
> few months ago, the workaround for < 8.4 is to cast OLD and NEW to
> text, and then compare the two text values. A comment there suggests
> that comparing NULL and the empty string in this way might incorrectly
> result in a true equality test, which is a little worrying for my
> purposes. However, I'm unable to reproduce NULL and '' equating to
> each other when cast to text (see example code below). Are there any
> false equalities or other gotchas I should be worried about when
> comparing OLD::text and NEW::text? I can safely assume for my purposes
> that the old and new records will have the same structure (i.e. no
> ALTER TABLEs to worry about).
>
> I'm using Postgres 8.3.4, compiled from source on Linux.
>
> Thanks,
> Josh
>
>
> CREATE TEMPORARY TABLE test_trg (
>        colA  int,
>        colB  text,
>        colC  text,
>        updated timestamp with time zone NOT NULL
> ) ON COMMIT DROP;
>
> CREATE OR REPLACE FUNCTION pg_temp.track_updates()
> RETURNS TRIGGER AS $$
> DECLARE
> BEGIN
>    IF TG_OP = 'UPDATE' THEN
>        IF NEW::text IS NOT DISTINCT FROM OLD::text THEN
>            RAISE NOTICE 'OLD and NEW are the same!';
>        ELSE
>            RAISE NOTICE 'OLD and NEW are different!';
>        END IF;
>    END IF;
>    RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER "track_updates_trg"
> BEFORE INSERT OR UPDATE ON "pg_temp"."test_trg"
> FOR EACH ROW EXECUTE PROCEDURE pg_temp.track_updates();
>
> INSERT INTO pg_temp.test_trg (colA, colB, colC, updated)
> VALUES (1, '', NULL, CURRENT_TIMESTAMP);
>
> -- Each of these UPDATEs say 'OLD and NEW are different!'
> UPDATE test_trg SET colB = NULL;
> UPDATE test_trg SET colC = '';
> UPDATE test_trg SET colC = NULL;
> UPDATE test_trg SET colB = '', colC = '';
>
> --
> Footnotes:
> [1] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00040.php
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: 8.3 PL/pgSQL comparing arbitrary records

From
Alexander Pyhalov
Date:
Hello.
It seems that this works (I made tests on permanent table, postgresql
8.4.0):

CREATE OR REPLACE FUNCTION track_updates()
RETURNS TRIGGER AS $$
DECLARE
BEGIN
     IF TG_OP = 'UPDATE' THEN
         IF NEW IS NOT DISTINCT FROM OLD THEN
             RAISE NOTICE 'OLD and NEW are the same!';
         ELSE
             RAISE NOTICE 'OLD and NEW are different!';
         END IF;
     END IF;
     RETURN NEW;
END;
$$ LANGUAGE plpgsql;


 > update test_trg set cola=1 where cola=1;
NOTICE:  OLD and NEW are the same!

 > update test_trg set cola=2 where cola=1;
NOTICE:  OLD and NEW are different!

Josh Kupershmidt wrote:
> Hi all,
>
> Short version of my question:
> What's the best way to compare arbitrary records (OLD and NEW, in my
> case) using PL/pgSQL in Postgres 8.3, without knowing anything about
> the structure of the records? If the answer is to cast OLD and NEW to
> text, and then compare, as suggested in [1], what false equalities
> could be produced, assuming the records have identical structure and
> NULLs are allowed?
>
> Long version:
> I am porting a trigger function written in PL/Python to PL/pgSQL. The
> function, called track_updates() is declared as a BEFORE INSERT OR
> UPDATE trigger on many tables in our database; simplifying a bit, its
> job is to bump up an "updated" timestamp whenever a row has been
> modified.
>
> This simple plpython snippet tests whether the old and new rows are
> identical (i.e. an update with no effect), and ignores if so:
>
> if TD["new"] == TD["old"]:
>     return "SKIP"
>
> I'd like to perform a similar test in plpgsql, but the straightforward:
>
> IF NEW IS NOT DISTINCT FROM OLD THEN
>     RETURN NEW;
>
> doesn't work -- I get:
>
>     ERROR:  operator does not exist: [my table name] = [my table name]
>     LINE 1: SELECT   $1  IS NOT DISTINCT FROM  $2
>     HINT:  No operator matches the given name and argument type(s).
> You might need to add explicit type casts.
>
> According to discussion[1] ("8.3 PLpgSQL Can't Compare Records?") a
> few months ago, the workaround for < 8.4 is to cast OLD and NEW to
> text, and then compare the two text values. A comment there suggests
> that comparing NULL and the empty string in this way might incorrectly
> result in a true equality test, which is a little worrying for my
> purposes. However, I'm unable to reproduce NULL and '' equating to
> each other when cast to text (see example code below). Are there any
> false equalities or other gotchas I should be worried about when
> comparing OLD::text and NEW::text? I can safely assume for my purposes
> that the old and new records will have the same structure (i.e. no
> ALTER TABLEs to worry about).
>
> I'm using Postgres 8.3.4, compiled from source on Linux.
>
> Thanks,
> Josh
>
>
> CREATE TEMPORARY TABLE test_trg (
>         colA  int,
>         colB  text,
>         colC  text,
>         updated timestamp with time zone NOT NULL
> ) ON COMMIT DROP;
>
> CREATE OR REPLACE FUNCTION pg_temp.track_updates()
> RETURNS TRIGGER AS $$
> DECLARE
> BEGIN
>     IF TG_OP = 'UPDATE' THEN
>         IF NEW::text IS NOT DISTINCT FROM OLD::text THEN
>             RAISE NOTICE 'OLD and NEW are the same!';
>         ELSE
>             RAISE NOTICE 'OLD and NEW are different!';
>         END IF;
>     END IF;
>     RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER "track_updates_trg"
> BEFORE INSERT OR UPDATE ON "pg_temp"."test_trg"
> FOR EACH ROW EXECUTE PROCEDURE pg_temp.track_updates();
>
> INSERT INTO pg_temp.test_trg (colA, colB, colC, updated)
> VALUES (1, '', NULL, CURRENT_TIMESTAMP);
>
> -- Each of these UPDATEs say 'OLD and NEW are different!'
> UPDATE test_trg SET colB = NULL;
> UPDATE test_trg SET colC = '';
> UPDATE test_trg SET colC = NULL;
> UPDATE test_trg SET colB = '', colC = '';
>
> --
> Footnotes:
> [1] http://archives.postgresql.org/pgsql-hackers/2009-07/msg00040.php
>


--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.


Re: 8.3 PL/pgSQL comparing arbitrary records

From
Josh Kupershmidt
Date:
On Tue, Dec 15, 2009 at 1:23 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> NEW and OLD is comparable in 8.4. In 8.3 and older you have to use
> little bit different syntax
>
> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Fast_compare_variables_NEW_and_OLD_in_trigger.27s_body
>

Thanks, Pavel! This is exactly what I was looking for. I am able to use

IF ROW(NEW.*) IS NOT DISTINCT FROM ROW(OLD.*) THEN ...

on 8.3.

Josh