Re: Practical question. - Mailing list pgsql-general

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