Re: [GENERAL] ERROR: query returned no rows

[Alexander Farber]

Thank you Adrian, with \sf+ words_skip_game(integer, integer) the line 85
was correct

Re: [GENERAL] ERROR: query returned no rows

[Adrian Klaver]

On 06/26/2017 01:10 PM, Paul A Jungwirth wrote:

On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
 wrote:

On 06/26/2017 12:03 PM, Paul Jungwirth wrote:

Perhaps
you should see what is line 85 when you do `\sf words_skip_game` (rather
than line 85 in your own source code).


Or easier yet:

https://www.postgresql.org/docs/9.5/static/app-psql.html
"
\ef [ function_description [ line_number ] ]


I am always nervous about answering questions here when so many actual
Postgres maintainers are around. But it's pretty great that so often
when I do it provokes an even better answer. It's not the first time!
:-)


This:

"It's how I have learned a lot deal about Postgres. "

should be:

"It's how I have learned a lot about Postgres. "

The hazards of editing on the fly.



Paul




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ERROR: query returned no rows

[Adrian Klaver]

On 06/26/2017 01:10 PM, Paul A Jungwirth wrote:

On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
 wrote:

On 06/26/2017 12:03 PM, Paul Jungwirth wrote:

Perhaps
you should see what is line 85 when you do `\sf words_skip_game` (rather
than line 85 in your own source code).


Or easier yet:

https://www.postgresql.org/docs/9.5/static/app-psql.html
"
\ef [ function_description [ line_number ] ]


I am always nervous about answering questions here when so many actual
Postgres maintainers are around. But it's pretty great that so often
when I do it provokes an even better answer. It's not the first time!
:-)


The docs are extensive and in a constant state of change, so the list is 
 a great resource for finding information you haven't stumbled across 
yet on your own. It's how I have learned a lot deal about Postgres.




Paul




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ERROR: query returned no rows

[Paul A Jungwirth]

On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver
 wrote:
> On 06/26/2017 12:03 PM, Paul Jungwirth wrote:
>> Perhaps
>> you should see what is line 85 when you do `\sf words_skip_game` (rather
>> than line 85 in your own source code).
>
> Or easier yet:
>
> https://www.postgresql.org/docs/9.5/static/app-psql.html
> "
> \ef [ function_description [ line_number ] ]

I am always nervous about answering questions here when so many actual
Postgres maintainers are around. But it's pretty great that so often
when I do it provokes an even better answer. It's not the first time!
:-)

Paul


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ERROR: query returned no rows

[Adrian Klaver]

On 06/26/2017 12:03 PM, Paul Jungwirth wrote:

On 06/26/2017 11:21 AM, Alexander Farber wrote:

The error message returned by
the database is rather cryptic:

words=> select * from words_skip_game(1, 1);
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
SQL statement

When I look at my source code
( https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at
the line 85, then I am not sure if the line number reported by the error
message is correct, because it points into middle of an UPDATE statement:


I agree the line numbers for error messages inside of plpgsql functions 
never seem to make sense, so I wouldn't get too hung up on line 85. 
Perhaps you should see what is line 85 when you do `\sf words_skip_game` 
(rather than line 85 in your own source code). But mostly I would try to 
find some other way of locating the cause of the error.


Or easier yet:

https://www.postgresql.org/docs/9.5/static/app-psql.html
"
\ef [ function_description [ line_number ] ]

This command fetches and edits the definition of the named 
function, in the form of a CREATE OR REPLACE FUNCTION command. Editing 
is done in the same way as for \edit. After the editor exits, the 
updated command waits in the query buffer; type semicolon or \g to send 
it, or \r to cancel.


The target function can be specified by name alone, or by name and 
arguments, for example foo(integer, text). The argument types must be 
given if there is more than one function of the same name.


If no function is specified, a blank CREATE FUNCTION template is 
presented for editing.


If a line number is specified, psql will position the cursor on the 
specified line of the function body. (Note that the function body 
typically does not begin on the first line of the file.)


"

So:

\ef words_skip_game 85



You can read about the "query returned no rows" message here:

https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html

It seems to me you should only see it for `INTO STRICT`, not plain 
`INTO`. I see from your gist that your function *does* have some queries 
that are `INTO STRICT`, so I would focus on those.


That page also describes how to use `print_strict_params` to get a 
little more info about the details of the error.


I hope that helps!

Paul





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ERROR: query returned no rows

[Paul Jungwirth]

On 06/26/2017 11:21 AM, Alexander Farber wrote:

The error message returned by
the database is rather cryptic:

words=> select * from words_skip_game(1, 1);
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
SQL statement

When I look at my source code
( https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at
the line 85, then I am not sure if the line number reported by the error
message is correct, because it points into middle of an UPDATE statement:


I agree the line numbers for error messages inside of plpgsql functions 
never seem to make sense, so I wouldn't get too hung up on line 85. 
Perhaps you should see what is line 85 when you do `\sf words_skip_game` 
(rather than line 85 in your own source code). But mostly I would try to 
find some other way of locating the cause of the error.


You can read about the "query returned no rows" message here:

https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html

It seems to me you should only see it for `INTO STRICT`, not plain 
`INTO`. I see from your gist that your function *does* have some queries 
that are `INTO STRICT`, so I would focus on those.


That page also describes how to use `print_strict_params` to get a 
little more info about the details of the error.


I hope that helps!

Paul


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ERROR: query returned no rows

[Alexander Farber]

In my case _opponent was NULL and there are no records in words_users with
PK uid being NULL... so that was the reason.

Thank you

Re: [GENERAL] ERROR: query returned no rows

[Alexander Farber]

Hi again,

On Mon, Jun 26, 2017 at 8:21 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> words=> select * from words_skip_game(1, 1);
> ERROR:  query returned no rows
> CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at
> SQL statement
>
> When I look at my source code ( https://gist.github.com/afarber/
> cac9a83b7a37307ace8d787be9b8ff4c ) at the line 85, then I am not sure if
> the line number reported by the error message is correct
>


I have added many RAISE NOTICE '' lines and now see that the error is
at SELECT ... INTO STRICT (which was not the line 85):

SELECT
_opponent,
fcm,
apns,
sns,
CASE
WHEN _skips = 5 THEN 'Противник пропускает
ход (еще один пропуск завершит игру)'
ELSE 'Противник пропускает ход'
END
FROMwords_users
WHERE   uid = _opponent
INTO STRICT
out_uid,
out_fcm,
out_apns,
out_sns,
out_note;
RETURN NEXT;

Regards
Alex

Re: [SPAM] [GENERAL] ERROR: query returned no rows

[Alexander Farber]

On Mon, Jun 26, 2017 at 8:39 PM, Moreno Andreo 
wrote:

> Il 26/06/2017 20:21, Alexander Farber ha scritto:
>
>>
>> RETURNING
>> player1,
>> score2,
>> score1
>> INTO
>> _opponent,
>> _score1,   -- the line 85
>> _score2;
>>
>> Is it intentional to return score2 in score1 and vice versa?



>
> Yes, Andreo, that one is intentional, but thank you.
>
> In all my custom functions I return player1, played1, hand1 for the
> calling player (even if it is player2 in the words_games table)
>

Regards
Alex

Re: [SPAM] [GENERAL] ERROR: query returned no rows

[Moreno Andreo]

Il 26/06/2017 20:21, Alexander Farber ha scritto:


RETURNING
player1,
score2,
score1
INTO
_opponent,
_score1,   -- the line 85
_score2;


Is it intentional to return score2 in score1 and vice versa?



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ERROR: query returned no rows

[Alexander Farber]

Good evening,

with PostgreSQL 9.5 I have extended a larger custom function, which has
worked well before and my problem is that the error message returned by the
database is rather cryptic:

words=> select * from words_skip_game(1, 1);
ERROR:  query returned no rows
CONTEXT:  PL/pgSQL function words_skip_game(integer,integer) line 85 at SQL
statement

When I look at my source code (
https://gist.github.com/afarber/cac9a83b7a37307ace8d787be9b8ff4c ) at the
line 85, then I am not sure if the line number reported by the error
message is correct, because it points into middle of an UPDATE statement:

UPDATE words_games SET
finished = _finished,
played2  = CURRENT_TIMESTAMP
WHERE
gid  = in_gid AND
player2  = in_uid AND
-- game is not over yet
finished IS NULL  AND
-- and it is second player's turn
(played2 IS NULL OR played2 < played1)
RETURNING
player1,
score2,
score1
INTO
_opponent,
_score1,   -- the line 85
_score2;

And here is my words_games table:

words=> \d words_games
   Table "public.words_games"
  Column  |   Type   | Modifiers

--+--+---
 gid  | integer  | not null default
nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone | not null
 finished | timestamp with time zone |
 player1  | integer  | not null
 player2  | integer  |
 played1  | timestamp with time zone |
 played2  | timestamp with time zone |
 score1   | integer  | not null
 score2   | integer  | not null
 hand1| character(1)[]   | not null
 hand2| character(1)[]   | not null
 pile | character(1)[]   | not null
 letters  | character(1)[]   | not null
 values   | integer[]| not null
 bid  | integer  | not null
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_notes" CONSTRAINT "words_notes_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE

And the record for the gid=1 (apologies if I copy-paste too much data here):

words=> select * from words_games where gid=1;
 gid |created| finished | player1 | player2 |
played1 | played2 | score1 | score2 |  hand1  |  hand2  |

pile

 |






  letters







 |







 values






   | bid