Remove regprocedure oid type from catalog #6940

fabriziomello · 2024-05-21T13:24:05Z

In #6624 we refactored the time bucket catalog table to make it more
generic and save information for all Continuous Aggregates. Previously it stored only variable bucket size information.

The problem is we used the regprocedure type to store the OID of the given time bucket function but unfortunately it is not supported by pg_upgrade.

Fixed it by changing the column to TEXT and resolve to/from OID using builtin regprocedurein and format_procedure_qualified functions.

Fixes #6935

codecov · 2024-05-21T13:33:28Z

Codecov Report

All modified and coverable lines are covered by tests ✅

Project coverage is 81.70%. Comparing base (59f50f2) to head (0bc1f67).
Report is 171 commits behind head on main.

Additional details and impacted files

@@            Coverage Diff             @@
##             main    #6940      +/-   ##
==========================================
+ Coverage   80.06%   81.70%   +1.64%     
==========================================
  Files         190      198       +8     
  Lines       37181    36850     -331     
  Branches     9450     9635     +185     
==========================================
+ Hits        29770    30110     +340     
+ Misses       2997     2867     -130     
+ Partials     4414     3873     -541

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

mkindahl

Have you tested this patch with pg_upgrade?

sql/pre_install/tables.sql

src/ts_catalog/continuous_agg.c

fabriziomello · 2024-05-22T13:38:07Z

Have you tested this patch with pg_upgrade?

Sure, just for record check the results.

Getting some information about the old pg15 cluster

1. PG15 up and running

~/pgsql 
➜ ps faux | grep postgres
fabrizio  819957  0.0  0.0  19020  2560 pts/7    SN+  10:23   0:00  |   |       \_ grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn --exclude-dir=.idea --exclude-dir=.tox postgres
fabrizio  819868  0.6  0.0 272508 50944 ?        SNs  10:23   0:00  \_ /data/fabrizio/home/fabrizio/pgsql/REL_15_STABLE/bin/postgres -D /tmp/pg15
fabrizio  819869  0.0  0.0 272648  5956 ?        SNs  10:23   0:00      \_ postgres: checkpointer 
fabrizio  819870  0.0  0.0 272640  6084 ?        SNs  10:23   0:00      \_ postgres: background writer 
fabrizio  819872  0.0  0.0 272640  5828 ?        SNs  10:23   0:00      \_ postgres: walwriter 
fabrizio  819873  0.0  0.0 274248  9412 ?        SNs  10:23   0:00      \_ postgres: autovacuum launcher 
fabrizio  819874  1.0  0.0 274124 10308 ?        SNs  10:23   0:00      \_ postgres: TimescaleDB Background Worker Launcher 
fabrizio  819875  0.0  0.0 274120  8644 ?        SNs  10:23   0:00      \_ postgres: logical replication launcher 
fabrizio  819876  0.2  0.0 277212 20804 ?        SNs  10:23   0:00      \_ postgres: TimescaleDB Background Worker Scheduler

2. PG15 metadata

~/pgsql 
➜ psql -X <<EOF                                                           
select version();
\d _timescaledb_catalog.continuous_aggs_bucket_function
SELECT * FROM _timescaledb_catalog.continuous_aggs_bucket_function;
EOF
                                                version                                                
-------------------------------------------------------------------------------------------------------
 PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

 Table "_timescaledb_catalog.continuous_aggs_bucket_function"
       Column       |  Type   | Collation | Nullable | Default 
--------------------+---------+-----------+----------+---------
 mat_hypertable_id  | integer |           | not null | 
 bucket_func        | text    |           | not null | 
 bucket_width       | text    |           | not null | 
 bucket_origin      | text    |           |          | 
 bucket_offset      | text    |           |          | 
 bucket_timezone    | text    |           |          | 
 bucket_fixed_width | boolean |           | not null | 
Indexes:
    "continuous_aggs_bucket_function_pkey" PRIMARY KEY, btree (mat_hypertable_id)
Check constraints:
    "continuous_aggs_bucket_function_func_check" CHECK (to_regprocedure(bucket_func)::oid IS DISTINCT FROM 0::oid)
Foreign-key constraints:
    "continuous_aggs_bucket_function_mat_hypertable_id_fkey" FOREIGN KEY (mat_hypertable_id) REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE

 mat_hypertable_id |                      bucket_func                      | bucket_width | bucket_origin | bucket_offset | bucket_timezone | bucket_fixed_width 
-------------------+-------------------------------------------------------+--------------+---------------+---------------+-----------------+--------------------
                 2 | public.time_bucket(interval,timestamp with time zone) | 1 day        |               |               |                 | t
(1 row)

3. Check the compatibility to upgrade from PG15 to PG16

~/pgsql 
➜ ./REL_16_STABLE/bin/pg_upgrade -d /tmp/pg15 -D /tmp/pg16 -b /home/fabrizio/pgsql/REL_15_STABLE/bin -B /home/fabrizio/pgsql/REL_16_STABLE/bin --check
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking database connection settings                         ok
Checking for prepared transactions                            ok
Checking for system-defined composite types in user tables    ok
Checking for reg* data types in user tables                   ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking for incompatible "aclitem" data type in user tables  ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

*Clusters are compatible*

Upgrading from PG15 to PG16

1. Stop old PG15

~/pgsql 
➜ ./REL_15_STABLE/bin/pg_ctl -D /tmp/pg15 -l /tmp/pg15/postgresql.log stop                                                                            
waiting for server to shut down.... done
server stopped

2. Execute the upgrade from PG15 to PG16

~/pgsql 
➜ ./REL_16_STABLE/bin/pg_upgrade -d /tmp/pg15 -D /tmp/pg16 -b /home/fabrizio/pgsql/REL_15_STABLE/bin -B /home/fabrizio/pgsql/REL_16_STABLE/bin --copy 
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking database connection settings                         ok
Checking for prepared transactions                            ok
Checking for system-defined composite types in user tables    ok
Checking for reg* data types in user tables                   ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking for incompatible "aclitem" data type in user tables  ok
Creating dump of global objects                               ok
Creating dump of database schemas                             
                                                              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                               ok
Copying old pg_xact to new server                             ok
Setting oldest XID for new cluster                            ok
Setting next transaction ID and epoch for new cluster         ok
Deleting files from new pg_multixact/offsets                  ok
Copying old pg_multixact/offsets to new server                ok
Deleting files from new pg_multixact/members                  ok
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          ok
Resetting WAL archives                                        ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster                 
                                                              ok
Copying user relation files                                   
                                                              ok
Setting next OID for new cluster                              ok
Sync data directory to disk                                   ok
Creating script to delete old cluster                         ok
Checking for extension updates                                ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /home/fabrizio/pgsql/REL_16_STABLE/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

3. Start recently upgraded PG16 cluster

~/pgsql took 4s 
➜ ./REL_16_STABLE/bin/pg_ctl -D /tmp/pg16 -l /tmp/pg16/postgresql.log start                                                                          
waiting for server to start.... done
server started

4. PG16 up and running

~/pgsql 
➜ ps faux | grep postgres
fabrizio  821123  0.0  0.0  19020  2560 pts/7    SN+  10:25   0:00  |   |       \_ grep --color=auto --exclude-dir=.bzr --exclude-dir=CVS --exclude-dir=.git --exclude-dir=.hg --exclude-dir=.svn --exclude-dir=.idea --exclude-dir=.tox postgres
fabrizio  821051  1.5  0.0 272700 51712 ?        SNs  10:25   0:00  \_ /data/fabrizio/home/fabrizio/pgsql/REL_16_STABLE/bin/postgres -D /tmp/pg16
fabrizio  821052  0.0  0.0 272844  5876 ?        SNs  10:25   0:00      \_ postgres: checkpointer 
fabrizio  821053  0.0  0.0 272836  6004 ?        SNs  10:25   0:00      \_ postgres: background writer 
fabrizio  821055  0.0  0.0 272836  5748 ?        SNs  10:25   0:00      \_ postgres: walwriter 
fabrizio  821056  0.0  0.0 274456  9588 ?        SNs  10:25   0:00      \_ postgres: autovacuum launcher 
fabrizio  821057  1.5  0.0 274328 10228 ?        SNs  10:25   0:00      \_ postgres: TimescaleDB Background Worker Launcher 
fabrizio  821058  0.0  0.0 274320  8820 ?        SNs  10:25   0:00      \_ postgres: logical replication launcher 
fabrizio  821059  0.0  0.0 277844 21236 ?        SNs  10:25   0:00      \_ postgres: TimescaleDB Background Worker Scheduler

5. Post-upgrade ANALYZE on PG16 cluster

~/pgsql 
➜ /home/fabrizio/pgsql/REL_16_STABLE/bin/vacuumdb --all --analyze-in-stages
vacuumdb: processing database "fabrizio": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "fabrizio": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "fabrizio": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics

6. PG16 metadata

~/pgsql took 3s 
➜ psql -X <<EOF                                                                                                                                       
select version();
\d _timescaledb_catalog.continuous_aggs_bucket_function
SELECT * FROM _timescaledb_catalog.continuous_aggs_bucket_function;
EOF
                                                version                                                
-------------------------------------------------------------------------------------------------------
 PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)

 Table "_timescaledb_catalog.continuous_aggs_bucket_function"
       Column       |  Type   | Collation | Nullable | Default 
--------------------+---------+-----------+----------+---------
 mat_hypertable_id  | integer |           | not null | 
 bucket_func        | text    |           | not null | 
 bucket_width       | text    |           | not null | 
 bucket_origin      | text    |           |          | 
 bucket_offset      | text    |           |          | 
 bucket_timezone    | text    |           |          | 
 bucket_fixed_width | boolean |           | not null | 
Indexes:
    "continuous_aggs_bucket_function_pkey" PRIMARY KEY, btree (mat_hypertable_id)
Check constraints:
    "continuous_aggs_bucket_function_func_check" CHECK (to_regprocedure(bucket_func)::oid IS DISTINCT FROM 0::oid)
Foreign-key constraints:
    "continuous_aggs_bucket_function_mat_hypertable_id_fkey" FOREIGN KEY (mat_hypertable_id) REFERENCES _timescaledb_catalog.hypertable(id) ON DELETE CASCADE

 mat_hypertable_id |                      bucket_func                      | bucket_width | bucket_origin | bucket_offset | bucket_timezone | bucket_fixed_width 
-------------------+-------------------------------------------------------+--------------+---------------+---------------+-----------------+--------------------
                 2 | public.time_bucket(interval,timestamp with time zone) | 1 day        |               |               |                 | t
(1 row)

In timescale#6624 we refactored the time bucket catalog table to make it more generic and save information for all Continuous Aggregates. Previously it stored only variable bucket size information. The problem is we used the `regprocedure` type to store the OID of the given time bucket function but unfortunately it is not supported by `pg_upgrade`. Fixed it by changing the column to TEXT and resolve to/from OID using builtin `regprocedurein` and `format_procedure_qualified` functions. Fixes timescale#6935

@DiAifU

This release contains bug fixes since the 2.15.0 release. We recommend that you upgrade at the next available opportunity. **Bugfixes** * timescale#6540 Segmentation fault when backfilling data with COPY into a compressed chunk * timescale#6858 Before update trigger not working correctly * timescale#6908 Fix gapfill with timezone behaviour around dst switches * timescale#6911 Fix dropped chunk metadata removal in update script * timescale#6940 Fix `pg_upgrade` failure by removing `regprocedure` from catalog table * timescale#6957 Fix segfault in UNION queries with ordering on compressed chunks **Thanks** * @DiAifU, @kiddhombre and @intermittentnrg for reporting issues with gapfill and daylight saving time * @edgarzamora for reporting issue with update triggers * @hongquan for reporting an issue with the update script * @iliastsa and @SystemParadox for reporting an issue with COPY into a compressed chunk

@DiAifU

the 2.14.2 release. We recommend that you upgrade at the next available opportunity. **Bugfixes** * timescale#6540 Segmentation fault when backfilling data with COPY into a compressed chunk * timescale#6858 Before update trigger not working correctly * timescale#6908 Fix gapfill with timezone behaviour around dst switches * timescale#6911 Fix dropped chunk metadata removal in update script * timescale#6940 Fix `pg_upgrade` failure by removing `regprocedure` from catalog table * timescale#6957 Fix segfault in UNION queries with ordering on compressed chunks **Thanks** * @DiAifU, @kiddhombre and @intermittentnrg for reporting issues with gapfill and daylight saving time * @edgarzamora for reporting issue with update triggers * @hongquan for reporting an issue with the update script * @iliastsa and @SystemParadox for reporting an issue with COPY into a compressed chunk

@DiAifU

This release contains performance improvements and bug fixes since the 2.15.0 release. Best practice is to upgrade at the next available opportunity. **Migrating from self-hosted TimescaleDB v2.14.x and earlier** After you run `ALTER EXTENSION`, you must run [this SQL script](https://github.com/timescale/timescaledb-extras/blob/master/utils/2.15.X-fix_hypertable_foreign_keys.sql). For more details, see the following pull request [#6797](#6797). If you are migrating from TimescaleDB v2.15.0, no changes are required. **Bugfixes** * #6540: Segmentation fault when you backfill data using COPY into a compressed chunk. * #6858: `BEFORE UPDATE` trigger not working correctly. * #6908: Fix `time_bucket_gapfill()` with timezone behaviour around daylight savings time (DST) switches. * #6911: Fix dropped chunk metadata removal in the update script. * #6940: Fix `pg_upgrade` failure by removing `regprocedure` from the catalog table. * #6957: Fix the `segfault` in UNION queries that contain ordering on compressed chunks. **Thanks** * @DiAifU, @kiddhombre and @intermittentnrg for reporting the issues with gapfill and daylight saving time. * @edgarzamora for reporting the issue with update triggers. * @hongquan for reporting the issue with the update script. * @iliastsa and @SystemParadox for reporting the issue with COPY into a compressed chunk.

@DiAifU

This release contains bug fixes since the 2.15.0 release. We recommend that you upgrade at the next available opportunity. **Bugfixes** * timescale#6540 Segmentation fault when backfilling data with COPY into a compressed chunk * timescale#6858 Before update trigger not working correctly * timescale#6908 Fix gapfill with timezone behaviour around dst switches * timescale#6911 Fix dropped chunk metadata removal in update script * timescale#6940 Fix `pg_upgrade` failure by removing `regprocedure` from catalog table * timescale#6957 Fix segfault in UNION queries with ordering on compressed chunks **Thanks** * @DiAifU, @kiddhombre and @intermittentnrg for reporting issues with gapfill and daylight saving time * @edgarzamora for reporting issue with update triggers * @hongquan for reporting an issue with the update script * @iliastsa and @SystemParadox for reporting an issue with COPY into a compressed chunk

@DiAifU

This release contains bug fixes since the 2.15.0 release. We recommend that you upgrade at the next available opportunity. **Bugfixes** * #6540 Segmentation fault when backfilling data with COPY into a compressed chunk * #6858 Before update trigger not working correctly * #6908 Fix gapfill with timezone behaviour around dst switches * #6911 Fix dropped chunk metadata removal in update script * #6940 Fix `pg_upgrade` failure by removing `regprocedure` from catalog table * #6957 Fix segfault in UNION queries with ordering on compressed chunks **Thanks** * @DiAifU, @kiddhombre and @intermittentnrg for reporting issues with gapfill and daylight saving time * @edgarzamora for reporting issue with update triggers * @hongquan for reporting an issue with the update script * @iliastsa and @SystemParadox for reporting an issue with COPY into a compressed chunk

fabriziomello self-assigned this May 21, 2024

fabriziomello force-pushed the remove_regprocedure_from_cagg_metadata branch from c1b1c99 to 5f1d64d Compare May 21, 2024 13:41

fabriziomello added this to the TimescaleDB 2.15.1 milestone May 21, 2024

fabriziomello added bug continuous_aggregate labels May 21, 2024

fabriziomello force-pushed the remove_regprocedure_from_cagg_metadata branch 3 times, most recently from 170910b to c636a33 Compare May 21, 2024 15:56

fabriziomello marked this pull request as ready for review May 21, 2024 15:56

fabriziomello changed the title ~~Remove regprocedure from cagg metadata~~ Remove regprocedure oid type from catalog May 21, 2024

mkindahl reviewed May 21, 2024

View reviewed changes

sql/pre_install/tables.sql Show resolved Hide resolved

src/ts_catalog/continuous_agg.c Outdated Show resolved Hide resolved

fabriziomello force-pushed the remove_regprocedure_from_cagg_metadata branch 2 times, most recently from fddaa13 to c7d0a8b Compare May 21, 2024 19:03

mkindahl approved these changes May 21, 2024

View reviewed changes

akuzm approved these changes May 22, 2024

View reviewed changes

fabriziomello force-pushed the remove_regprocedure_from_cagg_metadata branch from c7d0a8b to 0bc1f67 Compare May 22, 2024 13:41

fabriziomello merged commit 8b994c7 into timescale:main May 22, 2024
35 checks passed

timescale-automation mentioned this pull request May 22, 2024

Backport to 2.15.x: #6940: Remove regprocedure oid type from catalog #6944

Merged

timescale-automation added the backported-2.15.x label May 23, 2024

fabriziomello mentioned this pull request May 27, 2024

Release 2.15.1 #6966

Merged

pallavisontakke mentioned this pull request May 28, 2024

Release 2.15.1 #6970

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Remove regprocedure oid type from catalog #6940

Remove regprocedure oid type from catalog #6940

fabriziomello commented May 21, 2024 •

edited

codecov bot commented May 21, 2024 •

edited

mkindahl left a comment

fabriziomello commented May 22, 2024

Remove regprocedure oid type from catalog #6940

Remove regprocedure oid type from catalog #6940

Conversation

fabriziomello commented May 21, 2024 • edited

codecov bot commented May 21, 2024 • edited

Codecov Report

mkindahl left a comment

Choose a reason for hiding this comment

fabriziomello commented May 22, 2024

Getting some information about the old pg15 cluster

1. PG15 up and running

2. PG15 metadata

3. Check the compatibility to upgrade from PG15 to PG16

Upgrading from PG15 to PG16

1. Stop old PG15

2. Execute the upgrade from PG15 to PG16

3. Start recently upgraded PG16 cluster

4. PG16 up and running

5. Post-upgrade ANALYZE on PG16 cluster

6. PG16 metadata

fabriziomello commented May 21, 2024 •

edited

codecov bot commented May 21, 2024 •

edited