Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

On 9 Jan 2012, at 18:57, Radosław Smogura wrote:

> In real world BLOBs are transfered as references, and those references are
> managed in way as the trigger does. Nacked PG doesn't support deletion, Oid is
> universal type so it can't be used by GC approach, unles collector will know
> which Oid is LOB oid.

What do you mean by "nacked"?
You can unlink lob's, there's your support for deletion.

> Oid is like void*, it's abstarct pointer. If you get void* you don't know if
> data referenced by it represent person row, or car row, you don't know if
> void* is even reference or just 64 bit number. Current implementation is not
> type safe. You can't just write UPDATE TABLE x SET blob = 'aadfasfasfda' which
> in current times should be supported, but you may write (if are not fully
> familiar with db) UPDATE table X set varchar_d = blob_column;

That's easy to remedy, similar to how most implementations in C don't use straight void pointers. In C you'd just typedef them to something meaningful:

typedef blob oid;

Similarly you can wrap them in a domain in PG:

create domain blob as oid;

It would be cool if that would allow to add an FK-constraint to the oid in pg_largeobject to that domain, but alas, that isn't possible in my version (I'm a bit behind with pg 8.4).

I agree that it would be nice if PG provided a built-in type for lobs (blob's are a subdivision of those), especially if that would also handle the reference to pg_largeobject.

> In fact LOB's id may be stored even as varchar. So true is that PG supports
> LOBs, but due to missing functionality LOBs are quite hard to manage. It's
> like car withot steering wheel - you may drive, but it's little bit hard.

That's probably just because PG knows to cast that varchar to something compatible with oid's. I suspect that in recent versions that cast may not be allowed anymore though.

And remember, SELECT 'my explicit string value'; does not in fact denote a string value, but a literal. While the query is still in SQL notation (meaning until the query parser is done with it), everything is text.
The way I understand it, a literal gets a meaningful type once it is compared to a value of a known type (typically from a column) or once it gets cast to a type explicitly. If that never happens, I expect that the literal will not be converted to any type and stay the text value that it was in the SQL query string.

This is probably documented, but I don't have time to dig into the manuals right now.

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.