Cartodbfy error assigning an organization owner

[juanignaciosl]

@yasharora73 has assigned a user to an organization as owner and it has failed.

This's been the error: Sequel::DatabaseError: PG::Error: ERROR: CDB(_CDB_Has_Usable_Primary_ID): ALTER TABLE "USERNAME".USER_TABLE ADD CONSTRAINT cartodb_id_pk PRIMARY KEY (cartodb_id) CONTEXT: SQL statement "SELECT _CDB_Error(sql, '_CDB_Has_Usable_Prima:

Sequel::DatabaseError: PG::Error: ERROR: CDB(_CDB_Has_Usable_Primary_ID): ALTER TABLE "USERNAME".USER_TABLE ADD CONSTRAINT cartodb_id_pk PRIMARY KEY (cartodb_id)
CONTEXT: SQL statement "SELECT _CDB_Error(sql, '_CDB_Has_Usable_Primary_ID')"
PL/pgSQL function _cdb_has_usable_primary_id(regclass) line 59 at PERFORM
SQL statement "SELECT _CDB_Has_Usable_Primary_ID(reloid)"
PL/pgSQL function _cdb_rewrite_table(regclass,text) line 51 at SQL statement
SQL statement "SELECT _CDB_Rewrite_Table(reloid, destschema)"
PL/pgSQL function cdb_cartodbfytable(text,regclass) line 51 at PERFORM

... 18 non-project frames
19
File "/home/ubuntu/www/production.cartodb.com/releases/20151026092424/app/models/user/db_service.rb", line 283 in block (3 levels) in move_tables_to_schema
20
File "/home/ubuntu/www/production.cartodb.com/releases/20151026092424/app/models/user/db_service.rb", line 274 in each
21
File "/home/ubuntu/www/production.cartodb.com/releases/20151026092424/app/models/user/db_service.rb", line 274 in block (2 levels) in move_tables_to_schema
... 6 non-project frames
28
File "/home/ubuntu/www/production.cartodb.com/releases/20151026092424/app/models/user/db_service.rb", line 273 in block in move_tables_to_schema
29
File "/home/ubuntu/www/production.cartodb.com/releases/20151026092424/app/models/user.rb", line 514 in in_database
30
File "/home/ubuntu/www/production.cartodb.com/releases/20151026092424/app/models/user/db_service.rb", line 272 in move_tables_to_schema
31
File "/home/ubuntu/www/production.cartodb.com/releases/20151026092424/app/models/user/db_service.rb", line 913 in move_to_own_schema
32
File "/home/ubuntu/www/production.cartodb.com/releases/20151026092424/app/models/user/user_organization.rb", line 25 in promote_user_to_admin

As a result, none of the tables were moved (since CartoDB/cartodb#5477 table movement is performed in a transaction). Some metadata was kept and fixed manually: CartoDB/cartodb#6006 .

I reported @rafatower and he managed to get the trace:

Oct 26 11:47:35 dbd18 postgres[7329]: [17344-1] 2015-10-26 11:47:35 GMT postgres cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db [unknown] ERROR:  schema "icf-pnw-geo" already exists
Oct 26 11:47:35 dbd18 postgres[7329]: [17344-2] 2015-10-26 11:47:35 GMT postgres cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db [unknown] STATEMENT:  CREATE SCHEMA "icf-pnw-geo" AUTHORIZATION "cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0"
Oct 26 11:47:35 dbd18 postgres[7329]: [17345-1] 2015-10-26 11:47:35 GMT postgres cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db [unknown] ERROR:  multiple primary keys for table "chehalis_reaches_habitat_hits" are not allowed
Oct 26 11:47:35 dbd18 postgres[7329]: [17345-2] 2015-10-26 11:47:35 GMT postgres cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db [unknown] CONTEXT:  SQL statement "ALTER TABLE "icf-pnw-geo".chehalis_reaches_habitat_hits ADD CONSTRAINT cartodb_id_pk PRIMARY KEY (cartodb_id)"
Oct 26 11:47:35 dbd18 postgres[7329]: [17345-8] 2015-10-26 11:47:35 GMT postgres cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db [unknown] STATEMENT:   SELECT cartodb.CDB_CartodbfyTable('icf-pnw-geo'::TEXT, 'icf-pnw-geo.chehalis_reaches_habitat_hits'::REGCLASS) 

First error is related to CartoDB/cartodb#6006, but it's not an stopper.

Second error means that extension is trying to add a new PK to a table that already has it. In this situation PK on cartodb_id should probably be kept.

[rafatower]

This is the root cause (the query done in the cartodbfy function):

cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db=#   SELECT a.attname, i.indisprimary, i.indisunique, a.attnotnull, a.atttypid
cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db-#   FROM pg_class c 
cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db-#   JOIN pg_attribute a ON a.attrelid = c.oid 
cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db-#   JOIN pg_type t ON a.atttypid = t.oid
cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db-#   LEFT JOIN pg_index i ON c.oid = i.indrelid AND a.attnum = ANY(i.indkey)
cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db-#   WHERE c.oid = 'chehalis_reaches_habitat_hits'::regclass
cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db-#   AND NOT a.attisdropped
cartodb_user_54c33c72-84fd-4c63-a1bf-60bd123d3ca0_db-#   AND a.attname = 'cartodb_id';
  attname   | indisprimary | indisunique | attnotnull | atttypid 
------------+--------------+-------------+------------+----------
 cartodb_id | f            | t           | t          |       23
 cartodb_id | t            | t           | t          |       23
(2 rows)

it gets two rows, actually the first one in the variable rec, which has indisprimary set to false.

[rafatower]

The problem is having two indexes defined on the same cartodb_id column:

\d chehalis_reaches_habitat_hits
-- ...
Indexes:
    "chehalis_reaches_habitat_hits_pkey" PRIMARY KEY, btree (cartodb_id)
    "chehalis_reaches_habitat_hits_cartodb_id_key" UNIQUE CONSTRAINT, btree (cartodb_id)
-- ...

The failing code: https://github.com/CartoDB/cartodb-postgresql/blob/master/scripts-available/CDB_CartodbfyTable.sql#L533-L577

I deleted the second index but it failed with other tables. @pramsey Any suggestion to make it robust to this problem?

[pramsey]

I'm guessing that it's hitting the non-primary key index first, and then trying to create a primary key, which raises an uncaught error. In actuality our test is a bit too strict, at https://github.com/CartoDB/cartodb-postgresql/blob/master/scripts-available/CDB_CartodbfyTable.sql#L550

IF rec.indisprimary AND rec.indisunique AND rec.attnotnull THEN

cloud be

IF (rec.indisprimary OR rec.indisunique) AND rec.attnotnull THEN

right? If it's either a primary key index or a unique index we'll be OK?

I think I need to rework this for the case of a compound primary key, but that's another degree of unlikeliness at the moment. Think that makes sense?

[rafatower]

Putting things in context, this is kind of a corner case which won't affect normal cases. We deleted the extra index manually cause it doesn't make sense to have two btree indexes on the same column.

I think the change in the IF expression you suggest makes sense, thanks, we'll try it.

rework this for the case of a compound primary key

I wouldn't do that, I think we're good with taking existing simple candidate keys if they exist or creating a new one if there's no suitable one.

Thanks for the help and prompt response.

[juanignaciosl]

FYI this has happened again. Now, to @micagm , with blockbrief user: https://rollbar.com/vizzuality/CartoDB/items/10418/occurrences/9049351252/ . I'll delete the extra indexes again, but add a vote to a fix for this :-)

[rafatower]

Gonna keep this open until we actually release the new version of the extension

[jgoizueta]

Version 0.11.4, which fixes this was deployed in production to all user DBs on 2015-11-27.