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

2018-03-02 Thread Adrian Klaver 

On 03/02/2018 10:58 AM, Alexander Farber wrote:

I see, thank you for your comments, David and Adrian.

In the "tiles" column actually save either the JSON array of tiles - 
when the user plays them


Or a string (which is jsonb too) concatenated of letters - when the user 
swaps the letters.


Maybe I should rethink my table structure (I just want to "log" all 
plays, swaps, skips, resigns in the words_moves table)...


Or make the string the value of an array:

[{"swap": "ТСНЦУЭ"}]

so you are not changing the inner JSON in the field.



Or maybe I should always check for the "action" column first (acts as 
enum) - before accessing "tiles" column


Regrads
Alex




--
Adrian Klaver
adrian.kla...@aklaver.com

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

2018-03-02 Thread David G. Johnston 
On Fri, Mar 2, 2018 at 11:58 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> I see, thank you for your comments, David and Adrian.
>
> In the "tiles" column actually save either the JSON array of tiles - when
> the user plays them
>
> Or a string (which is jsonb too) concatenated of letters - when the user
> swaps the letters.
>
> Maybe I should rethink my table structure (I just want to "log" all plays,
> swaps, skips, resigns in the words_moves table)...
>
> Or maybe I should always check for the "action" column first (acts as
> enum) - before accessing "tiles" column
>
>
​A table constraint like:

CASE WHEN action = 'Play'
   THEN lengh < 0
   WHEN action = 'Swap'
   THEN ...
   ELSE false
END

Is perfectly fine - though having actual non-null values take on different
meanings based upon the enum is generally not a good idea.  I've been using
the above to enforce conditional not null constraints when I don't want to
implement explicit inheritance​.

David J.

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

2018-03-02 Thread Alexander Farber 
I see, thank you for your comments, David and Adrian.

In the "tiles" column actually save either the JSON array of tiles - when
the user plays them

Or a string (which is jsonb too) concatenated of letters - when the user
swaps the letters.

Maybe I should rethink my table structure (I just want to "log" all plays,
swaps, skips, resigns in the words_moves table)...

Or maybe I should always check for the "action" column first (acts as enum)
- before accessing "tiles" column

Regrads
Alex

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

2018-03-02 Thread Adrian Klaver 

On 03/02/2018 10:43 AM, Alexander Farber wrote:

# select * from words_moves where gid=609 limit 3;
-[ RECORD 1 
]

mid    | 1040
action | play
gid    | 609
uid    | 1192
played | 2018-03-02 10:13:57.943876+01
tiles  | [{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4, 
"row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2, 
"letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col": 
7, "row": 7, "value": 3, "letter": "Я"}]

score  | 10
-[ RECORD 2 
]

mid    | 1041
action | play
gid    | 609
uid    | 7
played | 2018-03-02 10:56:58.72503+01
tiles  | [{"col": 3, "row": 8, "value": 2, "letter": "В"}, {"col": 3, 
"row": 9, "value": 1, "letter": "И"}, {"col": 3, "row": 10, "value": 2, 
"letter": "Т"}, {"col": 3, "row": 11, "value": 2, "letter": "К"}, 
{"col": 3, "row": 12, "value": 1, "letter": "А"}]

score  | 14
-[ RECORD 3 
]

mid    | 1043
action | play
gid    | 609
uid    | 1192
played | 2018-03-02 11:03:58.614094+01
tiles  | [{"col": 0, "row": 10, "value": 2, "letter": "С"}, {"col": 1, 
"row": 10, "value": 2, "letter": "М"}, {"col": 2, "row": 10, "value": 1, 
"letter": "О"}, {"col": 4, "row": 10, "value": 2, "letter": "Р"}]

score  | 13

#  select column_name, data_type from information_schema.columns where 
table_name='words_moves';

  column_name |        data_type
-+--
  mid         | bigint
  action      | text
  gid         | integer
  uid         | integer
  played      | timestamp with time zone
  tiles       | jsonb
  score       | integer
(7 rows)

#  select jsonb_array_length(tiles) from words_moves where gid=609 limit 3;
  jsonb_array_length

                   5
                   5
                   4
(3 rows)

BUT:

#  select 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


Which means only some data is bad, but how to find it please?


What is? :

select count(*) from words_moves where gid=609;

A simplistic approach would be:

select mid, jsonb_array_length(tiles) from words_moves where gid=609 
order by mid limit x;


where you  increment x until you trigger the error.

A more sophisticated approach would be to use plpgsql EXCEPTION handling:

https://www.postgresql.org/docs/10/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

and create a function that loops through:

select jsonb_array_length(tiles) from words_moves where gid=609 ;

and RAISES a NOTICE for each incorrect value along with its mid value.










--
Adrian Klaver
adrian.kla...@aklaver.com

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

2018-03-02 Thread David G. Johnston 
On Fri, Mar 2, 2018 at 11:48 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> Oops, I've got strings there too - when swapping instead of playing tiles:
>
>
​You should probably add:

jsonb_array_length(tiles) > 0

as a check constraint on column
​
David J.

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

2018-03-02 Thread Alexander Farber 
Oops, I've got strings there too - when swapping instead of playing tiles:

#  select * from words_moves where gid=609 and action <> 'play';
 mid  | action | gid | uid  |played |  tiles   |
score
--++-+--+---+--+---
 1063 | swap   | 609 | 1192 | 2018-03-02 14:13:24.684301+01 | "ТСНЦУЭ" |
 ¤
 1067 | swap   | 609 | 1192 | 2018-03-02 15:31:14.378474+01 | "ЕЯУЕФП" |
 ¤
 1068 | swap   | 609 |7 | 2018-03-02 15:52:07.629119+01 | "ОЕЕАУ"  |
 ¤
 1072 | swap   | 609 |7 | 2018-03-02 16:06:43.365012+01 | "ЕЕЫ"|
 ¤
 1076 | swap   | 609 |7 | 2018-03-02 16:20:18.933948+01 | "Ъ"  |
 ¤
(5 rows)

So my problem was simple. I am sorry for the numerous mails!

(it is just such a long chain - android/html5 - Jetty - PostgreSQL, so that
sometimes I am confused.
At least I am glad to have most of my logic as Pl/pgSQL right at the data)

Regards
Alex

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

2018-03-02 Thread Alexander Farber 
# select * from words_moves where gid=609 limit 3;
-[ RECORD 1
]
mid| 1040
action | play
gid| 609
uid| 1192
played | 2018-03-02 10:13:57.943876+01
tiles  | [{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4,
"row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2,
"letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col": 7,
"row": 7, "value": 3, "letter": "Я"}]
score  | 10
-[ RECORD 2
]
mid| 1041
action | play
gid| 609
uid| 7
played | 2018-03-02 10:56:58.72503+01
tiles  | [{"col": 3, "row": 8, "value": 2, "letter": "В"}, {"col": 3,
"row": 9, "value": 1, "letter": "И"}, {"col": 3, "row": 10, "value": 2,
"letter": "Т"}, {"col": 3, "row": 11, "value": 2, "letter": "К"}, {"col":
3, "row": 12, "value": 1, "letter": "А"}]
score  | 14
-[ RECORD 3
]
mid| 1043
action | play
gid| 609
uid| 1192
played | 2018-03-02 11:03:58.614094+01
tiles  | [{"col": 0, "row": 10, "value": 2, "letter": "С"}, {"col": 1,
"row": 10, "value": 2, "letter": "М"}, {"col": 2, "row": 10, "value": 1,
"letter": "О"}, {"col": 4, "row": 10, "value": 2, "letter": "Р"}]
score  | 13

#  select column_name, data_type from information_schema.columns where
table_name='words_moves';
 column_name |data_type
-+--
 mid | bigint
 action  | text
 gid | integer
 uid | integer
 played  | timestamp with time zone
 tiles   | jsonb
 score   | integer
(7 rows)

