Re: [HACKERS] B-tree parent pointer and checkpoints
On 11.11.2010 00:49, Tom Lane wrote: I wrote: What happens if you error out in between? Or is it assumed that the *entire* sequence is a critical section? If it has to be that way, one might wonder what's the point of trying to split it into multiple WAL records. Or, to be more concrete: I'm wondering if this *entire* mechanism isn't a bad idea that we should just rip out. The question that ought to be asked here, I think, is whether it shouldn't be required that every inter-WAL-record state is a valid consistent state that doesn't require post-crash fixups. If that isn't the case, then a simple ERROR or FATAL exit out of the backend that was creating the sequence originally will leave the system in an unacceptable state. We could prevent such an exit by wrapping the whole sequence in a critical section, but if you have to do that then it's not apparent why you shouldn't fold it into one WAL record. IOW, forget this patch. Take out the logic that tries to complete pending splits during replay, instead. I believe this is perfectly safe for btree: loss of a parent record isn't fatal, as proven by the fact that searches don't have to be locked out while a split proceeds. (We might want to make btree_page_del not think that a missing parent record is an error, but it shouldn't think that anyway, because of the possibility of a non-crashing failure during the original split.) This approach might not be safe for GIST or GIN; but if it isn't, they need fixes anyway. GIN is similar to b-tree, the incomplete split logic there is for inserting the parent pointers in the b-tree within the GIN index, just like nbtree. GiST is different. When you insert a key to a leaf page, you (sometimes) need to adjust the parent pointer to reflect the new key as well. B-tree tolerates incomplete splits with the 'next page' pointer, but that is not applicable to gist. Teodor described the issue back in 2005 when WAL-logging was added to GiST (http://archives.postgresql.org/pgsql-hackers/2005-06/msg00555.php): The problem with incopmleted inserts is: when new entry is installed into leaf page, all chain (in a worst case) of keys from root page to leaf should be updated. Of course, case of updating all chain is rarely and usially it's updated only part. Each key on inner pages contains union of keys (bounding box in a case of rtree, for example) on page which it points. This union can formed only with a help of user-defined function of opclass, because of GiST doesn't know something about nature of keys. Returning to WAL, GiST core write xlog entry with all nessary information for restoration before write page, but in this moment it doesn't know it should update keys on parent page or key is unchanged. So GiST's WAL restoration code should remember this page's update as incompleted insert. When insert complited, GiST's core write to log that insert is completed and restore code can clean up stored incompleted insert. If it was crash, then sign of completed insert can be absent in log, and GiST's restore code should continue it. While continue, it's know which page was changed and should walk up to root. On each step of walk it should form union for page and insert it to parent. Reading that I wonder: what harm would an incomplete insert cause if we just left it in the tree? Imagine that you insert a key to a leaf page, but crash before updating the parent. If you search for the key starting from the root, you'll fail to find it, because the parent pointer claims that there are no entries with such a key on the child page. But that's OK, the inserting transaction aborted with the crash! Do some of the other GiST algorithms get confused if there's a key on a page that's not represented by the parent pointer? It's possible that you insert a key to the leaf, update the leaf's immediate parent, but crash before updating the parent's parent. As far as search is concerned, that's OK as well, but it creates a hazard for subsequent inserts. If you later insert a tuple with the same key to the same leaf page, the insertion will see that the parent pointer already includes the key, and will fail to update the parent's parent. That's a problem. Would it be hard to change the algorithm to update the parent keys top-down rather than bottom-up? -- 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] B-tree parent pointer and checkpoints
On 11.11.2010 00:49, Tom Lane wrote: I wrote: What happens if you error out in between? Or is it assumed that the *entire* sequence is a critical section? If it has to be that way, one might wonder what's the point of trying to split it into multiple WAL records. Or, to be more concrete: I'm wondering if this *entire* mechanism isn't a bad idea that we should just rip out. The question that ought to be asked here, I think, is whether it shouldn't be required that every inter-WAL-record state is a valid consistent state that doesn't require post-crash fixups. If that isn't the case, then a simple ERROR or FATAL exit out of the backend that was creating the sequence originally will leave the system in an unacceptable state. We could prevent such an exit by wrapping the whole sequence in a critical section, but if you have to do that then it's not apparent why you shouldn't fold it into one WAL record. IOW, forget this patch. Take out the logic that tries to complete pending splits during replay, instead. I believe this is perfectly safe for btree: loss of a parent record isn't fatal, as proven by the fact that searches don't have to be locked out while a split proceeds. (We might want to make btree_page_del not think that a missing parent record is an error, but it shouldn't think that anyway, because of the possibility of a non-crashing failure during the original split.) This approach might not be safe for GIST or GIN; but if it isn't, they need fixes anyway. GIN is similar to b-tree, the incomplete split logic there is for inserting the parent pointers in the b-tree within the GIN index, just like nbtree. GiST is different. When you insert a key to a leaf page, you (sometimes) need to adjust the parent pointer to reflect the new key as well. B-tree tolerates incomplete splits with the 'next page' pointer, but that is not applicable to gist. Teodor described the issue back in 2005 when WAL-logging was added to GiST (http://archives.postgresql.org/pgsql-hackers/2005-06/msg00555.php): The problem with incopmleted inserts is: when new entry is installed into leaf page, all chain (in a worst case) of keys from root page to leaf should be updated. Of course, case of updating all chain is rarely and usially it's updated only part. Each key on inner pages contains union of keys (bounding box in a case of rtree, for example) on page which it points. This union can formed only with a help of user-defined function of opclass, because of GiST doesn't know something about nature of keys. Returning to WAL, GiST core write xlog entry with all nessary information for restoration before write page, but in this moment it doesn't know it should update keys on parent page or key is unchanged. So GiST's WAL restoration code should remember this page's update as incompleted insert. When insert complited, GiST's core write to log that insert is completed and restore code can clean up stored incompleted insert. If it was crash, then sign of completed insert can be absent in log, and GiST's restore code should continue it. While continue, it's know which page was changed and should walk up to root. On each step of walk it should form union for page and insert it to parent. Reading that I wonder: what harm would an incomplete insert cause if we just left it in the tree? Imagine that you insert a key to a leaf page, but crash before updating the parent. If you search for the key starting from the root, you'll fail to find it, because the parent pointer claims that there are no entries with such a key on the child page. But that's OK, the inserting transaction aborted with the crash! Do some of the other GiST algorithms get confused if there's a key on a page that's not represented by the parent pointer? It's possible that you insert a key to the leaf, update the leaf's immediate parent, but crash before updating the parent's parent. As far as search is concerned, that's OK as well, but it creates a hazard for subsequent inserts. If you later insert a tuple with the same key to the same leaf page, the insertion will see that the parent pointer already includes the key, and will fail to update the parent's parent. That's a problem. Would it be hard to change the algorithm to update the parent keys top-down rather than bottom-up? -- 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] improved parallel make support
On Wed, Nov 10, 2010 at 6:13 PM, Andrew Dunstan and...@dunslane.net wrote: Yeah, it's complaining about not finding bison, but configure managed to find bison just fine. Are you sure the right make was installed? It looks suspicious because it's not talking about msys virtual maths like the old make did. It needs to be make-3.81-3-msys-1.0.13 http://sourceforge.net/projects/mingw/files/MSYS/make/make-3.81-3/make-3.81-3-msys-1.0.13-bin.tar.lzma/download You'll need another couple of libraries as well (libiconv and libintl) if they are not already installed. Making this change took me a while to get right on dawn_bat. I installed the latest make from gnu.org (which I've now uninstalled). The Msys installation on this box is old, and doesn't support the lzma packages used by the latest releases - and from what I can tell, it would take a major upgrade of the installation to get that support. I'm not sure thats a path I want to go down, as I have no idea how much will break if I do that, and I don't exactly have much in the way of spare time to fix it if that happens. I'm currently leaning towards removing the 9.1 build from the machine; on a purely selfish note, I have no interest in mingw/msys builds anymore anyway. However, I'm open to suggestions if anyone knows a relatively safe way to resolve this. /D -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] improved parallel make support
On 11/11/2010 06:58 AM, Dave Page wrote: On Wed, Nov 10, 2010 at 6:13 PM, Andrew Dunstanand...@dunslane.net wrote: Yeah, it's complaining about not finding bison, but configure managed to find bison just fine. Are you sure the right make was installed? It looks suspicious because it's not talking about msys virtual maths like the old make did. It needs to be make-3.81-3-msys-1.0.13 http://sourceforge.net/projects/mingw/files/MSYS/make/make-3.81-3/make-3.81-3-msys-1.0.13-bin.tar.lzma/download You'll need another couple of libraries as well (libiconv and libintl) if they are not already installed. Making this change took me a while to get right on dawn_bat. I installed the latest make from gnu.org (which I've now uninstalled). The Msys installation on this box is old, and doesn't support the lzma packages used by the latest releases - and from what I can tell, it would take a major upgrade of the installation to get that support. I'm not sure thats a path I want to go down, as I have no idea how much will break if I do that, and I don't exactly have much in the way of spare time to fix it if that happens. I'm currently leaning towards removing the 9.1 build from the machine; on a purely selfish note, I have no interest in mingw/msys builds anymore anyway. However, I'm open to suggestions if anyone knows a relatively safe way to resolve this. No, all you need to unpack those is the basic-bsdtar package. But to save you the pain of all this, I have copied the three objects I installed to get this working on my likewise pretty old Msys to where you can get them. Just grab http://developer.postgresql.org/~adunstan/msys-make.tgz As a matter of policy, I do not want to drop support for a FOSS build tool chain on Windows if at all avoidable. 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] multi-platform, multi-locale regression tests
Robert Haas robertmh...@gmail.com writes: I think the big hurdle with contrib isn't that it's called contrib but that it's not part of the core server and, in many cases, enabling a contrib module means editing postgresql.conf and bouncing the server. Of course, there are certainly SOME people who wouldn't mind editing postgresql.conf and bouncing the server but are scared off by the name contrib, but I suspect the hassle-factor is the larger issue by a substantial margin. You're forgetting about the dump and restore problems you now have as soon as you're using any contrib. They are more visible at upgrade time, of course, but still bad enough otherwise. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renaming contrib.
Robert Haas robertmh...@gmail.com writes: work will help with that somewhat, but there's still that nasty business of needing to update shared_preload_libraries and bounce the server, at least for some modules. We have 45 contribs (ls -l contrib | grep -c ^d), out of which: auto_explain is shared_preload_libraries but I think could be local_preload_libraries pg_stat_statements is shared_preload_libraries (needs SHM) and that's it So my reading is that currently the only contrib module that needs more than a server reload is pg_stat_statements, because it needs some shared memory. Am I missing anything? Ok, now I'll add the custom_variable_classes setting to the control files in the extension's patch for the contribs that expose some of them. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renaming contrib. (was multi-platform, multi-locale regression tests)
On 11/10/2010 07:51 PM, Robert Haas wrote: (And no, don't you dare breathe a word about git making that all automagically better. I have enough back-patching experience with git by now to be unimpressed; in fact, I notice that its rename-tracking feature falls over entirely when trying to back-patch further than 8.3. Apparently there's some hardwired limit on the number of files it can cope with.) That's very sad. Did you file a bug? It's intentional behavior. It gives up when there are too many differences to avoid being slow. We should adopt that philosophy. I suggest we limit all tables in future to 1m rows in the interests of speed. 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] improved parallel make support
On Thu, Nov 11, 2010 at 1:04 PM, Andrew Dunstan and...@dunslane.net wrote: No, all you need to unpack those is the basic-bsdtar package. Ahh, OK. That seems to be in the MinGW (compiler) section of the downloads for some reason. But to save you the pain of all this, I have copied the three objects I installed to get this working on my likewise pretty old Msys to where you can get them. Just grab http://developer.postgresql.org/~adunstan/msys-make.tgz Thanks - installed. As a matter of policy, I do not want to drop support for a FOSS build tool chain on Windows if at all avoidable. Nor I, however I only have limited time to dedicate to that goal. -- 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] Exposing an installation's default value of unix_socket_directory
Peter Eisentraut pete...@gmx.net writes: On tor, 2010-10-21 at 16:59 -0400, Tom Lane wrote: Actually, the only reason this is even up for discussion is that there's no configure option to set DEFAULT_PGSOCKET_DIR. If there were, and debian were using it, then pg_config --configure would tell what I wish to know. I thought for a bit about proposing we add such an option, but given the current state of play it might be more misleading than helpful: as long as distros are accustomed to changing this setting via a patch, you couldn't trust pg_config --configure to tell you what a given installation actually has compiled into it. Presumably, if a configure option were added, they couldn't change it via patch anymore. Hm, you're right: we'd remove the pg_config_manual.h entry, so the existing patches would stop working, and presumably maintainers would figure out that they ought to use the configure switch instead. So that argument holds little water. Btw., a configure option for this was rejected years ago to discourage people from actually changing the default. Yeah, I remember that discussion now that you mention it. It still seems like a good policy ... but given that some popular packages are changing the default whether we think it's a good idea or not, maybe it's better to acknowledge that reality. We could still have some text in the manual pointing out the compatibility hazards of using the switch, I guess. 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] MULTISET and additional functions for ARRAY
Postgres supports ARRAY data types well, but there are some more array functions in the SQL standard. Also, the standard has MULTISET data type, that is an unordered array. It looks easy to support additional array functions. There might be some confusion to treat multi-dimensional arrays with them, but we could treat all arrays as one-dimensional like as unnest(). MULTISET supports are more difficult. We have corresponding type IDs for each array, but we might not want to add additional IDs for multiset for each type. Any ideas for the issue? If we reuse type IDs of arrays for multisets, the multisets would have some special typmod. For example, typmod = 0 means multiset, and positive value means array with max cardinality. Note that the SQL standard doesn't mention about multi-dimensional arrays. So, we can use typmod = -1 as a free-size and free-dimensional array for backward compatibility. If we have troublesome issues to support multiset data types, I'm thinking to add multiset functions that receives ARRAY types instead at time first time, because an ARRAY is a MULTISET by definition. Some of functions for multisets seems to be useful for arrays, too. Comments and suggestions welcome. === Array functions === - [FUNCTION] cardinality(anyarray) = integer - [FUNCTION] trim_array(anyarray, nTrimmed integer) = anyarray === Multiset functions === - [FUNCTION] cardinality(anymultiset) = integer - [FUNCTION] element(anymultiset) = anyelement - [FUNCTION] multiset_member_of(anymultiset, anyelement) = boolean [SYNTAX] $2 MEMBER OF $1 - [FUNCTION] multiset_is_a_set(anymultiset) = boolean [SYNTAX] $1 IS A SET - [FUNCTION] multiset_sub_multiset_of(anymultiset, anymultiset) = boolean [SYNTAX] $2 SUB MULTISET OF $1 - [FUNCTION] multiset_union[_all](anymultiset, anymultiset) = anymultiset [SYNTAX] $1 MULTISET UNION [ALL | DISTINCT] $2 - [FUNCTION] multiset_intersect[_all](anymultiset, anymultiset) = anymultiset [SYNTAX] $1 MULTISET INTERSECT [ALL | DISTINCT] $2 - [FUNCTION] multiset_except[_all](anymultiset, anymultiset) = anymultiset [SYNTAX] $1 MULTISET EXCEPT [ALL | DISTINCT] $2 - [AGGREGATE] collect(anyelement) = anymultiset - [AGGREGATE] fusion(anymultiset) = anymultiset - [AGGREGATE] intersection(anymultiset) = anymultiset See also secondary sources. http://waelchatila.com/2005/05/18/1116485743467.html http://farrago.sourceforge.net/design/CollectionTypes.html http://publib.boulder.ibm.com/infocenter/db2luw/v9r8/index.jsp?topic=/com.ibm.db2.luw.apdv.sqlpl.doc/doc/t0053486.html http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/conditions006.htm http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/operators006.htm -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BUG #5748: Invalid oidvector data during binary recv
Hello list,
Sorry for not replying to the bug list, but I didn't receive that
message. It's about
http://archives.postgresql.org/pgsql-bugs/2010-11/msg00065.php
The test case there with remark about LBOUND is incorrect; we first
found the bug on a different result. In the process of finding the most
simple test case we apparantly found one where another check failed
(ARR_NDIM(result) 1)
The following testcase has a value for ndim that passes the check, but
an lbound value of 0 right after the call of array_recv in oidvectorrecv:
postgres=# copy (select '{1}'::oidvector[]) to '/tmp/test' with binary;
COPY 1
postgres=# copy a from '/tmp/test' with binary;
(gdb) p *result
$6 = {vl_len_ = 112, ndim = 1, dataoffset = 0, elemtype = 26, dim1 = 1,
lbound1 = 0, values = {1}}
The same behaviour is also seen in int2vectorrecv.
regards,
Yeb Havinga
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] B-tree parent pointer and checkpoints
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: GiST is different. When you insert a key to a leaf page, you (sometimes) need to adjust the parent pointer to reflect the new key as well. B-tree tolerates incomplete splits with the 'next page' pointer, but that is not applicable to gist. Teodor described the issue back in 2005 when WAL-logging was added to GiST (http://archives.postgresql.org/pgsql-hackers/2005-06/msg00555.php): Reading that I wonder: what harm would an incomplete insert cause if we just left it in the tree? Imagine that you insert a key to a leaf page, but crash before updating the parent. If you search for the key starting from the root, you'll fail to find it, because the parent pointer claims that there are no entries with such a key on the child page. But that's OK, the inserting transaction aborted with the crash! I think it'd be okay as far as that one entry is concerned, since as you say it doesn't matter whether a search finds it. (We'd have to be sure that VACUUM would still find it to remove it, of course, but that doesn't use a normal search.) You're right that it poses a hazard of subsequent inserts deciding that they don't need to do work on upper levels because the lower ones look OK already. But depending on the details of the search algorithm, this might be a non-problem: if you remember that the upper level entries didn't cover your key when you descended, you'd still know you need to recompute them. Something else I just noticed is that WAL replay isn't capable of completely fixing the index anyway: * To complete insert we can't use basic insertion algorithm because * during insertion we can't call user-defined support functions of opclass. * So, we insert 'invalid' tuples without real key and do it by separate algorithm. * 'invalid' tuple should be updated by vacuum full. Given that there's no more vacuum full, and nobody has been expected to run it routinely for a long time anyway, this fixup approach seems pretty completely broken anyhow. 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] renaming contrib. (was multi-platform, multi-locale regression tests)
On Thu, Nov 11, 2010 at 8:28 AM, Andrew Dunstan and...@dunslane.net wrote: It's intentional behavior. It gives up when there are too many differences to avoid being slow. And, it's configurable, at least to diff and merge. If it's not available in all the other porcelains, yes, that would be bugs that should be fixed: -lnum The -M and -C options require O(n^2) processing time where n is the number of potential rename/copy targets. This option prevents rename/copy detection from running if the number of rename/copy targets exceeds the specified number. And can even be specified as config options diff.renameLimit and merge.renameLimit. We should adopt that philosophy. I suggest we limit all tables in future to 1m rows in the interests of speed. As long as it's configurable, and if it would make operations on smaller tables faster, than go for it. And we should by defualt limit shared_buffers to 32MB. Oh wait. There are always tradeoffs when picking defaults, a-la-postgresql.conf. We as a community are generally pretty quick to pick up the defaults are very conservative, make sure you tune ... song when people complain about pg being too slow ;-) 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] renaming contrib. (was multi-platform, multi-locale regression tests)
Aidan Van Dyk ai...@highrise.ca writes: It's intentional behavior. It gives up when there are too many differences to avoid being slow. And, it's configurable, at least to diff and merge. If it's not available in all the other porcelains, yes, that would be bugs that should be fixed: FWIW, I was seeing this with git cherry-pick, whose man page gives no hint of supporting any such option. -lnum The -M and -C options require O(n^2) processing time where n is the number of potential rename/copy targets. This option prevents rename/copy detection from running if the number of rename/copy targets exceeds the specified number. Given that we have, in fact, never renamed any files in the history of the project, I'm wondering exactly why it thinks that the number of potential rename/copy targets isn't zero. The whole thing smells broken to me, which is why I am unhappy about the idea of suddenly starting to depend on it in a big way. 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] renaming contrib. (was multi-platform, multi-locale regression tests)
On Thu, Nov 11, 2010 at 17:24, Tom Lane t...@sss.pgh.pa.us wrote: Given that we have, in fact, never renamed any files in the history of the project, I'm wondering exactly why it thinks that the number of potential rename/copy targets isn't zero. The whole thing smells broken to me, which is why I am unhappy about the idea of suddenly starting to depend on it in a big way. Because git doesn't do rename tracking at all -- a rename operation is no different from a delete+add operation. Instead it tracks how lines of code move around in the tree: https://git.wiki.kernel.org/index.php/GitFaq#Why_does_git_not_.22track.22_renames.3F Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renaming contrib. (was multi-platform, multi-locale regression tests)
Aidan Van Dyk ai...@highrise.ca writes: Can you share what commit you were trying to cherry-pick, and what your resulting commit was? I can try and take a quick look at them and see if there is something obviously fishy with how git's trying to merge the new commit on the old tree... See yesterday's line_construct_pm() patches. I committed in HEAD and then did git cherry-pick master in each back branch. These all worked, which would be the minimum expectation for a single-file patch against a function that hasn't changed since 1999. But in the older branches it bleated about shutting off rename detection because of too many files (sorry, don't have the exact message in front of me, but that was the gist of it). Not the sort of thing that gives one a warm feeling about the tool. I've seen this before when trying to use git cherry-pick, but I forget on which other patches exactly. Oh, for the record: $ git --version git version 1.7.3 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] renaming contrib. (was multi-platform, multi-locale regression tests)
Marti Raudsepp ma...@juffo.org writes: On Thu, Nov 11, 2010 at 17:24, Tom Lane t...@sss.pgh.pa.us wrote: Given that we have, in fact, never renamed any files in the history of the project, I'm wondering exactly why it thinks that the number of potential rename/copy targets isn't zero. Because git doesn't do rename tracking at all -- a rename operation is no different from a delete+add operation. Instead it tracks how lines of code move around in the tree: https://git.wiki.kernel.org/index.php/GitFaq#Why_does_git_not_.22track.22_renames.3F Hmmm ... so rename tracking is O(N^2) in the total number of patches applied, or lines patched, or some such measure, between the branches you're trying to patch between? Ugh. Doesn't sound like something we want to grow dependent on. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] renaming contrib. (was multi-platform, multi-locale regression tests)
On 11/11/2010 10:17 AM, Aidan Van Dyk wrote: We should adopt that philosophy. I suggest we limit all tables in future to 1m rows in the interests of speed. As long as it's configurable, and if it would make operations on smaller tables faster, than go for it. And we should by defualt limit shared_buffers to 32MB. Oh wait. There are always tradeoffs when picking defaults, a-la-postgresql.conf. We as a community are generally pretty quick to pick up the defaults are very conservative, make sure you tune ... song when people complain about pg being too slow ;-) Well, I was of course being facetious. But since you mention it, Postgres is conservative about its defaults because it's a server. I don't think quite the same considerations apply to developer software that will be running on a workstation. And Tom's complaint was about what he saw as incorrect behavior. Our defaults might hurt performance, but I don't think they trade speed for incorrect behavior. Anyway, revenons à nos moutons. 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] Re: [COMMITTERS] pgsql: Don't unblock SIGQUIT in the SIGQUIT handler This was possibly
On ons, 2010-11-10 at 20:30 +0900, Fujii Masao wrote: On Thu, Dec 17, 2009 at 8:05 AM, Peter Eisentraut pet...@postgresql.org wrote: Log Message: --- Don't unblock SIGQUIT in the SIGQUIT handler This was possibly linked to a deadlock-like situation in glibc syslog code invoked by the ereport call in quickdie(). In any case, a signal handler should not unblock its own signal unless there is a specific reason to. Modified Files: -- pgsql/src/backend/tcop: postgres.c (r1.577 - r1.578) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/postgres.c?r1=1.577r2=1.578) pgsql/src/include/libpq: pqsignal.h (r1.35 - r1.36) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/libpq/pqsignal.h?r1=1.35r2=1.36) Why wasn't this patch backported? Recently my customer encountered the bug which this patch fixed, in 8.3. It was at the time a perhaps experimental behavioral change. I'm also running a 8.3 version with this patched in, though. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote: Hi all, The discussion around wCTE during the last week or so has brought to my attention that we don't actually have a consensus on how exactly wCTEs should behave. The question seems to be whether or not a statement should see the modifications of statements ran before it. While I think making the modifications visible would be a lot more intuitive, it's not clear how we'd optimize the execution in the future without changing the behaviour (triggers are a big concern). +1 for letting writeable CTEs see the results of previous CTEs, just as current non-writeable ones do. A lot of the useful cases for this feature depend on this visibility. 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] improved parallel make support
Dave Page wrote: Thanks - installed. As a matter of policy, I do not want to drop support for a FOSS build tool chain on Windows if at all avoidable. Nor I, however I only have limited time to dedicate to that goal. One thing to think about is that since PostGIS uses MingW/PGXS on Windows, we use MingW builds in order to generate the Makefiles we need (there is no native MSVC build for Windows). Not being able to do this would cause us great inconvenience :( ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On 2010-11-11 6:41 PM +0200, David Fetter wrote: On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote: The discussion around wCTE during the last week or so has brought to my attention that we don't actually have a consensus on how exactly wCTEs should behave. The question seems to be whether or not a statement should see the modifications of statements ran before it. While I think making the modifications visible would be a lot more intuitive, it's not clear how we'd optimize the execution in the future without changing the behaviour (triggers are a big concern). +1 for letting writeable CTEs see the results of previous CTEs, just as current non-writeable ones do. A lot of the useful cases for this feature depend on this visibility. Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] improved parallel make support
On 11/11/2010 11:43 AM, Mark Cave-Ayland wrote: Dave Page wrote: Thanks - installed. As a matter of policy, I do not want to drop support for a FOSS build tool chain on Windows if at all avoidable. Nor I, however I only have limited time to dedicate to that goal. One thing to think about is that since PostGIS uses MingW/PGXS on Windows, we use MingW builds in order to generate the Makefiles we need (there is no native MSVC build for Windows). Not being able to do this would cause us great inconvenience :( Interesting. Doesn't EDB's PostgresPlus package include PostGIS, and isn't its Windows version build with MSVC? 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] wCTE behaviour
On 11 November 2010 16:50, Marko Tiikkaja marko.tiikk...@cs.helsinki.fiwrote: On 2010-11-11 6:41 PM +0200, David Fetter wrote: On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote: The discussion around wCTE during the last week or so has brought to my attention that we don't actually have a consensus on how exactly wCTEs should behave. The question seems to be whether or not a statement should see the modifications of statements ran before it. While I think making the modifications visible would be a lot more intuitive, it's not clear how we'd optimize the execution in the future without changing the behaviour (triggers are a big concern). +1 for letting writeable CTEs see the results of previous CTEs, just as current non-writeable ones do. A lot of the useful cases for this feature depend on this visibility. Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? I would expect that select to return nothing. And if the user wished to reference what was deleted, they could use RETURNING anyway. /probable ignorance WITH t AS (UPDATE foo SET col = true) SELECT * FROM foo WHERE col = false; ... Wouldn't this be more practical to have foo's UPDATEs applied prior to SELECT? Otherwise what would the usecase be? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Re: [HACKERS] improved parallel make support
On Thu, Nov 11, 2010 at 4:51 PM, Andrew Dunstan and...@dunslane.net wrote: Interesting. Doesn't EDB's PostgresPlus package include PostGIS, and isn't its Windows version build with MSVC? Yes - it's a PITA as we have to have a dummy build of the server in mingw/msys to compile PostGIS and Slony. We're probably going to be looking at that in the not-to-distant future as we want 64bit builds of both and will be using VC++. -- 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] wCTE behaviour
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes:
On 2010-11-11 6:41 PM +0200, David Fetter wrote:
On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote:
The discussion around wCTE during the last week or so has brought to
my attention that we don't actually have a consensus on how exactly
wCTEs should behave. The question seems to be whether or not a
statement should see the modifications of statements ran before it.
+1 for letting writeable CTEs see the results of previous CTEs, just
as current non-writeable ones do. A lot of the useful cases for this
feature depend on this visibility.
Just to be clear, the main point is whether they see the data
modifications or not. The simplest case to point out this behaviour is:
WITH t AS (DELETE FROM foo)
SELECT * FROM foo;
And the big question is: what state of foo should the SELECT statement
see?
You've already predetermined the outcome of the argument by phrasing it
that way: if you assume that the CTE runs before the main statement
then the conclusion is foregone. To my mind, they should be thought of
as running in parallel, or at least in an indeterminate order, just
exactly the same way that different data modifications made in a single
INSERT/UPDATE/DELETE command are considered to be made simultaneously.
If someone came to us and complained because his ON UPDATE trigger
couldn't reliably see changes made to other rows by the same UPDATE
command, and could we please make UPDATE more deterministic, we'd
tell him to rethink what he was doing. This is the same thing.
It is already the case that a user who pushes on things hard enough can
see that a WITH isn't really run before the main command. For
example,
regression=# create sequence s1;
CREATE SEQUENCE
regression=# with tt(x,y) as (select x, nextval('s1') from
generate_series(1,10) x)
regression-# select x,y, nextval('s1') as z from tt;
x | y | z
++
1 | 1 | 2
2 | 3 | 4
3 | 5 | 6
4 | 7 | 8
5 | 9 | 10
6 | 11 | 12
7 | 13 | 14
8 | 15 | 16
9 | 17 | 18
10 | 19 | 20
(10 rows)
If we establish a precedent that WITHs can be thought of as executing
before the main command, we will eventually have to de-optimize existing
WITH behavior. Or else make up reasons why the inconsistency is okay in
some cases and not others, but that will definitely be a case of
rationalizing after the fact.
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] wCTE behaviour
On Nov 11, 2010, at 9:13 AM, Tom Lane wrote: If we establish a precedent that WITHs can be thought of as executing before the main command, we will eventually have to de-optimize existing WITH behavior. Or else make up reasons why the inconsistency is okay in some cases and not others, but that will definitely be a case of rationalizing after the fact. I can see that, but if one can't see the result of the write, or can't determine whether or not it will be visible in advance, what's the point of writeable CTEs? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] improved parallel make support
Dave Page wrote: On Thu, Nov 11, 2010 at 4:51 PM, Andrew Dunstan and...@dunslane.net wrote: Interesting. Doesn't EDB's PostgresPlus package include PostGIS, and isn't its Windows version build with MSVC? Yes - it's a PITA as we have to have a dummy build of the server in mingw/msys to compile PostGIS and Slony. We're probably going to be looking at that in the not-to-distant future as we want 64bit builds of both and will be using VC++. Just for the record, a lot of work was done in the 1.4 release series to make MSVC builds possible, and indeed several people have reported success: http://postgis.refractions.net/pipermail/postgis-devel/2009-March/005102.html http://postgis.refractions.net/pipermail/postgis-devel/2010-September/010299.html The two main outstanding issues as I see it are: 1) The GTK-based GUI for shp2pgsql (although if someone wanted to sponsor work to convert to wxWidgets to bring us in line with pgAdmin, that would be strongly considered). 2) Maintenance of the MSVC build system. So far we have had some complaints about not using MSVC, but then no-one has stepped up to maintain the build system for it. Forcing all existing developers to suddenly start maintaining the Windows build is a total non-starter. My hope is that one day CMake will enable us to come up with a universal solution, but we're some way from that yet. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MULTISET and additional functions for ARRAY
On Nov 11, 2010, at 7:02 AM, Itagaki Takahiro wrote: MULTISET supports are more difficult. We have corresponding type IDs for each array, but we might not want to add additional IDs for multiset for each type. Any ideas for the issue? Why not? If we reuse type IDs of arrays for multisets, the multisets would have some special typmod. For example, typmod = 0 means multiset, and positive value means array with max cardinality. Note that the SQL standard doesn't mention about multi-dimensional arrays. So, we can use typmod = -1 as a free-size and free-dimensional array for backward compatibility. If we have troublesome issues to support multiset data types, I'm thinking to add multiset functions that receives ARRAY types instead at time first time, because an ARRAY is a MULTISET by definition. An array is a superset of MULTISET, I guess? Some of functions for multisets seems to be useful for arrays, too. Comments and suggestions welcome. So are you planning to implement multisets? It's a feature I'd love to see… Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
David E. Wheeler da...@kineticode.com writes: I can see that, but if one can't see the result of the write, or can't determine whether or not it will be visible in advance, what's the point of writeable CTEs? The writeable CTE returns a RETURNING set, which you can and should use in the outer query. The thing that is being argued about here is what you see if you look directly at the target table rather than making use of RETURNING. Essentially, I'm arguing that we shouldn't promise any particular behavior at that level, just as we don't promise that UPDATE updates different rows in any determinate order. 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] wCTE behaviour
Thom Brown t...@linux.com writes: WITH t AS (UPDATE foo SET col = true) SELECT * FROM foo WHERE col = false; ... Wouldn't this be more practical to have foo's UPDATEs applied prior to SELECT? Otherwise what would the usecase be? If that's what you want, you might as well just issue two separate statements. There is no use-case for this at all unless the WITH produces some RETURNING data that the SELECT makes use of. 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] wCTE behaviour
On 11 Nov 2010, at 19:13, Tom Lane t...@sss.pgh.pa.us wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: On 2010-11-11 6:41 PM +0200, David Fetter wrote: On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote: The discussion around wCTE during the last week or so has brought to my attention that we don't actually have a consensus on how exactly wCTEs should behave. The question seems to be whether or not a statement should see the modifications of statements ran before it. +1 for letting writeable CTEs see the results of previous CTEs, just as current non-writeable ones do. A lot of the useful cases for this feature depend on this visibility. Just to be clear, the main point is whether they see the data modifications or not. The simplest case to point out this behaviour is: WITH t AS (DELETE FROM foo) SELECT * FROM foo; And the big question is: what state of foo should the SELECT statement see? You've already predetermined the outcome of the argument by phrasing it that way: if you assume that the CTE runs before the main statement then the conclusion is foregone. To my mind, they should be thought of as running in parallel, or at least in an indeterminate order, just exactly the same way that different data modifications made in a single INSERT/UPDATE/DELETE command are considered to be made simultaneously. .. If we establish a precedent that WITHs can be thought of as executing before the main command, we will eventually have to de-optimize existing WITH behavior. Or else make up reasons why the inconsistency is okay in some cases and not others, but that will definitely be a case of rationalizing after the fact. I apologize, I had misunderstood what you are suggesting. But now that I do, it seems to be an even worse idea to go your way. Based on my research, I'm almost certain that the SQL standard says that the execution order is deterministic if there is at least one DML statement in the WITH list. Can anyone confirm this? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Thu, Nov 11, 2010 at 12:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: then the conclusion is foregone. To my mind, they should be thought of as running in parallel, or at least in an indeterminate order, just exactly the same way that different data modifications made in a single INSERT/UPDATE/DELETE command are considered to be made simultaneously. +1 merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Nov 11, 2010, at 9:29 AM, Tom Lane wrote: I can see that, but if one can't see the result of the write, or can't determine whether or not it will be visible in advance, what's the point of writeable CTEs? The writeable CTE returns a RETURNING set, which you can and should use in the outer query. The thing that is being argued about here is what you see if you look directly at the target table rather than making use of RETURNING. Essentially, I'm arguing that we shouldn't promise any particular behavior at that level, just as we don't promise that UPDATE updates different rows in any determinate order. Yes, if RETURNING guarantees the execution order, then great. That was the first thing I tried to do before I realized that the current CTE implementation doesn't support w. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MULTISET and additional functions for ARRAY
David E. Wheeler da...@kineticode.com writes: So are you planning to implement multisets? It's a feature I'd love to see What actual functionality does it buy? AFAICT from Itagaki-san's description, it's an array only you ignore the specific element order. So what? You can write functions that work that way now. 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] MULTISET and additional functions for ARRAY
On Nov 11, 2010, at 10:05 AM, Tom Lane wrote: So are you planning to implement multisets? It's a feature I'd love to see What actual functionality does it buy? AFAICT from Itagaki-san's description, it's an array only you ignore the specific element order. So what? You can write functions that work that way now. Also, no dupes. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
David E. Wheeler da...@kineticode.com writes: On Nov 11, 2010, at 9:29 AM, Tom Lane wrote: The writeable CTE returns a RETURNING set, which you can and should use in the outer query. The thing that is being argued about here is what you see if you look directly at the target table rather than making use of RETURNING. Essentially, I'm arguing that we shouldn't promise any particular behavior at that level, just as we don't promise that UPDATE updates different rows in any determinate order. Yes, if RETURNING guarantees the execution order, then great. That was the first thing I tried to do before I realized that the current CTE implementation doesn't support w. Well, it doesn't guarantee the execution order, it's just that that's the defined conduit for getting information out of the WITH and into the parent query. Looking directly at the table is not that conduit. I misspoke by saying that the behavior would be nondeterministic. What I think we should do is run all elements of the tree with the same snapshot, which would provide perfectly deterministic behavior: if you look at the target table, you see the prior state. You don't see the updated state, which is what allows us to possibly optimize things so that the updates aren't completely made before execution of the main query starts. 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] B-tree parent pointer and checkpoints
On 11.11.2010 17:16, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: GiST is different. When you insert a key to a leaf page, you (sometimes) need to adjust the parent pointer to reflect the new key as well. B-tree tolerates incomplete splits with the 'next page' pointer, but that is not applicable to gist. Teodor described the issue back in 2005 when WAL-logging was added to GiST (http://archives.postgresql.org/pgsql-hackers/2005-06/msg00555.php): Reading that I wonder: what harm would an incomplete insert cause if we just left it in the tree? Imagine that you insert a key to a leaf page, but crash before updating the parent. If you search for the key starting from the root, you'll fail to find it, because the parent pointer claims that there are no entries with such a key on the child page. But that's OK, the inserting transaction aborted with the crash! I think it'd be okay as far as that one entry is concerned, since as you say it doesn't matter whether a search finds it. (We'd have to be sure that VACUUM would still find it to remove it, of course, but that doesn't use a normal search.) You're right that it poses a hazard of subsequent inserts deciding that they don't need to do work on upper levels because the lower ones look OK already. But depending on the details of the search algorithm, this might be a non-problem: if you remember that the upper level entries didn't cover your key when you descended, you'd still know you need to recompute them. Hmm, we don't currently keep track of that when we descend the tree to choose the target page, but perhaps an extra Consistent call to check that would be acceptable. We already call Penalty for every tuple on each internal node on the way, so compared to that one more call should not be too expensive. If we do that, I think it would simplify the algorithm quite a bit to just update all the parents on the way down, instead of traversing up from the bottom after inserting the tuple to the leaf. Something else I just noticed is that WAL replay isn't capable of completely fixing the index anyway: * To complete insert we can't use basic insertion algorithm because * during insertion we can't call user-defined support functions of opclass. * So, we insert 'invalid' tuples without real key and do it by separate algorithm. * 'invalid' tuple should be updated by vacuum full. Given that there's no more vacuum full, and nobody has been expected to run it routinely for a long time anyway, this fixup approach seems pretty completely broken anyhow. The 'invalid' tuples don't affect correctness, but are a drag on performance, so they are similar to incomplete b-tree splits. I suspect the overhead of an invalid gist pointer is much bigger than the overhead of an incomplete b-tree split, though. I agree we should get rid of that, it's not comforting to get a stream of messages in the logs saying you should run VACUUM FULL. -- 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] MULTISET and additional functions for ARRAY
I think that it would be best to implement MULTISET in the same way that a TABLE is implemented. Logically and structurally they are the same thing, but that a MULTISET typically is used as a field value of a table row. Aka, a table and a multiset are just different names for a relation, loosely speaking. The association of a multiset-typed attribute of a table with said table is like the association of a child and parent table in a many-to-one. So reuse your structure for tables to hold multisets. -- Darren Duncan Itagaki Takahiro wrote: Postgres supports ARRAY data types well, but there are some more array functions in the SQL standard. Also, the standard has MULTISET data type, that is an unordered array. It looks easy to support additional array functions. There might be some confusion to treat multi-dimensional arrays with them, but we could treat all arrays as one-dimensional like as unnest(). MULTISET supports are more difficult. We have corresponding type IDs for each array, but we might not want to add additional IDs for multiset for each type. Any ideas for the issue? If we reuse type IDs of arrays for multisets, the multisets would have some special typmod. For example, typmod = 0 means multiset, and positive value means array with max cardinality. Note that the SQL standard doesn't mention about multi-dimensional arrays. So, we can use typmod = -1 as a free-size and free-dimensional array for backward compatibility. If we have troublesome issues to support multiset data types, I'm thinking to add multiset functions that receives ARRAY types instead at time first time, because an ARRAY is a MULTISET by definition. Some of functions for multisets seems to be useful for arrays, too. Comments and suggestions welcome. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MULTISET and additional functions for ARRAY
2010/11/11 David E. Wheeler da...@kineticode.com: On Nov 11, 2010, at 10:05 AM, Tom Lane wrote: So are you planning to implement multisets? It's a feature I'd love to see What actual functionality does it buy? AFAICT from Itagaki-san's description, it's an array only you ignore the specific element order. So what? You can write functions that work that way now. Also, no dupes. The multi in multiset indicates that duplicate elements are explicitly allowed and tracked. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] B-tree parent pointer and checkpoints
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Hmm, we don't currently keep track of that when we descend the tree to choose the target page, but perhaps an extra Consistent call to check that would be acceptable. We already call Penalty for every tuple on each internal node on the way, so compared to that one more call should not be too expensive. If we do that, I think it would simplify the algorithm quite a bit to just update all the parents on the way down, instead of traversing up from the bottom after inserting the tuple to the leaf. Oh, that's a really good idea, I think. But what about page splits? I guess in the case of a split, you'd be replacing the parent entry anyway, so having previously updated it to something larger doesn't really cause a problem other than wasting a few cycles --- which are probably still less than you save by not having to traverse back up. If we supported UNIQUE GIST indexes then you could criticize this plan on the grounds that parent entries would get uselessly enlarged before detecting a uniqueness failure; but we don't and I know of no plans to. So on the whole I think it sounds good. Teodor, what do you think? 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] MULTISET and additional functions for ARRAY
On Nov 11, 2010, at 10:19 AM, Darren Duncan wrote: I think that it would be best to implement MULTISET in the same way that a TABLE is implemented. Logically and structurally they are the same thing, but that a MULTISET typically is used as a field value of a table row. Aka, a table and a multiset are just different names for a relation, loosely speaking. That sounds like a composite type to me. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MULTISET and additional functions for ARRAY
On Nov 11, 2010, at 10:24 AM, Nicolas Barbier wrote: Also, no dupes. The multi in multiset indicates that duplicate elements are explicitly allowed and tracked. D'oh! Right. D -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Thu, Nov 11, 2010 at 12:36:38PM -0500, Merlin Moncure wrote: On Thu, Nov 11, 2010 at 12:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: then the conclusion is foregone. To my mind, they should be thought of as running in parallel, or at least in an indeterminate order, just exactly the same way that different data modifications made in a single INSERT/UPDATE/DELETE command are considered to be made simultaneously. +1 -1. When people want to see what has gone before, they can use RETURNING clauses. With the indeterminate order proposal, they cannot. 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] wCTE behaviour
On Thu, Nov 11, 2010 at 12:34:55PM -0500, Tom Lane wrote: Thom Brown t...@linux.com writes: WITH t AS (UPDATE foo SET col = true) SELECT * FROM foo WHERE col = false; ... Wouldn't this be more practical to have foo's UPDATEs applied prior to SELECT? Otherwise what would the usecase be? If that's what you want, you might as well just issue two separate statements. There is no use-case for this at all unless the WITH produces some RETURNING data that the SELECT makes use of. There are lots of use cases where it does exactly this. One simple example is maintaining a rollup table, so as less-rolled data get deleted, they get aggregated into an INSERT into that table. Think of RRDtool, only with a real data store. 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] wCTE behaviour
David Fetter da...@fetter.org writes: On Thu, Nov 11, 2010 at 12:36:38PM -0500, Merlin Moncure wrote: On Thu, Nov 11, 2010 at 12:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: then the conclusion is foregone. To my mind, they should be thought of as running in parallel, or at least in an indeterminate order, just exactly the same way that different data modifications made in a single INSERT/UPDATE/DELETE command are considered to be made simultaneously. +1 -1. When people want to see what has gone before, they can use RETURNING clauses. With the indeterminate order proposal, they cannot. Say what? The RETURNING data is well defined in any case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
On Thu, Nov 11, 2010 at 1:53 PM, David Fetter da...@fetter.org wrote: On Thu, Nov 11, 2010 at 12:36:38PM -0500, Merlin Moncure wrote: On Thu, Nov 11, 2010 at 12:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: then the conclusion is foregone. To my mind, they should be thought of as running in parallel, or at least in an indeterminate order, just exactly the same way that different data modifications made in a single INSERT/UPDATE/DELETE command are considered to be made simultaneously. +1 -1. When people want to see what has gone before, they can use RETURNING clauses. With the indeterminate order proposal, they cannot. If you want to see what happened 'before' you *must* use a returning clause. It's the link that pipelines data from one query to another. There is in fact no 'before', just a way to define hook output into input. ISTM you have a lot more available routes of CTE optimization if you go this way. but, can you present an example of a case that depends on execution order w/o returning? maybe I'm not seeing something... merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE behaviour
David Fetter da...@fetter.org writes: On Thu, Nov 11, 2010 at 12:34:55PM -0500, Tom Lane wrote: If that's what you want, you might as well just issue two separate statements. There is no use-case for this at all unless the WITH produces some RETURNING data that the SELECT makes use of. There are lots of use cases where it does exactly this. Name *one*. If there is no RETURNING data, there is absolutely no reason to use WITH instead of issuing the query separately. In fact, I would assume that a DML query without RETURNING would not even be syntactically legal in WITH. One simple example is maintaining a rollup table, so as less-rolled data get deleted, they get aggregated into an INSERT into that table. Yes, exactly. The way you would do that is something like WITH del AS (DELETE FROM foo WHERE whatever RETURNING *) INSERT INTO rollup SELECT * FROM del; I am very interested to see how you will do the same thing without using RETURNING and with the behavior you claim to want that the DELETE is visibly complete before the INSERT starts. Where's the INSERT gonna get the already-deleted data from? With my proposal (ie, both queries using same snapshot) you could actually do it without RETURNING, like this: WITH useless_cte AS (DELETE FROM foo WHERE whatever) INSERT INTO rollup SELECT * FROM foo WHERE same-whatever; But I don't see any reason to think that that's a superior way to write the query, especially since it might be subject to weird race conditions against other concurrent modifications of the table. RETURNING is just a lot saner way to be sure that you're looking at exactly what the DELETE deleted. 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] MULTISET and additional functions for ARRAY
Excerpts from David E. Wheeler's message of jue nov 11 15:45:55 -0300 2010: On Nov 11, 2010, at 10:19 AM, Darren Duncan wrote: I think that it would be best to implement MULTISET in the same way that a TABLE is implemented. Logically and structurally they are the same thing, but that a MULTISET typically is used as a field value of a table row. Aka, a table and a multiset are just different names for a relation, loosely speaking. That sounds like a composite type to me. No, it's perpendicular in the sense that while a composite type allows you to have different columns, this multiset thing lets you have rows (I initially thought about them as sets of scalars, but AFAIU they could in turn be rows) -- Á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] renaming contrib. (was multi-platform, multi-locale regression tests)
On Thu, Nov 11, 2010 at 6:08 PM, Tom Lane t...@sss.pgh.pa.us wrote: Marti Raudsepp ma...@juffo.org writes: On Thu, Nov 11, 2010 at 17:24, Tom Lane t...@sss.pgh.pa.us wrote: Given that we have, in fact, never renamed any files in the history of the project, I'm wondering exactly why it thinks that the number of potential rename/copy targets isn't zero. Because git doesn't do rename tracking at all -- a rename operation is no different from a delete+add operation. Instead it tracks how lines of code move around in the tree: https://git.wiki.kernel.org/index.php/GitFaq#Why_does_git_not_.22track.22_renames.3F Hmmm ... so rename tracking is O(N^2) in the total number of patches applied, or lines patched, or some such measure, between the branches you're trying to patch between? Ugh. Doesn't sound like something we want to grow dependent on. No, it's dependant on files changed between two trees. It does not analyze history when doing rename tracking. Default limit is 200. It should be easy to calculate whats needed for Postgres. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] improved parallel make support
On Thu, Nov 11, 2010 at 5:19 PM, Mark Cave-Ayland mark.cave-ayl...@siriusit.co.uk wrote: Dave Page wrote: On Thu, Nov 11, 2010 at 4:51 PM, Andrew Dunstan and...@dunslane.net wrote: Interesting. Doesn't EDB's PostgresPlus package include PostGIS, and isn't its Windows version build with MSVC? Yes - it's a PITA as we have to have a dummy build of the server in mingw/msys to compile PostGIS and Slony. We're probably going to be looking at that in the not-to-distant future as we want 64bit builds of both and will be using VC++. Just for the record, a lot of work was done in the 1.4 release series to make MSVC builds possible, and indeed several people have reported success: http://postgis.refractions.net/pipermail/postgis-devel/2009-March/005102.html http://postgis.refractions.net/pipermail/postgis-devel/2010-September/010299.html Cool - that will help. The two main outstanding issues as I see it are: 1) The GTK-based GUI for shp2pgsql (although if someone wanted to sponsor work to convert to wxWidgets to bring us in line with pgAdmin, that would be strongly considered). :-) 2) Maintenance of the MSVC build system. So far we have had some complaints about not using MSVC, but then no-one has stepped up to maintain the build system for it. Forcing all existing developers to suddenly start maintaining the Windows build is a total non-starter. Unless you're making major architectural changes, it shouldn't take any real effort to add/remove the occasional source file. I'm sure there are folks that could be persuaded to do that occasionally. My hope is that one day CMake will enable us to come up with a universal solution, but we're some way from that yet. We used CMake for a couple of projects, but ended up abandoning it for new stuff. It just didn't work as nicely as we wanted. -- 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] MULTISET and additional functions for ARRAY
On Nov 11, 2010, at 12:08 PM, Alvaro Herrera wrote: That sounds like a composite type to me. No, it's perpendicular in the sense that while a composite type allows you to have different columns, this multiset thing lets you have rows (I initially thought about them as sets of scalars, but AFAIU they could in turn be rows) How is that different from an array of RECORDs? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MULTISET and additional functions for ARRAY
On Thu, Nov 11, 2010 at 3:42 PM, David E. Wheeler da...@kineticode.com wrote: On Nov 11, 2010, at 12:08 PM, Alvaro Herrera wrote: That sounds like a composite type to me. No, it's perpendicular in the sense that while a composite type allows you to have different columns, this multiset thing lets you have rows (I initially thought about them as sets of scalars, but AFAIU they could in turn be rows) How is that different from an array of RECORDs? not a whole lot outside of syntax details...there is a decent summary here: http://waelchatila.com/2005/05/18/1116485743467.html I like this part: Alternatively the SQL standard also permits the same construct with the bracket trigraphs ??( and ??) :-D merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MULTISET and additional functions for ARRAY
Merlin Moncure wrote:
On Thu, Nov 11, 2010 at 3:42 PM, David E. Wheeler da...@kineticode.com wrote:
On Nov 11, 2010, at 12:08 PM, Alvaro Herrera wrote:
That sounds like a composite type to me.
No, it's perpendicular in the sense that while a composite type allows
you to have different columns, this multiset thing lets you have rows
(I initially thought about them as sets of scalars, but AFAIU they could
in turn be rows)
How is that different from an array of RECORDs?
I could ask the same question about a TABLE, the ordering issue aside.
This is one place that SQL made things more complicated than they needed to be.
Multisets have generally the same structure *and* operators (union, etc) as
tables, but they use different syntax for each. A better design would be to
make tables and multisets interchangeable. Its an unnecessary distinction.
not a whole lot outside of syntax details...there is a decent summary
here: http://waelchatila.com/2005/05/18/1116485743467.html
I like this part: Alternatively the SQL standard also permits the
same construct with the bracket trigraphs ??( and ??) :-D
As I recall, the concept of using stuff like ?( or ?) etc was so that SQL could
be written in EBCDIC which natively lacks some of the bracketing characters that
ASCII has. Hence, such is an alternative way to spell either { } or [ ] (I
forget which).
-- Darren Duncan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] improved parallel make support
On 11/11/2010 03:19 PM, Dave Page wrote: My hope is that one day CMake will enable us to come up with a universal solution, but we're some way from that yet. We used CMake for a couple of projects, but ended up abandoning it for new stuff. It just didn't work as nicely as we wanted. Yes, it's been discussed before here too and didn't really go anywhere :-( 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
[HACKERS] Restructuring plancache.c API
I've been thinking about supporting automatic replan of cached plans using specific parameter values, as has been discussed several times, at greatest length in this thread: http://archives.postgresql.org/pgsql-hackers/2010-02/msg00607.php There doesn't seem to be full consensus about what the control method ought to be, but right at the moment I'm thinking about mechanism not policy. I think that what we need to do is restructure the API of plancache.c to make it more amenable to returning throwaway plans. It can already do that to some extent using the fully_planned = false code path, but that's not the design center and it was shoehorned in in perhaps a less than clean fashion. I want to rearrange it so there's an explicit notion of three levels of cacheable object: 1. Raw parse tree + source string. These obviously never change. 2. The result tree of parsing and rewriting (ie, the output of pg_analyze_and_rewrite applied to level 1). This can change, but only as a result of schema changes on the tables and other objects referenced in the query. We already have entirely adequate mechanisms for recognizing when this has to be rebuilt. 3. The finished plan (ie, the output of pg_plan_queries applied to level 2). This might be either cached for reuse, or a throwaway object, depending on the control mechanism's decisions. I think we could get rid of the fully_planned switch and instead design the API around caching levels 1 and 2. Then there's a GetCachedPlan function (replacing RevalidateCachedPlan) that returns a finished plan, but it's unspecified whether you get a persistent cached plan or a throwaway one. The control mechanism would execute inside this function. We'd still have ReleaseCachedPlan, which would take care of throwing away the plan if it's throwaway. Right now the API is structured so that the initial creator of a cacheable plan has to build levels 2 and 3 first, and the plancache.c code just copies that data into persistent storage. I'm thinking that might have been a mistake. Maybe we should just have the caller hand over the data for level 1, with parse analysis + rewrite done solely internally within plancache.c. The level-2 data wouldn't be exposed outside plancache.c at all. With this focus, the name plancache becomes a little bit of a misnomer, but I am inclined to stick with it because a better name isn't apparent. rewritecache isn't an improvement really. Comments? 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] Delimited identifier brhavior
Hi, I' wondering if following delimited identifier brhavior is correct or not: test=# create table t1(i int); create table t1(i int); CREATE TABLE test=# create table t1_foo(i int, j int); create table t1_foo(i int, j int); CREATE TABLE test=# select * from t1; select * from t1; i --- (0 rows) test=# select * from t1_foo; select * from t1_foo; i | j ---+--- (0 rows) test=# select * from t1_foo; select * from t1_foo; i --- (0 rows) It seems PostgreSQL thinks t1_foo is equivalent to t1. Is this an expected behavior? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.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] Delimited identifier brhavior
Tatsuo Ishii is...@postgresql.org wrote: It seems PostgreSQL thinks t1_foo is equivalent to t1. It thinks you've given t1 an alias of _foo in that query, same as if you'd had a space between t1 and _foo. -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] Delimited identifier brhavior
Tatsuo Ishii wrote: test=# select * from t1_foo; select * from t1_foo; i --- (0 rows) It seems PostgreSQL thinks t1_foo is equivalent to t1. Is this an expected behavior? That code looks badly written in any event. Delimiters should be put around each part of an identifier or chain as a whole, such as: select * from t1_foo; Or with schema-delimited objects, for example, any of these: schema.table schema.table schema.table schema.table Personally, I treat all of my identifiers as being case-sensitive. Knowing that Pg treats non-delimited identifiers as being lowercase, I write undelimited when the identifier is entirely lowercase, and I delimit ones that have any uppercase. And by doing this consistently everything works correctly. Since most of my identifiers are lowercase anyway, the code also reads cleanly in general. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Delimited identifier brhavior
On 11/11/2010 06:03 PM, Tatsuo Ishii wrote: Hi, I' wondering if following delimited identifier brhavior is correct or not: test=# create table t1(i int); create table t1(i int); CREATE TABLE test=# create table t1_foo(i int, j int); create table t1_foo(i int, j int); CREATE TABLE test=# select * from t1; select * from t1; i --- (0 rows) test=# select * from t1_foo; select * from t1_foo; i | j ---+--- (0 rows) test=# select * from t1_foo; select * from t1_foo; i --- (0 rows) It seems PostgreSQL thinks t1_foo is equivalent to t1. Is this an expected behavior? It's treating _foo as an alias in the query for t1. So the behaviour is quite correct, I think. 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] MULTISET and additional functions for ARRAY
On Fri, Nov 12, 2010 at 03:05, Tom Lane t...@sss.pgh.pa.us wrote:
David E. Wheeler da...@kineticode.com writes:
So are you planning to implement multisets? It's a feature I'd love to see
What actual functionality does it buy? AFAICT from Itagaki-san's
description, it's an array only you ignore the specific element order.
So what? You can write functions that work that way now.
I think there are almost no difference between a multiset and an array
in terms of functions I described in the first mail.
However, if we have separated multiset data type, we could have special
comparison operators for them; array = array returns true only if they
have the same elements in the same order, but multiset = multiset only
checks elements in them. Also, we could optimize on-disk structure of
multiset for fast UNION operations or for dataset that has many duplicates.
For example, we could use a sorted array of {value, count} pairs.
If we decide to have data type IDs for multiset, I'll go for it (ex. int4,
_int4, and an additional $int4), but it consumes +50% of typoids. If it
is not preferable, only function support might be better at the first try.
--
Itagaki Takahiro
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Delimited identifier brhavior
It seems PostgreSQL thinks t1_foo is equivalent to t1. It thinks you've given t1 an alias of _foo in that query, same as if you'd had a space between t1 and _foo. Oh, ok. I thought we always need at least one space character between the table name and the alias. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.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] MULTISET and additional functions for ARRAY
On Fri, Nov 12, 2010 at 06:06, Darren Duncan dar...@darrenduncan.net wrote: This is one place that SQL made things more complicated than they needed to be. Multisets have generally the same structure *and* operators (union, etc) as tables, but they use different syntax for each. A better design would be to make tables and multisets interchangeable. Its an unnecessary distinction. We can use unnest() to convert MULTISET into TABLE, and collect() agg function from TABLE to MULTISET. I don't think they need to have the same on-disk structure; they can share operators and constructor syntax even now. -- Itagaki Takahiro -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] duplicate connection failure messages
Peter Eisentraut wrote:
On tor, 2010-10-14 at 07:30 +0200, Magnus Hagander wrote:
And I agree it's not very friendly in this specific case - I
wonder if we should log it as localhost (127.0.0.1) and localhost
(::1) (and similar for any other case that returns more than one
address).
That looks good.
I have developed the attached patch to report whether IPv4 or IPv6 are
being used. I could not find the numeric value as alwasy populated, and
this seems clearer too:
$ pql -h localhost test
psql: could not connect to server: Connection refused
Is the server running on host localhost (IPv4) and accepting
TCP/IP connections on port 5432?
$ psql -h 127.0.0.1 test
psql: could not connect to server: Connection refused
Is the server running on host 127.0.0.1 (IPv4) and accepting
TCP/IP connections on port 5432?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 8f318a1..bf85b49 100644
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
*** connectFailureMessage(PGconn *conn, int
*** 962,968
{
appendPQExpBuffer(conn-errorMessage,
libpq_gettext(could not connect to server: %s\n
! \tIs the server running on host \%s\ and accepting\n
\tTCP/IP connections on port %s?\n),
SOCK_STRERROR(errorno, sebuf, sizeof(sebuf)),
conn-pghostaddr
--- 962,968
{
appendPQExpBuffer(conn-errorMessage,
libpq_gettext(could not connect to server: %s\n
! \tIs the server running on host \%s\ (%s) and accepting\n
\tTCP/IP connections on port %s?\n),
SOCK_STRERROR(errorno, sebuf, sizeof(sebuf)),
conn-pghostaddr
*** connectFailureMessage(PGconn *conn, int
*** 970,975
--- 970,980
: (conn-pghost
? conn-pghost
: ???),
+ (conn-addr_cur-ai_family == AF_INET) ? IPv4 :
+ #ifdef HAVE_IPV6
+ (conn-addr_cur-ai_family == AF_INET6) ? IPv6 :
+ #endif
+ ???,
conn-pgport);
}
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Magnus Hagander wrote: On Fri, Sep 17, 2010 at 05:51, Ashesh Vashi ashesh.va...@enterprisedb.com wrote: Hi Mark, On of my college (Sujeet) has found a way to reproduce the same behaviour. 1. Installed PG 9.0 on Win XP SP3 2. Stop the Postgresql-9.0 service from service manager console 3. Create pgpass.conf in postgres (service account) user's profile with an incorrect password deliberately. (Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html) 4. Now start the postgresql-9.0 service, it will return an error and the status ?? shows stopped 5. However i could connect to the psql shell and get the prompt which means ??? the server is running. I took a quick look at the code, and from what I can tell this is because PQconnectionNeedsPassword() always returns false if a pgpass.conf has been used. There is no handling the case where pgpass is used, but has an incorrect password. Does anybody recall the specific reason for this? Do we need a way for pg_ctl to figure this out, or do we need to change it in PQconnecitonNeedsPassword()? I was not able to reproduce this failure on my BSD system using GIT head: $ psql test psql: FATAL: password authentication failed for user postgres password retrieved from file /u/postgres/.pgpass $ pg_ctl status pg_ctl: server is running (PID: 710) /usr/var/local/pgsql/bin/postgres -i -- 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] Exposing an installation's default value of unix_socket_directory
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On tor, 2010-10-21 at 16:59 -0400, Tom Lane wrote: Actually, the only reason this is even up for discussion is that there's no configure option to set DEFAULT_PGSOCKET_DIR. If there were, and debian were using it, then pg_config --configure would tell what I wish to know. I thought for a bit about proposing we add such an option, but given the current state of play it might be more misleading than helpful: as long as distros are accustomed to changing this setting via a patch, you couldn't trust pg_config --configure to tell you what a given installation actually has compiled into it. Presumably, if a configure option were added, they couldn't change it via patch anymore. Hm, you're right: we'd remove the pg_config_manual.h entry, so the existing patches would stop working, and presumably maintainers would figure out that they ought to use the configure switch instead. So that argument holds little water. Btw., a configure option for this was rejected years ago to discourage people from actually changing the default. Yeah, I remember that discussion now that you mention it. It still seems like a good policy ... but given that some popular packages are changing the default whether we think it's a good idea or not, maybe it's better to acknowledge that reality. We could still have some text in the manual pointing out the compatibility hazards of using the switch, I guess. Might have been a nice change for 9.0. :-( I don't think there is a great amount of defense that it should be in /tmp except for backward compatibility, and for non-root installs. For a package installer, I think moving it out of temp makes sense, hence a configure flag. -- 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] Simplifying replication
Robert Haas wrote: On Thu, Oct 28, 2010 at 1:13 AM, Josh Berkus j...@agliodbs.com wrote: I sort of agree with you that the current checkpoint_segments parameter is a bit hard to tune, at least if your goal is to control the amount of disk space that will be used by WAL files. ?But I'm not sure your proposal is better. ?Instead of having a complicated formula for predicting how much disk space would get used by a given value for checkpoint_segments, we'd have a complicated formula for the amount of WAL that would force a checkpoint based on max_wal_size. Yes, but the complicated formula would then be *in our code* instead of being inflicted on the user, as it now is. I don't think so - I think it will just be inflicted on the user in a different way. We'd still have to document what the formula is, because people will want to understand how often a checkpoint is going to get forced. So here's an example of how this could happen. Someone sets max_wal_size = 480MB. Then, they hear about the checkpoint_completion_target parameter, and say, ooh, goody, let me boost that. So they raise it from 0.5 to 0.9. Now, all of a sudden, they're getting more frequent checkpoints. Performance may get worse Uh, checkpoint_completion_target only controls flushing of buffers between checkpoints, not the frequency of checkpoints. It is hard to believe that, for tuning, the number of 16mb files is more meaningful then raw file size. -- 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] Simplifying replication
Robert Haas wrote: On Wed, Oct 27, 2010 at 3:53 AM, Simon Riggs si...@2ndquadrant.com wrote: On Tue, 2010-10-26 at 22:03 -0400, Robert Haas wrote: On Tue, Oct 26, 2010 at 9:59 PM, Josh Berkus j...@agliodbs.com wrote: If you set wal_keep_segments=0, archive_mode=on, and archive_command=something, you might run out of disk space. If you set wal_keep_segments=-1, you might run out of disk space. Are you any more screwed in the second case than you are in the first case? It is the same to the user either way. ?In either case you have to change some settings and restart the master. Except that changing wal_keep_segments doesn't require restarting the master. The point of allowing -1 was to allow someone to set it to that value temporarily, to be able to do a hot backup without having to guess how large to set it. ?If you don't have enough disk space for a backup to complete, you're kind of hosed either way. You're not hosed either way. Fujii designed this carefully to avoid that and it works. The case of archive_command failing isn't comparable because that is a failure case, not a normal working server. You don't need to guess the setting of wal_keep_segments. It's a safety net that has been deliberately created to avoid the crash that would otherwise happen. I've not heard a better proposal, yet, though I too am hopeful there is a better one. I think you might be confused about what the use case Bruce and I are imagining, because this doesn't make any sense at all in that context. The specific use case is that you have archive_mode=off, wal_level=archive or wal_level=hot_standby, and you want to take a hot backup. If you do pg_start_backup(), copy the data directory, and do pg_stop_backup(), you won't necessarily end up with enough xlog to This is a clear case of protecting people from themselves (make them specify a max wal size), and making the feature easy to use. We can't have both, folks. For 9.0, we picked the former. The same tradeoff often exists for flexibility and ease of use. -- 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] Simplifying replication
On Thu, Nov 11, 2010 at 10:13 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Thu, Oct 28, 2010 at 1:13 AM, Josh Berkus j...@agliodbs.com wrote: I sort of agree with you that the current checkpoint_segments parameter is a bit hard to tune, at least if your goal is to control the amount of disk space that will be used by WAL files. ?But I'm not sure your proposal is better. ?Instead of having a complicated formula for predicting how much disk space would get used by a given value for checkpoint_segments, we'd have a complicated formula for the amount of WAL that would force a checkpoint based on max_wal_size. Yes, but the complicated formula would then be *in our code* instead of being inflicted on the user, as it now is. I don't think so - I think it will just be inflicted on the user in a different way. We'd still have to document what the formula is, because people will want to understand how often a checkpoint is going to get forced. So here's an example of how this could happen. Someone sets max_wal_size = 480MB. Then, they hear about the checkpoint_completion_target parameter, and say, ooh, goody, let me boost that. So they raise it from 0.5 to 0.9. Now, all of a sudden, they're getting more frequent checkpoints. Performance may get worse Uh, checkpoint_completion_target only controls flushing of buffers between checkpoints, not the frequency of checkpoints. According to the formula in our fine documentation, if you increase checkpoint_completion_target, the maximum number of WAL files also increases. This makes sense: the files from the last checkpoint can't be removed until further along into the next cycle. Therefore, if you wanted to increase the checkpoint_completion_target while keeping the maximum amount of WAL on disk the same, you'd need to trigger checkpoints more frequently. -- 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] MULTISET and additional functions for ARRAY
On Thu, Nov 11, 2010 at 10:02 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: If we reuse type IDs of arrays for multisets, the multisets would have some special typmod. For example, typmod = 0 means multiset, and positive value means array with max cardinality. Note that the SQL standard doesn't mention about multi-dimensional arrays. So, we can use typmod = -1 as a free-size and free-dimensional array for backward compatibility. I would really like to see us fix our type system so that it doesn't require this type of awful hack. But maybe that's asking too much of a patch to implement this 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] duplicate connection failure messages
Bruce Momjian br...@momjian.us writes: I have developed the attached patch to report whether IPv4 or IPv6 are being used. What's the use of that exactly? It doesn't really respond to Peter's concern, I think. 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] MULTISET and additional functions for ARRAY
Robert Haas robertmh...@gmail.com writes: On Thu, Nov 11, 2010 at 10:02 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: If we reuse type IDs of arrays for multisets, the multisets would have some special typmod. For example, typmod = 0 means multiset, and positive value means array with max cardinality. Note that the SQL standard doesn't mention about multi-dimensional arrays. So, we can use typmod = -1 as a free-size and free-dimensional array for backward compatibility. I would really like to see us fix our type system so that it doesn't require this type of awful hack. But maybe that's asking too much of a patch to implement this feature. The problem is not with the type system: as long as you give multisets different type OIDs from arrays, everything will work fine. It will absolutely not work to try to use typmod to make the behavior vary like that ... but Itagaki-san knew that already. 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] MULTISET and additional functions for ARRAY
On Fri, Nov 12, 2010 at 12:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Nov 11, 2010 at 10:02 AM, Itagaki Takahiro itagaki.takah...@gmail.com wrote: If we reuse type IDs of arrays for multisets, the multisets would have some special typmod. For example, typmod = 0 means multiset, and positive value means array with max cardinality. Note that the SQL standard doesn't mention about multi-dimensional arrays. So, we can use typmod = -1 as a free-size and free-dimensional array for backward compatibility. I would really like to see us fix our type system so that it doesn't require this type of awful hack. But maybe that's asking too much of a patch to implement this feature. The problem is not with the type system: as long as you give multisets different type OIDs from arrays, everything will work fine. It will absolutely not work to try to use typmod to make the behavior vary like that ... but Itagaki-san knew that already. And thus you must create a THIRD copy of every entry in pg_type. That doesn't qualify as a 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] Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
On Fri, Nov 12, 2010 at 03:49, Bruce Momjian br...@momjian.us wrote: Magnus Hagander wrote: On Fri, Sep 17, 2010 at 05:51, Ashesh Vashi ashesh.va...@enterprisedb.com wrote: Hi Mark, On of my college (Sujeet) has found a way to reproduce the same behaviour. 1. Installed PG 9.0 on Win XP SP3 2. Stop the Postgresql-9.0 service from service manager console 3. Create pgpass.conf in postgres (service account) user's profile with an incorrect password deliberately. (Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html) 4. Now start the postgresql-9.0 service, it will return an error and the status ?? shows stopped 5. However i could connect to the psql shell and get the prompt which means ??? the server is running. I took a quick look at the code, and from what I can tell this is because PQconnectionNeedsPassword() always returns false if a pgpass.conf has been used. There is no handling the case where pgpass is used, but has an incorrect password. Does anybody recall the specific reason for this? Do we need a way for pg_ctl to figure this out, or do we need to change it in PQconnecitonNeedsPassword()? I was not able to reproduce this failure on my BSD system using GIT head: $ psql test psql: FATAL: password authentication failed for user postgres password retrieved from file /u/postgres/.pgpass $ pg_ctl status pg_ctl: server is running (PID: 710) /usr/var/local/pgsql/bin/postgres -i The problem is not in pg_ctl status, it's in pg_ctl start. They're different codepaths - status never tries to actually connect, it just checks if the process is alive. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
