2.40.5 error while updating resource tables

[Edward_Robinson]

While updating resource tables in 2.40.5, I receive this error:

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

Any assistance would be appreciated.

[Gassim (AL-Gassim Sharaf Addin)]

Hi @Edward_Robinson

It does seem like you are not the first person to post this type of error. It sounds like others have faced the same error with different periods. @Collin.Dosunmu suggested the following:

@Collin.Dosunmu

I removed all the SQL views and that solved the problem. I can now view my data in versions 2.29 and 2.30

But you can also see the other suggestions by @Stephan_Mestach and @rcpoudel :

@Stephan_Mestach

but you can verify with a query like this

select * from period where startdate = '1993-02-06' or enddate = '1993-02-06'

@rcpoudel

Drop materialized views and clear analytics data.

In an older post but very similar, @dhuser suggested the following:

@dhuser

If no calendar setting issues, or period database-level issues can be identified, check the timezone setting of the server. The exact same issue of resource tables not being able to populate helped when setting the timezone back to UTC.

In another topic, @linxd mentioned that this issue happened in Windows 2008 server but not in Ubuntu. Would you share more details about the instance environment? Additionally, please check the calendar settings (this one was asked by @Lars several times in older posts).

I hope these help. Thanks!

[Edward_Robinson]

Thanks so much for the pointers, setting timezone to UTC and restarting Tomcat / Postgres worked for me.

[sami.oracle10g (Sami Yassin)]

try to do it again. It will work well. I think this is caused because there are some events scheduled simultaneously when you update the resources tables. again I recommend trying it in another time