Re: [GENERAL] List archives search function broken
On Sat, 4 Dec 2004, Michael Fuhr wrote: Using the list archive search function currently fails with 503 Service Unavailable. Should messages about list archive problems go to pgsql-general, or would it be better to use one of the other lists like bugs, hackers, or www? I think complaints should go to -www list. btw, www.pgsql.ru is working and I think it could be as 'backstop' for main search engine, for example on server error there could link to http://www.pgsql.ru/db/pgsearch/index.html?set=archives Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.0.0 Release Candidate 1
David Fetter wrote: It's also on http://bt.postgresql.org/ :) I don't know why, but I always seem to have problems with the torrents at bt.postgresql.org. I click them and my download manager says starting but then nothing happens. I can however download the torrents using wget url of torrent and start them locally on my machine without problems. This might of course be something in my own setup but I don't have this problem with any other torrents on the net. Anyone else experiencing similar problems? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-www] [GENERAL] List archives search function broken
Using the list archive search function currently fails with 503 Service Unavailable. Should messages about list archive problems go to pgsql-general, or would it be better to use one of the other lists like bugs, hackers, or www? I think complaints should go to -www list. btw, www.pgsql.ru is working and I think it could be as 'backstop' for main search engine, for example on server error there could link to http://www.pgsql.ru/db/pgsearch/index.html?set=archives Not a bad idea, but I won't be able to implement that, as the frontend does not allow per virtual host error pages. - Unless someone is willing to write a patch for pound (http://www.apsis.ch/pound). Btw, this would have been caught earlier, if not for the fact that I've been without an internet connection @ home for the past few days. Db backend server OS had shut down for yet to be determined reasons. Kind Regards, John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-www] [GENERAL] List archives search function broken
John, you could always rely on your script where you check if db handler is alive and if not then show gentle error message with link to www.pgsql.ru Oleg On Sun, 5 Dec 2004, John Hansen wrote: Using the list archive search function currently fails with 503 Service Unavailable. Should messages about list archive problems go to pgsql-general, or would it be better to use one of the other lists like bugs, hackers, or www? I think complaints should go to -www list. btw, www.pgsql.ru is working and I think it could be as 'backstop' for main search engine, for example on server error there could link to http://www.pgsql.ru/db/pgsearch/index.html?set=archives Not a bad idea, but I won't be able to implement that, as the frontend does not allow per virtual host error pages. - Unless someone is willing to write a patch for pound (http://www.apsis.ch/pound). Btw, this would have been caught earlier, if not for the fact that I've been without an internet connection @ home for the past few days. Db backend server OS had shut down for yet to be determined reasons. Kind Regards, John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-www] [GENERAL] List archives search function broken
John, you could always rely on your script where you check if db handler is alive and if not then show gentle error message with link to www.pgsql.ru I never check if dbhandler is alive in the frontend This was the backend OS that shut down, which also hosts the filesystems for the scripts So that would have had the same result. The 503 is hardcoded btw, tho it does have a method of displaying a file. But that would be globally for all hosts, not just search.postgresql.org Frontend is pound reverse proxy running on the gateway. The actual search is a backend running vmware http://search.postgresql.org/phpsysinfo/ ... John ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Preview of Fourth PostgreSQL RFD.
On Sat, Dec 04, 2004 at 14:07:50 -0800, Mike Cox [EMAIL PROTECTED] wrote: Core PostgreSQL development, bug reports, are off-topic in comp.databases.postgresql. Those topics are to be discussed in pgsql.* or the PostgreSQL mailing lists. I think this is going a bit far. I think the previous warning that core development and bug reports are effectively handled elsewhere is good enough. I don't think you need to declare these topics off topic. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] SSL confirmation
Hi, I am running postgreSQL and just wanted to know how I confirm that SSL is fully functional? I have placed server.key, server.crt and root.crt in the data folder and am able to launch postgreSQL with no problems. I m launching postgreSQl with the following command: /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data Is that sufficient to start SSL, how can I check? regards Andrew ---(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] SSL confirmation
To answer my own question I included the -l flag: /usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data No errors were reported, which I guess there would be if: 1. postgreSQL had not ben built with SSL support? or 2. the certificate has not been properly setup? regards Andrew On 5 Dec 2004, at 16:12, Andrew M wrote: Hi, I am running postgreSQL and just wanted to know how I confirm that SSL is fully functional? I have placed server.key, server.crt and root.crt in the data folder and am able to launch postgreSQL with no problems. I m launching postgreSQl with the following command: /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data Is that sufficient to start SSL, how can I check? regards Andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Constaints
Is there anyway to declare a constant that you can then use within a postgresql 'session', i am connecting from a PHP based application and trying to integrate another. What I want to be able to do is setup a rule on another table so that whenever a query is run on the table it appends another value to the query. i.e i want to be able to do: 'SET someconstant an_id=1;' Then with a rule, running: SELECT * FROM a_table would become: SELECT * FROM a_table WHERE id=a_id is this possible? Thanks Jake ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SSL confirmation
Andrew M. writes: To answer my own question I included the -l flag: /usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data No errors were reported, which I guess there would be if: 1. postgreSQL had not ben built with SSL support? or 2. the certificate has not been properly setup? You could also use openssl's utilities to diagnose the SSL part of the connection. For example: $ openssl s_client -host localhost -port port will show you details about the authentication and encryption in use. HTH Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 04:12:38PM +, Andrew M wrote: I am running postgreSQL and just wanted to know how I confirm that SSL is fully functional? I have placed server.key, server.crt and root.crt in the data folder and am able to launch postgreSQL with no problems. I m launching postgreSQl with the following command: /usr/local/pgsql/bin/postmaster -i -D /usr/local/pgsql/data You can omit the -i if you have tcpip_socket = true (or set listen_address if you're using 8.0) in postgresql.conf Is that sufficient to start SSL, how can I check? You should have ssl = true in postgresql.conf (restart the backend after making a change). When you make an SSL connection with psql, psql should print a message like the following: SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Make sure you're using a TCP connection instead of a local (Unix-domain) connection. You can use psql's -h option or the PGHOST environment variable to force a TCP connection (e.g., psql -h localhost). See also the hostssl and hostnossl connection types in pg_hba.conf. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] Constaints
On Sun, Dec 05, 2004 at 16:55:33 +, Jake Stride [EMAIL PROTECTED] wrote: Is there anyway to declare a constant that you can then use within a postgresql 'session', i am connecting from a PHP based application and trying to integrate another. What I want to be able to do is setup a rule on another table so that whenever a query is run on the table it appends another value to the query. i.e i want to be able to do: 'SET someconstant an_id=1;' Then with a rule, running: SELECT * FROM a_table would become: SELECT * FROM a_table WHERE id=a_id is this possible? At the worst you should be able to use a table with a row for each session that includes the value to be used for each session. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SSL confirmation
Andreas, this what I get when I issue the openssl command: 6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake failure:s23_lib.c:226: could you explain what this means if you know? When I do: /usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data I get: LOG: checkpoint record is at 0/DAB280 LOG: redo record is at 0/DAB280; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 1216; next OID: 17668 LOG: database system is ready LOG: invalid length of startup packet There is no mention of SSL at all regards Andrew On 5 Dec 2004, at 17:03, Andreas Seltenreich wrote: Andrew M. writes: To answer my own question I included the -l flag: /usr/local/pgsql/bin/postmaster -l -i -D /usr/local/pgsql/data No errors were reported, which I guess there would be if: 1. postgreSQL had not ben built with SSL support? or 2. the certificate has not been properly setup? You could also use openssl's utilities to diagnose the SSL part of the connection. For example: $ openssl s_client -host localhost -port port will show you details about the authentication and encryption in use. HTH Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Constaints
Bruno Wolff III wrote: On Sun, Dec 05, 2004 at 16:55:33 +, Jake Stride [EMAIL PROTECTED] wrote: Is there anyway to declare a constant that you can then use within a postgresql 'session', i am connecting from a PHP based application and trying to integrate another. At the worst you should be able to use a table with a row for each session that includes the value to be used for each session. Would this be a postgresql session? If so how do I go about accessing it from a query/setting the value of it? I have looked at set authorization but I don't think this is where I should be looking! Thanks Jake ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Constaints
On 12/5/2004 12:48 PM, Jake Stride wrote: Bruno Wolff III wrote: On Sun, Dec 05, 2004 at 16:55:33 +, Jake Stride [EMAIL PROTECTED] wrote: Is there anyway to declare a constant that you can then use within a postgresql 'session', i am connecting from a PHP based application and trying to integrate another. At the worst you should be able to use a table with a row for each session that includes the value to be used for each session. Would this be a postgresql session? If so how do I go about accessing it from a query/setting the value of it? I have looked at set authorization but I don't think this is where I should be looking! PostgreSQL does not have userland session variables. You would have to write some custom set/get functions in a procedural language that is capable of holding global data across function calls (like PL/Tcl). Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SSL confirmation
Andrew M. writes: this what I get when I issue the openssl command: 6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake failure:s23_lib.c:226: could you explain what this means if you know? I'm afraid, I think my suggestion to use openssl's s_client with the postmaster's builtin SSL support was bogus, since Magnus Hagander writes in an older message: SSL is not enabled at connection time in pgsql - it is negotiatied with the postmaster, and enabled later. URL:http://groups.google.de/groups?as_umsgid=81124B76C0CF364EBAC6CD213ABEDEF71D3095%40ARGON.edu.sollentuna.se So using the openssl tools won't help here. Sorry for the inconvenience Andreas ---(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] Index bloat in 7.2
I've got a box running 7.2.1 (yes, I know :-() in which an index for a rapidly turning over (and regularly vacuumed) table is growing steadily in size. The index in question is on a timestamp field that is just set to now() on the entry of the row, to enable the query that clears out old data to an archive to run efficiently. Reindexing shrinks it back to a reasonable size. Other indexes reach an equilibrium size and stay there. The behaviour is fine on a system running 7.4.x: the index stays at a sensible number of pages. Is this likely to be related to a known issue with 7.2 that got fixed, or have I got potentially more serious problems? Thanks Julian Scarfe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 06:59:41PM +0100, Andreas Seltenreich wrote: Andrew M. writes: this what I get when I issue the openssl command: 6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake failure:s23_lib.c:226: could you explain what this means if you know? I'm afraid, I think my suggestion to use openssl's s_client with the postmaster's builtin SSL support was bogus, since Magnus Hagander writes in an older message: SSL is not enabled at connection time in pgsql - it is negotiatied with the postmaster, and enabled later. URL:http://groups.google.de/groups?as_umsgid=81124B76C0CF364EBAC6CD213ABEDEF71D3095%40ARGON.edu.sollentuna.se So using the openssl tools won't help here. Right -- see the Frontend/Backend Protocol chapter in the documentation, in particular the SSL Session Encryption section: http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52782 You can use psql to check if SSL is working. Psql prints a message like the following if SSL was successfully negotiated: SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] Select distinct sorting all rows 8.0rc1
The planner is smarter with GROUP BY than with DISTINCT, so you can rewrite your query as the following, whihc will probaly use a HashAggregate, and be a lot faster : SELECT service_id FROM five_min_stats_200408 GROUP BY service_id; This won't avoid the Seq Scan however. If you know you have few different service_ids, you can code a plpgsql function which does this : SELECT INTO value service_id FROM five_min_stats_200408 ORDER BY service_id ASC LIMIT 1; WHILE FOUND RETURN NEXT value SELECT INTO value service_id FROM five_min_stats_200408 WHERE service_isvalue ORDER BY service_id ASC LIMIT 1; END WHILE Basically it skips from one value to the next using your index, and returns them as they come. You'll get one indexed SELECT by distinct value. If you have, say 100 distinct values in 1M rows it'll be many orders of magniude faster. estat= explain analyze select distinct(service_id) from five_min_stats_200408; QUERY PLAN Unique (cost=13578354.70..13894902.76 rows=726 width=12) (actual time=1227906.271..1282110.055 rows=879 loops=1) - Sort (cost=13578354.70..13736628.73 rows=63309612 width=12) (actual time=1227906.266..1255961.318 rows=63359396 loops=1) Sort Key: service_id - Seq Scan on five_min_stats_200408 (cost=0.00..1668170.12 rows=63309612 width=12) (actual time=0.061..80398.222 rows=63359396 loops=1) Total runtime: 1284212.556 ms (5 rows) Time: 1284213.359 ms ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SSL confirmation
Ok, is it possible to trace/monitor processes as they come into postgresql tables like you can in windows SQLServer? regards Andrew On 5 Dec 2004, at 18:27, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 06:59:41PM +0100, Andreas Seltenreich wrote: Andrew M. writes: this what I get when I issue the openssl command: 6521:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake failure:s23_lib.c:226: could you explain what this means if you know? I'm afraid, I think my suggestion to use openssl's s_client with the postmaster's builtin SSL support was bogus, since Magnus Hagander writes in an older message: SSL is not enabled at connection time in pgsql - it is negotiatied with the postmaster, and enabled later. URL:http://groups.google.de/groups? as_umsgid=81124B76C0CF364EBAC6CD213ABEDEF71D3095%40ARGON.edu.sollentun a.se So using the openssl tools won't help here. Right -- see the Frontend/Backend Protocol chapter in the documentation, in particular the SSL Session Encryption section: http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52782 You can use psql to check if SSL is working. Psql prints a message like the following if SSL was successfully negotiated: SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] SSL confirmation
On Sun, Dec 05, 2004 at 11:27:57AM -0700, Michael Fuhr wrote: Right -- see the Frontend/Backend Protocol chapter in the documentation, in particular the SSL Session Encryption section: http://www.postgresql.org/docs/7.4/static/protocol-flow.html#AEN52782 You can use psql to check if SSL is working. Psql prints a message like the following if SSL was successfully negotiated: SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) I tend to fire up ethereal and look at the data stream to make absolutely sure that my app is doing SSL to postgresql. I've been burnt once or twice by the libpq my app uses not negotiating SSL correctly while the version of libpq that psql uses being just fine (dumb build problems on my part, but I'd probably have missed them without the sanity check of sniffing the connection). Cheers, Steve ---(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] SSL confirmation
On Sun, Dec 05, 2004 at 06:37:57PM +, Andrew M wrote: is it possible to trace/monitor processes as they come into postgresql tables like you can in windows SQLServer? See the Run-time Configuration section in the Server Run-time Environment chapter of the PostgreSQL documentation. Look for the variables to configure logging. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 11:02:33AM -0800, Steve Atkins wrote: On Sun, Dec 05, 2004 at 11:27:57AM -0700, Michael Fuhr wrote: You can use psql to check if SSL is working. Psql prints a message like the following if SSL was successfully negotiated: SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) I tend to fire up ethereal and look at the data stream to make absolutely sure that my app is doing SSL to postgresql. Doesn't hurt to be sure. I've been burnt once or twice by the libpq my app uses not negotiating SSL correctly while the version of libpq that psql uses being just fine (dumb build problems on my part, but I'd probably have missed them without the sanity check of sniffing the connection). On the backend side you can force SSL by using hostssl in pg_hba.conf; connections that don't use SSL should then fail instead of silently proceeding unencrypted. On the client side you could set the PGSSLMODE environment variable to require (or the older PGREQUIRESSL to 1), which should tell libpq to attempt only SSL connections. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Index bloat in 7.2
Julian Scarfe [EMAIL PROTECTED] writes: I've got a box running 7.2.1 (yes, I know :-() in which an index for a rapidly turning over (and regularly vacuumed) table is growing steadily in size. The index in question is on a timestamp field that is just set to now() on the entry of the row, to enable the query that clears out old data to an archive to run efficiently. Reindexing shrinks it back to a reasonable size. Other indexes reach an equilibrium size and stay there. The behaviour is fine on a system running 7.4.x: the index stays at a sensible number of pages. That's exactly what I'd expect ... 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: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 12:27:33PM -0700, Michael Fuhr wrote: On the client side you could set the PGSSLMODE environment variable to require (or the older PGREQUIRESSL to 1), which should tell libpq to attempt only SSL connections. I forgot to mention that you could also use sslmode=require or requiressl=1 (deprecated in 7.4 and later) in your connect string if you're using libpq directly or your interface to libpq allows it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SSL confirmation
Michael, are you saying I need to do: 'select * from myTable where x=y sslmode=require' to make a request to the database a secure one? If so, as I am using Hibernate, do you know how I would alter my maps to reflect this? regards Andrew On 5 Dec 2004, at 19:41, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 12:27:33PM -0700, Michael Fuhr wrote: On the client side you could set the PGSSLMODE environment variable to require (or the older PGREQUIRESSL to 1), which should tell libpq to attempt only SSL connections. I forgot to mention that you could also use sslmode=require or requiressl=1 (deprecated in 7.4 and later) in your connect string if you're using libpq directly or your interface to libpq allows it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 08:07:26PM +, Andrew M wrote: are you saying I need to do: 'select * from myTable where x=y sslmode=require' to make a request to the database a secure one? If so, as I am using Hibernate, do you know how I would alter my maps to reflect this? No, sslmode=require would be part of the string that gets passed to libpq's PQconnectdb() or its ilk, i.e., the functions that make the initial connection to the database. If you're using an abstraction layer that sits above libpq or an interface that implements the communications protocol without using libpq, then you may or may not have a way to specify such connection options. Check your interface's documentation. If your interface sits above libpq but doesn't allow you to specify connection options like sslmode=require, then setting environment variables might still work. See the Environment Variables section of the libpq - C Library chapter in the PostgreSQL documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index bloat in 7.2
Clinging to sanity, [EMAIL PROTECTED] (Julian Scarfe) mumbled into her beard: I've got a box running 7.2.1 (yes, I know :-() in which an index for a rapidly turning over (and regularly vacuumed) table is growing steadily in size. The index in question is on a timestamp field that is just set to now() on the entry of the row, to enable the query that clears out old data to an archive to run efficiently. Reindexing shrinks it back to a reasonable size. Other indexes reach an equilibrium size and stay there. The behaviour is fine on a system running 7.4.x: the index stays at a sensible number of pages. Is this likely to be related to a known issue with 7.2 that got fixed, or have I got potentially more serious problems? The empty pages not reclaimed problem is something that did indeed get fixed in the post-7.2 days. I _think_ it was 7.4, but it might have been 7.3. When we were running 7.2, we used to fairly regularly (e.g. - about every other month) need to schedule maintenance windows in order to reindex tables in order to resolve this issue. Some indices on heavily-update tables would get pretty big dead zones that only reindexing would fix. The last it was discussed, there still seemed to be a _theoretical_ possibility of there still being a pathological case even in 7.4, but nobody has reported it in practice. That case would result if you dropped down to 1 index entry remaining live per page. That would be a very sparse handling of things, leaving 98% of the page empty, and there's no obvious mechanism to merge such pages back together. But as you're deleting _all_ old entries, that would clear out the relevant index pages entirely, so that they could be reclaimed. In short, 7.4.x is indeed a good resolution to your issue. -- let name=cbbrowne and tld=gmail.com in name ^ @ ^ tld;; http://www3.sympatico.ca/cbbrowne/sgml.html I would guess that he really believes whatever is politically advantageous for him to believe. -- Alison Brooks, referring to Michael Portillo, on soc.history.what-if ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SSL confirmation
Ah... ok I need something like: datasources local-tx-datasource jndi-namePostgresDS/jndi-name connection-urljdbc:postgresql://localhost:5432/beyarecords/ connection-url driver-classorg.postgresql.Driver/driver-class user-namemyName/user-name passwordmyPass/password sslmoderequire/sslmode /local-tx-datasource /datasources would that do the trick? Sorry for so many questions.. i have a lot to learn about postgreSQL ;-) regards Andrew On 5 Dec 2004, at 20:39, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 08:07:26PM +, Andrew M wrote: are you saying I need to do: 'select * from myTable where x=y sslmode=require' to make a request to the database a secure one? If so, as I am using Hibernate, do you know how I would alter my maps to reflect this? No, sslmode=require would be part of the string that gets passed to libpq's PQconnectdb() or its ilk, i.e., the functions that make the initial connection to the database. If you're using an abstraction layer that sits above libpq or an interface that implements the communications protocol without using libpq, then you may or may not have a way to specify such connection options. Check your interface's documentation. If your interface sits above libpq but doesn't allow you to specify connection options like sslmode=require, then setting environment variables might still work. See the Environment Variables section of the libpq - C Library chapter in the PostgreSQL documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 08:50:15PM +, Andrew M wrote: datasources local-tx-datasource jndi-namePostgresDS/jndi-name connection-urljdbc:postgresql://localhost:5432/beyarecords/ connection-url driver-classorg.postgresql.Driver/driver-class user-namemyName/user-name passwordmyPass/password sslmoderequire/sslmode /local-tx-datasource /datasources would that do the trick? Sorry for so many questions.. i have a lot to learn about postgreSQL ;-) I don't know if that would work or not -- I'm not familiar with the interface you're using. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL RPMs for 8.0.0rc1 are available
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi,
- -
PostgreSQL New RPM Set
2004-12-05
Version: 8.0.0rc1
Set labels: 8.0.0.rc1-2PGDG
- -
- -
Release Info:
PostgreSQL RPM Building Project
(http://www.pgfoundry.org/projects/pgsqlrpms) has released RPMs for
8.0.0rc1, and they are available in main FTP site and its mirrors. To find
the closest mirrors, please visit
http://www.PostgreSQL.org/mirrors-ftp.html
RPMs and SRPMs for Fedora Core {2-3} and Red Hat Linux 9, Red Hat
Enterprise Linux 3.0 are available now. RPMs for Fedora Core 1-x86 and
Fedora Core 2-x86_64 will be available shortly.
All the RPMs have been gpg-signed. You can find the detailed info on each
directory, in the CURRENT_MAINTAINER file (after sync...)
Thanks to Joe Conway for great efforts on this release cycle.
Please report any problems to [EMAIL PROTECTED]
Regards,
PostgreSQL RPM Build Project
- --
Devrim GUNDUZ
devrim~gunduz.orgdevrim.gunduz~linux.org.tr
http://www.tdmsoft.com
http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
iD8DBQFBs3h2tl86P3SPfQ4RAr6gAJ9fMR5x+2ebxyJsC6fgkP1u/dThqQCfTEM+
x3TvINYluaJ8stdSQDaTTJA=
=5kjp
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] SSL confirmation
The map i make reference to is a Jboss map used to make a jndi connection to postgreSQL. What document do I need to access to get hold of the envireonment variables, namely PGSSLMODE? regards Andrewx-tad-bigger /x-tad-bigger On 5 Dec 2004, at 21:00, Michael Fuhr wrote: On Sun, Dec 05, 2004 at 08:50:15PM +, Andrew M wrote: datasources> local-tx-datasource> jndi-name>PostgresDS/jndi-name> connection-url>jdbc:postgresql://localhost:5432/beyarecords/ connection-url> driver-class>org.postgresql.Driver/driver-class> user-name>myName/user-name> password>myPass/password> sslmode>require/sslmode> /local-tx-datasource> /datasources> would that do the trick? Sorry for so many questions.. i have a lot to learn about postgreSQL ;-) I don't know if that would work or not -- I'm not familiar with the interface you're using. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Constaints
On Sun, Dec 05, 2004 at 17:48:32 +, Jake Stride [EMAIL PROTECTED] wrote: Would this be a postgresql session? If so how do I go about accessing it from a query/setting the value of it? I have looked at set authorization but I don't think this is where I should be looking! At the start of each session you use an insert or update to update the table with the values using the session id (process id) as the key. Your views can refer to this table and the session id to get the appropiate value. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SSL confirmation
On Sun, Dec 05, 2004 at 09:10:42PM +, Andrew M wrote: The map i make reference to is a Jboss map used to make a jndi connection to postgreSQL. What document do I need to access to get hold of the envireonment variables, namely PGSSLMODE? I don't know if the J-stuff wraps libpq or if it implements the communications protocol on its own. If it uses libpq then see the libpq - C Library chapter in the PostgreSQL documentation, in particular the Database Connection Control Functions and Environment Variables sections. http://www.postgresql.org/docs/ Even if you're using an interface that abstracts libpq and you're not calling its functions directly, it's useful to know how the underlying library works. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
[GENERAL] Function Problem
Problem I am trying to store value in a TEMPORARY table and I am getting the following error ERROR: relation with OID 51533 does not exist Trigger CREATE TRIGGER createtemporytable AFTER INSERT ON component FOR EACH ROW EXECUTE PROCEDURE createtemp(); Function begin CREATE temporary TABLE primarykey ( componentpk Integer, plannerpk Integer, materialplanpk Integer, resourceplanpk Integer ); INSERT INTO primarykey(componentpk) VALUES (new.primary); UPDATE component set notes = 'Updated' where component.primary = primarykey.componentpk; end ---(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] SSL confirmation
Michael Fuhr [EMAIL PROTECTED] writes: On Sun, Dec 05, 2004 at 09:10:42PM +, Andrew M wrote: The map i make reference to is a Jboss map used to make a jndi connection to postgreSQL. What document do I need to access to get hold of the envireonment variables, namely PGSSLMODE? I don't know if the J-stuff wraps libpq or if it implements the communications protocol on its own. The latter. AFAIK it doesn't use environment variables. See the JDBC driver docs for how to set options when connecting. -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] source control integration
What is currently regarded as postgresql best-practice for controlling changes to a database? I currently administer SQL Server. I implemented a system which scripts every database object each hour (into a SQL script on the filesystem), and then uses SVN to track changes and email me if a change has occured, which then gives me the opportunity to review and commit the change. Is this sort of thing possible with postgresql? Sw. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Function Problem
On Mon, Dec 06, 2004 at 10:34:12AM +1100, Jamie Deppeler wrote: I am trying to store value in a TEMPORARY table and I am getting the following error ERROR: relation with OID 51533 does not exist The FAQ has a question regarding functions and temporary tables. See also past discussion in the list archives. http://www.postgresql.org/docs/faqs/FAQ.html http://archives.postgresql.org/ -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] When to encrypt
A customer of mine recently asked me to try a penetration test on his website, and I found a nice SQL Injection vulnerability. Using that vuln I was able to wander round his DB at will, viewing customer information, user logins, passwords, the lot. He asked me to make some recommendations, of which the first was to close the vulnerability. But it also got me thinking about encrypting sensitive information in the DB. If another SQL Injection vulnerability turns up (which it might, given the state of the website code), the data in the DB will be vulnerable to anyone who looks, which might be someone less friendly next time. My gut reaction is to say encrypt anything sensitive but a little thought makes that seem like an over reaction. After all, aren't modern database systems supposed to be secure in their own right? It seems silly to tell him to encrypt everything, including customer names and addresses, etc. - I've never heard of DB admin recommending such action - and it'll have an impact on performance. So where do I draw the line? Encrypt everything on the basis that it adds a layer of security? Encrypt nothing on the basis that there shouldn't be any way of accessing the sensitive stuff so the extra security isn't necessary? Or encrypt a few things, just in case? What do people recommend? ---(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] initdb error: could not identify current directory (or,
% cd /var/data/pgsql (B still reports the absolute path (B -- /Volumes/data/pgsql (B (BIt looks to me like you are attempting to mount a few volumes under /var? (B (BI think that's going to wrinkle your handkerchief in Darwin. (B (B-- (BJoel Rees [EMAIL PROTECTED] (Bdigitcom, inc. $B3t<02q
Re: [GENERAL] SSL confirmation
Hi, seems like I may have located the solution to my earlier problem: http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php many thanks Andrew On 5 Dec 2004, at 23:51, Doug McNaught wrote: Michael Fuhr [EMAIL PROTECTED] writes: On Sun, Dec 05, 2004 at 09:10:42PM +, Andrew M wrote: The map i make reference to is a Jboss map used to make a jndi connection to postgreSQL. What document do I need to access to get hold of the envireonment variables, namely PGSSLMODE? I don't know if the J-stuff wraps libpq or if it implements the communications protocol on its own. The latter. AFAIK it doesn't use environment variables. See the JDBC driver docs for how to set options when connecting. -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Constaints
On 2004-12-05, Jan Wieck [EMAIL PROTECTED] wrote: PostgreSQL does not have userland session variables. You would have to write some custom set/get functions in a procedural language that is capable of holding global data across function calls (like PL/Tcl). You can fake session variables by using constant-returning functions defined in the per-session temporary namespace. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] initdb error: could not identify current directory
hi joel, It looks to me like you are attempting to mount a few volumes under /var? actually, no. i'm symlinking local volumes mounted where they're supposed to be, in /Volumes, to ny /var/sub-hierarchy. I think that's going to wrinkle your handkerchief in Darwin. it's all been settled, actually (thread's discontinious ... sorry). turns out it was the perms/ownership of the actual mount dir /Volumes/X that were the(my) problem. cheers, richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Select distinct sorting all rows 8.0rc1
Pierre-Frédéric Caillaud wrote: The planner is smarter with GROUP BY than with DISTINCT, so you can rewrite your query as the following, whihc will probaly use a HashAggregate, and be a lot faster : SELECT service_id FROM five_min_stats_200408 GROUP BY service_id; Pierre-Frédéric, thanks! Vast improvement - this is definitely acceptable. It did indeed use a seq scan followed by a hash aggregate. Total runtime: 76295.775 ms estat= explain analyze select distinct(service_id) from five_min_stats_200408; QUERY PLAN Time: 1284213.359 ms -- Guy Rouillier ---(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] source control integration
Simon Wittber [EMAIL PROTECTED] writes: What is currently regarded as postgresql best-practice for controlling changes to a database? I currently administer SQL Server. I implemented a system which scripts every database object each hour (into a SQL script on the filesystem), and then uses SVN to track changes and email me if a change has occured, which then gives me the opportunity to review and commit the change. Is this sort of thing possible with postgresql? Look at pg_dump -s. I do something similar though I only pull the data on demand and check it in manually with a commit message. With 7.4 you get spurious changes that you have to strip out. My makefile entry for 7.4 is: schema.sql: pg_dump -U postgres -s db | sed '/^-- TOC entry/d;/^\\connect - postgres/,/^\\connect - db/d;/^SET search_path/d;/^$$/d;/^--$$/d' $@ I think the 8.0 pg_dump has removed the extraneous information. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] When to encrypt
Derek Fountain [EMAIL PROTECTED] writes:
If another SQL Injection vulnerability turns up (which it might, given the
state of the website code),
You will never see another SQL injection vulnerability if you simply switch to
always using prepared queries and placeholders. Make it a rule that you
_never_ interpolate variables into the query string. period. No manual quoting
to get right, no subtle security audit necessary: If the SQL query isn't a
constant string you reject it.
Any good driver should support prepared queries. Even on pre-8.0 it should
support them and emulate them by quoting parameters for you. The driver is
much less likely to get this wrong than you are. On 8.0 the driver can pass
the parameters to the server separately from the query in a binary protocol.
The interface you're looking for should look something like:
$sth = $dbh-prepare(select * from foo where id = ?);
$sth-execute($id);
$results = $sth-fetch();
...
Notice that there's absolutely nothing you can do to inject anything using
$id. And there's not really any way to get this code wrong and compromise
security.
Really interpolating variables in the query string should never have been
considered an acceptable coding practice. It's mixing executable code and
program data which has never been a good idea.
There are more complex queries than this where it can be useful to interpolate
some variables, things like
if ($view_all) {
$join_type = OUTER;
} else {
$join_type = ;
}
$sth = $dbh-execute(SELECT * FROM foo $join_type JOIN bar USING (x) where z
= ?);
but these types of queries are relatively rare and careful security analysis
should still be able to show that the query is still completely built up out
of program constants. Not parameters taken from outside data. That's still a
lot easier to prove than checking that every parameter is quoted properly.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] When to encrypt
In the last exciting episode, [EMAIL PROTECTED] (Derek Fountain) wrote: It seems silly to tell him to encrypt everything, including customer names and addresses, etc. - I've never heard of DB admin recommending such action - and it'll have an impact on performance. So where do I draw the line? Encrypt everything on the basis that it adds a layer of security? Encrypt nothing on the basis that there shouldn't be any way of accessing the sensitive stuff so the extra security isn't necessary? Or encrypt a few things, just in case? What do people recommend? There is a model for doing this sort of thing; look at Peter Wayner's _Translucent Databases_: http://www.wayner.org/books/td/ Most databases provide elaborate control mechanisms for letting the right people in to see the right records. These tools are well-designed and thoroughly tested, but they can only provide so much support. If someone breaks into the operating system itself, all of the data on the hard disk is unveiled. If a clerk, a supervisor, or a system administrator decides to turn traitor, there's nothing anyone can do. It's worth pointing out that the really serious classes of security breaches are of the latter sorts. I have seen several reports, of late, of _serious_ security breaches at major banks in my country that are, in a sense, of this nature. One of the banks, CIBC, had a central 1-800 number to which funds transfer request information was to be faxed by branches. A typo in the number, regularly made by branch staff, led to tens of thousands of records containing sensitive personal information being sent to a junkyard in West Virginia. Then there was the time medical billing records were used as props on a children's show. http://www.medbroadcast.ca/health_news_details.asp?news_channel_id=1000news_id=2279rating=1 In the same jurisdiction, ISM (which used to be an IBM consulting subsidiary) lost a disk drive containing client data for multiple insurance companies as well as multiple government departments. It appears an employee stole it in order to get a free disk drive; had the intent been more ominous, the results could certainly have been bad. There are database vendors that promote that they are more secure as a result of offering data encryption schemes; this seems a red herring as in order to be able to use the data, the encryption keys must correspondingly be available _in the database engine_, which makes them likely to be readily accessible by the very people that would be most dangerous should they turn traitor. That's not even the worst part; if the encryption keys are sitting in the DBMS, that even makes them vulnerable to capture by an outsider who finds a way to inject outside code into some DB interface. -- (reverse (concatenate 'string moc.liamg @ enworbbc)) http://linuxfinances.info/info/lisp.html Rules of the Evil Overlord #127. Prison guards will have their own cantina featuring a wide variety of tasty treats that will deliver snacks to the guards while on duty. The guards will also be informed that accepting food or drink from any other source will result in execution. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Rules
Hi, What i am trying to do is i have a join table eg primarykey field1 field2 and based on SQL Select have the values of field1 inserted into a new table which will scroll which will launch a trigger but im having problems getting this to work? Any ideas anyone? sql select may return 1 or more results so i will 1 insert statements ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] DBD::PgSPI 0.02
Hello, A short note that I've updated DBD::PgSPI version 0.02 to CPAN. There are no new features - but the code now expects (and works with) reasonably decent versions of perl (5.8.x) and pgsql (8.x). No warranty is given, this code compiles and 'scratches my itch'. If it happens to scratch yours, more the merrier. -alex ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] When to encrypt
On Monday 06 December 2004 12:31, you wrote: Derek Fountain [EMAIL PROTECTED] writes: If another SQL Injection vulnerability turns up (which it might, given the state of the website code), You will never see another SQL injection vulnerability if you simply switch to always using prepared queries and placeholders. much wisdom snipped Indeed, but I'm still interested in the general answer. The server I have been looking at was hopelessly insecure and SQL injection is only one of its problems. There were several other ways in! Assume, for example, an attacker can write his own script directly into the website document tree. In this case prepared queries don't help protect what's in the database. The attacker can use them himself if he likes! Given this type of mess, having logins, passwords, credit card info and the like encrypted in the DB will add another layer of protection. The question is, do people normally add this layer, just in case, or do they assume that all the previous layers will do the job? Personally I've never encrypted data in this way, but for this guy there does seem to be a requirement. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] DBD::PgSPI 0.02
On Mon, Dec 06, 2004 at 12:27:18AM -0500, [EMAIL PROTECTED] wrote: A short note that I've updated DBD::PgSPI version 0.02 to CPAN. There are no new features - but the code now expects (and works with) reasonably decent versions of perl (5.8.x) and pgsql (8.x). Using PostgreSQL 8.0.0rc1 and Perl 5.8.6 on FreeBSD 4.10-STABLE and Solaris 9, I had to make a couple of changes to get DBD::PgSPI to build: 1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise the build fails with: In file included from PgSPI.xs:14: PgSPI.h:16:22: postgres.h: No such file or directory PgSPI.h:17:21: funcapi.h: No such file or directory PgSPI.h:18:26: executor/spi.h: No such file or directory 2. Remove or comment out #include ppport.h from PgSPI.h. Neither of my systems have this file and the module builds without it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] [HACKERS] DBD::PgSPI 0.02
On Sun, 5 Dec 2004, Michael Fuhr wrote: Using PostgreSQL 8.0.0rc1 and Perl 5.8.6 on FreeBSD 4.10-STABLE and Solaris 9, I had to make a couple of changes to get DBD::PgSPI to build: 1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise the build fails with: You should point POSTGRES_HOME to src directory of your pgsql tree. In file included from PgSPI.xs:14: PgSPI.h:16:22: postgres.h: No such file or directory PgSPI.h:17:21: funcapi.h: No such file or directory PgSPI.h:18:26: executor/spi.h: No such file or directory 2. Remove or comment out #include ppport.h from PgSPI.h. Neither of my systems have this file and the module builds without it. Strange that 'make tardist' didn't include it. I'm not sure if its even required or not to have backward compatibility (to perl 5.4 for example) or not. Or whether I even care about backward compatibility. I'll remove it in next release, I suppose. -alex ---(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] [HACKERS] DBD::PgSPI 0.02
On Mon, Dec 06, 2004 at 01:38:27AM -0500, [EMAIL PROTECTED] wrote: On Sun, 5 Dec 2004, Michael Fuhr wrote: Using PostgreSQL 8.0.0rc1 and Perl 5.8.6 on FreeBSD 4.10-STABLE and Solaris 9, I had to make a couple of changes to get DBD::PgSPI to build: 1. Add -I$POSTGRES_HOME/include/server to Makefile.PL. Otherwise the build fails with: You should point POSTGRES_HOME to src directory of your pgsql tree. Why should the module need the PostgreSQL source code? It builds fine using the headers under the PostgreSQL install directory (e.g., /usr/local/pgsql/include), at least with 8.0.0rc1. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] rewrite count distinct query
Hi all, 'Scuse the long post :) I'm trying to include all relevant info.. I'm trying to work out a better way to approach a query, any tips are greatly appreciated. The relevant tables: db=# \d tp_conversions Table public.tp_conversions Column | Type | Modifiers ---++ conversionid | integer| not null default 0 type | character varying(10) | name | character varying(255) | amount| double precision | cookieid | character varying(32) | currtime | integer| ip| character varying(20) | origintype| character varying(20) | originfrom| character varying(255) | origindetails | character varying(255) | userid| integer| Indexes: tp_conversions_pkey primary key, btree (conversionid) conv_origindetails btree (origindetails) conv_originfrom btree (originfrom) conv_origintype btree (origintype) conv_time btree (currtime) conv_userid btree (userid) trackpoint=# SELECT count(*) from tp_conversions; count --- 261 (1 row) db=# \d tp_search Table public.tp_search Column | Type | Modifiers --++ searchid | integer| not null default 0 searchenginename | character varying(255) | keywords | character varying(255) | currtime | integer| ip | character varying(20) | landingpage | character varying(255) | cookieid | character varying(32) | userid | integer| Indexes: tp_search_pkey primary key, btree (searchid) search_cookieid btree (cookieid) search_keywords btree (keywords) search_searchenginename btree (searchenginename) search_userid btree (userid) trackpoint=# SELECT count(*) from tp_search; count --- 5086 (1 row) What I'm trying to do... Work out the number of conversions for each search origin. This query works: select count(distinct conversionid) as convcount, count(distinct searchid) as searchcount, (count(distinct conversionid) / count(distinct searchid)) as perc, s.searchenginename from tp_conversions c, tp_search s where c.origintype='search' and s.searchenginename=c.originfrom and s.userid=c.userid and c.userid=1 group by searchenginename order by convcount desc; convcount | searchcount | perc | searchenginename ---+-+--+-- 15 |2884 |0 | Google 1 | 110 |0 | Google AU 2 | 308 |0 | Google CA 1 | 25 |0 | Google CL 1 | 143 |0 | Google DE 1 | 117 |0 | Google IN 1 | 26 |0 | Google NZ 3 | 49 |0 | Google RO 1 | 60 |0 | Google TH 2 | 174 |0 | Yahoo (10 rows) However the percentage is wrong. I can cast one to a float: (count(distinct conversionid) / count(distinct searchid)::float) and it'll give me a better percentage: convcount | searchcount |perc | searchenginename ---+-+-+-- 15 |2884 | 0.00520110957004161 | Google 3 | 49 | 0.0612244897959184 | Google RO 2 | 308 | 0.00649350649350649 | Google CA 2 | 174 | 0.0114942528735632 | Yahoo 1 | 110 | 0.00909090909090909 | Google AU 1 | 25 |0.04 | Google CL 1 | 143 | 0.00699300699300699 | Google DE 1 | 117 | 0.00854700854700855 | Google IN 1 | 26 | 0.0384615384615385 | Google NZ 1 | 60 | 0.0167 | Google TH (10 rows) (I think the answer to this is 'no' but I'm going to ask anyway :P) Is there an easier way to get the more-detailed percentage (it's meant to work in multiple databases - so casting to a float won't work for other db's) ? More importantly... Is there a better way to write the query (I don't like the count(distinct...) but it works and gives the right info) ? I tried to do it with a union: SELECT count(searchid), searchenginename from tp_search s where userid=1 group by searchenginename union select count(conversionid), originfrom from tp_conversions c where c.userid=1 group by originfrom; but then realised that getting the data out with php would be a nightmare (plus I can't get the percentages). Lastly: db=# SELECT version(); version --- PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by GCC 2.95.4 (1 row) (I know it's a little out of date, upgrading later this week). Any suggestions/hints/tips welcome :) Thanks, Chris.
Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was:
[EMAIL PROTECTED] (Marc G. Fournier) wrote in news:[EMAIL PROTECTED]: On Sat, 4 Dec 2004, Rolf Xstvik wrote: I am curious. Where can i learn about these 'official newsgroups'? I can't find any information about them on www.postgresql.org. http://archives.postgresql.org/pgsql-announce/2004-11/msg00011.php I'm going to post that once a month so that new ppl know as well, so if anyone has any revisions they'd like to submit, please do ... And this will be posted to the mailing list? (and to pgsql.*) If i am looking for a usenet group then i don't want to read a mailing list. And if i am a new user, how can i easily find out that pgsql.* is the hierarchy i am looking for. Could it be an idea to mention it on the same web page which contain information about the mailing lists? -- Rolf Østvik ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
