Find how to catch the Zone index error

• remove the memsize constraint (do a migration)
• find a polygon payload that trigger the index error (and pubish it here)
• try to find a way to log or catch the exception from POtsgres
• ideally report it in the HTTP message of a 50x error

added 1 deleted label

Out of 18142 polygons sent to me in XML files by Mélodie, without applying any simplification (but applying ST_Normalize to please the other constraints), I got 13820 failing polygons. Here is the smallest of them with "only" 577 points along with its error message:

arkindex_dev=# INSERT INTO images_zone (id, created, updated, image_id, polygon) VALUES (uuid_generate_v4(), now(), now(), '88c9e9d4-bcf3-4e89-ac21-52f6aa102930'::uuid, ST_Normalize(ST_GeomFromText('LINESTRING (912 857,922 857,925 856,935 856,938 855,941 858,944 860,946 860,949 857,951 857,956 852,959 851,961 851,966 846,969 845,971 845,974 842,979 842,982 844,984 847,987 847,990 844,992 841,995 841,997 838,1005 838,1008 840,1013 840,1016 842,1021 847,1024 847,1027 849,1029 846,1032 846,1034 843,1044 843,1047 842,1050 845,1053 847,1055 850,1061 854,1066 859,1069 857,1071 854,1074 854,1077 853,1079 851,1082 850,1084 850,1089 845,1091 842,1094 839,1097 841,1099 841,1102 844,1108 848,1110 851,1116 851,1118 850,1123 850,1126 847,1128 847,1131 844,1134 847,1136 846,1139 846,1141 843,1152 843,1154 840,1157 840,1162 845,1168 849,1173 849,1175 846,1178 846,1180 843,1188 843,1191 840,1193 840,1196 839,1199 842,1204 842,1206 841,1217 841,1219 840,1222 838,1224 835,1227 832,1231 826,1245 826,1247 828,1252 828,1255 827,1260 827,1263 829,1266 832,1269 834,1271 834,1274 836,1282 836,1285 838,1287 836,1293 840,1303 840,1306 839,1308 839,1316 831,1320 825,1328 817,1330 817,1333 819,1336 819,1338 816,1346 816,1348 813,1351 813,1354 815,1359 815,1362 817,1364 819,1377 819,1380 818,1388 818,1391 820,1393 823,1396 825,1399 825,1402 827,1404 827,1407 829,1412 824,1414 824,1417 823,1420 823,1422 821,1425 820,1427 820,1430 823,1433 822,1435 825,1438 825,1440 822,1443 824,1446 824,1449 826,1462 826,1466 820,1469 817,1477 817,1479 819,1482 821,1485 824,1488 826,1491 829,1493 828,1496 828,1499 831,1501 830,1509 830,1512 832,1515 835,1517 835,1520 832,1522 829,1527 829,1530 831,1535 831,1538 830,1543 830,1546 832,1559 832,1561 829,1564 829,1566 826,1569 826,1572 828,1577 828,1579 827,1587 827,1590 829,1593 829,1595 826,1598 826,1600 829,1603 828,1606 828,1608 831,1611 833,1616 833,1619 830,1621 830,1624 827,1626 824,1629 821,1631 818,1634 816,1638 810,1646 810,1652 814,1660 822,1663 819,1667 813,1670 810,1672 810,1675 813,1678 815,1683 815,1686 812,1688 809,1693 809,1696 806,1700 800,1703 798,1705 795,1708 792,1712 786,1715 784,1717 783,1723 783,1725 780,1728 780,1730 777,1733 775,1735 772,1738 772,1740 771,1748 771,1751 773,1756 778,1759 775,1761 772,1764 770,1766 767,1769 767,1771 764,1776 764,1779 763,1781 763,1784 766,1787 768,1790 768,1792 765,1797 765,1800 764,1805 764,1808 766,1811 769,1813 766,1821 766,1823 763,1826 763,1829 765,1834 770,1837 772,1840 775,1843 777,1848 782,1851 784,1854 784,1856 781,1858 779,1861 778,1866 778,1869 780,1872 780,1874 783,1880 787,1883 787,1885 790,1891 794,1896 794,1901 789,1903 786,1906 783,1908 780,1913 775,1916 774,1926 774,1921 709,1911 709,1908 710,1903 710,1898 705,1895 705,1892 703,1884 703,1882 704,1879 704,1874 699,1871 702,1868 699,1866 700,1863 700,1860 697,1858 697,1855 698,1850 698,1848 701,1845 701,1843 704,1840 707,1835 707,1832 705,1824 697,1821 700,1819 698,1816 698,1814 701,1811 701,1809 704,1806 706,1803 707,1801 704,1798 702,1795 699,1792 697,1790 697,1787 700,1785 703,1780 708,1777 709,1775 709,1772 711,1770 712,1767 714,1765 715,1762 717,1760 718,1755 718,1752 721,1750 721,1747 724,1744 721,1742 724,1739 724,1737 725,1724 725,1718 721,1713 716,1705 716,1702 717,1694 709,1691 707,1683 707,1681 710,1678 710,1675 708,1673 711,1665 711,1663 714,1660 717,1656 723,1653 725,1651 728,1648 731,1646 734,1643 737,1641 737,1638 739,1636 737,1633 740,1631 743,1628 740,1626 743,1623 741,1612 741,1610 742,1607 739,1604 737,1596 729,1590 725,1588 727,1582 723,1575 723,1572 726,1570 729,1567 729,1564 727,1562 724,1559 722,1556 719,1553 719,1551 717,1548 715,1543 715,1540 713,1529 713,1527 714,1524 716,1520 722,1517 725,1515 728,1512 730,1510 733,1507 736,1505 739,1500 744,1498 747,1495 750,1493 750,1490 748,1485 748,1482 746,1479 743,1477 743,1474 744,1471 741,1466 741,1460 737,1458 734,1455 732,1452 729,1450 732,1447 735,1445 735,1442 738,1440 738,1437 736,1434 733,1431 731,1426 731,1423 729,1421 729,1418 732,1415 729,1413 730,1410 732,1408 733,1405 733,1403 736,1392 736,1390 734,1387 734,1385 737,1379 737,1377 735,1374 738,1372 738,1364 746,1362 749,1359 752,1357 755,1355 757,1352 758,1349 758,1347 761,1344 758,1341 756,1339 753,1336 753,1333 754,1331 754,1326 759,1323 757,1321 760,1316 760,1313 763,1308 763,1305 764,1292 764,1290 765,1287 765,1282 760,1279 758,1276 755,1274 758,1271 756,1268 758,1264 764,1261 762,1258 759,1256 762,1253 765,1248 765,1245 763,1243 763,1238 768,1235 769,1233 769,1230 766,1227 767,1224 764,1222 764,1219 765,1216 762,1214 762,1211 763,1203 763,1201 766,1196 766,1193 764,1185 764,1182 762,1177 762,1175 765,1172 765,1169 763,1164 763,1161 761,1159 764,1156 761,1154 761,1151 762,1143 762,1140 760,1138 757,1135 760,1133 763,1128 763,1125 764,1120 769,1118 769,1115 767,1112 764,1106 760,1104 757,1101 755,1098 752,1092 748,1087 748,1084 746,1082 743,1079 743,1077 746,1074 749,1072 752,1064 752,1062 755,1059 758,1057 761,1054 763,1052 766,1049 766,1047 767,1044 769,1042 772,1039 775,1034 775,1031 773,1029 770,1026 768,1023 768,1020 766,1018 763,1012 759,1007 754,1004 754,1001 752,998 749,996 752,988 752,986 755,983 758,981 761,978 761,976 764,971 769,969 772,966 772,963 773,961 770,958 768,953 773,950 774,945 774,943 777,940 779,938 782,935 782,931 788,928 786,925 788,923 786,917 786,915 787,907 787,912 857)')));
ERROR:  index row size 2768 exceeds btree version 4 maximum 2704 for index "zone_unique_image_polygon"
DETAIL: Index row references tuple (230083,2) in relation "images_zone".
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.

ST_MemSize reports 9264 bytes. pg_column_size reports 9264 bytes, but if we first create a dummy table, store the value, then call pg_column_size, we get a smaller value due to TOAST:

arkindex_dev=# CREATE TEMPORARY TABLE lol (polygon GEOMETRY(LINESTRING));
arkindex_dev=# INSERT INTO lol VALUES (ST_Normalize(ST_GeomFromText('LINESTRING (912 857,922 857,925 856,935 856,938 855,941 858,944 860,946 860,949 857,951 857,956 852,959 851,961 851,966 846,969 845,971 845,974 842,979 842,982 844,984 847,987 847,990 844,992 841,995 841,997 838,1005 838,1008 840,1013 840,1016 842,1021 847,1024 847,1027 849,1029 846,1032 846,1034 843,1044 843,1047 842,1050 845,1053 847,1055 850,1061 854,1066 859,1069 857,1071 854,1074 854,1077 853,1079 851,1082 850,1084 850,1089 845,1091 842,1094 839,1097 841,1099 841,1102 844,1108 848,1110 851,1116 851,1118 850,1123 850,1126 847,1128 847,1131 844,1134 847,1136 846,1139 846,1141 843,1152 843,1154 840,1157 840,1162 845,1168 849,1173 849,1175 846,1178 846,1180 843,1188 843,1191 840,1193 840,1196 839,1199 842,1204 842,1206 841,1217 841,1219 840,1222 838,1224 835,1227 832,1231 826,1245 826,1247 828,1252 828,1255 827,1260 827,1263 829,1266 832,1269 834,1271 834,1274 836,1282 836,1285 838,1287 836,1293 840,1303 840,1306 839,1308 839,1316 831,1320 825,1328 817,1330 817,1333 819,1336 819,1338 816,1346 816,1348 813,1351 813,1354 815,1359 815,1362 817,1364 819,1377 819,1380 818,1388 818,1391 820,1393 823,1396 825,1399 825,1402 827,1404 827,1407 829,1412 824,1414 824,1417 823,1420 823,1422 821,1425 820,1427 820,1430 823,1433 822,1435 825,1438 825,1440 822,1443 824,1446 824,1449 826,1462 826,1466 820,1469 817,1477 817,1479 819,1482 821,1485 824,1488 826,1491 829,1493 828,1496 828,1499 831,1501 830,1509 830,1512 832,1515 835,1517 835,1520 832,1522 829,1527 829,1530 831,1535 831,1538 830,1543 830,1546 832,1559 832,1561 829,1564 829,1566 826,1569 826,1572 828,1577 828,1579 827,1587 827,1590 829,1593 829,1595 826,1598 826,1600 829,1603 828,1606 828,1608 831,1611 833,1616 833,1619 830,1621 830,1624 827,1626 824,1629 821,1631 818,1634 816,1638 810,1646 810,1652 814,1660 822,1663 819,1667 813,1670 810,1672 810,1675 813,1678 815,1683 815,1686 812,1688 809,1693 809,1696 806,1700 800,1703 798,1705 795,1708 792,1712 786,1715 784,1717 783,1723 783,1725 780,1728 780,1730 777,1733 775,1735 772,1738 772,1740 771,1748 771,1751 773,1756 778,1759 775,1761 772,1764 770,1766 767,1769 767,1771 764,1776 764,1779 763,1781 763,1784 766,1787 768,1790 768,1792 765,1797 765,1800 764,1805 764,1808 766,1811 769,1813 766,1821 766,1823 763,1826 763,1829 765,1834 770,1837 772,1840 775,1843 777,1848 782,1851 784,1854 784,1856 781,1858 779,1861 778,1866 778,1869 780,1872 780,1874 783,1880 787,1883 787,1885 790,1891 794,1896 794,1901 789,1903 786,1906 783,1908 780,1913 775,1916 774,1926 774,1921 709,1911 709,1908 710,1903 710,1898 705,1895 705,1892 703,1884 703,1882 704,1879 704,1874 699,1871 702,1868 699,1866 700,1863 700,1860 697,1858 697,1855 698,1850 698,1848 701,1845 701,1843 704,1840 707,1835 707,1832 705,1824 697,1821 700,1819 698,1816 698,1814 701,1811 701,1809 704,1806 706,1803 707,1801 704,1798 702,1795 699,1792 697,1790 697,1787 700,1785 703,1780 708,1777 709,1775 709,1772 711,1770 712,1767 714,1765 715,1762 717,1760 718,1755 718,1752 721,1750 721,1747 724,1744 721,1742 724,1739 724,1737 725,1724 725,1718 721,1713 716,1705 716,1702 717,1694 709,1691 707,1683 707,1681 710,1678 710,1675 708,1673 711,1665 711,1663 714,1660 717,1656 723,1653 725,1651 728,1648 731,1646 734,1643 737,1641 737,1638 739,1636 737,1633 740,1631 743,1628 740,1626 743,1623 741,1612 741,1610 742,1607 739,1604 737,1596 729,1590 725,1588 727,1582 723,1575 723,1572 726,1570 729,1567 729,1564 727,1562 724,1559 722,1556 719,1553 719,1551 717,1548 715,1543 715,1540 713,1529 713,1527 714,1524 716,1520 722,1517 725,1515 728,1512 730,1510 733,1507 736,1505 739,1500 744,1498 747,1495 750,1493 750,1490 748,1485 748,1482 746,1479 743,1477 743,1474 744,1471 741,1466 741,1460 737,1458 734,1455 732,1452 729,1450 732,1447 735,1445 735,1442 738,1440 738,1437 736,1434 733,1431 731,1426 731,1423 729,1421 729,1418 732,1415 729,1413 730,1410 732,1408 733,1405 733,1403 736,1392 736,1390 734,1387 734,1385 737,1379 737,1377 735,1374 738,1372 738,1364 746,1362 749,1359 752,1357 755,1355 757,1352 758,1349 758,1347 761,1344 758,1341 756,1339 753,1336 753,1333 754,1331 754,1326 759,1323 757,1321 760,1316 760,1313 763,1308 763,1305 764,1292 764,1290 765,1287 765,1282 760,1279 758,1276 755,1274 758,1271 756,1268 758,1264 764,1261 762,1258 759,1256 762,1253 765,1248 765,1245 763,1243 763,1238 768,1235 769,1233 769,1230 766,1227 767,1224 764,1222 764,1219 765,1216 762,1214 762,1211 763,1203 763,1201 766,1196 766,1193 764,1185 764,1182 762,1177 762,1175 765,1172 765,1169 763,1164 763,1161 761,1159 764,1156 761,1154 761,1151 762,1143 762,1140 760,1138 757,1135 760,1133 763,1128 763,1125 764,1120 769,1118 769,1115 767,1112 764,1106 760,1104 757,1101 755,1098 752,1092 748,1087 748,1084 746,1082 743,1079 743,1077 746,1074 749,1072 752,1064 752,1062 755,1059 758,1057 761,1054 763,1052 766,1049 766,1047 767,1044 769,1042 772,1039 775,1034 775,1031 773,1029 770,1026 768,1023 768,1020 766,1018 763,1012 759,1007 754,1004 754,1001 752,998 749,996 752,988 752,986 755,983 758,981 761,978 761,976 764,971 769,969 772,966 772,963 773,961 770,958 768,953 773,950 774,945 774,943 777,940 779,938 782,935 782,931 788,928 786,925 788,923 786,917 786,915 787,907 787,912 857)')));
arkindex_dev=# select pg_column_size(polygon) from lol;
 pg_column_size 
