On Mon, May 23, 2022 at 2:21 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > The big picture here is that Postgres is a hodgepodge of features > that were developed at different times and with different quality > standards, over a period that's now approaching forty years. > Some of these features interoperate better than others. Large > objects, in particular, are largely a mess with a lot of issues > such as not having a well-defined garbage collection mechanism.
Well, in one sense, the garbage mechanism is pretty well-defined: objects get removed when you explicitly remove them. Given that PostgreSQL has no idea that the value you store in your OID column has any relationship with the large object that is identified by that OID, I don't see how it could work any other way. The problem isn't really that the behavior is unreasonable or even badly-designed. The real issue is that it's not what people want. I used to think that what people wanted was something like TOAST. After all, large objects can be a lot bigger than toasted values, and that size limitation might be a problem for some people. But then I realized that there's a pretty important behavioral difference: when you fetch a row that contains an OID that happens to identify a large object, you can look at the rest of the row and then decide whether or not you want to fetch the large object. If you just use a regular column, with a data type of text or bytea, and store really big values in there, you don't have that option: the server sends you all the data whether you want it or not. Similarly, on the storage side, you can't send the value to the server a chunk at a time, which means you have to buffer the whole value in memory on the client side first, which might be inconvenient. I don't think that allowing larger toasted values would actually be that hard. We couldn't do it with varlena, but we could introduce a new negative typlen that corresponds to some new representation that permits larger values. That would require sorting out various places where we randomly limit things to 1GB, but I think that's pretty doable. However, I'm not sure that would really solve any problem, because who wants to malloc(1TB) in your application, and then probably again in libpq, to schlep that value to the server -- and then do the same thing in reverse when you get the value back? Without some notion of certain values that are accessed via streaming rather than monolithically, I can't really imagine getting to a satisfying place. I realize I've drifted away from the original topic a bit. I just think it's interesting to think about what a better mechanism might look like. -- Robert Haas EDB: http://www.enterprisedb.com
