
> > I am concerned about the case where VACUUM FULL:
> > 1. inserts heap tuple to new location using FrozenXid
> > 2. updates original heap tuples's xmax
> 
> It doesn't matter whether it's FrozenXid or not.  The tuple is not
> visible if it's got the wrong setting of HEAP_MOVED_OFF/IN.

But the FrozenXid tuple has HEAP_MOVED_IN and the original has
not yet been altered to HEAP_MOVED_OFF because of abort.
Is the HEAP_MOVED_IN tuple not visible ?

Andreas

Thu, 17 Jan 2002 15:12:52 +0100
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: tuptoaster.c must *not* use SnapshotAny 
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
>> It doesn't matter whether it's FrozenXid or not.  The tuple is not
>> visible if it's got the wrong setting of HEAP_MOVED_OFF/IN.

> But the FrozenXid tuple has HEAP_MOVED_IN and the original has
> not yet been altered to HEAP_MOVED_OFF because of abort.
> Is the HEAP_MOVED_IN tuple not visible ?

Right.  Actually it doesn't matter whether the old tuple has
HEAP_MOVED_OFF or not; it's still visible *until* the VACUUM commits.
The commit atomically switches us from the state where the unmoved
tuples are good to the state where the moved ones are good.

This is all exactly the same whether FrozenXid is involved or not.

			regards, tom lane

Thu, 17 Jan 2002 09:22:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny 
Tom Lane wrote:
> "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:
> >> It doesn't matter whether it's FrozenXid or not.  The tuple is not
> >> visible if it's got the wrong setting of HEAP_MOVED_OFF/IN.
>
> > But the FrozenXid tuple has HEAP_MOVED_IN and the original has
> > not yet been altered to HEAP_MOVED_OFF because of abort.
> > Is the HEAP_MOVED_IN tuple not visible ?
>
> Right.  Actually it doesn't matter whether the old tuple has
> HEAP_MOVED_OFF or not; it's still visible *until* the VACUUM commits.
> The commit atomically switches us from the state where the unmoved
> tuples are good to the state where the moved ones are good.
>
> This is all exactly the same whether FrozenXid is involved or not.

    Originally I added SnapshotAny only to solve the problem that
    the lookup of the toast table happens  independant  from  the
    access  to  the  main  tuple, and that in fact the main tuple
    could have been deleted by the own transaction in between.

    Would changing SnapshotAny  to  honor  HEAP_MOVED_*  fix  the
    problem?  If nothing else by now uses this snapshot, it could
    be done in place.


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


Thu, 17 Jan 2002 13:11:24 -0500 (EST)
Jan Wieck <janwieck@yahoo.com>
Re: tuptoaster.c must *not* use SnapshotAny
Jan Wieck <janwieck@yahoo.com> writes:
>     Originally I added SnapshotAny only to solve the problem that
>     the lookup of the toast table happens  independant  from  the
>     access  to  the  main  tuple, and that in fact the main tuple
>     could have been deleted by the own transaction in between.

>     Would changing SnapshotAny  to  honor  HEAP_MOVED_*  fix  the
>     problem?  If nothing else by now uses this snapshot, it could
>     be done in place.

What I've done is add an additional snapshot class SnapshotToast
that behaves that way.  There are a couple other uses of SnapshotAny
that I didn't think I wanted to mess with.

			regards, tom lane

Thu, 17 Jan 2002 16:47:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny 
Tom Lane wrote:
> 
> Jan Wieck <janwieck@yahoo.com> writes:
> >     Originally I added SnapshotAny only to solve the problem that
> >     the lookup of the toast table happens  independant  from  the
> >     access  to  the  main  tuple, and that in fact the main tuple
> >     could have been deleted by the own transaction in between.
> 
> >     Would changing SnapshotAny  to  honor  HEAP_MOVED_*  fix  the
> >     problem?  If nothing else by now uses this snapshot, it could
> >     be done in place.
> 
> What I've done is add an additional snapshot class SnapshotToast
> that behaves that way.  There are a couple other uses of SnapshotAny
> that I didn't think I wanted to mess with.

