[EDITED: Issue happens with or without transactions, thought it was only with transactions]
Hi,
another day, another bug/issue (although no one seems to have responded to my other post https://forum.servoy.com/viewtopic.php?f=69&t=22421 )
Anyway, this time it looks like there is a problem with how Servoy does database transactions when the backend is Postgres (I’m using 10.7 in a Mac App called ‘Postgres’ that’s like MAMP, but just acts as a Postgres database server) and I have defined all of the PK’s in my backend as UUIDs like this
"id" uuid NOT NULL DEFAULT uuid_generate_v4()
...
ALTER TABLE "mytable" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE
In Servoy developer if these are set as sequence type ‘db_identity’, if I start a Servoy transaction, create a new record, then save that record, it returns ‘true’ that save was ok, but the ID/PK does not have anything in it (no UUID), so then further down my code where I am trying to create a related record for a 2nd new created further down a join, it fails (because there is no PK on left of relationship).
HOWEVER, if the PK’s are all changed to sequence type ‘uuid_generator’, the UUIDs for the PK’s are created and everything works as I had hoped.
(You might think ‘why is he moaning again?’, but there is more…)
BUT, when I quit Servoy Developer (8.4.0) and re-open it, ALL the PK’s have gone back to being sequence type ‘db_identity’, so when I run my transaction code again, it doesn’t work!!!
And when I try to deploy this to server (I have used the method of creating a WAR with all Servoy stuff to run on Tomcat, but no solution, then exporting a .servoy ‘file’ directly to server) I have to make sure I go in to all DB’s and set sequence type to ‘uuid_generator’, and then do the export/deploy so they go with correct sequence type.
This is VERY frustrating
I do not want to have to do any of the following
- let Servoy do all UUID creation (as external systems may access the Postgres DB generating new records, so PG has to generate keys
- generate my own UUID’s in my code with the transaction & remember to assign it to the PK just so I know there is one (as what happens when it’s committed, might it get overwritten…)
- revert all PK’s to INTEGER type (which might work with Servoy transactions) as I think that UUID’s are the ‘future’
- having to use raw SQL to do it myself
- some other workaround (the whole great thing normally about working with Servoy, is NOT having to do workarounds)
I will attach some images showing the basic ERD for the section of my solution this is for along with a very simple pipeline of events (with last step failing)
[attachment=1]CtoC.png[/attachment]
[attachment=0]Create Client Company - Flowchart.png[/attachment]
I hope that this can be fixed and addressed in the 201903 release if possible, as I can’t hang on for this in 201906 release… or if there is a simple answer (e.g. Rafi you are an idiot & are doing it wrong, do it like this), then I’m very happy to hear that (but if not I’m very angry that I have hit yet another issue/bug in a supposedly mature, working product…)
[RANT OVER]
[UPDATE, as stated at top, this happens even if I turn off transactions, the failure to create UUID’s, not the switching of sequence types, which also always happens…]
Thanks
Rafi