Re: [GENERAL] base backup and tar problems with disappearing files.
On 15 Mar 2011, at 3:06, Rajesh Kumar Mallah wrote: Dear Friends, While taking online basebackup we ignore tar exit codes of 1 . However under certain circumstances tar exits we code '2' which stands for 'Fatal Errors' . Eg in case of Cannot stat: No such file or directory encountered while taking backup of the pgdatadir . My question is can we ignore such errors of vanishing files ? is it normal ? I think the situation is arising because some table which were created before start of backup were dropped during the backup. But that is quite normal also. You should probably exclude the PG data directories from your file-system backups, there isn't much point in backing them up anyway. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d7f0b68235882060612259! -- 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] base backup and tar problems with disappearing files.
On 15 Mar 2011, at 7:46, Alban Hertroys wrote: On 15 Mar 2011, at 3:06, Rajesh Kumar Mallah wrote: Dear Friends, While taking online basebackup we ignore tar exit codes of 1 . However under certain circumstances tar exits we code '2' which stands for 'Fatal Errors' . Eg in case of Cannot stat: No such file or directory encountered while taking backup of the pgdatadir . My question is can we ignore such errors of vanishing files ? is it normal ? I think the situation is arising because some table which were created before start of backup were dropped during the backup. But that is quite normal also. You should probably exclude the PG data directories from your file-system backups, there isn't much point in backing them up anyway. I should refine that a bit... A file-system level backup backs up the files in a sequential order, while the database writes it's transactions in them in a pattern that's much closer to random order. As a result of that, your file-system backup is likely to contain the database files in an inconsistent state. If you subsequently try to recover from that backup, you rely on the ability of the database to recover from that inconsistent state. Postgres is pretty good at recovering, but there's no guarantee it will succeed. It's probably a bad idea to rely on that for your backups. Instead, for backing up your database, use one of the strategies outlined in the fine manual. Those are reliable. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4d7f1594235881055444518! -- 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] base backup and tar problems with disappearing files.
On 03/15/11 12:30 AM, Alban Hertroys wrote: On 15 Mar 2011, at 7:46, Alban Hertroys wrote: On 15 Mar 2011, at 3:06, Rajesh Kumar Mallah wrote: Dear Friends, While taking online basebackup we ignore tar exit codes of 1 . However under certain circumstances tar exits we code '2' which stands for 'Fatal Errors' . Eg in case of Cannot stat: No such file or directory encountered while taking backup of the pgdatadir . My question is can we ignore such errors of vanishing files ? is it normal ? I think the situation is arising because some table which were created before start of backup were dropped during the backup. But that is quite normal also. You should probably exclude the PG data directories from your file-system backups, there isn't much point in backing them up anyway. I should refine that a bit... A file-system level backup backs up the files in a sequential order, while the database writes it's transactions in them in a pattern that's much closer to random order. As a result of that, your file-system backup is likely to contain the database files in an inconsistent state. If you subsequently try to recover from that backup, you rely on the ability of the database to recover from that inconsistent state. Postgres is pretty good at recovering, but there's no guarantee it will succeed. It's probably a bad idea to rely on that for your backups. Instead, for backing up your database, use one of the strategies outlined in the fine manual. Those are reliable. um, I assumed from the original post that he was talking about taking a base backup in preparation for setting up WAL replication, presumably preceded by a call to pg_start_backup(), etc... -- 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] Huge spikes in number of connections doing PARSE
On Tue, Mar 15, 2011 at 12:13:21AM -0400, Noah Misch wrote: What is a typical lifespan for a backend in this system? What sort of connection pooling are you using, if any? quite long, but: we have n (~ 40 i think) web servers. each webserver has it's own pgbouncer (in session pooling). application connects to pgbouncer, issues (usually) single query, and disconnects. pgbouncers are set to close oder connections, if unused, after (i think 2 minutes. (i don't actually have access there). generally, through the day we see from 400 to 800 connections, mostly idle, but sometimes it goes much higher (like 1400), and then the connections are mostly parsing. depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Autocommit off - commits/rollbacks
Thanks alexander The on_error_rollback setting was exactly the switch I was looking for, thanks. However I tried to set this setting via the jdbc driver (connection object) or jdbc properties, but couldnt find a switch to enable this option. How can i set this setting from my java app? Regards and thanks in advance michael -Ursprüngliche Nachricht- Von: Alexander Pyhalov [mailto:a...@rsu.ru] Gesendet: Montag, 14. März 2011 17:28 An: Vogt, Michael Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Autocommit off - commits/rollbacks Hello. On 03/14/2011 12:24, Vogt, Michael wrote: I have a question, using the autocommit off option in postgres. As starting position I use a table called xxx.configuration using a unique id constraint. Why does postgres rollback the whole transaction after an error? I compared the behavior with oracle/hsql - those dbms commit whats possible. To illustrate my question, here are some examples: AFAIK, sqlplus uses savepoints behind the scene. So, you can do something like this: create table t1 (i int unique); CREATE TABLE begin; BEGIN * insert into t1 values(1); INSERT 0 1 * savepoint s1; SAVEPOINT * insert into t1 values(2); INSERT 0 1 * savepoint s2; SAVEPOINT * insert into t1 values(2); ERROR: duplicate key value violates unique constraint t1_i_key ! ROLLBACK TO SAVEPOINT s2; ROLLBACK * commit; COMMIT Or, you can just do: \set ON_ERROR_ROLLBACK on -- Best regards, Alexander Pyhalov, system administrator of Computer Center of Southern Federal University smime.p7s Description: S/MIME cryptographic signature
[GENERAL] Application Stack Builder and Slony
Hi, Who maintains the Slony-version that is accessible from Application Stack Builder on windows? Would be nice to get the version (for pg 8.4) updated to Slony 2.0.6... -- Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul] demo 2011 Tore Halvorsen || +052 0553034554 -- 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] Application Stack Builder and Slony
On 15/03/2011 11:41, Tore Halvorsen wrote: Hi, Who maintains the Slony-version that is accessible from Application Stack Builder on windows? Would be nice to get the version (for pg 8.4) updated to Slony 2.0.6... It's maintained by EnterpriseDB as part of the one-click installers. 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] Application Stack Builder and Slony
On Tue, Mar 15, 2011 at 12:54 PM, Raymond O'Donnell r...@iol.ie wrote: On 15/03/2011 11:41, Tore Halvorsen wrote: Who maintains the Slony-version that is accessible from Application Stack Builder on windows? It's maintained by EnterpriseDB as part of the one-click installers. Ok, I'll post to their forum and start hoping for the best. Thanks. -- Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul] demo 2011 Tore Halvorsen || +052 0553034554 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL for Holdem Manager could not be installed.
Hi, I was using Holdem Manager and Postgres database server until a month ago when it just stopped. Whatever I did didn't help, incl. a teamviewer session with one of the HM administrators. I've already read and done everything about that in FAQ, forums,net etc. and nothing helps. Please help! I am using Windows 7 64, I've also tried with/without firewalls, opened all local ports... Using Nod32 atm and Spybot that runs in the background, when ununstalled (both of them) postgres doesn' t work too Cheers, Dion -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-could-not-be-installed-tp3700954p3700954.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] pgstat wait timeout
Hello everyone Our system: Windows Server 2008 R2 Foundation PostgreSQL 9.0.1 64 bits Days ago, the following message began to appear in the logs every 50 seconds: 2011-03-15 00:07:06 BRT AVISO: pgstat wait timeout I searched the mailing lists. but couldn't find any useful information. Someone already encountered this problem before? Norberto -- 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] Huge spikes in number of connections doing PARSE
hubert depesz lubaczewski dep...@depesz.com writes: On Tue, Mar 15, 2011 at 12:13:21AM -0400, Noah Misch wrote: What is a typical lifespan for a backend in this system? What sort of connection pooling are you using, if any? quite long, but: we have n (~ 40 i think) web servers. each webserver has it's own pgbouncer (in session pooling). application connects to pgbouncer, issues (usually) single query, and disconnects. pgbouncers are set to close oder connections, if unused, after (i think 2 minutes. (i don't actually have access there). generally, through the day we see from 400 to 800 connections, mostly idle, but sometimes it goes much higher (like 1400), and then the connections are mostly parsing. If the backends are mostly idle then it'd be a good idea to set a smaller maximum limit on their number. Idle backends can be a performance risk pre-8.4 because of the cache reset problem Noah pointed to. You'd be better off having pgbouncer delay incoming queries until there's a free backend to pass them to. 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] Huge spikes in number of connections doing PARSE
On Tue, Mar 15, 2011 at 10:20:38AM -0400, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: On Tue, Mar 15, 2011 at 12:13:21AM -0400, Noah Misch wrote: What is a typical lifespan for a backend in this system? What sort of connection pooling are you using, if any? quite long, but: we have n (~ 40 i think) web servers. each webserver has it's own pgbouncer (in session pooling). application connects to pgbouncer, issues (usually) single query, and disconnects. pgbouncers are set to close oder connections, if unused, after (i think 2 minutes. (i don't actually have access there). generally, through the day we see from 400 to 800 connections, mostly idle, but sometimes it goes much higher (like 1400), and then the connections are mostly parsing. If the backends are mostly idle then it'd be a good idea to set a smaller maximum limit on their number. Idle backends can be a performance risk pre-8.4 because of the cache reset problem Noah pointed to. You'd be better off having pgbouncer delay incoming queries until there's a free backend to pass them to. while I understand it, and will pass this information to client, one thing bugs me - one the problem happens only sometimes, with relatively low count of backends? we saw database running normally with 1200 connections, yet sometimes, it has 500 connections, and then something happens and they all end up locking in this parse mode. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Huge spikes in number of connections doing PARSE
hubert depesz lubaczewski dep...@depesz.com writes: while I understand it, and will pass this information to client, one thing bugs me - one the problem happens only sometimes, with relatively low count of backends? we saw database running normally with 1200 connections, yet sometimes, it has 500 connections, and then something happens and they all end up locking in this parse mode. Well, if they're all busy, you're not going to get this cache reset problem. It's idle backends that have fallen behind that create the problem. 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] PostgreSQL for Holdem Manager could not be installed.
Please help! I am using Windows 7 64, I've also tried with/without firewalls, opened all local ports... Using Nod32 atm and Spybot that runs in the background, when ununstalled (both of them) postgres doesn' t work too Hi. I really don't know what a Holdem Manager is, but have you tried to connect to PostgreSQL directly (using pgadmin-III for example)? Is the process running, or does it fail at start time? Is there something in the log? regards Tomas -- 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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 5:09:27 am Alphadion wrote: Hi, I was using Holdem Manager and Postgres database server until a month ago when it just stopped. Whatever I did didn't help, incl. a teamviewer session with one of the HM administrators. I've already read and done everything about that in FAQ, forums,net etc. and nothing helps. Please help! I am using Windows 7 64, I've also tried with/without firewalls, opened all local ports... Using Nod32 atm and Spybot that runs in the background, when ununstalled (both of them) postgres doesn' t work too Some information required: What version of Postgres? How was Postgres installed? How do you know Postgres is not working? Or is that Holdem is not finding Postgres? Is all of this happening locally or is there a remote component? Cheers, Dion -- Adrian Klaver adrian.kla...@gmail.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] Maximum number of tables
Hi all, I am using postgres 9.0 under CentOS 5.5 (Dual Xeon Quad Core @ 2,44 GHz, 64GB RAM, 2TB RAID 5). In my case, postgres is used as a backend for the RDF store Sesame. I am trying to store a dataset that consists of approximately 900.000.000 insertions (organized in either 10 tables, or in an arbitrary number of tables exceeding 2000). Loading the data in 10 tables takes about two days, while in the 2nd case, an org.postgresql.util.PSQLException: ERROR: out of shared memory error is thrown. After browsing this list, I found this post http://archives.postgresql.org/pgsql-admin/2007-03/msg00215.php describing a problem similar to mine. The value I currently use for my shared buffers is 512mb, and 64mb for my temp buffers. I have also tried disabling the db's logs. Would you suggest changing these values for optimization purposes? I would also like to ask you if a maximum value for max_locks_per_transaction exists, and if you have any experience concerning how many tables I will be able to have under this maximum value (so that I can limit my application to this number). Thanks a lot, Manos -- === Manos Karpathiotakis National Kapodistrian University of Athens Department of Informatics Telecommunications, Office B25 Management of Data Information Knowledge Group Panepistimioupolis, Ilissia GR-15784 Athens, Greece Tel: +30.210.727.5159 Fax: +30.210.727.5214 e-mail: m...@di.uoa.gr ===
Re: [GENERAL] Huge spikes in number of connections doing PARSE
On Tue, Mar 15, 2011 at 10:58:52AM -0400, Tom Lane wrote: hubert depesz lubaczewski dep...@depesz.com writes: while I understand it, and will pass this information to client, one thing bugs me - one the problem happens only sometimes, with relatively low count of backends? we saw database running normally with 1200 connections, yet sometimes, it has 500 connections, and then something happens and they all end up locking in this parse mode. Well, if they're all busy, you're not going to get this cache reset problem. It's idle backends that have fallen behind that create the problem. thanks. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] test
test -- 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] PostgreSQL for Holdem Manager could not be installed.
I tried to install any of the versions but it always gives that message Could not connect to the database server, please check port 5432 and 127.0.0.1 or something like that. I've opened a range of ports on my router's page, and also opened one in my computer through the firewall. Holdem Manager is a tracking software for playing poker online. It stores all yours and opponents' hands and uses Postgresql for that. It was working properly until one day when it just stopped, giving a message about inability to onnect to database server. I tried system restore and everything possible, including a teamviewer session with one of the Holdem Manager forums admins. Nothing helped... I dont want to reinstall the whole Windows as I'm not sure if it's going to work. I tried installing HM and postgres on another computer in the same network and it doesnt work on it too... -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-could-not-be-installed-tp3700954p3708616.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PostgreSQL for Holdem Manager could not be installed.
On Tue, Mar 15, 2011 at 10:55 AM, Alphadion s_savo...@yahoo.com wrote: I tried to install any of the versions but it always gives that message Could not connect to the database server, please check port 5432 and 127.0.0.1 or something like that. I've opened a range of ports on my router's page, and also opened one in my computer through the firewall. Holdem Manager is a tracking software for playing poker online. It stores all yours and opponents' hands and uses Postgresql for that. It was working properly until one day when it just stopped, giving a message about inability to onnect to database server. I tried system restore and everything possible, including a teamviewer session with one of the Holdem Manager forums admins. Nothing helped... I dont want to reinstall the whole Windows as I'm not sure if it's going to work. I tried installing HM and postgres on another computer in the same network and it doesnt work on it too... Check your event viewer application log for interesting details. Confirm if any postgres processes are running in task manager. Look and see if postgres services is installed in service control manager, and if it is not running, try and start it. My money is on expired password issue on the postgres account. 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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 8:55:33 am Alphadion wrote: I tried to install any of the versions but it always gives that message Could not connect to the database server, please check port 5432 and 127.0.0.1 or something like that. I've opened a range of ports on my router's page, and also opened one in my computer through the firewall. Holdem Manager is a tracking software for playing poker online. It stores all yours and opponents' hands and uses Postgresql for that. It was working properly until one day when it just stopped, giving a message about inability to onnect to database server. I tried system restore and everything possible, including a teamviewer session with one of the Holdem Manager forums admins. Nothing helped... I dont want to reinstall the whole Windows as I'm not sure if it's going to work. I tried installing HM and postgres on another computer in the same network and it doesnt work on it too... To get this moving please answer the questions I provided earlier: What version of Postgres? How was Postgres installed? How do you know Postgres is not working? Or is that Holdem is not finding Postgres? Is all of this happening locally or is there a remote component? In addition: Anything happen a month ago when it stopped working? Software upgrade? Computer crash? Etc? -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-coul d-not-be-installed-tp3700954p3708616.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
Quote: Is all of this happening locally or is there a remote component? I dont know how to define that... -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-could-not-be-installed-tp3700954p3708753.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PostgreSQL for Holdem Manager could not be installed.
I am ready to conclude that the commercial software, Holdem Manager, is basically trying to farm out their database support to us. However, Holdem Manager has extensive help topics about getting Postgres working: http://faq.holdemmanager.com/categories/Frequently+Asked+Questions/PostgreSQL/ I think we just need to point users to those pages. --- Alphadion wrote: I tried to install any of the versions but it always gives that message Could not connect to the database server, please check port 5432 and 127.0.0.1 or something like that. I've opened a range of ports on my router's page, and also opened one in my computer through the firewall. Holdem Manager is a tracking software for playing poker online. It stores all yours and opponents' hands and uses Postgresql for that. It was working properly until one day when it just stopped, giving a message about inability to onnect to database server. I tried system restore and everything possible, including a teamviewer session with one of the Holdem Manager forums admins. Nothing helped... I dont want to reinstall the whole Windows as I'm not sure if it's going to work. I tried installing HM and postgres on another computer in the same network and it doesnt work on it too... -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-could-not-be-installed-tp3700954p3708616.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] PostgreSQL for Holdem Manager could not be installed.
The larger question is whether these forums should be used to help people get support for commercial products that embed Postgres. --- Alphadion wrote: Quote: Is all of this happening locally or is there a remote component? I dont know how to define that... -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-could-not-be-installed-tp3700954p3708753.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] PostgreSQL for Holdem Manager could not be installed.
Merlin Moncure-2 wrote: Check your event viewer application log for interesting details. Confirm if any postgres processes are running in task manager. Look and see if postgres services is installed in service control manager, and if it is not running, try and start it. My money is on expired password issue on the postgres account. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general The process is running and I've tried the service manager.. How can I renovate the pass? -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-could-not-be-installed-tp3700954p3708915.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 9:01:50 am Alphadion wrote: Quote: Is all of this happening locally or is there a remote component? I dont know how to define that... Are HM and the database both on the same machine or is the connection happening over the network? -- Adrian Klaver adrian.kla...@gmail.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] How to add hosts to pg_hba.conf and postgresql.conf?
Hi Guys, I'm trying to allow a remote host on our 10.3.55.X network remote access to a Postgres Database on the same network. We're running Solaris 10 with Postgres 83 My postgresql.conf looks like this; listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all port = 5432 My pg_hba.conf looks like this; # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1/32 trust hostall all 10.3.55.0/24 255.255.255.0 trust # IPv6 local connections: hostall all ::1/128 trust I restart Postgres with; svcadm restart svc:/application/database/postgresql_83:default_64bit But I get a connection refused if I try and telnet to port 5432 from a remote host. What am I missing? Thanks, -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3708421.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to add hosts to pg_hba.conf and postgresql.conf?
On 03/15/11 8:50 AM, general_lee wrote: svcadm restart svc:/application/database/postgresql_83:default_64bit But I get a connection refused if I try and telnet to port 5432 from a remote host. firewall? -- 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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 9:04:30 am Bruce Momjian wrote: The larger question is whether these forums should be used to help people get support for commercial products that embed Postgres. The eternal Open Source dilemma. Once it is released into the wild are we responsible for it? In a perfect world the folks that incorporate Postgres into their products would be entirely accountable for its care and feeding. Part of me says hold to that standard. Another part says how do you know where a question is really coming from? I for one am not going to interrogate everyone on their reasons for asking a question. The other issue is perception. Right or wrong, not answering questions ends up reflecting negatively on the community. -- Adrian Klaver adrian.kla...@gmail.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] base backup and tar problems with disappearing files.
Sorry Alban , For the late update. Yep John, I am talking about the tar'ring of pgdatadir only excluding the pg_xlog dir. We have set up our full backup system in accordance to the admin guide. Even the guide puts forward the limitation of tar in producing distinguishing exit codes. My doubt at this moment is , Is it normal to expect files disappearing from the pgdatadir during the course of taking base backup ? I can think about the temp sorting files disappearing but i am not sure what could cause data files disappear like example given below: tar: /mnt/disk1/pgdatadir/base/16399/861272781: Cannot stat: No such file or directory If the above incidence is normal then i would only worry about making tar not to worry about the disappearing files. The reason of putting this question is that the line in one of the later paras of section 24.3.2 says Some file system backup tools emit warnings or errors if the files they are trying to copy change while the copy proceeds. , it only says about change not disappearance. Since i have deep respect for the excellent documentation quality of PostgreSQL project i read and interpret it by words. Warm Regds -mallah. On Tue, Mar 15, 2011 at 1:42 PM, John R Pierce pie...@hogranch.com wrote: On 03/15/11 12:30 AM, Alban Hertroys wrote: On 15 Mar 2011, at 7:46, Alban Hertroys wrote: On 15 Mar 2011, at 3:06, Rajesh Kumar Mallah wrote: Dear Friends, While taking online basebackup we ignore tar exit codes of 1 . However under certain circumstances tar exits we code '2' which stands for 'Fatal Errors' . Eg in case of Cannot stat: No such file or directory encountered while taking backup of the pgdatadir . My question is can we ignore such errors of vanishing files ? is it normal ? I think the situation is arising because some table which were created before start of backup were dropped during the backup. But that is quite normal also. You should probably exclude the PG data directories from your file-system backups, there isn't much point in backing them up anyway. I should refine that a bit... A file-system level backup backs up the files in a sequential order, while the database writes it's transactions in them in a pattern that's much closer to random order. As a result of that, your file-system backup is likely to contain the database files in an inconsistent state. If you subsequently try to recover from that backup, you rely on the ability of the database to recover from that inconsistent state. Postgres is pretty good at recovering, but there's no guarantee it will succeed. It's probably a bad idea to rely on that for your backups. Instead, for backing up your database, use one of the strategies outlined in the fine manual. Those are reliable. um, I assumed from the original post that he was talking about taking a base backup in preparation for setting up WAL replication, presumably preceded by a call to pg_start_backup(), etc... -- 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 8.3 vs. 8.4 - Query plans and performance
On Mon, Mar 14, 2011 at 9:48 AM, Jo jl.n...@uni-bonn.de wrote: I set the work_mem to 100MB and the shared buffers are 2 GB The query plans are long and complex. I send the beginning of the two plans. Hope this helps to understand the differences. I assume the join strategy in 8.3 differs from the one in 8.4. * The beginning of the 8.4: * Seq Scan on relations (cost=0.00..1502557856.52 rows=332613 width=24) Filter: (((SubPlan 36) OR (SubPlan 37)) AND (SubPlan 38)) SubPlan 1 - Index Scan using idx_relation_tags_relation_id on relation_tags (cost=0.00..8.97 rows=1 width=0) Index Cond: (relation_id = $0) Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text)) SubPlan 2 well, regardless of the version, you're doing a gazillion sequential scans on relation tags. This looks like the primary culprit (I had to look up the ~~* operator...it's 'ilike'): ( (k ~~* 'boundary'::text) OR ( (k ~~* 'type'::text) AND (v ~~* 'boundary'::text) AND (relation_id = $0) ) ) 1. do we really. really need to be using ~~* here? how about '~~' (like) or '=' 2. can we see definition and indexes on relation_tags? In particular, have you considered an index on (k,v,relation_id), or maybe one on (relation_id, v, k) and one on k? 3. can we see the source query? 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] PostgreSQL for Holdem Manager could not be installed.
Adrian Klaver wrote: On Tuesday, March 15, 2011 9:04:30 am Bruce Momjian wrote: The larger question is whether these forums should be used to help people get support for commercial products that embed Postgres. The eternal Open Source dilemma. Once it is released into the wild are we responsible for it? In a perfect world the folks that incorporate Postgres into their products would be entirely accountable for its care and feeding. Part of me says hold to that standard. Another part says how do you know where a question is really coming from? I for one am not going to interrogate everyone on their reasons for asking a question. The other issue is perception. Right or wrong, not answering questions ends up reflecting negatively on the community. I think we should make sure they have checked the vendor help system first or point them to that. -- 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] How to add hosts to pg_hba.conf and postgresql.conf?
On Tuesday, March 15, 2011 8:50:34 am general_lee wrote: Hi Guys, I'm trying to allow a remote host on our 10.3.55.X network remote access to a Postgres Database on the same network. We're running Solaris 10 with Postgres 83 My postgresql.conf looks like this; listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all port = 5432 My pg_hba.conf looks like this; # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1/32 trust hostall all 10.3.55.0/24 255.255.255.0 trust # IPv6 local connections: hostall all ::1/128 trust I restart Postgres with; svcadm restart svc:/application/database/postgresql_83:default_64bit But I get a connection refused if I try and telnet to port 5432 from a remote host. Is this a connection refused from Postgres or telnet? What am I missing? Thanks, -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
On Tue, Mar 15, 2011 at 11:04 AM, Bruce Momjian br...@momjian.us wrote: The larger question is whether these forums should be used to help people get support for commercial products that embed Postgres. I have no particular philosophical objection to helping people using Holdem Manager -- I'd venture most people posting here asking for help are working on commercial software, and I'd prefer people to come away from this forum with a positive impression. It's not like we are getting 100 emails a day on this stuff. This is a participatory environment -- so what's the fuss? That said, to the OP, on this issue you might have better luck on irc channel with this type of problem. 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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 9:26:44 am Bruce Momjian wrote: Adrian Klaver wrote: On Tuesday, March 15, 2011 9:04:30 am Bruce Momjian wrote: The larger question is whether these forums should be used to help people get support for commercial products that embed Postgres. The eternal Open Source dilemma. Once it is released into the wild are we responsible for it? In a perfect world the folks that incorporate Postgres into their products would be entirely accountable for its care and feeding. Part of me says hold to that standard. Another part says how do you know where a question is really coming from? I for one am not going to interrogate everyone on their reasons for asking a question. The other issue is perception. Right or wrong, not answering questions ends up reflecting negatively on the community. I think we should make sure they have checked the vendor help system first or point them to that. Actually in this case that had already been done:) From the original post: Whatever I did didn't help, incl. a teamviewer session with one of the HM administrators. I've already read and done everything about that in FAQ, forums,net etc. and nothing helps. -- Adrian Klaver adrian.kla...@gmail.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] PostgreSQL for Holdem Manager could not be installed.
On 03/15/11 9:26 AM, Bruce Momjian wrote: I think we should make sure they have checked the vendor help system first or point them to that. well, the OP did mention a troubleshooting session with the HM support folks using teamviewer. if they couldn't fix it, well, I dunno how we're going to be able to help without a LOT more information than is likely to be available. win7 definately made a mess out of the windows NT security model -- 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] PostgreSQL for Holdem Manager could not be installed.
On Tuesday, March 15, 2011 9:31:17 am Merlin Moncure wrote: On Tue, Mar 15, 2011 at 11:04 AM, Bruce Momjian br...@momjian.us wrote: The larger question is whether these forums should be used to help people get support for commercial products that embed Postgres. I have no particular philosophical objection to helping people using Holdem Manager -- I'd venture most people posting here asking for help are working on commercial software, and I'd prefer people to come away from this forum with a positive impression. It's not like we are getting 100 emails a day on this stuff. This is a participatory environment -- so what's the fuss? Not only that, but should a particular product show up consistently it becomes an opportunity, for those on this list that do offer commercial support, to make a 'warm' call. That said, to the OP, on this issue you might have better luck on irc channel with this type of problem. merlin -- Adrian Klaver adrian.kla...@gmail.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] How to add hosts to pg_hba.conf and postgresql.conf?
Both the below were ran on the Postgres Server. $ telnet 5432 Trying ... telnet: Unable to connect to remote host: Connection refused $ psql -h -U post_owner -d post_db psql: could not connect to server: Connection refused Is the server running on host and accepting TCP/IP connections on port 5432? Thanks, -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3709528.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to add hosts to pg_hba.conf and postgresql.conf?
Hi, Thanks for the reply. Nope, not a Firewall problem. I also get connection refused if I telnet by IP address on the Postgres server. I can telnet localhost 5432 But not telnet 5432 Anything else I can try here? I'm not sure my pg_hba.conf is correct, what do you think? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3709271.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to add hosts to pg_hba.conf and postgresql.conf?
Adrian Klaver adrian.kla...@gmail.com writes: On Tuesday, March 15, 2011 8:50:34 am general_lee wrote: But I get a connection refused if I try and telnet to port 5432 from a remote host. Is this a connection refused from Postgres or telnet? Postgres doesn't ever emit an error message spelled like that. This error means the connection request is never getting to the postmaster, which means either (a) the postmaster isn't actually running, or at least isn't listening on the port you think it is, or (b) there's a packet filter or something similar blocking the connection request from getting through. If you can connect locally but not remotely, then it's almost certainly (b). Check network-related settings to see about opening up port 5432. 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 to add hosts to pg_hba.conf and postgresql.conf?
can you post output of netstat -lnp when run on the machine running postgresql server ? On Tue, Mar 15, 2011 at 9:54 PM, general_lee tim_stockf...@hotmail.comwrote: Hi, Thanks for the reply. Nope, not a Firewall problem. I also get connection refused if I telnet by IP address on the Postgres server. I can telnet localhost 5432 But not telnet 5432 Anything else I can try here? I'm not sure my pg_hba.conf is correct, what do you think? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3709271.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PostgreSQL for Holdem Manager could not be installed.
Adrian Klaver wrote: On Tuesday, March 15, 2011 9:31:17 am Merlin Moncure wrote: On Tue, Mar 15, 2011 at 11:04 AM, Bruce Momjian br...@momjian.us wrote: The larger question is whether these forums should be used to help people get support for commercial products that embed Postgres. I have no particular philosophical objection to helping people using Holdem Manager -- I'd venture most people posting here asking for help are working on commercial software, and I'd prefer people to come away from this forum with a positive impression. It's not like we are getting 100 emails a day on this stuff. This is a participatory environment -- so what's the fuss? Not only that, but should a particular product show up consistently it becomes an opportunity, for those on this list that do offer commercial support, to make a 'warm' call. Well, also consider how many subscribers to this email list might not want to read Holdem Manager problem reports. As long as the reports are generic to Postgres, I think we are fine, but I am unclear how many of these are released to Holdem Manager or the Holdem Manager installer? The same issue exists for the EnterpriseDB installers. -- 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] Maximum number of tables
Manos Karpathiotakis m...@di.uoa.gr writes: Hi all, I am using postgres 9.0 under CentOS 5.5 (Dual Xeon Quad Core @ 2,44 GHz, 64GB RAM, 2TB RAID 5). In my case, postgres is used as a backend for the RDF store Sesame. I am trying to store a dataset that consists of approximately 900.000.000 insertions (organized in either 10 tables, or in an arbitrary number of tables exceeding 2000). Loading the data in 10 tables takes about two days, while in the 2nd case, an org.postgresql.util.PSQLException: ERROR: out of shared memory error is thrown. I'm guessing that you're seeing this through some client-side code that helpfully suppresses the HINT about raising max_locks_per_transaction :-( If your application tries to touch all 2000 tables in one transaction, you will need to raise that parameter to avoid running out of locktable space for the AccessShareLocks it needs on all those tables. However ... generally speaking, I'd guess that whatever you did to refactor 10 tables into 2000 was a bad idea --- one table with an extra key column is almost always a better design than N basically-identical tables. The latter will be a *lot* harder to use, and probably won't fix your performance problem anyway. You should attack the performance problem in a different way. Have you read http://www.postgresql.org/docs/9.0/static/populate.html ? 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 to add hosts to pg_hba.conf and postgresql.conf?
On 03/15/2011 09:36 AM, general_lee wrote: Both the below were ran on the Postgres Server. $ telnet 5432 Trying ... telnet: Unable to connect to remote host: Connection refused $ psql -h -U post_owner -d post_db psql: could not connect to server: Connection refused Is the server running on host and accepting TCP/IP connections on port 5432? With the above connection string you are not actually connecting to a host. From the error message 'Is the server running on host ...' Thanks, -- Adrian Klaver adrian.kla...@gmail.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] How to add hosts to pg_hba.conf and postgresql.conf?
On 15/03/2011 16:36, general_lee wrote: Both the below were ran on the Postgres Server. $ telnet 5432 Trying ... telnet: Unable to connect to remote host: Connection refused $ psql -h -U post_owner -d post_db psql: could not connect to server: Connection refused Is the server running on host and accepting TCP/IP connections on port 5432? Don't you need to have a host in there? - e.g. $ psql -h 127.0.0.1 etc 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] How to add hosts to pg_hba.conf and postgresql.conf?
On 03/15/11 10:33 AM, Raymond O'Donnell wrote: Don't you need to have a host in there? - e.g. $ psql -h 127.0.0.1 etc $5 gets you $20, the OP was obfuscating his hosts/IPs. -- 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 to add hosts to pg_hba.conf and postgresql.conf?
I'm trying to allow a remote host on our 10.3.55.X network remote access to a Postgres Database on the same network. We're running Solaris 10 with Postgres 83 My postgresql.conf looks like this; listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all port = 5432 My pg_hba.conf looks like this; # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1/32 trust hostall all 10.3.55.0/24 255.255.255.0 trust # IPv6 local connections: hostall all ::1/128 trust I restart Postgres with; svcadm restart svc:/application/database/postgresql_83:default_64bit But I get a connection refused if I try and telnet to port 5432 from a remote host. I'll give you pretty good odds your postmaster is not listening on '*' like you want it to. What does 'netstat -a | grep LISTEN' tell you? Benny -- Hairy ape nads.-- Colleen, playing Neverwinter Nights -- 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 to add hosts to pg_hba.conf and postgresql.conf?
To confirm, 10.3.55.182 is the Postgres Server $ telnet 10.3.55.182 5432 Trying ... telnet: Unable to connect to remote host: Connection refused $ telnet localhost 5432 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. $ psql -h 10.3.55.182 -U post_owner -d post_db psql: could not connect to server: Connection refused Is the server running on host and accepting TCP/IP connections on port 5432? My pg_hba.conf looks like this; # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1/32 trust hostall all 10.3.55.0/24 255.255.255.255 trust # IPv6 local connections: hostall all ::1/128 trust My postgresql.conf looks like this; # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 100 -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3711674.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to add hosts to pg_hba.conf and postgresql.conf?
Hi, I've checked IP Filter is disabled... Also this is Solaris 10 - netstat -lnp is not recognised. Here is netstat -np without my macs :) I'm using vnet3 so looks like I'm using the wrong netmask, tried editing pg_hba.conf to 255.255.255.255 instead but still no joy... Device IP Address Mask Flags Phys Addr -- --- --- vnet3 10.3.55.1255.255.255.255 o vnet3 10.3.55.181 255.255.255.255 o vnet3 10.3.55.8255.255.255.255 o vnet3 10.3.55.9255.255.255.255 o vnet3 10.3.55.4255.255.255.255 vnet1 10.3.200.182 255.255.255.255 SPLA vnet3 10.3.55.182 255.255.255.255 SPLA vnet1 224.0.0.0240.0.0.0 SM vnet3 224.0.0.0240.0.0.0 SM -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3711224.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PostgreSQL for Holdem Manager could not be installed.
Depending on whether the vendor or user is expected to install and setup PostgreSQL the issue is that the vendor should be contacting the list and asking for help with one of their users/setups as opposed to the end-user asking directly for help. The end-user shouldn't be expected to have the skill and knowledge to debug/fix their database setup if the vendor is providing and setting up the database during install. Now, if the vendor leaves PostgreSQL integration to the end-user then maybe the end-user can be expected to make a somewhat informed choice as to which back-end they wish to use and should be expected to at least be able to assist in their own trouble-shooting if they plan to make use of community resources. In this case making the effort to isolate the problem with respect to the software or the database. Depending on the un-importance of data loss I would be surprised if a full uninstall of both the software and PostgreSQL (including manual deletion of any data directorie and maybe the user account) did not at least make the software functional again. As for the working on commercial software comment - that is true but in most of those cases the interrogator is mostly able to separate the database and the software and thus isolate and ask pointed questions. When a software end-user ends up asking about the database that ability is limited. Hence why the software provider, and not the user, should be asking the questions. One last thought comes to mind is that having an unsecure distribution that is much easier to setup and keep running, that can be used with desktop deployment, may be something to consider to make having PostgreSQL a more viable option for back-end integration. IMO, currently PostgreSQL is excellent for vertical deployment as opposed to horizontal deployment. Having a PostgreSQL Lite configuration that is designed for horizontal deployment is something to consider. Personally I do not care but the situation this thread implies stems from what appears to be a mis-match between what the vendor needs and what PostgreSQL provides. David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Adrian Klaver Sent: Tuesday, March 15, 2011 12:44 PM To: pgsql-general@postgresql.org Cc: Merlin Moncure; Bruce Momjian; Alphadion Subject: Re: [GENERAL] PostgreSQL for Holdem Manager could not be installed. On Tuesday, March 15, 2011 9:31:17 am Merlin Moncure wrote: On Tue, Mar 15, 2011 at 11:04 AM, Bruce Momjian br...@momjian.us wrote: The larger question is whether these forums should be used to help people get support for commercial products that embed Postgres. I have no particular philosophical objection to helping people using Holdem Manager -- I'd venture most people posting here asking for help are working on commercial software, and I'd prefer people to come away from this forum with a positive impression. It's not like we are getting 100 emails a day on this stuff. This is a participatory environment -- so what's the fuss? Not only that, but should a particular product show up consistently it becomes an opportunity, for those on this list that do offer commercial support, to make a 'warm' call. That said, to the OP, on this issue you might have better luck on irc channel with this type of problem. merlin -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Partitioned Database and Choosing Subtables
-Original Message- From: Bill Thoen [mailto:bth...@gisnet.com] Sent: Monday, March 14, 2011 11:31 PM To: pgsql-general@postgresql.org Subject: Partitioned Database and Choosing Subtables I've got a ver 8.4.5 partitioned data base with records organized by US state, so the partitions are set up by state. When I query this database and include the key field that tells postgres what partition you , everything works as I expect. It searches only the specified partition, and it's fast . But that's only if I use a constant, like this: SELECT lions, tigers, bears FROM WildLife WHERE state_pt = 'CO'; What I want to be able to do is put this key value in a table and PG look in whatever partition the column specifies, like so: SELECT lions, tigers, bears, statecode FROM WildLife WHERE state_pt = statecode; However when I try anything other than a constant, it search EVERY partition, sequentially, which is not what I want it to do. So is there any way to specify the partition to search using a variable/column name? -- *Bill Thoen* GISnet - www.gisnet.com 303-786-9961 Try using dynamic sql: EXECUTE 'SELECT lions, tigers, bears, statecode FROM WildLife WHERE state_pt = ' || statecode INTO ...; See: http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html Regards, Igor Neyman -- 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 to add hosts to pg_hba.conf and postgresql.conf?
On 03/15/2011 10:37 AM, general_lee wrote: To confirm, 10.3.55.182 is the Postgres Server $ telnet 10.3.55.182 5432 Trying ... telnet: Unable to connect to remote host: Connection refused $ telnet localhost 5432 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. $ psql -h 10.3.55.182 -U post_owner -d post_db psql: could not connect to server: Connection refused Is the server running on host and accepting TCP/IP connections on port 5432? My pg_hba.conf looks like this; # TYPE DATABASEUSERCIDR-ADDRESS METHOD # local is for Unix domain socket connections only local all all trust # IPv4 local connections: hostall all 127.0.0.1/32 trust hostall all 10.3.55.0/24 255.255.255.255 trust Not sure that it makes a difference, but you only need to do one of either: 10.3.55.0/24 10.3.55.0 255.255.255.0 # IPv6 local connections: hostall all ::1/128 trust My postgresql.conf looks like this; # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all #port = 5432 max_connections = 100 -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3711674.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@gmail.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] How to add hosts to pg_hba.conf and postgresql.conf?
On 03/15/11 10:24 AM, general_lee wrote: Hi, I've checked IP Filter is disabled... Also this is Solaris 10 - netstat -lnp is not recognised. Here is netstat -np without my macs :) netstat -an shows listening ports. -- 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] PostgreSQL for Holdem Manager could not be installed.
Doesn't matter I'l try reinstallin the Windows anyway -- View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-for-Holdem-Manager-could-not-be-installed-tp3700954p3712964.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Different encoding for string values and identifier strings? Or (select 'tést' as tést) returns different values for string and identifier...
Hi all! I received a bug report about problems with identifiers. If I do the following select: select 1 as tést The column name returned contains strange chars. If I do: select 'tést' as tést the select value returned is ok but the column name is not. :( Is there any configuration I need to change to specify the encoding of the identifiers? I'm using UTF-8 for my database. Thanks in advance. -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- 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 to add hosts to pg_hba.conf and postgresql.conf?
Here you go. Explains why locahost is OK, but how do I get PM to listen on *.5432 Thanks for your help # netstat -a | grep LISTEN localhost.5999 *.*0 0 49152 0 LISTEN *.ssh*.*0 0 49152 0 LISTEN *.telnet *.*0 0 49152 0 LISTEN localhost.smtp *.*0 0 49152 0 LISTEN localhost.submission *.*0 0 49152 0 LISTEN *.sunrpc *.*0 0 49152 0 LISTEN *.32796 *.*0 0 49152 0 LISTEN *.lockd *.*0 0 49152 0 LISTEN perjt02-55.5666 *.*0 0 49152 0 LISTEN localhost.5432 *.*0 0 49152 0 LISTEN *.ssh *.* 0 0 49152 0 LISTEN *.telnet *.* 0 0 49152 0 LISTEN -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3712646.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to add hosts to pg_hba.conf and postgresql.conf?
To confirm, the below would indicate PM is not listening on *.5432 How do I change this? # netstat -a | grep LISTEN localhost.5999 *.*0 0 49152 0 LISTEN *.ssh*.*0 0 49152 0 LISTEN *.telnet *.*0 0 49152 0 LISTEN localhost.smtp *.*0 0 49152 0 LISTEN localhost.submission *.*0 0 49152 0 LISTEN *.sunrpc *.*0 0 49152 0 LISTEN *.32796 *.*0 0 49152 0 LISTEN *.lockd *.*0 0 49152 0 LISTEN perjt02-55.5666 *.*0 0 49152 0 LISTEN localhost.5432 *.*0 0 49152 0 LISTEN *.ssh *.* 0 0 49152 0 LISTEN *.telnet *.* 0 0 49152 0 LISTEN -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3713212.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Different encoding for string values and identifier strings? Or (select 'tést' as tést) returns different values for string and identifier...
Francisco Figueiredo Jr. franci...@npgsql.org wrote: Hi all! I received a bug report about problems with identifiers. If I do the following select: select 1 as tést The column name returned contains strange chars. If I do: select 'tést' as tést the select value returned is ok but the column name is not. :( Is there any configuration I need to change to specify the encoding of the identifiers? test=# select 'tést' as tést; tést -- tést looks okay for me ... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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 to add hosts to pg_hba.conf and postgresql.conf?
On 15/03/2011 18:14, general_lee wrote: To confirm, the below would indicate PM is not listening on *.5432 How do I change this? Did you restart Postgres after changing listen_addresses in postgresql.conf? 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] How to add hosts to pg_hba.conf and postgresql.conf?
Explains why locahost is OK, but how do I get PM to listen on *.5432 Well, you believe you already have, by telling PostgreSQL to listen on '*'. There might be a few different reason why it's not: 1) Are you editing the correct postgresql.conf file? Do you have multiple ones on the filesystem somewhere? 2) Are you editing the correct directive? It's 'listen_addresses' on both my 8.x and 9.x servers. It looks like the right option in your original posting, have you re-typed the option name just to be sure a control or unprintable character wasn't accidentally inserted? Does PostgreSQL log anything when you restart it that would suggest which file it's looking at, or if it's complaining about the configuration it's reading? Benny -- Hairy ape nads.-- Colleen, playing Neverwinter Nights -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] Different encoding for string values and identifier strings? Or (select 'tést' as tést) returns different values for string and identifier...
This is the query our user sent as one of the problems: select zurück as zurück3_103_ from Tabelle tabellenhib0_ And here is the link to our bug report: http://pgfoundry.org/tracker/index.php?func=detailaid=1010988group_id=1000140atid=590 I didn't test putting double quotes in the column name identifier as the bug report isn't using it. What happens if you remove the double quotes in the column name identifier? Thanks in advance. On Tue, Mar 15, 2011 at 15:21, Andreas Kretschmer akretsch...@spamfence.net wrote: Francisco Figueiredo Jr. franci...@npgsql.org wrote: Hi all! I received a bug report about problems with identifiers. If I do the following select: select 1 as tést The column name returned contains strange chars. If I do: select 'tést' as tést the select value returned is ok but the column name is not. :( Is there any configuration I need to change to specify the encoding of the identifiers? test=# select 'tést' as tést; tést -- tést looks okay for me ... Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- 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 to add hosts to pg_hba.conf and postgresql.conf?
On 03/15/2011 11:14 AM, general_lee wrote: To confirm, the below would indicate PM is not listening on *.5432 How do I change this? Are you sure you are looking at the correct configuration files? Is it possible there is more than one set on the machine and the cluster you are restarting is using a different one from the one you are working on? -- Adrian Klaver adrian.kla...@gmail.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] How to add hosts to pg_hba.conf and postgresql.conf?
On 03/15/11 11:26 AM, Raymond O'Donnell wrote: On 15/03/2011 18:14, general_lee wrote: To confirm, the below would indicate PM is not listening on *.5432 How do I change this? Did you restart Postgres after changing listen_addresses in postgresql.conf? he indicated he did, via 'svcadm restart' (the Solaris 10 service manager) I wonder if he's editing the *right* postgresql.conf, there's the possibility on Solaris of having more than one instance installed. -- 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] Re: [GENERAL] Different encoding for string values and identifier strings? Or (select 'tést' as tést) returns different values for string and identifier...
Francisco Figueiredo Jr. franci...@npgsql.org wrote: What happens if you remove the double quotes in the column name identifier? the same: test=*# select 'tést' as tést; tést -- tést (1 Zeile) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] autovacuum issue after upgrade to 9.0.1
We recently upgraded from 8.3.something to 9.0.1. With 9.0.1, we have a huge spike in vacuums every 8 days only on one of our DB servers. We go from approx 20 vacuums every 5 minutes to 350 per 5 minutes. This lasts for several hours, then stops. I have attached a graph that shows the occurrence. I am assuming that it needs to vacuum all of my tables to avoid some sort of wrap around counter. I am wondering what is the best way to make autovacuum spread this out so it will not be quite a big of a hit. I we did not see this with 8.3 and I kept the setting the same after the upgrade. Here are my autovacuum settings: autovacuum = on log_autovacuum_min_duration = 0 autovacuum_max_workers = 5 autovacuum_naptime = 1min autovacuum_vacuum_threshold = 100 autovacuum_analyze_threshold = 100 autovacuum_vacuum_scale_factor = 0.1 autovacuum_analyze_scale_factor = 0.05 autovacuum_freeze_max_age = 2 autovacuum_vacuum_cost_delay = 20ms autovacuum_vacuum_cost_limit = -1 Any suggestions would be appreciated. Thanks, George Woodring -- iGLASS Networks www.iglass.net attachment: staticGraph.do.png -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] Re: [GENERAL] Different encoding for string values and identifier strings? Or (select 'tést' as tést) returns different values for string and identifier...
H, What would change the encoding of the identifiers? Because on my dev machine which unfortunately isn't with me right now I can't get the identifier returned correctly :( I remember that it returns: test=*# select 'tést' as tést; tst -- tést Is there any config I can change at runtime in order to have it returned correctly? Thanks in advance. On Tue, Mar 15, 2011 at 15:45, Andreas Kretschmer akretsch...@spamfence.net wrote: Francisco Figueiredo Jr. franci...@npgsql.org wrote: What happens if you remove the double quotes in the column name identifier? the same: test=*# select 'tést' as tést; tést -- tést (1 Zeile) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- 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 to add hosts to pg_hba.conf and postgresql.conf?
Guys, Thanks for your help. When I saw that netstat was not listening as *.5432 I decided to; svcadm disable svc:/application/database/postgresql_83:default_64bit Then; pg_ctl -D /var/lib/pgsql/data -l postmaster.log start This fixed the problem. You guys were right on the money, looks like restarting via smf was not picking up the config changes. So I guess they need to be made elsewhere in Solaris. I can now talk to my PG DB via a remote host. Big thanks the help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3719853.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to add hosts to pg_hba.conf and postgresql.conf?
On Tuesday, March 15, 2011 1:33:48 pm general_lee wrote: Guys, Thanks for your help. When I saw that netstat was not listening as *.5432 I decided to; svcadm disable svc:/application/database/postgresql_83:default_64bit Then; pg_ctl -D /var/lib/pgsql/data -l postmaster.log start This fixed the problem. You guys were right on the money, looks like restarting via smf was not picking up the config changes. So I guess they need to be made elsewhere in Solaris. What it probably means is that this: svc:/application/database/postgresql_83:default_64bit is pointing at a different instance of Postgres than this: pg_ctl -D /var/lib/pgsql/data -l postmaster.log start My guess is if you search for postgresql.conf and pg_hba.conf you will find more than one copy of each and that the copies that are not in /var/lib/pgsql/data are the ones being used for the svc start. I can now talk to my PG DB via a remote host. Big thanks the help. -- Adrian Klaver adrian.kla...@gmail.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] How to add hosts to pg_hba.conf and postgresql.conf?
What it probably means is that this: svc:/application/database/postgresql_83:default_64bit is pointing at a different instance of Postgres than this: pg_ctl -D /var/lib/pgsql/data -l postmaster.log start indeed. # svcprop svc:/application/database/postgresql_83:default_64bit | grep data /var/postgres/8.3/data_64 so, yeah, wrong place entirely. god knows what mess you have now. # svcprop svc:/application/database/postgresql:version_81 | grep data postgresql/data astring /var/lib/pgsql/data hm. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Enable/Disable Triggers
Hi All, Is it a big no-no to enable/disable triggers by manually setting pg_trigger.tgenabled to 'D'? -- Andy Chambers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A join of 2 tables with sum(column) 30
Hello, I have a table holding number of games per week for each user: # select id,completed,yw from pref_match limit 3; id | completed | yw +---+- OK2650139676 | 10 | 2011-03 OK513367704098 | 20 | 2011-03 OK513367704098 | 30 | 2011-04 (3 rows) and then another table with user names: # select id, first_name from pref_users limit 3; id | first_name + OK272457241702 | Alex OK123280785043 | Felix OK513367704098 | Alissa (3 rows) I'm trying to print the first_name's of players, who played more than 30 complete games (in total): # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and m.completed 30 group by u.id, u.first_name order by sum desc limit 3; id | first_name | sum ++- DE9143 | BATISTA| 619 DE8890 | CBETA | 485 DE9163 | andrej75 | 458 (3 rows) This seems to work, but the condition above is m.completed 30 i.e. it wants 30 games or more completed per week? I'm trying to change it to a sum, but get the error: # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum 30 group by u.id, u.first_name order by sum desc limit 3; ERROR: column sum does not exist LINE 4: ...f_match m where u.id=m.id and u.id like 'DE%' and sum 30 g... Any suggestions please? I've tried ... sum(m.completed) as total too... Regards Alex -- 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] Enable/Disable Triggers
On Tue, Mar 15, 2011 at 05:01:50PM -0400, Andy Chambers wrote: Is it a big no-no to enable/disable triggers by manually setting pg_trigger.tgenabled to 'D'? No, but I seem to recall having seen corner cases where it didn't work. (This was the reason that in older versions of slony, slony fooled with the system catalogues instead.) You can use the replication_role control to prevent triggers firing. A -- Andrew Sullivan a...@crankycanuck.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] Enable/Disable Triggers
On Mar 16, 2011, at 2:31 AM, Andy Chambers wrote: Is it a big no-no to enable/disable triggers by manually setting pg_trigger.tgenabled to 'D'? Not recommended to do anything with catalog tables. At the place of this use following command: ALTER TABLE tablename DISABLE TRIGGER [ trigger_name | ALL | USER ] Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.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] Copying data from one table to another - how to specify fields?
Thank you all for the replies! On Wed, Mar 9, 2011 at 7:01 PM, David Johnston pol...@yahoo.com wrote: SELECT username, count(username) FROM phpbb_users GROUP BY username HAVING count(username) 1; If anything shows up then (phpbb_users .username) is not a unique field but you are trying to insert it into one that is (drupal_users.uid) -- 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] A join of 2 tables with sum(column) 30
On Mar 16, 2011, at 3:03 AM, Alexander Farber wrote: # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum 30 group by u.id, u.first_name order by sum desc limit 3; ERROR: column sum does not exist LINE 4: ...f_match m where u.id=m.id and u.id like 'DE%' and sum 30 g... Any suggestions please? I've tried ... sum(m.completed) as total too.. Try following: select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum(m.completed 30 group by u.id, u.first_name order by sum desc limit 3; Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.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] A join of 2 tables with sum(column) 30
Unfortunately I get: # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum(m.completed) 30 group by u.id, u.first_name order by sum desc limit 3; ERROR: aggregates not allowed in WHERE clause LINE 1: ...f_match m where u.id=m.id and u.id like 'DE%' and sum(m.comp... On Tue, Mar 15, 2011 at 10:43 PM, Vibhor Kumar vibhor.ku...@enterprisedb.com wrote: On Mar 16, 2011, at 3:03 AM, Alexander Farber wrote: # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum 30 group by u.id, u.first_name order by sum desc limit 3; ERROR: column sum does not exist LINE 4: ...f_match m where u.id=m.id and u.id like 'DE%' and sum 30 g... Any suggestions please? I've tried ... sum(m.completed) as total too.. Try following: select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum(m.completed 30 group by u.id, u.first_name order by sum desc limit 3; -- 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] A join of 2 tables with sum(column) 30
And same for a simple select-query from1 table (w/o join): # select id from pref_match where sum(completed) 30 group by id; ERROR: aggregates not allowed in WHERE clause LINE 1: select id from pref_match where sum(completed) 30 group by... ^ -- 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] A join of 2 tables with sum(column) 30
On Mar 16, 2011, at 3:12 AM, Alexander Farber wrote: Unfortunately I get: # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum(m.completed) 30 group by u.id, u.first_name order by sum desc limit 3; ERROR: aggregates not allowed in WHERE clause LINE 1: ...f_match m where u.id=m.id and u.id like 'DE%' and sum(m.comp... My Bad... Missed you have to use having clause as given below: select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' group by u.id, u.first_name having sum(m.completed 30 order by sum desc limit 3; On Tue, Mar 15, 2011 at 10:43 PM, Vibhor Kumar vibhor.ku...@enterprisedb.com wrote: On Mar 16, 2011, at 3:03 AM, Alexander Farber wrote: # select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum 30 group by u.id, u.first_name order by sum desc limit 3; ERROR: column sum does not exist LINE 4: ...f_match m where u.id=m.id and u.id like 'DE%' and sum 30 g... Any suggestions please? I've tried ... sum(m.completed) as total too.. Try following: select u.id, u.first_name, sum(m.completed) from pref_users u, pref_match m where u.id=m.id and u.id like 'DE%' and sum(m.completed 30 group by u.id, u.first_name order by sum desc limit 3; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.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] A join of 2 tables with sum(column) 30
On Mar 16, 2011, at 3:15 AM, Alexander Farber wrote: And same for a simple select-query from1 table (w/o join): # select id from pref_match where sum(completed) 30 group by id; ERROR: aggregates not allowed in WHERE clause LINE 1: select id from pref_match where sum(completed) 30 group by... ^ Use having clause for aggrgate function, group by col1, col2 having sum(col) Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company vibhor.ku...@enterprisedb.com Blog:http://vibhork.blogspot.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] Partitioned Database and Choosing Subtables
On 3/15/2011 12:02 PM, Igor Neyman wrote:
-Original Message-
From: Bill Thoen [mailto:bth...@gisnet.com]
Sent: Monday, March 14, 2011 11:31 PM
To: pgsql-general@postgresql.org
Subject: Partitioned Database and Choosing Subtables
I've got a ver 8.4.5 partitioned data base with records
organized by US state, so the partitions are set up by state.
When I query this database and include the key field that
tells postgres what partition you , everything works as I
expect. It searches only the specified partition, and it's
fast . But that's only if I use a constant, like this:
SELECT lions, tigers, bears FROM WildLife WHERE state_pt = 'CO';
What I want to be able to do is put this key value in a table
and PG look in whatever partition the column specifies, like so:
SELECT lions, tigers, bears, statecode FROM WildLife WHERE
state_pt = statecode;
However when I try anything other than a constant, it search
EVERY partition, sequentially, which is not what I want it to
do. So is there any way to specify the partition to search
using a variable/column name?
--
*Bill Thoen*
GISnet - www.gisnet.com
303-786-9961
Try using dynamic sql:
EXECUTE 'SELECT lions, tigers, bears, statecode FROM WildLife WHERE
state_pt = ' || statecode INTO ...;
See:
http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html
Thanks Igor. It was a nice try -- and I thought it would work, but the
Planner had other plans. Basically, I tried the interactive method using
a PREPARE statement with one text parammeter followed by an EXECUTE
statement. Unfortunately, the query still went rummaging across the
entire database sequentially.
To recap, (I still hope there's a chance for a workable answer) here's a
simple contrived scenario
Incidents Table
---
total integer, -- total number of human:critter encounters
statecode char(2), -- by state
outcome char(20) -- outcome of incident {fatal, serious, minor,
close call}
Wildlife Table -- the Partitioned table. Uses state_pt to
separate data tables.
table 1: state_pt='AK'
table 2: state_pt='AL'
...
table 50: state_pt='WY'
-
lions integer, -- total population of lions in the state
tigers integer, -- total tigers
bears integer, -- total bears
outcome char(20),-- outcome of incident {fatal, serious, minor,
close call}
... more attributes...
state_pt -- key index that separates the sub tables
inthe partitioned datbase
Some incidents table data:
total | statecode | outcome
--+--+-
7 | CA | fatal
12 | CA | close call
3 | CO | fatal
16 | CO | close call
4 | WY | minor
If I do the following it finds the data and goes directly to the right
table.
SELECT lions, tigers, bears FROM Wildlife
WHERE state_pt = 'CO';
Or this:
\set theState '\''CO'\''
SELECT lions, tigers, bears FROM Wildlife
WHERE state_pt = :theState ;
It works perfectly. But when I tried this:
PREPARE foo (text) AS
SELECT lions, tigers, bears FROM Wildlife
WHERE state_pt = $1;
EXECUTE foo( 'CO' )
It sequentially scanned everything, so I didn't even try the scenario
I'm trying to create, which is using the results of a select to supply
the key code to the Planner as to what partition it should search for
reach record.
SELECT lions, tigers , bears FROM Wildlife a JOIN incidents b ON
a.outcome=b.outcome
WHERE state_pt=statecode;
Apparently, the Planner is pretty fussy about how you refer to one of
your subtables, but I almost always use queries that involve multiple
states,and the knowledge of what state table a particular record needs
to access is carried as one of that record's attributes. I'm surprised
that there's so little info on this. I tseems like an obvious
application of partitioned databases, but maybe I just don't understand
how it should be used?
It seems that the only variables it likes are host variables, and there
maybe something down that road...
Anywya the quuestion is still open if anyone has some good ideas.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] Re: [GENERAL] Different encoding for string values and identifier strings? Or (select 'tést' as tést) returns different values for string and identifier...
Now, I'm using my dev machine. With the tests I'm doing, I can see the following: If I use: select 'seléct' as seléct; column name returns ok as expected. If I do: select 'seléct' as seléct; This is the sequence of bytes I receive from postgresql: byte1 - 115 UTF-8 for s byte2 - 101 UTF-8 for e byte3 - 108 UTF-8 for l byte4 - 227 byte5 - 169 byte6 - 99 UTF-8 for c byte7 - 116 UTF-8 for t The problem lies in the byte4. According to [1], the first byte defines how many bytes will compose the UTF-8 char. the problem is that 227 encodes to a binary value of 1110 0011 and so, the UTF-8 decoder will think there are 3 bytes in sequence when actually there are only 2! :( And this seems to be the root of the problem for me. For the select value the correct byte is returned: byte1 - 115 UTF-8 for s byte2 - 101 UTF-8 for e byte3 - 108 UTF-8 for l byte4 - 195 byte5 - 169 byte6 - 99 UTF-8 for c byte7 - 116 UTF-8 for t Where 195 is 1100 0011 which gives two bytes in sequence and the decoder can decode this to the U+00E9 which is the char é Do you think this can be related to my machine? I'm using OSX 10.6.6 and I compiled postgresql 9.0.1 from source code. Thanks in advance. [1] - http://en.wikipedia.org/wiki/UTF-8 On Tue, Mar 15, 2011 at 15:52, Francisco Figueiredo Jr. franci...@npgsql.org wrote: H, What would change the encoding of the identifiers? Because on my dev machine which unfortunately isn't with me right now I can't get the identifier returned correctly :( I remember that it returns: test=*# select 'tést' as tést; tst -- tést Is there any config I can change at runtime in order to have it returned correctly? Thanks in advance. On Tue, Mar 15, 2011 at 15:45, Andreas Kretschmer akretsch...@spamfence.net wrote: Francisco Figueiredo Jr. franci...@npgsql.org wrote: What happens if you remove the double quotes in the column name identifier? the same: test=*# select 'tést' as tést; tést -- tést (1 Zeile) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- Regards, Francisco Figueiredo Jr. Npgsql Lead Developer http://www.npgsql.org http://fxjr.blogspot.com http://twitter.com/franciscojunior -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to use savepoint and rollback in function
Hi All, In Postgresql we can use savepoint and rollback to savepoint in transaction. But we cannot use savepoint in function. Currently I am doing migration from Informix to Postgresql 8.4. In Informix they used Savepoint. Please advice how to use Savepoint and rollback to savepoint in function. I know Postgresql has autocommit. But I want to rollback to a particular savepoint. Thanks Regards, Tushar
