This is similar to a previous question I had asked about INSERT ...
DELETE ...
To be "safe", to archive an existing row, and replace with a new row, I
believe on must do:
begin; row := select ... from XXX where ... for update; insert into XXX_archived values (row); ... update or
delete/inserton XXX ... commit;
I am trying to lock the row for update to prevent a concurrent process
from trying archive the row at the same time.
I tried the following and received an odd error:
begin; insert into XXX_archived select ... from XXX where ... for update; ... update or delete/insert on XXX ...
commit;
First, if the table doesn't match any rows:
# insert into product_image_archived select * from product_image where
itemno = 'XXXXXX' for update;
INSERT 0 0
Second, if the table does match a row:
# insert into product_image values ('XXXXXX', 'somepath');
INSERT 0 1
# insert into product_image_archived select * from product_image where
itemno = 'XXXXXX' for update;
ERROR: cannot extract system attribute from virtual tuple
Is this supposed to work? Is it an easy thing to fix?
The only difference between the product_image and product_image_archived
tables, is that product_image has a primary key constraint on the
product identifier.
I can do it the original way - it just seemed "odd".
Cheers,
mark
--
Mark Mielke <mark@mielke.cc>