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
