Quick Links

Re: TypeInfoCache

Lists:	pgsql-jdbc

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Subject:	TypeInfoCache
Date:	2007-12-19 14:42:51
Message-ID:	47692DEB.9070307@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Hello dear developers,

I just updated the TypeInfoCache to return LONGVARCHAR for the
PostgreSQL Type text. Currently VARCHAR(0) is returned, which seems not
correct in my eyes, and confuses some major software packets like
Chrystal Reports.

Patch attached,
with best regards,
Daniel Migowski
--

|¯¯|¯¯| *IKOffice GmbH Daniel Migowski*
| | |/| Mail: dmigowski ÄT ikoffice.de
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| http://www.ikoffice.de Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864

Attachment	Content-Type	Size
pgjdbc.patch	text/plain	1.3 KB

From:	Gregory Stark <stark(at)enterprisedb(dot)com>
To:	"Daniel Migowski" <dmigowski(at)ikoffice(dot)de>
Cc:	<pgsql-jdbc(at)postgresql(dot)org>
Subject:	Re: TypeInfoCache
Date:	2007-12-19 15:14:30
Message-ID:	877ijan2cp.fsf@oxford.xeocode.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

"Daniel Migowski" <dmigowski(at)ikoffice(dot)de> writes:

> Hello dear developers,
>
> I just updated the TypeInfoCache to return LONGVARCHAR for the PostgreSQL Type
> text. Currently VARCHAR(0) is returned, which seems not correct in my eyes, and
> confuses some major software packets like Chrystal Reports.

Why?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Cc:	Gregory Stark <stark(at)enterprisedb(dot)com>
Subject:	Re: TypeInfoCache
Date:	2007-12-19 15:38:56
Message-ID:	47693B10.3040804@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Gregory Stark schrieb:
> "Daniel Migowski" <dmigowski(at)ikoffice(dot)de> writes:
>
>> Hello dear developers,
>>
>> I just updated the TypeInfoCache to return LONGVARCHAR for the PostgreSQL Type
>> text. Currently VARCHAR(0) is returned, which seems not correct in my eyes, and
>> confuses some major software packets like Chrystal Reports.
>>
>
> Why?
Because VARCHAR (in my understanding) has some limit, like 256 or 50 or
even 8192, whatever. LONGVARCHAR is unlimited as I understand and much
better matches what i understood what "text" is for. While looking at
the JDBC specification "JSR-000221 JDBC 4.0 Final Release Evaluation",
they didn't make a clear statement on this, but AFAIK varchar always
carries a length with it. "text" does not, so its a LONGVARCHAR. It
doesn't matters, because all functions to get the data are equally
defined for both datatypes (spec page 199, table B-6).

At all, the driver returns a length of -1 for the "text" type, since its
no varchar type, and software using the metadata might expect to get
something >0 for the length of a VARCHAR, like defined in the SQL spec
(don't know the page there, but the postgresql handbook says, varchar
without a parameter is a postgres extension).

With best regards,
Daniel Migowski
--

|¯¯|¯¯| *IKOffice GmbH Daniel Migowski*
| | |/| Mail: dmigowski(at)ikoffice(dot)de <mailto:dmigowski(at)ikoffice(dot)de>
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| http://www.ikoffice.de Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Cc:	Gregory Stark <stark(at)enterprisedb(dot)com>
Subject:	Re: TypeInfoCache
Date:	2007-12-19 15:45:15
Message-ID:	47693C8B.3000603@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Daniel Migowski schrieb:
<blockquote cite="mid:47693B10(dot)3040804(at)ikoffice(dot)de" type="cite">Gregory
Stark schrieb:
 
<blockquote type="cite">"Daniel Migowski"
<a class="moz-txt-link-rfc2396E" href="mailto:dmigowski(at)ikoffice(dot)de"><dmigowski(at)ikoffice(dot)de></a> writes:
 
 
<blockquote type="cite">Hello dear developers,
 
 
I just updated the TypeInfoCache to return LONGVARCHAR for the
PostgreSQL Type
 
text. Currently VARCHAR(0) is returned, which seems not correct in my
eyes, and
 
confuses some major software packets like Chrystal Reports.
 
    </blockquote>
 
Why?
 
</blockquote>
Because VARCHAR (in my understanding) has some limit, like 256 or 50 or
even 8192, whatever. LONGVARCHAR is unlimited as I understand and much
better matches what i understood what "text" is for. While looking at
the JDBC specification "JSR-000221 JDBC 4.0 Final Release Evaluation",
they didn't make a clear statement on this, but AFAIK varchar always
carries a length with it. "text" does not, so its a LONGVARCHAR. It
doesn't matters, because all functions to get the data are equally
defined for both datatypes (spec page 199, table B-6).
 
 
At all, the driver returns a length of -1 for the "text" type, since
its no varchar type, and software using the metadata might expect to
get something >0 for the length of a VARCHAR, like defined in the
SQL spec (don't know the page there, but the postgresql handbook says,
varchar without a parameter is a postgres extension).
 
 
With best regards,
 
Daniel Migowski
 
</blockquote>
Btw, a varchar without length in PostgreSQL should be treated as
LONGVARCHAR, too, but i didn't implement that for now. 
 
With best regards, 
Daniel Migowski 
<div class="moz-signature">-- 
<pre> |¯¯|¯¯| IKOffice GmbH Daniel Migowski
| | |/| Mail: <a
href="mailto:dmigowski(at)ikoffice(dot)de">dmigowski(at)ikoffice(dot)de</a>
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| <a href="http://www.ikoffice.de">http://www.ikoffice.de</a> Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864</pre>
</div>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	2.6 KB

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Cc:	Gregory Stark <stark(at)enterprisedb(dot)com>
Subject:	Re: TypeInfoCache
Date:	2007-12-19 15:50:32
Message-ID:	47693DC8.1010809@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Daniel Migowski schrieb:
<blockquote cite="mid:47693B10(dot)3040804(at)ikoffice(dot)de" type="cite">Gregory
Stark schrieb:
 
<blockquote type="cite">"Daniel Migowski"
<a class="moz-txt-link-rfc2396E" href="mailto:dmigowski(at)ikoffice(dot)de"><dmigowski(at)ikoffice(dot)de></a> writes:
 
 
<blockquote type="cite">Hello dear developers,
 
 
I just updated the TypeInfoCache to return LONGVARCHAR for the
PostgreSQL Type
 
text. Currently VARCHAR(0) is returned, which seems not correct in my
eyes, and
 
confuses some major software packets like Chrystal Reports.
 
    </blockquote>
 
Why?
 
</blockquote>
Because VARCHAR (in my understanding) has some limit, like 256 or 50 or
even 8192, whatever. LONGVARCHAR is unlimited as I understand and much
better matches what i understood what "text" is for. While looking at
the JDBC specification "JSR-000221 JDBC 4.0 Final Release Evaluation",
they didn't make a clear statement on this, but AFAIK varchar always
carries a length with it. "text" does not, so its a LONGVARCHAR. It
doesn't matters, because all functions to get the data are equally
defined for both datatypes (spec page 199, table B-6).
 
 
At all, the driver returns a length of -1 for the "text" type, since
its no varchar type, and software using the metadata might expect to
get something >0 for the length of a VARCHAR, like defined in the
SQL spec (don't know the page there, but the postgresql handbook says,
varchar without a parameter is a postgres extension).
 
 
With best regards,
 
Daniel Migowski
 
