bug(connector): PostgreSQL CDC treats PRIMARY KEY INCLUDE columns as key columns

Describe the bug

When RisingWave auto-derives the schema of a PostgreSQL CDC table, primary-key discovery matches every attribute in pg_index.indkey:

JOIN pg_attribute a
  ON a.attrelid = i.indrelid
 AND a.attnum = ANY(i.indkey)

However, PostgreSQL stores both key attributes and non-key INCLUDE payload attributes in indkey. Only the first indnkeyatts entries are actual key columns.

As a result, for an upstream constraint such as:

PRIMARY KEY (tenant_id, order_id) INCLUDE (payload)

RisingWave discovers tenant_id, order_id, payload as the primary key instead of tenant_id, order_id. The extra column can then become part of the RisingWave primary/distribution key and CDC snapshot pagination even though PostgreSQL does not enforce uniqueness on it and does not consider it part of the primary key.

The relevant query is:

https://github.com/risingwavelabs/risingwave/blob/c4a578bb71145dab308d64c676d8009033b00d01/src/connector/src/connector_common/postgres.rs#L44-L54

Error message/log

No error is reported. Schema discovery returns included payload columns as primary-key columns.

To Reproduce

1. Create a PostgreSQL table with an included column in its primary-key index:

CREATE TABLE public.orders (
    tenant_id INT NOT NULL,
    order_id INT NOT NULL,
    payload TEXT,
    PRIMARY KEY (tenant_id, order_id) INCLUDE (payload)
);

1. Confirm PostgreSQL's index metadata:

SELECT
    a.attname,
    array_position(i.indkey, a.attnum) AS index_position,
    i.indnkeyatts
FROM pg_index i
JOIN pg_class c ON c.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a
  ON a.attrelid = i.indrelid
 AND a.attnum = ANY(i.indkey)
WHERE n.nspname = 'public'
  AND c.relname = 'orders'
  AND i.indisprimary
ORDER BY index_position;

This returns all three attributes, while indnkeyatts is 2.

1. Create a PostgreSQL CDC source and auto-derived table:

CREATE TABLE orders_cdc (*)
FROM pg_source TABLE 'public.orders';

1. Run:

DESCRIBE orders_cdc;

The derived primary key includes payload.

Expected behavior

RisingWave should discover only the actual PostgreSQL primary-key attributes and preserve their index order:

tenant_id, order_id

Primary-key discovery should restrict indkey to its first indnkeyatts entries. For example, it could filter on:

array_position(i.indkey, a.attnum) <= i.indnkeyatts

or use unnest(i.indkey) WITH ORDINALITY and retain ordinals through indnkeyatts.

PostgreSQL documents that indkey contains indnatts entries, including payload attributes, while only indnkeyatts entries are key columns:

https://www.postgresql.org/docs/current/catalog-pg-index.html

How did you deploy RisingWave?

Found by code inspection.

The version of RisingWave

Current main at c4a578bb71145dab308d64c676d8009033b00d01.

Additional context

This is adjacent to, but different from, #26846. PostgreSQL CDC already preserves composite primary-key index order with ORDER BY array_position(i.indkey, a.attnum); the problem here is that the query does not exclude non-key INCLUDE attributes.