Working with PostgreSQL 9.0.8 on linux. Can't add foreign key.
I have two tables: Table1 and Table2.
Table1 has 10 columns and 200 000 rows filled with some data, where first column is 'id' (primary key).
Table2 has 0 rows and 2 columns: id (primary key) and table1_id.
Adding foreign key from Table2.table1_id to Table1.id blocks database (can't select any data - got "FATAL: remaining connection slots are reserved for non-replication superuser connections" error).
I tried to wait for 6 hours, but nothing happened. Then I restarted database and this restored normal database work, but foreign key wasn't added anyway.
Any advice?

  • Check sourceforge.net/p/dspace/mailman/message/31794343 – fragles Mar 17 '14 at 9:00
  • 1
    check out this post.. maybe something there might help you? (i.e. setting the max_connections, and checking how your app is using its connections?) stackoverflow.com/questions/11847144/… – Gabriel Amram Mar 17 '14 at 9:05
  • 1
    It sounds like the strong lock taken by ALTER TABLE caused other connections to back up, but at the same time the ALTER TABLE might its self have been waiting on locks held by other transactions, so it held enough locks to stop anything else getting anything useful done, but not enough to proceed its self. – Craig Ringer Mar 17 '14 at 9:44
  • After setting max_connections to 500 and trying to add foreign key got gateway timeout error while accessing data from site. And foreign key wasn't added anyway. – Geslot Mar 17 '14 at 10:51
  • 1
    Gateway timeout is not exactly a PostgreSQL error, AFAIK... Have you already checked the DB logs? Also, while this is happening, a view on pg_locks may reveal some important clues. – dezso Mar 17 '14 at 13:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy