1

I have a table country that is referenced by many tables like so:

db=# \d client
                          Table "public.client"
      Column              |            Type             | Modifiers 
--------------------------+-----------------------------+-----------
 id                       | character varying(32)       | not null
 name                     | character varying(64)       | 
 country_id               | character varying(32)       | not null
 insert_date              | timestamp without time zone | 
 update_date              | timestamp without time zone | 
Foreign-key constraints:
   "client_country_fk" FOREIGN KEY (country_id) REFERENCES country(id)
Triggers:
    update_client_insert_date BEFORE INSERT ON client FOR EACH ROW EXECUTE PROCEDURE update_insert_column()
    update_client_update_date BEFORE UPDATE ON client FOR EACH ROW EXECUTE PROCEDURE update_update_column()

The referenced table:

db=# \d country
                              Table "public.country"
       Column             |              Type           |  Modifiers   
--------------------------+-----------------------------+--------------
 id                       | character varying(32)       | not null
 name                     | character varying(256)      | 
 insert_date              | timestamp without time zone | 
 update_date              | timestamp without time zone | 
 Indexes:
"country_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "client" CONSTRAINT "client_country_fk" FOREIGN KEY (country_id) REFERENCES country(id)
Triggers:
    update_client_insert_date BEFORE INSERT ON client FOR EACH ROW EXECUTE PROCEDURE update_insert_column()
    update_client_update_date BEFORE UPDATE ON client FOR EACH ROW EXECUTE PROCEDURE update_update_column()

And the trigger :

db=# SELECT proname, prosrc FROM pg_proc WHERE proname='update_update_column';
        proname         |                                  prosrc                                  
------------------------+--------------------------------------------------------------------------
 update_update_column |     BEGIN       NEW.update_date = now() ;       RETURN NEW ;    END ; 

I discovered by chance that these foreign keys were violated when running an UPDATE command on one of the tables:

UPDATE client SET name = 'foo';

ERROR: insert or update on table "client" violates foreign key constraint "client_country_fk"
DETAIL: Key (country_id)=(someRandomId) is not present in table "country".

I deleted all rows referencing someRandomId and I was able to run the UPDATE command.

How can my database be in an inconsistent state?

Can I run all database constraints manually to make sure it is now consistent?

I suspect the issue has to do something with the DEFERRABLE setting. But according to the doc, it's not the default.

According to the doc, there's a SET CONSTRAINTS; setting but it only applies to a specific transaction.

  • 2
    It's hard to imagine that updating the name column would violate a foreign key using the country_id column. Is that really the complete update statement you are running? Are there any triggers on the table? – a_horse_with_no_name Feb 18 '16 at 18:29
  • Yes, the UPDATE command was only updating one column which was not country_id. Could Postgresql run all column constraints when inserting/updating a row? – pyb Feb 18 '16 at 21:32
  • 1
    No, it will only check the constraints for the columns you change (because those that did not change are already validated). What about triggers? Can you please edit your question and add the complete create table statement for both tables and the foreign key definitions – a_horse_with_no_name Feb 18 '16 at 21:34
  • Yes, there is a trigger on UPDATE which is updating a date lastModified column: BEGIN NEW.lastModified = now() ; RETURN NEW ; END ;. Could this trigger the checks on other columns? – pyb Feb 18 '16 at 21:37
  • @a_horse_with_no_name will do, thanks for your help and suggestions. – pyb Feb 18 '16 at 21:37

Your Answer

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

Browse other questions tagged or ask your own question.