UPDATE runs slow in a transaction - Mailing list pgsql-general

Hi,

the script below runs very fast when executed alone.  But when I call
it from within a transaction block it's so slow that I have to abort
it after a while.  Specifically the second-to-last UPDATE seems to
take forever within a transaction while it completes in about 3
seconds outside a transaction.

The Postgres manual states that statements should ran faster within a
transaction.

I wonder if I have to up a configuration parameter to give Postgres
more resources or if the problem lies elsewhere.

Thanks for your help,
Viktor

The script:

-- add columns left_token, right_token and copy values from token_index
ALTER TABLE _struct ADD left_token integer;
ALTER TABLE _struct ADD right_token integer;
UPDATE _struct SET left_token = token_index;
UPDATE _struct SET right_token = token_index;

-- set left, right values for non-terminals
-- (use temporary table to get rid of joins between struct and rank)
CREATE TABLE tmp AS SELECT r.pre, r.post, s.id, s.left_token,
s.right_token FROM _rank r, _struct s WHERE r.struct_ref = s.id;
CREATE INDEX idx_tmp_pre_post ON tmp (pre, post);
UPDATE tmp SET left_token = (SELECT min(t2.left_token) FROM tmp t2
WHERE t2.pre >= tmp.pre AND t2.pre <= tmp.post);
UPDATE tmp SET right_token = (SELECT max(t2.right_token) FROM tmp t2
WHERE t2.pre >= tmp.pre AND t2.pre <= tmp.post);

-- copy left, right values for everything
CREATE INDEX tmp_id ON tmp (id);
UPDATE _struct SET left_token = (SELECT DISTINCT left_token FROM tmp
WHERE _struct.id = tmp.id);
-- the UPDATE above takes ages when called within a transaction
UPDATE _struct SET right_token = (SELECT DISTINCT right_token FROM tmp
WHERE _struct.id = tmp.id);

-- clean up
DROP TABLE tmp;