cartodb/cartodb-postgresql

PostgreSQL extension for CartoDB

cartodb-postgresql

PostgreSQL extension for CartoDB

See the cartodb-postgresql wiki.

Dependencies

• PostgreSQL 11+ (with plpythonu extension). For PostgreSQL 12+ plpython3u is required instead. Older versions might still work but they aren't actively tested or supported.
• PostGIS extension
• Python with Redis module

Install

make all install

Test installation

make installcheck

NOTE: you need to run the installcheck as a superuser, use PGUSER env variable if needed, like: PGUSER=postgres make installcheck

NOTE: the tests need to run against a clean postgres instance, if you have some roles already created test will likely fail due publicuser not being dropped.

Enable database

In a database that needs to be turned into a "cartodb" user database, run:

CREATE EXTENSION cartodb CASCADE;

Update cartodb extension

Updating the version of cartodb extension installed in a database is done using ALTER EXTENSION.

ALTER EXTENSION cartodb UPDATE TO '0.1.1';

The target version needs to be installed on the system first (see Install section).

If the "TO 'x.y.z'" part is omitted, the extension will be updated to the latest installed version, which you can find with the following command:

grep default_version `pg_config --sharedir`/extension/cartodb.control

Updates are performed by PostgreSQL by loading one or more migration scripts as needed to go from the installed version S to the target version T. All migration scripts are in the "extension" directory of PostgreSQL:

ls `pg_config --sharedir`/extension/cartodb*

During development the cartodb extension version doesn't change with every commit, so testing latest change requires special steps documented in the CONTRIBUTING document, under "Testing changes live".

Limitations

• The main schema of an organization user must have one only owner (the user).

cartodb-postgresql's Issues

Add CDB_TableMetadata inspection to the DDL trigger tests

I've seen the DDL trigger tests succeed in the TableMetadataView but I'm sure they are supposed to fail instead, as the code tries to INSERT into CDB_TableMetadata which would be a view in that case.

Drop roles and permission management from the extension

CartoDB roles for PostgreSQL are not clearly defined yet, so the extension will better not create any nor consider any. For the time being permissions will still be handled by the ruby counterpart.

Evaluate including trgm extension on postgresql builds

http://www.postgresql.org/docs/9.2/static/pgtrgm.html

Complete migration to "EXTENSION" mechanism

We want the extension to be loadable via "CREATE EXTENSION".
Also we want to hook on the automated testing framework

Make versioning less strict, follow semver

Right now the extension versioning takes the manually given VERSION number, à la semver, plus the git describe information. It would be much better to use plain semver as it would ease the integration with cartodb project.

See: CartoDB/cartodb#712
Jira REF CDB-3504

Add tests for calls to CDB_InvalidateVarnish on table create/alter/drop

The test could install its own CDB_InvalidateVarnish function and have it print the received parameters.

Cat: .git: No such file or directory

I tried to install cartodb-postgresql but with no luck. There seems to be missing but I don't really know why is that. I downloaded the zip file and extract it and I'm using ubuntu server 14.04.
Below is the full error when executing make all install.

cartodb-postgresql$ sudo make all install
cat: .git: No such file or directory
make: *** No rule to make target scripts-enable/*.sql', needed bycartodb--0.3.0dev.sql'. Stop.

Move SQL code from Ruby to functions

Following Ruby class contains SQL code that should live as DB SQL functions just called by ruby with appropiate parameters.

User.rb

• setup_user()

Includes following code

conn.run("CREATE DATABASE \"#{self.database_name}\"
        WITH TEMPLATE = template_postgis
        OWNER = #{::Rails::Sequel.configuration.environment_for(Rails.env)['username']}
        ENCODING = 'UTF8'
        CONNECTION LIMIT=-1")

• add_python() + create_function_invalidate_varnish()
• create_trigger_function_update_timestamp()

Nice to have (especially regarding multiuser/orgs):

• set_database_permissions_in_schema()
• set_database_permissions()
• fix_permissions()

The 14 tests fails executing $make installcheck

I has following the instruction for install cartodb-postgresql, but the command
$ make installcheck
fails, with the output:

 mkdir -p sql/test/

mkdir -p expected/test/
mkdir -p results/test/
for f in test/CDB_CartodbfyTableTest.sql test/CDB_DigitSeparatorTest.sql test/CDB_HeadsTailsBinsTest.sql test/CDB_HexagonTest.sql test/CDB_JenksBinsTest.sql test/CDB_QuantileBinsTest.sql test/CDB_QueryStatementsTest.sql test/CDB_QueryTablesTest.sql test/CDB_QuotaTest.sql test/CDB_TransformToWebmercatorTest.sql test/CDB_UserTablesTest.sql test/CDB_XYZ_ExtentTest.sql; do
tn=basename ${f} .sql;
of=sql/test/${tn}.sql;
echo '\set ECHO off' > ${of};
echo '\a' >> ${of};
echo '\t' >> ${of};
echo '\set QUIET off' >> ${of};
cat ${f} |
sed -e 's/public./cartodb./g' >> ${of};
exp=expected/test/${tn}.out;
echo '\set ECHO off' > ${exp};
cat test/${tn}_expect >> ${exp};
done
/usr/lib/postgresql/9.3/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --psqldir='/usr/lib/postgresql/9.3/bin' --dbname=contrib_regression test_setup test_ddl_triggers test/CDB_CartodbfyTableTest test/CDB_DigitSeparatorTest test/CDB_HeadsTailsBinsTest test/CDB_HexagonTest test/CDB_JenksBinsTest test/CDB_QuantileBinsTest test/CDB_QueryStatementsTest test/CDB_QueryTablesTest test/CDB_QuotaTest test/CDB_TransformToWebmercatorTest test/CDB_UserTablesTest test/CDB_XYZ_ExtentTest
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test test_setup ... FAILED
test test_ddl_triggers ... FAILED
test test/CDB_CartodbfyTableTest ... FAILED
test test/CDB_DigitSeparatorTest ... FAILED
test test/CDB_HeadsTailsBinsTest ... FAILED
test test/CDB_HexagonTest ... FAILED
test test/CDB_JenksBinsTest ... FAILED
test test/CDB_QuantileBinsTest ... FAILED
test test/CDB_QueryStatementsTest ... FAILED
test test/CDB_QueryTablesTest ... FAILED
test test/CDB_QuotaTest ... FAILED
test test/CDB_TransformToWebmercatorTest ... FAILED
test test/CDB_UserTablesTest ... FAILED
test test/CDB_XYZ_ExtentTest ... FAILED

======================= =
14 of 14 tests failed.
======================= =

The differences that caused some tests to fail can be viewed in the
file "/home/aajimenezqs/Quarksoft/UEAF/CartoDB/cartodb-postgresql/regression.diffs". A copy of the test summary that you see
above is saved in the file "/home/aajimenezqs/Quarksoft/UEAF/CartoDB/cartodb-postgresql/regression.out".

make: *** [installcheck] Error 1

---

I add the next line to /etc/postgresql/9.3/main/postgresql.conf:
shared_preload_libraries = 'schema_triggers.so' # (change requires restart)

Then I reset the postgres service.

But the tests fails again.

What should I do?

This error also occurs when I testing pd_schema_triggers: CartoDB/pg_schema_triggers#1

Thanks in advance!

Batch _CDB_populate_the_geom_from_the_geom_webmercator

From CartoDB/cartodb#1144: _CDB_populate_the_geom_from_the_geom_webmercator is throwing timeout errors for some users. Since function receives a table the query is a select thus can't be batched. We need to modify the function so it's internally batched or accepts batching somehow.

Add CDB_SetUserQuotaInBytes(int) function

Rather than expecting an externally installed _CDB_UserQuotaInBytes(), we should delegate that (or whatever else will be internally needed to deal with user quota) to a "configurator function".

This could be a cartodb.CDB_SetUserQuotaInBytes(int) method.

\cc @Kartones

Ruby should call CDB_SetUserQuotaInBytes()

When we switch to "extension" needs to be called from ruby side.

Turn CDB_TableMetadata to a view

The real metadata should be in a table which is only readable by the superuser while the CDB_TableMetadata should be a view that only allows users to see records for tables they have select privileges on.

Would likely be affected by #21 (tagging tables as configuration tables).
Might need to wait for 0.2, to do the change in a migration script.

Have _CDB_UserQuotaInBytes() take precedence over CDB_CheckQuota parameter

A parameter passed to CDB_CheckQuota should only be used if no _CDB_UserQuotaInBytes() is provided. That way upgrading to config-based quota will not need running CDB_CartodbfyTable on each and every table.

View using CDB_xxxx() breaks functions upgrade

A user has created a view that performs at his user DB:

SELECT cdb_xxxx() AS cdb_xxxx;

So, upon trying to reload all functions into that user DB the process fails

ERROR:  cannot drop function cdb_xxx() because other objects depend on it
DETAIL:  view yyy depends on function cdb_xxx()
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Include git commit identifier in cdb_version() output

Helps finding out exact commit deployed. \cc @Kartones

Consider quota on migration from unpackaged

When creating cartodb extension from unpackaged we cartodbfy all tables having triggers that call the CDB_XXX functions. Doing so we basically drop the second parameter to the CDB_CheckQuota() function, loosing the quota information.

Since configuring the user database is now done using a function, failing to call that function by the "migrator" would leave the user database with no quota.

This ticket is to make the migration code attempt to automatically configure user quota looking at the parameters passed to CDB_CheckQuota in the existing user tables.

NOTE: if the user has no tables then no quota will be set.
NOTE2: if the quota is different in different tables we'll probably want to set the smallest quota

\cc @Kartones

See also #13

Evaluate including hstore extension on pg builds

http://www.postgresql.org/docs/9.0/static/hstore.html

Do not allow running CDB_CartodbfyTable while user quota is unset

The CDB_CartodbfyTable function installs quota triggers that rely on the presence of a _CDB_UserQuotaInBytes() function to tell them what the user quota is.

For this reason it's better to refuse cartdbfying a table when the function isn't available.
The function would be created by calling CDB_SetUserQuotaInBytes(), which may be hinted by an exception.

Move functions documentation wiki pages

Move them from https://github.com/CartoDB/cartodb/wiki/CartoDB-PLpgSQL

move varnish invalidation to cartodb-postgres

Since ideally the platform should work without rails app (cartodbpostgres + sql api + maps api) the invalidation should be in the extension so:

• CDB_Invalidate_Varnish() method should be in cartodb-postgres
• there should be some CDB_Config_Varnish or similar to configure it (it will be called from rails app or manually if you are not using it)
• If it's not configured, varnish invalidation is not used

As a side not maybe we should remove varnish name from the function and start to use something more generic, like CacheService. We know that the interface will be the same if we move to a different invalidation service.

if created_at or updated_at break come empty, Cartodbfication breaks

If a user tampers with an exported CSV and removes some created_at or updated_at (leaving the field empty), cartodbfication and import fails (at least from CSVs).

PG::Error: ERROR:  Got invalid input syntax for type timestamp with time zone: "" (22007)

Cartodbfication has to either accept or discard the critical fields, not break under some scenarios, even if the user wrongly emptied cells exported from cartodb. The correct behavior in this case would be to rename the column and start a new one.

CDB_CartodbfyTable forces duplicated unique index on cartodb_id

When a table was created with PRIMARY KEY constraint on "cartodb_id" field,
calling CDB_CartodbfyTable on it creates a second index.

The first index is called "tablename_pkey" , the second "tablename_cartodb_id_key".
The code of CDB_CartodbfyTable should be changed to avoid the second index when
a unique index already exists.

\cc @Kartones

CDB_drop_triggers & CDB_create_triggers are too fragile

If any trigger exists upon calling CDB_create_triggers the whole process dies.

As we've already faced scenarios of users not having all triggers, we need to be able to activate a "continue on error" from Rakes and half-deleted user cleanups.

cartodb_id sequenced DEFAULT is not enforced

When a cartodb_id field exists and is of acceptable type (any integer)
no effort is taken by CDB_CartodbfyTable to set a DEFAULT value incrementing an associated sequence.

It looks like the CartoDB Map Editor requires that, as it does not attempt to assign an explicit value to the cartodb_id field of newly created records.

\cc @Kartones

CDB_CartodbfyTable should try to convert cartodb_id,created_at,updated_at

Upon cartodbfying a talbe, CDB_CartodbfyTable should try to convert cartodb_id, created_at and updated_at fields if are found, as it is a common import scenario and duplicating them is safe for the data but not nice from the product point of view.

If conversion fails we'll rename them as it is currently doing.

See https://github.com/CartoDB/cartodb-postgresql/wiki/CartoDB-user-table

Fix travis build

Currently failing: https://travis-ci.org/CartoDB/cartodb-postgresql/builds/24693469

Evaluate including pgrouting on pg builds

http://pgrouting.org/

DDL triggers prevent creating a table with a cartodb_id field

Example (running as a non-superuser):

create table a ( cartodb_id int );

Debug logs:

NOTICE:  trigger "track_updates" for table "a" does not exist, skipping
NOTICE:  trigger "update_the_geom_webmercator_trigger" for table "a" does not exist, skipping
NOTICE:  trigger "update_updated_at_trigger" for table "a" does not exist, skipping
NOTICE:  trigger "test_quota" for table "a" does not exist, skipping
NOTICE:  trigger "test_quota_per_row" for table "a" does not exist, skipping
NOTICE:  Column cartodb_id already exists
NOTICE:  trigger "track_updates" for table "a" does not exist, skipping
NOTICE:  trigger "update_the_geom_webmercator_trigger" for table "a" does not exist, skipping
NOTICE:  trigger "update_updated_at_trigger" for table "a" does not exist, skipping
NOTICE:  trigger "test_quota" for table "a" does not exist, skipping
NOTICE:  trigger "test_quota_per_row" for table "a" does not exist, skipping
NOTICE:  Column cartodb_id already exists
NOTICE:  event trigger "cdb_on_relation_create" does not exist, skipping
NOTICE:  event trigger "cdb_on_relation_drop" does not exist, skipping
NOTICE:  event trigger "cdb_on_alter_column" does not exist, skipping
NOTICE:  event trigger "cdb_on_drop_column" does not exist, skipping
NOTICE:  event trigger "cdb_on_add_column" does not exist, skipping
NOTICE:  Column created_at already exists
NOTICE:  Column cartodb_id already exists
NOTICE:  Column created_at already exists
NOTICE:  Column updated_at already exists
NOTICE:  Column the_geom already exists
NOTICE:  Column the_geom_webmercator already exists
NOTICE:  event trigger "cdb_on_relation_create" does not exist, skipping
NOTICE:  event trigger "cdb_on_relation_drop" does not exist, skipping
NOTICE:  event trigger "cdb_on_alter_column" does not exist, skipping
NOTICE:  event trigger "cdb_on_add_column" does not exist, skipping
NOTICE:  Column updated_at already exists
NOTICE:  Column cartodb_id already exists
NOTICE:  Column created_at already exists
NOTICE:  Column updated_at already exists
NOTICE:  Column the_geom already exists
NOTICE:  Column the_geom_webmercator already exists
NOTICE:  event trigger "cdb_on_relation_create" does not exist, skipping
NOTICE:  event trigger "cdb_on_relation_drop" does not exist, skipping
NOTICE:  event trigger "cdb_on_alter_column" does not exist, skipping
NOTICE:  event trigger "cdb_on_drop_column" does not exist, skipping
NOTICE:  event trigger "cdb_on_add_column" does not exist, skipping
NOTICE:  Column the_geom already exists
NOTICE:  Column the_geom_webmercator already exists
ERROR:  trigger "track_updates" for relation "a" already exists

By the look of it it seems that the idea of disable/enabling DDL triggers during operations of a single handler is not working as expected.

Ensure user quota is retained in dumps ?

Right now dumping a database with the "cartodb" extension created will not add the _CDB_UserQuotaInBytes() function in the dump. That's probably because the function is created by a function of the "cartodb" extension, so it is considered part of the extension (as the ddl triggers).

But we should instead consider the user quota function as data!
So, either we mark the function as not being part of the extension OR we create a new table to contain all user settings and mark that table as data.

\cc @Kartones

Fix DDL trigger test for updated_at

The test assumes NOW() changes but it doesn't within a transaction.
The expected output is indeed bogus for updated_at (we should expect it to update).

Have CDB_TableMetadata tagged as a configuration table

Failing to do so would not include the contents of CDB_TableMetadata in dumps, which might break relocation of cartodb databases.

\cc @zenitraM

Think how to invalidate materialized views

Coming from: https://cartodb.atlassian.net/browse/CDB-3931

We should invalidate materialized views by adding a DDL trigger on the REFRESH action of the view.. but then views can only be created over the SQL API.

Invalidating with the source table wouldn't work: you have to REFRESH the view for the changes to be applied.

Maybe adding a CDB_CartodbfyView which only adds the invalidation trigger?

cc @javisantana @rochoa @Kartones

cdb_cartodbifytable does not make cartodb_id PK

CDB_UserTables() returns names of deleted tables

Deleting tables via SQL-API does not prevent them from being returned by CDB_UserTables(). I guess that's due to CDB_TableMetadata still listing them, which is part of the broader problem of handling schema changes.

Extension of CDB_LatLng

extended cdb_latlng to accept (text) and (text, text) inputs. also handles comma as decimal point as a special case e.g. 121,39::text = 121.39::numeric

no schema has been selected to create

I was in the stage of enabling cartodb on my local server but when i do
$ sh script/create_dev_user ${SUBDOMAIN}
--- Creating databases
** Invoke cartodb:db:setup (first_time)
** Invoke db:create (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:create
[sequel] Created database 'carto_db_development'
[sequel] Created database 'carto_db_test'
** Invoke db:migrate (first_time)
** Invoke db:migrate:load (first_time)
** Invoke environment
** Execute db:migrate:load
** Execute db:migrate
rake aborted!
PG::Error: ERROR: no schema has been selected to create in
/home/gis/.rvm/gems/ruby-1.9.3-p547@cartodb/gems/sequel-3.42.0/lib/sequel/adapters/postgres.rb:145:in async_exec' /home/gis/.rvm/gems/ruby-1.9.3-p547@cartodb/gems/sequel-3.42.0/lib/sequel/adapters/postgres.rb:145:inblock in execute_query'
/home/gis/.rvm/gems/ruby-1.9.3-p547@cartodb/gems/sequel-3.42.0/lib/sequel/database/logging.rb:37:in `log_yield
....
Tasks: TOP => cartodb:db:setup => db:migrate

How can i enable without those errors?
I am using the
$ git clone --recursive https://github.com/CartoDB/cartodb.git
$ cd cartodb20
$ git checkout master
commands as suggested on this site:
https://groups.google.com/forum/#!msg/cartodb/fpmOsM30t-Q/PMOH7-6M1gkJ

Jenks Bins fails to produce 3 bins for 3 values

https://documentation.cartodb.com/api/v1/sql?q=SELECT%20CDB_JenksBins%28ARRAY[1,2,3],%203%29

\cc @javisantana

"ERROR: type "spheroid" already exists" on install

these are the commands I execute:

cartodb_dev_user_4ab67a64-7cbf-4890-88b8-9d3c398249d5_db=# BEGIN; CREATE EXTENSION postgis; CREATE EXTENSION schema_triggers; CREATE EXTENSION cartodb from unpackaged;
BEGIN
NOTICE:  command CREATE EXTENSION is disabled
ERROR:  type "spheroid" already exists
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
cartodb_dev_user_4ab67a64-7cbf-4890-88b8-9d3c398249d5_db=# 

I'm executing on a current (non migrated) cartodb database

CDB_CartodbfyTable not detecting ok if _CDB_UserQuotaInBytes exists

SELECT public._CDB_UserQuotaInBytes();

Works (returns the bytes quota as expected), and

SELECT * FROM pg_proc WHERE proname = '_cdb_userquotainbytes';

also returns a result.

But, when I try to do:

CREATE EXTENSION cartodb FROM unpackaged;

It fails:

ERROR: Please set user quota before cartodbfying tables.
SQL state: P0001

I've run before all UPGRADE steps (including cartodb:db:load_functions), and the extensions are correctly built (and schema_triggers already installed).

Speedup DDL trigger test

The test currently uses pg_sleep(1) to ensure the "updated_at" field in CDB_TableMetadata is updated. Would be better to reduce the sleep time.

Add Last Updated info above each object

The information will be useful for doing the upgrade scripts

Fully qualify call to cdb_invalidate_varnish in CDB_TableMetadata trigger function

The call is currently not qualified, making it a security concern.

Let CDB_CheckQuota second parameter override _CDB_UserQuota

When installing the new CDB_CheckQuota over an existing cartodb installation (not the extension way, but using scripts), all user quota triggers fail to enforce the quota, because no _CDB_UserQuotaInBytes() is present and the second parameter passed to the function by the existing triggers fail to be honoured.

We could fix by honouring a second parameter when present.

Note that calling CDB_CartodbfyTable over such tables would drop the second parameter from the trigger call, bringing you back to no quota enforced.
I'm not sure if the CDB_CartodbfyTable should refuse to run in absence of quota configuration (or noisly raise a WARNING).

\cc @Kartones

Refuse to create new extension if legacy code is present on database

You should be disallowed to "CREATE EXTENSION cartodb;" on a database already having cartodb objects installed. The only accepted way should be "CREATE EXTENSION cartodb FROM unpackaged;"

Do not assume presence of "publicuser" user

... or testsuite fails when it's not available.
See https://travis-ci.org/CartoDB/cartodb-postgresql/builds/24701157#L161

Related to #4

The assumption is ok when the tests are run as part of cartodb "make check", but not as a standalone extension.

It may be a good idea for the test to create the required user and drop it at the end
(prefixed appropriately to avoid clashes).

new CDB_CartodbfyTable fails to re-cartodbfy tables on which views exist

If you define a view on an already-cartodbfied table, the current CDB_CartodbfyTable fails to re-cartodbfy due to the attempt to ALTER COLUMN performed with hope of it being a no-op when all the altered parameters match the existing configuration.

In particular, altering the type is the part that breaks. Needs a testcase, used to work before the recent change.

Consider not automatically enable DDL triggers

I'm actually not sure about this, but I wonder if DDL triggers should only be enabled on demand, rather than automatically enabled at extension creation time.

Fully qualify type names

The presence of public tables with the name of any type used by any function in the cartodb extension might prevent successfully creating the extension.

In order to fix that we need to fully qualify type names.
It's not "date", but "pg_catalog.date" (for example).

Make sed usage in Makefile compatible with Mac OS X

Current usage of sed in Makefile is incompatible with Mac OS X sed version (coming from FreeBSD).

The current suggested workaround is to install gnu-sed from Homebrew, however it would be much nicer to make it compatible with Mac OS X. For more information check Differences between sed on Mac OSX and other “standard” sed?.

Another possible workaround (that still requires sed from Homebrew) can be found in How to share a GNU sed script between Linux and Mac OS X.