Circumvent primary key duplication error

Question

Hello,

I have the following script, . sql, to create a table and also create a Trigger.

SET client_encoding TO 'LATIN1';

CREATE OR REPLACE FUNCTION before_insert() RETURNS trigger AS '
  DECLARE
   n integer;
  BEGIN
   IF tg_op = ''INSERT'' THEN
     select count(*) into n
      from files
      where date=new.date and url=new.url;
      IF n > 0 THEN
        RETURN NULL;
      ELSE
        RETURN new;
      END IF;
   END IF;
  END
' LANGUAGE plpgsql;

create table files
    (date TIMESTAMP, 
     url VARCHAR(4000),
     type VARCHAR(100),
     status INTEGER,
     size INTEGER NOT NULL,
     arcname VARCHAR(100) NOT NULL,
     PRIMARY KEY (url,date));

CREATE TRIGGER before_insert_trigger BEFORE INSERT ON files
FOR EACH ROW EXECUTE PROCEDURE before_insert();

From what I understand, every insertion checks whether there is already an equal record, if there is no inserts. It seems to me that Trigger was created so that the insertion did not return the error of repeated primary keys. Since I have to enter a value of 90 million records and Trigger is taking a long time to respond to each insertion, is there any other way around the problem?

Thank you.

Answer 1

The composite primary key (url, date) table files is already able to maintain data integrity, this TRIGGER is completely unnecessary.

However, if the idea is the rapid and massive insertion of data in the table files, I suggest that all the triggers related to table files are temporarily disabled as follows:

ALTER TABLE files DISABLE TRIGGER ALL;

To enable them again:

ALTER TABLE files ENABLE TRIGGER ALL;

If you are sure the integrity of the data you want to insert in the table files do not violate your primary key, you can speed up the data insertion process by removing this key:

ALTER TABLE files DROP CONSTRAINT files_pkey;

After the operations of INSERT you can recreate it as follows:

ALTER TABLE files ADD CONSTRAINT files_pkey PRIMARY KEY(url, date);