Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar - Mailing list pgsql-general
| From | Alexander Farber |
|---|---|
| Subject | Re: jsonb_array_length: ERROR: 22023: cannot get array length of a scalar |
| Date | |
| Msg-id | CAADeyWi25Ts_3q8d62CAHvBW8TdsROe-jOVQGSDPB-8qcUXF7g@mail.gmail.com Whole thread Raw |
| In response to | Re: jsonb_array_length: ERROR: 22023: cannot get array length of ascalar (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: jsonb_array_length: ERROR: 22023: cannot get array length of ascalar
Re: jsonb_array_length: ERROR: 22023: cannot get array length of ascalar |
| List | pgsql-general |
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.klaver@aklaver.com> wrote:
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":
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":
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":
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":
I just pass as the 3rd argument in_tiles to my stored function: '[{"col":0,"letter":"К","row":
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;
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
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);
}
}
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);
}
}
pgsql-general by date: