Thread: New manual chapters
Hey Peter, I notice that here: http://candle.pha.pa.us/main/writings/pgsql/sgml/ddl-alter.html You mention that you cannot remove a column. This is no longer true as of last friday. And here: http://candle.pha.pa.us/main/writings/pgsql/sgml/ddl-depend.html You say: "All drop commands in PostgreSQL support specifying CASCADE. Of course" Which isn't technically true for the ALTER TABLE/DROP NOT NULL statement.. Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> You say:
> "All drop commands in PostgreSQL support specifying CASCADE. Of course"
> Which isn't technically true for the ALTER TABLE/DROP NOT NULL statement..
Come to think of it, that's probably a bug: you should not be able to
DROP NOT NULL on a column that's part of a PRIMARY KEY. Unless you
cascade to remove the primary key, that is.
regards, tom lane
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes: > > You say: > > "All drop commands in PostgreSQL support specifying CASCADE. Of course" > > Which isn't technically true for the ALTER TABLE/DROP NOT NULL > statement.. > > Come to think of it, that's probably a bug: you should not be able to > DROP NOT NULL on a column that's part of a PRIMARY KEY. Unless you > cascade to remove the primary key, that is. I did ask you about this before, Tom :) The DROP NOT NULL code I submitted will not allow you to drop a not null on a column that participates in a primary key. I was very careful about that. So basically, it's a restrict-only implementation. Although it would be fairly easy I guess to make it support cascade and restrict keywords...? Perhaps not thru the dependency mechanism, but it can be done explicitly. Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Come to think of it, that's probably a bug: you should not be able to
>> DROP NOT NULL on a column that's part of a PRIMARY KEY. Unless you
>> cascade to remove the primary key, that is.
> I did ask you about this before, Tom :)
> The DROP NOT NULL code I submitted will not allow you to drop a not null on
> a column that participates in a primary key. I was very careful about that.
Duh, so you were.
> So basically, it's a restrict-only implementation. Although it would be
> fairly easy I guess to make it support cascade and restrict keywords...?
> Perhaps not thru the dependency mechanism, but it can be done explicitly.
Yeah, I doubt it's worth trying to force NOT NULL into the dependency
mechanism for this. Do you feel like trying to do it "by hand"? It
doesn't seem like a very important issue to me.
regards, tom lane
> > So basically, it's a restrict-only implementation. Although it would be > > fairly easy I guess to make it support cascade and restrict keywords...? > > Perhaps not thru the dependency mechanism, but it can be done > explicitly. > > Yeah, I doubt it's worth trying to force NOT NULL into the dependency > mechanism for this. Do you feel like trying to do it "by hand"? It > doesn't seem like a very important issue to me. Well it'd be nice from a consistency point of view. And it wouldn't be too hard. I don't think it's essential for 7.3, as it would be perfectly backwards compatible. I'll add it to my TODO list, right after changing column type :) Anyone have any ideas on how we could implement changing column type. We'd have to move all dependencies and any object that refers to the column by its attnum, etc... I guess we could steal a lot of the renameatt() code... Chris
On Tue, 2002-08-06 at 09:58, Christopher Kings-Lynne wrote: > > Anyone have any ideas on how we could implement changing column type. We'd > have to move all dependencies and any object that refers to the column by > its attnum, etc... I guess we could steal a lot of the renameatt() code... As discussed some time ago, introducing attlognum would help here a lot if you want the changed column not to hop to the end of column list for "SELECT * " . Or are attlognum changes done already ? ----------- Hannu
> As discussed some time ago, introducing attlognum would help here a lot > if you want the changed column not to hop to the end of column list for > "SELECT * " . Or are attlognum changes done already ? Arg. That's something I didn't think of. No, attlognums aren't done. I think having drop column is more important than the above concern tho. I'll add it to my TODO. Chris
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Arg. That's something I didn't think of. No, attlognums aren't done. I
> think having drop column is more important than the above concern tho. I'll
> add it to my TODO.
But we *have* drop column.
If we don't do attlognum for 7.3, there's little point in doing it at
all. By the time 7.4 comes out, clients that formerly expected a
consecutive series of attnums will have found some way to cope.
I'm not sure that I feel any strong sense of urgency about this ---
7.3 will break clients that examine the system catalogs in many ways,
and this doesn't seem like the nastiest of 'em.
I just wanted to point out that "we'll do it later" isn't a profitable
attitude towards attlognum. Either it's done by the end of August,
or it never gets done at all.
regards, tom lane
> But we *have* drop column. I know that....I think I must have been on crazy pills when I mentioned it in regards to DROP COLUMN... > If we don't do attlognum for 7.3, there's little point in doing it at > all. By the time 7.4 comes out, clients that formerly expected a > consecutive series of attnums will have found some way to cope. I think that what Hannu is saying is that if we implemented changing column type with a drop and an add, all queries that used to go 'select * from' would return the modified column at the end of the list of columns instead of in its original position. > I'm not sure that I feel any strong sense of urgency about this --- > 7.3 will break clients that examine the system catalogs in many ways, > and this doesn't seem like the nastiest of 'em. > > I just wanted to point out that "we'll do it later" isn't a profitable > attitude towards attlognum. Either it's done by the end of August, > or it never gets done at all. Ok - don't know if I'll be able to tho. Chris
On Tue, 2002-08-06 at 11:02, Christopher Kings-Lynne wrote: > > But we *have* drop column. > > I know that....I think I must have been on crazy pills when I mentioned it > in regards to DROP COLUMN... > > > If we don't do attlognum for 7.3, there's little point in doing it at > > all. By the time 7.4 comes out, clients that formerly expected a > > consecutive series of attnums will have found some way to cope. > > I think that what Hannu is saying is that if we implemented changing column > type with a drop and an add, all queries that used to go 'select * from' > would return the modified column at the end of the list of columns instead > of in its original position. There is at least one type of change that can be safely done in-place, namely making variable length types longer (varchar(2) --> varchar(5)). Making them shorter would be doable in-place, but then there are two ways to go - 1. we check that the data fits the new length 2. i/o funtions would enforce length the 2. way has an unexpected behaviour if we first make a field shorter and then longer again. > > I'm not sure that I feel any strong sense of urgency about this --- > > 7.3 will break clients that examine the system catalogs in many ways, > > and this doesn't seem like the nastiest of 'em. It's not about inspecting system catalogs by clients, it is the change in select * after a column type change (say from float to numeric(15,2)) if done by "add column + update + drop column + rename column" if we had lognums, we could do "move column" as a final step. Using the above scenario we already can do "alter table alter column type" manually, so I'd suggest that implementing attlognum (move column) gives more additional functionality than putting in a frontend to do move column manually. The "drop column + rename column + move column" could also be rolled into one "replace column" command ;) ----------- Hannu