make statements in postgres function behave atomically

Question

I have the following postgres function

CREATE OR REPLACE FUNCTION refresh_materialized_view(name)
  RETURNS integer AS
$BODY$
DECLARE
  _table_name ALIAS FOR $1;
  _entry materialized_views%ROWTYPE;
  _result INT;
BEGIN

  SELECT * INTO _entry FROM materialized_views WHERE table_name = _table_name;

  BEGIN;
    EXECUTE 'CREATE TEMP TABLE new_materialized_view_rows ON COMMIT DROP AS SELECT * FROM ' || _entry.view_name;
    EXECUTE 'TRUNCATE TABLE ' || _table_name;
    EXECUTE 'INSERT INTO ' || _table_name || ' SELECT * FROM new_materialized_view_rows';

    UPDATE materialized_views
    SET    last_refresh = CURRENT_TIMESTAMP
    WHERE  table_name = _table_name;
  COMMIT;

  EXECUTE 'ANALYZE ' || table_name; 
  RETURN 1;
END
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

ALTER FUNCTION refresh_materialized_view(name) OWNER TO portal;

The function is called from a non-transactional context, so I've enclosed the statements that update data with

BEGIN; 

COMMIT;

so that these statements execute atomically. However, when I execute the script above I get the following error:

ERROR:  syntax error at or near ";"
LINE 16:   BEGIN;

user330315 · Accepted Answer · 2012-10-08 10:16:00Z

1

A function is always part of the calling transaction. You cannot commit the transaction in a function. So you will need to do the following in the calling code:

begin;
select efresh_materialized_view('foobar');
commit;

"The function is called from a non-transactional context"
That is not possible. You cannot work without a transaction in PostgreSQL. You are probably referring to the "autocommit" mode which implicitely commits every statement - but that is still transactional.

answered Oct 8, 2012 at 10:16

user330315

Sign up to request clarification or add additional context in comments.

Comments

Frank Heikens · Accepted Answer · 2012-10-08 10:15:23Z

0

You can't use COMMIT within a function. The function is transactional anyway, that's why you don't need the second BEGIN; as well. This is your bug as well, this is where it fails.

answered Oct 8, 2012 at 10:15

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Collectives™ on Stack Overflow

make statements in postgres function behave atomically

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related