From 726926a523906ab9d6a405a4239df090efd1d78c Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Thu, 1 Jun 2000 01:34:02 +0000 Subject: [PATCH] Update pgcvslog --- doc/TODO | 4 +- doc/TODO.detail/performance | 211 +++++++++++++++++++++++++++++ doc/src/FAQ.html | 259 +++++++++++++++++------------------- src/tools/pgcvslog | 17 ++- 4 files changed, 345 insertions(+), 146 deletions(-) diff --git a/doc/TODO b/doc/TODO index 168adf66a6..91a8f554b9 100644 --- a/doc/TODO +++ b/doc/TODO @@ -192,7 +192,8 @@ PERFORMANCE FSYNC -* Allow transaction commits with rollback with no-fsync performance [fsync](Vadim) +* Allow transaction commits with rollback with no-fsync performance + [fsync] (Vadim) INDEXES @@ -231,6 +232,7 @@ MISC * Remove pg_listener index * Remove ANALYZE from VACUUM so it can be run separately without locks * Gather more accurate statistics using indexes +* Improve statistics storage in pg_class [performance] SOURCE CODE ----------- diff --git a/doc/TODO.detail/performance b/doc/TODO.detail/performance index abe1f67619..e73550bb5a 100644 --- a/doc/TODO.detail/performance +++ b/doc/TODO.detail/performance @@ -341,3 +341,214 @@ Informix Software (No, really) 300 Lakeside Drive Oakland, CA 94612 good, you'll have to ram them down people's throats." -- Howard Aiken +From owner-pgsql-hackers@hub.org Tue Oct 19 10:31:10 1999 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id KAA29087 + for ; Tue, 19 Oct 1999 10:31:08 -0400 (EDT) +Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.2 $) with ESMTP id KAA27535 for ; Tue, 19 Oct 1999 10:19:47 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.9.3/8.9.3) with SMTP id KAA30328; + Tue, 19 Oct 1999 10:12:10 -0400 (EDT) + (envelope-from owner-pgsql-hackers) +Received: by hub.org (bulk_mailer v1.5); Tue, 19 Oct 1999 10:11:55 -0400 +Received: (from majordom@localhost) + by hub.org (8.9.3/8.9.3) id KAA30030 + for pgsql-hackers-outgoing; Tue, 19 Oct 1999 10:11:00 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) + by hub.org (8.9.3/8.9.3) with ESMTP id KAA29914 + for ; Tue, 19 Oct 1999 10:10:33 -0400 (EDT) + (envelope-from tgl@sss.pgh.pa.us) +Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) + by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA09038; + Tue, 19 Oct 1999 10:09:15 -0400 (EDT) +To: "Hiroshi Inoue" +cc: "Vadim Mikheev" , pgsql-hackers@postgreSQL.org +Subject: Re: [HACKERS] mdnblocks is an amazing time sink in huge relations +In-reply-to: Your message of Tue, 19 Oct 1999 19:03:22 +0900 + <000801bf1a19$2d88ae20$2801007e@cadzone.tpf.co.jp> +Date: Tue, 19 Oct 1999 10:09:15 -0400 +Message-ID: <9036.940342155@sss.pgh.pa.us> +From: Tom Lane +Sender: owner-pgsql-hackers@postgreSQL.org +Status: OR + +"Hiroshi Inoue" writes: +> 1. shared cache holds committed system tuples. +> 2. private cache holds uncommitted system tuples. +> 3. relpages of shared cache are updated immediately by +> phisical change and corresponding buffer pages are +> marked dirty. +> 4. on commit, the contents of uncommitted tuples except +> relpages,reltuples,... are copied to correponding tuples +> in shared cache and the combined contents are +> committed. +> If so,catalog cache invalidation would be no longer needed. +> But synchronization of the step 4. may be difficult. + +I think the main problem is that relpages and reltuples shouldn't +be kept in pg_class columns at all, because they need to have +very different update behavior from the other pg_class columns. + +The rest of pg_class is update-on-commit, and we can lock down any one +row in the normal MVCC way (if transaction A has modified a row and +transaction B also wants to modify it, B waits for A to commit or abort, +so it can know which version of the row to start from). Furthermore, +there can legitimately be several different values of a row in use in +different places: the latest committed, an uncommitted modification, and +one or more old values that are still being used by active transactions +because they were current when those transactions started. (BTW, the +present relcache is pretty bad about maintaining pure MVCC transaction +semantics like this, but it seems clear to me that that's the direction +we want to go in.) + +relpages cannot operate this way. To be useful for avoiding lseeks, +relpages *must* change exactly when the physical file changes. It +matters not at all whether the particular transaction that extended the +file ultimately commits or not. Moreover there can be only one correct +value (per relation) across the whole system, because there is only one +length of the relation file. + +If we want to take reltuples seriously and try to maintain it +on-the-fly, then I think it needs still a third behavior. Clearly +it cannot be updated using MVCC rules, or we lose all writer +concurrency (if A has added tuples to a rel, B would have to wait +for A to commit before it could update reltuples...). Furthermore +"updating" isn't a simple matter of storing what you think the new +value is; otherwise two transactions adding tuples in parallel would +leave the wrong answer after B commits and overwrites A's value. +I think it would work for each transaction to keep track of a net delta +in reltuples for each table it's changed (total tuples added less total +tuples deleted), and then atomically add that value to the table's +shared reltuples counter during commit. But that still leaves the +problem of how you use the counter during a transaction to get an +accurate answer to the question "If I scan this table now, how many tuples +will I see?" At the time the question is asked, the current shared +counter value might include the effects of transactions that have +committed since your transaction started, and therefore are not visible +under MVCC rules. I think getting the correct answer would involve +making an instantaneous copy of the current counter at the start of +your xact, and then adding your own private net-uncommitted-delta to +the saved shared counter value when asked the question. This doesn't +look real practical --- you'd have to save the reltuples counts of +*all* tables in the database at the start of each xact, on the off +chance that you might need them. Ugh. Perhaps someone has a better +idea. In any case, reltuples clearly needs different mechanisms than +the ordinary fields in pg_class do, because updating it will be a +performance bottleneck otherwise. + +If we allow reltuples to be updated only by vacuum-like events, as +it is now, then I think keeping it in pg_class is still OK. + +In short, it seems clear to me that relpages should be removed from +pg_class and kept somewhere else if we want to make it more reliable +than it is now, and the same for reltuples (but reltuples doesn't +behave the same as relpages, and probably ought to be handled +differently). + + regards, tom lane + +************ + +From owner-pgsql-hackers@hub.org Tue Oct 19 21:25:30 1999 +Received: from renoir.op.net (root@renoir.op.net [209.152.193.4]) + by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id VAA28130 + for ; Tue, 19 Oct 1999 21:25:26 -0400 (EDT) +Received: from hub.org (hub.org [216.126.84.1]) by renoir.op.net (o1/$Revision: 1.2 $) with ESMTP id VAA10512 for ; Tue, 19 Oct 1999 21:15:28 -0400 (EDT) +Received: from localhost (majordom@localhost) + by hub.org (8.9.3/8.9.3) with SMTP id VAA50745; + Tue, 19 Oct 1999 21:07:23 -0400 (EDT) + (envelope-from owner-pgsql-hackers) +Received: by hub.org (bulk_mailer v1.5); Tue, 19 Oct 1999 21:07:01 -0400 +Received: (from majordom@localhost) + by hub.org (8.9.3/8.9.3) id VAA50644 + for pgsql-hackers-outgoing; Tue, 19 Oct 1999 21:06:06 -0400 (EDT) + (envelope-from owner-pgsql-hackers@postgreSQL.org) +Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) + by hub.org (8.9.3/8.9.3) with ESMTP id VAA50584 + for ; Tue, 19 Oct 1999 21:05:26 -0400 (EDT) + (envelope-from Inoue@tpf.co.jp) +Received: from cadzone ([126.0.1.40] (may be forged)) + by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP + id KAA01715; Wed, 20 Oct 1999 10:05:14 +0900 +From: "Hiroshi Inoue" +To: "Tom Lane" +Cc: +Subject: RE: [HACKERS] mdnblocks is an amazing time sink in huge relations +Date: Wed, 20 Oct 1999 10:09:13 +0900 +Message-ID: <000501bf1a97$b925a860$2801007e@cadzone.tpf.co.jp> +MIME-Version: 1.0 +Content-Type: text/plain; + charset="iso-8859-1" +Content-Transfer-Encoding: 7bit +X-Priority: 3 (Normal) +X-MSMail-Priority: Normal +X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 +X-Mimeole: Produced By Microsoft MimeOLE V4.72.2106.4 +Importance: Normal +Sender: owner-pgsql-hackers@postgreSQL.org +Status: ORr + +> -----Original Message----- +> From: Hiroshi Inoue [mailto:Inoue@tpf.co.jp] +> Sent: Tuesday, October 19, 1999 6:45 PM +> To: Tom Lane +> Cc: pgsql-hackers@postgreSQL.org +> Subject: RE: [HACKERS] mdnblocks is an amazing time sink in huge +> relations +> +> +> > +> > "Hiroshi Inoue" writes: +> +> [snip] +> +> > +> > > Deletion is necessary only not to consume disk space. +> > > +> > > For example vacuum could remove not deleted files. +> > +> > Hmm ... interesting idea ... but I can hear the complaints +> > from users already... +> > +> +> My idea is only an analogy of PostgreSQL's simple recovery +> mechanism of tuples. +> +> And my main point is +> "delete fails after commit" doesn't harm the database +> except that not deleted files consume disk space. +> +> Of cource,it's preferable to delete relation files immediately +> after(or just when) commit. +> Useless files are visible though useless tuples are invisible. +> + +Anyway I don't need "DROP TABLE inside transactions" now +and my idea is originally for that issue. + +After a thought,I propose the following solution. + +1. mdcreate() couldn't create existent relation files. + If the existent file is of length zero,we would overwrite + the file.(seems the comment in md.c says so but the + code doesn't do so). + If the file is an Index relation file,we would overwrite + the file. + +2. mdunlink() couldn't unlink non-existent relation files. + mdunlink() doesn't call elog(ERROR) even if the file + doesn't exist,though I couldn't find where to change + now. + mdopen() doesn't call elog(ERROR) even if the file + doesn't exist and leaves the relation as CLOSED. + +Comments ? + +Regards. + +Hiroshi Inoue +Inoue@tpf.co.jp + +************ + diff --git a/doc/src/FAQ.html b/doc/src/FAQ.html index 4285490eeb..a4f6a57d86 100644 --- a/doc/src/FAQ.html +++ b/doc/src/FAQ.html @@ -28,109 +28,109 @@ HREF="http://www.PostgreSQL.org/docs/faq-hpux.shtml">http://www.PostgreSQL.org/d

