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

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(dot)klaver(at)aklaver(dot)com