Postgresql update timestamp trigger in inherited table

Question

I used this example and it worked well when all my tables were in public schema.

But trigger hasn't been working since I separated tables into different schemas and applied inheriting.

Here is example of my structure:

CREATE SCHEMA common;
CREATE SCHEMA video;

CREATE TABLE common.file (
    file_id    SERIAL PRIMARY KEY,
    url        VARCHAR(255)                         NOT NULL,
    mime_type  VARCHAR(31) DEFAULT ''               NOT NULL,
    size       INTEGER                              NOT NULL,
    modified   TIMESTAMP DEFAULT CURRENT_TIMESTAMP  NOT NULL
);

CREATE TABLE video.file (
    width       INTEGER                 NOT NULL,
    height      INTEGER                 NOT NULL,
    local_path  VARCHAR(255) DEFAULT '' NOT NULL
)
INHERITS (common.file);

CREATE FUNCTION common.update_modified()
    RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;

CREATE TRIGGER update_modified 
   BEFORE UPDATE ON common.file 
FOR EACH ROW EXECUTE PROCEDURE common.update_modified();

When I do UPDATE common.file ... or UPDATE video.file ... field common.file.modified doesn't change itself. It seems trigger doesn't run, but I don't understand why.

What should I do to repair the behavior?

The trigger won't fire for updates on video.file, but it works fine for updates on comon.file for me: rextester.com/UVRSL27366 — user330315
– user330315, Commented Sep 25, 2018 at 13:52

Evgenii Karavskii · Accepted Answer · 2018-09-25 14:46:32Z

1

In described issue trigger is set only on common.file, so UPDATE common.file ... doesn't work if row inserted in video.file

Documentation says: INSERT always inserts into exactly the table specified

So trigger should be applied to both common.file and video.file.

-- Creating schemas and tables the same

-- Let function be in public scheme
CREATE FUNCTION update_modified()
    RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;

CREATE TRIGGER update_modified 
   BEFORE UPDATE ON common.file 
FOR EACH ROW EXECUTE PROCEDURE update_modified();

CREATE TRIGGER update_modified 
   BEFORE UPDATE ON video.file 
FOR EACH ROW EXECUTE PROCEDURE update_modified();

In that case when we update rows inserted either in common.file or in video.file corresponding trigger will call.

answered Sep 25, 2018 at 14:46

Evgenii Karavskii

8310 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql update timestamp trigger in inherited table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related