New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Updating PMM_Week availability trend fails #3169

Closed
metas-ts opened this Issue Dec 8, 2017 · 4 comments

Comments

Projects
None yet
3 participants
@metas-ts
Member

metas-ts commented Dec 8, 2017

Is this a bug or feature request?

Bug

What is the current behavior?

 I_C_Queue_WorkPackage: X_C_Queue_WorkPackage[C_Queue_WorkPackage_ID=17837769, trxName=null]
 IQueueProcessor: ThreadPoolQueueProcessor{name=Procurement, executor=BlockingThreadPoolExecutor [getPoolSize()=1]}
 trxName: <<ThreadInherited>>
        SQL: INSERT INTO PMM_Week (AD_Client_ID,AD_Org_ID,C_BPartner_ID,Created,CreatedBy,IsActive,M_AttributeSetInstance_ID,M_Product_ID,PMM_Trend,PMM_Week_ID,Processed,Updated,UpdatedBy,WeekDate) VALUES (1000000,0,2000278,TO_TIMESTAMP('2017-12-08 05:51:39','YYYY-MM-DD HH24:MI:SS'),0,'Y',7119955,2000824,'U',nextval('pmm_week_seq'),'N',TO_TIMESTAMP('2017-12-08 05:51:39','YYYY-MM-DD HH24:MI:SS'),0,TO_TIMESTAMP('2016-11-21','YYYY-MM-DD')) RETURNING PMM_Week_ID
2017-12-08 05:52:00.706  WARN 30907 --- [de.metas.async.processor.impl.ThreadPoolQueueProcessor-Procurement-pool-27-thread-29] d.m.async.model.X_C_Queue_WorkPackage    : ErrorMsg - Value too long - truncated to length=255: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pmm_week_uq"
  Detail: Key (c_bpartner_id, m_product_id, weekdate)=(2000278, 2000824, 2016-11-21 00:00:00) already exists.
Additional parameters:
 I_C_Queue_WorkPackage: X_C_Queue_WorkPackage[C_Queue_WorkPackage_ID=17837770, trxName=null]
 IQueueProcessor: ThreadPoolQueueProcessor{name=Procurement, executor=BlockingThreadPoolExecutor [getPoolSize()=1]}
 trxName: <<ThreadInherited>>
  • the event is received, but can't be processed
  • further events are not processed either

Which are the steps to reproduce?

I believe the error occurs if you create and then change a week-trend in the procurement webui

@metas-ts metas-ts added the type:bug label Dec 8, 2017

@metas-ts metas-ts self-assigned this Dec 8, 2017

@metas-mk metas-mk assigned metas-rc and unassigned metas-ts Dec 8, 2017

@metas-ts

This comment has been minimized.

Show comment
Hide comment
@metas-ts

metas-ts Dec 8, 2017

Member

@metas-rc here is also the stack trace:

org.adempiere.exceptions.DBUniqueConstraintException: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pmm_week_uq"
  Detail: Key (c_bpartner_id, m_product_id, weekdate)=(2000278, 2000824, 2016-11-21 00:00:00) already exists.
