Re: ERROR: failed to find conversion function from key_vals_nn to record[] - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: ERROR: failed to find conversion function from key_vals_nn to record[] |
| Date | |
| Msg-id | F27C633E-F13D-4B46-B996-438BB83B3073@yugabyte.com Whole thread Raw |
| In response to | Re: ERROR: failed to find conversion function from key_vals_nn to record[] ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: ERROR: failed to find conversion function from key_vals_nn to record[]
|
| List | pgsql-general |
david.g.johnston@gmail.com wrote:bryn@yugabyte.com 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?
----------
tHowever, 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.
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));
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;
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
---+--------+--------------
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".
pgsql-general by date: