BUG #14110: information_schema.constraint_column_usage not working as expected
От | srkunze@mail.de |
---|---|
Тема | BUG #14110: information_schema.constraint_column_usage not working as expected |
Дата | |
Msg-id | 20160425112333.2151.62919@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #14110: information_schema.constraint_column_usage not
working as expected
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Joe SteffeeДата:
Сообщение: Re: BUG #14117: psql and pg_dump commands should use ENV perl
Следующее
От: georg.kahest@internet.eeДата:
Сообщение: BUG #14112: sorting v and w is broken with et_EE locate