Re: [GENERAL] Recomended FS
Maybe it is a little late to be posting on this thread - but I was doing pgbench runs with a Raid 0 ATA system and thought the results might be interesting. So here they are : pgbench -c 5 -t 1000 -s 5, median of 3 runs on a Dual PIII 700 512Mb 2x7200 RPM ATA 133 Promise TX200 (same method / Pg configuration parameters as Scott's): 2 disk Raid0 W0 66 tps 2 disk Raid0 W1 220 tps I was expecting a slightly better result for W0 (write caching off), mind you the point could be made that you get about half the performance of the SCSI system - for about half the price. And the W1 result - that's fast, when (or if) that little power saving capacitor arrives for these drives we could see performance, reliability *and* economy regards Mark scott.marlowe wrote: MachineA Config1: 141 tps MachineB Config1 W0: 60 tps MachineB Config1 W1: 112 tps MachineA Config2: 101 tps MachineB Config2 W0: 44 tps MachineB Config2 W1: 135 tps ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] PGSQL technical documentation
Hi I prepare to write a master thesis about PostgreSQL. I need some technical information about solution (concrite information about algorithms, why the postgresql masters used exactly this algorithms). Most information avaliable (s2k-ftp.cs.berkeley.edu:8000/postgre/papers, http://developer.postgresql.org/docs/postgres/biblio.html) presents solution in Postgres (not PostgreSQL), and is quite old or isn't detailed. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Converting SQL-ASCII encoding database to UNICODE
Le Dimanche 9 Novembre 2003 19:39, Rajesh Kumar Mallah a écrit : If so what is the process The advantage of using a Unicode database is that UTF-8 supports/includes all known encodings at once. Therefore, in the process of development, it can help you save time. When using a Unicode database, if the client does not support Unicode, it is always possible to recode data streams on the fly with set client_encoding = foo_encoding. Therefore, there is no real drawback in using Unicode. It may only be a little slower, but there is no real proof. The process of conversion is as follows: - dump the whole database using pg_dump: pg_dump database_name pg_data_dump.sql - Do no drop the old database. Make a backup of the dump: cp pg_data_dump.sql pg_data_dump.sql.bak - recode the dump using the GNU recode package: recode ..u8 pg_data_dump.sql recode will try to guess the encoding the original ASCII database. - Open the file in an UTF-8 editor like Kate and verify that all characters are preserved and display well (in Hindi?). If it does not work, it may be a problem of original encoding. Try to guess it and retry using: cp -f source_encoding..u8.bak source_encoding..u8 recode source_encoding..u8 pg_data_dump.sql - create an empty Unicode database: psql template1 create database new_database with encoding=Unicode; reload the dump: psql new_database pg_data_dump.sql GNU/recode is available in most GNU/Linux distributions. By the way, as you live in India, do not hesitate to visit http://pgadmin.postgresql.org/pgadmin3/translation.php if you can help us. Most translations in languages for India are stalled, do not hesitate to take over the translation in Hindi for example. Cheers, Jean-Michel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/PGSQL help for getting number of rows matched.
Hello, it isn't problem. You can write SELECT INTO IF FOUND THEN ... END IF or SELECT INTO .. GET DIAGNOSTICS variable = ROW_COUNT; IF variable 0 THEN ... END IF You can see on http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGSQL-SELECT-INTO Regards Pavel On Mon, 10 Nov 2003, Rajesh Kumar Mallah wrote: Hi, We need to implement following logic efficiently. SELECT * from some_table where [ Query 1 ] IF rows_matched = 1 THEN use the single row that matched. ELSIF loop thru the results of [Query 1] END IF; Currently i am doing select count(*) for getting rows_matched in the top and repeating the same query in both branches of IF to get the data of matching rows. I have tried GET DIAGNOSTICS ROW_COUNT but for SELECTS if returns 0 or 1 based on matching I am sure there exists better methods. Kindly post a link to better documentation of pl/pgsql or point out section in the standard docs that discuss this issue. Regds Mallah. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] performance: time disk access measurment
Le Samedi 8 Novembre 2003 17:38, monu agrawal a écrit : Is there any way in pgsql to measure that how much time a transaction has taken how many disk accesses it has performed. You can use pgAdmin3 (http://www.pgadmin.org) SQL editor for transaction time. Now, if you are using WAL with enough shared memory, it is likely there will be little disk access other than flushing data to disk from time to time. Cheers, Jean-Michel ---(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] drop user question
I have create user PIPPO: CREATE USER PIPPO; I have create a table: create table aa (id int4); Change owner of table to user PIPPO. alter table aa owner pippo; I drop user PIPPO. Who is the owner of the table? Thanks. Bye !! Frank Lupo (Wolf) !! /\_ _/\ \ o o / --ooo-ooo--- -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Usi ancora fax e carta? Allora sei preistorico! Oggi i fax li ricevi direttamente sul computer con il nuovo EmailFax IN Clicca e scopri i vantaggi Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=1568d=10-11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] starting the server at boot
Hello; If I add the line: --- su -c 'pg_ctl start -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/logfile' postgres --- to /etc/rc.d/rd.local. Is there a way this could work when I don't boot as root, but as a common user? (I should be able to automatically pass a password, and I don't know how) Thanks and regards Javier Garcia ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] PL/PGSQL help for getting number of rows matched.
Hi, We need to implement following logic efficiently. SELECT * from some_table where [ Query 1 ] IF rows_matched = 1 THEN use the single row that matched. ELSIF loop thru the results of [Query 1] END IF; Currently i am doing select count(*) for getting rows_matched in the top and repeating the same query in both branches of IF to get the data of matching rows. I have tried GET DIAGNOSTICS ROW_COUNT but for SELECTS if returns 0 or 1 based on matching I am sure there exists better methods. Kindly post a link to better documentation of pl/pgsql or point out section in the standard docs that discuss this issue. Regds Mallah. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] starting the server at boot
On Monday 10 November 2003 15:02, javier garcia - CEBAS wrote: Hello; If I add the line: --- su -c 'pg_ctl start -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/logfile' postgres --- to /etc/rc.d/rd.local. Is there a way this could work when I don't boot as root, but as a common user? rc.local script is always run as root. So it should work even if you boot/log in as normal user. (I should be able to automatically pass a password, and I don't know how) Read man pages for .pgpass. HTH Shridhar ---(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] PL/PGSQL help for getting number of rows matched.
On Mon, 10 Nov 2003, Pavel Stehule wrote: Hello, it isn't problem. You can write SELECT INTO IF FOUND THEN ... END IF or SELECT INTO .. GET DIAGNOSTICS variable = ROW_COUNT; IF variable 0 THEN ... END IF You can see on http://developer.postgresql.org/docs/postgres/plpgsql-statements.html#PLPGSQL-SELECT-INTO Probably sectino 37.7.4 of the docs ( in http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html ) is a better source as shown lower. On Mon, 10 Nov 2003, Rajesh Kumar Mallah wrote: Hi, We need to implement following logic efficiently. SELECT * from some_table where [ Query 1 ] IF rows_matched = 1 THEN use the single row that matched. ELSIF loop thru the results of [Query 1] END IF; Currently i am doing select count(*) for getting rows_matched in the top and repeating the same query in both branches of IF to get the data of matching rows. I have tried GET DIAGNOSTICS ROW_COUNT but for SELECTS if returns 0 or 1 based on matching I am sure there exists better methods. Kindly post a link to better documentation of pl/pgsql or point out section in the standard docs that discuss this issue. DECLARE tup RECORD; BEGIN FOR tup IN select * from mytable LOOP Do the required action END LOOP; END Indeed, I'm not even sure how to loop through the results of the query using the scheme you show above. What do you assign the results of the select to? -- Nigel Andrews ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Temp rows - is it possible?
Mattias Kregert [EMAIL PROTECTED] writes: This is great! create table a(...); insert into a(...); # fixed values create table b() inherits (a); insert into b values(...); # temporary values select * from a; # You can get both global and temporary values. I don't think it's actually reliable. B was meant to be a temp table, right? The problem is that B will be globally visible to all sessions as being a child table of A, but because temp tables are processed in backend-local buffers, it will be quite erratic whether other sessions can see the rows you've inserted. In an experiment just now, another session could not see the rows in B until I'd inserted several thousand of them (enough to overrun the local buffers) ... and then the other session could see some but not all of them. We recently decided we had to forbid foreign-key references from temp tables to permanent tables because of this effect. I wonder whether we won't end up forbidding temp tables as children of permanent tables too. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Dynamic expression evaluation
Philippe Lang [EMAIL PROTECTED] writes:
Is there a simple way of doing kind of a
SELECT *, EVAL(f) FROM public.test;
... and having f evaluated as an expression, so that we get back:
--
id a bfeval
--
12 3a+b 5
2123a*b 36
35 6a+2*b17
--
Not really. You can sort of approximate eval() with plpgsql's EXECUTE:
regression=# create or replace function eval(text) returns int as '
regression'# declare res record;
regression'# begin
regression'# for res in execute ''select '' || $1 || '' as result'' loop
regression'# return res.result;
regression'# end loop;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select eval ('23+34');
eval
--
57
(1 row)
regression=#
but this has a problem with supporting more than one result type (hmm,
maybe you could fake that with 7.4's polymorphism?). And I don't see
any way at all for the function to have access to the other values in
the row, as your example presumes it would do.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem Deleting Referenced records
On Mon, Nov 10, 2003 at 16:20:21 +0900, Alex [EMAIL PROTECTED] wrote: Bruno, thanks. I actually did it that way but having to join two tables each 1-2 million records makes this process rather time consuming. I was hoping that the ON DELETE options in the constraint could handle that. If only a small number of the 1-2 million records have old dates, than the where not exists method might be faster. An index scan could be used to find the records with old dates and then for each record an index lookup could be done in table B to see if it should really be deleted. It seems to be a bit odd that if I want to delete 100 records that are not related to each other, and one record deletion fails that then the entire delete process fails. You can delete each record in its own transaction if you want that behavior. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Dynamic expression evaluation
Hello, Imagine we have the following kind of table, with two values (a and b), and a varchar (f) representing an expression. -- CREATE TABLE public.test ( id serial NOT NULL, a int4, b int4, f varchar(50), CONSTRAINT id PRIMARY KEY (id) ) WITHOUT OIDS; INSERT INTO public.test(a,b,f) VALUES(2,3,'a+b'); INSERT INTO public.test(a,b,f) VALUES(12,3,'a*b'); INSERT INTO public.test(a,b,f) VALUES(5,6,'a+2*b'); -- Is there a simple way of doing kind of a SELECT *, EVAL(f) FROM public.test; ... and having f evaluated as an expression, so that we get back: -- id a bfeval -- 12 3a+b 5 2123a*b 36 35 6a+2*b17 -- Has anyone done anything like that already? Thanks! Philippe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Mon, Nov 10, 2003 at 08:09:29AM -0800, Scott Chapman wrote: Chronological events here: X inserts a new record into A. Y inserts a new record into A. X fetches currval of the SA. What value does X get in this case, the one from X's insert or Y's? X's. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) ¿Qué importan los años? Lo que realmente importa es comprobar que a fin de cuentas la mejor edad de la vida es estar vivo (Mafalda) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] SQL-question: returning the id of an insert querry
On Monday 10 November 2003 08:23, David Green wrote:
Are X Y two different connections?
If you execute 2 statements on the same connection and then get
currval() it will give the last generated id.
Ex.
On 1 connection:
INSERT INTO A (fld) VALUES (val); -- id generated = 1
INSERT INTO A (fld) VALUES (val2); -- id generated = 2
SELECT currval('SA');
2
Thanks for the clarification. With web applications and connection
pooling, it would appear that it's quite easy to get incorrect values
back. This is what I thought.
I talked with the author or SQLObject about this recently and I thnk
he's implementing this correctly, by querying the cursor for the last
OID?:
def _queryInsertID(self, conn, table, idName, names, values):
c = conn.cursor()
q = self._insertSQL(table, names, values)
if self.debug:
print 'QueryIns: %s' % q
c.execute(q)
c.execute('SELECT %s FROM %s WHERE oid = %s'
% (idName, table, c.lastoid()))
return c.fetchone()[0]
The other way to do it would be to manually fetch nextval and insert
into the table over-riding the default for the ID field (assuming it
defaulted to the nextval in the sequence). I don't know which way is
best (for performance, for instance).
It's be nice if INSERT could be made to return the OID or (better yet)
the primary key field value when it completes. That would solve this
problem in one action and completely remove the need for the second
query. I expect it would have to be user-togglable so it didn't break
with existing code?
Scott
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Temp rows - is it possible?
Tom Lane wrote: Mattias Kregert [EMAIL PROTECTED] writes: This is great! create table a(...); insert into a(...); # fixed values create table b() inherits (a); insert into b values(...); # temporary values select * from a; # You can get both global and temporary values. I don't think it's actually reliable. B was meant to be a temp table, right? The problem is that B will be globally visible to all sessions as being a child table of A, but because temp tables are processed in backend-local buffers, it will be quite erratic whether other sessions can see the rows you've inserted. In an experiment just now, another session could not see the rows in B until I'd inserted several thousand of them (enough to overrun the local buffers) ... and then the other session could see some but not all of them. We recently decided we had to forbid foreign-key references from temp tables to permanent tables because of this effect. I wonder whether we won't end up forbidding temp tables as children of permanent tables too. Yep, I think we will have to do that. TODO item? -- 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
[GENERAL] case-sensitive sorting and locale settings?
On my windows/cygwin/PG 7.3.1 box, results are sorted with lowercase appearing after uppercase. On my redhat/PG 7.3.3 boxes results are sorted without regard to case. Is there some locale (or other) setting that controls this? If so, which locales do which? Or is this due to something else entirely - such as the OS's? __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree ---(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] SQL-question: returning the id of an insert querry
I saw this method of Statement class in jdbc.
Will the return int contain the autogenerated key value ??
public int executeUpdate(String sql,
int autoGeneratedKeys)
throws SQLException
thanks,
kathy
Scott Chapman wrote:
On Monday 10 November 2003 08:23, David Green wrote:
Are X Y two different connections?
If you execute 2 statements on the same connection and then get
currval() it will give the last generated id.
Ex.
On 1 connection:
INSERT INTO A (fld) VALUES (val); -- id generated = 1
INSERT INTO A (fld) VALUES (val2); -- id generated = 2
SELECT currval('SA');
2
Thanks for the clarification. With web applications and connection
pooling, it would appear that it's quite easy to get incorrect values
back. This is what I thought.
I talked with the author or SQLObject about this recently and I thnk
he's implementing this correctly, by querying the cursor for the last
OID?:
def _queryInsertID(self, conn, table, idName, names, values):
c = conn.cursor()
q = self._insertSQL(table, names, values)
if self.debug:
print 'QueryIns: %s' % q
c.execute(q)
c.execute('SELECT %s FROM %s WHERE oid = %s'
% (idName, table, c.lastoid()))
return c.fetchone()[0]
The other way to do it would be to manually fetch nextval and insert
into the table over-riding the default for the ID field (assuming it
defaulted to the nextval in the sequence). I don't know which way is
best (for performance, for instance).
It's be nice if INSERT could be made to return the OID or (better yet)
the primary key field value when it completes. That would solve this
problem in one action and completely remove the need for the second
query. I expect it would have to be user-togglable so it didn't break
with existing code?
Scott
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] SQL query not returning the value expected !!!!!!!!!!!!!!!!!!
Hi ifyou take a look at the following query's you can see that the third query should logically return043219but instead it's not returning any rows ?? What's happening ?? dragon= SELECT cod_etu from parcours_v where cod_etu = '043219';cod_etu-(0 rows) dragon= SELECT cod_etu from parcours_hors_cursus_v where cod_etu = '043219';cod_etu-043219(1 row) dragon= SELECT cod_etu from parcours_hors_cursus_v where cod_etu ='043219' and cod_etu not in (select cod_etu from parcours_v);cod_etu-(0 rows)
Re: [GENERAL] Temp rows - is it possible?
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: We recently decided we had to forbid foreign-key references from temp tables to permanent tables because of this effect. I wonder whether we won't end up forbidding temp tables as children of permanent tables too. Yep, I think we will have to do that. TODO item? Plan B would be to arrange for the planner to ignore temp tables of other backends whenever it is searching for child tables. Then the behavior would be predictable: you never see any rows inserted in other people's temp child tables (and cannot update or delete 'em, either). I'm not sure if this is the behavior the OP wanted, but it seems at least marginally useful. 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
[GENERAL] Two build problems - openssl and plperl on RedHat
Greetings, I'm trying to build 7.3.4 and I've come across two problems, one during the configure and the other afterward. Problem 1) Trying to build with openssl support gives this: ./configure --with-openssl --enable-odbc --with-perl --enable-multibyte ... checking openssl/ssl.h usability... no checking openssl/ssl.h presence... no checking for openssl/ssl.h... no configure: error: header file openssl/ssl.h is required for OpenSSL [EMAIL PROTECTED] postgresql-7.3.4]# locate openssl/ssl.h /usr/include/openssl/ssl.h I've tried many flavors of --with-openssl=/usr/include but with no luck. I've removed the --with-openssl, because it isn't needed immediately, and everything configured and compiled fine. 2) I cannot figure out how to createlang plperl and plperlu. I'm getting this message: [EMAIL PROTECTED] postgres]$ /usr/local/pgsql/bin/createlang plperlu chris ERROR: Load of file /usr/local/pgsql/lib/plperl.so failed: libperl.so: cannot open shared object file: No such file or directory createlang: language installation failed The INSTALL docs state that I must have a full install of Perl (which I do: perl-5.8.0 via RedHat's rpm) and that it Postgres won't be able to compile if it isn't a shared object. Since Postgres compiled, can I assume that the Perl install is ok? My machine is a P4 running RedHat 9.0. Any info would be greatly appreciated. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(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] Two build problems - openssl and plperl on RedHat
Christopher Murtagh writes: checking openssl/ssl.h usability... no checking openssl/ssl.h presence... no checking for openssl/ssl.h... no configure: error: header file openssl/ssl.h is required for OpenSSL Check the config.log file. [EMAIL PROTECTED] postgres]$ /usr/local/pgsql/bin/createlang plperlu chris ERROR: Load of file /usr/local/pgsql/lib/plperl.so failed: libperl.so: cannot open shared object file: No such file or directory createlang: language installation failed This is an error message of the run-time loader. Perhaps you need to tweak /etc/ld.so.conf or set LD_LIBRARY_PATH. -- Peter Eisentraut [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] why does explain tell me I'm using a seq scan?
Adam Ruth wrote: The optimizer is looking at the statistics and figuring that the second query could be done better with a sequential scan (perhaps there aren't many rows). Have you analyzed the table to get the statistics up to date? It could also be that the seq scan is faster with the size of your table. Thanks! An analyze fixed it and reduced our search time from seconds to milliseconds. Is there ever any reason to do a vacuum without doing a vacuum analyze? Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Why does primary key violation cause an abort?
I am using Postgres 7.3.4 through JDBC, and turning auto-commit off. My application needs to insert a row or, if a row with the same primary key already exists, update the existing row. I was hoping to implement this by just trying the insert, and doing the update only in case of a PK violation (which results in a SQLException). I've run into two problems. 1) Detecting a PK violation cannot be done cleanly. The violation results in a SQLException, and the only way I can see to distinguish a PK violation from some other problem is to check the text of the error message returned by SQLException.getMessage(). (SQLException.getErrorCode() returns 0, and getSQLState() returns null). It would be nice if the error code clearly identified a PK violation, (or even just a uniqueness violation). 2) The more serious problem is that the PK violation causes an abort of the transaction, so I can't proceed to do the update in the same transaction. Yes, there are easy ways to code around this problem, but they are going to be slower. Duplicates are very unlikely in my application, so if I update, and then do the insert on an update count of zero, I will end up executing twice as many commands as I would otherwise. Why does PostgreSQL abort a transaction when a PK violation occurs? The closest I was able to find was this: http://archives.postgresql.org/pgsql-hackers/2002-06/msg00325.php but it doesn't really answer my question. I can understand this behavior for pgplsql programs, where exceptions cannot be caught, but it seems to be an unnecessary restriction for Java, and in general, for applications written using APIs that permit continuation following an error. Jack Orenstein Reference Information Systems, Inc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] [off-topic] Bugtracker using PostgreSQL
Hi guys, Do you know any web based bug tracker software that use PostgreSQL??? Somebody has told me about Mantis, but it use MySQL... and I resign to use that! :( Which is the best bug tracker you know???(PHP+PostgreSQL) Thanks in advance and Best Regards, Marcelo Pereira Yahoo! Mail - 6MB, anti-spam e antivírus gratuito. Crie sua conta agora: http://mail.yahoo.com.br ---(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] [off-topic] Bugtracker using PostgreSQL
MaRcElO PeReIrA wrote: Hi guys, Do you know any web based bug tracker software that use PostgreSQL??? Somebody has told me about Mantis, but it use MySQL... and I resign to use that! :( Which is the best bug tracker you know???(PHP+PostgreSQL) We use TUTOS ( www.tutos.org ) but the bugs tracking tool that have is not too much evolved, try it. Regards Gaetano Mendola ---(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] [off-topic] Bugtracker using PostgreSQL
El Lun 10 Nov 2003 16:14, MaRcElO PeReIrA escribió: Hi guys, Do you know any web based bug tracker software that use PostgreSQL??? Somebody has told me about Mantis, but it use MySQL... and I resign to use that! :( Which is the best bug tracker you know???(PHP+PostgreSQL) Wasn't someone in Red Hat migrating bugzilla to PostgreSQL? -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Temp rows - is it possible?
Hello Bruce, Monday, November 10, 2003, 11:08:47 AM, you wrote: BM Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: We recently decided we had to forbid foreign-key references from temp tables to permanent tables because of this effect. I wonder whether we won't end up forbidding temp tables as children of permanent tables too. Yep, I think we will have to do that. TODO item? Plan B would be to arrange for the planner to ignore temp tables of other backends whenever it is searching for child tables. Then the behavior would be predictable: you never see any rows inserted in other people's temp child tables (and cannot update or delete 'em, either). I'm not sure if this is the behavior the OP wanted, but it seems at least marginally useful. BM Agreed. It seems wrong that a session should ever see other people's BM temp tables as children. So going back to the original problem, do you think there should be a way to implement temp rows in tables visible to everyone? I worked around the original problem I had by using custom entries in pg_listener (listen identifier) and that works well because they disappear as soon as backend detects the disconnect, but I'd really like to be able to do exact same thing outside of pg_listener and be able to reference that table from other permanent tables, which is currently impossible with pg_listener as its a part of system catalog. -- -Boris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/Perl returning multiple rows
On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote: The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is counter intuitive to some people and those of us who are coming from say a Perl background are going to be much more proficient in using pl/Perl then having to learn YET another language. Thanks for all the feedback. I've written a bunch of triggers in pl/pgsql and it wasn't the worst thing. The reason why I was thinking pl/perl is because my perl function needs to make a system call (to htDig actually) and extract integers from URLs that htDig give it. I know I could write this in Perl with my eyes closed, but I'm not so sure how I would do this with pgSQL. Can you even make pl/pgSQL 'untrusted' to make syscalls? Basically, what I'm doing is using htDig to index and search text objects within Postgres. I spent a lot of time trying to get GiST and tsearch to work, but the lack of documentation and complexity of it made it impossible. Plus, htDig already has features that allow it to ignore HTML, phrase searching as well as fuzzy logic for lexemes, soudex and whatnot. We donated a G4 (and hopefully more soon) to the htDig team to help get 3.2 out of beta, and it is paying off big time. Here's a prototype of one component of the search engine: http://newfind.mcgill.ca/ads/ which basically is an index of: http://www.mcgill.ca/classified/ That search tool works well, but it is a PHP wrapper/hack. I would much rather do it at the DB level rather than PHP as it makes it a much more powerful tool. If I had a month or two, I would take the htDig source and make it a Postgres plugin, but unfortunately I don't. The worst part of this is that I have about two days to finish building this. :-( So, perhaps I should stick with Perl for now, and hope that with a real SPI, the speed will improve significantly. Someone mentioned earlier that there is an experimental SPI... just how experimental exactly? Segfault and die or less dangerous? Thanks again for all the feedback. I'd be happy for any more thoughts and ideas. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl returning multiple rows
Hello, If you can code in Perl then pl/C wouldn't be a deep jump. J Christopher Murtagh wrote: On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote: The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is counter intuitive to some people and those of us who are coming from say a Perl background are going to be much more proficient in using pl/Perl then having to learn YET another language. Thanks for all the feedback. I've written a bunch of triggers in pl/pgsql and it wasn't the worst thing. The reason why I was thinking pl/perl is because my perl function needs to make a system call (to htDig actually) and extract integers from URLs that htDig give it. I know I could write this in Perl with my eyes closed, but I'm not so sure how I would do this with pgSQL. Can you even make pl/pgSQL 'untrusted' to make syscalls? Basically, what I'm doing is using htDig to index and search text objects within Postgres. I spent a lot of time trying to get GiST and tsearch to work, but the lack of documentation and complexity of it made it impossible. Plus, htDig already has features that allow it to ignore HTML, phrase searching as well as fuzzy logic for lexemes, soudex and whatnot. We donated a G4 (and hopefully more soon) to the htDig team to help get 3.2 out of beta, and it is paying off big time. Here's a prototype of one component of the search engine: http://newfind.mcgill.ca/ads/ which basically is an index of: http://www.mcgill.ca/classified/ That search tool works well, but it is a PHP wrapper/hack. I would much rather do it at the DB level rather than PHP as it makes it a much more powerful tool. If I had a month or two, I would take the htDig source and make it a Postgres plugin, but unfortunately I don't. The worst part of this is that I have about two days to finish building this. :-( So, perhaps I should stick with Perl for now, and hope that with a real SPI, the speed will improve significantly. Someone mentioned earlier that there is an experimental SPI... just how experimental exactly? Segfault and die or less dangerous? Thanks again for all the feedback. I'd be happy for any more thoughts and ideas. Cheers, Chris -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/Perl returning multiple rows
Christopher Murtagh wrote: On Mon, 2003-11-10 at 16:52, Alvaro Herrera wrote: Better start learning Tcl ... Thanks for the info. I'm ok with that, I like Perl, but I can live without it too. :-) Two questions: 1) Can Tcl return multiple rows? 3) ok, 3 questions... Any word on pl/php and a release date? AFAIK, the only PLs that support returning multiple rows at the moment are SQL, PL/pgSQL, and PL/R. Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/Perl returning multiple rows
On Mon, 2003-11-10 at 23:05, Joshua D. Drake wrote: Hello, If you can code in Perl then pl/C wouldn't be a deep jump. That might not be a bad idea. Haven't done much C programming since my CS days, but I really loved it then. Other than here: http://www.postgres.org/docs/7.3/static/xfunc-c.html Can you recommend more reading on writing C functions for Postgres? Books, anything? Thanks again. Cheers, Chris -- Christopher Murtagh Enterprise Systems Administrator ISR / Web Communications Group McGill University Montreal, Quebec Canada Tel.: (514) 398-3122 Fax: (514) 398-2017 ---(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] PL/Perl returning multiple rows
On Mon, Nov 10, 2003 at 03:00:34PM -0800, Joshua D. Drake wrote: Better start learning Tcl ... What are we torturing people now? Can plPython do this? Well, aparently Tcl is not up to the task either, nor is plPython. At least I can find no mention on the docs nor the source code. Can your plPHP or plPerl do it? It seems the only choices left are PL/pgSQL and C ... -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Acepta los honores y aplausos y perderás tu libertad ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PL/Perl returning multiple rows
Christopher Murtagh wrote: On Mon, 2003-11-10 at 21:11, Joshua D. Drake wrote: The fact that it is pl/pgSQL? Seriously though, I think that pl/pgSQL is counter intuitive to some people and those of us who are coming from say a Perl background are going to be much more proficient in using pl/Perl then having to learn YET another language. Thanks for all the feedback. I've written a bunch of triggers in pl/pgsql and it wasn't the worst thing. The reason why I was thinking pl/perl is because my perl function needs to make a system call (to htDig actually) and extract integers from URLs that htDig give it. I know I could write this in Perl with my eyes closed, but I'm not so sure how I would do this with pgSQL. Can you even make pl/pgSQL 'untrusted' to make syscalls? Write a Pl/Perl function that just does the syscall, and call it from PL/pgSQL. Similarly for complex string parsing, etc. HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] drop user question
According to my test (pg 7.2.4), the owner of the table will be null after the user PIPPO is dropped. test=# \dt aa List of relations Name | Type | Owner --+---+--- aa | table | pippo test=# drop user PIPPO; DROP USER test=# \dt aa List of relations Name | Type | Owner --+---+--- aa | table | Hope that helps. ~R ps. I had to change the syntax of your alter table statement to alter table aa owner to pippo; for it to work. frank_lupo wrote: I have create user PIPPO: CREATE USER PIPPO; I have create a table: create table aa (id int4); Change owner of table to user PIPPO. alter table aa owner pippo; I drop user PIPPO. Who is the owner of the table? Thanks. Bye !! Frank Lupo (Wolf) !! /\_ _/\ \ o o / --ooo-ooo--- -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: Usi ancora fax e carta? Allora sei preistorico! Oggi i fax li ricevi direttamente sul computer con il nuovo EmailFax IN Clicca e scopri i vantaggi Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=1568d=10-11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Temp rows - is it possible?
Tom, On Mon, 10 Nov 2003 09:39:32 -0500 Tom Lane [EMAIL PROTECTED] wrote: select * from a; # You can get both global and temporary values. I don't think it's actually reliable. B was meant to be a temp table, right? Ugh Yes. create *temp* table b() inherits (a); -- TANIDA Yutaka [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Temp rows - is it possible?
On Tuesday 11 November 2003 02:16, Robert Creager wrote: When grilled further on (Mon, 10 Nov 2003 09:39:32 -0500), Tom Lane [EMAIL PROTECTED] confessed: We recently decided we had to forbid foreign-key references from temp tables to permanent tables because of this effect. I wonder whether we won't end up forbidding temp tables as children of permanent tables too. Forbidding temp tables that inherit? That would suck (as someone who uses them). Would there be an alternate method to easily create a temp table that is identical to another? You can use LIKE clause in create table. See http://developer.postgresql.org/docs/postgres/sql-createtable.html HTH Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/Perl returning multiple rows
Christopher Murtagh wrote: On Mon, 2003-11-10 at 23:05, Joshua D. Drake wrote: Hello, If you can code in Perl then pl/C wouldn't be a deep jump. That might not be a bad idea. Haven't done much C programming since my CS days, but I really loved it then. Other than here: http://www.postgres.org/docs/7.3/static/xfunc-c.html Can you recommend more reading on writing C functions for Postgres? Books, anything? PostgreSQL Developer's Handbook. Also look into ecpg... it will make your life easier. -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PL/Perl returning multiple rows
Christopher Murtagh wrote:
On Tue, 2003-11-11 at 00:07, Joe Conway wrote:
Write a Pl/Perl function that just does the syscall, and call it from
PL/pgSQL. Similarly for complex string parsing, etc.
That would work if I could get the Pl/Perl function to return an array
or set of results, but this brings me back to the original problem
(unless I'm missing something obvious).
Sorry, I guess I didn't sufficiently understand the issue. I don't
really use PL/Perl myself, but I would think there was some way to
return an array. In the docs, I see:
Conversely, the return command will accept any string that is
acceptable input format for the function's declared return type. So, the
PL/Perl programmer can manipulate data values as if they were just text.
So if you declare the PL/Perl function to return text[], and return a
properly formatted array, e.g. something like
{\blah blah\,\foo bar\,\etc etc\}
it ought to work.
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] PostgreSQL v7.4 Release Candidate 2
We have just packaged up our second Release Candidate for v7.4, with the hopes of producing a full release next week. A full ChangeLog is available at: ftp://ftp.postgresql.org/pub/sources/v7.4/ChangeLog.RC1.to.RC2 But, one of the highlights is that support for tcl8.0.x has been re-introduced. there are alot of doc changes, and some what appear to be small fixes, mostly related to the various ports. As we are in the home stretch of a full release, we encourage as many as possible to test and report any bugs they can find, whether as part of the build process, or running in real life scenarios. If we've heard no reports back before midnight on Thursday, we are looking at a full code freeze, with a Final Release to happen on the following Monday. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
