Modify

Opened 6 years ago

Closed 4 years ago

#2658 closed defect (fixed)

[Patch] Type Casts in Postgresl 8.3

Reported by: victorhg@… Owned by: coderanger
Priority: highest Component: MasterTicketsPlugin
Severity: blocker Keywords:
Cc: theyranos@…, shanec@…, nulleke76@…, dag.viggo@…, carlos@…, bernd.tegge@…, kmacinni@…, brad-trachacks@…, kuehn@… Trac Release: 0.11

Description

Hello,

I've been trying to use the MasterTicketsPlugin with postgresql 8.3. There are some issues with type checking (PSQGL has a strong type checking mechanism):

model.py

cursor.execute('SELECT dest FROM mastertickets WHERE source=%s ORDER BY dest', (self.tkt.id,))

should be

cursor.execute('SELECT dest FROM mastertickets WHERE source='%s' ORDER BY dest', (self.tkt.id,))

Am i right?? I'm receiveing the psql error:

2008-02-29 11:38:17 BRT HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
2008-02-29 11:38:17 BRT COMMAND:  SELECT dest FROM mastertickets WHERE source=1 ORDER BY dest

The TracInstall comes with the follwing alert, related with the problem described above:
Warning: PostgreSQL 8.3 uses a strict type checking mechanism. To use Trac with the 8.3 Version of PostgreSQL, you will need trac-0.11 or later.

thanks

Attachments (11)

model.py.diff (1.1 KB) - added by anonymous 6 years ago.
fix
model.py.2.diff (1.1 KB) - added by anonymous 6 years ago.
fix
model.py.unified.diff (2.4 KB) - added by brad-trachacks@… 6 years ago.
Same change model.py.diff but as unified diff.
pg-mastertickets-types.patch (542 bytes) - added by mixedpuppy 6 years ago.
fix table creation to deal with strict types for postgres
mastertickets-intfields-patch.2.diff (2.5 KB) - added by luke@… 6 years ago.
This patch changes the database schema to use integers instead of strings, and provides the necessary code to upgrade from the previous database schema.
mastertickets-intfields-patch.3.diff (3.2 KB) - added by luke@… 6 years ago.
This replaces my previous patch, and fixes a case where a ticket number was unsuccessfully being compared to its string representation.
mastertickets-hs.patch (2.4 KB) - added by henrik.steensland@… 4 years ago.
Patch based on mastertickets-intfields-patch.3.diff, but actually deletes rows from masterticket table
mastertickets-intfields-hs-combined.patch (4.2 KB) - added by matt.caron@… 4 years ago.
Combined patch of mastertickets-hs.patch and mastertickets-intfields-patch.3.diff
mastertickets-intfields-hs-combined_rev2.patch (4.3 KB) - added by matt.caron@… 4 years ago.
Rev 2 of combined patck
mastertickets-intfields-hs-combined_rev2a.patch (4.3 KB) - added by matt.caron@… 4 years ago.
Rev 2a of combined patch (fixes accidental dropping of typecast)
mastertickets-intfields-hs-combined_rev2b.patch (4.4 KB) - added by matt.caron@… 4 years ago.
Rev 2b of combined patch (fixes accidental dropping of typecast, and malformed previous patch)

Download all attachments as: .zip

Change History (36)

comment:1 Changed 6 years ago by victorhg@…

  • Trac Release changed from 0.10 to 0.11

sorr

comment:2 Changed 6 years ago by anonymous

  • Cc nulleke76@… added

comment:3 Changed 6 years ago by dag.viggo@…

  • Cc dag.viggo@… added

comment:4 Changed 6 years ago by anonymous

  • Cc carlos@… added

comment:5 Changed 6 years ago by anonymous

  • Cc bernd.tegge@… added

comment:6 Changed 6 years ago by anonymous

  • Cc kmacinni@… added

Changed 6 years ago by anonymous

fix

Changed 6 years ago by anonymous

fix

comment:7 Changed 6 years ago by apatrushev@…

diff -r /tmp/masterticketsplugin/0.11/mastertickets/model.py mastertickets/model.py
22c22
<         cursor.execute('SELECT dest FROM mastertickets WHERE source=%s ORDER BY dest', (self.tkt.id,))
---
>         cursor.execute('SELECT dest FROM mastertickets WHERE source=%s ORDER BY dest', (str(self.tkt.id),))
26c26
<         cursor.execute('SELECT source FROM mastertickets WHERE dest=%s ORDER BY source', (self.tkt.id,))
---
>         cursor.execute('SELECT source FROM mastertickets WHERE dest=%s ORDER BY source', (str(self.tkt.id),))
53c53
<                     cursor.execute('INSERT INTO mastertickets (%s, %s) VALUES (%%s, %%s)'%sourcedest, (self.tkt.id, n))
---
>                     cursor.execute('INSERT INTO mastertickets (%s, %s) VALUES (%%s, %%s)'%sourcedest, (str(self.tkt.id), str(n)))
57c57
<                     cursor.execute('DELETE FROM mastertickets WHERE %s=%%s AND %s=%%s'%sourcedest, (self.tkt.id, n))
---
>                     cursor.execute('DELETE FROM mastertickets WHERE %s=%%s AND %s=%%s'%sourcedest, (str(self.tkt.id), str(n)))
62c62
<                                    (n, field))
---
>                                    (n, str(field)))

