Long story short, I tried setting REPLICA IDENTITY FULL on a table with a box 
type and it made replication stall on the receiver because - as I found out 
later - the predefined box type seems to lack a way to definitively determine 
exact equality of coordinates.

I was tempted to post this as a bug because - while discouraged - FULL works 
with other types, and from a user perspective based on the string output the 
box type seems like it could at least have hash-based equality, if not a btree 
total ordering.

The wording of 
https://www.postgresql.org/docs/current/logical-replication-publication.html 
also implies to me that FULL should work in all cases with default types: "If 
there are no such suitable indexes, the search on the subscriber side can be 
very inefficient, therefore replica identity FULL should only be used as a 
fallback if no other solution is possible." - so inefficient, but still working?

I might be missing something obvious, so I figured I'd bring it up here for 
advice. If not... well, it could've led to a production outage if I hadn't 
caught it in time, and this kind of issue seems likely to increase as logical 
replication becomes more popular.

Related reading:
https://www.postgresql.org/message-id/flat/1361F665-56E4-4CE6-9199-592067A656AB%40postgrespro.ru
https://www.postgresql.org/message-id/flat/4CF96953.40505%40enterprisedb.com#06df5ff5c9835e6c05ac3c55d48b2228

--

We have a cascading replica setup in production; the replicas are used to 
offload CPU-intensive recommendation queries and disaster recovery. In the 
PostgreSQL 16 upgrade cycle we switched from physical to logical replication. 
This entailed a few gotchas - we have a decade-and-a-half old, 
rapidly-developed schema, and had to set up some primary keys or use other 
indexes for replica identity. In cases where there was no obvious candidate, 
and the tables weren't busy, we set REPLICA IDENTITY FULL.

One that we missed until now, as it is rarely changed, is content servers areas 
- a set of boxes in ascending priority order defining the geospatial areas 
within which users should be directed to particular content servers. (It was 
great to find that PostgreSQL had support for these - like inet/cidr and uuid - 
out of the box.)

fb=# \d content_servers_areas
             Table "public.content_servers_areas"
      Column       |   Type   | Collation | Nullable | Default
-------------------+----------+-----------+----------+---------
 content_server_id | smallint |           |          |
 priority          | smallint |           |          |
 area              | box      |           |          |

db=# SELECT * FROM content_servers_areas WHERE content_server_id = 10;
 content_server_id | priority |          area
-------------------+----------+------------------------
                10 |       53 | (90,-96),(-90,-170)
                10 |       65 | (90,-27),(-90,-170)
                10 |       35 | (48,-81.5),(35,-105.5)
                10 |       35 | (90,-81.5),(48,-128)
                10 |        5 | (48,-84.8),(37,-99)
(5 rows)

The use of logical replication became an issue when we wanted to alter an 
existing area:

db=# UPDATE content_servers_areas SET area = '(48,-89),(37,-99)' WHERE area = 
'(48,-84.8),(37,-99)';
ERROR:  cannot update table "content_servers_areas" because it does not have a 
replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
db=# ALTER TABLE content_servers_areas REPLICA IDENTITY FULL;
ALTER TABLE

This seemed to work, right up to the point our Munin monitoring infrastructure 
trigged a critical warning for high WAL levels on the sender a few hours later. 
Looking at the replica logs, the issue became obvious.

2023-12-26 01:55:49.849 UTC,,,2752638,,658a32a5.2a007e,1,,2023-12-26 01:55:49 
UTC,8/310000,0,LOG,00000,"logical replication apply worker for subscription 
""replica"" has started",,,,,,,,"InitializeApplyWorker, 
worker.c:4514","","logical replication worker",,0
2023-12-26 01:55:49.930 UTC,,,2752638,,658a32a5.2a007e,2,,2023-12-26 01:55:49 
UTC,8/0,0,ERROR,42883,"could not identify an equality operator for type 
box",,,,,"processing remote data for replication origin ""pg_18583"" during 
message type ""UPDATE"" for replication target relation 
""public.content_servers_areas"" in transaction 256215511, finished at 
244/5DE85B48",,,"tuples_equal, execReplication.c:309","","logical replication 
worker",,0
2023-12-26 01:55:49.932 UTC,,,1314286,,65819479.140dee,13166,,2023-12-19 
13:02:49 UTC,,0,LOG,00000,"background worker ""logical replication worker"" 
(PID 2752638) exited with exit code 1",,,,,,,,"LogChildExit, 
postmaster.c:3660","","postmaster",,0

ALTER SUBSCRIPTION replica SKIP (lsn = '244/5DE85B48'); worked to clear this - 
I had to do it a few times as we'd made further changes.

Unfortunately I can't do the obvious:
db=# CREATE UNIQUE INDEX content_server_id_area ON content_servers_areas 
(content_server_id, area);
ERROR:  data type box has no default operator class for access method "btree"
HINT:  You must specify an operator class for the index or define a default 
operator class for the data type

Indeed, this appears to be the case:
db=# SELECT am.amname AS index_method,
       opc.opcname AS opclass_name,
       opc.opcintype::regtype AS indexed_type,
       opc.opcdefault AS is_default
    FROM pg_am am, pg_opclass opc
    WHERE opc.opcmethod = am.oid and opc.opcintype::regtype::text = 'box'
    ORDER BY index_method, opclass_name;
 index_method |   opclass_name    | indexed_type | is_default
--------------+-------------------+--------------+------------
 brin         | box_inclusion_ops | box          | t
 gist         | box_ops           | box          | t
 spgist       | box_ops           | box          | t
(3 rows)

And per 
https://www.postgresql.org/docs/devel/sql-createindex.html#id-1.9.3.69.7 "Only 
b-tree currently supports unique indexes." (Even in devel? I thought hash was 
meant to be an option... I guess 
https://www.postgresql.org/message-id/flat/CAHut%2BPuzhADSevvypRikx4mtT_1B6_14Z1iCcfhEYVM5EsgGGQ%40mail.gmail.com#91a2c03066f1df409d6101f3de84cfbf
 is more about using hash in replica identity rather than unique indexes.)
...so you get `ERROR:  access method "gist" does not support unique indexes` if 
you try USING gist().

I get the impression that PostGIS might be able to handle this:
https://gis.stackexchange.com/a/223344/106307
...but I'm hoping to avoid adding a whole geospatial solution just for this.

This seems like it should work but doesn't:
db=# CREATE UNIQUE INDEX content_server_id_area ON content_servers_areas 
(content_server_id, CAST(area AS text));
CREATE INDEX
db=# ALTER TABLE content_servers_areas REPLICA IDENTITY USING INDEX 
content_server_id_area;
ERROR:  cannot use expression index "content_server_id_area" as replica identity

The dubious 'content_servers_areas (content_server_id, AREA(area));' fails 
likewise; and while ~= might be helpful, I can't see how to use it for this 
purpose - maybe a trigger-updated column? Although at this point I'm minded to 
just remove it from replication.

It was said in one of the above threads that "there's no plausible linear 
ordering of boxes", but coordinates are already reordered into (top right, 
bottom left) when set, so perhaps treating the individual elements of each 
coordinate pair as numbers might work? Failing that, is a hash operator 
possible? Maybe negative zero is an issue? ( 
https://www.virtualdub.org/blog2/entry_259.html )

I was also a little disappointed that I didn't get a warning about using 
REPLICA IDENTITY FULL to start with, as this confirmed my assumption that it 
should work, just inefficiently. I appreciate what matters is the indexes on 
the replica, which may differ from the source, but in an ideal world it 
might've warned "if you try to apply a full row with the indexes defined on 
this end, it won't work".

Best wishes for the holiday,
--
Laurence 'GreenReaper' Parry
https://greenreaper.co.uk - https://inkbunny.net

Reply via email to