<!--

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.31 2002/11/11 20:14:02 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/advanced.sgml,v 1.32 2003/02/19 04:06:27 momjian Exp $

-->

<chapter id="tutorial-advanced">

<para>

Refer back to the queries in <xref linkend="tutorial-join">.

Suppose the combined listing of weather records and city location

is of particular interest to your application, but youdon't want

is of particular interest to your application, but youdo not want

to type the query each time you need it. You can create a

<firstterm>view</firstterm> over the query, which gives a name to

the query that you can refer to like an ordinary table.

</para>

<para>

If, partway through the transaction, we decide wedon't want to

If, partway through the transaction, we decide wedo not want to

commit (perhaps we just noticed that Alice's balance went negative),

we can issue the command <command>ROLLBACK</> instead of

<command>COMMIT</>, and all our updates so far will be canceled.

</para>

<para>

<productname>PostgreSQL</> actually treats every SQL statement as being

executed within a transaction. If youdon't issue a <command>BEGIN</>

executed within a transaction. If youdo not issue a <command>BEGIN</>

command,

then each individual statement has an implicit <command>BEGIN</> and

(if successful) <command>COMMIT</> wrapped around it. A group of

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/arch-dev.sgml,v 2.17 2003/01/1900:13:28 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/arch-dev.sgml,v 2.18 2003/02/1904:06:27 momjian Exp $

-->

<chapter id="overview">

<para>

This chapter gives an overview of the internal structure of the

backend of <productname>PostgreSQL</productname>.

After havingread the following sections you

detaileddescriptionhere (I think such a description dealing with

all data structures and functions used within<productname>PostgreSQL</productname>

control and data flow withinthebackend from receiving a queryto

sending the results.

backend of <productname>PostgreSQL</productname>. After having

read the following sections you should have an idea of how a query

descriptionof the internal operation of

<productname>PostgreSQL</productname>, as such a document would be

backend fromthepoint at which a queryis received, to the point

when the results are returned to the client.

</para>

<sect1 id="query-path">

<title>How Connections are Established</title>

<para>

<productname>PostgreSQL</productname> is implemented using a simple "process per-user"

client/server model. In this model there is one <firstterm>client process</firstterm>

connected to exactly one <firstterm>server process</firstterm>.

As we don't know <foreignphrase>per se</foreignphrase>

how many connections will be made, we have to use a <firstterm>master process</firstterm>

that spawns a new server process every time a connection is

requested. This master process is called <literal>postmaster</literal> and

listens at a specified TCP/IP port for incoming connections. Whenever

a request for a connection is detected the <literal>postmaster</literal> process

spawns a new server process called <literal>postgres</literal>. The server

tasks (<literal>postgres</literal> processes) communicate with each other using

<firstterm>semaphores</firstterm> and <firstterm>shared memory</firstterm>

to ensure data integrity

throughout concurrent data access. Figure

\ref{connection} illustrates the interaction of the master process

<literal>postmaster</literal> the server process <literal>postgres</literal> and a client

application.

<productname>PostgreSQL</productname> is implemented using a

simple "process per-user" client/server model. In this model

there is one <firstterm>client process</firstterm> connected to

exactly one <firstterm>server process</firstterm>. As we do not

know ahead of time how many connections will be made, we have to

use a <firstterm>master process</firstterm> that spawns a new

server process every time a connection is requested. This master

process is called <literal>postmaster</literal> and listens at a

specified TCP/IP port for incoming connections. Whenever a request

for a connection is detected the <literal>postmaster</literal>

process spawns a new server process called

<literal>postgres</literal>. The server tasks

(<literal>postgres</literal> processes) communicate with each

other using <firstterm>semaphores</firstterm> and

<firstterm>shared memory</firstterm> to ensure data integrity

throughout concurrent data access. Figure \ref{connection}

illustrates the interaction of the master process

<literal>postmaster</literal> the server process

<literal>postgres</literal> and a client application.

</para>

<para>

for the <literal>varno</literal> fields in the

<literal>VAR</literal> nodes appearing in the <literal>mergeclauses</literal> list (and also in the

<literal>targetlist</literal>) mean that not the tuples of the current node should be

considered but the tuples of the next"deeper" nodes (i.e. the top

considered but the tuples of the next<quote>deeper</quote> nodes (i.e. the top

nodes of the subplans) should be used instead.

</para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.114 2003/02/1903:48:10 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.115 2003/02/1904:06:27 momjian Exp $

-->

<chapter id="datatype">

actual string, and in case of <type>character</type> plus the

padding. Long strings are compressed by the system automatically, so

the physical requirement on disk may be less. Long values are also

stored in background tables so theydon't interfere with rapid

stored in background tables so theydo not interfere with rapid

access to the shorter column values. In any case, the longest

possible character string that can be stored is about 1 GB. (The

maximum value that will be allowed for <replaceable>n</> in the data

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.11 2003/02/1903:13:24 momjian Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.12 2003/02/1904:06:27 momjian Exp $ -->

<chapter id="ddl">

<title>Data Definition</title>

</para>

<para>

If youdon'tneed a table anymore, you can remove it using the

<literal>DROP TABLE</literal> command. For example:

If youno longerneed a table, you can remove it using the

<command>DROP TABLE</command> command. For example:

<programlisting>

DROP TABLE my_first_table;

DROP TABLE products;

<para>

We know that the foreign keys disallow creation of orders that

don't relate to any products. But what if a product is removed

do not relate to any products. But what if a product is removed

after an order is created that references it? SQL allows you to

specify that as well. Intuitively, we have a few options:

<itemizedlist spacing="compact">

<para>

Restricting and cascading deletes are the two most common options.

<literal>RESTRICT</literal> can also be written as <literal>NO

ACTION</literal> and it's also the default if youdon't specify

ACTION</literal> and it's also the default if youdo not specify

anything. There are two other options for what should happen with

the foreign key columns when a primary key is deleted:

<literal>SET NULL</literal> and <literal>SET DEFAULT</literal>.

<para>

In the SQL standard, the notion of objects in the same schema

being owned by different users does not exist. Moreover, some

implementationsdon't allow you to create schemas that have a

implementationsdo not allow you to create schemas that have a

different name than their owner. In fact, the concepts of schema

and user are nearly equivalent in a database system that

implements only the basic schema support specified in the

ERROR: Cannot drop table products because other objects depend on it

Use DROP ... CASCADE to drop the dependent objects too

</screen>

The error message contains a useful hint:If youdon't want to

The error message contains a useful hint:if youdo not want to

bother deleting all the dependent objects individually, you can run

<screen>

DROP TABLE products CASCADE;

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/docguide.sgml,v 1.42 2002/07/28 15:22:20 petere Exp $ -->

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/docguide.sgml,v 1.43 2003/02/19 04:06:27 momjian Exp $ -->

<appendix id="docguide">

<title>Documentation</title>

<listitem>

<para>

If the program uses 0 for success and non-zero for failure,

then youdon't need to document it. If there is a meaning

then youdo not need to document it. If there is a meaning

behind the different non-zero exit codes, list them here.

</para>

</listitem>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.32 2003/01/11 00:00:02 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.33 2003/02/19 04:06:28 momjian Exp $

-->

<chapter id="mvcc">

</para>

<para>

The main difference between multiversion and lock models is that

in <acronym>MVCC</acronym> locks acquired for querying (reading)

data don't conflict with locks acquired for writing data, and so

The main advantage to using the <acronym>MVCC</acronym> model of

concurrency control rather than locking is that in

<acronym>MVCC</acronym> locks acquired for querying (reading) data

do not conflict with locks acquired for writing data, and so

reading never blocks writing and writing never blocks reading.

</para>

</para>

<para>

The <command>SELECT</command>command acquires a

lock of this mode on referenced tables. In general, any query

this lock mode.

Thecommands<command>SELECT</command>and

<command>ANALYZE</command> acquire alock of this mode on

and does not modify it will acquirethis lock mode.

</para>

</listitem>

</varlistentry>

<para>

Acquired by the <command>ALTER TABLE</command>, <command>DROP

TABLE</command>, and <command>VACUUM FULL</command> commands.

This is also the default lock mode for <command>LOCK TABLE</command>

statements that do not specify a mode explicitly.

TABLE</command>, <command>REINDEX</command>,

<command>CLUSTER</command>, and <command>VACUUM FULL</command>

commands. This is also the default lock mode for <command>LOCK

TABLE</command> statements that do not specify a mode explicitly.

</para>

</listitem>

</varlistentry>

A row-level lock on a specific row is automatically acquired when the

row is updated (or deleted or marked for update). The lock is held

until the transaction commits or rolls back.

Row-level locksdon't affect data

Row-level locksdo not affect data

querying; they block <emphasis>writers to the same row</emphasis>

only. To acquire a row-level lock on a row without actually

modifying the row, select the row with <command>SELECT FOR

<para>

Because readers in <productname>PostgreSQL</productname>

don't lock data, regardless of

do not lock data, regardless of

transaction isolation level, data read by one transaction can be

overwritten by another concurrent transaction. In other words,

if a row is returned by <command>SELECT</command> it doesn't mean that

<para>

The final section is the "special section" which may contain anything the

access method wishes to store. Ordinary tables do not use this at all

(indicated by setting <structfield>pd_special</> to equal the pagesize).

The final section is the <quote>special section</quote> which may

contain anything the access method wishes to store. Ordinary tables

do not use this at all (indicated by setting

<structfield>pd_special</> to equal the pagesize).

</para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.14 2003/01/1900:13:28 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.15 2003/02/1904:06:28 momjian Exp $

-->

<chapter id="plpgsql">

</para>

<para>

By using <type>%TYPE</type> youdon't need to know

By using <type>%TYPE</type> youdo not need to know

the data type of the structure you are referencing,

and most important, if the data type of the

referenced item changes in the future (e.g: you

up a <firstterm>cursor</> that encapsulates the query, and then read

the query result a few rows at a time. One reason for doing this is

to avoid memory overrun when the result contains a large number of

rows. (However, <application>PL/pgSQL</> usersdon't normally need

rows. (However, <application>PL/pgSQL</> usersdo not normally need

to worry about that, since FOR loops automatically use a cursor

internally to avoid memory problems.) A more interesting usage is to

return a reference to a cursor that it has created, allowing the

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.55 2003/02/13 05:19:59 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.56 2003/02/19 04:06:28 momjian Exp $

PostgreSQL documentation

-->

default and NOT NULL clauses for the new column are not supported.

The new column always comes into being with all values NULL.

You can use the <literal>SET DEFAULT</literal> form

of <command>ALTER TABLE</command> to set the defaultafterwards.

of <command>ALTER TABLE</command> to set the defaultafterward.

(You may also want to update the already existing rows to the

new default value, using

<xref linkend="sql-update" endterm="sql-update-title">.)

</para>

<para>

If a table has any descendant tables, it is not permitted to ADD or

RENAME a column in the parent table without doing the same to the

descendants --- that is, ALTER TABLE ONLY will be rejected. This

ensures that the descendants always have columns matching the parent.

If a table has any descendant tables, it is not permitted to ADD

or RENAME a column in the parent table without doing the same to

the descendants --- that is, <command>ALTER TABLE ONLY</command>

will be rejected. This ensures that the descendants always have

columns matching the parent.

</para>

<para>

A recursive DROP COLUMN operation will remove a descendant table's column

only if the descendant does not inherit that column from any other

parents and never had an independent definition of the column.

A nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN)

never removes any descendant columns, but instead marks them as

independently defined rather than inherited.

A recursive <literal>DROP COLUMN</literal> operation will remove a

descendant table's column only if the descendant does not inherit

that column from any other parents and never had an independent

definition of the column. A nonrecursive <literal>DROP

COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP

COLUMN</command>) never removes any descendant columns, but

instead marks them as independently defined rather than inherited.

</para>

<para>