<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.48 2005/11/19 01:50:08 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.49 2006/04/23 03:39:47 momjian Exp $ -->

<sect1 id="arrays">

<title>Arrays</title>

embedded in element values will be backslash-escaped. For numeric

data types it is safe to assume that double quotes will never appear, but

for textual data types one should be prepared to cope with either presence

or absence of quotes. (This is a change in behavior from pre-7.2

<productname>PostgreSQL</productname> releases.)

or absence of quotes.

</para>

<para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.79 2006/03/10 19:10:46 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/backup.sgml,v 2.80 2006/04/23 03:39:48 momjian Exp $ -->

<chapter id="backup">

<title>Backup and Restore</title>

the number after the first dot changes). This does not apply to

different minor releases under the same major release (where the

number after the second dot changes); these always have compatible

storage formats. For example, releases 7.0.1, 7.1.2, and 7.2 are

not compatible, whereas 7.1.1 and 7.1.2 are. When you update

storage formats. For example, releases 7.2.1, 7.3.2, and 7.4 are

not compatible, whereas 7.2.1 and 7.2.2 are. When you update

between compatible versions, you can simply replace the executables

and reuse the data directory on disk. Otherwise you need to back

up your data and restore it on the new server. This has to be done

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.54 2006/04/18 12:41:15 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.55 2006/04/23 03:39:48 momjian Exp $ -->

<chapter Id="runtime-config">

<title>Server Configuration</title>

<indexterm><primary>inheritance</></>

<listitem>

<para>

This controls the inheritance semantics, in particular whether

subtables are included by various commands by default. They were

not included in versions prior to 7.1. If you need the old

behavior you can set this variable to <literal>off</>, but in

the long run you are encouraged to change your applications to

use the <literal>ONLY</literal> key word to exclude subtables.

See <xref linkend="ddl-inherit"> for more information about

inheritance.

This controls the inheritance semantics. If turned <literal>off</>,

subtables are not included by various commands by default; basically

an implied <literal>ONLY</literal> key word. This was added for

compatibility with releases prior to 7.1. See

<xref linkend="ddl-inherit"> for more information.

</para>

</listitem>

</varlistentry>

<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.166 2006/03/10 19:10:47 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.167 2006/04/23 03:39:49 momjian Exp $ -->

<chapter id="datatype">

<title id="datatype-title">Data Types</title>

string.

</para>

<para>

If one explicitly casts a value to <type>character

varying(<replaceable>n</>)</type> or

<type>character(<replaceable>n</>)</type>, then an over-length

value will be truncated to <replaceable>n</> characters without

raising an error. (This too is required by the

<acronym>SQL</acronym> standard.)

</para>

<note>

<para>

Prior to <productname>PostgreSQL</> 7.2, strings that were too long were

always truncated without raising an error, in either explicit or

implicit casting contexts.

</para>

</note>

<para>

If one explicitly casts a value to <type>character

varying(<replaceable>n</>)</type> or

<type>character(<replaceable>n</>)</type>, then an over-length

value will be truncated to <replaceable>n</> characters without

raising an error. (This too is required by the

<acronym>SQL</acronym> standard.)

</para>

<para>

The notations <type>varchar(<replaceable>n</>)</type> and

</para>

</note>

<note>

<para>

Prior to <productname>PostgreSQL</> 7.2, <type>bit</type> data

was always silently truncated or zero-padded on the right, with

or without an explicit cast. This was changed to comply with the

<acronym>SQL</acronym> standard.

</para>

</note>

<para>

Refer to <xref

linkend="sql-syntax-bit-strings"> for information about the syntax

<!-- $PostgreSQL: pgsql/doc/src/sgml/datetime.sgml,v 2.48 2006/03/10 19:10:47 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/datetime.sgml,v 2.49 2006/04/23 03:39:50 momjian Exp $ -->

<appendix id="datetime-appendix">

<title>Date/Time Support</title>

<tip>

<para>

Gregorian years AD 1-99 may be entered by using 4 digits with leading

zeros (e.g., <literal>0099</> is AD 99). Previous versions of

<productname>PostgreSQL</productname> accepted years with three

digits and with single digits, but as of version 7.0 the rules have

been tightened up to reduce the possibility of ambiguity.

zeros (e.g., <literal>0099</> is AD 99).

</para>

</tip>

</para>

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.55 2006/02/18 23:14:45 neilc Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.56 2006/04/23 03:39:50 momjian Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

<note>

<title>Deprecated</title>

<para>

Inprevious versionsof <productname>PostgreSQL</productname>, the

