Function to add to bytea blob in postgresql

Question

I'm loading a large amount of files (binary data) into a Postgres database (the files being stored as blobs in the db).

One way I found was to use Large Objects, which works nicely. However, for various reasons I would like to explore the possibility of storing the file contents directly in a bytea field. Now, the Large Object managers allows me to directly write chunks of bytes directly into its buffer, eliminating the need to first load the whole file into memory.

This is rather important for me as im loading the files with several threads working in parallel (and the GC just can't keep up if all threads are simultaneously loading whole files in to memory).

Is there any way I could do this with a bytea parameter? I was thinking of an SQL function which appends a chunk of bytes to the bytea field of a row. Then I can just call this function repeatedly for a blob. I'm rather new to PostgreSQL so I really could do with a hint how such a SQL function could look like.

So, how do I append to a bytea blob in PostgreSQL?

BTW, alternative ideas are also appreciated.

I'm using PostgreSQL 9.3, C# (.NET 4), and the NpgSql Postgres Data provider, the files (blobs) can be everything from a few kb to several mb.

Edit: I've seen references to a byteacat function, seems promising but I can't find an example on how I should use it.

Edit2: In the end it really seems to be easiest to just load the blobs as single parameters in a query... just as long as one makes sure that they are garbage collected fast enough (e.g. not making the byte arrays a property of an object). Postgresql handles the data very nicely!

Answer 1

Edit: Daniel Vérité is right, this gets messy (and slows down massively after some time), I would not recommend this path.

Ok, it was really simple:

There is a || operator which appends binary data... examples:

UPDATE TableWithBlob SET blob = blob || E'123\\000456' WHERE id = 'a356211f-1cb7-436d-80a8-7e608ffds0';

Or as parameterized SQL query (when using NpgSQL Data provider):

UPDATE TableWithBlob SET blob = blob || @Data WHERE ID = @ID;