Thread: Detecting changes to certain fields in 'before update' trigger functions

Hi list,

First steps in trigger functions and PL/pgSQL so please bear with me...

How can one detect changes to certain fields in before update trigger
functions?

 IF (NEW.<column-name> != OLD.<column-name>) THEN ...

doesn't work, so obviously my understanding of the values of the
varriables NEW and OLD in before update trigger functions is wrong; I
had thought that OLD holds the record as it was before the update, and
that NEW holds the record as it is since the update (but before the
update has been committed)?

How should one go about detecting changes to certain fields in before
update trigger functions?

Any help/advice much appreciated.

Sebastian

On Monday 01 December 2008 7:18:51 am Sebastian Tennant wrote:
> Hi list,
>
> First steps in trigger functions and PL/pgSQL so please bear with me...
>
> How can one detect changes to certain fields in before update trigger
> functions?
>
>  IF (NEW.<column-name> != OLD.<column-name>) THEN ...
>
> doesn't work, so obviously my understanding of the values of the
> varriables NEW and OLD in before update trigger functions is wrong; I
> had thought that OLD holds the record as it was before the update, and
> that NEW holds the record as it is since the update (but before the
> update has been committed)?
>
> How should one go about detecting changes to certain fields in before
> update trigger functions?
>
> Any help/advice much appreciated.
>
> Sebastian

It works here. Can you be more specific? Full function code, table schema,etc.


Thanks,
--
Adrian Klaver
aklaver@comcast.net

On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant
<sebyte@smolny.plus.com> wrote:

>  IF (NEW.<column-name> != OLD.<column-name>) THEN ...

 The != operator doesn't work the way you might think when nulls are
thrown into the mix.  I asked a similar question a while back and was
kindly pointed to the following syntax:

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


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Quoth Adrian Klaver <aklaver@comcast.net>:
> On Monday 01 December 2008 7:18:51 am Sebastian Tennant wrote:
>> I had thought that OLD holds the record as it was before the update,
>> and that NEW holds the record as it is since the update (but before
>> the update has been committed)?

'42.10 Trigger Procedures' seems to confirm this:

"`NEW'
      Data type `RECORD'; variable holding the new database row for
      `INSERT'/`UPDATE' operations in row-level triggers. This variable
      is `NULL' in statement-level triggers.

 `OLD'
      Data type `RECORD'; variable holding the old database row for
      `UPDATE'/`DELETE' operations in row-level triggers. This variable
      is `NULL' in statement-level triggers."

> It works here. Can you be more specific? Full function code, table schema,etc.

Of course.

######## timestamper.sql starts here ########
 -- \i ./timestamper.sql

 DROP TABLE IF EXISTS tt;
 CREATE TEMP TABLE tt (username character varying(12),
                       delisted boolean,
                       created_at timestamp(0) without time zone,
                       updated_at timestamp(0) without time zone,
                       delisted_at timestamp(0) without time zone);

 CREATE OR REPLACE FUNCTION timestamper() RETURNS TRIGGER AS $$
   BEGIN
   IF (TG_OP = 'INSERT') THEN NEW.created_at := current_timestamp(0); END IF;
   IF (TG_OP = 'UPDATE') THEN
     NEW.updated_at := current_timestamp(0);
     IF ((NEW.delisted = true) AND (NEW.delisted != OLD.delisted)) THEN
       NEW.delisted_at := current_timestamp(0); END IF;
     END IF;
   RETURN NEW;
   END;
 $$ LANGUAGE plpgsql;

 CREATE TRIGGER timestamper_before_insert BEFORE INSERT ON tt FOR EACH ROW
   EXECUTE PROCEDURE timestamper();

 CREATE TRIGGER timestamper_before_update BEFORE UPDATE ON tt FOR EACH ROW
   EXECUTE PROCEDURE timestamper();

 -- DROP FUNCTION timestamper() CASCADE;
 -- no need to drop temporary tables

######## timesatmper.sql ends here ########

 testdb=> \i ./timestamper.sql
 DROP TABLE
 CREATE TABLE
 CREATE FUNCTION
 CREATE TRIGGER
 CREATE TRIGGER
 testdb=> insert into tt values (foo');
 INSERT 0 1
 testdb=> select * from tt;
 -[ RECORD 1 ]--------------------
 username    | foo
 delisted    |
 created_at  | 2008-12-01 16:17:37
 updated_at  |
 delisted_at |

 testdb=> update tt set username=bar';
 UPDATE 1
 testdb=> select * from tt;
 -[ RECORD 1 ]--------------------
 username    | bar
 delisted    |
 created_at  | 2008-12-01 16:17:37
 updated_at  | 2008-12-01 16:18:27
 delisted_at |

 testdb=> update tt set delisted=true where username='bar';
 UPDATE 1
 testdb=> select * from tt;
 -[ RECORD 1 ]--------------------
 username    | bar
 delisted    | t
 created_at  | 2008-12-01 16:17:37
 updated_at  | 2008-12-01 16:19:01
 delisted_at |


The triggers for the initial insert and the first update do what I want
them to, but the second update (that marks 'foo' as delisted) fails to
update the delisted_at timestamp.

Sebastian

Quoth "Richard Broersma" <richard.broersma@gmail.com>:
> On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant
> <sebyte@smolny.plus.com> wrote:
>
>>  IF (NEW.<column-name> != OLD.<column-name>) THEN ...
>
>  The != operator doesn't work the way you might think when nulls are
> thrown into the mix.  I asked a similar question a while back and was
> kindly pointed to the following syntax:
>
> IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ...

That's it!  Thanks very much Richard.

I sometimes think this kind of gotcha is purposely buried, or not
addressed at all, in order to force users to read the manual.  I wasn't
planning on spending four hours doing just that, but now I suppose I'm
almost glad I did.

Sebastian

On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant
<sebyte@smolny.plus.com> wrote:
>>>  IF (NEW.<column-name> != OLD.<column-name>) THEN ...
>>
>> IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ...
>
> I sometimes think this kind of gotcha is purposely buried, or not
> addressed at all, in order to force users to read the manual.

I wouldn't say it is intentionally buried.  I would say that the
PostgreSQL manual focuses primarily is on "What are the PG features".
 While the manual may at times document some of the good/best
practices to use by combining various PG features,  I wouldn't say
that its intention isn't to be an authoritative source on "How to use
PG features."

On the other hand, there are many ANSI-SQL books that focus on good
practices.   For example, the need for the "IS DISTINCT FROM" when
dealing with nulls would be discussed in an SQL book.  Once you have
the theory down, you can turn to the PostgreSQL manual to find out how
PostgreSQL implements this functionality.


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Sebastian Tennant <sebyte@smolny.plus.com> writes:
> I sometimes think this kind of gotcha is purposely buried, or not
> addressed at all, in order to force users to read the manual.

Where exactly do you think we should document it, if not in the manual?
In any case it's SQL-standard behavior that any book about SQL will
tell you.

            regards, tom lane

If my perusal of the sql generated by pg_dump is correct, then it
doesn't appear that it's wrapped in a transaction, and thus might be
able to only complete a partial restore?

Or does

psql myDatabase <mypg_dumpfile

wrap the file stream in a transaction?

If not, is there a reason why it can't be done so (some process that
cannot be run as a transaction inside the file)?

Or should I just add begin and commit statements at the beginning and
end of file?

I want to provide a mechanized daily update of one schema into a
differently named database, and I'd like it to rollback if if fails.

-Owen

Owen Hartnett <owen@clipboardinc.com> writes:
> If my perusal of the sql generated by pg_dump is correct, then it
> doesn't appear that it's wrapped in a transaction, and thus might be
> able to only complete a partial restore?

That's correct, and intentional.  You can use pg_restore's -1 switch
or add begin/end manually if you don't want it to work that way.

            regards, tom lane

Owen Hartnett wrote:

> If my perusal of the sql generated by pg_dump is correct, then it
> doesn't appear that it's wrapped in a transaction, and thus might be
> able to only complete a partial restore?

You're right, it is not.  Try pg_restore --single-transaction.  (You'll
need pg_dump -Fc though.)


--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Quoting Owen Hartnett <owen@clipboardinc.com>:

>
> If my perusal of the sql generated by pg_dump is correct, then it
> doesn't appear that it's wrapped in a transaction, and thus might be
> able to only complete a partial restore?
>
> Or does
>
> psql myDatabase <mypg_dumpfile
>

Try to use pg_restore with the following option:

  -1, --single-transaction
                           restore as a single transaction

Or psql with the following option:

  -1 ("one")      execute command file as a single transaction

Kind regards,

Peter

At 12:37 PM -0500 12/1/08, Tom Lane wrote:
>Owen Hartnett <owen@clipboardinc.com> writes:
>>  If my perusal of the sql generated by pg_dump is correct, then it
>>  doesn't appear that it's wrapped in a transaction, and thus might be
>>  able to only complete a partial restore?
>
>That's correct, and intentional.  You can use pg_restore's -1 switch
>or add begin/end manually if you don't want it to work that way.
>
>            regards, tom lane

Thanks to everybody for their help.  You guys are great.

-Owen

Quoth "Richard Broersma" <richard.broersma@gmail.com>:
> On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant
> <sebyte@smolny.plus.com> wrote:
>>>>  IF (NEW.<column-name> != OLD.<column-name>) THEN ...
>>>
>>> IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ...
>>>
>> I sometimes think this kind of gotcha is purposely buried, or not
>> addressed at all, in order to force users to read the manual.
>
> I wouldn't say it is intentionally buried.  I would say that the
> PostgreSQL manual focuses primarily is on "What are the PG features".
> While the manual may at times document some of the good/best practices
> to use by combining various PG features, I wouldn't say that its
> intention isn't to be an authoritative source on "How to use PG
> features."
>
> On the other hand, there are many ANSI-SQL books that focus on good
> practices.   For example, the need for the "IS DISTINCT FROM" when
> dealing with nulls would be discussed in an SQL book.  Once you have
> the theory down, you can turn to the PostgreSQL manual to find out how
> PostgreSQL implements this functionality.

That's sound advice and I take your point about the manual focussing on
Postgre features rather than SQL per se.  I have read one or two SQL
books but I'm very much a learn by doing person... and the fact is, I
haven't done much doing, until now.

May I wriggle out a little by saying that I didn't really mean what I
said, or rather, I failed to say what I really meant; that it sometimes
feels as if a gotcha has been buried in order to make you read the
manual.

Sebastian

Quoth Tom Lane <tgl@sss.pgh.pa.us>:
> Sebastian Tennant <sebyte@smolny.plus.com> writes:
>> I sometimes think this kind of gotcha is purposely buried, or not
>> addressed at all, in order to force users to read the manual.
>
> Where exactly do you think we should document it, if not in the
> manual?

I clearly didn't express myself very well.  Let me set the record
straight by saying that my experience with PostgreSQL over the past
three months or so has been fantastic, thanks in no small part to the
clear and comprehensive accompanying manual.

All I meant was that it sometimes _feels_ as if a vital piece of
information has been buried in the manual in order to make you read it.

(I wasn't making a serious point and I didn't expect it to be taken
literally).

Sebastian

P.S.  Emacs users of PostgreSQL might like to know that there's a
      texinfo version of the manual (version 8.3.3) available for
      download from here:

        http://www.emacswiki.org/PostGreSQL

Sebastian Tennant wrote:

> P.S.  Emacs users of PostgreSQL might like to know that there's a
>       texinfo version of the manual (version 8.3.3) available for
>       download from here:
>
>         http://www.emacswiki.org/PostGreSQL

Hmm, we did have a patch to add a texinfo target to the docs Makefile ...
apparently it was never applied.  Maybe that's a good idea?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Quoth Alvaro Herrera <alvherre@commandprompt.com>:
> Sebastian Tennant wrote:
>
>> P.S.  Emacs users of PostgreSQL might like to know that there's a
>>       texinfo version of the manual (version 8.3.3) available for
>>       download from here:
>>
>>         http://www.emacswiki.org/PostGreSQL
>
> Hmm, we did have a patch to add a texinfo target to the docs Makefile ...
> apparently it was never applied.  Maybe that's a good idea?

It's a very good idea IMHO.

I would love to see a texinfo target in the docs Makefile.  Nothing
beats Info for convenience.

Sebastian

Sebastian Tennant wrote:
> Quoth Alvaro Herrera <alvherre@commandprompt.com>:
> > Sebastian Tennant wrote:
> >
> >> P.S.  Emacs users of PostgreSQL might like to know that there's a
> >>       texinfo version of the manual (version 8.3.3) available for
> >>       download from here:
> >>
> >>         http://www.emacswiki.org/PostGreSQL
> >
> > Hmm, we did have a patch to add a texinfo target to the docs Makefile ...
> > apparently it was never applied.  Maybe that's a good idea?
>
> It's a very good idea IMHO.

Hmm, actually now that I look closer, it is there (make postgres.info
does the trick).  The build process throws a worrying number of warnings
though.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Quoth Alvaro Herrera <alvherre@commandprompt.com>:
> Sebastian Tennant wrote:
>> Quoth Alvaro Herrera <alvherre@commandprompt.com>:
>> > Sebastian Tennant wrote:
>> >
>> >> P.S.  Emacs users of PostgreSQL might like to know that there's a
>> >>       texinfo version of the manual (version 8.3.3) available for
>> >>       download from here:
>> >>
>> >>         http://www.emacswiki.org/PostGreSQL
>> >
>> > Hmm, we did have a patch to add a texinfo target to the docs Makefile ...
>> > apparently it was never applied.  Maybe that's a good idea?
>>
>> It's a very good idea IMHO.
>
> Hmm, actually now that I look closer, it is there (make postgres.info
> does the trick).  The build process throws a worrying number of warnings
> though.

Warnings are better than errors :-)

I'll download the source and have a go myself.

Many thanks Alvaro.

Sebastian