Inreleasesof <productname>PostgreSQL</productname> prior to 7.1, the

default behavior was not to include child tables in queries. This was

found to be error prone and is also in violation of the SQL

standard. Under the old syntax, to include the child tables you append

<literal>*</literal> to the table name. For example:

<programlisting>

SELECT * from cities*;

</programlisting>

You can still explicitly specify scanning child tables by

appending <literal>*</literal>, as well as explicitly specify not

scanning child tables by writing <literal>ONLY</literal>. But

beginning in version 7.1, the default behavior for an undecorated

table name is to scan its child tables too, whereas before the

default was not to do so. To get the old default behavior,

disable the <xref linkend="guc-sql-inheritance"> configuration

found to be error prone and also in violation of the SQL

standard. You can get the pre-7.1 behavior by turning off the

<xref linkend="guc-sql-inheritance"> configuration

option.

</para>

</note>

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.313 2006/03/10 20:15:25 neilc Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.314 2006/04/23 03:39:50 momjian Exp $ -->

<chapter id="functions">

<title>Functions and Operators</title>

the result is given to the full available precision.

</para>

<note>

<para>

Prior to <productname>PostgreSQL</productname> 7.2, the precision

parameters were unimplemented, and the result was always given

in integer seconds.

</para>

</note>

<para>

Some examples:

<screen>

<!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.206 2006/03/10 19:10:48 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.207 2006/04/23 03:39:51 momjian Exp $ -->

<chapter id="libpq">

<title><application>libpq</application> - C Library</title>

<indexterm><primary>libpq-int.h</></>

<application>libpq</application> application programmers should be careful to

maintain the <structname>PGconn</structname> abstraction. Use the accessor

functions described below to get

at the contents of <structname>PGconn</structname>. Avoid directly referencing the fields of the

<structname>PGconn</> structure because they are subject to change in the future.

(Beginning in <productname>PostgreSQL</productname> release 6.4, the

definition of the <type>struct</type> behind <structname>PGconn</> is not even provided in <filename>libpq-fe.h</filename>.

If you have old code that accesses <structname>PGconn</structname> fields directly, you can keep using it

by including <filename>libpq-int.h</filename> too, but you are encouraged to fix the code

soon.)

functions described below to get at the contents of <structname>PGconn</structname>.

Reference to internal <structname>PGconn</structname> fields using

<filename>libpq-int.h</> is not recommended because they are subject to change

in the future.

</para>

</tip>

typedef struct pgNotify {

char *relname; /* notification condition name */

int be_pid; /* process ID of server process */

int be_pid; /* process ID ofnotifyingserver process */

char *extra; /* notification parameter */

} PGnotify;

</synopsis>

always point to an empty string.)

</para>

<note>

<para>

In <productname>PostgreSQL</productname> 6.4 and later,

the <structfield>be_pid</structfield> is that of the notifying server process,

whereas in earlier versions it was always the <acronym>PID</acronym> of your own server process.

</para>

</note>

<para>

<xref linkend="libpq-example-2"> gives a sample program that illustrates the use

of asynchronous notification.

</itemizedlist>

</para>

<para>

<indexterm><primary>libpq-int.h</></>

If your codes references the header file

<filename>libpq-int.h</filename> and you refuse to fix your code to

not use it, starting in <productname>PostgreSQL</> 7.2, this file will be found in

<filename><replaceable>includedir</replaceable>/postgresql/internal/libpq-int.h</filename>,

so you need to add the appropriate <option>-I</option> option to

your compiler command line.

</para>

</sect1>

<!-- $PostgreSQL: pgsql/doc/src/sgml/lobj.sgml,v 1.39 2006/03/10 19:10:48 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/lobj.sgml,v 1.40 2006/04/23 03:39:52 momjian Exp $ -->

<chapter id="largeObjects">

<title id="largeObjects-title">Large Objects</title>

values. This is not described here.

</para>

<sect1 id="lo-history">

<title>History</title>

<sect1 id="lo-intro">

<title>Introduction</title>

<para>

<productname>POSTGRES 4.2</productname>, the indirect predecessor

of <productname>PostgreSQL</productname>, supported three standard

implementations of large objects: as files external to the

<productname>POSTGRES</productname> server, as external files

managed by the <productname>POSTGRES</productname> server, and as

data stored within the <productname>POSTGRES</productname>

database. This caused considerable confusion among users. As a

result, only support for large objects as data stored within the

database is retained in <productname>PostgreSQL</productname>.

Even though this is slower to access, it provides stricter data

integrity. For historical reasons, this storage scheme is

referred to as <firstterm>Inversion large

objects</firstterm>. (You will see the term Inversion used

occasionally to mean the same thing as large object.) Since

<productname>PostgreSQL 7.1</productname>, all large objects are

placed in one system table called

<classname>pg_largeobject</classname>.

</para>

<indexterm>

<primary>TOAST</primary>

<secondary>versus large objects</secondary>

</indexterm>

<para>

<indexterm>

<primary>TOAST</primary>

<secondary>versus large objects</secondary>

</indexterm>

<productname>PostgreSQL</productname> 7.1 introduced a mechanism

(nicknamed <quote><acronym>TOAST</acronym></quote>) that allows

data values to be much larger than single pages. This

makes the large object facility partially obsolete. One

All large objects are placed in a single system table called

<classname>pg_largeobject</classname>.

<productname>PostgreSQL</productname> also supports a storage system called

<quote><acronym>TOAST</acronym></quote> that automatically stores values

larger than a single database page into a secondary storage area per table.

This makes the large object facility partially obsolete. One

remaining advantage of the large object facility is that it allows values

up to 2 GB in size, whereas <acronym>TOAST</acronym>ed fields can be at

most 1 GB. Also, large objects can bemanipulated piece-by-piece much more

different.

most 1 GB. Also, large objects can berandomly modified using a read/write

<acronym>TOAST</acronym>.

</para>

</sect1>

<title>Implementation Features</title>

<para>

The large object implementation breakslarge

objectsupinto<quote>chunks</quote>andstores the chunks in

The large object implementation breaks large

objects up into <quote>chunks</quote> and stores the chunks in

rows in the database. A B-tree index guarantees fast

searches for the correct chunk number when doing random

access reads and writes.

<productname>PostgreSQL</productname> client interface libraries

provide for accessing large objects. All large object

manipulation using these functions <emphasis>must</emphasis> take

place within an SQL transaction block. (This requirement is

strictly enforced as of <productname>PostgreSQL 6.5</>, though it

has been an implicit requirement in previous versions, resulting

in misbehavior if ignored.)

place within an SQL transaction block.

The <productname>PostgreSQL</productname> large object interface is modeled after

the <acronym>Unix</acronym> file-system interface, with analogues of

<function>open</function>, <function>read</function>,

<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.54 2006/03/10 19:10:48 momjian Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.55 2006/04/23 03:39:52 momjian Exp $ -->

<chapter id="maintenance">

<title>Routine Database Maintenance Tasks</title>

</para>

<para>

Beginning in <productname>PostgreSQL</productname> 7.2, the standard form

of <command>VACUUM</> can run in parallel with normal database operations

(selects, inserts, updates, deletes, but not changes to table definitions).

Routine vacuuming is therefore not nearly as intrusive as it was in prior

releases, and it is not as critical to try to schedule it at low-usage

times of day.

</para>

<para>

The standard form of <command>VACUUM</> can run in parallel with

normal database operations (SELECTs, INSERTs, UPDATEs, DELETEs, but not

changes to table definitions).

Beginning in <productname>PostgreSQL</productname> 8.0, there are

configuration parameters that can be adjusted to further reduce the

performance impact of background vacuuming. See

It is possible to run <command>ANALYZE</> on specific tables and even

just specific columns of a table, so the flexibility exists to update some

statistics more frequently than others if your application requires it.

In practice, however, the usefulness of this feature is doubtful.

Beginning in <productname>PostgreSQL</productname> 7.2,

<command>ANALYZE</> is a fairly fast operation even on large tables,

because it uses a statistical random sampling of the rows of a table

rather than reading every single row. So it's probably much simpler

to just run it over the whole database every so often.

In practice, however, it is usually best to just analyze the entire database

because it is a fast operation. It uses a statistical random sampling of

the rows of a table rather than reading every single row.

</para>

<tip>

transactions that were in the past appear to be in the future &mdash; which

means their outputs become invisible. In short, catastrophic data loss.

(Actually the data is still there, but that's cold comfort if you can't

get at it.)

</para>

<para>

Prior to <productname>PostgreSQL</productname> 7.2, the only defense

against XID wraparound was to re-<command>initdb</> at least every 4

billion transactions. This of course was not very satisfactory for

high-traffic sites, so a better solution has been devised. The new

approach allows a server to remain up indefinitely, without

<command>initdb</> or any sort of restart. The price is this

maintenance requirement: <emphasis>every table in the database must

be vacuumed at least once every billion transactions</emphasis>.

get at it.) To avoid this, it is <emphasis>necessary to vacuum every table

in every database at least once every billion transactions</emphasis>.

</para>

<para>