----------------
           2738
(1 ligne)

2738 means there are 30 bytes used by the rest of the index tuple (the error had reported 2768 bytes). With 8 bytes as the BTree index tuple header and 16 bytes for a UUID, this leaves six extra unknown bytes. docker exec -it ark-database pg_controldata reports aligments on 8 bytes, the default for x64 binaries, so we need two extra bytes on 2738, still leaving 4 bytes to nothingness.

I used a rather cursed method to find this, but it was faster than trying to run a Transkribus import or trying to use Python in any way:

grep Coords **/*.xml |
sed '
  # Transkribus uses x,y x,y coords, but PostGIS wants x y,x y
  y/, / ,/
  # Wrap everything into transactions and insert statements. Uses a random image ID I found in my database
  s/.*points="/BEGIN; INSERT INTO images_zone (id, created, updated, image_id, polygon) VALUES (uuid_generate_v4(), now(), now(), '"'"'88c9e9d4-bcf3-4e89-ac21-52f6aa102930'"'"'::uuid, ST_Normalize(ST_GeomFromText('"'"'LINESTRING (/;s/"[^"]*$/)'"'"'))); ROLLBACK;/
  # Add the first point as the last, since we require linestrings to be closed
  s/LINESTRING (\([0-9]* [0-9]*\)\([0-9 ,]*\)/&,\1/' |
# Uses -b to report failed SQL statements along with their errors
psql -bh 127.0.0.1 -p 9100 -U devuser arkindex_dev 2>&1
# Just grab the failed instructions
| grep '^INSTRUCTION'
# Get the shortest instruction
| awk '{ print length, $0 }' | sort -n | head -n1

That concurs with results from @vrigal : looks like we can extend the limit to 300 - 400 points

Endpoints to check:

• https://preprod.arkindex.teklia.com/api-docs/#operation/CreateElement
• https://preprod.arkindex.teklia.com/api-docs/#operation/CreateElements
• https://preprod.arkindex.teklia.com/api-docs/#operation/UpdateElement
• https://preprod.arkindex.teklia.com/api-docs/#operation/PartialUpdateElement
• https://preprod.arkindex.teklia.com/api-docs/#operation/CreateElementTranscriptions

mentioned in merge request !1324 (merged)

mentioned in commit 70fbebf1

closed

added 4h of time spent at 2021-04-27