BUG #14110: information_schema.constraint_column_usage not working as expected

The following bug has been logged on the website:

Bug reference:      14110
Logged by:          Sven R. Kunze
Email address:      srkunze@mail.de
PostgreSQL version: 9.3.5
Operating system:   OpenSUSE
Description:

Hi,

I hope it qualifies as a proper bug. Since the issue is a bit longer, I will
cut it into several pieces for better digestion.


#### Background ####

Recently, we wrote the following query (inspired by
http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys/1152321#1152321):

SELECT tc.table_name
  FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
  WHERE constraint_type = 'FOREIGN KEY' and ccu.table_name = 'our_table';


The query gathers all table names which refer to 'our_table' by a foreign
key. It worked quite reliably while testing on some test databases by hand.

Unfortunately, we run into some strange behavior. For some databases (even
on the **same** cluster), we get 0 rows back despite having the same
schema.

'\d' shows all relationships properly though.


We sifted through our database instances in order to find out whether it was
a flipped bit on a single production server.

It wasn't.

Overview of a collection of our database instances (all different clusters
on different hosts):

30 - PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
0 - PostgreSQL 9.2.7 on i586-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit
30 - PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
0 - PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
0 - PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
30 - PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
0 - PostgreSQL 9.2.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
0 - PostgreSQL 9.2.7 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
0 - PostgreSQL 9.2.7 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit
0 - PostgreSQL 9.3.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 64-bit

0  - signifies zero rows (wrong output)
30 - signifies 30 tables which refer to a given table (right output)


Interestingly, not a single database on our ci machines showed this
behavior.


##### Broken information_schema.constraint_column_usage? ####


We could strip the query down even further:

db1 => select count(*) from information_schema.constraint_column_usage ;
count | 666
db2 => select count(*) from information_schema.constraint_column_usage ;

count | 96

It seems that in case of a "broken" database,
information_schema.constraint_column_usage carries far less entries.


##### A permission issue? ####

Our systems usually connect via Unix-domain socket to the PostgreSQL
server.

Even more noticeable is the fact that user 'postgres' **can** read
information_schema.constraint_column_usage of the "broken" database
properly.


However, '\du' reveals no differences between database users who read
constraint_column_usage properly and those who don't.



##### dumpall+restore don't fix things ####

We updated some of our older 9.2 instances to 9.3. However, dumpall and
restore does not fix the issue at all.


We decided that this might be worth posting publicly because we couldn't
really explain this behavior. When ever you need more details, just let me
know.

Best regards,
Sven