attached files stored as bytea instead of blob

[seanderickson]

migrated:

To alleviate the administrative complexity of dumping and restoring the pg_largeobject table, which is a system-wide table, and not a database-specific table, it might be better to see if we can store the AttachedFile.fileContents property as a byte array type, rather than a BLOB type. However, we should look into a strategy for lazy loading the fileContents, if we do this, to avoid memory usage problems. Could simply store in a secondary table, just like we do with Well.molfile

Follow-ups Submitted By: Andrew Tolopko

Adddate: 2008-10-02 13:12:48

[ant4 - Fri Aug 08 11:59:53 2008]:

To alleviate the administrative complexity of dumping and restoring the pg_largeobject table, which is a system-wide table, and not a database-specific table, it might be better to see if we can store the AttachedFile.fileContents property as a byte array type, rather than a BLOB type. However, we should look into a strategy for lazy loading the fileContents, if we do this, to avoid memory usage problems. Could simply store in a secondary table, just like we do with Well.molfile

Additional motivations include:

• pg_largeobject rows are not deleted when the rows containing their foreign keys are deleted
• Hibernate is apparently issuing queries using int values for OIDs, even though they are longs, causing query errors for some screens!

http://forge.abcd.harvard.edu/gf/project/screensaver/tracker/?action=TrackerItemEdit&tracker_item_id=1382&start=50

[seanderickson]

http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#LIKE_to_list_of_patterns

CREATE OR REPLACE FUNCTION merge_oid(val oid) RETURNS bytea AS $$ DECLARE merged bytea; arr bytea; BEGIN FOR arr IN SELECT DATA FROM pg_largeobject WHERE loid = val ORDER BY pageno LOOP IF merged IS NULL THEN merged := arr; ELSE merged := merged || arr; END IF; END LOOP; RETURN merged; END $$ LANGUAGE plpgsql;