PostgreSQL 14 Breaks the .NET and Java Drivers for PostgreSQL

MMS Founder
MMS Jonathan Allen

Article originally posted on InfoQ. Visit InfoQ

In some circumstances, the new syntax in PostgreSQL 14 will break its official .NET and Java database driver. Specifically, when using either to create a SQL function using BEGIN ATOMIC … END. If you are not modifying your database schema via Npgsql or PgJDBC, there’s no need to worry.

Java’s JDBC and .NET’s ADO.NET database driver frameworks have a common feature: they both support batching SQL statements using semi-colons. This was thought to be necessary for performance reasons. If you send one command at a time, you have to pay the latency cost for each command. Conversely, by sending a batch you only have to pay the cost once.

For some databases such as SQL Server, you literally send the entire batch as one massive SQL string. But PostgreSQL’s wire format doesn’t work this way. The client needs to break the batch into individual commands even though they are still sent as a set.

The naive implementation of this would be to simply assume each semi-colon means the end of the batch. Of course, it is possible that a semi-colon doesn’t represent the end of a statement, but is instead just part of a string literal. The Npgsql and PgJDBC parsers take this into consideration.

So far so good. But what if you are defining a new SQL function that consists of multiple statements? This was still not a problem because the body of the function would be escaped using dollar-quoting. Any semi-colons inside the pair of $$ tokens would be treated like any other string literal.

Then PostgreSQL 14 came along and added BEGIN ATOMIC … END, also known as the “SQL-standard syntax”. The release notes say,

When writing a function or procedure in SQL-standard syntax, the body is parsed immediately and stored as a parse tree. This allows better tracking of function dependencies, and can have security benefits.

Since semi-colons can appear anywhere inside a BEGIN ATOMIC … END block, without being in a quoted string, the parsers cannot use the current approach to determine where the batch should be divided into statements. Fully supporting this would require either an API change or building a new, far more complex parser.

As they were already concerned with the overhead caused by the current parser, the Npgsql decided to change the API. They added what they call a raw SQL mode to the library. This mode requires also using positional parameters instead of named parameters.

The PgJDBC team have not decided which approach to take. You can track their progress in the bug report titled New PG14 SQL-standard function bodies break our SQL parser.

About the Author

Subscribe for MMS Newsletter

By signing up, you will receive updates about our latest information.

  • This field is for validation purposes and should be left unchanged.