On 03/02/2018 06:42 AM, Alexander Farber wrote:
Hi Adrian, I 100% agree that nobody except me should debug my huge
stored function, but if you look at my PostgreSQL 10.3 log -
Which proves what has already been proven, that at least some of the
data is correct. The issue is data that is not correct as evidenced by
the error message:
select mid, jsonb_array_length(tiles) from words_moves where gid=609;
ERROR: 22023: cannot get array length of a scalar
LOCATION: jsonb_array_length, jsonfuncs.c:1579
This is not going to get solved until you identify the 'bad' tiles data.
2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.110 ms parse
<unnamed>: SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns,
out_adm AS adm, out_body AS body FROM words_play_game($1::int,
$2::int, $3::jsonb)
2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms bind
<unnamed>: SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns,
out_adm AS adm, out_body AS body FROM words_play_game($1::int,
$2::int, $3::jsonb)
2018-03-02 15:30:33.646 CET [16693] DETAIL: parameters: $1 = '7', $2 =
'609', $3 =
'[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter":"И","row":4,"value":1}]'
2018-03-02 15:30:33.646 CET [16693] LOG: execute <unnamed>: SELECT
out_uid AS uid, out_fcm AS fcm, out_apns AS apns, out_adm AS adm,
out_body AS body FROM words_play_game($1::int, $2::int, $3::jsonb)
2018-03-02 15:30:33.646 CET [16693] DETAIL: parameters: $1 = '7', $2 =
'609', $3 =
'[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter":"И","row":4,"value":1}]'
I just pass as the 3rd argument in_tiles to my stored function:
'[{"col":0,"letter":"К","row":3,"value":2},{"col":0,"letter":"И","row":4,"value":1}]'
and then take the in_tiles and store it unchanged in the words_moves table:
INSERT INTO words_moves (
action,
gid,
uid,
played,
tiles
) VALUES (
'play',
in_gid,
in_uid,
CURRENT_TIMESTAMP,
in_tiles
) RETURNING mid INTO STRICT _mid;
Does anybody happen to see what could I do wrong there?
Thank you for any hints
Alex
P.S: Here my stored fuinction:
https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914
Here my table:
https://gist.github.com/afarber/06cc37114ff8dd14f05077f312904361
And here is how I call the stored function from Java:
String SQL_PLAY_GAME =
"SELECT " +
"out_uid AS uid, " +
"out_fcm AS fcm, " +
"out_apns AS apns, " +
"out_adm AS adm, " +
"out_body AS body " +
"FROM words_play_game(?::int, ?::int, ?::jsonb)";
private void handlePlay(int gid, String tiles) throws SQLException,
IOException {
LOG.info("handlePlay: {} -> {} {}", mUid, gid, tiles);
try (Connection db = DriverManager.getConnection(DATABASE_URL,
DATABASE_USER, DATABASE_PASS);
PreparedStatement st =
db.prepareStatement(SQL_PLAY_GAME)) {
st.setInt(1, mUid);
st.setInt(2, gid);
st.setString(3, tiles);
runPlayerAction(st, gid);
}
}
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
