From: | Mark Mielke <mark(at)mark(dot)mielke(dot)cc> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | INSERT ... SELECT ... FOR SHARED? |
Date: | 2008-04-20 16:59:35 |
Message-ID: | 480B7677.3010306@mark.mielke.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2008-04-20 17:08:38 | Re: pgkill on win32 |
Previous Message | Guillaume Smet | 2008-04-20 16:32:11 | Re: -DCLOBBER_CACHE_ALWAYS build takes far longer than before |