Обсуждение: Role & User - Inheritance?
[GENERAL] Role & User - Inheritance? A ROLE dba01 has been given the option of SUPERSUSER. A USER user01 is created and tagged to the above ROLE dba01. When attempting to create a Tablespace (logged in as user01) it generates the following message: "permission denied to create tablespace (tblspc01). Must be superuser to create a tablespace.". Since user01 is tagged to dba01 (who has the SUPERUSER option) - should'nt user01 also inherit this? If not - what benefit exists in grouping users under a Role? Cheers! _________________________________________________________________ Spice up your IM conversations. New, colorful and animated emoticons. Get chatting! http://server1.msn.co.in/SP05/emoticons/
Unfortunately,
Everything that is a permission (CREATEROLE, etc) when creating a role cannot be inherited. Only the GRANT stuff is inherited and I think only when the WITH .. is given on the GRANT.
I may be wrong on the last part though.
David
Everything that is a permission (CREATEROLE, etc) when creating a role cannot be inherited. Only the GRANT stuff is inherited and I think only when the WITH .. is given on the GRANT.
I may be wrong on the last part though.
David
On 3/15/07, Alexi Gen <sqlcatz@hotmail.com> wrote:
[GENERAL] Role & User - Inheritance?
A ROLE dba01 has been given the option of SUPERSUSER.
A USER user01 is created and tagged to the above ROLE dba01.
When attempting to create a Tablespace (logged in as user01) it generates
the following message:
"permission denied to create tablespace (tblspc01). Must be superuser to
create a tablespace.".
Since user01 is tagged to dba01 (who has the SUPERUSER option) - should'nt
user01 also inherit this? If not - what benefit exists in grouping users
under a Role?
Cheers!
_________________________________________________________________
Spice up your IM conversations. New, colorful and animated emoticons. Get
chatting! http://server1.msn.co.in/SP05/emoticons/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
"Alexi Gen" <sqlcatz@hotmail.com> writes: > Since user01 is tagged to dba01 (who has the SUPERUSER option) - should'nt > user01 also inherit this? No, he has to actually SET ROLE to the superuser account to make himself a superuser. SUPERUSER and the other non-GRANTable attributes of a role don't inherit through mere membership. This is partly an implementation limitation but I happen to think it's a good idea ... superuserness is a mighty big hammer to be letting loose accidentally. regards, tom lane
Hello List, I want to write a statement-level trigger - one that happens once per statement - such that, immediately after an insert into a table(which gets a unique integer value as an ID from a defined sequence, being the primary key on the table), a new table is created with foreign key constraint on that unique ID. My concern is with what happens if two such inserts occur at almost the same time, what is the best way to ensure that I never miss the creation of one of the tables due to the 2nd one, possibly reading the same "max" or nextvalue that the immediate previous trigger read. As an example: insertX which initiates the trigger reads the 'nextvalue' from the sequence and begins to create the associcated table insertY happens almost at the same time, so that it gets the same 'nextvalue' from the sequence and would then create or attempt to create the 'same' assoicated table while missing it's 'true' 'nextvalue'. Thanks for any insight! -- Email: louis.gonzales@linuxlouis.net WebSite: http://www.linuxlouis.net "Open the pod bay doors HAL!" -2001: A Space Odyssey "Good morning starshine, the Earth says hello." -Willy Wonka
louis gonzales <gonzales@linuxlouis.net> writes: > As an example: > insertX which initiates the trigger reads the 'nextvalue' from the > sequence and begins to create the associcated table > insertY happens almost at the same time, so that it gets the same > 'nextvalue' from the sequence [ blink... ] Whatever makes you think that could happen? regards, tom lane
:) , something that is analogous to a race condition. is this something I shouldn't be concerned with? I suppose if I knew for certain there was some kind of synchronous behavior, then I wouldn't fear a potentially subsequent event completing before the previous one doing so. As a possible solution, I'm thinking that I can make the trigger be a before trigger, where the before trigger captures the 'nextvalue' for both the actual insert and the table creation would be based on this, while incrementing the sequence to guarantee that each successive pull on the nextvalue will have the correct one. Does that sound plausible? Thanks, Tom Lane wrote: >louis gonzales <gonzales@linuxlouis.net> writes: > > >>As an example: >>insertX which initiates the trigger reads the 'nextvalue' from the >>sequence and begins to create the associcated table >>insertY happens almost at the same time, so that it gets the same >>'nextvalue' from the sequence >> >> > >[ blink... ] Whatever makes you think that could happen? > > regards, tom lane > > -- Email: louis.gonzales@linuxlouis.net WebSite: http://www.linuxlouis.net "Open the pod bay doors HAL!" -2001: A Space Odyssey "Good morning starshine, the Earth says hello." -Willy Wonka
Tom Lane wrote: > louis gonzales <gonzales@linuxlouis.net> writes: >> As an example: >> insertX which initiates the trigger reads the 'nextvalue' from the >> sequence and begins to create the associcated table >> insertY happens almost at the same time, so that it gets the same >> 'nextvalue' from the sequence That won't happen because of isolation :). When InsertX increments the sequence, it is forever incremented, so when InsertY increments it gets the next value... e.g; CREATE TABLE foo(id serial); Transaction 1: BEGIN; INSERT INTO foo(id) VALUES (DEFAULT); id now == 1 Transaction 2: BEGIN; INSERT INTO foo(id) VALUES (DEFAULT); id now == 2 Transaction 1; COMMIT; Transaction 2; COMMIT; Even if Transaction 1 were to rollback, it has already incremented the sequence so the next transaction would get 3. Joshua D. Drake > > [ blink... ] Whatever makes you think that could happen? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Hey Joshua, I appreciate the insight. That's clear. Thanks again, Joshua D. Drake wrote: >Tom Lane wrote: > > >>louis gonzales <gonzales@linuxlouis.net> writes: >> >> >>>As an example: >>>insertX which initiates the trigger reads the 'nextvalue' from the >>>sequence and begins to create the associcated table >>>insertY happens almost at the same time, so that it gets the same >>>'nextvalue' from the sequence >>> >>> > >That won't happen because of isolation :). When InsertX increments the >sequence, it is forever incremented, so when InsertY increments it gets >the next value... e.g; > >CREATE TABLE foo(id serial); > >Transaction 1: >BEGIN; >INSERT INTO foo(id) VALUES (DEFAULT); >id now == 1 > >Transaction 2: >BEGIN; >INSERT INTO foo(id) VALUES (DEFAULT); >id now == 2 > >Transaction 1; >COMMIT; > >Transaction 2; >COMMIT; > >Even if Transaction 1 were to rollback, it has already incremented the >sequence so the next transaction would get 3. > >Joshua D. Drake > > > > > >>[ blink... ] Whatever makes you think that could happen? >> >> regards, tom lane >> >>---------------------------(end of broadcast)--------------------------- >>TIP 5: don't forget to increase your free space map settings >> >> >> > > > > -- Email: louis.gonzales@linuxlouis.net WebSite: http://www.linuxlouis.net "Open the pod bay doors HAL!" -2001: A Space Odyssey "Good morning starshine, the Earth says hello." -Willy Wonka
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 03/15/07 22:14, louis gonzales wrote: > Hello List, > I want to write a statement-level trigger - one that happens once per > statement - such that, immediately after an insert into a table(which > gets a unique integer value as an ID from a defined sequence, being the > primary key on the table), a new table is created with foreign key ^^^^^^^^^^^^^^^^^^^^ > constraint on that unique ID. So if you insert 10,000 records into T, you then have 10,000 new tables? -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF+nDLS9HxQb37XmcRAkrOAJ4pKzY/TVlm3K0aqiEY7ukhCfNZjwCfa5C7 mukqcoAmU2/OYr4QFVxjt6k= =sHA3 -----END PGP SIGNATURE-----
Actually, there will be creation of 2 new tables for each insert on 'primary' table, so for 10K records, we would have 20K tables. Those tables each will never grow more than a few records each. Is it better to have 1 monolithic table and have to search it, or small individual tables but many of them? Ron Johnson wrote: >-----BEGIN PGP SIGNED MESSAGE----- >Hash: SHA1 > >On 03/15/07 22:14, louis gonzales wrote: > > >>Hello List, >>I want to write a statement-level trigger - one that happens once per >>statement - such that, immediately after an insert into a table(which >>gets a unique integer value as an ID from a defined sequence, being the >>primary key on the table), a new table is created with foreign key >> >> > ^^^^^^^^^^^^^^^^^^^^ > > >>constraint on that unique ID. >> >> > >So if you insert 10,000 records into T, you then have 10,000 new tables? > >-----BEGIN PGP SIGNATURE----- >Version: GnuPG v1.4.6 (GNU/Linux) > >iD8DBQFF+nDLS9HxQb37XmcRAkrOAJ4pKzY/TVlm3K0aqiEY7ukhCfNZjwCfa5C7 >mukqcoAmU2/OYr4QFVxjt6k= >=sHA3 >-----END PGP SIGNATURE----- > >---------------------------(end of broadcast)--------------------------- >TIP 6: explain analyze is your friend > > -- Email: louis.gonzales@linuxlouis.net WebSite: http://www.linuxlouis.net "Open the pod bay doors HAL!" -2001: A Space Odyssey "Good morning starshine, the Earth says hello." -Willy Wonka
louis gonzales wrote: > Actually, there will be creation of 2 new tables for each insert on > 'primary' table, so for 10K records, we would have 20K tables. Those > tables each will never grow more than a few records each. > > Is it better to have 1 monolithic table and have to search it, or small > individual tables but many of them? 20k tables sounds insane. I am not sure why you wouldn't want just one table. I mean, you are saying a *few* records, so you are talking what 100k records in a single table instead? 100k is nothing. Joshua D. Drake Joshua D. Drake > Ron Johnson wrote: > > On 03/15/07 22:14, louis gonzales wrote: > > >>>> Hello List, >>>> I want to write a statement-level trigger - one that happens once per >>>> statement - such that, immediately after an insert into a table(which >>>> gets a unique integer value as an ID from a defined sequence, being the >>>> primary key on the table), a new table is created with foreign key >>>> > ^^^^^^^^^^^^^^^^^^^^ > > >>>> constraint on that unique ID. >>>> > > So if you insert 10,000 records into T, you then have 10,000 new tables? > >> ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend >> -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
louis gonzales wrote: > > Is it better to have 1 monolithic table and have to search it, or > small individual tables but many of them? > Ron Johnson wrote: Yes, 1 large table. This is what RDMS were designed for.
On 3/16/07, louis gonzales <gonzales@linuxlouis.net> wrote: > I want to write a statement-level trigger - one that happens once per > statement - such that, immediately after an insert into a table(which > gets a unique integer value as an ID from a defined sequence, being the > primary key on the table), a new table is created with foreign key > constraint on that unique ID. hi, i think what you;re trying to do is wrong - having that many tables simply cannot work properly. additionally - i think you're misinformed. the kind of action you would like to "trigger on" is not "per statement" but "per row". example: insert into table x (field) select other_field from other_table; if this insert would insert 10 records - "once per statement" trigger would be called only once. but anyway - what you're proposing will lead to many, many problems. (plus it will never scale correctly). depesz
Dear Hubert,<br /> Two things<br /> 1) <u><b>"statement-level" and "row-level" straight from PostgreSQL: <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/8.1/interactive/trigger-datachanges.html">http://www.postgresql.org/docs/8.1/interactive/trigger-datachanges.html</a></b></u><br /><ul><li><p><u><b>Statement-leveltriggers</b></u> follow simple visibility rules: none of the changes made by a statementare visible to statement-level triggers that are invoked before the statement, whereas all modifications are visibleto statement-level after triggers. <li><p>The data change (insertion, update, or deletion) causing the trigger tofire is naturally <span class="emphasis"><i class="EMPHASIS">not</i></span> visible to SQL commands executed in a row-levelbefore trigger, because it hasn't happened yet. <li><p>However, SQL commands executed in a row-level before trigger<span class="emphasis"><i class="EMPHASIS">will</i></span> see the effects of data changes for rows previously processedin the same outer command. This requires caution, since the ordering of these change events is not in general predictable;a SQL command that affects multiple rows may visit the rows in any order. <li><p>When a <u><b>row-level</b></u>after trigger is fired, all data changes made by the outer command are already complete, and are visibleto the invoked trigger function. </ul> 2) Seeing as you have no idea - not attacking, stating fact - on the rationalebehind the "insert statement-level" to create 1-to-1 table for each statement-level <br /> insert, I'd say yourpresumption is unfounded. If you have some benchmark data, which support why/how to quantify, 50K records in a singletable, all of which would have N number of associated records in another table, would out perform 50K records in asingle table referencing dedicated 'small' tables, please do share.<br /><br /> Thanks though.<br /><br /> hubert depeszlubaczewski wrote: <blockquote cite="mid9e4684ce0703162139r7bc64180ga47e854af9678967@mail.gmail.com" type="cite">On3/16/07, louis gonzales <a class="moz-txt-link-rfc2396E" href="mailto:gonzales@linuxlouis.net"><gonzales@linuxlouis.net></a>wrote: <br /><blockquote type="cite">I want to writea <big><big><big><b>statement-level</b></big></big></big> trigger - one that happens once per <br /> statement - suchthat, immediately after an insert into a table(which <br /> gets a unique integer value as an ID from a defined sequence,being the <br /> primary key on the table), a new table is created with foreign key <br /> constraint on that uniqueID. <br /></blockquote><br /> hi, <br /> i think what you;re trying to do is wrong - having that many tables <br />simply cannot work properly. <br /> additionally - i think you're misinformed. the kind of action you <br /> would liketo "trigger on" is not "per statement" but "per row". <br /> example: <br /> insert into table x (field) select other_fieldfrom other_table; <br /> if this insert would insert 10 records - "once per statement" trigger <br /> would becalled only once. <br /><br /> but anyway - what you're proposing will lead to many, many problems. <br /> (plus it willnever scale correctly). <br /><br /> depesz <br /></blockquote><br /><br /><pre class="moz-signature" cols="72">-- Email: <a class="moz-txt-link-abbreviated" href="mailto:louis.gonzales@linuxlouis.net">louis.gonzales@linuxlouis.net</a> WebSite: <a class="moz-txt-link-freetext" href="http://www.linuxlouis.net">http://www.linuxlouis.net</a> "Open the pod bay doors HAL!" -2001: A Space Odyssey "Good morning starshine, the Earth says hello." -Willy Wonka </pre>
louis gonzales wrote: > Dear Hubert, > Two things > 1) _*"statement-level" and "row-level" straight from PostgreSQL: > http://www.postgresql.org/docs/8.1/interactive/trigger-datachanges.html*_ > > * > > _*Statement-level triggers*_ follow simple visibility rules: > none of the changes made by a statement are visible to > statement-level triggers that are invoked before the statement, > whereas all modifications are visible to statement-level after > triggers. > > * > > The data change (insertion, update, or deletion) causing the > trigger to fire is naturally /not/ visible to SQL commands > executed in a row-level before trigger, because it hasn't > happened yet. > > * > > However, SQL commands executed in a row-level before trigger > /will/ see the effects of data changes for rows previously > processed in the same outer command. This requires caution, > since the ordering of these change events is not in general > predictable; a SQL command that affects multiple rows may visit > the rows in any order. > > * > > When a _*row-level*_ after trigger is fired, all data changes > made by the outer command are already complete, and are visible > to the invoked trigger function. > > 2) Seeing as you have no idea - not attacking, stating fact - on the > rationale behind the "insert statement-level" to create 1-to-1 table > for each statement-level > insert, I'd say your presumption is unfounded. If you have some > benchmark data, which support why/how to quantify, 50K records in a > single table, all of which would have N number of associated records > in another table, would out perform 50K records in a single table > referencing dedicated 'small' tables, please do share. > > Thanks though. > > hubert depesz lubaczewski wrote: > >> On 3/16/07, louis gonzales <gonzales@linuxlouis.net> wrote: >> >>> I want to write a *statement-level* trigger - one that happens once per >>> statement - such that, immediately after an insert into a table(which >>> gets a unique integer value as an ID from a defined sequence, being the >>> primary key on the table), a new table is created with foreign key >>> constraint on that unique ID. >> >> >> hi, >> i think what you;re trying to do is wrong - having that many tables >> simply cannot work properly. >> additionally - i think you're misinformed. the kind of action you >> would like to "trigger on" is not "per statement" but "per row". >> example: >> insert into table x (field) select other_field from other_table; >> if this insert would insert 10 records - "once per statement" trigger >> would be called only once. >> >> but anyway - what you're proposing will lead to many, many problems. >> (plus it will never scale correctly). >> >> depesz > > > >-- >Email: louis.gonzales@linuxlouis.net >WebSite: http://www.linuxlouis.net >"Open the pod bay doors HAL!" -2001: A Space Odyssey >"Good morning starshine, the Earth says hello." -Willy Wonka > > -- Email: louis.gonzales@linuxlouis.net WebSite: http://www.linuxlouis.net "Open the pod bay doors HAL!" -2001: A Space Odyssey "Good morning starshine, the Earth says hello." -Willy Wonka
louis gonzales escribió: > louis gonzales wrote: > > >2) Seeing as you have no idea - not attacking, stating fact - on the > >rationale behind the "insert statement-level" to create 1-to-1 table > >for each statement-level > >insert, I'd say your presumption is unfounded. This won't work anyway, because a FOR STATEMENT trigger doesn't have access to the row being inserted. Even if that worked, consider this case: insert into foo values ('one', 'row'), ('two', 'rows'); How do you know you need to create two tables and not just one? > > If you have some benchmark data, which support why/how to quantify, > > 50K records in a single table, all of which would have N number of > > associated records in another table, would out perform 50K records > > in a single table referencing dedicated 'small' tables, please do > > share. Indexes occupy less space in the single table case, and there will be 49999 less catalog entries and 49999 less files on disk. Sure, these are implementation details; you can ignore them if you want, but the performance difference is likely to be noticeable. I'll tell you something: the first question I made to these lists, around year 2000 IIRC, was around exactly the same issue you are proposing. A couple of guys suggested the same thing they're telling you now. I didn't listen and went ahead with this silly idea; and in time, I understood what they were all about. After much pain, I redesigned the stuff to use a single table to store all customers' data, instead of a small table for each customer. http://archives.postgresql.org/pgsql-general/2000-11/msg00094.php -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On 3/17/07, louis gonzales <gonzales@linuxlouis.net> wrote: > Statement-level triggers follow simple visibility rules: none of the changes > made by a statement are visible to statement-level triggers that are invoked > before the statement, whereas all modifications are visible to > statement-level after triggers. you are misunderstanding manual. by "change visible" it means - content of the table. not the tuples updates/modified. > The data change (insertion, update, or deletion) causing the trigger to fire > is naturally not visible to SQL commands executed in a row-level before > trigger, because it hasn't happened yet. as i mentioned - you do not understand the text correctly. you have access to change data as NEW.* and OLD.* records. and it works in both "before" and "after" triggers. > However, SQL commands executed in a row-level before trigger will see the > effects of data changes for rows previously processed in the same outer > command. This requires caution, since the ordering of these change events is > not in general predictable; a SQL command that affects multiple rows may > visit the rows in any order. statement-level trigger sees the changes *if* it calls select * from table. but it doesn't mean you have the ability to tell which record were added/modified. as you dont. know. > When a row-level after trigger is fired, all data changes made by the outer > command are already complete, and are visible to the invoked trigger > function. read comment above. > 2) Seeing as you have no idea - not attacking, stating fact - on > the rationale behind the "insert statement-level" to create 1-to-1 table for > each statement-level seeing as you have problems with understanding simple english text - not attacking, stating fact - please check the docs, and do some tests yourself before you will state this kind of "facts". > insert, I'd say your presumption is unfounded. If you have some benchmark > data, which support why/how to quantify, 50K records in a single table, all > of which would have N number of associated records in another table, would > out perform 50K records in a single table referencing dedicated 'small' > tables, please do share. no, i dont have such benchmarks as i think it is obvious that having 50k tables will kill any kind of performance and/or simplicity of writing queries. if you dont belive me - fine, your database, your problems. just test the "visibility" thing in triggers (especially in case of multi-row inserts and updates). best regards, depesz