-1

I observed the following: when I create a table with "create table mytable ..." in the SQL tab of the UI, the table is created under the public schema.

I can see these tables only using:

SELECT *
  FROM information_schema.tables
 WHERE table_schema='public'
   AND table_type='BASE TABLE'

I also need to create referential integrity between these objects and "cartodbfied" tables.

these tables don't count for the quota, not for number of tables nor for space.

Is it safe to use such tables in my application or is there some housekeeping job that is going to trash these objects at some point ?

  1. what about user defined functions ? is it safe to create UDF and rely on them staying there permanently ?
  2. one more issue with these objects is they seem to be cached. how can I force a cache refresh ?

@jaorre responded . Yes, I would like to know how to create "cartodbfied" tables programmatically.

I would also need to create referential integrity on these "cartodbfied" tables.

What about the other point from the original question?

  • forcing a cache refresh

One more observation is when I execute :

SELECT *
 FROM information_schema.tables
WHERE table_schema='public'
  AND table_type='BASE TABLE'

Couldn't the column table_catalog be used to identify "owner"?

closed as too broad by PolyGeo Dec 18 '17 at 22:07

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

You have found an issue we have on CartoDB and that we are aware of. When you create a table using the APIs or the UI, we take care of what we call "cartodbfying" the table, which means adding a cartodb_id, other columns, and triggers. Those fields for instrumenting the tables is what we use for quota checking and so on.

So when you create the table this way, we "do not realize" the table has been created and therefore do not "instrument" the table. We called this internally "ghost tables".

We are working to tackle this by using a new CartoDB extension to PostgreSQL that will trigger schema changes and will instrument those tables created by the user using the SQL API or the UI.

Meanwhile, we can not promise how we will treat those ghost tables in the future. The most likely is that we will instrument them automatically so that they count in quotas and have the right rights and triggers for invalidations.

If you want further info on how to create tables programatically that are instrumented and therefore they get invalidated and all the other things, please let me know.

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