Re: [PATCHES] ALTER TABLE modifications
Rod, I tried the current patch on a RC2 release, and I noticed one undesirable side affect. Modifying a column moves it to the end. In high availability situations this would not be desirable, I would imagine it would break lots of code. Dave On Thu, 2003-11-13 at 11:35, Hannu Krosing wrote: > Rod Taylor kirjutas N, 13.11.2003 kell 16:59: > > > > > Can you please suggest a better term to use in place of TRANSFORM? > > Perhaps UPDATE WITH? > > or perhaps USING, based loosely on our use of USING in CREATE INDEX ? > > -- > Hannu > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] ALTER TABLE modifications
On Fri, Nov 14, 2003 at 08:59:05AM -0500, Dave Cramer wrote: > I tried the current patch on a RC2 release, and I noticed one > undesirable side affect. > > Modifying a column moves it to the end. In high availability situations > this would not be desirable, I would imagine it would break lots of > code. This is expected. Doing otherwise would incur into a much bigger performance hit. Anyway, IMHO no code should use SELECT * in any case, which is the only scenario where one would expect physical column order to matter, isn't it? -- Alvaro Herrera () "La primera ley de las demostraciones en vivo es: no trate de usar el sistema. Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] ALTER TABLE modifications
Alvaro Herrera kirjutas R, 14.11.2003 kell 16:17: > On Fri, Nov 14, 2003 at 08:59:05AM -0500, Dave Cramer wrote: > > > I tried the current patch on a RC2 release, and I noticed one > > undesirable side affect. > > > > Modifying a column moves it to the end. In high availability situations > > this would not be desirable, I would imagine it would break lots of > > code. > > This is expected. Doing otherwise would incur into a much bigger > performance hit. Not neccessarily, but it would make the patch much bigger ;) IIRC there was discussion about splitting colum numbers into physical and logical numbers at the time when DROP COLUMN was done. > Anyway, IMHO no code should use SELECT * in any case, which is the only > scenario where one would expect physical column order to matter, isn't > it? and this could also break when just changing the column type. Hannu ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] ALTER TABLE modifications
On Fri, 2003-11-14 at 08:59, Dave Cramer wrote: > Rod, > > I tried the current patch on a RC2 release, and I noticed one > undesirable side affect. > > Modifying a column moves it to the end. In high availability situations > this would not be desirable, I would imagine it would break lots of > code. Yes, I've done that to myself a few times. The method is rename old column, add new column, move data across, move or reform dependencies, drop old column. Adding the new column puts it to the end. In order to avoid the repositioning we would need some kind of a position abstraction from the physical storage to what the user sees. It's on the TODO list, but not a part of this patch. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] ALTER TABLE modifications
Rod Taylor writes: > The method is rename old column, add new column, move data across, move > or reform dependencies, drop old column. I can do this by hand. If we have an explicit command to do it, then it needs to preserve the table schema. Else, this feature would be mostly useless and a certain source of complaints. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] ALTER TABLE modifications
On Fri, 2003-11-14 at 09:57, Peter Eisentraut wrote: > Rod Taylor writes: > > > The method is rename old column, add new column, move data across, move > > or reform dependencies, drop old column. > > I can do this by hand. If we have an explicit command to do it, then it > needs to preserve the table schema. Else, this feature would be mostly > useless and a certain source of complaints. The method was agreed to on -hackers prior to any code having been written. Unless I'm mistaken, the method was suggested to me by other on -hackers. Can you give me an alternative on how to approach this problem without relying on another TODO item labelled ALTER TABLE .. POSITION? The point of the command isn't to accomplish anything magical, simply to make it easier. begin; Rename old column Add new column Copy data Recreate default Recreate NOT NULL constraint Recreate index 1 Recreate index 2 (multi-key) Recreate check constraint 1 Recreate check constraint 2 Recreate check constraint 3 Recreate foreign key 1 remove old column commit; vacuum full table (to get rid of 2x problem since the user was unable to do a fileswap) The above is by hand, the below is with this command: begin; Alter table .. type .. commit; If it becomes possible to change the order without having to goto an exceptional amount of work (that POSITION item) then it would only be obvious to attempt to preserve the position. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] ALTER TABLE modifications
OK, Here is another approach, that would retain column order. It will require that the table be locked while this proceeds, but I think this is a good idea anyway. lock table create newtable as select c1, c2, c3::newtype modify pg_class to point to the new filename modify existing pg_attribute for the column in question recreate indexes that exist on the column unlock table Dave On Fri, 2003-11-14 at 09:57, Peter Eisentraut wrote: > Rod Taylor writes: > > > The method is rename old column, add new column, move data across, move > > or reform dependencies, drop old column. > > I can do this by hand. If we have an explicit command to do it, then it > needs to preserve the table schema. Else, this feature would be mostly > useless and a certain source of complaints. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] ALTER TABLE modifications
Rod Taylor <[EMAIL PROTECTED]> writes: > On Fri, 2003-11-14 at 09:57, Peter Eisentraut wrote: >> I can do this by hand. If we have an explicit command to do it, then it >> needs to preserve the table schema. Else, this feature would be mostly >> useless and a certain source of complaints. > The method was agreed to on -hackers prior to any code having been > written. I believe the consensus was that automating what you could do by hand is still a step forward. It clearly would be better if we could relabel the logical column position after finishing the whole process, but I agree with Rod that that is an independent patch. Combining them into one mega-patch doesn't sound like good engineering. I guess the real question here is whether we would want to revert this capability if a patch to adjust logical column orderings doesn't appear before 7.5. My vote would be "no", but apparently Peter's is "yes". Any other opinions? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] ALTER TABLE modifications
> lock table > create newtable as select c1, c2, c3::newtype > modify pg_class to point to the new filename > modify existing pg_attribute for the column in question > recreate indexes that exist on the column > unlock table I actually tried this to start with an ran into several dead-ends in trying to rebuild constraints, defaults, etc. In order to do this I think you would need to create a second pg_class entry and do a full table swap. By far, the easiest method to preserve order without writing a ton of additional code is to do something that is on the TODO list already, separate the visible position from physical position. Doing the above has lots of added benefits, where spending a ton of time on TYPE change has very few benefits. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] ALTER TABLE modifications
Tom Lane writes: > I believe the consensus was that automating what you could do by hand > is still a step forward. I don't recall that, but if so, I would like to revisit that consensus. AFAICT, this patch does not buy us anything at all. It's just a different spelling of existing functionality. We have never done that before. It just makes the system harder to maintain and use. All commands should be reasonably independent, or at least add some functionality of their own. > It clearly would be better if we could relabel the logical column > position after finishing the whole process, but I agree with Rod that > that is an independent patch. Combining them into one mega-patch > doesn't sound like good engineering. Good engineering would be if the logical column number patch comes first. We cannot possibly leave this patch as is. People expect in-place column changes. Things will break left and right, users will complain all over the place if we offer a way to change a column, but yeah, by the way it changes the structure of the table as well. We've had these kinds of good idea/right direction/better than nothing approaches in areas like DROP COLUMN and CLUSTER already, and they were no good. Except in this case, "better than nothing" doesn't even apply, because there is already something. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] ALTER TABLE modifications
Peter Eisentraut kirjutas R, 14.11.2003 kell 18:51: > Tom Lane writes: > > > I believe the consensus was that automating what you could do by hand > > is still a step forward. > > I don't recall that, but if so, I would like to revisit that consensus. > > AFAICT, this patch does not buy us anything at all. It's just a different > spelling of existing functionality. We have never done that before. what about DROP COLUMN - this is also just a different spelling for SELECT INTO, migrate all constraints, DROP OLD TABLE, RENAME. > It just makes the system harder to maintain and use. All commands should be > reasonably independent, or at least add some functionality of their own. > > > It clearly would be better if we could relabel the logical column > > position after finishing the whole process, but I agree with Rod that > > that is an independent patch. Combining them into one mega-patch > > doesn't sound like good engineering. > > Good engineering would be if the logical column number patch comes first. Agreed, it would be nice. > We cannot possibly leave this patch as is. People expect in-place column > changes. Does SQL spec even require that SELECT * always returns columns in the same order ? I don't think that relational model assigns any 'order' to columns. BTW, SELECT * is just a different spelling of existing functionality ;) > Things will break left and right, users will complain all over > the place if we offer a way to change a column, but yeah, by the way it > changes the structure of the table as well. ALTER TABLE ADD/DROP COLUMN would also break SELECT *. as would type change in many cases (query expects int but gets string) > We've had these kinds of good > idea/right direction/better than nothing approaches in areas like DROP > COLUMN and CLUSTER already, and they were no good. Except in this case, > "better than nothing" doesn't even apply, because there is already > something. We have always had DUMP -> sed -> LOAD too. This patch is *MUCH* better than nothing for performance. Instead of being N times SEQSCAN (+ intervening VACUUM FULLs to reclaim space), it can do it all in one pass. If that pass takes 1.5 hours, we have a very big win here. - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] ALTER TABLE modifications
Hannu Krosing <[EMAIL PROTECTED]> writes: > Peter Eisentraut kirjutas R, 14.11.2003 kell 18:51: >> I don't recall that, but if so, I would like to revisit that consensus. > [ Hannu disagrees ] Please take this thread to pgsql-hackers; if the issue is going to be contentious then we should try to get a wider spectrum of opinion than just people who follow -patches. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PATCHES] update for brazilian portuguese translation
Hi, I have updated the translations of psql and pg_dump. postgres and jdbc are new one. I made 2 files: one for CVS HEAD and other one for REL7_4_STABLE. http://www.ufgnet.ufg.br/euler/patch_pt_BR-cvs.tgz http://www.ufgnet.ufg.br/euler/patch_pt_BR-rel7_4_stable.tgz Please apply. Regards, -- Euler Taveira de Oliveira euler (at) ufgnet.ufg.br Desenvolvedor Web e Administrador de Sistemas UFGNet - Universidade Federal de Goiás ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] New List
Gaetano Mendola <[EMAIL PROTECTED]> writes: > 10E6 INSERT => real 0m5.161s > user 0m4.010s > sys0m1.150s What operation is this benchmarking? Only linked-list appends, or something else? -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] update for brazilian portuguese translation
Euler Taveira de Oliveira writes: > I have updated the translations of psql and pg_dump. postgres and jdbc are new one. > I made 2 files: one for CVS HEAD and other one for REL7_4_STABLE. > http://www.ufgnet.ufg.br/euler/patch_pt_BR-cvs.tgz > http://www.ufgnet.ufg.br/euler/patch_pt_BR-rel7_4_stable.tgz Folks, is this still OK to put into 7.4? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PATCHES] ALTER TABLE modifications
This is expected. Doing otherwise would incur into a much bigger performance hit. Anyway, IMHO no code should use SELECT * in any case, which is the only scenario where one would expect physical column order to matter, isn't it? Well, we can always bring back the old idea of a attlognum which is the logical order of the columns. Then we use that to expand * Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PATCHES] ALTER TABLE modifications
I guess the real question here is whether we would want to revert this capability if a patch to adjust logical column orderings doesn't appear before 7.5. My vote would be "no", but apparently Peter's is "yes". Any other opinions? The fact that it deals with the nightmare of dropping and recreating indexes and fk's makes it worth it to me. How about functions and views and rules that depend on it though? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] heads up -- subtle change of behavior of new initdb
darnit!
patch attached.
(Thinks - do we need to worry about suid sgid and sticky bits on data dir?)
andrew
Tom Lane wrote:
Joe Conway <[EMAIL PROTECTED]> writes:
I just noticed tonight that the new initdb introduced a subtle change of
behavior. I use a shell script to automate the process of
- rm old data directory
- mkdir new data directory
- initdb
- load from pgdumpall
Now, that second step is not needed, but as of today it produces an
installation that won't start due to improper permissions on data
That's a bug --- evidently the "fix permissions" path of control is
wrong; can you take a look?
? .deps
? initdb
Index: initdb.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/initdb/initdb.c,v
retrieving revision 1.7
diff -c -w -r1.7 initdb.c
*** initdb.c13 Nov 2003 23:46:31 - 1.7
--- initdb.c14 Nov 2003 06:47:50 -
***
*** 2345,2350
--- 2345,2359
made_new_pgdata = true;
}
+ else
+ {
+ printf("fixing permissions on existing directory %s... ",pg_data);
+ fflush(stdout);
+ if (!chmod(pg_data,0700))
+ exit_nicely();
+ else
+ check_ok();
+ }
/* Create required subdirectories */
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] heads up -- subtle change of behavior of new initdb
Andrew Dunstan <[EMAIL PROTECTED]> writes: > darnit! > patch attached. Applied with correction (you got the return-value check backwards) and further work to deal reasonably with error conditions occurring in check_data_dir. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] heads up -- subtle change of behavior of new initdb
Patch applied. Thanks.
---
Andrew Dunstan wrote:
>
> darnit!
>
> patch attached.
>
> (Thinks - do we need to worry about suid sgid and sticky bits on data dir?)
>
> andrew
>
> Tom Lane wrote:
>
> >Joe Conway <[EMAIL PROTECTED]> writes:
> >
> >
> >>I just noticed tonight that the new initdb introduced a subtle change of
> >>behavior. I use a shell script to automate the process of
> >>- rm old data directory
> >>- mkdir new data directory
> >>- initdb
> >>- load from pgdumpall
> >>Now, that second step is not needed, but as of today it produces an
> >>installation that won't start due to improper permissions on data
> >>
> >>
> >
> >That's a bug --- evidently the "fix permissions" path of control is
> >wrong; can you take a look?
> >
> >
> >
> >
> ? .deps
> ? initdb
> Index: initdb.c
> ===
> RCS file: /projects/cvsroot/pgsql-server/src/bin/initdb/initdb.c,v
> retrieving revision 1.7
> diff -c -w -r1.7 initdb.c
> *** initdb.c 13 Nov 2003 23:46:31 - 1.7
> --- initdb.c 14 Nov 2003 06:47:50 -
> ***
> *** 2345,2350
> --- 2345,2359
>
> made_new_pgdata = true;
> }
> + else
> + {
> + printf("fixing permissions on existing directory %s... ",pg_data);
> + fflush(stdout);
> + if (!chmod(pg_data,0700))
> + exit_nicely();
> + else
> + check_ok();
> + }
>
> /* Create required subdirectories */
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [HACKERS] heads up -- subtle change of behavior of new
Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > darnit! > > patch attached. > > Applied with correction (you got the return-value check backwards) > and further work to deal reasonably with error conditions occurring > in check_data_dir. Tom applied it before I could. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] heads up -- subtle change of behavior of
Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: darnit! patch attached. Applied with correction (you got the return-value check backwards) and further work to deal reasonably with error conditions occurring in check_data_dir. darnit again. I'm taking a break - my head is swimming with Java, JavaScript, Perl, HTML and XML/XSL from my real (i.e. paying) work, and context switching is causing massive mental thrashing. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
