Re: Extend ALTER DEFAULT PRIVILEGES for large objects

On Fri, 26 Apr 2024 12:23:45 +0200
Matthias van de Meent <boekewurm+postgres@gmail.com> wrote:

> On Fri, 26 Apr 2024 at 10:54, Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> >
> > On Wed, 24 Apr 2024 16:08:39 -0500
> > Nathan Bossart <nathandbossart@gmail.com> wrote:
> >
> > > On Tue, Apr 23, 2024 at 11:47:38PM -0400, Tom Lane wrote:
> > > > On the whole I find this proposed feature pretty unexciting
> > > > and dubiously worthy of the implementation/maintenance effort.
> > >
> > > I don't have any particularly strong feelings on $SUBJECT, but I'll admit
> > > I'd be much more interested in resolving any remaining reasons folks are
> > > using large objects over TOAST.  I see a couple of reasons listed in the
> > > docs [0] that might be worth examining.
> > >
> > > [0] https://www.postgresql.org/docs/devel/lo-intro.html
> >
> > If we could replace large objects with BYTEA in any use cases, large objects
> > would be completely obsolete. However, currently some users use large objects
> > in fact, so improvement in this feature seems beneficial for them.
> >
> >
> > Apart from that, extending TOAST to support more than 1GB data and
> > stream-style access seems a good challenge. I don't know if there was a
> > proposal for this in past. This is  just a thought, for this purpose, we
> > will need a new type of varlena that can contains large size information,
> > and a new toast table schema that can store offset information or some way
> > to convert a offset to chunk_seq.
> 
> If you're interested in this, you may want to check out [0] and [1] as
> threads on the topic of improving TOAST handling of large values ([1]
> being a thread where the limitations of our current external TOAST
> pointer became clear once more), and maybe talk with Aleksander
> Alekseev and Nikita Malakhov. They've been working closely with
> systems that involve toast pointers and their limitations.
> 
> The most recent update on the work of Nikita (reworking TOAST
> handling) [2] is that he got started adapting their externally
> pluggable toast into type-internal methods only, though I've not yet
> noticed any updated patches appear on the list.

Thank you for your information. I'll check the threads you mentioned.

> As for other issues with creating larger TOAST values:
> TOAST has a value limit of ~1GB, which means a single large value (or
> two, for that matter) won't break anything in the wire protocol, as
> DataRow messages have a message size field of uint32 [^3]. However, if
> we're going to allow even larger values to be stored in table's
> attributes, we'll have to figure out how we're going to transfer those
> larger values to (and from) clients. For large objects, this is much
> less of an issue because the IO operations are already chunked by
> design, but this may not work well for types that you want to use in
> your table's columns.

I overlooked this issue. I faced the similar issue when I tried to
pg_dump large text values, although the error was raised from
enlargeStringInfo() in that case....

Regards,
Yugo Nagata


> Kind regards,
> 
> Matthias van de Meent
> 
> [0] https://postgr.es/m/flat/CAN-LCVMq2X%3Dfhx7KLxfeDyb3P%2BBXuCkHC0g%3D9GF%2BJD4izfVa0Q%40mail.gmail.com
> [1] https://postgr.es/m/flat/CAJ7c6TOtAB0z1UrksvGTStNE-herK-43bj22%3D5xVBg7S4vr5rQ%40mail.gmail.com
> [2] https://postgr.es/m/CAN-LCVOgMrda9hOdzGkCMdwY6dH0JQa13QvPsqUwY57TEn6jww%40mail.gmail.com
> 
> [^3] Most, if not all PostgreSQL wire protocol messages have this
> uint32 message size field, but the DataRow one is relevant here as
> it's the one way users get their data out of the database.


-- 
Yugo NAGATA <nagata@sraoss.co.jp>