Sporadic error on Postgres

[Peter]

It does not seem to impact normal operation, but I'm gettin tons of error messages in Postgres logs:

HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.
STATEMENT:
                    SELECT value
                      FROM session_attribute
                     WHERE sid='jbrink'
                       AND authenticated=true
                       AND name='announcer_email_format_ticket'

ERROR:  current transaction is aborted, commands ignored until end of transaction block
STATEMENT:  SELECT DISTINCT s.sid, n.value, e.value FROM session AS s  LEFT JOIN session_attribute AS n ON (n.sid=s.sid   and n.authenticated=1 AND n.name = 'name')  LEFT JOIN session_attribute AS e ON (e.sid=s.sid   AND e.authenticated=1 AND e.name = 'email') WHERE s.authenticated=1 ORDER BY s.sid

I cant pinpoint where this happens - tried browsing around Trac but couldnt trigger it. Obviously the cause is 'authenticated=true' clause, should have been 'authenticated=1'

[Peter]

Here is excerpt from Trac log when it happens:

2008-03-25 05:12:11,204 Trac[api] ERROR: AnnouncementSystem failed.
Traceback (most recent call last):
  File "build/bdist.linux-i686/egg/announcerplugin/api.py", line 376, in _real_send
    distributor.distribute(transport, packages[transport], evt)
  File "build/bdist.linux-i686/egg/announcerplugin/distributors/email_distributor.py", line 157, in distribute
    format = self._get_preferred_format(event.realm, name, authenticated)
  File "build/bdist.linux-i686/egg/announcerplugin/distributors/email_distributor.py", line 205, in _get_preferred_format
    """, (sid, authenticated, 'announcer_email_format_%s' % realm))
  File "/usr/lib/python2.5/site-packages/Trac-0.11b1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib/python2.5/site-packages/Trac-0.11b1-py2.5.egg/trac/db/util.py", line 50, in execute
    return self.cursor.execute(sql_escape_percent(sql), args)
ProgrammingError: operator does not exist: integer = boolean
LINE 5:                AND authenticated=true
                                        ^
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

[Robert Corsaro]

Try the postgres-announcer.patch. I think the problem is that sqlite doesn't really have types, so true == 1. Maybe if we make it a text column trac can figure it out. I'm not using postgres, so I can't tell. The idea is to change the schema itself. You will have to install it on a fresh environment so the schema can be generated.

[Robert Corsaro]

convert authenticated to int before any query.

[Robert Corsaro]

This newer patch will work better. Please test and reply.

[Peter]

Now I'm getting the following error:

ERROR:  operator does not exist: text = integer at character 272
HINT:  No operator matches the given name and argument type(s). You might need t
o add explicit type casts.
STATEMENT:
                            SELECT transport, sid, authenticated
                              FROM subscriptions
                             WHERE enabled=1 AND managed='watcher'
                               AND realm='ticket'
                               AND category='*'
                               AND rule=1037

Column 'rule' is of type text yet is being compared to integer. This no longer works in Postgres 8.3 (used to work in 8.2)

[Robert Corsaro]

thanks for working on this with me. Looks like we are getting closer to a solution. sqlite does a lot of automatic casting and I think this is what we are seeing. Postgres doesn't. Try this patch.

[Robert Corsaro]

cast rule in select to string before running query

[Peter]

The patch fails, all hunks get rejected. I'm on revision 4069. Excerpt from .rej file:

***************
*** 76,82 ****
                 AND realm=%s
                 AND category=%s
                 AND rule=%s
-         """, (sid, authenticated and 1 or 0, 'watcher', realm, '*', resource))

          result = cursor.fetchone()
          if result:
--- 76,82 ----
                 AND realm=%s
                 AND category=%s
                 AND rule=%s
+         """, (sid, int(authenticated), 'watcher', realm, '*', str(resource)))

          result = cursor.fetchone()
          if result:
***************

[anonymous]

can you try

cd announcerplugin/0.11
svn revert . -R 
svn up
cat ann_rule2str.patch | patch -p0 

[Peter]

OK this seems to have done the trick. Thanks!