Re: [HACKERS] Enabling Checksums

2013-03-23 Thread Jim Nasby
On 3/18/13 2:25 PM, Simon Riggs wrote: On 18 March 2013 19:02, Jeff Davis pg...@j-davis.com wrote: On Sun, 2013-03-17 at 22:26 -0700, Daniel Farina wrote: as long as I am able to turn them off easily To be clear: you don't get the performance back by doing ignore_checksum_failure = on. You

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Jim Nasby
On 3/22/13 7:27 PM, Ants Aasma wrote: On Fri, Mar 22, 2013 at 10:22 PM, Merlin Moncure mmonc...@gmail.com wrote: well if you do a non-locking test first you could at least avoid some cases (and, if you get the answer wrong, so what?) by jumping to the next buffer immediately. if the non

Re: [HACKERS] Let's invent a function to report lock-wait-blocking PIDs

2013-03-23 Thread Jim Nasby
On 3/20/13 10:36 PM, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On 20 March 2013 18:02, Tom Lane t...@sss.pgh.pa.us wrote: The API that comes to mind is (name subject to bikeshedding) pg_blocking_pids(pid int) returns int[] Useful. Can we also have an SRF rather than an

Re: [HACKERS] Enabling Checksums

2013-03-23 Thread Jim Nasby
I realize Simone relented on this, but FWIW... On 3/16/13 4:02 PM, Simon Riggs wrote: Most other data we store doesn't consist of large runs of 0x00 or 0xFF as data. Most data is more complex than that, so any runs of 0s or 1s written to the block will be detected. ... It's not that uncommon

Re: [HACKERS] Enabling Checksums

2013-03-23 Thread Jim Nasby
On 3/20/13 8:41 AM, Bruce Momjian wrote: On Mon, Mar 18, 2013 at 01:52:58PM -0400, Bruce Momjian wrote: I assume a user would wait until they suspected corruption to turn it on, and because it is only initdb-enabled, they would have to dump/reload their cluster. The open question is whether

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Amit Kapila
On Saturday, March 23, 2013 9:34 AM Jim Nasby wrote: On 3/22/13 7:27 PM, Ants Aasma wrote: On Fri, Mar 22, 2013 at 10:22 PM, Merlin Moncure mmonc...@gmail.com wrote: One other interesting idea I have seen is closeable scalable nonzero indication (C-SNZI) from scalable rw-locks [1]. The

Re: [HACKERS] Single-argument variant for array_length and friends?

2013-03-23 Thread Brendan Jurd
On 22 March 2013 09:12, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 21, 2013 at 2:00 AM, Pavel Stehule pavel.steh...@gmail.com wrote: lot of postgresql functions calculate with all items in array without respect to dimensions - like unnest. so concept use outermost dim is not in pg

Re: [HACKERS] SDP query optimizer

2013-03-23 Thread Adriano Lange
Hi, On 22-03-2013 21:22, Josh Berkus wrote: Woah! Way cool. As a warning, we're in the closing throes of version 9.3 right now, so if you code/ideas doesn't get the attention it deserves, that's why. Ok. No problem. :-) There is an incomplete project from a few years back to make the

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Ants Aasma
On Sat, Mar 23, 2013 at 6:29 AM, Atri Sharma atri.j...@gmail.com wrote: One way to distribute memory contention in case of spinlocks could be to utilize the fundamentals of NUMA architecture. Specifically, we can let the contending backends spin on local flags instead on the buffer header

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Ants Aasma
On Sat, Mar 23, 2013 at 6:04 AM, Jim Nasby j...@nasby.net wrote: Partitioned clock sweep strikes me as a bad idea... you could certainly get unlucky and end up with a lot of hot stuff in one partition. Surely that is not worse than having everything in a single partition. Given a decent

Re: [HACKERS] Enabling Checksums

2013-03-23 Thread Ants Aasma
On Sat, Mar 23, 2013 at 5:14 AM, Craig Ringer cr...@2ndquadrant.com wrote: Making zero a not checksummed magic value would significantly detract from the utility of checksums IMO. FWIW using 65521 modulus to compress larger checksums into 16 bits will leave 14 non-zero values unused. Regards,

Re: [HACKERS] Enabling Checksums

2013-03-23 Thread Andres Freund
Results for pgbench scale 100: No checksums: tps = 56623.819783 Fletcher checksums: tps = 55282.222687 (1.024x slowdown) CRC Checksums: tps = 50571.324795 (1.120x slowdown) SIMD Checksums: tps = 56608.888985 (1.000x slowdown) So to conclude, the 3 approaches: Great

Re: [HACKERS] SDP query optimizer

2013-03-23 Thread Andres Freund
On 2013-03-22 20:35:43 -0300, Adriano Lange wrote: Hi all, I have developed a new query optimizer for PostgreSQL and I would like to share it with the community. The optimizer's name is Sampling and Dynamic Programming (SDP). I put it into a plugin developed some years ago, named LJQO:

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-23 Thread Andres Freund
On 2013-03-22 07:38:36 +0900, Michael Paquier wrote: Is someone planning to provide additional feedback about this patch at some point? Yes, now that I have returned from my holidays - or well, am returning from them, I do plan to. But it should probably get some implementation level review

Re: [HACKERS] Enabling Checksums

2013-03-23 Thread Ants Aasma
On Sat, Mar 23, 2013 at 3:10 PM, Andres Freund and...@2ndquadrant.com wrote: Andres showed that switching out the existing CRC for zlib's would result in 8-30% increase in INSERT-SELECT speed (http://www.postgresql.org/message-id/201005202227.49990.and...@anarazel.de) with the speeded up CRC

Re: [HACKERS] pg_dump/restore syntax checking bug?

2013-03-23 Thread Joshua D. Drake
On 03/22/2013 10:13 PM, Josh Kupershmidt wrote: On Fri, Mar 22, 2013 at 9:35 PM, Joshua D. Drake j...@commandprompt.com wrote: postgres@jd-laptop:~$ pg_restore -d test -P 'by(),hello()' foo.sqlc Note, the pg_restore doc makes no mention of trying to squeeze multiple function prototypes in

Re: [HACKERS] Enabling Checksums

2013-03-23 Thread Andres Freund
On 2013-03-23 15:36:03 +0200, Ants Aasma wrote: On Sat, Mar 23, 2013 at 3:10 PM, Andres Freund and...@2ndquadrant.com wrote: Andres showed that switching out the existing CRC for zlib's would result in 8-30% increase in INSERT-SELECT speed

Re: [HACKERS] [GENERAL] timeofday() and clock_timestamp() produce different results when casting to timestamptz

2013-03-23 Thread Tom Lane
Adrian Klaver adrian.kla...@gmail.com writes: Seems the time zone info still thinks Moscow time is being setting forward an hour for DST when in fact the time remains constant through the year. I think the discrepancy is between this in timezone/data/europe: Zone Europe/Moscow 2:30:20

[HACKERS] Request for Patch Feedback: Lag Lead Window Functions Can Ignore Nulls

2013-03-23 Thread Nicholas White
The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, [...]. This is not implemented in PostgreSQL (http://www.postgresql.org/docs/devel/static/functions-window.html) I've had a go at implementing this, and I've attached the resulting patch. It's not finished yet, but I

Re: [HACKERS] SDP query optimizer

2013-03-23 Thread Adriano Lange
On 23-03-2013 10:15, Andres Freund wrote: I just want to mention that unless you skew the statistics for the individual tables from their empty/default state this mostly measures a pretty degenerate case where optima are very rare and not very differentiated. Thats a useful thing to test, but

Re: [HACKERS] Request for Patch Feedback: Lag Lead Window Functions Can Ignore Nulls

2013-03-23 Thread Tom Lane
Nicholas White n.j.wh...@gmail.com writes: The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, [...]. This is not implemented in PostgreSQL (http://www.postgresql.org/docs/devel/static/functions-window.html) I've had a go at implementing this, and I've attached the

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Atri Sharma
Partitioned clock sweep strikes me as a bad idea... you could certainly get unlucky and end up with a lot of hot stuff in one partition. Another idea that'sbeen broughht up inthe past is to have something in the background keep a minimum number of buffers on the free list. That's how OS VM

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Jeff Janes
On Thu, Mar 21, 2013 at 9:51 PM, Atri Sharma atri.j...@gmail.com wrote: Hello all, Sorry if this is a naive question. I was going through Greg Smith's slides on buffer cache( http://www.westnet.com/~gsmith/content/postgresql/InsideBufferCache.pdf). When going through the page replacement

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Merlin Moncure
On Fri, Mar 22, 2013 at 7:27 PM, Ants Aasma a...@cybertec.at wrote: On Fri, Mar 22, 2013 at 10:22 PM, Merlin Moncure mmonc...@gmail.com wrote: well if you do a non-locking test first you could at least avoid some cases (and, if you get the answer wrong, so what?) by jumping to the next buffer

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: I'm more convinced in the other direction, new pages should enter with 0 rather than with 1. I think that the argument that a new buffer needs to be given more of an opportunity to get used again is mostly bogus. IIRC, the argument for starting at 1 not

Re: [HACKERS] Page replacement algorithm in buffer cache

2013-03-23 Thread Jeff Janes
On Fri, Mar 22, 2013 at 4:06 AM, Atri Sharma atri.j...@gmail.com wrote: Not yet, I figured this might be a problem and am designing test cases for the same. I would be glad for some help there please. Perhaps this isn't the help you were looking for, but I spent a long time looking into

Re: [HACKERS] Request for Patch Feedback: Lag Lead Window Functions Can Ignore Nulls

2013-03-23 Thread Nicholas White
Thanks - I've added it here: https://commitfest.postgresql.org/action/patch_view?id=1096 . I've also attached a revised version that makes IGNORE and RESPECT UNRESERVED keywords (following the pattern of NULLS_FIRST and NULLS_LAST). Nick On 23 March 2013 14:34, Tom Lane t...@sss.pgh.pa.us

[HACKERS] [PATCH] avoid buffer underflow in errfinish()

2013-03-23 Thread Xi Wang
CHECK_STACK_DEPTH checks if errordata_stack_depth is negative. Move the dereference of errordata[errordata_stack_depth] after the check to avoid out-of-bounds read. --- src/backend/utils/error/elog.c |4 +++- 1 file changed, 3 insertions(+), 1 deletion(-) diff --git

Re: [HACKERS] [PATCH] avoid buffer underflow in errfinish()

2013-03-23 Thread Xi Wang
A side question: at src/backend/storage/lmgr/proc.c:1150, is there a null pointer deference for `autovac'? There is a null pointer check `autovac != NULL', but the pointer is already dereferenced earlier when initializing `autovac_pgxact'. Is this null pointer check redundant, or should we move

[HACKERS] Re: [GENERAL] timeofday() and clock_timestamp() produce different results when casting to timestamptz

2013-03-23 Thread Adrian Klaver
On 03/23/2013 08:16 AM, Tom Lane wrote: Adrian Klaver adrian.kla...@gmail.com writes: Seems the time zone info still thinks Moscow time is being setting forward an hour for DST when in fact the time remains constant through the year. I think the discrepancy is between this in

Re: [HACKERS] Support for REINDEX CONCURRENTLY

2013-03-23 Thread Michael Paquier
On Sat, Mar 23, 2013 at 10:20 PM, Andres Freund and...@2ndquadrant.comwrote: On 2013-03-22 07:38:36 +0900, Michael Paquier wrote: Is someone planning to provide additional feedback about this patch at some point? Yes, now that I have returned from my holidays - or well, am returning from