EndOfStream exception when fetching query in DataTable using DbDataAdapter. #747
Comments
|
Hey @FransBouma. The stack trace definitely points at an Npgsql bug. However, I just tried to reproduce the issue with the code above and didn't manage to. Below is the precise code I'm using (against 3.0.1), can you try to produce a repro? using (var conn = new NpgsqlConnection(ConnectionString))
{
var query = @"SELECT c.conname AS constraint_name, pkn.nspname AS pk_schema, fkn.nspname AS fk_schema,
fkt.relname AS fk_table, pkt.relname AS pk_table, pkf.attname AS pk_field, fkf.attname AS fk_field,
c.confkey AS pk_field_ordinals, c.conkey AS fk_field_ordinals, pkf.attnum AS pk_field_ordinal,
fkf.attnum AS fk_field_ordinal, c.confupdtype AS fk_update_rule, c.confdeltype AS fk_delete_rule
FROM pg_constraint c
INNER JOIN pg_namespace n ON c.connamespace = n.oid
INNER JOIN pg_class pkt ON c.confrelid=pkt.oid
INNER JOIN pg_namespace pkn ON pkt.relnamespace = pkn.oid
INNER JOIN pg_class fkt ON c.conrelid=fkt.oid
INNER JOIN pg_namespace fkn ON fkt.relnamespace = fkn.oid
INNER JOIN pg_attribute pkf ON pkf.attnum = ANY(c.confkey) AND pkf.attrelid = pkt.oid
INNER JOIN pg_attribute fkf ON fkf.attnum = ANY(c.conkey) AND fkf.attrelid = fkt.oid
WHERE c.contype='f' ORDER BY fk_schema ASC, fk_table ASC, constraint_name ASC, fk_field ASC, pk_schema ASC
";
var selectCommand = new NpgsqlCommand(query, conn);
var adapter = new NpgsqlDataAdapter(selectCommand);
var fkConstraints = new DataTable();
adapter.Fill(fkConstraints);
} |
|
Did you have a table with a self-reference? This table fails: CREATE TABLE public.Job
(
Id bigint NOT NULL DEFAULT new_id() PRIMARY KEY,
TypeFk bigint NOT NULL REFERENCES public.JobType,
ParentJobFk bigint REFERENCES public.Job,
ExecMethod varchar(250),
Arguments text,
Status varchar(50) NOT NULL,
Comment varchar(500),
Created timestamp NOT NULL DEFAULT utcnow(),
Modified timestamp NOT NULL DEFAULT utcnow()
); |
|
I do now, see code below. It's hard to imagine the self-reference being part of the problem - at the end of the day Npgsql just reads columns and rows from the big join query... using (var conn = new NpgsqlConnection(ConnectionString))
{
ExecuteNonQuery(@"DROP TABLE IF EXISTS public.""Job""");
ExecuteNonQuery(@"DROP TABLE IF EXISTS public.""JobType""");
ExecuteNonQuery(@"CREATE TABLE public.""JobType"" (Id BIGSERIAL NOT NULL PRIMARY KEY)");
ExecuteNonQuery(@"CREATE TABLE public.""Job""
(
Id BIGSERIAL NOT NULL PRIMARY KEY,
TypeFk bigint NOT NULL REFERENCES public.""JobType"",
ParentJobFk bigint REFERENCES public.""Job"",
ExecMethod varchar(250),
Arguments text,
Status varchar(50) NOT NULL,
Comment varchar(500),
Created timestamp,
Modified timestamp
);");
var query = @"SELECT c.conname AS constraint_name, pkn.nspname AS pk_schema, fkn.nspname AS fk_schema,
fkt.relname AS fk_table, pkt.relname AS pk_table, pkf.attname AS pk_field, fkf.attname AS fk_field,
c.confkey AS pk_field_ordinals, c.conkey AS fk_field_ordinals, pkf.attnum AS pk_field_ordinal,
fkf.attnum AS fk_field_ordinal, c.confupdtype AS fk_update_rule, c.confdeltype AS fk_delete_rule
FROM pg_constraint c
INNER JOIN pg_namespace n ON c.connamespace = n.oid
INNER JOIN pg_class pkt ON c.confrelid=pkt.oid
INNER JOIN pg_namespace pkn ON pkt.relnamespace = pkn.oid
INNER JOIN pg_class fkt ON c.conrelid=fkt.oid
INNER JOIN pg_namespace fkn ON fkt.relnamespace = fkn.oid
INNER JOIN pg_attribute pkf ON pkf.attnum = ANY(c.confkey) AND pkf.attrelid = pkt.oid
INNER JOIN pg_attribute fkf ON fkf.attnum = ANY(c.conkey) AND fkf.attrelid = fkt.oid
WHERE c.contype='f' ORDER BY fk_schema ASC, fk_table ASC, constraint_name ASC, fk_field ASC, pk_schema ASC
";
var selectCommand = new NpgsqlCommand(query, conn);
var adapter = new NpgsqlDataAdapter(selectCommand);
var fkConstraints = new DataTable();
adapter.Fill(fkConstraints);
} |
|
FYI I was thinking of publishing 3.0.2 very soon (e.g. tomorrow), I hope you guys can get a reliable repro quickly (there is obviously a bug to be fixed here) |
|
Does the backend log say anything useful? |
|
I can test it tomorrow morning (CET) on our setup (a customer ran into it). I'll ask the customer to participate in this thread too, and to test your small repro. One thing I was thinking about which might be the cause (but it's a bit far fetched) is whether it's caused by a timeout: if a timeout occurs, can this exception occur? I do know that a timeout sometimes causes a similar exception on ODP.NET (Oracle's ADO.NET provider) when data is streamed from the server to the client.
|
|
Well, according to the stack trace Npgsql is trying to read data (in order to populate the values) but the backend has closed the connection. Like @Emill suggests it could be a good idea to look at the PostgreSQL logs. If PostgreSQL decided to close the connection because of a timeout, that would explain it (I think pgbouncer has an option like this). So let's try to work on this tomorrow. I'm in Europe so I won't be up CET morning, but I think I'll be free in the afternoon if you and your client are still around. |
|
I have some exceptions at not mission critical production usage now (with 3.0.1) That exception is more or less frequent with some state of query results, that I still can't reproduce in test environment, so I didn't report it before. This exception is thrown after timeout of statement (30 sec) and has backend log entry 'unexpected EOF on client connection with an open transaction' That exception is not so frequent, but I get it from same query and without any timeout I was hoping it was from array issues, that was fixed in 3.0.2 |
|
Hi, we are still around. I am that client, who submitted this bug. It's late right now (UTC +3), but tomorrow i'll look to the PostgreSQL logs. But i can say now, that i'm using port for fetching this query, that PostgreSQL listens directly. As for question that @roji was wrote about self-reference - it's definetly can be a reason, because in table, that @FransBouma have posted, when i dropping self-reference field - i was able to fetch this query without any errors. |
|
@TheMidgardWatcher thanks for the extra info, I'll wait for more info tomorrow. My point about the self-reference field is that it's not a direct cause, but it could definitely trigger the bug in some way. The best way forward is some sort of way I can reproduce the bug on my machine. |
|
@kae, thanks for joining in, your experience definitely seems relevant. It could in theory arise from the array issues already fixed, you can install the 3.0.2-beta nuget from our unstable feed to test and confirm (@TheMidgardWatcher, this goes for you too if you're interested). Sorry for the hassle this is causing, hopefully we'll resolve it quickly and release 3.0.2. |
|
@roji I'm unable to find 3.0.2-beta version of npgsql to test if this bug resolved there. |
|
@TheMidgardWatcher you have to add the nuget unstable feed to see it: https://www.myget.org/F/npgsql-unstable. You can do this inside the GUI in Visual Studio (Tools -> NuGet Package Manager -> Package Manager Settings -> Package Sources), or you can add it to NuGet.config at the root of your project (https://docs.nuget.org/consume/nuget-config-file). Let me know if you still have trouble. |
|
@roji Thanks! Just tested unstable version Npgsql.3.0.2-beta0027 - bug is not reproducing. I think my problem is solved - I'll wait stable release! |
|
@TheMidgardWatcher, that would be really great news. Are you confident that the bug is gone, was it easy to reproduce before? If so I'll release 3.0.2. |
|
Yes - it was easy to repoduce on our environment, and after i replaced npgsql 3.0.1 to 3.0.2-beta0027 - bug is gone. So with this version we can't reproduce it anymore! |
|
Trying to reproduce it, but I run into another problem:
Stacktrace: SQL: SELECT attname as column_name, attnum as ordinal_position, atttypid::regtype as data_type,
information_schema._pg_char_max_length(atttypid, atttypmod) as character_maximum_length,
information_schema._pg_char_octet_length(atttypid, atttypmod) as character_octet_length,
information_schema._pg_numeric_precision(atttypid, atttypmod) as numeric_precision,
information_schema._pg_numeric_precision_radix(atttypid, atttypmod) as numeric_precision_radix,
information_schema._pg_numeric_scale(atttypid, atttypmod) as numeric_scale
FROM pg_attribute WHERE attrelid = ('public.'||:tableName)::regclass AND attnum > 0 AND NOT attisdropped
Where :tableName is customersmatview which looks like: -- Materialized View: customersmatview
CREATE MATERIALIZED VIEW customersmatview AS
SELECT customers.customerid,
customers.companyname,
customers.contactname,
customers.contacttitle,
customers.address,
customers.city,
customers.region,
customers.postalcode,
customers.country,
customers.phone,
customers.fax
FROM customers
WITH DATA;
ALTER TABLE customersmatview
OWNER TO postgres;which is defined on the Northwind Customers table. If I disable retrieving this, I can't reproduce the issue I started this thread with, I have the table which gives an issue in the DB (albeit without the fields with a default constraint). PG9.3, npgsql 3.0.1 So TL;DR: original issue isn't reproducible here, but doing so I ran into another issue. |
|
@FransBouma, this is because you're querying a relatively exotic type (regtype). Can you please open a separate issue for this, I'll take a look now if we can natively support it. Otherwise closing this issue. |
|
Will do. |
See: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=133091&ThreadID=23469
So, basically we execute:
using:
where
queryis the query as a normal string, and CreateDataAdapter creates using the DbProviderFactory a DbDataAdapter with a select command and a properly setup connection.gives:
The text was updated successfully, but these errors were encountered: