Postgres Pro
Downloads
Home   >   mailing lists

Thread: RE: [SQL] Text type

RE: [SQL] Text type

From
"Jackson, DeJuan"
Date:
The current limit to a TEXT field is MAX_TUPLE_SIZE - TUPLE_OVERHEAD -
ANY_OTHER_FIELD_SIZES_AND_OVERHEAD, which with no other fields in the
table is just less than 8k.  But this can be increased at compile time.
It's set to 8k because that tends to be optimal page size for most of
the OS's that are supported.
    -DEJ

> -----Original Message-----
> From: Guido.Goldstein@t-online.de [mailto:Guido.Goldstein@t-online.de]
> Sent: Wednesday, January 13, 1999 7:40 AM
> To: pgsql-sql@hub.org
> Subject: Re: [SQL] Text type
>
>
>
> Hello!
>
> On Wed, 13 Jan 1999 08:56:19 +0100 (MET)
>     Remigiusz Sokolowski <rems@gdansk.sprint.pl> wrote:
> > >
> > > Hi List,
> > >
> > > What is the max size of the text type in characters,
> preferable, or
> > > bytes.
> > >
> > > Thanks,
> > >
> > > -Greg
> >
> > AFAIK this is 8KB
>
> Somewhat less then 8K, because one -record- has to fit into one page
> (of 8K). AFAIK.
>
> Bye
>   Guido
> --
> We're showing progress.  Things are getting worse at a slower rate.
>

Re: [SQL] Text type

From
Frank Barknecht
Date:
Jackson, DeJuan hat gesagt: // Jackson, DeJuan wrote:

> The current limit to a TEXT field is MAX_TUPLE_SIZE - TUPLE_OVERHEAD -
> ANY_OTHER_FIELD_SIZES_AND_OVERHEAD, which with no other fields in the
> table is just less than 8k.  But this can be increased at compile time.
> It's set to 8k because that tends to be optimal page size for most of
> the OS's that are supported.
>     -DEJ

My question to this is: What to do if I need a big text field on a not
recompiled (8k buffer size) postgresql server?

I am developing a www backend with perl-DBI that keeps track of a lot of
texts that can get longer than 8k. When inserting these they get cut off or
the query gives an error.

I consider changing to a large object type field instead but how can I
conveniently insert, change and search the text there?
(DBD::Pg does not have those nice lo_read() functions, just lo_import and
lo_export)

I think mine is a common problem. So I would be very happy if I could get
some tips on how to handle large chunks of text with postgres (and perl)

Thank you,
--
                                                     __    __
 Frank Barknecht           ____ ______   ____ __ trip\ \  / /wire ______
                          / __// __  /__/ __// // __  \ \/ /  __ \\  ___\
                         / /  / ____/  / /  / // ____// /\ \\  ___\\____ \
                        /_/  /_____/  /_/  /_//_____// /  \ \\_____\\_____\
                                                    /_/    \_\

Re: [SQL] Text type

From
"Gene Selkov Jr."
Date:
> I consider changing to a large object type field instead but how can I
> conveniently insert, change and search the text there?

Large objects are nothing but files with a postgres OID. You will save
yourself a lot of hassle by treating your text as files.

You inset a text by creating a file and inserting its name in a table.

You change it by editing or replacing the file.

In order to search your texts, split them up and load into a table one
word per row. This will also allow you to build an index. Numbering
those words in a sequential order will also allow sequences, or
phrases, to be matched. If you need a full text search, refer to
contrib/fulltextindex or search files directly with grep. The latter
will work if the number of files in one directory does not exceed the
maximum number of command line arguments your system supports (which
is about 2000 in most linuxes, but can be ridiculously small in other
systems (e. g., 80 on IRIX).

--Gene