Desync issue using RDS Postgres DB

[andresjgc]

Hi, it has happened multiple times that the user updates some data through the app and performs some actions and suddenly the app goes back to the same state as it was initially. We are using a Postgres DB hosted in AWS. We recently switched from Google Sheets to Postgres and we started noticing this sync issue. 

What are the common reason that will make this happen? How can I monitor the app and see what's causing this issue?

Thanks

[ai_saks]

Hi andresjgc,

I have the same issue on my prototype app, which uses Postgresql hosted on Google Cloud SQL. The problem only occurs when user is trying to immediately edit the newly added row. The scenario goes like this:

1.) User click 'Add' and fill in the form and click 'Save'

2.) User immediately clicks on an action (on Detail View) which edits a field on the newly added row within a few seconds. For example, an action that changes [State] from 'draft' to 'approved'

3.) After 5-10 seconds, the changes made in 2.) was reverted ([State] was reverted back to 'draft')

4.) I notice if user waits long enough (more than 10 seconds) before trying to edit the newly added row, this problem does not occur.

I didn't have this issue when my database was on Google Sheets. I'm not sure whether this has something to do with how Appsheet interacts with Postgresql.

Hope somebody can help explaining this.

[andresjgc]

Hi ai_saks,

The workflow that you have explained is pretty similar to mine. It's basically happens when an item gets added and is then updated. Seems like the update operation get's dropped and is not getting pushed to the db. I'm not sure what are the best ways to debug this issue and what is the reason for this happening

[SkrOYC]

https://www.appsheet.com/Support/Contact

[andresjgc]

Do you know or someone else knows what  "UpdateDeletedRecord": "531B2ABA-C263-4433-A61E-AD14FCC1031C" means? This shows up in the logs for the Edit operation on data stored in the Postgres DB but doesn't show up for data stored in Google Sheets

[Phil]

Audit History reports "UpdateDeletedRecord" when the client submits an Update request but the server cannot find that record to perform the update. This can arise legitimately in the following way. 
1. User 1 adds a records
2. User 2 reads the newly added record
3. User 1 (or any other user) deletes the added record.
4. User 2 attempts to update the newly added record.

User 2's update will report "UpdateDeletedRecord" because it could not find the record to update it.

[andresjgc]

Hey ai_saks, I'm wondering what type of ID  you are using for your PostgresDB table's? I'm managing the db through Django and the id's were defaulted to integer with auto-increment, I noticed this wouldn't work with appsheet so I changed the id to be UUID within django so appsheet can write the id into it

[ai_saks]

Hi andresjgc,

My key column is also uuid in my Postgresql database.

[andresjgc]

@LeventK @pravse @Aleksi @Steve 

Sorry I'm tagging you guys but still haven't heard back from support so I was wondering if you guys have any idea. Getting this issue solved is critical and I need to find a solution as soon as possible

[andresjgc]

The workflow is similar to what ai_saks said.

In my case: 

1. Use a form to create a row. This will add a timestamp using UTCNOW() in a column

2. In the following view (Deck view) the new item is shown. Using a button to trigger an action, a form is displayed, a column gets updated with the form and then an action is triggered to set a value in a column which is a timestamp.

3. The UI updates to display the new state but after all syncing is done, it goes back to the initial state.

I guess the My appsheet app connects to an Appsheet server which then communicated to my Postgres DB. All the events created from the app seems to be sent to the appsheet server because I can see them in the Audit Log and they even show as success. However, I'm using a session manager with my DB and I see that the INSERT statement runs but the two UPDATE statements never get executed. So I guess something is happening in the appsheet server that is dropping the update commands. I don't know if its coincidence or not but from one of my posts above, I see there's an UpdatedDeletedRecord item coming back with the ID of the row getting updated.

Any help is greatly appreciated. Thanks

[Steve]

The problem is likely with your database, not AppSheet at all. If the data is making it to your database (as you say), but not getting written, then something with your database is preventing the write. It's not uncommon for database admins to try to tune the databases that AppSheet uses, but in doing so, they break AppSheet.

[SkrOYC]

Maybe is a permissions thing for the user you use to connect to your sql

[andresjgc]

Could you elaborate more on it? 