#  select jsonb_array_length(tiles) from words_moves where gid=609 limit 3;
 jsonb_array_length

  5
  5
  4
(3 rows)

BUT:

#  select 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


Which means only some data is bad, but how to find it please?

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

2018-03-02 Thread Alexander Farber 
Good evening -

On Fri, Mar 2, 2018 at 7:11 PM, Adrian Klaver 
wrote:

>
>> 2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.058 ms  bind
>> : 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)
>>
>> where is $3::jsonb coming from?
>>
>>
>> I was thinking more about the ultimate source of the data. The
> words_play_game() function, AFAICT, just passes the jsonb from input into
> the word_moves table. If that is the case the problem may occur further
> upstream where the jsonb array is actually built.
>
>
that argument comes over Websocket - either from my Android app,
or from my HTML5 game at https://slova.de/words/Words.js (just an array of
objects, stringified):

var tiles = [];
for (var i = boardTiles.length - 1;
i >= 0; i--) {
var tile = boardTiles[i];
tiles.push({
letter: tile.letter,
value:  tile.value,
col:tile.col,
row:tile.row
});
}

var play = {
social:  SOCIAL,
sid: SID,
auth:AUTH,
action:  'play',
gid: gid,
tiles:   tiles
};
ws.send(JSON.stringify(play));

Regards
Alex

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

2018-03-02 Thread Adrian Klaver 

On 03/02/2018 10:04 AM, David G. Johnston wrote:
On Friday, March 2, 2018, Adrian Klaver > wrote:


On 03/02/2018 06:42 AM, Alexander Farber wrote:



2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.058 ms  bind
: 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)

where is $3::jsonb coming from?


Java prepared statement I think, using setString.


I was thinking more about the ultimate source of the data. The 
words_play_game() function, AFAICT, just passes the jsonb from input 
into the word_moves table. If that is the case the problem may occur 
further upstream where the jsonb array is actually built.




Not at computer right now but what does:

'[1,2,3]'::text::jsonb result in compared to '[1,2,3]'::jsonb ?

Because the system think your array-looking string is actually just a 
scalar that happens to have a leading [ and a trailing ]


David J.




--
Adrian Klaver
adrian.kla...@aklaver.com

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

2018-03-02 Thread David G. Johnston 
On Friday, March 2, 2018, Adrian Klaver  wrote:

> On 03/02/2018 06:42 AM, Alexander Farber wrote:
>
>>
>>
> 2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.058 ms  bind
> : 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)
>
> where is $3::jsonb coming from?
>
>
Java prepared statement I think, using setString.

Not at computer right now but what does:

'[1,2,3]'::text::jsonb result in compared to '[1,2,3]'::jsonb ?

Because the system think your array-looking string is actually just a
scalar that happens to have a leading [ and a trailing ]

David J.

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

2018-03-02 Thread Adrian Klaver 

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 -




Another thought, in :

2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.058 ms  bind 
: 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)


where is $3::jsonb coming from?


--
Adrian Klaver
adrian.kla...@aklaver.com

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

2018-03-02 Thread Adrian Klaver 

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

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

2018-03-02 Thread Alexander Farber 
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 
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
: 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
: 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 : 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);
}
}

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

2018-03-02 Thread Adrian Klaver 

On 03/02/2018 06:14 AM, Alexander Farber wrote:

Hi Adrian, thank you for the reply -




#  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

What am I doing wrong here please?


Are you sure all the values in tiles are correctly formatted because
when I use jsonb_array_length with the provided data:

test=# select jsonb_array_length( '[{"col": 3, "row": 7, "value": 2,
"letter": "С"}, {"col": 4, "row": 7, "value": 1, "letter": "О"},
{"col": 5, "row": 7, "value": 2, "letter": "П"}, {"col": 6, "row":
7, "value": 0, "letter": "Л"}, {"col": 7, "row": 7, "value": 3,
"letter": "Я"}]');
  jsonb_array_length

                   5


I fill that table with the following stored function (please pardon the 
huge listing):


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




