Automatic Table Creation

Relevant source files

The following files were used as context for generating this wiki page:

• src/meltano_state_backend_postgresql/backend.py
• README.md
• tests/test_integration.py
• tests/test_conninfo.py
• tests/test_meltano.py
• pyproject.toml

The meltano-state-backend-postgresql extension features a mechanism for automatic table creation to facilitate seamless state management in PostgreSQL. When the PostgreSQLStateStoreManager is initialized, it automatically ensures that the required database infrastructure exists, creating the state table if it is not already present in the target database and schema.

This automation eliminates the need for manual DDL (Data Definition Language) execution by the user, ensuring that Meltano can immediately begin storing job states upon connection. The table structure is designed to hold both partial and completed Singer states, keyed by a unique state ID.

Sources: README.md:33-35, src/meltano_state_backend_postgresql/backend.py:175, tests/test_meltano.py:18-20

Architecture and Initialization Flow

The automatic table creation logic is encapsulated within the PostgreSQLStateStoreManager.__init__ method. This method triggers an internal call to _ensure_tables(), which executes the necessary SQL to define the storage schema.

Initialization Sequence

The following diagram illustrates how the manager initializes the connection and verifies the existence of the state table:

Components of Table Identification

The backend uses a table_identifier (a psycopg.sql.Identifier object) to handle namespacing. The location of the table is determined by three factors:

1. Explicit Table Name: Provided via POSTGRESQL_TABLE (defaults to state).
2. Explicit Schema: Provided via POSTGRESQL_SCHEMA.
3. Connection Search Path: If no schema is provided, the backend relies on the PostgreSQL search_path.

Sources: src/meltano_state_backend_postgresql/backend.py:157-169, tests/test_conninfo.py:53-62

Data Model and Schema Definition

The table created by the backend is optimized for storing JSON-based state information. It uses the state_id as the primary key and includes timestamps for tracking updates.

State Table Schema

The following Entity-Relationship diagram represents the schema of the automatically created table:

Field Descriptions

Field	Type	Description
state_id	VARCHAR(900)	The Primary Key identifying the specific Meltano job or state stream.
partial_state	TEXT	Stores the JSON representation of the partial_state.
completed_state	TEXT	Stores the JSON representation of the completed_state.
updated_at	TIMESTAMP	Automatically set to CURRENT_TIMESTAMP on creation and update.

Sources: src/meltano_state_backend_postgresql/backend.py:182-187, src/meltano_state_backend_postgresql/backend.py:206-215

Configuration Parameters

Automatic table creation is influenced by the following settings defined in the project:

Setting Name	Env Var	Default	Description
state_backend.postgresql.table	MELTANO_STATE_BACKEND_POSTGRESQL_TABLE	state	The name of the table to be created.
state_backend.postgresql.schema	MELTANO_STATE_BACKEND_POSTGRESQL_SCHEMA	None	The schema where the table will reside.

Sources: src/meltano_state_backend_postgresql/backend.py:100-112, pyproject.toml:39-40, README.md:61-66

Implementation Detail: _ensure_tables

The implementation uses the CREATE TABLE IF NOT EXISTS syntax to ensure idempotency. This allows the backend to be initialized multiple times or by multiple concurrent processes without causing errors if the table already exists.

# src/meltano_state_backend_postgresql/backend.py:178-189
def _ensure_tables(self) -> None:
    """Ensure the state table exists."""
    with self.connection.cursor() as cursor:
        query = SQL(
            """\
            CREATE TABLE IF NOT EXISTS {table_identifier} (
                state_id VARCHAR(900) PRIMARY KEY,
                partial_state TEXT,
                completed_state TEXT,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """
        ).format(table_identifier=self.table_identifier)
        cursor.execute(query)

Sources: src/meltano_state_backend_postgresql/backend.py:178-189

Conclusion

Automatic Table Creation in the meltano-state-backend-postgresql extension provides a robust, zero-configuration start for users. By leveraging PostgreSQL's CREATE TABLE IF NOT EXISTS and flexible identifier handling, the backend ensures that state storage is always available regardless of whether the user specifies a custom schema or relies on default database settings.