There are changes in Sigstore's Trusted root used for signing, details
are explained here => https://blog.sigstore.dev/tuf-root-update/

Solution: Switch to cosign-release v2.2.3

Fixes https://github.com/dimitri/pgcopydb/issues/723



Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>

The clone operation employs table copy workers to copy data. Once completed, it delegates index creation to index workers. If interrupted during index creation, resuming is impossible since the underlying table has already been copied.

Solution: On resume, let the table-data skip data copy if it is done but
still signal the index workers to proceed with creating indexes. Index
worker shall ignore if the index is already exists, else creates one.

Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>

Postgres 11 and 12 only exposed PG_MAJORVERSION in libpq, we can't rely on PG_MAJORVERSION_NUM.

* Problem: CDC replication is slow

Our existing implementation is basic and it will execute a statement and waits for its result to come before issuing next statement. We already improved DML statements targetting same table with in a transaction by coalscing them into multi-value insert statement, but it will be beneficial only for logical decoding transaction which has multiple DML statements with in a txn.

However, we have encountered few cases where the transaction had only one DML statement. This kind of txn will be slower because statements are executed sequentially from the client's perspective.

Here is an example of how a single statement transaction will be executed now,

1. Execute ["BEGIN"]()
2. Execute [bunch of "SET" statements]() which are related to replication session setup
3. Prepare [DML statement]()
4. Execute [prepared statement with values]()
5. Execute procedure to update [replication origin progress]()
6. Execute [COMMIT]()
7. Map [target current insert lsn to commit lsn]() for feedback reporting (i.e. sentinel replay_lsn)

**Solution**: Use [pipeline
API](https://www.postgresql.org/docs/current/libpq-pipeline-mode.html

) from libpq client library

The proposed implementation would enter into pipeline mode as soon as a new libpq connection is created for the ld_apply/ld_replay process. All the statements would be executed on a pipeline by default except few for statements which needs response immediately(e.g. step 7). A dedicated connection would be used to serve (step 7), because it needs synchronous response.

The following functions are being called on the target PGSQL handle from ld_apply & ld_replay.

* pgsql_begin
* pgsql_set_gucs
* pgsql_execute
* pgsql_replication_origin_xact_setup
* pgsql_prepare
* pgsql_execute_prepared
* pgsql_current_wal_insert_lsn
* pgsql_current_wal_flush_lsn

Among all of the above function, only pgsql_current_wal_insert_lsn and pgsql_current_wal_flush_lsn returns values and other functions are write only.

The idea is to have 2 PGSQL connection handles, 1 for all write activity which can go through pipeline and another one could be used for reading.

Pipeline connection has to be synced/drained at some point to avoid accumulating results on the server & client which would end up eating lots of heap memory. The current implementation syncs based on the time interval(i.e. for every 1s). There are other methods like statement/txn count based sync, which may or may not be efficient.

The following command can be used to generate loads to understand the performance improvement made by this commit,

```
CREATE TABLE metrics (
    time TIMESTAMP NOT NULL,
    name TEXT,
    id NUMERIC,
    value FLOAT
);
```

```
-- insert_metric.sql
\set id random(1, 1000000)
\set value random(0,100)
INSERT INTO metrics (time, name, value) VALUES (NOW(), 'metric_' || :id, :value);
```
```
pgbench -n -c 40 -j 1 -t 10000 -f insert_metric.sql $SOURCE
```

- `-c` number of database connection to utlize (i.e. server side concurrency)
- `-j` number of threads to create on the client machine (i.e. client side concurrency)

synchronous_commit=off)
```
pgbench -n -c 1 -j 1 -t 10000 -f insert_metric.sql $TARGET
```
```
tps = 1177 (without initial connection time)
```

```
tps = 175
```

```
tps = 1652
```

**This commit improves the single statement txn throughput by 10x**

Ideally, we should aim to get performance number close to direct ingestion(i.e. 1200 txn/s). We are 40% performing better than the baseline in this iteration. However, we can aim more as in the real system there will be more than 1 connection will be utilized. We can't really race against multiple connection doing steady ingestion around 1000 txn/s per connection, but lets optimize the single connection throughput to the max!

This change will be a foundation to the future improvements which steers towards that.

1. Optimize step 2 - Instead of executing bunch of SET statements for every txn, run once in the beginning for the session
2. Optimize step 9 - Probably we can simply use pg_replication_origin_progress as replay_lsn?

Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>

* Force sequences reset at the end of the follow process.

* Re-fetch current sequences values at source before resetting them.

Also, for test_decoding plugin we would require the catalog information so it's better to let it initialise first and then we start the prefetch.

When the replication connection is interrupted due to network or server issues, although there is a retry logic in place, it fails to work effectively. This is because the interruption may leave the last transaction in a partial state, leading to a failure when the ld_transform module attempts to transform the message.

