Thread: query has no destination for result data

query has no destination for result data

From

Rob Sargent

Date:

06 March 2019, 10:45:50

I’m using 10.7. Does an empty result set generate this error by any chance.

One of my plpgsql functions is now throwing this error:

select * from genome_threshold_mono('11-O3C.pbs','1-O3C_chr',1.96, 1000000);
NOTICE: group id is 5eed8d65-d39a-4f72-97a3-ca391b84880d
NOTICE: New threshold: 66128154-d128-4e66-bb8e-e9c9ee5ae89d
NOTICE: doing chrom 11
NOTICE: 2019-03-06 00:21:17.253375-07: markerset id is 9a8f7487-bd64-4d43-9adf-5ae1c6744e60(1-O3C_chr11.Loci.ld), people (5eed8d65-d39a-4f72-97a3-ca391b84880d) id is 11-O3C.pbs
NOTICE: table "collected" does not exist, skipping
NOTICE: table "mrkidx" does not exist, skipping
NOTICE: 2019-03-06 00:21:17.295142-07: working with 28607 markers
NOTICE: 2019-03-06 00:21:17.383835-07: added 3514 segments to imputed_pvalue_t
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function optimal_pvalue_mono(text,text,integer,double precision,integer) line 65 at SQL statement
SQL statement "insert into goptset
select * from optimal_pvalue_mono(people_name, mvec.name, mvec.chrom, conf, maxi)"
PL/pgSQL function genome_pvalue_mono(text,text,double precision,integer) line 19 at SQL statement
SQL statement "insert into threshold_segment(id,threshold_id, segment_id, smooth_pvalue)
select uuid_generate_v4(), tid, f.segment_id, f.pval
from genome_pvalue_mono(pbs_name, markers_rx, conf, maxi) as f"
PL/pgSQL function genome_threshold_mono(text,text,double precision,integer) line 30 at SQL statement

The code referenced at line 65 is the last line in a "for row in query” construct as follows:

54 for segp in
55 select s.id, s.firstmarker, s.lastmarker,
56 v.ipv,
57 array_length(p.probands,1) as pbs,
58 s.lastmarker - s.firstmarker as mks
59 from segment s
60 join imputed_pvalue_t v on s.id = v.segment_id
61 join probandset p on s.probandset_id = p.id
62 join probandset_group_member m on p.id = m.member_id
63 where s.markerset_id = mkset
64 and m.group_id = pbsgid
65 order by ipv, pbs, mks
66 LOOP

Plugging in the appropriate values for an example run generates a proper dataset (~1300 rows)as far as I can tell.

This construct had been working until recent changes but I cannot relate the message to any deformity in the current schema or code.

Any pointers appreciated.

Re: query has no destination for result data

From

Tom Lane

Date:

06 March 2019, 17:29:03

Rob Sargent <robjsargent@gmail.com> writes:
> One of my plpgsql functions is now throwing this error:

> ERROR:  query has no destination for result data
> HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
> CONTEXT:  PL/pgSQL function optimal_pvalue_mono(text,text,integer,double precision,integer) line 65 at SQL statement

> The code referenced at line 65 is the last line in a "for row in query” construct as follows:
>     54      for segp in
>     55        select s.id, s.firstmarker, s.lastmarker,
>     56               v.ipv,
>     57               array_length(p.probands,1) as pbs,
>     58               s.lastmarker - s.firstmarker as mks
>     59        from segment s
>     60             join imputed_pvalue_t v on s.id = v.segment_id
>     61             join probandset p on s.probandset_id = p.id
>     62             join probandset_group_member m on p.id = m.member_id
>     63        where s.markerset_id = mkset
>     64              and m.group_id = pbsgid
>     65        order by ipv, pbs, mks
>     66      LOOP

I think perhaps you've miscounted lines somehow, or are looking into
the wrong function.  The "at SQL statement" bit indicates that plpgsql
thinks it's dealing with a run-of-the-mill SQL command, not a FOR loop;
moreover, looking at the source code shows that "query has no destination
for result data" is thrown only in exec_stmt_execsql, which ditto.

Another possibility, perhaps, is that there's some syntax error a little
bit above what you've shown us, such that this stanza isn't being seen
as a FOR loop at all, but as more lines in a SQL command that started
earlier.  I'm not entirely sure how such a case would have got past
parsing and into execution, but it's worth thinking about.

In any case, this isn't solvable with just what you've shown us here.

            regards, tom lane

Re: query has no destination for result data

From

Ron

Date:

06 March 2019, 17:32:51

On 3/6/19 1:45 AM, Rob Sargent wrote:
[snip]
> This construct had been working until recent changes but I cannot relate 
> the message to any deformity in the current schema or code.
> Any pointers appreciated.

What were the recent changes?

-- 
Angular momentum makes the world go 'round.

Re: query has no destination for result data

From

Rob Sargent

Date:

06 March 2019, 17:37:05


> On Mar 6, 2019, at 6:29 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Rob Sargent <robjsargent@gmail.com> writes:
>> One of my plpgsql functions is now throwing this error:
>
>> ERROR:  query has no destination for result data
>> HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
>> CONTEXT:  PL/pgSQL function optimal_pvalue_mono(text,text,integer,double precision,integer) line 65 at SQL statement
>
>> The code referenced at line 65 is the last line in a "for row in query” construct as follows:
>>    54      for segp in
>>    55        select s.id, s.firstmarker, s.lastmarker,
>>    56               v.ipv,
>>    57               array_length(p.probands,1) as pbs,
>>    58               s.lastmarker - s.firstmarker as mks
>>    59        from segment s
>>    60             join imputed_pvalue_t v on s.id = v.segment_id
>>    61             join probandset p on s.probandset_id = p.id
>>    62             join probandset_group_member m on p.id = m.member_id
>>    63        where s.markerset_id = mkset
>>    64              and m.group_id = pbsgid
>>    65        order by ipv, pbs, mks
>>    66      LOOP
>
> I think perhaps you've miscounted lines somehow, or are looking into
> the wrong function.  The "at SQL statement" bit indicates that plpgsql
> thinks it's dealing with a run-of-the-mill SQL command, not a FOR loop;
> moreover, looking at the source code shows that "query has no destination
> for result data" is thrown only in exec_stmt_execsql, which ditto.
>
> Another possibility, perhaps, is that there's some syntax error a little
> bit above what you've shown us, such that this stanza isn't being seen
> as a FOR loop at all, but as more lines in a SQL command that started
> earlier.  I'm not entirely sure how such a case would have got past
> parsing and into execution, but it's worth thinking about.
>
> In any case, this isn't solvable with just what you've shown us here.
>
>             regards, tom lane

Thank you sir, I shall keep digging. If it comes to that I can certainly share all three functions involved. I presume
theanswer to the issue of a zero row result is No it won’t cause this error. 

@Ron: Did you hit send a little early?

Re: query has no destination for result data

From

Rob Sargent

Date:

06 March 2019, 18:12:53

> On Mar 6, 2019, at 6:32 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> On 3/6/19 1:45 AM, Rob Sargent wrote:
> [snip]
>> This construct had been working until recent changes but I cannot relate the message to any deformity in the current
schemaor code. 
>> Any pointers appreciated.
>
> What were the recent changes?
>
> --
> Angular momentum makes the world go 'round.
>

diff optimalMonoPed.sql  optimalMonoPed.sql.~20359ea9e67ddf009db89b94140f67988862f247~
13,14d12
<   imkr          int;
<   jmkr          int;
53c51
< --
---
> --
67,68d64
<     select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where ordinal between segp.firstmarker and
segp.lastmarker;
<     raise notice 'seg % start=% i=% end=% j=%', segp.id, segp.firstmarker, imkr, segp.lastmarker, jmkr;

In other words two variables dropped along with the select which set them for their only use in a NOTICE, plus white
spaceon a comment line. 

So yes the problem must be in the caller.

A not on line numbers: Using \ef on this function presents a slight variation of my code: it rearranges the “language
plpgsql”from after the final END; (old style I guess) to before the AS.  So line 65 is actually what I thought was line
64.
Still not the real problem of course.  (I’ll update my ways re: coding functions)

Re: query has no destination for result data

From

Adrian Klaver

Date:

06 March 2019, 18:15:52

On 3/5/19 11:45 PM, Rob Sargent wrote:
> I’m using 10.7. Does an empty result set generate this error by any chance.
> 
> One of my plpgsql functions is now throwing this error:
> 
>     select * from genome_threshold_mono('11-O3C.pbs','1-O3C_chr',1.96,
>     1000000);

The above is the function you started with.
See below for more.

>     NOTICE:  group id is 5eed8d65-d39a-4f72-97a3-ca391b84880d
>     NOTICE:  New threshold: 66128154-d128-4e66-bb8e-e9c9ee5ae89d
>     NOTICE:  doing chrom 11
>     NOTICE:  2019-03-06 00:21:17.253375-07: markerset id is
>     9a8f7487-bd64-4d43-9adf-5ae1c6744e60(1-O3C_chr11.Loci.ld), people
>     (5eed8d65-d39a-4f72-97a3-ca391b84880d) id is 11-O3C.pbs
>     NOTICE:  table "collected" does not exist, skipping
>     NOTICE:  table "mrkidx" does not exist, skipping
>     NOTICE:  2019-03-06 00:21:17.295142-07: working with 28607 markers
>     NOTICE:  2019-03-06 00:21:17.383835-07: added 3514 segments to
>     imputed_pvalue_t
>     ERROR:  query has no destination for result data
>     HINT:  If you want to discard the results of a SELECT, use PERFORM
>     instead.

Below you have two other functions in play:

optimal_pvalue_mono()
genome_threshold_mono()

If I am following correctly it is  line 30 in genome_threshold_mono() 
you want to take a look at.



>     CONTEXT:  PL/pgSQL function
>     optimal_pvalue_mono(text,text,integer,double precision,integer) line
>     65 at SQL statement
>     SQL statement "insert into goptset
>              select * from optimal_pvalue_mono(people_name, mvec.name,
>     mvec.chrom, conf, maxi)"
>     PL/pgSQL function genome_pvalue_mono(text,text,double
>     precision,integer) line 19 at SQL statement
>     SQL statement "insert into threshold_segment(id,threshold_id,
>     segment_id, smooth_pvalue)
>               select uuid_generate_v4(), tid, f.segment_id, f.pval
>               from genome_pvalue_mono(pbs_name, markers_rx, conf, maxi)
>     as f"
>     PL/pgSQL function genome_threshold_mono(text,text,double
>     precision,integer) line 30 at SQL statement
> 
> 
> The code referenced at line 65 is the last line in a "for row in query” 
> construct as follows:
> 
>          54 for segp in
>          55   select s.id <http://s.id>, s.firstmarker, s.lastmarker,
>          56          v.ipv,
>          57          array_length(p.probands,1) as pbs,
>          58          s.lastmarker - s.firstmarker as mks
>          59   from segment s
>          60        join imputed_pvalue_t v on s.id <http://s.id> =
>     v.segment_id
>          61        join probandset p on s.probandset_id = p.id <http://p.id>
>          62        join probandset_group_member m on p.id <http://p.id>
>     = m.member_id
>          63   where s.markerset_id = mkset
>          64         and m.group_id = pbsgid
>          65   order by ipv, pbs, mks
>          66 LOOP
> 
> Plugging in the appropriate values for an example run generates a proper 
> dataset (~1300 rows)as far as I can tell.
> This construct had been working until recent changes but I cannot relate 
> the message to any deformity in the current schema or code.
> Any pointers appreciated.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: query has no destination for result data

From

Adrian Klaver

Date:

06 March 2019, 18:27:14

On 3/6/19 7:12 AM, Rob Sargent wrote:
> 
> 
>> On Mar 6, 2019, at 6:32 AM, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> On 3/6/19 1:45 AM, Rob Sargent wrote:
>> [snip]
>>> This construct had been working until recent changes but I cannot relate the message to any deformity in the
currentschema or code.
 
>>> Any pointers appreciated.
>>
>> What were the recent changes?
>>
>> -- 
>> Angular momentum makes the world go 'round.
>>
> 
> diff optimalMonoPed.sql  optimalMonoPed.sql.~20359ea9e67ddf009db89b94140f67988862f247~
> 13,14d12
> <   imkr          int;
> <   jmkr          int;
> 53c51
> < --
> ---
>> --
> 67,68d64
> <     select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where ordinal between segp.firstmarker and
segp.lastmarker;
> <     raise notice 'seg % start=% i=% end=% j=%', segp.id, segp.firstmarker, imkr, segp.lastmarker, jmkr;
> 
> In other words two variables dropped along with the select which set them for their only use in a NOTICE, plus white
spaceon a comment line.
 
> 
> So yes the problem must be in the caller.
> 
> A not on line numbers: Using \ef on this function presents a slight variation of my code: it rearranges the “language
plpgsql”from after the final END; (old style I guess) to before the AS.  So line 65 is actually what I thought was line
64.

I believe language plpgsql is not considered part of the function body 
so it is not included in the line count:

https://www.postgresql.org/docs/10/plpgsql-structure.html

When tracking a line number down I usually do:

\ef some_function line_number

which counts the line in the function body not the file. So for example:

Using set nu in Vi:

1 CREATE OR REPLACE FUNCTION public.ts_update()
2  RETURNS trigger
3  LANGUAGE plpgsql
4 AS $function$
5 BEGIN
6     NEW.ts_update := timeofday();
7 RETURN NEW;
8 END;
9 $function$

