[ETL] Seems to be failing when address renamed

[mahalakshme]

Issue:

1. org: apfodisha, env: prod
   scheduled_job_run table: id IN (502396, 502319, 502244)
   Looks like on 12th Nov, Barapada was renamed(stating from last_modified_date_time) to Barapadasahi, since then it is failing

2. Similar issue in prerelease - org: apfodisha for address with name 42 Colony(id: 332232)
   From ETL and public data looks like it was renamed(stating from last_modified_date_time) from 42 Colony to 42Colony1 - since then it is failing

But was unable to reproduce this issue by renaming '42 Colony' to '42 Colony1' in apfodisha UAT org in prerelease

AC:

• Though rerunning ETL will fix the issue, these kind of issues are delaying the implementation work
• Find the root cause of the above issue(come up with Steps to reproduce) and address it.

Stacktrace:

org.springframework.dao.DuplicateKeyException: StatementCallback; SQL [insert into "apfodisha".address ("Village/Hamlet", "Village/Hamlet id", id, "uuid",
  "is_voided", "created_by_id", "last_modified_by_id",
  "created_date_time", "last_modified_date_time", "organisation_id", "gps_coordinates"
  , "SAMPURNA Village", "Geographically hard to reach village", "PPK Village", "PVTG Village")
(with address_concept_maps as (SELECT public.hstore((array_agg(c2.uuid)) :: text [], (array_agg(c2.name)) :: text []) AS map
FROM public.concept
join public.concept_answer a on concept.id = a.concept_id
join public.concept c2 on a.answer_concept_id = c2.id
where concept.uuid in ('dummy', '5c1f9940-2a3f-4f86-ac54-48612cbb5129', 'b65ea6e5-b3cf-459e-9902-6012cbf73794', 'e9a75284-7fa0-46f2-a519-0999779dd507', 'd06665f5-904a-452c-a776-130cb0915ec6'))
select entity.title,
  entity.id,
  entity.id,
  entity.uuid,
  entity.is_voided,
  entity.created_by_id,
  entity.last_modified_by_id,
  entity.created_date_time,
  entity.last_modified_date_time,
  entity.organisation_id,
  entity.gps_coordinates
  ,public.get_coded_string_value(entity.location_properties-> '5c1f9940-2a3f-4f86-ac54-48612cbb5129', address_concept_maps.map)::TEXT as "SAMPURNA Village",
  public.get_coded_string_value(entity.location_properties-> 'b65ea6e5-b3cf-459e-9902-6012cbf73794', address_concept_maps.map)::TEXT as "Geographically hard to reach village",
  public.get_coded_string_value(entity.location_properties-> 'e9a75284-7fa0-46f2-a519-0999779dd507', address_concept_maps.map)::TEXT as "PPK Village",
  public.get_coded_string_value(entity.location_properties-> 'd06665f5-904a-452c-a776-130cb0915ec6', address_concept_maps.map)::TEXT as "PVTG Village"
from address_level entity
join address_level_type alt on entity.type_id = alt.id
cross join address_concept_maps
where alt.name = 'Village/Hamlet'
and entity.last_modified_date_time > '2024-11-12T03:06:39.477'
and entity.last_modified_date_time <= '2024-11-12T04:56:34.543');]; ERROR: duplicate key value violates unique constraint "address_id_idx"
Detail: Key (id)=(373617) already exists.
  at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:103)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1538)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
at org.avniproject.etl.repository.sync.AddressTableSyncAction.lambda$runAddressQuery$4(AddressTableSyncAction.java:76)
at org.avniproject.etl.repository.JdbcContextWrapper.wrap(JdbcContextWrapper.java:33)
at org.avniproject.etl.repository.JdbcContextWrapper.wrapInOrgContext(JdbcContextWrapper.java:21)
at org.avniproject.etl.repository.JdbcContextWrapper.runInOrgContext(JdbcContextWrapper.java:10)
at org.avniproject.etl.repository.sync.AddressTableSyncAction.runAddressQuery(AddressTableSyncAction.java:75)
at org.avniproject.etl.repository.sync.AddressTableSyncAction.lambda$insertLowestLevelAddresses$1(AddressTableSyncAction.java:50)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
at org.avniproject.etl.repository.sync.AddressTableSyncAction.insertLowestLevelAddresses(AddressTableSyncAction.java:45)
at org.avniproject.etl.repository.sync.AddressTableSyncAction.perform(AddressTableSyncAction.java:39)
at jdk.internal.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:569)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)

[1t5j0y]

There is an unique index address_id_idx on the address table only for apfodisha org which is not created by ETL process that is causing this issue.

On prod, this index appears to have already been dropped and ETL is completing successfully.

Have dropped this index on prerelease as well.

openchs: apfodisha, public> drop index if exists address_id_idx
[2024-12-10 11:50:48] completed in 38 ms

[1t5j0y]

Confirmed ETL is completing for apfodisha on prerelease.

2024-12-10 07:53:50,400 INFO Avni ETL_Worker-1 [etl.service.EtlService] Completed ETL for schema apfodisha with dbUser apfodisha and schemaUser apfodisha