[Postgres-xl-general] Prepared statement failure?

Aaron Jackson ajackson at revionics.com
Mon Sep 8 13:11:10 PDT 2014


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.

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<mailto: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<mailto:msharp at translattice.com>]
Sent: Wednesday, July 23, 2014 7:18 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?




On Wed, Jul 23, 2014 at 5:27 PM, Aaron Jackson <ajackson at revionics.com<mailto: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<mailto: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<mailto:ajackson at revionics.com>]
Sent: Wednesday, July 23, 2014 3:55 PM
To: Mason Sharp
Cc: postgres-xl-general at lists.sourceforge.net<mailto: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<mailto:ajackson at revionics.com>]
Sent: Wednesday, July 23, 2014 2:57 PM
To: Mason Sharp
Cc: postgres-xl-general at lists.sourceforge.net<mailto: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<mailto:msharp at translattice.com>]
Sent: Wednesday, July 23, 2014 11:43 AM
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?




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





--
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/private.cgi/postgres-xl-general-postgres-xl.org/attachments/20140908/3b5b9397/attachment.htm>


More information about the postgres-xl-general mailing list