With Servoy 5.2.1 on a mac-mini running OS X 10.6.4, I am experiencing issues with setting sequences for postgres tables. Solution runs fine under 5.1.4 on Sybase v11; and was OK under 5.2 postgres. But now with 5.2.1 I find that Servoy Developer shows all tables with primary key sequence set to dbIdentity, changed from Servoy sequence.
To amend back to Servoy sequences, I go through all tables, changing the sequences to Servoy, and saving. Checks show that all keys are now set to Servoy sequences. I export the solution, then import to Servoy server, changing the sequences to the imported solution type. But then I find that a client creating a new record has related records already - a sign of a repeat key.
When I start Developer again, I find that the sequences are back to dbidentity. Separately, on running a smart client connected to the application server and creating a new record, I get a db error on committing the transaction.
This result looks like issue “295874 sequence type “servoy seq” not saved”, which was fixed for 5.2.
As extra check can you look with PgAdmin at that table to see if the PK column is of type Bigint or Integer (without a default value “nextval(‘somename_seq’::regclass)”)? I.e. to check if you don’t have any database sequences defined on it.
Good suggestion. Following your tip I found that the PK column of each table is of type integer or Bigint, and has an associated db sequence. However, each db sequence value is set to 1.
I should explain that the database is a result of a migration from Servoy 5.1.4 and Sybase v11 (as supplied with Servoy) to Servoy 5.2.1 with its supplied Postgres db. I used Servoy tools (Developer and Server) for the entire migration: no external tools at all. The 5.1.4 solution has db sequences set to ‘db indentity’.
I exported the solution from 5.1.4 using Servoy Developer, with sample data set to ‘all rows’.
I imported the solution using Servoy Server 5.2.1, with sequences set to those given in the solution, and sample data imported.
The great news is that the migrated solution looks good. All tables and rows have migrated. In 5.2.1 Developer I have had to adjust SQL queries to the Postgres dialect, as expected. However, with Servoy having left the db sequence values at 1, I get errors on attempting to create new records.
It seems that I have two options:
Use PgAdmin to drop the PK sequences; change all PK columns using Servoy Developer: from ‘db identity’ to ‘Servoy sequence’.
Use PgAdmin to reset the db sequence values to the appropriate number, following import.
Am I missing something here? Does 5.2.1 incorporate a way of both creating the db and resetting the sequence value following import?
I just migrated a simple solution with db identities from servoy 5.1/sybase to 5.2/postgres.
The pks in postgres show up as dbident, as expected.
I migrated by just exportin/importin the solution.
When you do that you get servoy sequence in postgres?
Case “295874 sequence type “servoy seq” not saved” is about changing the sequence type from db ident to servoy seq in developer.
This does not really work because Servoy checks if a pk column is auto-increment.
When it is we have to treat the column as dbident because for some databases we have to remove the pk column from the insert statement, otherwise an sql error occurs.
When I follow your process I too get db indentities as expected.
The problem I was experiencing was having postgres tables with thousands of rows but the db sequences all set to 1 following the completion of the Servoy import.
A sequence number can be reset using SQL through pgAdmin. For example, for table ‘actions’: SELECT SETVAL(‘actions_id_action_seq’ , MAX(id_action)) FROM actions. However, this is rather tedious if you have 40 tables!
Would it not be sensible for the Servoy import to include the process of resetting sequences on import?