defalut value

Is this a known problem?

CREATE TABLE test (
plt int2 PRIMARY KEY,
state CHAR(5) NOT NULL DEFAULT 'new',
used boolean NOT NULL DEFAULT 'f',
id int4
);

INSERT INTO test (plt, id) VALUES (1, 1);
INSERT INTO test (plt, id) VALUES (2, 2);
INSERT INTO test (plt, id) VALUES (3, 3);

SELECT * FROM test;

plt|state|used|id
---+-----+----+--
1|new |f | 1
2|new |f | 2
3|new |f | 3
(3 rows)

UPDATE test SET state = 'diff' WHERE plt = 1;
SELECT * FROM test;

plt|state|used| id
---+-----+----+-----
2|new |f | 2
3|new |f | 3
1|diff |t |26144
(3 rows)

???

This is scary, but not unexpected. I have a bug report in my mailbox
that describes a similar problem with default. I am sure it is the same
cause. Somewhere, default is broken, and it is on the Open Items list.
I believe it is an improper default length field or rounding of length.
I looked at it once, but could not find the cause.

Report is below.

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

Hi,

I found a bug in 6.4.2 which seems to be
related to the char(n) type and shows up
if one assigns a zero-length default value.

Here is an example:

test=> create table t1 (
test-> str1 char(2) default '', <---- note this one
test-> str2 text default '',
test-> str3 text default ''
test-> );
CREATE

test=> insert into t1 values ('aa', 'string2', 'string3');
INSERT 91278 1
test=> insert into t1 (str3) values ('string3');
INSERT 91279 1
test=>test=> select * from t1;
Backend message type 0x44 arrived while idle
Backend message type 0x44 arrived while idle
We have lost the connection to the backend, so further processing is
impossible. Terminating.

If the table is created as

create table t1 (
str1 char(2) default ' ',
str2 text default '',
str3 text default ''
);

the crash doesn't happen.

Regards
Erich

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

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

1|new |f | 1

UPDATE test SET state = 'diff' WHERE plt = 1;

1|diff |t |26144

???

This is scary, but not unexpected. I have a bug report in my mailbox
that describes a similar problem with default. I am sure it is the
same cause. Somewhere, default is broken, and it is on the Open Items
list.

But the value looks correct at the first SELECT, so how could it be the
fault of the DEFAULT clause? Seems you are right though, because I can
reproduce the error given the stated table definition --- but not when
there's no defaults.

Interesting data point: my value for the trashed ID field comes out as
543555584 = 0x20660000, versus Tatsuo's 26144 = 0x00006620. My HP box
is big-endian hardware, and I'm guessing that Tatsuo is using something
little-endian. The data looks like it is the 'f' and space that would
be at the end of the "state" field. How did this get over into the "id"
field, especially without corrupting "used" in between?

Even more interesting: if I declare the state field as
state CHAR(5) NOT NULL DEFAULT 'new ',
all else the same, there's no error.

I believe it is an improper default length field or rounding of length.

I think somehow, somewhere, the size of the default value is getting
used instead of the size of the field itself. Weird. Is it specific
to char(n), perhaps? That might help explain how the bug got past
the regression tests.

regards, tom lane

I think somehow, somewhere, the size of the default value is getting
used instead of the size of the field itself. Weird. Is it specific
to char(n), perhaps? That might help explain how the bug got past
the regression tests.

I'm pretty sure it is specific to char(n), and it is due to the 4 byte
difference in length between the string and the storage. When we fix
this one we will also fix the "c char(2) default ''" problem too.

In fact, I could have sworn I already had looked at it. Oh well.

- Tom

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

This is now fixed in 6.5.

Is this a known problem?

CREATE TABLE test (
plt int2 PRIMARY KEY,
state CHAR(5) NOT NULL DEFAULT 'new',
used boolean NOT NULL DEFAULT 'f',
id int4
);

INSERT INTO test (plt, id) VALUES (1, 1);
INSERT INTO test (plt, id) VALUES (2, 2);
INSERT INTO test (plt, id) VALUES (3, 3);

SELECT * FROM test;

plt|state|used|id
---+-----+----+--
1|new |f | 1
2|new |f | 2
3|new |f | 3
(3 rows)

UPDATE test SET state = 'diff' WHERE plt = 1;
SELECT * FROM test;

plt|state|used| id
---+-----+----+-----
2|new |f | 2
3|new |f | 3
1|diff |t |26144
(3 rows)

???
--
Tatsuo Ishii

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