--
Adrian Klaver
adrian.kla...@aklaver.com

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

2018-03-02 Thread Alexander Farber 
And here is how I call my stored function -

https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914

- why wouldn't it store a jsonb array in the tiles column of words_moves
table?

2018-03-02 15:29:42.644 CET [16693] LOG:  statement: DISCARD ALL
2018-03-02 15:29:42.644 CET [16693] LOG:  duration: 0.015 ms
2018-03-02 15:30:33.645 CET [16693] LOG:  statement: select 1
2018-03-02 15:30:33.645 CET [16693] LOG:  duration: 0.094 ms
2018-03-02 15:30:33.645 CET [16693] LOG:  statement: SET DateStyle='ISO';
2018-03-02 15:30:33.645 CET [16693] LOG:  duration: 0.050 ms
2018-03-02 15:30:33.645 CET [16693] LOG:  duration: 0.021 ms  parse
: SET extra_float_digits = 3
2018-03-02 15:30:33.645 CET [16693] LOG:  duration: 0.003 ms  bind
: SET extra_float_digits = 3
2018-03-02 15:30:33.645 CET [16693] LOG:  execute : SET
extra_float_digits = 3
2018-03-02 15:30:33.645 CET [16693] LOG:  duration: 0.006 ms
2018-03-02 15:30:33.645 CET [16693] LOG:  duration: 0.007 ms  parse
: SET application_name = 'PostgreSQL JDBC Driver'
2018-03-02 15:30:33.645 CET [16693] LOG:  duration: 0.002 ms  bind
: SET application_name = 'PostgreSQL JDBC Driver'
2018-03-02 15:30:33.645 CET [16693] LOG:  execute : SET
application_name = 'PostgreSQL JDBC Driver'
2018-03-02 15:30:33.645 CET [16693] LOG:  duration: 0.005 ms
2018-03-02 15:30:33.646 CET [16693] LOG:  duration: 0.110 ms  parse
: 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
: 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 : 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.653 CET [16693] LOG:  duration: 7.567 ms

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

2018-03-02 Thread Alexander Farber 
Hi Adrian, thank you for the reply -

On Fri, Mar 2, 2018 at 3:05 PM, Adrian Klaver 
wrote:

> On 03/02/2018 05:52 AM, Alexander Farber wrote:
>
>>
>> in PostgreSQL 10.3 I have the following table with a jsonb column:
>>
>> #  \d words_moves;
>>   Table "public.words_moves"
>>   Column |   Type   | Collation | Nullable |
>> Default
>> +--+---+--+-
>> -
>>   mid| bigint   |   | not null |
>> nextval('words_moves_mid_seq'::regclass)
>>   action | text |   | not null |
>>   gid| integer  |   | not null |
>>   uid| integer  |   | not null |
>>   played | timestamp with time zone |   | not null |
>>   tiles  | jsonb|   |  |
>>   score  | integer  |   |  |
>> Indexes:
>>  "words_moves_pkey" PRIMARY KEY, btree (mid)
>> Check constraints:
>>  "words_moves_score_check" CHECK (score >= 0)
>> Foreign-key constraints:
>>  "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
>> ON DELETE CASCADE
>>  "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
>> ON DELETE CASCADE
>> Referenced by:
>>  TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY
>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>>  TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>>
>>
>> #  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
>>
>> What am I doing wrong here please?
>>
>
> Are you sure all the values in tiles are correctly formatted because when
> I use jsonb_array_length with the provided data:
>
> test=# select jsonb_array_length( '[{"col": 3, "row": 7, "value": 2,
> "letter": "С"}, {"col": 4, "row": 7, "value": 1, "letter": "О"}, {"col": 5,
> "row": 7, "value": 2, "letter": "П"}, {"col": 6, "row": 7, "value": 0,
> "letter": "Л"}, {"col": 7, "row": 7, "value": 3, "letter": "Я"}]');
>  jsonb_array_length
> 
>   5
>
>
I fill that table with the following stored function (please pardon the
huge listing):

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
$func$
DECLARE
_tile jsonb;
_letter   char;
_valueinteger;
_col  integer;
_row  integer;
_pos  integer;
_mid  bigint;
_totalinteger;
_hand_len integer;
_pile_len integer;
_move_len integer;
_pile char[];
_hand char[];
_letters  char[][];
_values   integer[][];
_opponent integer;
_finished timestamptz;
_reason   text;
_score1   integer;
_score2   integer;
BEGIN
IF EXISTS (SELECT 1 FROM words_users
WHERE uid = in_uid AND
banned_until > CURRENT_TIMESTAMP) THEN
RAISE EXCEPTION 'User % is banned', in_uid;
END IF;

