Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

#109 Use "insert ... returning id" with postgres

None
closed-accepted
Oleg Broytman
None
5
2013-01-19
2013-01-18
Ken Lalonde
No

When inserting records with driver postgres, it's faster to use the single statement "INSERT ... RETURNING id",
rather than 2 statements -- one to get the current sequence value (and increment it), and another to do the insert.
Patch is against SQLObject-1.2.4/sqlobject/postgres/pgconnection.py.

Discussion

  • Ken Lalonde
    Ken Lalonde
    2013-01-18

     
    Attachments
  • Oleg Broytman
    Oleg Broytman
    2013-01-18

    Good idea, thanks! Will test it. It's a major change (SQLObject currently supports Postgres back to 8.1 while INSERT/RETURNING requires at least 8.2) so I can only apply it to the trunk.

     
  • Oleg Broytman
    Oleg Broytman
    2013-01-18

    • assigned_to: nobody --> phd
     
  • Oleg Broytman
    Oleg Broytman
    2013-01-19

    I have to work on the patch. There were two problems. First, it was created against branch 1.2 and I have to modify it to apply to the trunk. Second, it creates a possibility for an INSERT without columns in case like

    1
    2
    class Test(SQLObject):
        pass
    

    SQLObject has never had a need to handle such a case — at least the id column was always there. With your patch SQLObject has to generate query "INSERT INTO table DEFAULT VALUES" instead of "INSERT INTO table () VALUES ()".

    Now everything works. Applied and committed in the revision 4574 in the trunk. Will be in the next release. Thank you!

     
  • Oleg Broytman
    Oleg Broytman
    2013-01-19

    • status: open --> closed-accepted
    • milestone: -->