Modify

Opened 16 years ago

Closed 10 years ago

#3677 closed defect (fixed)

Database operations fail with PostgreSQL 8.3

Reported by: david@… Owned by: Steffen Hoffmann
Priority: normal Component: TagsPlugin
Severity: normal Keywords: postgresql text cast
Cc: Ryan J Ollos, Michael Renzmann Trac Release: 0.11

Description

As of PostgreSQL, many automatic casts to text were removed. See the release notes. A symptom is the following error I received:

ProgrammingError: operator does not exist: text = integer
LINE 1: DELETE FROM tags WHERE tagspace=E'screenshots' AND name=3
                                                               ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Traceback:

Most recent call last:

    * File "/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/main.py", line 423, in _dispatch_request
      Code fragment:
       418. try:
       419. if not env and env_error:
       420. raise HTTPInternalError(env_error)
       421. try:
       422. dispatcher = RequestDispatcher(env)
       423. dispatcher.dispatch(req)
       424. except RequestDone:
       425. pass
       426. resp = req._response or []
       427.  
       428. except HTTPException, e:
      Local variables:
      Name	Value
      after 	[u' except RequestDone:', u' pass', u' resp = ...
      before 	[u' try:', u' if not env and env_error:', u' raise ...
      dispatcher 	<trac.web.main.RequestDispatcher object at 0x8058f7c90>
      e 	ProgrammingError("operator does not exist: text = integer\nLINE 1: DELETE ...
      env 	<trac.env.Environment object at 0x803d0e310>
      env_error 	None
      exc_info 	(<class 'psycopg2.ProgrammingError'>, ProgrammingError("operator does not ...
      filename 	'/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/main.p ...
      frames 	[{'function': '_dispatch_request', 'lines_before': [u' try:', u' ...
      has_admin 	True
      line 	u' dispatcher.dispatch(req)'
      lineno 	422
      message 	u"ProgrammingError: operator does not exist: text = integer\nLINE 1: ...
      req 	<Request "POST u'/screenshots'">
      resp 	[]
      tb 	<traceback object at 0x809623cb0>
      tb_hide 	None
      traceback 	'Traceback (most recent call last):\n File ...
    * File "/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/main.py", line 197, in dispatch
      Code fragment:
       192. req.args.get('__FORM_TOKEN') != req.form_token:
       193. raise HTTPBadRequest('Missing or invalid form token. '
       194. 'Do you have cookies enabled?')
       195.  
       196. # Process the request and render the template
       197. resp = chosen_handler.process_request(req)
       198. if resp:
       199. if len(resp) == 2: # Clearsilver
       200. chrome.populate_hdf(req)
       201. template, content_type = \
       202. self._post_process_request(req, *resp)
      Local variables:
      Name	Value
      chosen_handler 	<tracscreenshots.core.ScreenshotsCore object at 0x805d46410>
      chrome 	<trac.web.chrome.Chrome object at 0x8058e6c10>
      ctype 	'multipart/form-data'
      err 	(<class 'psycopg2.ProgrammingError'>, ProgrammingError("operator does not ...
      handler 	<tracscreenshots.core.ScreenshotsCore object at 0x805d46410>
      options 	{'boundary': '---------------------------1687091515871393671656955613'}
      req 	<Request "POST u'/screenshots'">
      self 	<trac.web.main.RequestDispatcher object at 0x8058f7c90>
    * File "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tracscreenshots/core.py", line 127, in process_request
      Local variables:
      Name	Value
      actions 	['post-add', 'view']
      context 	<Context <Resource 'screenshots-core'>>
      db 	<trac.db.pool.PooledConnection object at 0x8096123f8>
      req 	<Request "POST u'/screenshots'">
      self 	<tracscreenshots.core.ScreenshotsCore object at 0x805d46410>
    * File "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tracscreenshots/core.py", line 338, in _do_actions
      Local variables:
      Name	Value
      action 	'post-add'
      actions 	['post-add', 'view']
      api 	<tracscreenshots.api.ScreenshotsApi object at 0x808d1bbd0>
      components 	[]
      context 	<Context <Resource 'screenshots-core'>>
      ext 	u'.jpg'
      file 	<open file '<fdopen>', mode 'w+b' at 0x809429d50>
      filename 	u'signet.jpg'
      filepath 	u'/usr/local/svn/trac/screenshots/3/signet-1024x721.jpg'
      image 	<JpegImagePlugin.JpegImageFile instance at 0x80914bd40>
      listener 	<tracscreenshots.tags.ScreenshotsTags object at 0x809655650>
      name 	u'signet'
      out_file 	<closed file u'/usr/local/svn/trac/screenshots/3/signet-1024x721.jpg', ...
      path 	u'/usr/local/svn/trac/screenshots/3'
      reg 	<_sre.SRE_Pattern object at 0x809452108>
      result 	<_sre.SRE_Match object at 0x80951c8b0>
      screenshot 	{'description': u'', 'author': u'davidb', 'components': [], 'tags': u'', ...
      self 	<tracscreenshots.core.ScreenshotsCore object at 0x805d46410>
      versions 	[]
    * File "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tracscreenshots/tags.py", line 42, in screenshot_created
      Local variables:
      Name	Value
      req 	<Request "POST u'/screenshots'">
      resource 	<Resource u'screenshots:3'>
      screenshot 	{'description': u'', 'author': u'davidb', 'components': [], 'tags': u'', ...
      self 	<tracscreenshots.tags.ScreenshotsTags object at 0x809655650>
      tag_system 	<tractags.api.TagSystem object at 0x808d1bdd0>
    * File "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tractags/api.py", line 226, in delete_tags
      Local variables:
      Name	Value
      provider 	<tracscreenshots.tags.ScreenshotsTagProvider object at 0x809001f90>
      req 	<Request "POST u'/screenshots'">
      resource 	<Resource u'screenshots:3'>
      self 	<tractags.api.TagSystem object at 0x808d1bdd0>
      tags 	None
    * File "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tractags/api.py", line 150, in remove_resource_tags
      Local variables:
      Name	Value
      cursor 	<trac.db.util.IterableCursor object at 0x80964ebe8>
      db 	<trac.db.pool.PooledConnection object at 0x80918a830>
      req 	<Request "POST u'/screenshots'">
      resource 	<Resource u'screenshots:3'>
      self 	<tracscreenshots.tags.ScreenshotsTagProvider object at 0x809001f90>
    * File "/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/db/util.py", line 50, in execute
      Code fragment:
        45. def execute(self, sql, args=None):
        46. # -- In case of SQL errors, uncomment the following 'print' statements
        47. # print 'execute', repr(sql)
        48. if args:
        49. # print repr(args)
        50. return self.cursor.execute(sql_escape_percent(sql), args)
        51. return self.cursor.execute(sql)
        52.  
        53. def executemany(self, sql, args=None):
        54. # print 'executemany', repr(sql)
        55. if args:
      Local variables:
      Name	Value
      args 	('screenshots', 3)
      self 	<trac.db.util.IterableCursor object at 0x80964ebe8>
      sql 	'DELETE FROM tags WHERE tagspace=%s AND name=%s'
    * File "/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/db/util.py", line 50, in execute
      Code fragment:
        45. def execute(self, sql, args=None):
        46. # -- In case of SQL errors, uncomment the following 'print' statements
        47. # print 'execute', repr(sql)
        48. if args:
        49. # print repr(args)
        50. return self.cursor.execute(sql_escape_percent(sql), args)
        51. return self.cursor.execute(sql)
        52.  
        53. def executemany(self, sql, args=None):
        54. # print 'executemany', repr(sql)
        55. if args:
      Local variables:
      Name	Value
      args 	('screenshots', 3)
      self 	<trac.db.util.IterableCursor object at 0x809449328>
      sql 	'DELETE FROM tags WHERE tagspace=%s AND name=%s'

File "/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/main.py", line 423, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/web/main.py", line 197, in dispatch
  resp = chosen_handler.process_request(req)
File "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tracscreenshots/core.py", line 127, in process_requestFile "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tracscreenshots/core.py", line 338, in _do_actionsFile "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tracscreenshots/tags.py", line 42, in screenshot_createdFile "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tractags/api.py", line 226, in delete_tagsFile "build/bdist.freebsd-7.0-RELEASE-amd64/egg/tractags/api.py", line 150, in remove_resource_tagsFile "/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/local/lib/python2.5/site-packages/Trac-0.11-py2.5.egg/trac/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)

System Information:

User Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.1) Gecko/2008072820 Firefox/3.0.1
Trac: 	0.11
Python: 	2.5.2 (r252:60911, Jun 11 2008, 10:11:18) [GCC 4.2.1 20070719 [FreeBSD]]
setuptools: 	0.6c8
psycopg2: 	2.0.7
Genshi: 	0.5.1
Pygments: 	0.11.1
Subversion: 	1.5.1 (r32289)
SilverCity: 	0.9.7
jQuery:	1.2.3

Attachments (1)

tractags-postgresql83.patch (1.4 KB) - added by david@… 16 years ago.
Patch to use to_unicode for any instance of the tag "name" attribute.

Download all attachments as: .zip

Change History (7)

comment:1 Changed 16 years ago by david@…

The first sentence should read:

"As of PostgreSQL 8.3, many automatic casts to text were removed."

Changed 16 years ago by david@…

Attachment: tractags-postgresql83.patch added

Patch to use to_unicode for any instance of the tag "name" attribute.

comment:2 Changed 14 years ago by Michael Renzmann

Owner: changed from Alec Thomas to Michael Renzmann

Hmm, has this problem been fixed meanwhile? I'm runnung v0.6 of the plugin along with Trac 0.11.6 and PostgreSQL 8.3.9 on Debian Lenny, and I didn't spot any issues related to the tags plugin.

Can you please confirm that it still exists?

comment:3 Changed 13 years ago by John Hampton

I am also using TagsPlugin with 0.12-stable and PostgreSQL 8.4 and do not have any issues. Closing as I believe this is fixed in the trunk branch of tags. I think this issue can be closed.

comment:4 Changed 12 years ago by Steffen Hoffmann

Cc: Ryan J Ollos Michael Renzmann added; anonymous removed
Owner: changed from Michael Renzmann to Steffen Hoffmann

I'll do some further checking, but converting to unicode object on first read of tag name would be better than multiple times further down in the process as suggested here.

comment:5 Changed 10 years ago by Steffen Hoffmann

As I see now, this ticket's proposed changes have been applied when together with other API changes in [13429].

Version 0, edited 10 years ago by Steffen Hoffmann (next)

comment:6 Changed 10 years ago by Steffen Hoffmann

Resolution: fixed
Status: newclosed

In 13815:

TagsPlugin: Completing preparation for v0.7 release.

Availability of that code as stable, tagged release
closes #2429, #3359, #3610, #3624, #3677, #3754, #3864, #3947, #3983, #4078, #4277, #4503, #4799, #5523, #7787, #7857, #8638, #9057, #9058, #9059, #9060, #9061, #9062, #9063, #9149, #9210, #9521, #9630, #9636, #10032, #10416, #10636, #11096, #11147, #11152, #11274, #11302, #11658 and #11659.

Additionally there are some issues and enhancement requests showing progress,
but known to require more work to resolve them satisfactorily, specifically
refs #2804, #4200, #8747 and #9064.

Thanks to all contributors and followers, that enabled and encouraged a good
portion of this development work.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Steffen Hoffmann.
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.