Re: [GENERAL] Gborg down?
Marc G. Fournier wrote: On Fri, 5 Nov 2004, Joshua D. Drake wrote: I've posted details to the FreeBSD -stable and -current mailing list, but the simple summary is that 'ifconfig dev -alias' with no ip specific'd literally erases all IPs on that device, leaving the server running 'un-networked' ... :( Good lord. Ya well, its not something I'm particularly proud about ... Stupid question for someone running Linux ... is this standard behaviour that I've been lucky never to hit before, or is this something that Linux deals with slightly more intelligently? I used to have a script on my remote server that I was running in BG before to touch the network, that script was bringing up the network if was down for more then 2 minutes. My server is now hosted in my house and I do not need it anymore, I changed it with the no-ip script :-) Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] create a text file from postgres (like Oracle UTL_FILE package)
ok. I gonna explain a little more my situation.
I have a postgres server (Linux) and clients
(Win2k+delphi application).
I need create a text file with some columns from a
table in the server machine, but this file must be
created only when the user wants. (click button on my
delphi program on the client side create a text file
on the server file system).
I can't share o mount a resource or connect by socket.
I think execute a postgres function from delphi and
leave to postgres a work to create the file.
so, my problem is how to create a text file from a
postgres function. may be I can use a java program
(like Oracle Java Stored Procedures).
How I execute a external java program from postgres
function?
exist some OS system call in postgres?
something like
Create or Replace Function run () returns int4 as
begin
execute_OS_system_call('java -jar someapp.jar');
return (1);
end;
thanks
--- Thomas F.O'Connell [EMAIL PROTECTED] wrote:
At this point, I'm not sure I'm understanding your
question. I'll try
restating my solution again:
I would have, for example, a Perl script that used
the DBI module to
extract information from a given database and then
let Perl handle any
relevant text/data manipulation and file
creation/manipulation.
DBI should allow the execution of arbitrary SQL
against any supported
database, and postgres is supported.
Also, I'd recommend continuing to post and not
merely emailing me
directly to keep more eyes on your problem.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 5, 2004, at 12:20 PM, Leo Martin Orfei wrote:
If I let the script connect to DB and create files
(I
belive in this solution), how I execute the
script
from a client side? may be a function? some OS
system
call?
thanks.
--- Thomas F. O'Connell [EMAIL PROTECTED]
wrote:
You're thinking about it in reverse. Let the
script
make calls via a
database interface. There are database interfaces
to
postgres in a
variety of modern programming languages.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Nov 5, 2004, at 7:24 AM, Leo Martin Orfei
wrote:
ok, this appears to be a good solution...
so, how I execute a perl (or C, or bash ) script
from
a postgresql function?
if I use C (I think that write C code and use it
in
the database is possible), I have to connect to
the
database from my C code or it's embebed and I
can
use
tables directly?
thanks.
---(end of
broadcast)---
TIP 8: explain analyze is your friend
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(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] create a text file from postgres (like Oracle
Hi,
Am Sa, den 06.11.2004 schrieb Leo Martin Orfei um 13:52:
ok. I gonna explain a little more my situation.
I have a postgres server (Linux) and clients
(Win2k+delphi application).
I need create a text file with some columns from a
table in the server machine, but this file must be
created only when the user wants. (click button on my
delphi program on the client side create a text file
on the server file system).
I can't share o mount a resource or connect by socket.
I think execute a postgres function from delphi and
leave to postgres a work to create the file.
so, my problem is how to create a text file from a
postgres function. may be I can use a java program
(like Oracle Java Stored Procedures).
How I execute a external java program from postgres
function?
exist some OS system call in postgres?
something like
Create or Replace Function run () returns int4 as
begin
execute_OS_system_call('java -jar someapp.jar');
return (1);
end;
Well. Although there is a project for java as function
language, I dont think its the best option when you
look at startup times and memory footprint.
You can either do with temp tables and copy
or use any of the *u languages (u=unrestricted)
for example plpythonu
CREATE FUNCTION makefile(text) RETURNS text AS '
o=open(/path/to/file)
o.write(args[0])
o.close()
return ok
' LANGUAgE plpythonu;
Regards
Tino
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Gborg down?
On Sat, 6 Nov 2004, Gaetano Mendola wrote: Marc G. Fournier wrote: On Fri, 5 Nov 2004, Joshua D. Drake wrote: I've posted details to the FreeBSD -stable and -current mailing list, but the simple summary is that 'ifconfig dev -alias' with no ip specific'd literally erases all IPs on that device, leaving the server running 'un-networked' ... :( Good lord. Ya well, its not something I'm particularly proud about ... Stupid question for someone running Linux ... is this standard behaviour that I've been lucky never to hit before, or is this something that Linux deals with slightly more intelligently? I used to have a script on my remote server that I was running in BG before to touch the network, that script was bringing up the network if was down for more then 2 minutes. My server is now hosted in my house and I do not need it anymore, I changed it with the no-ip script :-) Until now, I didn't think I'd need such a think :( In almost 12 years of networking, that is one thing that I've never done, unconfigured the main IP ;( Then again, I think I've been lucky so far in that I've never done an 'rm -rf /' either :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Retrieve ip client from postgres
OK thank you On Fri, Nov 05, 2004 at 04:21:52PM -0400, Mario A. Soto Cordones wrote: How can i get the Client Host name/IP Address for the current connection(session). Is there any function or system table(view) which will these details. In 8.0 you'll be able to use inet_client_addr(). I don't think you could get this info in earlier versions, although I recall seeing an user-defined function that somebody had written. Use a search engine and see if anything comes up. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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] Gborg down?
On Sat, Nov 06, 2004 at 11:09:07AM -0400, Marc G. Fournier wrote: On Sat, 6 Nov 2004, Gaetano Mendola wrote: I used to have a script on my remote server that I was running in BG before to touch the network, that script was bringing up the network if was down for more then 2 minutes. My server is now hosted in my house and I do not need it anymore, I changed it with the no-ip script :-) Until now, I didn't think I'd need such a think :( In almost 12 years of networking, that is one thing that I've never done, unconfigured the main IP ;( Yeah, I think part of the problem was that it was an accident and not something planned. I have had to change the main IP address of a remote server a couple of times, so I used a script like Gaetano's (though I measured time in terms of seconds, usually less than ten, not minutes). But you weren't given the chance :-) Then again, I think I've been lucky so far in that I've never done an 'rm -rf /' either :) Very lucky indeed. I did that (or equivalents like rm -fr /lib) at least two times. Unrelated: I just noticed that you added the mboxen to lists archives. Many many thanks for that! -- Alvaro Herrera ([EMAIL PROTECTED]) La realidad se compone de muchos sueños, todos ellos diferentes, pero en cierto aspecto, parecidos... (Yo, hablando de sueños eróticos) ---(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] Can this be indexed?
Is there a way to create an index that would make this query be efficient and not perform a sequential scan? SELECT count(*) AS count,id FROM sometable GROUP BY id; .. I've considered creating a rule on this table which would put the results of this into another table anytime it is updated, but I thought there might be an easier way. Thanks! - Greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Can this be indexed?
On Saturday November 6 2004 7:34, Net Virtual Mailing Lists wrote: Is there a way to create an index that would make this query be efficient and not perform a sequential scan? SELECT count(*) AS count,id FROM sometable GROUP BY id; .. I've considered creating a rule on this table which would put the results of this into another table anytime it is updated, but I thought there might be an easier way. Since you have no WHERE clause and you want to group by id, I believe pgsql has to scan all id values. Those id values are only fully stored in the table, so I don't think so. Ed ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] superuser equality
I'd like to have a DB client connect using a username ('psuedodba')
different from the creator/owner ('dba') of the DB and its tables, but
still have that username be able to do everything the creator/owner can do
(alter tables, drop databases, etc). It appears that
createuser -d -a psuedodba
works for this purpose. Are there circumstances where 'psuedodba' lacks
powers that 'dba' has to create/destroy/alter/update/insert/delete? Or are
they truly equivalent in powers?
TIA.
Ed
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Can this be indexed?
I am not clear how to use a trigger for this, I will need to look into that It is my understanding that Postgres does not have materialized views though (which I believe would solve this problem nicely) - am I mistaken?... - Greg Net Virtual Mailing Lists wrote: Is there a way to create an index that would make this query be efficient and not perform a sequential scan? SELECT count(*) AS count,id FROM sometable GROUP BY id; Indexes cannot be used for retrieving results... .. I've considered creating a rule on this table which would put the results of this into another table anytime it is updated, but I thought there might be an easier way. I don't think that a rule could come useful in this case. IMHO you could use triggers or a materialized view to store the results and speed up things. Best regards -- Matteo Beccati http://phpadsnew.com/ http://phppgads.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Temporarily disable rule, is this possible?
Hello,
I have a table with a rule that goes something like this:
CREATE OR REPLACE RULE sometable_delete ON DELETE DO delete FROM cache
WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_insert ON INSERT DO delete FROM cache
WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_update ON UPDATE DO delete FROM cache
WHERE tablename='sometable';
And what I have is set of objects which for certain queries will populate
a serialized variable into the cache table, like this:
INSERT INTO cache (tablename, cache_key, datavalue) VALUES ('sometable',
'some_md5_hash', 'serialized_data');
Using this method it is possible for me to just do a:
SELECT datavalue FROM cache WHERE tablename='sometable' AND
cache_key='some_md5_hash';
.. and if I get an empty value for datavalue then execute the query
normally, process it, then store the serialized data back into the cache...
This all works very well for this situation, it has sped up my
application about 20 times for 95% of the requests
The problem I have is there are frequently times I need to update
sometable and not have the rule execute, specifically when I am updating
something in the table which does not affect aggregate results.
Is there a way to cause the rules to be temporarily disabled for these
types of queries?
Thanks!
- Greg
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] superuser equality
Ed L. wrote: createuser -d -a psuedodba works for this purpose. Are there circumstances where 'psuedodba' lacks powers that 'dba' has to create/destroy/alter/update/insert/delete? Or are they truly equivalent in powers? You have created a superuser, who can do anything. This may be even more than the dba user. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Postresql RFD version 2.0 Help Wanted.
Since we have the discussion going, someone mentioned that the group name should be comp.databases.postgresql. I think this is a good name and I'd like to see what everyone thinks of it. There is also the issue of the charter. I would like to get some feed back on what the best charter could be for the revision of the RFD so it is a strong as possible. So the things I'm seeing that people are having the most problems with the current RFD are: 1. The name. They want a better name, and also one that doesn't clash with the bogus (usenet terminology, no disrespect intended) comp.databases.postgresql.general mailing-list newsgroup gateway name space. Someone suggested comp.databases.postgresql. I think that is a good one, and if others agree (please respond in this thread), then that will be one of the changes in the next version of the RFD. 2. The charter. A lot of people expressed feedback that my default charter wasnt very good. I'll agree with them as it was provided as a starting point. I would like the community to craft the charter and the one they decide upon, I will include in the next RFD. If there is anything else that would make the next postgresql RFD stronger, and better, please discuss it in this thread. I also think that a postgresql group should definately be in the big eight under the comp.* hierarchy. The (newly created) alt group should not be a primary place for discussion because it is not guarenteed that all proper usenet servers will carry it, as they would if it were in the big 8. There is also a certain air of respectablity to being in the big 8. It means that it has gone through a process and has passed scrutiny. Then people would find postgresql next to oracle in the comp.databases.* hierarchy! ;-) ---(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] Report Generation
At the risk of asking an ill-formed oft-asked question that's probably in the FAQ, is there any report generation tools that are particularly suited for use with postgres databases? -- % Randy Yates % My Shangri-la has gone away, fading like %% Fuquay-Varina, NC% the Beatles on 'Hey Jude' %%% 919-577-9882% [EMAIL PROTECTED] % 'Shangri-La', *A New World Record*, ELO http://home.earthlink.net/~yatescr ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Mass Import/Generate PKs
I have a CSV file with 400,000 lines of email mailing list information that I need to migrate to a new PostgreSQL database. Each line has all the info I need except a PK (I usually use an int4 column for a PK). If the file were smaller I would probably just use Excel to pop in a PK and then just load into the table... Since Excel chokes on files greater than 65k lines, this won't work. Is there a way to get this done inside psql for instance? Or another route? Thanks, Hunter ---(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] Report Generation
On Saturday November 6 2004 12:04, Randy Yates wrote: At the risk of asking an ill-formed oft-asked question that's probably in the FAQ, is there any report generation tools that are particularly suited for use with postgres databases? Not sure if you're looking for open source products or commercial, but PostgreSQL is accessible via ODBC JDBC, so any tool that speaks those tongues would work in theory. I know Crystal Reports works, as does Excel. Ed ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Trying to get postgres to use an index
Hi, I'm using PostgreSQL 8. I have two tables that I am doing a join on, and the join executes very slowly. The table called Notification has a text field called NotificationID, which is its primary key. The Notification table also has an int4 field called ItemID, and it has an index on the ItemID field. The table called Item has an int4 field called ItemID, which is its primary key. If I do a simple select on Notification using just the ItemID field, the index is used... explain select notificationID from NOTIFICATION n where n.itemID = 12; QUERY PLAN - Index Scan using notification_4_idx on notification n (cost=0.00..129.22 rows=57 width=44) Index Cond: (itemid = 12) This query runs in far less than one second. But if I do a join, the index isn't used... explain select notificationID from NOTIFICATION n, ITEM i where n.itemID = i.itemID; QUERY PLAN -- Hash Join (cost=47162.85..76291.32 rows=223672 width=44) Hash Cond: (outer.itemid = inner.itemid) - Seq Scan on notification n (cost=0.00..12023.71 rows=223671 width=48) - Hash (cost=42415.28..42415.28 rows=741028 width=4) - Seq Scan on item i (cost=0.00..42415.28 rows=741028 width=4) This query takes about 20 seconds to run. I have run vacuum analyze, and it didn't make any difference. I've seen people say that sometimes the query optimizer will decide to not use an index if it thinks that doing a sequential scan would be faster. I don't know if that's what's happening here, but it seems to me that using the index should be much faster than the performance I'm getting here. Does anyone have any suggestions on how to make this query run faster? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Mass Import/Generate PKs
Hunter Hillegas wrote: I have a CSV file with 400,000 lines of email mailing list information that I need to migrate to a new PostgreSQL database. Each line has all the info I need except a PK (I usually use an int4 column for a PK). You could import the file into PostgreSQL and add a primary key column later. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Trying to get postgres to use an index
On Sat, 06 Nov 2004 12:00:02 -0800, Mike Wertheim wrote: Does anyone have any suggestions on how to make this query run faster? Does it help if you decrease the value of random_page_cost? - That value can be changed run-time, from within psql. If you find that a certain, lower value helps, you can make it permanent in postgresql.conf. -- Greetings from Troels Arvin, Copenhagen, Denmark ---(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] Postresql RFD version 2.0 Help Wanted.
Woodchuck Bill wrote: Mike Cox [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED] berlin.de: Since we have the discussion going, someone mentioned that the group name should be comp.databases.postgresql. I think this is a good name and I'd like to see what everyone thinks of it. Much better, especially if you are only proposing a single newsgroup in the hierarchy. Use of the word general is unnecessary, and cumbersome. My original intention was to make the comp.database.postgresql.* groups proper members of the big 8 managed hierarchy. They are considered bogus currently by many proper News providers because they haven't gone through RFD and CFV. I wanted to start slowly and with the most benefitial group, comp.databases.postgresql.general, and then do the others in accordance to traffic interest as measured by google groups. There is resistance in the mailing lists however, even though the groups are already on usenet and are in the managed big 8 name space without RFD and CFV. That is why I am now proposing to change it to comp.databases.postresql so it doesn't clash with the mailing list name space of comp.databases.postgresql.general. If others on the mailing-list/usenet-gateway do want to be proper members of the big 8, then they should speak up. There is also the issue of moving the postgresql mailing list/news gateway to a private namespace like postgresql.*. This would be similar to gnu.* and microsoft.*. This would solve the problem of the postgresql groups residing in a managed hierarchy without going through RFD and CFV, which was the problem I was originally trying to solve. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Mass Import/Generate PKs
That sounds nice and easy... So, I would do something like 'ALTER TABLE' to generate the PK column? What would be the best way to populate it? Is there an area of doco I should be looking at? Thanks, Hunter From: Peter Eisentraut [EMAIL PROTECTED] Date: Sat, 6 Nov 2004 21:21:25 +0100 To: Hunter Hillegas [EMAIL PROTECTED], PostgreSQL [EMAIL PROTECTED] Subject: Re: [GENERAL] Mass Import/Generate PKs Hunter Hillegas wrote: I have a CSV file with 400,000 lines of email mailing list information that I need to migrate to a new PostgreSQL database. Each line has all the info I need except a PK (I usually use an int4 column for a PK). You could import the file into PostgreSQL and add a primary key column later. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Trying to get postgres to use an index
explain select notificationID from NOTIFICATION n, ITEM i where n.itemID = i.itemID; QUERY PLAN -- Hash Join (cost=47162.85..76291.32 rows=223672 width=44) Hash Cond: (outer.itemid = inner.itemid) - Seq Scan on notification n (cost=0.00..12023.71 rows=223671 width=48) - Hash (cost=42415.28..42415.28 rows=741028 width=4) - Seq Scan on item i (cost=0.00..42415.28 rows=741028 width=4) This query takes about 20 seconds to run. Well, you're joining the entire two tables, so yes, the seq scan might be faster. Try your query with enable_seqscan=0 so it'll use an index scan and compare the times. You may be surprised to find that the planner has indeed made the right choice. This query selects 223672 rows, are you surprised it's slow ? What are you trying to do with this query ? Is it executed often ? If you want to select only a subset of this, use an additional where condition and the planner will use the index. ---(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] Mass Import/Generate PKs
the simplest way to do it seems to be adding a SERIAL column to your table, and then adding a primary key constraint: 1)insert data into table 2)ALTER TABLE table ADD id SERIAL; 3)ALTER TABLE table ADD CONSTRAINT table_pk PRIMARY KEY (id); you can check the docs for the SERIAL type: http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-SERIAL On Sat, 2004-11-06 at 17:29, Hunter Hillegas wrote: That sounds nice and easy... So, I would do something like 'ALTER TABLE' to generate the PK column? What would be the best way to populate it? Is there an area of doco I should be looking at? Thanks, Hunter From: Peter Eisentraut [EMAIL PROTECTED] Date: Sat, 6 Nov 2004 21:21:25 +0100 To: Hunter Hillegas [EMAIL PROTECTED], PostgreSQL [EMAIL PROTECTED] Subject: Re: [GENERAL] Mass Import/Generate PKs Hunter Hillegas wrote: I have a CSV file with 400,000 lines of email mailing list information that I need to migrate to a new PostgreSQL database. Each line has all the info I need except a PK (I usually use an int4 column for a PK). You could import the file into PostgreSQL and add a primary key column later. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Mass Import/Generate PKs
On Saturday November 6 2004 2:13, Franco Bruno Borghesi wrote:
the simplest way to do it seems to be adding a SERIAL column to your
table, and then adding a primary key constraint:
1)insert data into table
2)ALTER TABLE table ADD id SERIAL;
3)ALTER TABLE table ADD CONSTRAINT table_pk PRIMARY KEY (id);
You may also need to populate the id column with unique values in between
these two steps with something like
update table set id = nextval('table_id_seq'::text) where id isnull
I don't think SERIAL does that for you.
Ed
---(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] Mass Import/Generate PKs
I've tested it, and the SERIAL type populates the column when you add it
On Sat, 2004-11-06 at 18:56, Ed L. wrote:
On Saturday November 6 2004 2:13, Franco Bruno Borghesi wrote:
the simplest way to do it seems to be adding a SERIAL column to your
table, and then adding a primary key constraint:
1)insert data into table
2)ALTER TABLE table ADD id SERIAL;
3)ALTER TABLE table ADD CONSTRAINT table_pk PRIMARY KEY (id);
You may also need to populate the id column with unique values in between
these two steps with something like
update table set id = nextval('table_id_seq'::text) where id isnull
I don't think SERIAL does that for you.
Ed
attachment: smiley-4.png
signature.asc
Description: This is a digitally signed message part
Re: [GENERAL] Trying to get postgres to use an index
At 10:11 PM +0100 11/6/04, Pierre-Frédéric Caillaud wrote: explain select notificationID from NOTIFICATION n, ITEM i where n.itemID = i.itemID; QUERY PLAN -- Hash Join (cost=47162.85..76291.32 rows=223672 width=44) Hash Cond: (outer.itemid = inner.itemid) - Seq Scan on notification n (cost=0.00..12023.71 rows=223671 width=48) - Hash (cost=42415.28..42415.28 rows=741028 width=4) - Seq Scan on item i (cost=0.00..42415.28 rows=741028 width=4) This query takes about 20 seconds to run. Well, you're joining the entire two tables, so yes, the seq scan might be faster. Try your query with enable_seqscan=0 so it'll use an index scan and compare the times. You may be surprised to find that the planner has indeed made the right choice. This query selects 223672 rows, are you surprised it's slow ? I'm not a SQL guru by any stretch but would a constrained sub-select be appropriate here? e.g. a simple test setup where each record in table test1 has a FK referenced to an entry in test: joels=# \d test Table public.test Column | Type | Modifiers +--+--- id | integer | not null foo| character(3) | Indexes: test_pkey primary key, btree (id) joels=# \d test1 Table public.test1 Column | Type | Modifiers -+-+--- id | integer | not null test_id | integer | Indexes: test1_pkey primary key, btree (id) test1_test_id_idx btree (test_id) Foreign-key constraints: $1 FOREIGN KEY (test_id) REFERENCES test(id) ON DELETE CASCADE joels=# select count(*) from test; count --- 10001 (1 row) joels=# select count(*) from test1; count --- 10001 (1 row) joels=# explain select test_id from test1 t1, test t where t1.test_id = t.id; QUERY PLAN Hash Join (cost=170.01..495.05 rows=10002 width=4) Hash Cond: (outer.test_id = inner.id) - Seq Scan on test1 t1 (cost=0.00..150.01 rows=10001 width=4) - Hash (cost=145.01..145.01 rows=10001 width=4) - Seq Scan on test t (cost=0.00..145.01 rows=10001 width=4) (5 rows) joels=# explain select test_id from test1 t1 where test_id in (select id from test where id = t1.test_id); QUERY PLAN -- Seq Scan on test1 t1 (cost=0.00..15269.02 rows=5001 width=4) Filter: (subplan) SubPlan - Index Scan using test_pkey on test (cost=0.00..3.01 rows=2 width=4) Index Cond: (id = $0) (5 rows) So with the subselect the query planner would use the primary key index on test when finding referencing records in the test1 table. Pierre, I seen the advice to use an additional where condition in certain cases to induce an index scan; how is this done? my 1.2 pennies, -Joel ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Trying to get postgres to use an index
I'm not a SQL guru by any stretch but would a constrained sub-select be appropriate here? Well, you're joining the entire two tables, so yes, the seq scan might be faster. My mistake! When composing the email to state the problem, I accidentally gave a wrong version of the join query. Here is the corrected version, which still has the sequential scan... explain select notificationID from NOTIFICATION n, ITEM i where n.itemID = i.itemID and i.projectID = 12; QUERY PLAN --- --- Hash Join (cost=2237.54..15382.32 rows=271 width=44) Hash Cond: (outer.itemid = inner.itemid) - Seq Scan on notification n (cost=0.00..12023.71 rows=223671 width=48) - Hash (cost=2235.31..2235.31 rows=895 width=4) - Index Scan using item_ix_item_4_idx on item i (cost=0.00..2235.31 rows=895width=4) Index Cond: (projectid = 12) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Here's a question for the SQL guru's out there, which I've been trying to solve for the last couple of hours. There's got to be a solution to this, but somehow I can't find it. Tables: table1 ( uid int PK, uname varchar(64) ) table2 ( uid int FK to table1, xuid int FK to table 1 ) table3 ( uid int FK to table1, yuid int FK to table1 ) There might be more tables of the type like table2 and table3, but I'd already be happy to solve the puzzle with the 3 tables above. Ok, assume table1 is the master table - in my case a table used for login authentication (some columns removed above) table2 and table3 are tables where the uid always references to the uid in table1. The second uid (xuid and yuid in this example) references to another uid record in table1. The problem is that there may or may not be entries in table2 (or table3) referencing a specific uid in their second uid field. Maybe some data: table1: 1 test1 2 test2 3 test3 table2: 1 2 1 3 3 1 table3: 1 2 2 3 3 2 What I want to do in a view is the following resultset: uid uname xuid yuid 1test1 2 2 1test1 3 2test2 3 3test3 1 3test3 2 So basically I want to know which uid is connected to which uid, one relationship per row. So xuid and yuid shall be identical if records exist in both table2 and table3 or the value shall be NULL if a corresponding record can't be found in either table2 or table3. Can anyone here help me out? Thanks a lot UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjVqVjqGXBvRToM4RAhk5AKCjjGc4VilA45PnPZoKluTNYUP6FACgrRQq XFIvkCIJHyz7TvvV/XxL4Lk= =/vxG -END PGP SIGNATURE- ---(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] Trying to get postgres to use an index
[EMAIL PROTECTED] writes: Here is the corrected version, which still has the sequential scan... explain select notificationID from NOTIFICATION n, ITEM i where n.itemID = i.itemID and i.projectID = 12; QUERY PLAN --- --- Hash Join (cost=2237.54..15382.32 rows=271 width=44) Hash Cond: (outer.itemid = inner.itemid) - Seq Scan on notification n (cost=0.00..12023.71 rows=223671 width=48) - Hash (cost=2235.31..2235.31 rows=895 width=4) - Index Scan using item_ix_item_4_idx on item i (cost=0.00..2235.31 rows=895width=4) Index Cond: (projectid = 12) This seems like a perfectly fine plan to me. If it were turned around into a nested indexscan as you suggest, there would need to be 895 indexscans of NOTIFICATION (one for each row retrieved from ITEM) and from your original mail we can see the planner thinks that an indexscan on NOTIFICATION will take about 129 cost units, for a total cost of 129 * 895 = 115455 units (and that's not counting the indexscan on ITEM nor any join overhead). So at least according to these estimates, using the index would take 10x more time than this plan. If you want to see whether this costing is accurate, you could do EXPLAIN ANALYZE for this way and the other (I expect that you'd get the other if you did set enable_seqscan = off). But with a 10x discrepancy I suspect the planner probably did the right thing. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Sorting based on maximum value over several columns
Hello, Lets say I have data like this: value1|value2|value3|value4||value(N) --|--|--|--|| 100 | 200 | 300 | 400 || 10| 20 | | 40 || | 15 | | 16 || 5 | | | || Now I want to sort these based on the maximum value of the data in each row, so for sorting purposes I would have this: sort 400 40 16 5 Any ideas?... I've tried several things but none of them have given me the result I am after Thanks as always! - Greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 If someone knows this it would be great - because I'm still curious how to solve it. However I just remodelled my db structure to eliminate the problem (basically I pulled the several tables into one since each of the table2/table3 tables only has 3 fields) so now I do: table2 ( uid int FK to table1, luid int FK to table1, is_in_table3 boolean, is_in_table4 boolean, . ) this just needs a simple select with a join against table1. UC On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote: Here's a question for the SQL guru's out there, which I've been trying to solve for the last couple of hours. There's got to be a solution to this, but somehow I can't find it. Tables: table1 ( uid int PK, uname varchar(64) ) table2 ( uid int FK to table1, xuid int FK to table 1 ) table3 ( uid int FK to table1, yuid int FK to table1 ) There might be more tables of the type like table2 and table3, but I'd already be happy to solve the puzzle with the 3 tables above. Ok, assume table1 is the master table - in my case a table used for login authentication (some columns removed above) table2 and table3 are tables where the uid always references to the uid in table1. The second uid (xuid and yuid in this example) references to another uid record in table1. The problem is that there may or may not be entries in table2 (or table3) referencing a specific uid in their second uid field. Maybe some data: table1: 1 test1 2 test2 3 test3 table2: 1 2 1 3 3 1 table3: 1 2 2 3 3 2 What I want to do in a view is the following resultset: uid uname xuid yuid 1test1 2 2 1test1 3 2test2 3 3test3 1 3test3 2 So basically I want to know which uid is connected to which uid, one relationship per row. So xuid and yuid shall be identical if records exist in both table2 and table3 or the value shall be NULL if a corresponding record can't be found in either table2 or table3. Can anyone here help me out? Thanks a lot UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjXebjqGXBvRToM4RAq+kAKCyK1bwfRBdVoAvoi0boELr90wSmwCdHUEf p9L9Z1OSHwqvYn+ZnDWSTQw= =Ih7b -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
Devin L. Ganger wrote: On Sat, 06 Nov 2004 11:11:09 -0800, Mike Cox [EMAIL PROTECTED] wrote: Since we have the discussion going, someone mentioned that the group name should be comp.databases.postgresql. I think this is a good name and I'd like to see what everyone thinks of it. I think you're pursuing this backwards, Mike. You should contact the current owner of the present mail-to-news gateway and build some sort of consensus with *him* on what the problem and proposed solution is, before trying to create a solution that will only muddy the waters up even farther. This person made a choice to use Big 8 namespace on his server (and other servers). His server, his rules. Maybe he can be brought to the table to discuss why that isn't the easy fix he thought it was and figure out what the best way to go is from here. That is way beyond my technical scope I'm afraid. I wouldn't know what the correct solution would be. Russ Allbery [EMAIL PROTECTED], seems very knowledgable about this, and I would be pleased if you could mail the postgresql list person about this discussion and Russ's email address. If the postgresql list manager and Russ decide it is a bad idea to be part of the big 8, all the list maintainer has to do is post a message to news.groups and I will not go any farther. Basically if the mailing list-news-gateway doesn't want to be in the big 8 then I'm not going to continue in that process. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
Polarhound wrote: Mike Cox wrote: There is resistance in the mailing lists however, even though the groups are already on usenet and are in the managed big 8 name space without RFD and CFV. This now brings up the question of traffic numbers. Historically, if people are against a NG, or are already happy with their current method of communication, they are unlikely to switch. That is why I am now proposing to change it to comp.databases.postresql so it doesn't clash with the mailing list name space of comp.databases.postgresql.general. If others on the mailing-list/usenet-gateway do want to be proper members of the big 8, then they should speak up. Wasn't the original point of the whole proposal to legitimize the list owner's stupidity in creating the comp.* groups in the first place? Yes it was. It was to make them legitimate so I could post through my usenet provider. I will probably let the proposal die. If I were to create a seperate group, I've realized that then people would be split up into the usenet and mailing list camps. That is not my original intention. My goal was to make the group a proper member of the comp.* hierarchy so that more people could participate through usenet. I cannot handle the volume of email that a mailing list would place on my inbox. I will not be able to use the mailing list, only for dire emergencies where I will have to constantly monitor my inbox so it doesn't overflow. I cannot understand why they wouldn't want to be part of the comp.* hierarchy properly though. They could still be a mail the list for those that wanted it, just like they are doing now! The only change is that it would allow people to post through usenet instead of just being able to get them on non-standard usenet servers who don't follow the big 8 config file. We can make the group moderated too if that's their concern. If Marc or anyone wants to be a moderator, I see why not. They could probably have a script that could allow those on the mailing list to get through faster too. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
Woodchuck Bill wrote: Mike Cox [EMAIL PROTECTED] wrote in news:[EMAIL PROTECTED]: I cannot handle the volume of email that a mailing list would place on my inbox. Ever heard of a digest version? I don't care. Its too much of a hassle to dig through without being able to google groups search it. I'm pretty much done with this anyway. It is a waste of time putting in anymore effort since no one seems to want it. Here are the steps I went through. 1. I tried subscribing to comp.databases.postgresql.general through my usenet provider thinking it was a regular big 8 group. When it wasn't found, I sent a request to my news provider to include it. 2. My news provider emailed me back saying it was bogus and they would not carry it. 3. How could it be bogus I thought. It is a legitimate project with years of history. It has won numerous awards. I did a search on google and found out that it was indeed bogus. Simple enough I thought. Obviously the postgresql folks are way too busy developing the features of postgresql to have time to go through a rigerous process of RFD and CFV which takes about a month to complete. Given that they named their group under the big 8 namespace, it seemed obvious to me that they wanted to be there. I'm not a programmer so I thought I could contribute by going through the process for them. I tried posting to the group but my mail bounced. I searched but I couldn't find out how to make it post to the mailing list. Well, an RFD is a Request for Discussion so what better way to get the ball rolling on what is basically a formality because they are *already* on usenet, just in a bogus way. I would go through the rigerous process and get the group approved, with the knowlegde that the only thing that would change is that they would be a legitimate member of the comp domain. 4. It seems that it was a much bigger issue than just completing a formality, such as reminding someone that their domain name had expired. The big 8 membership seemingly went over badly in private email discussions between the list members from what I've heard from one usenet poster. If it is an ego thing, I've already said that I would let someone take over if that was the issue. I've heard a postive response initially from members of the news.groups group, saying it was a good idea to put them in since they are established, have readership in usenet, and are well liked. Many usenet providers also voiced their support. They have a choice of bowing to user demand and have a bogus group in their comp hierarchy or like my provider, refusing to carry it. Not pretty in either case. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
Devin L. Ganger wrote: On Sat, 06 Nov 2004 18:03:57 -0800, Mike Cox [EMAIL PROTECTED] wrote: Devin L. Ganger wrote: I think you're pursuing this backwards, Mike. You should contact the current owner of the present mail-to-news gateway and build some sort of consensus with *him* on what the problem and proposed solution is, before trying to create a solution that will only muddy the waters up even farther. This person made a choice to use Big 8 namespace on his server (and other servers). His server, his rules. Maybe he can be brought to the table to discuss why that isn't the easy fix he thought it was and figure out what the best way to go is from here. That is way beyond my technical scope I'm afraid. I wouldn't know what the correct solution would be. No one ever said you have to do it *alone*. There are folks here who would be more than willing to *help* you do it, but they're not going to do it for you. Russ Allbery [EMAIL PROTECTED], seems very knowledgable about this, and I would be pleased if you could mail the postgresql list person about this discussion and Russ's email address. Russ is a busy person; don't be so quick to volunteer his time for him unless you're damn sure you have his permission. Even if he were not a busy person, most people consider it to be rude to be volunteered without their consent. Basically if the mailing list-news-gateway doesn't want to be in the big 8 then I'm not going to continue in that process. Which is fair, but since you're the person who *did* kick this off, you should probably be the person to email the owner and ask him if he would be willing to have a conversation with you about the best way to proceed from here. I'm frankly scared to talk to him. I'm afraid he might scream at me or something for starting this off without talking to him first. I would be pretty upset if someone just tried to promote my mailing-list/news-gateway to the big 8 without consulting me first. I do have ignorance as an excuse since until a few days ago I didn't even know how usenet group creation even worked. Heck, I didn't even know that the postgresql groups were connected to the mailing list!!! I was like a regular person trying to litigate a complex legal case pro se. Now I have egg on my face for biting off more than I could chew. From what I've heard from someone on this newsgroup is that it didn't fly over too well in the mailing list member's private emails. I'm assuming that the list owner was upset. I seriously doubt I have the political capital to complete the process. I'm sure if someone else decides it is a worthy cause, they will at least have the precedent of what I did to guide them. I tried googling to see if anyone had attempted to make the postgresql groups non-bogus, but there were no pervious efforts. You should also probably take a step back and contact the group mentor list [EMAIL PROTECTED] and get some advice and participation from the folks there, no matter which way you intend to pursue this. Again, there are people who will help, but you need to be willing to run point on this even though it's likely not going to be the slam-dunk you thought it would be initially. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
On Sat, 6 Nov 2004, Mike Cox wrote: 1. I tried subscribing to comp.databases.postgresql.general through my usenet provider thinking it was a regular big 8 group. When it wasn't found, I sent a request to my news provider to include it. Most modern news readers allow for multiple news server ... just point yours at news.postgresql.org, and you can read from there, which has always been the case ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Visual Designer in linux?
On Sat, Nov 06, 2004 at 10:32:30PM -0500, Eric wrote: Is there a visual designer (open source) in linux for database? I would like to developp my data model on the computer... I see dia with uml library but... it won't export to SQL. You can have UML diagrams exported to SQL with some Perl or Python program whose URL you can find on Dia's homepage. dia2sql or something like that. It's damn easy. Hmm ... I think I got the URL somewhere ... yes, you are lucky: http://tedia2sql.tigris.org/ -- Alvaro Herrera ([EMAIL PROTECTED]) Si quieres ser creativo, aprende el arte de perder el tiempo ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SQL question
I'm afraid, I'm not used to SQL92 join syntax and almost all my experience is in Oracle but how about: SELECT t1.uid , t1.xname , t2.uid , t3.uid FROM table1 t1 INNER JOIN table2 t2 ON t1.uid = t2.uid INNER JOIN table3 t3 ON t2.uid = t3.uid UNION SELECT t1.uid , t1.xname , t2.uid , NULL FROM table1 t1 INNER JOIN table2 t2 ON t1.uid = t2.uid WHERE NOT EXISTS (SELECT NULL FROM table3 t3 WHERE t3.uid = t1.uid ) UNION SELECT t1.uid , t1.xname , NULL , t3.uid FROM table1 t1 INNER JOIN table3 t3 ON t1.uid = t3.uid WHERE NOT EXISTS (SELECT NULL FROM table2 t2 WHERE t2.uid = t3.uid ) Perhaps there was a solution using outer joins and case statements within the SELECT clause. Perhaps there is also a solution using subselects in the SELECT clause. However, this is all I can do for tonight. Vincent - Original Message - From: Uwe C. Schroeder [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, November 06, 2004 3:13 PM Subject: [GENERAL] SQL question -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Here's a question for the SQL guru's out there, which I've been trying to solve for the last couple of hours. There's got to be a solution to this, but somehow I can't find it. Tables: table1 ( uid int PK, uname varchar(64) ) table2 ( uid int FK to table1, xuid int FK to table 1 ) table3 ( uid int FK to table1, yuid int FK to table1 ) There might be more tables of the type like table2 and table3, but I'd already be happy to solve the puzzle with the 3 tables above. Ok, assume table1 is the master table - in my case a table used for login authentication (some columns removed above) table2 and table3 are tables where the uid always references to the uid in table1. The second uid (xuid and yuid in this example) references to another uid record in table1. The problem is that there may or may not be entries in table2 (or table3) referencing a specific uid in their second uid field. Maybe some data: table1: 1 test1 2 test2 3 test3 table2: 1 2 1 3 3 1 table3: 1 2 2 3 3 2 What I want to do in a view is the following resultset: uid uname xuid yuid 1test1 2 2 1test1 3 2test2 3 3test3 1 3test3 2 So basically I want to know which uid is connected to which uid, one relationship per row. So xuid and yuid shall be identical if records exist in both table2 and table3 or the value shall be NULL if a corresponding record can't be found in either table2 or table3. Can anyone here help me out? Thanks a lot UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjVqVjqGXBvRToM4RAhk5AKCjjGc4VilA45PnPZoKluTNYUP6FACgrRQq XFIvkCIJHyz7TvvV/XxL4Lk= =/vxG -END PGP SIGNATURE- ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
