Re: [HACKERS] ISAM to SQL
On Thu, Mar 28, 2013 at 9:47 PM, John Mudd wrote: > > I see a few old messages referring to ISAM to SQL emulation. > > For example: > > http://www.postgresql.org/message-id/200402171616.i1hgg9u11...@candle.pha.pa.us > > Does anyone know of any actual source code to one of these projects? Any > active projects? I wrote an unfortunately closed source ISAM driver for postgresql for COBOL (AcuCobol). It was not a too terribly difficult project -- Postgres has a couple of features that made it pretty easy, especially advisory locks and row-wise comparison. Type conversion can be difficult or complex depending on how far you want to take things and how clean the API is that you will be hooking on the ISAM side. I almost put together a C-ISAM driver a couple years back for a project but it fell through. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ISAM to SQL
I see a few old messages referring to ISAM to SQL emulation. For example: http://www.postgresql.org/message-id/200402171616.i1hgg9u11...@candle.pha.pa.us Does anyone know of any actual source code to one of these projects? Any active projects? John
Re: [HACKERS] Draft release notes up for review
Tom Lane wrote: > Since there has been some, um, grumbling about the quality of the > release notes of late, I've prepared draft notes for next week's > releases, covering commits through today. These are now committed > into the master branch for review, and should show up at > http://www.postgresql.org/docs/devel/static/ > after guaibasaurus' next buildfarm run, about three hours from now. I prodded it a bit -- it's up now. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Draft release notes up for review
Since there has been some, um, grumbling about the quality of the release notes of late, I've prepared draft notes for next week's releases, covering commits through today. These are now committed into the master branch for review, and should show up at http://www.postgresql.org/docs/devel/static/ after guaibasaurus' next buildfarm run, about three hours from now. Please comment if you find anything that could be improved. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Changing recovery.conf parameters into GUCs
Hi, The main argument on which this proposal is based on is to keep backward-compatibility. This has been discussed before many times and the position of each people is well-known, so I am not going back to that... So, based on *only* what I see in this thread... On Fri, Mar 29, 2013 at 12:48 AM, Simon Riggs wrote: > What we want to do is make recovery parameters into GUCs, allowing > them to be reset by SIGHUP and also to allow all users to see the > parameters in use, from any session. > > The existing mechanism for recovery is that > 1. we put parameters in a file called recovery.conf > 2. we use the existence of a recovery.conf file to trigger archive > recovery/replication > > I also wish to see backwards compatibility maintained, so am proposing > the following: > > a) recovery parameters are made into GUCs (for which we have a patch > from Fujii) > b) all processes automatically read recovery.conf as the last step in > reading configuration files, if it exists, even if data_directory > parameter is in use (attached patch) > c) we trigger archive recovery by the presence of either > recovery.conf or recovery.trigger in the data directory. At the end, > we rename to recovery.done just as we do now. This means that any > parameters put into recovery.conf will not be re-read when we SIGHUP > after end of recovery. Note that recovery.trigger will NOT be read for > parameters and is assumed to be zero-length. > (minor patch required) > > This allows these use cases > > 1. Existing users create $PGDATA/recovery.conf and everything works as > before. No servers crash, because the HA instructions in the wiki > still work. Users can now see the parameters in pg_settings and we can > use SIGHUP without restarting server. Same stuff, new benefits. > Forcing hardcoding of "include_if_exists recovery.conf" at the bottom of postgresql.conf is not a good thing for the user as it makes postgresql.conf processing more opaque and complicates parameter reloading. IMO, simplicity and transparency of operations are important when processing parameters. 2. New users wish to move their existing recovery.conf file to the > config directory. Same file contents, same file name (if desired), > same behaviour, just more convenient location for config management > tools. Recovery is triggered by recovery.trigger in $PGDATA. Trigger > and configuration are now separate, if desired. > So, people could be able to also define a recovery.trigger file? What about the case where both recovery.conf and recovery.trigger are found in the base folder? Priority needs to be given to one way of processing or the other. Is it really our goal to confuse the user with internal priority mechanisms at least for GUC handling? 3. Split mode. We can put things like trigger_file into the > postgresql.conf directory and also put other parameters (for example > PITR settings) into recovery.conf. Where multiple tools are in use, we > support both APIs. > > Specific details... > > * primary_conninfo, trigger_file and standby_mode are added to > postgresql.conf.sample > Not adding all the recovery_target_* parameters would confuse the user. With this proposal it is actually possible to define a recovery target inside postgresql.conf even if the parameter is not plainly written in postgresql.conf.sample. > * all ex-recovery.conf parameters are SIGHUP, so no errors if > recovery.conf has changed to .done > Drop of recovery.trigger at the same time? And what about the case where both files are specified, that the server can only remove one of the trigger files, and is then restarted with only 1 trigger file present? If desired, this behaviour could be enabled by a parameter called > recovery_conf_enabled = on (default). When set to off, this would > throw an error if recovery.conf exists. (I don't see a need for this) > Me neither, the less GUCs the better. > The patch to implement this is very small (attached). This works > standalone, but obviously barfs at the actual parameter parsing stage. > Just in case it wasn't clear, this patch is intended to go with the > parts of Fujji's patch that relate to GUC changes. > > If we agree, I will merge and re-post before commit. > If an agreement is reached based on this proposal, I highly recommend that you use one of the latest updated version I sent. Fujii's version had some bugs, one of them being that as standbyModeRequested can be set to true if specified in postgresql.conf, a portion of the code using in xlog.c can be triggered even if ArchiveRecoveryRequested is not set to true. I also added fixes related to documentation. Comments from others are welcome. -- Michael
Re: [HACKERS] Enabling Checksums
On Wed, Mar 27, 2013 at 10:15 AM, Andres Freund wrote: > On 2013-03-27 10:06:19 -0400, Robert Haas wrote: >> On Mon, Mar 18, 2013 at 4:31 PM, Greg Smith wrote: >> > to get them going again. If the install had checksums, I could have >> > figured >> > out which blocks were damaged and manually fixed them, basically go on a >> > hunt for torn pages and the last known good copy via full-page write. >> >> Wow. How would you extract such a block image from WAL? >> >> That would be a great tool to have, but I didn't know there was any >> practical way of doing it today. > > Given pg_xlogdump that should be doable with 5min of hacking in 9.3. Just add > some hunk to write out the page to the if (config->bkp_details) hunk in > pg_xlogdump.c:XLogDumpDisplayRecord. I have done that for some debugging > already. > > If somebody comes up with a sensible & simple UI for this I am willing to > propose a patch adding it to pg_xlogdump. One would have to specify the > rel/file/node, the offset, and the target file. Hmm. Cool. But, wouldn't the hard part be to figure out where to start reading the WAL in search of the *latest* FPI? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: [HACKERS] Should array_length() Return NULL)
On 28 March 2013 20:34, Dean Rasheed wrote: > Is the patch also going to allow empty arrays in higher dimensions > where not just the last dimension is empty? It doesn't allow that at present. > It seems as though, if > it's allowing 1-by-0 arrays like '{{}}' and '[4:4][8:7]={{}}', it > should also allow 0-by-0 arrays like '[4:3][8:7]={}', and 0-by-3 > arrays like '[4:3][11:13]={}'. I think the array literal parser would reject this on the grounds that the brace structure doesn't match the dimension specifiers. You could modify that check to respect zero length in dimensions other than the innermost one, but it's hard to say whether it would be worth the effort. It might be instructive to hear from somebody who does (or intends to) use multidim arrays for some practical purpose, but I don't even know whether such a person exists within earshot of this list. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hash Join cost estimates
All, Marty's issue w/ NestLoop reminded me that I'd put together a test case which illustrates a HashJoin doing the wrong thing. The test case is here: http://snowman.net/~sfrost/test_case.sql Simply load that up and then check out this plan: explain select * from small_table join big_table using (id_short); We end up deciding to build a hash table of 4M records and then seq scan the table that only has 41K. Much of the reason for this is because the analytics point out, correctly, that the column we're using from the small table to join isn't unique and therefore the buckets will be deeper- but it's not *nearly* as bad as we estimate. The bucket estimate for the small table comes back as 26, while the reality is that we only look through ~5.5 entries per bucket with the longest run being 21. With the big table being hashed, the bucket estimate is 4 (though this seem to vary way more than I would expect, sometimes 4, sometimes 8, sometimes 2..) while the average number scanned through is actually ~3.5 and the longest scan ends up being 20. Without the bucket question, we end up with pretty reasonable results (directly out of initial_cost_hashjoin): 41K hashed, seqscan 4M: startup_cost = 1229.46 run_cost = 72307.39 4M hashed, 41K seqscan: startup_cost = 115030.10 run_cost = 817.70 When we get through dealing with the bucketing question in final_cost_hashjoin (costsize.c:2673), we have some pretty gross results for the 'good' plan's run_cost: run_cost = 72307.39 + 138848.81 = 211156.20 While the 'bad' plan's run_cost is only bumped a tiny bit: run_cost = 817.7 + 411.76 = 1229.46 Resulting in total costs that look all kinds of wacky: 41K hashed, seqscan 4M: 115030.10 + 1229.46 = 116259.56 4M hashed, seqscan 41K: 1229.46 + 211156.20 = 212385.66 Or the 'good' plan being costed at *nearly double*. Now, my laptop might not be the 'best' system CPU wise, but there's a pretty massive time difference between these plans: 41K hashed, seqscan 4M: 2252.007 ms http://explain.depesz.com/s/FEq 4M hashed, seqscan 41K: 2708.471 ms http://explain.depesz.com/s/FOU That's half a second and a good 15+% difference. Now, back to the bucket estimation- the ndistinct for the small table is -0.475058 (or 19561 tuples), which is about right. There are 23 distinct values, 18,795 duplicated, and another 841 with dup counts ranging from 3 to 10. This leads to an avgfreq of 0.51, unfortunately, we're going for only 8192 buckets, so this gets moved up to 0.00012 and then the adjustment for MCV (which is 0.00027) bumps this all the way up to 0.00064, leading to our bucket depth estimate of 26 (bucket size estimate multiplied by the inner rows) and the resulting cost dominating the overall costing. If we consider the bucketing wrong and look at what the costs would have been with the actual number of average scans (and therefore excluding the 0.5 'adjustment'), we get: seqscan 41K cost: 360.28 (cpu_op_cost * 41K * 3.5) seqscan 4M cost: 58743.73 (cpu_op_cost * 4M * 5.5) which isn't exactly going in the 'right' direction for us. Now, I'm sure that there's a cost to having to consider more buckets, but it seems to be far less, in comparison to the hash table creation cost, than what our model would suggest. In the end, I think the problem here is that we are charging far too much for these bucket costs (particularly when we're getting them so far wrong) and not nearly enough for the cost of building the hash table in the first place. Thoughts? Ideas about how we can 'fix' this? Have others run into similar issues? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Ignore invalid indexes in pg_dump.
On 2013-03-28 17:35:08 -0400, Bruce Momjian wrote: > On Thu, Mar 28, 2013 at 10:31:51PM +0100, anara...@anarazel.de wrote: > > > > > > Tom Lane schrieb: > > > > >Bruce Momjian writes: > > >> Should I just patch pg_upgrade to remove the "indisvalid", skip > > >> "indisvalid" indexes, and backpatch it? Users should be using the > > >> version of pg_upgrade to match new pg_dump. Is there any case where > > >> they don't match? Do I still need to check for "indisready"? > > > > > >Yeah, if you can just ignore !indisvalid indexes that should work fine. > > >I see no need to look at indisready if you're doing that. > > > > You need to look at inisready in 9.2 since thats used for about to be > > dropped indexes. No? > > Well, if it is dropped, pg_dump will not dump it. At this point though, > pg_upgrade is either running in check mode, or it is the only user. I > think we are OK. Its about DROP INDEX CONCURRENTLY which can leave indexes in a partial state visible to the outside. Either just transiently while a DROP CONCURRENLTY is going on or even permanently if the server crashed during such a drop or similar. c.f. index.c:index_drop Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Ignore invalid indexes in pg_dump.
"anara...@anarazel.de" writes: > 9.2 represents inisdead as live && !ready, doesn't it? So just looking at > indislive will include about to be dropped or partially dropped indexes? Ooooh ... you're right, in 9.2 (only) we need to check both indisvalid and indisready (cf IndexIsValid() macro in the different branches). So that's actually a bug in the committed pg_dump patch, too. I'll fix that shortly. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ignore invalid indexes in pg_dump.
On 2013-03-28 17:54:08 -0400, Bruce Momjian wrote: > On Thu, Mar 28, 2013 at 10:47:55PM +0100, anara...@anarazel.de wrote: > > > > > > Tom Lane schrieb: > > > > >"anara...@anarazel.de" writes: > > >> Tom Lane schrieb: > > >>> Yeah, if you can just ignore !indisvalid indexes that should work > > >fine. > > >>> I see no need to look at indisready if you're doing that. > > > > > >> You need to look at inisready in 9.2 since thats used for about to > > >> be > > >dropped indexes. No? > > > > > >No, he doesn't need to look at indisready/indislive; if either of > > >those flags are off then indisvalid should certainly be off too. (If > > >it isn't, queries against the table are already in trouble.) > > > > 9.2 represents inisdead as live && !ready, doesn't it? So just looking > > at indislive will include about to be dropped or partially dropped > > indexes? > > Where do you see 'inisdead' defined? Sorry, its named the reverse, indislive. And its only there in 9.3+... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Ignore invalid indexes in pg_dump.
On Thu, Mar 28, 2013 at 10:47:55PM +0100, anara...@anarazel.de wrote: > > > Tom Lane schrieb: > > >"anara...@anarazel.de" writes: > >> Tom Lane schrieb: > >>> Yeah, if you can just ignore !indisvalid indexes that should work > >fine. > >>> I see no need to look at indisready if you're doing that. > > > >> You need to look at inisready in 9.2 since thats used for about to > >> be > >dropped indexes. No? > > > >No, he doesn't need to look at indisready/indislive; if either of > >those flags are off then indisvalid should certainly be off too. (If > >it isn't, queries against the table are already in trouble.) > > 9.2 represents inisdead as live && !ready, doesn't it? So just looking > at indislive will include about to be dropped or partially dropped > indexes? Where do you see 'inisdead' defined? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ignore invalid indexes in pg_dump.
Tom Lane schrieb: >"anara...@anarazel.de" writes: >> Tom Lane schrieb: >>> Yeah, if you can just ignore !indisvalid indexes that should work >fine. >>> I see no need to look at indisready if you're doing that. > >> You need to look at inisready in 9.2 since thats used for about to be >dropped indexes. No? > >No, he doesn't need to look at indisready/indislive; if either of those >flags are off then indisvalid should certainly be off too. (If it >isn't, queries against the table are already in trouble.) 9.2 represents inisdead as live && !ready, doesn't it? So just looking at indislive will include about to be dropped or partially dropped indexes? Andres --- Please excuse brevity and formatting - I am writing this on my mobile phone. -- 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] Ignore invalid indexes in pg_dump.
"anara...@anarazel.de" writes: > Tom Lane schrieb: >> Yeah, if you can just ignore !indisvalid indexes that should work fine. >> I see no need to look at indisready if you're doing that. > You need to look at inisready in 9.2 since thats used for about to be dropped > indexes. No? No, he doesn't need to look at indisready/indislive; if either of those flags are off then indisvalid should certainly be off too. (If it isn't, queries against the table are already in trouble.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ignore invalid indexes in pg_dump.
On Thu, Mar 28, 2013 at 10:31:51PM +0100, anara...@anarazel.de wrote: > > > Tom Lane schrieb: > > >Bruce Momjian writes: > >> Should I just patch pg_upgrade to remove the "indisvalid", skip > >> "indisvalid" indexes, and backpatch it? Users should be using the > >> version of pg_upgrade to match new pg_dump. Is there any case where > >> they don't match? Do I still need to check for "indisready"? > > > >Yeah, if you can just ignore !indisvalid indexes that should work fine. > >I see no need to look at indisready if you're doing that. > > You need to look at inisready in 9.2 since thats used for about to be dropped > indexes. No? Well, if it is dropped, pg_dump will not dump it. At this point though, pg_upgrade is either running in check mode, or it is the only user. I think we are OK. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ignore invalid indexes in pg_dump.
Tom Lane schrieb: >Bruce Momjian writes: >> Should I just patch pg_upgrade to remove the "indisvalid", skip >> "indisvalid" indexes, and backpatch it? Users should be using the >> version of pg_upgrade to match new pg_dump. Is there any case where >> they don't match? Do I still need to check for "indisready"? > >Yeah, if you can just ignore !indisvalid indexes that should work fine. >I see no need to look at indisready if you're doing that. You need to look at inisready in 9.2 since thats used for about to be dropped indexes. No? Andres --- Please excuse brevity and formatting - I am writing this on my mobile phone. -- 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] Ignore invalid indexes in pg_dump.
Bruce Momjian writes: > Should I just patch pg_upgrade to remove the "indisvalid", skip > "indisvalid" indexes, and backpatch it? Users should be using the > version of pg_upgrade to match new pg_dump. Is there any case where > they don't match? Do I still need to check for "indisready"? Yeah, if you can just ignore !indisvalid indexes that should work fine. I see no need to look at indisready if you're doing that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade segfaults when given an invalid PGSERVICE value
On Thu, Mar 28, 2013 at 03:06:30PM -0400, Steve Singer wrote: > So to summarise: > > Plan A: The first patch I attached for pg_upgrade + documentation > changes, and changing the other places that call PQconndefaults() to > accept failures on either out of memory, or an invalid PGSERVICE > > Plan B: Create a new function PQconndefaults2(char * errorBuffer) or > something similar that returned error information to the caller. > > Plan C: PQconndefaults() just ignores an invalid service but > connection attempts fail because other callers of > conninfo_add_defaults still pay attention to connection failures. > This is the second patch I sent. > > Plan D: Service lookup failures are always ignored by > conninfo_add_defaults. If you attempt to connect with a bad > PGSERVICE set it will behave as if no PGSERVICE value was set. I > don't think anyone explicitly proposed this yet. > > Plan 'D' is the only option that I'm opposed to, it will effect a > lot more applications then ones that call PQconndefaults() and I > feel it will confuse users. > > I'm not convinced plan B is worth the effort of having to maintain > two versions of PQconndefaults() for a while to fix a corner case. Yep, that's pretty much it. I agree B is overkill, and D seems dangerous. I think Tom likes C --- my only question is how many people will be confused that C returns inaccurate information, because though it returns connection options, it doesn't process the service file, while forced processing of the service file for real connections throws an error. We might be fine with C, but it must be documented in the C code and SGML docs. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Ignore invalid indexes in pg_dump.
On Tue, Mar 26, 2013 at 09:43:54PM +, Tom Lane wrote: > Ignore invalid indexes in pg_dump. > > Dumping invalid indexes can cause problems at restore time, for example > if the reason the index creation failed was because it tried to enforce > a uniqueness condition not satisfied by the table's data. Also, if the > index creation is in fact still in progress, it seems reasonable to > consider it to be an uncommitted DDL change, which pg_dump wouldn't be > expected to dump anyway. > > Back-patch to all active versions, and teach them to ignore invalid > indexes in servers back to 8.2, where the concept was introduced. This commit affects pg_upgrade. You might remember we had to patch pg_upgrade to prevent it from migrating clusters with invalid indexes in December, 2012: http://momjian.us/main/blogs/pgblog/2012.html#December_14_2012 This was released on February 7, 2013 in 9.2.3 and other back branches: http://www.postgresql.org/docs/9.2/static/release-9-2-3.html This git commit means that pg_upgrade can again migrate systems with invalid indexes as pg_upgrade can just skip migrating them because pg_dump will dump the SQL commands to create them --- previously pg_upgrade threw an error. Should I just patch pg_upgrade to remove the "indisvalid", skip "indisvalid" indexes, and backpatch it? Users should be using the version of pg_upgrade to match new pg_dump. Is there any case where they don't match? Do I still need to check for "indisready"? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Call for Google Summer of Code mentors, admins
Hi, I've been asked by a local student to be his mentor in GSoC. He is interested in working on parallelizing hash join/aggregation. So I'd like to volunteer to be a mentor for this project (if it gets accepted). I've asked him to post his project proposal on pgsql-students. kind regards Tomas On 14.2.2013 19:02, Josh Berkus wrote: > Folks, > > Once again, Google is holding Summer of Code. We need to assess whether > we want to participate this year. > > Questions: > > - Who wants to mentor for GSOC? > > - Who can admin for GSOC? Thom? > > - Please suggest project ideas for GSOC > > - Students seeing this -- please speak up if you have projects you plan > to submit. > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: sepgsql: Support for new post-ALTER access hook.
On Wed, Mar 27, 2013 at 11:27 AM, Thom Brown wrote: > On 27 March 2013 15:19, Robert Haas wrote: >> On Wed, Mar 27, 2013 at 10:51 AM, Thom Brown wrote: create here is referring to the sepgsql permission, not the SQL command, so it's correct as-is. >>> >>> My bad. >> >> Here's an attempt at reworking the whole section to be more >> understandable. I took the liberty of rearranging the text into >> bulleted lists, which I hope is more clear. >> >> Comments welcome. > > This looks a lot better, apart from: > > s/permssions/permissions/ > > +1 from me. OK, committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [sepgsql 1/3] add name qualified creation label
On Thu, Mar 28, 2013 at 12:33 PM, Kohei KaiGai wrote: > Thanks for your checking. > > I doubt of whether security policy module for this regression test is not > installed on your test environment. Ah, you are right. Sorry for the noise. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade segfaults when given an invalid PGSERVICE value
On 13-03-26 12:40 AM, Tom Lane wrote: Bruce Momjian writes: On Mon, Mar 25, 2013 at 07:07:42PM -0400, Tom Lane wrote: Well, plan B would be to invent a replacement function that does have the ability to return an error message, but that seems like a lot of work for a problem that's so marginal that it wasn't noticed till now. (It's not so much creating the function that worries me, it's fixing clients to use it.) Plan C would be to redefine bogus value of PGSERVICE as not an error, period. Given all of these poor options, is defining a PQconndefaults() as perhaps out of memory or a service file problem really not better? Uh ... no. In the first place, what evidence have you got that those are (and will continue to be) the only two possible causes? In the second place, this still requires changing every client of PQconndefaults(), even if it's only to the extent of fixing their error message texts. If we're going to do that, I'd rather ask them to change to a more future-proof solution. So to summarise: Plan A: The first patch I attached for pg_upgrade + documentation changes, and changing the other places that call PQconndefaults() to accept failures on either out of memory, or an invalid PGSERVICE Plan B: Create a new function PQconndefaults2(char * errorBuffer) or something similar that returned error information to the caller. Plan C: PQconndefaults() just ignores an invalid service but connection attempts fail because other callers of conninfo_add_defaults still pay attention to connection failures. This is the second patch I sent. Plan D: Service lookup failures are always ignored by conninfo_add_defaults. If you attempt to connect with a bad PGSERVICE set it will behave as if no PGSERVICE value was set. I don't think anyone explicitly proposed this yet. Plan 'D' is the only option that I'm opposed to, it will effect a lot more applications then ones that call PQconndefaults() and I feel it will confuse users. I'm not convinced plan B is worth the effort of having to maintain two versions of PQconndefaults() for a while to fix a corner case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Extra security measures for next week's releases
The core committee has decided that one of the security issues due to be fixed next week is sufficiently bad that we need to take extra measures to prevent it from becoming public before packages containing the fix are available. (This is a scenario we've discussed before, but never had to actually implement.) What we intend to do is shut off updates from the master git repo to the anonymous-git mirror, and to github, from Monday afternoon until Thursday morning. Commit-log emails to pgsql-committers will also be held for this period. This will prevent the commits that fix and document the bug from becoming visible to anyone except Postgres committers. Updates will resume as soon as the release announcement is made. Although committers will still be able to work normally, we realize that this is likely to be a handicap for non-committers; and it will also mean that buildfarm runs will not test any new commits until the mirrors are allowed to update. We do not intend to start doing this as a routine thing, and apologize in advance for any disruption. It seems necessary in this instance, however. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [sepgsql 1/3] add name qualified creation label
Thanks for your checking. I doubt of whether security policy module for this regression test is not installed on your test environment. Could you try ./test_sepgsql after: $ make -f /usr/share/selinux/devel/Makefile clean $ make -f /usr/share/selinux/devel/Makefile $ sudo semodule -i sepgsql-regtest $ sudo semodule -l | grep sepgsql-regtest sepgsql-regtest 1.05 I expect the installed sepgsql-regtest should be 1.05. This patch contains updates towards the security policy that adds special rule to assign special default security label on system columns, so regression test will fail if previous policy was loaded. It might ought to be checked within ./test_sepgsql script, however, it takes superuser privilege to run semodule -l even though it lists all the modules without any modification... Thanks, 2013/3/28 Robert Haas : > On Wed, Mar 27, 2013 at 8:41 AM, Robert Haas wrote: >> Based on KaiGai's analysis, it seems to me that there is no serious >> problem here in terms of versioning, and as this patch represents a >> small but useful step forward in our support for SELinux integration, >> I'd like to go ahead and push it. >> >> Are there serious objections to that course of action? > > Sounds like not, but when I ran the sepgsql regression tests with this > applied, they failed in the following way: > > *** /home/rhaas/pgsql/contrib/sepgsql/expected/label.out > 2013-03-28 10:49:26.513998274 -0400 > --- /home/rhaas/pgsql/contrib/sepgsql/results/label.out 2013-03-28 > 10:50:50.818996744 -0400 > *** > *** 95,106 >column | t3.tableoid | unconfined_u:object_r:user_sepgsql_table_t:s0 >column | t4.n| unconfined_u:object_r:sepgsql_table_t:s0 >column | t4.m| unconfined_u:object_r:sepgsql_table_t:s0 > ! column | t4.ctid | unconfined_u:object_r:sepgsql_sysobj_t:s0 > ! column | t4.xmin | unconfined_u:object_r:sepgsql_sysobj_t:s0 > ! column | t4.cmin | unconfined_u:object_r:sepgsql_sysobj_t:s0 > ! column | t4.xmax | unconfined_u:object_r:sepgsql_sysobj_t:s0 > ! column | t4.cmax | unconfined_u:object_r:sepgsql_sysobj_t:s0 > ! column | t4.tableoid | unconfined_u:object_r:sepgsql_sysobj_t:s0 > (16 rows) > > -- > --- 95,106 >column | t3.tableoid | unconfined_u:object_r:user_sepgsql_table_t:s0 >column | t4.n| unconfined_u:object_r:sepgsql_table_t:s0 >column | t4.m| unconfined_u:object_r:sepgsql_table_t:s0 > ! column | t4.ctid | unconfined_u:object_r:sepgsql_table_t:s0 > ! column | t4.xmin | unconfined_u:object_r:sepgsql_table_t:s0 > ! column | t4.cmin | unconfined_u:object_r:sepgsql_table_t:s0 > ! column | t4.xmax | unconfined_u:object_r:sepgsql_table_t:s0 > ! column | t4.cmax | unconfined_u:object_r:sepgsql_table_t:s0 > ! column | t4.tableoid | unconfined_u:object_r:sepgsql_table_t:s0 > (16 rows) > > -- > > Some trivial rebasing appears needed as well. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company -- KaiGai Kohei -- 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] FDW for PostgreSQL
* Tom Lane (t...@sss.pgh.pa.us) wrote: > I don't want to have API features that make connections > explicit, because I don't think that can be shoehorned into the FDW > model without considerable strain and weird corner cases. It seems we're talking past each other here. I'm not particularly interested in exposing what connections have been made to other servers via some API (though I could see some debugging use there). What I was hoping for is a way for a given user to say "I want this specific change, to this table, to be persisted immediately". I'd love to have that ability *without* FDWs too. It just happens that FDWs provide a simple way to get there from here and without a lot of muddying of the waters, imv. FDWs are no stranger to remote connections which don't have transactions either, file_fdw will happily return whatever the current contents of the file are with no concern for PG transactions. I would expect a writable file_fdw to act the same and immediately write out any data written to it. Hope that clarifies things a bit. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] sql_drop Event Triggerg
Pushed, with some further minor changes. One not-so-minor change I introduced was that pg_event_trigger_dropped_objects() now only works within a sql_drop event function. The reason I decided to do this was that if we don't have that protection, then it is possible to have a ddl_command_end trigger calling pg_event_trigger_dropped_objects(); and if there is an sql_drop trigger, then it'd return the list of dropped objects, but if there's no sql_drop trigger, it'd raise an error. That seemed surprising enough action-at-a-distance that some protection is warranted. Thanks for all the review. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] FDW for PostgreSQL
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> ... The only way to >> make this sane at all would be to provide user control of which >> operations go to which connections; which is inherent in dblink's API >> but is simply not a concept in the FDW universe. And I don't want to >> try to plaster it on, either. > This concern would make a lot more sense to me if we were sharing a > given FDW connection between multiple client backends/sessions; I admit > that I've not looked through the code but the documentation seems to > imply that we create one-or-more FDW connection per backend session and > there's no sharing going on. Well, ATM postgres_fdw shares connections across tables and queries; but my point is that that's all supposed to be transparent and invisible to the user. I don't want to have API features that make connections explicit, because I don't think that can be shoehorned into the FDW model without considerable strain and weird corner cases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Changing recovery.conf parameters into GUCs
What we want to do is make recovery parameters into GUCs, allowing them to be reset by SIGHUP and also to allow all users to see the parameters in use, from any session. The existing mechanism for recovery is that 1. we put parameters in a file called recovery.conf 2. we use the existence of a recovery.conf file to trigger archive recovery/replication I also wish to see backwards compatibility maintained, so am proposing the following: a) recovery parameters are made into GUCs (for which we have a patch from Fujii) b) all processes automatically read recovery.conf as the last step in reading configuration files, if it exists, even if data_directory parameter is in use (attached patch) c) we trigger archive recovery by the presence of either recovery.conf or recovery.trigger in the data directory. At the end, we rename to recovery.done just as we do now. This means that any parameters put into recovery.conf will not be re-read when we SIGHUP after end of recovery. Note that recovery.trigger will NOT be read for parameters and is assumed to be zero-length. (minor patch required) This allows these use cases 1. Existing users create $PGDATA/recovery.conf and everything works as before. No servers crash, because the HA instructions in the wiki still work. Users can now see the parameters in pg_settings and we can use SIGHUP without restarting server. Same stuff, new benefits. 2. New users wish to move their existing recovery.conf file to the config directory. Same file contents, same file name (if desired), same behaviour, just more convenient location for config management tools. Recovery is triggered by recovery.trigger in $PGDATA. Trigger and configuration are now separate, if desired. 3. Split mode. We can put things like trigger_file into the postgresql.conf directory and also put other parameters (for example PITR settings) into recovery.conf. Where multiple tools are in use, we support both APIs. Specific details... * primary_conninfo, trigger_file and standby_mode are added to postgresql.conf.sample * all ex-recovery.conf parameters are SIGHUP, so no errors if recovery.conf has changed to .done If desired, this behaviour could be enabled by a parameter called recovery_conf_enabled = on (default). When set to off, this would throw an error if recovery.conf exists. (I don't see a need for this) The patch to implement this is very small (attached). This works standalone, but obviously barfs at the actual parameter parsing stage. Just in case it wasn't clear, this patch is intended to go with the parts of Fujji's patch that relate to GUC changes. If we agree, I will merge and re-post before commit. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services read_recovery.conf_from_data_directory.v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW for PostgreSQL
* Tom Lane (t...@sss.pgh.pa.us) wrote: > TBH I think this is a fairly bad idea. You can get that behavior via > dblink if you need it, While I appreciate that dblink can do it, I simply don't see it as a good solution to this. > but there's no way to do it in an FDW without > ending up with astonishing (and not in a good way) semantics. A commit > would force committal of everything that'd been done through that > connection, regardless of transaction/subtransaction structure up to > that point; and it would also destroy open cursors. The only way to > make this sane at all would be to provide user control of which > operations go to which connections; which is inherent in dblink's API > but is simply not a concept in the FDW universe. And I don't want to > try to plaster it on, either. This concern would make a lot more sense to me if we were sharing a given FDW connection between multiple client backends/sessions; I admit that I've not looked through the code but the documentation seems to imply that we create one-or-more FDW connection per backend session and there's no sharing going on. A single backend will be operating in a linear fashion through the commands sent to it. As such, I'm not sure that it's quite as bad as it may seem. Perhaps a reasonable compromise would be to have a SERVER option which is along the lines of 'autocommit', where a user could request that any query to this server is automatically committed independent of the client transaction. I'd be happier if we could allow the user to control it, but this would at least allow for 'log tables', which are defined using this server definition, where long-running pl/pgsql code could log progress where other connections could see it. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Catching resource leaks during WAL replay
Heikki Linnakangas writes: > On 28.03.2013 01:01, Tom Lane wrote: >> Simon Riggs writes: >>> I'm inclined to think that the overhead isn't worth the trouble. This >>> is the only bug of its type we had in recent years. >> I agree that checking for resource leaks after each WAL record seems >> too expensive compared to what we'd get for it. But perhaps it's worth >> making a check every so often, like at restartpoints? > That sounds very seldom. How about making it an assertion to check after > every record? I guess I'll have to do some testing to see how expensive > it really is. Well, the actually productive part of this patch is to reduce such a failure from ERROR to WARNING, which seems like it probably only requires *one* resource cleanup after we exit the apply loop. Doing it per restartpoint is probably reasonable to limit the resource owner's memory consumption (if there were a leak) over a long replay sequence. I am really not seeing much advantage to doing it per record. I suppose you are thinking of being helpful during development, but if anything I would argue that the current behavior of a hard failure is best for development. It guarantees that the developer will notice the failure, if it occurs at all in his test scenario; whereas a WARNING that goes only to the postmaster log will be very very easily missed. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Allow external recovery_config_directory
On 28 March 2013 13:47, Heikki Linnakangas wrote: >> Therefore anybody using pg_basebackup and the config_file parameter >> does *not* have an executable backup when used with the -R option, as >> Heikki was suggesting was a requirement for this patch. > > > That's not related to the -R option, the situation with config_file is the > same with or without it. Yes, it is related to -R. You said that with -R we are expecting to have a fully executable backup, which won't happen because the config files are missing. That is the situation now and the fact the patch had the same issue is not relevant. It's a pre-existing issue. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Catching resource leaks during WAL replay
On 28.03.2013 01:01, Tom Lane wrote: Simon Riggs writes: On 27 March 2013 20:40, Heikki Linnakangas wrote: While looking at bug #7969, it occurred to me that it would be nice if we could catch resource leaks in WAL redo routines better. It would be useful during development, to catch bugs earlier, and it could've turned that replay-stopping error into a warning. I'm inclined to think that the overhead isn't worth the trouble. This is the only bug of its type we had in recent years. I agree that checking for resource leaks after each WAL record seems too expensive compared to what we'd get for it. But perhaps it's worth making a check every so often, like at restartpoints? That sounds very seldom. How about making it an assertion to check after every record? I guess I'll have to do some testing to see how expensive it really is. - Heikki -- 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] [sepgsql 1/3] add name qualified creation label
On Wed, Mar 27, 2013 at 8:41 AM, Robert Haas wrote: > Based on KaiGai's analysis, it seems to me that there is no serious > problem here in terms of versioning, and as this patch represents a > small but useful step forward in our support for SELinux integration, > I'd like to go ahead and push it. > > Are there serious objections to that course of action? Sounds like not, but when I ran the sepgsql regression tests with this applied, they failed in the following way: *** /home/rhaas/pgsql/contrib/sepgsql/expected/label.out 2013-03-28 10:49:26.513998274 -0400 --- /home/rhaas/pgsql/contrib/sepgsql/results/label.out 2013-03-28 10:50:50.818996744 -0400 *** *** 95,106 column | t3.tableoid | unconfined_u:object_r:user_sepgsql_table_t:s0 column | t4.n| unconfined_u:object_r:sepgsql_table_t:s0 column | t4.m| unconfined_u:object_r:sepgsql_table_t:s0 ! column | t4.ctid | unconfined_u:object_r:sepgsql_sysobj_t:s0 ! column | t4.xmin | unconfined_u:object_r:sepgsql_sysobj_t:s0 ! column | t4.cmin | unconfined_u:object_r:sepgsql_sysobj_t:s0 ! column | t4.xmax | unconfined_u:object_r:sepgsql_sysobj_t:s0 ! column | t4.cmax | unconfined_u:object_r:sepgsql_sysobj_t:s0 ! column | t4.tableoid | unconfined_u:object_r:sepgsql_sysobj_t:s0 (16 rows) -- --- 95,106 column | t3.tableoid | unconfined_u:object_r:user_sepgsql_table_t:s0 column | t4.n| unconfined_u:object_r:sepgsql_table_t:s0 column | t4.m| unconfined_u:object_r:sepgsql_table_t:s0 ! column | t4.ctid | unconfined_u:object_r:sepgsql_table_t:s0 ! column | t4.xmin | unconfined_u:object_r:sepgsql_table_t:s0 ! column | t4.cmin | unconfined_u:object_r:sepgsql_table_t:s0 ! column | t4.xmax | unconfined_u:object_r:sepgsql_table_t:s0 ! column | t4.cmax | unconfined_u:object_r:sepgsql_table_t:s0 ! column | t4.tableoid | unconfined_u:object_r:sepgsql_table_t:s0 (16 rows) -- Some trivial rebasing appears needed as well. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW for PostgreSQL
Stephen Frost writes: > Apologies for bringing this up pretty late, but wrt writable FDW > transaction levels, I was *really* hoping that we'd be able to implement > autonomous transactions on top of writeable FDWs. It looks like there's > no way to do this using the postgres_fdw due to it COMMIT'ing only when > the client transaction commits. Would it be possible to have a simply > function which could be called to say "commit the transaction on the > foreign side for this server/table/connection/whatever"? A nice > addition on top of that would be able to define 'auto-commit' for a > given table or server. TBH I think this is a fairly bad idea. You can get that behavior via dblink if you need it, but there's no way to do it in an FDW without ending up with astonishing (and not in a good way) semantics. A commit would force committal of everything that'd been done through that connection, regardless of transaction/subtransaction structure up to that point; and it would also destroy open cursors. The only way to make this sane at all would be to provide user control of which operations go to which connections; which is inherent in dblink's API but is simply not a concept in the FDW universe. And I don't want to try to plaster it on, either. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Allow external recovery_config_directory
On 28.03.2013 14:45, Simon Riggs wrote: On 28 March 2013 11:36, Robert Haas wrote: On Thu, Mar 28, 2013 at 6:23 AM, Simon Riggs wrote: No, it *would* take effect. The parameter is set in a config file that is not part of the backup, so if you start the server from the backup then it doesn't know that the recovery_config_directory had been set and so it would read the recovery.conf written by pg_basebackup. Are you saying pg_basebackup doesn't back up postgresql.conf? I thought it did. postgresql.conf will be backed up if it is present in the data directory. If it is not present, it is not backed up. Right. Therefore anybody using pg_basebackup and the config_file parameter does *not* have an executable backup when used with the -R option, as Heikki was suggesting was a requirement for this patch. That's not related to the -R option, the situation with config_file is the same with or without it. But yes, if you use config_file option to point outside the data directory, the config file won't be backed up. That feels intuitive to me, I wouldn't expect it to be. Same with include or include_dir directives in the config file, as well as hba_file and ident_file - I wouldn't expect any of the files that those point to to be included in the backup. The filesystem-level backup procedure documented in the user manual, not using pg_basebackup, behaves the same. pg_basebackup's behaviour with respect to .conf files is undocumented so its a "feature" that it skips .conf files in the config_file case. Ok. It's always beem self-evident to me, but I agree it should be documented. How about the attached? - Heikki diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index e444b1c..987802a 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -920,7 +920,10 @@ SELECT pg_stop_backup(); If you are using tablespaces that do not reside underneath this directory, be careful to include them as well (and be sure that your backup dump archives symbolic links as links, otherwise the restore will corrupt -your tablespaces). +your tablespaces). If you have relocated any configuration files +outside the database cluster directory (see + and +), include them as well. diff --git a/doc/src/sgml/ref/pg_basebackup.sgml b/doc/src/sgml/ref/pg_basebackup.sgml index 9fe440a..bdf74a0 100644 --- a/doc/src/sgml/ref/pg_basebackup.sgml +++ b/doc/src/sgml/ref/pg_basebackup.sgml @@ -512,7 +512,11 @@ PostgreSQL documentation The backup will include all files in the data directory and tablespaces, including the configuration files and any additional files placed in the directory by third parties. Only regular files and directories are allowed - in the data directory, no symbolic links or special device files. + in the data directory, no symbolic links or special device files. If you + have relocated any configuration files outside the data directory (see +and + ), they will not be included + in the backup. -- 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] Support for REINDEX CONCURRENTLY
On Thu, Mar 28, 2013 at 10:34 AM, Andres Freund wrote: > On 2013-03-28 10:18:45 +0900, Michael Paquier wrote: >> On Thu, Mar 28, 2013 at 3:12 AM, Fujii Masao wrote: >> Since we call relation_open() with lockmode, ISTM that we should also call >> > relation_close() with the same lockmode instead of NoLock. No? >> > >> Agreed on that. > > That doesn't really hold true generally, its often sensible to hold the > lock till the end of the transaction, which is what not specifying a > lock at close implies. You're right. Even if we release the lock there, the lock is taken again soon and hold till the end of the transaction. There is no need to release the lock there. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Allow external recovery_config_directory
On 28 March 2013 11:36, Robert Haas wrote: > On Thu, Mar 28, 2013 at 6:23 AM, Simon Riggs wrote: >> No, it *would* take effect. The parameter is set in a config file that >> is not part of the backup, so if you start the server from the backup >> then it doesn't know that the recovery_config_directory had been set >> and so it would read the recovery.conf written by pg_basebackup. > > Are you saying pg_basebackup doesn't back up postgresql.conf? I thought it > did. postgresql.conf will be backed up if it is present in the data directory. If it is not present, it is not backed up. Therefore anybody using pg_basebackup and the config_file parameter does *not* have an executable backup when used with the -R option, as Heikki was suggesting was a requirement for this patch. So if we regard that as a bug with the patch, then there is a bug with -R with/without the patch. pg_basebackup's behaviour with respect to .conf files is undocumented so its a "feature" that it skips .conf files in the config_file case. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] FDW for PostgreSQL
Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Another thing I was wondering about, but did not change, is that if we're > having the remote transaction inherit the local transaction's isolation > level, shouldn't it inherit the READ ONLY property as well? Apologies for bringing this up pretty late, but wrt writable FDW transaction levels, I was *really* hoping that we'd be able to implement autonomous transactions on top of writeable FDWs. It looks like there's no way to do this using the postgres_fdw due to it COMMIT'ing only when the client transaction commits. Would it be possible to have a simply function which could be called to say "commit the transaction on the foreign side for this server/table/connection/whatever"? A nice addition on top of that would be able to define 'auto-commit' for a given table or server. I'll try and find time to work on this, but I'd love feedback on if this is possible and where the landmines are. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Default connection parameters for postgres_fdw and dblink
On Wed, Mar 27, 2013 at 8:22 AM, Robert Haas wrote: > On Wed, Mar 27, 2013 at 10:47 AM, Tom Lane wrote:>> >> Use a service file maybe? But you can't have it both ways: either we >> like the behavior of libpq absorbing defaults from the postmaster >> environment, or we don't. You were just arguing this was a bug, and >> now you're calling it a feature. > > Maybe we could compromise and call it a beature. Or a fug. In all > seriousness, it's in that grey area where most people would probably > consider this a surprising and unwanted behavior, but there might be a > few out there who had a problem and discovered that they could use > this trick to solve it. > > In terms of a different solution, what about a GUC that can contain a > connection string which is used to set connection defaults, but which > can still be overriden? So it would mimic the semantics of setting an > environment variable, but it would be better, because you could do all > of the usual GUC things with it instead of having to hack on the > postmaster startup environment. In my meta-experience, "nobody" really wants defaults in that configuration anyway, and if they do, most of them would be appreciative of being notified positively of such a problem of relying on connection defaults (maybe with a warning at first at most?). In this case, such an abrupt change in the contrib is probably fine. >From the vantage point I have: full steam ahead, with the sans GUC solution...it's one less detailed GUC in the world, and its desired outcome can be achieved otherwise. I'd personally rather open myself up for dealing with the consequences of this narrow change in my own corner of the world. This anecdote carries a standard disclaimer: my meta-experience doesn't include all use cases for everyone everywhere. -- fdr -- 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] in-catalog Extension Scripts and Control parameters (templates?)
On Wed, Mar 27, 2013 at 10:32 AM, Alvaro Herrera wrote: >> Surely creating an extension template must be a superuser-only >> operation, in which case this is an issue because Mallory could also >> have just blown up the world directly if he's already a superuser >> anyway. > > Yeah .. (except "this is NOT an issue") > >> If the current patch isn't enforcing that, it's 100% broken. > > Even if it's not enforcing that, it's not 100% broken, it only contains > one more bug we need to fix. Sure. I didn't mean that such a mistake would make the patch unsalvageable, only that it would be disastrous from a security point of view. But as you say, pretty easy to fix. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Allow external recovery_config_directory
On Thu, Mar 28, 2013 at 6:23 AM, Simon Riggs wrote: > No, it *would* take effect. The parameter is set in a config file that > is not part of the backup, so if you start the server from the backup > then it doesn't know that the recovery_config_directory had been set > and so it would read the recovery.conf written by pg_basebackup. Are you saying pg_basebackup doesn't back up postgresql.conf? I thought it did. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Allow external recovery_config_directory
On 28 March 2013 08:31, Heikki Linnakangas wrote: > On 27.03.2013 23:36, Simon Riggs wrote: >> >> Why do you think recovery_config_directory are different to >> config_file with respect to pg_basebackup? > > > pg_basebackup doesn't try to *write* anything to config_file. It does write > to $PGDATA/recovery.conf, with the expectation that it takes effect when you > start the server. > > When you take a backup, I think it's quite reasonable that if you have set > config_file to a different location, that's not backed up. Same with > recovery.conf. But when pg_basebackup *creates* recovery.conf, it's at least > surprising if it doesn't take effect. No, it *would* take effect. The parameter is set in a config file that is not part of the backup, so if you start the server from the backup then it doesn't know that the recovery_config_directory had been set and so it would read the recovery.conf written by pg_basebackup. If you backup the parameter files as well, then it would fail, but that is easily handled by saying the recovery.conf can exist either in datadir or recovery_config_directory. I don't see that as a major problem, or change. But for other reasons, I have revoked the patch. You have highlighted that pg_basebackup does *not* take a fully working backup in all cases, especially the -R case where it is clearly broken. It is that pre-existing issue that leads to your complaint, not the patch itself. Please admit that by adding a line to the docs to explain the non-working case of -R. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Exorcise "zero-dimensional" arrays (Was: Re: [HACKERS] Should array_length() Return NULL)
On 28 March 2013 03:01, Tom Lane wrote: > [snip] > ranges *are not arrays*. OK, fair enough. I guess it's the mathematician in me seeing patterns in things that behave similarly, but which are admittedly different. Is the patch also going to allow empty arrays in higher dimensions where not just the last dimension is empty? It seems as though, if it's allowing 1-by-0 arrays like '{{}}' and '[4:4][8:7]={{}}', it should also allow 0-by-0 arrays like '[4:3][8:7]={}', and 0-by-3 arrays like '[4:3][11:13]={}'. That last example seems like the more useful kind of thing to allow, since you might one day be able to append a non-empty 1-D array onto it. As it stands, the patch only allows empty 2-D arrays that are empty in the final dimension, to which the only thing you could append would be more empty 1-D arrays. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Allow external recovery_config_directory
On 27.03.2013 23:36, Simon Riggs wrote: Why do you think recovery_config_directory are different to config_file with respect to pg_basebackup? pg_basebackup doesn't try to *write* anything to config_file. It does write to $PGDATA/recovery.conf, with the expectation that it takes effect when you start the server. When you take a backup, I think it's quite reasonable that if you have set config_file to a different location, that's not backed up. Same with recovery.conf. But when pg_basebackup *creates* recovery.conf, it's at least surprising if it doesn't take effect. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers