Re: [HACKERS] SSI modularity questions
On 27.06.2011 21:23, Kevin Grittner wrote: There are two outstanding patches for SSI which involve questions about modularity. In particular, they involve calls to predicate locking and conflict detection from executor source files rather than AM source files (where most such calls exist). (1) Dan submitted this patch: http://archives.postgresql.org/message-id/20110622045850.gn83...@csail.mit.edu which is a very safe and very simple patch to improve performance on sequential heap scans at the serializable transaction isolation level. The location of the code being modified raised questions about modularity. There is a reasonably clear place to which it could be moved in the heap AM, but because it would acquire a predicate lock during node setup, it would get a lock on the heap even if the node was never used, which could be a performance regression in some cases. The bigger question is if those calls are needed at all (http://archives.postgresql.org/message-id/4e072ea9.3030...@enterprisedb.com). I'm uneasy about changing them this late in the release cycle, but I don't feel good about leaving useless clutter in place just because we're late in the release cycle either. More importantly, if locking the whole relation in a seqscan is not just a performance optimization, but is actually required for correctness, it's important that we make the code and comments to reflect that or someone will break it in the future. (2) In reviewing the above, Heikki noticed that there was a second place in the executor that SSI calls were needed but missing. I submitted a patch here: http://archives.postgresql.org/message-id/4e07550f02250003e...@gw.wicourts.gov I wonder, though, whether the section of code which I needed to modify should be moved to a new function in heapam.c on modularity grounds. If these two places were moved, there would be no SSI calls from any source file in the executor subdirectory. Same here, we might not need those PredicateLockTuple calls in bitmap heap scan at all. Can you check my logic, and verify if those PredicateLockTuple() calls are needed? -- 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 base backup from the hot-standby
> Considering everything that has been discussed on this thread so far.
>
> Do you still think your patch is the best way to accomplish base backups
> from standby servers?
> If not what changes do you think should be made?
I reconsider the way to not use pg_stop_backup().
Process of online base backup on standby server:
1. pg_start_backup('x');
2. copy the data directory
3. copy *pg_control*
Behavior while restore:
* read "Minimum recovery ending location" of the copied pg_control.
* use the value with the same purposes as the end-of-backup location.
-> When the value is equal to 0/0, this behavior do not do.
This situation is to acquire backup from master server.
Jun Ishizuka
NTT Software Corporation
TEL：045-317-7018
E-Mail: ishizuka@po.ntts.co.jp
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] how to call the function--pqCatenateResultError()
Hello!~
Now i encounter a function call problem in PostgreSQL's psql module!
The situation is as follow:
In ./src/bin/psql/common.c, I want to call the function
pqCatenateResultError().
Function pqCatenateResultError() is declared in
./src/interfaces/libpq/libpq-init.h
extern void pqCatenateResultError(PGresult
*res, const char *msg);
and is defined in ./src/interfaces/libpq/fe-exec.c
void
pqCatenateResultError(PGresult *res, const char
*msg)
{
PQExpBufferData errorBuf;
if (!res || !msg)
return;
initPQExpBuffer(&errorBuf);
if (res->errMsg)
appendPQExpBufferStr(&errorBuf, res->errMsg);
appendPQExpBufferStr(&errorBuf, msg);
pqSetResultError(res, errorBuf.data);
termPQExpBuffer(&errorBuf);
}
To call this function in ./common.c, I include 'libpq-init.h' in
./src/bin/psql/common.h .
As ./common.c include the header file 'common.h'.
But when I use pqCatenateResultError() in ./common.c, It appears
"undefined reference to pqCatenateResultError()" first.
So I include 'extern void pqCatenateResultError(PGresult *res, const char
*msg);' at the begining of './common.c' .
But this change make no difference to the result.
I do not know why this happened! Someone hlep me! Thank you.
There is another situation similar to the situation above:
Function PQexec() is declared in ./src/interfaces/libpq/libpq-fe.h and
defined in ./src/interfaces/libpq/fe-exec.c
extern PGresult *PQexec(PGconn *conn, const char
*query);
I can call this function with no error happening!
These two situation puzzled me!~
From: Stone
Re: [HACKERS] [Hackers]Backend quey plan process
HuangQi writes: > Hi, > I've been tracing the data structure in the query plan process for a > while. But then I found the data structure manipulation is really so > confusing. > Could some guy tell me where could I find any guide on how to figure out the > process and data structure usage? Is there any good resource helping us read > the code? maybe you can read: http://www.pgcon.org/2011/schedule/events/350.en.html, it's still a mistery for me but this seems more clear than read the code... after read this, read the README's in the code... read the code :) > BTW, which email system are you using to send to postgres mailing list? > As you can keep the top-answering and maintain the title of your email with > [hackers] in front, my gmail can not help on that. For this email, I just add > by hand. > Thank you a lot for your help. > i used gmail until a couple of weeks ago and never had problems... (well i'm still using it at least as a mail server, i just changed the interface a access from) -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL Soporte 24x7, desarrollo, capacitación y servicios -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [Hackers]Backend quey plan process
Hi, I've been tracing the data structure in the query plan process for a while. But then I found the data structure manipulation is really so confusing. Could some guy tell me where could I find any guide on how to figure out the process and data structure usage? Is there any good resource helping us read the code? BTW, which email system are you using to send to postgres mailing list? As you can keep the top-answering and maintain the title of your email with [hackers] in front, my gmail can not help on that. For this email, I just add by hand. Thank you a lot for your help. -- Best Regards Huang Qi Victor
Re: [HACKERS] Range Types, constructors, and the type system
On Mon, 2011-06-27 at 14:50 -0400, Robert Haas wrote: > Couldn't we also do neither of these things? I mean, presumably > '[1,10]'::int8range had better work. I think that if we combine this idea with Florian's "PAIR" suggestion here: http://archives.postgresql.org/message-id/ad4fc75d-db99-48ed-9082-52ee3a4d7...@phlo.org then I think we have a solution. If we add a type RANGEINPUT that is not a pseudotype, we can use that as an intermediate type that is returned by range constructors. Then, we add casts from RANGEINPUT to each range type. That would allow range(1,2)::int8range to work without changing the type system around, because range() would have the signature: range(ANYELEMENT, ANYELEMENT) -> RANGEINPUT and then the cast would change it into an int8range. But we only need the one cast per range type, and we can also support all of the other kinds of constructors like: range_cc(ANYELEMENT, ANYELEMENT) -> RANGEINPUT range_linf_c(ANYELEMENT) -> RANGEINPUT without additional hassle. The RANGEINPUT type itself would hold similar information to actual range types: the subtype OID (instead of the range type, because it's not a range yet), optionally the two bounds (depending on the flags), and the flags byte. The cast to a real range type would read the subtype, and try to coerce the bounds to the subtype of the range you're casting to, set the range type oid, leave the flags byte the same, and it's done. So, in effect, RANGEINPUT is a special type used only for range constructors. If someone tried to output it, it would throw an exception, and we'd even have enough information at that point to print a nice error message with a hint. Actually, this is pretty much exactly Florian's idea (thanks again, Florian), but at the time I didn't like it because "pair" didn't capture everything that I wanted to capture, like infinite bounds, etc. But there's no reason that it can't, and your point made me realize that -- you are effectively just using TEXT as the intermediate type (which works, but has some undesirable characteristics). Do we think that this is a good way forward? The only thing I can think of that's undesirable is that it's not normal to be required to cast the result of a function, and might be slightly difficult to explain in the documentation in a straightforward way. 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] Avoid index rebuilds for no-rewrite ALTER TABLE ALTER TYPE
On Mon, Jun 27, 2011 at 03:45:43PM -0400, Robert Haas wrote: > On Wed, Jun 15, 2011 at 1:03 AM, Noah Misch wrote: > > [patch to avoid index rebuilds] > > With respect to the documentation hunks, it seems to me that the first > hunk might be made clearer by leaving the paragraph of which it is a > part as-is, and adding another paragraph afterwards beginning with the > words "In addition". The added restriction elaborates on the transitivity requirement, so I wanted to keep the new language adjacent to that. > I am not sure whether the second hunk is > necessary at all. Doesn't the existing language cover the same > territory as what you've added? The first hunk updates the contract for btree families, and the second updates the contract for hash families. I kept the second instance a bit terse since it follows soon after the similar text for B-tree. > I think that the variables in ATPostAlterTypeCleanup() could be better > named. They appear to be values, when in fact they are ListCells. > Honestly I'd probably just use l1 and l2, but if you want to insist on > some more mnemonic naming it should probably be something that sounds > vaguely list-ish. Okay; I'll do that in the next version. Either l1/l2 or maybe oid_item/def_item like we use in postgres.c. > As you no doubt expected, my eyes was immediately drawn to the > index-resurrection hack. Reviewing the thread, I see that you asked > about that in January and never got feedback. I have to say that what > you've done here looks like a pretty vile hack, but it's hard to say > for sure without knowing what to compare it against. You made > reference to this being smaller and simpler than updating the index > definition in place - can you give a sketch of what would need to be > done if we went that route instead? In "at7-index-opfamily.patch" attached to http://archives.postgresql.org/message-id/20110113230124.ga18...@tornado.gateway.2wire.net check out the code following the comment "/* The old index is compatible. Update catalogs. */" until the end of the function. That code would need updates for per-column collations, and it incorrectly reuses values/nulls/replace arrays. It probably does not belong in tablecmds.c, either. However, it gives the right general outline. It would be valuable to avoid introducing a second chunk of code that knows everything about the catalog entries behind an index. That's what led me to the put forward the most recent version as best. What do you find vile about that approach? I wasn't comfortable with it at first, because I suspected the checks in RelationPreserveStorage() might be important for correctness. Having studied it some more, though, I think they just reflect the narrower needs of its current sole user. Thanks, nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] minor patch submission: CREATE CAST ... AS EXPLICIT
On 18 June 2011 09:49, Brendan Jurd wrote: > Hi Fabien, > > I'm taking a look at this patch for the commitfest. On first reading > of the patch, it looked pretty sensible to me, but I had some trouble > applying it to HEAD: > > error: patch failed: doc/src/sgml/ref/create_cast.sgml:20 > error: doc/src/sgml/ref/create_cast.sgml: patch does not apply > error: patch failed: src/backend/parser/gram.y:499 > error: src/backend/parser/gram.y: patch does not apply > error: patch failed: src/include/parser/kwlist.h:148 > error: src/include/parser/kwlist.h: patch does not apply > error: patch failed: src/test/regress/expected/create_cast.out:27 > error: src/test/regress/expected/create_cast.out: patch does not apply > error: patch failed: src/test/regress/sql/create_cast.sql:27 > error: src/test/regress/sql/create_cast.sql: patch does not apply > > Perhaps the patch could use a refresh? The author has yet to reply to the above -- we are still lacking a patch version that applies cleanly to HEAD. I have marked this patch 'Waiting on Author'. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address
On Mon, Jun 27, 2011 at 4:40 PM, Kohei KaiGai wrote: > The attached patch is rebased one towards the latest tree, using > relation_openrv_extended(). Committed. > Although it is not a matter in this patch itself, I found a problem on > the upcoming patch > that consolidate routines associated with DropStmt. > Existing RemoveRelations() acquires a lock on the table owning an > index to be removed > in the case when OBJECT_INDEX is supplied. > However, the revised get_object_address() opens the supplied relation > (= index) in same > time with lookup of its name. So, we may break down the > relation_openrv_extended() > into a pair of RangeVarGetRelid() and relation_open(). Not without looking at the patch. I will respond on that thread when I've read through it more thoroughly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per-column generic option
On Fri, Jun 17, 2011 at 05:59:31AM -0700, David Fetter wrote: > On Fri, Jun 17, 2011 at 07:19:39PM +0900, Shigeru Hanada wrote: > > > Here's an example of a non-trivial mapping. > > > > > > Database type: > > > MySQL > > > Foreign data type: > > > datetime > > > PostgreSQL data type: > > > timestamptz > > > Transformation direction: > > > Import > > > Transformation: > > > CASE > > > WHEN DATA = '-00-00 00:00:00' > > > THEN NULL > > > ELSE DATA > > > END > > > > > > Here, I'm making the simplifying assumption that there is a bijective > > > mapping between data types. Any word on this? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] add support for logging current role (what to review?)
Ive been holding off because its marked as Waiting on Author, am now thinking thats a mistake. =) It links to this patch: http://archives.postgresql.org/message-id/20110215135131.gx4...@tamriel.snowman.net Which is older than the latest patch in that thread posted by Robert: http://archives.postgresql.org/message-id/AANLkTikMadttguOWTkKLtgfe90kxR=u9njk9zebrw...@mail.gmail.com (There are also various other patches and versions in that thread...) The main difference between the first and the last patch is the first one has support for changing what csv columns we output, while the latter just tacks on an additional column. The thread was very long and left me a bit confused as to what I should actually be looking at. Or perhaps thats the point-- we need to decide if a csvlog_fields GUC is worth it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
On mån, 2011-06-27 at 14:34 -0400, Bruce Momjian wrote: > Bruce Momjian wrote: > > Robert Haas wrote: > > > It's easier to read the patches if you do separate changes in separate > > > patches. Anyway, I'm a bit nervous about this hunk: > > > > > > + if (old_cluster.port == DEF_PGUPORT) > > > + pg_log(PG_FATAL, "When checking a live old > > > server, " > > > +"you must specify the old server's > > > port number.\n"); > > > > > > Is the implication here that I'm now going to need to specify more > > > than 4 command-line options/environment variables for this to work? > > > > Yes, we don't inherit PGPORT anymore. Doing anything else was too > > complex to explain in the docs. > > But only if you are running --check on a live server. Otherwise, we > will just default to 50432 instead of 5432/PGPORT. "When checking a live server, the built-in default port number or the value of the environment variable PGPORT is used. But when performing an upgrade, a different port number is used by default, namely 50432, which can be overridden XXX [how?]" Seems pretty clear to me, as long as that last bit is figured out. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Dry Run mode for pg_archivecleanup
Hi guys,
I have added the '-n' option to pg_archivecleanup which performs a
dry-run and outputs the names of the files to be removed to stdout
(making possible to pass the list via pipe to another process). Please
find attached the small patch.
Thanks,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
>From 25fcf05ff787dae2b0d62de423a363f8597f1d42 Mon Sep 17 00:00:00 2001
From: Gabriele Bartolini
Date: Mon, 27 Jun 2011 12:27:09 +0200
Subject: [PATCH] Add dryrun option to pg_archivecleanup
---
contrib/pg_archivecleanup/pg_archivecleanup.c | 13 -
doc/src/sgml/pgarchivecleanup.sgml|9 +
2 files changed, 21 insertions(+), 1 deletions(-)
diff --git a/contrib/pg_archivecleanup/pg_archivecleanup.c
b/contrib/pg_archivecleanup/pg_archivecleanup.c
index dd8a451..0bbec92 100644
--- a/contrib/pg_archivecleanup/pg_archivecleanup.c
+++ b/contrib/pg_archivecleanup/pg_archivecleanup.c
@@ -36,6 +36,7 @@ const char *progname;
/* Options and defaults */
bool debug = false; /* are we debugging? */
+bool dryrun = false; /* are we performing a dry-run
operation? */
char *archiveLocation;/* where to find the archive? */
char *restartWALFileName; /* the file from which we can restart restore */
@@ -123,6 +124,12 @@ CleanupPriorWALFiles(void)
fprintf(stderr, "%s: removing file
\"%s\"\n",
progname, WALFilePath);
+ if (dryrun) {
+ /* Prints the name of the file and
+* skips the actual removal of the file
*/
+ fprintf(stdout, "%s\n", WALFilePath);
+ continue;
+ }
rc = unlink(WALFilePath);
if (rc != 0)
{
@@ -205,6 +212,7 @@ usage(void)
printf(" %s [OPTION]... ARCHIVELOCATION OLDESTKEPTWALFILE\n",
progname);
printf("\nOptions:\n");
printf(" -d generates debug output (verbose mode)\n");
+ printf(" -n shows the names of the files that would
have been removed (dry-run)\n");
printf(" --help show this help, then exit\n");
printf(" --version output version information, then exit\n");
printf("\n"
@@ -241,13 +249,16 @@ main(int argc, char **argv)
}
}
- while ((c = getopt(argc, argv, "d")) != -1)
+ while ((c = getopt(argc, argv, "dn")) != -1)
{
switch (c)
{
case 'd': /* Debug mode */
debug = true;
break;
+ case 'n': /* Dry-Run mode */
+ dryrun = true;
+ break;
default:
fprintf(stderr, "Try \"%s --help\" for more
information.\n", progname);
exit(2);
diff --git a/doc/src/sgml/pgarchivecleanup.sgml
b/doc/src/sgml/pgarchivecleanup.sgml
index ddffa32..8148c53 100644
--- a/doc/src/sgml/pgarchivecleanup.sgml
+++ b/doc/src/sgml/pgarchivecleanup.sgml
@@ -98,6 +98,15 @@ pg_archivecleanup: removing file
"archive/00010037000E"
+
+ -n
+
+
+Print the names of the files that would have been removed on
stdout (performs a dry run).
+
+
+
+
--
1.7.4.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address
The attached patch is rebased one towards the latest tree, using relation_openrv_extended(). Although it is not a matter in this patch itself, I found a problem on the upcoming patch that consolidate routines associated with DropStmt. Existing RemoveRelations() acquires a lock on the table owning an index to be removed in the case when OBJECT_INDEX is supplied. However, the revised get_object_address() opens the supplied relation (= index) in same time with lookup of its name. So, we may break down the relation_openrv_extended() into a pair of RangeVarGetRelid() and relation_open(). Any good idea? 2011/6/27 Robert Haas : > On Mon, Jun 27, 2011 at 2:59 PM, Noah Misch wrote: >> On Mon, Jun 27, 2011 at 01:28:30PM -0400, Robert Haas wrote: >>> On Wed, Jun 22, 2011 at 1:36 PM, Robert Haas wrote: >>> > I agree with you. ?If we had a whole pile of options it might be worth >>> > having heap_openrv() and heap_openrv_extended() so as not to >>> > complicate the simple case, but since there's no forseeable need to >>> > add anything other than missing_ok, my gut is to just add it and call >>> > it good. >>> >>> On further review, my gut is having second thoughts. This patch is an >>> awful lot smaller and easier to verify correctness if I just mess with >>> the "try" calls and not the regular ones; and it avoids both >>> back-patching hazards for us and hoops for third-party loadable >>> modules that are using the non-try versions of those functions to jump >>> through. >> >> +1. (Note that the function header comments need a few more updates.) > > Oh, good catch, thanks. Committed with some further comment changes. > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- KaiGai Kohei pgsql-v9.2-drop-reworks-part-0.v5.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
On Jun 27, 2011, at 12:36 PM, Christopher Browne wrote: > I wrote something on this on pgsql-general about 5 years ago that > still seems pretty relevant. > > http://archives.postgresql.org/pgsql-general/2006-02/msg00159.php iwantsandy.com (now defunct) originally had a solution like this. However it supported a slew of recurrences: * hours * 2xday * days * weeks * months * quarters * years * decades We had materializations of all of these going out 5 years or so. It took up an incredible amount of database space and was really slow. I replaced it with a variation on the code described in this blog post: http://www.justatheory.com/computers/databases/postgresql/recurring_events.html The database was a fraction of the original size and, because views were usually limited to a month at most, the number of rows generated for a query to show recurring events was quite limited (no one had an hourly reminder that when for more than a couple of days). Queries were a lot faster, too. So I think the materialization of dates can work in certain limited cases such as your "vacations 2005" example, and will be easier to use thanks to JOINs, I found that it performed poorly and was unnecessarily resource-intensive for our usage. And I suspect the same would be try for anyone building a calendar app with more than one simple kind of limited recurrence. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address
On Mon, Jun 27, 2011 at 2:59 PM, Noah Misch wrote: > On Mon, Jun 27, 2011 at 01:28:30PM -0400, Robert Haas wrote: >> On Wed, Jun 22, 2011 at 1:36 PM, Robert Haas wrote: >> > I agree with you. ?If we had a whole pile of options it might be worth >> > having heap_openrv() and heap_openrv_extended() so as not to >> > complicate the simple case, but since there's no forseeable need to >> > add anything other than missing_ok, my gut is to just add it and call >> > it good. >> >> On further review, my gut is having second thoughts. This patch is an >> awful lot smaller and easier to verify correctness if I just mess with >> the "try" calls and not the regular ones; and it avoids both >> back-patching hazards for us and hoops for third-party loadable >> modules that are using the non-try versions of those functions to jump >> through. > > +1. (Note that the function header comments need a few more updates.) Oh, good catch, thanks. Committed with some further comment changes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Avoid index rebuilds for no-rewrite ALTER TABLE ALTER TYPE
On Wed, Jun 15, 2011 at 1:03 AM, Noah Misch wrote: > [patch to avoid index rebuilds] With respect to the documentation hunks, it seems to me that the first hunk might be made clearer by leaving the paragraph of which it is a part as-is, and adding another paragraph afterwards beginning with the words "In addition". I am not sure whether the second hunk is necessary at all. Doesn't the existing language cover the same territory as what you've added? I think that the variables in ATPostAlterTypeCleanup() could be better named. They appear to be values, when in fact they are ListCells. Honestly I'd probably just use l1 and l2, but if you want to insist on some more mnemonic naming it should probably be something that sounds vaguely list-ish. As you no doubt expected, my eyes was immediately drawn to the index-resurrection hack. Reviewing the thread, I see that you asked about that in January and never got feedback. I have to say that what you've done here looks like a pretty vile hack, but it's hard to say for sure without knowing what to compare it against. You made reference to this being smaller and simpler than updating the index definition in place - can you give a sketch of what would need to be done if we went that route instead? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
On Mon, Jun 27, 2011 at 2:36 PM, Steve Crawford
wrote:
> On 06/27/2011 10:56 AM, David E. Wheeler wrote:
>>
>> On Jun 27, 2011, at 10:54 AM, Steve Crawford wrote:
>>
>>> That's just how intervals that represent varying periods of time work.
>>> You would need to write your own. But a series of end-of-month dates is
>>> pretty easy:
>>> select generate_series('2011-06-01'::timestamp , '2012-04-01'::timestamp,
>>> '1 month') - '1 day'::interval;
>>
>> Yeah, but it's trickier if you have a calendaring app and don't know that
>> date a user has chosen for a monthly recurring event. They might have
>> selected June 30, in which case only February would ever need to be
>> different than the default.
>>
>> Best,
>>
>> David
>>
>>
>>
> The query is marginally trickier. But the better calendaring apps give a
> variety of options when selecting "repeat": A user who selects June 30, 2011
> and wants a monthly repeat might want:
>
> 30th of every month - skip months without a 30th
> 30th of every month - move to end-of-month if 30th doesn't exist
> Last day of every month
> Last Thursday of every month
>
> Typical payday repeats are "the 15th and last -day-of-month if a workday or
> the closest preceding workday if not", "second and last Friday", "every
> other Friday"...
>
> No matter how '1 month' is interpreted in generate_series, the application
> programmer will still need to write the queries required to handle whatever
> calendar-repeat features are deemed necessary.
If you look up David Skoll's "remind" application
, you'll find something
that does this kind of pattern matching quite, quite well, at a rather
sophisticated level.
I find that I don't want to go through the struggle of figuring out
how to correctly describe those recurrences.
The other way of doing this sort of thing, which seems to be generally
more intuitive, is to treat these calendars as sets, which are a
structure that SQL is rather will designed to manipulate, and use
inclusions/exclusions/intersections to determine which days are of
interest.
I wrote something on this on pgsql-general about 5 years ago that
still seems pretty relevant.
http://archives.postgresql.org/pgsql-general/2006-02/msg00159.php
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
On Mon, Jun 27, 2011 at 1:49 PM, David E. Wheeler wrote:
> Hackers,
>
> I'm curious about behavior such as this:
>
> bric=# select generate_series('2011-05-31'::timestamp ,
> '2012-04-01'::timestamp, '1 month');
> generate_series
> -
> 2011-05-31 00:00:00
> 2011-06-30 00:00:00
> 2011-07-30 00:00:00
> 2011-08-30 00:00:00
> 2011-09-30 00:00:00
> 2011-10-30 00:00:00
> 2011-11-30 00:00:00
> 2011-12-30 00:00:00
> 2012-01-30 00:00:00
> 2012-02-29 00:00:00
> 2012-03-29 00:00:00
>
> It seems to me that this is subject to interpretation. If I was building a
> calendaring app, for example, I might rather that the results were:
>
> generate_series
> -
> 2011-05-31 00:00:00
> 2011-06-30 00:00:00
> 2011-07-31 00:00:00
> 2011-08-31 00:00:00
> 2011-09-30 00:00:00
> 2011-10-31 00:00:00
> 2011-11-30 00:00:00
> 2011-12-31 00:00:00
> 2012-01-31 00:00:00
> 2012-02-29 00:00:00
> 2012-03-31 00:00:00
>
> Is there some way to change the interpretation of interval calculation like
> this? Or would I just have to write my own function to do it the way I want?
It's not hugely difficult to get something pretty appropriate:
emp@localhost-> select generate_series('2011-06-01'::timestamp ,
'2012-04-01'::timestamp, '1 month')- '1 day' ::interval;
?column?
-
2011-05-31 00:00:00
2011-06-30 00:00:00
2011-07-31 00:00:00
2011-08-31 00:00:00
2011-09-30 00:00:00
2011-10-31 00:00:00
2011-11-30 00:00:00
2011-12-31 00:00:00
2012-01-31 00:00:00
2012-02-29 00:00:00
2012-03-31 00:00:00
(11 rows)
That's more or less a bit of "cleverness." But it's not so grossly
clever as to seem too terribly frightful.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
Yeah, which is why I said it was subject to interpretation. Of course there's no way to tell generate_series() which to use, which is what I figured. Fortunately PostgreSQL uses the same interpretation for '1 month' when used in generate_series that it does everywhere else - to do otherwise would be hella confusing. :) Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
On Mon, Jun 27, 2011 at 1:38 PM, David E. Wheeler wrote: > > Yeah, which is why I said it was subject to interpretation. Of course > there's no way to tell generate_series() which to use, which is what I > figured. > generate_series() is doing exactly what it was designed to do, the imprecision regarding adding '1 month' to something that may or may not have been intended to be 'last day of the month' is a limitation in the interval code. One way to change this would be to implement another interval type such as 'full_month' which would take a date that is know to be the last day of the month and make it the last day of the appropriate month. If the starting date is NOT the last day of a month, the existing logic would suffice. Or you can do as I have done and create your own last_day() function that takes any date and makes it the last day of that month, and apply it to the output of generate_series(); -- Mike Nolan no...@tssi.com
Re: [HACKERS] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address
On Mon, Jun 27, 2011 at 01:28:30PM -0400, Robert Haas wrote: > On Wed, Jun 22, 2011 at 1:36 PM, Robert Haas wrote: > > I agree with you. ?If we had a whole pile of options it might be worth > > having heap_openrv() and heap_openrv_extended() so as not to > > complicate the simple case, but since there's no forseeable need to > > add anything other than missing_ok, my gut is to just add it and call > > it good. > > On further review, my gut is having second thoughts. This patch is an > awful lot smaller and easier to verify correctness if I just mess with > the "try" calls and not the regular ones; and it avoids both > back-patching hazards for us and hoops for third-party loadable > modules that are using the non-try versions of those functions to jump > through. +1. (Note that the function header comments need a few more updates.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, constructors, and the type system
On Sat, Jun 25, 2011 at 6:29 PM, Jeff Davis wrote: > Different ranges over the same subtype make sense when using different > total orders for the subtype. This is most apparent with text collation, > but makes sense (at least mathematically, if not practically) for any > subtype. > > For instance: > [a, Z) > is a valid range in "en_US", but not in "C", so it makes sense to have > multiple ranges over the same subtype with different collations. > > But what if you have a function (like a constructor), of the form: > (anyelement, anyelement) -> anyrange > ? To work with the type system, you need to be able to figure out the > return type from the arguments; which means to support functions like > this we need a mapping from the subtype to the range type. > Unfortunately, that restricts us to one range type per subtype (this > isn't a problem for ARRAYs, because there is only one useful array type > for a given element type). > > This problem first came up a while ago: > http://archives.postgresql.org/pgsql-hackers/2011-01/msg02788.php > > My workaround was to use domains, but that's not a very clean solution > (you have to add a bunch of casts to make sure the right domain is > chosen). It became entirely unworkable with collations, because people > would be using different text collations a lot more frequently than, > say, a different ordering for timestamptz. Tom mentioned that here: > > http://archives.postgresql.org/message-id/24831.1308579...@sss.pgh.pa.us > > I think Florian proposed the most promising line of attack here: > > http://archives.postgresql.org/message-id/ad4fc75d-db99-48ed-9082-52ee3a4d7...@phlo.org > > by suggesting that functions of the form: > (anyelement, [other non-anyrange arguments]) -> anyrange > might be expendable. After all, they are only useful for constructors as > far as we can tell. Other range functions will have an anyrange > parameter, and we can use the actual type of the argument to know the > range type (as well as the subtype). > > Although it's very nice to be able to say: > range(1,10) > and get an int4range out of it, it's not the only way, and it's not > without its problems anyway. For instance, to get an int8range you have > to do: > range(1::int8, 10::int8) > or similar. > > So, we could just make functions like: > int4range(int4, int4) > int8range(int8, int8) > ... > when creating the range type, and it would actually be a usability > improvement. Couldn't we also do neither of these things? I mean, presumably '[1,10]'::int8range had better work. I'm not saying that's ideal from a usability perspective but I fear this patch is going to be unmanageably large, and separating out the things that you need for it to work at all from the things that you need in order for it to be convenient might have some merit. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
On Jun 27, 2011, at 11:36 AM, Steve Crawford wrote: > The query is marginally trickier. But the better calendaring apps give a > variety of options when selecting "repeat": A user who selects June 30, 2011 > and wants a monthly repeat might want: > > 30th of every month - skip months without a 30th > 30th of every month - move to end-of-month if 30th doesn't exist > Last day of every month > Last Thursday of every month > > Typical payday repeats are "the 15th and last -day-of-month if a workday or > the closest preceding workday if not", "second and last Friday", "every other > Friday"... > > No matter how '1 month' is interpreted in generate_series, the application > programmer will still need to write the queries required to handle whatever > calendar-repeat features are deemed necessary. Yeah, which is why I said it was subject to interpretation. Of course there's no way to tell generate_series() which to use, which is what I figured. Thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
On 06/27/2011 10:56 AM, David E. Wheeler wrote:
On Jun 27, 2011, at 10:54 AM, Steve Crawford wrote:
That's just how intervals that represent varying periods of time work. You
would need to write your own. But a series of end-of-month dates is pretty easy:
select generate_series('2011-06-01'::timestamp , '2012-04-01'::timestamp, '1
month') - '1 day'::interval;
Yeah, but it's trickier if you have a calendaring app and don't know that date
a user has chosen for a monthly recurring event. They might have selected June
30, in which case only February would ever need to be different than the
default.
Best,
David
The query is marginally trickier. But the better calendaring apps give a
variety of options when selecting "repeat": A user who selects June 30,
2011 and wants a monthly repeat might want:
30th of every month - skip months without a 30th
30th of every month - move to end-of-month if 30th doesn't exist
Last day of every month
Last Thursday of every month
Typical payday repeats are "the 15th and last -day-of-month if a workday
or the closest preceding workday if not", "second and last Friday",
"every other Friday"...
No matter how '1 month' is interpreted in generate_series, the
application programmer will still need to write the queries required to
handle whatever calendar-repeat features are deemed necessary.
Cheers,
Steve
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
On Mon, Jun 27, 2011 at 2:34 PM, Bruce Momjian wrote: > Bruce Momjian wrote: >> Robert Haas wrote: >> > On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian wrote: >> > > Robert Haas wrote: >> > >> On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian wrote: >> > >> > Robert Haas wrote: >> > >> >> On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian >> > >> >> wrote: >> > >> >> > OK, fair enough. ?Should I apply my ports patch to Postgres 9.2? >> > >> >> >> > >> >> I'm not sure which patch you are referring to. >> > >> > >> > >> > This one which makes 50432 the default port. >> > >> >> > >> There appear to be some other changes mixed into this patch. >> > > >> > > The additional changes were to have the existing environment variables >> > > begin with "PG", as requested. >> > >> > It's easier to read the patches if you do separate changes in separate >> > patches. Anyway, I'm a bit nervous about this hunk: >> > >> > + if (old_cluster.port == DEF_PGUPORT) >> > + pg_log(PG_FATAL, "When checking a live old server, " >> > + "you must specify the old server's port >> > number.\n"); >> > >> > Is the implication here that I'm now going to need to specify more >> > than 4 command-line options/environment variables for this to work? >> >> Yes, we don't inherit PGPORT anymore. Doing anything else was too >> complex to explain in the docs. > > But only if you are running --check on a live server. Otherwise, we > will just default to 50432 instead of 5432/PGPORT. Oh... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
Bruce Momjian wrote: > Robert Haas wrote: > > On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian wrote: > > > Robert Haas wrote: > > >> On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian wrote: > > >> > Robert Haas wrote: > > >> >> On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian > > >> >> wrote: > > >> >> > OK, fair enough. ?Should I apply my ports patch to Postgres 9.2? > > >> >> > > >> >> I'm not sure which patch you are referring to. > > >> > > > >> > This one which makes 50432 the default port. > > >> > > >> There appear to be some other changes mixed into this patch. > > > > > > The additional changes were to have the existing environment variables > > > begin with "PG", as requested. > > > > It's easier to read the patches if you do separate changes in separate > > patches. Anyway, I'm a bit nervous about this hunk: > > > > + if (old_cluster.port == DEF_PGUPORT) > > + pg_log(PG_FATAL, "When checking a live old server, " > > + "you must specify the old server's port > > number.\n"); > > > > Is the implication here that I'm now going to need to specify more > > than 4 command-line options/environment variables for this to work? > > Yes, we don't inherit PGPORT anymore. Doing anything else was too > complex to explain in the docs. But only if you are running --check on a live server. Otherwise, we will just default to 50432 instead of 5432/PGPORT. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
On Mon, Jun 27, 2011 at 2:27 PM, Bruce Momjian wrote: > Robert Haas wrote: >> On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian wrote: >> > Robert Haas wrote: >> >> On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian wrote: >> >> > Robert Haas wrote: >> >> >> On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian >> >> >> wrote: >> >> >> > OK, fair enough. ?Should I apply my ports patch to Postgres 9.2? >> >> >> >> >> >> I'm not sure which patch you are referring to. >> >> > >> >> > This one which makes 50432 the default port. >> >> >> >> There appear to be some other changes mixed into this patch. >> > >> > The additional changes were to have the existing environment variables >> > begin with "PG", as requested. >> >> It's easier to read the patches if you do separate changes in separate >> patches. Anyway, I'm a bit nervous about this hunk: >> >> + if (old_cluster.port == DEF_PGUPORT) >> + pg_log(PG_FATAL, "When checking a live old server, " >> + "you must specify the old server's port >> number.\n"); >> >> Is the implication here that I'm now going to need to specify more >> than 4 command-line options/environment variables for this to work? > > Yes, we don't inherit PGPORT anymore. Doing anything else was too > complex to explain in the docs. Seems like a usability regression. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Fast GiST index build
On Mon, Jun 27, 2011 at 6:34 PM, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > The penalty function is called whenever a tuple is routed to the next level > down, and the final tree has the same depth with and without the patch, so I > would expect the number of penalty calls to be roughly the same. But clearly > there's something wrong with that logic; can you explain in layman's terms > why the patch adds so many gist penalty calls? And how many calls does it > actually add, can you gather some numbers on that? Any ides on how to > mitigate that, or do we just have to live with it? Or maybe use some > heuristic to use the existing insertion method when the patch is not > expected to be helpful? > In short due to parralel routing of many index tuples routing can alter. In fast build algorithm index tuples are accumulating into node buffers. When corresponding node splits we have to repocate index tuples from it. In original algorithm we are relocating node buffers into buffers of new nodes produced by split. Even this requires additional penalty calls. But for improvement of index quality I modified algorithm. With my modification index tuple of splitted node buffer can be relocated also into other node buffers of same parent. It produces more penalty calls. I didn't have an estimate yet, but I'm working on it. Unfortunatelly, I haven't any idea about mitigating it except turning off my modification. Heuristic is possible, but I feel following problems. At first, we need to somehow estimate length of varlena keys. I avoid this estimate in fast algorithm itself just assumed worst case, but I believe we need some more precise for good heuristic. At second, the right decision is strongly depend on concurrent load. When there are no concurrent load (as in my experiments) fraction of tree which fits to effective cache is reasonable for estimating benefit of IO economy. But with high concurrent load part of cache occupied by tree should be considerable smaller than whole effective cache. -- With best regards, Alexander Korotkov.
Re: [HACKERS] pg_upgrade defaulting to port 25432
Robert Haas wrote: > On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian wrote: > > Robert Haas wrote: > >> On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian wrote: > >> > Robert Haas wrote: > >> >> On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian wrote: > >> >> > OK, fair enough. ?Should I apply my ports patch to Postgres 9.2? > >> >> > >> >> I'm not sure which patch you are referring to. > >> > > >> > This one which makes 50432 the default port. > >> > >> There appear to be some other changes mixed into this patch. > > > > The additional changes were to have the existing environment variables > > begin with "PG", as requested. > > It's easier to read the patches if you do separate changes in separate > patches. Anyway, I'm a bit nervous about this hunk: > > + if (old_cluster.port == DEF_PGUPORT) > + pg_log(PG_FATAL, "When checking a live old server, " > +"you must specify the old server's port > number.\n"); > > Is the implication here that I'm now going to need to specify more > than 4 command-line options/environment variables for this to work? Yes, we don't inherit PGPORT anymore. Doing anything else was too complex to explain in the docs. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
On Mon, Jun 27, 2011 at 2:19 PM, Bruce Momjian wrote: > Robert Haas wrote: >> On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian wrote: >> > Robert Haas wrote: >> >> On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian wrote: >> >> > OK, fair enough. ?Should I apply my ports patch to Postgres 9.2? >> >> >> >> I'm not sure which patch you are referring to. >> > >> > This one which makes 50432 the default port. >> >> There appear to be some other changes mixed into this patch. > > The additional changes were to have the existing environment variables > begin with "PG", as requested. It's easier to read the patches if you do separate changes in separate patches. Anyway, I'm a bit nervous about this hunk: + if (old_cluster.port == DEF_PGUPORT) + pg_log(PG_FATAL, "When checking a live old server, " + "you must specify the old server's port number.\n"); Is the implication here that I'm now going to need to specify more than 4 command-line options/environment variables for this to work? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SSI modularity questions
There are two outstanding patches for SSI which involve questions about modularity. In particular, they involve calls to predicate locking and conflict detection from executor source files rather than AM source files (where most such calls exist). (1) Dan submitted this patch: http://archives.postgresql.org/message-id/20110622045850.gn83...@csail.mit.edu which is a very safe and very simple patch to improve performance on sequential heap scans at the serializable transaction isolation level. The location of the code being modified raised questions about modularity. There is a reasonably clear place to which it could be moved in the heap AM, but because it would acquire a predicate lock during node setup, it would get a lock on the heap even if the node was never used, which could be a performance regression in some cases. (2) In reviewing the above, Heikki noticed that there was a second place in the executor that SSI calls were needed but missing. I submitted a patch here: http://archives.postgresql.org/message-id/4e07550f02250003e...@gw.wicourts.gov I wonder, though, whether the section of code which I needed to modify should be moved to a new function in heapam.c on modularity grounds. If these two places were moved, there would be no SSI calls from any source file in the executor subdirectory. Should these be moved before beta3? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
Robert Haas wrote: > On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian wrote: > > Robert Haas wrote: > >> On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian wrote: > >> > OK, fair enough. ?Should I apply my ports patch to Postgres 9.2? > >> > >> I'm not sure which patch you are referring to. > > > > This one which makes 50432 the default port. > > There appear to be some other changes mixed into this patch. The additional changes were to have the existing environment variables begin with "PG", as requested. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, constructors, and the type system
Jeff Davis wrote: On Sun, 2011-06-26 at 22:29 -0700, Darren Duncan wrote: Tom Lane wrote: Darren Duncan writes: I believe that the best general solution here is for every ordered base type to just have a single total order, which is always used with that type in any generic order-sensitive operation, including any ranges defined over it, or any ORDER BY or any <,>,etc. We've spent years and blood on making sure that Postgres could support multiple orderings for any datatype; and there are plenty of natural examples for the usefulness of that. So I'm not at all impressed by any line of reasoning that starts out by baldly throwing that away. I'm not saying that you can't use multiple orderings with a data type. I'm just saying that the type only has *at most* one (possibly none) *native* ordering, which is what is used when you do something ordered-sensitive with the type, such as have a range. So, are you saying that it would be impossible to have a range that uses a different ordering? What about ORDER BY? What about BTrees? And if those things can use different orders for the same type, then what is the difference between what you are suggesting and a default ordering for the type (which we already support)? I suppose it's hard to tell what you mean by "native". Regards, Jeff Davis Maybe I'm just talking about "default ordering" then. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Commitfest 2001-06: 10 days in
All, So we're supposedly 1/3 of the way through CF1. Here's the good news: - Almost all patches have reviewers assigned. - 9 patches have been committed - 8 more are ready for a committer - 9 have been returned This means that 1/4 of the patches have been dealt with and another 1/8 should be dealt with soon. That sounds good until you realize that we're 1/3 of the way into the commitfest -- at this point most of the patches should have been reviewed, and half of them committed. So we need to get cracking. The biggest overall holdup seems to be that very few reviews have been turned in to -hackers. I'll be emailing individual reviewers about their patches soon. In the meantime, there's a couple of patch "sets" which need an advanced pg hacker to review them. I don't feel comfortable assigning them via RRR to an intermediate-level contributor: 1) Robert Haas's vxid and less lwlocks patches: https://commitfest.postgresql.org/action/patch_view?id=572 https://commitfest.postgresql.org/action/patch_view?id=585 2) Kaigai's security patches: https://commitfest.postgresql.org/action/patch_view?id=550 https://commitfest.postgresql.org/action/patch_view?id=570 https://commitfest.postgresql.org/action/patch_view?id=571 https://commitfest.postgresql.org/action/patch_view?id=578 If you can help with any of these, please let me know! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
On Jun 27, 2011, at 11:03 AM, Kevin Grittner wrote: > It is precisely to support such fancy things that some products > support a more abstract date type which allows 31 days in any month, > and then normalizes to real dates as needed. The PostgreSQL > developer community has generally not been receptive to such use > cases. I think you need to iterate through month intervals and add > those to the starting date for now. If you want to start with the > last day of a month with less than 31 days, you may need to back up > a month or two to find a suitable month and offset your intervals by > the appropriate number of months. > > I'd bet that if you encapsulate all that in a PostgreSQL function, > you're not the only one who would find it useful. Yeah, did that a while ago: http://www.justatheory.com/computers/databases/postgresql/recurring_events.html I think it could be simpler now, with generate_series() for some intervals. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per-column generic option
2011/6/27 Shigeru Hanada : >> * It might be an option to extend attreloptions, instead of the new >> attfdwoptions. >> Although I didn't track the discussion when pg_foreign_table catalog >> that provides >> relation level fdw-options, was it impossible or unreasonable to extend >> existing >> design of reloptions/attoptions? >> Right now, it accepts only hard-wired options listed at reloptions.c. >> But, it seems >> to me worthwhile, if it could accept options validated by loadable modules. > > IIRC someone has objected against storing FDW options in > reloptions/attoptions, but I couldn't find such post. I'll follow the > discussion again. I think they should definitely be separate. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
On Mon, Jun 27, 2011 at 1:59 PM, Bruce Momjian wrote: > Robert Haas wrote: >> On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian wrote: >> > OK, fair enough. ?Should I apply my ports patch to Postgres 9.2? >> >> I'm not sure which patch you are referring to. > > This one which makes 50432 the default port. There appear to be some other changes mixed into this patch. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
"David E. Wheeler" wrote: > generate_series > - > 2011-05-31 00:00:00 > 2011-06-30 00:00:00 > 2011-07-31 00:00:00 > 2011-08-31 00:00:00 > 2011-09-30 00:00:00 > 2011-10-31 00:00:00 > 2011-11-30 00:00:00 > 2011-12-31 00:00:00 > 2012-01-31 00:00:00 > 2012-02-29 00:00:00 > 2012-03-31 00:00:00 > > Is there some way to change the interpretation of interval > calculation like this? Or would I just have to write my own > function to do it the way I want? It is precisely to support such fancy things that some products support a more abstract date type which allows 31 days in any month, and then normalizes to real dates as needed. The PostgreSQL developer community has generally not been receptive to such use cases. I think you need to iterate through month intervals and add those to the starting date for now. If you want to start with the last day of a month with less than 31 days, you may need to back up a month or two to find a suitable month and offset your intervals by the appropriate number of months. I'd bet that if you encapsulate all that in a PostgreSQL function, you're not the only one who would find it useful. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Make the visibility map crash-safe.
On Thu, Jun 23, 2011 at 9:22 AM, Robert Haas wrote: > On Wed, Jun 22, 2011 at 10:23 PM, Robert Haas wrote: >> Well, it seems I didn't put nearly enough thought into heap_update(). >> The fix for the immediate problem looks simple enough - all the code >> has been refactored to use the new API, so the calls can be easily be >> moved into the critical section (see attached). But looking at this a >> little more, I see that heap_update() is many bricks short of a load, >> because there are several places where the buffer can be unlocked and >> relocked, and we don't recheck whether the page is all-visible after >> reacquiring the lock. So I've got some more work to do here. > > See what you think of the attached. I *think* this covers all bases. > It's a little more complicated than I would like, but I don't think > fatally so. For lack of comment, committed. It's hopefully at least better than what was there before, which was clearly several bricks short of a load. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
Robert Haas wrote:
> On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian wrote:
> > OK, fair enough. ?Should I apply my ports patch to Postgres 9.2?
>
> I'm not sure which patch you are referring to.
This one which makes 50432 the default port.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 1ee2aca..5c5ce72
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*** output_check_banner(bool *live_check)
*** 29,34
--- 29,37
if (user_opts.check && is_server_running(old_cluster.pgdata))
{
*live_check = true;
+ if (old_cluster.port == DEF_PGUPORT)
+ pg_log(PG_FATAL, "When checking a live old server, "
+ "you must specify the old server's port number.\n");
if (old_cluster.port == new_cluster.port)
pg_log(PG_FATAL, "When checking a live server, "
"the old and new port numbers must be different.\n");
diff --git a/contrib/pg_upgrade/option.c b/contrib/pg_upgrade/option.c
new file mode 100644
index 4401a81..d29aad0
*** a/contrib/pg_upgrade/option.c
--- b/contrib/pg_upgrade/option.c
*** parseCommandLine(int argc, char *argv[])
*** 58,65
os_info.progname = get_progname(argv[0]);
/* Process libpq env. variables; load values here for usage() output */
! old_cluster.port = getenv("PGPORT") ? atoi(getenv("PGPORT")) : DEF_PGPORT;
! new_cluster.port = getenv("PGPORT") ? atoi(getenv("PGPORT")) : DEF_PGPORT;
os_user_effective_id = get_user_info(&os_info.user);
/* we override just the database user name; we got the OS id above */
--- 58,65
os_info.progname = get_progname(argv[0]);
/* Process libpq env. variables; load values here for usage() output */
! old_cluster.port = getenv("PGPORTOLD") ? atoi(getenv("PGPORTOLD")) : DEF_PGUPORT;
! new_cluster.port = getenv("PGPORTNEW") ? atoi(getenv("PGPORTNEW")) : DEF_PGUPORT;
os_user_effective_id = get_user_info(&os_info.user);
/* we override just the database user name; we got the OS id above */
*** parseCommandLine(int argc, char *argv[])
*** 203,215
}
/* Get values from env if not already set */
! check_required_directory(&old_cluster.bindir, "OLDBINDIR", "-b",
"old cluster binaries reside");
! check_required_directory(&new_cluster.bindir, "NEWBINDIR", "-B",
"new cluster binaries reside");
! check_required_directory(&old_cluster.pgdata, "OLDDATADIR", "-d",
"old cluster data resides");
! check_required_directory(&new_cluster.pgdata, "NEWDATADIR", "-D",
"new cluster data resides");
}
--- 203,215
}
/* Get values from env if not already set */
! check_required_directory(&old_cluster.bindir, "PGBINOLD", "-b",
"old cluster binaries reside");
! check_required_directory(&new_cluster.bindir, "PGBINNEW", "-B",
"new cluster binaries reside");
! check_required_directory(&old_cluster.pgdata, "PGDATAOLD", "-d",
"old cluster data resides");
! check_required_directory(&new_cluster.pgdata, "PGDATANEW", "-D",
"new cluster data resides");
}
*** For example:\n\
*** 254,270
or\n"), old_cluster.port, new_cluster.port, os_info.user);
#ifndef WIN32
printf(_("\
! $ export OLDDATADIR=oldCluster/data\n\
! $ export NEWDATADIR=newCluster/data\n\
! $ export OLDBINDIR=oldCluster/bin\n\
! $ export NEWBINDIR=newCluster/bin\n\
$ pg_upgrade\n"));
#else
printf(_("\
! C:\\> set OLDDATADIR=oldCluster/data\n\
! C:\\> set NEWDATADIR=newCluster/data\n\
! C:\\> set OLDBINDIR=oldCluster/bin\n\
! C:\\> set NEWBINDIR=newCluster/bin\n\
C:\\> pg_upgrade\n"));
#endif
printf(_("\nReport bugs to .\n"));
--- 254,270
or\n"), old_cluster.port, new_cluster.port, os_info.user);
#ifndef WIN32
printf(_("\
! $ export PGDATAOLD=oldCluster/data\n\
! $ export PGDATANEW=newCluster/data\n\
! $ export PGBINOLD=oldCluster/bin\n\
! $ export PGBINNEW=newCluster/bin\n\
$ pg_upgrade\n"));
#else
printf(_("\
! C:\\> set PGDATAOLD=oldCluster/data\n\
! C:\\> set PGDATANEW=newCluster/data\n\
! C:\\> set PGBINOLD=oldCluster/bin\n\
! C:\\> set PGBINNEW=newCluster/bin\n\
C:\\> pg_upgrade\n"));
#endif
printf(_("\nReport bugs to .\n"));
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 613ddbd..4729ac3
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
***
*** 15,20
--- 15,23
#include "libpq-fe.h"
+ /* Use port in the private/dynamic port number range */
+ #define DEF_PGUPORT 50432
+
/* Allocate for null byte */
#define USER_NAME_SIZE 128
diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml
new file mode 100644
index b24c1e7..aa633e2
*** a/doc/src/sgml
Re: [HACKERS] generate_series() Interpretation
On Jun 27, 2011, at 10:54 AM, Steve Crawford wrote:
> That's just how intervals that represent varying periods of time work. You
> would need to write your own. But a series of end-of-month dates is pretty
> easy:
> select generate_series('2011-06-01'::timestamp , '2012-04-01'::timestamp, '1
> month') - '1 day'::interval;
Yeah, but it's trickier if you have a calendaring app and don't know that date
a user has chosen for a monthly recurring event. They might have selected June
30, in which case only February would ever need to be different than the
default.
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generate_series() Interpretation
On 06/27/2011 10:49 AM, David E. Wheeler wrote:
Hackers,
I'm curious about behavior such as this:
bric=# select generate_series('2011-05-31'::timestamp ,
'2012-04-01'::timestamp, '1 month');
generate_series
-
2011-05-31 00:00:00
2011-06-30 00:00:00
2011-07-30 00:00:00
2011-08-30 00:00:00
2011-09-30 00:00:00
2011-10-30 00:00:00
2011-11-30 00:00:00
2011-12-30 00:00:00
2012-01-30 00:00:00
2012-02-29 00:00:00
2012-03-29 00:00:00
It seems to me that this is subject to interpretation. If I was building a
calendaring app, for example, I might rather that the results were:
generate_series
-
2011-05-31 00:00:00
2011-06-30 00:00:00
2011-07-31 00:00:00
2011-08-31 00:00:00
2011-09-30 00:00:00
2011-10-31 00:00:00
2011-11-30 00:00:00
2011-12-31 00:00:00
2012-01-31 00:00:00
2012-02-29 00:00:00
2012-03-31 00:00:00
Is there some way to change the interpretation of interval calculation like
this? Or would I just have to write my own function to do it the way I want?
Thanks,
David
That's just how intervals that represent varying periods of time work.
You would need to write your own. But a series of end-of-month dates is
pretty easy:
select generate_series('2011-06-01'::timestamp ,
'2012-04-01'::timestamp, '1 month') - '1 day'::interval;
?column?
-
2011-05-31 00:00:00
2011-06-30 00:00:00
2011-07-31 00:00:00
2011-08-31 00:00:00
2011-09-30 00:00:00
2011-10-31 00:00:00
2011-11-30 00:00:00
2011-12-31 00:00:00
2012-01-31 00:00:00
2012-02-29 00:00:00
2012-03-31 00:00:00
Cheers,
Steve
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] beta3?
On Mon, Jun 27, 2011 at 1:51 PM, Josh Berkus wrote: > On 6/27/11 9:45 AM, Robert Haas wrote: >> We have a couple of open items outstanding right now, but I'm >> wondering if it's about time we should be thinking about a date for >> beta3. >> >> We tagged beta1 on April 27th, and beta2 on June 9th, so about six weeks >> apart. >> >> But perhaps we shouldn't wait quite so long before putting out beta3? > > I'd be up for July 11. July 5 would be difficult, both because of the > American holiday, and Tom being on a trip. That sounds reasonable to me. I'll be on vacation then, but (1) I'm not really involved in pushing the release out the door and (2) I should have Internet access if push comes to shove. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
On Mon, Jun 27, 2011 at 1:49 PM, Bruce Momjian wrote: > OK, fair enough. Should I apply my ports patch to Postgres 9.2? I'm not sure which patch you are referring to. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] beta3?
On 6/27/11 9:45 AM, Robert Haas wrote: > We have a couple of open items outstanding right now, but I'm > wondering if it's about time we should be thinking about a date for > beta3. > > We tagged beta1 on April 27th, and beta2 on June 9th, so about six weeks > apart. > > But perhaps we shouldn't wait quite so long before putting out beta3? I'd be up for July 11. July 5 would be difficult, both because of the American holiday, and Tom being on a trip. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] generate_series() Interpretation
Hackers,
I'm curious about behavior such as this:
bric=# select generate_series('2011-05-31'::timestamp ,
'2012-04-01'::timestamp, '1 month');
generate_series
-
2011-05-31 00:00:00
2011-06-30 00:00:00
2011-07-30 00:00:00
2011-08-30 00:00:00
2011-09-30 00:00:00
2011-10-30 00:00:00
2011-11-30 00:00:00
2011-12-30 00:00:00
2012-01-30 00:00:00
2012-02-29 00:00:00
2012-03-29 00:00:00
It seems to me that this is subject to interpretation. If I was building a
calendaring app, for example, I might rather that the results were:
generate_series
-
2011-05-31 00:00:00
2011-06-30 00:00:00
2011-07-31 00:00:00
2011-08-31 00:00:00
2011-09-30 00:00:00
2011-10-31 00:00:00
2011-11-30 00:00:00
2011-12-31 00:00:00
2012-01-31 00:00:00
2012-02-29 00:00:00
2012-03-31 00:00:00
Is there some way to change the interpretation of interval calculation like
this? Or would I just have to write my own function to do it the way I want?
Thanks,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
\Robert Haas wrote: > On Mon, Jun 27, 2011 at 1:39 PM, Bruce Momjian wrote: > > Robert Haas wrote: > >> On Sun, Jun 26, 2011 at 10:33 PM, Alvaro Herrera > >> wrote: > >> > Excerpts from Robert Haas's message of vie jun 24 22:22:55 -0400 2011: > >> >> On Fri, Jun 24, 2011 at 7:47 PM, Bruce Momjian wrote: > >> > > >> >> > You want the environment variable support removed? > >> >> > >> >> I don't. ?It's production usefulness is questionable, but it's quite > >> >> handy for testing IMO. > >> > > >> > If that's what you want, I think being able to read a file (whose > >> > filename you pass with a switch to pg_upgrade) with a bunch of settings > >> > is even more convenient. ?Heck, maybe it's more convenient for the user > >> > too. > >> > >> If someone wants to do the work, I'm all in favor. ?But I don't feel > >> that we should insist that Bruce do it. > > > > Is there agreement to remove all pg_upgrade-specific environment > > variables? > > I'm not in favor of that unless we have a workable replacement for them. OK, fair enough. Should I apply my ports patch to Postgres 9.2? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
On Mon, Jun 27, 2011 at 1:39 PM, Bruce Momjian wrote: > Robert Haas wrote: >> On Sun, Jun 26, 2011 at 10:33 PM, Alvaro Herrera >> wrote: >> > Excerpts from Robert Haas's message of vie jun 24 22:22:55 -0400 2011: >> >> On Fri, Jun 24, 2011 at 7:47 PM, Bruce Momjian wrote: >> > >> >> > You want the environment variable support removed? >> >> >> >> I don't. ?It's production usefulness is questionable, but it's quite >> >> handy for testing IMO. >> > >> > If that's what you want, I think being able to read a file (whose >> > filename you pass with a switch to pg_upgrade) with a bunch of settings >> > is even more convenient. ?Heck, maybe it's more convenient for the user >> > too. >> >> If someone wants to do the work, I'm all in favor. But I don't feel >> that we should insist that Bruce do it. > > Is there agreement to remove all pg_upgrade-specific environment > variables? I'm not in favor of that unless we have a workable replacement for them. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
Robert Haas wrote: > On Sun, Jun 26, 2011 at 10:33 PM, Alvaro Herrera > wrote: > > Excerpts from Robert Haas's message of vie jun 24 22:22:55 -0400 2011: > >> On Fri, Jun 24, 2011 at 7:47 PM, Bruce Momjian wrote: > > > >> > You want the environment variable support removed? > >> > >> I don't. ?It's production usefulness is questionable, but it's quite > >> handy for testing IMO. > > > > If that's what you want, I think being able to read a file (whose > > filename you pass with a switch to pg_upgrade) with a bunch of settings > > is even more convenient. ?Heck, maybe it's more convenient for the user > > too. > > If someone wants to do the work, I'm all in favor. But I don't feel > that we should insist that Bruce do it. Is there agreement to remove all pg_upgrade-specific environment variables? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [v9.2] DROP Reworks Part.0 - 'missing_ok' support of get_object_address
On Wed, Jun 22, 2011 at 1:36 PM, Robert Haas wrote: > On Wed, Jun 22, 2011 at 12:51 PM, Alvaro Herrera > wrote: >> Excerpts from Robert Haas's message of mié jun 22 08:56:02 -0400 2011: >> >>> Another option might be to leave heap_openrv() and relation_openrv() >>> alone and add a missing_ok argument to try_heap_openrv() and >>> try_relation_openrv(). Passing true would give the same behavior as >>> presently; passing false would make them behave like the non-try >>> version. >> >> That would be pretty weird, having two functions, one of them sometimes >> doing the same thing as the other one. >> >> I understand Noah's concern but I think your original proposal was saner >> than both options presented so far. > > I agree with you. If we had a whole pile of options it might be worth > having heap_openrv() and heap_openrv_extended() so as not to > complicate the simple case, but since there's no forseeable need to > add anything other than missing_ok, my gut is to just add it and call > it good. On further review, my gut is having second thoughts. This patch is an awful lot smaller and easier to verify correctness if I just mess with the "try" calls and not the regular ones; and it avoids both back-patching hazards for us and hoops for third-party loadable modules that are using the non-try versions of those functions to jump through. Third try attached... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company there-is-no-try-v3.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, constructors, and the type system
On Sun, 2011-06-26 at 22:29 -0700, Darren Duncan wrote: > Tom Lane wrote: > > Darren Duncan writes: > >> I believe that the best general solution here is for every ordered base > >> type to > >> just have a single total order, which is always used with that type in any > >> generic order-sensitive operation, including any ranges defined over it, > >> or any > >> ORDER BY or any <,>,etc. > > > > We've spent years and blood on making sure that Postgres could support > > multiple orderings for any datatype; and there are plenty of natural > > examples for the usefulness of that. So I'm not at all impressed by > > any line of reasoning that starts out by baldly throwing that away. > > I'm not saying that you can't use multiple orderings with a data type. I'm > just > saying that the type only has *at most* one (possibly none) *native* > ordering, > which is what is used when you do something ordered-sensitive with the type, > such as have a range. So, are you saying that it would be impossible to have a range that uses a different ordering? What about ORDER BY? What about BTrees? And if those things can use different orders for the same type, then what is the difference between what you are suggesting and a default ordering for the type (which we already support)? I suppose it's hard to tell what you mean by "native". 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] Range Types, constructors, and the type system
On Mon, 2011-06-27 at 12:16 +0200, Florian Pflug wrote: > I wouldn't take it that far. What I had in mind was to *only* support > the case where the cast directly follows the function call, i.e. the case > f(...)::type OK, so instead of writing: range(lower(range(1,2)),upper(range(1,2)))::int8range users would write: range(lower(range(1,2)::int8range),upper(range(1,2)::int8range))::int8range A little more verbose, but it seems like it wouldn't be a practical problem in very many cases. Multiple levels of constructors seem like they'd be fairly uncommon, and probably a case where a function should be written anyway. OK, I'll have to think about this a little more, but it seems like a reasonable approach. 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] beta3?
We have a couple of open items outstanding right now, but I'm wondering if it's about time we should be thinking about a date for beta3. We tagged beta1 on April 27th, and beta2 on June 9th, so about six weeks apart. But perhaps we shouldn't wait quite so long before putting out beta3? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types and length function
On Mon, 2011-06-27 at 12:25 +0200, Florian Pflug wrote:
> Does the current definition of length(range), i.e.
> upper(range) - lower(range)
> deal correctly with open vs. closed ranges and unbounded ranges? I'm thinking
> that it probably doesn't - what would be the results of
> length('[0,1]'::intrange) -- Should be 2
> length('[0,1)'::intrange) -- Should be 1
I alluded to this problem in an earlier email.
I think this would need to be handled by the "canonical" function. If
the canonical function is specified to return values in [) or (] form,
then we'd get the behavior above.
However, it's a little strange, because for discrete ranges you probably
want cardinality, not length. I don't have a clear idea on exactly what
behavior users will expect in this case, which is a pretty good argument
to leave length() out.
> length('[0,inf]'::intrange) -- Should be infinity, but ints can't
> represent that, can't they?
That would throw an exception currently, for exactly the reason you
mention.
> If it cannot be easily made to support these cases, than I vote for
> removing it all together.
I now agree. I think you've brought up some good reasons for that. If
users write upper(r)-lower(r), then they know what the semantics will
be; or they can easily write their own length() function (perhaps
specific to a range type).
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] Small 9.1 documentation fix (SSPI auth)
* Robert Haas wrote: On Fri, Jun 24, 2011 at 6:07 PM, Christian Ullrich wrote: When Magnus fixed and applied my SSPI-via-GSS patch in January, we forgot to fix to the documentation. Suggested patch attached; should I also put that four-liner into any CFs? I have committed a slightly different wording change to fix this problem. Let me know whether it looks OK... It does. Thanks for fixing. -- Christian -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deriving release notes from git commit messages
On Fri, 24 Jun 2011 13:42:04 -0400 Robert Haas wrote: > As for annotating the commit messages, I think something like: > > Reporter: Sam Jones > Author: Beverly Smith > Author: Jim Davids > Reviewer: Fred Block > Reviewer: Pauline Andrews Can I just toss in one little note from the sidelines? Various other projects (Linux kernel at the top of the list) have adopted tags like Reported-by and Reviewed-by for metadata like this. (Authorship lives in git itself, with additional authors sometimes ambiguously indicated with additional Signed-off-by lines). There are tools out there which make use of those tags now. It would seem that, in the absence of a reason to make up your own tags, it might make sense to be consistent with other projects? Thanks, jon -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade defaulting to port 25432
On Sun, Jun 26, 2011 at 10:33 PM, Alvaro Herrera wrote: > Excerpts from Robert Haas's message of vie jun 24 22:22:55 -0400 2011: >> On Fri, Jun 24, 2011 at 7:47 PM, Bruce Momjian wrote: > >> > You want the environment variable support removed? >> >> I don't. It's production usefulness is questionable, but it's quite >> handy for testing IMO. > > If that's what you want, I think being able to read a file (whose > filename you pass with a switch to pg_upgrade) with a bunch of settings > is even more convenient. Heck, maybe it's more convenient for the user > too. If someone wants to do the work, I'm all in favor. But I don't feel that we should insist that Bruce do it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Word-smithing doc changes
On Sat, Jun 25, 2011 at 9:01 PM, Greg Stark wrote: > I think this commit was ill-advised: > http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=a03feb9354bda5084f19cc952bc52ba7be89f372 > > In a concurrent index build, the index is actually entered into the > system catalogs in one transaction, then the two table scans occur in a > - second and third transaction. > + second and third transaction. All active transactions at the time the > + second table scan starts, not just ones that already involve the table, > + have the potential to block the concurrent index creation until they > + finish. When checking for transactions that could still use the original > + index, concurrent index creation advances through potentially interfering > + older transactions one at a time, obtaining shared locks on their virtual > + transaction identifiers to wait for them to complete. > > > Seems way to implementation-specific and detailed for a user to make > heads or tails of. Except in the sections talking about locking > internals we don't talk about "shared locks on virtual transactions > identifiers" we just talk about waiting for a transaction to complete. > And looping over the transactions one by one is purely an > implementation detail and uninteresting to users. Also it uses > ill-defined terms like "active transactions", "potentially interfering > older transactions", and "original index" -- from the user's point of > view there's only one index and it just isn't completely built yet. > > Are we not yet in string-freeze though? I'll go ahead and edit it if > people don't mind. I'm curious to see the original complaint though. We don't have a string freeze, and certainly not for the documentation, so if you'd like to wordsmith some more, have at it. But it would probably be best to post your revised version and solicit feedback before committing, since there was quite a bit of discussion about that change before it was made. (Sorry, don't have the pointer at the moment...) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Deriving release notes from git commit messages
On Mon, Jun 27, 2011 at 11:49 AM, Jonathan Corbet wrote: > On Fri, 24 Jun 2011 13:42:04 -0400 > Robert Haas wrote: > >> As for annotating the commit messages, I think something like: >> >> Reporter: Sam Jones >> Author: Beverly Smith >> Author: Jim Davids >> Reviewer: Fred Block >> Reviewer: Pauline Andrews > > Can I just toss in one little note from the sidelines? Various other > projects (Linux kernel at the top of the list) have adopted tags like > Reported-by and Reviewed-by for metadata like this. (Authorship lives in > git itself, with additional authors sometimes ambiguously indicated with > additional Signed-off-by lines). There are tools out there which make use > of those tags now. It would seem that, in the absence of a reason to make > up your own tags, it might make sense to be consistent with other projects? I'm not averse to inventing our own tags that fit our particular needs, but I don't think it would be a bad idea to maximize the intersection of what we do with what other people do. I think the biggest difference is probably that we (or at least I) don't really like the idea of Signed-off-by, and certainly not as a way of ambiguously indicating additional authors. Many patches are collaborative efforts, and the metadata should make that clear. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] spinlock contention
On Sat, Jun 25, 2011 at 8:26 PM, Greg Stark wrote: > On Thu, Jun 23, 2011 at 4:42 PM, Robert Haas wrote: >> ProcArrayLock looks like a tougher nut to crack - there's simply no >> way, with the system we have right now, that you can take a snapshot >> without locking the list of running processes. I'm not sure what to >> do about that, but we're probably going to have to come up with >> something, because it seems clear that once we eliminate the lock >> manager LWLock contention, this is a major bottleneck. > > Well as Tom observed earlier the kernel of a snapshot is actually a > LSN. A snapshot contains a set of xids which all committed before some > LSN and none which committed after it. > > So if we had a record of what log sequence number the commit record > for any given transaction is we could build the snapshot at our > leisure without any exclusive lock. In fact we could even build it > lazily as a kind of cache only when we actually are interested in a > given xid. Yeah, I've been thinking about that. I think what we might do is set up a new SLRU that works like CLOG, but each entry is an LSN rather than just two bits. When a transaction commits, we save the commit LSN under the entry for that XID. We truncate away SLRU pages that contain no still-running XIDs. When we need to check whether an XID is visible to our snapshot, we just look up the commit LSN and compare it with our snapshot LSN. If it's before and non-zero, we can see it. If it's after or all-zeroes, we can't. But I'm not sure how much this would really help. It might (subject to working out the details) make the actual process of taking a snapshot faster. But it's not clear that taking snapshots more quickly will actually help anything, because the problem is not the amount of time spending taking the snapshot. The problem is rather that doing so requires acquiring and releasing an LWLock, and each of those operations requires taking and releasing a spinlock. And it is the spinlock contention that is killing us. That makes me think we need a way to take a snapshot without taking a spinlock. Or if we must take spinlocks, we at least have to avoid every backend that needs a snapshot lusting after the *same* spinlock. What I've been thinking about this weekend is whether it might be possible to create a sort of lock-free queue infrastructure. When a backend starts up, it would add an entry to the queue saying "I'm running". When it commits, it would add an entry to the queue saying "I'm committed". All entries would be added at the *end* of the queue, so a backend scanning the queue to build up a snapshot wouldn't ever be able to see commits out of order. We would need some memory barrier operations on weak-memory-ordering machines to make sure that the queue writes became visible before the end-of-queue pointer bump. The trick is figuring out how to clean up the queue. Since "commit" entries exist only to guard against "running" entries earlier in the queue, the start-of-queue pointer can be advanced whenever it points to a "commit" entry. Also, if it points to a "running" entry for which there is a later "commit" entry, then the start-of-queue pointer can be advanced over that as well. However, just because we can advance the point at which backends start reading doesn't mean that we can actually recycle space, because while we know that new scans needn't worry about those entries, we *don't* know that there isn't already a scan in flight that still needs them. Furthermore, if a transaction runs for a long time, we can never advance the start-of-queue pointer past the "running" entry for its XID, which is obviously problematic since the queue would get very long. To work around that problem, I think we could use Florian's idea upthread of an RCU system. We keep two copies of the queue around, an A copy and a B copy. When the currently active copy fills up, we rewrite it into the other queue, omitting all "committed" entries and any "running" entries that have matching "committed" entries, and then tell everyone to start looking at that copy instead. We would need some kind of gymnastics to make sure that we don't flip from the A copy to the B copy and back to the A copy while some laggardly backend is still hoping to scan the old A copy. A simple algorithm (there's probably a smarter one) would be to have each backend take a spinlock while it's scanning either copy, and to have the backend that is doing the rewrite take and release all of those spinlocks one at a time before beginning the rewrite, thus guaranteeing that any scans still in progress when the rewrite is requested have completed before it's actually performed. Any new scans started in the meanwhile will certainly be looking at the current copy rather than the old copy we're about to overwrite. We would still need a lock around the operation of adding new items to the queue; if two backends try to do that at the same time, chaos will en
Re: [HACKERS] Small 9.1 documentation fix (SSPI auth)
On Fri, Jun 24, 2011 at 6:07 PM, Christian Ullrich wrote: > When Magnus fixed and applied my SSPI-via-GSS patch in January, we forgot to > fix to the documentation. Suggested patch attached; should I also put that > four-liner into any CFs? I have committed a slightly different wording change to fix this problem. Let me know whether it looks OK... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: starting to review the Extend NOT NULL representation to pg_constraint patch
On Mon, Jun 27, 2011 at 3:08 AM, Dean Rasheed wrote: > On 27 June 2011 03:31, Robert Haas wrote: >> On Sat, Jun 25, 2011 at 2:15 AM, Dean Rasheed >> wrote: >>> Really? I would expect the reverse, namely that the not-nullness is >>> part of the PK constraint and dropping the PK *would* then start >>> allowing NULLs. >> >> Hmm, OK. I had assumed we were only trying to fix the problem that >> parent and child inheritance tables could get out of step, but maybe >> you're right. >> >> If we go with that approach, then consider: >> >> CREATE TABLE foo (a int); >> CREATE TABLE bar () INHERITS (foo);> >> Now if someone adds a primary key foo (a), what happens currently is >> that foo.a becomes NOT NULL, but bar.a still allows NULLs. Should >> that remain true (on the theory that a primary key constraint is not >> inherited) or become false (on the theory that parent and child tables >> should match)? >> > > I'm not sure, but my real problem with the current behaviour is its > inconsistency. Consider this case: > > CREATE TABLE foo (a int PRIMARY KEY); > CREATE TABLE bar () INHERITS (foo); > > Currently this results in bar not allowing NULLs, which is > inconsistent with adding the PK after defining the inheritance. Then > if the PK is dropped, the non-nullness is left behind on both foo and > bar. > > I would summarise the consistency requirements as: > > 1). ADD CONSTRAINT should leave both parent and child tables in the > same state as they would have been if the constraint had been defined > at table creation time. > > 2). DROP CONSTRAINT should leave both parent and child tables in the > same state as if the constraint had never existed (completely > reversing the effects of ADD CONSTRAINT). > > I don't have a strong opinion as to whether or not the NOT NULL part > of a PK should be inherited, provided that it is consistent with the > above. > > I guess that if I were forced to choose, I would say that the NOT NULL > part of a PK should not be inherited, since I do think of it as part > of the PK, and PKs are not inherited. OK, I see your point, and I agree with you. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Fast GiST index build
On 27.06.2011 13:45, Alexander Korotkov wrote: I've added information about testing on some real-life dataset to wiki page. This dataset have a speciality: data is ordered inside it. In this case tradeoff was inverse in comparison with expectations about "fast build" algrorithm. Index built is longer but index quality is significantly better. I think high speed of regular index built is because sequential inserts are into near tree parts. That's why number of actual page reads and writes is low. The difference in tree quality I can't *convincingly explain now.* I've also maked tests with shuffled data of this dataset. In this case results was similar to random generated data. Once again, interesting results. The penalty function is called whenever a tuple is routed to the next level down, and the final tree has the same depth with and without the patch, so I would expect the number of penalty calls to be roughly the same. But clearly there's something wrong with that logic; can you explain in layman's terms why the patch adds so many gist penalty calls? And how many calls does it actually add, can you gather some numbers on that? Any ides on how to mitigate that, or do we just have to live with it? Or maybe use some heuristic to use the existing insertion method when the patch is not expected to be helpful? -- 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] heap_hot_search_buffer refactoring
On Sat, Jun 25, 2011 at 6:24 AM, Jeff Davis wrote: > On Fri, 2011-06-24 at 15:32 -0400, Robert Haas wrote: >> On Sun, Jun 19, 2011 at 2:16 PM, Robert Haas wrote: >> > New patch attached, with that one-line change. >> >> Jeff, are you planning to review this further? Do you think it's OK to >> commit? > > 1. Patch does not apply to master cleanly, and it's in unified format > (so I can't compare it against the old patch very easily). This review > is for the first patch, disregarding the "skip = !first_call" issue that > you already fixed. If you had other changes in the latest version, > please repost the patch. That is strange, because it applies for me. But I had no other changes. > 2. Comment above heap_hot_search_buffer should be updated to document > that heapTuple is an out-parameter and document the behavior of > first_call > > 3. The logic around "skip" is slightly confusing to me. Here's my > description: if it's not an MVCC snapshot and it's not the first call, > then you don't actually want to fetch the tuple with the given tid or a > later one in the chain -- you want to fetch the _next_ tuple in the > chain or a later one in the chain. Some wording of that description in a > comment (either in the function's comment or near the use of "skip") > would help a lot. Also, if skip is true, then the tid _must_ be visible > according to the (non-MVCC) snapshot, correct? It might help if that was > apparent from the code/comments. > > Other than that, it looks good. OK, I've applied this with some additional comment changes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq SSL with non-blocking sockets
On Fri, Jun 24, 2011 at 5:14 PM, Steve Singer wrote: > A few things I noticed (that you might be aware of since you mentioned it > needs cleanup) > > -The patch doesn't compile with non-ssl builds, the debug at the bottom of > PQSendSome isn't in an #ifdef > > -I don't think your handling the return code properly. Consider this case. > > pqSendSome(some data) > sslRetryBuf = some Data > return 1 > pqSendSome(more data) > it sends all of 'some data' > returns 0 > > I think 1 should be returned because all of 'more data' still needs to be > sent. I think returning a 0 will break PQsetnonblocking if you call it when > there is data in both sslRetryBuf and outputBuffer. > We might even want to try sending the data in outputBuffer after we've sent > all the data sitting in sslRetryBuf. > > > If you close the connection with an outstanding sslRetryBuf you need to free > it. Based on these comments, I have updated the status of the patch to "Waiting on Author". https://commitfest.postgresql.org/action/patch_view?id=594 -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Optimizing box_penalty (Re: [HACKERS] WIP: Fast GiST index build)
On 27.06.2011 13:45, Alexander Korotkov wrote:
I've added information about testing on some real-life dataset to wiki page.
This dataset have a speciality: data is ordered inside it. In this case
tradeoff was inverse in comparison with expectations about "fast build"
algrorithm. Index built is longer but index quality is significantly better.
I think high speed of regular index built is because sequential inserts are
into near tree parts. That's why number of actual page reads and writes is
low. The difference in tree quality I can't *convincingly explain now.*
I've also maked tests with shuffled data of this dataset. In this case
results was similar to random generated data.
Hmm, I assume the CPU overhead is coming from the penalty calls in this
case too. There's some low-hanging optimization fruit in
gist_box_penalty(), see attached patch. I tested this with:
CREATE TABLE points (a point);
CREATE INDEX i_points ON points using gist (a);
INSERT INTO points SELECT point(random(), random()) FROM
generate_series(1,100);
and running "checkpoint; reindex index i_points;" a few times with and
without the patch. The patch reduced the runtime from about 17.5 s to
15.5 s. oprofile confirms that the time spent in gist_box_penalty() and
rt_box_union() is reduced significantly.
This is all without the fast GiST index build patch, so this is
worthwhile on its own. If penalty function is called more, then this
becomes even more significant.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
*** a/src/backend/access/gist/gistproc.c
--- b/src/backend/access/gist/gistproc.c
***
*** 23,29
static bool gist_box_leaf_consistent(BOX *key, BOX *query,
StrategyNumber strategy);
! static double size_box(Datum dbox);
static bool rtree_internal_consistent(BOX *key, BOX *query,
StrategyNumber strategy);
--- 23,29
static bool gist_box_leaf_consistent(BOX *key, BOX *query,
StrategyNumber strategy);
! static double size_box(BOX *box);
static bool rtree_internal_consistent(BOX *key, BOX *query,
StrategyNumber strategy);
***
*** 32,63 static bool rtree_internal_consistent(BOX *key, BOX *query,
* Box ops
**/
! static Datum
! rt_box_union(PG_FUNCTION_ARGS)
{
- BOX *a = PG_GETARG_BOX_P(0);
- BOX *b = PG_GETARG_BOX_P(1);
- BOX *n;
-
- n = (BOX *) palloc(sizeof(BOX));
-
n->high.x = Max(a->high.x, b->high.x);
n->high.y = Max(a->high.y, b->high.y);
n->low.x = Min(a->low.x, b->low.x);
n->low.y = Min(a->low.y, b->low.y);
-
- PG_RETURN_BOX_P(n);
}
! static Datum
! rt_box_inter(PG_FUNCTION_ARGS)
{
- BOX *a = PG_GETARG_BOX_P(0);
- BOX *b = PG_GETARG_BOX_P(1);
- BOX *n;
-
- n = (BOX *) palloc(sizeof(BOX));
-
n->high.x = Min(a->high.x, b->high.x);
n->high.y = Min(a->high.y, b->high.y);
n->low.x = Max(a->low.x, b->low.x);
--- 32,56
* Box ops
**/
! /*
! * Calculates union of two boxes, a and b. The result is stored in *n.
! */
! static void
! rt_box_union(BOX *n, BOX *a, BOX *b)
{
n->high.x = Max(a->high.x, b->high.x);
n->high.y = Max(a->high.y, b->high.y);
n->low.x = Min(a->low.x, b->low.x);
n->low.y = Min(a->low.y, b->low.y);
}
! /*
! * Calculates intersection of two boxes, a and b. The result is stored in *n.
! * Returns false if the boxes don't intersect;
! */
! static bool
! rt_box_inter(BOX *n, BOX *a, BOX *b)
{
n->high.x = Min(a->high.x, b->high.x);
n->high.y = Min(a->high.y, b->high.y);
n->low.x = Max(a->low.x, b->low.x);
***
*** 65,76 rt_box_inter(PG_FUNCTION_ARGS)
if (n->high.x < n->low.x || n->high.y < n->low.y)
{
! pfree(n);
! /* Indicate "no intersection" by returning NULL pointer */
! n = NULL;
}
!
! PG_RETURN_BOX_P(n);
}
/*
--- 58,67
if (n->high.x < n->low.x || n->high.y < n->low.y)
{
! /* Indicate "no intersection" by returning false */
! return false;
}
! return true;
}
/*
***
*** 187,196 gist_box_penalty(PG_FUNCTION_ARGS)
GISTENTRY *origentry = (GISTENTRY *) PG_GETARG_POINTER(0);
GISTENTRY *newentry = (GISTENTRY *) PG_GETARG_POINTER(1);
float *result = (float *) PG_GETARG_POINTER(2);
! Datum ud;
! ud = DirectFunctionCall2(rt_box_union, origentry->key, newentry->key);
! *result = (float) (size_box(ud) - size_box(origentry->key));
PG_RETURN_POINTER(result);
}
--- 178,189
GISTENTRY *origentry = (GISTENTRY *) PG_GETARG_POINTER(0);
GISTENTRY *newentry = (GISTENTRY *) PG_GETARG_POINTER(1);
float *result = (float *) PG_GETARG_POINTER(2);
! BOX *origbox = DatumGetBoxP(origentry->key);
! BOX *newbox = DatumGetBoxP(newentry->key);
! BOX unionbox;
! rt_box_union(&unionbox, origbox, newbox);
! *result = (float) (size_box(&unionbox) - size_b
Re: [HACKERS] WIP: Fast GiST index build
I've added information about testing on some real-life dataset to wiki page. This dataset have a speciality: data is ordered inside it. In this case tradeoff was inverse in comparison with expectations about "fast build" algrorithm. Index built is longer but index quality is significantly better. I think high speed of regular index built is because sequential inserts are into near tree parts. That's why number of actual page reads and writes is low. The difference in tree quality I can't *convincingly explain now.* I've also maked tests with shuffled data of this dataset. In this case results was similar to random generated data. -- With best regards, Alexander Korotkov.
Re: [HACKERS] Range Types and length function
On Jun27, 2011, at 03:12 , Jeff Davis wrote:
> But I think you're right, it shouldn't be the responsibility of range
> types. Perhaps I should leave length() as some inlinable SQL functions
> like I mentioned, or perhaps I should remove them completely.
Does the current definition of length(range), i.e.
upper(range) - lower(range)
deal correctly with open vs. closed ranges and unbounded ranges? I'm thinking
that it probably doesn't - what would be the results of
length('[0,1]'::intrange) -- Should be 2
length('[0,1)'::intrange) -- Should be 1
length('[0,inf]'::intrange) -- Should be infinity, but ints can't
represent that, can't they?
If it cannot be easily made to support these cases, than I vote for
removing it all together.
best regards,
Florian Pflug
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Range Types, constructors, and the type system
On Jun27, 2011, at 02:48 , Jeff Davis wrote: > On Mon, 2011-06-27 at 00:56 +0200, Florian Pflug wrote: >> Well, there actually *is* some precedence for that kind of top-down >> (form a syntactic perspective) type inference. We *enforce* the cast >> in >> array[]:: >> and actually for a very similar reason - without the case, there's no >> way of knowing which type of empty array was meant. I think we also > > That's a good point. > > Although, I'm not sure whether that's an argument that we can make the > type system work as-is, or if it means that we should add syntax like > ARRAY[]. It was meant as an argument for the former, i.e. for extending the type system (or rather the function call syntax, as I argue below). >> special-case >> 'literal':: >> to use the input function of type directly, instead of first creating >> a text value and later casting it to . > > That is certainly true. Quoted strings never start out as text, they > start out as "unknown" and wait for the type inference to determine the > type. I'm not entirely sure whether a quoted string followed by a cast > is briefly unknown and then cast, or if it's directly interpreted using > the cast's type input function. It's at least labelled with type "unknown" for a while AFAIK. > I don't know if that's a good example though because it's near the end > of the line and there's no function call in between the arguments and > the cast. It might get more complex with cases like: > > range(lower(range(1,2)),upper(range(1,2)))::int8range > > but maybe that can be done more easily than I think? I wouldn't take it that far. What I had in mind was to *only* support the case where the cast directly follows the function call, i.e. the case f(...)::type I view this more as an extension of the function call syntax than of type inference. In other languages with polymorphism, there usually is an explicit syntactic construct for specifying the type arguments to a polymorphic function. For example, C++ you'd write make_range(3,4) to call the polymorphic function make_range() with it's (first) type argument set to "int". I think of f(...)::type as essentially the same thing, but re-using already existing syntax instead of inventing new one. I just checked - we currently special case "array[]::type" in transformExpr() by detecting the case of an array expression being the immediate child of a cast expression. I suggest we do the same for "f(...)::type", i.e. also special case a function call being the immediate child of a cast expression and pass down the forced result type to the function call node. Function call nodes would then usually ignore that passed-down result type, except in the case of a polymorphic functions whose argument types don't uniquely define its result type. But I haven't tried doing that, so there might be stumbling block down that road that I missed... best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] silent_mode and LINUX_OOM_ADJ
Hi Heikki, On Mon, 27 Jun 2011 at 12:10, Heikki Linnakangas wrote: Max, you're the maintainer of the PostgreSQL SuSE RPMs, right? my first name is Reinhard, but aside from that, you are right. ;) Can you comment on the above? I enabled it many years ago when (IIRC) it was needed in conjunction with "logging_collector = on" to get proper logging and especially log rotation. It might very well be that it is not needed anymore and suggestions for doing it better are welcome. cu Reinhard -- SUSE LINUX Products GmbH, Maxfeldstraße 5, 90409 Nürnberg, Germany GF: Jeff Hawn, Jennifer Guild, Felix Imendörffer, HRB 16746 (AG Nürnberg) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] silent_mode and LINUX_OOM_ADJ
On 27.06.2011 10:23, Magnus Hagander wrote: On Fri, Jun 24, 2011 at 16:37, Alvaro Herrera wrote: Excerpts from Heikki Linnakangas's message of vie jun 24 07:01:57 -0400 2011: While reviewing Peter Geoghegan's postmaster death patch, I noticed that if you turn on silent_mode, the LINUX_OOM_ADJ code in fork_process() runs when postmaster forks itself into background. That re-enables the OOM killer in postmaster, if you've disabled it in the startup script by adjusting /proc/self/oom_adj. That seems like a bug, albeit a pretty minor one. This may be a dumb question, but what is the purpose of silent_mode? Can't you just use nohup? I think silent_mode is an artifact from when our daemon handling in general was a lot more primitive (I bet there wasn't even pg_ctl then). Maybe we could discuss removing it altogether. If I'm not entirely mistaken, it's on by default in SuSE RPMs. I don't have a box with access right now, but I've come across it a couple of times recently with clients, and I think that's how it is. Might want to doublecheck with the suse maintainer if there's a particular reason they do that... Yep, seems to be so. Max, you're the maintainer of the PostgreSQL SuSE RPMs, right? Can you comment on the above? -- 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] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 6:22 PM, Tom Lane wrote: > Robert Haas writes: >> On Thu, Jun 16, 2011 at 6:54 PM, Tom Lane wrote: >>> I believe that this is fundamentally unavoidable so long as we use >>> SnapshotNow to read catalogs --- which is something we've talked about >>> changing, but it will require a pretty major R&D effort to make it >>> happen. > >> Ouch. > >> I wonder if we could avoid this anomaly by taking a throwaway MVCC >> snapshot at the beginning of each system catalog scan and using it >> just for the duration of that scan. If nothing that has touched the >> catalog commits while the scan is open, then this is logically >> equivalent to SnapshotNow. If something does commit in mid-scan, then >> we might not get the latest version of the row, but we should end up >> with exactly one. If it's not the latest one, we'll do the rebuild >> again upon seeing the next sinval message; in the meantime, the >> version we're using mustn't be too intolerably bad or it was an error >> not to use AccessExclusiveLock in the first place. > > Yeah, this seems like a possibly workable direction to explore. I like > this better than what Simon is proposing, because it would fix the > generic issue for all types of catalog SnapshotNow scans. > >> IIUC, the problem with this approach is not correctness but >> performance. Taking snapshots is (currently) expensive. > > Yeah. After mulling it for awhile, what about this idea: we could > redefine SnapshotNow as a snapshot type that includes a list of > transactions-in-progress, somewhat like an MVCC snapshot, but we don't > fill that list from the PGPROC array. Instead, while running a scan > with SnapshotNow, anytime we determine that a particular XID is > still-in-progress, we add that XID to the snapshot's list. > Subsequently, the SnapshotNow code assumes that XID to be > still-in-progress without consulting its actual state. We reset the XID > list to empty when starting a new SnapshotNow scan. (We might be able > to do so less often than that, like only when we do > AcceptInvalidationMessages, but it's not clear to me that there's any > real benefit in hanging onto the state longer.) > > This costs no performance; if anything it should be faster than now, > because we'll be replacing expensive transaction state probes with > relatively-cheap searches of an XID array that should almost always > be quite short. > > With this approach, we would have no serialization anomalies from single > transactions committing while a scan is in progress. There could be > anomalies resulting from considering an earlier XID to be in-progress > while a later XID is considered committed (because we didn't observe > it until later). So far as I can see offhand, the impact of that would > be that there might be multiple versions of a tuple that are considered > good, but never that there would be no version considered good (so long > as the other XIDs simply updated the tuple and didn't delete it). I > think this would be all right, since the scan would just seize on the > first good version it finds. As you argue above, if that's not good > enough for our purposes then the updater(s) should have taken a stronger > lock. I liked this idea, so began to prototype the code. My rough hack is attached, for the record. One thing that occurs to me about this is that SnapshotNow with or without these changes returns the latest committed row and ignores in-progress changes. Accepting an older version of the definition will always be potentially dangerous. I can't see a way of doing this that doesn't require locking - for changes such as new constraints we need to wait until in progress changes are complete. So maybe this idea is worth doing, but I don't think it helps us much reduce lock levels for DDL. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services snapshotnow_consistent.v1.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another issue with invalid XML values
On Mon, Jun 27, 2011 at 12:45:02AM +0200, Florian Pflug wrote: > Updated patch attached. Do you think this is "Ready for Committer"? Thanks. Yes; I have just marked it that way. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 8:45 PM, Tom Lane wrote: > Robert Haas writes: >> Department of second thoughts: I think I see a problem. > > Um, yeah, so that doesn't really work any better than my idea. > > On further reflection, there's a problem at a higher level than this > anyway. Even if we can get a single SnapshotNow scan to produce > guaranteed-self-consistent results, that doesn't ensure consistency > between the results of scans occurring serially. An example here is > ALTER COLUMN DROP DEFAULT, which is currently imagined to impact only > writers. However, suppose that a concurrent relcache load fetches the > pg_attribute row, notes that it has atthasdef = true, and then the ALTER > commits before we start to scan pg_attrdef. The consistency checks in > AttrDefaultFetch() will complain about a missing pg_attrdef entry, and > rightly so. We could lobotomize those checks, but it doesn't feel right > to do so; and anyway there may be other cases that are harder to kluge up. Locking the whole definition is at least one way of solving this problem. My locking fix does that. > So really we need consistency across *at least* one entire relcache load > cycle. We could maybe arrange to take an MVCC snap (or some lighter > weight version of that) at the start, and use that for all the resulting > scans, but I think that would be notationally messy. It's not clear > that it'd solve everything anyhow. There are parts of a relcache entry > that we fetch only on-demand, so they are typically loaded later than > the core items, and probably couldn't use the same snapshot. Worse, > there are lots of places where we assume that use of catcache entries or > direct examination of the catalogs will yield results consistent with > the relcache. > > I suspect these latter problems will impact Simon's idea as well. I think you're probably right, or at least, the suspicion is not something I can address quickly enough to be safe. I will revert to the AccessExclusiveLocks. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] silent_mode and LINUX_OOM_ADJ
On Fri, Jun 24, 2011 at 16:37, Alvaro Herrera wrote: > Excerpts from Heikki Linnakangas's message of vie jun 24 07:01:57 -0400 2011: >> While reviewing Peter Geoghegan's postmaster death patch, I noticed that >> if you turn on silent_mode, the LINUX_OOM_ADJ code in fork_process() >> runs when postmaster forks itself into background. That re-enables the >> OOM killer in postmaster, if you've disabled it in the startup script by >> adjusting /proc/self/oom_adj. That seems like a bug, albeit a pretty >> minor one. >> >> This may be a dumb question, but what is the purpose of silent_mode? >> Can't you just use nohup? > > I think silent_mode is an artifact from when our daemon handling in > general was a lot more primitive (I bet there wasn't even pg_ctl then). > Maybe we could discuss removing it altogether. If I'm not entirely mistaken, it's on by default in SuSE RPMs. I don't have a box with access right now, but I've come across it a couple of times recently with clients, and I think that's how it is. Might want to doublecheck with the suse maintainer if there's a particular reason they do that... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: starting to review the Extend NOT NULL representation to pg_constraint patch
On 27 June 2011 03:31, Robert Haas wrote: > On Sat, Jun 25, 2011 at 2:15 AM, Dean Rasheed > wrote: >> Really? I would expect the reverse, namely that the not-nullness is >> part of the PK constraint and dropping the PK *would* then start >> allowing NULLs. > > Hmm, OK. I had assumed we were only trying to fix the problem that > parent and child inheritance tables could get out of step, but maybe > you're right. > > If we go with that approach, then consider: > > CREATE TABLE foo (a int); > CREATE TABLE bar () INHERITS (foo);> > Now if someone adds a primary key foo (a), what happens currently is > that foo.a becomes NOT NULL, but bar.a still allows NULLs. Should > that remain true (on the theory that a primary key constraint is not > inherited) or become false (on the theory that parent and child tables > should match)? > I'm not sure, but my real problem with the current behaviour is its inconsistency. Consider this case: CREATE TABLE foo (a int PRIMARY KEY); CREATE TABLE bar () INHERITS (foo); Currently this results in bar not allowing NULLs, which is inconsistent with adding the PK after defining the inheritance. Then if the PK is dropped, the non-nullness is left behind on both foo and bar. I would summarise the consistency requirements as: 1). ADD CONSTRAINT should leave both parent and child tables in the same state as they would have been if the constraint had been defined at table creation time. 2). DROP CONSTRAINT should leave both parent and child tables in the same state as if the constraint had never existed (completely reversing the effects of ADD CONSTRAINT). I don't have a strong opinion as to whether or not the NOT NULL part of a PK should be inherited, provided that it is consistent with the above. I guess that if I were forced to choose, I would say that the NOT NULL part of a PK should not be inherited, since I do think of it as part of the PK, and PKs are not inherited. But I wouldn't be too upset if it were inherited (consistently!) and I can't think of a use case where that would be a problem. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
