[SQL] Starting autovacuum in postgresql-8.1.9
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I am running Red Hat Enterprise Linux 5 and postgresql-8.1.9-1.el5 (&c.). It seems autovacuum would be a good idea, but I cannot see how to start it, mainly because I cannot find it. There are autovacuum settings in postgresql.conf, but they are all commented out. Does it suffice to turn them on and restart the postmaster? Or are they off because autovacuum is not supported? I have looked around in the PostgreSQL book by Douglas & Douglas and they say it is in a contrib directory, but the one that makes sense does not seem to contain it. Is it built into the server now, or is it to be found somewhere else? In particular, pgavd does not exist anywhere on my system. - -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 09:05:01 up 7 days, 12:27, 3 users, load average: 4.16, 4.35, 4.29 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org iD8DBQFGxE8DPtu2XpovyZoRAuAgAJ9hrXdGSfX02BRQ/ZZpu+/4fcF+CQCdFAlT RtTL04V+dNhpWi/wh4MLc/w= =Am4q -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Starting autovacuum in postgresql-8.1.9
Jean-David Beyer wrote: > I am running Red Hat Enterprise Linux 5 and postgresql-8.1.9-1.el5 (&c.). > > It seems autovacuum would be a good idea, but I cannot see how to start it, > mainly because I cannot find it. There are autovacuum settings in > postgresql.conf, but they are all commented out. > > Does it suffice to turn them on and restart the postmaster? Or are they off > because autovacuum is not supported? I have looked around in the PostgreSQL > book by Douglas & Douglas and they say it is in a contrib directory, but the > one that makes sense does not seem to contain it. Is it built into the > server now, or is it to be found somewhere else? In particular, pgavd does > not exist anywhere on my system. Contrib existed up to 8.0. Autovacuum was integrated into the server in 8.1. It seems Douglas' book covers only 8.0. You can simply uncomment and turn it on. Note that you will need to enable stats_row_level too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Problem with phone list.
--- Michael Glaesemann wrote: > SELECT DISTINCT ON (phone_number) > phone_number, call_duration, id > FROM calls > ORDER BY phone_number > , call_duration DESC; Wasn't acquainted with "DISTINCT ON (column)". I found it to be many times faster than other suggestions using JOIN. Cheers, Fernando. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] SUPERUSER vs CREATEUSER causes foul 'code smell'
Hello, In the pg_users view - is there a way to differentiate between a role with SUPERUSER priveleges, and a user who merely has the CREATEUSER flag? If I want to create a role who can create other roles, but not have other SUPERUSER priveleges - how can I do that? Cheers, -J ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SUPERUSER vs CREATEUSER causes foul 'code smell'
On Aug 16, 2007, at 10:36 , Joshua_Kramer wrote: In the pg_users view - is there a way to differentiate between a role with SUPERUSER priveleges, and a user who merely has the CREATEUSER flag? If I want to create a role who can create other roles, but not have other SUPERUSER priveleges - how can I do that? I think you may be confusing CREATEROLE and CREATEUSER. AIUI, CREATEUSER is a holdover from the pre-role PostgreSQL days, when CREATEUSER implied SUPERUSER. (I may very well be wrong with the explanation, but the effects look the same.) CREATEROLE privilege does not imply SUPERUSER however. test=# create role user_creator with createuser; CREATE ROLE test=# create role role_creator with createrole; CREATE ROLE test=# select rolname, rolsuper, rolcreaterole from pg_roles; rolname | rolsuper | rolcreaterole -+--+--- postgres| t| t ... user_creator| t| f role_creator| f| t (By the way, I don't see a pg_users view in v8.2.4. There's a pg_user view and a pg_roles view however. What version are you using?) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SUPERUSER vs CREATEUSER causes foul 'code smell'
On 8/16/07, Joshua_Kramer <[EMAIL PROTECTED]> wrote: > In the pg_users view - is there a way to differentiate between a role with > SUPERUSER priveleges, and a user who merely has the CREATEUSER flag? select * from pg_roles; > If I want to create a role who can create other roles, but not have other > SUPERUSER priveleges - how can I do that? create role foo createrole login password 'foo'; See: http://www.postgresql.org/docs/8.2/static/sql-createrole.html ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] SUPERUSER vs CREATEUSER causes foul 'code smell'
Am Donnerstag, 16. August 2007 17:36 schrieb Joshua_Kramer: > In the pg_users view - is there a way to differentiate between a role with > SUPERUSER priveleges, and a user who merely has the CREATEUSER flag? No, because they are the same. > If I want to create a role who can create other roles, but not have other > SUPERUSER priveleges - how can I do that? See CREATEROLE privilege. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SUPERUSER vs CREATEUSER causes foul 'code smell'
"=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <[EMAIL PROTECTED]> writes: > On 8/16/07, Joshua_Kramer <[EMAIL PROTECTED]> wrote: >> If I want to create a role who can create other roles, but not have other >> SUPERUSER priveleges - how can I do that? > create role foo createrole login password 'foo'; CREATEROLE privilege is not exactly a superuser, but it implies a lot of administrative privileges anyway --- for instance the ability to change the password of another account. So this may not be exactly what the OP wants. We intended CREATEROLE as a way that a DBA could do all his routine account-management duties without being a "real" superuser (with the attendant ability to completely destroy the system with a slipup). Think of it as sudo that lets you do anything you want to /etc/passwd, but not "rm -rf /"... If you want exactly what was stated --- the ability to create new roles, and nothing else --- the best way is to create a function that does only that one thing, make it superuser-owned and SECURITY DEFINER, and grant execute privilege on it to whomever you want to be able to do that. (Don't forget to revoke the default public execute privilege.) Gateway functions of this sort are the standard solution whenever the system's classification of privileges isn't quite what you want. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
