The Writer is generating SQL for an Attribute that doesn't exist in the Feature, during an Update operation on a Postgres table.
Here is the error message:
Error executing SQL command ('update "schema_name"."table_name" set "date_created" = NULL,"date_updated" = E'2021-05-18T07:47:58.73945-07:00',"distance" = E'1.00' WHERE "unique_id" = E'51''): 'ERROR: null value in column "date_created" violates not-null constraint
During an Update operation, date_created should not be changed. Although the data flow is read from the same table it's being written to, an AttributeManager specifically removes date_created from the Features on the ChangeDetector's Updated port.
unique_id=51 is the very first Feature being fed to the Writer. Like every other Feature from the Updated port, its fme_db_operation Attribute is "UPDATE". The Feature Operation field of the Writer is configured to use fme_db_operation, and its Match Columns field is set to unique_id.
The Writer was created by reading the corresponding Reader's Feature schema. I tried changing the Attribute Definition field (on the User Attributes tab) to Automatic, so it would match the incoming Features (i.e. make the date_created field disappear) but this had no effect.
I'd rather not use a SQLExecutor because:
1) This workspace actually contains three Reader/Writer pairs for three identical tables in different environments (Local, Development, QA). Tester Transformers after each Reader and before its Writer allow only one pair at a time to use the workspace's core logic. The Local environment works fine while the Development environment fails with the error above.
2) The Local environment also works fine in the original, slightly more ambiguous design, where Features on the ChangeDetector's Inserted port also pass through an AttributeManager (to assign "now" to date_created) before they are merged with the Updated Features at the (Writer) Tester's input. Now the Tester (and therefore the Writer) knows about the date_created Attribute. However, the attribute is only present on Inserted Features and the Local-environment Writer (using fme_db_operation) generates the proper SQL for Features that have date_created (fme_db_operation = "INSERT") as well as those that don't (fme_db_operation) = "UPDATE").
Turning off the Writer's Bulk Insert mode has no effect.
Changing the Writer's Match Columns field to a different unique id (rather than the primary key) has no effect.
Disconnecting the Inserted feed and changing the Writer's Feature Operation to "Update" has no effect.
How do I solve this incorrectly-generated SQL?
Possible causes/options:
1) I had to install a newer version of Postres in my Local environment than the version in our Development and QA environments. Perhaps the Writer is getting a different response from the Local table than the Development table (even though the Local table was created with the creation-SQL pulled from the Development table using DataGrip).
2) Maybe re-creating the Writer manually, rather than from its Reader, will let me define date_created in a way that keeps the Writer from generating SQL that writes NULL when date_created isn't included.
Sorry for being so verbose, but every other post I've seen on this topic leaves me with more questions.
One last thing:
I'm running FME Desktop v2019.2.3.2 (20200320 - Build 19825 - WIN64).
My Local version of Postgres is 13.2, build 1914, 64-bit.
Our Development version of Postgres is 11.9, Linux, 64-bit.