Re: [PROPOSAL] DIAGNOSTICS <var> = SKIPPED_ROW_COUNT

On Tue, Oct 13, 2015 at 5:53 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

>
>>> > 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 ?!

Let me know, if I am still not clear about this.

David J.
>
>

--

Regards,
Dinesh
manojadinesh.blogspot.com