1

We have the update query inside the transaction block with the exception block. Shown below:

do $$       
begin

    update dbo.myTable set name = 'Table Name' where id = 47;
    commit;

exception when others then 

       raise notice '% %', SQLERRM, SQLSTATE;
    rollback;

end; $$ 
language 'plpgsql';

But this will return an exception raised message which is cannot commit while a subtransaction is active 2D000. Any leads on this issue and how can we support this transaction behaviour?

Improve this question
7
  • 2
    You can't have transaction control inside a PL/pgSQL block. Also: Postgres 9.3 is no longer supported you should plan an upgrade as soon as possible. Commented Sep 28, 2022 at 7:37
  • Any clue, on how we can handle this query syntax? any way out? Commented Sep 28, 2022 at 7:38
  • 1
    You don't need a PL/pgSQL block anyway. Just run the update, if an error occurs you will be forced to rollback anyway. Commented Sep 28, 2022 at 7:41
  • You're trying to implement default behavior in using pretty complex code, code that is not supported this way. Drop all your code and just keep the UPDATE, and it works. Check the manual: postgresql.org/docs/current/transaction-iso.html Commented Sep 28, 2022 at 8:09
  • there are multiple update queries in this begin block... for the sake of simplicity I have mentioned only one update query. So it may happen that one update succeeds and the second would get failed. Commented Sep 28, 2022 at 9:02
Related questions
2
Subtransactions in functions that can commit
0
ERROR: Transaction not found
1
Postgresql "no transaction in progress"
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.