I’am porting a Servoy 3.5 application on iAnywhere 10 to Servoy 5.2 on Postgresql 8.4.
Everything went positively as expected just adapting few SQL statements like the one needed to reset db sequences; I tested it initially to arrange sequences after initial data migration (higher values) and it was working properly.
Now I am facing a strange behaviour: in some app functions I need to delete rows of some tables and to reset related db sequences with (obviously) a lower value.
It is not working!!!
What I do in a Servoy method as an example is:
TRUNCATE TABLE table_name
then in another Servoy method for all truncated tables:
SELECT COALESCE(MAX(id_field), 0) FROM table_name
SELECT setval(‘table_name_id_field_seq’, max_value + 1)
and both sets of instructions are executed within transactions.
The mistake is in the first SELECT statement that is returning the max_value as the table was NOT truncated!
I tried to stop the execution after the first stage (truncating) just before executing the mistaking select; at that stage executing the select externally with interactive SQL is returning correctly 0 (zero) while executing it within the Servoy method is returning a number exactly like the table was not truncated.
I thinked about some cache problem so I exited from the Servoy app and restarted it executing the function in a new session; the problem it is there again, no changes: still the first select returning the previous value.
I am new to Postgresql…and…I’m running out of ideas…
Any suggestion/hint on what can be wrong?
Tnks,
Gianni