Thread: Large strings
Hi,
I have a JDBC aplication that actualy works with Oracle and I am
porting it PostgreSQL. The trouble is that I have some rows with type
"LONG" (in Oracle) where are stored strings with up to 20Kb and
PostgreSQL doesn't hava this type and seems to be impossible to store
strings larger than 8Kb with the types "TEXT" or "VARCHAR". What can I do ?!?!
thanks.
Nelson Ferreira Junior wrote:
> Hi,
>
> I have a JDBC aplication that actualy works with Oracle and I am
> porting it PostgreSQL. The trouble is that I have some rows with type
> "LONG" (in Oracle) where are stored strings with up to 20Kb and
> PostgreSQL doesn't hava this type and seems to be impossible to store
> strings larger than 8Kb with the types "TEXT" or "VARCHAR".
> What can I do ?!?!
>
> thanks.
Now I read something about changing the BLOCK SIZE to a higher
value and recompiling the backend. But it will require more disk space to
store the data, isn't it? How much disk space will I need if a chage the
block size to 32K? Will it decrease performance or confiability ? Before you suggest me to use BLOBs to store
theselarge strings,
I'd say that I cannot change my aplication in that way.
Thanks
again.
The generic answer for storing anything larger than the page size is to
use large objects. Set the columns type to oid, and use the setBytes()
method to insert the strings. To retrieve, you can use either the
getBytes() or getBlob() methods.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Nelson Ferreira Junior [mailto:nelson@radix.com.br]
Sent: Monday, April 17, 2000 9:10 PM
To: pgsql-interfaces@postgresql.org
Subject: [INTERFACES] Large strings
Hi,
I have a JDBC aplication that actualy works with Oracle and I am
porting it PostgreSQL. The trouble is that I have some rows with type
"LONG" (in Oracle) where are stored strings with up to 20Kb and
PostgreSQL doesn't hava this type and seems to be impossible to store
strings larger than 8Kb with the types "TEXT" or "VARCHAR". What can I do ?!?!
thanks.
The page size is only really the size of disk allocated each time. You
can get many rows into a page.
As you said you can't use LO's, perhaps the page size is your only
chance (although I'm not sure if the text/varchar types are hardcoded to
a max 4k anyhow.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Nelson Ferreira Junior [mailto:nelson@radix.com.br]
Sent: Monday, April 17, 2000 10:31 PM
To: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Large strings
Nelson Ferreira Junior wrote:
> Hi,
>
> I have a JDBC aplication that actualy works with Oracle and I am
> porting it PostgreSQL. The trouble is that I have some rows with type
> "LONG" (in Oracle) where are stored strings with up to 20Kb and
> PostgreSQL doesn't hava this type and seems to be impossible to store
> strings larger than 8Kb with the types "TEXT" or "VARCHAR".
> What can I do ?!?!
>
> thanks.
Now I read something about changing the BLOCK SIZE to a higher
value and recompiling the backend. But it will require more disk space
to
store the data, isn't it? How much disk space will I need if a chage
the
block size to 32K? Will it decrease performance or confiability ? Before you suggest me to use BLOBs to store
theselarge strings,
I'd say that I cannot change my aplication in that way.
Thanks
again.
I reinstalled Postgre with page size set to 32K, but it still doesn't
work. I got the following errors. Is it complaining abou the size of the
query itself ?
* Java program (even using a prepared statement) SQLException: The SQL Statement is too long
* Puting the query in a file and calling with psql -f : ERROR: Tuple is too big: size 11424, max size 8140
Thanks
Peter Mount wrote:
> The page size is only really the size of disk allocated each time. You
> can get many rows into a page.
>
> As you said you can't use LO's, perhaps the page size is your only
> chance (although I'm not sure if the text/varchar types are hardcoded to
> a max 4k anyhow.
>
> Peter
>
> --
> Peter Mount
> Enterprise Support
> Maidstone Borough Council
> Any views stated are my own, and not those of Maidstone Borough Council.
>
> -----Original Message-----
> From: Nelson Ferreira Junior [mailto:nelson@radix.com.br]
> Sent: Monday, April 17, 2000 10:31 PM
> To: pgsql-interfaces@postgresql.org
> Subject: Re: [INTERFACES] Large strings
>
> Nelson Ferreira Junior wrote:
>
> > Hi,
> >
> > I have a JDBC aplication that actualy works with Oracle and I am
> > porting it PostgreSQL. The trouble is that I have some rows with type
> > "LONG" (in Oracle) where are stored strings with up to 20Kb and
> > PostgreSQL doesn't hava this type and seems to be impossible to store
> > strings larger than 8Kb with the types "TEXT" or "VARCHAR".
> > What can I do ?!?!
> >
> > thanks.
>
> Now I read something about changing the BLOCK SIZE to a higher
> value and recompiling the backend. But it will require more disk space
> to
> store the data, isn't it? How much disk space will I need if a chage
> the
> block size to 32K? Will it decrease performance or confiability ?
> Before you suggest me to use BLOBs to store these large strings,
> I'd say that I cannot change my aplication in that way.
>
> Thanks
> again.
Hello, If your strings are much larger than the limit, it might be more efficient to store the string in it's own file and just store the location of that file in the db. John David Garza garza@keyframe.cjas.org On Wed, 19 Apr 2000, Nelson Ferreira Junior wrote: > I reinstalled Postgre with page size set to 32K, but it still doesn't > work. I got the following errors. Is it complaining abou the size of the > query itself ? > > * Java program (even using a prepared statement) > SQLException: The SQL Statement is too long > * Puting the query in a file and calling with psql -f : > ERROR: Tuple is too big: size 11424, max size 8140 > > Thanks > >