Dang Minh Huong | 4 Feb 14:54 2016
Picon

Degradation of performance when upgrade psqlodbc to 09.03.0400

Hi,

I'm using psqlodbc version 09.00.0310 for my project. Recently i tried to upgrade to version 09.03.0400 and found that it is very slow in processing my batch (batch with milion of SELECT, UPDATE, INSERT statements).

[environment] 

OS: Redhat 6.7 x 2 nodes

PostgreSQL 9.1.19: Synchronouse Streaming replication & hot_standby.

DB connect method: psqlodbc/unixODBC.

[results of performance]

It took below timing to execute my batch

In version 09.00.0310: 11minute 

In version 09.03.0400: 53minute

When my batch run in 09.03.0400, ps command repeatedly show below processes.

Something delay the transmitting & replaying WAL in STANDBY db node?

-----

... UPDATE waiting for 0/xxx 

or 

... INSERT waiting for 0/yyy

------

Note that, my replication connection is working well, and above messages just display within a second. This phenomenon is not reproduced in version 09.00.0310.

I have confirmed release note. I thought changing of UseServerSidePrepare's default value made this perfomance problem but not. I don't know what change make it slower.

Sorry for lack of infomation but, is any big changes related to performance between above versions?

Best regards,

bocap.

Don Naumann | 2 Feb 20:11 2016

PostgreSQL 9.5 23bit ODBC Driver

There doesn’t seem to be a 32bit version of the ODBC driver available on the site (http://www.postgresql.org/ftp/odbc/versions/msi/) – the psqlodbc_09_05_0100-x86.zip file seems to only install the 64bit version.  Is there another place to download the driver?

 

 

Don Naumann, Data Warehouse Director

Phone: 678.266.9990 x306 - Fax: 925.888.1935

 

30 Mansell Court – Suite 220 - Roswell, GA 30076

Email: dnaumann <at> mydealerlot.com
Website:
www.mydealerlot.com
 

     Like us on Facebook      Visit our YouTube Channel

 

Ha Van | 2 Feb 16:23 2016
Picon

Supported Windows Version

Hello,

I am using the PostgreSQL ODBC driver version 9.2 on Windows and am trying to find documentation on which version of Windows is supported by this driver.  Are there any Windows version that is not compatible with this driver?  Any help would be greatly appreciated.

Thanks,
Ha Van
Viktor Trojanovic | 1 Feb 21:29 2016
Picon

Issues with PostgreSQL ODBC on Win 10

Hi,

I'm trying to debug a third party ODBC application but I can't seem to get the ODBC driver (latest version,  from psqlodbc_09_05_0100-x86.zip) to log anything under Windows 10.

The check fields CommLog as well as MyLog don't seem to have any effect at all. But also the ODBC Admin built-in tracing doesn't work. It's definitely not a problem with the tracer as it traces other ODBC connections just fine.

Is it maybe necessary to select a custom trace DLL for this to work? Or is this just a bug?

Viktor
Venkatesan, Sekhar | 28 Jan 17:53 2016

PostgreSQL: Autocommit through windows odbc driver doesnt work!!!

Hi All,

 

I am trying  to certify our product with PostgreSQL database and facing an issue with windows odbc driver and need your help to identify the problem.

Our application uses SQLDriverConnect to connect to PostgreSQL server. I am using PostgreSQL odbc driver version 9.4

SQLSetConnectAttr to enable autocommit as below:

Eg: SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON, 0)

 

But it seems autocommit is not set and hence any queries executed from other session never gets updated data in other session since autocommit of insert statements never happens.

Only workaround I am seeing is to explicitly issue “commit” to save the updates in the database.

 

Has anyone seen this issue earlier? I see a relevant issue in psqlODBC 09.05.0100 Release something like below:

https://odbc.postgresql.org/docs/release.html

1.     Don't reset autocommit when a connection is established

