Timescaledb: check constraint violation when trying to update time column

[brintal]

Since yesterday, I'm getting the following error on some inserts into my timescaledb enabled table:

Caused by: org.postgresql.util.PSQLException: ERROR: new row for relation "_hyper_4_33_chunk" violates check constraint "constraint_33" Detail: Failing row contains (1312926, 0.000272209999999999998, 0.000271579999999999998, 0.000269160000000000005, 0.000287760000000000023, 0.000271289999999999986, 0.000268090000000000017, 0.000272380000000000018, 0.000272070000000000016, 0.000271079999999999986, 0.000268239999999999993, 0.000307079999999999994, 0.000271990000000000003, 0.00027074, 0.000271899999999999995, 0.000270420000000000003, 0.000273240000000000006, 0.00027231, 0.000271799999999999993, 0.000270270000000000026, 0.00027525999999999999, 0.000232079999999999987, 0.000271689999999999996, 0.000269629999999999978, 0.00028276000000000001, 0.000272400000000000008, ONTBTC, 2018-03-29 14:18:58.192+02).

Why is timescaledb trying to insert the row into the wrong chunk?
Can it be possible, that I triggered it by altering the time column AFTER I created the hypertable with data already inside?
ALTER TABLE trade_stat ALTER time TYPE TIMESTAMPTZ USING time AT TIME ZONE 'UTC';
The column time was of type TIMESTAMP (without time zone) at the time of hypertable creation.

I'm just confused because it running for several weeks without issues until yesterday and the table in question processed several million inserts until the error appeared the first time.

Any help highly appreciated! thx!

[mfreed]

Hi @brintal that change does seem like it's potentially dangerous. Offhand, it seems like the problem could be related to that.

What version of TimescaleDB are you running?

[brintal]

@mfreed Thanks alot for your assessment.
I'm running Version: 0.9.0~ubuntu16.04+3

[mfreed]

@brintal Just to clarify: Had you upgraded the DB or changed anything else since performing that ALTER command? And did something change "yesterday" when you started seeing the errors?

[brintal]

@mfreed
Thanks for your answer. No, nothing changed.
Now I tried updating to timescale 0.9.1, creating a completely new hypertable (with initial time column type timestamptz) and migrating the data from the old table into the new one.
The data migration worked fine, but afterwards when trying to insert new data, the constraint violation still happens sometimes.
Is it possible that timescaledb has issues with timestamptz?
My next step would be to use int8 and save the time data as epoch millis.

[brintal]

@mfreed
I think I found what the problem was. Because of an error in my application logic, I wasn't inserting a new row but rather updating an existing row, including the time column.
The new time was outside the chunk boundaries and hence the constraint violation.
So I guess it's not possible to update the time of a hypertable entry after the initial insert. Is that correct?

[mfreed]

@brintal Thanks for the clarification.

There's actually a corner case here that's partly due to implementation details: You can update a timestamp unless the change would cause it to no longer satisfy it's current chunk's constraints.

Example: Imagine you have a chunk interval of a day (and for simplicity, assume midnight to midnight). You are free to update the timestamp within that 24hour day. But if you cross a chunk boundary, say, trying to update 11:50pm to 12:05am, it will return an error: the "new" timestamp violates its existing chunk's constraints, and we don't currently support moving the row across chunks as part of the update. This also explains why it only occurs occasionally.

That said, and as a workaround if this is required, we recommend catching such errors, then redo-ing the operation (transactionally) via a DELETE then a subsequent INSERT.

Sorry for the confusion.

[brintal]

@mfreed
ok. thanks alot for your help and this awesome product!
maybe adding this limitation to the UPDATE section in the docs might be helpful for others running into the same problem:
https://docs.timescale.com/v0.9/using-timescaledb/writing-data#update

I will close this issue now.

[abramjstamper]

Another potential work around (that's less destructive that removing and reinserting) could be adding another timestamp (eg: upload_timestamp for streaming data), and chunking based on that timestamp. In theory, upload_timestamp should never change; while an event_timestamp has the potential to change. I also think the upload_timestamp is more predictable to set up chunk sizing correctly. Then, you should be able to update your event_timestamp. I'm not sure if this is applicable in your case, but we are using the Timescale for streaming data, so keeping track of when we receive it is important for us.

So sorry to be posting on this thread back from April. I just came across it, and it wasn't locked.