-- fetch the 4 arrays (_hand, _pile, _letters, _values) for the
current game
SELECT
hand1,
pile,
letters,
values
INTO
_hand,
_pile,
_letters,
_values
FROM words_games WHERE
gid = in_gid AND
player1 = in_uid AND
-- game is not over yet
finished IS NULL AND
-- and it is first player's turn
(played1 IS NULL OR played1 < played2);

IF NOT FOUND THEN
SELECT
hand2,
pile,
letters,
values
INTO
_hand,
_pile,
_letters,
_values
FROM words_games 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);
END IF;

IF NOT FOUND THEN
RAISE EXCEPTION 'Game % not found for user %', in_gid,
in_uid;

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

2018-03-02 Thread Adrian Klaver 

On 03/02/2018 05:52 AM, Alexander Farber wrote:

Good afternoon,

in PostgreSQL 10.3 I have the following table with a jsonb column:

#  \d words_moves;
                                      Table "public.words_moves"
  Column |           Type           | Collation | Nullable | 
     Default

+--+---+--+--
  mid    | bigint                   |           | not null | 
nextval('words_moves_mid_seq'::regclass)

  action | text                     |           | not null |
  gid    | integer                  |           | not null |
  uid    | integer                  |           | not null |
  played | timestamp with time zone |           | not null |
  tiles  | jsonb                    |           |          |
  score  | integer                  |           |          |
Indexes:
     "words_moves_pkey" PRIMARY KEY, btree (mid)
Check constraints:
     "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
     "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES 
words_games(gid) ON DELETE CASCADE
     "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES 
words_users(uid) ON DELETE CASCADE

Referenced by:
     TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY 
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
     TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY 
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE


Here are some records (please pardon the non-english chars):

# select * from words_moves where gid=609 limit 3;
-[ RECORD 1 
]

mid    | 1040
action | play
gid    | 609
uid    | 1192
played | 2018-03-02 10:13:57.943876+01
tiles  | [{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4, 
"row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2, 
"letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col": 
7, "row": 7, "value": 3, "letter": "Я"}]

score  | 10
-[ RECORD 2 
]

mid    | 1041
action | play
gid    | 609
uid    | 7
played | 2018-03-02 10:56:58.72503+01
tiles  | [{"col": 3, "row": 8, "value": 2, "letter": "В"}, {"col": 3, 
"row": 9, "value": 1, "letter": "И"}, {"col": 3, "row": 10, "value": 2, 
"letter": "Т"}, {"col": 3, "row": 11, "value": 2, "letter": "К"}, 
{"col": 3, "row": 12, "value": 1, "letter": "А"}]

score  | 14
-[ RECORD 3 
]

mid    | 1043
action | play
gid    | 609
uid    | 1192
played | 2018-03-02 11:03:58.614094+01
tiles  | [{"col": 0, "row": 10, "value": 2, "letter": "С"}, {"col": 1, 
"row": 10, "value": 2, "letter": "М"}, {"col": 2, "row": 10, "value": 1, 
"letter": "О"}, {"col": 4, "row": 10, "value": 2, "letter": "Р"}]

score  | 13

I would like to get the length of the tiles array (because in my word 
game 7 played tiles mean +15 score bonus) - but that call fails for some 
reason:


#  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

What am I doing wrong here please?


Are you sure all the values in tiles are correctly formatted because 
when I use jsonb_array_length with the provided data:


test=# select jsonb_array_length( '[{"col": 3, "row": 7, "value": 2, 
"letter": "С"}, {"col": 4, "row": 7, "value": 1, "letter": "О"}, {"col": 
5, "row": 7, "value": 2, "letter": "П"}, {"col": 6, "row": 7, "value": 
0, "letter": "Л"}, {"col": 7, "row": 7, "value": 3, "letter": "Я"}]');

 jsonb_array_length

  5

test=# select jsonb_array_length( '[{"col": 3, "row": 8, "value": 2, 
"letter": "В"}, {"col": 3, "row": 9, "value": 1, "letter": "И"}, {"col": 
3, "row": 10, "value": 2, "letter": "Т"}, {"col": 3, "row": 11, "value": 
2, "letter": "К"}, {"col": 3, "row": 12, "value": 1, "letter": "А"}]');

 jsonb_array_length

  5

test=# select jsonb_array_length('[{"col": 0, "row": 10, "value": 2, 
"letter": "С"}, {"col": 1, "row": 10, "value": 2, "letter": "М"}, 
{"col": 2, "row": 10, "value": 1, "letter": "О"}, {"col": 4, "row": 10, 
"value": 2, "letter": "Р"}]');
 jsonb_array_length 



 



  4

it works.

The error message would suggest there is data in tiles w

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

2018-03-02 Thread Alexander Farber 
Good afternoon,

in PostgreSQL 10.3 I have the following table with a jsonb column:

#  \d words_moves;
 Table "public.words_moves"
 Column |   Type   | Collation | Nullable |
Default
+--+---+--+--
 mid| bigint   |   | not null |
nextval('words_moves_mid_seq'::regclass)
 action | text |   | not null |
 gid| integer  |   | not null |
 uid| integer  |   | not null |
 played | timestamp with time zone |   | not null |
 tiles  | jsonb|   |  |
 score  | integer  |   |  |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_daily" CONSTRAINT "words_daily_mid_fkey" FOREIGN KEY (mid)
REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE

Here are some records (please pardon the non-english chars):

# select * from words_moves where gid=609 limit 3;
-[ RECORD 1
]
mid| 1040
action | play
gid| 609
uid| 1192
played | 2018-03-02 10:13:57.943876+01
tiles  | [{"col": 3, "row": 7, "value": 2, "letter": "С"}, {"col": 4,
"row": 7, "value": 1, "letter": "О"}, {"col": 5, "row": 7, "value": 2,
"letter": "П"}, {"col": 6, "row": 7, "value": 0, "letter": "Л"}, {"col": 7,
"row": 7, "value": 3, "letter": "Я"}]
score  | 10
-[ RECORD 2
]
mid| 1041
action | play
gid| 609
uid| 7
played | 2018-03-02 10:56:58.72503+01
tiles  | [{"col": 3, "row": 8, "value": 2, "letter": "В"}, {"col": 3,
"row": 9, "value": 1, "letter": "И"}, {"col": 3, "row": 10, "value": 2,
"letter": "Т"}, {"col": 3, "row": 11, "value": 2, "letter": "К"}, {"col":
3, "row": 12, "value": 1, "letter": "А"}]
score  | 14
-[ RECORD 3
]
mid| 1043
action | play
gid| 609
uid| 1192
played | 2018-03-02 11:03:58.614094+01
tiles  | [{"col": 0, "row": 10, "value": 2, "letter": "С"}, {"col": 1,
"row": 10, "value": 2, "letter": "М"}, {"col": 2, "row": 10, "value": 1,
"letter": "О"}, {"col": 4, "row": 10, "value": 2, "letter": "Р"}]
score  | 13

I would like to get the length of the tiles array (because in my word game
7 played tiles mean +15 score bonus) - but that call fails for some reason:

#  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

What am I doing wrong here please?

Regards
Alex