What the hell is going on?

On Wed, 18 Nov 2009, noreply(at)pgfoundry(dot)org wrote:
>
> Date: Wed, 18 Nov 2009 23:38:06 +0000 (UTC)
> From: noreply(at)pgfoundry(dot)org
> To: noreply(at)pgfoundry(dot)org
> Subject: [ODBC] [ psqlodbc-Bugs-1010731 ] Incorrect info for varchar(255), text columns
>
> Bugs item #1010731, was opened at 2009-11-18 15:55
> You can respond by visiting:
> http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010731&group_id=1000125
>
> Category: None
> Group: None
> Status: Open
> Resolution: None
> Priority: 3
> Submitted By: Farid Zidan (faridz)
> Assigned to: Nobody (None)
> Summary: Incorrect info for varchar(255), text columns
>
> Initial Comment:
> PostgreSQL ANSI ODBC driver 8.04.01 returns incorrect info for
> SQLColAttribute SQL_DESC_TYPE and/or SQL_DESC_OCTET_LENGTH
>
> Example,
> create table test_varchar(col1 varchar(20), col2 varchar(255), col3 text);
>
> I get the following information from SQLColAttribute:
> column SQL_DESC_TYPE SQL_DESC_OCTET_LENGTH
> col1 SQL_VARCHAR 20
> col2 SQL_LONGVARCHAR 255
> col3 SQL_LONGVARCHAR 8190
>
> col1 info is correct.
> col2 SQL_DESC_TYPE should be SQL_VARCHAR not SQL_LONGVARCHAR.
> col3 SQL_DESC_OCTET_LENGTH should be something large such as 2147483647 (2**31 -1)
>
> Attached is screenshot of the Firebird vs PostgreSQL ODBC driver results for the test table above (MS SQL Server ODBC driver
> returns the same info as the Firebird ODBC driver)
>
> ----------------------------------------------------------------------
>
> >Comment By: Farid Zidan (faridz)
> Date: 2009-11-18 18:38
>
> Message:
> It may be helpful to group the defaults in question in a group box that says "MS Access" or something similar and then one
> checkbox that will set all the
> options for MS Access, etc. Oracle ODBC driver has a tab called "Workaround" and another tab called "SQLServer Migration",
> for example.
>
> I am a little curious though, why MS SQL Server own ODBC driver or other ODBC drivers do not have such options, could it be
> that recent versions of MS Access
> correctly handle large varchar/TEXT columns and these options are no longer required? Anyway, it may very well be useful to
> keep those options just in case
> somebody, somewhere needs them.
>
> Regards
>
> ----------------------------------------------------------------------
>
> Comment By: Richard Broersma (rabroersma)
> Date: 2009-11-18 18:16
>
> Message:
> one last comment:
>
> notice that the maximum field size in PostgreSQL is 1GB.
>
> http://www.postgresql.org/about/
>
> Also the number of possible characters is less that this since there is some meta data associated with VARCHAR and TEXT
> columns.
>
> http://www.postgresql.org/docs/8.4/interactive/datatype-character.html
>
> I'm not sure how this relates to the correct value for SQL_DESC_OCTET_LENGTH.
>
> Regarding the choice of the default setting for these data types, I can only guess as to why the ODBC maintainers made the
> choices that they did. I believe that your view point is technical correct from a standards point of view.
>
> But if the vast majority of PostgreSQL ODBC users today are MS-Access users, I doubt they would have adopted PostgreSQL for
> their database back-end if it didn't automatic work for their use. So for them it is a nice convenience.
>
> On the other hand, PostgreSQL is probably far more recognized in the MS community today that it was back is the PG version 6
> days. So they might be will to take the time necessarily to read the manual for appropriate adjustments. Perhaps the time
> is ripe to change the defaults to values actually supported by PostgreSQL.
>
> ----------------------------------------------------------------------
>
> Comment By: Farid Zidan (faridz)
> Date: 2009-11-18 17:44
>
> Message:
> It may very well be so. A workaround for MS Access (doc also mentions Borland). Note that MS SQL Server own driver does not
> do
> anything of the kind. It reports the datatypes and size as they are at the database for the test table.
>
> I do not actively use MS Access, but for people that do and require this workaround, then it is good to have it. My issue is
> that
> everybody is getting the workaround without asking for it or requiring it and in the process the driver is masking the real
> datatype/size of the database table columns by default!
>
> The PostgreSQL ODBC driver by default should report the datatype information accurately as those at the server, so by default
> the
> limit on varchar should be the same as the server limit and the limit on TEXT datatype should be the same as the server
> limit. It
> is up to he client application to determine what buffer type/size to use for a very large varchar column (inline or pointer
> to
> allocated memory).
>
> That's how all other DBMS vendor ODBC drivers behave by default, MS SQL Server, Oracle, DB2, Informix, Firebird, Sybase ASE,
> SQLAnywhere, Ingres, just to name few that I have tested.
>
> For my test table I only get the incorrect info when using the PostreqSQL driver out-of-the box. I don't have to alter the
> options
> of any of mentioned DBMS ODBC drivers to get the expected results.
>
> Best regards
>
> ----------------------------------------------------------------------
>
> Comment By: Richard Broersma (rabroersma)
> Date: 2009-11-18 17:22
>
> Message:
> > I suspect the limit on the long varchar size was done as
> > I workaround for something, in that case only people that
> > need the workaround should get it and everybody who does
> > not need it or want it by default.
>
> It I were to make a guess, I would say that these setting were defaulted so that there would be an exact mapping to the
> MS-Access data types: text{255} and memo{8190}.
>
> I would expect that there is special magic to make the data type mappings work nicely with MS SQL Server using ODBC linked
> tables. I not sure how nicely Firebird ODBC driver would work when mapping data type to MS-Access.
>
> For example, try linking a table with a TEXT PRIMARY KEY (perfectly valid in PostgreSQL). While MS-Access will not allow its
> mapped memo data type to be assigned as a primary key.
>
> ----------------------------------------------------------------------
>
> Comment By: Farid Zidan (faridz)
> Date: 2009-11-18 17:08
>
> Message:
> Thanks. That is very related. I updated the driver page 1 options Max Varchar to 2000
> and Max LongVarChar to 2147483647 and now I get identical results to other ODBC
> drivers.
>
> I understand the need to set a limit to SQL_VARCHAR buffers, the default value 254 is
> very small a more reasonable value is 2000 or 8190.
>
> However, setting a limit on SQL_LONGVARCHAR by default to 8190 seems to be very odd.
> No other driver does that. If I define a column at the Server as TEXT then I want to
> get all its data and not the first 8190 characters (by default).
>
> I suspect the limit on the long varchar size was done as I workaround for something,
> in that case only people that need the workaround should get it and everybody who does
> not need it or want it by default.
>
> This issue is similar to http://pgfoundry.org/tracker/?
> func=detail&aid=1010516&group_id=1000125&atid=538
> where the driver does LF <-> LF/CR by default.
>
> I believe both are the wrong default behavior by the driver and by default the max on
> SQL_LONGVARCHAR column should be the same as the server supported max for the datatype
> TEXT (2**31 - 1) so user will not have to change anything to use the driver.
>
> OK, I need to update this issue title to say:
> Driver Max LongVarChar value should be the same as Server max for TEXT datatype by
> default, and update driver Max Varchar option to something bigger such as 2000 (like
> Oracle) or 8000 (like MS SQL Server), otherwise every user who wants to use the driver
> to handle TEXT columns bigger than 8190 will have to fiddle with the driver options
> which should be not required nor necessary if the driver used the server max size for
> the TEXT column like you expect to do
>
> ----------------------------------------------------------------------
>
> Comment By: Richard Broersma (rabroersma)
> Date: 2009-11-18 16:21
>
> Message:
> I have one thought that might be unrelated to your problem:
>
> Notice the ODBC driver setting for Max LongVarChar.
>
> http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/psqlodbc/psqlodbc/docs/config.html?rev=1.10&content-type=text/html
>
> I wonder if the ODBC driver is masking the actual size that a TEXT column could be?
>
> ----------------------------------------------------------------------
>
> You can respond by visiting:
> http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010731&group_id=1000125
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
>
> [ Note: This message contains email list management information ]
>

Why are these messages, if they are in fact sent via the pgsql-odbc
mailing list, not addressed to the mailing list?

Why is that mailing list, if the messages are in fact "sent via" that
mailing list, distributing messages that are not addressed to that
mailing list?

If they are in fact sent from somewhere in the PostgreSQL organisation,
why are they designed to sabotage email filters, by not using the
mailing list email address for posting, on which messages to the list
can be easily filtered?

Or, are these messages from some sinister organisation, spoofing some
PostgreSQL entity?

--
Bret Busby
Armadale
West Australia
..............

"So once you do know what the question actually is,
you'll know what the answer means."
- Deep Thought,
Chapter 28 of Book 1 of
"The Hitchhiker's Guide to the Galaxy:
A Trilogy In Four Parts",
written by Douglas Adams,
published by Pan Books, 1992

....................................................