Re: [GENERAL] php4 and postgresql 8.3
On Mon, Mar 2, 2009 at 8:24 PM, shadrack shadke...@hotmail.com wrote: On Mar 2, 5:35 pm, pie...@hogranch.com (John R Pierce) wrote: Tom Lane wrote: shadrack shadke...@hotmail.com writes: My basic question is...are php4 and postgresql 8.3 compatible? I'm running Linux Redhat 3.4.6, php4.3.9, and postgresql 8.3. I know, some of those versions are old...its government, and I unfortunately don't have control over the version. Er ... Red Hat *what*? I don't think they ever used such a version number. If they did it was a very long time ago (for calibration, they were just about to release RHL 7.3 when I joined the company, in 2001). well, remember, they went Red Hat Linux 7.x, 8.x, 9 then very quickly switched to Red Hat Enterprise Linux 2, 2.1, 3, 4, and currently RHEL 5. RHEL 3 has had several quarterly updates, most recent of which is u9(I think), sometimes referred to as 3.9. if its RHEL 3 update-something that shadrack is discussing, it came with php 4.3.2 and rh-postgresql 7.3.21 (shudder!) -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Yes, we had postgresql 7.3 but I installed postgresql 8.3 onto the rhel3. Do you think its going to work? What's the solution that involves the least work, considering I'm not the one maintaining these machines? Do you think it would be simple for the IT person to update to rhel5 and php5? Its basically just one machine that he would have to update. Thoughts? Thanks so much for all the feedback. shad Going straight to RHEL 5 would be a way smarter move. it's stable, it's supported, and it has php5 and pgsql 8.something as a default (8.2? Somewhere in there). Plus,. if you want 8.3 you just don't install 8.2 and instead grab the PGDG rpms from the postgresql ftp site. I could maybe understand a corporate policy of supporting RHEL4, but RHEL3 is ancient. -- 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 amazon ec2 cloud
John, Is it possible to host postgreSQL on Amazon's cloud? What are the issues involved? in theory, sure. anything is possible. in practice, as I understand it from my relatively superficial reading, fast storage is fairly expensive and limited in the EC2 compute cloud, and also not real persistent That also was my understanding. But just today a message from AWS dropped in my inbox: Starting today, you can now launch Amazon EC2 running Windows or SQL Server instances in the the EU Region, So there must be some way to run a relational database with EC2, as the storage requirements of SQL Server and PostgreSQL are not THAT different. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] grant everything on everything and then revoke
I'd like to have different users mainly to have a different search schema path. Things may evolve so this is not going to be the only reason to have more than one user. But I'm faced with the problem of granting the same access of the owner of the db to the other users. But I read: http://www.postgresql.org/docs/8.3/static/sql-grant.html The SQL standard does not support setting the privileges on more than one object per command. This is going to make maintenance and development a PITA every time I add a new table, sequence, schema... Defining a role/group with all grant access and then assigning that group to all users is going to make this a bit less painful, but still every time I'm going to add something to the DB I'll have to remember to modify the group privileges. Even when things will evolve, all users should be able to do everything to most object with a few exception so it is easier to revoke than to grant. Any advice even with completely different approach? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL clustering with DRBD
Again, this is a lot of work to avoid master / slave with failover. Are you sure it's really needed for your situation? What is the most straightforward and simple way to achieve master slave with failover? Preferably a solution that would have decent monitoring, alerting and failback capacity. It would be also nice if you could use the standby as a read only database for reporting or something.
Re: [GENERAL] postgreSQL amazon ec2 cloud
I found today that postgres EnterpriseDB supports Amazon EC2. On a shoestring budget EnterpriseDB is just as much an option as Oracle ;-( So, question is what makes EnterpriseDB more suitable for the cloud than plain vanilla postgreSQL? Anyone? With best regards. Sanjay. On Tue, Mar 3, 2009 at 3:49 PM, Harald Armin Massa[legacy] haraldarminma...@gmail.com wrote: John, Is it possible to host postgreSQL on Amazon's cloud? What are the issues involved? in theory, sure. anything is possible. in practice, as I understand it from my relatively superficial reading, fast storage is fairly expensive and limited in the EC2 compute cloud, and also not real persistent That also was my understanding. But just today a message from AWS dropped in my inbox: Starting today, you can now launch Amazon EC2 running Windows or SQL Server instances in the the EU Region, So there must be some way to run a relational database with EC2, as the storage requirements of SQL Server and PostgreSQL are not THAT different. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad?
Re: [GENERAL] postgreSQL amazon ec2 cloud
On Tuesday 03 March 2009 4:41:48 am Sanjay Arora wrote: I found today that postgres EnterpriseDB supports Amazon EC2. On a shoestring budget EnterpriseDB is just as much an option as Oracle ;-( So, question is what makes EnterpriseDB more suitable for the cloud than plain vanilla postgreSQL? Anyone? With best regards. Sanjay. Nothing. I have created a Postgres instance on an EC2 virtual machine with attached EBS(Elastic Block Storage). I only got as far as creating in it and verifying it would run, no benchmarking. EC2 instances have storage as part of the instance but it is temporary and goes away when the instance is shut down. For a database you want EBS as it is a virtual harddrive that persists. Should an EC2 instance go down, you just reattach the EBS drive on reboot. If I remember correctly there are also some articles at aws.amazon.com about setting up RAID using EBS drives. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgreSQL amazon ec2 cloud
I don't know exactly, but here here is a paper from Elastra http://elastra.com/about/2008/03/07/enterprisedb-to-deliver-oltp-database-using-amazon-cloud/ Oleg On Tue, 3 Mar 2009, Sanjay Arora wrote: I found today that postgres EnterpriseDB supports Amazon EC2. On a shoestring budget EnterpriseDB is just as much an option as Oracle ;-( So, question is what makes EnterpriseDB more suitable for the cloud than plain vanilla postgreSQL? Anyone? With best regards. Sanjay. On Tue, Mar 3, 2009 at 3:49 PM, Harald Armin Massa[legacy] haraldarminma...@gmail.com wrote: John, Is it possible to host postgreSQL on Amazon's cloud? What are the issues involved? in theory, sure. anything is possible. in practice, as I understand it from my relatively superficial reading, fast storage is fairly expensive and limited in the EC2 compute cloud, and also not real persistent That also was my understanding. But just today a message from AWS dropped in my inbox: Starting today, you can now launch Amazon EC2 running Windows or SQL Server instances in the the EU Region, So there must be some way to run a relational database with EC2, as the storage requirements of SQL Server and PostgreSQL are not THAT different. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Stra?e 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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 amazon ec2 cloud
Adrian Klaver akla...@comcast.net writes: Nothing. I have created a Postgres instance on an EC2 virtual machine with attached EBS(Elastic Block Storage). I only got as far as creating in it and verifying it would run, no benchmarking. EC2 instances have storage as part of the instance but it is temporary and goes away when the instance is shut down. For a database you want EBS as it is a virtual harddrive that persists. Should an EC2 instance go down, you just reattach the EBS drive on reboot. ... I wonder whether you have any guarantees about database consistency in that situation? PG has some pretty strong requirements about fsync behavior etc, and I'd not want to take it on faith that a cloud environment will meet those requirements. Performance would be an interesting question too. 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 use pg_dump to dump tables whose owner is me
Thanks. If there are many tables owned by me and some other users, are there any easy ways to do that? On Mon, Mar 2, 2009 at 9:23 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Mar 2, 2009 at 5:31 PM, Roger Chen roger.edc...@gmail.com wrote: Hi, Can anyone tell me how to do that? I could find that in man page of pg_dump. Thanks. pg_dump -t table1 -t table2 ? -- 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] date - null casted to integer?
Ups, sorry, I'm idiot... changes from the default casting to text is
really helpful in clearing brain bugs...
Роман Маширов wrote:
Hi!
Excuse me, if this been discussed before, but following thing seems to
me a little bit strange:
select '2009-01-12'::date - null::date '1 day'::interval;
ERROR: operator does not exist: integer interval
LINE 1: select '2009-11-12'::date - null::date '1 day'::interval;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
select ('2009-11-12'::date - null::date)::interval '1 day'::interval;
ERROR: cannot cast type integer to interval
LINE 1: select ('2009-11-12'::date - null::date)::interval '1 day'...
server 8.3.5 It's really not a problem in queries, but could became a
real pain with plpgsql. I'm not sure, but it seems to me that any
operation with null should product null from the standart's point of
view?
Thank you beforehand
--
MRJ
--
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] grant everything on everything and then revoke
On Mar 3, 2009, at 4:35 AM, Ivan Sergio Borgonovo wrote: But I read: http://www.postgresql.org/docs/8.3/static/sql-grant.html The SQL standard does not support setting the privileges on more than one object per command. This is going to make maintenance and development a PITA every time I add a new table, sequence, schema... There is some pl/pgsql code here grant on more than one object at a time: http://pgedit.com/tip/postgresql/access_control_functions John DeSoi, Ph.D. -- 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 use pg_dump to dump tables whose owner is me
--- On Tue, 3/3/09, Roger Chen roger.edc...@gmail.com wrote: Hi, Can anyone tell me how to do that? I could find that in man page of pg_dump. Thanks. pg_dump -t table1 -t table2 Thanks. If there are many tables owned by me and some other users, are there any easy ways to do that? I would use psql to query all the tables you own to a file, using a query like this: select schemaname || '.' || tablename as to_dump from pg_tables where tableowner = 'myuser' And then use xargs to or similar text tools to generate the pg_dump command you need. -- 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 amazon ec2 cloud
On Tuesday 03 March 2009 7:25:17 am Tom Lane wrote: Adrian Klaver akla...@comcast.net writes: Nothing. I have created a Postgres instance on an EC2 virtual machine with attached EBS(Elastic Block Storage). I only got as far as creating in it and verifying it would run, no benchmarking. EC2 instances have storage as part of the instance but it is temporary and goes away when the instance is shut down. For a database you want EBS as it is a virtual harddrive that persists. Should an EC2 instance go down, you just reattach the EBS drive on reboot. ... I wonder whether you have any guarantees about database consistency in that situation? PG has some pretty strong requirements about fsync behavior etc, and I'd not want to take it on faith that a cloud environment will meet those requirements. Performance would be an interesting question too. regards, tom lane The EBS starts out as a raw drive. You format it with the file system of your choice and it gets mounted as a regular drive. From the point of view of the OS it is a physical hard drive. As to the cloud environment meeting the requirements I can only go with IBM on this one- http://aws.amazon.com/solutions/featured-partners/ibm/ To quote: In the coming months, AWS will provide pay-as-you-go pricing for the Amazon EC2 running IBM service, enabling you to purchase these services by the hour. These AMIs will enable you to utilize Amazon EC2 with many of the IBM platform technologies you’re already familiar with in the cost-effective, high-performance, reliable, and secure Amazon EC2 environment. The initial list of IBM AMIs that Amazon EC2 will run include: IBM DB2, IBM Informix, IBM WebSphere sMash, IBM Lotus Web Content Management, and IBM WebSphere Portal Server. Performance remains to be determined. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange behavior: row won't delete
HI. I made a small alteration to a table (added a column). Now when I do: vacuum analyze TABLENAME or delete from TABLENAME where id = 99 Nothing happens! The carriage return means the my shell cursor goes to the next line, but it just stays there. I thought something may be happening silently but it has been sitting this way since an hour. How can I debug this? Nothing in the logs at all. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] date - null casted to integer?
Hi!
Excuse me, if this been discussed before, but following thing seems to
me a little bit strange:
select '2009-01-12'::date - null::date '1 day'::interval;
ERROR: operator does not exist: integer interval
LINE 1: select '2009-11-12'::date - null::date '1 day'::interval;
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
select ('2009-11-12'::date - null::date)::interval '1 day'::interval;
ERROR: cannot cast type integer to interval
LINE 1: select ('2009-11-12'::date - null::date)::interval '1 day'...
server 8.3.5 It's really not a problem in queries, but could became a
real pain with plpgsql. I'm not sure, but it seems to me that any
operation with null should product null from the standart's point of view?
Thank you beforehand
--
MRJ
--
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] Strange behavior: row won't delete
Phoenix Kiula phoenix.ki...@gmail.com writes: Now when I do: vacuum analyze TABLENAME or delete from TABLENAME where id = 99 Nothing happens! The carriage return means the my shell cursor goes to the next line, but it just stays there. Did you forget the semicolon? 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] Strange behavior: row won't delete
On Tuesday 03 March 2009, Phoenix Kiula phoenix.ki...@gmail.com wrote: HI. I made a small alteration to a table (added a column). Now when I do: vacuum analyze TABLENAME or delete from TABLENAME where id = 99 Nothing happens! The carriage return means the my shell cursor goes to the next line, but it just stays there. I thought something may be happening silently but it has been sitting this way since an hour. How can I debug this? Nothing in the logs at all. commit the transaction where you altered the table. It has an open lock on the table. -- Even a sixth-grader can figure out that you can’t borrow money to pay off your debt -- 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] Strange behavior: row won't delete
On Wed, Mar 4, 2009 at 12:10 AM, Tom Lane t...@sss.pgh.pa.us wrote: Phoenix Kiula phoenix.ki...@gmail.com writes: Now when I do: vacuum analyze TABLENAME or delete from TABLENAME where id = 99 Nothing happens! The carriage return means the my shell cursor goes to the next line, but it just stays there. Did you forget the semicolon? Cute. But no :) myuser=# delete from visitcount where id = 99; -- 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] Strange behavior: row won't delete
commit the transaction where you altered the table. It has an open lock on the table. =# commit; WARNING: there is no transaction in progress COMMIT Time: 0.282 ms So no, there's nothing pending. -- 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] Strange behavior: row won't delete
Although when I try this: select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation; There are many rows! How can I get rid of these open locks? -- 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] Strange behavior: row won't delete
- Phoenix Kiula phoenix.ki...@gmail.com wrote: commit the transaction where you altered the table. It has an open lock on the table. =# commit; WARNING: there is no transaction in progress COMMIT Time: 0.282 ms So no, there's nothing pending. -- Are you connected to the right database?. I have been in that situation, looking at the log for db A and doing things in db B. Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange behavior: row won't delete
On Wed, Mar 4, 2009 at 12:36 AM, Adrian Klaver akla...@comcast.net wrote: Are you connected to the right database?. I have been in that situation, looking at the log for db A and doing things in db B. Thanks. I only have one database, so yes I am connected to it. I have the lock file in /tmp:.s.PGSQL.5432.lock Should I delete this file? -- 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] Strange behavior: row won't delete
- Phoenix Kiula phoenix.ki...@gmail.com wrote: On Wed, Mar 4, 2009 at 12:36 AM, Adrian Klaver akla...@comcast.net wrote: Are you connected to the right database?. I have been in that situation, looking at the log for db A and doing things in db B. Thanks. I only have one database, so yes I am connected to it. I have the lock file in /tmp:.s.PGSQL.5432.lock Should I delete this file? No, that is the lock file for the entire cluster. Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange behavior: row won't delete
I guess my question is, how should I remove all pending locks on a table so that I can get on with the rest of the stuff? I mean, even if I can now find an offending RULE on the table, I cannot replace or remove it. ' Thanks for any pointers! -- 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] Strange behavior: row won't delete
Phoenix Kiula phoenix.ki...@gmail.com writes: How can I get rid of these open locks? Close the transactions that are holding them. Look into pg_stat_activity and pg_prepared_xacts. 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
How to delete all locks? Re: [GENERAL] Strange behavior: row won't delete
On Wed, Mar 4, 2009 at 12:55 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: I guess my question is, how should I remove all pending locks on a table so that I can get on with the rest of the stuff? I mean, even if I can now find an offending RULE on the table, I cannot replace or remove it. ' Any ideas? I think I have identified the offending RULE that was newly created on the table. It may have missed the WHERE condition. But even if CREATE OR REPLACE this rule, the command doesn't go through. So I'm stuck in a vicious loop. How can I get rid of all open locks? When I go through pg_locks there are about 1041 of them right now. I am looking for a mechanism of deleting them all (can I just delete all rows in pg_locks?) instead of manually going through 1041 on a live database. Thanks! -- 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] Strange behavior: row won't delete
On Wed, Mar 4, 2009 at 1:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: Phoenix Kiula phoenix.ki...@gmail.com writes: How can I get rid of these open locks? Close the transactions that are holding them. Look into pg_stat_activity and pg_prepared_xacts. Thanks for this. But can I simply delete all the pg_locks table? Or delete all rows in pg_stat_activity? In my case the _xacts table is empty. Ideally I don't want to lose pg_stat_activity. I just want to change a RULE on a table. Is there any place I can do that? -- 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] Strange behavior: row won't delete
On Wed, Mar 4, 2009 at 1:23 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Wed, Mar 4, 2009 at 1:17 AM, Tom Lane t...@sss.pgh.pa.us wrote: Phoenix Kiula phoenix.ki...@gmail.com writes: How can I get rid of these open locks? Close the transactions that are holding them. Look into pg_stat_activity and pg_prepared_xacts. Thanks for this. But can I simply delete all the pg_locks table? Or delete all rows in pg_stat_activity? In my case the _xacts table is empty. Ideally I don't want to lose pg_stat_activity. I just want to change a RULE on a table. Is there any place I can do that? I found 232 rows in pg_stat_activity of offending current_query. How can I delete them? =# delete from pg_stat_activity where current_query like 'UPDATE visitcount%'; ERROR: cannot delete from a view HINT: You need an unconditional ON DELETE DO INSTEAD rule Thanks! -- 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] grant everything on everything and then revoke
Ivan Sergio Borgonovo wrote: I'd like to have different users mainly to have a different search schema path. Things may evolve so this is not going to be the only reason to have more than one user. But I'm faced with the problem of granting the same access of the owner of the db to the other users. ... have the database owned by a 'ROLE and make your users members of that ROLE. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: How to delete all locks? Re: [GENERAL] Strange behavior: row won't delete
phoenix.ki...@gmail.com (Phoenix Kiula) writes: On Wed, Mar 4, 2009 at 12:55 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: I guess my question is, how should I remove all pending locks on a table so that I can get on with the rest of the stuff? I mean, even if I can now find an offending RULE on the table, I cannot replace or remove it. ' Any ideas? I think I have identified the offending RULE that was newly created on the table. It may have missed the WHERE condition. But even if CREATE OR REPLACE this rule, the command doesn't go through. So I'm stuck in a vicious loop. How can I get rid of all open locks? When I go through pg_locks there are about 1041 of them right now. I am looking for a mechanism of deleting them all (can I just delete all rows in pg_locks?) instead of manually going through 1041 on a live database. Thanks! You don't delete locks - the requestors need to relinquish them. pg_locks is NOT a table - it is a view that draws in data from a set returning function, so attempting to delete them won't work. See? mydatabase=# delete from pg_locks; ERROR: cannot delete from a view HINT: You need an unconditional ON DELETE DO INSTEAD rule. The hint isn't particularly relevant here; it's not meaningful to try to delete the locks. Locks are taken out as a result of connections doing their work. In order for them to relinquish the locks, one of two things must happen: a) The transaction (held by a connection) needs to finish, or b) You might terminate the connection to *force* termination of the transaction. Thus, what you *might* do would be to look at the processes involved with those 1041 locks, and terminate the PIDs. I doubt that there are 1041 unique PIDs involved; it is much more likely that a few connections have claimed most of those locks. Of course, terminating those connections might have some negative side-effects. It would probably, for instance, terminate the connection that you WANT to have working on alterations, so you might want to exclude *that* connection. -- (reverse (concatenate 'string gro.mca @ enworbbc)) http://cbbrowne.com/info/lsf.html Rules of the Evil Overlord #154. I will instruct my Legions of Terror in proper search techniques. In particular, if they are searching for escapees and someone shouts, Quick! They went that way!, they must first ascertain the identity of this helpful informant before dashing off in hot pursuit. http://www.eviloverlord.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] grant everything on everything and then revoke
On Tue, 03 Mar 2009 09:29:17 -0800 John R Pierce pie...@hogranch.com wrote: Ivan Sergio Borgonovo wrote: I'd like to have different users mainly to have a different search schema path. Things may evolve so this is not going to be the only reason to have more than one user. But I'm faced with the problem of granting the same access of the owner of the db to the other users. ... have the database owned by a 'ROLE and make your users members of that ROLE. This looks really neat for the beginning and it doesn't even look as an hack ;) What if I had to differentiate privileges of each user? Will revoking privileges on each users work? I didn't understand how CREATE SCHEMA schemaname AUTHORIZATION username; AUTHORIZATION really works and maybe it could be another way to approach the problem. Thanks to everybody. Even the pointer to the functions was interesting. The acl_admin.grant_on_all seems what my initial quest was looking for, but the ROLE trick seems much more straight forward currently. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgreSQL amazon ec2 cloud
On Tue, Mar 03, 2009 at 10:25:17AM -0500, Tom Lane wrote: Adrian Klaver akla...@comcast.net writes: Nothing. I have created a Postgres instance on an EC2 virtual machine with attached EBS(Elastic Block Storage). I only got as far as creating in it and verifying it would run, no benchmarking. EC2 instances have storage as part of the instance but it is temporary and goes away when the instance is shut down. For a database you want EBS as it is a virtual harddrive that persists. Should an EC2 instance go down, you just reattach the EBS drive on reboot. ... I wonder whether you have any guarantees about database consistency in that situation? PG has some pretty strong requirements about fsync behavior etc, and I'd not want to take it on faith that a cloud environment will meet those requirements. Performance would be an interesting question too. regards, tom lane There's a place called Engine Yard offering Ruby on Rails hosting with PostgreSQL on Amazon EC2. - Josh / eggyknap signature.asc Description: Digital signature
Re: [GENERAL] Strange behavior: row won't delete
Phoenix Kiula phoenix.ki...@gmail.com writes: I guess my question is, how should I remove all pending locks on a table so that I can get on with the rest of the stuff? I mean, even if I can now find an offending RULE on the table, I cannot replace or remove it. ' You're off on the wrong track. Locks are held by transactions until the transaction commits. You need to find the transactions which are holding these locks and either commit or roll them back. You look in pg_locks to see what locks transactions are holding. In particular look for rows with granted set to t, especially locks on relations and especially ExclusiveLocks. Then you take the pid of those transactions and look in pg_stat_activity to see what they're up to. If they say idle in transaction then they're waiting for the client to do something. If they stay that way for any length of time while holding locks which block other transactions that's bad. Alternately if you see a query in pg_stat_transaction which is taking a long time to run you might check whether you have a bad plan or a bad query running while holding locks effectively doing the same thing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL clustering with DRBD
On Tue, Mar 3, 2009 at 3:57 AM, Tim Uckun timuc...@gmail.com wrote: Again, this is a lot of work to avoid master / slave with failover. Are you sure it's really needed for your situation? What is the most straightforward and simple way to achieve master slave with failover? We use Slony at work, which has a pretty easily run command to failover. We initiate failover at the application level when a majority of the servers agree that the primary is no longer responding. Preferably a solution that would have decent monitoring, alerting and failback capacity. We had to write out own alerting and such back in the day. Nagios can definitely keep you apprised of things happening. So can a collection of shell scripts. I'm afraid I know of no pre-made pre-wrapped packages to do what you want. Then again, each solution needs to fit the needs of the user, so it's hard to just have one size fit all here. It would be also nice if you could use the standby as a read only database for reporting or something. That works wonderfully well with Slony. We actually have one master, one failover slave that would take over in the case of the master going down, and x read slaves that read from that machine for the web app to read. Allows pretty good scalability and redundancy. The problem with multi-master is that you can either have good performance or good redundancy, but it's hard to get both. And don't hold up RAC as an example of great multi-master. It's overly complex, tends to fail individual nodes a lot and costs an arm and a leg. And performance wise it's definitely a meh grade solution for most applications. -- 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 use pg_dump to dump tables whose owner is me
I'd put them all into the same schema and dump that. pg_dump -n schemaname On Tue, Mar 3, 2009 at 8:30 AM, Roger Chen roger.edc...@gmail.com wrote: Thanks. If there are many tables owned by me and some other users, are there any easy ways to do that? On Mon, Mar 2, 2009 at 9:23 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Mon, Mar 2, 2009 at 5:31 PM, Roger Chen roger.edc...@gmail.com wrote: Hi, Can anyone tell me how to do that? I could find that in man page of pg_dump. Thanks. pg_dump -t table1 -t table2 ? -- When fascism comes to America, it will be the intolerant selling it as diversity. -- 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 amazon ec2 cloud
On Mar 3, 2009, at 12:39 PM, Joshua Tolley wrote: On Tue, Mar 03, 2009 at 10:25:17AM -0500, Tom Lane wrote: Adrian Klaver akla...@comcast.net writes: Nothing. I have created a Postgres instance on an EC2 virtual machine with attached EBS(Elastic Block Storage). I only got as far as creating in it and verifying it would run, no benchmarking. EC2 instances have storage as part of the instance but it is temporary and goes away when the instance is shut down. For a database you want EBS as it is a virtual harddrive that persists. Should an EC2 instance go down, you just reattach the EBS drive on reboot. ... I wonder whether you have any guarantees about database consistency in that situation? PG has some pretty strong requirements about fsync behavior etc, and I'd not want to take it on faith that a cloud environment will meet those requirements. Performance would be an interesting question too. regards, tom lane There's a place called Engine Yard offering Ruby on Rails hosting with PostgreSQL on Amazon EC2. Actually, we don't have any EC2 offerings for PostgreSQL yet. Well, technically it *is* installed, but it's not pre-configured and, thus, not supported yet. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgsql announce now on twitter
Hi everyone: I really wanted to let everyone know that I've created @PGSQL_Announce on Twitter and setup a cron job to parse the feed and post it to Twitter. It's been working for a little while now and I think it's stable enough to announce to the world. Forgive me if this isn't the way to announce this, or if I'm stepping on someone's toes. I checked Twitter and didn't see anything existing for this, so I decided to scratch my own itch. :) If anyone with PGSQL wants it shut down or wants to take it and make it official, just ping me. Otherwise, I hope someone can find use in this. (PS - I'm not on these lists. CC me if you want me to see your response) -- Douglas Just-scratching-his-own-itch Hunley (doug at hunley.homeip.net) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Date/time of last commit
Is there a way to get the date/time of the last commit on a database? Tom -- 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] Shared Buffers
On Mon, Mar 2, 2009 at 7:40 AM, Gauthier, Dave dave.gauth...@intel.com wrote: I believe that op system side buffering can play a role too. I our case, the DB server (machine op sys) caches data that it pulled from disk (not necessarily from a DB) and also the disk servers do the same. If a block was removed from the DB buffer cache to accommodate more recently requested data, but the evicted block is live in memory on the DB server or the disk server, it can pull from there instead of performing an expensive disk-IO. This is very true for certain workloads. If your db is bigger than memory, and you only work on a tiny bit at a time, the kernel is often better at caching than pgsql. Run something like pgbench on a machine with say 4 Gig of memory and a 40 Gig pgbench db dir, and you're better off with 128M or something for shared_buffers than 1G often times. Truly random small access, has, for me, worked better with moderate to smaller shared_buffers. Plus if you need to run a few memory hog queries, the kernel can flush out some free memory from cache quickly, but shared_buffers is static. So, the kernel file cache is self tuning. But you'll have to test it with your application to really see. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgsql announce now on twitter
Am 2009-03-03 21:38:39, schrieb Douglas J Hunley: Hi everyone: I really wanted to let everyone know that I've created @PGSQL_Announce on Twitter and setup a cron job to parse the feed and post it to Twitter. It's been working for a little while now and I think it's stable enough to announce to the world. For What? -- Increasing Twitter-Spam? Forgive me if this isn't the way to announce this, or if I'm stepping on someone's toes. I checked Twitter and didn't see anything existing for this, so I decided to scratch my own itch. :) If anyone with PGSQL wants it shut down or wants to take it and make it official, just ping me. Otherwise, I hope someone can find use in this. Twitter is the last crap, sending me between 30 and 150 spams per day and the Abuse Departement does not react. I was forced to block Twitter entirely on my Mailserver since they have even spamed my postmaster and abuse address. (PS - I'm not on these lists. CC me if you want me to see your response) Done Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # http://www.tamay-dogan.net/ http://www.can4linux.org/ Michelle Konzack Apt. 917 ICQ #328449886 +49/177/935194750, rue de Soultz MSN LinuxMichi +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
[GENERAL] Problem with Mauritius summer time (MUST)
Hi, Mauritius use daylight saving since 2008-11. The Olson database has been updated to reflect the Mauritius timezone change at the end of 2008 (maybe since tzdata-2008f or tzdata-2008g, not really sure). Postgresql is supposed to have the correct Mauritius timezone since 8.3.5 (http://www.postgresql.org/docs/8.3/interactive/release-8-3-5.html). From a debian box configured in the Mauritius timezone : $ date Wed Mar 4 11:10:01 MUST 2009 $ psql -c SELECT '2009-01-01 00:56:00 MUT'::timestamp timestamp - 2009-01-01 00:56:00 (1 row) $ psql -c SELECT '2009-01-01 00:56:00 MUST'::timestamp ERROR: invalid input syntax for type timestamp: 2009-01-01 00:56:00 MUST As you can see Posgresql does not recognize the MUST (Mauritius Summer Time). For reference, here is the content of the tzdata file related to Mauritius : # Rule NAMEFROMTO TYPEIN ON AT SAVELETTER/S Rule Mauritius 1982only- Oct 10 0:001:00S Rule Mauritius 1983only- Mar 21 0:000 - Rule Mauritius 2008max - Oct lastSun 2:00s 1:00S Rule Mauritius 2009max - Mar lastSun 2:00s 0 - # Zone NAMEGMTOFF RULES FORMAT [UNTIL] Zone Indian/Mauritius 3:50:00 - LMT 1907# Port Louis 4:00 Mauritius MU%sT # Mauritius Time Is this a bug in Posgresql or am I missing something ? This problem was encountered with Postgresql 8.3.5 and 8.3.6. Regards. -- Xavier Bugaud -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
