Обсуждение: Bug? {? = CALL insert_page_segment (?, ?)}
Hi!
I have a problem with newer versions of the ODBC driver. I made a small
testing example to reproduce the problem:
The following code works fine with version 09.02.0100 of the driver. With
09.03.0210, it crashes. With 09.03.0400, I get a strange exception and error
message.
This is all on Windows, 32-Bit. I tried Windows 7 and Windows 8.1; I also
tried Postgres versions 9.2.4, 9.2.9, 9.3.4 and 9.3.5, all 32-Bit.
Here is the create script for the database:
CREATE TABLE page_segments (
task_id uuid,
id uuid,
PRIMARY KEY (task_id, id)
);
CREATE FUNCTION insert_page_segment(theTaskId uuid, theId uuid) RETURNS int
AS $$
DECLARE
ret int NOT NULL := 0;
BEGIN
BEGIN
INSERT INTO page_segments (task_id, id) VALUES (theTaskId,
theId);
ret := 1;
EXCEPTION WHEN unique_violation THEN
-- ignore
END;
RETURN ret;
END
$$ LANGUAGE plpgsql;
And here is some C# code that calls the insert_page_segment function:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Odbc;
namespace ODBCTest
{
class Program
{
const string ConnString = @"Driver={PostgreSQL
Unicode};server=localhost;port=5432;database=stringtest;uid=cartan;pwd=...";
static void Main(string[] args)
{
using (var conn = new OdbcConnection(ConnString))
{
conn.Open();
Guid g1 = Guid.NewGuid();
Guid g2 = Guid.NewGuid();
Console.WriteLine("First call returns {0}",
CallTheFunc(conn, g1, g2));
}
Console.WriteLine("Press any key to exit...");
Console.ReadKey(true);
}
static int CallTheFunc(OdbcConnection conn, Guid taskId, Guid id)
{
using (var trans = conn.BeginTransaction())
{
try
{
const string insertCmdText = @"{? = CALL
insert_page_segment (?, ?)}";
using (var insertCmd = new OdbcCommand(insertCmdText,
conn, trans))
{
var retParam = new OdbcParameter();
retParam.OdbcType = OdbcType.Int;
retParam.Direction = ParameterDirection.ReturnValue;
insertCmd.Parameters.Add(retParam);
var taskIdParam = new OdbcParameter();
taskIdParam.OdbcType = OdbcType.UniqueIdentifier;
taskIdParam.Value = taskId;
insertCmd.Parameters.Add(taskIdParam);
var idParam = new OdbcParameter();
idParam.OdbcType = OdbcType.UniqueIdentifier;
idParam.Value = id;
insertCmd.Parameters.Add(idParam);
insertCmd.ExecuteNonQuery();
int ret = (int) retParam.Value;
trans.Commit();
return ret;
}
}
catch
{
trans.Rollback();
throw;
}
}
}
}
}
With the old 9.2.1 driver, the function just returns 1 as expected. With
9.3.4, I get an unusual InvalidOperationException in ExecuteNonQuery(),
saying
"This OdbcTransaction has completed; it is no longer usable."
In the Postgres log file, I find this message:
2014-11-11 17:26:04 CET ERROR: function insert_page_segment(unknown) does
not exist at character 8
2014-11-11 17:26:04 CET HINT: No function matches the given name and
argument types. You might need to add explicit type casts.
2014-11-11 17:26:04 CET STATEMENT: SELECT insert_page_segment ($1, $2)
2014-11-11 17:26:04 CET FATAL: invalid frontend message type 0
Can anybody help me with this?
Regards,
--
Nils Gösche
Dont ask for whom the <Ctrl-G> tolls.
Hi Nils,
On 2014/11/12 2:08, Nils Gösche wrote:
> Hi!
>
> I have a problem with newer versions of the ODBC driver. I made a small
> testing example to reproduce the problem:
>
> The following code works fine with version 09.02.0100 of the driver. With
> 09.03.0210, it crashes. With 09.03.0400, I get a strange exception and error
> message.
Please try to add ;parse=1 to your connection string or turn on
the *Parse statements* option of your DSN.
regards,
Hiroshi Inoue
> This is all on Windows, 32-Bit. I tried Windows 7 and Windows 8.1; I also
> tried Postgres versions 9.2.4, 9.2.9, 9.3.4 and 9.3.5, all 32-Bit.
>
> Here is the create script for the database:
>
> CREATE TABLE page_segments (
> task_id uuid,
> id uuid,
> PRIMARY KEY (task_id, id)
> );
>
> CREATE FUNCTION insert_page_segment(theTaskId uuid, theId uuid) RETURNS int
> AS $$
> DECLARE
> ret int NOT NULL := 0;
> BEGIN
> BEGIN
> INSERT INTO page_segments (task_id, id) VALUES (theTaskId,
> theId);
> ret := 1;
> EXCEPTION WHEN unique_violation THEN
> -- ignore
> END;
> RETURN ret;
> END
> $$ LANGUAGE plpgsql;
>
> And here is some C# code that calls the insert_page_segment function:
>
> using System;
> using System.Collections.Generic;
> using System.Linq;
> using System.Text;
> using System.Threading.Tasks;
> using System.Data;
> using System.Data.Odbc;
>
> namespace ODBCTest
> {
> class Program
> {
> const string ConnString = @"Driver={PostgreSQL
> Unicode};server=localhost;port=5432;database=stringtest;uid=cartan;pwd=...";
> static void Main(string[] args)
> {
> using (var conn = new OdbcConnection(ConnString))
> {
> conn.Open();
> Guid g1 = Guid.NewGuid();
> Guid g2 = Guid.NewGuid();
> Console.WriteLine("First call returns {0}",
> CallTheFunc(conn, g1, g2));
> }
> Console.WriteLine("Press any key to exit...");
> Console.ReadKey(true);
> }
>
> static int CallTheFunc(OdbcConnection conn, Guid taskId, Guid id)
> {
> using (var trans = conn.BeginTransaction())
> {
> try
> {
> const string insertCmdText = @"{? = CALL
> insert_page_segment (?, ?)}";
> using (var insertCmd = new OdbcCommand(insertCmdText,
> conn, trans))
> {
> var retParam = new OdbcParameter();
> retParam.OdbcType = OdbcType.Int;
> retParam.Direction = ParameterDirection.ReturnValue;
> insertCmd.Parameters.Add(retParam);
>
> var taskIdParam = new OdbcParameter();
> taskIdParam.OdbcType = OdbcType.UniqueIdentifier;
> taskIdParam.Value = taskId;
> insertCmd.Parameters.Add(taskIdParam);
>
> var idParam = new OdbcParameter();
> idParam.OdbcType = OdbcType.UniqueIdentifier;
> idParam.Value = id;
> insertCmd.Parameters.Add(idParam);
>
> insertCmd.ExecuteNonQuery();
> int ret = (int) retParam.Value;
>
> trans.Commit();
> return ret;
> }
> }
> catch
> {
> trans.Rollback();
> throw;
> }
> }
> }
> }
> }
>
> With the old 9.2.1 driver, the function just returns 1 as expected. With
> 9.3.4, I get an unusual InvalidOperationException in ExecuteNonQuery(),
> saying
>
> "This OdbcTransaction has completed; it is no longer usable."
>
> In the Postgres log file, I find this message:
>
> 2014-11-11 17:26:04 CET ERROR: function insert_page_segment(unknown) does
> not exist at character 8
> 2014-11-11 17:26:04 CET HINT: No function matches the given name and
> argument types. You might need to add explicit type casts.
> 2014-11-11 17:26:04 CET STATEMENT: SELECT insert_page_segment ($1, $2)
> 2014-11-11 17:26:04 CET FATAL: invalid frontend message type 0
>
> Can anybody help me with this?
>
> Regards,
> --
> Nils Gösche
> Don’t ask for whom the <Ctrl-G> tolls.
Hiroshi wrote: > On 2014/11/12 2:08, Nils Gösche wrote: > > Hi! > > > > I have a problem with newer versions of the ODBC driver. I made a > > small testing example to reproduce the problem: > > > > The following code works fine with version 09.02.0100 of the driver. > > With 09.03.0210, it crashes. With 09.03.0400, I get a strange > > exception and error message. > > Please try to add ;parse=1 to your connection string or turn on the > *Parse statements* option of your DSN. This setting does not seem to make any difference. Now I notice that the InvalidOperationException I am getting has an inner OdbcException, with the message: "ERROR [08S01] No response from the backend" On the server side, I still get: 2014-11-12 13:44:44 CET ERROR: function insert_page_segment(unknown) does not exist at character 8 2014-11-12 13:44:44 CET HINT: No function matches the given name and argument types. You might need to add explicit typecasts. 2014-11-12 13:44:44 CET STATEMENT: SELECT insert_page_segment ($1, $2) 2014-11-12 13:44:44 CET FATAL: invalid frontend message type 0 Regards, -- Nils Gösche "Don't ask for whom the <CTRL-G> tolls."
I wrote: > Now I notice that the InvalidOperationException I am getting has an > inner OdbcException, with the message: > > "ERROR [08S01] No response from the backend" > > On the server side, I still get: > > 2014-11-12 13:44:44 CET ERROR: function insert_page_segment(unknown) > does not exist at character 8 > 2014-11-12 13:44:44 CET HINT: No function matches the given name and > argument types. You might need to add explicit type casts. > 2014-11-12 13:44:44 CET STATEMENT: SELECT insert_page_segment ($1, $2) > 2014-11-12 13:44:44 CET FATAL: invalid frontend message type 0 Now I also turned on mylog and commlog. The output is in the attachment. Regards, -- Nils Gösche "Don't ask for whom the <CTRL-G> tolls."
Вложения
On 2014/11/12 21:51, Nils Gösche wrote: > Hiroshi wrote: > >> On 2014/11/12 2:08, Nils Gösche wrote: >>> Hi! >>> >>> I have a problem with newer versions of the ODBC driver. I made a >>> small testing example to reproduce the problem: >>> >>> The following code works fine with version 09.02.0100 of the driver. >>> With 09.03.0210, it crashes. With 09.03.0400, I get a strange >>> exception and error message. >> >> Please try to add ;parse=1 to your connection string or turn on the >> *Parse statements* option of your DSN. > > This setting does not seem to make any difference. Oops please try to add ;UseServerSidePrepare=0 instead. regards, Hiroshi Inoue > Now I notice that the InvalidOperationException I am getting has an inner OdbcException, with the message: > > "ERROR [08S01] No response from the backend" > > On the server side, I still get: > > 2014-11-12 13:44:44 CET ERROR: function insert_page_segment(unknown) does not exist at character 8 > 2014-11-12 13:44:44 CET HINT: No function matches the given name and argument types. You might need to add explicit typecasts. > 2014-11-12 13:44:44 CET STATEMENT: SELECT insert_page_segment ($1, $2) > 2014-11-12 13:44:44 CET FATAL: invalid frontend message type 0 > > Regards, >
Hiroshi wrote: > On 2014/11/12 21:51, Nils Gösche wrote: > > Hiroshi wrote: > > > >> Please try to add ;parse=1 to your connection string or turn on the > >> *Parse statements* option of your DSN. > > > > This setting does not seem to make any difference. > > Oops please try to add ;UseServerSidePrepare=0 instead. There you go: The problem goes away then! So, should I use the new driver together with this driver option on our customers' servers? I had always left this optionat its default value (1), which seems to be the recommended setting. Or is this something that will be fixed in thedriver, and I should stick to 9.2.1 for the time being? Regards, -- Nils Gösche "Don't ask for whom the <CTRL-G> tolls."
On Wed, Nov 12, 2014 at 11:06 PM, Nils Gösche <cartan@cartan.de> wrote: > So, should I use the new driver together with this driver option on our customers' servers? I had always left this optionat its default value (1), which seems to be the recommended setting. Or is this something that will be fixed in thedriver, and I should stick to 9.2.1 for the time being? UseServerSidePrepare default value has been switched from 0 to 1 in 09.03.0100, that's btw the value recommended for Postgres 7.4 onwards for quite some time. -- Michael
Michael wrote: > On Wed, Nov 12, 2014 at 11:06 PM, Nils Gösche <cartan@cartan.de> wrote: > > So, should I use the new driver together with this driver option on > > our customers' servers? I had always left this option at its default > > value (1), which seems to be the recommended setting. Or is this > > something that will be fixed in the driver, and I should stick to 9.2.1 > > for the time being? > UseServerSidePrepare default value has been switched from 0 to 1 in > 09.03.0100, that's btw the value recommended for Postgres 7.4 onwards > for quite some time. Oh! I knew that it had been the recommended value, I just assumed it also must have been the default value, then :-) So infact, our software has been running with UseServerSidePrepare=0 all the time. I can make a note for our support peopleto tell every customer who runs into this problem to add this setting to his connection string. What I do not quite understand, however, is why it is even necessary for us to deviate from the recommended setting. Is thereanything wrong with the way I am doing the query? Shouldn't it work with UseServerSidePrepare=1 as well? Regards, -- Nils Gösche "Don't ask for whom the <CTRL-G> tolls."
On 2014/11/13 16:56, Nils Gösche wrote: > Michael wrote: > >> On Wed, Nov 12, 2014 at 11:06 PM, Nils Gösche <cartan@cartan.de> wrote: > >>> So, should I use the new driver together with this driver option on >>> our customers' servers? I had always left this option at its default >>> value (1), which seems to be the recommended setting. Or is this >>> something that will be fixed in the driver, and I should stick to 9.2.1 >>> for the time being? > >> UseServerSidePrepare default value has been switched from 0 to 1 in >> 09.03.0100, that's btw the value recommended for Postgres 7.4 onwards >> for quite some time. > > Oh! I knew that it had been the recommended value, I just assumed it also must have been the default value, then :-) Soin fact, our software has been running with UseServerSidePrepare=0 all the time. I can make a note for our support peopleto tell every customer who runs into this problem to add this setting to his connection string. > > What I do not quite understand, however, is why it is even necessary for us to deviate from the recommended setting. Isthere anything wrong with the way I am doing the query? Shouldn't it work with UseServerSidePrepare=1 as well? Yes it should work. Currently there are some bugs about handling output parameters. It would be fixed in the next release. regards, Hiroshi Inoue
Hiroshi wrote: > Yes it should work. > Currently there are some bugs about handling output parameters. > It would be fixed in the next release. Cool. I am looking forward to that, then :-) Regards, -- Nils Gösche "Don't ask for whom the <CTRL-G> tolls."