ERROR: failed to find conversion function from key_vals_nn to record[]

From: Bryn Llewellyn <bryn(at)yugabyte(dot)com>
To: pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: ERROR: failed to find conversion function from key_vals_nn to record[]
Date: 2022-06-16 02:27:38
Message-ID: 37D76918-6FD7-4598-A409-A7091687D8E6@yugabyte.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I’ve copied a self-contained testcase below. Is the error that the "as intended" test causes due to a known limitation—or even a semantic dilemma that I'm failing to spot? Or might it be due to a bug?

If you're interested, the testcase rests on this thinking:

Many SQL experts claim that the RDBMS pioneers made a mistake when they made data types nullable by default—and they argue that the optional modifier should have been *nullable*. Anyway, the philosophy is clear:

«
Nulls bring all sorts of risks to the programmer that are trivially avoided in most cases where you don’t anyway want nulls.
»

However, it’s a pain to have to remember to write "not null" in a gazillion places. And the user-defined domain (conspicuous by its absence in Oracle Database) is the perfect device to impose your intentions from a single point of maintenance.

I've gone to the max. with the "nn" domain approach in my testcase. It uses a composite type thus:

type key_val as (k text_nn, v text_nn);

(At least the "text_nn" idea doesn't cause problems.)

It uses the "any()" array function to test if a given "key_val" value is found in an array of such values.

The error that occurs when I write what I mean, using a "key_val_nn" value and a "key_vals_nn" value.

I can work around the problem by typecasting my values back to their base "key_val" and "key_val[]" values by hand.

So I'm surprised that PG can't manage this typecasting for itself.

——————————————————————————————

create domain text_nn as text not null;
create type key_val as (k text_nn, v text_nn);

create domain key_val_nn as key_val not null;
create domain key_vals_nn as key_val_nn[] not null;

create function f(which in text)
returns text
language plpgsql
as $body$
declare
-- Use the raw composite type.
kv1 constant key_val := ('a', 'b');
kv2 constant key_val := ('a', 'b');
kvs constant key_val[] := array[kv1, kv2];

-- Use the domains that bring null-safety.
kv1_nn constant key_val_nn := ('a', 'b');
kvs_nn constant key_vals_nn := array[kv1, kv2];

-- Typecast the null-safe values back to the raw composite type.
kv1_0 constant key_val := kv1_nn;
kvs_0 constant key_val[] := kvs_nn;
begin
case which
when 'without NOT NULL safety' then
return (kv1 = any(kvs));
when 'as intended' then
return (kv1_nn = any(kvs_nn));
when 'workaround' then
return (kv1_0 = any(kvs_0));
end case;
end;
$body$;

select f('without NOT NULL safety');
select f('workaround');

/*
This one cases the error, thus:

ERROR: failed to find conversion function from key_vals_nn to record[]
CONTEXT: SQL expression "(kv1_nn = any(kvs_nn))"
*/;
select f('as intended');

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-06-16 03:13:42 Re: ERROR: failed to find conversion function from key_vals_nn to record[]
Previous Message Nathan Bossart 2022-06-15 17:35:08 Re: Extension pg_trgm, permissions and pg_dump order