[BUGS] pg_dumpall bug & feature request
Hello, Perhaps you missed my previous message. pg_dumpall has not -f command line option to specify output file - it always send output to standart output. But there is an issue with it in Windows (I described it earlier) - function's line endings are changed from 0D 0A to 0D 0D 0A. Could you please add -f option to pg_dumpall, because it's very inconvenient to replace these character sequences in hex editor all the time. Thank you for support! -- Best regards, Ivan mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] pg_dumpall bug & feature request
Ivan wrote: Hi, > Perhaps you missed my previous message. > pg_dumpall has not -f command line option > to specify output file - it always send output > to standart output. But there is an issue with > it in Windows (I described it earlier) - function's > line endings are changed from 0D 0A to 0D 0D 0A. Hmm, what do you need that for? Why don't you directly restore the binary dump to a database by using option -d? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2018: segfaults with some german errormessages
The following bug has been logged online: Bug reference: 2018 Logged by: Bernhard Weisshuhn Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1rc1 Operating system: Linux Description:segfaults with some german errormessages Details: Some errormessages from backend/po/de.po use format strings with parameters that are not set. This leads to segfaults when this error is attempted to be logged. At first glance I found 4 occurences, but there might be more lurking. Probably we should have a perl script that checks whether all format strings off the translations match. These are the ones that I found, I hope the webform doesn't mess up the formatting too much. I'll also send this to the translator via email. --- postgresql-8.1RC1/src/backend/po/de.po.orig +++ postgresql-8.1RC1/src/backend/po/de.po @@ -8838,22 +8838,22 @@ msgstr "ungültige Eingabesyntax für Typ #: utils/adt/float.c:219 msgid "type \"real\" value out of range: overflow" -msgstr "Wert %s ist auÃerhalb des gültigen Bereiches für Typ »real«: Ãberlauf" +msgstr "Wert ist auÃerhalb des gültigen Bereiches für Typ »real«: Ãberlauf" #: utils/adt/float.c:223 msgid "type \"real\" value out of range: underflow" -msgstr "Wert %s ist auÃerhalb des gültigen Bereiches für Typ »real«: Unterlauf"+msgstr "Wert ist auÃerhalb des gültigen Bereiches für Typ »real«: Unterlauf" #: utils/adt/float.c:238 msgid "type \"double precision\" value out of range: overflow" msgstr "" -"Wert %s ist auÃerhalb des gültigen Bereiches für Typ »double precision«: " +"Wert ist auÃerhalb des gültigen Bereiches für Typ »double precision«: " "Ãberlauf" #: utils/adt/float.c:242 msgid "type \"double precision\" value out of range: underflow" msgstr "" -"Wert %s ist auÃerhalb des gültigen Bereiches für Typ »double precision«: " +"Wert ist auÃerhalb des gültigen Bereiches für Typ »double precision«: " "Unterlauf" #: utils/adt/float.c:274 utils/adt/float.c:315 utils/adt/float.c:339 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2017: column labels ignored on selects from views
The following bug has been logged online: Bug reference: 2017 Logged by: Jolly Chen Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1RC1 Operating system: Mac OS X 10.4.3 Description:column labels ignored on selects from views Details: Note how the column labels work for v2 but not for v1. This problem causes columns to be mistakenly missing in JDBC ResultSets. testdb=# create table foo (f1 integer); CREATE TABLE testdb=# insert into foo values (1); INSERT 0 1 testdb=# insert into foo values (2); INSERT 0 1 testdb=# create view v1 as select sum(f1) as sum from foo; CREATE VIEW testdb=# select * from v1; sum - 3 (1 row) testdb=# select sum as s from v1; sum - 3 (1 row) testdb=# create view v2 as select f1*10 as tenX from foo; CREATE VIEW testdb=# select * from v2; tenx -- 10 20 (2 rows) testdb=# select tenX as t from v2; t 10 20 (2 rows) testdb=# select version(); version --- PostgreSQL 8.1RC1 on powerpc-apple-darwin8.3.0, compiled by GCC powerpc-apple-darwin8-gcc-4.0.0 (GCC) 4.0.0 (Apple Computer, Inc. build 5026)(1 row) testdb=# ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] pg_dumpall bug & feature request
Hello Alvaro, Thursday, November 3, 2005, 3:28:41 PM, you wrote: AH> Ivan wrote: AH> Hi, >> Perhaps you missed my previous message. >> pg_dumpall has not -f command line option >> to specify output file - it always send output >> to standart output. But there is an issue with >> it in Windows (I described it earlier) - function's >> line endings are changed from 0D 0A to 0D 0D 0A. AH> Hmm, what do you need that for? Why don't you directly restore the AH> binary dump to a database by using option -d? I don't need to restore binary dump. I need to make a dump of the whole database cluster to plain sql file (what pg_dumpall does). So I have to redirect pg_dumpall's output to a file pg_dump [options] > cluster_dump.sql When I do it on Windows, it creates cluster_dump.sql file BUT in that file all sql and plpgsql function bodies are corrupted! - each line in the body ends with 0D 0D 0A sequence instead of 0D 0A - therefor functions after restoring looks like: CREATE OR REPLACE FUNCTION "foo"() RETURNS bar AS $$ select * from "bar" where bla-bla order by bla-bla; $$ LANGUAGE 'sql' VOLATILE; instead of CREATE OR REPLACE FUNCTION "foo"() RETURNS bar AS $$ select * from "bar" where bla-bla order by bla-bla; $$ LANGUAGE 'sql' VOLATILE; - extra blank lines are added. So it would be great if the output redirection ">" will be workaround for example the same way like in pg_dump it is done - via -f file option. Thank you for support. -- Best regards, Ivanmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Failed CLUSTER seems to leave files behind
Antti Salmela <[EMAIL PROTECTED]> writes: > Failed CLUSTER due to insufficient disk space seems to leave temporary files > behind at least on 7.4.7. What was the "failure" exactly? If you ran out of disk space for the data files, I'd have expected it to reclaim the temp files. On the other hand, running out of disk space for WAL would lead to a database-wide PANIC, and there's no mechanism for getting rid of unreferenced files after a PANIC. (IIRC this is intentional --- deleting files that we think aren't used seems too risky.) This is one of several reasons for keeping WAL and data on separate disks ... > Is it safe to remove these files manually? Yeah, if you're sure they correspond to no pg_class.relfilenode entry. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2017: column labels ignored on selects from views
"Jolly Chen" <[EMAIL PROTECTED]> writes: > testdb=# select sum as s from v1; > sum > - >3 > (1 row) Good catch. 8.0 and earlier get this right, so it's a regression we induced somewhere in 8.1 development ... wonder where? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] pg_dumpall bug & feature request
Ivan wrote: Hi, > Thursday, November 3, 2005, 3:28:41 PM, you wrote: > >> Perhaps you missed my previous message. > >> pg_dumpall has not -f command line option > >> to specify output file - it always send output > >> to standart output. But there is an issue with > >> it in Windows (I described it earlier) - function's > >> line endings are changed from 0D 0A to 0D 0D 0A. > > AH> Hmm, what do you need that for? Why don't you directly restore the > AH> binary dump to a database by using option -d? > > I don't need to restore binary dump. I need to make a dump of the > whole database cluster to plain sql file (what pg_dumpall does). > So I have to redirect pg_dumpall's output to a file > pg_dump [options] > cluster_dump.sql Hum, doh! I misread your message as thought it referred to pg_restore rather than pg_dumpall. Not sure why. Certainly pg_dumpall needs a redirecting feature. I imagine there must be a way to tell the shell not to do newline translation, but I wouldn't know it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] Failed CLUSTER seems to leave files behind
On Thu, Nov 03, 2005 at 10:03:16AM -0500, Tom Lane wrote: > Antti Salmela <[EMAIL PROTECTED]> writes: > > Failed CLUSTER due to insufficient disk space seems to leave temporary files > > behind at least on 7.4.7. > > What was the "failure" exactly? If you ran out of disk space for the > data files, I'd have expected it to reclaim the temp files. On the > other hand, running out of disk space for WAL would lead to a > database-wide PANIC, and there's no mechanism for getting rid of > unreferenced files after a PANIC. (IIRC this is intentional --- > deleting files that we think aren't used seems too risky.) PANIC'ed. > This is one of several reasons for keeping WAL and data on separate > disks ... Now I know better.. > > Is it safe to remove these files manually? > > Yeah, if you're sure they correspond to no pg_class.relfilenode entry. Thanks. -- Antti Salmela ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2017: column labels ignored on selects from views
"Jolly Chen" <[EMAIL PROTECTED]> writes: > Description:column labels ignored on selects from views Fixed --- turns out the problem was in some recently-added code to eliminate unnecessary SubqueryScan nodes. The resnames attached to the SubqueryScan targetlist items have to be moved down to its child node, not just dropped. Thanks for the report! regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[BUGS] attislocal value changed with dump
The problem is that after a dump and reload of
a table hierarchy there are different values in
pg_attribute.attislocal.
A quick grep shows few references to attislocal.
But I cannot say for sure it is unused since it is
documented. However, I'm looking at a db diff
tool and there it does matter.
This is the setup:
I've got an inheritance hierarchy, answer_values
with a bunch of answer_[type] tables inheriting
from it.
tiny=# \d answer_values
Table "public.answer_values"
Column | Type |Modifiers
+-+-
orid | integer | not null
qid| integer | not null
avid | integer | not null default
nextval('public.answer_values_avid_seq'::text)
atype | text|
ncols | integer |
Indexes:
"answer_values_pkey" PRIMARY KEY, btree (orid, qid, avid)
tiny=# \d answer_text
Table "public.answer_text"
Column | Type |Modifiers
+-+--
orid | integer | not null
qid| integer | not null
avid | integer | not null default nextval('answer_values_avid_seq'::text)
atype | text|
ncols | integer |
avalue | text|
Indexes:
"answer_text_pk" UNIQUE, btree (avid)
Inherits: answer_values
* In 8.0.4,
* created a clean db (tiny) and loaded the SQL to define the hierarchy.
* pg_dump tiny > tiny.dat
* createdb tiny2
* psql < tiny.dat
After this, looking at the 'avid' attribute in the
pg_attribute table all of tables loaded in the second
db have 't' for attislocal. This is different from
the original definition.
tiny=# select r.relname, attname, attislocal, attinhcount from pg_attribute a
join pg_class r ON (a.attrelid = r.oid) where attname = 'avid' order by relname;
relname| attname | attislocal | attinhcount
--+-++-
answer_addr | avid| f | 1
answer_addr_pk | avid| t | 0
answer_bool_pk | avid| t | 0
answer_boolean | avid| f | 1
answer_date | avid| f | 1
answer_date_pk | avid| t | 0
answer_date_range| avid| f | 1
answer_dater_pk | avid| t | 0
answer_float | avid| f | 1
answer_flt_pk| avid| t | 0
answer_num_pk| avid| t | 0
answer_numeric | avid| f | 1
answer_numeric_range | avid| f | 1
answer_numr_pk | avid| t | 0
answer_text | avid| f | 1
answer_text_pk | avid| t | 0
answer_values| avid| t | 0
answer_values_pkey | avid| t | 0
av_v | avid| t | 0
(19 rows)
tiny2=# select r.relname, attname, attislocal, attinhcount from pg_attribute a
join pg_class r ON (a.attrelid = r.oid) where attname = 'avid' order by relname;
relname| attname | attislocal | attinhcount
--+-++-
answer_addr | avid| t | 1
answer_addr_pk | avid| t | 0
answer_bool_pk | avid| t | 0
answer_boolean | avid| t | 1
answer_date | avid| t | 1
answer_date_pk | avid| t | 0
answer_date_range| avid| t | 1
answer_dater_pk | avid| t | 0
answer_float | avid| t | 1
answer_flt_pk| avid| t | 0
answer_num_pk| avid| t | 0
answer_numeric | avid| t | 1
answer_numeric_range | avid| t | 1
answer_numr_pk | avid| t | 0
answer_text | avid| t | 1
answer_text_pk | avid| t | 0
answer_values| avid| t | 0
answer_values_pkey | avid| t | 0
av_v | avid| t | 0
(19 rows)
- End forwarded message -
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] attislocal value changed with dump
Hi Elein, elein wrote: > The problem is that after a dump and reload of > a table hierarchy there are different values in > pg_attribute.attislocal. > > A quick grep shows few references to attislocal. > But I cannot say for sure it is unused since it is > documented. However, I'm looking at a db diff > tool and there it does matter. It's not unused, though it's not a hot spot (it's only used to prevent you from dropping an inherited column). I can't reproduce your problem here though -- the dump comes out just like the tables I produced. Care to provide the original SQL script? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] is this a bug or do I not understand the query planner?
two almost identical queries: one searches for
read in ('N','n')
and the other searches for
read in ('Y','y').
the (explain) SQL statement says that one uses the
index on the (read) field and the other does a
sequential table scan. Why!!! I can think of no
logical reason for this behavior.
usenet=# \d+ article
Table "public.article"
Column | Type | Modifiers
| Description
-++--+-
msg | integer| not null
|
thedate | date | not null
|
subject | character varying(300) |
|
lines | integer| not null default 0
|
read| character(1) | not null default
'N'::bpchar |
ng | integer| not null default 0
|
author | integer| not null default 0
|
Indexes:
"article_pkey" PRIMARY KEY, btree (msg)
"article_read" btree ("read")
Has OIDs: yes
usenet=# explain select * from article where read in
('Y','y');
QUERY PLAN
Index Scan using article_read, article_read on
article (cost=0.00..4.03 rows=1 width=107)
Index Cond: (("read" = 'Y'::bpchar) OR ("read" =
'y'::bpchar))
(2 rows)
usenet=# explain select * from article where read in
('N','n');
QUERY PLAN
---
Seq Scan on article (cost=0.00..68661.02
rows=2018135 width=107)
Filter: (("read" = 'N'::bpchar) OR ("read" =
'n'::bpchar))
(2 rows)
__
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] is this a bug or do I not understand the query planner?
"Rob Prowel" <[EMAIL PROTECTED]> wrote
> two almost identical queries: one searches for
>
> read in ('N','n')
>
> and the other searches for
>
> read in ('Y','y').
>
> the (explain) SQL statement says that one uses the
> index on the (read) field and the other does a
> sequential table scan. Why!!!
With big chance, it is not a bug. As your explain indicates, Yy query is
estimated only return 1 row while Nn query is estimated to return 2018135
rows. So for the latter, compared to your table size(I guess), an seqscan is
more suitable.
Try "explain analyze" to see if the query optimizer gets a right guess. If
not, run "vacuum full analyze", then check again. If still not, you may want
to post the results here.
Regards,
Qingqing
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] is this a bug or do I not understand the query planner?
Rob Prowel wrote:
> two almost identical queries: one searches for
> read in ('N','n')
> and the other searches for
> read in ('Y','y').
>
> the (explain) SQL statement says that one uses the
> index on the (read) field and the other does a
> sequential table scan. Why!!! I can think of no
> logical reason for this behavior.
Imagine a table with one million 'Y' and one 'N'. Searching for the
first should not use an index, searching for the second should. A
similar case probably applies here. I hope that is logical enough for
you. :)
In general, if you disagree with a plan choice, you should provide
evidence that the chosen plan is in practice worse then the one you
would have preferred.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[BUGS] BUG #2019: tsearch2-related coredump
The following bug has been logged online: Bug reference: 2019 Logged by: Bernhard Weisshuhn Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1rc1 Operating system: Linux Fedora Core 4 x86_64 Description:tsearch2-related coredump Details: We could not yet reproduce the following coredump, but we keep trying. If you need more info I'll be happy to supply more details. Unfortunately we could not yet get hold of the full insert that caused it. Most likely it was an insert into a table that has triggers to update another table that holds tsearch2 vectors, which are indexed with a tsearch2-gist index. [EMAIL PROTECTED] gdb /usr/bin/postmaster /var/lib/pgsql/data/core.18131 GNU gdb Red Hat Linux (6.3.0.0-1.84rh) Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type "show copying" to see the conditions. There is absolutely no warranty for GDB. Type "show warranty" for details. This GDB was configured as "x86_64-redhat-linux-gnu"...Using host libthread_db library "/lib64/libthread_db.so.1". Core was generated by `postgres: smg_cms smg 62.8.240.222(52138) INSERT '. Program terminated with signal 11, Segmentation fault. Reading symbols from /lib64/libpam.so.0...done. Loaded symbols for /lib64/libpam.so.0 Reading symbols from /lib64/libssl.so.5...done. Loaded symbols for /lib64/libssl.so.5 Reading symbols from /lib64/libcrypto.so.5...done. Loaded symbols for /lib64/libcrypto.so.5 Reading symbols from /usr/lib64/libkrb5.so.3...done. Loaded symbols for /usr/lib64/libkrb5.so.3 Reading symbols from /usr/lib64/libz.so.1...done. Loaded symbols for /usr/lib64/libz.so.1 Reading symbols from /usr/lib64/libreadline.so.5...done. Loaded symbols for /usr/lib64/libreadline.so.5 Reading symbols from /lib64/libtermcap.so.2...done. Loaded symbols for /lib64/libtermcap.so.2 Reading symbols from /lib64/libcrypt.so.1...done. Loaded symbols for /lib64/libcrypt.so.1 Reading symbols from /lib64/libresolv.so.2...done. Loaded symbols for /lib64/libresolv.so.2 Reading symbols from /lib64/libnsl.so.1...done. Loaded symbols for /lib64/libnsl.so.1 Reading symbols from /lib64/libdl.so.2...done. Loaded symbols for /lib64/libdl.so.2 Reading symbols from /lib64/libm.so.6...done. Loaded symbols for /lib64/libm.so.6 Reading symbols from /lib64/libc.so.6...done. Loaded symbols for /lib64/libc.so.6 Reading symbols from /lib64/libcom_err.so.2...done. Loaded symbols for /lib64/libcom_err.so.2 Reading symbols from /lib64/libaudit.so.0...done. Loaded symbols for /lib64/libaudit.so.0 Reading symbols from /usr/lib64/libgssapi_krb5.so.2...done. Loaded symbols for /usr/lib64/libgssapi_krb5.so.2 Reading symbols from /usr/lib64/libk5crypto.so.3...done. Loaded symbols for /usr/lib64/libk5crypto.so.3 Reading symbols from /lib64/ld-linux-x86-64.so.2...done. Loaded symbols for /lib64/ld-linux-x86-64.so.2 Reading symbols from /usr/lib64/libkrb5support.so.0...done. Loaded symbols for /usr/lib64/libkrb5support.so.0 Reading symbols from /lib64/libnss_files.so.2...done. Loaded symbols for /lib64/libnss_files.so.2 Reading symbols from /usr/lib64/pgsql/plpgsql.so...done. Loaded symbols for /usr/lib64/pgsql/plpgsql.so Reading symbols from /usr/lib64/pgsql/tsearch2.so...done. Loaded symbols for /usr/lib64/pgsql/tsearch2.so #0 RS_compile (r=0x12d5e78, issuffix=101, str=0x12adb70 "e") at regis.c:116 116 ptr = ptr->next; (gdb) bt #0 RS_compile (r=0x12d5e78, issuffix=101, str=0x12adb70 "e") at regis.c:116 #1 0x2aaabe56cf82 in CheckAffix ( word=0xa1dd88 "wto-subventionsübereinkommens", len=29, Affix=0x12d5e58, flagflags=Variable "flagflags" is not available. ) at spell.c:782 #2 0x2aaabe56d1c6 in NormalizeSubWord (Conf=0x92d9e8, word=0xa1dd88 "wto-subventionsübereinkommens", flag=Variable "flag" is not available. ) at spell.c:905 #3 0x2aaabe56d9f6 in NINormalizeWord (Conf=0x92d9e8, word=0xa1dd88 "wto-subventionsübereinkommens") at spell.c:1130 #4 0x2aaabe55ac80 in spell_lexize (fcinfo=Variable "fcinfo" is not available. ) at dict_ispell.c:168 #5 0x00606d69 in FunctionCall3 (flinfo=Variable "flinfo" is not available. ) at fmgr.c:1179 #6 0x2aaabe55dd0d in parsetext_v2 (cfg=0x93c860, prs=0x7fffa260, buf=Variable "buf" is not available. ) at ts_cfg.c:328 #7 0x2aaabe55fa7b in to_tsvector (fcinfo=0x7fffa2b0) at tsvector.c:737 #8 0x0060666e in DirectFunctionCall3 ( func=0x2aaabe55f9e0 , arg1=Variable "arg1" is not available. ) at fmgr.c:905 #9 0x00505643 in ExecMakeFunctionResult (fcache=0x99ef68, econtext=0x99de48, isNull=0x7fffae80 "\200", isDone=0x7fffaae4) at execQual.c:1096 #10 0x00505346 in ExecEvalFuncArgs (fcinfo=0x7fffab40, argList=Variable "argList" is not available. ) at execQual.c:856 #11 0x00505471 in ExecMakeFunctionResult (fcache=0