\ef ts_update 4

CREATE OR REPLACE FUNCTION public.ts_update()
  RETURNS trigger
  LANGUAGE plpgsql
AS $function$
BEGIN
     NEW.ts_update := timeofday();
RETURN NEW;  <--- This row is marked
END;
$function$



> Still not the real problem of course.  (I’ll update my ways re: coding functions)
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: query has no destination for result data

From

Pavel Stehule

Date:

06 March 2019, 18:37:37

I believe language plpgsql is not considered part of the function body
so it is not included in the line count:

https://www.postgresql.org/docs/10/plpgsql-structure.html

When tracking a line number down I usually do:

\ef some_function line_number

which counts the line in the function body not the file. So for example:

Using set nu in Vi:

1 CREATE OR REPLACE FUNCTION public.ts_update()
2 RETURNS trigger
3 LANGUAGE plpgsql
4 AS $function$
5 BEGIN
6 NEW.ts_update := timeofday();
7 RETURN NEW;
8 END;
9 $function$

\ef ts_update 4

CREATE OR REPLACE FUNCTION public.ts_update()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
NEW.ts_update := timeofday();
RETURN NEW; <--- This row is marked
END;
$function$

\sf+ functioname

Regards

Pavel

Re: query has no destination for result data

From

Adrian Klaver

Date:

06 March 2019, 18:41:22

On 3/6/19 7:37 AM, Pavel Stehule wrote:
> 
> 

> 
> or
> 
> \sf+ functioname

Cool, I learned something new.

> 
> Regards
> 
> Pavel


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: query has no destination for result data

From

Rob Sargent

Date:

06 March 2019, 19:19:59

On Mar 6, 2019, at 7:41 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 3/6/19 7:37 AM, Pavel Stehule wrote:

or
\sf+ functioname

Cool, I learned something new.

Regards
Pavel

using \ef function 65 puts the cursor on the first line of the loop. So a debugging statement got in the way! I don’t need the variables set other than to keep track of what’s going on in the loop. They’re gone now and so it the problem (and hopefully I’ll correct my ways).

Thank you all.

raise notice '%: added % segments to imputed_pvalue_t', clock_timestamp(), rcount;
--
for segp in
select s.id, s.firstmarker, s.lastmarker,
v.ipv,
array_length(p.probands,1) as pbs,
s.lastmarker - s.firstmarker as mks
from segment s
join imputed_pvalue_t v on s.id = v.segment_id
join probandset p on s.probandset_id = p.id
join probandset_group_member m on p.id = m.member_id
where s.markerset_id = mkset
and m.group_id = pbsgid
order by ipv, pbs, mks
LOOP
select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where ordinal between segp.firstmarker and segp.lastmarker;
raise notice 'seg % start=% i=% end=% j=%', segp.id, segp.firstmarker, imkr, segp.lastmarker, jmkr;
delete from mrkidx where ordinal between segp.firstmarker and segp.lastmarker;
get diagnostics rcount = ROW_COUNT;
segsdone = segsdone + 1;
if rcount > 0 then
insert into collected values(segp.id, segp.ipv);
totalinserts = totalinserts + rcount;
if totalinserts = mkrcnt then -- really totalDELETES
raise notice '%: no markers left on %th segment %', clock_timestamp(), segsdone, segp.id;
exit;
end if;
end if;
end loop;

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: query has no destination for result data

From

Adrian Klaver

Date:

06 March 2019, 21:29:18

On 3/6/19 8:19 AM, Rob Sargent wrote:
> 
> 
>> On Mar 6, 2019, at 7:41 AM, Adrian Klaver <adrian.klaver@aklaver.com 
>> <mailto:adrian.klaver@aklaver.com>> wrote:
>>
>> On 3/6/19 7:37 AM, Pavel Stehule wrote:
>>
>>> or
>>> \sf+ functioname
>>
>> Cool, I learned something new.
>>
>>> Regards
>>> Pavel
>>
>>
> 
> using \ef function 65 puts the cursor on the first line of the loop. So 
> a debugging statement got in the way!  I don’t need the variables set 
> other than to keep track of what’s going on in the loop. They’re gone 
> now and so it the problem (and hopefully I’ll correct my ways).

Where you maybe needing SELECT INTO?:

https://www.postgresql.org/docs/10/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

So something like(not tested):

select min(ordinal), max(ordinal) into imkr, jmkr from mrkidx where 
ordinal between segp.firstmarker and segp.lastmarker;

