Обсуждение: hooks for supporting third party blobs?
A recent project of ours involved storing/fetching some reasonably large datasets in a home-brew datatype. The datasets tended to range from a few megabytes, to several gigabytes. We were seeing some nonlinear slowness with using native large objects with larger datasets, presumably due to the increasing depth of the btree index used to track all the little pieces of the blobs.
After some careful consideration, we implemented an alternative to large objects, a system based on storing files in a particular directory, and storing a reference to the files in the database. It worked and gave us good and consistent performance. However, it doesn't support transactions (no isolation, no rollback). We can probably implement some backend code to support such functionality, but the trick is getting the postgres server to keep our code in the loop (so to speak) about when a rollback should be done (and to when).
Is anyone aware of any hooks to support schemes such as ours, or has solved a similar problem?
Thank you.
After some careful consideration, we implemented an alternative to large objects, a system based on storing files in a particular directory, and storing a reference to the files in the database. It worked and gave us good and consistent performance. However, it doesn't support transactions (no isolation, no rollback). We can probably implement some backend code to support such functionality, but the trick is getting the postgres server to keep our code in the loop (so to speak) about when a rollback should be done (and to when).
Is anyone aware of any hooks to support schemes such as ours, or has solved a similar problem?
Thank you.
**********************************************
Eric Davies, M.Sc.
Barrodale Computing Services Ltd.
Tel: (250) 472-4372 Fax: (250) 472-4373
Web: http://www.barrodale.com
Email: eric@barrodale.com
**********************************************
Mailing Address:
P.O. Box 3075 STN CSC
Victoria BC Canada V8W 3W2
Shipping Address:
Hut R, McKenzie Avenue
University of Victoria
Victoria BC Canada V8W 3W2
**********************************************
On Mon, Dec 06, 2004 at 05:11:21PM -0800, Eric Davies wrote: > Is anyone aware of any hooks to support schemes such as ours, or has solved > a similar problem? There's RegisterXactCallback() and RegisterSubXactCallback() functions that may be what you want. They are called whenever a transaction or subtransaction starts, commits, or aborts. You could probably keep a list of things modified during the transaction, so you can clean up at transaction end. (Much like the storage manager does: it only unlinks files for dropped tables at transaction commit.) Make sure to react appropiately at subtransaction abort ... -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Si quieres ser creativo, aprende el arte de perder el tiempo"
Eric Davies <Eric@barrodale.com> writes:
> A recent project of ours involved storing/fetching some reasonably large
> datasets in a home-brew datatype. The datasets tended to range from a few
> megabytes, to several gigabytes. We were seeing some nonlinear slowness
> with using native large objects with larger datasets, presumably due to the
> increasing depth of the btree index used to track all the little pieces of
> the blobs.
Did you do any profiling to back up that "presumably"? It seems at
least as likely to me that this was caused by some easily-fixed
inefficiency somewhere. There are still a lot of O(N^2) algorithms
in the backend that no one has run up against yet ...
regards, tom lane