[#1011005] using 2 NpgsqlCopyIn at the same time

[franciscojunior]

From: http://pgfoundry.org/tracker/?func=detail&atid=590&aid=1011005&group_id=1000140

if an application using two or more NpgsqlCopyIn at the same time and it upload data to tables with different number of columns npsql raise exception ERROR: 22P04: extra data after last expected column

sample code:

var cb = new NpgsqlConnectionStringBuilder("host=127.0.0.1;User Id=postgres;Password=xxxxxx;database=temp");

var connection1 = new NpgsqlConnection(cb.ToString());
var connection2 = new NpgsqlConnection(cb.ToString());

connection1.Open();
connection2.Open();

var copy1 = new NpgsqlCopyIn("COPY table1 FROM STDIN;", connection1);
var copy2 = new NpgsqlCopyIn("COPY table2 FROM STDIN;", connection2);

copy1.Start();
copy2.Start();

NpgsqlCopySerializer cs1 = new NpgsqlCopySerializer(connection1);
//NpgsqlCopySerializer cs2 = new NpgsqlCopySerializer(connection2);

for (int index = 0; index < 10; index ++)
{
cs1.AddInt32(index);
cs1.AddString(string.Format("Index {0} ", index));
cs1.EndRow();

/*cs2.AddInt32(index);
cs2.AddNull();
cs2.AddString(string.Format("Index {0} ", index));
cs2.AddNull();
cs2.AddString("jjjjj");
cs2.AddNull();
cs2.EndRow();*/

}
cs1.Close(); //Exception
//cs2.Close();

copy1.End();
copy2.End();

connection1.Close();
connection2.Close();

database structure:

CREATE TABLE table1
(
id integer,
"name" character varying(100)
)
WITH (
OIDS=FALSE
);
ALTER TABLE table1 OWNER TO postgres;

CREATE TABLE table2
(
id integer,
"null1" integer,
"name" character varying(100),
"null2" integer,
"description" character varying(1000),
"null3" integer
)
WITH (
OIDS=FALSE
);
ALTER TABLE table2 OWNER TO postgres;

you can find more details here (russian) http://www.sql.ru/forum/actualthread.aspx?tid=832312

[franciscojunior]

Date: 2013-09-17 12:50
Sender: Marcin Głowacki

Hi,

I found some time to tinker and made a workaround for this problem.

I made NpsqlCopyInState ctor public and this change:

@@ -966,7 +966,7 @@ namespace Npgsql
case BackEndMessageCode.CopyInResponse:
// Enter COPY sub protocol and start pushing data to server
NpgsqlEventLog.LogMsg(resman, "Log_ProtocolMessage", LogLevel.Debug, "CopyInResponse");
- ChangeState(context, NpgsqlCopyInState.Instance);
+ ChangeState(context, new NpgsqlCopyInState());
PGUtil.ReadInt32(stream); // length redundant
context.CurrentState.StartCopy(context, ReadCopyHeader(stream));
yield break;

This way number of fields does not get overwritten when using two NpgsqlCopyIn at the same time. I didn't dig too deep so there might be some issues, however I'm able to run two CopyIns from different connections and it works just fine.

Please take a look at it.

---

Date: 2013-06-08 18:44
Sender: Marcin Głowacki

As Dariusz said NpgsqlCopyInState is a singleton. FieldCount and probably some other data is being overridden when second NpgsqlCopyIn receives a response from server after starting. I'll try to fix it. If you know any reasons as to why these states are singletons let me know please.

---

Date: 2013-06-08 14:08
Sender: Francisco Figueiredo jr.

Hi, Marcin!

Please, let me know if you find anything which could help solve that situation.

As you are using 2 different connections, I think the problem may be that NpgsqlCopyIn class maybe is using some static data and this is giving trouble when being used by two connections simultaneously.

Also note that the official code repository changed to git. http://git.npgsql.org
I hope it helps.

---

Date: 2013-06-08 09:45
Sender: Marcin Głowacki

This approach doesn't seem to work and I'm not using NpgsqlCopySerializer class either.

I'm analyzing Npgsql code now and see what the problem is. I'm serializing objects myself so I'm trying to find where the extra data is added.

---

Date: 2013-06-07 06:34
Sender: Dariusz Dudek

My suggestion for workaround

UTF8Encoding enc = new UTF8Encoding(false);
StreamWriter sw = new StreamWriter(copy1.CopyStream, enc);

for (int index = 0; index < 100; index++)
{
sw.Write(index);
sw.Write('\t');
sw.Write(string.Format("Index {0} ", index));
sw.Write('\n');
}
sw.Flush();

... or change implementation of NpgsqlCopyInState or NpgsqlCopySerializer. NpgsqlCopyInState is singleton, so internal CopyFormat has invalid value in FieldCount from second command NpgsqlCopyIn. NpgsqlCopySerializer.EndRow shouldn't add extra null values.

---

Date: 2013-06-06 19:17
Sender: Marcin Głowacki

Has anyone found a solution to that problem? I've stumbled upon this today and can't find much information on any workarounds apart from not using 2 NpgsqlCopyIn's at the same time which in my case it's not an option.

I'm getting the same error as above but it's also dependant on amount of data to copy. In my case for each object I need to insert one row to first table (17 columns) and several rows to second table (67 columns). I use one NpgsqlCopyIn for each table. It works fine up to around 600 objects. If I try to copy more at once I get 22P04 error.

First I tried to simulate this situation in a test program which also had 2 tables and everything worked smoothly even though one had 2 columns and other one had 3... so it looks like it's not entirely dependant their number.