Re: [GENERAL] php4 and postgresql 8.3

[Scott Marlowe]

On Mon, Mar 2, 2009 at 8:24 PM, shadrack  wrote:
> On Mar 2, 5:35 pm, pie...@hogranch.com (John R Pierce) wrote:
>> Tom Lane wrote:
>> > shadrack  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

[Harald Armin Massa[legacy]]

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

[Ivan Sergio Borgonovo]

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

[Tim Uckun]

>
>
> 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

[Sanjay Arora]

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

[Adrian Klaver]

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

[Oleg Bartunov]

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

[Tom Lane]

Adrian Klaver  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

[Roger Chen]

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  wrote:
> On Mon, Mar 2, 2009 at 5:31 PM, Roger Chen  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

[John DeSoi]

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

[A. Rich]

--- On Tue, 3/3/09, Roger Chen  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

[Adrian Klaver]

On Tuesday 03 March 2009 7:25:17 am Tom Lane wrote:
> Adrian Klaver  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

[Phoenix Kiula]

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

[Tom Lane]

Phoenix Kiula  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

[Alan Hodgson]

On Tuesday 03 March 2009, Phoenix Kiula  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

[Phoenix Kiula]

On Wed, Mar 4, 2009 at 12:10 AM, Tom Lane  wrote:
> Phoenix Kiula  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

[Phoenix Kiula]

> 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

[Phoenix Kiula]

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

[Adrian Klaver]

- "Phoenix Kiula"  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

[Phoenix Kiula]

On Wed, Mar 4, 2009 at 12:36 AM, Adrian Klaver  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

[Adrian Klaver]

- "Phoenix Kiula"  wrote:

> On Wed, Mar 4, 2009 at 12:36 AM, Adrian Klaver 
> 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

[Phoenix Kiula]

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

[Tom Lane]

Phoenix Kiula  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

[Phoenix Kiula]

On Wed, Mar 4, 2009 at 12:55 AM, Phoenix Kiula  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

[Phoenix Kiula]

On Wed, Mar 4, 2009 at 1:17 AM, Tom Lane  wrote:
> Phoenix Kiula  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

[Phoenix Kiula]

On Wed, Mar 4, 2009 at 1:23 AM, Phoenix Kiula  wrote:
> On Wed, Mar 4, 2009 at 1:17 AM, Tom Lane  wrote:
>> Phoenix Kiula  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

[John R Pierce]

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

[Chris Browne]

phoenix.ki...@gmail.com (Phoenix Kiula) writes:
> On Wed, Mar 4, 2009 at 12:55 AM, Phoenix Kiula  
> 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."  

-- 
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]

On Tue, 03 Mar 2009 09:29:17 -0800
John R Pierce  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

[Joshua Tolley]

On Tue, Mar 03, 2009 at 10:25:17AM -0500, Tom Lane wrote:
> Adrian Klaver  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

[Gregory Stark]

Phoenix Kiula  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 "" 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

[Scott Marlowe]

On Tue, Mar 3, 2009 at 3:57 AM, Tim Uckun  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

[Scott Marlowe]

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  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  wrote:
>> On Mon, Mar 2, 2009 at 5:31 PM, Roger Chen  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

[Erik Jones]

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  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

[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.

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

[Tom Spencer]

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

[Scott Marlowe]

On Mon, Mar 2, 2009 at 7:40 AM, Gauthier, Dave  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

[Michelle Konzack]

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 #
   
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)

[Xavier Bugaud]

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