Logo ← PostgreSQL Blog

OID is Dead: Hacking Legacy Code for PostgreSQL 15

Upgrading to PostgreSQL 15 broke our legacy middleware. Here is how we fixed the “Column OID Does Not Exist” error live in production.

OID is Dead: Hacking Legacy Code for PostgreSQL 15

Upgrading to PostgreSQL 15 broke our legacy middleware. Here is how we fixed the “Column OID Does Not Exist” error live in production.

The Upgrade that Went Wrong

We recently migrated our critical infrastructure to PostgreSQL 15 for better performance and security. The migration scripts ran smoothly, but the moment we opened the gates to live traffic, the system collapsed with a fatal error:

ERROR: column "oid" does not exist

We were baffled. Our modern application code doesn’t use OIDs. So why was the database screaming about a missing column?

The Root Cause

If you are maintaining legacy systems, here is the harsh reality: Starting with PostgreSQL 12, the community removed the oid system column from user tables.

Ancient software (like older ArcGIS/SDE versions) relied on this hidden column to lock rows. When we upgraded to PG 15, that column vanished, but the vendor’s auto-generated code kept looking for it.

We couldn’t wait for a vendor patch. We had to fix it ourselves by swapping the deprecated OID with PostgreSQL’s physical address locator: CTID.

Below is the code breakdown of how we patched the “Black Box” function.

The Fix: Swapping OID for CTID

Since OID is gone, we needed a replacement. Fortunately, PostgreSQL still provides a low-level physical address for every row version, known as CTID (Tuple ID).

For the purpose of row locking (FOR UPDATE), CTID is a perfect drop-in replacement for OID.

Below is the full breakdown of the “Open Heart Surgery” we performed on the vendor’s code.

Phase 1: The Broken Legacy Code

(This code works in Postgres 11 and below, but crashes instantly on 12, 13, 14, and 15.)

-- [BEFORE] THE VENDOR'S "BLACK BOX" FUNCTION

CREATE OR REPLACE FUNCTION gis_core.get_row_ids(
    i_id_type integer,
    i_num_requested_ids integer,
    OUT sql_code integer,
    OUT o_base_id integer,
    OUT o_num_obtained_ids integer)
    RETURNS record
    LANGUAGE 'plpgsql'
AS $BODY$
 
 DECLARE 
   -- [FATAL FLAW 1] Legacy code treats ID as an INTEGER
   o_oid                 INTEGER; 
   l_count               INTEGER; 
   i_table_name          VARCHAR(256); 
   l_sqlstmt             VARCHAR(528); 
   l_sqlstmt_upd         VARCHAR(528); 
   l_sqlstmt_del         VARCHAR(528); 
   l_record_temp         RECORD; 
  BEGIN  
  sql_code   := -1; 
  o_oid := -1; 
  o_base_id := -1; 
  o_num_obtained_ids := -1; 
  i_table_name := 'gis_core.registry_table'; 
  
  -- Logic omitted for brevity...
  
   IF i_num_requested_ids > 0 THEN 
     -- [FATAL FLAW 2] Querying 'oid' which is REMOVED in PG 12+
     l_sqlstmt := 'SELECT oid,base_id, num_ids FROM ' || i_table_name || 
                  ' WHERE id_type = ' || i_id_type ||  
                  ' ORDER BY num_ids DESC FOR UPDATE '; 
   END IF; 
   
   l_count := 0; 
   FOR l_record_temp IN EXECUTE l_sqlstmt 
    LOOP 
      l_count := l_count + 1;
      
      -- [FATAL FLAW 3] Assigning non-existent column
      o_oid            :=  l_record_temp.oid;
      o_base_id            :=  l_record_temp.base_id;
      o_num_obtained_ids :=  l_record_temp.num_ids;
      
      IF o_num_obtained_ids = -1 THEN 
         o_num_obtained_ids = i_num_requested_ids;
         
         -- [FATAL FLAW 4] Dynamic SQL treats ID as a simple number
         l_sqlstmt_upd := 'UPDATE ' || i_table_name || 
                          ' SET base_id = base_id + ' || o_num_obtained_ids || 
                          ', last_id = ' || o_base_id || ' WHERE oid = ' || o_oid; 
          EXECUTE l_sqlstmt_upd; 
      END IF; 
      EXIT; 
    END LOOP; 
    
    IF l_count = 0 THEN RAISE EXCEPTION 'Not Found'; END IF;
    sql_code := 0; 
  RETURN;   END;  
$BODY$;

Phase 2: The Production Hotfix

We rewrote the function to use CTID. Pay attention to the variable type change and the quoting logic.

-- [AFTER] THE PATCHED "SURVIVAL" CODE
-- Fully compatible with PostgreSQL 15

CREATE OR REPLACE FUNCTION gis_core.get_row_ids(
    i_id_type integer,
    i_num_requested_ids integer,
    OUT sql_code integer,
    OUT o_base_id integer,
    OUT o_num_obtained_ids integer)
    RETURNS record
    LANGUAGE 'plpgsql'
AS $BODY$
 
 DECLARE 
   -- [FIX 1] Changed Data Type: INTEGER -> tid (System Tuple ID)
   o_oid                 tid; 
   l_count               INTEGER; 
   i_table_name          VARCHAR(256); 
   l_sqlstmt             VARCHAR(528); 
   l_sqlstmt_upd         VARCHAR(528); 
   l_sqlstmt_del         VARCHAR(528); 
   l_record_temp         RECORD; 
  BEGIN  
  sql_code   := -1; 
  -- [FIX 2] Removed invalid initialization (-1) for 'tid' type
  o_base_id := -1; 
  o_num_obtained_ids := -1; 
  i_table_name := 'gis_core.registry_table'; 
  
  -- Logic omitted for brevity...
  
   IF i_num_requested_ids > 0 THEN 
     -- [FIX 3] Replaced 'oid' with physical address 'ctid'
     l_sqlstmt := 'SELECT ctid, base_id, num_ids FROM ' || i_table_name || 
                  ' WHERE id_type = ' || i_id_type ||  
                  ' ORDER BY num_ids DESC FOR UPDATE '; 
   END IF; 
   
   l_count := 0; 
   FOR l_record_temp IN EXECUTE l_sqlstmt 
    LOOP 
      l_count := l_count + 1;
      
      -- [FIX 4] Capturing the 'ctid'
      o_oid            :=  l_record_temp.ctid;
      o_base_id            :=  l_record_temp.base_id;
      o_num_obtained_ids :=  l_record_temp.num_ids;
      
      IF o_num_obtained_ids = -1 THEN 
         o_num_obtained_ids = i_num_requested_ids;
         
         -- [FIX 5] CRITICAL QUOTING
         -- CTID is a literal, so it needs triple quotes in Dynamic SQL (''' || o_oid || ''')
         l_sqlstmt_upd := 'UPDATE ' || i_table_name || 
                          ' SET base_id = base_id + ' || o_num_obtained_ids || 
                          ', last_id = ' || o_base_id || ' WHERE ctid = ''' || o_oid || ''''; 
          EXECUTE l_sqlstmt_upd; 
      END IF; 
      EXIT; 
    END LOOP; 
    
    IF l_count = 0 THEN RAISE EXCEPTION 'Not Found'; END IF;
    sql_code := 0; 
  RETURN;   END;  
$BODY$;
-- Apply Permissions
ALTER FUNCTION gis_core.get_row_ids(integer, integer) OWNER TO gis_admin;

Conclusion

If you are upgrading to PostgreSQL 12, 13, 14, or 15, do not assume your third-party tools are ready. The removal of the oid column is a breaking change that many legacy vendors have still not fully addressed.

By using CTID as a proxy for the deprecated OID, you can modernize your database engine without breaking the legacy applications that depend on it.

This patch saved our production environment. I hope it saves yours too.