Re: Practical question.

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(at)linuxlouis(dot)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(dot)gonzales(at)linuxlouis(dot)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(dot)gonzales(at)linuxlouis(dot)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