var interval = new NpgsqlTypes.NpgsqlInterval(1, 2, 3, 4, 5);
var intervalFromTimespan = new NpgsqlTypes.NpgsqlInterval(new TimeSpan(1, 2, 3, 4, 5));
Console.WriteLine("Interval              :"+interval);
Console.WriteLine("Interval from timespan:" + intervalFromTimespan);

Output:

Interval              :1 day 02:03:04.005
Interval from timespan:26:03:04.005

First I thought that is 26 days :) then realized that it's just represented in hours... Can this be considered as bug?

Migrated from pgfoundry feature request #1011216

Right now, Npgsql will handle a RANGE type as a string, which is not ideal.

However, I'm not sure the best way to implement this as there's not really a native concept of a range type in .NET. It would probably be hacky to implement this as a two-element array.

Perhaps Npgsql can implement its own Range class which would contain an upper and lower value. This class would also need to support inclusive and exclusive bounds, as well as unbounded ranges.

A bonus feature would be to implement an implicit conversion between Range and String so that code can work with ranges exactly as you could in Postgres, for example:

Range foo = "[5,10]";

As well as operators to compare ranges with other instances.

Reference: http://www.postgresql.org/docs/devel/static/rangetypes.html

http://nuget.org/packages/Npgsql is still on 2.0.11

I have to convert an application from EF4 to EF6.
The application uses edmgen to generate the classes from a Postgres database.
The generated msl, csdl and ssdl seem to work, but the ObjectLayer.cs-file does only work after some editing as the namespaces are not right.
How can i get edmgen to work properly with EF6?

Dear,

I'm no more able to recompile my application after upgrading to your latest release. I discovered that the property 'ConstraintName' of class NpgsqlException doesn't exist any more. why you removed this very important property? I believe this is a bug as I can't find alternative way to get the viloated Constraint Name.

Thank you

I want to pass a list or a simpler collection as an argument to a query. In 2.0.14.3, it works. 2.1.0rc1 breaks my code. In 2.1.0rc1 does not accept List as value of parameters any more.

(Arrays do still work as arguments.)

The error message is "missing dimension value" or "missing assignment operator". I have seen both of them.

Here's my NUnit test:

using System;
using System.Collections.Generic;
using Npgsql;
using NUnit.Framework;

namespace TestsClientNunit
{
    [TestFixture]
    public class NpgsqlTest
    {
        private static readonly NpgsqlConnectionStringBuilder ConnectDetails = new NpgsqlConnectionStringBuilder() {
            Host = [..],
            Database = [..],
            UserName = [..],
            Password = [..]
        };

        public void ContainerTest(IEnumerable<string> container, int count) {
            using (var con = new Npgsql.NpgsqlConnection(ConnectDetails)) {
                con.Open();
                using (var cmd = con.CreateCommand()) {
                    cmd.CommandText = "select count(*) from unnest(:names)";
                    cmd.Parameters.AddWithValue("names", container);
                    Assert.AreEqual(
                        count,
                        Convert.ToInt32(cmd.ExecuteScalar()));
                }
            }
        }

        [Test]
        public void ArrayTest() {
            var data = new[] { "lalala", "lililili" };
            ContainerTest(data, data.Length);
        }

        [Test]
        public void ListTest() {
            var data = new List<string>();
            data.Add("one");
            data.Add("two");
            ContainerTest(data, data.Count);
        }

        [Test]
        public void CollectionTest() {
            var data = new Dictionary<string, string> { { "one", "ONE" }, { "two", "TWO" } };
            ContainerTest(data.Keys, data.Count);
        }

        static IEnumerable<string> Generate() {
            yield return "a";
            yield return "b";
            yield return "c";
        }

        [Test]
        public void EnumTest() {
            ContainerTest(Generate(), 3);
        }

        [Test]
        public void Complex() {
            using (var con = new Npgsql.NpgsqlConnection(ConnectDetails)) {
                con.Open();
                using (var cmd = con.CreateCommand()) {
                    cmd.CommandText = "CREATE temporary TABLE table4 (id integer NOT NULL, name text,  PRIMARY KEY (id))";
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = con.CreateCommand()) {
                    cmd.CommandText = "insert into table4 values (1, 'one'), (2, 'twoo')";
                    cmd.ExecuteNonQuery();
                }
                var ids = new List<int>(new[] { 2 });
                var names = new List<string>(new[]{"two"});
                using (var cmd = con.CreateCommand()) {
                    cmd.CommandText = "update table4 set (name) = (sub.newname) "
                        + " from (select unnest(@ids) as id, unnest(@names) as newname)  as sub "
                        + "where table1.id = sub.id";
                    cmd.Parameters.AddWithValue("ids", ids);
                    cmd.Parameters.AddWithValue("names", names);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

I'm using EF6. I'm doing INSERT and I see this in raw SQL:

INSERT INTO 
"public"."user" (
  "id",
  "firstname",
  "lastname",
  "is_admin"
 ) 
 VALUES (
   E'f3a6d52a-a179-4cfc-9c56-55ee03a471f4',
   E'Test',
   E'User',
   EFALSE
 )

Error:

-- Failed in 44 ms with error: ERROR: 42703: column "efalse" does not exist

is_admin is boolean field.

Using

• Npgsql.EF6.2.0.12-pre4\lib\net45
• EntityFramework.6.0.2\lib\net45

Using the new version from NuGet I get a strange error when try to update: all values has an 'E' prefix like:

insert into foo (x, y) values (E'text', ETrue)

When adding large strings via AddString you can get a buffer problem in AddString. MakeRoomForBytes does not allocate space in the buffer. This is because MakeRoomForBytes makes room for len bytes not len bytes + what is already in buffer.

Migrated from http://pgfoundry.org/tracker/?func=detail&atid=590&aid=1011341&group_id=1000140

Version is 2.0.12.0, not beta, but couldn't select this from the version drop-down.

I've got a table containing an integer array:

CREATE TABLE array_test
(
id integer NOT NULL DEFAULT nextval('"ArrayTest_ID_seq"'::regclass),
int_array integer[] NOT NULL,
CONSTRAINT array_test_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);

and some C# code where I insert a row into this table:

NpgsqlCommand insertArray = new NpgsqlCommand("INSERT INTO array_test (int_array) VALUES (:array_values)", this.connection);
insertArray.Parameters.Add(new NpgsqlParameter("array_values", NpgsqlDbType.Array | NpgsqlDbType.Integer));
insertArray.Parameters["array_values"].Value = new int[] { 1, 2, 3, 4, 5 };
insertArray.ExecuteNonQuery();

which works fine and stores a row like that:
1 | {1, 2, 3, 4, 5}

When I prepare the command before using it:

NpgsqlCommand insertArray = new NpgsqlCommand("INSERT INTO array_test (int_array) VALUES (:array_values)", this.connection);
insertArray.Parameters.Add(new NpgsqlParameter("array_values", NpgsqlDbType.Array | NpgsqlDbType.Integer));
insertArray.Prepare();
insertArray.Parameters["array_values"].Value = new int[] { 1, 2, 3, 4, 5 };
insertArray.ExecuteNonQuery();

the array is stored as a two-dimensional array instead:
2 | {{1, 2, 3, 4, 5}}

Npgsql can not determine parameter types for NpgsqlCommand.Prepare() --> error 22021 is fired.
Problem tested with PostgreSQL 9.3.2, VS2012, .NET 4.0.
This problem was introduced with commit de3e670 on Aug 09, 2013.
commit 48d7117 --> OK
commit de3e670 --> error

using Npgsql;
class Program
{
    static void Main(string[] args)
    {
        using (var con = new NpgsqlConnection("Server=localhost;User ID=npgsql_tests;Password=npgsql_tests;Database=npgsql_tests;syncnotification=false"))
        {
            con.Open();
            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = "SELECT CAST(@0 AS text)";
                var par = cmd.CreateParameter();
                par.ParameterName = "0";
                par.Value = 1;
                cmd.Parameters.Add(par);
                cmd.Prepare(); // exception fires error 22021: invalid byte sequence for encoding „UTF8“: 0x00
                cmd.ExecuteScalar();
            }
        }
    }
}

On code "iQueryable.Skip(n).Take(k)" I get error:
"ERROR: 42703: column Extent1.User_Id does not exist"
And other:
"ERROR: 42703: column Extent1.C1 does not exist"
I guess the similar problem is here:
http://pgfoundry.org/forum/forum.php?thread_id=15594&forum_id=519&group_id=1000140

Any fix?

More information:
//query of iQueryable
SELECT "Extent1"."Id" AS "Id","Extent1"."ContractInitiatedDate" AS "ContractInitiatedDate","Extent1"."ContractSentDate" AS "ContractSentDate","Extent1"."ContractSignedDate" AS "ContractSignedDate","Extent1"."UserId" AS "UserId","Extent1"."ResponsibleUserId" AS "ResponsibleUserId","Extent2"."Id" AS "Id1","Extent2"."Email" AS "Email","Extent2"."Phone" AS "Phone","Extent2"."Password" AS "Password","Extent2"."Firstname" AS "Firstname","Extent2"."Lastname" AS "Lastname","Extent2"."Position" AS "Position","Extent2"."Rights" AS "Rights","Extent2"."Ip" AS "Ip","Extent2"."LastActive" AS "LastActive","Extent2"."ProjectDatabaseId" AS "ProjectDatabaseId","Extent2"."CountryId" AS "CountryId","Extent2"."CarrierId" AS "CarrierId","Extent3"."Id" AS "Id2","Extent3"."Email" AS "Email1","Extent3"."Phone" AS "Phone1","Extent3"."Password" AS "Password1","Extent3"."Firstname" AS "Firstname1","Extent3"."Lastname" AS "Lastname1","Extent3"."Position" AS "Position1","Extent3"."Rights" AS "Rights1","Extent3"."Ip" AS "Ip1","Extent3"."LastActive" AS "LastActive1","Extent3"."ProjectDatabaseId" AS "ProjectDatabaseId1","Extent3"."CountryId" AS "CountryId1","Extent3"."CarrierId" AS "CarrierId1","Extent5"."Id" AS "Id3","Extent5"."Name" AS "Name","Extent5"."PhoneCode" AS "PhoneCode","Extent4"."Id" AS "Id4","Extent4"."Name" AS "Name1","Extent1"."User_Id" AS "User_Id","Extent1"."User_Id1" AS "User_Id1" FROM "public"."Prospect" AS "Extent1" INNER JOIN "public"."User" AS "Extent2" ON "Extent1"."ResponsibleUserId"="Extent2"."Id" INNER JOIN "public"."User" AS "Extent3" ON "Extent1"."UserId"="Extent3"."Id" INNER JOIN "public"."Carrier" AS "Extent4" ON "Extent3"."CarrierId"="Extent4"."Id" INNER JOIN "public"."Country" AS "Extent5" ON "Extent3"."CountryId"="Extent5"."Id" ORDER BY "Extent2"."Firstname" ASC ,"Extent2"."Lastname" ASC ,"Extent4"."Name" ASC
//error
[NpgsqlException (0x80004005): ERROR: 42703: column Extent1.User_Id does not exist] Npgsql.<ProcessBackendResponses_Ver_3>d__9.MoveNext() +4069 Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup) +1215 Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() +128 Npgsql.ForwardsOnlyDataReader.NextResultInternal() +89 Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription) +216 Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) +737 Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) +262 Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10 System.Data.Entity.Infrastructure.Interception.<>c__DisplayClassb.<Reader>b__8() +69 System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch(Func1 operation, TInterceptionContext interceptionContext, Action1 executing, Action1 executed) +93 System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +319 System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +240 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10 System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +97

//another query of iQueryable
SELECT "Project1"."Id" AS "Id","Project1"."Name" AS "Name","Project1"."C2" AS "C1","Project1"."Id1" AS "Id1","Project1"."Email" AS "Email","Project1"."Phone" AS "Phone","Project1"."Password" AS "Password","Project1"."Firstname" AS "Firstname","Project1"."Lastname" AS "Lastname","Project1"."Position" AS "Position","Project1"."Rights" AS "Rights","Project1"."Ip" AS "Ip","Project1"."LastActive" AS "LastActive","Project1"."ProjectDatabaseId" AS "ProjectDatabaseId","Project1"."CountryId" AS "CountryId","Project1"."CarrierId" AS "CarrierId","Project1"."Id2" AS "Id2","Project1"."Name1" AS "Name1" FROM (SELECT cast(FALSE as boolean) AS "C1","Extent1"."Id" AS "Id","Extent1"."Name" AS "Name","Extent2"."Id" AS "Id1","Extent2"."Email" AS "Email","Extent2"."Phone" AS "Phone","Extent2"."Password" AS "Password","Extent2"."Firstname" AS "Firstname","Extent2"."Lastname" AS "Lastname","Extent2"."Position" AS "Position","Extent2"."Rights" AS "Rights","Extent2"."Ip" AS "Ip","Extent2"."LastActive" AS "LastActive","Extent2"."ProjectDatabaseId" AS "ProjectDatabaseId","Extent2"."CountryId" AS "CountryId","Extent2"."CarrierId" AS "CarrierId","Extent3"."Id" AS "Id2","Extent3"."Name" AS "Name1", CASE WHEN ("Extent2"."Id" IS NULL ) THEN (CAST (NULL AS int4)) ELSE (1) END AS "C2" FROM "public"."ProjectDatabase" AS "Extent1" LEFT OUTER JOIN "public"."User" AS "Extent2" INNER JOIN "public"."Carrier" AS "Extent3" ON "Extent2"."CarrierId"="Extent3"."Id" ON "Extent1"."Id"="Extent2"."ProjectDatabaseId") AS "Project1" ORDER BY "Project1"."C1" ASC ,"Project1"."Id" ASC ,"Project1"."C2" ASC
//error
[NpgsqlException (0x80004005): ERROR: 42703: column Extent1.C1 does not exist] Npgsql.<ProcessBackendResponses_Ver_3>d__9.MoveNext() +4069 Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(Boolean cleanup) +1215 Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() +128 Npgsql.ForwardsOnlyDataReader.NextResultInternal() +89 Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean preparedStatement, NpgsqlRowDescription rowDescription) +216 Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) +737 Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) +262 Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +41 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10 System.Data.Entity.Infrastructure.Interception.<>c__DisplayClassb.<Reader>b__8() +69 System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch(Func1 operation, TInterceptionContext interceptionContext, Action1 executing, Action1 executed) +93 System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +319 System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +240 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10 System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +97

On these iQueryable I do "iQueryable.Skip(n).Take(k)" and get errors.

I am using Npgsql 2.1.0-beta1 + Npgsql.EntityFramework

Hi,

I'm trying the following:

using (var conn = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Database=test;User Id=;Password=;"))
using (var com = new NpgsqlCommand("select lastmodfied from passwords", conn))
{
    conn.Open();
    var reader = com.ExecuteReader();

    while (reader.Read())
    {
        var date = (DateTimeOffset)reader.GetValue(0);
    }
}

however its returning a DateTime. How do I get a DateTime Offset from a TimestampTZ?

Cheers,
Adam

See previous discussion: jjchiw@af0548a#commitcomment-4923431

While using EF6 with Kendo UI grids, I'm getting an issue when sorting by one field and grouping by another field. I can't see the linq that is being created, as Kendo is not open source, but I have traced the issue down to Npgsql. This issue does not happen with devart's driver.

This issue occurs when I sort by one column and try to group by a different column. The error is "column "Extent1.name" must appear in the GROUP BY clause or be used in an aggregate function"

In my example, I'm trying to sort by name and group by organizationtypename

Here are some snippets of the outputted SQL causing the issue.
The outputted SQL in Npgsql is:
SELECT "Extent1"."organizationtypename" AS "K1"
FROM "link"."organizationgrid" AS "Extent1"
GROUP BY "Extent1"."organizationtypename"
ORDER BY "Extent1"."organizationtypename" ASC ,"Extent1"."name" ASC LIMIT 100) AS "GroupBy1"

While using dotConnect the SQL is:
SELECT "Limit1".organizationtypename AS "K1"
FROM ( SELECT
"Extent1".organizationid,
"Extent1"."name",
"Extent1".city,
"Extent1".organizationtypename,
"Extent1"."state",
"Extent1".country
FROM link.organizationgrid AS "Extent1"
ORDER BY "Extent1".organizationtypename ASC, "Extent1"."name" ASC
LIMIT 100
) AS "Limit1"
GROUP BY "Limit1".organizationtypename ) AS "GroupBy1"

I'm not sure if the issue is there is no DefiningQuery, so it is created a different (wrong) ScanExpression.

When we use the latest Npgsql with a parameter to insert a row that has a "'" character in a column of the XML data type we're getting an error where it appears the "'" character isn't properly quoted.

I'm guessing the problem is here:

https://github.com/npgsql/Npgsql/blob/master/Npgsql/NpgsqlTypes/NpgsqlTypesHelper.cs#L442

where the line that reads

  nativeTypeMapping.AddType("xml", NpgsqlDbType.Xml, DbType.Xml, true);

probably also needs some conversion function defined, like possibly this:

   nativeTypeMapping.AddType("xml", NpgsqlDbType.Xml, DbType.Xml, true,
                   BasicNativeToBackendTypeConverter.StringToTextText);

but I couldn't figure out how to compile npgsql under mono so can't figure out how to test it. (the old instructions that suggest running "nant tests" aren't working for me, and I didn't find new ones)

Anyway --- here's a small test program that shows the problem.

It assumes a table where the PrecisionXML column is of the postgres XML type.

Thanks,

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;
using System.Text;

using Npgsql;

namespace npgsqltest
{
    class Program
    {
        static void Main(string[] args)
        {
            string sXML = null;
            Int32 iPrimaryKey = -1;
            string sSQL = null;
            string sErr = null;
            DbConnection conn = null;
            DbCommand cmd = null;
            DbParameter prm = null;
            DbDataAdapter da = null;
            string sConnStr = "server=#.#.#.#; port=####; database=mydb; user id=myid; password=mypwd; Pooling=True;MinPoolSize=1;MaxPoolSize=5";

            conn = new NpgsqlConnection(sConnStr);
            conn.Open();

            cmd = new NpgsqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = System.Data.CommandType.Text;

            da = new NpgsqlDataAdapter();
            da.SelectCommand = cmd;

            prm = cmd.CreateParameter();
            prm.DbType = DbType.String;
            prm.Size = 200;
            prm.ParameterName = "@LocationFullText";
            prm.Value = "somelocationfulltextvalue";
            cmd.Parameters.Add(prm);

            sXML = "<?xml version=\"1.0\" encoding=\"UTF-8\"?> <strings type=\"array\"> <string> this is a test with ' single quote </string></strings>";
            prm = cmd.CreateParameter();
            prm.DbType = DbType.Xml;  // To make it work we need to use DbType.String; and then CAST it in the sSQL: cast(@PrecisionXML as xml)
            prm.ParameterName = "@PrecisionXML";
            prm.Value = sXML;
            cmd.Parameters.Add(prm);

            sSQL = "insert into GlobalLocation(LocationFullText, PrecisionXML, xcoordinate, ycoordinate)" +
                    " values(@LocationFullText, @PrecisionXML, -1, -1)" +
                    " returning GlobalLocationID";

            cmd.CommandText = sSQL;
            iPrimaryKey = (int)Convert.ToInt32(cmd.ExecuteScalar());

            conn.Close();
            conn.Dispose();
            cmd.Dispose();
            da.Dispose();
        }
    }
}

npgsql 2.1 RC + EF6

SSL no work
Protocol=3;SSL=true;SslMode=Require;

An unhandled exception of type 'System.Data.Entity.Core.ProviderIncompatibleException' occurred in EntityFramework.dll

Additional information: An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct.

InnerException:
{"The provider did not return a ProviderManifestToken string."}
{"Failed to establish a connection to 'my host...'."}

This test demonstrates shifting of DateTime fields for -X hours where X is the current time zone.

[Test]
public void DateTimeIssue()
{
    var createdDate = DateTime.Now;
    var id = new Guid("AB51DED2-0264-4C32-B755-27CA15214A8E");

    using (var db = Db.GetContext()) // Db.GetContext() returns instance of db context
    {
        var r = new Report { CreateDate = createdDate, Id = id };
        db.Reports.Add(r);
        db.SaveChanges();
    }

    using (var db = Db.GetContext())
    {
        var r = db.Reports.Find(id);
        db.AttachAsModified(r); // this method will be described below
        db.SaveChanges();
    }

    using (var db = Db.GetContext())
    {
        var r = db.Reports.Find(id);

        var newDate = r.CreateDate;
        Assert.AreEqual(createdDate, newDate.ToLocalTime()); // ToLocalTime because I send local time but recive utc time
    }
}

AttachAsModified definition:

        public static TEntity AttachAsModified<TEntity>(this IIbsscDbContext ctx, TEntity entity)
            where TEntity : BaseEntity
        {
            ctx.Set<TEntity>().Attach(entity); // Same as DbContext.Set
            ctx.Entry(entity).State = EntityState.Modified; // Same as DbContext.Entry(...)
            return entity;
        }

Ok, I know that Attach here is not needed. But this code reveals buggy spot with processing DateTime with time zones, I think.

And maybe, when time zone in db entry and client local time zone are equal, ORM shoud return DateTime in DateTimeKind.Local form? Now it always have Utc form.

versions:

<package id="EntityFramework" version="5.0.0" targetFramework="net45" />
   <package id="Npgsql" version="2.1.0-beta1" targetFramework="net45" />
  <package id="Npgsql.EntityFrameworkLegacy" version="2.1.0-beta1" targetFramework="net45" />
  <package id="NUnit" version="2.6.3" targetFramework="net45" />

CREATE TABLE "Reports"
(
  "Id" uuid NOT NULL,
  "CreateDate" timestamp with time zone,
...
)

Using EF the Npgsql generates an invalid insert command when the table have only one column and this column is the primary key with auto-generation:

{create table Participant ( id serial, primary key(id) );}

The correct insert statement is:

insert into Participant values (default)

But the generated statement is:

insert into Participant() values()

This statement is not a valid PostgreSQL command.

Akos Lukacs (akoslukacs)

Hi!

The policy.2.0.Npgsql.dll file latest download, and the nuget package for .net 3.5 is built against .NET 4.0, therefore it can't be loaded in a .NET 3.5 app.

Is this file required? Actually if I delete this assembly reference, the application runs as far as I can tell.

Date: 2013-12-17 14:00
Sender: Richard Brown

The latest NuGet package (2.0.14.2) still appears to have this problem:

C:>corflags packages\Npgsql.2.0.14.2\lib\net35\policy.2.0.Npgsql.dll
Microsoft (R) .NET Framework CorFlags Conversion Tool. Version 4.0.30319.1
Copyright (c) Microsoft Corporation. All rights reserved.

Version : v4.0.30319
CLR Header: 2.5
PE : PE32
CorFlags : 9
ILONLY : 1
32BIT : 0
Signed : 1

Date: 2013-06-28 14:56
Sender: Francisco Figueiredo jr.

This is fixed in github. See this pull request with the patch:
#24

We need an EXE installer that seamlessly installs Npgsql and its dependencies into the GAC.

Add an optional checkbox for the publisher policy as well.

Relevant issues:

• #781
• #870
• #731
• #513
• #634
• #1163
• #1208

Hello,

I am specifically working with the following cofiguration:
Npgsql 2.0.14.3
PostgreSQL 9.3
Entity Framework 4.3.1

I have the following table:

CREATE TABLE tbltest
(
name text,
id integer NOT NULL DEFAULT nextval('tbl2_id_seq1'::regclass),
CONSTRAINT tbltest_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE tbltest
OWNER TO postgres;

The field "id" is not defined as a SERIAL but as an integer, with a default column value, which is nextval() on a sequence.

The respective EF class for accessing the table is:

[Table("tbltest", Schema="public")]
public class tbltest
{
public string name { get; set; }

//Primary key and also identity field (this field is supposed to be automatically set by the database on inserts)
[Key, DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int id { get; set; }
}

Reading from the table through the EF class works fine, but then it comes to INSERTs, an exception is thrown.
This is the code that attempts the insert:

using (var db = new DataContext())
{
var i = db.tbltest.Create();
i.name = "George";
db.tbltest.Add(i);
db.SaveChanges();
}

Specifically, I get the error:

Unhandled Exception: System.Data.Entity.Infrastructure.DbUpdateException: A null store-generated val
ue was returned for a non-nullable member 'id' of type 'DataModel.tbltest'. ---> System.Data.UpdateE
xception: A null store-generated value was returned for a non-nullable member 'id' of type 'DataMode
l.tbltest'.
at System.Data.Mapping.Update.Internal.UpdateTranslator.AlignReturnValue(Object value, EdmMember
member, PropagatorResult context)
at System.Data.Mapping.Update.Internal.UpdateTranslator.SetServerGenValue(PropagatorResult contex
t, Object value)
at System.Data.Mapping.Update.Internal.UpdateTranslator.BackPropagateServerGen(List`1 generatedVa
lues)
at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager,
IEntityAdapter adapter)
at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Entity.Internal.InternalContext.SaveChanges()
--- End of inner exception stack trace ---
at System.Data.Entity.Internal.InternalContext.SaveChanges()
at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
at System.Data.Entity.DbContext.SaveChanges()

This means that the Microsoft entity framework classes were expecting a value to be set from the database (through Npgsql) for the identity field "id", but it was not set.

The Npgsql generated INSERT SQL command was actually the following:
"INSERT INTO "public"."tbltest"("name") VALUES ('George');SELECT currval(pg_get_serial_sequence('"public"."tbltest"', 'id')) AS "id""

Npgsql assumes that there is an associated Postgres sequence associated on column "id" since the DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity) attribute was defined on class property "id" of the respective entity class. But this may not be always the case! Sometimes identity fields are declared as plain integer fields but with associated default values, which are calling a sequence instead of explicitly assigning a sequence to them (the Postgres SERIAL type column). There is sometimes a good reason for doing so, as the same sequence needs to be sometimes shared amongst many tables.

Therefore to cover those cases as well, the INSERT command should not be trying to query the column's associated "sequence" property for returning the column's database associated value after the insert, but it should be using the RETURNING statement instead. So equivalent generated command can be:

"INSERT INTO "public"."tbltest"("name") VALUES ('George 9/2/2014 12:41:52 πμ') RETURNING id AS "id""

This will effectively work with identity-type columns that are EITHER explicitly defined as SERIAL (which in Postgres is an integer column with an explicitly associated sequence) OR with columns that defined as integer and have a default value, which is allocated through a sequence.

In order to achieve the above functionality, I patched the following lines in the source code:

In file Npgsql\SqlGenerators\SqlInsertGenerator.cs around line 56:

Original code:
return new LiteralExpression("currval(pg_get_serial_sequence('" + tableName + "', '" + expression.Property.Name + "'))");

Replaced with:
return new LiteralExpression(expression.Property.Name);

And in file Npgsql\SqlGenerators\VisitedExpression.cs around line 266:

Original code:
if (ReturningExpression != null)
{
sqlText.Append(";");
ReturningExpression.WriteSql(sqlText);
}

Replaced with:
if (ReturningExpression != null)
{
var sb = new StringBuilder();
ReturningExpression.WriteSql(sb);
string returning = sb.ToString();
returning = " RETURNING" + returning.Substring("SELECT".Length);
sqlText.Append(returning);
}

This is certainly a workaround and not a solid fix, but maybe you could consider this case.

Many thanks,
George.

I'm trying the following:

foreach(bool bit in new BitString(true, 33))
{
     Console.WriteLine(bit);
}

this printing a "true, false, false, false, ..." instead "true, true, true, ..."

From the Nuget Package 'Npgsql 2.1.0-beta1'

Repro steps:

NpgsqlConnectionStringBuilder builder = new NpgsqlConnectionStringBuilder();
builder.ApplicationName = "test";

Throws exception with: The given key was not present in the dictionary.

stack trace:
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Npgsql.NpgsqlConnectionStringBuilder.SetValue(String keyword, Keywords key, Object value)
at Npgsql.NpgsqlConnectionStringBuilder.set_ApplicationName(String value)

looking at the static NpgsqlConnectionStringBuilder() it seems that the Keywords.ApplicationName enum value is omitted from being added to valueDescriptions.

The newer binary protocol 3 has been supported since Postgresql 7.4. To simplify our code base and not be kept back, remove all traces of protocol 2.

Dear Team,

I was using the version 2.0.13 and I was able to use "SELECT" with Npgsqlcommand.Parameters as following:

command.Parameters.Select(s => new { N = s.ParameterName, V = s.Value })

When I upgraded to 2.1, I'm no more able to compile my code. I'm getting a compilation error with the above statement.

Please take this issue into consideration before releasing the coming version.

Hope the final release 2.1 is going to production soon.

Best Regards,

Usama Farag

It seems that Amazon redshift does not support some of the functionality expected by postgres connections. When connecting to an SSL enabled cluster with the 2.1.0.beta1 driver the following error occurs:

permission denied to set parameter "lc_monetary" to "C"

I would like to request a feature whereby a connection string option can be set to specify that the connection is to redshift and subsequently decisions can be made in the code to avoid certain nuances with the implementation of the postgres-like schema.

SSPI Authentication does not work.

Steps:
I have set up a windows domain trying to use SSPI.
In the same server, it is no problem.
It is a problem when divided into AP server and DB server.

Step1.
AP Server and DB Server are set as the same domain.

Step2.
Windows firewall on DB Server setup.
It enables it to connect the port of PostgreSQL to DB Server from AP Server.

Step3.
The login roll of the same name as the login user of Windows is added to the login roll of PostgreSQL on DB Server.

Step4.
Method of pg_hba.conf of PostgreSQL of DB Server is changed into SSPI.
host all all 0.0.0.0/0 sspi
host all all ::1/0 sspi

Step5.
I run my .NET client program on AP server.

NpgsqlConnection conn = new NpgsqlConnection("Server=APserver;Port=5432;Database=postgres; Integrated Security= true;");
conn.Open();
NpgsqlCommand command = conn.CreateCommand();
command.CommandText = "select * from pg_locks";
command.ExecuteScalar();
conn.Close();

Result:
pg_log file
FATAL: could not accept SSPI security context
Detail:The token supplied to the function is invalid
(80090308)

When running client program on DB Server, it has connected using SSPI.
When using md5, id and password were set up and it has connected from AP Server.

possible solution:
SSPI connect does not work between npgsql client and postgres server in a domain.
Whereas it works with command line psql or a Qt application (using libpq).

Capturing network traffic reveals something interesting:

works psql

2014-01-31 06:40:16 PST DEBUG: Processing received SSPI token of length 40
2014-01-31 06:40:16 PST DEBUG: sending SSPI response token of length 264
2014-01-31 06:40:16 PST DEBUG: sending GSS token of length 264
2014-01-31 06:40:16 PST DEBUG: SSPI continue needed
2014-01-31 06:40:16 PST DEBUG: Processing received SSPI token of length 562
2014-01-31 06:40:16 PST LOG: connection authorized: user=mynewuser database=mdb

not work npgsql

2014-01-31 06:35:10 PST DEBUG: Processing received SSPI token of length 41
2014-01-31 06:35:10 PST DEBUG: sending SSPI response token of length 264
2014-01-31 06:35:10 PST DEBUG: sending GSS token of length 264
2014-01-31 06:35:10 PST DEBUG: SSPI continue needed
2014-01-31 06:35:10 PST DEBUG: Processing received SSPI token of length 563
2014-01-31 06:35:10 PST FATAL: could not accept SSPI security context
2014-01-31 06:35:10 PST DETAIL: The token supplied to the function is invalid

(80090308)

works qt

2014-01-31 06:47:18 PST DEBUG: Processing received SSPI token of length 40
2014-01-31 06:47:18 PST DEBUG: sending SSPI response token of length 264
2014-01-31 06:47:18 PST DEBUG: sending GSS token of length 264
2014-01-31 06:47:18 PST DEBUG: SSPI continue needed
2014-01-31 06:47:18 PST DEBUG: Processing received SSPI token of length 562

2014-01-31 06:47:18 PST LOG: connection authorized: user=mynewuser database=mdb

Looks like npgsql is sending an extra char.

I checked the code (2.0.14.3) and made two changes to remove the extra char:

NpgsqlPasswordPacket.cs: function WriteToStream:
case ProtocolVersion.Version3:
outputStream.WriteByte((Byte) 'p');
/// PGUtil.WriteInt32(outputStream, 4 + password.Length + 1);
PGUtil.WriteInt32(outputStream, 4 + password.Length);

PgUtil.cs: function writeBytes
network_stream.Write(the_bytes, 0, the_bytes.Length);
// comment this out
// network_stream.Write(new byte[1], 0, 1);

After that sspi worked.

Hi,

I noticed that NpgsqlConnection.GetSchema will, for some cases, create a new NpgsqlConnection using the same connection string. In these cases, the new connection is never disposed. I think these connections should be wrapped in using block to prevent this case.

Mathieu

I would like to request an Npgsql Nuget release including the Boolean fix from SHA: f8a82fa
This would help me replace our own patched version with a clean upstream NuGet package.

PG 9.3 isn't happy with Npgsql sending the "SET statement_timeout" command ahead of the ROLLBACK command when there's an aborted transaction (say, when there's a concurrency issue). The PG log says:

ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SET statement_timeout = 20000

This happens even when you use the NpgsqlTransaction class; the Rollback() method calls NpgsqlCommand.ExecuteBlind, which implicitly always sends a timeout command.

This means running Postgresql again, totally separate from the Postgresql instances used for running tests (as these are destroyed/tweaked)

Npgsql sends parameter values with type casting depending on the type of the parameter. This is done to avoid possible ambiguities in parameters values. One example of this ambiguity is show in the test case:

[Test]
        public void AmbiguousFunctionParameterType()
        {
            ExecuteNonQuery(@"CREATE OR REPLACE FUNCTION ambiguousParameterType(int2, int4, int8, text, varchar(10), char(5)) returns int4 as '
                                select 4 as result;
                              ' language 'sql'");
            //NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
            NpgsqlCommand command = new NpgsqlCommand("ambiguousParameterType(:a, :b, :c, :d, :e, :f)", Conn);
            command.CommandType = CommandType.StoredProcedure;
            NpgsqlParameter p = new NpgsqlParameter("a", DbType.Int16);
            p.Value = 2;
            command.Parameters.Add(p);
            p = new NpgsqlParameter("b", DbType.Int32);
            p.Value = 2;
            command.Parameters.Add(p);
            p = new NpgsqlParameter("c", DbType.Int64);
            p.Value = 2;
            command.Parameters.Add(p);
            p = new NpgsqlParameter("d", DbType.String);
            p.Value = "a";
            command.Parameters.Add(p);
            p = new NpgsqlParameter("e", NpgsqlDbType.Char);
            p.Value = "a";
            command.Parameters.Add(p);
            p = new NpgsqlParameter("f", NpgsqlDbType.Varchar);
            p.Value = "a";
            command.Parameters.Add(p);

            command.ExecuteScalar();
        }

If the parameter cast isn't specified, Postgresql complains that it can't find the function because the types of the parameters are int2, int4 and int8.

On the other side, most of the time the user doesn't want this cast to be applied as it may cause problems and there is no easy way to make Npgsql not add those casts (unless an undocumented feature of setting the parameter dbtype to DbType.Object.

In a recent discussion: #124 (comment) the user MrJul tried to send a query and it returned an error because Npgsql added an explicit cast to text to one of the parameters.

Although the error message is correct because the first parameter of the convert function is of type bytea and not text, if Npgsql hadn't sent the type cast, Postgresql would infer the type and would execute the query without errors.

We would need to check when would be really needed to send the type cast in order to avoid this type of problem.

It's OK to build with MS .NET, but the whole point is to run the unit tests with mono...!

versions:

  <package id="EntityFramework" version="5.0.0" targetFramework="net45" />
  <package id="Npgsql" version="2.1.0-beta1" targetFramework="net45" />
  <package id="Npgsql.EntityFrameworkLegacy" version="2.1.0-beta1" targetFramework="net45" /> 

I have found some strange bahaviour, but have no time to check it properly.
For now I know following:
Wen I changed type of fields from DateTime to DateTimeOffset, new exception is appeared while execiting next linq query:

{
                List<Guid> departmentIds =
                    context.Departments.Where(d => d.ParentDepartmentId == someParam).Select(d => d.Id).ToList();

                List<Report> reports =  context.Reports
                        .Where(x => departmentIds.Contains(x.DepartmentId.Value))
                        .ToList(); // exception here!
}

When all fields in Report model are DateTime - all works fine, but when I replace one of them to DateTimeOffset - exception.

exception:

[NotSupportedException: Указанный метод не поддерживается.]
   Npgsql.SqlGenerators.SqlBaseGenerator.GetDbType(EdmType edmType) +312
   Npgsql.SqlGenerators.SqlSelectGenerator.Visit(DbNullExpression expression) +114
   System.Data.Common.CommandTrees.DbNullExpression.Accept(DbExpressionVisitor`1 visitor) +24
   Npgsql.SqlGenerators.SqlBaseGenerator.Visit(DbNewInstanceExpression expression) +360
   System.Data.Common.CommandTrees.DbNewInstanceExpression.Accept(DbExpressionVisitor`1 visitor) +25
   Npgsql.SqlGenerators.SqlBaseGenerator.Visit(DbProjectExpression expression) +91
   System.Data.Common.CommandTrees.DbProjectExpression.Accept(DbExpressionVisitor`1 visitor) +25
   Npgsql.SqlGenerators.SqlSelectGenerator.BuildCommand(DbCommand command) +114
   Npgsql.NpgsqlServices.TranslateCommandTree(DbCommandTree commandTree, DbCommand command) +410
   Npgsql.NpgsqlServices.CreateDbCommand(DbCommandTree commandTree) +349
   Npgsql.NpgsqlServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) +54
   System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree) +125
   System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) +442

