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

On 11/20/2015 07:26 AM, Chris Richards wrote:
> Adrian is correct. This worked by itself whereas using it in the
> creation of the temporary table failed.
>
> mdb-> SELECT pq.* FROM policyqueue AS pq
> mdb-> JOIN seed_progress AS sp ON pq.id <http://pq.id/>=sp.polidx;
>
> I checked the query Albe suggested; there were two `relfilenode`s (11936
> and 11937) that exhibited the error. Respectively, they were
> pg_depend_depender_index and pg_depend_reference_index.
>
> Unfortunately, I didn't disable the nightly processes and something must
> have(?) fixed the glitch; at midnight GMT the query ran successfully. Ugh.

What are the nightly processes, or at least those that might have
touched the tables in the join?

If this pops up again, could be another clue.

>
> If it crops up again, I have some tools to try and capture data
> immediately, and the suggested REINDEX since both appear to be indices.
>
> Thanks for the help. It's appreciated.
>
> Chris
>
> On Fri, Nov 20, 2015 at 8:28 AM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> 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
> <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.
>
>
> 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 <http://pq.id>=sp.polidx;
>
>
> Yours,
> Laurenz Albe
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

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