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