On Wed, 13 Jan 2016, Tory M Blue wrote:

Tory,

You talk about slon 'initializing'.  When subscriptions start it needs to 
wait until all in progress transactions are committed before starting the 
copy. Once your cluster is subscribed a create index shouldn't block things.




> Postgres 9.4.5 slon 2.2.3 CentOS 6.6
> 
> Afternoon
> 
> Based on some searching this is not new, but I have failed to find an answer for it.. I had an index running on a non
> replicated table but slon would not initialize due to that index, something about set 1 failed due to a previous PID.
> 
> Now I'm trying to to reindex 2 tables that are not in the slon relationship, but slon is backing up, once I stop the
> reindex, slon replicates just fine (immediately). Something is wonky (its a technical term, you can look it up!: ) )
> 
> So killing the index, I was able to add my host, but now I'm trying to add another 2 indexes and it's backing up slon, and
> or one is holding up the other.
> 
> So now I'm trying to create 2 indexes concurrently...
> 
> These are the 2 i am creating. 
> 
> One is waiting  on the other. (Waiting is true in pg_stat_Activity)
> 
> If I kill the 1st the second starts. Nothing is querying this table.
> 
> 
> CREATE INDEX CONCURRENTLY idx_impsarchive_psttstamp
> 
>   ON torque.impressions_archive
> 
>   USING btree
> 
>   (timezone('US/Pacific'::text, timezone('UTC'::text, log_tstamp)))
> 
> TABLESPACE torquespace;
> 
> 
> -- Index: torque.idx_impsarchive_system
> 
> 
> -- DROP INDEX torque.idx_impsarchive_system;
> 
> 
> CREATE INDEX CONCURRENTLY idx_impsarchive_system
> 
>   ON torque.impressions_archive
> 
>   USING btree
> 
>   (system COLLATE pg_catalog."default")
> 
> TABLESPACE torquespace;
> 
> 
> Also when I had it running non-concurrently they both ran at the same time. But slony got backed up. It didn’t explicitly
> say that it was waiting. But after about 45 minutes, data had not replicated. I killed the indices and it replicated
> immediately. This table is not being replicated. 
> 
> 
> I also queried sl_table on the master and this table is not there
> 
> 
>  tab_id | tab_reloid |     tab_relname     | tab_nspname | tab_set |       tab_idxname        | tab_altered |        tab
> 
> _comment         
> 
> --------+------------+---------------------+-------------+---------+--------------------------+-------------+-----------
> 
> -----------------
> 
>     225 |      17959 | variables           | torque      |       2 | pk_variables_name        | f           |
> replicatedtable
> 
>     226 |      17935 | impsbulkimporteven  | torque      |       2 | pk_impsbulkimporteven_id | f           |
> torque.impsbulkimporteven
> 
>     228 |      17947 | impsbulkimportodd   | torque      |       2 | pk_impsbulkimportodd_id  | f           |
> torque.impsbulkimportodd
> 
>     233 |      17919 | impressions_daily | torque      |       2 | pk_impsdaily_id        | f           |
> torque.impressions_daily
> 
>     235 |      17907 | impressions       | torque      |       2 | pk_impressions_id      | f           | torque.impressions
> 
> (5 rows)
> 
> 
> So I want to create these indexes, they are not being replicated but they completely freeze replication. Same thing happened
> when I added a node, slon would not initialize until we killed the same index creation on this same non replicated table.
> I'm not sure what is going on and my apologies i deleted the set 1 failed, due to a previous pid message, but if this table
> is not in replication, why is it causing slon to be blocked?
> 
> Thanks
> Tory
> 
> 
>