error doing

<ProgramListing>

test=>create index pixon polytmp

test->using gist (p:box gist_poly_ops)with (islossy);

test=>CREATE INDEX pixON polytmp

test->USING GIST (p:box gist_poly_ops)WITH (ISLOSSY);

ERROR: cannot open pix

(PostgreSQL 6.3 Sun Feb 1 14:57:30 EST 1998)

<ProgramListing>

-- -- there's a memory leak in rtree poly_ops!!

-- --create index pix2on polytmpusing rtree (p poly_ops);

-- --CREATE INDEX pix2ON polytmpUSING RTREE (p poly_ops);

</ProgramListing>

Roger that!! I thought it could be related to a number of

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/jdbc.sgml,v 1.25 2001/09/13 15:55:22 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/jdbc.sgml,v 1.26 2001/10/12 23:32:33 momjian Exp $

-->

<chapter id="jdbc">

One of the consequences of this, is that you can have one table

refer to a row in another table. For example:

<screen>

test=>create table users (usernamename,fullnametext);

test=>CREATE TABLE users (usernameNAME,fullnameTEXT);

CREATE

test=>create table server (servernamename,adminuser users);

test=>CREATE TABLE server (servernameNAME,adminuser users);

CREATE

test=>insert into usersvalues ('peter','Peter Mount');

test=>INSERT INTO usersVALUES ('peter','Peter Mount');

INSERT 2610132 1

test=>insert into servervalues ('maidast',2610132::users);

test=>INSERT INTO serverVALUES ('maidast',2610132::users);

INSERT 2610133 1

test=>select *from users;

test=>SELECT *FROM users;

username|fullname

--------+--------------

peter |Peter Mount

(1 row)

test=>select *from server;

test=>SELECT *FROM server;

servername|adminuser

----------+---------

maidast | 2610132

This would work if table <classname>table</> has fields <structfield>control</> and <structfield>name</>

(and, perhaps, other fields):

<ProgramListing>

pg_select $pgconn "SELECT *from table" array {

pg_select $pgconn "SELECT *FROM table" array {

puts [format "%5d %s" array(control) array(name)]

}

</ProgramListing>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/libpq++.sgml,v 1.32 2001/09/13 15:55:23 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/Attic/libpq++.sgml,v 1.33 2001/10/12 23:32:34 momjian Exp $

-->

<chapter id="libpqplusplus">

<programlisting>

PgDatabase data;

data.Exec("create table foo (a int4, b char(16), d double precision)");

data.Exec("copy foofrom stdin");

data.Exec("CREATE TABLE foo (a int4, b char(16), d double precision)");

data.Exec("COPY fooFROM STDIN");

data.PutLine("3\tHello World\t4.5\n");

data.PutLine("4\tGoodbye World\t7.11\n");

&amp;...

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v 1.72 2001/09/13 15:55:23 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v 1.73 2001/10/12 23:32:34 momjian Exp $

-->

<chapter id="libpq">

proceed with the connection sequence. Loop thus: Consider a connection

<quote>inactive</quote> by default. If <function>PQconnectPoll</function> last returned <symbol>PGRES_POLLING_ACTIVE</>,

consider it <quote>active</quote> instead. If <function>PQconnectPoll(conn)</function> last returned

<symbol>PGRES_POLLING_READING</symbol>, perform aselect for reading on <function>PQsocket(conn)</function>. If

it last returned <symbol>PGRES_POLLING_WRITING</symbol>, perform aselect for writing on

<symbol>PGRES_POLLING_READING</symbol>, perform aSELECT for reading on <function>PQsocket(conn)</function>. If

it last returned <symbol>PGRES_POLLING_WRITING</symbol>, perform aSELECT for writing on

<function>PQsocket(conn)</function>. If you have yet to call <function>PQconnectPoll</function>, i.e. after the call

to <function>PQconnectStart</function>, behave as if it last returned <symbol>PGRES_POLLING_WRITING</symbol>. If

theselect shows that the socket is ready, consider it <quote>active</quote>. If it has

theSELECT shows that the socket is ready, consider it <quote>active</quote>. If it has

been decided that this connection is <quote>active</quote>, call <function>PQconnectPoll(conn)</function>

again. If this call returns <symbol>PGRES_POLLING_FAILED</symbol>, the connection procedure

has failed. If this call returns <symbol>PGRES_POLLING_OK</symbol>, the connection has been

As an example:

<programlisting>

PQexec(conn, "create table foo (a int4, b char(16), d double precision)");

PQexec(conn, "copy foofrom stdin");

PQexec(conn, "CREATE TABLE foo (a int4, b char(16), d double precision)");

PQexec(conn, "COPY fooFROM STDIN");

PQputline(conn, "3\thello world\t4.5\n");

PQputline(conn,"4\tgoodbye world\t7.11\n");

...

* fetch rows from the pg_database, the system catalog of

* databases

*/

res = PQexec(conn, "DECLARE mycursor CURSOR FORselect *from pg_database");

res = PQexec(conn, "DECLARE mycursor CURSOR FORSELECT *FROM pg_database");

if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)

