Re: plperl %_SHARED and rollbacks

[Michael Fuhr <mike@xxxxxxxx>]

On Wed, Mar 08, 2006 at 09:06:36AM -0500, Kenneth Downs wrote:
> If there are triggers writing to %_SHARED within a transaction, and the 
> transaction is rolled back, do the changes to %_SHARED roll back also?  

What happened when you tried it?

CREATE TABLE foo (id integer, t text, last_t text);

CREATE FUNCTION trigfunc() RETURNS trigger AS $$
  $_TD->{new}{last_t} = $_SHARED{last_t};
  $_SHARED{last_t} = $_TD->{new}{t};
  return "MODIFY";
$$ LANGUAGE plperl;

CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo
  FOR EACH ROW EXECUTE PROCEDURE trigfunc();

INSERT INTO foo (id, t) VALUES (1, 'one');
INSERT INTO foo (id, t) VALUES (2, 'two');
BEGIN; INSERT INTO foo (id, t) VALUES (3, 'three'); ROLLBACK;
INSERT INTO foo (id, t) VALUES (4, 'four');

SELECT * FROM foo;
 id |  t   | last_t 
----+------+--------
  1 | one  | 
  2 | two  | one
  4 | four | three
(3 rows)

Notice that the value assigned in the rolled back transaction was
used in the subsequent insert.

> If not then I assume I should manually clear it at the start of 
> transactions, no?

Apparently so.

-- 
Michael Fuhr