Обсуждение: 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/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>

Mark Mielke <mark@mark.mielke.cc> writes:
> # insert into product_image_archived select * from product_image where 
> itemno = 'XXXXXX' for update;
> ERROR:  cannot extract system attribute from virtual tuple

Hm, on an assert-enabled build this actually crashes :-(.  It looks like
I broke the specific case here:

http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/execMain.c.diff?r1=1.280;r2=1.281;f=h

as a result of thinking (in a moment of brain fade) that FOR UPDATE
could only occur within a SELECT.  It works in 8.2.

That's fairly easily fixed --- just move the loop that initializes
ctidAttNo out of the operation == CMD_SELECT case --- but looking around
execMain.c I notice that this isn't the only such assumption.
ExecutePlan() thinks that UPDATE and DELETE can't have any FOR UPDATE
clauses.  That hasn't been true for awhile; consider

update t1 set ... from (select ... from t2 for update) ss where ...;

CVS HEAD will take this, and silently not do the requested locks :-(

While there are a lot of related cases that we can't currently handle,
this one would work if execMain weren't just blindly ignoring the
possibility.  I think we need to rejigger the tests in execMain so
that it either honors the rowmarks or throws error if it can't.

The lack of regression tests covering this area is a bit annoying
at this point.  However, it's hard to see how to test FOR UPDATE
until we get some concurrent-sessions support in psql.
        regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> The lack of regression tests covering this area is a bit annoying
> at this point.  However, it's hard to see how to test FOR UPDATE
> until we get some concurrent-sessions support in psql.

Well, end-to-end testing would e better but we can test that the locks are
at least being recorded by white-box inspection:

postgres=# begin;
BEGIN
postgres=# select * from tellers for share;tid | bid | tbalance | filler 
-----+-----+----------+--------  1 |   1 |        0 |   2 |   1 |        0 |   3 |   1 |        0 |   4 |   1 |
0|   5 |   1 |        0 |   6 |   1 |        0 |   7 |   1 |        0 |   8 |   1 |        0 |   9 |   1 |        0 |
10|   1 |        0 | 
 
(10 rows)

postgres=# select distinct age(xmax) from tellers;age 
-----  0
(1 row)

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!

Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> The lack of regression tests covering this area is a bit annoying
>> at this point.  However, it's hard to see how to test FOR UPDATE
>> until we get some concurrent-sessions support in psql.

> Well, end-to-end testing would e better but we can test that the locks are
> at least being recorded by white-box inspection:

> postgres=# select distinct age(xmax) from tellers;

Huh, that's a cute trick ... and it would answer the immediate problem,
which is to check that the statement did take the row locks it was
supposed to.
        regards, tom lane