[Oledb-dev] [patch] parameterized varchar values containing >64 characters are not accepted by PostgreSQL

[Victor Snezhko]

Shachar Shemesh <shachar at lingnu.com> writes:

>>  The problem occurs when pgoledb receives parameterized query like
>>"UPDATE mytable SET str=? WHERE key=123" with the parameter of type
>>DBTYPE_WSTR. Inside FillInValues, we use GetOIDType for DBTYPE_WSTR,
>>and it returns 19. PostgreSQL type for oid 19 is "name", it's used to
>>store identifiers. Unfortunately, identifier length is limited to 64
>>characters and is a compile-time constant in PostgreSQL, ABI depends
>>on it. Therefore, at present pgoledb can't pass strings longer than 64
>>characters as parameters.
>>
> Well spotted!
>
>>Any objections?
>>  
> Well, knowing how the postgresql internal type system works, it is
> better to pass strings as "text" rather than "varchar".

Hmm, could you explain why? I may be wrong here, but as far as I
understood from src/backend/utils/adt/varchar.c, varchar internally
enforces length checks (if length is supplied), which is generally a
good thing. Not sure about text.

> Other then that, I also rearranged the entire table so that it has a
> "forced translation" lines at the begining, followed by the lines we
> don't really care about (that are presented in ascending order for clarity).

Good, the resulting table looks more tasty than with my patch.
Thanks for the fast commit.

> I had some doubts regarding sending timestamp vs. sending timestamptz.
> At the end I decided to send timestamptz. This means that the following
> holds:
> create table example ( a timestamp, b timestamptz );
> insert into example values ( ?, ? ); ( both values are 'Wed May  3
> 11:27:54 IDT 2006 (GMT+0300)')
> select * from example;
>
> If the server is in the same timezone as the clients, this is what will
> happen:
> 1. For the insert, both values will be translated into 'Wed May  3
> 8:27:54 2006' and sent to the server (information does not include the
> timezone).
> 2. The first value will be translated back into 'Wed May  3 11:27:54
> 2006' and stored in a.
> 3. The second value will be stored as is in b.
> 4. The select will return 'Wed May  3 11:27:54 2006' for the first field
> and 'Wed May  3 8:27:54 2006' for the second.
> 5. The conversion function will keep the first field as is, and
> translate the second one to 'Wed May  3 11:27:54 2006'
>
> So, as long as client and server are in the same time zone, and agree on
> the interpretation of daylight saving, we are ok. Now here's the part
> where it starts to get confusing.
> If server is, say, in Moscow time zone, 1 is the same. For 2:
> 2. The first value will be translated into 'Wed May  3 12:27:54 2006'
> and stored in a.
> 3 is unchanged
> 4. The select will return 'Wed May  3 12:27:54 2006' for a and 'Wed May 
> 3 8:27:54 2006' for b.
> 5. The select will return 'Wed May  3 12:27:54 2006' for a and 'Wed May 
> 3 11:27:54 2006' for b.
>
> So, if the client and server are in different time zones, a simple store
> and then retrieve returns the correct value for timestamptz and an
> incorrect value for timestamp. The problem is that if we reverese my
> decision from above:
>
> 1. Insert - both values are sent as 'Wed May  3 11:27:54 2006'
> 2. Server translates second value to 'Wed May  3 7:27:54 2006'
> 3. Select returns 'Wed May  3 11:27:54 2006' for a and 'Wed May  3
> 7:27:54 2006' for b.
> 4. Client leaves a as is, and translates b into 'Wed May  3 10:27:54 2006'
>
> So what we get is that either way, either timestamp or timestamptz are
> reported incorrectly if the server's timezone is not aligned with the
> client's timezone.
>
> Here are the considerations:
> For using "timestamp":
> - This is the native OleDB type
> - Avoids double conversion
>
> Against:
> - TimestampTZ is the more correct way of doing things. Prevents problems
> with clients from different timezones working on the same database.
>
> I think I'll do the following:
> Right now we'll leave it as is (using timestamptz), hoping that if
> someone complains we can tell them to either switch their DB to
> "timestamptz", or to make sure that the server and clients both use the
> same timezone.

Yes, let it be this way.
BTW, I haven't yet written binary conversion functions, so timestamps
aren't passed anyway.

> Long term, we'll try to add a caching mechanism for this lookup. 

Hmm... I understood everything until this moment. Lookup?

> We'll also place a connection string optional element that tells
> PgOleDb to reverse this decision. This will be done by injecting a
> cache entry upon connection startup.

-- 
WBR, Victor V. Snezhko
E-mail: snezhko at indorsoft.ru