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 <[EMAIL PROTECTED]>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
