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: (view raw, whole thread or download thread mbox)
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;

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.

    RH Linux 7.2 on Intel P4 1.6 GHz.
    PostgreSQL 7.2.1

Any reason why this is happening?

Thanks for your help,

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-2017 The PostgreSQL Global Development Group