Servoy Issue with DB UUID PKs

[rafig]

[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

Code: Select all

"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
1) let Servoy do all UUID creation (as external systems may access the Postgres DB generating new records, so PG has to generate keys
2) 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...)
3) revert all PK's to INTEGER type (which might work with Servoy transactions) as I think that UUID's are the 'future'
4) having to use raw SQL to do it myself
5) 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)

ERD

CtoC.png (11.5 KiB) Viewed 4967 times

Flow

Create Client Company - Flowchart.png (74.9 KiB) Viewed 4967 times

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

[mboegem]

Hi Rafi,

I think setting your pk to 'db identity' does not match the way and value you want to assign to your pk's (being UUID).
I'm not completely sure, but I think Servoy is expecting a db identity column which in case of PG (and other DBE's) seems to be either smallint, int or bigint (http://www.postgresqltutorial.com/postg ... ty-column/)
I even think it's not a problem in Servoy, but more PG and/or the driver not communicating this the way you expect this to work.

The easiest way to work with DB generated values (for both Servoy and external applications to use) is by using sequences in PG.
I noticed your comment as this being a non-option, but still this works great for both Servoy and external applications, or plain inserts through rawSQL plugin. (been there, done that, should never have it done a different way)

Assumed the problem is not Servoy and you don't want to go the PG-sequence way, the only thing left is a slightly different approach (don't want to mention 'workarounds' ).
1) remove the default generation of UUID in PG.
2) create a DB 'onInsert' trigger in PG that will insert the UUID into the PK whenever it's empty on insert. (This will happen when an external application inserts records)
3) let Servoy generate the UUID through the UUID generator OR use the db-event onFoundsetRecordInsert to do this.

I think once you've completed step 1, Servoy won't change your setting to UUID-generator on restart, but try this first for one table before moving on.
'Something' must trigger this revert.

Hope this helps.

[rafig]

Hi Marc,
thanks for your reply & suggestions, I will bear them in mind & think about possibly changing over to 'sequences'.

However, I would like to know from Servoy why it is that the DB sequence type is being changed when re-opening developer...???

Thanks

Rafi

[mboegem]

why it is that the DB sequence type is being changed when re-opening developer...???

I guess the driver does report the default uuidgenerator setting in PG as db identity, which it isn't according to the page in the link.
This is the reason Servoy is reverting your changes, but still not able to use the db identity.

Just give it a try:
- create a dummy table with a pk of type uuid
- set servoy to db identity for this table
- save and restart servoy
- change the pk type to uuid generator
- save and restart again.
- what happened?

In my opinion much faster to find out the behaviour than waiting for a response...

[rgansevles]

Rafi,

Servoy recognises the uuid generation from postgres, that is why the generation is set to db-identity after restart of developer.
The postgres uuid generator is supported by servoy for quite some time, see https://support.servoy.com/browse/SVY-4991

If it does not work for you, please file a jira issue with a small sample solution and include the create-table statements.

Rob

[rafig]

Hi all,

Assumed the problem is not Servoy and you don't want to go the PG-sequence way, the only thing left is a slightly different approach (don't want to mention 'workarounds' ).
1) remove the default generation of UUID in PG.
2) create a DB 'onInsert' trigger in PG that will insert the UUID into the PK whenever it's empty on insert. (This will happen when an external application inserts records)
3) let Servoy generate the UUID through the UUID generator OR use the db-event onFoundsetRecordInsert to do this.
I think once you've completed step 1, Servoy won't change your setting to UUID-generator on restart, but try this first for one table before moving on.
'Something' must trigger this revert.
Hope this helps.

Thank you so much Marc, I did the above workaround [ ] and now it works for both Servoy created records & direct (imported directly) records!

For anyone else that might come across this and need to 'solve' it, here is the SQL for creating the 'trigger'

Code: Select all

CREATE
OR REPLACE FUNCTION update_row_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF NEW.ID IS NULL THEN
   NEW.ID := uuid_generate_v4() ;
END IF ;
RETURN NEW ;
END $$ ;


where 'ID' is the PK column set as UUID
then you need to add this 'trigger' to each table in your system with code like this

Code: Select all

CREATE TRIGGER onInsertMyTableID BEFORE INSERT ON MyTable FOR EACH ROW EXECUTE PROCEDURE update_row_trigger();

@Rob, thanks for your reply too. Servoy was seeing the PG 'uuid_generate_v4()' on PK's and then setting them as 'db_identity'. As soon as I removed that & opened developer they showed 'uuid_generator', as I had changed them to.

@Servoy, maybe in a future release, Servoy's default behaviour could be changed so that if it sees back-end columns in Postgres defined as 'uuid_generate_v4()' on a PK, you could use sequence type 'uuid_generator' and not 'db_identity', then things would work great for everyone

Thanks

Rafi

[rgansevles]

@Rob, thanks for your reply too. Servoy was seeing the PG 'uuid_generate_v4()' on PK's and then setting them as 'db_identity'. As soon as I removed that & opened developer they showed 'uuid_generator', as I had changed them to.

The uuid_generate_v4() columns should work though, so a fix (with a case filed) for that would even be better.

[rafig]

@Rob
The uuid_generate_v4() columns should work though, so a fix (with a case filed) for that would even be better.

Hi Rob,
does that mean you want me to file a case for this?
Thanks

[rafig]

I have created a Case, https://support.servoy.com/browse/SVY-13551, for this.

[rgansevles]

Thanks!