Thread: Changing column types...
Hi all, Some questions: 1. What is the difference between abstime and timestamp - they seem to display equally... 2. Since int4 and abstime are binary compatible (ie int4::abstime works), is there any serious problem with updating a pg_attribute row for an int4 and changing it to and abstime? My experiments seem to work. 3. Is there any way of checking pg_type to check that two types are binary compatible and can be substiuted in this way? 4. Is there any worth in me submitting a patch that will allow rudimentary column type changing, so long as the types are binary compatible??? Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> 3. Is there any way of checking pg_type to check that two types are binary
> compatible and can be substiuted in this way?
Binary compatibility is not represented in pg_type (which is a shortcoming).
You have to use the IsBinaryCompatible() routine provided by
parse_coerce.h.
> 4. Is there any worth in me submitting a patch that will allow rudimentary
> column type changing, so long as the types are binary compatible???
Hmm. Seems like that case, and the various ones involving adjustment of
char/varchar length by hacking atttypmod, would be useful to support via
ALTER COLUMN even if we don't have a full implementation. Essentially
this would be taking the existing folklore about safe ways to hack
pg_attribute and reducing them to code --- why not do it?
regards, tom lane
> 1. What is the difference between abstime and timestamp - they seem to
> display equally...
abstime is four bytes with a range of +/- 68 years. timestamp is eight
bytes with a range from 4212BC to way into the future.
> 2. Since int4 and abstime are binary compatible (ie int4::abstime works), is
> there any serious problem with updating a pg_attribute row for an int4 and
> changing it to and abstime? My experiments seem to work.
A few integer values are reserved values in abstime, to allow
implementation of infinity, -infinity, etc.
- Thomas
> > 2. Since int4 and abstime are binary compatible (ie > int4::abstime works), is > > there any serious problem with updating a pg_attribute row for > an int4 and > > changing it to and abstime? My experiments seem to work. > > A few integer values are reserved values in abstime, to allow > implementation of infinity, -infinity, etc. Does this mean that hacking the type of an int4 column to become abstime is a bad idea? Yes in theory - no in practice? Chris
> > 4. Is there any worth in me submitting a patch that will allow > rudimentary > > column type changing, so long as the types are binary compatible??? > > Hmm. Seems like that case, and the various ones involving adjustment of > char/varchar length by hacking atttypmod, would be useful to support via > ALTER COLUMN even if we don't have a full implementation. Essentially > this would be taking the existing folklore about safe ways to hack > pg_attribute and reducing them to code --- why not do it? Can you only reduce the length of a varchar (say) or can you actually increase them as well? Chris
> > A few integer values are reserved values in abstime, to allow
> > implementation of infinity, -infinity, etc.
> Does this mean that hacking the type of an int4 column to become abstime is
> a bad idea?
> Yes in theory - no in practice?
Hmm. I assume that this is in the context of an "officially supported"
conversion strategy? I'm afraid I am not recalling the details of these
threads; my brain does not hold as much as it used to ;)
Anyway, if we are thinking of allowing some types to be converted in
place without actually modifying the contents of tuples, then for this
case the risks are relatively small afaicr. The reserved values are at
the high and low ends of the integer range, so there are some large (in
the absolute sense) integer values which would take on some unexpected
interpretations for an abstime value.
That said, I'm not sure why we would want to bother with hacking things
in this way (but if I recalled the details of the threads maybe I
would?).
istm that the general strategy for changing column types would require
marking a column as dead and adding a new column to replace it, or
writing an atomic copy / modify / replace operation for tables which
modifies tuples as it proceeds, or ?? Just because we may allow a hack
for text types because they happen to have a similar/identical storage
structure doesn't necessarily mean that it is a good design for the
general case. But you've probably already covered that territory...
- Thomas
On Fri, 5 Apr 2002, Christopher Kings-Lynne wrote: > > > 2. Since int4 and abstime are binary compatible (ie > > int4::abstime works), is > > > there any serious problem with updating a pg_attribute row for > > an int4 and > > > changing it to and abstime? My experiments seem to work. > > > > A few integer values are reserved values in abstime, to allow > > implementation of infinity, -infinity, etc. > > Does this mean that hacking the type of an int4 column to become abstime is > a bad idea? The only problem with this would be if the int4 column contained the reserved values: #define INVALID_ABSTIME ((AbsoluteTime) 0x7FFFFFFE) #define NOEND_ABSTIME ((AbsoluteTime) 0x7FFFFFFC) #define NOSTART_ABSTIME ((AbsoluteTime) INT_MIN) > > Yes in theory - no in practice? Kind of the other way around, in my opinion: No in theory, yes in practice. Gavin
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Hmm. Seems like that case, and the various ones involving adjustment of
>> char/varchar length by hacking atttypmod, would be useful to support via
>> ALTER COLUMN even if we don't have a full implementation. Essentially
>> this would be taking the existing folklore about safe ways to hack
>> pg_attribute and reducing them to code --- why not do it?
> Can you only reduce the length of a varchar (say) or can you actually
> increase them as well?
You can go either way. If you're reducing then in theory you should
scan the column and make sure that no current values exceed the new
limit.
For char() as opposed to varchar(), you actually need to update the
column to establish the correctly-padded new values.
regards, tom lane
Thomas Lockhart <thomas@fourpalms.org> writes:
> istm that the general strategy for changing column types would require
> marking a column as dead and adding a new column to replace it, or
> writing an atomic copy / modify / replace operation for tables which
> modifies tuples as it proceeds, or ?? Just because we may allow a hack
> for text types because they happen to have a similar/identical storage
> structure doesn't necessarily mean that it is a good design for the
> general case.
Sure. This is not intended to cover the general case; if we hold Chris
to that standard then the task will drop right back to the TODO list
where it's been for years. My thought was that we've frequently
answered people on the mailing lists "well, officially that's not
supported, but unofficially, for the case you need you can hack the
catalogs like this: ...". Why not make that folklore functionality
available in a slightly cleaner package? It won't preclude doing a
full-up ALTER COLUMN implementation later.
regards, tom lane