Postgresql - alter column type from oid to bytea with data retention

3
votes

Now I'm developing application that uses hibernate + postgresql to persist data. We need to store a number of serializable HashMaps, so LOB (oid) was used for this purpose. But due to some problems with oid deletion in postgresql we decide to store this data as bytea instead of oid. So the question is that what is the best way to alter column type from oid to bytea with retaining old data? If someone can give a working script/solution for such a data transition it will be great.

Thank you in advance.

2
sqlhibernatepostgresqllob
Did you try the vacuumlo and lo modules? They should provide automatic management of LO stuff in most cases. – A.H.
Yes, we've tried vacuumlo, but faced a problem: since we have high read/write load on database, vacuumlo could not complete clean-up on running database and fails. – Vladimir
Strange, because together with the lo_manage trigger from lo this management should occur in the original transaction itself - at least most of the time. Of course vacuumlo alone is not fit for the job. – A.H.

2 Answers

2
votes

i had the same issue. So i did it with four requests with creating buffered column

ALTER TABLE attachment add column content_bytea bytea
UPDATE attachment SET content_bytea = lo_get(content)
ALTER TABLE attachment drop column content
ALTER TABLE attachment rename column content_bytea to content
1
votes

lobs are probably the wrong solution for serializable hash maps unless you need to do seeking with offsets. Bytea is cleaner. The problem is that you will probably have to have some down time to switch things over.

What I would probably do is create a new column and copy the data over, and then unlink, and then drop the old column. Then run appropriate vacuum jobs.