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

@a_horse_with_no_name When I do a SELECT afterwards, the value stayed the same. — Murukan
– Murukan, Commented Oct 12, 2017 at 6:32
Without more information this is impossible to answer. Could be you are connected to a different database when you check the update or you have a trigger that reverts the assignment of the value or some other process/transaction reverted your change. But if Postgres returns that one row update, then one row was updated. — user330315
– user330315, Commented Oct 12, 2017 at 7:03
@VaoTsun This was it! I found the update trigger that matched on the record in question. Thank you all for the guidance! — Murukan
– Murukan, Commented Oct 12, 2017 at 7:17
please share the definition of update function, so I could answer and other users could learn from our experience — Vao Tsun
– Vao Tsun, Commented Oct 12, 2017 at 7:18

Vao Tsun · Accepted Answer · 2017-10-12 07:30:08Z

2

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...

answered Oct 12, 2017 at 7:30

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

1 Comment

Ben Wilson Over a year ago

This. Check your triggers with something like: SELECT * FROM information_schema.triggers You may be accidentally automatically updating your row right after you manually update it.

Kaan · Accepted Answer · 2018-09-17 10:53:31Z

1

Update privilege requires select as well. Maybe that was the issue or will be an issue for someone else.

In practice, any nontrivial UPDATE command will require SELECT privilege as well, since it must reference table columns to determine which rows to update, and/or to compute new values for columns

For more info check the doc : https://www.postgresql.org/docs/current/static/sql-grant.html

answered Sep 17, 2018 at 10:53

Kaan

3991 gold badge3 silver badges8 bronze badges

Collectives™ on Stack Overflow

PostgreSQL update doesn't work

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related