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

Update bug.

From: "Rick Szeto" <rszeto(at)csi(dot)ca>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Update bug.
Date: 2002-05-06 17:58:39
Message-ID: scd68c2a.004@toronto.csi.ca (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi all,
   I have encountered a bug that I think is a major problem.
I have a table as follows:
  CREATE TABLE registration (
    registration_id    int PRIMARY KEY;
    course_name     varchar(6) NOT NULL;
    course_code      varchar(6) NOT NULL;
    etc...
)

Note: There is no index on course_name and/or course_code;

I needed to update the values in course_name and course_code columns to remove trailling whitespace.
(you don't want to know how they got there...). So I used this statement to update it:

UPDATE registration SET course_name = trim(course_name), course_code = trim(course_code);

At first I just thought that the operation took a long time(it does have over 300k entries), so I left it over night to run.
But when I got back the next morning, the process was still not finished. When I looked at the CPU usage using 'top',
postgres was basically sitting idle. I had to use the a PL/pgsql function to operate on the table for it to do the update, 
it was slow, but at least it did the job.

Configuration:
    RH Linux 7.2 on Intel P4 1.6 GHz.
    PostgreSQL 7.2.1

Any reason why this is happening?

Thanks for your help,
Rick



pgsql-bugs by date

Next:From: Tom LaneDate: 2002-05-06 18:44:35
Subject: Re: Bug in functions lower(), upper() with SQL_ASCII and LATIN1?
Previous:From: pgsql-bugsDate: 2002-05-06 17:43:15
Subject: Bug #658: psql does not start on cygwin

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