2

I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels & meta-data). I am trying to create a PostgreSQL trigger to update the sensor data as they are inserted into SENSORS. Each row of sensor data will be updated with the corresponding label name from LABELS. Unable to get the row update to work. Sample shown below:

Sample Dataset

CREATE TABLE sensor (
   datavalue integer, 
   created timestamp,
   label_id integer,
   label_name text);

CREATE TABLE labels (
   id integer,
   label_id integer,
   label_name text);

Insert into labels values (1, 215, 'Home'), (2, 216, 'Shop'), (3, 217, 'Flat'), (4, 218, 'Street');
Insert into sensor values (67, '2020-09-02 08:40:07.354', 215),(67, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

Trigger Function

CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS 
$func$
BEGIN
UPDATE sensor 
    SET label_name = b.label_name 
    from labels b
    where new.label_id = b.label_id;
     RETURN NEW; 
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
AFTER INSERT OR UPDATE ON sensor
FOR EACH ROW EXECUTE PROCEDURE update_name();

Test row insertion

Insert into sensor values (78, '2020-09-02 08:40:07.354', 215),(77, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

I have a very large number of rows flowing in, and would like to update each row as it is inserted.

Any assistance greatly appreciated. Thanks!

3
  • Hi. Why exactly do you want to do this in triggers? Seems like overkill as you could easily fetch such combination using kind of SELECT * FROM sensor s INNER JOIN labels b ON b.label_id = s.label_id or similar. Alternatively you could do such update in batch to update them masivelly. Feb 2, 2021 at 19:53
  • Thanks for your suggestion. Your suggestion is what we are doing now. However, the SENSOR table will eventually have 100s of millions of rows. Thus, the JOIN option we are using is becoming slower as we add more data. That's why I was thinking the UPDATE option might reduce query time.
    – tango91a
    Feb 2, 2021 at 20:03
  • Sounds like an X-Y problem to me. You should ask how to improve performance of your query instead.
    – mustaccio
    Feb 2, 2021 at 20:08

2 Answers 2

1

Most likely you're going in wrong direction but I have only gut feelings for that.

But, you can play with triggers - maybe it will do a work. However it seems that you will have massive updates in each trigger call because you are updating all rows from sensor with new label, which seems just wrong.

As I'm not sure about your specific needs, I will just push you in most proper way (I hope) with these changes:


CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO sensor(datavalue, created, label_id, label_name)
        SELECT NEW.datavalue, NEW.created, NEW.label_id, b.label_name
        from labels b
        where new.label_id = b.label_id;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE sensor
        SET label_name = b.label_name
        from labels b
        where new.label_id = b.label_id;

    END IF;
    RETURN NULL;

END
$$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
BEFORE INSERT OR UPDATE ON sensor
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE update_name();

Here, you do these changes instead (before) insert or update. Insert will be proper, update needs to be adjusted or removed as from your description it seems like you will be making only inserts here.

And please note that there is limiter as proposed here https://dba.stackexchange.com/a/103661/213360.

5
  • Thanks for your input. Very much appreciated. Yes, we will making only inserts. The inserts are done directly from the sensor database, so perhaps if we want to continue on this path, we may have to intercept the data feed before the insert is completed.
    – tango91a
    Feb 2, 2021 at 20:48
  • So in such case you could change that to before insert only and in function to remove that IF TG_OP = ..., ELSIF TG_OP = 'UPDATE' THEN ... parts and leave only ` INSERT INTO sensor...` Feb 2, 2021 at 21:06
  • How many labels are you going to need? Maybe you could put some mapping directly in function definition with would allow you to avoid fetching that from labels table for each row from sensors. Feb 2, 2021 at 21:07
  • There are about 9000 labels. We would also have about 500 million rows of sensor data each year. We archive the data after one year.
    – tango91a
    Feb 2, 2021 at 21:11
  • So definitely no way to put such mapping into function. Just make sure you have nice index on labels.label_id, but I bet you already have one. Feb 2, 2021 at 21:18
0

I have to agree with Adam Tokarski's answer that the usage pattern looks a bit like it's going in an awkward direction. For simplicity, would it not make sense to treat the label as if it were any other measurement that may be updated? i.e.:

CREATE TABLE sensor (
  sensor_id integer,
  label text,
  datavalue integer,
  created timestamp);

INSERT INTO sensor VALUES 
  (215, 'Home', 61, '2020-09-02 08:40:07.354'),
  (216, 'Shop', 61, '2020-09-02 08:41:07.354'),
  (217, 'Flat', 61, '2020-09-02 08:41:07.354');

This way, you're only doing one insert, not worrying about updating, and the integer for sensor ID is constant and is your identifier.

In general, if you're struggling with performance, I suggest using a time series database for this - I write docs for QuestDB which is designed for this kind of work. A bonus optimisation you would get on the labels if they are enum-like, is using the symbol type that's faster for queries than strings as they're stored as ints internally. You just specify the label as symbol type instead of string.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.