[Phpgroupware-developers] Scheme Proc on sequence for postgresql

[Edgar Luna]

Hi everyone.

While testing the scripts for upgrade of contacts backend, we found
that the algorithm for DropColumn is like this:

if copy_data:
        Create a temporal table t1.
        Fill t1 with all the data from orginal_table.
end_if

Drop original_table

sql = get_sql_table()
sequence_sql = get_sql_secuence()

Create sequence with sql_sequence
if copy_data:
        Create_Table new_table with sql
        __exit__
end_if

Create_Table new_table with sql ---- [this includes a nextal(sequence)
insert_data from old table

Drop t1
__exit__

But, because the insert_data includes insert to the field that include
the sequence, then the sequence don't step forward, so the sequence
and the table don't match on nextval for fields that are defined with
sequence.
This makes that the next N attempts to insert will fail. Where N is
the number of record that exist before DropColumn is called.

In order to fix this, I think that the best is, change the algorithm
to do:

if copy_data:
        Create a temporal table t1.
        Fill t1 with all the data from orginal_table.
end_if

## HERE we get the max value first
max = get_max_value()

Drop original_table

sql = get_sql_table()
sequence_sql = get_sql_secuence()

## HERE we change the sequence string, based on the syntax:
## CREATE SEQUENCE seqname [ START start ]

Create sequence with sql_sequence starting on max
....
# NEXT is the same


If you approve this change or have any other solution for the problem
that what happens with sequence on PostgreSQL when using scheme_proc`s
method DropColumn, please comments.

-- 
Edgar Antonio Luna Díaz - http://www.sogrp.com
Fingerprint: C008 5EAC 5272 AC8C 7589  4821 8B34 6166 8733 8310

pgpQwDMBifVn5.pgp
Description: PGP signature