Modify

Opened 9 years ago

Closed 9 years ago

#2764 closed defect (fixed)

Sporadic error on Postgres

Reported by: Peter Owned by: Robert Corsaro
Priority: normal Component: AnnouncerPlugin
Severity: minor Keywords:
Cc: Trac Release: 0.11

Description

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'

Attachments (3)

postgres-announcer.patch (550 bytes) - added by Robert Corsaro 9 years ago.
authenticated2int.patch (2.3 KB) - added by Robert Corsaro 9 years ago.
convert authenticated to int before any query.
ann_rule2str.patch (2.4 KB) - added by Robert Corsaro 9 years ago.
cast rule in select to string before running query

Download all attachments as: .zip

Change History (12)

comment:1 Changed 9 years ago by 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.

comment:2 Changed 9 years ago by 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.

Changed 9 years ago by Robert Corsaro

Attachment: postgres-announcer.patch added

comment:3 Changed 9 years ago by Robert Corsaro

Owner: changed from Stephen Hansen to Robert Corsaro
Status: newassigned

Changed 9 years ago by Robert Corsaro

Attachment: authenticated2int.patch added

convert authenticated to int before any query.

comment:4 Changed 9 years ago by Robert Corsaro

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

comment:5 Changed 9 years ago by 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)

comment:6 Changed 9 years ago by 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.

Changed 9 years ago by Robert Corsaro

Attachment: ann_rule2str.patch added

cast rule in select to string before running query

comment:7 Changed 9 years ago by 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:
***************

comment:8 Changed 9 years ago by anonymous

can you try

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

comment:9 Changed 9 years ago by Peter

Resolution: fixed
Status: assignedclosed

OK this seems to have done the trick. Thanks!

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Robert Corsaro.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.