Skip to content

2.27.0.0-b269

2.27.0.0-b269: [#11419] YSQL: Use ybctid as tuple identifier in postgres_fdw queries
@karthik-ramanathan-3006 karthik-ramanathan-3006 tagged this 26 Jun 15:09 
Summary:
postgres_fdw queries that interact with the tuple identifier (ctid) system column currently do not work. These queries fall into three buckets:
- Queries on foreign relations that use the option `use_remote_estimate`:
    - This makes postgres_fdw send queries of the form `SELECT ctid FROM relation WHERE <predicate>` to the foreign server to obtain row estimates.
    - Since the foreign server receiving these queries is YugabyteDB which does not support querying ctid, these queries fail.
- Queries that perform a two-pass UPDATE or DELETE on a foreign relation:
    - This is typically queries of the form `DELETE FROM relation WHERE col < localfunc()`. Since `localfunc()` is not found on the foreign server, this query is executed in two passes like so:
    - Pass 1: `SELECT ctid, * FROM relation`. The returned tuples are then locally filtered by localfunc() into a ctid-set.
    - Pass 2: `DELETE FROM relation WHERE ctid in ctid-set`.
- Queries that explicitly use ctid as a tuple identifier. This may feature in the projection list of SELECT statements, in the RETURNING clause, and in WHERE clause predicates.

This revision contains the following fixes:
 - Fix min_attr computation: Foreign table are relations created via the `CREATE FOREIGN TABLE` command which have a pg_class relkind value of 'f'. They may map to either a YugabyteDB backed relation (on the same or a different cluster) when the foreign server is of type YugabyteDB or a vanilla Postgres relation when server_type is 'postgres'. These tables are created with a `ybctid` system attribute. This revision ensures that such tables use YBFirstLowInvalidAttributeNumber as min offset in order to work with ybctids. The ybctid attribute is required for those cases where the foreign table returns tuples containing ybctid.
 - This change is propagated to the globally used function `YBGetFirstLowInvalidAttributeNumber(Relation)`. All other functions in the family now use this function as the source of truth. The only exception to this was `YBGetFirstLowInvalidAttrNumber(bool)`. This function has been removed as there was no way to figure out the relation in question from a boolean. All of its callers have now been migrated to `YBGetFirstLowInvalidAttributeNumber(Relation)`.
 -  Postgres CTIDs are fixed length and can be shallow-copied. Ybctids are of variable length and require a memcpy. This revision adds logic to correctly populate ybctid each time postgres_fdw looks up the tuple identifier.
 - Additionally, there is an implicit assumption in the YB code that when a RETURNING list projection is computed, the corresponding tuple always has the corresponding ybctid populated. To work around this, this revision always appends the `ybctid` column to the list of RETURNING projections requested from the foreign server. [GH-27762](https://github.com/yugabyte/yugabyte-db/issues/27762) has been filed to fix the underlying issue.
Jira: DB-13874

Test Plan:
Run the following test:
```
./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressContribPostgresFdw#schedule'
./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressContribYbPostgresFdw#schedule'
```

This revision fixes the output the output of a large number of queries in the `yb.port.postgres_fdw` test.
This is as a result of either:
 - The queries erroring out previously and not running to completion
 - Queries running on incorrect data as a result of previous ModifyTable queries erroring out

A sanity check for the output can be performed using the following command:
```
OUTFILE_REL_PATH=contrib/postgres_fdw/expected/
diff --side-by-side /path/to/postgres_REL_11_STABLE/${OUTFILE_REL_PATH}/postgres_fdw.out /path/to/yuagbyte-dn/src/postgres/${OUTFILE_REL_PATH}/yb.port.postgres_fdw.out
```
.... and checking that there are no changes to any of the outputs that return data.

Additionally, the postgres_fdw test was also run against a postgres instance (as a remote server) manually to ensure that there are no regressions.

Reviewers: amartsinchyk, telgersma

Reviewed By: amartsinchyk

Subscribers: smishra, yql

Tags: #jenkins-ready

Differential Revision: https://phorge.dev.yugabyte.com/D43541
Assets 2
Loading