Re: [HACKERS] pg_autovacuum next steps
On Sun, Mar 21, 2004 at 05:32:59PM -0500, Matthew T. O'Connor wrote: Lately I have been thinking about the next steps for the pg_autovacuum daemon. I have written up a document that describes what I'm planning to do next. Please read the attached and response as I would really like some feedback. Hi, Matthew. For our uses, we found that pg_autovacuum did not behave as expected with vacuum_threshold set to 0. For our purposes, we have a very good idea of how many tuples need to be slurped up over a given interval, and would like the autovacuum daemon to simply go through and vacuum when it hits a, er, threshold. Obligatory explanation: We were doing this with cron, but when something gets really ugly, cron is not as intuitive as autovacuum. Having this in the postmaster would be fine by me (and in fact, I would even suggest that it belongs there), but I really need to have more flexibility or we are going to (read: I will be tasked with) be writing our own. Sorry for the terseness -- and I haven't had a chance to go over the whole thread; we're moving from 7.3.2 to 7.4.2 on the big database this week. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Systems Programmer You have a spell checker. It is called a brain. Please use it. - Chris Lyth ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_autovacuum next steps
On Mon, Mar 22, 2004 at 04:50:57PM -0500, Matthew T. O'Connor wrote: Could you please explain this better, I don't really understand what the problem is. If you want pg_autovacuum to perform a vacuum on a table that has had exactly X updates no matter what, you can just run it with -V0 -vX (where X is the vacuum threshold) same thing can be done for analyze. Are you saying that you did this and it did not work as expected? My experience was it did not vacuum at all. Everyone agrees it should be in the backend eventually the questions were just if it was ready, and how or if it should change when it goes in. It is very simple code, I'm not sure what is not ready about it. If people don't want to use it, they don't have to. Many databases can go days to weeks without vacuuming. alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator You cannot invade the mainland United States. There would be a rifle behind each blade of grass. - Admiral Isoroku Yamamoto ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Timing of 'SELECT 1'
On Wed, Mar 10, 2004 at 11:43:48AM -0500, Bruce Momjian wrote: The problem with gprof is that I am going to see all the backend startup stuff too, no? Is there a way to get a dump just the run of the query? I was sort of lurking on this thread, waiting to see what became of it. Did nobody actually come to a conclusion on what that last msec was from? Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Slony-I makes progress
On Fri, Mar 05, 2004 at 12:47:23AM +0100, Jochem van Dieten wrote: I personally don't think that a GUI tool should be the province of the Slony project. Seriously. I think that Slony should focus on a I very much agree with this, but this is Jan's baby, so I didn't say anything. I have personally never used a GUI with a postgres database (well, okay, I used one for a bit to troubleshoot a problem my boss was having with a pg node once), and I don't really plan to. I guess I was unaware this is a common usage pattern. command-line api and catalogs, and allow the existing GUI projects to build a slony-supporting interface. Why a command line api? I believe it would make sense to be able to configure and control all nodes of the entire system from psql connected to any of the nodes. That would also facilitate the existing GUI projects in adding a Slony-manager. In theory, most of the stuff that Slony is doing is within the database, and as such, could be configurable via stored procedures. I see a few problems with this. First off, it is not possible to configure external applications (such as erserver has a daemon) from within the database except through the modification of tables within the database which are monitored by said application. Second, it increases the footprint of Slony on the database. I am fairly uneasy about adding more tables, functions, and triggers to my (already quite taxed) production database. To add further functions for configuration, as well as related tables and triggers, makes my job managing the database more difficult. Additionally, those commands are queries. For something as trivial as configuration data, I would much rather not be issuing queries against an already very busy database. I am much more comfortable with the principle of external configuration files and programs. Lastly, and I may be the black sheep here, I don't find sql to be particularly useful for doing things that require a complex grammar. In this instance, I don't want to have to do something like: production=# select slony_config_setval( 'log_dir', '/data/slony_logs'); to manage the configuration. Obviously, this could be worse than the above example. I don't understand the opposition to an external set of tools (even a gui if need be). It seems to me, that until the postmaster has some kind of native replication, all replication efforts will be based on external programs. As such, they should be configured externally, and be treated as any other daemon would be. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator v shpxvat ungr jvaqbjf naq v ubcr ovyy tngrf oheaf va uryy. - Ronald O. Thompson, 13 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Slony-I makes progress
On Wed, Mar 03, 2004 at 04:57:28PM -0500, Jan Wieck wrote: After some substantial progress on the Slony-I engine development, I'd like to give a little status report and invite everyone who wants to participate in this project to join the mailing list and the development team. Jan, thank you so much for your hard work on this project. Both, the provider change and the failover need a much more complex configuration than the current shell scripts can setup. The problem is that the configuration and administration tools are not designed yet. So here is a huge field for others to step up and take a key role in this project. So what are you looking for here? When I last built slony, things mostly worked, but a few niggling details were broken. I was going to submit a few patches, but when I talked to you, it seemed like you weren't quite ready for patches. Is the tree stable enough that I could do some work on it and expect it to remain relatively consistent over a few hours or even a day or two? Also, to get this out of the way (as it presently plagues erserver), do you have a particular language in mind? I'd like to avoid the dogmatic jihad by not submitting a perl tool if the eventual goal is to be end-to-end C (or java or tcl or whatever). Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator Something seems to have happened to the life support system , Dave. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] Schema comparisons
On Sat, Feb 28, 2004 at 10:39:40AM -0500, Tom Lane wrote: Have you tried diffing pg_dump output? It's not the greatest tool but it's helpful. Yes, I did. It was quite cumbersome. Especially since the OIDs and TOC entry numbers didn't matchup; and, since those didn't always match, the order of objects wasn't quite the same either. So, diff was throwing a lot of false positives at me. Yeah. CVS-tip pg_dump doesn't show OIDs by default, to make it easier to use for purposes like this. The ordering issue is the bigger problem though. I presume that the object creation history is different in the two databases and so pg_dump's habit of sorting by OID isn't helpful. I recently had to figure out what was different between the live schema and the schema in cvs at work. This was a really painful process, and it occurred to me that it wouldn't be terribly hard to write a perl program to do it (I wound up using vim and diff). Is there interest in such a tool? I could probably have one written within a day or two. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Solaris Systems Masseur http://envy.posixnap.net/~alex/articles/nro-wahhabi.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Tablespaces
On Thu, Feb 26, 2004 at 11:22:28PM +1100, Gavin Sherry wrote: Certainly, table spaces are used in many ways in oracle, db2, etc. You can mirror data across them, have different buffer sizes for example. In some implementations, they can be raw disk partitions (no file system). I don't intend going this far, however. Perhaps now would be a good time to bring up my directio on Solaris question from a year or so back? Is there any interest in the ability to use raw disk? Alex (who is overjoyed to hear discussion of tablespaces again) -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator Sep 25 12:52:39 buggle /bsd: wsdisplay0 at vga1: removing /dev/radio/* ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Tablespaces
On Fri, Feb 27, 2004 at 08:22:25AM +1100, Gavin Sherry wrote: interested if anyone could provide some real world benchmarking of file system vs. raw disk. Postgres benefits a lot from kernel file system cache at the moment. Also, I believe that database designers have traditionally made bad file system designers. Raw database partitions often lack the tools essential to a scalable environment. For example, the ability to resize partitions. The only reason I mentioned it to begin with was the recommendation of directio for databases in the Sun Blueprint, _Tuning Databases on the Solaris Platform_ (and being a Solaris geek, I asked, but apparently nobody else is worried enough about performance or not using Solaris enough to care). It's not critical, of course. I think, however, that many of us would like to see some of the features of Oracle and DB2 available to users of postgres. Some of these features are raw disk, tablespaces, and replication. We're getting there, and making really terrific progress (I fully expect replication to be ready for primetime in the 8-12 mos timeframe), but we're not quite there yet. As I said, I'm very glad to hear tablespaces mentioned again and see what looks like work being done on it. Thanks! Alex -- [EMAIL PROTECTED] Alex J. Avriette, Solaris Artillery Officer Among the many misdeeds of the British rule in India, history will look upon the act of depriving a whole nation of arms, as the blackest. - Mahatma Gandhi ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RFC: Security documentation
On Sun, Feb 08, 2004 at 09:34:15PM -0500, Tom Lane wrote: Is this nothing? http://www.postgresql.org/docs/7.4/static/libpq-exec.html#LIBPQ-EXEC-ESCAPE-STRING I don't think the docs are nearly as bereft of security-related items as you claim. They may be scattered and poorly indexed, but they're there. Tom, I think this is largely a semantic issue. If documentation exists, but is difficult to find, or stored in such a way as to not be quickly available to somebody looking for it, it isn't useful. While not nothing as such, it doesn't count for much. I've liked what I've heard so far in this thread. Is there a consensus that some documentation could be added regarding security? If we can agree on that, I would be happy to start doing some collating of data on the subject. Could it go in the distributed documentation? I know there was some debate as to whether it belonged in the docs themselves, or in techdocs. Personally, I feel that distributing it in the main documentation would be preferable. However, I don't have any particular allegiance to that method; I mostly look for answers to questions via google first. If the docs were included on techdocs, google would find them soon enough. I suppose, also, anyone who was interested in securing their database would look a little further than the included documentation. Opinions? Alex -- [EMAIL PROTECTED] Alex J. Avriette, Shepherd of wayward Database Administrators We are paying through the nose to be ignorant. - Larry Ellison ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] RFC: Very large scale postgres support
On Sun, Feb 08, 2004 at 08:07:14PM +0100, Andreas Pflug wrote: I feel that it would be a very good thing if some thinking on this subject was done. In the future, people will hopefully begin using postgres for more intense applications. We are looking at perhaps many tens of billions of transactions per day within the next year or two. tens of billions =10e10 per day? This is probably a typo, because this would mean 100,000 requests per second? Do you want to feed a monitor That's what I said, and what I meant. Ten billion transactions equates to 115,740 transactions per second. with pixel data right from the database, using individual queries for each pixel? Or record each irc user's keyclick in the world concurrently online in a single database? Just because you don't think there is a valid use for that sort of traffic doesn't mean there isn't one. Imagine, if you will, a hundred thousand agents making four to five requests a second. Now, imagine these requests are all going to the same database. I'll leave the rest of this exercise up to you. The fact is, there are situations in which such extreme traffic is warranted. My concern is that I am not able to use postgres in such situations because it cannot scale to that level. I feel that it would be possible to reach that level with support in the postmaster for replication. With software load balancing (eg rotors or similar) and updates between postmasters, it would be (it seems to me) possible to drastically increase the available capacity of a database installation through the addition of more nodes. This has the added benefit of allowing us to distribute network resources. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator The Emperor Wears No Clothes. http://www.drugsense.org/wodclock.htm ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Security documentation
On Sun, Feb 08, 2004 at 01:33:31PM -0500, Tom Lane wrote: Actually I can and it involves changing the backend to not permit multiple statements in one request. I can't imagine how that could sensibly be implemented, if at all, though. Actually, the extended-query message in the new FE/BE protocol works exactly that way. This was done for protocol-simplicity reasons not for security, but you could use it for that. The new protocol's ability to separate parameter values from SQL command is also useful for ensuring security. (Tom is referring to this: http://archives.postgresql.org/pgsql-interfaces/2003-03/msg00017.php) How would you suggest implementing this? Having a no subqueries setting? Asking the postmaster to throw an exception on queries-within-data? I can think of several ways to do it, but I'd like to know what you had in mind. At some stage your interface code has to accept responsibility for preventing dangerous input from reaching libpq. However, I quite agree with that statement. The app programmer has to take responsibility for properly segregating or quoting data strings. We can (and do) provide tools to make this easier, but it's still the programmer's responsibility to use the tools correctly. I agree with this as well. In my original message, I complained that there was no documentation at all. Since we offer documentation on how to code in plpgsql, pltcl, plperl, etc., it might be nice to include something. Even if it were something brief, such as suggesting escaped quotes and other suspicious characters, it would be better than the nothing that is there presently. Like I said, it allows some disclaiming of culpability for the programmer -- I did what the docs said -- and it gives them an idea of where to start. My initial feeling is that a small addition to the 'Server Programming' section would be reasonable, or perhaps in the Appendix. I can't see why anyone would be opposed to this, however. I'm happy to write the document and provide a patch for inclusion if we can come to agreeance on some basic policies. The reason I posted the original message in this thread is I wanted to know what others felt were appropriate policies, and to suggest said policies wound up in a doc. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator I favor the Civil Rights Act of 1965, and it must be enforced at gunpoint if necessary. - Ronald Reagan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Very large scale postgres support
On Sun, Feb 08, 2004 at 08:01:38PM -0500, Rod Taylor wrote: Replication won't help if those are all mostly write transactions. If a small percentage, even 1% would be challenging, is INSERTS, UPDATES or DELETES, master / slave replication might get you somewhere. There is no way on earth we could be doing writes at that rate. I think that's a given. Otherwise you're going to need to partition the data up into smaller, easily managed sizes -- that of course requires an ability to horizontally partition the data. Obviously, this is the route we have taken. Anyway, if you want a sane answer we need more information about the data (is it partitionable?), schema type, queries producing the load (simple or complex), acceptable data delays (does a new insert need to be immediately visible?), etc. We've considered a lot of this. Like I said, I think a lot of our need for distributing the database can be helped along with native replication. Am I hearing that nobody believes scalability is a concern? I think many of us would like to see features that would allow large scale installations to be more practical. I also think most of us would agree that the current graft-on replication methods are sub-ideal. alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator The Emperor Wears No Clothes. http://www.drugsense.org/wodclock.htm ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RFC: Very large scale postgres support
On Sun, Feb 08, 2004 at 09:20:07PM -0500, Rod Taylor wrote: On Sun, 2004-02-08 at 21:01, Alex J. Avriette wrote: On Sun, Feb 08, 2004 at 08:01:38PM -0500, Rod Taylor wrote: Replication won't help if those are all mostly write transactions. If a small percentage, even 1% would be challenging, is INSERTS, UPDATES or DELETES, master / slave replication might get you somewhere. There is no way on earth we could be doing writes at that rate. I think that's a given. Sure you can, if you can horizontally partition the data so clients A are on machine A, clients B are on machine B, ... I think you were assuming inserts here. The problem actually comes from updates here. The problem is, if I update here, how long before the rest of my cloud of postgres nodes understand that record is updated? With an insert, the transaction and propagation are fairly clear. With an update, the overall cost is higher, and the cost per-node is higher. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator You cannot invade the mainland United States. There would be a rifle behind each blade of grass. - Admiral Isoroku Yamamoto ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] RFC: Very large scale postgres support
Recently I was tasked with creating a distribution system for postgres nodes here at work. This would allow us to simply bring up a new box, push postgres to it, and have a new database. At the same time, we have started to approach the limits of what we can do with postgres on one machine. Our platform presently is the HP DL380. It is a reasonably fast machine, but in order to eke more performance out of postgres, we are going to have to upgrade the hardware substantially. So the subject came up, wouldn't it be nice if, with replication and proxies, we could create postgres clusters? When we need more throughput, to just put a new box in the cluster, dist a psotgres instance to it, and tell the proxy about it. This is a very attractive idea for us, from a scalability standpoint. It means that we don't have to buy $300,000 servers when we max out our 2- or 4- cpu machines (in the past, I would have suggested a Sun V880 for this database, but we are using Linux on x86). We are left with one last option, and that is re-engineering our application to distribute load across several instances of postgres which are operating without any real knowledge of eachother. I worry, though, that as our needs increase further, these application redesigns will become asymptotic. I find myself wondering what other people are doing with postgres that this doesn't seem to have come up. When one searches for postgres clustering on google, they will find lots of HA products. However, nobody seems to be attempting to create very high throughput clusters. I feel that it would be a very good thing if some thinking on this subject was done. In the future, people will hopefully begin using postgres for more intense applications. We are looking at perhaps many tens of billions of transactions per day within the next year or two. To simply buy a bigger box each time we outgrow the one we're on is not effective nor efficient. I simply don't believe we're the only ones pushing postgres this hard. I understand there are many applications out there trying to achieve replication. Some of them seem fairly promising. However, it seems to me that if we want to see a true clustered database environment, there would have to be actual native support in the postmaster (inter postmaster communication if you will) for replication and cross-instance locking. This is obviously a complicated problem, and probably not very many of us are doing anything near as large-scale as this. However, I am sure most of us can see the benefit of being able to provide support for these sorts of applications. I've just submitted this RFC in the hopes that we can discuss both the best way to support very large scale databases, as well as how to handle them presently. Thanks again for your time. alex -- [EMAIL PROTECTED] Alex J. Avriette, Solaris Systems Masseur I ... remain against the death penalty because I feel that eternal boredom with no hope of parole is a much worse punishment than just ending it all mercifully with that quiet needle. - Rachel Mills, NC Libertarian Gubernatorial Candidate ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Brokenness in parsing of pg_hba.conf
So one of the client machines for one of my databases at work resides on 10.128.0.45. I had to enter something in pg_hba.conf for it today, as we're bringing this database up. We have a lot of 10/8 subnets, and I use it at home, so I'm accustomed to just using 10.128.45 for the IP. Strangely, however, postgres refused to acknowledge the host when it connected. I checked it again, and sure enough, the IP was right. It turns out that postgres parses pg_hba.conf in an unexpected way -- it does not accept abbreviated ip4 addresses (note that this is common in both ip4 and ip6). In the manpage for inet_aton, we see: INTERNET ADDRESSES (IP VERSION 4) Values specified using the `.' notation take one of the following forms: a.b.c.d a.b.c a.b a When four parts are specified, each is interpreted as a byte of data and assigned, from left to right, to the four bytes of an Internet address. Andrew Dunstan on IRC mentioned that the parser is using the native getaddrinfo. I'm not sure if there are any advantages to this; I've said before that I'm really not a C guy. Paul Vixie had this to say about the convention: What this man page is trying to tell you is that BSD users have historically said 10.73 rather than 10.0.0.73 because they both worked any place where either worked. This includes DNS primary zone files, by the way. I am pretty much assuming that the IETF does not want to standardize this BSD practice, and that we ought not to accept ::10.73 as equivilent to the longer ::10.0.0.73, especially given that the degenerate case given in that man page would be ambiguous with respect to ::1234, a valid RFC1884 address specifier whose low order 16 bits are hexadecimal 1234 rather than decimal 1234. However, that's only _my_ assumption, and some other implementor may feel differently. In fact some other implementor of RFC 1884 might decide to just call inet_aton() for parsing that IPv4 dotted quad, which is what I almost did. The original article can be found here: http://www.cs-ipv6.lancs.ac.uk/ipv6/mail-archive/IPng/1996-06/0037.html I think it is important that postgres behave as expected when handing it a properly formatted ip4 address. However, I'm aware that many people don't even realize this is a valid address. As such, I won't lose any sleep over it, but I thought I'd mention it, since it surprised me today. Thoughts? Alex -- [EMAIL PROTECTED] Alex J. Avriette, Solaris Frobnosticator You can get much farther with a kind word and a gun than you can with a kind word alone. - Al Capone ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Brokenness in parsing of pg_hba.conf
On Tue, Jan 06, 2004 at 10:52:19PM -0500, Andrew Dunstan wrote: 4. My personal preference would be that if any change is made it would be to insist on an unabbreviated dotted quad for ip4. Alternatively, we I really think this is the wrong way to approach it. The 127.1 convention is common, and valid. To disallow it because you haven't experienced it is pretty egocentric. If you would instead object on the grounds of it being difficult to implement, or non portable, or outright incorrect, I would be fine with it. But the attitude of I've never seen this, and I don't like it, regardless of the documentation just sucks. need to make sure that whatever we do is consistent. That might not be possible, however, if different platforms or different library calls behave differently. In how many places are we using inet_aton? I see in the docs: http://www.postgresql.org/docs/7.4/static/datatype-net-types.html#DATATYPE-INET It looks like the abbreviated addresses there refer to networks (like the RFC says). Additionally, if you give it '192.168.1/32', you get 192.168.1.0/32. This is even weirder than I expected. I'd really like to hear from others what their opinions on this are. alex -- [EMAIL PROTECTED] Alex J. Avriette, Shepherd of wayward Database Administrators We are paying through the nose to be ignorant. - Larry Ellison ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Brokenness in parsing of pg_hba.conf
On Tue, Jan 06, 2004 at 11:38:44PM -0500, Tom Lane wrote: AFAICS, Alex is quite far out in left field to believe that this is a standard notation. The fact that some BSD platforms have accepted it How did I know you'd say that, Tom? By standard, I mean, many people use it. Not, some standard is defined. For me, the manpage is enough. Additionally, the fact that I (and you) can ping 127.1 on our (your) machine is enough for me. Go on, try it. does not make it standard, especially not when Vixie's research shows that there is no RFC to legitimize it. (Personally I never heard of Vixie is known for being slightly ... irritable. If he encounters something he doesn't like, his first response is oh, that's stupid. It seems strange that Linux, BSD, and Solaris (I can investigate IRIX and OSF1 tomorrow) all support it if it is either incorrect or nonstandard. We're not talking about just BSD here. 4. My personal preference would be that if any change is made it would be to insist on an unabbreviated dotted quad for ip4. I can't get excited about replacing or second-guessing the platform's getaddrinfo() or inet_aton() implementation. If you don't like how Given on both Solaris (my database server) and OpenBSD (the machine from which that manpage came from) I can connect to 127.1, I think you must be mistaken here. What made you think that it isn't supported? those library routines behave, forward your bug report appropriately --- but it's not Postgres' problem. There isn't any point in filing a bug if it will be ignored. alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator You cannot invade the mainland United States. There would be a rifle behind each blade of grass. - Admiral Isoroku Yamamoto ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] psql \d option list overloaded
On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote: Anything other than simple, short commands is a waste, IMHO. I can easily remember SHOW DATABASES and SHOW TABLES and DESC table, because they reflect my intensions directly and 'make sense'. What makes sense to me in csh doesn't make sense in a bourne shell. You can't expect all applications to work correctly. I'd like to second Peter's yep when asked if he could remember all the various \d* commands. It really comes down to whether you're trying. New software (even though you may have been using it for a year) requires some adjustment. Using the slash commands works if you are familiar with them ... sorta like 'ls' switches (I type 'ls -alF' without thinking about what those switches do because it's embedded in my head from years of repetition. Any other flags to 'ls', and I gotta go hit the man pages.) So, hit the documentation when you can't find what you need. Postgres has superb documentation. Or, as a counterpoint, ls -alF doesn't always work on every operating system (such as old IRIX and old SunOS), and is sometimes even a shell builtin. The flawed assumption, again, is that because /your/ environment supports something, that another environment should. What's more important is the ability to use these commands from any interface not just 'psql' client. I think 'psql' already has the slash commands. No need to create NEW slash commands there... If you want to find out how to show the databases in sql, use psql -E. Have you actually done that? OMG! Yes, I do it frequently. You may notice a recent post of mine used exactly that output. 1) Using System Catalogs ... (from psql -E) SELECT n.nspname as Schema, c.relname as Name, CASE c.relkind -snip- ORDER BY 1,2; If the point here was to show that it was a complex query, then the only real reply is This is why we have macros!. 3) like MySQL does it... SHOW TABLES; Should postgres also support the '#' comment? What other non-sql sqlisms should we support? There's something to be said about the 'SHOW'and 'DESC' sql-extensions added into MySQL. Newbies can really 'get' it quickly. It's what really I would argue that these are not sql extensions at all. If you like, I can go over the source to verify this myself, but my guess is that MySQL is doing exactly what postgres is doing, and evaluating this as a macro. Furthermore, databases are not designed for newbies to jump right in with both feet. They are designed to be robust and stable. Additionally, some SQL compliance is nice. After that, you work on features. Changing the interface so that you or others don't have to read the documentation smacks of laziness. Somebody like Bruce, Peter, or Tom (or indeed somebody else) is going to waste yet more time making things like this available to somebody who probably won't read any of the other documentation either, and will wind up on irc pestering somebody like myself, Dave, or Neil. Why is this progress? sold me on MySQL when I first learned it. For me, it's like: 'dir' in DOS, 'ls' in Unix 'SHOW' in MySQL ??? in PostgreSQL ? We've been over this. It's \d*. Sure, with time as my database needs grew and I matured as a developer, I eventually gained more respect for PostgreSQL and have made the switch even without this feature, but to this day, I really think MySQL *did it right* with those extensions. You can't become a PostgreSQL guru without being a newbie first. I vote we make it easier for newbies. What really frightens me here is that I know of several applications (shudder, LAMP applications) which use the output of show tables or other of your extensions. The problem with this is precisely that it /isn't/ sql, and it can't be supported as a static command. It is intended to be there for people to use interactively. Making pseudo sql will encourage more developers to (and I'd apologize for this if it weren't true) code in Postgres the same lazy way they code in MySQL. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Engineer It's computationally FEROCIOUS. - Steve Jobs, discussing 64-bit computing ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql \d option list overloaded
On Sat, Jan 03, 2004 at 08:25:21PM -0500, Bruce Momjian wrote: I finally figure it out, I just end up forgetting again later. I still have no clue how I'd find the same data without using psql. In MySQL I can run those queries from PHP, PERL...etc. I know you can find that data in system tables in PostgreSQL, but I don't wanna muck around with all that. I just wanna do something as simple as MySQL. [ Moved to hackers.] I am starting to agree that our \d* handling is just too overloaded. Look at the option list from \?: I like the idea of adding a new syntax to show that information using simple SQL command syntax, and putting it in the backend so all applications can access it. I know we have information schema, and maybe that can be used to make this simpler. Bruce, while I agree with you about \d (and all its children), as well as the querying we talked about on irc, I disagree with the notion of a SHOW DATABASES query. This is one of the things that irritates me about mysql is the pseudo-sql that everyone has come to accept (through learning sql on mysql) as sql. Things such as the '#' comments, and the various SHOW DATABASES, I feel, detract from the look and feel of the database. That look and feel is one of the reasons I am so loyal to postgres (and indeed why some people are so loyal to mysql). It doesn't make sense to create pseudo-sql, when all you're abstracting is function-macros. I think the backslash syntax is fine. If you really wanted to change it, you might consider a different syntax for it. Many of us are familiar with slash/bang/colon/backslash commands in interfacing with the programs we use regularly (vi, shells, irc clients). Why not a /functions as a long syntax for \df? Would there be a direct problem using the forward slash as a command indicator? This way you could give people like the original poster something they were looking for, eg: /functions /databases and what I was looking for: /functions timestamp It also allows us a lot more freedom in changing the syntax, as the expression of the commands is english (or, pick your language). I seem to recall Neil mentioning to me that was a problem with internationalization, but that's over my head. I don't have any particular allegiance to the forward slash over anything else. My chief concern is that what we're abstracting here are macros, and as such, they should not be treated as sql. Because they aren't sql. If you want to find out how to show the databases in sql, use psql -E. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Solaris Systems Masseur I ... remain against the death penalty because I feel that eternal boredom with no hope of parole is a much worse punishment than just ending it all mercifully with that quiet needle. - Rachel Mills, NC Libertarian Gubernatorial Candidate ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] feature request: \qf datatype
On Fri, Dec 26, 2003 at 11:49:20AM -0600, David Fetter wrote: The latter is harder to answer than the former. For the former, I propose a macro in psql, \qf (query function). Obviously, the name implies a broader scope than simply querying the datatypes permissable. * Part of \df (for functions) This is my initial feeling. It really is just another \df. However, I don't see a good way to merge the functionality of the new function and the old function, as the parameter for df is the function name, not its arguments. * Part of \dT (describing data types) This, too would work, but again, I have a hard time figuring out where to put the arguments. * A new \ command The problem with this is that few people are going to notice it immediately whereas I would contend that many people already know aobut \df and \dT. The goal here is to get people to use the tools they have. If they're not already seeking out tools, it doesn't help to add new ones. Duplication--especially for help systems--is not a bad thing, as long as it's only duplication of access and not of code bases. Duplication of help systems that are never going to be used is a waste of everyone's time. Alex -- [EMAIL PROTECTED] Alex J. Avriette, Unix Systems Gladiator Shut down some of the bullshit the government is spending money on and use it to buy all the Microsoft stock. If we did that, we could ... just bronze Gates, turn him into a statue, and stick him in front of the Commerce Department. - Scott McNealy ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] feature request: \qf datatype
On Fri, Dec 26, 2003 at 04:07:50PM -0800, David Fetter wrote: \dfT integer might bring back all the functions that operate on (or return) integers. I like this the best so far. My only concern is that there is a whole lot of output generated by this (see thread parent). Duplication--especially for help systems--is not a bad thing, as long as it's only duplication of access and not of code bases. Duplication of help systems that are never going to be used is a waste of everyone's time. Perhaps I didn't make clear what I was trying to say. :) We should continue to avoid the The docs for any given thing are findable in exactly one way. If you don't divine it, you are S.O.L. model of documentation. Well, I'm perfectly happy to see it in psql. I'm just unable to actually do the C part myself. If somebody would step up to the plate, we could see this in the next release. I haven't heard anyone say they didn't like it. alex -- [EMAIL PROTECTED] Alex J. Avriette, Windows Systems Defenestrator Object-oriented programming is an exceptionally bad idea which could only have originated in California. - Edsger Dijkstra ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] feature request: \qf datatype
| text| timestamp with time zone, text to_char | text| timestamp without time zone, text timetz | time with time zone | timestamp with time zone time| time without time zone | timestamp with time zone time| time without time zone | timestamp without time zone date_trunc | timestamp with time zone| text, timestamp with time zone timestamptz | timestamp with time zone| timestamp with time zone, integer timestamptz | timestamp with time zone| timestamp without time zone timestamptz_larger | timestamp with time zone| timestamp with time zone, timestamp with time zone timestamptz_mi_span | timestamp with time zone| timestamp with time zone, interval timestamptz_pl_span | timestamp with time zone| timestamp with time zone, interval timestamptz_smaller | timestamp with time zone| timestamp with time zone, timestamp with time zone timezone| timestamp with time zone| interval, timestamp without time zone timezone| timestamp with time zone| text, timestamp without time zone date_trunc | timestamp without time zone | text, timestamp without time zone timestamp | timestamp without time zone | timestamp with time zone timestamp | timestamp without time zone | timestamp without time zone, integer timestamp_larger| timestamp without time zone | timestamp without time zone, timestamp without time zone timestamp_mi_span | timestamp without time zone | timestamp without time zone, interval timestamp_pl_span | timestamp without time zone | timestamp without time zone, interval timestamp_smaller | timestamp without time zone | timestamp without time zone, timestamp without time zone timezone| timestamp without time zone | text, timestamp with time zone (61 rows) The sql required to generate that is as follows: SELECT CASE WHEN p.proretset THEN 'setof ' ELSE '' END || p.proname as Name, pg_catalog.format_type(p.prorettype, NULL) as Result data type, pg_catalog.oidvectortypes(p.proargtypes) as Argument data types FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.prorettype 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND pg_catalog.oidvectortypes(p.proargtypes) ~ 'timestamp' ORDER BY 2, 1, 3; I looked in src/bin/psql/describe.c, and even found the \df macro. However, the C stuff was beyond my ability. Hopefully, this is a direct clone \df item. I really think this would be useful for people who haven't yet becomes familiar with postgres' (very rich) function base. Hm. On second thought, \qf is a bad name for it, as \q is quit, and 'f' is an unexpected extra argument. Perhaps \dfq? Thanks, alex -- [EMAIL PROTECTED] Alex J. Avriette, Professional Something-or-Other Premature optimization is the root of all evil! BAD PROGRAMMER! No COOKIE!!! - Mark-Jason Dominus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