AFAIR SnapshotAny was introduced for Foreign Key stuff.
SnapshotToast doesn't seem a proper solution to me but
7.2 should be released ASAP anyway.

regards,
Hiroshi Inoue

Fri, 18 Jan 2002 10:55:44 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: tuptoaster.c must *not* use SnapshotAny
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> SnapshotToast doesn't seem a proper solution to me

Do you have a better idea in mind?

			regards, tom lane

Thu, 17 Jan 2002 21:07:55 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny 
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > SnapshotToast doesn't seem a proper solution to me
> 
> Do you have a better idea in mind?

The same snapshot for both the main and the toast table
seems better though I don't know how it's difficult to
change. For example is it possible to update a toast
chunk partially using SnapshotToast ?

regards,
Hiroshi Inoue

Fri, 18 Jan 2002 11:27:23 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: tuptoaster.c must *not* use SnapshotAny
Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> The same snapshot for both the main and the toast table
> seems better though I don't know how it's difficult to
> change.

That would be cleaner but I don't see any way to do it.  The toasted
value gets passed all over the system, potentially, before someone asks
to detoast it.  At that point you really don't have a good way to know
which snapshot was used to fetch the main-table row.

> For example is it possible to update a toast
> chunk partially using SnapshotToast ?

As things stand (with either SnapshotToast or the old SnapshotAny way)
it is never possible to update an individual toast value, either
partially or wholly.  All you can do is lay down a new toast value (with
a new identifying OID) and then delete the old one.

But I'm not sure that this is wrong, or fixable.  Trying to update part
of a toasted value is very much like wanting to update part of an
existing row in-place, which we cannot possibly do.  We have to lay down
a whole new row whenever any part of it is updated.

			regards, tom lane

Thu, 17 Jan 2002 22:12:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny 
Tom Lane wrote:
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > The same snapshot for both the main and the toast table
> > seems better though I don't know how it's difficult to
> > change.
>
> That would be cleaner but I don't see any way to do it.  The toasted
> value gets passed all over the system, potentially, before someone asks
> to detoast it.  At that point you really don't have a good way to know
> which snapshot was used to fetch the main-table row.

    That's  IMHO  not the point. The design of toast is that if a
    main heap tuple points to a toast id, and the heap  tuple  at
    scan  time  was  visible,  then  no matter what, the value is
    readable.  Regardless  of  any  isolation  level  or  command
    counter  or  whatnot.  That's achieved by allways assigning a
    new toast ID on change of a toasted value. So if the  columns
    value  changed, the old and new rows point to different toast
    values not by visibility but by toast  id.   If  it  did  not
    change, they point to the same.

    The  problem  arises  only because SnapshotAny is *too* blind
    and ignores the special vacuum HEAP_MOVED_* states, so that a
    scan after a crashed vacuum full, that left yet unborn moved-
    in tuples in the toast table, can see them.

    Since the design of toast is NO NEED to care about  snapshots
    during  toast  value  fetching,  I don't see why doing so now
    would be a cleaner solution.


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


Thu, 17 Jan 2002 22:55:17 -0500 (EST)
Jan Wieck <janwieck@yahoo.com>
Re: tuptoaster.c must *not* use SnapshotAny
Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> 
> > For example is it possible to update a toast
> > chunk partially using SnapshotToast ?
> 
> As things stand (with either SnapshotToast or the old SnapshotAny way)
> it is never possible to update an individual toast value, either
> partially or wholly.  All you can do is lay down a new toast value (with
> a new identifying OID) and then delete the old one.
> 
> But I'm not sure that this is wrong, or fixable.  Trying to update part
> of a toasted value is very much like wanting to update part of an
> existing row in-place, which we cannot possibly do.

Bytea seems to be considered as a candidate for BLOB
though I think the entirely new type is preferable.
It seems impossible to implement a functionality like
inv_write using bytea which the current large object
stuff has.

regards,
Hiroshi Inoue

