#11015 closed defect (fixed)
db upgrade fails when using postgres backend
| Reported by: | Owned by: | branson | |
|---|---|---|---|
| Priority: | normal | Component: | DirectoryAuthPlugin |
| Severity: | major | Keywords: | |
| Cc: | Ryan J Ollos | Trac Release: | 1.0 |
Description
Attempting to use Postgres 9.1 backend with this plugin version 1.0.2 (Trac 1.0, AccountManager 0.4.3) gives the following error:
The upgrade failed. Please fix the issue and try again. ProgrammingError: type "blob" does not exist LINE 4: "data" blob
it looks like "blob" is not a valid type for postgres. If I replace "blob" with "bytea" (patch) it works but how do we handle this in a cross database way? I have to believe there is also code in the trac DB api that can handle these types but I could not find it.
Attachments (2)
Change History (15)
Changed 12 years ago by
| Attachment: | postgres_blob_datatype.diff added |
|---|
comment:1 Changed 12 years ago by
| Trac Release: | → 1.0 |
|---|
comment:2 Changed 12 years ago by
Even after my hack to switch to 'bytea', this plugin fails spectacularly when using postgres as a backend. I've tried finagling things around for a while now but I'm not familiar with any of the specifics (of Trac, db api, or postgres) and it would appear that a lot of the SQL statements in this plugin are not compatible with Postgres.
In the least, the INSERT OR REPLACE INTO statements need to be replaced with something but I couldn't find a clean way to do it. Help would be appreciated as I would really like to use this plugin in my Trac deployments with Postgres.
comment:3 Changed 12 years ago by
As I mentioned above (and someone discovered the problem for MySQL too in #10632), Postgres does not support a built-in upsert statement. So I have replaced that with an update/insert (in postgres_upsert.diff) which should be more portable but I've only tested it on Postgres as of now. Still need to a solution to the blob/bytea though.
comment:4 Changed 12 years ago by
Kyle, I think you are on the right path with your patch. Since all of our recent improvements to the plugin have been reverted by the author, I'm not going to waste my time on this one anymore. I'd suggest just forking it if you continue to have trouble and the issues don't get fixed.
comment:5 Changed 12 years ago by
Okay...well that is a bummer but thanks for the heads up. I was maintaining my own version to write and test the changes so I'll just stick with that. Thanks for your assistance!
comment:6 Changed 12 years ago by
It is particularly sad that we have such a completely broken plugin on trac-hacks and the author reverts fundamental fixes like [12776]/#10618, without even taking a moment to communicate with us. That defect shows that the developer hasn't taken the least bit of care in testing the plugin before posting it to the community, and to revert such a fundamental fix shows a complete lack of concern for others using the plugin.
Regarding your patch, the only concern I had with postgres_upsert.diff was that WHERE NOT EXISTS doesn't work on SQLite, as far as I can see (haven't tested the code). The solution there might be to just pull some of the SQL logic out into the Python code, like the PrivateCommentsPlugin does.
I have no clue what a good cross-db replacement for the blob data type is, but you might want to ask on the t:MailingList.
comment:8 Changed 9 years ago by
| Resolution: | → fixed |
|---|---|
| Status: | new → closed |
added a separate schema for PostreSQL
comment:9 Changed 9 years ago by
With proper use of the Trac database API it shouldn't be necessary to define different schemas for each database type. See trac:TracDev/DatabaseApi for more info.
comment:10 Changed 9 years ago by
Uhm, I checked this resource and found nothing for binary blobs.
The code then was inspired by trac/upgrades/db16.py
comment:11 Changed 9 years ago by
You'd need to figure out how to design the table to avoid blob types.
this is a hack