Re: BUG #12808: BDR lock adding Postgis extension in one node

Hi,

[some information has been acquired privately]

On 2015-02-27 15:30:20 +0100, Ruth Melendo wrote:
> The error got when adding a PostGIS extension from PGAdmin in node
> 1. Extension was created and the depending table too but in node 2 got
> the error when adding the extension because the depending table was
> not created.

> But, although the problem was originated for this, my main dude is how
> to fix the database then it´s locked. What do I have to do? Because
> now, I cannot do any DDL or DML against it.

The reason it's locked is that it's waiting for DDL to be
replicated. And that fails due to the spatial_ref_sys error.

In the version you're using the easiest way to resolve this is probably
to skip replication of the problematic transaction, and then fix up
things afterwards by hand.

To do that connect to the database you performed the CREATE EXTENSION
on. There get a list of the outgoing connections with a query like:

SELECT slot_name, datoid, database, active
FROM pg_replication_slots
WHERE slot_name LIKE 'bdr_'||(SELECT oid FROM pg_database WHERE datname = current_database())||'_%';
┌─────────────────────────────────────────┬────────┬──────────┬────────┐
│ slot_name │ datoid │ database │ active │
├─────────────────────────────────────────┼────────┼──────────┼────────┤
│ bdr_17911_6120567807158814813_1_16385__ │ 17911 │ node_03 │ t │
│ bdr_17911_6120567807158814813_1_17153__ │ 17911 │ node_03 │ f │
└─────────────────────────────────────────┴────────┴──────────┴────────┘
(2 rows)

In my three node setup those two consume changes from the node_03
database.

To consume the problematic transaction, use something like:

COPY (SELECT * FROM pg_logical_slot_get_binary_changes('bdr_17911_6120567807158814813_1_16385__', NULL, 1, 'interactive', 'true')) TO '/dev/null';

for each of the remote nodes. After that you should be able to execute
DDL again.

Then please reconnect and do
SET bdr.skip_ddl_replication = on;
DROP EXTENSION postgis;

That should bring the nodes back in sync with regard to the postgis
extension.

We'll work on making it possible to replicate extensions that insert
data into their own tables during creation.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services