</blockquote>
See <a class="moz-txt-link-freetext" href="http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html">http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html</a>,
Chapter 8.9.7, how others map a very large text type. The biggest they
support as varchar is 8.000 chars (MS SQL Server). Everything over that
is LONGVARCHAR (and "text" in postgres is up to 1GB). 
 
With best regards, 
Daniel Migowski 
 
<div class="moz-signature">-- 
<pre> |¯¯|¯¯| IKOffice GmbH Daniel Migowski
| | |/| Mail: <a
href="mailto:dmigowski(at)ikoffice(dot)de">dmigowski(at)ikoffice(dot)de</a>
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| <a href="http://www.ikoffice.de">http://www.ikoffice.de</a> Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864</pre>
</div>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	2.9 KB

From:	Gregory Stark <stark(at)enterprisedb(dot)com>
To:	"Daniel Migowski" <dmigowski(at)ikoffice(dot)de>
Cc:	<pgsql-jdbc(at)postgresql(dot)org>
Subject:	Re: TypeInfoCache
Date:	2007-12-19 16:11:25
Message-ID:	873atymzpu.fsf@oxford.xeocode.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

"Daniel Migowski" <dmigowski(at)ikoffice(dot)de> writes:

>> Why?
>
> Because VARCHAR (in my understanding) has some limit, like 256 or 50 or even
> 8192, whatever.

yes, 2GB, same as text.

> LONGVARCHAR is unlimited as I understand and much better
> matches what i understood what "text" is for.

Well you haven't explained what you understand "text" is for but in Postgres
they can be used pretty much interchangeably.

I think this has come up before, you should check the mail archives. The
problem is that describing "text" as if it's not a simple varchar type of type
confuses other applications into restricting what you can do with it. They
assume it has the kind of restrictions other databases impose.

Generally in Postgres you're probably best off using "text" unless you have
some specific limit you need to impose. In most cases Postgres will silently
cast your varchars to text when necessary but every now and then you might
find a case where it doesn't and fails to use an index or optimize a query
where it could.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	Gregory Stark <stark(at)enterprisedb(dot)com>
Cc:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-19 18:17:46
Message-ID:	4769604A.9050006@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Gregory Stark schrieb:
<blockquote cite="mid:873atymzpu(dot)fsf(at)oxford(dot)xeocode(dot)com" type="cite">
<pre wrap="">"Daniel Migowski" <a class="moz-txt-link-rfc2396E" href="mailto:dmigowski(at)ikoffice(dot)de"><dmigowski(at)ikoffice(dot)de></a> writes:
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="">Why?
</pre>
</blockquote>
<pre wrap="">Because VARCHAR (in my understanding) has some limit, like 256 or 50 or even
8192, whatever.
</pre>
</blockquote>
<pre wrap="">
yes, 2GB, same as text.
</pre>
</blockquote>
I meant the limit you give it. Not the internal one. VARCHAR(50) has a
limit of 50, right?
<blockquote cite="mid:873atymzpu(dot)fsf(at)oxford(dot)xeocode(dot)com" type="cite">
<blockquote type="cite">
<pre wrap="">LONGVARCHAR is unlimited as I understand and much better
matches what i understood what "text" is for.
</pre>
</blockquote>
<pre wrap="">
Well you haven't explained what you understand "text" is for but in Postgres
they can be used pretty much interchangeably.
</pre>
</blockquote>
I think a VARCHAR(50) and text are not interchangeable. 
<blockquote cite="mid:873atymzpu(dot)fsf(at)oxford(dot)xeocode(dot)com" type="cite">
<pre wrap="">
I think this has come up before, you should check the mail archives. The
problem is that describing "text" as if it's not a simple varchar type of type
confuses other applications into restricting what you can do with it. They
assume it has the kind of restrictions other databases impose.
</pre>
</blockquote>
Which restrictions does an JDBC-LONGVARCHAR impose? Read the JDBC spec,
please, where they say they are interchangeable reagrding all Access
methods? Like in PostgreSQL. But a LONGVARCHAR is IMHO commonly
regarded as "very much text", while a VARCHAR(n) is regarded as "up to
n chars" of text. 
<blockquote cite="mid:873atymzpu(dot)fsf(at)oxford(dot)xeocode(dot)com" type="cite">
<pre wrap="">Generally in Postgres you're probably best off using "text" unless you have
some specific limit you need to impose. In most cases Postgres will silently
cast your varchars to text when necessary but every now and then you might
find a case where it doesn't and fails to use an index or optimize a query
where it could.
</pre>
</blockquote>
This one is new to me. Does this mean even storage is done the same for
text and varchar? Does this mean I could savely convert all my
varchar's to text (if my client application accepts this?) 
 
With best regards, 
Daniel Migowski 
 
PS: Now searching the archives... 
 
<div class="moz-signature">-- 
<pre> |¯¯|¯¯| IKOffice GmbH Daniel Migowski
| | |/| Mail: <a
href="mailto:dmigowski(at)ikoffice(dot)de">dmigowski(at)ikoffice(dot)de</a>
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| <a href="http://www.ikoffice.de">http://www.ikoffice.de</a> Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864</pre>
</div>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	3.3 KB

From:	Oliver Jowett <oliver(at)opencloud(dot)com>
To:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
Cc:	Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-19 21:14:47
Message-ID:	476989C7.8020307@opencloud.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Daniel Migowski wrote:

> I think a VARCHAR(50) and text are not interchangeable.

But a "varchar" (with no limit) and "text" *are* interchangeable, which
is why we identify text as VARCHAR

-O

From:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To:	Oliver Jowett <oliver(at)opencloud(dot)com>
Cc:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 01:25:44
Message-ID:	1811.1198113944@sss.pgh.pa.us
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> Daniel Migowski wrote:
>> I think a VARCHAR(50) and text are not interchangeable.

> But a "varchar" (with no limit) and "text" *are* interchangeable, which
> is why we identify text as VARCHAR

But note that varchar-with-no-limit is itself a Postgres-ism: it's
not allowed by the standard.

regards, tom lane

From:	"Jan de Visser" <jdevisser(at)digitalfairway(dot)com>
To:	pgsql-jdbc(at)postgresql(dot)org
Subject:	TypeInfoCache
Date:	2007-12-20 01:39:29
Message-ID:	1159c1e90712191739p77fe2335odc1b883d2076c9a5@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

On 12/19/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
> > Daniel Migowski wrote:
> >> I think a VARCHAR(50) and text are not interchangeable.
>
> > But a "varchar" (with no limit) and "text" *are* interchangeable, which
> > is why we identify text as VARCHAR
>
> But note that varchar-with-no-limit is itself a Postgres-ism: it's
> not allowed by the standard.

I was about to write the same thing. For educational value, this is
what a popular closed-source dbms will tell you:

SQL> create table a ( a varchar );
create table a ( a varchar )
*
ERROR at line 1:
xxx-00906: missing left parenthesis

SQL> create table a (a varchar(4001));
create table a (a varchar(4001))
*
ERROR at line 1:
xxx-00910: specified length too long for its datatype

SQL> create table a (a varchar(4000));

Table created.

That's right, textual data longer than 4000 characters needs to go
through the CLOB interface. And my license doesn't permit me to say
what that does to performance :)

So what Daniel is trying to say here is that Crystal Report probably
croaks on a column for the type is Types.VARCHAR but which does not
have a maximum length associated with it.

I think his patch is good.

jan

