xTuple.com xTupleU Blog & News Customer Support

Gripe about upgrade adding new trigger to all files

Since the title of this forum says it is a place for comments about current functionality I’m going to complain about one of my recent issues. When upgrading an xtuple installation from 4.9.5 to 4.11.1 I found that the upgrade process had added a “notifydatachanged” trigger to the database for every file in the DB. Not only in the “public” schema but also in the custom schemas I had added. Even to the standard “pkg…” files that are created when a schema is added via the xtuple updater. I guess my question is “why”. What are the intentions of that trigger to do? Why burden our DB engine with hundreds of triggers that do nothing other than format a string? Was the addition intentional? Just wondering.

Jim

Jim,

That’s a good question. The intent here is to invalidate caches.

One of the changes in 4.11 is that most of the comboboxes in the application now cache their data. If you watch the queries executed by version 4.10 and earlier, the exact same queries get run every time you open a window. In 4.11.0 and later, many of these queries are run once, possibly for the entire time the application is running. This leads to a measurable performance improvement in the application, especially over slow networks.

The triggers you mention allow any client that’s listening to invalidate specific data. The string that is generated tells the listener exactly which table was changed. Thus if anyone anywhere edits the terms table, every client that has opened a window with a Terms combobox (Customer, Sales Order, Quote windows, for example), invalidates its cache of terms. It does not immediately requery. Instead, the client requeries the next time it opens a window with a Terms combobox.

The combobox queries are the first in a larger set of objects that will cache their own data. Other objects on the list are MetaSQL statements, the various “cluster” objects, and application scripts. That’s why we created triggers to all of the tables. We don’t know a priori which tables we’re going to need to monitor. The end result is that overall application performance improved.

I hope this addresses your concerns.

Gil

Gil,

We have discovered something related to the use of notifydatachanged in version 5.0. Every 45 seconds or so the xTuple client will change the cursor to the blue circle (wait) cursor. This cursor last about 2 seconds or so.

The wait cursor was becoming a user experience issue on screens where we have added tabs that are posting changelog comments. So we have disabled the notifydatachanged trigger on the comments table and the wait cursor is no longer appearing as it did.

Are there any ramifications to leaving this trigger disabled on this table?

Rebecca

Rebecca,
There is a 1 minute ticker that triggers a refresh of the Comment Console on the user Desktop. If you have a large selection of comments to display, that creates quite a bit of traffic which could be what you are seeing.

There is a new setting in the User Preferences “Manual Refresh Comment Console”. That turns off the automatic refresh, and when you go to the Desktop Comment Console you can manually update the list of comments.

I found this makes a large difference to performance and may mean you do not need to disable that trigger.

Thanks, Dave. I was trying to remember where that setting was. I have not seen the problem the developers reported and that was because I had that option checked. I updated their preferences and will make this a standard setting for our users.

Rebecca,

Disabling the trigger on the comment table should be fine. I don’t see any listeners on that table.

Gil

1 Like