NpgsqlCommand.Dispose() should execute "DEALLOCATE <name>" for a prepared command #158
Description
NpgsqlCommand supports Prepare() and I think it should execute "DEALLOCATE " in its Dispose() method.
Currently there is no NpgsqlCommand.Dispose() method at all.
Preparing of many commands will pump up the server process memory and closing the connection afterwards takes very long!
Below is an example program...
(It was tested with Win7/64bit, PostgreSQL 9.2.6, .NET 4.0, VS2012, Npgsql/master current commit 6407cc3.)
In my case disposing of connection takes 14s. But with DEALLOCATE work around it takes only 2ms.
using Npgsql;
using System;
using System.Diagnostics;
class Program
{
static void Main(string[] args)
{
var sw = Stopwatch.StartNew();
using (var con = new NpgsqlConnection("Server=localhost;User ID=npgsql_tests;Password=npgsql_tests;Database=npgsql_tests;syncnotification=false"))
{
con.Open();
for (int i = 0; i < 30000; i++)
{
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT 0";
cmd.Prepare();
cmd.ExecuteScalar();
}
// activating this DEALLOCATE command works around missing deallocation of prepared command
/*
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "DEALLOCATE ALL";
cmd.ExecuteNonQuery();
}
*/
}
Console.WriteLine(sw.Elapsed);
sw.Restart();
}
Console.WriteLine(sw.Elapsed);
Console.ReadLine();
}
}