Skip to main content
Solved

DatabaseJoiner doesn't find PostgreSQL record inserted by SQLExecutor on previous feature

kweller
Contributor
Forum|alt.badge.img+8

I have a custom transformer that caches API call results to a PostgreSQL table. It tries a DatabaseJoiner on the key, and if it finds a matching record, it passes the joined feature directly to the main Output port. Otherwise, it calls the API, passes the result to a SQLExecutor to INSERT it for joining on same key value on subsequent features passed to the transformer.

Unfortunately, the DatabaseJoiner is unable to find records stored by the SQLExecutor for subsequent features during the same workspace execution. I've tried explicit COMMITs (with appropriate delimiter declaration) in the SQLExecutor (it says no transaction is in progress) and a 5-second-per-feature Decelerator after the SQLExecutor in case there is some kind of race condition between FME Workbench and PostgreSQL. No luck. I still get duplicate key violations because the failure of the DatabaseJoiner causes the same key to be sent to the API and storage attempted again via the SQLExecutor. I've even tried moving the SQLExecutor to another workspace called via WorkspaceRunner, but the errors persist.

There is no parallelism in this workspace, and it appears that the INSERT is committed every time the SQLExecutor is called. So I can sense no race condition that should cause this behavior. Hoping someone else can shed some light.

Best answer by takashi

Hi @kweller, I guess that the DatabaseJoiner would fetch and cache matched records as testing result for a condition when a feature having the condition is entered at first, and attempt to find matched records from the cache when the second or later feature having the same condition is entered. The cache for a condition would be empty if there were no records that match the first feature having the condition, so the second feature having the same condition would not match any record even if a matched record has been inserted into the database after the first feature was processed.

If my guessing above was correct, the DatabaseJoiner might not be a good choice for your purpose.

A possible workaround is: use a SQLExecutor to execute a SQL statement that returns the number of matched records for each feature, then use a Tester to test if the number is 0. The SQL statement looks something like this.

select count(*) as _number_of_matched_records
from my_table where key = @Value(_key)

  • Attributes to Expose: _number_of_matched_records
  • Combine Attributes: Keep Result Attributes if Conflict
  • Combine Geometry: Initiator Geometry Only

View original
Did this help you find an answer to your question?

13 replies

david_r
Evangelist
  • david_rEvangelist
  • May 23, 2018

What happens if you set a breakpoint just after the SQLExecutor and you look at the contents of the postgresql table using e.g. pgAdmin -- is the new record there?

kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • May 23, 2018
david_r wrote:

What happens if you set a breakpoint just after the SQLExecutor and you look at the contents of the postgresql table using e.g. pgAdmin -- is the new record there?

Yes, it is there when I look in Navicat, but the DatabaseJoiner still doesn't find it on subsequent call. And I've confirmed that the keys are identical across the board. Thoroughly checked breakpoints after DatabaseJoiner and both Initiator and output ports from SQLExecutor. :-(

 

kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • May 23, 2018

And I've just tried replacing the DatabaseJoiner with another SQLExecutor and a FeatureMerger...same problem. So it's not just the DatabaseJoiner that misbehaves.

david_r
Evangelist
  • david_rEvangelist
  • May 23, 2018

Try something like the following in the SQLExecutor that's inserting the record:

FME_SQL_DELIMITER #
insert into ...
#

This should force FME into commiting any ongoing transactions before continuing.

kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • May 23, 2018
david_r wrote:

Try something like the following in the SQLExecutor that's inserting the record:

FME_SQL_DELIMITER #
insert into ...
#

This should force FME into commiting any ongoing transactions before continuing.

Tried that before I posted the original question, but I've since made other changes, so tried again.  Same problem.  Besides, if the new record shows up in Navicat, then it doesn't seem like transaction demarcation is the problem.
kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • May 23, 2018

BTW, I've also shut off all prefetch options in my DatabaseJoiner, so it should not be prefetching the results before passing the features through. Though I must admit that it looks like it's doing something like that anyway.

takashi
Supporter
  • takashiSupporter
  • Best Answer
  • May 27, 2018

Hi @kweller, I guess that the DatabaseJoiner would fetch and cache matched records as testing result for a condition when a feature having the condition is entered at first, and attempt to find matched records from the cache when the second or later feature having the same condition is entered. The cache for a condition would be empty if there were no records that match the first feature having the condition, so the second feature having the same condition would not match any record even if a matched record has been inserted into the database after the first feature was processed.

If my guessing above was correct, the DatabaseJoiner might not be a good choice for your purpose.

A possible workaround is: use a SQLExecutor to execute a SQL statement that returns the number of matched records for each feature, then use a Tester to test if the number is 0. The SQL statement looks something like this.

select count(*) as _number_of_matched_records
from my_table where key = @Value(_key)

  • Attributes to Expose: _number_of_matched_records
  • Combine Attributes: Keep Result Attributes if Conflict
  • Combine Geometry: Initiator Geometry Only

kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • May 29, 2018
takashi wrote:

Hi @kweller, I guess that the DatabaseJoiner would fetch and cache matched records as testing result for a condition when a feature having the condition is entered at first, and attempt to find matched records from the cache when the second or later feature having the same condition is entered. The cache for a condition would be empty if there were no records that match the first feature having the condition, so the second feature having the same condition would not match any record even if a matched record has been inserted into the database after the first feature was processed.

If my guessing above was correct, the DatabaseJoiner might not be a good choice for your purpose.

A possible workaround is: use a SQLExecutor to execute a SQL statement that returns the number of matched records for each feature, then use a Tester to test if the number is 0. The SQL statement looks something like this.

select count(*) as _number_of_matched_records
from my_table where key = @Value(_key)

  • Attributes to Expose: _number_of_matched_records
  • Combine Attributes: Keep Result Attributes if Conflict
  • Combine Geometry: Initiator Geometry Only

I already tried something similar, but even the SQLExecutor could not find the new record, even though Navicat could.  However, I believe I've identified and solved the problem.  I will post
kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • May 29, 2018
takashi wrote:

Hi @kweller, I guess that the DatabaseJoiner would fetch and cache matched records as testing result for a condition when a feature having the condition is entered at first, and attempt to find matched records from the cache when the second or later feature having the same condition is entered. The cache for a condition would be empty if there were no records that match the first feature having the condition, so the second feature having the same condition would not match any record even if a matched record has been inserted into the database after the first feature was processed.

If my guessing above was correct, the DatabaseJoiner might not be a good choice for your purpose.

A possible workaround is: use a SQLExecutor to execute a SQL statement that returns the number of matched records for each feature, then use a Tester to test if the number is 0. The SQL statement looks something like this.

select count(*) as _number_of_matched_records
from my_table where key = @Value(_key)

  • Attributes to Expose: _number_of_matched_records
  • Combine Attributes: Keep Result Attributes if Conflict
  • Combine Geometry: Initiator Geometry Only

I think we can also safely say that the DatabaseJoiner is not caching its data on its own (without explicit prefetching).  That's good to know!
kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • May 29, 2018

It appears that this was actually caused by single quotes in the key values. As soon as I installed some logic to defend against SQL injection (including quote escapes), the problem went away. I suspect that when I was comparing key values during diagnosis, the differences between what was actually stored and what I was querying for were masked thanks to the escape mechanisms.

kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • June 3, 2018
kweller wrote:

It appears that this was actually caused by single quotes in the key values. As soon as I installed some logic to defend against SQL injection (including quote escapes), the problem went away. I suspect that when I was comparing key values during diagnosis, the differences between what was actually stored and what I was querying for were masked thanks to the escape mechanisms.

Well, this has reared its ugly head again. Happens less often, but it happens. :-( Submitted support request, will post ultimate solution here.

 

 

kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • June 8, 2018
kweller wrote:
Well, this has reared its ugly head again. Happens less often, but it happens. :-( Submitted support request, will post ultimate solution here.

 

 

Resolved for good. See final response comment under Best Answer above.
kweller
Contributor
Forum|alt.badge.img+8
  • kwellerContributor
  • Author
  • Contributor
  • June 8, 2018
takashi wrote:

Hi @kweller, I guess that the DatabaseJoiner would fetch and cache matched records as testing result for a condition when a feature having the condition is entered at first, and attempt to find matched records from the cache when the second or later feature having the same condition is entered. The cache for a condition would be empty if there were no records that match the first feature having the condition, so the second feature having the same condition would not match any record even if a matched record has been inserted into the database after the first feature was processed.

If my guessing above was correct, the DatabaseJoiner might not be a good choice for your purpose.

A possible workaround is: use a SQLExecutor to execute a SQL statement that returns the number of matched records for each feature, then use a Tester to test if the number is 0. The SQL statement looks something like this.

select count(*) as _number_of_matched_records
from my_table where key = @Value(_key)

  • Attributes to Expose: _number_of_matched_records
  • Combine Attributes: Keep Result Attributes if Conflict
  • Combine Geometry: Initiator Geometry Only

OK, turns out that the quoting issue was masking the ultimate solution, which is to use a SQLExecutor instead of a DatabaseJoiner.  The latter must pre-cache the results regardless of the pre-cache settings.  Confusing and unintuitive, but true.

Reply


Helpful Members This Week

Community Stats

31,039
Posts
117,660
Replies
38,818
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.