[Bug]: Upgrading timescale with partial aggregate continuous aggregate fails #5348
Comments
|
Hi @pushpeepkmonroe and thanks for reporting this issue!
Yes, this explains why the rename fails, but it is not clear how the |
|
The actual rename is done as part of the upgrade from 2.4.1 to 2.4.2, so could you create the following views and check the result of CREATE VIEW objs AS
SELECT format('%I.%I', user_view_schema, user_view_name)::regclass AS user_view,
format('%I.%I', direct_view_schema, direct_view_name)::regclass AS direct_view,
format('%I.%I', partial_view_schema, partial_view_name)::regclass AS partial_view,
format('%I.%I', schema_name, table_name)::regclass AS mat_table,
mat_hypertable_id AS mat_id
FROM _timescaledb_catalog.continuous_agg
JOIN _timescaledb_catalog.hypertable ON mat_hypertable_id = id;
CREATE VIEW user_view AS
SELECT attrelid, attname, attnum, mat_id
FROM objs, pg_attribute
WHERE attrelid = objs.user_view;
CREATE VIEW direct_view AS
SELECT attrelid, attname, attnum, mat_id
FROM objs, pg_attribute
WHERE attrelid = objs.direct_view;
CREATE VIEW rename_tables AS
SELECT (SELECT user_view FROM objs WHERE uv.attrelid = user_view),
uv.attname AS new_name,
dv.attname AS old_name,
(SELECT partial_view FROM objs WHERE uv.attrelid = user_view),
(SELECT direct_view FROM objs WHERE uv.attrelid = user_view),
(SELECT mat_table FROM objs WHERE uv.attrelid = user_view),
(SELECT mat_id FROM objs WHERE uv.attrelid = user_view)
FROM user_view uv JOIN direct_view dv USING (mat_id, attnum)
WHERE uv.attname != dv.attname; |
|
Stores the counts that get manipulated the none caggs |
Yes, but the problem is that the lookup that fetches the name of the column is confused for some reason, and it would be good to figure out why so that we can fix the problem. The above queries show the information that the rename uses to figure out the old and new column names and this would allow us to investigate what changes were made that causes this error. If you could start by providing the information shown by |
|
|
The partial view you mentioned in the initial report is not in this list and there is no mention of the Not sure how you wrote this, it looks correct. Are you using some non-standard quotes as a result of a cut-and-paste? |
|
The error is still present from the initial issue: psql -X -d -c 'ALTER EXTENSION timescaledb UPDATE;' |
|
The command you told me to run was typed out not copied and pasted: SELECT * FROM rename_tables WHERE partial_view='_timescaledb_internal._partial_view_288'; |
|
Try this instead: SELECT * FROM rename_tables WHERE partial_view::text = '_timescaledb_internal._partial_view_288'; |
|
|
SELECT * FROM direct_view WHERE attname LIKE '%count%'; |
|
|
We are using the old partial aggregate format as it is a bytea column type as well. Which is deprecated, but I am not sure when. |
The deprecation announcement is in the release notes for 2.10 |
|
This is straightforward to reproduce: Start a docker image: Connect to the database (using -- Create a hypertable with some data
CREATE TABLE conditions(
time TIMESTAMP NOT NULL,
device_id INTEGER,
temperature numeric,
humidity numeric
);
SELECT * FROM create_hypertable('conditions', 'time', 'device_id', 12);
INSERT INTO conditions
SELECT time, (random()*3 + 1)::int, random()*80 - 40, random()*100
FROM generate_series(now() - INTERVAL '28 days', now(), '1 hour') AS time;
-- Create two continuous aggregates. Note that 2.4 automatically
-- creates the old-style continuous aggregate while the new one
-- requires timescaledb.finalized=false to create an old-style format.
CREATE MATERIALIZED VIEW conditions_summary_hourly
WITH (timescaledb.continuous) AS
SELECT device_id,
time_bucket(INTERVAL '1 hour', "time") AS bucket,
AVG(temperature),
MAX(temperature),
MIN(temperature)
FROM conditions
GROUP BY device_id, bucket
WITH NO DATA;
CREATE MATERIALIZED VIEW conditions_summary_daily
WITH (timescaledb.continuous) AS
SELECT device_id,
time_bucket(INTERVAL '1 day', "time") AS bucket,
AVG(temperature),
MAX(temperature),
MIN(temperature)
FROM conditions
GROUP BY device_id, bucket
WITH NO DATA;Using a tweaked version of the view above (where the partial view name is added) it is easy to see that the name for the partial view does not match the name for the direct view: mats=> select user_view_name, partial_view_name, direct_view_name from rename_tables ;
user_view_name | partial_view_name | direct_view_name
----------------+-------------------+------------------
device_id | device_id | device_id
bucket | bucket | bucket
avg | agg_3_3 | avg
max | agg_4_4 | max
min | agg_5_5 | min
device_id | device_id | device_id
bucket | bucket | bucket
avg | agg_3_3 | avg
max | agg_4_4 | max
min | agg_5_5 | min
(10 rows)This will then fail when running this part of the upgrade script: DO
$$
DECLARE
user_view regclass;
new_name name;
old_name name;
partial_view regclass;
direct_view regclass;
mat_table regclass;
ht_id int;
BEGIN
FOR user_view, new_name, old_name, partial_view, direct_view, mat_table, ht_id IN
SELECT * FROM _timescaledb_internal.rename_tables
LOOP
-- There is no RENAME COLUMN for views, but we can use ALTER TABLE
-- to rename a column in a view.
--> CALL _timescaledb_internal.alter_table_column(user_view, partial_view, old_name, new_name); <--
CALL _timescaledb_internal.alter_table_column(user_view, direct_view, old_name, new_name);
CALL _timescaledb_internal.alter_table_column(user_view, mat_table, old_name, new_name);
UPDATE _timescaledb_catalog.dimension SET column_name = new_name
WHERE hypertable_id = ht_id AND column_name = old_name;
END LOOP;
END
$$; |
|
You reproduced the error that I am initially having through Docker in your latest comment. Is there any workaround? |
This is what I'm looking into. Reproduction is the first step. |
mkindahl
added
continuous_aggregate
upgrade
mkindahl
changed the title
mkindahl
changed the title
|
In order to do an upgrade, it is necessary to do the renames manually before the actual upgrade. The update script will check if there are any renames that needs to be done and not do them if all looks good already. The following procedure seems to work for that purpose, but use it at your own risk. CREATE PROCEDURE alter_table_column(relation regclass, old_column_name name, new_column_name name) AS $$
BEGIN
EXECUTE format('ALTER TABLE %s RENAME COLUMN %I TO %I', relation, old_column_name, new_column_name);
EXCEPTION
WHEN SQLSTATE '42701' THEN
-- Do nothing and just ignore it
END;
$$ LANGUAGE plpgsql;
CREATE PROCEDURE upgrade_all_caggs() AS
$$
DECLARE
user_view regclass;
user_column text;
partial_view regclass;
partial_column text;
direct_view regclass;
direct_column text;
mat_table regclass;
ht_id int;
BEGIN
PERFORM timescaledb_pre_restore();
FOR user_view, user_column, direct_view, direct_column, partial_view, partial_column, mat_table, ht_id IN
WITH
objs AS (
SELECT format('%I.%I', user_view_schema, user_view_name)::regclass AS user_view,
format('%I.%I', direct_view_schema, direct_view_name)::regclass AS direct_view,
format('%I.%I', partial_view_schema, partial_view_name)::regclass AS partial_view,
format('%I.%I', schema_name, table_name)::regclass AS mat_table,
mat_hypertable_id AS mat_id
FROM _timescaledb_catalog.continuous_agg
JOIN _timescaledb_catalog.hypertable ON mat_hypertable_id = id),
user_view AS (SELECT attrelid, attname, attnum, mat_id
FROM objs, pg_attribute
WHERE attrelid = objs.user_view),
partial_view AS (SELECT attrelid, attname, attnum, mat_id
FROM objs, pg_attribute
WHERE attrelid = objs.partial_view),
direct_view AS (SELECT attrelid, attname, attnum, mat_id
FROM objs, pg_attribute
WHERE attrelid = objs.direct_view)
SELECT (SELECT objs.user_view FROM objs WHERE uv.attrelid = objs.user_view),
uv.attname AS user_column,
(SELECT objs.direct_view FROM objs WHERE uv.attrelid = objs.user_view),
dv.attname AS direct_column,
(SELECT objs.partial_view FROM objs WHERE uv.attrelid = objs.user_view),
pv.attname AS partial_column,
(SELECT objs.mat_table FROM objs WHERE uv.attrelid = objs.user_view),
(SELECT objs.mat_id FROM objs WHERE uv.attrelid = objs.user_view)
FROM user_view uv JOIN direct_view dv USING (mat_id, attnum)
JOIN partial_view pv USING (mat_id, attnum)
LOOP
CALL alter_table_column(partial_view, partial_column, user_column);
CALL alter_table_column(direct_view, direct_column, user_column);
CALL alter_table_column(mat_table, partial_column, user_column);
UPDATE _timescaledb_catalog.dimension SET column_name = user_column
WHERE hypertable_id = ht_id AND column_name = direct_column;
END LOOP;
PERFORM timescaledb_post_restore();
END
$$ LANGUAGE plpgsql;Update: the procedure was updated to capture a failure to alter the column name so that it does not abort altering other columns. |
|
This will rename the column agg_9_9 to count in the _timescaledb_internal._partial_view_288 in order to upgrade. Then we will have to rename it back, correct? |
No. When renaming the column of the partial and direct view the update script will ignore them. It only processes continuous aggregates where the name of the user view and direct view is different. |
What type of bug is this?
Configuration, Incorrect result, Unexpected error, Other
What subsystems and features are affected?
Other
What happened?
psql -X -d -c 'ALTER EXTENSION timescaledb UPDATE;'
ERROR: column "count" does not exist
CONTEXT: SQL statement "ALTER TABLE _timescaledb_internal._partial_view_288 RENAME COLUMN count TO pstag_android"
PL/pgSQL function _timescaledb_internal.alter_table_column(regclass,regclass,name,name) line 3 at EXECUTE
SQL statement "CALL _timescaledb_internal.alter_table_column(user_view, partial_view, old_name, new_name)"
PL/pgSQL function inline_code_block line 16 at CALL
The current columns in _timescaledb_internal._partial_view_288 have been attached with column agg_9_9 replacing the count column.
\dx timescaledb
FATAL: "timescaledb" already loaded with a different version
DETAIL: The new version is "2.4.0", this session is using version "2.9.3". The session will be restarted.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
psql (12.6 (Ubuntu 12.6-1.pgdg18.04+1), server 12.14 (Ubuntu 12.14-1.pgdg18.04+1))
This view is owned by our database user. There is a column "count" that it is looking for does not exist in the view. After speaking with my manager I the "agg_9_9" column in timescale has replaced the "count" column. This is the error as to why it cannot ALTER the column "count" to "pstag_android".
_timescaledb_internal._partial_view_288
Columns:
bucket
account
browser_name
browser_version
device_type
platform_name
platform_version
website
agg_9_9 <- replaced the column "count"
chunk_id
TimescaleDB version affected
2.4.0
PostgreSQL version used
12.14
What operating system did you use?
Ubuntu 18.04
What installation method did you use?
Other
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
How can we reproduce the bug?
The text was updated successfully, but these errors were encountered: