Re: [HACKERS] wrong message on REASSIGN OWNED
On Sat, Jun 11, 2011 at 8:01 PM, Robert Haas robertmh...@gmail.com wrote: can't reassign objects owned by %s because this user is internal to the database system ? that message is not that clear... it seems to imply i can't reassign any object from that user... btw, i'm allowed to use ALTER TABLE to assign a new owner (even an unprivileged one) to a system catalog, probably that's a bug. db=# create role unprivileged_user login; CREATE ROLE db=# alter table pg_class owner to unprivileged_user ; ALTER TABLE db=# \dt pg_class List of relations Schema | Name | Type | Owner +--+---+--- pg_catalog | pg_class | table | unprivileged_user (1 row) -- 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] Boolean operators without commutators vs. ALL/ANY
On Jun13, 2011, at 05:12 , Robert Haas wrote: On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote: So I the end, I had to wrap the sub-query in a SQL-language function and use that in the check constraint. While this solved my immediate problem, the necessity of doing that highlights a few problems (A) ~ is an extremely bad name for the regexp-matching operators, since it's visual form is symmetric but it's behaviour isn't. This doesn't only make its usage very error-prone, it also makes it very hard to come up with sensible name for an commutator of ~. I suggest that we add =~ as an alias for ~, ~= as an commutator for =~, and deprecate ~. The same holds for ~~. Does any other database or programming language implement it this way? Ruby has =~, which returns the position of the regexp's first match, or nil if there is none. $ ruby -e puts 'hello' =~ /l+/ 2 $ ruby -e puts 'hello' =~ /x+/ nil (B) There should be a way to use ANY()/ALL() with the array elements becoming the left arguments of the operator. Ideally, we'd support ANY(array) operator value, but if that's not possible grammar-wise, I suggest we extend the OPERATOR() syntax to allow value OPERATOR(COMMUTATOR operator) ANY(array). OPERATOR(COMMUTATOR operator) would use the COMMUTATOR of the specified operator if one exists, and otherwise use the original operator with the arguments swapped. It seems to me that if we provided some way of handling this, your first proposal would be moot; and I have to say I like the idea of allowing this a lot more than tinkering with the operator names. Well, the issue of ~ being anti-self-explanatory remains independent from whether we do (B) or not. I'm not crazy about the proposed syntax, though; it seems cumbersome, and it's really only needed for SOME/ALL/ANY, not in general operator expressions. Since ANY is a reserved keyword, I believe we could allow something like expr op ANY BACKWARD ( ... ) -- or some other keyword in lieu of BACKWARD if you prefer. Hm, that's less bulky but more kludgy, I'd say. But wait a minute... If ANY and ALL are reserved anyway, should it be possible to make (ANY(..) op expr) and (ALL(...) op expr) work grammar-wise? (Note the enclosing parens) I just tried that, and it seems to work. bison doesn't report and conflicts, the regression tests still succeed, and I get the following postgres=# select (all(array[1,2]) = 1); ERROR: ANY()/ALL() op expr is not yet implemented at character 9 STATEMENT: select (all(array[1,2]) = 1); ERROR: ANY()/ALL() op expr is not yet implemented LINE 1: select (all(array[1,2]) = 1); ^ I've attached a patch with the changes to gram.y. best regards, Florian Pflug pg_anyall_reversed.v0.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] Boolean operators without commutators vs. ALL/ANY
On Sun, Jun 12, 2011 at 6:46 AM, Florian Pflug f...@phlo.org wrote: (B) There should be a way to use ANY()/ALL() with the array elements becoming the left arguments of the operator. FWIW, in case people were unaware, this is getting close to Perl 6 junctions/superpositions. See: http://dev.perl.org/perl6/doc/design/exe/E06.html The Wonderful World of Junctions. Beyond any and all they propose one and none. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PATCH: CreateComments: use explicit indexing for ``values''
Hello, I'm new to PostgreSQL and git, but having read through the wiki entries such as http://wiki.postgresql.org/wiki/Submitting_a_Patch, I think I have a patch worthy of submission. It's a readability improvement in src/backend/commands/comment.c (CreateComments function), which changes the existing code from incrementing a variable for use as the array index, to use explicit ``values'' instead. This has the following benefits 1) The structure of ``values'' is now clear at first glance. 2) ``i'' is then only used for 1 reason; the for loop The patch is based on master, and all existing tests pass. Regards Richardcommit fd4f57d8e67d723c071bdc374ede58d453c854ca (master) Author: Richard Hopkins richhguard-monot...@yahoo.co.uk Date: Sun Jun 12 12:03:28 2011 +0100 CreateComment: use explicit indexing for ``values'' This improves readability, and clarifies the structure of ``values''. No functionality has changed, and all existing tests pass. diff --git a/src/backend/commands/comment.c b/src/backend/commands/comment.c index d09bef0..20603dc 100644 --- a/src/backend/commands/comment.c +++ b/src/backend/commands/comment.c @@ -157,11 +157,10 @@ CreateComments(Oid oid, Oid classoid, int32 subid, char *comment) nulls[i] = false; replaces[i] = true; } - i = 0; - values[i++] = ObjectIdGetDatum(oid); - values[i++] = ObjectIdGetDatum(classoid); - values[i++] = Int32GetDatum(subid); - values[i++] = CStringGetTextDatum(comment); + values[0] = ObjectIdGetDatum(oid); + values[1] = ObjectIdGetDatum(classoid); + values[2] = Int32GetDatum(subid); + values[3] = CStringGetTextDatum(comment); } /* Use the index to search for a matching old tuple */ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Boolean operators without commutators vs. ALL/ANY
On Jun13, 2011, at 05:44 , Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote: (C) Why do we forbid sub-queries in CHECK constraints? Dunno. Maybe it's just an implementation restriction? (1) We don't want to invoke the planner in the places where we'd have to do so to make that work. Hm, OK, I get that. (2) It's just about inevitable that a sub-query would have results dependent on other rows beside the one being checked. As such, it would be trying to enforce semantics that you simply can't enforce via CHECK. (And yes, you can bypass that with a function, but guess what: it still won't actually work.) Yeah, I never expected non-immutable CHECK constraints to work. I was simply pointing out that UNNEST() allows one to write all kinds of interesting CHECK constraints, all of which are immutable. Now, if supporting these would increase code complexity, or cause a performance drop for non-sub-query CHECK constraints, I'm perfectly fine with leaving them unsupported. I just wanted to make sure we aren't simply nannying the user - especially since it's a nanny who's out-smarted by function calls. 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] DOMAINs and CASTs
On Mon, Jun 6, 2011 at 6:36 AM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-05-17 at 14:11 -0500, Jaime Casanova wrote: On Tue, May 17, 2011 at 12:19 PM, Robert Haas robertmh...@gmail.com wrote: The more controversial question is what to do if someone tries to create such a cast anyway. We could just ignore that as we do now, or we could throw a NOTICE, WARNING, or ERROR. IMHO, not being an error per se but an implementation limitation i would prefer to send a WARNING Implementation limitations are normally reported as errors. I don't see why it should be different here. ok, patch reports an error... do we want to backpatch this? if we want to do so maybe we can backpatch as a warning -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 03da168..a29c13c 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -1517,6 +1517,17 @@ CreateCast(CreateCastStmt *stmt) errmsg(target data type %s is a pseudo-type, TypeNameToString(stmt-targettype; + /* no domains allowd */ + if (sourcetyptype == TYPTYPE_DOMAIN) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), +errmsg(casts over domains are not implemented yet))); + + if (targettyptype == TYPTYPE_DOMAIN) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), +errmsg(casts over domains are not implemented yet))); + /* Permission check */ if (!pg_type_ownercheck(sourcetypeid, GetUserId()) !pg_type_ownercheck(targettypeid, GetUserId())) @@ -1672,11 +1683,13 @@ CreateCast(CreateCastStmt *stmt) * etc. would have to be modified to look through domains to their * base types. */ +#ifdef NOT_USED if (sourcetyptype == TYPTYPE_DOMAIN || targettyptype == TYPTYPE_DOMAIN) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg(domain data types must not be marked binary-compatible))); +#endif } /* -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench--new transaction type
On 13/06/11 06:38, Greg Smith wrote: On 06/11/2011 03:21 PM, Jeff Janes wrote: I wouldn't expect IPC chatter to show up in profiling, because it costs wall time, but not CPU time. The time spent might be attributed to the kernel, or to pgbench, or to nothing at all. Profilers aren't necessarily just accumulating raw CPU time though. If the approach includes sampling what code is active right now? periodically, you might be able to separate this out even though it's not using CPU time in the normal fashion. I think you might just need to use a better profiler. I got surprisingly insightful results in the past using http://poormansprofiler.org/ I never used it with Postgres, but it might be worth to try. J -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lazy vxid locks, v1
On 06/12/2011 11:39 PM, Robert Haas wrote: Here is a patch that applies over the reducing the overhead of frequent table locks (fastlock-v3) patch and allows heavyweight VXID locks to spring into existence only when someone wants to wait on them. I believe there is a large benefit to be had from this optimization, because the combination of these two patches virtually eliminates lock manager traffic on pgbench -S workloads. However, there are several flies in the ointment. 1. It's a bit of a kludge. I leave it to readers of the patch to determine exactly what about this patch they think is kludgey, but it's likely not the empty set. I suspect that MyProc-fpLWLock needs to be renamed to something a bit more generic if we're going to use it like this, but I don't immediately know what to call it. Also, the mechanism whereby we take SInvalWriteLock to work out the mapping from BackendId to PGPROC * is not exactly awesome. I don't think it matters from a performance point of view, because operations that need VXID locks are sufficiently rare that the additional lwlock traffic won't matter a bit. However, we could avoid this altogether if we rejiggered the mechanism for allocating PGPROCs and backend IDs. Right now, we allocate PGPROCs off of linked lists, except for auxiliary procs which allocate them by scanning a three-element array for an empty slot. Then, when the PGPROC subscribes to sinval, the sinval mechanism allocates a backend ID by scanning for the lowest unused backend ID in the ProcState array. If we changed the logic for allocating PGPROCs to mimic what the sinval queue currently does, then the backend ID could be defined as the offset into the PGPROC array. Translating between a backend ID and a PGPROC * now becomes a matter of pointer arithmetic. Not sure if this is worth doing. 2. Bad thing happen with large numbers of connections. This patch increases peak performance, but as you increase the number of concurrent connections beyond the number of CPU cores, performance drops off faster with the patch than without it. For example, on the 32-core loaner from Nate Boley, using 80 pgbench -S clients, unpatched HEAD runs at ~36K TPS; with fastlock, it jumps up to about ~99K TPS; with this patch also applied, it drops down to about ~64K TPS, despite the fact that nearly all the contention on the lock manager locks has been eliminated.On Stefan Kaltenbrunner's 40-core box, he was actually able to see performance drop down below unpatched HEAD with this applied! This is immensely counterintuitive. What is going on? just to add actual new numbers to the discussion(pgbench -n -S -T 120 -c X -j X) on that particular 40cores/80 threads box: unpatched: c1: tps = 7808.098053 (including connections establishing) c4: tps = 29941.444359 (including connections establishing) c8: tps = 58930.293850 (including connections establishing) c16:tps = 106911.385826 (including connections establishing) c24:tps = 117401.654430 (including connections establishing) c32:tps = 110659.627803 (including connections establishing) c40:tps = 107689.945323 (including connections establishing) c64:tps = 104835.182183 (including connections establishing) c80:tps = 101885.549081 (including connections establishing) c160: tps = 92373.395791 (including connections establishing) c200: tps = 90614.141246 (including connections establishing) fast locks: c1: tps = 7710.824723 (including connections establishing) c4: tps = 29653.578364 (including connections establishing) c8: tps = 58827.195578 (including connections establishing) c16:tps = 112814.382204 (including connections establishing) c24:tps = 154559.012960 (including connections establishing) c32:tps = 189281.391250 (including connections establishing) c40:tps = 215807.263233 (including connections establishing) c64:tps = 180644.527322 (including connections establishing) c80:tps = 118266.615543 (including connections establishing) c160: tps = 68957.22 (including connections establishing) c200: tps = 68803.801091 (including connections establishing) fast locks + lazy vxid: c1: tps = 7828.644389 (including connections establishing) c4: tps = 30520.558169 (including connections establishing) c8: tps = 60207.396385 (including connections establishing) c16:tps = 117923.775435 (including connections establishing) c24:tps = 158775.317590 (including connections establishing) c32:tps = 195768.530589 (including connections establishing) c40:tps = 223308.779212 (including connections establishing) c64:tps = 152848.742883 (including connections establishing) c80:tps = 65738.046558 (including connections establishing) c160: tps = 57075.304457 (including connections establishing) c200: tps = 59107.675182 (including connections establishing) so my reading of that is that we currently only scale well to ~12
Re: [HACKERS] [v9.1] sepgsql - userspace access vector cache
On Mon, Jun 13, 2011 at 7:51 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: For syscache, length of a typical security label in selinux is less than 64 bytes. If we assume an entry consume 128bytes including Oid pairs or pointers, its consumption is 128KBytes per 1,000 of tables or others. (Do we have a way to confirm syscache status?) I was thinking you might start a new session, SELECT pg_backendd_pid() to get the PID, use top/ps to get its memory usage; then do a bunch of stuff and see how much it's grown. The difference between how much it grows with and without the patch is the amount of additional memory the patch consumes. -- 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] PATCH: CreateComments: use explicit indexing for ``values''
On Sun, Jun 12, 2011 at 7:26 AM, richhguard-monot...@yahoo.co.uk wrote: Hello, I'm new to PostgreSQL and git, but having read through the wiki entries such as http://wiki.postgresql.org/wiki/Submitting_a_Patch, I think I have a patch worthy of submission. It's a readability improvement in src/backend/commands/comment.c (CreateComments function), which changes the existing code from incrementing a variable for use as the array index, to use explicit ``values'' instead. This has the following benefits 1) The structure of ``values'' is now clear at first glance. 2) ``i'' is then only used for 1 reason; the for loop The patch is based on master, and all existing tests pass. Wow. That code is pretty ugly, all right. I think, though, that we probably ought to be using the Apg_description_columnname constants instead of writing 0-3. Care to update the patch? -- 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] Make relation_openrv atomic wrt DDL
On Mon, Jun 13, 2011 at 1:12 AM, Noah Misch n...@leadboat.com wrote: That might be a start, but it's not a complete replacement for the global counter. AcceptInvalidationMessages() is actually called in LockRelationOid(), but the comparison needs to happen a level up in RangeVarLockRelid(). So, we would be adding encapsulation in one place to lose it in another. Also, in the uncontended case, the patch only calls AcceptInvalidationMessages() once per relation_openrv. It compares the counter after that call with a counter as the last caller left it -- RangeVarLockRelid() doesn't care who that caller was. Hmm, OK. Taking that a bit further, what if we put that counter in shared-memory? After writing new messages into the queue, a writer would bump this count (only one process can be doing this at a time because SInvalWriteLock is held) and memory-fence. Readers would memory-fence and then read the count before acquiring the lock. If it hasn't changed since we last read it, then don't bother acquiring SInvalReadLock, because no new messages have arrived. Or maybe an exact multiple of 2^32 messages have arrived, but there's probably someway to finesse around that issue, like maybe also using some kind of memory barrier to allow resetState to be checked without the lock. This probably would not replace a backend-local counter of processed messages for RangeVarLockRelid()'s purposes. It's quite possibly a good way to reduce SInvalReadLock traffic, though. Exact multiples of 2^32 messages need not be a problem, because the queue is limited to MAXNUMMESSAGES (4096, currently). I think you will need to pack into one 32-bit value all data each backend needs to decide whether to proceed with the full process. Given that queue offsets fit into 13 bits (easily reduced to 12) and resetState is a bit, that seems practical enough at first glance. I was imagining one shared global counter, not one per backend, and thinking that each backend could do something like: volatile uint32 *the_global_counter = global_counter; uint32 latest_counter; mfence(); latest_counter = *the_global_counter; if (latest_counter != previous_value_of_global_counter || myprocstate-isReset) really_do_it(); previous_value_of_global_counter = latest_counter; I'm not immediately seeing why that wouldn't work for your purposes as well. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wrong message on REASSIGN OWNED
On Mon, Jun 13, 2011 at 2:41 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Sat, Jun 11, 2011 at 8:01 PM, Robert Haas robertmh...@gmail.com wrote: can't reassign objects owned by %s because this user is internal to the database system ? that message is not that clear... it seems to imply i can't reassign any object from that user... cannot change owner of some objects owned by %s because they are required by the database system? btw, i'm allowed to use ALTER TABLE to assign a new owner (even an unprivileged one) to a system catalog, probably that's a bug. +1 for tightening that up in 9.2. -- 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] lazy vxid locks, v1
Stefan Kaltenbrunner wrote: on that particular 40cores/80 threads box: unpatched: c40:tps = 107689.945323 (including connections establishing) c80:tps = 101885.549081 (including connections establishing) fast locks: c40:tps = 215807.263233 (including connections establishing) c80:tps = 118266.615543 (including connections establishing) fast locks + lazy vxid: c40:tps = 223308.779212 (including connections establishing) c80:tps = 65738.046558 (including connections establishing) Is there any way to disable the HT (or whatever technology attempts to make each core look like 2)? In my benchmarking that has kept performance from tanking as badly when a large number of processes are contending for CPU. -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] FOREIGN TABLE doc fix
2011/6/13 Shigeru Hanada shigeru.han...@gmail.com: Thanks for the review. (2011/06/12 13:21), Robert Haas wrote: 2011/6/9 Shigeru Hanadahan...@metrosystems.co.jp: Attached patch includes fixes for FOREIGN TABLE documents: I committed the changes to ALTER FOREIGN TABLE, but I think the changes to CREATE FOREIGN TABLE need more thought. The first of the two hunks you've proposed to add doesn't seem necessary to me, and the second one seems like it belongs in a chapter on how to write a foreign data wrapper correctly, rather than here. Agreed. How about the section for IterateForeignScan() in 50.1. Foreign Data Wrapper Callback Routines[1] for the second hunk? It seems proper place to describe responsibility about applying NOT NULL constraint, because it would be where the author works for the issue. The section also mentions responsibility of column signature matching. By the way, I found another document issue. 5.10. Foreign Data[2] says that FDW for PG is available alike FDW for files, but postgresql_fdw won't be available for 9.1 release, at least as a bundled extension. ISTM that such mention should be removed to avoid misunderstanding. Please find attached the revised patch. Committed, with some additional word-smithing. Incidentally, are you planning to revive the PostgreSQL FDW for 9.2? That would be a killer feature. -- 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] lazy vxid locks, v1
On 06/13/2011 02:29 PM, Kevin Grittner wrote: Stefan Kaltenbrunner wrote: on that particular 40cores/80 threads box: unpatched: c40:tps = 107689.945323 (including connections establishing) c80:tps = 101885.549081 (including connections establishing) fast locks: c40:tps = 215807.263233 (including connections establishing) c80:tps = 118266.615543 (including connections establishing) fast locks + lazy vxid: c40:tps = 223308.779212 (including connections establishing) c80:tps = 65738.046558 (including connections establishing) Is there any way to disable the HT (or whatever technology attempts to make each core look like 2)? In my benchmarking that has kept performance from tanking as badly when a large number of processes are contending for CPU. I can do that tomorrow, but I have now done a fair amount of benchmarking on that box using various tests and for CPU intense workloads(various math stuff, parallel compiles of the linux kernel, some inhouse stuff, and some other database) I usually get a 60-70x speedup over just using a single core and most recent CPUs (this one is actually a brand new Westmere-EX) showed pretty good scaling with HT/threading. I'm actually pretty sure that at leas in some benchmarks it was not HT that was the real problem but rather our general inability to scale much beyond 10-12 cores for reads and even worse for writes (due to WAL contention). Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas robertmh...@gmail.com wrote: Incidentally, are you planning to revive the PostgreSQL FDW for 9.2? That would be a killer feature. Even more killer would be that it could be built/packaged as an extension, and use for 9.1 too ;-) a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 9:03 AM, Aidan Van Dyk ai...@highrise.ca wrote: On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas robertmh...@gmail.com wrote: Incidentally, are you planning to revive the PostgreSQL FDW for 9.2? That would be a killer feature. Even more killer would be that it could be built/packaged as an extension, and use for 9.1 too ;-) +1! -- 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] DOMAINs and CASTs
On Mon, Jun 13, 2011 at 4:39 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Mon, Jun 6, 2011 at 6:36 AM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-05-17 at 14:11 -0500, Jaime Casanova wrote: On Tue, May 17, 2011 at 12:19 PM, Robert Haas robertmh...@gmail.com wrote: The more controversial question is what to do if someone tries to create such a cast anyway. We could just ignore that as we do now, or we could throw a NOTICE, WARNING, or ERROR. IMHO, not being an error per se but an implementation limitation i would prefer to send a WARNING Implementation limitations are normally reported as errors. I don't see why it should be different here. ok, patch reports an error... do we want to backpatch this? if we want to do so maybe we can backpatch as a warning I'm not even really sure I want an ERROR anywhere. If it weren't something we have accepted previously, I'd be all in favor, but I'm unconvinced it's worth breaking people's dumps over this. As far as the back-branches go, I'd be inclined to back-patch only a doc fix. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Boolean operators without commutators vs. ALL/ANY
Robert Haas robertmh...@gmail.com writes: On Sun, Jun 12, 2011 at 11:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: There are syntactic reasons not to do that. It'd be a lot easier just to provide a commutator operator for ~. Details? Well, for one, it becomes unobvious what A op ANY (B) op C means. This has come up before, and I seem to recall that we identified some more-compelling problems, but that's the best I can do before consuming any caffeine. In any case, if you dig around enough to notice all the infrastructure that's involved with this, you'll definitely come to the conclusion that it'd be a lot less work to just add the missing commutator operators. 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] lazy vxid locks, v1
On 06/12/2011 11:39 PM, Robert Haas wrote: Here is a patch that applies over the reducing the overhead of frequent table locks (fastlock-v3) patch and allows heavyweight VXID locks to spring into existence only when someone wants to wait on them. I believe there is a large benefit to be had from this optimization, because the combination of these two patches virtually eliminates lock manager traffic on pgbench -S workloads. However, there are several flies in the ointment. 1. It's a bit of a kludge. I leave it to readers of the patch to determine exactly what about this patch they think is kludgey, but it's likely not the empty set. I suspect that MyProc-fpLWLock needs to be renamed to something a bit more generic if we're going to use it like this, but I don't immediately know what to call it. Also, the mechanism whereby we take SInvalWriteLock to work out the mapping from BackendId to PGPROC * is not exactly awesome. I don't think it matters from a performance point of view, because operations that need VXID locks are sufficiently rare that the additional lwlock traffic won't matter a bit. However, we could avoid this altogether if we rejiggered the mechanism for allocating PGPROCs and backend IDs. Right now, we allocate PGPROCs off of linked lists, except for auxiliary procs which allocate them by scanning a three-element array for an empty slot. Then, when the PGPROC subscribes to sinval, the sinval mechanism allocates a backend ID by scanning for the lowest unused backend ID in the ProcState array. If we changed the logic for allocating PGPROCs to mimic what the sinval queue currently does, then the backend ID could be defined as the offset into the PGPROC array. Translating between a backend ID and a PGPROC * now becomes a matter of pointer arithmetic. Not sure if this is worth doing. 2. Bad thing happen with large numbers of connections. This patch increases peak performance, but as you increase the number of concurrent connections beyond the number of CPU cores, performance drops off faster with the patch than without it. For example, on the 32-core loaner from Nate Boley, using 80 pgbench -S clients, unpatched HEAD runs at ~36K TPS; with fastlock, it jumps up to about ~99K TPS; with this patch also applied, it drops down to about ~64K TPS, despite the fact that nearly all the contention on the lock manager locks has been eliminated.On Stefan Kaltenbrunner's 40-core box, he was actually able to see performance drop down below unpatched HEAD with this applied! This is immensely counterintuitive. What is going on? Profiling reveals that the system spends enormous amounts of CPU time in s_lock. just to reiterate that with numbers - at 160 threads with both patches applied the profile looks like: samples %image name symbol name 828794 75.8662 postgres s_lock 51672 4.7300 postgres LWLockAcquire 51145 4.6817 postgres LWLockRelease 17636 1.6144 postgres GetSnapshotData 7521 0.6885 postgres hash_search_with_hash_value 6193 0.5669 postgres AllocSetAlloc 4527 0.4144 postgres SearchCatCache 4521 0.4138 postgres PinBuffer 3385 0.3099 postgres SIGetDataEntries 3160 0.2893 postgres PostgresMain 2706 0.2477 postgres _bt_compare 2687 0.2460 postgres fmgr_info_cxt_security 1963 0.1797 postgres UnpinBuffer 1846 0.1690 postgres LockAcquireExtended 1770 0.1620 postgres exec_bind_message 1730 0.1584 postgres hash_any 1644 0.1505 postgres ExecInitExpr even at the peak performance spot of the combined patch-set (-c40) the contention is noticable in the profile: samples %image name symbol name 1497826 22.0231 postgres s_lock 5921048.7059 postgres LWLockAcquire 5122137.5313 postgres LWLockRelease 2300503.3825 postgres GetSnapshotData 1762522.5915 postgres AllocSetAlloc 1551222.2808 postgres hash_search_with_hash_value 1162351.7091 postgres SearchCatCache 1101971.6203 postgres _bt_compare 94101 1.3836 postgres PinBuffer 80119 1.1780 postgres PostgresMain 65584 0.9643 postgres fmgr_info_cxt_security 55198 0.8116 postgres hash_any 52872 0.7774 postgres exec_bind_message 48438 0.7122 postgres LockReleaseAll 46631 0.6856 postgres MemoryContextAlloc 45909 0.6750 postgres ExecInitExpr 42293 0.6219 postgres
Re: [HACKERS] wrong message on REASSIGN OWNED
Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 2:41 AM, Jaime Casanova ja...@2ndquadrant.com wrote: btw, i'm allowed to use ALTER TABLE to assign a new owner (even an unprivileged one) to a system catalog, probably that's a bug. +1 for tightening that up in 9.2. Nonsense. You won't like the results of DELETE FROM pg_proc; either, but we don't try to put training wheels on superusers. 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] wrong message on REASSIGN OWNED
Jaime Casanova ja...@2ndquadrant.com writes: On Sat, Jun 11, 2011 at 8:01 PM, Robert Haas robertmh...@gmail.com wrote: can't reassign objects owned by %s because this user is internal to the database system ? that message is not that clear... it seems to imply i can't reassign any object from that user... I think s/drop/reassign ownership of/ is sufficient. There's no reason to change the rest of the message, especially because the same phraseology is in use elsewhere. 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] wrong message on REASSIGN OWNED
On Mon, Jun 13, 2011 at 9:55 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 2:41 AM, Jaime Casanova ja...@2ndquadrant.com wrote: btw, i'm allowed to use ALTER TABLE to assign a new owner (even an unprivileged one) to a system catalog, probably that's a bug. +1 for tightening that up in 9.2. Nonsense. You won't like the results of DELETE FROM pg_proc; either, but we don't try to put training wheels on superusers. allow_system_table_mods? -- 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
pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On 06/13/2011 01:55 PM, Stefan Kaltenbrunner wrote: [...] all those tests are done with pgbench running on the same box - which has a noticable impact on the results because pgbench is using ~1 core per 8 cores of the backend tested in cpu resoures - though I don't think it causes any changes in the results that would show the performance behaviour in a different light. actuall testing against sysbench with the very same workload shows the following performance behaviour: with 40 threads(aka the peak performance point): pgbench:223308 tps sysbench: 311584 tps with 160 threads (backend contention dominated): pgbench:57075 sysbench: 43437 so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. for those curious - the profile for pgbench looks like: samples %symbol name 2937841.9087 doCustom 1750224.9672 threadRun 7629 10.8830 pg_strcasecmp 5871 8.3752 compareVariables 2568 3.6633 getVariable 2167 3.0913 putVariable 2065 2.9458 replaceVariable 1971 2.8117 parseVariable 534 0.7618 xstrdup 278 0.3966 xrealloc 137 0.1954 xmalloc Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: CreateComments: use explicit indexing for ``values''
Robert Haas robertmh...@gmail.com writes: On Sun, Jun 12, 2011 at 7:26 AM, richhguard-monot...@yahoo.co.uk wrote: It's a readability improvement in src/backend/commands/comment.c (CreateComments function), which changes the existing code from incrementing a variable for use as the array index, to use explicit ``values'' instead. Wow. That code is pretty ugly, all right. I think, though, that we probably ought to be using the Apg_description_columnname constants instead of writing 0-3. Care to update the patch? Historically this i++ approach has been used in a lot of places that fill in system catalog tuples. We've fixed some of them over time, but I doubt this is the only one remaining. If we're going to try to remove it here, maybe we ought to try to fix them all rather than just this one. I agree that the main point of doing so would be to introduce the greppable Apg_xxx constants, and so just using hard-coded integers is not much of an improvement. 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] Boolean operators without commutators vs. ALL/ANY
On Mon, Jun 13, 2011 at 3:01 AM, Florian Pflug f...@phlo.org wrote: On Jun13, 2011, at 05:12 , Robert Haas wrote: On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug f...@phlo.org wrote: So I the end, I had to wrap the sub-query in a SQL-language function and use that in the check constraint. While this solved my immediate problem, the necessity of doing that highlights a few problems (A) ~ is an extremely bad name for the regexp-matching operators, since it's visual form is symmetric but it's behaviour isn't. This doesn't only make its usage very error-prone, it also makes it very hard to come up with sensible name for an commutator of ~. I suggest that we add =~ as an alias for ~, ~= as an commutator for =~, and deprecate ~. The same holds for ~~. Does any other database or programming language implement it this way? Ruby has =~, which returns the position of the regexp's first match, or nil if there is none. $ ruby -e puts 'hello' =~ /l+/ 2 $ ruby -e puts 'hello' =~ /x+/ nil Sure. Some languages use =~ and some use just ~... I was just wondering if anyone thought the commutator of =~ was ~=... -- 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] wrong message on REASSIGN OWNED
On 06/13/2011 09:55 AM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 2:41 AM, Jaime Casanovaja...@2ndquadrant.com wrote: btw, i'm allowed to use ALTER TABLE to assign a new owner (even an unprivileged one) to a system catalog, probably that's a bug. +1 for tightening that up in 9.2. Nonsense. You won't like the results of DELETE FROM pg_proc; either, but we don't try to put training wheels on superusers. Yeah. Amusing anecdote along these lines: I had fun recently recovering the situation for someone who had for reasons I can't even imagine done delete from pg_database; on their production system. 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] Boolean operators without commutators vs. ALL/ANY
On 06/13/2011 10:07 AM, Robert Haas wrote: Some languages use =~ and some use just ~... I was just wondering if anyone thought the commutator of =~ was ~=... My feeling is it's a bit dangerous. It's too easy to fat-finger the reverse op, and get something quite unintended. cheers andrew (whose lack of coordination sadly increases with age) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 9:03 AM, Aidan Van Dyk ai...@highrise.ca wrote: On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas robertmh...@gmail.com wrote: Incidentally, are you planning to revive the PostgreSQL FDW for 9.2? That would be a killer feature. Even more killer would be that it could be built/packaged as an extension, and use for 9.1 too ;-) +1! Don't hold your breath. We'll probably be making enough changes in the FDW infrastructure (particularly planner support) that making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. 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] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 3:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 9:03 AM, Aidan Van Dyk ai...@highrise.ca wrote: On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas robertmh...@gmail.com wrote: Incidentally, are you planning to revive the PostgreSQL FDW for 9.2? That would be a killer feature. Even more killer would be that it could be built/packaged as an extension, and use for 9.1 too ;-) +1! Don't hold your breath. We'll probably be making enough changes in the FDW infrastructure (particularly planner support) that making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. Oh joy. There's a GSoC student working on 2 non-trivial FDW's right now, and I have a couple I've been working on. If we're going to make the API incompatible to that extent, we might as well not bother :-( -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake 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] lazy vxid locks, v1
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: On 06/12/2011 11:39 PM, Robert Haas wrote: Profiling reveals that the system spends enormous amounts of CPU time in s_lock. just to reiterate that with numbers - at 160 threads with both patches applied the profile looks like: samples %image name symbol name 828794 75.8662 postgres s_lock Do you know exactly which spinlocks are being contended on here? The next few entries 51672 4.7300 postgres LWLockAcquire 51145 4.6817 postgres LWLockRelease 17636 1.6144 postgres GetSnapshotData suggest that it might be the ProcArrayLock as a result of a huge amount of snapshot-fetching, but this is very weak evidence for that theory. 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] FOREIGN TABLE doc fix
Dave Page dp...@pgadmin.org writes: On Mon, Jun 13, 2011 at 3:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Don't hold your breath. We'll probably be making enough changes in the FDW infrastructure (particularly planner support) that making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. Oh joy. There's a GSoC student working on 2 non-trivial FDW's right now, and I have a couple I've been working on. If we're going to make the API incompatible to that extent, we might as well not bother :-( Oh, that's by no means a waste of time --- we need some examples to help us figure out where the pain points are. I'm just saying that the best ways to do things will probably change quite a bit as we introduce solutions for the pain points. And I don't intend to be too concerned about preserving backwards compatibility at this stage. 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] wrong message on REASSIGN OWNED
On Mon, Jun 13, 2011 at 8:55 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 2:41 AM, Jaime Casanova ja...@2ndquadrant.com wrote: btw, i'm allowed to use ALTER TABLE to assign a new owner (even an unprivileged one) to a system catalog, probably that's a bug. +1 for tightening that up in 9.2. Nonsense. You won't like the results of DELETE FROM pg_proc; either, but we don't try to put training wheels on superusers. really? i thought we do when system catalogs are involved based on this example: db=# alter table pg_largeobject set tablespace tblspc_prueba; ERROR: permission denied: pg_largeobject is a system catalog -- 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] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 3:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: On Mon, Jun 13, 2011 at 3:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Don't hold your breath. We'll probably be making enough changes in the FDW infrastructure (particularly planner support) that making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. Oh joy. There's a GSoC student working on 2 non-trivial FDW's right now, and I have a couple I've been working on. If we're going to make the API incompatible to that extent, we might as well not bother :-( Oh, that's by no means a waste of time --- we need some examples to help us figure out where the pain points are. I'm just saying that the best ways to do things will probably change quite a bit as we introduce solutions for the pain points. And I don't intend to be too concerned about preserving backwards compatibility at this stage. No problem with providing feedback on pain points, however we're trying to write production quality code that can be used by people sooner rather than later, in my case, in my own time. If^WNow I know I'm likely to have to rewrite it for 9.2, it's significantly harder to find any kind of enthusiasm to work on it for 9.1. I think we need to figure out a way to maintain a certain level of backwards compatibility that isn't going to require massive rewrites, or people just won't bother with SQL/MED until they know the API is stable. I know I realised it would change, but I assumed we would either add new optional function calls, or implement a v2 interface whilst continuing to support the v1 interface. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake 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] lazy vxid locks, v1
On Mon, Jun 13, 2011 at 10:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: On 06/12/2011 11:39 PM, Robert Haas wrote: Profiling reveals that the system spends enormous amounts of CPU time in s_lock. just to reiterate that with numbers - at 160 threads with both patches applied the profile looks like: samples % image name symbol name 828794 75.8662 postgres s_lock Do you know exactly which spinlocks are being contended on here? The next few entries 51672 4.7300 postgres LWLockAcquire 51145 4.6817 postgres LWLockRelease 17636 1.6144 postgres GetSnapshotData suggest that it might be the ProcArrayLock as a result of a huge amount of snapshot-fetching, but this is very weak evidence for that theory. I don't know for sure what is happening on Stefan's system, but I did post the results of some research on this exact topic in my original post. -- 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] PATCH: CreateComments: use explicit indexing for ``values''
I wrote: Historically this i++ approach has been used in a lot of places that fill in system catalog tuples. We've fixed some of them over time, but I doubt this is the only one remaining. If we're going to try to remove it here, maybe we ought to try to fix them all rather than just this one. A quick grep reveals that the places that still do it that way are OperatorShellMake OperatorCreate TypeShellMake TypeCreate update_attstats (though this one might be hard to improve) CreateComments CreateSharedComments InsertRule Of these, all but the two in comment.c follow the convention of mentioning the assigned-to column in a comment, so that the code is at least somewhat greppable. So those two definitely need improvement, but should we consider changing the others while at it? BTW, there are some contrib modules with functions-returning-record that fill in result tuples this way as well. But we don't have symbolic constants for the column numbers there, and it's probably not worth introducing such. 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] On-the-fly index tuple deletion vs. hot_standby
On Mon, Jun 13, 2011 at 3:11 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 12, 2011 at 3:01 PM, Noah Misch n...@leadboat.com wrote: I fully agree. That said, if this works on the standby, we may as well also use it opportunistically on the master, to throttle bloat. As long as the performance cost is de minimis, I agree. At any rate, if taking a cleanup lock on the right-linked page on the standby is sufficient to fix the problem, that seems like a far superior solution in any case. Presumably the frequency of someone having a pin on that particular page will be far lower than any matching based on XID or heavyweight locks. And the vast majority of such pins should disappear before the startup process feels obliged to get out its big hammer. Yep; looks promising. Does such a thing have a chance of being backpatchable? I think the chances start slim and fall almost to zero on account of the difficulty of avoiding a WAL format change. I can't see back-patching it. Maybe people would feel it was worth considering if we were getting legions of complaints about this problem, but so far you're the only one. In any case, back-patching a WAL format change is a complete non-starter -- we can't go making minor versions non-interoperable. Assuming that conclusion, I do think it's worth starting with something simple, even if it means additional bloat on the master in the wal_level=hot_standby + vacuum_defer_cleanup_age / hot_standby_feedback case. In choosing those settings, the administrator has taken constructive steps to accept master-side bloat in exchange for delaying recovery conflict. What's your opinion? I'm pretty disinclined to go tinkering with 9.1 at this point, too. Not least because a feature already exists in 9.1 to cope with this problem: hot standby feedback. -- 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] procpid?
On Jun 11, 2011, at 9:36 PM, Robert Haas wrote: This is at least a use-case for something^Wfeature like 'create synonym', allowing smooth end-user's application upgrade on schema update. I am not claiming that we need that, it just seems a good usecase for column alias/synonym. I had the same thought. I'm not sure that this particular example would be worthwhile even if we had a column synonym facility. But at least if we were bent on changing it we could do it without breaking things. A synonym feature would definitely be useful for cases like this. We have a poorly named database at work; it's been that way for years and the only reason it's never been cleaned up is because it would require simultaneously changing config settings in dozens of places on hundreds of machines (many of which are user machines, which makes performing the change very difficult). As annoying as dealing with the oddball name is (there's a number of pieces of code that have to special case it), it would be even more painful to fix the problem. If we had database name synonyms we could create a synonym and migrate everything over time... and in the meantime, code could stop special-casing it. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 10:20 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 9:03 AM, Aidan Van Dyk ai...@highrise.ca wrote: On Mon, Jun 13, 2011 at 12:30 PM, Robert Haas robertmh...@gmail.com wrote: Incidentally, are you planning to revive the PostgreSQL FDW for 9.2? That would be a killer feature. Even more killer would be that it could be built/packaged as an extension, and use for 9.1 too ;-) +1! Don't hold your breath. We'll probably be making enough changes in the FDW infrastructure (particularly planner support) that making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. Well, so far the people who seem willing to work on such changes are not exactly thick on the ground, so I think it might be a little premature to speculate about what changes they might make when they show up. -- 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] procpid?
On Mon, Jun 13, 2011 at 11:20 AM, Jim Nasby j...@nasby.net wrote: On Jun 11, 2011, at 9:36 PM, Robert Haas wrote: This is at least a use-case for something^Wfeature like 'create synonym', allowing smooth end-user's application upgrade on schema update. I am not claiming that we need that, it just seems a good usecase for column alias/synonym. I had the same thought. I'm not sure that this particular example would be worthwhile even if we had a column synonym facility. But at least if we were bent on changing it we could do it without breaking things. A synonym feature would definitely be useful for cases like this. We have a poorly named database at work; it's been that way for years and the only reason it's never been cleaned up is because it would require simultaneously changing config settings in dozens of places on hundreds of machines (many of which are user machines, which makes performing the change very difficult). As annoying as dealing with the oddball name is (there's a number of pieces of code that have to special case it), it would be even more painful to fix the problem. If we had database name synonyms we could create a synonym and migrate everything over time... and in the meantime, code could stop special-casing it. That's probably the best explanation of why synonyms would be useful I believe I've yet heard. -- 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] FOREIGN TABLE doc fix
Dave Page dp...@pgadmin.org writes: On Mon, Jun 13, 2011 at 3:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: Oh, that's by no means a waste of time --- we need some examples to help us figure out where the pain points are. I'm just saying that the best ways to do things will probably change quite a bit as we introduce solutions for the pain points. And I don't intend to be too concerned about preserving backwards compatibility at this stage. No problem with providing feedback on pain points, however we're trying to write production quality code that can be used by people sooner rather than later, in my case, in my own time. If^WNow I know I'm likely to have to rewrite it for 9.2, it's significantly harder to find any kind of enthusiasm to work on it for 9.1. I think we need to figure out a way to maintain a certain level of backwards compatibility that isn't going to require massive rewrites, or people just won't bother with SQL/MED until they know the API is stable. I know I realised it would change, but I assumed we would either add new optional function calls, or implement a v2 interface whilst continuing to support the v1 interface. The problem here is not so much that we're going to change APIs as that we don't *have* APIs, in the sense of something we're committing to not changing. Until we do, you're pretty much coding on sand. I don't intend to be bound by some concept of we can't change the planner because somebody somewhere might be depending on XYZ in their first-generation FDW. If you're not willing to adapt, then yes, you should not be writing FDWs yet. Depending on what your idea of stable is, maybe you shouldn't be writing FDWs ever. Of course, how much you're affected by all this depends on what you're doing. Something like file_fdw doesn't really have any intelligent planning to do, so it's likely that it wouldn't get broken too badly by any changes in the near future --- though I'd still hope that eventually we have some cleaner APIs for it to call than what you see in its estimate_costs() today. But a postgresql FDW will need to get pretty darn intimate with the planner in order to be any good, and I absolutely will *not* make any promises that code like that will continue to work unchanged in future versions. To do so would be tantamount to decreeing that all progress on the planner stopped dead yesterday. 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] DOMAINs and CASTs
On Mon, Jun 13, 2011 at 03:39:39AM -0500, Jaime Casanova wrote: On Mon, Jun 6, 2011 at 6:36 AM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-05-17 at 14:11 -0500, Jaime Casanova wrote: On Tue, May 17, 2011 at 12:19 PM, Robert Haas robertmh...@gmail.com wrote: The more controversial question is what to do if someone tries to create such a cast anyway. We could just ignore that as we do now, or we could throw a NOTICE, WARNING, or ERROR. IMHO, not being an error per se but an implementation limitation i would prefer to send a WARNING Implementation limitations are normally reported as errors. I don't see why it should be different here. ok, patch reports an error... do we want to backpatch this? if we want to do so maybe we can backpatch as a warning Minor clarification attached. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 03da168..a29c13c 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -1517,6 +1517,17 @@ CreateCast(CreateCastStmt *stmt) errmsg(target data type %s is a pseudo-type, TypeNameToString(stmt-targettype; + /* no domains allowd */ + if (sourcetyptype == TYPTYPE_DOMAIN) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), +errmsg(casts from domains are not implemented yet))); + + if (targettyptype == TYPTYPE_DOMAIN) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), +errmsg(casts to domains are not implemented yet))); + /* Permission check */ if (!pg_type_ownercheck(sourcetypeid, GetUserId()) !pg_type_ownercheck(targettypeid, GetUserId())) @@ -1672,11 +1683,13 @@ CreateCast(CreateCastStmt *stmt) * etc. would have to be modified to look through domains to their * base types. */ +#ifdef NOT_USED if (sourcetyptype == TYPTYPE_DOMAIN || targettyptype == TYPTYPE_DOMAIN) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), errmsg(domain data types must not be marked binary-compatible))); +#endif } /* -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wrong message on REASSIGN OWNED
Excerpts from Robert Haas's message of sáb jun 11 21:01:55 -0400 2011: On Thu, Jun 9, 2011 at 1:26 PM, Jaime Casanova ja...@2ndquadrant.com wrote: on shdepReassignOwned() we have this message, which is obviously wrong we are not dropping objects just reassigning them... ereport(ERROR, (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST), errmsg(cannot drop objects owned by %s because they are required by the database system, getObjectDescription(obj; Oh my. I introduced this mistake 6 years ago and nobody noticed in all this time. I guess this is not a very frequently used feature. but haven't thought of a good way of rephrase it can't reassign objects owned by %s because this user is internal to the database system ? Thanks, I'll use that. -- Á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] FOREIGN TABLE doc fix
On 06/13/2011 10:25 AM, Dave Page wrote: Don't hold your breath. We'll probably be making enough changes in the FDW infrastructure (particularly planner support) that making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. Oh joy. There's a GSoC student working on 2 non-trivial FDW's right now, and I have a couple I've been working on. If we're going to make the API incompatible to that extent, we might as well not bother :-( If nobody bothers then there won't be any experience on which to base a stable API. In particular, I think it's crucial that we get working FDWs for MySQL, SQLServer and Oracle ASAP. 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] procpid?
On Jun 13, 2011, at 10:22 AM, Robert Haas wrote: A synonym feature would definitely be useful for cases like this. We have a poorly named database at work; it's been that way for years and the only reason it's never been cleaned up is because it would require simultaneously changing config settings in dozens of places on hundreds of machines (many of which are user machines, which makes performing the change very difficult). As annoying as dealing with the oddball name is (there's a number of pieces of code that have to special case it), it would be even more painful to fix the problem. If we had database name synonyms we could create a synonym and migrate everything over time... and in the meantime, code could stop special-casing it. That's probably the best explanation of why synonyms would be useful I believe I've yet heard. FWIW, I've asked Command Prompt to look into creating database name synonyms for us, but perhaps there are other synonyms that would make sense? I can't really think of any other cases where you care about name and don't have a way to work around it (ie: column and tables can be done with views; you can grant a role to another role; you can create a wrapper function). -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] On-the-fly index tuple deletion vs. hot_standby
On Thu, Jun 9, 2011 at 10:38 PM, Noah Misch n...@leadboat.com wrote: On Fri, Apr 22, 2011 at 11:10:34AM -0400, Noah Misch wrote: On Tue, Mar 15, 2011 at 10:22:59PM -0400, Noah Misch wrote: On Mon, Mar 14, 2011 at 01:56:22PM +0200, Heikki Linnakangas wrote: On 12.03.2011 12:40, Noah Misch wrote: The installation that inspired my original report recently upgraded from 9.0.1 to 9.0.3, and your fix did significantly decrease its conflict frequency. The last several conflicts I have captured involve XLOG_BTREE_REUSE_PAGE records. (FWIW, the index has generally been pg_attribute_relid_attnam_index.) I've attached a test script demonstrating the behavior. _bt_page_recyclable approves any page deleted no more recently than RecentXmin, because we need only ensure that every ongoing scan has witnessed the page as dead. For the hot standby case, we need to account for possibly-ongoing standby transactions. Using RecentGlobalXmin covers that, albeit with some pessimism: we really only need LEAST(RecentXmin, PGPROC-xmin of walsender_1, .., PGPROC-xmin of walsender_N) - vacuum_defer_cleanup_age. Not sure the accounting to achieve that would pay off, though. Thoughts? Hmm, instead of bloating the master, I wonder if we could detect more accurately if there are any on-going scans, in the standby. For example, you must hold a lock on the index to scan it, so only transactions holding the lock need to be checked for conflict. That would be nice. Do you have an outline of an implementation in mind? In an attempt to resuscitate this thread, here's my own shot at that. Apologies in advance if it's just an already-burning straw man. [full proposal at http://archives.postgresql.org/message-id/20110422151034.ga8...@tornado.gateway.2wire.net] Anyone care to comment? On this system, which has vacuum_defer_cleanup_age set to 3 peak hours worth of xid consumption, the problem caps recovery conflict hold off at 10-20 minutes. It will have the same effect on standby feedback in 9.1. I think we should start by using RecentGlobalXmin instead of RecentXmin as the reuse horizon when wal_level = hot_standby, and backpatch that. Then, independently consider for master a bloat-avoidance improvement like I outlined most recently; I'm not sure whether that's worth it. In any event, I'm hoping to get some consensus on the way forward. I like your ideas. (Also, I note that using xids in this way unnecessarily keeps bloat around for a long time, if we have periods of mostly read-only activity. Interesting point.) I think we would only get away with this approach on leaf pages of the index. It doesn't seem worth trying for the locks if we were higher up. On the standby side, its possible this could generate additional buffer pin deadlocks and/or contention. So I would also want to look at some deferral mechanism, so that we can mark the block removed, but not actually do so until some time later, or we really need to, for example when we write new data to that page. Got time for a patch in this coming CF? -- 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] Boolean operators without commutators vs. ALL/ANY
On Mon, Jun 13, 2011 at 09:01:45AM +0200, Florian Pflug wrote: Hm, that's less bulky but more kludgy, I'd say. But wait a minute... If ANY and ALL are reserved anyway, should it be possible to make (ANY(..) op expr) and (ALL(...) op expr) work grammar-wise? (Note the enclosing parens) This would be a very, very useful feature. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 4:38 PM, Andrew Dunstan and...@dunslane.net wrote: On 06/13/2011 10:25 AM, Dave Page wrote: Don't hold your breath. We'll probably be making enough changes in the FDW infrastructure (particularly planner support) that making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. Oh joy. There's a GSoC student working on 2 non-trivial FDW's right now, and I have a couple I've been working on. If we're going to make the API incompatible to that extent, we might as well not bother :-( If nobody bothers then there won't be any experience on which to base a stable API. In particular, I think it's crucial that we get working FDWs for MySQL, SQLServer and Oracle ASAP. Yeah - MySQL is one of the ones I've been hacking on. It's hard to be motivated if its going to need a complete rewrite within a year though. I'll still have to work on it, as I've committed to giving talks on it, but others might not bother to even start. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake 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] procpid?
On Sun, Jun 12, 2011 at 2:23 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Jun 11, 2011 at 9:15 PM, Joshua D. Drake j...@commandprompt.com wrote: On 6/11/2011 1:23 PM, Bruce Momjian wrote: There is a difference between a project name and something that directly affects usability. +1 on fixing this. IMO, we don't create a new pid column, we just fix the problem. If we do it for 9.2, we have 18 months to communicate the change. Uh, I am the first one I remember complaining about this so I don't see why we should break compatibility for such a low-level problem. Because it is a very real problem with an easy fix. We have 18 months to publicize that fix. I mean really? This is a no-brainer. I really don't see what the big deal with calling it the process PID rather than just the PID is. Changing something like this forces pgAdmin and every other application out there that is built to work with PG to make a code change to keep working with PG. That seems like pushing a lot of unnecessary work on other people for what is basically a minor cosmetic issue. +1 If we were going to make changes like this, I'd suggest we save them up in a big bag for when we change major version number. Everybody in the world thinks that PostgreSQL v8 is compatible across all versions (8.0, 8.1, 8.2, 8.3, 8.4), and it will be same with v9. That way we would still have forward progress, but in more sensible sized steps. Otherwise we just break the code annually for all the people that support us. If we had a more stable environment for tools vendors, maybe people wouldn't need to be manually typing procpid anyway... -- 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] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 3:54 PM, Dave Page dp...@pgadmin.org wrote: Yeah - MySQL is one of the ones I've been hacking on. It's hard to be motivated if its going to need a complete rewrite within a year though. I'll still have to work on it, as I've committed to giving talks on it, but others might not bother to even start. It's a double-edged sword. If nobody writes anything, because everyone is afraid to possibly having to change things, nothing will never need to be changed ;-) a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wrong message on REASSIGN OWNED
On Mon, Jun 13, 2011 at 10:37 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of sáb jun 11 21:01:55 -0400 2011: On Thu, Jun 9, 2011 at 1:26 PM, Jaime Casanova ja...@2ndquadrant.com wrote: on shdepReassignOwned() we have this message, which is obviously wrong we are not dropping objects just reassigning them... ereport(ERROR, (errcode(ERRCODE_DEPENDENT_OBJECTS_STILL_EXIST), errmsg(cannot drop objects owned by %s because they are required by the database system, getObjectDescription(obj; Oh my. I introduced this mistake 6 years ago and nobody noticed in all this time. I guess this is not a very frequently used feature. well, i used it once last year... ;) but the only reason i found this one was because i was giving a training and it jumps out in the middle of it... -- 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] [v9.1] sepgsql - userspace access vector cache
2011/6/13 Robert Haas robertmh...@gmail.com: On Mon, Jun 13, 2011 at 7:51 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: For syscache, length of a typical security label in selinux is less than 64 bytes. If we assume an entry consume 128bytes including Oid pairs or pointers, its consumption is 128KBytes per 1,000 of tables or others. (Do we have a way to confirm syscache status?) I was thinking you might start a new session, SELECT pg_backendd_pid() to get the PID, use top/ps to get its memory usage; then do a bunch of stuff and see how much it's grown. The difference between how much it grows with and without the patch is the amount of additional memory the patch consumes. I checked memory consumption of the backend with / without patches. Because sepgsql_restorecon() tries to reset security label of all the schemas, relations, columns and procedures, an execution of this function is suitable to emphasize differences between two cases in maximum. The results shows us about 3MB of additional consumption in VmRSS, even if it caches all the security label of the objects being created in the default (3331 entries). * without patches before/after sepgsql_restorecon() VmPeak: 150812 kB - 170864 kB VmSize: 150804 kB - 154712 kB VmLck: 0 kB - 0kB VmHWM: 3800 kB - 22248 kB VmRSS: 3800 kB - 10620 kB VmData: 1940 kB - 5820 kB VmStk: 196 kB - 196 kB VmExe: 5324 kB - 5324 kB VmLib: 2468 kB - 2468 kB VmPTE: 108 kB - 120 kB VmSwap:0 kB - 0kB * with patches before/after sepgsql_restorecon() VmPeak: 150816 kB - 175092 kB VmSize: 150808 kB - 158804 kB VmLck: 0 kB - 0 kB VmHWM: 3868 kB - 25956 kB VmRSS: 3868 kB - 13736 kB VmData: 1944 kB - 9912 kB VmStk: 192 kB - 192 kB VmExe: 5324 kB - 5324 kB VmLib: 2472 kB - 2472 kB VmPTE: 100 kB - 124 kB VmSwap:0 kB - 0 kB Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: CreateComments: use explicit indexing for ``values''
On Mon, Jun 13, 2011 at 11:09 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Historically this i++ approach has been used in a lot of places that fill in system catalog tuples. We've fixed some of them over time, but I doubt this is the only one remaining. If we're going to try to remove it here, maybe we ought to try to fix them all rather than just this one. A quick grep reveals that the places that still do it that way are OperatorShellMake OperatorCreate TypeShellMake TypeCreate update_attstats (though this one might be hard to improve) CreateComments CreateSharedComments InsertRule Of these, all but the two in comment.c follow the convention of mentioning the assigned-to column in a comment, so that the code is at least somewhat greppable. So those two definitely need improvement, but should we consider changing the others while at it? Have at it, if you like. BTW, there are some contrib modules with functions-returning-record that fill in result tuples this way as well. But we don't have symbolic constants for the column numbers there, and it's probably not worth introducing such. Yeah, I think that would not be a good use of time. -- 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] procpid?
On Mon, Jun 13, 2011 at 11:56 AM, Simon Riggs si...@2ndquadrant.com wrote: +1 If we were going to make changes like this, I'd suggest we save them up in a big bag for when we change major version number. Everybody in the world thinks that PostgreSQL v8 is compatible across all versions (8.0, 8.1, 8.2, 8.3, 8.4), and it will be same with v9. That way we would still have forward progress, but in more sensible sized steps. Otherwise we just break the code annually for all the people that support us. If we had a more stable environment for tools vendors, maybe people wouldn't need to be manually typing procpid anyway... Amen. -- 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] postgresql 9.0.4 source compilation issue on OSX
Thanks Bob for your lead. I was finally able to solve my issue. I had earlier installed the binary version of postgresql. As I had to compile certain contrib/modules sometime back, I had chosen to set USE PGXS=1 in my .zshrc file. This was causing these errors. It was silly mistake from my end. Thanks a ton for your lead which helped me discover the root cause. - Bhavin On Thu, Jun 9, 2011 at 7:19 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jun 9, 2011 at 2:59 AM, Bhavin Kamani bhavinkam...@gmail.com wrote: I am installing postgresql using homebrew on OSX. I am getting strange compilation errors. Googling did not help me in getting any lead to the resolution. When I compile it on a standalone basis (without using brew) I still get the same error. Compiling 9.1beta1 source package also gave me the same set of errors. Any lead/help will be highly appreciated. I'm not exactly sure what's going on here, but contrib modules can be built in two ways: with PGXS, or without PGXS. Typically, you compile without PGXS when you're building the server and it's contrib modules, and with PGXS if the server is already installed (and thus pg_config is present) and you just want to compile one more module. Here it looks like you're doing an in-tree compile but somehow PGXS is getting enabled anyway - not sure what's going on there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 4:08 PM, Aidan Van Dyk ai...@highrise.ca wrote: On Mon, Jun 13, 2011 at 3:54 PM, Dave Page dp...@pgadmin.org wrote: Yeah - MySQL is one of the ones I've been hacking on. It's hard to be motivated if its going to need a complete rewrite within a year though. I'll still have to work on it, as I've committed to giving talks on it, but others might not bother to even start. It's a double-edged sword. If nobody writes anything, because everyone is afraid to possibly having to change things, nothing will never need to be changed ;-) It might be that the process of writing the MySQL FDW code would show off things that'll need to get changed. So the breakage might turn out to be Dave's fault! :-) [Seriously.] We really won't know what needs fixing/improving until nontrivial FDWs get written, and it would be somewhat ironic, but really not hugely surprising, if Dave wound up requesting changes to the underlying API to *properly* support what he writes. There's some degree of irony and amusement to be found here, but nothing that strikes me as disturbing. -- When confronted by a difficult problem, solve it by reducing it to the question, How would the Lone Ranger handle this? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 5:29 PM, Christopher Browne cbbro...@gmail.com wrote: On Mon, Jun 13, 2011 at 4:08 PM, Aidan Van Dyk ai...@highrise.ca wrote: On Mon, Jun 13, 2011 at 3:54 PM, Dave Page dp...@pgadmin.org wrote: Yeah - MySQL is one of the ones I've been hacking on. It's hard to be motivated if its going to need a complete rewrite within a year though. I'll still have to work on it, as I've committed to giving talks on it, but others might not bother to even start. It's a double-edged sword. If nobody writes anything, because everyone is afraid to possibly having to change things, nothing will never need to be changed ;-) It might be that the process of writing the MySQL FDW code would show off things that'll need to get changed. So the breakage might turn out to be Dave's fault! :-) [Seriously.] We really won't know what needs fixing/improving until nontrivial FDWs get written, and it would be somewhat ironic, but really not hugely surprising, if Dave wound up requesting changes to the underlying API to *properly* support what he writes. There's some degree of irony and amusement to be found here, but nothing that strikes me as disturbing. Oh, I can imagine that happening; what I would expect though is that we make some attempt to retain compatibility to avoid the need for total rewrites of FDWs as Tom seems to be expecting. BTW; it seems to me this should be documented, as it could really hack off developers. I can't see anything in the docs to imply the API might be radically redesigned. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake 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] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 12:38 PM, Dave Page dp...@pgadmin.org wrote: On Mon, Jun 13, 2011 at 5:29 PM, Christopher Browne cbbro...@gmail.com wrote: On Mon, Jun 13, 2011 at 4:08 PM, Aidan Van Dyk ai...@highrise.ca wrote: On Mon, Jun 13, 2011 at 3:54 PM, Dave Page dp...@pgadmin.org wrote: Yeah - MySQL is one of the ones I've been hacking on. It's hard to be motivated if its going to need a complete rewrite within a year though. I'll still have to work on it, as I've committed to giving talks on it, but others might not bother to even start. It's a double-edged sword. If nobody writes anything, because everyone is afraid to possibly having to change things, nothing will never need to be changed ;-) It might be that the process of writing the MySQL FDW code would show off things that'll need to get changed. So the breakage might turn out to be Dave's fault! :-) [Seriously.] We really won't know what needs fixing/improving until nontrivial FDWs get written, and it would be somewhat ironic, but really not hugely surprising, if Dave wound up requesting changes to the underlying API to *properly* support what he writes. There's some degree of irony and amusement to be found here, but nothing that strikes me as disturbing. Oh, I can imagine that happening; what I would expect though is that we make some attempt to retain compatibility to avoid the need for total rewrites of FDWs as Tom seems to be expecting. BTW; it seems to me this should be documented, as it could really hack off developers. I can't see anything in the docs to imply the API might be radically redesigned. And I'm still unconvinced that it's needed. I think we're going to end up adding on things that are missing and maybe replacing things that are just stubs, but I don't see why we'd whack it around just for fun. -- 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] Participation Requested: Survey about Open-Source Software Development
Hi, Drs. Jeffrey Carver, Rosanna Guadagno, Debra McCallum, and Mr. Amiangshu Bosu, University of Alabama, and Dr. Lorin Hochstein, University of Southern California, are conducting a survey of open-source software developers. This survey seeks to understand how developers on distributed, virtual teams, like open-source projects, interact with each other to accomplish their tasks. You must be at least 19 years of age to complete the survey. The survey should take approximately 15 minutes to complete. If you are actively participating as a developer, please consider completing our survey. Here is the link to the survey: http://goo.gl/HQnux We apologize for inconvenience and if you receive multiple copies of this email. This survey has been approved by The University of Alabama IRB board. Thanks, Dr. Jeffrey Carver Assistant Professor University of Alabama (v) 205-348-9829 (f) 205-348-0219 http://www.cs.ua.edu/~carver -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] SECURITY LABEL on shared database object
On Mon, Jun 13, 2011 at 12:24 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The attached patch is an update revision of security label support for shared database objects. I'm kind of unexcited about this whole idea. Adding a shared catalog for a feature that's only of interest to a small percentage of our user population seems unfortunate. Are there any other possible approaches to this problem? -- 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] procpid?
Simon Riggs wrote: On Sun, Jun 12, 2011 at 2:23 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, Jun 11, 2011 at 9:15 PM, Joshua D. Drake j...@commandprompt.com wrote: On 6/11/2011 1:23 PM, Bruce Momjian wrote: There is a difference between a project name and something that directly affects usability. +1 on fixing this. IMO, we don't create a new pid column, we just fix the problem. If we do it for 9.2, we have 18 months to communicate the change. Uh, I am the first one I remember complaining about this so I don't see why we should break compatibility for such a low-level problem. Because it is a very real problem with an easy fix. We have 18 months to publicize that fix. I mean really? This is a no-brainer. I really don't see what the big deal with calling it the process PID rather than just the PID is. ?Changing something like this forces pgAdmin and every other application out there that is built to work with PG to make a code change to keep working with PG. ?That seems like pushing a lot of unnecessary work on other people for what is basically a minor cosmetic issue. +1 If we were going to make changes like this, I'd suggest we save them up in a big bag for when we change major version number. Everybody in the world thinks that PostgreSQL v8 is compatible across all versions (8.0, 8.1, 8.2, 8.3, 8.4), and it will be same with v9. That way we would still have forward progress, but in more sensible sized steps. Otherwise we just break the code annually for all the people that support us. If we had a more stable environment for tools vendors, maybe people wouldn't need to be manually typing procpid anyway... Agreed. I did add a C comment that this was misnamed so when we are in that code we will see it. I did reorder the pg_stat_activity columns in 9.0 for sanity, and no one complained, but renaming is more disruptive than reordering. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] SECURITY LABEL on shared database object
2011/6/13 Robert Haas robertmh...@gmail.com: On Mon, Jun 13, 2011 at 12:24 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The attached patch is an update revision of security label support for shared database objects. I'm kind of unexcited about this whole idea. Adding a shared catalog for a feature that's only of interest to a small percentage of our user population seems unfortunate. Are there any other possible approaches to this problem? If unexcited about the new shared catalog, one possible idea is to add a new field to pg_database, pg_tablespace and pg_authid to store security labels? The reason why we had pg_seclabel is to avoid massive amount of modifications to system catalog. But only 3 catalogs to be modified to support security label on shared object. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 12:38 PM, Dave Page dp...@pgadmin.org wrote: BTW; it seems to me this should be documented, as it could really hack off developers. I can't see anything in the docs to imply the API might be radically redesigned. And I'm still unconvinced that it's needed. I think we're going to end up adding on things that are missing and maybe replacing things that are just stubs, but I don't see why we'd whack it around just for fun. I think we're talking past each other. The point I'm trying to make is that there are no defined/documented APIs for most of the planner, and so any FDW that needs to do nontrivial planning stuff will need to reach into pieces of the code that we've historically felt free to change as needed. We can't just suddenly decide that all that code is now locked down on the grounds that somebody might be touching it. As an example, assuming that I figure out how to do generalized parameterized inner plans in 9.2, whether or not the changes required might break somebody's FDW is simply not going to be a consideration. Once we have some idea of exactly which aspects of the planner FDWs are likely to need to depend on, we can write down some API contracts and then try to adhere to them. But we don't have those today, and I don't think it's profitable to try to write them until we have some more experience with writing nontrivial FDWs. In practice this might turn out to be less of a problem than Dave thinks. We've made plenty of changes in the past that could affect third-party selectivity functions, and lately we've been adding planner hooks that likewise are seeing call contexts that change from version to version; but I've not heard very many complaints about those instabilities. So maybe the average FDW won't find this to be a big deal either. What I was reacting to at the start of this sub-thread was the idea that the remote-postgresql FDW in particular would be cross-version compatible. That's not an average FDW; I think that it will have enough planner dependencies to be a poster child for these issues. 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] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 12:38 PM, Dave Page dp...@pgadmin.org wrote: BTW; it seems to me this should be documented, as it could really hack off developers. I can't see anything in the docs to imply the API might be radically redesigned. And I'm still unconvinced that it's needed. I think we're going to end up adding on things that are missing and maybe replacing things that are just stubs, but I don't see why we'd whack it around just for fun. I think we're talking past each other. The point I'm trying to make is that there are no defined/documented APIs for most of the planner, and so any FDW that needs to do nontrivial planning stuff will need to reach into pieces of the code that we've historically felt free to change as needed. We can't just suddenly decide that all that code is now locked down on the grounds that somebody might be touching it. As an example, assuming that I figure out how to do generalized parameterized inner plans in 9.2, whether or not the changes required might break somebody's FDW is simply not going to be a consideration. Hmm, I wonder if you're correct (as usual :-p). I thought you were talking about the API as defined here: http://www.postgresql.org/docs/9.1/static/fdw-routines.html, not internal planner stuff. I agree that if I use that (and I have, but only minimally), it should be on my own head. In practice this might turn out to be less of a problem than Dave thinks. We've made plenty of changes in the past that could affect third-party selectivity functions, and lately we've been adding planner hooks that likewise are seeing call contexts that change from version to version; but I've not heard very many complaints about those instabilities. I've certainly seen similar issues with the debugger plugin for example - but that's not using a documented API, bar a couple of hooks. So maybe the average FDW won't find this to be a big deal either. What I was reacting to at the start of this sub-thread was the idea that the remote-postgresql FDW in particular would be cross-version compatible. That's not an average FDW; I think that it will have enough planner dependencies to be a poster child for these issues. That I can understand - you'll have to forgive me for reading more into making an FDW work on both 9.1 and 9.2 would be an exercise in frustration, if it's even possible. though :-) -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake 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] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 1:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Jun 13, 2011 at 12:38 PM, Dave Page dp...@pgadmin.org wrote: BTW; it seems to me this should be documented, as it could really hack off developers. I can't see anything in the docs to imply the API might be radically redesigned. And I'm still unconvinced that it's needed. I think we're going to end up adding on things that are missing and maybe replacing things that are just stubs, but I don't see why we'd whack it around just for fun. I think we're talking past each other. The point I'm trying to make is that there are no defined/documented APIs for most of the planner, and so any FDW that needs to do nontrivial planning stuff will need to reach into pieces of the code that we've historically felt free to change as needed. We can't just suddenly decide that all that code is now locked down on the grounds that somebody might be touching it. As an example, assuming that I figure out how to do generalized parameterized inner plans in 9.2, whether or not the changes required might break somebody's FDW is simply not going to be a consideration. Once we have some idea of exactly which aspects of the planner FDWs are likely to need to depend on, we can write down some API contracts and then try to adhere to them. But we don't have those today, and I don't think it's profitable to try to write them until we have some more experience with writing nontrivial FDWs. In practice this might turn out to be less of a problem than Dave thinks. We've made plenty of changes in the past that could affect third-party selectivity functions, and lately we've been adding planner hooks that likewise are seeing call contexts that change from version to version; but I've not heard very many complaints about those instabilities. So maybe the average FDW won't find this to be a big deal either. What I was reacting to at the start of this sub-thread was the idea that the remote-postgresql FDW in particular would be cross-version compatible. That's not an average FDW; I think that it will have enough planner dependencies to be a poster child for these issues. But my point is: any FDW code Dave rights now is not going to have major dependencies on the planner that will potentially require extensive reworking in the future because it won't have any real dependencies on the planner at all. It's not like we have an API and we're planning to change it: what we have to talk to the planner right now is little more than a stub. Unless I miss my guess, the work Dave et al are doing right now is just around making PostgreSQL talk to X, for various values of X. Now, if we expose an API to allow qual pushdown, all of those FDWs will need to be updated if they want to support qual pushdown (and maybe even a little bit, if they don't). But the work of, say, making it possible to translate MySQL tuples into PostgreSQL tuples doesn't seem likely to be wasted. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] SECURITY LABEL on shared database object
On Mon, Jun 13, 2011 at 1:40 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2011/6/13 Robert Haas robertmh...@gmail.com: On Mon, Jun 13, 2011 at 12:24 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The attached patch is an update revision of security label support for shared database objects. I'm kind of unexcited about this whole idea. Adding a shared catalog for a feature that's only of interest to a small percentage of our user population seems unfortunate. Are there any other possible approaches to this problem? If unexcited about the new shared catalog, one possible idea is to add a new field to pg_database, pg_tablespace and pg_authid to store security labels? The reason why we had pg_seclabel is to avoid massive amount of modifications to system catalog. But only 3 catalogs to be modified to support security label on shared object. I guess maybe my real question here is - what do you plan to do with those security labels, from a security perspective? For example: roles. The user's security contect AIUI is passed over from the remote side; his DAC role doesn't even enter into it from a MAC perspective. Or does it? -- 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] FOREIGN TABLE doc fix
Dave Page dp...@pgadmin.org writes: On Mon, Jun 13, 2011 at 6:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think we're talking past each other. Hmm, I wonder if you're correct (as usual :-p). I thought you were talking about the API as defined here: http://www.postgresql.org/docs/9.1/static/fdw-routines.html, not internal planner stuff. I agree that if I use that (and I have, but only minimally), it should be on my own head. Well, you'll notice that that document is mighty handwavy about exactly what PlanForeignScan needs to do to accomplish its responsibilities... But as far as breaking things at that level of detail is concerned, the main thing I can foresee is that doing a parameterized inner scan on a foreign table is both extremely desirable, and unsupportable given this contract for PlanForeignScan. We'll need to either add more parameters to it or invent a different entry point for considering parameterized scans. 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] FOREIGN TABLE doc fix
Robert Haas robertmh...@gmail.com writes: But my point is: any FDW code Dave rights now is not going to have major dependencies on the planner that will potentially require extensive reworking in the future because it won't have any real dependencies on the planner at all. It's not like we have an API and we're planning to change it: what we have to talk to the planner right now is little more than a stub. No, what we have to talk to the planner right now is look through all of src/include/optimizer/ and call whatever you want, and maybe lift some code out of src/backend/optimizer/ if the function you need isn't exported. I agree that a lot of basic FDW work can be done without much of any planner contact, but as soon as you do get interested in having non-brain-dead planning behavior in your FDW, you're going to be doing stuff that way. We're going to want to codify it a bit better than that; but it's not going to be practical to do so until some people have taken the plunge and written some code on the understanding that it's probably throwaway code. 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] Reminder: 1.5 days to 9.2 CF1
Hackers, As a reminder, CommitFest 1 for PostgreSQL 9.2 development starts in less than two days. If you have a patch for 9.2 which is ready for review, please add it to the commitfest application right now. Thanks! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating new remote branch in git?
Robert Haas wrote: On Sun, Jun 12, 2011 at 7:59 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Did the system create the .git/config '[branch REL9_1_STABLE]' section for you or did you create it manually? git created them for me. ?I did no config hacking whatever, but now I have: [branch REL9_1_STABLE] ? ? ? remote = origin ? ? ? merge = refs/heads/REL9_1_STABLE ? ? ? rebase = true which exactly parallels the pre-existing entries for the other branches. One point that might affect this is that in ~/.gitconfig I have [branch] ? ? ? autosetuprebase = always which is as per the setup recommendations on the wiki page. I have the same in my ~/.gitconfig: ? ? ? ?[branch] ? ? ? ? ? ? ? ?autosetuprebase = always I am attaching my ~/.gitconfig. Do I need to run this in every branch? ? ? ? ?git config branch.master.rebase true Right now our wiki only says to run it in the master branch. ?I am attaching my postgresql/.git/config file too. This is ironclad evidence that you followed the directions out of order, but yes, running that for every branch will fix it. I found the cause. When I added 'github' to ~/.gitconfig a few months ago, I copied this line from .git/config: fetch = +refs/heads/*:refs/remotes/origin/* If this line is in ~/.gitconfig for both 'origin' and 'github', git cannot create .git/config entries. Attached is my corrected ~/.gitconfig file. I now use only the 'url' branch entries, which is all that is needed. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + [user] name = Bruce Momjian email = br...@momjian.us [core] excludesfile = /u/postgres/.gitignore editor = emastd pager = less -x4 -E [diff] external = git-external-diff [remote origin] url = ssh://g...@gitmaster.postgresql.org/postgresql.git # Do not add the next line or .git/config is not updated. # fetch = +refs/heads/*:refs/remotes/origin/* [remote github] url = g...@github.com:bmomjian/postgres.git [branch] autosetuprebase = always rebase = true [gc] auto = 0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reminder: 1.5 days to 9.2 CF1
On Mon, Jun 13, 2011 at 2:25 PM, Josh Berkus j...@agliodbs.com wrote: As a reminder, CommitFest 1 for PostgreSQL 9.2 development starts in less than two days. If you have a patch for 9.2 which is ready for review, please add it to the commitfest application right now. You forgot part #2: please also volunteer to review a patch someone else wrote. -- 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] SSI patch renumbered existing 2PC resource managers??
So I finally started actually reading the SSI changes, and I am a tad distressed by this: diff --git a/src/include/access/twophase_rmgr.h b/src/include/access/twophase_rmgr.h index a541d0f..1c7d8bb 100644 --- a/src/include/access/twophase_rmgr.h +++ b/src/include/access/twophase_rmgr.h @@ -23,8 +23,9 @@ typedef uint8 TwoPhaseRmgrId; */ #define TWOPHASE_RM_END_ID 0 #define TWOPHASE_RM_LOCK_ID1 -#define TWOPHASE_RM_PGSTAT_ID 2 -#define TWOPHASE_RM_MULTIXACT_ID 3 +#define TWOPHASE_RM_PREDICATELOCK_ID 2 +#define TWOPHASE_RM_PGSTAT_ID 3 +#define TWOPHASE_RM_MULTIXACT_ID 4 #define TWOPHASE_RM_MAX_ID TWOPHASE_RM_MULTIXACT_ID extern const TwoPhaseCallback twophase_recover_callbacks[]; What was the rationale for changing the assignments of existing 2PC IDs? So far as I can tell, that breaks pg_upgrade (if there are any open prepared transactions) for no redeeming social benefit. Is there a reason why TWOPHASE_RM_PREDICATELOCK_ID has to be 2 and not at the end? 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] [v9.2] SECURITY LABEL on shared database object
2011/6/13 Robert Haas robertmh...@gmail.com: On Mon, Jun 13, 2011 at 1:40 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2011/6/13 Robert Haas robertmh...@gmail.com: On Mon, Jun 13, 2011 at 12:24 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The attached patch is an update revision of security label support for shared database objects. I'm kind of unexcited about this whole idea. Adding a shared catalog for a feature that's only of interest to a small percentage of our user population seems unfortunate. Are there any other possible approaches to this problem? If unexcited about the new shared catalog, one possible idea is to add a new field to pg_database, pg_tablespace and pg_authid to store security labels? The reason why we had pg_seclabel is to avoid massive amount of modifications to system catalog. But only 3 catalogs to be modified to support security label on shared object. I guess maybe my real question here is - what do you plan to do with those security labels, from a security perspective? For example: roles. The user's security contect AIUI is passed over from the remote side; his DAC role doesn't even enter into it from a MAC perspective. Or does it? The current primary target of security label of shared object is to acquire control on databases. It performs as a basis to compute default security label of underlying objects, and I also plan to control when we open the connection like ACL_CONNECT. Right now, we assume any database has system_u:object_r:sepgsql_db_t:s0, then the default security label of schema objects are computed. (See sepgsql_schema_post_create in contrib/sepgsql/schema.c) Regarding to the pg_tablespace and pg_authid, I have a plan to control DDL statements on these objects, However, its priority is not higher than databases or other non-shared objects such as tables or procedures. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FOREIGN TABLE doc fix
On Mon, Jun 13, 2011 at 7:08 PM, Robert Haas robertmh...@gmail.com wrote: But my point is: any FDW code Dave rights now is not going to have major dependencies on the planner that will potentially require extensive reworking in the future because it won't have any real dependencies on the planner at all. It's not like we have an API and we're planning to change it: what we have to talk to the planner right now is little more than a stub. Unless I miss my guess, the work Dave et al are doing right now is just around making PostgreSQL talk to X, for various values of X. Now, if we expose an API to allow qual pushdown, all of those FDWs will need to be updated if they want to support qual pushdown (and maybe even a little bit, if they don't). But the work of, say, making it possible to translate MySQL tuples into PostgreSQL tuples doesn't seem likely to be wasted. Right - that's what I thought Tom was saying would be junked. I've already implemented some simple qual pushdown in the redis FDW, and am planning to do something similar for MySQL - however I won't be surprised if I have to rewrite redisGetQual in https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c for example. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake 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] WIP: collect frequency statistics for arrays
On Mon, Jun 13, 2011 at 8:16 AM, Robert Haas robertmh...@gmail.com wrote: If the data type is hashable, you could consider building a hash table on the MCVs and then do a probe for each element in the array. I think that's better than the other way around because there can't be more than 10k MCVs, whereas the input constant could be arbitrarily long. I'm not entirely sure whether this case is important enough to be worth spending a lot of code on, but then again it might not be that much code. Unfortunately, most time consuming operation isn't related to elements comparison. It is caused by complex computations in calc_distr function. Another option is to bound the number of operations you're willing to perform to some reasonable limit, say, 10 * default_statistics_target. Work out ceil((10 * default_statistics_target) / number-of-elements-in-const) and consider at most that many MCVs. When this limit kicks in you'll get a less-accurate selectivity estimate, but that's a reasonable price to pay for not blowing out planning time. Good option. I'm going to add such condition to my patch. -- With best regards, Alexander Korotkov.
Re: [HACKERS] SSI patch renumbered existing 2PC resource managers??
Tom Lane t...@sss.pgh.pa.us wrote: What was the rationale for changing the assignments of existing 2PC IDs? So far as I can tell, that breaks pg_upgrade (if there are any open prepared transactions) for no redeeming social benefit. Is there a reason why TWOPHASE_RM_PREDICATELOCK_ID has to be 2 and not at the end? I'm sure that Dan will jump in if this guess is wrong, but since the predicate lock code is largely derived from the heavyweight locking code, it probably seemed to have a minor cosmetic benefit to put it adjacent to that. It didn't occur to me when the SSI 2PC code went in, but I can see the problem now that you point it out. The new entry should be moved to the end for compatibility. Would you like me to submit a patch to fix this, or should I stay out of your way? -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] SSI patch renumbered existing 2PC resource managers??
On 13.06.2011 21:31, Tom Lane wrote: So I finally started actually reading the SSI changes, and I am a tad distressed by this: diff --git a/src/include/access/twophase_rmgr.h b/src/include/access/twophase_rmgr.h index a541d0f..1c7d8bb 100644 --- a/src/include/access/twophase_rmgr.h +++ b/src/include/access/twophase_rmgr.h @@ -23,8 +23,9 @@ typedef uint8 TwoPhaseRmgrId; */ #define TWOPHASE_RM_END_ID0 #define TWOPHASE_RM_LOCK_ID 1 -#define TWOPHASE_RM_PGSTAT_ID 2 -#define TWOPHASE_RM_MULTIXACT_ID 3 +#define TWOPHASE_RM_PREDICATELOCK_ID 2 +#define TWOPHASE_RM_PGSTAT_ID 3 +#define TWOPHASE_RM_MULTIXACT_ID 4 #define TWOPHASE_RM_MAX_IDTWOPHASE_RM_MULTIXACT_ID extern const TwoPhaseCallback twophase_recover_callbacks[]; What was the rationale for changing the assignments of existing 2PC IDs? As far as I can tell it was for purely cosmetic reasons, to have lock and predicate lock lines together. So far as I can tell, that breaks pg_upgrade (if there are any open prepared transactions) for no redeeming social benefit. Surely pg_upgrade can't work anyway if there's any open prepared transactions in the database. We're not going to guarantee to keep all the data structures we write in two-phase state files unchanged over major releases. If pg_upgrade is not checking for prepared transcations at the moment, such a check should probably should be added. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Bug in XPATH() if expression returns a scalar value
On ons, 2011-06-08 at 10:14 +0200, Florian Pflug wrote: But then you lose the ability to evaluate user-supplied XPath expressions, because there's no way of telling which of these function to use. Perhaps having both variants, one type-safe and one not, would work. I don't agree with doing away with type-safety completely for the sake of convenience. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI patch renumbered existing 2PC resource managers??
On Mon, Jun 13, 2011 at 10:22:19PM +0300, Heikki Linnakangas wrote: As far as I can tell it was for purely cosmetic reasons, to have lock and predicate lock lines together. Yes, that is the only reason. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI patch renumbered existing 2PC resource managers??
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 13.06.2011 21:31, Tom Lane wrote: So far as I can tell, that breaks pg_upgrade (if there are any open prepared transactions) for no redeeming social benefit. Surely pg_upgrade can't work anyway if there's any open prepared transactions in the database. We're not going to guarantee to keep all the data structures we write in two-phase state files unchanged over major releases. If pg_upgrade is not checking for prepared transcations at the moment, such a check should probably should be added. No, pg_upgrade should not be unilaterally refusing that. The correct way to deal with this consideration is to change the TWOPHASE_MAGIC number when we make a change in on-disk 2PC state. Which wasn't done in the SSI patch. We can either change that now, or undo the unnecessary change in existing RM IDs. I vote for the latter. 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] FOREIGN TABLE doc fix
Dave Page dp...@pgadmin.org writes: I've already implemented some simple qual pushdown in the redis FDW, and am planning to do something similar for MySQL - however I won't be surprised if I have to rewrite redisGetQual in https://github.com/dpage/redis_fdw/blob/master/redis_fdw.c for example. OK, *now* we're on the same page. This sort of experimentation is exactly what I'm talking about: we'll certainly want to rewrite the code once we have better infrastructure, but it's necessary to write some throwaway code while we're learning what infrastructure FDWs want. (I find it a bit weird btw that you seem to be doing that at execution time not plan time...) 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] ECPG parse.pl and parse2.pl
Surely we don't need parse.pl and parse2.pl? parse.pl, the one that's not used, it already showing signs of semi-bit-rot: Use of assignment to $[ is deprecated at ./parse.pl line 21. I propose that we move parse2.pl to parse.pl and only keep the one. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reminder: 1.5 days to 9.2 CF1
On 6/13/11 11:29 AM, Robert Haas wrote: On Mon, Jun 13, 2011 at 2:25 PM, Josh Berkus j...@agliodbs.com wrote: As a reminder, CommitFest 1 for PostgreSQL 9.2 development starts in less than two days. If you have a patch for 9.2 which is ready for review, please add it to the commitfest application right now. You forgot part #2: please also volunteer to review a patch someone else wrote. Yeah, I harassed people about that last week. And I'll do it again on Wednesday. -- 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] Make relation_openrv atomic wrt DDL
On Mon, Jun 13, 2011 at 08:21:05AM -0400, Robert Haas wrote: On Mon, Jun 13, 2011 at 1:12 AM, Noah Misch n...@leadboat.com wrote: This probably would not replace a backend-local counter of processed messages for RangeVarLockRelid()'s purposes. ?It's quite possibly a good way to reduce SInvalReadLock traffic, though. I was imagining one shared global counter, not one per backend, and thinking that each backend could do something like: volatile uint32 *the_global_counter = global_counter; uint32 latest_counter; mfence(); latest_counter = *the_global_counter; if (latest_counter != previous_value_of_global_counter || myprocstate-isReset) really_do_it(); previous_value_of_global_counter = latest_counter; I'm not immediately seeing why that wouldn't work for your purposes as well. That takes us back to the problem of answering the (somewhat rephrased) question Did any call to AcceptInvalidationMessages() between code point A and code point B call really_do_it()? in a way not prone to breaking when new calls to AcceptInvalidationMessages(), perhaps indirectly, get added. That's what the local counter achieved. To achieve that, previous_value_of_global_counter would need to be exposed outside sinval.c. That leaves us with a backend-local counter updated in a different fashion. I might be missing something... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating new remote branch in git?
On 06/13/2011 02:26 PM, Bruce Momjian wrote: I found the cause. When I added 'github' to ~/.gitconfig a few months ago, I copied this line from .git/config: fetch = +refs/heads/*:refs/remotes/origin/* If this line is in ~/.gitconfig for both 'origin' and 'github', git cannot create .git/config entries. Attached is my corrected ~/.gitconfig file. I now use only the 'url' branch entries, which is all that is needed. [...] [remote origin] url = ssh://g...@gitmaster.postgresql.org/postgresql.git # Do not add the next line or .git/config is not updated. #fetch = +refs/heads/*:refs/remotes/origin/* [remote github] url = g...@github.com:bmomjian/postgres.git Is putting remotes in your ~/.gitconfig good practice? I certainly don't have any in mine. The one for origin seems a particularly bad idea to me, although I don't claim that my git-fu is of the highest. pager = less -x4 -E I experimented with this setting quite a bit before getting it the way I wanted. You might find this works better: pager = less -+$LESS -FRSX 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] ECPG parse.pl and parse2.pl
Peter Eisentraut pete...@gmx.net writes: Surely we don't need parse.pl and parse2.pl? parse.pl, the one that's not used, it already showing signs of semi-bit-rot: Use of assignment to $[ is deprecated at ./parse.pl line 21. I propose that we move parse2.pl to parse.pl and only keep the one. Yes. This was objected to immediately when Michael made it that way in the first place, but he didn't change it. I don't think we need to use HEAD as a repository for obsolete code, when we have a perfectly good SCM. 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] SSI patch renumbered existing 2PC resource managers??
On Mon, Jun 13, 2011 at 03:33:24PM -0400, Tom Lane wrote: We can either change that now, or undo the unnecessary change in existing RM IDs. I vote for the latter. Sounds good to me. I'd offer a patch, but it'd probably take you longer to apply than to make the change yourself. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ITYM DROP TABLE
I was reading the partitioning docs when I spotted this. I think it means to
highlight the advantages of DROP TABLE over DELETE rather than ALTER TABLE.
Best,
David
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 4c9fc5d..0cdb800 100644
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*** VALUES ('New York', NULL, NULL, 'NY');
*** 2332,2338
para
Bulk loads and deletes can be accomplished by adding or removing
partitions, if that requirement is planned into the partitioning design.
! commandALTER TABLE/ is far faster than a bulk operation.
It also entirely avoids the commandVACUUM/command
overhead caused by a bulk commandDELETE/.
/para
--- 2332,2338
para
Bulk loads and deletes can be accomplished by adding or removing
partitions, if that requirement is planned into the partitioning design.
! commandDROP TABLE/ is far faster than a bulk operation.
It also entirely avoids the commandVACUUM/command
overhead caused by a bulk commandDELETE/.
/para
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wrong message on REASSIGN OWNED
Excerpts from Tom Lane's message of lun jun 13 09:57:50 -0400 2011: Jaime Casanova ja...@2ndquadrant.com writes: On Sat, Jun 11, 2011 at 8:01 PM, Robert Haas robertmh...@gmail.com wrote: can't reassign objects owned by %s because this user is internal to the database system ? that message is not that clear... it seems to imply i can't reassign any object from that user... I think s/drop/reassign ownership of/ is sufficient. There's no reason to change the rest of the message, especially because the same phraseology is in use elsewhere. Fixed in 8.2 and up. -- Á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] creating CHECK constraints as NOT VALID
Excerpts from Dean Rasheed's message of sáb jun 11 09:32:15 -0400 2011: On 1 June 2011 23:47, Alvaro Herrera alvhe...@commandprompt.com wrote: Here's a complete patch with all this stuff, plus doc additions and simple regression tests for the new ALTER DOMAIN commands. Enable CHECK constraints to be declared NOT VALID This means that they can initially be added to a large existing table without checking its initial contents, but new tuples must comply to them; a separate pass invoked by ALTER TABLE / VALIDATE can verify existing data and ensure it complies with the constraint, at which point it is marked validated and becomes a normal part of the table ecosystem. I think that you also need to update the constraint exclusion code (get_relation_constraints() or nearby), otherwise the planner might exclude a relation on the basis of a CHECK constraint that is not currently VALID. Ouch, yeah, thanks for pointing that out. Fortunately the patch to fix this is quite simple. I don't have it handy right now but I'll post it soon. -- Á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] creating CHECK constraints as NOT VALID
Alvaro, Dean, I think that you also need to update the constraint exclusion code (get_relation_constraints() or nearby), otherwise the planner might exclude a relation on the basis of a CHECK constraint that is not currently VALID. Ouch, yeah, thanks for pointing that out. Fortunately the patch to fix this is quite simple. I don't have it handy right now but I'll post it soon. Hmmm. Is this the behavior we want with NOT VALID constraints though? I know that if I'm pouring 100m rows into a new partition as part of a repartitioning scheme, I don't want to *ever* check them if I know they're correct because of how I created the table (CREATE TABLE AS ...). -- 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] Creating new remote branch in git?
Andrew Dunstan wrote: On 06/13/2011 02:26 PM, Bruce Momjian wrote: I found the cause. When I added 'github' to ~/.gitconfig a few months ago, I copied this line from .git/config: fetch = +refs/heads/*:refs/remotes/origin/* If this line is in ~/.gitconfig for both 'origin' and 'github', git cannot create .git/config entries. Attached is my corrected ~/.gitconfig file. I now use only the 'url' branch entries, which is all that is needed. [...] [remote origin] url = ssh://g...@gitmaster.postgresql.org/postgresql.git # Do not add the next line or .git/config is not updated. #fetch = +refs/heads/*:refs/remotes/origin/* [remote github] url = g...@github.com:bmomjian/postgres.git Is putting remotes in your ~/.gitconfig good practice? I certainly don't have any in mine. Putting 'github' in there allows me to push/pull from github branches without having to specify the github URL. The one for origin seems a particularly bad idea to me, although I don't claim that my git-fu is of the highest. Yeah, it isn't necessary, but it does allow me to do: git clone origin again without having to specify the URL. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Creating new remote branch in git?
On 06/13/2011 06:38 PM, Bruce Momjian wrote: Andrew Dunstan wrote: On 06/13/2011 02:26 PM, Bruce Momjian wrote: I found the cause. When I added 'github' to ~/.gitconfig a few months ago, I copied this line from .git/config: fetch = +refs/heads/*:refs/remotes/origin/* If this line is in ~/.gitconfig for both 'origin' and 'github', git cannot create .git/config entries. Attached is my corrected ~/.gitconfig file. I now use only the 'url' branch entries, which is all that is needed. [...] [remote origin] url = ssh://g...@gitmaster.postgresql.org/postgresql.git # Do not add the next line or .git/config is not updated. #fetch = +refs/heads/*:refs/remotes/origin/* [remote github] url = g...@github.com:bmomjian/postgres.git Is putting remotes in your ~/.gitconfig good practice? I certainly don't have any in mine. Putting 'github' in there allows me to push/pull from github branches without having to specify the github URL. The one for origin seems a particularly bad idea to me, although I don't claim that my git-fu is of the highest. Yeah, it isn't necessary, but it does allow me to do: git clone origin again without having to specify the URL. Well, TIMTOWTDI, but I suspect you'd be much better off using git aliases for both these purposes. Then you would not have got yourself into the trouble that gave rise to this conversation. 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] creating CHECK constraints as NOT VALID
I can see why you would want that, but I'd say that's a separate feature you need to explicitly request when creating the constraint. Consider what happens in the old data is garbage, but I want the new data to be validated use case if we allow constraint exclusion on NOT VALID constraints. Yeah, I guess what I'm suggesting is that we should have an ALTER TABLE ... VALID DONT CHECK option. -- 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] creating CHECK constraints as NOT VALID
On 14/06/2011 01:11, Josh Berkus wrote: Hmmm. Is this the behavior we want with NOT VALID constraints though? I know that if I'm pouring 100m rows into a new partition as part of a repartitioning scheme, I don't want to *ever* check them if I know they're correct because of how I created the table (CREATE TABLE AS ...). I can see why you would want that, but I'd say that's a separate feature you need to explicitly request when creating the constraint. Consider what happens in the old data is garbage, but I want the new data to be validated use case if we allow constraint exclusion on NOT VALID constraints. -- Marko Tiikkajahttp://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] pg_trgm: unicode string not working
Robert Haas wrote: On Sun, Jun 12, 2011 at 8:40 AM, Florian Pflug f...@phlo.org wrote: Note that this restriction was removed in postgres 9.1 which is currently in beta. However, GIT indices must be re-created with REINDEX after upgrading from 9.0 to leverage that improvement. Does pg_upgrade know about this? No, it does not. Under what circumstances should I issue a suggestion to reindex, and what should the text be? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Why polecat and colugos are failing to build back branches
I looked into $SUBJECT. There appear to be two distinct issues: 1. On colugos (OS X with LLVM), the plperl link step is spitting up because Apple's ExtUtils::Embed puts some -arch switches into perl_embed_ldflags. We found out about that some time ago, and fixed it for 9.0 and up here: http://git.postgresql.org/gitweb?p=postgresql.gita=commitdiffh=d69a419e682c2d39c2355105a7e5e2b90357c8f0 However, because when using gcc that only results in a warning, we didn't back-patch it. Now it appears that it's an error when using LLVM, so maybe we oughta back-patch it, at least to whichever releases we think will build with LLVM. 2. Pre-9.0, the installation step is failing like this: make -C src install /bin/sh ../config/mkinstalldirs '/Volumes/High Usage/usr/local/src/build-farm-4.4/builds/REL8_2_STABLE/pgsql.14435/src/test/regress/./tmp_check/install/usr/local/src/build-farm-4.4/builds/REL8_2_STABLE/inst/lib/postgresql/pgxs/src' mkdir /Volumes/High/Usage mkdir: /Volumes/High/Usage: Permission denied mkdir /Volumes/High/Usage/usr mkdir: /Volumes/High/Usage: No such file or directory mkdir /Volumes/High/Usage/usr/local mkdir: /Volumes/High/Usage/usr: No such file or directory [etc] What is happening here of course is that mkinstalldirs is not prepared to deal with spaces in the given pathname. In 9.0 we replaced it with a more modern script that apparently hasn't got that problem. (Or at least, there's no such failure ... I don't see any reference to '/Volumes/High Usage/' in the later-version buildfarm logs, so maybe there is some other factor at work?) It looks to me like it wouldn't be terribly difficult to make mkinstalldirs handle this case correctly. We aren't normally in the habit of patching bugs that only appear in old release branches, but should we make an exception for this? The alternatives seem to be to ask Robert to rename the volume, or stop testing pre-9.0 branches on that machine. Neither seems like a great idea from a test coverage standpoint. We *should* have at least one test machine that's testing building and installation into space-containing paths, or the case is certain to break. 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] lazy vxid locks, v1
On Sun, Jun 12, 2011 at 2:39 PM, Robert Haas robertmh...@gmail.com wrote: ... Profiling reveals that the system spends enormous amounts of CPU time in s_lock. LWLOCK_STATS reveals that the only lwlock with significant amounts of blocking is the BufFreelistLock; This is curious. Clearly the entire working set fits in RAM, or you wouldn't be getting number like this. But does the entire working set fit in shared_buffers? If so, you shouldn't see any traffic on BufFreelistLock once all the data is read in. I've only seen contention here when all data fits in OS cache memory but not in shared_buffers. 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] Why polecat and colugos are failing to build back branches
On 06/13/2011 08:05 PM, Tom Lane wrote: I looked into $SUBJECT. There appear to be two distinct issues: 1. On colugos (OS X with LLVM), the plperl link step is spitting up because Apple's ExtUtils::Embed puts some -arch switches into perl_embed_ldflags. We found out about that some time ago, and fixed it for 9.0 and up here: http://git.postgresql.org/gitweb?p=postgresql.gita=commitdiffh=d69a419e682c2d39c2355105a7e5e2b90357c8f0 However, because when using gcc that only results in a warning, we didn't back-patch it. Now it appears that it's an error when using LLVM, so maybe we oughta back-patch it, at least to whichever releases we think will build with LLVM. 2. Pre-9.0, the installation step is failing like this: make -C src install /bin/sh ../config/mkinstalldirs '/Volumes/High Usage/usr/local/src/build-farm-4.4/builds/REL8_2_STABLE/pgsql.14435/src/test/regress/./tmp_check/install/usr/local/src/build-farm-4.4/builds/REL8_2_STABLE/inst/lib/postgresql/pgxs/src' mkdir /Volumes/High/Usage mkdir: /Volumes/High/Usage: Permission denied mkdir /Volumes/High/Usage/usr mkdir: /Volumes/High/Usage: No such file or directory mkdir /Volumes/High/Usage/usr/local mkdir: /Volumes/High/Usage/usr: No such file or directory [etc] What is happening here of course is that mkinstalldirs is not prepared to deal with spaces in the given pathname. In 9.0 we replaced it with a more modern script that apparently hasn't got that problem. (Or at least, there's no such failure ... I don't see any reference to '/Volumes/High Usage/' in the later-version buildfarm logs, so maybe there is some other factor at work?) It looks to me like it wouldn't be terribly difficult to make mkinstalldirs handle this case correctly. We aren't normally in the habit of patching bugs that only appear in old release branches, but should we make an exception for this? The alternatives seem to be to ask Robert to rename the volume, or stop testing pre-9.0 branches on that machine. Neither seems like a great idea from a test coverage standpoint. We *should* have at least one test machine that's testing building and installation into space-containing paths, or the case is certain to break. I think we can be a bit more liberal about build patches than things that can affect the runtime behaviour. So +1 for fixing both of these. 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: pgbench cpu overhead (was Re: [HACKERS] lazy vxid locks, v1)
On Mon, Jun 13, 2011 at 7:03 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: ... so it seems that sysbench is actually significantly less overhead than pgbench and the lower throughput at the higher conncurency seems to be cause by sysbench being able to stress the backend even more than pgbench can. Hi Stefan, pgbench sends each query (per connection) and waits for the reply before sending another. Do we know whether sysbench does that, or if it just stuffs the kernel's IPC buffer full of queries without synchronously waiting for individual replies? I can't get sysbench to make for me, or I'd strace in single client mode and see what kind of messages are going back and forth. 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
