PostgreSQL sequences again :-(, Servoy 3.5.2

Classic Servoy
1

Hi all,

I find the Auto Enter settings very confusing. I have about 80 tables, all with pk’s generated by sequences. The Auto Enter settings dialog offers me various ways to telling Servoy this:

  1. Value = database managed
  2. Sequence = db sequence
  3. Sequence = db identity
  4. Custom?
  5. Database Default = nextval(‘some_sequence_name’::regclass)
  6. Lookup value (not right here)

Servoy seems to default to 3. “Sequence = db identity”. This does not work properly since Servoy makes its own guesses about what the sequence name should be. What is really IRRITATING is that if I search the repository, the repository has the correct sequence name in the db_sequence_name of the servoy_columninfo in the repository.

Then I tried 2. “Sequence = db sequence” and to specify my sequence names in “Specify”. This seemed to work fine in developer, so I did this on 100 tables… only to discover this does not work in client.

:frowning:

And when I restarted my test server my settings had reverted back to “Sequence = db identity” again.

Then I have tried 5. “Database Default”, this does not save and jumps back to 3. “Sequence = db identity”.

Just to make sure I’m not doing something stupid,
What is the correct setting here?

2

I now realise this post is a variation of an ‘old favourite’,
see

http://forum.servoy.com/viewtopic.php?t=9000

Any News on this?

Do I need to switch to Servoy sequences to be able to move to Servoy 3.5?

3

There seem to be a workaround for this:
Set the Auto-Enter to DB identity.
Rename your Sequence to

tablename + ‘_’ + pk_name + ‘_seq’

Remember to also change the sequence name in the auto enter in PostgreSQL.

Restart Servoy, and it will recognise your sequence.

It should be possible to run a query on the Servoy repository to generate a set of queries to do all the renaming in one go.

4

Here are two queries which generate SQL for PostgreSQL to rename sequences to a more Servoy-compatible format so they are accepted as DB identites by Servoy 3.5.x:

SELECT 'ALTER TABLE ' || db_sequence_name || ' RENAME TO ' || tablename || '_' || columnname || '_seq ;' FROM servoy_columninfo WHERE auto_enter_type = 2 AND connection_name = 'myconnection' AND is_row_ident = 1 AND NOT (db_sequence_name ILIKE tablename || '_' || columnname || '_seq') ORDER BY tablename
--SELECT 'ALTER TABLE  ' || tablename || ' ALTER COLUMN '  || columnname || '  SET DEFAULT nextval(\''  || tablename || '_' || columnname || '_seq\'::regclass);' FROM servoy_columninfo WHERE auto_enter_type = 2 AND connection_name = 'myconnection' AND is_row_ident = 1 AND NOT (db_sequence_name ILIKE tablename || '_' || columnname || '_seq') ORDER BY tablename

Back up your database before running.
Replace ‘myconnection’ with the name of your database connection, in single quotes.