Thread: Unimpletmented features
From the ALTER TABLE documentation on postgresql.org
Synopsis
ALTER TABLE [ ONLY ]table [ * ]
ADD [ COLUMN ] column type
ALTER TABLE [ ONLY ]table [ * ]
ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
ALTER TABLE table [ * ]
RENAME [ COLUMN ] column TO newcolumn
ALTER TABLE table
RENAME TO newtable
ALTER TABLE table
ADD table constraint definition
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
What happened when I tryed it:
DBD::Pg::db do failed: ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented
If I find another unimplemented 'feature' of postgresql 7.0.2 documented
in the manual I swear to god I'm going to go postal.
How about a 'not yet implemented in 7.0' line in the documentation for
this stuff?
John.
On Wed, Aug 16, 2000 at 06:44:27PM +1200, John Morton wrote: > > What happened when I tryed it: > > DBD::Pg::db do failed: ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented > > If I find another unimplemented 'feature' of postgresql 7.0.2 documented > in the manual I swear to god I'm going to go postal. Now John, let's keep it all in perspective: ALTER TABLE / ADD CONSTRAINT not working isn't going to cost you your job or anything, is it? > > How about a 'not yet implemented in 7.0' line in the documentation for > this stuff? > How about reading farther down the document? to quote ( from http://postgresql.org/docs/user/sql-altertable.htm under Notes): In the current implementation, only FOREIGN KEY constraints can be added to a table. To create or remove a unique constraint, create a unique index (see CREATE INDEX). To add check constraints you need to recreate and reload the table, using other parameters to the CREATE TABLE command. reedstrm=# alter table t2 add constraint foo foreign key (x2) references t1(x); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) CREATE reedstrm=# select version(); version --------------------------------------------------------------- PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2 (1 row) reedstrm=# Is that what you experienced? A further problem can occur because of reorganization problems at postgresql.org: There are two sets of documentation! Those for the released version, and those for that track the CVS development version. In response to many cries of "But all the cool new features aren't documented!" that happened around the 6.[03] releases, the core developers got very hardnosed (with each other, as well as everyone else) and insisted that a patch fora new feature was not complete until it patched the docs, as well. This means the development docs often reference features that are not yet released, but _do_ exist. It's not vaporware documentation, just pre-release. Hey, it's a lot better than _no_ documentation. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
If you read the entire man page, it should say further
down that it only works for FOREIGN KEY constraints (and
gives info on what to do for other constraints).
"In the current implementation, only FOREIGN KEY
constraints can be added to a table."
Stephan Szabo
sszabo@bigpanda.com
On Wed, 16 Aug 2000, John Morton wrote:
> >From the ALTER TABLE documentation on postgresql.org
>
> Synopsis
>
> ALTER TABLE [ ONLY ]table [ * ]
> ADD [ COLUMN ] column type
> ALTER TABLE [ ONLY ]table [ * ]
> ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
> ALTER TABLE table [ * ]
> RENAME [ COLUMN ] column TO newcolumn
> ALTER TABLE table
> RENAME TO newtable
> ALTER TABLE table
> ADD table constraint definition
>
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> What happened when I tryed it:
>
> DBD::Pg::db do failed: ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented
>
> If I find another unimplemented 'feature' of postgresql 7.0.2 documented
> in the manual I swear to god I'm going to go postal.
>
> How about a 'not yet implemented in 7.0' line in the documentation for
> this stuff?
>
> John.
>
On Wed, 16 Aug 2000 10:28:24 -0500 you wrote: > On Wed, Aug 16, 2000 at 06:44:27PM +1200, John Morton wrote: > > > > What happened when I tryed it: > > > > DBD::Pg::db do failed: ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented > > > > If I find another unimplemented 'feature' of postgresql 7.0.2 documented > > in the manual I swear to god I'm going to go postal. > > Now John, let's keep it all in perspective: ALTER TABLE / ADD CONSTRAINT > not working isn't going to cost you your job or anything, is it? I've taken my dried frog pills and I'm feeling much better :-) > > > > How about a 'not yet implemented in 7.0' line in the documentation for > > this stuff? > > > > How about reading farther down the document? to quote ( from > http://postgresql.org/docs/user/sql-altertable.htm under Notes): > > In the current implementation, only FOREIGN KEY constraints can > be added to a table. To create or remove a unique constraint, > create a unique index (see CREATE INDEX). To add check constraints > you need to recreate and reload the table, using other parameters > to the CREATE TABLE command. Ah. I was just glancing at the synopsis and the section of ANSI SQL that wasn't implemented at the bottom, so I missed it. > reedstrm=# alter table t2 add constraint foo foreign key (x2) references t1(x); > NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) > for FOREIGN KEY check(s) CREATE > reedstrm=# select version(); > version > --------------------------------------------------------------- > PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2 > (1 row) > > reedstrm=# > > Is that what you experienced? That much works for me, thanks. I was looking at adding uniqueness across fields but I can do that with CREATE INDEX, so it's not a showstopper. If I was trying to add other contraints I'd be having more trouble. I seem to recall that the rename/create/fill/delete kludge required rebuilding indexes and permissions by hand in 6.5.*; is it the same story in 7.0? > A further problem can occur because of reorganization problems > at postgresql.org: There are two sets of documentation! Those for > the released version, and those for that track the CVS development > version. In response to many cries of "But all the cool new features > aren't documented!" that happened around the 6.[03] releases, the core > developers got very hardnosed (with each other, as well as everyone > else) and insisted that a patch fora new feature was not complete until > it patched the docs, as well. This means the development docs often > reference features that are not yet released, but _do_ exist. It's not > vaporware documentation, just pre-release. Hey, it's a lot better than > _no_ documentation. Yeah, I guess I can live with that, though I'd be much happier during development to have the full array of alter commands at my disposal. John
On Thu, 17 Aug 2000, John Morton wrote: > That much works for me, thanks. I was looking at adding uniqueness across > fields but I can do that with CREATE INDEX, so it's not a showstopper. > If I was trying to add other contraints I'd be having more trouble. I seem > to recall that the rename/create/fill/delete kludge required rebuilding > indexes and permissions by hand in 6.5.*; is it the same story in 7.0? Right now yes, although 7.1 should have an ADD CONSTRAINT for check constraints. (It might be in CVS at this point, I'm not sure if it was applied)
On Thu, Aug 17, 2000 at 01:13:20PM +1200, John Morton wrote: > > Now John, let's keep it all in perspective: ALTER TABLE / ADD CONSTRAINT > > not working isn't going to cost you your job or anything, is it? > > I've taken my dried frog pills and I'm feeling much better :-) Yeah, aren't they wonderful? I'm worried about the worldwide depletion of the amphibian populations, though. ;-) > > That much works for me, thanks. I was looking at adding uniqueness across > fields but I can do that with CREATE INDEX, so it's not a showstopper. And that's how the backend handles it, anyway. > If I was trying to add other contraints I'd be having more trouble. I seem > to recall that the rename/create/fill/delete kludge required rebuilding > indexes and permissions by hand in 6.5.*; is it the same story in 7.0? > 'Fraid so. Becareful of any 'serial' fields, as well: the sequences will need to be moved around by hand. > Yeah, I guess I can live with that, though I'd be much happier during > development to have the full array of alter commands at my disposal. > That's part of the problem: the ALTER family are really only useful for development, so they're not targeted as strongly as features that will affect the daily functioning of a production DB. Ross -- Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Ross, I am not so sure that ALTER <object> family is used for development purposes only... Consider data warehousing where typically each day the administrator would : 1 Extract data from source database(s) - usually in ascii file format, as these dbs may be connection unfriendly, not controlled by us etc etc etc 2 Modify relevant tables in the target database and disable all constraints ( primary keys too usually ) 3 Drop all remaining indexes 4 Bulk load the source data. 5 Enable all constraints 6 Create any remaining indexes These tables tend to have a very large number of rows ( >100,000,000 ) and are often corrospondingly large ( >10GB ) and thus it is important to remove indexes and constraints to load the data in a sensible time frame ( a few hours or less). Postgresql is good at the "star" type queries that are common in such warehouse applications, and thus is well suited to move into that area - but the unimplemented ALTER features hinder this regards Mark