General Questions

-1.1) What is PostgreSQL?
-1.2) What's the copyright on PostgreSQL?
-1.3) What Unix platforms does PostgreSQL run on?
+1.1) What is PostgreSQL?
+1.2) What's the copyright on PostgreSQL?
+1.3) What Unix platforms does PostgreSQL run on?
1.4) What non-unix ports are available?
-1.5) Where can I get PostgreSQL?
-1.6) Where can I get support for PostgreSQL?
-1.7) What is the latest release of PostgreSQL?
-1.8) What documentation is available for PostgreSQL?
-1.9) How do I find out about known bugs or missing features?
-1.10) How can I learn SQL?
-1.11) Is PostgreSQL Y2K compliant?
-1.12) How do I join the development team?
-1.13) How do I submit a bug report?
-1.14) How does PostgreSQL compare to other DBMS's?
+1.5) Where can I get PostgreSQL?
+1.6) Where can I get support for PostgreSQL?
+1.7) What is the latest release of PostgreSQL?
+1.8) What documentation is available for PostgreSQL?
+1.9) How do I find out about known bugs or missing features?
+1.10) How can I learn SQL?
+1.11) Is PostgreSQL Y2K compliant?
+1.12) How do I join the development team?
+1.13) How do I submit a bug report?
+1.14) How does PostgreSQL compare to other DBMS's?
+1.15) What are the maximum size limits?

User Client Questions

-2.1) Are there ODBC drivers for +2.1) Are there ODBC drivers for PostgreSQL?
-2.2) What tools are available for hooking +2.2) What tools are available for hooking PostgreSQL to Web pages?
-2.3) Does PostgreSQL have a graphical user interface? +2.3) Does PostgreSQL have a graphical user interface? A report generator? An embedded query language interface?
-2.4) What languages are available to communicate +2.4) What languages are available to communicate with PostgreSQL?

Administrative Questions

-3.1) Why does initdb fail?
-3.2) How do I install PostgreSQL somewhere other than +3.1) Why does initdb fail?
+3.2) How do I install PostgreSQL somewhere other than /usr/local/pgsql?
-3.3) When I start the postmaster, I get a +3.3) When I start the postmaster, I get a Bad System Call or core dumped message. Why?
-3.4) When I try to start the postmaster, I get +3.4) When I try to start the postmaster, I get IpcMemoryCreate errors3. Why?
3.5) When I try to start the postmaster, I get IpcSemaphoreCreate errors. Why?
-3.6) How do I prevent other hosts from accessing my +3.6) How do I prevent other hosts from accessing my PostgreSQL database?
-3.7) Why can't I connect to my database from +3.7) Why can't I connect to my database from another machine?
-3.8) Why can't I access the database as the +3.8) Why can't I access the database as the root user?
-3.9) All my servers crash under concurrent +3.9) All my servers crash under concurrent table access. Why?
-3.10) How do I tune the database engine for +3.10) How do I tune the database engine for better performance?
-3.11) What debugging features are available in +3.11) What debugging features are available in PostgreSQL?
3.12) I get 'Sorry, too many clients' when trying to connect. Why?
-3.13) What are the pg_psort.XXX files in my +3.13) What are the pg_psort.XXX files in my database directory?
-3.14) How do I set up a pg_group?

Operational Questions

-4.1) The system seems to be confused about commas, +4.1) The system seems to be confused about commas, decimal points, and date formats.
-4.2) What is the exact difference between +4.2) What is the exact difference between binary cursors and normal cursors?
-4.3) How do I select only the first few rows of +4.3) How do I select only the first few rows of a query?
-4.4) How do I get a list of tables, or other +4.4) How do I get a list of tables, or other things I can see in psql?
-4.5) How do you remove a column from a table?
+4.5) How do you remove a column from a table?
-4.6) What is the maximum size for a +4.6) What is the maximum size for a row, table, database?
-4.7) How much database disk space is required +4.7) How much database disk space is required to store data from a typical flat file?
-4.8) How do I find out what indices or +4.8) How do I find out what indices or operations are defined in the database?
-4.9) My queries are slow or don't make use of the +4.9) My queries are slow or don't make use of the indexes. Why?
-4.10) How do I see how the query optimizer is +4.10) How do I see how the query optimizer is evaluating my query?
-4.11) What is an R-tree index?
-4.12) What is Genetic Query Optimization?
+4.11) What is an R-tree index?
+4.12) What is Genetic Query Optimization?
-4.13) How do I do regular expression searches +4.13) How do I do regular expression searches and case-insensitive regexp searching?
-4.14) In a query, how do I detect if a field +4.14) In a query, how do I detect if a field is NULL?
-4.15) What is the difference between the +4.15) What is the difference between the various character types?
-4.16.1) How do I create a serial/auto-incrementing field?
-4.16.2) How do I get the value of a serial insert?
-4.16.3) Wouldn't use of currval() and nextval() lead to a race condition with other concurrent backend processes?
+4.16.1) How do I create a serial/auto-incrementing field?
+4.16.2) How do I get the value of a serial insert?
+4.16.3) Wouldn't use of currval() and nextval() lead to a race condition with other concurrent backend processes?
-4.17) What is an oid? What is a tid?
-4.18) What is the meaning of some of the terms +4.17) What is an oid? What is a tid?
+4.18) What is the meaning of some of the terms used in PostgreSQL?
-4.19) Why do I get the error "FATAL: palloc +4.19) Why do I get the error "FATAL: palloc failure: memory exhausted?"
-4.20) How do I tell what PostgreSQL version I +4.20) How do I tell what PostgreSQL version I am running?
-4.21) My large-object operations get invalid +4.21) My large-object operations get invalid large obj descriptor. Why?
4.22) How do I create a column that will default to the current time?
@@ -138,15 +138,15 @@ current time?

Extending PostgreSQL

-5.1) I wrote a user-defined function. When I run +5.1) I wrote a user-defined function. When I run it in psql, why does it dumps core?
-5.2) What does the message: +5.2) What does the message: NOTICE:PortalHeapMemoryFree: 0x402251d0 not in alloc set! mean?
-5.3) How can I contribute some nifty new types and functions +5.3) How can I contribute some nifty new types and functions for PostgreSQL?
-5.4) How do I write a C function to return a +5.4) How do I write a C function to return a tuple?
-5.5) I have changed a source file. Why does the +5.5) I have changed a source file. Why does the recompile does not see the change?
@@ -154,7 +154,7 @@ recompile does not see the change?

General Questions

1.1) What is PostgreSQL?

+NAME="1.1">1.1) What is PostgreSQL?

PostgreSQL is an enhancement of the POSTGRES database management system, a next-generation DBMS research prototype. While PostgreSQL retains the @@ -183,7 +183,7 @@ name was changed at the end of 1996 to PostgreSQL.

It is pronounced Post-Gres-Q-L. -

1.2) What's the copyright on +

1.2) What's the copyright on PostgreSQL?

PostgreSQL is subject to the following COPYRIGHT.

@@ -215,7 +215,7 @@ MODIFICATIONS.

-

1.3) What Unix platforms does PostgreSQL run +

1.3) What Unix platforms does PostgreSQL run on?

The authors have compiled and tested PostgreSQL on the following @@ -243,7 +243,7 @@ platforms (some of these compiles require gcc):

-

1.4) What non-unix ports are available?

+

1.4) What non-unix ports are available?

It is possible to compile the libpq C library, psql, and other interfaces and binaries to run on MS Windows platforms. In this case, @@ -263,14 +263,14 @@ There is another port using U/Win at http://surya.wipro.com/uwin/ported.html. -

1.5) Where can I get PostgreSQL?

+

1.5) Where can I get PostgreSQL?

The primary anonymous ftp site for PostgreSQL is ftp://ftp.postgreSQL.org/pub

For mirror sites, see our main web site. -

1.6) Where can I get support for PostgreSQL?

+

1.6) Where can I get support for PostgreSQL?

There is no official support for PostgreSQL from the University of California, Berkeley. It is maintained through volunteer effort.

@@ -336,14 +336,14 @@ Commercial support for PostgreSQL is available at http://www.pgsql.com/

-

1.7) What is the latest release of PostgreSQL?

+

1.7) What is the latest release of PostgreSQL?

The latest release of PostgreSQL is version 7.0.

We plan to have major releases every four months.

-

1.8) What documentation is available for PostgreSQL?

+

1.8) What documentation is available for PostgreSQL?

Several manuals, manual pages, and some small test examples are included in the distribution. See the /doc directory. You can also @@ -358,14 +358,14 @@ operators, functions, aggregates, etc.

The web site contains even more documentation.

-

1.9) How do I find out about known bugs or missing features? +

1.9) How do I find out about known bugs or missing features?

PostgreSQL supports an extended subset of SQL-92. See our TODO for a list of known bugs, missing features, and future plans.

-

1.10) How can I learn SQL?

+

1.10) How can I learn SQL?

There is a nice tutorial at @@ -382,12 +382,12 @@ Addison Wesley. Others like Lan Times Guide to SQL, Groff et al., Osborne McGraw-Hill.

-

1.11) Is PostgreSQL Y2K compliant?

+

1.11) Is PostgreSQL Y2K compliant?

Yes, we easily handle dates past the year 2000AD, and before 2000BC.

-

1.12) How do I join the development team?

+

1.12) How do I join the development team?

First, download the latest sources and read the PostgreSQL Developers documentation on our web site, or in the distribution. @@ -400,7 +400,7 @@ high-quality patches that it was a pain for the existing committers to keep up, and we had confidence that patches they committed were likely to be of high quality. -

1.13) How do I submit a bug report?

+

1.13) How do I submit a bug report?

Fill out the "bug-template" file and send it to: bugs@postgreSQL.org

@@ -410,7 +410,7 @@ HREF="ftp://ftp.postgreSQL.org/pub">ftp://ftp.postgreSQL.org/pub to see if there is a more recent PostgreSQL version or patches.

-

1.14) How does PostgreSQL compare to other +

1.14) How does PostgreSQL compare to other DBMS's?

There are several ways of measuring software: features, performance, @@ -484,6 +484,19 @@ add our code to your product with no limitations, except those outlined in our BSD-style license stated above.

+

1.14) What are the maximum size limits?

+ +These are the limits: + +

+Maximum size for a database? 	         unlimited (60GB databases exist)
+Maximum size for a table?                unlimited on all operating systems
+Maximum size for a row?                  8k, configurable to 32k
+Maximum number of rows in a table?	 unlimited
+Maximum number of columns table?         unlimited
+Maximun number of indexes on a table?	 unlimited
+
+The row length limit will be removed in 7.1.

@@ -492,7 +505,7 @@ in our BSD-style license stated above.

-

2.1) Are there ODBC drivers for PostgreSQL?

+

2.1) Are there ODBC drivers for PostgreSQL?

There are two ODBC drivers available, PostODBC and OpenLink ODBC.

@@ -512,7 +525,7 @@ HREF="mailto:postgres95@openlink.co.uk">postgres95@openlink.co.uk.

-

2.2) What tools are available for hooking +

2.2) What tools are available for hooking PostgreSQL to Web pages?

A nice introduction to Database-backed Web pages can be seen at: A WWW gateway based on WDB using perl can be downloaded from http://www.eol.ists.ca/~dunlop/wdb-p95 -

2.3) Does PostgreSQL have a graphical user interface? +

2.3) Does PostgreSQL have a graphical user interface? A report generator? An embedded query language interface?

We have a nice graphical user interface called pgaccess, which is @@ -541,7 +554,7 @@ generator. The web page is ecpg, which is an embedded SQL query language interface for C. -

2.4) What languages are available to +

2.4) What languages are available to communicate with PostgreSQL?

We have: @@ -563,7 +576,7 @@ We have:

Administrative Questions

-

3.1) Why does initdb fail?

+

3.1) Why does initdb fail?

  • check that you don't have any of the previous version's binaries in @@ -574,7 +587,7 @@ your path (If you see the message WARN:heap_modifytuple: repl is

-

3.2) How do I install PostgreSQL somewhere +

3.2) How do I install PostgreSQL somewhere other than /usr/local/pgsql?

The simplest way is to specify the --prefix option when running configure. @@ -582,7 +595,7 @@ If you forgot to do that, you can edit Makefile.global and change POSTGRESDIR accordingly, or create a Makefile.custom and define POSTGRESDIR there.

-

3.3) When I start the postmaster, I get a Bad +

3.3) When I start the postmaster, I get a Bad System Call or core dumped message. Why?

It could be a variety of problems, but first check to see that you @@ -590,7 +603,7 @@ have system V extensions installed in your kernel. PostgreSQL requires kernel support for shared memory and semaphores.

-

3.4) When I try to start the postmaster, I +

3.4) When I try to start the postmaster, I get IpcMemoryCreate errors. Why?

You either do not have shared memory configured properly in kernel or @@ -600,7 +613,7 @@ and backend processes you configure postmaster to run with. For most systems, with default numbers of buffers and processes, you need a minimum of ~1MB.

-

3.5) When I try to start the postmaster, I +

3.5) When I try to start the postmaster, I get IpcSemaphoreCreate errors. Why?

If the error message is IpcSemaphoreCreate: semget failed (No space @@ -615,7 +628,7 @@ If the error message is something else, you might not have semaphore support configured in your kernel at all.

-

3.6) How do I prevent other hosts from +

3.6) How do I prevent other hosts from accessing my PostgreSQL database?

By default, PostgreSQL only allows connections from the local machine @@ -625,7 +638,7 @@ unless you add the -i flag to the postmaster, $PGDATA/pg_hba.conf accordingly. This will allow TCP/IP connections.

-

3.7) Why can't I connect to my database from +

3.7) Why can't I connect to my database from another machine?

The default configuration allows only unix domain socket connections @@ -635,7 +648,7 @@ appropriate host entry to the file pgsql/data/pg_hba.conf. See the pg_hba.conf manual page.

-

3.8) Why can't I access the database as the root +

3.8) Why can't I access the database as the root user?

You should not create database users with user id 0 (root). They will be @@ -644,14 +657,14 @@ of the ability of any user to dynamically link object modules into the database engine.

-

3.9) All my servers crash under concurrent +

3.9) All my servers crash under concurrent table access. Why?

This problem can be caused by a kernel that is not configured to support semaphores.

-

3.10) How do I tune the database engine for +

3.10) How do I tune the database engine for better performance?

Certainly, indices can speed up queries. The EXPLAIN command @@ -685,7 +698,7 @@ You can also use the CLUSTER command to group data in base tables match an index. See the cluster(l) manual page for more details.

-

3.11) What debugging features are available in +

3.11) What debugging features are available in PostgreSQL?

PostgreSQL has several features that report status information that can @@ -763,7 +776,7 @@ In Postgres versions prior to 6.5, the maximum number of backends was 64, and changing it required a rebuild after altering the MaxBackendId constant in include/storage/sinvaladt.h.

-

3.13) What are the pg_tempNNN.NN files in my +

3.13) What are the pg_tempNNN.NN files in my database directory?

They are temporary files generated by the query executor. For @@ -775,38 +788,12 @@ The temp files should go away automatically, but might not if a backend crashes during a sort. If you have no transactions running at the time, it is safe to delete the pg_tempNNN.NN files.

-

3.14) How do I set up a pg_group?

- -Currently, there is no easy interface to set up user groups. You have to -explicitly insert/update the pg_group table. For example: - -

-	jolly=> insert into pg_group (groname, grosysid, grolist)
-	jolly=>     values ('posthackers', '1234', '{5443, 8261}');
-	INSERT 548224
-	jolly=> grant insert on foo to group posthackers;
-	CHANGE
-	jolly=>
-

- - The fields in pg_group are: -

    -
  • groname: the group name. This a name and should -be purely alphanumeric. Do not include underscores -or other punctuation. -
  • grosysid: the group id. This is an int4. -This should be unique for each group. -
  • grolist: the list of pg_user id's that belong in the group. -This is an int4[]. -

- -

Operational Questions

-

4.1) The system seems to be confused about +

4.1) The system seems to be confused about commas, decimal points, and date formats.

Check your locale configuration. PostgreSQL uses the locale settings of @@ -815,12 +802,12 @@ SET commands to control the date format. Set those accordingly for your operating environment.

-

4.2) What is the exact difference between +

4.2) What is the exact difference between binary cursors and normal cursors?

See the DECLARE manual page for a description.

-

4.3) How do I SELECT only the first few +

4.3) How do I SELECT only the first few rows of a query?

See the FETCH manual page, or use SELECT ... LIMIT....

@@ -842,7 +829,7 @@ with the -E option so that it will print out the queries it uses to execute the commands you give.

-

4.5) How do you remove a column from a +

4.5) How do you remove a column from a table?

We do not support ALTER TABLE DROP COLUMN, but do @@ -858,7 +845,7 @@ this: -

4.6) What is the maximum size for a +

4.6) What is the maximum size for a row, table, database?

Rows are limited to 8K bytes, but this can be changed by editing @@ -904,7 +891,7 @@ this data can be estimated at 14MB: Indexes do not contain as much overhead, but do contain the data that is being indexed, so they can be large also.

-

4.8) How do I find out what indices or +

4.8) How do I find out what indices or operations are defined in the database?

psql has a variety of backslash commands to show such information. Use @@ -915,7 +902,7 @@ illustrates many of the SELECTs needed to get information from the database system tables.

-

4.9) My queries are slow or don't make +

4.9) My queries are slow or don't make use of the indexes. Why?

PostgreSQL does not automatically maintain statistics. One has to make @@ -943,12 +930,12 @@ the string. So, to use indices, LIKE searches should not begin with %, and ~(regular expression searches) should start with ^. -

4.10) How do I see how the query optimizer is +

4.10) How do I see how the query optimizer is evaluating my query?

See the EXPLAIN manual page.

-

4.11) What is an R-tree index?

+

4.11) What is an R-tree index?

An r-tree index is used for indexing spatial data. A hash index can't handle range searches. A B-tree index only handles range searches in a @@ -971,7 +958,7 @@ extending R-trees require a bit of work and we don't currently have any documentation on how to do it.

-

4.12) What is Genetic Query +

4.12) What is Genetic Query Optimization?

The GEQO module in PostgreSQL is intended to solve the query @@ -983,7 +970,7 @@ For further information see the documentation. -

4.13) How do I do regular expression searches and +

4.13) How do I do regular expression searches and case-insensitive regexp searching?

The ~ operator does regular-expression matching, and ~* @@ -994,13 +981,13 @@ effect of case-insensitive LIKE with this: WHERE lower(textfield) LIKE lower(pattern) -

4.14) In a query, how do I detect if a field +

4.14) In a query, how do I detect if a field is NULL?

You test the column with IS NULL and IS NOT NULL.

-

4.15) What is the difference between the +

4.15) What is the difference between the various character types?

@@ -1025,7 +1012,7 @@ them. Specifically, the penalty is for access to all columns after the
 first column of this type.

 
 
-
4.16.1)	How do I create a
+
4.16.1) How do I create a
 serial/auto-incrementing field?

 
 PostgreSQL supports SERIAL data type.  It auto-creates a
@@ -1054,7 +1041,7 @@ option or COPY WITH OIDS option to preserve the oids.

 For more details, see Bruce Momjian's chapter on 
 Numbering Rows.
 
-
4.16.2)	How do I get the back the generated SERIAL value after an insert?

