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

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

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