RDBMS specific features

[eeverman]

Hi - I'm looking at Celesta for a work project. We use PostGreSQL, but we use several features that it doesn't look like Celesta supports:

• spatial columns
• partitions
• advanced indexing features

Is there any way to way to inject some vendor specific DDL into the Celesta SQL? Or are there other strategies for dealing with these types of features with Celesta?

[inponomarev]

Hi @eeverman ! So sorry for overlooking your question!!

Celesta DDL has undocumented feature EXEC NATIVE, that can work like this:

exec native POSTGRESQL before --{{
   /* any vendor-specific DDL goes here. It will be executed before each update of the schema and it must be idempotent (note'if exists')! */
   drop view if exists products_shops;
--}};

exec native POSTGRESQL after --{{
 /* This will be executed after each update of the schema, and it must be idempotent (note 'create or replace')!*/
  create or replace view products_shops as select distinct shop_id from market.products;
--}};

(also note --{{ and --}} markers: they are chosen in such a way that your native code will be highlighted by any SQL syntax highlighter, while in Celesta they denote the beginning and the end of native SQL literal)

However, this feature is disabled for Celesta since I'm not sure about its safety: it's your responsibility to make this code idempotent. But I've been asked to enable it, so if you're willing, I can enable.

Another used approach is to utilize Flyway for schema migration and Celesta only for data access (by marking all the tables that are managed by Flyway with no autoupdate)