Menu

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

None
closed-accepted
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
     
  • 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: -->
     

Log in to post a comment.

Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.