Some advanced database features, are they present in PostgreSQL

On Tue, 10 Oct 2000, Marc SCHAEFER wrote:

- ability to have databases bigger than the host's maximum file size

This has been in there since the old postgres days (although we did find
it have a problem with Linux and files exactly 2Gig). PostgreSQL now
splits tables at the 1Gb level, so tables are only really limited to the
disk size, not the file size (normally 2^32 bytes = 2Gig)

Peter

--
Peter T Mount peter@retep.org.uk http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/

Marc SCHAEFER writes:

- log all transactions to a special log file, that can be used for
backup purposes:

Will be in 7.1.

- hard transactions: cutting the power to a PostgreSQL server
may cause data loss and/or data corruption. Some databases use
sophisticated techniques to ensure serialization of operation
through journaling, redoing some of the transactions at
bootup time if required.

This is really the same as above in implementation. So same answer.

- the ability to synchronize two database servers, with only the
changes being exchanged, live. Or the ability to have many
servers in a load-balancing or data scattering pool.

Something like this has recently been announced as add-on from PostgreSQL,
Inc. (www.pgsql.com) Haven't seen it used, though.

- ability to have databases bigger than the host's maximum file size

That has been available for quite a while.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

- hard transactions: cutting the power to a PostgreSQL server
may cause data loss and/or data corruption.

That is false; Postgres is secure now against power failures,
at least if you run in the default mode with lots of fsync()s.

The WAL feature planned for 7.1 should make performance better,
but it won't make any fundamental change in reliability for power
failures.

The existing server does not offer any protection against disk hardware
failure, however. RAID disks might be an adequate answer to that.

regards, tom lane

On Tue, 10 Oct 2000, Peter Eisentraut wrote:

Will be in 7.1.

[ ... ]

Something like this has recently been announced as add-on from PostgreSQL,

[ ... ]

That has been available for quite a while.

[ ... ]

So, those are very good news. Thanks, and keep the good work.

- hard transactions: cutting the power to a PostgreSQL server
may cause data loss and/or data corruption. Some databases use
sophisticated techniques to ensure serialization of operation
through journaling, redoing some of the transactions at
bootup time if required.

- ability to have databases bigger than the host's maximum file size

Neither of these are problems in PostgreSQL. Tom Lane already answered the
transaction one, but I'll answer the size one. PostgreSQL stores each table
in a separate file, and will store a table in multiple files if it becomes
larger than about 1 gig. I've had (for testing, not production) databases
with 30 GB of data on Linux where the file limit was 2 GB.

As for your other two concerns, I'd like to see them fixed as well. I
handle those issues currently at the application level, but I'm considering
writing a small change to the core to provide that functionality (as soon as
I finish my other add on projects).

--
Adam Ruth
InterCation, Inc.
www.intercation.com

"Marc SCHAEFER" <schaefer@alphanet.ch> wrote in message
news:Pine.LNX.3.96.1001010155021.1563A-100000@defian.alphanet.ch...

hello.

i am faced with a situation where i must store a potentially large ascii
string (several thousand characters?). i am looking for some insight beyond
what the documentation offers...

is there a limit on the upper limit of a VARCHAR? i cannot find one in the
documentation.

is it true that a TEXT field can be any size?

what is the best way to manage memory? for example, if i declare it as
VARCHAR(10000), does that mean that memory for 10,000 characters will be
allocated whether i use it or not, or is it dynamic? how about the TEXT
type.

thanks
chris

I'm looking for info about the synching and I don't see any. Is there a
more specific URL and is there a time frame for this. Also, has
segmentation been discussed?

-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Peter Eisentraut
Sent: Tuesday, October 10, 2000 1:08 PM
To: Marc SCHAEFER
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Some advanced database features, are they present
in PostgreSQL

Marc SCHAEFER writes:

- log all transactions to a special log file, that can be used for
backup purposes:

Will be in 7.1.

- hard transactions: cutting the power to a PostgreSQL server
may cause data loss and/or data corruption. Some databases use
sophisticated techniques to ensure serialization of operation
through journaling, redoing some of the transactions at
bootup time if required.

This is really the same as above in implementation. So same answer.

