Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2ba6cbc

Browse files
committed
Mega-clarifications from Joachim Wieland.
1 parent99b735c commit2ba6cbc

File tree

2 files changed

+90
-69
lines changed

2 files changed

+90
-69
lines changed

‎doc/FAQ‎

Lines changed: 45 additions & 32 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11

22
Frequently Asked Questions (FAQ) for PostgreSQL
33

4-
Last updated: Sat Nov 2700:14:59 EST 2004
4+
Last updated: Sat Nov 2723:55:37 EST 2004
55

66
Current maintainer: Bruce Momjian (pgman@candle.pha.pa.us)
77

@@ -79,8 +79,7 @@
7979
4.14) What is the difference between the various character types?
8080
4.15.1) How do I create a serial/auto-incrementing field?
8181
4.15.2) How do I get the value of a SERIAL insert?
82-
4.15.3) Don't currval() and nextval() lead to a race condition with
83-
other users?
82+
4.15.3) Doesn't currval() lead to a race condition with other users?
8483
4.15.4) Why aren't my sequence numbers reused on transaction abort?
8584
Why are there gaps in the numbering of my sequence/SERIAL column?
8685
4.16) What is an OID? What is a TID?
@@ -305,9 +304,8 @@
305304

306305
1.13) How do I submit a bug report?
307306

308-
Please visit the PostgreSQL BugTool page at
309-
http://www.PostgreSQL.org/bugs/bugs.php, which gives guidelines and
310-
directions on how to submit a bug report.
307+
Visit the PostgreSQL bug form at
308+
http://www.postgresql.org/bugform.html.
311309

312310
Also check out our ftp site ftp://ftp.PostgreSQL.org/pub to see if
313311
there is a more recent PostgreSQL version or patches.
@@ -420,10 +418,11 @@
420418

421419
Yes, there are several graphical interfaces to PostgreSQL available.
422420
These include PgAccess http://www.pgaccess.org), PgAdmin III
423-
(http://www.pgadmin.org, RHDB Admin (http://sources.redhat.com/rhdb/ )
424-
and Rekall ( http://www.thekompany.com/products/rekall/, proprietary).
425-
There is also PhpPgAdmin ( http://phppgadmin.sourceforge.net/ ), a
426-
web-based interface to PostgreSQL.
421+
(http://www.pgadmin.org, RHDB Admin (http://sources.redhat.com/rhdb/
422+
), TORA (http://www.globecom.net/tora/ (partly commercial), and Rekall
423+
( http://www.thekompany.com/products/rekall/, proprietary). There is
424+
also PhpPgAdmin ( http://phppgadmin.sourceforge.net/ ), a web-based
425+
interface to PostgreSQL.
427426

428427
See http://techdocs.postgresql.org/guides/GUITools for a more detailed
429428
list.
@@ -464,7 +463,8 @@
464463
kernel. The exact amount you need depends on your architecture and how
465464
many buffers and backend processes you configure for postmaster. For
466465
most systems, with default numbers of buffers and processes, you need
467-
a minimum of ~1 MB. See the PostgreSQL Administrator's Guide for more
466+
a minimum of ~1 MB. See the PostgreSQL Administrator's Guide/Server
467+
Run-time Environment/Managing Kernel Resources section for more
468468
detailed information about shared memory and semaphores.
469469

470470
3.4) When I try to start postmaster, I get IpcSemaphoreCreate errors. Why?
@@ -507,19 +507,20 @@
507507
overhead. Also, consider dropping and recreating indexes when making
508508
large data changes.
509509

510-
There are several tuning options. You can disable fsync() by starting
511-
postmaster with a -o -F option. This will prevent fsync()s from
512-
flushing to disk after every transaction.
510+
There are several tuning options in the Administration Guide/Server
511+
Run-time Environment/Run-time Configuration. You can disable fsync()
512+
by using fsync option. This will prevent fsync()s from flushing to
513+
disk after every transaction.
513514

514-
You canalsouse thepostmaster -Boption to increase the number of
515-
sharedmemory buffers used by the backend processes. If you make this
515+
You can use theshared_buffersoption to increase the number of shared
516+
memory buffers used by the backend processes. If you make this
516517
parameter too high, the postmaster may not start because you have
517518
exceeded your kernel's limit on shared memory space. Each buffer is 8K
518-
and the default is64 buffers.
519+
and the default is1000 buffers.
519520

520-
You can also use thebackend -S optionto increase the maximum amount
521-
of memory used by the backendprocess fortemporary sorts. The -S
522-
value is measured in kilobytes, and thedefault is512 (i.e.512K).
521+
You can also use thesort_mem and work_mem optionsto increase the
522+
maximum amountof memory used by the backendprocesses foreach
523+
temporary sort. Thedefault is1024 (i.e.1MB).
523524

524525
You can also use the CLUSTER command to group data in tables to match
525526
an index. See the CLUSTER manual page for more details.
@@ -556,15 +557,22 @@
556557
may not be duplicated.
557558

558559
If postmaster is running, start psql in one window, then find the PID
559-
of the postgres process used by psql. Use a debugger to attach to the
560-
postgres PID. You can set breakpoints in the debugger and issue
561-
queries from psql. If you are debugging postgres startup, you can set
562-
PGOPTIONS="-W n", then start psql. This will cause startup to delay
563-
for n seconds so you can attach to the process with the debugger, set
564-
any breakpoints, and continue through the startup sequence.
565-
566-
The postgres program has -s, -A, and -t options that can be very
567-
useful for debugging and performance measurements.
560+
of the postgres process used by psql using
561+
SELECT pg_backend_pid()
562+
563+
. Use a debugger to attach to the postgres PID. You can set
564+
breakpoints in the debugger and issue queries from psql. If you are
565+
debugging postgres startup, you can set PGOPTIONS="-W n", then start
566+
psql. This will cause startup to delay for n seconds so you can attach
567+
to the process with the debugger, set any breakpoints, and continue
568+
through the startup sequence.
569+
570+
There are several
571+
log_*
572+
573+
server configuration variables that enable printing of process
574+
statistics which can be very useful for debugging and performance
575+
measurements.
568576

569577
You can also compile with profiling to see what functions are taking
570578
execution time. The backend profile files will be deposited in the
@@ -795,7 +803,13 @@
795803
* Case-insensitive searches such as ILIKE and ~* do not utilise
796804
indexes. Instead, use functional indexes, which are described in
797805
section 4.12.
798-
* The default C locale must be used during initdb.
806+
* The default C locale must be used during initdb because it is not
807+
possible to know the next-greater character in a non-C locale. You
808+
can create a special
809+
text_pattern_ops
810+
index for such cases that work only for
811+
LIKE
812+
indexing.
799813

800814
In pre-8.0 releases, indexes often can not be used unless the data
801815
types exactly match the index's column types. This is particularly
@@ -931,8 +945,7 @@ BYTEA bytea variable-length byte array (null-byte safe)
931945
oid value is made available via $sth->{pg_oid_status} after
932946
$sth->execute().
933947

934-
4.15.3) Don't currval() and nextval() lead to a race condition with other
935-
users?
948+
4.15.3) Doesn't currval() lead to a race condition with other users?
936949

937950
No. currval() returns the current value assigned by your backend, not
938951
by all users.

‎doc/src/FAQ/FAQ.html‎

Lines changed: 45 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
alink="#0000ff">
1111
<H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
1212

13-
<P>Last updated: Sat Nov 2700:14:59 EST 2004</P>
13+
<P>Last updated: Sat Nov 2723:55:37 EST 2004</P>
1414

1515
<P>Current maintainer: Bruce Momjian (<Ahref=
1616
"mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)<BR>
@@ -111,8 +111,8 @@ <H2 align="center">Operational Questions</H2>
111111
serial/auto-incrementing field?<BR>
112112
<Ahref="#4.15.2">4.15.2</A>) How do I get the value of a
113113
<SMALL>SERIAL</SMALL> insert?<BR>
114-
<Ahref="#4.15.3">4.15.3</A>)Don't<I>currval()</I> and
115-
<I>nextval()</I>lead to a race condition with other users?<BR>
114+
<Ahref="#4.15.3">4.15.3</A>)Doesn't<I>currval()</I>
115+
lead to a race condition with other users?<BR>
116116
<Ahref="#4.15.4">4.15.4</A>) Why aren't my sequence numbers
117117
reused on transaction abort? Why are there gaps in the numbering of
118118
my sequence/SERIAL column?<BR>
@@ -395,10 +395,9 @@ <H4><A name="1.12">1.12</A>) How do I join the development
395395

396396
<H4><Aname="1.13">1.13</A>) How do I submit a bug report?</H4>
397397

398-
<P>Please visit the PostgreSQL BugTool page at<Ahref=
399-
"http://www.PostgreSQL.org/bugs/bugs.php">http://www.PostgreSQL.org/bugs/bugs.php</A>,
400-
which gives guidelines and directions on how to submit a
401-
bug report.</P>
398+
<P>Visit the PostgreSQL bug form at<Ahref=
399+
"http://www.postgresql.org/bugform.html">
400+
http://www.postgresql.org/bugform.html</A>.</P>
402401

403402
<P>Also check out our ftp site<Ahref=
404403
"ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A> to
@@ -546,7 +545,9 @@ <H4><A name="2.3">2.3</A>) Does PostgreSQL have a graphical user
546545
http://www.pgaccess.org</a>), PgAdmin III (<a
547546
href="http://www.pgadmin.org">http://www.pgadmin.org</a>, RHDB Admin (<a
548547
href="http://sources.redhat.com/rhdb/">http://sources.redhat.com/rhdb/
549-
</a>) and Rekall (<ahref="http://www.thekompany.com/products/rekall/">
548+
</a>), TORA (<ahref="http://www.globecom.net/tora/">http://www.globecom.net/tora/
549+
(partly commercial)</a>, and Rekall
550+
(<ahref="http://www.thekompany.com/products/rekall/">
550551
http://www.thekompany.com/products/rekall/</a>, proprietary). There is
551552
also PhpPgAdmin (<ahref="http://phppgadmin.sourceforge.net/">
552553
http://phppgadmin.sourceforge.net/</a>), a web-based interface to
@@ -606,10 +607,9 @@ <H4><A name="3.3">3.3</A>) When I try to start <I>postmaster</I>, I
606607
how many buffers and backend processes you configure for
607608
<I>postmaster</I>. For most systems, with default numbers of
608609
buffers and processes, you need a minimum of ~1 MB. See the<A
609-
href=
610-
"http://www.PostgreSQL.org/docs/view.php?version=current&amp;idoc=1&amp;file=kernel-resources.html">PostgreSQL
611-
Administrator's Guide</A> for more detailed information about
612-
shared memory and semaphores.</P>
610+
href="http://www.postgresql.org/docs/current/static/kernel-resources.html">PostgreSQL
611+
Administrator's Guide/Server Run-time Environment/Managing Kernel Resources</A>
612+
section for more detailed information about shared memory and semaphores.</P>
613613

614614
<H4><Aname="3.4">3.4</A>) When I try to start<I>postmaster</I>, I
615615
get<I>IpcSemaphoreCreate</I> errors. Why?</H4>
@@ -645,8 +645,9 @@ <H4><A name="3.6">3.6</A>) How do I tune the database engine for
645645
better performance?</H4>
646646

647647
<P>Certainly, indexes can speed up queries. The
648-
<SMALL>EXPLAIN ANALYZE</SMALL> command allows you to see how PostgreSQL is
649-
interpreting your query, and which indexes are being used.</P>
648+
<SMALL>EXPLAIN ANALYZE</SMALL> command allows you to see how
649+
PostgreSQL is interpreting your query, and which indexes are
650+
being used.</P>
650651

651652
<P>If you are doing many<SMALL>INSERTs</SMALL>, consider doing
652653
them in a large batch using the<SMALL>COPY</SMALL> command. This
@@ -657,22 +658,23 @@ <H4><A name="3.6">3.6</A>) How do I tune the database engine for
657658
reduces the transaction overhead. Also, consider dropping and
658659
recreating indexes when making large data changes.</P>
659660

660-
<P>There are several tuning options. You can disable<I>fsync()</I>
661-
by starting<I>postmaster</I> with a<I>-o -F</I> option. This will
661+
<P>There are several tuning options in the<ahref=
662+
"http://www.postgresql.org/docs/current/static/runtime.html">
663+
Administration Guide/Server Run-time Environment/Run-time Configuration</a>.
664+
You can disable<I>fsync()</I> by using<i>fsync</I> option. This will
662665
prevent<I>fsync()</I>s from flushing to disk after every
663666
transaction.</P>
664667

665-
<P>You canalsouse the<I>postmaster</I><I>-B</I> option to
668+
<P>You can use the<I>shared_buffers</I> option to
666669
increase the number of shared memory buffers used by the backend
667670
processes. If you make this parameter too high, the
668671
<I>postmaster</I> may not start because you have exceeded your
669672
kernel's limit on shared memory space. Each buffer is 8K and the
670-
default is64 buffers.</P>
673+
default is1000 buffers.</P>
671674

672-
<P>You can also use the backend<I>-S</I> option to increase the
673-
maximum amount of memory used by the backend process for temporary
674-
sorts. The<I>-S</I> value is measured in kilobytes, and the
675-
default is 512 (i.e. 512K).</P>
675+
<P>You can also use the<I>sort_mem</I> and<I>work_mem</I> options
676+
to increase the maximum amount of memory used by the backend processes
677+
for each temporary sort. The default is 1024 (i.e. 1MB).</P>
676678

677679
<P>You can also use the<SMALL>CLUSTER</SMALL> command to group
678680
data in tables to match an index. See the<SMALL>CLUSTER</SMALL>
@@ -717,17 +719,18 @@ <H4><A name="3.7">3.7</A>) What debugging features are
717719

718720
<P>If<I>postmaster</I> is running, start<I>psql</I> in one
719721
window, then find the<SMALL>PID</SMALL> of the<I>postgres</I>
720-
process used by<I>psql</I>. Use a debugger to attach to the
721-
<I>postgres</I><SMALL>PID</SMALL>. You can set breakpoints in the
722-
debugger and issue queries from<I>psql</I>. If you are debugging
723-
<I>postgres</I> startup, you can set PGOPTIONS="-W n", then start
724-
<I>psql</I>. This will cause startup to delay for<I>n</I> seconds
725-
so you can attach to the process with the debugger, set any
726-
breakpoints, and continue through the startup sequence.</P>
727-
728-
<P>The<I>postgres</I> program has<I>-s, -A</I>, and<I>-t</I>
729-
options that can be very useful for debugging and performance
730-
measurements.</P>
722+
process used by<I>psql</I> using<pre>SELECT pg_backend_pid()</pre>.
723+
Use a debugger to attach to the<I>postgres</I><SMALL>PID</SMALL>.
724+
You can set breakpoints in the debugger and issue queries from
725+
<I>psql</I>. If you are debugging<I>postgres</I> startup, you can
726+
set PGOPTIONS="-W n", then start<I>psql</I>. This will cause startup
727+
to delay for<I>n</I> seconds so you can attach to the process with
728+
the debugger, set any breakpoints, and continue through the startup
729+
sequence.</P>
730+
731+
<P>There are several<pre>log_*</pre> server configuration variables
732+
that enable printing of process statistics which can be very useful
733+
for debugging and performance measurements.</P>
731734

732735
<P>You can also compile with profiling to see what functions are
733736
taking execution time. The backend profile files will be deposited
@@ -947,7 +950,8 @@ <H4><A name="4.7">4.7</A>) How do I find out what tables, indexes,
947950

948951
<H4><Aname="4.8">4.8</A>) My queries are slow or don't make use of
949952
the indexes. Why?</H4>
950-
Indexes are not automatically used by every query. Indexes are only
953+
954+
<P>Indexes are not automatically used by every query. Indexes are only
951955
used if the table is larger than a minimum size, and the query
952956
selects only a small percentage of the rows in the table. This is
953957
because the random disk access caused by an index scan can be
@@ -997,7 +1001,11 @@ <H4><A name="4.8">4.8</A>) My queries are slow or don't make use of
9971001
<I>~*</I> do not utilise indexes. Instead, use functional
9981002
indexes, which are described in section<ahref="#4.12">4.12</a>.</LI>
9991003
<LI>The default<I>C</I> locale must be used during
1000-
<i>initdb</i>.</LI>
1004+
<i>initdb</i> because it is not possible to know the next-greater
1005+
character in a non-C locale. You can create a special
1006+
<PRE>text_pattern_ops</PRE> index for such cases that work only
1007+
for<PRE>LIKE</PRE> indexing.
1008+
</LI>
10011009
</UL>
10021010
<P>
10031011

@@ -1169,8 +1177,8 @@ <H4><A name="4.15.2">4.15.2</A>) How do I get the value of a
11691177
value is made available via<I>$sth-&gt;{pg_oid_status}</I> after
11701178
<I>$sth-&gt;execute()</I>.
11711179

1172-
<H4><Aname="4.15.3">4.15.3</A>)Don't<I>currval()</I> and
1173-
<I>nextval()</I>lead to a race condition with other users?</H4>
1180+
<H4><Aname="4.15.3">4.15.3</A>)Doesn't<I>currval()</I>
1181+
lead to a race condition with other users?</H4>
11741182

11751183
<P>No.<I>currval()</I> returns the current value assigned by your
11761184
backend, not by all users.</P>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp