Re: [HACKERS] more multibyte/After TGL...
Larry Rosenman [EMAIL PROTECTED] writes: Saw Tom's commits, now it breaks here: cc -c -I/usr/local/include -I../../../src/include -DFRONTEND -I. -DSYSCONFDIR='"/home/ler/pg-test/etc/postgresql"' -O -K inline -K PIC -o fe-connect.o fe-connect.c UX:acomp: ERROR: "../../../src/include/mb/pg_wchar.h", line 10: syntax error in macro parameters This one is Tatsuo's fault: he's recently started relying on a gcc-ism: #ifdef FRONTEND #define elog(X...) #endif which will not do. Ok, I have removed the "gcc-ism" macro. Please try to build again on your non-gcc platform and please let me know if you have further problem... -- Tatsuo Ishii
Re: [HACKERS] Summary: what to do about INET/CIDR
makes sense to me On Thu, 26 Oct 2000, Tom Lane wrote: After reviewing a number of past threads about the INET/CIDR mess, I have concluded that we should adopt the following behavior: 1. A data value like '10.1.2.3/16' is a legal INET value (it implies the host 10.1.2.3 in the network 10.1/16) but not a legal CIDR value. Hence, cidr_in should reject such a value. Up to now it hasn't. 2. We do not have a datatype corresponding strictly to a host address alone --- to store a plain address, use INET and let the mask width default to 32. inet_out suppresses display of a "/32" netmask (whereas cidr_out does not). 3. Given that CIDRs never have invalid bits set, we can use the same ordering rules for both datatypes: sort by address part, then by number of bits. This is compatible with what 7.0 did when sorting. It is *not* quite the same as what current sources do, but I will revert that change. I didn't see anyone objecting to this scheme in past discussions, but I also didn't see any clear statement that all the interested parties had agreed to it. Last chance to complain... regards, tom lane Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
[HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
[ Blind CC to general added for comment below.] [Taken off GENERAL, added HACKERS to cc:] Bruce Momjian wrote: He's meaning the libpq version for dynamic link loading. Is the libpq.so lib changing versions (like the change from 6.5.x to 7.0.x changed from libpq.so.2.0 to libpq.so.2.1, which broke binary RPM compatibility for other RPM's linked against libpq.so.2.0, which failed when libpq.so.2.1 came on the scene). I think the answer is no, but I haven't checked the details yet. I usually up the .so version numbers before entering beta. That way, they get marked as newer than older versions. May I ask: is it necessary? Have there been version-bumping changes to libpq since 7.0.x? (With the rate that necessary improvement is happening to PostgreSQL, probably). No, only major releases have bumps. But, enough rant. That _is_ I believe what Trond was asking about. We have been bitten before with people installing the PHP from RedHat 6.2 after installing the PostgreSQL 7.0.x RPMset -- and dependency failures wreaked havoc. So, PostgreSQL 7.1 is slated to be libpq.so.2.2, then? Actually, Bruce, it would do me and Trond a great favor if a list of what so's are getting bumped and to what version were to be posted. At least we can plan for a transition at that point. See pgsql/src/tools/RELEASE_CHANGES. I edit interfaces/*/Makefile and increase the minor number for every interface by one. Let me add one thing on this RPM issue. There has been a lot of talk recently about RPM's, and what they should do, and what they don't do, and who should be blamed. Unfortunately, much of the discussion has been very unproductive and more like 'venting'. I really don't appreciate people 'venting' on these lists, especially since we have _nothing_ to do with RPM's. All we do is make the PostgreSQL software. If people want to discuss RPM's on the ports list, or want to create a new list just about RPM's, that's OK, but venting is bad, and venting on a list that has nothing to do with RPM's is even worse. What would be good is for someone to constructively make a posting about the known problems, and come up with acceptible solutions. Asking us to fix it really isn't going to help because we don't deal with RPM's here, and we don't have enough free time to make significant changes to meet the needs of RPM's. Also, remember we support many Unix platforms, and Linux is only one of them. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Summary: what to do about INET/CIDR
* Tom Lane [EMAIL PROTECTED] [001026 18:46]: After reviewing a number of past threads about the INET/CIDR mess, I have concluded that we should adopt the following behavior: 1. A data value like '10.1.2.3/16' is a legal INET value (it implies the host 10.1.2.3 in the network 10.1/16) but not a legal CIDR value. Hence, cidr_in should reject such a value. Up to now it hasn't. 2. We do not have a datatype corresponding strictly to a host address alone --- to store a plain address, use INET and let the mask width default to 32. inet_out suppresses display of a "/32" netmask (whereas cidr_out does not). 3. Given that CIDRs never have invalid bits set, we can use the same ordering rules for both datatypes: sort by address part, then by number of bits. This is compatible with what 7.0 did when sorting. It is *not* quite the same as what current sources do, but I will revert that change. I didn't see anyone objecting to this scheme in past discussions, but I also didn't see any clear statement that all the interested parties had agreed to it. Last chance to complain... I'd like to see a way to get all 4 octets of a CIDR printed out... Also a way to get network (.0) and broadcast (all ones) for a cidr block out of our stuff. Larry regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] PostgreSQL article in Linux Journal Nov 2000
There is an _excellent_ PostgreSQL article in the current (November 2000) issue of Linux Journal. It is fair, even-handed, and was even written by a MySQL user. Almost a convert, I might add. (He even liked the RPM's :-)) It's not linked on their online site (www.linuxjournal.com) as of yet. Oh, and MySQL beat us 2 to 1 on their 2000 Reader's choice awards. We came in second. The only other contender was Oracle. But, we were ONLY beat 2 to 1. That is an improvement. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
[HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
[Taken off GENERAL, added HACKERS to cc:] Bruce Momjian wrote: He's meaning the libpq version for dynamic link loading. Is the libpq.so lib changing versions (like the change from 6.5.x to 7.0.x changed from libpq.so.2.0 to libpq.so.2.1, which broke binary RPM compatibility for other RPM's linked against libpq.so.2.0, which failed when libpq.so.2.1 came on the scene). I think the answer is no, but I haven't checked the details yet. I usually up the .so version numbers before entering beta. That way, they get marked as newer than older versions. May I ask: is it necessary? Have there been version-bumping changes to libpq since 7.0.x? (With the rate that necessary improvement is happening to PostgreSQL, probably). Let me explain: RPM's contain a plethora of dependency information, some of which is added manually, but most of which is generated automatically. These dependencies are based on which 'soname' is needed to satisfy dynamic linking requirements, interpreter requirements, etc. With version numbers as part of the name, a change in version numbers changes the dependency. Unfortunately RPM deems a dependency upon libpq.so.2.0 to not be fulfilled by libpq.so.2.1 (how _can_ it know? A client linked to 2.0 might fail if 2.1 were to be loaded under it (hypothetically)). Now, that doesn't directly effect the PostgreSQL RPM's. What it does effect is the guy who wants to install PHP from with PostgreSQL support enabled and cannot because of a failed dependency. Who gets blamed? PostgreSQL. Trond may correct me on this, but I don't know of a workaround for this. And any workaround has to be applied to packages that depend upon PostgreSQL, not to the PostgreSQL RPM's (which I would gladly modify) -- although I am going to try something -- I know that a symlink to the old soname works, even though it is a kludge and, IMO, stinks like a polecat. But, enough rant. That _is_ I believe what Trond was asking about. We have been bitten before with people installing the PHP from RedHat 6.2 after installing the PostgreSQL 7.0.x RPMset -- and dependency failures wreaked havoc. So, PostgreSQL 7.1 is slated to be libpq.so.2.2, then? Actually, Bruce, it would do me and Trond a great favor if a list of what so's are getting bumped and to what version were to be posted. At least we can plan for a transition at that point. I just hate to pull a threepeat on RedHat customers. (RH 5.0 shipped PG 6.2.1. RH 5.1 shipped PG 6.3.2. BONG!) (RH 6.0 shipped 6.4.2 (bong!) RH 6.1 shipped 6.5.2 (double BONG!)). RH 7 shipped 7.0.x (small bong) -- RH 7.1 ships 7.1.x (ouch bong). Whew. Trond, you ready for this? [Note: I have been ill, so this message may be more incoherent than my normal scattered self] -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
[HACKERS] Re: [COMMITTERS] pgsql/src/backend/nodes (copyfuncs.c outfuncs.c print.c)
[To hackers this time] At 12:11 27/10/00 +0900, Hiroshi Inoue wrote: For example,LIMIT ALL means LIMIT 1 for optimizer and means no LIMIT for executor. Comments ? It seems there's two possibilities: (a) You know you will only use a limited number of rows, but you are not sure exactly how many. In this case, I'd vote for a 'OPTIMIZE FOR FAST START' clause. (b) You really want all rows, in which case you should let the optimizer do it's stuff. If it fails to work well, then use either 'OPTIMIZE FOR TOTAL COST' or 'OPTIMIZE FOR FAST START' to change the behaviour. ISTM that LIMIT ALL is just the syntax for the default limit clause - and should, if anything, be equivalent to 'OPTIMIZE FOR TOTAL COST'. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
[HACKERS] Re: [COMMITTERS] pgsql/src/backend/nodes (copyfuncs.c outfuncs.c print.c)
[To hackers this time] At 20:59 26/10/00 -0400, Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: Yes I want to give optimizer a hint "return first rows fast". When Jan implemented LIMIT first,there was an option "LIMIT ALL" and it was exactly designed for the purpose. Well, we could make that work that way again, I think. I think that would be a *bad* idea. ISTM that the syntax is obtuse for the meaning it is being given. The (mild) confusion in this thread is evidence of that, at least. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] more multibyte/After TGL...
Todays Sources still die: cc -c -I/usr/local/include -I../../../src/include -I../../../src/interfaces/libpq -O -K inline -o copy.o copy.c cc -c -I/usr/local/include -I../../../src/include -I../../../src/interfaces/libpq -O -K inline -o startup.o startup.c cc -c -I/usr/local/include -I../../../src/include -I../../../src/interfaces/libpq -O -K inline -o prompt.o prompt.c cc -c -I/usr/local/include -I../../../src/include -I../../../src/interfaces/libpq -O -K inline -o variables.o variables.c cc -c -I/usr/local/include -I../../../src/include -I../../../src/interfaces/libpq -O -K inline -o large_obj.o large_obj.c cc -c -I/usr/local/include -I../../../src/include -I../../../src/interfaces/libpq -O -K inline -o print.o print.c cc -c -I/usr/local/include -I../../../src/include -I../../../src/interfaces/libpq -O -K inline -o describe.o describe.c cc -c -I/usr/local/include -I../../../src/include -I../../../src/interfaces/libpq -O -K inline -o tab-complete.o tab-complete.c cc -O -K inline -o psql command.o common.o help.o input.o stringutils.o mainloop.o copy.o startup.o prompt.o variables.o large_obj.o print.o describe.o tab-complete.o -L../../../src/interfaces/libpq -lpq -L/usr/local/lib -lz -lgen -lld -lnsl -lsocket -ldl -lm -lreadline -ltermcap -lcurses Undefined first referenced symbol in file pg_encoding_to_char command.o UX:ld: ERROR: Symbol referencing errors. No output written to psql gmake[3]: *** [psql] Error 1 gmake[3]: Leaving directory `/home/ler/pg-dev/pgsql/src/bin/psql' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/home/ler/pg-dev/pgsql/src/bin' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/home/ler/pg-dev/pgsql/src' gmake: *** [all] Error 2 * Tatsuo Ishii [EMAIL PROTECTED] [001027 02:49]: Larry Rosenman [EMAIL PROTECTED] writes: Saw Tom's commits, now it breaks here: cc -c -I/usr/local/include -I../../../src/include -DFRONTEND -I. -DSYSCONFDIR='"/home/ler/pg-test/etc/postgresql"' -O -K inline -K PIC -o fe-connect.o fe-connect.c UX:acomp: ERROR: "../../../src/include/mb/pg_wchar.h", line 10: syntax error in macro parameters This one is Tatsuo's fault: he's recently started relying on a gcc-ism: #ifdef FRONTEND #define elog(X...) #endif which will not do. Ok, I have removed the "gcc-ism" macro. Please try to build again on your non-gcc platform and please let me know if you have further problem... -- Tatsuo Ishii -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Lamar Owen [EMAIL PROTECTED] writes: Unfortunately RPM deems a dependency upon libpq.so.2.0 to not be fulfilled by libpq.so.2.1 (how _can_ it know? A client linked to 2.0 might fail if 2.1 were to be loaded under it (hypothetically)). If so, I claim RPM is broken. The whole point of major/minor version numbering for .so's is that a minor version bump is supposed to be binary-upward-compatible. If the RPM stuff has arbitrarily decided that it won't honor that definition, why do we bother with multiple numbers at all? So, PostgreSQL 7.1 is slated to be libpq.so.2.2, then? To answer your question, there are no pending changes in libpq that would mandate a major version bump (ie, nothing binary-incompatible, AFAIK). We could ship it with the exact same version number, but then how are people to tell whether they have a 7.0 or 7.1 libpq? regards, tom lane
Re: [HACKERS] Summary: what to do about INET/CIDR
* Larry Rosenman [EMAIL PROTECTED] [001027 09:51]: * Tom Lane [EMAIL PROTECTED] [001027 09:49]: Larry Rosenman [EMAIL PROTECTED] writes: Also a way to get network (.0) and broadcast (all ones) for a cidr block out of our stuff. network() and broadcast() have been there all along ... but don't work on CIDR types. And I get to be wrong. Sorry about that. But, it would still be nice if we can force all 4 octets to be printed for the network funcs.. LER LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] Select syntax (broken in current CVS tree)
"Kevin O'Gorman" [EMAIL PROTECTED] writes: I've been poking into the syntax in gram.y, and finding that the provision of parentheses for SELECT statements is pretty broken. I have previously posted examples of odd things. On closer examination, it appears to need an overhaul. There are two problems with this: (1) I'm new here, I don't know the players and the protocols very well. I don't want to offend. The existing handling of parens in SELECTs was done by me, a month or so back. I'm not satisfied with it, but decided that I couldn't spend any more time on it right then. If you can improve it, be my guest. And (2) I don't have access to the SQL standards so that we might get it right. The SQL spec is available (I haven't got a URL at hand but see the list archives), but it really won't help you a lot in this case, because the grammar it gives is clearly ambiguous. The whole problem here is to come up with a yacc-compatible grammar that does what we want. AFAIK our current grammar is correct in that (a) it requires parens where they are required by the spec, and (b) it permits one level of parens where they are permitted by the spec. What it doesn't do is permit redundant multiple levels of parens. The other thing it doesn't do is allow ORDER BY or LIMIT in sub-selects, only in a top-level SELECT statement. This is correct per SQL92 spec, but as I commented yesterday, I think we should ignore that spec restriction henceforth. It's possible that dropping that distinction would make the paren situation easier to solve --- I did not consider the possibility of doing that when I was hacking on it last month. Little fixes here are going to get into trouble with yacc because the current approach is so awkward. It turns out it's the reason Select Statements cannot be listed in a CREATE RULE like the other kinds of commands. No, the distinction between selects and other rule statements in CREATE RULE is there for an entirely different reason: to enforce a semantic restriction. See past thread about whether multiple selects make sense in a rule. AFAIK the paren situation doesn't affect that. Given a target syntax (like from the SQL standard) this can be done in a day or so. The question is: should it happen, and if so what is the target syntax? The overall structure of the SQL-spec grammar is sufficiently different from ours that I'm not sure we want to adopt it at all. It's certainly not going to be a one-day project if we try. regards, tom lane
Re: [HACKERS] Re: [COMMITTERS] pgsql/src/backend/nodes (copyfuncs.c outfuncs.c print.c)
On Fri, Oct 27, 2000 at 09:21:23PM +1000, Philip Warner wrote: [To hackers this time] At 20:59 26/10/00 -0400, Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: Yes I want to give optimizer a hint "return first rows fast". When Jan implemented LIMIT first,there was an option "LIMIT ALL" and it was exactly designed for the purpose. Well, we could make that work that way again, I think. I think that would be a *bad* idea. ISTM that the syntax is obtuse for the meaning it is being given. The (mild) confusion in this thread is evidence of that, at least. Syncronicity, man. I didn't see the beginning of this thread (not on COMMITERS) so I may be repeating things from there. I was recently cleaning out a stack of old trade-rags lying around, and snipped an article out of a DB2 mag I've been getting. Very technical, and discusses the uses (and abuses) of OPTIMIZE FOR N ROWS, where N is an actual number. Discusses how the DB2 optimizer will use this hint to decide if it should use an index to get the right order, even if it's a full scan, and the total cost might be higher. I'll see if I can find it online, if anyones interested. The original article is all in the context of cursors (and multi-gig tables), but I think LIMIT brings in many of the same optimization considerations. ISTM that the most common use of LIMIT right now is to simulate a cursor to provide some state over the stateless HTTP protocol, no? So the LIMIT is not 'fast start' vs 'total cost': the webpage often allows the enduser to select the batchsize. At some batchsize, 'total cost' wins over a simplistic 'fast start' approach. And only the optimizer has any hope of figuring out where that might be, as it will change with the exact query structure. Ross -- Open source code is like a natural resource, it's the result of providing food and sunshine to programmers, and then staying out of their way. [...] [It] is not going away because it has utility for both the developers and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Lamar Owen [EMAIL PROTECTED] writes: Unfortunately RPM deems a dependency upon libpq.so.2.0 to not be fulfilled by libpq.so.2.1 (how _can_ it know? A client linked to 2.0 might fail if 2.1 were to be loaded under it (hypothetically)). If so, I claim RPM is broken. The whole point of major/minor version numbering for .so's is that a minor version bump is supposed to be binary-upward-compatible. If the RPM stuff has arbitrarily decided that it won't honor that definition, why do we bother with multiple numbers at all? So, PostgreSQL 7.1 is slated to be libpq.so.2.2, then? To answer your question, there are no pending changes in libpq that would mandate a major version bump (ie, nothing binary-incompatible, AFAIK). We could ship it with the exact same version number, but then how are people to tell whether they have a 7.0 or 7.1 libpq? Yes, we need to have new numbers so binaries from different releases use the proper .so files. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Summary: what to do about INET/CIDR
* Larry Rosenman [EMAIL PROTECTED] [001027 11:08]: * Tom Lane [EMAIL PROTECTED] [001027 09:49]: Larry Rosenman [EMAIL PROTECTED] writes: Also a way to get network (.0) and broadcast (all ones) for a cidr block out of our stuff. network() and broadcast() have been there all along ... OK, what I really meant was a way to coerce a CIDR entity to INET so that host() can work with a CIDR type to print all 4 octets. Does this help with what I want? Currently you can't coerce a CIDR type to INET. For example, I feel the following should work: ler=# \d ler_test Table "ler_test" Attribute | Type | Modifier ---+--+-- net | cidr | host | inet | ler=# select * from ler_test; net | host ---+-- 207.158.72/24 | 207.158.72.11/24 (1 row) ler=# select host(net::inet) from ler_test; ERROR: CIDR type has no host part ERROR: CIDR type has no host part ler=# regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
What would be good is for someone to constructively make a posting about the known problems, and come up with acceptable solutions. Asking us to fix it really isn't going to help because we don't deal with RPM's here, and we don't have enough free time to make significant changes to meet the needs of RPM's. Which is why I stepped up to the plate last year to help with RPM's. I apologize if you took my post (which I edited greatly) as 'venting' -- it was not my intention to 'vent', much less offend. I just want to know what to expect from the 7.1 release. I feel that that is germane to the Hackers list, as the knowledge necessary to answer the question is to be found on the list. (and you answered the question above). No, I was not pointing to you when I mentioned venting. There have been other RPM threads lately. I just want information on how to make things better for RPM's, not vents. Like it or not, in the eyes of many people having solid RPM's is a core issue. If there are gotchas, I want to document them so people don't get blindsided. Or work around them. Or ask why the change is necessary in the first place. Sure. I appreciate the fact that we are not here to make it easy for distributors to package our software. I also appreciate the fact that if you don't at least make an effort to work with major distributors (and RedHat, TurboLinux, Caldera, and SuSE together comprise a major userbase) that you run the risk of not being distributed in favor of an inferior product. Let them. It is their decision. Frankly, I have seen this attitude before, and I don't like it. Just the mention that "Gee, if you don't cooperate, we may yank you," is really a veiled threat. Now, I know you aren't saying that, but the "if you don't play nice, we will drop you" argument sounds a lot more like MS that a Linux vendor should be acting, especially since they are not telling us what they want or assisting in the work. The "We are big. Just fix it and let us know when it is ready" attitude does not work here, and that is what I am hearing mostly from the RPM people. I also appreciate and applaud the cross-platform mentality of the PostgreSQL developers. Linux is far from the only OS to be supported by PostgreSQL, true. But Linux is also the most popular OS for PostgreSQL deployment. True, it is the most popular, but that doesn't make the others less important. This whole statement comes across as, "You run on Linux, and look, you took the time to run on other OS's too. How quaint." In the history of this project, Linux was an after-thought. None of our platforms are inferior or superior, except to the extent that the platform does not support Unix standard functions (like NT/Cygwin). However, there are known problems that can bite people who are not using RPM's and are not running Linux. Some of those problems are such that it will take someone with more knowledge than I currently possess to solve. One is the issue of upgrading/migrating tools. This is not an RPM-specific issue. To me, that is the only big issue that I have spoken about in a way that could even remotely be construed as 'venting'. And it is not a Linux-specific issue. It is a core issue. Again, your comments where quite helpful. We need more of them. We need to hear more about the problems people are having with RPM's, and how to make them better. There must be a list of known problems. Let's hear them, so we can try to solve them as a group. However, in general, we do not make dramatic change to work around OS bugs, and do not plan to make major changes to work around the limitations of RPM's. My bet is that some middle layer can be created that will fix that for us. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Summary: what to do about INET/CIDR
* Tom Lane [EMAIL PROTECTED] [001027 09:49]: Larry Rosenman [EMAIL PROTECTED] writes: Also a way to get network (.0) and broadcast (all ones) for a cidr block out of our stuff. network() and broadcast() have been there all along ... OK, what I really meant was a way to coerce a CIDR entity to INET so that host() can work with a CIDR type to print all 4 octets. Does this help with what I want? Currently you can't coerce a CIDR type to INET. regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Tom Lane [EMAIL PROTECTED] writes: Lamar Owen [EMAIL PROTECTED] writes: Unfortunately RPM deems a dependency upon libpq.so.2.0 to not be fulfilled by libpq.so.2.1 (how _can_ it know? A client linked to 2.0 might fail if 2.1 were to be loaded under it (hypothetically)). You link against libpq.so.2 , not libpq.so.2.1. This isn't a problem. If the RPM stuff has arbitrarily decided that it won't honor that definition, why do we bother with multiple numbers at all? There is no such problem. So, PostgreSQL 7.1 is slated to be libpq.so.2.2, then? To answer your question, there are no pending changes in libpq that would mandate a major version bump (ie, nothing binary-incompatible, AFAIK). We could ship it with the exact same version number, but then how are people to tell whether they have a 7.0 or 7.1 libpq? If there isn't any changes, why bump it? -- Trond Eivind Glomsrød Red Hat, Inc.
[HACKERS] LIMIT in DECLARE CURSOR: request for comments
Hiroshi and I had a discussion last night that needs to reach a wider audience than just the bystanders on pgsql-committers. Let me see if I can reconstruct the main points. In 7.0, a LIMIT clause can appear in a DECLARE CURSOR, but it's ignored: play= select * from vv1; f1 - 0 123456 -123456 2147483647 -2147483647 0 (6 rows) play= begin; BEGIN play= declare c cursor for select * from vv1 limit 2; SELECT play= fetch 10 from c; f1 - 0 123456 -123456 2147483647 -2147483647 0 (6 rows) The reason for this behavior is that LIMIT and the FETCH count are implemented by the same mechanism (ExecutorRun's count parameter) and so FETCH has no choice but to override the LIMIT with its own argument. Yesterday I reimplemented LIMIT as a separate plan node type, in order to make it work in views. A side effect of this is that ExecutorRun's count parameter is now *only* used for FETCH, and therefore a LIMIT appearing in a DECLARE CURSOR does what IMHO it should do: you get that many rows and no more from the cursor. regression=# begin; BEGIN regression=# declare c cursor for select * from vv1 limit 2; SELECT regression=# fetch 10 from c; f1 0 123456 (2 rows) Hiroshi was a little concerned about this change in behavior, and so the first order of business is whether anyone wants to defend the old way? IMHO it was incontrovertibly a bug, but ... The second question is how the presence of a LIMIT clause ought to affect the planner's behavior. In 7.0, we taught the planner to pay attention to LIMIT as an indicator whether it ought to prefer fast-start plans over lowest-total-cost plans. For example, consider SELECT * FROM tab ORDER BY col; and assume there's a b-tree index on col. Then the planner has two possible choices of plan: an indexscan on col, or a sequential scan followed by sort. The indexscan will begin delivering tuples right away, whereas the sort has to finish the sequential scan and perform the sort before it can deliver the first tuple. OTOH the total cost to deliver the entire result is likely to be less for the sort plan (let's assume for this discussion that it is). So for the above query the planner should and will choose the sort plan. But for SELECT * FROM tab ORDER BY col LIMIT 1; it will choose the indexscan plan because of the low startup cost. This is implemented by pricing a query that uses LIMIT on the basis of linear interpolation between the startup and total costs, with the interpolation point determined by the fraction of tuples we expect to retrieve. This is all pretty clear and seems to work OK for stand-alone SELECT. But what about a DECLARE CURSOR? The planner has no way to know how much of the cursor's result will actually be FETCHed by the user, so it's not clear how to use all this shiny new LIMIT planning mechanism for a DECLARE CURSOR. What happens in 7.0 and current code is that for a DECLARE CURSOR, the planner ignores any LIMIT clause and arbitrarily assumes that the user will FETCH about 10% of the available data. Hence, the planning is done on the basis of least "startup + 0.10*(total - startup)" cost. Ignoring the limit clause was correct in 7.0, given the fact that the limit wouldn't actually be used at runtime, but it's wrong now (unless I'm beaten down on the semantics change). Also, the 10% estimate is the sort of compromise that's likely to satisfy nobody --- if you intend to fetch all the data, quite likely you want the least total cost, whereas if you only want the first few rows, you probably want a plan biased even more heavily towards startup cost at the expense of total cost. After thinking some more about yesterday's discussions, I propose that we adopt the following planning behavior for cursors: 1. If DECLARE CURSOR does not contain a LIMIT, continue to plan on the basis of 10%-or-so fetch (I'd consider anywhere from 5% to 25% to be just as reasonable, if people want to argue about the exact number; perhaps a SET variable is in order?). 10% seems to be a reasonable compromise between delivering tuples promptly and not choosing a plan that will take forever if the user fetches the whole result. 2. If DECLARE CURSOR contains a specific "LIMIT n" clause, plan on the assumption that n tuples will be fetched. For small n this allows the user to heavily bias the plan towards fast start. Since the LIMIT will actually be enforced by the executor, the user cannot bias the plan more heavily than is justified by the number of tuples he's intending to fetch, however. 3. If DECLARE CURSOR contains "LIMIT ALL", plan on the assumption that all tuples will be fetched, ie, select lowest-total-cost plan. (Note: LIMIT ALL has been in the grammar right along, but up to now it has been entirely equivalent to leaving out the LIMIT clause. This proposal essentially suggests allowing it to act as a planner hint
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
To answer your question, there are no pending changes in libpq that would mandate a major version bump (ie, nothing binary-incompatible, AFAIK). We could ship it with the exact same version number, but then how are people to tell whether they have a 7.0 or 7.1 libpq? If there isn't any changes, why bump it? This is huge software. There are changes to every library in every major release, major for us meaning, i.e., 7.0-7.1. That is why I bump the numbers. The interesting issue is that the version number changes for .so do _not_ mean they only talk with servers of the same release. They will talk to future servers of higher release numbers. This is done because there is a backend protocol number that is passed from client to server which determines how the server should behave with that client. We can't always have new clients talking to older servers because the old servers may not know the newer protocol. We could get fancy and trade version numbers and try to get it working, but it has not been a priority, and few have asked for it. Having old clients talking to new databases has been enough for most users. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: Unfortunately RPM deems a dependency upon libpq.so.2.0 to not be fulfilled by libpq.so.2.1 (how _can_ it know? A client linked to 2.0 might fail if 2.1 were to be loaded under it (hypothetically)). If so, I claim RPM is broken. The whole point of major/minor version numbering for .so's is that a minor version bump is supposed to be binary-upward-compatible. If the RPM stuff has arbitrarily decided that it won't honor that definition, why do we bother with multiple numbers at all? So, PostgreSQL 7.1 is slated to be libpq.so.2.2, then? To answer your question, there are no pending changes in libpq that would mandate a major version bump (ie, nothing binary-incompatible, AFAIK). We could ship it with the exact same version number, but then how are people to tell whether they have a 7.0 or 7.1 libpq? And that is a very good point. Hey, I'm caught in the middle here :-). I want to see PostgreSQL succeed and excel (which, to me, means becoming the RDBMS of choice) on RPM-based Linux distributions, which I am sure is a goal of others too. And I'm sure no one here is against that. But, there is friction between RedHat's (to use the first example of a distributor to pop into my head) needs and the needs of the PostgreSQL group. My gut feel is that RedHat may be better off shipping 7.0.x if the library version numbers are a contributory problem. The data upgrade problem is a bigger problem. To which RedHat might just want to stay at 7.0.x until either a tool is written to painlessly migrate or until the next major RedHat is released. Of course, that doesn't affect what I do as far as building 7.1 RPM's for distribution from the PostgreSQL site (or by anyone who so desires to distribute them). I have no choice for my own self but to stay on the curve. I need TOAST and OUTER JOINS too much. So, what I feel may be the best compromise is for RedHat (and myself) to continue building 7.0.x RPM's with bugfixes, etc, while I build 7.1 ad subsequent RPMset's for those who know what they're doing and not blindly upgrading their systems. Trond, do you have any comments on that? Or is the likely migration to kernel 2.4 in the next RedHat going to make a compatability compromise here moot? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
[HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Bruce Momjian [EMAIL PROTECTED] writes: Let them. It is their decision. Frankly, I have seen this attitude before, and I don't like it. Just the mention that "Gee, if you don't cooperate, we may yank you," is really a veiled threat. Now, I know you aren't saying that, but the "if you don't play nice, we will drop you" argument sounds a lot more like MS that a Linux vendor should be acting, especially since they are not telling us what they want or assisting in the work. FWIW, I've never threatened to do so. If I wanted to, I would just do it[1] - threats are bad and never cause anything but bad feelings. That being said, my favorite wishes (in addition to as much SQL compliance and performance as possible, of course) are: * migration on upgrade * old libraries being able to speak to newer databases, so old binaries can continue working after database upgrades * good sonames on libraries - if a library hasn't changed, bumping the number to show it's part of a new version isn't necesarry. If it is backwards compatible, just bump the minor version, if it isn't, bump the major version. Or even better, use versioned symbols (I don't know how many other OSes than Linux and Solaris supports this, though). As for assisting, at least Red Hat contributes to a lot of projects, some of which are important to postgres on one or more platforms: gdb, gcc, glibc and the linux kernel. There just isn't enough resources to do everything, but I try to help out with the RPMs. When we make patches for packages, we try to cooperate with the author(s) to get them in - happily, we haven't had much of a need for that with postgresql. The "We are big. Just fix it and let us know when it is ready" attitude does not work here, and that is what I am hearing mostly from the RPM people. I haven't heard anyone say that. There must be a list of known problems. Let's hear them, so we can try to solve them as a group. However, in general, we do not make dramatic change to work around OS bugs, and do not plan to make major changes to work around the limitations of RPM's. I don't think there are any apart from the upgrade issues - if library versioning follows the standard, that certainly won't be a problem. [1] which I'm not even close to doing - I've spent a bit of time lately hunting down aliasing bugs in MySQL which causes wrong SQL query results if compiled with "-O2". Ouch. -- Trond Eivind Glomsrød Red Hat, Inc.
Re: [HACKERS] Idea: cross-check versions during initdb
1. Add a --version switch to postgres or postmaster to print its version and exit. postmaster already has this. Someone can copy the code into tcop/postgres.c as well. But should we not use the catversion for this? to a compatible library directory. Alternatively, add version info as a comment in the first line of global.bki. I think that's better. Bonus project: find out why initdb is picking up the wrong files in the first place. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Bruce Momjian wrote: I appreciate the fact that we are not here to make it easy for distributors to package our software. I also appreciate the fact that if you don't at least make an effort to work with major distributors (and RedHat, TurboLinux, Caldera, and SuSE together comprise a major userbase) that you run the risk of not being distributed in favor of an inferior product. Let them. It is their decision. Frankly, I have seen this attitude before, and I don't like it. Just the mention that "Gee, if you don't cooperate, we may yank you," is really a veiled threat. I don't even see it as a veiled threat, Bruce. It simply _is_ a threat. There are other RDBMS choices. Currently PostgreSQL is the Officially Sanctioned RDBMS for multiple Linux distributions. As our capabilities increase, it will make us more and more attractive as the Choice, Top Shelf Open Source RDBMS. However, the upgrade gotcha has left a very bitter taste in more than one user's mouth. I'll not say more about that now, as I've said quite enough in the past. And I'm still trying to figure out enough of the internals of the storage manager to try to write the migration tools myself. But, I have other fish to fry right now, the biggest being cross-distribution RPM's. Linux is far from the only OS to be supported by PostgreSQL, true. But Linux is also the most popular OS for PostgreSQL deployment. True, it is the most popular, but that doesn't make the others less important. No, it doesn't. This whole statement comes across as, "You run on Linux, and look, you took the time to run on other OS's too. How quaint." I ran Unix before there was linux. I ran Unix years before Linus was even out of High School. Well, that is if you count Tandy Xenix V7 and System III as Unix. Or ATT 3B1 SysVR2. Or Apollo DomainOS SR10.2. Or Ultrix on a VAX 11/750 (running in tandem with VMS). And I'm considering moving my most critical public servers from Linux over to OpenBSD. A Linux bigot I'm not. However, there are known problems that can bite people who are not using RPM's and are not running Linux. Some of those problems are such that it will take someone with more knowledge than I currently possess to Again, your comments where quite helpful. We need more of them. We need to hear more about the problems people are having with RPM's, and how to make them better. Bruce, sometimes I fear my own lack of communications skills. If I can make my wife fighting mad at me with me having no clue as to what I said that made her mad, I fear I can make anyone mad, without knowing what I said to do so. So, I guess you could say I'm a little paranoid about my communications skills. So, I'm glad you considered my comments helpful -- I was beginning to get worried. There must be a list of known problems. Let's hear them, so we can try to solve them as a group. However, in general, we do not make dramatic change to work around OS bugs, and do not plan to make major changes to work around the limitations of RPM's. My bet is that some middle layer can be created that will fix that for us. Meet Mr. Middle Layer. :-) The PostgreSQL spec file that controls the RPM build is one of the most complex ones in the RedHat distribution, AFAIK. There's the middle layer. It does quite a bit of finagling already. And the work that Peter E is doing is helping my cause significantly. Bruce, when I recover fully from the illness I've had the last few days, I'll try to come up with a coherent listing of what I've had to work around in the past. My current headache won't let me think straight right now, which makes it likely that I won't effectively communicate the issues. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [HACKERS] Summary: what to do about INET/CIDR
Larry Rosenman [EMAIL PROTECTED] writes: OK, what I really meant was a way to coerce a CIDR entity to INET so that host() can work with a CIDR type to print all 4 octets. Hm. I don't see any really good reason why host() rejects CIDR input in the first place. What's wrong with producing the host address that corresponds to extending the CIDR network address with zeroes? Currently you can't coerce a CIDR type to INET. Well you can, but it doesn't *do* anything. One of the peculiarities of these two types is that the cidr-vs-inet flag is actually stored in the data value. The type-system differentiation between CIDR and INET is a complete no-op for everything except initial entry of a value (ie, conversion of a text string to CIDR or INET); all the operators that care (which is darn few ... in fact it looks like host() is the only one!) look right at the value to see which type they've been given. So applying a type coercion may make the type system happy, but it doesn't do a darn thing to the bits, and thus not to the behavior of subsequent operators either. I have not yet figured out if that's a good thing or a bad thing ... regards, tom lane
Re: [HACKERS] Summary: what to do about INET/CIDR
Alex Pilosov [EMAIL PROTECTED] writes: Also, I agree with Larry that cidr _must_ be printed with 4 octets in them, whether they are 0 or not. (i.e. it should print 207.158.72.0/24) This is the standard way of specifying addresses in all network equipment. RFC specifies that, just the library that we use doesn't (yes, it is from Vixie, but it doesn't make it RFC-compliant) Somehow, I am more inclined to believe Vixie's opinion on this than either yours or Larry's ;-) If you think there is an RFC that demands the above behavior and not what Vixie recommended to us, let's see chapter and verse. FWIW, the direction we seem to be converging in is that INET will always print all four octets. Maybe the answer for you is to use INET, rather than to try to persuade us that you understand CIDR notation better than Vixie does... regards, tom lane
(forw) Re: [HACKERS] Summary: what to do about INET/CIDR
I can't type today - Forwarded message from Larry Rosenman [EMAIL PROTECTED] - From: Larry Rosenman [EMAIL PROTECTED] Subject: Re: [HACKERS] Summary: what to do about INET/CIDR Date: Fri, 27 Oct 2000 15:09:36 -0500 Message-ID: [EMAIL PROTECTED] User-Agent: Mutt/1.3.10i X-Mailer: Mutt http://www.mutt.org/ To: Tom Lane [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] * Tom Lane [EMAIL PROTECTED] [001027 15:07]: Larry Rosenman [EMAIL PROTECTED] writes: OK, what I really meant was a way to coerce a CIDR entity to INET so that host() can work with a CIDR type to print all 4 octets. Hm. I don't see any really good reason why host() rejects CIDR input in the first place. What's wrong with producing the host address that corresponds to extending the CIDR network address with zeroes? Agreed. If we could do that, I'd be satisfied. This is what started my tirade in the summer (trying to do an IP Allocation system). Currently you can't coerce a CIDR type to INET. Well you can, but it doesn't *do* anything. One of the peculiarities of these two types is that the cidr-vs-inet flag is actually stored in the data value. The type-system differentiation between CIDR and INET is a complete no-op for everything except initial entry of a value (ie, conversion of a text string to CIDR or INET); all the operators that care (which is darn few ... in fact it looks like host() is the only one!) look right at the value to see which type they've been given. So applying a type coercion may make the type system happy, but it doesn't do a darn thing to the bits, and thus not to the behavior of subsequent operators either. I have not yet figured out if that's a good thing or a bad thing ... OIC. Hadn't looked that closely. What I want is a way to print all 4 octets of a CIDR/INET entry at ALL times. LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 - End forwarded message - -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Trond Eivind Glomsrød wrote: Lamar Owen [EMAIL PROTECTED] writes: Unfortunately RPM deems a dependency upon libpq.so.2.0 to not be fulfilled by libpq.so.2.1 (how _can_ it know? A client linked to 2.0 might fail if 2.1 were to be loaded under it (hypothetically)). There usually are no such problems, and I'm not aware of any specific to postgresql either. There have been reports to the pgsql-bugs list and to the PHP list about this very issue. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
[HACKERS] Can't import date using copy
Hi I have tried reformatting dates in many ways but every thing I have tried fails. I used insert and update to create and change dates with different styles and was successful. Is this a known bug? Is their a fix for this bug? or Do I have to import date variables using insert/update statements? I built the binaries from Redhat's postgresql-7.0.2-2.src.rpm package and installed all the binary packages that were built: postgresql-7.0.2-2.i386.rpm postgresql-devel-7.0.2-2.i386.rpm postgresql-jdbc-7.0.2-2.i386.rpm postgresql-odbc-7.0.2-2.i386.rpm postgresql-perl-7.0.2-2.i386.rpm postgresql-python-7.0.2-2.i386.rpm postgresql-server-7.0.2-2.i386.rpm postgresql-tcl-7.0.2-2.i386.rpm postgresql-test-7.0.2-2.i386.rpm postgresql-tk-7.0.2-2.i386.rpm The reason I built the binaries from the source code, was because the ftp server at redhat was overloaded and was only lucky enough to get on after numerous attempts. I have been building using linux since 1995, and postgresql since 1997. This is the first major problem I have had. I have only requested help a couple of times, but do contribute to the list on occasions. Guy
Re: [HACKERS] LIMIT in DECLARE CURSOR: request for comments
Philip Warner [EMAIL PROTECTED] writes: At 12:18 27/10/00 -0400, Tom Lane wrote: 1. If DECLARE CURSOR does not contain a LIMIT, continue to plan on the basis of 10%-or-so fetch (I'd consider anywhere from 5% to 25% to be just as reasonable, if people want to argue about the exact number; perhaps a SET variable is in order?). SET sounds good; will this work on a per-connection basis? A SET variable would be connection-local, same as any other ... I don't suppose you'd consider 'OPTIMIZE FOR TOTAL COST' and 'OPTIMIZE FOR FAST START' optimizer hints? I don't much care for adding such syntax to DECLARE CURSOR, if that's what you're suggesting. LIMIT ALL would have the same effect as 'OPTIMIZE FOR TOTAL COST' anyway. LIMIT 1 (or a small number) would have the effect of 'OPTIMIZE FOR FAST START', but would constrain you to not fetch any more rows than that. If we had a SET variable then you could twiddle that value to favor fast-start or total-cost concerns over a continuous range, without constraining how many rows you actually fetch from a LIMIT-less cursor. Also, does the change you have made to the executor etc mean that subselect-with-limit is now possible? The executor will do it, but unless Kevin figures out how to fix the grammar, you'll have to put the LIMIT into a view definition, not inline in a subquery. View-with-LIMIT does work as of today. regards, tom lane
Re: [HACKERS] LIMIT in DECLARE CURSOR: request for comments
At 12:18 27/10/00 -0400, Tom Lane wrote: 1. If DECLARE CURSOR does not contain a LIMIT, continue to plan on the basis of 10%-or-so fetch (I'd consider anywhere from 5% to 25% to be just as reasonable, if people want to argue about the exact number; perhaps a SET variable is in order?). 10% seems to be a reasonable compromise between delivering tuples promptly and not choosing a plan that will take forever if the user fetches the whole result. SET sounds good; will this work on a per-connection basis? 2. If DECLARE CURSOR contains a specific "LIMIT n" clause, plan on the assumption that n tuples will be fetched. For small n this allows the user to heavily bias the plan towards fast start. Since the LIMIT will actually be enforced by the executor, the user cannot bias the plan more heavily than is justified by the number of tuples he's intending to fetch, however. Fine. 3. If DECLARE CURSOR contains "LIMIT ALL", plan on the assumption that all tuples will be fetched, ie, select lowest-total-cost plan. Good. Comments? I don't suppose you'd consider 'OPTIMIZE FOR TOTAL COST' and 'OPTIMIZE FOR FAST START' optimizer hints? Also, does the change you have made to the executor etc mean that subselect-with-limit is now possible? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Re: [HACKERS] Idea: cross-check versions during initdb
Peter Eisentraut [EMAIL PROTECTED] writes: But it's still dependent on the user's PATH to point to the right executables, no? This is what's puzzling me. There's code in there that tries to locate initdb and uses the executables and bki files (7.0 only) from the same tree. Yeah, but how long has that code been in there? Wouldn't be at all surprised if the complaints are coming from people who are managing to invoke a 6.5 initdb script against 7.0 postgres executable and/or library files. Of course, people who manage to invoke a 6.5 or 7.0 initdb script aren't going to be helped anyway by defenses we put into 7.1 initdb :-(. Perhaps there need to be additional crosschecks performed by the postgres executable to ensure that (a) it's being called by a compatible initdb and (b) it's being fed compatible bki files. Point b could be addressed if we put version IDs into the bki files and have BootstrapMain check for them. As for point a, maybe we could extend the bootstrap switch set so that it includes a version number passed by the initdb script; then BootstrapMain refuses to play unless the correct version number is supplied. This would work if old postgres executables reject the version# info as an invalid switch ... regards, tom lane
Re: [HACKERS] Summary: what to do about INET/CIDR
* Tom Lane [EMAIL PROTECTED] [001027 17:04]: BTW, does it strike anyone else as peculiar that the host(), broadcast(), network(), and netmask() functions yield results of type text, rather than type inet? Seems like it'd be considerably more useful if they returned values of type inet with masklen = 32 (except for network(), which would keep the original masklen while coercing bits to its right to 0). Given the current proposal that inet_out should always display all 4 octets, and the existing fact that inet_out suppresses display of a /32 netmask, the textual display of SELECT host(...) etc would remain the same as it is now. But AFAICS you could do more with an inet-type result value, like say compare it to other inet or cidr values ... Comments? Why was it done this way, anyway? It doesn't bother me, as long as there is someway for me to get from a CIDR type to 4 octets output with no mask indicated, and print the broadcast and netmask and bits out separately from ONE column in the table. I.E. for select network('207.158.72.0/24'),broadcast('207.158.72.0/24'),netmask('207.158.72.0/24') I get 207.158.72.0 207.158.72.255 255.255.255.0 as output. Aside from that, I'm not picky. Larry regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] Re: [GENERAL] A rare error
Kevin O'Gorman wrote: [EMAIL PROTECTED] wrote: "Kevin O'Gorman" [EMAIL PROTECTED] writes: Anyway, the bottom line of all this rambling is that if you can get rid of the distinction between SelectStmt and select_clause altogether, that would be fine with me. You might consider looking at whether you can write two nonterminals: a SELECT construct that has no outer parens, and then an additional construct subselect: SelectStmt | '(' subselect ')' which would be used for all the sub-select nonterminals in SelectStmt itself. I'm headed in that direction. I've been calling it 'subquery'. OTOH, maybe we don't want NOT IN (((SELECT foo FROM bar))). If we can't do that then we're still going to get complaints, I think. The original bug report in this thread was specifically that the thing didn't like redundant parentheses; we should try to remove that restriction in all contexts not just some. All that being said, I'm not sure enough notice has been taken of one aspect of the changes already in place, and likely to become more pronounced. It may be okay with everybody, but I don't want it to be a big surprise: queries may no longer begin with SELECT, but instead with an arbitrary number of left parens. In some cases, the semantics gets lost in the syntax. Consider: (SELECT * INTO newtable FROM table1) UNION (SELECT * FROM table2); Notice the INTO? Doesn't this seem like an odd place for it, in what appears to be a subordinate query? Where else would it go? How would it grab you in an expression with five or more levels of parens? How about five levels of parens and a complicated targetlist before you get to the INTO? This just occurred to me: how would you sort the results of this query? The path of least resistance from the way things work now would be most non-obvious: put the ORDER BY on the leftmost query. It looks like this (SELECT * INTO newtable FROM table1 ORDER BY field1) UNION (SELECT * FROM table2); And I have to say that's about the ugliest construct I've seen in a pretty ugly language. What I'm suggesting is that the parens be allowed only on the right hand side of the set operations. How does that strike you? Anyway, that's the direction I'm going in now, but as always, I solicit comments. regards, tom lane -- Kevin O'Gorman (805) 650-6274 mailto:[EMAIL PROTECTED] Permanent e-mail forwarder: mailto:Kevin.O'[EMAIL PROTECTED] At school: mailto:[EMAIL PROTECTED] Web: http://www.cs.ucsb.edu/~kogorman/index.html Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html "There is a freedom lying beyond circumstance, derived from the direct intuition that life can be grounded upon its absorption in what is changeless amid change" -- Alfred North Whitehead -- Kevin O'Gorman (805) 650-6274 mailto:[EMAIL PROTECTED] Permanent e-mail forwarder: mailto:Kevin.O'[EMAIL PROTECTED] At school: mailto:[EMAIL PROTECTED] Web: http://www.cs.ucsb.edu/~kogorman/index.html Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html "There is a freedom lying beyond circumstance, derived from the direct intuition that life can be grounded upon its absorption in what is changeless amid change" -- Alfred North Whitehead
[HACKERS] Gram.y patches for better parenthesis handling.
Okay, here's my attempt at fixing the problems with parentheses in subqueries. It passes the normal 'runcheck' tests, and I've tried a few simple things like select 1 as foo union (select 2) order by foo; There are a few things that it doesn't do that have been talked about here at least a little: 1) It doesn't allow things like "IN(((select 1)))" -- the select here has to be at the top level. This is not new. 2) It does NOT preserve the odd syntax I found when I started looking at this, where a SELECT statement could begin with parentheses. Thus, (SELECT a from foo) order by a; fails. I have preserved the ability, used in the regression tests, to have a single select statement in what appears to be a RuleActionMulti (but wasn't -- the parens were part of select_clause syntax). In my version, this is a special form. This may cause some discussion: I have differentiated the two kinds of RuleActionMulti. Perhaps nobody knew there were two kinds, because I don't think the second form appears in the regression tests. This one uses square brackets instead of parentheses, but originally was otherwise the same as the one in parentheses. In this version of gram.y, the square bracket form treats SELECT statements the same as the other allowed statements. As discussed before on this list, psql cannot make sense out of the results of such a thing, but an application might. And I have designs on just such an application. ++ kevin -- Kevin O'Gorman (805) 650-6274 mailto:[EMAIL PROTECTED] Permanent e-mail forwarder: mailto:Kevin.O'[EMAIL PROTECTED] At school: mailto:[EMAIL PROTECTED] Web: http://www.cs.ucsb.edu/~kogorman/index.html Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html "There is a freedom lying beyond circumstance, derived from the direct intuition that life can be grounded upon its absorption in what is changeless amid change" -- Alfred North Whitehead --- gram.y.orig Thu Oct 26 13:13:04 2000 +++ gram.y Fri Oct 27 17:37:58 2000 @@ -124,14 +124,15 @@ DropGroupStmt, DropPLangStmt, DropSchemaStmt, DropStmt, DropTrigStmt, DropUserStmt, DropdbStmt, ExplainStmt, ExtendStmt, FetchStmt, GrantStmt, IndexStmt, InsertStmt, ListenStmt, LoadStmt, LockStmt, - NotifyStmt, OptimizableStmt, ProcedureStmt, ReindexStmt, + NotifyStmt, OptimizableStmt, ProcedureStmt + QualifiedSelectStmt, ReindexStmt, RemoveAggrStmt, RemoveFuncStmt, RemoveOperStmt, RemoveStmt, RenameStmt, RevokeStmt, RuleActionStmt, RuleActionStmtOrEmpty, RuleStmt, SelectStmt, SetSessionStmt, TransactionStmt, TruncateStmt, UnlistenStmt, UpdateStmt, VacuumStmt, VariableResetStmt, VariableSetStmt, VariableShowStmt, ViewStmt -%type node select_clause, select_subclause +%type node subquery, simple_select, select_head, set_select %type list SessionList %type node SessionClause @@ -174,19 +175,20 @@ result, OptTempTableName, relation_name_list, OptTableElementList, OptUnder, OptInherit, definition, opt_distinct, opt_with, func_args, func_args_list, func_as, - oper_argtypes, RuleActionList, RuleActionMulti, + oper_argtypes, RuleActionList, RuleActionMulti, + RuleActionOrSelectMulti, RuleActions, RuleActionBracket, opt_column_list, columnList, opt_va_list, va_list, sort_clause, sortby_list, index_params, index_list, name_list, from_clause, from_list, opt_array_bounds, expr_list, attrs, target_list, update_target_list, def_list, opt_indirection, group_clause, TriggerFuncArgs, - opt_select_limit + opt_select_limit, select_limit %type typnam func_arg, func_return, aggr_argtype %type booleanopt_arg, TriggerForOpt, TriggerForType, OptTemp -%type list for_update_clause, update_list +%type list opt_for_update_clause, for_update_clause, update_list %type booleanopt_all %type booleanopt_table %type booleanopt_chain, opt_trans @@ -2689,7 +2691,7 @@ RuleStmt: CREATE RULE name AS { QueryIsRule=TRUE; } ON event TO event_object where_clause - DO opt_instead RuleActionList + DO opt_instead RuleActions { RuleStmt *n = makeNode(RuleStmt); n-rulename = $3; @@ -2702,17 +2704,42 @@ } ; -RuleActionList: NOTHING { $$ = NIL; } - | SelectStmt{ $$ = makeList1($1); } - | RuleActionStmt{ $$ = makeList1($1); } - | '[' RuleActionMulti ']'
[HACKERS] Problem with installing as root
I'm back a bit from the tip of the CVS tree, so this might not be current, but as of around 8 October, installing as root gets in the way of later operations. I think this is an artifact of having Perl in the build. I did a 'su root' and a 'make install', then as my normal user self, attempted to do the regression tests. They failed and left the attached file in install.log. It points at a problem with permissions on a file left behind by the 'make install' in the Perl stuff. BTW, I'm back from the tip because of instability when I started looking at this stuff. Is it stable enough to install and run now? ++ kevin -- Kevin O'Gorman (805) 650-6274 mailto:[EMAIL PROTECTED] Permanent e-mail forwarder: mailto:Kevin.O'[EMAIL PROTECTED] At school: mailto:[EMAIL PROTECTED] Web: http://www.cs.ucsb.edu/~kogorman/index.html Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html "There is a freedom lying beyond circumstance, derived from the direct intuition that life can be grounded upon its absorption in what is changeless amid change" -- Alfred North Whitehead gmake[1]: Entering directory `/PG/OLAP/pgsql' gmake -C doc install gmake[2]: Entering directory `/PG/OLAP/pgsql/doc' mkdir /PG/OLAP/pgsql/src/test/regress/tmp_check/man mkdir /PG/OLAP/pgsql/src/test/regress/tmp_check/doc gmake[2]: Leaving directory `/PG/OLAP/pgsql/doc' gmake -C src install gmake[2]: Entering directory `/PG/OLAP/pgsql/src' gmake -C backend install gmake[3]: Entering directory `/PG/OLAP/pgsql/src/backend' gmake -C access all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/access' gmake -C common SUBSYS.o gmake[5]: Entering directory `/PG/OLAP/pgsql/src/backend/access/common' gmake[5]: `SUBSYS.o' is up to date. gmake[5]: Leaving directory `/PG/OLAP/pgsql/src/backend/access/common' gmake -C gist SUBSYS.o gmake[5]: Entering directory `/PG/OLAP/pgsql/src/backend/access/gist' gmake[5]: `SUBSYS.o' is up to date. gmake[5]: Leaving directory `/PG/OLAP/pgsql/src/backend/access/gist' gmake -C hash SUBSYS.o gmake[5]: Entering directory `/PG/OLAP/pgsql/src/backend/access/hash' gmake[5]: `SUBSYS.o' is up to date. gmake[5]: Leaving directory `/PG/OLAP/pgsql/src/backend/access/hash' gmake -C heap SUBSYS.o gmake[5]: Entering directory `/PG/OLAP/pgsql/src/backend/access/heap' gmake[5]: `SUBSYS.o' is up to date. gmake[5]: Leaving directory `/PG/OLAP/pgsql/src/backend/access/heap' gmake -C index SUBSYS.o gmake[5]: Entering directory `/PG/OLAP/pgsql/src/backend/access/index' gmake[5]: `SUBSYS.o' is up to date. gmake[5]: Leaving directory `/PG/OLAP/pgsql/src/backend/access/index' gmake -C nbtree SUBSYS.o gmake[5]: Entering directory `/PG/OLAP/pgsql/src/backend/access/nbtree' gmake[5]: `SUBSYS.o' is up to date. gmake[5]: Leaving directory `/PG/OLAP/pgsql/src/backend/access/nbtree' gmake -C rtree SUBSYS.o gmake[5]: Entering directory `/PG/OLAP/pgsql/src/backend/access/rtree' gmake[5]: `SUBSYS.o' is up to date. gmake[5]: Leaving directory `/PG/OLAP/pgsql/src/backend/access/rtree' gmake -C transam SUBSYS.o gmake[5]: Entering directory `/PG/OLAP/pgsql/src/backend/access/transam' gmake[5]: `SUBSYS.o' is up to date. gmake[5]: Leaving directory `/PG/OLAP/pgsql/src/backend/access/transam' gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/access' gmake -C bootstrap all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/bootstrap' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/bootstrap' gmake -C catalog all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/catalog' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/catalog' gmake -C parser all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/parser' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/parser' gmake -C commands all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/commands' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/commands' gmake -C executor all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/executor' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/executor' gmake -C lib all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/lib' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/lib' gmake -C libpq all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/libpq' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/libpq' gmake -C main all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/main' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/main' gmake -C nodes all gmake[4]: Entering directory `/PG/OLAP/pgsql/src/backend/nodes' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/PG/OLAP/pgsql/src/backend/nodes' gmake -C optimizer
Re: [HACKERS] Summary: what to do about INET/CIDR
Larry Rosenman [EMAIL PROTECTED] writes: I.E. for select network('207.158.72.0/24') I get 207.158.72.0 To my mind that should be done with host(), not network(). If you strip the masklen information then what you have is no longer a network specification, so expecting a function named network() to behave that way strikes me as bizarre. regards, tom lane
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
However, the upgrade gotcha has left a very bitter taste in more than one user's mouth. I'll not say more about that now, as I've said quite enough in the past. And I'm still trying to figure out enough of the internals of the storage manager to try to write the migration tools myself. But, I have other fish to fry right now, the biggest being cross-distribution RPM's. Actually, I would prefer to see how we can improve what we have before making a binary conversion utility that will have to be updated for every release. Meet Mr. Middle Layer. :-) The PostgreSQL spec file that controls the RPM build is one of the most complex ones in the RedHat distribution, AFAIK. There's the middle layer. It does quite a bit of finagling already. Yes, I suspected the RPM was the middle layer. To the extent we can make that easier, let's hear it. Tell us what you need to do, and what you can't do, and see if any of us can figure out how to make things easier. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Idea: cross-check versions during initdb
Tom Lane writes: But it's still dependent on the user's PATH to point to the right executables, no? This is what's puzzling me. There's code in there that tries to locate initdb and uses the executables and bki files (7.0 only) from the same tree. Evidently this code does not always work right, but that's what needs to be fixed. CMDNAME=`basename $0` ... # # Find out where we're located # if echo "$0" | grep '/' /dev/null 21 then # explicit dir name given PGPATH=`echo $0 | sed 's,/[^/]*$,,'` # (dirname command is not portable) else # look for it in PATH ('which' command is not portable) for dir in `echo "$PATH" | sed 's/:/ /g'` do # empty entry in path means current dir [ -z "$dir" ] dir='.' if [ -f "$dir/$CMDNAME" ] then PGPATH="$dir" break fi done fi -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Ok, here goes: Cool, a list. * Location-agnostic installation. Documentation (which I'll be happy to contribute) on that. Peter E is already working in this area. Getting the installation that 'make install' spits out massaged into an FHS compliant setup is the majority of the RPM's spec file. Well, we certainly don't want to make changes that make things harder or more confusing for non-RPM installs. How are they affected here? * Upgrades that don't require an ASCII database dump for migration. This can either be implemented as a program to do a pg_dump of an arbitrary version of data, or as a binary migration utility. Currently, I'm saving old executables to run under a special environment to pull a dump -- but it is far from optimal. What if the OS upgrade behind 99% of the upgrades makes it where those old executables can't run due to binary incompatibility (say I'm going from RedHat 3.0.3 to RedHat 7 -- 3.0.3, IIRC, as a.out...( and I know 3.0.3 didn't have PostgreSQL RPMs).)? What I could actually do to prevent that problem is build all of PostgreSQL's 6.1.x, 6.2.x, 6.3.x, 6.4.x, and 6.5.x and include the necessary backend executables as part of the RPM But I think you see the problem there. However, that would in my mind be better than the current situation, albeit taking up a lot of space. I really don't see the issue here. We can compress ASCII dump files, so the space need should not be too bad. Can't you just check to see if there is enough space, and error out if there is not? If the 2GIG limit is a problem, can't the split utility drop the files in 2gig chunks that can be pasted together in a pipe on reload? * A less source-centric mindset. Let's see, how to explain? The regression tests are a good example. You need make. You need the source installed, configured, and built in the usual location. You need portions of contrib. RPM's need to be installable on compiler-crippled servers for security. While the demand for regression testing on such a box may not be there, it certainly does give the user something to use to get standard output for bug reports. As a point, I run PostgreSQL in production on a compilerless machine. No compiler == more security. And Linux has enough security problems without a compiler being available :-(. Oh, and I have no make on that machine either. Well, no compiler? I can't see how we would do that without making other OS installs harder. That is really the core of the issue. We can't be making changes that make things harder for other OS's. Those have to be isolated in the RPM, or in some other middle layer. The documentation as well as many of the examples assume too much, IMHO, about the install location and the install methodology. Well, if we are not specific, things get very confusing for those other OS's. Being specific about locations makes things easier. Seems we may need to patch RPM installs to fix that. Certainly a pain, but I see no other options. I think I may have a solution for the library versioning problem. Rather than symlink libpq.so-libpq.so.2-libpq.so.2.x, I'll copy libpq.so.2.1 to libpq.so.2 and symlink libpq.so to that. A little more code for me. There is no real danger in version confusion with RPM's versioning and upgrade methodology, as long as you consistently use the RPMset. The PostgreSQL version number is readily found from an RPM database query, making the so version immaterial. Oh, that is good. The upgrade issue is the hot trigger for me at this time. It is and has been a major drain on my time and effort, as well as Trond's and others, to get the RPM upgrade working even remotely smoothly. And I am willing to code -- once I know how to go about doing it in the backend. Please give us more information about how the current upgrade is a problem. We don't hear that much from other OS's. How are RPM's specific, and maybe we can get a plan for a solution. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [HACKERS] Re: [GENERAL] 7.0 vs. 7.1 (was: latest version?)
Bruce Momjian wrote: However, the upgrade gotcha has left a very bitter taste in more than one user's mouth. I'll not say more about that now, as I've said quite enough in the past. And I'm still trying to figure out enough of the internals of the storage manager to try to write the migration tools myself. But, I have other fish to fry right now, the biggest being cross-distribution RPM's. Actually, I would prefer to see how we can improve what we have before making a binary conversion utility that will have to be updated for every release. Meet Mr. Middle Layer. :-) The PostgreSQL spec file that controls the RPM build is one of the most complex ones in the RedHat distribution, AFAIK. There's the middle layer. It does quite a bit of finagling already. Yes, I suspected the RPM was the middle layer. To the extent we can make that easier, let's hear it. Tell us what you need to do, and what you can't do, and see if any of us can figure out how to make things easier. Ok, here goes: * Location-agnostic installation. Documentation (which I'll be happy to contribute) on that. Peter E is already working in this area. Getting the installation that 'make install' spits out massaged into an FHS compliant setup is the majority of the RPM's spec file. * Upgrades that don't require an ASCII database dump for migration. This can either be implemented as a program to do a pg_dump of an arbitrary version of data, or as a binary migration utility. Currently, I'm saving old executables to run under a special environment to pull a dump -- but it is far from optimal. What if the OS upgrade behind 99% of the upgrades makes it where those old executables can't run due to binary incompatibility (say I'm going from RedHat 3.0.3 to RedHat 7 -- 3.0.3, IIRC, as a.out...( and I know 3.0.3 didn't have PostgreSQL RPMs).)? What I could actually do to prevent that problem is build all of PostgreSQL's 6.1.x, 6.2.x, 6.3.x, 6.4.x, and 6.5.x and include the necessary backend executables as part of the RPM But I think you see the problem there. However, that would in my mind be better than the current situation, albeit taking up a lot of space. * A less source-centric mindset. Let's see, how to explain? The regression tests are a good example. You need make. You need the source installed, configured, and built in the usual location. You need portions of contrib. RPM's need to be installable on compiler-crippled servers for security. While the demand for regression testing on such a box may not be there, it certainly does give the user something to use to get standard output for bug reports. As a point, I run PostgreSQL in production on a compilerless machine. No compiler == more security. And Linux has enough security problems without a compiler being available :-(. Oh, and I have no make on that machine either. The documentation as well as many of the examples assume too much, IMHO, about the install location and the install methodology. I think I may have a solution for the library versioning problem. Rather than symlink libpq.so-libpq.so.2-libpq.so.2.x, I'll copy libpq.so.2.1 to libpq.so.2 and symlink libpq.so to that. A little more code for me. There is no real danger in version confusion with RPM's versioning and upgrade methodology, as long as you consistently use the RPMset. The PostgreSQL version number is readily found from an RPM database query, making the so version immaterial. The upgrade issue is the hot trigger for me at this time. It is and has been a major drain on my time and effort, as well as Trond's and others, to get the RPM upgrade working even remotely smoothly. And I am willing to code -- once I know how to go about doing it in the backend. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [HACKERS] Summary: what to do about INET/CIDR
* Alex Pilosov [EMAIL PROTECTED] [001027 21:36]: On Fri, 27 Oct 2000, Larry Rosenman wrote: Not necessarily, especially for novices. Some people may want to store the netmask with the IP of a host (think ifconfig being AUTOGEN'd). For a single host? Or for a network of hosts? But yes, I see your point if a single host has x interfaces, and you are autogenerating ifconfig, with my proposal, you'd need to insert each network into networks table. Or a table of Routers, listed by IP's. I want to be able to efficently store the interface name, IP, Mask. With your proposal, I can't store it as one row in one table. With Tom's proposal, I can. 99% of people who would be storing IP addresses into postgres database really do not know nor care what is a netmask on that IP. Only people who would care are ones who store their _internal_ addresses (read: addresses used on networks they manage). There is usually a very limited number of such networks (1000). I disagree. I'm an ISP, and the network engineer for same. I have a BOATLOAD of Netblocks from ARIN and providers in a BUNCH of sizes. I need to subnet them out to customers and for internal use. I like Tom's latest proposal. This one LOSES functionality for ME. Explain how does it lose functionality? I may need to list an interface in their net, with their netmask, but not have it in my networks table. I don't think that the system should supply a networks table, per se. I have much more than 1000's networks in my shop. Please don't FORCE me to your model. I like Tom's proposal, especially from the "least surprise" aspects. It makes no sense to have in database both 10.0.0.1/24 and 10.0.0.2/16. None whatsoever. Not necessarily, especially with RFC1918 addresses, and reuse within different unconnected networks of the SAME enterprise. Makes no sense to have them in one table, anyway, I stand corrected. For people in situation you describe, you can have a second table of networks, and second function to look up networks in that table. See above. Please don't force me to your paradigm. This does NOT apply to CIDR datatype, as there are real applications (such as storing routing tables) where you would care about netmask, but won't care about a host part. What I am suggesting is we do the following: a) inet will NOT have a netmask Please DONT. See above. b) all the fancy comparison functions on inet should be deleted. (leave only = = = ) Maybe. I think they should stay, but I'm one lowly network engineer. c) the only things you can do on inet is to convert it to 4 octets (of int1), to a int8, and to retrieve its network from a table of networks. d) have a table, 'networks' (or any other name, maybe pg_networks?) which would have one column 'network', with type cidr. create table networks (network cidr not null primary key) Why? Because netmask is a property of a network, not of an IP address. e) have a function network(inet) which would look up the address in a table of networks using longest-prefix-match. I.E. something similar to: No need. Let the user do it themselves. Similar to what we did for macaddr's back in the summer. Yeah, it can be user-defined (or a contrib), no question about it, and for people who have more than one table of networks, it will _have_ to be user-defined. Actually, that's probably what I'll end up doing on my own. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] Summary: what to do about INET/CIDR
Please read below if the whole thing with inet/cidr doesn't make you puke yet ;) The semi-longish proposal is at the bottom. On Fri, 27 Oct 2000, Tom Lane wrote: Alex Pilosov [EMAIL PROTECTED] writes: We should have as much error-checking as possible. How so? Without a netmask you have no way to know if it's a broadcast address or not. 10.0.0.255/32 might be a perfectly valid host address in, say, 10.0/16. But 10.0.0.255/24 is recognizably the broadcast address for 10.0.0/24 (and not for any other network...) Right, that's what I'm trying to say: It shouldn't allow you to use 10.0.0.255/24 as a host address, but it should allow you to use 10.0.0.255/16 (ie. broadcast() function must return a value with /32 mask) I don't disagree with that part, but that's only because I see broadcast() as mainly a display convenience. If we had a larger and more thoroughly worked out set of inet/cidr operators, I'd be inclined to argue that broadcast('10.0.0.0/24') should yield 10.0.0.255/24 for computational convenience. Then we'd need to offer a separate function that would let you strip off the netmask for display purposes (actually host() would do for that...) Actually, now that I think longer about the whole scheme in terms of actual IP experience, here are my ideas: a) inet is crock. I don't know anyone who would need to _care_ about a netmask of a host, who wouldn't have a lookup table of networks/masks. (Think /etc/hosts, and /etc/netmasks). Storing a netmask of a network in a inet actually violates the relational constraints: netmask is not a property of an IP address, its a property of a network. 99% of people who would be storing IP addresses into postgres database really do not know nor care what is a netmask on that IP. Only people who would care are ones who store their _internal_ addresses (read: addresses used on networks they manage). There is usually a very limited number of such networks (1000). It makes no sense to have in database both 10.0.0.1/24 and 10.0.0.2/16. None whatsoever. This does NOT apply to CIDR datatype, as there are real applications (such as storing routing tables) where you would care about netmask, but won't care about a host part. What I am suggesting is we do the following: a) inet will NOT have a netmask b) all the fancy comparison functions on inet should be deleted. (leave only = = = ) c) the only things you can do on inet is to convert it to 4 octets (of int1), to a int8, and to retrieve its network from a table of networks. d) have a table, 'networks' (or any other name, maybe pg_networks?) which would have one column 'network', with type cidr. create table networks (network cidr not null primary key) e) have a function network(inet) which would look up the address in a table of networks using longest-prefix-match. I.E. something similar to: select network from networks where $1network order by network_prefix(network) desc limit 1; I realise that this sounds a little bit strange after all the arguments about inet, but if you think about it, this is the only sane way to deal with these datatypes. Right now, the datatypes we have look and sound pretty but are pretty much useless in reality. Yes, it is nice to be able to store a netmask with every IP address, it is useless in reality. (Yes, please, someone tell me if you are using inet with netmasks and you actually like it). I'd especially like to get input of Marc on this, as he's both a core team member and has actual networking background...Oh yeah, if Marc can comment on whether 10/8 or 10.0.0.0/8 is a proper way to represent a network, it'd be great too :)
[HACKERS] Second proposal: what to do about INET/CIDR
Sigh ... I was really hoping not to get drawn into fixing these issues for 7.1, but ... It seems like much of the problem is that there isn't any easy way to choose between CIDR-style display format ('127.1/16') and INET-style format ('127.1.0.0/16'). We need to bite the bullet and add conversion functions, so that people can pick which they want. Picking and choosing among the ideas discussed, here's my stab at a complete proposal: 1. CIDR-type values will be displayed in "abbreviated" format, eg "127.1/16". Since a CIDR value is no longer allowed to have any nonzero bits to the right of the mask, no information is lost by abbreviation. The /n will appear even when it is 32. 2. INET-type values will always be displayed with all octets, eg "127.1.0.0/16". The /n part will be suppressed from display if it is 32. INET will accept any octet pattern as an address together with any netmask length from 1 to 32. 3. We will add explicit functions cidr(inet) and inet(cidr) to force the data type to one or the other style, thus allowing selection of either display style. Note that cidr(inet) will raise an error if given something with nonzeroes to the right of the netmask. 4. The function host(inet) will now return inet not text. It will take the address octets of the given value but force the netmask to 32 and the display type to INET. So for example host('127.1/16'::cidr) will yield '127.1.0.0/32'::inet, which if displayed will appear as just '127.1.0.0', per item 2. 5. The function broadcast(inet) will now return inet not text. It will take the given address octets and force the bits to the right of the netmask to 1. The display type will be set to inet. After more thought about my last message, I am inclined to have it return the same masklength as the input, so for example broadcast('127.1/16') would yield '127.1.255.255/16'::inet. If you want the broadcast address displayed without a netmask notation, you'd need to write host(broadcast(foo)). Alternatively, we could say that broadcast() always returns masklen 32, but I think this loses valuable functionality. 6. The function network(inet) will now return cidr not text. The result has the same masklen as the input, with bits to the right of the mask zeroed to ensure it is a valid cidr value. The display type will be set to cidr. For example, network('127.1.2.3/16') will yield '127.1/16'::cidr. To get this result displayed in inet format, you'd write inet(network(foo)) --- yielding '127.1.0.0/16'. If you want it displayed with no netmask, write host(network(foo)) --- result '127.1.0.0'. 7. The function netmask(inet) will now return inet not text. It will return octets with 1s in the input's netmask, 0s to the right, and output display type and masklen set to inet and 32. For example, netmask('127.1/16') = '255.255.0.0/32'::inet which will display as '255.255.0.0'. (I suppose a really anal definition would keep the input masklen, forcing you to write host(netmask(foo)) to get a display without "/n". But I don't see any value in that for netmasks.) 8. Because we still consider inet and cidr to be binary-equivalent types, all of these functions will be applied to either inet or cidr columns without any type conversion. (In other words, cidr(inet) and inet(cidr) will only be applied if *explicitly* invoked.) I am not convinced whether this is a good thing. In this proposal, no system function except display will care whether its input is inet or cidr, so the lack of conversion doesn't matter. But in the long run it might be better to remove the binary-equivalence. Then, for example, host(cidr) would be implemented as host(inet(cidr)), costing an extra function call per operation. Right now I don't think we need to pay that price, but maybe someday we will. Thoughts? regards, tom lane
[HACKERS] Notice: rpath in use
Picking up from a discussion several months back, the build now uses the -rpath option (or -Wl,-R or whatever yours uses) to store the location of the shared libraries into the executables and the shared libraries themselves. That means that the LD_LIBRARY_PATH/ld.so.conf thing should no longer be necessary. When making a binary package you might not want to use this. Use "configure --disable-rpath" to disable it. Doesn't work on all platforms, though. (OTOH, if you're using hpux, osf/cc, or irix5 then this is old news for you, but now it's a feature across the board.) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Summary: what to do about INET/CIDR
* Tom Lane [EMAIL PROTECTED] [001027 17:54]: Alex Pilosov [EMAIL PROTECTED] writes: On Fri, 27 Oct 2000, Tom Lane wrote: BTW, does it strike anyone else as peculiar that the host(), broadcast(), network(), and netmask() functions yield results of type text, rather than type inet? I absolutely agree, except for network(), which should return cidr. We could do that, but if we did, it would print out per CIDR format (eg, '192.1/16') whereas both you and Larry have been saying you want a way to produce '192.1.0.0/16'. Perhaps we need two functions, one to produce the network in CIDR notation and one to produce it in INET notation. I'd agree with this. For that matter, perhaps we should not change host() to accept CIDR but instead provide a separate function that does what I proposed host() should do with a CIDR. Not sure. As I mentioned in another email, should inet datatype really care whether host part is all-ones or all-zeros and reject that? I'm inclined to think not, partially because that would mean that the results of broadcast() and network() could *NOT* be considered valid INET values. True. The way I'm visualizing this, INET is a generalized type that will store any 4-octet address plus any netmask width from 1 to 32. This includes not only host addresses, but network specs and broadcast addresses. CIDR is a subset type that only accepts valid network specs (ie, no nonzero address bits to the right of the netmask). There is no subset type that corresponds to "valid host addresses only" --- if there were, it would be a subset of INET but would have no valid values in common with CIDR. We could make such a type but I dunno if it's worth the trouble. I believe this is true. Now if we could get the output stuff so there are BOTH ways of displaying the data (we seem to need both, from the statements we get each time this has been brought up), such that you can freely move between the 4-octet and short-octet (for lack of a better term) version of a CIDR network spec. Thanks for any consideration, and if this could make 7.1, I'd be most appreciative... Larry regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
[HACKERS] Zoltan, call home!
Hi Zoltan. Your recent mail has had a reply-to of Kovacs Zoltan Sandor [EMAIL PROTECTED] and a couple of messages from me to you have bounced. It looks like it reaches the machine, but says that the "user is unknown". Is that really a good address for you? Hope to hear from you... - Thomas
Re: [HACKERS] Summary: what to do about INET/CIDR
one more small request: int8_inet(inet) and inet_int8(int8): functions to convert an inet to an int8 and back. (not an int4, since postgres int4s are signed) This allows me to do some additional manipulations on values. (ie. given a host, determine its default gateway, for us, it is always first host on that network, this could be implemented as inet_int8(int8_inet(network(x))+1), or splitting a cidr into two halves, -alex
Re: [HACKERS] Summary: what to do about INET/CIDR
e) have a function network(inet) which would look up the address in a table of networks using longest-prefix-match. I.E. something similar to: No need. Let the user do it themselves. Similar to what we did for macaddr's back in the summer. Yeah, it can be user-defined (or a contrib), no question about it, and for people who have more than one table of networks, it will _have_ to be user-defined. It seems clear to me that this mapping is best left to the user. A more interesting question is whether the system needs to provide any assisting functions that aren't there now. The lookup function you guys are postulating seems like it would be (in the simple cases) create function my_network(inet) returns cidr as 'select network from my_networks where ???' Maybe it's too late at night, but I'm having a hard time visualizing what the ??? condition is and whether any additional system-level functions are needed to make it simple/efficient. regards, tom lane
Re: [HACKERS] Re: [GENERAL] A rare error
(SELECT * INTO newtable FROM table1) UNION (SELECT * FROM table2); Possibly a silly (and definitely not standards-conformant) suggestion: Maybe grammar should be amended to allow for (SELECT * FROM table1) UNION (SELECT * FROM table2) INTO newtable i.e. union_expr: (select_expr) union (union_expr) [into into_table] Notice the INTO? Doesn't this seem like an odd place for it, in what appears to be a subordinate query? Where else would it go? How would it grab you in an expression with five or more levels of parens? How about five levels of parens and a complicated targetlist before you get to the INTO? What I'm suggesting is that the parens be allowed only on the right hand side of the set operations. How does that strike you? regards, tom lane
Re: [HACKERS] Re: [GENERAL] A rare error
"Kevin O'Gorman" [EMAIL PROTECTED] writes: This just occurred to me: how would you sort the results of this query? The path of least resistance from the way things work now would be most non-obvious: put the ORDER BY on the leftmost query. It looks like this (SELECT * INTO newtable FROM table1 ORDER BY field1) UNION (SELECT * FROM table2); And I have to say that's about the ugliest construct I've seen in a pretty ugly language. No. This is not SQL92: the spec is perfectly definite that it does not allow such a construct. What it allows is SELECT ...foo... UNION SELECT ...bar... ORDER BY baz and here the ORDER BY is to be interpreted as ordering the results of the UNION, not the results of the righthand sub-SELECT. This is one of the cases that you'll need to be careful to get right when rejiggering the syntax. Purely as an implementation issue, the current gram.y code drills down to find the leftmost sub-SELECT and attaches the outer-level ORDER BY clause to that Select node. analyze.c later extracts the ORDER BY and attaches it to a top-level Query node that doesn't correspond to any node existing in the gram.y output. That's all behind the scenes, however, and shouldn't be exposed to the tender eyes of mere mortal users. AFAICS, the input (SELECT * FROM table1 ORDER BY field1) UNION (SELECT * FROM table2); should either be rejected (as current sources and all prior releases would do) or else treat the ORDER BY as ordering the leftmost subselect before it feeds into the UNION. There is no point in such an ORDER BY by itself, since UNION will feel free to reorder the tuples --- but OTOH something like (SELECT ... ORDER BY ... LIMIT 1) UNION (SELECT ...) seems entirely sensible and useful to me. In short: there is a considerable difference between (SELECT ...foo... UNION SELECT ...bar...) ORDER BY baz SELECT ...foo... UNION (SELECT ...bar... ORDER BY baz) (SELECT ...foo... ORDER BY baz) UNION SELECT ...bar... and any attempt to allow ORDER BY on subqueries will have to be careful to keep these straight. This may well mean that you need to rejigger the output structures of gram.y as well as the grammar itself. regards, tom lane
Re: [HACKERS] Re: [GENERAL] A rare error
"Kevin O'Gorman" [EMAIL PROTECTED] writes: All that being said, I'm not sure enough notice has been taken of one aspect of the changes already in place, and likely to become more pronounced. It may be okay with everybody, but I don't want it to be a big surprise: queries may no longer begin with SELECT, but instead with an arbitrary number of left parens. That's no surprise, because it's been true for a long time. It's certainly true in the 6.5 grammar, which is the oldest I have on hand. In some cases, the semantics gets lost in the syntax. Consider: (SELECT * INTO newtable FROM table1) UNION (SELECT * FROM table2); Notice the INTO? Doesn't this seem like an odd place for it, in what appears to be a subordinate query? Where else would it go? How would it grab you in an expression with five or more levels of parens? How about five levels of parens and a complicated targetlist before you get to the INTO? Agreed, it's pretty ugly. This one is only partially SQL92's fault, since it defines SELECT ... INTO for just a limited context: select statement: single row ::= SELECT [ set quantifier ] select list INTO select target list table expression (select target list here appears to mean a list of local variables in a calling program, a la ECPG, and doesn't really have anything to do with the table-destination semantics that Postgres puts on the construct. But I digress.) The above restricted form of SELECT does not admit UNION/INTERSECT/EXCEPT constructs at the top level. Postgres has generalized this to allow INTO target in a UNION/etc construct, which means the word SELECT is not necessarily going to be the very first thing you see. We do require the INTO to be in the leftmost primitive SELECT, so the only thing you can really see in front of "SELECT selectlist INTO" is some number of left parentheses. To me the potential hairiness of the selectlist seems like a much bigger readability issue than the leading parens --- but we got that part of the syntax straight from SQL92. What I'm suggesting is that the parens be allowed only on the right hand side of the set operations. How does that strike you? Will not do, first because EXCEPT is not symmetric, and second because SQL92 does not describe any such limitation. regards, tom lane
Re: [HACKERS] Gram.y patches for better parenthesis handling.
"Kevin O'Gorman" [EMAIL PROTECTED] writes: 2) It does NOT preserve the odd syntax I found when I started looking at this, where a SELECT statement could begin with parentheses. Thus, (SELECT a from foo) order by a; fails. Um, as a general rule that's not an acceptable limitation. Consider (SELECT foo EXCEPT SELECT bar) INTERSECT SELECT baz; Without parens this will mean something quite different, since INTERSECT has higher precedence than EXCEPT. Also, a leading paren is clearly legal according to SQL92 --- trace for example the productions direct select statement: multiple rows query expression non-join query expression non-join query term non-join query primary ::= left paren non-join query expression right paren (UNION/EXCEPT structures are non-join query expression in this hierarchy.) The reason that making this grammar yacc-compatible is so hard is precisely that leading parens must sometimes be part of the SELECT structure, whereas extraneous parens need to be kept out of it. regards, tom lane
[HACKERS] Re: [SQL] renaming columns... danger?
Just tested this on latest devel. version, and there does seem to be a problem. []$ psql test Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit test=# select version(); version PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) test=# create table a ( aa serial primary key ); NOTICE: CREATE TABLE will create implicit sequence 'a_aa_seq' for SERIAL column 'a.aa' NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'a_pkey' for table 'a' CREATE test=# alter TABLE a RENAME aa to new_aa; ALTER []$ pg_dump test -- -- Selected TOC Entries: -- \connect - gaf -- -- TOC Entry ID 2 (OID 20352) -- -- Name: "a_aa_seq" Type: SEQUENCE Owner: gaf -- CREATE SEQUENCE "a_aa_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ; -- -- TOC Entry ID 4 (OID 20370) -- -- Name: a Type: TABLE Owner: gaf -- CREATE TABLE "a" ( "new_aa" integer DEFAULT nextval('"a_aa_seq"'::text) NOT NULL, PRIMARY KEY ("aa") ); -- -- Data for TOC Entry ID 5 (OID 20370) TABLE DATA a -- -- Disable triggers UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'a'; COPY "a" FROM stdin; \. -- Enable triggers BEGIN TRANSACTION; CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint); INSERT INTO "tr" SELECT C."relname", count(T."oid") FROM "pg_class" C, "pg_trigger" T WHERE C."oid" = T."tgrelid" AND C."relname" ~* 'a' GROUP BY 1; UPDATE "pg_class" SET "reltriggers" = TMP."tmp_reltriggers" FROM "tr" TMP WHERE "pg_class"."relname" = TMP."tmp_relname"; DROP TABLE "tr"; COMMIT TRANSACTION; -- -- TOC Entry ID 3 (OID 20352) -- -- Name: "a_aa_seq" Type: SEQUENCE SET Owner: -- SELECT setval ('"a_aa_seq"', 1, 'f'); Michael Teter wrote: hi. I just discovered that doing an alter table ... alter column (to rename a column) does not do a complete rename throughout the database. for example, say you have table a, with columns b and c. b is your primary key. now rename b to new_b. if you do a dump of the schema after you rename, you'll find that you can't reload that schema because at the bottom of the definition of table a you have PRIMARY KEY ("b"). shouldn't rename update any index and key definitions? also, and this may actually the source of the problem, while scanning my full (schema and data) dump, I noticed that the contents of table pga_layout also had the old values of columns that I have renamed. I'm very frightened right now, because I'm rather dependent upon my database right now. I don't like the thought that my database is corrupt at the schema level. michael __ Do You Yahoo!? Yahoo! Messenger - Talk while you surf! It's FREE. http://im.yahoo.com/ -- Poorly planned software requires a genius to write it and a hero to use it. Grant Finnemore BSc(Eng) (mailto:[EMAIL PROTECTED]) Software Engineer Universal Computer Services Tel (+27)(11)712-1366PO Box 31266 Braamfontein 2017, South Africa Cell (+27)(82)604-553620th Floor, 209 Smit St., Braamfontein Fax (+27)(11)339-3421Johannesburg, South Africa
Re: [HACKERS] Summary: what to do about INET/CIDR
Tom Lane writes: 1. A data value like '10.1.2.3/16' is a legal INET value (it implies the host 10.1.2.3 in the network 10.1/16) but not a legal CIDR value. Hence, cidr_in should reject such a value. Up to now it hasn't. Nod. 2. We do not have a datatype corresponding strictly to a host address alone --- to store a plain address, use INET and let the mask width default to 32. inet_out suppresses display of a "/32" netmask (whereas cidr_out does not). Inet is supposed to be host address, with optional network specification. I also have in my notes (some might have been fixed since): * inet output is broken = 127.0.0.1/8 * no cast function to "text" available (what about host()?) * equality/distinctness is broken in certain cases = select '10.0.0.1/27'::inet='10.0.0.2/27'::inet; returns true * operator commutators and negators are incorrect * ouput functions apparently null-terminate their result = select host('10.0.0.1')='10.0.0.1'; returns false * comparing inet and cidr is not well defined * should '127.0.0.1/24'::cidr fail? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Summary: what to do about INET/CIDR
On Fri, 27 Oct 2000, Tom Lane wrote: Larry Rosenman [EMAIL PROTECTED] writes: OK, what I really meant was a way to coerce a CIDR entity to INET so that host() can work with a CIDR type to print all 4 octets. Hm. I don't see any really good reason why host() rejects CIDR input in the first place. What's wrong with producing the host address that corresponds to extending the CIDR network address with zeroes? _maybe_ cuz this is an invalid address. (an address cannot have all-zeros or all-ones host part). On other hand, postgres doesn't enforce that in inet_in, so its inconsistent to enforce it there... Currently you can't coerce a CIDR type to INET. Well you can, but it doesn't *do* anything. One of the peculiarities of these two types is that the cidr-vs-inet flag is actually stored in the data value. The type-system differentiation between CIDR and INET is a complete no-op for everything except initial entry of a value (ie, conversion of a text string to CIDR or INET); all the operators that care (which is darn few ... in fact it looks like host() is the only one!) look right at the value to see which type they've been given. So applying a type coercion may make the type system happy, but it doesn't do a darn thing to the bits, and thus not to the behavior of subsequent operators either. I have not yet figured out if that's a good thing or a bad thing ... Probably cidr_inet should make a copy instead of just "blessing" the original value? -alex
Re: [HACKERS] Summary: what to do about INET/CIDR
On Fri, 27 Oct 2000, Tom Lane wrote: The way I'm visualizing this, INET is a generalized type that will store any 4-octet address plus any netmask width from 1 to 32. This includes not only host addresses, but network specs and broadcast addresses. CIDR is a subset type that only accepts valid network specs (ie, no nonzero address bits to the right of the netmask). There is no subset I really don't think it should. We should have as much error-checking as possible. Broadcast address does _not_ have a netmask, i.e. 10.0.0.255/24 does not make sense as inet, it should be 10.0.0.255/32 (ie. broadcast() function must return a value with /32 mask) type that corresponds to "valid host addresses only" --- if there were, it would be a subset of INET but would have no valid values in common with CIDR. We could make such a type but I dunno if it's worth the trouble.
Re: [HACKERS] Idea: cross-check versions during initdb
Larry Rosenman writes: Sounds like an easy one for a newbie to pick up. Let me look at it, but I think I'd like dibs on it. Actually, initdb of 7.1 gets the directory location of the bootstrap files wired in at build time. The only way to override it is to use the -L option. So the problem seems a lot less grave that way. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] Summary: what to do about INET/CIDR
* Tom Lane [EMAIL PROTECTED] [001027 17:41]: Larry Rosenman [EMAIL PROTECTED] writes: Fine, but host() rejects CIDR types right now What's your point? network() doesn't behave the way you want right now, either. Fine, network() can return CIDR (207.158.72/24), but allow host(cidr) to print all 4 octets without the mask. Larry regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 (voice) Internet: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [HACKERS] Summary: what to do about INET/CIDR
Alex Pilosov [EMAIL PROTECTED] writes: On Fri, 27 Oct 2000, Tom Lane wrote: BTW, does it strike anyone else as peculiar that the host(), broadcast(), network(), and netmask() functions yield results of type text, rather than type inet? I absolutely agree, except for network(), which should return cidr. We could do that, but if we did, it would print out per CIDR format (eg, '192.1/16') whereas both you and Larry have been saying you want a way to produce '192.1.0.0/16'. Perhaps we need two functions, one to produce the network in CIDR notation and one to produce it in INET notation. For that matter, perhaps we should not change host() to accept CIDR but instead provide a separate function that does what I proposed host() should do with a CIDR. Not sure. As I mentioned in another email, should inet datatype really care whether host part is all-ones or all-zeros and reject that? I'm inclined to think not, partially because that would mean that the results of broadcast() and network() could *NOT* be considered valid INET values. The way I'm visualizing this, INET is a generalized type that will store any 4-octet address plus any netmask width from 1 to 32. This includes not only host addresses, but network specs and broadcast addresses. CIDR is a subset type that only accepts valid network specs (ie, no nonzero address bits to the right of the netmask). There is no subset type that corresponds to "valid host addresses only" --- if there were, it would be a subset of INET but would have no valid values in common with CIDR. We could make such a type but I dunno if it's worth the trouble. regards, tom lane
Re: [HACKERS] Summary: what to do about INET/CIDR
Alex Pilosov [EMAIL PROTECTED] writes: We should have as much error-checking as possible. Only possible with a much tighter definition of what the intended use of each type is. For example, you seem to be saying that broadcast addresses aren't valid inet values, with which I do not agree unless there is another type that they can be part of. My inclination is to leave INET with the range of valid values it currently has, and to let people apply column constraints if they want to restrict a particular column to, say, valid host addresses, or valid broadcast addresses, or whatever. Broadcast address does _not_ have a netmask, i.e. 10.0.0.255/24 does not make sense as inet, it should be 10.0.0.255/32 How so? Without a netmask you have no way to know if it's a broadcast address or not. 10.0.0.255/32 might be a perfectly valid host address in, say, 10.0/16. But 10.0.0.255/24 is recognizably the broadcast address for 10.0.0/24 (and not for any other network...) (ie. broadcast() function must return a value with /32 mask) I don't disagree with that part, but that's only because I see broadcast() as mainly a display convenience. If we had a larger and more thoroughly worked out set of inet/cidr operators, I'd be inclined to argue that broadcast('10.0.0.0/24') should yield 10.0.0.255/24 for computational convenience. Then we'd need to offer a separate function that would let you strip off the netmask for display purposes (actually host() would do for that...) regards, tom lane
Re: [HACKERS] Summary: what to do about INET/CIDR
Larry Rosenman [EMAIL PROTECTED] writes: Fine, but host() rejects CIDR types right now What's your point? network() doesn't behave the way you want right now, either. regards, tom lane
Re: [HACKERS] Summary: what to do about INET/CIDR
On Fri, 27 Oct 2000, Tom Lane wrote: A more interesting question is whether the system needs to provide any assisting functions that aren't there now. The lookup function you guys are postulating seems like it would be (in the simple cases) create function my_network(inet) returns cidr as 'select network from my_networks where ???' as in my mail: select network from my_network where network$1 order by network_prefix(network) desc limit 1; (i.e. if many networks cover the ip address, pick the one with longest prefix). The only hard question here, how to properly index this table. This sounds like a perfect application of user-defined index method. I need to look up documentation on how they work... However, this probably won't pose a major problem in production: the networks table will be relatively small. Maybe it's too late at night, but I'm having a hard time visualizing what the ??? condition is and whether any additional system-level functions are needed to make it simple/efficient. Actually, you can scratch my proposal. I realise it could be inconvenient for some people. I'll be probably putting all my hosts as inet::xxx/32, have the above lookup function to get real network, and do operations on that.
Re: [HACKERS] Summary: what to do about INET/CIDR
On Fri, 27 Oct 2000, Tom Lane wrote: BTW, does it strike anyone else as peculiar that the host(), broadcast(), network(), and netmask() functions yield results of type text, rather than type inet? Seems like it'd be considerably more useful if they returned values of type inet with masklen = 32 (except for network(), which would keep the original masklen while coercing bits to its right to 0). I absolutely agree, except for network(), which should return cidr. (after all, this is the network). As I mentioned in another email, should inet datatype really care whether host part is all-ones or all-zeros and reject that? It would make sense to me (10.0.0.0/8::inet is not a valid address, but 10.0.0.0/8::cidr is), but it would break some people's scripts... I'm talking here from a perspective of a network provider with P knowledge...I'm sure Marc can chime in here... -alex
[HACKERS] Re: [GENERAL] A rare error
[EMAIL PROTECTED] wrote: "Kevin O'Gorman" [EMAIL PROTECTED] writes: Anyway, the bottom line of all this rambling is that if you can get rid of the distinction between SelectStmt and select_clause altogether, that would be fine with me. You might consider looking at whether you can write two nonterminals: a SELECT construct that has no outer parens, and then an additional construct subselect: SelectStmt | '(' subselect ')' which would be used for all the sub-select nonterminals in SelectStmt itself. I'm headed in that direction. I've been calling it 'subquery'. OTOH, maybe we don't want NOT IN (((SELECT foo FROM bar))). If we can't do that then we're still going to get complaints, I think. The original bug report in this thread was specifically that the thing didn't like redundant parentheses; we should try to remove that restriction in all contexts not just some. All that being said, I'm not sure enough notice has been taken of one aspect of the changes already in place, and likely to become more pronounced. It may be okay with everybody, but I don't want it to be a big surprise: queries may no longer begin with SELECT, but instead with an arbitrary number of left parens. In some cases, the semantics gets lost in the syntax. Consider: (SELECT * INTO newtable FROM table1) UNION (SELECT * FROM table2); Notice the INTO? Doesn't this seem like an odd place for it, in what appears to be a subordinate query? Where else would it go? How would it grab you in an expression with five or more levels of parens? How about five levels of parens and a complicated targetlist before you get to the INTO? What I'm suggesting is that the parens be allowed only on the right hand side of the set operations. How does that strike you? regards, tom lane -- Kevin O'Gorman (805) 650-6274 mailto:[EMAIL PROTECTED] Permanent e-mail forwarder: mailto:Kevin.O'[EMAIL PROTECTED] At school: mailto:[EMAIL PROTECTED] Web: http://www.cs.ucsb.edu/~kogorman/index.html Web: http://trixie.kosman.via.ayuda.com/~kevin/index.html "There is a freedom lying beyond circumstance, derived from the direct intuition that life can be grounded upon its absorption in what is changeless amid change" -- Alfred North Whitehead