[HACKERS] Reconstructing Insert queries with indirection
Hi All, Consider following sequence of commands create type complex as (r float8, i float8); create type quad as (c1 complex, c2 complex); create temp table quadtable(f1 int, q quad); insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66); While parsing the INSERT query, we parse the query with three columns and three values in the target list, but during rewriting we combine q.c1.r and q.c2.i into a single column in the form of FieldStore structure. In Postgres-XC, we deparse these parse trees, to be sent to other PostgreSQL servers. The function processIndirection(), which deparses the indirections, can not handle more than one field in FieldStore node. 7344 /* 7345 * Print the field name. There should only be one target field in 7346 * stored rules. There could be more than that in executable 7347 * target lists, but this function cannot be used for that case. 7348 */ 7349 Assert(list_length(fstore->fieldnums) == 1); 7350 fieldname = get_relid_attribute_name(typrelid, 7351 linitial_int(fstore->fieldnums)); 7352 if (printit) 7353 appendStringInfo(buf, ".%s", quote_identifier(fieldname)); Why is this restriction here? The assertion is added by commit 858d1699. The notes for the commit have following paragraph related to FieldStore deparsing. I chose to represent an assignment ArrayRef as "array[subscripts] := source", which is fairly reasonable and doesn't omit any information. However, FieldStore is problematic because the planner will fold multiple assignments to fields of the same composite column into one FieldStore, resulting in a structure that is hard to understand at all, let alone display comprehensibly. So in that case I punted and just made it print the source expression(s). So, there doesn't seem to be any serious reason behind the restriction. -- Best Wishes, Ashutosh Bapat EntepriseDB Corporation The Enterprise Postgres Company
Re: [HACKERS] Regarding column reordering project for GSoc 2012
On Wed, Mar 21, 2012 at 12:42 AM, Merlin Moncure wrote: > On Tue, Mar 20, 2012 at 12:34 PM, Atri Sharma wrote: >> >> >> -Original Message- >> From: Merlin Moncure [mailto:mmonc...@gmail.com] >> Sent: 20 March 2012 22:50 >> To: Atri Sharma >> Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; Dave >> Page; Pg Hackers >> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 >> >> On Tue, Mar 20, 2012 at 11:57 AM, Atri Sharma wrote: >>> >>> >>> -Original Message- >>> From: Merlin Moncure [mailto:mmonc...@gmail.com] >>> Sent: 20 March 2012 20:52 >>> To: Atri Sharma >>> Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; >> Dave >>> Page; Pg Hackers >>> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 >>> >>> On Tue, Mar 20, 2012 at 6:58 AM, Atri Sharma wrote: -Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: 20 March 2012 03:15 To: Heikki Linnakangas Cc: Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave >> Page; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On Mon, Mar 19, 2012 at 2:49 PM, Heikki Linnakangas wrote: > On 19.03.2012 21:29, Alvaro Herrera wrote: >> >> >> Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012: >> >>> I was just going through PGfoundry and I think I will be able to work >>> on the PL/Java project.Please let me know If I can proceed further on >>> ot for GSOC and also,if currently there is any work that needs to be >>> done on PL/java. >> >> >> Hm, I'm not sure on PL/Java. As far as I know, it is a dead project. > > > I haven't followed PL/java either, but it doesn't look quite dead to me. The > last release was in September 2011, and there are active discussions on the > mailing lists. If it's not completely up-to-date with latest JDK and > PostgreSQL releases, perhaps bringing it up-to-date and then adding some > missing functionality would be a useful GSoC project. > > I would suggest that you ask on the pl/java mailing list if there is > something suitable for a GSoC project there, and if one of the pl/java > developers would be willing to mentor. pl/java works pretty well and is somewhat widely used although it might need some more active maintainers. just jumping into the project and nailing some old bugs and getting the juices flowing would be a tremendous help, as well as the less glamorous work of documentation and regular status updates. one pl/java based project that IMO is just screaming to be done is a pl/java based FDW (wrapping JDBC) that would approximately reproduce dblink...maybe with some extra features geared towards ETL type jobs like a row based callback for transformations in java. Merlin --- Hi Merlin Thanks for the idea. Could you please elaborate more on the project idea? It would be a great help for me. >>> >>> sure: >>> first start here: http://wiki.postgresql.org/wiki/Foreign_data_wrappers >>> and take a look at all the various fdw projects. they all utilize the >>> high level interfaces that postgresql provides to incorporate external >>> datasources and expose them you can query them as if they were views >>> or tables. as you can see, this interface is popular with the >>> proliferation of projects to expose this or that. what i'm thinking >>> is you make a fdw that invokes pl/java routines that make jdbc calls >>> out to external databases. for fetching data and building sets. as >>> long as you stick to vanilla jdbc calls, you then have a fdw that can >>> gather data from pretty much anything you can access via jdbc url >>> which is quite nice. >>> >>> The fdw API is a C api, so you need to figure out how to proxy to your >>> pl/java calls where the real work is done -- I haven't done that so >>> I'm not sure if this is even technically feasible. If you can make >>> SPI calls from your fdw routines, that's one path through. Tou have >>> lots of previous work to crib from though and hopefully there's a path >>> through. As far as the ETL stuff I was talking about -- that was just >>> me thinking out loud...better to just get the basic mechanics working >>> up front.. For now, if you're interested in doing this, start >>> thinking about how to marry the fdw interface to pl/java...one you get >>> 'hello world' there, it's all about where you want tot take it. >>> >>> This is an interesting project -- you should entertain no illusions of >>> it making it into core, but if done right and completed it will raise >>> visibility of both pl/java and postgresql. >>> >>> Merlin >>> >>> >>> --- >>> Hi Merlin, >>> >>> First of all,thanks a lot for th
Re: [HACKERS] Gsoc2012 Idea --- Social Network database schema
> Date: Tue, 20 Mar 2012 14:12:45 -0700> Subject: Re: [HACKERS] Gsoc2012 Idea > --- Social Network database schema > From: neil.con...@gmail.com > To: huangq...@hotmail.com > CC: dan...@heroku.com; j...@agliodbs.com; pgsql-hackers@postgresql.org > > 2012/3/19 Qi Huang : > >> I actually tried to find out, personally...not sure if I was searching > >> wrongly, but searching for TABLESAMPLE did not yield a cornucopia of > >> useful conversations at the right time in history (~2007), even when > >> the search is given a broad date-horizon (all), so I, too, an > >> uninformed as to the specific objections. > >> > >> http://www.postgresql.org/search/?m=1&q=TABLESAMPLE&l=&d=-1&s=d > > > > I sent a mail to Nail Conway asking him about this. Hope he could give a > > good answer. > > I never tried to get TABLESAMPLE support into the main PostgreSQL tree > -- I just developed the original code as an exercise for the purposes > of the talk. Implementing TABLESAMPLE would probably be a reasonable > GSoc project. > > My memory of the details is fuzzy, but one thing to check is whether > the approach taken by my patch (randomly choose heap pages and then > return all the live tuples in a chosen page) actually meets the > standard's requirements -- obviously it is not true that each heap > page has the same number of live tuples, so you aren't getting a truly > random sample. > > Neil > Thanks so much, Neil. I think I kind of understand the situation for now. The implementation posted by Neil was for the purpose of the talk, thus rushed and may not be up to standard of Postgres Community. Also Neil mentioned the PRNG state in the patch is buggy, and maybe also some others. Thus, in the Gsoc project, I could understand the details of Neil's implementation, fix the bugs, make the code fit for the community standard, and test. Is there any comment on this? Best Regards and ThanksHuang Qi VictorComputer Science of National University of Singapore
Re: [HACKERS] Another review of URI for libpq, v7 submission
Marko Kreen writes:
> On Thu, Mar 15, 2012 at 11:29:31PM +0200, Alex wrote:
>> https://github.com/a1exsh/postgres/commits/uri
>
> The point of the patch is to have one string with all connection options,
> in standard format, yes? So why does not this work:
>
> db = PQconnectdb("postgres://localhost");
>
> ?
Good catch.
I've figured out that we'll need a bit more intrusive change than simply
overriding the expand_dbname check in conninfo_array_parse (like the
current version does) to support URIs in all PQconnect* variants.
I still need to figure out some details, but this is to give people a
status update.
--
Alex
--
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] Memory usage during sorting
On Tue, Mar 20, 2012 at 8:00 PM, Robert Haas wrote: >> Frankly that analysis didn't make any sense to me at the time. >> Comparing integers is fast, sure, but it's still slower than not >> having to do any comparison at all. > > I think you're underestimating how much it costs to call the > datatype-specific comparator. My belief is that it's wicked > expensive. I'm totally with you on the datatype-specific comparator being expensive. But we must be talking about two different scenarios. I don't see why Tom's algorithm was slower than Knuth's unless there was a bug. It seems to me it should perform exactly as many comparator calls but save the integer comparisons and the extra space for them. In the current algorithm, Knuth's, it compares the new tuple against the most recently emitted tuple to set the run number then adds it to the bottom of the heap and sifts up. If it's from the current run it does a bunch of integer comparisons and skips past the next run quickly. If it's from the next run it sifts up to the right spot in the next run and if it hits the top of the next run it does a quick integer comparison and stops. In Tom's algorithm it would perform the same comparison against the recently emitted tuple to set the run number and either add it to the unsorted list or the bottom of the heap. If it's added to the unsorted list we're done, if it's added to the bottom of the heap it performs the same siftup it would have done above except that it skips the bottom few levels of the heap -- all of which were fast integer comparisons. They might be fast but they can't be faster than doing nothing at all. When the heap is empty then we do a heapify which currently would be exactly the same O(nlogn) comparisons that we do maintaining the bottom of the heap but with a O(n) heapify would be fewer. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch for parallel pg_dump
Excerpts from Joachim Wieland's message of mar mar 20 08:26:52 -0300 2012: > On Tue, Mar 20, 2012 at 12:03 AM, Erik Rijkers wrote: > > In my hands, the patch complains: > > Thanks, updated patch attached. Applied, with some minor tweaks, thanks. I didn't try the WIN32 compile. I hope I didn't break it (assuming it was working in your patch.) -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Chronic performance issue with Replication Failover and FSM.
Heikki, > The FSM is included in the base backup, and it is updated when VACUUM > records are replayed. Oh? H. In that case, the issue I'm seeing in production is something else. Unless that was a change for 9.1? > It is also updated when insert/update/delete records are replayed, > athough there's some fuzziness there: records with full page images > don't update the FSM, and the FSM is only updated when the page has less > than 20% of free space left. But that would cause an error in the other > direction, with the FSM claiming that some pages have more free space > than they do in reality. Thanks. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Gsoc2012 Idea --- Social Network database schema
2012/3/19 Qi Huang : >> I actually tried to find out, personally...not sure if I was searching >> wrongly, but searching for TABLESAMPLE did not yield a cornucopia of >> useful conversations at the right time in history (~2007), even when >> the search is given a broad date-horizon (all), so I, too, an >> uninformed as to the specific objections. >> >> http://www.postgresql.org/search/?m=1&q=TABLESAMPLE&l=&d=-1&s=d > > I sent a mail to Nail Conway asking him about this. Hope he could give a > good answer. I never tried to get TABLESAMPLE support into the main PostgreSQL tree -- I just developed the original code as an exercise for the purposes of the talk. Implementing TABLESAMPLE would probably be a reasonable GSoc project. My memory of the details is fuzzy, but one thing to check is whether the approach taken by my patch (randomly choose heap pages and then return all the live tuples in a chosen page) actually meets the standard's requirements -- obviously it is not true that each heap page has the same number of live tuples, so you aren't getting a truly random sample. Neil -- 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] vacuumlo issue
Robert Haas writes: > On Tue, Mar 20, 2012 at 11:50 AM, Tom Lane wrote: >> I see that patch has not made it to any released versions yet. >> Is it too late to rethink the design? I propose (a) redefining it >> as committing after every N objects, and (b) having a limit of 1000 >> or so objects by default. > I'll dispute the characterization of "utterly brain-dead"; it's better > than what we had before, which was nothing. However, I think your > proposal might be better still. Not hearing any objections, I will go make that happen. 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] Memory usage during sorting
Robert Haas writes: > Yeah, I think I'm going to try implementing > quicksort-the-whole-batch-and-dump-it-out-as-a-run algorithm, just to > see how good or bad that is compared to what we have now. We may not > end up doing anything that remotely resembles that, in the end, but I > want to see the numbers. The reason replacement selection is attractive is that the initial runs tend to be about twice as long as you can get if you just sort memory-loads independently. (And that's for random input, the win can be a lot more on partially ordered data.) It seems unlikely that you will get enough win from quicksorting to overcome that; especially not if your thesis is correct that all the cost is coming from comparison infrastructure. But don't let me discourage you from measuring it ... 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] Memory usage during sorting
On 3/18/12 10:25 AM, Tom Lane wrote: Jeff Janes writes: > On Wed, Mar 7, 2012 at 11:55 AM, Robert Haas wrote: >> On Sat, Mar 3, 2012 at 4:15 PM, Jeff Janes wrote: >>> Anyway, I think the logtape could use redoing. > The problem there is that none of the files can be deleted until it > was entirely read, so you end up with all the data on disk twice. I > don't know how often people run their databases so close to the edge > on disk space that this matters, but someone felt that that extra > storage was worth avoiding. Yeah, that was me, and it came out of actual user complaints ten or more years back. (It's actually not 2X growth but more like 4X growth according to the comments in logtape.c, though I no longer remember the exact reasons why.) We knew when we put in the logtape logic that we were trading off speed for space, and we accepted that. It's possible that with the growth of hard drive sizes, real-world applications would no longer care that much about whether the space required to sort is 4X data size rather than 1X. Or then again, maybe their data has grown just as fast and they still care. I believe the case of tape sorts that fit entirely in filesystem cache is a big one as well... doubling or worse the amount of data that needed to live "on disk" at once would likely suck in that case. Also, it's not uncommon to be IO-bound on a database server... so even if we're not worried about storing everything 2 or more times from a disk space standpoint, we should be concerned about the IO bandwidth. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.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] Error trying to compile a simple C trigger
Excerpts from Marco Nenciarini's message of mar mar 20 07:53:06 -0300 2012: > I was trying to compile orafce on the current master and it yield > an error at line > > tupdesc = trigdata->tg_relation->rd_att; > > alert.c: In function ‘dbms_alert_defered_signal’: > alert.c:839:33: error: dereferencing pointer to incomplete type > make: *** [alert.o] Error 1 > > I've also tried the example at > > http://www.postgresql.org/docs/devel/static/trigger-example.html > > and the result is exactly the same. > > trigtest.c: In function ‘trigf’: > trigtest.c:44:36: error: dereferencing pointer to incomplete type > make: *** [trigtest.o] Error 1 Thanks, I believe the example is fixed now. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] vacuumlo issue
> > I think you are asking for this option: > > -l LIMIT stop after removing LIMIT large objects > > which was added in b69f2e36402aaa. Thank you for informing about -l option in 9.2. Can I build/use this contrib with older pg versions i.e. pg 9.1 ? . Thanks. > Uh, no, actually that flag seems utterly brain-dead. Who'd want to > abandon the run after removing some arbitrary subset of the > known-unreferenced large objects? You'd just have to do all the search > work over again. What I'm thinking about is doing a COMMIT after every > N large objects. > > I see that patch has not made it to any released versions yet. > Is it too late to rethink the design? I propose (a) redefining it > as committing after every N objects, and (b) having a limit of 1000 > or so objects by default. > That will be really nice and helpful if it automatically clean all of the orphan large objects. Thanks. > 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] Memory usage during sorting
On Tue, Mar 20, 2012 at 3:41 PM, Greg Stark wrote: > On Tue, Mar 20, 2012 at 5:04 PM, Robert Haas wrote: >> No. It does the opposite: it slows it down. This is a highly >> surprising result but it's quite repeatable: removing comparisons >> makes it slower. As previously pontificated, I think this is probably >> because the heap can fill up with next-run tuples that are cheap to >> compare against, and that spares us having to do "real" comparisons >> involving the actual datatype comparators. > > Frankly that analysis didn't make any sense to me at the time. > Comparing integers is fast, sure, but it's still slower than not > having to do any comparison at all. I think you're underestimating how much it costs to call the datatype-specific comparator. My belief is that it's wicked expensive. The COMPARETUP() macro extracts a function pointer from the Tuplesortstate and calls it; we end up comparetup_heap, which calls ApplySortComparator(), which pulls the comparator function out of the state and then calls that. Since I was sorting strings, which have no sortsupport, we then end up in comparison_shim(), which uses the FunctionCallInvoke method to extract the actual function pointer and jump into bttextcmp(), which unpacks its arguments and then calls text_cmp(), which unpacks its arguments some more and then calls varstr_cmp() where the actual work happens. That's not trivial either - we have to call lc_collate_is_c() and then memcmp(). I have no problem believing that 6 levels of function calls, 3 of which involve jumps through function pointers, followed by lc_collate_is_c() and memcmp() is 100x or more as expensive than the lone integer comparison that happens when the tupindex values don't match - that's a single instruction. > Fwiw I think more interesting than improving tapesort would be > implementing wholly different algorithms like radix sort or the > repeated quicksort. Being able to handle different algorithms that > require a different API would be the first step to being able to > handle parallel algorithms using threads or GPUs. Yeah, I think I'm going to try implementing quicksort-the-whole-batch-and-dump-it-out-as-a-run algorithm, just to see how good or bad that is compared to what we have now. We may not end up doing anything that remotely resembles that, in the end, but I want to see the numbers. -- 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] Memory usage during sorting
On Tue, Mar 20, 2012 at 5:04 PM, Robert Haas wrote: > No. It does the opposite: it slows it down. This is a highly > surprising result but it's quite repeatable: removing comparisons > makes it slower. As previously pontificated, I think this is probably > because the heap can fill up with next-run tuples that are cheap to > compare against, and that spares us having to do "real" comparisons > involving the actual datatype comparators. Frankly that analysis didn't make any sense to me at the time. Comparing integers is fast, sure, but it's still slower than not having to do any comparison at all. It's just barely conceivable that it's a cache effect on the *code* but even that seems pretty darned unlikely to me. My money currently is on a measurement error but that's just a guess since I haven't tried to replicate any of your results. > >> BTW, there's a link at the bottom of the wikipedia page to a very >> interesting ACM Queue article, which argues that the binary-tree >> data structure isn't terribly well suited to virtual memory because >> it touches random locations in succession. I'm not sure I believe >> his particular solution, but I'm wondering about B+ trees, ie more >> than 2 children per node. > > I don't think virtual memory locality is the problem. I read > somewhere that a ternary heap is supposed to be about one-eighth > faster than a binary heap, but that's because picking the smallest of > three tuples requires two comparisons, whereas picking the smallest of > four tuples requires three comparisons, which is better. The things I was reading suggested 4-heap was more cache efficient which is a heap with 4-children per node and still representable as a flat array. There's also Fibonacci heap which makes insertions really fast but since we're doing exactly as many insertions as deletions the question is how much work it adds to deletions. It's still O(logn) amortized but it may be 2x the constant factor. Fwiw I think more interesting than improving tapesort would be implementing wholly different algorithms like radix sort or the repeated quicksort. Being able to handle different algorithms that require a different API would be the first step to being able to handle parallel algorithms using threads or GPUs. I also wonder if disabling the space reuse and just generating separate files for each run might not be a win on exotic filesystems like ZFS or WAFL where overwriting blocks is more expensive than allocating new blocks. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: Create index on foreign table
On Fri, Mar 16, 2012 at 4:44 AM, Etsuro Fujita wrote: > I have a plan to support 'Create index on foreign table' for 9.3. Here > is my plan. > > The index creation is supported for a flat file such as CSV and a remote > table on a RDB e.g., Postgres using CREATE INDEX. (I thought using a > new statement, CREATE FOREIGN INDEX, at first, but I think that CREATE > INDEX would be sufficient to define an index for the foreign table.) > For a flat file, CREATE INDEX constructs an index in the same way as an > index for a regular table. It seems really weird to have the data half inside the database and half outside of it like this. What is the use case for this feature? I would have thought that something like file_fdw would be primarily targeted toward use cases where you intend to read the data file only a few times, or you always intend to sequential scan it. If you need to index it, why not store the data in a regular table? That's what they are for. What you're proposing sounds to me like a lot of work for no real gain. > On the other hand, for a remote table, > CREATE INDEX collects information about the index on the specified > column(s) for the specified table that was created on the remote table. I can possibly see the point of this. Having local information about which remote indexes are available seems like it could be useful. As Heikki says, you could cache it on a per-session basis, but that might not be very efficient. I also think that it would not be a very good idea to have CREATE INDEX on a foreign table sometimes really create an index and other times just define the properties of a remote index. If we're going to have both features at all, I think they should use different syntax. I suggest that CREATE INDEX or CREATE FOREIGN INDEX is the right way to provide the query planner with information about remote-side indexes; and that if we even want to have indexes on flat files, the interface to those should be exported via functions bundled in the file_fdw extension rather than DDL. There's no reason to suppose that the indexes the FDW supports correspond to PostgreSQL's AMs, so tying it into that framework doesn't seem wise. -- 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] [v9.2] Add GUC sepgsql.client_label
On Fri, Mar 16, 2012 at 3:44 AM, Yeb Havinga wrote: > In the patch with copy-editing documentation following that commit, at "in > at their option", s/in// ? Oh, yeah. Oops. Thanks. > Also 'rather than .. as mandated by the system': > I'm having trouble parsing 'as'. It is also unclear to me what 'system' > means: selinux or PostgreSQL, or both? I suspect it is PostgreSQL, since > selinux is still enforcing / 'mandating' it's policy. What about "rather > than that the switch is controlled by the PostgreSQL server, as in the case > of a trusted procedure." Well, I think it's both. PostgreSQL is responsible for enforcing privileges on database objects, but it relies on SE-Linux to tell it whether a given access is allowable. So, from PostgreSQL's point of view, it's delegating the decision to SE-Linux. But SE-Linux views itself as a mechanism for enforcing a system-wide security policy, so views PostgreSQL as an instrument for carrying out its access control goals. I don't know how to disentangle that. I'm actually not entirely sure that I even believe the underlying sentiment that dynamic transitions are dangerous. Maybe KaiGai could comment further on what we should be trying to convey here. -- 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] Regarding column reordering project for GSoc 2012
On Tue, Mar 20, 2012 at 12:34 PM, Atri Sharma wrote: > > > -Original Message- > From: Merlin Moncure [mailto:mmonc...@gmail.com] > Sent: 20 March 2012 22:50 > To: Atri Sharma > Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; Dave > Page; Pg Hackers > Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 > > On Tue, Mar 20, 2012 at 11:57 AM, Atri Sharma wrote: >> >> >> -Original Message- >> From: Merlin Moncure [mailto:mmonc...@gmail.com] >> Sent: 20 March 2012 20:52 >> To: Atri Sharma >> Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; > Dave >> Page; Pg Hackers >> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 >> >> On Tue, Mar 20, 2012 at 6:58 AM, Atri Sharma wrote: >>> -Original Message- >>> From: Merlin Moncure [mailto:mmonc...@gmail.com] >>> Sent: 20 March 2012 03:15 >>> To: Heikki Linnakangas >>> Cc: Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave > Page; >>> Pg Hackers >>> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 >>> >>> On Mon, Mar 19, 2012 at 2:49 PM, Heikki Linnakangas >>> wrote: On 19.03.2012 21:29, Alvaro Herrera wrote: > > > Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012: > >> I was just going through PGfoundry and I think I will be able to work >> on the PL/Java project.Please let me know If I can proceed further on >> ot for GSOC and also,if currently there is any work that needs to be >> done on PL/java. > > > Hm, I'm not sure on PL/Java. As far as I know, it is a dead project. I haven't followed PL/java either, but it doesn't look quite dead to me. >>> The last release was in September 2011, and there are active discussions on >>> the mailing lists. If it's not completely up-to-date with latest JDK and PostgreSQL releases, perhaps bringing it up-to-date and then adding some missing functionality would be a useful GSoC project. I would suggest that you ask on the pl/java mailing list if there is something suitable for a GSoC project there, and if one of the pl/java developers would be willing to mentor. >>> >>> pl/java works pretty well and is somewhat widely used although it >>> might need some more active maintainers. just jumping into the >>> project and nailing some old bugs and getting the juices flowing would >>> be a tremendous help, as well as the less glamorous work of >>> documentation and regular status updates. >>> >>> one pl/java based project that IMO is just screaming to be done is a >>> pl/java based FDW (wrapping JDBC) that would approximately reproduce >>> dblink...maybe with some extra features geared towards ETL type jobs >>> like a row based callback for transformations in java. >>> >>> Merlin >>> >>> >>> --- >>> Hi Merlin >>> >>> Thanks for the idea. >>> >>> Could you please elaborate more on the project idea? It would be a great >>> help for me. >> >> sure: >> first start here: http://wiki.postgresql.org/wiki/Foreign_data_wrappers >> and take a look at all the various fdw projects. they all utilize the >> high level interfaces that postgresql provides to incorporate external >> datasources and expose them you can query them as if they were views >> or tables. as you can see, this interface is popular with the >> proliferation of projects to expose this or that. what i'm thinking >> is you make a fdw that invokes pl/java routines that make jdbc calls >> out to external databases. for fetching data and building sets. as >> long as you stick to vanilla jdbc calls, you then have a fdw that can >> gather data from pretty much anything you can access via jdbc url >> which is quite nice. >> >> The fdw API is a C api, so you need to figure out how to proxy to your >> pl/java calls where the real work is done -- I haven't done that so >> I'm not sure if this is even technically feasible. If you can make >> SPI calls from your fdw routines, that's one path through. Tou have >> lots of previous work to crib from though and hopefully there's a path >> through. As far as the ETL stuff I was talking about -- that was just >> me thinking out loud...better to just get the basic mechanics working >> up front.. For now, if you're interested in doing this, start >> thinking about how to marry the fdw interface to pl/java...one you get >> 'hello world' there, it's all about where you want tot take it. >> >> This is an interesting project -- you should entertain no illusions of >> it making it into core, but if done right and completed it will raise >> visibility of both pl/java and postgresql. >> >> Merlin >> >> >> --- >> Hi Merlin, >> >> First of all,thanks a lot for the extensive reply and explanation. >> >> The project seems very exciting,and I would consider it and honour to be >> able to do it for PostGreSQL. >> >> Basically,from what I u
Re: [HACKERS] Command Triggers patch v18
On 20 March 2012 17:49, Dimitri Fontaine wrote: > Hi, > > I guess I sent v17 a little early considering that we now already have > v18 including support for CREATE TABLE AS and SELECT INTO, thanks to the > work of Andres and Tom. > > There was some spurious tags in the sgml files in v17 that I did clean > up too. The new command triggers work correctly. Having looked at your regression tests, you don't seem to have enough "before" triggers in the tests. There's no test for before CREATE TABLE, CREATE TABLE AS or SELECT INTO. In my tests I have 170 unique command triggers, but there are only 44 in the regression test. Is there a reason why there aren't many tests? A problem still outstanding is that when I build the docs, the CREATE COMMAND TRIGGER is listed after COMMAND TRIGGER in html/sql-commands.html. I recall you mentioned you didn't have this issue on your side. Can you just confirm this again? I believe I've located the cause of this problem. In doc/src/sgml/reference.sgml the ALTER/CREATE/DROP COMMAND TRIGGER references are placed below their respective trigger counterparts. Putting them back into alphabetical order corrects the issue. On the pg_cmdtrigger page in the documentation, I'd recommend the following changes: s/Trigger's name/Trigger name/ s/Command TAG/Command tag/ s/The OID of the function that is called by this command trigger./The OID of the function called by this command trigger./ Also "contype" should read "ctgtype". Note that I haven't tested pl/Perl, pl/Python or pl/tcl yet. Regards -- Thom -- 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] lots of unused variable warnings in assert-free builds
Peter Eisentraut writes: > As you can see, pgindent adds whitespace on top of #ifdef > USE_ASSERT_CHECKING, and messes up the vertical alignment of variable > definitions that contain extra attributes. Hm. I bet it thinks that PG_USED_FOR_ASSERTS_ONLY is the variable name, which means that the behavior might be more exciting for multi-word type names (for instance "struct foo" or "volatile int *". Could you check a few cases like that? > All things considered, I like the PG_USED_FOR_ASSERTS_ONLY solution best. I agree, unless the more complicated cases go further off the rails. 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] lots of unused variable warnings in assert-free builds
On tis, 2012-01-24 at 13:18 -0500, Tom Lane wrote:
> Robert Haas writes:
> > Yes, that's what I meant when I suggested it originally. I'm just not
> > sure it's any nicer than adding ifdefs for USE_ASSERT_CHECKING.
>
> I'm inclined to think that it probably is nicer, just because of less
> vertical space used. But again, this opinion is contingent on what it
> will look like after pgindent gets done with it ...
Here is a demo diff of what pgindent would do with the various
approaches (btw., nice job on making pgindent easy to use for everyone
now).
As you can see, pgindent adds whitespace on top of #ifdef
USE_ASSERT_CHECKING, and messes up the vertical alignment of variable
definitions that contain extra attributes.
All things considered, I like the PG_USED_FOR_ASSERTS_ONLY solution best.
diff --git i/src/backend/access/hash/hashovfl.c w/src/backend/access/hash/hashovfl.c
index 130c296..b61c8ee 100644
--- i/src/backend/access/hash/hashovfl.c
+++ w/src/backend/access/hash/hashovfl.c
@@ -391,7 +391,10 @@ _hash_freeovflpage(Relation rel, Buffer ovflbuf,
uint32 ovflbitno;
int32 bitmappage,
bitmapbit;
+
+#ifdef USE_ASSERT_CHECKING
Bucket bucket;
+#endif
/* Get information from the doomed page */
_hash_checkpage(rel, ovflbuf, LH_OVERFLOW_PAGE);
@@ -400,7 +403,9 @@ _hash_freeovflpage(Relation rel, Buffer ovflbuf,
ovflopaque = (HashPageOpaque) PageGetSpecialPointer(ovflpage);
nextblkno = ovflopaque->hasho_nextblkno;
prevblkno = ovflopaque->hasho_prevblkno;
+#ifdef USE_ASSERT_CHECKING
bucket = ovflopaque->hasho_bucket;
+#endif
/*
* Zero the page for debugging's sake; then write and release it. (Note:
diff --git i/src/backend/executor/execCurrent.c w/src/backend/executor/execCurrent.c
index b07161f..2c8929b 100644
--- i/src/backend/executor/execCurrent.c
+++ w/src/backend/executor/execCurrent.c
@@ -151,7 +151,7 @@ execCurrentOf(CurrentOfExpr *cexpr,
{
ScanState *scanstate;
bool lisnull;
- Oid tuple_tableoid;
+ Oid tuple_tableoid PG_USED_FOR_ASSERTS_ONLY;
ItemPointer tuple_tid;
/*
diff --git i/src/backend/executor/nodeMaterial.c w/src/backend/executor/nodeMaterial.c
index b320b54..3a6bfec 100644
--- i/src/backend/executor/nodeMaterial.c
+++ w/src/backend/executor/nodeMaterial.c
@@ -66,7 +66,7 @@ ExecMaterial(MaterialState *node)
* Allocate a second read pointer to serve as the mark. We know it
* must have index 1, so needn't store that.
*/
- int ptrno;
+ int ptrno PG_USED_FOR_ASSERTS_ONLY;
ptrno = tuplestore_alloc_read_pointer(tuplestorestate,
node->eflags);
diff --git i/src/backend/executor/nodeSetOp.c w/src/backend/executor/nodeSetOp.c
index 7fa5730..ad2e80d 100644
--- i/src/backend/executor/nodeSetOp.c
+++ w/src/backend/executor/nodeSetOp.c
@@ -344,7 +344,7 @@ setop_fill_hash_table(SetOpState *setopstate)
SetOp *node = (SetOp *) setopstate->ps.plan;
PlanState *outerPlan;
int firstFlag;
- bool in_first_rel;
+ bool in_first_rel __attribute__((unused));
/*
* get state info from node
diff --git i/src/include/c.h w/src/include/c.h
index 82acd14..2dd5c67 100644
--- i/src/include/c.h
+++ w/src/include/c.h
@@ -850,4 +850,10 @@ extern int fdatasync(int fildes);
/* /port compatibility functions */
#include "port.h"
+#ifdef USE_ASSERT_CHECKING
+#define PG_USED_FOR_ASSERTS_ONLY
+#else
+#define PG_USED_FOR_ASSERTS_ONLY __attribute__((unused))
+#endif
+
#endif /* C_H */
--
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] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)
On Tue, Mar 20, 2012 at 10:13 AM, Tom Lane wrote: > Robert Haas writes: >> Maybe we should just not worry about this. > > That's been my reaction right along. There's no evidence that PID > recycling is a problem in the real world. I'm entirely willing to acquiesce to that point of view. I only thought this was the blocker as to why pg_terminate_backend was left out of the pg_cancel_backend patch. In that thread, I have since posted the simpler "just let it happen" version. -- 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] Command Triggers, patch v11
Tom Lane writes: > I've applied the CTAS patch after rather heavy editorialization. Don't > know what consequences that will have for Dimitri's patch. It allows my patch to add support for CREATE TABLE AS and SELECT INTO, I've been doing that and am on my way to sending a v18 now. The way you worked out the command tag is exactly what I needed, so thanks a lot for your work comitting this and paying attention :) Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Regarding column reordering project for GSoc 2012
-Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: 20 March 2012 22:50 To: Atri Sharma Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; Dave Page; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On Tue, Mar 20, 2012 at 11:57 AM, Atri Sharma wrote: > > > -Original Message- > From: Merlin Moncure [mailto:mmonc...@gmail.com] > Sent: 20 March 2012 20:52 > To: Atri Sharma > Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; Dave > Page; Pg Hackers > Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 > > On Tue, Mar 20, 2012 at 6:58 AM, Atri Sharma wrote: >> -Original Message- >> From: Merlin Moncure [mailto:mmonc...@gmail.com] >> Sent: 20 March 2012 03:15 >> To: Heikki Linnakangas >> Cc: Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave Page; >> Pg Hackers >> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 >> >> On Mon, Mar 19, 2012 at 2:49 PM, Heikki Linnakangas >> wrote: >>> On 19.03.2012 21:29, Alvaro Herrera wrote: Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012: > I was just going through PGfoundry and I think I will be able to work > on the PL/Java project.Please let me know If I can proceed further on > ot for GSOC and also,if currently there is any work that needs to be > done on PL/java. Hm, I'm not sure on PL/Java. As far as I know, it is a dead project. >>> >>> >>> I haven't followed PL/java either, but it doesn't look quite dead to me. >> The >>> last release was in September 2011, and there are active discussions on >> the >>> mailing lists. If it's not completely up-to-date with latest JDK and >>> PostgreSQL releases, perhaps bringing it up-to-date and then adding some >>> missing functionality would be a useful GSoC project. >>> >>> I would suggest that you ask on the pl/java mailing list if there is >>> something suitable for a GSoC project there, and if one of the pl/java >>> developers would be willing to mentor. >> >> pl/java works pretty well and is somewhat widely used although it >> might need some more active maintainers. just jumping into the >> project and nailing some old bugs and getting the juices flowing would >> be a tremendous help, as well as the less glamorous work of >> documentation and regular status updates. >> >> one pl/java based project that IMO is just screaming to be done is a >> pl/java based FDW (wrapping JDBC) that would approximately reproduce >> dblink...maybe with some extra features geared towards ETL type jobs >> like a row based callback for transformations in java. >> >> Merlin >> >> >> --- >> Hi Merlin >> >> Thanks for the idea. >> >> Could you please elaborate more on the project idea? It would be a great >> help for me. > > sure: > first start here: http://wiki.postgresql.org/wiki/Foreign_data_wrappers > and take a look at all the various fdw projects. they all utilize the > high level interfaces that postgresql provides to incorporate external > datasources and expose them you can query them as if they were views > or tables. as you can see, this interface is popular with the > proliferation of projects to expose this or that. what i'm thinking > is you make a fdw that invokes pl/java routines that make jdbc calls > out to external databases. for fetching data and building sets. as > long as you stick to vanilla jdbc calls, you then have a fdw that can > gather data from pretty much anything you can access via jdbc url > which is quite nice. > > The fdw API is a C api, so you need to figure out how to proxy to your > pl/java calls where the real work is done -- I haven't done that so > I'm not sure if this is even technically feasible. If you can make > SPI calls from your fdw routines, that's one path through. Tou have > lots of previous work to crib from though and hopefully there's a path > through. As far as the ETL stuff I was talking about -- that was just > me thinking out loud...better to just get the basic mechanics working > up front.. For now, if you're interested in doing this, start > thinking about how to marry the fdw interface to pl/java...one you get > 'hello world' there, it's all about where you want tot take it. > > This is an interesting project -- you should entertain no illusions of > it making it into core, but if done right and completed it will raise > visibility of both pl/java and postgresql. > > Merlin > > > --- > Hi Merlin, > > First of all,thanks a lot for the extensive reply and explanation. > > The project seems very exciting,and I would consider it and honour to be > able to do it for PostGreSQL. > > Basically,from what I understood,I will be building a fdw which will invoke > PL/Java routines which will make JDBC calls to external databases.So,the fdw > can be used to access any database that supports JD
Re: [HACKERS] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)
On Mon, Mar 19, 2012 at 08:48:07PM +, Peter Geoghegan wrote: > On 19 March 2012 19:55, Peter Eisentraut wrote: > > If someone wanted to bite the bullet and do the work, I think we could > > move to a Perl/TAP-based test suite (not pgTAP, but Perl and some fairly > > standard Test::* modules) and reduce that useless reformatting work and > > test more interesting things. Just a thought ... > > I think that that is a good idea. However, I am not a Perl hacker, > though this is the second time that that has left me at a disadvantage > when working on Postgres, so I think it's probably time to learn a > certain amount. My blog entry on this topic might be helpful: http://momjian.us/main/blogs/pgblog/2008.html#October_4_2008_2 -- 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] Regarding column reordering project for GSoc 2012
On Tue, Mar 20, 2012 at 11:57 AM, Atri Sharma wrote: > > > -Original Message- > From: Merlin Moncure [mailto:mmonc...@gmail.com] > Sent: 20 March 2012 20:52 > To: Atri Sharma > Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; Dave > Page; Pg Hackers > Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 > > On Tue, Mar 20, 2012 at 6:58 AM, Atri Sharma wrote: >> -Original Message- >> From: Merlin Moncure [mailto:mmonc...@gmail.com] >> Sent: 20 March 2012 03:15 >> To: Heikki Linnakangas >> Cc: Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave Page; >> Pg Hackers >> Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 >> >> On Mon, Mar 19, 2012 at 2:49 PM, Heikki Linnakangas >> wrote: >>> On 19.03.2012 21:29, Alvaro Herrera wrote: Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012: > I was just going through PGfoundry and I think I will be able to work > on the PL/Java project.Please let me know If I can proceed further on > ot for GSOC and also,if currently there is any work that needs to be > done on PL/java. Hm, I'm not sure on PL/Java. As far as I know, it is a dead project. >>> >>> >>> I haven't followed PL/java either, but it doesn't look quite dead to me. >> The >>> last release was in September 2011, and there are active discussions on >> the >>> mailing lists. If it's not completely up-to-date with latest JDK and >>> PostgreSQL releases, perhaps bringing it up-to-date and then adding some >>> missing functionality would be a useful GSoC project. >>> >>> I would suggest that you ask on the pl/java mailing list if there is >>> something suitable for a GSoC project there, and if one of the pl/java >>> developers would be willing to mentor. >> >> pl/java works pretty well and is somewhat widely used although it >> might need some more active maintainers. just jumping into the >> project and nailing some old bugs and getting the juices flowing would >> be a tremendous help, as well as the less glamorous work of >> documentation and regular status updates. >> >> one pl/java based project that IMO is just screaming to be done is a >> pl/java based FDW (wrapping JDBC) that would approximately reproduce >> dblink...maybe with some extra features geared towards ETL type jobs >> like a row based callback for transformations in java. >> >> Merlin >> >> >> --- >> Hi Merlin >> >> Thanks for the idea. >> >> Could you please elaborate more on the project idea? It would be a great >> help for me. > > sure: > first start here: http://wiki.postgresql.org/wiki/Foreign_data_wrappers > and take a look at all the various fdw projects. they all utilize the > high level interfaces that postgresql provides to incorporate external > datasources and expose them you can query them as if they were views > or tables. as you can see, this interface is popular with the > proliferation of projects to expose this or that. what i'm thinking > is you make a fdw that invokes pl/java routines that make jdbc calls > out to external databases. for fetching data and building sets. as > long as you stick to vanilla jdbc calls, you then have a fdw that can > gather data from pretty much anything you can access via jdbc url > which is quite nice. > > The fdw API is a C api, so you need to figure out how to proxy to your > pl/java calls where the real work is done -- I haven't done that so > I'm not sure if this is even technically feasible. If you can make > SPI calls from your fdw routines, that's one path through. Tou have > lots of previous work to crib from though and hopefully there's a path > through. As far as the ETL stuff I was talking about -- that was just > me thinking out loud...better to just get the basic mechanics working > up front.. For now, if you're interested in doing this, start > thinking about how to marry the fdw interface to pl/java...one you get > 'hello world' there, it's all about where you want tot take it. > > This is an interesting project -- you should entertain no illusions of > it making it into core, but if done right and completed it will raise > visibility of both pl/java and postgresql. > > Merlin > > > --- > Hi Merlin, > > First of all,thanks a lot for the extensive reply and explanation. > > The project seems very exciting,and I would consider it and honour to be > able to do it for PostGreSQL. > > Basically,from what I understood,I will be building a fdw which will invoke > PL/Java routines which will make JDBC calls to external databases.So,the fdw > can be used to access any database that supports JDBC.That will be truly > awesome. > > For connecting the C API and Pl/Java code,we can use JNI(Java Native > Interface).It's not too stable,but it can work if taken care of. That's backwards -- the C code will invoke java, not the other way around -- so JNI is no help. SPI is a C interf
Re: [HACKERS] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)
Robert Haas writes: > Maybe we should just not worry about this. That's been my reaction right along. There's no evidence that PID recycling is a problem in the real world. 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] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)
On Tue, Mar 20, 2012 at 12:48 PM, Tom Lane wrote: > Robert Haas writes: >> Well, I'm not sure it would save anything meaningful to read the PID >> after releasing the lock even if it were safe, so I'd be inclined to >> keep things simple. But on further reflection I had us using the PID >> to find the target PGPROC in the first place, so we don't need to >> "remember" a value that we already know; that step is simply >> redundant. > > I'm confused. If the premise is that PID is untrustworthy as a process > ID, how does searching PGPROC make it more trustworthy? The > hypothetical new owner of the PID could have gotten into PGPROC before > you begin to look. Hmm, I guess that's true. > What would make sense to me is to search PGPROC for some *other* > identifying property (and then set bit, remember PID, unlock, send > signal). But it seems like the key point here is what are we going > to use as an identifying property. Well, Dan's idea of an ascending 64-bit sequence number would work, but then we'd have to whack around the API to pg_cancel_backend and pg_terminate_backend to accept that identifier in lieu of a PID, or have alternate versions defined that way, and we'd have to export the identifiers through pg_stat_activity as well. Maybe we should just not worry about this. -- 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] Memory usage during sorting
On Tue, Mar 20, 2012 at 12:33 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Mar 20, 2012 at 7:44 AM, Greg Stark wrote: >>> Offhand I wonder if this is all because we don't have the O(n) heapify >>> implemented. > >> I'm pretty sure that's not the problem. Even though our heapify is >> not as efficient as it could be, it's plenty fast enough. I thought >> about writing a patch to implement the better algorithm, but it seems >> like a distraction at this point because the heapify step is such a >> small contributor to overall sort time. What's taking all the time is >> the repeated siftup operations as we pop things out of the heap. > > Right, but wouldn't getting rid of the run-number comparisons provide > some marginal improvement in the speed of tuplesort_heap_siftup? No. It does the opposite: it slows it down. This is a highly surprising result but it's quite repeatable: removing comparisons makes it slower. As previously pontificated, I think this is probably because the heap can fill up with next-run tuples that are cheap to compare against, and that spares us having to do "real" comparisons involving the actual datatype comparators. > BTW, there's a link at the bottom of the wikipedia page to a very > interesting ACM Queue article, which argues that the binary-tree > data structure isn't terribly well suited to virtual memory because > it touches random locations in succession. I'm not sure I believe > his particular solution, but I'm wondering about B+ trees, ie more > than 2 children per node. I don't think virtual memory locality is the problem. I read somewhere that a ternary heap is supposed to be about one-eighth faster than a binary heap, but that's because picking the smallest of three tuples requires two comparisons, whereas picking the smallest of four tuples requires three comparisons, which is better. -- 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] Regarding column reordering project for GSoc 2012
-Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: 20 March 2012 20:52 To: Atri Sharma Cc: Heikki Linnakangas; Alvaro Herrera; Daniel Farina; Andrew Dunstan; Dave Page; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On Tue, Mar 20, 2012 at 6:58 AM, Atri Sharma wrote: > -Original Message- > From: Merlin Moncure [mailto:mmonc...@gmail.com] > Sent: 20 March 2012 03:15 > To: Heikki Linnakangas > Cc: Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave Page; > Pg Hackers > Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 > > On Mon, Mar 19, 2012 at 2:49 PM, Heikki Linnakangas > wrote: >> On 19.03.2012 21:29, Alvaro Herrera wrote: >>> >>> >>> Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012: >>> I was just going through PGfoundry and I think I will be able to work on the PL/Java project.Please let me know If I can proceed further on ot for GSOC and also,if currently there is any work that needs to be done on PL/java. >>> >>> >>> Hm, I'm not sure on PL/Java. As far as I know, it is a dead project. >> >> >> I haven't followed PL/java either, but it doesn't look quite dead to me. > The >> last release was in September 2011, and there are active discussions on > the >> mailing lists. If it's not completely up-to-date with latest JDK and >> PostgreSQL releases, perhaps bringing it up-to-date and then adding some >> missing functionality would be a useful GSoC project. >> >> I would suggest that you ask on the pl/java mailing list if there is >> something suitable for a GSoC project there, and if one of the pl/java >> developers would be willing to mentor. > > pl/java works pretty well and is somewhat widely used although it > might need some more active maintainers. just jumping into the > project and nailing some old bugs and getting the juices flowing would > be a tremendous help, as well as the less glamorous work of > documentation and regular status updates. > > one pl/java based project that IMO is just screaming to be done is a > pl/java based FDW (wrapping JDBC) that would approximately reproduce > dblink...maybe with some extra features geared towards ETL type jobs > like a row based callback for transformations in java. > > Merlin > > > --- > Hi Merlin > > Thanks for the idea. > > Could you please elaborate more on the project idea? It would be a great > help for me. sure: first start here: http://wiki.postgresql.org/wiki/Foreign_data_wrappers and take a look at all the various fdw projects. they all utilize the high level interfaces that postgresql provides to incorporate external datasources and expose them you can query them as if they were views or tables. as you can see, this interface is popular with the proliferation of projects to expose this or that. what i'm thinking is you make a fdw that invokes pl/java routines that make jdbc calls out to external databases. for fetching data and building sets. as long as you stick to vanilla jdbc calls, you then have a fdw that can gather data from pretty much anything you can access via jdbc url which is quite nice. The fdw API is a C api, so you need to figure out how to proxy to your pl/java calls where the real work is done -- I haven't done that so I'm not sure if this is even technically feasible. If you can make SPI calls from your fdw routines, that's one path through. Tou have lots of previous work to crib from though and hopefully there's a path through. As far as the ETL stuff I was talking about -- that was just me thinking out loud...better to just get the basic mechanics working up front.. For now, if you're interested in doing this, start thinking about how to marry the fdw interface to pl/java...one you get 'hello world' there, it's all about where you want tot take it. This is an interesting project -- you should entertain no illusions of it making it into core, but if done right and completed it will raise visibility of both pl/java and postgresql. Merlin --- Hi Merlin, First of all,thanks a lot for the extensive reply and explanation. The project seems very exciting,and I would consider it and honour to be able to do it for PostGreSQL. Basically,from what I understood,I will be building a fdw which will invoke PL/Java routines which will make JDBC calls to external databases.So,the fdw can be used to access any database that supports JDBC.That will be truly awesome. For connecting the C API and Pl/Java code,we can use JNI(Java Native Interface).It's not too stable,but it can work if taken care of. Please let me know how to proceed further. Waiting for your reply, Atri -- 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] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)
Robert Haas writes: > Well, I'm not sure it would save anything meaningful to read the PID > after releasing the lock even if it were safe, so I'd be inclined to > keep things simple. But on further reflection I had us using the PID > to find the target PGPROC in the first place, so we don't need to > "remember" a value that we already know; that step is simply > redundant. I'm confused. If the premise is that PID is untrustworthy as a process ID, how does searching PGPROC make it more trustworthy? The hypothetical new owner of the PID could have gotten into PGPROC before you begin to look. What would make sense to me is to search PGPROC for some *other* identifying property (and then set bit, remember PID, unlock, send signal). But it seems like the key point here is what are we going to use as an identifying property. 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] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)
On Tue, Mar 20, 2012 at 4:35 AM, Daniel Farina wrote: > I chose the SessionId mostly because I didn't have a great sense > around how hot the ProcArrayLock is, and it was easy to add a > fine-grained spinlock to just get the flavor of the idea. It's fairly hot, but terminating or canceling backends is a sufficiently rare operation that I don't think it matters. Terminating a large number of backends this way all at once might be enough to create a performance hit for concurrent activities, like a pgbench -S run that's taking snapshots at full speed. But you should recoup that cost in a fraction of a second from the fact that there are now many fewer backends around - even if they were idle, they still make the ProcArray larger, and if they weren't idle, even moreso. So I think there's no problem there that's worth sweating over. > To attempt to simplify your protocol more: is it necessary or sound to > remember the PID at all if one takes a lock on the entire PGPROC > array? At that point backend birth and death contends against that > lock, so the postmaster just has to initialize PGPROC the usual way > (writing over the administrative action words) and then...I don't see > a problem, on first blush. But I'll see your midnight and raise you a > 1:35 AM. Well, I'm not sure it would save anything meaningful to read the PID after releasing the lock even if it were safe, so I'd be inclined to keep things simple. But on further reflection I had us using the PID to find the target PGPROC in the first place, so we don't need to "remember" a value that we already know; that step is simply redundant. > Also, I had a use case that would be roughly right for "cross-backend > administration" I want to temporarily suppress autovacuum/autoanalyze > without having to muck with thrashing configuration files, if > possible. ALTER TABLE can do this on a per-table basis. I think that creating a separate way to control this on a system-wide basis would probably be a mistake; it's rarely a good idea to have two separate systems to manipulate the same behavior. I have often wished that we had an ALTER SYSTEM command that could be used to make certain kinds of state changes in lieu of having GUCs, because there are lots of things (e.g. wal_level) that can only be changed at startup for want of the ability to be certain that a config reload will result in everyone getting the memo. A DDL command would have more flexibility in that regard. But I wouldn't choose to use it for this unless it replaced, rather than duplicated, the existing GUC. -- 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] Memory usage during sorting
Robert Haas writes: > On Tue, Mar 20, 2012 at 7:44 AM, Greg Stark wrote: >> Offhand I wonder if this is all because we don't have the O(n) heapify >> implemented. > I'm pretty sure that's not the problem. Even though our heapify is > not as efficient as it could be, it's plenty fast enough. I thought > about writing a patch to implement the better algorithm, but it seems > like a distraction at this point because the heapify step is such a > small contributor to overall sort time. What's taking all the time is > the repeated siftup operations as we pop things out of the heap. Right, but wouldn't getting rid of the run-number comparisons provide some marginal improvement in the speed of tuplesort_heap_siftup? BTW, there's a link at the bottom of the wikipedia page to a very interesting ACM Queue article, which argues that the binary-tree data structure isn't terribly well suited to virtual memory because it touches random locations in succession. I'm not sure I believe his particular solution, but I'm wondering about B+ trees, ie more than 2 children per node. 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] Memory usage during sorting
On Tue, Mar 20, 2012 at 12:12 PM, Tom Lane wrote: > Greg Stark writes: >> http://en.wikipedia.org/wiki/Binary_heap#Building_a_heap > > Interesting. I'm pretty sure that idea appears nowhere in Knuth > (which might mean it's new enough to have a live patent on it ... > anybody know who invented this?). It's in every introductory algorithms textbook; I'd be shocked if anyone could make an IP claim on it. > But it seems like that should buy > back enough comparisons to justify leaving the next-run tuples out of > the heap (unordered) until the heap becomes empty. You still want to > insert new tuples into the heap if they can go to the current run, of > course. It seems like it should, but if you read (or reread) my long boring analysis upthread, you'll learn that it doesn't. It's slower even if the cost of building a heap is zero. -- 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] Memory usage during sorting
On Tue, Mar 20, 2012 at 7:44 AM, Greg Stark wrote: > On Tue, Mar 20, 2012 at 1:57 AM, Tom Lane wrote: >> That was a long time ago, of course, but I have some vague recollection >> that keeping next-run tuples in the current heap achieves a net savings >> in the total number of comparisons needed to heapify both runs. > > Offhand I wonder if this is all because we don't have the O(n) heapify > implemented. I'm pretty sure that's not the problem. Even though our heapify is not as efficient as it could be, it's plenty fast enough. I thought about writing a patch to implement the better algorithm, but it seems like a distraction at this point because the heapify step is such a small contributor to overall sort time. What's taking all the time is the repeated siftup operations as we pop things out of the heap. -- 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] Memory usage during sorting
Greg Stark writes: > http://en.wikipedia.org/wiki/Binary_heap#Building_a_heap Interesting. I'm pretty sure that idea appears nowhere in Knuth (which might mean it's new enough to have a live patent on it ... anybody know who invented this?). But it seems like that should buy back enough comparisons to justify leaving the next-run tuples out of the heap (unordered) until the heap becomes empty. You still want to insert new tuples into the heap if they can go to the current run, of course. 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] Regarding column reordering project for GSoc 2012
Excerpts from Jeff Janes's message of mar mar 20 12:54:55 -0300 2012: > On Mon, Mar 19, 2012 at 12:29 PM, Alvaro Herrera > wrote: > > Did you have a look at the TODO list? http://wiki.postgresql.org/wiki/Todo > > However, the new user should be aware that many of the things in that > list are better described as "to finish discussing/arguing whether we > want it or not, and what trade offs we are willing to make" rather > than "to do". > > That was my experience, anyway. Feel free to edit the commentary at the top :-) -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Command Triggers
On Tuesday, February 28, 2012 12:43:02 AM Andres Freund wrote:
> On Tuesday, February 28, 2012 12:30:36 AM Tom Lane wrote:
> > Andres Freund writes:
> > > Sorry for letting this slide.
> > >
> > > Is it worth adding this bit to OpenIntoRel? Not sure if there is danger
> > > in allowing anyone to create shared tables
> > >
> > > /* In all cases disallow placing user relations in pg_global */
> > > if (tablespaceId == GLOBALTABLESPACE_OID)
> > >
> > > ereport(ERROR,
> > >
> > > (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> > >
> > >errmsg("only shared relations can be placed in
> > > pg_global
> > >
> > > tablespace")));
> >
> > Ugh ... if that's currently allowed, we definitely need to fix it.
> > But I'm not sure OpenIntoRel is the right place. I'd have expected
> > the test to be at some lower level, like heap_create_with_catalog
> > or so.
>
> Its definitely allowed right now:
>
> test-upgrade=# CREATE TABLE foo TABLESPACE pg_global AS SELECT 1;
> SELECT 1
> Time: 354.097 ms
>
> The analogous check for the missing one in OpenIntoRel for plain relations
> is in defineRelation. heap_create_with_catalog only contains the inverse
> check:
>
> /*
>* Shared relations must be in pg_global (last-ditch check)
>*/
> if (shared_relation && reltablespace != GLOBALTABLESPACE_OID)
> elog(ERROR, "shared relations must be placed in pg_global
> tablespace");
>
>
> Moving it there sounds like a good idea without any problem I can see right
> now. Want me to prepare a patch or is it just the same for you if you do it
> yourself?
Sorry to bother you with that dreary topic further, but this should probably
be fixed before the next set of stable releases.
The check cannot easily be moved to heap_create_with_catalog because e.g.
cluster.c's make_new_heap does heap_create_with_catalog for a temporary copy
of shared relations without being able to mark them as such (because they
don't have the right oid and thus IsSharedRelation would cry). So I think just
adding the same check to the ctas code as the normal DefineRelation contains
is the best way forward.
The attached patch applies from 8.3 to 9.1 (8.2 has conflicts but
thankfully...).
Andres
From 77896a7385d1ef5c793e06c5085a8b37ab5857c9 Mon Sep 17 00:00:00 2001
From: Andres Freund
Date: Tue, 20 Mar 2012 16:33:13 +0100
Subject: [PATCH] Check that the specified tablespace in a CREATE TABLE AS
command is not pg_global
That check was not added to the CTAS code when it was added to the ordinary
CREATE TABLE AS.
It might be nicer to add that check heap_create_with_catalog as well, but thats
not easily possible because e.g. cluster creates a temporary new heap which
cannot be marked shared because there is a fixed list of shared relations (see
IsSharedRelation).
---
src/backend/executor/execMain.c |7 +++
1 files changed, 7 insertions(+), 0 deletions(-)
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 621ad8a..8a43db7 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -43,6 +43,7 @@
#include "access/xact.h"
#include "catalog/heap.h"
#include "catalog/namespace.h"
+#include "catalog/pg_tablespace.h"
#include "catalog/toasting.h"
#include "commands/tablespace.h"
#include "commands/trigger.h"
@@ -2452,6 +2453,12 @@ OpenIntoRel(QueryDesc *queryDesc)
get_tablespace_name(tablespaceId));
}
+ /* In all cases disallow placing user relations in pg_global */
+ if (tablespaceId == GLOBALTABLESPACE_OID)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("only shared relations can be placed in pg_global tablespace")));
+
/* Parse and validate any reloptions */
reloptions = transformRelOptions((Datum) 0,
into->options,
--
1.7.6.409.ge7a85.dirty
--
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] Command Triggers, patch v11
On Tuesday, March 20, 2012 02:39:56 AM Tom Lane wrote: > I've applied the CTAS patch after rather heavy editorialization. Don't > know what consequences that will have for Dimitri's patch. Thanks for all the work you put into this! Looks cleaner now... Thanks, Andres -- 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] vacuumlo issue
On Tue, Mar 20, 2012 at 11:50 AM, Tom Lane wrote: > Josh Kupershmidt writes: >> On Tue, Mar 20, 2012 at 7:53 AM, Tom Lane wrote: >>> I'm not entirely convinced that that was a good idea. However, so far >>> as vacuumlo is concerned, the only reason this is a problem is that >>> vacuumlo goes out of its way to do all the large-object deletions in a >>> single transaction. What's the point of that? It'd be useful to batch >>> them, probably, rather than commit each deletion individually. But the >>> objects being deleted are by assumption unreferenced, so I see no >>> correctness argument why they should need to go away all at once. > >> I think you are asking for this option: >> -l LIMIT stop after removing LIMIT large objects >> which was added in b69f2e36402aaa. > > Uh, no, actually that flag seems utterly brain-dead. Who'd want to > abandon the run after removing some arbitrary subset of the > known-unreferenced large objects? You'd just have to do all the search > work over again. What I'm thinking about is doing a COMMIT after every > N large objects. > > I see that patch has not made it to any released versions yet. > Is it too late to rethink the design? I propose (a) redefining it > as committing after every N objects, and (b) having a limit of 1000 > or so objects by default. I'll dispute the characterization of "utterly brain-dead"; it's better than what we had before, which was nothing. However, I think your proposal might be better still. -- 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] Regarding column reordering project for GSoc 2012
On Mon, Mar 19, 2012 at 12:29 PM, Alvaro Herrera wrote: > > Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012: > >> I was just going through PGfoundry and I think I will be able to work >> on the PL/Java project.Please let me know If I can proceed further on >> ot for GSOC and also,if currently there is any work that needs to be >> done on PL/java. > > Hm, I'm not sure on PL/Java. As far as I know, it is a dead project. > But maybe I'm wrong and somebody can mentor you to do something useful > with it -- there are so many advancements in PLs that I'm sure there > must be something that can be done with PL/Java. > > Did you have a look at the TODO list? http://wiki.postgresql.org/wiki/Todo However, the new user should be aware that many of the things in that list are better described as "to finish discussing/arguing whether we want it or not, and what trade offs we are willing to make" rather than "to do". That was my experience, anyway. Cheers, Jeff -- 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] vacuumlo issue
Josh Kupershmidt writes: > On Tue, Mar 20, 2012 at 7:53 AM, Tom Lane wrote: >> I'm not entirely convinced that that was a good idea. However, so far >> as vacuumlo is concerned, the only reason this is a problem is that >> vacuumlo goes out of its way to do all the large-object deletions in a >> single transaction. What's the point of that? It'd be useful to batch >> them, probably, rather than commit each deletion individually. But the >> objects being deleted are by assumption unreferenced, so I see no >> correctness argument why they should need to go away all at once. > I think you are asking for this option: > -l LIMIT stop after removing LIMIT large objects > which was added in b69f2e36402aaa. Uh, no, actually that flag seems utterly brain-dead. Who'd want to abandon the run after removing some arbitrary subset of the known-unreferenced large objects? You'd just have to do all the search work over again. What I'm thinking about is doing a COMMIT after every N large objects. I see that patch has not made it to any released versions yet. Is it too late to rethink the design? I propose (a) redefining it as committing after every N objects, and (b) having a limit of 1000 or so objects by default. 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] Postgres 8.4 planner question - bad plan, good plan for almost same queries.
=?UTF-8?B?0JTQvNC40YLRgNC40Lk=?= writes: > Running PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC > gcc-4.6.real (Ubuntu/Linaro 4.6.0-7ubuntu1) 4.6.1, 64-bit > under Ubuntu 11.10. > with uuu as ( > select dml.id >from mp_locs12 dml > where (complex conditions, leaving about 100 rows from millions) >) >select label, country, region, parish, city, district, > st_geometrytype(loc::geometry) as gtype, > '0x' || to_hex(type) as n_type, file_name, line > from mp_locs12 dml1 > where dml1.id in (select uu.id from uuu uu) > and not exists ( >select 1 from mp_locs12 dml2 > where dml2.id in (select uu.id from uuu uu) >and dml2.id <> dml1.id >and not st_contains(dml1.loc::geometry, dml2.loc::geometry) > ); I think the reason the planner isn't too bright about this case is http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=cd1f0d04bf06938c0ee5728fc8424d62bcf2eef3 ie, it won't do IN/EXISTS pullup below a NOT EXISTS. HEAD is better, thanks to commit 0816fad6eebddb8f1f0e21635e46625815d690b9, but of course there is no chance at all of back-patching the planner changes that depends on. 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] Memory usage during sorting
On Tue, Mar 20, 2012 at 6:31 AM, Tom Lane wrote: > Greg Stark writes: >> Offhand I wonder if this is all because we don't have the O(n) heapify >> implemented. I think we do already have it implemented. 1/2 the time the tuple stays where it is after one comparison, 1/4 it moves up one level with two comparisons, 1/8 it moves up two levels with 3 comparisons, etc. That series sums up to a constant. Maybe there is a worst-case that makes this fall apart, though. Heapifying something which is already reverse sorted, maybe? > Robert muttered something about that before, but is it real? If you > could do that, I'd think you'd have a less-than-n-log-n sorting > solution. Turning random tuples into heap can be linear. Extracting them while maintaining the heap is NlogN, though. You can't sort without the extraction step, so the law is preserved. Cheers, Jeff -- 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] Memory usage during sorting
http://en.wikipedia.org/wiki/Binary_heap#Building_a_heap -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] vacuumlo issue
On Tue, Mar 20, 2012 at 7:53 AM, Tom Lane wrote: > I'm not entirely convinced that that was a good idea. However, so far > as vacuumlo is concerned, the only reason this is a problem is that > vacuumlo goes out of its way to do all the large-object deletions in a > single transaction. What's the point of that? It'd be useful to batch > them, probably, rather than commit each deletion individually. But the > objects being deleted are by assumption unreferenced, so I see no > correctness argument why they should need to go away all at once. I think you are asking for this option: -l LIMIT stop after removing LIMIT large objects which was added in b69f2e36402aaa. Josh -- 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] Regarding column reordering project for GSoc 2012
On Tue, Mar 20, 2012 at 6:58 AM, Atri Sharma wrote: > -Original Message- > From: Merlin Moncure [mailto:mmonc...@gmail.com] > Sent: 20 March 2012 03:15 > To: Heikki Linnakangas > Cc: Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave Page; > Pg Hackers > Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 > > On Mon, Mar 19, 2012 at 2:49 PM, Heikki Linnakangas > wrote: >> On 19.03.2012 21:29, Alvaro Herrera wrote: >>> >>> >>> Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012: >>> I was just going through PGfoundry and I think I will be able to work on the PL/Java project.Please let me know If I can proceed further on ot for GSOC and also,if currently there is any work that needs to be done on PL/java. >>> >>> >>> Hm, I'm not sure on PL/Java. As far as I know, it is a dead project. >> >> >> I haven't followed PL/java either, but it doesn't look quite dead to me. > The >> last release was in September 2011, and there are active discussions on > the >> mailing lists. If it's not completely up-to-date with latest JDK and >> PostgreSQL releases, perhaps bringing it up-to-date and then adding some >> missing functionality would be a useful GSoC project. >> >> I would suggest that you ask on the pl/java mailing list if there is >> something suitable for a GSoC project there, and if one of the pl/java >> developers would be willing to mentor. > > pl/java works pretty well and is somewhat widely used although it > might need some more active maintainers. just jumping into the > project and nailing some old bugs and getting the juices flowing would > be a tremendous help, as well as the less glamorous work of > documentation and regular status updates. > > one pl/java based project that IMO is just screaming to be done is a > pl/java based FDW (wrapping JDBC) that would approximately reproduce > dblink...maybe with some extra features geared towards ETL type jobs > like a row based callback for transformations in java. > > Merlin > > > --- > Hi Merlin > > Thanks for the idea. > > Could you please elaborate more on the project idea? It would be a great > help for me. sure: first start here: http://wiki.postgresql.org/wiki/Foreign_data_wrappers and take a look at all the various fdw projects. they all utilize the high level interfaces that postgresql provides to incorporate external datasources and expose them you can query them as if they were views or tables. as you can see, this interface is popular with the proliferation of projects to expose this or that. what i'm thinking is you make a fdw that invokes pl/java routines that make jdbc calls out to external databases. for fetching data and building sets. as long as you stick to vanilla jdbc calls, you then have a fdw that can gather data from pretty much anything you can access via jdbc url which is quite nice. The fdw API is a C api, so you need to figure out how to proxy to your pl/java calls where the real work is done -- I haven't done that so I'm not sure if this is even technically feasible. If you can make SPI calls from your fdw routines, that's one path through. Tou have lots of previous work to crib from though and hopefully there's a path through. As far as the ETL stuff I was talking about -- that was just me thinking out loud...better to just get the basic mechanics working up front.. For now, if you're interested in doing this, start thinking about how to marry the fdw interface to pl/java...one you get 'hello world' there, it's all about where you want tot take it. This is an interesting project -- you should entertain no illusions of it making it into core, but if done right and completed it will raise visibility of both pl/java and postgresql. merlin -- 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] vacuumlo issue
MUHAMMAD ASIF wrote: > We have noticed the following issue with vacuumlo database that have millions > of record > in pg_largeobject i.e. [...] > ~/work/pg/postgresql-9.1.2/inst$ bin/vacuumlo vacuumlo_test > > WARNING: out of shared memory > Failed to remove lo 36726: ERROR: out of shared memory > > HINT: You might need to increase max_locks_per_transaction. This is not a question for the hackers list. vacuumlo handles all deletes in a single transaction, so max_locks_per_transaction can be exhausted. Yours, Laurenz Albe -- 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] vacuumlo issue
MUHAMMAD ASIF writes: > We have noticed the following issue with vacuumlo database that have millions > of record in pg_largeobject i.e. >WARNING: out of shared memoryFailed to remove lo 155987:ERROR: out > of shared memory HINT: You might need to increase > max_locks_per_transaction. > Why do we need to increase max_locks_per_transaction/shared memory for > clean up operation, This seems to be a consequence of the 9.0-era decision to fold large objects into the standard dependency-deletion algorithm and hence take out locks on them individually. I'm not entirely convinced that that was a good idea. However, so far as vacuumlo is concerned, the only reason this is a problem is that vacuumlo goes out of its way to do all the large-object deletions in a single transaction. What's the point of that? It'd be useful to batch them, probably, rather than commit each deletion individually. But the objects being deleted are by assumption unreferenced, so I see no correctness argument why they should need to go away all at once. 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] Error trying to compile a simple C trigger
Excerpts from Marco Nenciarini's message of mar mar 20 07:53:06 -0300 2012: > I was trying to compile orafce on the current master and it yield > an error at line > > tupdesc = trigdata->tg_relation->rd_att; > > alert.c: In function ‘dbms_alert_defered_signal’: > alert.c:839:33: error: dereferencing pointer to incomplete type > make: *** [alert.o] Error 1 > > I've also tried the example at > > http://www.postgresql.org/docs/devel/static/trigger-example.html > > and the result is exactly the same. > > trigtest.c: In function ‘trigf’: > trigtest.c:44:36: error: dereferencing pointer to incomplete type > make: *** [trigtest.o] Error 1 Thanks ... it seems we need these fixes (each of them backpatched to a different set of releases). -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support trigger-fix.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] CREATE DOMAIN json vs built-in json
Hitoshi Harada writes: > I've noticed our plv8 regression test now fails. It has CREATE DOMAIN > json AS text ... and validates text via v8's JSON.parse(), which was > working before introducing built-in json type. The test itself can be > solved simply by creating schema, but my surprise is that we allow a > domain whose name is the same as other base type. Is it intentional? Sure. The built-in type is in the pg_catalog schema, but your domain is (most likely) being created in the public schema. 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] Memory usage during sorting
Greg Stark writes: > Offhand I wonder if this is all because we don't have the O(n) heapify > implemented. Robert muttered something about that before, but is it real? If you could do that, I'd think you'd have a less-than-n-log-n sorting solution. 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] Regarding column reordering project for GSoc 2012
-Original Message- From: Heikki Linnakangas [mailto:heikki.linnakan...@enterprisedb.com] Sent: 20 March 2012 17:39 To: Claes Jakobsson Cc: Merlin Moncure; Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave Page; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On 20.03.2012 11:10, Claes Jakobsson wrote: > On 19 mar 2012, at 22.45, Merlin Moncure wrote: >> one pl/java based project that IMO is just screaming to be done is a >> pl/java based FDW (wrapping JDBC) that would approximately reproduce >> dblink...maybe with some extra features geared towards ETL type jobs >> like a row based callback for transformations in java. > > Personally I'd love a type 2 JDBC driver for PostgreSQL. Why? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com --- Hi Heikki, one pl/java based project that IMO is just screaming to be done is a pl/java based FDW (wrapping JDBC) that would approximately reproduce dblink...maybe with some extra features geared towards ETL type jobs like a row based callback for transformations in java. The project will be wrapping JDBC in a wrapper class and adding ETL features? Please elaborate a bit more.I am not too sure if I understand it completely. Waiting for your reply, Atri
Re: [HACKERS] Regarding column reordering project for GSoc 2012
On 20.03.2012 11:10, Claes Jakobsson wrote: On 19 mar 2012, at 22.45, Merlin Moncure wrote: one pl/java based project that IMO is just screaming to be done is a pl/java based FDW (wrapping JDBC) that would approximately reproduce dblink...maybe with some extra features geared towards ETL type jobs like a row based callback for transformations in java. Personally I'd love a type 2 JDBC driver for PostgreSQL. Why? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Regarding column reordering project for GSoc 2012
-Original Message- From: Merlin Moncure [mailto:mmonc...@gmail.com] Sent: 20 March 2012 03:15 To: Heikki Linnakangas Cc: Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave Page; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On Mon, Mar 19, 2012 at 2:49 PM, Heikki Linnakangas wrote: > On 19.03.2012 21:29, Alvaro Herrera wrote: >> >> >> Excerpts from Atri Sharma's message of lun mar 19 16:20:09 -0300 2012: >> >>> I was just going through PGfoundry and I think I will be able to work >>> on the PL/Java project.Please let me know If I can proceed further on >>> ot for GSOC and also,if currently there is any work that needs to be >>> done on PL/java. >> >> >> Hm, I'm not sure on PL/Java. As far as I know, it is a dead project. > > > I haven't followed PL/java either, but it doesn't look quite dead to me. The > last release was in September 2011, and there are active discussions on the > mailing lists. If it's not completely up-to-date with latest JDK and > PostgreSQL releases, perhaps bringing it up-to-date and then adding some > missing functionality would be a useful GSoC project. > > I would suggest that you ask on the pl/java mailing list if there is > something suitable for a GSoC project there, and if one of the pl/java > developers would be willing to mentor. pl/java works pretty well and is somewhat widely used although it might need some more active maintainers. just jumping into the project and nailing some old bugs and getting the juices flowing would be a tremendous help, as well as the less glamorous work of documentation and regular status updates. one pl/java based project that IMO is just screaming to be done is a pl/java based FDW (wrapping JDBC) that would approximately reproduce dblink...maybe with some extra features geared towards ETL type jobs like a row based callback for transformations in java. Merlin --- Hi Merlin Thanks for the idea. Could you please elaborate more on the project idea? It would be a great help for me. Atri -- 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] Regarding column reordering project for GSoc 2012
-Original Message- From: Claes Jakobsson [mailto:cl...@versed.se] Sent: 20 March 2012 14:40 To: Merlin Moncure Cc: Heikki Linnakangas; Alvaro Herrera; Atri Sharma; Daniel Farina; Andrew Dunstan; Dave Page; Pg Hackers Subject: Re: [HACKERS] Regarding column reordering project for GSoc 2012 On 19 mar 2012, at 22.45, Merlin Moncure wrote: > one pl/java based project that IMO is just screaming to be done is a > pl/java based FDW (wrapping JDBC) that would approximately reproduce > dblink...maybe with some extra features geared towards ETL type jobs > like a row based callback for transformations in java. Personally I'd love a type 2 JDBC driver for PostgreSQL. It should be feasible as a summer project. It's somewhere deep down on my TODO list so I'd be happy to help. Cheers, Claes -- Hi Claes, Thank you for taking time to reply to my query. I would simply love to implement a type 2 JDBC driver.It would be simply wonderful to add this feature to PL/Java (IMHO). Please let me know how to proceed further.Also,I would request you to please mentor me in this pet project of yours. Thanks a lot for giving me this project idea. Waiting for your reply, Atri -- 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] Error trying to compile a simple C trigger
Il giorno mar, 20/03/2012 alle 16.46 +0500, Asif Naeem ha scritto: > It seems that compiler is complain about "Relation" structure, can you > please try adding the following in trigtest.c i.e. > > #include "utils/rel.h" > It does the trick. Regards, Marco -- Marco Nenciarini - 2ndQuadrant Italy PostgreSQL Training, Services and Support marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it -- 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] Error trying to compile a simple C trigger
It seems that compiler is complain about "Relation" structure, can you please try adding the following in trigtest.c i.e. #include "utils/rel.h" Best Regards, Asif Naeem On Tue, Mar 20, 2012 at 3:53 PM, Marco Nenciarini < marco.nenciar...@2ndquadrant.it> wrote: > I was trying to compile orafce on the current master and it yield > an error at line > > tupdesc = trigdata->tg_relation->rd_att; > > alert.c: In function ‘dbms_alert_defered_signal’: > alert.c:839:33: error: dereferencing pointer to incomplete type > make: *** [alert.o] Error 1 > > I've also tried the example at > > http://www.postgresql.org/docs/devel/static/trigger-example.html > > and the result is exactly the same. > > trigtest.c: In function ‘trigf’: > trigtest.c:44:36: error: dereferencing pointer to incomplete type > make: *** [trigtest.o] Error 1 > > Regards, > Marco > > -- > Marco Nenciarini - 2ndQuadrant Italy > PostgreSQL Training, Services and Support > marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it > > > > -- > 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] Memory usage during sorting
On Tue, Mar 20, 2012 at 1:57 AM, Tom Lane wrote: > That was a long time ago, of course, but I have some vague recollection > that keeping next-run tuples in the current heap achieves a net savings > in the total number of comparisons needed to heapify both runs. Offhand I wonder if this is all because we don't have the O(n) heapify implemented. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error trying to compile a simple C trigger
Il giorno mar, 20/03/2012 alle 11.16 +, Peter Geoghegan ha scritto: > On 20 March 2012 10:53, Marco Nenciarini > wrote: > > alert.c: In function ‘dbms_alert_defered_signal’: > > alert.c:839:33: error: dereferencing pointer to incomplete type > > make: *** [alert.o] Error 1 > > > > I've also tried the example at > > > > http://www.postgresql.org/docs/devel/static/trigger-example.html > > > > and the result is exactly the same. > > > > trigtest.c: In function ‘trigf’: > > trigtest.c:44:36: error: dereferencing pointer to incomplete type > > make: *** [trigtest.o] Error 1 > > I'd say this is an unintended consequence of a pgrminclude run. Try adding > this: > > #include "access/tupdesc.h" It doesn't work. The error is stil the same. Regards, Marco -- Marco Nenciarini - 2ndQuadrant Italy PostgreSQL Training, Services and Support marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it -- 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] Error trying to compile a simple C trigger
On 20 March 2012 10:53, Marco Nenciarini wrote: > alert.c: In function ‘dbms_alert_defered_signal’: > alert.c:839:33: error: dereferencing pointer to incomplete type > make: *** [alert.o] Error 1 > > I've also tried the example at > > http://www.postgresql.org/docs/devel/static/trigger-example.html > > and the result is exactly the same. > > trigtest.c: In function ‘trigf’: > trigtest.c:44:36: error: dereferencing pointer to incomplete type > make: *** [trigtest.o] Error 1 I'd say this is an unintended consequence of a pgrminclude run. Try adding this: #include "access/tupdesc.h" -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and 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] Error trying to compile a simple C trigger
I was trying to compile orafce on the current master and it yield an error at line tupdesc = trigdata->tg_relation->rd_att; alert.c: In function ‘dbms_alert_defered_signal’: alert.c:839:33: error: dereferencing pointer to incomplete type make: *** [alert.o] Error 1 I've also tried the example at http://www.postgresql.org/docs/devel/static/trigger-example.html and the result is exactly the same. trigtest.c: In function ‘trigf’: trigtest.c:44:36: error: dereferencing pointer to incomplete type make: *** [trigtest.o] Error 1 Regards, Marco -- Marco Nenciarini - 2ndQuadrant Italy PostgreSQL Training, Services and Support marco.nenciar...@2ndquadrant.it | www.2ndQuadrant.it -- 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] vacuumlo issue
I have reformatted the mail, sorry for inconvenience. Thanks. We have noticed the following issue with vacuumlo database that have millions of record in pg_largeobject i.e. WARNING: out of shared memory Failed to remove lo 155987:ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. Why do we need to increase max_locks_per_transaction/shared memory for clean up operation, if there are huge number records how can we tackle this situation with limited memory?. It is reproducible on postgresql-9.1.2. The steps are as following (PFA vacuumlo-test_data.sql that generates dummy data) i.e. Steps: 1. ./bin/initdb -D data-vacuumlo_test12. ./bin/pg_ctl -D data-vacuumlo_test1 -l logfile_data-vacuumlo_test1 start3. ./bin/createdb vacuumlo_test4. bin/psql -d vacuumlo_test -f vacuumlo-test_data.sql5. bin/vacuumlo vacuumlo_test ~/work/pg/postgresql-9.1.2/inst$ bin/psql -d vacuumlo_test -f vacuumlo-test_data.sqlCREATE FUNCTIONCREATE FUNCTION create_manylargeobjects- (1 row) count--- 13001(1 row) ~/work/pg/postgresql-9.1.2/inst$ bin/vacuumlo vacuumlo_test WARNING: out of shared memoryFailed to remove lo 36726: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction.Failed to remove lo 36727: ERROR: current transaction is aborted, commands ignored until end of transaction blockFailed to remove lo 36728: ERROR: current transaction is aborted, commands ignored until end of transaction blockFailed to remove lo 36729: ERROR: current transaction is aborted, commands ignored until end of transaction block Best Regards,Asif Naeem -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] vacuumlo issue
Hi, We have noticed the following issue with vacuumlo database that have millions of record in pg_largeobject i.e. WARNING: out of shared memoryFailed to remove lo 155987: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. Why do we need to increase max_locks_per_transaction/shared memory for clean up operation, if there are huge number records how can we tackle this situation with limited memory?. It is reproducible on postgresql-9.1.2. The steps are as following (PFA vacuumlo-test_data.sql that generates dummy data) i.e. Steps: 1. ./bin/initdb -D data-vacuumlo_test12. ./bin/pg_ctl -D data-vacuumlo_test1 -l logfile_data-vacuumlo_test1 start3. ./bin/createdb vacuumlo_test4. bin/psql -d vacuumlo_test -f vacuumlo-test_data.sql5. bin/vacuumlo vacuumlo_test ~/work/pg/postgresql-9.1.2/inst$ bin/psql -d vacuumlo_test -f vacuumlo-test_data.sql CREATE FUNCTION CREATE FUNCTION create_manylargeobjects - (1 row) count --- 13001 (1 row) ~/work/pg/postgresql-9.1.2/inst$ bin/vacuumlo vacuumlo_test WARNING: out of shared memory Failed to remove lo 36726: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction. Failed to remove lo 36727: ERROR: current transaction is aborted, commands ignored until end of transaction block Failed to remove lo 36728: ERROR: current transaction is aborted, commands ignored until end of transaction block Failed to remove lo 36729: ERROR: current transaction is aborted, commands ignored until end of transaction block Best Regards,Muhammad Asif Naeem vacuumlo-test_data.sql 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] CREATE DOMAIN json vs built-in json
On Tue, Mar 20, 2012 at 2:47 AM, Abel Abraham Camarillo Ojeda wrote: > On Tue, Mar 20, 2012 at 2:44 AM, Hitoshi Harada wrote: >> I've noticed our plv8 regression test now fails. It has CREATE DOMAIN >> json AS text ... and validates text via v8's JSON.parse(), which was >> working before introducing built-in json type. The test itself can be >> solved simply by creating schema, but my surprise is that we allow a >> domain whose name is the same as other base type. Is it intentional? >> >> Thanks, >> -- >> Hitoshi Harada >> > > Mmm... > > _n_srv=# create domain text as text; > CREATE DOMAIN > _n_srv=# > _n_srv=# create domain int as text; > CREATE DOMAIN > _n_srv=# > > Mmm... $ psql -U postgres _n_srv psql (9.1.2) Type "help" for help. _n_srv=# -- 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] CREATE DOMAIN json vs built-in json
On Tue, Mar 20, 2012 at 2:44 AM, Hitoshi Harada wrote: > I've noticed our plv8 regression test now fails. It has CREATE DOMAIN > json AS text ... and validates text via v8's JSON.parse(), which was > working before introducing built-in json type. The test itself can be > solved simply by creating schema, but my surprise is that we allow a > domain whose name is the same as other base type. Is it intentional? > > Thanks, > -- > Hitoshi Harada > Mmm... _n_srv=# create domain text as text; CREATE DOMAIN _n_srv=# _n_srv=# create domain int as text; CREATE DOMAIN _n_srv=# Mmm... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CREATE DOMAIN json vs built-in json
I've noticed our plv8 regression test now fails. It has CREATE DOMAIN json AS text ... and validates text via v8's JSON.parse(), which was working before introducing built-in json type. The test itself can be solved simply by creating schema, but my surprise is that we allow a domain whose name is the same as other base type. Is it intentional? Thanks, -- Hitoshi Harada -- 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] Cross-backend signals and administration (Was: Re: pg_terminate_backend for same-role)
On Mon, Mar 19, 2012 at 9:08 PM, Robert Haas wrote: > It's after midnight here so maybe I'm being slow, but I don't > understand what problem the SessionId solves. ISTM that you could > solve the problem like this: > > 1. Acquire ProcArrayLock in exclusive mode, to keep the set of PGPROCs > from changing. > 2. Search for the target PGPROC by PID; when you find it, set a bit in > the PGPROC indicating that you want it to cancel/die/whatever. > 3. Remember the PID. > 4. Release ProcArrayLock. > 5. Send SIGUSR1. I chose the SessionId mostly because I didn't have a great sense around how hot the ProcArrayLock is, and it was easy to add a fine-grained spinlock to just get the flavor of the idea. To attempt to simplify your protocol more: is it necessary or sound to remember the PID at all if one takes a lock on the entire PGPROC array? At that point backend birth and death contends against that lock, so the postmaster just has to initialize PGPROC the usual way (writing over the administrative action words) and then...I don't see a problem, on first blush. But I'll see your midnight and raise you a 1:35 AM. I do think a session identifier will be useful someday in Postgres's future, but it only really is worth it if it is non-guessable, to which my implementation need not apply. Also, I had a use case that would be roughly right for "cross-backend administration" I want to temporarily suppress autovacuum/autoanalyze without having to muck with thrashing configuration files, if possible. -- 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] pg_terminate_backend for same-role
On Thu, Mar 15, 2012 at 9:39 PM, Fujii Masao wrote:
> On Fri, Mar 16, 2012 at 8:14 AM, Daniel Farina wrote:
>> Parallel to pg_cancel_backend, it'd be nice to allow the user to just
>> outright kill a backend that they own (politely, with a SIGTERM),
>> aborting any transactions in progress, including the idle transaction,
>> and closing the socket.
>
> +1
Here's a patch implementing the simple version, with no more guards
against signal racing than have been seen previously. The more
elaborate variants to close those races is being discussed in a
parallel thread, but I thought I'd get this simple version out there.
--
fdr
From 73c794a0cce148c2848adfb06be9aac985ac41d8 Mon Sep 17 00:00:00 2001
From: Daniel Farina
Date: Sun, 18 Mar 2012 20:08:37 -0700
Subject: [PATCH] Extend same-role backend management to pg_terminate_backend
This makes it more similar to pg_cancel_backend, except it gives users
the ability to close runaway connections entirely.
Signed-off-by: Daniel Farina
---
doc/src/sgml/func.sgml |6 +-
src/backend/utils/adt/misc.c | 12 +++-
2 files changed, 12 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 34fea16..7cece3a 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 14403,14409 SELECT set_config('log_statement_stats', 'off', false);
pg_terminate_backend(pid int)
boolean
!Terminate a backend
--- 14403,14413
pg_terminate_backend(pid int)
boolean
!Terminate a backend. You can execute this against
! another backend that has exactly the same role as the user
! calling the function. In all other cases, you must be a
! superuser.
!
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***
*** 162,179 pg_cancel_backend(PG_FUNCTION_ARGS)
}
/*
! * Signal to terminate a backend process. Only allowed by superuser.
*/
Datum
pg_terminate_backend(PG_FUNCTION_ARGS)
{
! if (!superuser())
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! errmsg("must be superuser to terminate other server processes"),
! errhint("You can cancel your own processes with pg_cancel_backend().")));
! PG_RETURN_BOOL(pg_signal_backend(PG_GETARG_INT32(0), SIGTERM) == SIGNAL_BACKEND_SUCCESS);
}
/*
--- 162,181
}
/*
! * Signal to terminate a backend process. This is allowed if you are superuser
! * or have the same role as the process being terminated.
*/
Datum
pg_terminate_backend(PG_FUNCTION_ARGS)
{
! int r = pg_signal_backend(PG_GETARG_INT32(0), SIGTERM);
!
! if (r == SIGNAL_BACKEND_NOPERMISSION)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
! (errmsg("must be superuser or have the same role to terminate backends running in other server processes";
! PG_RETURN_BOOL(r == SIGNAL_BACKEND_SUCCESS);
}
/*
--
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 8.4 planner question - bad plan, good plan for almost same queries.
Running PostgreSQL 8.4.8 on x86_64-pc-linux-gnu, compiled by GCC
gcc-4.6.real (Ubuntu/Linaro 4.6.0-7ubuntu1) 4.6.1, 64-bit
under Ubuntu 11.10.
Got large table with geography (PostGIS 1.5) data, running two almost
same queries with same result, but very different performance.
First query:
with uuu as (
select dml.id
from mp_locs12 dml
where (complex conditions, leaving about 100 rows from millions)
)
select label, country, region, parish, city, district,
st_geometrytype(loc::geometry) as gtype,
'0x' || to_hex(type) as n_type, file_name, line
from mp_locs12 dml1
where dml1.id in (select uu.id from uuu uu)
and not exists (
select 1 from mp_locs12 dml2
where dml2.id in (select uu.id from uuu uu)
and dml2.id <> dml1.id
and not st_contains(dml1.loc::geometry, dml2.loc::geometry)
);
Planner choose to seqscan dml2 table in NOT EXISTS condition, very
surprising for me - "dml2.id in (select uu.id from uuu uu)" is a best
cut off here I believe, but planner thinks different (for dml1 it does not).
QUERY PLAN
--
Nested Loop Anti Join (cost=2451523.67..8486747.31 rows=1 width=2130)
Join Filter: ((dml2.id <> dml1.id) AND ((NOT ((dml1.loc)::geometry
&& (dml2.loc)::geometry)) OR (NOT _st_contains((dml1.loc)::geometry,
(dml2.loc)::geometry
CTE uuu
-> Seq Scan on mp_locs12 dml (cost=0.00..2451523.62 rows=1 width=4)
Filter: (complex conditions, leaving about 100 rows from
millions)
-> Nested Loop (cost=0.02..8.85 rows=1 width=2134)
-> HashAggregate (cost=0.02..0.03 rows=1 width=4)
-> CTE Scan on uuu uu (cost=0.00..0.02 rows=1 width=4)
-> Index Scan using mp_locs12_pkey on mp_locs12 dml1
(cost=0.00..8.81 rows=1 width=2134)
Index Cond: (dml1.id = uu.id)
-> Seq Scan on mp_locs12 dml2 (cost=0.02..1750366.67 rows=15581266
width=1946)
Filter: (hashed SubPlan 2)
SubPlan 2
-> CTE Scan on uuu uu (cost=0.00..0.02 rows=1 width=4)
(14 rows)
Now try to use bit modified query:
explain with uuu as (
select dml.id
from mp_locs12 dml
where (complex conditions, leaving about 100 rows from millions)
)
select label, country, region, parish, city, district,
st_geometrytype(loc::geometry) as gtype,
'0x' || to_hex(type) as n_type, file_name, line
from mp_locs12 dml1
where dml1.id in (select uu.id from uuu uu)
and lower(st_geometrytype(dml1.loc::geometry)) not in
('st_geometrycollection')
and not exists (
select 1 from (
select dml2.id as id from mp_locs12 dml2
where dml2.id in (select uu.id from uuu uu)
and not st_contains(dml1.loc::geometry,
dml2.loc::geometry)
and lower(st_geometrytype(dml2.loc::geometry)) not in
('st_geometrycollection')
) vv
where vv.id <> dml1.id
);
Only thing I changed - scanned CTE for ids to get, then compared taken
ids with dml1.id. And now planner chose best plan (I think so):
QUERY PLAN
---
Nested Loop (cost=2139898.32..2139916.30 rows=1 width=2130)
CTE uuu
-> Seq Scan on mp_locs12 dml (cost=0.00..2139898.30 rows=1 width=4)
Filter: (complex conditions, leaving about 100 rows from
millions)
-> HashAggregate (cost=0.02..0.03 rows=1 width=4)
-> CTE Scan on uuu uu (cost=0.00..0.02 rows=1 width=4)
-> Index Scan using mp_locs12_pkey on mp_locs12 dml1
(cost=0.00..17.95 rows=1 width=2134)
Index Cond: (dml1.id = uu.id)
Filter: ((lower(st_geometrytype((dml1.loc)::geometry)) <>
'st_geometrycollection'::text) AND (NOT (SubPlan 2)))
SubPlan 2
-> Nested Loop (cost=0.02..9.13 rows=1 width=0)
-> HashAggregate (cost=0.02..0.03 rows=1 width=4)
-> CTE Scan on uuu uu (cost=0.00..0.02 rows=1
width=4)
-> Index Scan using mp_locs12_pkey on mp_locs12 dml2
(cost=0.00..9.08 rows=1 width=4)
Index Cond: (dml2.id = uu.id)
Filter: ((dml2.id <> $2) AND
(lower(st_geometrytype((dml2.loc)::geometry)) <>
'st_geometrycollection'::text) AND ((NOT (($1)::geometry &&
(dml2.loc)::geometry)) OR (NOT _st_contains(($1)::geome