[EntityCommandCompilationException: При подготовке определения команды произошла ошибка. Подробные сведения см. во внутреннем исключении.]
   System.Data.EntityClient.EntityCommandDefinition..ctor(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) +1406
   System.Data.EntityClient.EntityProviderServices.CreateCommandDefinition(DbProviderFactory storeProviderFactory, DbCommandTree commandTree) +78
   System.Data.EntityClient.EntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree) +159
   System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree) +125
   System.Data.Objects.Internal.ObjectQueryExecutionPlan.Prepare(ObjectContext context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption, Span span, ReadOnlyCollection`1 compiledQueryParameters, AliasGenerator aliasGenerator) +382
   System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption) +736
   System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) +131
   System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() +36
   System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator() +126
   System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator() +99
   System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +369
   System.Linq.Enumerable.ToList(IEnumerable`1 source) +58
   Ru.Rosavtodor.Sc.RepCollect.Bl.Services.ReportService.GetChildDepartmentDocuments(Guid parentDepartmentId, Nullable`1 reportDate) in d:\Dev\IBSSC\RepCollect\Sc.Bl\Services\ReportService.cs:48
   Ru.Rosavtodor.Sc.RepCollect.Web.Controllers.HomeController.GetDashboardViewModel() in d:\Dev\IBSSC\RepCollect\Sc.Web\Controllers\HomeController.cs:41
   Ru.Rosavtodor.Sc.RepCollect.Web.Controllers.HomeController.Index() in d:\Dev\IBSSC\RepCollect\Sc.Web\Controllers\HomeController.cs:26
   lambda_method(Closure , ControllerBase , Object[] ) +101
   System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
   System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +211
   System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27
   System.Web.Mvc.Async.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() +28
   System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +48
   System.Web.Mvc.Async.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() +57
   System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223
   System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__36(IAsyncResult asyncResult) +10
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +48
   System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20() +24
   System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult) +102
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57
   System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +43
   System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__18(IAsyncResult asyncResult) +14
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +57
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +47
   System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
   System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__3(IAsyncResult asyncResult) +25
   System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23
   System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62
   System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +47
   System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9628972
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155

I'm writing this issue mainly to keep track of this problem and make users of "Microsoft.AspNet.Identity.EntityFramework nuget package" aware of this since they are most likely to run into this(as I did)... Which is used for MVC login data storing...

This is mostly EF bug: http://entityframework.codeplex.com/workitem/371
But it seems like they won't fix this until EF7 problem is in this file
http://entityframework.codeplex.com/SourceControl/latest#src/EntityFramework/Internal/DatabaseTableChecker.cs as it's missing "Npgsql" in switch case and no possibility to define checker from outside...

Workaround that I used was to set different database name for DbContext of Identity and since database was non existing tables were created... But if same database is used as for "BloggingContext" it assumes that tables are already created and skips creation resulting in errors later...

Hello,

I have to deal with a PostgreSQL database I've no control over, where the encoding is set to SQL_ASCII. Every client of this database is expected to encode text values in Latin-1. This sucks, I know, Unicode exists for a thing...

Well, that's my unfortunate requirements, and I can't meet them with Npgsql. All strings are always UTF-8 encoded (believe me, I understand this is normally a good thing) and there seems to be no way to change that.

There is a now obsolete connection parameter Encoding, so I suppose support for this has been willingly dropped. I tried to use byte arrays parameters instead of strings or changing the DbType, but the bytes get escaped and the resulting strings aren't correct.

Before I fork my own Npgsql with Latin-1 encoding just for this terrible database, is there any way I can currently make this work? Did I miss something obvious? Is some support planned?

Thanks.

I am currently trying to get NHibernate to work with JSON data type. I am currently getting stuck on ERROR: 42804: column "post" is of type json but expression is of type text - I have used Npgsql directly and it works just fine. I am thinking that step one might be to add Json to NpgsqlDbType but after that I am not 100% sure where to go.

On korean windows XP machine using npgsql drivier from 2.0.12 and higher I get an
The type initializer for 'NpgsqlTypes.NpgsqlTypesHelper' threw an exception
error

This only happens on XP machine, windows 7 works fine.

    public string Connect(string server
                          , string port
                          , string user
                          , string password
                          , string database)
    {
        string connectStr =
              "Server=" + server +
              ";Port=" + port +
              ";User Id=" + user +
              ";Password=" + password +
              ";Encoding=UNICODE" +
              ";Timeout=15" +
              ";CommandTimeout=300" +
              ";Database=" + database + ";";

        try
        {
            Wizard.Connection = new NpgsqlConnection(connectStr);
            Wizard.Connection.Open();
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
        return "";
    }

Hello,

I recently installed a VB.NET application which uses Npgsql to connect to a LATIN9-encoded database in a PostgreSQL 8.2.7 server.
Ocasionally, Npgsql signals the occurrence of error 08P01, 'invalid message format' while reading the rows produced by the following function:

create type udttappxmbtmp as
(
nlinha int4 ,
slinha text
);

create or replace function tappxmbtmpobterdeniiddth_setof( pniiddth int4, pnmododecomunicacao int4 ) returns setof udttappxmbtmp as
$$
-- 003.2014.02.23.20.14
-- pnmododecomunicacao 1=Teste, 2=Producao
declare
rdat udttappxmbtmp;
begin
--
perform tappxmbtmppreencher( pniiddth, pnmododecomunicacao );
--
for rdat in
select *
from tappxmbtmp
order by nlinha
loop
raise notice 'L=%, T=%', rdat.nlinha, rdat.slinha;
return next rdat;
end loop;
--
return;
end;
$$
language plpgsql volatile security definer;
grant execute on function tappxmbtmpobterdeniiddth_setof( pniiddth int4, pnmododecomunicacao int4 ) to operador;

The returned set is read in the application by a NpgsqlDataReader.
The 'slinha' text column never carries more than three hundred characters.
The returned set contains less than 100 rows.
The function is being called some 20 to 30 times a day, and at least once a day the error 08P01 is being raised..
In the database log, the 'raise notice' command shows that all the expected rows are always produced.
When the error occurs, it also appears in the database log, immediately after the log of the last row in the set.
If the application is run again with the exact same parameters, it usually obtains an identical set without any error being raised, which further complicates the problem, as there's no immediately detectable pattern on the conditions that cause the error.

I just created a backup of the database, created a new database with UTF8 encoding and restored the backup, expecting that, a few hours from now, I'll be able to know if the error is unrelated with the database encoding.

Following this step, I'm not sure about the best way to effectively isolate the error.
Any suggestions ?

Thanks,

Helder

When i use NpgsqlCommandBuilder with NpgsqlDataAdapter i always want to configure update,insert,delete commands. Earlier when i used Npgsql 2.0.1.0 version this code works fine:

DataTable t = new DataTable();            
NpgsqlConnection con = new NpgsqlConnection("some connection string");
con.Open();
NpgsqlDataAdapter adp = new NpgsqlDataAdapter("select id,description from device", con); //where id is primary key
adp.Fill(t);
NpgsqlCommandBuilder bld = new NpgsqlCommandBuilder(adp);
DataRow r = t.NewRow();
r[0] = 300001;
t.Rows.Add(r);
DataRow[] rows = new DataRow[1];
rows[0] = r;
adp.Update(rows);
con.Close();

Now with Npgsql 2.0.14.3 this code doesn`t work and all our projects must be changed because we have

InvalidOperationException

Now we must everywhere where we use Adapter and Builder before adp.Update() do this:

adp.InsertCommand = bld.GetInsertCommand();
adp.UpdateCommand = bld.GetUpdateCommand();
adp.DeleteCommand = bld.GetDeleteCommand();

Executing this code:

    using (var context = new MyDbContext())
    {
        var w = new Worker {Id = 1, Name = @"aaa\bbb"};
        context.Workers.Add(w);
        context.SaveChanges();
    }

will result new entry in Workers table and entry will have name "aaa\bbb"

I expect "aaa\bbb".

versions:
PostgreSQL 9.3.2, compiled by Visual C++ build 1600, 64-bit
EntityFramework v5

command "SHOW standard_conforming_strings" returns "on"

Hello,
I Have a probleme with npgsql and entity Framework, when I build my EF Schémas, all work fine.
But in the database, there is many view who contain "." Under column name and when I try to open view, npgsql say me error SYNTAX ERROR near ".".

I use npgsql v2.0.12.0 and EF v4.0
Before this, the software use the same npgsql version but with EF v3.5 (edm version 1), and its where working well.

Thanks in advance.

Olivier MATIAS

We already use the .NET framework SSL implementation, but Mono.Security has been kept for backwards compatibility.

When bumping versions, we currently need to do so:

• In CommonAssemblyInfo.cs
• In the nuspec files, both in the version and in the EntityFramework->Npgsql dependency version
• In the publisher policy file

Find some way of automating this better

In this case for example:

using(var c = new NpgsqlConnection("Server=127.0.0.1;Database=XXX;User Id=YYY;Password=ZZZ;AlwaysPrepare=True"))
{
c.Open();

using(NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = c;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_test";
cmd.Parameters.Add("@ttt1", NpgsqlDbType.Integer).Value = "1";
cmd.Parameters.Add("@ttt2", NpgsqlDbType.Integer).Value = "2";
cmd.ExecuteNonQuery();
}
}

HI there,

I am trying to recompile this driver, but the tests are failing. I tested against a 8.4, 9.0 and 9.2 server, I always have some that are failing.
Maybe I am missing something.
I use VS2010 solution, clean the db and regenerate it everytime, but I still have some tests failing.
Thanks.

NpgsqlCommand supports Prepare() and I think it should execute "DEALLOCATE " in its Dispose() method.
Currently there is no NpgsqlCommand.Dispose() method at all.
Preparing of many commands will pump up the server process memory and closing the connection afterwards takes very long!
Below is an example program...
(It was tested with Win7/64bit, PostgreSQL 9.2.6, .NET 4.0, VS2012, Npgsql/master current commit 6407cc3.)
In my case disposing of connection takes 14s. But with DEALLOCATE work around it takes only 2ms.

using Npgsql;
using System;
using System.Diagnostics;
class Program
{
    static void Main(string[] args)
    {
        var sw = Stopwatch.StartNew();
        using (var con = new NpgsqlConnection("Server=localhost;User ID=npgsql_tests;Password=npgsql_tests;Database=npgsql_tests;syncnotification=false"))
        {
            con.Open();
            for (int i = 0; i < 30000; i++)
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT 0";
                    cmd.Prepare();
                    cmd.ExecuteScalar();
                }
                // activating this DEALLOCATE command works around missing deallocation of prepared command
                /*
                using (var cmd = con.CreateCommand())
                {
                    cmd.CommandText = "DEALLOCATE ALL";
                    cmd.ExecuteNonQuery();
                }
                */
            }
            Console.WriteLine(sw.Elapsed);
            sw.Restart();
        }
        Console.WriteLine(sw.Elapsed);
        Console.ReadLine();
    }
}