C# - postgresql - memory leak over time

Question

Problem: The memory leaks (accumulates) over time, and reaches 99% capacity eventually.

I have the following C# code that constantly pushes data into PostgreSQL DB using while loop. I'm really struggling because I'm not a C# programmer. My main language is Python. I've been trying to look up C# references to solve my issue, but failed cuz I simply don't understand lots of syntax. The C# code is written by someone else in my company, but he's not available now.

Here is the code:

var connString = "Host=x.x.x.x;Port=5432;Username=postgres;Password=password;Database=database";

using (var conn = new Npgsql.NpgsqlConnection(connString)){

    conn.Open();

    int ctr = 0;

    // Insert some data
    while(@tag.TerminateTimeScaleLoop == 100)
    {
        @Info.Trace("Pushed Data: PostGre A " + ctr.ToString());
        using (var cmd = new Npgsql.NpgsqlCommand())
        {
            cmd.Connection = conn;
            cmd.CommandText = "INSERT INTO TORQX VALUES (@r,@p)";
            cmd.Parameters.AddWithValue("r", System.DateTime.Now.ToUniversalTime());
            cmd.Parameters.AddWithValue("p", @Tag.RigData.Time.TORQX);
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();


            cmd.CommandText = "INSERT INTO BLKPOS VALUES (@s,@t)";
            cmd.Parameters.AddWithValue("s", System.DateTime.Now.ToUniversalTime());
            cmd.Parameters.AddWithValue("t", @Tag.RigData.Time.BLKPOS);
            cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();



           // @Info.Trace("Pushed Data: PostGre " + ctr.ToString());
        }

        ctr = ctr + 1;
    }
    @Info.Trace("Pushed Data: PostGre A Terminated");

The code successfully established a connection in the beginning, and uses only one connection the entire time. It correctly inserts data to DB. But after memory capacity reaches 99%, its not inserting very well. The source of issue I can think of is that this code is constantly creating new object, but does not clear that object after one iteration is done. Can anyone tell me where the source of problem is & provide possible solution to this?

++ please understand that I'm not a C# programmer... I'm not too familiar with the concept of memory handling. But I will try my best to understand

You may just be overwhelming the garbage collector by looping too fast and too much. Try creating the command and parameters outside of the loop so you only create one instance instead of one for each iteration. Set the CommandText and parameter values inside the loop. — Crowcoder
– Crowcoder, Commented Aug 18, 2018 at 18:53

Crowcoder · Accepted Answer · 2018-08-18 19:17:09Z

2

Here is something you can try. Notice the instantiation of the command and parameters happens outside of the loop, not on every iteration.

I am recycling the parameters. As a result I am using Add(), not AddWithValue() and you must fill in the database type for the second parameter and consider using precision and scale parameters too as appropriate. This will only work if the two commands use the same parameter types. You might consider creating two commands, one for each query.

Know that variable names beginning with @ makes me cringe as a c# developer....

var connString = "Host=x.x.x.x;Port=5432;Username=postgres;Password=password;Database=database";

using (var conn = new Npgsql.NpgsqlConnection(connString))
{
    conn.Open();

    int ctr = 0;

    @Info.Trace("Pushed Data: PostGre A " + ctr.ToString());

    using (var cmd = new Npgsql.NpgsqlCommand())
    {
        cmd.Connection = conn;

        var par_1 = cmd.Parameters.Add("@p1", /*< appropriate datatype here >*/);
        var par_2 = cmd.Parameters.Add("@p2", /*< appropriate datatype here >*/);

        while(@tag.TerminateTimeScaleLoop == 100)
        {
            cmd.CommandText = "INSERT INTO TORQX VALUES (@p1,@p2)";
            par_1.Value = System.DateTime.Now.ToUniversalTime());
            par_2.Value = @Tag.RigData.Time.TORQX;

            cmd.ExecuteNonQuery();

            cmd.CommandText = "INSERT INTO BLKPOS VALUES (@p1,@p2)";

            par_1.Value = System.DateTime.Now.ToUniversalTime());
            par_2.Value = @Tag.RigData.Time.BLKPOS;

            cmd.ExecuteNonQuery();

            ctr = ctr + 1;
        }
    }
}
@Info.Trace("Pushed Data: PostGre A Terminated");

answered Aug 18, 2018 at 19:17

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Eric Kim Over a year ago

thanks for your answer. I have a follow up question. 1. Why do I have to define the datatype, with your set of code? With the previous code, I don't see any code that defines datatype, but it worked. 2. If I must define datatype, how should I define it? I printed the data type for par_1 and par_2, and it said T.Kernel.Tags.AnalogDouble, and System.DateTime. What's the c# code that defines these data types?

Eric Kim Over a year ago

I printed the data type by using .GetType().ToString()

Crowcoder Over a year ago

You don't have to define the data type but it's good practice. I don't know the nuances of postgres but omitting the data type can have performance and accuracy issues in ms sql server. I can't tell you how to define the types because I'm not familiar with the postgres provider. I'm sure the API is documented online.

Luminous_Dev Over a year ago

Hi @Crowcoder i know this been many years, I am having similar memory building up issue and trying to track down why. So why would AddWithValue cause memory leak to build up? shouldnt they be garbage collected? I am using npgsqlconnection with dapper, do you have any idea?

Crowcoder Over a year ago

@Luminous_Dev AddWithValue won't cause a memory leak, I just used it because it is a better implementation. Assuming the postgres provider is properly implemented with good finalizers then yes, garbage collection will do its job eventually even if you don't dispose() correctly. But it is still possible to overwhelm the GC. Most likely your issue is not with sql parameters though.

Collectives™ on Stack Overflow

C# - postgresql - memory leak over time

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related