Solution: Add a ROLLBACK message when the last transaction is partially written and the stream is in retry mode.

Use the following query on psql to test the network interruption behaviour while doing DML on source.

```.sql
 SELECT pg_cancel_backend(pid) FROM pg_stat_activity where application_name like '%pgcopydb%' and query ilike '%START_REPL%';

\watch 5
```

The following error message will be loged on console when it is interrupted,

> 11:40:54.061 94512 ERROR  unexpected termination of replication stream:
11:40:54.061 94512 ERROR  ERROR:  canceling statement due to user request
11:40:54.061 94512 ERROR  CONTEXT:  slot "pgcopydb", output plugin "test_decoding", in the change callback, associated LSN 7B/704E6A18
11:40:54.061 94512 WARN   Streaming got interrupted at 7B/704E6A18, reconnecting in 1s

Without this fix, ld_transform would fail with the following console error logs,

>2024-02-28 18:44:44.785 37 ERROR  ld_transform.c:1099       Failed to parse BEGIN: transaction already in progress
2024-02-28 18:44:44.785 37 ERROR  ld_transform.c:924        Failed to parse JSON message: {"action":"B","xid":"3560764890","lsn":"DD68/8ADC210","timestamp":"2024-02-28 17:59:31.756879+0000","message":{"action":"B","xid":3560764890}}
2024-02-28 18:44:44.789 37 ERROR  ld_transform.c:686        Stream transform worker encountered 1 errors, see above for details
2024-02-28 18:44:44.789 37 INFO   follow.c:758              Transform process has terminated
2024-02-28 18:44:44.855 19 ERROR  follow.c:993              Subprocess transform with pid 37 has exited with error code 12
2024-02-28 18:44:45.016 19 ERROR  follow.c:471              Failed to transform 2 messages from the queue, see above for details

Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>
Co-authored-by: Dimitri Fontaine <dim@tapoueh.org>

This fixes the case where copydb_table_data_worker receives a
`QMSG_TYPE_STOP` and we return within the switch statement without calling
catalog_delete_process outside the swtich body.

There were several issues with the CFLAGS in the pgcopydb Makefile. This patch fixes them.

1. Removal of conditional logic for USE_SECURITY_FLAGS

Historically, we needed to support some linux distros such as RHEL6 that
did not support the subset of security flags that are required for
Microsoft Compliance. However, we are now at a point where we can remove
this conditional logic and always use those security flags. This patch
removes the conditional logic and always uses the security flags.

2. Fix typo in JENKINS_CFLAGS

The JENKINS_CFLAGS variable was misspelled as CFAGS. This patch fixes
the typo.

3. Remove unnecessary override of CFLAGS

The override of CFLAGS was unnecessary and was causing the security
flags to be overridden. This patch removes the override. If we do not
have -Wformat option, -Wformat-security which is a required compiler
flag for compliance is ignored.

Relevant error message:
cc1: error: -Wformat-security ignored without -Wformat [-Werror=format-security]

Extension configuration tables can have foreign key dependencies between
them. If the configuration tables are restored in the incorrect order,
it can cause foreign key constraint violations.

This change ensures that the configuration tables are ordered according
to the dependency order in `pg_constraint`.

We always call `summary_start_timing` regardless of whether the section has been already done or not., which causing insertion(insert or replace) of default value to `done_time_epoch` because `summary_start_timing` won't be called for already done section.

Solution: Check whether the section is already completed using `runState` and exit early without calling `summary_start_timing` for already done section.

Related to https://github.com/dimitri/pgcopydb/issues/692



Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>

pgcopydb recently switched to Boehm-Demers-Weiser Garbage Collector[1] and replaced
malloc family with GC_malloc and friends. However, strdup is not replaced
to GC_strdup.

Solution: Use macros to replace strdup/strndup with it's GC variant.

[1] https://github.com/dimitri/pgcopydb/pull/647



Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>

Given ALTER TABLE ... DROP COLUMN ... we could well have a table without an
attribute with attnum == 1. Our code used to rely on that to happen in order
to allocate our SourceTableAttribute array in-memory, before filling it in.

Instead, use a window-function in the SQL query that we run against our
internal SQLite catalogs, using count(*) over(order by attnum) to map
whatever the Postgres attnum are into a proper gapless series that starts at
one.

This function does not exists anymore, but the header was still defined and
the call site was not removed yet.

Instead of storing the table size in the source database, store it in our internal catalogs in the SQLite database. This allows better compatibility with read-only source databases.

At the moment this removes the possibility for the user to provide their own table size data in order to avoid the `pg_table_size()` query which is known to be a long running query in some platforms. A later improvement could be done to enable external table size input again.

Missing xid triggers the following error during apply/replay.

"BUG: parseTxnMetadataFile is called with transaction xid: 0"

Solution: Use xid from commit message as a transaction xid.

Unlike wal2json, test_decoding don't have xid in the DML logical
messages. So we use the xid from the COMMIT message to update the
transaction xid.

Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>

Solution: fflush stdout after printing the snapshot id. By default stdout is
buffered and flushed only when the buffer is full.

Signed-off-by: Arunprasad Rajkumar <ar.arunprasad@gmail.com>

Before embedding SQLite we could not depend on aggregates FILTER clause to
be available, and as a result we had to aggregate the attribute list in our
own C code. Turns out we had strange bugs around that, that hopefully the
very simple SQL query will not have.

Follow up of #595

This allows to ensure we're always using a known version of the SQLite lib
and allows pgcopydb code to use some "recent" features on all OS targets.

Such features include the RETURNING clause for DMLs, the FILTER clause for
aggregate function calls, and time formatting function or JSON functions.

See https://sqlite.org/amalgamation.html for details about that distribution
mode for SQLite.

Before this change we had 153 files on the main branch that did broke at
least one of our editorconfig rules. After this change, all of our files
honor the rules that are partially updated.

There are 2 places that are exempt from our rules:
- external libraries in src/bin/lib/
- debian directory in debian/
- test file from outside source in tests/extensions/

You can see the .editorconfig file in each of these directories for more
details.

I used a copy of Citus' CONTRIBUTING.md as a starting point.

I removed sections that are not relevant to pgcopydb, and added some
more sections about documentation and running tests.

I also added references to the CONTRIBUTING.md file in the README.md and
in documentation page about installing pgcopydb. While I was at it, I
also removed the outdated installation instructions from the README.md
file, and replaced them with a reference to the documentation page.

Co-authored-by: Dimitri Fontaine <dim@tapoueh.org>

* Fix confusion around reporting operations in progress.

The SQLite refactor meant rewriting all the SourceTableArray loops to use a
query result iterator instead. Well there is a place for a SourceTableArray
loop still, and that's the pgcopydb list progress thing.

* Fix the table-in-copy SQLite query.

The query was meant to report actual duration and transmitted bytes for each
table in-progress, but the join and sum() operations are creating problems
in the SQLite query and the rest of the progress.c code was not prepared to
benefit from the extra information anyway.

The SQLite problem is that the sum(s.duration), sum(s.bytes) causes a single
row output when the dataset is empty, and the other columns are all set to
NULL. That might be because of the lack of a GROUP BY clause.

Simplify that aspect by removing the extra information for now.

Since the move of the summary and process information to the SQLite
catalogs, we don't have a progress JSON file on-disk with parts of the setup
anymore.

The code needed to be updated to behave correctly without having
--table-jobs and --index-jobs information.

* Use Boehm-Demers-Weiser Garbage Collector.

From their own documentation:

> The Boehm-Demers-Weiser conservative garbage collector can be used as a
> garbage collecting replacement for C malloc or C++ new. It allows you to
> allocate memory basically as you normally would, without explicitly
> deallocating memory that is no longer useful. The collector automatically
> recycles memory when it determines that it can no longer be otherwise
> accessed.

In pgcopydb C code base it's been hard to maintain proper malloc/free
concerns while also implementing proper error-handling, which is a common
problem when writing C code. In order to ease the maintenance of the code
and reduce production hazards, the best way to proceed looks like automating
the memory management altogether by using a Garbage Collector.

* Revert removal of regree() calls.

* Get rid of LinesBuffer.ownsBuffer.

Now that libgc is handling the memory for us, we don't need to keep track of
the ownership of the buffer anymore.

I observed frequent `[SQLite] PRAGMA journal_mode = WAL` when it should be
run only once as it's a sqlite database property, not something that has be
set for every connection. This might fix
https://github.com/dimitri/pgcopydb/actions/runs/7614380466/job/20736769276?pr=647

```
2024-01-22 16:45:01.923 44 ERROR  catalog.c:7129            [SQLite]: PRAGMA journal_mode = WAL failed: database is locked
```

- Adds CI to check that the documentation templates are up-to-date.
- Add a new `help` command to the documentation templates. This command
  prints the help message for all the sub-commands of `pgcopydb` in a
  single file. This is useful for users who want to see all the
  sub-commands in one place.
- Remove dependency on installing the binary before updating the
  documentation templates. This allows users to update the documentation
  templates without installing the binary. One can set the `PGCOPYDB`
  environment variable to point to the binary to use for updating the
  documentation templates.
- Fix a casing issue in command descriptions. The first letter of the
  first word in the description of each command should be capitalized.

When registering the catalog setup, which includes the same-table
concurrency threshold, only consider that value when the table partitions
have been populated in the cache already. Otherwise the table partitions are
going to be computed again anyway.

* Improve SQLite write performance with journal_mode as WAL.

* Batch initial catalog setup write in a single transaction.