{

fprintf(stderr, "DECLARE CURSOR command failed\n");

* fetch rows from the pg_database, the system catalog of

* databases

*/

res = PQexec(conn, "DECLARE mycursor BINARY CURSOR FORselect *from test1");

res = PQexec(conn, "DECLARE mycursor BINARY CURSOR FORSELECT *FROM test1");

if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)

{

fprintf(stderr, "DECLARE CURSOR command failed\n");

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.4 2001/09/23 04:06:24 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.5 2001/10/12 23:32:34 momjian Exp $

-->

<chapter id="maintenance">

examine this information is to execute the query

<informalexample>

<programlisting>

select datname, age(datfrozenxid)from pg_database;

SELECT datname, age(datfrozenxid)FROM pg_database;

</programlisting>

</informalexample>

The <literal>age</> column measures the number of transactions from the

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v 1.11 2001/10/09 18:46:00 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/perform.sgml,v 1.12 2001/10/12 23:32:34 momjian Exp $

-->

<chapter id="performance-tips">

vacuum analyze, and 7.2 development sources):

<programlisting>

regression=#explain select *from tenk1;

regression=#EXPLAIN SELECT *FROM tenk1;

NOTICE: QUERY PLAN:

Seq Scan on tenk1 (cost=0.00..333.00 rows=10000 width=148)

This is about as straightforward as it gets. If you do

<programlisting>

select *from pg_classwhere relname = 'tenk1';

SELECT *FROM pg_classWHERE relname = 'tenk1';

</programlisting>

you will find out that <classname>tenk1</classname> has 233 disk

Now let's modify the query to add a qualification clause:

<programlisting>

regression=#explain select *from tenk1where unique1 &lt; 1000;

regression=#EXPLAIN SELECT *FROM tenk1WHERE unique1 &lt; 1000;

NOTICE: QUERY PLAN:

Seq Scan on tenk1 (cost=0.00..358.00 rows=1007 width=148)

Modify the query to restrict the qualification even more:

<programlisting>

regression=#explain select *from tenk1where unique1 &lt; 50;

regression=#EXPLAIN SELECT *FROM tenk1WHERE unique1 &lt; 50;

NOTICE: QUERY PLAN:

Index Scan using tenk1_unique1 on tenk1 (cost=0.00..181.09 rows=49 width=148)

Add another condition to the qualification:

<programlisting>

regression=#explain select *from tenk1where unique1 &lt; 50and

regression=#EXPLAIN SELECT *FROM tenk1WHERE unique1 &lt; 50AND

regression-# stringu1 = 'xxx';

NOTICE: QUERY PLAN:

Let's try joining two tables, using the fields we have been discussing:

<programlisting>

regression=#explain select *from tenk1 t1, tenk2 t2where t1.unique1 &lt; 50

regression-#and t1.unique2 = t2.unique2;

regression=#EXPLAIN SELECT *FROM tenk1 t1, tenk2 t2WHERE t1.unique1 &lt; 50

regression-#AND t1.unique2 = t2.unique2;

NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..330.41 rows=49 width=296)

<programlisting>

regression=# set enable_nestloop = off;

SET VARIABLE

regression=#explain select *from tenk1 t1, tenk2 t2where t1.unique1 &lt; 50

regression-#and t1.unique2 = t2.unique2;

regression=#EXPLAIN SELECT *FROM tenk1 t1, tenk2 t2WHERE t1.unique1 &lt; 50

regression-#AND t1.unique2 = t2.unique2;

NOTICE: QUERY PLAN:

Hash Join (cost=181.22..564.83 rows=49 width=296)

For example, we might get a result like this:

<screen>

regression=#explain analyze

regression-#select *from tenk1 t1, tenk2 t2

regression-# wheret1.unique1 &lt; 50and t1.unique2 = t2.unique2;

regression=#EXPLAIN ANALYZE

regression-#SELECT *FROM tenk1 t1, tenk2 t2

regression-#WHEREt1.unique1 &lt; 50AND t1.unique2 = t2.unique2;

NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..330.41 rows=49 width=296) (actual time=1.31..28.90 rows=50 loops=1)