The install script for uc_order contains invalid SQL for postgres:
First the create table:

--- uc_order.install.orig       2008-01-10 14:36:14.000000000 +0100
+++ uc_order.install    2008-01-10 14:36:54.000000000 +0100
@@ -127,7 +127,7 @@
         delivery_street1 varchar(64) NOT NULL default '',
         delivery_street2 varchar(64) NOT NULL default '',
         delivery_city varchar(32) NOT NULL default '',
-        delivery_zone integer NOT NULL defualt 0,
+        delivery_zone integer NOT NULL default 0,
         delivery_postal_code varchar(10) NOT NULL default '',
         delivery_country integer NOT NULL default 0,
         billing_first_name varchar(32) NOT NULL default '',

Then in the updates:

    case 'pgsql':
      $ret[] = update_sql("ALTER TABLE {uc_orders} CHANGE delivery_zip delivery_postal_code VARCHAR(10) CHARACTER SET 
utf8 COLLATE utf8_general_ci NOT NULL default ''");
      $ret[] = update_sql("ALTER TABLE {uc_orders} CHANGE billing_zip billing_postal_code VARCHAR(10) CHARACTER SET ut
f8 COLLATE utf8_general_ci NOT NULL default ''");
      break;

CHANGE is a mysql thing. It has to be performed by a rename and a alter type in postgres. I am not prepared to venture into a patch for all this (the list goes on).

What is the status of the postgres support for ubercart? The above errors makes it unlikely that it can work at all.

CommentFileSizeAuthor
#5 ubercart_pgsqlfix.patch.txt71.59 KBsemafour

Comments

robin t’s picture

robin t commented
Status: Needs work » Active

Found this issue in the ubercart.org tracker:

http://www.ubercart.org/issue/2096/ubercart_and_postgresql

Looks like I was right about the missing postgres support. Support might be coming soon.

rszrama’s picture

rszrama commented

Yeah, I was under the impression that patkins' patch had made it into beta and was waiting for him to verify. The Uberteam is pretty ignorant of postgres in general, so if you can just post up proper queries even w/o making it into a patch we can copy/paste code in.

robin t’s picture

robin t commented

I was actually starting to do that (on a production site) but I quickly lost faith in it because of the many scripted updates to the original schema.

And even if you get the tables right you may need to review all the queries to see if they are postgres safe. There are quite many postgres SQL bugs in other (non ubercart) modules. Stuff like:

insert into sometable (id, ...) values ('', ...);

where id is a serial (auto increment). Apparently mysql just inserts the default next value. Postgres needs a literal "default" or implicit default.

I also found lots of '%d' in code where actual field is an integer. Postgres does not not like to get a number in a string.

Island Usurper’s picture

Island Usurper commented

I wouldn't worry about the database update functions. Either they're broken or not, but nobody should be using them on Postgres if the install functions didn't work in the first place.

I'm more worried about the queries in the modules. I don't think there are any problems like you mentioned, but there certainly could be others. Übercart really needs a code audit for Postgres support, but that takes time and effort that we don't have to spend.

semafour’s picture

semafour commented
StatusFileSize
new ubercart_pgsqlfix.patch.txt71.59 KB

I also posted this on the postgres thread at ubercart.org referenced above, but thought I would add it here too in case it is watched more.

Attached is a patch that should make Ubercart work with PostgreSQL. The patch is against a checkout of revision 811.

Most of the changes were simple: fixing typos in the pgsql installation queries, removing `ticks` around column names, and things like that. Some of the queries required more substantial changes. In these cases I added conditional logic to define the query based on the database type (mysql[i] or pgsql). This way the more-tested support for mysql should not be changed at all because of this patch.

I tested most of the functionality of most of the modules, with some exceptions. I did not test the 2Checkout, Cybersource, Importer, and Repeater modules at all, though based on a code review they should not have problems. I only performed limited testing on the Shipping and Shipping Quote modules.

If this patch can be applied for an upcoming release, I would love to hear feedback from anybody who can test ubercart with postgres. If you come across any errors please post them here (or is there a better place?), and I will be happy to fix them.

- Jeremy

rszrama’s picture

rszrama commented
Status: Active » Fixed

This issue has been addressed and can be followed here: http://www.ubercart.org/issue/2096/ubercart_and_postgresql

It is going to require continual input from Postgres folks, though, to make sure we stay compatible.

Anonymous’s picture

Anonymous (not verified) commented
Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.