Urgent | Analytics Table Management Issue

Vishwanideep_Kumar · 20 February 2025 13:55

Hi Team,

I am trying to generate metadata file for visualisations from below API

GET /api/metadata.json?skipSharing=true&visualizations=true&dashboards=true&eventCharts=true&eventReports=true&download=true

which fails with 500 error.

When I check DHIS logs, I get below error

Caused by: org.postgresql.util.PSQLException: ERROR: relation "analytics_event_pqflle3gg9q" does not exist
  Position: 70
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236) ~[postgresql-42.2.19.jar:42.2.19]
        at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:327) ~[c3p0-0.9.5.5.jar:0.9.5.5]
        at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:452) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ~[spring-jdbc-5.3.18.jar:5.3.18]
        ... 133 more

To resolve this particular error, I am performing following steps:

Apps>Data Management>Analytics Table Management>Export

while Updating resource table, i get below error

* INFO  2025-02-20T11:11:21,382 Updating resource tables (NotificationLoggerUtil.java [taskScheduler-12])
* ERROR 2025-02-20T11:11:52,024 Process failed: PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists. (NotificationLoggerUtil.java [taskScheduler-12])
* ERROR 2025-02-20T11:11:52,029 Process failed: PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists. (NotificationLoggerUtil.java [taskScheduler-12])
* ERROR 2025-02-20T11:11:52,029 Job failed: 'inMemoryAnalyticsJob' (AbstractSchedulingManager.java [taskScheduler-12])
org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists.
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:247) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:691) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1034) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1088) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1079) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.batchUpdate(JdbcResourceTableStore.java:212) ~[dhis-service-administration-2.37.8.1-SNAPSHOT.jar:?]
        at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.generateResourceTable(JdbcResourceTableStore.java:135) ~[dhis-service-administration-2.37.8.1-SNAPSHOT.jar:?]
        at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDatePeriodTable(DefaultResourceTableService.java:187) ~[dhis-service-administration-2.37.8.1-SNAPSHOT.jar:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.15.RELEASE.jar:5.2.15.RELEASE]
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205) ~[spring-aop-5.2.15.RELEASE.jar:5.2.15.RELEASE]
        at com.sun.proxy.$Proxy356.generateDatePeriodTable(Unknown Source) ~[?:?]
        at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateResourceTables(DefaultAnalyticsTableGenerator.java:222) ~[dhis-service-analytics-2.37.8.1-SNAPSHOT.jar:?]
        at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateTables(DefaultAnalyticsTableGenerator.java:126) ~[dhis-service-analytics-2.37.8.1-SNAPSHOT.jar:?]
        at org.hisp.dhis.analytics.table.scheduling.AnalyticsTableJob.execute(AnalyticsTableJob.java:82) ~[dhis-service-analytics-2.37.8.1-SNAPSHOT.jar:?]
        at org.hisp.dhis.scheduling.AbstractSchedulingManager.execute(AbstractSchedulingManager.java:278) ~[dhis-service-core-2.37.8.1-SNAPSHOT.jar:?]
        at org.hisp.dhis.scheduling.DefaultSchedulingManager.lambda$executeNow$4(DefaultSchedulingManager.java:184) ~[dhis-service-core-2.37.8.1-SNAPSHOT.jar:?]
        at org.hisp.dhis.scheduling.DefaultSchedulingManager.lambda$runIfPossible$5(DefaultSchedulingManager.java:212) ~[dhis-service-core-2.37.8.1-SNAPSHOT.jar:?]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) [?:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) [spring-context-5.3.18.jar:5.3.18]
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) [?:?]
        at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) [?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
        at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1404) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1429) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:507) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:878) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:901) ~[postgresql-42.2.19.jar:42.2.19]
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1644) ~[postgresql-42.2.19.jar:42.2.19]
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2544) ~[c3p0-0.9.5.5.jar:0.9.5.5]
        at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$4(JdbcTemplate.java:1048) ~[spring-jdbc-5.3.18.jar:5.3.18]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651) ~[spring-jdbc-5.3.18.jar:5.3.18]
        ... 29 more
* ERROR 2025-02-20T11:11:52,030 org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists.
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:247)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:691)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1034)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1088)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1079)
        at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.batchUpdate(JdbcResourceTableStore.java:212)
        at org.hisp.dhis.resourcetable.jdbc.JdbcResourceTableStore.generateResourceTable(JdbcResourceTableStore.java:135)
        at org.hisp.dhis.resourcetable.DefaultResourceTableService.generateDatePeriodTable(DefaultResourceTableService.java:187)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)
        at com.sun.proxy.$Proxy356.generateDatePeriodTable(Unknown Source)
        at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateResourceTables(DefaultAnalyticsTableGenerator.java:222)
        at org.hisp.dhis.analytics.table.DefaultAnalyticsTableGenerator.generateTables(DefaultAnalyticsTableGenerator.java:126)
        at org.hisp.dhis.analytics.table.scheduling.AnalyticsTableJob.execute(AnalyticsTableJob.java:82)
        at org.hisp.dhis.scheduling.AbstractSchedulingManager.execute(AbstractSchedulingManager.java:278)
        at org.hisp.dhis.scheduling.DefaultSchedulingManager.lambda$executeNow$4(DefaultSchedulingManager.java:184)
        at org.hisp.dhis.scheduling.DefaultSchedulingManager.lambda$runIfPossible$5(DefaultSchedulingManager.java:212)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey"
  Detail: Key (dateperiod)=(2021-01-31) already exists.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
        at org.postgresql.core.v3.QueryExecutorImpl.flushIfDeadlockRisk(QueryExecutorImpl.java:1404)
        at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1429)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:507)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:878)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:901)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1644)
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:2544)
        at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$4(JdbcTemplate.java:1048)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651)
        ... 29 more
 (AbstractSchedulingManager.java [taskScheduler-12])

I have followed, multiple community chats, where people have discussed this issue and also have followed few steps as well:

Cleared materialized views, prune periods, clear cache from maintenance and regenerate analytics table but face the same issue, so i generated while. checking skip resource table, to atleast generate other analytics table.
Checked for the given dateperiod (2021-01-31) under _dateperiodstructure_temp table but nothing shows up.
Checked period table and could see below result, but unable to delete due to constraints, hence ruled out this approach.


ocahisv2=# Select * from period where startdate = '2021-01-31' or enddate = '2021-01-31';
 periodid | periodtypeid | startdate  |  enddate
----------+--------------+------------+------------
   171487 |            3 | 2021-01-01 | 2021-01-31
   205182 |            2 | 2021-01-25 | 2021-01-31
  1693509 |        81910 | 2021-01-31 | 2021-02-06
(3 rows)

Please find below DHIS version details

Build revision	ceb94ad
Build date	February 24, 2023 at 12:54
Jasper reports version	6.3.1
User agent	Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:135.0) Gecko/20100101 Firefox/135.0
Server date	February 20, 2025 at 12:41
Last analytics table generation	February 20, 2025 at 11:39
Time since last analytics table generation	1 h, 1 m, 57 s
Last analytics table runtime	00:00:22.753
Environment variable	DHIS2_HOME
System ID	139734d8-3048-4a7f-b6a9-99844e798009
Last monitoring success	Never
External configuration directory	/root/dhis2_home
File store provider	filesystem
Java version	11.0.16
Java vendor	Amazon.com Inc.
OS name	Linux
OS architecture	amd64
OS version	4.19.0-21-amd64
Memory info	Mem Total in JVM: 2000 Free in JVM: 895 Max Limit: 4000
CPU cores	4
Calendar	iso8601

@Gassim @dhis2-analytics

Vishwanideep_Kumar · 20 February 2025 13:56

Hello Everyone

I know a lot of people have faced this issue but I couldnt conclude reading through topics, what is the exact fix for the issue.

For me when I run Analytics Table, I get error as below:

Process failed: PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey” Detail: Key (dateperiod)=(2021-01-31) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “_dateperiodstructure_temp_pkey” Detail: Key (dateperiod)=(2021-01-31) already exists.

I have tried below steps mentioned across various community chats like:

Running Maintenance(Clear Analytics table, Clear Cache)
Checked for Improper year formats in db under period/_dateperiodstructure.

My intention is to download metadata file for visualization, for which the API fails with 500 and when I check logs i get that ref table analytics_event_pqflle3gg9q is unavailable. Therefore, I run Analytics table maintenance.

Please help!

@Gassim @dhis2-analytics

Gassim · 20 February 2025 15:12

Thanks @Vishwanideep_Kumar ! For the details. It looks like one of those database issues, but I’ve triaged this to @dhis2-analytics !

Vishwanideep_Kumar · 21 February 2025 06:16

Hi @Gassim

You can please mark this conversation as Resolved.

Hi tried the solution given by @Edward_Robinson in

2.40.5 error while updating resource tables

i.e changing the timezone of server to UTC and it worked for me.

However, I am still facing issue while downloading metadata for Visualisations and no logs show up in dhis.log. Maybe I’ll start a new conversation for that.