From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, 'Chris Richards *EXTERN*' <chris(at)infiniteio(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: What could cause CREATE TEMP... "could not read block" error? |
Date: | 2015-11-20 14:28:38 |
Message-ID: | 564F2E16.6090303@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/20/2015 06:18 AM, Albe Laurenz wrote:
> Chris Richards wrote:
>> Howdy. I have two tables that I'm joining together and using the result to create a temporary table.
>> Performing the join operation works fine; it produces 0 rows (as expected). If I prepend "CREATE TEMP
>> tmp_policyqueue AS" to the SELECT then it generates this error:
>>
>> ERROR: could not read block 39 in file "base/16391/11937": read only 0 of 8192 bytes
>>
>>
>> $ psql
>> psql (9.3.9)
>>
>> mdb=> CREATE TEMP TABLE tmp_policyqueue AS
>>
>> mdb-> SELECT pq.* FROM policyqueue AS pq
>> mdb-> JOIN seed_progress AS sp ON pq.id=sp.polidx;
>> ERROR: could not read block 40 in file "base/16391/11937": read only 0 of 8192 bytes
>>
>> You'll also observe that the block number is changing each time I execute the command. I know very
>> little about postgres internal structure so it may be irrelevant. I've left my database in this state
>> should extra information be needed.
>
> It would be interesting to know what object is affected:
>
> SELECT s.nspname AS schemaname, t.relname AS objectname, t.relkind
> FROM pg_class t JOIN
> pg_namespace s ON t.relnamespace = s.oid
> WHERE t.relfilenode = 11937;
>
> If it is an index, REINDEX should help.
>
> What is the statement that performs the join operation and works just fine?
If I am following correctly it is:
mdb-> SELECT pq.* FROM policyqueue AS pq
mdb-> JOIN seed_progress AS sp ON pq.id=sp.polidx;
>
> Yours,
> Laurenz Albe
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-11-20 14:52:16 | Re: What could cause CREATE TEMP... "could not read block" error? |
Previous Message | Albe Laurenz | 2015-11-20 14:18:35 | Re: What could cause CREATE TEMP... "could not read block" error? |