[GENERAL] Not able to access schema functions and table...
Hi, I have executed below queries. CREATE SCHEMA mydb_schema AUTHORIZATION postgres; GRANT ALL ON SCHEMA mydb_schema TO postgres; REVOKE ALL ON SCHEMA mydb_schema FROM PUBLIC; REVOKE ALL ON FUNCTION mydb_schema.readtable() FROM PUBLIC; GRANT EXECUTE ON FUNCTION mydb_schema.readtable() to user1; GRANT SELECT ON mydb_schema.test1 TO user1; $ psql -h postgresqlhost.aus -d mydb -U user1 psql (8.4.1) Type help for help. user1= select mydb_schema.readtable(); ERROR: permission denied for schema mydb_schema user1= select * from mydb_schema.test1; ERROR: permission denied for mydb_schema LINE 1: select * from mydb_schema.test1; ^ user1= Could anyone please tell me what is wrong here? *I want users to have only select persions on tables of mydb_schema schema and function readtable execute permissions for only few users(like above user1).* Please let me know if there is any alternative way. Thanks, Dipti
Re: [GENERAL] Not able to access schema functions and table...
Le 19/02/2010 08:21, dipti shah a écrit : Hi, I have executed below queries. CREATE SCHEMA mydb_schema AUTHORIZATION postgres; GRANT ALL ON SCHEMA mydb_schema TO postgres; REVOKE ALL ON SCHEMA mydb_schema FROM PUBLIC; REVOKE ALL ON FUNCTION mydb_schema.readtable() FROM PUBLIC; GRANT EXECUTE ON FUNCTION mydb_schema.readtable() to user1; GRANT SELECT ON mydb_schema.test1 TO user1; $ psql -h postgresqlhost.aus -d mydb -U user1 psql (8.4.1) Type help for help. user1= select mydb_schema.readtable(); ERROR: permission denied for schema mydb_schema user1= select * from mydb_schema.test1; ERROR: permission denied for mydb_schema LINE 1: select * from mydb_schema.test1; ^ user1= Could anyone please tell me what is wrong here? *I want users to have only select persions on tables of mydb_schema schema and function readtable execute permissions for only few users(like above user1).* You should at least GRANT USAGE on your schema mydb_schema to your users. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Not able to access schema functions and table...
On 19/02/10 07:21, dipti shah wrote: Hi, I have executed below queries. CREATE SCHEMA mydb_schema AUTHORIZATION postgres; GRANT ALL ON SCHEMA mydb_schema TO postgres; REVOKE ALL ON SCHEMA mydb_schema FROM PUBLIC; user1= select * from mydb_schema.test1; ERROR: permission denied for mydb_schema Could anyone please tell me what is wrong here? There is a USAGE permission for the schema that you need to GRANT. -- Richard Huxton Archonet Ltd -- 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] Not able to access schema functions and table...
Thanks. That worked. On Fri, Feb 19, 2010 at 2:02 PM, Guillaume Lelarge guilla...@lelarge.infowrote: Le 19/02/2010 08:21, dipti shah a écrit : Hi, I have executed below queries. CREATE SCHEMA mydb_schema AUTHORIZATION postgres; GRANT ALL ON SCHEMA mydb_schema TO postgres; REVOKE ALL ON SCHEMA mydb_schema FROM PUBLIC; REVOKE ALL ON FUNCTION mydb_schema.readtable() FROM PUBLIC; GRANT EXECUTE ON FUNCTION mydb_schema.readtable() to user1; GRANT SELECT ON mydb_schema.test1 TO user1; $ psql -h postgresqlhost.aus -d mydb -U user1 psql (8.4.1) Type help for help. user1= select mydb_schema.readtable(); ERROR: permission denied for schema mydb_schema user1= select * from mydb_schema.test1; ERROR: permission denied for mydb_schema LINE 1: select * from mydb_schema.test1; ^ user1= Could anyone please tell me what is wrong here? *I want users to have only select persions on tables of mydb_schema schema and function readtable execute permissions for only few users(like above user1).* You should at least GRANT USAGE on your schema mydb_schema to your users. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Re: [GENERAL] Getting number of affected row after performing update
On 19/02/10 05:42, Yan Cheng Cheok wrote: Of course, I get an error at line : IF @@ROWCOUNT=0 THEN May I know what is the correct PostgreSQL syntax for @@ROWCOUNT after update? Best place for this sort of information is the manuals: http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS GET DIAGNOSTICS my_variable = ROWCOUNT; -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to get the users name from users group?
Hello All, I have created role database-users and inserted some of users inside it. Could you tell me which query to use for listing out those users name? I went through pg_users, pg_group but no luck. CREATE ROLE database-users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; CREATE ROLE dbuser1 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser1; CREATE ROLE dbuser2 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser2; How to query database-users to list the dbuser1 and dbuser2? Thanks in advance, Jack
Re: [GENERAL] PERFORM not working properly, please help..
2010/2/19 wilcza...@op.pl: Hi Pavel, thanks for reply. Your solution: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin return query select * from A1(); return; end; $BODY$ LANGUAGE 'plpgsql'; generates error cannot use RETURN QUERY in a non-SETOF function because A3 returns VOID. problem is in A3, cannot be void. PostgreSQL has only function. It hasn't procedures where you can execute unbinded queries. So if you can take any result from any rutine, you have to take it explicitly. VOID in pg means, there are no any interesting result, really no any interesting result. It can be problem, when you know MySQL procedures or MSSQL procedures. You have to forgot on procedures with returning recordset or multirecordset as secondary effect. regards Pavel Stehule Pavel Stehule pavel.steh...@gmail.com napisał(a): Hello 2010/2/18 wilcza...@op.pl: I have a function A1 that returns setof records, and I use it in two ways: 1) from function A2, where I need results from A1 2) from function A3, where I don't need these results, all I need is to execute logic from A1 Here ale very simple versions of my functions: CREATE OR REPLACE FUNCTION A1() RETURNS setof record AS $BODY$ begin  -- some logic here  return query select col from tab; end; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION A2() RETURNS setof record AS $BODY$ begin  -- some logic here  return query select * from A1() as dummy ( x double precision); end; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin  perform A1(); end; $BODY$ LANGUAGE 'plpgsql'; And here are my function calls: select * from A1() as(x double precision) --ok select * from A2() as(x double precision) --ok select * from A3(); --not ok, argh! it is correct. Every function has own stack for result. There are not some global stack. Perform just run function and doesn't copy inner result's stack to outer result stack. your A3 function have to be begin return query select * from a1 return; end; like a2 function regards Pavel Stehule The last one generates error set-valued function called in context that cannot accept a set. Why doesn't PERFORM work here? Thanks for help.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] set line number / input file name in psql
Heyho! I'm writing a little toy project, part of it involves scripts that pre- process sql and then feed it to psql. Is it possible to set the input filename / line number in psql? (Similar to how cpp includes this information so that the compiler can report the filename/line number of the C file and not the preprocessed file.) If it's not possible: would it be realistic that PostgreSQL would accept a patch, if I were to implement it? Either (without changing the syntax) using \set a special variable, or (for those who'd like to use cpp) extending the lexer and accepting the cpp syntax. (Perhaps conditionally on a --cpp-processed cmdline option.) cheers -- vbi -- Kallisti! signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] PERFORM not working properly, please help..
Hi Pavel, thanks for reply. Your solution: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin return query select * from A1(); return; end; $BODY$ LANGUAGE 'plpgsql'; generates error cannot use RETURN QUERY in a non-SETOF function because A3 returns VOID. Pavel Stehule pavel.steh...@gmail.com napisał(a): Hello 2010/2/18 wilcza...@op.pl: I have a function A1 that returns setof records, and I use it in two ways: 1) from function A2, where I need results from A1 2) from function A3, where I don't need these results, all I need is to execute logic from A1 Here ale very simple versions of my functions: CREATE OR REPLACE FUNCTION A1() RETURNS setof record AS $BODY$ begin  -- some logic here  return query select col from tab; end; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION A2() RETURNS setof record AS $BODY$ begin  -- some logic here  return query select * from A1() as dummy ( x double precision); end; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin  perform A1(); end; $BODY$ LANGUAGE 'plpgsql'; And here are my function calls: select * from A1() as(x double precision) --ok select * from A2() as(x double precision) --ok select * from A3(); --not ok, argh! it is correct. Every function has own stack for result. There are not some global stack. Perform just run function and doesn't copy inner result's stack to outer result stack. your A3 function have to be begin return query select * from a1 return; end; like a2 function regards Pavel Stehule The last one generates error set-valued function called in context that cannot accept a set. Why doesn't PERFORM work here? Thanks for help.. -- 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] PERFORM not working properly, please help..
Hi Ray, thanks for reply. Your solution needs to be modified with alias to get executed properly: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin select * from A1() as dummy ( x double precision ); return; end; $BODY$ LANGUAGE 'plpgsql'; but when used: select * from A3() it generates error query has no destination for result data. That was the reason to use PERFORM in the first place.. Raymond O'Donnell r...@iol.ie napisał(a): On 18/02/2010 12:05, wilcza...@op.pl wrote: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin perform A1(); end; $BODY$ LANGUAGE 'plpgsql'; You need to do: select * from A1(); Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get the users name from users group?
2010/2/19 Jignesh Shah jignesh.shah1...@gmail.com: Hello All, I have created role database-users and inserted some of users inside it. Could you tell me which query to use for listing out those users name? I went through pg_users, pg_group but no luck. CREATE ROLE database-users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; CREATE ROLE dbuser1 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser1; CREATE ROLE dbuser2 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser2; How to query database-users to list the dbuser1 and dbuser2? I think you're looking for: SELECT rolname from pg_authid a INNER JOIN pg_auth_members m ON m.member=a.oid WHERE m.roleid=(SELECT oid FROM pg_authid WHERE rolname='database-users') or something similar to that. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PERFORM not working properly, please help..
And what about that : http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET Maybe my french english disallowed me to understand right the question, but I think that this item could help in a way! Le vendredi 19 février 2010 à 11:04 +0100, Pavel Stehule a écrit : 2010/2/19 wilcza...@op.pl: Hi Pavel, thanks for reply. Your solution: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin return query select * from A1(); return; end; $BODY$ LANGUAGE 'plpgsql'; generates error cannot use RETURN QUERY in a non-SETOF function because A3 returns VOID. problem is in A3, cannot be void. PostgreSQL has only function. It hasn't procedures where you can execute unbinded queries. So if you can take any result from any rutine, you have to take it explicitly. VOID in pg means, there are no any interesting result, really no any interesting result. It can be problem, when you know MySQL procedures or MSSQL procedures. You have to forgot on procedures with returning recordset or multirecordset as secondary effect. regards Pavel Stehule Pavel Stehule pavel.steh...@gmail.com napisał(a): Hello 2010/2/18 wilcza...@op.pl: I have a function A1 that returns setof records, and I use it in two ways: 1) from function A2, where I need results from A1 2) from function A3, where I don't need these results, all I need is to execute logic from A1 Here ale very simple versions of my functions: CREATE OR REPLACE FUNCTION A1() RETURNS setof record AS $BODY$ begin  -- some logic here  return query select col from tab; end; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION A2() RETURNS setof record AS $BODY$ begin  -- some logic here  return query select * from A1() as dummy ( x double precision); end; $BODY$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin  perform A1(); end; $BODY$ LANGUAGE 'plpgsql'; And here are my function calls: select * from A1() as(x double precision) --ok select * from A2() as(x double precision) --ok select * from A3(); --not ok, argh! it is correct. Every function has own stack for result. There are not some global stack. Perform just run function and doesn't copy inner result's stack to outer result stack. your A3 function have to be begin return query select * from a1 return; end; like a2 function regards Pavel Stehule The last one generates error set-valued function called in context that cannot accept a set. Why doesn't PERFORM work here? Thanks for help..
Re: [GENERAL] PERFORM not working properly, please help..
On 19/02/2010 09:59, wilcza...@op.pl wrote: Hi Ray, thanks for reply. Your solution needs to be modified with alias to get executed properly: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin select * from A1() as dummy ( x double precision ); return; end; $BODY$ LANGUAGE 'plpgsql'; but when used: select * from A3() it generates error query has no destination for result data. That was the reason to use PERFORM in the first place.. Yes, you're absolutely right - the returned data needs a destination. I should have written: perform * from A1(); That ought to work too. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PERFORM not working properly, please help..
2010/2/19 wilcza...@op.pl: I suppose some workaround would be to introduce temporary cursor: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ declare _tmp record; begin select * from A1() as dummy ( x double precision ) into _tmp; end; $BODY$ LANGUAGE 'plpgsql'; But I'm not sure if this is more effiecent than A3 returning the set. Thanks for replies! Hard to say. Temporary tables needs changes in system dictionary - but are not limited by RAM and you can create index and actualise statistic. SRF (Set Returning Function) doesn't needs changes in dictionary, but doesn't allow indexes. Any way has own plus and minus. Personally I prefere SRF - when is possible - for to ten thousand rows sets. Regards Pavel Stehule Pavel Stehule pavel.steh...@gmail.com napisał(a): 2010/2/19 wilcza...@op.pl: Hi Pavel, thanks for reply. Your solution: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin  return query select * from A1();  return; end; $BODY$ LANGUAGE 'plpgsql'; generates error cannot use RETURN QUERY in a non-SETOF function because A3 returns VOID. problem is in A3, cannot be void. PostgreSQL has only function. It hasn't procedures where you can execute unbinded queries. So if you can take any result from any rutine, you have to take it explicitly. VOID in pg means, there are no any interesting result, really no any interesting result. It can be problem, when you know MySQL procedures or MSSQL procedures. You have to forgot on procedures with returning recordset or multirecordset as secondary effect. regards Pavel Stehule Pavel Stehule pavel.steh...@gmail.com napisał(a):  Hello   2010/2/18  wilcza...@op.pl:  I have a function A1 that returns setof records, and I use it in two ways:  1) from function A2, where I need results from A1  2) from function A3, where I don't need these results, all I need is to  execute logic from A1   Here ale very simple versions of my functions:   CREATE OR REPLACE FUNCTION A1() RETURNS setof record AS $BODY$  begin  Ă? -- some logic here  Ă? return query select col from tab;  end;  $BODY$ LANGUAGE 'plpgsql';   CREATE OR REPLACE FUNCTION A2() RETURNS setof record AS $BODY$  begin  Ă? -- some logic here  Ă? return query select * from A1() as dummy ( x double precision);  end;  $BODY$ LANGUAGE 'plpgsql';   CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$  begin  Ă? perform A1();  end;  $BODY$ LANGUAGE 'plpgsql';   And here are my function calls:  select * from A1() as(x double precision) --ok  select * from A2() as(x double precision) --ok  select * from A3(); --not ok, argh!    it is correct. Every function has own stack for result. There are not  some global stack. Perform just run function and doesn't copy inner  result's stack to outer result stack.   your A3 function have to be  begin   return query select * from a1   return;  end;   like a2 function   regards  Pavel Stehule  The last one generates error set-valued function called in context that  cannot accept a set. Why doesn't PERFORM work here? Thanks for help..  -- 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] PERFORM not working properly, please help..
I suppose some workaround would be to introduce temporary cursor: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ declare _tmp record; begin select * from A1() as dummy ( x double precision ) into _tmp; end; $BODY$ LANGUAGE 'plpgsql'; But I'm not sure if this is more effiecent than A3 returning the set. Thanks for replies! Pavel Stehule pavel.steh...@gmail.com napisał(a): 2010/2/19 wilcza...@op.pl: Hi Pavel, thanks for reply. Your solution: CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$ begin  return query select * from A1();  return; end; $BODY$ LANGUAGE 'plpgsql'; generates error cannot use RETURN QUERY in a non-SETOF function because A3 returns VOID. problem is in A3, cannot be void. PostgreSQL has only function. It hasn't procedures where you can execute unbinded queries. So if you can take any result from any rutine, you have to take it explicitly. VOID in pg means, there are no any interesting result, really no any interesting result. It can be problem, when you know MySQL procedures or MSSQL procedures. You have to forgot on procedures with returning recordset or multirecordset as secondary effect. regards Pavel Stehule Pavel Stehule pavel.steh...@gmail.com napisał(a):  Hello   2010/2/18  wilcza...@op.pl:  I have a function A1 that returns setof records, and I use it in two ways:  1) from function A2, where I need results from A1  2) from function A3, where I don't need these results, all I need is to  execute logic from A1   Here ale very simple versions of my functions:   CREATE OR REPLACE FUNCTION A1() RETURNS setof record AS $BODY$  begin  Ă? -- some logic here  Ă? return query select col from tab;  end;  $BODY$ LANGUAGE 'plpgsql';   CREATE OR REPLACE FUNCTION A2() RETURNS setof record AS $BODY$  begin  Ă? -- some logic here  Ă? return query select * from A1() as dummy ( x double precision);  end;  $BODY$ LANGUAGE 'plpgsql';   CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$  begin  Ă? perform A1();  end;  $BODY$ LANGUAGE 'plpgsql';   And here are my function calls:  select * from A1() as(x double precision) --ok  select * from A2() as(x double precision) --ok  select * from A3(); --not ok, argh!    it is correct. Every function has own stack for result. There are not  some global stack. Perform just run function and doesn't copy inner  result's stack to outer result stack.   your A3 function have to be  begin   return query select * from a1   return;  end;   like a2 function   regards  Pavel Stehule  The last one generates error set-valued function called in context that  cannot accept a set. Why doesn't PERFORM work here? Thanks for help..  -- 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] Setting a table to be ignored by autovacuum
On 18 Feb 2010, at 18:47, Chris Barnes wrote: Right you are, I'm due to upgrade end of month on this system. Here I was thinking 8.4. Sorry for the spam. Chris [postg...@pgprd01:~/pgcheck]$ psql Welcome to psql 8.3.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# Actually that just tells that you're using an 8.3 _client_. It doesn't tell what server-version you're using. For the server version do: deploy=# SELECT version(); version PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit (1 row) Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b7e74ef10441772699480! -- 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] Getting number of affected row after performing update
On 19 Feb 2010, at 6:42, Yan Cheng Cheok wrote: -- HOW?!?! --ERROR: column rowcount does not exist --LINE 1: SELECT @@ROWCOUNT=0 IF @@ROWCOUNT=0 THEN EXECUTE 'INSERT INTO statistic(fk_lot_id, value, measurement_type, statistic_type) VALUES ($1, $2, $3, $4)' USING _lotID, _value, _measurementType, _statisticType; END IF; May I know what is the correct PostgreSQL syntax for @@ROWCOUNT after update? In plpgsql you can test for FOUND or NOT FOUND after performing a query: IF NOT FOUND THEN EXECUTE '...' END IF; I think that's more convenient for your case than counting actual rows. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4b7e795110442010528220! -- 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 get the users name from users group?
Exactly the same! Thanks a ton. On Fri, Feb 19, 2010 at 3:58 PM, Magnus Hagander mag...@hagander.netwrote: 2010/2/19 Jignesh Shah jignesh.shah1...@gmail.com: Hello All, I have created role database-users and inserted some of users inside it. Could you tell me which query to use for listing out those users name? I went through pg_users, pg_group but no luck. CREATE ROLE database-users NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; CREATE ROLE dbuser1 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser1; CREATE ROLE dbuser2 LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; GRANT database-users TO dbuser2; How to query database-users to list the dbuser1 and dbuser2? I think you're looking for: SELECT rolname from pg_authid a INNER JOIN pg_auth_members m ON m.member=a.oid WHERE m.roleid=(SELECT oid FROM pg_authid WHERE rolname='database-users') or something similar to that. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [GENERAL] Doubts about oid
Jayadevan M escribió: I am an Oracle guy who is learning PostgreSQL. oid sounded a lot like rowid in Oracle. In Oracle, access by rowid is expected to be the fastest way of accessing a record, faster than even an index access followed by table access using the primary key. That was why I have this doubt about usage of oid being deprecated. Even if we use a sequence as PK (which is there in Oracle too), it is not as fast as access by rowid (I don't know if this applies to PostgreSQL's oid too). This is important when we use a cursors in an Oracle procedure (function in PostgreSQL) and loop through it and update specific records, when some conditions are met. Of course, that approach has its drawbacks -as in the case when row movement is enabled some maintenance activity moves the row to another location. I suppose you could use a cursor and then UPDATE ... WHERE CURRENT OF the cursor Another scenario is when we want to delete duplicate records in a table. You can use the ctid system column for this. This column represents the physical position of the row in the table, so it changes in many situations, for example during an UPDATE. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Setting a table to be ignored by autovacuum
It is... Welcome to psql 8.3.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# SELECT version(); version -- PostgreSQL 8.3.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9) (1 row) postgres=# Subject: Re: [GENERAL] Setting a table to be ignored by autovacuum From: dal...@solfertje.student.utwente.nl Date: Fri, 19 Feb 2010 12:24:24 +0100 CC: schmi...@gmail.com; d...@archonet.com; pgsql-general@postgresql.org To: compuguruchrisbar...@hotmail.com On 18 Feb 2010, at 18:47, Chris Barnes wrote: Right you are, I'm due to upgrade end of month on this system. Here I was thinking 8.4. Sorry for the spam. Chris [postg...@pgprd01:~/pgcheck]$ psql Welcome to psql 8.3.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# Actually that just tells that you're using an 8.3 _client_. It doesn't tell what server-version you're using. For the server version do: deploy=# SELECT version(); version PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5646), 64-bit (1 row) Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:1029,4b7e74ee10441497119330! _ Check your Hotmail from your phone. http://go.microsoft.com/?linkid=9708121
[GENERAL] PostgreSQL fails to start
I've installed postgreSQL 8.4 on a Windows XP machine, and I can't get it to start. Choosing Start Server from the menu produces a DOS box that remains blank for about 3 minutes, then comes up with this error message: The postgresql-8.4 service is starting. The postgresql-8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press return to continue... Typing the suggested command actually produces no help other than what has already been printed. I do normally use NOD32 anti-virus and Windows Firewall. I uninstalled NOD32 before installing postgreSQL, and disabled the firewall. This was the state of the machine when the above error message was produced. I get the same error if NOD32 and the firewall are running. As far as I can tell, no log files have been produced. Therefore I am at a loss as to how to proceed. Any ideas? Eddie -- 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 fails to start
On 19/02/2010 13:10, Heddon's Gate Hotel wrote: I've installed postgreSQL 8.4 on a Windows XP machine, and I can't get it to start. Choosing Start Server from the menu produces a DOS box that remains blank for about 3 minutes, then comes up with this error message: The postgresql-8.4 service is starting. The postgresql-8.4 service could not be started. The service did not report an error. More help is available by typing NET HELPMSG 3534. The start command returned an error (2) Press return to continue... Typing the suggested command actually produces no help other than what has already been printed. I do normally use NOD32 anti-virus and Windows Firewall. I uninstalled NOD32 before installing postgreSQL, and disabled the firewall. This was the state of the machine when the above error message was produced. I get the same error if NOD32 and the firewall are running. As far as I can tell, no log files have been produced. Therefore I am at a loss as to how to proceed. Any ideas? Something may have been written to the Windows event log - have a look there. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compiling .pgc programs gives error
I have a problem compiling pgc programs with ecpg. I always get the following error: /usr/local/pgsql/lib/libpgtypes.a(timestamp.o): In function `timestamp2tm': timestamp.c:(.text+0x2fc): undefined reference to `rint' Version: PostgreSQL 8.3.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4 Is this a known problem? Any help will be useful. Michael Stanton W.
Re: [GENERAL] Compiling .pgc programs gives error
mike stanton mstan...@acsa.cl writes: I have a problem compiling pgc programs with ecpg. I always get the following error: /usr/local/pgsql/lib/libpgtypes.a(timestamp.o): In function `timestamp2tm': timestamp.c:(.text+0x2fc): undefined reference to `rint' Seems like you need to add -lm to your link command. Or consider linking to libpgtypes.so instead of libpgtypes.a --- .so's generally carry information about libraries they depend on, which .a's don't. 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] GROUP BY column alias?
On Fri, Feb 19, 2010 at 12:07:42AM -0500, Tom Lane wrote: Lew no...@lwsc.ehost-services.com writes: Eric B. Ridge wrote: That explains it. Thanks. Breaks the rule of least surprise, but it is SQL. SQL:1999 and later use a slightly different definition which is not entirely upward compatible with SQL-92. In most cases, however, PostgreSQL will interpret an ORDER BY or GROUP BY expression the same way SQL:1999 does. The current SQL standard *supersedes* all previous ones. There isn't a hierarchy in the sense of higher levels of compliance that our docs implicitly and falsely assume in many spots, and we need to make them stop including this idea. The only standard actually worth citing today is SQL:2008, and the day the next one comes out, we need to change all our references to cite it. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] GROUP BY column alias?
David Fetter da...@fetter.org writes: On Fri, Feb 19, 2010 at 12:07:42AM -0500, Tom Lane wrote: SQL:1999 and later use a slightly different definition which is not entirely upward compatible with SQL-92. In most cases, however, PostgreSQL will interpret an ORDER BY or GROUP BY expression the same way SQL:1999 does. The current SQL standard *supersedes* all previous ones. That is the opinion of the SQL committee, all right, but it has got precious little to do with the real world. 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] Doubts about oid
On Thu, Feb 18, 2010 at 11:08 PM, Scott Marlowe scott.marl...@gmail.com wrote: Oracle and postgres are definitely different here. There's really no equivalent to rowid in pgsql. oid has no special optimizations. An indexed PK of a serial is about as good as it gets, possibly clustered. access by CTID is the fastest it gets. I use it to do mass updates after selecting a large number of rows. I can guarantee nobody else is modifying those rows so i know it is safe. -- 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] Possible causes for database corruption and solutions
Is changing the OS/X wal_sync_method default something we should consider? --- Greg Smith wrote: Michael Clark wrote: The solution to the problem seemed to be to change the value for the wal_sync_method setting to fsync_writethrough from the default of fsync. I was surprised recently to discover the default wasn't fsync_writethrough on that platform, because it probably should be. There is no other safe mode to run PostgreSQL in OS X with. If you don't invoke the write-through cache flushing code, you can expect databases to get regularly corrupted if people do things like lose power in the middle of writing something, exactly as you're seeing. Secondly, I ask about an alternative solution to the corruption problem because with preliminary testing we have seen a significant degradation in performance. So far the two operations we have noted are database creation and database restores. For the restore case, you might get a good sized boost in performance without introducing a risk of corruption by turning off the synchronous_commit parameter. That will put you in a position where you can have a committed transaction not actually be on disk if there's a crash or sudden power outage, but you won't get an actual corruption in that case. So fsync_writethough plus synchronous_commit=off should be no less safe than what you've got now, but probably not as fast as what you're used to. As already pointed out, there is a trade-off here you can't bargain with: you can either have your data completely safe, or you can execute quickly, but you can't do both. Robust data integrity slows things down and there's little you can do about it without buying hardware targeted to improve on that. The database creation issue just came up on one of the lists here the other day as being particularly slow in the situation you're in, and that parameter change doesn't help there. There's been some design change suggestions around that to improve the situation, but you're not likely to see those in the server code for a year or more. I should note here that we have not tuned PG at all. You could probably see a good sized performance increase just from increasing checkpoint_segments a bit from its default (3). Since it sounds like you're trying to keep your product's disk space footprint under control, increasing that to around 10 would probably as high as you want to go. You can't really increase shared_buffers a lot on your platform lest your users get stuck with weird problems where the server won't start, from what I hear OS X is fairly hostile to the kernel adjustments you need to do in order to support that. There's a general intro to things you might tune in the postgresql.conf at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server None of those are going to help you out with slow database creation, you might be able to pull down the restore times by tweaking some of the parameters there upwards. A large number of the tunables recommend to tweak there mainly impact query execution time. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] When making ODBC remote connection, which executable is called?
Hello, After I install PostgreSQL to Windows Server 2008, I cannot connect to it through another computer. I believe the Window's firewall is blocking the traffic. So I go to the firewall setup to create a new Inbound rule. When creating a Custom-type rule to specify the details, I have the following question. I can specify the rule applies to PostreSQL service. But I am not certain what program it should be applied to? Is it: - [PostgreSQL_install_dir]\bin\postgres.exe or other file? Many thanks! Nim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sr. Linux Systems Architect - CentOS, PostgresSQL/pgPool-II, Apache Server, Python, Bacula
bJob Responsibilities/b The Sr. Linux Systems Architect will be responsible for production and supporting infrastructure behind the WordStream Suite of products. If you enjoy a startup experience which is both challenging and very rewarding, then this is the position for you! As a part of a small team, you will be required to take ownership of mission-critical systems, and will be frequently expected to implement changes which will have an immediate impact on our business. bDay-to-day activities include/b: * Systems Administration across dozens of machines, including keeping systems up-to-date, protected, and backed up * Implementation and management of distributed, redundant database systems, using tools like pgPool-II * Development and maintenance of product-specific system maintenance tools and processes * Design of processes for scaling the infrastructure from dozens of machines to hundreds of machines * Design and implementation of infrastructure management/ monitoring solutions * Implementation and maintenance of Engineering-related systems, including systems related to: project management, source code management, code review, and continuous builds This position is a Senior position which requires a highly motivated candidate who is able to take initiative and has a thorough understanding of all aspects of Systems Administration. Though this is a telecommute/work-from-home position, it is a full- time position: candidates should be prepared to work a normal schedule without direct supervision, and must have excellent time management skills. Day-to-day direction will consist primarily of high-level objectives, and the Sr. Systems Architect will be expected to establish a design and execution plan, and follow through with it. This position will require a significant amount of work with Open Source technologies. WordStream is a proud supporter of Open Source, and does strive to contribute improvements back to the Open Source community. bQualifications/b: Only candidates with a Bachelor's degree in Computer Science or Engineering, or, a very substantial amount of related technical coursework and work experience in a related field will be considered. bRequired Skills/b: Linux System Administration, PostgresSQL, Apache Server bBonus Skills/b: Red Hat Enterprise Linux (RHEL)/CentOS, pgPool- II, Git, Trac, Bacula, BuildBot, SQLite, Open-Source software development, SQL, Python, Google App Engine, Juniper Networking Gear bRequired Experience/b: 3+ years bRequired Education/b: Bachelor's Degree bPosition Title/b: Sr. Systems Architect bType of Position/b: Full-time or Contract-to-hire bTravel Required/b: Limited. Two trips a year (all expenses paid) to Boston for Engineering Team meetings. bLocation: Worldwide/b - This is a Telecommute Job / Work From Home (WFH), or, optionally, work from Boston, MA. bHow To Apply/b: To apply for the Sr. Systems Architect position, send a resume and cover letter to bjobs at WordStream dot com./b bAbout WordStream/b WordStream is a venture-backed startup engaged in providing online software as a service (SaaS) search engine marketing software solutions for PPC/SEM and SEO. Our patented, innovative software-as-a- service applications automate the manual, repetitive work involved in search engine optimization, saving time and enabling customers to improve ROI on search marketing objectives in a consistent and repeatable manner. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How can I get the English version?
Hi! I am running PostgreSQL on a German Windows machine. Client programs like psql and pgAdmin are printing German translation strings everywhere, even though I told the installer to use English/United States locale. How can I disable all translations and simply use the English version of all programs? Regards, -- Nils Gösche Don't ask for whom the CTRL-G tolls. -- 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 can I get the English version?
Nils Gösche wrote on 19.02.2010 23:29: Hi! I am running PostgreSQL on a German Windows machine. Client programs like psql and pgAdmin are printing German translation strings everywhere, even though I told the installer to use English/United States locale. How can I disable all translations and simply use the English version of all programs? Regards, set LC_MESSAGES=English Regards Thomas -- 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 can I get the English version?
Thomas wrote: set LC_MESSAGES=English Yes, that works very well for psql, thanks! However, pgAdmin is still in German. Not even setting LC_ALL to en_US seems to help. Any other trick? Regards, -- Nils Gösche Don't ask for whom the CTRL-G tolls. -- 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 can I get the English version?
Nils Gösche wrote on 20.02.2010 00:20: set LC_MESSAGES=English Yes, that works very well for psql, thanks! However, pgAdmin is still in German. Any other trick? File - Options - User Language - English works for me Thomas -- 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 can I get the English version?
Thomas wrote: File - Options - User Language - English works for me *groan* I was looking at that dialog, but still missed that. Thanks again! Regards, -- Nils Gösche Don't ask for whom the CTRL-G tolls. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] A new log analysis tool for Postgres is available
I've just committed a log-parsing front-end for mk-query-digest, a tool included in Maatkit, so it can parse and analyze Postgres log files. I encourage people to try this functionality and give feedback by replying here, adding to http://code.google.com/p/maatkit/issues/detail?id=535, or jumping on the Maatkit mailing list. A brief mental QA of things I expect people to ask: Q: Why another log analysis tool? Isn't pgfouine good enough? A: The mk-query-digest log analysis tool already existed, and lots of work has gone into making it very good. All I did was add a pg-capable parser to it. I am not trying to play king-of-the-mountain with any other log analysis tool, but in brief browsing around I saw complaints that a mere GB or three of log files was taking hours to parse in pgfouine. I don't have real-life files that big myself, but mk-query-digest has been extensively tuned for performance and has no problems with many gigs of files in other formats (but please do watch out for memory consumption; Perl hogs RAM. Run this on a non-critical server, please.) Additionally, mk-query-digest has some nice properties: just download-and-go with no installation necessary; written in Perl with minimal dependencies so no PHP install or CPAN libraries are necessary; and lots more. Finally, it's not a me-too tool; it is different from pgfouine and different might be a good thing. Q: How do I get it? A: At this point, it's unreleased, but you can get the latest SVN trunk in the usual Maatkit way: wget http://www.maatkit.org/trunk/mk-query-digest;. Q: How do I run it? A: Simple: perl mk-query-digest --type pglog /path/to/logfile should produce a report on the most important queries. Q: Where is the documentation? A: Maatkit's documentation is always embedded within the tools themselves. Use perldoc mk-query-digest and search for pglog to find docs on this specific feature, or use the --help option to get an overview of the tool in general. This is a complex and powerful tool, and I encourage you to learn more about what you can do with it. The default is to do something useful, as you should see by running the command above. Q: What's the development status? A: Dozens of PG-specific unit and integration test cases all pass cleanly. But I need real-life testing and bug reports, feature requests, etc. Also, the reporting format and perhaps some other functionality is not yet PG-aware. There are helpful little copy-paste ready shortcuts for things like examining the structure of tables found within queries; right now these are MySQL-centric. But that will change. That's all for now -- let me know what you think! - Baron -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
