Re: [HACKERS] WAL Rate Limiting

something like that committed. -- 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] WAL Rate Limiting

well it works in practice and whether users need finer control or what other sources become the next problem before we've invested a whole lot in a general solution. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] WAL Rate Limiting

On Mon, Jan 20, 2014 at 5:37 PM, Simon Riggs si...@2ndquadrant.com wrote: Agreed; that was the original plan, but implementation delays prevented the whole vision/discussion/implementation. Requirements from various areas include WAL rate limiting for replication, I/O rate limiting, hard CPU

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

On Wed, Feb 19, 2014 at 3:11 PM, Robert Haas robertmh...@gmail.com wrote: Hopefully the commit I just pushed will fix it. It now works on my machine with and without --disable-float8-byval. It builds and passes here on 32bits -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

was filled up, it hardly helps matters if it can sometimes be unlost if the earlier data happens to get written to in a particular pattern. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [HACKERS] New hook after raw parsing, before analyze

* access all 150k you still have to worry about the worst case anyway and it might be cheaper to just engineer for it in the first place. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [HACKERS] walsender doesn't send keepalives when writes are pending

happen to know (the hard way) that typical PPPoE routers *do* send LCP pings even when the link is busy so there's precedent for either behaviour. I'm guess it comes down to why you want the keepalives. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] New hook after raw parsing, before analyze

actually determine if it's superior rather than rewriting the query before it gets to that point. That would be much more flexible for users too who might not write the query in a way that exactly matches the materialized view. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

Going over this I think this is still a potential issue: On 31 Jan 2014 15:56, Andres Freund and...@2ndquadrant.com wrote: I am not sure that explains the issue, but I think the redo action for truncation is not safe across crashes. A XLOG_SMGR_TRUNCATE will just do a smgrtruncate() (and

Re: [HACKERS] New hook after raw parsing, before analyze

for the existing views doesn't change very often. I can see why a plugin might want to add rows to the views but that doesn't seem to be what this feature is about. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] New hook after raw parsing, before analyze

. But this is why our instinct is that if you want to be able to push down joins the way to do it is to extend the FDW api so that the planner can make those decisions just like it makes the decisions about internal joins rather than have an external module that takes over part of the planner's work. -- greg

Re: [HACKERS] Ctrl+C from sh can shut down daemonized PostgreSQL cluster

On 14 Feb 2014 23:07, Tom Lane t...@sss.pgh.pa.us wrote: If this is, as it sounds to be, a Solaris shell bug, doesn't it affect other daemons too? This is simmering i never exactly followed but i think if the shell doesn't support job control it's expected behaviour, not a bug. Only shells

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

I think what you're arguing is that we should see WAL records filling the rest of segment 1 before we see any references to segment 2, but if that's the case then how did we get into the situation you reported? Or is it just that it was a broken base backup to start with? The scenario I

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

did see. Andres said he thought that was due to the bug where some piece of code was mistakenly using the presence of a snapshot but I'm not clear how that can cause this though. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

in the assert already). -- 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] Recovery inconsistencies, standby much larger than primary

So here's my attempt to rewrite this logic. I ended up refactoring a bit because I found it unnecessarily confusing having the mode branches in several places. I think it's much clearer just having two separate pieces of logic for RBM_NEW and the extension cases since all they have in common is

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

the initial problem. But I think it's possible to occur without that bug so I think it still needs to be addressed. -- 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] Recovery inconsistencies, standby much larger than primary

On Wed, Feb 12, 2014 at 6:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st...@mit.edu writes: On Wed, Feb 12, 2014 at 5:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: How about the attached instead? This does possibly allocate an extra block past the target block. I'm not sure how

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

after the short segment is a reference to an invalid page since it means it's a record which predates the records which caused the extension. Obviously it would still give the error in the case we had where files were missing but then probably it should give errors in that case. -- greg -- Sent

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

On Sun, Feb 9, 2014 at 2:54 PM, Greg Stark st...@mit.edu wrote: Bad block's page header -- this is in the 56'th relation segment: =# select (page_header(E'\\x2005583b05aa050028001805002004201098e00f2090e00f088d24061885e00f')).*; lsn | tli | flags

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

On Thu, Feb 6, 2014 at 11:41 PM, Greg Stark st...@mit.edu wrote: That doesn't explain the other instance or the other copies of this database. I think the most productive thing I can do is switch my attention to the other database to see if it really looks like the same problem. So here's

Re: [HACKERS] extension_control_path

/emacs/site-lisp. The same idea as /usr/share/perl and /usr/local/share/perl or with Python or anything else. -- 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] extension_control_path

On Thu, Feb 6, 2014 at 5:49 PM, David E. Wheeler da...@justatheory.com wrote: On Feb 6, 2014, at 6:51 AM, Greg Stark st...@mit.edu wrote: Homebrew sounds kind of confused. Having a non-root user have access to make global system changes sounds like privilege escalation vulnerability by design

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

kind of race condition. The only other thread that could conceivably cause a heisenbug is the bgwriter. It's hard to imagine how a race condition in there could be so easy to hit that it would happen four times on one restore but otherwise go mostly unnoticed. -- greg -- Sent via pgsql-hackers

Re: [HACKERS] Release schedule for 9.3.3?

the corner. Bug fix releases are released when we find bugs, not on a schedule. That said, there were some bugs fixed in the last few weeks including one fairly serious one so I would expect one in not too long. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

extends the file *first* then reads in the backup block into a buffer. I can't see how it could corrupt the stack or the wal recovery buffer in the window between reading in the wal buffer and deciding to extend the relation. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

. Look at the ctids and the xids on the rows for lp 3 in the attached file for example. That second copy is from the location in the xlog record. -- greg =# select * from (select relfilenode,(pg_relation_size(oid)/8192)::integer-1 as blockno,((pg_relation_size(oid)/8192)::integer-1)/1024/128

[HACKERS] Re: Misaligned BufferDescriptors causing major performance problems on AMD

based on cachegrind profiling or something like that. Then we wouldn't need to know the oddities ourselves and optimize manually. We could maybe even do it on the build farm and select the right profile at build time by matching build target information. -- greg -- Sent via pgsql-hackers mailing

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

I've poked at this a bit more. There are at least 10 relations where the last block doesn't match the block mentioned in the xlog record that its LSN indicates. At least it looks like from the info xlogdump prints. Including two blocks where the correct block has the same LSN which maybe means

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

Hm, I'm not entirely convinced those are erroneous replays to wrong blocks. They don't look right but there are no blocks of NULs preceding them. So if they're wrong then they only extended the relations by a single block. The relfilenodes that have nul blocks before the last block are:

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

On Sun, Feb 2, 2014 at 6:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st...@mit.edu writes: The relfilenodes that have nul blocks before the last block are: Can we see the associated WAL records (ie, the ones matching the LSNs in the last blocks of these files)? Sorry, I've lost

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

1261982.54 -- 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] Recovery inconsistencies, standby much larger than primary

The plot thickens... Looking at the next relation I see a similar symptom of a single valid block at the end of several segments of nuls. This relation is also a btree on the same table and has a header in the near vicinity of the xlog: d9de7pcqls4ib6=# select

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

after crashes, etc. -- 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] Recovery inconsistencies, standby much larger than primary

of uninitialized pages and touching those pages triggers this bug. But this doesn't explain why the btree is being extended I don't think. -- 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] Recovery inconsistencies, standby much larger than primary

1261982.53 is entirely nuls. I think that's true for most if not all of the intervening files, still investigating. The 54th segment is nul up to offset 1f0c after which it has valid looking blocks: # hexdump 1261982.54 | head -100 000 * 1f0c

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

On Fri, Jan 31, 2014 at 2:39 PM, Greg Stark st...@mit.edu wrote: [cur:EA1/637140, xid:1418089147, rmid:11(Btree), len/tot_len:18/6194, info:8, prev:EA1/635290] bkpblock[1]: s/d/r:1663/16385/1261982 blk:3634978 hole_off/len:1240/2072 [cur:EA1/638988, xid:1418089147, rmid:11(Btree), len/tot_len

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

) =# select get_raw_page('data_pkey', 'main', 11073632) ; ERROR: block number 11073632 is out of range for relation data_pkey d9de7pcqls4ib6=# select relpages from pg_class where relname = 'data_pkey'; relpages -- 2889286 -- greg -- Sent via pgsql-hackers mailing list (pgsql

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

segment at offset 1F0C So unless my arithmetic is wrong: bc -l ibase=16 400 * 400 * 400 / 2000 * 54 + 1F0C / 2000 11073632 -- 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] Recovery inconsistencies, standby much larger than primary

Sorry guys. I transposed two numbers when looking up the relation. data_pk wasn't the right index. =# select (page_header(get_raw_page('index_data_id', 'main', 3020854))).* ; lsn | tli | flags | lower | upper | special | pagesize | version | prune_xid

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

+-+---+---+---+-+--+-+--- EA1/638988 | 6 | 0 | 1240 | 3312 |8176 | 8192 | 4 | 0 -- 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] Recovery inconsistencies, standby much larger than primary

So just to summarize, this xlog record: [cur:EA1/637140, xid:1418089147, rmid:11(Btree), len/tot_len:18/6194, info:8, prev:EA1/635290] insert_leaf: s/d/r:1663/16385/1261982 tid 3634978/282 [cur:EA1/637140, xid:1418089147, rmid:11(Btree), len/tot_len:18/6194, info:8, prev:EA1/635290] bkpblock[1]:

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

it would happen to the same variable twice and not to other variables. Unless there's some unrelated code writing through a wild pointer, possibly to a stack allocated object that just happens to often be that variable? -- greg On 31 Jan 2014 20:21, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

from the same base backup but it'll take time. It's a large database. Also the fear I have above is that if I set a recovery target I might make it miss the bug. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] proposal: hide application_name from other users

because it's useful. And all of that without removing a valuable debugging/tracing tool from other users. Why is application_name useful for users who aren't the DBA and aren't the user in question. The sql_query would probably be more useful than application_name but we hide that... -- greg

Re: [HACKERS] proposal: hide application_name from other users

and given the choice we prefer to have it than not. -- 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] proposal: hide application_name from other users

On Tue, Jan 28, 2014 at 11:28 AM, Greg Stark st...@mit.edu wrote: Well maybe. Or we want this useful information at a finer granularity than everyone or nobody and given the choice we prefer to have it than not. Anyways, I don't feel incredibly strongly about this. I think we should default

Re: [HACKERS] proposal: hide application_name from other users

access to sql_query but be able to check for backends running specific queries (perhaps vacuum or ddl or a known problematic query). -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [HACKERS] In-core regression tests for replication, cascading, archiving, PITR, etc. Michael Paquier

git integration based on subsytems - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201401261211 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE

Re: [HACKERS] Recovery inconsistencies, standby much larger than primary

On Sun, Jan 26, 2014 at 9:45 AM, Andres Freund and...@2ndquadrant.com wrote: Hi, On 2014-01-24 19:23:28 -0500, Greg Stark wrote: Since the point release we've run into a number of databases that when we restore from a base backup end up being larger than the primary database was. Sometimes

Re: [HACKERS] proposal: hide application_name from other users

application name for non-superusers seems like a bad solution that still only fixes a small part of the problem. It makes a lot of sense to me to treat it the same way as sql_query. It's pretty similar (especially in the above given that we put the sql query in $0 after all) -- greg -- Sent

[HACKERS] pg_get_viewdefs() indentation considered harmful

is already there. It's just that every single variation of pg_get_viewdef sets the indentation flag explicitly. I wonder if this was the intended behaviour. Shouldn't pg_get_viewdef(view, false) set indentation off? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org

[HACKERS] Recovery inconsistencies, standby much larger than primary

but I don't really understand how they could be hitting when both the primary and standby are post-upgrade to the most recent point release which have the fixes -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] pg_get_viewdefs() indentation considered harmful

| 1034 |1090 information_schema.parameters | 30497 | 3640 |3750 pg_stat_all_tables | 27193 | 1367 |1387 (20 rows) -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] pg_get_viewdefs() indentation considered harmful

Argh. Attached is a plain text file with that query data. I'll be switching back to Gnus any day now. daeqck898dvduj= select ev_class::regclass, length(ev_action) rewrite_len,length(pg_get_viewdef(ev_class,true)) prettyprint_len, length(pg_get_viewdef(ev_class,false)) non_prettyprint_len from

Re: [HACKERS] pg_get_viewdefs() indentation considered harmful

On Fri, Jan 24, 2014 at 8:49 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jan 24, 2014 at 6:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st...@mit.edu writes: We're finding it more and more common for people to define partitioned table views with hundreds or thousands of union

Re: [HACKERS] Minmax indexes

summary functions that would be interesting and could use the same infrastructure otherwise. -- 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] proposal: hide application_name from other users

, maybe you have lots of similar queries running, etc. The first step in debugging is always gathering data which is why it's so frustrating if the first thing to go down is your monitoring software. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] proposal: hide application_name from other users

On Wed, Jan 22, 2014 at 1:03 PM, Josh Berkus j...@agliodbs.com wrote: Probably Heroku has some more specific exploit case to be concerned about here; if so, might I suggest taking it up with the -security list? I don't think there's a specific vulnerability that needs to be kept secret here.

Re: [HACKERS] Hard limit on WAL space used (because PANIC sucks)

Fwiw I think all transactions lock up until space appears is *much* better than PANICing. Often disks fill up due to other transient storage or people may have options to manually increase the amount of space. it's much better if the database just continues to function after that rather than need

[HACKERS] Re: [Lsf-pc] Linux kernel impact on PostgreSQL performance (summary v2 2014-1-17)

an exact reimplementation of DIRECT_IO semantics using posix_fadvise. -- 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] [Lsf-pc] Linux kernel impact on PostgreSQL performance

to your feet. -- 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] [Lsf-pc] Linux kernel impact on PostgreSQL performance

and that none of us is really excited about doing. -- 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] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

with your new event? -- 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] Planning time in explain/explain analyze

(particularly for users coming from $OTHER-RDBMS). Perhaps we should try and come up with a solution to address those cases (turn off metrics ala turning off stats?) while not preventing us from gathering metrics on more reasonable systems. -- greg -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Planning time in explain/explain analyze

store the time in the cached plan and explain should output the time the original planning took. -- 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] Bug in visibility map WAL-logging

some dead tuples from it, it certainly should exist and be correctly initialized. Unless the vacuum subsequently truncated the file to be shorter and the backup was taken after that? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] RFC: Async query processing

by having an RPC server near the database so all the latency is handled by the RPC server and not the database connection. But it seems to me that that's unnecessary complication and shouldn't be necessary. The database protocol basically is an RPC layer. -- greg -- Sent via pgsql-hackers mailing

[HACKERS] Re: In-core regression tests for replication, cascading, archiving, PITR, etc. Michael Paquier

-- greg On 5 Jan 2014 14:54, Mark Dilger markdil...@yahoo.com wrote: I am building a regression test system for replication and came across this email thread. I have gotten pretty far into my implementation, but would be happy to make modifications if folks have improvements to suggest

Re: [HACKERS] preserving forensic information when we freeze

is a heap tuple and look in the header? And presumably throw an error if passed a non heap tuple. -- greg On 1 Jan 2014 21:34, Robert Haas robertmh...@gmail.com wrote: On Fri, Dec 27, 2013 at 9:24 PM, Stephen Frost sfr...@snowman.net wrote: I'm not sure what you mean by doesn't work, because

Re: [HACKERS] Extension Templates S03E11

for why those goals are important enough to outweigh the downsides. I don't think loading shared libraries from RAM or a temp download directory is a *complete* show stopper the way Tom says but it would require a pretty compelling use case to make it worth the difficulties it would cause. -- greg

Re: [HACKERS] Cost estimation problem on seq scan in a loop

rows from the lhs and then decides whether to do a hash or nested loop on the rhs based on how many it finds might be more tractable than most other strategies. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http

Re: [HACKERS] stats for network traffic WIP

of steaming slaves you might want to be able to identify which ones are not getting used or are dominating your network usage. -- greg On 11 Dec 2013 10:52, Tom Lane t...@sss.pgh.pa.us wrote: Atri Sharma atri.j...@gmail.com writes: On Wed, Dec 11, 2013 at 11:12 PM, Peter Eisentraut pete...@gmx.net

Re: [HACKERS] typo: XIDs are actually compared using modulo-2^32 arithmetic

in the future. -- greg On 12 Dec 2013 09:41, Tom Lane t...@sss.pgh.pa.us wrote: Gianni Ciolli gianni.cio...@2ndquadrant.it writes: It seems there is a typo here: http://www.postgresql.org/docs/devel/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND where we say that we compare XIDs

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

On 14 Dec 2013 15:40, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley dgrowle...@gmail.com writes: The attached patch is not quite finished yet, I've not yet fully covered SUM and AVG for all types. I think you *can't* cover them for the float types; roundoff error would mean you don't

Re: [HACKERS] should we add a XLogRecPtr/LSN SQL type?

Bonus points if you implement a (explicit) cast to and from timestamptz :) -- greg On 11 Dec 2013 12:41, Andres Freund and...@2ndquadrant.com wrote: Hi, There's already a couple of SQL function dealing with XLogRecPtrs and the logical replication work will add a couple of more. Currently

Re: [HACKERS] RFC: programmable file format for postgresql.conf

On 12 Dec 2013 04:20, Álvaro Hernández Tortosa a...@nosys.es wrote: Thanks, Greg. I've been going through those threads, they are quite interesting. I didn't find an answer, though, about my question: why parsing the postgresql.conf (and for instance preserving the comments while writing

Re: [HACKERS] ANALYZE sampling is too good

On Wed, Dec 11, 2013 at 12:58 AM, Simon Riggs si...@2ndquadrant.com wrote: Yes, it is not a perfect statistical sample. All sampling is subject to an error that is data dependent. Well there's random variation due to the limitations of dealing with a sample. And then there's systemic biases

Re: [HACKERS] ANALYZE sampling is too good

On Wed, Dec 11, 2013 at 11:01 AM, Greg Stark st...@mit.edu wrote: I'm not actually sure there is any systemic bias here. The larger number of rows per block generate less precise results but from my thought experiments they seem to still be accurate? So I've done some empirical tests

Re: [HACKERS] ANALYZE sampling is too good

On Wed, Dec 11, 2013 at 12:08 PM, Greg Stark st...@mit.edu wrote: The only thing I can think of is maybe the most common elements are being selected preferentially from the early part of the sample which is removing a substantial part of the lower end of the range. But even removing 100 from

Re: [HACKERS] ANALYZE sampling is too good

larger values. But that actually *is* the kind of thing that Simon's approach of just compensating with later samples can deal with. -- greg

Re: [HACKERS] ANALYZE sampling is too good

On 10 Dec 2013 08:28, Albe Laurenz laurenz.a...@wien.gv.at wrote: Doesn't all that assume a normally distributed random variable? I don't think so because of the law of large numbers. If you have a large population and sample it the sample behaves like a normal distribution when if the

Re: [HACKERS] ANALYZE sampling is too good

carefully. -- 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] ANALYZE sampling is too good

be preferable for most or all purposes. We do block sampling now. But then we select rows from those blocks uniformly. Incidentally, if you mean Surajit Chaudhuri, he's a Microsoft Research lead so I would be nervous about anything he's published being patented by Microsoft. -- greg -- Sent via

Re: [HACKERS] ANALYZE sampling is too good

On Wed, Dec 11, 2013 at 12:14 AM, Simon Riggs si...@2ndquadrant.com wrote: Block sampling, with parameter to specify sample size. +1 Simon this is very frustrating. Can you define block sampling? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] ANALYZE sampling is too good

? -- 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] Time-Delayed Standbys

On 9 Dec 2013 12:16, Craig Ringer cr...@2ndquadrant.com wrote: The only way to deal with clock drift that isn't fragile in the face of variable latency, etc, is to basically re-implement (S)NTP in order to find out what the clock difference with the remote is. There's actually an entirely

Re: [HACKERS] RFC: programmable file format for postgresql.conf

On Sat, Dec 7, 2013 at 3:28 AM, Álvaro Hernández Tortosa a...@nosys.es wrote: Right now, writing such a tool in a generic way gets so bogged down just in parsing/manipulating the postgresql.conf file that it's hard to focus on actually doing the tuning part. That was in 2008. I don't think

Re: [HACKERS] ANALYZE sampling is too good

% is nearly useless so I'm convinced you can't get good results without reading the whole table. -- 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] ANALYZE sampling is too good

On Mon, Dec 9, 2013 at 6:54 PM, Greg Stark st...@mit.edu wrote: This some math is straightforward basic statistics. The 95th percentile confidence interval for a sample consisting of 300 samples from a population of a 1 million would be 5.66%. A sample consisting of 1000 samples would have

Re: [HACKERS] ANALYZE sampling is too good

that they seem to be consistently getting less accurate as the blockiness increases. I've attached the results of pg_stats following the analyze with the various levels of blockiness. -- greg stark=# set statistics_rows_per_block = 1; SET Time: 1.363 ms stark=# analyze verbose pgbench_accounts; INFO

Re: [HACKERS] ANALYZE sampling is too good

properties like histograms or nullfrac etc. From a computer point of view it's too high to be worth bothering. If we have to read 5% of the table we might as well do a full scan anyways, it'll be marginally slower but much better quality results. -- greg -- Sent via pgsql-hackers mailing list

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

for that client doesn't continue. FATAL means a backend died. It is kind of vague how FATAL and PANIC differ. They both sound like the end of the world. If you read FATAL thinking it means the whole service is quitting -- ie what PANIC means -- then these sound like they're noise. -- greg -- Sent via

Re: [HACKERS] [RFC] Shouldn't we remove annoying FATAL messages from server log?

seen them except in the server logs and without the context might not realize that FATAL is a term of art peculiar to Postgres. -- 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] ANALYZE sampling is too good

could increase the statistics target dramatically and still be reading the same number of blocks as today. In an ideal world perhaps we could have people reading 1% of the blocks they read today and get statistics targets 10x better than today. -- greg -- Sent via pgsql-hackers mailing list (pgsql

Re: [HACKERS] ANALYZE sampling is too good

retail) or from the way Postgres works (e.g. hotter rows are on blocks with fewer rows in them and colder rows are more densely packed). I've felt for a long time that Postgres would make an excellent test bed for some aspiring statistics research group. -- greg -- Sent via pgsql-hackers

Re: [HACKERS] Why we are going to have to go DirectIO

buffer in memory which are then flushed to disk based on another scheduler. That it works at all is a testament to the quality of the code in Postgres and Linux but it's implausibly inefficient. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

Re: [HACKERS] Why we are going to have to go DirectIO

flushes that doesn't involve splitting mappings and can be efficiently controlled without having the kernel storing arbitrarily large tags on page tables or searching through all the page tables to mark pages flushable. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org

Re: [HACKERS] Extension Templates S03E11

-tenant databases to be able to have different .so files visible depending on which database is opened then that's a bit trickier. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] ANALYZE sampling is too good

after a statistically valid sample when we can spend a lot less resources and get something 90% as good. Or if we're really going to read that much data that we might as well use more of the rows we find. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes

<    1   2   3   4   5   6   7   8   9   10   >