I'm using UUID for my ids and the statement that should run in the database are INSERT, UPDATE, and another UPDATE. I don't know if creating this ids adds some type of overhead which then prevents the two UPDATE not to run successfully. Another question that I have, when a row is created and is followed by an update operation, how is that managed in appsheet? The two updates aren't showing up in my session manager so maybe when the appsheet server tries to create those update sql statements is falling into a race condition because the insert is taking more time than the update, something along those lines

[andresjgc]

Sorry, when I meant "never get executed" I actually wanted to say that the query doesn't show up in the session manager. It only shows up when I retry this operation after 10 seconds or more. Otherwise, I don't see the statement in the session manager

[andresjgc]

After trying different things, I think I found the problem. I generated all my tables and relationships through Django. I created the foreign keys to the column that were labeled as Ref in appsheet thinking that somehow there was some sort of constraint needed in the database. I decided to make a copy of my postgres db app and selected the option to copy the tables into google sheet. The sync issue didn't show up in this version of the app. I then decided to copy this app and selected the option to generate the tables in my existing postgres db. I tried to reproduce the issue and I'm glad it didn't appear. When looking into the schema of the generated table, I realized the was no foreign key relationship set in the database which might be the cause if the problem or maybe some other configuration. 

Question to the community,  is generating the tables on your own (not through appsheet) and then connecting them to appsheet is not a good practice? Could setting the constraint in the database of foreign key to another table be the cause of the issue or is there something else I should look at? I would really like to have those foreign key sets because I'm planning of using the data through a django app as well.

Thanks!

[ai_saks]

You're right andresjgc. Removing the foreign key constraint does fix this problem. I guess right now I have to make do with removing all the foreign keys. Too bad if the foreign key itself is the cause of this problem because it really improves the quality of any DB structure. It'd be great if the support team or other Appsheet veterans can verify and help with this issue.

[Steve]

---

@andresjgc wrote:

Question to the community,  is generating the tables on your own (not through appsheet) and then connecting them to appsheet is not a good practice? Could setting the constraint in the database of foreign key to another table be the cause of the issue or is there something else I should look at?

---

Best to let AppSheet create and drive the structure. AppSheet is not designed to share its data sources with other applications. Consider using web hooks and the API to integrate AppSheet with other apps.

[andresjgc]

Ok! Makes sense, thanks for the clarification 

[Jonathon · Accepted solution]

FYI; this is a case sensitivity issue.

Appsheets UNIQUEID("UUID") formula generates a uuid like the one you posted (531B2ABA-C263-4433-A61E-AD14FCC1031C)

Postgres will accept that UUID, but cast it to lowercase.

This will cause issues with workflows that create a record and then within a short timeframe attempt to interact or perform another data action against the row.

Try using LOWER(UNIQUEID("UUID")) and see if the issue goes away.

[ai_saks]

Hi Jonathon, 

You're alsolutely right. LOWER(UNIQUEID("UUID")) does solve this problem. Thank you so much @Jonathon .

[andresjgc]

Hi Jonathon,

Thanks for the suggestion, that actually made it work, amazing!

[Phil]

Excellent diagnosis Jonathan. 

There was actually a clue in Audit History. The failing update contained "UpdateDeletedRecord".  Audit History reports "UpdateDeletedRecord" when the client submits an Update request but the server cannot find that record to perform the update. This can arise legitimately in the following way. 
1. User 1 adds a records
2. User 2 reads the newly added record
3. User 1 (or any other user) deletes the newly added record.
4. User 2 attempts to update the newly added record.

User 2's update will report "UpdateDeletedRecord" because it could not find the record to update. The server looks for the updated record using its key value.

In this case, the update was failing because the key compare is case sensitive and Postgres was modifying the added record's key value by making it lower case. The upper case key in the update did not match the lower case key in the newly added record, so the server reported "UpdateDeletedRecord" when it failed to find the record.

I have updated the following article to describe the problem and Jonathan's solution. https://help.appsheet.com/en/articles/1332187-using-data-from-postgresql
Comments or suggestions for improving the article are welcome.

[andresjgc]

Hi Phil, thanks to getting back on the UpdateDeletedRecord question and for adding this in the Wiki