PostgreSQL update doesn't work

Question

I am working on a PostgreSQL 9.5.3 db which I haven't setup myself. The issue that I have is that a simple update of the form: UPDATE <table> SET <col> = <val> WHERE <col> = <old_val>; does not apply the change. I mention that the pgAdmin output is:

UPDATE 1

Query returned successfully in .. msec

What could be the cause? Could it be explained by some set constraints?

Thanks

Answer 1

It could be many resons for real update happening and yet rows values not changed: RULES, triggers, wrong schema from search_path and probably many more if you sit and think seriously. You can update other table, other row and set previous value instead of new with bothe RULES and triggers. Thus a full DDL for table and its "dependants" is needed. Here is probably the simplest example with trigger:

f=# create table ut(i int);
CREATE TABLE
f=# insert into ut select 1;
INSERT 0 1
f=# create or replace function tf() returns trigger as
$$
begin
if true then NEW.i=OLD.i; end if;
return NEW;
end;
$$ language plpgsql;
CREATE FUNCTION
f=# create trigger tn before update on ut for each row execute procedure tf();
CREATE TRIGGER
f=# update ut set i = 2;
UPDATE 1
f=# select * from ut;
 i
---
 1
(1 row)

so you see

f=# update ut set i = 2;
UPDATE 1

and yet data not changed.

Also after you commit changes the value could be simply updated by another transaction, thus you select value, updated after you...