How to add explicit types so I can use OVERLAPS?

I want to use the OVERLAPS keyword to compare two time intervals, but
I'm having trouble.

In the code below, I want to test if the time interval from 9:15 AM
through 9:45 AM overlaps the time interval from 8 AM through 10 AM.

This approach fails:

    >>> d = {'9:15 AM':datetime(2008, 8, 1, 9, 15),
    ...      '9:45 AM':datetime(2008, 8, 1, 9, 45),
    ...      '8 AM':datetime(2008, 8, 1, 8),
    ...      '10 AM':datetime(2008, 8, 1, 10)}

    >>> cursor.execute('select (%(9:15 AM)s, %(9:45 AM)s) '
    ...                'overlaps (%(8 AM)s, %(10 AM)s);', d)

    ------------------------------------------------------------
    Traceback (most recent call last):
      File "<ipython console>", line 1, in <module>
    ProgrammingError: function pg_catalog.overlaps(unknown, unknown,
    unknown, unknown) is not unique
    LINE 1: ...ct ('2008-08-01T09:15:00', '2008-08-01T09:45:00') overlaps
    (...
                                                                 ^
    HINT:  Could not choose a best candidate function. You might need to add
    explicit type casts.

Meanwhile, this approach works fine:

    >>> cursor.execute('select (TIMESTAMP %(9:15 AM)s, TIMESTAMP %(9:45 AM)s) '

Re: How to add explicit types so I can use OVERLAPS?

Il giorno ven, 01/08/2008 alle 14.47 +0000, Matthew Wilson ha scritto:
> I want to use the OVERLAPS keyword to compare two time intervals, but
> I'm having trouble.
> 
> In the code below, I want to test if the time interval from 9:15 AM
> through 9:45 AM overlaps the time interval from 8 AM through 10 AM.
> 
> This approach fails:
> 
>     >>> d = {'9:15 AM':datetime(2008, 8, 1, 9, 15),
>     ...      '9:45 AM':datetime(2008, 8, 1, 9, 45),
>     ...      '8 AM':datetime(2008, 8, 1, 8),
>     ...      '10 AM':datetime(2008, 8, 1, 10)}
> 
>     >>> cursor.execute('select (%(9:15 AM)s, %(9:45 AM)s) '
>     ...                'overlaps (%(8 AM)s, %(10 AM)s);', d)
> 
>     ------------------------------------------------------------
>     Traceback (most recent call last):
>       File "<ipython console>", line 1, in <module>
>     ProgrammingError: function pg_catalog.overlaps(unknown, unknown,
>     unknown, unknown) is not unique
>     LINE 1: ...ct ('2008-08-01T09:15:00', '2008-08-01T09:45:00') overlaps
>     (...
>                                                                  ^
>     HINT:  Could not choose a best candidate function. You might need to add
>     explicit type casts.
> 
> Meanwhile, this approach works fine:
> 

Re: How to add explicit types so I can use OVERLAPS?

On Aug 1, 2008, at 4:07 PM, Federico Di Gregorio wrote:

> Il giorno ven, 01/08/2008 alle 14.47 +0000, Matthew Wilson ha scritto:
>> I want to use the OVERLAPS keyword to compare two time intervals, but
>> I'm having trouble.
>>
>> In the code below, I want to test if the time interval from 9:15 AM
>> through 9:45 AM overlaps the time interval from 8 AM through 10 AM.
>>
>> This approach fails:
>>
>>>>> d = {'9:15 AM':datetime(2008, 8, 1, 9, 15),
>>    ...      '9:45 AM':datetime(2008, 8, 1, 9, 45),
>>    ...      '8 AM':datetime(2008, 8, 1, 8),
>>    ...      '10 AM':datetime(2008, 8, 1, 10)}
>>
>>>>> cursor.execute('select (%(9:15 AM)s, %(9:45 AM)s) '
>>    ...                'overlaps (%(8 AM)s, %(10 AM)s);', d)
>>
>>    ------------------------------------------------------------
>>    Traceback (most recent call last):
>>      File "<ipython console>", line 1, in <module>
>>    ProgrammingError: function pg_catalog.overlaps(unknown, unknown,
>>    unknown, unknown) is not unique
>>    LINE 1: ...ct ('2008-08-01T09:15:00', '2008-08-01T09:45:00')  
>> overlaps
>>    (...
>>                                                                 ^
>>    HINT:  Could not choose a best candidate function. You might  
>> need to add

Re: How to add explicit types so I can use OVERLAPS?

Il giorno ven, 01/08/2008 alle 16.49 -0400, Phillip Frost ha scritto:
> I'd be opposed to a patch that made this the default behavior, because  
> I have already some queries with things such as "timestamp %s" and so  
> on. If psycopg added the "timestamp" bit for me I'd get syntax errors.  
> Using CAST syntax might not cause a syntax error but could potentially  
> change the semantics of my queries.
> 
> I think it would also run counter to the spirit of the SQL language. I  
> think it's a frequently misunderstood fact that quoting something in  
> SQL does not make it a string; it is a literal value of unknown type.  
> The type can later be inferred (or coerced) as needed to match the  
> type of other operands, which may be defined by tables, etc. It's nice  
> to have the freedom to explicitly state the type of a literal or leave  
> it up to coercion when writing a query.
> 
> That said, it's not hard to replace the adapter for datetime objects  
> to insert the "timestamp" prior to the quoted value if that is more  
> useful for your application. Please just don't do this for me.

I understand but here we have a problem: psycopg already worked out of
the box. Now we have "errors" in queries that with PostgreSQL 8.2 where
ok. psycopg is not supposed to send to the backend "unknown" values. It
is supposed to send values of the right type. Untill now using
PostgreSQL intrinsic ability to convert a quoted string to the right
type worked. If it does not work anymore it should be fixed.

Anyway, I'll make sure to fix this maintaining backward compatibility. I
won't just stick in typecasts from the next release.

federico

Re: Build issues in psycopg.h (PGConn)

Il giorno mar, 29/07/2008 alle 10.36 +0200, Jorgen Austvik - Sun Norway
ha scritto:
> Hi,
> 
> I am seeing some build issues for psycopg.h:

You're right. This is now fixed in bzr trunk. Thanks.

federico

--

-- 
Federico Di Gregorio                         http://people.initd.org/fog
DISCLAIMER. If I receive a message from you, you are agreeing that:
 1. I am by definition, "the intended recipient".
 2. All information in the email is mine to do with as I see fit and
 make such financial profit, political mileage, or good joke as it lends
 itself to. In particular, I may quote it on USENET or the WWW.
 3. I may take the contents as representing the views of your company.
 4. This overrides any disclaimer or statement of confidentiality that
 may be included on your message.

_______________________________________________
Psycopg mailing list
Psycopg@...
http://lists.initd.org/mailman/listinfo/psycopg

Advice on debugging what seems a deadlock with Zope and psycopg

Hi all.

I'm asking advice on something which can seems a little of archeology,
here. In particulare, I have and application:

- running on Zope 2.8.8
- python2.3
- psycopg 1.1.21

After got some reports of occasional "freezes" under load of the
server, I set up a little testbed with ab, in order to
check if I was able to reproduce the problem.

The problem was so nice to show quickly. Zope was frozen, with all his
threads waiting.

Now, I followed the recipe to attach gdb to a running Zope; I can see
five threads right now:

(gdb) info threads
  5 Thread 0xb5b2fb90 (LWP 8935)  0xffffe410 in __kernel_vsyscall ()
  4 Thread 0xb5328b90 (LWP 8936)  0xffffe410 in __kernel_vsyscall ()
  3 Thread 0xb4b27b90 (LWP 8937)  0xffffe410 in __kernel_vsyscall ()
  2 Thread 0xb4326b90 (LWP 8938)  0xffffe410 in __kernel_vsyscall ()
  1 Thread 0xb7d518c0 (LWP 8922)  0xffffe410 in __kernel_vsyscall ()

and this is the backtrace for each of them:

(gdb) thread 1
[Switching to thread 1 (Thread 0xb7d518c0 (LWP 8922))]#0  0xffffe410

Re: Advice on debugging what seems a deadlock with Zope and psycopg

Hi Marco,

if I remember correctly, SELECT FOR UPDATE place a lock on the selected
rows so this _can_ be a deadlock problem. At first sight it does not
seem a psycopg problem but the version you're using is so old I don't
remember if there were locking bugs. Which version of PostgreSQL are you
using?

federico

Il giorno dom, 03/08/2008 alle 09.40 +0200, Marco Bizzarri ha scritto:
> Hi all.
> 
> I'm asking advice on something which can seems a little of archeology,
> here. In particulare, I have and application:
> 
> - running on Zope 2.8.8
> - python2.3
> - psycopg 1.1.21
> 
> After got some reports of occasional "freezes" under load of the
> server, I set up a little testbed with ab, in order to
> check if I was able to reproduce the problem.
> 
> The problem was so nice to show quickly. Zope was frozen, with all his
> threads waiting.
> 
> Now, I followed the recipe to attach gdb to a running Zope; I can see
> five threads right now:
> 

Re: Advice on debugging what seems a deadlock with Zope and psycopg

On Sun, Aug 3, 2008 at 11:16 AM, Federico Di Gregorio <fog@...> wrote:
> Hi Marco,
>
> if I remember correctly, SELECT FOR UPDATE place a lock on the selected
> rows so this _can_ be a deadlock problem.

Yes, SELECT FOR UPDATE do places a lock on the rows. In my testcase,
I'm basically performing over and over (using ab) the same sequence of
operations; they are, more or less:

SELECT FOR UPDATE to get the value of the counter
UPDATE to increase the counter

even though there are other tables involved they should not be
involved in this kind of problem.

And, transaction isolation level is SERIALIZABLE.

> At first sight it does not
> seem a psycopg problem but the version you're using is so old I don't
> remember if there were locking bugs.

Yes, I know; the version of psycopg is quite old, and I'm considering
a migration path; the problem is that I've to consider a migration
path for psycopg, python, Zope and PostgreSQL (altough, admitedly,
this one seems the less probelmatic to me).

> Which version of PostgreSQL are you
> using?

Re: Advice on debugging what seems a deadlock with Zope and psycopg

Il giorno dom, 03/08/2008 alle 12.56 +0200, Marco Bizzarri ha scritto:
> On Sun, Aug 3, 2008 at 11:16 AM, Federico Di Gregorio <fog@...> wrote:
> > Hi Marco,
> >
> > if I remember correctly, SELECT FOR UPDATE place a lock on the selected
> > rows so this _can_ be a deadlock problem.
> 
> Yes, SELECT FOR UPDATE do places a lock on the rows. In my testcase,
> I'm basically performing over and over (using ab) the same sequence of
> operations; they are, more or less:
> 
> SELECT FOR UPDATE to get the value of the counter
> UPDATE to increase the counter
> 
> even though there are other tables involved they should not be
> involved in this kind of problem.
> 
> And, transaction isolation level is SERIALIZABLE.
> 
> > At first sight it does not
> > seem a psycopg problem but the version you're using is so old I don't
> > remember if there were locking bugs.
> 
> Yes, I know; the version of psycopg is quite old, and I'm considering
> a migration path; the problem is that I've to consider a migration
> path for psycopg, python, Zope and PostgreSQL (altough, admitedly,
> this one seems the less probelmatic to me).
> 
> > Which version of PostgreSQL are you
> > using?

Re: lastrowid and INSERT ... RETURNING

Il giorno ven, 06/06/2008 alle 17.58 -0700, Cliff Wells ha scritto:
> On Wed, 2006-07-26 at 08:45 +0200, Federico Di Gregorio wrote:
> > Il giorno mar, 25/07/2006 alle 15.22 -0700, craigp ha scritto:
> > > there's a todo item at
> > > http://www.postgresql.org/docs/faqs.TODO.html (search
> > > for returning) which would return columns/expressions on
> > > insert/update. so
> > > if/when that's done there'll be a mechanism to support the use-case of
> > > returning the primary-key of a newly-inserted row (among others). 
> > 
> > That would be neat. Please, poke at me when it is ready in pg. :)
> 
> Poke.  Added in 8.2.4.
> 
> test=# create table foo ( id serial primary key not null, name text );
> 
> test=# insert into foo ( name ) values ( 'cliff' ) returning id;

RETURNING can return anything so does it make sense to hack some magic
to automatically populate .lastrowid when the query is an INSERT and the
value returned is a long (like the OID were) or is better to simply
accept that PostgreSQL does not support row ids and put the .fetchone()
in the code?

I am for the second but I can hack .lastrowid if enough people need it
to transition from old backends with automatic OIDs to the new ones
where OIDs are not geneated by default.

federico