Обсуждение: 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/

"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