onupdate/for_update fails with PassiveDefaults (maybe others?)

[Craig Paterson]

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)

[Former user]

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'}

[Mike Bayer]

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.

[Former user]

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()

[Mike Bayer]

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.

[Mike Bayer]

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.

[Mike Bayer]

Removing milestone: 0.3.0 (automated comment)