search

mbannert / timeseriesdb

{timeseriesdb} Manage Time Series with R and PostgreSQL
GNU General Public License v3.0
23 stars 3 forks source link

`db_ts_read` Error: Syntax Error at or Near Date on One Device #266

Open GigiHadid97 opened 1 month ago

GigiHadid97 commented 1 month ago

Hello everyone,

I'm encountering an issue with the db_ts_read function, but it's only happening on one of my two devices. The error I'm receiving is:

>   data_ts <- db_ts_read(con, ts_keys = names(keys), valid_on = Sys.Date()) 
Fehler: Failed to prepare query : ERROR:  syntax error at or near "'2024-08-19'" 
LINE 1: select * from "timeseries"ts_read_raw('2024-08-19'::date, FA... 
                                              ^

On my other device, this same code runs without any problems. Both devices are using the same package version: 1.0.0.1.1.2. Despite this, I can't figure out what's causing the issue on the affected device.

Has anyone encountered a similar problem or have any ideas on what might be going wrong? I've tried various troubleshooting steps but haven't had any luck so far. Any help or suggestions would be greatly appreciated!

Thank you!

mbannert commented 1 week ago

It's hard to debug with just this information, but here are a few things from the top of my head:

  1. The SQL line look definitely wrong. It should be select * from "timeseries.ts_read_raw('2024-08-19'::date ... because it's always schema.function.

It might be though the error message is not brough to R in correctly formatted fashion. One option would be to start the debugger for db_ts_read like debug(db_ts_read) and track down where exactly it fails.

  1. Second, you can use a Postgres console and try to run the SQL query directly once you have extracted the query that R runs.

  2. Possible difference between machines: Not only at the R level, but make sure to also check your libpq and psql client versions.

Does this help?

Kjir commented 1 week ago

This has to do with the latest version of RPostgres, that despite increasing just the patch version, has a breaking change which leads to this issue. The problem is described here