Re: [HACKERS] pg_comments (was: Allow \dd to show constraint comments)
On 9 November 2011 02:42, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 8, 2011 at 8:12 PM, Thom Brown t...@linux.com wrote: Why is this marked as done on with Wiki's todo list? I assume you're referring to this item: -- \dd is missing comments for several types of objects. Comments are not handled at all for some object types, and are handled by both \dd and the individual backslash command for others. Consider a system view like pg_comments to manage this mess. -- What we did is modify psql so that all the backslash commands display comments for their corresponding object types, at least when the + modifier is used. We then made \dd display comments for all object types that lack their own backslash commands. That seems like it pretty much covers the todo item. I initially thought that pg_comments could be used *by psql*, which seemed to me a good argument in favor of its general utility. When we didn't end up doing it that way, the view got less appealing to me. I'm still willing to add it if enough people say they want it, but so far I think we've gotten about three votes between +0.25 and +0.50 and a -1 from Tom. Which is not really enough for me to feel like pulling the trigger. Okay, I've skim-read this topic too lightly. I understand now. Can I ask, for which type of object are there comments for which we still can't easily find without this proposed view? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: 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
[HACKERS] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
Hi, Consider the following sequence of events: s1 # CREATE SCHEMA test_schema; s1 # CREATE TABLE test_schema.c1(x int); Now open another session s2 and via gdb issue a breakpoint on heap_create_with_catalog() which is called by DefineRelation(). s2 # CREATE TABLE test_schema.c2(y int); The above will break on the function. Now issue a drop schema in session s1 s1 # DROP SCHEMA test_schema CASCADE; NOTICE: drop cascades to table test_schema.c1 DROP SCHEMA Continuing in gdb, also completes the creation of c2 table without any errors. We are now left with a dangling entry in pg_class along with all the corresponding data files in our data directory. The problem becomes worse if c2 was created using a TABLESPACE. Now dropping of that tablespace does not work at all. Am sure we can come up with myriad such other issues. Am sure other CREATE commands in this namespace will have similar issues when faced with a concurrent DROP SCHEMA. We definitely need some interlocking to handle this. For lack of better APIs, we could do a LockDatabaseObject() call in AccessShareLock mode on the namespace and release the same on completion of the creation of the object. Thoughts? Regards, Nikhils
Re: [HACKERS] Disable OpenSSL compression
Christopher Browne wrote: I think that JDBC and Npgsql should also support disabling compression. That's the *real* problem here... You're quite right that if we allow controlling this on the libpq side, it is surely desirable to allow controlling this via JDBC, Npgsql, and other mechanisms people may have around. [...] With that series of complications, I wonder if maybe the right place to control this is pg_hba.conf. I think that wouldn't work, because to query pg_hba.conf, you have to know user and database, which come from the client side. But the SSL negotiation takes place earlier, namely when the connection is established. I wonder how many SSL parameters there are which would be worth trying to have available. I expect we'd benefit from looking at all the relevant ones at once, so as to not have the problem of hacking one more into place and perhaps doing it a bit differently each time. Sure, if anybody can think of any. A quick look at man SSL_CTX_set_options didn't show me any, but then OpenSSL's documentation is very bad (the page does not even mention SSL_OP_NO_COMPRESSION) and I am no SSL expert. Is the following proposal acceptable: - Add a GUC ssl_compression, defaulting to on. - Add a client option sslcompression and an environment variable PGSSLCOMPRESSION, defaulting to 1. Compression will be disabled if either side refuses. That way you can control the setting per client, but you can also force it on clients that do not use libpq if you want. 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] pg_comments (was: Allow \dd to show constraint comments)
On Wed, Nov 9, 2011 at 3:38 AM, Thom Brown t...@linux.com wrote: On 9 November 2011 02:42, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 8, 2011 at 8:12 PM, Thom Brown t...@linux.com wrote: Why is this marked as done on with Wiki's todo list? I assume you're referring to this item: -- \dd is missing comments for several types of objects. Comments are not handled at all for some object types, and are handled by both \dd and the individual backslash command for others. Consider a system view like pg_comments to manage this mess. -- What we did is modify psql so that all the backslash commands display comments for their corresponding object types, at least when the + modifier is used. We then made \dd display comments for all object types that lack their own backslash commands. That seems like it pretty much covers the todo item. I initially thought that pg_comments could be used *by psql*, which seemed to me a good argument in favor of its general utility. When we didn't end up doing it that way, the view got less appealing to me. I'm still willing to add it if enough people say they want it, but so far I think we've gotten about three votes between +0.25 and +0.50 and a -1 from Tom. Which is not really enough for me to feel like pulling the trigger. Okay, I've skim-read this topic too lightly. I understand now. Can I ask, for which type of object are there comments for which we still can't easily find without this proposed view? Well, the problem with our backslash commands in general is that they work fine until they don't. For example, if you want to find a function by name, \df is completely adequate. You can just type \df foo* and away you go. But if you want to find a function that *returns type foo*, it's useless. You've got to manually write a query, and the level of effort is far higher than just typing a backslash commands. Or if you want to add columns to or remove columns from the output, you are out of luck. psql -E is a help, but it's still kind of a pain. Now, the problem is even worse than average when it comes to comments, because extracting the comments typically involves a hairy left join from whichever table contains the objects over to pg_description and/or pg_shdescription, and the meanings of the columns in those tables are not at all evident to the newcomer, who will probably therefore screw it up. It seemed to me that, for those sorts of situations, something like pg_comments might be useful. I think the way that psql handles comments now is a big improvement over previous releases, and I'm satisfied with it. But there are certainly situations - especially, when you have a large number of objects and want to filter them in some slightly unusual way - where it - or our backslash commands more generally - might be judged lacking. -- 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
[HACKERS] [Feature Request] \dx show options
Hi guys, I was thinking that could be great to add a column in \dx showing the available options for each foreign data wrapper. Seems reasonable? -- -- Emanuel Calvo Helpame.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Measuring relation free space
Excerpts from Jaime Casanova's message of mar nov 08 18:12:25 -0300 2011: On Sun, Nov 6, 2011 at 5:38 AM, Magnus Hagander mag...@hagander.net wrote: Looks pretty useful. thanks for the review, attached is a new version of it Note that AFAIK you shouldn't update the 1.0 extension script ... you have to create a 1.1 version (or whatever), update the default version in the control file, and create an 1.0--1.1 script to upgrade from the original version to 1.1. -- Álvaro Herrera alvhe...@commandprompt.com 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] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
On Wed, Nov 9, 2011 at 4:56 AM, Nikhil Sontakke nikkh...@gmail.com wrote: Consider the following sequence of events: s1 # CREATE SCHEMA test_schema; s1 # CREATE TABLE test_schema.c1(x int); Now open another session s2 and via gdb issue a breakpoint on heap_create_with_catalog() which is called by DefineRelation(). s2 # CREATE TABLE test_schema.c2(y int); The above will break on the function. Now issue a drop schema in session s1 s1 # DROP SCHEMA test_schema CASCADE; NOTICE: drop cascades to table test_schema.c1 DROP SCHEMA Continuing in gdb, also completes the creation of c2 table without any errors. We are now left with a dangling entry in pg_class along with all the corresponding data files in our data directory. The problem becomes worse if c2 was created using a TABLESPACE. Now dropping of that tablespace does not work at all. Am sure we can come up with myriad such other issues. Am sure other CREATE commands in this namespace will have similar issues when faced with a concurrent DROP SCHEMA. We definitely need some interlocking to handle this. For lack of better APIs, we could do a LockDatabaseObject() call in AccessShareLock mode on the namespace and release the same on completion of the creation of the object. Thoughts? In general, we've been reluctant to add locking on non-table objects for reasons of overhead. You can, for example, drop a type or function while a query is running that depends on it (which is not true for tables). But I think it is sensible to do it for DDL commands, which shouldn't be frequent enough for the overhead to matter much. When I rewrote the comment code for 9.1, I added locking that works just this way, to prevent pg_description entries from being orphaned; see the end of get_object_address(). -- 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
[HACKERS] a modest improvement to get_object_address()
I'd like to propose the attached patch, which changes get_object_address() in a manner similar to what we did in RangeVarGetRelid() in commit 4240e429d0c2d889d0cda23c618f94e12c13ade7. The basic idea is that, if we look up an object name, acquire the corresponding lock, and then find that the object was dropped during the lock wait, we retry the whole operation instead of emitting a baffling error message. Example: rhaas=# create schema x; CREATE SCHEMA rhaas=# begin; BEGIN rhaas=# drop schema x; DROP SCHEMA Then, in another session: rhaas=# comment on schema x is 'doodle'; Then, in the first session: rhaas=# commit; COMMIT At this point, the first session must error out. The current code produces this: ERROR: cache lookup failed for class 2615 object 16386 subobj 0 With the attached patch, you instead get: ERROR: schema x does not exist ...which is obviously quite a bit nicer. Also, if the concurrent transaction drops and creates the schema instead of just dropping it, the new code will allow the operation to succeed (with the expected results) rather than failing. Objections? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company objectaddress-retry.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] Syntax for partitioning
On 15 December 2009 02:31, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Dec 14, 2009 at 7:29 PM, Simon Riggs si...@2ndquadrant.com wrote: On Fri, 2009-12-04 at 09:00 +, Simon Riggs wrote: On Fri, 2009-12-04 at 11:54 +0900, Itagaki Takahiro wrote: Here is an update partitioning syntax patch. A bug reported by Marko is fixed. I will review and eventually commit this, if appropriate, though it is 3rd in my queue and will probably not be done for at least 2 weeks, possibly 4 weeks. I'll have to go back on this unfortunately, sorry about that. the next patch for this will arrive in the next commitfest so maybe you have more time then So will this be revived any time soon? Were there any subsequent proposals which were posted? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: In COPY, insert tuples to the heap in batches.
On Wed, Nov 9, 2011 at 9:06 AM, Heikki Linnakangas heikki.linnakan...@iki.fi wrote: In COPY, insert tuples to the heap in batches. This greatly reduces the WAL volume, especially when the table is narrow. The overhead of locking the heap page is also reduced. Reduced WAL traffic also makes it scale a lot better, if you run multiple COPY processes at the same time. Sounds good. I can't see where this applies backup blocks. If it does, can you document why/where/how it differs from other WAL records? There's no need for conflict processing on replay with this new WAL record type. But you should document that and alter the comments that say it is necessary. Search conflict. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] a modest improvement to get_object_address()
2011/11/9 Robert Haas robertmh...@gmail.com: I'd like to propose the attached patch, which changes get_object_address() in a manner similar to what we did in RangeVarGetRelid() in commit 4240e429d0c2d889d0cda23c618f94e12c13ade7. The basic idea is that, if we look up an object name, acquire the corresponding lock, and then find that the object was dropped during the lock wait, we retry the whole operation instead of emitting a baffling error message. Example: rhaas=# create schema x; CREATE SCHEMA rhaas=# begin; BEGIN rhaas=# drop schema x; DROP SCHEMA Then, in another session: rhaas=# comment on schema x is 'doodle'; Then, in the first session: rhaas=# commit; COMMIT At this point, the first session must error out. The current code produces this: ERROR: cache lookup failed for class 2615 object 16386 subobj 0 With the attached patch, you instead get: ERROR: schema x does not exist ...which is obviously quite a bit nicer. Also, if the concurrent transaction drops and creates the schema instead of just dropping it, the new code will allow the operation to succeed (with the expected results) rather than failing. Objections? Maybe I miss something but: The ERROR message is misleading: the schema 'x' does exist. And also why a drop schema would fail and a drop+create would success ?! -- 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 -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- 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] Materialized views
On Tue, Nov 8, 2011 at 9:23 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: That page describes three components: creating MVs, updating MVs, and having the planner automatically detect when an MV matches some portion of a regular query and using the MV instead of the specified tables in such cases. I have high confidence that if time is approved I could do the first two for the 9.3, but that last one seems insanely complicated and not necessarily a good idea. (That's particularly true with some of the lazier strategies for maintaining the data in the materialized view.) I don't think we want to use that 3rd component in our shop, anyway. So the question is, would a patch which does the first two without the third be accepted by the community? For me, yes. I support and encourage your work. It's a big topic and we must approach it incrementally. Having said that, we should assume that #3 will be implemented and that we need to collect appropriate metadata and anything else required. So the design should foresee #3 and not in any way optimise for the case where #3 doesn't happen. It may occur that #3 is added during next cycle concurrently with this development. I would also caution that all other databases currently provide #3 as a matter of course. That is the sauce as far as many people are concerned. Everything else is already achievable using external application code. So I would not want people to start saying we have MVs when in fact all we did was add declarative syntax to support what was already possible - we could easily publicise that incorrectly at release time. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] a modest improvement to get_object_address()
On Wed, Nov 9, 2011 at 8:37 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Maybe I miss something but: The ERROR message is misleading: the schema 'x' does exist. No, it doesn't. The concurrent transaction has dropped it. And also why a drop schema would fail and a drop+create would success ?! Because you can't comment on a schema that doesn't exist any more, but you can comment on one that does. -- 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] Syntax for partitioning
On Wed, Nov 9, 2011 at 8:26 AM, Thom Brown t...@linux.com wrote: So will this be revived any time soon? Were there any subsequent proposals which were posted? there was an updated patch, you can find in this thread: http://archives.postgresql.org/message-id/20100114181323.9a33.52131...@oss.ntt.co.jp not sure what happens after that. -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
We definitely need some interlocking to handle this. For lack of better APIs, we could do a LockDatabaseObject() call in AccessShareLock mode on the namespace and release the same on completion of the creation of the object. Thoughts? In general, we've been reluctant to add locking on non-table objects for reasons of overhead. You can, for example, drop a type or function while a query is running that depends on it (which is not true for tables). But I think it is sensible to do it for DDL commands, which shouldn't be frequent enough for the overhead to matter much. Agreed. Especially if the race condition has non-trivial downsides as mentioned in the tablespace case. When I rewrote the comment code for 9.1, I added locking that works just this way, to prevent pg_description entries from being orphaned; see the end of get_object_address(). Yeah thanks, that does the object locking. For pre-9.1 versions, we will need a similar solution. I encountered the issue on 8.3.x.. Regards, Nikhils
Re: [HACKERS] a modest improvement to get_object_address()
Robert Haas robertmh...@gmail.com writes: I'd like to propose the attached patch, which changes get_object_address() in a manner similar to what we did in RangeVarGetRelid() in commit 4240e429d0c2d889d0cda23c618f94e12c13ade7. I would think you need to drop the now-useless lock, and I sure hope that RangeVarGetRelid does likewise. 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] a modest improvement to get_object_address()
On Wed, Nov 9, 2011 at 9:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I'd like to propose the attached patch, which changes get_object_address() in a manner similar to what we did in RangeVarGetRelid() in commit 4240e429d0c2d889d0cda23c618f94e12c13ade7. I would think you need to drop the now-useless lock, and I sure hope that RangeVarGetRelid does likewise. It doesn't currently. The now-useless lock doesn't really hurt anything, aside from taking up space in the lock table. But we can certainly make it (and this) do that, if you think it's worth the extra lines of code. -- 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] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
On Wed, Nov 9, 2011 at 9:51 AM, Nikhil Sontakke nikkh...@gmail.com wrote: Yeah thanks, that does the object locking. For pre-9.1 versions, we will need a similar solution. I encountered the issue on 8.3.x.. I don't think we should back-patch a fix of this type. There is a lot of cruftiness of this type scattered throughout the code base, and if we start back-patching all the fixes for it, we're going to end up destabilizing older branches for little real benefit. Also, the fix would need to be quite different in older branches. For example, in the master branch, you can probably fix 90% of the issue by adjusting dropcmds.c, which now handles drop operations for most object types. I believe KaiGai Kohei is still working on code which will allow that code to support drop operations for most of the remaining object types as well. But in any previous release you would need scattered fixes all over the code base. -- 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] Syntax for partitioning
On Wed, Nov 9, 2011 at 9:35 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Wed, Nov 9, 2011 at 8:26 AM, Thom Brown t...@linux.com wrote: So will this be revived any time soon? Were there any subsequent proposals which were posted? there was an updated patch, you can find in this thread: http://archives.postgresql.org/message-id/20100114181323.9a33.52131...@oss.ntt.co.jp not sure what happens after that. I reviewed a later version here: http://archives.postgresql.org/pgsql-hackers/2010-07/msg00183.php -- 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] const correctness
Thomas Munro mu...@ip9.org writes: I am a long time user and fan of PostgreSQL and have built various projects large and small on every major release since 6.5. Recently I decided to try doing something more with the source than just compiling it, and spent some time 'constifying' some parts of the code as an exercise (this is an item I saw on the wiki to-do list, and I figured it might provide me with an educational traversal of the source tree). There was some discussion of this just a couple days ago in connection with a patch Peter offered ... did you see that? http://archives.postgresql.org/pgsql-hackers/2011-11/msg00314.php Perhaps there should be a few more 'XXX_const' accessor function variants, for example list_nth_const, This is exactly what was bothering Robert and me about Peter's patch. If you go down this road you soon start needing duplicate functions for no other reason than that one takes/returns const and one doesn't. That might be acceptable on a very small scale, but any serious attempt to const-ify the PG code is going to require it on a very large scale. The maintenance costs of duplicate code are significant (not even considering whether there's a performance hit), and it just doesn't seem likely to be repaid in easier or safer development. Offhand I cannot remember the last bug in PG which would have been prevented by better const-ification. So I'm starting to feel that this idea is a dead end, and we should take it off the TODO list. [1] For functions built on top of expression_tree_walker (like expression_returns_set), it seems that it and therefore they can be modified to work entirely on pointers to const Node without any complaints from GCC at least, but only because the walker function pointer is of type bool (*)() (that is, pointer to a function with unspecified arguments, thereby side-stepping all static type checking). But I guess it would only be honest to change the signature of expression_tree_walker to take const Node * if the type of the walker function pointer were changed to bool (*)(const Node *, void *), and the walker mechanism were declared in the comments not to permit any mutation at all (rather than the weaker restriction that routines can modify nodes in-place but not add/delete/replace nodes). Yeah, that was an alternative I considered when designing the tree walker infrastructure. However, if we force walker functions to be declared just like that, we lose rather than gain type safety. In the typical usage, there's a startup function that sets up a context structure and calls the walker function directly. As things stand, that call is type-checked: you pass the wrong kind of context object, you'll get a bleat. Changing the walkers to use void * would remove that check, while adding a need for explicit casting of the argument inside the walkers, and gain nothing of value. As for the question of whether we should insist that walkers never modify the tree ... yeah, we could, but there are enough instances of nominal walkers that do modify the tree to make me not enthused about it. We would have to change each one of those walkers to instead create a new copy of the tree, with attendant memory consumption and palloc overhead. It would almost certainly be a noticeable performance hit, and the benefit seems debatable at best. There would, I think, be both performance and safety benefits from getting certain entire modules to not scribble on their input trees; especially the planner. But that is a high-level consideration and I'm not sure that const-decoration would really do much to help us achieve 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] Syntax for partitioning
On 9 November 2011 15:15, Robert Haas robertmh...@gmail.com wrote: On Wed, Nov 9, 2011 at 9:35 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Wed, Nov 9, 2011 at 8:26 AM, Thom Brown t...@linux.com wrote: So will this be revived any time soon? Were there any subsequent proposals which were posted? there was an updated patch, you can find in this thread: http://archives.postgresql.org/message-id/20100114181323.9a33.52131...@oss.ntt.co.jp not sure what happens after that. I reviewed a later version here: http://archives.postgresql.org/pgsql-hackers/2010-07/msg00183.php Ah yes, I've located a reference to this on the wiki now. No wiki updates needed. I guess it's a matter of whether Takahiro-san has the time and desire to pick this up again any time soon. Whenever I cross the topic of partitioning in PostgreSQL, it's always a tad embarrassing to explain that it's still hacky compared to other database systems (and this came up again last week), so this is of particular interest to me. At the moment there's no testing required as it's returned with feedback, but I'm very willing to help assist in testing it should this return to the fore again. The idea of getting both this and materialised views in time for 9.3 is extremely appealing; a performance release (9.2) followed by a usability release. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: 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] [Feature Request] \dx show options
2011/11/9 Emanuel Calvo postgres@gmail.com: Hi guys, I was thinking that could be great to add a column in \dx showing the available options for each foreign data wrapper. Seems reasonable? Sorry the spam guys, it necesary the sme for index collations. They appear in \d+ table but with \di+ it doesn't describe or show the collation. Maybe a column or in the description field could be enough. -- -- Emanuel Calvo Helpame.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] a modest improvement to get_object_address()
Robert Haas robertmh...@gmail.com writes: On Wed, Nov 9, 2011 at 9:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: I would think you need to drop the now-useless lock, and I sure hope that RangeVarGetRelid does likewise. It doesn't currently. The now-useless lock doesn't really hurt anything, aside from taking up space in the lock table. Well, there are corner cases where the object OID gets reused during the lifetime of the transaction, and then the lock *does* do something (and what it does would be bad). But taking up extra space in the finite-size lock table is sufficient reason IMO to drop the lock. It's not like these are performance-critical code paths. 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] const correctness
Tom Lane t...@sss.pgh.pa.us wrote: Perhaps there should be a few more 'XXX_const' accessor function variants, for example list_nth_const, This is exactly what was bothering Robert and me about Peter's patch.If you go down this road you soon start needing duplicate functions for no other reason than that one takes/returns const and one doesn't. What about existing functions which are not intended to modify their inputs, don't actually do so, and can be marked to indicate that just by adding const to the current declarations? Aside from any possible value in code optimization by the compiler, I find it helps me understand unfamiliar code more quickly, by making the contract of the API more explicit in the declaration. Perhaps it's worth going after the low-hanging fruit? -Kevin -- 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] const correctness
Peter Geoghegan pe...@2ndquadrant.com writes: On 9 November 2011 15:24, Tom Lane t...@sss.pgh.pa.us wrote:. If you go down this road you soon start needing duplicate functions for no other reason than that one takes/returns const and one doesn't. Why would you have to do that? list_nth is an example. Now admittedly you can hack it, in the same spirit as the C library functions that are declared to take const pointers and return non-const pointers to the very same data; but that hardly satisfies anyone's idea of const cleanliness. In particular it doesn't fix what Peter E. was on about, which was getting rid of cast-away-const warnings, since such a function will have to do that internally. 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] a modest improvement to get_object_address()
2011/11/9 Robert Haas robertmh...@gmail.com: On Wed, Nov 9, 2011 at 8:37 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Maybe I miss something but: I read that the error was produced by first session and didn't check carefuly (it fails silently in 9.0! and 'works' as expected in 9.1) No objection, but I would like to still be able to diagnose the same things as in the past, can you make it clear that the schema/object just disappear ? (else we don't know if the relation just never exists or was drop while we were waiting) -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- 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] const correctness
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: This is exactly what was bothering Robert and me about Peter's patch.If you go down this road you soon start needing duplicate functions for no other reason than that one takes/returns const and one doesn't. What about existing functions which are not intended to modify their inputs, don't actually do so, and can be marked to indicate that just by adding const to the current declarations? Aside from any possible value in code optimization by the compiler, I find it helps me understand unfamiliar code more quickly, by making the contract of the API more explicit in the declaration. Perhaps it's worth going after the low-hanging fruit? I have no objection to const-ifying anything that can be done with one or two localized changes. The difficulty comes in when you try to make core infrastructure like expression_tree_walker do it. (And of course the problem then is that there are not so many functions that don't use any of that core infrastructure, so if you try to const-ify them you end up having to cast away const internally; which does not seem like a net advance to me.) 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] const correctness
On Wed, Nov 9, 2011 at 10:45 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: Perhaps there should be a few more 'XXX_const' accessor function variants, for example list_nth_const, This is exactly what was bothering Robert and me about Peter's patch.If you go down this road you soon start needing duplicate functions for no other reason than that one takes/returns const and one doesn't. What about existing functions which are not intended to modify their inputs, don't actually do so, and can be marked to indicate that just by adding const to the current declarations? Aside from any possible value in code optimization by the compiler, I find it helps me understand unfamiliar code more quickly, by making the contract of the API more explicit in the declaration. Perhaps it's worth going after the low-hanging fruit? My feeling is that there's no harm (and possibly some benefit) in const-ifying functions that do very simple things. But as soon as you get to functions where the const-ness starts growing all over the system like kudzu, it's time to run away screaming. Moreover, I don't really want to see us spend a lot of time figuring out exactly what we can or can't const-ify. I feel as virtuous as the next guy when I mark something const, but my experience over the years is that it rapidly turns a huge amount of work. That by itself is not enough reason not to do it; many worthwhile things are hard. The kicker is that it's a lot of work for an unbelievably tiny benefit, sometimes a negative benefit. -- 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] Concurrent CREATE TABLE/DROP SCHEMA leaves inconsistent leftovers
Yeah thanks, that does the object locking. For pre-9.1 versions, we will need a similar solution. I encountered the issue on 8.3.x.. I don't think we should back-patch a fix of this type. There is a lot of cruftiness of this type scattered throughout the code base, and if we start back-patching all the fixes for it, we're going to end up destabilizing older branches for little real benefit. Ok, understood. Thanks and Regards, Nikhils
Re: [HACKERS] a modest improvement to get_object_address()
On Wed, Nov 9, 2011 at 10:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Nov 9, 2011 at 9:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: I would think you need to drop the now-useless lock, and I sure hope that RangeVarGetRelid does likewise. It doesn't currently. The now-useless lock doesn't really hurt anything, aside from taking up space in the lock table. Well, there are corner cases where the object OID gets reused during the lifetime of the transaction, and then the lock *does* do something (and what it does would be bad). But taking up extra space in the finite-size lock table is sufficient reason IMO to drop the lock. It's not like these are performance-critical code paths. OK. -- 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
[HACKERS] reduce null bitmap size
hi, how about making heap_form_tuple and its variants use smaller natts for tuples whose trailing columns are NULL? depending on the schema, it can save considerable space. the most of code are ready to deal with such tuples for ALTER TABLE ADD COLUMN anyway. (except system catalogs?) YAMAMOTO Takashi -- 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] ProcArrayLock contention
hi, I've been playing with the attached patch, which adds an additional light-weight lock mode, LW_SHARED2. LW_SHARED2 conflicts with LW_SHARED and LW_EXCLUSIVE, but not with itself. The patch changes ProcArrayEndTransaction() to use this new mode. IOW, multiple processes can commit at the same time, and multiple processes can take snapshots at the same time, but nobody can take a snapshot while someone else is committing. Needless to say, I don't we'd really want to apply this, because adding a LW_SHARED2 mode that's probably only useful for ProcArrayLock would be a pretty ugly wart. But the results are interesting. pgbench, scale factor 100, unlogged tables, Nate Boley's 32-core AMD box, shared_buffers = 8GB, maintenance_work_mem = 1GB, synchronous_commit = off, checkpoint_segments = 300, checkpoint_timeout = 15min, checkpoint_completion_target = 0.9, wal_writer_delay = 20ms, results are median of three five-minute runs: #clients tps(master) tps(lwshared2) 1 657.984859 683.251582 8 4748.906750 4946.069238 32 10695.160555 17530.390578 80 7727.563437 16099.549506 That's a pretty impressive speedup, but there's trouble in paradise. With 80 clients (but not 32 or fewer), I occasionally get the following error: ERROR: t_xmin is uncommitted in tuple to be updated So it seems that there's some way in which this locking is actually incorrect, though I'm not seeing what it is at the moment. Either that, or there's some bug in the existing code that happens to be exposed by this change. The patch also produces a (much smaller) speedup with regular tables, but it's hard to know how seriously to take that until the locking issue is debugged. Any ideas? latestCompletedXid got backward due to concurrent updates and it fooled TransactionIdIsInProgress? YAMAMOTO Takashi -- 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] const correctness
On 9 November 2011 15:24, Tom Lane t...@sss.pgh.pa.us wrote:. If you go down this road you soon start needing duplicate functions for no other reason than that one takes/returns const and one doesn't. Why would you have to do that? To my mind, the fact that const spreads is a feature, not a deficiency. -- 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] new warning
This commit adds a new warning on my machine: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d326d9e8ea1d690cf6d968000efaa5121206d231 copy.c: In function *DoCopy*: copy.c:1861:14: warning: *bufferedTuples* may be used uninitialized in this function -Kevin -- 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] a modest improvement to get_object_address()
On Wed, Nov 9, 2011 at 10:50 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/11/9 Robert Haas robertmh...@gmail.com: On Wed, Nov 9, 2011 at 8:37 AM, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: Maybe I miss something but: I read that the error was produced by first session and didn't check carefuly (it fails silently in 9.0! and 'works' as expected in 9.1) No objection, but I would like to still be able to diagnose the same things as in the past, can you make it clear that the schema/object just disappear ? (else we don't know if the relation just never exists or was drop while we were waiting) I don't see a clean way to do that, and I'm not convinced it's a good idea anyway. I think that if we start generating different error messages based on whether or not a lock wait was involved at some point in the operation, we're going to drive ourselves nuts. There are a lot of places where that can happen. e.g. Suppose that you have a table with a unique index on column a. Transaction A deletes the tuple where a = 1. Transaction B attempts to insert a new tuple with a = 1, and blocks. Now if A commits, B will succeed, but if A rolls back, B will abort. Had transaction A not existed, B would simply abort at once. But the error message will not indicate which of the two it was, and I don't thinkit needs to. -- 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] new warning
On Wed, Nov 9, 2011 at 11:12 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: This commit adds a new warning on my machine: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d326d9e8ea1d690cf6d968000efaa5121206d231 copy.c: In function *DoCopy*: copy.c:1861:14: warning: *bufferedTuples* may be used uninitialized in this function Yeah, I was just noticing that. It looks like it's just a case of the compiler failing to see that there can't be a problem in real life, so I pushed a fix to initialize it to NULL. -- 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
[HACKERS] parallel make failure
We still seem to have some dependency issues with parallel builds. I just got this on a fresh F16 box against git tip, when running make -j 3: ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DECPG_COMPILE -I../include -I../../../../src/interfaces/ecpg/include -I. -I. -DMAJOR_VERSION=4 -DMINOR_VERSION=7 -DPATCHLEVEL=0 -I../../../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o parser.o parser.c -MMD -MP -MF .deps/parser.Po parser.c:25:21: fatal error: preproc.h: No such file or directory compilation terminated. cheers andrew -- 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] const correctness
Robert Haas robertmh...@gmail.com wrote: My feeling is that there's no harm (and possibly some benefit) in const-ifying functions that do very simple things. But as soon as you get to functions where the const-ness starts growing all over the system like kudzu, it's time to run away screaming. The patch attached to Thomas's original post is an example of what I consider low-hanging fruit worth going after. It applies cleanly, causes no new warnings, and adds no new objects -- it just clarifies the API. (It was in checking for new warnings that I found the one I mentioned in the other post.) Moreover, I don't really want to see us spend a lot of time figuring out exactly what we can or can't const-ify. Well, nobody is asking you to do so. Thomas said that he was looking for something to do which would lead him through the code so he could learn it. I feel as virtuous as the next guy when I mark something const, but my experience over the years is that it rapidly turns a huge amount of work. That by itself is not enough reason not to do it; many worthwhile things are hard. If Thomas wants to do this as an exercise in learning PostgreSQL code, it seems like a win/win to me. We get minor clarifications of our APIs and another person with some understanding of the code base. The kicker is that it's a lot of work for an unbelievably tiny benefit, sometimes a negative benefit. Assuming duplicate declarations with and without const are off the table, where do you see the negative? -Kevin -- 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] parallel make failure
Andrew Dunstan and...@dunslane.net writes: We still seem to have some dependency issues with parallel builds. I just got this on a fresh F16 box against git tip, when running make -j 3: ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DECPG_COMPILE -I../include -I../../../../src/interfaces/ecpg/include -I. -I. -DMAJOR_VERSION=4 -DMINOR_VERSION=7 -DPATCHLEVEL=0 -I../../../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o parser.o parser.c -MMD -MP -MF .deps/parser.Po parser.c:25:21: fatal error: preproc.h: No such file or directory compilation terminated. I think you have a broken version of make. ecpg/preproc/Makefile is perfectly clear that parser.o depends on preproc.h. 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] Measuring relation free space
On Tue, Nov 8, 2011 at 7:19 PM, Greg Smith g...@2ndquadrant.com wrote: On 11/08/2011 05:07 PM, Robert Treat wrote: It's already easy to get good enough numbers based on user space tools with very little overhead, so I think it's more important that the server side tool be accurate rather than fast. What user space method do you consider good enough here? I haven't found any approximation that I was really happy with; wouldn't have bothered with this otherwise. check_postgres and the pg_bloat_report both use a method of comparing on disk size vs estimated size based on table structure (or index info). Run regularly, it's certainly possible to keep bloat under control. That said, I'd still like to see something more accurate. Robert Treat conjecture: xzilla.net consulting: omniti.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reduce null bitmap size
y...@mwd.biglobe.ne.jp (YAMAMOTO Takashi) writes: how about making heap_form_tuple and its variants use smaller natts for tuples whose trailing columns are NULL? This idea has been proposed before, and rejected on the basis that it's unlikely to save enough to be worth the cycles needed to check for the case. Keep in mind that you aren't going to save anything at all unless the bitmap length decreases across a MAXALIGN boundary. 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] parallel make failure
On 11/09/2011 11:35 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: We still seem to have some dependency issues with parallel builds. I just got this on a fresh F16 box against git tip, when running make -j 3: ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DECPG_COMPILE -I../include -I../../../../src/interfaces/ecpg/include -I. -I. -DMAJOR_VERSION=4 -DMINOR_VERSION=7 -DPATCHLEVEL=0 -I../../../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o parser.o parser.c -MMD -MP -MF .deps/parser.Po parser.c:25:21: fatal error: preproc.h: No such file or directory compilation terminated. I think you have a broken version of make. ecpg/preproc/Makefile is perfectly clear that parser.o depends on preproc.h. Stock distro build: GNU Make 3.82 Built for i386-redhat-linux-gnu It's not repeatable, at least not easily. I'll let it go, but I won't be surprised if we see sporadic reports of this. cheers andrew -- 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] Collect frequency statistics for arrays
Rebased with head. -- With best regards, Alexander Korotkov. arrayanalyze-0.7.patch.gz Description: GNU Zip compressed 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] const correctness
On Wed, Nov 9, 2011 at 11:28 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: The kicker is that it's a lot of work for an unbelievably tiny benefit, sometimes a negative benefit. Assuming duplicate declarations with and without const are off the table, where do you see the negative? If it doesn't uglify the code, there aren't any negatives. I'm just saying we may not be able to get very far before we run up against that issue. For example, in the OP, Thomas wrote: 7. I made a list_head_const function, which can be used used to get a pointer to the head cell when you have a pointer to const List; I needed that so I could make foreach_const and forboth_const; they were needed to be able to make list_member, _equalList and various other list-visiting functions work with const List objects. So that's already duplicating list_head, foreach, and forboth. -- 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] parallel make failure
On Wed, Nov 9, 2011 at 11:46 AM, Andrew Dunstan and...@dunslane.net wrote: On 11/09/2011 11:35 AM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: We still seem to have some dependency issues with parallel builds. I just got this on a fresh F16 box against git tip, when running make -j 3: ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DECPG_COMPILE -I../include -I../../../../src/interfaces/ecpg/include -I. -I. -DMAJOR_VERSION=4 -DMINOR_VERSION=7 -DPATCHLEVEL=0 -I../../../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o parser.o parser.c -MMD -MP -MF .deps/parser.Po parser.c:25:21: fatal error: preproc.h: No such file or directory compilation terminated. I think you have a broken version of make. ecpg/preproc/Makefile is perfectly clear that parser.o depends on preproc.h. Stock distro build: GNU Make 3.82 Built for i386-redhat-linux-gnu It's not repeatable, at least not easily. I'll let it go, but I won't be surprised if we see sporadic reports of this. Either make 3.82 is broken, or there's something about it's behavior we don't understand. We've had other reports of problems with that version (by me, just a week or two ago, at least). -- 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] [Feature Request] \dx show options
On Wed, Nov 9, 2011 at 7:57 AM, Emanuel Calvo postgres@gmail.com wrote: I was thinking that could be great to add a column in \dx showing the available options for each foreign data wrapper. Seems reasonable? \dx doesn't list foreign data wrappers. \dew+ lists the actual options supplied to a foreign data wrapper already. -- 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] Misleading CREATE TABLE error
On Tue, Nov 8, 2011 at 4:49 PM, Thom Brown t...@linux.com wrote: I found the following error message misleading: test=# create table cows2 (LIKE cows); ERROR: inherited relation cows is not a table STATEMENT: create table cows2 (LIKE cows); I'm not trying to inherit a relation, I'm trying to base a table on it. As it happens, cows is a foreign table, which *is* a table, just not a regular table. It might be useful to add support to clone foreign tables into regular tables, the use-case being that you may wish to import all the data locally into a table of the same structure. But the gripe here is the suggestion that the relation would have been inherited, which would actually be achieved using INHERITS. Interesting. I agree that there's no obvious reason why that shouldn't be allowed to work. Could be useful with views, too. -- 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] [Feature Request] \dx show options
2011/11/9 Robert Haas robertmh...@gmail.com: On Wed, Nov 9, 2011 at 7:57 AM, Emanuel Calvo postgres@gmail.com wrote: I was thinking that could be great to add a column in \dx showing the available options for each foreign data wrapper. Seems reasonable? \dx doesn't list foreign data wrappers. I was confused, forget it. \dew+ lists the actual options supplied to a foreign data wrapper already. Checked, but the options doesn't appear (the column exists, but is empty). postgres=# \dew+ List of foreign-data wrappers Name| Owner | Handler | Validator | Access privileges | Options ---+--+---+-+---+- file_fdw | postgres | file_fdw_handler | file_fdw_validator | | mysql_fdw | postgres | mysql_fdw_handler | mysql_fdw_validator | | (2 rows) -- -- Emanuel Calvo Helpame.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] parallel make failure
Robert Haas robertmh...@gmail.com writes: On 11/09/2011 11:35 AM, Tom Lane wrote: I think you have a broken version of make. ecpg/preproc/Makefile is perfectly clear that parser.o depends on preproc.h. Either make 3.82 is broken, or there's something about it's behavior we don't understand. We've had other reports of problems with that version (by me, just a week or two ago, at least). I wonder whether it's related to the dummy rule for preproc.h: preproc.h: preproc.c ; preproc.c: preproc.y ifdef BISON $(BISON) -d $(BISONFLAGS) -o $@ $ else @$(missing) bison $ $@ endif I'd be tempted to try a non-empty rule for that, perhaps preproc.h: preproc.c @echo preproc.c rebuilt and see if that makes any difference. BTW, didn't we establish that it makes a difference whether you launch the make run from the top level or locally in ecpg/preproc/? That seems to me to be prima facie evidence of a make bug. 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] Disable OpenSSL compression
On Wednesday, November 9, 2011, Albe Laurenz wrote: Christopher Browne wrote: I think that JDBC and Npgsql should also support disabling compression. That's the *real* problem here... You're quite right that if we allow controlling this on the libpq side, it is surely desirable to allow controlling this via JDBC, Npgsql, and other mechanisms people may have around. [...] With that series of complications, I wonder if maybe the right place to control this is pg_hba.conf. I think that wouldn't work, because to query pg_hba.conf, you have to know user and database, which come from the client side. But the SSL negotiation takes place earlier, namely when the connection is established. Oh, right, that's going to be a problem doing it there. I wonder how many SSL parameters there are which would be worth trying to have available. I expect we'd benefit from looking at all the relevant ones at once, so as to not have the problem of hacking one more into place and perhaps doing it a bit differently each time. Sure, if anybody can think of any. A quick look at man SSL_CTX_set_options didn't show me any, but then OpenSSL's documentation is very bad (the page does not even mention SSL_OP_NO_COMPRESSION) and I am no SSL expert. Oh yeah, their docs are. Um. Yeah, let's leave it at that. I think the other one is to control which encryption options are available - but we already have a guc for that. Is the following proposal acceptable: - Add a GUC ssl_compression, defaulting to on. - Add a client option sslcompression and an environment variable PGSSLCOMPRESSION, defaulting to 1. Seems like the reasonable thing, yes. Compression will be disabled if either side refuses. I assume OpenSSL takes care of this for us, right? We just have to set the flags on the connection? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [HACKERS] 9.1.2 ?
On 9 Nov 2011, at 05:06, Magnus Hagander wrote: I definitely think they are important enough to trigger a release. But as you say, I think we need confirmation that they actually fix the problem... Would you consider it a blocker for a rollout on production system ?
Re: [HACKERS] 9.1.2 ?
I definitely think they are important enough to trigger a release. But as you say, I think we need confirmation that they actually fix the problem... Just last night Heroku was offering to help us test replication stuff. I'll take them up on it. Link for the patch and issue in question? -- 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] new warning
On 9 Nov 2011, at 16:16, Robert Haas wrote: On Wed, Nov 9, 2011 at 11:12 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: This commit adds a new warning on my machine: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=d326d9e8ea1d690cf6d968000efaa5121206d231 copy.c: In function *DoCopy*: copy.c:1861:14: warning: *bufferedTuples* may be used uninitialized in this function Yeah, I was just noticing that. It looks like it's just a case of the compiler failing to see that there can't be a problem in real life, so I pushed a fix to initialize it to NULL. Yes, but one day someone will add some more code there - and will forget to initialise it to NULL.. Kabboom. ;) -- 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] const correctness
On 9 Nov 2011, at 15:33, Peter Geoghegan wrote: On 9 November 2011 15:24, Tom Lane t...@sss.pgh.pa.us wrote:. If you go down this road you soon start needing duplicate functions for no other reason than that one takes/returns const and one doesn't. Why would you have to do that? To my mind, the fact that const spreads is a feature, not a deficiency. +1. I would go as far as compiling most of my stuff using C++ compiler, because it is much more strict about const-correctness. (but then I have rule about making source files small). C compilers (and standard) allows you to do silly things like : char *foo = barbar; foo[1] = '4'; Not an option in C++ and if you use const correctness. I had few bugs like that in the past, where pointer was passed around (in C code), and one of the pointers was pointing to const string - but since compiler was fine with it... You know what happened. And that was on an embedded platform which made it even harder to trace down. The point is, const correctness is deeply unappreciated. Added bonus is the fact that compiler can make extra optimisations based on the const keyword. Kind of like read-only transactions in the database. Probably the most extreme and tedious way of introducing full const correctness in PostgreSQL would be to rename all files to .cpp, and let c++ compiler tell you what you need to fix. -- 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] 9.1.2 ?
On 11/09/2011 01:12 PM, Greg Jaskiewicz wrote: Would you consider it a blocker for a rollout on production system ? I wouldn't. Good process for checking your backups should find this problem if it pops up, and it's not that easy to run into. That's why I was saying there are workarounds here, they're just not nice to put people through. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: In COPY, insert tuples to the heap in batches.
On 09.11.2011 15:25, Simon Riggs wrote: On Wed, Nov 9, 2011 at 9:06 AM, Heikki Linnakangas heikki.linnakan...@iki.fi wrote: In COPY, insert tuples to the heap in batches. This greatly reduces the WAL volume, especially when the table is narrow. The overhead of locking the heap page is also reduced. Reduced WAL traffic also makes it scale a lot better, if you run multiple COPY processes at the same time. Sounds good. I can't see where this applies backup blocks. If it does, can you document why/where/how it differs from other WAL records? Good catch, I missed that. I copied the redo function from normal insertion, but missed that heap_redo() takes care of backup blocks for you, while heap2_redo() does not. I'll go fix that.. -- 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
[HACKERS] Adding Node support in outfuncs.c and readfuncs.c
Hi, The task in $subject is something I will have to do repeatedly for completing the Command Trigger patch. I've been doing some of them manually, covering initdb. Then I've been scripting away the editing. The script takes a Node number as input (because that's what you're given in ERROR messages) and as an output will edit outfuncs.c and readfuncs.c for you. That's only intended as a developer friendly help, not a part of the build process or the like, and I've been writing that in Emacs Lisp -- that's what make most sense to me (I don't do perl). Now, I intend to be maintaining the script if needs be, and it could be useful for others too. What about adding that into src/tools/editors/pgsrc.el? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support pgsrc.el Description: pgsrc.el -- 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] a modest improvement to get_object_address()
Robert Haas robertmh...@gmail.com writes: e.g. Suppose that you have a table with a unique index on column a. Transaction A deletes the tuple where a = 1. Transaction B attempts to That's DML, I agree with you there, no need. In DML we have MVCC. Back to the problem you raised, it's DDL and we're sitting in between SnapshotNow and catalog cache entries. Not so comfy. I would guess that the problem (I confess didn't read carefully enough) happens after having done a cache lookup when trying to use its result? Could we check the object still exists as part of the cache lookup, or would that mean we don't have a cache anymore? Or is the answer related to consuming invalidation messages before returning a stale entry from the cache? 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] const correctness
On Wed, Nov 9, 2011 at 2:35 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: If it doesn't uglify the code, there aren't any negatives. I'm just saying we may not be able to get very far before we run up against that issue. For example, in the OP, Thomas wrote: 7. I made a list_head_const function, which can be used used to get a pointer to the head cell when you have a pointer to const List; I needed that so I could make foreach_const and forboth_const; they were needed to be able to make list_member, _equalList and various other list-visiting functions work with const List objects. So that's already duplicating list_head, foreach, and forboth. OK, I failed to pick up on that properly. With that stripped out, you get the attached patch, which does nothing but add const to 661 lines. It still applies cleanly, builds with no warnings, and passes regression tests. So what happens when someone wants to use list_nth in one of the outfuncs? Would we then rip all these back out? Or would we then bite the bullet and duplicate the code? -- 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] a modest improvement to get_object_address()
On Wed, Nov 9, 2011 at 3:40 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Back to the problem you raised, it's DDL and we're sitting in between SnapshotNow and catalog cache entries. Not so comfy. I would guess that the problem (I confess didn't read carefully enough) happens after having done a cache lookup when trying to use its result? There's a test case in the original post, but yes, the problem happens when something changes between the time you do the catcache lookup and the time you acquire the lock. This is not a new problem; I'm just trying to give a more intelligible error message - and avoid unnecessary failures, as in the case where two concurrent DROP IF EXISTS operations target the same object and one of them unnecessarily rolls back. Could we check the object still exists as part of the cache lookup, or would that mean we don't have a cache anymore? Or is the answer related to consuming invalidation messages before returning a stale entry from the cache? All of that is way beyond the scope of what I'm doing 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] Syntax for partitioning
Thom Brown t...@linux.com writes: Whenever I cross the topic of partitioning in PostgreSQL, it's always a tad embarrassing to explain that it's still hacky compared to other database systems (and this came up again last week), so this is of particular interest to me. At The more I think about this problem, the more I think that the reason why we still don't have declarative partitioning is that it basically sucks. Other vendors offer it because they couldn't do better, but they are just asking the DBA to implement a service the system should be able to care for itself. Who knows better than PostgreSQL which part of the data are the most often queried and how to best lay them out on disk to ensure QoS? If you think that's the DBA, go ask Tom to implement query hints… More seriously, partitioning in PostgreSQL could be mostly transparent to the users and just working: it's all about data storage locality and we already have a sub-relation storage model. By using segment exclusion and some level of automatic clustering (physical organization) of data, we could have all the benefits of partitioning without the hassle of maintaining yet another explicit level of data definition. In particular, what part of the declarative partitioning system is intended to take care about creating the right partitions before new data are sent to them? In a first implementation, we could decide to partition the data over an index that the DBA has to pick, and then maintain a segment index which is a map of the data distribution in the physical segments, for the indexed columns. The granularity could be different and maybe dynamic so that you start at a block level map index for very little tables and grow up to a segment map index for huge tables that require thousands of segments, 1GB each. Then the system have to organize data modifications so that it optimizes the ranges to be the smallest possible on each map entry. And the executor then has to be able to consult that map at query time and exclude whole segments of data (or blocks for little tables) when the segment indexing is able to exclude data. With some tricks because we realize that depending on the size of the portions you skip you might not benefit from moving the head on the platter in another way than what the ongoing seqscan does, but we already have GUCs about that. We might also need some internal facilities to lock out per segment (or rather map entries) rather than per table so that we have something like a WHERE clause support for TRUNCATE. 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] 9.1.2 ?
On Wed, Nov 9, 2011 at 12:58 PM, Daniel Farina dan...@heroku.com wrote: On Tue, Nov 8, 2011 at 9:06 PM, Magnus Hagander mag...@hagander.net wrote: I definitely think they are important enough to trigger a release. But as you say, I think we need confirmation that they actually fix the problem... I have confirmed that the clog/subtrans fixes allow us to start up while in hot standby on otherwise problematic base backups. Also, this is something of a big deal to us; otherwise it happens frequently enough that I cannot claim that I can use hot standby in an unattended, automated way. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] MPI programming in postgreSQL backend source code
Hello, I'm a new programmer in postgreSQL source code.. Is possible use MPI functions in postgreSQL source code? Help me please! Best Regards. Rudyar. -- 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] heap vacuum cleanup locks
On Wed, Nov 9, 2011 at 3:46 PM, Simon Riggs si...@2ndquadrant.com wrote:
Holding buffer pins for a long time is a problem in Hot Standby also,
not just vacuum.
Agreed.
AFAIK seq scans already work page at a time for normal tables. So the
issue is when we *aren't* using a seq scan, e.g. nested loops joins.
Is there a way to solve that?
Well, I'm not sure of the details of how page-at-a-time mode works for
seq scans, but I am absolutely 100% sure that you can reproduce this
problem using a cursor over a sequential scan. Just do this:
create table test (a text);
insert into test values ('aaa'), ('bbb');
delete from test where a = 'aaa';
begin;
declare x cursor for select * from test;
fetch next from x;
Then switch to another session and run VACUUM test. Prior to commit
bbb6e559c4ea0fb4c346beda76736451dc24eb4e, this would hang. Now, it
doesn't. But VACUUM FREEZE test still does.
As for what to do about all this, I think Tom's idea would work for
good tuples, but the current freezing code can't handle dead tuples;
it counts on those having been already removed. I wonder if we could
just set xmin = InvalidTransactionId and set HEAP_XMIN_INVALID, or
something like that. I'm worried that there might be code out there
that thinks InvalidTransactionId can never appear in a real tuple.
--
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] heap vacuum cleanup locks
On Tue, Nov 8, 2011 at 3:26 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Nov 8, 2011 at 2:26 AM, Simon Riggs si...@2ndquadrant.com wrote: I think we need to avoid long pin hold times generally. In the case of a suspended sequential scan, which is the case where this has most recently bitten me on a production system, it actually seems rather unnecessary to hold the pin for a long period of time. If we release the buffer pin, then someone could vacuum the buffer. This seems unlikely to be a productive line of thought. The only way you could release buffer pin is if you first copied all the tuples you need out of the page, and that seems like an unacceptable performance hit. We should not be penalizing foreground query operations for the benefit of background maintenance like VACUUM. (The fact that we do an equivalent thing in btree index scans isn't an argument for doing it here, because the tradeoffs are very different. In the index case, the amount of data to be copied is a great deal less; the length of time the lock would have to be held is often a great deal more; and releasing the lock quickly gives a performance benefit for other foreground operations, not only background maintenance.) It strikes me that the only case where vacuum now has to wait is where it needs to freeze an old XID. Couldn't it do that without insisting on exclusive access? We only need exclusive access if we're going to move data around, but we could have a code path in vacuum that just replaces old XIDs with FrozenXID without moving/deleting anything. Holding buffer pins for a long time is a problem in Hot Standby also, not just vacuum. AFAIK seq scans already work page at a time for normal tables. So the issue is when we *aren't* using a seq scan, e.g. nested loops joins. Is there a way to solve that? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] const correctness
Robert Haas robertmh...@gmail.com wrote: So what happens when someone wants to use list_nth in one of the outfuncs? Would we then rip all these back out? If we just go this far and don't create a separate const flavor of the one function and two macros, then we would at least need to rip out the const keyword on the parameter of the affected function(s). Or would we then bite the bullet and duplicate the code? I'm not sure we shouldn't go that far right up front. The entire body of the only duplicated function is: return l ? l-head : NULL; As cloned code goes, I've seen worse. Of the two new macros, one has three lines of body, the other has one line. If people aren't inclined to support this on the grounds of API clarity, maybe we should do some sort of benchmark run while we have a patch which applies cleanly before writing off the possible performance impact, but I'm not sure what makes a good stress-test for the affected code. -Kevin -- 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] 9.1.2 ?
On Tue, Nov 8, 2011 at 9:06 PM, Magnus Hagander mag...@hagander.net wrote: I definitely think they are important enough to trigger a release. But as you say, I think we need confirmation that they actually fix the problem... I have confirmed that the clog/subtrans fixes allow us to start up while in hot standby on otherwise problematic base backups. -- 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] const correctness
Kevin Grittner kevin.gritt...@wicourts.gov writes: If people aren't inclined to support this on the grounds of API clarity, maybe we should do some sort of benchmark run while we have a patch which applies cleanly before writing off the possible performance impact, but I'm not sure what makes a good stress-test for the affected code. I don't doubt that just duplicating macros and inlineable functions is a wash performance-wise (in fact, in principle it shouldn't change the generated code at all). My objection is the one Robert already noted: it takes extra brain cells to remember which function/macro to use, and I have seen not a shred of evidence that that extra development/maintenance effort will be repaid. I think that const works materially better in C++ where you can overload foo(struct *) and foo(const struct *) and let the compiler sort out which is being called. In C, the impedance match is a lot worse, so you have to pick and choose where const is worth the trouble. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] heap vacuum cleanup locks
On Wed, Nov 9, 2011 at 9:12 PM, Robert Haas robertmh...@gmail.com wrote:
Well, I'm not sure of the details of how page-at-a-time mode works for
seq scans, but I am absolutely 100% sure that you can reproduce this
problem using a cursor over a sequential scan. Just do this:
create table test (a text);
insert into test values ('aaa'), ('bbb');
delete from test where a = 'aaa';
begin;
declare x cursor for select * from test;
fetch next from x;
That's a bug. heapam.c line 1202 says
/*
* we can use page-at-a-time mode if it's an MVCC-safe snapshot
*/
scan-rs_pageatatime = IsMVCCSnapshot(snapshot);
So either the comment or the code is wrong.
Can't see where, as yet.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] const correctness
Tom Lane t...@sss.pgh.pa.us wrote: I don't doubt that just duplicating macros and inlineable functions is a wash performance-wise (in fact, in principle it shouldn't change the generated code at all). I had the impression that compilers these days could sometimes better optimize across calls to functions with const parameters, because previously-referenced elements of the structures could be trusted to be unchanged across the call. I'm not talking about calls to the inlineable function or macros themselves, but the higher level functions which can then use const. My objection is the one Robert already noted: it takes extra brain cells to remember which function/macro to use, and I have seen not a shred of evidence that that extra development/maintenance effort will be repaid. Well, for me at least, seeing a parameter flagged as const helps me be sure that it will be use only for input to the function, and thus more quickly grasp the semantics of the API. For someone who is already familiar with an API, I doubt it helps much; and it may be one of those cognitive differences that just exist between people. As far as which to use when there is a const and a non-const version -- how is that unclear? For me it seems intuitively obvious (although perhaps my intuition is off-base) that I would use const when I didn't want the called function to change what was pointed at by the parameter. Maybe you're looking at the slippery slope more than this one function and two macros, though. In C, the impedance match is a lot worse, so you have to pick and choose where const is worth the trouble. Agreed. And I'm not sure how much of what Thomas is proposing is worth it; it just seems prudent to consider it while the offer is being made to do the work. -Kevin -- 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] Syntax for partitioning
On 9 November 2011 21:05, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Thom Brown t...@linux.com writes: Whenever I cross the topic of partitioning in PostgreSQL, it's always a tad embarrassing to explain that it's still hacky compared to other database systems (and this came up again last week), so this is of particular interest to me. At The more I think about this problem, the more I think that the reason why we still don't have declarative partitioning is that it basically sucks. Other vendors offer it because they couldn't do better, but they are just asking the DBA to implement a service the system should be able to care for itself. Who knows better than PostgreSQL which part of the data are the most often queried and how to best lay them out on disk to ensure QoS? If you think that's the DBA, go ask Tom to implement query hints… That also sounds like an argument against indexes. Since the system knows which parts of data are most often queried, surely it should be managing indexes, not the DBA? I imagine structuring data after the fact would involve rewriting data whereas planning for upcoming data is something DBAs are expected to do anyway using constraints, triggers, indexes etc. And as it stands, what the DBA has to do at the moment is to exploit table inheritance, apply constraints to each of the child tables (mainly for constraint exclusion), then create a trigger to support various types of update/insert/delete. So what we have now is very un-user-friendly, and tends to surprise end users. The flow of data isn't part of the table definition, it's custom-programmed into an event on the parent table. And partitioning may not just be about performance, it can be about organising data and making it more manageable. Although I agree that if it came to partitioning across nodes for parallel access, PostgreSQL could be in a position to make decisions about how that is distributed. More seriously, partitioning in PostgreSQL could be mostly transparent to the users and just working: it's all about data storage locality and we already have a sub-relation storage model. By using segment exclusion and some level of automatic clustering (physical organization) of data, we could have all the benefits of partitioning without the hassle of maintaining yet another explicit level of data definition. That could be unworkable in a high-load OLTP environment. In particular, what part of the declarative partitioning system is intended to take care about creating the right partitions before new data are sent to them? In a first implementation, we could decide to partition the data over an index that the DBA has to pick, and then maintain a segment index which is a map of the data distribution in the physical segments, for the indexed columns. The granularity could be different and maybe dynamic so that you start at a block level map index for very little tables and grow up to a segment map index for huge tables that require thousands of segments, 1GB each. Then the system have to organize data modifications so that it optimizes the ranges to be the smallest possible on each map entry. And the executor then has to be able to consult that map at query time and exclude whole segments of data (or blocks for little tables) when the segment indexing is able to exclude data. With some tricks because we realize that depending on the size of the portions you skip you might not benefit from moving the head on the platter in another way than what the ongoing seqscan does, but we already have GUCs about that. We might also need some internal facilities to lock out per segment (or rather map entries) rather than per table so that we have something like a WHERE clause support for TRUNCATE. Would this solve the same set of problems that partitioning attempts to address? And what about the example case of quarterly data? In your proposed design could you drop an entire set of data without a DELETE? And maybe I'm not looking at it from the right angle. (probably) Although I appreciate some thought is needed about how useful partitioning implementations in other database systems really are. And now to demonstrate some additional ignorance on my part... does the standard cover this? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: 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] heap vacuum cleanup locks
On Wed, Nov 9, 2011 at 9:48 PM, simon si...@2ndquadrant.com wrote:
On Wed, Nov 9, 2011 at 9:12 PM, Robert Haas robertmh...@gmail.com wrote:
Well, I'm not sure of the details of how page-at-a-time mode works for
seq scans, but I am absolutely 100% sure that you can reproduce this
problem using a cursor over a sequential scan. Just do this:
create table test (a text);
insert into test values ('aaa'), ('bbb');
delete from test where a = 'aaa';
begin;
declare x cursor for select * from test;
fetch next from x;
That's a bug.
No, I'm wrong. It's not a bug at all.
heapgetpage() gets a page and a pin, but holds the pin until it reads
the next page. Wow!
That is both annoying and very dumb. It should hold the pin long
enough to copy the data and then release the pin.
It looks inefficient from a memory access viewpoint and from a pin
longevity viewpoint. If we copied out relevant data it would be more
cache efficient without affecting visibility. Looking at a patch.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] const correctness
Kevin Grittner kevin.gritt...@wicourts.gov writes: In C, the impedance match is a lot worse, so you have to pick and choose where const is worth the trouble. Agreed. And I'm not sure how much of what Thomas is proposing is worth it; it just seems prudent to consider it while the offer is being made to do the work. If the gain is for human readers of the API rather than the compiler and some level of automated checking, what about this trick: #define constp Then you can use it wherever you want to instruct readers that the parameter is a constant, it's now a noise word as far as the compiler is concerned (thanks to the precompiler replacing it with an empty string). 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] Cost estimates for parameterized paths
More than a year ago, I wrote in http://archives.postgresql.org/message-id/14624.1283463...@sss.pgh.pa.us Awhile back I ranted about replacing the planner's concept of inner indexscans with a more generalized notion of parameterized paths: http://archives.postgresql.org/pgsql-hackers/2009-10/msg00994.php The executor fixes for that are done, and now I'm grappling with getting the planner to do something useful with it. The biggest problem I've run into is that a parameterized path can't really be assigned a fixed cost in the same way that a normal path can. The current implementation of cost_index() depends on knowing the size of the outer relation --- that is, the expected number of execution loops for the indexscan --- in order to account for cache effects sanely while estimating the average cost of any one inner indexscan. Since this project has been stalled for so long, I am thinking that what I need to do to make some progress is to punt on the repeated-execution cache effects problem, at least for the first cut. I propose costing parameterized inner paths on the worst case basis that they're only executed once, and don't get any benefit from caching across repeated executions. This seems like a reasonably sane first-order approximation on two grounds: 1. In most of the cases where such a plan is of interest, the outer relation for the nestloop actually does provide only one or a few rows. If it generates a lot of rows, you probably don't want a nestloop anyhow. 2. In the cases where we really care, the alternatives are so much worse that the parameterized nestloop will win even if it's estimated very conservatively. Another thing in the back of my mind is that the whole issue of cache effects is something we know we don't model very well, so putting large amounts of time into enlarging the present approach to handle more complicated plan structures may be misplaced effort anyway. So unless somebody's got a better idea, I'm going to push forward with getting the parameterized-path infrastructure in place, and just settle for a crude cost model for the moment. Perhaps the implementation experience will shake loose some new ideas about how to do the cost modeling. 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] heap vacuum cleanup locks
Robert Haas robertmh...@gmail.com writes: As for what to do about all this, I think Tom's idea would work for good tuples, but the current freezing code can't handle dead tuples; it counts on those having been already removed. I have not gone back to look at the code, but are you worried about the fact that it doesn't consider replacing xmax with FrozenTransactionId? Surely we could do that if we wanted. It just never seemed necessary before --- but if vacuum is to be allowed to punt repeatedly on the same page, maybe we do need to cover the case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] heap vacuum cleanup locks
Simon Riggs si...@2ndquadrant.com writes: heapgetpage() gets a page and a pin, but holds the pin until it reads the next page. Wow! That is both annoying and very dumb. It should hold the pin long enough to copy the data and then release the pin. I don't find that anywhere near as obvious as you seem to. I think you are trying to optimize for the wrong set of conditions. I will also note that the behavior of holding pin for as long as we are stopped on a particular tuple is not specific to seqscans. 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] 9.1.2 ?
On 11/09/2011 03:58 PM, Daniel Farina wrote: On Tue, Nov 8, 2011 at 9:06 PM, Magnus Hagandermag...@hagander.net wrote: I definitely think they are important enough to trigger a release. But as you say, I think we need confirmation that they actually fix the problem... I have confirmed that the clog/subtrans fixes allow us to start up while in hot standby on otherwise problematic base backups. I think Daniel has run into this problem more than anyone else, so hearing it's fixed for him makes me feel a lot better that it's been resolved. I'd characterize this problem as a medium grade data corruption issue. It's not security issue bad that it needs to be released tomorrow, but a backbranch release of at least 9.0/9.1 that includes it would be a big relief for people nervous about this. I'd hate to see that slip forward to where it gets sucked into the holiday vortex. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] const correctness
On Nov9, 2011, at 22:38 , Tom Lane wrote: I think that const works materially better in C++ where you can overload foo(struct *) and foo(const struct *) and let the compiler sort out which is being called. In C, the impedance match is a lot worse, so you have to pick and choose where const is worth the trouble. Yup. In fact, C++ even *forces* you to use const in a few instances - you aren't, for example, allowed to call non-const member functions on temporary objects (i.e., myclass().nonconstmember() fails to compile where as myclass().constmember() works as expected). Also, in C++ const influences actual run-time behaviour - there's a very real difference in the life-time of temporary objects depending on whether they're assigned to a const or a non-const reference. So, while C++ and C are similar in a lot of aspects, the situation regarding const is very different. best regards, Florian Pflug -- 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] Disable OpenSSL compression
Magnus Hagander mag...@hagander.net writes: On Wednesday, November 9, 2011, Albe Laurenz wrote: Is the following proposal acceptable: - Add a GUC ssl_compression, defaulting to on. - Add a client option sslcompression and an environment variable PGSSLCOMPRESSION, defaulting to 1. Seems like the reasonable thing, yes. A GUC is entirely, completely, 100% the wrong answer. It has no way to deal with the fact that some clients may need compression and others not. It should be a client option, full stop. The fact that that will be more work to implement does not make kluge it at the server the right answer. 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] const correctness
Thomas Munro mu...@ip9.org wrote: There is another option: if list_head is changed to take a pointer to const List and return a pointer to non-const ListCell (something I was trying to avoid before), then no XXX_const functions/macros are necessary, and all of the functions from the first patch can keep their 'const', adding const to 930 lines. Now that you mention it, I think that's better anyway. Just because you don't want the *called* function to change something doesn't seem like it should imply anything about whether the *caller* should be able to change something. Leave that to the caller unless the function is quite sure that it is returning a pointer to something which should be immutable in all cases. I've attached a new patch, which simply adds the keyword 'const' in lots of places, no new functions etc. This version generates no warnings under -Wcast-qual (now that I've read Peter E's thread and been inspired to fix up some places that previously cast away const) for all code under backend/nodes. To achieve that I had to stray outside backend/nodes and change get_leftop and get_rightop (from clauses.h). On this end it applies cleanly, compiles without warning, and passes check-world regression tests. -Kevin -- 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] const correctness
On Nov9, 2011, at 22:54 , Kevin Grittner wrote: Tom Lane t...@sss.pgh.pa.us wrote: I don't doubt that just duplicating macros and inlineable functions is a wash performance-wise (in fact, in principle it shouldn't change the generated code at all). I had the impression that compilers these days could sometimes better optimize across calls to functions with const parameters, because previously-referenced elements of the structures could be trusted to be unchanged across the call. I'm not talking about calls to the inlineable function or macros themselves, but the higher level functions which can then use const. I don't think that's true. Const (for pointer types) generally only means you cannot modify the value through *this* pointer. But there may very well be other pointers to the same object, and those may very well be used to modify the value at any time. So unless both the calling and the called function are in the same compilation unit, the compiler needs to assume that any non-local (and even local values whose address was taken previously) value in the calling function may change as a result of the function call. Or at least I think so. If we're concerned about helping the compiler produce better code, I think we should try to make our code safe under strict aliasing rules. AFAIK, that generally helps much more than const-correctness. (Dunno how feasible that is, though) best regards, Florian Pflug -- 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] Syntax for partitioning
On 11/09/2011 04:05 PM, Dimitri Fontaine wrote: The more I think about this problem, the more I think that the reason why we still don't have declarative partitioning is that it basically sucks. I think that we don't have it because no one has ever dumped the much larger than might be expected amount of time into pulling all the pieces together and smoothing out the rough parts. I don't think there's any design thinking leap needed over what's already been worked out. Just a lot of work to get all the edge cases right on the simplest possible thing that is useful. The path to reach something that could be considered for commit includes something like this set of things: 1) Add partitioning catalog support 2) Create new syntax for partitioning that writes to the catalog 3) Decide how to represent partition data in memory 4) Route new INSERTed data into the right place 5) Support moving UPDATEd data into a new partition 6) Handle COPY usefully The last rev of this submitted was still working through (1) here, i.e. this review from Robert: http://archives.postgresql.org/message-id/aanlktikp-1_8b04eyik0sdf8ua5kmo64o8sorfbze...@mail.gmail.com And there's a whole pile of issues I don't think have been fully explored about even the most basic case. How to handle ALTER to these structures cleanly, locking, etc.. I don't think it's possible to design such that you skip a large portion of these details; someone needs to put some number of spend weeks+ getting them all right instead. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] const correctness
Florian Pflug f...@phlo.org writes: If we're concerned about helping the compiler produce better code, I think we should try to make our code safe under strict aliasing rules. AFAIK, that generally helps much more than const-correctness. (Dunno how feasible that is, though) The last time we talked about that, we gave up and added -fno-strict-aliasing, mainly because nobody trusted gcc to warn us about violations of the aliasing rules. That was quite some time ago though. Perhaps recent gcc versions do better? 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] 9.1.2 ?
On Wed, Nov 9, 2011 at 2:24 PM, Greg Smith g...@2ndquadrant.com wrote: I think Daniel has run into this problem more than anyone else, so hearing it's fixed for him makes me feel a lot better that it's been resolved. I'd characterize this problem as a medium grade data corruption issue. It's not security issue bad that it needs to be released tomorrow, but a backbranch release of at least 9.0/9.1 that includes it would be a big relief for people nervous about this. I'd hate to see that slip forward to where it gets sucked into the holiday vortex. The first time I encountered this I had to reason very carefully for a while that I just did not suffer some sort of corruption problem or recovery bug. After I figured out that normal (non-hot-standby) recovery worked and what the general mechanism was only then I was sort-of-assuaged into letting it slide as a workaround. I think a novice user would be scared half to death: I know I was the first time. That's not a great impression for the project to leave for what is not, at its root, a vast defect, and the fact it's occurring for people when they use rsync rather than my very sensitive backup routines is indication that it's not very corner-ey. So that's my take on it. It's not a tomorrow severity release (we've been living with the workaround for months, even though it is blocking some things), but I would really appreciate an expedited release to enable unattended hot-standby operation and to avoid scaring those who encounter this. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_dump 9.1.1 hanging (collectSecLabels gets 0 labels)
We have a cluster running 9.1.1 where pg_dump hangs when we try to dump some a database inside of the cluster. The server is running AIX. I can see this on clean cluster where we do an initdb, followed by a createdb and try running pg_dump. I've tracked the issue down to collectSecLabels in pg_dump.c SELECT label, provider, classoid, objoid, objsbid FROM pg_catalog.pg_seclabel; returns 0 rows. The code in collectSecLabels() is not prepared to deal with a zero row result and tries to malloc 0 bytes. I am not yet sure if the problem is that my pg_seclabel is empty or if the issue is in collectSecLabels() or if collectSecLabels shouldn't even be called. Has anyone seen something similar? -- 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] const correctness
Kevin Grittner kevin.gritt...@wicourts.gov writes: Thomas Munro mu...@ip9.org wrote: There is another option: if list_head is changed to take a pointer to const List and return a pointer to non-const ListCell (something I was trying to avoid before), then no XXX_const functions/macros are necessary, and all of the functions from the first patch can keep their 'const', adding const to 930 lines. Now that you mention it, I think that's better anyway. IOW, the strchr() trick? If the C standards committee couldn't find any better answer than that, maybe we shouldn't expect to either. In general I don't have an objection to adding const to individual routines, so long as it doesn't create propagating requirements to const-ify other code. This may be the only way to do 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] heap vacuum cleanup locks
On Wed, Nov 9, 2011 at 10:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: heapgetpage() gets a page and a pin, but holds the pin until it reads the next page. Wow! That is both annoying and very dumb. It should hold the pin long enough to copy the data and then release the pin. I don't find that anywhere near as obvious as you seem to. I think you are trying to optimize for the wrong set of conditions. ISTM we should optimise to access the cachelines in the buffer once. Holding a pin and re-accessing the buffer via main memory seems pretty bad plan to me. Which conditions are being optimised by doing that? I will also note that the behavior of holding pin for as long as we are stopped on a particular tuple is not specific to seqscans. Agreed. Bad things may happen in more than one place. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MPI programming in postgreSQL backend source code
On 11/09/2011 04:10 PM, Rudyar Cortés wrote: I'm a new programmer in postgreSQL source code.. Is possible use MPI functions in postgreSQL source code? To do this the proper way, you would need to modify the database's configure step to: -Check if the OpenMPI libraries are available and include the necessary bits. For example, this is in one of the projects I work on: #ifdef _OPENMP extern int omp_get_num_threads(); #endif Some form of that test and defining the functions available would be needed for what you want. -Link OpenMPI in. At the gcc level you'll need -fopenmp to start. Then you could start using OpenMPI functions in database code. You might hack the build steps to do this in a simpler way, too, rather than fight with configure the proper way. Since a lot of the MPI functions aim at specific types of thread use and I/O, it would be a major effort to utilize the library for too many things. The existing notion of how processes are created and managed is scattered throughout the PostgreSQL code. And the I/O parts of the database are buried through a few layers of indirection. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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_dump 9.1.1 hanging (collectSecLabels gets 0 labels)
Steve Singer ssin...@ca.afilias.info writes: I've tracked the issue down to collectSecLabels in pg_dump.c SELECT label, provider, classoid, objoid, objsbid FROM pg_catalog.pg_seclabel; returns 0 rows. The code in collectSecLabels() is not prepared to deal with a zero row result and tries to malloc 0 bytes. pg_seclabel is almost always empty, so I'm not convinced that you've identified your problem correctly. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [patch] Include detailed information about a row failing a CHECK constraint into the error message
Hi,
when I insert/update many rows at once using INSERT ... SELECT into a
table which has plenty of CHECK constraints, the error message that
Postgres returns has no indication of which row failed the constraint
check. The attached patch tries to provide information in a similar way
to how duplicate items in a UNIQUE constraint are handled.
Originally, I tried to simply check the new row's t_ctid, but it was
always (0,0) -- I guess that's expected, maybe it's still in memory at
that time and maybe such nodes don't have a ctid assigned yet.
Please let me know if this patch is suitable for inclusion. It's based
on REL9_0_STABLE, because that's the version I'm running.
I'd like to thank intgr on IRC for his feedback when I was wondering
about the t_ctid.
With kind regards,
Jan
Hi Jan / all.
I'm looking for a simple patch to review and this one doesn't look too
complicate.
The patch seens to be useful, it adds a better feedback.
First, I couldn't apply it as in the email, even in REL9_0_STABLE: the
offset doesn't look right. Which commit are your repository in?
Anyway, I could copy / paste it at the correct place, using the
current master. I could compile it, put a postgres with it running and
it's working:
postgres=# create table test1(id serial primary key, value text);
NOTICE: CREATE TABLE will create implicit sequence test1_id_seq for
serial column test1.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
test1_pkey for table test1
CREATE TABLE
postgres=# ALTER TABLE test1 ADD CONSTRAINT must_be_unique unique (value);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index
must_be_unique for table test1
ALTER TABLE
postgres=# insert into test1 values (default, 'Hello World');
INSERT 0 1
postgres=# insert into test1 values (default, 'Hello World');
ERROR: duplicate key value violates unique constraint must_be_unique
DETAIL: Key (value)=(Hello World) already exists.
The patch I've used:
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index fd7a9ed..57894cf 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -1574,10 +1574,32 @@ ExecConstraints(ResultRelInfo *resultRelInfo,
const char *failed;
if ((failed = ExecRelCheck(resultRelInfo, slot, estate)) !=
NULL)
+ {
+ StringInfoData buf;
+ int natts = rel-rd_att-natts;
+ int i;
+ initStringInfo(buf);
+ for (i = 0; i natts; ++i)
+ {
+ char *val;
+ Oid foutoid;
+ bool typisvarlena;
+
getTypeOutputInfo(rel-rd_att-attrs[i]-atttypid, foutoid,
typisvarlena);
+ if (slot-tts_isnull[i])
+ val = NULL;
+ else
+ val = OidOutputFunctionCall(foutoid,
slot-tts_values[i]);
+ if (i 0)
+ appendStringInfoString(buf, , );
+ appendStringInfoString(buf, val);
+ }
ereport(ERROR,
(errcode(ERRCODE_CHECK_VIOLATION),
errmsg(new row for relation \%s\
violates check constraint \%s\,
-
RelationGetRelationName(rel), failed)));
+ RelationGetRelationName(rel),
failed),
+ errdetail(New row with data (%s)
violates check constraint \%s\.,
+ buf.data, failed)));
+ }
}
}
--
José Arthur Benetasso Villanova
--
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] const correctness
Tom Lane t...@sss.pgh.pa.us wrote: In general I don't have an objection to adding const to individual routines, so long as it doesn't create propagating requirements to const-ify other code. This may be the only way to do it. As I understand it (although I'm no C expert), a const qualifier on a function parameter declaration is a promise that the function will not modify what is thus qualified. That means that it can't pass a const parameter to another function as a parameter not also declared const. It doesn't say anything about the object itself or what is returned from the function. So a non-const parameter in can be passed to a const parameter in a call, but not vice versa. And a variable need not be declared const to pass it to a function as a const parameter. I don't know if this meets your conditions for non-propagation. -Kevin -- 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] 9.1.2 ?
So that's my take on it. It's not a tomorrow severity release (we've been living with the workaround for months, even though it is blocking some things), but I would really appreciate an expedited release to enable unattended hot-standby operation and to avoid scaring those who encounter this. The earliest we could release an update would the November 21st, the monday before American Thanksgiving. That seems doable to me ... should we ping the packagers about it? -- 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] Syntax for partitioning
On Wed, Nov 9, 2011 at 1:05 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Thom Brown t...@linux.com writes: Whenever I cross the topic of partitioning in PostgreSQL, it's always a tad embarrassing to explain that it's still hacky compared to other database systems (and this came up again last week), so this is of particular interest to me. At The more I think about this problem, the more I think that the reason why we still don't have declarative partitioning is that it basically sucks. Other vendors offer it because they couldn't do better, but they are just asking the DBA to implement a service the system should be able to care for itself. Your last sentence seems to be a better description of PostgreSQL's current implementation of partitioning via inheritance and constraints and triggers. Partitioning is a service the system should be able to care for itself, even if it does need the DBA to declare it. And why shouldn't it need a DBA to declare it? How is the system supposed to anticipate that at some point years in the future I will want to run the command sequence create foo_archive as select from foo where year2009; delete from foo where year2009, or its partition-based equivalent, and have it operate on several billion rows cleanly and quickly? I don't think we can expect the system to anticipate what it has never before experienced. This is the DBA's job. Who knows better than PostgreSQL which part of the data are the most often queried and how to best lay them out on disk to ensure QoS? If you think that's the DBA, go ask Tom to implement query hints… More seriously, partitioning in PostgreSQL could be mostly transparent to the users and just working: it's all about data storage locality and we already have a sub-relation storage model. By using segment exclusion and some level of automatic clustering (physical organization) of data, we could have all the benefits of partitioning without the hassle of maintaining yet another explicit level of data definition. While automatic clustering would be nice, it isn't the same thing as partitioning. 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] 9.1.2 ?
On Nov9, 2011, at 23:53 , Daniel Farina wrote: I think a novice user would be scared half to death: I know I was the first time. That's not a great impression for the project to leave for what is not, at its root, a vast defect, and the fact it's occurring for people when they use rsync rather than my very sensitive backup routines is indication that it's not very corner-ey. Just to emphasize the non-conerish-ness of this problem, it should be mentioned that the HS issue was observed even with backups taken with pg_basebackup, if memory serves correctly. best regards, Florian Pflug -- 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] 9.1.2 ?
On 11/09/2011 03:56 PM, Josh Berkus wrote: So that's my take on it. It's not a tomorrow severity release (we've been living with the workaround for months, even though it is blocking some things), but I would really appreciate an expedited release to enable unattended hot-standby operation and to avoid scaring those who encounter this. The earliest we could release an update would the November 21st, the monday before American Thanksgiving. That seems doable to me ... should we ping the packagers about it? Ehhh That week is kind of moot for most of the United States. Shouldn't it be like Tuesday the week after? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] 9.1.2 ?
Ehhh That week is kind of moot for most of the United States. Shouldn't it be like Tuesday the week after? Given that we start packaging on Thursday, that would mean waiting an additional 2 weeks. -- 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] Cost estimates for parameterized paths
On Wed, Nov 9, 2011 at 5:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: More than a year ago, I wrote in http://archives.postgresql.org/message-id/14624.1283463...@sss.pgh.pa.us Awhile back I ranted about replacing the planner's concept of inner indexscans with a more generalized notion of parameterized paths: http://archives.postgresql.org/pgsql-hackers/2009-10/msg00994.php The executor fixes for that are done, and now I'm grappling with getting the planner to do something useful with it. The biggest problem I've run into is that a parameterized path can't really be assigned a fixed cost in the same way that a normal path can. The current implementation of cost_index() depends on knowing the size of the outer relation --- that is, the expected number of execution loops for the indexscan --- in order to account for cache effects sanely while estimating the average cost of any one inner indexscan. Since this project has been stalled for so long, I am thinking that what I need to do to make some progress is to punt on the repeated-execution cache effects problem, at least for the first cut. I propose costing parameterized inner paths on the worst case basis that they're only executed once, and don't get any benefit from caching across repeated executions. This seems like a reasonably sane first-order approximation on two grounds: 1. In most of the cases where such a plan is of interest, the outer relation for the nestloop actually does provide only one or a few rows. If it generates a lot of rows, you probably don't want a nestloop anyhow. 2. In the cases where we really care, the alternatives are so much worse that the parameterized nestloop will win even if it's estimated very conservatively. I agree, on all counts. Errors that make new planner possibilities look unduly expensive aren't as serious as those that go the other way, because the alternative is that you can never generate the new plan at all. That's why I'm sweating about the costing index-only scans a bit. Another thing in the back of my mind is that the whole issue of cache effects is something we know we don't model very well, so putting large amounts of time into enlarging the present approach to handle more complicated plan structures may be misplaced effort anyway. True. And I think that might not even be the highest priority project to tackle anyway. The things that are hurting people most routinely and hardest to fix with existing tools seem to be things like cross-column correlation, and other selectivity estimation errors. -- 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] heap vacuum cleanup locks
On Wed, Nov 9, 2011 at 5:18 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
As for what to do about all this, I think Tom's idea would work for
good tuples, but the current freezing code can't handle dead tuples;
it counts on those having been already removed.
I have not gone back to look at the code, but are you worried about the
fact that it doesn't consider replacing xmax with FrozenTransactionId?
Surely we could do that if we wanted. It just never seemed necessary
before --- but if vacuum is to be allowed to punt repeatedly on the same
page, maybe we do need to cover the case.
No, I'm worried about the fact that that it does this:
xid = HeapTupleHeaderGetXmin(tuple);
if (TransactionIdIsNormal(xid)
TransactionIdPrecedes(xid, cutoff_xid))
{
if (buf != InvalidBuffer)
{
/* trade in share lock for exclusive lock */
LockBuffer(buf, BUFFER_LOCK_UNLOCK);
LockBuffer(buf, BUFFER_LOCK_EXCLUSIVE);
buf = InvalidBuffer;
}
HeapTupleHeaderSetXmin(tuple, FrozenTransactionId);
Note that the ONLY thing we're checking with respect to the tuple xmin
is that it's a normal XID that precedes the cutoff. We are not
checking whether it's committed. So there had better not be any
tuples left on the page that are from inserting transactions that
aborted, or this is going to go boom.
--
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] heap vacuum cleanup locks
On Wed, Nov 9, 2011 at 6:10 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, Nov 9, 2011 at 10:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: heapgetpage() gets a page and a pin, but holds the pin until it reads the next page. Wow! That is both annoying and very dumb. It should hold the pin long enough to copy the data and then release the pin. I don't find that anywhere near as obvious as you seem to. I think you are trying to optimize for the wrong set of conditions. ISTM we should optimise to access the cachelines in the buffer once. Holding a pin and re-accessing the buffer via main memory seems pretty bad plan to me. Which conditions are being optimised by doing that? I believe it reduces memory copying. But we can certainly test some alternative you may have in mind and see how it shakes out. -- 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] 9.1.2 ?
On Wed, Nov 9, 2011 at 9:03 PM, Josh Berkus j...@agliodbs.com wrote: Ehhh That week is kind of moot for most of the United States. Shouldn't it be like Tuesday the week after? Given that we start packaging on Thursday, that would mean waiting an additional 2 weeks. Yeah, I don't see what's wrong with the 21st. People may not install the update the minute it comes out, but that's not necessarily a big deal, especially since it's not a security update. The point is that all the packaging will be done *before* people leave to go eat Turkey. -- 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
