Question about todo item

I was going through the Todo list looking at the items that are planned
for 7.2 (i.e. those starting with a '-'). I was doing this to see if
any might impact the jdbc driver. The only one that I thought might
have an impact on the jdbc code is the item:

* -Make binary/file in/out interface for TOAST columns (base64)

Marked items are done, not planned for 7.2.

I looked through the 7.2 docs and I couldn't find any reference to this
new functionality, so I am assuming that it isn't completed yet. If this
is going to be done for 7.2, I would like to get a better understanding
of what functionality is going to be provided. That way I can decide
how best to expose that functionality through the jdbc interface.

Not sure on the docs issue, but it is a set of function uuencode,
uudecode, etc that allow binary data to be uuencoded, then loaded into a
bytea field as binary.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

OK. Those functions are in the docs. I didn't relate those functions
and this todo item together.

By 'in/out interface for TOAST columns' I thought this item dealt with
adding large object like functions to read/write/append to TOAST column
data. I know that has been talked about in the past on hackers. But I
don't see it on the todo list. Has that been done?

thanks,
--Barry

Bruce Momjian wrote:

OK. Those functions are in the docs. I didn't relate those functions
and this todo item together.

By 'in/out interface for TOAST columns' I thought this item dealt with
adding large object like functions to read/write/append to TOAST column
data. I know that has been talked about in the past on hackers. But I
don't see it on the todo list. Has that been done?

Only large objects allow that kind of access. I don't think we will do
that for TOAST columns.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

By 'in/out interface for TOAST columns' I thought this item dealt with
adding large object like functions to read/write/append to TOAST column
data. I know that has been talked about in the past on hackers. But I
don't see it on the todo list. Has that been done?

Only large objects allow that kind of access. I don't think we will do
that for TOAST columns.

Barry's right --- that *has* been talked about, and I thought the
consensus was that we needed such functions. You don't necessarily
want to read or write a multi-megabyte TOASTed value all in one go.
If it's not on TODO then it should be. (But I suspect if you check
the archives, you'll discover that this is exactly what the TODO
item was really about.)

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

By 'in/out interface for TOAST columns' I thought this item dealt with
adding large object like functions to read/write/append to TOAST column
data. I know that has been talked about in the past on hackers. But I
don't see it on the todo list. Has that been done?

Only large objects allow that kind of access. I don't think we will do
that for TOAST columns.

Barry's right --- that *has* been talked about, and I thought the
consensus was that we needed such functions. You don't necessarily
want to read or write a multi-megabyte TOASTed value all in one go.
If it's not on TODO then it should be. (But I suspect if you check
the archives, you'll discover that this is exactly what the TODO
item was really about.)

Yes, I kept talking about it, but no one was interested, saying large
objects are better for that kind of access. When the uuencode idea came
around, I though the read/write binary toast idea was dead.

I agree we should have it, but I thought the problem was that we
couldn't come up with an API that worked.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I agree we should have it, but I thought the problem was that we
couldn't come up with an API that worked.

AFAIR, no one's really tried yet. I do not recall any proposals
getting shot down ...

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I agree we should have it, but I thought the problem was that we
couldn't come up with an API that worked.

AFAIR, no one's really tried yet. I do not recall any proposals
getting shot down ...

I keep bugging Jan about it, since pre-7.1 and no one has come up with
an idea. I think the lack of any proposal or anyone even mentioning
they liked the idea made me give up, especially when uuencode at least
gave us binary in/out.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I keep bugging Jan about it, since pre-7.1 and no one has come up with
an idea.

Well, if you want an idea:

BEGIN;

SELECT open_toast_object(toastable_column) FROM tab WHERE ...;

-- app checks that it got exactly one result back

-- app lo_reads and/or lo_writes using ID returned by SELECT

END;

Implementation is left as an exercise for the reader ;-).

