<!--

$PostgreSQL: pgsql/doc/src/sgml/ecpg.sgml,v 1.61 2005/01/07 05:43:28 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/ecpg.sgml,v 1.62 2005/01/08 22:13:25 tgl Exp $

-->

<chapter id="ecpg">

the error message that is stored in

<literal>sqlca.sqlerrm.sqlerrmc</literal> (the result of

<function>strlen()</function>, not really interesting for a C

programmer).

programmer). Note that some messages are too long to fit in the

fixed-size <literal>sqlerrmc</literal> array; they will be truncated.

</para>

<para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.14 2003/11/29 19:51:37 pgsql Exp $

$PostgreSQL: pgsql/doc/src/sgml/gist.sgml,v 1.15 2005/01/08 22:13:25 tgl Exp $

-->

<chapterId="GiST">

<chapterid="GiST">

<title>GiST Indexes</title>

<sect1 id="intro">

<title>Introduction</title>

<para>

<indexterm>

<primary>index</primary>

<secondary>GiST</secondary>

</indexterm>

<indexterm>

<primary>GiST</primary>

<see>index</see>

</indexterm>

<acronym>GiST</acronym> stands for Generalized Search Tree. It is a

balanced, tree-structured access method, that acts as a base template in

which to implement arbitrary indexing schemes. B+-trees, R-trees and many

<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.48 2004/12/23 23:07:38 tgl Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.49 2005/01/08 22:13:25 tgl Exp $ -->

<chapter id="indexes">

<title id="indexes-title">Indexes</title>

<para>

<productname>PostgreSQL</productname> provides several index types:

B-tree, R-tree,GiST, andHash. Each index type uses a different

B-tree, R-tree,Hash, andGiST. Each index type uses a different

algorithm that is best suited to different types of queries.

By default, the <command>CREATE INDEX</command> command will create a

B-tree index, which fits the most common situations.

</para>

<para>

<indexterm>

<primary>index</primary>

<secondary>B-tree</secondary>

<primary>B-tree</primary>

<see>index</see>

</indexterm>

By default, the <command>CREATE INDEX</command> command will create a

B-tree index, which fits the most common situations. B-trees can

handle equality and range queries on data that can be sorted into

some ordering. In

particular, the <productname>PostgreSQL</productname> query planner

B-trees can handle equality and range queries on data that can be sorted

into some ordering.

In particular, the <productname>PostgreSQL</productname> query planner

will consider using a B-tree index whenever an indexed column is

involved in a comparison using one of these operators:

<simplelist type="inline">

<simplelist>

<member><literal>&lt;</literal></member>

<member><literal>&lt;=</literal></member>

<member><literal>=</literal></member>

<member><literal>&gt;=</literal></member>

<member><literal>&gt;</literal></member>

</simplelist>

Constructs equivalent to combinations of these operators, such as

<literal>BETWEEN</> and <literal>IN</>, can also be implemented with

a B-tree index search. (But note that <literal>IS NULL</> is not

equivalent to <literal>=</> and is not indexable.)

</para>

<para>

'foo%'</literal> or <literal>col ~ '^foo'</literal>, but not

<literal>col LIKE '%bar'</literal>. However, if your server does

not use the C locale you will need to create the index with a

special operator class. See <xref linkend="indexes-opclass">

below.

special operator class to support indexing of pattern-matching queries.

See <xref linkend="indexes-opclass">below.

</para>

<para>

consider using an R-tree index whenever an indexed column is

involved in a comparison using one of these operators:

<simplelist type="inline">

<simplelist>

<member><literal>&lt;&lt;</literal></member>

<member><literal>&amp;&lt;</literal></member>

<member><literal>&amp;&gt;</literal></member>

<member><literal>~=</literal></member>

<member><literal>&amp;&amp;</literal></member>

</simplelist>

