Hi,
I want to insert data as fast as possible into the database, the
following is a rough idea of what can happen to the data as it is
inserted:
The Query to insert the data will look something like this:
#SELECT Insert_Data(data1, data2, data3, data4);
The following should happen:
1. data4 will be transformed and a new field data5 will be created
2. All the data should be inserted into a table [Main_Table]
3. All the data should be inserted into a table [Second_Table]
4. Rows in [Main_Table] older than x seconds should be deleted
5. Data2 and Data3 should be inserted into a table [Partial_Table]
Notes:
[Second_Table] can be very big with a few indexes and inserts to it
can be long compared to inserts into table [Main_Table].
This can all be done in the stored procedure (function) I know. It can
also be done using triggers on an insert statement like
#INSERT INTO [Main_Table] VALUES (data1, data2, data3, data4);
Either one or many triggers can do the above.
And recently I've read up on rules, and they can also do the the
transformation in the INSERT (if I understand rules correctly).
What is the best way to do this, with reference to speed and logic?
Secondly:
As far as I know the query does not return untill all the triggers
were triggered and executed completely. Is there a way to do steps 3 -
5 in the background so that the query can return after step 2 so that
the application inserting the data does not have to wait for steps 3,
4 and 5 to complete before it can continue. The options are: an
external application monitors [Main_Table] and does steps 3, 4, 5 at
regular intervals for all rows in [Main_Table] or to have a buffer in
the application inserting the data. Both of these options require
development outside of the database, is it possible to do this inside
the Postgres Database?
Thank you
--
Carel Combrink
s25291930@tuks.co.za
This message and attachments are subject to a disclaimer. Please refer
to www.it.up.ac.za/documentation/governance/disclaimer/ for full
details. / Hierdie boodskap en aanhangsels is aan 'n vrywaringsklousule
onderhewig. Volledige besonderhede is by
www.it.up.ac.za/documentation/governance/disclaimer/ beskikbaar.
