risingwavelabs/risingwave · error · anyhow::Error

Postgres table should define the primary key for non-append-

Error message

Postgres table should define the primary key for non-append-only tables

What it means

RisingWave's PostgreSQL connector requires a primary key for any non-append-only source table, because CDC row updates/deletes must be keyed. `connect` throws this error when the discovered table schema (`pk_names`) is empty while `is_append_only` is false.

Source

Thrown at src/connector/src/connector_common/postgres.rs:453

                        Some(scalar),
                    ),
                    Err(err) => {
                        tracing::warn!(
                            error=%err.as_report(),
                            "failed to parse the PostgreSQL default value expression; only constants are supported",
                        );
                        ColumnDesc::named(col.name.clone(), ColumnId::placeholder(), rw_data_type)
                    }
                }
            } else {
                ColumnDesc::named(col.name.clone(), ColumnId::placeholder(), rw_data_type)
            };
            column_descs.push(column_desc);
        }

        // Check primary key existence using the directly discovered pk_names
        if !is_append_only && pk_names.is_empty() {
            return Err(anyhow!(
                "Postgres table should define the primary key for non-append-only tables"
            )
            .into());
        }

        Ok(Self {
            column_descs,
            pk_names,
        })
    }

    // return the mapping from column name to pg type, the pg type is used for writing data to postgres
    pub async fn type_mapping(
        config: &PgConnectionConfig,
        schema: &str,
        table: &str,
        is_append_only: bool,
    ) -> ConnectorResult<HashMap<String, tokio_postgres::types::Type>> {

View on GitHub (pinned to 6469eb736d)

Solutions

1. Add a primary key to the upstream table: `ALTER TABLE <table> ADD PRIMARY KEY (<cols>);`
2. If the table truly receives only inserts, declare the RisingWave source as append-only so the PK requirement is skipped
3. Use a UNIQUE NOT NULL column set as the table primary key

Example fix

-- before
CREATE TABLE orders (id BIGINT, amount NUMERIC);
-- after
CREATE TABLE orders (id BIGINT PRIMARY KEY, amount NUMERIC);

-- before creating the RW source
SELECT COUNT(*) FROM pg_constraint
WHERE contype = 'p' AND conrelid = 'public.orders'::regclass; -- must be > 0

if let Err(e) = connect_result {
    if e.to_string().contains("should define the primary key") {
        // either add a PK upstream or declare the source append-only
        return Err(PlanError::MissingPrimaryKey(e));
    }
    return Err(e.into());
}

When it happens

Trigger: Creating a CDC table/source via `PgCdcConnector::connect` on a Postgres table with no PRIMARY KEY or UNIQUE constraints recognized as pk_names, with append-only mode not enabled.

Common situations: Upstream table was created without a primary key; the key is enforced only at application level; the user assumed RisingWave tolerates keyless tables for CDC.

Understand the failure class

Background: Schema validation failed / invalid input schema: payload rejected because its shape doesn't match the expected schema — this error's family across 28 libraries.

Related errors

• Debezium Mongo needs a `_id` column in table
• {connector_name} snapshot primary-key index {index} is out o
• {connector_name} snapshot primary key `{}` cannot be NULL
• unsupported PostgreSQL snapshot data type {data_type} for co
• MySQL table doesn't define the primary key

AI-assisted analysis of risingwavelabs/risingwave@6469eb736d (2026-09-11). Data as JSON: /api/errors/7d9625a843230c75. Report an issue: GitHub.