Re: [BUGS] Bug #513: union all changes char(3) column definition
От | Tom Lane |
---|---|
Тема | Re: [BUGS] Bug #513: union all changes char(3) column definition |
Дата | |
Msg-id | 1596.1005417559@sss.pgh.pa.us обсуждение исходный текст |
Ответы |
Re: [BUGS] Bug #513: union all changes char(3) column definition
(Peter Eisentraut <peter_e@gmx.net>)
|
Список | pgsql-hackers |
Rae Stiening (stiening@cannon.astro.umass.edu) writes: > This script demonstrates the incorrect restoration of a > table created by a union under postgresql version 7.1.3. What's really going on here is that 1. The CREATE TABLE AS command creates a column with type bpchar and typmod -1 (ie, no specific length enforced). 2. pg_dump dumps this column with the type identified as "character". 3. On reload, "character" is interpreted as "character(1)". While each of these behaviors is justifiable to some degree when considered by itself, their interaction is not good. It is actually not possible for pg_dump to dump this table correctly, because there is no CREATE TABLE command it can give to reproduce the type/typmod combination. I thought a little bit about trying to disallow the creation of such tables, but I don't believe that can work in the general case. CREATE TABLE AS cannot be expected to be able to extract a suitable typmod from complex expressions. We could think about replacing "bpchar/-1" with "text", but that only fixes the problem for bpchar; we have the exact same issue with numeric, and there is no comparable workaround for numeric. So I think what we need to do is rejigger the type display and entry rules so that there is a recognized representation for "bpchar with no typmod", "numeric with no typmod", etc, and the parser will not bogusly insert default length limits when it sees this representation. For char I propose that this representation be bpchar ie the underlying type name. This is a bit ugly, but since the notion of char(n) with no particular limit is definitely non-SQL92 anyway, using a non-SQL name seems appropriate. For varchar, it already works to write any of varchar char varying character varying This does not conflict with SQL92 since the standard doesn't allow the length spec to be omitted in these types, and so there's not an expected default of 1 as there is for char. For numeric, we could say that the representation for typmod -1 is "numeric" (double quotes required) ... but I really wonder why we have the convention that numeric defaults to numeric(30,6) in the first place. Why shouldn't the default behavior be to use typmod -1 (no limit)? The (30,6) convention cannot be justified on the basis of the SQL spec; it says the default precision is implementation-defined and the default scale is zero. "No limit" looks like a good enough implementation-defined precision value to me, and as for the scale, defaulting to no scale adjustment is less likely to make anyone unhappy than defaulting to scale zero. So I propose that we remove all notion of a default precision and scale for numeric, and say that numeric written without a precision/scale spec means numeric with typmod -1. For bit, the SQL spec requires us to interpret unadorned bit as meaning bit(1), so there seems little choice but to use "bit" (quotes required) for the typmod -1 case. For varbit, "bit varying" already works and need not be messed with; same rationale as for varchar. As far as implementation goes, on the output side all that's needed is some simple changes in format_type to emit the desired representation. In the parser, we need to remove transformColumnType's diddling of typmod and instead insert the correct default typmod in gram.y. We can't do it later than gram.y since the distinction between "bit" and bit, etc, is not visible later. Comments? regards, tom lane
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: Bug?? -- Alter table drop constraint doesn't seem to work on a primary key constraint in 7.2beta2