Tom Lane wrote:
> Tara Piorkowski <tara@vilaj.com> writes:
>>junk=> create table testing
>>junk-> (testing_id serial not null primary key);
>>NOTICE: CREATE TABLE will create implicit sequence
>>'testing_testing_id_seq' for SERIAL column 'testing.testing_id'
>>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
>>'testing_pkey' for table 'testing'
>>CREATE TABLE
>>junk=> alter table testing
>>junk-> alter column testing_id drop default;
>>ALTER TABLE
>
>
> I'd be inclined to say that the bug here is that you shouldn't be
> allowed to do ALTER COLUMN DROP DEFAULT (nor SET DEFAULT for that
> matter) on a SERIAL column. The default expression is part of the
> implementation of SERIAL, not an independently tweakable entity.
Tom -
I am actually inclined to agree with you. This is not an issue I see
coming up for us again, so making it work as you described above seems
to me does not impact us negatively at all. This was a fluke for us as
our application code was dependent on the manner in which the way
pre-7.3 versions of PostgreSQL truncated sequence names. In running
tests on the software we wrote to migrate our 7.2 databases to become
7.3 databases we just happened to stumble across this issue by accident
(literally).
Regardless, my thinking had been that I was looking at an INT with a
DEFAULT set, in which case I think this would be a bonified bug, thus my
report. However, it makes more sense to consider the column to be of
type SERIAL, despite the labelling. Perhaps the best result would be to
not allow the DROP DEFAULT on a SERIAL column and to label the SERIAL
column as such (as opposed to an INT with DEFAULT).
Thanks, as always, for taking the time to consider my posting and respond.
- Tara
--
Tara Piorkowski
System Administrator, vilaj.com, LLC
<http://www.vilaj.com/>