Some prepared statements originating from ruby-pg gem result in errors on QuestDB 8.2.0

Community
1

As of the 8.2.0 release some prepared statements originating from the ruby-pg gem are generating errors. The error specifically occurs when attempting to use a bind parameter in conjunction with a LIMIT statement. I have created a reproduction repository: GitHub - edsinclair/questdb_ruby_pg_prepared_statements: Reproduction repository for issues with using prepared statements from ruby-pg gem with QuestDb 8.2.0 But the underlying issue might be in the ruby-pg gem rather than QuestDB as I cannot reproduce the issue using psycopg. This is the error in the QuestDb log:

2024-12-02 18:13:28 2024-12-02T17:13:28.466637Z E i.q.g.e.QueryProgress err [id=30, sql=`SELECT "events".* FROM "events" WHERE "events"."name" = $1 AND "events"."facility" = $2 AND "events"."start_ts" BETWEEN $3 AND $4 LIMIT $5`, principal=admin, cache=false, jit=false, time=94208, msg=undefined bind variable: 4, errno=0, pos=136]

There isnā€™t any indication of how ruby-pg might be passing the bind variables differently from psycopg in the logs. Any suggestions you can provide for further isolating this would be appreciated. Thanks.

2

Hi @edsinclair ,

Thanks for the clear repro, we will investigate!

We number bind variables starting with 0, therefore the undefined bind variable: 4 message should correspond to LIMIT $5 as you expected.

From your description, it sounds like this worked prior to 8.2.0? Or have you only tried this on that version? I ask because we released a new PG wire connector in that version so if it is limited to 8.2.0, there might be a strange interaction.

Also, has there been any change in ruby-pg version? From your repro, it looks like you are using 1.5.9. I donā€™t think we routinely test against ruby-pg at the moment so there could be an impact from this too.

3

@nwoolmer Thanks for the quick reply. This just started happening when we upgraded to 8.2.0 over the weekend. As for ruby-pg version, I have observed the issue with 1.5.4 and 1.5.9. Strangely it does not occur when using equivalent prepared statement with psycopg. (Also included in the repro repository). Iā€™m not sure if it is relevant but the error does not occur if the SQL statement does not have a bind parameter for the LIMIT clause. I.E. ā€œā€¦LIMIT $5ā€ throws the error but the exact same statement with ā€œā€¦LIMIT 1ā€ does not throw the error even while the other bind variables are present. Subsequent to this reproduction I have also observed the error when attempting to use bind variables with a WHERE IN ($1,ā€¦) clause. (only on 8.2.0)

4

This is certainly strange. A query demonstrating the IN issue would be great too, as we can make sure both of the queries are test cases for any bugfix.

Also, try again with pg.legacy.mode.enabled=true. If this resolves the issue, then we have narrowed it down to something in the new connector and that unblocks you.

If not, then your options in the meantime are to interpolate the dodgy bind vars directly into the SQL string, or downgrade versions. A version downgrade should be safe from a storage perspective, if neccessary.

5

I added an example demonstrating the IN issue. I added it to both the ruby-pg and the psycopg test case and that error is present when running against QuestDb 8.2.0 for both clients.

6

It is getting late here. Iā€™ll try pg.legacy.mode.enabled=true tomorrow and report back. In the meantime Iā€™ve pushed a workaround that temporarily interpolates the bind variables since this application is not an external application. Thanks.

7

I just tried enabling pg.legacy.mode.enabled=true and that resolved the errors for both ruby-pg and psycopg. So it appears to be an issue in the new connector. Let me know if there is anything else I can do to help narrow it down further.

8

Thanks @edsinclair !

We released 8.2.1 yesterday which contained a couple of bugfixes around PG wire, including prepared statements. You can try that version (and set pg.legacy.mode.enabled=false) and see if its resolved in that version.

In the meantime, Iā€™ll take this back to the team and see if thereā€™s an outstanding issue. Thanks for your persistence!

9

Nevermind, it still seems problematic on 8.2.1. Thanks for your help and for the repro! Weā€™ll revert back when we have a fix.

1 Like
10

Yes. I just updated repro repository with 8.2.1 and included pg.legacy.mode.enabled=true result in the README. Thanks for your help with this.

11

This is a related PR to track that fixes some of the issues around IN. I donā€™t know if this will also resolve the ruby-pg with LIMIT issue just yet.

1 Like
12

Hello, I tested the fix with the ruby test case, and it works as expected.
Thanks to both @edsinclair and @nwoolmer!

15:02 $ ./test_case.rb 
QuestDB version 8.2-patch
2022-01-03 14:00:50.000000, URS17005, EGA-03Jan22@09_00_50, 2022-01-03 14:42:00.000000
2022-01-03 14:00:50.000000, URS17005, EGA-03Jan22@09_00_50, 2022-01-03 14:42:00.000000
2022-01-03 13:24:40.000000, URS17006, EGA-03Jan22@08_24_40, 2022-01-03 14:15:50.000000, 2022-01-03 14:00:50.000000, URS17005, EGA-03Jan22@09_00_50, 2022-01-03 14:42:00.000000
2 Likes
13

Thanks for sorting this out, @jerrinot !

1 Like
14

Thatā€™s great. Thanks for the update.

1 Like