[BUGS] BUG #4461: Should avoid create tblspcs in system location
The following bug has been logged online: Bug reference: 4461 Logged by: Dmitry Orlov Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3 Operating system: Ubuntu Hardy 8.04.1 Description:Should avoid create tblspcs in system location Details: if I issue create tablespace user_ts location '/var/lib/postgresql/8.3/main/pg_tblspc' then appears nested links in /var/lib/postgresql/8.3/main/pg_tblspc. So should avoid to create tablespaces in pg_tblspc [EMAIL PROTECTED]:/home/dorlov/tmp/pgpool-II-2.1$ uname -a&&pg_config Linux dorlov-laptop 2.6.24-19-rt #1 SMP PREEMPT RT Thu Aug 21 02:08:03 UTC 2008 i686 GNU/Linux BINDIR = /usr/lib/postgresql/8.3/bin DOCDIR = /usr/share/doc/postgresql-doc-8.3 INCLUDEDIR = /usr/include/postgresql PKGINCLUDEDIR = /usr/include/postgresql INCLUDEDIR-SERVER = /usr/include/postgresql/8.3/server LIBDIR = /usr/lib PKGLIBDIR = /usr/lib/postgresql/8.3/lib LOCALEDIR = /usr/share/locale MANDIR = /usr/share/postgresql/8.3/man SHAREDIR = /usr/share/postgresql/8.3 SYSCONFDIR = /etc/postgresql-common PGXS = /usr/lib/postgresql/8.3/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE = '--build=i486-linux-gnu' '--prefix=/usr' '--includedir=/usr/include' '--mandir=/usr/share/man' '--infodir=/usr/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--libexecdir=/usr/lib/postgresql-8.3' '--disable-maintainer-mode' '--disable-dependency-tracking' '--srcdir=.' '--mandir=/usr/share/postgresql/8.3/man' '--with-docdir=/usr/share/doc/postgresql-doc-8.3' '--sysconfdir=/etc/postgresql-common' '--datadir=/usr/share/postgresql/8.3' '--bindir=/usr/lib/postgresql/8.3/bin' '--includedir=/usr/include/postgresql/' '--enable-nls' '--enable-integer-datetimes' '--enable-thread-safety' '--enable-debug' '--disable-rpath' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-krb5' '--with-gssapi' '--with-openssl' '--with-libxml' '--with-libxslt' '--with-ldap' '--with-ossp-uuid' '--with-gnu-ld' '--with-tclconfig=/usr/lib/tcl8.4' '--with-tkconfig=/usr/lib/tk8.4' '--with-includes=/usr/include/tcl8.4' '--with-system-tzdata=/usr/share/zoneinfo' '--with-pgport=5432' 'CFLAGS=-g -O2 -g -Wall -O2 -fPIC' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,--as-needed' 'CC=cc' 'CPPFLAGS=' 'build_alias=i486-linux-gnu' CC = cc CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.4 CFLAGS = -g -O2 -g -Wall -O2 -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g CFLAGS_SL = -fpic LDFLAGS = -Wl,-Bsymbolic-functions -Wl,--as-needed LDFLAGS_SL = LIBS = -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto -lkrb5 -lcom_err -lgssapi_krb5 -lz -lreadline -lcrypt -ldl -lm VERSION = PostgreSQL 8.3.3 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Locale (unsupported) bug. uk_UA.KOI8-U
ivocs admin wrote: initdb -E ISO-8859-1 -D data/ This is probably not a good idea. You should specify a locale and let initdb figure out the matching encoding. Otherwise you might end up with an incompatible locale/encoding combination. (initdb would probably not even allow this to proceed, but you have other problems as well, see below.) The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale uk_UA.KOI8-U. could not determine encoding for locale "uk_UA.KOI8-U": codeset is "KOI8-U" initdb: could not find suitable text search configuration for locale uk_UA.KOI8-U PostgreSQL currently doesn't support the KOI8-U encoding. You could consider using UTF-8 instead. Try running this initdb --locale=uk_UA.utf8 -D data/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Locale (unsupported) bug. uk_UA.KOI8-U
initdb -E ISO-8859-1 -D data/ The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale uk_UA.KOI8-U. could not determine encoding for locale "uk_UA.KOI8-U": codeset is "KOI8-U" initdb: could not find suitable text search configuration for locale uk_UA.KOI8-U The default text search configuration will be set to "simple". fixing permissions on existing directory data ... ok creating subdirectories ... ok selecting default max_connections ... 100 selecting default shared_buffers/max_fsm_pages ... 24MB/153600 creating configuration files ... ok creating template1 database in data/base/1 ... ok initializing pg_authid ... ok initializing dependencies ... ok creating system views ... ok loading system objects' descriptions ... ok creating conversions ... ok creating dictionaries ... ok setting privileges on built-in objects ... ok creating information schema ... ok vacuuming database template1 ... ok copying template1 to template0 ... WARNING: could not determine encoding for locale "uk_UA.KOI8-U": codeset is "KOI8-U" DETAIL: Please report this to . ok copying template1 to postgres ... WARNING: could not determine encoding for locale "uk_UA.KOI8-U": codeset is "KOI8-U" DETAIL: Please report this to . ok WARNING: enabling "trust" authentication for local connections You can change this by editing pg_hba.conf or using the -A option the next time you run initdb. Success. You can now start the database server using: postgres -D data -- WBR, Bohdan Turkynewych iVOCS.com System Administrator -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Locale (unsupported) bug. uk_UA.KOI8-U
Peter Eisentraut <[EMAIL PROTECTED]> writes: > ivocs admin wrote: >> The database cluster will be initialized with locale uk_UA.KOI8-U. >> could not determine encoding for locale "uk_UA.KOI8-U": codeset is "KOI8-U" >> initdb: could not find suitable text search configuration for locale >> uk_UA.KOI8-U > PostgreSQL currently doesn't support the KOI8-U encoding. You could > consider using UTF-8 instead. Try running this > initdb --locale=uk_UA.utf8 -D data/ It's still not going to find a suitable text search configuration, since we haven't got anything for Ukranian ... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4462: Adding COUNT to query causes massive slowdown
Jussi Pakkanen wrote:
However when I try to count the amount of distinct codes, I get this:
EXPLAIN SELECT COUNT(DISTINCT code) FROM log;
QUERY PLAN
-
Aggregate (cost=100801488.30..100801488.31 rows=1 width=10)
-> Seq Scan on log (cost=1.00..100721245.24 rows=32097224
width=10)
(2 rows)
This looks like you have one of the enable_${plantype} parameters turned
off. 1 is the penalty that is added when a plantype if turned off.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4461: Should avoid create tblspcs in system location
On Thu, 09 Oct 2008 14:06:35 +0200, Peter Eisentraut <[EMAIL PROTECTED]> wrote: If this is the location you give it, why should it avoid creating tablespaces there? I believe this is called "making things `idiot-proof'". This is the direction Microsoft's software takes, and I wouldn't be really happy to see that in PG. This would eventually lead to limitations for PG's advanced users (well, maybe not in this particular case), disallowing them to do things, because the software "thinks it's wrong". On the other hand - initdb has several "sanity" checks if I'm not mistaken. -- ru -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4461: Should avoid create tblspcs in system location
Dmitry Orlov wrote: if I issue create tablespace user_ts location '/var/lib/postgresql/8.3/main/pg_tblspc' then appears nested links in /var/lib/postgresql/8.3/main/pg_tblspc. So should avoid to create tablespaces in pg_tblspc If this is the location you give it, why should it avoid creating tablespaces there? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4462: Adding COUNT to query causes massive slowdown
The following bug has been logged online: Bug reference: 4462 Logged by: Jussi Pakkanen Email address: [EMAIL PROTECTED] PostgreSQL version: 8.3.3 Operating system: Ubuntu x86 8/04 Description:Adding COUNT to query causes massive slowdown Details: I have a table in the following format code CHARACTER(9) NOT NULL text VARCHAR(200) I have built an INDEX on "code", VACUUMed and ANALYZEd the table. I have about 32 million rows and roughly 200 000 unique "code" elements. I determine the unique codes using the following SQL query: EXPLAIN SELECT DISTINCT code FROM log; QUERY PLAN --- Unique (cost=0.00..1384173.89 rows=6393 width=10) -> Index Scan using codeindex on log (cost=0.00..1303930.83 rows=32097224 width=10) (2 rows) This takes about 4 minutes (it's a slow machine) but pretty much works as expected. However when I try to count the amount of distinct codes, I get this: EXPLAIN SELECT COUNT(DISTINCT code) FROM log; QUERY PLAN - Aggregate (cost=100801488.30..100801488.31 rows=1 width=10) -> Seq Scan on log (cost=1.00..100721245.24 rows=32097224 width=10) (2 rows) For some reason PostgreSQL wants to do a full table scan in this case. This takes over 11 minutes. Transferring the result set from the first query to a Python client program and calculating the lines there takes about 4 seconds. This makes pg over 100 times slower than the naive implementation. If I do the same COUNT using a view, it uses the index and is fast: CREATE VIEW distcode AS SELECT DISTINCT code FROM log; EXPLAIN SELECT COUNT(*) FROM distcode; QUERY PLAN - Aggregate (cost=1384253.81..1384253.82 rows=1 width=0) -> Unique (cost=0.00..1384173.89 rows=6393 width=10) -> Index Scan using codeindex on log (cost=0.00..1303930.83 rows=320972 I tried setting seq_scan to off. It did not help. Due to reasons beyond my control, I can't test version 8.3.4 until the next Ubuntu is released (at the end of this month). -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4462: Adding COUNT to query causes massive slowdown
On Thu, Oct 9, 2008 at 3:05 PM, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> Jussi Pakkanen wrote:
>>
>> However when I try to count the amount of distinct codes, I get this:
>>
>>
>> EXPLAIN SELECT COUNT(DISTINCT code) FROM log;
>> QUERY PLAN
>>
>>
>> -
>> Aggregate (cost=100801488.30..100801488.31 rows=1 width=10)
>> -> Seq Scan on log (cost=1.00..100721245.24 rows=32097224
>> width=10)
>> (2 rows)
>
> This looks like you have one of the enable_${plantype} parameters turned
> off. 1 is the penalty that is added when a plantype if turned off.
This was caused by enable_seqscan. When I set it to 'on', the penalty
disappears but it still does the full table scan.
Given that PostgreSQL does the scan even with the huge seqscan
penalty, I can think of only two different causes:
1) some sort of a bug in the query analyzer
2) SELECT COUNT(DISTINCT x) for some reason requires information that
is not available in the index. The only one I could think of would be
NULL values, but the 'code' field is defined as 'NOT NULL'.
Also I had a small error in my original but report. There are 2
million distinct values of 'code', rather than 200 000.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4462: Adding COUNT to query causes massive slowdown
"Jussi Pakkanen" <[EMAIL PROTECTED]> writes: > Given that PostgreSQL does the scan even with the huge seqscan > penalty, I can think of only two different causes: > 1) some sort of a bug in the query analyzer > 2) SELECT COUNT(DISTINCT x) for some reason requires information that > is not available in the index. Try (3) COUNT(DISTINCT x) ... or any DISTINCT aggregate for that matter ... is implemented by a sort-and-uniq step inside the aggregate function itself. You can't see it in the plan. I wouldn't actually think that this approach would be slower than an indexscan, btw, unless maybe the index were very nearly correlated with physical order --- but that would make the sort more efficient, too. Perhaps you need to raise work_mem enough to allow the sort to take place without spilling to disk? (Turning on trace_sort should let you see what's happening there.) regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Locale (unsupported) bug. uk_UA.KOI8-U
Tom Lane wrote: Peter Eisentraut <[EMAIL PROTECTED]> writes: ivocs admin wrote: The database cluster will be initialized with locale uk_UA.KOI8-U. could not determine encoding for locale "uk_UA.KOI8-U": codeset is "KOI8-U" initdb: could not find suitable text search configuration for locale uk_UA.KOI8-U PostgreSQL currently doesn't support the KOI8-U encoding. You could consider using UTF-8 instead. Try running this initdb --locale=uk_UA.utf8 -D data/ It's still not going to find a suitable text search configuration, since we haven't got anything for Ukranian ... But initdb will still succeed, so if you don't need the text search feature, you can proceed. If you do need text search, you can possibly get away with using the configuration for russian (see initdb -T option). Do let us know if we need to make any adjustments to support Ukrainian better. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
