Let me begin this reply with a caveat: I've never encountered this exact problem, so I don't fully understand the nature of it. However, I'm going to give my advice so that perhaps it gives you the insight you need to complete your task.
pageinspect
extension
Recent versions of PostgreSQL come with an extension which you can install that allows you to inspect the individual pages of elements stored in your database.
First things first, all you need to do to use it is to run the command
CREATE EXTENSION pageinspect;
and it's ready to go.
Now, once again, I don't fully understand the issue, but it appears you are concerned with the size of the indexed entry, and not necessarily the actual row entry in the heap.
For that case, you should use a function call from the pageinspect
extension to query the pages of your BTree index, as
SELECT * FROM bt_page_items('idx_123', 1);
and you'll get back some results like
itemoffset ctid itemlen nulls vars data
-----------------------------------------------------------------
1 (1,182) 16 f f "00 3e 9b ac b2 02 01 00"
2 (0,1) 16 f f "00 20 d6 fa 65 02 01 00"
3 (0,2) 16 f f "00 09 7b 30 66 02 01 00"
4 (0,3) 16 f f "00 f2 1f 66 66 02 01 00"
5 (0,4) 16 f f "00 db c4 9b 66 02 01 00"
6 (0,5) 16 f f "00 c4 69 d1 66 02 01 00"
...
Presumably, though you should check against your own data, you can see the number of bytes which the index key is using by checking the itemlen
field. I am betting that you can use this to check against your pre-determined limit which you said was 2712 bytes, and in that case use this info to collect all the ctid
s which violate your condition.
Broadly speaking then, you need to create a PL/pgSQL function to iterate over the pages of your index(s), checking the itemlen
, and collecting the ctid
s of the heap entries, and once completed, since you state it was OK to drop violating rows, then wrap it all up with a
DELETE FROM my_table WHERE my_table.ctid = *** your ctid list ***;
I hope this helps!