BUG #8408: Not exactly correct error messages for failed check in presence of dropped columns.
The following bug has been logged on the website:
Bug reference: 8408
Logged by: Maxim Boguk
Email address: maxim.boguk@gmail.com
PostgreSQL version: 9.2.4
Operating system: Any
Description:
There are simple test case:
create table test_columns (id serial primary key, value int
check(value<10));
NOTICE: CREATE TABLE will create implicit sequence "test_columns_id_seq"
for serial column "test_columns.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_columns_pkey" for table "test_columns"
CREATE TABLE
insert into test_columns(value) values (100);
ERROR: new row for relation "test_columns" violates check constraint
"test_columns_value_check"
DETAIL: Failing row contains (1, 100).
--it's ok...
--now let drop value column and add new one
alter table test_columns drop column value;
ALTER TABLE
alter table test_columns add column "value" int check (value < 10);
ALTER TABLE
insert into test_columns(value) values (100);
ERROR: new row for relation "test_columns" violates check constraint
"test_columns_value_check"
DETAIL: Failing row contains (2, null, 100).
-- now (2, null, 100) sound weird...
And yes I completely understand where this NULL came from and why this
happened.
However, I think error message should filter dropped column and strange null
values (because if a table has a lot dropped column this error message
quickly become completely unreadable).
Regards,
Maksym
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Fri, Aug 30, 2013 at 2:13 PM, <maxim.boguk@gmail.com> wrote:
However, I think error message should filter dropped column and strange null
values (because if a table has a lot dropped column this error message
quickly become completely unreadable).
Yes, this is not really user-friendly. This comes from
ExecBuildSlotValueDescription@execMain.c when generating a string
representation of a tuple. Attached is a patch to make this function
aware of dropped columns when generating the string (usable down to
9.2). However, after some debugging I am seeing that the dropped
column is not seen inside TupleDesc (only the column name is correct)
when calling this function in executor.
Breakpoint 1, ExecBuildSlotValueDescription (slot=0x7fc26412b8e0,
maxfieldlen=64) at execMain.c:1636
1636 TupleDesc tupdesc = slot->tts_tupleDescriptor;
(gdb) p *tupdesc->attrs[0]
$1 = {
attrelid = 0,
attname = {
data = "id", '\0' <repeats 61 times>
},
atttypid = 23,
attstattarget = -1,
attlen = 4,
attnum = 1,
attndims = 0,
attcacheoff = 0,
atttypmod = -1,
attbyval = 1 '\001',
attstorage = 112 'p',
attalign = 105 'i',
attnotnull = 0 '\0',
atthasdef = 0 '\0',
attisdropped = 0 '\0',
attislocal = 1 '\001',
attinhcount = 0,
attcollation = 0
}
(gdb) p *tupdesc->attrs[1]
$2 = {
attrelid = 0,
attname = {
data = "........pg.dropped.2........", '\0' <repeats 35 times>
},
atttypid = 23,
attstattarget = -1,
attlen = 4,
attnum = 2,
attndims = 0,
attcacheoff = -1,
atttypmod = -1,
attbyval = 1 '\001',
attstorage = 112 'p',
attalign = 105 'i',
attnotnull = 0 '\0',
atthasdef = 0 '\0',
attisdropped = 0 '\0',
attislocal = 1 '\001',
attinhcount = 0,
attcollation = 0
}
attisdropped is not set to true, only attname is updated to its new value.
When removing an attribute in RemoveAttributeById, it is written in
comment that updating a pg_attribute row triggers a relcache flush for
target relation, but obviously it is not happening. I am missing
something perhaps?
--
Michael
Attachments:
20130901_dropped_column_gen.patchapplication/octet-stream; name=20130901_dropped_column_gen.patchDownload+9-1
Michael Paquier <michael.paquier@gmail.com> writes:
On Fri, Aug 30, 2013 at 2:13 PM, <maxim.boguk@gmail.com> wrote:
However, I think error message should filter dropped column and strange null
values (because if a table has a lot dropped column this error message
quickly become completely unreadable).
Yes, this is not really user-friendly. This comes from
ExecBuildSlotValueDescription@execMain.c when generating a string
representation of a tuple. Attached is a patch to make this function
aware of dropped columns when generating the string (usable down to
9.2). However, after some debugging I am seeing that the dropped
column is not seen inside TupleDesc (only the column name is correct)
when calling this function in executor.
I got around to looking at this finally, and found out that the reason
Michael's patch doesn't work is that the slot's tuple descriptor is one
that's been made by ExecAssignResultTypeFromTL from the result targetlist
the planner generates, so it has no dropped-column markings. (The
targetlist includes a simple null constant at any dropped column's
position; but TargetEntry has no way to mark itself as a dropped column.)
I think it was coded that way to minimize ExecBuildSlotValueDescription's
API complexity, but really what we ought to do is pass the actual target
relation's tuple descriptor, which will have the necessary markings.
The attached revision of Michael's patch fixes it.
regards, tom lane