Thread: results via pgAdmin but not via psycopg2

Hello everyone,

I’ve been struggling with an issue for 2 days now and couldn’t find the answer via google search.

here’s a method of my db class:

    def get_old_feeds( self, not_refreshed_for_secs ):
        cursor = self.connection.cursor( cursor_factory = psycopg2.extras.RealDictCursor )
        cursor.execute( "SELECT id, url FROM feeds WHERE refreshed_on is not NULL AND refreshed_on < ( now() - interval
'%sseconds' );", ( not_refreshed_for_secs, ) ) 
        old_feeds = cursor.fetchall()
        cursor.close()
        return old_feeds

there’s a loop that invokes this method every X minutes. All “old_feeds” get their value for “refreshed_on” updated to
thecurrent date when processed using this method: 

    def set_feed_last_refresh( self, feed_id, refresh_datetime ):
        cursor = self.connection.cursor()
        cursor.execute( "UPDATE feeds SET refreshed_on = %s WHERE id = %s;", ( refresh_datetime, feed_id ) )
        self.connection.commit()
        cursor.close()

The problem is that the select query in the first method returns some results only the first time it is run, all
subsequentexecutions give 0 results. The odd thing is that when I issue exactly the same query via pgAdmin I get the
resultsas one would expect. 

I’d highly appreciate your help with this issue,

Thank you,
Krystian

now() always returns the transaction start timestamp.

Might that be it ?

Karsten

On Tue, Dec 31, 2013 at 11:44:46AM +0000, Krystian Samp wrote:

> Hello everyone,
>
> I’ve been struggling with an issue for 2 days now and couldn’t find the answer via google search.
>
> here’s a method of my db class:
>
>     def get_old_feeds( self, not_refreshed_for_secs ):
>         cursor = self.connection.cursor( cursor_factory = psycopg2.extras.RealDictCursor )
>         cursor.execute( "SELECT id, url FROM feeds WHERE refreshed_on is not NULL AND refreshed_on < ( now() -
interval'%s seconds' );", ( not_refreshed_for_secs, ) ) 
>         old_feeds = cursor.fetchall()
>         cursor.close()
>         return old_feeds
>
> there’s a loop that invokes this method every X minutes. All “old_feeds” get their value for “refreshed_on” updated
tothe current date when processed using this method: 
>
>     def set_feed_last_refresh( self, feed_id, refresh_datetime ):
>         cursor = self.connection.cursor()
>         cursor.execute( "UPDATE feeds SET refreshed_on = %s WHERE id = %s;", ( refresh_datetime, feed_id ) )
>         self.connection.commit()
>         cursor.close()
>
> The problem is that the select query in the first method returns some results only the first time it is run, all
subsequentexecutions give 0 results. The odd thing is that when I issue exactly the same query via pgAdmin I get the
resultsas one would expect. 
>
> I’d highly appreciate your help with this issue,
>
> Thank you,
> Krystian
>
> --
> Sent via psycopg mailing list (psycopg@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg

--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Hi Karsten,

Thank you so much, this was the problem indeed, and “connection.commit()” solved it.

Didn’t think about committing after a SELECT command.

Now I can peacefully end 2013!

Thanks and Happy New Year!

On 31 Dec 2013, at 12:07, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

> now() always returns the transaction start timestamp.
>
> Might that be it ?
>
> Karsten
>
> On Tue, Dec 31, 2013 at 11:44:46AM +0000, Krystian Samp wrote:
>
>> Hello everyone,
>>
>> I’ve been struggling with an issue for 2 days now and couldn’t find the answer via google search.
>>
>> here’s a method of my db class:
>>
>>     def get_old_feeds( self, not_refreshed_for_secs ):
>>         cursor = self.connection.cursor( cursor_factory = psycopg2.extras.RealDictCursor )
>>         cursor.execute( "SELECT id, url FROM feeds WHERE refreshed_on is not NULL AND refreshed_on < ( now() -
interval'%s seconds' );", ( not_refreshed_for_secs, ) ) 
>>         old_feeds = cursor.fetchall()
>>         cursor.close()
>>         return old_feeds
>>
>> there’s a loop that invokes this method every X minutes. All “old_feeds” get their value for “refreshed_on” updated
tothe current date when processed using this method: 
>>
>>     def set_feed_last_refresh( self, feed_id, refresh_datetime ):
>>         cursor = self.connection.cursor()
>>         cursor.execute( "UPDATE feeds SET refreshed_on = %s WHERE id = %s;", ( refresh_datetime, feed_id ) )
>>         self.connection.commit()
>>         cursor.close()
>>
>> The problem is that the select query in the first method returns some results only the first time it is run, all
subsequentexecutions give 0 results. The odd thing is that when I issue exactly the same query via pgAdmin I get the
resultsas one would expect. 
>>
>> I’d highly appreciate your help with this issue,
>>
>> Thank you,
>> Krystian
>>
>> --
>> Sent via psycopg mailing list (psycopg@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/psycopg
>
> --
> GPG key ID E4071346 @ gpg-keyserver.de
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via psycopg mailing list (psycopg@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg

On Tue, Dec 31, 2013 at 12:52:51PM +0000, Krystian Samp wrote:

> Thank you so much, this was the problem indeed, and “connection.commit()” solved it.
>
> Didn’t think about committing after a SELECT command.

If you want to spare the commit you may want to look at statement_timestamp();

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Thanks,

This sounds good,

Would a commit() be considered slow or undesirable?

K

On 31 Dec 2013, at 13:09, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

> On Tue, Dec 31, 2013 at 12:52:51PM +0000, Krystian Samp wrote:
>
>> Thank you so much, this was the problem indeed, and “connection.commit()” solved it.
>>
>> Didn’t think about committing after a SELECT command.
>
> If you want to spare the commit you may want to look at statement_timestamp();
>
> Karsten
> --
> GPG key ID E4071346 @ gpg-keyserver.de
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via psycopg mailing list (psycopg@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg

On 12/31/2013 05:14 AM, Krystian Samp wrote:
> Thanks,
>
> This sounds good,
>
> Would a commit() be considered slow or undesirable?

For the  purpose of the SELECT, more unnecessary than anything else. You
would be invoking a transaction for the sole purpose of rolling over a
time value. This as pointed out can be solved without committing a
transaction. For a detailed look at your options see:

http://www.postgresql.org/docs/9.3/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

9.9.4. Current Date/Time


It documents the behavior of the various date(time) functions.

>
> K
>
> On 31 Dec 2013, at 13:09, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:
>
>> On Tue, Dec 31, 2013 at 12:52:51PM +0000, Krystian Samp wrote:
>>
>>> Thank you so much, this was the problem indeed, and “connection.commit()” solved it.
>>>
>>> Didn’t think about committing after a SELECT command.
>>
>> If you want to spare the commit you may want to look at statement_timestamp();
>>
>> Karsten
>> --
>> GPG key ID E4071346 @ gpg-keyserver.de
>> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>>
>>
>> --
>> Sent via psycopg mailing list (psycopg@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/psycopg
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com