Re: Practical question. - Mailing list pgsql-general

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>