[DOCS] Updated Russian translation of the FAQ
There is updated Russian translation of the FAQ in attachment. -- With best wishes, Victor Vislobokov Perm. Russia FAQ_russian.html.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [DOCS] Updated Russian translation of the FAQ
Patch applied to CVS HEAD and 8.0.X. Thanks. --- Viktor Vislobokov wrote: > There is updated Russian translation of the FAQ in attachment. > > -- > With best wishes, > Victor Vislobokov > Perm. Russia > > > [ application/x-gzip is not supported, skipping... ] > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[DOCS] Bibliography updates for new editions?
Should entries in the Bibliography be updated to reflect new editions? For example, Date's _An Introduction to Database Systems_ is now in its eighth edition, and Elmasri and Navathe's _Fundamentals of Database Systems_ is in its fourth. Would it be appropriate to add links to Amazon or another bookseller? If the project would rather not favor a particular retailer, then maybe it would be reasonable to link to the publishers' own web pages where such exist. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [DOCS] Instructions for Linux ipc config
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Mark Kirkwood wrote: > Attached are my first thoughts for the amended instructions. > > Mark Kirkwood wrote: > > > > But to be on the safe side, it would make sense to do something similar > > to the BSD section, and comment about older distributions maybe needing > > to manipulate /proc/kernel/* directly. > > > > > --- runtime.sgml.orig Fri Feb 4 22:17:45 2005 > +++ runtime.sgml Fri Feb 4 22:17:57 2005 > @@ -4414,37 +4414,29 @@ >LinuxIPC configuration > > > -The default shared memory limit (both > -SHMMAX and SHMALL) is 32 > -MB in 2.2 kernels, but it can be changed in the > -proc file system (without reboot). For > -example, to allow 128 MB: > +The default settings are only suitable for small installations > +(the default max segment size is 32 MB). However the remaining > +defaults are quite generously sized, and usually do not require > +changes. The max segment size can be changed via the > +sysctl interface. For example, to allow 128 MB, > +and explicitly set the maximum total shared memory size to 2097152 > +pages (the default): > > -$ echo 134217728 > >/proc/sys/kernel/shmall > -$ echo 134217728 > >/proc/sys/kernel/shmmax > +$ systcl -w kernel.shmmax=134217728 > +$ systcl -w kernel.shmall=2097152 > > -You could put these commands into a script run at boot-time. > - > - > - > -Alternatively, you can use sysctl, if > -available, to control these parameters. Look for a file > -called /etc/sysctl.conf and add lines > -like the following to it: > - > -kernel.shmall = 134217728 > -kernel.shmmax = 134217728 > - > -This file is usually processed at boot time, but > -sysctl can also be called > -explicitly later. > +In addition these settings can be saved between reboots in > +/etc/sysctl.conf. > > > > -Other parameters are sufficiently sized for any application. If > -you want to see for yourself look in > - > /usr/src/linux/include/asm-xxx/shmparam.h > -and /usr/src/linux/include/linux/sem.h. > +Older distributions may not have the sysctl > program, > +but equivalent changes can be made by manipulating the > +/proc filesystem: > + > +$ echo 134217728 > >/proc/sys/kernel/shmmax > +$ echo 2097152 > >/proc/sys/kernel/shmall > + > > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Bruce Momjian| http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [DOCS] faq 4.20: pl/pgsql temporary tables create/drop
Yes, you are right, the question was inaccurate. Here is the updated text: 4.20) Why do I get "missing oid" errors when accessing temporary tables in PL/PgSQL functions? --- Merlin Moncure wrote: > Bruce Momijan wrote: > > Merlin Moncure wrote: > > > The PostgreSQL FAQ currently suggests using dynamic SQL as a > workaround > > > for the table OID caching problem of temp tables in pg/pgsql > functions. > > > While this is ok, it fails to suggest that besides the initial > > > create/drop statements, every statement that touches the table must > also > > > be dynamic. > > Uh, the FAQ reads: > > > > 4.20) Why can't I reliably create/drop > > temporary tables in PL/PgSQL functions? > > > > PL/PgSQL caches function scripts, and an unfortunate side > effect > > is that if a PL/PgSQL function accesses a temporary table, and > that > > table is later dropped and recreated, and the function called > again, > > the function will fail because the cached function contents still > > point to the old temporary table. The solution is to use > > EXECUTE for temporary table access in PL/PgSQL. > This > > will cause the query to be reparsed every time. > > > > What should be changed? I see it saying "function accesses a > temporary > > table". The word "access" suggests all access, not just create/drop. > > You are 100% correct. But something still doesn't feel right. > > Namely, the answer answers the question, "why can't I reliably access > temporary tables in pg/pgsql functions?" Note that a temporary table > not created in a pg/pgsql function will still have this behavior. So, > really, it is the question that is misleading, not the answer. > > One possible re-phrasing would be: > "Why do temporary tables in PL/PgSQL functions give me "missing oid" > errors?" > > I think this matches the existing answer much better. Given further > consideration, my previous suggestions regarding using exception handing > to manage temporary table construction, etc. would be more appropriate > in the proper documentation than in a FAQ. > > I will say that for most cases of usage of temporary tables for storage > from within pg/pgsql functions, using dynamic sql is probably not the > optimial solution unless dynamic sql is more generally preferred. > > Merlin > > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- Bruce Momjian| http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [DOCS] How the planner uses statistics
Mark Kirkwood wrote: > Josh Berkus wrote: > > > > Or plain text, if that's easier. > > > > Probably the easiest way is for me to generate the sgml -> html and send > you that. It would look like a Pg docs html page. I guess the header and > heading information might need a bit of editing (if it is obvious what > should be removed I will do the edit). > > How does that sound? > > At Tom's suggestion, I am going to amend the page to fit into the > 'internals' chapter as opposed to 'performance tips' one. I might do > this first, and send you the resulting page. That sounds good, that this become part of the developer docs. -- Bruce Momjian| http://candle.pha.pa.us [email protected] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [DOCS] How the planner uses statistics
Bruce Momjian wrote:
Mark Kirkwood wrote:
At Tom's suggestion, I am going to amend the page to fit into the
'internals' chapter as opposed to 'performance tips' one. I might do
this first, and send you the resulting page.
That sounds good, that this become part of the developer docs.
Here is the amended version. I have placed it in its own chapter located
immediately after 'bki Backend Interface', however there is nothing
special about that location... feel free to move it around :-)
Mark
diff -Naur sgml.orig/filelist.sgml sgml/filelist.sgml
--- sgml.orig/filelist.sgml Mon Feb 14 15:02:16 2005
+++ sgml/filelist.sgml Tue Feb 15 09:52:33 2005
@@ -77,6 +77,7 @@
+
diff -Naur sgml.orig/howplanstats.sgml sgml/howplanstats.sgml
--- sgml.orig/howplanstats.sgml Thu Jan 1 12:00:00 1970
+++ sgml/howplanstats.sgml Tue Feb 15 17:18:30 2005
@@ -0,0 +1,370 @@
+
+
+
+ How the Planner Uses Statistics
+
+
+ This chapter builds on the material covered in
+ and , and shows how the planner uses the
+ system statistics to estimate the number of rows each stage in a query might
+ return. This is a significant part of the planning / optimizing process,
+ providing much of the raw material for cost calculation.
+
+
+
+ The intent of this chapter is not to document the code —
+ better done in the code itself, but to present an overview of how it works.
+ This will perhaps ease the learning curve for someone who subsequently
+ wishes to read the code. As a consequence, the approach chosen is to analyze
+ a series of incrementally more complex examples.
+
+
+
+ The outputs and algorithms shown below are taken from version 8.0.
+ The behaviour of earlier (or later) versions may vary.
+
+
+
+ Row Estimation Examples
+
+
+ row estimation
+ planner
+
+
+
+ Using examples drawn from the regression test database, let's start with a
+ very simple query:
+
+EXPLAIN SELECT * FROM tenk1;
+
+ QUERY PLAN
+-
+ Seq Scan on tenk1 (cost=0.00..445.00 rows=1 width=244)
+
+
+ How the planner determines the cardinality of tenk1
+ is covered in , but is repeated here for
+ completeness. The number of rows is looked up from
+ pg_class:
+
+
+SELECT reltuples, relpages FROM pg_class WHERE relname = 'tenk1';
+
+ relpages | reltuples
+--+---
+ 345 | 1
+
+ The planner will check the relpages estimate
+ (this is a cheap operation) and if incorrect may scale
+ reltuples to obtain a row estimate. In this case
it
+ does not, thus:
+
+
+rows = 1
+
+
+
+
+
+ let's move on to an example with a range condition in its
+ WHERE clause:
+
+
+EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
+
+ QUERY PLAN
+
+ Seq Scan on tenk1 (cost=0.00..470.00 rows=1031 width=244)
+ Filter: (unique1 < 1000)
+
+
+ The planner examines the WHERE clause condition:
+
+
+unique1 < 1000
+
+
+ and looks up the restriction function for the operator
+ < in pg_operator.
+ This is held in the column oprrest,
+ and the result in this case is scalarltsel.
+ The scalarltsel function retrieves the histogram for
+ unique1 from pg_statistics
+ - we can follow this by using the simpler pg_stats
+ view:
+
+
+SELECT histogram_bounds FROM pg_stats
+WHERE tablename='tenk1' AND attname='unique1';
+
+ histogram_bounds
+--
+ {1,970,1943,2958,3971,5069,6028,7007,7919,8982,9995}
+
+
+ Next the fraction of the histogram occupied by < 1000
+ is worked out. This is the selectivity. The histogram divides the range
+ into equal frequency buckets, so all we have to do is locate the bucket
+ that our value is in and count part of it and
+ all of the ones before. The value 1000 is clearly in
+ the second (970 - 1943) bucket, so by assuming a linear distribution of
+ values inside each bucket we can calculate the selectivity as:
+
+
+selectivity = (1 + (1000 - bckt[2].min)/(bckt[2].max - bckt[2].min))/num_bckts
+= (1 + (1000 - 970)/(1943 - 970))/10
+= 0.1031
+
+
+ that is, one whole bucket plus a linear fraction of the second, divided by
+ the number of buckets. The estimated number of rows can now be calculated as
+ the product of the selectivity and the cardinality of
+ tenk1:
+
+
+rows = rel_cardinality * selectivity
+ = 1 * 0.1031
+ = 1031
+
+
+
+
+
+ Next let's consider an example with equality condition in its
+ WHERE clause:
+
+
+EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'AT';
+
+QUERY PLAN
+--
+ Seq Scan on tenk1 (cost=0.00..470.00 rows=31 width=244)
+ Filter: (stringu1 = 'AT'::name)
+
+
+ Again the planner examines the WHERE cla
