GeoTools
  1. GeoTools
  2. GEOT-2231

Support for getNextAutoGeneratedValue impossible on postgresql

    Details

    • Type: Bug Bug
    • Status: Closed Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5.7, 2.6-M2
    • Component/s: jdbc
    • Labels:
      None

      Description

      The code assumes it's ok to call the method before performing an insert, but generally speaking, that does not make sense, since the next pk value is yet to be generated. On H2 and SqlServer an access to the "curr" value is performed, which generates one if missing I guess?
      But in postgis using currval to do the same results in an error:
      "Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did. "

      So it's not really possible to report back the values generated by a "serial" type in postgresql, as the function should be called after the insert occurred.

      Marked as critical since "serial" is the most common way to create a primary key.

      1. GEOT-2231.patch
        25 kB
        Justin Deoliveira

        Activity

        Hide
        Amos Hayes added a comment -
        Why do you need to know the currval before an insert? Just out of curiosity...
        Show
        Amos Hayes added a comment - Why do you need to know the currval before an insert? Just out of curiosity...
        Hide
        Andrea Aime added a comment -
        Because there is no sane way to retrieve it after the insert is made. The getGeneratedKeys() call that should provide the value is not working properly in most databases, and in particular, it's not implemented in the postgres one.
        Looking at this discussion it seems it will be scheduled for inclusion in the 8.4 version of the driver thought:
        http://groups.google.com/group/pgsql.interfaces.jdbc/browse_thread/thread/ab96f52b967e296e

        Not sure if that driver can talk to older versions of postgres thought (one of the reasons we're not upgrading the jdbc driver, the other being that it became slower at transferring geometries)
        Show
        Andrea Aime added a comment - Because there is no sane way to retrieve it after the insert is made. The getGeneratedKeys() call that should provide the value is not working properly in most databases, and in particular, it's not implemented in the postgres one. Looking at this discussion it seems it will be scheduled for inclusion in the 8.4 version of the driver thought: http://groups.google.com/group/pgsql.interfaces.jdbc/browse_thread/thread/ab96f52b967e296e Not sure if that driver can talk to older versions of postgres thought (one of the reasons we're not upgrading the jdbc driver, the other being that it became slower at transferring geometries)
        Hide
        Justin Deoliveira added a comment -
        Here is a patch for this one, which does the following:

        * Adds a flag to the dialect which controls wether auto generated insert values are determined pre insert or post insert. SQLDialect.lookupGeneratedValuesPostInsert()
        * Adds a method getLastAutoGeneratedValue() to mirror getNextAutoGeneratedValue()
        * Some minor changes to ContentFeatureCollection to pass through some user data
        Show
        Justin Deoliveira added a comment - Here is a patch for this one, which does the following: * Adds a flag to the dialect which controls wether auto generated insert values are determined pre insert or post insert. SQLDialect.lookupGeneratedValuesPostInsert() * Adds a method getLastAutoGeneratedValue() to mirror getNextAutoGeneratedValue() * Some minor changes to ContentFeatureCollection to pass through some user data
        Hide
        Andrea Aime added a comment -
        The patch looks good to me.
        Show
        Andrea Aime added a comment - The patch looks good to me.
        Hide
        Andrea Aime added a comment -
        Mass closing all issues that have been in "resolved" state for 2 months or more without any feedback or update
        Show
        Andrea Aime added a comment - Mass closing all issues that have been in "resolved" state for 2 months or more without any feedback or update

          People

          • Assignee:
            Justin Deoliveira
            Reporter:
            Andrea Aime
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: