Improve Ghost Tables functions

[gonzaloriestra]

AFAIK, this should be equivalent, but it seems to fix the random failures in cartodb CI.

[Algunenano]

I'm not sure why this is making a difference, specially since the documentation recommends not to use a explicit name:

Tip
It is often preferable to use unique index inference rather than naming a constraint directly using ON CONFLICT ON CONSTRAINT constraint_name. Inference will continue to work correctly when the underlying index is replaced by another more or less equivalent index in an overlapping way, for example when using CREATE UNIQUE INDEX ... CONCURRENTLY before dropping the index being replace

Note that since you are using the table primary key as constraint (cdb_ddl_execution_pkey) I don't think this applies to us since you aren't going to reindex it, right? In any case, being explicit should be fine.

There is a couple of things I find odd after looking at the whole trigger process:

• txid is created as txid integer PRIMARY KEY, but transaction ids are 64b ints. It's highly unlikely but there is the possibiliy of conflicts here. Since txid_current() returns bigint, I'd suggest to change it and use that; theoretically there could still be conflicts, but I can't conceive how.
• link_ghost_tables is not being triggered for foreign tables (ALTER FOREIGN TABLE, CREATE FOREIGN TABLE....). Do we need to add those?

[gonzaloriestra]

Regarding the fix, I had seen the tip from the documentation, but as you say, I don't think is a problem here.

It also says this: conflict_target can perform unique index inference. [...] If an attempt at inference is unsuccessful, an error is raised.

I still don't know why the inference could fail here (and only sometimes), but at least we know that it can happen, that's why I tried to use the explicit constraint instead of the inference.

[gonzaloriestra]

About your suggestions, good ones! I'm going to add both in this PR.

@ethervoid @javitonino I'll add CREATE/ALTER/DROP FOREIGN TABLE to the ghost tables events, ok?