2

A table in my PostGIS database (PostgreSQL 9.3.5) had the wrong geometry-type, therefore I wanted to change the geometry type. I tried to do this by dropping the geometry column like this:

ALTER TABLE xxx.my_table DROP COLUMN geometry_column;

After which I would have added a new geometry column of the correct type. In hindsight, I should probably have used ALTER COLUMN, however this didn't occur to me at the time.

The query started to run, however it never finished, so I had to stop the execution. Following this, every time I click on the table from within pgAdmin, the program crashes. I have tried using DROP TABLE to delete the table, but the query just runs without finishing. I also tried deleting the entire schema from within pgAdmin, however this also causes a crash.

The program gets stuck when "retrieving details" on the table, so I suspect the table is corrupt in some way. We have a backup of the schema in a working state, however using the restore functionality in pgAdmin doest not seem to work either.

Is there anyway to "fix" the table or force delete it in any way?

  • 1
    This sounds like a bug to me – nickves Apr 25 '17 at 12:32
  • A bug in pgAdmin or PostGIS/PostgreSQL? – Chrisnizz Apr 25 '17 at 12:34
  • 1
    Try using psql instead to make the necessary changes. – Fjellrev Apr 25 '17 at 12:57
  • Is the query still running? "SELECT * FROM pg_stat_activity" – Gob Tron Apr 25 '17 at 13:02
1

If you're using PostgreSQL 9.3.5, you're likely using PostGIS 2.1/2.2. Being 2.x both of these should use a view for geometry_columns. It's hard to tell where the problem exists. I back @Fjellrev's suggestion though,

Try using psql instead to make the necessary changes.

Using psql, connect and run \d+ xxx.my_table if that works, pasted the result above.

Either way, DROP TABLE xxx.my_table; should certainly work in psql

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.