Solved

Writer trying to update a non-null Postgres field with a null value


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.

icon

Best answer by markatsafe 19 May 2021, 01:05

View original

2 replies

Badge +2

@pbyhistorian​ hard to determine the cause without seeing the workspace run. Try using Feature Caching and check the date_created is actually removed from the feature before it hits the writer.

@markatsafe thanks! That got me to the solution.

 

I've seen this problem mentioned elsewhere with no resolution, so here's what was happening in my case:

 

The date_created Attribute was unchecked in the Reader's Attribute list, and was not present in the Features - until the Features passed through a ChangeDetector. There, the Columns... button in the Visual Preview revealed that date_created was present but not selected (IOW, it was hidden).

 

A lookup-database Reader feeds the ChangeDetector's Revised port. Although that Reader's date_created Attribute was also unchecked, that Reader (alone) had Attributes to Read set to All Attributes. So the identically-named-but-inappropriate Attribute from the lookup database was sneaking, hidden, all the way to the Writer.

 

Changing the lookup-database Reader's Attributes to Read setting to to Exposed Attributes (like all of the other Readers) removed the rogue date_created Attribute and solved the problem.

 

So now I know to pay attention to Attributes to Read.

Reply