På torsdag 22. juni 2017 kl. 19:30:49, skrev Andres Freund <and...@anarazel.de <mailto:and...@anarazel.de>>: On 2017-06-22 18:10:40 +0300, Achilleas Mantzios wrote: > > Once again having pg_largeobject as a system-catalog prevents LOs > > from working smoothly. Neither replication nor having LOs on a > > different tablespace (by moving pg_largeobject) works.
> I think logical decoding was designed for supporting DML SQL commands > (i.e. a finite set of commands) and not specific functions (lo_*) > which by nature can be arbitrary, infinite and version specific. That's not really the reason. The first reason its currently unsupported is that LOs are stored in a system catalog, and currently all system catalogs are excluded from the change stream. The second problem is how exactly to represent the changes - we can't represent it as the whole LO being changed, as that'd increase the volume of WAL and replicated writes dramatically. Thus we need to invent an API that can represent creation, deletion, and writes to arbitrary offsets, for output plugins. pg_largeobject being a system catalog is the cause of much pain as I've found out. It also prevents moving it to a separate tablespace and maintaining pg_upgrade compatibility. The first initiative would (possibly) be coming up with a new model for storing LOs, not involving system-catalogs. But, pg_largeobject doesn't seem all that magic and appears to contain "logical-decoding compatible" column-types: \d pg_largeobject Table "pg_catalog.pg_largeobject" ┌────────┬─────────┬───────────┬──────────┬─────────┐ │ Column │ Type │ Collation │ Nullable │ Default │ ├────────┼─────────┼───────────┼──────────┼─────────┤ │ loid │ oid │ │ not null │ │ │ pageno │ integer │ │ not null │ │ │ data │ bytea │ │ not null │ │ └────────┴─────────┴───────────┴──────────┴─────────┘ If this was a regular table there would be nothing preventing it from being replicated successfully using logical decoding, correct? > > I wish PG in some future version will address these quirks so one can operate on LOs more smoothly. You're welcome to help... Every time issues arise regarding LOs there seems to be little interest to improve matters, and if it doesn't itch.... -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
