Modify

Opened 6 years ago

Closed 5 months ago

#3677 closed defect (fixed)

Database operations fail with PostgreSQL 8.3

Reported by: david@… Owned by: hasienda
Priority: normal Component: TagsPlugin
Severity: normal Keywords: postgresql text cast
Cc: rjollos, otaku42 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@… 6 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 6 years ago by david@…

The first sentence should read:

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

Changed 6 years ago by david@…

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

comment:2 Changed 5 years ago by otaku42

  • Owner changed from athomas to otaku42

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 4 years ago by pacopablo

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 3 years ago by hasienda

  • Cc rjollos otaku42 added
  • Owner changed from otaku42 to hasienda

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 6 months ago by hasienda

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

Last edited 6 months ago by hasienda (previous) (diff)

comment:6 Changed 5 months ago by hasienda

  • Resolution set to fixed
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Action
as closed .
The resolution will be deleted. Next status will be 'reopened'.
Author


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

 
Note: See TracTickets for help on using tickets.