Обсуждение: execution plan and record variable in dynamic sql
Hello! The following code results in error, if executed in one session: create or replace function exec(sql text) returns void language plpgsql as $_$ declarer record; beginexecute sql into r; -- works ok everytimeraise notice 'r %', r; -- fails if type of r.status differs from first exec invocation-- making r.status inaccessibleraise notice 'r.status %',r.status; end; $_$; select * from exec($$ select 1 as status $$); select * from exec($$ select 'test' as status $$); Results: NOTICE: r (1) NOTICE: r.status 1 exec ------ (1 row) NOTICE: r (test) ERROR: type of parameter 4 (iso-8859-1) does not match that when preparing the plan (integer) Context: PL/pgSQL function exec(text) line 8 at RAISE Version: PostgreSQL 9.4rc1 on x86_64-iso-8859-1-linux-gnu, compiled by gcc (Debian 4.9.2-2) 4.9.2, 64-bit also checked on PostgreSQL 9.1.9 Thanks! -- Serge Pushkin
=D0=9F=D1=83=D1=88=D0=BA=D0=B8=D0=BD =D0=A1=D0=B5=D1=80=D0=B3=D0=B5=D0=B9 w= rote > Hello! >=20 > The following code results in error, if executed in one session: >=20 > select * from exec($$ select 1 as status $$); > select * from exec($$ select 'test' as status $$); >=20 > Version: > PostgreSQL 9.4rc1 on x86_64-unknown-linux-gnu, compiled by gcc (Debian = =20 > 4.9.2-2) 4.9.2, 64-bit > also checked on PostgreSQL 9.1.9 SQL is a strongly typed language and this is a natural consequence of dealing with dynamic queries in such an environment. The performance gains of caching outweigh the ability to make code like your example work. You may wish to share a real use case that you think requires this to not error - and explain what it is you think it should do instead. The first notice works only because any record type can be converted to tex= t regardless of the underlying fields but as soon as you want fields it matters that what you request matches the stored model. I think parameter 4 is the % in the format string...that string is cached with an integer type input and then is confused when an unknown is provided the next time around... I'm not sure how Line 8 is calculated in your example... David J. -- View this message in context: http://postgresql.nabble.com/execution-plan-a= nd-record-variable-in-dynamic-sql-tp5830846p5830851.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
> I'm not sure how Line 8 is calculated in your example...
"Line 8" refers to line 12 "raise notice 'r.status %', r.status;"
(I have added comments into function body later, thus changing line
numbering, sorry for this mistake)
> You may wish to share a real use case that you think requires this to not
> error - and explain what it is you think it should do instead.
The real use case is a bit more complex wrapper function, which executes
arbitrary sql code, which may (or may not) return textual status code.
The function itself intended to always return this textual code as output
parameter "status" or null if there was no output from executed code.
(the same for int8 parameter "forward")
create or replace function logic.execute_trigger(sql text, process int8,
params anyarray)
returns table (status text, forward int8) language plpgsql volatile as $_$
declare r record; status text; forward int8;
begin begin execute sql using process, params into r; exception when
syntax_errorthen execute sql using process, params; end;
begin status=r.status; exception when undefined_column then status=null;
end; begin forward=r.forward; exception when undefined_column then
forward=null; end; return query select status, forward;
end;
$_$;
After implementing this (as a part of quite complicated logic-level
triggering system)
I found that if this function is called inside one session, and executed
sql
were like this: (1) "select 'text 1' as status;" and this: (2) "select
format('text 2') as status"
I get "ERROR: type of parameter 13 (text) does not match that when
preparing the plan (iso-8859-1)"
because (1) returns field "status" of type 'iso-8859-1' and (2) of 'text'.
However, if the same sql in the same order were executed in different
consquenting
sessions, there was no error, and that was what I expected.
> explain what it is you think it should do instead
I think the fields of record variable should remain accessible regardless
of
type of its values (consider we sure that field with certain name do exists
in this record), and should be castable to another type explicitly or
implicitly
also consider the following examle, which do the same thing (executes the
same
dynamic sql in the same order in one transaction), but does not lead to
any error:
do $_$
declare r record;
beginexecute $$ select 1 as status $$ into r;raise notice '%', r;raise notice '%', r.status;execute $$ select 'test' as
status$$ into r;raise notice '%', r;raise notice '%', r.status;
end;
$_$;
I think that test case stated in my initial letter should have the same
behaviour.
--
Serge Pushkin
Hi
This bug/feature is related to cached plans - PLpgSQL is able to invalidate
plans when some database objects are dropped, but it is not this use case.
I am not sure, if we would to change current behave, because it is safe
against to unexpected changes of database schema.
As solution of your problem, I can help only - don't use plpgsql language
for too dynamic tasks. When you dynamicly change datatypes, then use a more
code paths in function or use other PL language.
PLpgSQL is perfect language for static use cases .. fixed business logic.
Typically PLPerl or PLPython is good language (preferable language) for
these tasks.
Regards
Pavel
2014-12-16 10:14 GMT+01:00 =D0=9F=D1=83=D1=88=D0=BA=D0=B8=D0=BD =D0=A1=D0=
=B5=D1=80=D0=B3=D0=B5=D0=B9 <pushkinsv@gmail.com>:
>
> I'm not sure how Line 8 is calculated in your example...
>>
>
> "Line 8" refers to line 12 "raise notice 'r.status %', r.status;"
> (I have added comments into function body later, thus changing line
> numbering, sorry for this mistake)
>
> You may wish to share a real use case that you think requires this to no=
t
>> error - and explain what it is you think it should do instead.
>>
>
> The real use case is a bit more complex wrapper function, which executes
> arbitrary sql code, which may (or may not) return textual status code.
> The function itself intended to always return this textual code as output
> parameter "status" or null if there was no output from executed code.
> (the same for int8 parameter "forward")
>
> create or replace function logic.execute_trigger(sql text, process int8,
> params anyarray)
> returns table (status text, forward int8) language plpgsql volatile as $_=
$
> declare
> r record;
> status text;
> forward int8;
> begin
> begin
> execute sql using process, params into r;
> exception
> when syntax_error then execute sql using process, params;
> end;
>
> begin
> status=3Dr.status;
> exception
> when undefined_column then status=3Dnull;
> end;
> begin forward=3Dr.forward; exception when undefined_column then
> forward=3Dnull; end;
> return query select status, forward;
> end;
> $_$;
>
> After implementing this (as a part of quite complicated logic-level
> triggering system)
> I found that if this function is called inside one session, and executed
> sql
> were like this: (1) "select 'text 1' as status;" and this: (2) "select
> format('text 2') as status"
> I get "ERROR: type of parameter 13 (text) does not match that when
> preparing the plan (unknown)"
> because (1) returns field "status" of type 'unknown' and (2) of 'text'.
>
> However, if the same sql in the same order were executed in different
> consquenting
> sessions, there was no error, and that was what I expected.
>
> explain what it is you think it should do instead
>>
>
> I think the fields of record variable should remain accessible regardless
> of
> type of its values (consider we sure that field with certain name do exis=
ts
> in this record), and should be castable to another type explicitly or
> implicitly
>
> also consider the following examle, which do the same thing (executes the
> same
> dynamic sql in the same order in one transaction), but does not lead to
> any error:
>
> do $_$
> declare
> r record;
> begin
> execute $$ select 1 as status $$ into r;
> raise notice '%', r;
> raise notice '%', r.status;
> execute $$ select 'test' as status $$ into r;
> raise notice '%', r;
> raise notice '%', r.status;
> end;
> $_$;
>
> I think that test case stated in my initial letter should have the same
> behaviour.
>
> --
> Serge Pushkin
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
On Tue, Dec 16, 2014 at 2:15 AM, =D0=9F=D1=83=D1=88=D0=BA=D0=B8=D0=BD =D0= =A1=D0=B5=D1=80=D0=B3=D0=B5=D0=B9 [via PostgreSQL] < ml-node+s1045698n5830862h46@n5.nabble.com> wrote: > > I'm not sure how Line 8 is calculated in your example... > > I think the fields of record variable should remain accessible regardless > > of > type of its values (consider we sure that field with certain name do > exists > in this record), and should be castable to another type explicitly or > implicitly > > also consider the following examle, which do the same thing (executes the > > same > dynamic sql in the same order in one transaction), but does not lead to > any error: > > do $_$ > declare > r record; > begin > execute $$ select 1 as status $$ into r; > raise notice '%', r; > raise notice '%', r.status; > execute $$ select 'test' as status $$ into r; > raise notice '%', r; > raise notice '%', r.status; > end; > $_$; > > I think that test case stated in my initial letter should have the same > behaviour. > > Fair enough but the difference here is that you have dedicated raise statements for each of the two record structure outcomes whereas in the complaint you are using the same statement for both. The question here is whether there is some way for the placeholder argument in the raise notice to always be considered "text" and for all assigned values to be implicitly converted to text at runtime in an unchecked/uncached manner. Since all types have textual conversion/output functions, and the idea of "notice" is to output text, that is indeed what is expected. I'm not sure this really solves your use case though. I am pretty certain you are going to be stuck trying to enforce that input queries use homogenious types by using explicit casting. SELECT 1::text; SELECT 'test'::text; David J. -- View this message in context: http://postgresql.nabble.com/execution-plan-a= nd-record-variable-in-dynamic-sql-tp5830846p5830927.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.