You cannot use ROLLBACK in PL/pgSQL, except in certain limited cases inside procedures.

You don't need to explicitly roll back in your PL/pgSQL code. Just let the exception propagate out of the PL/pgSQL code, and it will cause an error, which will cause the whole transaction to be rolled back.

Your comments suggest that this code is called from an SQL script. Then the solution would be to have a COMMIT in that SQL script at some place after the PL/pgSQL code. That would end the transaction and roll it back.

I tested this question on a Postgres database (version 12.44). I created the file test.sql (same as in the question):

BEGIN;
SELECT
    *
FROM
    intentional_error;
CREATE TABLE faketable (fakecolumn TIMESTAMP);
COMMIT;

This was the output from CLI.

-d postgres -f test.sql -W
Password: 
BEGIN
psql:test.sql:5: ERROR:  relation "intentional_error" does not exist
LINE 4:     intentional_error;
            ^
psql:test.sql:6: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

Then I double-checked that the table wasn't created.

$ psql -h my-host -U postgres -d postgres -W
Password: 
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1), server 12.14)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off)
Type "help" for help.

postgres=> \d faketable
Did not find any relation named "faketable".

In conclusion, putting BEGIN; at the start of the script and COMMIT; at the end of the script is sufficient to ROLLBACK; the transaction in the case that one of the statements errors.

The transaction won't be "auto rolled back", it will be marked as failed, and the only option you have, is to do a rollback.

If you did not run rollback in the function but simply return from it in case of an error, then the UPDATE following the function call would result in "current transaction is aborted, commands ignored until end of transaction block" - so the UPDATE can't be run either (again: only if you omit the rollback in the function in case of an error).

Unrelated to your question, but: your UPDATE will (implicitly) run in a second (different) transaction if your insert succeeds because your function cleanly ends the transaction that was started in there. If you did mean to run insert and update in the same transaction, your code is not going to do that.

The whole procedure runs inside a single transaction automatically. Except, if you execute COMMIT, which commits everything so far and starts a new transaction.

So, no, you do not have to catch exceptions unless you want to. If an exception occurs (and is not caught), the whole transaction is rolled back automatically.

There are some limitations in which constellations a nested COMMIT is allowed. Read the manual about Transaction Management.

Be sure to know the difference between functions and procedures in Postgres:

• In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?
• When to use stored procedure / user-defined function?

This really is best understood by reading the manual:

https://www.postgresql.org/docs/current/static/tutorial-transactions.html

ROLLBACK TO is the only way to regain control of a transaction block that was put in aborted state by the system due to an error, short of rolling it back completely and starting again.

Rollback should not be able to fail.

Transactions are placed in an aborted state meaning you can't carry on doing anything in that transaction. You can use a save point to recover that transaction but other than that all you can do is rollback.

If an error occurs, PostgreSQL does not actually rollback the transaction. It fails every subsequent statements with an error. You can try this out in the client.

You need to execute rollback before any statements can be executed successfully.

In the case that you close the connection and start a new one, this is of little consequence. However, if you retain the connection and run other statements which you expect to execute successfully, it will not work.

The problem I have with your example is that you're talking about JDBC behaviour, but also using explicit "start transaction" etc commands, which seems a bit of a clash, since I'd expect you'd use JDBC's auto-commit mode to manage transactions.

If you are in auto-commit mode, then the two inserts will each be in their own transaction, and the throw of a SQLException for the first one will not affect the second.

If you are not in auto-commit mode, then an implicit "start transaction" is generated before the first insert, and the second insert cannot be processed until the transaction is rolled back. This behaviour is quite different from if you were executing the script with psql.

(JDBC does not specify whether drivers/connections should default to auto-commit on or off, you should always explicitly set it)

Postgresql treats any error processing a statement as immediately aborting the transaction-- essentially like the XACT_ABORT mode in SQL Server. The intent being that if you submit a sequence of commands as a transaction, each one is dependent on the previous ones, so the failure of any one invalidates all the subsequent ones.

If this isn't the behaviour you want inside a transaction, you need to surround the potentially-aborting updates with creating a savepoint, and rolling back to that savepoint in case of an error.

Beware of looking at very old discussions of behaviour (bugs over ten years old definitely count), as at some point in Postgresql's history, there was a session variable called autocommit, and the behaviour could have been quite different. That variable is gone now, replaced (as I understand it) with the concepts of the database or the JDBC driver automatically wrapping commands inside transactions (so in fact there is not really any such thing as non-transactional interaction with postgresql).

Here is what happens when you execute the script you suggest with psql:

steve@steve@[local] =# start transaction;
START TRANSACTION
steve@steve@[local] *=# create table test(id int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
steve@steve@[local] *=# insert into test values (1);
INSERT 0 1
steve@steve@[local] *=# commit;
COMMIT
steve@steve@[local] =# 
steve@steve@[local] =# -- Following statement throws a SQLException(duplicate key) in
steve@steve@[local] =# -- PG, SS and ORacle
steve@steve@[local] =# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
steve@steve@[local] =# 
steve@steve@[local] =# -- Following statement behaves differently for different DBMS:
steve@steve@[local] =# -- SS and OR: No error...statement runs fine
steve@steve@[local] =# -- PG: Another SQLException thrown...must rollback or commit
steve@steve@[local] =# insert into test values (99);
INSERT 0 1

In order to get the same behaviour as you wrote in the script, you'd have to turn off auto-commit before doing the insert- that stops the JDBC driver from issuing an implicit "start transaction" before it executes the next statement. If you put that implicitly-generated transaction into the psql script, it produces the error you describe:

steve@steve@[local] =# start transaction; -- generated by JDBC driver
START TRANSACTION
steve@steve@[local] *=# -- Following statement throws a SQLException(duplicate key) in
steve@steve@[local] *=# -- PG, SS and ORacle
steve@steve@[local] *=# insert into test values (1);
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (id)=(1) already exists.
steve@steve@[local] !=# 
steve@steve@[local] !=# -- Following statement behaves differently for different DBMS:
steve@steve@[local] !=# -- SS and OR: No error...statement runs fine
steve@steve@[local] !=# -- PG: Another SQLException thrown...must rollback or commit
steve@steve@[local] !=# insert into test values (99);
ERROR:  current transaction is aborted, commands ignored until end of transaction block

As an illustration of why this behaviour exists, consider what happens if I run the first transaction again. The intent is "create the table and populate it with a single row":

steve@steve@[local] =# start transaction;
START TRANSACTION
steve@steve@[local] *=# create table test(id int primary key);
ERROR:  relation "test" already exists
steve@steve@[local] !=# insert into test values (1);
ERROR:  current transaction is aborted, commands ignored until end of transaction block
steve@steve@[local] !=# commit;
ROLLBACK

So as soon as a problem is detected ("test" already exists), the remaining data manipulation isn't appropriate (the row already existed too, anyway)