- the ability to synchronize two database servers, with only the
changes being exchanged, live. Or the ability to have many
servers in a load-balancing or data scattering pool.

Something like this has recently been announced as add-on from PostgreSQL,
Inc. (www.pgsql.com) Haven't seen it used, though.

- ability to have databases bigger than the host's maximum file size

That has been available for quite a while.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

"chris markiewicz" <cmarkiew@commnav.com> writes:

is there a limit on the upper limit of a VARCHAR? i cannot find one in the
documentation.

The physical limit is circa 1Gb under TOAST. There's a purely arbitrary
limit at 10Mb, which I put in on the theory that "varchar(100000000)"
is probably a typo and certainly pretty silly. (If anyone wants to
argue that decision, feel free --- I just did it on the spur of the
moment while changing the old code that checked for declared size <
BLCKSZ.)

is it true that a TEXT field can be any size?

TEXT also has a limit at 1Gb. There's really no difference between TEXT
and VARCHAR as far as storage goes. My advice is use VARCHAR(n) if
there is some reason *in the semantics of your application* why the
field should never exceed n characters. If there's not an application-
derived reason for a specific upper limit, declare your field as TEXT
to document that there's no particular limit on it.

what is the best way to manage memory? for example, if i declare it as
VARCHAR(10000), does that mean that memory for 10,000 characters will be
allocated whether i use it or not, or is it dynamic? how about the TEXT
type.

Either one stores however many characters there are, and no more. Think
of the VARCHAR limit as a constraint check ("length(field) <= n"),
not a storage property.

This is quite unlike CHAR(n), where you get truncation or blank padding
to exactly n characters, so the limit is a storage property as well as
a constraint.

regards, tom lane

Hello Tom,

On Tuesday, October 10, 2000 � 11:34:49 PM, you said :

TL> "chris markiewicz" <cmarkiew@commnav.com> writes:

is there a limit on the upper limit of a VARCHAR? i cannot find one in the
documentation.

TL> The physical limit is circa 1Gb under TOAST. There's a purely arbitrary
TL> limit at 10Mb, which I put in on the theory that "varchar(100000000)"
TL> is probably a typo and certainly pretty silly. (If anyone wants to
TL> argue that decision, feel free --- I just did it on the spur of the
TL> moment while changing the old code that checked for declared size <
TL> BLCKSZ.)

Excuse me, what is the 8kb-per-record size limit if we can have so
big fields ?

--
Jean-Christophe Boggio
cat@thefreecat.org
Independant Consultant and Developer
Delphi, Linux, Oracle, Perl

Jean-Christophe Boggio <cat@thefreecat.org> writes:

is there a limit on the upper limit of a VARCHAR? i cannot find one in the
documentation.

TL> The physical limit is circa 1Gb under TOAST.

Excuse me, what is the 8kb-per-record size limit if we can have so
big fields ?

Sorry --- TOAST is 7.1. In existing releases, TEXT, VARCHAR, and CHAR
are all constrained by the BLCKSZ limit on total row size. But still,
that offers no reason for choosing TEXT over VARCHAR or vice versa.

regards, tom lane

There is another issue with TEXT vs VARCHAR. A TEXT field is not handled well
by ODBC and MS Access. If there is an index on the TEXT field, the ODBC link
will fail, saying something like "Can Not Index a OLE field." OLE fields are Access's
attempt at a BLOB field, and they are not indexable.

If you are using ODBC, I would not use a TEXT field until you test it in your enviroment.

Have Fun,
Dave Huttleston Jr

right now, we are working with our partners internally on this, before we
release it publicly, but more information can be found at:

http://www.erserver.com/

On Tue, 10 Oct 2000, Rob Hutton wrote:

I'm looking for info about the synching and I don't see any. Is there a
more specific URL and is there a time frame for this. Also, has
segmentation been discussed?

-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Peter Eisentraut
Sent: Tuesday, October 10, 2000 1:08 PM
To: Marc SCHAEFER
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Some advanced database features, are they present
in PostgreSQL

Marc SCHAEFER writes:

- log all transactions to a special log file, that can be used for
backup purposes:

Will be in 7.1.

- hard transactions: cutting the power to a PostgreSQL server
may cause data loss and/or data corruption. Some databases use
sophisticated techniques to ensure serialization of operation
through journaling, redoing some of the transactions at
bootup time if required.

This is really the same as above in implementation. So same answer.

- the ability to synchronize two database servers, with only the
changes being exchanged, live. Or the ability to have many
servers in a load-balancing or data scattering pool.

Something like this has recently been announced as add-on from PostgreSQL,
Inc. (www.pgsql.com) Haven't seen it used, though.

- ability to have databases bigger than the host's maximum file size

That has been available for quite a while.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

David Huttleston Jr <dhjr@hddesign.com> writes:

There is another issue with TEXT vs VARCHAR. A TEXT field is not
handled well by ODBC and MS Access. If there is an index on the TEXT
field, the ODBC link will fail, saying something like "Can Not Index a
OLE field." OLE fields are Access's attempt at a BLOB field, and they
are not indexable.

Hmm ... sounds like our ODBC driver is falling down on the job when it
comes to representing TEXT columns in ODBC-speak. There's surely no
reason for a TEXT column to behave worse than VARCHAR(n).

I think this points up the comment I made earlier today on
pgsql-interfaces, that our ODBC driver is badly in need of attention
from a committed maintainer. There's a lot of minor stuff that needs
done, and no one seems to want to do it. There's gotta be someone
out there to pick up this ball and run with it...

regards, tom lane

I have to tell you guys that his is the major limiting factor that has
kept us away from PGSQL for major projects. This looks great. Now, two
questions.

How much of what is on the page is what it will eventually do and how much
will there actually be in the beta.

Also, the site talks about data hierarchy replication. Is this similar to
segmentation where each site sees the information that is of interest to
them but not the info from all other sites? We have kind of the hub and
spoke situation where the corporate office gets everything from all the
sites, but the sites only get info from the home office that is of interest
to them. We were going to do this by segmenting the database into site
specific segments and only replicating that segment to the appropriate
site...

Thanks,
Rob

-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of The Hermit Hacker
Sent: Tuesday, October 10, 2000 8:34 PM
To: Rob Hutton
Cc: 'Peter Eisentraut'; 'Marc SCHAEFER'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Some advanced database features, are they present
in PostgreSQL

right now, we are working with our partners internally on this, before we
release it publicly, but more information can be found at:

http://www.erserver.com/

On Tue, 10 Oct 2000, Rob Hutton wrote:

I'm looking for info about the synching and I don't see any. Is there a
more specific URL and is there a time frame for this. Also, has
segmentation been discussed?

-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of Peter Eisentraut
Sent: Tuesday, October 10, 2000 1:08 PM
To: Marc SCHAEFER
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Some advanced database features, are they present
in PostgreSQL

Marc SCHAEFER writes:

- log all transactions to a special log file, that can be used for
backup purposes:

Will be in 7.1.

- hard transactions: cutting the power to a PostgreSQL server
may cause data loss and/or data corruption. Some databases use
sophisticated techniques to ensure serialization of operation
through journaling, redoing some of the transactions at
bootup time if required.

This is really the same as above in implementation. So same answer.

- the ability to synchronize two database servers, with only the
changes being exchanged, live. Or the ability to have many
servers in a load-balancing or data scattering pool.

Something like this has recently been announced as add-on from PostgreSQL,
Inc. (www.pgsql.com) Haven't seen it used, though.

- ability to have databases bigger than the host's maximum file size

That has been available for quite a while.

--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/

Marc G. Fournier ICQ#7615664 IRC Nick:
Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary:
scrappy@{freebsd|postgresql}.org

Marc SCHAEFER wrote that the Addison-Wesley wrote a book on PostgreSQL.
Anyone can give me the title and the author or the way to find it? Here in
italy i can't find nothing !!
Thanks

-----Messaggio Originale-----
Da: "Marc SCHAEFER" <schaefer@alphanet.ch>
A: <pgsql-general@postgresql.org>
Data invio: Tuesday, October 10, 2000 03:51 PM
Oggetto: [GENERAL] Some advanced database features, are they present in
PostgreSQL

