fails to set custom sequence name upon table creation in postgresql

[alfonsodg]

From andrea.p...@gmail.com on January 26, 2011 10:50:30

I've been trying to use legacy postgres databases that uses a custom sequence name. I'm able to read and write a table, by specifying the sequence_name parameter when declaring the Table object lets say:

table_name = 'table' db.define_table(table_name, Field('%s_id'%table_name,'id'), Field('filed1','string'), Field('field2','string'), migrate=True, sequence_name = '%s_pk_seq'%tablename)

If the table is created before using web2py everything goes smooth, and I can add values and fields to the table.

However if the Table does not exist, web2py creates it without setting the given sequence name. Is this the supposed behavior or a bug?

This test was done on a 8.3.4 postgresql server running under debian sid. I don't know if this issue is also related to other db backend.

Original issue: http://code.google.com/p/web2py/issues/detail?id=167

[alfonsodg]

From massimo....@gmail.com on January 28, 2011 07:02:22

Please check this now works in trunk

Status: Started

[alfonsodg]

From andrea.p...@gmail.com on January 31, 2011 08:45:38

thanks Massimo. the posted example has some error... by running this:

table_name = 'test' db2.define_table(table_name, Field('%s_id'%table_name,'id'), Field('filed1','string'), Field('field2','string'), migrate=True, sequence_name = '%s_pk_seq'%table_name)

I get the correct 'test_pk_seq' and it is used for the test_id sequence. however an additiona sequence is created in the postgresql db: 'test_test_id_seq' this is apparently not used

[alfonsodg]

From andrea.p...@gmail.com on January 31, 2011 09:16:55

also consider that, by setting this:

table_name = 'test' db2.define_table(table_name, Field('%s_id'%table_name,'id'), Field('filed1','string'), Field('field2','string'), migrate=True, sequence_name = '%s_pk_seq'%table_name)

table_name = 'test2' db2.define_table(table_name, Field('filed1','string'), Field('field2','string'),)

table test2 is not created, and the same for other tables declared after that point. if I set a sequence_name for table test2, it correctly created.

[alfonsodg]

From massimo....@gmail.com on February 05, 2011 20:55:18

Please help me with this. There is the following code in dal.py

# following too lines should only be executed if sequence_name does not exist                   
    # self.execute('CREATE SEQUENCE %s;' % sequence_name)                                           
    # self.execute("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT NEXTVAL('%s');" % (tablename,fieldna\

me,sequence_name))

How do I check in postgresql is the sequence exists so that the last two lines are executed only conditionally?

[alfonsodg]

From andrea.p...@gmail.com on February 06, 2011 03:39:50

this should work:

col_defaults = self.execute('''SELECT column_default, column_name FROM information_schema.columns WHERE table_name = '%s'; '''%tablename, as_dict = True) for column in col_defaults: if column['column_name'] == fieldname: if column['column_default']:#if a sequence name exist it is written here, but this condition can be skipped if not needed current_sequence_name = column['column_default'].split("'")[1] else: self.execute('CREATE SEQUENCE %s;' % sequence_name)
self.execute("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT NEXTVAL('%s');" % (tablename,fieldna\ me,sequence_name))

of course this will return an error if table does not exist, and also let you know the currently set sequence name in the current_sequence_name variable

[alfonsodg]

From andrea.p...@gmail.com on March 09, 2011 06:36:01

this version of dal.py should work I've modified the 1.93.2 version.

Attachment: dal.py

[alfonsodg]

From andrea.p...@gmail.com on May 05, 2011 03:34:08

last, working version:

def create_sequence_and_triggers(self, query, table, **args): self.execute(query)#create table '''change sequence name''' rows = self.execute('''SELECT column_default, column_name FROM information_schema.columns WHERE table_name = '%s';'''%table._tablename,) current_sequence_name = None if rows: for column_default,column_name in rows: if (column_name == table._fieldname) and ('nexval(' in column_default): current_sequence_name = column['column_default'].split("'")[1] break if (not current_sequence_name) and (table._sequence_name != self.sequence_name(table)): self.execute('CREATE SEQUENCE %s;' % table._sequence_name)
self.execute('''ALTER TABLE %s ALTER COLUMN %s SET DEFAULT NEXTVAL('%s');''' % (table._tablename, table._id.name, table._sequence_name))

self.execute('DROP SEQUENCE %s;' % self.sequence_name(table))#this should remove the standard _is_seq sequences but is not working

this ends up in duplicated sequences, since the standard one is firstly created when creating the table, then a new one is created and assigned to the table. however the last DROP SEQUENCE command cannot be issued since the standard sequence do not exist until the db.commit() is run. this works but is not elegant.

[alfonsodg]

From andrea.p...@gmail.com on May 26, 2011 08:03:56

finally 100% working code, this also removes the standard sequences created automatically and named as 'tablename_idfield_Seq':

def create_sequence_and_triggers(self, query, table, **args):
    self.execute(query)#create table
    '''change sequence name'''
    rows = self.execute('''SELECT column_default, column_name  FROM information_schema.columns WHERE table_name = '%s';'''%table._tablename,)
    current_sequence_name = None
    if rows:
        for column_default,column_name  in rows:
            if (column_name == table._fieldname) and ('nexval(' in column_default):
                current_sequence_name = column['column_default']#.split("'")[1]
                break
    if (not current_sequence_name) and (table._sequence_name != self.sequence_name(table)):
        self.execute('CREATE SEQUENCE %s;' % table._sequence_name)  
        self.execute('''ALTER TABLE %s ALTER COLUMN %s SET DEFAULT NEXTVAL('%s');''' % (table._tablename, table._id.name, table._sequence_name)) 
        self.execute('DROP SEQUENCE %s_%s_Seq;' % (table._tablename, table._id.name))#this should remove the standard _id_seq sequences

[alfonsodg]

From andrea.p...@gmail.com on June 23, 2011 08:09:18

This is a working patch for web2py dal.py file in versione 1.96.4. I've added a function to get the current sequence name, and also modified the lastrowid function accordingly. this works both for table with custom sequence and those with standard sequence.

Attachment: dal.py.patch

[alfonsodg]

From andrea.p...@gmail.com on June 24, 2011 02:27:27

it looks like sequence names are handled as case insensitive in postgresql

Attachment: dal.py.patch

[alfonsodg]

From massimo....@gmail.com on July 21, 2011 08:26:31

Sorry for my late reply. I did not notice all your posts until today. Your patch is good but has two problems:

• for eget all to lastrowid if check for sequence names and that adds a huge overhead on insert.
• by having the sequence creation handled by web2py it breaks migrations. I think sequence creation should be handled outside of web2py If you can solve the former issue we can find a solution for the latter.

Status: Accepted

[alfonsodg]

From massimo....@gmail.com on December 02, 2011 22:40:54

Status: WontFix