> Thank you all.
> 
>        raise notice '%: added % segments to imputed_pvalue_t',
>     clock_timestamp(), rcount;
>     --
>        for segp in
>          select s.id <http://s.id>, s.firstmarker, s.lastmarker,
>                 v.ipv,
>                 array_length(p.probands,1) as pbs,
>                 s.lastmarker - s.firstmarker as mks
>          from segment s
>               join imputed_pvalue_t v on s.id <http://s.id> = v.segment_id
>               join probandset p on s.probandset_id = p.id <http://p.id>
>               join probandset_group_member m on p.id <http://p.id> =
>     m.member_id
>          where s.markerset_id = mkset
>                and m.group_id = pbsgid
>          order by ipv, pbs, mks
>        LOOP
>     _select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where
>     ordinal between segp.firstmarker and segp.lastmarker;_
>          raise notice 'seg % start=% i=% end=% j=%', segp.id
>     <http://segp.id>, segp.firstmarker, imkr, segp.lastmarker, jmkr;
>          delete from mrkidx where ordinal between segp.firstmarker and
>     segp.lastmarker;
>          get diagnostics rcount = ROW_COUNT;
>          segsdone = segsdone + 1;
>          if rcount > 0 then
>             insert into collected values(segp.id <http://segp.id>,
>     segp.ipv);
>             totalinserts = totalinserts + rcount;
>             if totalinserts = mkrcnt then  -- really totalDELETES
>                raise notice '%: no markers left on %th segment %',
>     clock_timestamp(), segsdone, segp.id <http://segp.id>;
>                exit;
>             end if;
>          end if;
>        end loop;
> 
>> -- 
>> Adrian Klaver
>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: query has no destination for result data

From

Rob Sargent

Date:

06 March 2019, 22:20:31


> On Mar 6, 2019, at 10:29 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 3/6/19 8:19 AM, Rob Sargent wrote:
>>> On Mar 6, 2019, at 7:41 AM, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>>>
>>>> On 3/6/19 7:37 AM, Pavel Stehule wrote:
>>>>
>>>> or
>>>> \sf+ functioname
>>>
>>> Cool, I learned something new.
>>>
>>>> Regards
>>>> Pavel
>>>
>>>
>> using \ef function 65 puts the cursor on the first line of the loop. So a debugging statement got in the way!  I
don’tneed the variables set other than to keep track of what’s going on in the loop. They’re gone now and so it the
problem(and hopefully I’ll correct my ways). 
>
> Where you maybe needing SELECT INTO?:
>
> https://www.postgresql.org/docs/10/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
>
> So something like(not tested):
>
> select min(ordinal), max(ordinal) into imkr, jmkr from mrkidx where ordinal between segp.firstmarker and
segp.lastmarker;
>
Quite likely but I really don’t need the assignment so the problem is gone.


>> Thank you all.
>>       raise notice '%: added % segments to imputed_pvalue_t',
>>    clock_timestamp(), rcount;
>>    --
>>       for segp in
>>         select s.id <http://s.id>, s.firstmarker, s.lastmarker,
>>                v.ipv,
>>                array_length(p.probands,1) as pbs,
>>                s.lastmarker - s.firstmarker as mks
>>         from segment s
>>              join imputed_pvalue_t v on s.id <http://s.id> = v.segment_id
>>              join probandset p on s.probandset_id = p.id <http://p.id>
>>              join probandset_group_member m on p.id <http://p.id> =
>>    m.member_id
>>         where s.markerset_id = mkset
>>               and m.group_id = pbsgid
>>         order by ipv, pbs, mks
>>       LOOP
>>    _select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where
>>    ordinal between segp.firstmarker and segp.lastmarker;_
>>         raise notice 'seg % start=% i=% end=% j=%', segp.id
>>    <http://segp.id>, segp.firstmarker, imkr, segp.lastmarker, jmkr;
>>         delete from mrkidx where ordinal between segp.firstmarker and
>>    segp.lastmarker;
>>         get diagnostics rcount = ROW_COUNT;
>>         segsdone = segsdone + 1;
>>         if rcount > 0 then
>>            insert into collected values(segp.id <http://segp.id>,
>>    segp.ipv);
>>            totalinserts = totalinserts + rcount;
>>            if totalinserts = mkrcnt then  -- really totalDELETES
>>               raise notice '%: no markers left on %th segment %',
>>    clock_timestamp(), segsdone, segp.id <http://segp.id>;
>>               exit;
>>            end if;
>>         end if;
>>       end loop;
>>> --
>>> Adrian Klaver
>>> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com