[Bug]: Before Update trigger on hypertable make updates fail

[edgarzamora]

What type of bug is this?

Crash, Unexpected error

What subsystems and features are affected?

Query executor

What happened?

We are currently in the process of integrating TimescaleDB into our system, and for the most part, everything has been functioning smoothly. However, we have encountered a bug within our hypertable that appears to be triggered by specific data scenarios, and despite our efforts, we have been unable to pinpoint the root cause.

Since we are not sure about the root cause of it, and the problem does not appear always which makes us think that is a data-dependant issue, we provide here a dump of an small example which consistently reproduces the issue. The dump file is in the next zip: dump_file.zip

The issue revolves around a hypertable containing multiple fields, including a crucial column named effective_date_time which serves as our partition key. Additionally, we utilise standard columns such as created_at and modified_at, with the latter being updated whenever modifications occur to ensure data accuracy. To achieve this, we implemented a before_update trigger on the hypertable, as illustrated below:

CREATE OR REPLACE FUNCTION update_modified_at() 
RETURNS TRIGGER 
LANGUAGE PLPGSQL AS $$ 
BEGIN 
    NEW.modified_at = NOW(); 
    RETURN NEW; 
END; $$

Just in case, see here the create trigger command:

CREATE TRIGGER example_table_before_update_sync_modified_at
BEFORE UPDATE ON example_table
FOR EACH ROW
EXECUTE PROCEDURE update_modified_at();

Unfortunately, we have recently encountered an error message when attempting updates on this table, specifically:
[2024-04-24 11:15:03] [XX000] ERROR: attempted to lock invisible tuple.

For instance, the update query provided below triggers this error:

UPDATE example_table SET measurement = measurement + 2
WHERE example_table.effective_date_time >= '2020-01-01T00:00:00'::timestamp AT TIME ZONE 'UTC'

Our investigations into this error led us to references such as https://postgrespro.com/list/thread-id/1502244, alluding to its resolution in Postgres version 15. Despite performing maintenance tasks such as VACUUM and REINDEX on the example_table table, as well as restarting our Postgres instance, we have been unable to resolve the issue.

TimescaleDB version affected

2.14.2

PostgreSQL version used

15.6

What operating system did you use?

Redhat 8 and image timescale/timescaledb:2.14.0-pg15

What installation method did you use?

Docker

What platform did you run on?

Amazon Web Services (AWS), Other

Relevant log output and stack trace

2024-04-24 14:31:37.863 UTC [87] ERROR:  attempted to lock invisible tuple
2024-04-24 14:31:37.863 UTC [87] STATEMENT:  UPDATE example_table SET measurement = measurement + 2
        WHERE example_table.effective_date_time >= '2020-01-01T00:00:00'::timestamp AT TIME ZONE 'UTC'

How can we reproduce the bug?

Create an empty database and a user with access right on it and load the dump as following:


psql --quiet --username USERNAME --database DATABASE < dump.sql

The dump.sql file content is attached above: dump_file.zip

Then try to run the next query:

UPDATE example_table SET measurement = measurement + 2
WHERE example_table.effective_date_time >= '2020-01-01T00:00:00'::timestamp AT TIME ZONE 'UTC'

[antekresic]

Thanks for the detailed repro script! I'm looking into this, will let you know when I have something.

[evgenith]

Oh, we forgot to mention something: Dumping the data in example_table to CSV using COPY TO, truncating / deleting and re-creating the table, and importing the data back using COPY FROM makes the problem go away. This is one of the reasons why we think this is data-dependent (possibly dependant on insert order, but we haven't managed to pin that down).

[antekresic]

Yeah, I have narrowed down the issue and will be making a PR containing the fix.

Thanks for the report!

[edgarzamora]

That's good news! Thanks @antekresic!

Meanwhile you are working on it, can you give some summarised explanation of the root cause and if you have any suggestion of a workaround, if possible. I guess you will include this info on the PR description when you finish, so please link the PR with this issue. Do you have an ETA? and in which release you think the fix can be included?

For now, the only we can think is to disable the update trigger and handle the synchronisation of the modified_at at the application layer, as it shows us that the problem disappear, but without knowing the root cause we don't know if actually it is fixing the issue or is just coincidence.