Role & User - Inheritance?

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

"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

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
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&quot;&gt;http://www.postgresql.org/docs/8.1/interactive/trigger-datachanges.html&lt;/a&gt;&lt;/b&gt;&lt;/u&gt;&lt;br&gt;
<ul>
<li>
<p><u><b>Statement-level triggers</b></u> 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. </p>
</li>
<li>
<p>The data change (insertion, update, or deletion) causing the
trigger to fire is naturally <span class="emphasis"><i class="EMPHASIS">not</i></span>
visible to SQL commands executed in a row-level before trigger, because
it hasn't happened yet. </p>
</li>
<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 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. </p>
</li>
<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 visible
to the invoked trigger function. </p>
</li>
</ul>
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 <br>
insert, I'd say your presumption is unfounded.&nbsp; 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.<br>
<br>
Thanks though.<br>
<br>
hubert depesz lubaczewski wrote:
<blockquote
cite="mid9e4684ce0703162139r7bc64180ga47e854af9678967@mail.gmail.com"
type="cite">On 3/16/07, louis gonzales <a class="moz-txt-link-rfc2396E" href="mailto:gonzales@linuxlouis.net">&lt;gonzales@linuxlouis.net&gt;</a>
wrote:
<br>
<blockquote type="cite">I want to write a <big><big><big><b>statement-level</b></big></big></big>
trigger - one that happens once per
<br>
statement - such that, 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 unique ID.
<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 like to "trigger on" is not "per statement" but "per row".
<br>
example:
<br>
insert into table x (field) select other_field from other_table;
<br>
if this insert would insert 10 records - "once per statement" trigger
<br>
would be called only once.
<br>
<br>
but anyway - what you're proposing will lead to many, many problems.
<br>
(plus it will never 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&quot;&gt;http://www.linuxlouis.net&lt;/a&gt;
"Open the pod bay doors HAL!" -2001: A Space Odyssey
"Good morning starshine, the Earth says hello." -Willy Wonka
</pre>
</body>
</html>

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