Re: jsonb_array_length: ERROR: 22023: cannot get array length of ascalar - Mailing list pgsql-general

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@aklaver.com