Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar

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 -

On Fri, Mar 2, 2018 at 3:31 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

>
> The little gray cells are not awake enough to work through the below:) If
> it where me I would first confirm there was malformed data by looking at
> the data itself. If there are not that many records for gid = 609 maybe a
> simple select of tiles would be sufficient. Otherwise maybe a simple
> plpgsql function that loops through the records applying jsonb_array_length
> and raising a notice on the error. In any case the point is to identify the
> presence of malformed data and if present the nature of the malformation.
> That would help reverse engineer any issues with below.
>
>
>> CREATE OR REPLACE FUNCTION words_play_game(
>> in_uid integer,
>> in_gid integer,
>> in_tiles jsonb
>> ) RETURNS table (
>> out_uid integer, -- the player to be notified
>> out_fcm text,
>> out_apns text,
>> out_adm text,
>> out_body text
>> ) AS
>>
>
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);
}
}