SEO

--- On Fri, 11/30/07, Greg Cocks <gcocks@stoller.com> wrote:

> Using a MS product? Is this *maybe* related to the same
> 'issue' with
> NULLs from PostgreSQL to MS products i.e., in the
> <postgresql.conf>
> file, turning 'transform_null_equals' to
> 'on'?
>
> Likely not, but thought I would put it out there... :-)
> .. DEFAULT '-infinity', ...

I should probably clarify what I am seeing.

I have a table that has a time stamp field. The timestamp fields have two special values in postgres:
'infinity' which implies '+infinity'
and '-infinity'

when I create a recordset of records that have the value 'infinity' in the timestamp field all is well. The client side timestamp displays '9999-12-31 .... '.
However, when I try to create a recordset that has field that contains the '-infinity' values I get an error message.

Regards,
Richard Broesma Jr.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Richard Broersma Jr wrote:
> --- On Fri, 11/30/07, Greg Cocks <gcocks@stoller.com> wrote:
>
>> Using a MS product? Is this *maybe* related to the same
>> 'issue' with
>> NULLs from PostgreSQL to MS products i.e., in the
>> <postgresql.conf>
>> file, turning 'transform_null_equals' to
>> 'on'?
>>
>> Likely not, but thought I would put it out there... :-)
>> .. DEFAULT '-infinity', ...
>
> I should probably clarify what I am seeing.
>
> I have a table that has a time stamp field. The timestamp fields have two special values in postgres:
> 'infinity' which implies '+infinity'
> and '-infinity'

ODBC doesn't have such a concept.

> when I create a recordset of records that have the value 'infinity' in the timestamp field all is well. The client side timestamp displays '9999-12-31 .... '.

Though I have no confidence, I'm doing so.
Do you think it is an appropriate handling ?

> However, when I try to create a recordset that has field that contains the '-infinity' values I get an error message.

How do you think the driver should treat -infinity ?

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

--- On Sun, 12/2/07, Hiroshi Inoue <inoue@tpf.co.jp> wrote:

> Do you think it is an appropriate handling ?

This is a good question. I am not sure what an appropriate handling would be. My first test of '-infinity' (which resulted in a raised error) was with an ado recordset. Since you are questioning what is appropriate I thought that I should try a few different client front ends to see how they treated (+/-)infinity:

Client | -infinity | + infinity
ADO | <error> |9999-12-31 23:59:59|
Access/LT|1899-11-30 00:00:00|9999-12-31 23:59:59|
OOB(ODBC)|0001-01-03 00:00:00|9999-12-31 23:59:59|

ADO = ado recordset created in VB
Access/LT = a linked tabled in MS-Access (probably DAO/ODBC?)
OOB(ODBC) = Open Office.org Base ODBC connection to the Database.

Having done further tests, it seems that the error must be a bug with ADO since other clients have not a problem with making -infinity equal to the smallest time stamp that their respective data type can display. In my case, I do not really care about what time stamp values are displayed by the clients for (+/-) infinity since I use these values as an implied NULL value for time stamps. Unfortunately, ADO is broken and can't display any value for -infinity.

> How do you think the driver should treat -infinity ?

It seems that it already handles it correctly. I was mistaken in thinking that the translation of (+/-) infinity was handled by ODBC. )
Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Hello.

Richard Broersma Jr wrote:
> --- On Sun, 12/2/07, Hiroshi Inoue <inoue@tpf.co.jp> wrote:
>
>
>> Do you think it is an appropriate handling ?
>>
>
> This is a good question. I am not sure what an appropriate handling would be. My first test of '-infinity' (which resulted in a raised error) was with an ado recordset. Since you are questioning what is appropriate I thought that I should try a few different client front ends to see how they treated (+/-)infinity:
>
> Client | -infinity | + infinity
> ADO | <error> |9999-12-31 23:59:59|
> Access/LT|1899-11-30 00:00:00|9999-12-31 23:59:59|
> OOB(ODBC)|0001-01-03 00:00:00|9999-12-31 23:59:59|
>
> ADO = ado recordset created in VB
> Access/LT = a linked tabled in MS-Access (probably DAO/ODBC?)
> OOB(ODBC) = Open Office.org Base ODBC connection to the Database.
>
> Having done further tests, it seems that the error must be a bug with ADO since other clients have not a problem with making -infinity equal to the smallest time stamp that their respective data type can display. In my case, I do not really care about what time stamp values are displayed by the clients for (+/-) infinity since I use these values as an implied NULL value for time stamps. Unfortunately, ADO is broken and can't display any value for -infinity.
>
>
>> How do you think the driver should treat -infinity ?
>>
>
> It seems that it already handles it correctly. I was mistaken in thinking that the translation of (+/-) infinity was handled by ODBC. )
>
It should be handled by ODBC because of possible type conversions on the
ODBC driver layer. Take a look:
http://msdn2.microsoft.com/en-us/library/ms709280aspx. Table shows
required conversions. Also all Microsoft applications notify ODBC driver
by setting additional parameter during initialization. I think ADO
requests 'inifinity' value as SQL_C_CHAR and sure it doesn't expect it
to be 'infinity'.


> Regards,
> Richard Broersma Jr.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote:

> http://msdn2.microsoft.com/en-us/library/ms709280aspx.

I tried this link, but is says "content not found".

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Hello,

Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote:
>
>
>> http://msdn2.microsoft.com/en-us/library/ms709280aspx.
>>
>
> I tried this link, but is says "content not found".
>

Oh... Sorry, missed a dot before 'aspx'. Correct link:
http://msdn2.microsoft.com/en-us/library/ms709280.aspx

> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

--- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote:

> Also all Microsoft applications notify ODBC driver
> by setting additional parameter during initialization. I
> think ADO requests 'inifinity' value as SQL_C_CHAR and sure
> it doesn't expect it to be 'infinity'.

Actually, postgresql doesn't actually have a (+/-) infinity value. Notice:
http://www.postgresql.org/docs/8.3/s...e.html#AEN4999

+infinity = 5874897 AD
-infinity = 4713 BC

so the problem must be that ADO doesn't know what to do with dates as small as 4713 BCE.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote:
>
>
>> Also all Microsoft applications notify ODBC driver
>> by setting additional parameter during initialization. I
>> think ADO requests 'inifinity' value as SQL_C_CHAR and sure
>> it doesn't expect it to be 'infinity'.
>>
>
> Actually, postgresql doesn't actually have a (+/-) infinity value. Notice:
> http://www.postgresql.org/docs/8.3/s...e.html#AEN4999
>
> +infinity = 5874897 AD
> -infinity = 4713 BC
>
Are you sure these values are equal? Try this in PgAdmin:

create table t (t timestamp);
insert into t values ('-infinity');
insert into t values ('infinity');
insert into t values ('9999-12-31 23:59:59');
insert into t values ('5874897-12-31 23:59:59');
insert into t values ('5874897-12-31 23:59:59.999999999');
--insert into t values ('5874898-01-01 00:00:00');
select t::date, t::time, t::timestamp, t::varchar from t;

Cool behaviour: PostgreSQL 8.2.x thinks that 'infinity' = '-infinity' =
null if it was converted from timestamp to date or time....

By the way - you can't 'insert into t values ('5874898-01-01
00:00:00')', but you can 'insert into t values ('5874897-12-31
23:59:59.999999999')' - with interesting result
> so the problem must be that ADO doesn't know what to do with dates as small as 4713 BCE.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

--- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote:

> Are you sure these values are equal? Try this in PgAdmin:
>
> create table t (t timestamp);
> insert into t values ('-infinity');
> insert into t values ('infinity');
> insert into t values ('9999-12-31 23:59:59');
> insert into t values ('5874897-12-31 23:59:59');
> insert into t values ('5874897-12-31
> 23:59:59.999999999');
> --insert into t values ('5874898-01-01 00:00:00');
> select t::date, t::time, t::timestamp, t::varchar from t;

Now the real question is, "What does and ODBC client do with these value?".

Also, don't forget to include values for -infinity and other timestamps that are smaller than what the client side application can display.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hello, Happy New Year!

Richard Broersma Jr wrote:
> --- On Mon, 12/3/07, Andrei Kovalevski <andyk@commandprompt.com> wrote:
>
>
>> Are you sure these values are equal? Try this in PgAdmin:
>>
>> create table t (t timestamp);
>> insert into t values ('-infinity');
>> insert into t values ('infinity');
>> insert into t values ('9999-12-31 23:59:59');
>> insert into t values ('5874897-12-31 23:59:59');
>> insert into t values ('5874897-12-31
>> 23:59:59.999999999');
>> --insert into t values ('5874898-01-01 00:00:00');
>> select t::date, t::time, t::timestamp, t::varchar from t;
>>
>
> Now the real question is, "What does and ODBC client do with these value?".
>
> Also, don't forget to include values for -infinity and other timestamps that are smaller than what the client side application can display.
>

ODBC supports dates and timestamps as:
typedef struct tagTIMESTAMP_STRUCT
{
SQLSMALLINT year;
..........
} TIMESTAMP_STRUCT;

So, it can't show dates bigger then 32,767 or smaller then -32,768.

I think - correct driver behavour should be:
1) driver returns NULL value for SQL_DATE_STRUCT and SQL_TIMESTAMP_STRUCT
2) function return value should be SQL_SUCCESS_WITH_INFO;
3) driver also sets Informative Description for this error with actual
value - if possible.

--
Andrei Kovalevski
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster