"Cannot write to a BufferedStream" error discussion

[Aleksey Kontsevich]

Continuation of discussion started here:

• https://github.com/npgsql/Npgsql/issues/252
• https://github.com/npgsql/npgsql/issues/270

About following error: Cannot write to a BufferedStream while the read buffer is not empty if the underlying stream is not seekable. Ensure that the stream underlying this BufferedStream can seek or avoid interleaving read and write operations on this BufferedStream.

Hi Francisco,

Hope following code will help. I open connection one time while setting connection string:

    public class PgDataAccess
    {
        // Connection variables
        private string connectionString = "";
        private NpgsqlConnection sqlConnection = new NpgsqlConnection();

        private bool Open()
        {
            try
            {
                if (sqlConnection.State == System.Data.ConnectionState.Closed)
                    sqlConnection.Open();
               
                return true;
            }
            catch (Exception e)
            {
                Log.Warn(string.Format("PgDataAccess::Open(): Error openning DB, connection state: {0}, connection string:  {1}",
                                        sqlConnection.State.ToString(), ConnectionString.RemovePassword()), e);
                return false;
            }
        }

        /// <summary>
        /// Closes database connection
        /// </summary>
        public void Close()
        {
            if (sqlConnection.State == System.Data.ConnectionState.Open)
                sqlConnection.Close();
        }

        /// <summary>
        /// Sets or gets Connection string, opens connection automatically on set
        /// </summary>
        public string ConnectionString
        {
            set
            {
                if (connectionString != value)
                    Close();
                else
                    return;

                connectionString = value;
                sqlConnection.ConnectionString = connectionString.SetCommandTimeout(CommandTimeout);

                if (Open())
                {
                    GetFunctionsList();
                    GetAccountVariables();
                }
            }
            get
            {
                return connectionString;
            }
        }

        private NpgsqlParameter[] PrepareParameters(object[] parameters)
        {
            if (parameters == null || parameters.Length == 0)
                return null;

            NpgsqlParameter[] pgParameters = new NpgsqlParameter[parameters.Length];

            for(int i = 0; i < parameters.Length; i++)
            {
                Type type;
                if (parameters[i].IsDBNull())
                    type = System.DBNull.Value.GetType();
                else
                    type = parameters[i].GetType();
                pgParameters[i] = new NpgsqlParameter();
                pgParameters[i].NpgsqlDbType = GetParameterType(type);
                pgParameters[i].Value = parameters[i] ?? System.DBNull.Value;
            }
            return pgParameters;
        }
    }

[Francisco Figueiredo Jr.]

Hi, Aleksey!


Thanks for sharing your code.

I notice you are using a single connection tied to PgDataAccess and that you use this class as an static variable in your log class.
This means your code is very susceptible to thread concurrency issues which may cause you a lot of problems.
NpgsqlConnection isn't thread safe, just like other .net data providers.

I'd say your problems with the BufferedStream exception is caused by two or more threads trying to use the same connection at the same time.


I suggest you to open the connection and close it each time you use the connection, by relying in the internal Npgsql connection pooling. This will help you avoid a lot of concurrency problems. If you still want to keep using a single connection through the lifetime of PgDataAccess, I think you should some serialization mechanism when accessing the NpgsqlConnection.


I think that after you make one of those changes: open and close a new NpgsqlConnection each time you are sending logging to Postgresql, or use some locking mechanism, your problems will disappear.  I'd go with the first one which is simpler and the recommended way.
 
I hope it helps.

Please, let me know if any of those changes worked for you.

[Aleksey Kontsevich]

2014-12-14 22:44 GMT+02:00 Francisco Figueiredo Jr. <francisco.f...@gmail.com>:

I notice you are using a single connection tied to PgDataAccess and that you use this class as an static variable in your log class.
This means your code is very susceptible to thread concurrency issues which may cause you a lot of problems.
NpgsqlConnection isn't thread safe, just like other .net data providers.

I'd say your problems with the BufferedStream exception is caused by two or more threads trying to use the same connection at the same time.

Think  so also, did not know Npgsql is not thread safe.

I suggest you to open the connection and close it each time you use the connection, by relying in the internal Npgsql connection pooling. This will help you avoid a lot of concurrency problems.

I see, I tried open/close connection without thread pooling, this also led to error like: trying to execute command when connection is not open. Seems connection was in opening state.

 

If you still want to keep using a single connection through the lifetime of PgDataAccess, I think you should some serialization mechanism when accessing the NpgsqlConnection.

Seems so, need to how that was implemented in NLog, problem disappeared when I switched to it.

 

I think that after you make one of those changes: open and close a new NpgsqlConnection each time you are sending logging to Postgresql, or use some locking mechanism, your problems will disappear.  I'd go with the first one which is simpler and the recommended way.

I tried locking on da, did not help, strange.

--
С Любовью,
Алексей.

Blessings,
Aleksey.

[Aleksey Kontsevich]

2014-12-15 4:24 GMT+02:00 Aleksey Kontsevich <akont...@gmail.com>:

I tried locking on da, did not help, strange.

I think this is because ExecuteNonQuery returned control before actual command was executed and next on tried to execute.

[Francisco Figueiredo Jr.]

Hi, Sorry for late reply.

Did you try creating a new connection each time da interacts with database?

Also note that you shouldn't block or try to access the database "inside" a notification handler, unless you open another database connection. This is because if you try to use the same connection, you will endup being locked up because Npgsql isn't thread safe.

I hope it helps.

On Monday, December 15, 2014 12:27:19 AM UTC-2, Aleksey Kontsevich wrote: