ERROR: query returned no rows

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: ERROR: query returned no rows
Date: 2017-06-26 18:21:10
Message-ID: CAADeyWi2CRO8t3mo7nKdf=mmRfbPqjrL-0_-QoTD4VqE6u5Zjw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
-----+-------------------------------+----------+---------+---------+---------+---------+--------+--------+-----------------+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----
1 | 2017-06-26 19:42:22.356327+02 | | 1 | |
| | 0 | 0 | {У,К,Ж,О,И,Д,Т} | {Е,Р,П,З,Х,О,*} |
{Т,Р,В,Л,Ц,А,С,Л,И,В,Б,Д,Ш,Ы,Е,О,Н,В,И,В,С,А,Т,Ж,С,К,С,Л,Г,*,А,Щ,Н,И,Е,Б,К,Ф,Д,Т,О,К,Р,П,Б,М,Е,Г,Ю,З,Д,Ъ,С,А,К,Ч,И,П,М,Й,У,Е,Е,М,О,О,Н,Н,Ь,У,Й,Э,Л,О,С,Х,И,М,Я,Д,О,Е,А,П,А,Е,Ч,Я,Й,Г,А,Н,Я,О,И,Р,О,П,Е,Р,Н,К,Ь,В,Р,М,А,А,А,Н,Т,П,Ы,Н,И,Й}
|
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
|
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULwords=>

Any hints please?

Searching Google for "query returned no rows" postgresql has not worked for
me yet.

Looking at the server logs has not helped either (same cryptic error
message with no additional details).

Best regards
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Moreno Andreo 2017-06-26 18:39:38 Re: [SPAM] ERROR: query returned no rows
Previous Message Arthur Zakirov 2017-06-26 09:10:01 Re: Configure Qt Creator to work with PostgreSQL to extensions development