Обсуждение: getting a error message when creating temporary table
Not sure what is causing this. When we execute:
CREATE TEMPORARY TABLE test(test_code varchar (255))
We getCREATE TEMPORARY TABLE test(test_code varchar (255))
ERROR: could not read block 6 in file "global/... " read only 0 of 8192 bytes
Evan Walter <ewalter@decisionanalyst.com> writes:
> Not sure what is causing this. When we execute:
> CREATE TEMPORARY TABLE test(test_code varchar (255))
> We get
> ERROR: could not read block 6 in file "global/... " read only 0 of 8192
> bytes
What's the exact filename you omitted there, and which PG version is this?
I'm guessing this indicates corruption of pg_shdepend or one of its
indexes, as that seems like the only shared catalog that's really likely
to get touched during a CREATE TEMP TABLE. If you're lucky, it's one of
the indexes, and a REINDEX will fix it.
Have you had any OS-level crashes lately, power failures, etc? Whatever
is corrupted exactly, this shouldn't have happened if filesystem integrity
was being maintained properly.
regards, tom lane
Thanks for the replies, sorry for not giving more details/background.
It is version 9.1.9
os is ubuntu on an Amazon ec2 server
The directory is global/26745
The server is a backend for python django applications. We started having problems last Thursday. Queries were running a very long time. And there were some application issues that were affecting the database. It is my understanding that the application issues have been fixed. We were also on version 9.1.1.
Anyway, on Friday we called an oustide specialist who broke replication. rsync-ed the files to a new volume drive, upgraded the version of Postgresql, and rebuilt it with the new version. Performance-wise queries are running well now, but we are getting that error.
We have rebuilt some of the indexes. Some of the tables are quite large, so we re-create the indexes concurrently and drop the original.
We will reindex some of the catalog indexes tonight and see if that helps.
Thank you
Evan
On Mon, Apr 28, 2014 at 5:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Evan Walter <ewalter@decisionanalyst.com> writes:What's the exact filename you omitted there, and which PG version is this?
> Not sure what is causing this. When we execute:
> CREATE TEMPORARY TABLE test(test_code varchar (255))
> We get
> ERROR: could not read block 6 in file "global/... " read only 0 of 8192
> bytes
I'm guessing this indicates corruption of pg_shdepend or one of its
indexes, as that seems like the only shared catalog that's really likely
to get touched during a CREATE TEMP TABLE. If you're lucky, it's one of
the indexes, and a REINDEX will fix it.
Have you had any OS-level crashes lately, power failures, etc? Whatever
is corrupted exactly, this shouldn't have happened if filesystem integrity
was being maintained properly.
regards, tom lane
Evan Walter <ewalter@decisionanalyst.com> writes:
> Thanks for the replies, sorry for not giving more details/background.
> The directory is global/26745
OK, so try
select relname from pg_class where relisshared and pg_relation_filenode(oid) = 26745;
That should tell you which table or index is busted.
> It is version 9.1.9
> os is ubuntu on an Amazon ec2 server
Um. AWS is, AFAIK, pretty much guaranteed to lose your data eventually.
It's a great compute service if you can afford to have nodes fail now
and then ... but probably not the best substrate for a database. If
you have to use it for that, you *must* have good backup and replication
strategies in place.
regards, tom lane