Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Excessive cagg warnings in PostgreSQL log file #6387

Closed
bijaymandal362 opened this issue Dec 8, 2023 · 6 comments
Closed

Excessive cagg warnings in PostgreSQL log file #6387

bijaymandal362 opened this issue Dec 8, 2023 · 6 comments
Labels
bgw The background worker subsystem, including the scheduler bug continuous_aggregate no-activity waiting-for-author

Comments

@bijaymandal362
Copy link

What type of bug is this?

Performance issue

What subsystems and features are affected?

Other

What happened?

I moved to TimescaleDB release 2.12 and moves all internal functions from the _timescaledb_internal schema into the _timescaledb_functions schema but in my logs in every mins,
LOG: refreshing continuous aggregate "ts_connections_success_one_min" in window -
LOG: deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
LOG: inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"

all the internal functions changed to _timescaledb_functions but when continuous aggregate refreshing, why i am getting log, I mean why does it trigger this query?
Please help me, to solve this issue, why is it triggering those delete and insert queries from the materialization table "_timescaledb_internal._materialized_hypertable_25"?

TimescaleDB version affected

2.12

PostgreSQL version used

14

What operating system did you use?

Ubuntu 20.02.3 LTS

What installation method did you use?

Not applicable

What platform did you run on?

Not applicable

Relevant log output and stack trace

2023-12-07 22:52:58.062 CST [19960] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:53:00-06, 2023-12-07 22:51:00-06 ]
2023-12-07 22:52:58.069 CST [19960] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:52:58.070 CST [19960] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:53:57.121 CST [19965] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:54:00-06, 2023-12-07 22:52:00-06 ]
2023-12-07 22:53:57.128 CST [19965] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:53:57.130 CST [19965] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:53:58.081 CST [19966] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:54:00-06, 2023-12-07 22:52:00-06 ]
2023-12-07 22:53:58.086 CST [19966] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:53:58.087 CST [19966] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:54:57.147 CST [19971] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:55:00-06, 2023-12-07 22:53:00-06 ]
2023-12-07 22:54:57.152 CST [19971] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:54:57.153 CST [19971] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:54:58.099 CST [19972] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:55:00-06, 2023-12-07 22:53:00-06 ]
2023-12-07 22:54:58.104 CST [19972] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:54:58.105 CST [19972] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:55:57.179 CST [19977] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:56:00-06, 2023-12-07 22:54:00-06 ]
2023-12-07 22:55:57.186 CST [19977] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:55:57.188 CST [19977] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:55:58.118 CST [19978] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:56:00-06, 2023-12-07 22:54:00-06 ]
2023-12-07 22:55:58.123 CST [19978] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:55:58.125 CST [19978] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:56:57.202 CST [19983] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:57:00-06, 2023-12-07 22:55:00-06 ]
2023-12-07 22:56:57.208 CST [19983] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:56:57.213 CST [19983] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:56:58.138 CST [19984] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:57:00-06, 2023-12-07 22:55:00-06 ]
2023-12-07 22:56:58.142 CST [19984] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:56:58.143 CST [19984] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:57:57.256 CST [19989] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:58:00-06, 2023-12-07 22:56:00-06 ]
2023-12-07 22:57:57.265 CST [19989] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:57:57.268 CST [19989] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:57:58.165 CST [19990] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:58:00-06, 2023-12-07 22:56:00-06 ]
2023-12-07 22:57:58.171 CST [19990] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:57:58.173 CST [19990] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"

How can we reproduce the bug?

2023-12-07 22:52:58.062 CST [19960] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:53:00-06, 2023-12-07 22:51:00-06 ]
2023-12-07 22:52:58.069 CST [19960] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:52:58.070 CST [19960] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:53:57.121 CST [19965] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:54:00-06, 2023-12-07 22:52:00-06 ]
2023-12-07 22:53:57.128 CST [19965] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:53:57.130 CST [19965] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:53:58.081 CST [19966] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:54:00-06, 2023-12-07 22:52:00-06 ]
2023-12-07 22:53:58.086 CST [19966] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:53:58.087 CST [19966] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:54:57.147 CST [19971] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:55:00-06, 2023-12-07 22:53:00-06 ]
2023-12-07 22:54:57.152 CST [19971] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:54:57.153 CST [19971] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:54:58.099 CST [19972] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:55:00-06, 2023-12-07 22:53:00-06 ]
2023-12-07 22:54:58.104 CST [19972] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:54:58.105 CST [19972] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:55:57.179 CST [19977] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:56:00-06, 2023-12-07 22:54:00-06 ]
2023-12-07 22:55:57.186 CST [19977] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:55:57.188 CST [19977] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:55:58.118 CST [19978] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:56:00-06, 2023-12-07 22:54:00-06 ]
2023-12-07 22:55:58.123 CST [19978] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:55:58.125 CST [19978] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:56:57.202 CST [19983] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:57:00-06, 2023-12-07 22:55:00-06 ]
2023-12-07 22:56:57.208 CST [19983] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:56:57.213 CST [19983] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:56:58.138 CST [19984] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:57:00-06, 2023-12-07 22:55:00-06 ]
2023-12-07 22:56:58.142 CST [19984] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:56:58.143 CST [19984] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:57:57.256 CST [19989] LOG:  refreshing continuous aggregate "ts_connections_attempt_one_min" in window [ 2023-12-07 21:58:00-06, 2023-12-07 22:56:00-06 ]
2023-12-07 22:57:57.265 CST [19989] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:57:57.268 CST [19989] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_15"
2023-12-07 22:57:58.165 CST [19990] LOG:  refreshing continuous aggregate "ts_connections_success_one_min" in window [ 2023-12-07 21:58:00-06, 2023-12-07 22:56:00-06 ]
2023-12-07 22:57:58.171 CST [19990] LOG:  deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
2023-12-07 22:57:58.173 CST [19990] LOG:  inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
@bijaymandal362
Copy link
Author

job_id | proc_schema | proc_name | pid | sqlerrcode | err_message
--------+------------------------+------------------+-----+------------+-------------------------------------
1 | _timescaledb_functions | policy_telemetry | | | job crash detected, see server logs
(1 row)

~
~
~

@svenklemm
Copy link
Member

Hi @bijaymandal362,
none of those log messages you are seeging are due to timescaledb internal functions now living in the _timescaledb_functions schema. While we did move all our functions and procedures materialization hypertables and other internal tables still live in the _timescaledb_internal schema. Those log messages you are seeing are from cagg refreshed. Do you have a very aggressive cagg policy?

@svenklemm svenklemm changed the title [Bug]: <Title>TimescaleDB release 2.12 moves all internal functions from the _timescaledb_internal schema into the _timescaledb_functions schema. This results in TimescaleDB publishing excessive warning to the PostgreSQL log file when working with the database Excessive cagg warnings in PostgreSQL log file Dec 8, 2023
@bijaymandal362
Copy link
Author

Hi @svenklemm thanks,
why i am getting this log LOG: refreshing continuous aggregate "ts_connections_success_one_min" in window -
LOG: deleted 0 row(s) from materialization table "_timescaledb_internal._materialized_hypertable_25"
LOG: inserted 0 row(s) into materialization table "_timescaledb_internal._materialized_hypertable_25"
inserted and deleted,
i only want log when some rows get inserted or deleted, not for 0 rows inserted and deleted

@jflambert
Copy link

jflambert commented Dec 11, 2023 •
edited
Loading

Can you upgrade to 2.13.0 and see if your problems are gone?

Specifically #6161

@horzsolt horzsolt added waiting-for-author bug bgw The background worker subsystem, including the scheduler labels Dec 19, 2023
@github-actions GitHub Actions
Copy link

Dear Author,

This issue has been automatically marked as stale due to lack of activity. With only the issue description that is currently provided, we do not have enough information to take action. If you have or find the answers we would need, please reach out. Otherwise, this issue will be closed in 30 days.

Thank you!

@github-actions GitHub Actions
Copy link

Dear Author,

We are closing this issue due to lack of activity. Feel free to add a comment to this issue if you can provide more information and we will re-open it.

Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bgw The background worker subsystem, including the scheduler bug continuous_aggregate no-activity waiting-for-author
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@horzsolt @jflambert @svenklemm @bijaymandal362