1 2 Previous Next 16 Replies Latest reply on Apr 21, 2015 6:23 AM by gethappy@happypc.com

    Cotcha for PSoS (commit step)

    psijmons

      Perform Script On Server (PSoS) is a different beast to debug.

      As it creates an entirely new session, context and global variables are lost when such a PSoS script is called.

       

      I was well aware of this and was still caught by surprise for something I had overlooked completely;

      This was in a series of nested scripts where at some point one (lengthy) subscript is set for PSoS. But when the scripts preceding the PSoS script do not have an explicit commit step, that data is not yet available when PSoS kicks in.

       

      It took me a while before I figured this out, so I think it may help others who fall into the same trap.

      Also used some temporary steps that you can insert all over the place to get a sort of "debugger" for PSoS.

       

      Screen Shot 2015-03-19 at 10.09.28.jpg

        • 1. Re: Cotcha for PSoS (commit step)
          psijmons

          A fellow developer Hans Erik, pointed me to a similar effect but much more tricky:

           

          Even when PSoS is set to wait for completion, you cannot be sure that the correct data set is passed on to a client-side script that is set as the next step.

           

          For those fluent in Dutch, this is a detailed report:  http://www.clarify.net/viewtopic.php?f=48&t=8894

           

          The situation was as follows: PSoS is set to delete a substantial nr of records (> 5000), then the remainder is exported to CSV by a client side script. However, the client-side is not completely aware the deletion process is finished and starts exporting a large nr of empty records.

           

          Additional commit steps, refresh windows etc did not help, the only way to get this right was to insert a 5 second pause step before the client side took over (although this number of seconds may vary, depending on record nrs, WAN/LAN effects etc, so it may even be guesswork how long the pause should be).

           

          This was reproduced by another developer on Clarify and is probably reported as bug to FMI, but you are now warned for this behaviour.

           

          Look at the curious status bar below at the point where the client script kicks in.

           

          statusbalk.png

          • 2. Re: Cotcha for PSoS (commit step)
            Mike_Mitchell

            These are both good points; thanks for sharing.

             

            The latter one looks like a cache refresh issue. It might be solved via a Flush Cache to Disk script step (since I don't read Dutch, I don't know if that was tried or not). Just a thought.

            • 3. Re: Cotcha for PSoS (commit step)
              ch0c0halic

              Server side deletions are a special case of the PSoS operation. When the Script runs it is telling FMS to delete the records. The script can complete but FMS hasn't yet updated them all. Its still a work in progress. The same thing can occur with the Replace command. I recommend that in both cases you run a looping script and not exit the script until confirming the operation is complete. In case of a delete maybe perform the same Find done to get the records to delete and confirm no records match the request.

               

              The Client needs an updated record ID list. IMHO it is essential to the integrity of the Data that you perform that same Find for the appropriate records in the client after running the delete.

              If you use the Replace command you could do a find in the PSoS for the replaced value (maybe include the modifier name) and return a found count. Then the client should do the same Find and compare record counts. If the client gets a different count than the script then wait and do over till they match. If there is a possibility of other clients changing the Found count then you should allow for the possible difference.

              • 4. Re: Cotcha for PSoS (commit step)
                psijmons

                Yes Mike, that was tested, didn't solve the issue, but chocohalic gives a good explanation.

                Adding a loop is a good suggestion but still, if you set PSoS to wait for completion, one would expect it is really completed and the client can take it from there.

                • 5. Re: Cotcha for PSoS (commit step)
                  electon

                  psijmons Thanks for the heads up!

                  Mike, I can read quite a bit of Dutch. Flush Cache to Disk wasn't mentioned in the linked posts.

                  Al lot they refer to is Refresh Window, not clear if the Flush cached join results is being used here but i looks like it is, since a lot of commenters expect the action to clear the cache.

                  It seems the problem occurs with larger found sets and somehow the server isn't updating clients fast enough.

                  • 6. Re: Cotcha for PSoS (commit step)
                    Mike_Mitchell

                    Yeah, those are two separate operations. (As we recently discussed on another thread.) Don't know; might be worth a try.

                     

                    Ch0c0halic has good observations, so his recommendations are probably good to try, too.

                     

                    It's worth pointing out that this isn't a whole lot different from another client performing a large-scale delete while the current client is in the middle of an operation. Something to ponder.  

                    • 7. Re: Cotcha for PSoS (commit step)
                      wimdecorte

                      psijmons wrote:

                       

                      when the scripts preceding the PSoS script do not have an explicit commit step, that data is not yet available when PSoS kicks in.

                       

                      Yes, and that can be very useful.  My 2014 devcon session made good use of that for the audit log approach.  As long as you don't commit, the client has access to the updated / new data but the server does not know about it yet.  So before you commit, you can run a PSoS to retrieve the old data and compare it to the new...

                      • 8. Re: Cotcha for PSoS (commit step)
                        hehazelhorst

                        Thank you for this explanation.

                        During my testing, the 'ghost records' I retrieved turned out to be the records that PSoS had just previously deleted. You suggest this 'lagging' is caused by the server cache not being updated as opposed to the cache on the client's computer?

                         

                        I also performed an ExecuteSQL (part of the client side script) to check how many records were left over after the deletion process. This returns a ? if you don't pause for a few seconds. I sincerely hope FMI will address these issues, perhaps with an extra option to the PSoS command, or with a server-side equivalent of 'flush cache to disk'. In the meantime, some insight in the caching process would be most welcome.

                         

                        Hans Erik Hazelhorst

                        • 9. Re: Cotcha for PSoS (commit step)
                          wimdecorte

                          user13378 wrote:

                           

                          I also performed an ExecuteSQL (part of the client side script) to check how many records were left over after the deletion process. This returns a ? if you don't pause for a few seconds

                           

                          An "?" generally means a syntax error which is not what I would expect if the SQL query does not find data... so that may be a red herring.  Can you post the SQL query that you are sending?

                          • 10. Re: Cotcha for PSoS (commit step)
                            electon

                            My test show exactly same behaviour.

                            Flush cache to disk doesn't seem to help.

                            An immediate export after deleting 5000 records exports the whole 5000 records with only the first 25 containing values from beginning of the set.

                            Wasn't it the case that filemaker pushes / manages record changes in batches of 100 or some other factor for larger sets of data? Within one network packet / http request.

                             

                            Definitely something to look out for.

                            • 11. Re: Cotcha for PSoS (commit step)
                              psijmons

                              Yes Wim, I can understand it can be useful in several scenario's, hence the "cotcha..."

                              It took me a while to track the cause, not a bug, just a design error.

                               

                              But the issue Hans Erik reported is trickier.

                              • 12. Re: Cotcha for PSoS (commit step)
                                hehazelhorst

                                TheSQL is very simple:

                                 

                                "SELECT unit, COUNT(id)

                                FROM a_importtmp

                                GROUP BY unit"

                                 

                                The result is placed in a text variable which is then stored in a logrecord. So I was able to retrieve the actual results and it really displays a '?', indicating either an SQL syntax error or invalid data. The latter is the case, as the SQL runs fine after a short pause, giving FMS enough time to update its cache.

                                • 13. Re: Cotcha for PSoS (commit step)
                                  electon

                                  Additional commit steps, refresh windows etc did not help, the only way to get this right was to insert a 5 second pause step before the client side took over (although this number of seconds may vary, depending on record nrs, WAN/LAN effects etc, so it may even be guesswork how long the pause should be).

                                  I think if the target Found Count can be known before running PsOS a loop can be used right after it.

                                   

                                  Loop

                                       Exit Loop If ( Get ( FoundCount ) = XXX )

                                       Pause Script ( 1 sec. or whatever desired )

                                  End Loop

                                   

                                  That way the next step will wait until records are synced and the wait time can be more less automatic depending on increments.

                                  Could that be a possible solution?

                                  • 14. Re: Cotcha for PSoS (commit step)
                                    Markus Schneider

                                    THanks for sharing! After running into some troubles with PSoS (and server scripts as well), I'm running those solutions on my test server for a while - just to make sure that they do what I want them to do..

                                    1 2 Previous Next