Changed 6 years ago by brad-trachacks@…

Same change model.py.diff but as unified diff.

comment:8 Changed 6 years ago by brad-trachacks@…

  • Cc brad-trachacks@… added

Just ran into the same problem and was able to fix it with the
model.py.diff patch. (I have no idea if that's a good solution.)

The attached model.py.diff and model.py.2.diff files are identical:

5d543b1e2db100be8d4a382cf264da21  model.py.diff
5d543b1e2db100be8d4a382cf264da21  model.py.2.diff

FWIW, I attached a unified diff version.

comment:9 Changed 6 years ago by khym@…

Shouldn't that last chunk in the diff change n to str(n), rather than field to str(field)?

comment:10 Changed 6 years ago by mixedpuppy

  • Cc shanec@… added

since dest and source contain ticket id, why use a text field? wouldn't a simpler approach be to change the table to have integer fields?

Changed 6 years ago by mixedpuppy

fix table creation to deal with strict types for postgres

Changed 6 years ago by luke@…

This patch changes the database schema to use integers instead of strings, and provides the necessary code to upgrade from the previous database schema.

comment:11 follow-up: Changed 6 years ago by luke@…

I've supplied a patch above that allows the database schema to be upgraded from the previous version that uses text fields instead of integers. I performed the following tests.

  • New install
    1. Set up a clean Trac install with no previous mastertickets tables of records in the database
    2. Installed my patched version of mastertickets
    3. ran trac-admin /var/lib/trac-alpha upgrade --no-backup
    4. Started trac, created tickets with blocked by/blocking fields
    5. Verified UI display of the fields
    6. Verified tables and records in the database
    7. TEST OK


  • Upgraded install
    1. Set up a clean Trac install with no previous mastertickets tables of records in the database
    2. Installed mastertickets with this patch which uses version 1 of the database schema
    3. ran trac-admin /var/lib/trac-alpha upgrade --no-backup
    4. Started trac, created tickets with blocked by/blocking fields
    5. Replaced mastertickets with my patched version
    6. ran trac-admin /var/lib/trac-alpha upgrade --no-backup
    7. Started trac, verified my old tickets, created new ones too
    8. Verified tables and records in the database
    9. TEST OK


comment:12 in reply to: ↑ 11 ; follow-up: Changed 6 years ago by mixedpuppy

Replying to luke@zymeworks.com:

I've supplied a patch above that allows the database schema to be upgraded from the previous version that uses text fields instead of integers. I performed the following tests.

Your patch is empty.

comment:13 in reply to: ↑ 12 Changed 6 years ago by anonymous

Replying to mixedpuppy:

Your patch is empty.

It's there, but you have to download it in the original format. Trac-hacks isn't parsing it for some reason, but I can't see anything wrong with the formatting. It's simply the output of svn diff. The same problem happens with the first two attachments on this ticket.

Changed 6 years ago by luke@…

This replaces my previous patch, and fixes a case where a ticket number was unsuccessfully being compared to its string representation.

comment:14 Changed 5 years ago by johnwilliams@…

I'd love to use this plugin for its blocking/blocked by fields, but tickets become unusable (see server output below) when I install the plugin and upgrade the database. I think it's related to this ticket, so I tried the various patches posted above but without success. I'm running Trac 0.11, Apache 2.2.9, and Postgres 8.3.7 on Ubuntu Intrepid.

Is the issue still being looked into? Is there any other information you need from me?

Here is the output of my server when I try to view a ticket:

Trac detected an internal error:

ProgrammingError: operator does not exist: text = integer
LINE 1: SELECT dest FROM mastertickets WHERE source=3 ORDER BY dest
                                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Python Traceback

Most recent call last:
File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 423, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 209, in dispatch
  self._post_process_request(req, *resp)
File "/usr/lib/python2.5/site-packages/trac/web/main.py", line 299, in _post_process_request
  resp = f.post_process_request(req, *resp)
File "build/bdist.linux-i686/egg/mastertickets/web_ui.py", line 45, in 
  post_process_requestFile "build/bdist.linux-i686/egg/mastertickets/model.py", 
  line 22, in __init__File "/usr/lib/python2.5/site-packages/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/db/util.py", line 50, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)


System Information:

User Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.0.8) Gecko/2009032609 Firefox/3.0.8
Trac: 	0.11
Python: 	2.5.2 (r252:60911, Oct 5 2008, 19:42:18) [GCC 4.3.2]
setuptools: 	0.6c9
psycopg2: 	2.0.8
Genshi: 	0.5.1
mod_python: 	3.3.1
Pygments: 	0.10
Subversion: 	1.5.1 (r32289)
jQuery:	1.2.3

comment:15 Changed 5 years ago by mikero

I'm hitting similar problems and we're also massively keen to move forward with this module. Is there any more data beyond the below I can provide that would help?

Trac detected an internal error:
ProgrammingError: operator does not exist: text = integer LINE 1: SELECT dest FROM mastertickets WHERE source=415 ORDER BY des... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
This is probably a local installation issue.

Python Traceback

Most recent call last:
File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/web/main.py", line 444, in _dispatch_request
  dispatcher.dispatch(req)
File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/web/main.py", line 216, in dispatch
  self._post_process_request(req, *resp)
File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/web/main.py", line 308, in _post_process_request
  resp = f.post_process_request(req, *resp)
File "build/bdist.linux-x86_64/egg/mastertickets/web_ui.py", line 45, in post_process_requestFile "build/bdist.linux-x86_64/egg/mastertickets/model.py", line 22, in __init__File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/db/util.py", line 59, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)
File "/usr/lib/python2.5/site-packages/Trac-0.11.5-py2.5.egg/trac/db/util.py", line 59, in execute
  return self.cursor.execute(sql_escape_percent(sql), args)


System Information:

User Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_5_7; en-us) AppleWebKit/525.28.3 (KHTML, like Gecko) Version/3.2.3 Safari/525.28.3

Trac:	0.11.5
Python:	2.5.2 (r252:60911, Jul 22 2009, 15:33:10) [GCC 4.2.4 (Ubuntu 4.2.4-1ubuntu3)]
setuptools:	0.6c8
psycopg2:	2.0.6
Genshi:	0.5.1
Pygments:	1.0
RPC:	1.0.6
jQuery:	1.2.6

comment:16 Changed 5 years ago by mikero

apatrushev@…'s patch has helped - apologies for not reading the full thread first.

comment:17 Changed 5 years ago by anonymous

  • Cc kuehn@… added

comment:18 Changed 5 years ago by anonymous

Can a working patch be incorporated into the official version? It looks like the mastertickets-intfields-patch.3.diff patch is the most useful one. I'm using it now but it would be best if the fix was incorporated into the official version in svn.

comment:19 Changed 5 years ago by theyranos@…

  • Cc theyranos@… added

#6378 has been closed as a duplicate of this ticket.

comment:20 Changed 4 years ago by AdrianFritz

  • Summary changed from Type Casts in Postgresl 8.3 to [Patch] Type Casts in Postgresl 8.3

Changed 4 years ago by henrik.steensland@…

Patch based on mastertickets-intfields-patch.3.diff, but actually deletes rows from masterticket table

comment:21 follow-up: Changed 4 years ago by henrik.steensland@…

The problem I had with mastertickets-intfields-patch.3.diff was that once you had added a blocked_by or blocking and then later attempted to remove it, it would remain in the masterticket table, even though it would be deleted from ticket_custom.

comment:22 in reply to: ↑ 21 Changed 4 years ago by matt.caron@…

Replying to henrik.steensland@gmail.com:

The problem I had with mastertickets-intfields-patch.3.diff was that once you had added a blocked_by or blocking and then later attempted to remove it, it would remain in the masterticket table, even though it would be deleted from ticket_custom.

FYI - Your patch is backwards and needs to be applied with patch -R.

Changed 4 years ago by matt.caron@…

Combined patch of mastertickets-hs.patch and mastertickets-intfields-patch.3.diff

comment:23 Changed 4 years ago by matt.caron@…

Added meta-patch which combines mastertickets-intfields-patch.3.diff and mastertickets-hs.patch.

Rationale:

  • mastertickets-hs.patch was in the wrong order and had to be applied with patch -R
  • mastertickets-hs.patch lacked the "save the old DB data" migration code which was in mastertickets-intfields-patch.3.diff - useful if you have existing data
  • mastertickets-hs.patch failed to increment the DB version

Changed 4 years ago by matt.caron@…

Rev 2 of combined patck

comment:24 Changed 4 years ago by matt.caron@…

Updated meta patch.

  • Fixes an issue where a join would fail because it expects a string
  • Fixes an issue where you couldn't delete dependencies once established.

Changed 4 years ago by matt.caron@…

Rev 2a of combined patch (fixes accidental dropping of typecast)

Changed 4 years ago by matt.caron@…

Rev 2b of combined patch (fixes accidental dropping of typecast, and malformed previous patch)

comment:25 Changed 4 years ago by coderanger

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

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from coderanger. Next status will be '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.