INSERT ... SELECT ... FOR SHARED?

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/insert on 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(at)mielke(dot)cc>