Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type
Дата
Msg-id CAFj8pRC0j4hSK=j-NHSRkS7LYSzc=A746pvoc+U6ss7QRJJo0A@mail.gmail.com
обсуждение исходный текст
Ответ на PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type  (Bryn Llewellyn <bryn@yugabyte.com>)
Ответы Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Дерево обсуждения


po 9. 8. 2021 v 21:41 odesílatel Bryn Llewellyn <bryn@yugabyte.com> napsal:
The problem that I report here seems to be known and seems, too, to astonish and annoy users. It's a bare "computer says No". It's hard to find anything of ultimate use with Google search (either constrained to the PG doc or unconstrained). Here's an example on stackoverflow: 


However, it does give the _clue_ to the workaround.

Here's an illustration of the issue, starting with what works fine. I tested in using PG 13.3.

create type rect as (h int, w int);
create table t1(k int primary key, r1 rect not null);
insert into t1(k, r1) values(1, (10, 20));

do $body$
declare
  r rect;
begin
  r := (
    select r1
    from t1
    where k = 1);
  raise info '%', r::text;
end;
$body$;

The "raise info" shows what you'd expect.

This re-write fails. It simply uses the approach that anybody who hasn't yet been bitten by this would expect to work.

do $body$
declare
  r rect;
begin
  select r1 -- line 5
  into r
  from t1
  where k = 1;
end;
$body$;

This is the error:

22P02: invalid input syntax for type integer: "(10,20)" ... at line 5

With "VERBOSITY" set to "verbose", there's not hint to tell you what the problem is and how to work around it.

This is true. There is no possibility to list source code with line numbers, because anonymous blocks are not persistent. The most simple way is creating simple function from your example

postgres=# \sf+ fx
        CREATE OR REPLACE FUNCTION public.fx()
         RETURNS void
         LANGUAGE plpgsql
1       AS $function$
2       declare
3         r rect;
4       begin
5         select r1 -- line 5
6         into r
7         from t1
8         where k = 1; raise notice '%', r;
9       end;
10      $function$

postgres=# select fx();
ERROR:  invalid input syntax for type integer: "(10,20)"
CONTEXT:  PL/pgSQL function fx() line 5 at SQL statement


 

Question 1.
-----------
Where, in the PL/pgSQL doc, does it state that "select col into var" when col is a user-defined type doesn't work—and where is the viable approach shown?


The problem is in implicit build of composite values. Postgres knows two types - scalars and composites. The result of the query is always tuple, and there are different rules when the target is composite or when the target is scalar. Unfortunately, until execution the PLpgSQL engine has no idea what type of expression will result. In your case, PLpgSQL got a tuple, and try to make a composite value, because the target is a composite value.

postgres=# do $body$
declare
  r rect;
begin
  select 10, 20      
  into r;
  raise notice '%', r;
end;                
$body$
;

NOTICE:  (10,20)
DO

But same mechanism break your example -

postgres=# do $body$
declare
  r rect;
begin
  select (10, 20)
  into r;
  raise notice '%', r;
end;
$body$
;
ERROR:  invalid input syntax for type integer: "(10,20)"
CONTEXT:  PL/pgSQL function inline_code_block line 5 at SQL statement

when you replace rect type by record type, you can see result

postgres=# do $body$
declare
  r record;
begin
  select (10, 20)::rect
  into r;
  raise notice '%', r;
end;
$body$
;
NOTICE:  ("(10,20)")
DO

The result has a nested rect type. The solution is easy - you can unpack composite value, and assign it

postgres=# do $body$
declare
  r record;
begin
  select ((10, 20)::rect).*
  into r;
  raise notice '%', r;
end;
$body$
;
NOTICE:  (10,20)
DO

or your example
postgres=# do $body$
declare
  r rect;
begin
  select (r1).* -- line 5
  into r
  from t1
  where k = 1; raise notice '%', r;
end;
$body$;
NOTICE:  (10,20)
DO

 

Question 2.
-----------
If I can easily re-write a failing approach by hand (once I know that I must) so that it works, why cannot the PL/pgSQL compiler do this under the covers?


The compiler checks just SQL syntax, but doesn't check semantic (identifiers). At compile time, the referred objects should not exist. So there is not any information about query results at compile time. The database objects have to exist before execution. There are advantages (and disadvantages) of this design. PL/pgSQL should not use forward declarations - and the relations between database objects and code are not too strong (PLpgSQL is much more dynamic than PL/SQL). On the other hand, some errors can be detected at runtime only. And because both sides are composite, plpgsql tries to run dynamic IO conversions, and it fails.

Although PL/pgSQL looks like PL/SQL, it is an absolutely different technology. PL/SQL is a classic compiler based environment with strong type checking - and composite types have to be known at compile time. PL/pgSQL is interpreted environment, much more similar to Python - and composite types can be static, but most of composite types are dynamic - they are created by any query execution, and assign of composite value to composite variable is mostly dynamic - based on assign of any individual field instead copy of structure's related memory. So you should not see PL/SQL in PL/pgSQL. It is similar to C and Javascript - the syntax is similar - based on {}, but any other is different.

Some errors like this, but not this can be detected by plpgsql_check https://github.com/okbob/plpgsql_check - probably the heuristic for type check is not complete.

I am afraid that what you described cannot be fixed without a compatibility break now.

Regards

Pavel
 

В списке pgsql-general по дате отправления:

Предыдущее
От: John W Higgins
Дата:
Сообщение: Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type
Следующее
От: Tom Lane
Дата:
Сообщение: Re: PL/pgSQL: Spurious 22P02 error on "select col into var" when col is user-defined type