[Postgres-xl-general] Prepared statement failure?

Aaron Jackson ajackson at revionics.com
Wed Jul 23 13:55:22 PDT 2014


So, it appears that when row description of a prepared statement is provided from the coordinator to the client, that the coordinator for protocol version 3 is setting the "formats" value to (1) - which is BINARY.  The datanode passes back tuples in TEXT which are then forwarded to the client.  The client attempts to parse them in BINARY - which then fails.
I'm going to do a little more root cause analysis on why the formats parameter is set to BINARY after which, I believe I can find a resolution to this issue.
Aaron
________________________________
From: Aaron Jackson [ajackson at revionics.com]
Sent: Wednesday, July 23, 2014 2:57 PM
To: Mason Sharp
Cc: postgres-xl-general at lists.sourceforge.net
Subject: Re: [Postgres-xl-general] Prepared statement failure?

I can certainly put something together.  However, what I've found so far seems to indicate that the tuple is encoded coming out of the datanode in the text format.  Hence, all the values are encoded using pq_sendcountedtext().  However, when pgsql receives the result, it believes the format is supposed to be binary.  A smallint like "3" would be encoded as an octet stream of {1:int32,3:ascii}.

If the client receives this and assumes binary it will decode the first tuple as int32.  This will result in a field size of 1 which is wrong but it's only wrong because the format is incorrect.

Once I've confirmed this, I'll be able to provide a patch.

Aaron
________________________________
From: Mason Sharp [msharp at translattice.com]
Sent: Wednesday, July 23, 2014 11:43 AM
To: Aaron Jackson
Cc: postgres-xl-general at lists.sourceforge.net
Subject: Re: [Postgres-xl-general] Prepared statement failure?




On Tue, Jul 22, 2014 at 10:25 PM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
To my knowledge, smallint is the only one that has triggered this behavior.  But I have not tested other configurations.  My guess is that some other drivers may handle the case of int length 1 properly, however, in this case, the non-prepared and prepared statements give different behavior.

Also, it only occurs once there is a resultset to read... so, simply preparing the value does not cause the problem to occur.  I'm guessing that part of the protocol involves serializing the field spec to the wire so that the remote can deserialize it.  I'm not sure where that serialization occurs so, I'm still hunting.  Any hints would be much appreciated.

Do you have a small test case in .NET (C#?) that will reproduce the problem in XL?  That will help us investigate.

Thanks,

Mason



Aaron
________________________________
From: Mason Sharp [msharp at translattice.com<mailto:msharp at translattice.com>]
Sent: Tuesday, July 22, 2014 5:11 PM
To: Aaron Jackson
Cc: postgres-xl-general at lists.sourceforge.net<mailto:postgres-xl-general at lists.sourceforge.net>
Subject: Re: [Postgres-xl-general] Prepared statement failure?

Hi Aaron,



On Tue, Jul 22, 2014 at 2:46 PM, Aaron Jackson <ajackson at revionics.com<mailto:ajackson at revionics.com>> wrote:
So a colleague of mine noticed that when they prepared a statement with Postgres-XL that it would fail and that with standard Postgres it would not.  So, I looked a little further and it appears that Npgsql (the postgresql driver for .NET) is failing because the fieldsize being sent back for smallint after being prepared is 1, whereas standard postgresql returns a fieldsize of 2.

I am doing my best to look down through the way that postgresql is rewriting queries to understand what is happening but it appears to be reducing the fieldsize down to that of a single byte.  Ngpsql fails because it believes that ints only come in 3 flavors (2, 4 and 8 byte).  Now, I could patch Npgsql, but something seems strange here.  I’m okay with optimization but not at the expense of breaking existing drivers.  I will look further into this and see if I can’t uncover why Postgres-XC/XL exhibits this behavior and not standard postgres.


That sounds like an issue. To be clear, are prepared statements working for regular integers and bigints, it is just failing for smallints? Only through Npgsql?

I just did a quick prepared statement test with smallint and it seemed to work ok though, but not through Npgsql, just on the psql command line.

Thanks,


--
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions





--
Mason Sharp

TransLattice - http://www.translattice.com
Distributed and Clustered Database Solutions


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20140723/ae2bbcc9/attachment.htm>


More information about the postgres-xl-general mailing list