Re: [GENERAL] What was new in 8.4 8.3?
Le 23/09/2010 01:28, Scott Ribe a écrit : The what's new in 9.0 document on the wiki is great. Is there anything similar for 8.4 8.3 so on? Nope, that's the first time we have this. Marc Cousin already did this in french for the 8.4 release. Unfortunately, it's only available in french. For those reading french, it's available here: http://blog.postgresql.fr/index.php?post/2009/04/28/Nouveaut%C3%A9s-PostgreSQL-8.4 I haven't had time to take advantage of all new features for a while. (Always read the release notes, just couldn't do much about them.) And now I'm looking at it being a good time to really update my dbs... You can probably find some technical articles in some IT magazines. I wrote some in France since the 8.3 release, I suppose you can find the same in an english magazine. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgsql-sql] Daily digest v1.3328 (5 messages)
On 23/09/2010 02:00, Adrian Klaver wrote: On Wednesday 22 September 2010 5:40:55 pm David Wilson wrote: On Wed, Sep 22, 2010 at 8:30 PM, Adrian Klaveradrian.kla...@gmail.comwrote: From here; http://www.postgresql.org/docs/8.4/interactive/datatype-boolean.html I believe the question relates to changing the string *output* of the database to something other than 't' and 'f', not an issue with input formats. Oops missed that. This would seem to an adapter problem. I work with Python and psycopg2 adapts the boolean values to True and False. Not sure about the PHP solution. AFAIK the native functions (pg_query() etc) output 't' and 'f', but PDO (which I've been using in the context of Zend Framework) output real true and false values. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Kill -9 for a session process caused all the sessions to be killed
Thanks Tom, I found the right way to kill the process. Regards, Atul Goel -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 22 September 2010 17:43 To: Atul Goel Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Kill -9 for a session process caused all the sessions to be killed atul.g...@globaldatapoint.com writes: I by mistake ran a query to update a huge table with around 50 rows and has to kill the session. I found the process-id from the query select * from pg_stat_activity. I killed the process using Kill -9 process_id. This caused all other sessions in the system to be killed and database was unreachable for a minute or so. MY QUESTION: Is this normal? Yes. That is not the recommended way to cancel a query. regards, tom lane This e-mail is confidential and should not be used by anyone who is not the original intended recipient. Global DataPoint Limited does not accept liability for any statements made which are clearly the sender's own and not expressly made on behalf of Global DataPoint Limited. No contracts may be concluded on behalf of Global DataPoint Limited by means of e-mail communication. Global DataPoint Limited Registered in England and Wales with registered number 3739752 Registered Office Middlesex House, 34-42 Cleveland Street, London W1T 4LB -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Index on points
Hello. If I have a table like this create table fleet ( ship_id integer, location point); and fill it with a lot of ships and their locations and then want to create an index on this to speed up operations on finding ships within a certain region (let's say its a rectangular region), how do I do this? I tried: CREATE INDEX my_index ON fleet USING gist ( box(location,location)); ? and that command worked, but I have no idea why and what I have just done. The docs are a little to advanced. How should one do it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What was new in 8.4 8.3?
On Thu, Sep 23, 2010 at 3:15 AM, Guillaume Lelarge guilla...@lelarge.info wrote: Nope, that's the first time we have this. Marc Cousin already did this in french for the 8.4 release. Unfortunately, it's only available in french. For those reading french, it's available here: http://blog.postgresql.fr/index.php?post/2009/04/28/Nouveaut%C3%A9s-PostgreSQL-8.4 Google translate does a useful job with that document. Very informative. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What was new in 8.4 8.3?
or even better, learn English properly and contribute your translation. I'm not whining that there's no Polish translation. French people are somewhat specific in that matter, don't get me started on aviation (cos it drives me insane). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What was new in 8.4 8.3?
Le 23/09/2010 14:42, Vick Khera a écrit : On Thu, Sep 23, 2010 at 3:15 AM, Guillaume Lelarge guilla...@lelarge.info wrote: Nope, that's the first time we have this. Marc Cousin already did this in french for the 8.4 release. Unfortunately, it's only available in french. For those reading french, it's available here: http://blog.postgresql.fr/index.php?post/2009/04/28/Nouveaut%C3%A9s-PostgreSQL-8.4 Google translate does a useful job with that document. Very informative. If Google translate is good enough, you can also take a look at this one: http://www.dalibo.org/hs44_postgresql_8.4 -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What was new in 8.4 8.3?
On 23/09/2010 13:55, Grzegorz Jaśkiewicz wrote: or even better, learn English properly and contribute your translation. I'm not whining that there's no Polish translation. French people are somewhat specific in that matter, don't get me started on aviation (cos it drives me insane). :-) I do a bit of flying, and though I haven't flown in France I've heard stories... in fairness, I gather that ATC is generally willing enough to switch to English when required. But we're getting a bit off-topic here :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What was new in 8.4 8.3?
2010/9/23 Raymond O'Donnell r...@iol.ie: On 23/09/2010 13:55, Grzegorz Jaśkiewicz wrote: or even better, learn English properly and contribute your translation. I'm not whining that there's no Polish translation. French people are somewhat specific in that matter, don't get me started on aviation (cos it drives me insane). :-) I do a bit of flying, and though I haven't flown in France I've heard stories... in fairness, I gather that ATC is generally willing enough to switch to English when required. But we're getting a bit off-topic here :-) ATC is fine, it is the other frenchman that are not. They don't use english to respond, when you hear english on radio. So if you want to fly there from the UK, you better learn some french (or skip the damn nation's airspace altogether). yes, offtopics ;) -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What was new in 8.4 8.3?
Actually, I found the feature matrix on the wiki, which serves my purpose pretty well--reminder of major new features that I might not have adopted... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Feature request: pgcron
Hi I did not see this feature on the TODO page on the wiki. I apologise if I have overlooked something similar. I would like postgres to have a cron-like feature for scheduling things to happen at particular times. An example of this might be routinely purging data from tables, or vacuuming specific tables. I realise that these can easily be done via cron itself, but I feel an internal implementation would be useful because: * The tasks would live with the database itself and become part of the schema and so are not forgotten if the database is moved across clusters or hosts. * Windows doesn't have cron, it uses its own system. This would provide a consistent way of running tasks regardless of platform. I hope this makes sense. Regards Martin A. Brooks http://antibodymx.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature request: pgcron
On Thu, Sep 23, 2010 at 16:20, Martin A. Brooks mar...@antibodymx.net wrote: Hi I did not see this feature on the TODO page on the wiki. I apologise if I have overlooked something similar. I would like postgres to have a cron-like feature for scheduling things to happen at particular times. An example of this might be routinely purging data from tables, or vacuuming specific tables. I realise that these can easily be done via cron itself, but I feel an internal implementation would be useful because: * The tasks would live with the database itself and become part of the schema and so are not forgotten if the database is moved across clusters or hosts. * Windows doesn't have cron, it uses its own system. This would provide a consistent way of running tasks regardless of platform. You can use pgAgent for this. It ships with pgAdmin3 (and as a separate download) and can do all this. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres wont drop foriegn keys on tables.
I am having an odd problem that I have seen before. It usually clears itself after I restart postgres. I am attempting to drop an foreign key on a table and it sits for hours and doesn't drop or put anything into the log. Killing the alter puts an error in, but it doesn't time out and it cause the system to lock at some point. What can I do to troubleshoot this? Chris
Re: [GENERAL] Postgres wont drop foriegn keys on tables.
Sorry, I am running the following. [postg...@pgprd01 pgcheck]$ psql --version psql (PostgreSQL) 8.4.2 contains support for command-line editing [postg...@pgprd01 pgcheck]$ uname -a Linux system.name.com 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux From: compuguruchrisbar...@hotmail.com To: pgsql-general@postgresql.org Subject: [GENERAL] Postgres wont drop foriegn keys on tables. Date: Thu, 23 Sep 2010 11:01:28 -0400 I am having an odd problem that I have seen before. It usually clears itself after I restart postgres. I am attempting to drop an foreign key on a table and it sits for hours and doesn't drop or put anything into the log. Killing the alter puts an error in, but it doesn't time out and it cause the system to lock at some point. What can I do to troubleshoot this? Chris
[GENERAL] update from 9.0rc1 to 9.0
hi, is an initdb and pg_upgrade required when upgrading from 9.0rc1 to final 9.0? there is no mention about that in the release notes. regards, jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to upgrade old cluster from 8.4 to 9.0
Hello, I have Debian Linux (unstable) server, and I am trying to upgrade 8.4 version database to 9.0, all developer packages for both versions were installed, as well as client ( postgresql-server-dev-8.4 postgresql-client-8.4 postgresql-server-dev-9.0 postgresql-client-9.0). Here is the command I use /usr/lib/postgresql/9.0/bin/pg_upgrade -d -c /var/lib/postgresql/8.4/main/ -D /var/lib/postgresql/9.0/main/ -b /usr/lib/postgresql/8.4/bin/ -B /usr/lib/postgresql/9.0/bin/ --user=postgres output is as follows: Performing Consistency Checks - Checking old data directory (-c) check for base failed: No such file or directory I stopped server as it was suggested in upgrade manual (http://developer.postgresql.org/pgdocs/postgres/pgupgrade.html) All data directories are exist: $ ls -lha /var/lib/postgresql/8.4/main/ total 72K drwx-- 11 postgres postgres 4.0K Sep 22 23:25 . drwxr-xr-x 3 root root 4.0K Sep 22 23:03 .. drwx-- 11 postgres postgres 4.0K Feb 13 2010 base drwx-- 2 postgres postgres 4.0K Sep 21 15:41 global drwx-- 2 postgres postgres 4.0K Mar 4 2010 pg_clog drwx-- 4 postgres postgres 4.0K Dec 3 2009 pg_multixact drwx-- 2 postgres postgres 4.0K Sep 21 15:41 pg_stat_tmp drwx-- 2 postgres postgres 4.0K Sep 12 00:58 pg_subtrans drwx-- 2 postgres postgres 4.0K Dec 3 2009 pg_tblspc drwx-- 2 postgres postgres 4.0K Dec 3 2009 pg_twophase -rw--- 1 postgres postgres4 Dec 3 2009 PG_VERSION drwx-- 3 postgres postgres 4.0K Sep 18 13:26 pg_xlog -rw-r--r-- 1 postgres postgres 17K Sep 22 23:25 postgresql.conf -rw--- 1 postgres postgres 133 Sep 15 21:01 postmaster.opts lrwxrwxrwx 1 root root 36 Dec 3 2009 server.crt - /etc/ssl/certs/ssl-cert-snakeoil.pem lrwxrwxrwx 1 root root 38 Dec 3 2009 server.key - /etc/ssl/private/ssl-cert-snakeoil.key $ ls -lha /var/lib/postgresql/9.0/main/ total 76K drwx-- 12 postgres postgres 4.0K Sep 23 13:34 . drwxr-xr-x 3 postgres postgres 4.0K Sep 21 15:49 .. drwx-- 6 postgres postgres 4.0K Sep 22 22:51 base drwx-- 2 postgres postgres 4.0K Sep 23 13:34 global drwx-- 2 postgres postgres 4.0K Sep 21 15:49 pg_clog drwx-- 4 postgres postgres 4.0K Sep 21 15:49 pg_multixact drwx-- 2 postgres postgres 4.0K Sep 23 11:52 pg_notify drwx-- 2 postgres postgres 4.0K Sep 23 13:34 pg_stat_tmp drwx-- 2 postgres postgres 4.0K Sep 21 15:49 pg_subtrans drwx-- 2 postgres postgres 4.0K Sep 21 15:49 pg_tblspc drwx-- 2 postgres postgres 4.0K Sep 21 15:49 pg_twophase -rw--- 1 postgres postgres4 Sep 21 15:49 PG_VERSION drwx-- 3 postgres postgres 4.0K Sep 21 15:49 pg_xlog -rw-r--r-- 1 postgres postgres 18K Sep 22 23:27 postgresql.conf -rw--- 1 postgres postgres 133 Sep 23 11:52 postmaster.opts lrwxrwxrwx 1 root root 36 Sep 22 23:00 server.crt - /etc/ssl/certs/ssl-cert-snakeoil.pem lrwxrwxrwx 1 root root 38 Sep 22 23:00 server.key - /etc/ssl/private/ssl-cert-snakeoil.key Can anyone please give some advice why pg_upgrade fails to uprage old cluster to new one? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres wont drop foriegn keys on tables.
On Thu, Sep 23, 2010 at 11:01 AM, Chris Barnes compuguruchrisbar...@hotmail.com wrote: I am attempting to drop an foreign key on a table and it sits for hours and doesn't drop or put anything into the log. Does the pg_stat_activity view show that it is waiting for a lock? It will need to lock both the origin and destination tables of the FK to clear the triggers. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres wont drop foriegn keys on tables.
Chris Barnes compuguruchrisbar...@hotmail.com writes: I am attempting to drop an foreign key on a table and it sits for hours and doesn't drop or put anything into the log. Dropping a foreign key is fast in itself, but it requires exclusive lock on both the referencing and referenced tables. Look to see what is blocking the lock request. pg_locks and pg_stat_activity views are your friends. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to upgrade old cluster from 8.4 to 9.0
There is already a bug filed for this: http://bugs.debian.org/597600 According to mpitt (the maintainer), the supported upgrade path is to use pg_upgradecluster On Thu, Sep 23, 2010 at 3:21 PM, Boris sw0rdf1s...@yahoo.com wrote: Hello, I have Debian Linux (unstable) server, and I am trying to upgrade 8.4 version database to 9.0, all developer packages for both versions were installed, as well as client ( postgresql-server-dev-8.4 postgresql-client-8.4 postgresql-server-dev-9.0 postgresql-client-9.0). Here is the command I use /usr/lib/postgresql/9.0/bin/pg_upgrade -d -c /var/lib/postgresql/8.4/main/ -D /var/lib/postgresql/9.0/main/ -b /usr/lib/postgresql/8.4/bin/ -B /usr/lib/postgresql/9.0/bin/ --user=postgres output is as follows: Performing Consistency Checks - Checking old data directory (-c) check for base failed: No such file or directory I stopped server as it was suggested in upgrade manual (http://developer.postgresql.org/pgdocs/postgres/pgupgrade.html) All data directories are exist: $ ls -lha /var/lib/postgresql/8.4/main/ total 72K drwx-- 11 postgres postgres 4.0K Sep 22 23:25 . drwxr-xr-x 3 root root 4.0K Sep 22 23:03 .. drwx-- 11 postgres postgres 4.0K Feb 13 2010 base drwx-- 2 postgres postgres 4.0K Sep 21 15:41 global drwx-- 2 postgres postgres 4.0K Mar 4 2010 pg_clog drwx-- 4 postgres postgres 4.0K Dec 3 2009 pg_multixact drwx-- 2 postgres postgres 4.0K Sep 21 15:41 pg_stat_tmp drwx-- 2 postgres postgres 4.0K Sep 12 00:58 pg_subtrans drwx-- 2 postgres postgres 4.0K Dec 3 2009 pg_tblspc drwx-- 2 postgres postgres 4.0K Dec 3 2009 pg_twophase -rw--- 1 postgres postgres4 Dec 3 2009 PG_VERSION drwx-- 3 postgres postgres 4.0K Sep 18 13:26 pg_xlog -rw-r--r-- 1 postgres postgres 17K Sep 22 23:25 postgresql.conf -rw--- 1 postgres postgres 133 Sep 15 21:01 postmaster.opts lrwxrwxrwx 1 root root 36 Dec 3 2009 server.crt - /etc/ssl/certs/ssl-cert-snakeoil.pem lrwxrwxrwx 1 root root 38 Dec 3 2009 server.key - /etc/ssl/private/ssl-cert-snakeoil.key $ ls -lha /var/lib/postgresql/9.0/main/ total 76K drwx-- 12 postgres postgres 4.0K Sep 23 13:34 . drwxr-xr-x 3 postgres postgres 4.0K Sep 21 15:49 .. drwx-- 6 postgres postgres 4.0K Sep 22 22:51 base drwx-- 2 postgres postgres 4.0K Sep 23 13:34 global drwx-- 2 postgres postgres 4.0K Sep 21 15:49 pg_clog drwx-- 4 postgres postgres 4.0K Sep 21 15:49 pg_multixact drwx-- 2 postgres postgres 4.0K Sep 23 11:52 pg_notify drwx-- 2 postgres postgres 4.0K Sep 23 13:34 pg_stat_tmp drwx-- 2 postgres postgres 4.0K Sep 21 15:49 pg_subtrans drwx-- 2 postgres postgres 4.0K Sep 21 15:49 pg_tblspc drwx-- 2 postgres postgres 4.0K Sep 21 15:49 pg_twophase -rw--- 1 postgres postgres4 Sep 21 15:49 PG_VERSION drwx-- 3 postgres postgres 4.0K Sep 21 15:49 pg_xlog -rw-r--r-- 1 postgres postgres 18K Sep 22 23:27 postgresql.conf -rw--- 1 postgres postgres 133 Sep 23 11:52 postmaster.opts lrwxrwxrwx 1 root root 36 Sep 22 23:00 server.crt - /etc/ssl/certs/ssl-cert-snakeoil.pem lrwxrwxrwx 1 root root 38 Sep 22 23:00 server.key - /etc/ssl/private/ssl-cert-snakeoil.key Can anyone please give some advice why pg_upgrade fails to uprage old cluster to new one? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to upgrade old cluster from 8.4 to 9.0
On 23 September 2010 10:51, Boris sw0rdf1s...@yahoo.com wrote: Hello, I have Debian Linux (unstable) server, and I am trying to upgrade 8.4 version database to 9.0, all developer packages for both versions were installed, as well as client ( postgresql-server-dev-8.4 postgresql-client-8.4 postgresql-server-dev-9.0 postgresql-client-9.0). Here is the command I use /usr/lib/postgresql/9.0/bin/pg_upgrade -d -c /var/lib/postgresql/8.4/main/ -D /var/lib/postgresql/9.0/main/ -b /usr/lib/postgresql/8.4/bin/ -B /usr/lib/postgresql/9.0/bin/ --user=postgres The -d and -c options appear to be the wrong way round. Looks like you're passing -c as the parameter for -d. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need magic for identifieing double adresses
On Thu, 16 Sep 2010 06:22:15 -0700, Andreas maps...@gmx.net wrote: It's not only typos to catch. There is variation in the way to write things that not necessarily are wrong. e.g. Miller's Bakery Bakery Miller Bakery Miller, Ltd. Bakery Miller and sons Bakery Smith (formerly Miller) and the usual Strawberry Street Strawberrystreet Strawberry Str.42 Strawberry Str. 42 Strawberry Str. 42-45 If this is a one-time procedure, I'd definitely go manually. The key is to quickly bind records and find the remaining ones. I'd create a lookup table and bind all similar values to a single value. I would also take each word in the field, turn it to lower case, remove punctuation signs and enter it in another table (original_word varchar, normalized_word varchar). I would then search for the most popular normalized_word, hoping that would throw me back keywords like strawberry and miller. I would then search for those to continue creating the look up table. You might want to write an interface to let you drag all the DISTINCT keywords and drop them to the single value. I have never seen it, though. :) Good luck. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to upgrade old cluster from 8.4 to 9.0
Thom Brown wrote: On 23 September 2010 10:51, Boris sw0rdf1s...@yahoo.com wrote: Hello, I have Debian Linux (unstable) server, and I am trying to upgrade 8.4 version database to 9.0, all developer packages for both versions were installed, as well as client ( postgresql-server-dev-8.4 postgresql-client-8.4 postgresql-server-dev-9.0 postgresql-client-9.0). Here is the command I use /usr/lib/postgresql/9.0/bin/pg_upgrade -d -c /var/lib/postgresql/8.4/main/ -D /var/lib/postgresql/9.0/main/ -b /usr/lib/postgresql/8.4/bin/ -B /usr/lib/postgresql/9.0/bin/ --user=postgres The -d and -c options appear to be the wrong way round. Looks like you're passing -c as the parameter for -d. Yep, that is it. In fact the error message confirms it: Checking old data directory (-c) check for base failed: No such file or directory It thinks the old data directory is -c. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] update from 9.0rc1 to 9.0
On Thu, Sep 23, 2010 at 11:14 AM, Jan Otto as...@me.com wrote: hi, is an initdb and pg_upgrade required when upgrading from 9.0rc1 to final 9.0? there is no mention about that in the release notes. the assumption is 'no' on rc-rc or rc-releases generally unless specifically indicated in the release notes that you must do so. it never hurts to keep the old binaries around, 'just in case' though :-). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] update from 9.0rc1 to 9.0
Jan Otto wrote: hi, is an initdb and pg_upgrade required when upgrading from 9.0rc1 to final 9.0? there is no mention about that in the release notes. No, there were no system catalog changes between 9.0rc1 and 9.0. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Visualize GiST Index
Andrew Hunter ahun...@ucalgary.ca writes: On 2010-09-22, at 9:56 PM, Tom Lane wrote: Andrew Hunter ahun...@ucalgary.ca writes: I have been trying to install the Gevel module but am getting an error when running make on the gevel files download. The error is: /contrib/contrib-global.mk: No such file or directory. I have also tried USE_PGXS=1 make, but get the same result. I am unable to find contrib-global.mk. Could we see the output of pg_config? PGXS = /usr/lib/postgresql/8.4/lib/pgxs/src/makefiles/pgxs.mk Hm. If that file is in fact present, I'd have expected make USE_PGXS=1 to work. The error you cite looks like what would happen without USE_PGXS --- are you sure you get the exact same error both ways? If you're still baffled, it might help to show Gevel's Makefile. It's possible it isn't invoking pgxs correctly. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How about synchronous notifications?
On Tue, Sep 21, 2010 at 12:23 PM, Lincoln Yeoh ly...@pop.jaring.my wrote:
To me what would also be useful would be synchronous notifications.
This would allow many programs to wait for events to happen rather than all
of them polling the database (and wasting CPU cycles, battery life, etc).
You could still poll with a suitable timeout if you want.
Example functions:
pg_listen(text[, buffersize]);
-- text is the channel the session wants to listen to, and buffersize is
the maximum number of payloads the buffer will queue up (default = 1).
-- immediately returns true if successful, false if failed.
pg_unlisten(text);
-- this unregisters the session's interest with the channel indicated by
text, and clears the relevant channel's buffer.
-- immediately returns true if successful, false if failed.
pg_wait(text [,timeout value in milliseconds]);
-- this waits on channel text for at most timeout milliseconds (timeout
default = NULL) and returns the notification payload.
-- returns NULL if timed out, or no notifications were sent.
-- a timeout value of NULL means wait indefinitely till a notification is
received, 0 means don't wait at all just return what's in the session's
channel buffer (which would be NULL if there were no notifications).
Example scenario:
session #1:
select pg_listen('channel 2');
pg_listen
---
t
(1 row)
SELECT pg_wait('channel 2');
-- this waits/blocks indefinitely till session #2 below
session #2:
SELECT pg_notify('channel 2','hi there');
session #1:
-- session 1 now unblocks and gives the following result
SELECT pg_wait('channel 2');
pg_wait
--
hi there
(1 row)
-- session 1 can now do other stuff here - check various tables for new
data, etc.
Would this be asking for too much? :)
I asked for something like this about 9 years ago, and was told to look into
something like pqwait, and waiting on PQsocket fds. But I think that's not
so simple if you are using stuff like ODBC/DBI/JDBC etc.
Yes it might be more scalable to use an external messaging server for this,
but it's often just not as convenient or as easy. With this you could have
many DB clients waiting for events and then checking tables, doing various
other things only when relevant stuff happens. Developers can then easily
write event triggered DB stuff, without having to deal with another service,
or looking for some messaging library for their language of choice, or
writing it from scratch. Basically if it supports JDBC/ODBC/DBI it will
work, and work the same way.
They can be effectively rigged. If you want to block and wait in a
single function call, you have to deliver notifications
mid-transaction (which is really, I think, what you are asking for).
This is prohibited strictly speaking but you can work around the issue
via dblink: dblink to self w/query that generates the notification.
As long as you are in read committed mode, the notified client can
respond back with a signal and any response data you want. Taking
advantage of read committed, you can loop w/sleep and wait for your
signal to be set or until an appropriate timeout occurs. If you had
the ability to send notifications immediately (which I believe to be
possible within the constraints of the new implementation), you could
do this w/o the dblink step.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] update from 9.0rc1 to 9.0
is an initdb and pg_upgrade required when upgrading from 9.0rc1 to final 9.0? there is no mention about that in the release notes. No, there were no system catalog changes between 9.0rc1 and 9.0. thank you bruce. regards, jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] update from 9.0rc1 to 9.0
is an initdb and pg_upgrade required when upgrading from 9.0rc1 to final 9.0? there is no mention about that in the release notes. No, there were no system catalog changes between 9.0rc1 and 9.0. thank you bruce. regards, jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] update from 9.0rc1 to 9.0
is an initdb and pg_upgrade required when upgrading from 9.0rc1 to final 9.0? there is no mention about that in the release notes. the assumption is 'no' on rc-rc or rc-releases generally unless specifically indicated in the release notes that you must do so. it never hurts to keep the old binaries around, 'just in case' though :-). thank you merlin. regards, jan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg 8.4 crashing.
I've got a problem with pg 8.4.4 crashing with a sig 6 abort due to a panic on failing to add a right sibling in an index. Log output: PANIC: failed to add item to the right sibling in index logged_in_uid STATEMENT: INSERT INTO logged_in ... Now, the column that's failing here is a serial that isn't referenced in the SQL statement, i.e. it gets the default nextval. OS: Ubuntu 8.04, Pg: 8.4.4 The only arg to ./configure was to change the prefix dir to ~/pg84 I've had this problem before, but didn't have the time to track it down. Now I do, and it's on a server I can play on a bit if I need to. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Visualize GiST Index
On 2010-09-23, at 10:27 AM, Tom Lane wrote: Andrew Hunter ahun...@ucalgary.ca writes: On 2010-09-22, at 9:56 PM, Tom Lane wrote: Andrew Hunter ahun...@ucalgary.ca writes: I have been trying to install the Gevel module but am getting an error when running make on the gevel files download. The error is: /contrib/contrib-global.mk: No such file or directory. I have also tried USE_PGXS=1 make, but get the same result. I am unable to find contrib-global.mk. Could we see the output of pg_config? PGXS = /usr/lib/postgresql/8.4/lib/pgxs/src/makefiles/pgxs.mk Hm. If that file is in fact present, I'd have expected make USE_PGXS=1 to work. The error you cite looks like what would happen without USE_PGXS --- are you sure you get the exact same error both ways? If you're still baffled, it might help to show Gevel's Makefile. It's possible it isn't invoking pgxs correctly. regards, tom lane Hi Tom, Yes, I got the same result with USE_PGXS=1. This is my first attempt at compiling a module for Postgresql, so it could well be something very simple that I am missing... Here is the content of the gevel Makefile subdir = contrib/gevel top_builddir = ../.. include $(top_builddir)/src/Makefile.global MODULES = gevel DATA_built = gevel.sql DOCS = README.gevel REGRESS = gevel include $(top_srcdir)/contrib/contrib-global.mk I have tried running this from the gevel directory under /usr/share/postgresql/8.4/contrib Thanks for your assistance. Regards Andrew Andrew Hunter PEng RPSurv PhD Assistant Professor Department of Geomatics Engineering Schulich School of Engineering University of Calgary T: +403.220.7377 F: +403.284.1980 E: ahunter (at) ucalgary (dot) ca ~
Re: [GENERAL] pg 8.4 crashing.
On Thu, Sep 23, 2010 at 2:28 PM, Scott Marlowe scott.marl...@gmail.com wrote: I've got a problem with pg 8.4.4 crashing with a sig 6 abort due to a panic on failing to add a right sibling in an index. Log output: PANIC: failed to add item to the right sibling in index logged_in_uid STATEMENT: INSERT INTO logged_in ... Now, the column that's failing here is a serial that isn't referenced in the SQL statement, i.e. it gets the default nextval. OS: Ubuntu 8.04, Pg: 8.4.4 The only arg to ./configure was to change the prefix dir to ~/pg84 I've had this problem before, but didn't have the time to track it down. Now I do, and it's on a server I can play on a bit if I need to. Hm, is this repeatable? Have you ruled out corruption? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Index on points
On Thu, 2010-09-23 at 12:45 +0200, A B wrote: Hello. If I have a table like this create table fleet ( ship_id integer, location point); I recommend taking a look into PostGIS: http://postgis.org Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Visualize GiST Index
Andrew Hunter ahun...@ucalgary.ca writes: Here is the content of the gevel Makefile subdir = contrib/gevel top_builddir = ../.. include $(top_builddir)/src/Makefile.global MODULES = gevel DATA_built = gevel.sql DOCS = README.gevel REGRESS = gevel include $(top_srcdir)/contrib/contrib-global.mk Oh ... well, there's your problem: it's not even *trying* to use PGXS. Replace it with this: MODULES = gevel DATA_built = gevel.sql DOCS = README.gevel REGRESS = gevel ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) else subdir = contrib/gevel top_builddir = ../.. include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif ... and file a bug with the gevel authors, whoever they are, telling them to fix that upstream. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg 8.4 crashing.
Scott Marlowe scott.marl...@gmail.com writes: I've got a problem with pg 8.4.4 crashing with a sig 6 abort due to a panic on failing to add a right sibling in an index. Log output: PANIC: failed to add item to the right sibling in index logged_in_uid STATEMENT: INSERT INTO logged_in ... If you can apply this patch: http://archives.postgresql.org/pgsql-committers/2010-08/msg00365.php it should tell you which index page is causing the problem. Then please dump that page with pg_filedump and send it in. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Restore/dump from /usr/local/pgsql/data directory
Hi, I have a copy of /usr/local/pgsql/data from old server. Is it possible to do a dump of the sql databases in this directory, so that I can easily migrate them to my current system? -Håvard Wahl Kongsgård -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore/dump from /usr/local/pgsql/data directory
kongs...@stud.ntnu.no wrote: Hi, I have a copy of /usr/local/pgsql/data from old server. Is it possible to do a dump of the sql databases in this directory, so that I can easily migrate them to my current system? Yes, run pg_dumpall, save it, and load it into the new system. You really haven't given us much information on what problem you are having. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore/dump from /usr/local/pgsql/data directory
On September 23, 2010 01:49:50 pm kongs...@stud.ntnu.no wrote: Hi, I have a copy of /usr/local/pgsql/data from old server. Is it possible to do a dump of the sql databases in this directory, so that I can easily migrate them to my current system? You should be able to launch a postmaster against it to do so, yes. It will need to be the same (major) version as the old server was running. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
On Sat, 2010-09-11 at 14:21 -0700, Gabe Nell wrote: Is there a way to get this without using hot standby? Why would you want to avoid using hot standby? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Visualize GiST Index
Hi Tom, Thanks very much for your assistance, your suggestion worked a treat. I have updated the gevel people. Regards Andrew On 2010-09-23, at 2:05 PM, Tom Lane wrote: Andrew Hunter ahun...@ucalgary.ca writes: Here is the content of the gevel Makefile subdir = contrib/gevel top_builddir = ../.. include $(top_builddir)/src/Makefile.global MODULES = gevel DATA_built = gevel.sql DOCS = README.gevel REGRESS = gevel include $(top_srcdir)/contrib/contrib-global.mk Oh ... well, there's your problem: it's not even *trying* to use PGXS. Replace it with this: MODULES = gevel DATA_built = gevel.sql DOCS = README.gevel REGRESS = gevel ifdef USE_PGXS PG_CONFIG = pg_config PGXS := $(shell $(PG_CONFIG) --pgxs) include $(PGXS) else subdir = contrib/gevel top_builddir = ../.. include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif ... and file a bug with the gevel authors, whoever they are, telling them to fix that upstream. regards, tom lane Andrew Hunter PEng RPSurv PhD Assistant Professor Department of Geomatics Engineering Schulich School of Engineering University of Calgary T: +403.220.7377 F: +403.284.1980 E: ahunter (at) ucalgary (dot) ca ~ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgsql-sql] Daily digest v1.3328 (5 messages)
Yes indeed, pg_query returns t or f as strings when selecting boolean columns. I was able to switch over to PDO with only an afternoon's work and it works perfectly for boolean columns, returning values that are properly interpreted as true and false by php, and by json_encode. I'm all set now. Thanks for the help! TJ O'Donnell On Thu, Sep 23, 2010 at 1:18 AM, Raymond O'Donnell r...@iol.ie wrote: On 23/09/2010 02:00, Adrian Klaver wrote: On Wednesday 22 September 2010 5:40:55 pm David Wilson wrote: On Wed, Sep 22, 2010 at 8:30 PM, Adrian Klaveradrian.kla...@gmail.comwrote: From here; http://www.postgresql.org/docs/8.4/interactive/datatype-boolean.html I believe the question relates to changing the string *output* of the database to something other than 't' and 'f', not an issue with input formats. Oops missed that. This would seem to an adapter problem. I work with Python and psycopg2 adapts the boolean values to True and False. Not sure about the PHP solution. AFAIK the native functions (pg_query() etc) output 't' and 'f', but PDO (which I've been using in the context of Zend Framework) output real true and false values. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Nested literal parsing rules?
Ok, so, stupid question: how does all this interact with COPY escaping
rules[1]?:
Backslash characters (\) can be used in the COPY data to quote
data characters that might otherwise be taken as row or column
delimiters. In particular, the following characters must be preceded
by a backslash if they appear as part of a column value: backslash
itself, newline, carriage return, and the current delimiter character.
I imagined that this would be just another layer, but in my initial
tests, I was not doing this, and things worked. When I add it in, I
get too many levels of escaping. I can see this with even something
fairly simple:
postgres=# select version();
version
---
PostgreSQL 8.4.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
(1 row)
postgres=# create type foo as (a text);
CREATE TYPE
postgres=# create table bar(a foo[]);
CREATE TABLE
postgres=# copy bar from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
{(baz)}
\.
ERROR: malformed array literal: {(\\baz\\)}
CONTEXT: COPY bar, line 1, column a: {(\\baz\\)}
postgres=# show standard_conforming_strings;
standard_conforming_strings
-
off
(1 row)
My escaping logic for the above COPY input: baz is double-quoted as
a value in a UDT. When I put that UDT in an array literal, I enclose
it in double quotes and I precede all double quote characters with a
backslash. Because of standard_conforming_strings being off, I escape
all backslashes with backslashes (so I now have two backslashes and a
quote). Because of text-mode COPY, I escape each of those again (so I
now have four backslashes and a quote).
I seem to be missing something. Does standard_conforming_strings not
apply during COPY? Or is there something else I'm missing?
Thanks,
[1]: http://www.postgresql.org/docs/8.3/static/sql-copy.html
---
Maciek Sakrejda | System Architect | Truviso
1065 E. Hillsdale Blvd., Suite 215
Foster City, CA 94404
www.truviso.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg 8.4 crashing.
On Thu, Sep 23, 2010 at 2:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Scott Marlowe scott.marl...@gmail.com writes: I've got a problem with pg 8.4.4 crashing with a sig 6 abort due to a panic on failing to add a right sibling in an index. Log output: PANIC: failed to add item to the right sibling in index logged_in_uid STATEMENT: INSERT INTO logged_in ... If you can apply this patch: http://archives.postgresql.org/pgsql-committers/2010-08/msg00365.php it should tell you which index page is causing the problem. Then please dump that page with pg_filedump and send it in. Patch applied. This crash happens on average about once a day. Happened twice yesterday, but hasn't happened today. I'll report back when / if it does it again. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Nested literal parsing rules?
Maciek Sakrejda msakre...@truviso.com writes: I seem to be missing something. Does standard_conforming_strings not apply during COPY? It does not. That setting is about literal strings in SQL commands. The COPY escaping rules do not depend on it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
