7.2.1 PL/pgSQL issue - Mailing list pgsql-general

From Ed Loehr
Subject 7.2.1 PL/pgSQL issue
Date
Msg-id 3CB67D5C.3010308@bluepolka.net
Whole thread Raw
Responses Re: 7.2.1 PL/pgSQL issue
List pgsql-general
This looks like a bug.  Only question is whose.  The triggered update of a
timestamp field is not working.  I can see the expected outputs in the log,
but the timestamp update is not sticking.  Can someone identify my error or
test this on 7.2.1 and tell me if the bug is mine (most likely) or a bug in
PL/pgSQL?

-- Simple function to update the 'updated' column to the current time
CREATE FUNCTION set_update_time() RETURNS OPAQUE AS
'BEGIN
     NEW.updated := ''now'';
     RAISE NOTICE ''Resetting %.updated to %'', TG_RELNAME, NEW.updated;
     RETURN NEW;
END;'
LANGUAGE 'plpgsql';

CREATE TABLE plpgsql_test (
     id      SERIAL,
     created TIMESTAMP NOT NULL DEFAULT now(),
     updated TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TRIGGER plpgsql_test_trigger
    AFTER UPDATE ON plpgsql_test FOR EACH ROW
    EXECUTE PROCEDURE set_update_time();

-- Insert 1 and note the timestamps...
INSERT INTO plpgsql_test (id) VALUES (0);
SELECT * FROM plpgsql_test;

-- Wait a few seconds, then...

UPDATE plpgsql_test SET id = (SELECT nextval('plpgsql_test_id_seq'));

-- This should show an updated value for
-- plpgsql_test.updated, but does not on my system...
SELECT * FROM plpgsql_test;


-- Clean-up...
DROP FUNCTION set_update_time();
DROP SEQUENCE plpgsql_test_id_seq;
DROP TABLE plpgsql_test;

template1=# select version();
                            version
-------------------------------------------------------------
  PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)


pgsql-general by date:

Previous
From: Oleg Bartunov
Date:
Subject: Re: Critical performance problems on large databases
Next
From: Markus Wagner
Date:
Subject: how to get more detailed error messages?