+
4.16.2) How do I get the back the generated SERIAL value after an insert?

 Probably the simplest approach is to to retrieve the next SERIAL value from the sequence object with the nextval() function before inserting and then insert it explicitly.  Using the example table in 4.16.1, that might look like this:
 
 	$newSerialID = nextval('person_id_seq');
@@ -1069,12 +1056,12 @@ Similarly, you could retrieve the just-assigned SERIAL value with the currval
 

 Finally, you could use the oid returned from the INSERT statement to lookup the default value, though this is probably the least portable approach.  In perl, using DBI with Edmund Mergl's DBD::Pg module, the oid value is made available via $sth->{pg_oid_status} after $sth->execute().
 
-
4.16.3)	Wouldn't use of currval() and nextval() lead to a race condition with other concurrent backend processes?

+
4.16.3) Wouldn't use of currval() and nextval() lead to a race condition with other concurrent backend processes?

 
 No.  That has been handled by the backends.
 
 
-
4.17)	What is an oid?  What is a tid?

+
4.17) What is an oid?  What is a tid?

 
 Oids are PostgreSQL's answer to unique row ids.  Every row that is
 created in PostgreSQL gets a unique oid.  All oids generated during
@@ -1111,7 +1098,7 @@ values.  Tids change after rows are modified or reloaded.  They are used
 by index entries to point to physical rows.

 
 
-
4.18)	What is the meaning of some of the terms
+
4.18) What is the meaning of some of the terms
 used in PostgreSQL?

 
 Some of the source code and older documentation use terms that have more
@@ -1206,20 +1193,20 @@ We hope to fix this limitation in a future release.
 
Extending PostgreSQL

 
 
-
5.1)	I wrote a user-defined function.  When
+
5.1) I wrote a user-defined function.  When
 I run it in psql, why does it dump core?

 
 The problem could be a number of things.  Try testing your user-defined
 function in a stand alone test program first.
 
-
5.2)	What does the message:
+
5.2) What does the message:
 NOTICE:PortalHeapMemoryFree: 0x402251d0 not in alloc set! mean?

 
 You are pfree'ing something that was not palloc'ed.
 Beware of mixing malloc/free and palloc/pfree.
 
 
-
5.3)	How can I contribute some nifty new types and
+
5.3) How can I contribute some nifty new types and
 functions for PostgreSQL?

 
 
@@ -1227,13 +1214,13 @@ Send your extensions to the pgsql-hackers mailing list, and they will
 eventually end up in the contrib/ subdirectory.

 
 
-
5.4)	How do I write a C function to return a
+
5.4) How do I write a C function to return a
 tuple?

 
 This requires wizardry so extreme that the authors have never
 tried it, though in principle it can be done.

 
-
5.5)	I have changed a source file.  Why does the
+
5.5) I have changed a source file.  Why does the
 recompile does not see the change?

 
 The Makefiles do not have the proper dependencies for include files. You
diff --git a/src/tools/pgcvslog b/src/tools/pgcvslog
index 51eae013db..83d7e3bbf6 100755
--- a/src/tools/pgcvslog
+++ b/src/tools/pgcvslog
@@ -2,16 +2,17 @@
 # This utility is used to generate a compact list of changes for each
 # release, bjm 2000-02-22
 
-# Usage $0 [-r 'revision pattern'] file
+# Usage $0 file
 
+# no branches: 
 # cvs log -d '>1999-06-14 00:00:00 GMT' . > log
-# pgcvslog -r '\.2\.[0-9]*$' log
+#
+# pre and post-branch logs:
+# cvs log -d'2000-05-08 00:00:00 GMT<2000-05-29 00:00:00 GMT'
+# cvs log -d'>2000-05-29 00:00:00 GMT' -rREL7_0_PATCHES 
+#
 
-if [ "X$1" = "X-r" ]
-then	REV="$2"
-	shift 2
-else	REV=".*"
-fi
+# pgcvslog -r '\.2\.[0-9]*$' log
 
 cat "$@" |
 
@@ -21,8 +22,6 @@ cat "$@" |
 awk '
 	$0 ~ /^Working file:/	{workingfile = $0}
 
-	$1 == "revision" && $2 !~ /'"$REV"'/ {skip = "Y"}
-
 	($0 ~ /^====*$/ || $0 ~ /^----*$/) && skip == "N" \
 	{
 		/* print blank line separating entries */
-- 
2.40.0