Re: [HACKERS] WIP: SP-GiST, Space-Partitioned GiST
Attached is updated SP-GiST patch, which provides full logging support and fixed several bugs (Thanks ALexander Korotkov for help). Regards, Oleg On Thu, 1 Sep 2011, Oleg Bartunov wrote: This is updates SP-GiST patch, which fixed one bug and replaced test to the locale independent one. On Wed, 31 Aug 2011, Oleg Bartunov wrote: Hi there, attached is our WIP-patch for 9.2 development source tree, which provides implementation of SP-GiST (prototype was presented at PGCon-2011, see http://www.pgcon.org/2011/schedule/events/309.en.html and presentation for details) as a core feature. Main differences from prototype version: 1. Now it's part of pg core, not contrib module 2. It provides more operations for quadtree and suffix tree 3. It uses clustering algorithm of nodes on disk and has much better utilization of disk space. Fillfactor is supported 4. Some corner cases were eliminated 5. It provides support for concurency and recovery (inserts are logged, supports for deletes, and log replay will be added really soon) So, now code contains almost all possible overhead of production code and we ask hackers to test performance on real data sets. We expect the same performance for random data (since almost no overlaps) and much better performance on real-life data, plus much better index creation time. Also, we appreciate your comments and suggestions about API. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 spgist_patch-0.89.gz Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: SP-GiST, Space-Partitioned GiST
I attached wrong patch in previous message, sorry ! Here is a last version. This is a new WIP of SP-GiST patch, which provides support for: 1. Concurrent vacuum 2. Vacuum logging 3. WAL replay Oleg On Thu, 1 Sep 2011, Oleg Bartunov wrote: This is updates SP-GiST patch, which fixed one bug and replaced test to the locale independent one. On Wed, 31 Aug 2011, Oleg Bartunov wrote: Hi there, attached is our WIP-patch for 9.2 development source tree, which provides implementation of SP-GiST (prototype was presented at PGCon-2011, see http://www.pgcon.org/2011/schedule/events/309.en.html and presentation for details) as a core feature. Main differences from prototype version: 1. Now it's part of pg core, not contrib module 2. It provides more operations for quadtree and suffix tree 3. It uses clustering algorithm of nodes on disk and has much better utilization of disk space. Fillfactor is supported 4. Some corner cases were eliminated 5. It provides support for concurency and recovery (inserts are logged, supports for deletes, and log replay will be added really soon) So, now code contains almost all possible overhead of production code and we ask hackers to test performance on real data sets. We expect the same performance for random data (since almost no overlaps) and much better performance on real-life data, plus much better index creation time. Also, we appreciate your comments and suggestions about API. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 spgist_patch-0.92.gz Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove fmgr.h include in cube contrib --- caused crash on a Ge
On Sun, 4 Sep 2011, Tom Lane wrote: What I would suggest is to see whether a more recent x86 version shows the problem or not. If not, let's just write it off as an already-fixed compiler bug. I have installed the most recent version in the home directory of a purpose-made user on that machine. configure:3252: icc --version 5 icc (ICC) 12.0.5 20110719 Copyright (C) 1985-2011 Intel Corporation. All rights reserved. I did git checkout 6416a82a62db4e66b2edb0fa8fc83a580c3f1931 to get a revision I knew was right in the broken range for mongoose. Apparently they deprecated one of my compiler flags: -xN (N is for Nocona), seems they renamed it to -xSSE2. Since this is a one-off run, I ignored that warning. The result is no crash in the cube test. I think tomorrow I'll try to get the 9.0 compiler set up on a clean VM, and if the issue duplicates there, I can see about setting up SSH access if anyone is still interested in investigating this further. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Redundant bitmap index scans on smallint column
Hi list,
This simple query shouldn't cause two bitmap index scans:
EXPLAIN select * from test where b='0';
Bitmap Heap Scan on test (cost=1056.68..8200.12 rows=29839 width=314)
Recheck Cond: ((b = 0) AND (b = 0::smallint))
- BitmapAnd (cost=1056.68..1056.68 rows=5237 width=0)
- Bitmap Index Scan on test_i_idx (cost=0.00..485.45
rows=29839 width=0)
- Bitmap Index Scan on test_b_c_idx (cost=0.00..556.06
rows=29839 width=0)
Index Cond: (b = 0::smallint)
One of the indexes is a partial index, and the other is just a simple index.
Apparently, for some reason, the '0' is expanded into both an integer
and a smallint literal and the planner thinks it can reduce rows by
checking the condition twice?
This is how I reproduced the issue:
set enable_indexscan=off;
create table test as select i, (i/3)::smallint as b, 0::int as c,
repeat('x', 300) as filler from generate_series(1,17) i;
create index test_i_idx on test (i) where b=0;
create index test_b_c_idx on test (b,c);
analyze test;
explain select * from test where b='0';
Reproduced on PostgreSQL 8.3.15, 8.4.8, 9.0.4, 9.1rc1 and 9.2devel.
However, this issue does NOT occur on 8.2.21
When I write the literal without quotes, I get a more sensible plan:
EXPLAIN select * from test where b=0;
Bitmap Heap Scan on test (cost=493.79..8260.88 rows=30007 width=314)
Recheck Cond: (b = 0)
- Bitmap Index Scan on test_i_idx (cost=0.00..486.29 rows=30007 width=0)
Also, *before* analyzing the table, I get a good plan:
EXPLAIN select * from test where b='0';
Bitmap Heap Scan on test (cost=18.86..2450.01 rows=850 width=42)
Recheck Cond: (b = 0::smallint)
- Bitmap Index Scan on test_b_c_idx (cost=0.00..18.64 rows=850 width=0)
Index Cond: (b = 0::smallint)
Regards,
Marti Raudsepp
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL low watermark during base backup
On Sun, Sep 4, 2011 at 19:02, Simon Riggs si...@2ndquadrant.com wrote: On Fri, Sep 2, 2011 at 6:52 PM, Magnus Hagander mag...@hagander.net wrote: Attached patch implements a low watermark wal location in the walsender shmem array. Setting this value in a walsender prevents transaction log removal prior to this point - similar to how wal_keep_segments work, except with an absolute number rather than relative. For now, this is set when running a base backup with WAL included - to prevent the required WAL to be recycled away while the backup is running, without having to guestimate the value for wal_keep_segments. (There could be other ways added to set it in the future, but that's the only one I've done for now) It obviously needs some documentation updates as well, but I wanted to get some comments on the way it's done before I work on those. I'm not yet fully available for a discussion on this, but not sure I like this. You don't have to guess the setting of wal_keep_segments, you calculate it exactly from the size of your WAL disk. No other calculation is easy or accurate. Uh, no. What about the (very large number of) cases where pg is just sitting on one partition, possibly shared with a whole lot of other services? You'd need to set it to all-of-your-disk, which is something that will change over time. Maybe I wasn't entirely clear in the submission, but if it wasn't obvious: the use-case for this is the small and simple installations that need a simple way of doing a reliable online backup. This is the pg_basebackup -x usecase altogether - for example, anybody bigger likely has archiv elogging setup already, in which case this functionality is not interesting at all. This patch implements fill disk until primary croaks behaviour which means you are making a wild and risky guess as to whether it will work. If it does not, you are hosed. Replace primary with server - remember that this is about backups and not replication primarily. That said, you are correct, it does implement that. But then again, logging into the database and opening a transaction and just leaving it around for $forever will have similar problems - yet, we allow users to do that. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] force_not_null option support for file_fdw
In my usual environment that test passed, but finally I've reproduced the
failure with setting
$LC_COLLATE to es_ES.UTF-8. Do you have set any $LC_COLLATE in your test
environment?
It is not set in my environment.
I checked the behavior of ORDER BY when we set a collation on the regular
relation, not a foreign table.
Do we hit something other unexpected bug in collation here?
postgres=# CREATE TABLE t1 (word1 text);
CREATE TABLE
postgres=# INSERT INTO t1 VALUES ('ABC'),('abc'),('123'),('NULL');
INSERT 0 4
postgres=# ALTER TABLE t1 ALTER word1 TYPE text COLLATE ja_JP.utf8;
ALTER TABLE
postgres=# SELECT * FROM t1 ORDER BY word1;
word1
---
123
ABC
NULL
abc
(4 rows)
postgres=# ALTER TABLE t1 ALTER word1 TYPE text COLLATE en_US.utf8;
ALTER TABLE
postgres=# SELECT * FROM t1 ORDER BY word1;
word1
---
123
abc
ABC
NULL
(4 rows)
Thanks,
--
NEC Europe Ltd, SAP Global Competence Center
KaiGai Kohei kohei.kai...@emea.nec.com
-Original Message-
From: pgsql-hackers-ow...@postgresql.org
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of
Shigeru Hanada
Sent: 5. September 2011 06:56
To: Kohei KaiGai
Cc: PostgreSQL-development
Subject: Re: [HACKERS] force_not_null option support for file_fdw
Thanks for the review.
(2011/09/05 3:55), Kohei KaiGai wrote:
I tried to review this patch.
It seems to me its implementation is reasonable and enough simple.
All the works of this patch is pick-up force_not_null option from
pg_attribute.attfdwoptions and transform its data structure into
suitable form to the existing BeginCopyFrom().
So, I'd almost like to mark this patch Ready for Committer.
Here are only two points I'd like to comment on.
+ tuple = SearchSysCache2(ATTNUM,
+ RelationGetRelid(rel),
+ Int16GetDatum(attnum));
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+errmsg(cache lookup failed for attribute %d of
relation %u,
+ attnum, RelationGetRelid(rel;
The tuple should be always found unless we have any bugs that makes
mismatch between pg_class.relnatts and actual number of attributes.
So, it is a case to use elog(), instead of ereport() with error code.
Oh, I've missed that other similar errors use elog()...
Fixed.
One other point is diffset of regression test, when I run `make check
-C contrib/file_fdw'.
Do we have something changed corresponding to COPY TO/FROM statement
since 8th-August to now?
I don't know about such change, and src/backend/command/copy.c has not been
touched since Feb 23.
*** /home/kaigai/repo/sepgsql/contrib/file_fdw/expected/file_fdw.out
2011-09-04 20:36:23.670981921 +0200
--- /home/kaigai/repo/sepgsql/contrib/file_fdw/results/file_fdw.out
2011-09-04 20:36:51.202989681 +0200
***
*** 118,126
word1 | word2
---+---
123 | 123
ABC | ABC
NULL |
- abc | abc
(4 rows)
-- basic query tests
--- 118,126
word1 | word2
---+---
123 | 123
+ abc | abc
ABC | ABC
NULL |
(4 rows)
-- basic query tests
==
In my usual environment that test passed, but finally I've reproduced the
failure with setting
$LC_COLLATE to es_ES.UTF-8. Do you have set any $LC_COLLATE in your test
environment?
Regards,
--
Shigeru Hanada
Click
https://www.mailcontrol.com/sr/yQEP2keV9uzTndxI!oX7UgZzT7dlvrTeW0pvcI7!FpP+qgioCQTZMxIe1v95Rjzlbr
CRFdjEt0BTEf5tQBqpNg== to report this email as spam.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
On 2011-09-01 14:40, Robert Haas wrote: userspace avc. I've committed this, but I still think it would be helpful to revise that comment. The turn boosted up is not very precise or informative. Could you submit a separate, comment-only patch to improve this? I didn't see my name as one of the reviewers in the commit message. If that is because the review was bad, I'd be interested to know what I can improve for the next one. regards, Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove fmgr.h include in cube contrib --- caused crash on a Ge
Jeremy Drake wrote: On Sun, 4 Sep 2011, Tom Lane wrote: What I would suggest is to see whether a more recent x86 version shows the problem or not. If not, let's just write it off as an already-fixed compiler bug. I have installed the most recent version in the home directory of a purpose-made user on that machine. configure:3252: icc --version 5 icc (ICC) 12.0.5 20110719 Copyright (C) 1985-2011 Intel Corporation. All rights reserved. I did git checkout 6416a82a62db4e66b2edb0fa8fc83a580c3f1931 to get a revision I knew was right in the broken range for mongoose. Apparently they deprecated one of my compiler flags: -xN (N is for Nocona), seems they renamed it to -xSSE2. Since this is a one-off run, I ignored that warning. The result is no crash in the cube test. I think tomorrow I'll try to get the 9.0 compiler set up on a clean VM, and if the issue duplicates there, I can see about setting up SSH access if anyone is still interested in investigating this further. What would we investigate except a compiler bug? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Clean up the #include mess a little.
Tom Lane wrote: Clean up the #include mess a little. walsender.h should depend on xlog.h, not vice versa. (Actually, the inclusion was circular until a couple hours ago, which was even sillier; but Bruce broke it in the expedient rather than logically correct direction.) Because of that poor decision, plus blind application of pgrminclude, we had a situation where half the system was depending on xlog.h to include such unrelated stuff as array.h and guc.h. Clean up the header inclusion, and manually revert a lot of what pgrminclude had done so things build again. This episode reinforces my feeling that pgrminclude should not be run without adult supervision. Inclusion changes in header files in particular need to be reviewed with great care. More generally, it'd be good if we had a clearer notion of module layering to dictate which headers can sanely include which others ... but that's a big task for another day. What pgrminclude does it to lock down the minimal set of includes, and that easily could cause something like xlog.h becoming the go-to include file for many C files. I don't remember this problem happening before but it clearly happened this time. Not sure how to avoid that except, as you said, analyze the entire changeset of pgrminclude. For this run, I focused on not breaking any platform builds so I was not focusing on the actual include file layout. I assumed fiddling with the actual pgrminclude output would likely break builds. The process I used was to get pgcompinclude to allow all include files to compile (so they their inclusion would not bleed into files that included them), then run pgrminclude. Well, I assume we are done for another five years. The includes removed were minimal, especially considering five years of work. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Clean up the #include mess a little.
On Mon, Sep 5, 2011 at 2:52 PM, Bruce Momjian br...@momjian.us wrote: Well, I assume we are done for another five years. The includes removed were minimal, especially considering five years of work. What I wouldn't mind seeing is a graph of all includes and what they include. This might help figure out what layering violations there are like the one that caused this mess. I think I've seen tools to do this already somewhere. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Clean up the #include mess a little.
On Mon, Sep 5, 2011 at 15:55, Greg Stark st...@mit.edu wrote: On Mon, Sep 5, 2011 at 2:52 PM, Bruce Momjian br...@momjian.us wrote: Well, I assume we are done for another five years. The includes removed were minimal, especially considering five years of work. What I wouldn't mind seeing is a graph of all includes and what they include. This might help figure out what layering violations there are like the one that caused this mess. I think I've seen tools to do this already somewhere. http://doxygen.postgresql.org will do some of that, but I think not globally - but if you click into one header, I think it shows you the map from that perspective. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CF 2011-09-15 Call for Reviewers
At the PGCon 2011 PostgreSQL Developer Meeting the CommitFest schedule for 9.2 development was set. This called for four CFs, one month each, to start on these dates: - June 15 - September 15 - November 15 - January 15 We're coming up on the start of the second of those in ten days. I have volunteered to manage the CF process for this cycle. First, anyone with work they want reviewed during this time should be sure to submit it before September 15th. Please follow the guidelines here: http://wiki.postgresql.org/wiki/Submitting_a_Patch Note that context diff format is preferred, and that you should attach the patch to a post to the pgsql-hackers list. Then add an entry to the open commitfest with a reference to the message ID of that post. http://commitfest.postgresql.org/action/commitfest_view/open A major goal of the CF process is to involve more people into the review process. This is a great way to contribute to the project, regardless of skill level -- pretty much if you are subscribed to this list and following along, you can make a valuable contribution. Please read this page and follow the instructions there: http://wiki.postgresql.org/wiki/RRReviewers I see this CF has a great many performance-related patches, so it would be *very* helpful if people with access to hardware suitable for running performance tests could volunteer as reviewers. You don't need to be an expert C coder -- if you can compile from source and run benchmarks, you are needed! The goal is to have all patches which are submitted by the start of the CF disposed by the end. Disposition can be Rejected for features which are determined not be desirable by the community, or for which the patch takes a basically untenable approach. Disposition can be Returned with Feedback if the feature is desirable and the patch uses a fundamentally sound approach, but it cannot be brought to a finished state during the CF. Most patches need one or more rounds of revision based on review, and are then committed. To have patches committed before the end of the one-month cycle, both reviewers and authors must be prompt in posting (normally within four days of the patch waiting on them), so that committers have sufficient time to do a final review and edit within the CF. I do recognize that sometimes events conspire to delay things, or a good set of benchmarks may require more than four days to run. In those cases, it would be helpful to send email off-list to me so that I don't need to spend time checking on the status. Also, if you find yourself in over your head on a review or find yourself short on time, let me know so I can find another reviewer to help or continue the work. Reviewers should subscribe to both the pgsql-hackers and pgsql-rrreviewers lists. The -rrreviewers list is for discussion of who will take which patches, and other administrative tasks. Discussion of the patches themselves, and the features they are intended to implement, as well as actual reviews and revisions should all be posted to the -hackers list. If you can help, please sign up as outlined on the Wiki page, and either put yourself down as reviewer for a patch or email me off-list with an outline of your skills and interests so I can pick an unclaimed patch that seems a good fit. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Clean up the #include mess a little.
Excerpts from Magnus Hagander's message of lun sep 05 11:02:23 -0300 2011: On Mon, Sep 5, 2011 at 15:55, Greg Stark st...@mit.edu wrote: On Mon, Sep 5, 2011 at 2:52 PM, Bruce Momjian br...@momjian.us wrote: Well, I assume we are done for another five years. The includes removed were minimal, especially considering five years of work. What I wouldn't mind seeing is a graph of all includes and what they include. This might help figure out what layering violations there are like the one that caused this mess. I think I've seen tools to do this already somewhere. http://doxygen.postgresql.org will do some of that, but I think not globally - but if you click into one header, I think it shows you the map from that perspective. Yeah; and it isn't always complete, because some graphs tend to get too unwieldy so it has to prune (you can see this because some nodes show up with red borders). I am not sure it is really feasible to build a complete graph for all headers. We have too many of them and too many dependencies. Another useful graph to see is what files include a given header. A funny thing is that doxygen doesn't always display this; for example http://doxygen.postgresql.org/rel_8h.html -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regress test failed
On Sun, Sep 04, 2011 at 09:39:39AM -0400, Joe Abbate wrote: On 09/04/2011 08:57 AM, Andrew Dunstan wrote: In what locale does 'sc' sort before 's4'? (And I'd humbly suggest that whatever locale it is is possibly broken.) EBCDIC? If you have any EBCDIC machines for the buildfarm, that'd be great :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
On Thu, Sep 01, 2011 at 08:05:51AM +0200, hubert depesz lubaczewski wrote: On Wed, Aug 31, 2011 at 09:54:20PM -0400, Bruce Momjian wrote: Working with depesz, I have found the cause. The code I added to fix pg_upgrade in 9.0.4 and earlier releases didn't handle old 8.3 servers properly. I mistakenly processed toast table with the same pg_dump query as used for pre-8.4 toast tables, not realizing those were not functional because there were no reloptions for toast tables in pre-8.4. Thanks a lot. Will test and post results (around sunday/monday I guess). All worked. pg_upgrade/vacuum didn't raise any errors. Will check some random queries too, but don't expect anything to break. thanks again for quick help. Best regards, depesz -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
hubert depesz lubaczewski wrote: On Thu, Sep 01, 2011 at 08:05:51AM +0200, hubert depesz lubaczewski wrote: On Wed, Aug 31, 2011 at 09:54:20PM -0400, Bruce Momjian wrote: Working with depesz, I have found the cause. The code I added to fix pg_upgrade in 9.0.4 and earlier releases didn't handle old 8.3 servers properly. I mistakenly processed toast table with the same pg_dump query as used for pre-8.4 toast tables, not realizing those were not functional because there were no reloptions for toast tables in pre-8.4. Thanks a lot. Will test and post results (around sunday/monday I guess). All worked. pg_upgrade/vacuum didn't raise any errors. Will check some random queries too, but don't expect anything to break. Thanks. I will announce the known bug and the fix. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Clean up the #include mess a little.
Alvaro Herrera alvhe...@commandprompt.com writes: I am not sure it is really feasible to build a complete graph for all headers. We have too many of them and too many dependencies. Yeah, it's the too many dependencies aspect that is bothering me. The only concrete idea I've come up with so far is that it'd be a good idea to isolate certain primitive datatypes into their own group of headers. We have a number of headers that are meant to be this sort of animal already, eg storage/block.h, storage/relfilenode.h. But (1) there's no clear distinction between these headers and ones like, say, storage/smgr.h or storage/proc.h. (2) other things that have become widely-used primitive datatypes, such as TimestampTz, are declared in places that ideally ought to be near the top of the #include hierarchy not the bottom. So I think we could make some forward progress by moving all these simple datatype declarations into a separate set of headers in their own subdirectory of src/include/, perhaps datatype. There would be a hard and fast rule that no header in this set could depend on anything beyond postgres.h and other members of the same set, so that these headers clearly form the bottom level of the #include hierarchy. Probably some of the stuff now in postgres.h could migrate to this group too. Eventually I'd like to see some fairly clear layering rules at the header-directory level, like storage/ is lower-level than commands/ so nothing in the former directory should include anything in the latter. But achieving that is a long way off. Of course, the problem with all of this is that making much progress would be a large amount of work with relatively small concrete payoff. Still, I'm starting to feel that we've got such a spaghetti-like mess that we need to do something. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Help with pg_locks query
I am writing a talk about the lock manager for PG Open and I would like suggestions on how to improve a query in my talk. The query creates a lockinfo_hierarchy view of a recursive query on other views. The output shows the locks held and the locks being waited for: \! psql -e -c 'SELECT * FROM lockinfo_hierarchy;' | sed 's/^/\t/g' SELECT * FROM lockinfo_hierarchy; ?column? | pid | vxid | granted | xid_lock | lock_type | relname | page | tuple --+---++-+--+---+--+--+--- 1| 24860 | 2/3106 | t | 828 | transactionid | | | 1| 24864 | 3/42 | t | 829 | transactionid | | | 1| 24868 | 4/78 | t | 830 | transactionid | | | 1| 24872 | 5/22 | t | 831 | transactionid | | | 2| 24864 | 3/42 | f | 828 | transactionid | | | 3| 24864 | 3/42 | t | | tuple | lockdemo |0 | 1 4| 24868 | 4/78 | f | | tuple | lockdemo |0 | 1 4| 24872 | 5/22 | f | | tuple | lockdemo |0 | 1 (8 rows) The SQL needed to reproduce this output is attached, and must be run in your personal database, e.g. postgres. What this output shows are four transactions holding locks on their own xids, transaction 3/42 waiting for 828 to complete, and 3/42 holding a row lock that 4/78 and 5/22 are waiting on. When there are multiple waiters, one transaction waits on the real xid and the others sleep waiting to be woken up later. Is there any better way to show this? (The first column is just there for debugging so you can see what part of the query generated the row.) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- cannot be a temporary view because other sessions must see it DROP VIEW IF EXISTS lockview CASCADE; CREATE VIEW lockview AS SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted, CASE WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL THENvirtualxid || ' ' || transactionid WHEN virtualxid::text IS NOT NULL THENvirtualxid ELSEtransactionid::text END AS xid_lock, relname, page, tuple, classid, objid, objsubid FROMpg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) WHERE -- do not show our view's locks pid != pg_backend_pid() AND -- no need to show self-vxid locks virtualtransaction IS DISTINCT FROM virtualxid -- granted is ordered earlier ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7; DROP VIEW IF EXISTS lockview1 CASCADE; CREATE VIEW lockview1 AS SELECT pid, vxid, lock_type, lock_mode, granted, xid_lock, relname FROMlockview -- granted is ordered earlier ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7; DROP VIEW IF EXISTS lockview2 CASCADE; CREATE VIEW lockview2 AS SELECT pid, vxid, lock_type, page, tuple, classid, objid, objsubid FROMlockview -- granted is first -- add non-display columns to match ordering of lockview ORDER BY 1, 2, granted DESC, vxid, xid_lock::text, 3, 4, 5, 6, 7, 8; DROP TABLE IF EXISTS lockdemo; CREATE TABLE lockdemo (col int); INSERT INTO lockdemo VALUES (1); -- do two UPDATEs to cause a wait CREATE VIEW lockinfo_hierarchy AS WITH RECURSIVE lockinfo1 AS ( SELECT '1', pid, vxid, granted, xid_lock, lock_type, relname, page, tuple FROM lockview WHERE xid_lock IS NOT NULL AND relname IS NULL AND granted UNION ALL SELECT '2', lockview.pid, lockview.vxid, lockview.granted, lockview.xid_lock, lockview.lock_type, lockview.relname, lockview.page, lockview.tuple FROM lockinfo1 JOIN lockview ON (lockinfo1.xid_lock = lockview.xid_lock) WHERE lockview.xid_lock IS NOT NULL AND lockview.relname IS NULL AND NOT lockview.granted AND lockinfo1.granted), lockinfo2 AS ( SELECT '3', pid, vxid, granted, xid_lock, lock_type, relname, page, tuple FROM lockview WHERE lock_type = 'tuple' AND granted UNION ALL SELECT '4', lockview.pid, lockview.vxid, lockview.granted, lockview.xid_lock, lockview.lock_type, lockview.relname, lockview.page, lockview.tuple FROM lockinfo2 JOIN lockview ON (
Re: [HACKERS] WIP: Fast GiST index build
On 05.09.2011 14:10, Heikki Linnakangas wrote: On 01.09.2011 12:23, Alexander Korotkov wrote: On Thu, Sep 1, 2011 at 12:59 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: So I changed the test script to generate the table as: CREATE TABLE points AS SELECT random() as x, random() as y FROM generate_series(1, $NROWS); The unordered results are in: testname | nrows | duration | accesses -+**---+-+**-- points unordered buffered | 25000 | 05:56:58.575789 | 2241050 points unordered auto | 25000 | 05:34:12.187479 | 2246420 points unordered unbuffered | 25000 | 04:38:48.663952 | 2244228 Although the buffered build doesn't lose as badly as it did with more overlap, it still doesn't look good :-(. Any ideas? But it's still a lot of overlap. It's about 220 accesses per small area request. It's about 10 - 20 times greater than should be without overlaps. If we roughly assume that 10 times more overlap makes 1/10 of tree to be used for actual inserts, then that part of tree can easily fit to the cache. You can try my splitting algorithm on your test setup (it this case I advice to start from smaller number of rows, 100 M for example). I'm requesting real-life datasets which makes troubles in real life from Oleg. Probably those datasets is even larger or new linear split produce less overlaps on them. I made a small tweak to the patch, and got much better results (this is with my original method of generating the data): testname | nrows | duration | accesses -+---+-+-- points unordered buffered | 25000 | 03:34:23.488275 | 3945486 points unordered auto | 25000 | 02:55:10.248722 | 3767548 points unordered unbuffered | 25000 | 04:02:04.168138 | 4564986 The full results of this test are in: testname | nrows |duration | accesses -+---+-+-- points unordered buffered | 25000 | 03:34:23.488275 | 3945486 points unordered auto | 25000 | 02:55:10.248722 | 3767548 points unordered unbuffered | 25000 | 04:02:04.168138 | 4564986 points ordered buffered | 25000 | 02:00:10.467914 | 5572906 points ordered auto | 25000 | 02:16:01.859039 | 5435673 points ordered unbuffered | 25000 | 03:23:18.061742 | 1875826 (6 rows) Interestingly, in this test case the buffered build was significantly faster even in the case of ordered input - but the quality of the produced index was much worse. I suspect it's because of the last-in-first-out nature of the buffering, tuples that pushed into buffers first are flushed to lower levels last. Tweaking the data structures to make the buffer flushing a FIFO process might help with that, but I'm afraid that might make our cache hit ratio worse when reading pages from the temporary file. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Reminder: 9.1 release is upcoming
Barring any disastrous bug reports in the next few days, 9.1.0 will be wrapped Thursday evening for public announcement Monday September 12. Try not to break the 9.1 branch this week ;-) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is the attribute options cache actually worth anything?
Tom Lane wrote: So while poking at a recent example from Marc Cousin (hundreds of tables each with 1000 attributes) I observed that a simple ANALYZE would bloat the backend process to the tune of several hundred megabytes. I think there is a leak in CacheMemoryContext, but haven't tracked it down yet. But I also noticed that tens of megabytes were disappearing into Attopt cache, and after reading the code to see what the heck that was, I am wondering what the justification for having it is at all. In the presumably normal case where the attribute hasn't got options, all it's saving us is a syscache access, which is probably not noticeably more expensive than the hash lookup. In the case where there is an option, it's saving us an attribute_reloptions() call, but it's not apparent to me that that's so expensive as to justify putting a cache in front of it, especially not if we're going to do a palloc cycle anyway. Did anybody do any performance measurements to demonstrate that this code has a reason to live? Because if I don't see some, I'm going to rip it out. Did we decide to keep the cache in attoptcache.c? Is this a TODO? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] toast tables on system catalogs
Alvaro Herrera wrote: Excerpts from Tom Lane's message of mar mar 01 19:03:35 -0300 2011: Alvaro Herrera alvhe...@alvh.no-ip.org writes: Strangely, we made pg_database have a toast table, and the only reason for this is datacl. Should we create toast tables for the remaining catalogs? As I commented on your blog, this is nonsense. pg_database has a TOAST table becase we thought it might need one for datconfig[]. Now that that's gone, it'd be consistent to remove the toast table, but it didn't occur to us to do that. Yeah, it occured to me to troll the git logs just after sending the email and I promptly noticed the bug in my conclusion -- there was no datacl back then; and pg_db_role_settings is very new. aclitem entries wide enough to need toasting are going to suck for all sorts of reasons (IIRC there are some O(N^2) algorithms in there, not to mention the cost of pulling in entries from a toast table on every access) so I am not excited about encouraging people to use them. I agree on not supporting large numbers of privileges, though the error message leaves a bit to be desired. Should we remove the toast table declaration for pg_database? (BTW with the relmapper mechanism, do we still need to declare the toast table OIDs?) Did we decide on this? Is it a TODO? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is the attribute options cache actually worth anything?
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Did anybody do any performance measurements to demonstrate that this code has a reason to live? Because if I don't see some, I'm going to rip it out. Did we decide to keep the cache in attoptcache.c? Is this a TODO? It's still a TODO, I think --- the code's still there, and nobody's done any performance measurements either way. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] regular logging of checkpoint progress
Tomas, I cannot seem to see any of the patches you link here: https://commitfest.postgresql.org/action/patch_view?id=628 Looks like you need to take the out of the messageid. -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] KEEPONLYALNUM for pg_trgm is not documented
Robert Haas wrote: On Fri, Mar 11, 2011 at 3:59 AM, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Mar 11, 2011 at 5:52 PM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: contrib/pg_trgm in 9.1 becomes more attractive feature by index supports for LIKE operators, but only alphabet and numeric characters are indexed by default. But, we can modify KEEPONLYALNUM in the source code to keep all characters in n-gram words. However, the limitation and KEEPONLYALNUM are not documented in the page: ?http://developer.postgresql.org/pgdocs/postgres/pgtrgm.html An additonal documentation patches acceptable? The issues would be a FAQ for non-English users. I heard that pg_trgm will be one of the *killer features* of 9.1 in Japan, where N-gram based text search is preferred. +10 It's certainly not too late for doc patches. I have applied the attached documention patch to 9.0, 9.1, and current to mention that only ascii alphanumeric characters are processed by contrib/pg_trgm. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/pgtrgm.sgml b/doc/src/sgml/pgtrgm.sgml new file mode 100644 index 18f0f3e..30e5355 *** a/doc/src/sgml/pgtrgm.sgml --- b/doc/src/sgml/pgtrgm.sgml *** *** 9,15 para The filenamepg_trgm/filename module provides functions and operators ! for determining the similarity of text based on trigram matching, as well as index operator classes that support fast searching for similar strings. /para --- 9,16 para The filenamepg_trgm/filename module provides functions and operators ! for determining the similarity of acronymASCII/ ! alphanumeric text based on trigram matching, as well as index operator classes that support fast searching for similar strings. /para -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
On Mon, Sep 05, 2011 at 05:48:50PM +0200, hubert depesz lubaczewski wrote:
On Thu, Sep 01, 2011 at 08:05:51AM +0200, hubert depesz lubaczewski wrote:
On Wed, Aug 31, 2011 at 09:54:20PM -0400, Bruce Momjian wrote:
Working with depesz, I have found the cause. The code I added to fix
pg_upgrade in 9.0.4 and earlier releases didn't handle old 8.3 servers
properly. I mistakenly processed toast table with the same pg_dump
query as used for pre-8.4 toast tables, not realizing those were not
functional because there were no reloptions for toast tables in pre-8.4.
Thanks a lot. Will test and post results (around sunday/monday I guess).
All worked.
pg_upgrade/vacuum didn't raise any errors. Will check some random
queries too, but don't expect anything to break.
Hmm .. got breakage.
Have table with ltree column, and any select to it causes:
=# select * from categories limit 1;
The connection to the server was lost. Attempting reset: Failed.
strace shows that backend read table, then it opened correct ltree.so,
but then:
29293 17:49:00.667865 stat(/opt/pgsql-9.0.5a-int/lib/ltree, 0x7fffb026ceb0) =
-1 ENOENT (No such file or directory) 0.13
29293 17:49:00.667935 stat(/opt/pgsql-9.0.5a-int/lib/ltree.so,
{st_mode=S_IFREG|0755, st_size=72966, ...}) = 0 0.10
29293 17:49:00.668007 stat(/opt/pgsql-9.0.5a-int/lib/ltree.so,
{st_mode=S_IFREG|0755, st_size=72966, ...}) = 0 0.09
29293 17:49:00.668135 open(/opt/pgsql-9.0.5a-int/lib/ltree.so, O_RDONLY) = 46
0.12
29293 17:49:00.668181 read(46,
\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0\0\1\0\0\0\240.\0\0..., 832) = 832
0.08
29293 17:49:00.668227 fstat(46, {st_mode=S_IFREG|0755, st_size=72966, ...}) = 0
0.06
29293 17:49:00.668294 mmap(NULL, 2153248, PROT_READ|PROT_EXEC,
MAP_PRIVATE|MAP_DENYWRITE, 46, 0) = 0x7feba4abc000 0.13
29293 17:49:00.668341 mprotect(0x7feba4aca000, 2093056, PROT_NONE) = 0
0.12
29293 17:49:00.668381 mmap(0x7feba4cc9000, 4096, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 46, 0xd000) = 0x7feba4cc9000 0.12
29293 17:49:00.668429 close(46) = 0 0.07
29293 17:49:00.668715 open(/dev/tty, O_RDWR|O_NOCTTY|O_NONBLOCK) = -1 ENXIO
(No such device or address) 0.17
29293 17:49:00.668771 writev(2, [{*** glibc detected *** , 23}, {postgres:
postgres xxx [loca..., 41}, {: , 2}, {double free or corruption
(!prev..., 33}, {: 0x, 4}, {00be67a0, 16}, { ***\n, 5}], 7) =
124 0.14
29293 17:49:00.668863 open(/opt/pgsql-9.0.5a-int/lib/libgcc_s.so.1, O_RDONLY)
= -1 ENOENT (No such file or directory) 0.10
29293 17:49:00.668907 open(/opt/pgsql-8.3.11-int/lib/libgcc_s.so.1, O_RDONLY)
= -1 ENOENT (No such file or directory) 0.12
29293 17:49:00.668952 open(/etc/ld.so.cache, O_RDONLY) = 46 0.10
29293 17:49:00.668990 fstat(46, {st_mode=S_IFREG|0644, st_size=17400, ...}) = 0
0.06
29293 17:49:00.669044 mmap(NULL, 17400, PROT_READ, MAP_PRIVATE, 46, 0) =
0x7feba80d7000 0.08
29293 17:49:00.669077 close(46) = 0 0.06
29293 17:49:00.669110 access(/etc/ld.so.nohwcap, F_OK) = -1 ENOENT (No such
file or directory) 0.09
29293 17:49:00.669156 open(/lib/libgcc_s.so.1, O_RDONLY) = 46 0.12
29293 17:49:00.669197 read(46,
\177ELF\2\1\1\0\0\0\0\0\0\0\0\0\3\0\0\1\0\0\0\240!\0\0..., 832) = 832
0.09
29293 17:49:00.669244 mmap(NULL, 134217728, PROT_NONE,
MAP_PRIVATE|MAP_ANONYMOUS|MAP_NORESERVE, -1, 0) = 0x7feb9cabc000 0.07
29293 17:49:00.669278 munmap(0x7feb9cabc000, 55853056) = 0 0.11
29293 17:49:00.669313 munmap(0x7feba400, 11255808) = 0 0.08
29293 17:49:00.669347 mprotect(0x7feba000, 135168, PROT_READ|PROT_WRITE) =
0 0.08
29293 17:49:00.669387 fstat(46, {st_mode=S_IFREG|0644, st_size=56072, ...}) = 0
0.06
29293 17:49:00.669451 mmap(NULL, 2151816, PROT_READ|PROT_EXEC,
MAP_PRIVATE|MAP_DENYWRITE, 46, 0) = 0x7feba48ae000 0.09
29293 17:49:00.669487 mprotect(0x7feba48bb000, 2097152, PROT_NONE) = 0
0.09
29293 17:49:00.669522 mmap(0x7feba4abb000, 4096, PROT_READ|PROT_WRITE,
MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 46, 0xd000) = 0x7feba4abb000 0.09
29293 17:49:00.669565 close(46) = 0 0.06
29293 17:49:00.669614 munmap(0x7feba80d7000, 17400) = 0 0.12
29293 17:49:00.669765 write(2, === Backtrace: =\n, 29) = 29
0.11
29293 17:49:00.669852 writev(2, [{/lib/libc.so.6, 14}, {[0x, 3},
{7feba759908a, 12}, {]\n, 2}], 4) = 31 0.11
29293 17:49:00.669937 writev(2, [{/lib/libc.so.6, 14}, {(, 1}, {cfree,
5}, {+0x, 3}, {8c, 2}, {), 1}, {[0x, 3}, {7feba759cc1c, 12}, {]\n,
2}], 9) = 43 0.12
29293 17:49:00.670128 writev(2, [{postgres: postgres xxx [loca..., 41},
{[0x, 3}, {6c18c9, 6}, {]\n, 2}], 4) = 52 0.11
29293 17:49:00.670289 writev(2, [{postgres: postgres xxx [loca..., 41},
{(, 1}, {MemoryContextDelete, 19}, {+0x, 3}, {54, 2}, {), 1}, {[0x,
3}, {6c1e54, 6}, {]\n, 2}], 9) = 78 0.12
29293 17:49:00.670453 writev(2, [{postgres: postgres xxx [loca..., 41},
{(, 1},
[HACKERS] savepoint commit performance
This patch: https://commitfest.postgresql.org/action/patch_view?id=605 Seems to have been after thoughts, and back burner stuff, and forgotten about... Has it already been commit? http://archives.postgresql.org/pgsql-committers/2011-07/msg00206.php Oh, wait, nevermind, it was revoked and reworked: http://archives.postgresql.org/pgsql-hackers/2011-07/msg01041.php but that was posted Jul 19, 2011. And the Patch linked from commitfest is Jun 6, 2011. So is that an old patch? Or a new patch? I'm confused. -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Redundant bitmap index scans on smallint column
Marti Raudsepp ma...@juffo.org writes: This simple query shouldn't cause two bitmap index scans: EXPLAIN select * from test where b='0'; Bitmap Heap Scan on test (cost=1056.68..8200.12 rows=29839 width=314) Recheck Cond: ((b = 0) AND (b = 0::smallint)) - BitmapAnd (cost=1056.68..1056.68 rows=5237 width=0) - Bitmap Index Scan on test_i_idx (cost=0.00..485.45 rows=29839 width=0) - Bitmap Index Scan on test_b_c_idx (cost=0.00..556.06 rows=29839 width=0) Index Cond: (b = 0::smallint) One of the indexes is a partial index, and the other is just a simple index. Apparently, for some reason, the '0' is expanded into both an integer and a smallint literal and the planner thinks it can reduce rows by checking the condition twice? Yeah, this happens because choose_bitmap_and() compromises between planning speed and exact detection of redundant index conditions. What we have to start with is WHERE b = 0::smallint, which the planner is able to prove implies the index predicate WHERE b = 0::integer, so both indexes are considered. But the check for predicate redundancy in choose_bitmap_and() only uses simple equality not provability, so it does not recognize that the two indexes are entirely redundant. I'm not really eager to change that, especially in view of the fact that a plain (non bitmap) indexscan is considerably cheaper than any of these alternatives in this example. I did have an idea while looking at this example --- namely, that we could provide some further protection cheaply with this simple hack in cost_bitmap_heap_scan: diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index 7812a8628fc94335aaf1f506c4ea5ebb7960f8d8..c001725267a06063f45bbcde0b09f5784b0f5c3a 100644 *** a/src/backend/optimizer/path/costsize.c --- b/src/backend/optimizer/path/costsize.c *** cost_bitmap_heap_scan(Path *path, Planne *** 607,612 --- 607,622 */ tuples_fetched = clamp_row_est(indexSelectivity * baserel-tuples); + /* +* Disbelieve an estimate that's less than what we previously estimated +* for the actual number of rows needed from the table. This can happen +* when we are considering a bitmap AND of indexes with redundant +* conditions, since it's difficult for the selectivity code to recognize +* the redundancy. By clamping the cost estimate this way, we prevent +* redundant AND scans from looking cheaper than non-redundant ones. +*/ + tuples_fetched = Max(tuples_fetched, baserel-rows); + T = (baserel-pages 1) ? (double) baserel-pages : 1.0; if (outer_rel != NULL outer_rel-rows 1) I tested this and it fixes this particular example, by preventing the heap scan part of the plan from looking cheaper than it does with just one index in use. (The index scan part is of course more expensive with extra indexes, so possibilities with extra indexes will lose out.) It'd be nice to imagine that this quick and dirty solution obsoletes the need for most of the expensive heuristics in choose_bitmap_and, but I'm afraid it probably does not. baserel-rows might include the effects of some non-index-related WHERE conditions, so the clamp here is not tight. Still, it should fix egregious cases like this one, so I'm inclined to apply it. Reproduced on PostgreSQL 8.3.15, 8.4.8, 9.0.4, 9.1rc1 and 9.2devel. However, this issue does NOT occur on 8.2.21 8.2 doesn't recognize the partial index as applicable (for lack of enough understanding of cross-type operator relationships), so it doesn't reach the problematic decision. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regular logging of checkpoint progress
On 09/05/2011 12:17 PM, Andy Colson wrote: Tomas, I cannot seem to see any of the patches you link here: https://commitfest.postgresql.org/action/patch_view?id=628 Looks like you need to take the out of the messageid. -Andy This patch seems to solve the problem of going back in time to solve a problem. (need time stamped log files to see if things where slow because of checkpoint). Several people thought a view or some-non-log option would be better. Tomas replied but I need to go back in time to post diagnose a problem, and I saw no replies to that. Taking into account Noah's and Greg's Displaying accumulated autovacuum cost patch is also sending to logs, do we all now agree that this is proper way? -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
Bruce Momjian wrote:
hubert depesz lubaczewski wrote:
On Wed, Aug 31, 2011 at 01:23:05PM -0400, Bruce Momjian wrote:
Can you get me the 9.0.X pg_class.relfrozenxid for the toast and heap
tables involved?
Sure:
=# select oid::regclass, relfrozenxid from pg_class where relname in
('transactions', 'pg_toast_106668498');
oid | relfrozenxid
-+--
pg_toast.pg_toast_106668498 | 3673553926
transactions| 3623560321
(2 rows)
Working with depesz, I have found the cause. The code I added to fix
pg_upgrade in 9.0.4 and earlier releases didn't handle old 8.3 servers
properly. I mistakenly processed toast table with the same pg_dump
query as used for pre-8.4 toast tables, not realizing those were not
functional because there were no reloptions for toast tables in pre-8.4.
The attached applied patches fix all releases. This will have to be
mentioned in the 9.0.5 release notes, and we should probably do the same
kind of announcement we did when I fixed this for 9.0.4. :-(
Yeah, I should not have caused this bug. It did not show up in any of
my testing.
I have posted the bug and fix announcement to the announce email list.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
hubert depesz lubaczewski wrote: I'm not sure if it's upgrade thing, or is it because of error in ltree/compilation, but it looks bad. Is there any more info I could show/gather to help debug the issue? I am confused by the error --- is it not loading, or can you get a backtrace of the crash? If I had to take a guess, it would be that there is some ltree incompatibility from PG 8.3 that we didn't know about. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] remove useless ccache searching
This patch: https://commitfest.postgresql.org/action/patch_view?id=597 caches the cache because, I guess, the cache is slow. Simon asked, What is making the first cache so slow?. Pavel does not know, nor how to fix it, and nobody else responded. So my question is: is someone going to take a look at the cache? Should this be accepted as a short term fix (cuz someone will fix the cache later), long term fix (cuz the cache needs to say as-is), or not at all (because someone will fix cache right now now)? -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Couple document fixes
Alvaro Herrera wrote: Removing CC to pg-docs so that Robert reads it. Excerpts from Bruce Momjian's message of vie mar 11 08:13:20 -0300 2011: Kevin Grittner wrote: relpersistence should be typechar/type, not typechar/type. Oddly enough, there is a difference. I am unsure on that one. We have many 'char' mentions in catalog.sgml, and I don't see any of them shown as 'char'. (Wow, we should have just called this type char1, but I think that name came from Berkeley!) The big problem is that the pg_type name is really char _without_ quotes. One idea is to rename the type to something else. We could keep char as an alias for backwards compatibility, but use the new name in system catalogs, and document it as the main name of the type. Discussed the idea a bit on IM with Bruce, but couldn't find any really good alternative. Idea floated so far: * byte (seems pretty decent to me) * octet (though maybe people would expect it'd output as a number) * char1 (looks ugly, but then we have int4 and so on) * achar (this one is just plain weird) None seems great. Thoughts? Any new ideas on how to document our char data type? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove useless ccache searching
Pavel, I have not taken on your patch for review, but I was reading the history of it, and one question popped up: If you are allocating a new cache, what if the array is really big, will 1st cache + your cache get bigger than work_mem? (or are array op's not constrained by work_mem? Sorry, I have not used array's so not sure if there are memory limits on them) -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Couple document fixes
Excerpts from Bruce Momjian's message of lun sep 05 15:21:46 -0300 2011: Alvaro Herrera wrote: Discussed the idea a bit on IM with Bruce, but couldn't find any really good alternative. Idea floated so far: * byte (seems pretty decent to me) * octet (though maybe people would expect it'd output as a number) * char1 (looks ugly, but then we have int4 and so on) * achar (this one is just plain weird) None seems great. Thoughts? Any new ideas on how to document our char data type? I think part of the problem is that this only seems to bother patch developers, and only until they become aware of the issue. After that, it just becomes a known gotcha that's easy to work around. Thus, there's not much interest in spending a lot of time fixing it. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove useless ccache searching
Hello 2011/9/5 Andy Colson a...@squeakycode.net: This patch: https://commitfest.postgresql.org/action/patch_view?id=597 caches the cache because, I guess, the cache is slow. Simon asked, What is making the first cache so slow?. Pavel does not know, nor how to fix it, and nobody else responded. So my question is: is someone going to take a look at the cache? Should this be accepted as a short term fix (cuz someone will fix the cache later), long term fix (cuz the cache needs to say as-is), or not at all (because someone will fix cache right now now)? A idea so this patch is cache of cache is not exact Access to array needs lot of metadata related to element type. These metadata are statics. I can store these data when we first access a variable with data. ccache is relative fast - hash search, but should not be faster than just direct access to structure. This technique is used more time in PL/pgSQL. Regards Pavel -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
On Mon, Sep 05, 2011 at 02:18:18PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: I'm not sure if it's upgrade thing, or is it because of error in ltree/compilation, but it looks bad. Is there any more info I could show/gather to help debug the issue? I am confused by the error --- is it not loading, or can you get a backtrace of the crash? The one in logs is not sufficient? If not - could you tell me how to make the backtrace? I'm by far not a c programmer, so for this I'd need some tutoring. If I had to take a guess, it would be that there is some ltree incompatibility from PG 8.3 that we didn't know about. it's possible. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove useless ccache searching
Hello 2011/9/5 Andy Colson a...@squeakycode.net: Pavel, I have not taken on your patch for review, but I was reading the history of it, and one question popped up: If you are allocating a new cache, what if the array is really big, will 1st cache + your cache get bigger than work_mem? (or are array op's not constrained by work_mem? Sorry, I have not used array's so not sure if there are memory limits on them) this patch doesn't cache a array - it store only a 18 bytes more per array variable - it doesn't depend on array size. but generally, arrays are not limited by work_mem - so if you work with large arrays - you can go out of memory. Regards Pavel -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
hubert depesz lubaczewski wrote: On Mon, Sep 05, 2011 at 02:18:18PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: I'm not sure if it's upgrade thing, or is it because of error in ltree/compilation, but it looks bad. Is there any more info I could show/gather to help debug the issue? I am confused by the error --- is it not loading, or can you get a backtrace of the crash? The one in logs is not sufficient? If not - could you tell me how to make the backtrace? I'm by far not a c programmer, so for this I'd need some tutoring. I think you want this: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD While strace is useful, it doesn't show us where the C code is failing. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] B-tree parent pointer and checkpoints
Heikki Linnakangas wrote: On 11.03.2011 19:41, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: On 11.03.2011 17:59, Tom Lane wrote: But that will be fixed during WAL replay. Not under the circumstances that started the original thread: 1. Backend splits a page 2. Checkpoint starts 3. Checkpoint runs to completion 4. Crash (5. Backend never got to insert the parent pointer) WAL replay starts at the checkpoint redo pointer, which is after the page split record, so WAL replay won't insert the parent pointer. That's an incredibly tight window to hit in practice, but it's possible in theory. Hmm. It's not so improbable that checkpoint would start inside that window, but that the parent insertion is still pending by the time the checkpoint finishes is pretty improbable. How about just reducing the deletion-time ERROR for missing downlink to a LOG? Well, the code that follows expects to have a valid parent page locked, so you can't literally do just that. But yeah, LOG and aborting the page deletion seems fine to me. Did this get fixed? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] memory-related bugs
Noah Misch wrote: A suitably-instrumented run of make installcheck-world under valgrind turned up a handful of memory-related bugs: * memcpy()/strncpy() between overlapping regions uniqueentry() and dispell_lexize() both deduplicate an array by iteratively copying elements downward to occlude the duplicates. Before finding a first duplicate, these functions call memcpy() with identical arguments. Similarly, resolve_polymorphic_tupdesc() calls TupleDescInitEntry() with an attributeName pointing directly into the TupleDesc's name bytes, causing the latter to call strncpy() with identical arguments. The attached mem1v1-memcpy-overlap.patch fixes these sites by checking for equal pointers before the affected call. For TupleDescInitEntry(), I considered instead having resolve_polymorphic_tupdesc() pstrdup() the value. * read past the end of a Form_pg_type in examine_attribute() examine_attribute() copies a Form_pg_type naively. Since the nullable columns at the end of the structure are not present in memory, the memcpy() reads eight bytes past the end of the source allocation. mem2v1-analyze-overread.patch updates this code to match how we address the same issue for Form_pg_attribute. * off-by-one error in shift_jis_20042euc_jis_2004() This function grabs two bytes at a time, even when only one byte remains; this makes it read past the end of the input. mem3v1-sjis-offbyone.patch changes it to not do this and to report an error when the input ends in a byte that would start a two-byte sequence. Did we conclude any of these were useful? http://archives.postgresql.org/pgsql-hackers/2011-03/msg00856.php I know there were concerns about some of them in the thread. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
hubert depesz lubaczewski dep...@depesz.com writes: On Mon, Sep 05, 2011 at 02:18:18PM -0400, Bruce Momjian wrote: If I had to take a guess, it would be that there is some ltree incompatibility from PG 8.3 that we didn't know about. it's possible. [ checks the git history... ] This 8.4 commit: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=8eee65c996048848c20f6637c1d12b319a4ce244 changed a number of ltree data structures, though I'm not sure whether any of those are on-disk structures. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove useless ccache searching
Andy Colson a...@squeakycode.net writes: This patch: https://commitfest.postgresql.org/action/patch_view?id=597 caches the cache because, I guess, the cache is slow. Simon asked, What is making the first cache so slow?. Pavel does not know, nor how to fix it, and nobody else responded. Well, the cache doesn't need to be slow to make it worthwhile to eliminate repeated cache lookups altogether. We do that in many places. The questions that need to be asked here are whether the speed gain is worthwhile, whether there is any possibility of the locally cached information becoming obsolete, whether it's done in a clean fashion, whether it should be done somewhere else instead of right here. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgindent messing up translator: comments
I just noticed that this comment got reindented by pgindent (xlog.c, line 3226 in REL9_1_STABLE): /* * translator: First %s represents a recovery.conf parameter name like * recovery_end_command, and the 2nd is the value of that parameter. */ ereport((signaled failOnSignal) ? FATAL : WARNING, (errmsg(%s \%s\: return code %d, commandName, command, rc))); Sure enough, the resulting POT entry does not have the necessary comment: #: /pgsql/source/REL9_1_STABLE/src/backend/access/transam/xlog.c:3230 #, c-format msgid %s \%s\: return code %d msgstr I think the proper fix would be to use the /* trick, such as in postmaster.c: /*-- translator: %s is a noun phrase describing a child process, such as server process */ (errmsg(%s (PID %d) exited with exit code %d, procname, pid, WEXITSTATUS(exitstatus; It seems to me that we should alert if pgindent does anything to a comment line containing translator:. -- Álvaro Herrera alvhe...@alvh.no-ip.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent messing up translator: comments
Alvaro Herrera wrote: I just noticed that this comment got reindented by pgindent (xlog.c, line 3226 in REL9_1_STABLE): /* * translator: First %s represents a recovery.conf parameter name like * recovery_end_command, and the 2nd is the value of that parameter. */ ereport((signaled failOnSignal) ? FATAL : WARNING, (errmsg(%s \%s\: return code %d, commandName, command, rc))); Sure enough, the resulting POT entry does not have the necessary comment: #: /pgsql/source/REL9_1_STABLE/src/backend/access/transam/xlog.c:3230 #, c-format msgid %s \%s\: return code %d msgstr I think the proper fix would be to use the /* trick, such as in postmaster.c: /*-- translator: %s is a noun phrase describing a child process, such as server process */ (errmsg(%s (PID %d) exited with exit code %d, procname, pid, WEXITSTATUS(exitstatus; It seems to me that we should alert if pgindent does anything to a comment line containing translator:. Well, the comment adjustments happen in the C code, which is hard to modify. We would need a wrapper that understood when it was in a C command and add /*--- markers if the word 'translator:' appeared. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] memory-related bugs
Bruce Momjian br...@momjian.us writes: Did we conclude any of these were useful? http://archives.postgresql.org/pgsql-hackers/2011-03/msg00856.php I know there were concerns about some of them in the thread. Hmm, I guess this slipped through the cracks. I thought that avoiding memcpy(x, x, n) was unnecessary, and I had doubts about the style of some of the other changes, but I think we do need to avoid accessing past the defined end of a data structure. We've seen cases in the past where one day that structure is right up against the end of memory and you get a SIGSEGV; there's no good reason to believe it cannot happen in these places. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Macros for time magic values
Alvaro Herrera wrote: Excerpts from Tom Lane's message of mar mar 15 11:42:06 -0300 2011: Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: Dimitri Fontaine dimi...@2ndquadrant.fr writes: Would it help moving toward Leap Second support, and is this something we want to have? IMO we don't want to have that, as it would completely bollix datetime calculations of all kinds. You couldn't even count on stored timestamps not changing their meaning. I'm inclined to agree, but if that's the choice, should we stop claiming that we're using UTC, and instead claim UT1 support? It always seemed a little odd to me that the docs say UTC but there's no actual support for leap seconds in calculations. Maybe, but if the docs started talking about that, we'd have to define the term every time. The number of PG users who know what UT1 is can probably be counted without running out of toes. A small note somewhere visible would suffice: these docs talk about UTC but they really mean UT1 because we have no leap seconds support. Done with the attached doc patch, backpatched to 9.1. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml new file mode 100644 index 6d5dad3..d6baf84 *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *** *** 8341,8347 The view structnamepg_timezone_names/structname provides a list of time zone names that are recognized by commandSET TIMEZONE/, along with their associated abbreviations, UTC offsets, !and daylight-savings status. Unlike the abbreviations shown in link linkend=view-pg-timezone-abbrevsstructnamepg_timezone_abbrevs/structname/link, many of these names imply a set of daylight-savings transition date rules. Therefore, the associated information changes across local DST --- 8341,8349 The view structnamepg_timezone_names/structname provides a list of time zone names that are recognized by commandSET TIMEZONE/, along with their associated abbreviations, UTC offsets, !and daylight-savings status. (Technically, !productnamePostgreSQL/productname uses acronymUT1/ rather !than UTC because leap seconds are not handled.) Unlike the abbreviations shown in link linkend=view-pg-timezone-abbrevsstructnamepg_timezone_abbrevs/structname/link, many of these names imply a set of daylight-savings transition date rules. Therefore, the associated information changes across local DST diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml new file mode 100644 index 4c3e232..c03dd6c *** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *** SELECT EXTRACT(SECOND FROM TIME '17:12:2 *** 6898,6904 para The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to ! zones west of UTC. /para /listitem /varlistentry --- 6898,6906 para The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to ! zones west of UTC. (Technically, ! productnamePostgreSQL/productname uses acronymUT1/ because ! leap seconds are not handled.) /para /listitem /varlistentry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent messing up translator: comments
Excerpts from Alvaro Herrera's message of lun sep 05 16:21:38 -0300 2011: I just noticed that this comment got reindented by pgindent (xlog.c, line 3226 in REL9_1_STABLE): /* * translator: First %s represents a recovery.conf parameter name like * recovery_end_command, and the 2nd is the value of that parameter. */ ereport((signaled failOnSignal) ? FATAL : WARNING, (errmsg(%s \%s\: return code %d, commandName, command, rc))); Actually, after I looked into Git history it turns out that this comment was introduced in this way; it wasn't pgindent's fault. I checked a couple of diffs from pgindent runs, and I found no translator: comment reindented destructively. Still, it seems possible that it could happen someday. I will fix this one occurence. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent messing up translator: comments
Alvaro Herrera alvhe...@alvh.no-ip.org writes: I think the proper fix would be to use the /* trick, such as in postmaster.c: /*-- translator: %s is a noun phrase describing a child process, such as server process */ (errmsg(%s (PID %d) exited with exit code %d, procname, pid, WEXITSTATUS(exitstatus; Ugh. Are the gettext tools so broken that they force us to use that (very ugly IMO) layout for translator: comments? Why can't we get the tools fixed instead? By and large, the people who put in those comments don't know about any specialized restrictions that gettext might have on the layout of the comment; the only documentation I've ever seen just says that the comment has to start with translator:: http://developer.postgresql.org/pgdocs/postgres/nls-programmer.html I think that if gettext can't handle the comment as it stands, that's a gettext bug, not something that both pgindent and the human code authors ought to be subservient to. Or at the very least, I want to see an exact specification for what the allowed format is, and it had better not be very magical. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove fmgr.h include in cube contrib --- caused crash on a Ge
On Mon, 5 Sep 2011, Bruce Momjian wrote: Jeremy Drake wrote: I think tomorrow I'll try to get the 9.0 compiler set up on a clean VM, and if the issue duplicates there, I can see about setting up SSH access if anyone is still interested in investigating this further. What would we investigate except a compiler bug? To me, simply chalking it up to some uncharacterized compiler bug is still quite a bit of black magic. But, if that explanation is good enough for you, I've certainly got better things to do with my holiday than spending time on this :) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove fmgr.h include in cube contrib --- caused crash on a Ge
Jeremy Drake wrote: On Mon, 5 Sep 2011, Bruce Momjian wrote: Jeremy Drake wrote: I think tomorrow I'll try to get the 9.0 compiler set up on a clean VM, and if the issue duplicates there, I can see about setting up SSH access if anyone is still interested in investigating this further. What would we investigate except a compiler bug? To me, simply chalking it up to some uncharacterized compiler bug is still quite a bit of black magic. But, if that explanation is good enough for you, I've certainly got better things to do with my holiday than spending time on this :) If the underlying tools are buggy, the system can't be reliable. We can't invest time to track down every compiler bug, especially when there are later compiler versions available. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove fmgr.h include in cube contrib --- caused crash on a Ge
Jeremy Drake pg...@jdrake.com writes: On Mon, 5 Sep 2011, Bruce Momjian wrote: What would we investigate except a compiler bug? To me, simply chalking it up to some uncharacterized compiler bug is still quite a bit of black magic. If there were some reason to believe either that it wasn't a compiler bug, or that there would be something reasonable we could do to work around it, then I'd be interested in pressing further. But on the strength of what we have now, neither of those things seem real likely. I'm with Bruce on thinking that it's probably not going to repay further effort. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent messing up translator: comments
Excerpts from Tom Lane's message of lun sep 05 16:43:32 -0300 2011: Alvaro Herrera alvhe...@alvh.no-ip.org writes: I think the proper fix would be to use the /* trick, such as in postmaster.c: /*-- translator: %s is a noun phrase describing a child process, such as server process */ (errmsg(%s (PID %d) exited with exit code %d, procname, pid, WEXITSTATUS(exitstatus; Ugh. Are the gettext tools so broken that they force us to use that (very ugly IMO) layout for translator: comments? Why can't we get the tools fixed instead? By and large, the people who put in those comments don't know about any specialized restrictions that gettext might have on the layout of the comment; the only documentation I've ever seen just says that the comment has to start with translator:: http://developer.postgresql.org/pgdocs/postgres/nls-programmer.html Well, this is all the xgettext manpage says: -cTAG, --add-comments=TAG place comment blocks starting with TAG and preceding keyword lines in output file I think nobody bothers to fix this because everyone else is using the GNU indentation style, which would make the message look like this: /* translator: %s is a noun phrase describing a child process, such as server process */ errmsg( ... ); I think that if gettext can't handle the comment as it stands, that's a gettext bug, not something that both pgindent and the human code authors ought to be subservient to. Or at the very least, I want to see an exact specification for what the allowed format is, and it had better not be very magical. Hmm. I think the only other place than the above line in the manpage where this is mentioned in the manual, is this: http://www.gnu.org/software/gettext/manual/gettext.html#Bug-Report-Address No mention of the format is done anywhere. This seems related to this (unanswered) bug report: http://savannah.gnu.org/bugs/?33451 -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
On Mon, Sep 05, 2011 at 02:51:12PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: On Mon, Sep 05, 2011 at 02:18:18PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: I'm not sure if it's upgrade thing, or is it because of error in ltree/compilation, but it looks bad. Is there any more info I could show/gather to help debug the issue? I am confused by the error --- is it not loading, or can you get a backtrace of the crash? The one in logs is not sufficient? If not - could you tell me how to make the backtrace? I'm by far not a c programmer, so for this I'd need some tutoring. I think you want this: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD While strace is useful, it doesn't show us where the C code is failing. ok. got this: (gdb) bt #0 0x7fdc28605095 in raise () from /lib/libc.so.6 #1 0x7fdc28606af0 in abort () from /lib/libc.so.6 #2 0x7fdc2863fa7b in ?? () from /lib/libc.so.6 #3 0x7fdc2864708a in ?? () from /lib/libc.so.6 #4 0x7fdc2864ac1c in free () from /lib/libc.so.6 #5 0x006c18c9 in AllocSetDelete (context=value optimized out) at aset.c:551 #6 0x006c1e54 in MemoryContextDelete (context=0xbdae80) at mcxt.c:196 #7 0x0054913e in standard_ExecutorEnd (queryDesc=0xbbb4f0) at execMain.c:360 #8 0x0051c88f in PortalCleanup (portal=0xbb7a70) at portalcmds.c:268 #9 0x006c26fc in PortalDrop (portal=0xbb7a70, isTopCommit=0 '\0') at portalmem.c:434 #10 0x005f8c95 in exec_simple_query (query_string=0xb9b980 select * from categories limit 1;) at postgres.c:1067 #11 0x005f95de in PostgresMain (argc=value optimized out, argv=value optimized out, username=value optimized out) at postgres.c:3936 #12 0x005c94f6 in ServerLoop () at postmaster.c:3555 #13 0x005ca0fe in PostmasterMain (argc=3, argv=0xaf0870) at postmaster.c:1092 #14 0x00574070 in main (argc=3, argv=0xaf0870) at main.c:188 Hope it helps. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regular logging of checkpoint progress
On 5 Září 2011, 19:17, Andy Colson wrote: Tomas, I cannot seem to see any of the patches you link here: https://commitfest.postgresql.org/action/patch_view?id=628 Looks like you need to take the out of the messageid. Sorry, fixed. Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade automatic testing
On lör, 2011-09-03 at 19:58 -0400, Tom Lane wrote: Anyway, after giving up on that I went back to plan A, namely install regress.so and friends into $libdir. That turns out to be really quite straightforward, though I had to hack pg_regress.c a bit to get its idea of $libdir to match up exactly with the way the backend sees it. (The only reason this matters is that there's one error report in the regression tests where the full expansion of $libdir is reported. Maybe we should just drop that one test case instead of maintaining the infrastructure for replacing @libdir@ in pg_regress.c.) Attached is a draft patch for HEAD. It passes make check and make installcheck on Unix, but I've not touched the MSVC scripts. Comments? I'll try to integrate this with my pg_upgrade test runner to see if it gets the job done. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
hubert depesz lubaczewski wrote: On Mon, Sep 05, 2011 at 02:51:12PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: On Mon, Sep 05, 2011 at 02:18:18PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: I'm not sure if it's upgrade thing, or is it because of error in ltree/compilation, but it looks bad. Is there any more info I could show/gather to help debug the issue? I am confused by the error --- is it not loading, or can you get a backtrace of the crash? The one in logs is not sufficient? If not - could you tell me how to make the backtrace? I'm by far not a c programmer, so for this I'd need some tutoring. I think you want this: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD While strace is useful, it doesn't show us where the C code is failing. ok. got this: (gdb) bt #0 0x7fdc28605095 in raise () from /lib/libc.so.6 #1 0x7fdc28606af0 in abort () from /lib/libc.so.6 #2 0x7fdc2863fa7b in ?? () from /lib/libc.so.6 #3 0x7fdc2864708a in ?? () from /lib/libc.so.6 #4 0x7fdc2864ac1c in free () from /lib/libc.so.6 #5 0x006c18c9 in AllocSetDelete (context=value optimized out) at aset.c:551 #6 0x006c1e54 in MemoryContextDelete (context=0xbdae80) at mcxt.c:196 #7 0x0054913e in standard_ExecutorEnd (queryDesc=0xbbb4f0) at execMain.c:360 #8 0x0051c88f in PortalCleanup (portal=0xbb7a70) at portalcmds.c:268 #9 0x006c26fc in PortalDrop (portal=0xbb7a70, isTopCommit=0 '\0') at portalmem.c:434 #10 0x005f8c95 in exec_simple_query (query_string=0xb9b980 select * from categories limit 1;) at postgres.c:1067 #11 0x005f95de in PostgresMain (argc=value optimized out, argv=value optimized out, username=value optimized out) at postgres.c:3936 #12 0x005c94f6 in ServerLoop () at postmaster.c:3555 #13 0x005ca0fe in PostmasterMain (argc=3, argv=0xaf0870) at postmaster.c:1092 #14 0x00574070 in main (argc=3, argv=0xaf0870) at main.c:188 Good. Is it possible to compile with debug symbols, -g? Odd you are crashing in libc. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
On Mon, Sep 05, 2011 at 04:43:47PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: On Mon, Sep 05, 2011 at 02:51:12PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: On Mon, Sep 05, 2011 at 02:18:18PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: I'm not sure if it's upgrade thing, or is it because of error in ltree/compilation, but it looks bad. Is there any more info I could show/gather to help debug the issue? I am confused by the error --- is it not loading, or can you get a backtrace of the crash? The one in logs is not sufficient? If not - could you tell me how to make the backtrace? I'm by far not a c programmer, so for this I'd need some tutoring. I think you want this: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD While strace is useful, it doesn't show us where the C code is failing. ok. got this: (gdb) bt #0 0x7fdc28605095 in raise () from /lib/libc.so.6 #1 0x7fdc28606af0 in abort () from /lib/libc.so.6 #2 0x7fdc2863fa7b in ?? () from /lib/libc.so.6 #3 0x7fdc2864708a in ?? () from /lib/libc.so.6 #4 0x7fdc2864ac1c in free () from /lib/libc.so.6 #5 0x006c18c9 in AllocSetDelete (context=value optimized out) at aset.c:551 #6 0x006c1e54 in MemoryContextDelete (context=0xbdae80) at mcxt.c:196 #7 0x0054913e in standard_ExecutorEnd (queryDesc=0xbbb4f0) at execMain.c:360 #8 0x0051c88f in PortalCleanup (portal=0xbb7a70) at portalcmds.c:268 #9 0x006c26fc in PortalDrop (portal=0xbb7a70, isTopCommit=0 '\0') at portalmem.c:434 #10 0x005f8c95 in exec_simple_query (query_string=0xb9b980 select * from categories limit 1;) at postgres.c:1067 #11 0x005f95de in PostgresMain (argc=value optimized out, argv=value optimized out, username=value optimized out) at postgres.c:3936 #12 0x005c94f6 in ServerLoop () at postmaster.c:3555 #13 0x005ca0fe in PostmasterMain (argc=3, argv=0xaf0870) at postmaster.c:1092 #14 0x00574070 in main (argc=3, argv=0xaf0870) at main.c:188 Good. Is it possible to compile with debug symbols, -g? Odd you are crashing in libc. this had debug: ./configure \ --prefix=/opt/pgsql-9.0.5a-int \ --enable-debug \ --disable-rpath \ --without-perl \ --without-python \ --without-tcl \ --without-openssl \ --without-pam \ --without-krb5 \ --without-gssapi \ --enable-nls \ --enable-integer-datetimes \ --enable-thread-safety \ --with-libxml \ --with-libxslt \ --without-ldap Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
hubert depesz lubaczewski wrote: Good. Is it possible to compile with debug symbols, -g? Odd you are crashing in libc. this had debug: ./configure \ --prefix=/opt/pgsql-9.0.5a-int \ --enable-debug \ --disable-rpath \ --without-perl \ --without-python \ --without-tcl \ --without-openssl \ --without-pam \ --without-krb5 \ --without-gssapi \ --enable-nls \ --enable-integer-datetimes \ --enable-thread-safety \ --with-libxml \ --with-libxslt \ --without-ldap --enable-debug adds internal debug calls, not compiler debug symbols. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Review: prepare plans of embedded sql on function start
Pavel, this patch: https://commitfest.postgresql.org/action/patch_view?id=624 It applied clean and compiled ok, but I cannot get it to work at all. $ psql Timing is on. psql (9.2devel) Type help for help. andy=# set plpgsql.prepare_plans to on_start; ERROR: unrecognized configuration parameter plpgsql.prepare_plans It was also really upset when I added it to my postgresql.conf file. I hate to split hairs, but the GUC having option on_start and on_demand seems weird. Most everything else is a yes/no. How'd you feel about renaming it to: prepare_plans_on_start = yes/no But really its not start (start might imply you call the function and it starts executing), its on create, so maybe: prepare_plans_on_create = yes/no -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
hubert depesz lubaczewski wrote: On Mon, Sep 05, 2011 at 02:51:12PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: On Mon, Sep 05, 2011 at 02:18:18PM -0400, Bruce Momjian wrote: hubert depesz lubaczewski wrote: I'm not sure if it's upgrade thing, or is it because of error in ltree/compilation, but it looks bad. Is there any more info I could show/gather to help debug the issue? I am confused by the error --- is it not loading, or can you get a backtrace of the crash? The one in logs is not sufficient? If not - could you tell me how to make the backtrace? I'm by far not a c programmer, so for this I'd need some tutoring. I think you want this: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD While strace is useful, it doesn't show us where the C code is failing. ok. got this: (gdb) bt #0 0x7fdc28605095 in raise () from /lib/libc.so.6 #1 0x7fdc28606af0 in abort () from /lib/libc.so.6 #2 0x7fdc2863fa7b in ?? () from /lib/libc.so.6 #3 0x7fdc2864708a in ?? () from /lib/libc.so.6 #4 0x7fdc2864ac1c in free () from /lib/libc.so.6 #5 0x006c18c9 in AllocSetDelete (context=value optimized out) at aset.c:551 #6 0x006c1e54 in MemoryContextDelete (context=0xbdae80) at mcxt.c:196 #7 0x0054913e in standard_ExecutorEnd (queryDesc=0xbbb4f0) at execMain.c:360 #8 0x0051c88f in PortalCleanup (portal=0xbb7a70) at portalcmds.c:268 #9 0x006c26fc in PortalDrop (portal=0xbb7a70, isTopCommit=0 '\0') at portalmem.c:434 #10 0x005f8c95 in exec_simple_query (query_string=0xb9b980 select * from categories limit 1;) at postgres.c:1067 #11 0x005f95de in PostgresMain (argc=value optimized out, argv=value optimized out, username=value optimized out) at postgres.c:3936 #12 0x005c94f6 in ServerLoop () at postmaster.c:3555 #13 0x005ca0fe in PostmasterMain (argc=3, argv=0xaf0870) at postmaster.c:1092 #14 0x00574070 in main (argc=3, argv=0xaf0870) at main.c:188 Odd it is dying in the memory freeing at executor close --- not in the ltree code. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
Bruce Momjian br...@momjian.us writes: Odd it is dying in the memory freeing at executor close --- not in the ltree code. Doesn't seem odd. The glibc complaint previously shown already indicates this is a memory stomp problem. --enable-cassert might (or might not) provide additional help. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: prepare plans of embedded sql on function start
On 09/05/2011 05:03 PM, Andy Colson wrote: Pavel, this patch: https://commitfest.postgresql.org/action/patch_view?id=624 It applied clean and compiled ok, but I cannot get it to work at all. $ psql Timing is on. psql (9.2devel) Type help for help. andy=# set plpgsql.prepare_plans to on_start; ERROR: unrecognized configuration parameter plpgsql.prepare_plans Did you add plpgsql to custom_variable_classes? It looks like you might not have. (I'm not sure why plpgsql switch should require one, though, especially since we now load plpgsql by default. It might be better just to call it plpgsql_prepare_on_start.) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade automatic testing
On 09/03/2011 07:58 PM, Tom Lane wrote: Anyway, after giving up on that I went back to plan A, namely install regress.so and friends into $libdir. That turns out to be really quite straightforward, though I had to hack pg_regress.c a bit to get its idea of $libdir to match up exactly with the way the backend sees it. (The only reason this matters is that there's one error report in the regression tests where the full expansion of $libdir is reported. Maybe we should just drop that one test case instead of maintaining the infrastructure for replacing @libdir@ in pg_regress.c.) Attached is a draft patch for HEAD. It passes make check and make installcheck on Unix, but I've not touched the MSVC scripts. Comments? This looks like it should work. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Fast GiST index build
Small bugfix: in gistBufferingFindCorrectParent check that downlinkoffnum doesn't exceed maximal offset number. -- With best regards, Alexander Korotkov. gist_fast_build-0.14.3.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: prepare plans of embedded sql on function start
On 09/05/2011 05:04 PM, Andrew Dunstan wrote: On 09/05/2011 05:03 PM, Andy Colson wrote: Pavel, this patch: https://commitfest.postgresql.org/action/patch_view?id=624 It applied clean and compiled ok, but I cannot get it to work at all. $ psql Timing is on. psql (9.2devel) Type help for help. andy=# set plpgsql.prepare_plans to on_start; ERROR: unrecognized configuration parameter plpgsql.prepare_plans Did you add plpgsql to custom_variable_classes? It looks like you might not have. (I'm not sure why plpgsql switch should require one, though, especially since we now load plpgsql by default. It might be better just to call it plpgsql_prepare_on_start.) cheers andrew Ah, yep, that was the problem, thank you. -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Large C files
Excerpts from Bruce Momjian's message of sáb sep 03 20:18:47 -0300 2011: FYI, here are all the C files with over 6k lines: - 45133 ./interfaces/ecpg/preproc/preproc.c - 33651 ./backend/parser/gram.c - 17551 ./backend/parser/scan.c 14209 ./bin/pg_dump/pg_dump.c 10590 ./backend/access/transam/xlog.c 9764 ./backend/commands/tablecmds.c 8681 ./backend/utils/misc/guc.c - 7667 ./bin/psql/psqlscan.c 7213 ./backend/utils/adt/ruleutils.c 6814 ./backend/utils/adt/selfuncs.c 6176 ./backend/utils/adt/numeric.c 6030 ./pl/plpgsql/src/pl_exec.c I have dash-marked the files that are computer-generated. It seems pg_dump.c and xlog.c should be split into smaller C files. I don't think there's any particular point to this general exercise (too large for what?), but Simon had patches (or at least ideas) to split xlog.c IIRC. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: regular logging of checkpoint progress
On 2 Září 2011, 22:04, Tom Lane wrote: Tomas Vondra t...@fuzzy.cz writes: On 2 Z?? 2011, 21:23, Tom Lane wrote: Well, to be blunt, putting stuff into the postmaster log is entirely the wrong way to satify a requirement like that. If you want to expose progress information, it should be exposed via something dynamic like pg_stat_activity. What could be useful to log is statistics that people might want to aggregate later, and I don't immediately see a reason why such stats couldn't be logged just once at end of each checkpoint cycle. The problem with pg_stat_activity is that it provides just 'current state', no history. If you don't sample that often enough, you may completely miss the checkpoint (and thus you'll have no info about it, unless you enable log_checkpoints and check the log). And it's imposible to use if you need info about something that happened in the past. And AFAIK it does not show processes running timed checkpoints for example. Your requirements seem sufficiently slippery that I don't think you've thought them through very well. As far as I can see, the proposed patch will mostly result in bloating the postmaster log with repetitive information of next to no value. I can see the reason for wanting to know what the system is doing right now, and I can see the reason for wanting aggregatable statistics so that you can tell over time whether your settings need to be adjusted. I don't see the value in a lot of 10% done log entries --- there is essentially no historical value in such, IMO, because they don't correspond to any user-level activity. (Which is what distinguishes this from, say, log_connections or log_statements.) The fact that you can't figure out a reasonable frequency for making the entries is a symptom of the design being wrong at its core. No, I probably haven't thought through all the requirements - but that's exactly the feedback I expect from a commit fest. Originally I've built the patch to fix a single use-case, where the 'log each 10%' approach made perfect sense. I'm the first one to admin this is not a good approach for the other use-cases and that the frequency should be figured out in a different way. Anyway I don't think that a piece of information that does not correspond to any user-level activity is useless. Checkpoints are often a very intrusively and significantly influence the users - that's why I believe any help to tune them is desirable. The ability to see what the system is doing right now and aggregatable statistics are fine, but in many cases they're useless (e.g. when investigating past events). Tomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Couple document fixes
On Mon, Sep 05, 2011 at 02:21:46PM -0400, Bruce Momjian wrote: Alvaro Herrera wrote: Removing CC to pg-docs so that Robert reads it. Excerpts from Bruce Momjian's message of vie mar 11 08:13:20 -0300 2011: Kevin Grittner wrote: relpersistence should be typechar/type, not typechar/type. Oddly enough, there is a difference. I am unsure on that one. We have many 'char' mentions in catalog.sgml, and I don't see any of them shown as 'char'. (Wow, we should have just called this type char1, but I think that name came from Berkeley!) The big problem is that the pg_type name is really char _without_ quotes. One idea is to rename the type to something else. We could keep char as an alias for backwards compatibility, but use the new name in system catalogs, and document it as the main name of the type. Discussed the idea a bit on IM with Bruce, but couldn't find any really good alternative. Idea floated so far: * byte (seems pretty decent to me) * octet (though maybe people would expect it'd output as a number) * char1 (looks ugly, but then we have int4 and so on) * achar (this one is just plain weird) None seems great. Thoughts? Any new ideas on how to document our char data type? What say we document it as deprecated and remove the silly thing over the next three releases or so? It's deep in the realm of micro-optimization, and of a kind we well and truly don't need any more, assuming we ever did. Alternate proposals would involve a more aggressive deprecation and removal schedule. ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rectifying wrong Date outputs
Piyush Newe wrote:
Hi,
I was randomly testing some date related stuff on PG observed that the
outputs were wrong.
e.g.
postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-YY');
to_date
3910-01-01 - Look at this
(1 row)
postgres=# SELECT TO_DATE('01-jan-2010', 'DD-MON-');
to_date
2010-01-01
(1 row)
I have done some work on this problem, and have developed the attached
patch. It genarates the output in the final column of this table:
Oracle PostgreSQL
With PG Patch
1 TO_DATE('01-jan-1', 'DD-MON-Y')01-JAN-2011 01-JAN-2001
01-JAN-2001+
2 TO_DATE('01-jan-1', 'DD-MON-YY') 01-JAN-2001 01-JAN-2001
01-JAN-2001
3 TO_DATE('01-jan-1', 'DD-MON-YYY') 01-JAN-2001 01-JAN-2001
01-JAN-2001
4 TO_DATE('01-jan-1', 'DD-MON-') 01-JAN-0001 01-JAN-0001
01-JAN-0001
5 TO_DATE('01-jan-10', 'DD-MON-Y') Error 01-JAN-2010
01-JAN-2010
6 TO_DATE('01-jan-10', 'DD-MON-YY') 01-JAN-2010 01-JAN-2010
01-JAN-2010
7 TO_DATE('01-jan-10', 'DD-MON-YYY') 01-JAN-2010 01-JAN-2010
01-JAN-2010
8 TO_DATE('01-jan-10', 'DD-MON-')01-JAN-0010 01-JAN-0010
01-JAN-0010
9 TO_DATE('01-jan-067', 'DD-MON-Y') Error 01-JAN-2067
01-JAN-2067
10 TO_DATE('01-jan-111', 'DD-MON-YY') 01-JAN-0111 01-JAN-2011
01-JAN-2111*+
11 TO_DATE('01-jan-678', 'DD-MON-YYY')01-JAN-2678 01-JAN-1678
01-JAN-1678+
12 TO_DATE('01-jan-001', 'DD-MON-') 01-JAN-0001 01-JAN-0001
01-JAN-0001
13 TO_DATE('01-jan-2010', 'DD-MON-Y') Error 01-JAN-4010
01-JAN-2010*
14 TO_DATE('01-jan-2010', 'DD-MON-YY')01-JAN-2010 01-JAN-3910
01-JAN-2010*
15 TO_DATE('01-jan-2010', 'DD-MON-YYY') Error 01-JAN-3010
01-JAN-2010*
16 TO_DATE('01-jan-2010', 'DD-MON-') 01-JAN-2010 01-JAN-2010
01-JAN-2010
I marked with '*' every case where the patch doesn't match current PG,
and used a '+' to mark every case where it doesn't match Oracle.
I know Tom was worried that because the year field took more digits than
specified, it would prevent numeric columns from being pulled apart, but
our code has this check:
if (S_FM(node-suffix) || is_next_separator(node))
{
/*
* This node is in Fill Mode, or the next node is known to be a
* non-digit value, so we just slurp as many characters as we can get.
*/
errno = 0;
result = strtol(init, src, 10);
}
The reason these tests are accepting an unlimited number of digits is
because it is at the end of the string. If you place a digit field
right after it, it will not use more characters than specified:
test= select to_date('9876', 'YYY');
to_date
9876-01-01
(1 row)
test= select to_date('9876', 'YYYMM');
to_date
1987-06-01
(1 row)
Yes, not documented, but I assume the coder was trying to be helpful.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 726a1f4..f4677af
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** static void dump_node(FormatNode *node,
*** 964,969
--- 964,970
static char *get_th(char *num, int type);
static char *str_numth(char *dest, char *num, int type);
+ static int add_era_to_partial_year(int year);
static int strspace_len(char *str);
static int strdigits_len(char *str);
static void from_char_set_mode(TmFromChar *tmfc, const FromCharDateMode mode);
*** is_next_separator(FormatNode *n)
*** 1968,1973
--- 1969,1995
return TRUE;/* some non-digit input (separator) */
}
+
+ static int
+ add_era_to_partial_year(int year)
+ {
+ /* Force 0-69 into the 2000's */
+ if (year 70)
+ return year + 2000;
+ /* Force 70-99 into the 1900's */
+ else if (year = 70 year 100)
+ return year + 1900;
+ /* Force 100-499 into the 2000's */
+ else if (year = 100 year 500)
+ return year + 2000;
+ /* Force 500-999 into the 1000's */
+ else if (year = 500 year 1000)
+ return year + 1000;
+ else
+ return year;
+ }
+
+
static int
strspace_len(char *str)
{
*** DCH_from_char(FormatNode *node, char *in
*** 2931,2972
case DCH_YYY:
case DCH_IYY:
from_char_parse_int(out-year, s, n);
out-yysz = 3;
-
- /*
- * 3-digit year: '100' ... '999' = 1100 ... 1999 '000' ...
- * '099' = 2000 ... 2099
- */
- if (out-year = 100)
- out-year += 1000;
- else
- out-year += 2000;
Re: [HACKERS] Couple document fixes
David Fetter wrote: I am unsure on that one. We have many 'char' mentions in catalog.sgml, and I don't see any of them shown as 'char'. (Wow, we should have just called this type char1, but I think that name came from Berkeley!) The big problem is that the pg_type name is really char _without_ quotes. One idea is to rename the type to something else. We could keep char as an alias for backwards compatibility, but use the new name in system catalogs, and document it as the main name of the type. Discussed the idea a bit on IM with Bruce, but couldn't find any really good alternative. Idea floated so far: * byte (seems pretty decent to me) * octet (though maybe people would expect it'd output as a number) * char1 (looks ugly, but then we have int4 and so on) * achar (this one is just plain weird) None seems great. Thoughts? Any new ideas on how to document our char data type? What say we document it as deprecated and remove the silly thing over the next three releases or so? It's deep in the realm of micro-optimization, and of a kind we well and truly don't need any more, assuming we ever did. Alternate proposals would involve a more aggressive deprecation and removal schedule. ;) Uh, pg_class uses it: relpersistence | char| not null relkind| char| not null -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: regular logging of checkpoint progress
On 3 Září 2011, 8:19, Greg Smith wrote:
If you're expanding log_checkpoints to an enum, for that to handle what
I think everybody might ever want (for what checkpoints do now at
least), I'd find that more useful if it happened like this instead:
log_checkpoints = {off, on, write, sync, verbose}
I don't think you should change the semantics of off/on, which will
avoid breaking existing postgresql.conf files and resources that suggest
tuning advice. write can toggle on what you're adding; sync should
control whether the DEBUG1 messages showing the individual file names in
the sync phase appear; and verbose can include both.
Thanks, those are definitely good ideas extending the original patch and
making it much more useful I guess.
As far as a heuristic for making this less chatty when there's nothing
exciting happening goes, I think something based on how much time has
passed would be the best one. In your use case, I would guess you don't
really care whether a message appears every n%. If I understand you
correctly now, you would mainly care about getting enough log detail to
know 1) when things are running really slow, or b) often enough that the
margin of error in your benchmark results from unaccounted checkpoint
writes is acceptable. In both of those cases, I'd think a time-based
threshold would be appropriate, and that also deals with the time-based
checkpoints, too.
Yes, the time-based threshold seems like the right solution.
If your logging criteria for the write phase was display a message any
time more than 30 seconds have passed since last seeing one, that would
give you only a few lines of output in a boring, normal
checkpoint--certainly less than the 9 in-progress samples you're
outputting now, at 10% intervals. But in the pathological situations
where writes are super slow, your log data would become correspondingly
denser, which is exactly what you want in that situation.
I still am not sure what should be a reasonable value or how to determine
it. What happens when the checkpoint_timeout is increased, there's more
shared_buffers etc.? What about using (checkpoint_timeout/10) for the
time-based checkpoints and 30s for the other checkpoints?
I think combining the two makes the most sense: log when =30 seconds
have passed since the last message, and there's been =10% more progress
made. (Maybe do the progress check before the time one, to cut down on
Is this is a good idea? The case when the timeout expires and not much
data was written is interesting, and this would not log it. But OTOH this
would nicely solve the issue with time-based checkpoints and a fixed
threshold.
Tomas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: prepare plans of embedded sql on function start
On 09/05/2011 05:27 PM, Andy Colson wrote: On 09/05/2011 05:04 PM, Andrew Dunstan wrote: On 09/05/2011 05:03 PM, Andy Colson wrote: Pavel, this patch: https://commitfest.postgresql.org/action/patch_view?id=624 It applied clean and compiled ok, but I cannot get it to work at all. $ psql Timing is on. psql (9.2devel) Type help for help. andy=# set plpgsql.prepare_plans to on_start; ERROR: unrecognized configuration parameter plpgsql.prepare_plans Did you add plpgsql to custom_variable_classes? It looks like you might not have. (I'm not sure why plpgsql switch should require one, though, especially since we now load plpgsql by default. It might be better just to call it plpgsql_prepare_on_start.) cheers andrew Ah, yep, that was the problem, thank you. -Andy However I still cannot get it to work. andy=# set plpgsql.prepare_plans to on_start; SET Time: 0.123 ms andy=# show plpgsql.prepare_plans; plpgsql.prepare_plans --- on_start (1 row) andy=# create or replace function test1(a integer) returns integer as $$ andy$# begin andy$# return b+1; andy$# end; andy$# $$ language plpgsql; CREATE FUNCTION Time: 16.926 ms andy=# Oh... shoot, having gone back and read more closely I realize I didnt understand. I thought the sql would be checked on create. That's not the case. This is what I'd hopped it was: create table junk1 ( id serial, code1 integer, ); create or replace function test2() returns integer as $$ declare x integer; begin select bob into x from junk1 where id = 4; return x; end; $$ language plpgsql; I was thinking the create function would immediately return saying, unknown column bob, and not create the function. So now with the function above, this patch has not helped me at all. I wont get an error until I exec the function. Just like without the patch. I'm not so sure how helpful that is. What is you use the if false then ... end if trick to comment out some old code? You're sill going to check the tables and fields on every exec? Pavel, is there any way to move all that code to the create function? But, then that would create a dependency where there is not one now. So that would be bad. How about a new check function test2() type of call? I think having the tables/fields checked just once would be better than checking them over and over on ever single execute. -Andy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Couple document fixes
On Mon, Sep 05, 2011 at 07:33:09PM -0400, Bruce Momjian wrote: David Fetter wrote: I am unsure on that one. We have many 'char' mentions in catalog.sgml, and I don't see any of them shown as 'char'. (Wow, we should have just called this type char1, but I think that name came from Berkeley!) The big problem is that the pg_type name is really char _without_ quotes. One idea is to rename the type to something else. We could keep char as an alias for backwards compatibility, but use the new name in system catalogs, and document it as the main name of the type. Discussed the idea a bit on IM with Bruce, but couldn't find any really good alternative. Idea floated so far: * byte (seems pretty decent to me) * octet (though maybe people would expect it'd output as a number) * char1 (looks ugly, but then we have int4 and so on) * achar (this one is just plain weird) None seems great. Thoughts? Any new ideas on how to document our char data type? What say we document it as deprecated and remove the silly thing over the next three releases or so? It's deep in the realm of micro-optimization, and of a kind we well and truly don't need any more, assuming we ever did. Alternate proposals would involve a more aggressive deprecation and removal schedule. ;) Uh, pg_class uses it: relpersistence | char| not null relkind| char| not null Interesting. :) Now that you mention it... SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE data_type = 'char'; table_schema | table_name | column_name --++--- pg_catalog | pg_proc| provolatile pg_catalog | pg_type| typtype pg_catalog | pg_type| typcategory pg_catalog | pg_type| typdelim pg_catalog | pg_type| typalign pg_catalog | pg_type| typstorage pg_catalog | pg_attribute | attstorage pg_catalog | pg_attribute | attalign pg_catalog | pg_class | relkind pg_catalog | pg_constraint | contype pg_catalog | pg_constraint | confupdtype pg_catalog | pg_constraint | confdeltype pg_catalog | pg_constraint | confmatchtype pg_catalog | pg_operator| oprkind pg_catalog | pg_rewrite | ev_type pg_catalog | pg_rewrite | ev_enabled pg_catalog | pg_trigger | tgenabled pg_catalog | pg_cast| castcontext pg_catalog | pg_cast| castmethod pg_catalog | pg_depend | deptype pg_catalog | pg_shdepend| deptype pg_catalog | pg_default_acl | defaclobjtype (22 rows) On brief inspection, it appears that each of these would be better served, at least functionally, with some kind of enumerated type. Might it be worth trying to micro-optimize this case for a one-byte enum? Or maybe something like the varvarlena pattern? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
Sorry I missed your reply, catching up now. On Wed, Aug 31, 2011 at 09:56:59PM -0400, Bruce Momjian wrote: daveg wrote: On Mon, Aug 29, 2011 at 07:49:24PM +0200, hubert depesz lubaczewski wrote: On Mon, Aug 29, 2011 at 06:54:41PM +0200, hubert depesz lubaczewski wrote: vacuumdb: vacuuming of database etsy_v2 failed: ERROR: could not access status of transaction 3429738606 DETAIL: Could not open file pg_clog/0CC6: No such file or directory. Interestingly. In old dir there is pg_clog directory with files: 0AC0 .. 0DAF (including 0CC6, size 262144) but new pg_clog has only: 0D2F .. 0DB0 File content - nearly all files that exist in both places are the same, with exception of 2 newest ones in new datadir: 3c5122f3e80851735c19522065a2d12a 0DAF 8651fc2b9fa3d27cfb5b496165cead68 0DB0 0DB0 doesn't exist in old, and 0DAF has different md5sum: 7d48996c762d6a10f8eda88ae766c5dd ... I had this same thing happen this Saturday just past and my client had to restore the whole 2+ TB instance from the previous days pg_dumps. ... After running pg_upgrade apparently successfully and analyzeing all the Update: reviewing the logs I see some of the analyzes hit the could not access status of transaction error too. tables we restarted the production workload and started getting errors: 2011-08-27 04:18:34.015 12337 c06 postgres ERROR: could not access status of transaction 2923961093 2011-08-27 04:18:34.015 12337 c06 postgres DETAIL: Could not open file pg_clog/0AE4: No such file or directory. 2011-08-27 04:18:34.015 12337 c06 postgres STATEMENT: analyze public.b_pxx; On examination the pg_clog directory contained on two files timestamped after the startup of the new cluster with 9.0.4. Other hosts that upgraded successfully had numerous files in pg_clog dating back a few days. So it appears that all the clog files went missing during the upgrade somehow. a This happened upgrading from 8.4.7 to 9.0.4, with a brief session in between at 8.4.8. We have upgraded several hosts to 9.0.4 successfully previously. I have posted this fix to the hackers email list, but I found it only affected old 8.3 servers, not old 8.4.X, so I am confused by your bug report. I have tested 8.4.X to 9.0.4 and found pg_upgrade preserves toast relfrozenxids properly in that case. Can you tell me what table is showing this error? Does it happen during vacuum? Can you run a vacuum verbose to see what it is throwing the error on? Thanks. This was upgrading from 8.4.8 to 9.0.4. I don't have the running cluster anymore, but I do have tar.gz archives of it and could probably find 2TB free somewhere to restore it to if there is something useful to extract. However, I don't think this was toast related. Most of our rows are short and have only int, float, and short text columns. These errors hit over 60 different tables mostly during the analyzes we ran immediately after the upgrade. It also hit during select, insert and delete statements. We did not run the db more than a few minutes as the damage was so extensive. As far as I can tell pg_upgrade never copied any pg_clog files from the old cluster to the new cluster. I wish I had detected that before running the remove_old_cluster.sh script. -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
daveg wrote: Can you tell me what table is showing this error? Does it happen during vacuum? Can you run a vacuum verbose to see what it is throwing the error on? Thanks. This was upgrading from 8.4.8 to 9.0.4. I don't have the running cluster anymore, but I do have tar.gz archives of it and could probably find 2TB free somewhere to restore it to if there is something useful to extract. However, I don't think this was toast related. Most of our rows are short and have only int, float, and short text columns. These errors hit over 60 different tables mostly during the analyzes we ran immediately after the upgrade. It also hit during select, insert and delete statements. We did not run the db more than a few minutes as the damage was so extensive. As far as I can tell pg_upgrade never copied any pg_clog files from the old cluster to the new cluster. I wish I had detected that before running the remove_old_cluster.sh script. Wow, no clogs? That would make the system very confused. You can pull the clogs out of the old backup and move them over if the files don't already exist. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Couple document fixes
Excerpts from David Fetter's message of lun sep 05 21:05:10 -0300 2011: On brief inspection, it appears that each of these would be better served, at least functionally, with some kind of enumerated type. Might it be worth trying to micro-optimize this case for a one-byte enum? Or maybe something like the varvarlena pattern? What would be the point? It works pretty well already. It doesn't need fixing. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Shared invalidation cache messages for temporary tables
Jim Nasby wrote: On Mar 14, 2011, at 9:29 AM, Robert Haas wrote: On Mon, Mar 14, 2011 at 10:21 AM, Bruce Momjian br...@momjian.us wrote: Since your original email is fairly unclear about what you think the problem is, it's a bit hard to speculate here, but like Simon, I don't see any obvious problem here. Maybe you're asking not so much about inserts, updates, or deletes into temporary tables but about creating and making modifications to them, which will generate catcache and relcache flushes when the pg_class/pg_attribute entries are updated. But I don't think those invalidation messages can be optimized away, since other backends can access temporary tables of other sessions in limited ways - for example, they can drop them. Sorry, yes that was my point --- should we be doing as much cache invalidation traffic for temporary tables as we are doing? I think you are saying we are fine and there are no optimizations possible. Yeah, I think so. I mean, if you have a concrete example of this causing a problem, then we can look into it, but my intuition is that it's OK. Programmers intuition are notoriously wrong, of course, so we're all just shooting in the dark until we have something to measure. Sounds like there should be a comment somewhere in the code that explains why we actually need those messages... Done. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_ctl restart - behaviour based on wrong instance
Robert Haas wrote: On Wed, Mar 23, 2011 at 1:48 AM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Mar 19, 2011 at 10:20 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 18, 2011 at 1:19 PM, Erik Rijkers e...@xs4all.nl wrote: This is OK and expected. ?But then it continues (in the logfile) with: FATAL: ?lock file postmaster.pid already exists HINT: ?Is another postmaster (PID 20519) running in data directory /var/data1/pg_stuff/pg_installations/pgsql.vanilla_1/data? So, complaints about the *other* instance. ?It doesn't happen once a successful start (with pg_ctl start) has happened. I'm guessing that leftover postmaster.pid contents might be responsible for this? The cause is that pg_ctl restart uses the postmaster.opts which was created in the primary. Since its content was something like pg_ctl -D vanilla_1/data, vanilla_1/data/postmaster.pid was checked wrongly. The simple workaround is to exclude postmaster.opts from the backup as well as postmaster.pid. But when postmaster.opts doesn't exist, pg_ctl restart cannot start up the server. We might also need to change the code of pg_ctl restart so that it does just pg_ctl start when postmaster.opts doesn't exist. Sounds reasonable. Has this been handled? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tolower() identifier downcasing versus multibyte encodings
Did we ever address this? --- Tom Lane wrote: I've been able to reproduce the behavior described here: http://archives.postgresql.org/pgsql-general/2011-03/msg00538.php It's specific to UTF8 locales on Mac OS X. I'm not sure if the problem can manifest anywhere else; considering that OS X's UTF8 locales have a general reputation of being broken, it may only happen on that platform. What is happening is that downcase_truncate_identifier() tries to downcase identifiers like this: unsigned char ch = (unsigned char) ident[i]; if (ch = 'A' ch = 'Z') ch += 'a' - 'A'; else if (IS_HIGHBIT_SET(ch) isupper(ch)) ch = tolower(ch); result[i] = (char) ch; This is of course incapable of successfully downcasing any multibyte characters, but there's an assumption that isupper() won't return TRUE for a character fragment in a multibyte locale. However, on OS X it seems that that's not the case :-(. For the particular example cited by Francisco Figueiredo, I see the byte sequence \303\251 converted to \343\251, because isupper() returns TRUE for \303 and then tolower() returns \343. The byte \251 is not changed, but the damage is already done: we now have an invalidly-encoded string. It looks like the blame for the subsequent disappearance of the bogus data lies with fprintf back on the client side; that surprises me a bit because I'd only heard of glibc being so cavalier with data it thought was invalidly encoded. But anyway, the origin of the problem is in the downcasing transformation. We could possibly fix this by not attempting the downcasing transformation on high-bit-set characters unless the encoding is single-byte. However, we have the exact same downcasing logic embedded in the functions in src/port/pgstrcasecmp.c, and those don't have any convenient way of knowing what the prevailing encoding is --- when compiled for frontend use, they can't use pg_database_encoding_max_length. Or we could bite the bullet and start using str_tolower(), but the performance implications of that are unpleasant; not to mention that we really don't want to re-introduce the Turkish problem with unexpected handling of i/I in identifiers. Or we could go the other way and stop downcasing non-ASCII letters altogether. None of these options seem terribly attractive. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
On Mon, Sep 05, 2011 at 08:19:21PM -0400, Bruce Momjian wrote: daveg wrote: Can you tell me what table is showing this error? Does it happen during vacuum? Can you run a vacuum verbose to see what it is throwing the error on? Thanks. This was upgrading from 8.4.8 to 9.0.4. I don't have the running cluster anymore, but I do have tar.gz archives of it and could probably find 2TB free somewhere to restore it to if there is something useful to extract. However, I don't think this was toast related. Most of our rows are short and have only int, float, and short text columns. These errors hit over 60 different tables mostly during the analyzes we ran immediately after the upgrade. It also hit during select, insert and delete statements. We did not run the db more than a few minutes as the damage was so extensive. As far as I can tell pg_upgrade never copied any pg_clog files from the old cluster to the new cluster. I wish I had detected that before running the remove_old_cluster.sh script. Wow, no clogs? That would make the system very confused. You can pull the clogs out of the old backup and move them over if the files don't already exist. We don't have the old cluster after running delete_old_cluster.ch. We use pg_dump for backup, so no clogs. We ended up restored 20 odd dbs totalling 2.1TB from the previous days pg_dumps. If you review my original report I mentioned that there were only 2 clog files in the new cluster both with ctime after the start of postgresql after the upgrade. I did the upgrade for three hosts at the same time, the others were fine. They have dozens of clogs dating back days before the upgrade. The failing system had only 2 recent clog. -dg -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] limit in subquery causes poor selectivity estimation
On Fri, Sep 2, 2011 at 12:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: column values). But GROUP BY or DISTINCT would entirely invalidate the column frequency statistics, which makes me think that ignoring the pg_statistic entry might be the thing to do. Comments? There's a possible problem there in that you may have trouble getting a good join selectivity estimate in cases like: SELECT ... FROM foo LEFT JOIN (SELECT x, SUM(1) FROM bar GROUP BY 1) ON foo.x = bar.x My guess is that in practice, the number of rows in foo that find a join partner here is going to be much higher than what a stats-less join selectivity estimation is likely to come up with. You typically don't write a query like this in the first place if you don't expect to find matches, although I'm sure it's been done. In some cases you might even have a foreign key relationship to work with. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
On Mon, Sep 5, 2011 at 9:14 AM, Yeb Havinga yebhavi...@gmail.com wrote: On 2011-09-01 14:40, Robert Haas wrote: userspace avc. I've committed this, but I still think it would be helpful to revise that comment. The turn boosted up is not very precise or informative. Could you submit a separate, comment-only patch to improve this? I didn't see my name as one of the reviewers in the commit message. If that is because the review was bad, I'd be interested to know what I can improve for the next one. No, it's because I flaked. Sorry, Yeb. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] toast tables on system catalogs
On Mon, Sep 5, 2011 at 1:01 PM, Bruce Momjian br...@momjian.us wrote: Alvaro Herrera wrote: Excerpts from Tom Lane's message of mar mar 01 19:03:35 -0300 2011: Alvaro Herrera alvhe...@alvh.no-ip.org writes: Strangely, we made pg_database have a toast table, and the only reason for this is datacl. Should we create toast tables for the remaining catalogs? As I commented on your blog, this is nonsense. pg_database has a TOAST table becase we thought it might need one for datconfig[]. Now that that's gone, it'd be consistent to remove the toast table, but it didn't occur to us to do that. Yeah, it occured to me to troll the git logs just after sending the email and I promptly noticed the bug in my conclusion -- there was no datacl back then; and pg_db_role_settings is very new. aclitem entries wide enough to need toasting are going to suck for all sorts of reasons (IIRC there are some O(N^2) algorithms in there, not to mention the cost of pulling in entries from a toast table on every access) so I am not excited about encouraging people to use them. I agree on not supporting large numbers of privileges, though the error message leaves a bit to be desired. Should we remove the toast table declaration for pg_database? (BTW with the relmapper mechanism, do we still need to declare the toast table OIDs?) Did we decide on this? Is it a TODO? Uh, maybe. It's not really clear that there's enough benefit here to justify someone spending time on it. If no one is feeling motivated maybe we should just let it go... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] savepoint commit performance
On Mon, Sep 5, 2011 at 1:56 PM, Andy Colson a...@squeakycode.net wrote: This patch: https://commitfest.postgresql.org/action/patch_view?id=605 Seems to have been after thoughts, and back burner stuff, and forgotten about... Has it already been commit? http://archives.postgresql.org/pgsql-committers/2011-07/msg00206.php Oh, wait, nevermind, it was revoked and reworked: http://archives.postgresql.org/pgsql-hackers/2011-07/msg01041.php but that was posted Jul 19, 2011. And the Patch linked from commitfest is Jun 6, 2011. So is that an old patch? Or a new patch? I'm confused. As far as I can see, Simon stated that he would revert it but never did so. Perhaps we should go do that... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade problem
daveg wrote: As far as I can tell pg_upgrade never copied any pg_clog files from the old cluster to the new cluster. I wish I had detected that before running the remove_old_cluster.sh script. Wow, no clogs? That would make the system very confused. You can pull the clogs out of the old backup and move them over if the files don't already exist. We don't have the old cluster after running delete_old_cluster.ch. We use pg_dump for backup, so no clogs. We ended up restored 20 odd dbs totalling 2.1TB from the previous days pg_dumps. If you review my original report I mentioned that there were only 2 clog files in the new cluster both with ctime after the start of postgresql after the upgrade. I did the upgrade for three hosts at the same time, the others were fine. They have dozens of clogs dating back days before the upgrade. The failing system had only 2 recent clog. That is certainly unusual. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] regular logging of checkpoint progress
On Mon, Sep 5, 2011 at 2:02 PM, Andy Colson a...@squeakycode.net wrote: Taking into account Noah's and Greg's Displaying accumulated autovacuum cost patch is also sending to logs, do we all now agree that this is proper way? My general impression of the thread is that nobody really wants to reject the patch (because we all know that we need a lot more logging options than we currently have) but at the same time nobody seems quite certain why someone would want to look at this precise bit of information. I mean, it's already possible to get log messages at the start and end of a checkpoint, so there's no problem with finding out whether a checkpoint was in progress at the time something was slow. In fact, you can even figure out which phase of the checkpoint you were in. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
Excerpts from Robert Haas's message of lun sep 05 23:27:16 -0300 2011: On Mon, Sep 5, 2011 at 9:14 AM, Yeb Havinga yebhavi...@gmail.com wrote: On 2011-09-01 14:40, Robert Haas wrote: userspace avc. I've committed this, but I still think it would be helpful to revise that comment. The turn boosted up is not very precise or informative. Could you submit a separate, comment-only patch to improve this? I didn't see my name as one of the reviewers in the commit message. If that is because the review was bad, I'd be interested to know what I can improve for the next one. No, it's because I flaked. Sorry, Yeb. Pity we can't use git notes. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Large C files
On Mon, Sep 5, 2011 at 6:56 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Bruce Momjian's message of sáb sep 03 20:18:47 -0300 2011: FYI, here are all the C files with over 6k lines: - 45133 ./interfaces/ecpg/preproc/preproc.c - 33651 ./backend/parser/gram.c - 17551 ./backend/parser/scan.c 14209 ./bin/pg_dump/pg_dump.c 10590 ./backend/access/transam/xlog.c 9764 ./backend/commands/tablecmds.c 8681 ./backend/utils/misc/guc.c - 7667 ./bin/psql/psqlscan.c 7213 ./backend/utils/adt/ruleutils.c 6814 ./backend/utils/adt/selfuncs.c 6176 ./backend/utils/adt/numeric.c 6030 ./pl/plpgsql/src/pl_exec.c I have dash-marked the files that are computer-generated. It seems pg_dump.c and xlog.c should be split into smaller C files. I don't think there's any particular point to this general exercise (too large for what?), but Simon had patches (or at least ideas) to split xlog.c IIRC. Yeah. xlog.c and pg_dump.c are really pretty horrible code, and could probably benefit from being split up. Actually, just splitting them up probably isn't enough: I think they need extensive refactoring. For example, ISTM that StartupXLOG() should delegate substantial chunks of what it does to subroutines, so that the toplevel function is short enough to read and understand without getting lost. On the other hand, I can't help but think splitting up numeric.c would be a bad idea all around. There's not really going to be any coherent way of dividing up the functionality in that file, and it would hinder the ability to make functions static and keep interfaces private. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
On Mon, Sep 5, 2011 at 10:52 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of lun sep 05 23:27:16 -0300 2011: On Mon, Sep 5, 2011 at 9:14 AM, Yeb Havinga yebhavi...@gmail.com wrote: On 2011-09-01 14:40, Robert Haas wrote: userspace avc. I've committed this, but I still think it would be helpful to revise that comment. The turn boosted up is not very precise or informative. Could you submit a separate, comment-only patch to improve this? I didn't see my name as one of the reviewers in the commit message. If that is because the review was bad, I'd be interested to know what I can improve for the next one. No, it's because I flaked. Sorry, Yeb. Pity we can't use git notes. Well, I guess there's no law that says we can't. Should I give it a try? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: regular logging of checkpoint progress
On Mon, Sep 5, 2011 at 7:52 PM, Tomas Vondra t...@fuzzy.cz wrote: If your logging criteria for the write phase was display a message any time more than 30 seconds have passed since last seeing one, that would give you only a few lines of output in a boring, normal checkpoint--certainly less than the 9 in-progress samples you're outputting now, at 10% intervals. But in the pathological situations where writes are super slow, your log data would become correspondingly denser, which is exactly what you want in that situation. I still am not sure what should be a reasonable value or how to determine it. What happens when the checkpoint_timeout is increased, there's more shared_buffers etc.? What about using (checkpoint_timeout/10) for the time-based checkpoints and 30s for the other checkpoints? I think the idea here is that we only need to log a message often enough that the admin who is sitting there watching this won't get too impatient waiting for the next one. As that's not a function of checkpoint_timeout, I don't see much value in conditioning this on that. +1 for the suggestion of 30s intervals - that seems infrequent enough not to result in too much log spam, but sufficiently frequent that anyone who is concerned about checkpoint progress won't have to wait terribly long to find out how things are going. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Couple document fixes
On Mon, Sep 05, 2011 at 10:07:29PM -0300, Alvaro Herrera wrote: Excerpts from David Fetter's message of lun sep 05 21:05:10 -0300 2011: On brief inspection, it appears that each of these would be better served, at least functionally, with some kind of enumerated type. Might it be worth trying to micro-optimize this case for a one-byte enum? Or maybe something like the varvarlena pattern? What would be the point? Removing the legacy char type, per original post. :) It works pretty well already. It doesn't need fixing. We've made changes as big on aesthetic grounds before, and if the change results in an enum type optimized for space efficiency, that's all to the good. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
Excerpts from Robert Haas's message of lun sep 05 23:55:33 -0300 2011: On Mon, Sep 5, 2011 at 10:52 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of lun sep 05 23:27:16 -0300 2011: On Mon, Sep 5, 2011 at 9:14 AM, Yeb Havinga yebhavi...@gmail.com wrote: On 2011-09-01 14:40, Robert Haas wrote: userspace avc. I've committed this, but I still think it would be helpful to revise that comment. The turn boosted up is not very precise or informative. Could you submit a separate, comment-only patch to improve this? I didn't see my name as one of the reviewers in the commit message. If that is because the review was bad, I'd be interested to know what I can improve for the next one. No, it's because I flaked. Sorry, Yeb. Pity we can't use git notes. Well, I guess there's no law that says we can't. Should I give it a try? I don't see why not :-) (But my guess is that you're going to need to publish some pull and push instructions, because I gather it's not trivial). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg-json
Interesting. http://pgxn.org/dist/pg-json/ Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
