Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost
On Sat, Oct 31, 2009 at 6:04 PM, Robert Haas robertmh...@gmail.com wrote: Looking at this a little more, it seems that part of the motivation for the existing design is that user-created AMs might require arbitrary options, which therefore can't be wired into the system catalogs. There's no equivalent for tablespaces (we could add one some day, I suppose), so there's less intrinsic reason to think we have to do it that way. Can't custom modules define arbitrary options which they declare can be defined per tablespace? We could have a column for all booleans, a column for all integers, etc. but that's not really any more normalized than having a single column for all the types with a rule for how to marshal each value type. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per-tablespace random_page_cost/seq_page_cost
On Nov 1, 2009, at 7:43 AM, Greg Stark gsst...@mit.edu wrote: On Sat, Oct 31, 2009 at 6:04 PM, Robert Haas robertmh...@gmail.com wrote: Looking at this a little more, it seems that part of the motivation for the existing design is that user-created AMs might require arbitrary options, which therefore can't be wired into the system catalogs. There's no equivalent for tablespaces (we could add one some day, I suppose), so there's less intrinsic reason to think we have to do it that way. Can't custom modules define arbitrary options which they declare can be defined per tablespace? Yeah, probably we can support that for free, although I'm not sure there is much demand for it. We could have a column for all booleans, a column for all integers, etc. but that's not really any more normalized than having a single - how to marshal each value type. That has no advantages and several disadvantages AFAICS. I don't want to get sidetracked here. The real issue is the one I discussed in the portion of the email you didn't quote... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node
Robert Haas robertmh...@gmail.com writes: On Sat, Oct 31, 2009 at 5:00 PM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: What I've had in mind is pipelining the execution only when it doesn't have *any* impact on the outcome. This would mean only allowing it when the top-level statement is either a SELECT or an INSERT. Also, UPDATEs and DELETEs inside CTEs can't have the same result relations. Whether or not we want to break the expected(?) behaviour for statement-level triggers, I have no opinion to way or another. You'd also have to disallow the case when there are any triggers on the INSERT, or where there are any triggers on anything else (because they might access the target table of the INSERT). This will end up being so restricted as to be useless. Well, it's already the case that BEFORE triggers shouldn't count on seeing any particular subset of a statement's results completed. We could define AFTER triggers as all being fired after the entire statement is complete (which is not the direction my patch was headed in, but I have no allegiance to that). So I think we could define our way out of the trigger timing issue, and I don't see any big objection to limiting pipelining to cases where the sub-statements' target tables don't overlap. However, this still doesn't address the problem of what happens when the top-level select fails to read all of the CTE output (because it has a LIMIT, or the client doesn't read all the output of a portal, etc etc). Partially executing an update in such cases is no good. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node
Robert Haas wrote: You'd also have to disallow the case when there are any triggers on the INSERT, or where there are any triggers on anything else (because they might access the target table of the INSERT). This will end up being so restricted as to be useless. I might be wrong here, but I don't think it matters because those trigger calls would have a different snapshot, right? Or am I missing something? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] tsearch parser inefficiency if text includes urls or emails
Hi,
While playing around/evaluating tsearch I notices that to_tsvector is
obscenely slow for some files. After some profiling I found that this is due
using a seperate TSParser in p_ishost/p_isURLPath in wparser_def.c.
If a multibyte encoding is in use TParserInit copies the whole remaining input
and converts it to wchar_t or pg_wchar - for every email or protocol prefixed
url in the the document. Which obviously is bad.
I solved the issue by having a seperate TParserCopyInit/TParserCopyClose which
reuses the the already converted strings of the original TParser - only at
different offsets.
Another approach would be to get rid of the separate parser invocations -
requiring a bunch of additional states. This seemed more complex to me, so I
wanted to get some feedback first.
Without patch:
andres=# SELECT to_tsvector('english', document) FROM document WHERE filename =
'/usr/share/doc/libdrm-nouveau1/changelog';
─
...
(1 row)
Time: 5835.676 ms
With patch:
andres=# SELECT to_tsvector('english', document) FROM document WHERE filename =
'/usr/share/doc/libdrm-nouveau1/changelog';
─
...
(1 row)
Time: 395.341 ms
Ill cleanup the patch if it seems like a sensible solution...
Is this backpatch-worthy?
Andres
PS: I let the additional define in for the moment so that its easier to see the
performance differences.
diff --git a/src/backend/tsearch/wparser_def.c b/src/backend/tsearch/wparser_def.c
index 301c1eb..14757d9 100644
*** a/src/backend/tsearch/wparser_def.c
--- b/src/backend/tsearch/wparser_def.c
***
*** 24,29
--- 24,30
/* Define me to enable tracing of parser behavior */
/* #define WPARSER_TRACE */
+ #define WPARSER_REUSE
/* Output token categories */
*** TParserInit(char *str, int len)
*** 328,333
--- 329,358
return prs;
}
+ static TParser *
+ TParserCopyInit(const TParser const* orig, size_t offset, int len)
+ {
+ TParser*prs = (TParser *) palloc0(sizeof(TParser));
+
+ prs-charmaxlen = orig-charmaxlen;
+ prs-usewide = orig-usewide;
+ prs-lenstr = len;
+
+ prs-str = orig-str + offset;
+ prs-pgwstr = orig-pgwstr + offset;
+ prs-wstr = orig-wstr + offset;
+
+
+ prs-state = newTParserPosition(NULL);
+ prs-state-state = TPS_Base;
+
+ #ifdef WPARSER_TRACE
+ fprintf(stderr, parsing \%.*s\\n, len, orig-str + offset);
+ #endif
+
+ return prs;
+ }
+
static void
TParserClose(TParser *prs)
{
*** TParserClose(TParser *prs)
*** 345,351
--- 370,388
if (prs-pgwstr)
pfree(prs-pgwstr);
#endif
+ pfree(prs);
+ }
+ static void
+ TParserCopyClose(TParser *prs)
+ {
+ while (prs-state)
+ {
+ TParserPosition *ptr = prs-state-prev;
+
+ pfree(prs-state);
+ prs-state = ptr;
+ }
pfree(prs);
}
*** p_isignore(TParser *prs)
*** 617,623
static int
p_ishost(TParser *prs)
{
! TParser*tmpprs = TParserInit(prs-str + prs-state-posbyte, prs-lenstr - prs-state-posbyte);
int res = 0;
tmpprs-wanthost = true;
--- 654,664
static int
p_ishost(TParser *prs)
{
! #ifdef WPARSER_REUSE
! TParser *tmpprs = TParserCopyInit(prs, prs-state-posbyte, prs-lenstr - prs-state-posbyte);
! #else
! TParser *tmpprs = TParserInit(prs-str + prs-state-posbyte, prs-lenstr - prs-state-posbyte);
! #endif
int res = 0;
tmpprs-wanthost = true;
*** p_ishost(TParser *prs)
*** 631,637
--- 672,682
prs-state-charlen = tmpprs-state-charlen;
res = 1;
}
+ #ifdef WPARSER_REUSE
+ TParserCopyClose(tmpprs);
+ #else
TParserClose(tmpprs);
+ #endif
return res;
}
*** p_ishost(TParser *prs)
*** 639,645
static int
p_isURLPath(TParser *prs)
{
! TParser*tmpprs = TParserInit(prs-str + prs-state-posbyte, prs-lenstr - prs-state-posbyte);
int res = 0;
tmpprs-state = newTParserPosition(tmpprs-state);
--- 684,694
static int
p_isURLPath(TParser *prs)
{
! #ifdef WPARSER_REUSE
! TParser *tmpprs = TParserCopyInit(prs, prs-state-posbyte, prs-lenstr - prs-state-posbyte);
! #else
! TParser *tmpprs = TParserInit(prs-str + prs-state-posbyte, prs-lenstr - prs-state-posbyte);
! #endif
int res = 0;
tmpprs-state = newTParserPosition(tmpprs-state);
*** p_isURLPath(TParser *prs)
*** 654,660
--- 703,713
prs-state-charlen = tmpprs-state-charlen;
res = 1;
}
+ #ifdef WPARSER_REUSE
+ TParserCopyClose(tmpprs);
+ #else
TParserClose(tmpprs);
+ #endif
return res;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To
Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node
Tom Lane wrote: However, this still doesn't address the problem of what happens when the top-level select fails to read all of the CTE output (because it has a LIMIT, or the client doesn't read all the output of a portal, etc etc). Partially executing an update in such cases is no good. I've previously thought about making the CTE aware of the LIMIT, similarly to a top-N sort, but I don't think it's worth it. If we have a LIMIT, we could just fall back to the statement-at-the-time execution. I'm not sure what all cases you mean with the client doesn't read all the output of a portal, but at least for cursors we'd have to first execute ModifyTable nodes. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: I've previously thought about making the CTE aware of the LIMIT, similarly to a top-N sort, but I don't think it's worth it. That approach doesn't lead to a solution because then you could *never* optimize it. The protocol-level limit option is always present. It's conceivable that we could have ExecutorEnd forcibly run any DML CTEs to the end (and fire their triggers?) before shutting down the executor, but it seems like a kluge. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: push AFTER-trigger execution into ModifyTable node
On Nov 1, 2009, at 10:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Oct 31, 2009 at 5:00 PM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: What I've had in mind is pipelining the execution only when it doesn't have *any* impact on the outcome. This would mean only allowing it when the top-level statement is either a SELECT or an INSERT. Also, UPDATEs and DELETEs inside CTEs can't have the same result relations. Whether or not we want to break the expected(?) behaviour for statement- level triggers, I have no opinion to way or another. You'd also have to disallow the case when there are any triggers on the INSERT, or where there are any triggers on anything else (because they might access the target table of the INSERT). This will end up being so restricted as to be useless. Well, it's already the case that BEFORE triggers shouldn't count on seeing any particular subset of a statement's results completed. We could define AFTER triggers as all being fired after the entire statement is complete (which is not the direction my patch was headed in, but I have no allegiance to that). So I think we could define our way out of the trigger timing issue, and I don't see any big objection to limiting pipelining to cases where the sub-statements' target tables don't overlap. Hmm... yeah. If we do that, then pipelining becomes a much more feasible optimization. I think that definition is a bit more likely to result in POLA violations, but I'm not sure by how much. However, this still doesn't address the problem of what happens when the top-level select fails to read all of the CTE output (because it has a LIMIT, or the client doesn't read all the output of a portal, etc etc). Partially executing an update in such cases is no good. Well, like you said elsewhere on this thread, you just have to finish out any remaining bits after the main query completes. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] \du quite ugly in 8.4
Peter Eisentraut wrote: As a compromise, we could keep the combined Attributes column but replace the line breaks by commas. This would at least get rid of the confusing and ugly line breaks in the common cases. That works for me. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] \du quite ugly in 8.4
2009/11/2 Alvaro Herrera alvhe...@commandprompt.com: Peter Eisentraut wrote: As a compromise, we could keep the combined Attributes column but replace the line breaks by commas. This would at least get rid of the confusing and ugly line breaks in the common cases. That works for me. Mmm, perhaps we were a little too hasty about employing the improved multiline support in psql when we implemented this feature. I personally don't see what Peter dislikes about the output ... to me the example he posted is far more readable with linebreaks than it would be without, but the original thread [1] didn't provoke a lot of discussion. I think it's likely that a lot of people just plain missed the thread and are only now seeing the new output as they try out 8.4. I suppose it's inevitable that some of them won't like it. Will fewer people take offense if we change it to commas? Perhaps. It might be worth giving it a try just to see what the reaction is, but I won't be astonished if we get a similar ratio of people annoyed by the change regardless of whether we use commas or linebreaks. Cheers, BJ [1] http://archives.postgresql.org/message-id/9543.1203007...@sss.pgh.pa.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-01 at 10:53 -0800, Jeff Davis wrote: New patch attached. After reading the docs in the patch I don't believe you're going to all this trouble to ensure two circles don't overlap. Can you give some better examples of what you're trying to achieve and why anyone else would care? (I'm busy, so are others). I can probably guess, but my feeling is I shouldn't have to. I feel like this might be a truly great feature, but I'm worried that either it isn't at all or it is and yet will be overlooked. Does this project link in with other planned developments in various plugins? The current patch writes the syntax like this EXCLUSION USING gist (c CHECK WITH ) makes it look like a table constraint, yet it clearly refers to a single column. That looks very clumsy to read, to my eyes. The syntax be easier to read if it was stated as a comparison e.g. in the circle example CHECK ( NOT (NEW.c c)) USING GIST where NEW is the incoming row. This is similar to the way I would write the constraint if I wanted to ensure the values in two columns did not match/overlap etc CHECK ( NOT (col1 col2)) and is also not such a radical departure from existing SQL Standard syntax. We only need the NOT when there isn't a clear negator defined, so in most cases I would hope to read something like this CHECK (NEW.pkey != pkey) USING btree which should be equivalent to the UNIQUE constraint I don't think its too late to change the syntax. -- Simon Riggs www.2ndQuadrant.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] operator exclusion constraints
After reading the docs in the patch I don't believe you're going to all this trouble to ensure two circles don't overlap. Can you give some better examples of what you're trying to achieve and why anyone else would care? (I'm busy, so are others). Non overlapping time intervals is one use case - think about room scheduling. I personally want to use it to ensure the consistency of genomic annotations. -Nathan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
Simon Riggs si...@2ndquadrant.com writes: The syntax be easier to read if it was stated as a comparison e.g. in the circle example CHECK ( NOT (NEW.c c)) USING GIST I don't think this is a good idea at all. NEW is a nonstandard Postgres-ism, and introducing it into this syntax doesn't seem very future-proof to me. What's more, the above is not in the least analogous to a regular CHECK constraint, because there's some implicit notion of c ranging over all other rows, which is not what is meant by the same column reference in a CHECK constraint. I agree that the proposed syntax is a bit awkward, but this isn't better. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-01 at 22:42 +, Simon Riggs wrote: After reading the docs in the patch I don't believe you're going to all this trouble to ensure two circles don't overlap. Can you give some better examples of what you're trying to achieve and why anyone else would care? (I'm busy, so are others). Non-overlapping periods of time. I couldn't document that, because the PERIOD type doesn't exist in core (yet). I can probably guess, but my feeling is I shouldn't have to. I feel like this might be a truly great feature, but I'm worried that either it isn't at all or it is and yet will be overlooked. Does this project link in with other planned developments in various plugins? Absolutely: http://archives.postgresql.org/pgsql-hackers/2009-10/msg01813.php The current patch writes the syntax like this EXCLUSION USING gist (c CHECK WITH ) makes it look like a table constraint, yet it clearly refers to a single column. That looks very clumsy to read, to my eyes. It is a table constraint, and you can specify multiple columns. I don't see much point in allowing this as a column constraint, because that's not the typical case. Most of the time, there will be two columns like: EXCLUSION(room_number CHECK WITH =, during CHECK WITH ) In other words, usually there is both a resource and a period of time for the reservation. It is of course possible to use it for a column constraint, and I'll add syntax if there's demand for it. The syntax be easier to read if it was stated as a comparison e.g. in the circle example CHECK ( NOT (NEW.c c)) USING GIST where NEW is the incoming row. This is similar to the way I would write the constraint if I wanted to ensure the values in two columns did not match/overlap etc CHECK ( NOT (col1 col2)) and is also not such a radical departure from existing SQL Standard syntax. We've already had very extensive discussion about the syntax. Your idea is interesting, but I agree with Tom that it's not ideal, either. NEW might be OK, but Tom's observation about the new meaning of c (ranging over the entire table) is a compelling problem. Consider: CHECK ( NOT (NEW.c c OR c d)) The right side of the OR could either mean c overlaps d or forall c, d: c overlaps d. I can't come up with a way to treat c consistently between the left and right side of the OR (put another way, is c free or bound?). We could allow subselects in CHECK, but it's difficult to infer from arbitrary queries what I can enforce with an operator exclusion constraint, and what I can't. If you want to re-open the syntax discussion, we can (right is better than soon). However, it is late in the cycle, so I'll need something very clear quite soon if this is going to make it into 8.5. Personally I think the current syntax is pretty good. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] proposal: operator exclusion constraints with cardinality
I have completed the open issues (that I'm aware of) for operator exclusion constraints: http://archives.postgresql.org/message-id/1257101600.27737.159.ca...@jdavis I'd now like to propose an extension to that feature: cardinality limits. It could go something like this (syntax still open for discussion, for illustration only): EXCLUSION (room CHECK WITH =, attendee CHECK WITH =, during CHECK WITH ) CARDINALITY 30 To mean that at most 30 attendees can be signed up for the same room at overlapping times. I have hacked up a basic prototype just to make sure the semantics can work reasonably well. I haven't implemented the syntax or catalog changes yet, but the basic semantics with a hard-coded cardinality seem to hold up. Thoughts? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
Devrim GNDZ dev...@gunduz.org wrote: Is this the same as / similar to Oracle's syntax? Yes. IIRC Nikhil's patch was Oracle's syntax No. See: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2125922 Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent 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: operator exclusion constraints with cardinality
Jeff Davis pg...@j-davis.com writes: It could go something like this (syntax still open for discussion, for illustration only): EXCLUSION (room CHECK WITH =, attendee CHECK WITH =, during CHECK WITH ) CARDINALITY 30 There's an old design principle that says the only good numbers in computer science are 0, 1, and N -- that is, if you need to allow more than one of something, you should have no hard-wired upper bound on how many of them you allow. The reason that meme comes to mind is that I'm having difficulty seeing applications for this type of constraint. I can certainly believe that people might like to enforce constraints like no more than N people are signed up for class X. The problem is that they won't want the *same* N for every class, and that's what this constraint design seems to require. What they'll want is N drawn from some other table entry about the size of the classroom. If you can't support that then the design isn't fully baked yet. (Of course, the reason UNIQUE constraints are good is that they correspond to the number 1 ;-)) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: operator exclusion constraints with cardinality
On Sun, Nov 1, 2009 at 10:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeff Davis pg...@j-davis.com writes: It could go something like this (syntax still open for discussion, for illustration only): EXCLUSION (room CHECK WITH =, attendee CHECK WITH =, during CHECK WITH ) CARDINALITY 30 There's an old design principle that says the only good numbers in computer science are 0, 1, and N -- that is, if you need to allow more than one of something, you should have no hard-wired upper bound on how many of them you allow. The reason that meme comes to mind is that I'm having difficulty seeing applications for this type of constraint. I can certainly believe that people might like to enforce constraints like no more than N people are signed up for class X. The problem is that they won't want the *same* N for every class, and that's what this constraint design seems to require. What they'll want is N drawn from some other table entry about the size of the classroom. If you can't support that then the design isn't fully baked yet. (Of course, the reason UNIQUE constraints are good is that they correspond to the number 1 ;-)) Following that thought, in this particular case it seems like you could do: EXCLUSION (room CHECK WITH =, attendee CHECK WITH =, foobar CHECK WITH =, during CHECK WITH ) and then also CHECK (foobar = 1 AND foobar = 30) I'm a bit baffled by what this constraint is trying to represent, actually. I would have thought that the example would be room and during constrained to a quantity of 30, and the solution would be to have attendee. Since you already have attendee as part of the constraint, I'm a little mystified as to what the quantity of 30 is supposed to represent, but it any case it seems like you can get the effect with an extra field - which also allows you to do things like variable room-sizes (by setting up triggers that arrange to copy the room size into a column of your scheduling table so that you can then check that the attendee number is less than the room size). ...Robert -- Sent 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: operator exclusion constraints with cardinality
On Sun, 2009-11-01 at 22:49 -0500, Tom Lane wrote: you should have no hard-wired upper bound on how many of them you allow. You're right. I saw something that looked easy to implement, but in practice it wouldn't be very useful. Regards, Jeff Davis -- Sent 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: operator exclusion constraints with cardinality
On Sun, 2009-11-01 at 23:09 -0500, Robert Haas wrote: Following that thought, in this particular case it seems like you could do: EXCLUSION (room CHECK WITH =, attendee CHECK WITH =, foobar CHECK WITH =, during CHECK WITH ) and then also CHECK (foobar = 1 AND foobar = 30) ... Since you already have attendee as part of the constraint, I'm a little mystified as to what the quantity of 30 is supposed to represent, Yes, that was wrong, attendee shouldn't have been in the constraint. but it any case it seems like you can get the effect with an extra field That's one way to do it, but then you have to assign numbers to all attendees, which creates a new contention problem. If using discrete time slots aligned to the hour, for instance, you could increment a field in the room table every time an attendee was added, and then use a CHECK constraint to limit that field. The fact that updates follow the chain of concurrent updates makes that work nicely. That doesn't seem to work for general overlapping and unaligned time periods, however. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Syntax for partitioning
Hi, Is this the same as / similar to Oracle's syntax? Yes. IIRC Nikhil's patch was Oracle's syntax No. See: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#i2125922 Any decent prevalent partitioning syntax should be ok IMHO. IIRC, MySQL paritioning syntax is also pretty similar to Oracle's. Regards, Nikhils -- http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] backup_label in a crash recovery
Hi, When a crash occurs before calling pg_stop_backup(), the subsequent crash recovery causes the FATAL error and outputs the following HINT message. If you are not restoring from a backup, try removing the file \%s/backup_label\. I wonder why backup_label isn't automatically removed in normal crash recovery case. Is this for the fail-safe protection; prevent admin from restoring from a backup wrongly without creating recovery.conf? Or another? If that's intentional, a clusterware for shared disk failover system should remove backup_label whenever doing failover. Otherwise, when a crash occurs during online-backup, the failover would fail. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Remove backup_label automatically
Hi, Is there a reason not to remove backup_label automatically when crash recovery is run? ereport in StartXLOG() says the folloing hint message, but I can't see other cases we have to take care; ereport(PANIC, (errmsg(could not locate required checkpoint record), errhint(If you are not restoring from a backup, try removing the fi le \%s/backup_label\., DataDir))); regards, -- Toru SHIMOGAKIshimogaki.t...@oss.ntt.co.jp NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] operator exclusion constraints
On Sun, 2009-11-01 at 15:42 -0800, Jeff Davis wrote: Most of the time, there will be two columns like: EXCLUSION(room_number CHECK WITH =, during CHECK WITH ) Now that's a great example. Looks like the classic don't allow the same room to be booked more than once at the same time. It bothers me that we would have completely separate syntax for this feature as opposed to normal SQL. It also doesn't make it easy to interpret from the business statement to the implementation. Notice that the , above means AND. How would we use an OR conditional? How would we express the wish to use a partial index? How would I express a bidding rule: Only allow bids that are better than the highest bid so far EXCLUSION (item CHECK WITH =, bid_price CHECK WITH ) Did I get the the right way around? How would I specify a tree that has only 2 down branches at any node, 'left' and 'right'? If you want to re-open the syntax discussion, we can I don't *want* to and I don't want to derail a good feature. But we'll be looking at this for years and believe me if you introduce even a minor bump you'll hear it repeated endlessly. Personally I think the current syntax is pretty good. The feature sounds great, regrettably the syntax doesn't seem very clean, as an objective observer. I apologise if this causes you trouble, I have no axe to grind here. Not sure that if we submitted this to SQL Standard committee that it would be accepted as is. (You made a few other points which I regrettably skimmed over in my reply). -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for automated partitioning
shrish purohit shrishpuro...@gmail.com wrote: Updated Kedar's patch sending on behalf of Kedar. There are not toomany modifications. It includes improvements for validating multi-column partitioned tables. Thanks, but I'd like to keep the first patch as possible as simple. I'll refer your work in the *next* step, especially partition triggers and ALTER commands. I glanced over your patch and find some codes that have room for improvement: * Need to avoid RANGE and PARTITION to be reserved keywords. * Non SQL-based operations are preferred if possible, so need to reconsider pg_parse_query(). * Adding NOTICE message to completionTag is a very bad idea. we don't need such feature because users can create trigger on the overflow partition to send those messages. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent 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 TABLE ... INHERITS (parent INCLUDING xxx)
Tom Lane t...@sss.pgh.pa.us wrote: Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Should we have INHERITS ( parent INCLUDING xxx ) syntax ? No. That would imply that the user has some control over what is inherited and what isn't, which would be a complete mess. Hmmm, but users can already control using LIKE INCLUDING clause what is inherited and what isn't. Also, we don't inherit anything from INHERITS clause in default. We have nothing to lose even if we support INHERITS + INCLUDING, no? And there are certain merits; we can avoid unexpected log messages and don't have to repeat the parent name in DDL. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