Fri, 18 Jan 2002 13:00:21 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: tuptoaster.c must *not* use SnapshotAny
> Tom Lane wrote:
> > 
> > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > 
> > > For example is it possible to update a toast
> > > chunk partially using SnapshotToast ?
> > 
> > As things stand (with either SnapshotToast or the old SnapshotAny way)
> > it is never possible to update an individual toast value, either
> > partially or wholly.  All you can do is lay down a new toast value (with
> > a new identifying OID) and then delete the old one.
> > 
> > But I'm not sure that this is wrong, or fixable.  Trying to update part
> > of a toasted value is very much like wanting to update part of an
> > existing row in-place, which we cannot possibly do.
> 
> Bytea seems to be considered as a candidate for BLOB
> though I think the entirely new type is preferable.
> It seems impossible to implement a functionality like
> inv_write using bytea which the current large object
> stuff has.

Agreed.  I think that was the reason we kept TOAST and large objects,
because large objects were designed for random read-write.  If we can
get large objects to auto-delete, probably with pg_depend, we can then
use them seamlessly with BLOB I/O routines.

-- 
  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

Thu, 17 Jan 2002 23:20:01 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny
Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> > >
> > > Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > >
> > > > For example is it possible to update a toast
> > > > chunk partially using SnapshotToast ?
> > >
> > > As things stand (with either SnapshotToast or the old SnapshotAny way)
> > > it is never possible to update an individual toast value, either
> > > partially or wholly.  All you can do is lay down a new toast value (with
> > > a new identifying OID) and then delete the old one.
> > >
> > > But I'm not sure that this is wrong, or fixable.  Trying to update part
> > > of a toasted value is very much like wanting to update part of an
> > > existing row in-place, which we cannot possibly do.
> >
> > Bytea seems to be considered as a candidate for BLOB
> > though I think the entirely new type is preferable.
> > It seems impossible to implement a functionality like
> > inv_write using bytea which the current large object
> > stuff has.
> 
> Agreed.  I think that was the reason we kept TOAST and large objects,
> because large objects were designed for random read-write.  If we can
> get large objects to auto-delete, probably with pg_depend, we can then
> use them seamlessly with BLOB I/O routines.

Oops I seem to have missed the discussion about excluding
bytea from the candidate from BLOB. Yes now we seem to have
a good reason to exclude existent type from the candidate
of BLOB.

regards,
Hiroshi Inoue

Fri, 18 Jan 2002 14:10:15 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: tuptoaster.c must *not* use SnapshotAny
> > Agreed.  I think that was the reason we kept TOAST and large objects,
> > because large objects were designed for random read-write.  If we can
> > get large objects to auto-delete, probably with pg_depend, we can then
> > use them seamlessly with BLOB I/O routines.
> 
> Oops I seem to have missed the discussion about excluding
> bytea from the candidate from BLOB. Yes now we seem to have
> a good reason to exclude existent type from the candidate
> of BLOB.

Well, we had the discussion when Jan was adding TOAST, and Jan was
saying we still need large objects for I/O purposes and for very large
items.

-- 
  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

Fri, 18 Jan 2002 00:12:23 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny
Bruce Momjian wrote:
>
> Agreed.  I think that was the reason we kept TOAST and large objects,
> because large objects were designed for random read-write.  If we can
> get large objects to auto-delete, probably with pg_depend, we can then
> use them seamlessly with BLOB I/O routines.

    Not  entirely.  BLOB's  should  have  copy semantics, so that
    doing

        INSERT INTO tab1 SELECT id, blob_column FROM tab2 ...

    and subsequently changing the blob in  either  tab1  or  tab2
    does  NOT change the blob in the other table. Currently, even
    if you make the pg_depend stuff smart enough to let the  blob
    live  until  all  references  are  gone, the two tables would
    share the same blob.  And that's not only  IMHO  wrong,  it's
    also incompatible to Oracle :-)


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


Fri, 18 Jan 2002 00:21:26 -0500 (EST)
Jan Wieck <janwieck@yahoo.com>
Re: tuptoaster.c must *not* use SnapshotAny
Jan Wieck wrote:
> Bruce Momjian wrote:
> >
> > Agreed.  I think that was the reason we kept TOAST and large objects,
> > because large objects were designed for random read-write.  If we can
> > get large objects to auto-delete, probably with pg_depend, we can then
> > use them seamlessly with BLOB I/O routines.
> 
>     Not  entirely.  BLOB's  should  have  copy semantics, so that
>     doing
> 
>         INSERT INTO tab1 SELECT id, blob_column FROM tab2 ...
> 
>     and subsequently changing the blob in  either  tab1  or  tab2
>     does  NOT change the blob in the other table. Currently, even
>     if you make the pg_depend stuff smart enough to let the  blob
>     live  until  all  references  are  gone, the two tables would
>     share the same blob.  And that's not only  IMHO  wrong,  it's
>     also incompatible to Oracle :-)

Yes, we have to add duplication large object stuff hard-wired to that new data
type.

-- 
  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

Fri, 18 Jan 2002 00:27:10 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Well, we had the discussion when Jan was adding TOAST, and Jan was
> saying we still need large objects for I/O purposes and for very large
> items.

Yeah.  I hadn't realized until this conversation that TOAST couldn't
support partial updates of huge values.  That is a definite problem for
supporting lo_read/lo_write-type operations.

			regards, tom lane

Fri, 18 Jan 2002 00:27:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny 
Bruce Momjian wrote:
> 
> > > Agreed.  I think that was the reason we kept TOAST and large objects,
> > > because large objects were designed for random read-write.  If we can
> > > get large objects to auto-delete, probably with pg_depend, we can then
> > > use them seamlessly with BLOB I/O routines.
> >
> > Oops I seem to have missed the discussion about excluding
> > bytea from the candidate from BLOB. Yes now we seem to have
> > a good reason to exclude existent type from the candidate
> > of BLOB.
> 
> Well, we had the discussion when Jan was adding TOAST, and Jan was
> saying we still need large objects for I/O purposes and for very large
> items.

Though I've often seen the reference to bytea BLOB
I remember no clear negation. Don't we have to negate
it clearly from the first ?

regards,
Hiroshi Inoue

Fri, 18 Jan 2002 14:45:47 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: tuptoaster.c must *not* use SnapshotAny
> > Well, we had the discussion when Jan was adding TOAST, and Jan was
> > saying we still need large objects for I/O purposes and for very large
> > items.
> 
> Though I've often seen the reference to bytea BLOB
> I remember no clear negation. Don't we have to negate
> it clearly from the first ?

As I remember, we only mention BLOB when people ask.  We don't refer to
either as BLOB, only as TOAST/bytea and large objects.

-- 
  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

Fri, 18 Jan 2002 00:53:25 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: tuptoaster.c must *not* use SnapshotAny
Hiroshi Inoue wrote:
[Charset iso-2022-jp unsupported, skipping...]
> Though I've often seen the reference to bytea BLOB
> I remember no clear negation. Don't we have to negate
> it clearly from the first ?

    Any  datatype that has the potential to be loaded into memory
    entirely by the backend is dangerous. It  leads  to  problems
    like  "out  of  swapspace",  which  aren't  really  funny  in
    production.

    We need something that  is  restricted  to  streaming  access
    only.  Yet, it needs to have copy semantics.

    That  means to me, that this will not only be a new datatype,
    but  some  more  infrastructure  too.  We  need  INSERT   ...
    RETURNING  ...   where  the  values  for  BLOBs  are given as
    NEW_BLOB() and the returned identifier is  subsequently  used
    to open the created BLOBs for writing and pump the data in.

    We  need  heap  access level tuple support to duplicate those
    values on INSERT ... SELECT, to remove them on DELETE and  to
    manipulate them on UPDATE.

    We  need new functionality in procedural languages to support
    streaming in and out of these guy's, where the actual data is
    buffered  in  temp  files  and  only  blob identifiers passed
    around.

    I see the final storage of blob data to happen in  the  toast
    table,   sliced,   diced   and   handy   (most  probably  not
    compressed). But I don't see an easy way to abuse  the  bytea
    datatype for real blob support.


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


Fri, 18 Jan 2002 01:06:11 -0500 (EST)
Jan Wieck <janwieck@yahoo.com>
Re: tuptoaster.c must *not* use SnapshotAny
Hiroshi Inoue wrote:

> Though I've often seen the reference to bytea BLOB
> I remember no clear negation. Don't we have to negate
> it clearly from the first ?
> 
> regards,
> Hiroshi Inoue


With the changes in 7.2, bytea is actually very close to matching the
SQL99 definition of binary strings, which it also refers to as BLOB.
BLOBS should include support for (section 4.3):

<comparison predicate> - yes
<blob concatenation> - yes
<blob substring function> - yes
<blob overlay function> - no
<trim function> - yes
<length expression> - yes
<position expression> - yes
<like predicate> - yes

Other than the overlay function, the primary thing missing is the use of
 hex as the I/O representation.

See table 3-9 at
http://candle.pha.pa.us/main/writings/pgsql/sgml/datatype-binary.html
for more info.

Joe




Thu, 17 Jan 2002 22:16:34 -0800
Joe Conway <jconway@cox.net>
Re: tuptoaster.c must *not* use SnapshotAny
Jan Wieck wrote:

> 
>     Any  datatype that has the potential to be loaded into memory
>     entirely by the backend is dangerous. It  leads  to  problems
>     like  "out  of  swapspace",  which  aren't  really  funny  in
>     production.
> 
>     We need something that  is  restricted  to  streaming  access
>     only.  Yet, it needs to have copy semantics.
> 
>     That  means to me, that this will not only be a new datatype,
>     but  some  more  infrastructure  too.  We  need  INSERT   ...
>     RETURNING  ...   where  the  values  for  BLOBs  are given as
>     NEW_BLOB() and the returned identifier is  subsequently  used
>     to open the created BLOBs for writing and pump the data in.
> 
>     We  need  heap  access level tuple support to duplicate those
>     values on INSERT ... SELECT, to remove them on DELETE and  to
>     manipulate them on UPDATE.
> 
>     We  need new functionality in procedural languages to support
>     streaming in and out of these guy's, where the actual data is
>     buffered  in  temp  files  and  only  blob identifiers passed
>     around.
> 
>     I see the final storage of blob data to happen in  the  toast
>     table,   sliced,   diced   and   handy   (most  probably  not
>     compressed). But I don't see an easy way to abuse  the  bytea
>     datatype for real blob support.
> 

All this may be needed for BLOBs that are *huge*, but there are at least 
two classes of BLOB data that are not typically huge in practice. One is 
encrypted or hashed data, the other is image data for web pages. Either 
of these work very well with the current bytea support.

Joe



Thu, 17 Jan 2002 22:36:09 -0800
Joe Conway <jconway@cox.net>
Re: tuptoaster.c must *not* use SnapshotAny
Joe Conway wrote:
> 
> Hiroshi Inoue wrote:
> 
> > Though I've often seen the reference to bytea BLOB
> > I remember no clear negation. Don't we have to negate
> > it clearly from the first ?
> >
> > regards,
> > Hiroshi Inoue
> 
> With the changes in 7.2, bytea is actually very close to matching the
> SQL99 definition of binary strings, which it also refers to as BLOB.
> BLOBS should include support for (section 4.3):
> 
> <comparison predicate> - yes
> <blob concatenation> - yes
> <blob substring function> - yes
> <blob overlay function> - no
> <trim function> - yes
> <length expression> - yes
> <position expression> - yes
> <like predicate> - yes

> Other than the overlay function, 

It seems good as long as they are not large(huge ?).
It seems a significant flaw for bytea to have no
possibility of partial update.

> the primary thing missing is the use of
>  hex as the I/O representation.

I don't love the current I/O representation of bytea.
It seems worth adding a new data type only to change the 
binary I/O representation.

regards,
Hiroshi Inoue

Fri, 18 Jan 2002 16:48:10 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: tuptoaster.c must *not* use SnapshotAny