'update returning *' returns 0 columns instead of empty row with 2 columns when (i) no rows updated and (ii) when applied to a partitioned table with sub-partition

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

'update returning *' returns 0 columns instead of empty row with 2 columns when (i) no rows updated and (ii) when applied to a partitioned table with sub-partition

petr.fedorov
Hello,

The following code snippet demonstrates the problem: the first select
passes and the second [select * from testf(FALSE)] fails. I would expect
that select * from testf(...); works without errors in both cases.

begin;

create table test (id integer, data char(1)) partition by list (id)
tablespace pg_default;
create table test_1 partition of test for values in (1) partition by
list (data);
create table test_1_a partition of test_1 for values in ('a');
create function testf(p boolean) returns setof test language 'plpgsql'
as $body$ begin return query update test set id=id where p returning *;
end; $body$;
insert into test (id, data) values (1, 'a');
select * from testf(TRUE);
select * from testf(FALSE);

rollback;   

The result:

ERROR: structure of query does not match function result type

SQL state: 42804

Detail: Number of returned columns (0) does not match expected column
count (2).

Context: PL/pgSQL function testf(boolean) line 1 at RETURN QUERY


I'm on  PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit on Centos 7

uname -a gives   Linux 3.10.0-862.11.6.el7.x86_64 #1 SMP Tue Aug 14
21:49:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux



Reply | Threaded
Open this post in threaded view
|

Re: 'update returning *' returns 0 columns instead of empty row with 2 columns when (i) no rows updated and (ii) when applied to a partitioned table with sub-partition

Amit Langote-2
Hi,

On 2019/02/01 23:32, Petr Fedorov wrote:

> Hello,
>
> The following code snippet demonstrates the problem: the first select
> passes and the second [select * from testf(FALSE)] fails. I would expect
> that select * from testf(...); works without errors in both cases.
>
> begin;
>
> create table test (id integer, data char(1)) partition by list (id)
> tablespace pg_default;
> create table test_1 partition of test for values in (1) partition by
> list (data);
> create table test_1_a partition of test_1 for values in ('a');
> create function testf(p boolean) returns setof test language 'plpgsql'
> as $body$ begin return query update test set id=id where p returning *;
> end; $body$;
> insert into test (id, data) values (1, 'a');
> select * from testf(TRUE);
> select * from testf(FALSE);
>
> rollback;   
>
> The result:
>
> ERROR: structure of query does not match function result type
>
> SQL state: 42804
>
> Detail: Number of returned columns (0) does not match expected column
> count (2).
>
> Context: PL/pgSQL function testf(boolean) line 1 at RETURN QUERY
Thanks for the report.  There indeed appears to be a bug here.

The problem seems to be with how planner handles an empty plan (due to
constant-FALSE qual) when the target table is an inheritance tree.  OP's
example contains a partitioned table, but I could reproduce it with
regular inheritance:

create table parent (id int);
create table child () inherits (parent);
create or replace function testf(p boolean) returns setof parent
  language 'plpgsql' as $body$
    begin
      return query update parent set id = id where p returning *;
    end;
$body$;

select * from testf(true);
 id
────
(0 rows)

select * from testf(false);
ERROR:  structure of query does not match function result type
DETAIL:  Number of returned columns (0) does not match expected column
count (1).
CONTEXT:  PL/pgSQL function testf(boolean) line 1 at RETURN QUERY

No problem when there is no inheritance:

drop function testf;
create table foo (like parent);
create or replace function testf(p boolean) returns setof foo
  language 'plpgsql' as $body$
    begin
      return query update foo set id = id where p returning *;
    end;
$body$;
select * from testf(false);
 id
────
(0 rows)


Mismatch between the query result type and the function result type occurs
in the inheritance case, because the targetlist of the plan for the UPDATE
query in testf's body is empty, whereas the function execution code
(pl_exec.c) expects it match the function's result type (set of parent).
It's empty because inheritance_planner sets an empty Result path when it
finds that all the children are excluded, but hasn't generated enough
state in the path's RelOptInfo and PlannerInfo such that the correct
targetlist could be set in the empty Result plan that's eventually created.

Attached patch seems to fix it.  It also adds a test in inherit.sql.

Thoughts?

Thanks,
Amit

inheritance_planner-empty-Result-set-tlist.patch (4K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: 'update returning *' returns 0 columns instead of empty row with 2 columns when (i) no rows updated and (ii) when applied to a partitioned table with sub-partition

Amit Langote-2
On 2019/02/06 16:35, Amit Langote wrote:

> Hi,
>
> On 2019/02/01 23:32, Petr Fedorov wrote:
>> Hello,
>>
>> The following code snippet demonstrates the problem: the first select
>> passes and the second [select * from testf(FALSE)] fails. I would expect
>> that select * from testf(...); works without errors in both cases.
>>
>> begin;
>>
>> create table test (id integer, data char(1)) partition by list (id)
>> tablespace pg_default;
>> create table test_1 partition of test for values in (1) partition by
>> list (data);
>> create table test_1_a partition of test_1 for values in ('a');
>> create function testf(p boolean) returns setof test language 'plpgsql'
>> as $body$ begin return query update test set id=id where p returning *;
>> end; $body$;
>> insert into test (id, data) values (1, 'a');
>> select * from testf(TRUE);
>> select * from testf(FALSE);
>>
>> rollback;   
>>
>> The result:
>>
>> ERROR: structure of query does not match function result type
>>
>> SQL state: 42804
>>
>> Detail: Number of returned columns (0) does not match expected column
>> count (2).
>>
>> Context: PL/pgSQL function testf(boolean) line 1 at RETURN QUERY
>
> Thanks for the report.  There indeed appears to be a bug here.
>
> The problem seems to be with how planner handles an empty plan (due to
> constant-FALSE qual) when the target table is an inheritance tree.  OP's
> example contains a partitioned table, but I could reproduce it with
> regular inheritance:
>
> create table parent (id int);
> create table child () inherits (parent);
> create or replace function testf(p boolean) returns setof parent
>   language 'plpgsql' as $body$
>     begin
>       return query update parent set id = id where p returning *;
>     end;
> $body$;
>
> select * from testf(true);
>  id
> ────
> (0 rows)
>
> select * from testf(false);
> ERROR:  structure of query does not match function result type
> DETAIL:  Number of returned columns (0) does not match expected column
> count (1).
> CONTEXT:  PL/pgSQL function testf(boolean) line 1 at RETURN QUERY
>
> No problem when there is no inheritance:
>
> drop function testf;
> create table foo (like parent);
> create or replace function testf(p boolean) returns setof foo
>   language 'plpgsql' as $body$
>     begin
>       return query update foo set id = id where p returning *;
>     end;
> $body$;
> select * from testf(false);
>  id
> ────
> (0 rows)
>
>
> Mismatch between the query result type and the function result type occurs
> in the inheritance case, because the targetlist of the plan for the UPDATE
> query in testf's body is empty, whereas the function execution code
> (pl_exec.c) expects it match the function's result type (set of parent).
> It's empty because inheritance_planner sets an empty Result path when it
> finds that all the children are excluded, but hasn't generated enough
> state in the path's RelOptInfo and PlannerInfo such that the correct
> targetlist could be set in the empty Result plan that's eventually created.
>
> Attached patch seems to fix it.  It also adds a test in inherit.sql.
>
> Thoughts?

Will add this to next CF.

Thanks,
Amit