When model is created on the basis of a medium to large model, the server times out

[samatstariongroup]

Prerequisites

• [x] I have written a descriptive issue title
• [x] I have verified that I am running the latest version of the CDP4 Web Services
• [x] I have searched open and closed issues to ensure it has not already been reported

Description

When model is created on the basis of a medium to large model, the server times out. As a starting solution the creation of a model needs to be made more verbose in the logging sequence and the CommandTimeout setting needs to be configurable in the config,json file

Steps to Reproduce

NA

System Configuration

• CDP4 Web Services version:
  • [x] CDP4WebServices.API: 2.1.0
  • [x] CDP4Common: 1.6.0
  • [x] PostrgreSQL: 9.6.5

[samatstariongroup]

The timeout setting is already available, more logging to be added

[samatstariongroup]

here we may require a different way to copy data between "models" which are essentially PostgreSQL schema. Unfortunately there is no copy schema command in postgres. If we want that as a solution we may need to develop a generic function or we need to code-generate. Lets not forget that when a new model is created, all unique identifiers need to be updated as well. (perhaps here we can think of a dump to disk and do a find and replace on unique identifiers in the dumped file, which we can then reload)?

[IevhenIkonnykov]

There is something happens on the DB side. My experiments with the provided "big" model gave me the following information:

• error is purely from the DB side: 2019-11-27 11:50:48 EET ERROR: canceling statement due to statement timeout 2019-11-27 11:50:48 EET CONTEXT: SQL statement "SELECT EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name='transaction_info' and column_name='transaction_time')" PL/pgSQL function "SiteDirectory".get_transaction_time() line 5 at IF 2019-11-27 11:50:48 EET STATEMENT: UPDATE "Iteration_30e1755e_8a0b_4a1d_96b6_a9a743f13675"."ParameterValueSet" SET "ValidFrom" = "SiteDirectory".get_transaction_time(), "ValidTo" = 'infinity'

Interesting moment, all errors point to "ParameterValueSet", but once it was "ParameterValueSetBase". Coincedence?

• by default statement timeout is set to 0 which means "turned off" and this is puzzling, because it looks like it is ignored. Experiments with changing this value did not reveal anything. On my machine I usually get this error after ~51 sec

• the error is not consistent, it does not appear every time on the same model creation. This morning I had a fail every other time, after a couple of hours it changed this behavior. Nevertheless when it first fail, repeatable attempt to create a model succeeds anyway after 3-4 attempts. On my machine it usually succeeds in ~21 sec

My changes to DB's logging do not reveal any lock problems which puzzles even more.

[samatstariongroup]

My impression is that the triggers are causing a lot of work on the database, which is strictly not required.

I am in the process of cleaning up the generated SQL schema so they are easier to work with, I hope to have that ready by the beginning of next week. Once they are ready, we can think of the following approach:

• create a new schema for an EngineeringModel/Iteration (Without any triggers)
• dump the data to a file
• read all the GUID's from the instances in the source model EngineeringModel / Iteration and do a find-and-replace to create new unique GUID's in the copied model
• load the dumped data (with new GUIDs) into the created schema (perhaps using the COPY command)
• create the triggers

On the resulting model i think we need to have the following:

• only have data for iterations, not any revisions in-between
• in my opinion the audit_log should correspond to the iteration data and should have as actor the unique identifier of the user that is creating the new model (not of the persons that did the actions in the source model)

[antoineatstariongroup]

Recent notes:
We spot 2 sub-operation that takes more longer than it should

• Schema creation : It could be simplified since Views and Functions are not used anymore
• Revisions creation : Bulk Insert can be implemented to make it faster (https://github.com/RHEAGROUP/COMET-WebServices-Community-Edition/pull/322)

[antoineatstariongroup]

Closed with #323