[Info-ingres] Why I love table procedures!

[Martin Bowes]

Hi All,

Gloat mode engaged....

I had a table which was very big, it had more than the maximum integer4 (2+billion) rows and had to be partitioned 32 ways just to store the data.

It looked like this:
Run_id integer4
Pid    integer4
Gid    integer2
Status integer1
For any run_id there may be as many as (and typically are as many as) 500,000 rows.

Then I got told, 'What? You're storing that stuff! We don't need it.' ... but after examining the code for a while I found that they did need it during a particular operational phase and then not until another process which occurs once every 6months.

So I couldn't remove the data. But the good news was that those processes accessed the data by run_id only.

So I made an archive table which looked like this:
Run_id   integer4
All_data long varchar
Tinsert  ingresdate

The all_data field was composed of all the old rows pid,gid,status data separated by commas and each of the original rows data separated by colons.
Eg. 1000780,-1,0:1000800,-1,0:1000889,-1,0:1000959,-1,0...

So how to extract the data when actually needed it? Enter a table procedure and the similar to regular expression pattern matching.




-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20181122/f88441d9/attachment.html>