Can't export large user data file with PostgreSQL, but can with Valentina

notiusweb

Win 7 64. Receiving error in log file upon trying to export user data through PostgreSQL on Daz 4.8.0.55

WARNING: dzpostgresqlobjectdatabase.cpp(39): DB Select error: ERROR: canceling statement due to statement timeout

DB Select error: ERROR: canceling statement due to statement timeout

The export itself appears to start in Daz, it says Gathering User Data, like normal, but then never proceeds with the counter starting at 1%. It just shows the hourglass thing like it is loading, but then just stops after a couple minutes.

Last working Valentina data file consists of 4 user data files, in addition to metadata.
UserData_1.dsx
UserData_2.dsx
UserData_3.dsx
UserData_4.dsx

I can reset database and then re-import the data just fine. Just cannot then subsequently export. I can though, after resetting database, create a couple of categories and then export fine through PostgreSQL, so PostgreSQL seems to function normal with a smaller database capacity.

Also, I can uninstall PostgreSQL and restart back with Valentina, and then export data fine, as a workaround, and then reinstall to go back to the more secure PostgreSQL to access and continue to add more categories, etc. In other words, use PostgreSQL while adding categories and using the software, and then every once in a while uninstall it with the install manager, open up Daz and check log to see it is running Valentina, then export through Valetina, and then once exported, go back to PostgreSQL by reinstalling. (To be honest, I hadn't really felt the need to backup the data since using PostgreSQL as it works very well and I encountered no issues, I am just hesitant to not have backups with all the Valentina database collapses I had experienced in the past.)

Wondering if this error has anything to due with a larger user database file. I did try reinstalling, resetting databases, using the conversion tool, etc.

THANKS! Any suggestions are appreciated!

**Special thanks to Kitakoredaz for pointing me to the log file**

kitakoredaz

Hi sorry, I have no idea to correct it. I do not know if it is limits bug
(I serched postgre elloer message, then there are some topic aobut postgre timeout setting
which make same erroer message, of config, or data base query code)
https://github.com/CartoDB/cartodb/issues/1246
http://bajis-postgres.blogspot.jp/2014/01/basic-errors-of-postgresql-part1.html
(the last topic Error 10)

I do not know, if it was simply postgre.conf setting problem or not.
I checked conf file ,but there seems no setting about timeout,
then I can not recommend to tweak setting about postgresql, which setted by daz.

And, I think if Valentina database was corrupted, exported user data can not work well.
I may open them by text editor,, then if it was corrupted,, you can not correct them I think,,
(then before we needed to save valentina database and it was recommended,,,to recover safety,,)

anyway, I think you may better to send bug report with the erroer message.
https://helpdaz.zendesk.com/forums
(the right side of page, ,Contact US> subimit bug report)

then wait other user who have known well about database,
I thnk they can find this topic,,and hope give us good advice,,
(it is not only your problem if it was limit problem of postgre setting and user-data)

And,, I sometimes add link of forum topic , with bug ticket,,
to tell clear,, what I tried, and other user advice,,
I could easy understand what you have done, and tried, then it may help daz support too.

without it, I often needed add same report to support udnerstand what is problem ^^;)

sriesch

Did you ever find a resolution to this issue?

Is the timeout setting mentioned in topic error 10 at kitakoredaz's link http://bajis-postgres.blogspot.com/2014/01/basic-errors-of-postgresql-part1.html somethign that we can control in any way, whether directly by changing some parameter somewhere or indirectly if it is actually speed related?

sriesch

notiusweb said:

Also, I can uninstall PostgreSQL and restart back with Valentina,

How are you doing this?  I thought Valentina was removed prior to version 4.8.  Is it still somehow built in, or was there a separate installer?

sriesch

If this were related to the size of the database in some way and I've hit some maximum limit, short of uninstalling products is there any massive hidden quantity of unnecessary junk (temporary files, hidden folders like those ones that periodically appear in unassigned again long after you deleted them, stuff left over from database conversions from old versions of DS, etc) that could be removed?  Or is there something that could be particularly problematic, like one category with an extremely large number of folders in it that could be redistributed into smaller chunks?

Richard Haseltine

sriesch said:

notiusweb said:

Also, I can uninstall PostgreSQL and restart back with Valentina,

How are you doing this?  I thought Valentina was removed prior to version 4.8.  Is it still somehow built in, or was there a separate installer?

Valentina support has certainly been dropped, not sure if it was 4.8 or 4.9 - I thought the latter, with the introduction of Connect and other changes to the way the database works.

sriesch

Completely deleting the database (the folder C:\Users\Sean Riesch\AppData\Roaming\DAZ 3D\cms ) and reloading everything from scratch doesn't seem to affect the issue.

 

sriesch

Does anybody have a working backup with more than 5 userdata files?  If so, that would suggest that the problem is not the volume of content.

sriesch

It appears the problem is not soley caused by the size of the database.  If I try importing, making no changes of any kind and exporting, I still get the issue, but it worked once with that exact same data set since I have that previous export.

sriesch

I looked at the C:\Users\<myUserName>\AppData\Roaming\DAZ 3D\cms\ContentCluster\postgresql.conf file (rename it to .txt to view it, and rename it back before using it), and found several settings for timeouts, including the one mentioned above that was set to 0.  As the error appears to happen about 5 minutes in, just for kicks I tried blindly changing the one parameter  checkpoint_timeout  that was in fact set to 5 minutes to be 59 minutes hoping perhaps that was it, but that had no effect so that wasn't it either.

sriesch

Ok, so I'm going to try the super low-budget workaround of just copying and restoring C:\Users\<myUserName>\AppData\Roaming\DAZ 3D\cms\ContentCluster .  Are there any catches I should be aware of, anything that might not be stored in this folder after DS & postgresql are completely shutdown or anything else unexpected?  I'll be able to verify the test worked after a complete wipe and reload of my OS of course, but that's a lot of work to repeat and it would be nice to catch everything the first time.   I did the quick test of actually making a copy, resetting the database and verifying it was gone, then copying it back (restarts in between all) and it seems to be working fine at first glance.