[Postgres-xl-general] Prepared statement failure?

Mason Sharp msharp at translattice.com
Mon Sep 8 14:15:17 PDT 2014


On Mon, Sep 8, 2014 at 4:11 PM, Aaron Jackson <ajackson at revionics.com>
wrote:

>    Mason,
>
>  If I prepare a patch for this is there a normal protocol (i.e. pull
> request) that I can use so that the development team can review, decide and
> accept/discard a patch?  I think I have a working patch that converts the
> tuples to binary upon pass through.  However, I've only had a chance to
> test it against the npgsql driver, I have no idea if it introduced a
> breaking change for other drivers.
>

Great, thank you for doing that.

Please post the patch (diff format) to the postgres-xl-developers mailing
list and someone will take a look at it.

Thanks,

Mason


>  Aaron
>   ------------------------------
> *From:* Mason Sharp [msharp at translattice.com]
> *Sent:* Thursday, July 24, 2014 3:57 PM
>
> *To:* Aaron Jackson
> *Cc:* postgres-xl-general at lists.sourceforge.net
> *Subject:* Re: [Postgres-xl-general] Prepared statement failure?
>
>   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
>
>
>


-- 
Mason Sharp

TransLattice - http://www.translattice.com
TransLattice Elastic Database (TED)
Postgres-XL Support
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/private.cgi/postgres-xl-general-postgres-xl.org/attachments/20140908/f7809e8d/attachment.htm>


More information about the postgres-xl-general mailing list