Re: [HACKERS] Index corruption with CREATE INDEX CONCURRENTLY
On Sun, Feb 5, 2017 at 9:42 PM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > On Mon, Feb 6, 2017 at 5:41 AM, Peter Geoghegan <p...@bowt.ie> wrote: >> On Sun, Feb 5, 2017 at 4:09 PM, Robert Haas <robertmh...@gmail.com> wrote: >> > I don't think this kind of black-and-white thinking is very helpful. >> > Obviously, data corruption is bad. However, this bug has (from what >> > one can tell from this thread) been with us for over a decade; it must >> > necessarily be either low-probability or low-severity, or somebody >> > would've found it and fixed it before now. Indeed, the discovery of >> > this bug was driven by new feature development, not a user report. It >> > seems pretty clear that if we try to patch this and get it wrong, the >> > effects of our mistake could easily be a lot more serious than the >> > original bug. >> >> +1. The fact that it wasn't driven by a user report convinces me that >> this is the way to go. >> > > I'm not sure that just because the bug wasn't reported by a user, makes it > any less critical. As Tomas pointed down thread, the nature of the bug is > such that the users may not discover it very easily, but that doesn't mean > it couldn't be affecting them all the time. We can now correlate many past > reports of index corruption to this bug, but we don't have evidence to prove > that. Lack of any good tool or built-in checks probably makes it even > harder. > > The reason why I discovered this with WARM is because it now has a built-in > recheck logic, which was discarding some tuples returned by the index scan. > It took me lots of code review and inspection to finally conclude that this > must be an existing bug. Even then for lack of any utility, I could not > detect this easily with master. That doesn't mean I wasn't able to > reproduce, but detection was a problem. > > If you look at the reproduction setup, one in every 10, if not 5, CREATE > INDEX CONCURRENTLY ends up creating a corrupt index. That's a good 10% > probability. And this is on a low end laptop, with just 5-10 concurrent > clients running. Probability of hitting the problem will be much higher on a > bigger machine, with many users (on a decent AWS machine, I would find every > third CIC to be corrupt). Moreover the setup is not doing anything > extraordinary. Just concurrent updates which change between HOT to non-HOT > and a CIC. > Not that I am advocating that we should do a release just for this; having a fix we believe in is certainly as important a factor, but that the idea that the bug has been around a long time means it is less of an issue does seem wrong. We've certainly seen plenty of cases over the years where bugs have existed in the code in seldom used code paths, only to be exposed by new features or other code changes over time. In general, we should be less worried about the age of a bug vs our expectations that users are likely to hit that bug now, which does seem high based on the above numbers. In the meantime, it's certainly worth warning users, providing help on how to determine if this is a likely problem for them, and possibly rolling a patch ahead of upstream in cases where that's feasible. Robert Treat play: xzilla.net work: omniti.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 array_nulls?
On Thu, Dec 17, 2015 at 4:31 PM, Robert Haas <robertmh...@gmail.com> wrote: > On Wed, Dec 16, 2015 at 10:48 PM, Jim Nasby <jim.na...@bluetreble.com> wrote: >> IIUC, that means supporting backwards compat. GUCs for 10 years, which seems >> a bit excessive. Granted, that's about the worse-case scenario for what I >> proposed (ie, we'd still be supporting 8.0 stuff right now). > > Not to me. GUCs like array_nulls don't really cost much - there is no > reason to be in a hurry about removing them that I can see. > Perhaps not with rock solid consistency, but we've certainly used the argument of the "not a major major version release" to shoot down introducing incompatible features / improvements (protocol changes come to mind), which further lends credence to Jim's point about people expecting backwards incompatible breakage to be in a major major version changes. Given the overhead from a development standpoint is low, whats the better user experience: delay removal for as long as possible (~10 years) to narrow the likely of people being affected, or make such changes as visible as possible (~6+ years) so that people have clear expectations / lines of demarcation? Robert Treat play: xzilla.net work: omniti.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] Standalone synchronous master
On Wed, Jan 8, 2014 at 6:15 PM, Josh Berkus j...@agliodbs.com wrote: Stephen, I'm aware, my point was simply that we should state, up-front in 25.2.7.3 *and* where we document synchronous_standby_names, that it requires at least three servers to be involved to be a workable solution. It's a workable solution with 2 servers. That's a low-availability, high-integrity solution; the user has chosen to double their risk of not accepting writes against never losing a write. That's a perfectly valid configuration, and I believe that NTT runs several applications this way. In fact, that can already be looked at as a kind of auto-degrade mode: if there aren't two nodes, then the database goes read-only. Might I also point out that transactions are synchronous or not individually? The sensible configuration is for only the important writes being synchronous -- in which case auto-degrade makes even less sense. I really think that demand for auto-degrade is coming from users who don't know what sync rep is for in the first place. The fact that other vendors are offering auto-degrade as a feature instead of the ginormous foot-gun it is adds to the confusion, but we can't help that. I think the problem here is that we tend to have a limited view of the right way to use synch rep. If I have 5 nodes, and I set 1 synchronous and the other 3 asynchronous, I've set up a known successor in the event that the leader fails. In this scenario though, if the successor fails, you actually probably want to keep accepting writes; since you weren't using synchronous for durability but for operational simplicity. I suspect there are probably other scenarios where users are willing to trade latency for improved and/or directed durability but not at the extent of availability, don't you? In fact there are entire systems that provide that type of thing. I feel like it's worth mentioning that there's a nice primer on tunable consistency in the Riak docs; strongly recommended. http://docs.basho.com/riak/1.1.0/tutorials/fast-track/Tunable-CAP-Controls-in-Riak/. I'm not entirely sure how well it maps into our problem space, but it at least gives you a sane working model to think about. If you were trying to explain the Postgres case, async is like the N value (I want the data to end up on this many nodes eventually) and sync is like the W value (it must be written to this many nodes, or it should fail). Of course, we only offer an R = 1, W = 1 or 2, and N = all. And it's worse than that, because we have golden nodes. This isn't to say there isn't a lot of confusion around the issue. Designing, implementing, and configuring different guarantees in the presence of node failures is a non-trivial problem. Still, I'd prefer to see Postgres head in the direction of providing more options in this area rather than drawing a firm line at being a CP-oriented system. Robert Treat play: xzilla.net work: omniti.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] How to detect invisible rows caused by the relfrozenxid bug?
On Thu, Dec 5, 2013 at 4:14 PM, Omar Kilani omar.kil...@gmail.com wrote: Hi, How would one go about detecting whether they've lost rows due to the relfrozenxid? Unfortunately running 'SELECT txid_current() 2^31' on our DB returns false, and I'm a little bit worried, since we've been seeing some WeirdStuff(tm) lately. We're only 200M txids or so past 2^31. We've been working on coming up with a way to determine this, and I think we're pretty close, so if you can hang tight for a bit, hopefully we can post something. That said, if anyone else has come up with a method, I'd be interested in looking at it. Robert Treat play: xzilla.net work: omniti.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] Feature Request: pg_replication_master()
On Mon, Dec 24, 2012 at 7:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: What the patch doesn't change is the requirement to have a file that causes the server to place itself into archive recovery. So there is no more recovery.conf and instead we have a file called recovery.trigger instead. Requiring a file in order to make a server a replica is what we should be trying to get away from. It should be possible to configure a server as a replica by setting a GUC in PostgreSQL.conf (before first startup, obviously). I'm not entirely convinced about that, because if we do it like that, we will *never*, *ever* be able to store GUC settings except in a flat, editable textfile. Now, that's fine by me personally, but there seem to be a lot of people around here with ambitions to bury those settings in not-so-editable places. Including you, to judge by your next sentence: Naturally, this then links in with SET PERSISTENT or however we're calling it these days in order to take a server out of replica mode. People are going to want to be able to push a server into, and possibly out of, replica mode without necessarily having the server up at the time. So I'm not real convinced that we want that flag to be a GUC. A trigger file is a lot easier to manipulate from places like shell scripts. I'm not sure that my POV exactly matches up with Tom's, but on the last point, I strongly agree that the use of the trigger file makes it trivial to integrate Postgres warm standby management into 3rd party tools. I'm not against coming up with a new API that's better for postgres dedicated tools, but I think you're going to really make it harder for people if you eliminate the trigger file method for coming out of recovery. Robert Treat play: xzilla.net work: omniti.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] Page Checksums
On Tue, Jan 24, 2012 at 3:02 AM, jes...@krogh.cc wrote: * Robert Treat: Would it be unfair to assert that people who want checksums but aren't willing to pay the cost of running a filesystem that provides checksums aren't going to be willing to make the cost/benefit trade off that will be asked for? Yes, it is unfair of course, but it's interesting how small the camp of those using checksummed filesystems is. Don't checksumming file systems currently come bundled with other features you might not want (such as certain vendors)? I would chip in and say that I would prefer sticking to well-known proved filesystems like xfs/ext4 and let the application do the checksumming. *shrug* You could use Illumos or BSD and you'd get generally vendor free systems using ZFS, which I'd say offers more well-known and proved checksumming than anything cooking in linux land, or than the as-to-be-written yet checksumming in postgres. I dont forsee fully production-ready checksumming filesystems readily available in the standard Linux distributions within a near future. And yes, I would for sure turn such functionality on if it were present. That's nice to say, but most people aren't willing to take a 50% performance hit. Not saying what we end up with will be that bad, but I've seen people get upset about performance hits much lower than that. Robert Treat conjecture: xzilla.net consulting: omniti.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] Page Checksums
On Sat, Jan 21, 2012 at 6:12 PM, Jim Nasby j...@nasby.net wrote: On Jan 10, 2012, at 3:07 AM, Simon Riggs wrote: I think we could add an option to check the checksum immediately after we pin a block for the first time but it would be very expensive and sounds like we're re-inventing hardware or OS features again. Work on 50% performance drain, as an estimate. That is a level of protection no other DBMS offers, so that is either an advantage or a warning. Jim, if you want this, please do the research and work out what the probability of losing shared buffer data in your ECC RAM really is so we are doing it for quantifiable reasons (via old Google memory academic paper) and to verify that the cost/benefit means you would actually use it if we built it. Research into requirements is at least as important and time consuming as research on possible designs. Maybe I'm just dense, but it wasn't clear to me how you could use the information in the google paper to extrapolate data corruption probability. I can say this: we have seen corruption from bad memory, and our Postgres buffer pool (8G) is FAR smaller than available memory on all of our servers (192G or 512G). So at least in our case, CRCs that protect the filesystem cache would protect the vast majority of our memory (96% or 98.5%). Would it be unfair to assert that people who want checksums but aren't willing to pay the cost of running a filesystem that provides checksums aren't going to be willing to make the cost/benefit trade off that will be asked for? Yes, it is unfair of course, but it's interesting how small the camp of those using checksummed filesystems is. Robert Treat conjecture: xzilla.net consulting: omniti.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] Vacuum rate limit in KBps
On Mon, Jan 23, 2012 at 3:21 AM, Benedikt Grundmann bgrundm...@janestreet.com wrote: On 19/01/12 17:39, Greg Smith wrote: On 1/19/12 1:10 PM, Robert Haas wrote: I have to say that I find that intensely counterintuitive. The current settings are not entirely easy to tune correctly, but at least they're easy to explain. If there's anyone out there who has run a larger PostgreSQL database and not at some point been extremely frustrated with how the current VACUUM settings are controlled, please speak up and say I'm wrong about this. I thought it was well understood the UI was near unusably bad, it just wasn't obvious what to do about it. We are frustrated but mostly our frustration is not about the somewhat inscrutable knobs but the inscrutable meters or lack there of. I keep thinking Greg has mistaken happiness with the MB based info in the vacuum patch as being happy without the output format, when really it is all about increased visibility. (For the record, we've backpatched that initial change to a large number of our customers, just cause we're a bit zealous about monitoring). Postgres (auto or manual for that matter) vacuuming and analyzing is essentially a performance tuning problem without a good way to measure the current performance, the fact that the knobs to turn are confusing as well is secondary. What I think is missing is a clear way to know if you are vacuuming (and analyzing) enough, and how much you are paying for that. At the moment we are basically changing the knobs blindly based on some back of the envelope calculations and hearsay. Than sometimes month later we find out that eps we haven't been analyzing enough and that's why on that particular table the planner is now picking a bad query. Hmm, I've always thought the answer here is just a systematic approach to operations. We monitor free space across the system (along with a bunch of other stuff) so that we know when we're not vacuuming / analyzing enough. What I want is that page http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html to start with Here is how you know if you are vacuuming enough... In an ideal world one would like some meter in a statistics table or similar that returns a percentage 100% means just enough 50% means you have to double 150% means 50% too much (e.g. wasted)... But I could do with a boolean as well. A complicated extension and the recommendation to install 3 different extensions would be better than what is there right now but only very barely. Of course a meter wouldn't tell you that if traffic doubled you would still keep up and for that you need a complicated calculation or (you just keep looking at the meter and adjust). But at the moment there is no such meter (at least I don't know of it) and that is the actual problem. These pieces are out there. I guess I'd say they are crude, but you can get a handle on it. Of course, if your problem is with analyze, that's cheap enough that you should probably just do it more. We're probably a lot more agressive on our vacuum / analyze scale settings than some people (we cut the defaults in half as a matter of course), and I come from the don't limit stuff camp too, but by and large what we do works, even if it's more black magic than people would like. :-) Robert Treat conjecture: xzilla.net consulting: omniti.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] IDLE in transaction introspection
On Tue, Nov 15, 2011 at 12:00 PM, Greg Smith g...@2ndquadrant.com wrote: On 11/15/2011 09:44 AM, Scott Mead wrote: Fell off the map last week, here's v2 that: * RUNNING = active * all states from CAPS to lower case This looks like what I was hoping someone would add here now. Patch looks good, only issue I noticed was a spaces instead of a tab goof where you set beentry_st_state at line 2419 in src/backend/postmaster/pgstat.c Missing pieces: -There is one regression test that uses pg_stat_activity that is broken now. -The documentation should list the new field and all of the states it might include. That's a serious doc update from the minimal info available right now. I know this issue has been beat up already some, but let me summarize and extend that thinking a moment. I see two equally valid schools of thought on how exactly to deal with introducing this change: -Add the new state field just as you've done it, but keep updating the query text anyway. Do not rename current_query. Declare the overloading of current_query as both a state and the query text to be deprecated in 9.3. This would keep existing tools working fine against 9.2 and give a clean transition period. -Forget about backward compatibility and just put all the breaking stuff we've been meaning to do in here. If we're going to rename current_query to query--what Scott's patch does here--that will force all code using pg_stat_activity to be rewritten. This seems like the perfect time to also change procpid to pid, finally blow away that wart. +1 I'll happily update all of the tools and samples I deal with to support this change. Most of the ones I can think of will be simplified; they're already parsing query_text and extracting the implicit state. Just operating on an explicit one instead will be simpler and more robust. lmk if you need help, we'll be doing this with some of our tools / projects anyway, it probably wouldn't hurt to coordinate. Robert Treat conjecture: xzilla.net consulting: omniti.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] Working with git repo tagged versions
Howdy folks, Occasionally I need to grab an older release from git based on a tag rather than a branch, eg the REL8_3_10 tag vs the REL8_3_STABLE branch. I used to know how to do this in CVS, but I find I tend to revert to grabbing tarballs now that we're on git. So, I'm wondering if anyone knows a way to do this directly from git clone (or similar), and ideally as a shallow clone (ie. I just need a copy of the code at that tag, rather than needing the repo for development purposes). If anyone knew a way to do this down to a specific commit, that would also be interesting to know. Thanks in advance. Robert Treat conjecture: xzilla.net consulting: omniti.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] Measuring relation free space
On Tue, Nov 8, 2011 at 7:19 PM, Greg Smith g...@2ndquadrant.com wrote: On 11/08/2011 05:07 PM, Robert Treat wrote: It's already easy to get good enough numbers based on user space tools with very little overhead, so I think it's more important that the server side tool be accurate rather than fast. What user space method do you consider good enough here? I haven't found any approximation that I was really happy with; wouldn't have bothered with this otherwise. check_postgres and the pg_bloat_report both use a method of comparing on disk size vs estimated size based on table structure (or index info). Run regularly, it's certainly possible to keep bloat under control. That said, I'd still like to see something more accurate. Robert Treat conjecture: xzilla.net consulting: omniti.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] Measuring relation free space
On Tue, Nov 8, 2011 at 1:07 PM, Greg Smith g...@2ndquadrant.com wrote: On 11/06/2011 11:55 PM, Mark Kirkwood wrote: I am guessing (at this point very much guessing) that pg_freespace map may return its data faster, as pageinspect is gonna have to grovel through all the pages for itself (whereas pg_freespacemap relies on using info from the ... free space map fork). I started with pageinspect because I wasn't sure if other methods would be as accurate. For example, I wasn't sure until just before submission that only the free space and size of the relation are needed to get a useful measure here; at one point I was considering some other things too. I've ruled them out as unnecessary as far as I can tell, but I can't claim my tests are exhaustive. Some review confirmation that this measure is useful for other people is one thing I was hoping for feedback on, as one thing to consider in addition to the actual implementation. If this measurement is the only one needed, than as I said at the start of the thread here it might easily be implemented to run just against the free space map instead. I'm thinking of what's been sent so far as a UI with matching output it should produce. If it's possible to get the same numbers faster, exactly how to implement the function under the hood is easy enough to change. Jaime already has a new version in development that adds a ring buffer for example. It's already easy to get good enough numbers based on user space tools with very little overhead, so I think it's more important that the server side tool be accurate rather than fast. Of course, if we can get both, that's a bonus, but I'd rather not go that route at the expense of accuracy. Just my .02. Robert Treat conjecture: xzilla.net consulting: omniti.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] IDLE in transaction introspection
On Fri, Nov 4, 2011 at 3:28 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Nov 4, 2011 at 2:46 PM, Scott Mead sco...@openscg.com wrote: If waiting == true, then state = WAITING else state = appropriate state No, I think the state and waiting columns should be completely independent. If they aren't I don't think we need both columns. +1 for leaving them independent though. Robert Treat play: xzilla.net work: omniti.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] IDLE in transaction introspection
On Fri, Nov 4, 2011 at 10:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: I guess with the changes that showed different thing like fastpath, that makes sense. But if we just mapped the states that are there today straight off, is there any case where waiting can be true, when we're either idle or idle in transaction? I think not.. I'm not totally sure about that. I know that it's possible to see idle waiting in the ps display, but that comes from getting blocked in parse analysis before the command type has been determined. The pg_stat_activity string is set a bit earlier, so *maybe* it's impossible there. Still, why wire such an assumption into the structure of the view? Robert's point about sinval catchup is another good one, though I don't remember what that does to the pg_stat_activity display. BTW, a quick grep shows that there are four not two special values of the activity string today: src/backend/tcop/postgres.c: 3792: pgstat_report_activity(IDLE in transaction (aborted)); src/backend/tcop/postgres.c: 3797: pgstat_report_activity(IDLE in transaction); src/backend/tcop/postgres.c: 3805: pgstat_report_activity(IDLE); src/backend/tcop/postgres.c: 3925: pgstat_report_activity(FASTPATH function call); It's also worth considering whether the autovacuum: that's prepended by autovac_report_activity() ought to be folded into the state instead of continuing to put something that's not valid SQL into the query string. Well, it would be interesting to see rows for autovacuum or replication processes showing up in pg_stat_activity with a corresponding state (autovacuum, walreciever?) and the query field showing what they are working on, at the risk that we'd need to build more complex parsing into the various monitoring scripts, but I guess it's no worse than before (and I guess probably easier on some level). Robert Treat play: xzilla.net work: omniti.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] unite recovery.conf and postgresql.conf
On Tue, Nov 1, 2011 at 2:34 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, Nov 1, 2011 at 6:12 PM, Robert Treat r...@xzilla.net wrote: On Tue, Nov 1, 2011 at 1:34 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, Nov 1, 2011 at 5:11 PM, Joshua Berkus j...@agliodbs.com wrote: So, we have four potential paths regarding recovery.conf: 1) Break backwards compatibility entirely, and stop supporting recovery.conf as a trigger file at all. Note that is exactly what I have suggested when using standby mode from pg_ctl. But you already know that, since you said If it's possible to run a replica without having a recovery.conf file, then I'm fine with your solution, and I already confirmed back to you that would be possible. It's possible to run a replica without having a recovery.conf file is not the same thing as If someone makes a recovery.conf file, it won't break my operations. AIUI, you are not supporting the latter. Yes, that is part of the combined proposal, which allows both old and new APIs. New API pg_ctl standby will startup a server in standby mode, do not implicitly include recovery.conf and disallow recovery_target parameters in postgresql.conf (you may, if you wish, explicitly have include recovery.conf in your postgresql.conf, should you desire that) Old API pg_ctl start and a recovery.conf has been created will startup a server in PITR and/or replication, recovery.conf will be read automatically (as now) so the presence of the recovery.conf acts as a trigger, only if we issue start So the existing syntax works exactly as now, but a new API has been created to simplify things in exactly the way requested. The old and the new API don't mix, so there is no confusion between them. You must still use the old API when you wish to perform a PITR, as explained by me, following comments by Peter. Ah, thanks for clarifying, your earlier proposal didn't read that way to me. It still doesn't solve the problem for tool makers of needing to be able to deal with two possible implementation methods, but it should be easier for them to make a choice. One thing though, I think it would be better to have this work the other way around. ISTM we're going to end up telling people to avoid using pg_ctl start and instead use pg_ctl standby, which doesn't feel like the right answer. Ie. Starting in 9.2, you should use pg_ctl standby to launch your database for normal operations and/or in cases where you are writing init scripts to control your production databases. For backwards compatibility, if you require the old behavior of using a recovery.conf, we would recommend you use pg_ctl start instead. Robert Treat conjecture: xzilla.net consulting: omniti.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] IDLE in transaction introspection
On Tue, Nov 1, 2011 at 1:20 PM, Magnus Hagander mag...@hagander.net wrote:
On Tue, Nov 1, 2011 at 18:11, Scott Mead sco...@openscg.com wrote:
On Tue, Nov 1, 2011 at 10:40 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
On Tue, Nov 1, 2011 at 9:52 AM, Tom Lane t...@sss.pgh.pa.us wrote:
That would cost twice as much shared memory for query strings, and
twice
as much time to update the strings, for what seems pretty marginal
value. I'm for just redefining the query field as current or last
query.
Not really. You could just store it once in shared memory, and put
the complexity in the view definition.
I understood the proposal to be store the previous query in addition
to the current-query-if-any. If that's not what was meant, then my
objection was incorrect. However, like you, I'm pretty dubious of
having two mostly-redundant fields in the view definition, just because
of window width issues.
The biggest reason I dislike the multi-field approach is because it limits
us to only the [single] previous_query in the system with all the overhead
we talked about (memory, window width and messing with system catalogs in
general). That's actually why I implemented it the way I did, just by
appending the last query on the end of the string when it's IDLE in
transaction.
Well, by appending it in that field, you require the end
user/monitoring app to parse out the string anyway, so you're not
exactly making life easier on the consumer of the information..
+1
Marti wrote:
I'd very much like to see a more generic solution: a runtime query log
facility that can be queried in any way you want. pg_stat_statements
comes close, but is limited too due to its (arbitrary, I find)
deduplication -- you can't query for 10 last statements from process
N since it has no notion of processes, just users and databases.
This is what I'd really like to see (just haven't had time as it is a much
bigger project). The next question my devs ask is what were the last five
queries that ran... can you show me an overview of an entire transaction
etc...
That being said, having the previous_query available feels like it fixes
about 80% of the *problem*; transaction profiling, or looking back 10 / 15 /
20 queries is [immensely] useful, but I find that the bigger need is the
ability to short-circuit dba / dev back-n-forth by just saying Your app
refused to commit/rollback after running query XYZ.
This would be great, but as you say, it's a different project.
+1 (but I'd like to see that different project)
Perhaps something could be made along the line of each backend keeping
it's *own* set of old queries, and then making it available to a
specific function (pg_get_last_queries_for_backend(nnn)) - since
this is not the type of information you'd ask for often, it would be
ok if getting this data took longer.. And you seldom want give me the
last 5 queries for each backend at once.
Robert Wrote:
Yeah. Otherwise, people who are parsing the hard-coded strings idle
and idle in transaction are likely to get confused.
I would be interested ( and frankly very surprised ) to find out if many
monitoring tools are actually parsing that field. Most that I see just dump
whatever is in current_query to the user. I would imaging that, so long as
the server obeyed pgstat_track_activity_size most tools would behave nicely.
Really? I'd assume every single monitoring tool that graphs how many
active connections you have (vs idle vs idle in transaction) would do
just this.
Having written and/or patched dozens of these types of things, your
spot on; all of the ones with anything other than the most brain dead
of monitoring parse for IDLE and IDLE in transaction. That said, I'm
happy to see the {active|idle|idle in txn} status field and
query_string fields show up and break backwards compatibility.
Updating the tools will be simple for those who need it, and make a
view to work around it will be simple for those who don't. Happy to
add an example view definition to the docs if it will help.
Robert Treat
conjecture: xzilla.net
consulting: omniti.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] unite recovery.conf and postgresql.conf
On Tue, Nov 1, 2011 at 1:34 PM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, Nov 1, 2011 at 5:11 PM, Joshua Berkus j...@agliodbs.com wrote: So, we have four potential paths regarding recovery.conf: 1) Break backwards compatibility entirely, and stop supporting recovery.conf as a trigger file at all. Note that is exactly what I have suggested when using standby mode from pg_ctl. But you already know that, since you said If it's possible to run a replica without having a recovery.conf file, then I'm fine with your solution, and I already confirmed back to you that would be possible. It's possible to run a replica without having a recovery.conf file is not the same thing as If someone makes a recovery.conf file, it won't break my operations. AIUI, you are not supporting the latter. Robert Treat conjecture: xzilla.net consulting: omniti.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] unite recovery.conf and postgresql.conf
On Mon, Oct 31, 2011 at 3:19 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Oct 31, 2011 at 7:05 PM, Josh Berkus j...@agliodbs.com wrote: If it's possible to run a replica without having a recovery.conf file, then I'm fine with your solution. If it's not, then I find your solution not to be a solution at all. Then you are fine with the solution - not mine alone, just the sum of everybody's inputs. So we can teach the new way, while supporting the old way a while longer. In most cases we either break backwards compatibility or require some type of switch to turn on backwards compatibility for those who want it. While the above plan tries to do one better, it leaves me feeling that the thing I don't like about this is that it sounds like you are forcing backwards compatibility on people who would much rather just do things the new way. Given that, I foresee a whole new generation of confused users who end up setting their configs one way only to have someone else set the same config in the other file, or some tool dump out some config file, overriding what was really intended. This will also make things *harder* for those tool providers you are trying to help, as they will be forced to support the behavior *both ways*. I'd much rather see some type of switch which turns on the old behavior for those who really want it, because while you can teach the new behavior, if you can't prevent the old behavior, you're creating operational headaches for yourself. Robert Treat conjecture: xzilla.net consulting: omniti.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] postgresql.conf archive_command example
On Tue, Sep 6, 2011 at 10:11 PM, Fujii Masao masao.fu...@gmail.com wrote: On Sat, Sep 3, 2011 at 5:10 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: (2) It should copy, not move, with protection against overwriting an existing file. I agree that basically archive_command should not overwrite an existing file. But if the size of existing file is less than 16MB, it should do that. Otherwise, that WAL file would be lost forever. I think best practice in this case is that if you ever find an existing file with the same name already in place, you should error and investigate. We don't ship around partially completed WAL files, and finding an existing one probably means something went wrong. (Of course, we use rsync instead of copy/move, so we have some better guarantees about this). I have another feature request; (5) Maybe not in the initial version, but eventually it might be nice to support calling posix_fadvise(POSIX_FADV_DONTNEED) after copying a WAL file. Can you go into more details on how you envision this working. I'm mostly curious because I think rsync might already support this, which would make it easy to incorporate. On a side note, seeing this thread hasn't died, I'd encourage everyone to take another look at OmniPITR, https://github.com/omniti-labs/omnipitr. It's postgresql licensed, solves a lot of the problems listed here, and I think makes for a good example for people who want to accomplish more advanced awl management goals. So far the biggest criticism we've gotten is that it wasn't written in python, for some of you that might be a plus though ;-) Robert Treat play: xzilla.net work: omniti.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] Informix FDW - anybody working on this?
On Wed, Aug 31, 2011 at 8:13 AM, Bernd Helmle maili...@oopsware.de wrote: Out of curiosity, is anybody working on $subject? I'm currently planning to work on such a driver, but given the current stream of new drivers i want to make sure to not duplicate any efforts... The most complete list I've seen of FDW's is on the wiki: http://wiki.postgresql.org/wiki/Foreign_data_wrappers Note there is an ODBC FDW, which might work, but if you start working on an Informix specific one, please add it there. (I say this, knowing that one of my co-workers has the outlines of a riak fdw he hasn't listed yet... guess I should go pester him). Robert Treat conjecture: xzilla.net consulting: omniti.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] branching for 9.2devel
On Sat, Apr 30, 2011 at 5:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Joshua Berkus j...@agliodbs.com wrote: I just searched backwards on this thread and I can't find it. I think he's talking about the bottom of this post: http://archives.postgresql.org/message-id/BANLkTimnjZNemdpqgK=8Mj=pzq33pz0...@mail.gmail.com ... which was: CF #1: June 1-30 CF #2: August 1-31 CF #3: October 1-31 CF #4 (one week shortened CF): December 1-7 CF #5: January 1-31 I think the main thing we have to think about before choosing is whether we believe that we can shorten the CFs at all. Josh's proposal had 3-week CFs after the first one, which makes it a lot easier to have a fest in November or December, but only if you really can end it on time. If we made the deadline for patch acceptance into 9.2 CF#4, then shortening that to a two week cycle whose main goal was simply to sanity check patches for 9.2 would probably work. Most would probably still need further work, which we would expect to get handled in the final, full CF#5, but we wouldn't let anything new come into CF#5. This way when we get the 100 patch pile up in CF#4, there's no expectation that those patches will be committed, just that they can be sanity checked for the 9.2 release. Robert Treat play: xzilla.net work: omniti.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] branching for 9.2devel
On Sun, May 1, 2011 at 1:14 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Treat r...@xzilla.net wrote: Tom Lane t...@sss.pgh.pa.us wrote: CF #1: June 1-30 CF #2: August 1-31 CF #3: October 1-31 CF #4 (one week shortened CF): December 1-7 CF #5: January 1-31 I think the main thing we have to think about before choosing is whether we believe that we can shorten the CFs at all. Josh's proposal had 3-week CFs after the first one, which makes it a lot easier to have a fest in November or December, but only if you really can end it on time. If we made the deadline for patch acceptance into 9.2 CF#4, then shortening that to a two week cycle whose main goal was simply to sanity check patches for 9.2 would probably work. Most would probably still need further work, which we would expect to get handled in the final, full CF#5, but we wouldn't let anything new come into CF#5. This way when we get the 100 patch pile up in CF#4, there's no expectation that those patches will be committed, just that they can be sanity checked for the 9.2 release. Which makes it not really a CommitFest, but rather ... a SanityFest? To make sure I understand you, you're suggesting no WIP patch review in the last two CFs? (Of course nothing stops someone from looking at someone else's WIP between fests.) Would a patch submitted to #4, the sanity of which was questioned, be eligible for another try in #5. I think you can have WIP patches for both CF#4 and CF#5. What we're hoping to get from CF#4 is a better scope on the number of patches we might have to get 9.2 out the door. WRT patches whose sanity is questioned, I'd presume that questioning would have a list of specific complaints, so if you address those between CF#4 and CF#5, I don't see why you can't try again. Robert Treat play: xzilla.net work: omniti.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] why is max standby delay only 35 minutes?
On Fri, Mar 4, 2011 at 2:03 AM, Magnus Hagander mag...@hagander.net wrote: On Fri, Mar 4, 2011 at 04:00, Robert Treat r...@xzilla.net wrote: I have a server where I wanted to do some reporting on a standby, and wanted to set the max standby delay to 1 hour. upon doing that, i get this in the logs: 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG: received SIGHUP, reloading configuration files 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG: 360 is outside the valid range for parameter max_standby_archive_delay (-1 .. 2147483) The error is clear enough, but is there some reason that the parameter is coded this way? istm people are much more likely to want to be able to set the precision in hours than in microseconds. OTOH, maybe it's a bug? The default resolution is in milliseconds, and you can't set it to anything less than that (afaict). I asked on irc and the consensus seemed to be that the internal representation is off, are we missing something? See this thread here: http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php Summary: should be fixed, but it needs to be verified that it works across all possible codepaths. It's not an issue with just max_standby_delay. Thanks for the pointer! I guess the next question is if anyone is working on that, and/or what would need to be done to know we've done a satisfactory job of verifying nothing breaks across all codepaths were someone to take on the job? Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/Lg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] why is max standby delay only 35 minutes?
I have a server where I wanted to do some reporting on a standby, and wanted to set the max standby delay to 1 hour. upon doing that, i get this in the logs: 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG: received SIGHUP, reloading configuration files 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG: 360 is outside the valid range for parameter max_standby_archive_delay (-1 .. 2147483) The error is clear enough, but is there some reason that the parameter is coded this way? istm people are much more likely to want to be able to set the precision in hours than in microseconds. OTOH, maybe it's a bug? The default resolution is in milliseconds, and you can't set it to anything less than that (afaict). I asked on irc and the consensus seemed to be that the internal representation is off, are we missing something? Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/Lg -- 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] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
On Mon, Feb 28, 2011 at 3:42 AM, Magnus Hagander mag...@hagander.net wrote: On Mon, Feb 28, 2011 at 06:21, Tom Lane t...@sss.pgh.pa.us wrote: Robert Treat r...@xzilla.net writes: Did anything ever come of this discussion? I think it's a TODO --- nothing done about it as yet, AFAIR. On one of the databases I was upgrading, I ran into a similar problem with roles that are set as roles. The problem seems to stem from pg_dumpall dumping roles in alphabetical order: CREATE ROLE asha; ALTER ROLE asha SET role TO 'omniti'; .. sometime later ... CREATE ROLE omniti; That seems like a pretty bizarre thing to do. Why would you want such a setting? I'm sure there are several. I've seen (and done) this more than once to ensure that the owner of newly created object is the shared role and not the individual, for example. Yeah, there are actually several of the roles that get set to the omniti role, like the robert role, which doesn't have any issue because it comes alphabetically after omniti. This also helps folks get around several permission related issues (simplified management, uniform permissions across users, simplified dependencies, etc..), but object ownership is a key part of it. Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/Lg -- 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: Why our counters need to be time-based WAS: WIP: cross column correlation ...
On Mon, Feb 28, 2011 at 4:13 PM, Greg Stark gsst...@mit.edu wrote: On Mon, Feb 28, 2011 at 6:31 PM, Josh Berkus j...@agliodbs.com wrote: What's needed here is for someone to write a good mrtg/rrd/whatever replacement using postgres as its data store. If you're monitoring something sensitive then you would store the data in a *different* postgres server to avoid Tom's complaint. There may be aspects of the job that Postgres does poorly but we can focus on improving those parts of Postgres rather than looking for another database. And frankly Postgres isn't that bad a tool for it -- when I did some performance analysis recently I actually ended up loading the data into Postgres so I could do some of the aggregations using window functions anyways. Greg, see https://labs.omniti.com/labs/reconnoiter, but also see Josh's nearby email about how he's trying to solve this internal to the database. Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/Lg -- 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] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
On Thu, Jan 6, 2011 at 10:08 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade mode. I am unclear if that might cause some other problems though. I finally figured out what was really bugging me about that proposal: it's a one-shot hack for fixing one problem that could arise from non-default ALTER DATABASE/ALTER ROLE settings. Who's to say there are not other such issues, either now or in the future? It occurs to me that a more principled way to deal with this class of problems would be to delay restoring ALTER DATABASE/ALTER ROLE settings until after everything else is done. Not sure what the implementation consequences of that would be. Ideally we'd make pg_dumpall output work that way in general, not just for pg_upgrade. Yep, it feels like a one-off that no one else will ever hit, and there are certainly other ALTER DATABASE SET commands that could also obstruct a restore. Did anything ever come of this discussion? On one of the databases I was upgrading, I ran into a similar problem with roles that are set as roles. The problem seems to stem from pg_dumpall dumping roles in alphabetical order: CREATE ROLE asha; ALTER ROLE asha SET role TO 'omniti'; .. sometime later ... CREATE ROLE omniti; It generates an error because the ALTER ROLE fails with the role not existing, which causes pg_upgrade to bail out (it's in the on error stop part). ISTM this fails in general, so not blaming pg_upgrade; I think there should probably be a fix in pg_dumpall to create all roles first before running the alters, but there might be some other options. Thoughts? Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/Lg -- 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] We need to log aborted autovacuums
On Wed, Jan 5, 2011 at 2:27 PM, Josh Berkus j...@agliodbs.com wrote: If you could gather more info on whether this logging catches the problem cases you're seeing, that would really be the right test for the patch's usefulness. I'd give you solid 50/50 odds that you've correctly diagnosed the issue, and knowing for sure would make advocating for this logging a pretty easy sell to me at least. Well, I already resolved the issue through polling pg_locks. The issue was IIT sessions which lasted up to an hour, which we fixed in the application, so I don't have the test case available anymore. I'd have to generate a synthetic test case, and since I agree that a SQL-callable diagnostic is superior to logging, I don't think we should pursue the log levels further. This is a great use case for user level tracing support. Add a probe around these bits, and you can capture the information when you need it. Robert Treat http://www.xzilla.net -- 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] Sync Rep Design
and possibly increase performance as well. The parameter must be set on both primary and standby. On the primary, this parameter can be changed at any time; the behavior for any one transaction is determined by the setting in effect when it commits. It is therefore possible, and useful, to have some transactions replicate synchronously and others asynchronously. For example, to make a single multistatement transaction commit asynchronously when the default is synchronous replication, issue SET LOCAL synchronous_replication TO OFF within the transaction. On the standby, the parameter value is taken only at server start. synchronous_replication_timeout (boolean) If the client has synchronous_replication set, and allow_standalone_primary is also set, then the commit will wait for up to synchronous_replication_timeout milliseconds before it returns a success, or will wait forever if synchronous_replication_timeout is set to -1. If a standby server does not reply for synchronous_replication_timeout the primary will terminate the replication connection. allow_standalone_primary (boolean) If allow_standalone_primary is not set, then the server will not allow connections until a standby connects that has synchronous_replication enabled. allow_standalone_primary also affects the behaviour when the synchronous_replication_timeout is reached. somewhat concerned that we seem to need to use double negatives to describe whats going on here. it makes me think we ought to rename this to require_synchronous_standby or similar. 25.5.2. Handling query conflicts …. Remedial possibilities exist if the number of standby-query cancellations is found to be unacceptable. Typically the best option is to enable hot_standby_feedback. This prevents VACUUM from removing recently-dead rows and so cleanup conflicts do not occur. If you do this, you should note that this will delay cleanup of dead rows on the primary, which may result in undesirable table bloat. However, the cleanup situation will be no worse than if the standby queries were running directly on the primary server. You are still getting the benefit of off-loading execution onto the standby and the query may complete faster than it would have done on the primary server. max_standby_archive_delay must be kept large in this case, because delayed WAL files might already contain entries that conflict with the desired standby queries. … 18.5.6. Standby Servers These settings control the behavior of a standby server that is to receive replication data. hot_standby (boolean) Specifies whether or not you can connect and run queries during recovery, as described in Section 25.5. The default value is off. This parameter can only be set at server start. It only has effect during archive recovery or in standby mode. hot_standby_feedback (boolean) Specifies whether or not a hot standby will send feedback to the primary about queries currently executing on the standby. This parameter can be used to eliminate query cancels caused by cleanup records, though it can cause database bloat on the primary for some workloads. The default value is off. This parameter can only be set at server start. It only has effect if hot_standby is enabled. i was expecting this section to mention the synchronous_replication (bool) somewhere, to control if the standby will participate synchronously or asynch; granted it's the same config as listed in 18.5.5 right? Just that the heading of that section specifically targets the primary. HTH, looks pretty good at first glance. Robert Treat http://www.xzilla.net
Re: [HACKERS] pg_dump --split patch
On Thu, Dec 30, 2010 at 2:13 AM, Joel Jacobson j...@gluefinance.com wrote: 2010/12/29 Dimitri Fontaine dimi...@2ndquadrant.fr Please have a look at getddl: https://github.com/dimitri/getddl Nice! Looks like a nifty tool. When I tried it, ./getddl.py -f -F /crypt/funcs -d glue, I got the error No such file or directory: 'sql/schemas.sql'. While the task of splitting objects into separate files could be solved by an external wrapper tool like yours around pg_dump, I argue it makes more sense of putting the (minimal required) logics into pg_dump, due to a number of reasons, most importantly because it's simplier and less complex, thus less error prone. My patch is only a few lines of code and doesn't add any logics to pg_dump, it merely reroutes the fwrite() system calls based on the toc entries. Just the fact you and others had to create own tools to do the splitting shows the feature is important, which I think should be included in the normal pg_dump tool. As someone whose own version of getddl helped inspire Dimitri to create his own version, I've both enjoyed reading this thread and seeing this wheel reinvented yet again, and wholeheartedly +1 the idea of building this directly into pg_dump. (The only thing better would be to make everything thing sql callable, but that's a problem for another day). Robert Treat http://www.xzilla.net
Re: [HACKERS] Sync Rep Design
On Thu, Dec 30, 2010 at 3:36 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-12-30 at 15:07 -0500, Robert Treat wrote: If more than one standby server specifies synchronous_replication, then whichever standby replies first will release waiting commits. I don't want you to think I am setting an expectation, but I'm curious about the possibility of requiring more than 1 server to reply? I was initially interested in this myself, but after a long discussion on quorum commit it was decided to go with first past post. That is easier to manage, requires one less parameter, performs better and doesn't really add that much additional confidence. Yes, I think with a single master, you are probably right (been dealing with more than my fair share of multi-master based nosql solutions lately) Still, one thing that has me concerned is that in the case of two slaves, you don't know which one is the more up-to-date one if you need to failover. It'd be nice if you could just guarantee they both are, but in lieu of that, I guess whatever decision tree is being used, it needs to look at current xlog location of any potential failover targets. It was also discussed that we would have a plugin API, but I'm less sure about that now. Perhaps we can add that option in the future, but its not high on my list of things for this release. Agreed. Robert Treat http://www.xzilla.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] understanding minimum recovery ending location
Howdy, I am hoping someone can help me better understand what the minimum recovery ending location of pg_controldata represents with regards to 9.0 hot standbys. When I look at any of our 8.4 (or lower) installs this number is almost always somewhere in the past of the xlog timeline (presuming the server has been up for any length of time), which makes sense because we've done enough replay that we're covered for consistent slave recovery purposes. However, on the hot standby machines the number seems to normally be at some point in the future. Below is the relevant bits of pg_controldata output from a hot standby I was looking at earlier today: Database cluster state: in archive recovery pg_control last modified: Wed 29 Dec 2010 04:54:34 PM GMT Latest checkpoint location: 56/21020CA8 Prior checkpoint location:56/1E36D780 Latest checkpoint's REDO location:56/1F599068 Time of latest checkpoint:Wed 29 Dec 2010 04:46:09 PM GMT Minimum recovery ending location: 56/24B88500 Backup start location:0/0 Current wal_level setting:hot_standby As you can see, the minimum recovery ending location is clearly ahead of the most recent checkpoint activity. Now, it's not always like this, but most of the time it is, and since minimum recovery ending location seems to be regularly be updating going forward, it tends to make me think that either I misunderstand what this means, or it means something different in this context. Partially because I can query on the hot standby machine already, so I know I have a recoverable slave, but even more so in the context of pg_controldata on the master: Database cluster state: in production pg_control last modified: Wed 29 Dec 2010 04:54:04 PM GMT Latest checkpoint location: 56/234325B0 Prior checkpoint location:56/21020CA8 Latest checkpoint's REDO location:56/220558A8 Time of latest checkpoint:Wed 29 Dec 2010 04:51:09 PM GMT Minimum recovery ending location: 0/0 Backup start location:0/0 Current wal_level setting:hot_standby As you can see, the masters checkpoint information is actually ahead of the slaves (as expected), but even in this scenario, the slave is saying that the minimum recovery ending location is actually in the future compared to the latest checkpoint and redo locations of the master. This seems like a bug to me (how can it possibly be required that the minimum recovery ending location would be at a point in the xlog timeline that may never exist?) , but I am guessing this field is actually reporting something different in this context, so I am hoping someone can help clarify it for me? Robert Treat http://www.xzilla.net
Re: [HACKERS] Anyone for SSDs?
On Wed, Dec 29, 2010 at 3:34 PM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: Vaibhav Kaushal wrote: On Fri, 2010-12-10 at 18:07 -0800, Josh Berkus wrote: On 12/10/10 5:06 PM, Daniel Loureiro wrote: An quicksort method in sequential disk its just awful to be thinking in a non SSD world, but its possible in an SSD. So, code it. Shouldn't be hard to write a demo comparison. I don't believe that SSDs make quicksort-on-disk feasible, but would be happy to be proven wrong. I too do not believe it in normal case. However, considering the 'types' of SSDs, it may be feasible! Asking for 'the next page and getting it' has a time delay in the process. While on a regular HDD with spindles, the question is where is that page located, with SSDs, the question disappears, because the access time is uniform in case of SSDs. Also, the access time is about 100 times fasterm which would change quite a few things about the whole process. What _is_ interesting is that Postgres often has sequential and random/disk ways of doing things, and by reducing random_page_cost when using SSDs, you automatically use more random operations, so in a way the Postgres code was already prepared for SSD usage. Surprisingly, we had to change very little. To add to this very late reply, we basically had random methods to do things (in RAM), and sequential/random methods for disk. By changing random_page_cost, we favor doing random things on disk. The big question is whether there are random things we have never implemented on disk that now make sense --- off hand, I can't think of any. The idea of us avoiding quicksort when we know we need to spill to disk is the type of thing that I wonder if it should be investigated, if you figure that spill to disk means ssd's so it's not so much of a performance hit. This reminds me of some performance testing we did maybe a year, year and a half ago, trying to see how best to get performance by adding some SSD's into one of our servers. Basically speed increased as we changed things like so: put entire $pgdata on sata put entire $pgdata on ssd put xlogs on ssd, pgdata on sata put pgdata and xlogs on sata, put arc on ssd, crank up postgres's memory settings arc being zfs's adaptive replacement cache, so basically giving the server a second, very large level of memory to work with, and then configuring postgres to make use of it. It wasn't terribly obvious to me why this ended up outperforming the initial idea of putting everything on ssd, but my impression was that the more you could force postgres into making decisions as if it was dealing with fast storage rather than slow storage, the better off you'd be (and that random_page_cost is not so wholly inclusive enough to do this for you). Robert Treat http://www.xzilla.net
Re: [HACKERS] Patch to add a primary key using an existing index
On Sat, Dec 4, 2010 at 6:48 AM, Robert Haas robertmh...@gmail.com wrote: On Dec 4, 2010, at 1:30 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ross J. Reedstrom reeds...@rice.edu writes: If you consider that an index basically is, in some sense, a pre-canned column list, then: ALTER TABLE table_name ADD PRIMARY KEY (column_list); ALTER TABLE table_name ADD PRIMARY KEY USING index_name; are parallel constructions. And it avoids the error case of the user providing a column list that doesn't match the index. +1 for that approach. One other thought is that ordinarily, the add-constraint syntax ensures that the constraint is named the same as its underlying index; in fact we go so far as to keep them in sync if you rename the index later. But after ALTER TABLE table_name ADD CONSTRAINT con_name PRIMARY KEY USING index_name; they'd be named differently, unless we (a) throw an error or (b) forcibly rename the index. Neither of those ideas seems to satisfy the principle of least surprise, but leaving it alone seems like it will also lead to confusion later. I think that might be the best way though. Haas, are you promoting to leave them different? I'd be comfortable with that. I'd also be comfortable with B (renaming with notice, similar to the notice when creating a constraint). Given we rename the constraint when we rename the index, I would not find the reverse behavior terribly surprising. Actually I think I'd even be comfortable with A, either you must name the constraint after the index, or you can leave the constraint name out, and we'll use the index name. I wonder whether, in the same spirit as not letting the user write a column name list that might not match, we should pick a syntax that doesn't allow specifying a constraint name different from the index name. In the case where you say CONSTRAINT it'd be a bit plausible to write something like ALTER TABLE table_name ADD CONSTRAINT con_name PRIMARY KEY USING EXISTING INDEX; (implying that the index to use is named con_name) but I don't know what to do if you want to leave off the CONSTRAINT name clause. Because this seems plain weird. +1 Robert Treat play: http://www.xzilla.net work: http://www.omniti.com/is/hiring
Re: [HACKERS] Patch to add a primary key using an existing index
On Fri, Dec 3, 2010 at 4:41 PM, Josh Berkus j...@agliodbs.com wrote: On 12/3/10 12:27 PM, Robert Haas wrote: On Fri, Dec 3, 2010 at 2:56 PM, r t pg...@xzilla.net wrote: What exactly was the objection to the following -- ALTER TABLE table_name ADD PRIMARY KEY (column_list) USING index_name; Is the objection that you might have been trying to specify a constraint named using ? I'm willing to make that option more difficult. :-) I think it's that someone might expect the word after USING to be the name of an index AM. Seems unlikely to cause confusion to me. +1. And were we ever to support that, I think that would be the case to use WITH (storage_parameter) type syntax, where you would specify access_method=hash (or whatever). Although, let's not debate that syntax right now, at this point :-) However, I don't see why we need (column_list). Surely the index has a column list already? ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY USING index_name ... seems like the syntax most consistent with the existing commands. Anything else would be confusingly inconsistent with the way you add a brand-new PK. Uh, the syntax I posted was based on this currently valid syntax: ALTER TABLE table_name ADD PRIMARY KEY (column_list); The constraint bit is optional, which is why I left it out, but I presume it would be optional with the new syntax as well... Also, I'm not wedded to the idea of keeping the column list, but if you are arguing to make it super consistent, then I think you need to include it. Robert Treat play: http://www.xzilla.net work: http://www.omniti.com/is/hiring
Re: [HACKERS] Simplifying replication
On Tue, Oct 19, 2010 at 11:16 AM, Greg Smith g...@2ndquadrant.com wrote: Josh Berkus wrote: It is critical that we make replication easier to set up, administrate and monitor than it currently is. In my conversations with people, this is more important to our users and the adoption of PostgreSQL than synchronous replication is. snip I find this launch into a new round of bike-shedding a bit distracting. If you want this to be easier to use, which it's obvious to any observer it should be because what's delivered in 9.0 is way too complicated, please work on finding development resources to assign to that problem. Because that's the bottleneck on simplifying things, not ideas about what to do. I would recommend finding or assigning a developer to work on integrating base backup in to the streaming protocol as the biggest single thing that would improve the built-in replication. All of the rest of the trivia about what knobs to set and such are tiny details that make for only a minor improvement until that's taken care of. Yeah, I'm sure we all think it should be easier, but figuring out what that means is certainly a moving target. The idea of being able to create a base backup automagically sounds good, but comparatively it's not significantly more difficult than what many other systems make you do, and actually if done incorrectly could be something rather limiting. On the whole the customers we are talking with are far more concerned about things like managing failover scenarios when you have multiple slaves, and it's the lack of capabilities around those kinds of things that hurt postgres adoption much more than it being hard to set up. Robert Treat play: http://www.xzilla.net work: http://omniti.com/is/hiring
[HACKERS] docs on contrib modules that can't pg_upgrade?
Howdy folks, Was wondering if there are any docs on which contrib modules don't work with pg_upgrade? I seem to remember discussion on this during the 9.0 cycle, but couldn't find it in the mail archive, and don't see anything in the wiki. What brings this up is I'm currently working on an 8.3 upgrade and it has pg_freespacemap which breaks things; I think easy enough to work-around in this case, but I am sure for other contribs, or for folks with a lot of machinery built on top of a contrib, that won't always be the case. If something like this doesn't exist, I'll start a wiki page on it, but thought I'd ask first. -- Robert Treat Play: http://www.xzilla.net Work: http://omniti.com/is/hiring -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgres officially accepted in to 2010 Google Summer of Code program
Howdy folks, I'm very happy to announce that the Postgres project has been selected to participate in this years Google Summer of Code program. Over the next couple weeks we'll be looking to solidify our mentor base; if you work on Postgres and would be willing to mentor a student, please send me a note so we can get you signed up. If you are a student and you're interested in working on Postgres, now is the time to get your proposal together. Student applications will open up on March 29th, so we'd like to have our mentors in place for review, and hopefully had students discussing with the Postgres devs their proposals as much as needed. If anyone has any questions, feel free to email me, or track me down on irc. Handy links for Postgres GSoC: Our ideas page for GSoC: http://www.postgresql.org/developer/summerofcode Our loose attempt at organization: http://wiki.postgresql.org/wiki/GSoC_2010 Our Postgres page on the GSoC site: http://socghop.appspot.com/gsoc/org/show/google/gsoc2010/postgresql Users Guide to GSoC: http://socghop.appspot.com/document/show/gsoc_program/google/gsoc2010/userguide Thanks everyone, I'm looking forward to another interesting year with GSoC, and hoping you'll join in. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.5 vs. 9.0, Postgres vs. PostgreSQL
On Saturday 23 January 2010 16:19:11 Andrew Dunstan wrote: Robert Treat wrote: I'm not saying there aren't downsides, but having a name the community can unify on is a definite plus, and imho that name has to be Postgres. Translation: we'll only be unified if everyone agrees with me. Wow Andrew, that's kind of a dick thing to say. This really isn't about agreeing with me except maybe that I've watched the issue for years and I think I have come to the most reasonable conclusion. If there is a more reasonable conclusion, I'm happy to switch to that, but of course we'd be back to people agreeing with me... Sorry, that is quite clearly not going to happen. People said that about win32 and people said that about git; the former has happened, the latter hasn't, but I suspect it will. Given the problems with the name PostgreSQL aren't just going to magically disappear, eventually I believe a name change will be made (though I've no doubt people will try to dig themselves in deeper in opposition to it in the mean time). Can we please get on with actually making a better product? Raising this issue again is simply an unnecessary distraction. A strong and growing community is arguably the most important feature of any software project; to that extent this *is* the work of making a better product. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.5 vs. 9.0, Postgres vs. PostgreSQL
On Friday 22 January 2010 23:44:11 Tom Lane wrote: David E. Wheeler da...@kineticode.com writes: On Jan 22, 2010, at 4:54 PM, Mark Mielke wrote: MS SQL, MySQL, SQLite - do they have advocacy problems due to the SQL in their name? I think it is the opposite. SQL in the name almost grants legitimacy to them as products. Dropping the SQL has the potential to increase confusion. What is a Postgres? :-) Something that comes after black, but before white. Yeah. As best I can tell, most newbies think that PostgreSQL means Postgre-SQL --- they're not too sure what Postgre is, but they guess it must be the specific name of the product. And that annoys those of us who would rather they pronounced it Postgres. But in terms of recognizability of the product it's not a liability. Well, it clearly is a liability to have your product name be confused in 3 or 4 different ways. I don't think it's impossible for people to not connect the dots that someone talking about postgrey is talking about the same thing as someone talking about postgres-sequel. The business about pronunciation is a red herring. It's just as unclear whether MySQL is to be pronounced my-se-quel or my-ess-cue-ell, but how many people have you heard claiming that's a lousy name? The difference is that that product name is still easily searchable. Looking for a job? type in mysql. trying to find talent? mysql. looking for product support? mysql. need training? mysql. Every one of these things (and many more) is made harder by the constant confusion of our product name. We're currently looking to hire new dba's, and we have to adjust search information to account for the potential use of postgres or postgresql as a skill (we're currently on the fence philosophically about hiring someone who calls it postgre). But we're lucky, because we know enough to try to account for these things. Consider someone new to Postgres looking for a job. Go to monster.com and search on postgre, postgres, or postgresql and you will get a different list of jobs for each keyword. digs a little A yes, and here are those statistics I posted a couple of years ago, showing site traffic into our website. http://archives.postgresql.org/pgsql-advocacy/2007-09/msg00108.php These are for the people who figure it out, I wonder how many people we miss out on because they get sidetracked trying to find out more about postgre? You once said Arguably, the 1996 decision to call it PostgreSQL instead of reverting to plain Postgres was the single worst mistake this project ever made. I think I would have to agree, and I can't see this issue ever going away as long as we stick with PostgreSQL. I'm not saying there aren't downsides, but having a name the community can unify on is a definite plus, and imho that name has to be Postgres. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Streaming replication status
On Monday 11 January 2010 23:24:24 Greg Smith wrote: Fujii Masao wrote: On Mon, Jan 11, 2010 at 5:36 PM, Craig Ringer cr...@postnewspapers.com.au wrote: Personally, I'd be uncomfortable enabling something like that without _both_ an admin alert _and_ the ability to refresh the slave's base backup without admin intervention. What feature do you specifically need as an alert? Just writing the warning into the logfile is enough? Or need to notify by using SNMP trap message? Though I'm not sure if this is a role of Postgres. It's impossible for the database to have any idea whatsoever how people are going to want to be alerted. Provide functions to monitor things like replication lag, like the number of segments queued up to feed to archive_command, and let people build their own alerting mechanism for now. They're going to do that anyway, so why waste precious time here building someone that's unlikely to fit any but a very narrow use case? That said, emitting the information to a log file makes for a crappy way to retrieve the information. The ideal api is that I can find the information out via result of some SELECT query; view, table ,function doesn't matter, as long as I can select it out. Bonus points for being able to get information from the hot standby. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Streaming replication status
On Tuesday 12 January 2010 17:37:11 Simon Riggs wrote: There is not much sense being talked here. I have asked for sufficient monitoring to allow us to manage it in production, which is IMHO the minimum required to make it shippable. This is a point I have mentioned over the course of many months, not a sudden additional thought. Even subscribing to this view point, there is sure to be a significant wiggle room in what people find to be sufficient monitoring. If I had to score the monitoring facilities we have for PITR standby, I'd give it about a crap out of 5, and yet somehow we seem to manage it. If the majority thinks that being able to find out the current replay point of recovery is all we need to manage replication then I will happily defer to that view, without changing my opinion that we need more. It should be clear that we didn't even have that before I raised the point. I'm certainly interested in specifics of what you think need to be exposed for monitoring, and I'd be interested in whether those things can be exposed as either trace points or possibly as C functions. My guess is that we won't get them into core for 8.5, but that we might be able to provide some additional facilities after the fact as we get more of these systems deployed. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.5alpha3 bug in information_schema.table_privileges
pagila=# select * from information_schema.table_privileges; TRAP: FailedAssertion(!(idx[0] (((int *) (((char *) (acl)) + sizeof(ArrayType)))[0])), File: acl.c, Line: 1740) server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. On a build without assertions it dumps core, which gives a backtrace like so: (dbx) where current thread: t...@1 =[1] aclexplode(fcinfo = ???) (optimized), at 0x7345fa (line ~1743) in acl.c [2] ExecMakeFunctionResult(fcache = ???, econtext = ???, isNull = ???, isDone = ???) (optimized), at 0x65ae76 (line ~1590) in execQual.c [3] ExecEvalFieldSelect(fstate = ???, econtext = ???, isNull = ???, isDone = ???) (optimized), at 0x65e57c (line ~3759) in execQual.c [4] ExecTargetList(targetlist = ???, econtext = ???, values = ???, isnull = ???, itemIsDone = ???, isDone = ???) (optimized), at 0x6600b8 (line ~4981) in execQual.c [5] ExecProject(projInfo = ???, isDone = ???) (optimized), at 0x660a2d (line ~5198) in execQual.c [6] ExecScan(node = ???, accessMtd = ???, recheckMtd = ???) (optimized), at 0x660b57 (line ~139) in execScan.c [7] ExecSeqScan(node = ???) (optimized), at 0x671c5f (line ~116) in nodeSeqscan.c [8] ExecProcNode(node = ???) (optimized), at 0x658cc9 (line ~378) in execProcnode.c [9] SubqueryNext(node = ???) (optimized), at 0x674fd8 (line ~53) in nodeSubqueryscan.c [10] ExecScan(node = ???, accessMtd = ???, recheckMtd = ???) (optimized), at 0x660d3d (line ~82) in execScan.c [11] ExecSubqueryScan(node = ???) (optimized), at 0x675007 (line ~87) in nodeSubqueryscan.c [12] ExecProcNode(node = ???) (optimized), at 0x658c95 (line ~396) in execProcnode.c [13] ExecNestLoop(node = ???) (optimized), at 0x670ef0 (line ~154) in nodeNestloop.c [14] ExecProcNode(node = ???) (optimized), at 0x658c48 (line ~419) in execProcnode.c [15] ExecNestLoop(node = ???) (optimized), at 0x670eb4 (line ~120) in nodeNestloop.c [16] ExecProcNode(node = ???) (optimized), at 0x658c48 (line ~419) in execProcnode.c [17] ExecNestLoop(node = ???) (optimized), at 0x670eb4 (line ~120) in nodeNestloop.c [18] ExecProcNode(node = ???) (optimized), at 0x658c48 (line ~419) in execProcnode.c [19] ExecutePlan(estate = ???, planstate = ???, operation = ???, sendTuples = ???, numberTuples = ???, direction = ???, dest = ???) (optimized), at 0x657220 (line ~1190) in execMain.c [20] standard_ExecutorRun(queryDesc = ???, direction = ???, count = ???) (optimized), at 0x6561e8 (line ~284) in execMain.c [21] ExecutorRun(queryDesc = ???, direction = ???, count = ???) (optimized), at 0x65610b (line ~227) in execMain.c [22] PortalRunSelect(portal = ???, forward = ???, count = ???, dest = ???) (optimized), at 0x71f8a4 (line ~953) in pquery.c [23] PortalRun(portal = ???, count = ???, isTopLevel = ???, dest = ???, altdest = ???, completionTag = ???) (optimized), at 0x71f4b2 (line ~779) in pquery.c [24] exec_simple_query(query_string = ???) (optimized), at 0x719ed6 (line ~1052) in postgres.c [25] PostgresMain(argc = ???, argv = ???, username = ???) (optimized), at 0x71de04 (line ~3671) in postgres.c [26] BackendRun(port = ???) (optimized), at 0x6e9b57 (line ~3369) in postmaster.c [27] BackendStartup(port = ???) (optimized), at 0x6e94a9 (line ~3076) in postmaster.c [28] ServerLoop() (optimized), at 0x6e709e (line ~1400) in postmaster.c [29] PostmasterMain(argc = ???, argv = ???) (optimized), at 0x6e675f (line ~1065) in postmaster.c [30] main(argc = ???, argv = ???) (optimized), at 0x688336 (line ~188) in main.c Both of those systems run Solaris, though one was compiled with gcc, the other with SunStudio. I can probably dig up more info if needed. Oh, seems it might be related to one of: http://git.postgresql.org/gitweb?p=postgresql.gita=searchh=HEADst=commits=aclexplode HTH -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] damage control mode
On Saturday 09 January 2010 16:32:29 Robert Haas wrote: On Sat, Jan 9, 2010 at 4:01 PM, Peter Eisentraut pete...@gmx.net wrote: On lör, 2010-01-09 at 14:12 -0500, Robert Haas wrote: If we accept large patches at the very end of the development cycle, that's when people will submit them. You've previously criticized the high proportion of the release cycle that is set aside for CommitFest and beta, so I'm surprised to see you advocating for a policy that seems likely to lengthen the time for which the tree is closed. Just to clarify: I am for sticking to the agreed dates. If some things are not ready by the necessary date plus/minus one, they won't make the release. If it's obvious earlier that something won't make the date, it shouldn't be committed, and maybe put on the backburner right now. But AFAICT, that's independent of when it was submitted. Some things that were submitted just the other day might be almost ready, some things that were first submitted many months ago are still not ready. The portion of the schedule I'm worried about is the one where we go to beta by March 7th. http://archives.postgresql.org/pgsql-hackers/2009-09/msg01251.php I think we can get all the remaining large patches committed by February 15th if Tom doesn't start working on the remaining open items until February 15th - but then I do not think that we will have a beta on March 7th. 1) The goal of any CF should not be that everything submitted gets committed, but that everything gets reviewed. 2) ISTM what had the most agreement was that the large/ugly patches that show up late should have no expectation of being committed (though we will try to review them), and also that they would be first in line for being punted should we start missing dates. 3) Our biggist problem wrt oscillating between a time based and feature based release has not been releasing the software, but on never even settling on a feature set to get into beta with. Given that, if we follow the normal CF process, by Feb 15 we should have a clear indication of what is and is not ready for commit, and my suspicion is that those large patches you are most worried about will have taken care of themselves (one way or the other) But I don't see much sense in worrying about it now; the 2 weeks between end of CF and Beta are when we need to be cut-throat. Given that this time the must-have feature is already in the tree, I think you will find people coming around quickly to the side of pushing things out rather than fighting to get things in. Just my .02 -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] damage control mode
On Sunday 10 January 2010 01:38:07 Tom Lane wrote: Robert Treat xzi...@users.sourceforge.net writes: ... I don't see much sense in worrying about it now; the 2 weeks between end of CF and Beta are when we need to be cut-throat. Given that this time the must-have feature is already in the tree, I think you will find people coming around quickly to the side of pushing things out rather than fighting to get things in. I think the other Robert's main point is that getting to beta in only two weeks is ridiculously optimistic (which I'm afraid I agree with). I believe that what he's proposing is tossing enough stuff overboard so that we can finish the January CF in much less than a month, and thereby have more time for alpha-level testing and stabilization of the tree. I agree with your summary, although I'm not sure it needs to be supported at this point. While it hasn't been stated explicitly, I suspect most reviewers will be reviewing with the idea of is there any chance that this is ready for commit in the back of thier heads, and things that aren't will likely get pushed off quickly. Now the other approach we could take is that we'll ship *something* on 7 Mar, even if it's less stable than what we've traditionally considered to be beta quality. I don't think that really helps much though; it just means we need more time in beta. There are three reasons I'd probably be comfortable with that; 1) the CF process means we've likely had more eyes on the code going in than in past releases. 2) the alpha releases mean we should have already had more review than in previous releases. 3) so far we're still looking good on pg_migrator, which should make it easier for people to test the release once we get into beta (which should help speed that cycle up). But really if beta slips because we don't like the looks of our open issues list, thats signicantly better than the last couple releases where we held everything up just to get things into CVS months after feature freeze had passed us by. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.5alpha3 hot standby crash report (DatabasePath related?)
On Jan 8, 2010, at 7:36 AM, Simon Riggs wrote: On Fri, 2010-01-08 at 01:26 +, Simon Riggs wrote: I'll test and commit tomorrow, since it's a fairly standalone problem Fix attached, thanks for testing. Works for me and I don't expect it to fail on Solaris, since the root cause of the failure has been addressed and a correctly designed test executed. I will wait a day for your confirmation and/or other's comments. Looks good from my end, thanks Simon. Robert Treat http://www.omniti.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] 8.5alpha3 hot standby crash report (DatabasePath related?)
Howdy folks,
Doing some testing with 8.5alpha3, my standby crashed this morning whilst
doing some testing. Seems I have a core file, so thought I would send a report.
Version: PostgreSQL 8.5alpha3 on i386-pc-solaris2.10, compiled by GCC gcc
(GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 32-bit
Error in the log was simply this:
LOG: restored log file 000100030032 from archive
LOG: startup process (PID 385) was terminated by signal 11
LOG: terminating any other active server processes
info from core file was:
postg...@postgresdev[/export/home/postgres/pgwork/data/slave]dbx
/export/home/postgres/pgwork/inst/slave/bin/postgres core
For information about new features see `help changes'
To remove this message, put `dbxenv suppress_startup_message 7.6' in your
.dbxrc
Reading postgres
core file header read successfully
Reading ld.so.1
Reading libnsl.so.1
Reading librt.so.1
Reading libsocket.so.1
Reading libm.so.2
Reading libc.so.1
Reading libaio.so.1
Reading libmd.so.1
program terminated by signal SEGV (no mapping at the fault address)
0xfedb578c: strlen+0x000c: movl (%eax),%edx
Current function is RelationCacheInitFileInvalidate
4237 snprintf(initfilename, sizeof(initfilename), %s/%s,
(dbx) where
[1] strlen(0x0), at 0xfedb578c
[2] _ndoprnt(0x8417d81, 0x8042e7c, 0x8042e50, 0x0), at 0xfee0d976
[3] snprintf(0x8042e90, 0x400, 0x8417d7f, 0x0, 0x8417d30, 0x2f676f6c,
0x4f434552, 0x59524556), at 0xfee10734
=[4] RelationCacheInitFileInvalidate(beforeSend = '\0'), line 4237 in
relcache.c
[5] xact_redo_commit(xlrec = 0x84ed434, xid = 0, lsn = RECORD), line 4414 in
xact.c
[6] StartupXLOG(), line 5834 in xlog.c
[7] StartupProcessMain(), line 8359 in xlog.c
[8] AuxiliaryProcessMain(argc = 2, argv = 0x80475e0), line 408 in
bootstrap.c
[9] StartChildProcess(type = 134510104), line 4218 in postmaster.c
[10] PostmasterMain(argc = 3, argv = 0x84c6e00), line 1061 in postmaster.c
[11] main(argc = 3, argv = 0x84c6e00), line 188 in main.c
(dbx) print initfilename
initfilename = XLOG
(dbx) list
4237 snprintf(initfilename, sizeof(initfilename), %s/%s,
4238DatabasePath, RELCACHE_INIT_FILENAME);
4239
4240 if (beforeSend)
4241 {
4242 /* no interlock needed here */
4243 unlink(initfilename);
4244 }
4245 else
4246 {
(dbx) print DatabasePath
DatabasePath = (nil)
Theo mentioned that he expects the above would work under Linux, but that we
need DatabasePath to not be null in the above.
FWIW, the SQL I was running on the master did involve two create database
commands back to back.
LMK if you have any questions.
--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.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] 8.5alpha3 hot standby crash report (DatabasePath related?)
On Jan 7, 2010, at 4:15 PM, Simon Riggs wrote: On Thu, 2010-01-07 at 14:41 -0500, Robert Treat wrote: Doing some testing with 8.5alpha3, my standby crashed this morning whilst doing some testing. Seems I have a core file, so thought I would send a report. Version: PostgreSQL 8.5alpha3 on i386-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 32-bit Error in the log was simply this: LOG: restored log file 000100030032 from archive LOG: startup process (PID 385) was terminated by signal 11 LOG: terminating any other active server processes Please send some more of the prior log, so we can tell the initialization state at that point. Not much more to send really. It's just restored log file lines going back to the startup (as expected) If you can reproduce it, that would help also. Thanks. Not sure about reproducing, but if I restart the database, it crashes again during log replay (stack trace info looks the same). LOG: database system was interrupted while in recovery at log time 2010-01-07 12:01:57 EST HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: starting archive recovery LOG: restore_command = '/export/home/postgres/pgwork/inst/slave/bin/ pg_standby /export/home/postgres/pgwork/data/.hot_standby %f %p %r' cp: cannot access /export/home/postgres/pgwork/data/.hot_standby/ 0001.history cp: cannot access /export/home/postgres/pgwork/data/.hot_standby/ 0001.history cp: cannot access /export/home/postgres/pgwork/data/.hot_standby/ 0001.history cp: cannot access /export/home/postgres/pgwork/data/.hot_standby/ 0001.history LOG: restored log file 00010003002A from archive LOG: automatic recovery in progress LOG: initializing recovery connections LOG: redo starts at 3/2A20, consistency will be reached at 3/2D000120 LOG: restored log file 00010003002B from archive LOG: restored log file 00010003002C from archive LOG: restored log file 00010003002D from archive LOG: consistent recovery state reached at 3/2D000120 LOG: database system is ready to accept read only connections LOG: restored log file 00010003002E from archive LOG: restored log file 00010003002F from archive LOG: restored log file 000100030030 from archive LOG: restored log file 000100030031 from archive LOG: restored log file 000100030032 from archive LOG: startup process (PID 4397) was terminated by signal 11 LOG: terminating any other active server processes -- Robert Treat http://www.omniti.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] 8.5alpha3 hot standby crash report (DatabasePath related?)
On Jan 7, 2010, at 5:18 PM, Simon Riggs wrote: On Thu, 2010-01-07 at 16:56 -0500, Robert Treat wrote: Not much more to send really. No problem. I can see what causes it, nothing more required, thanks. What I don't fully understand yet is why the error hasn't shown itself before, because it appears such a basic error. Is anyone doing regular testing on non-linux? (to the extent that this is a problem due to Solaris ideas about snprintf). Robert Treat http://www.omniti.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] SE-PostgreSQL/Lite Review
Manager, I feel we've given this patch its fair chunk of time this last month. I don't really see any options except to mark it returned with feedback yet again for now, as this CF is nearing its close and there's still plenty of open issues. My hope is that we've made progress toward answering some of the fundamental concerns that keep popping up around this patch for good, and that a plan with community members who will act on it (in this country for once) is coming together. I don't believe all works will be closed within 5 days. Thanks for your and BWPUG's offer. I'll provide my maximum efforts to become it commitable in the last commit fest. As always, we owe KaiGai a debt for offering his code contributions, sticking through an immense amount of criticism, and suggesting ways the rest of the database might become better overall through that interaction. I do hope a committer is able to give his Largeobject access controls patch proper attention and commit it if feasible to do so. It would be nice to see confirmed progress toward the larger goal of both feature and buzzword/checkbox complete PostgreSQL security is being made through his contributions. It is not a one-sided contribution. When we implement SE-PgSQL with full functionality, access controls on large objects are absolutely necessary. I consider it is a groundwork. At this point, I think someone comfortable with hacking into the PostgreSQL core will need to work on this project from that angle before even SE/PostgreSQL Lite is likely to be something we can commit. Maybe we can get KaiGai thinking in those terms instead of how he's been approaching the problem. Maybe Bruce or Steven can champion that work. I have to be honest and say that I'm not optimistic that this is possible or even a good idea to accomplish in the time remaining during this release. A patch that touches the security model in fairly fundamental ways seems like it would be much better as an alpha-1 candidate, while there's still plenty of time to shake out issues, than as a beta-1 or even alpha-3 one. And I'm skeptical that this feature really fits the true use-cases for SEPostgres without row-level filtering anyway. After our talk last night, it's obvious we need to figure out how to get that back before including the code does what people really want here. But based on looking at the market for this sort of feature, providing this new form of security integrated into the database does seem like a worthwhile goal. I wouldn't have spent this much time talking about it if I didn't believe that to be true. On my side, in addition to helping coordinate everyone pushing in the same direction, I'll also continue trying to shake out some sponsorship funding for additional work out of the people in this country it would benefit. It seems I'm going to keep getting pulled into the middle of this area regularly anyway. One point. I'd like to introduce a use case without row-level granularity. The page.24 in this slide: http://sepgsql.googlecode.com/files/JLS2009-KaiGai-LAPP_SELinux.pdf shows SELinux performs as a logical wall between virtual domains in web-services. Unlike physical database separation, it also allows to share a part of files/tables from multiple virtual hosts, because of its flexibility. I got the impression that this is doable with current SEPostgres stuff, would be nice to see a little more detailed writeup on how to do it. Especially if it could be linked to the hosting providors page in the wiki. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] limit to default display in psql
In the following query (used by psql to show attribute defaults) SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = '32923' AND a.attnum 0 AND NOT a.attisdropped ORDER BY a.attnum Is there some significance to the 128 other than simple formatting? Also, if anyone knows when pg_attrdef.adsrc became unreliable, that would save me some trouble. Thanks in advance. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Standalone backends run StartupXLOG in an incorrect environment
On Monday 21 September 2009 14:24:07 Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: So if you need to enter standalone mode, you'll have to start postmaster, wait for replay to finish, stop it and restart standalone. Yeah, that's the case at the moment. Would this be a problem when you need standalone mode in an emergency, for example when the database won't start due to Xid wraparound? If it won't run through StartupXLOG under the postmaster, it's not going to do so standalone either. Hmm... istr cases where I couldn't startup regular postgres but could in stand-alone mode that had system indexes disabled...I could be misremembering that so that the postmaster would start, I just couldn't connect unless in stand-alone. In any case this does seem less than ideal, but if there aren't any better options... -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] single bit integer (TINYINT) revisited for 8.5
On Thursday 02 July 2009 12:40:49 Simon Riggs wrote: On Wed, 2009-07-01 at 11:19 -0400, Caleb Cushing wrote: A couple of times I've been told you don't need tinyint, use boolean which is not true, several projects I've worked on I've needed and integer field that supports number within a small range 0-5 1-10 1-100 or something similar. I end up using smallint but it's range is huge for the actual requirements. Completely agree. Blech. More often than not, I find people using all these granular types to be nothing more than premature optimization. And if you really do need a single byte type, you can use char (though again I'm not a big fan of that) I'm most or the way through working on this as an add-on module, rather than a new datatype in core. I don't see much reason to include it in core: its not an SQL standard datatype, it complicates catalog entries and most people don't need or want it. That's too bad. I'd much rather see someone implement something closer to Oracle's number type. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] [pgsql-www] commitfest.postgresql.org
On Friday 03 July 2009 11:50:29 Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Jul 3, 2009 at 10:35 AM, Tom Lanet...@sss.pgh.pa.us wrote: The current URL seems to be http://commitfest.postgresql.org/action/commitfest_view?id=2 which is both opaque as can be and not looking like it's intended to be stable over the long term. I'm not sure why you would think that it's not stable. Because it's exposing three or four details of your implementation, which you might wish to change later. I'm also not sure what you would think that it's not self-explanatory, since it looks pretty self explanatory to me. It's impossible to know that this is commitfest 2009-07. commitfest.postgresql.org/2009/07 ? That, or any similar scheme, seems easily doable with a little apache rewrite magic and some programming. See my blog urls for one such example. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] WAL dump tool
On Saturday 09 May 2009 00:20:43 Jaime Casanova wrote: On Fri, May 8, 2009 at 3:43 AM, higepon hige...@gmail.com wrote: Hi. Is the following todo item still necessary? Create dump tool for write-ahead logs for use in determining transaction id for point-in-time recovery. This is useful for checking PITR recovery. If so, I want to make it. What is the expected output of the dump tool? TransactionId, TimeLineID and ? don't know if this project ever works http://pgfoundry.org/projects/xlogviewer but seems like is a start (it was made for 8.2 so you will have to adjust for 8.3 or even better 8.4) It did work at one point. We used it for some disaster recovery work maybe a year or so ago. We had to update it for 8.3, and remove some linuxisms to get it to compile for Solaris. I think it might have still had issues actually dumping data, but it did do a good job at finding corrupted xlogs. istr Theo submitted a patch, but I think the author had abandoned it. Personally I'd love to see it moved into postgresql proper (and get the cleaning/updating that implies). -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Restore deleted rows
On Wednesday 29 April 2009 14:03:14 Dimitri Fontaine wrote:
Hi,
On Tuesday 28 April 2009 20:43:38 Robert Treat wrote:
We had started down the path of making a function to read deleted tuples
from a table for a DR scenario we were involved with once. The idea was
that you could do something like select * from
viewdeletedpages('tablename') t (table type), which would allow you to
see the dead rows. It ended up unnessesary, so we never finished it, but
I still think the utility of such a function would be high... for most
people, if you told them that they could do create table as select * from
viewdeletedttuples(...) t(...) after doing a mis-placed delete/update, at
the cost of having to sift through extra data, they would make that trade
in a heartbeat.
There has been another idea proposed to solve this problem:
http://archives.postgresql.org/pgsql-hackers/2009-02/msg00117.php
The idea is to have VACUUM not discard the no more visible tuples but store
them on a specific fork (which you'll want to have on a WORM (cheap)
tablespace, separate issue).
Sounds similar to Oracle's undo logs.
Then you want to be able to associate the tuple xid info with a timestamptz
clock, which could be done thanks to txid and txid_snapshot by means of a
ticker daemon. PGQ from Skytools has such a daemon, a C version is being
prepared for the 3.0 release (alpha1 released).
Hannu said:
Reintroducing keeping old tuples forever would also allow us to bring
back time travel feature, that is
SELECT AS OF 'yesterday afternoon'::timestamp;
It could be that there's a simpler way to implement the feature than
provide a ticker daemon (one more postmaster child), but the linked thread
show some other use cases of an integrated ticker. I know we use PGQ alone
here to obtain reliable batches as presented at Prato:
http://wiki.postgresql.org/wiki/Image:Prato_2008_pgq_batches.pdf
Interesting.
Something like flashback queries would certaily be nice, and it's interesting
that we have most of the machinery to do this stuff already, we just need to
spruce it up a little.
--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.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] Prepared transactions vs novice DBAs, again
On Wednesday 22 April 2009 15:49:32 Tom Lane wrote: I wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Configuration affects what can be tested in installcheck, that's quite natural. I would be happy with simply adding an alternative expected output file for min_prepared_xacts=0 case. Like we've done for xml test cases, for example, though that's a compile-time option. Hmm, that's true; the xml case is a relevant precedent. This would be a pretty low-effort way of addressing the problem. Another nice thing about it is that we'd stop having a default max_prepared_transactions value that's completely useless (5 is guaranteed to be either too much or not enough...) The more I think about this the more I like it. The current default of 5 never had any justification beyond allowing the regression tests to run --- it's almost certainly not enough for production usage of the feature, but it exposes you to all of the downsides of accidental use. If we change it to zero, we could alter the Notes for PREPARE TRANSACTION to urge more strongly that the feature not be enabled without having set up appropriate external infrastructure. Warning about very old prepared transactions is something that we could think about doing as well; it doesn't have to be either-or. I think the need for it would decrease quite a bit if they weren't enabled by default, though. Comments? Anyone seriously opposed to making the default be zero? I see this has already been committed, and I am not seriously opposed to changing it, but I wanted to chime in on a point no one seemed to raise. I used to recommend people set this to 0 pretty regularly, since most web shops don't even know what prepared transactions are, let alone use them. I got less agressive about this after a few people reported to me that they had run out of lock slots on thier systems. Now, you'd think that ~300 lock slots wouldn't make that much difference, but it did make me a little nervous; so I thought I'd mention it. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Restore deleted rows
On Monday 27 April 2009 11:17:42 Tom Lane wrote:
Alvaro Herrera alvhe...@commandprompt.com writes:
Anton Egorov escribió:
I need to recover deleted rows from table. After I delete those rows I
stopped postgres immediately and create tar archive of database. I found
solution
http://archives.postgresql.org/pgsql-hackers/2005-03/msg00965.php, but
is there another (easyer) way to do it?
I guess you could figure out the Xid of the transaction that deleted the
tuples, and mark it as aborted in pg_clog; you'd also need to reset the
hint bits on the tuples themselves. Not necessarily any easier than the
above, but at least you don't have to patch Postgres code.
The solution recommended in that message doesn't work anyway --- it will
consider *all* tuples visible, even ones you don't want. Reversing a
single transaction, or small number of transactions, as Alvaro suggests
is much less likely to create a huge mess.
We had started down the path of making a function to read deleted tuples from
a table for a DR scenario we were involved with once. The idea was that you
could do something like select * from viewdeletedpages('tablename') t (table
type), which would allow you to see the dead rows. It ended up unnessesary,
so we never finished it, but I still think the utility of such a function
would be high... for most people, if you told them that they could do create
table as select * from viewdeletedttuples(...) t(...) after doing a
mis-placed delete/update, at the cost of having to sift through extra data,
they would make that trade in a heartbeat.
--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.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] psql \d* and system objects
On Monday 30 March 2009 10:52:47 Bruce Momjian wrote: Robert Haas wrote: On Mon, Mar 30, 2009 at 10:13 AM, Bruce Momjian br...@momjian.us wrote: Maybe the best we are going to do is to have any pattern supplied to \d* assume 'S' (include system objects). ?I actually have a patch that does that, attached. (It is from January so might need adjustment.) That still has the problem that \df a* is horribly inconsistent with \df. It might be reasonable to assume that if a name without wildcards is given to any \d command, it should display whatever object it finds, user or system - but I can't see doing it for any wildcard at all. I think you are re-iterating the URL I referenced when I started this thread: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01443.php I think the big question is whether the inconsistency (pattern implies 'S') is worth accepting for greater usability. Actually I find the iconsistency to hurt usability, which is typically what you get with inconsistent interfaces. I'm not certain, but I think I would be happier if we did: \d*user space objects \d*S include system objects For those that want system only, do \d*S pg_catalog. ( if you want to argue temp/toast, adjust the search accordingly) I think the trick to getting this working is to enforce this with search patterns *and* tab completion as well. Yes, this means that Tom's desire for sin has to become \dfS sin, but this maintains consistency (one of the issues I've been running into is finding something via tab completion that I can't actually see in the output listing, which is very annoying). Further, should you create a function called sin and do \df sin, you should only see your function. This can lead to confusion where you are calling a built in function but you dont see it in \df, or you see a different function in \df, but I haven't run into that case yet; in the work I've been doing in 8.4, the above is how I've been wanting it to work, and swapping to \df* to see system hasn't been much of an issue. BTW, I often do \df *.sin when looking for a function I'm not sure of where it lives exactly; this being on current (=8.3) releases, but many of the systems involve a fair number of schemas, that might not be a common practice, but perhaps should be. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Unexpected Result in Windowing
On Monday 30 March 2009 15:34:49 David Fetter wrote: On Mon, Mar 30, 2009 at 01:31:23PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: SELECT i, t, RANK() OVER ( PARTITION BY i ORDER BY random() ) FROM foo; ERROR: ORDER/GROUP BY expression not found in targetlist Fixed. Thanks! :) Yes, thanks! -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] small but useful patches for text search
On Wednesday 25 March 2009 23:17:41 Robert Haas wrote: With respect to this item: Disable appending of the epoch date/time when '%' escapes are missing in log_filename (Robert Haas) I might suggest explaining it this way: This change makes it easier to use PostgreSQL in conjunction with an external log rotation tool. Hey! We were just complaining about this behavior the other day at $dayjob. We were considering hacking our build to make it stop doing this ourselves, but decided to use syslog in the end. Nice to see this feature disappear. :-) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] small but useful patches for text search
On Saturday 21 March 2009 09:04:12 Robert Haas wrote: On Fri, Mar 20, 2009 at 11:59 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: snip My concern with the list of outstanding items for 8.4 based on a quick look is that I think many of those things are not, in fact, outstanding items for 8.4, and those that are may not be important enough to hold up beta for. Now since I haven't read through them all yet, I'm not 100% sure of that, but that's my concern for what it's worth. Robert, this has been discussed many times before, and most people agree with you, but Bruce doesn't. I think the ony way this will change is if someone takes on the role of release notes manager, subscrbes to pgsql-commits, and then starts updating a wiki page as each item is committed. Once other committers see that, I'm guessing they will start helping, and eventually Bruce will join in. Outside of that I think we're wasting our time on this. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] contrib function naming, and upgrade issues
On Saturday 21 March 2009 12:27:27 Tom Lane wrote: Andrew Gierth and...@tao11.riddles.org.uk writes: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom I agree that this wasn't an amazingly good choice, but I think Tom there's no real risk of name collisions because fmgr only Tom searches for such names within the particular .so. Oh, if only life were so simple. I think you are missing the point. There are certainly *potential* problems from common function names in different .so's, but that does not translate to evidence of *actual* problems in the Postgres environment. In particular, I believe that we load .so's without adding their symbols to those globally known by the linker --- at least on platforms where that's possible. Not to mention that the universe of other .so's we might load is not all that large. So I think the actual risks posed by contrib/hstore are somewhere between minimal and nonexistent. The past discussions we've had about developing a proper module facility included ways to replace not-quite-compatible C functions. I think that we can afford to let hstore go on as it is for another release or two, in hopes that we'll have something that makes a fix for this transparent to users. The risks don't look to me to be large enough to justify imposing any upgrade pain on users. We've been talking about this magical proper module facility for a few releases now... are we still opposed to putting contrib modules in thier own schema? People who took my advice and did that for tsearch were mighty happy when 8.2 broke at the C level, and when 8.3 broke all around. Doing that for hstore now would make the transition a little easier in the future as well. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] small but useful patches for text search
On Tuesday 17 March 2009 09:38:59 Bruce Momjian wrote: You are assuming that only commit-fest work is required to get us to beta. You might remember the long list of open items I faced in January that I have whittled down, but I still have about twenty left. I think part of the perception of the project sitting around doing nothing isn't so much that you/tom are doing nothing, but others who were doing review or coding features are now caught in limbo. One of the things the commitfest has been successful at is helping delegate code review. Perhaps we need to take a fresh look at your list of twenty things and see what can be delegated out to others. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Should SET ROLE inherit config params?
On Thursday 12 March 2009 21:39:54 Josh Berkus wrote: Josh, this isn't a rejection. Both Tom and I asked for more exploration of the implications of doing as you suggest. Tom has been more helpful than I was in providing some scenarios that would cause problems. It is up to you to solve the problems, which is often possible. OK, well, barring the context issues, what do people think of the idea? What I was thinking was that this would be a setting on the SET ROLE statement, such as: SET ROLE special WITH SETTINGS ... or similar; I'd need to find an existing keyword which works. I think this bypasses a lot of the issues which Tom raises, but I'd want to think about the various permutations some more. How bad of an idea would it be to split set session authorization to be privilege specific, and set role to focus on configiuration? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Hot standby, running xacts, subtransactions
On Tuesday 03 March 2009 03:22:30 Simon Riggs wrote: On Mon, 2009-03-02 at 21:11 -0500, Robert Treat wrote: On Wednesday 25 February 2009 16:43:54 Simon Riggs wrote: On Wed, 2009-02-25 at 13:33 -0800, Josh Berkus wrote: You raised that as an annoyance previously because it means that connection in hot standby mode may be delayed in cases of heavy, repeated use of significant numbers of subtransactions. While most users still don't use explicit subtransactions at all, wouldn't this also affect users who use large numbers of stored procedures? If they regularly use more than 64 levels of nested EXCEPTION clauses *and* they start their base backups during heavy usage of those stored procedures, then yes. We have stored procedrues that loop over thousands of records, with begin...exception blocks in that loop, so I think we do that. AFAICT there's no way to tell if you have it wrong until you fire up the standby (ie. you can't tell at the time you make your base backup), right ? That was supposed to be a simplification for phase one, not a barrier for all time. Understood; I only mention it because it's usually good to know how quickly we run into some of these cases that we don't think will be common. I'm changing that now, though the effect will be that in some cases we take longer before we accept connections. The initialisation requirements are that we have full knowledge of transactions in progress before we allow snapshots to be taken. That seems pretty reasonable; hopefully people aren't setting up hot standy machines as an emergency scaling technique :-) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] sanity check on max_fsm_relations
I have an app that needs to create about 50 partitions per day. I'm planning to boost up max_fsm_relations to about 100,000, so I won't have to worry about changing it again until I can upgrade to 8.4 ;-) According to the docs, this should take about 6MB of shmem, which is no big deal, but I'm wondering if there might be other performanace implications that I'm not aware of? Anyone ever run with 6 figure fsm relations (not just pages) before? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Hot standby, running xacts, subtransactions
On Wednesday 25 February 2009 16:43:54 Simon Riggs wrote: On Wed, 2009-02-25 at 13:33 -0800, Josh Berkus wrote: You raised that as an annoyance previously because it means that connection in hot standby mode may be delayed in cases of heavy, repeated use of significant numbers of subtransactions. While most users still don't use explicit subtransactions at all, wouldn't this also affect users who use large numbers of stored procedures? If they regularly use more than 64 levels of nested EXCEPTION clauses *and* they start their base backups during heavy usage of those stored procedures, then yes. We have stored procedrues that loop over thousands of records, with begin...exception blocks in that loop, so I think we do that. AFAICT there's no way to tell if you have it wrong until you fire up the standby (ie. you can't tell at the time you make your base backup), right ? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] autovacuum not honoring pg_autovacuum in 8.3.5?
On Monday 16 February 2009 11:20:11 Alvaro Herrera wrote: Joshua D. Drake wrote: Thanks for the work around, but that is ridiculous. I still say this is a bug. Yes, which is why we fixed it on 8.4 by dropping pg_autovacuum. (As Jaime and Tom say, it's actually pilot error, but the UI is crap so we fixed it.) A little confused by this... robert=# select version(); version - PostgreSQL 8.4devel on i386-pc-solaris2.10, compiled by cc: Sun C 5.9 SunOS_i386 2007/05/03, 32-bit (1 row) robert=# \d pg_catalog.pg_autovacuum Did not find any relation named pg_catalog.pg_autovacuum. robert=# \dtS+ pg_catalog.pg_autovacuum List of relations Schema | Name | Type | Owner | Size | Description +---+---++-+- pg_catalog | pg_autovacuum | table | robert | 0 bytes | (1 row) I think this build is a couple weeks old, but is the pg_autovacuum table really gone in 8.4, or just deprecated? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] pg_migrator progress
On Wednesday 18 February 2009 10:47:25 Tom Lane wrote: Gregory Stark st...@enterprisedb.com writes: Tom Lane t...@sss.pgh.pa.us writes: No, but this would just be the same situation that prevails after OID-counter wraparound, so I don't see a compelling need for us to change the OID counter in the new DB. If the user has done the Proper Things (ie, made unique indexes on his OIDs) then it won't matter. If he didn't, his old DB was a time bomb anyway. Also I wonder about the performance of skipping over thousands or even millions of OIDs for something like a toast table. I think that argument is a red herring. In the first place, it's unlikely that there'd be a huge run of consecutive OIDs *in the same table*. In the second place, if he does have such runs, the claim that he can't possibly have dealt with OID wraparound before seems pretty untenable --- he's obviously been eating lots of OIDs. Yeah, but its not just lots... it's lots and lots of lots. Sure, I have multi-billion row _tables_ now, but I know I ran systems for years back in the day when we used oids in user tables, and they never made it to oid wraparound terratory, because they just didn't churn through that much data. But having said that, there isn't any real harm in fixing the OID counter to match what it was. You need to run pg_resetxlog to set the WAL position and XID counter anyway, and it can set the OID counter too. +1 for doing this, otherwise we need some strong warnings in the migrator docs about this case imho. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] pg_restore --multi-thread
On Thursday 12 February 2009 11:50:26 Joshua D. Drake wrote: On Thu, 2009-02-12 at 11:47 -0500, Andrew Dunstan wrote: Joshua D. Drake wrote: On Thu, 2009-02-12 at 11:32 -0500, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: The implementation is actually different across platforms: on Windows the workers are genuine threads, while elsewhere they are forked children in the same fashion as the backend (non-EXEC_BACKEND case). In either case, the program will use up to NUM concurrent connections to the server. How about calling it --num-connections or something like that? I agree with Peter that thread is not the best terminology on platforms where there is no threading involved. --num-workers or --num-connections would both work. *shrug* whatever. What should the short option be (if any?). -n is taken, so -N ? Works for me. yikes... -n and -N have specific meaning to pg_dump, I think keeping consistency with that in pg_restore would be a bonus. (I still see people get confused because -d work differently between those two apps) Possibly -w might work, which could expand to --workers, which glosses over the thread/process difference, is also be available for pg_dump, and has existing mindshare with autovacuum workers. not having a short option seems ok to me too, but I really think -N is a bad idea. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] connection logging dtrace probe
I whipped up a quick dtrace probe for one of our servers to monitor connection
attempts. My goal was to monitor for any connection attempts from a specific
role within the database. Unfortunatly you can't set logging of connections
for a specific user, and logging all connections on that machine would be
quite the logfile bloater... enter dtrace. With the probe, I can do something
like this:
-bash-3.00$ /opt/csw/bin/sudo dtrace -n 'postgresql*:::connection
{printf(connection attempt: %...@%s\n,copyinstr(arg0),copyinstr(arg1)) }' |
grep robert
dtrace: description 'postgresql*:::connection ' matched 5 probes
2 18984ServerLoop:connection connection attempt: rob...@robert
2 16222ServerLoop:connection connection attempt: rob...@robert
1 16876ServerLoop:connection connection attempt: rob...@pagila
which can be piped to logfile or whatever. I'm attaching a patch against 8.4
as an idea of what I've implemented (actual implementation was against a
custom build) but should be close to working (don't have a working pg repo on
any solaris machines atm). Any feedback appreciated (mostly wondering about
probe name or location). TIA
--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 3380b80..ddf23d8 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -118,6 +118,7 @@
#include utils/datetime.h
#include utils/memutils.h
#include utils/ps_status.h
+#include pg_trace.h
#ifdef EXEC_BACKEND
#include storage/spin.h
@@ -3142,6 +3143,8 @@ BackendInitialize(Port *port)
elog(FATAL, could not disable timer for authorization timeout);
PG_SETMASK(BlockSig);
+ TRACE_POSTGRESQL_CONNECTION_ATTEMPT(port-user_name, port-database_name);
+
if (Log_connections)
ereport(LOG,
(errmsg(connection authorized: user=%s database=%s,
diff --git a/src/backend/utils/probes.d b/src/backend/utils/probes.d
index f68a7d2..d8b418a 100644
--- a/src/backend/utils/probes.d
+++ b/src/backend/utils/probes.d
@@ -91,4 +91,6 @@ provider postgresql {
probe xlog__switch();
probe wal__buffer__write__dirty__start();
probe wal__buffer__write__dirty__done();
+
+ probe connection__attempt(char *, char *);
};
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] confirm timezone changes for new releases was Fwd: Re: [pgsql-slavestothewww] New News Entry (id: 1055)
I know that the fedora tzdata-2009a packages have the Argentian changes (as well as some others depending on version of fedora), but I'm not sure what is includedin the original package, or exactly where we pull our changes in from Note our release notes dont specify those changes, so I am inclined to think they aren't in there, but can someone confirm for our release announcement if 8.3.6 et al have Argentinian timezone updates? (Or any other updates we should mention) TIA Robert Treat -- Forwarded Message -- Subject: Re: [pgsql-slavestothewww] New News Entry (id: 1055) Date: Thursday 05 February 2009 From: Guido Barosio gbaro...@gmail.com To: PostgreSQL Webmaster w...@wwwmaster.postgresql.org quote . Included as well are Daylight Savings Time changes for Nepal, Switzerland and Cuba /quote Isn't there a change for Argentina? Just asking because we had 2 changes during the last 6 months and I am not sure if we are handling this properly. gb.- On Fri, Feb 6, 2009 at 1:15 AM, Guido Barosio gbaro...@gmail.com wrote: These update releases also include patches for several low-risk security holes, as well as up to 17 other minor fixes, depending on your major version of PostgreSQL. Included as well are Daylight Savings Time changes for Nepal, Switzerland and Cuba. See the a href=/docs/current/static/release.htmlrelease notes/a for full details. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] confirm timezone changes for new releases was Fwd: Re: [pgsql-slavestothewww] New News Entry (id: 1055)
On Friday 06 February 2009 10:43:30 Bruce Momjian wrote: Bruce Momjian wrote: Robert Treat wrote: I know that the fedora tzdata-2009a packages have the Argentian changes (as well as some others depending on version of fedora), but I'm not sure what is includedin the original package, or exactly where we pull our changes in from Note our release notes dont specify those changes, so I am inclined to think they aren't in there, but can someone confirm for our release announcement if 8.3.6 et al have Argentinian timezone updates? (Or any other updates we should mention) TIA The commit messages is: Update time zone data files to tzdata release 2009a: introduces Asia/Kathmandu as the preferred spelling of that zone name, corrects historical DST information for Switzerland and Cuba. We get our data files as mentioned in /pgtop/src/timezone/README: ftp://elsie.nci.nih.gov/pub/tzdata*.tar.gz To better answer your specific question, I see Argentina timezone changes added CVS for in 2008i: revision 1.13 date: 2008/10/30 13:16:52; author: tgl; state: Exp; lines: +91 -16 Update time zone data files to tzdata release 2008i (DST law changes in Argentina, Brazil, Mauritius, Syria). Those changes would have been included in 8.3.5 released on 2008-11-03. Thanks Bruce! -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Thursday 29 January 2009 12:03:45 Robert Haas wrote: I don't believe that you can speed a project up much by adjusting the length of the release cycle, but it is *sometimes* possible to speed up a project by dividing up the work over more people. This is interesting. We had a problem in 8.3 (and most of the releases before that) of too many patches in the queue at the end of the development cycle. Most everyone agreed that more reviewers/committers would help, but given no way to conjure them up, they realized that wasn't a solution. Instead, we went to a tighter development cycle, with one month of dev and then a commifest. This allowed us to better parralelize both reviews and commits, allowed a number of patches to get bumped through multiple fests with relatively few compliants (after all, the next fest was just a month down the line), keep the patch queue pretty manageable (right up untill the end, when we stopped the cycle), and also delivered us some really big features along the way. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Thursday 29 January 2009 08:39:48 Gregory Stark wrote: I wish we could get rid of the whole concept and stigma of being bumped your patch will be released in the next release after it's committed. What does it matter if there's been another release since you started or not? This is the whole point. It isn't that there is a stigma to getting bumped; It matters becase missing a release means 12-14 months before your feature will be released, even when it takes far less time than that to complete the work. That 12-14 month delay has real implications on a number of levels for users and developers. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Wednesday 28 January 2009 08:55:56 Magnus Hagander wrote: We're still going to have to pay the full cost of doing a release every time. With beta/rc management, release notes, announcements, postings, packaging and all those things. As I pointed out to Tom, by percentage the additional beta/release cycles wouldn't be very different than what we have now; the more churn you have during development, the longer it takes to beta/release. I'm pretty sure that if we had pushed everything not committed on December 1st, we would be very close to release right now, and that's with more dev cycles than I'm talking about for 8.5. And I think most people (aka not the patch authors :-) would have been willing to push the stuff we're dealing with now if they knew the next release would be closer to 6 months than 14 months. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Wednesday 28 January 2009 12:35:42 Tom Lane wrote: Robert Treat xzi...@users.sourceforge.net writes: On Wednesday 28 January 2009 08:55:56 Magnus Hagander wrote: We're still going to have to pay the full cost of doing a release every time. With beta/rc management, release notes, announcements, postings, packaging and all those things. As I pointed out to Tom, by percentage the additional beta/release cycles wouldn't be very different than what we have now; the more churn you have during development, the longer it takes to beta/release. I don't believe that thesis in itself, because it ignores economies of scale and parallelism for beta testing. And in any case it's complete nonsense in respect to back-branch maintenance costs. If we double the frequency of releases we are going to be pretty much forced to halve the support lifetime, and ain't nobody going to be happy with us. Yes, back branch maintanance is an issue, but I'd bet that as long as we occasionally designate specific releases as long term support releases (my guess is 1 every 4 releases, though I haven't done the math), people would be comfortable with this. We've already had short maintainance windows for win32 support, and that has gone over without significant uproar. Also other projects (some much larger than ours) have implemented similar schemes, and it's been fairly well recieved. People understand the trade-offs of new features verses stability, and as long as you give them a long term option, they're happy. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Wednesday 28 January 2009 20:12:40 Bruce Momjian wrote: Robert Treat wrote: The revisionism was that of remarkable failure. That was our shortest release cycle in the modern era. And it didn't have the advantage of the commitfest process. But I think what is important here is to recognize why it didn't work. Once again we ended up with large, complex features (HOT, tsearch) that people didn't want to wait 14 months to see if they missed the 8.3 release. And yes, most of these same arguements were raised then... full text search is killer feature, whole applications are waiting for in-core full text search, hot will give allow existing customers to use postgres on a whole new level, not fair to push back patches so long when developers followed the rules, sponsors wont want to pay for features they wont see for years, developers dont want to wait so long to see features committed, and on and on... I think the big reminder for me from above is that we will always have big stuff that doesn't make a certain major release, and trying to circumvent our existing process is usually a mistake. Our usual process *is* to try and circumvent our usual process. And I believe it will continue to be that way until we lower the incentive to lobby for circumvention. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Wednesday 28 January 2009 23:42:11 Robert Haas wrote: Our usual process *is* to try and circumvent our usual process. And I believe it will continue to be that way until we lower the incentive to lobby for circumvention. I think Tom and Bruce have both pretty much stated that they're not keen on a shorter release cycle, and they're the ones who would have to do the work, so I think this argument is going nowhere. Moreover, I agree with them. Having short release cycles would probably be a good idea if we had a larger community with more patch authors, more reviewers, and more committers. more reviewers and more committers would actually be an argument against shorter release cycles, since we'd have a better shot at actually getting all patches in in a timely fasion. we dont, and we cant change that. again, thats the whole point of this... look at the variables and see which ones we can and cant change, and if those changes would address the causes. As it is, I think it would simply mean that the committers would spend more time doing releases and back-branch maintenance, and correspondingly less time to do what we really want them to do: review and commit patches. That problem is already pretty severe, and it would be a bad thing if it got worse. read up-thread, i've already shown that this would not be the case. remember, we reduce the pressure from the large, complex patches that bottleneck the process, which allows more parralell review/commit. If anyone really can't wait a year for a new feature, they can backport it to the previous release, or pay the patch author to do it. If they were paying the patch author to develop the feature in the first place, it shouldn't be a horribly expensive proposition. i dont think we as a community should encourage people to pay for private releases. that is a *really* bad idea. At the moment, what we really should be doing is conducting final reviews of as many patches as possible and trying to make sure that they are in good shape to be committed so that the people who have put in hard work for THIS release have a chance to see that work go out the door in a somewhat timely fashion. not that i disagree, but if we can improve things for the people working on the next release, well, I think that's a good idea too, and I dont see how doing nothing is going to help. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Monday 26 January 2009 15:13:56 dp...@pgadmin.org wrote: On 1/26/09, Josh Berkus j...@agliodbs.com wrote: All, 1) having the last CF on Nov. 1 was a mistake. That put us square in the path of the US Christian holidays during the critical integration phase .. which means we haven't really had 3 months of integration, we've had *two*. Actually, I'm thinking about this again, and made a mistake about the mistake. The *original plan* was that we were not going to accept any new patches for Nov-CF. Just revised patches from eariler Fests. We didn't stick to that, which is mostly why we are still reviewing now. I don't recall us discussing that, but it sounds like it might help next cycle. What would be the significance of opening up the tree to future development between the last commitfest and last commitfest -1, if no new patches could be introduced? Essentially this is where we are at now... November commit fest finished, December we re-opened for development, and we're in the January commitfest where no new features have been accepted. The problem is what to do when we get to the end of the commit fests, and we have a few reamining (invariably large/complex) patches that people don't want to push. I had been leaning toward the idea of pushing the 8.4 release back six months, but reopening development for 2-3 more development/commitfest cycles, but I am starting to think this is moving things in the wrong direction. Now I am starting to think that we cannot prevent large patches from showing up at the end of a cycle no matter what, and the only way to really solve that problem is to lesson the pain of getting bumped from a release. Ie. instead of being bump meaning you must wait 12-14 months till next release, we move toward more of a 6 month cycle of development. I'm not sure it's feasible to boil down beta/rc phase to two months, tough it seems possible if we were strict about bumping features that aren't ready, and if our development cycles only went 4 months. For end users who are concerned about continuous upgrading, we might think about putting restrictions on every other release (ie. require binary or data file level compatability with 8.4 for the 8.5 release, and remove that restriction for 8.6) to lesson the upgrade path. (alternativly, a working IPU plan could make that less of an issue) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Tuesday 27 January 2009 11:56:51 Simon Riggs wrote: On Tue, 2009-01-27 at 11:36 -0500, Tom Lane wrote: David Fetter da...@fetter.org writes: On Mon, Jan 26, 2009 at 03:12:02PM -0500, Tom Lane wrote: I don't think this is correct. I do. People literally grab my shoulder and ask when we'll have it. Do these people understand the difference between HS and a complete replication solution? Are they still as excited after you explain the difference? Yes, I think they do. http://www.postgresql.org/community/survey.55 These people seem to understand also. Sync rep *is* important, but it opens up new classes of applications for us. As does SEP. Both of those are more speculative and harder to measure, but we've seen big impact before from this type of new feature. HS appeals to current users. Current users aren't so worried about new applications, they look forward to being able to run queries on their currently idle standby servers. That's modest. I've talked to several oracle and db2 shops that want a standby for reporting that has relatively easy setup/maintenance (handling ddl is a big part of this) and the HS feature your working on will give them something as good as what they are getting now. So yeah, HS appeals to future users as well. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Tuesday 27 January 2009 10:34:59 Joshua D. Drake wrote: On Tue, 2009-01-27 at 06:40 +0100, Pavel Stehule wrote: 8.4-stable 8.4-experimental stable is everything that stable is. PostgreSQL at its best. I dislike this idea - it's same like short processed 8.5 - Actually it isn't because we wouldn't accept features into 8.4-experimental. The only thing we would accept into 8.4-experimental would be bug fixes that would automatically be ported up to 8.5 (or perhaps the other way around). We would still continue to build 8.5 as normal. that is more simple. We have tried the short release cycle before, it was called 8.2. It fails, remarkably. I think this is a bit of revisionsit history. While I'd agree it didn't work, the cycle was shorter... or to put it another way, would you say that the commitfest model failed miserably? should we scrap that for next cycle? I wonder... Feb 1 - all remaining patches bump / beta starts March 15th - beta ends / rc starts (note, giving 3 months for beta/rc, since we had a longer dev round) May 1st - release 8.4, open 8.5 dev June 1st - first 8.5 commitfest (we don't worry about short May because we have built up patche queue) July - second round of dev August - second/final commitfest Sept - beta opens October - rc opens November - release 8.5, open 8.6 December - first commitfest for 8.6 Jan 2010 - second dev cycle Feb - final commitfest March - 8.6 beta April - 8.6 rc May 2010 - release 8.6 rinse, repeat -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Tuesday 27 January 2009 18:51:01 Tom Lane wrote: Robert Treat xzi...@users.sourceforge.net writes: Now I am starting to think that we cannot prevent large patches from showing up at the end of a cycle no matter what, and the only way to really solve that problem is to lesson the pain of getting bumped from a release. Ie. instead of being bump meaning you must wait 12-14 months till next release, we move toward more of a 6 month cycle of development. I really can't see going in that direction. In the first place, no one wants to spend a third or more of the time in beta mode. Yeah, I was thinking that, but the truth is we do that now. We released last Febuary right? and we're looking at releasing (optimisttically) May 1st, right? So thats 15months, of which November - May (6 months) will have been feature freeze / beta / rc phase of development. In the second place, if the problem is big patches that take a long time to develop, halving the length of the development cycle is no solution. (If it did work, our plea to break large patches into segments landing in different commitfests would have had more results.) I think this is a mis-assesment of our problem. The problem is not that big patches take a long time; not so much that they don't, just that is not a problem we can solve... Hey Simon, code faster! is not going to work ;-) The problem is that the pain point is extremely high for missing a given release cycle. If you don't make a specific release, you have a 12-14 month wait for feature arrival. Given that choice, someone who deperately need (aka wants) HS/SEPostgres/Win32/HOT/IPU/etc... will likely be willing to push a release 3-6 months for that one feature. Incidentally, this is probably why people didnt worry about making a given commitfest. The pain point was low, so there was no percieved need to rework a patch for a specific commit, since there was another one just a couple months away. However, we still see a rush of patches at the final freeze because people know that there is no tommorrow at that point. In the third place, unless we get an upgrade-in-place process that works all the time, we would be looking at maintaining twice as many back branches in order to provide the same kind of release lifespan we have today. We are at the limit of what we can realistically do in back-branch maintenance already :-( Yeah, I can't argue with that. I'm not sure it's an unsolvable problem though; if odd/even release maintenance doesn't sound good, we could do something like ubuntus LTS, where we pick 1 release every 3 years to make a long-term support commitment (I think 5 years is our current max), and keep other releases on a shorter lifespan (1 or 2 years). Certainly having IPU (or is that UIP?) would make that an easier decision. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Tuesday 27 January 2009 19:04:49 Tom Lane wrote: Robert Treat xzi...@users.sourceforge.net writes: On Tuesday 27 January 2009 10:34:59 Joshua D. Drake wrote: We have tried the short release cycle before, it was called 8.2. It fails, remarkably. I think this is a bit of revisionsit history. JD got the release number wrong, it was 8.3, but otherwise there's no revisionism involved: http://archives.postgresql.org/pgsql-hackers/2006-12/msg00786.php The revisionism was that of remarkable failure. That was our shortest release cycle in the modern era. And it didn't have the advantage of the commitfest process. But I think what is important here is to recognize why it didn't work. Once again we ended up with large, complex features (HOT, tsearch) that people didn't want to wait 14 months to see if they missed the 8.3 release. And yes, most of these same arguements were raised then... full text search is killer feature, whole applications are waiting for in-core full text search, hot will give allow existing customers to use postgres on a whole new level, not fair to push back patches so long when developers followed the rules, sponsors wont want to pay for features they wont see for years, developers dont want to wait so long to see features committed, and on and on... The more I think about it, the more I feel that where we failed for 8.3 was not having a short 8.4 cycle lined up, which would give more freedom to bump patches to the next release. The theme that our release cycles are too long is not exactly new, of course, eg http://archives.postgresql.org/pgsql-hackers/2000-05/msg00574.php http://archives.postgresql.org/pgsql-hackers/2001-06/msg00766.php http://archives.postgresql.org/pgsql-hackers/2003-11/msg00889.php Yeah, I remember all that, and I think you'll find that I mostly was on the other side of this issue :-) But many of the arguments from back then don't apply any more. Remember when you couldn't depend on pg_dump giving you dumps in the right order? Now that was a recipe for painful upgrades. With things like Slony, it's now possible to upgrade a majority of the Postgres installations with extremely minimal downtime. (And yes, I happen to have one that wont work with slony, but hey...) but by now I think we've learned to stop banging our heads against that particular rock. One-year major cycles work for this project, shorter ones are wishful thinking. Do they? 1 year cycles certainly don't solve the problem of being left with big/complex left over patches. They don't decrease the amount of time (as a percentage) we spend in freeze/beta/release mode. And we don't even get them out in 1 year; this 1 year cycle looks like at least 15 months. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] 8.4 release planning
On Tuesday 27 January 2009 21:07:48 Tom Lane wrote: Robert Treat xzi...@users.sourceforge.net writes: The more I think about it, the more I feel that where we failed for 8.3 was not having a short 8.4 cycle lined up, which would give more freedom to bump patches to the next release. Heh. The reason we wanted a short 8.3 cycle was so we could push out patches that had been held over from 8.2. And the reason that didn't work was because when we got to feature freeze, we once again had several large, complex patches which people didn't want to push for the long 8.4 cycle. (But note: people are willing to push patches when they believe the wait time won't be excessive for eventual inclusion) We are going to have exactly no credibility if we tell Simon et al we're pushing these patches to 8.5, but don't worry, it'll be a short release cycle. The other options being we stall 8.4 indefinatly waiting for HS (which, honestly I am comfortable with), or his patches get pushed and he doesnt get them for another 14 months. Seems to me our credibility isn't really even a factor here. Right now I'm really trying to figure out how to solve this problem for the long term. If we say up front now that the next 2 cycles are short cycles, then I think people will be more willing to push patches come end-of-8.5 (and let's not pretend we're not going to have this same argument over streaming replication or synchronous replay or merge command or whatever hot feature is almost ready at that time) I think the best thing we could do overall is to set release dates and stick to them. If your patch is not ready, well, at least it will get out in a defined amount of time. Right now, the *real* problem with it being pushed to the next release is you don't know how successful some other guy will be at persuading us to delay the next release. I wont argue that setting release dates and sticking to them is a bad idea, but that extra month at the end that that occures due to feature lobbying doesn't strike me as the straw breaking the camels back, it's the 12-14 months in front of that people don't want to wait through. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Auto-updated fields
On Wednesday 21 January 2009 20:21:41 Bruce Momjian wrote: Alvaro Herrera wrote: Robert Treat wrote: On Thursday 08 May 2008 00:27:10 Tino Wildenhain wrote: David Fetter wrote: Ref: http://archives.postgresql.org/pgsql-hackers/2008-05/msg00198.php 1. Create a generic (possibly overloaded) trigger function, bundled with PostgreSQL, which sets a field to some value. For example, a timestamptz version might set the field to now(). Having the pre defined triggers at hand could be useful, especially for people not writing triggers so often to get used to it but I'm really not happy with the idea of magic preprocessing. I have a generic version of this in pagila. Now that we have a specific file in core for generic triggers (right now with a single one), how about adding this one to it? Any progress on this? TODO? I think this is a TODO, but not sure who is working on it or what needs to be done. The generic version in pagila is perhaps not generic enough: CREATE FUNCTION last_updated() RETURNS trigger AS $$ BEGIN NEW.last_update = CURRENT_TIMESTAMP; RETURN NEW; END $$ LANGUAGE plpgsql; It requires you name your column last_update, which is what the naming convention is in pagila, but might not work for everyone. Can someone work with that and move forward? Or maybe give a more specific pointer to the generic trigger stuff (I've not looked at it before) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Status Report on Hot Standby
On Friday 16 January 2009 19:16:42 Simon Riggs wrote: Bruce asked for 2 more weeks to get patches into shape for commit. Current patch v8e is attached here. Ready for commit? Up to you. My overall opinion is that it's in very good shape. Worth the community including it in this release and spending further time on it. I'm happy to stand by this going forwards. +1 The patch could benefit further from input from other various hackers, what couldn't? It's time to put this in a shared repository (of some kind) and make further changes to it in a controlled manner. Yep. I've now got this running on Linux and Solaris and testing so far has looked good. I've also spoken to a couple other people who have built it and run it, and everyone has been pretty happy. It'd certainly be nice to see this get into the main source tree to make it easier for future testing. (For example, one hurdle on Solaris, I had to get a different version of patch to handle Simon's diff... ugh!) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] New pg_dump patch -- document statistics collector exception
On Tuesday 20 January 2009 20:22:10 Bryce Nesbitt wrote: This is a proposed patch to document disabling the statistics collector pg_dump activity, and give a bit more visibility to the PGOPTIONS environment variable supported by libpq. It is an alternative to the prior patch, which supplied a --no-stats flag. This is a documentation only patch, not tied to a recent code change. s/varriable/variable/g also, I forget which way is proper, but you're inconsistent with your closing tags for application in that paragraph (using both /application and /) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Copyright update
On Thursday 01 January 2009 15:28:51 Bruce Momjian wrote: Joshua D. Drake wrote: On Thu, 2009-01-01 at 14:47 -0500, Bruce Momjian wrote: Andrew Chernow wrote: Bruce Momjian wrote: Greg Stark wrote: Is that actually legal if we haven't modified the files? Or is the whole source tree considiered one work? One work, I assume. I am not a lawyer, but if its one work, why is there a notice in every source file? ISTM that if it were one work there would only have to be one notice. Because people often take source files and copy them for use in other projects. I think the correct resolution to the question is to ask legal. Yes? So I can get three different answers? It is not a priority for me. Nor does it need to be... copyright for organizations runs ~ 100 years, so a year here or there is unlikely to make much difference to any of us. (Though for future generations, we'd probably have been better off not having a copyright notice at all). -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Hot standby and b-tree killed items
On Saturday 20 December 2008 04:10:21 Heikki Linnakangas wrote: Simon Riggs wrote: On Sat, 2008-12-20 at 09:21 +0200, Heikki Linnakangas wrote: Gregory Stark wrote: Simon Riggs si...@2ndquadrant.com writes: Increasing the waiting time increases the failover time and thus decreases the value of the standby as an HA system. Others value high availability higher than you and so we had agreed to provide an option to allow the max waiting time to be set. Sure, it's a nice option to have. But I think the default should be to pause WAL replay. I think I agree that pausing should be the default. If for no other reason, because I can't think of a good default for max_standby_delay. I would rather err on the side of caution. If we do as you suggest, somebody will lose their database and start shouting stupid default. Even if we stop applying the WAL, it should still be archived safely, right? So no data should be lost, although the standby can fall very much behind, and it can take a while to catch up. I was thinking the condition Simon was concerned about was that on a very busy slave with wal delay, you could theoretically fill up the disks and destroy the slave. With query cancel, you might be annoyed to see the queries canceled, but theres no way that you would destroy the slave. (That might not have been what he meant though) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Hot standby and b-tree killed items
On Friday 19 December 2008 19:36:42 Simon Riggs wrote: Perhaps we should listen to the people that have said they don't want queries cancelled, even if the alternative is inconsistent answers. That is easily possible yet is not currently an option. Plus we have the option I referred to up thread, which is to defer query cancel until the query reads a modified data block. I'm OK with implementing either of those, as non-default options. Do we need those options or are we ok? Haven't seen any feed back on this, but I think the two options of cancel query for replay, and pause replay for queries, are probably enough for a first go around (especially if you can get the query canceling to work only when changes are made to the specific database in question) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Hot standby and b-tree killed items
On Friday 19 December 2008 05:52:42 Simon Riggs wrote: BTW, I noticed the other day that Oracle 11g only allows you to have a read only slave *or* allows you to continue replaying. You need to manually switch back and forth between those modes. They can't do *both*, as Postgres will be able to do. That's because their undo information is stored off-block in the Undo Tablespace, so is not available for standby queries. Nice one, Postgres. I think this is true for physical replay, but Oracle also offers the option to do logical replay (where transaction logs are converted into sql and run against the standby; i believe this is similar to what continuant is trying to do with thier latest offering). In that scenario you can do read and replay at the same time, though I think there are some conflicts possible; fewer than what postgres will have, since I think most of thier DDL can be done online. (This might require some extra modules / high end version of Oracle, please consult your local Oracle wizard for more details) -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Coding TODO for 8.4: Synch Rep
On Tuesday 16 December 2008 08:01:49 Alvaro Herrera wrote: Fujii Masao escribió: Since there are many TODO items, I'm worried about the deadline. When is the deadline of this commit fest? December 31st? first half of January? ...etc? November 1st was the deadline. We're now in feature freeze. November 1st was when the commitfest started, I think he was wondering when the commitfest was going to end. This being the last commitfest, it runs differently than others; as Alvaro mentioned, we're now in feature freeze, but when that will end is still undetermined. In other words, if you have a patch for 8.4 that is already submitted but not committed, keep hacking! -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] plperl: Documentation on BYTEA decoding is wrong
On Monday 28 January 2008 05:37:03 Florian Weimer wrote:
* Robert Treat:
Note we've been using Theo's plperl bytea patch on one of our
production servers for some time; if anyone wants access to that
lmk.
I'm interested. Could you post a pointer to this code, please?
I had to do some work on this machine last week, and while digging into it, I
ran across this email which appears to have fallen through the cracks. Not
sure that anyone is still interested (heck, i've forgotten what this thread
was even about), but the following patch should apply cleanly to 8.2.11.
Note that personally I think this is a bit of a hack; I'd rather see a
solution in the upstream code, but aiui this approach is frowned upon. If I
get time, I might redo this as a new pl language (plperlo or something)
rather than maintaing the patch. In any case, if anyone is interested on
hacking on this, please drop us a line.
--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com
Index: plperl.c
===
RCS file: /projects/cvsroot/pgsql/src/pl/plperl/plperl.c,v
retrieving revision 1.123.2.4
diff -c -r1.123.2.4 plperl.c
*** plperl.c 22 Jan 2008 20:19:53 - 1.123.2.4
--- plperl.c 15 Dec 2008 16:20:21 -
***
*** 53,58
--- 53,59
Oid result_typioparam;
int nargs;
FmgrInfo arg_out_func[FUNC_MAX_ARGS];
+ Oid arg_out_oid[FUNC_MAX_ARGS];
bool arg_is_rowtype[FUNC_MAX_ARGS];
SV *reference;
} plperl_proc_desc;
***
*** 142,147
--- 143,149
static Datum plperl_trigger_handler(PG_FUNCTION_ARGS);
static plperl_proc_desc *compile_plperl_function(Oid fn_oid, bool is_trigger);
+ static Datum plperl_convert_sv_to_datum(Oid rtypeid, FmgrInfo *in_func, Oid typioparam, SV *sv);
static SV *plperl_hash_from_tuple(HeapTuple tuple, TupleDesc tupdesc);
static void plperl_init_shared_libs(pTHX);
***
*** 561,566
--- 563,592
return res;
}
+ /* Build a Datum form an SV handling the case of bytea */
+
+ static Datum
+ plperl_convert_sv_to_datum(Oid rtypeid, FmgrInfo *in_func, Oid typioparam, SV *sv)
+ {
+ char *val;
+ STRLEN len;
+ Datum retval;
+
+ val = SvPV(sv, len);
+ if(rtypeid == BYTEAOID)
+ {
+ StringInfoData buf;
+ initStringInfo(buf);
+ appendBinaryStringInfo(buf, val, len);
+ retval = ReceiveFunctionCall(in_func, buf,
+ typioparam, -1);
+ }
+ else {
+ retval = InputFunctionCall(in_func, val,
+ typioparam, -1);
+ }
+ return retval;
+ }
/* Build a tuple from a hash. */
***
*** 767,773
while ((val = hv_iternextsv(hvNew, key, klen)))
{
int attn = SPI_fnumber(tupdesc, key);
- Oid typinput;
Oid typioparam;
int32 atttypmod;
FmgrInfo finfo;
--- 793,798
***
*** 778,793
errmsg(Perl hash contains nonexistent column \%s\,
key)));
/* XXX would be better to cache these lookups */
! getTypeInputInfo(tupdesc-attrs[attn - 1]-atttypid,
! typinput, typioparam);
! fmgr_info(typinput, finfo);
atttypmod = tupdesc-attrs[attn - 1]-atttypmod;
if (SvOK(val))
{
! modvalues[slotsused] = InputFunctionCall(finfo,
! SvPV(val, PL_na),
typioparam,
atttypmod);
modnulls[slotsused] = ' ';
}
else
--- 803,845
errmsg(Perl hash contains nonexistent column \%s\,
key)));
/* XXX would be better to cache these lookups */
! if(tupdesc-attrs[attn - 1]-atttypid == BYTEAOID)
! {
! Oid typreceive;
! getTypeBinaryInputInfo(tupdesc-attrs[attn - 1]-atttypid,
! typreceive, typioparam);
! fmgr_info(typreceive, finfo);
! }
! else
! {
! Oid typinput;
! getTypeInputInfo(tupdesc-attrs[attn - 1]-atttypid,
! typinput, typioparam);
! fmgr_info(typinput, finfo);
! }
atttypmod = tupdesc-attrs[attn - 1]-atttypmod;
if (SvOK(val))
{
! STRLEN len;
! char *str;
! str = SvPV(val, len);
! if(tupdesc-attrs[attn - 1]-atttypid == BYTEAOID)
! {
! StringInfoData buf;
! initStringInfo(buf);
! appendBinaryStringInfo(buf, str, len);
! modvalues[slotsused] = ReceiveFunctionCall(finfo,
! buf,
typioparam,
atttypmod);
+ }
+ else
+ {
+ modvalues[slotsused] = InputFunctionCall(finfo,
+ str,
+ typioparam,
+ atttypmod);
+ }
modnulls[slotsused] = ' ';
}
else
***
*** 1077,1089
}
else
{
! char *tmp;
!
! tmp = OutputFunctionCall((desc-arg_out_func[i]),
! fcinfo-arg[i]);
! sv = newSVstring(tmp);
! XPUSHs(sv_2mortal(sv));
! pfree(tmp);
}
}
PUTBACK;
--- 1129,1152
}
else
{
! if(desc-arg_out_oid[i] == BYTEAOID)
! {
! bytea *tmpbytes;
! tmpbytes = SendFunctionCall((desc-arg_out_func[i
Re: [HACKERS] Quick patch: Display sequence owner
On Tuesday 09 December 2008 15:49:17 Alvaro Herrera wrote: Josh Williams wrote: On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote: I think the place that such information could most naturally be squeezed into psql's \d commands would be to add another type of footer information to \dt, eg Table foo.bar ... Indexes: bari ... Owned sequences: baz owned by col1 That makes more sense, though isn't that a little repetitive when default nextval(...) is visible immediately above it? I don't think that it is all that repetitive. It's not uncommon to see people creating sequences and assigning to default values, without setting the OWNED BY bits. It's good that this information is very visible. It's only a couple more lines in the common case anyway (if you want to save half of that overhead, make it a single line when there's a single sequence.) It feels like noise to me; showing indexes/triggers/constraints affect how you interact with a table, but whether a sequence is owned or not doesn't make a significant difference. Given we don't list other dependencies (views/functions/etc...) I'm not excited about adding this one. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] default statistics target testing (was: Simple postgresql.conf wizard)
On Friday 05 December 2008 00:05:34 Robert Haas wrote: On Thu, Nov 27, 2008 at 6:46 PM, Gregory Stark [EMAIL PROTECTED] wrote: ANALYZE with default_statistics_target set to 10 takes 13 s. With 100, 92 s. With 1000, 289 s. That is interesting. It would also be interesting to total up the time it takes to run EXPLAIN (without ANALYZE) for a large number of queries. I wonder if we'd see anything dramatically different using PREPARE... OK, I did this. I actually tried 10 .. 100 in increments of 10 and then 100 ... 1000 in increments of 50, for 7 different queries of varying complexity (but all generally similar, including all of them having LIMIT 100 as is typical for this database). I planned each query 100 times with each default_statistics_target. The results were somewhat underwhelming. The one thing this test seems to overlook is at what point do we see diminshing returns from increasing dst. I think the way to do this would be to plot dst setting vs. query time; Robert, do you think you could modify your test to measure prepare time and then execute time over a series of runs? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Mostly Harmless: Welcoming our C++ friends
On Friday 05 December 2008 09:51:50 Kurt Harriman wrote: Peter Eisentraut wrote: FYI, we have received patches morally equivalent to yours many times over the years, and they have all been rejected. You might want to review the archives about that. Hi Peter, I went back as far as 2005 in the archives, and found only this thread covering similar territory: snip The foremost opposing argument seems to have been that there should be no attempt to alleviate the existing reserved word problem without automatic enforcement to guarantee that never in the future can new occurrences be introduced. But can we not separate the two problems of (1) actual identifiers which prevent C++ compilation today, vs. (2) hypothetical code which someone might submit in the future? The first problem is immediate; the second would only be troublesome if the hypothetical identifier makes it all the way through beta testing into a release. Actually, given your configure changes, istm a buildfarm member compiling with --enablecplusplus would prevent any such issue from getting to far. snip PS. A few other threads had (at least somewhat) relevant discussion. They're listed below. I didn't find any other patches. I'd appreciate any links or pointers to any other threads which I should look at. Might I suggest you collect all of these various arguments (both for and against) and patches into a wiki page on the developers wiki? Also, I've no real experience in masquerading c++ as c, but the main concern I would have is possible imcompatabilities that might be introduced between postgresql's compiled with c++ and those compiled in c. I'm not sure there should be any, but maybe someone with more experience in this area might have ideas on what to watch out for? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Statement-level triggers and inheritance
On Friday 28 November 2008 16:59:19 Tom Lane wrote: Greg Sabino Mullane [EMAIL PROTECTED] writes: Looks like inheritance causes a statement-level trigger to fire on the last evaluated table in the inheritance chain. Is this the desired behavior? Hm, I think whoever wrote the statement-level trigger code completely forgot to consider the possibility of multiple target relations. It's not even consistent between BEFORE and AFTER triggers for this case. My feeling is that it ought to fire such triggers on *each* target. This would amount to statement level triggers firing multiple times per statement wouldn't it? That behavior might be rather surprising for folks. I guess the alternative is to have it fire only on the parent in an inheritance stack. I'm not sure that's much more defensible, but maybe it's more practical? -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] Logging auto_explain outputs to another log file
On Sunday 23 November 2008 15:50:09 Andrew Dunstan wrote: Tom Lane wrote: Devrim =?ISO-8859-1?Q?G=DCND=DCZ?= [EMAIL PROTECTED] writes: Is $SUBJECT possible? If not, do you think it would be worth implementing this? No, and no. The feature isn't even in core; it can hardly qualify as something that should drive a massive overhaul of the elog infrastructure. Which is what this would take. Well, it might be possible to build some sort of splitting facility (regex based?) into the logging collector without having to change the rest of the logging infrastructure. But there are already good log splitting tools for some varieties of syslog, and like Tom I suspect using any effort in this direction on our part is probably not worth it. How would you folks feel about adding a dtrace probe to look for this? I haven't exactly worked out where/how this would be put, but it would allow for easily tracking these via dtrace if we had one. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
