Re: [GENERAL] why ORDER BY works wrong in pg7.3.4?
Thank u for hint it really helped me - I made initdb with new lc-ctype and lc-collate settings : ru_RU:UTF8. But im still having one question - some functions - lower() and upper() doesnt work properly . what im doing wrong? Tom Lane wrote: igor [EMAIL PROTECTED] writes: why order by and like doesnt work in 7.3.4 properly? - im working with ALT server local settings. in 7.2.xx all this things worked well . Most likely you forgot to select the right locale settings before initdb'ing the 7.3 installation. pg_controldata can help you check what locale is in force in the database. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Declaring empty, non-NULL array in plpgsql
CSN [EMAIL PROTECTED] writes: Is there a way to declare an array in plpgsql so that it's empty (not NULL)? Sure. An empty-array literal is '{}', so: regression=# create function foo(int) returns varchar[] as ' regression'# declare regression'# arr varchar[] := ''{}''; regression'# begin regression'# for i in 1..$1 loop regression'# arr = array_append(arr, i::varchar); regression'# end loop; regression'# return arr; regression'# end' language plpgsql; CREATE FUNCTION regression=# select foo(10); foo {1,2,3,4,5,6,7,8,9,10} (1 row) 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])
[GENERAL] autocommit in 7.4
Hi, thanks for the new great version! Only one small problem, how can I disable autocommit in Postgres 7.4 in libpgtcl and psql? I found something about .psqlrc but how is the syntax and how can I set it for libpgtcl? BTW, SET AUTOCOMMIT TO ON still gives no error, only SET AUTOCOMMIT TO OFF says ERROR: SET AUTOCOMMIT TO OFF is no longer supported. Thanks and best regards, Christian ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Lock strategies!
Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+-- id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Ok! The forn_id is supposed to be sequencial and without holes (if someone perform a DELETE or UPDATE, so there will be a hole... no problem if the hole happens in this case!). Well, to know the next value of the forn_id column, it was planned to be done like this: teste=# INSERT INTO forn (forn_id,descrip) VALUES ((SELECT max(forn_id) FROM forn),'descrip1'); It will cause a huge delay in case this table became huge, because the forn_id isn't an indexed column (but I would index it! The problem I am talking about is ONLY about the sequence of numbers). As a way to be sure it will not another other client getting the exact value as the max(forn_id), there was a dirty thing: teste=# BEGIN; teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE; teste=# INSERT INTO ... teste=# COMMIT; Well, I really think it is not the best way to do that and I am asking you for advices! 1) Is it (... max(forn_id)... ) the best way to get the next value to be inserted in the table? 2) Is there a automatic way to do that? Thanks in advance and Best Regards, Marcelo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Lock strategies!
I think that defining forn_id as serial is what you are looking for. This will handle the assignment of unique numbers to the id for you (it creates a sequence table). The locking stategy is fraught with danger... and unnecessary. Marc A. Leith redboxdata inc. E-mail:[EMAIL PROTECTED] Quoting MaRcElO PeReIrA [EMAIL PROTECTED]: Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+-- id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Ok! The forn_id is supposed to be sequencial and without holes (if someone perform a DELETE or UPDATE, so there will be a hole... no problem if the hole happens in this case!). Well, to know the next value of the forn_id column, it was planned to be done like this: teste=# INSERT INTO forn (forn_id,descrip) VALUES ((SELECT max(forn_id) FROM forn),'descrip1'); It will cause a huge delay in case this table became huge, because the forn_id isn't an indexed column (but I would index it! The problem I am talking about is ONLY about the sequence of numbers). As a way to be sure it will not another other client getting the exact value as the max(forn_id), there was a dirty thing: teste=# BEGIN; teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE; teste=# INSERT INTO ... teste=# COMMIT; Well, I really think it is not the best way to do that and I am asking you for advices! 1) Is it (... max(forn_id)... ) the best way to get the next value to be inserted in the table? 2) Is there a automatic way to do that? Thanks in advance and Best Regards, Marcelo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Lock strategies!
Dave, I actually use just the sequence, as you wrote! The biggest problem it that I *can't* have holes in that column, so it was because I used id (serial) and forn_id (integer). All other tables use only the sequence by itself, but this one, especially, CAN'T have holes! It is the problem!!! ;-) So, if I rollback or whatever, the ID will be populated with the sequence values, but the forn_id must increase in a controled way, ie, without holes! Advices?? Regards! Marcelo --- Dave Cramer [EMAIL PROTECTED] escreveu: Marceio The sequence logic takes care of it. try it yourself open two connections with psql on one do a begin; insert into table select curval('forn_id_seq'); on the other do a begin insert into table select curval('forn_id_seq'); You will see that they both increment the sequence number you will also see how to get the current value as well. Note, no locking is actually required, you can do this without the transaction stuff, it is there just so you can see it in two sessions at the same time. Also note that a rollback will NOT roll back the sequence number, this will end up with holes but sequences are not guaranteed to not have holes. Why do you have two columns, id, and forn_id, you only need one. and then do an insert into forn (descrip) values ( 'some description' ); then select curval('forn_id_seq'); forn_id will be populated for you with the value from curval. Dave On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote: Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+-- id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Ok! The forn_id is supposed to be sequencial and without holes (if someone perform a DELETE or UPDATE, so there will be a hole... no problem if the hole happens in this case!). Well, to know the next value of the forn_id column, it was planned to be done like this: teste=# INSERT INTO forn (forn_id,descrip) VALUES ((SELECT max(forn_id) FROM forn),'descrip1'); It will cause a huge delay in case this table became huge, because the forn_id isn't an indexed column (but I would index it! The problem I am talking about is ONLY about the sequence of numbers). As a way to be sure it will not another other client getting the exact value as the max(forn_id), there was a dirty thing: teste=# BEGIN; teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE; teste=# INSERT INTO ... teste=# COMMIT; Well, I really think it is not the best way to do that and I am asking you for advices! 1) Is it (... max(forn_id)... ) the best way to get the next value to be inserted in the table? 2) Is there a automatic way to do that? Thanks in advance and Best Regards, Marcelo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(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] Lock strategies!
Marceio The sequence logic takes care of it. try it yourself open two connections with psql on one do a begin; insert into table select curval('forn_id_seq'); on the other do a begin insert into table select curval('forn_id_seq'); You will see that they both increment the sequence number you will also see how to get the current value as well. Note, no locking is actually required, you can do this without the transaction stuff, it is there just so you can see it in two sessions at the same time. Also note that a rollback will NOT roll back the sequence number, this will end up with holes but sequences are not guaranteed to not have holes. Why do you have two columns, id, and forn_id, you only need one. and then do an insert into forn (descrip) values ( 'some description' ); then select curval('forn_id_seq'); forn_id will be populated for you with the value from curval. Dave On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote: Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+-- id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Ok! The forn_id is supposed to be sequencial and without holes (if someone perform a DELETE or UPDATE, so there will be a hole... no problem if the hole happens in this case!). Well, to know the next value of the forn_id column, it was planned to be done like this: teste=# INSERT INTO forn (forn_id,descrip) VALUES ((SELECT max(forn_id) FROM forn),'descrip1'); It will cause a huge delay in case this table became huge, because the forn_id isn't an indexed column (but I would index it! The problem I am talking about is ONLY about the sequence of numbers). As a way to be sure it will not another other client getting the exact value as the max(forn_id), there was a dirty thing: teste=# BEGIN; teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE; teste=# INSERT INTO ... teste=# COMMIT; Well, I really think it is not the best way to do that and I am asking you for advices! 1) Is it (... max(forn_id)... ) the best way to get the next value to be inserted in the table? 2) Is there a automatic way to do that? Thanks in advance and Best Regards, Marcelo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] building 7.4 with plperl
Gianni Mariani writes: The 7.4 version of plperl.c (with some error handling API calls commented out) compiles fine in the 7.3.4 tree. (Same machine - same install of perl !) Points to using some alternate perl API probably by macro collision ? /* Define to 1 to build client libraries as thread-safe code. (--enable-thread-safety) */ #define USE_THREADS 1 So this seems to be the collision. Fixed in 7.4 branch and current. -- Peter Eisentraut [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] Lock strategies!
On Mon, 24 Nov 2003 10:20:07 -0300 (ART) MaRcElO PeReIrA [EMAIL PROTECTED] wrote: Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+- - id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Why not make forn_id a sequence as well? then you simply call nextval('forn_id_seq') -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(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] rounding timestamps
Joe Conway wrote: Is this what you wanted? regression=# select to_char(timestamp(0) '2003-10-24 15:30:59.999','MMDDHH24MISS'); to_char 20031024153100 (1 row) Yes! Exactly! See: http://www.postgresql.org/docs/current/static/datatype-datetime.html Shame on me. I've must read that page more times than I can remember. I never realized that I could use the precision qualifier to do a cast (and round): comp_20031117= create table ts (ts timestamp without time zone); CREATE TABLE comp_20031117= insert into ts values ('2003-10-24 15:30:59.999'); INSERT 406299 1 comp_20031117= select * from ts; ts - 2003-10-24 15:30:59.999 (1 row) comp_20031117= select to_char (ts ::timestamp(0), 'MMDDHH24MISS') from ts; to_char 20031024153100 (1 row) thank you very much Joe cl. PS. Alvaro, your solution was what I was implementing already, but yes it's ugly, that's why I gave it a second round. Thanks anyway. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] why ORDER BY works wrong in pg7.3.4?
igor [EMAIL PROTECTED] writes: Thank u for hint it really helped me - I made initdb with new lc-ctype and lc-collate settings : ru_RU:UTF8. But im still having one question - some functions - lower() and upper() doesnt work properly . what im doing wrong? We don't have any support for upper() and lower() in multibyte character encodings, such as UTF8. You need to use a single-byte encoding (and a locale setting to match, of course). Yeah, I know this sucks :-(. I think Peter Eisentraut is looking into fixing it for 7.5. 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] Export CSV from psql
--- Craig O'Shannessy [EMAIL PROTECTED] wrote: As it's very unlikely that there are tab's in your strings, tab makes a much safer field separator. Probably, but I wouldn't bet the farm on it. I have found plenty of instances of unexpected tabs, especially with data generated from character-based apps, where users tend to forget that they can't tab from one field to another... There is no substitute for checking. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] SQL text of view
Hi. Where is the SQL text of view stored in the database? I mean the text like SELECT the_field FROM the_table. I can found the function's text in pg_proc.prosrc, but can't find the same for a view. Yury Shvetsov. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Lock strategies!
Marcelo, You are asking for the impossible. In order for sequences to work reliably they have to exist outside of a transaction, and be atomic. If two transactions asked for a sequence simultaneously, what number would you give them? If the first one gets 1, and the second gets 2 how do you roll back the first one and then give the second one 1? And it gets worse, what happens if 10 connections ask for one simultaneously and then connection 3 7 rollback? I don't know how to say this gently, but usually this requirement suggests that more thinking is required on the application end. Dave On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote: Dave, I actually use just the sequence, as you wrote! The biggest problem it that I *can't* have holes in that column, so it was because I used id (serial) and forn_id (integer). All other tables use only the sequence by itself, but this one, especially, CAN'T have holes! It is the problem!!! ;-) So, if I rollback or whatever, the ID will be populated with the sequence values, but the forn_id must increase in a controled way, ie, without holes! Advices?? Regards! Marcelo --- Dave Cramer [EMAIL PROTECTED] escreveu: Marceio The sequence logic takes care of it. try it yourself open two connections with psql on one do a begin; insert into table select curval('forn_id_seq'); on the other do a begin insert into table select curval('forn_id_seq'); You will see that they both increment the sequence number you will also see how to get the current value as well. Note, no locking is actually required, you can do this without the transaction stuff, it is there just so you can see it in two sessions at the same time. Also note that a rollback will NOT roll back the sequence number, this will end up with holes but sequences are not guaranteed to not have holes. Why do you have two columns, id, and forn_id, you only need one. and then do an insert into forn (descrip) values ( 'some description' ); then select curval('forn_id_seq'); forn_id will be populated for you with the value from curval. Dave On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote: Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+-- id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Ok! The forn_id is supposed to be sequencial and without holes (if someone perform a DELETE or UPDATE, so there will be a hole... no problem if the hole happens in this case!). Well, to know the next value of the forn_id column, it was planned to be done like this: teste=# INSERT INTO forn (forn_id,descrip) VALUES ((SELECT max(forn_id) FROM forn),'descrip1'); It will cause a huge delay in case this table became huge, because the forn_id isn't an indexed column (but I would index it! The problem I am talking about is ONLY about the sequence of numbers). As a way to be sure it will not another other client getting the exact value as the max(forn_id), there was a dirty thing: teste=# BEGIN; teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE; teste=# INSERT INTO ... teste=# COMMIT; Well, I really think it is not the best way to do that and I am asking you for advices! 1) Is it (... max(forn_id)... ) the best way to get the next value to be inserted in the table? 2) Is there a automatic way to do that? Thanks in advance and Best Regards, Marcelo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Lock strategies!
Hi Dave, Marc and all others, I know it is really weird! But, how can I explain to the user, who use the sequence numbers, that he will have to handle with those holes? Ok! I will try to handle the holes! (fight against the users) Thanks! Marcelo --- Dave Cramer [EMAIL PROTECTED] escreveu: Marcelo, You are asking for the impossible. In order for sequences to work reliably they have to exist outside of a transaction, and be atomic. If two transactions asked for a sequence simultaneously, what number would you give them? If the first one gets 1, and the second gets 2 how do you roll back the first one and then give the second one 1? And it gets worse, what happens if 10 connections ask for one simultaneously and then connection 3 7 rollback? I don't know how to say this gently, but usually this requirement suggests that more thinking is required on the application end. Dave On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote: Dave, I actually use just the sequence, as you wrote! The biggest problem it that I *can't* have holes in that column, so it was because I used id (serial) and forn_id (integer). All other tables use only the sequence by itself, but this one, especially, CAN'T have holes! It is the problem!!! ;-) So, if I rollback or whatever, the ID will be populated with the sequence values, but the forn_id must increase in a controled way, ie, without holes! Advices?? Regards! Marcelo --- Dave Cramer [EMAIL PROTECTED] escreveu: Marceio The sequence logic takes care of it. try it yourself open two connections with psql on one do a begin; insert into table select curval('forn_id_seq'); on the other do a begin insert into table select curval('forn_id_seq'); You will see that they both increment the sequence number you will also see how to get the current value as well. Note, no locking is actually required, you can do this without the transaction stuff, it is there just so you can see it in two sessions at the same time. Also note that a rollback will NOT roll back the sequence number, this will end up with holes but sequences are not guaranteed to not have holes. Why do you have two columns, id, and forn_id, you only need one. and then do an insert into forn (descrip) values ( 'some description' ); then select curval('forn_id_seq'); forn_id will be populated for you with the value from curval. Dave On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote: Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+-- id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Ok! The forn_id is supposed to be sequencial and without holes (if someone perform a DELETE or UPDATE, so there will be a hole... no problem if the hole happens in this case!). Well, to know the next value of the forn_id column, it was planned to be done like this: teste=# INSERT INTO forn (forn_id,descrip) VALUES ((SELECT max(forn_id) FROM forn),'descrip1'); It will cause a huge delay in case this table became huge, because the forn_id isn't an indexed column (but I would index it! The problem I am talking about is ONLY about the sequence of numbers). As a way to be sure it will not another other client getting the exact value as the max(forn_id), there was a dirty thing: teste=# BEGIN; teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE; teste=# INSERT INTO ... teste=# COMMIT; Well, I really think it is not the best way to do that and I am asking you for advices! 1) Is it (... max(forn_id)... ) the best way to get the next value to be inserted in the table? 2) Is there a automatic way to do that? Thanks in advance and Best Regards, Marcelo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br === message truncated === Yahoo! Mail - 6MB, anti-spam e antivírus gratuito. Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Lock strategies!
On Mon, 24 Nov 2003 12:48:26 -0300 (ART) MaRcElO PeReIrA [EMAIL PROTECTED] wrote: The biggest problem it that I *can't* have holes in that column, so it was because I used id (serial) and forn_id (integer). Well, if you cannot use a sequence you will have no choice but to use locking. don't use max - it isn't fast on PG use select forn_id from thetable order by fornid desc limit 1. You'll need an index on forn_id or performance will suffer. -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(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] updated linux startup script?
On Saturday 22 November 2003 06:36 pm, CSN wrote: I did initdb -D /var/lib/pgsql/data. I downloaded the attached script from this message - http://archives.postgresql.org/pgsql-general/2003-11/msg01258.php - and will give that a try. (I was getting the same old version errors as well.) I have installed, started, and restarted successfully with the 0.2PGDG set on Fedora. It isn't breaking for me as yet. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Lock strategies!
Perhaps the primary key should be a sequence/serial, but also have a secondary key which is assigned after commit. You could have a process that continually ran something like: select max(skey) from the_table; select pkey from the_table where skey is null; Then loop through the answers and assign sequenctial values. As long as this is the only process that is allowed to update skey, it should work. Jon On 24 Nov 2003, Dave Cramer wrote: Marcelo, You are asking for the impossible. In order for sequences to work reliably they have to exist outside of a transaction, and be atomic. If two transactions asked for a sequence simultaneously, what number would you give them? If the first one gets 1, and the second gets 2 how do you roll back the first one and then give the second one 1? And it gets worse, what happens if 10 connections ask for one simultaneously and then connection 3 7 rollback? I don't know how to say this gently, but usually this requirement suggests that more thinking is required on the application end. Dave On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote: Dave, I actually use just the sequence, as you wrote! The biggest problem it that I *can't* have holes in that column, so it was because I used id (serial) and forn_id (integer). All other tables use only the sequence by itself, but this one, especially, CAN'T have holes! It is the problem!!! ;-) So, if I rollback or whatever, the ID will be populated with the sequence values, but the forn_id must increase in a controled way, ie, without holes! Advices?? Regards! Marcelo --- Dave Cramer [EMAIL PROTECTED] escreveu: Marceio The sequence logic takes care of it. try it yourself open two connections with psql on one do a begin; insert into table select curval('forn_id_seq'); on the other do a begin insert into table select curval('forn_id_seq'); You will see that they both increment the sequence number you will also see how to get the current value as well. Note, no locking is actually required, you can do this without the transaction stuff, it is there just so you can see it in two sessions at the same time. Also note that a rollback will NOT roll back the sequence number, this will end up with holes but sequences are not guaranteed to not have holes. Why do you have two columns, id, and forn_id, you only need one. and then do an insert into forn (descrip) values ( 'some description' ); then select curval('forn_id_seq'); forn_id will be populated for you with the value from curval. Dave On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote: Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+-- id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Ok! The forn_id is supposed to be sequencial and without holes (if someone perform a DELETE or UPDATE, so there will be a hole... no problem if the hole happens in this case!). Well, to know the next value of the forn_id column, it was planned to be done like this: teste=# INSERT INTO forn (forn_id,descrip) VALUES ((SELECT max(forn_id) FROM forn),'descrip1'); It will cause a huge delay in case this table became huge, because the forn_id isn't an indexed column (but I would index it! The problem I am talking about is ONLY about the sequence of numbers). As a way to be sure it will not another other client getting the exact value as the max(forn_id), there was a dirty thing: teste=# BEGIN; teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE; teste=# INSERT INTO ... teste=# COMMIT; Well, I really think it is not the best way to do that and I am asking you for advices! 1) Is it (... max(forn_id)... ) the best way to get the next value to be inserted in the table? 2) Is there a automatic way to do that? Thanks in advance and Best Regards, Marcelo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 4: Don't
Re: [GENERAL] Lock strategies!
But, how can I explain to the user, who use the sequence numbers, that he will have to handle with those holes? If it's just hte user, you might try to make sure that there are ALWAYS holes, so he doesn't get confused. Jon Ok! I will try to handle the holes! (fight against the users) Thanks! Marcelo --- Dave Cramer [EMAIL PROTECTED] escreveu: Marcelo, You are asking for the impossible. In order for sequences to work reliably they have to exist outside of a transaction, and be atomic. If two transactions asked for a sequence simultaneously, what number would you give them? If the first one gets 1, and the second gets 2 how do you roll back the first one and then give the second one 1? And it gets worse, what happens if 10 connections ask for one simultaneously and then connection 3 7 rollback? I don't know how to say this gently, but usually this requirement suggests that more thinking is required on the application end. Dave On Mon, 2003-11-24 at 10:48, MaRcElO PeReIrA wrote: Dave, I actually use just the sequence, as you wrote! The biggest problem it that I *can't* have holes in that column, so it was because I used id (serial) and forn_id (integer). All other tables use only the sequence by itself, but this one, especially, CAN'T have holes! It is the problem!!! ;-) So, if I rollback or whatever, the ID will be populated with the sequence values, but the forn_id must increase in a controled way, ie, without holes! Advices?? Regards! Marcelo --- Dave Cramer [EMAIL PROTECTED] escreveu: Marceio The sequence logic takes care of it. try it yourself open two connections with psql on one do a begin; insert into table select curval('forn_id_seq'); on the other do a begin insert into table select curval('forn_id_seq'); You will see that they both increment the sequence number you will also see how to get the current value as well. Note, no locking is actually required, you can do this without the transaction stuff, it is there just so you can see it in two sessions at the same time. Also note that a rollback will NOT roll back the sequence number, this will end up with holes but sequences are not guaranteed to not have holes. Why do you have two columns, id, and forn_id, you only need one. and then do an insert into forn (descrip) values ( 'some description' ); then select curval('forn_id_seq'); forn_id will be populated for you with the value from curval. Dave On Mon, 2003-11-24 at 08:20, MaRcElO PeReIrA wrote: Hi guys, I have a simple table: teste=# \d forn Table public.forn Column | Type | Modifiers -+-+-- id | integer | not null default nextval('public.forn_id_seq'::text) forn_id | integer | descrip | text| Ok! The forn_id is supposed to be sequencial and without holes (if someone perform a DELETE or UPDATE, so there will be a hole... no problem if the hole happens in this case!). Well, to know the next value of the forn_id column, it was planned to be done like this: teste=# INSERT INTO forn (forn_id,descrip) VALUES ((SELECT max(forn_id) FROM forn),'descrip1'); It will cause a huge delay in case this table became huge, because the forn_id isn't an indexed column (but I would index it! The problem I am talking about is ONLY about the sequence of numbers). As a way to be sure it will not another other client getting the exact value as the max(forn_id), there was a dirty thing: teste=# BEGIN; teste=# LOCK TABLE forn IN ACCESS EXCLUSIVE MODE; teste=# INSERT INTO ... teste=# COMMIT; Well, I really think it is not the best way to do that and I am asking you for advices! 1) Is it (... max(forn_id)... ) the best way to get the next value to be inserted in the table? 2) Is there a automatic way to do that? Thanks in advance and Best Regards, Marcelo __ Yahoo! Mail: 6MB, anti-spam e antivírus gratuito! Crie sua conta agora: http://mail.yahoo.com.br === message truncated === Yahoo! Mail - 6MB, anti-spam e antivírus gratuito. Crie sua conta agora: http://mail.yahoo.com.br ---(end of broadcast)--- TIP 7: don't forget to increase your free space
Re: [GENERAL] autocommit in 7.4
Christian Traber [EMAIL PROTECTED] writes: Only one small problem, how can I disable autocommit in Postgres 7.4 in libpgtcl and psql? I found something about .psqlrc but how is the syntax and how can I set it for libpgtcl? In psql, \set AUTOCOMMIT off. In libpgtcl, I think you're out of luck :-( ... AFAIR no one got around to writing any autocommit control logic for libpgtcl. You are of course welcome to write some and send it in if you need it ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] pam authentication for postgres
I am trying to authenticate POstgres using Pam. How do I do this. I tried using webmin on RH9 and telling it to use Pam for authentication but it doesn't seem to work. Jason Tesser Web/Multimedia Programmer Northland Ministries Inc. (715)324-6900 x3050 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] SQL text of view
Hello run psql with param -E and use metacommand \d nameofyourview regards Pavel On Mon, 24 Nov 2003, Yury Shvetsov wrote: Hi. Where is the SQL text of view stored in the database? I mean the text like SELECT the_field FROM the_table. I can found the function's text in pg_proc.prosrc, but can't find the same for a view. Yury Shvetsov. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] autocommit in 7.4
Tom Lane wrote: In psql, \set AUTOCOMMIT off. In libpgtcl, I think you're out of luck ... AFAIR no one got around to writing any autocommit control logic for libpgtcl. You are of course welcome to write some and send it in if you need it ... Ok, I'll try to make it. Do you know any reason why libpgtcl is not tclstub enabled? Maybe I 'll try to build it with stubs... Regards, Christian ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] SRPM (PGDG) will not build
Hopefully not fuel to the RPM discussion but I've downloaded the PGDG SRPM this AM made some changes to what packages/portions would build and am now getting the following error. gcc: cannot specify -o with -c or -S and multiple compilations Somewhere near line 719 in the spec file. The changes I've made pertained to not building tk, tcl, and python portions of PostgreSQL. This has worked in the past. The system is based on RHL 8 but runs in a vserver, again this has worked in the past (PG 7.3.3 ) Did I kill the wrong thing? I'll continue looking but hope someone will spot the problem (or my mistake) quicker. TIA, Rod -- Open Source Software - Sometimes you get more than you paid for... ---(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] autocommit in 7.4
Christian Traber [EMAIL PROTECTED] writes: Ok, I'll try to make it. Do you know any reason why libpgtcl is not tclstub enabled? Nobody's done the work (I don't think tclstub even existed the last time anyone was doing serious work on libpgtcl). If you think it's worth having, and it can be done without breaking backwards compatibility with older Tcl releases, go for it ... 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] SRPM (PGDG) will not build
On Monday 24 November 2003 12:49 pm, Roderick A. Anderson wrote: Hopefully not fuel to the RPM discussion but I've downloaded the PGDG SRPM this AM made some changes to what packages/portions would build and am now getting the following error. gcc: cannot specify -o with -c or -S and multiple compilations Can you send me a complete stderr and stdout log of the build? (nohup usually works the best to do this, as it catches everything). -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(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] Humor me: Postgresql vs. MySql (esp. licensing)
[sNip] For exsample, if I have a product that I built for a customer, I would have to give it to them under the GPL. But I also have the choice to not give it to them AT ALL. So, they pay me to get it, and the license is the GPL. Their other choice, if they didn't pay me, would be to not have it at all. It's likely that I could sell this same software to multiple entities, because the likelihood of the first company having the time, personel, and motivation to just giving it away on the Internet are very small. [sNip] In summary, you could be charging them for some very expensive courier services, if for which they don't pay then you won't deliver. =) -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SCSI vs. IDE performance test
we have no portable means of expressing that exact constraint to the kernel Does this mean that specific operating systems have a better way of dealing with this? Which ones and how? I'm not aware of any that offer a way of expressing write these particular blocks before those particular blocks. It doesn't seem like it would require rocket scientists to devise such an API, but no one's got round to it yet. Part of the problem is that the issue would have to be approached at multiple levels: there is no point in offering an OS-level API for this when the hardware underlying the bus-level API (IDE) is doing its level best to sabotage the entire semantics. [sNip] Actually, NetWare is one OS that does this, and has been doing so since the 1980s with version 2 (version 6.5 is the current version today). They have a Patented caching algorithm called Elevator Seeking which both prolongs the life of the drive by reducing wear-and-tear and improving read/write performance by minimizing seek operations. With IDE it seems that this caching algorithm is also beneficial, but it really shines with SCSI drives. In all my experience, SCSI drives are much faster and far more reliable than IDE drives. I've always assumed that it boils down to you get what you pay for. -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(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] Where is Postgesql ? - MYSQL SURPRISES WITH MAXDB /
[sNip] * My favorite - TABLESPACES!! [sNip] This is one feature I dearly wished PostgreSQL had because it would allow for some additional performance tuning options. One thing I really liked about Oracle's Table Spaces was that I could specify a full path to it and the entire thing was in one file which could easily be copied to a different OS and the same version of Oracle would just use it without any problems. The other thing I really liked about Table Spaces is that Oracle had a tool called OCOPY which would make a duplicate of it (even while live) frozen at that moment without interfering with live transactions, thus backups could be made at any time from the copies of the files. I usually created a subdirectory called backups and used OCopy to put them there. If a future version of PostgreSQL could use Table Spaces instead of a group of individual files in the base directory (as it does now), then I believe it would add a whole new dimension to performance tuning (in addition to other things such as table, index, etc., size management). -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Export CSV from psql
[sNip] I have been playing with this lately, and I have found that if you use \f , \o /tmp/output.csv it opens fine in excel, it just bypasses the need to go next, next, finish when opening from a TAB separated .txt file Have you tested other Spreadsheet applications? If not, please feel free to post both .TAB and .TXT files in a reply here in this newsgroup and I'll try to load them up in Quattro Pro and post the results (and maybe others who use different spreadsheet applications could do the same if they have a little extra time to spare). -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
- Define a 32-bit field in MySQL. Insert a 64-bit number instead. Common sense tells you the value would be rejected. Yet MySQL happily folds it in and carries on its merry way. That's unacceptable. To me, this is a complete show-stopper because I simply won't tolerate data loss due to an idiotic design flaw. Worse. It is no data loss. It is loss of data integrity. If I know I have lost two hours of work, I will crib but redo it. If I know around 5% of records are messed up by database in last 5 years but don't know which, just think where do I stand. That is worse, thanks for your clarity. I stopped before thinking it through this far because any kind of screw-up like this just isn't worth the risk in my opinion -- they really need to fix that and I think they should make it a high priority. [sNip] Do you know of any published benchmarks for this? I need to convince some people who are hell-bent on MySQL being fast for everything that they're mis-informed, and they refuse to take anyone's word for it. Good benchmarks are hard to come by for two reasons 1. It is very difficult not to be blamed biased. 2. Featuer compensation. What if you run a postgresql benchmark with triggers and views, how do you test it with mysql anyways? I would suggest you to try OSDB becnhmarks http://osdb.sourceforge.net/ Thanks, I'll take a look at that. Your results will be great contribution to the community. If I get enough spare time, I'll consider doing this. Or try porting pgbench to mysql innodb. Actually I would like to see what the this benchmark does. Any prior knowledge of the results? I have no idea. [sNip] I've experienced this very problem with MySQL actually. It seems that Apache James (an open source Java-based SMTP/POP3 mail server) running on FreeBSD will trigger this problem very quickly as soon as multiple users attempt to send large (greater than 10 MBs) file attachments -- perhaps JDBC is part of the problem, but in the Apache James error logs there is indication of MySQL connectivity problems (also during busy times on systems sending approximately 500,000 eMails per day). Try dbmail. I am no mail admin but that is a mail server which works off postgresql/mysql. http://www.dbmail.org I see that it doesn't support file-based mail directories for storing messages. That's too bad, because it just won't be able to meet the performance of well-programmed mail servers such as Mercury (uses Novell Directory Services for the user database) or qmail (can use PostgreSQL, and other database engines, for the user database). -- Randolf Richardson - [EMAIL PROTECTED] Inter-Corporate Computer Network Services, Inc. Vancouver, British Columbia, Canada http://www.8x.ca/ This message originated from within a secure, reliable, high-performance network ... a Novell NetWare network. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] tsearch2 column update produces word too longerror
Hi! Now I really couldn't code C to save my life, but I managed to elicit some more debugging info. It's still dumb-user-interaction as suspected, but this is an issue I have to take into account as a basis; here's the patch for ts_cfg.c: if (lenlemm = MAXSTRLEN) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), !errmsg(word is too long(%d): %s,lenlemm,lemm))); Now when I try UPDATE ct_com_board_message SET ftindex=to_tsvector('default',coalesce(user_login,'') ||' '|| coalesce(title,'') ||' '|| coalesce(text,'')); I eventually get: ERROR: word is too long(2724): jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajjajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajjajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajjajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajjajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajjajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajjajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajjajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajjajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajjajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajjajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja jajajajajajajajajajajajajajajajajajajajajajajajajjajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj This is a brightly shining example of utterly wanton user-stupidity, I think: A 2k+ string of |:ja:|. Input like that cannot be helped, though - if he'd been a bit more imaginative, he could have used a few dozen Llanfairpwllgwyngyllgogerychwyrndrobwantysiliogogogoch in a row or anything else; unfortunately there's no app that could automatically whack a user if he's doing something stupid. But on the other hand I cannot think of any reason why crap like that should be indexed in the first place. Therefore I would like to see some sort of option allowing me to still use tsearch2 but actually automatically excluding anything exceeding MAXSTRLEN - so the UPDATE might throw a NOTICE (if anything at all) but still get on with the rest. An alteration like that does however exceed my limited abilities with C by far and I don't want to mess with something I do not fully understand and then use that mess in a production environment. Is there a way to get around this problem with oversized words? Kind regards Markus -Ursprüngliche Nachricht- Von: Oleg Bartunov [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 21. November 2003 15:13 An: Markus Wollny Cc: [EMAIL PROTECTED] Betreff: Re: AW: [GENERAL] tsearch2 column update produces word too longerror On Fri, 21 Nov 2003, Markus Wollny wrote: Hello! Von: Oleg Bartunov [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 21. November 2003 13:06 An: Markus Wollny Cc: [EMAIL PROTECTED] Word length is limited by 2K. What's exactly the word
[GENERAL] configure --with-java fails
Hello! When I try to run ./configure --with-java, it complains that ant doesn't work. However ant is installed, as is the latest Java SDK 1.4.2 from sun, PATH and JAVA_HOME are set correctly; helles:/ # /usr/java/apache-ant-1.5.4/bin/ant -version Apache Ant version 1.5.4 compiled on August 12 2003 It complains about some unsupported class-version; does it require an older Java-SDK? Here's the bit from config.log: configure:3157: checking for jakarta-ant configure:3190: result: no configure:3157: checking for ant configure:3175: found /usr/java/apache-ant-1.5.4/bin/ant configure:3187: result: /usr/java/apache-ant-1.5.4/bin/ant configure:3198: checking whether /usr/java/apache-ant-1.5.4/bin/ant works configure:3222: /usr/java/apache-ant-1.5.4/bin/ant -buildfile conftest.xml 12 Buildfile: conftest.xml conftest: [javac] Compiling 1 source file BUILD FAILED java.lang.UnsupportedClassVersionError: com/sun/tools/javac/Main (Unsupported major.minor version 48.0) at java.lang.ClassLoader.defineClass0(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:488) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:106) at java.net.URLClassLoader.defineClass(URLClassLoader.java:243) at java.net.URLClassLoader.access$100(URLClassLoader.java:51) at java.net.URLClassLoader$1.run(URLClassLoader.java:190) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:183) at java.lang.ClassLoader.loadClass(ClassLoader.java:294) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:281) at java.lang.ClassLoader.loadClass(ClassLoader.java:250) at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:310) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:115) at org.apache.tools.ant.taskdefs.compilers.CompilerAdapterFactory.doesModer nCompilerExist(CompilerAdapterFactory.java:173) at org.apache.tools.ant.taskdefs.compilers.CompilerAdapterFactory.getCompil er(CompilerAdapterFactory.java:131) at org.apache.tools.ant.taskdefs.Javac.compile(Javac.java:835) at org.apache.tools.ant.taskdefs.Javac.execute(Javac.java:682) at org.apache.tools.ant.Task.perform(Task.java:341) at org.apache.tools.ant.Target.execute(Target.java:309) at org.apache.tools.ant.Target.performTasks(Target.java:336) at org.apache.tools.ant.Project.executeTarget(Project.java:1339) at org.apache.tools.ant.Project.executeTargets(Project.java:1255) at org.apache.tools.ant.Main.runBuild(Main.java:609) at org.apache.tools.ant.Main.start(Main.java:196) at org.apache.tools.ant.Main.main(Main.java:235) com/sun/tools/javac/Main (Unsupported major.minor version 48.0) configure:3225: $? = 1 configure: failed java program was: public class conftest { int testmethod(int a, int b) { return a + b; } } configure: failed build file was: project name=conftest default=conftest target name=conftest javac srcdir=. includes=conftest.java /javac /target /project configure:3241: result: no configure:3245: error: ant does not work ## ## ## Cache variables. ## ## ## ac_cv_path_ANT=/usr/java/apache-ant-1.5.4/bin/ant #define PG_VERSION_STR PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 configure: exit 1 ---(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] Where is Postgesql ? - MYSQL SURPRISES WITH MAXDB /
Randolf Richardson wrote: From my perspective MySQL and PostgreSQL are completely different projects (for starters they even use different licensing schemes -- BSD v. GPL) that are in competition. Since PostgreSQL stands on its own without any dependencies on MySQL, I don't see any reason why PostgreSQL would suffer in any way if MySQL came to an end; The headlines that Jack and Jill Wall Street will be reading in the nightmarish outcome of something bad happening to MySQL will not be MySQL.com Fails To Migrate To SAPDB, it will be Open Source Database Disaster. The article will spin the story into rhetoric about how this team of hackers failed to accomplish the simple task of taking code from an ancient mainframe database system and making it work with the implication woven in that if open source/free software programmers can't even get old software to work, how can they get new software to work? Jack and Jill Wall Street only read the headlines*. They rarely if ever read the first paragraph, and only the smallest fraction of them read the whole article. Out of that miniscule fraction, the ones who actually do any groundwork of their own to figure out what the newspaper was talking about, are yourselves and myself. Combine that with the newspapers' habit of writing headlines to sell newspapers, rather than tell the truth, and you can see where any undesirable outcome in an open source project will lead. Especially with open source is bad being topic-du-jour what with SCO vs World going on right now. Anyone who actually works with computers (as opposed to accomplishes their work using computers) is more likely to understand that there's more to any problem or statement than just the words. If we heard a story about Is Your Car And Incinerator On Wheels?, we'd be more likely to think, what are they on about? - Jack and Jill Wall Street will be thinking, what if my car *is* an incinerator on wheels?. Once you come to the realisation that people are not inherently good or evil, they are just (as a whole) inherently stupid**, the whole world starts to make a lot more sense. Alex * I'm going to cop out here and say I forget where these figures come from - but in truth, it's just that Google can't remember for me ;) Check out how most people recommend teaching kids to read newspapers, and you'll find out why people develop bad habits like forming opinions from headlines! ** In any sufficiently large group of people, the average intelligence tends towards the minimum. I can't remember whose axiom that was... but it refers (more or less) to the fact that in a group, people want to conform, and conformity means not asking questions, which means believing whatever you're told, which means that you're being stupid. ---(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
[GENERAL] Index on array element
How can I create an index on an array element? I seem to recall having done this in the past but I don't recall how. steve=# \d foo Table public.foo Column | Type | Modifiers ---++--- textarray | text[] | steve=# create index foodex on foo (textarray[3]); ERROR: parser: parse error at or near [ at character 38 steve=# select version(); PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3 20030226 (prerelease) (SuSE Linux) Cheers, Steve ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] retrieve statement from catalogs
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
Randolf Richardson, DevNet SysOp 29 wrote: [sNip] For exsample, if I have a product that I built for a customer, I would have to give it to them under the GPL. But I also have the choice to not give it to them AT ALL. So, they pay me to get it, and the license is the GPL. Their other choice, if they didn't pay me, would be to not have it at all. It's likely that I could sell this same software to multiple entities, because the likelihood of the first company having the time, personel, and motivation to just giving it away on the Internet are very small. [sNip] In summary, you could be charging them for some very expensive courier services, if for which they don't pay then you won't deliver. =) Of course a competitor could purchase a copy or get it from a customer and set up shop right away selling it too. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Cron-job for checking up on pg_autovacuum
Hi! I haven't found anything in terms of startup- and check-scripts for pg_autovacuum yet; usually I like to have some sort of mechanism to check if some daemon is running and restart it if it isn't. Of course this sort of script shouldn't be too much of a bother for more experienced users; however you might actually find this small checkup-script more or less useful - just save it in /opt/pgsql/bin/ as autovachk, chmod +x and follow the included instructions for adding it to your crontab. Regards Markus #!/bin/sh # # This is a script suitable for use in a crontab. It checks to make sure # your pg_autovacuum daemon is running. # # To check for the daemon every 5 minutes, put the following line in your # crontab: #2,7,12,17,22,27,32,37,42,47,52,57 * * * * # /opt/pgsql/bin/autovachk /dev/null 21 # change this to the directory you run the daemon from: dir=/opt/pgsql/bin # change this to the complete commandline you usually start the daemon with daemoninvoc=pg_autovacuum -D -U postgres -L /var/log/pgautovac.log # I wouldn't touch this if I were you. daemonpid=`eval ps ax | sed -n '/[p]g_autovacuum/p' | awk '{ print $1 }'` ## you probably don't need to change anything below here ## cd $dir if `kill -CHLD $daemonpid /dev/null 21`; then # it's still going, so back out quietly exit 0 fi echo echo Couldn't find the pg_autovacuum daemon running. Reloading it... echo ./$daemoninvoc sleep 3 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] array iteration?
Ben wrote: Hey, that's cool. When did those functions arrive? I think 7.4. We had a lot of nice array stuff added. On Sun, 23 Nov 2003, Tom Lane wrote: Ben [EMAIL PROTECTED] writes: On Sat, 2003-11-22 at 12:44, CSN wrote: Is it possible to iterate over an array in plpgsql? Yep. http://archives.postgresql.org/pgsql-general/2003-11/msg00852.php The cited example is pretty iffy since it assumes that the valid array entries are all 0. In recent PG version you can use the array_upper and array_lower functions instead: for i in array_lower(a,1) .. array_upper(a,1) loop -- do something with a[i] end loop; 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]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] unsubscribe
---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] get_typdefault: failed to ...
Hi, Here is the case: mm=# delete from signon where user_id='210'; ERROR: get_typdefault: failed to lookup type 0 What is wrong here? A bug or something else. Thanks for your information. Vernon Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail! http://login.mail.lycos.com/r/referral?aid=27005 ---(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] PostgreSQL certifications?
CSN wrote: Just curious - are there any PostgreSQL certifications, or plans for them? I ask because I just became aware of two for mysql: No, we have talked about certification but felt it was better for individual companies to offer their own certifications. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index on array element
Steve Crawford [EMAIL PROTECTED] writes: How can I create an index on an array element? You need 7.4 and an extra layer of parentheses: create index foodex on foo ((textarray[3])); regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Help, Australian Time Problem
Is there a faq on this..cause boy it is giving me a headache. System is FreeBSD 4.9 System date returns Tue Nov 25 16:32:36 EST 2003 Postgres Version is 7.3.4 installed from the port collection I've set the datestyle to ISO, european I've set australian_timezones to true I've set TimeZone to local I do a SELECT CURRENT_TIMESTAMP as today; or SELECT now(); and I get 2003-11-25 05:36:18.17298+00 which is 11 hours behind, how do I get it to report the correct time? Dean ---(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] Where is Postgesql ? - MYSQL SURPRISES WITH MAXDB /
[EMAIL PROTECTED] (Jonathan Bartlett) writes: As far as I'm aware, MAXDB is completely different from MySQL, although it's sold by the same company. It's basically SAP's DB, which is really just a fork off of Adabas D, which has been a mainframe database for a long time. So, it really has nothing to do with MySQL, just the MySQL company. MySQL AB got about $19M of venture capital to take over development of SAPDB, which represents rather a lot of 'nothing.' What will happen in the integration of the product lines is a Really Big Question. - If it succeeds, it shows how impoverished the old product was; - If it fails, it will be a company-killing disaster for them that will reflect badly across the whole 'open source' community. The latter would injure the credibility of PostgreSQL in the wider community, despite the fact you or I might imagine this to be irrelevant. -- cbbrowne,@,libertyrms.info http://dev6.int.libertyrms.com/ Christopher Browne (416) 646 3304 x124 (land) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]