[nhibernate-issues] [NHJIRA] Commented: (NH-2720) Exception using PostgreSQL with server-side prepared statements and identity columns

[Stefan P. (JIRA) <nh...@gm...>]

    [ http://216.121.112.228/browse/NH-2720?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=21155#action_21155 ] 

Stefan Podskubka commented on NH-2720:
--------------------------------------

The workaround I have mentioned (change PostgreSQL81Dialect.SupportsInsertSelectIdentity to return "false") sometimes leads to errors.
For some reason the "select lastval()" statement sometimes throws an NpgsqlException saying "lastval is not yet defined in this session", yet a second try a few seconds later is successful.
I have no idea if that's possible but for that to happen the "select lastval()" statement must have been executed on a different database session than the INSERT statement.

I have found another workaround now which doesn't need a modification of the NHibernate source code:
I have specified the correct sequence name in the mappings of my ID columns with the "native" ID Generation algorithm, like this:
  <id name="Id" column="`ID`" access="field.lowercase">
    <generator class="native">
      <param name="sequence">`Table_ID_seq`</param>
    </generator>
  </id>
I changed my configuration to use PostgreSQLDialect instead of PostgreSQL82Dialect, this way the sequence is used directly instead of the identity column approach.
With this I have no problem with server-side prepared statements, because the two statements resulting from this change are always executed seperately.

> Exception using PostgreSQL with server-side prepared statements and identity columns
> ------------------------------------------------------------------------------------
>
>                 Key: NH-2720
>                 URL: http://216.121.112.228/browse/NH-2720
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.1.0
>            Reporter: Stefan Podskubka
>            Priority: Minor
>
> I did not write a test case for this issue because I think this scenario is not supported by the test framework.
> .NET Framework version: 3.5
> Database: PostgreSQL 8.3.3
> Dialect: NHibernate.Dialect.PostgreSQL82Dialect
> Driver: NHibernate.Driver.NpgsqlDriver (Npgsql.dll version 2.0.11)
> Additionally I have set this configuration property:
> <property name="prepare_sql">true</property>
> This issue is related to NH-2267, it has to do with server-side prepared statements.
> I need prepared statements because I am writing large byte arrays (up to 50 MB) into the database, this is only possible and performant using server-side prepared statements (otherwise the Npgsql driver is filling a large amount of memory - hundreds of megabytes - with string objects when it constructs the SQL string on the client side, I have analyzed this with Microsoft's CLRProfiler).
> The problem lies in using server-side prepared statements with identity columns.
> These are the three properties that are relevant to the problem:
> PostgreSQL81Dialect.IdentitySelectString returns "select lastval()"
> PostgreSQL81Dialect.SupportsInsertSelectIdentity returns true (which tells NHibernate to issue the IdentitySelectString in the same command as the INSERT statement)
> NpgsqlDriver.SupportsPreparingCommands returns true (which enables NHibernate to use server-side prepared statements at all)
> If NHibernate issues an INSERT statement with identity columns it looks like this because of the above properties:
> INSERT INTO tab1(...);select lastval();
> This is no problem when server-side prepared statements are not used.
> But if server-side prepared statements are used, PostgreSQL returns an error which apparently was the reason why prepared statements were not enabled before on the NpgsqlDriver.
> This was the comment in NpgsqlDriver.cs with the same exception I am receiving:
> // NOTE: Npgsql1.0 and 2.0-preview apparently doesn't correctly support prepared commands.
> // The following exception is thrown on insert statements:
> // Npgsql.NpgsqlException : ERROR: 42601: cannot insert multiple commands into a prepared statement 
> I have successfully worked around this issue by changing the property PostgreSQL81Dialect.SupportsInsertSelectIdentity to return "false". In this case the "select lastval()" command was issued as a seperate command and everything worked fine.
> I think this problem is also closely related to the tickets NH-1316 and NH-2204, if those can be fixed with the suggested "returning id" syntax, this may not be an issue anymore.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira