[ic] Options table with Postgresql & 4.9.7

Brian Kosick interchange-users@icdevgroup.org
Fri Apr 4 16:40:01 2003


Kevin Old wrote:

>On Fri, 2003-04-04 at 14:21, Brian Kosick wrote:
>  
>
>>Kevin Old wrote:
>>
>>    
>>
>>>Hello everyone,
>>>
>>>I'm playing around with 4.9.7 and am getting the following error when
>>>just trying to setup a demo store with Postgresql 7.2.1.
>>>Configuring catalog foundationdb...Using PostgreSQL,
>>>DSN=dbi:Pg:dbname=foundationdb...NOTICE:  CREATE TABLE / PRIMARY KEY
>>>will create implicit index 'options_pkey' for table 'options'
>>>table options created: create table options ( 
>>>code varchar(64) primary key NOT NULL,
>>>sku varchar(64),
>>>o_group varchar(20),
>>>o_sort varchar(16),
>>>o_default varchar(64),
>>>o_label text,
>>>o_value text,
>>>o_widget text,
>>>o_height int,
>>>o_width int,
>>>description text,
>>>price varchar(20),
>>>wholesale varchar(20),
>>>display_type text
>>>)
>>>
>>>DBI: Post creation query 'CREATE  INDEX options_o_enable ON options
>>>(o_enable)' failed: ERROR:  DefineIndex: attribute "o_enable" not found
>>>DBI: Post creation query 'CREATE  INDEX options_o_master ON options
>>>(o_master)' failed: ERROR:  DefineIndex: attribute "o_master" not found
>>>table 'options' failed: options import failed: ERROR:  value too long
>>>for type character varying(20)
>>>
>>>I have Postgres working with 4.9.6 and I even copied over the
>>>options.pgsql file from my 4.9.6 dist, did a diff and tried to build the
>>>table.
>>>
>>>What's happening is that the sql statement doesn't include the o_enable
>>>and o_master fields.....even though they're explicitly defined in in the
>>>options.pgsql file.
>>>
>>>Any ideas?
>>>
>>>Kevin
>>>
>>> 
>>>
>>>      
>>>
>>The problem is not interchange, but with postgres.  as of 7.2 the 
>>default behavior of postgres has changed.  From truncating text when it 
>>is too long to fit in the field to rejecting the transaction.  The 
>>solution is to bump up the VARCHAR length in the <tablename>.pgsql 
>>file.  This has all ready been documented on the list before.
>>
>>    
>>
>
>Brian,
>
>Well, that is somewhat true.  I have fixed some of those errors in other
>tables, but this problem, seems to be that IC never puts them in the
>table definition.....have a look and the create table script above and
>you'll see that o_enable or o_master aren't in there.  They are
>explicitly defined in the options.pgsql file.  Here's a dump of my 4.9.6
>options table running on the same installation of Postgres (7.2.1):
>
>
>CREATE TABLE "options" (
>   "code" varchar(64) NOT NULL,
>   "o_master" varchar(64) DEFAULT '' NOT NULL,
>   "sku" varchar(64) DEFAULT '' NOT NULL,
>   "o_group" varchar(20) DEFAULT '' NOT NULL,
>   "o_sort" varchar(16) DEFAULT '' NOT NULL,
>   "phantom" varchar(1),
>   "o_enable" varchar(1) DEFAULT '' NOT NULL,
>   "o_matrix" varchar(1),
>   "o_modular" varchar(1),
>   "o_default" varchar(64),
>   "o_label" text,
>   "o_value" text,
>   "o_widget" text,
>   "o_footer" text,
>   "o_header" text,
>   "o_height" int4,
>   "o_width" int4,
>   "description" text,
>   "price" varchar(20),
>   "wholesale" varchar(20),
>   "differential" varchar(20),
>   "weight" varchar(20),
>   "volume" varchar(20),
>   "mv_shipmode" varchar(128),
>   "o_exclude" text,
>   "o_include" text,
>   CONSTRAINT "options_pkey" PRIMARY KEY ("code")
>);
>CREATE  INDEX "options_code" ON "options" ("code");
>CREATE  INDEX "options_o_enable" ON "options" ("o_enable");
>CREATE  INDEX "options_o_group" ON "options" ("o_group");
>CREATE  INDEX "options_o_master" ON "options" ("o_master");
>CREATE  INDEX "options_o_sort" ON "options" ("o_sort");
>CREATE  INDEX "options_sku" ON "options" ("sku");
>GRANT ALL ON "options" TO "interch";
>
>I can manually create this table and everything works fine in Postgres,
>and the table is created.  But, even though the table is there, IC throws up a
>notice during the startup a seems to try and create the table again.  Is there 
>a way I can disable this so that it starts cleanly?
>
>Thanks,
>Kevin
>  
>

touch the file options.sql in the products folder.  IC will not look to 
create the SQL table anymore....

Without looking at IC 4.9.7, I would guess then that the products.pgsql 
file, and the products.txt fields are out of sync.