Offhand this seems like it would be doable for a column-value that
was actually moved out-of-line by TOAST, since the open_toast_object
function could see and return the TOAST pointer, and then the read/
write operations just hack on rows in pg_largeobject. The hard part
is how to provide equivalent functionality (transparent to the client
of course) when the particular value you select has *not* been moved
out-of-line. Ideas anyone?

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I keep bugging Jan about it, since pre-7.1 and no one has come up with
an idea.

Well, if you want an idea:

BEGIN;

SELECT open_toast_object(toastable_column) FROM tab WHERE ...;

-- app checks that it got exactly one result back

-- app lo_reads and/or lo_writes using ID returned by SELECT

END;

Implementation is left as an exercise for the reader ;-).

Offhand this seems like it would be doable for a column-value that
was actually moved out-of-line by TOAST, since the open_toast_object
function could see and return the TOAST pointer, and then the read/
write operations just hack on rows in pg_largeobject. The hard part

I am confused how pg_largeobject is involved?

is how to provide equivalent functionality (transparent to the client
of course) when the particular value you select has *not* been moved
out-of-line. Ideas anyone?

Don't forget compression of TOAST columns. How do you fseek/read/write
in there?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Offhand this seems like it would be doable for a column-value that
was actually moved out-of-line by TOAST, since the open_toast_object
function could see and return the TOAST pointer, and then the read/
write operations just hack on rows in pg_largeobject. The hard part

I am confused how pg_largeobject is involved?

s/pg_largeobject/toast_table_for_relation/ ... sorry about that ...

Don't forget compression of TOAST columns. How do you fseek/read/write
in there?

Well, you can *do* it, just don't expect it to be fast. The
implementation would have to read or write most of the value, not just
the segment you wanted. A person who actually expected to use this
stuff would likely want to disable compression on a column he wanted
random access within.

Hmm ... that provides an idea. We could easily add some additional
'attstorage' settings that say *all* values of a column must be forced
out-of-line (with or without allowing compression), regardless of size.
Then, open_toast_object would work reliably on such a column. One
possible user API to such an infrastructure is to invent BLOB and CLOB
datatypes, which are just like bytea and text except that they force the
appropriate attstorage value. Ugly as sin, ain't it ... but I bet it
could be made to work.

Okay, there's your idea. Now, who can do better?

regards, tom lane

Bruce Momjian wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I agree we should have it, but I thought the problem was that we
couldn't come up with an API that worked.

AFAIR, no one's really tried yet. I do not recall any proposals
getting shot down ...

I keep bugging Jan about it, since pre-7.1 and no one has come up with
an idea. I think the lack of any proposal or anyone even mentioning
they liked the idea made me give up, especially when uuencode at least
gave us binary in/out.

Can anyone recall, why was uuencode chosen over base64 encoding ?

-----------------
Hannu

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I keep bugging Jan about it, since pre-7.1 and no one has come up with
an idea.

Well, if you want an idea:

BEGIN;

SELECT open_toast_object(toastable_column) FROM tab WHERE ...;

-- app checks that it got exactly one result back

-- app lo_reads and/or lo_writes using ID returned by SELECT

END;

Implementation is left as an exercise for the reader ;-).

Offhand this seems like it would be doable for a column-value that
was actually moved out-of-line by TOAST, since the open_toast_object
function could see and return the TOAST pointer, and then the read/
write operations just hack on rows in pg_largeobject. The hard part
is how to provide equivalent functionality (transparent to the client
of course) when the particular value you select has *not* been moved
out-of-line. Ideas anyone?

I'd propose the folllowing -

BEGIN;

DECLARE toastaccesscursor
CURSOR FOR
SELECT open_toast_object_handle(toastable_column) as
toast_object_handle FROM tab WHERE ...;

-- while you get any rows

FETCH 1 IN toastaccesscursor;

-- app lo_reads and/or lo_writes using toast_object_handle
returned by SELECT

END;

If we really wanted to have lo_xxx functionality on any toastable column
it should be doable by
creating a fake toast-handle and manipulating the column value directly,
preferrably automatically
moving the lo_written column to toast. Faking the handle should be easy
as it has to live only while
cursor is positioned on affected row .

