Re: What could cause CREATE TEMP... "could not read block" error?

On 11/18/2015 04:31 PM, 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 <http://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.
>
> Thanks for any assist.

What happens if you do?:

SELECT pq.* INTO TEMP TABLE tmp_policyqueue FROM policyqueue AS pq
JOIN seed_progress AS sp ON pq.id=sp.polidx;

What does \d show for:

policyqueue

seed_progress

>
> Chris

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com