Обсуждение: Problems building pg 8.1.1
Hi, I'm unable to compile PostgreSQL 8.1.1 on my Suse 10.0 with thread safety enabled. ./configure --prefix=/opt/pgsql8.1.1 --enable-thread-safety --with-python ... ... ... checking thread safety of required library functions... no configure: error: *** Thread test program failed. Your platform is not thread-safe. *** Check the file 'config.log'for the exact reason. *** *** You can use the configure option --enable-thread-safety-force *** to force threads to be enabled. However, you must then run *** the program in src/tools/thread and add locking function calls *** to your applications to guarantee thread safety. And when I look in config.log, I have : configure:22455: checking thread safety of required library functions configure:22483: gcc -o conftest -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DIN_CONFIGURE -D_GNU_SOURCE conftest.c -lz -lreadline -lcrypt -lresolv -lnsl -ldl -lm -lpthread >&5 conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file or directory configure:22486: $? = 1 configure: program exited with status 1 Is it a problem with my system or is it a configur bug ? thanks
mordicus <mordicus@free.fr> writes: > ./configure --prefix=/opt/pgsql8.1.1 --enable-thread-safety --with-python > ... > checking thread safety of required library functions... no > And when I look in config.log, I have : > conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file or > directory Hmm, it works fine for me. Is that file actually present in your source tree? regards, tom lane
Is there a way to change the order of columns in a table in Postgres after it has been created? Back when I used MS SQL Server, it was trivial to go into Enterprise Manager and move columns up and down. I find this a desirable feature, as I like to keep like columns grouped in my table. Sometimes I add a column after table creation that makes more sense grouped with columns toward the top of the table. John
John McCawley wrote: > Is there a way to change the order of columns in a table in Postgres > after it has been created? Back when I used MS SQL Server, it was > trivial to go into Enterprise Manager and move columns up and down. I > find this a desirable feature, as I like to keep like columns grouped > in my table. Sometimes I add a column after table creation that makes > more sense grouped with columns toward the top of the table. > > John No. You could use a view on the table for presentation purposes. Joshua D. Drake > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/
John McCawley wrote: > Is there a way to change the order of columns in a table in Postgres > after it has been created? ... The best way to do it is when you have the opportunity to do a restore, edit the pg_dump output between the dump and the restore steps. There are other approaches that might not be feasible depending on circumstances, like dropping and recreating the table and reloading data, but you have to deal with foreign key and other dependencies and so it is probably more work than justifiable for something that makes no practical difference. Regards, Berend Tober
am 06.01.2006, um 9:45:53 -0600 mailte John McCawley folgendes: > Is there a way to change the order of columns in a table in Postgres after > it has been created? Back when I used MS SQL Server, it was trivial to go Yes. 'select foo, bar, batz from table', 'select bar, foo, batz from table' or 'select batz, bar, foo from table'. Please, if you create a new thread, then make a new thread and do not response to a other mail. HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
I believe that it makes a lot of practical difference, just like organizing related code into files, classes etc. is important for clarity. This isn't a trivial thing, and the other (sarcastic?) suggestion that I reorder my select misses the point. I think that having a good visual representation of the database is extremely important. So much so that I wrote my own tool to do it because one didn't exist for Postgres at the time. But I also think it's important for this visual representation to be tied to the database such that changes to the DB reflect in the visual representation and vice versa. That's why I was asking my question about column order. It would be bad to allow a user to move a column in the visual representation when it is unable to be modified in the database. I'm sure that it's a difficult feature to implement at the database level, and I'm sure there are sound technical reasons why it hasn't been implemented, but I do believe that it is a desirable feature. Berend Tober wrote: > John McCawley wrote: > >> Is there a way to change the order of columns in a table in Postgres >> after it has been created? ... > > > The best way to do it is when you have the opportunity to do a > restore, edit the pg_dump output between the dump and the restore > steps. There are other approaches that might not be feasible depending > on circumstances, like dropping and recreating the table and reloading > data, but you have to deal with foreign key and other dependencies and > so it is probably more work than justifiable for something that makes > no practical difference. > > Regards, > Berend Tober > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
Le Vendredi 6 Janvier 2006 16:31, Tom Lane a écrit : > > > > conftest.c:140:44: error: ./src/tools/thread/thread_test.c: No such file > > or directory > > Hmm, it works fine for me. Is that file actually present in your source > tree? No and that's my fault, I have dowloaded postgresql-base-8.1.1.tar.bz2 in place of postgresql-8.1.1.tar.bz2 and then, tar xjvf <TAB> without really looking the file or directory name... Thanks for the reply ! Regards.
John McCawley <nospam@hardgeus.com> schrieb: > I believe that it makes a lot of practical difference, just like organizing > related code into files, classes etc. is important for clarity. This isn't > a trivial thing, and the other (sarcastic?) suggestion that I reorder my > select misses the point. Not really sarcastic. You can't expect the order of values in a table, and you can't expect the order of columns in a table. If you do a 'select * from ...', and the table-structure is changed, then you get problems. If you do a 'select col1, col2, col2, ... coln ...'), no problems. A (german) article about this (with mysql): http://php-faq.de/q/q-sql-select.html HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
I would certainly never have any of my normal application code depend on the ordering of columns. My desire for column ordering is strictly for administration purposes, keeping the visual clarity of the datamodel, and ensuring that the visual datamodel is consistent with what is actually in the database. When one initially designs a database, thought goes into the ordering of columns (at least, I hope it does). In a large table it makes sense to group like columns. Using my line of work as an example, if you are storing the first and last names of the insured and claimant in a claim record, one would not consciously define the table as: insured_firstname varchar(32), claimant_lastname varchar(32), insured_lastname varchar(32), claimant_firstname varchra(32) However, in the real world, columns are often added willy-nilly as they are needed, and it is not immediately obvious which, if any, of the columns will be related. Later, solely for visual clarity, it is desirable to have the ability to reorder the columns in the database. I wouldn't depend on this ordering for a "select *" query. But I *would* like the columns to appear in this order whenever any Postgres administration tool asked for the column list of a table, that way it appears the way I like in PgAdminIII, the \d command of psql, etc. etc. John Andreas Kretschmer wrote: > Not really sarcastic. You can't expect the order of values in a table, > and you can't expect the order of columns in a table. > > If you do a 'select * from ...', and the table-structure is changed, > then you get problems. If you do a 'select col1, col2, col2, ... coln > ...'), no problems. > > A (german) article about this (with mysql): > http://php-faq.de/q/q-sql-select.html > > > HTH, Andreas > >
John McCawley <nospam@hardgeus.com> schrieb: > However, in the real world, columns are often added willy-nilly as they are > needed, and it is not immediately obvious which, if any, of the columns > will be related. Later, solely for visual clarity, it is desirable to have > the ability to reorder the columns in the database. I wouldn't depend on I understand you, but i repeat Joshua and Berend: PostgreSQL hasn't a build-in solution for this. And i think, other systems do this: - begin - create a new temp. table with the new order - insert all values from the old table in the new temp. table - drop the old table - rename the temp. table to the old table name - commit You can do the same, no problem. HTH, Andreas, and sorry for my bad english -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
OK, one last question on the subject and I'll shut up. I would assume that all dependent database objects are also dropped when you drop the table, so you'd have to recreate all of your foreign keys (both directions) and triggers etc.? Andreas Kretschmer wrote: >John McCawley <nospam@hardgeus.com> schrieb: > > >>However, in the real world, columns are often added willy-nilly as they are >>needed, and it is not immediately obvious which, if any, of the columns >>will be related. Later, solely for visual clarity, it is desirable to have >>the ability to reorder the columns in the database. I wouldn't depend on >> >> > >I understand you, but i repeat Joshua and Berend: PostgreSQL hasn't a >build-in solution for this. >And i think, other systems do this: > >- begin >- create a new temp. table with the new order >- insert all values from the old table in the new temp. table >- drop the old table >- rename the temp. table to the old table name >- commit > >You can do the same, no problem. > > >HTH, Andreas, and sorry for my bad english > >
Andreas Kretschmer wrote: >John McCawley <nospam@hardgeus.com> schrieb: > > >>However, in the real world, ... >> >> > >...And i think, other systems do this: > >- begin >- create a new temp. table with the new order >- insert all values from the old table in the new temp. table >- drop the old table >- rename the temp. table to the old table name >- commit > >You can do the same, no problem. > > > Except for the details regarding dependent views and foreign key references... (as someone mentioned mentioned earlier!). And you probably wouldn't want to do that on an in-production system, I would think, although the transaction block prehaps provides appropriate protection. Regards, Berend Tober
John McCawley <nospam@hardgeus.com> schrieb: > OK, one last question on the subject and I'll shut up. I would assume that > all dependent database objects are also dropped when you drop the table, so > you'd have to recreate all of your foreign keys (both directions) and > triggers etc.? Um, yes, this may be a problem. I don't know a practicable solution. As i said, attach new columns at the end and formulate useful SELECT's and INSERT's. Or, see also "Joshua D. Drake", create VIEWS. > > Andreas Kretschmer wrote: Btw.: Top Posting with Fullquote Below (german: TOFU) are silly... (-> http://learn.to/quote) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Actually, I'm pretty sure this is on the TODO. It can't really happen until we have the ability to somehow divorce on-disk ordering from what's presented in the catalog. It's not exactly rocket science to make this happen, but it is quite a bit of work... On Fri, Jan 06, 2006 at 10:59:12AM -0600, John McCawley wrote: > I believe that it makes a lot of practical difference, just like > organizing related code into files, classes etc. is important for > clarity. This isn't a trivial thing, and the other (sarcastic?) > suggestion that I reorder my select misses the point. > > I think that having a good visual representation of the database is > extremely important. So much so that I wrote my own tool to do it > because one didn't exist for Postgres at the time. But I also think > it's important for this visual representation to be tied to the database > such that changes to the DB reflect in the visual representation and > vice versa. That's why I was asking my question about column order. It > would be bad to allow a user to move a column in the visual > representation when it is unable to be modified in the database. > > I'm sure that it's a difficult feature to implement at the database > level, and I'm sure there are sound technical reasons why it hasn't been > implemented, but I do believe that it is a desirable feature. > > > Berend Tober wrote: > > >John McCawley wrote: > > > >>Is there a way to change the order of columns in a table in Postgres > >>after it has been created? ... > > > > > >The best way to do it is when you have the opportunity to do a > >restore, edit the pg_dump output between the dump and the restore > >steps. There are other approaches that might not be feasible depending > >on circumstances, like dropping and recreating the table and reloading > >data, but you have to deal with foreign key and other dependencies and > >so it is probably more work than justifiable for something that makes > >no practical difference. > > > >Regards, > >Berend Tober > > > > > > > >---------------------------(end of broadcast)--------------------------- > >TIP 9: In versions below 8.0, the planner will ignore your desire to > > choose an index scan if your joining column's datatypes do not > > match > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes: > Actually, I'm pretty sure this is on the TODO. [ checks... ] No, it isn't. My recollection is that we looked very hard at this around the time DROP COLUMN was implemented, and concluded that we weren't ever going to support it, because distinguishing physical from logical column numbers would create a nigh-inexhaustible source of bugs. See the pghackers archives for lots of discussion. regards, tom lane
> I would assume > that all dependent database objects are also dropped when you drop the > table, so you'd have to recreate all of your foreign keys (both > directions) and triggers etc.? Basically. I try to keep my DDL scripts organized in a way that makes this easy. Of course an automated tool could do this as well. For instance I used to use the products from Embarcadero to maintain Sybase databases, and their design tool would create all the DDL needed to update a live database to match the current design. Of course, one experience with a bug and I learned to have it show me the script and read it carefully before proceeding ;-) (Hey, I'm not stupid, it was just a development db that I hosed!) -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 665-7007 voice
As I recall, the MS SQL Server draggy droppy diagrammer tool made it seem trivial to rearrange columns did the same thing. It just generated SQL statements to: Begin transaction select data in new order into a new table drop dependent objects drop old table rename new table re-create dependent objects end transaction It seemed kinda squirrelly to me, but it worked most of the time since MSSQL Server had a good dependency tracking thingie. However, I would not really call it a feature of the DBMS. I would call it a bolted on utility. On 1/6/06, Scott Ribe <scott_ribe@killerbytes.com> wrote: > > I would assume > > that all dependent database objects are also dropped when you drop the > > table, so you'd have to recreate all of your foreign keys (both > > directions) and triggers etc.? > > Basically. I try to keep my DDL scripts organized in a way that makes this > easy. Of course an automated tool could do this as well. For instance I used > to use the products from Embarcadero to maintain Sybase databases, and their > design tool would create all the DDL needed to update a live database to > match the current design. Of course, one experience with a bug and I learned > to have it show me the script and read it carefully before proceeding ;-) > (Hey, I'm not stupid, it was just a development db that I hosed!) > > > -- > Scott Ribe > scott_ribe@killerbytes.com > http://www.killerbytes.com/ > (303) 665-7007 voice > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
You should be able to do this now using pg_depend, it would just take a bit of leg-work. Pretty sure it would be easier than solving physical/logical attribute separation. Someone writing a pg_list_all_dependencies function would make for a really good head start... I wonder if the newsysviews provides anything like that. Robert Treat On Friday 06 January 2006 22:34, Ian Harding wrote: > As I recall, the MS SQL Server draggy droppy diagrammer tool made it > seem trivial to rearrange columns did the same thing. It just > generated SQL statements to: > > Begin transaction > select data in new order into a new table > drop dependent objects > drop old table > rename new table > re-create dependent objects > end transaction > > It seemed kinda squirrelly to me, but it worked most of the time since > MSSQL Server had a good dependency tracking thingie. However, I would > not really call it a feature of the DBMS. I would call it a bolted on > utility. > > On 1/6/06, Scott Ribe <scott_ribe@killerbytes.com> wrote: > > > I would assume > > > that all dependent database objects are also dropped when you drop the > > > table, so you'd have to recreate all of your foreign keys (both > > > directions) and triggers etc.? > > > > Basically. I try to keep my DDL scripts organized in a way that makes > > this easy. Of course an automated tool could do this as well. For > > instance I used to use the products from Embarcadero to maintain Sybase > > databases, and their design tool would create all the DDL needed to > > update a live database to match the current design. Of course, one > > experience with a bug and I learned to have it show me the script and > > read it carefully before proceeding ;-) (Hey, I'm not stupid, it was just > > a development db that I hosed!) > > > > > > -- > > Scott Ribe > > scott_ribe@killerbytes.com > > http://www.killerbytes.com/ > > (303) 665-7007 voice > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Robert Treat Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Sat, Jan 07, 2006 at 02:45:44PM -0500, Robert Treat wrote: > You should be able to do this now using pg_depend, it would just take a bit of > leg-work. Pretty sure it would be easier than solving physical/logical > attribute separation. Someone writing a pg_list_all_dependencies function > would make for a really good head start... I wonder if the newsysviews > provides anything like that. The closest it comes is pg_*_foreign_key*. Listing other dependancies would be damn cool to add, though. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461