[Postgres-xl-general] Prepared statement failure?
Mason Sharp
msharp at translattice.com
Thu Jul 24 13:57:00 PDT 2014
I have added this as bug #24:
https://sourceforge.net/p/postgres-xl/tickets/24/
On Wed, Jul 23, 2014 at 9:30 PM, Aaron Jackson <ajackson at revionics.com>
wrote:
> Yes, it would definitely affect more than just smallint. It could
> certainly be translated at the coordinator to ensure that it's compatible
> with vanilla postgresql on the wire.
> *Aaron*
> ------------------------------
> *From:* Mason Sharp [msharp at translattice.com]
> *Sent:* Wednesday, July 23, 2014 7:18 PM
>
> *To:* Aaron Jackson
> *Cc:* postgres-xl-general at lists.sourceforge.net
> *Subject:* Re: [Postgres-xl-general] Prepared statement failure?
>
>
>
>
> On Wed, Jul 23, 2014 at 5:27 PM, Aaron Jackson <ajackson at revionics.com>
> wrote:
>
>> Even more messy than I thought...
>>
>> It appears that by default, postgresql will report the fields of a
>> prepared statement as type "text" - however, they are actually encoded
>> binary. This explains the difference between Postgres-Vanilla and
>> Postgres-XL. In Postgres-XL, the prepared schema reports in "text" and
>> actually is encoded text (passthrough tuples from the datanode). So, the
>> reported format is correct but the driver tries to account and assumes it's
>> really binary.
>>
>> If this is unclear, well, yeah it probably should be.
>>
>
> Good sleuthing, Aaron.
>
> It sounds like we should convert to binary then in this case on the
> coordinator, or have the datanodes use binary.
>
> Does this not mean that other values are actually incorrect for prepared
> statements then, too, not just smallint?
>
>
>
>
>>
>> *Aaron*
>> ------------------------------
>> *From:* Aaron Jackson
>>
>> *Sent:* Wednesday, July 23, 2014 4:19 PM
>> *To:* Mason Sharp
>> *Cc:* postgres-xl-general at lists.sourceforge.net
>> *Subject:* RE: [Postgres-xl-general] Prepared statement failure?
>>
>> My goodness, this appears to have all been a long chase to discover
>> that Npgsql (the driver) is explicitly changing the format code when
>> parameters are bound. It's part of their PrepareInternal() method. As a
>> result of this adjustment they bind the wrong parser for the data and the
>> result is that the driver parses it wrong.
>>
>> So, chalk this up to driver error. However, I am still not sure what
>> is so different between pgsql and pgxl that makes this problem manifest
>> itself.
>>
>> *Aaron*
>> ------------------------------
>> *From:* Aaron Jackson [ajackson at revionics.com]
>> *Sent:* Wednesday, July 23, 2014 3:55 PM
>> *To:* Mason Sharp
>> *Cc:* postgres-xl-general at lists.sourceforge.net
>> *Subject:* Re: [Postgres-xl-general] Prepared statement failure?
>>
>> 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>
>> 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]
>>> *Sent:* Tuesday, July 22, 2014 5:11 PM
>>> *To:* Aaron Jackson
>>> *Cc:* 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>
>>> 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
>>
>>
>>
>
>
> --
> 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/20140724/e54dbcd0/attachment.htm>
More information about the postgres-xl-general
mailing list