> Using this attribute, we can have more control on parallel operations like,
> IF SKIPPED_ROW_COUNT =0 THEN > <<Treat me as, a complete transaction, and do below stuff>> > ELSE > <<Got only few tuples than required, and do below stuff>> > END IF;
Um ... so what? This is not a use-case.
In my view, "How one can be sure that, he obtained all the tuples with SKIP LOCKED". If the end user has this counter value, he may proceed with a different approach with partially locked tuples.
Can you be more specific? In most cases I can come up with (queues, basically) where skipped locked is running the processing performing the query is going to re-query the database on the next tick regardless of whether they thought they say only some or all of the potential rows on the prior pass.
Sure,
In an existing wait policies like WAIT(default) and NO WAIT, one can be sure to determine(Using ROW_COUNT daignostics counter), how many required tuples he processed in a transaction. But this is not case when it comes to SKIP LOCKED. My concern is, how we come to know that, our SKIP LOCKED missed few tuples due to lock, OR it's processed all the tuples. I understood that, the name itself defining SKIP the LOCKED rows, but we are not measuring it.
I wrote the patch and it's working as below. But I haven't extended this to other planners yet.
postgres=# DO $$ DECLARE rc INT; BEGIN PERFORM * FROM test WHERE mod(t,2)=0 FOR UPDATE SKIP LOCKED; GET DIAGNOSTICS rc=SKIPPED_ROW_COUNT; RAISE NOTICE 'Skipped : %', rc; GET DIAGNOSTICS rc=ROW_COUNT; RAISE NOTICE 'Processed : %', rc; END; $$ ; NOTICE: Skipped : 2 NOTICE: Processed : 3 DO
Once we measured the SKIP LOCKED, then we can only consider to re-process the Skipped !=0 transactions rather doing every transaction again.
In my view, SKIP LOCKED is a nice feature, which gives only the available OR unlocked tuples. But those are not the complete required tuples for the given SQL statement. Isn't it ?!