Hi Jeff,
Thanks again for your reply.
>If there are no constraint violations, do you still see the problem?
Yes, I've stripped it down to an empty table with a 10 row load and the pk index on the secondary node still corrupts.
> Were there any older version on which it worked?
I'm afraid I started on 9.1.5, I upgraded to 9.1.6 when I had the initial problem due to the bugfix in the changelog to do with corrupt indexes on the secondary node but it hasn't resolved the issue.
> Can
you post a minimal schema and control file to reproduce the problem?
I've attached a text file with details for table, load config file etc, is that everything you would need?
Cheers,
James
From: Jeff Janes <jeff.janes@xxxxxxxxx>
To: James Cowell <jcowell@xxxxxxxxxxxxxx>
Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, 10 December 2012, 16:53
Subject: Re: Corrupt indexes on slave when using pg_bulkload on master
To: James Cowell <jcowell@xxxxxxxxxxxxxx>
Cc: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, 10 December 2012, 16:53
Subject: Re: Corrupt indexes on slave when using pg_bulkload on master
On Wed, Dec 5, 2012 at 5:17 AM, James Cowell <jcowell@xxxxxxxxxxxxxx> wrote:
> I'm using pg_bulkload to load large amounts of CSV data into a postgres
> database hourly.
>
> This database is replicated to a second node.
>
> Whenever a bulk load happens the indexes on the updated tables on the
> secondary node corrupt and are unusable until a reindex is run on the
> primary node. I get the error below on node 2:
>
> ERROR: index "tablename" contains unexpected zero page at block 0
> SQL state: XX002
> Hint: Please REINDEX it.
>
> I'm assuming that this is because of the way pg_bulkload builds the index on
> the primary, and possibly has something to do with the way pg_bulkload
> overwrites rows in the event of a constraint violation,
If there are no constraint violations, do you still see the problem?
> but at the same time
> if something works on the primary shouldn't the replicated node be able to
> process the WAL log?
>
> I've tried this on 9.1.6 and 9.1.5 on RHEL 6.3 with pg_bulkload build
> 3.1.1-1.pg91.rhel6 and it happens every time.
Were there any older version on which it worked? Can you post a
minimal schema and control file to reproduce the problem?
Cheers,
Jeff
CREATE TABLE:
-------------
CREATE TABLE public.bulkreptest
(
route integer NOT NULL,
dtg timestamp without time zone NOT NULL,
issue_dtg timestamp without time zone NOT NULL,
insert_dtg timestamp without time zone,
just_a_number numeric(4,2)
CONSTRAINT pk_bulkreptest PRIMARY KEY (route , issue_dtg , dtg )
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.bulkreptest
OWNER TO postgres;
GRANT ALL ON TABLE public.bulkreptest TO postgres;
CREATE BULKLOAD FILTER:
-----------------------
CREATE OR REPLACE FUNCTION public.bulkreptest_filter(integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, numeric)
RETURNS record AS
$BODY$
SELECT $1, $2, $3, now(), $5
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION public.bulkreptest_filter(integer, timestamp without time zone, timestamp without time zone, timestamp without time zone, numeric)
OWNER TO postgres;
CREATE BULKLOAD CONFIG FILE: (change db name)
---------------------------------------------
OUTPUT = *YOUR_DB_NAME*.public.bulkreptest
TYPE = CSV
QUOTE = "\""
ESCAPE = \
DELIMITER = ","
LOADER = BUFFERED
ON_DUPLICATE_KEEP = NEW
DUPLICATE_ERRORS = -1
FILTER = *YOUR_DB_NAME*.public.bulkreptest_filter
CREATE LOAD DATA FILE:
----------------------
100,2012-12-13 10:00,2012-12-13 10:00,,1.498
100,2012-12-13 11:00,2012-12-13 10:00,,2.599
100,2012-12-13 12:00,2012-12-13 10:00,,3.426
100,2012-12-13 13:00,2012-12-13 10:00,,3.935
100,2012-12-13 14:00,2012-12-13 10:00,,4.008
100,2012-12-13 15:00,2012-12-13 10:00,,3.417
100,2012-12-13 16:00,2012-12-13 10:00,,2.507
100,2012-12-13 17:00,2012-12-13 10:00,,1.956
100,2012-12-13 18:00,2012-12-13 10:00,,1.699
100,2012-12-13 19:00,2012-12-13 10:00,,1.944
BULKLOAD COMMAND:
-----------------
/usr/pgsql-9.1/bin/pg_bulkload -d *YOUR_DB_NAME* -i /path/to/data/file.txt /path/to/load/config.cfg 2>> /path/to/load/logfile.log
SQL COMMAND:
------------
On the primary node
select * from bulkreptest where issue_dtg>='2012-12-12 00:00' and issue_dtg<='2012-12-14 00:00' order by route,issue_dtg,dtg
100;"2012-12-13 10:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";1.498
100;"2012-12-13 11:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";2.599
100;"2012-12-13 12:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";3.426
100;"2012-12-13 13:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";3.935
100;"2012-12-13 14:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";4.008
100;"2012-12-13 15:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";3.417
100;"2012-12-13 16:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";2.507
100;"2012-12-13 17:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";1.956
100;"2012-12-13 18:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";1.699
100;"2012-12-13 19:00:00";"2012-12-13 10:00:00";"2012-12-13 11:46:31.097496";1.944
On the secondary node:
select * from bulkreptest where issue_dtg>='2012-12-12 00:00' and issue_dtg<='2012-12-14 00:00' order by route,issue_dtg,dtg
ERROR: could not read block 0 in file "base/28227/77139": read only 0 of 8192 bytes
********** Error **********
ERROR: could not read block 0 in file "base/28227/77139": read only 0 of 8192 bytes
SQL state: XX001
select count(*) from bulkreptest
10
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
