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).

CommentFileSizeAuthor
#15 views_install_pgsql_updates.patch_2.txt4.64 KBdww
#10 views_install_update_14_postgres.patch1.55 KBHorsePunchKid
#9 views_install_pgsql_updates.patch.txt4.34 KBdww
#6 views_install_pgsql_int_updates.patch.txt2.96 KBdww
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

dww’s picture

dww
we/he/they
CreditAttribution: dww commented
Assigned: Unassigned » dww

i'll roll a patch for this.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

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.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

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.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented

Please use the 2nd version. =)

HorsePunchKid’s picture

HorsePunchKid CreditAttribution: HorsePunchKid commented

Great! I'm up to 12 now, though note that Postgres wants just UPDATE instead of UPDATE TABLE. Thanks for the quick response!

dww’s picture

dww
we/he/they
CreditAttribution: dww commented
Status: Active » Needs review
FileSize
views_install_pgsql_int_updates.patch.txt2.96 KB

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.

HorsePunchKid’s picture

HorsePunchKid CreditAttribution: HorsePunchKid commented
Status: Needs review » Active

On checking, I don't think the default syntax itself is an issue; in fact, I patched image_attach to support Postgres and used that same syntax.

However, Postgres doesn't understand int, I believe, and should instead get integer. I think longblob is a problem, too, though I'm not sure yet what to replace it with.

HorsePunchKid’s picture

HorsePunchKid CreditAttribution: HorsePunchKid commented

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.

dww’s picture

dww
we/he/they
CreditAttribution: dww commented
Status: Active » Needs review
FileSize
views_install_pgsql_updates.patch.txt4.34 KB

here's a more complete version of the patch i posted in #6, only this one fixes views_make_cache_table(), too.

HorsePunchKid’s picture

HorsePunchKid CreditAttribution: HorsePunchKid commented
FileSize
views_install_update_14_postgres.patch1.55 KB

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!

HorsePunchKid’s picture

HorsePunchKid CreditAttribution: HorsePunchKid commented

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!

dww’s picture

dww
we/he/they
CreditAttribution: dww commented

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

stormsweeper’s picture

stormsweeper CreditAttribution: stormsweeper commented

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).

dww’s picture

dww
we/he/they
CreditAttribution: dww commented
Status: Needs review » Needs work

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.

dww’s picture

dww
we/he/they
CreditAttribution: dww commented
Status: Needs work » Needs review
FileSize
views_install_pgsql_updates.patch_2.txt4.64 KB

thanks for pointing out the error in my ways, stormsweeper... this is much better.

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Needs review » Patch (to be ported)

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).

merlinofchaos’s picture

merlinofchaos CreditAttribution: merlinofchaos commented
Status: Patch (to be ported) » Closed (fixed)

We are no longer backporting to 4.7.