CartoDB / cartodb-postgresql

PostgreSQL extension for CartoDB
BSD 3-Clause "New" or "Revised" License
111 stars 53 forks source link

Cdb table exists #332

Closed rafatower closed 6 years ago

rafatower commented 6 years ago

This adds a new helper function _CDB_Table_Exists(table_name_with_optional_schema TEXT). The intent is to use it as a guard for other calls. E.g:

diff --git a/app/services/carto/overviews_service.rb b/app/services/carto/overviews_service.rb
index 86211ff..eb7f067 100644
--- a/app/services/carto/overviews_service.rb
+++ b/app/services/carto/overviews_service.rb
@@ -26,11 +26,9 @@ module Carto
     end

     def delete_overviews(table_name)
-      # TODO: this is not very elegant, we could detect the existence of the
-      # table some otherway or have a CDB_DropOverviews(text)  function...
-      @database.run(%{SELECT cartodb.CDB_DropOverviews('#{table_name}'::REGCLASS)})
-    rescue Carto::Db::SqlInterface::Error => e
-      raise unless e.to_s.match /relation .+ does not exist/
+      if @database.fetch(%{SELECT cartodb._CDB_Table_Exists('#{table_name}')}).first[:_cdb_table_exists]
+        @database.run(%{SELECT cartodb.CDB_DropOverviews('#{table_name}'::REGCLASS)})
+      end
     end

     def overview_tables(table_name)

This way we avoid exception-driven code, and potentially breaking transactions.

Note that the error handling cannot be added to cartodb.CDB_DropOverviews(), because it fails as soon as some casting to REGCLASS is attempted on a non-existing table (i.e. it does not reach the body of the function, regardless of having the cast implicit or explicit).