[I] Improve Handling of Existing Tables in PostgreSQL Sink

["stefaniesinner (via GitHub)" <gi...@apache.org>]

stefaniesinner opened a new issue, #4382:
URL: https://github.com/apache/streampipes/issues/4382

   ### Apache StreamPipes version
   
   dev (current development state)
   
   ### Affected StreamPipes components
   
   Backend
   
   ### What happened?
   
   Currently, the PostgreSQL Sink assumes full control over table creation. When a pipeline is started, the sink always attempts to execute a `CREATE TABLE` statement based on the incoming event schema. If the user configures the sink to write into a pre-existing PostgreSQL table, the pipeline can fail.
   
   Specifically, two main errors can occur with existing tables:
   
   1. If the table uses standard PostgreSQL types, like `DOUBLE PRECISION` or `CHARACTER VARYING`, the client crashes with a `No enum constant` error.
   
   <img width="2500" height="1200" alt="Image" src="https://github.com/user-attachments/assets/87daad8c-0c00-4e5a-86cb-e9a4987ed7af" />
   
   2. If the table is missing a column, the sink throws a generic `Table does not match the EventProperties` error instead of explaining exactly what is missing or wrong.
   
   <img width="2500" height="1200" alt="Image" src="https://github.com/user-attachments/assets/1f16d4e4-5ac2-431c-86e0-863c9b686faa" />
   
   Supporting existing schemas without altering them is essential for production environments and enterprise data governance.
   
   ### How to reproduce?
   
   To trigger the **first error**:
   
   1. Set up a PostgreSQL database.
   2. Create a table using types like `DOUBLE PRECISION` or `VARCHAR`. For example: 
   ```sql
   CREATE TABLE sensor_data (
      "timestamp" BIGINT,
      "sensorId" VARCHAR(255),
      "mass_flow" DOUBLE PRECISION
   );
   ```
   3. Create an adapter with a data stream that matches this schema.
   4. Create a new pipeline. Connect the data stream to the PostgreSQL sink.
   5. Configure the sink with your database credentials.
   6. Save and try to start the pipeline.
   
   The pipeline will crash with the `No enum constant` error.
   
   To trigger the **second error**:
   
   1. Do the same as before, but use a table with missing columns. For example:
   ```sql
   CREATE TABLE sensor_data (
      "timestamp" BIGINT
   );
   ```
   2. Save and try to start the pipeline again.
   
   The pipeline will fail with the generic `Table does not match the EventProperties` error.
   
   ### Expected behavior
   
   The PostgreSQL sink should be able to reliably write to existing PostgreSQL tables without trying to recreate them. Specifically, the sink needs to:
   
   - Check if the target table already exists in the database
   - Skip the `CREATE TABLE` statement if the table is already there
   - Validate the incoming stream data against the existing database schema
   - Show a clear error message in the UI if things do not match (e.g., exactly which column is missing or has the wrong data type)
   - Seamlessly insert the data if everything is correct, including support for batch inserts and timestamps
   
   To achieve this behavior, we can make the following changes in the codebase:
   
   - Use `information_schema.columns` to securely read the existing table schema from PostgreSQL
   - Fix the `DbDataTypes` enum and the JDBC mapping logic so that it correctly translates multi-word PostgreSQL types like `DOUBLE PRECISION` or `CHARACTER VARYING`
   - Bump the pipeline element version and define a migration in the Extension Service. If we add a new UI toggle (like "Append to existing table"), this step guarantees backward compatibility. It ensures that older user pipelines don't break and are automatically updated to the new format on startup
   
   ### Additional technical information
   
   _No response_
   
   ### Are you willing to submit a PR?
   
   Yes, I am willing to submit a PR!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@streampipes.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] Improve Handling of Existing Tables in PostgreSQL Sink

["dominikriemer (via GitHub)" <gi...@apache.org>]

dominikriemer closed issue #4382: Improve Handling of Existing Tables in PostgreSQL Sink
URL: https://github.com/apache/streampipes/issues/4382


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@streampipes.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org