[HACKERS] default_tablespace
Hi Hackers, After reviewing docs and searching web cannot find out how to determine the default tablespace of a user? Like: select spcname from blab where roloid = ; Is this possible? Thank you for your time. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default_tablespace
On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote: - Hi Hackers, - - After reviewing docs and searching web - cannot find out how to determine the default tablespace - of a user? - - Like: - - select spcname from blab where roloid = ; - - Is this possible? - - Thank you for your time. - It doesn't appear to me that default tablespaces are assigned to a user, they're assigned to a database. A user can set the variable default_tablespace in their session to over-ride the database default, but that wouldn't be stored anywhere in the database (it's a client variable). you can find the OID for the default tablespace for a specific database in pg_database. more info: http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-DEFAULT-TABLESPACE Dave -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default_tablespace
On Feb17, 2011, at 18:32 , David Kerr wrote: On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote: - After reviewing docs and searching web - cannot find out how to determine the default tablespace - of a user? It doesn't appear to me that default tablespaces are assigned to a user, they're assigned to a database. A user can set the variable default_tablespace in their session to over-ride the database default, but that wouldn't be stored anywhere in the database (it's a client variable). You can, however, do ALTER USER user SET default_tablespace=whatever, which will cause default_tablespace to be set automatically upon login for that user. I don't know of the top of my head how we store that in the system catalogs, though. You'll have to check the documentation to find that out... best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default_tablespace
On Thu, Feb 17, 2011 at 08:55:55PM +0100, Florian Pflug wrote: - On Feb17, 2011, at 18:32 , David Kerr wrote: - On Wed, Feb 16, 2011 at 03:59:13PM -0800, carl clemens wrote: - - After reviewing docs and searching web - - cannot find out how to determine the default tablespace - - of a user? - - It doesn't appear to me that default tablespaces are assigned to a user, they're - assigned to a database. - - A user can set the variable default_tablespace in their session to over-ride the - database default, but that wouldn't be stored anywhere in the database (it's a client - variable). - - - You can, however, do ALTER USER user SET default_tablespace=whatever, which will - cause default_tablespace to be set automatically upon login for that user. - - I don't know of the top of my head how we store that in the system catalogs, though. - You'll have to check the documentation to find that out... - - best regards, - Florian Pflug oh, you're right. my mistake. and it looks like it's stored in useconfig in pg_user. select * from pg_user where usename = 'david.kerr'; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig +--+-+--+---+--+--+--- david.kerr |16482 | f | t| t | | | {default_tablespace=test} Dave -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] default_tablespace
Excerpts from Florian Pflug's message of jue feb 17 16:55:55 -0300 2011: You can, however, do ALTER USER user SET default_tablespace=whatever, which will cause default_tablespace to be set automatically upon login for that user. I don't know of the top of my head how we store that in the system catalogs, though. You'll have to check the documentation to find that out... It's in pg_db_role_settings. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers