query has no destination for result data

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

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.

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 the answer to the issue of a zero row result is No it won’t cause this error.

@Ron: Did you hit send a little early?

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 schema 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 space on 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)

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&gt;, 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&gt; =
v.segment_id
    61        join probandset p on s.probandset_id = p.id <http://p.id&gt;
    62        join probandset_group_member m on p.id <http://p.id&gt;
= 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

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 current schema 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 space on 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

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$

or

\sf+ functioname

Regards

Pavel

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

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;

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&gt;, 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&gt; = v.segment_id
         join probandset p on s.probandset_id = p.id <http://p.id&gt;
         join probandset_group_member m on p.id <http://p.id&gt; =
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&gt;, 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&gt;,
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&gt;;
          exit;
       end if;
    end if;
  end loop;

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

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’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;

Quite likely but I really don’t need the assignment so the problem is gone.