But your another idea of creating special [B|C]LOB types that are
allways saved to toast seems nicer

CREATE TABLE breakfast (
main eggs_and_bacon WITH TOAST = 'always,nocompress'
);

and just raise an error or do a silent conversion if a section is
lo_written in a compressed
or non-toasted column.

As TOAST is a general purpose feature of postgres I think that providing
the WITH options is more
desirable than special types for only a few of them.

CLOB and BLOB could still be provided as shorthand names similar to
SERIAL.

---------------
Hannu

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I agree we should have it, but I thought the problem was that we
couldn't come up with an API that worked.

AFAIR, no one's really tried yet. I do not recall any proposals
getting shot down ...

One of the problems I saw, and that's probably why we don't
have a proposal yet, is, that the size of the data is
recorded in the toast reference held in the main tuple. If
you later open the toast value for writing, you'll change the
size, but you'd need to change it in the main tuple too,
what'd require a regular update on the main tuple, what I
don't think we want to have here.

The other problem is, if you insert a tuple containing a
small value (e.g. empty string), it'll not get toasted and
you can't force it to get. Later you open it for writing and
pump a CD-image into. How do we convert the existing empty
text datum into a toast reference in the main tuple?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I keep bugging Jan about it, since pre-7.1 and no one has come up with
an idea.

Well, if you want an idea:

BEGIN;

SELECT open_toast_object(toastable_column) FROM tab WHERE ...;

-- app checks that it got exactly one result back

-- app lo_reads and/or lo_writes using ID returned by SELECT

END;

Implementation is left as an exercise for the reader ;-).

Offhand this seems like it would be doable for a column-value that
was actually moved out-of-line by TOAST, since the open_toast_object
function could see and return the TOAST pointer, and then the read/
write operations just hack on rows in pg_largeobject. The hard part
is how to provide equivalent functionality (transparent to the client
of course) when the particular value you select has *not* been moved
out-of-line. Ideas anyone?

TOAST values aren't stored in pg_largeobject. And how do you
seek to a position in a compressed and then sliced object? We
need a way to force the object over a streaming interface
into uncompressed toast slices first. Let me think about it
for two days, Okay?

The interface lacks imho a mode (r/w/rw/a) argument. Other
than that I'd like this part.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Jan Wieck <JanWieck@yahoo.com> writes:

One of the problems I saw, and that's probably why we don't
have a proposal yet, is, that the size of the data is
recorded in the toast reference held in the main tuple. If
you later open the toast value for writing, you'll change the
size, but you'd need to change it in the main tuple too,
what'd require a regular update on the main tuple, what I
don't think we want to have here.

Well, in fact, maybe we *should*. I was thinking last night about
the fact that large objects as they stand are broken from a
permissions-checking point of view: anyone who knows an LO's OID
can read or write it. A LO-style interface for toasted columns must
not be so brain-dead. This says that a SELECT open_toast_object()
should deliver a read-only object reference, and that if you want
to update, you should have to do an UPDATE.

Now a read-only TOAST LO reference strikes me as no problem. If the
open() function finds that it's been handed a not-toasted value, it
can just save the value verbatim in the open-LO-reference table.
The value is not large, by definition, so this will work fine.

As for the update side of things, the best idea I can come up with
is a multi-phase operation: open the value with a select, read/write
the reference, store the updated reference with UPDATE. Something
like:

1. SELECT writable_toast_reference(column) FROM table WHERE ...;

(Actually, SELECT FOR UPDATE would be the more common idiom.)

2. Read and/or write the LO reference returned by SELECT. Note that
this must be defined to read/write a temporary work area --- if the
transaction aborts in this part, or commits without doing UPDATE,
nothing has happened to the stored value referenced by the main table
row. (I think this happens automatically if we are hacking rows in
a toast table. If we are hacking an in-line value stored in the
LO-reference table, we might at some point decide we need to shove it
out to disk.)

3. UPDATE table SET column = write_toast_reference(objectref) WHERE ...;

write_toast_reference extracts the toastable column's data or reference
from the LO table, closes the open LO reference (so you can't continue
hacking the data afterwards), and proceeds with a normal UPDATE.

It would also be pretty straightforward to extend this to the INSERT
case: we just need an "open" function that creates a new, empty object
of a TOASTable type in the LO reference table. Write on this, and
finally invoke write_toast_reference() in the INSERT.

Kinda grotty, but implementable, and it doesn't require a whole new set
of permissions concepts. Can anyone improve on this?

regards, tom lane

Tom Lane wrote:

Jan Wieck <JanWieck@yahoo.com> writes:

One of the problems I saw, and that's probably why we don't
have a proposal yet, is, that the size of the data is
recorded in the toast reference held in the main tuple. If
you later open the toast value for writing, you'll change the
size, but you'd need to change it in the main tuple too,
what'd require a regular update on the main tuple, what I
don't think we want to have here.

Well, in fact, maybe we *should*.

I think so too, as we shouldnt do in-place modification in the toast
table anyway but give changed pages new trx ids, i.e UPDATE them.

it could be somewhat tricky to change just a few pages if there are
some inter page pointers in toast-table. If its all done with regular
index only then this should pose no problem.

I was thinking last night about
the fact that large objects as they stand are broken from a
permissions-checking point of view: anyone who knows an LO's OID
can read or write it. A LO-style interface for toasted columns must
not be so brain-dead. This says that a SELECT open_toast_object()
should deliver a read-only object reference, and that if you want
to update, you should have to do an UPDATE.

Now a read-only TOAST LO reference strikes me as no problem. If the
open() function finds that it's been handed a not-toasted value, it
can just save the value verbatim in the open-LO-reference table.
The value is not large, by definition, so this will work fine.

As for the update side of things, the best idea I can come up with
is a multi-phase operation: open the value with a select, read/write
the reference, store the updated reference with UPDATE. Something
like:

1. SELECT writable_toast_reference(column) FROM table WHERE ...;

(Actually, SELECT FOR UPDATE would be the more common idiom.)

2. Read and/or write the LO reference returned by SELECT. Note that
this must be defined to read/write a temporary work area --- if the
transaction aborts in this part, or commits without doing UPDATE,
nothing has happened to the stored value referenced by the main table
row. (I think this happens automatically if we are hacking rows in
a toast table. If we are hacking an in-line value stored in the
LO-reference table, we might at some point decide we need to shove it
out to disk.)

but in both inline and toast-table modified pages should have new
transaction id's like regular tuples and thus be handled by regular
transaction commit/abort mechanics, at least this seema as a postgres
way to do it .

3. UPDATE table SET column = write_toast_reference(objectref) WHERE ...;

write_toast_reference extracts the toastable column's data or reference
from the LO table, closes the open LO reference (so you can't continue
hacking the data afterwards), and proceeds with a normal UPDATE.

It would also be pretty straightforward to extend this to the INSERT
case: we just need an "open" function that creates a new, empty object
of a TOASTable type in the LO reference table. Write on this, and
finally invoke write_toast_reference() in the INSERT.

Kinda grotty, but implementable, and it doesn't require a whole new set
of permissions concepts. Can anyone improve on this?

If toast table has the same permissions as the main table and lo_write
honours these then we should be ok.

---------------
Hannu

Jan Wieck <JanWieck@yahoo.com> writes:

For all the default operations, the system would treat the
datums still like regular attributes. That means, that an

INSERT ... SELECT ...

copying a BLOB from one table to another (and that's correct,
BLOB's should have copy semantics) would force the entire
BLOB data into memory ... and ... then ... after ... some
... time ... run out of memory.

This does not seem expensive or difficult to solve. tuptoaster.c
will be handed a TOAST pointer as part of heap_insert, and it will
know that it has to duplicate the value. It seems an easy, localized
change to persuade it to do that copying chunk-at-a-time instead of
suck-it-all-in-then-spew-it-all-out.

regards, tom lane

I wrote:

Tom Lane wrote:

Implementation is left as an exercise for the reader ;-).