From:	Kris Jurka <books(at)ejurka(dot)com>
To:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
Cc:	Oliver Jowett <oliver(at)opencloud(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 04:41:05
Message-ID:	Pine.BSO.4.64.0712192320120.16373@leary.csoft.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

On Wed, 19 Dec 2007, Tom Lane wrote:

> Oliver Jowett <oliver(at)opencloud(dot)com> writes:
>
>> But a "varchar" (with no limit) and "text" *are* interchangeable, which
>> is why we identify text as VARCHAR
>
> But note that varchar-with-no-limit is itself a Postgres-ism: it's
> not allowed by the standard.
>

So should varchar(10000000) be returned as VARCHAR or LONGVARCHAR?

Right now we return 0 for the precision of text or varchar without length.
Perhaps we should return something else for that similar to how we changed
the result of ResultSetMetaData.getColumnDisplaySize to return
Integer.MAX_VALUE instead of -1 for types without lengths.

Kris Jurka

From:	Gregory Stark <stark(at)enterprisedb(dot)com>
To:	"Daniel Migowski" <dmigowski(at)ikoffice(dot)de>
Cc:	<pgsql-jdbc(at)postgresql(dot)org>
Subject:	Re: TypeInfoCache
Date:	2007-12-20 10:04:59
Message-ID:	87k5n9adh0.fsf@oxford.xeocode.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

"Gregory Stark" <stark(at)enterprisedb(dot)com> writes:

> "Daniel Migowski" <dmigowski(at)ikoffice(dot)de> writes:
>
>>> Why?
>>
>> Because VARCHAR (in my understanding) has some limit, like 256 or 50 or even
>> 8192, whatever.
>
> yes, 2GB, same as text.

Oops, sorry, 1GB.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc:	Oliver Jowett <oliver(at)opencloud(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 10:14:44
Message-ID:	476A4094.6070601@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-15"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thank you for your opinions so far. I just looked through the mailing
lists to get the points from former discussions and try to sum it all
up here now: 
<ul>
<li>At first, VARCHAR as defined by SQL and as used by all other JDBC
drivers always has a upper limit. LONGVARCHAR is for very large
quantities of text, so IMHO returning a "text" or an unrestricted
"varchar" as VARCHAR just breaks the specs and the expectations.</li>
<li>It is said by Oliver Jowett, that "varchar" (with no limit) and
"text" *are* interchangeable. This is completly
true. But then *both* have to be described by the driver as
LONGVARCHAR, not as VARCHAR. As Tom Lane said, VARCHAR without length
is a postgres specific extension. </li>
<li>The JDBC-Spec recommends for Fieldtypes of type LONGVARCHAR to
use the getStream() function to get the data, instead of getString()
for VARCHAR. A few years ago the argument was, that an application
should use getString() also for "text" types, since the data is on the
heap already in the current driver design. This has some flaws: 
1. The internals of the driver just don't care when programming a well
defined interface like JDBC. 
2. An application accessing a LONGVARCHAR might very well just use the
first 100 chars to display a field preview, so a getStream() call might
be completly reasonable. Note it could be up to one Gig of data. 
3. The drivers stores the data internally as a byte[] and wrapped it
into a string when the getString() method is called. This clones the
data anyway, so for very large data getString() is even more
inperformant than getStream() which pulls only a bit of data. If the
application wants everything, it will call getString() anyway,
regardless of the type... 
4. An application, that knows the data stored in the db, because is db
schema is by the db developer, can also use getString() on a
LONGVARCHAR if it knows, that the "text" field shouldn't very big. 
</li>
<li>A real generic JDBC application will also work with other JDBC
drivers, and will assume all behave the same. As I already stated, this
Link (<a
href="http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html">http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html</a>)
at 8.9.7 shows what other DB drivers deliver. 
</li>
<li>Someone told a few years ago, that all their data in Postgres is
stored as "text" and that exporting this as LONGVARCHAR would break its
application. WTF? He declares unbounded text fields and then his
application should handle them.</li>
<li>For backwards compatibily we could have users switch from "text"
to "varchar" without bounds, which will also be shown as VARCHAR with
my patch. My patch currently jsut affects the "text" type.</li>
<li>The current driver implementation breaks ORM mappers, and any
other software, that tries to understand the database schema. As far as
I see, those software completly reasonable relies on a VARCHAR
returning a length</li>
</ul>
Please give me any good reasons not to apply my patch, with would
further improve standards conformance. 
With best regards, 
 
Daniel Migowski 
 
 
<div class="moz-signature">-- 
<pre> |¯¯|¯¯| IKOffice GmbH Daniel Migowski
| | |/| Mail: <a
href="mailto:dmigowski(at)ikoffice(dot)de">dmigowski(at)ikoffice(dot)de</a>
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| <a href="http://www.ikoffice.de">http://www.ikoffice.de</a> Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864</pre>
</div>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	4.0 KB

From:	Oliver Jowett <oliver(at)opencloud(dot)com>
To:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
Cc:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 10:43:42
Message-ID:	476A475E.2020108@opencloud.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Daniel Migowski wrote:

> Please give me any good reasons not to apply my patch, with would
> further improve standards conformance.

My main concern is that 'text' is a very common type to use in
PostgreSQL based designs, and that JDBC applications are more likely to
understand how to interpret a field that claims to be VARCHAR than one
that is LONGVARCHAR, given that LONGVARCHAR is a relatively strange type
and at best poorly defined.

i.e. - there are likely to be applications out there that depend on the
current behaviour - what are you going to do to support them?

This is the first time that mapping 'text' to LONGVARCHAR has been
suggested, as far as I can recall, so I think your "this breaks ORM
mappers and anything else that tries to understand the database schema"
claim is perhaps a bit of an exaggeration. If it does, where are all the
bug reports?

-O

From:	Oliver Jowett <oliver(at)opencloud(dot)com>
To:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
Cc:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 10:48:31
Message-ID:	476A487F.9070006@opencloud.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Oliver Jowett wrote:
> Daniel Migowski wrote:
>
>> Please give me any good reasons not to apply my patch, with would
>> further improve standards conformance.
>
> My main concern is that 'text' is a very common type to use in
> PostgreSQL based designs, and that JDBC applications are more likely to
> understand how to interpret a field that claims to be VARCHAR than one
> that is LONGVARCHAR, given that LONGVARCHAR is a relatively strange type
> and at best poorly defined.

Also worth noting that 'text' is commonly used even for very short
strings. Do you really want to hint to JDBC clients that they should be
using the streaming interface and expect to deal with very long strings
in that common case? I suspect (with no hard data to back it up,
admittedly!) that 'text' fields in general use are more commonly under
100 characters than over a megabyte.

-O

From:	Gregory Stark <stark(at)enterprisedb(dot)com>
To:	"Daniel Migowski" <dmigowski(at)ikoffice(dot)de>
Cc:	"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Oliver Jowett" <oliver(at)opencloud(dot)com>, <pgsql-jdbc(at)postgresql(dot)org>
Subject:	Re: TypeInfoCache
Date:	2007-12-20 10:51:38
Message-ID:	87d4t1abb9.fsf@oxford.xeocode.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

"Daniel Migowski" <dmigowski(at)ikoffice(dot)de> writes:

You keep sending blank emails.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

From:	Kris Jurka <books(at)ejurka(dot)com>
To:	Oliver Jowett <oliver(at)opencloud(dot)com>
Cc:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 10:53:30
Message-ID:	Pine.BSO.4.64.0712200547450.15638@leary.csoft.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

On Thu, 20 Dec 2007, Oliver Jowett wrote:

> My main concern is that 'text' is a very common type to use in PostgreSQL
> based designs, and that JDBC applications are more likely to understand how
> to interpret a field that claims to be VARCHAR than one that is LONGVARCHAR,
> given that LONGVARCHAR is a relatively strange type and at best poorly
> defined.

This is my concern as well, which is why I suggested that changing the
precision value might be a better solution. Daniel, any opinion on that
alternative?

Kris Jurka

From:	Kris Jurka <books(at)ejurka(dot)com>
To:	Gregory Stark <stark(at)enterprisedb(dot)com>
Cc:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Oliver Jowett <oliver(at)opencloud(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 10:54:57
Message-ID:	Pine.BSO.4.64.0712200553490.15638@leary.csoft.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

On Thu, 20 Dec 2007, Gregory Stark wrote:

> You keep sending blank emails.
>

Just HTML only emails.

Kris Jurka

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Cc:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Oliver Jowett <oliver(at)opencloud(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>
Subject:	Re: TypeInfoCache
Date:	2007-12-20 10:57:59
Message-ID:	476A4AB7.90509@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

[Resend... the first one seemed to be blank for at least Gregory Stark]

Thank you for your opinions so far. I just looked through the mailing
lists to get the points from former discussions and try to sum it all up
here now:

* At first, VARCHAR as defined by SQL and as used by all other JDBC
drivers always has a upper limit. LONGVARCHAR is for very large
quantities of text, so IMHO returning a "text" or an unrestricted
"varchar" as VARCHAR just breaks the specs and the expectations.
* It is said by Oliver Jowett, that "varchar" (with no limit) and
"text" *are* interchangeable. This is completly true. But then
**both** have to be described by the driver as LONGVARCHAR, not as
VARCHAR. As Tom Lane said, VARCHAR without length is a postgres
specific extension.
* The JDBC-Spec recommends for Fieldtypes of type LONGVARCHAR to use
the getStream() function to get the data, instead of getString()
for VARCHAR. A few years ago the argument was, that an application
should use getString() also for "text" types, since the data is on
the heap already in the current driver design. This has some flaws:
1. The internals of the driver just don't care when programming a
well defined interface like JDBC.
2. An application accessing a LONGVARCHAR might very well just use
the first 100 chars to display a field preview, so a getStream()
call might be completly reasonable. Note it could be up to one Gig
of data.
3. The drivers stores the data internally as a byte[] and wrapped
it into a string when the getString() method is called. This
clones the data anyway, so for very large data getString() is even
more inperformant than getStream() which pulls only a bit of data.
If the application wants everything, it will call getString()
anyway, regardless of the type...
4. An application, that knows the data stored in the db, because
is db schema is by the db developer, can also use getString() on a
LONGVARCHAR if it knows, that the "text" field shouldn't very big.
* A real generic JDBC application will also work with other JDBC
drivers, and will assume all behave the same. As I already stated,
this Link
(http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html)
at 8.9.7 shows what other DB drivers deliver.
* Someone told a few years ago, that all their data in Postgres is
stored as "text" and that exporting this as LONGVARCHAR would
break its application. WTF? He declares unbounded text fields and
then his application should handle them.
* For backwards compatibily we could have users switch from "text"
to "varchar" without bounds, which will also be shown as VARCHAR
with my patch. My patch currently jsut affects the "text" type.
* The current driver implementation breaks ORM mappers, and any
other software, that tries to understand the database schema. As
far as I see, those software completly reasonable relies on a
VARCHAR returning a length

Please give me any good reasons not to apply my patch, with would
further improve standards conformance.
With best regards,

Daniel Migowski

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	Kris Jurka <books(at)ejurka(dot)com>
Cc:	Oliver Jowett <oliver(at)opencloud(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 11:13:01
Message-ID:	476A4E3D.3050203@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello Kris, hello Oliver, 
 
Kris Jurka schrieb: 
<blockquote cite="mid:Pine(dot)BSO(dot)4(dot)64(dot)0712200547450(dot)15638(at)leary(dot)csoft(dot)net"
type="cite">
<blockquote type="cite">My main concern is that 'text' is a very
common type to use in PostgreSQL based designs, and that JDBC
applications are more likely to understand how to interpret a field
that claims to be VARCHAR than one that is LONGVARCHAR, given that
LONGVARCHAR is a relatively strange type and at best poorly defined.
 
</blockquote>
This is my concern as well, which is why I suggested that changing the
precision value might be a better solution.  Daniel, any opinion on
that alternative?
 
</blockquote>
I thought about this, too. In this case we deliver a
VARCHAR(1073741824) for every "text". I think about this as being very
strange alternative to just saying LONGVARCHAR. Also because of my
argument of better using getStream() for big text fields IS a good
alternative. 
 
Okey, how if we give this as an connection parameter to the driver?
describeUnboundTextAs=VARCHAR(1073741824) (default, current behaviour)
vs. showTextAs=LONGVARCHAR. This should be extended to show postgres
varchar(unbound) in the same type. 
 
IMHO should LONGVARCHAR be the default. If the sql/jdbc spec leaves it
relatively undefined, the application (which is generic jdbc and could
also use other dbs at the backend), should handle those as relatively
undefined, too. And IF the application knows about postgres at the
backend, then there is no problem with LONGVARCHAR in the description,
too. 
 
Best regards, 
Daniel Migowski 
 
 
<div class="moz-signature">-- 
<pre> |¯¯|¯¯| IKOffice GmbH Daniel Migowski
| | |/| Mail: <a
href="mailto:dmigowski(at)ikoffice(dot)de">dmigowski(at)ikoffice(dot)de</a>
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| <a href="http://www.ikoffice.de">http://www.ikoffice.de</a> Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864</pre>
</div>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	2.4 KB

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	Kris Jurka <books(at)ejurka(dot)com>
Cc:	Oliver Jowett <oliver(at)opencloud(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gregory Stark <stark(at)enterprisedb(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 12:39:29
Message-ID:	476A6281.6090008@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Kris Jurka schrieb:
> On Thu, 20 Dec 2007, Oliver Jowett wrote:
>> My main concern is that 'text' is a very common type to use in
>> PostgreSQL based designs, and that JDBC applications are more likely
>> to understand how to interpret a field that claims to be VARCHAR than
>> one that is LONGVARCHAR, given that LONGVARCHAR is a relatively
>> strange type and at best poorly defined.
> This is my concern as well, which is why I suggested that changing the
> precision value might be a better solution. Daniel, any opinion on
> that alternative?
Btw., you say that "JDBC applications" are more likely to understand how
to interpret a VARCHAR field, than an LONGVARCHAR field. Which? I don't
know any.And currently a JDBC application (Chrystal Reports) broke,
because we did't send a LONGVARCHAR! Every JDBC application should be
able to handle both, since JDBC defines both. And, most important, both
types can, according to JDBC spec, treated equally (same access
functions,etc.). So we shouldn't create a broken driver for
applications, that _might_ be broken. And if they are, a parameter
option should be a fair deal to give to the driver, to let bad behaving
applications work. We don't have to stick to bugs just for backwards
compatibility, don't we? We are not Microsoft, i think.

With best regards,
Daniel Migowski

From:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
Cc:	pgsql-jdbc(at)postgresql(dot)org, Oliver Jowett <oliver(at)opencloud(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>
Subject:	Re: TypeInfoCache
Date:	2007-12-20 15:38:36
Message-ID:	11679.1198165116@sss.pgh.pa.us
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Daniel Migowski <dmigowski(at)ikoffice(dot)de> writes:
> * At first, VARCHAR as defined by SQL and as used by all other JDBC
> drivers always has a upper limit. LONGVARCHAR is for very large
> quantities of text, so IMHO returning a "text" or an unrestricted
> "varchar" as VARCHAR just breaks the specs and the expectations.

Unfortunately, LONGVARCHAR is no more standard than TEXT, at least
as far as the non-JDBC world is concerned.

I concur with the complaints that LONGVARCHAR is likely to prompt
applications to do things that might be enormously inefficient overkill
for typical-size fields. If the driver had a way to know which fields
are likely to be wide, it'd be OK to translate them to LONGVARCHAR,
but I'm dubious about doing that for text fields in general.

regards, tom lane

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc:	pgsql-jdbc(at)postgresql(dot)org, Oliver Jowett <oliver(at)opencloud(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>
Subject:	Re: TypeInfoCache
Date:	2007-12-20 15:55:47
Message-ID:	476A9083.80407@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-15"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hello Tom, 
 
Tom Lane schrieb:
<blockquote cite="mid:11679(dot)1198165116(at)sss(dot)pgh(dot)pa(dot)us" type="cite">
<pre wrap="">Daniel Migowski <a class="moz-txt-link-rfc2396E" href="mailto:dmigowski(at)ikoffice(dot)de"><dmigowski(at)ikoffice(dot)de></a> writes:
</pre>
<blockquote type="cite">
<pre wrap=""> * At first, VARCHAR as defined by SQL and as used by all other JDBC
drivers always has a upper limit. LONGVARCHAR is for very large
quantities of text, so IMHO returning a "text" or an unrestricted
"varchar" as VARCHAR just breaks the specs and the expectations.
</pre>
</blockquote>
<pre wrap="">
Unfortunately, LONGVARCHAR is no more standard than TEXT, at least
as far as the non-JDBC world is concerned.

regards, tom lane</pre>
</blockquote>
Okey, we have 3 Scenarios: 
<ol>
<li>Scenario 1: A JDBC conformant application trying to use JDBC to
do arbitrary things with arbitrary databases and artbitrary JDBC
drivers (Like some general JDBC database manager). This application
indeed has to use getStream() for LONGVARCHAR fields, because it cannot
know about the size of the contents. But there is a difference in what
the application wants to do with that stream: 
 
</li>
<ol>
<li>If it wants to get all the contents, it will call getString()
anyway, playing the loop into the driver-</li>
<li>If it wants to show a bit of the content, it will call
getStream(), and request e.g. just the first 100 chars.</li>
</ol>
</ol>
<blockquote>In either case it is BETTER to deliver a LONGVARCHAR
because now the application can DECIDE what to do. For VARCHAR it will
always use getString(), because of the spec. 
</blockquote>
<ol start="2">
<li>Scenario 2: The application either knows about the data itself,
because it provided DDL. Or the application has to work with different
DBs and different Drivers but is the same application, and just has
some O/R-Mapping below its feet. Every other DBMS behaves by returning
LONGVARCHAR, only for PostgreSQL this application has to write a
workaround and to use the VARCHAR.</li>
</ol>
<ol start="3">
<li>Scenario 3: Specialized Java Application with one driver and one
postgres db knowing exactly about the backend and just using JDBC as an
abstraction. This app can easily work around the LONGVARCHAR because it
knows the containing data anyway.</li>
</ol>
Sorry, but no Scenario profits from the decision of not returning
LONGVARCHAR. Does someone have any scenario that does? 
 
With best regards, 
Daniel Migowski 
 
 
 
<div class="moz-signature">-- 
<pre> |¯¯|¯¯| IKOffice GmbH Daniel Migowski
| | |/| Mail: <a
href="mailto:dmigowski(at)ikoffice(dot)de">dmigowski(at)ikoffice(dot)de</a>
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| <a href="http://www.ikoffice.de">http://www.ikoffice.de</a> Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864</pre>
</div>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	3.6 KB

From:	danap(at)ttc-cmc(dot)net
To:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 16:15:14
Message-ID:	32821.216.129.226.71.1198167314.squirrel@www.webmail.mtintouch.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

I'm trying to create a generic interface for multiple databases.
Please don't ask me why I'm doing such a stupid thing, but I
just know that this change you guys are talking about is going
to break it. Certain data types need to be handled in special
ways in the application. No big deal I can accommodate the change,
but I'm really already baffled by some of the deviations on data
types in PostgreSQL. I would prefer adhering as much as possible
to standards in SQL and the SUN JDBC. If this changes accomplish
this so be it.

MyJSQLView Project Manager
Dana Proctor

From:	Oliver Jowett <oliver(at)opencloud(dot)com>
To:	danap(at)ttc-cmc(dot)net
Cc:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-20 21:08:39
Message-ID:	476AD9D7.6050708@opencloud.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

danap(at)ttc-cmc(dot)net wrote:

> but I'm really already baffled by some of the deviations on data
> types in PostgreSQL.

Can you send through some examples? That's exactly the sort of feedback
we need to be able to improve the driver.

> I would prefer adhering as much as possible
> to standards in SQL and the SUN JDBC. If this changes accomplish
> this so be it.

It's arguable whether it will or not, given that 'text' isn't a standard
type anyway.

-O

From:	dmp <danap(at)ttc-cmc(dot)net>
To:	Oliver Jowett <oliver(at)opencloud(dot)com>
Cc:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-21 20:18:06
Message-ID:	476C1F7E.5020004@ttc-cmc.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

This is the standard output I obtain from a database about
its data types. I use the defined data types from manuals. This
is important to the MyJSQLView program because if a database
defines a data type I need to assume that a user somewhere
will use it so I have to be able to handle it. This seems sensible
from the perspective of a generic database GUI access too. I
admit two areas I have not addressed yet in the current version
of the application are user defined and arrays. Noticed the asterisked
rows. These are data types that are specific to PostgreSQL. Granted
each database is going to have a few of these. Notice the returned
Class & Type. The 'object' class returned are fine for the Network
Address types, since the Sun Java JDBC defines methods for objects
in the getter/setter methods, but the the Interval and Geometric data
types return specific types that are not defined by the Sun Java JDBC,
'PGxxx'. I have learned how to properly handle these types without
importing them from the org.postgresql driver, but I would call them
deviants. By the way importing such deviants from a database driver
in a generic GUI is not particular acceptable. Maybe I'm missing
something there; Anyway I would think that standard data types should
really relate more to hardware and the the specifics of the nature of
program's, in this case database, requirements. Example blob/bytea.
This is a obvious specific to a database program and so therefore should
be an extension to the standard data types. If points and circles are going
to be added is then next perhaps mole?

danap

-----------------------
PostgreSQL 8.2.5

ColumnName, Modified ColumnName, ColumnClassName, ColumnClassType,
ColumnPreferedSize

1 data_type_id Data Type Id java.lang.Integer int4 11
2 smallint_type Smallint Type java.lang.Integer int2 6
3 int_type Int Type java.lang.Integer int4 11
4 bigint_type Bigint Type java.lang.Long int8 20
5 decimal_type Decimal Type java.math.BigDecimal numeric 18
6 numeric_type Numeric Type java.math.BigDecimal numeric 12
7 real_type Real Type java.lang.Float float4 14
8 doubleprecision_type Doubleprecision Type java.lang.Double float8 24
9 serial_type Serial Type java.lang.Integer int4 11
10 bigserial_type Bigserial Type java.lang.Long int8 20
11 varchar_type Varchar Type java.lang.String varchar 30
12 char_type Char Type java.lang.String bpchar 30
13 text_type Text Type java.lang.String text 2147483647
14 bytea_type Bytea Type [B bytea 2147483647
15 date_type Date Type java.sql.Date date 13
16 time_type Time Type java.sql.Time time 15
17 timetz_type Timetz Type java.sql.Time timetz 21
18 timestamp_type Timestamp Type java.sql.Timestamp timestamp 29
19 timestamptz_type Timestamptz Type java.sql.Timestamp timestamptz 35
*20 interval_type Interval Type org.postgresql.util.PGInterval interval 49
21 boolean_type Boolean Type java.lang.Boolean bool 1
*22 point_type Point Type org.postgresql.geometric.PGpoint point 2147483647
*23 linesegment_type Linesegment Type org.postgresql.geometric.PGlseg
lseg 2147483647
*24 box_type Box Type org.postgresql.geometric.PGbox box 2147483647
*25 path_type Path Type org.postgresql.geometric.PGpath path 2147483647
*26 polygon_type Polygon Type org.postgresql.geometric.PGpolygon polygon
2147483647
*27 circle_type Circle Type org.postgresql.geometric.PGcircle circle
2147483647
*28 cidr_type Cidr Type java.lang.Object cidr 2147483647
*29 inet_type Inet Type java.lang.Object inet 2147483647
*30 macaddr_type Macaddr Type java.lang.Object macaddr 2147483647
31 bit2_type Bit2 Type java.lang.Boolean bit 2
32 bitvarying2_type Bitvarying2 Type java.lang.Object varbit 5

From:	Oliver Jowett <oliver(at)opencloud(dot)com>
To:	dmp <danap(at)ttc-cmc(dot)net>
Cc:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-21 21:36:41
Message-ID:	476C31E9.3030708@opencloud.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

dmp wrote:

> Noticed the asterisked
> rows. These are data types that are specific to PostgreSQL. Granted
> each database is going to have a few of these. Notice the returned
> Class & Type. The 'object' class returned are fine for the Network
> Address types, since the Sun Java JDBC defines methods for objects
> in the getter/setter methods, but the the Interval and Geometric data
> types return specific types that are not defined by the Sun Java JDBC,
> 'PGxxx'. I have learned how to properly handle these types without
> importing them from the org.postgresql driver, but I would call them
> deviants. By the way importing such deviants from a database driver
> in a generic GUI is not particular acceptable.

Can't you just use getString() or getObject() on any type you don't
recognize and let the driver deal with any necessary casting?

> Maybe I'm missing
> something there; Anyway I would think that standard data types should
> really relate more to hardware and the the specifics of the nature of
> program's, in this case database, requirements. Example blob/bytea.
> This is a obvious specific to a database program and so therefore should
> be an extension to the standard data types. If points and circles are going
> to be added is then next perhaps mole?

They're mappings of standard PostgreSQL types, and they use driver
infrastructure that allows adding of specific support for any
user-defined type that happens to get installed in the DB (type
extensibility is a fairly big thing in PostgreSQL, why would we ignore
it?). The geometric types are quite a bit more than just another data
structure, there are various "spatial" indexes and queries you can run
against them. Same is generally true of other custom types, they have
their own indexing and operator behaviour which is why they're there in
the first place.

If you don't want to use the type the driver implements you don't have
to, getString() still works just fine (or somewhat more indirectly -
getObject() -> I don't recognize the returned object -> call toString()
on it)

How would you rather have the driver handle these types, if not in this way?

-O

From:	dmp <danap(at)ttc-cmc(dot)net>
To:	Oliver Jowett <oliver(at)opencloud(dot)com>
Cc:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-22 21:04:28
Message-ID:	476D7BDC.1060808@ttc-cmc.net
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

dmp wrote:

> Can't you just use getString() or getObject() on any type you don't
>recognize and let the driver deal with any necessary casting?

Yes, and this is exactly what the application does. The same is not true of
the setter methods. When I first created the TableEntryForm it did not pay
attention to any of the data types, but obvious ones like date and time.
The form would just use setObject(). Later it was decided to do some checks
on data before it was submitted to the database, for instance numeric
characters were sent for integers. This then involved checking and using
the appropriate java.sql.Type setter for the type. Any types not caught
would
drop through and use setObject. With the PostgreSQL JDBC driver this does
not work. You have to use the appropriate setter, unfortunately the standard
JDBC does not define the interval, network, or geometric types. So you have
to CAST or import the type from the postgreSQL driver. Since the MyJSQLView
program also supports MySQL the program would then need the postgreSQL
driver to function even if a user only wanted to use MySQL. This creates a
dependency outside of the standard JDBC. Well a CAST was the solution, but
still the application has to catch all defined data types and not let
them drop through
to a default setObject().

> They're mappings of standard PostgreSQL types, and they use driver
> infrastructure that allows adding of specific support for any
> user-defined type that happens to get installed in the DB (type
> extensibility is a fairly big thing in PostgreSQL, why would we ignore
> it?).

I'm sorry I can not really answer this. All you have done is state a
reality.
We already know PostgreSQL is calling these standard types and or course
if they are then the driver I guess would then support them. If you call
them
one of the standard types then again yes users can use them for a basis for
building more complex user-defined types. Extendibility is an admirable
feature for a program, I guess my argument here is that maybe it should be
limited with regard to data types. I have never seen in my limited
experience
a programing language that defined a point as a data type. In reality it
is a
concept that is composed of data types, real/integer numbers. Yes it is nice
to have these predisposed types, but a user could of created a point through
a user-defined type just like an example I saw in the manual regarding a
complex type.

> The geometric types are quite a bit more than just another data
> structure, there are various "spatial" indexes and queries you can run
> against them. Same is generally true of other custom types, they have
> their own indexing and operator behaviour which is why they're there in
> the first place.

Now were are getting somewhere. If the internals of the database are using
these geometric types in the algorithms for indexes and queries then perhaps
they are justified as some form of data structure. Notice that I have quoted
you from above, you are now even calling them data structures, and not
types. If this is the case then it might be understandable in defining them
as available 'data types'. If this is not the case then you are again
just making
a statement in support of providing the ability to use something that was
created. If you create a type then obviously you have to provide indexing
and operators for it, the latter does not justify its existence.

> If you don't want to use the type the driver implements you don't have
> to, getString() still works just fine (or somewhat more indirectly -
> getObject() -> I don't recognize the returned object -> call toString()
>on it)

I already answered this.

> How would you rather have the driver handle these types, if not in
this way?

I would rather not have to CAST or import non-standard types which are not
defined in the Sun JDBC. If you wish to have them at least make the driver
except a generic string setter method like the getter. Which is where we
started
with this thread in which I think was done. I have been busy trying to get a
PostgreSQL export working so I have not checked. Anyway I think I'm happy,
how about everyone else? I really do appreciate the support/information
this forum
and the others PostgreSQL provides. Thanks guys/gals.

danap.

From:	Oliver Jowett <oliver(at)opencloud(dot)com>
To:	dmp <danap(at)ttc-cmc(dot)net>
Cc:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2007-12-22 21:28:41
Message-ID:	476D8189.4030003@opencloud.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

dmp wrote:

> I would rather not have to CAST or import non-standard types which are not
> defined in the Sun JDBC. If you wish to have them at least make the driver
> except a generic string setter method like the getter.

You can now do this in CVS HEAD by using setObject(n, someStringObject,
Types.OTHER). Or you can enable it globally for all strings by using
"stringtype=unspecified" at the cost of less type safety (which I
wouldn't recommend)

Normally the meaning of setString() is pretty explicit, though - it says
that you want the target type to be text or varchar. If the underlying
database type is something different, I don't think it's unreasonable to
require you to either use a different method, or put a cast in your query.

-O

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Cc:	Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Oliver Jowett <oliver(at)opencloud(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>, Kris Jurka <books(at)ejurka(dot)com>
Subject:	Re: TypeInfoCache
Date:	2007-12-27 08:40:02
Message-ID:	477364E2.2010808@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Hello, dear JDBC mailing list members,

At first I hope you all had a merry christmas. Regarding the patch I
sent, another important argument is this one: I createt a benchmark for
getCharacterStream vs. getStream running on a table with a varchar(50)
and a text column, each containing 50 chars of data. These are the
benchmarks for the differences of calling 1000 times the given function
on a Core2Duo 2Ghz and JDK1.5:

getString on text_col took 8.27384ms
getCharacterStream on text_col took 14.26489ms
Factor: 1.724095462324628
getString on varchar_col took 9.57153ms
getCharacterStream on varchar_col took 12.76733ms
Factor: 1.3338860140437319

Please note only the getString() vs.
getCharacterStream()+creatingAString are benchmarked, not the DB
connection itself (which is the real bottleneck). And please note only
those applications that really use getCharacterStream are a bit slower,
but as I explained in my previous mails, they do it for a good reason.

Conclusion: There is no real performance loss even in the worst case
(which should never occur anyway), so please accept my patch. I wish you
all a happy new year!

With best regards,
Daniel Migowski

Attachment	Content-Type	Size
StringVsStreamTest.java	text/plain	2.9 KB

From:	Oliver Jowett <oliver(at)opencloud(dot)com>
To:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
Cc:	pgsql-jdbc(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gregory Stark <stark(at)enterprisedb(dot)com>, Kris Jurka <books(at)ejurka(dot)com>
Subject:	Re: TypeInfoCache
Date:	2007-12-27 09:01:55
Message-ID:	47736A03.5020009@opencloud.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Daniel Migowski wrote:

> getString on text_col took 8.27384ms
> getCharacterStream on text_col took 14.26489ms
> Factor: 1.724095462324628
> getString on varchar_col took 9.57153ms
> getCharacterStream on varchar_col took 12.76733ms
> Factor: 1.3338860140437319

> Conclusion: There is no real performance loss even in the worst case
> (which should never occur anyway), so please accept my patch. I wish you
> all a happy new year!

I'm confused. Don't your numbers show a 30%-70% slowdown when using
getCharacterStream()?

Also some quick notes about microbenchmarks:

- 1000 iterations isn't really enough to get useful numbers, try 20,000+
for JIT effects or considerably more for GC effects
- server vs. client JVM can have quite different behaviours, you should
check both
- put your inner benchmark loops in a separate method as some JIT has
method-level granularity
- benchmark the same code path in your warmup loop as you actually
measure the numbers from. In your code, you're benchmarking a different
code path to the one you run in your warmup loop.

-O

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2008-01-07 16:30:09
Message-ID:	47825391.9050304@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Hello, together,

some news or comments about my patch? Will it be accepted or am i doomed
with ignorance, now ;)

With best regards,
Daniel Migowski

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2008-01-07 16:33:42
Message-ID:	47825466.5000705@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Sorry, somehow most [JDBC] mails managed to get into the spam filter...
will follow up now... sorry for MY ignorance :( 
 
With best regards, 
Daniel Migowski 
 
Daniel Migowski schrieb:
<blockquote cite="mid:47825391(dot)9050304(at)ikoffice(dot)de" type="cite">Hello,
together,
 
 
some news or comments about my patch? Will it be accepted or am i
doomed with ignorance, now ;)
 
 
With best regards,
 
Daniel Migowski
 
 
</blockquote>
 
 
<div class="moz-signature">-- 
<pre> |¯¯|¯¯| IKOffice GmbH Daniel Migowski
| | |/| Mail: <a
href="mailto:dmigowski(at)ikoffice(dot)de">dmigowski(at)ikoffice(dot)de</a>
| | // | Nordstr. 10 Tel.: +49 (441) 21 98 89 52
| | \\ | 26135 Oldenburg Fax.: +49 (441) 21 98 89 55
|__|__|\| <a href="http://www.ikoffice.de">http://www.ikoffice.de</a> Mob.: +49 (176) 22 31 20 76

Geschäftsführer: Ingo Kuhlmann, Daniel Migowski
Amtsgericht Oldenburg, HRB 201467
Steuernummer: 64/211/01864</pre>
</div>
</body>
</html>

Attachment	Content-Type	Size
unknown_filename	text/html	1.3 KB

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2008-01-07 16:33:53
Message-ID:	47825471.8080707@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Sorry, somehow most [JDBC] mails managed to get into the spam filter...
will follow up now... sorry for MY ignorance :(

With best regards,
Daniel Migowski

Daniel Migowski schrieb:
> Hello, together,
>
> some news or comments about my patch? Will it be accepted or am i
> doomed with ignorance, now ;)
>
> With best regards,
> Daniel Migowski
>

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2008-01-07 16:35:23
Message-ID:	478254CB.7050608@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Sorry, somehow most [JDBC] mails managed to get into the spam filter...
will follow up now... sorry for MY ignorance :(

With best regards,
Daniel Migowski

Daniel Migowski schrieb:
> Hello, together,
>
> some news or comments about my patch? Will it be accepted or am i
> doomed with ignorance, now ;)
>
> With best regards,
> Daniel Migowski
>

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	pgsql-jdbc(at)postgresql(dot)org
Subject:	Re: TypeInfoCache
Date:	2008-01-07 16:48:35
Message-ID:	478257E3.5070800@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Sorry, somehow most [JDBC] mails since 2007-12-19 managed to get into
the spam filter... will follow up now... sorry for MY ignorance :(

With best regards,
Daniel Migowski

Daniel Migowski schrieb:
> Hello, together,
>
> some news or comments about my patch? Will it be accepted or am i
> doomed with ignorance, now ;)
>
> With best regards,
> Daniel Migowski
>

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	Oliver Jowett <oliver(at)opencloud(dot)com>
Cc:	pgsql-jdbc(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Gregory Stark <stark(at)enterprisedb(dot)com>, Kris Jurka <books(at)ejurka(dot)com>
Subject:	Re: TypeInfoCache
Date:	2008-01-07 19:16:45
Message-ID:	47827A9D.6000002@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Hello Oliver,

I redid the benchmark, now with 10,000,000 iterations and externalized
functions. Thank you for your hints on writing good benchmarks. These
are the results:

getString on text_col took 9015.563444ms
getCharacterStream on text_col took 12239.496833ms
Factor: 1.3575964396485478

getString on varchar_col took 9047.101624ms
getCharacterStream on varchar_col took 12268.299423ms
Factor: 1.3560474871261377

A feel a bit ashamed of the different results, now, but nontheless do
they still speak what I wanted to say: We have a 35% percent slowdown
JUST on the getStream-Method, regardless of a varchar or a text type.
This is not much if seen in a real life application where the actual
waiting for the data is the real bottleneck.

And this is just a small slowdown, if the applicatin does not use
getString() on the LONGVARCHAR anyway!

So I don't see any problems here.

I can understand your concerns, if you are used to applications that
store everything in text-fields on the database. But a problem is only
if you have such an applicatoin, and forgets about its database fields
and uses the metadata to remember what its fields have been, now notices
the data is in LONGVARCHARs, and uses the stream-methods. But normally,
if you create such a database, you created it just for postgresql
anyway, want to stay with postgresql, and have access to the code that
accesses the JDBC driver. But this again will only contain
getString()-Methods, as it was used to, and so your code does not have
any penalty if we apply the patch.

Please think about this again. Only the very generic applications need
the metadata. And if the fields might contain very large data, it is
good if they use the "slow" interface. The 33% will be reduced to max 1%
at all anyways if mixed in the other code.

Thank you and with best regards,
Daniel Migowski

Oliver Jowett schrieb:
> Daniel Migowski wrote:
>
>> getString on text_col took 8.27384ms
>> getCharacterStream on text_col took 14.26489ms
>> Factor: 1.724095462324628
>> getString on varchar_col took 9.57153ms
>> getCharacterStream on varchar_col took 12.76733ms
>> Factor: 1.3338860140437319
>
>> Conclusion: There is no real performance loss even in the worst case
>> (which should never occur anyway), so please accept my patch. I wish
>> you all a happy new year!
>
> I'm confused. Don't your numbers show a 30%-70% slowdown when using
> getCharacterStream()?
>
> Also some quick notes about microbenchmarks:
>
> - 1000 iterations isn't really enough to get useful numbers, try
> 20,000+ for JIT effects or considerably more for GC effects
> - server vs. client JVM can have quite different behaviours, you
> should check both
> - put your inner benchmark loops in a separate method as some JIT has
> method-level granularity
> - benchmark the same code path in your warmup loop as you actually
> measure the numbers from. In your code, you're benchmarking a
> different code path to the one you run in your warmup loop.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

From:	Gregory Stark <stark(at)enterprisedb(dot)com>
To:	"Daniel Migowski" <dmigowski(at)ikoffice(dot)de>
Cc:	"Oliver Jowett" <oliver(at)opencloud(dot)com>, <pgsql-jdbc(at)postgresql(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Kris Jurka" <books(at)ejurka(dot)com>
Subject:	Re: TypeInfoCache
Date:	2008-01-07 20:08:48
Message-ID:	87ejct4cvj.fsf@oxford.xeocode.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

"Daniel Migowski" <dmigowski(at)ikoffice(dot)de> writes:

> I can understand your concerns, if you are used to applications that store
> everything in text-fields on the database. But a problem is only if you have
> such an applicatoin, and forgets about its database fields and uses the
> metadata to remember what its fields have been, now notices the data is in
> LONGVARCHARs, and uses the stream-methods.

I think that's quite likely though if you build an application and then later
throw some generic tool at it such as a reporting tool, or a schema design
tool, or a migration tool or something like that.

But I wouldn't be too worried about a slowdown. I would be more worried about
having said tool see LONGVARCHAR and throw its hands in the air and refuse to
include it in your reports. Or insist on migrating it to or from MEMO fields
instead of plain strings.

Perhaps this is all just FUD though. I haven't seen such a case myself. I was
under the impression such cases had been previously posted on list but if
you've searched and not found anything then perhaps I'm thinking of some other
scenario.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

From:	Daniel Migowski <dmigowski(at)ikoffice(dot)de>
To:	Gregory Stark <stark(at)enterprisedb(dot)com>
Cc:	Oliver Jowett <oliver(at)opencloud(dot)com>, pgsql-jdbc(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Kris Jurka <books(at)ejurka(dot)com>
Subject:	Re: TypeInfoCache
Date:	2008-01-08 10:30:03
Message-ID:	478350AB.4070307@ikoffice.de
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

Hello Gregory,

Gregory Stark schrieb:
> "Daniel Migowski" <dmigowski(at)ikoffice(dot)de> writes:
>
>
>> I can understand your concerns, if you are used to applications that store
>> everything in text-fields on the database. But a problem is only if you have
>> such an applicatoin, and forgets about its database fields and uses the
>> metadata to remember what its fields have been, now notices the data is in
>> LONGVARCHARs, and uses the stream-methods.
>>
>
> I think that's quite likely though if you build an application and then later
> throw some generic tool at it such as a reporting tool, or a schema design
> tool, or a migration tool or something like that.
>
Is reasonable, yes.
> But I wouldn't be too worried about a slowdown. I would be more worried about
> having said tool see LONGVARCHAR and throw its hands in the air and refuse to
> include it in your reports. Or insist on migrating it to or from MEMO fields
> instead of plain strings.
>
What would be the problem with this? text fields ARE memo fields. Use
varchar(n) if you want length constrained fields :).
> Perhaps this is all just FUD though.
In fact LONGVARCHAR is made easy in JDBC, since it is required to be
accessible by the same functions as VARCHAR.
> I haven't seen such a case myself. I was
> under the impression such cases had been previously posted on list but if
> you've searched and not found anything then perhaps I'm thinking of some other
> scenario.
>
In fact I searched for LONGVARCHAR on the list, and everything I got are
complaints that it is not supported in the metadata (in about 4
threads). Of course we could not get complaints for the reversed case,
in which LONGVARCHAR itself was a problem, yet.

With best regards,
Daniel Migowski

From:	Gregory Stark <stark(at)enterprisedb(dot)com>
To:	"Daniel Migowski" <dmigowski(at)ikoffice(dot)de>
Cc:	"Oliver Jowett" <oliver(at)opencloud(dot)com>, <pgsql-jdbc(at)postgresql(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Kris Jurka" <books(at)ejurka(dot)com>
Subject:	Re: TypeInfoCache
Date:	2008-01-08 12:16:26
Message-ID:	87ejcsecmd.fsf@oxford.xeocode.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Lists:	pgsql-jdbc

"Daniel Migowski" <dmigowski(at)ikoffice(dot)de> writes:

> What would be the problem with this? text fields ARE memo fields. Use
> varchar(n) if you want length constrained fields :).

Well if I understand correctly memo fields are much less functional than text
fields. In short the mapping between Postgres types and types of other
databases is imperfect and what type is the closest match will depend on which
properties are most important. IIUC memo fields cannot be used in queries the
way text fields can, passed to functions, and stored in variables etc. The
ability to not declare a length constraint is a fairly minor distinguishing
property that doesn't impact the application much.

>> Perhaps this is all just FUD though.
>
> In fact LONGVARCHAR is made easy in JDBC, since it is required to be accessible
> by the same functions as VARCHAR.

Sure, but that doesn't mean tools will make the right decision. It'll be
positively weird for a tool to provide a different set of options to the user
if it sees a text field rather than a varchar for example since in Postgres
they're almost exactly equivalent.

>> I haven't seen such a case myself. I was
>> under the impression such cases had been previously posted on list but if
>> you've searched and not found anything then perhaps I'm thinking of some other
>> scenario.
>
> In fact I searched for LONGVARCHAR on the list, and everything I got are
> complaints that it is not supported in the metadata (in about 4 threads). Of
> course we could not get complaints for the reversed case, in which LONGVARCHAR
> itself was a problem, yet.

Right, searching for LONGVARCHAR isn't going to find problems since it's not
the way Postgres worked in the past. Perhaps searching for "memo" or "lob" or
something like that might work. But I'm being unfair, I guess I have to do
this search myself if I still think there's a problem :)

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!