Re: [HACKERS] [NOVICE] Last ID Problem
Tom Lane Writes: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote: > >> His point stands though: if you are accessing Postgres > through some > >> kind of connection-pooling software, currval() cannot be trusted > >> across transaction boundaries, since the pool code might give your > >> connection to someone else. In this situation the > >> nextval-before-insert paradigm is the only way. > > > I don't disagree with that; if the thread mentioned > connection pooling > > then I must have overlooked it. > > >> (But in most of the applications I can think of, your uses > of currval > >> subsequent to an INSERT ought to be in the same transaction as the > >> insert, so are perfectly safe. If your connection pooler takes > >> control away from you within a transaction block, you need a less > >> broken > >> pooler...) > > > That's the common situation I was talking about: doing an > INSERT and > > immediately calling currval(), presumably in the same transaction. > > I should have been more clear about that and warned what > could happen > > in other situations. Thanks. > > Apropos to all this: Tatsuo recently proposed a RESET > CONNECTION command that could be used to reset a connection > between pooling assignments, so as to be sure that different > pooled threads wouldn't see state that changes depending on > what some other thread did. It seems like RESET CONNECTION > ought to reset all currval() states to the "error, currval > not called yet" condition. Comments? I have a suggestion... For libpq: Since OID's are now deprecated, and will eventually disappear, wouldn't it be a good idea, to have INSERT and UPDATE return a copy of the tuple that was inserted/updated? This way, you could have a funtion to fetch an arbitrary named column from that tuple. Like: last_insert_value(tuple,'column_name') ... John ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [NOVICE] Last ID Problem
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Tue, Feb 01, 2005 at 12:56:20AM -0500, Tom Lane wrote: >> His point stands though: if you are accessing Postgres through some kind >> of connection-pooling software, currval() cannot be trusted across >> transaction boundaries, since the pool code might give your connection >> to someone else. In this situation the nextval-before-insert paradigm >> is the only way. > I don't disagree with that; if the thread mentioned connection > pooling then I must have overlooked it. >> (But in most of the applications I can think of, your uses of currval >> subsequent to an INSERT ought to be in the same transaction as the >> insert, so are perfectly safe. If your connection pooler takes control >> away from you within a transaction block, you need a less broken >> pooler...) > That's the common situation I was talking about: doing an INSERT > and immediately calling currval(), presumably in the same transaction. > I should have been more clear about that and warned what could > happen in other situations. Thanks. Apropos to all this: Tatsuo recently proposed a RESET CONNECTION command that could be used to reset a connection between pooling assignments, so as to be sure that different pooled threads wouldn't see state that changes depending on what some other thread did. It seems like RESET CONNECTION ought to reset all currval() states to the "error, currval not called yet" condition. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Some things I like to pick from the TODO list ...
Andreas Pflug wrote: > Merlin Moncure wrote: > >>>3) Allow GRANT/REVOKE permissions to be applied to all schema > > > > objects > > > >>>with one > >> > >>Maybe this is "apply schema changes to several objects with one > >>command". This seems reasonable. > > > > > > Well, I don't know. IMO, what I would really like to see is for various > > database objects to inherit permissions from the schema. The ability to > > GRANT to all tables in a schema, etc. is almost trivial to implement in > > plpgsql/dynamic sql currently. > > And pgAdmin3 V1.2 has the Grant Wizard, as pgAdmin2 had. Though an SQL > command for granting on multiple objects won't hurt. > > > However the major annoyance with that > > approach is that I have to remember to re-grant permissions to tables in > > a schema after running that function. As worded, the TODO does not > > solve that problem. > > > > IOW, I would really like to see the TODO to be reworded 'allow schema > > objects to inherit permissions from the schema' or something like > > this... > > This sounds quite helpful too, but I'd call it a second TODO. Added to TODO: * Allow GRANT/REVOKE permissions to be inherited by objects based on schema permissions -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Proposed TODO: fetch->INT8
Merlin Moncure wrote: > Jeff wrote: > > Is there a practical use for retrieving > 2^31 records at once? > > > > (this is a serious question, I'm not arguing that it should cause a > > syntax error) > > > > Regards, > > Jeff Davis > > > > On Mon, 2005-01-24 at 14:13 -0500, Merlin Moncure wrote: > > > I was browsing the TODO list and I noticed the todo to bump > limit/offset > > > to int8. IMO, the flavors of fetch that take a numeric parameter > need > > > this as well. > > > > > > FWIW, trying to pass integer > 2^31 to fetch gives a syntax error, > which > > > is clearly wrong. > > No, but don't forget about relative positioning: > fetch relative c from huge_cursor; TODO updated: * Change LIMIT/OFFSET and FETCH/MOVE to use int8 -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
when my cidr datatype was integrated into pgsql, the decision was made to incorporate a copy of bind's inet_net_pton.c rather than add a link-time dependence to libbind.a (libbind.so). thus, when this bug was fixed in 2003: revision 1.14 date: 2003/08/20 02:21:08; author: marka; state: Exp; lines: +10 -4 1580. [bug] inet_net_pton() didn't fully handle implicit multicast IPv4 network addresses. the pgsql "fork" of this code did not benefit from the fix. the patch was: Index: inet_net_pton.c === RCS file: /proj/cvs/prod/bind8/src/lib/inet/inet_net_pton.c,v retrieving revision 1.13 retrieving revision 1.14 diff -u -r1.13 -r1.14 --- inet_net_pton.c 27 Sep 2001 15:08:38 - 1.13 +++ inet_net_pton.c 20 Aug 2003 02:21:08 - 1.14 @@ -16,7 +16,7 @@ */ #if defined(LIBC_SCCS) && !defined(lint) -static const char rcsid[] = "$Id: inet_net_pton.c,v 1.13 2001/09/27 15:08:38 marka Exp $"; +static const char rcsid[] = "$Id: inet_net_pton.c,v 1.14 2003/08/20 02:21:08 marka Exp $"; #endif #include "port_before.h" @@ -59,7 +59,7 @@ * Paul Vixie (ISC), June 1996 */ static int -inet_net_pton_ipv4( const char *src, u_char *dst, size_t size) { +inet_net_pton_ipv4(const char *src, u_char *dst, size_t size) { static const char xdigits[] = "0123456789abcdef"; static const char digits[] = "0123456789"; int n, ch, tmp = 0, dirty, bits; @@ -152,7 +152,7 @@ if (*odst >= 240) /* Class E */ bits = 32; else if (*odst >= 224) /* Class D */ - bits = 4; + bits = 8; else if (*odst >= 192) /* Class C */ bits = 24; else if (*odst >= 128) /* Class B */ @@ -160,8 +160,14 @@ else/* Class A */ bits = 8; /* If imputed mask is narrower than specified octets, widen. */ - if (bits >= 8 && bits < ((dst - odst) * 8)) + if (bits < ((dst - odst) * 8)) bits = (dst - odst) * 8; + /* +* If there are no additional bits specified for a class D +* address adjust bits to 4. +*/ + if (bits == 8 && *odst == 224) + bits = 4; } /* Extend network to cover the actual mask. */ while (bits > ((dst - odst) * 8)) { re: > To: Steve Atkins <[EMAIL PROTECTED]> > Cc: pgsql-hackers , [EMAIL PROTECTED] > Subject: Re: [HACKERS] [BUGS] Bug in create operator and/or initdb > Comments: In-reply-to Steve Atkins <[EMAIL PROTECTED]> > message dated "Mon, 31 Jan 2005 07:23:05 -0800" > Date: Mon, 31 Jan 2005 12:16:26 -0500 > From: Tom Lane <[EMAIL PROTECTED]> > > Steve Atkins <[EMAIL PROTECTED]> writes: > > The cidr type, including it's external interface, is simply broken. > > That is a large claim that I don't think you have demonstrated. > The only one of your examples that seems to me to contradict the > documentation is this one: > > steve=# select '224.0.0.0'::cidr; > cidr > - >224.0.0.0/4 > > which should be /32 according to what the docs say: > > : If y is omitted, it is calculated using assumptions from the older > : classful network numbering system, except that it will be at least large > : enough to include all of the octets written in the input. > > The bogus netmask is in turn responsible for this case: > > steve=# select '224.10.0.0'::cidr; > ERROR: invalid cidr value: "224.10.0.0" > DETAIL: Value has bits set to right of mask. > > > Looking at the source code, there seems to be a special case for "class D" > network numbers that causes the code not to extend y to cover the > supplied inputs: > > /* If no CIDR spec was given, infer width from net class. */ > if (bits == -1) > { > if (*odst >= 240)/* Class E */ > bits = 32; > else if (*odst >= 224)/* Class D */ > bits = 4; > else if (*odst >= 192)/* Class C */ > bits = 24; > else if (*odst >= 128)/* Class B */ > bits = 16; > else /* Class A */ > bits = 8; > /* If imputed mask is narrower than specified octets, widen. */ > if (bits >= 8 && bits < ((dst - odst) * 8)) > ^ > bits = (dst - odst) * 8; > } > > I think the test for "bits >= 8" should be removed. Does anyone know > why it's there? > > regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] win32. Please help me to compile PostGre
I set up MinGW and msys distributive. Also i did download CVS source of PostGre. What command should i do next to compile sources. Please help, never worked with MinGW. thank in advance. Dmitry Konnov ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
> We didn't really want to assume that all platforms are using libbind :-( i think you could have, at the time, since windows wasn't even a gleam in pgsql's eye. even now, libbind would be a dependable universal dependency, since we publish windows binaries. > > the pgsql "fork" of this code did not benefit from the fix. the patch was: > > Ah-hah. Many thanks for supplying the patch --- will integrate it. i have two suggestions. first, look at the rest of the current source file, in case there are other fixes. second, track changes this source file during your release engineering process for each new pgsql version. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] FunctionCallN improvement.
Neil Conway <[EMAIL PROTECTED]> writes: > On Mon, 2005-01-31 at 23:38 +0900, a_ogawa wrote: >> (b)Define the macro that initialize FunctionCallInfoData, and use it >> instead of MemSet in all FunctionCallN, DirectFunctionCallN, >> OidFunctionCallN. >> This macro is the following. >> >> #define InitFunctionCallInfoData(Fcinfo, Flinfo, Nargs) \ >> do {\ >> (Fcinfo)->flinfo = Flinfo; \ >> (Fcinfo)->context = NULL; \ >> (Fcinfo)->resultinfo = NULL;\ >> (Fcinfo)->isnull = false; \ >> (Fcinfo)->nargs = Nargs;\ >> MemSet((Fcinfo)->argnull, 0, Nargs * sizeof(bool)); \ >> } while(0) >> >> I think that plan(b) is better, because source code consistency >> and efficiency improve. > I agree; I think the macro is a nice improvement to readability. But a dead loss for performance, since it does a MemSet *and* some other operations. What's worse, it changes a word-aligned MemSet into a non-aligned one, knocking out all the optimizations therein. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema
Merlin, Tom: > A table or function privilege, if it exists, will override anything for > the table. This will be faster (FWIW) than a multiple table grant > because it's just setting one permission at the schema level. Someone > else will have to comment on how effectively this will work with > existing implementation, however. The problem with this approach is it leaves us with no way to REVOKE permissions on a specific table from a user who has permissions on the SCHEMA. Our permissions model is completely additive, so if you did: GRANT SELECT ON SCHEMA public TO phpuser; then REVOKE SELECT ON TABLE user_passwords FROM phpuser; ... would have no real effect. At the very least, we'd have to code a warning to the effect of: "WARNING: user phpaccess has permissions on the schema level which override the current statement." And overall, I'd think it would make the feature a *lot* less useful; basically it would encourage a lot of DBAs to organize their schemas by security level, which is not really what schemas are for. > This does seem conceptually cleaner than GRANT ON NEW TABLES, which to > me has a flavor of action-at-a-distance about it. Does anyone see any > cases where it's really important to have the distinction between acting > on existing tables and acting on future tables? Databases which are already in production. I suggested it, of course, because I would utilize the distinction if it was available. I don't know about other users. For example, I have one content-serving database for a website which already has a complex set of permissions in place (some of the content is confidential company information, available only to officers of that company). I'd like to, by default, have each new VIEW available to the phpwebuser, because that's why I create views in the first place, 95% of the time. However, I don't want to automatically grant permissions on all existing views to that user in order to get the new default. The analogue here is file permissions vs. umask for unix directories. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FunctionCallN improvement.
On Mon, 2005-01-31 at 23:38 +0900, a_ogawa wrote: > (b)Define the macro that initialize FunctionCallInfoData, and use it > instead of MemSet in all FunctionCallN, DirectFunctionCallN, > OidFunctionCallN. > This macro is the following. > > #define InitFunctionCallInfoData(Fcinfo, Flinfo, Nargs) \ > do {\ > (Fcinfo)->flinfo = Flinfo; \ > (Fcinfo)->context = NULL; \ > (Fcinfo)->resultinfo = NULL;\ > (Fcinfo)->isnull = false; \ > (Fcinfo)->nargs = Nargs;\ > MemSet((Fcinfo)->argnull, 0, Nargs * sizeof(bool)); \ > } while(0) > > I think that plan(b) is better, because source code consistency > and efficiency improve. I agree; I think the macro is a nice improvement to readability. It would be good to see some benchmarks once the patch is written to verify that this really does improve performance, but I think it's a good idea. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
Paul Vixie <[EMAIL PROTECTED]> writes: > i have two suggestions. first, look at the rest of the current source file, > in case there are other fixes. Right, I already grabbed the latest. > second, track changes this source file during > your release engineering process for each new pgsql version. Bruce, do you think this is worth adding to RELEASE_CHANGES? inet_net_ntop.c and inet_net_pton.c are both extracted from the BIND distribution. But they're hardly the only files we took from elsewhere. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ...
> -Original Message- > From: Marc G. Fournier [mailto:[EMAIL PROTECTED] > Sent: 31 January 2005 22:35 > To: Tom Lane > Cc: Dave Page; pgsql-hackers@postgresql.org; David Fetter > Subject: Re: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ... > > On Mon, 31 Jan 2005, Tom Lane wrote: > > > "Dave Page" writes: > >> /pub/src/v8.0.1 is missing :-( > > > > Why do we even have individual symlinks in pub/src? Seems to me we > > could replace the whole src directory with a symlink to source, and > > eliminate one bit of release bookkeeping ... > > the symlinks wre to make Peter happy ... he didn't like the > 'v' in the > directory name, so we make the src directory that has > 'non-v'd symlinks :) > > And yes, that is the *only* reason there is a src directory ... Urgh. No offence to Peter, but I think it's a helluva lot messier having all those symlinks. Not to mention the binary/ dir which doesn't have a non-v'ified bin/X.X.X equivalent. Can we lose /src and just keep /source and /binary (personally I don't care whether or not we keep the v's on the subdirs)? It probably causes a lot of bandwidth to be wasted as well - I recently found a mirror site that was mirroring the entire ftp ste using about 20 simultaneous ftp connections. I'll bet that didn't copy the symlinks as symlinks and instead grabbed all the files again, each time it tried to 'sync'. I barred that particular site in the ftpd conf, but there could be others of course. Regards, Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ...
On Mon, 31 Jan 2005, Tom Lane wrote: "Dave Page" writes: /pub/src/v8.0.1 is missing :-( Why do we even have individual symlinks in pub/src? Seems to me we could replace the whole src directory with a symlink to source, and eliminate one bit of release bookkeeping ... the symlinks wre to make Peter happy ... he didn't like the 'v' in the directory name, so we make the src directory that has 'non-v'd symlinks :) And yes, that is the *only* reason there is a src directory ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ...
fixed On Mon, 31 Jan 2005, Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier Sent: 31 January 2005 18:30 To: pgsql-hackers@postgresql.org Cc: David Fetter Subject: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ... Please review them to make sure they look already ... Dave, I've changed the symlink's as well ... /pub/src/v8.0.1 is missing :-( Regards, Dave. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] New 7.2.7 Bundle Up
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Please check this one over ... Tom foudn some issues with the docs build > that should be fixed ... Seems clean now. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ...
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: 31 January 2005 22:24 > To: Dave Page > Cc: Marc G. Fournier; pgsql-hackers@postgresql.org; David Fetter > Subject: Re: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ... > > "Dave Page" writes: > > /pub/src/v8.0.1 is missing :-( > > Why do we even have individual symlinks in pub/src? Seems to me we > could replace the whole src directory with a symlink to source, and > eliminate one bit of release bookkeeping ... For some reason we have /pub/source/vX.X.X/ And /pub/src/X.X.X Note the missing v on the second. I dunno why we have both - did some bad URLs get advertised at some point? If that's the case, perhaps we could get rid of one set now. All the v8 announcements pointed to the websites ftp browser, so I doubt anyone is likely to miss one set of symlinks. Regards,Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ...
"Dave Page" writes: > /pub/src/v8.0.1 is missing :-( Why do we even have individual symlinks in pub/src? Seems to me we could replace the whole src directory with a symlink to source, and eliminate one bit of release bookkeeping ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Our getopt_long() doesn't do abbreviations or NLS
I just noticed that our port/getopt_long.c substitute implementation does not accept abbreviated names for long options: $ pg_dump --username tgl regression ... works ... $ pg_dump --user tgl regression pg_dump: illegal option -- user Try "pg_dump --help" for more information. $ The GNU implementation of getopt_long is documented to allow this: The getopt_long() function works like getopt() except that it also accepts long options, started out by two dashes. Long option names may be abbreviated if the abbreviation is unique or is an exact match for some defined option. A long option may take a parameter, of the form --arg=param or --arg param. and experimentation confirms it works. Barring objections, I'm going to modify our version to allow unique abbreviations too. I also notice that our version isn't i18n-ready: if (opterr && optstring[0] != ':') fprintf(stderr, "%s: illegal option -- %s\n", argv[0], place); Should it be gettext'ified? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ...
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Marc > G. Fournier > Sent: 31 January 2005 18:30 > To: pgsql-hackers@postgresql.org > Cc: David Fetter > Subject: [HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ... > > > Please review them to make sure they look already ... > > Dave, I've changed the symlink's as well ... /pub/src/v8.0.1 is missing :-( Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
On Mon, Jan 31, 2005 at 12:16:26PM -0500, Tom Lane wrote: > Steve Atkins <[EMAIL PROTECTED]> writes: > > The cidr type, including it's external interface, is simply broken. > > That is a large claim that I don't think you have demonstrated. > The only one of your examples that seems to me to contradict the > documentation is this one: > > steve=# select '224.0.0.0'::cidr; > cidr > - >224.0.0.0/4 > > which should be /32 according to what the docs say: OK. If this sort of thing is considered a bug, rather than part of the external interface that shouldn't be changed, then I'd agree that cidr isn't entirely broken and it may well be possible to improve it without changing the interface. /me goes grovelling through the IPv6 inet code... Cheers, Steve ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Is the price of looking up a schema a deal breaker here, or is it > possible to avoid it? My guess is "no" as to both questions. I've never seen any profiles suggesting that permissions-checking is a significant part of query startup. In any case, if you assume that the same set of permissions are going to get checked either way (they're just distributed differently) then the only direct cost involved would be one additional syscache fetch, which surely ought not be significant. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] PostgreSQL at LinuxWorld Boston
[bcc to -hackers, -general] Folks, I'm happy to announce that PostgreSQL will have a home at LinuxWorld in two weeks after all. My company, OpenMFG, is teaming up with SRA America to wave the elephant flag at booth #1411 - right next door to the ".org Pavillion", two booths up from the Intel spread. OpenMFG is a powerful and feature-rich ERP system that's written with PostgreSQL on the server, and Qt on the client (binaries available for Linux, Windows, and Mac OS X). The business logic is all in pl/pgsql, and performance is fantastic and very scalable. We'll be handing out CDs with free demo versions of our ERP package, as well as PostgreSQL 8.0 source and binaries. The CD will also contain a beta version of our OpenRPT report writer and rendering engine, which we'll be releasing under an open source license at the end of the beta cycle. SRA America is the US arm of the established Japan-based systems integrator Software Research Associates. As most of you know, SRA has been an active supporter of the PostgreSQL project for many years, and they currently employ several active contributors to the project, including core developer Bruce Momjian. They provide a variety of PostgreSQL services, including consulting, migration, support, and training. So the general idea is that you get: a) a software product vendor who's built their entire product and business around PostgreSQL, and b) an international services firm who is leading the charge of PostgreSQL consulting and support in the enterprise. If you're going to be in Boston from Feb. 15 to 17, please stop by and say hi. Regards, Ned Lilly -- Ned Lilly President and CEO OpenMFG, LLC 420 North Center Drive Building 11, Suite 115 Norfolk, VA 23502 tel: 757-461-3022 mailto: [EMAIL PROTECTED] www.openmfg.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] New 7.2.7 Bundle Up
Please check this one over ... Tom foudn some issues with the docs build that should be fixed ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
> > GRANT SELECT ON ALL TABLES IN public TO phpuser; > > GRANT SELECT ON NEW TABLES IN public TO phpuser; > > > Really better than this? > > GRANT { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER > > | EXECUTE | CREATE | ALL [ PRIVILEGES ] }ON SCHEMA schemaname [, > > ...] > > The latter confuses privileges-for-a-schema with privileges-for-a-table. Right. > > This will be faster (FWIW) than a multiple table grant > > because it's just setting one permission at the schema level. > I think this argument is bogus, because the savings in time spent to do Of course. GRANT is not really performance sensitive, anyways. Is the price of looking up a schema a deal breaker here, or is it possible to avoid it? Merlin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Group-count estimation statistics
Manfred Koizar <[EMAIL PROTECTED]> writes: > That's not what I meant. I tried to say that if we have a GROUP BY > several columns and one of these columns alone has more than N/10 > distinct values, there's no way to get less than that many groups. Oh, I see, you want a "max" calculation in there too. Seems reasonable. Any objections? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
Tom Lane <[EMAIL PROTECTED]> writes: > steve=# select '224.0.0.0'::cidr; > cidr > - >224.0.0.0/4 > > which should be /32 according to what the docs say: 224-239 are multicast addresses. Making it /4 makes the entire multicast address space one network block which is about as reasonable an answer as anything else. > if (bits >= 8 && bits < ((dst - odst) * 8)) > ^ > bits = (dst - odst) * 8; > } > > I think the test for "bits >= 8" should be removed. Does anyone know > why it's there? I guess Vixie figured network blocks subdividing multicast address space weren't a sensible concept? It's a bit of a strange constraint to hard code into the C code though. Incidentally, how can that code possibly work? It treats odst as a pointer in some places but then calculates bits using arithmetic on it directly without dereferencing? -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Group-count estimation statistics
On Mon, 31 Jan 2005 11:20:31 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: >Already done that way. >if (relvarcount > 1) >clamp *= 0.1; That's not what I meant. I tried to say that if we have a GROUP BY several columns and one of these columns alone has more than N/10 distinct values, there's no way to get less than that many groups. Servus Manfred ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
Paul Vixie <[EMAIL PROTECTED]> writes: > when my cidr datatype was integrated into pgsql, the decision was made to > incorporate a copy of bind's inet_net_pton.c rather than add a link-time > dependence to libbind.a (libbind.so). We didn't really want to assume that all platforms are using libbind :-( > thus, when this bug was fixed in 2003: > > revision 1.14 > date: 2003/08/20 02:21:08; author: marka; state: Exp; lines: +10 -4 > 1580. [bug] inet_net_pton() didn't fully handle implicit > multicast IPv4 network addresses. > the pgsql "fork" of this code did not benefit from the fix. the patch was: Ah-hah. Many thanks for supplying the patch --- will integrate it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Is this: > GRANT SELECT ON ALL TABLES IN public TO phpuser; > GRANT SELECT ON NEW TABLES IN public TO phpuser; > Really better than this? > GRANT { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER > | EXECUTE | CREATE | ALL [ PRIVILEGES ] }ON SCHEMA schemaname [, > ...] The latter confuses privileges-for-a-schema with privileges-for-a-table. The proposal would fail completely if we had any similarly spelled privileges for both schemas and tables. Which we don't at the moment, but it would be foolish to assume that we never will --- especially when you consider extending this idea to non-table objects. If you want it to work that way (essentially, losing the distinction between ALL and NEW cases) then you could spell it like GRANT privileges ON TABLES IN schemas TO users; which is implementation-wise the same but avoids the assumption about non overlap of privilege types. This does seem conceptually cleaner than GRANT ON NEW TABLES, which to me has a flavor of action-at-a-distance about it. Does anyone see any cases where it's really important to have the distinction between acting on existing tables and acting on future tables? > This will be faster (FWIW) than a multiple table grant > because it's just setting one permission at the schema level. I think this argument is bogus, because the savings in time spent to do the GRANT will be eaten many times over by extra time spent to look in two places every time the privileges are checked. But it might be worth doing it this way anyway, because of the cleaner conceptual model. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Combining index scans
Hello again. This time I'd like to speak about in-memory bitmap to combine index scan results. I know, this code should use minimal amount of memory, so I really want to hear any possible pros and cons. Below, pseudocode is given. After running it, we'll have a list of CTID pointers, and one bitmap for each clause, that planner marked for index scan. Consider query with m clauses, each marked for index scan. Let: - j denotes number of the clause, 0 <= j < m, m is total number of clauses; - R total number of CTID returned by all calls to index scans so far (for any of the m clauses), i.e. CTIDs list cardinality; - p CTID position in the list, 0 <= p < R; - c CTID returned by index scan API. for (j = 0; j < m; j++) { if (no_bitmap_for_caluse(j)) { /* create bitmap of length R */ create_bitmap(j, R); for (i = 0; i < R; i++) /* set i-th bit of j-th bitmap to 0 */ set_bitmap_bit(j, i, 0); } c = get_ctid_from_index_scan_api(); /* search for CTID in the list */ p = search_ctid(c); /* * p will be either -1 if CTIDs not seen so far * OR it'll be in the range 0 <= p < R */ if (p == -1) { /* this will also increase R (total number of ctids) */ add_ctid_to_the_list(c); /* position of just-added CTID entry in list */ p = R; /* * update all bitmaps created so far, * setting bit for just-added entry to 0 */ for (i = 0; i < j; i++) { set_bitmap_bit(i, p, 0); } } /* update current bitmap, set bit p to 1 */ set_bitmap_bit(j, p, 1); } After that, one could do AND/OR of bitmaps and return the list of CTIDs that matches final results. For the case of 3 clauses, we could have such data in memoy: idx | CTID idx | clause1 | clause2 | clause3 1 | 12341 | 1 | 0 | 0 2 | 34562 | 0 | 1 | 0 3 | 56783 | 0 | 0 | 1 As you can see, there were 3 scans, each returned exactly 1 CTID. If there were at list one AND in the WHERE clause, then no rows will be returned. After inserting some more data to the table, we could have such more entry in our in-memory bitmaps: idx | CTID idx | clause1 | clause2 | clause3 ... ... 3 | 56783 | 0 | 1 | 1 So, in case of AND present in the WHERE clause, CTID 5678 would have been returned. That's it. Waiting for your feedback. -- Victor Y. Yegorov ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] weird behaviour on DISTINCT ON
Gaetano Mendola <[EMAIL PROTECTED]> writes: > my warning was due the fact that in the docs is written nowhere this > drawback. The SELECT reference page already says that the output rows are computed before applying ORDER BY or DISTINCT. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
Matthias wrote: > I think it is best to code the basic functionallity within the two new > commands, and see > how this works out. We can add your idea and others on top of it later > on. I think you should do whatever you think is most appropriate...discussion can of course continue after you have a workable patch...I'm just a pundit anyways... Just for your consideration though: Is this: GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW TABLES IN public TO phpuser; Really better than this? GRANT { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | EXECUTE | CREATE | ALL [ PRIVILEGES ] }ON SCHEMA schemaname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] A table or function privilege, if it exists, will override anything for the table. This will be faster (FWIW) than a multiple table grant because it's just setting one permission at the schema level. Someone else will have to comment on how effectively this will work with existing implementation, however. For example, granting 'select' to a schema (which currently is impossible) solves both the 'all'/'new' problem...it implicitly adds select privileges to all current tables and new ones...is there really any reason to distinguish between the two cases? This is simple and effective, IMO. Good luck, Merlin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] weird behaviour on DISTINCT ON
Greg Stark wrote: Gaetano Mendola <[EMAIL PROTECTED]> writes: now what do I see is that for each different x value the foo is executed more than once, I guess this is because the distinct filter out the rows after executing the query. Is this behaviour the normal one? Shall be not documented ? Usually DISTINCT ON is only really useful if you're sorting on something. Otherwise the choice of which record is output is completely arbitrary. So the above would typically be something like: SELECT DISTINCT ON (x), y, foo(x) ... ORDER BY x, y Now you can see why every record does need to be looked at to handle that. In fact the ORDER BY kicks in before output columns are generated so you can do things like: SELECT DISTINCT ON (x), y, foo(x) ... ORDER BY x, y, foo(x) And of course obviously foo() has to be executed for every record to do this. Postgres doesn't try to detect cases where it's safe to change the regular order in which things are done and delay execution of functions whose results aren't needed right away. You could just use SELECT x, foo(x) from (SELECT x ... GROUP BY x) I totally agree and this was clear after having seen what was happening, my warning was due the fact that in the docs is written nowhere this drawback. A novice, like I was 4 years ago ( I discover it in the code only today ), can burn it self. Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] weird behaviour on DISTINCT ON
Gaetano Mendola <[EMAIL PROTECTED]> writes: > now what do I see is that for each different x value > the foo is executed more than once, I guess this is because > the distinct filter out the rows after executing the query. > > Is this behaviour the normal one? Shall be not documented ? Usually DISTINCT ON is only really useful if you're sorting on something. Otherwise the choice of which record is output is completely arbitrary. So the above would typically be something like: SELECT DISTINCT ON (x), y, foo(x) ... ORDER BY x, y Now you can see why every record does need to be looked at to handle that. In fact the ORDER BY kicks in before output columns are generated so you can do things like: SELECT DISTINCT ON (x), y, foo(x) ... ORDER BY x, y, foo(x) And of course obviously foo() has to be executed for every record to do this. Postgres doesn't try to detect cases where it's safe to change the regular order in which things are done and delay execution of functions whose results aren't needed right away. You could just use SELECT x, foo(x) from (SELECT x ... GROUP BY x) -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] 7.2.7 -> 8.0.1 Bundles Ready ...
Please review them to make sure they look already ... Dave, I've changed the symlink's as well ... Will announce everything publicly around 4am GMT tonight (midnight my time) to give ~10hrs for the rest to propogate out ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-hackers] Patent issues and 8.1
Marc, > alot smoother today then it went yesterday ... and faster ... but, then > again, *most* clients use <256MB of storage, so moving their VM around > takes no time ... svr1 is @ ~13G :) Something like 3G is justin's mailbox > alone ... and i miscalculated how long it would take to move it back over > to neptune :( I doubt that's intentional, why don't you ask him to truncate it? I noticed that you used to grant @postgresql.org addresses as "unlimited", I changed the default to 5MB, which is what all the regional contacts now have. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
Hi Merlin, sorry - I replied to Tom & PG hackers before I saw you last post. I think it is best to code the basic functionallity within the two new commands, and see how this works out. We can add your idea and others on top of it later on. what about that? cheers, Matthias -- Matthias Schmidt Viehtriftstr. 49 67346 Speyer GERMANY Tel.: +49 6232 4867 Fax.: +49 6232 640089 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [pgsql-hackers] Patent issues and 8.1
On Mon, 31 Jan 2005, Josh Berkus wrote: Marc, And to be perfectly frank, I was mostly thinking of Marc when I said that. Sorry, that was uncharitable. I meant that (at the time) you were panicking. Wait, I've not panic'd about all of this at any point ... the only 'chicken little' comment I made had to do with everyone panicking about a patent that doesn't yet exist, and comparing that to "chicken little and his 'the sky is falling'" ... *scratch head* Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [pgsql-hackers] Patent issues and 8.1
On Mon, 31 Jan 2005, Josh Berkus wrote: Now you have something different to panic about. How goes the server shuffle? alot smoother today then it went yesterday ... and faster ... but, then again, *most* clients use <256MB of storage, so moving their VM around takes no time ... svr1 is @ ~13G :) Something like 3G is justin's mailbox alone ... and i miscalculated how long it would take to move it back over to neptune :( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-hackers] Allow GRANT/REVOKE permissions to be applied to all schema
Hi *, I will start implementing this stuff based on this syntax: GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW TABLES IN public TO phpuser; so there are two seperate commands to use. is everybody fine with this aproach? cheers, Matthias PS.: Tom, shouldn't we mention the fact, that one can grant on views and sequences as well in the documentation? Am 29.01.2005 um 01:43 schrieb Tom Lane: Josh Berkus writes: GRANT SELECT ON ALL, NEW TABLES IN public TO phpuser; ... does both. Ah, I overlooked that part of your message. I think the above probably doesn't work in bison, but if not we could spell it like GRANT SELECT ON ALL AND NEW TABLES IN public TO phpuser; Or just make the user enter two commands for this case. Aside from syntactic simplicity, that might be a good idea anyway. The NEW TABLES case is *fundamentally* different from every other form of GRANT, in that it causes future actions. So it might be a wise idea from the standpoint of understandability to keep it as a separate command from the immediate-acting ALL TABLES. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Matthias Schmidt Viehtriftstr. 49 67346 Speyer GERMANY Tel.: +49 6232 4867 Fax.: +49 6232 640089 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [pgsql-hackers] Patent issues and 8.1
Josh Berkus wrote: Guys, BTW, if you hadn't guessed, that comment was supposed to be off-list. Unfortunately, I discovered a bug with KMail and list management, the hard way ... Sigh.Just in case anyone wants to know, KMail 1.5.1 + has a bug where, if you have list management turned on, it sometimes sends stuff to the list instead of the To: line you see on the screen. Dammit! Any other Linux-friendly mail GUIs that have list management features and don't have this problem? Evolution does although I haven't tried it in a while. J -- Command Prompt, Inc., your source for PostgreSQL replication, professional support, programming, managed services, shared and dedicated hosting. Home of the Open Source Projects plPHP, plPerlNG, pgManage, and pgPHPtoolkit. Contact us now at: +1-503-667-4564 - http://www.commandprompt.com begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [pgsql-hackers] Patent issues and 8.1
Marc, > And to be perfectly frank, I was mostly thinking of Marc when I said that. Sorry, that was uncharitable. I meant that (at the time) you were panicking. Now you have something different to panic about. How goes the server shuffle? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [pgsql-hackers] Patent issues and 8.1
Andrew, > On Thu, Jan 27, 2005 at 10:39:52AM -0800, Josh Berkus wrote: > > Thanks. As you know, I'm getting a little sick of the chicken little > > act among many of the -hackers > > I think this is a little bit of a mischaracterisation. Afilias is > already a customer of IBM. BTW, if you hadn't guessed, that comment was supposed to be off-list. Unfortunately, I discovered a bug with KMail and list management, the hard way ... And to be perfectly frank, I was mostly thinking of Marc when I said that. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-hackers] Patent issues and 8.1
Guys, > BTW, if you hadn't guessed, that comment was supposed to be off-list. > Unfortunately, I discovered a bug with KMail and list management, the hard > way ... Sigh.Just in case anyone wants to know, KMail 1.5.1 + has a bug where, if you have list management turned on, it sometimes sends stuff to the list instead of the To: line you see on the screen. Dammit! Any other Linux-friendly mail GUIs that have list management features and don't have this problem? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
Steve Atkins <[EMAIL PROTECTED]> writes: > The cidr type, including it's external interface, is simply broken. That is a large claim that I don't think you have demonstrated. The only one of your examples that seems to me to contradict the documentation is this one: steve=# select '224.0.0.0'::cidr; cidr - 224.0.0.0/4 which should be /32 according to what the docs say: : If y is omitted, it is calculated using assumptions from the older : classful network numbering system, except that it will be at least large : enough to include all of the octets written in the input. The bogus netmask is in turn responsible for this case: steve=# select '224.10.0.0'::cidr; ERROR: invalid cidr value: "224.10.0.0" DETAIL: Value has bits set to right of mask. Looking at the source code, there seems to be a special case for "class D" network numbers that causes the code not to extend y to cover the supplied inputs: /* If no CIDR spec was given, infer width from net class. */ if (bits == -1) { if (*odst >= 240)/* Class E */ bits = 32; else if (*odst >= 224)/* Class D */ bits = 4; else if (*odst >= 192)/* Class C */ bits = 24; else if (*odst >= 128)/* Class B */ bits = 16; else /* Class A */ bits = 8; /* If imputed mask is narrower than specified octets, widen. */ if (bits >= 8 && bits < ((dst - odst) * 8)) ^ bits = (dst - odst) * 8; } I think the test for "bits >= 8" should be removed. Does anyone know why it's there? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Bundles running late ...
8.0.1 & 7.4.7 are currently both available for testing, and 7.3.9 is currently being built ... will pop a note once both 7.3. and 7.2 are also ready ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.3.8 under FC3 takes excessive semaphores?
"Mark Cave-Ayland" <[EMAIL PROTECTED]> writes: > I'm trying to install a copy of PostgreSQL 7.3.8 on FC3 x86_64, and having a > very strange problem with shared memory in that PostgreSQL seems to be > taking far too many semaphores for the parameters in the postgresql.conf > file. Judging by the symptoms, you have built a version with what we now call --disable-spinlocks; that is, it didn't figure out how to do assembly TAS on your platform and fell back to using SysV semaphores for spinlocks. Quite aside from the drain on semaphores, the performance is going to be spectacularly bad, so you'd better fix that. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Group-count estimation statistics
Manfred Koizar <[EMAIL PROTECTED]> writes: > On Fri, 28 Jan 2005 10:53:33 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: >> we should consider >> something like "clamp to size of table / 10" instead. > ... unless a *single* grouping column is estimated to have more than > N/10 distinct values, which should be easy to check. Already done that way. /* * Clamp to size of rel, or size of rel / 10 if multiple Vars. * The fudge factor is because the Vars are probably correlated * but we don't know by how much. */ doubleclamp = rel->tuples; if (relvarcount > 1) clamp *= 0.1; if (reldistinct > clamp) reldistinct = clamp; regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
On Sun, Jan 30, 2005 at 09:49:43PM -0600, Larry Rosenman wrote: > On Sun, 30 Jan 2005, Tom Lane wrote: > > >Steve Atkins <[EMAIL PROTECTED]> writes: > >>For a replacement type, how important is it that it be completely > >>compatible with the existing inet/cidr types? Is anyone actually using > >>inet types with a non-cidr mask? > > > >If you check the archives you'll discover that our current inet/cidr > >types were largely designed and implemented by Paul Vixie (yes, that > >Vixie). I'm disinclined to second-guess Paul about the external > >definition of these types; I just want to rationalize the internal > >representation a bit. In particular we've got some issues about > >conversions between the two types ... > > Please do **NOT** break the external representations. We had enough fights > about that 2-3 releases ago, and I personally don't want to revisit them. > Yes, we do flakey things with inet on the masking stuff. Well, if you want the ability to store both a host address and a netmask in the same datatype the inet masking stuff makes sense. That's not really a useful datatype for any actual use, but it's fairly well-defined. The problem is that when someone looks at the docs they'll see inet as the obvious datatype to use to store IP addresses, and it isn't very good for that. But that's not all that's flakey, unfortunately. The CIDR input format is documented to be classful, which in itself is horribly obsolete and completely useless in this decades internet (and was when the current code was written in '98). But the implementation isn't either classful or classless, and the behaviour disagrees with documented behaviour, and the behaviour you'd reasonably expect, in many cases. -- Class A - documented to be 10.0.0.0/8 steve=# select '10.0.0.0'::cidr; cidr - 10.0.0.0/32 -- Class B - documented to be 128.0.0.0/16 steve=# select '128.0.0.0'::cidr; cidr -- 128.0.0.0/32 -- Class C - documented to be 223.10.0.0/24 steve=# select '223.10.0.0'::cidr; cidr --- 223.10.0.0/32 -- Class D steve=# select '224.10.0.0'::cidr; ERROR: invalid cidr value: "224.10.0.0" DETAIL: Value has bits set to right of mask. steve=# select '224.0.0.0'::cidr; cidr - 224.0.0.0/4 -- Class E steve=# select '240.10.0.0'::cidr; cidr --- 240.10.0.0/32 I use postgresql for network-related applications and for IP address related data mining, so I'm dealing with IP addresses in postgresql on a daily basis. The cidr type, including it's external interface, is simply broken. There is no way to fix it that doesn't change that external interface. I know of at least two independant implementations of function IP address types that have been put together for specific projects to implement a working IP datatype. The ability to use gist indexes on them to accelerate range-based lookups is a bonus. If it's not possible (for backwards compatibility reasons) to fix inet+cidr, would migrating them out to contrib be a possibility? Data types in the core tend to be widely used, even if they're broken and there are better datatypes implemented as external modules. Cheers, Steve ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Allow GRANT/REVOKE permissions to be applied to all schema objects with one command
> Josh's last suggestion (ALL TABLES IN someschema) seems to me to be a > reasonable compromise between usefulness, syntactic weirdness, and > hiding implementation details. Maybe it is not necessary to extend the syntax to distinguish between the two cases. Maybe it's worth considering to have newly created tables/functions automatically 'GRANTED' with permissions set at the schema level. This could perhaps by guarded with GUC variable to preserve compatibility with previous versions. That way people like me who prefer this behavior can just set security at the schema level which is what we want. In the event that the schema security changes, I don't mind having to issue one of Matthias's beefed up GRANTS to get everything right. This removes confusion and allows more freedom to tinker with the GRANT sytax. Plus, it makes having to mess with the system tables/views less likely, IMO. Merlin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Two-phase commit for 8.1
On Sun, Jan 23, 2005 at 07:32:55PM +0200, Heikki Linnakangas wrote: > If it helps, I could try to split it into two patches, one with code > rearrangements that don't change current behaviour, and then the actual > 2PC stuff on top of that. I think that'd be a good idea, because such a patch could be merged right now, and the actual 2PC stuff would be smaller and easier to review. You'd only need a committer to actually commit the initial patch ... -- Alvaro Herrera (<[EMAIL PROTECTED]>) "No hay cielo posible sin hundir nuestras raĆces en la profundidad de la tierra"(Malucha Pinto) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] Bug in create operator and/or initdb
On Sun, 30 Jan 2005, Tom Lane wrote: Steve Atkins <[EMAIL PROTECTED]> writes: For a replacement type, how important is it that it be completely compatible with the existing inet/cidr types? Is anyone actually using inet types with a non-cidr mask? If you check the archives you'll discover that our current inet/cidr types were largely designed and implemented by Paul Vixie (yes, that Vixie). I'm disinclined to second-guess Paul about the external definition of these types; I just want to rationalize the internal representation a bit. In particular we've got some issues about conversions between the two types ... Please do **NOT** break the external representations. We had enough fights about that 2-3 releases ago, and I personally don't want to revisit them. Yes, we do flakey things with inet on the masking stuff. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 8.0.1 bundled ...
Primarily to give Magnus/Dave a chance at the code as early as possible for the Windows package, the 8.0.1 bundles are now available ... I'm working on the 7.x series bundles, but due to some delays in maintenance work on the servers this past weekend, they are taking a wee bit longer then normal ... will announce as soon as thye've been completed... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] IBM patent
Am Samstag, 29. Januar 2005 23:32 schrieb Marc G. Fournier: > On Wed, 26 Jan 2005, Christopher Browne wrote: > > Actually, the latter isn't so. > > > > If Mammoth or Pervasive or such release their own release of > > PostgreSQL, nothing has historically mandated that they make that > > release available under the BSD license. > > > > Presumably acceptance of the patent would change that. > > > > You and I might not have individual objections to this situation, but > > one or another of the companies putting together PostgreSQL releases > > very well might. > > But, there is nothing stop'ng them from replacing the ARC code with their > own variant though ... > And what if there are many more patented parts? If someone wants to have a patent-free variant, he has to replace big parts of postgresql? That wouldn't be good for postgresql. If there is a patent-problem, postgresql has to remove it. What I think about is the legal implications. Sorry, but I don't know BSD very well. Does BSD really allow to remove this BSD-license and put his own, or does BSD allow to release commercial closed-source-variants under the BSD-license? Tommi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Packaging begins in 4 hours ...
Bruce Momjian writes: > A bigger question is whether we want to modify the timezone datbase in > minor releases. Such a change could cause application behavior changes. The reason the zic database changes so often is that in certain parts of the world they throw dice before deciding what this year's DST transition dates will be (or so it seems anyway ;-)). We do need to make an effort to keep it up-to-date, or it will be wrong for those folks. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] FunctionCallN improvement.
(BWhen SQL that returns many tuples with character code conversion (Bis executed, the FunctionCall3/FunctionCall5 becomes a bottleneck. (BBecause MemSet is used to initialize FunctionCallInfoData in these (Bfunctions, a lot of cycles are spent. (B (B (Bset client_encoding to 'SJIS'; (Bselect * from pg_class, pg_amop; (B(This SQL is used only to get a lot of tuples, and there is no (Blogical meaning) (B (B (BEach sample counts as 0.01 seconds. (B % cumulative self self total (B time seconds secondscalls s/call s/call name (B 22.91 1.29 1.29 1562351 0.00 0.00 FunctionCall5 (B 18.29 2.32 1.03 1602006 0.00 0.00 FunctionCall3 (B 5.06 2.60 0.28 4892127 0.00 0.00 AllocSetAlloc (B 4.88 2.88 0.28 9781322 0.00 0.00 AllocSetFreeIndex (B 4.35 3.12 0.24 1587600 0.00 0.00 ExecEvalVar (B (BMost of calls of these functions are from printtup. (BFunctionCall3 is used to generate the text. (BFunctionCall5 is used to character code conversion. (B(printtup -> pq_sendcountedtext -> pg_server_to_client -> (B perform_default_encoding_conversion -> FunctionCall5) (B (BI think that we should initialize only the fields of (BFunctionCallInfoData that must be initialized. (B(Such as FunctionCall1) (B (BI have two plans to modify the code. (B(a)Change FunctionCall3/FunctionCall5 like FunctionCall1. (B It is simple, minimum change. (B (B(b)Define the macro that initialize FunctionCallInfoData, and use it (Binstead of MemSet in all FunctionCallN, DirectFunctionCallN, (BOidFunctionCallN. (B This macro is the following. (B (B#define InitFunctionCallInfoData(Fcinfo, Flinfo, Nargs) \ (Bdo {\ (B(Fcinfo)->flinfo = Flinfo; \ (B(Fcinfo)->context = NULL; \ (B(Fcinfo)->resultinfo = NULL;\ (B(Fcinfo)->isnull = false; \ (B(Fcinfo)->nargs = Nargs;\ (BMemSet((Fcinfo)->argnull, 0, Nargs * sizeof(bool)); \ (B} while(0) (B (BI think that plan(b) is better, because source code consistency (Band efficiency improve. (B (BAny comments? (B (Bregards, (B (B--- (BA.Ogawa ( [EMAIL PROTECTED] ) (B (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (B http://www.postgresql.org/docs/faq
[HACKERS] 7.3.8 under FC3 takes excessive semaphores?
Hi everyone, I'm trying to install a copy of PostgreSQL 7.3.8 on FC3 x86_64, and having a very strange problem with shared memory in that PostgreSQL seems to be taking far too many semaphores for the parameters in the postgresql.conf file. I've raised this with some of the members of the IRC channel, and they have been unable to explain why this is happening. Using the default parameters from a fresh initdb, if I increase shared buffers much above 1000 then the postmaster refuses to start with the following error: - IpcSemaphoreCreate: semget(key=5433025, num=17, 03600) failed: No space left on device This error does *not* mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL's consumption of semaphores by reducing its max_connections parameter (currently 32). The PostgreSQL Administrator's Guide contains more information about configuring your system for PostgreSQL. - Even when I have increased the values of SEMMNI and SEMMNS far above their default values, the postmaster still refuses to start. When I drop shared_buffers back to 1000, the postmaster starts but takes 127(??!) different semaphores arrays each with 17 elements (as listed using ipcs). To check this, I installed the same source on a FC1 i386 machine to do a comparison, using my original shared_buffers value of 1, and found that a successfully running postmaster only takes 3 semaphore arrays with 17 elements. Digging into the source, I added an elog() at line 227 in src/backend/port/sysv_sema.c and found that on the FC1 i386 machine, the loop in IpcSemaphoreCreate() is called only 3 times. Doing the same on the FC3 x86_64 machine, the loop seems to cycle endlessly until I run out of semaphores. Can anyone suggest a reason why the behaviour is so different between FC3 x86_64 and FC1 i386? I'm thinking it may be either to do with the x86_64 platform or perhaps the newer version of gcc used to compile from source. Any pointers in the right direction would be gratefully received :) Kind regards, Mark. WebBased Ltd South West Technology Centre Tamar Science Park Plymouth PL6 8BT T: +44 (0)1752 791021 F: +44 (0)1752 791023 W: http://www.webbased.co.uk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] weird behaviour on DISTINCT ON
Hi all, I have a query that is something like this: SELECT DISTINCT ON ( x ) x, foo(x) FROM ... now what do I see is that for each different x value the foo is executed more than once, I guess this is because the distinct filter out the rows after executing the query. Is this behaviour the normal one? Shall be not documented ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Refactoring
On Wed, 19 Jan 2005 18:57:48 +0100, I wrote: > My first vacuum.c >refactoring patch, rev 1.281 2004-06-08, added these comments in >repair_frag(): > >/* > * VACUUM FULL has an exclusive lock on the relation. So > * normally no other transaction can have pending INSERTs or > * DELETEs in this relation. A tuple is either > * (a) a tuple in a system catalog, inserted or deleted by > * a not yet committed transaction or > * (b) dead (XMIN_INVALID or XMAX_COMMITTED) or > * (c) inserted by a committed xact (XMIN_COMMITTED) or > * (d) moved by the currently running VACUUM. > * In case (a) we wouldn't be in repair_frag() at all. > * In case (b) we cannot be here, because scan_heap() has > * already marked the item as unused, see continue above. > * Case (c) is what normally is to be expected. > * Case (d) is only possible, if a whole tuple chain has been > * moved while processing this or a higher numbered block. > */ It turns out that this comment is not quite correct. It is incomplete. Case (b) should be: known dead (XMIN_INVALID, or XMAX_COMMITTED and xmax is visible to all active transactions). And there is a fifth possibility: (e) deleted (XMAX_COMMITTED) but at least one active transaction does not see the deleting transaction. The patch seems to imply that case (e) is a subcase of (b), but effectively tuples in this state are treated more like (c). Servus Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Group-count estimation statistics
On Fri, 28 Jan 2005 10:53:33 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > we should consider >something like "clamp to size of table / 10" instead. ... unless a *single* grouping column is estimated to have more than N/10 distinct values, which should be easy to check. Servus Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Packaging begins in 4 hours ...
Tom Lane wrote: > Robert Treat <[EMAIL PROTECTED]> writes: > > I thought I saw something this weekend about a new zic/timezone database > > being > > released though having trouble finding out the details now... is this > > something that should be backpatched into the 8.0.x series? > > Too late for 8.0.1. It's not like there won't be any more 8.0 releases ;-) A bigger question is whether we want to modify the timezone datbase in minor releases. Such a change could cause application behavior changes. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly