Re: [SQL] How to check postgres running or not ?
Worik wrote: Assuming it is unix The command ps xau|grep post You might want to change that to: ps aux|grep postgres As your suggestion will pick up extraneous data if one is running postfix on the same box. -- Until later, Geoffrey Registered Linux User #108567 ATT Certified UNIX System Programmer - 1995 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] ORDER BY and NULLs
Hello, I am using PostgreSQL 7.4.2 and as I understand NULL values always sort last. However, I have a table from which select using two numerical sort keys FROM and TO. TO might be NULL and I would like to display those rows first (without sorting the column in descending order). Is there any way this can be achieved without inserting bogus values into that column? -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER BY and NULLs
select ... order by FROM is not null, FROM; If you have large amount of rows (with or without nulls) it is faster if use a partial index. create index ... on ...(FROM); create index ... on ...(FROM) where FROM is null; JLL [EMAIL PROTECTED] wrote: Use the coalesce() function. (coalesce returns the first non-null value in its list) Specifically ORDER BY coalesce(TO, 0), FROM If you have records in TO column whose values is LESS then 0, then you need to replace 0 with something that sorts BEFORE the first most value that your TO result can return. Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of T E Schmitz Sent: Sunday, September 19, 2004 10:58 AM To: [EMAIL PROTECTED] Subject: [SQL] ORDER BY and NULLs Hello, I am using PostgreSQL 7.4.2 and as I understand NULL values always sort last. However, I have a table from which select using two numerical sort keys FROM and TO. TO might be NULL and I would like to display those rows first (without sorting the column in descending order). Is there any way this can be achieved without inserting bogus values into that column? -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to check postgres running or not ?
Geoffrey [EMAIL PROTECTED] writes: Worik wrote: Assuming it is unix The command ps xau|grep post You might want to change that to: ps aux|grep postgres As your suggestion will pick up extraneous data if one is running postfix on the same box. Actually I'd recommend grepping for postmaster. If your PG user is named postgres then the above command will find any program the PG user is running --- which might only be a shell, for instance. If your PG user is not named postgres then the above might find nothing at all, even though the postmaster is alive (since depending on the details of your local ps command, it might report all the server processes as postmaster). There is even another gotcha, which is that the grep postmaster command could easily find itself in the ps output. So what really works is ps aux | grep postmaster | grep -v grep (or use ps -ef if using a SysV-ish ps). Obviously none of this matters if you are just going to eyeball the output, but if you want something suitable for a test in a script, you'd better use something like the last one. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ORDER BY and NULLs
Hello Jean-Luc, You must've been reading my mind. I was just wondering what to do about indexing on that particular table. I read somewhere that an Index is not going to improve the performance of an ORDER BY if the sort column contains NULLs because NULLs aren't indexed? For the sake of the example I had simplified matters a wee bit. What I really have is: SELECT * FROM PRODUCT ORDER BY NAME, FROM, TO, FROM2, TO2 FROM, TO, FROM2, TO2 might be NULL. If FROM is NULL, TO will be NULL. If FROM2 is NULL, TO2 will be NULL. How would you index this table? Kind regards, Tarlika Jean-Luc Lachance wrote: select ... order by FROM is not null, FROM; If you have large amount of rows (with or without nulls) it is faster if use a partial index. create index ... on ...(FROM); create index ... on ...(FROM) where FROM is null; JLL [EMAIL PROTECTED] wrote: Use the coalesce() function. (coalesce returns the first non-null value in its list) Specifically ORDER BY coalesce(TO, 0), FROM If you have records in TO column whose values is LESS then 0, then you need to replace 0 with something that sorts BEFORE the first most value that your TO result can return. Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of T E Schmitz Sent: Sunday, September 19, 2004 10:58 AM To: [EMAIL PROTECTED] Subject: [SQL] ORDER BY and NULLs Hello, I am using PostgreSQL 7.4.2 and as I understand NULL values always sort last. However, I have a table from which select using two numerical sort keys FROM and TO. TO might be NULL and I would like to display those rows first (without sorting the column in descending order). Is there any way this can be achieved without inserting bogus values into that column? -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to check postgres running or not ?
On Sun, Sep 19, 2004 at 12:25:00PM -0400, Tom Lane wrote: ps aux | grep postmaster | grep -v grep (or use ps -ef if using a SysV-ish ps). Except that on Solaris, ps -ef _always_ shows postmaster, even for the individual back ends. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER BY and NULLs
T E Schmitz [EMAIL PROTECTED] writes: You must've been reading my mind. I was just wondering what to do about indexing on that particular table. I read somewhere that an Index is not going to improve the performance of an ORDER BY if the sort column contains NULLs because NULLs aren't indexed? Whatever you were reading had it pretty badly garbled :-( Btree indexes *do* store nulls, so the presence of nulls doesn't affect whether they are usable for meeting an ORDER BY spec. However the index sort order does have to exactly match the ORDER BY list, and even then it's not necessarily the case that the index is useful. The brutal fact is that seqscan-and-sort is generally faster than a full-table indexscan for large tables anyway, unless the table is clustered or otherwise roughly in order by the index. If you are going to use an ORDER BY that involves COALESCE or NOT NULL expressions, then the only way that it could be met with an index is to create an expressional index on exactly that list of expressions. For instance regression=# create table foo (f int, t int); CREATE TABLE regression=# explain select * from foo order by f, coalesce(t, -1); QUERY PLAN - Sort (cost=69.83..72.33 rows=1000 width=8) Sort Key: f, COALESCE(t, -1) - Seq Scan on foo (cost=0.00..20.00 rows=1000 width=8) (3 rows) regression=# create index fooi on foo (f, (coalesce(t, -1))); CREATE INDEX regression=# explain select * from foo order by f, coalesce(t, -1); QUERY PLAN Index Scan using fooi on foo (cost=0.00..52.00 rows=1000 width=8) (1 row) regression=# I'm a bit dubious that such an index would be worth its update costs, given that it's likely to be no more than a marginal win for the query. But try it and see. Jean-Luc Lachance wrote: If you have large amount of rows (with or without nulls) it is faster if use a partial index. This advice seems entirely irrelevant to the problem of sorting the whole table... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to check postgres running or not ?
Andrew Sullivan [EMAIL PROTECTED] writes: On Sun, Sep 19, 2004 at 12:25:00PM -0400, Tom Lane wrote: ps aux | grep postmaster | grep -v grep (or use ps -ef if using a SysV-ish ps). Except that on Solaris, ps -ef _always_ shows postmaster, even for the individual back ends. Right, but if you see a backend then you can figure the system is up. If you are concerned about the case where the postmaster has crashed and yet there are still backends laying about, then the whole ps approach is probably wrong anyway. It would make more sense to check whether the postmaster is answering the doorbell --- ie, send a connection request and see what happens. At one time there was discussion of writing a pg_ping utility program to do exactly this, but it still hasn't got done. You can fake it to some extent by just running psql -l /dev/null and checking the exit code, but this does require supplying a valid username and possibly a password (because psql's exit code doesn't distinguish could not connect from authentication errors). BTW, pg_ctl status doesn't answer this need because it only looks for a postmaster.pid file, it doesn't attempt to verify that the postmaster is really alive. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] ORDER BY and NULLs
Hello Tom, Tom Lane wrote: T E Schmitz [EMAIL PROTECTED] writes: I read somewhere that an Index is not going to improve the performance of an ORDER BY if the sort column contains NULLs because NULLs aren't indexed? Whatever you were reading had it pretty badly garbled :-( I just dug out the PostgreSQL book again because I thought I might've garbled it: Quote: PostgreSQL will not index NULL values. Because an index will never include NULL values, it cannot be used to satisfy the ORDER BY clause of a query that returns all rows in a table. Btree indexes *do* store nulls, so the presence of nulls doesn't affect Thank you for your explanations. At the moment the table has only 1300 entries and any query is responsive. I'm just planning ahead... -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] ORDER BY and NULLs
T E Schmitz [EMAIL PROTECTED] writes: I just dug out the PostgreSQL book again because I thought I might've garbled it: Quote: PostgreSQL will not index NULL values. Because an index will never include NULL values, it cannot be used to satisfy the ORDER BY clause of a query that returns all rows in a table. You should just cross out that whole section. It's just flatly wrong. I had always assumed it was just people bringing assumptions over from Oracle where it is true. Perhaps this book is to blame for some of the confusion. Which book is it? Postgres indexes NULLs. It can use them for ORDER BY clauses. Where it cannot use them is to satisfy WHERE foo IS NULL or WHERE foo IS NOT NULL constraints though. That's an implementation detail, but it can be worked around with partial indexes. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] ORDER BY and NULLs
T E Schmitz [EMAIL PROTECTED] writes: Tom Lane wrote: Whatever you were reading had it pretty badly garbled :-( I just dug out the PostgreSQL book again because I thought I might've garbled it: Quote: PostgreSQL will not index NULL values. Because an index will never include NULL values, it cannot be used to satisfy the ORDER BY clause of a query that returns all rows in a table. [ shrug ] It's wrong on both counts, and has been since (checks CVS) 1997. What book is that anyway? There is a related statement that is still true: WHERE x IS NULL (or NOT NULL) clauses are not indexscannable. This is a shortcoming of the planner-to-index-access-method interface, though, not a question of whether the index can store NULLs. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] ORDER BY and NULLs
Hello Greg, Greg Stark wrote: T E Schmitz [EMAIL PROTECTED] writes: Quote: PostgreSQL will not index NULL values. Because an index will never include NULL values, it cannot be used to satisfy the ORDER BY clause of a query that returns all rows in a table. You should just cross out that whole section. It's just flatly wrong. I had always assumed it was just people bringing assumptions over from Oracle where it is true. Perhaps this book is to blame for some of the confusion. Which book is it? PostgreSQL by Korry Douglas + Susan Douglas, ISBN 0-7357-1257-3; Feb 2003 Postgres indexes NULLs. It can use them for ORDER BY clauses. Where it cannot use them is to satisfy WHERE foo IS NULL or WHERE foo IS NOT NULL constraints though. That's an implementation detail, but it can be worked around with partial indexes. The paragraph continues: If the SELECT command included the clause WHERE phone NOT NULL, PostgreSQL could use the index to satisfy the ORDER BY clause. An index that covers optional (NOT NULL) columns will not be used to speed table joins either. -- Regards/Gruß, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] ORDER BY and NULLs
T E Schmitz [EMAIL PROTECTED] writes: Greg Stark wrote: Which book is it? PostgreSQL by Korry Douglas + Susan Douglas, ISBN 0-7357-1257-3; Feb 2003 Hmm, I've heard of that book but never seen it. The authors are not participants in the PG community --- AFAICT neither of them have ever posted anything in the mailing lists. The paragraph continues: If the SELECT command included the clause WHERE phone NOT NULL, PostgreSQL could use the index to satisfy the ORDER BY clause. An index that covers optional (NOT NULL) columns will not be used to speed table joins either. My goodness, it seems to be a veritable fount of misinformation :-( I wonder how much of this is stuff that is true for Oracle and they just assumed it carried over? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ORDER BY and NULLs
Hello Tom, Tom Lane wrote: T E Schmitz [EMAIL PROTECTED] writes: Greg Stark wrote: Which book is it? PostgreSQL by Korry Douglas + Susan Douglas, ISBN 0-7357-1257-3; Feb 2003 Hmm, I've heard of that book but never seen it. The authors are not participants in the PG community --- AFAICT neither of them have ever posted anything in the mailing lists. The paragraph continues: If the SELECT command included the clause WHERE phone NOT NULL, PostgreSQL could use the index to satisfy the ORDER BY clause. An index that covers optional (NOT NULL) columns will not be used to speed table joins either. My goodness, it seems to be a veritable fount of misinformation :-( Well, that's great. My knowledge of SQL is good enough to model a DB and do run of the mill queries; but when it comes to some fine details I rely on sensible input ;-) Thanks for chipping in here and answering what I thought was a dummy question. -- Kind Regards/Gruß, Tarlika ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to check postgres running or not ?
Jeff Eckermann wrote: --- Christopher Browne [EMAIL PROTECTED] wrote: In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Sandeep Gaikwad) transmitted: Hello Sir, I want to know how to check whether postgres database is running or not ? when I give command like ./postmaster -i , whether all databases in that postgres will run or any one [default] ? If any one, then how to detect that database ? The standard way would be pg_ctl status. man pg_ctl is recommended reading for anyone administering a PostgreSQL setup. Is not enough because it check only for the postmaster.pid and not if the engine is really up. Regards Gaetano Mendola ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to check postgres running or not ?
Tom Lane wrote: Geoffrey [EMAIL PROTECTED] writes: Worik wrote: Assuming it is unix The command ps xau|grep post You might want to change that to: ps aux|grep postgres As your suggestion will pick up extraneous data if one is running postfix on the same box. Actually I'd recommend grepping for postmaster. If your PG user is named postgres then the above command will find any program the PG user is running --- which might only be a shell, for instance. If your PG user is not named postgres then the above might find nothing at all, even though the postmaster is alive (since depending on the details of your local ps command, it might report all the server processes as postmaster). There is even another gotcha, which is that the grep postmaster command could easily find itself in the ps output. So what really works is ps aux | grep postmaster | grep -v grep (or use ps -ef if using a SysV-ish ps). Just to enforce the test is better looking for the entire executable path: ps aux | grep /usr/bin/postmaster | grep -v grep Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to check postgres running or not ?
On Sun, Sep 19, 2004 at 01:12:07PM -0400, Tom Lane wrote: Except that on Solaris, ps -ef _always_ shows postmaster, even for the individual back ends. Right, but if you see a backend then you can figure the system is up. Oops, good point. (And in any case, on Solaris you also have the ucb ps, so it makes no difference.) A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] How to check postgres running or not ?
[snip] Just to enforce the test is better looking for the entire executable path: ps aux | grep /usr/bin/postmaster | grep -v grep Does not work for me! [EMAIL PROTECTED]:~$ ps aux | grep /usr/bin/postmaster | grep -v grep [EMAIL PROTECTED]:~$ ps aux | grep postmaster | grep -v grep postgres 670 0.1 0.6 8544 1688 pts/1S12:33 0:00 /usr/lib/postgresql/bin/postmaster [EMAIL PROTECTED]:~$ So... ps aux | grep postmaster | grep -v grep is more reliable(?) cheers Worik Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] ORDER BY and NULLs
Tom Lane [EMAIL PROTECTED] writes: The paragraph continues: If the SELECT command included the clause WHERE phone NOT NULL, PostgreSQL could use the index to satisfy the ORDER BY clause. An index that covers optional (NOT NULL) columns will not be used to speed table joins either. My goodness, it seems to be a veritable fount of misinformation :-( I wonder how much of this is stuff that is true for Oracle and they just assumed it carried over? The first part is true for Oracle. You have to add the WHERE phone NOT NULL to convince Oracle it can use an index. Or just make the column NOT NULL to begin with I think. However as far as I recall the second part is not true. Oracle is smart enough to realize that an equijoin clause implies NOT NULL and therefore allows it to use the index. (This may have all changed in Oracle 9+. The last I saw of Oracle was 8i) I wonder if they just tried explain on a bunch of queries and noticed that postgres wasn't using an index for SELECT * FROM foo ORDER BY bar and came up with explanations for the patterns they saw? -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to check postgres running or not ?
Perhaps I have a bug in pg_ctrl? This is what I get... [EMAIL PROTECTED]:~$ ps aux | grep postmaster | grep -v grep ;/usr/lib/postgresql/bin/pg_ctl status postgres 670 0.0 0.6 8544 1688 pts/1S12:33 0:00 /usr/lib/postgresql/bin/postmaster pg_ctl: postmaster or postgres is not running [EMAIL PROTECTED]:~$ cheers Worik Jeff Eckermann wrote: --- Christopher Browne [EMAIL PROTECTED] wrote: In an attempt to throw the authorities off his trail, [EMAIL PROTECTED] (Sandeep Gaikwad) transmitted: Hello Sir, I want to know how to check whether postgres database is running or not ? when I give command like ./postmaster -i , whether all databases in that postgres will run or any one [default] ? If any one, then how to detect that database ? The standard way would be pg_ctl status. man pg_ctl is recommended reading for anyone administering a PostgreSQL setup. Well, the way I usually check on what databases are running is thus: [EMAIL PROTECTED]:/tmp/mm5/doc netstat -an | grep PG Saturday 13:18:30 unix 2 [ ACC ] STREAM LISTENING 2793 /var/run/postgresql/.s.PGSQL.5432 One could presumably script things further to get more out of that; it doesn't normally seem worthwhile to do so... -- output = (cbbrowne @ ntlug.org) http://www.ntlug.org/~cbbrowne/postgresql.html As long as war is regarded as wicked, it will always have its fascination. When it is looked upon as vulgar, it will cease to be popular. --Oscar Wilde ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to check postgres running or not ?
Worik [EMAIL PROTECTED] writes: Perhaps I have a bug in pg_ctrl? More likely you have the wrong value of PGDATA in your environment (where wrong means not what that postmaster is using). regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to check postgres running or not ?
How about a simple telnet localhost 5432 ? assuming postmaster is listening on that host and port On Fri, 17 Sep 2004, Sandeep Gaikwad wrote: Hello Sir, I want to know how to check whether postgres database is running or not ? when I give command like ./postmaster -i , whether all databases in that postgres will run or any one [default] ? If any one, then how to detect that database ? Thanks and regards, Sandeep. ---(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
[SQL] Test for file exists?
I want to use pgsql to send the command: Copy sometable from 'sometable.csv'; But only if 'sometable.csv' exists; If 'sometable.csv' does not exist as an input table I want to continue the next command. My full procedure is as follows and any help is greatly appreciated. Thanks Richard CREATE OR REPLACE FUNCTION restore_database(text) RETURNS text AS $BODY$ declare tblname record; cnt record; tname varchar :=''; tquery varchar :=''; filename varchar :=''; begin tname := ''; for tblname in select tablename from pg_tables WHERE not(tablename like 'pg_%') and not(tablename like 't_%') and not(tablename like '%_list') order by tablename loop raise notice '%',tblname.tablename; tquery := 'delete from ' || tblname.tablename ; execute tquery; filename := '/'||$1||'/'|| lower(tblname.tablename)||'.csv'; tquery := 'copy ' || tblname.tablename || ' from ' || quote_literal(filename); execute tquery; end loop; return tquery; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.760 / Virus Database: 509 - Release Date: 10/09/2004 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
