Re: [PROPOSAL] DIAGNOSTICS <var> = SKIPPED_ROW_COUNT

On Wed, Oct 14, 2015 at 4:06 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Wed, Oct 14, 2015 at 6:28 PM, dinesh kumar <dineshkumar02(at)gmail(dot)com>
> wrote:
> > I see this feature as an add on to do the parallel DML operations.
> > There won't be any problem, if operations are mutually exclusive.
> > I mean, each session operates on unique set of tuples.
> >
> > In the above case, we don't even need of SKIP LOCKED wait policy.
> >
> > But, when it comes to mutually depend operations, isn't it nice to
> provide,
> > how much were locked by the other sessions. OR atlest a HINT to the other
> > session like,
> >
> > GET DIAGNOSTICS var = DID_I_MISS_ANYTHING_FROM_OTHER_SESSIONS;
> >
> > I agree that, adding counter will take a performance hit.
> > Rather going to my actual proposal on providing the counter value,
> > isn't it good to provide a boolean type HINT, if we miss atleast a single
> > tuple.
>
> Suppose there are 5 locked rows and 5 unlocked rows in the heap and you do
> this:
>
> select * from t1 for share skip locked limit 5
>
> The Boolean you propose will be false if the first 5 rows in physical
> order are locked, and otherwise it will be false. But there's no
> difference between those two scenarios from the perspective of the
> application. Here's another example:
>
> with foo as (select * from t1 for share skip locked) select * from foo
> where a = 2;
>
> If foo contains any locked rows at all, this will return true,
> regardless of whether a = 2.
>
> It's true that, for a lot of normal-ish queries, LockRows is applied
> late enough that your proposed Boolean would return the intended
> answer. But there are a bunch of exceptions, like the ones shown
> above, and there might be more in the future.
>
>
Hi Robert,

As usual, a great guidance from you. Thanks :-)

But I'm still trying to see, is there a way we can implement this for all
use cases.
Will update this thread with my findings.

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

--

Regards,
Dinesh
manojadinesh.blogspot.com