0

Assume I created a function that I execute in REPEATABLE_READ isolation level in PostgresSQL, e.g.

CREATE FUNCTION some_stuff() RETURNS void AS $$ BEGIN -- do stuff that could throw an error END; $$ LANGUAGE plpgsql;

It's possible for this function internally to throw the following error: ERROR: could not serialize access due to concurrent update. Is there a way to catch this error and repeat the function/transaction internally? So have something like this:

CREATE FUNCTION some_stuff() RETURNS void AS $$ BEGIN try { -- do stuff that could throw an error } catch (error) { call some_stuff(); } END; $$ LANGUAGE plpgsql;

Or has this error catching to be done in the application level, i.e. in Java that is calling this function?

Cœur
  • 37,241
  • 25
  • 195
  • 267
insumity
  • 5,311
  • 8
  • 36
  • 64

1 Answers1

1

It's possible for this function internally to throw the following error: ERROR: could not serialize access due to concurrent update. Is there a way to catch this error and repeat the function/transaction internally?

No, it is not.

PostgreSQL's functions are implicitly wrapped in a transaction if one isn't already in progress. There is no way to obtain a new transaction snapshot from within a function in repeatable read or serializable isolation. You can't rollback and begin a new transaction.

For this to work PostgreSQL would need to support top-level procedures with autonomous commit.

You must have the controlling code on a separate connection. This means doing it from a client, or (ab)using dblink for the purpose.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • about this: `are implicitly wrapped in a transaction if one isn't already in progress.`. Does this mean that if we have two functions `A` and `B` and function `A` internally calls `B` then there is only one transaction (created for the execution of `A`) and that the execution of `B` (when called by `A`) is being done inside this single transaction? – insumity Oct 05 '14 at 13:43
  • 1
    @foobar Correct. Nested transactions are possible (begin/except blocks, subtransactions) but not autonomous commit or transaction suspend/resume. – Craig Ringer Oct 05 '14 at 14:13