CODEWITHSUNDEEP
postgresqltransactions
Dónal
Dónal

Reputation: 187499

make statements in postgres function behave atomically

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;

Upvotes: 0

Views: 231

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 1

Frank Heikens
Frank Heikens

Reputation: 126970

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.

Upvotes: 0

Related Questions