onupdate/for_update fails with PassiveDefaults (maybe others?)

Issue #253 resolved
Craig Paterson created an issue

I have two columns with PassiveDefaults in my Users table:

tableName = "Users"
self.Users = Table(tableName, metadata, 
  Column("id", Integer, primary_key=True),
  Column('name', Unicode, unique=True, nullable=False),
  Column('pwd', String, nullable=False),
  Column('email', String, index="user_email_idx", nullable=False),
  Column('bio', Unicode, nullable=True),
  Column('date_created', DateTime, PassiveDefault(func.current_timestamp()), nullable=False),
  Column('date_updated', DateTime, PassiveDefault(func.current_timestamp()), 
    #PassiveDefault(func.current_timestamp(), for_update=True), nullable=False)  #1
    onupdate=PassiveDefault(func.current_timestamp()), nullable=False),        #2
  )

#1: doesn't seem to fire #2: fails with the following exception

Traceback (most recent call last): File "MagicRealm\mr_model.py", line 93, in ? session.flush() File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 234, in flush File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 192, in flush File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 363, in execut e File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 631, in execut e File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 585, in save objects File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 813, in save_obj File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 244, in execute File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 268, in execute_c lauseelement File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 284, in execute_c ompiled File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 280, in proxy File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 319, in execute raw File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 338, in _execute SQLError: (ProgrammingError) can't adapt 'UPDATE Users SET bio=%(bio)s, date_upd ated=%(date_updated)s WHERE Users.id = %(Users_id)s' {'date_updated': PassiveDef ault(<sqlalchemy.sql.Function object at 0x022F05B0>), 'Users_id': 1, 'bio': 'upd ated bio4'}

(i'm using postgres 8.1x btw)

Comments (6)

  1. Former user Account Deleted

    sorry, i should have escaped the above. also, this may be related to #120.

    my code:

        tableName = "Users"
        self.Users = Table(tableName, metadata, 
          Column("id", Integer, primary_key=True),
          Column('name', Unicode, unique=True, nullable=False),
          Column('pwd', String, nullable=False),
          Column('email', String, index="user_email_idx", nullable=False),
          Column('bio', Unicode, nullable=True),
          Column('date_created', DateTime, PassiveDefault(func.current_timestamp()), nullable=False),
          Column('date_updated', DateTime, PassiveDefault(func.current_timestamp()), 
            #PassiveDefault(func.current_timestamp(), for_update=True), nullable=False)  #1
            onupdate=PassiveDefault(func.current_timestamp()), nullable=False),        #2
          )
    

    and the error:

    Traceback (most recent call last):
      File "MagicRealm\mr_model.py", line 93, in ?
        session.flush()
      File "build\bdist.win32\egg\sqlalchemy\orm\session.py", line 234, in flush
      File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 192, in flush
      File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 363, in execut
    e
      File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 631, in execut
    e
      File "build\bdist.win32\egg\sqlalchemy\orm\unitofwork.py", line 585, in _save_
    objects
      File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 813, in save_obj
      File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 244, in execute
      File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 268, in execute_c
    lauseelement
      File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 284, in execute_c
    ompiled
      File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 280, in proxy
      File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 319, in _execute_
    raw
      File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 338, in _execute
    SQLError: (ProgrammingError) can't adapt 'UPDATE Users SET bio=%(bio)s, date_upd
    ated=%(date_updated)s WHERE Users.id = %(Users_id)s' {'date_updated': PassiveDef
    ault(<sqlalchemy.sql.Function object at 0x022F05B0>), 'Users_id': 1, 'bio': 'upd
    ated bio4'}
    
  2. Mike Bayer repo owner

    a PassiveDefault with "for update" or "on update" amounts to a SQL trigger. SA has no support for triggers and does not plan to (this may be a job for the Migrate package being built off of SQLAlchemy). the docs which stated PassiveUpdate supports on-update operations was in error and was fixed a few weeks ago; PassiveUpdates only work with INSERT as they correspond to the DEFAULT clause on a column. if you want a default value for an "UPDATE", use a plain ColumnDefault which pre-executes the function.

    ticket #120 is similar to this; if that were merged into the "Default" paradigm it would be implementing ColumnDefault, not PassiveDefault (PassiveDefaults are schema-defined defaults). However in most cases SA needs to know the value generated by a ColumnDefault after execution so its not clear if integrating the function call into the SET clause of the update is really worth it, since the row then has to be post-fetched; easier to just pre-execute it the way it happens now.

    There is a ticket #75 to add ON DELETE/ON UPDATE to foreign key which is not exactly the same thing, but that will be implemented at some point.

  3. Former user Account Deleted
    • removed status
    • changed status to open

    i built the docs from svn (rev 1744), and it includes this example (metadata.html#metadata_defaults_passive):

    # an on-update database-side default 
    Column('data2', Integer, PassiveDefault("d2_func", for_update=True)) )
    

    i'm not sure why you can't specify a server-side function on update, eg:

    update users set bio='my bio', date_updated=now()
    
  4. Mike Bayer repo owner

    PassiveDefault is only for defaults set up at the schema level:

    CREATE TABLE foo (
        mycol INTEGER DEFAULT 7
    )
    

    the only "UPDATE" equivalent for this is triggers, which SA core is not going to get into.

    ColumnDefault is for a default that SA runs at runtime:

        SELECT myfunct()
        UPDATE mytable set foo=<result of myfunct>
    

    The functionality youre looking for is present. To run the function inline is ticket #120.

    The doc example you have found is incorrect, ill remove it.

  5. Mike Bayer repo owner

    the "on_update" example for PassiveDefault has been removed in changeset:1745.

    when #120 is implemented, it wont be such a big deal to add an "inline=True" argument to ColumnDefault and allow it to execute inline, since I gather its the optimization youre looking for.

  6. Log in to comment