Hasn't been released for print yet... you can download a PDF version of it
at www.postgresql.org

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Giorgio Ponza" <giorgio@opla.it>
To: "Marc SCHAEFER" <schaefer@alphanet.ch>; <pgsql-general@postgresql.org>
Sent: Wednesday, October 11, 2000 11:37 AM
Subject: R: [GENERAL] PostgreSQL book

Marc SCHAEFER wrote that the Addison-Wesley wrote a book on PostgreSQL.
Anyone can give me the title and the author or the way to find it? Here in
italy i can't find nothing !!
Thanks

-----Messaggio Originale-----
Da: "Marc SCHAEFER" <schaefer@alphanet.ch>
A: <pgsql-general@postgresql.org>
Data invio: Tuesday, October 10, 2000 03:51 PM
Oggetto: [GENERAL] Some advanced database features, are they present in
PostgreSQL

Hi,

I have been using PostgreSQL a bit and I have been surprised by the good
quality, the features, and now the Addison-Wesley book which is

excellent.

PostgreSQL:
Introduction and Concepts
by Bruce Momjian

You can download a postscript version and print yourself a copy, or read
it online at http://www.postgresql.org/docs/awbook.html

----------
Marc SCHAEFER wrote that the Addison-Wesley wrote a book on PostgreSQL.
Anyone can give me the title and the author or the way to find it? Here
in
italy i can't find nothing !!
Thanks

-----Messaggio Originale-----
Da: "Marc SCHAEFER" <schaefer@alphanet.ch>
A: <pgsql-general@postgresql.org>
Data invio: Tuesday, October 10, 2000 03:51 PM
Oggetto: [GENERAL] Some advanced database features, are they present
in
PostgreSQL

Hi,

I have been using PostgreSQL a bit and I have been surprised by the

good

quality, the features, and now the Addison-Wesley book which is excellent.
However, I have some questions about the implementation of a few
additional features.

I suppose that PostgreSQL hasn't any ability to do the following yet:

- log all transactions to a special log file, that can be used for
backup purposes: ie you dump the database every day, but you keep
the transaction log on a separate disk. Should the database disk
crash, you won't have any data loss if you restore the backup

and

replay the transaction log.

- hard transactions: cutting the power to a PostgreSQL server
may cause data loss and/or data corruption. Some databases use
sophisticated techniques to ensure serialization of operation
through journaling, redoing some of the transactions at
bootup time if required.

- the ability to synchronize two database servers, with only the
changes being exchanged, live. Or the ability to have many
servers in a load-balancing or data scattering pool.

- ability to have databases bigger than the host's maximum file

size

if this is true, can someone tell me why it hasn't been implemented

yet

(there might be very good reasons), if it will be, and if the task

seems

On Wed, 11 Oct 2000, source wrote:

You can download a postscript version and print yourself a copy, or read
it online at http://www.postgresql.org/docs/awbook.html

I have read the PDF version (without printing it), and I will enjoy the
book when it's available here, ie probably about December. I will buy the
book to support the author and also Addison-Wesley, since they allowed the
Internet publication.

That book is definitely worth reading.

Download it from
http://www.postgresql.org/docs/aw_pgsql_book/aw_pgsql_book.pdf

----- Original Message -----
From: "Giorgio Ponza" <giorgio@opla.it>
To: "Marc SCHAEFER" <schaefer@alphanet.ch>; <pgsql-general@postgresql.org>
Sent: Wednesday, October 11, 2000 11:37 AM
Subject: R: [GENERAL] PostgreSQL book

Marc SCHAEFER wrote that the Addison-Wesley wrote a book on PostgreSQL.
Anyone can give me the title and the author or the way to find it? Here in
italy i can't find nothing !!
Thanks

-----Messaggio Originale-----
Da: "Marc SCHAEFER" <schaefer@alphanet.ch>
A: <pgsql-general@postgresql.org>
Data invio: Tuesday, October 10, 2000 03:51 PM
Oggetto: [GENERAL] Some advanced database features, are they present in
PostgreSQL

Hi,

I have been using PostgreSQL a bit and I have been surprised by the good
quality, the features, and now the Addison-Wesley book which is

excellent.