Most-common value docs in PG 12
Our docs for most-common values in PG 12 has:
--> CREATE STATISTICS stts3 (mcv) ON state, city FROM zipcodes;
ANALYZE zipcodes;
SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid =
stxoid),
pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';
index | values | nulls | frequency | base_frequency
---++---+---+
--> 0 | {Washington, DC} | {f,f} | 0.003467 |2.7e-05
1 | {Apo, AE} | {f,f} | 0.003067 |1.9e-05
2 | {Houston, TX} | {f,f} | 0.002167 | 0.000133
3 | {El Paso, TX} | {f,f} | 0.002 | 0.000113
It seems pg_mcv_list_items() reports the column names in the order they
appear in the table, not in the order they appear in the CREATE
STATISTICS statement. Same for psql \d:
\d zipcodes
Table "public.zipcodes"
Column | Type | Collation | Nullable | Default
-+--+---+--+-
city| text | | |
state | text | | |
zipcode | text | | |
Statistics objects:
--> "public"."stts3" (mcv) ON city, state FROM zipcodes
If this is so, why don't we show the CREATE STATISTICS example as
city/state, and not state/city?
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
Re: Most-common value docs in PG 12
On Wed, Aug 28, 2019 at 12:22:38PM -0400, Bruce Momjian wrote:
Our docs for most-common values in PG 12 has:
--> CREATE STATISTICS stts3 (mcv) ON state, city FROM zipcodes;
ANALYZE zipcodes;
SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid =
stxoid),
pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';
index | values | nulls | frequency | base_frequency
---++---+---+
--> 0 | {Washington, DC} | {f,f} | 0.003467 |2.7e-05
1 | {Apo, AE} | {f,f} | 0.003067 |1.9e-05
2 | {Houston, TX} | {f,f} | 0.002167 | 0.000133
3 | {El Paso, TX} | {f,f} | 0.002 | 0.000113
It seems pg_mcv_list_items() reports the column names in the order they
appear in the table, not in the order they appear in the CREATE
STATISTICS statement. Same for psql \d:
\d zipcodes
Table "public.zipcodes"
Column | Type | Collation | Nullable | Default
-+--+---+--+-
city| text | | |
state | text | | |
zipcode | text | | |
Statistics objects:
--> "public"."stts3" (mcv) ON city, state FROM zipcodes
If this is so, why don't we show the CREATE STATISTICS example as
city/state, and not state/city?
Yes, we deduplicate the attributes and store them sorted by attnum. I'm
not sure it makes sense to change the example to match this order, which
is mostly an implementation detail, though. It might be better to point
out the order may not exactly match CREATE STATISTICS, and point users to
what e.g. "\d" shows (because that will show the order as stored in the
system catalog).
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Can we bring some organization to the configure options list?
I wrote: > I was just noticing, while answering a user question, the amount of > unorganized urban sprawl we've accumulated in > https://www.postgresql.org/docs/devel/install-procedure.html > We've got practically-essential options like --prefix and > --with-openssl intermixed with obscure portability flags > and options that only developers should take any interest in. > For bonus points, the ordering seems chosen largely by dartboard; > there's certainly no visible plan to it. > I think we should try to improve the situation by dividing the > configure options into categories and/or separating commonly > used options from obscure ones. I don't have any concrete > proposal to make right now, but I am hoping to kick off a > discussion about what such an organization would look like. Hearing nothing but crickets, I took a stab at this myself, ending up with a division into these categories: Installation Locations PostgreSQL Features PostgreSQL Anti-Features Build Process Details Miscellaneous Developer Options plus a separate section for environment variables. This seems to me to work fairly well, though surely it's not the only way things could have been divided. Except for the "Features" section which contains many commonly-used options, there's generally only one or two commonly-used options per section, which I put first. I also added a short para of advice about which options to use, where it seemed helpful. DocBook wasn't happy with putting inside a 's step list, so I had to move all this stuff out of the installation procedure's step list. I'm not sure whether I like that or not --- it does result in the step list being fairly short and comprehensible, but the info that you need to consult for the "configure" step is now some distance away. It's moot unless somebody knows another way to do the markup, though. Because of relocating and re-ordering the options, the diff is just about unreadable :-(. However, I mostly refrained from changing the descriptions of individual options. They're just ordered differently, and there is more text around them. I did yield to temptation in some small ways though. Comments? regards, tom lane diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml index 4493862..cdc743c 100644 --- a/doc/src/sgml/installation.sgml +++ b/doc/src/sgml/installation.sgml @@ -325,12 +325,12 @@ su - postgres Also check that you have sufficient disk space. You will need about - 100 MB for the source tree during compilation and about 20 MB for + 350 MB for the source tree during compilation and about 60 MB for the installation directory. An empty database cluster takes about - 35 MB; databases take about five times the amount of space that a + 40 MB; databases take about five times the amount of space that a flat text file with the same data would take. If you are going to run the regression tests you will temporarily need up to an extra - 150 MB. Use the df command to check free disk + 300 MB. Use the df command to check free disk space. @@ -349,8 +349,11 @@ su - postgres gunzip postgresql-&version;.tar.gz tar xf postgresql-&version;.tar - (Use bunzip2 instead of gunzip if you - have the .bz2 file.) + (Use bunzip2 instead of gunzip if + you have the .bz2 file. Also, note that most + modern versions of tar can unpack compressed archives + directly, so you don't really need the + separate gunzip or bunzip2 step.) This will create a directory postgresql-&version; under the current directory with the PostgreSQL sources. @@ -387,10 +390,14 @@ su - postgres This script will run a number of tests to determine values for various system dependent variables and detect any quirks of your operating system, and finally will create several files in the -build tree to record what it found. You can also run -configure in a directory outside the source -tree, if you want to keep the build directory separate. This -procedure is also called a +build tree to record what it found. + + + +You can also run configure in a directory outside +the source tree, and then build there, if you want to keep the build +directory separate from the original source files. This procedure is +called a VPATHVPATH build. Here's how: @@ -410,8 +417,230 @@ su - postgres You can customize the build and installation process by supplying one -or more of the following command line options to -configure: +or more command line options to configure. +Typically you would customize the install location, or the set of +optional features that are built. configure +has a large number of options, which are described in +. + + + +Also, configure responds to certain environment +variables, as described in . +These provide additional ways to customize the configuration. + + + +
Cleaning up some remarkably old stuff in installation.sgml
While poking at the configure options list, I noticed that there is some other text in installation.sgml that is long past its sell-by date. For instance, the first para in install-requirements points to standalone FAQ documents that we removed back in 8.4. I don't think we need to have blow-by-blow discussions of decades-old AIX or HPUX bugs either; nor recommendations to use gcc 2.95.3 ;-). The HPUX section actually seems totally unnecessary after removing info that is obsolete or duplicative. Proposed patch attached. regards, tom lane diff --git a/doc/src/sgml/installation.sgml b/doc/src/sgml/installation.sgml index 4493862..ff98fe1 100644 --- a/doc/src/sgml/installation.sgml +++ b/doc/src/sgml/installation.sgml @@ -54,10 +54,11 @@ su - postgres In general, a modern Unix-compatible platform should be able to run PostgreSQL. The platforms that had received specific testing at the - time of release are listed in - below. In the doc subdirectory of the distribution - there are several platform-specific FAQ documents you - might wish to consult if you are having trouble. + time of release are described in + below. Modern versions of Windows are also supported, but this chapter + does not cover installation on + Windows (see + ). @@ -1986,175 +1987,11 @@ export MANPATH -PostgreSQL works on AIX, but getting it installed properly can be -challenging. AIX versions from 4.3.3 to 6.1 are considered supported. -You can use GCC or the native IBM compiler xlc. In -general, using recent versions of AIX and PostgreSQL helps. Check -the build farm for up to date information about which versions of -AIX are known to work. +PostgreSQL works on AIX, but AIX versions before about 6.1 have +various issues and are not recommended. +You can use GCC or the native IBM compiler xlc. - -The minimum recommended fix levels for supported AIX versions are: - - - - - AIX 4.3.3 - Maintenance Level 11 + post ML11 bundle - - - - AIX 5.1 - Maintenance Level 9 + post ML9 bundle - - - - AIX 5.2 - Technology Level 10 Service Pack 3 - - - - AIX 5.3 - Technology Level 7 - - - - AIX 6.1 - Base Level - - - - -To check your current fix level, use -oslevel -r in AIX 4.3.3 to AIX 5.2 ML 7, or -oslevel -s in later versions. - - - -Use the following configure flags in addition -to your own if you have installed Readline or libz in -/usr/local: ---with-includes=/usr/local/include ---with-libraries=/usr/local/lib. - - - -GCC Issues - - - On AIX 5.3, there have been some problems getting PostgreSQL to - compile and run using GCC. - - - - You will want to use a version of GCC subsequent to 3.3.2, - particularly if you use a prepackaged version. We had good - success with 4.0.1. Problems with earlier versions seem to have - more to do with the way IBM packaged GCC than with actual issues - with GCC, so that if you compile GCC yourself, you might well - have success with an earlier version of GCC. - - - - -Unix-Domain Sockets Broken - - - AIX 5.3 has a problem - where sockaddr_storage is not defined to - be large enough. In version 5.3, IBM increased the size of - sockaddr_un, the address structure for - Unix-domain sockets, but did not correspondingly increase the - size of sockaddr_storage. The result of - this is that attempts to use Unix-domain sockets with PostgreSQL - lead to libpq overflowing the data structure. TCP/IP connections - work OK, but not Unix-domain sockets, which prevents the - regression tests from working. - - - - The problem was reported to IBM, and is recorded as bug report - PMR29657. If you upgrade to maintenance level 5300-03 or later, - that will include this fix. A quick workaround - is to alter _SS_MAXSIZE to 1025 in - /usr/include/sys/socket.h. In either case, - recompile PostgreSQL once you have the corrected header file. - - - - -Internet Address Issues - - - PostgreSQL relies on the system's getaddrinfo function - to parse IP addresses in listen_addresses, - pg_hba.conf, etc. Older versions of AIX have assorted - bugs in this function. If you have problems related to these settings, - updating to the appropriate AIX fix level shown above - should take care of it. - - - - - - One user reports: - - - - When implementing PostgreSQL version 8.1 on AIX 5.3, we - periodically ran into problems where the statistics collector - would mysteriously not come up successfully. This - appears to be the result of unexpected behavior in the IPv6 - implementation. It looks like PostgreSQL and IPv6 do not play - very well together on AIX
