title: Fixing invalid memory alloc request at PostgreSQL 9.2.9
body: <p>I've encountered a problem querying some of my tables recently. When I try to select data I get an ERROR telling: ERROR:  invalid memory alloc request size 4294967293. This generally indicates data corruption. There is a nice and precise technique of how to delete corrupted rows described here: <a href="https://confluence.atlassian.com/jirakb/invalid-memory-alloc-request-size-440107132.html" rel="nofollow">https://confluence.atlassian.com/jirakb/invalid-memory-alloc-request-size-440107132.html</a><br>
  But, since I have lots of corrupted tables, this method is too slow. So, I've found a nice function which returns the last successful ctid here: <a href="http://blog.dob.sk/2012/05/19/fixing-pg_dump-invalid-memory-alloc-request-size/" rel="nofollow">http://blog.dob.sk/2012/05/19/fixing-pg_dump-invalid-memory-alloc-request-size/</a></p>
  
  <p>Looking for corrupted row is a bit faster when using it, but not fast enough. I slightly modified it to store all "last successful ctid" in a different table and now it looks like this:  </p>
  
  <pre><code>CREATE OR REPLACE FUNCTION
  find_bad_row(tableName TEXT)
  RETURNS void
  as $find_bad_row$
  DECLARE
  result tid;
  curs REFCURSOR;
  row1 RECORD;
  row2 RECORD;
  tabName TEXT;
  count BIGINT := 0;
  BEGIN
  DROP TABLE IF EXISTS bad_rows_tbl;
  CREATE TABLE bad_rows_tbl (id varchar(255), offs BIGINT);
  SELECT reverse(split_part(reverse($1), '.', 1)) INTO tabName;
  
  OPEN curs FOR EXECUTE 'SELECT ctid FROM ' || tableName;
  
  count := 1;
  
  FETCH curs INTO row1;
  
  WHILE row1.ctid IS NOT NULL LOOP
      BEGIN
      result = row1.ctid;
  
      count := count + 1;
      FETCH curs INTO row1;
  
      EXECUTE 'SELECT (each(hstore(' || tabName || '))).* FROM '
      || tableName || ' WHERE ctid = $1' INTO row2
      USING row1.ctid;
  
      IF count % 100000 = 0 THEN
      RAISE NOTICE 'rows processed: %', count;
      END IF;
      EXCEPTION
      WHEN SQLSTATE 'XX000' THEN
          RAISE NOTICE 'LAST CTID: %', result;
          EXECUTE 'INSERT INTO bad_rows_tbl VALUES(' || result || ',' || count || ')';
      END;
  
  END LOOP;
  
  CLOSE curs;
  
  END
  $find_bad_row$
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>I'm quite new to plpgsql, so I'm stuck with the following question: how to query not pre-unsuccessful ctid, but the exact unsuccessful one (or calculate the next one from pre-unsuccessful) so I could insert it into bad_rows_tbl and use as an argument for a DELETE statement further?  </p>
  
  <p>Hope for some help...  </p>
  
  <p>UPD: a function I ended up</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION
  find_bad_row(tableName TEXT)
  RETURNS tid[]
  as $find_bad_row$
  DECLARE
  result tid;
  curs REFCURSOR;
  row1 RECORD;
  row2 RECORD;
  tabName TEXT;
  youNeedMe BOOLEAN = false;
  count BIGINT := 0;
  arrIter BIGINT := 0;
  arr tid[];
  BEGIN
  CREATE TABLE bad_rows_tbl (id varchar(255), offs BIGINT);
  SELECT reverse(split_part(reverse($1), '.', 1)) INTO tabName;
  
  OPEN curs FOR EXECUTE 'SELECT ctid FROM ' || tableName;
  
  count := 1;
  
  FETCH curs INTO row1;
  
  WHILE row1.ctid IS NOT NULL LOOP
      BEGIN
      result = row1.ctid;
      count := count + 1;
  
      IF youNeedMe THEN
          arr[arrIter] = result;
          arrIter := arrIter + 1;     
          RAISE NOTICE 'ADDING CTID: %', result;
          youNeedMe = FALSE;
      END IF;
  
      FETCH curs INTO row1;
  
      EXECUTE 'SELECT (each(hstore(' || tabName || '))).* FROM '
      || tableName || ' WHERE ctid = $1' INTO row2
      USING row1.ctid;
  
      IF count % 100000 = 0 THEN
          RAISE NOTICE 'rows processed: %', count;
      END IF;
      EXCEPTION
      WHEN SQLSTATE 'XX000' THEN
          RAISE NOTICE 'LAST GOOD CTID: %', result;
          youNeedMe = TRUE;
      END;
  
  END LOOP;
  
  CLOSE curs;
  RETURN arr;
  END
  $find_bad_row$
  LANGUAGE plpgsql;
  </code></pre>