Use stacked diagnostics to check error details (+ check of deadlock details)

This is not exactly “incredibly clever nerd stuff” but it works and you are most welcome to suggest improvements.

This “fat beauty” collects all information about error using GET STACKED DIAGNOSTICS command. Then checks error details and create summary in JSON format – which you can insert into error log etc. Check of error details is this case was created especially for deadlock situation – it found blocking process and stores query which caused deadlock. So for other error you have to change it.

This beauty cannot be placed totally into separate function. At least first part with GET must be in exception handling blocks.

Analysis of the error text is of course mutual for all other functions and therefore in real live place it into special function.

do $$
declare
begin
	begin
              .... here some very incredible program which can cause very incredible error you need to check ....
	end;

exception when others then
        raise notice 'ERROR: % - %', sqlstate, sqlerrm;
	declare
		_error_sqlstate text;
		_error_column_name text;
		_error_constraint_name text;
		_error_pg_datatype_name text;
		_error_message_text text;
		_error_table_name text;
		_error_schema_name text;
		_error_pg_exception_detail text;
		_error_pg_exception_hint text;
		_error_pg_exception_context text;
		_processes text;
		_transactions text;
		_error_data text;
		_lock_data text;
	begin
		get stacked diagnostics
			_error_sqlstate=RETURNED_SQLSTATE,
			_error_column_name=COLUMN_NAME,
			_error_constraint_name=CONSTRAINT_NAME,
			_error_pg_datatype_name=PG_DATATYPE_NAME,
			_error_message_text=MESSAGE_TEXT,
			_error_table_name=TABLE_NAME,
			_error_schema_name=SCHEMA_NAME,
			_error_pg_exception_detail=PG_EXCEPTION_DETAIL,
			_error_pg_exception_hint=PG_EXCEPTION_HINT,
			_error_pg_exception_context=PG_EXCEPTION_CONTEXT;

		if position('PROCESS' in upper(_error_pg_exception_detail) ) > 0 then
			select
			array_to_string(array_agg(distinct case when _part='PROCESS' then _next_part else null end),',') as _process,
			array_to_string(array_agg(distinct case when _part='TRANSACTION' then _next_part else null end),',') as _transaction
			into _processes, _transactions
			from (
			select _part, lead(_part,1) over (order by _row) as _next_part from (
			select row_number() over () as _row,
			regexp_split_to_table (translate(upper(_error_pg_exception_detail),'.;',''), E'\\s+') as _part
			) src
			) src1 where _part in ('PROCESS', 'TRANSACTION');

			select array_to_string( array_agg(locks._locks),',') into _lock_data
			from (
			select row_to_json(src)::text as _locks
			from (
				select * from (
					SELECT bl.pid,
					a.usename,
					d.datname as database_name,
					bl.relation,
					c.relname,
					case when c.relkind='r' then 'ordinary table'
					when c.relkind='i' then 'index'
					when c.relkind='S' then 'sequence'
					when c.relkind='v' then 'view'
					when c.relkind='m' then 'materialized view'
					when c.relkind='c' then 'composite type'
					when c.relkind='t' then 'TOAST table'
					when c.relkind='f' then 'foreign table'
					else '???' end reltype,
					bl.virtualxid,
					coalesce(bl.transactionid, first_value(bl.transactionid) over (partition by bl.virtualtransaction order by coalesce(bl.transactionid::text,'0') desc) ) as transactionid,
					bl.virtualtransaction, bl.locktype, bl.mode, bl.granted,
					to_json(replace(replace(coalesce(a.query,'null'),'''',''''''),'"','')) as query
					FROM  pg_catalog.pg_locks         bl
					left JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
					left join pg_catalog.pg_database d on bl.database=d.oid
					left join pg_catalog.pg_class c on bl.relation=c.oid
					) src0
				where src0.pid::text in (select unnest(string_to_array(_processes,','))) and
				src0.transactionid::text in (select unnest(string_to_array(_transactions,','))) and
				src0.relation is not null
			) src ) locks;

		end if;

		_error_data := '{ "pid":'||to_json(pg_backend_pid())||
		', "processes":'||to_json(coalesce(_processes,'null'))||
		', "transactions":'||to_json(coalesce(_transactions,'null'))||
		', "sqlstate":'||to_json(coalesce(_error_sqlstate,'null'))||
		', "column_name":'||to_json(coalesce(_error_column_name,'null'))||
		', "constraint_name":'||to_json(coalesce(_error_constraint_name,'null'))||
		', "pg_datatype_name":'||to_json(coalesce(_error_pg_datatype_name,'null'))||
		', "message_text":'||to_json(coalesce(_error_message_text,'null'))||
		', "table_name":'||to_json(coalesce(_error_table_name,'null'))||
		', "schema_name":'||to_json(coalesce(_error_schema_name,'null'))||
		', "pg_exception_detail":'||to_json(coalesce(_error_pg_exception_detail,'null'))||
		', "pg_exception_hint":'||to_json(coalesce(_error_pg_exception_hint,'null'))||
		', "pg_exception_context":'||
		to_json(replace(replace(coalesce(_error_pg_exception_context,'null'),'''',''''''),'"',' '))||
		', "locks": ['||coalesce(_lock_data,'null')||'] }';

		raise notice '_error_data= %', _error_data;
	end;

end;
$$