Thread: Weird behavior of 'default user'

Hello!

Look at this:

------------------
adb=> create table hh (dd char(2) default user, ff int4);
CREATE
adb=> insert into hh (ff) values (5);
INSERT 572034 1
adb=> select * from hh;
dd  |ff
----+--
leon| 5
(1 row)
------------------

How can I understand that? Column dd is of type char(2), whereas
'leon' is four characters! Even more, look here:

------------------
adb=> insert into hh  values (user, 7);
INSERT 572045 1
adb=> select * from hh;
dd  |ff
----+--
leon| 5
le  | 7
(2 rows)
------------------

This absolutely beyond my mind. This means that user, being
inserted explicitly, is correctly truncated. If it is inserted
by default, it is wider than column! Seems something very strange
is going on here.

--
Leon.

Leon wrote:
>
> Hello!
>
> Look at this:

I, after a short investigation, I found that it is a BUG!!!

Now I logged in as Postgres. Consider:

-----------
adb=> select * from hh;
dd  |ff
----+--
leon| 5
le  | 7
(2 rows)

adb=> insert into hh (ff) values (9);
INSERT 572224 1
adb=> select * from hh;
dd      |        ff
--------+----------
leon    |         5
le      |         7
postgres|1936028263
(3 rows)

adb=>
-----------

It clearly shows that when user is long enough, it destroys nearby
fields.

By the way, when will be released the major bugfix to 6.5?

--
Leon.

> Hello!
>
> Look at this:
>
> ------------------
> adb=> create table hh (dd char(2) default user, ff int4);
> CREATE
> adb=> insert into hh (ff) values (5);
> INSERT 572034 1
> adb=> select * from hh;
> dd  |ff
> ----+--
> leon| 5
> (1 row)
> ------------------
>
> How can I understand that? Column dd is of type char(2), whereas
> 'leon' is four characters! Even more, look here:
>
> ------------------
> adb=> insert into hh  values (user, 7);
> INSERT 572045 1
> adb=> select * from hh;
> dd  |ff
> ----+--
> leon| 5
> le  | 7
> (2 rows)
> ------------------
>
> This absolutely beyond my mind. This means that user, being
> inserted explicitly, is correctly truncated. If it is inserted
> by default, it is wider than column! Seems something very strange
> is going on here.
>

Yes, this is a known problem.  If the default were a fixed string:

    test=> create table hh2 (dd char(2) default 'fred', ff int4);

it works, but 'user' is system variable, and not auto-converted.

Tom Lane pointed this out as a problem before, but it was too close to
6.5 to fix.

Added to TODO:

  * CREATE TABLE test(col char(2) DEFAULT user) fails in length restriction

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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