Обсуждение: [HACKERS] Tuple size

Hi!

  I have tried:

create table tab (v1 char(4096), v2 char(4096), v3 char(4096));

  Then:

insert into tab values('1');

  OK

insert into tab values('1','2');

  Tuple is too big: size 8256.

  Well, I understand why, but type 'char' is *fixed* length type,
  so even if I don't use some attributes, it *must* be inserted
  with all spaces... At least, I expected this... In this case
  I must get "tuple is too big" even if I will insert "empty"
  record. It is not so - why?

  And one more: when I use 'varchar' without size, I get variable
  length attribute similar to 'text', which is stored with actual
  length, without padding. If I will specify length, it will be
  stored without padding, but will occupy specified number of bytes
  *always*, but, when retrieved, will return actual number of characters.
  Why not store only actual length, even if maximum specified?
  And, it will be listed as attribute with length zero, not 'var',
  but *actually* it is variable length, same as 'text'...

/Al

------------------------------

I can take a guess...

>   Well, I understand why, but type 'char' is *fixed* length type,
>   so even if I don't use some attributes, it *must* be inserted
>   with all spaces... At least, I expected this... In this case
>   I must get "tuple is too big" even if I will insert "empty"
>   record. It is not so - why?

Your first insertion had nulls for the other two positions. Tuples are
stored with a bit vector specifying which columns are null. Null columns
are not stored. So the example fit into one page, no problem. If you had
inserted empty strings in the other two char()s, then you would have run
out of space.

------------------------------

>
> Hi!
>
>   I have tried:
>
> create table tab (v1 char(4096), v2 char(4096), v3 char(4096));
>
>   Then:
>
> insert into tab values('1');
>
>   OK
>
> insert into tab values('1','2');
>
>   Tuple is too big: size 8256.
>
>   Well, I understand why, but type 'char' is *fixed* length type,
>   so even if I don't use some attributes, it *must* be inserted
>   with all spaces... At least, I expected this... In this case
>   I must get "tuple is too big" even if I will insert "empty"
>   record. It is not so - why?

NULL has zero length.

>
>   And one more: when I use 'varchar' without size, I get variable
>   length attribute similar to 'text', which is stored with actual
>   length, without padding. If I will specify length, it will be
>   stored without padding, but will occupy specified number of bytes
>   *always*, but, when retrieved, will return actual number of characters.
>   Why not store only actual length, even if maximum specified?
>   And, it will be listed as attribute with length zero, not 'var',
>   but *actually* it is variable length, same as 'text'...

It is just part of the way varchar works.  Performance is slightly
better with fixed-length strings like this, perhaps.

- --
Bruce Momjian
maillist@candle.pha.pa.us

------------------------------

> > From a friendly Alexander Dememshin:
> >
> > Hi!
> >
> >   I have tried:
> >
> > create table tab (v1 char(4096), v2 char(4096), v3 char(4096));
> >
> >   Then:
> >
> > insert into tab values('1');
> >
> >   OK
> >
> > insert into tab values('1','2');
> >
> >   Tuple is too big: size 8256.
> >
> >   Well, I understand why, but type 'char' is *fixed* length type,
> >   so even if I don't use some attributes, it *must* be inserted
> >   with all spaces... At least, I expected this... In this case
> >   I must get "tuple is too big" even if I will insert "empty"
> >   record. It is not so - why?
>
> Bruce Momjian wrote:
>
> NULL has zero length.

I think Alexander is questioning the fact that postgres let him make a table
with three fields totaling 12k in size when the max tuple size is at about
8000 bytes or so now.  Seems to me that "create table" code should check the
size of all attributes, at least when a size is given.  For "text" and any
other types that don't need a fixed size at create time, this understandably
can't be done, but for other sized types, IMHO, it should.


Darren  darrenk@insightdist.com

------------------------------

>
> > > From a friendly Alexander Dememshin:
> > >
> > > Hi!
> > >
> > >   I have tried:
> > >
> > > create table tab (v1 char(4096), v2 char(4096), v3 char(4096));
> > >
> > >   Then:
> > >
> > > insert into tab values('1');
> > >
> > >   OK
> > >
> > > insert into tab values('1','2');
> > >
> > >   Tuple is too big: size 8256.
> > >
> > >   Well, I understand why, but type 'char' is *fixed* length type,
> > >   so even if I don't use some attributes, it *must* be inserted
> > >   with all spaces... At least, I expected this... In this case
> > >   I must get "tuple is too big" even if I will insert "empty"
> > >   record. It is not so - why?
> >
> > Bruce Momjian wrote:
> >
> > NULL has zero length.
>
> I think Alexander is questioning the fact that postgres let him make a table
> with three fields totaling 12k in size when the max tuple size is at about
> 8000 bytes or so now.  Seems to me that "create table" code should check the
> size of all attributes, at least when a size is given.  For "text" and any
> other types that don't need a fixed size at create time, this understandably
> can't be done, but for other sized types, IMHO, it should.

True, he should be warned, but the fact is that varchar() and char() are
of limited usefulness when text is availble, and we can't issue a
warning for text.

- --
Bruce Momjian
maillist@candle.pha.pa.us

------------------------------

>
> On Fri, Jun 20, 1997 at 01:36:31PM -0400, Bruce Momjian wrote:
>
> > >   I know, this, of course. But varchar without length does same
> > >   thing. So, why we need text and varchar to co-exist?
> >
> > text always stores allocated bytes, text only stores used bytes.
>
>   ???
>
>   Try this:
>
> create table tab (t text, v varchar);
>
>   Then make some inserts with different length, and you will see,
>   both 't' and 'v' stores used bytes *only*...
>
>   So, again my question - why we need two different types with
>   identical behavior? I can use varchar for indexes, but I can use
>   text for this purpose too... Where is the difference?
>
>   And, why length of varchar() is limited to 4096, not, eg. 6144?

Now I am confused.  Does varchar() store only used bytes?  Then the FAQ
is wrong.  It says char() and varchar() always store maximum length.

The answer to your question of why they both exist is that postgres
initially had only text, and char() and varchar() were added for SQL
compatability.


- --
Bruce Momjian
maillist@candle.pha.pa.us

------------------------------

>
> On Fri, Jun 20, 1997 at 02:31:02PM -0400, Bruce Momjian wrote:
>
> > Now I am confused.  Does varchar() store only used bytes?  Then the FAQ
> > is wrong.  It says char() and varchar() always store maximum length.
>
>   varchar() stores maximum length only (and only!) if length is
>   specified. varchar(100) will always store 100 bytes, while
>   varchar will store only entered bytes:
>
> create table tab (v varchar);
>
>   This will work as text. But:
>
> create table tab (v varchar(100));
>
>   will *always* store 100 characters. The only difference from
>   char() - varchar() is not padded with spaces...

I feel better now.

- --
Bruce Momjian
maillist@candle.pha.pa.us

------------------------------