If autocommit is disabled on a connection, by calling SQLSetConnectAttr(SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0), before connecting with SQLDriverConnect(), autocommit was incorrectly reset back to on when the connection was established.

 

Is the above issue fixed? Also in my use-case, I want to enable autocommit at odbc driver level but even that doesn’t work.

The same application works in Linux OS when unix odbc driver is used. This seems to be specific to windows driver.

 

Please shed some light on this. Do ask me further question if you have any.

 

Thanks,

Sekhar

 

Tsunakawa, Takayuki | 28 Jan 09:20 2016

A query retrieving many rows crashes with psqlodbc-09.05.0100

Hello,

Some (perhaps simple) query which retrieves many rows crashes with psqlodbc-09.05.0100.  The same query
succeeds with psqlodbc-09.03.0400.

The call stack is as follows:

>	msvcr120.dll!strtoxl(localeinfo_struct * plocinfo=0x72b51870, const char * nptr=0x0808391c,
const char * * endptr=0x00000000, int ibase=10, int flags=0)  行 100 + 0x8 バイト	C++
 	msvcr120.dll!strtol(const char * nptr=0x0808391c, char * * endptr=0x00000000, int ibase=10)  行 236 +
0x7 バイト	C++
 	msvcr120.dll!atol(const char * nptr=0x0808391c)  行 56 + 0xc バイト	C
 	psqlodbc35w.dll!copy_and_convert_field(StatementClass_ * stmt=0x72b51870, unsigned int
field_type=4294967280, int atttypmod=44187360, void * valuei=0x0808391c, short fCType=-16, int
precision=0, void * rgbValue=0x01770360, long cbValueMax=4, long * pcbValue=0x0177035c, long *
pIndicator=0x0177035c)  行 1710 + 0xc バイト	C
 	psqlodbc35w.dll!copy_and_convert_field_bindinfo(StatementClass_ * stmt=0x72b51870, unsigned
int field_type=4294967280, int atttypmod=44187476, void * value=0x0808391c, int col=14)  行 732 +
0x3e バイト	C
 	psqlodbc35w.dll!SC_fetch(StatementClass_ * self=0x72b51870)  行 1742 + 0x12 バイト	C
 	psqlodbc35w.dll!PGAPI_ExtendedFetch(void * hstmt=0x72b51870, unsigned short fFetchType=65520,
long irow=1, unsigned long * pcrow=0x019e569c, unsigned short * rgfRowStatus=0x007f3b30, long
bookmark_offset=0, long rowsetSize=1)  行 1752 + 0xe バイト	C
 	psqlodbc35w.dll!SQLFetchScroll(void * StatementHandle=0x007f3b30, short FetchOrientation=5,
long FetchOffset=27326472)  行 225 + 0x15 バイト	C
 	odbc32.dll!_SQLFetchScroll <at> 12()  + 0x296 バイト	
 	msdasql.dll!CFetchRowsFetchScrollBmk::FetchRows()  + 0x383 バイト	
 	msdasql.dll!CImpIRowsetScroll::GetRowsAtBookmark()  + 0x32b バイト	
 	msdasql.dll!CImpIRowsetScroll::GetRowsAt()  + 0x341 バイト	
 	msadrh15.dll!CRowsetHelper::GetRowsAt()  + 0x9b バイト	
 	msado15.dll!CRecordset::MoveAbsolute()  + 0x523 バイト	
 	msado15.dll!CMemStream::SetModificationTime()  + 0x2c188 バイト	
 	msado15.dll!CRecordset::get_EOF()  + 0x1c5 バイト	
...

The same query also crashes with the unofficial psqlodbc-09.05.0101 that Inoue-san posted on his web site.

Could you investigate the cause?  I'll go into the code, too.

Regars, Takayuki Tsunakawa

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Tsunakawa, Takayuki | 28 Jan 07:27 2016

psqlODBC crashes upon memory allocation failures

Hello,

As I said here:

http://www.postgresql.org/message-id/0A3221C70F24FB45833433255569204D1F4DB1CF <at> G01JPEXMBYT05

psqlODBC ignores memory allocation failure in a number of places.  That causes the application crash due to
NULL dereference.  Actually, I ran into the crash of my 32-bit application under high load.

The attached patch is the fix for it.  I wish this will be included in the upcoming release.

Regards, Takayuki Tsunakawa

Attachment (crash_on_nomem.patch): application/octet-stream, 27 KiB

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Tsunakawa, Takayuki | 28 Jan 06:39 2016

Re: Please help solve various memory corruption failures

> I've been encountering various failures leading to application crash.  They
> all seem to be caused by some memory corruption bugs.  I tried to figure
> out the fix, but I couldn't.  I would really appreciate your help!
> 
> I'm using the latest release psqlodbc-09.03.0400 on Windows.  The
> application is multi-threaded 32-bit program.  The threads are independent
> workers, each of which uses ADO to perform simple data access:

I've just been able to solve this issue.  There were some bugs in psqlODBC and a communication library we are
using.  Let me explain those.

> (6) Abrupt socket close
> Throughout the test, these messages are frequently output in the PostgreSQL
> server log file:
> 
> LOG:  could not receive data from client: No connection could be made
> because the target machine actively refused it.
> LOG:  unexpected EOF on client connection

The communication library (not psqlODBC) mistakenly closed the same socket twice.  Depending on the
timing, that ended up closing a live socket which is being used for psqlODBC-postgres communication. 
This is the beginning of all mysterious crashes.

The sudden socket closure causes send()/recv() in psqlODBC to fail, leading to QR_next_tuple() and
CC_fetch_tuples() failures.  When CC_fetch_tuples() fails in CC_send_query_append(), the following
code fragment sets cmdres to retres.  Here, cmdres is just initialized and so has no error information.

[connection.c]
					if (!CC_fetch_tuples(res, self, cursor, &ReadyToReturn, &kill_conn))
					{
						if (QR_command_maybe_successful(res))
							retres = NULL;
						else
							retres = cmdres;
						aborted = TRUE;
					}

Returning from CC_send_query_append(), SC_execute() treats the result as success.  But the returned
QResult has no information about DECLARE+FETCH results.  Finally, SC_execute() accesses the memory
just freed by itself.

This problem does not occur with psqlodbc-09.05.0100.  I confirmed it by reviewing the source code and
actually running the same test.

> (5) NULL dereference
> The following line in SC_create_errorinfo() (statement.c) caused the crash.
> pgerror was NULL.  That means that malloc() in ER_Constructor() failed.
> NULL check is necessary somewhere.
> 
> 			strcpy(pgerror->sqlstate, EN_is_odbc3(env) ?
> 
> Statement_sqlstate[errornum].ver3str :
> 
> Statement_sqlstate[errornum].ver2str);

This bug still exists in the latest psqlodbc-09.05.0100.  I'll submit the patch later.

Regards, Takayuki Tsunakawa

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Dmitry Pogorelov | 20 Jan 22:46 2016
Picon

SUCCESS_WITH_INFO [01004] Fetched item was truncated error message

Hi,

I've got Microsoft Power BI (64 bit) installed on windows 7 64 bit and the latest PostgreSQL ODBC Driver 09.05.0100-x64 version. If you try to create the following table:

create table testpower_odbc_512
as select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

and try to get data from the table via odbc connection in the Power BI you'll get the following error message:

DataSource.Error: ODBC: SUCCESS_WITH_INFO [01004] Fetched item was truncated.

instead of real data.

From mylog I could get the following info:

[5292-0.163]PARSE: searchColInfo by attnum=1
[5292-0.163]colAttr: col 0 field_type=705 fi,ti=000000001AC75E80,000000000056A1F0
[5292-0.163]COLUMN_TYPE=12
[5292-0.167][[SQLColAttribute]][5292-0.167]PGAPI_ColAttributes: entering..col=1 1011 len=2048.
[5292-0.167]colAttr: col 0 field_type=705 fi,ti=000000001AC75E80,000000000056A1F0
[5292-0.168]PGAPI_ColAttributes: COLUMN_NAME = '?column?'
[5292-0.169][[SQLColAttribute]][5292-0.169]PGAPI_ColAttributes: entering..col=1 1008 len=0.
[5292-0.169]colAttr: col 0 field_type=705 fi,ti=000000001AC75E80,000000000056A1F0
[5292-0.170][[SQLColAttribute]][5292-0.170]PGAPI_ColAttributes: entering..col=1 14 len=2048.
[5292-0.170]colAttr: col 0 field_type=705 fi,ti=000000001AC75E80,000000000056A1F0
[5292-0.170][[SQLColAttribute]][5292-0.170]PGAPI_ColAttributes: entering..col=1 1003 len=0.
[5292-0.170]colAttr: col 0 field_type=705 fi,ti=000000001AC75E80,000000000056A1F0
[5292-0.171]PGAPI_ColAttributes: col 0, desc_length = 255
[5292-0.171][[SQLColAttribute]][5292-0.171]PGAPI_ColAttributes: entering..col=1 32 len=0.
[5292-0.171]colAttr: col 0 field_type=705 fi,ti=000000001AC75E80,000000000056A1F0
[5292-0.172][[SQLColAttribute]][5292-0.172]PGAPI_ColAttributes: entering..col=1 1006 len=0.
[5292-0.172]colAttr: col 0 field_type=705 fi,ti=000000001AC75E80,000000000056A1F0
[5292-0.172][[SQLColAttribute]][5292-0.172]PGAPI_ColAttributes: entering..col=1 1013 len=0.
[5292-0.172]colAttr: col 0 field_type=705 fi,ti=000000001AC75E80,000000000056A1F0
[5292-0.173]PGAPI_ColAttributes: col 0, octet_length = 255
[5292-0.208][SQLBindCol][5292-0.208]PGAPI_BindCol: entering...
[5292-0.208]**** PGAPI_BindCol: stmt = 000000001AC7CD60, icol = 1
[5292-0.208]**** : fCType=1 rgb=0000000012832F68 valusMax=512 pcb=0000000002C04860
[5292-0.208]extend_getdata_info: entering ... self=000000001AC7CFF8, gdata_allocated=0, num_columns=1
[5292-0.208]exit extend_gdata_info=000000001AD21EC0
[5292-0.208]       bound buffer[0] = 0000000012832F68
[5292-0.208][[SQLSetStmtAttr]] Handle=000000001AC7CD60 26,46094192
[5292-0.208]PGAPI_SetStmtAttr Handle=000000001AC7CD60 26,46094192(0000000002BF5770)
[5292-0.208][[SQLSetStmtAttr]] Handle=000000001AC7CD60 27,200
[5292-0.208]PGAPI_SetStmtAttr Handle=000000001AC7CD60 27,200(00000000000000C8)
[5292-0.208][[SQLFetch]][5292-0.208]PGAPI_ExtendedFetch: stmt=000000001AC7CD60 rowsetSize=200
[5292-0.208]SQL_FETCH_NEXT: num_tuples=1, currtuple=-1, rowst=-1
[5292-0.208]PGAPI_ExtendedFetch: new currTuple = -1
[5292-0.208]fetch_cursor=0, 000000001AC831E0->total_read=1
[5292-0.208]**** SC_fetch: non-cursor_result
[5292-0.208]fetch: cols=1, lf=0, opts = 000000001AC7CE40, opts->bindings = 000000001AD22070, buffer[] = 0000000012832F68
[5292-0.208]type = 705, atttypmod = -1
[5292-0.208]value = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
[5292-0.208]copy_and_convert: field_type = 705, fctype = 1, value = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxx', cbValueMax=512
[5292-0.208]DEFAULT: len = 512, ptr = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
[5292-0.218]    SQL_C_CHAR, default: len = 512, cbValueMax = 512, rgbValueBindRow = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxx'
[5292-0.218]copy_and_convert: retval = 3
[5292-0.218]STATEMENT WARNING: func=SC_fetch, desc='', errnum=-2, errmsg='Fetched item was truncated.'
[5292-0.218]fetch_cursor=0, 000000001AC831E0->total_read=1
[5292-0.218][[SQLGetDiagField]] Handle=(3,000000001AC7CD60) Rec=1 Id=4 info=(0000000000A3B7A0,12)
[5292-0.218]PGAPI_GetDiagField entering rec=1[5292-0.218]ER_ReturnError: status = -2, msg = #Fetched item was truncated.#
[5292-0.228]         szSqlState = '01004',len=27, szError='(null)'
[5292-0.228]PGAPI_GetDiagField exiting 0
[5292-0.228][[SQLGetDiagField]] Handle=(3,000000001AC7CD60) Rec=1 Id=-1248 info=(000000000045DC68,0)
[5292-0.228]PGAPI_GetDiagField entering rec=1[5292-0.228]PGAPI_GetDiagField exiting 0
[5292-0.228][[SQLGetDiagField]] Handle=(3,000000001AC7CD60) Rec=1 Id=-1247 info=(000000000045DBF0,0)
[5292-0.228]PGAPI_GetDiagField entering rec=1[5292-0.228]PGAPI_GetDiagField exiting 0
[5292-0.228][[SQLGetDiagRec]]
[5292-0.228]PGAPI_GetDiagRec entering type=3 rec=1
[5292-0.228]ER_ReturnError: status = -2, msg = #Fetched item was truncated.#
[5292-0.228]         szSqlState = '01004',len=27, szError='Fetched item was truncated.'
[5292-0.228]PGAPI_GetDiagRec exiting 0
[5292-0.228][[SQLGetDiagField]] Handle=(3,000000001AC7CD60) Rec=2 Id=4 info=(0000000000A3B7A0,12)
[5292-0.228]PGAPI_GetDiagField entering rec=2[5292-0.228]ER_ReturnError: status = -2, msg = #Fetched item was truncated.#
[5292-0.228]PGAPI_GetDiagField exiting 100

If I try to create the same table in MySQL and use their ODBC driver via Power BI everything will be OK. Please tell me - is such behavior OK for current PostgreSQL ODBC driver?
Thank you.

Best Regards
Dmitry Pogorelov
Alvaro Herrera | 19 Jan 23:28 2016
Picon
Gravatar

[Raiford <at> labware.com: Re: float8 column size defined as 15 instead of 53?]

----- Forwarded message from Raiford <at> labware.com -----

Date: Tue, 19 Jan 2016 17:12:33 -0700
From: Raiford <at> labware.com
To: pgsql-odbc-owner <at> postgresql.org
Subject: Re: float8 column size defined as 15 instead of 53?
Message-ID: <OFB569ED94.0C1AE84E-ON85257F40.00006986-87257F40.0000B402 <at> labware.com>

So I am finding conflicting information about this value.  The 
documentation for Column Size (
https://msdn.microsoft.com/en-us/library/ms711786%28v=vs.85%29.aspx) shows 
the value being the decimal precision.  I know from experience that I can 
get binary precision from SQL Server and from Oracle, so maybe there is 
something else going on that I don't understand.

I did look at the code for the pgsql-odbc driver and in 
pgtype_attr_column_size() the driver is answering 15 for float8 in the 
older driver that I am using.  In the current driver it is answering 17. 
Now I'm even more confused!  Surely there can only be 15 digits of decimal 
precision in a IEEE double floating point?  Is Postgres somehow able to 
include 17 digits precision if requesting the value as text (9 for single 
precision floats)?  In the current driver these values are from 
PG_REAL_DIGITS and PG_DOUBLE_DIGITS.  In the older driver they were simply 
hard coded.

Can anyone shed some light on this stuff?  Clearly there is something I'm 
missing.

Jon

From:   Jon Raiford/Employee/LW-US
To:     pgsql-odbc-owner <at> postgresql.org
Date:   01/19/2016 12:27 PM
Subject:        float8 column size defined as 15 instead of 53?

I'm seeing that the columns defined as float8 in Postgres are being 
described as SQL_FLOAT(15) instead of SQL_FLOAT(53).  From what I can 
tell, according to the ODBC spec, the column size should be reported as 
binary precision, but the Postgres ODBC driver is reporting the decimal 
precision.

https://msdn.microsoft.com/en-us/library/ms710150%28v=vs.85%29.aspx

"SQL_FLOAT  FLOAT(p)  Signed, approximate, numeric value with a binary 
precision of at least p. (The maximum precision is driver-defined.)[5]"

Unfortunately I'm not running on a current ODBC driver and it would be 
very inconvenient for me to load it at this moment.  Could someone with 
the latest driver verify whether or not SQLDescribeCol(), 
SQLGetTypeInfo(), or SQLColAttribute() still report this with the decimal 
precision?

I am happy to add a kludge to my code to treat numbers <= 15 as decimal 
precision, but at some point it would be nice to see this be fixed if it 
hasn't already.

I'm currently running the PostgreSQL Unicode 9.01.01.00 driver from 
30-Dec-2011.  I know it is quite old, so I'd be happy to hear that this is 
already resolved.  I'd also be happy to hear that I've misunderstood the 
spec and would love clarification.  For what its worth, I do see that both 
SQL Server and Oracle are reporting binary precision.

Thank you,
Jon

www.labware.com
Results Count

www.labware.com
Results Count

----- End forwarded message -----

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Alvaro Herrera | 19 Jan 23:27 2016
Picon
Gravatar

[Raiford <at> labware.com: float8 column size defined as 15 instead of 53?]

----- Forwarded message from Raiford <at> labware.com -----

Date: Tue, 19 Jan 2016 12:27:33 -0700
From: Raiford <at> labware.com
To: pgsql-odbc-owner <at> postgresql.org
Subject: float8 column size defined as 15 instead of 53?
Message-ID: <OFA97EBADB.D2727DE7-ON85257F3F.00697E76-87257F3F.006A723C <at> labware.com>

I'm seeing that the columns defined as float8 in Postgres are being 
described as SQL_FLOAT(15) instead of SQL_FLOAT(53).  From what I can 
tell, according to the ODBC spec, the column size should be reported as 
binary precision, but the Postgres ODBC driver is reporting the decimal 
precision.

https://msdn.microsoft.com/en-us/library/ms710150%28v=vs.85%29.aspx

"SQL_FLOAT  FLOAT(p)  Signed, approximate, numeric value with a binary 
precision of at least p. (The maximum precision is driver-defined.)[5]"

Unfortunately I'm not running on a current ODBC driver and it would be 
very inconvenient for me to load it at this moment.  Could someone with 
the latest driver verify whether or not SQLDescribeCol(), 
SQLGetTypeInfo(), or SQLColAttribute() still report this with the decimal 
precision?

I am happy to add a kludge to my code to treat numbers <= 15 as decimal 
precision, but at some point it would be nice to see this be fixed if it 
hasn't already.

I'm currently running the PostgreSQL Unicode 9.01.01.00 driver from 
30-Dec-2011.  I know it is quite old, so I'd be happy to hear that this is 
already resolved.  I'd also be happy to hear that I've misunderstood the 
spec and would love clarification.  For what its worth, I do see that both 
SQL Server and Oracle are reporting binary precision.

Thank you,
Jon

www.labware.com
Results Count

----- End forwarded message -----

--

-- 
Sent via pgsql-odbc mailing list (pgsql-odbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc


Gmane