I have a table that contains user locations (called public.user_location) and is the following:

   Column        |           Type           |                            Modifiers
     id          | bigint                   | not null default nextval('user_location_id_seq'::regclass)
     user_id     | integer                  | not null
     location    | geography(Point,4326)    | 
     accuracy    | smallint                 | 
     datetime    | timestamp with time zone | default now()
   "users_locations_pkey" PRIMARY KEY, btree (id)
   "user_location_datetime_desc_idx" btree (datetime DESC)
   "user_location_user_id_idx" btree (user_id)
   "users_locations_gix" gist (location)

Foreign-key constraints:
   "users_locations_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) ON DELETE 
Referenced by: ...

I had a unique constraint composed by the fields (location, datetime, user_id), but then I found out that it was corrupted and so there were duplicates.

After the deletion of the duplicates I tried to reindex but I have got the following message:

The connection to the server was lost. Attempting reset: Failed.

So I am not able anymore to create the constraint or even just the unique index and I always got an INVALID index.

What should I do? Let me know if you need more info.


  • Before you do anything else, read and act on this: wiki.postgresql.org/wiki/Corruption – Craig Ringer Sep 19 '14 at 1:00
  • 1
    OK, done making a fresh backup? Now go look at the PostgreSQL log files after your "connection to the server was lost" error. There should be a bunch more detail about what happened. It might be quite big, so put it on gist.github.com or pastebin or something rather than pasting it here if it's big. Then comment here when you've uploaded the log. – Craig Ringer Sep 19 '14 at 1:01
  • 1
    X-posted to SO: stackoverflow.com/questions/25922810/… – Erwin Brandstetter Sep 19 '14 at 1:02
  • Sorry @ErwinBrandstetter, I have deleted the post on StackOverflow. – angelst00ne Sep 19 '14 at 8:11

Your Answer

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

Browse other questions tagged or ask your own question.