Is there a way to find ID of inserted row if there is trigger after insert?

[Dragan Matic]

If I do something like this:

new_id = db.table1.insert(...)

and there is a trigger that inserts row in another table (table2) based on insert in table1 then the value of new_id is the id that is inserted in table2 instead of table1. Is this a bug or a feature? If it is a feature how can I get id from table1?

Dragan

[Leonel Câmara]

Check _after_insert it's probably what you want.

http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#before-and-after-callbacks

[Niphlod]

it doesn't have to do with after_insert ..... it's something "broken" at the backend level..... what backend are you using dragan ?

[Dragan Matic]

Version 2.9.5-stable+timestamp.2014.03.16.02.35.39
Database drivers available: SQLite(sqlite3), MySQL(pymysql), PostgreSQL(pg8000), IMAP(imaplib)

Postgresql 9.3 on linux mint 17.

trigger is BEFORE_INSERT

[Dragan Matic]

I have found what causes it, in dal.py in PostgreSqlAdapter this method is supposed to return last inserted id:

def lastrowid(self,table = None):
        self.execute("select lastval()")
        return int(self.cursor.fetchone()[0])

This here http://stackoverflow.com/questions/17819001/postgres-not-returning-lastval-properly  says this the wrong way to ask for ID and that it should be returned this way:

insert into table ([columns]) values  ([values]) returning id

So it will return wrong ID everythime there is trigger that inserts something else somewhere else.

I'm sending a bug report

[Leonel Câmara]

I don't think this "bug" is just in PostgreSQL. For instance MSSQL should probably use OUTPUT INSERTED.ID to get the id and Oracle can use RETURNING like PostgreSQL too.

Basically any kind of lastval variant can be problematic.

 

[Niphlod]

not really (scouting through code and docs seems that we're using only for postgresql something that isn't that "stable" when triggers are involved), but it's anyway fixable.

[Richard Vézina]

Did we open a ticket and solve this one... I think I have this issue... I try to create an archive table and I fall on this error

dal/pydal/adapters/postgres.py", line 181, in lastrowid

    return int(self.cursor.fetchone()[0])

I can't figure out why so far...

Richard

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Massimo Di Pierro]

Can you show the full traceback?

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscribe@googlegroups.com.

[Richard Vézina]

Traceback (most recent call last):
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment
  File "/home/richard/Documents/programmation/version_2146/web2py/applications/sgddms/controllers/test.py", line 3883, in <module>
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/globals.py", line 417, in <lambda>
    self._caller = lambda f: f()
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/tools.py", line 4241, in f
    return action(*a, **b)
  File "/home/richard/Documents/programmation/version_2146/web2py/applications/sgddms/controllers/test.py", line 766, in update
    form.process(detect_record_change=True)
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/html.py", line 2298, in process
    self.validate(**kwargs)
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/html.py", line 2236, in validate
    if self.accepts(**kwargs):
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/sqlhtml.py", line 1744, in accepts
    self.id_field_name]).update(**fields)
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/packages/dal/pydal/objects.py", line 2049, in update
    if any(f(self, update_fields) for f in table._before_update):
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/packages/dal/pydal/objects.py", line 2049, in <genexpr>
    if any(f(self, update_fields) for f in table._before_update):
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/packages/dal/pydal/objects.py", line 390, in <lambda>
    archive_record(qset, fs, db[an], cn))
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/packages/dal/pydal/helpers/methods.py", line 97, in archive_record
    archive_table.insert(**fields)
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/packages/dal/pydal/objects.py", line 726, in insert
    ret = self._db._adapter.insert(self, self._listify(fields))
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/packages/dal/pydal/adapters/base.py", line 751, in insert
    id = self.lastrowid(table)
  File "/home/richard/Documents/programmation/version_2146/web2py/gluon/packages/dal/pydal/adapters/postgres.py", line 181, in lastrowid
    return int(self.cursor.fetchone()[0])
TypeError: int() argument must be a string or a number, not 'NoneType'

The only thing I change, is that I activate record versioning over a table in my system... I guess that since there is detect_record_change=True, there is more than only one callback that happen and it cause the versioning callback to failed because of "lastrowid". So I guess the solution is to use RETURNING as suggest by Dragan and Lionel...

Richard

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.

[Richard Vézina]

https://github.com/web2py/pydal/issues/399

And PR

https://github.com/web2py/pydal/pull/400