This might not interest many of you, but I recently heard about at least two people that stumbled upon the problems I did, so I figured I can write about problems we discovered, and how we solved them (or not).
When we began our journey, the latest Pg was 14.x, that's why we're upgrading to 14, not 15. But I suspect upgrading to 15 wouldn't change much …
Continue reading A tale about (incomplete) upgrade from PostgreSQL 12 to 14
Title: On 9th of December 2022, Tom Lane committed patch:
Add test scaffolding for soft error reporting from input functions.
pg_input_is_valid() returns boolean, while pg_input_error_message()
returns the primary error message if the input is bad, or NULL
if the input is OK. The main reason for having two functions is
so that we can test both the details-wanted and the no-details-wanted
Although these are primarily designed with testing in mind,
it could well be that they'll be useful to end users as well.
This patch is mostly by me, but it owes very substantial debt to
earlier work by Nikita Glukhov, Andrew Dunstan, and Amul Sul.
Thanks to Andres Freund for review.
Continue reading Waiting for PostgreSQL 16 – Add test scaffolding for soft error reporting from input functions.
On 12nd of September 2017, Tom Lane committed patch:
Add psql variables to track success/failure of SQL queries.
This patch adds ERROR, SQLSTATE, and ROW_COUNT, which are updated after
every query, as well as LAST_ERROR_MESSAGE and LAST_ERROR_SQLSTATE,
which are updated only when a query fails. The expected usage of these
is for scripting.
Fabien Coelho, reviewed by Pavel Stehule
Continue reading Waiting for PostgreSQL 11 – Add psql variables to track success/failure of SQL queries.
Yet another missed thing for “Waiting for 9.3". Sorry about that.
On 29th of January, Tom Lane committed patch:
Provide database object names as separate fields in error messages.
This patch addresses the problem that applications currently have to
extract object names from possibly-localized textual error messages,
if they want to know for example which index caused a UNIQUE_VIOLATION
failure. It adds new error message fields to the wire protocol, which
can carry the name of a table, table column, data type, or constraint
associated with the error. (Since the protocol spec has always instructed
clients to ignore unrecognized field types, this should not create any
Support for providing these new fields has been added to just a limited set
of error reports (mainly, those in the "integrity constraint violation"
SQLSTATE class), but we will doubtless add them to more calls in future.
Pavel Stehule, reviewed and extensively revised by Peter Geoghegan, with
additional hacking by Tom Lane.
Continue reading Waiting for 9.3 – Provide database object names as separate fields in error messages.
On 18th of July, Tom Lane committed patch:
Add GET STACKED DIAGNOSTICS plpgsql command to retrieve exception info.
This is more SQL-spec-compliant, more easily extensible, and better
performing than the old method of inventing special variables.
Pavel Stehule, reviewed by Shigeru Hanada and David Wheeler
Continue reading Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL
One common problem that a lot of people seem to have is when they encounter error message like this:
# \i test.sql
psql:test.sql:1: ERROR: invalid byte sequence for encoding "UTF8": 0xb3
Why it happens? What can be done about it? Let's see.
Continue reading ERROR: invalid byte sequence for encoding