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
