Thread: 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

On Monday, April 25, 2016, <srkunze@mail.de> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      14110
> Logged by:          Sven R. Kunze
> Email address:      srkunze@mail.de <javascript:;>
> 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.
>
> Interestingly, not a single database on our ci machines showed this
> behavior.
>
>
> 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.
>
>
This is unlikely to be a bug.  That view is filtered by current user
permissions.  Your discrepancies likely can be explained by variations in
object ownership between the various database being queried.

If you want further help I'd suggest picking two databases, one working and
one not, and asking on -general how to go about comparing the two setups
for differences that would prove this to your satisfaction.

Even if this was a bug you have not come close to prividing sufficient info
for us to confirm it.  A self-contained psql script exhibiting the
behavior would be necessary.

It's likely \du is insufficient to explore this, you have to look at the
tables, not the users.  You should see differences is ownership or grants.

http://www.postgresql.org/docs/8.0/static/infoschema-constraint-column-usage.html

David J.