Offhand this seems like it would be doable for a column-value that
was actually moved out-of-line by TOAST, since the open_toast_object
function could see and return the TOAST pointer, and then the read/
write operations just hack on rows in pg_largeobject. The hard part
is how to provide equivalent functionality (transparent to the client
of course) when the particular value you select has *not* been moved
out-of-line. Ideas anyone?

TOAST values aren't stored in pg_largeobject. And how do you
seek to a position in a compressed and then sliced object? We
need a way to force the object over a streaming interface
into uncompressed toast slices first. Let me think about it
for two days, Okay?

The interface lacks imho a mode (r/w/rw/a) argument. Other
than that I'd like this part.

The idea of making BLOB and CLOB simply toast forced special
datatypes and add streaming access functions lacks one
important requirement.

For all the default operations, the system would treat the
datums still like regular attributes. That means, that an

INSERT ... SELECT ...

copying a BLOB from one table to another (and that's correct,
BLOB's should have copy semantics) would force the entire
BLOB data into memory ... and ... then ... after ... some
... time ... run out of memory.

We don't get far without a real new datatype and special
support on the heap access level. We should for sure reuse
the toast shadow table to store the data. But that's the only
connection to toast here.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Can this be added to the TODO list? (actually put back on the TODO list)
Along with this email thread?

I feel that it is very important to have BLOB support in postgres that
is similar to what the commercial databases provide. This could either
mean fixing the current implementation or adding additional capabilities
to toasted columns.

The major problem with the current LargeObject implementation is that
when the row containing the LargeObject is deleted the LargeObject
isn't. This can be a useful feature under some circumstances, but it
isn't how other databases handle BLOBs. Thus porting code from other
databases is a challenge. While it is true that this can be worked
around through triggers, I don't like the manual nature of the workarounds.

thanks,
--Barry

Tom Lane wrote:

I have added to TODO:

BINARY DATA
o -Add non-large-object binary field (already exists -- bytea)
o -Make binary interface for TOAST columns (base64)
o Improve vacuum of large objects (/contrib/vacuumlo)
o Add security checking for large objects
o Make file in/out interface for TOAST columns, similar to large object
interface (force out-of-line storage and no compression)
o Auto-delete large objects when referencing row is deleted

Can this be added to the TODO list? (actually put back on the TODO list)
Along with this email thread?

I feel that it is very important to have BLOB support in postgres that
is similar to what the commercial databases provide. This could either
mean fixing the current implementation or adding additional capabilities
to toasted columns.

The major problem with the current LargeObject implementation is that
when the row containing the LargeObject is deleted the LargeObject
isn't. This can be a useful feature under some circumstances, but it
isn't how other databases handle BLOBs. Thus porting code from other
databases is a challenge. While it is true that this can be worked
around through triggers, I don't like the manual nature of the workarounds.

thanks,
--Barry

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Offhand this seems like it would be doable for a column-value that
was actually moved out-of-line by TOAST, since the open_toast_object
function could see and return the TOAST pointer, and then the read/
write operations just hack on rows in pg_largeobject. The hard part

I am confused how pg_largeobject is involved?

s/pg_largeobject/toast_table_for_relation/ ... sorry about that ...

Don't forget compression of TOAST columns. How do you fseek/read/write
in there?

Well, you can *do* it, just don't expect it to be fast. The
implementation would have to read or write most of the value, not just
the segment you wanted. A person who actually expected to use this
stuff would likely want to disable compression on a column he wanted
random access within.

Hmm ... that provides an idea. We could easily add some additional
'attstorage' settings that say *all* values of a column must be forced
out-of-line (with or without allowing compression), regardless of size.
Then, open_toast_object would work reliably on such a column. One
possible user API to such an infrastructure is to invent BLOB and CLOB
datatypes, which are just like bytea and text except that they force the
appropriate attstorage value. Ugly as sin, ain't it ... but I bet it
could be made to work.

Okay, there's your idea. Now, who can do better?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026