[GENERAL] change natural column order
Hello everyone, When I create a table and later on (say, because customers want to store extra info) add a column, like this: create table test (lastfield varchar); alter table test add column firstfield varchar; is it possible to change the natural order of the columns afterwards? The reason I need this is because the frontend picks up table columns in natural order, looks at the datatype and creates view, input and mutate (html) forms. I'd rather not use views, allthough I know this is the right way, because it would make the application a lot more complex. Can I dive into some system tables and change the natural order? Thanks! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Inheritance in Postgres ?
Tatu Salminen wrote: Hi, Is there going to be full support for inheritance (tables) in future ? Are there going to be any improvements about inheritance ? It all depends on whether anyone with the relevant interest and skills steps forward to develop improvements. At present it seems low on the list of priorities for the core developers. (I'm speaking purely as an outsider here - I'm just another user). However, if those who use/need inheritence can organise themselves and come up with constructive proposals/implementations then I don't believe anyone has anything against inheritence. As with any community project you need a certain amount of activity to get change. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] change natural column order
Hi, Am Dienstag, den 30.11.2004, 10:26 +0100 schrieb Joolz: Hello everyone, When I create a table and later on (say, because customers want to store extra info) add a column, like this: create table test (lastfield varchar); alter table test add column firstfield varchar; is it possible to change the natural order of the columns afterwards? The reason I need this is because the frontend picks up table columns in natural order, looks at the datatype and creates view, input and mutate (html) forms. I'd rather not use views, allthough I know this is the right way, because it would make the application a lot more complex. Can I dive into some system tables and change the natural order? Natural Order? This is similar to a select without order by - the SQL standard says nothing about a natural order. If you want to have a given ordering, why not just specify your column names in that order in your statements? Or just refer to them by column name if your host language allows it. I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) Regards Tino ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Temporal query question
Hi all, I have a "simple" question about the following temporal query with "interval" operator: can I define a query of this type? select myfield,numeric_field from mytable where temporal_attribute temporal_attribute - interval numeric_field || ' days' obviously this syntax is wrong. In other words, is there a way to define a query with a interval operator with variable argument? Thanks Stefano
[GENERAL] Maximum limit on int in plpgsql
Hello, I am passing the ip address as a varchar. Eg: 133.9.4.11 Later on i am splitting the ip address with the delimiter as '.' ip1t:= split_part($1,'.',1); // returns a text value ip2t:= split_part($1,'.',2); // returns a text value ip3t:= split_part($1,'.',3); ip4t:= split_part($1,'.',4); Then i am type casting it into an integer select into ip1 cast(ip1t as integer); select into ip2 cast(ip2t as integer); select into ip3 cast(ip3t as integer); select into ip4 cast(ip4t as integer); The i am taking the sum using this formula out_sum=ip1*256*256*256+ip2*256*256+ip3*256+ip4; When i run the procedure i am getting following error pg_atoi : Numerical result out of range I tried all possible datatypes but still i am getting the same error. Is it the problem of typecasting or the limits on datatype? Thank you in advance, deepthi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] change natural column order
Tino Wildenhain zei: Hi, Am Dienstag, den 30.11.2004, 10:26 +0100 schrieb Joolz: Hello everyone, When I create a table and later on (say, because customers want to store extra info) add a column, like this: create table test (lastfield varchar); alter table test add column firstfield varchar; is it possible to change the natural order of the columns afterwards? The reason I need this is because the frontend picks up table columns in natural order, looks at the datatype and creates view, input and mutate (html) forms. I'd rather not use views, allthough I know this is the right way, because it would make the application a lot more complex. Can I dive into some system tables and change the natural order? Natural Order? This is similar to a select without order by - the SQL standard says nothing about a natural order. Hi Tino, Yes, I know it's not very standard and certainly not in the spirit of relational db's If you want to have a given ordering, why not just specify your column names in that order in your statements? Or just refer to them by column name if your host language allows it. The frondend functions are made so they accept any query (select *) and find out or themselves how to handle things. I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I'll have a look at the system tables (that's where the answer must be) but maybe someone who has done this can save me the time... Thanks! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] change natural column order
Hi, Am Dienstag, den 30.11.2004, 11:31 +0100 schrieb Joolz: ... If you want to have a given ordering, why not just specify your column names in that order in your statements? Or just refer to them by column name if your host language allows it. The frondend functions are made so they accept any query (select *) and find out or themselves how to handle things. SELECT * is almost always bad style. It shouldnt be so hard to write the columns you need even in generic queries. And if you have so smart frontend functions they can always read the column names to find out - while naming them explicit in the select clause saves a lot of hassle here too. I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I'd better find out why :-) And change just this requirement :-) Pro: it also makes your application more db agnostic. I'll have a look at the system tables (that's where the answer must be) but maybe someone who has done this can save me the time... And next time you want to change the internals of the DB to not have to write an ORDER BY into your queries? :-) Regards Tino ---(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
Re: [GENERAL] Maximum limit on int in plpgsql
Hi, Am Dienstag, den 30.11.2004, 02:29 -0800 schrieb [EMAIL PROTECTED]: Hello, I am passing the ip address as a varchar. Eg: 133.9.4.11 Later on i am splitting the ip address with the delimiter as '.' ip1t:= split_part($1,'.',1); // returns a text value ip2t:= split_part($1,'.',2); // returns a text value ip3t:= split_part($1,'.',3); ip4t:= split_part($1,'.',4); Then i am type casting it into an integer select into ip1 cast(ip1t as integer); select into ip2 cast(ip2t as integer); select into ip3 cast(ip3t as integer); select into ip4 cast(ip4t as integer); The i am taking the sum using this formula out_sum=ip1*256*256*256+ip2*256*256+ip3*256+ip4; When i run the procedure i am getting following error pg_atoi : Numerical result out of range I tried all possible datatypes but still i am getting the same error. Is it the problem of typecasting or the limits on datatype? integer means int4 iirc. Which is 32 bit, but signed so you only have 2^31-1 as maxint Did you try int8 here too? btw. there are IP datatypes in PG as well. Otoh, they dont support ip-int8 conversion. It depends on what are your plans once you have that number. Regards Tino ---(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: [GENERAL] Temporal query question
The usual syntax is something like: select myfield,numeric_field from mytable where temporal_attribute_a temporal_attribute_b - ( numeric_field * '1 day'::interval); One of the temporal_attributes tends to be 'now' or 'today' but the principle is the same... On Tue, Nov 30, 2004 at 11:30:16AM +0100, Stefano Bonnin wrote: Hi all, I have a simple question about the following temporal query with interval operator: can I define a query of this type? select myfield,numeric_field from mytable where temporal_attribute temporal_attribute - interval numeric_field || ' days' obviously this syntax is wrong. In other words, is there a way to define a query with a interval operator with variable argument? Thanks Stefano -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpRQpn2j8pGO.pgp Description: PGP signature
Re: [GENERAL] change natural column order
Joolz wrote: I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was too much work for too small a gain. You have my sympathies, but you knew you weren't supposed to rely on the ordering. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Temporal query question
Stefano Bonnin wrote: Hi all, I have a simple question about the following temporal query with interval operator: can I define a query of this type? select myfield,numeric_field from mytable where temporal_attribute temporal_attribute - interval numeric_field || ' days' obviously this syntax is wrong. Not by much: SELECT now() - (1 || ' days')::interval; You could use CAST(...) instead of course, and a date plus/minus an integer defaults to days. -- Richard Huxton Archonet Ltd ---(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
Re: [GENERAL] Maximum limit on int in plpgsql
[EMAIL PROTECTED] wrote: The i am taking the sum using this formula out_sum=ip1*256*256*256+ip2*256*256+ip3*256+ip4; When i run the procedure i am getting following error pg_atoi : Numerical result out of range I tried all possible datatypes but still i am getting the same error. Is it the problem of typecasting or the limits on datatype? Type integer=int4 and is signed. IP addresses are unsigned. You'll need to use an int8 to hold them. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] starting the database server
Hello, I'm using a windows 2000 advanced server, postgresql was installed and working fine, and I'm using pgadminIII. the database server cannot start and get error "is the postmaster running with -i on localhost 127.0.0.1 and accepting tcp/ip connection on the port 5432" the last time, before this error,I've imported a data from a flat file into a table with the command copy. it was fine and I can see my data, after that I've closed the pgadmin and the application that I'm using. this application is via a web browser and you've a logout button, but I've closed the web browser without logging out. can that be the reason? I don't know. restarting the server also didn't help, because the pgsql starts automatically when windows starts, it was always fine. after looking, I find that it was a space problem on the drive where is installed windows and not the drive where is installed the pgsql and the database and also the application that I'm usingby the pgsql. make some free spaces and increasing the virtual memory don't help. now I've enoughfree space on all drives and the virtual memory is 2 times the physique memory of 1024. but still can't start the database. when trying to start it via the services of windows, getan internal error thaterror inwindows or in the service... any idea whyIcannot start the database and the service ? thx Disclaimer: This electronic transmission and any files attached to it are strictly confidential and intended solely for the addressee. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender by return and delete the transmission. Although the sender endeavors to maintain a computer virus free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted. Thank You.
Re: [GENERAL] Maximum limit on int in plpgsql
[EMAIL PROTECTED] wrote:
I have tried using the int8 also, even then i am having the problem.
Please CC the list.
The following works for me. I suspect your equivalent of i is an int4.
CREATE OR REPLACE FUNCTION test_int8(text) RETURNS int8 AS '
DECLARE
i int8;
tot int8;
BEGIN
i := CAST($1 AS int8);
tot := (i * 256 * 256 * 256) + (i * 256 * 256) + (i * 256) + i;
RETURN tot;
END;
' LANGUAGE plpgsql;
SELECT test_int8('255');
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] starting the database server
Nefnifi, Kasem wrote: when trying to start it via the services of windows, get an internal error that error in windows or in the service... What error? What do your system logs say? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] change natural column order
Richard Huxton zei: Joolz wrote: I dont think the overhead in implementing such a rarely needed feature isnt worth it. We need a lot more other things ;-) I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was too much work for too small a gain. Got it: http://archives.postgresql.org/pgsql-hackers/2003-11/msg00869.php You have my sympathies, but you knew you weren't supposed to rely on the ordering. Although I agree it sounds a bit weird, I saw someone mentioning that column ordering is a part of ANSI-SQL. Anyway, I'll have a look at attnum, see what it can do for me. Thanks everyone! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
On Mon, 29 Nov 2004, Bill Harris wrote: Marc G. Fournier [EMAIL PROTECTED] writes: If there was an official newsgroup for postgresql, would you switch to using Usenet from using the mailing lists? As a side note, for those that do vote 'yes', please note that there is an official pgsql.* hierarchy gated from the mailing lists, that is available at news.postgresql.org, if you do wish to use a news reader vs a mail reader ... FWIW, I voted yes, but my vote depended upon it being a comp.databases.postgresql.* hierarchy, done according to USENET guidelines. I sense that would be a lot more important for PostgreSQL in the long term and a lot more sustainable in general than a pgsql.* hierarchy. It's been my experience that processes done outside the norm tend to have extra problems along the way that cost more than the immediate gratification is worth, even if it does seem more painful at the time. Just as an FYI, the pgsql.* hierarchy was done within the guidelines, or, at least, was done with the aid of the newsadmins of two of the larger news sites on the 'Net (Stanford and Supernews, both of which carry, and distribute, it), *and* has been picked up by ISC as an official hierarchy, including in the active file that it distributes on their FTP server ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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
Re: [GENERAL] starting the database server
Nefnifi, Kasem wrote: thanks Richard for the reaction, bellow a print screen of the error that I get when I try to start the service from windows services control panel: ole0.bmp Try and stick to cutting and pasting text rather than embedding images - lots of people on the lists will be reading/posting in plain text rather than HTML. Also images use a lot more bandwidth than text. Anyway - The service did not return an error. Seems unlikely that you wouldn't get some sort of error. Make sure your logging is turned on in postgresql.conf and then check your system logs for an error message - there should be something unless PG is failing *very* early in the startup. If we still can't generate an error message, it might be worth trying to start the backend from the command-line. The second error message you sent Connection refused just means the application couldn't contact the PG backend. We know it can't since the service isn't starting. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] change natural column order
Hi, Joolz, you already got quite a few answers, that the frontend is probably not properly designed, if it relies on a certain column ordering. I agree completely with that. However your question got me curious, and I've digged around a bit in the system tables. You might be interested in my findings. See below. Citing Joolz [EMAIL PROTECTED]: I agree. Only I think this wouldn't require new functionality, I have a gut feeling that this is possible as it is. Now only find out how :) I'll have a look at the system tables (that's where the answer must be) but maybe someone who has done this can save me the time... If you do: set search_path=information_schema; \d columns and look at the Columns defined, you'll find a column called ordinal_position, which incidentally corresponds to the position of the columns on output. If you dig a bit further and look at the definition of the columns view, you'll find, that this column comes from a column attnum in pg_attribute. As database superuser, you can actually change the values of attnum, however doing so results in: ERROR: invalid memory alloc request size 4294967295 on queries on the tables for which you changed attnum. So: 1.) obviously PostgreSQL does not like it at all (at least on my platform, which is OpenBSD 3.6) 2.) I wouldn't risk messing with a system table, which I can only write to if I'm superuser if I don't completely understand what's happening behind the scenes (at least not for production use). 3.) changing that behaviour is probably a lot more work than changing the frontend. Regards, Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
Tino Wildenhain wrote: Hi, Am Dienstag, den 30.11.2004, 10:26 +0100 schrieb Joolz: is it possible to change the natural order of the columns afterwards? The reason I need this is because the frontend picks up table columns in natural order, looks at the datatype and creates view, input and mutate (html) forms. Natural Order? This is similar to a select without order by - the SQL standard says nothing about a natural order. This is not true. Columns have an order. You can do INSERTs without specifying the columns. The values won't be inserted randomly but in their order. Changing the order of the columns is not a frontend question but logically. (Some) people would like to see relevant columns near to each other, even with an admin program. I would welcome some alter table column order feature. Btw human beings like changing things. Mage ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
Marc G. Fournier [EMAIL PROTECTED] writes: If there was an official newsgroup for postgresql, would you switch to using Usenet from using the mailing lists? As a side note, for those that do vote 'yes', please note that there is an official pgsql.* hierarchy gated from the mailing lists, that is available at news.postgresql.org, if you do wish to use a news reader vs a mail reader ... FWIW, I voted yes, but my vote depended upon it being a comp.databases.postgresql.* hierarchy, done according to USENET guidelines. I sense that would be a lot more important for PostgreSQL in the long term and a lot more sustainable in general than a pgsql.* hierarchy. It's been my experience that processes done outside the norm tend to have extra problems along the way that cost more than the immediate gratification is worth, even if it does seem more painful at the time. My $0.02. Bill -- Bill Harris Facilitated Systems http://facilitatedsystems.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] delete with index scan
I have a table t1 with a primary key column pkey, and a table t2, with a primary key column pkey. Is there a way to make the following delete use the indexes? delete from t1 where pkey in (select pkey from t2); NOTICE: QUERY PLAN: Seq Scan on t1 (cost=0.00..6616238.99 rows=660239 width=6) SubPlan - Seq Scan on t2 (cost=0.00..20.00 rows=1000 width=8) EXPLAIN thanks, Sally -- Sally Ruggero Software Development North Electric Company, Inc. 6131 Falls of Neuse Road, Suite 205 Raleigh, NC 27609 Office: (919) 341-6009 Fax:(919) 341-6010 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] change natural column order
Daniel Martini zei:
Hi,
Joolz, you already got quite a few answers, that the frontend is
probably
not properly designed, if it relies on a certain column ordering. I
agree
Hi Daniel,
Well, I made the frontend myself, so... :)
There is a reason that I made it this way, I have a database with a
lot of different tables and I wanted the frontend to be as versatile
as possible, so I wouldn't have to write PHP frontend functions for
each table or change the SELECT statements that generate the data
for the frontend each time a column is added (and this will happen).
So my application does things like this (semi-code):
$exclude_columns = {oid, audit_column_one, audit_column_two};
function one() {
$sql = select * from fubar;
two($sql);
}
function two() {
// make array from $sql
// remove elements that exist in $exclude_columns
// show array
}
completely with that. However your question got me curious, and I've
digged
around a bit in the system tables. You might be interested in my
findings.
See below.
BTW I found out that my questions is not as weird as I expected it
to be. MySQL can do it (AFTER clause), Firebird too, and without a
doubt others like Oracle and DB2 too.
Citing Joolz [EMAIL PROTECTED]:
I agree. Only I think this wouldn't require new functionality, I
have a gut feeling that this is possible as it is. Now only find
out
how :)
I'll have a look at the system tables (that's where the answer
must
be) but maybe someone who has done this can save me the time...
If you do:
set search_path=information_schema;
\d columns
and look at the Columns defined, you'll find a column called
ordinal_position,
which incidentally corresponds to the position of the columns on
output. If
you dig a bit further and look at the definition of the columns
view, you'll
find, that this column comes from a column attnum in pg_attribute.
As
database superuser, you can actually change the values of attnum,
however
doing so results in:
ERROR: invalid memory alloc request size 4294967295
on queries on the tables for which you changed attnum. So:
1.) obviously PostgreSQL does not like it at all (at least on my
platform,
which is OpenBSD 3.6)
2.) I wouldn't risk messing with a system table, which I can only
write to
if I'm superuser if I don't completely understand what's
happening
behind the scenes (at least not for production use).
3.) changing that behaviour is probably a lot more work than
changing the
frontend.
Yes, if I understand all the threads correctly, attnum is somehow
bound to the physical location of the data it represents. That makes
it almost impossible to fiddle around with it. Someone proposed to
make an extra field attpos, but it doesn't look like this will be
happening soon.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Upcoming Changes to News Server ...
On 11/29/2004 11:53 PM, Gary L. Burnore wrote: Stay out of my email. This ia a PostgreSQL related topic discussed on PostgreSQL mailing lists and you react like this to a mail from a PostgreSQL CORE team member? Rethink your attitude. Jan At 11:50 PM 11/29/2004, you wrote: On 11/23/2004 4:46 PM, Gary L. Burnore wrote: It's ok. Mysql's better anyway. This is the attitude I've seen from many of the pro-usenet people. If I don't get it my way I will bash your project and try to do harm. I am too one of those who have left usenet many years ago. Partly because of people with this attitude. And I don't consider it much of a loss if we lose the message to these people. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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
Re: [GENERAL] delete with index scan
Have you considered a join? delete from t1 where pkey = t2.pkey; Also, it appears you never ANALYZEd t2, maybe that would help? On Tue, Nov 30, 2004 at 04:55:58AM -0500, [EMAIL PROTECTED] wrote: I have a table t1 with a primary key column pkey, and a table t2, with a primary key column pkey. Is there a way to make the following delete use the indexes? delete from t1 where pkey in (select pkey from t2); NOTICE: QUERY PLAN: Seq Scan on t1 (cost=0.00..6616238.99 rows=660239 width=6) SubPlan - Seq Scan on t2 (cost=0.00..20.00 rows=1000 width=8) EXPLAIN thanks, Sally -- Sally Ruggero Software Development North Electric Company, Inc. 6131 Falls of Neuse Road, Suite 205 Raleigh, NC 27609 Office: (919) 341-6009 Fax:(919) 341-6010 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp4KTT50adyK.pgp Description: PGP signature
Re: [GENERAL] change natural column order
SELECT * is almost always bad style. It shouldnt be so hard to
Why ?
Many languages, including PHP, have associative arrays, so you should
just use array[column_name] instead of array[column_number]. This is what
I do, all the time.
For instance, in Python :
* The wrong way :
cursor.execute( SELECT name, address, zipcode FROM people WHERE blah )
data = cursor.fetchone()
name = data[0]
address = data[1]
zipcode = data[2]
This is BAD because :
- When your SELECT has more than a few columns, you have to be really
really careful about the order, and one day you'll mess it up and it'll
bite you. Same thing with parameters by number in printf !
- When you add a column, you have to go through all the SELECTs in your
app that are not auto-generated
- Including all the columns slows down query generation and query parsing.
* The Very wrong way :
cursor.execute( SELECT * FROM people WHERE blah )
data = cursor.fetchone()
name = data[0]
address = data[1]
zipcode = data[2]
This is BAD because, when your table structure changes, your application
breaks.
* How I do it :
cursor.execute( SELECT * FROM people WHERE blah )
data = cursor.dictfetchone()
name = data['name']
address = data['address']
zipcode = data['zipcode']
or :
for key, value in data.items():
print key, =, value
or instanciate a class and set its attributes:
result = myclass()
for key, value in data.items():
setattr( result, key, myclass.type_converter[key](value) )
The last being how a decent DB library would do it.
I find this a lot better, because :
- no need to generate and then parse long queries with all the columns
- no worries about column order or adding columns
- raises an exception if a column misses or has the wrong name
- a lot simpler
- a lot more explicit
- you can auto-cast to and from the DB if your class has a table of type
converters indexed on the column name
- etc...
Also, in my case, it eases query generation a lot, I use the same code
for many tables.
You can do this in PHP I believe with associative arrays...
Now, to prove the point, take the following PHP code ripped out of the
osCommerce (which I don't consider an example of good programming, but
it's a good example here). It builds a SELECT wiht various parameters.
Now, tell me, if you access columns according to their number in the
result, what is the column number for the products_description ?
If you access columns by their name, then it's just
$result['products_description']
code class=spaghetti
$select_column_list = '';
for ($i=0, $n=sizeof($column_list); $i$n; $i++) {
switch ($column_list[$i]) {
case 'PRODUCT_LIST_MODEL':
$select_column_list .= 'p.products_model, ';
break;
case 'PRODUCT_LIST_NAME':
$select_column_list .= 'pd.products_name,
pd.products_description, ';
break;
case 'PRODUCT_LIST_MANUFACTURER':
$select_column_list .= 'm.manufacturers_name, ';
break;
case 'PRODUCT_LIST_QUANTITY':
$select_column_list .= 'p.products_quantity, ';
break;
case 'PRODUCT_LIST_IMAGE':
$select_column_list .= 'p.products_image, ';
break;
case 'PRODUCT_LIST_WEIGHT':
$select_column_list .= 'p.products_weight, ';
break;
}
}
// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
if (isset($HTTP_GET_VARS['filter_id'])
tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
$listing_sql = select . $select_column_list . p.products_id,
p.products_ready_to_ship, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status,
s.specials_new_products_price, p.products_price) as final_price from .
TABLE_PRODUCTS . p, . TABLE_PRODUCTS_DESCRIPTION . pd, .
TABLE_MANUFACTURERS . m, . TABLE_PRODUCTS_TO_CATEGORIES . p2c left
join . TABLE_SPECIALS . s on p.products_id = s.products_id where
p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and
m.manufacturers_id = ' . (int)$HTTP_GET_VARS['manufacturers_id'] . ' and
p.products_id = p2c.products_id and pd.products_id = p2c.products_id and
pd.language_id = ' . (int)$languages_id . ' and p2c.categories_id = ' .
(int)$HTTP_GET_VARS['filter_id'] . ';
} else {
// We show them all
$listing_sql = select . $select_column_list . p.products_id,
p.products_ready_to_ship, p.manufacturers_id, p.products_price,
p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL)
as specials_new_products_price, IF(s.status,
Re: [GENERAL] starting the database server
Hi Richard, bellow the text from the log file: -- start log file -- 30/11/2004 16:45:08PostgreSQL Error None0 N/A BAAN-AT-HOMEexecution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server. 30/11/2004 16:42:52SceCli Warning None1202N/A BAAN-AT-HOMESecurity policies are propagated with warning. 0x534 : No mapping between account names and security IDs was done. For best results in resolving this event, log on with a non-administrative account and search http://support.microsoft.com for troubleshooting 1202 events. A user account in one or more Group policy objects (GPOs) could not be resolved to a SID. This error is possibly caused by a mistyped nor deleted user account referenced in either the User Rights or Restricted Groups branch of a GPO. To resolve this event, contact an administrator in the domain to perform the following actions: 1.Identify accounts that could not be resolved to a SID: From the command prompt, type: FIND /I Cannot find %SYSTEMROOT%\Security\Logs\winlogon.log The string following Cannot find in the FIND output identifies the problem account names. Example: Cannot find JohnDough. In this case, the SID for username JohnDough could not be determined. This most likely occurs because the account was deleted, renamed, or is spelled differently (e.g. JohnDoe). 2.Identify the GPOs that contain the unresolvable account name: From the command prompt type FIND /I JohnDough %SYSTEMROOT%\Security\templates\policies\gpt*.* The output of the FIND command will resemble the following: -- GPT0.DOM -- GPT1.DOM SeRemoteShutdownPrivilege=JohnDough This indicates that of all the GPO's being applied to this machine, the unresolvable account exists only in one GPO. Specifically, the cached GPO named GPT1.DOM. Now we need to determine the friendly name of this GPO in the next step. 3. Locate the friendly names of each of the GPOs that contain an unresolvable account name. These GPOs were identified in the previous step. From the command prompt, type: FIND /I [Mapping] %SYSTEMROOT%\Security\Logs\winlogon.log The string following [Mapping] gpt?.dom = in the FIND output identifies the friendly names for all GPO's being applied to this machine. Example: [Mapping] gpt1.dom = User Rights Policy In this case, the GPO that contains the unresolvable account (gpt1.dom) has a friendly name of User Rights Policy. 4. Remove unresolved accounts from each GPO that contains an unresolvable account. a. Start - Run - MMC.EXE b. From the File menu select Add/Remove Snap-in... c. From the Add/Remove Snap-in dialog box select Add... d. In the Add Standalone Snap-in dialog box select Group Policy and click Add e. In the Select Group Policy Object dialog box click the Browse button. f. On the Browse for a Group Policy Object dialog box choose the All tab g. Right click on the first policy identified in step 3 and choose edit h. Review each setting under Computer Configuration/ Windows Settings/ Security Settings/ Local Policies/ User Rights Assignment or Computer Configuration/ Windows Settings/ SecuritySettings/ Restricted Groups for accounts identified in step 1. i. Repeat steps 3g and 3h for all subsequent GPOs identified in step 3. -- end log file -- Best Regards / Vriendelijke Groeten / Salutations Distinguées / Freundliche Grüße !!! Kasem NEFNIFI AtosOrigin Belgium N.V. Minervastraat 7 1930 Zaventem (Belgium) Tel : +32(0)2 712 28 30 Fax : +32(0)2 712 28 63 GSM : +32 495 25 12 33 Email : [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] www.atosorigin.com http://www.atosorigin.com -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 2:17 PM To: Nefnifi, Kasem Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] starting the database server Nefnifi, Kasem wrote: thanks Richard for the reaction, bellow a print screen of the error that I get when I try to start the service from windows services control panel: ole0.bmp Try and stick to cutting and pasting text rather than embedding images - lots of people on the lists will be reading/posting in plain text rather than HTML. Also images use a lot more bandwidth than text. Anyway - The service did not return an error. Seems unlikely that you wouldn't get some sort of error. Make sure your logging is turned on in postgresql.conf and then check your system logs for an error message - there should be something unless PG is failing *very* early in the startup. If we
Re: [GENERAL] VACUUM and ANALYZE Follow-Up
Hasn't anybody read the other threads I posted links to? (That's a rhetorical question, because the answer clearly is no :-() You mean this one : http://archives.postgresql.org/pgsql-hackers/2004-11/msg00985.php In which you write : rel-pages = RelationGetNumberOfBlocks(relation); if (relation-rd_rel-relpages 0) density = relation-rd_rel-reltuples / relation-rd_rel-relpages; else if (relation-rd_rel-reltuples 0) /* already a density */ density = relation-rd_rel-reltuples; else density = some_default_estimate; rel-tuples = round(rel-pages * density); A variant of this is to set reltuples = density, relpages = 1 instead of 0, which makes the relpages value a lie but would be even less likely to confuse client-side code. I don't know how it works internally, but if an empty table has a filesize of 0 that's a lie, but if an empty table is just one page with a header saying nothing here, go away, it's the truth. And I like your idea. I definitely think it would be useful. Your proposed implementation is a bit hackish but quick and easy to do, and with minimal breakage. What do you think of the idea of using the estimation of the number of rows to be inserted in the table as a help in planning the queries on this table made during the INSERT (like FK checks) ? Did you read my previous post on this ? Thanks ! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] change natural column order
Richard Huxton [EMAIL PROTECTED] writes: I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was too much work for too small a gain. Yup, that was exactly the conclusion. Too much work and too much risk of introducing bugs (by using the wrong one of logical and physical column number in any given place). You really have to drop and recreate the table if you want to reorder the columns. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Bill Harris Sent: Monday, November 29, 2004 9:50 PM To: Marc G. Fournier Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ... Marc G. Fournier [EMAIL PROTECTED] writes: If there was an official newsgroup for postgresql, would you switch to using Usenet from using the mailing lists? As a side note, for those that do vote 'yes', please note that there is an official pgsql.* hierarchy gated from the mailing lists, that is available at news.postgresql.org, if you do wish to use a news reader vs a mail reader ... FWIW, I voted yes, but my vote depended upon it being a comp.databases.postgresql.* hierarchy, done according to USENET guidelines. I sense that would be a lot more important for PostgreSQL in the long term and a lot more sustainable in general than a pgsql.* hierarchy. It's been my experience that processes done outside the norm tend to have extra problems along the way that cost more than the immediate gratification is worth, even if it does seem more painful at the time. My $0.02. me too. Funny how the YES vote got interpreted for us. ---(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: [GENERAL] change natural column order
Tom Lane wrote: Richard Huxton [EMAIL PROTECTED] writes: I think you'll find you're out of luck. IIRC there was some discussion on the hackers list regarding a mapping layer that would let you re-order columns. I think the decision was too much work for too small a gain. Yup, that was exactly the conclusion. Too much work and too much risk of introducing bugs (by using the wrong one of logical and physical column number in any given place). You really have to drop and recreate the table if you want to reorder the columns. This can be hard if you have foreign keys. I used dump, edit, and restore the whole database in the past. Mage ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Temporal query question
Richard Huxton [EMAIL PROTECTED] writes: SELECT now() - (1 || ' days')::interval; Note that the preferred form is SELECT now() - (n * '1 day'::interval); if n is a numeric variable. When you write SELECT now() - (n || ' days')::interval; you are relying on the following: (1) an implicit cast from n's numeric type to text; (2) the textual concatenation operator ||; (3) an explicit cast from text to interval; (4) the timestamp - interval operator. In the preferred way, '1 day'::interval is (in effect) a compile-time constant of type interval, and the * represents an invocation of the built-in float8 * interval operator. So you have (1) an implicit cast to float8, if n isn't already float8; (2) the float8 * interval operator; (3) the timestamp - interval operator. This is probably significantly faster than the other way, and more importantly it does not rely on an implicit cast across type categories, which is something we are trying to get away from. You could use CAST(...) instead of course, and a date plus/minus an integer defaults to days. Right, there are also the date +/- integer operators, which are the best thing to use if you only want date-level arithmetic. With timestamp minus interval you have to consider questions like what happens on daylight savings transition days. So the correct answer to this might just be SELECT CURRENT_DATE - 1; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] starting the database server
Nefnifi, Kasem wrote: Hi Richard, bellow the text from the log file: -- start log file -- 30/11/2004 16:45:08PostgreSQL Error None0 N/A BAAN-AT-HOME execution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server. There you go - the user PostgreSQL tries to run under has administrative permissions. This isn't allowed for security purposes. 30/11/2004 16:42:52SceCli Warning None1202N/A BAAN-AT-HOME Security policies are propagated with warning. 0x534 : No mapping between account names and security IDs was done. For best results in resolving this event, log on with a non-administrative account and search http://support.microsoft.com for troubleshooting 1202 events. A user account in one or more Group policy objects (GPOs) could not be resolved to a SID. This error is possibly caused by a mistyped nor deleted user account referenced in either the User Rights or Restricted Groups branch of a GPO. To resolve this event, contact an administrator in the domain to perform the following actions: What's more - there seems to have been a problem mapping user/group numbers to names. The rest of the message gives details of how to correct this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
[EMAIL PROTECTED] (Bill Harris) writes: Marc G. Fournier [EMAIL PROTECTED] writes: If there was an official newsgroup for postgresql, would you switch to using Usenet from using the mailing lists? As a side note, for those that do vote 'yes', please note that there is an official pgsql.* hierarchy gated from the mailing lists, that is available at news.postgresql.org, if you do wish to use a news reader vs a mail reader ... FWIW, I voted yes, but my vote depended upon it being a comp.databases.postgresql.* hierarchy, done according to USENET guidelines. I sense that would be a lot more important for PostgreSQL in the long term and a lot more sustainable in general than a pgsql.* hierarchy. It's been my experience that processes done outside the norm tend to have extra problems along the way that cost more than the immediate gratification is worth, even if it does seem more painful at the time. Just as an FYI ... the latest RFD is for *one* comp.databases.postgresql group to be created, that is not-gated ... this means that those using it would not have the benefit(s) that those using the pgsql.* hierarchy do, namely access to the wealth of knowledge/experience of those on the mailing lists ... I had posed the 'who would use USENET' question on -hackers previous to the poll, and the general opinion was not in this life time by ppl like PeterE, TomL, JoshuaD, etc ... the thread can be seen: http://archives.postgresql.org/pgsql-hackers/2004-11/msg01110.php ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] proper use of temp table in function
Hi all, Sorry to ask since I'm pretty sure this kind of question have been asked again an again. But I searched and haven't found my answer. So here is the question, please help :-) In plpgsql function how do you deal with temporary table. I need do a bunch of data manipulations in my function before returning a ref cursor. The problem is that, as you may know, the temp table aren't drop at the end of the function. And if I try to drop the tables when the cursor is open on it I get errors too. And I need to be able to call the function simultaneously without one interfering with the other. So table created by one instance of the function don't know about tables created by other functions ... I have 4 big selects to construct the data I need... I can't just put all in a big one. So I need a way to store and read data between selects. Thanks for your help!! /David ' LANGUAGE 'plpgsql'; CREATE FUNCTION uk_usp_Comptabilite_AgeDeCompteClient(DATE, VARCHAR, VARCHAR, INTEGER, INTEGER) RETURNS refcursor AS ' DECLARE __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] delete with index scan
Thank you so much, I guess my sql skills need sharpening. I have been trying all night to find out how to do a join with delete. I thought I needed to mention t2 in the from clause, but that didn't work. This worked great. Sally - Original Message - From: Martijn van Oosterhout [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 10:38 AM Subject: Re: [GENERAL] delete with index scan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] installing postgresql .rpms not in /usr/bin
Hi, Is there any reason why postgresql rpms (FC2) are not relocatable? The reason I ask I need to have 2 postgresql instances installed on machine with no C compiler. I'like installed in diferent driectory and not in /usr/bin... I'd rather not to start renaming files :( I know that I can set up prefix when building postgresql from the source. Any suggestion how to install from rpm 2 different versions on the single Linux machine. Thanks, Mark. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] starting the database server
Hello, but it has worked fine since the installation without any error until now and nothing has been changed in the system policy. how it can something like this happened. now the concrete solution, I've to follow the solution proposed in the log file. which user should I use to start the database, if I take an only normal user, get the message error that I don't permissions, as administrator PostgreSql don't let me start the database. strange, because in all databases you've to be administrator to do something like except Postgresql. what kind solution do you suggest to me and thx in advance. -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 30, 2004 5:42 PM To: Nefnifi, Kasem Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] starting the database server Nefnifi, Kasem wrote: Hi Richard, bellow the text from the log file: -- start log file -- 30/11/200416:45:08PostgreSQL Error None0 N/A BAAN-AT-HOME execution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server. There you go - the user PostgreSQL tries to run under has administrative permissions. This isn't allowed for security purposes. 30/11/200416:42:52SceCli Warning None1202N/A BAAN-AT-HOME Security policies are propagated with warning. 0x534 : No mapping between account names and security IDs was done. For best results in resolving this event, log on with a non-administrative account and search http://support.microsoft.com for troubleshooting 1202 events. A user account in one or more Group policy objects (GPOs) could not be resolved to a SID. This error is possibly caused by a mistyped nor deleted user account referenced in either the User Rights or Restricted Groups branch of a GPO. To resolve this event, contact an administrator in the domain to perform the following actions: What's more - there seems to have been a problem mapping user/group numbers to names. The rest of the message gives details of how to correct this. -- Richard Huxton Archonet Ltd Disclaimer: This electronic transmission and any files attached to it are strictly confidential and intended solely for the addressee. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender by return and delete the transmission. Although the sender endeavors to maintain a computer virus free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted. Thank You. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] starting the database server
Nefnifi, Kasem wrote: Hello, but it has worked fine since the installation without any error until now and nothing has been changed in the system policy. how it can something like this happened. Something must have changed. If it's not your installation of PostgreSQL then it's something in the authentication system. now the concrete solution, I've to follow the solution proposed in the log file. which user should I use to start the database, if I take an only normal user, get the message error that I don't permissions, as administrator PostgreSql don't let me start the database. strange, because in all databases you've to be administrator to do something like except Postgresql. Keep the PostgreSQL user the same, but trace its group membership and check file permissions. The reason PosgreSQL refuses to run as an administrator is that to do so opens a security hole. Other databases open that hole and you can read about the hacks on the security lists. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] starting the database server
30/11/200416:45:08PostgreSQL Error None0 N/A BAAN-AT-HOMEexecution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromise. See the documentation for more information on how to properly start the server. Hm, the first idea that comes to mind would be to follow the advice of this error message. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] VACUUM and ANALYZE Follow-Up
Title: Re: [GENERAL] VACUUM and ANALYZE Follow-Up Tom, I did read through the links you provided. Unfortunately, I don't feel qualified to judge the technical merits of the possible solutions. Since you appear to be well informed on this issue, can I ask you a couple of quick questions? 1. Would it be difficult to add an option to ANALYZE to force it to pretend that there are a minimum number of rows (e.g., ANALYZE MINIMUM 1000 or something)? This would appear to be a simple-minded way to solve the problem without any concerns about backward compatibility. 2. Why does a newly CREATE'd table behave differently than an empty table after ANALYZE? Does it make sense that it should? In the CREATE case, the assumptions appear to be much more reasonable for a table that is going to grow. 3. Has anyone ever tested whether there is a measurable performance gained after doing ANALYZE on empty or nearly empty tables? We know that there is a very large (in my case 15x) performance loss when the table starts growing. If the gain is small or negligable when the tables really are small, then perhaps worrying about maintaining current behaviour is not as important. The nice thing about option (1) is that is solves the slow insert issue both for empty tables and for tables with a few rows. It also causes absolutely no backward-compatibility issues. Thanks very much for your comments on this. Mark
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
For what its worth, I vote no. I like the mailing lists. If having a newsgroup is beneficial, I say go ahead and start one, but don't mess around with the mailing lists, please. I really like the one or two digests I get in my mailbox everyday. - Greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Dropping sequences
I have changed the default sequence on a primary key integer (created as SERIAL) field in a table, but it will not let me drop the old sequence and continues to tell me that the sequence is required by the table. Can someone tell me what is required to get this dropped? I am just paranoid that it could be using the old sequence still even though it seems all is coming from the new sequence. -- Robert ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Maximum limit on int in plpgsql
On Tue, Nov 30, 2004 at 11:18:44AM +, Richard Huxton wrote: [EMAIL PROTECTED] wrote: The i am taking the sum using this formula out_sum=ip1*256*256*256+ip2*256*256+ip3*256+ip4; When i run the procedure i am getting following error pg_atoi : Numerical result out of range I tried all possible datatypes but still i am getting the same error. Is it the problem of typecasting or the limits on datatype? Type integer=int4 and is signed. IP addresses are unsigned. You'll need to use an int8 to hold them. Or use a 2^31 bias and use a signed int4. These functions map between dotted-quads and int4s in this way, to maintain ordering. (Not as convenient as the inet or cidr types, or just cobbling together a simple ip type as a C function, but sometimes you have to do the inelegant approach...) create or replace function ip2int(text) returns int as ' DECLARE a int; b int; c int; d int; BEGIN a := split_part($1, ''.'', 1); b := split_part($1, ''.'', 2); c := split_part($1, ''.'', 3); d := split_part($1, ''.'', 4); RETURN (a-128) * 16777216 + b * 65536 + c * 256 + d; END; ' LANGUAGE plpgsql IMMUTABLE; create or replace function int2ip(int) returns text as ' DECLARE a int; b int; c int; d int; BEGIN a := (($1 24) 255) # 128; b := ($1 16) 255; c := ($1 8) 255; d := $1 255; RETURN to_char(a, ''FM999'') || ''.'' || to_char(b, ''FM999'') || ''.'' || to_char(c, ''FM999 '') || ''.'' || to_char(d, ''FM999''); END; ' LANGUAGE plpgsql IMMUTABLE; Cheers, Steve ---(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
[GENERAL] Postgres Design
I have hundreds of different survey files in a customized database. I would like to convert them to postgres. My question is should I create one database for all the surveys or should I limit the number of surveys to a database. for example create a different database for each survey year, or by research company ? ---(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
Re: [GENERAL] change natural column order
Pierre-Frédéric Caillaud [EMAIL PROTECTED] writes: SELECT * is almost always bad style. It shouldnt be so hard to Why ? Many languages, including PHP, have associative arrays, so you should just use array[column_name] instead of array[column_number]. This is what I do, all the time. This is another religious issue you'll find people pretty adamant on both sides. I tend to prefer to use SELECT * because it reduces repetition and improves modularity. There are fewer places in the code that need to know about a new column being added to a table (or expression to a query) and fewer places that need to know about a new column (or expression) being needed in the final result. [I am assuming you use look up columns by name. To me it seems the only reasonable approach for all but the simplest cases] However many databases and interfaces have some pretty strong practical problems that result from using it. So it's pretty standard DBA rule-of-thumb material to discourage its use. Oracle had serious problems dealing with prepared statements and views when new columns were added. I think they've mostly resolved those issues. The only problem I've run into with Postgres is that there's no way to *remove* a column from a list of columns without listing all the non-removed columns. And there's no way to disambiguate if you add a second column by the same name. So you have some situations where you can't add an expression with the correct name without explicitly listing every other column. There may be performance implications for having more columns than necessary in a select list as well. I wouldn't worry too much about this for reasonable sizes but when you start doing joins against many tables, some of which could be quite wide, and you don't need many of the columns being included then the select * could be slowing down the query. I haven't done experiments on this to see how big an effect it has though. Any other practical or aesthetic Pros and Cons people can suggest? -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Ignore this ...
Just making sure that the new amavisd with spamassassin 3.x isn't causing a problem ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] VACUUM and ANALYZE Follow-Up
Mark Dexter [EMAIL PROTECTED] writes: 1. Would it be difficult to add an option to ANALYZE to force it to pretend that there are a minimum number of rows (e.g., ANALYZE MINIMUM 1000 or something)? This would appear to be a simple-minded way to solve the problem without any concerns about backward compatibility. This strikes me as useless, not to mention not backward-compatible at all. Where is ANALYZE supposed to come up with the data to fill pg_statistic? Shall we add the same kluge option to VACUUM and CREATE INDEX? 2. Why does a newly CREATE'd table behave differently than an empty table after ANALYZE? Does it make sense that it should? This is a long-standing hack, which I am proposing undoing; see http://archives.postgresql.org/pgsql-patches/2004-11/msg00339.php and in particular read the comments that the patch deletes. 3. Has anyone ever tested whether there is a measurable performance gained after doing ANALYZE on empty or nearly empty tables? As long as the table *stays* empty or nearly so, the knowledge that it is small is good for the planner to have. The problem we are dealing with here boils down to the fact that a table can grow substantially without the planner being aware of the fact. So IMHO the correct solution is to attack that problem head-on, not to invent weird options to make ANALYZE lie about what it found. CREATE TABLE shouldn't be lying either, but at one time that seemed like a good quick-hack workaround ... regards, tom lane ---(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
Re: [GENERAL] Dropping sequences
Robert Fitzpatrick [EMAIL PROTECTED] writes: I have changed the default sequence on a primary key integer (created as SERIAL) field in a table, but it will not let me drop the old sequence and continues to tell me that the sequence is required by the table. Can someone tell me what is required to get this dropped? Theoretically you have to drop the serial column. If you don't mind mucking with system catalogs, you could remove the pg_depend entry linking the sequence to the column, and then it would let you drop the sequence. 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: [GENERAL] Postgres Design
On Tue, Nov 30, 2004 at 02:27:32PM -0500, Morris N. Grajower wrote: I have hundreds of different survey files in a customized database. I would like to convert them to postgres. My question is should I create one database for all the surveys or should I limit the number of surveys to a database. for example create a different database for each survey year, or by research company ? Might you ever need to issue a query that joins multiple surveys? If so then it would make sense to put them in the same database; if you want to group some surveys together then you could use schemas. Also think about whether you want to have separate tables or schemas for individual years or research companies, or whether you might be able to use a table with columns like year and research_company_id (or maybe a combination of both approaches). Without knowing more about your data it's hard to recommend a good way to organize it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [GENERAL] change natural column order
SELECT * is almost always bad style. It shouldnt be so hard to This is another religious issue you'll find people pretty adamant on both sides. Seems so. I tend to prefer to use SELECT * because it reduces repetition and improves modularity. There are fewer places in the code that need to know about a new column being added to a table (or expression to a query) and fewer places that need to know about a new column (or expression) being needed in the final result. Same here. Simplifies request generation, simplifies coding, less change-tracking... [I am assuming you use look up columns by name. To me it seems the only reasonable approach for all but the simplest cases] Of course. SELECT * and then using the columns by number is, well, like dropping a brick and hoping it doesnt land on your foot. However many databases and interfaces have some pretty strong practical problems that result from using it. So it's pretty standard DBA rule-of-thumb material to discourage its use. For instance the Python MySQL has no such functionality. PHP-Mysql has, though. The only problem I've run into with Postgres is that there's no way to *remove* a column from a list of columns without listing all the non-removed columns. True. In that case, the database library which generates queries, should offer this kind of functionality, and generate the SELECT col1, ..., colN appropriately (mine does). In fact I like SELECT * because I find generated queries to be, sometimes, kludgey at best to manipulate when using JOIN's or complicated WHEREs. Let me explain. In my DB library, I have classmethods to create a class instance from a row. For instance, Class.GetByKey( value ) looks up on the primary key. Which field is the primary key is declared once in the class definition. GetByKey is part of the base class, of course (some kind of DbRow). Some classes will have special methods, like fulltext search methods, most recent search methods... So, the SQL is neatly hidden in these methods, but sometimes you do need to mess with it : Then, I have something like Class.GetSQL( SELECT m.* FROM mytable m, othertable o WHERE some joins ... ) which just executes the SQL string (with parameters if needed), retrieves the rows as mappings of column_name=value and instanciates the objects. By the way, all the Get...() classmethods are usually one line, calling GetSQL(). And there's no way to disambiguate if you add a second column by the same name. MySQL has an easy solution : columns with the same name overwrite one another. Neat for joins on keys which have the same name, but still leaves this dropping a brick feeling. So you have some situations where you can't add an expression with the correct name without explicitly listing every other column. Well, that's what DB libraries are for, ain't they ? There may be performance implications for having more columns than necessary in a select list as well. If you just need the product name, don't retrieve the description ;) I added an additional, optional parameter to specify the columns to remove. In this case, the instance members are not created, and if you try to use them, an exception is raised... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] change natural column order
Using SELECT * FROM table_name from the PSQL prompt or any other
interactive tool is perfectly fine.
Putting SELECT * FROM table_name into a compiled program using libpq or
ESQL is a code defect. Period.
ALTER TABLE ADD COLUMN /* Most frequent defect maker for SELECT * */
ALTER TABLE DROP COLUMN /* If you didn't need the column, who cares */
ALTER TABLE RENAME COLUMN /* This will be a problem either way, but at
least you will find out about it. It also shows why renaming columns is almost
always a very, very bad idea after any release. */
ALTER TABLE SET WITHOUT OIDS {PG specific} /* One fewer column now, and
all the column numbers are now 'off-by-one' */
DROP TABLE/CREATE TABLE /* New version may have the same name and the
same number of columns, and they may even have the same data types but there is
no guarantee that the meaning is the same. */
The list goes on and on.
It is a defect of equal magnitude to assume that columns are returned in any
particular order unless specified in a column list (again, from a program and
not interactively).
Another typical defect is to assume that columns come backed ordered by the
primary key if the table is clustered on the primary key column. You can have
a page split with many database systems and so there is no guarantee that data
will be returned in order without an ORDER BY clause -- clustered or not.
Any of (ASSMUME NO COLUMN CHANGES/ASSUME COLUMN ORDER/ASSUME CLUSTERED KEY SORT
ORDER) would cause me to fail code in a code review.
IMO-YMMV
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Postgres Design
Speaking in a general sense, it's almost never a good idea to split something across databases by an arbitrary boundary such as year. It's also not a good idea to split things into multiple databases across logical boundaries unless there's a compelling reason to do so. On Tue, Nov 30, 2004 at 02:27:32PM -0500, Morris N. Grajower wrote: I have hundreds of different survey files in a customized database. I would like to convert them to postgres. My question is should I create one database for all the surveys or should I limit the number of surveys to a database. for example create a different database for each survey year, or by research company ? ---(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 -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] change natural column order
On Tue, Nov 30, 2004 at 03:03:37PM -0800, Dann Corbit wrote:
Using SELECT * FROM table_name from the PSQL prompt or any other
interactive tool is perfectly fine.
Putting SELECT * FROM table_name into a compiled program using libpq or
ESQL is a code defect. Period.
This looks like misinformation, a misunderstanding of the data
available from libpq or a misunderstanding of how any language more
sophisticated than C[1] tends to access data structures
Would you care to expand on why you think this...
my $row = $dbh-selectrow_hashref(select * from $table);
print 'foo = ', $row{'foo'};
...is inherently a a code defect?
Cheers,
Steve
[1] Not that there's anything wrong with the moral equivalent of
PQgetvalue(res, 0, PQfnumber(res, foo)) other than a slightly
clumsy syntax.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Trigger Problems
Hi , i have designed a trigger function called test2(Integer) im trying to use it with a trigger but get errors that function cannot be found Trigger statement is CREATE TRIGGER new_trigger566 BEFORE INSERT ON customer FOR EACH ROW EXECUTE PROCEDURE test2(primary); get the error test2() cannot be found ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Trigger Problems
That is probably because you created test2(INTEGER) and you're trying to call test2(TEXT). On Wed, Dec 01, 2004 at 11:22:41AM +1100, Jamie Deppeler wrote: Hi , i have designed a trigger function called test2(Integer) im trying to use it with a trigger but get errors that function cannot be found Trigger statement is CREATE TRIGGER new_trigger566 BEFORE INSERT ON customer FOR EACH ROW EXECUTE PROCEDURE test2(primary); get the error test2() cannot be found ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
From: Steve Atkins [EMAIL PROTECTED]
Would you care to expand on why you think this...
my $row = $dbh-selectrow_hashref(select * from $table);
print 'foo = ', $row{'foo'};
...is inherently a a code defect?
because it does not work ? (you mean $row-{'foo'})
sorry, could not resist :-)
gnari
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Trigger Problems
From: Jamie Deppeler [EMAIL PROTECTED] Hi , i have designed a trigger function called test2(Integer) the trigger function must be declared without arguments http://scripts.postgresql.org/docs/7.4/static/triggers.html#TRIGGER-DEFINITI ON gnari ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] change natural column order
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of gnari
Sent: Tuesday, November 30, 2004 4:48 PM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] change natural column order
From: Steve Atkins [EMAIL PROTECTED]
Would you care to expand on why you think this...
my $row = $dbh-selectrow_hashref(select * from $table);
print 'foo = ', $row{'foo'};
...is inherently a a code defect?
because it does not work ? (you mean $row-{'foo'})
sorry, could not resist :-)
There is an exception to every rule. If you need a hash for the whole
row, then you need all the columns.
And while we are at it:
$dbh-selectrow_hashref(SELECT * FROM $table LIMIT 1);
Or something along those lines would be a bit less absurd.
To do a table scan to select a single value is criminal.
SELECT * is a horrible disease, coughed up by those who do not know
what they want most of the time.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
From: Dann Corbit [EMAIL PROTECTED]
From: Steve Atkins [EMAIL PROTECTED]
Would you care to expand on why you think this...
my $row = $dbh-selectrow_hashref(select * from $table);
print 'foo = ', $row{'foo'};
...is inherently a a code defect?
There is an exception to every rule. If you need a hash for the whole
row, then you need all the columns.
I think the point was that when the language/libs allow for
easy reading of a row into a hash, it becomes the most
natural and least error prone way. If you add or rename a column,
there are fewer places in the code you need to change.
gnari
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Newbie question: returning rowtypes from a plpgsql function
I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. Am I doing something wrong or is this the expected behavior? Is there a standard way to code around this? I expected my client code to check the number of rows returned to decide what to do next. thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Using default tablespace for database?
I've searched the archives on this subject - appreciate some clarification on tablespaces defined during create database. Using 8.0beta5 on Linux, I've revoked permission to use default tablespaces and created a new default tablespace for a new database with the following: revoke create on schema public from public revoke create on tablespace pg_default from public revoke create on tablespace pg_global from public create tablespace ts location '/a/b/c/ts' create database db tablespace ts -- connect to database db as postgres create schema authorization -- Now, I connect to database db as . create table public.t1 (f1 smallint) - fails as it should create table t1 (f1 smallint) tablespace pg_default - fails as it should (1) create table t1 (f1 smallint) tablespace ts- fails - should it? (2) create table t1 (f1 smallint) - succeeds I'm unclear about the last two. Doesn't the fact that (2) succeeds imply that (1) should also succeed? The documentation says that when created without an explicit tablespace, the table is being created in the default tablespace for the database. I can't verify that because when I look in pg_class, the tablespace column is null. In psql, \d+ .t1 does not identify the tablespace. If it is not going into the ts tablespace, then where is it going, since I've prohibited it from using the default tablespaces? If I explicitly grant create on tablespace ts to , then (1) works. Even then, \d+ and pg_class still show no tablespace for this table. (1) and (2) look the same in the system catalog tables (and in the views in PgAdmin III.) -- Guy Rouillier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
[EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: Trying to sway the vote? There has been no CFV. During an RFD, he's completely entitled to try to persuade others people to vote yes or no when the time comes. I didn't say that he was not entitled. Bill, is it possible for you to drop the combative tone? Please follow your own advice, Barbara. -- Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
Marc G. Fournier From: [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: [EMAIL PROTECTED] writes: On 30 Nov 2004 22:55:00 GMT, Woodchuck Bill [EMAIL PROTECTED] wrote: Marc G. Fournier From: [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: Just as an FYI ... the latest RFD is for *one* comp.databases.postgresql group to be created, that is not-gated ... this means that those using it would not have the benefit(s) that those using the pgsql.* hierarchy do, namely access to the wealth of knowledge/experience of those on the mailing lists ... I had posed the 'who would use USENET' question on -hackers previous to the poll, and the general opinion was not in this life time by ppl like PeterE, TomL, JoshuaD, etc ... the thread can be seen: http://archives.postgresql.org/pgsql-hackers/2004-11/msg01110.php Trying to sway the vote? There has been no CFV. During an RFD, he's completely entitled to try to persuade others people to vote yes or no when the time comes. Bill, is it possible for you to drop the combative tone? It's not that helpful to constantly raise the temperature of the discussion. Actually, I didn't find Bill's comment 'combative' ... :) Nor was it intended to be that way. :-) -- Bill ---(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: [GENERAL] USENET vs Mailing Lists Poll ...
[EMAIL PROTECTED] (Joel) wrote in news:[EMAIL PROTECTED]: (crosspost added to news.groups) As long as the web page maintainers are going to the trouble of taking a survey, might I (at the risk of being tarred and feathered :-p) suggest a more thorough survey? Suggested questions: (1) If there were a USENET newsfeed, under comp.databases.postgresql.*, of one or more of the current postgresql mailing lists, I would (a) use USENET primarily, (b) use both USENET and the mailing lists, (c) use the mailing lists primarily, (d) unsubsribe from the mailing lists and use neither, or (e) not sure at this time. That is not likely to happen. The proponent has already submitted a new proposal for a single standalone comp.* group (comp.databases.postgresql), with no gating to any of the lists. (2) If there were a separate USENET comp.databases.postgresql newsgroup created, I would (a) use the separate USENET newsgroup primarily, (b) use both the separate USENET newsgroup and the mailing lists, (c) use the mailing lists primarily, (d) unsubsribe from the mailing lists and use neither, or (e) not sure at this time. (3) Concerning USENET, I would prefer (a) that the mailing lists be gated to USENET, (b) that the mailing lists and USENET be kept seperate, (c) that USENET go take a leap ;-/, or (d) not sure at this time. (4) If the mailing lists are gated to USENET, I would prefer (a) that the current SPAM moderation policy apply to both, (b) that no moderation occur on either USENET or the lists, (c) that kooks who post to USENET be tarred and feathered 8-*, or (d) not sure at this time. I like C. ;-) Please not that this is not an attempt at a survey, see 3c and 4c. It is only a suggestion. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
[EMAIL PROTECTED] writes: On 30 Nov 2004 22:55:00 GMT, Woodchuck Bill [EMAIL PROTECTED] wrote: Marc G. Fournier From: [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: Just as an FYI ... the latest RFD is for *one* comp.databases.postgresql group to be created, that is not-gated ... this means that those using it would not have the benefit(s) that those using the pgsql.* hierarchy do, namely access to the wealth of knowledge/experience of those on the mailing lists ... I had posed the 'who would use USENET' question on -hackers previous to the poll, and the general opinion was not in this life time by ppl like PeterE, TomL, JoshuaD, etc ... the thread can be seen: http://archives.postgresql.org/pgsql-hackers/2004-11/msg01110.php Trying to sway the vote? There has been no CFV. During an RFD, he's completely entitled to try to persuade others people to vote yes or no when the time comes. Bill, is it possible for you to drop the combative tone? It's not that helpful to constantly raise the temperature of the discussion. Actually, I didn't find Bill's comment 'combative' ... :) as to 'swaying the vote' ... by no means, since few on the lists would know how/where to vote in the first place ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] createlang fails w/ 'undef ref to _SPI_restore_connection' on 8b5 on OSX 10.3.6
hi all, i've a successful install of pgsql 8b5 on OSX 10.3.6. trying to install pl/pgsql: % createlang plpgsql template1 results in an immediate error: createlang: language installation failed: ERROR: could not load library /usr/local/pgsql/lib/plpgsql.so: dyld: /usr/local/pgsql/bin/postmaster Undefined symbols: /usr/local/pgsql/lib/plpgsql.so undefined reference to _SPI_restore_connection expected to be defined in the executable a similar error is generated by attempts to install pl/tcl, pl/perl or pl/python googl'ing on SPI_restore_connection results in 0 hits. nada. suggestions as to where to start? thx, richard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function
Larry White [EMAIL PROTECTED] writes: I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. How exactly would SELECT INTO return 0 rows? Perhaps the target variables vanish into a black hole? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Using default tablespace for database?
Guy Rouillier [EMAIL PROTECTED] writes: (1) create table t1 (f1 smallint) tablespace ts- fails - should it? Sure. You didn't grant any permissions on tablespace ts. (2) create table t1 (f1 smallint) - succeeds The presumption is that there should be no direct permission checks on the default tablespace for a database --- if a user has the ability to create tables in a database at all, then he's got the right to create 'em in the database's default tablespace. To do otherwise would break too many applications for too little gain. However, if you explicitly mention tablespace foo, then you'd better have permissions on foo. 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: [GENERAL] createlang fails w/ 'undef ref to _SPI_restore_connection' on 8b5 on OSX 10.3.6
OpenMacNews [EMAIL PROTECTED] writes: createlang: language installation failed: ERROR: could not load library /usr/local/pgsql/lib/plpgsql.so: dyld: /usr/local/pgsql/bin/postmaster Undefined symbols: /usr/local/pgsql/lib/plpgsql.so undefined reference to _SPI_restore_connection expected to be defined in the executable You seem to be trying to load a current plpgsql.so into a less than current backend. SPI_restore_connection() was just added a few days ago ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Upcoming Changes to News Server ...
On 11/30/2004 2:37 PM, Gary L. Burnore wrote: Perhaps I wasn't clear. I don't care WHO you are. I've already asked you once to stay out of my email. Further emails from you will be reported to both Yahoo and Comcast as harassment. I'm not on your list. _I_ am posting to a USENet discussion group. Your list is broken. Do NOT email me again. Oh my, after reading this he really caught my attention. You have to google for Gary Burnore a little. This guy has a record ... It seems to me that the whole RFD/CFV thing has attracted a bunch of net kooks and individuals who have nothing better to do than wasting other peoples time. Marc, can you add a kill line on the mail/news gateway so that messages from this guy (and as they pop up more of his kind) don't pollute our mailing lists and stay on the news side of it only? If not I will just add a /dev/null line for this idiot to my procmail config. Jan At 10:31 AM 11/30/2004, you wrote: On 11/29/2004 11:53 PM, Gary L. Burnore wrote: Stay out of my email. This ia a PostgreSQL related topic discussed on PostgreSQL mailing lists and you react like this to a mail from a PostgreSQL CORE team member? Rethink your attitude. Jan At 11:50 PM 11/29/2004, you wrote: On 11/23/2004 4:46 PM, Gary L. Burnore wrote: It's ok. Mysql's better anyway. This is the attitude I've seen from many of the pro-usenet people. If I don't get it my way I will bash your project and try to do harm. I am too one of those who have left usenet many years ago. Partly because of people with this attitude. And I don't consider it much of a loss if we lose the message to these people. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [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])
[GENERAL] Trigger problem 2
Hi Finally getting this trigger ro work have one last problem, im trying to use Trigger variables sample code UPDATE wip.resource set name = datarecord.borname where wip.resource.primary = OLD.primary; get the following error Error: record old is not yet assigned would be greatful for any help thx begin:vcard fn:Jamie Deppeler n:Deppeler;Jamie org:Once;Development adr:;;46 Roseneath Street;North Geelong;Vic;3215;Australia email;internet:[EMAIL PROTECTED] title:Database Admin tel;work:+61 3 52278 6699 url:http://www.doitonce.net.au version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] createlang fails w/ 'undef ref to
hi tom, thx for the reply =) You seem to be trying to load a current plpgsql.so into a less than current backend. SPI_restore_connection() was just added a few days ago ... just getting used to pgsql terminology, but i presume by 'backend' you simply mean version of pgsql iteslf, yes? to that end, % postmaster --version postmaster (PostgreSQL) 8.0.0beta5 % ls -alt postgresql-8.0.0beta5.tar.gz -rw-r--r-- 1 devuser wheel 13501406 Nov 30 16:50 postgresql-8.0.0beta5.tar.gz % ls -alt /usr/local/pgsql/bin/postgres -rwxr-xr-x 1 devuser wheel 10725396 Nov 30 17:41 /usr/local/pgsql/bin/postgres % ls -alt /usr/local/pgsql/lib/plpgsql.so -rwxr-xr-x 1 devuser wheel 403892 Nov 30 17:44 /usr/local/pgsql/lib/plpgsql.so % createlang --version createlang (PostgreSQL) 8.0.0beta5 then, still, % createlang plpgsql template1 Password: xx createlang: language installation failed: ERROR: could not load library /usr/local/pgsql/lib/plpgsql.so: dyld: /usr/local/pgsql/bin/postmaster Undefined symbols: /usr/local/pgsql/lib/plpgsql.so undefined reference to _SPI_restore_connection expected to be defined in the executable i _think_ i'm up to date ... thx again, richard ---(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
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
Marc G. Fournier From: [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: [EMAIL PROTECTED] (Bill Harris) writes: Marc G. Fournier [EMAIL PROTECTED] writes: If there was an official newsgroup for postgresql, would you switch to using Usenet from using the mailing lists? As a side note, for those that do vote 'yes', please note that there is an official pgsql.* hierarchy gated from the mailing lists, that is available at news.postgresql.org, if you do wish to use a news reader vs a mail reader ... FWIW, I voted yes, but my vote depended upon it being a comp.databases.postgresql.* hierarchy, done according to USENET guidelines. I sense that would be a lot more important for PostgreSQL in the long term and a lot more sustainable in general than a pgsql.* hierarchy. It's been my experience that processes done outside the norm tend to have extra problems along the way that cost more than the immediate gratification is worth, even if it does seem more painful at the time. Just as an FYI ... the latest RFD is for *one* comp.databases.postgresql group to be created, that is not-gated ... this means that those using it would not have the benefit(s) that those using the pgsql.* hierarchy do, namely access to the wealth of knowledge/experience of those on the mailing lists ... I had posed the 'who would use USENET' question on -hackers previous to the poll, and the general opinion was not in this life time by ppl like PeterE, TomL, JoshuaD, etc ... the thread can be seen: http://archives.postgresql.org/pgsql-hackers/2004-11/msg01110.php Trying to sway the vote? -- Bill ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
Woodchuck Bill wrote: ppl like PeterE, TomL, JoshuaD, etc ... the thread can be seen: http://archives.postgresql.org/pgsql-hackers/2004-11/msg01110.php Trying to sway the vote? Well, you have to admit that for _developers_, email is probably better. But remember developers are probably less than 1% of all PostgreSQL users. PostgreSQL is very popular, and is most likely among the most widely used BSD licensed projects. Ultimately, the RFD is about providing a place for _Usenet_ PostgreSQL users who have been neglected for quite some time. With the ease of posting to the big 8 group, and the very large propegation, I can see why the comp.databases.postgresql group will be very popular. I originally tried to include the developers so they could follow the comp PostgreSQL group through their mailing list, but that proved too technically difficult. If they want to follow what will be a huge PostgreSQL usenet community in the big 8, they will have to subscribe to comp.databases.postgresql. :-) ---(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
Re: [GENERAL] Upcoming Changes to News Server ...
On Tue, 30 Nov 2004, Jan Wieck wrote: On 11/30/2004 2:37 PM, Gary L. Burnore wrote: Perhaps I wasn't clear. I don't care WHO you are. I've already asked you once to stay out of my email. Further emails from you will be reported to both Yahoo and Comcast as harassment. I'm not on your list. _I_ am posting to a USENet discussion group. Your list is broken. Do NOT email me again. Oh my, after reading this he really caught my attention. You have to google for Gary Burnore a little. This guy has a record ... It seems to me that the whole RFD/CFV thing has attracted a bunch of net kooks and individuals who have nothing better to do than wasting other peoples time. Marc, can you add a kill line on the mail/news gateway so that messages from this guy (and as they pop up more of his kind) don't pollute our mailing lists and stay on the news side of it only? If not I will just add a /dev/null line for this idiot to my procmail config. Done :) And he's pretty much considered a net.kook on news.groups itself too ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] createlang fails w/ 'undef ref to _SPI_restore_connection' on 8b5 on OSX 10.3.6
OpenMacNews [EMAIL PROTECTED] writes: i _think_ i'm up to date ... Maybe you didn't restart your beta4-or-older postmaster after updating? SPI_restore_connection definitely exists in the beta5 sources ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Upcoming Changes to News Server ...
Jan Wieck [EMAIL PROTECTED] writes: Oh my, after reading this he really caught my attention. You have to google for Gary Burnore a little. This guy has a record ... gburnore was known far and wide as a net.asshole when I dropped out of Usenet, lo these many years ago. Doesn't look like he's acquired any social skills since then :-( regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] change natural column order
Dann Corbit [EMAIL PROTECTED] writes: Putting SELECT * FROM table_name into a compiled program using libpq or ESQL is a code defect. Period. ALTER TABLE ADD COLUMN /* Most frequent defect maker for SELECT * */ Whatever are you talking about? I've never tried ESQL precisely because it seems a terribly old-fashioned interface. Does it not support referring to columns by name? Even in libpq you can use PQfnumber or load all the columns found with PQfname into an associative array like higher level language drivers typically do automatically. DROP TABLE/CREATE TABLE /* New version may have the same name and the same number of columns, and they may even have the same data types but there is no guarantee that the meaning is the same. */ Um, well, there was no guarantee the meaning was the intended meaning in the first place except for your own interface documentation. Obviously if you replace the table with a new one you're either maintaining compatible semantics or else you're changing the interface and it will require some code changes. It is a defect of equal magnitude to assume that columns are returned in any particular order unless specified in a column list (again, from a program and not interactively). Actually the spec does guarantee that the columns have a fixed defined ordering. However I would agree it would be a poor design to depend on that ordering since it's not self-documenting and requires close synchronization between far distant pieces of code. But then I think it's bad to depend on ordering even when it is an explicitly listed column list because it requires close synchronization between two pieces of code even if they aren't too far distant. I prefer referring to columns by name in all but the simplest cases because it means the only synchronization is the presence or lack of a column, not the precise position in the output list. I'm free to add columns to a select list in the logical position without having to make adjustments elsewhere in the code. Another typical defect is to assume that columns come backed ordered by the primary key if the table is clustered on the primary key column. You can have a page split with many database systems and so there is no guarantee that data will be returned in order without an ORDER BY clause -- clustered or not. You're confusing columns with rows. Without an ORDER BY clause there's no guarantee of the order of the rows. Not because of page splits or any other specific technical reason. There just isn't. There are any number of reasons the database might choose another ordering to return rows. In the case of Postgres clustering doesn't really work anyways so *any* sequential table scan without an explicit ORDER BY will be out of order unless you haven't made any data changes since the clustering. Any of (ASSUME NO COLUMN CHANGES/ASSUME COLUMN ORDER/ASSUME CLUSTERED KEY SORT ORDER) would cause me to fail code in a code review. IMO-YMMV Well with Postgres and reasonably modern drivers none of the above impact at all on whether SELECT * is a good idea or not. Like I said in my post. Because of historical problems with various other databases the rule of thumb that SELECT * is bad has become entrenched in standard DBA dogma. One of those historical problems is that some interfaces depend on column ordering in result sets and have particular problems dealing when the columns change in any way. As you point out this can happen for lots of reasons, not just because SELECT * is used. Thankfully nowadays we get to use much more flexible interfaces that find columns based on column names that don't suffer from these problems. -- greg ---(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: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
On 11/30/2004 5:55 PM, Woodchuck Bill wrote: Marc G. Fournier From: [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: [EMAIL PROTECTED] (Bill Harris) writes: Marc G. Fournier [EMAIL PROTECTED] writes: If there was an official newsgroup for postgresql, would you switch to using Usenet from using the mailing lists? As a side note, for those that do vote 'yes', please note that there is an official pgsql.* hierarchy gated from the mailing lists, that is available at news.postgresql.org, if you do wish to use a news reader vs a mail reader ... FWIW, I voted yes, but my vote depended upon it being a comp.databases.postgresql.* hierarchy, done according to USENET guidelines. I sense that would be a lot more important for PostgreSQL in the long term and a lot more sustainable in general than a pgsql.* hierarchy. It's been my experience that processes done outside the norm tend to have extra problems along the way that cost more than the immediate gratification is worth, even if it does seem more painful at the time. Just as an FYI ... the latest RFD is for *one* comp.databases.postgresql group to be created, that is not-gated ... this means that those using it would not have the benefit(s) that those using the pgsql.* hierarchy do, namely access to the wealth of knowledge/experience of those on the mailing lists ... Which is all the contributing developers, all the key people in the project. So that newsgroup whould be for whom? I had posed the 'who would use USENET' question on -hackers previous to the poll, and the general opinion was not in this life time by ppl like PeterE, TomL, JoshuaD, etc ... the thread can be seen: http://archives.postgresql.org/pgsql-hackers/2004-11/msg01110.php Trying to sway the vote? Perhaps. The long term solution for this incompatibility seems clear to me. Set it up as a moderated newsgroups under pgsql.* and have the moderator bot respond with a fixed if you want your message to be read by all PostgreSQL community members, you must post to the underlying mailing list ... with a reference how to do the nomail subscribe etc. and the gateway setting Follow-Up-To: and so on so that news-lurkers usually mail it to the list server anyway. Everything else will lead to constant work on Marc's side, delayed or double posts, all the crap people have been complaining about. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] pgEdit 1.0b4
The next beta version of pgEdit is available for evaluation. This version includes a new tab completion feature based on the implementation in psql. In addition, there are 14 other completion commands where you can request a specific type of completion (e.g. table, column, function). As with all pgEdit commands, the completion commands can be bound to any key sequence of your choosing. Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
[EMAIL PROTECTED] writes: On 30 Nov 2004 22:55:00 GMT, Woodchuck Bill [EMAIL PROTECTED] wrote: Trying to sway the vote? There has been no CFV. During an RFD, he's completely entitled to try to persuade others people to vote yes or no when the time comes. Both of you are under the illusion that this was a Usenet discussion. Marc was asking the members of *a mailing list* whether they'd consider moving over to a Usenet group as a substitute. By my count the vote so far was 99% no way, so you should stop supposing that the core list membership cares about Usenet. We could care less, and the more we hear from the likes of gburnore the more we are inclined to install a solid firewall between us and you. There are however a fair number of people who prefer to use newsreader interfaces to read the PG discussions, and for their sakes I'd like to find an amicable solution. As someone who retired from newsadmin'ing a dozen years ago, I'm not about to defend the rogue comp.databases.postgresql groups --- that was poorly done from the start. But can't we fix it and move on? Bill, is it possible for you to drop the combative tone? It's not that helpful to constantly raise the temperature of the discussion. Indeed. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Using default tablespace for database?
Tom Lane wrote: The presumption is that there should be no direct permission checks on the default tablespace for a database --- if a user has the ability to create tables in a database at all, then he's got the right to create 'em in the database's default tablespace. To do otherwise would break too many applications for too little gain. However, if you explicitly mention tablespace foo, then you'd better have permissions on foo. Tom, thank you for the reply. I understand what you are saying, and now that I understand the rules I can work within them. This does seem logically inconsistent, though. That I can create a table in the database's default tablespace if I don't specify it demonstrates that I have permission to do; this permission has been implicitly granted to all users of the database. That implicit permission doesn't disappear because I mention the same tablespace explicitly. I suppose the safest thing to do is to grant create on the tablespace to all users of the DB, so that their creates will always work if they mention the tablespace or not. Any idea why the tablespace name does not appear to be associated with the table in the system catalog? Perhaps the tablespace name is not recorded if it is the default TS? -- Guy Rouillier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] createlang fails w/ 'undef ref to
Maybe you didn't restart your beta4-or-older postmaster after updating? SPI_restore_connection definitely exists in the beta5 sources ... all ok now. restarting wasn't doing the trick ... same errors. still able to access/read/write to my db's but nada else. but, looking in my logs i noted a slew of: FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with CATALOG_VERSION_NO 200410111, but the server was compiled with CATALOG_VERSION_NO 200411041. HINT: It looks like you need to initdb. after re-initdb'ing and restarting, all's ok with 'createlang' ... inasmuch as i get no error no log entries ... for all langs. i didna realize (and apparently missed in RTFM'ing) that initdb was necessary from beta-to-beta ... s, as usual, i created my own problem :S odd though that i was getting fatal errors, but still able to launch the db ... thanks for your patience help! cheers, richard ---(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
[GENERAL] granting privileges
How to grant privileges to all objects( tables,sequences etc) in specific schema, I mean I want to give users select,insert,delete and update to all objects in the schema in one sql statement.I know Grant select,insert,update,delete on table_name to user_name. But this is for individual table .
Re: [GENERAL] [ANNOUNCE] USENET vs Mailing Lists Poll ...
On 11/30/2004 5:27 PM, Mike Cox wrote: Ultimately, the RFD is about providing a place for _Usenet_ PostgreSQL users who have been neglected for quite some time. With the ease of posting to the big 8 group, and the very large propegation, I can see why the comp.databases.postgresql group will be very popular. I originally tried to include the developers so they could follow the comp PostgreSQL group through their mailing list, but that proved too technically difficult. If they want to follow what will be a huge PostgreSQL usenet community in the big 8, they will have to subscribe to comp.databases.postgresql. :-) Mike, I do recognize your honesty and good intentions. You originally tried to scratch an itch of many people. That is, that the PostgreSQL newsgroups were not carried by their NSP. In doing so, you have opened a can of worms (happens). As usual, a once opened can of worms can only be re-canned by using a bigger can. If you think that telling 99% of the knowledge on these mailing lists that they are only 1% of the users and that a huge PostgreSQL usenet community will discuss a lot of interesting stuff aside of them will change much, you're wrong ;-) I have been contributing to things via USENET and whatnot for over 15 years and all I know is that people either make the mistake to abandon a good open source product (and pay bucks to some greedy company instead) or they find the way to the forum, where the real knowledge is answering, and stop reading the unproductive mailing lists or newsgroups at all. Many of the PostgreSQL contributors are like me - long standing open source developers, contributors, people who left USENET behind years ago and who know that for them nothing will change as long as they don't unsubscribe from the mailing lists, no matter what happens on a newsgroup. The committed users will follow where we go and the professional users are there already, waiting for us. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] change natural column order
Regarding the Natural Order of columns. Why if we can delete a column from the middle of a table now, change the type of that column to something totally different, eg text - int. Can we not move the order of the rows around, and when the new row is written to disk in the new arrangement. Or more accurately, why is it not possible to add a new column, not at the end of the list. It's probably more complicated that I think, as that's usually the case. We don't need logical and physical mapping, probably just the ability to insert a column not on the end. Sorry if this comment is in the wrong place, I've been following the General and Hackers discussions and decided to post now after deleting the other posts. Regards Russell Smith. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Using default tablespace for database?
Guy Rouillier [EMAIL PROTECTED] writes: ... This does seem logically inconsistent, though. That I can create a table in the database's default tablespace if I don't specify it demonstrates that I have permission to do; this permission has been implicitly granted to all users of the database. That implicit permission doesn't disappear because I mention the same tablespace explicitly. I'm not convinced. I think that CREATE TABLE foo (...); means create my table in whatever tablespace my database likes, while CREATE TABLE foo (...) TABLESPACE bar; means create my table in tablespace bar. It might happen that bar is the same tablespace as foo's default, but that doesn't make the cases equivalent; in the latter case I'm asserting that I have the right to control the tablespace selection, whereas in the former I'm not. So in the latter case I should need the permissions to make that assertion, in the former case not. There isn't a whole lot of practical difference right at the moment, but let's suppose that in a release or two someone writes an ALTER DATABASE SET TABLESPACE command. My expectation would be that a table declared the first way would move to the new database-default tablespace, but a table declared the second way would stay right in tablespace bar. That's why you need some explicit permissions to say the latter. Any idea why the tablespace name does not appear to be associated with the table in the system catalog? Right at the moment we don't have a way to distinguish this table is in the database's default tablespace from this table is in tablespace foo that just happens to be the same as the database's default tablespace. But IMHO there is a semantic difference there; it's only an implementation glitch that we can't enforce the difference yet. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] change natural column order
Hi, Am Mittwoch, den 01.12.2004, 16:46 +1100 schrieb Russell Smith: Regarding the Natural Order of columns. Why if we can delete a column from the middle of a table now, change the type of that column to something totally different, eg text - int. Can we not move the order of the rows around, and when the new row is written to disk in the new arrangement. Or more accurately, why is it not possible to add a new column, not at the end of the list. It's probably more complicated that I think, as that's usually the case. How do you select middle of a table ? All I know is how to refer to columns by name. And for that it is unimportant in which order they appear in SELECT * In fact its not recommendet to use SELECT * in production code. And again, SELECTS on one table only are very rare in most projects I've seen - so why pull any extra column you arent going to use in an app over the wire? There are edge cases when you want to write a generic database tool where you just display data as it is, but then you could easily maintain your own order of columns in a table. Usually a table even has some columns with keys, would you show that to a user? And if so, why? Some even dont use a single table here because they dont feel comfortable let the average user fiddle with the schema. So whats the point to call for that cosmetic feature again and again? Just my 2ct Tino ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [HACKERS] Adding Reply-To: listname to Lists configuration ...
On Mon, Nov 29, 2004 at 12:49:46 +, Chris Green [EMAIL PROTECTED] wrote: This is a perpetual problem, if people all used the same MUA and (assuming it has the capability) all used the 'reply to list' command to reply to the list everything would be wonderful! :-) I think using mail-followup-to is better than having people do reply to list. I think the main benefit to having reply-to point to the list is for supporting clueless users on lists (who don't seem to understand the difference between reply to sender and reply to all) and I don't think we have too many of those here. When I am subscribed to lists that force reply-to to point to the list, I have my mail filter remove those headers so that things will work normally (other than not allowing a sender to use reply-to of their own). Reply-to would be especially bad for the postgres lists as nonsubscribers can post and that the list servers are often slow. People who don't want separate copies of messages should set the mail-followup-to header to indicate that preference. This isn't perfect since not all mail clients support this and some set up is required to make your client aware of the list. It is also possible for mailing list software to handle this preference for you (by not sending copies to addresses on the list that appear in the recipient headers), but I don't know if the software in use has that capability. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
