[HACKERS] CurrentUserId may be invalid during the rest of a session
Hi,
During the first transaction starting phase within a backend, if there is
an 'ereport' after setting transaction state but before
saving CurrentUserId into 'prevUser' in 'TransactionStateData',
CurrentUserId will be invalid in the rest of the session.
Take branch 'REL9_6_STABLE' for example:
1797 static void
1798 StartTransaction(void)
1799 {
1800 TransactionState s;
..
1822 s->state = TRANS_START;
*<===
'ereport' in this window*
..
1909 GetUserIdAndSecContext(&s->prevUser, &s->prevSecContext);
..
1927 }
If 'ereport' occurs in the described window, CurrentUserId will have no
chance to be saved into 'prevUser' and 'prevUser' will remain to be
InvalidOid as this is the first transaction of the session.
As transaction state has been set to be TRANS_START, 'AbortTransaction'
will be called then and CurrentUserId will be restored with 'prevUser',
which is InvalidOid. So in the rest of the session, CurrentUserId will be
invalid.
The invalid CurrentUserId may cause assertion failure or other issues, for
example:
(gdb) bt
#0 0x7f3d8ced9495 in raise () from /lib64/libc.so.6
#1 0x7f3d8cedac75 in abort () from /lib64/libc.so.6
#2 0x0095fdbd in ExceptionalCondition (conditionName=0xb72838
"!(((bool) ((CurrentUserId) != ((Oid) 0", errorType=0xb726ff "BadState",
fileName=0xb726c0 "miscinit.c", lineNumber=284) at assert.c:54
#3 0x00971b88 in GetUserId () at miscinit.c:284
#4 0x005559c4 in recomputeNamespacePath () at namespace.c:3496
#5 0x00551d53 in RelnameGetRelid (relname=0x1d3f288 "t1") at
namespace.c:673
#6 0x005514a7 in RangeVarGetRelidExtended (relation=0x1d3f2a8,
lockmode=1, missing_ok=1 '\001', nowait=0 '\000', callback=0x0,
callback_arg=0x0)
at namespace.c:326
Is this expected behavior?
Thanks
-Richard
Re: [HACKERS] Elusive segfault with 9.3.5 & query cancel
On 5 Dec 2014, at 22:41, Jim Nasby wrote: > > > Perhaps we should also officially recommend production servers be setup to > create core files. AFAIK the only downside is the time it would take to write > a core that's huge because of shared buffers, but perhaps there's some way to > avoid writing those? (That means the core won't help if the bug is due to > something in a buffer, but that seems unlikely enough that the tradeoff is > worth it...) Good idea. It seems the madvise() system call (with MADV_DONTDUMP) is exactly what's needed to avoid dumping shared buffers. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] patch: add MAP_HUGETLB to mmap() where supported (WIP)
The attached patch adds the MAP_HUGETLB flag to mmap() for shared memory
on systems that support it. It's based on Christian Kruse's patch from
last year, incorporating suggestions from Andres Freund.
On a system with 4GB shared_buffers, doing pgbench runs long enough for
each backend to touch most of the buffers, this patch saves nearly 8MB of
memory per backend and improves performances by just over 2% on average.
It is still WIP as there are a couple of points that Andres has pointed
out to me that haven't been addressed yet; also, the documentation is
incomplete.
Richard
--
Richard Poole http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 23ebc11..703b28f 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1052,6 +1052,42 @@ include 'filename'
+
+ huge_tlb_pages (enum)
+
+ huge_tlb_pages configuration parameter
+
+
+
+Enables/disables the use of huge tlb pages. Valid values are
+on, off and try.
+The default value is try.
+
+
+
+ Use of huge tlb pages reduces the cpu time spent on memory management and
+ the amount of memory used for page tables and therefore improves performance.
+
+
+
+With huge_tlb_pages set to on
+mmap() will be called with MAP_HUGETLB.
+If the call fails the server will fail fatally.
+
+
+
+With huge_tlb_pages set to off we
+will not use MAP_HUGETLB at all.
+
+
+
+With huge_tlb_pages set to try
+we will try to use MAP_HUGETLB and fall back to
+mmap() without MAP_HUGETLB.
+
+
+
+
temp_buffers (integer)
diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
index 20e3c32..57fff35 100644
--- a/src/backend/port/sysv_shmem.c
+++ b/src/backend/port/sysv_shmem.c
@@ -27,10 +27,14 @@
#ifdef HAVE_SYS_SHM_H
#include
#endif
+#ifdef MAP_HUGETLB
+#include
+#endif
#include "miscadmin.h"
#include "storage/ipc.h"
#include "storage/pg_shmem.h"
+#include "utils/guc.h"
typedef key_t IpcMemoryKey; /* shared memory key passed to shmget(2) */
@@ -61,6 +65,13 @@ typedef int IpcMemoryId; /* shared memory ID returned by shmget(2) */
#define MAP_FAILED ((void *) -1)
#endif
+#ifdef MAP_HUGETLB
+#define PG_HUGETLB_BASE_ADDR (void *)(0x0UL)
+#define PG_MAP_HUGETLB MAP_HUGETLB
+#else
+#define PG_MAP_HUGETLB 0
+#endif
+
unsigned long UsedShmemSegID = 0;
void *UsedShmemSegAddr = NULL;
@@ -342,6 +353,161 @@ PGSharedMemoryIsInUse(unsigned long id1, unsigned long id2)
}
+#ifdef MAP_HUGETLB
+#define HUGE_PAGE_INFO_DIR "/sys/kernel/mm/hugepages"
+
+/*
+ * static long InternalGetFreeHugepagesCount(const char *name)
+ *
+ * Attempt to read the number of available hugepages from
+ * /sys/kernel/mm/hugepages/hugepages-/free_hugepages
+ * Will fail (return -1) if file could not be opened, 0 if no pages are available
+ * and > 0 if there are free pages
+ *
+ */
+static long
+InternalGetFreeHugepagesCount(const char *name)
+{
+ int fd;
+ char buff[1024];
+ size_t len;
+ long result;
+ char *ptr;
+
+ len = snprintf(buff, 1024, "%s/%s/free_hugepages", HUGE_PAGE_INFO_DIR, name);
+ if (len == 1024) /* I don't think that this will happen ever */
+ {
+ ereport(huge_tlb_pages == HUGE_TLB_TRY ? DEBUG1 : WARNING,
+(errmsg("Filename %s/%s/free_hugepages is too long", HUGE_PAGE_INFO_DIR, name),
+ errcontext("while checking hugepage size")));
+ return -1;
+ }
+
+ fd = open(buff, O_RDONLY);
+ if (fd <= 0)
+ {
+ ereport(huge_tlb_pages == HUGE_TLB_TRY ? DEBUG1 : WARNING,
+(errmsg("Could not open file %s: %s", buff, strerror(errno)),
+ errcontext("while checking hugepage size")));
+ return -1;
+ }
+
+ len = read(fd, buff, 1024);
+ if (len <= 0)
+ {
+ ereport(huge_tlb_pages == HUGE_TLB_TRY ? DEBUG1 : WARNING,
+(errmsg("Error reading from file %s: %s", buff, strerror(errno)),
+ errcontext("while checking hugepage size")));
+ close(fd);
+ return -1;
+ }
+
+ /*
+ * If the content of free_hugepages is longer than or equal to 1024 bytes
+ * the rest is irrelevant; we simply want to know if there are any
+ * hugepages left
+ */
+ if (len == 1024)
+ {
+ buff[1023] = 0;
+ }
+ else
+ {
+ buff[len] = 0;
+ }
+
+ close(fd);
+
+ result = strtol(buff, &ptr, 10);
+
+ if (ptr == NULL)
+ {
+ ereport(huge_tlb_pages == HUGE_TLB_TRY ? DEBUG1 : WARNING,
+(errmsg("Could not convert contents of file %s/%s/free_hugepages to number", HUGE_PAGE_INFO_DIR, name),
+ errcontext("while checking hugepage size")));
+ return -1;
+ }
+
+ return result;
+}
+
+/*
+ * static long InternalGetHugepageSize()
+ *
+
[HACKERS] stray SIGALRM
In 9.3beta1, a backend will receive a SIGALRM after authentication_timeout seconds, even if authentication has been successful. Most of the time this doesn't hurt anyone, but there are cases, such as when the backend is doing the open() of a backend copy, when it breaks things and results in an error getting reported to the client. In particular, if you're doing a copy from a FIFO, it is normal for open() to block until the process at the other end has data ready, so you're very likely to have it interrupted by the SIGALRM and fail. To see the SIGALRM just run psql then determine your backend's pid, attach an strace to it, and wait 60 seconds, or whatever you've got authentication_timeout set to. This behaviour appears in 6ac7facdd3990baf47efc124e9d7229422a06452 as a side-effect of speeding things up by getting rid of setitimer() calls; it's not obvious what's a good way to fix it without losing the benefits of that commit. Thanks Alvaro and Andres for helping me get from "why is my copy getting these signals" to understanding what's actually going on. Richard -- Richard Poole http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \set vs \copy - bug or expected behaviour?
On 17/08/12 18:38, Tom Lane wrote:
Bruce Momjian writes:
On Fri, Oct 21, 2011 at 05:31:41PM -0400, Robert Haas wrote:
I'm not sure whether that's a bug per se, but I can see where a
behavior change might be an improvement.
I did some research on this and learned a little more about flex rules.
Turns out we can allow variable substitution in psql whole-line
commands, like \copy and \!, by sharing the variable expansion flex
rules with the code that does argument processing.
Well, it'd be nice to allow substitution there ...
What we can't easily do is to allow quotes to prevent variable
substitution in these whole-line commands because we can't process the
quotes because that will remove them.
... but if there is then no way to prevent it, that's absolutely
unacceptable.
If I'm understanding this correctly, \copy parsing just passes the query
part unaltered as part of a COPY statement back into the top-level
parser. Likewise with the \!shell stuff (but presumably to execve).
To handle variable-substitution correctly for \copy we'd need to
duplicate the full parsing for COPY. For \! we'd need something which
understood shell-syntax (for the various shells out there). Ick.
Or you'd need a separate variable-bracketing {{:x}} syntax that could
work like reverse dollar-quoting. Also Ick.
As far as we know this has only inconvenienced one person (me) badly
enough to report a maybe-bug. Thanks for trying Bruce, but I fear this
is one itch that'll go unscratched.
Rest assured I'm not about to storm off and replace all my installations
with MySQL :-)
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Escaping ":" in .pgpass - code or docs bug?
On 19/12/11 16:48, Robert Haas wrote: On Sat, Dec 17, 2011 at 3:27 AM, Ross Reedstrom wrote: This should either be fixed by changing the documentation to say to not escape colons or backslashes in the password part, only, or modify this function (PasswordFromFile) to silently unescape the password string. It already copies it. My vote is for a doc correction in the back-branches and a behavior change in master. Seems sensible - presumably mentioning "this will be corrected in 9.2"? It's clearly not what you'd call "urgent" since nobody else seems to have noticed before now. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Escaping ":" in .pgpass - code or docs bug?
According to the docs [1], you should escape embedded colons in .pgpass (fair enough). Below is PG 9.1.1 user = "te:st", db = "te:st", password = "te:st" $ cat ~/.pgpass *:*:te:st:te:st:te:st $ psql91 -U "te:st" -d "te:st" te:st=> $ cat ~/.pgpass *:*:te\:st:te\:st:te:st $ psql91 -U "te:st" -d "te:st" te:st=> $ cat ~/.pgpass *:*:te\:st:te\:st:te\:st $ psql91 -U "te:st" -d "te:st" psql: FATAL: password authentication failed for user "te:st" password retrieved from file "/home/richardh/.pgpass" I'm a bit puzzled how it manages without the escaping in the first case. There's a lack of consistency though that either needs documenting or fixing. [1] http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time bug with small years
On 24/11/11 04:45, Rod Taylor wrote: I have no idea what is going on with the minutes/seconds, particularly for years under 1895 where it gets appended onto the timezone component? sk_test=# select version(); version PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit (1 row) -- uname -a output: Linux rbt-dell 3.0.0-13-generic #22-Ubuntu SMP Wed Nov 2 13:27:26 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux sk_test=# select '1894-01-01'::timestamp with time zone; timestamptz -- 1894-01-01 00:00:00-05:17:32 Floating-point timestamps? Although I thought integer was the default for 9.x - hmm INSTALL says since 8.4 -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql \set vs \copy - bug or expected behaviour?
It looks like \copy is just passing the text of the query unadjusted to "COPY". I get a syntax error on ":x" with the \copy below on both 9.0 and 9.1 === test script === \set x '''HELLO''' -- Works \echo :x -- Works \o '/tmp/test1.txt' COPY (SELECT :x) TO STDOUT; -- Doesn't work \copy (SELECT :x) TO '/tmp/test2.txt' === end script === -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Questions about the internal of fastpath function call
Tom, Thanks a lot. The part I cant understand is I cant see where SendFunctionResult construct the return value into the buf. I expect it should copy something in retval to buf, but I cant find any of this in the code, the main logic of SendFunctionResult is: 00159 getTypeOutputInfo <http://doxygen.postgresql.org/lsyscache_8c.html#48d637426d06bfdf607eb29da3be558a>(rettype <http://doxygen.postgresql.org/structfp__info.html#2b1f02caad79fae010cdc5836a2fcb83>, &typoutput, &typisvarlena);00160 outputstr = OidOutputFunctionCall <http://doxygen.postgresql.org/fmgr_8c.html#9b747dd35f098ab369b44e94f4708210>(typoutput, retval);00161 pq_sendcountedtext <http://doxygen.postgresql.org/pqformat_8c.html#e6acc43bbc9bee686de201de82a26db5>(&buf, outputstr, strlen(outputstr), false);00162 pfree <http://doxygen.postgresql.org/mcxt_8c.html#4de9741ca04b2f01a82d3de16a1d6bf2>(outputstr); I think it should copy the real result in OidOutputFunctionCall, but unfortunately I can only find it call FunctionCall1() again. Do I miss something? If I copy the tuples returnned from pl module here, is it possible to send a vector result from fastpath function call? 2011/5/9 Tom Lane > lee Richard writes: > > I still can not see how it return a single return value to the client, > and > > why it call FunctionCall1() again when it want to send the result. > > pq_endmessage is where the bytes actually get pushed out to the client, > if that's what you're looking for. The rest of the activity in > SendFunctionResult is just concerned with constructing the message to be > sent within "buf". > >regards, tom lane >
Re: [HACKERS] Questions about the internal of fastpath function call
Merlin, Oh, I didnt realized that it does not support to return scalar, thanks a lot. When it returns a single value, I see it use the following function, SendFunctionCall result = DatumGetByteaP(FunctionCall1(flinfo, val)); I still can not see how it return a single return value to the client, and why it call FunctionCall1() again when it want to send the result. Regards Clipper 2011/5/8 Merlin Moncure > On Sun, May 8, 2011 at 8:01 AM, lee Richard > wrote: > > Hi, > > > > I am reading the source code of fastpath to understand the internal of > > fastpath. I can not understand how does it send result to the client, I > hope > > somebody can help me on this. > > > > I see it call it invoke the function in > > > > HandleFunctionRequest() > > -> retval = FunctionCallInvoke(&fcinfo); > > -> SendFunctionResult(retval, fcinfo.isnull, fip->rettype, rformat); > > ->OidOutputFunctionCall() > > -> OutputFunctionCall() > >-> result = > > DatumGetCString(FunctionCall1(flinfo, val)); > >-> result = > > FunctionCallInvoke(&fcinfo); > > > > but I can not see in where it send the tuples to the client, instead it > > invoke FunctionCallInvoke again. Can anyone tell me how it copy the > tuples > > and send it to the client. > > It can't: the fastpath function can only return a scalar. See the > client side docs. The whole system is a kludge more or less. > > merlin >
[HACKERS] Questions about the internal of fastpath function call
Hi, I am reading the source code of fastpath to understand the internal of fastpath. I can not understand how does it send result to the client, I hope somebody can help me on this. I see it call it invoke the function in HandleFunctionRequest() -> retval = FunctionCallInvoke(&fcinfo); -> SendFunctionResult(retval, fcinfo.isnull, fip->rettype, rformat); ->OidOutputFunctionCall() -> OutputFunctionCall() -> result = DatumGetCString(FunctionCall1(flinfo, val)); -> result = FunctionCallInvoke(&fcinfo); but I can not see in where it send the tuples to the client, instead it invoke FunctionCallInvoke again. Can anyone tell me how it copy the tuples and send it to the client. Thanks a lot. Clipper
Re: [HACKERS] A different approach to extension NO USER DATA feature
On 06/02/11 18:23, Tom Lane wrote: After a bit of thought I believe that we can fix this if we are willing to teach pg_dump explicitly about extension configuration tables. The behavior we want for those is for the table schema definition to never be dumped (the table should always be created by CREATE EXTENSION), but for some subset of the table data to get dumped, excluding any system-provided rows. [snip] pg_extension_partial_dump (table_name regclass, where_condition text) Possible alternative approach? 1. Extension provides list of config tables/views/set-returning functions to be dumped via e.g. my_config_tables() 2. They get dumped, but each as a TEMP TABLE (need unique names for multiple extensions though). 3. On restore, tables are created and populated, then read_your_config() is called in the extension. This separates the configuration-for-user from configuration-for-extension. It allows the extension to decide whether to load the new config or reject it. It lets you test/demonstrate multiple configurations fairly simply. The "system_data" column scenario can then be a default implementation of read_your_config(). -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [RRR] [HACKERS] Seeking Mentors for Funded Reviewers
On Wed, Jan 26, 2011 at 1:55 PM, Robert Haas wrote: > I don't think that's it exactly. Basically, if you fund reviewers, > and we get lots more people doing reviews and they're all great, I'll > be happy. If you fund reviewers, and we get lots more people doing > reviews and they're all terrible, I'll be unhappy. And likewise if > you do or don't fund mentors. The results matter a lot, and none of > us know that for sure yet. This makes sense. I should clarify that this point in time were talking about one maybe two people can awarded grants. Over the course of a year I wouldn't expect more that four grants issued (at least for now.) With these numbers, there is too much to be gained or lost from the perceptive of the community in my opinion. > I think all I (and others) are asking you > do is think about it carefully before you decide what to do; I at > least am not trying to push you down any particular path. > Fair enough. > > So any person regardless of association or funding is free to approach to > > community for assistance. > > I strongly agree with that statement. Of course, all such help is on > a best-effort, volunteer basis. If you need more than that, you can > try (a) begging, (b) T-shirts, or (c) money. What's not clear to me > is whether you do in fact need more than that, and which of (a)-(c) is > the best way to get it. > > > In addition, third party organizations should > > maintain a healthy disconnection from the community. > > I'm not sure what you mean by this. > Now that I read it, I not sure what I meant either. :) How about this: the selection, management, and oversight of grants and mentees should be opaque to the community so as to prevent distraction. There should be no appearance of community endorsement of such programs. -- Regards, Richard Broersma Jr.
Re: [RRR] [HACKERS] Seeking Mentors for Funded Reviewers
On Wed, Jan 26, 2011 at 1:19 PM, Robert Haas wrote: > It's just that > I require both income and sleep. That's probably not an issue for > people who are just getting started in the community. > > Another question is whether you really need assigned mentors at all. ... > Very few emails on -hackers go unanswered. > So I take it that the concern is not how reviews are funded, but over the perceived connection between the organic community and third party organizations. This makes sense. So any person regardless of association or funding is free to approach to community for assistance. In addition, third party organizations should maintain a healthy disconnection from the community. Is this correct? -- Regards, Richard Broersma Jr.
Re: [RRR] [HACKERS] Seeking Mentors for Funded Reviewers
On Wed, Jan 26, 2011 at 11:15 AM, Robert Haas wrote: > Usually, in an educational process, it's the teachers who get paid, > and the students who have to pay to get educated. I realize this is > somewhat different because we want to encourage people to get involved > in the project, but it still seems weird. This is probably a good point. I've never mentored, taught, authored a patch or review, so I can say what is similar or different. > People > sometimes do a lousy job now too, but at least we can count on the > fact that everyone who signs up to do it has some intrinsic > motivation. > > http://www.nytimes.com/2005/05/15/books/chapters/0515-1st-levitt.html > Interesting. -- Regards, Richard Broersma Jr.
Re: [HACKERS] Seeking Mentors for Funded Reviewers
On Wed, Jan 26, 2011 at 3:12 AM, Simon Riggs wrote: > You're paying the reviewers; are you paying the mentors? > The answer to this question is that we can fund mentor (teacher). However, the amount to fund a mentor would be significantly less that the amount to fund a reviewer (student). The mentors are part of the educational process. -- Regards, Richard Broersma Jr.
Re: [RRR] [HACKERS] Seeking Mentors for Funded Reviewers
On Wed, Jan 26, 2011 at 9:31 AM, David E. Wheeler wrote: > Part of GCOC's success has been in allowing a class of people to > participate who otherwise would have had to get summer jobs flipping > burgers. > This is essentially the idea for this grant, to fund a person in learning a new skill. And hopefully, such a person might continue to offer support for the project after the initial experience. -- Regards, Richard Broersma Jr.
Re: [HACKERS] Seeking Mentors for Funded Reviewers
On Wed, Jan 26, 2011 at 7:38 AM, David Fetter wrote: > I'm thinking that we should consider *very* carefully before we > introduce payments into what had been an all-volunteer effort. You > may get people to do things they might not otherwise have done, but > you'll also make people wonder whether they should be volunteering at > all. > > Offhand, I'd say this is a really bad > idea.<http://www.postgresql.org/about/donate> Wow, I hadn't considered this. But I'm reminded of GSOC, which is essentially doing something similar. Has this effect already taken place among the volunteering patch writers? -- Regards, Richard Broersma Jr.
Re: [HACKERS] Seeking Mentors for Funded Reviewers
On Wed, Jan 26, 2011 at 3:12 AM, Simon Riggs wrote: > > We have the funding. We're looking for mentors. Next we'll just > > about ready to open the application process. But I'd expect several > > weeks to pass before have ready to look at applicants. > > You're paying the reviewers; are you paying the mentors? > I don't believe that PgUS can use the word "pay." Since PgUS is a 501(c)3 non-profit organization, there are restrictions how funding can be used. One such way is to award grants to provide a learning experience by development of a well defined deliverable. are you paying the mentors? > Honestly, I hadn't considered the possibility of funding mentors. I'll have to raise this question with PgUS to see if there is a provision for this. However at this point-in-time, I am only proposing funding the reviewer. The utlimate goal is to add more people to the ranks of reviewers. > Are the mentors restricted to being US members? > The mentors can be anyone. If the possibility exists that PgUS can fund mentors, then prospective mentors will need to be or become members of PgUS. -- Regards, Richard Broersma Jr.
Re: [HACKERS] Seeking Mentors for Funded Reviewers
On Tue, Jan 25, 2011 at 11:15 AM, Josh Berkus wrote: > For 9.1, what about doing a bug-finding bounty when we get into the 9.1 > beta cycle? Mozilla has been using bug bounties and they've been > surprisingly successful. > This is do-able. We just have to present this in a way that will meet the requirements of the 501c. It needs to be a learning experience and there needs to be a well defined criteria of what will be delivered by the person awarded with the grant. -- Regards, Richard Broersma Jr.
Re: [HACKERS] Seeking Mentors for Funded Reviewers
On Tue, Jan 25, 2011 at 9:46 AM, Josh Berkus wrote: > In several weeks, the review period for 9.1 will be over. Is this a plan > for 9.2? Yes. Our timing for this grant is unfortunate as it will likely be issued too late to be useful for the 9.1 commit-fests. The delay is mostly my fault. I wasn't able to devote enough time to the grant process late last year. However, having a mentee begin work early in the 9.2 commit-fest cycle might be advantageous. I imagine that there is less pressure to review all of the patches in the early commit-fests. Perhaps this will give prospective mentors the ablility to spend more time with mentee's. -- Regards, Richard Broersma Jr.
Re: [RRR] [HACKERS] Seeking Mentors for Funded Reviewers
On Tue, Jan 25, 2011 at 12:42 AM, Dave Page wrote: > > Will the scheme be open to everyone, or just .USians? I do believe that such grants are limited to members of PgUS. Although, I should mention that there's no restriction for residents of any country becoming a member of PgUS. > If the latter, > I'd be a little concerned that it may have a negative effect on > attracting reviewers from outside the US. > Hmm... I hadn't considered the possibility of PgUS grants beings a turn-off to potential non-US residents. Would PgUS's open enrollment alleviate your concern? -- Regards, Richard Broersma Jr.
Re: [HACKERS] Seeking Mentors for Funded Reviewers
On Mon, Jan 24, 2011 at 5:53 PM, Josh Berkus wrote: > On 1/24/11 12:17 PM, Richard Broersma wrote: > > PgUS is preparing to fund a grant for PgUS members to learn and > > participate in the patch review process. We looking for experienced > > reviewers that can assist a candidate through to process of testing a > > patch - to submitting the final review. The ultimate deliverable > > would be the actual review posted to Hackers. > > > > Would anyone be available to assist with this? > > Do we have candidate mentees? > > Not at the moment. Were still in the process of getting ready. We have the funding. We're looking for mentors. Next we'll just about ready to open the application process. But I'd expect several weeks to pass before have ready to look at applicants. -- Regards, Richard Broersma Jr.
[HACKERS] Seeking Mentors for Funded Reviewers
PgUS is preparing to fund a grant for PgUS members to learn and participate in the patch review process. We looking for experienced reviewers that can assist a candidate through to process of testing a patch - to submitting the final review. The ultimate deliverable would be the actual review posted to Hackers. Would anyone be available to assist with this? Thoughts? -- Regards, Richard Broersma Jr. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "writable CTEs"
On Wed, Dec 22, 2010 at 10:44 AM, Marko Tiikkaja wrote: > I'd prefer something short but easily understandable, but those two might be > mutually exclusive. Volatile CTE's doesn't add any more clarity either. Maybe "Round Trip Reduction" CTE's. :) -- Regards, Richard Broersma Jr. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hypothetical Indexes - PostgreSQL extension - PGCON 2010
On 03/12/10 08:14, Jeroen Vermeulen wrote: On 2010-12-02 00:48, Ana Carolina Brito de Almeida wrote: We would like to inform you all that our extension to PostgreSQL, that includes hypothetical indexes (and soon index self-tuning), is available through a sourgeforge project. Looking at the sourceforge page, I'm left with one burning question: what are they for? I believe they're for performance testing. Add hypothetical index (takes very little time). Check estimated costs with EXPLAIN. If good, add real index (takes lots of time). Of course, they're also good for indexing hypothetical data ;-) -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE CONSTRAINT TRIGGER
On Fri, Nov 5, 2010 at 2:37 PM, Alvaro Herrera wrote: > Recent developments have turned it back into non-deprecated mode; it's > not going anywhere, and it needs to be fully documented. >From what I recall, there isn't anything in the trigger documentation or CREATE CONSTRAINT TRIGGER documentation that says the trigger function must explicitly raise an exception to create the notification that the custom constraint was violated. Would this be a good place for it? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Domains versus arrays versus typmods
On 20/10/10 01:47, Robert Haas wrote: On Tue, Oct 19, 2010 at 6:14 PM, Tom Lane wrote: Comments? It might be reasonable to back-patch whatever we decide on into 9.0, because it is so new, but I would be reluctant to go back further unless we have some evidence that it's bothering people. It seems to me that this can could have a lot of worms in it, and I fear that there could be several rounds of fixes, which I would rather not inflict on users of supposedly-stable branches. The work-around I applied when I stumbled across this was just to apply an explicit cast before my function's RETURN. That neatly solves my particular problem (which I at first thought was a formatting issue somewhere in my app). The real danger with this is the opportunity to end up with occasional bad data in tables, quite possibly unnoticed. If I'd come across this in an existing system rather than a new app I'm pretty sure it would have confused me for a lot longer than it did. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL command to edit postgresql.conf, with comments
On Tue, Oct 12, 2010 at 3:54 PM, Josh Berkus wrote: > Oh. Now that's an interesting perspective ... you're suggesting that we > take the comments and apply them as COMMENTS on the specific pg_settings? On a side note regarding comments, I'd like to make a request for a more comprehensive commenting mechanism. The first though that comes to my mind would allow for comments to be stored and annotated using XML or sgml. It'd be nice to be able to generate user documentation from selected comments taken from application derived database objects. I don't know, maybe this is already possible. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: [HACKERS] Online backup cause boot failure, anyone know why?
Sorry, wrong word, it should be job. You mean the wrong type of checkpoint causes XLOG file recovery fail? I was confused, the XLOG files seem corrupted, is it also caused by the checkpoint type? If so , why it can do this? -- Richard 2010-08-05 - 发件人：Nicolas Barbier 发送日期：2010-08-05 23:43:22 收件人：Richard 抄送：Heikki Linnakangas; Tom Lane; pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? 2010/8/5 Richard : > All jods are done by client code, not manually. What is a jod? > I still did't not understand what you said. > What break what? The fact that you replaced CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE is the cause of your problem. You "broke" the correctness of the system by doing so. 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] Online backup cause boot failure, anyone know why?
All jods are done by client code, not manually. I still did't not understand what you said. What break what? Thandks! -- Richard 2010-08-05 - 发件人：Heikki Linnakangas 发送日期：2010-08-05 23:21:54 收件人：Richard 抄送：Tom Lane; pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? On 05/08/10 17:56, Richard wrote: > I am sorry, my English is poor. > I was confused by what you said. > What do you mean by saying "that'd break it"! Replacing CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE broke it. Don't do that. If you want to change the behavior of pg_start_backup() to perform the checkpoint immediately, change "CHECKPOINT_WAIT" to "CHECKPOINT_WAIT | CHECKPOINT_IMMEDIATE". The usual work-around though is not to hack the source code, but perform a manual CHECKPOINT just before calling pg_start_backuo(). That makes the checkpoint performed by pg_start_backup() finish quickly. -- 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] Online backup cause boot failure, anyone know why?
I am sorry, my English is poor. I was confused by what you said. What do you mean by saying "that'd break it"! -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:44:50 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? "Richard" writes: > For perfromance purpose , I change the pg_start_backup checkpoint type from > CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? Oh, so this isn't so much "8.3.7" as "randomly-hacked-up 8.3.7". Yes, that'd break it, I believe. CHECKPOINT_IMMEDIATE doesn't imply waiting. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
I am sorry, my English is poor. I was confused by what you said. What do you mean by saying "that'd break it"! -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:44:50 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? "Richard" writes: > For perfromance purpose , I change the pg_start_backup checkpoint type from > CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? Oh, so this isn't so much "8.3.7" as "randomly-hacked-up 8.3.7". Yes, that'd break it, I believe. CHECKPOINT_IMMEDIATE doesn't imply waiting. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: Re: [HACKERS] Re: Re: [HACKERS] Online backup cause bootfailure,anyone know why?
Thanks for your patience. I use XLogCtl->Insert.forcePageWrites for XLOG recycling flag. So after pg_start_backup, no more XLOG files will be recycled. And as I said above, I make a CHECKPOINT_IMMEDIATE checkpoint in pg_start_backup, instead CHECKPOINT_WAIT. That all I did to code. I wonder whether the XLOG is corrupted, because the first error is "unexpected pageaddr %X/%X in log file %u, segment %u, offset %u" .The error page addr contains a LSN 8K before it should do and I compare the two pages , they are almost the same except the last several bytes. So it should not be missing some XLOG, can be the XLOG file or buffer was corrupted. -- Richard 2010-08-05 - 发件人：Robert Haas 发送日期：2010-08-05 22:38:37 收件人：Richard 抄送：Andrew Dunstan; pgsql-hackers 主题：Re: Re: [HACKERS] Re: Re: [HACKERS] Online backup cause bootfailure,anyone know why? On Thu, Aug 5, 2010 at 10:20 AM, Richard wrote: > Oh sorry, I missed something. I turned off the XLOG archive in code after > pg_start_backup so the pg_xlog directory contains all the xlog files. > And for performance purpose, I change the checkpoint type in pg_start_backup > to CHECKPOINT_IMMEDIATE, does it matter? > The PG log I mentioned above is the running error log not the XLOG. Well, it's pretty clear that you're missing some WAL; otherwise, you wouldn't be getting an error that says "WAL ends before end time of backup dump". It's hard to speculate as to whether that's a configuration problem or a result of your custom modifications to the source code, since you haven't provided many details about either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Online backup cause boot failure, anyone know why?
Thanks for replying. But I could't find relation between the RequestXLogSwitch function and the error I met. For perfromance purpose , I change the pg_start_backup checkpoint type from CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:04:30 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? "Richard" writes: > PS : I am using PG 8.3.7 I believe there's a related bug fix in 8.3.8. BTW, -hackers is not the place for this type of question. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why?
Oh sorry, I missed something. I turned off the XLOG archive in code after pg_start_backup so the pg_xlog directory contains all the xlog files. And for performance purpose, I change the checkpoint type in pg_start_backup to CHECKPOINT_IMMEDIATE, does it matter? The PG log I mentioned above is the running error log not the XLOG. -- Richard 2010-08-05 - 发件人：Robert Haas 发送日期：2010-08-05 22:07:45 收件人：Richard 抄送：Andrew Dunstan; pgsql-hackers 主题：Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why? On Thu, Aug 5, 2010 at 9:50 AM, Richard wrote: > I reboot PG because I found PG recovery end point if far away from the > actual end point of the XLOG on the backup directory, so I want to test if > the original DB is OK. > Unfortunately, I got the same PG log on the original DB. I don't unstand what > you said, I missing what? The transaction logs archived during the backup? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online backup cause boot failure, anyone know why?
Thanks for replying. But I could not find any relation between the RequestXLogSwitch function and the error I met. For perfromance purpose , I change the pg_start_backup checkpoint type from CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:04:30 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? "Richard" writes: > PS : I am using PG 8.3.7 I believe there's a related bug fix in 8.3.8. BTW, -hackers is not the place for this type of question. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: [HACKERS] Online backup cause boot failure, anyone know why?
I reboot PG because I found PG recovery end point if far away from the actual
end point of the XLOG on the backup directory, so I want to test if the
original DB is OK.
Unfortunately, I got the same PG log on the original DB. I don't unstand what
you said, I missing what?
--
Richard
2010-08-05
-
发件人：Andrew Dunstan
发送日期：2010-08-05 21:40:13
收件人：Richard
抄送：pgsql-hackers
主题：Re: [HACKERS] Online backup cause boot failure, anyone know why?
On 08/05/2010 09:19 AM, Richard wrote:
> I want to create a database backup when PG is running, so I call
> pg_start_backup(''), scp the data to a backup directory, pg_stop_backup.
> Then I reboot PG , PG boot failed with log like "unexpected pageaddr X/X in
> log file X, segment X, offset X" "WAL ends before end time of backup dump".
> Then I check the failure XLOG file, found the error page contains a pageaddr
> 8K before it should be, and the failure XLOG record a ONLINE CHECKPONT with
> 60 bytes in former page, the other 4 bytes missing.
>
> Any one met this before? Please help me!
>
>
This question really belongs on the pgsql-general list, not the -hackers
list.
If all you copied was the data directory then you haven't done this
right anyway. See
<http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-TIPS>
Why did you reboot postgres after taking your backup?
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] Online backup cause boot failure, anyone know why?
PS : I am using PG 8.3.7
--
Richard
2010-08-05
-
发件人：Richard
发送日期：2010-08-05 21:19:27
收件人：pgsql-hackers
抄送：
主题：Online backup cause boot failure, anyone know why?
I want to create a database backup when PG is running, so I call
pg_start_backup(''), scp the data to a backup directory, pg_stop_backup.
Then I reboot PG , PG boot failed with log like "unexpected pageaddr X/X in log
file X, segment X, offset X" "WAL ends before end time of backup dump".
Then I check the failure XLOG file, found the error page contains a pageaddr
8K before it should be, and the failure XLOG record a ONLINE CHECKPONT with 60
bytes in former page, the other 4 bytes missing.
Any one met this before? Please help me!
--
Richard
2010-08-05
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Online backup cause boot failure, anyone know why?
I want to create a database backup when PG is running, so I call
pg_start_backup(''), scp the data to a backup directory, pg_stop_backup.
Then I reboot PG , PG boot failed with log like "unexpected pageaddr X/X in log
file X, segment X, offset X" "WAL ends before end time of backup dump".
Then I check the failure XLOG file, found the error page contains a pageaddr
8K before it should be, and the failure XLOG record a ONLINE CHECKPONT with 60
bytes in former page, the other 4 bytes missing.
Any one met this before? Please help me!
--
Richard
2010-08-05
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Two problems when using Postgresql8.3.7, Please help me!
1.To add live HA to PG, I transfer WAL of a database instance(Primary node) to another database instance (standby node) at real time, and keep startup alive in standby node to recovery WAL online,so that standby node can be a hot standby. But I got some trouble. When standby node switch to primary mode to accept connections, of course after startup initilize the WAL and exit, the postgres process ereportERROR when mdread function enter ERROR branch, and I got message like this "could not read block X of relation X/X/X: read only Xof X bytes". I spent two days to figure out what happened, but it is too hard.Please help me. 2. When restore data from a LIVE backup , I got message like "unexpected pageaddr %X/%X in log file %u, segment %u, offset %u" "WAL ends before end time of backup dump". It seems the WAL was corrupted. I found the LSN where the error occured contained the wrong pageaddr, the pageaddr was 8K before it's real address.What was wrong? 2010-08-04 Richard
[HACKERS] Too problems when using Postgresql8.3.7,Please help!
1.To add live HA to PG, I transfer WAL of a database instance(Primary node) to another database instance (standby node) at real time, and keep startup alive in standby node to recovery WAL online,so that standby node can be a hot standby.But I got some trouble. When standby node switch to primary mode to accept connections, of course after startup initilize the WAL and exit, the postgres process ereport(ERROR when mdread function enter ERROR branch, and I got message like this "could not read block X of relation X/X/X: read only %d of %d bytes". I spent two days to figure out what happened, but it is too hard.Please help me. 2. When restore data from a LIVE backup , I got message like "unexpected pageaddr %X/%X in log file %u, segment %u, offset %u" "WAL ends before end time of backup dump". It seems the WAL was corrupted. I found the LSN where the error occured contained the wrong pageaddr, the pageaddr was 8K before it's real address.What was wrong? -- Richard 2010-08-03 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reducing NUMERIC size for 9.1
On 16/07/10 13:44, Brendan Jurd wrote: pg_column_size() did return the results I was expecting. pg_column_size(0::numeric) is 8 bytes on 8.4 and it's 6 bytes on HEAD with your patch. At this scale we should be seeing around 2 million bytes saved, but instead the tables are identical. Is there some kind of disconnect in how the new short numeric is making it to the disk, or perhaps another effect interfering with my test? You've probably got rows being aligned to a 4-byte boundary. You're probably not going to see any change unless you have a couple of 1-byte columns that get placed after the numeric. If you went from 10 bytes down to 8, that should be visible. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 15/07/10 20:43, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I was assuming the process would be something like: 1. Move existing \d queries into functions* 2. Convert psql to use those Oops! There's goes your ability to handle older versions of Postgres from the existing psql Arse. It's little details like this that demonstrate why I'm a user and not a hacker :-) -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 15/07/10 19:44, Robert Haas wrote: On Jul 15, 2010, at 11:59 AM, Simon Riggs wrote: I imagined that we would do something similar to EXPLAIN, a set of text rows returned. That seems rather wretched for machine-parsability, which I think is an important property for anything we do in this area. We need to think harder about how we could structure this to allow returning more than just a tabular result set while still allowing clients easy programmatic access to the underlying data. It should be possible to migrate \d options to using new outputs, when everything works in a useful manner. Probably not in this release. Feature sounds useful. I think our \dxx commands have grown a little unwieldy in the last version or two. Which is not to say you can take \d away :-) I was assuming the process would be something like: 1. Move existing \d queries into functions* 2. Convert psql to use those 3. Add "SHOW xxx" and have it return a single query Have it also issue "NOTICE: from psql, try \dt for more info" If/when we have multiple sets returned from one query it should be simple to provide something pretty close to \d... from a single command. Trying to format the data in the backend is probably just going to frustrate writers of different clients (of which I think we have quite a few now). * These functions could then be back-ported as an admin-pack too for clients/apps that wanted cross-version compatibility for these sorts of things. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standard_conforming_strings
On 14/07/10 15:48, Robert Haas wrote: On Fri, Jan 29, 2010 at 10:02 PM, Josh Berkus wrote: An actual plan here might look like "let's flip it before 9.1alpha1 so we can get some alpha testing cycles on it" ... "Hey, let's flip it in 9.1 CF 1, so that we can have some alpha testing cycles on it." Should we do this? Patch attached. Any reason not to add a line to the 9.0 docs/release notes saying "WARNING: The PGDG currently plan to change this setting's default in 9.1"? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On 30/06/10 18:11, Magnus Hagander wrote: On Wed, Jun 30, 2010 at 18:33, Richard Huxton wrote: IMHO The real solution would be something that could strip/rewrite the constraint on restore rather than trying to prevent people being stupid though. People *will* just tag their functions as immutable to get them to work. Are you sure? The people most likely to "just tag their functions as immutable", are the same ones most unlikely to know *how to do that*. At least for what I think is the majority case - which is calling builtin functions. People just cut and paste this stuff from ancient blog entries. Understanding is not necessary. Hell, I do it sometimes if I'm dealing with something like LDAP where I don't really have a deep knowledge of the situation. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Check constraints on non-immutable keys
On 30/06/10 17:11, Robert Haas wrote: On Wed, Jun 30, 2010 at 11:49 AM, Tom Lane wrote: Robert Haas writes: My scintillating contribution to this discussion is the observation that unrestorable dumps suck. No doubt, but is this a real problem in practice? Magnus tells me that that was what prompted his original email. I've done it. Luckily only with a small and fully functioning database so I could drop the constraint and re-dump it. Had a "recent_date" domain that was making sure new diary-style entries had a plausible date. Of course, two years later my dump can no longer restore the oldest record :-( IMHO The real solution would be something that could strip/rewrite the constraint on restore rather than trying to prevent people being stupid though. People *will* just tag their functions as immutable to get them to work. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
Replying to my own post - first sign of madness... Let's see if I've got the concepts clear here, and hopefully my thinking it through will help others reading the archives. There are two queues: 1. Cleanup on the master 2. Replay on the slave Running write queries on the master adds to both queues. Running (read-only) queries on the slave prevents you removing from both queues. There are two interesting measurements of "age"/"size": 1. Oldest item in / length of queue (knowable) 2. How long will it take to clear the queue (estimable at best) You'd like to know #2 to keep up with your workload. Unfortunately, you can't for certain unless you have control over new incoming queries (on both master and slave). You might want four separate GUCs for the two measurements on the two queues. We currently have two that (sort of) match #1 "Oldest item" (vacuum_defer_cleanup_age, max_standby_delay). Delaying replay on a slave has no effect on the master. If a slave falls too far behind it's responsible for catch-up (via normal WAL archives). There is no point in delaying cleanup on the master unless it's going to help one or more slaves. In fact, you don't want to start delaying cleanup until you have to, otherwise you're wasting your delay time. This seems to be the case with vacuum_defer_cleanup_age. If I have a heavily-updated table and I defer vacuuming then when any given query starts on the slave it's going to be half used up already. There's also no point in deferring cleanup on the master if the standby is already waiting on a conflict that will cause its queries to be cancelled anyway. Not only won't it help, but it might make things worse since transactions will be cancelled, the conflict will be replayed and (presumably) queries will be re-submitted only to be cancelled again. This is what Greg Smith's discussion of the keep-alives was about. Giving the master enough information to be smarter about cleanup (and making the conflicts more fine-grained). The situation with deferring on one or both ends of process just gets more complicated with multiple slaves. There's all sorts of unpleasant feedback loops I can envisage there. For the case of single slave being used to run long reporting queries the ideal scenario would be the following. Master starts deferring vacuum activity just before the query starts. When that times out, the slave will receive the cleanup info, refuse to replay it and start its delay. This gives you a total available query time of: natural time between vacuums + vacuum delay + WAL transfer time + standby delay I can think of five useful things we should be doing (and might be already - don't know). 1. On the master, deduce whether the slave is already waiting on a query. If so, don't bother delaying cleanup. Clearly you don't want to be signalling hundreds of times a second though. Does the slave pause fetching via streaming replication if replay is blocked on a query? Could we signal "half-way to max-age" or some such? 2. Perhaps simpler than trying to make the master smarter, just allow SET this_transaction_is_probably_a_long_one=true on the slave. That (a) clears the queue on the slave and (b) sends the signal to the master which then starts deferring vacuum. 3. Do a burst of cleanup activity on the master after blocking. This should concentrate conflicts together when they reach the slave. Perhaps vacuum_defer_cleanup_age should be vacuum_deferred_queue_size and measure the amount of work to do, rather than the max age of the oldest cleanup (if I've understood correctly). 4. Do a burst of replay on the slave after blocking. Perhaps every time it cancels a transaction it should replay at least half the queued WAL before letting new transactions start. Or perhaps it replays any vacuum activity it comes across and then stops. That should sync with #2 assuming the slave doesn't lag the master too much. 5. I've been mixing "defer" and "delay", as do the docs. We should probably settle on one or the other. I think defer conveys the meaning more precisely, but what about non-native English speakers? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 26/02/10 14:45, Heikki Linnakangas wrote: Richard Huxton wrote: On 26/02/10 08:33, Greg Smith wrote: I'm not sure what you might be expecting from the above combination, but what actually happens is that many of the SELECT statements on the table *that isn't even being updated* are canceled. You see this in the logs: Hmm - this I'd already figured out for myself. It's just occurred to me that this could well be the case between databases too. Database A gets vacuumed, B gets its queries kicked off on the standby. No, it's per-database already. Only queries in the same database are canceled. That's a relief. Dumb non-hacker question: why do we cancel all transactions rather than just those with "ACCESS SHARE" on the vacuumed table in question? Is it the simple fact that we don't know what table this particular section of WAL affects, or is it the complexity of tracking all this info? The problem is that even if transaction X doesn't have an (access share) lock on the vacuumed table at the moment, it might take one in the future. Simon proposed mechanisms for storing the information about vacuumed tables in shared memory, so that if X takes the lock later on it will get canceled at that point, but that's 9.1 material. I see - we'd need to age the list of vacuumed tables too, so when the oldest transactions complete the correct flags get cleared. Can we not wait to cancel the transaction until *any* new lock is attempted though? That should protect all the single-statement long-running transactions that are already underway. Aggregates etc. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 26/02/10 14:10, Heikki Linnakangas wrote: Ideally the standby would stash away the old pages or tuples somewhere so that it can still access them even after replaying the WAL records that remove them from the main storage. I realize that's not going to happen any time soon because it's hard to do, but that would really be the most robust fix possible. Something like snapshotting a filesystem, so updates continue while you're still looking at a static version. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot Standby query cancellation and Streaming Replication integration
On 26/02/10 08:33, Greg Smith wrote: There are a number of HS tunables that interact with one another, and depending your priorities a few ways you can try to optimize the configuration for what I expect to be common use cases for this feature. > I've written a blog entry at http://blog.2ndquadrant.com/en/2010/02/tradeoffs-in-hot-standby-deplo.html that tries to explain all that background clearly, It did too. Thanks for the nice summary people can be pointed at. I'm not sure what you might be expecting from the above combination, but what actually happens is that many of the SELECT statements on the table *that isn't even being updated* are canceled. You see this in the logs: Hmm - this I'd already figured out for myself. It's just occurred to me that this could well be the case between databases too. Database A gets vacuumed, B gets its queries kicked off on the standby. Granted lots of people just have the one main DB, but even so... LOG: restored log file "000100A5" from archive ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. STATEMENT: SELECT sum(abalance) FROM pgbench_accounts; Basically, every time a WAL segment appears that wipes out a tuple that SELECT expects should still be visible, because the dead row left behind by the update has been vacuumed away, the query is canceled. This happens all the time the way I've set this up, and I don't feel like this is a contrived demo. Having a long-running query on the standby while things get updated and then periodically autovacuumed on the primary is going to be extremely common in the sorts of production systems I expect want HS the most. I can pretty much everyone wanting HS+SR. Thousands of small DBs running on VMs for a start. Free mostly-live backup? Got to be a winner. Dumb non-hacker question: why do we cancel all transactions rather than just those with "ACCESS SHARE" on the vacuumed table in question? Is it the simple fact that we don't know what table this particular section of WAL affects, or is it the complexity of tracking all this info? If you're running a system that also is using Streaming Replication, there is a much better approach possible. "Requires keep-alives with timestamps to be added to sync rep feature" If those keep-alives flowed in both directions, and included both timestamps *and* xid visibility information, the master could easily be configured to hold open xid snapshots needed for long running queries on the standby when that was necessary. Presumably meaning we need *another* config setting to prevent excessive bloat on a heavily updated table on the master. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plperl.on_init - bug or just me?
On 25/02/10 17:10, Andrew Dunstan wrote: Richard Huxton wrote: Presumably "Safe" just clamps down and my sub isn't marked as acceptable. Is this intended, or am I doing something stupid? It's intended (at least by me). Also, please see the recent discussion about loading extra stuff into the Safe container. Ah - looks like I've missed a thread. > At the very least that has been shelved for now. We're going to proceed with deliberation in this area. I'm quite concerned to make sure that we don't provide an opportunity for people to undermine the behaviour of the trusted language. Fair enough. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] plperl.on_init - bug or just me?
From memory and the thread below, I thought one of the key uses was to let me use a module from trusted plperl. http://archives.postgresql.org/pgsql-hackers/2010-02/msg00167.php The example below has a TestModule that just exports one sub - visible from plerlu but not plperl. Presumably "Safe" just clamps down and my sub isn't marked as acceptable. Is this intended, or am I doing something stupid? postgresql.conf: plperl.on_init = 'use lib "/home/richardh/dev/"; use TestModule qw(add_one);' -- tries to call TestModule::add_one richardh=# SELECT add_one(1); ERROR: Undefined subroutine &TestModule::add_one called at line 1. CONTEXT: PL/Perl function "add_one" -- tries to call the exported main::add_one richardh=# SELECT add_one_e(1); ERROR: Undefined subroutine &main::add_one called at line 1. CONTEXT: PL/Perl function "add_one_e" -- plperlu - TestModule::add_one richardh=# SELECT add_one_u(1); add_one_u --- 2 (1 row) -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Probably badly timed suggestion: pl/perl calling style same as C style
With plperl.on_init allowing the loading of modules, might there be some
merit (and little cost) in allowing the same style of function-mapping
as with C functions?
CREATE FUNCTION add_one(integer) RETURNS integer
AS 'DIRECTORY/funcs', 'add_one'
LANGUAGE C STRICT;
CREATE FUNCTION add_one(integer) RETURNS integer
AS 'My::Package', 'add_one'
LANGUAGE plperl STRICT;
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FW: Unable to install PostgreSQL on Windows Server 2003 SP2
On 24/02/10 13:09, William wrote: Hello Pqsql-hacker, I have tried everything and ask many but cannot get my Installation of your PostgreSQL 8.4.1.1 to install. While you are having problems, take the opportunity to download 8.4.2 and work with that. That's the latest set of bugfixes. > I following all the steps exactly but I cannot get the PostgresSQL to install on my Windows Server 2003 Datacenter Edition SP2. Is there a problem in installing the PostgreSQL on this version of Windows which is Hosted at a Host Provider. if not why will it not install. I have tried everything but will not install. That is why I am here asking you hoping you can get me past this. Following is the installer log files that shows everything that went on during the install. Have you read the logs? Searching for "Error" is the best way to start. The first occurrence in bitrock_installer.log is at line 3244. If you read the 20 or so lines before that you will see that is says it installed everything: Success. You can now start the database server using... It then fails when trying to grant access to the data directory for your service account (the user the database runs as). Granting service account access to the data directory (using cacls): processed dir: D:\APPS\PostgreSQL\8.4\data The data is invalid. Failed to grant service account access to the data directory (D:\APPS\PostgreSQL\8.4\data) After that, you get more errors because the service couldn't be started. The second logfile is identical to the first. So - either there is a bug in the installer, or your administrator account in the virtual server doesn't have permission to do this installation properly. Doesn't matter which from your point of view. I'm not a Windows expert regarding PostgreSQL, but there are some obvious things to try. First thing to do - check if PostgreSQL is still installed and that you have a data directory in D:\APPS\PostgreSQL\8.4\data. If so, try granting permission on that data directory to the "postgres" user manually. Just right-click the folder and add full rights for "postgres". You should then be able to start the service manually (I think there is a menu item - if not it will be in the services control panel). If that all works, re-run the installer and you should be able to re-install the adminpack etc. over the top of your now working installation. If you didn't find the data directory, create it, grant permissions to "postgres" and then try a full re-install. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 17/02/10 18:30, David E. Wheeler wrote: On Feb 17, 2010, at 4:28 AM, Tim Bunce wrote: Umm, perhaps F->funcname(@args), or PG->funcname(@args), or ... ? Anyone got any better suggestions? PG is good. Or maybe DB? It's a module whose only use is embedded in a DB called PG - not sure those carry any extra info. It also treads on the toes of "PG->not_a_function" should such a beast be needed. I like "F->funcname" or "FN->funcname" myself. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 16/02/10 17:51, David E. Wheeler wrote:
On Feb 16, 2010, at 9:43 AM, Richard Huxton wrote:
Perhaps it would be better to be explicit about what's going on?
SEARCHPATH->function()
SCHEMA('public')->function2()
Or did "SP" mean "Stored Procedure"?
Yes.
Hmm - might be worth avoiding that in case we get actual
transaction-spanning stored procedures at any point.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 16/02/10 17:11, David E. Wheeler wrote:
On Feb 16, 2010, at 4:08 AM, Tim Bunce wrote:
Wouldn't work unless you'd installed an AUTOLOAD function into each
schema:: package that you wanted to use. (schema->SP::function_name()
could be made to work but that's just too bizzare :)
Maybe SP->schema('public')->function_name()? I kind of like the idea of objects
created for specific schemas, though (as in your example). Maybe that, too, is something
that could be specified in the `use`statement. Or maybe `SP::schema->function`?
That's kind of nice, keeps things encapsulated under SP. You could then do the
identifier quoting, too. The downside is that, once loaded, the schema package names
would be locked down. If I created a new schema in the connection, SP wouldn't know
about it.
Perhaps it would be better to be explicit about what's going on?
SEARCHPATH->function()
SCHEMA('public')->function2()
Or did "SP" mean "Stored Procedure"?
On a (kind of) related note, it might be worthwhile to mention
search_path in the docs and point out it has the same pros/cons as unix
file paths.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 15/02/10 10:32, Tim Bunce wrote:
On Mon, Feb 15, 2010 at 07:31:14AM +, Richard Huxton wrote:
Is there any value in having a two-stage interface?
$seq_fn = get_call('nextval(regclass)');
$foo1 = $seq_fn->($seq1);
$foo2 = $seq_fn->($seq2);
I don't think there's significant performance value in that.
Perhaps it could be useful to be able to pre-curry a call and
then pass that code ref around, but you can do that trivially
already:
$nextval_fn = sub { call('nextval(regclass)', @_) };
$val = $nextval_fn->($seq1);
or
$nextfoo_fn = sub { call('nextval(regclass)', 'foo_seqn') };
$val = $nextfoo_fn->();
Fair enough. Just wondered whether it was worth putting that on your
side of the interface. I'm forced to concede you probably have more
experience in database-related APIs than me :-)
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On 12/02/10 23:10, Tim Bunce wrote:
There was some discussion a few weeks ago about inter-stored-procedure
calling from PL/Perl.
I'd greatly appreciate any feedback.
Looks great.
PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from
PostgreSQL PL/Perl
I don't think you show an example with an explicit schema name being
used. Can't hurt to make it obvious.
$seqn = call('nextval(regclass)', $sequence_name);
Is there any value in having a two-stage interface?
$seq_fn = get_call('nextval(regclass)');
$foo1 = $seq_fn->($seq1);
$foo2 = $seq_fn->($seq2);
--
Richard Huxton
Archonet Ltd
--
Sent 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: Reduce the chatter to the log when starting a standby server.
On 12/02/10 15:37, Fujii Masao wrote: On Sat, Feb 13, 2010 at 12:28 AM, Robert Haas wrote: Well, let's come up with something else then. continuous_recovery ? One problem with the otherwise entirely wonderful HS/SR pairing is the whole business of the config parameters. They feel too bottom-up. Individually, each one makes sense but if you look at them on a page they don't say master/slave replication to me. What about something like: # Primary archive_mode = producer archive_producer_command = 'cp "%p" .../"%f"' max_consumers= 5 # Standby archive_mode = producer, consumer archive_producer_command = 'cp "%p" .../"%f"' archive_consumer_command = 'cp "%p" .../"%f"' consume_from = 'host=... user=...' Three other points that struck me: 1. Why have a separate recovery.conf file rather than just put the commands inline? We can use the include directive to have them in a separate file if required. 2. Why have a finish.replication file, rather than "SELECT pg_finish_replication()"? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
On 09/02/10 14:25, Jeroen Vermeulen wrote: Richard Huxton wrote: = Actual-cost threshold = Also stop using the generic plan if the statement takes a long time to run in practice. Do you mean: 1. Rollback the current query and start again 2. Mark the plan as a bad one and plan again next execute If you can figure out how to do #1 then you could probably do it for all queries, but I'm guessing it's far from simple to implement. I'm talking about #2. As a matter of fact #1 did come up in one of those discussions, but how do you know you're not killing the query juuust before it'd done, and then maybe executing a different plan that's no better? Ah, you'd need to be smarter when planning and also remember the expected rows from each node. That way if your (index driven) inner node was expecting 3 rows you could mark it to force a cancellation if it returns (say) 30 or more. You'd allow more slack in later processing and less slack earlier on where a bad estimate can explode the final number of rows. Or, there is always the case where we reverse-search an index to find the last 10 messages in a group say, but the particular group in question hasn't had a comment for months, so you trawl half the table. People regularly get bitten by that, and there's not much to be done about it. If we could abort when it looks like we're in worst-case rather than best-case scenarios then it would be one less thing for users to worry about. = Plan refresh = Periodically re-plan prepared statements on EXECUTE. This is also a chance for queries that were being re-planned every time to go back to a generic plan. Presumably some score based on update stats and vacuum activity etc. I was thinking of something very simple: re-do whatever we'd do if the statement were only being prepared at that point. Yes, I thought so, the scoring was for *when* to decide to cancel the old plan. I suppose total query-time would be another way to decide this plan needs reworking. The good side of all these ideas is good indeed. The bad side is plan instability. Someone somewhere will have a generic plan that turns out better than the specific plan (due to bad stats or config settings or just planner limitations). The question is (I guess): How many more winners will there be than losers? That's a good and surprising point, and therefore I'd like to draw attention away to a different point. :-) Yes, there will be losers in the sense that people may have optimized their use of prepared statements to whatever the current planner does. Maybe somebody out there even deliberately uses them to trick the planner into a different plan. But that is always going to happen; we're aiming for better plans, not for giving more detailed control over them. If you really can't take a change, don't upgrade. The competing point is: people out there may currently be forgoing prepared statements entirely because of erratic performance. To those people, if we can help them, it's like having a new feature. Oh, I'm persuaded, but that doesn't really get you anywhere :-) -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoiding bad prepared-statement plans.
On 09/02/10 12:08, Jeroen Vermeulen wrote: = Projected-cost threshold = [snip - this is the simple bit. Sounds very sensible. ] = Actual-cost threshold = Also stop using the generic plan if the statement takes a long time to run in practice. Do you mean: 1. Rollback the current query and start again 2. Mark the plan as a bad one and plan again next execute If you can figure out how to do #1 then you could probably do it for all queries, but I'm guessing it's far from simple to implement. = Plan refresh = Periodically re-plan prepared statements on EXECUTE. This is also a chance for queries that were being re-planned every time to go back to a generic plan. Presumably some score based on update stats and vacuum activity etc. The good side of all these ideas is good indeed. The bad side is plan instability. Someone somewhere will have a generic plan that turns out better than the specific plan (due to bad stats or config settings or just planner limitations). The question is (I guess): How many more winners will there be than losers? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.5 vs. 9.0
On 21/01/10 09:37, Dave Page wrote: In an attempt to pre-empt the normally drawn-out discussions about what the next version of PostgreSQL will be numbered. the core team have discussed the issue and following a lenghty debate lasting literally a few minutes decided that the next release shall be Wait for it 9.0. You don't have a code-name. All the cool kids have code-names for their projects. There - that should distract everyone from actual release-related work for the next week or so :-) -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
Greg Stark wrote: > On Tue, Dec 1, 2009 at 9:57 PM, Richard Huxton wrote: >> Why are we writing out the hint bits to disk anyway? Is it really so >> slow to calculate them on read + cache them that it's worth all this >> trouble? Are they not also to blame for the "write my import data twice" >> feature? > > It would be interesting to experiment with different strategies. But > the results would depend a lot on workloads and I doubt one strategy > is best for everyone. > > It has often been suggested that we could set the hint bits but not > dirty the page, so they would never be written out unless some other > update hit the page. In most use cases that would probably result in > the right thing happening where we avoid half the writes but still > stop doing transaction status lookups relatively promptly. The scary > thing is that there might be use cases such as static data loaded > where the hint bits never get set and every scan of the page has to > recheck those statuses until the tuples are frozen. And how scary is that? Assuming we cache the hints... 1. With the page itself, so same lifespan 2. Separately, perhaps with a different (longer) lifespan. Separately would then let you trade complexity for compactness - "all of block B is deleted", "all of table T is visible". So what is the cost of calculating the hint-bits for a whole block of tuples in one go vs reading that block from actual spinning disk? > There does need to be something like the hint bits which does > eventually have to be set because we can't keep transaction > information around forever. Even if you keep the transaction > information all the way back to the last freeze date (up to about 1GB > and change I think) then the data has to be written twice, the second > time is to freeze the transactions. In the worst case then reading a > page requires a random page access (or two) from anywhere in that 1GB+ > file for each tuple on the page (whether visible to us or not). While on that topic - I'm assuming freezing requires substantially more effort than updating hint bits? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Block-level CRC checks
Bruce Momjian wrote: > Tom Lane wrote: >> >> The suggestions that were made upthread about moving the hint bits >> could resolve the second objection, but once you do that you might >> as well just exclude them from the CRC and eliminate the guessing. > > OK, crazy idea #3. What if we had a per-page counter of the number of > hint bits set --- that way, we would only consider a CRC check failure > to be corruption if the count matched the hint bit count on the page. Can I piggy-back on Bruce's crazy idea and ask a stupid question? Why are we writing out the hint bits to disk anyway? Is it really so slow to calculate them on read + cache them that it's worth all this trouble? Are they not also to blame for the "write my import data twice" feature? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] IS DISTINCT FROM vs. ANY
David Fetter wrote: > Folks, > > Shouldn't this work and produce a "true?" > > SELECT NULL IS NOT DISTINCT FROM ANY(ARRAY['a',NULL]); > ERROR: syntax error at or near "ANY" > LINE 1: SELECT NULL IS NOT DISTINCT FROM ANY(ARRAY['a',NULL]); It should, but probably depends on whether "IS NOT DISTINCT" should be considered an "operator". http://www.postgresql.org/docs/8.4/static/functions-comparisons.html#AEN16561 Got caught by the same thing a couple of days ago. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal - temporal contrib module
Heikki Linnakangas wrote: > Scott Bailey wrote: >> I would like to add a temporal contrib module. > I'm very pleased to see people working on temporal issues, BTW! Me too - common use-case and difficult to handle without the right types/operators. >> Nulls - A common use case for periods is for modeling valid time. Often >> the end point is not known. For instance, you know when an employee has >> been hired but the termination time typically wouldn't be known ahead of >> time. We can either represent these with a null end time or with >> infinity. But I'm not sure how to deal with them. Obviously we can test >> for containment and overlap. But what about length or set operations? > > Hmm. Infinity feels like a better match. The behavior of length and set > operations falls out of that naturally. For example, length of a period > with an infinite beginning or end is infinite. For set operations, for > example the intersection of [123, infinity] and [100, 160] would be > [123, 160]. There are cases where one time is genuinely unknown, and there we need a null. For the "until further notice" scenarios, infinity seems the sensible choice. Where a null is present length is clearly null, and sets I guess should propagate the nulls. [123,null] intersecting [100,160] should be [123,null]. That's assuming we've got a guarantee that from<=to for all periods. >> Temporal Keys - We need two types of temporal keys. A primary key, >> exclusion type prevents overlap so someone isn't at two places at the >> same time. You're going to upset a lot of managers if they can't do that ;-) -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using views for row-level access control is leaky
Heikki Linnakangas wrote: > The most useful "automatic" annotation I can see is to treat functions > implementing B-tree operators as safe. I *think* that's safe, anyway. Index lookups and single-type comparisons were the only things I could come up with as safe. Unless there is some way to generate an error from geometric ops (overflow or some such). Anything involving a type-cast can obviously be finessed. If you allow arithmetic then you could trigger an overflow or divide-by-zero error. Hmm - you can probably do something evil with non-UTF8 characters if you allow string operations. Would string comparisons be safe (because a literal would be caught before the view gets evaluated)? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using views for row-level access control is leaky
Pavel Stehule wrote: > > postgres=# create or replace function vv(int, int) returns bool as > $$begin raise notice '% %', $1, $2; return true; end$$ language > plpgsql COST 0.01; > CREATE FUNCTION > postgres=# select * from v where vv(a,b);NOTICE: 10 20 > a │ b > ───┼─── > (0 rows) > > still I have not bad result, but, yes, I see what I could not to see. Ah - that's the problem. It's not possible to get the "hidden" values into the result set, but it is possible to see them. It only matters if you are using the view to prevent access to certain rows. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using views for row-level access control is leaky
Richard Huxton wrote: > Heikki Linnakangas wrote: >> CREATE VIEW phone_number AS >> SELECT person, phone FROM phone_data WHERE phone NOT LIKE '6%'; > >> CREATE OR REPLACE FUNCTION expose_person (person text, phone text) >> RETURNS bool AS $$ >> begin >> RAISE NOTICE 'person: % number: %', person, phone; >> RETURN true; >> END; $$ LANGUAGE plpgsql COST 0.01; >> >> postgres=> SELECT * FROM phone_number WHERE expose_person(person, phone); >> NOTICE: person: public person number: 12345 >> NOTICE: person: secret person number: 67890 >> person | phone >> ---+--- >> public person | 12345 Hmm - just using SQL (but with an expensive view filtering function): SELECT * FROM phone_number WHERE (CASE WHEN phone = '67890' THEN person::int ELSE 2 END)=2; ERROR: invalid input syntax for integer: "secret person" You could get a related problem where a view exposes a text column full of valid dates which the user then tries to cast to date. If the underlying table contains non-dates you could still get an error. Arguably the view should have handled the cast in this case though. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using views for row-level access control is leaky
Pavel Stehule wrote: > 2009/10/22 Heikki Linnakangas : >> That example I ran on CVS HEAD, but it's a generic problem on all versions. > postgres=# select version(); >version > > PostgreSQL 8.5devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC) > 4.4.1 20090725 > (1 row) > > postgres=# select * from x; > a │ b > ┼ > 10 │ 20 > (1 row) > > postgres=# create view v as select * from x where b <> 20; ^^^ This is the expression that needs to be expensive. Then the exposing function needs to be cheap. That makes the planner run the exposing function first. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using views for row-level access control is leaky
Pavel Stehule wrote: > What version do you have? > > I am cannot repeat it. It will depend on the relative cost of the clauses (though 0.0001 should have been enough to force it). Try: CREATE OR REPLACE FUNCTION row_hidden (phone text) RETURNS bool AS $$ BEGIN RETURN phone LIKE '6%'; END; $$ LANGUAGE plpgsql COST 999; CREATE VIEW phone_number AS SELECT person, phone FROM phone_data WHERE NOT row_hidden(phone); -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using views for row-level access control is leaky
Heikki Linnakangas wrote: > CREATE VIEW phone_number AS > SELECT person, phone FROM phone_data WHERE phone NOT LIKE '6%'; > CREATE OR REPLACE FUNCTION expose_person (person text, phone text) > RETURNS bool AS $$ > begin > RAISE NOTICE 'person: % number: %', person, phone; > RETURN true; > END; $$ LANGUAGE plpgsql COST 0.01; > > postgres=> SELECT * FROM phone_number WHERE expose_person(person, phone); > NOTICE: person: public person number: 12345 > NOTICE: person: secret person number: 67890 > person | phone > ---+--- > public person | 12345 Ouch! > 1. Change the planner so that conditions (and join!) in the view are > always enforced first, before executing any quals from the user-supplied > query. Unfortunately that would have a catastrophic effect on performance. I have the horrible feeling that you're going to end up doing this (possibly in conjunction with #4). Once you've executed a user-defined function on a "hidden" row I think the game is lost. That might even apply to non-trivial expressions too. > 2. As an optimization, we could keep the current behavior if the user > has access to all the underlying tables anyway, but that's nontrivial > because permission checks are supposed to be executed at runtime, not > plan time. > > 3. Label every function as safe or unsafe, depending on whether it can > leak information about the arguments. Classifying functions correctly > can be a bit tricky; e.g functions that throw an error on some input > values could be exploited. [snip] I'm sure there's a way to generate an error on-demand for rows with specific numbers. That opens you up to fishing for hidden rows. It might be possible to label a subset of operators etc as safe. I'd guess that would exclude any casts in it, and perhaps CASE. Hmm - you could probably generate a divide-by-zero or overflow error or some such for any targetted numeric value though. > 4. Make the behavior user-controllable, something along the lines of > "CREATE RESTRICTED VIEW ...", to avoid the performance impact when views > are not used for access control. Not pretty, but solves the problem. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] LIMIT 1 == EXISTS optimization?
I was just troubleshooting a slow query SELECT * FROM da_answer a WHERE a.provider_id IN ( SELECT visibility_bypass_providers( 0, 0 ) ) OR -- ownership ( EXISTS ( -- Visibility grant SELECT v.client_answer_id FROM sp_client_answervisibility v JOIN sp_sharing_group_provider_tree t ON v.sharing_group_id = t.sharing_group_id AND t.provider_id = 0 WHERE v.client_answer_id = a.answer_id AND v.visible = TRUE ) AND NOT EXISTS ( -- Visibility deny SELECT v.client_answer_id FROM sp_client_answervisibility v JOIN sp_sharing_group_provider_tree t ON v.sharing_group_id = t.sharing_group_id AND t.provider_id = 0 WHERE v.client_answer_id = a.answer_id AND v.visible = FALSE ) AND --ROI goes here a.covered_by_roi = TRUE ) The subplan 3 in the explain seemed to be looping through 3 million rows which explained the slowdown QUERY PLAN Bitmap Heap Scan on da_answer a (cost=222.43..946804.85 rows=22309 width=70) (actual time=15.717..5141.001 rows=34810 loops=1) Recheck Cond: (question_id = 18) Filter: ((hashed SubPlan 1) OR ((alternatives: SubPlan 2 or hashed SubPlan 3) AND (NOT (alternatives: SubPlan 4 or hashed SubPlan 5)) AND covered_by_roi)) -> Bitmap Index Scan on daanswer_questionid (cost=0.00..221.26 rows=35695 width=0) (actual time=6.438..6.438 rows=35060 loops=1) Index Cond: (question_id = 18) SubPlan 1 -> Result (cost=0.00..0.05 rows=1 width=0) (actual time=3.683..4.621 rows=1728 loops=1) SubPlan 2 -> Merge Join (cost=9.04..17.43 rows=1 width=0) (never executed) Merge Cond: (v.sharing_group_id = t.sharing_group_id) -> Index Scan using clientanswervisibility_answerid_sharinggroupid_allow on sp_client_answervisibility v (cost=0.00..8.38 rows=3 width=4) (never executed) Index Cond: (client_answer_id = $1) -> Sort (cost=9.04..9.04 rows=4 width=4) (never executed) Sort Key: t.sharing_group_id -> Bitmap Heap Scan on sp_sharing_group_provider_tree t (cost=2.05..9.03 rows=4 width=4) (never executed) Recheck Cond: (provider_id = 0) -> Bitmap Index Scan on sharinggroupprovidertree_providerid (cost=0.00..2.05 rows=4 width=0) (never executed) Index Cond: (provider_id = 0) SubPlan 3 -> Nested Loop (cost=0.00..52203.49 rows=2316644 width=4) (actual time=0.053..2827.799 rows=3321883 loops=1) -> Index Scan using sharinggroupprovidertree_providerid on sp_sharing_group_provider_tree t (cost=0.00..10.03 rows=4 width=4) (actual time=0.024..0.030 rows=3 loops=1) Index Cond: (provider_id = 0) -> Index Scan using spclientanswervisibility_sharinggroupid on sp_client_answervisibility v (cost=0.00..13011.17 rows=14877 width=8) (actual time=0.014..512.286 rows=1107294 loops=3) Index Cond: (v.sharing_group_id = t.sharing_group_id) Filter: v.visible SubPlan 4 -> Nested Loop (cost=0.00..8.19 rows=1 width=0) (never executed) -> Index Scan using clientanswervisibility_answerid_sharinggroupid_deny on sp_client_answervisibility v (cost=0.00..4.13 rows=1 width=4) (never executed) Index Cond: (client_answer_id = $1) -> Index Scan using sp_sharing_group_provider_tree_sharing_group_id_key on sp_sharing_group_provider_tree t (cost=0.00..4.05 rows=1 width=4) (never executed) Index Cond: ((t.sharing_group_id = v.sharing_group_id) AND (t.provider_id = 0)) SubPlan 5 -> Nested Loop (cost=2993.74..35065.77 rows=542897 width=4) (actual time=105.162..105.162 rows=0 loops=1) -> Bitmap Heap Scan on sp_sharing_group_provider_tree t (cost=2.05..9.03 rows=4 width=4) (actual time=0.037..0.047 rows=3 loops=1) Recheck Cond: (provider_id = 0) -> Bitmap Index Scan on sharinggroupprovidertree_providerid (cost=0.00..2.05 rows=4 width=0) (actual time=0.027..0.027 rows=3 loops=1) Index Cond: (provider_id = 0) -> Bitmap Heap Scan on sp_client_answervisibility v (cost=2991.69..8755.47 rows=3486 width=8) (actual time=35.030..35.030 rows=0 loops=3) Recheck Cond: ((v.sharing_group_id = t.sharing_group_id) AND (NOT v.visible)) -> Bitmap Index Scan on clientanswervisibility_answerid_sharinggroupid_deny (cost=0.00..2991.51 rows=3486 width=0) (actual time=35.027..35.027 rows=0 loops=3) Index Cond: (v.sharing_group_id = t.sharing_group_id) Total runtime: 5170.291 ms (42 rows) So on a whim I tossed a LIMIT 1 into both exists clauses: SELECT * FROM da_answer a
Re: [HACKERS] navigation menu for documents
David E. Wheeler wrote: > On Oct 1, 2009, at 1:12 AM, Richard Huxton wrote: > >>> Why wouldn't the entire TOC be in a collapsed list? >> >> Permanently on-screen? My only concern there would be for people viewing >> on phones etc. > > I have to admit that I'm never looking at the Pg docs on my iPhone. This > is mainly because I use them as a reference while hacking, and I'm not > (yet) hacking PostgreSQL on my phone. Ah, I _do_ look at them on my Nokia N810 when I'm on the train etc. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
David E. Wheeler wrote: > On Sep 29, 2009, at 8:55 AM, Richard Huxton wrote: > >> For the browser, does the following match what you're after, Andrew? >> - clicking chapter title opens the browser panel >> - panel stays open until you click close icon >> - panel contains collapsable tree of chapter/section headings >> Alternatively, could just auto-open the browser panel if javascript is >> enabled and window is wider than N pixels. > > Why wouldn't the entire TOC be in a collapsed list? Permanently on-screen? My only concern there would be for people viewing on phones etc. >> In addition we'll presumably want to meet: >> - no external js libraries (or do we care, if we just reference it from >> google?) > > Save yourself the hassle and just bundle jQuery. That's what I've done > for Pod::Site (module that builds the Bricolage API browser). It's MIT licensed (well MIT+GPL) which is BSD compatible, but I don't know if that's acceptable. It would be easier for me if it could be bundled and presumably make it easier for other contributors in the future too. >> - navigation is optional, disabling js leaves docs as at present > > As long as there's a way to get the nav back from a link on each doc page. > >> - works on all reasonable browsers (anything not IE6) > > +1 (IE6--) > >> - works online and in downloaded docs (except Windows .chm of course) > > That'd be nice, too. Offline is crucial as far as I'm concerned. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Peter Eisentraut wrote: > On Fri, 2009-07-17 at 13:58 +0100, Richard Huxton wrote: >> 2. Titles on navigation links. >> Run ./STYLING/title_links.pl and it should add title attributes to the >> navigation links. This means hovering over the top links gives the title >> of the page they will go to. Presumably we could do this directly from >> the sgml source, and I think it's probably worthwhile. > > I have updated the stylesheet to add a title attribute to the header > links. That has about the same effect as your script. Ah, good. My script was only ever intended to demonstrate. That's one item we can tick off. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Andrew Dunstan wrote: > > > Alvaro Herrera wrote: >> Did this go anywhere? > > Well, it was sorta nice but what Richard sent wasn't really what I want, > at least, which is more along the lines of the menu David Wheeler uses > for the Bricolage API docs. Well, if we nail down the details I'm happy to do the grunt-work. I think #1, #2 (fixed navigation links, titles) are a separate question and just need to be decided upon by those who feel strongly one way or t'other. For the browser, does the following match what you're after, Andrew? - clicking chapter title opens the browser panel - panel stays open until you click close icon - panel contains collapsable tree of chapter/section headings Alternatively, could just auto-open the browser panel if javascript is enabled and window is wider than N pixels. In addition we'll presumably want to meet: - no external js libraries (or do we care, if we just reference it from google?) - navigation is optional, disabling js leaves docs as at present - works on all reasonable browsers (anything not IE6) - works online and in downloaded docs (except Windows .chm of course) -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GRANT ON ALL IN schema
decibel wrote: In this specific case, I think there's enough demand to warrant a built-in mechanism for granting, but if something like exec() is built-in then the bar isn't as high for what the built-in GRANT mechanism needs to handle. CREATE OR REPLACE FUNCTION tools.exec( sql text , echo boolean ) RETURNS text LANGUAGE plpgsql AS $exec$ Perhaps another two functions too: list_all(objtype, schema_pattern, name_pattern) exec_for(objtype, schema_pattern, name_pattern, sql_with_markers) Obviously the third is a simple wrapper around the first two. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] When is a record NULL?
David E. Wheeler wrote:
On Jul 23, 2009, at 9:34 PM, Brendan Jurd wrote:
I guess the spec authors figured they might as well make IS [NOT] NULL
do something useful when applied to a row rather than throwing an
error. I tend to agree.
Frankly, I find the state where a record with a NULL and a not-null
value being neither NULL nor not NULL bizarre.
I'm guessing the justification (and presumably this was worked out based
on the behaviour of one or more of the big DB providers and then
justified afterwards) is that the composite is "partially unknown". Of
course you should either introduce a new code or throw an error, but
four-valued logic isn't going to win you any friends.
If the argument *is* that because you know part of the overall value the
composite isn't null then I'd argue that ('abc' || null) isn't null
either. After all, the first three characters are perfectly well
established.
I hope that provides some clarity.
It's useful to learn that `ROW(NULL, NULL)` is NULL, but I find the
whole thing totally bizarre. Is it me?
Yes, just you. None of the rest of us have any problems with this at all :-)
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions User Design
Peter Eisentraut wrote: > Instead of installing an "extension", that is, say, a collection > of types and functions provided by a third-party source, I would > like to have a mechanism to deploy my own actual database > application code. On the matter of schemas, I suggest that we consider two ideas that have helped RPM in its early days, when everyone had their own very specific ideas about what should be installed where: - file system hierarchy standard - relocations Of course if you have IMPORT from an extension, it's down to the DBA: INSTALL chinese_calendar; IMPORT FROM chinese_calendar SECTION (default) INTO SCHEMA pg_extension; IMPORT FROM chinese_calendar SECTION (year_names) INTO SCHEMA lookups; INSTALL peter_e_app; IMPORT FROM peter_e_app SECTION (all) INTO SCHEMA public; Of course this means two things: 1. Every "extension" has to have its own schema mappings. 2. The application view of the database is a sort of "default extension" Pros: - Namespace collisions begone! - Anything to help extension upgrades could be re-used for applications (and vice-versa) - Some stuff isn't visible outside the extension *at all* - You can separate extension installation from usage (good for multi-user setups). Cons: - Extra layer of indirection (find my namespace => namespace lookup => object) - Extensions need to list what they export in what sections - More code required -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Andrew Dunstan wrote: Peter Eisentraut wrote: This looks very cool, but should probably be implemented via a stylesheet change instead of some Perl parsing some HTML. :-) I'm not sure if this actually addresses Andrew's original concern, though. No, it doesn't. David Wheeler's navigation (see upthread) that he uses for the Bricolage docs does, however. Ah, if you can change the overall layout then the world is your shellfish of choice. Would it be possible to include jquery? It's GPL/MIT dual-licence. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
OK, if you untar the attached in the docs dir there are a three separate sets of changes in it. It all functions, but consider it a discussion point rather than a patch. Presumably we'd need to discuss a patch over on the docs mailing-list. 1. Fixed navigation Copy STYLING/stylesheet.css over the existing one and you will have static navigation links top and bottom of the page. 2. Titles on navigation links. Run ./STYLING/title_links.pl and it should add title attributes to the navigation links. This means hovering over the top links gives the title of the page they will go to. Presumably we could do this directly from the sgml source, and I think it's probably worthwhile. With 1+2 I think there's an argument in favour of removing the bottom navigation - it's only useful if you can't see the top links. 3. Javascript popup menu. This uses jquery, but that's just for convenience during discussion. You could rework this without it. Copy STYLING/*.js and STYLING/menu.inc to the docs dir and then run ./STYLING/include_javascript.pl to include the popup script. The central "chapter heading" section of the top navigation area should now be a link that toggles the menu on/off. The menu could be as simple/complex as you like - this is just what I hacked together by parsing the TOC on index.html I've tested it on Firefox, Opera, IE7 and Safari. Realistically, the only real problem platforms will be IE6 and perhaps iphones. -- Richard Huxton Archonet Ltd STYLING.tgz Description: application/compressed-tar -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] OT: Testing - please ignore
-- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Andrew Dunstan wrote: Richard Huxton wrote: Andrew Dunstan wrote: Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. Are you talking about the online manuals, or something else here? I don't care if we don't provide this for the online manuals on postgresql.org - I'm quite happy to install it on my own server if necessary. But I am talking about the HTML docs that come from our /doc directory. And I bet if we had the option of better navigation, our online users would want us to provide it. Shouldn't be too hard to come up with something reasonable with a little css. Something only activated if javascript is turned on or some such. Give me 48 hours and I'll have a play. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Andrew Dunstan wrote: Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. Are you talking about the online manuals, or something else here? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.5 development schedule
Kevin Grittner wrote: Tom Lane wrote: I think we used to do it more or less like that, but people didn't like it because they couldn't do any long-range planning. Well, obviously the 8.4 release cycle did little to help them. As has already been observed, there is a crying need to say "no" at some point to get a release out. It might actually help to do that on big patches if we don't let too many tiny ones accumulate. I seem to remember the argument being tossed about that "we might as well keep working on this one because there's all these others to wrap up." Have you chaps considered a simple points system? Every patch would need five minutes attention to triage it into one of: small (1 point), medium (2), large (10), huge (50 points - Sync Repl etc). First CF gets (say) 200 points, next 150, next 100, next 75. First-come, first-served - if your patch goes over the limit it goes in the next commit-fest. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions User Design
Peter Eisentraut wrote: Another thing we might want to consider once we have a robust extension mechanism is to move some things out of the backend into extensions. Candidates could be uuid, legacy geometry types, inet/cidr, for example. These extensions would still be available and probably installed by default, but they need not be hardcoded into the backend. Presumably would help the prospective upgrader too. Upgrade tool can't cope with the change to inet types? No problem, I *know* they're not in use, since they're not loaded. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions User Design
David E. Wheeler wrote: On Jun 23, 2009, at 3:02 PM, Dimitri Fontaine wrote: It's "just" PostgreSQL reading an SQL file (foo.install.sql) and parsing each statement etc, so we obviously have the machinery to recognize SQL objects names and schema qualification. Replacing the schema on-the-fly should be a SMOP? (*cough*) Well, no. I might have written a function in PL/Perl. Is PostgreSQL going to parse my Perl function for unqualified function calls? Really? Hell, I don't think that PL/pgSQL is parsed until functions are loaded, either, though I may be wrong about that. Better is to have some magic so that functions in an extension magically have their schema put onto the front of search_path when they're called. Or when they're compiled. Or something. With the given example of extension "foo" depending on "bar" and "baz", I'd suggest: - Default search_path = ext:self, pg_catalog - ext:self = - ext:bar = - ext:baz = You *can't* have anything other than the current package in the search-path in case bar/baz have conflicting objects. I've no idea if ext: makes sense from a parser point of view, but the idea is to map extension name to a schema. If possible, this should work anywhere in PG that a schema can be specified. So - If extension foo is installed in schema1 then ext:foo.fn1() is the same as schema1.fn1() -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multicolumn index corruption on 8.4 beta 2
Floris Bos / Maxnet wrote: I am having the problem that some queries are unable to find rows when using the index. When I force a sequential scan, by doing "set enable_indexscan=false; set enable_bitmapscan=false;", the same queries work fine. Not a hacker myself, but I can tell you that the first question you'll be asked is "can you produce a test case"? If you can generate the problem from a test table+generated data that will let people figure out the problem for you. If not, details of the table schema will be needed, and is there any pattern to the missed rows? Also - compile settings, character set and locale details might be relevant too. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A renewed plea for inclusion of zone.tab
>Surely we'd have seen more complaints, then. > regards, tom lane This gets a definite +1 here as we are using "SET TIMEZONE" at the beginning of each transaction so that each user sees/records dates automatically in whatever timezone they have associated with them. Works beautifully with very little help from the application side. The only downside is finding a way to give the user an appropriate list of timezones to choose from. -- "An eye for eye only ends up making the whole world blind." -- Mohandas Gandhi -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [Snowball-discuss] Snowball release cycle ?
On Wed, Apr 01, 2009 at 05:41:53PM -0400, Andrew Dunstan wrote: > Richard Boulton wrote: >> As I understand it, ASL 2 is incompatible with GPL 2, at least according to >> the FSF. This would be a showstopper problem for me. > > Er, what does Postgres have that is covered by GPL2? I think cross posting has confused this thread - I was talking about snowball not postgres. I don't use postgres at all; in fact, I'm not sure why this thread was copied to pgsql-hackers at all - sorry for the noise if there wasn't a good reason. What I mean is that I use snowball in a project which is GPL-2, and cannot be relicensed, and snowball changing to ASL 2 would be a showstopper problem for me for that reason. -- Richard -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [Snowball-discuss] Snowball release cycle ?
On Wed, Apr 01, 2009 at 05:10:01PM -0400, Grant Ingersoll wrote: > No, it would have to be ASL 2, but that is pretty similar to BSD, no? > (caveat: IANAL) i.e non-viral, free to use however you want, just > don't take credit for it. Everything I've read says the two are > completely compatible As I understand it, ASL 2 is incompatible with GPL 2, at least according to the FSF. This would be a showstopper problem for me. -- Richard -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Service not starting: Error 1053
Magnus Hagander wrote: > Heikki Linnakangas wrote: >> >> Of course, none of this helps if the culprit is a DLL or a 3rd party >> program that allocates the adress space immediately at CreateProcess. > > AFAIK all the cases where we *have* identified the culprit (which has > been antivirus or firewall), this is exactly what it was doing... Would it be possible to build a tool that runs through a series of permission-checks, tries to grab some shared-memory, write to files in the appropriate folders etc. and then shows the name of any process interfering? Half the problem is that whenever someone has Windows-related difficulties there's no standard tools we can use to diagnose. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Service not starting: Error 1053
Frank Featherlight wrote: > Hey guys, > > I had two running threads here: > > http://archives.postgresql.org/pgsql-general/2009-02/msg00859.php > http://www.postgresqlforums.com/forums/viewtopic.php?f=41&t=1574 > > Both have not come to a succesful conclusion. > > In very short (but you better read the threads): I was trying to help Frank out on the -general thread and we've ruled out antivirus etc. (complete uninstall) and my guess is that it's a permission issue. Not enough of a Windows guy to know *which* permission might be causing this though. > FATAL: could not reattach to shared memory (key=1804, addr=0170): 487 -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] Fwd: Need help in porting Oracle PL/SQL's OUT paramater based procedures
Gurjeet Singh wrote: > that is, not passing anything for the OUT or INOUT parameters. This works > fine for a simple SELECT usage, but does not play well when this function is > to be called from another function, (and assuming that it'd break the > application code too, which uses Oracle syntax of calling functions)! > > I have a simple function f() which I'd like to be ported in such a way that > it works when called from other plpgsql code, as well as when the > application uses the Oracle like syntax. Here's a sample usage of the > function f() in Oracle: If you really want Oracle-compatible functions I think there's a company that might sell you a solution :-) However, failing that you'll want an example of OUT parameters in PostgreSQL code - see below. The main thing to remember is that the OUT is really just a shortcut way of defining a record type that gets returned. It's nothing like passing by reference in . BEGIN; CREATE OR REPLACE FUNCTION f1(IN a integer, INOUT b integer, OUT c integer) RETURNS RECORD AS $$ BEGIN c := a + b; b := b + 1; -- No values in RETURN RETURN; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f2() RETURNS boolean AS $$ DECLARE a integer := 1; b integer := 2; c integer := -1; r RECORD; BEGIN r := f1(a, b); -- Original variables unaffected RAISE NOTICE 'a=%, b=%, c=%', a,b,c; -- OUT params are here instead RAISE NOTICE 'r.b=%, r.c=%', r.b, r.c; -- This works, though notice we treat the function as a row-source SELECT (f1(a,b)).* INTO b,c; RAISE NOTICE 'a=%, b=%, c=%', a,b,c; RETURN true; END; $$ LANGUAGE plpgsql; SELECT f2(); ROLLBACK; -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4 release planning
Greg Smith wrote: > Where I suspect this is all is going to settle down into is that if 1) > the SE GUC is on and 2) one of the tables in a join has rows filtered, > then you can expect that a) it's possible that the result will leak > information, which certainly need to be documented, As far as I can tell this is the case however you hide the information. If you implemented it with views you'll have the same issue. If you hide the existence of project p_id="TOPSECRET01" and people can run inserts then they can spot it. Likewise, it you have fkey references to the row then deletions can be used to spot it. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
