SQL enhancements v17

EDB Postgres Extended Server includes a number of SQL enhancements.

Rollback options

In PostgreSQL, any error in a transaction rolls back all actions by that transaction. This behavior is different from other DBMS, such as Oracle and SQL Server, where an error causes rollback of only the last statement. This difference in transaction handling semantics doesn't cause a problem in all cases, but it does make implementing business logic in PostgreSQL difficult for Oracle Database and Microsoft SQL Server developers.

One workaround is to manually introduce a savepoint, internally known as subtransactions, into the application code. This is time consuming and difficult to test. A savepoint is an additional statement and therefore increases transaction latency. Given the overhead of additional development work and slower performance, this approach isn't viable in most cases.

EDB Postgres Extended Server allows you to roll back just the current statement. The statement-level rollback feature provides an optional mode to choose whether to allow rollback of the whole transaction or just the current statement. No manual recoding is required. There's some added overhead, but it's lower than for a savepoint.

See transaction_rollback_scope for information on setting the transaction rollback scope inside the database and JDBC properties for rollback scope for information on continuing past an error on a JDBC batch job.

Cursors with prepared statements

EDB Postgres Extended Server allows declaring a cursor over a previously created prepared statement.

For example:

PREPARE foo AS ...; DECLARE c1 CURSOR FOR foo;

PL/pgSQL compatibility

EDB Postgres Extended Server integrates with other migration tools with a number of PL/pgSQL compatibility features.

For general simplicity, EDB Postgres Extended Server allows calling functions using plpqsl without the PERFORM keyword.

For example,

BEGIN somefunc(); END

Where somefunc is not a keyword.