-
Bruce Momjian authored
Viktor Vislobokov
Bruce Momjian authoredViktor Vislobokov
FAQ_russian.html 69.16 KiB
<!DOCTYPE html PUBLIC "-//W3C//DTD html 4.01 transitional//EN">
<HTML>
<!-- DOCTYPE html PUBLIC "-//W3C//DTD HTML 3.2//EN" -->
<HEAD>
<META name="generator" content="HTML Tidy, see www.w3.org">
<META http-equiv="Content-Type" content="text/html; charset=koi8-r">
<TITLE>PostgreSQL FAQ</TITLE>
</HEAD>
<BODY bgcolor="#ffffff" text="#000000" link="#ff0000" vlink="#a00000" alink="#0000ff">
<H1> PostgreSQL</H1>
<P> : 15 20:06:34 EST 2004</P>
<P> : (Bruce Momjian) (<A href=
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
</P>
<P> : (<A href=
"mailto:corochoone@perm.ru">corochoone@perm.ru</A>)<BR>
</P>
<P>
<A href="http://www.postgresql.org/docs/faqs/FAQ.html">http://www.PostgreSQL.org/docs/faqs/FAQ.html</A>.</P>
<P>
<A href="http://www.postgresql.org/docs/index.html">http://www.PostgreSQL.org/docs/index.html</A>.</P>
<HR>
<H2 align="center"> </H2>
<A href="#1.1">1.1</A>) PostgreSQL? ?<BR>
<A href="#1.2">1.2</A>) PostgreSQL?<BR>
<A href="#1.3">1.3</A>) Unix PostgreSQL?<BR>
<A href="#1.4">1.4</A>) Unix ?<BR>
<A href="#1.5">1.5</A>) PostgreSQL?<BR>
<A href="#1.6">1.6</A>) ?<BR>
<A href="#1.7">1.7</A>) ?<BR>
<A href="#1.8">1.8</A>) ?<BR>
<A href="#1.9">1.9</A>)
?<BR>
<A href="#1.10">1.10</A>) <SMALL>SQL</SMALL>?<BR>
<A href="#1.11">1.11</A>) PostgreSQL 2000- (Y2K)?<BR>
<A href="#1.12">1.12</A>) ?<BR>
<A href="#1.13">1.13</A>) ?<BR>
<A href="#1.14">1.14</A>) PostgreSQL
<SMALL></SMALL>?<BR>
<A href="#1.15">1.15</A>) PostgreSQL?<BR>
<H2 align="center"> </H2>
<A href="#2.1">2.1</A>) <SMALL>ODBC</SMALL>
PostgreSQL?<BR>
<A href="#2.2">2.2</A>)
PostgreSQL Web?<BR>
<A href="#2.3">2.3</A>) PostgreSQL
?<BR>
<A href="#2.4">2.4</A>)
PostgreSQL?<BR>
<H2 align="center"> </H2>
<A href="#3.1">3.1</A>) PostgreSQL
<I>/usr/local/pgsql</I>?<BR>
<A href="#3.2">3.2</A>) <I>postmaster</I>,
<I>Bad System Call</I> core dumped. ?<BR>
<A href="#3.3">3.3</A>) <I>postmaster</I>,
<I>IpcMemoryCreate</I>. ?<BR>
<A href="#3.4">3.4</A>) <I>postmaster</I>,
<I>IpcSemaphoreCreate</I>. ?<BR> <A href="#3.5">3.5</A>)
?<BR>
<A href="#3.6">3.6</A>)
?<BR>
<A href="#3.7">3.7</A>) ?<BR>
<A href="#3.8">3.8</A>) <I>"Sorry, too many
clients"</I> ?<BR>
<A href="#3.9">3.9</A>) <I>pgsql_tmp</I>?<BR>
<A href="#3.10">3.10</A>) dump restore
PostgreSQL?<BR>
<A href="#3.11">3.11</A>) ""
?<BR>
<H2 align="center"> </H2>
<A href="#4.1">4.1</A>)
?<BR>
<A href="#4.2">4.2</A>) <SMALL>SELECT</SMALL>
? ?<BR>
<A href="#4.3">4.3</A>)
<I>psql</I>?<BR>
<A href="#4.4">4.4</A>)
ţ ?<BR>
<A href="#4.5">4.5</A>) ,
?<BR>
<A href="#4.6">4.6</A>)
?<BR>
<A href="#4.7">4.7</A>) ,
, , ?<BR>
<A href="#4.8">4.8</A>)
. ?<BR>
<A href="#4.9">4.9</A>) ,
?<BR>
<A href="#4.10">4.10</A>) R-tree ?<BR>
<A href="#4.11">4.11</A>) Genetic Query Optimizer?<BR>
<A href="#4.12">4.12</A>)
?
?<BR>
<A href="#4.13">4.13</A>) ,
<SMALL>NULL</SMALL> - ?<BR>
<A href="#4.14">4.14</A>)
?<BR>
<A href="#4.15.1">4.15.1</A>) serial/--?<BR>
<A href="#4.15.2">4.15.2</A>)
<SMALL>SERIAL</SMALL>?<BR>
<A href="#4.15.3">4.15.3</A>) ,
<I>currval()</I> <I>nextval()</I>
?<BR>
<A href="#4.15.4">4.15.4</A>)
?
, /SERIAL?<BR>
<A href="#4.16">4.16</A>) <SMALL>OID</SMALL>?
<SMALL>TID</SMALL>?<BR>
<A href="#4.17">4.17</A>)
PostgreSQL?<BR>
<A href="#4.18">4.18</A>) <I>"ERROR: Memory
exhausted in AllocSetAlloc()"</I>?<BR>
<A href="#4.19">4.19</A>) , PostgreSQL
?<BR>
<A href="#4.20">4.20</A>)
<I>"invalid large obj descriptor"</I>?<BR>
<A href="#4.21">4.21</A>)
?<BR>
<A href="#4.22">4.22</A>) ,
<CODE><SMALL>IN</SMALL></CODE> ?<BR>
<A href="#4.23">4.23</A>) ?<BR>
<A href="#4.24">4.24</A>) ,
?<BR>
<A href="#4.25">4.25</A>) ?<BR>
<A href="#4.26">4.26</A>) / PL/PgSQL?<BR>
<A href="#4.27">4.27</A>) ?<BR>
<H2 align="center"> PostgreSQL</H2>
<A href="#5.1">5.1</A>) .
<I>psql</I>, core dump?<BR>
<A href="#5.2">5.2</A>)
PostgreSQL?<BR>
<A href="#5.3">5.3</A>) C ,
?<BR>
<A href="#5.4">5.4</A>) .
?<BR>
<HR>
<H2 align="center"> </H2>
<H4><A name="1.1">1.1</A>) PostgreSQL? ?</H4>
<P>PostgreSQL <I>Post-Gres-Q-L (---)</I>.
http://www.postgresql.org/postgresql.mp3
.</P>
<P>PostgreSQL - POSTGRES (
"Postgres"),
<SMALL></SMALL>.
PostgreSQL
POSTGRES, PostQuel
<SMALL>SQL</SMALL>. PostgreSQL -
.</P>
<P> PostgreSQL ,
. ,
(Marc G. Fournier) (<A href=
"mailto:scrappy@PostgreSQL.org">scrappy@PostgreSQL.org</A>). (.
<A href="#1.6">1.6</A> , ).
PostgreSQL.
- .
FAQ ,
<A href="http://www.postgresql.org/docs/faqs/FAQ_DEV.html">http://www.PostgreSQL.org/docs/faqs/FAQ_DEV.html</A>
</P>
<P> PostgreSQL 1.01 (Andrew Yu)
(Jolly Chen).
, , .
Postgres, PostgreSQL,
,
,
(Michael Stonebraker) ,
.</P>
<P> , , Postgres.
1995 <SMALL>SQL</SMALL>,
Postgres95.
1996 PostgreSQL.</P>
<H4><A name="1.2">1.2</A>) PostgreSQL?</H4>
<P>PostgreSQL COPYRIGHT:</P>
<P> PostgreSQL</P>
<P>Portions copyright (c) 1996-2004, PostgreSQL Global Development
Group Portions Copyright (c) 1994-6 Regents of the University of
California</P>
<P> , ,
, - ,
,
.</P>
<P>
, ,
,
,
.</P>
<P>
, , :
.
" "
, , , .</P>
<P> BSD ,
.
.
ţ .</P>
<H4><A name="1.3">1.3</A>) Unix PostgreSQL?</H4>
<P>, PostgreSQL
Unix. ,
, PostgreSQL
.</P>
<H4><A name="1.4">1.4</A>) Unix
?</H4>
<P> 8.0, PostgreSQL
Microsoft Windows, NT,
Win2000, WinXP Win2003.
<A href="http://pgfoundry.org/projects/pginstaller">
http://pgfoundry.org/projects/pginstaller</A>. Windows,
MS-DOS (Win95, Win98, WinMe)
PostgreSQL Cygwin.</P>
<P> Novell Netware 6
<a href="http://forge.novell.com/">http://forge.novell.com</a>.</P>
<H4><A name="1.5">1.5</A>) PostgreSQL?</H4>
<P>, ftp
PostgreSQL <A href=
"ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A>.
.</P>
<H4><A name="1.6">1.6</A>) ?</H4>
<P> : <A href=
"mailto:pgsql-general@PostgreSQL.org">pgsql-general@PostgreSQL.org</A>.
, PostgreSQL.
, ,
( ) :</P>
<PRE>
subscribe
end
</PRE>
<P> <A href=
"mailto:pgsql-general-request@PostgreSQL.org">pgsql-general-request@PostgreSQL.org</A>.</P>
<P> . ,
: <A href=
"mailto:pgsql-general-digest-request@PostgreSQL.org">pgsql-general-digest-request@PostgreSQL.org</A>
:</P>
<PRE> subscribe
end
</PRE>
,
30 .
<P> .
, <A href=
"mailto:pgsql-bugs-request@PostgreSQL.org">pgsql-bugs-request@PostgreSQL.org</A>
:</P>
<PRE>
subscribe
end
</PRE>
.
,
<A href=
"mailto:pgsql-hackers-request@PostgreSQL.org">pgsql-hackers-request@PostgreSQL.org</A>
:
<PRE>
subscribe
end
</PRE>
<P> PostgreSQL
PostgreSQL :</P>
<BLOCKQUOTE>
<A href="http://www.PostgreSQL.org">http://www.PostgreSQL.org</A>
</BLOCKQUOTE>
<P> IRC <I>#postgreql</I>,
Freenode (<I>irc.freenode.net</I>).
, Unix
<code>irc -c '#postgresql' "$USER" irc.freenode.net</code>
IRC .
(<I>#postgresql-es</I>) (<I>#postgresqlfr</I>)
. PostgreSQL EFNet.</P>
<P>
<A href="http://techdocs.postgresql.org/companies.php">http://techdocs.postgresql.org/companies.php</A>.</P>
<H4><A name="1.7">1.7</A>) ?</H4>
<P> PostgreSQL - 7.4.6</P>
<P> 6-8 .</P>
<H4><A name="1.8">1.8</A>) ?</H4>
<P> ,
man .
<I>/doc</I>.
<A href="http://www.postgresql.org/docs">http://www.PostgreSQL.org/docs</A>.</P>
<P> PostgreSQL <A href=
"http://www.PostgreSQL.org/docs/awbook.html">http://www.PostgreSQL.org/docs/awbook.html</A>
<a href="http://www.commandprompt.com/ppbook/">http://www.commandprompt.com/ppbook/</a>.
PostgreSQL,
<a href="http://techdocs.postgresql.org/techdocs/bookreviews.php">http://techdocs.postgresql.org/techdocs/bookreviews.php</a>.
, <a href="http://techdocs.postgresql.org">http://techdocs.PostgreSQL.org/</a>
PostgreSQL.</p>
<P><I>psql</I> \d
, , , ..</P>
<P> .</P>
<H4><A name="1.9">1.9</A>)
?</H4>
<P>PostgreSQL <SMALL>SQL</SMALL>-92.
<A href="http://developer.postgresql.org/todo.php">TODO</A>
,
.</P>
<H4><A name="1.10">1.10</A>) <SMALL>SQL</SMALL>?</H4>
<P> PostgreSQL <A href=
"http://www.PostgreSQL.org/docs/awbook.html">http://www.PostgreSQL.org/docs/awbook.html</A>
<SMALL>SQL</SMALL>. PostgreSQL
<A href="http://www.commandprompt.com/ppbook/">
http://www.commandprompt.com/ppbook.</A>
<A href=
"http://www.intermedia.net/support/sql/sqltut.shtm">http://www.intermedia.net/support/sql/sqltut.shtm,</A>
<A href=
"http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM">
http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM,</A>
<A href=
"http://sqlcourse.com/">http://sqlcourse.com.</A></P>
<P> - "Teach Yourself SQL in 21 Days, Second Edition"
( SQL 21 , )
<A href=
"http://members.tripod.com/er4ebus/sql/index.htm">http://members.tripod.com/er4ebus/sql/index.htm</A></P>
<P>
<I>The Practical SQL Handbook</I>,
Bowman, Judith S., et al., Addison-Wesley. <I>The
Complete Reference SQL</I>, Groff et al., McGraw-Hill.</P>
<H4><A name="1.11">1.11</A>) PostgreSQL 2000- (Y2K)?</H4>
<P>, 2000 2000 .</P>
<H4><A name="1.12">1.12</A>) ?</H4>
<P> ,
PostgreSQL .
, <I>pgsql-hackers</I>
<I>pgsql-patches</I>. , (patches)
pgsql-patches.</P>
<P> ,
<SMALL>CVS</SMALL> PostgreSQL.
,
, , ,
.</P>
<H4><A name="1.13">1.13</A>) ??</H4>
<P> PostgreSQL,
<A href="http://www.postgresql.org/bugform.html">http://www.postgresql.org/bugform.html</A>.</P>
<P> <A href=
"ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A>
PostgreSQL .</P>
<H4><A name="1.14">1.14</A>) PostgreSQL
<SMALL></SMALL>?</H4>
<P>
:
, , , .</P>
<DL>
<DT><B></B></DT>
<DD>PostgreSQL
<SMALL></SMALL>, : ,
, , ,
. ,
: , ,
,
.<BR>
<BR>
</DD>
<DT><B></B></DT>
<DD>PostgreSQL
, -
, - . MySQL
, , ,
/ . MySQL
SELECT , .
, MySQL -
, <I></I>.
,
.
, PostgreSQL MySQL
<A href="http://openacs.org/philosophy/why-not-mysql.html">
http://openacs.org/philosophy/why-not-mysql.html</A>. , MySQL -
,
,
, PostgreSQL.<BR>
<BR>
</DD>
<DT><B></B></DT>
<DD> , <SMALL></SMALL>
. ,
, .
-
, ,
. , ,
.<BR>
<BR>
</DD>
<DT><B></B></DT>
<DD>
,
. , -
, <SMALL></SMALL>
. ,
,
PostgreSQL <SMALL></SMALL>.
,
. ( <A href="#1.6"> 1.6</A>.)<BR>
<BR>
</DD>
<DT><B></B></DT>
<DD> ,
.
, ,
BSD, .<BR>
<BR>
</DD>
</DL>
<H4><A name="1.15">1.15</A>)
PostgreSQL?</H4>
<P>PostgreSQL
1996 .
(Marc Fournier),
.</P>
<P>
. ,
.</P>
<P>, .
,
. ,
,
<A href="https://store.pgsql.com/shopping/">
https://store.pgsql.com/shopping/</A>
.</P>
<P> PostgreSQL, Inc,
"contributions"
PostgreSQL - .
, , .</P>
<HR>
<p> PostgreSQL, ,
<a href="http://advocacy.postgresql.org/">
http://advocacy.postgresql.org</a>.</p>
<H2 align="center"> </H2>
<H4><A name="2.1">2.1</A>) <SMALL>ODBC</SMALL>
PostgreSQL?</H4>
<P> <SMALL>ODBC</SMALL> , PsqlODBC
OpenLink <SMALL>ODBC</SMALL>.</P>
<P> PsqlODBC <A href="http://gborg.postgresql.org/project/psqlodbc/projdisplay.php">
http://gborg.postgresql.org/project/psqlodbc/projdisplay.php</A>.</P>
<P>OpenLink <SMALL>ODBC</SMALL> <A href=
"http://www.openlinksw.com/">http://www.openlinksw.com</A>.
,
<SMALL>ODBC</SMALL>, , <SMALL>ODBC</SMALL>
PostgreSQL
(Win, Mac, Unix, VMS).</P>
<P>
, . ,
<A href=
"mailto:postgres95@openlink.co.uk">postgres95@openlink.co.uk</A>.</P>
<H4><A name="2.2">2.2</A>)
PostgreSQL Web?</H4>
<P> Web :
<A href="http://www.webreview.com">http://www.webreview.com</A></P>
<P> Web, PHP.
<A href="http://www.php.net">http://www.php.net</A>.</P>
<P> , Perl CGI.pm mod_perl.</P>
<H4><A name="2.3">2.3</A>) PostgreSQL
?</H4>
<P>, PostgreSQL.
PgAccess (<A href="http://www.pgaccess.org/">http://www.pgaccess.org</A>),
PgAdmin III (<A href="http://www.pgadmin.org/">http://www.pgadmin.org</A>), RHDB Admin (<A href="http://sources.redhat.com/rhdb/">http://sources.redhat.com/rhdb/</A>),
TORA (<A href="http://www.globecom.net/tora/">http://www.globecom.net/tora/
( )</A>
Rekall (<A href="http://www.thekompany.com/products/rekall/">
http://www.thekompany.com/products/rekall/</A>, ).
PhpPgAdmin (<A href="http://phppgadmin.sourceforge.net/">
http://phppgadmin.sourceforge.net/</A>) - PostgreSQL,
Web.</P>
<H4><A name="2.4">2.4</A>) PostgreSQL?</H4>
<P>- PostgreSQL
.
, .</P>
<P> PostgreSQL:</P>
<UL>
<LI>C (libpq)</LI>
<LI>Embedded C (ecpg)</LI>
<LI>Java (jdbc)</LI>
<LI>Python (PyGreSQL)</LI>
<LI>TCL (libpgtcl)</LI>
</UL>
<P>
<A href="http://gborg.postgresql.org/">http://gborg.PostgreSQL.org</a>
<i>Drivers/Interfaces</i>.
</P>
<HR>
<H2 align="center"> </H2>
<H4><A name="3.1">3.1</A>) PostgreSQL
<I>/usr/local/pgsql</I>?</H4>
<P> <I>--prefix</I> <I>configure</I>.</P>
<H4><A name="3.2">3.2</A>) <I>postmaster</I>,
<I>Bad System Call</I> core dumped. ?</H4>
<P> , ,
- ,
System V. PostgreSQL ,
.</P>
<H4><A name="3.3">3.3</A>) <I>postmaster</I>,
<I>IpcMemoryCreate</I>. ?</H4>
<P> ,
.
,
backend
<I>postmaster</I>. ,
, -
1 .
<A href=
"http://www.postgresql.org/docs/current/static/kernel-resources.html">
PostgreSQL.</A>.</P>
<H4><A name="3.4">3.4</A>) <I>postmaster</I>,
<I>IpcSemaphoreCreate</I>. ?</H4>
<P> <I>IpcSemaphoreCreate: semget failed (No
space left on device)</I> ,
. Postgres
backend . <I>postmaster</I> backend .
<I>-N</I> 32, .
- <SMALL>SEMMNS</SMALL>
<SMALL>SEMMNI</SMALL> .</P>
<P>
.</P>
<P> - ,
, .
PostgreSQL.</P>
<H4><A name="3.5">3.5</A>)
?</H4>
<P> , PostgreSQL
Unix TCP/IP .
listen_addresses
postgresql.conf <B></B> host-
<I>$PGDATA/pg_hba.conf</I>.</P>
<H4><A name="3.6">3.6</A>)
?</H4>
<P>, .
<SMALL>EXPLAIN ANALYZE</SMALL> PostgreSQL
.</P>
<P> <SMALL>INSERT</SMALL>,
,
<SMALL>COPY</SMALL>. ,
<SMALL>INSERT</SMALL>. -,
<SMALL>BEGIN WORK/COMMIT</SMALL> .
.
. ,
,
.</P>
<P>
<A href="http://www.postgresql.org/docs/current/static/runtime.html">
PostgreSQL</A>.
<I>fsync()</I> <I>postmaster</I> <I>-o -F</I>.
<I>fsync()</I>,
.</P>
<P> <I>shared_buffers</I> <I>-B</I>
,
backend .
, <I>postmaster</I>
.
8 1000 .</P>
<P> backend <I>sort_mem</I>
<I>work_mem</I> ,
backend .
1024 (.. 1MB).</P>
<P> <SMALL>CLUSTER</SMALL>
.
<SMALL>CLUSTER</SMALL>.</P>
<H4><A name="3.7">3.7</A>)
?</H4>
<P>PostgreSQL ,
,
.</P>
<P>-, <I>configure</I> --enable-cassert, <I>assert()</I> backend
-
.</P>
<P> <I>postmaster</I>, <I>postgres</I>
. -, <I>postmaster</I>, ,
:</P>
<PRE>
cd /usr/local/pgsql
./bin/postmaster >server.log 2>&1 &
</PRE>
<P> server.log
PostgreSQL.
, . <I>Postmaster</I> <I>-d</I>,
. <I>-d</I> ,
. ,
.</P>
<P> <I>postmaster</I> ,
<I>postgres</I> backend
<SMALL>SQL</SMALL> . <B></B>
. , ,
, .
, ,
, . backend
<I>postmaster</I>,
/backend .</P>
<P> <I>postmaster</I> , <I>psql</I>
, <SMALL>PID</SMALL> <I>postgres</I>,
<I>psql</I>, </P>
<PRE>
SELECT pg_backend_pid()
</PRE>
<P> <I>postgres</I> <SMALL>PID</SMALL>.
<I>psql</I>.
<I>postgres</I>,
PGOPTIONS="-W n", <I>psql</I>.
<I>n</I> ,
,
.</P>
<P> <I>postgres</I> <I>-s, -A</I>, <I>-t</I>
.</P>
<P> PostgreSQL
, .
backend'
<I>pgsql/data/base/dbname</I>.
. Linux
<I>-DLINUX_PROFILE</I>.</P>
<H4><A name="3.8">3.8</A>) <I>"Sorry, too
many clients"</I> ?</H4>
<P>
backend <I>postmaster</I>.</P>
<P> 32 .
<I>postmaster</I>
, <I>-N</I>
<I>postgresql.conf</I>.</P>
<P>, <I>-N</I> 32, <I>-B</I>
64; <I>-B</I>
<I>-N</I>,
ݣ .
backend ,
Unix. ,
, <SMALL>SHMMAX;</SMALL>
, <SMALL>SEMMNS</SMALL> <SMALL>SEMMNI;</SMALL>
, <SMALL>NPROC;</SMALL>
, <SMALL>MAXUPRC;</SMALL>
, <SMALL>NFILE</SMALL> <SMALL>NINODE</SMALL>.
backend
, .</P>
<H4><A name="3.9">3.9</A>) <I>pgsql_tmp</I>?</H4>
<P> ,
. , <small>ORDER BY</small>
, <i>-S</i>
backend',
.</P>
<P> ,
, backend .
.</P>
<H4><A name="3.10">3.10</A>) dump restore
PostgreSQL?</H4>
<P> PostgreSQL
. 7.2 7.2.1
dump restore.
(.. , 7.2 7.3)
.
,
. dump
,
.</P>
<P> , ,
<i>pg_upgrade</i>
dump/restore.
<i>pg_upgrade</i> .</P>
<H4><A name="3.11">3.11</A>) ""
?</H4>
<P> ""
, , ""
. . ECC, SCSI
ģ ,
"". PostgreSQL "",
ģ ,
"". ""
.</P>
<HR>
<H2 align="center"> </H2>
<H4><A name="4.1">4.1</A>)
?</H4>
<P>
<SMALL>DECLARE</SMALL>.</P>
<H4><A name="4.2">4.2</A>) <SMALL>SELECT</SMALL>
? ?</H4>
<P> <SMALL>FETCH</SMALL>
<SMALL>SELECT</SMALL> ... <SMALL>LIMIT</SMALL>....</P>
<P> ,
. ,
<SMALL>ORDER BY.</SMALL> - ,
<SMALL>ORDER BY</SMALL>, PostgreSQL
.</P>
<p>To <small>SELECT</small> a random row, use:
</p><pre> SELECT col
FROM tab
ORDER BY random()
LIMIT 1;
</pre>
</p>
<H4><A name="4.3">4.3</A>)
<I>psql</I>?</H4>
<P> <I>psql</I>, \dt.
<I>psql</I> , \?.
, <I>psql</I>
<I>pgsql/src/bin/psql/describe.c</I>.
<SMALL>SQL</SMALL> <I>psql</I> ,
.
<I>psql</I> <I>-E</I> ,
,
. PostgreSQL <SMALL>SQLi</SMALL>
INFORMATION SCHEMA , ,
.</P>
<H4><A name="4.4">4.4</A>)
ţ ?</H4>
<P><small>DROP COLUMN</small>
7.3 <small>ALTER TABLE DROP COLUMN</small>. ,
:</P>
<PRE>
BEGIN;
LOCK TABLE old_table;
SELECT ... -- ,
INTO TABLE new_table
FROM old_table;
DROP TABLE old_table;
ALTER TABLE new_table RENAME TO old_table;
COMMIT;
</PRE>
<P> , :</P>
<PRE>
BEGIN;
ALTER TABLE tab ADD COLUMN new_col <i>new_data_type</i>;
UPDATE tab SET new_col = CAST(old_col AS <i>new_data_type</i>);
ALTER TABLE tab DROP COLUMN old_col;
COMMIT;
</PRE>
<H4><A name="4.5">4.5</A>) ,
?</H4>
<P> :</P>
<PRE>
? ( 32 TB)
? 32 TB
? 1.6 TB
? 1 GB
?
? 250-1600
?
</PRE>
, ""
/.
,
.
<P> 32 TB
.
1 GB, ,
.</P>
<P>
,
32k.</P>
<H4><A name="4.6">4.6</A>)
?</H4>
<P> PostgreSQL 5
.</P>
<P> , 100,000 ,
. ,
, 20 . 2.8 MB.
PostgreSQL,
6.4 MB :</P>
<PRE>
32 : ()
+ 24 :
+ 4 :
----------------------------------------
60
PostgreSQL 8192 (8 KB), :
8192
--------------------- = 136 (̣)
60
100000
----------------------- = 735 (̣)
128
735 * 8192 = 6,021,120 (6 MB)
</PRE>
<P> ,
, .</P>
<P> <small>NULL</small>
.
</P>
<H4><A name="4.7">4.7</A>) ,
, , ?</H4>
<P><I>psql</I> ,
, .
\? , .
, <i>pg_</i>
. ݣ, <i>psql -l</i> .</P>
<P> <I>pgsql/src/tutorial/syscat.source</I>.
<SMALL>SELECT</SMALL>
.</P>
<H4><A name="4.8">4.8</A>)
. ?</H4>
<P> .
. ,
,
.</P>
<P> -
, PostgreSQL .
<SMALL>VACUUM ANALYZE</SMALL>
<SMALL>ANALYZE</SMALL>. ,
, .
.
.</P>
<P> <SMALL>ORDER BY</SMALL>
.
, .
, <SMALL>ORDER BY</SMALL> <SMALL>LIMIT</SMALL>
,
. MAX() MIN()
,
<SMALL>ORDER BY</SMALL> <SMALL>LIMIT</SMALL>:
<pre>
SELECT col
FROM tab
ORDER BY col [ DESC ]
LIMIT 1;
</pre>
<P> ,
, <CODE>SET enable_seqscan TO 'off'</CODE>
, , - .
</P>
<P> , <SMALL>LIKE</SMALL>
<I>~</I>, :</P>
<UL>
<LI> , ..:
<UL>
<LI><small>LIKE</small> <i>%.</i>.</LI>
<LI><i>~</i> <i>^</i>.</LI>
</UL></LI>
<LI> , .. [a-e].</LI>
<LI> , <small>ILIKE</small>
<i>~*</i> . ,
, <A href="#4.12">4.12</A>.</LI>
<LI> <i>initdb</i>
<i>C</i>, next-greater
-C .
<PRE>
text_pattern_ops
</PRE>
<PRE>
LIKE
</PRE>
.</LI>
</UL>
</P>
<P> 8.0, ,
.
int2, int8 numeric .</P>
<H4><A name="4.9">4.9</A>) ,
?</H4>
<P> <SMALL>EXPLAIN</SMALL>.</P>
<H4><A name="4.10">4.10</A>) R-tree ?</H4>
<P>R-tree .
. B-tree
. R-tree
. , R-tree
<I>point</I>,
"
."</P>
<P> , R-tree
:</P>
<P>Guttman, A. "R-trees: A Dynamic Index Structure for Spatial
Searching." Proceedings of the 1984 ACM SIGMOD Int'l Conf on Mgmt
of Data, 45-57.</P>
<P> "Readings in
Database Systems".</P>
<P> R-tree . ,
R-tree
. , R-tree
, , - ,
.</P>
<H4><A name="4.11">4.11</A>) Genetic Query
Optimizer?</H4>
<P> <SMALL>GEQO</SMALL> ,
Genetic Algorithm (GA).
.</P>
<H4><A name="4.12">4.12</A>)
?
?</H4>
<P> <I>~</I> ,
<I>~*</I>
. <SMALL>LIKE</SMALL>
<SMALL>ILIKE</SMALL>.</P>
<P> :</P>
<PRE>
SELECT *
FROM tab
WHERE lower(col) = 'abc';
</PRE>
. ,
, :
<PRE>
CREATE INDEX tabindex ON tab (lower(col));
</PRE>
<H4><A name="4.13">4.13</A>) ,
<SMALL>NULL</SMALL> - ?</H4>
<P> <SMALL>IS NULL</SMALL>
<SMALL>IS NOT NULL</SMALL>.</P>
<H4><A name="4.14">4.14</A>)
?</H4>
<PRE>
--------------------------------------------------
VARCHAR(n) varchar , CHAR(n) bpchar
TEXT text
BYTEA bytea ( null- )
"char" char
</PRE>
<P> ,
.</P>
<P> "varlena" (..,
, ).
, ,
. ,
<SMALL>TOAST</SMALL>,
,
.</P>
<SMALL>VARCHAR(n)</SMALL> - ,
, .
<SMALL>TEXT</SMALL> - ,
1 .
<P><SMALL>CHAR(n)</SMALL> - ,
. <SMALL>CHAR(n)</SMALL>
, <SMALL>VARCHAR(n)</SMALL>
, .
<SMALL>BYTEA</SMALL> ,
<SMALL>NULL</SMALL> .
, .</P>
<H4><A name="4.15.1">4.15.1</A>)
serial/--?</H4>
<P>PostgreSQL <SMALL>SERIAL</SMALL>.
. :</P>
<PRE>
CREATE TABLE person (
id SERIAL,
name TEXT
);
</PRE>
:
<PRE>
CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT
);
</PRE>
<I>create_sequence</I>.
<I>OID</I> . ,
,
<I>pg_dump</I> <I>-o</I>
<SMALL>COPY WITH OIDS</SMALL>
<SMALL>OID</SMALL>.
<H4><A name="4.15.2">4.15.2</A>)
<SMALL>SERIAL</SMALL>?</H4>
<P>
<SMALL>SERIAL</SMALL> sequence
<I>nextval()</I> <I></I>
. - <A href="#4.15.1">4.15.1</A>,
:</P>
<PRE>
new_id = execute("SELECT nextval('person_id_seq')");
execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
</PRE>
<CODE>new_id</CODE> (
<CODE>person</CODE>). ,
<SMALL>SEQUENCE</SMALL>
<<I>table</I>>_<<I>serialcolumn</I>>_<I>seq</I>,
<I>table</I> <I>serialcolumn</I>
<SMALL>SERIAL</SMALL>.
<P> ,
<SMALL>SERIAL</SMALL> <I>currval()</I>
<I></I> , </P>
<PRE>
execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
new_id = execute("SELECT currval('person_id_seq')");
</PRE>
,
<A href="#4.16"><SMALL>OID</SMALL></A>,
<SMALL>INSERT</SMALL> ,
, oid ,
4 . Perl, DBI Edmund Mergl' DBD::Pg,
oid <I>$sth->{pg_oid_status}</I>
<I>$sth->execute()</I>.
<H4><A name="4.15.3">4.15.3</A>) ,
<I>currval()</I> <I>nextval()</I>
?</H4>
<P>. <i>currval()</i> ,
backend', .</P>
<H4><A name="4.15.4">4.15.4</A>)
?
, /SERIAL?</H4>
<P> , ,
.
.</P>
<H4><A name="4.16">4.16</A>) <SMALL>OID</SMALL>?
<SMALL>TID</SMALL>?</H4>
<P> <SMALL>OID</SMALL>
PostgreSQL. , PostgreSQL
<SMALL>OID</SMALL>. <SMALL>OID</SMALL>
<I>initdb</I> 16384 (
<I>include/access/transam.h</I>).
<SMALL>OID</SMALL> . ,
<SMALL>OID</SMALL> -
, PostgreSQL.</P>
<P>PostgreSQL <SMALL>OID</SMALL>
. <SMALL>OID</SMALL>
,
.
<SMALL>OID</SMALL> <SMALL>OID</SMALL>
<SMALL>OID</SMALL>
.</P>
<P> <SMALL>OID</SMALL>
, .
<SMALL>OID</SMALL> -
<SMALL>OID</SMALL>, :</P>
<PRE>
CREATE TABLE new_table(mycol int); SELECT oid AS old_oid, mycol INTO tmp_table FROM old_table;
COPY tmp_table TO '/tmp/pgtable';
COPY new_table WITH OIDS FROM '/tmp/pgtable';
DROP TABLE tmp_table;
</PRE>
<P>O<SMALL>ID</SMALL> 4-
4 . , ,
,
.</P>
<P>T<SMALL>ID</SMALL>
offset . T<SMALL>ID</SMALL>
.
<P>T<SMALL>ID</SMALL>
.</P>
<H4><A name="4.17">4.17</A>)
PostgreSQL?</H4>
<P>
. :</P>
<UL>
<LI>table, relation, class</LI>
<LI>row, record, tuple</LI>
<LI>column, field, attribute</LI>
<LI>retrieve, select</LI>
<LI>replace, update</LI>
<LI>append, insert</LI>
<LI><SMALL>OID</SMALL>, serial value</LI>
<LI>portal, cursor</LI>
<LI>range variable, table name, table alias</LI>
</UL>
<P> <A href="http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html">http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html</A></P>
<H4><A name="4.18">4.18</A>) <I>"ERROR: Memory
exhausted in AllocSetAlloc()"</I>?</H4>
<P>
.
<I>postmaster</I>
:</P>
<PRE>
ulimit -d 262144
limit datasize 256m
</PRE>
shell,
,
.
,
ţ . <SMALL>SQL</SMALL>
, backend ,
.
<H4><A name="4.19">4.19</A>) , PostgreSQL
?</H4>
<P> <I>psql</I>, <CODE>SELECT version();</CODE></P>
<H4><A name="4.20">4.20</A>) <I>"invalid large obj descriptor"</I>?</H4>
<P>
<CODE>BEGIN WORK</CODE> <CODE>COMMIT</CODE>,
<CODE>lo_open</CODE> ... <CODE>lo_close.</CODE></P>
<P> PostgreSQL ,
. ,
- ,
<I>invalid large obj descriptor</I>,
(
)
.</P>
<P> <SMALL>ODBC</SMALL>,
<CODE>auto-commit off.</CODE></P>
<H4><A name="4.21">4.21</A>)
?</H4>
<P> <I>CURRENT_TIMESTAMP</I>:</P>
<PRE>
<CODE>CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
</CODE>
</PRE>
<H4><A name="4.22">4.22</A>) ,
<CODE><SMALL>IN</SMALL></CODE> ?</H4>
<P> 7.4,
.
, ,
<CODE><SMALL>IN</SMALL></CODE> .
, <CODE>IN</CODE>
<CODE>EXISTS</CODE>:</P>
<PRE>
SELECT *
FROM tab
WHERE col IN (SELECT subcol FROM subtab);
</PRE>
:
<PRE>
SELECT *
FROM tab
WHERE EXISTS (SELECT subcol FROM subtab WHERE subcol = col);
</PRE>
, <CODE>subcol</CODE>
.
<P> 7.4 , <CODE>IN</CODE>
,
<CODE>EXISTS</CODE></P>.
<H4><A name="4.23">4.23</A>) ?</H4>
<P>PostgreSQL ,
SQL. :</P>
<PRE>
SELECT *
FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
</PRE>
<PRE>
SELECT *
FROM t1 LEFT OUTER JOIN t2 USING (col);
</PRE>
<P> t1.col t2.col,
t1 ( t2).
<SMALL>RIGHT</SMALL>
t2. <SMALL>FULL</SMALL>
t1 t2. <SMALL>OUTER</SMALL>
<SMALL>LEFT</SMALL>,
<SMALL>RIGHT</SMALL> <SMALL>FULL</SMALL> .
<SMALL>INNER</SMALL> .</P>
<P> ,
<SMALL>UNION</SMALL> <SMALL>NOT IN</SMALL>. ,
<I>tab1</I> <I>tab2</I>,
<I></I> :<BR>
<BR>
</P>
<PRE>
SELECT tab1.col1, tab2.col2
FROM tab1, tab2
WHERE tab1.col1 = tab2.col1
UNION ALL
SELECT tab1.col1, NULL
FROM tab1
WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
ORDER BY col1
</PRE>
<H4><A name="4.24">4.24</A>) ,
?</H4>
<P> .
PostgreSQL
, , .</P>
<P><I>contrib/dblink</I> ,
. ,
.</P>
<H4><A name="4.25">4.25</A>) ?</H4>
<P> 7.3,
- ,
<href="http://techdocs.postgresql.org/guides/SetReturningFunctions">
http://techdocs.postgresql.org/guides/SetReturningFunctions</a>.</P>
<H4><A name="4.26">4.26</A>) /
PL/PgSQL?</H4>
<P>PL/PgSQL
, PL/PgSQL
,
, ,
.
, <SMALL>EXECUTE</SMALL>
PL/PgSQL.
.</P>
<h4><a name="4.27">4.27</a>) ?
</h4>
<ul>
<li><i>contrib/pgcrypto</i>
<small>SQL</small> .</li>
<li> ,
<i>postgresql.conf</i>, <i>ssl</i>
<i>true</i>, <i>pg_hba.conf</i>
<i>host</i> <i>hostssl</i> <i>sslmode</i>
ݣ <i>disable</i>. (,
,
stunnel ssh, SSL PostgreSQL).</li>
<li> , 7.3. ,
<i>PASSWORD_ENCRYPTION</i> <i>postgresql.conf</i>.</li>
<li> , .</li>
</ul>
<HR>
<H2 align="center"> PostgreSQL</H2>
<H4><A name="5.1">5.1</A>) .
<I>psql</I>, core dump?</H4>
<P> .
.</P>
<H4><A name="5.2">5.2</A>)
PostgreSQL?</H4>
<P> <I>pgsql-hackers</I>
<I>contrib/</I>.</P>
<H4><A name="5.3">5.3</A>) C ,
?</H4>
<P> PostgreSQL, 7.3, ,
C, PL/PgSQL SQL.
. ,
C, <i>contrib/tablefunc</i>.</P>
<H4><A name="5.4">5.4</A>) .
?</H4>
<P> <I>Makefile</I> include
. <I>make clean</I> <I>make</I>.
<SMALL>GCC</SMALL>
<I>--enable-depend</I> <I>configure</I>
.</P>
</BODY>
</HTML>