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

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

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?

I read the note in create domain as basically “don’t do this” (the not null part) but the issue you are pointing out seems unrelated to that. 

/*
  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');

The fact that a domain over an array isn’t being seen as an array here seems like a bug.  POLA violation at least, and I don’t recall any notes regarding this dynamic in the docs.

However, a more trivial case does work, at least in HEAD:

create domain mytext as text[] not null;

select '1' = any(array['1','2']::mytext);
 ?column?
----------
 t

However, as you show:

create type kv AS ( key text, val text );
create domain kvarr as kv[];
select ('1','one')::kv = any (array[('1','one')::kv]);
 ?column?
----------
 t
(1 row)

select ('1','one')::kv = any ((array[('1','one')::kv])::kvarr);
ERROR:  failed to find conversion function from kvarr to record[]

So the interaction of a composite type and the domain over array seems to be the scope of the issue - which makes me thing bug even more.

Thanks for the quick response, David. I'll use my workaround for now. And I'll look out for news about a possible fix.

About this from the "create domain" doc:

«

Best practice therefore is to design a domain's constraints so that a null value is allowed, and then to apply column NOT NULL constraints to columns of the domain type as needed, rather than directly to the domain type.

»

What an enormous disappointment. It defeats a large part of what I wanted to adopt as a practice. As I understand that “don’t do this” caution, and the discussion that surrounds it, the advice applies only to the case that a domain with a not null constraint is used as the data type of a column in a table. I tried this variant on what the doc has:

create domain text_nn as text not null;
create table t1(k int primary key, v text not null);
insert into t1(k, v) values(1, 'x');

-- Causes:
-- null value in column "v" of relation "t1" violates not-null constraint
insert into t1(k, v) values(2, (select 'y'::text_nn where false));

Right, the subquery evaluates to "null". Then failure comes, as is intended, when the attempt is made to assign "null" to "t.v" in the to-be-inserted row.

Then I repeated the test like this:

create table t2(k int primary key, v text_nn);
insert into t2(k, v) values(1, 'x');
insert into t2(k, v) values(2, (select 'y'::text_nn where false));

\pset null '<null>'
select
  k,
  v,
  pg_typeof(v) as "pg_typeof(v)"
from t2;

No error—and this result:

 k |   v    | pg_typeof(v) 
---+--------+--------------
 1 | x      | text_nn
 2 | <null> | text_nn

This is what the doc promises. But how can you see it as anything but a bug? The subquery evaluates to "null", and only then is the attempt made to create a new row which self-evidently violates the domain's constraint. How is it any different from this:

insert into t2(k, v) values(1, null);

This obligingly causes "domain text_nn does not allow null values".