Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
On Sat, 2003-11-29 at 04:37, cnliou wrote: > "Jason Tesser" <[EMAIL PROTECTED]> > > > MySQL cannot even handle sub-queries yet. > > Ohh! Really? > Allow me to pay my highest respect to the genius mySQL > programmers! > I completely have no clue on how to construct any single > tiny database on a DBMS having no sub-query capability. > > Being too dumb, I solicit mySQL programmers' help by showing > me employee FOO's birthday and his/her latest job title > effective on or before 2003-1-1 from the following tables: > > CREATE TABLE t1 (employee TEXT,BirthDay DATE); > CREATE TABLE t2 (employee TEXT,EffectiveDate DATE,JobTitle > TEXT); > > And make the result like this: > > FOO 1980-1-1 programmer > > Please do not give me the answer that you will merge these > two tables to form one like this: > > CREATE TABLE t1 (employee TEXT,BirthDay DATE,EffectiveDate > DATE,JobTitle TEXT); I have great trouble following your meaning, but I think you are talking about joining two tables in a query: SELECT t1.employee, t1.birthday, t2.jobtitle FROM t1, t2 WHERE t1.employee = t2.employee; That is not the same as using a sub-query: SELECT employee FROM t1 WHERE birthday > ( SELECT MIN(effectivedate) FROM t2 ); (select employees who were born after the longest-serving employee started work.) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Who shall ascend into the hill of the LORD? or who shall stand in his holy place? He that hath clean hands, and a pure heart..."Psalms 24:3,4 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
HI All, I'm glad that this thread prompted some thoughtful response. I think one of my main points I was trying to make, Jason hit the nail on the head. The article to which I was referring uses a great example which I have experienced many times before, but in order to grasp this, PHP et al, must be thought of as a scripting language which crosses many corporate boundries, and it is easy to assume that it's primary use (simple web site back ends) are the only thing to discuss. But the situation has changed enourmously since the release of PHP v4. Now many consultant/developer/sys-admins like myself are going to client site on a contract (this is especially true in the UK, I can't speak for anywhere else) and finding complex stocktrading systems, inventory systems, CRM systems, and others, all written in PHP backed by MySQL. Whether this is right or wrong, good choice or bad choice is not what I'm interested in debating. The point is that when these systems where architected, the developers used MySQL not because they were dumb, but because many of them develop awesome code and can get around most problems in the code, with a little ingenuity. Many simply do not have the insight into the potential benefits of *proper* RDBMS can offer. Had they had the benefit of such knowledge the code they have written would be faster (in DB) and more legible. Sadly often the developers are the only source of DBA for some of these companies. The second scenario, is with admin systems, written by people like myself for companies, whether they be simple or complex systems, that are intended as a temporary work around to an immediate problem. In a very short space of time the stop-gap application you had written to sort out the immediate problem quickly becomes a core business application (I recently returned to a site after not being there for two years and the temporary address book/ email system that I knocked up in an afternoon was not only still being used, but now relied upon heavily). So on to my point, MySQL guys will happily say "Hey, we're not saying that the features MySQL is missing aren't important, and we're working towards them, but in the meantime these issues can be worked around like this." and happily play the whole thing down. Many LAMP developers aren't aware of the benefits of stored procedures, of triggers and other good stuff. Like myself, if they were aware how much easier life could be if these things were accessible to them, they'd probably be converts too. There is not enough emphasis put on the basic importance of these functions in PG. Someone needs to standup and say "Hey, look how this can simplify your programming lives" until I started using Druid/Postgres, I had no idea why I needed triggers or what a cascade effect did, or why I might want one. The Linux community has grown at least in part because it has educated potential users and journo's to its benefits. I believe if the PG advocacy team did the same, then it would attract many more serious LAMP developers. Like Linux vs. Windows, PG has an awful lot going for it in respect to MySQL, so why not crow about it. It needs to be pointed at a crowd that are DB novices, they need to be told why PG is worth the time/knowledge investment, because anyone who reads the MySQL site, will come away with the impression that the Trigger, Stored Procs, and other things are a luxurious overhead not necessary for getting the job done. I'd gladly help out with such a paper, but find myself in the sad position of my prose being open to attack due to my newbieness in the DB world and not able to speak authoratatively on the subject. Have a think, I'd like to know if others agree. Cheers T. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql on file system EXT2 or EXT3
I'm in agreement with Joshua in some aspects of his reply but not others. I use Reiserfs on many production servers and have done so for a couple of years. I have needed perform one repair only on the filesystem, which was automated with the tools provided. Reiser is still beta, in the same way that Debian uses the term testing to refer to non-"stable" software. Many people us it in a production environment successfully. But, as a caveat to my praises to Reiser, if you decide to use it , understand that you *must* know your subject, how to use the tools and how to recover from failures. It is not enough to merely rely on fsck getting the job done, and, in some circumstances the incantations required to performa fix can be quite terse IMHO. The next fs I install will be XFS after much deliberation and conversation with like minded fellow compugeeks, since it's at least as good as any other journaling fs, but has the added bonus that filesystems can be *grown* without the aid of LVM, etc. Which would be a huge bonus. Just my 2 cents. Tony. Joshua D. Drake wrote: | Don't go on EXT2, its not reliable and takes lots of time to start after an Actually EXT2 is quite reliable and it is also quite fast. However your point is accurate about start up time after a crash. The most promising FS is Reiserfs v4 http://www.namesys.com/v4/v4.html Although Reiser is promising, I wouldn't touch it. It is beta, frankly my experience is that even their stable stuff is still beta. If you want a native, reliable, stable FS for Linux. Use JFS or XFS (when 2.6 comes out) Sincerely, Joshua D. Drake If you cant wait I suggest XFS or JFS. Look in the archives for all the explanations. Ohhh, and don't use IDE Drives, only SCSI. Cheer -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Fax: 972-4-6990098 http://www.canaan.net.il -- - Original Message - From: "Carmen Wai" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, November 27, 2003 5:00 PM Subject: [GENERAL] Postgresql on file system EXT2 or EXT3 Hello: I would like to know whether there is any different in installing Postgresql on the Linux system with file system of EXT2 or EXT3. I have two machines with idential OS (Red Hat 7.3 install with postgresql 7.3.4) but with different file system, 1 is EXT2 and the other is EXT3. When I insert 10,000 records to the two machines, I found that the machine with EXT2 insert much quicker than the other with EXT3. Is postgresqk perform better with EXT2 file system? Thanks a lot! Carmen _ Linguaphone : Learning English? Get Japanese lessons for FREE http://go.msnserver.com/HK/30476.asp ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Large objects [BLOB] again - general howto
On 25/11/2003 21:55 Jeremiah Jahn wrote: [snip] I have found that it is best to have a separate connection for BLOB's and one for everything else. Mind you, this is with Java, but the autocommit settings on the connection don't appear to be thread safe, so in high traffic you can accidentally cut off a transfer, or stop one before it ever starts. How could a connection ever have the kind of "thread-safe" behavior you seem to believe it should have? -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Further to this post, what might actually work is to convince O' Reilly (since they have PostgreSQL book/s) to do some articles like they have for PG, but making full use of the PG database. For instance, building a simple data-warehouse using PG. Articles that show off an OSS product/project in a clearly enterprise light in a step-by-step fashion. There have been so many articles on DB design using MySQL. How about an article on DB design using all the functionality of a real ORDBMS. Just a few thoughts. Cheers T. Tony wrote: HI All, I'm glad that this thread prompted some thoughtful response. I think one of my main points I was trying to make, Jason hit the nail on the head. The article to which I was referring uses a great example which I have experienced many times before, but in order to grasp this, PHP et al, must be thought of as a scripting language which crosses many corporate boundries, and it is easy to assume that it's primary use (simple web site back ends) are the only thing to discuss. But the situation has changed enourmously since the release of PHP v4. Now many consultant/developer/sys-admins like myself are going to client site on a contract (this is especially true in the UK, I can't speak for anywhere else) and finding complex stocktrading systems, inventory systems, CRM systems, and others, all written in PHP backed by MySQL. Whether this is right or wrong, good choice or bad choice is not what I'm interested in debating. The point is that when these systems where architected, the developers used MySQL not because they were dumb, but because many of them develop awesome code and can get around most problems in the code, with a little ingenuity. Many simply do not have the insight into the potential benefits of *proper* RDBMS can offer. Had they had the benefit of such knowledge the code they have written would be faster (in DB) and more legible. Sadly often the developers are the only source of DBA for some of these companies. The second scenario, is with admin systems, written by people like myself for companies, whether they be simple or complex systems, that are intended as a temporary work around to an immediate problem. In a very short space of time the stop-gap application you had written to sort out the immediate problem quickly becomes a core business application (I recently returned to a site after not being there for two years and the temporary address book/ email system that I knocked up in an afternoon was not only still being used, but now relied upon heavily). So on to my point, MySQL guys will happily say "Hey, we're not saying that the features MySQL is missing aren't important, and we're working towards them, but in the meantime these issues can be worked around like this." and happily play the whole thing down. Many LAMP developers aren't aware of the benefits of stored procedures, of triggers and other good stuff. Like myself, if they were aware how much easier life could be if these things were accessible to them, they'd probably be converts too. There is not enough emphasis put on the basic importance of these functions in PG. Someone needs to standup and say "Hey, look how this can simplify your programming lives" until I started using Druid/Postgres, I had no idea why I needed triggers or what a cascade effect did, or why I might want one. The Linux community has grown at least in part because it has educated potential users and journo's to its benefits. I believe if the PG advocacy team did the same, then it would attract many more serious LAMP developers. Like Linux vs. Windows, PG has an awful lot going for it in respect to MySQL, so why not crow about it. It needs to be pointed at a crowd that are DB novices, they need to be told why PG is worth the time/knowledge investment, because anyone who reads the MySQL site, will come away with the impression that the Trigger, Stored Procs, and other things are a luxurious overhead not necessary for getting the job done. I'd gladly help out with such a paper, but find myself in the sad position of my prose being open to attack due to my newbieness in the DB world and not able to speak authoratatively on the subject. Have a think, I'd like to know if others agree. Cheers T. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
On 28/11/2003 17:10 Jason Tesser wrote: [snip] I completely disagree. I do a lot of programming with PHP and the features of Postgres come in handy. Let me give you an example of just some basic things. Triggers! Why should I have to write insert and update triggers in the logic (PHP) if I can handle it at the database level. Sql is 10x as fast as the language. Better to handle what you can at the database level. Same with views and stored procedures. Stored procedures can be a 2-edged sword. They can lead to business logic being scattered between the persistence layer and the business layer. Thats not good for maintaining the application 3 years down the line. Triggers can also cause maintenance problems. Its so easy to forget/fail to document that inserting a record into table x causes column y of table z to be updated. Be careful how and where you use these features as they can come back to bite you! MySQL cannot even handle sub-queries yet. I also use Python for standalone interfaces to the data. Why should I not be able to use the same views and triggers etc in there that I use for my web apps. PHP is quite powerful if used correctly. You are, of course, free to do whatever want. But if you have to use features of the database to compensate for inadequacies in your programming language maybe you should be using another language? Java has its own issues and I am not sure it is as far supiour as you are claming it is. But that is not for this dscussion. I'm not aware of any "issues" with Java (unless you mean Swing ;)). MySQL may be more popular with (cheap) web hosting places but that doesn't mean it is the best or that Postgres wouldn't serve better even in this area. I am glad to see the article written for PHP mag as Postgres would help alot of PHP guys that are using MySQL. Much of the populatity of MySQL seems to stem from PHPs out-of-the-box support for it. With the MySQL client library license change, this situation will probably change. There was a long thread about this earlier this year. Check the archives. -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(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: [GENERAL] Misplaced modifier in Postgresql license
> "Permission to use, copy, modify, and distribute this software and its > documentation for any purpose, WITHOUT FEE, and without a written > agreement is hereby granted, provided that the above copyright notice > and this paragraph and the following two paragraphs appear in all > copies." My personal interpretation isn't very ambiguous at all. If the license were interpreted to require that you could not charge to provide someone with a copy of postgres, that would imply that you aren't allowed to have a written agreement with them either. That just doesn't make sense to me. I can see how a lawyer might tell someone to play it safe though. Also, I suppose in any disagreement over the ambiguity of a text, the side perceiving ambiguities is bound to win ;) regards, jeff davis ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL
> > Java has its own issues and I am not sure it is as far supiour as you > > are claming it is. But that is not for this dscussion. > > I'm not aware of any "issues" with Java (unless you mean Swing ;)). I know for one thing - Java's lack of support for returning tuples is hugely annoying. Jon ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
Paul Thomas wrote: > > > > On 28/11/2003 17:10 Jason Tesser wrote: > > [snip] > > > > MySQL cannot even handle > > sub-queries yet. I also use Python for standalone interfaces to > the data. > > > > Why should I not be able to use the same views and triggers etc > in there > > that I use for my web apps. PHP is quite powerful if used correctly. > > You are, of course, free to do whatever want. But if you have to use > features of the database to compensate for inadequacies in your > programming language maybe you should be using another language? This doesn't even make sense in the context of Jasons remark. > > > Java has its own issues and I am not sure it is as far supiour as you > > are claming it is. But that is not for this dscussion. > > I'm not aware of any "issues" with Java (unless you mean Swing ;)). > > MySQL may be more > > popular with (cheap) web hosting places but that doesn't mean it is the > > best > > or that Postgres wouldn't serve better even in this area. I am glad > > to see the article written for PHP mag as Postgres would help > alot of PHP > > guys that are using MySQL. > > Much of the populatity of MySQL seems to stem from PHPs out-of-the-box > support for it. With the MySQL client library license change, this > situation will probably change. There was a long thread about > this earlier > this year. Check the archives. > > This is incorrect. The embedded mysql client library was not added until PHP4.0 RC1. PHP's popularity existed long before this. The real culprit causing the popularity of MySQL was it's ubiquity among hosting providers and the virtual non-existence of PG in that arena. If PG had been more friendly to shared hosting environments, perhaps this situation wouldn't have arisen. Blaming PHP for this situation (and your other comments) show extreme prejudice. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Triggers, Stored Procedures, PHP. was: Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments
From: "Paul Thomas" <[EMAIL PROTECTED]>: > Stored procedures can be a 2-edged sword. They can lead to business logic > being scattered between the persistence layer and the business layer. > Thats not good for maintaining the application 3 years down the line. > Triggers can also cause maintenance problems. Its so easy to forget/fail > to document that inserting a record into table x causes column y of table > z to be updated. Be careful how and where you use these features as they > can come back to bite you! It is all how you organize your app. Stored proceedures are extremely useful when they represent a unified API for accessing parts of the database. Word of advice: Keep the database self-contained. If all you want is object persistance, then why non use Berkeley Database? It is even transactional. The point of having an RDBMS is to provide more flexibility than a simple persistance store. When used sensibly, stored proceedures are extremely simplifying, not the other way arround. > > > > Why should I not be able to use the same views and triggers etc in there > > that I use for my web apps. PHP is quite powerful if used correctly. > > You are, of course, free to do whatever want. But if you have to use > features of the database to compensate for inadequacies in your > programming language maybe you should be using another language? I don't think Jason was compensating for weaknesses in the language-- I think that he was asking why he woudln't want to build into the database the universal functions accessed by multiple applications. And he would be right in trying to do so. Let me give you an example: One of the large projects I maintain is HERMES (http://hermes.sourceforge.net). Hermes relies on its own user and permissions catalogs in order to provide a consistant administrative interface across database managers and simplify the task of assigning permissions to users and groups. The differences in syntax can them be handled in wrapper layers, etc. However, it makes sense to try to wrap these catalogs using stored proceedures so that third-party apps don't necessarily need to be aware of the structure of the catalogs when assigning permissions. This way, too, the db users' catalog and the user catalog in the RDBMS can be guaranteed to be in sync. It will also allow me eventually to directly enforce permissions using triggers rather than rely on the RDBMS model (useful in shared hosting environments). > > > Java has its own issues and I am not sure it is as far supiour as you > > are claming it is. But that is not for this dscussion. > > I'm not aware of any "issues" with Java (unless you mean Swing ;)). Every language has "issues." This is not the time or place for a development environemnt holy war ;-) But--- PHP and Python all the way ;-) > > Much of the populatity of MySQL seems to stem from PHPs out-of-the-box > support for it. With the MySQL client library license change, this > situation will probably change. There was a long thread about this earlier > this year. Check the archives. > Putting the cart before the horse. MySQL is far easier to administer in a shared hosting environment. Maybe one of these days, I will put together a package for managing PostgreSQL accounts in this way. If there is interest, please email me off-list and we can get started. I don't expect MySQL's dominance to change until we can offer an easy-to-administer alternative for these environments. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] ip of the user doing an insert
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Why not use the inet type, which can support both formats? What will you do with local Unix connections? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] After upgrade 7.2.1 to 7.4 "ERROR: large object ... does not exist"
Hi all, a few days ago, I upgraded from PostgreSQL 7.2.1 to 7.4, following the instructions in the INSTALL file, including dump and restore. All this worked fine without any error (message). Since then, I found lots of the following in the postmaster output: 2003-11-29 15:19:54 [1359] ERROR: large object 4838779 does not exist 2003-11-29 15:20:11 [1649] ERROR: large object 4838779 does not exist 2003-11-29 15:20:11 [1657] ERROR: large object 4838779 does not exist 2003-11-29 15:20:27 [1732] ERROR: large object 4838779 does not exist 2003-11-29 15:20:49 [1956] ERROR: large object 10204242 does not exist 2003-11-29 15:20:49 [1975] ERROR: large object 4838779 does not exist 2003-11-29 15:21:49 [2784] ERROR: large object 10204242 does not exist 2003-11-29 15:21:56 [3150] ERROR: large object 10204242 does not exist 2003-11-29 15:22:49 [4053] ERROR: large object 10204242 does not exist 2003-11-29 15:23:06 [4132] ERROR: large object 4838779 does not exist 2003-11-29 15:24:47 [5114] ERROR: large object 4838779 does not exist 2003-11-29 15:26:09 [6259] ERROR: large object 4838779 does not exist 2003-11-29 15:26:27 [6515] ERROR: large object 10204242 does not exist I cannot find the source of those errors. It seems that everything is basically working, but there are always those errors about the two large objects. I'd like to find out what went wrong and how I can repair it. I couldn't find anything in the docs and the mailing lists. To give more information about the messages, here are two more detailed outputs, one per id: 003-11-29 21:42:43 [2533] LOG: 0: statement: BEGIN LOCATION: pg_parse_query, postgres.c:464 2003-11-29 21:42:43 [2533] LOG: 0: statement: select proname, oid from pg_proc where proname = 'lo_open' or proname = 'lo_close'or proname = 'lo_creat' or proname = 'lo_unlink'or proname = 'lo_lseek' or proname = 'lo_tell' or proname = 'loread' or proname = 'lowrite' LOCATION: pg_parse_query, postgres.c:464 2003-11-29 21:42:43 [2533] ERROR: 42704: large object 10204242 does not exist LOCATION: inv_open, inv_api.c:128 2003-11-29 21:42:43 [2533] LOG: 0: statement: ROLLBACK --8<-8<-- 2003-11-29 21:43:48 [3047] LOG: 0: statement: BEGIN LOCATION: pg_parse_query, postgres.c:464 2003-11-29 21:43:48 [3047] LOG: 0: statement: select proname, oid from pg_proc where proname = 'lo_open' or proname = 'lo_close'or proname = 'lo_creat' or proname = 'lo_unlink'or proname = 'lo_lseek' or proname = 'lo_tell' or proname = 'loread' or proname = 'lowrite' LOCATION: pg_parse_query, postgres.c:464 2003-11-29 21:43:48 [3057] LOG: 0: statement: set datestyle to 'ISO'; select version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else get databaseencoding() end; LOCATION: pg_parse_query, postgres.c:464 2003-11-29 21:43:48 [3047] ERROR: 42704: large object 4838779 does not exist LOCATION: inv_open, inv_api.c:128 2003-11-29 21:43:48 [3047] LOG: 0: statement: ROLLBACK The logged statements seems to suggest that there went something wrong with the template database creation or maybe the dump/restore. But as I wrote earlier: There was no visible error. -dirk -- D i r k F "o r s t e r l i n g [EMAIL PROTECTED] http://[EMAIL PROTECTED]/ - A child of five could understand this. Fetch me a child of five! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Domains and function
I can create a function with a domain and define it to return a domain. The parameter is checked to see if it qualifies in the constraint of the domain, however, the return value is not. Is this a bug? Is the author of the function responsible for re-inforcing the constraint at runtime? This is the test case in 7.4: =# create domain one2hundred AS integer -#DEFAULT '1' CONSTRAINT email_domain check( VALUE > 0 AND VALUE <=100 ); CREATE DOMAIN =# =# create function gb52_add( one2hundred ) -# returns one2hundred as -# ' '# BEGIN '#RETURN $1 + 10; '# END; '# ' language 'plpgsql'; CREATE FUNCTION =# =# select gb52_add( 80); gb52_add -- 90 (1 row) =# select gb52_add( 100); gb52_add -- 110 (1 row) =# select gb52_add( 90); gb52_add -- 100 (1 row) =# select gb52_add( 91); gb52_add -- 101 (1 row) =# select gb52_add( 191); ERROR: value for domain one2hundred violates check constraint "email_domain" [EMAIL PROTECTED]Varlena, LLCwww.varlena.com PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = I have always depended on the [QA] of strangers. ---(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
[GENERAL] Drop Cascade of Domains
I have a table which contains three columns of domain X. If I drop the domain X with cascade, the table remains with no columns. Is this the proper behaviour? It seems to me that any action which results in an invalid object should be forbidden. On the other hand, with alter table working well, I am loathe to argue a table with no columns is an invalid argument. Is there a previous rule on this issue? --elein [EMAIL PROTECTED]Varlena, LLCwww.varlena.com PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = "Free your mind the rest will follow" -- En Vogue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Cron-job for checking up on pg_autovacuum
On Tue, 2003-11-25 at 02:13, Markus Wollny wrote: > Hi! > > I haven't found anything in terms of startup- and check-scripts for > pg_autovacuum yet; usually I like to have some sort of mechanism to > check if some daemon is running and restart it if it isn't. The Debian package of 7.4 starts pg_autovacuum in the rc script if it is so configured. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Who shall ascend into the hill of the LORD? or who shall stand in his holy place? He that hath clean hands, and a pure heart..."Psalms 24:3,4 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Was: Triggers, Stored Procedures, PHP
Jason Tesser wrote: [snip] A programmer that doesn't document stuff needs to find a new job :-) This is more of an issue with management. Anyone who does database apps for on any kind of a large scale will tell you that views, triggers, etc.. are essential. I am currently in teh process of writing a complete solution for the college I develop for. Finance, accounting, pos, registration, student tracking etc... I'm going to hop on this thread and ask a question rather than rant (although ranting is fine by me ... rant away). Could someone explain to me the usefulness of views? I understand how they are created. I understand a single query can be created as a view returning all records in a single column of a single table, or maybe even multiple columns across many tables using a complex join. That sounds find if all you want to do is to populate your drop-down list box with selection choices or use the same search criteria each time. But if I want to access certain information for a particular customer that requires joins and the like, then a view would be great. But as far as I know, I am unable to place search parameters into a view. Is this false or am I totally missing the point of views? Shane D ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Was: Triggers, Stored Procedures, PHP
Shane D <[EMAIL PROTECTED]> writes: >Could someone explain to me the usefulness of views? I understand > how they are created. I understand a single query can be created as a > view returning all records in a single column of a single table, or > maybe even multiple columns across many tables using a complex join. > >That sounds find if all you want to do is to populate your > drop-down list box with selection choices or use the same search > criteria each time. But if I want to access certain information for a > particular customer that requires joins and the like, then a view > would be great. But as far as I know, I am unable to place search > parameters into a view. Is this false or am I totally missing the > point of views? It's false. You can treat a view just like a table and add clauses to your query that restrict it beyond what the view gives you. I think that's what you're asking about... Views are useful for things like: 1) Insulating apps from details of the schema which may change 2) Giving different users different, well, views of the data, perhaps on a column basis. Create a view that only shows a subset of columns, and only allow unprivileged users access to the view, not the underlying table(s). -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Drop Cascade of Domains
elein writes: > It seems to me that any action which > results in an invalid object should be > forbidden. On the other hand, with > alter table working well, I am loathe > to argue a table with no columns is > an invalid argument. While not allowed according to the SQL standard, we have decided that tables with zero columns are valid, and the behavior of all commands and side effects has been aligned with that. -- Peter Eisentraut [EMAIL PROTECTED] ---(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: [GENERAL] ip of the user doing an insert
On Sat, 2003-11-29 at 20:46, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Why not use the inet type, which can support both formats? > > What will you do with local Unix connections? Why not use 127.0.0.1 or the ipv6 equivalent. -- Suchandra Thapa <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Drop Cascade of Domains
elein <[EMAIL PROTECTED]> writes: > I have a table which contains three > columns of domain X. > If I drop the domain X with cascade, > the table remains with no columns. > Is this the proper behaviour? Yes, we agreed some time ago that that is the best thing to do. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Was: Triggers, Stored Procedures, PHP
Doug McNaught wrote: It's false. You can treat a view just like a table and add clauses to your query that restrict it beyond what the view gives you. I think that's what you're asking about... Thanks for your reply. I found an example in the postgresql reference manual in the "CREATE VIEW" section that shows exactly what you said (reproduced below). CREATE VIEW kinds AS SELECT * FROM films WHERE kind = ’Comedy’; The manual uses the view thusly: SELECT * FROM kinds; But what if the films table also had a field for the production company. This implies based on the view definition that it too, has the field (call it prod_co). Could I use the following query to select all Comedy films distributed by the 'Small Company' production company? SELECT * FROM kinds WHERE prod_co = 'Small Company'; Yes this is contribed, but humor me please. Shane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Was: Triggers, Stored Procedures, PHP
Shane D <[EMAIL PROTECTED]> writes: >But what if the films table also had a field for the production > company. This implies based on the view definition that it too, has > the field (call it prod_co). Could I use the following query to > select all Comedy films distributed by the 'Small Company' production > company? > > SELECT * FROM kinds WHERE prod_co = 'Small Company'; Sure, as long as the column is part of the view, you can use it to constrain the SELECT. -Doug ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] permission errors for set authority and schema public
I upgraded to 7.4 a few days ago and am getting these errors when importing dumps: "permission denied to set session authority" and "permission denied for schema public". Not all of the dump is imported successfully. Are these errors due to new features in 7.4? Do I need to add more permissions for users? __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ ---(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: [GENERAL] Cron-job for checking up on pg_autovacuum
When grilled further on (Tue, 25 Nov 2003 11:19:32 -0500), "Matthew T. O'Connor" <[EMAIL PROTECTED]> confessed: > I know I heard from at least one person who said they were running it > from there inittab with respawn, which seems like overkill. Nah, it works great (it was me). I just had an unclean shutdown/reboot, and pg_autovacuum couldn't connect on start because PGSQL was coming up. inittab took note of the quick respawning and put a 5 minute delay in there. And I got to ignore it. Also works great when PGSQL is down for any reason. You don't have to worry about stopping/starting pg_autovacuum... Overkill is a good thing! It keeps stuff working ;-) Cheers, Rob my /etc/inittab line (Mandrake 9.1 system) vac:235:respawn:su - postgres -c "/usr/local/pgsql/bin/pg_autovacuum -d 0" -- 22:06:18 up 2 days, 4:12, 1 user, load average: 2.11, 2.15, 2.17 pgp0.pgp Description: PGP signature
Re: [GENERAL] patch for pg_autovacuum
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --- Brian Hirt wrote: > here's a patch that joins on pg_class.oid instead of > pg_class.relfilenode, also i have renamed the table structure from > relfilenode to relid. > [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] patch for pg_autovacuum
Bruce, for what it's worth, Matthew O'Connor submitted a patch which includes my patch. best regards, Brian Hirt. On Nov 29, 2003, at 10:14 PM, Bruce Momjian wrote: [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Sat, 2003-11-29 at 04:26, Randolf Richardson wrote: ... > Keep in mind that (at least in Canada) contractual agreements are only > valid when an aspect called "consideration" exists, which means that both > parties benefit in some way (which must not be grossly unfair to one side). > > With all this mish-mash of various licenses, I wonder how > "consideration" would fit in to it all. Consideration is a concept in contract law. It has no relevance to licences, which are NOT contracts. The essence of a contract is that each party gives something (the consideration) to the other. A licence is one-sided. (However, a licence may itself be the consideration, as when you pay for commercial software.) -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But grow in grace, and in the knowledge of our Lord and Saviour Jesus Christ. To him be glory both now and for ever. Amen." II Peter 3:18 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
