Обсуждение: update returns 1, but no changes have been made
I have been trying to update a table -- the update statement returns 1 indicating that one row has been updated, but no changes were made. This is happening on two servers: 1.my development server, running Postgres 7.0.3, RedHat 7.1 2.soon-to-be production server running Postgres 7.1.3, RedHat 6.2 Could I have a deadlock? Something else? What should I be looking for? Thanks. JT ________________________________________ James Thornton, http://jamesthornton.com
Do you perhaps have any triggers or rules on the table in question? If one of those is intercepting your UPDATE then the changes may not actually get to the database... Greg ----- Original Message ----- From: "James Thornton" <thornton@cs.baylor.edu> To: <pgsql-general@postgresql.org.pgsql-sql@postgresql.org> Sent: Saturday, December 01, 2001 4:44 AM Subject: [GENERAL] update returns 1, but no changes have been made > I have been trying to update a table -- the update statement returns 1 > indicating that one row has been updated, but no changes were made. > > This is happening on two servers: > > 1.my development server, running Postgres 7.0.3, RedHat 7.1 > 2.soon-to-be production server running Postgres 7.1.3, RedHat 6.2 > > Could I have a deadlock? Something else? > > What should I be looking for? > > Thanks. > > JT > ________________________________________ > James Thornton, http://jamesthornton.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
The big problem I see is: return old; If you return old from an UPDATE, no changes are made (it takes the old values and puts them into the database rather than your new values). Of course you need to return old from a DELETE (new doesn't exist in a delete trigger), so I would make a conditional: IF TG_OP=''UPDATE'' THEN RETURN NEW; ELSE IF TG_OP=''DELETE'' THEN RETURN OLD; END IF; Hope this helps, Greg ----- Original Message ----- From: "James Thornton" <thornton@cs.baylor.edu> To: "Gregory Wood" <gregw@com-stock.com> Cc: "PostgreSQL-General" <pgsql-general@postgresql.org> Sent: Tuesday, December 04, 2001 6:57 AM Subject: Re: [GENERAL] update returns 1, but no changes have been made > Gregory Wood wrote: > > > > Do you perhaps have any triggers or rules on the table in question? If one > > of those is intercepting your UPDATE then the changes may not actually get > > to the database... > > Yes -- there is a trigger on this table that updates an audit table, and > the audit table *IS* being updated, albeit with the same old values > every time. This is my first time writing triggers in Postgres so that's > probably where the error is. > > Please let me know if you see anything. Here's the table and the > trigger: > > create table wpp_product_question ( > product_question_id int primary key default > nextval('wpp_product_question_seq'::text), > product_type_id int not null references wpp_product_type, > product_faq_id int references wpp_product_faq, > product_question varchar(4000) not null, > sort_order int, > -- notes only the editor will see > editor_notes varchar(4000), > approved_p char(1) check (approved_p in ('f','t')) > default 'f', > last_modified datetime not null, > last_modifying_user int not null references users, > modified_ip_address varchar(50) not null, > unique (product_type_id,product_question) > > ); > > create table wpp_product_question_audit as > select * from wpp_product_question where 1 = 0; > > alter table wpp_product_question_audit add delete_p char(1) check > (delete_p in ('f','t')) default 'f'; > > drop function wpp_product_question_audit_fn(); > create function wpp_product_question_audit_fn() returns opaque > as ' > declare > begin > insert into wpp_product_question_audit ( > product_question_id, product_type_id, product_faq_id, > product_question, > sort_order, editor_notes, approved_p, last_modified, > last_modifying_user, > modified_ip_address > ) values ( > OLD.product_question_id, OLD.product_type_id, > OLD.product_faq_id, OLD.product_question, > OLD.sort_order, OLD.editor_notes, OLD.approved_p, > OLD.last_modified, OLD.last_modifying_user, > OLD.modified_ip_address > ); > > return old; > > end; > ' language 'plpgsql'; > > drop trigger wpp_product_question_audit_tr on wpp_product_question; > create trigger wpp_product_question_audit_tr > before update or delete on wpp_product_question > for each row execute procedure wpp_product_question_audit_fn(); > > Thanks. > > JT >
Gregory Wood wrote: > If you return old from an UPDATE, no changes are made (it takes the old > values and puts them into the database rather than your new values). Of > course you need to return old from a DELETE (new doesn't exist in a delete > trigger)... That was it -- thanks for your help Gregory. JT ________________________________________ James Thornton, http://jamesthornton.com
Gregory Wood wrote: > > Do you perhaps have any triggers or rules on the table in question? If one > of those is intercepting your UPDATE then the changes may not actually get > to the database... Yes -- there is a trigger on this table that updates an audit table, and the audit table *IS* being updated, albeit with the same old values every time. This is my first time writing triggers in Postgres so that's probably where the error is. Please let me know if you see anything. Here's the table and the trigger: create table wpp_product_question ( product_question_id int primary key default nextval('wpp_product_question_seq'::text), product_type_id int not null references wpp_product_type, product_faq_id int references wpp_product_faq, product_question varchar(4000) not null, sort_order int, -- notes only the editor will see editor_notes varchar(4000), approved_p char(1) check (approved_p in ('f','t')) default 'f', last_modified datetime not null, last_modifying_user int not null references users, modified_ip_address varchar(50) not null, unique (product_type_id,product_question) ); create table wpp_product_question_audit as select * from wpp_product_question where 1 = 0; alter table wpp_product_question_audit add delete_p char(1) check (delete_p in ('f','t')) default 'f'; drop function wpp_product_question_audit_fn(); create function wpp_product_question_audit_fn() returns opaque as ' declare begin insert into wpp_product_question_audit ( product_question_id, product_type_id, product_faq_id, product_question, sort_order, editor_notes, approved_p, last_modified, last_modifying_user, modified_ip_address ) values ( OLD.product_question_id, OLD.product_type_id, OLD.product_faq_id, OLD.product_question, OLD.sort_order, OLD.editor_notes, OLD.approved_p, OLD.last_modified, OLD.last_modifying_user, OLD.modified_ip_address ); return old; end; ' language 'plpgsql'; drop trigger wpp_product_question_audit_tr on wpp_product_question; create trigger wpp_product_question_audit_tr before update or delete on wpp_product_question for each row execute procedure wpp_product_question_audit_fn(); Thanks. JT