Thread: Bug? {? = CALL insert_page_segment (?, ?)}

Bug? {? = CALL insert_page_segment (?, ?)}

From
Nils Gösche
Date:
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
Don’t ask for whom the <Ctrl-G> tolls.




Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
"Inoue, Hiroshi"
Date:
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.


Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
Nils Gösche
Date:
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."




Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
Nils Gösche
Date:
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."


Attachment

Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
"Inoue, Hiroshi"
Date:
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,
>


Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
Nils Gösche
Date:
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."




Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
Michael Paquier
Date:
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


Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
Nils Gösche
Date:
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."



Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
"Inoue, Hiroshi"
Date:

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



Re: Bug? {? = CALL insert_page_segment (?, ?)}

From
Nils Gösche
Date:
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."