Additional parameters:
 I_C_Queue_WorkPackage: X_C_Queue_WorkPackage[C_Queue_WorkPackage_ID=17837769, trxName=null]
 IQueueProcessor: ThreadPoolQueueProcessor{name=Procurement, executor=BlockingThreadPoolExecutor [getPoolSize()=1]}
 trxName: <<ThreadInherited>>
	SQL: INSERT INTO PMM_Week (AD_Client_ID,AD_Org_ID,C_BPartner_ID,Created,CreatedBy,IsActive,M_AttributeSetInstance_ID,M_Product_ID,PMM_Trend,PMM_Week_ID,Processed,Updated,UpdatedBy,WeekDate) VALUES (1000000,0,2000278,TO_TIMESTAMP('2017-12-08 05:51:39','YYYY-MM-DD HH24:MI:SS'),0,'Y',7119955,2000824,'U',nextval('pmm_week_seq'),'N',TO_TIMESTAMP('2017-12-08 05:51:39','YYYY-MM-DD HH24:MI:SS'),0,TO_TIMESTAMP('2016-11-21','YYYY-MM-DD')) RETURNING PMM_Week_ID
	at org.compiere.util.DB.executeUpdate(DB.java:952)
	at org.compiere.model.PO.saveNew(PO.java:3626)
	at org.compiere.model.PO.save0(PO.java:2809)
	at org.compiere.model.PO.access$100(PO.java:138)
	at org.compiere.model.PO$1.run(PO.java:2711)
	at org.adempiere.ad.trx.api.impl.TrxCallableWrappers$3.call(TrxCallableWrappers.java:145)
	at org.adempiere.ad.trx.api.impl.TrxCallableWrappers$3.call(TrxCallableWrappers.java:135)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call0(AbstractTrxManager.java:771)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call(AbstractTrxManager.java:683)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call(AbstractTrxManager.java:584)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call(AbstractTrxManager.java:515)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.run(AbstractTrxManager.java:500)
	at org.compiere.model.PO.saveEx(PO.java:2704)
	at org.adempiere.model.POWrapper.save(POWrapper.java:781)
	at org.adempiere.model.InterfaceWrapperHelper.save(InterfaceWrapperHelper.java:541)
	at de.metas.procurement.base.event.impl.PMMWeekReportEventTrxItemProcessor.process(PMMWeekReportEventTrxItemProcessor.java:115)
	at de.metas.procurement.base.event.impl.PMMWeekReportEventTrxItemProcessor.process(PMMWeekReportEventTrxItemProcessor.java:47)
	at org.adempiere.ad.trx.processor.api.impl.TrxItemProcessor2TrxItemChunkProcessorWrapper.process(TrxItemProcessor2TrxItemChunkProcessorWrapper.java:75)
	at org.adempiere.ad.trx.processor.api.impl.TrxItemChunkProcessorExecutor.processItem(TrxItemChunkProcessorExecutor.java:260)
	at org.adempiere.ad.trx.processor.api.impl.TrxItemChunkProcessorExecutor.execute(TrxItemChunkProcessorExecutor.java:157)
	at org.adempiere.ad.trx.processor.api.impl.TrxItemExecutorBuilder.process(TrxItemExecutorBuilder.java:75)
	at de.metas.procurement.base.event.impl.PMMWeekReportEventsProcessor.processAll(PMMWeekReportEventsProcessor.java:60)
	at de.metas.procurement.base.event.async.PMM_WeekReport_Event_Processor.processWorkPackage(PMM_WeekReport_Event_Processor.java:46)
	at de.metas.async.processor.impl.WorkpackageProcessorTask.invokeProcessorAndHandleException(WorkpackageProcessorTask.java:293)
	at de.metas.async.processor.impl.WorkpackageProcessorTask.processWorkpackage(WorkpackageProcessorTask.java:285)
	at de.metas.async.processor.impl.WorkpackageProcessorTask.access$000(WorkpackageProcessorTask.java:85)
	at de.metas.async.processor.impl.WorkpackageProcessorTask$1.run(WorkpackageProcessorTask.java:168)
	at org.adempiere.ad.trx.api.impl.TrxCallableWrappers$2.call(TrxCallableWrappers.java:103)
	at org.adempiere.ad.trx.api.impl.TrxCallableWrappers$2.call(TrxCallableWrappers.java:93)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call0(AbstractTrxManager.java:771)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call(AbstractTrxManager.java:683)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.run(AbstractTrxManager.java:591)
	at de.metas.async.processor.impl.WorkpackageProcessorTask.run(WorkpackageProcessorTask.java:158)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pmm_week_uq"
  Detail: Key (c_bpartner_id, m_product_id, weekdate)=(2000278, 2000824, 2016-11-21 00:00:00) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
	at org.adempiere.sql.impl.CPreparedStatementProxy.executeQuery(CPreparedStatementProxy.java:122)
	at org.compiere.util.DB.executeUpdate(DB.java:911)
	... 37 more
