PgSqlException - "Parameter name is missing"

[andrewvk]

I have this code:

Code: Select all

private const string s_CatalogParam = "catalog";
private const string s_SqlSchemas = "SELECT schema_name FROM information_schema.schemata WHERE catalog_name=:" + s_CatalogParam;

...

using (var connection = new PgSqlConnection(m_ConnectionString))
{
	connection.Open();
	using (IDbCommand cmd = connection.CreateCommand())
	{
		cmd.CommandText = s_SqlSchemas;
		cmd.CommandType = CommandType.Text;
		var prm = cmd.CreateParameter();
		prm.ParameterName = s_CatalogParam;
		prm.DbType = DbType.String;
		prm.Direction = ParameterDirection.Input;
		prm.Value = Name ?? "";
		cmd.Parameters.Add(prm);
		using (var rdr = cmd.ExecuteReader())
			while (rdr.Read())
				schemas.Add(new PostgreSchema(this, rdr.GetString(0), m_ConnectionString));
	}
}

First call of this code completed without any problem, but second call throwed an "Parameter name is missing" exception inside ExecuteReader(). Are there any workarounds?

dotConnect version is 6.6.226.0

[Pinturiccio]

We could not reproduce the issue. Please send us a complete small test project with the corresponding DDL/DML scripts for reproducing the issue.

[andrewvk]

Sorry, but I can't reproduce this problem in a separate project. Probably, because failed application use many connections at same time and this corrupts internal state in specific situation. Is it possible to obtain source code or unobfuscated dll + pdb (of course with NDA), so I can trace the reason of the bug? Or, maybe, you can give me debug version with diagnostics and invariant checks?

[Pinturiccio]

Please post the call stack of the exception here. Please also specify whether you use multithreading in your application.

[andrewvk]

No, in this specific scenario multithreading is not used (and no multithreading in other parts of application).
Call stack:

Code: Select all

Devart.Data.PostgreSql.dll!Devart.Data.PostgreSql.PgSqlCommand.a(Devart.Data.PostgreSql.k[] A_0) + 0x1f4 bytes	
 	Devart.Data.PostgreSql.dll!Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(bool implicitPrepare, int startRecord, int maxRecords) + 0x1fa bytes	
 	Devart.Data.dll!Devart.Common.DbCommandBase.ExecuteDbDataReader(System.Data.CommandBehavior behavior, bool nonQuery) + 0xbd bytes	
 	System.Data.dll!System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() + 0x15 bytes	
>	PostgreDataProvider.dll!Parus.Net.Server.Data.PostgreSQL.Scheme.PostgreCatalog.LoadSchemas() Line 57 + 0x2c bytes	C#
 	DBScheme.dll!Parus.Data.Schema.Impl.DbCatalog.InitSchemasInternal() Line 81 + 0x1d bytes	C#
 	DBScheme.dll!Parus.Data.Schema.Impl.DbCatalog.GetScheme(string schemeName) Line 56 + 0xa bytes	C#
 	Parus.Deploy.Engine.dll!Parus.Deploy.Services.DbSchemaSynchronizer.Synchronize(Rsdn.SmartApp.IServiceProvider provider, System.Data.IDbConnection connection, System.Data.IDbTransaction transaction, Parus.Data.Schema.IDbScheme required, Parus.Net.Server.Data.DataProvider targetProvider) Line 56 + 0x159 bytes	C#
 	AppServer.Core.dll!Parus.Net.Server.Data.DatabaseChecker.Check() Line 43 + 0x3f bytes	C#
 	AppServer.Core.dll!Parus.Net.Metadata.ServerMetadataManager.CheckMetadata(Parus.ObjectManager.Schema.IModel model, string modelName) Line 275 + 0x10 bytes	C#
 	AppServer.Core.dll!Parus.Net.Metadata.ServerMetadataManager.GenerateMetadataModel(Rsdn.SmartApp.IServiceProvider serviceProvider, bool checkMetadataSchema) Line 224 + 0x46 bytes	C#
 	AppServer.Core.dll!Parus.Net.Metadata.ServerMetadataManager.ServerMetadataManager(Rsdn.SmartApp.IServiceProvider serviceProvider, bool checkMetadataSchema) Line 109 + 0x1b bytes	C#
 	AppServer.dll!Parus.Net.Server.AppServer.AppServer.AppServer(Parus.Net.Server.AppServerMode mode, Parus.Net.Logger.ILogger logger) Line 217 + 0x3a bytes	C#
 	TornadoServer.exe!Parus.Server.ServerHost.Init(Parus.Net.Server.AppServerMode mode, Parus.Net.Logger.ILogger logger) Line 29 + 0x3a bytes	C#
 	[Appdomain Transition]	
 	TornadoServer.exe!Parus.Server.TornadoRunner.DeployCore(Parus.Net.Logger.Logger logger) Line 78 + 0x1f bytes	C#
 	TornadoServer.exe!Parus.Server.TornadoRunner.Deploy(Parus.Net.Logger.Logger logger) Line 54 + 0xa bytes	C#
 	TornadoServer.exe!Parus.Server.Program.Main() Line 173 + 0x9 bytes	C#

[Pinturiccio]

Could you please provide the following information?
1) Do you use TransactionScope or PgSqlTransaction?
2) PgSqlConnection uses connection pooling. By default connection pooling is used. Is the issue reproduced, if you turn off connection pooling for your connections? This can be done by adding the 'Pooling=false'; parameter to the connection string.
3) Do you work with large objects anywhere in your application?
4) Do you use protocol 3 or protocol 2?
5) Do you use PgSqlAlerter in your application?
6) Do you work with cursors in your application?
7) Does your application work with stored procedures?

[andrewvk]

1) Yes, transactions is used in other connections (I'm not sure, is it direct use of PgSqlTransaction, or by calling IDbConnection.BeginTransaction()).
2) No, issue was not reproduced after disabling connection pool. Probably, state of the transaction corrupted elsewhere, and that is the reason why first call completes successfully.
3) Yes.
4) By default. Pg version is 9.2.4 x64.
5) No
6) No
7) No SPs, no user functions and no triggers exists in DB.

[Pinturiccio]

At the moment, we think that the reason of the problem is in using large objects or transactions. It would be great if you could run your application without transactions or large objects.

Please tell us whether you use our PgSqlLargeObject to work with large objects. If yes, please comment its using out for the purpose of testing.