Extension Update

This procedure updates an installed synchro_pg extension to a new version. The changelog lists the changes in each version.

The supported update path starts at the 0.3.1 baseline. A state before 0.3.1 has no update path. For that state, use the export, clean installation, and rebuild path of ADR 005.

You need:

the extension archive of the new version;
a PostgreSQL superuser.

Before you update to 0.3.2

Version 0.3.2 accepts only the registered key types in ADR 004. Each registry change validates every registration. After the update, a registration with another key type makes each registry change fail.

Find each such registration before you update:

SELECT registry.table_name, registry.pk_type
FROM synchro.sync_registry AS registry
JOIN synchro.sync_registry_generations AS generation
  ON generation.generation = registry.registry_generation
JOIN pg_catalog.pg_attribute AS attribute
  ON attribute.attrelid = registry.physical_relation_oid
 AND attribute.attname = registry.pk_column
WHERE generation.state = 'active'
  AND attribute.atttypid NOT IN (
    'smallint'::regtype, 'integer'::regtype, 'bigint'::regtype,
    'text'::regtype, 'character varying'::regtype, 'character'::regtype,
    'uuid'::regtype, 'inet'::regtype, 'cidr'::regtype,
    'macaddr'::regtype, 'macaddr8'::regtype,
    'int4range'::regtype, 'int8range'::regtype, 'numrange'::regtype,
    'int4multirange'::regtype, 'int8multirange'::regtype, 'nummultirange'::regtype
  )
ORDER BY registry.table_name;

Remove each registration that the query returns.

Version 0.3.2 also rejects a deferrable primary key. PostgreSQL uses only an immediate key as the replica identity. Find each such registration:

SELECT registry.table_name
FROM synchro.sync_registry AS registry
JOIN synchro.sync_registry_generations AS generation
  ON generation.generation = registry.registry_generation
JOIN pg_catalog.pg_index AS primary_index
  ON primary_index.indrelid = registry.physical_relation_oid
 AND primary_index.indisprimary
WHERE generation.state = 'active'
  AND NOT primary_index.indimmediate
ORDER BY registry.table_name;

Replace the deferrable key with an immediate key, or remove the registration.

Version 0.3.2 also enforces the write order of each registered row. A nested write can change a row before the fence of an earlier write of that row fires. The capture fence then fails the source transaction with SQLSTATE 27000. For example, an AFTER row trigger that changes its own row fails when its name sorts before synchro_capture_fence. COPY ... FREEZE into a registered table fails with SQLSTATE 0A000. A key change by a later BEFORE UPDATE trigger or by a partition row move fails with SQLSTATE 23514.

Review the application triggers on each registered relation before you update.

Before you update to 0.4.0

Version 0.4.0 checks each deferrable constraint at the end of each push unit. Each accepted outcome reports the row state after that unit. An application trigger or a foreign key cascade can delete a row that the same unit inserts or updates. The outcome of that insert or update then has no row. A 0.3.x client cannot read that outcome. It sends the same push again and does not send later changes.

If a trigger or a cascade on a registered relation can delete such a row, do these steps before you update:

Release the application versions that include the 0.4.0 clients. These versions must not call the atomic API. A 0.4.0 client that does not call the atomic API operates with a 0.3.x server.
Set MIN_CLIENT_VERSION on the adapter to the first application version that includes the 0.4.0 clients.
Update the extension and the adapter.
Release the application code that calls the atomic API. The server version requirement of the atomic API applies.

Version 0.4.0 evaluates each membership and impact function as its owner in a security-restricted operation. The update does not check the functions that are already registered. If the owner cannot read a relation that the function reads, evaluation fails and capture of the registered relation stops. Before you update, give each function owner SELECT on each relation that its function reads, and USAGE on the schema of that relation. See ADR 004 for the evaluation rules.

Version 0.4.0 rejects a database-generated key for a registration with push policy enabled. Clients cannot write an identity ALWAYS column. Clients can write a BY DEFAULT identity column.

Find each active registration with a database-generated push key or a writable field that the database generates:

SELECT registry.table_name
FROM synchro.sync_registry AS registry
JOIN synchro.sync_registry_generations AS generation
  ON generation.generation = registry.registry_generation
WHERE generation.state = 'active'
  AND (
    (
      registry.registration_kind = 'synced'
      AND registry.push_policy = 'enabled'
      AND EXISTS (
        SELECT 1
        FROM pg_catalog.pg_attribute AS key_attribute
        WHERE key_attribute.attrelid = registry.physical_relation_oid
          AND key_attribute.attname::text = registry.pk_column
          AND key_attribute.attnum > 0
          AND NOT key_attribute.attisdropped
          AND (
            key_attribute.attidentity = 'a'
            OR key_attribute.attgenerated <> ''
          )
      )
    )
    OR EXISTS (
      SELECT 1
      FROM synchro.sync_registry_fields AS field
      JOIN pg_catalog.pg_attribute AS field_attribute
        ON field_attribute.attrelid = registry.physical_relation_oid
       AND field_attribute.attname = field.physical_column
       AND field_attribute.attnum > 0
       AND NOT field_attribute.attisdropped
      WHERE field.registry_generation = registry.registry_generation
        AND field.relation_id = registry.relation_id
        AND field.writable
        AND (
          field_attribute.attidentity = 'a'
          OR field_attribute.attgenerated <> ''
        )
    )
  )
ORDER BY registry.table_name;

For each result, do these steps:

Remove the registration with synchro_unregister_table.
Wait until this query returns 0:
```
SELECT count(*)
FROM synchro.sync_registry_generations
WHERE state = 'pending';
```
The worker removes the capture triggers when it activates the new generation. Before that, a new registration of the table fails with registered relation has incompatible capture triggers.
Correct the table or the registration arguments. For example, use ALTER TABLE ... ALTER COLUMN ... SET GENERATED BY DEFAULT, or use push policy read_only.
Register the table again.

If you update before this repair, these conditions occur:

Readiness reports relation_identity as failed.
The worker does not reach the running state.
Each registry change fails, and synchro_unregister_table also fails.

To recover, correct each table first, for example with ALTER TABLE ... ALTER COLUMN ... SET GENERATED BY DEFAULT. The worker then starts, and registry changes operate again. Then do the steps above for each registration that needs other arguments.

1. Install the new extension files

Install the new library, the control file, the base script, and every update script from the new archive. Use the commands in the extension artifact step of server setup.

2. Restart PostgreSQL

shared_preload_libraries loads the library only when the postmaster starts. The server uses the new library only after the restart. Run the update only after the restart.

For the server setup cluster, use this command:

sudo pg_ctlcluster 18 synchro_tutorial restart

Between the restart and the update, readiness fails closed because the installed extension version does not match the library. The adapter refuses to start. If the SQL fingerprints differ, the WAL worker waits for the update. It continues after the update without another restart.

When the install SQL is identical, the fingerprints match and the worker can continue before the version update. Run the documented update even when extension_objects_stale reports ok.

3. Update the extension

Connect as a superuser to the database that synchro.database names. Run the update:

ALTER EXTENSION synchro_pg UPDATE;

PostgreSQL runs the update scripts from the installed version to the default_version of the new control file.

4. Verify the update

Examine the installed extension version:

SELECT extversion FROM pg_catalog.pg_extension WHERE extname = 'synchro_pg';

The result must be the new version.

Examine readiness:

SELECT synchro.synchro_readiness();

The result must be {"ready": true}.