(Refer to <xref linkend="functions-geometry"> about the meaning of

(See <xref linkend="functions-geometry"> for the meaning of

these operators.)

</para>

</note>

</para>

<para>

GiST indexes are not a single kind of index, but rather an infrastructure

within which many different indexing strategies can be implemented.

Accordingly, the particular operators with which a GiST index can be

used vary depending on the indexing strategy (the <firstterm>operator

class</>). For more information see <xref linkend="GiST">.

</para>

<para>

The B-tree index method is an implementation of Lehman-Yao

high-concurrency B-trees. The R-tree index method implements

<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.224 2005/01/0809:54:47 petere Exp $ -->

<!-- $PostgreSQL: pgsql/doc/src/sgml/installation.sgml,v 1.225 2005/01/0822:13:26 tgl Exp $ -->

<chapter id="installation">

<title><![%standalone-include[<productname>PostgreSQL</>]]>

This <![%standalone-include;[document]]>

<![%standalone-ignore;[chapter]]> describes the installation of

<productname>PostgreSQL</productname> from the source code

distribution.

distribution. (If you are installing a pre-packaged distribution,

such as an RPM or Debian package, ignore this

<![%standalone-include;[document]]>

<![%standalone-ignore;[chapter]]>

and read the packager's instructions instead.)

</para>

<sect1 id="install-short">

<!--

$PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.176 2005/01/06 21:20:43 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/libpq.sgml,v 1.177 2005/01/08 22:13:28 tgl Exp $

-->

<chapter id="libpq">

This feature allows commands

that will be used repeatedly to be parsed and planned just once, rather

than each time they are executed.

The statement must have been prepared previously in the current session.

<function>PQexecPrepared</> is supported only in protocol 3.0 and later

connections; it will fail when using protocol 2.0.

</para>

<!--

$PostgreSQL: pgsql/doc/src/sgml/lobj.sgml,v 1.34 2004/12/28 22:47:15 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/lobj.sgml,v 1.35 2005/01/08 22:13:33 tgl Exp $

-->

<chapter id="largeObjects">

or'ing together the bits <symbol>INV_READ</symbol> and

<symbol>INV_WRITE</symbol>. The low-order sixteen bits of the mask have

historically been used at Berkeley to designate the storage manager number on which the large object

should reside. These

bits should always be zero now.

The return value is the OID that was assigned to the new large object.

should reside. These bits should always be zero now. (The access type

does not actually do anything anymore either, but one or both flag bits

must be set to avoid an error.)

The return value is the OID that was assigned to the new large object,

or InvalidOid (zero) on failure.

</para>

<para>

An example:

<programlisting>

inv_oid = lo_creat(INV_READ|INV_WRITE);

inv_oid = lo_creat(conn,INV_READ|INV_WRITE);

</programlisting>

</para>

</sect2>

<replaceable class="parameter">filename</replaceable>

specifies the operating system name of

the file to be imported as a large object.

The return value is the OID that was assigned to the new large object.

The return value is the OID that was assigned to the new large object,

or InvalidOid (zero) on failure.

Note that the file is read by the client interface library, not by

the server; so it must exist in the client filesystem and be readable

by the client application.

int lo_export(PGconn *conn, Oid lobjId, const char *filename);

</synopsis>

<indexterm><primary>lo_export</></>

The <parameter>lobjId</parameter> argument specifiestheOIDofthe large

objecttoexportand the <parameter>filename</parameter> argument specifies

the operating system name of the file.

Note that the file iswritten by the client interface library, not by

the server.

The <parameter>lobjId</parameter> argument specifies the OID of the large

object to export and the <parameter>filename</parameter> argument

specifiesthe operating system name of the file. Note that the file is

written by the client interface library, not by the server. Returns 1

on success, -1 on failure.

</para>

</sect2>

<sect2>

<title>Opening an Existing Large Object</title>

<para>

To open an existing large object, call

To open an existing large object for reading or writing, call

<synopsis>

int lo_open(PGconn *conn, Oid lobjId, int mode);

</synopsis>

object is opened for reading (<symbol>INV_READ</>), writing (<symbol>INV_WRITE</symbol>), or

both.

A large object cannot be opened before it is created.

<function>lo_open</function> returns a large object descriptor

for later use in <function>lo_read</function>, <function>lo_write</function>,

<function>lo_lseek</function>, <function>lo_tell</function>, and

<function>lo_close</function>. The descriptor is only valid for

<function>lo_open</function> returns a (non-negative) large object

descriptor for later use in <function>lo_read</function>,

<function>lo_write</function>, <function>lo_lseek</function>,

<function>lo_tell</function>, and <function>lo_close</function>.

The descriptor is only valid for

the duration of the current transaction.

On failure, -1 is returned.

</para>

</sect2>

are <symbol>SEEK_SET</> (seek from object start),

<symbol>SEEK_CUR</> (seek from current position), and

<symbol>SEEK_END</> (seek from object end). The return value is

the new location pointer.

the new location pointer, or -1 on error.

</para>

</sect2>

</synopsis>

<indexterm><primary>lo_unlink</></> The

<parameter>lobjId</parameter> argument specifies the OID of the

large object to remove. In the event of an error, the return

value is negative.

large object to remove. Returns 1 if successful, -1 on failure.

</para>

</sect2>

</sect1>

<sect1 id="lo-funcs">

<title>Server-Side Functions</title>

<para>

There are two built-in server-side functions,

<function>lo_import</function><indexterm><primary>lo_import</></>

and

<function>lo_export</function>,<indexterm><primary>lo_export</></>

for large object access, which are available for use in

<acronym>SQL</acronym> commands. Here is an example of their

use:

<para>

There are server-side functions callable from SQL that correspond to

each of the client-side functions described above; indeed, for the

most part the client-side functions are simply interfaces to the

equivalent server-side functions. The ones that are actually useful

to call via SQL commands are

<function>lo_creat</function><indexterm><primary>lo_creat</></>,

<function>lo_unlink</function><indexterm><primary>lo_unlink</></>,

<function>lo_import</function><indexterm><primary>lo_import</></>, and

<function>lo_export</function><indexterm><primary>lo_export</></>.

Here are examples of their use:

<programlisting>

CREATE TABLE image (

name text,

raster oid

);

SELECT lo_creat(-1); -- returns OID of new, empty large object

SELECT lo_unlink(173454); -- deletes large object with OID 173454

INSERT INTO image (name, raster)

VALUES ('beautiful image', lo_import('/etc/motd'));

SELECT lo_export(image.raster, '/tmp/motd') FROM image

WHERE name = 'beautiful image';

</programlisting>

</para>

<para>

These functions read and write files in the server's file system, using the

permissions of the database's owning user. Therefore, their use is restricted

to superusers. (In contrast, the client-side import and export functions

read and write files in the client's file system, using the permissions of

the client program. Their use is not restricted.)

</para>

</para>

<para>

The server-side <function>lo_import</function> and

<function>lo_export</function> functions behave considerably differently

from their client-side analogs. These two functions read and write files

in the server's file system, using the permissions of the database's

owning user. Therefore, their use is restricted to superusers. In

contrast, the client-side import and export functions read and write files

in the client's file system, using the permissions of the client program.

The client-side functions can be used by any

<productname>PostgreSQL</productname> user.

</para>

</sect1>

<sect1 id="lo-examplesect">

<!--

$PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.39 2004/12/27 22:30:10 tgl Exp $

$PostgreSQL: pgsql/doc/src/sgml/manage-ag.sgml,v 2.40 2005/01/08 22:13:34 tgl Exp $

-->

<chapter id="managing-databases">

managing schemas is in <xref linkend="ddl-schemas">.

</para>

<para>

Databases are created with the <command>CREATE DATABASE</> command

(see <xref linkend="manage-ag-createdb">) and destroyed with the

<command>DROP DATABASE</> command

(see <xref linkend="manage-ag-dropdb">).

To determine the set of existing databases, examine the

<structname>pg_database</> system catalog, for example

<synopsis>

SELECT datname FROM pg_database;

</synopsis>

The <xref linkend="app-psql"> program's <literal>\l</> meta-command

and <option>-l</> command-line option are also useful for listing the

existing databases.

</para>

<note>

<para>

The <acronym>SQL</> standard calls databases <quote>catalogs</>, but there

</para>

<para>

To simplify the implementation of tablespaces,

<productname>PostgreSQL</> makes extensive use of symbolic links. This

To remove an empty tablespace, use the <xref linkend="sql-droptablespace">

command.

</para>

<para>

To determine the set of existing tablespaces, examine the

<structname>pg_tablespace</> system catalog, for example

<synopsis>

SELECT spcname FROM pg_tablespace;

</synopsis>

The <xref linkend="app-psql"> program's <literal>\db</> meta-command

is also useful for listing the existing tablespaces.

</para>

<para>

<productname>PostgreSQL</> makes extensive use of symbolic links

to simplify the implementation of tablespaces. This

means that tablespaces can be used <emphasis>only</> on systems

that support symbolic links.

</para>