Problems building pg 8.1.1

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:

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.

mordicus <mordicus@free.fr> writes:

Le Vendredi 6 Janvier 2006 16:31, Tom Lane a �crit :

./configure --enable-thread-safety
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...

I see. This is actually a packaging bug then: thread_test.c ought to be
part of the -base split, not the -opt split, because -opt is
specifically stated not to be required to build the base package.

Probably the easiest way to handle that is to move src/tools/thread/
someplace else in the directory tree, because the rest of src/tools/
doesn't seem to belong in -base.

regards, tom lane

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:

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:

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.

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