Re: Receiving many more rows than expected

On 05/09/2014 05:36 AM, Vincent de Phily wrote:
> On Friday 09 May 2014 07:01:32 Tom Lane wrote:
>> Vincent de Phily <vincent.dephily@mobile-devices.fr> writes:
>>> In case it changes anything, this is the uncut (but still anonimized)
>>>
>>> function:
>>>      query = """UPDATE foo SET processing = 't' WHERE id IN
>>>
>>>             (SELECT id FROM foo WHERE processing = 'f' ORDER BY id ASC
>>>             LIMIT %d
>>>
>>>              FOR UPDATE)
>>>
>>>             RETURNING *""" % (conf_getint('DEFAULT', 'push_count', 5000),)
>>
>> Well, of course this view of things exposes a relevant failure mode
>> you hadn't mentioned: maybe sometimes the conf_getint() call returns
>> something other than 5000?
>
> True. But I've commented already that I'd be very surprised (and wouldn't know
> how to begin) if that value was faulty (even though it would explain things
> nicely), because
> * It is parsed once at program start (using python's ConfigParser library)

What is parsed?

> * It has the correct value of 5000 in most cases (as demonstrated by the
>    frequency of number of rows returned)

Yes, but those are not the ones of interest.

> * There is no sign that I exited the loop (and therefore got the opportunity
>    to change the value of the query) before I start receiving overlong results.

Not sure I follow, You are passing a function as a parameter, it
would be re-run each time the query was run in:

curs.execute(query)

FYI, the psycopg2 docs recommend you not use the parameter passing
method above as it is susceptible to SQL injection:

http://initd.org/psycopg/docs/usage.html#passing-parameters-to-sql-queries

>
> Still, I agree it's suspicious, so I'm now logging the query string whenever I
> get over 5000 results (hardcoded). We'll see next time it happens.
>


--
Adrian Klaver
adrian.klaver@aklaver.com