Member

metas-ts commented Dec 8, 2017

@metas-rc here is also the stack trace:

org.adempiere.exceptions.DBUniqueConstraintException: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pmm_week_uq"
  Detail: Key (c_bpartner_id, m_product_id, weekdate)=(2000278, 2000824, 2016-11-21 00:00:00) already exists.
Additional parameters:
 I_C_Queue_WorkPackage: X_C_Queue_WorkPackage[C_Queue_WorkPackage_ID=17837769, trxName=null]
 IQueueProcessor: ThreadPoolQueueProcessor{name=Procurement, executor=BlockingThreadPoolExecutor [getPoolSize()=1]}
 trxName: <<ThreadInherited>>
	SQL: INSERT INTO PMM_Week (AD_Client_ID,AD_Org_ID,C_BPartner_ID,Created,CreatedBy,IsActive,M_AttributeSetInstance_ID,M_Product_ID,PMM_Trend,PMM_Week_ID,Processed,Updated,UpdatedBy,WeekDate) VALUES (1000000,0,2000278,TO_TIMESTAMP('2017-12-08 05:51:39','YYYY-MM-DD HH24:MI:SS'),0,'Y',7119955,2000824,'U',nextval('pmm_week_seq'),'N',TO_TIMESTAMP('2017-12-08 05:51:39','YYYY-MM-DD HH24:MI:SS'),0,TO_TIMESTAMP('2016-11-21','YYYY-MM-DD')) RETURNING PMM_Week_ID
	at org.compiere.util.DB.executeUpdate(DB.java:952)
	at org.compiere.model.PO.saveNew(PO.java:3626)
	at org.compiere.model.PO.save0(PO.java:2809)
	at org.compiere.model.PO.access$100(PO.java:138)
	at org.compiere.model.PO$1.run(PO.java:2711)
	at org.adempiere.ad.trx.api.impl.TrxCallableWrappers$3.call(TrxCallableWrappers.java:145)
	at org.adempiere.ad.trx.api.impl.TrxCallableWrappers$3.call(TrxCallableWrappers.java:135)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call0(AbstractTrxManager.java:771)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call(AbstractTrxManager.java:683)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call(AbstractTrxManager.java:584)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call(AbstractTrxManager.java:515)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.run(AbstractTrxManager.java:500)
	at org.compiere.model.PO.saveEx(PO.java:2704)
	at org.adempiere.model.POWrapper.save(POWrapper.java:781)
	at org.adempiere.model.InterfaceWrapperHelper.save(InterfaceWrapperHelper.java:541)
	at de.metas.procurement.base.event.impl.PMMWeekReportEventTrxItemProcessor.process(PMMWeekReportEventTrxItemProcessor.java:115)
	at de.metas.procurement.base.event.impl.PMMWeekReportEventTrxItemProcessor.process(PMMWeekReportEventTrxItemProcessor.java:47)
	at org.adempiere.ad.trx.processor.api.impl.TrxItemProcessor2TrxItemChunkProcessorWrapper.process(TrxItemProcessor2TrxItemChunkProcessorWrapper.java:75)
	at org.adempiere.ad.trx.processor.api.impl.TrxItemChunkProcessorExecutor.processItem(TrxItemChunkProcessorExecutor.java:260)
	at org.adempiere.ad.trx.processor.api.impl.TrxItemChunkProcessorExecutor.execute(TrxItemChunkProcessorExecutor.java:157)
	at org.adempiere.ad.trx.processor.api.impl.TrxItemExecutorBuilder.process(TrxItemExecutorBuilder.java:75)
	at de.metas.procurement.base.event.impl.PMMWeekReportEventsProcessor.processAll(PMMWeekReportEventsProcessor.java:60)
	at de.metas.procurement.base.event.async.PMM_WeekReport_Event_Processor.processWorkPackage(PMM_WeekReport_Event_Processor.java:46)
	at de.metas.async.processor.impl.WorkpackageProcessorTask.invokeProcessorAndHandleException(WorkpackageProcessorTask.java:293)
	at de.metas.async.processor.impl.WorkpackageProcessorTask.processWorkpackage(WorkpackageProcessorTask.java:285)
	at de.metas.async.processor.impl.WorkpackageProcessorTask.access$000(WorkpackageProcessorTask.java:85)
	at de.metas.async.processor.impl.WorkpackageProcessorTask$1.run(WorkpackageProcessorTask.java:168)
	at org.adempiere.ad.trx.api.impl.TrxCallableWrappers$2.call(TrxCallableWrappers.java:103)
	at org.adempiere.ad.trx.api.impl.TrxCallableWrappers$2.call(TrxCallableWrappers.java:93)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call0(AbstractTrxManager.java:771)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.call(AbstractTrxManager.java:683)
	at org.adempiere.ad.trx.api.impl.AbstractTrxManager.run(AbstractTrxManager.java:591)
	at de.metas.async.processor.impl.WorkpackageProcessorTask.run(WorkpackageProcessorTask.java:158)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pmm_week_uq"
  Detail: Key (c_bpartner_id, m_product_id, weekdate)=(2000278, 2000824, 2016-11-21 00:00:00) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:1418)
	at org.adempiere.sql.impl.CPreparedStatementProxy.executeQuery(CPreparedStatementProxy.java:122)
	at org.compiere.util.DB.executeUpdate(DB.java:911)
	... 37 more
