Skip site navigation (1) Skip section navigation (2)

Re: String Translation

From: David Wheeler <david(at)kineticode(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: String Translation
Date: 2003-10-16 17:39:05
Message-ID: A33608E2-FFFF-11D7-9D1B-0003931A964A@kineticode.com (view raw or flat)
Thread:
Lists: sfpug
On Thursday, October 16, 2003, at 10:05  AM, Josh Berkus wrote:

> So your purpose is to eliminate all non-[0-9A-Za-z_] characters?

Yeah, and change them into "_".

> That's rather odd ... I've done search-and-replace using Perl on 
> 30,000 large
> text fields in a couple minutes.    Does this field have foriegn keys 
> and/or
> several indexes on it?

Yes, here's the table declaration:

CREATE TABLE story_data_tile (
     id                   NUMERIC(10,0)  NOT NULL
                                         DEFAULT 
NEXTVAL('seq_story_data_tile'),
     name                 VARCHAR(64)    NOT NULL,
     key_name             VARCHAR(64)    NOT NULL,
     description          VARCHAR(256),
     element_data__id     NUMERIC(10,0)  NOT NULL,
     object_instance_id   NUMERIC(10,0)  NOT NULL,
     parent_id            NUMERIC(10,0)  NOT NULL,
     hold_val             NUMERIC(1,0)   NOT NULL
                                         DEFAULT 0
                                         CONSTRAINT ck_sd_tile__hold_val
                                           CHECK (hold_val IN (0,1)),

     place                NUMERIC(10,0)  NOT NULL,
     object_order         NUMERIC(10,0)  NOT NULL,
     date_val             TIMESTAMP,
     short_val            TEXT,
     blob_val             TEXT,
     active               NUMERIC(1,0)   NOT NULL
                                         DEFAULT 1
                                         CONSTRAINT ck_sd_tile__active
                                           CHECK (active IN (0,1)),

     CONSTRAINT pk_story_data_tile__id PRIMARY KEY (id)
);

CREATE INDEX idx_story_data_tile__key_name ON 
story_data_tile(LOWER(key_name));
CREATE INDEX fkx_story_instance__sd_tile ON 
story_data_tile(object_instance_id);
CREATE INDEX fkx_element__sd_tile ON story_data_tile(element_data__id);
CREATE INDEX fkx_sc_tile__sd_tile ON story_data_tile(parent_id);

ALTER TABLE story_data_tile
ADD CONSTRAINT fk_story_instance__sd_tile FOREIGN KEY 
(object_instance_id)
	REFERENCES story_instance(id) ON DELETE CASCADE;

ALTER TABLE story_data_tile
ADD CONSTRAINT fk_sc_tile__sd_tile FOREIGN KEY (parent_id)
	REFERENCES story_container_tile(id) ON DELETE CASCADE;

ALTER TABLE story_data_tile
ADD CONSTRAINT fk_at_data__sd_tile FOREIGN KEY (element_data__id)
	REFERENCES at_data(id) ON DELETE CASCADE;

Perhaps I should drop the idx_story_data_tile__key_name index, make the 
changes, and then add it again? This is important, because while my 
database has only 2200 records, there will be Bricolage installations 
with millions of records in this table.

> Well, the new substring can do substitutions, but not neatly in the 
> s//g
> format the way Perl can.   What about PL/perl?

Ah, in 7.4? I don't see making users recompile pg or add in PL/perl 
just for a single, one-time upgrade script.

 > Not necessarily.  I think your execution time problem is not releated 
to the
> queries themselves, but to some major database dependency.
>
> Wanna show me, since I'll soon be getting paid to tune Bric anyway?

Yeah, send me your ssh public keys and I'll give you a login.

Cheers,

David

-- 
David Wheeler                                     AIM: dwTheory
david(at)kineticode(dot)com                              ICQ: 15726394
http://www.kineticode.com/                     Yahoo!: dew7e
                                                Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]


In response to

Responses

sfpug by date

Next:From: David WheelerDate: 2003-10-16 17:40:23
Subject: Re: String Translation
Previous:From: eleinDate: 2003-10-16 17:30:57
Subject: Re: String Translation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group