Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

On Tue, Jul 17, 2012 at 9:16 PM, Bruce Momjian wrote: > WAL is not guaranteed to be the same between PG major versions, so doing > anything with WAL is pretty much a no-go. I understand that the WAL format changes, sometimes dramatically between versions. What I'm suggesting that the first WAL-re

Re: [HACKERS] Re: Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

Craig Ringer writes: > On 07/18/2012 08:31 AM, Tom Lane wrote: >> Not sure if we need a whole "farm", but certainly having at least one >> machine testing this sort of stuff on a regular basis would make me feel >> a lot better. > OK. That's something I can actually be useful for. > My current q

Re: [HACKERS] Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

Greg Smith writes: > On 07/17/2012 06:56 PM, Tom Lane wrote: >> Furthermore, I would say that any performance testing done since then, >> if it wasn't looking at purely read-only scenarios, isn't worth the >> electrons it's written on. In particular, any performance gain that >> anybody might hav

Re: [HACKERS] Re: Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

On 07/18/2012 08:31 AM, Tom Lane wrote: Not sure if we need a whole "farm", but certainly having at least one machine testing this sort of stuff on a regular basis would make me feel a lot better. OK. That's something I can actually be useful for. My current qemu/kvm test harness control code

Re: [HACKERS] Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

On 07/18/2012 12:00 PM, Greg Smith wrote: The second justification for the split was that it seems easier to get a low power result from, which I believe was the angle Peter Geoghegan was working when this popped up originally. The checkpointer has to run sometimes, but only at a 50% duty cyc

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

On Tue, Jul 17, 2012 at 04:49:39PM -0700, Daniel Farina wrote: > On Tue, Jul 17, 2012 at 11:55 AM, Jeff Davis wrote: > > On Tue, 2012-07-17 at 01:02 -0700, Daniel Farina wrote: > >> Could pg_upgrade emit WAL segment(s) to provide continuity of a > >> timeline? So something like: > > > > By "segme

Re: [HACKERS] Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

On 07/17/2012 06:56 PM, Tom Lane wrote: So I went to fix this in the obvious way (attached), but while testing it I found that the number of buffers_backend events reported during a regression test run barely changed; which surprised the heck out of me, so I dug deeper. The cause turns out to be

Re: [HACKERS] [PERFORM] DELETE vs TRUNCATE explanation

On 07/16/2012 02:39 PM, Robert Haas wrote: Unfortunately, there are lots of important operations (like bulk loading, SELECT * FROM bigtable, and VACUUM notverybigtable) that inevitably end up writing out their own dirty buffers. And even when the background writer does write something, it's not

Re: [HACKERS] During Xlog replaying, is there maybe emitted xlog?

Thanks a lot. oops, indeed, the clean up stage is not in the XLog replay, So there is no problem. 2012-07-18 xu2002261 发件人： Tom Lane 发送时间： 2012-07-18 10:05:26 收件人： xu2002261 抄送： pgsql-hackers 主题： Re: [HACKERS] During Xlog replaying, is there maybe emitted xlog? "xu2002261" wri

Re: [HACKERS] During Xlog replaying, is there maybe emitted xlog?

"xu2002261" writes: > Hi all, > I reviewed the source code, and saw the following calling path: > StartupXLOG() > StartupDatabase() > RmgrTable[rmid].rm_cleanup() > > btree_xlog_cleanup() > _bt_insert_parent > _bt_insertonpg() > XLogInsert() > As we can see, during xlog replaying, XLo

[HACKERS] During Xlog replaying, is there maybe emitted xlog?

Hi all, I reviewed the source code, and saw the following calling path: StartupXLOG() > StartupDatabase() > RmgrTable[rmid].rm_cleanup() > btree_xlog_cleanup() > _bt_insert_parent > _bt_insertonpg() > XLogInsert() As we can see, during xlog replaying, XLog may be emitted. So whether t

Re: [HACKERS] Re: Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

Craig Ringer writes: > On 07/18/2012 06:56 AM, Tom Lane wrote: >> This implies that nobody has done pull-the-plug testing on either HEAD >> or 9.2 since the checkpointer split went in (2011-11-01) > That makes me wonder if on top of the buildfarm, extending some > buildfarm machines into a "cras

[HACKERS] Re: Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

On 07/18/2012 06:56 AM, Tom Lane wrote: Robert Haas writes: On Mon, Jul 16, 2012 at 3:18 PM, Tom Lane wrote: BTW, while we are on the subject: hasn't this split completely broken the statistics about backend-initiated writes? Yes, it seems to have done just that. So I went to fix this in th

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

On Tue, Jul 17, 2012 at 11:55 AM, Jeff Davis wrote: > On Tue, 2012-07-17 at 01:02 -0700, Daniel Farina wrote: >> Could pg_upgrade emit WAL segment(s) to provide continuity of a >> timeline? So something like: > > By "segments" did you mean "records"? Yes. It would be nicer not to have to tie it

Re: [HACKERS] Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

On 17 July 2012 23:56, Tom Lane wrote: > This implies that nobody has done pull-the-plug testing on either HEAD > or 9.2 since the checkpointer split went in (2011-11-01), because even > a modicum of such testing would surely have shown that we're failing to > fsync a significant fraction of our w

[HACKERS] Checkpointer split has broken things dramatically (was Re: DELETE vs TRUNCATE explanation)

Robert Haas writes: > On Mon, Jul 16, 2012 at 3:18 PM, Tom Lane wrote: >> BTW, while we are on the subject: hasn't this split completely broken >> the statistics about backend-initiated writes? > Yes, it seems to have done just that. So I went to fix this in the obvious way (attached), but whil

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

On Mon, Jul 16, 2012 at 05:29:26PM -0700, Jeff Davis wrote: > On Tue, 2012-07-10 at 11:50 -0400, Bruce Momjian wrote: > > I don't think we can assume that because pg_upgrade was run on the > > master and standby that they are binary identical, can we? Technically > > the user file are identical, b

[HACKERS] New buildfarm client release

There is a new release of the PostgreSQL buildfarm client available at Most of the changes in the release are minor bug fixes. Enhancements include: * extra_config can now have a DEFAULT key, and these entries are p

Re: [HACKERS] isolation check takes a long time

On Tue, Jul 17, 2012 at 01:56:19PM -0400, Alvaro Herrera wrote: > Excerpts from Andrew Dunstan's message of dom jul 15 16:42:22 -0400 2012: > > I'm looking into that. But given that the default is to set > > max_prepared_transactions to 0, shouldn't we just remove that test from the > > normal inst

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

On Tue, 2012-07-17 at 01:02 -0700, Daniel Farina wrote: > Could pg_upgrade emit WAL segment(s) to provide continuity of a > timeline? So something like: By "segments" did you mean "records"? > * Take down the writable primary for pg_upgrade > * Some WAL is emitted and possibly archived > * The o

[HACKERS] Re: [COMMITTERS] pgsql: Split contrib documentation into extensions and programs

On ons, 2012-05-09 at 14:44 -0400, Alvaro Herrera wrote: > Excerpts from Peter Eisentraut's message of mié may 09 13:54:53 -0400 2012: > > Split contrib documentation into extensions and programs > > > > Create separate appendixes for contrib extensions and other server > > plugins on the one hand

Re: [HACKERS] pl/perl and utf-8 in sql_ascii databases

Excerpts from Kyotaro HORIGUCHI's message of mar jul 17 05:01:10 -0400 2012: > > I think that's probably too much engineering for something that doesn't > > really warrant it. A real solution to this problem could be to create > > yet another new test file containing just this function definitio

Re: [HACKERS] CompactCheckpointerRequestQueue versus pad bytes

I wrote: > I had thought that we might get a performance boost here by saving fsync > queue traffic, but I see that md.c was already not calling > register_dirty_segment for temp rels, so there's no joy there. Actually, wait a second. We were smart enough to not send fsync requests in the first p

Re: [HACKERS] several problems in pg_receivexlog

On Fri, Jul 13, 2012 at 1:15 AM, Magnus Hagander wrote: > On Thu, Jul 12, 2012 at 6:07 PM, Fujii Masao wrote: >> On Thu, Jul 12, 2012 at 8:39 PM, Magnus Hagander wrote: >>> On Tue, Jul 10, 2012 at 7:03 PM, Fujii Masao wrote: On Tue, Jul 10, 2012 at 3:23 AM, Fujii Masao wrote: > Hi, >>

Re: [HACKERS] isolation check takes a long time

Excerpts from Andrew Dunstan's message of dom jul 15 16:42:22 -0400 2012: > I'm looking into that. But given that the default is to set > max_prepared_transactions to 0, shouldn't we just remove that test from the > normal installcheck schedule? > > We could provide an alternative schedule that

Re: [HACKERS] b-tree index search algorithms

Samuel Vogel writes: > Am 17.07.12 05:21, schrieb Tom Lane: >> Samuel Vogel writes: >>> I'm currently on a university research project if performance could be >>> increased by substituting different inter-node search algorithms instead >>> of the currently used binary search. >> Hm, what have yo

Re: [HACKERS] CompactCheckpointerRequestQueue versus pad bytes

Robert Haas writes: > On Mon, Jul 16, 2012 at 9:58 PM, Tom Lane wrote: >> BTW, I wonder whether the code that checks for relfilenode conflict >> when selecting a pg_class or relfilenode OID tries both file naming >> conventions? If not, should we make it do so? > I don't believe it does, nor do

Re: [HACKERS] Covering Indexes

"David E. Wheeler" writes: > On Jul 17, 2012, at 5:32 PM, Simon Riggs wrote: >> The phrase "unindexed" seems misleading since the data is clearly in >> the index from the description on the URL you gave. And since the >> index is non-unique, I don't see any gap between Postgres and >> SQLliite4.

Re: [HACKERS] Covering Indexes

On 07/17/2012 12:41 PM, David Johnston wrote: So the question that needs to be asked is what kind of performance increase can be had during DML (insert/update) statements and whether those gains are worth pursuing. Since these other engines appear to allow both cases you should be able to get

Re: [HACKERS] Covering Indexes

On 17 July 2012 17:41, David Johnston wrote: > Concretely, I would presume that the contents of a covering index could then > look like the following (a,b,c,d): > > (2,1,2,A) > (2,1,5,A) <-- the 5 is out of natural order but exists in the "covering" > part > (2,1,3,A) > > Whereas PostgreSQL would

Re: [HACKERS] Covering Indexes

> -Original Message- > From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- > ow...@postgresql.org] On Behalf Of David E. Wheeler > Sent: Tuesday, July 17, 2012 11:55 AM > To: Simon Riggs > Cc: Pg Hackers > Subject: Re: [HACKERS] Covering Indexes > > On Jul 17, 2012, at 5:32 PM,

Re: [HACKERS] Covering Indexes

On Tue, Jul 17, 2012 at 6:08 PM, Simon Riggs wrote: > On 17 July 2012 16:54, David E. Wheeler wrote: > > Yeah, but that index is unnecessarily big if one will never use c or d > in the search. The nice thing about covering indexes as described for > SQLite 4 and implemented in MSSQL is that you

Re: [HACKERS] Covering Indexes

On 17 July 2012 16:54, David E. Wheeler wrote: > On Jul 17, 2012, at 5:32 PM, Simon Riggs wrote: > >> CREATE INDEX ON foo (a, b, c, d); >> >> allows >> >> SELECT c, d FROM foo WHERE a = ? AND b = ? >> >> to use an index only scan. >> >> The phrase "unindexed" seems misleading since the data is c

Re: [HACKERS] Covering Indexes

On Jul 17, 2012, at 5:32 PM, Simon Riggs wrote: > CREATE INDEX ON foo (a, b, c, d); > > allows > > SELECT c, d FROM foo WHERE a = ? AND b = ? > > to use an index only scan. > > The phrase "unindexed" seems misleading since the data is clearly in > the index from the description on the URL yo

Re: [HACKERS] Covering Indexes

On 17 July 2012 16:21, David E. Wheeler wrote: > On Jul 17, 2012, at 5:18 PM, Simon Riggs wrote: > >>> Now that we have index-only scans in 9.2, I'm wondering if it would make >>> sense to add covering index support, too, where additional, unindexed >>> columns are stored alongside indexed colum

Re: [HACKERS] Covering Indexes

On Jul 17, 2012, at 5:18 PM, Simon Riggs wrote: >> Now that we have index-only scans in 9.2, I'm wondering if it would make >> sense to add covering index support, too, where additional, unindexed >> columns are stored alongside indexed columns. > > Just to be clear, the ability to have covered

Re: [HACKERS] Covering Indexes

On 28 June 2012 13:16, David E. Wheeler wrote: > Very interesting design document for SQLite 4: > > http://www.sqlite.org/src4/doc/trunk/www/design.wiki > > I'm particularly intrigued by "covering indexes". For example: > > CREATE INDEX cover1 ON table1(a,b) COVERING(c,d); > > This allows t

Re: [HACKERS] Closing out the June commitfest

On 16 July 2012 01:16, Tom Lane wrote: > We are now at the end of the originally scheduled one-month window for > the June commitfest. While the numbers look fairly bad: > > Needs Review: 17, Waiting on Author: 10, Ready for Committer: 3, Committed: > 29, Returned with Feedback: 12, Rejected: 5.

Re: [HACKERS] b-tree index search algorithms

Am 17.07.12 05:21, schrieb Tom Lane: Samuel Vogel writes: I'm currently on a university research project if performance could be increased by substituting different inter-node search algorithms instead of the currently used binary search. Hm, what have you got in mind exactly? At first I wil

Re: [HACKERS] pl/perl and utf-8 in sql_ascii databases

Hello, > > I suppose that testing for the two cases and additional > > one case which runs pg_do_encoding_conversion(), say latin1, > > would be enough to confirm that encoding/decoding is properly > > done, since the concrete conversion scheme is not significant > > this case. > > > > So I recom

Re: [HACKERS] Using pg_upgrade on log-shipping standby servers

On Mon, Jul 16, 2012 at 5:29 PM, Jeff Davis wrote: > On Tue, 2012-07-10 at 11:50 -0400, Bruce Momjian wrote: >> I don't think we can assume that because pg_upgrade was run on the >> master and standby that they are binary identical, can we? Technically >> the user file are identical, but the syst