Installing the UC Attribute module failed because it tries to create 3 tables with a filed named 'oid', which conflicts with the Postgres system oid field if the database is configured with default_with_oids=on:
- uc_product_options
- uc_class_attribute_options
- uc_attribute_options

From what I've read, this general problem has been reported a year or so back on a D5 version of uc_order_quotes #224070: ´oid´ field in uc_order_quotes error on Postgresql8. In fact in uc_attribute.install function uc_attribute_update_8() even seems to 'attempting' to fix this problem by doing updates like "ALTER TABLE {uc_attribute_options} SET WITHOUT OIDS", but unfortunately you can't alter a table after it failed to create in the first place!

I've got round the problem by temporarily manually doing "ALTER DATABASE drupal SET default_with_oids=off" before installing the UC_Attribute module.

It would seem better if there were a way to explicitly specify "WITHOUT OIDS" in the schema, but I can't immediately see that this can be done (http://drupal.org/node/146939).

Comments

tr’s picture

tr commented
Version: 6.x-2.2 » 7.x-3.x-dev
Category: bug » task

I guess we could rename that column to avoid the issue with PostgreSQL, but since attribute options are a fundamental part of Ubercart there are many places that need to be changed, plus update functions need to be written to change the old tables to the new column names. The big issue is going to be with contributed modules - LOTS of contributed modules use/manipulate attribute data, and making this change is sure to break each and every one of them.

So I'm going to mark this as something that needs to be fixed for Ubercart 7.x-3.x, since a major point release is the right place to make such a big change. Part of the fix will be to create/enhance the attribute API so that contributed modules can manipulate attributes and options without having to make direct DB queries - this will isolate any future changes to just Ubercart core.

Drupal 6 users with PostgreSQL should be able to avoid the issue with your ALTER query.

It would be nice to have this handled in Drupal core, rather than needing to patch each and every module that uses oid as a column name (Ubercart isn't the first, and won't be the last...). Maybe you can read up on the oid issue in the Drupal issue queue and contribute to the fix there - people who know PostgreSQL and are active in reviewing these fixes are in short supply.

mesho’s picture

mesho commented

I have same problem (drupal 6), but I didn't want to change .module files in all of modules with this error. Using alter is uselles, becose is running after is table created.

I find that function in include/database.pgsql.inc - db_query(); run query in string, So i add to that function this lines (yes, yes, core hack, kitten will by killed)

if (strpos("##".$query,'CREATE TABLE') > 0) {
$query = $query . " WITHOUT OIDS ";
}

before line

return _db_query($query);

After that no problem. I hope that this will help somebody. And beware of core updates ;) If this won't be solved in next one, you must write this line after you do each one.

liam morland’s picture

liam morland
Primary language English
Location Ontario, CA 🇨🇦
commented
Issue tags: +PostgreSQL
tr’s picture

tr commented
Status: Active » Closed (won't fix)

I think it's correct to say this isn't a problem for Drupal 7 because D7 requires PostgreSQL 8.3+ where WITHOUT OIDS is the default.

For Drupal 6 this can still happen. However, there is a workaround above, so I don't see any need to try to implement the broad-reaching necessary to deal with this issue in D6. Changing a column name in uc_attributes would affect dozens of contributed modules, and since we've never had any significant help from the community with PostgreSQL-related issues, I feel even if we did make a patch it wouldn't be adequately tested. The potential to break the code for the vast majority of users who are on MySQL is too great to invest the effort here for such a little payoff.

Marking as "won't fix". Feel free to reopen the issue if the problem still exists in D7, but I suspect the proper solution belongs in the Drupal core issue queue as the DB abstraction layer is supposed to insulate module developers from things like different reserved words in different databases.