Installation

Operation

5. Release Notes

Release 6.4

Migration to v6.4

Release 6.4.1

Migration to v6.4.1

Detailed Change List

List of Tables

Chapter 2. Ports

This manual describes version 6.4 of Postgres. The

This manual describes version 6.4.1 of Postgres. The

Postgres developer community has compiled and tested

Postgres on a number of platforms. Check the web site

for the latest information.

Table 2-1. Supported Platforms

OS Processor Version Reported Remarks

AIX 4.2.1 RS6000 v6.4 1998-10-27 (Andreas Zeugswetter)

BSDI x86 v6.4 1998-10-25 (Bruce Momjian

FreeBSD x86 v6.4 1998-10-26 (Tatsuo Ishii, Marc

AIX 4.2.1 RS6000 v6.4.1 1998-10-27 (Andreas Zeugswetter)

BSDI x86 v6.4.1 1998-10-25 (Bruce Momjian

FreeBSD x86 v6.4.1 1998-10-26 (Tatsuo Ishii, Marc

2.2.x-3.x Fournier)

DGUX 5.4R4.11 m88k v6.3 1998-03-01 v6.4 probably OK. Needs

DGUX 5.4R4.11 m88k v6.3 1998-03-01 v6.4.1 probably OK. Needs

new maintainer. (Brian E

Gallew)

Digital Unix Alpha v6.4 1998-10-29 Minor patchable problems

Digital Unix Alpha v6.4.1 1998-10-29 Minor patchable problems

4.0 (Pedro J. Lobo)

HPUX PA-RISC v6.4 1998-10-25 Both 9.0x and 10.20

HPUX PA-RISC v6.4.1 1998-10-25 Both 9.0x and 10.20

(Tom Lane, Stan Brown)

IRIX 6.x MIPS v6.3 1998-03-01 5.x is different (Andrew

Martin)

linux 2.0.x Alpha v6.3.2 1998-04-16 Mostly successful. Needs

work for v6.4. (Ryan

work for v6.4.1. (Ryan

Kirkpatrick)

linux 2.0.x x86 v6.4 1998-10-27 (Thomas Lockhart)

linux x86 v6.4 1998-10-25 (Oliver Elphick, Taral)

linux 2.0.x x86 v6.4.1 1998-10-27 (Thomas Lockhart)

linux x86 v6.4.1 1998-10-25 (Oliver Elphick, Taral)

2.0.x/glibc2

linux 2.0.x Sparc v6.4 1998-10-25 (Tom Szybist)

linuxPPC PPC603e v6.4 1998-10-26 Powerbook 2400c (Tatsuo

linux 2.0.x Sparc v6.4.1 1998-10-25 (Tom Szybist)

linuxPPC PPC603e v6.4.1 1998-10-26 Powerbook 2400c (Tatsuo

2.1.24 Ishii)

mklinux DR3 PPC750 v6.4 1998-09-16 PowerMac 7600 (Tatsuo

mklinux DR3 PPC750 v6.4.1 1998-09-16 PowerMac 7600 (Tatsuo

Ishii)

NetBSD/i386 x86 v6.4 1998-10-25 (Brook Milligan)

NetBSD/i386 x86 v6.4.1 1998-10-25 (Brook Milligan)

1.3.2

NetBSD- NS32532 v6.4 1998-10-27 (small problems in

NetBSD- NS32532 v6.4.1 1998-10-27 (small problems in

current date/time math

(Jon Buller)

NetBSD/sparc Sparc v6.4 1998-10-27 (Tom I Helbekkmo)

NetBSD/sparc Sparc v6.4.1 1998-10-27 (Tom I Helbekkmo)

1.3H

NetBSD 1.3 VAX v6.3 1998-03-01 (Tom I Helbekkmo)

SCO UnixWare x86 v6.3 1998-03-01 aka UNIVEL (Billy G.

2.x Allie)

SCO UnixWare x86 v6.4 1998-10-04 (Billy G. Allie)

SCO UnixWare x86 v6.4.1 1998-10-04 (Billy G. Allie)

7

Solaris x86 v6.4 1998-10-28 (Marc Fournier)

Solaris Sparc v6.4 1998-10-28 (Tom Szybist, Frank

Solaris x86 v6.4.1 1998-10-28 (Marc Fournier)

Solaris Sparc v6.4.1 1998-10-28 (Tom Szybist, Frank

2.6-2.7 Ridderbusch)

SunOS 4.1.4 Sparc v6.3 1998-03-01 patches submitted (Tatsuo

Ishii)

SVR4 MIPS v6.4 1998-10-28 no 64-bit int support

SVR4 MIPS v6.4.1 1998-10-28 no 64-bit int support

(Frank Ridderbusch)

SVR4 4.4 m88k v6.2.1 1998-03-01 confirmed with patching

(Doug Winterburn)

Windows NT x86 v6.4 1998-10-08 Mostly working with the

Windows NT x86 v6.4.1 1998-10-08 Mostly working with the

Cygwin library. No DLLs

yet. (Horak Daniel)

Platforms listed for v6.3.x should also work with

v6.4, but we did not receive confirmation of such at

v6.4.1, but we did not receive confirmation of such at

the time this list was compiled.

Note: For Windows NT, the server-side port of

Chapter 3. Installation

Complete installation instructions for Postgres v6.4.

Complete installation instructions for Postgres v6.4.1.

Before installing Postgres, you may wish to visit

www.postgresql.org for up to date information,

patches, etc.

happen in this account.

4. Ftp file

ftp://ftp.postgresql.org/pub/postgresql-v6.4.tar.gz

ftp://ftp.postgresql.org/pub/postgresql-v6.4.1.tar.gz

from the Internet. Store it in your home directory.

5. Some platforms use flex. If your system uses flex

pull the most recent version of pg_dumpall from

the new distribution:

$ cd

$ gunzip -c postgresql-v6.4.tar.gz \

$ gunzip -c postgresql-v6.4.1.tar.gz \

| tar xvf - src/bin/pg_dump/pg_dumpall

$ chmod a+x src/bin/pg_dump/pg_dumpall

$ src/bin/pg_dump/pg_dumpall -z > db.out

$ exit

10. Unzip and untar the new source file. Type

$ cd /usr/src/pgsql

$ gunzip -c ~/postgresql-v6.4.tar.gz | tar xvf -

$ gunzip -c ~/postgresql-v6.4.1.tar.gz | tar xvf -

11. Configure the source code for your system. It

is this step at which you can specify your actual

installation path for the build process (see the

it appears to be a significant issue.

For example,

o For a i686/Linux-ELF platform, no tests

failed since this is the v6.4 regression

failed since this is the v6.4.1 regression

testing reference platform.

o For the SPARC/Linux-ELF platform, using the

970525 beta version of Postgres v6.2 the

not get Postgres to work on you system. Please

send a mail message to pgsql-ports@postgresql.org

telling us the following:

o The version of Postgres (v6.4, 6.3.2, beta 981014, etc.).

o The version of Postgres (v6.4.1, 6.3.2, beta 981014, etc.).

o Your operating system (i.e. RedHat v5.1 Linux v2.0.34).

o Your hardware (SPARC, i486, etc.).

o Did you compile, install and run the regression

Chapter 5. Release Notes

Release 6.4

Release 6.4.1

There are many new features and improvements in this

release. Thanks to our developers and maintainers,

has received some attention, and should be more

robust for more platforms than it has ever been.

Migration to v6.4

Migration to v6.4.1

A dump/restore is not required for those upgrading from 6.4.

A dump/restore using pg_dump or pg_dumpall is

required for those wishing to migrate data from any

previous release of Postgres.

required for those wishing to migrate data from pre-6.4

releases of Postgres. pg_upgrade may be helpful for those

upgrading from 6.3.*.

Detailed Change List

PostgreSQL Data Base Management System (formerly known as Postgres, then

as Postgres95).

This directory contains the version 6.4 release of the PostgreSQL

This directory contains the version 6.4.1 release of the PostgreSQL

database server. The server is not ANSI SQL compliant, but it gets

closer with every release. After you unzip and untar the distribution

file, look at file INSTALL for the installation notes and file HISTORY

Frequently Asked Questions (FAQ) for PostgreSQL

Last updated: SatOct 24 00:12:23 EDT 1998

Last updated: SatDec 12 22:56:56 EST 1998

Current maintainer: Bruce Momjian (maillist@candle.pha.pa.us)

cursors?

3.5) What is an R-tree index and what is it used for?

3.6) What is the maximum size for a tuple?

3.7) I defined indices but my queries don't seem to make use of them.

Why?

3.7) My queries are slow or don't make use of the indexes. Why?

3.8) How do I do regular expression searches? case-insensitive regexp

searching?

3.9) I experienced a server crash during a vacuum. How do I remove the

pg_upgrade utility. Those upgrading from earlier releases require a

dump and restore.

Thoseugrading from versions earlier than 1.09 must upgrade to 1.09

Thoseupgrading from versions earlier than 1.09 must upgrade to 1.09

first without a dump/reload, then dump the data from 1.09, and then

load it into 6.4.

PHP is great for simple stuff, but for more complex stuff, some still

use the perl interface and CGI.pm.

An WWWgatway based on WDB using perl can be downloaded from

An WWWgateway based on WDB using perl can be downloaded from

http://www.eol.ists.ca/~dunlop/wdb-p95

1.13) Does PostgreSQL have a graphical user interface? A report generator? A

embedded query language interface?

We have a nice graphical user interface called pgaccess, which is

shipped as part of thedistribtion. Pgaccess also has a report

shipped as part of thedistribution. Pgaccess also has a report

generator.

The web page is http://www.flex.ro/pgaccess We also include ecpg,

You can also use the postgres -S option to increase the maximum amount

of memory used by each backend process for temporary sorts. Each

buffer is 1K and thedefualt is 512 buffers.

buffer is 1K and thedefault is 512 buffers.

You can also use the cluster command to group data in base tables to

match an index. See the cluster(l) manual page for more details.

operating system can attach to a running backend directly to diagnose

problems.

The postgres program has a -s, -A, -t options that can be veryusefull

The postgres program has a -s, -A, -t options that can be veryuseful

for debugging and performance measurements.

You can also compile with profiling to see what functions are taking

Edit include/storage/sinvaladt.h, and change the value of

MaxBackendId. In the future, we plan to make this a configurable

prameter.

parameter.

2.14) What non-unix ports are available?

the client is running on MS Windows, and communicates via TCP/IP to a

server running on one of our supported Unix platforms.

A file win32.mak is included in thedistributiion for making a Win32

A file win32.mak is included in thedistribution for making a Win32

libpq library and psql.

Someone is attempting to port our PostgreSQL database server to

Windows NT using the Cygnus Unix/NT porting library. He has gotten it

compiled, but initdb is currently failing.

The database server is now working on Windows NT using the Cygnus

Unix/NT porting library. The only feature missing is dynamic loading

of user-defined functions/types. See

http://www.askesis.nl/AskesisPostgresIndex.html for more information.

There is another port using U/Win at

http://surya.wipro.com/uwin/ported.html.

_________________________________________________________________

Section 3: PostgreSQL Features

Tuples do not cross 8k boundaries so a 5k tuple will require 8k of

storage.

3.7)I defined indices but my queriesdon'tseem tomake use ofthem. Why?

3.7)My queries are slow ordon't make use ofthe indexes. Why?

PostgreSQL does not automatically maintain statistics. One has to make

an explicit vacuum call to update the statistics. After statistics are

updated, the optimizer knows how many rows in the table, and can

better decide if it should use indices. Note that the optimizer does

not use indices in cases when the table is small because a sequentail

scan would be faster. For column-specific optimization statistics, use

vacuum analyze.

not use indices in cases when the table is small because a sequential

scan would be faster.

For column-specific optimization statistics, use vacuum analyze.

Vacuum analyze is important for complex multi-join queries, so the

optimizer can estimate the number of rows returned from each table,

and choose the proper join order. The backend does not keep track of

column statistics on its own, and vacuum analyze must be run to

collect them periodically.

Indexes are not used for order by operations.

When usingwildcard operators like LIKE or ~, indices can only be used

if the beginning of the search is anchored to the start of the string.

So, to use indices, LIKE searches can should not begin with %, and

~(regular expression searches) should start with ^.

When usingwild-card operators like LIKE or ~, indices can only be

usedif the beginning of the search is anchored to the start of the

string.So, to use indices, LIKE searches can should not begin with %,

and~(regular expression searches) should start with ^.

3.8) How do I do regular expression searches? case-insensitive regexp

searching?

CREATE TABLE new_table (mycol int);

INSERT INTO new_table SELECT oid, mycol FROM old_table;

Tids are used toindentify specific physical rows with block and

offsetvalues. Tids change after rows are modified or reloaded. They

are usedby index entries to point to physical rows.

Tids are used toidentify specific physical rows with block and offset

values. Tids change after rows are modified or reloaded. They are used

by index entries to point to physical rows.

3.18) What is the meaning of some of the terms used in PostgreSQL?

4.2) I get messages of the type NOTICE:PortalHeapMemoryFree: 0x402251d0 not

in alloc set!

You are pfree'ing something that was not palloc'ed. When writing

user-defined functions, do not include the file "libpq-fe.h". Doing so

will cause your palloc to be a malloc instead of a free. Then, when

the backend pfrees the storage, you get the notice message.

You are pfree'ing something that was not palloc'ed. Beware of mixing

malloc/free and palloc/pfree.

4.3) I've written some nifty new types and functions for PostgreSQL.