<#3157 bug: Primary Key Column Order Mismatch in `...
# meltano-repo-activity
g
#3157 bug: Primary Key Column Order Mismatch in
create_empty_table
Issue created by edgarrmondragon ### Singer SDK Version 0.47.4 ### Is this a regression? • Yes ### Python Version NA ### Bug scope Targets (data type handling, batching, SQL object generation, etc.) ### Operating System No response ### Description ## Summary The
create_empty_table
method doesn’t maintain the order of columns specified in the
primary_keys
sequence when creating the primary key constraint. Instead, it adheres to the order of properties as they are listed in the
schema
, which can result in erroneous primary key definitions. ## Description In the current implementation of
create_empty_table
sdk/singer_sdk/connectors/sql.py Line 1279 in</meltano/sdk/commit/bba4d8818fcf77a015437ee785713aa50b1559fe|bba4d88> | def create_empty_table( | | ----------------------- | (downstream too: https://github.com/MeltanoLabs/target-postgres/blob/3d16266bcc1be6d01f09a90607d5288bbfeb3374/target_postgres/connector.py#L421) the method iterates through
schema['properties'].items()
and adds columns, setting the primary key flag if a column exists the primary_keys sequence. This means the primary key columns will be ordered according to how properties are defined in the schema dictionary, not according to the explicit order specified in the
primary_keys
sequence. This does not allow you to rely on the primary key column order that clients specify. Primary key order matters for database performance and indexing strategies. Additionally, the same schema with different property ordering could result in different primary key definitions. ## Expected Behavior The primary key columns should be created in the exact order specified in the
primary_keys
sequence parameter. ## Suggested Fix Do not set the
primary_key
property on the column and use
PrimaryKeyConstraint
. def create_empty_table( # noqa: PLR0913 self, table_name: str, meta: sa.MetaData, schema: dict[str, Any], connection: sa.engine.Connection, primary_keys: t.Sequence[str] | None = None, partition_keys: list[str] | None = None, as_temp_table: bool = False, ) -> sa.Table: """Create an empty target table. Args: table_name: the target table name. meta: the SQLAlchemy metadata object. schema: the JSON schema for the new table. connection: the database connection. primary_keys: list of key properties. partition_keys: list of partition keys. as_temp_table: True to create a temp table. Returns: The new table object. Raises: NotImplementedError: if temp tables are unsupported and as_temp_table=True. RuntimeError: if a variant schema is passed with no properties defined. """ columns: list[sa.Column[Any]] = [] primary_keys = primary_keys or [] try: properties: dict[str, Any] = schema["properties"] except KeyError: raise RuntimeError( f"Schema for table_name: '{table_name}'" f"does not define properties: {schema}" ) from None for property_name, property_jsonschema in properties.items(): columns.append( sa.Column( property_name, self.to_sql_type(property_jsonschema), autoincrement=False, # See: https://github.com/MeltanoLabs/target-postgres/issues/193 # noqa: E501 ) ) table_args = [] if primary_keys: missing_pk_columns = [col for col in primary_keys if col not in properties] if missing_pk_columns: raise RuntimeError( f"Primary keys {missing_pk_columns} are not defined in" f"schema {schema} for table_name '{table_name}'" ) table_args.append(sa.PrimaryKeyConstraint(*primary_keys)) table_kwargs: dict[str, Any] = {} if as_temp_table: table_kwargs["prefixes"] = ["TEMPORARY"] new_table = sa.Table(table_name, meta, *columns, *table_args, **table_kwargs) new_table.create(bind=connection) return new_table ### Link to Slack/Linen No response meltano/sdk