Changes between Version 7 and Version 8 of drmsSubscribe

Timestamp:

02/18/15 10:16:58 (9 years ago)

Author:

joe

Comment:

--

drmsSubscribe

@@ -110 +110 @@
 If you miss more than about a month's worth of slony updates, it will be necessary to unsubscribe and resubscribe to regenerate your data tables. Obviously, this is to be avoided.
 
+
+== VSO Specific Triggers ==
+
+There are two triggers that may be installed after the subscription process
+
+=== Automatic data mirroring ===
+
+Sites that are using the JMD to retrieve data will have a table in their database named `sunum_queue` :
+
+{{{
+sdac_drms=# \d public.sunum_queue;
+                                     Table "public.sunum_queue"
+    Column    |            Type             |                       Modifiers
+--------------+-----------------------------+-------------------------------------------------------
+ queue_id     | bigint                      | not null default nextval('sunum_queue_key'::regclass)
+ sunum        | bigint                      | not null
+ series_name  | text                        | not null
+ timestamp    | timestamp without time zone | default now()
+ recnum       | bigint                      |
+ request_type | character varying(10)       | not null default 'MIRROR'::character varying
+ priority     | integer                     | not null default 0
+Indexes:
+    "sunum_queue_pkey" PRIMARY KEY, btree (queue_id)
+}}}
+
+The JMD will check this table for entries of files that it should retrieve.    We use a trigger on the table under slony control to write records into the `sunum_queue` table.  There is a script in the CVS tree to build the appropriate triggers: `vso/DataProviders/JSOC/db_triggers/sunum_queue_trigger_sampled.pl`.  It takes the following arguments:
+{{{
+        Usage : ./sunum_queue_trigger_sampled.pl <series> [<instrument> [<cadence> [<max_age>]]]
+
+        series     : series name (with namespace)
+        instrument : hmi or aia
+        cadence    : integer, in seconds (should be a multiple of 72 for aia, 45 for hmi)
+        max_age    : maximum age, in days, of records to queue (default 14 days)
+}}}
+
+If given with just a series name, it will emit the SQL commands to create a trigger that retrieves all updates for observations within the last 14 days.  Specifying a cadence will allow you to retrieve observations at lower than the full data rate.  Note that only SQL is emitted to STDOUT, so that you can redirect this cleanly to a file.  Warnings and other messages are emitted to STDERR.
+
+{{{
+[oneiros@sdo3 db_triggers]$ ./sunum_queue_trigger_sampled.pl hmi.v_720s
+
+-- You only need to issue this once.  In theory, it'd have already been
+-- done as part of the setup for DRMS & slony:
+--
+-- CREATE LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION hmi_v_720s_fc() returns TRIGGER as $hmi_v_720s_trg$
+BEGIN
+  IF (TG_OP='INSERT' AND new.sunum > 0 and NEW.date__obs <> 'NaN') THEN
+    IF ( EXTRACT('epoch' FROM NOW()) - NEW.date__obs < 222134367 ) THEN
+      INSERT INTO sunum_queue (sunum,recnum,series_name) VALUES (new.sunum,NEW.recnum,'hmi.v_720s');
+    END IF;
+
+  END IF;
+RETURN NEW;
+END
+$hmi_v_720s_trg$ LANGUAGE plpgsql;
+
+
+-- the drop trigger will fail the first time, but there's no 'OR REPLACE' on
+-- triggers, so this is so it'll work after the first time.
+-- (or just don't mess with the trigger; replacing the function is enough,
+-- as it's just linked by name)
+
+DROP TRIGGER hmi_v_720s_trg ON hmi.v_720s;
+CREATE TRIGGER hmi_v_720s_trg AFTER INSERT ON hmi.v_720s
+    FOR EACH ROW EXECUTE PROCEDURE hmi_v_720s_fc();
+}}}
+
+=== VSO Shadow Tables ===
+
+(note that most sites do not need this trigger.  Only SDAC and NSO currently incur this extra overhead)
+
+To speed searching for data, the VSO generates a materialized view that we refer to as the 'shadow tables'.  These tables are keep up-to-date with the DRMS series table via a trigger.  There are two scripts to build the SQL commands necessary, both in CVS:
+`vso/DataProviders/JSOC/db_triggers/shadow_aia_template.pl` and `vso/DataProviders/JSOC/db_triggers/shadow_hmi_template.pl`.  They take a series name as an argument.  Note that only SQL is emitted to STDOUT, so that you can redirect this cleanly to a file.  Warnings and other messages are emitted to STDERR.
+
+{{{
+[oneiros@sdo3 db_triggers]$ perl shadow_hmi_template.pl hmi.v_720s
+SERIES : hmi.v_720s
+SHADOW : hmi__v_720s
+
+DROP TABLE vso.hmi__v_720s;
+
+CREATE TABLE vso.hmi__v_720s (
+    date_obs    timestamp       NOT NULL,
+    date        timestamp       NOT NULL,
+    t_rec_index bigint          UNIQUE NOT NULL,
+    recnum      bigint          UNIQUE NOT NULL,
+    sunum       bigint          NOT NULL,
+    slotnum     integer         NOT NULL,
+    clusterid   bigint          NOT NULL,
+    fileid      varchar(255)    UNIQUE NOT NULL,
+
+    PRIMARY KEY (t_rec_index)
+);
+
+CREATE INDEX vso_hmi__v_720s_cluster  ON vso.hmi__v_720s ( clusterid );
+CREATE INDEX vso_hmi__v_720s_date_obs ON vso.hmi__v_720s ( date_obs );
+CREATE INDEX vso_hmi__v_720s_date     ON vso.hmi__v_720s ( date );
+
+GRANT SELECT ON vso.hmi__v_720s TO vso;
+
+
+CREATE OR REPLACE FUNCTION proc_update_vso_hmi__v_720s ()
+RETURNS TRIGGER AS $proc_update_vso_hmi__v_720s$
+    DECLARE
+        record_date_obs   timestamp;
+        record_date       timestamp;
+        record_rec_index  bigint;
+        record_recnum     bigint;
+        record_sunum      bigint;
+        record_slotnum    integer;
+        record_fileid     varchar(255);
+        record_clusterid  bigint;
+        deleted_record    RECORD;
+    BEGIN
+        -- this seems out of order, I know.
+        -- we look at the new record, and try to figure out if it's invalid
+        -- if it is, we still need to try to clean up the old record.
+        record_rec_index = NEW.t_rec_index;
+        record_sunum     = NEW.sunum;
+        -- we dont do journaling -- but we *DO* want to figure out
+        -- when SUs are replaced
+        DELETE FROM vso.hmi__v_720s
+            WHERE t_rec_index = record_rec_index
+            RETURNING sunum
+            INTO deleted_record;
+        IF FOUND THEN
+            IF deleted_record.sunum <> NEW.sunum THEN
+            -- don't flag if it's still the same sunum (headers updated?)
+                INSERT INTO public.sunum_replaced (
+                    old_sunum, new_sunum
+                ) VALUES (
+                    deleted_record.sunum, NEW.sunum
+                );
+            END IF;
+        END IF;
+--
+        -- ocassionally, there's a missing record ... don't process those
+        IF 'NaN' = NEW.date__obs  THEN
+            RETURN NEW;
+        END IF;
+--
+        record_date_obs  = dynamical_to_unix(NEW.date__obs);
+        record_date      = dynamical_to_unix(NEW.date);
+        record_recnum    = NEW.recnum;
+        record_slotnum   = NEW.slotnum;
+
+        record_fileid    = 'hmi__v_720s:'||record_rec_index||':'||record_rec_index;
+        record_clusterid = ROUND( record_rec_index / 40 );
+--
+        -- I couldve done this as INSERT INTO ... SELECT NEW.*
+        -- but then theres that nasty concat and such.
+        INSERT into vso.hmi__v_720s (
+            date_obs, date, t_rec_index, recnum, sunum, slotnum, clusterid, fileid
+        ) values (
+            record_date_obs, record_date, record_rec_index, record_recnum,
+            record_sunum, record_slotnum, record_clusterid, record_fileid
+        );
+--
+        RETURN NEW;
+--
+    EXCEPTION
+      WHEN OTHERS THEN
+          RAISE WARNING 'vso.hmi__v_720s : % : % : %', SQLSTATE, NEW.recnum, SQLERRM;
+          RETURN NEW;
+    END;
+$proc_update_vso_hmi__v_720s$ LANGUAGE 'plpgsql';
+
+
+DROP TRIGGER trig_update_vso_hmi__v_720s ON hmi.v_720s;
+
+CREATE TRIGGER trig_update_vso_hmi__v_720s
+    BEFORE INSERT OR UPDATE
+    ON hmi.v_720s
+    FOR EACH ROW
+    EXECUTE PROCEDURE proc_update_vso_hmi__v_720s();
+
+
+-- trigger it to run for the data already in the table
+UPDATE hmi.v_720s SET recnum=recnum;
+}}}
+