Receiving System.IO.IOException when trying to delete an entry

Question

Receiving System.IO.IOException when trying to delete an entry

I have a database with 60 tables. One is table person which is referenced by a large number of other tables (about 40), mostly using ON DELETE CASCADE ON UPDATE CASCADE.

Using Npgsql, I try running the following command: DELETE FROM person WHERE id = @PersonId

As a result I receive a System.IO.Exception:

Npgsql.NpgsqlException (0x80004005): Exception while reading from stream ---> System.IO.IOException: Von der Übertragungsverbindung können keine Daten gelesen werden: Ein Verbindungsversuch ist fehlgeschlagen, da die Gegenstelle nach einer bestimmten Zeitspanne nicht richtig reagiert hat, oder die hergestellte Verbindung war fehlerhaft, da der verbundene Host nicht reagiert hat. ---> System.Net.Sockets.SocketException: Ein Verbindungsversuch ist fehlgeschlagen, da die Gegenstelle nach einer bestimmten Zeitspanne nicht richtig reagiert hat, oder die hergestellte Verbindung war fehlerhaft, da der verbundene Host nicht reagiert hat bei System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) bei System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) --- Ende der internen Ausnahmestapelüberwachung --- bei System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size) bei Npgsql.NpgsqlReadBuffer.<>c__DisplayClass31_0.<g__EnsureLong|0>d.MoveNext() bei Npgsql.NpgsqlReadBuffer.<>c__DisplayClass31_0.<g__EnsureLong|0>d.MoveNext() --- Ende der Stapelüberwachung vom vorhergehenden Ort, an dem die Ausnahme ausgelöst wurde --- bei System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) bei System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) bei Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext()

In English, the inner exception is

Npgsql.NpgsqlException (0x80004005): Exception while reading from stream ---> System.IO.IOException: Data can not be read from the transmission connection: A connection attempt failed because the remote station did not respond correctly after a certain period of time, or Established connection was faulty because the connected host did not respond. ---> System.Net.Sockets.SocketException: A connection attempt failed because the remote station did not respond correctly after a certain amount of time, or the connection made failed because the connected host did not respond to System.Net.Sockets. Socket.Receive (Byte [] buffer, Int32 offset, Int32 size, SocketFlags socketFlags) at System.Net.Sockets.NetworkStream.Read (Byte [] buffer, Int32 offset, Int32 size) --- End of internal exception stack trace

In the Postgres logs it says:

2019-03-28 12:42:15.077 CET [75060] LOG: konnte Daten vom Client nicht empfangen: unrecognized winsock error 10053

I suppose it is not a deadlock as there is no deadlock message in the logs.

I think it's related to the fact that the table is referenced by so many foreign keys. But I have no idea how to investigate further.

c#

postgresql

foreign-keys

sql-delete

npgsql

asked on Stack Overflow Mar 28, 2019 by

Abid • edited Mar 28, 2019 by

Laurenz Albe

2 Answers

Per the exception, this issue is related to a timeout of a long running script. You didn't include a code sample so I can't specifically provide the syntax to increase this but setting the commandTimeout = 0 will disable timeout and will resolve the problem.

To address your question in the comments,

Is there any way how I can investigate why it takes such a long time?

It could just be deleting a large amount of records. You should run your queries outside of C# to ensure queries are optimized before inserting them into code.

Deleting large sets of records can take a while, you should look into truncate or some other purging design such as dumping into temporary tables, etc.

answered on Stack Overflow Mar 28, 2019 by

Daniel

My money is on missing indexes on the foreign keys referencing person.

If you have a table like this:

CREATE TABLE atable (
   id bigint PRIMARY KEY,
   parent_id bigint NOT NULL
      REFERENCING parent ON DELETE CASCADE
);

and the table contains a lot of rows, then every row deleted in person will result in a sequential table scan on atable to find the matching rows. That is probably where all your time is spent.

Create an index on all tables referencing person:

CREATE INDEX ON atable (parent_id);

That will make the delete much faster.

This blog post explains the problem in detail.

answered on Stack Overflow Mar 28, 2019 by

Laurenz Albe

User contributions licensed under CC BY-SA 3.0