Thread: BUG #8436: Heisenbug: random: relation "XXX" does not exist on 3 tables/views

The following bug has been logged on the website:

Bug reference:      8436
Logged by:          Páll Haraldsson
Email address:      qwerty@hi.is
PostgreSQL version: 9.1.9
Operating system:   Linux <DEL> 2.6.32-279.1.1.el6.x86_64
Description:

Hi,


Just a few days ago we started randomly getting:


relation "th_thjoderni" does not exist


This has happened a few times now but the other two "same" errors below only
once each subsequently (yet). Those queries are very simple and have been
unchanged for a long time in our system while th_thjoderni ("nationality"),
an old static table, had just been added to the query below.


Seems there is nothing wrong in our code/system; these queries run on each
login (1000s per day) used to and usually now without error. A restart of
PostgreSQL is not "the solution" but my boss wants me to try that just in
case and see ef the problem goes away. Is there some way I lose any "debug"
information that way? Is it valuable to know or should I NOT do that and
check something before? Any thought on why this is happening now suddenly?




SQLSTATE[42P01]Undefined table: 7 ERROR: relation "nk_namskeid" does not
exist
LINE 1: select * from nk_namskeid where ke_fagnumer='86295820076'




SQLSTATE[42P01]Undefined table: 7 ERROR: relation "myndir_notenda" does not
exist
LINE 2: SELECT * FROM myndir_notenda WHERE kennitala = $1;




SQLSTATE[42P01]Undefined table: 7 ERROR: relation "th_thjoderni" does not
exist
LINE 5: LEFT JOIN th_thjoderni ON (th_thjoderni.th_landakodi = th_na...


SELECT th_nafnaskra.th_kennitala, th_nafnaskra.th_nafn,
th_nafnaskra.th_radnafn, th_nafnaskra.th_kyn, th_nafnaskra.th_rikisfang,
th_nafnaskra.th_faedingardag, acct.username, acct.nafn, acct.kennitala,
acct.uid, acct.gid, th_heimilisfong.th_nefnifall,
th_heimilisfong.th_thagufall, th_heimilisfong.th_postnumer,
th_heimilisfong.th_poststod, th_heimilisfong.th_logheimili_nefnifall,
th_heimilisfong.th_logheimili_thagufall,
th_heimilisfong.th_logheimili_postnumer,
th_heimilisfong.th_logheimili_poststod, th_thjoderni.th_land FROM
th_nafnaskra LEFT JOIN acct ON (acct.kennitala = th_nafnaskra.th_kennitala)
LEFT JOIN th_heimilisfong ON (th_heimilisfong.th_kennitala =
th_nafnaskra.th_kennitala) LEFT JOIN th_thjoderni ON
(th_thjoderni.th_landakodi = th_nafnaskra.th_rikisfang) WHERE
th_nafnaskra.th_kennitala = ?;




Table "public.th_thjoderni"
Table "ugla_hi.myndir_notenda"
View "ugla_unak.nk_namskeid"


We can rule out, view (or table) issues, prepared statements, (specific)
schema (they use different usernames that both see public) or public-schema
issues, as they are not common.




We upgraded to 9.1.9 many months ago.


ps -ef |grep postgres
postgres  5972     1  0 Apr15 ?        17:04:28
/usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data
..
ps -ef |grep postgres |wc -l
62


-bash-4.1$ uptime
 15:58:31 up 341 days, 17:26,  1 user,  load average: 1.00, 1.33, 1.27


ugla_hi=# \d+ th_thjoderni
                        Table "public.th_thjoderni"
    Column    |          Type          | Modifiers | Storage  | Description


--------------+------------------------+-----------+----------+-------------
 th_landakodi | character(2)           | not null  | extended |
 th_land      | character varying(200) |           | extended |
Indexes:
    "th_thjoderni_pkey" PRIMARY KEY, btree (th_landakodi)
Has OIDs: no


ugla_hi=# select count(*) from th_thjoderni;
 count
-------
   231


                       Table "ugla_hi.myndir_notenda"
   Column    |          Type           | Modifiers | Storage  | Description


-------------+-------------------------+-----------+----------+-------------
 kennitala   | character(10)           | not null  | extended |
 src         | character varying(2000) | not null  | extended |
 simaskra_id | character varying(2000) |           | extended |
Indexes:
    "myndir_notenda_kt" btree (kennitala)
    "myndir_notenda_simaskra_id" btree (simaskra_id)
Has OIDs: no


 count
-------
 12640 (and counting)


Your system is written in PHP using the usual client libraries (must be the
server and not the client libraries?).


Any more info you need? I'm pretty sure the few queries (not related to
these queries and all autocommit) that came before shouldn't matter.


This is my first time reporting (to PostgreSQL that is). Hopefully I'm doing
it right. Nobody likes a Heisenbug..


--
Sincerely,
Páll Haraldsson
DBA University of Iceland

qwerty@hi.is writes:
> Just a few days ago we started randomly getting:
> relation "th_thjoderni" does not exist

You haven't really provided any information about what changed around the
time this started happening.  What I'd wonder about is concurrent DDL on
these tables --- perhaps you added some kind of background maintenance
task that wasn't there before?

You might try enabling query logging (log_statement = all) to see exactly
what's happening at the time you get one of these errors.

            regards, tom lane