@metas-rc

This comment has been minimized.

Show comment
Hide comment
@metas-rc

metas-rc Dec 8, 2017

Member

I did some research and found out the PMM_Week has a unique index on c_bpartner_id, m_product_id and weekdate, but the retrieval logic in

de.metas.procurement.base.impl.PMMWeekDAO.retrieveFor(PMMBalanceSegment, Date)

is also checking is the M_AttributeSetInstance_IDs are equal.
So, if a PMM_Week entry is created for partner, product , date and certain ASI and will be called for the same ASI, it will be retrieved. But if it is called for the same partner, product , date and for another ASI, a new PMM_Week will be created => the index is not fulfilled and there will be the database error above. ( see

de.metas.procurement.base.event.impl.PMMWeekReportEventTrxItemProcessor.process(I_PMM_WeekReport_Event) 

We checked in the PMM weekly related views and they also check for the ASI.

Therefore, my solution, at least for the time being, is to change the unique index by inserting the M_AttributeSetInstance_ID into it, as well.

Member

metas-rc commented Dec 8, 2017

I did some research and found out the PMM_Week has a unique index on c_bpartner_id, m_product_id and weekdate, but the retrieval logic in

de.metas.procurement.base.impl.PMMWeekDAO.retrieveFor(PMMBalanceSegment, Date)

is also checking is the M_AttributeSetInstance_IDs are equal.
So, if a PMM_Week entry is created for partner, product , date and certain ASI and will be called for the same ASI, it will be retrieved. But if it is called for the same partner, product , date and for another ASI, a new PMM_Week will be created => the index is not fulfilled and there will be the database error above. ( see

de.metas.procurement.base.event.impl.PMMWeekReportEventTrxItemProcessor.process(I_PMM_WeekReport_Event) 

We checked in the PMM weekly related views and they also check for the ASI.

Therefore, my solution, at least for the time being, is to change the unique index by inserting the M_AttributeSetInstance_ID into it, as well.

metas-rc added a commit that referenced this issue Dec 8, 2017

@metas-rc

This comment has been minimized.

Show comment
Hide comment
@metas-rc

metas-rc Dec 8, 2017

Member

Note: I couldn't dev test this because I don't have access to procurement webui.

Member

metas-rc commented Dec 8, 2017

Note: I couldn't dev test this because I don't have access to procurement webui.

metas-ts added a commit that referenced this issue Dec 11, 2017

Merge pull request #3175 from metasfresh/gh3169
#3169 PMM_Week unique constraint including ASI

metas-rc added a commit that referenced this issue Dec 11, 2017

@metas-rc metas-rc self-assigned this Dec 11, 2017

metas-ts added a commit that referenced this issue Dec 18, 2017

Update ReleaseNotes.md
[#706](metasfresh/metasfresh-webui-api#706)
Port current MRP Product Info Window to the webui
[#755](metasfresh/metasfresh-webui-api#755)
Processes: support for parentViewSelectedIds and childViewSelectedIds
[#1427](metasfresh/metasfresh-webui-frontend#1427)
d3 errors on dashboard
[#2646](#2646) MRP
Product Info Just in Time
[#751](metasfresh/metasfresh-webui-api#751)
Picking Tray Clearing: packing HUs: Add to Transportation Order action
[#1437](metasfresh/metasfresh-webui-frontend#1437)
Language bug: The language in Settings is not respected
[#746](metasfresh/metasfresh-webui-api#746)
Picking Tray Clearing: Scan picking slot barcode filter
[#1455](metasfresh/metasfresh-webui-frontend#1455)
error when updating an attribute multiple times
[#747](metasfresh/metasfresh-webui-api#747)
Picking Tray Clearing: filter picking slots by Partner
[#1433](metasfresh/metasfresh-webui-frontend#1433)
Support Lookup view attributes
[#3211](#3211) Picking
Tray Clearing: right side view shall display only the HUs for current
BP/Location
[#1444](metasfresh/metasfresh-webui-frontend#1444)
Attributes are empty in material receipt candidate
[#3202](#3202) Error in
Paymentjournal Process/ Report in Payselection
[#3193](#3193) Improve
Window for manual OnHand Qty in WebUI
[#1448](metasfresh/metasfresh-webui-frontend#1448)
jenkins: fix current lint issues
[#1435](metasfresh/metasfresh-webui-frontend#1435)
Problem regarding multiple filters
[#1436](metasfresh/metasfresh-webui-frontend#1436)
Remove legacy docs
[#3169](#3169) Updating
PMM_Week availability trend fails
[#1206](metasfresh/metasfresh-webui-frontend#1206)
Use automatic code style enforcement
[#717](metasfresh/metasfresh-webui-api#717)
picking terminal: open HUs to pick shall display Best Before date and
Locator

me-45
@metas-dh

This comment has been minimized.

Show comment
Hide comment
@metas-dh

metas-dh Jan 12, 2018

Member

Results of IT1
tested in customer db

  • created trend in procurement webui for acertain bpartner, product, date and certain ASI (only product, no attribute): trend was correct in Procurement Availability Trend & Procurement Planning: OK
  • changed the trend in procurement webui for the same bpartner, product, date and ASI: trend was updated correctly in Procurement Availability Trend & Procurement Planning: OK
  • created another trend in procurement webui for the same bpartner, product, date but different ASI (attribute: Bio):
    • trend for this product was correct in Procurement Availability Trend & Procurement Planning: OK
    • trend for 1st product remained the same: OK
Member

metas-dh commented Jan 12, 2018

Results of IT1
tested in customer db

  • created trend in procurement webui for acertain bpartner, product, date and certain ASI (only product, no attribute): trend was correct in Procurement Availability Trend & Procurement Planning: OK
  • changed the trend in procurement webui for the same bpartner, product, date and ASI: trend was updated correctly in Procurement Availability Trend & Procurement Planning: OK
  • created another trend in procurement webui for the same bpartner, product, date but different ASI (attribute: Bio):
    • trend for this product was correct in Procurement Availability Trend & Procurement Planning: OK
    • trend for 1st product remained the same: OK

@metas-dh metas-dh closed this Jan 12, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment