We help IT Professionals succeed at work.
Get Started

How to fix a slightly corrupted table on PostgreSQL 9.0.4

sevzas
sevzas asked
on
1,001 Views
Last Modified: 2012-08-10
SQL select is returning strange results on a table that I have:

query:
select DISTINCT symbol from historical_prices where symbol='ACAS'

results:
"ACAS"
"AMAT"
"ACAS"

So the table seems to be slightly corrupt.  One fix that I confirmed that works in a test environment is to export all contents to a flat file, truncate the table and import again.

The complication is that the table has over a million rows and the database is replicated using streaming replication.  I'm afraid that dump/delete/reload of such a large table might kill the replication.

Are there any suggestions?  Perhaps some form of the VACUUM command?

Here are the details on this table as prepared by PGAdminIII:

Property Value
Name historical_prices
OID 24783
Owner produser
Tablespace pg_default
ACL  
Of type  
Primary key symbol, date
Rows (estimated) 1096900
Fill factor  
Rows (counted) not counted
Inherits tables No
Inherited tables count 0
Has OIDs? No
System table? No
Comment  


-- Table: historical_prices--
DROP TABLE historical_prices;
CREATE TABLE historical_prices(
symbol text NOT NULL,  
date date NOT NULL,  
open numeric(6,2),  
high numeric(6,2),  
low numeric(6,2),  
"close" numeric(6,2),  
adjusted_close numeric(6,2),  
datecreated timestamp without time zone DEFAULT now(),  
lastupdated timestamp without time zone DEFAULT now(),  
CONSTRAINT "PrimaryKey" PRIMARY KEY (symbol, date))
WITH (  OIDS=FALSE);

ALTER TABLE historical_prices OWNER TO proddb;
-- Trigger: audit_trig on historical_prices--
DROP TRIGGER audit_trig ON historical_prices;
CREATE TRIGGER audit_trig  BEFORE INSERT OR UPDATE  ON historical_prices  
FOR EACH ROW  EXECUTE PROCEDURE audit_trig_fn();
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE