Cache-related update queries fail against Postgres
HorsePunchKid - April 20, 2007 - 19:09
| Project: | Views |
| Version: | 5.x-1.6-beta4 |
| Component: | Code |
| Category: | bug report |
| Priority: | normal |
| Assigned: | dww |
| Status: | closed |
Description
I'm running a Drupal 5.1 site on Postgres 8.x with Views module version 5.x-1.6-beta4. The upgrade script fails with errors relating to the (new?) caching functionality:
# user warning: query: ALTER TABLE d_view_view ADD is_cacheable int(1) in .../drupal/includes/database.pgsql.inc on line 144.
# warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "(" at character 252 in .../drupal/includes/database.pgsql.inc on line 125.
# user warning: query: CREATE TABLE d_cache_views ( cid varchar(255) NOT NULL default '', data longblob, expire int NOT NULL default '0', created int NOT NULL default '0', headers text, PRIMARY KEY (cid), INDEX expire (expire) ) /*!40100 DEFAULT CHARACTER SET UTF8 */ in .../drupal/includes/database.pgsql.inc on line 144.The errors start on update 12 and continue from there through 14, since the table doesn't get created. The resulting messages on the update page under "the following queries" are:
* Failed: ALTER TABLE {view_view} ADD is_cacheable int(1)
* Failed: CREATE TABLE {cache_views} ( cid varchar(255) NOT NULL default '', data longblob, expire int NOT NULL default '0', created int NOT NULL default '0', headers text, PRIMARY KEY (cid), INDEX expire (expire) ) /*!40100 DEFAULT CHARACTER SET UTF8 */
* Failed: DELETE FROM {cache_views}I don't have a patch to submit yet, but I think the MySQL-specific parts there are evident. In particular, I suspect the "default" syntax, "INDEX" syntax, and charset comment need to be reworked for Postgres. I'm not sure why the is_cacheable alteration failed; perhaps Postgres doesn't like int(1).
#1
i'll roll a patch for this.
#2
This will work around the problem for you until we can get this patched for real:
Execute these queries:
ALTER TABLE d_view_view ADD is_cacheable tinyint;UPDATE TABLE d_system SET schema_version = 12 WHERE name = 'views';
Then go back to update.php and update -- you will see a bunch of failures for things that are already done, but the is_cacheable related items should succeed. That'll get you into a working state.
#3
This will work around the problem for you until we can get this patched for real:
Execute these queries:
ALTER TABLE d_view_view ADD is_cacheable smallint;UPDATE TABLE d_system SET schema_version = 12 WHERE name = 'views';
Then go back to update.php and update -- you will see a bunch of failures for things that are already done, but the is_cacheable related items should succeed. That'll get you into a working state.
#4
Please use the 2nd version. =)
#5
Great! I'm up to 12 now, though note that Postgres wants just
UPDATEinstead ofUPDATE TABLE. Thanks for the quick response!#6
this should work fine. haven't had time to fully test on pgsql and mysql yet, but i wanted to post the patch first so others can test, too.
#7
On checking, I don't think the
defaultsyntax itself is an issue; in fact, I patchedimage_attachto support Postgres and used that same syntax.However, Postgres doesn't understand
int, I believe, and should instead getinteger. I thinklongblobis a problem, too, though I'm not sure yet what to replace it with.#8
Sorry this isn't in patch form yet, but these queries appear to be good Postgres equivalents of the existing queries:
CREATE TABLE d_cache_views ( cid varchar(255) NOT NULL default '', data bytea, expire integer NOT NULL default '0', created integer NOT NULL default '0', headers text, PRIMARY KEY (cid));
CREATE INDEX d_cache_views_expire ON d_cache_views (expire);
I can roll a patch for the install file once I've tested it properly.
#9
here's a more complete version of the patch i posted in #6, only this one fixes views_make_cache_table(), too.
#10
Okay, here's an untested patch; untested because I've already upgraded and don't have a simple way to downgrade. If somebody else could test, that'd be lovely, otherwise I can work it out one way or another!
#11
Ah, thanks much dww! Feel free to check out my patch (it's somewhat validating that the patches to the cache table function are similar), but it's wholly redundant to yours. :-)
Thanks for the quick response, too!
#12
for future reference, #10 won't work, since you can't define keys/indexes directly inside table definitions like that in pgsql. so, folks should focus on testing #9 (which also fixes other updates, too).
cheers,
-derek
#13
You absolutely may define primary keys that way in Postgres 7.3+:
http://www.postgresql.org/docs/7.3/interactive/ddl-constraints.html#AEN1849
The index is implicitly created (in this case, the default is cache_views_pkey).
#14
ahh, upon further inspection, all 3 of us are wrong. ;) you can define a primary key, but you most certainly can not define the additional index. so, the patch in #10 will fail if you try to install it on pgsql because of this:
+ INDEX expire (expire)my patch from #9 works, but is wrong because we want cid to be more than an index, we need it to be unique. so, it should use this:
+ PRIMARY KEY (cid),instead of this:
+ $ret[] = update_sql("CREATE INDEX {cache_views}_cid_idx ON {cache_views} (cid)");i'll re-roll momentarily, stay tuned.
#15
thanks for pointing out the error in my ways, stormsweeper... this is much better.
#16
dww's patch in #15 applied. Can I please get this patch backported to 4.7? (I'd do it but I have yet to set up pgsql anywhere to test).
#17
We are no longer backporting to 4.7.