[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