Remove regprocedure oid type from catalog

[fabriziomello]

In https://github.com/timescale/timescaledb/pull/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[bot]]

Codecov Report

All modified and coverable lines are covered by tests :white_check_mark:

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

Additional details and impacted files

```diff @@ 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 ```

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[fabriziomello]

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)