5

I have a table who's purpose is to store the application's various images. It is defined this way :

CREATE TABLE images
(
  id lo NOT NULL,
  name character varying(1024) NOT NULL,
  type character varying(32) NOT NULL,
  dimension point,
  last_modified timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT "PK_images" PRIMARY KEY (id)
)

Some other tables have foreign keys referencing images.id with constraints that an image should not be deleted if it is referenced somewhere (i.e. the app is responsible to properly delete the reference first);

...
CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
   REFERENCES images (id) MATCH SIMPLE
   ON UPDATE CASCADE ON DELETE RESTRICT
...

Since many table rows may reference the same image, I need to automatically cleanup images that no longer have any reference.

My first idea was to implement the application to delete all foreign key references, then remove all images matching the values of these deleted foreign keys. To do so, I'd create a trigger on the table images, before delete, that could "swallow" all constraint exception if a query such as only "orphaned" images would be deleted and the others would be silently ignored. I'm just not sure how to properly do that.

If that idea is not good, what could be other (if not better) alternatives?

Basically, if I'm executing a query like

DELETE FROM images WHERE id IN (101, 102, 103, 104, 105)

and images.id's 102 and 104 have constraints that prevents them to be deleted, I'd like all other images to still be deleted. The DELETE query should be simple as the one above.

Thank you.

  • 1
    You mean you don't want to add explicit conditions to your DELETE query to check all referencing tables? Why is that a problem? – Andriy M Jul 21 '15 at 19:07
  • Because the app is modular and I don't want to add spaghetti-like references across modules. – Yanick Rochon Jul 21 '15 at 19:08
  • Well, in SQL Server I would create a stored procedure for deleting images, encapsulating the necessary logic in the procedure. My applications would then use the procedure and only need to pass the IDs to it. I don't know if PostgreSQL stored functions can be used in the same way. An alternative (again, in SQL Server) could be to create a view returning only unused (not referenced by any table) images and delete from that view. Again, the check logic would be incorporated into the view, an application would just delete from the view directly, and again I don't know if PostgreSQL can do that. – Andriy M Jul 21 '15 at 19:23
  • @AndriyM the problem is that, in order to know if any image is being referenced, you have to 1) know each and every table to reference the table images, or 2) construct a procedure that would reverse engineer the schema. Like I said, I am using only good design patterns (i.e. no anti-patterns) in this applicaiton, and such view as you describe, to me, is worst than handling contraint errors in a trigger. The suggestion you make is an anti-pattern as such view (or proc) would be required to know too much, and would require a lot of maintenance when adding / removing features. – Yanick Rochon Jul 21 '15 at 19:35
  • 1
    You seem to be looking for a dynamic solution then. I don't know if it's a good idea to go dynamic in a trigger in PostgreSQL but I would consider that an anti-pattern in SQL Server. I don't see a problem with a view or procedure (function) "knowing too much" – after all, it would be an object in the same database. If I add or drop a referencing table, then yes, I need to change the view or proc accordingly – I consider that normal practice, YMMV. – Andriy M Jul 21 '15 at 19:41
3

I was able to make this work with the below setup Process: 1. Loop through the pg_constraint table and understand the constraints defined on the table that has to delete a record 2. Before deleting a record verify no reference record exists in any of the dependent table

Detailed implementation can be found below. Let me know your thoughts on this.

            #1: DDL

            CREATE TABLE core.images
            (
                id serial NOT NULL,
                name character varying(1024) NOT NULL
                CONSTRAINT "PK_images" PRIMARY KEY (id)
            );

            create table core.app_images
            (
            id serial,
            image_id int,
            CONSTRAINT "FK_foo_image" FOREIGN KEY (image_id)
                 REFERENCES core.images (id) MATCH SIMPLE
                 ON UPDATE CASCADE ON DELETE RESTRICT
            );

            create table core.app_images1
            (
            id serial,
            image_id int,
            CONSTRAINT "FK_foo_image1" FOREIGN KEY (image_id)
                 REFERENCES core.images (id) MATCH SIMPLE
                 ON UPDATE CASCADE ON DELETE RESTRICT
            )

            #2: Funciton to check ref data integrity

            CREATE OR REPLACE FUNCTION core.check_ref_exists (
                p_schema TEXT,
                p_table TEXT,
                p_column TEXT,
                p_value TEXT
            ) 
            RETURNS BOOLEAN 
            LANGUAGE plpgsql 
            AS $$ 
            DECLARE
                r record ; 
                v_sql TEXT ; 
                v_return BOOLEAN DEFAULT FALSE ;
            BEGIN
                FOR r IN (
                    SELECT
                        pn.nspname s_name,
                        r_tab.relname AS t_name,
                        pa.attname AS c_name
                    FROM
                        pg_constraint pc
                    INNER JOIN pg_class tab ON tab.oid = pc.confrelid
                    INNER JOIN pg_class r_tab ON r_tab.oid = pc.conrelid
                    INNER JOIN pg_namespace pn ON pn.oid = r_tab.relnamespace
                    INNER JOIN pg_namespace pn_drv ON pn_drv.oid = tab.relnamespace
                    INNER JOIN pg_attribute pa ON (
                        pa.attrelid = pc.conrelid
                        AND pa.attnum = ANY (pc.conkey)
                    )
                    INNER JOIN pg_attribute pa_d ON (
                        pa_d.attrelid = pc.confrelid
                        AND pa_d.attnum = ANY (pc.confkey)
                    )
                    WHERE
                        contype = 'f'
                    AND tab.relname = p_table
                    AND pn_drv.nspname = p_schema
                    AND pa_d.attname = p_column
                ) loop 

                    v_sql := 'SELECT TRUE FROM ' || p_schema || '.' || p_table || ' s 
                        WHERE s. ID = ' || p_value || ' 
                        AND EXISTS ( 
                            SELECT 1 FROM ' || r.s_name || '.' || r.t_name || ' d WHERE s.' || p_column || ' = d.' || r.c_name || ')' ; EXECUTE v_sql INTO v_return ; 
                    v_return := COALESCE (v_return, FALSE) ;
                    IF v_return = TRUE THEN
                        RETURN v_return ;
                    END IF ;

                END loop ; 
                RETURN v_return ;
            END ; $$

            #3: Trigger Function
            CREATE OR REPLACE FUNCTION core.images_tr_func() 
            RETURNS TRIGGER AS 
            $BODY$ 
            DECLARE 
                v_ref_exists boolean;
            BEGIN 
                select core.check_ref_exists (
                    p_schema := 'core',
                    p_table :='images',
                    p_column :='id',
                    p_value := old.id::text
                ) into v_ref_exists;

                if v_ref_exists then 
                    return null;
                else
                    return old;
                end if;
            EXCEPTION 
                    WHEN OTHERS THEN
                            RAISE EXCEPTION 'SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
                            RETURN NULL;
            END;
            $BODY$ 
            LANGUAGE plpgsql;

            #4: Trigger
            DROP TRIGGER if exists images_tr ON core.images;

            CREATE TRIGGER images_tr BEFORE DELETE
                 ON core.images FOR EACH ROW 
                 EXECUTE PROCEDURE core.images_tr_func();

            #5: Sample Data
            insert into  core.images(name) values('image1'),('image2'),('image3'),('image4');
            select * from  core.images;

            insert into core.app_images(image_id) values
            (1),(1),(2)

            select * from core.app_images

            insert into core.app_images1(image_id) values
            (2),(3),(3)

            select * from core.app_images1

            #6: Testing
            delete from core.images where id = 1; --Nothing happened as it is still referred
            delete from core.images where id = 4; --Deleted as it is an orphan record
  • @Yanick Rochon Let me know if this helps you – Aneesh Mon N Jul 22 '15 at 10:07
2

My current solution involve creating a trigger on each table referencing images, like so :

CREATE FUNCTION public.foo_images_cleanup() RETURNS trigger AS $$
BEGIN
  BEGIN
    DELETE FROM images WHERE id = OLD.image_id;
  EXCEPTION WHEN OTHERS THEN
    NULL;
  END;

  IF (TG_OP = 'DELETE') THEN
     RETURN OLD;
  ELSE
     RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER foo_images_cleanup AFTER UPDATE OR DELETE ON public.foo_images
  FOR EACH ROW
  EXECUTE PROCEDURE public.foo_images_cleanup();

I am not aiming at performance as there should not be more than a few dozen images (at the very most) being cleaned up at any time.

0

I'd rather keep it very simple -if you're ok not delete rows which have references why just not have a loop on application side with DELETE FROM images WHERE id = :param1 and ignoring error ?

It's not gonna be less performant than trigger solution (which in my inderstanding should be INSTEAD OF DELETE trigger , and I'm not sure if you can have such a trigger on a table in Postgresql ), but much simplier and maintainable .

Side note : ON UPDATE CACSCADE appears very arguable ; normally you never change value of primary key.

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.