Re: [SQL] Problems invoking psql. Help please.
Uz.ytkownik Hugh Esco napisa?:
Thank you so much, Mallah, Tomasz Myrta, Luis Sousa,
Achilleus Mantzios, Tom Lane, Bill Eaton and Oliver Elphick.
I have chmod 755 my readpgenv file, and then copied the
shell script suggested by Tomasz Myrta into that file,
yielding these results:
biko:/usr/lib/postgresql/bin# cd /usr/bin
biko:/usr/bin# ./psql -U postgres
No database specified
It's like it should be.
biko:/usr/bin# ./psql -U postgres template1
/usr/lib/postgresql/bin/readpgenv: ./etc/postgresql/postgresql.env: No
such file
or directory
How should that file read?
Could not execv /usr/lib/postgresql/bin/psql
If you still want to fight with your postgres, here is content of my
(it's default) postgresql.env:
>>BEGIN<<
[ -r /etc/postgresql/postmaster.conf ] &&
. /etc/postgresql/postmaster.conf
PGDATA=${POSTGRES_DATA:-/var/lib/postgres/data}
PGLIB=/usr/lib/postgresql/lib
PGACCESS_HOME=/usr/lib/postgresql/share/pgaccess
PGHOST=
export PGLIB PGDATA PGACCESS_HOME PGHOST
>>END<<
I think you should just reinstall postgres from .deb package as said Tom
Lane. I had never troubles like yours with default .deb package.
Then I changed user from root to postgres,
under which this database had been created:
Psql works also as root, but you should run it as postgres for security
reason.
Tomasz Myrta
---(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: [SQL] Problems invoking psql. Help please.
Tom Lane wrote: Hugh Esco <[EMAIL PROTECTED]> writes: Any ideas on what my next steps should be would be greatly appreciated. Start over: delete your PG installation and reinstall the Debian package. It seems very clear that you've got an incomplete package. regards, tom lane I agree with Tom Lane. Probably is the best thing to do. When you install all it over, in theory, all the problems will solve by them selfs. Luis Sousa smime.p7s Description: S/MIME Cryptographic Signature
[SQL] index usage for query
Hi, I have a question about index usage in PostgreSQL 7.2.1 on Solaris. I have three tables like this: port: element text portnoint primary key: element, portno index: element port_s: element text portnoint sname text pri int primary key: element, portno, sname index: element index: element, portno port_s_p: element text portnoint sname text pname text value text primary key: element, portno, sname, pname index: element, portno, sname At first, I did the query like this: SELECT po.portno,po.element,s.sname,pri,p.pname,value FROM port po, port_s s LEFT OUTER JOIN port_s_p p USING (element, portno, sname) WHERE po.element=s.element AND po.portno=s.portno AND po.element LIKE 'lab-el1' ORDER BY po.element,po.portno,pri,s.sname; And got this query plan using EXPLAIN: Sort (cost=43690.55..43690.55 rows=26 width=157) -> Merge Join (cost=41757.20..43689.93 rows=26 width=157) -> Index Scan using idx_p_element on port po (cost=0.00..72.65 rows=22 width=67) -> Materialize (cost=42682.50..42682.50 rows=370111 width=90) -> Merge Join (cost=41757.20..42682.50 rows=370111 width=90) -> Sort (cost=41755.93..41755.93 rows=370111 width=42) -> Seq Scan on port_s s (cost=0.00..7525.11 rows=370111 width=42) -> Sort (cost=1.27..1.27 rows=10 width=48) -> Seq Scan on port_s_p p (cost=0.00..1.10 rows=10 width=48) EXPLAIN What I don't understand is why the index port_s(element,portno) is not used here. If I changed the query to: SELECT po.portno,po.element,s.sname,pri,p.pname,value FROM port po INNER JOIN port_s s USING (element, portno) LEFT OUTER JOIN port_s_p p USING (element, portno, sname) WHERE po.element LIKE 'lab-el1' ORDER BY po.element,po.portno,pri,s.sname; I.e. using INNER JOIN instead of the WHERE case to join port and port_s. This query gave this plan: NOTICE: QUERY PLAN: Sort (cost=239.17..239.17 rows=26 width=157) -> Merge Join (cost=1.27..238.55 rows=26 width=157) -> Nested Loop (cost=0.00..237.19 rows=26 width=109) -> Index Scan using idx_p_element on port po (cost=0.00..72.65 rows=22 width=67) -> Index Scan using idx_p_s_element_portno on port_s s (cost=0.00..7.38 rows=1 width=42) -> Sort (cost=1.27..1.27 rows=10 width=48) -> Seq Scan on port_s_p p (cost=0.00..1.10 rows=10 width=48) EXPLAIN Can someone explain why the index is used in the second query, but not in the first? Greetings, Tomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] index usage for query
On Tuesday 19 Nov 2002 9:10 am, Tomas Berndtsson wrote: > Hi, > > I have a question about index usage in PostgreSQL 7.2.1 on Solaris. > > At first, I did the query like this: > > SELECT po.portno,po.element,s.sname,pri,p.pname,value > FROM port po, port_s s LEFT OUTER JOIN port_s_p p > USING (element, portno, sname) WHERE po.element=s.element > AND po.portno=s.portno AND po.element LIKE 'lab-el1' > ORDER BY po.element,po.portno,pri,s.sname; > > And got this query plan using EXPLAIN: > > Sort (cost=43690.55..43690.55 rows=26 width=157) > -> Merge Join (cost=41757.20..43689.93 rows=26 width=157) > -> Index Scan using idx_p_element on port po (cost=0.00..72.65 > rows=22 width=67) -> Materialize (cost=42682.50..42682.50 rows=370111 > width=90) -> Merge Join (cost=41757.20..42682.50 rows=370111 width=90) -> > Sort (cost=41755.93..41755.93 rows=370111 width=42) -> Seq Scan on > port_s s (cost=0.00..7525.11 rows=370111 width=42) -> Sort > (cost=1.27..1.27 rows=10 width=48) > -> Seq Scan on port_s_p p (cost=0.00..1.10 > rows=10 width=48) > What I don't understand is why the index port_s(element,portno) > is not used here. Well - the estimate of 370111 rows shows where we have our problem. An explicit JOIN overrides the planner's normal order of operation. Looking at the plan, I think what's happening is that OUTER JOIN is being done first to "post_s s". Then it does your implicit join. > If I changed the query to: > > SELECT po.portno,po.element,s.sname,pri,p.pname,value > FROM port po INNER JOIN port_s s USING (element, portno) LEFT > OUTER JOIN port_s_p p USING (element, portno, sname) > WHERE po.element LIKE 'lab-el1' > ORDER BY po.element,po.portno,pri,s.sname; > > I.e. using INNER JOIN instead of the WHERE case to join port and > port_s. This query gave this plan: > > NOTICE: QUERY PLAN: > > Sort (cost=239.17..239.17 rows=26 width=157) > -> Merge Join (cost=1.27..238.55 rows=26 width=157) > -> Nested Loop (cost=0.00..237.19 rows=26 width=109) > -> Index Scan using idx_p_element on port po > (cost=0.00..72.65 rows=22 width=67) -> Index Scan using > idx_p_s_element_portno on port_s s (cost=0.00..7.38 rows=1 width=42) -> > Sort (cost=1.27..1.27 rows=10 width=48) > -> Seq Scan on port_s_p p (cost=0.00..1.10 rows=10 width=48) Here you only have one join in two parts "port po" - "port_s s" and then "port_s_p p". It's the overriding that's hitting you here. If you've got time could you rewrite it so that all the joins are implicit and let us know what that does to the plan? -- Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] create index
i don't know how to create the index about the following statement. SELECT b.screen_id AS screen_id, b.name AS screen_name FROM tbl_showlog AS a CROSS JOIN tbl_screen AS b CROSS JOIN tbl_company AS c WHERE a.screen_id = b.screen_id AND b.company_id = c.company_id AND c.company_id = 1 AND c.is_active = 1 GROUP BY b.screen_id, b.name ORDER BY b.screen_id many thx! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] slow group by query
Is there any way to make this query faster? I have indexes on year,
msa_code, and sic. I've also tried it with
an index on the combined group by columns. I've made both sort_mem and
shared_buffers bigger, but still this query
takes 40 seconds when I select 4 msa_codes and 7 minutes when I select 40
msa_codes. Would it just be better
to transpose the table to begin with and avoid the group by all together?
SELECT b.msa_code, b.sic, b.own, b.ind_div,
(select emp from tbl_bls_msa as bls
where bls.year = '1975' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1975,
(select emp from tbl_bls_msa as bls
where bls.year = '1976' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1976,
(select emp from tbl_bls_msa as bls
where bls.year = '1977' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1977,
(select emp from tbl_bls_msa as bls
where bls.year = '1978' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1978,
(select emp from tbl_bls_msa as bls
where bls.year = '1990' and bls.msa_code = b.msa_code and bls.sic = b.sic
and bls.own = b.own and bls.ind_div = b.ind_div) AS emp1990
FROM tbl_bls_msa AS b
where msa_code in ('1680','1640','0040','0120','0080')
GROUP BY b.msa_code, b.sic, b.ind_div, b.own ;
NOTICE: QUERY PLAN:
Group (cost=635.97..695.18 rows=592 width=22)
-> Sort (cost=635.97..635.97 rows=5921 width=22)
-> Index Scan using msa_code_tbl_bls_msa_key,
msa_code_tbl_bls_msa_key,
msa_code_tbl_bls_msa_key, msa_code_tbl_bls_msa_key,
msa_code_tbl_bls_msa_key on
tbl_bls_msa b (cost=0.00..264.99 rows=5921 width=22)
SubPlan
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
-> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa
bls (cost=0.00
..53.71 rows=1 width=10)
Thanks.
Ellen
---
---(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: [SQL] Proposal of hierarchical queries, a la Oracle
Oleg, i've read that info. mine CONNECT BY patch is based on internal pg's query processing, from that point it's a kind of ORDER BY or GROUP BY clause. Index access is implemented by lower layer. regards, --- .evgen On Sat, 16 Nov 2002, Oleg Bartunov wrote: > Evgen, > > read info about ltree module > http://www.sai.msu.su/~megera/postgres/gist/ltree/ > > Do you have implemented indexed access ? > > regards, > > Oleg > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL]
HI,What can I do to solve this error; I have two tables, as below tabel: works empnum | pnum | hours +--+--- E1 | P1 | 40 E1 | P2 | 20 E1 | P3 | 80 E1 | P4 | 20 E1 | P5 | 12 E1 | P6 | 12 E2 | P1 | 40 E2 | P2 | 80 E3 | P2 | 20 E4 | P2 | 20 E4 | P4 | 40 E4 | P5 | 80(12 rows) table:proj pnum | pname | ptype | budget | city --+--+++- P1 | MXSS | Design | 1 | Deale P2 | CALM | Code | 3 | Vienna P3 | SDP | Test | 3 | Tampa P4 | SDP | Design | 2 | Deale P5 | IRM | Test | 1 | Vienna P6 | PAYR | Design | 5 | Deale (6 rows) I want to do this query,but system returns "ERROR:Aggregates not allowd in WHERE clause" please help me,do the query: SELECT PNUM, SUM(HOURS) FROM WORKS GROUP BY PNUM HAVING EXISTS (SELECT PNAME FROM PROJ WHERE PROJ.PNUM = WORKS.PNUM AND SUM(WORKS.HOURS) > PROJ.BUDGET / 200); I think this query should return two tuples: p1/80 p5/92 ¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡îËïÓêÉòÑô¶«ÈíÖмä¼þ¼¼Êõ¹«Ë¾ Êý¾Ý¹ÜÀíÊÂÒµ²¿E-mail: [EMAIL PROTECTED]¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î¡î
Re: [SQL] Proposal of hierarchical queries, a la Oracle
Joe, i've made it already,as send first 'Proposal ...' message, but found a small bug. within nearest days i'll fix it, and post the patch to pgsql-patches. i've described why i can't use connectby() in message to Josh Berkus. i'll enhance CONNECT BY as clause, but not as function. i think it's more 'right' approach to make it:) regards, --- .evgen On Sat, 16 Nov 2002, Joe Conway wrote: > Josh Berkus wrote: > > Evgren, > > > > > >>I want to propose the patch for adding the hierarchical queries > >>posibility. > >>It allows to construct queries a la Oracle for ex: > >>SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; > > > > > > You'll be thrilled to know that this has already been implemented in > > PostgreSQL 7.3, as a /contrib module, by Joe Conway. > > > I have it on my personal TODO to approach this for 7.4, but I'll be happy to > focus on other things if you'd like to take this on. The connectby() function > in contrib/tablefunc could be a good starting point for you. > > Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Proposal of hierarchical queries, a la Oracle
-Josh, i don't be so thrilled:), with current implementation of /contrib/tablefunc mine problem sill remains: i need to sort tree leaves in some order, independent of tree itself. this can be fixed (in tablefunc) by addition to connectby() one more field like 'order_by' or so. but it will be more difficult to scale. for ex. if i need to make subquery with connect by, with tablefunc i need to create view to do so. what if i need several different such queries? of course this can be done with tablefunc, but IMHO mine approach is a bit more 'native':) --- .evgen On Sat, 16 Nov 2002, Josh Berkus wrote: > Evgren, > > > I want to propose the patch for adding the hierarchical queries > > posibility. > > It allows to construct queries a la Oracle for ex: > > SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; > > You'll be thrilled to know that this has already been implemented in > PostgreSQL 7.3, as a /contrib module, by Joe Conway. > > Download 7.3b5 now if you can't wait; Joe would like more people to > test his module, anyway. > > -Josh Berkus > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Proposal of hierarchical queries, a la Oracle
On Tue, 19 Nov 2002, Evgen Potemkin wrote: > Oleg, > > i've read that info. mine CONNECT BY patch is based on internal pg's query >processing, > from that point it's a kind of ORDER BY or GROUP BY clause. Index access is >implemented by > lower layer. ok. I see. > > regards, > --- > .evgen > > On Sat, 16 Nov 2002, Oleg Bartunov wrote: > > > Evgen, > > > > read info about ltree module > > http://www.sai.msu.su/~megera/postgres/gist/ltree/ > > > > Do you have implemented indexed access ? > > > > regards, > > > > Oleg > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] create index
Uz.ytkownik [EMAIL PROTECTED] napisa?: i don't know how to create the index about the following statement. SELECT b.screen_id AS screen_id, b.name AS screen_name FROM tbl_showlog AS a CROSS JOIN tbl_screen AS b CROSS JOIN tbl_company AS c WHERE a.screen_id = b.screen_id AND b.company_id = c.company_id AND c.company_id = 1 AND c.is_active = 1 GROUP BY b.screen_id, b.name ORDER BY b.screen_id Change your query to explicit joins. It looks much better and explains directly your problem. Example: SELECT screen_id, B.name as screen_name FROM tbl_company C join tbl_screen B using (company_id) join tbl_showlog A using (screen_id) WHERE company_id=1 and is_active=1 I don't know how many records do you have, but try to create indexes: 1. tbl_company -> company_id 2. tbl_screen -> company_id 3. tbl_showlog -> screen_id Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL]
Uz.ytkownik sun yu napisa?: > HI,What can I do to solve this error; > I have two tables, as below > tabel: works > > empnum | pnum | hours > +--+--- > E1 | P1 |40 > E1 | P2 |20 > E1 | P3 |80 > E1 | P4 |20 > E1 | P5 |12 > E1 | P6 |12 > E2 | P1 |40 > E2 | P2 |80 > E3 | P2 |20 > E4 | P2 |20 > E4 | P4 |40 > E4 | P5 |80 > (12 rows) > > table:proj > pnum |pname | ptype | budget | city > --+--+++- > P1 | MXSS | Design | 1 | Deale > P2 | CALM | Code | 3 | Vienna > P3 | SDP | Test | 3 | Tampa > P4 | SDP | Design | 2 | Deale > P5 | IRM | Test | 1 | Vienna > P6 | PAYR | Design | 5 | Deale > (6 rows) > > I want to do this query,but system returns "ERROR:Aggregates not > allowd in WHERE clause" > please help me,do the query: > >SELECT PNUM, SUM(HOURS) FROM WORKS > GROUP BY PNUM > HAVING EXISTS (SELECT PNAME FROM PROJ > WHERE PROJ.PNUM = WORKS.PNUM AND >SUM(WORKS.HOURS) > PROJ.BUDGET / 200); > I think this query should return two tuples: > p1/80 > p5/92 Try this: select pnum, sum(hours) from proj join works using (pnum) group by pnum having sum(hours)>budget/200; Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] slow group by query
On Mon, 18 Nov 2002, Ellen Cyran wrote:
> Is there any way to make this query faster? I have indexes on year,
> msa_code, and sic. I've also tried it with
> an index on the combined group by columns. I've made both sort_mem and
> shared_buffers bigger, but still this query
> takes 40 seconds when I select 4 msa_codes and 7 minutes when I select 40
> msa_codes. Would it just be better
> to transpose the table to begin with and avoid the group by all together?
>
> SELECT b.msa_code, b.sic, b.own, b.ind_div,
> (select emp from tbl_bls_msa as bls
> where bls.year = '1975' and bls.msa_code = b.msa_code and bls.sic = b.sic
> and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1975,
> (select emp from tbl_bls_msa as bls
> where bls.year = '1976' and bls.msa_code = b.msa_code and bls.sic = b.sic
> and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1976,
> (select emp from tbl_bls_msa as bls
> where bls.year = '1977' and bls.msa_code = b.msa_code and bls.sic = b.sic
> and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1977,
> (select emp from tbl_bls_msa as bls
> where bls.year = '1978' and bls.msa_code = b.msa_code and bls.sic = b.sic
> and bls.own = b.own and bls.ind_div = b.ind_div ) AS emp1978,
> (select emp from tbl_bls_msa as bls
> where bls.year = '1990' and bls.msa_code = b.msa_code and bls.sic = b.sic
> and bls.own = b.own and bls.ind_div = b.ind_div) AS emp1990
> FROM tbl_bls_msa AS b
> where msa_code in ('1680','1640','0040','0120','0080')
> GROUP BY b.msa_code, b.sic, b.ind_div, b.own ;
Have you tried doing the subqueries in from? Right now you're running
each subquery once for each output row AFAICS.
Maybe something like (doing only one year for example - and using a
postgres extension), would work...
select distinct on (b.msa_code, b.sic, b.own, b.ind_div)
b.msa_code, b.sic, b.own, b.ind_div, y1975.emp1975 from
tbl_bls_msa as b left outer join (select emp from tbl_bls_mas as bls
where bls.year='1975') y1975 on (b.msa_code=y1975.msa_code and
b.sic=y1975.sic and b.own=y1975.own and b.ind_div=y1975.ind_div)
where msa_code in ('1680', '1640', '0040', '0120', '0080');
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] slow group by query
Ellen Cyran <[EMAIL PROTECTED]> writes: > Is there any way to make this query faster? I have indexes on year, > msa_code, and sic. I've also tried it with > an index on the combined group by columns. I've made both sort_mem and > shared_buffers bigger, but still this query > takes 40 seconds when I select 4 msa_codes and 7 minutes when I select 40 > msa_codes. Hm, have you vacuum analyzed lately? Those cost estimates seem awfully low for a query that is taking 40 sec. Also, if you're using 7.2 (which you should be ;-)) then showing EXPLAIN ANALYZE results would be more useful than plain EXPLAIN. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how can I improve the speed of this query
Problem could be the SeqScan on visitor (very last line of explain). This appears to be coming from the last join in your query. Does an index on ID exist on Visitor table? Does changing: WHERE "VisitorExtra"."ID"="Visitor"."ID" AND "VisitorExtra"."ID"= 325903; to WHERE "VisitorExtra"."ID"="Visitor"."ID" AND "Visitor"."ID"= 325903; have any effect? Why do you use "From VisitorExtra" in first select when no result fields are in the table? Why not replace all references to "VisitorExtra"."ID"to "Visitor"."ID"? Frank At 03:33 PM 11/15/02, Peter T. Brown wrote: Hi-- I have this rather long complex query that takes really long to complete (please see below). It seems like I ought to improve the speed somehow. I don't understand, for example, what the query planner is doing when it says "Hash" and why this appears to take so long. And since I have a key for Visitor.ID, I don't understand why its doing a sequential scan on that table... Any advice would be greatly appreciated! Thanks Peter EXPLAIN SELECT "Visitor"."Created", "Visitor"."Updated", "Tidbit"."ID", "ProgramEvent"."ID", "Visitor"."Email", "Interest"."ID", "VisitorInternetDeviceAssoc"."ID", "Referral"."ID" FROM "VisitorExtra" LEFT OUTER JOIN Tidbit" ON "VisitorExtra"."ID"="Tidbit"."VisitorID" LEFT OUTER JOIN "ProgramEvent" ON "VisitorExtra"."ID"="ProgramEvent"."VisitorID" LEFT OUTER JOIN "Interest" ON "VisitorExtra"."ID"="Interest"."VisitorID" LEFT OUTER JOIN "VisitorInternetDeviceAssoc" ON "VisitorExtra"."ID"="VisitorInternetDeviceAssoc"."VisitorID" LEFT OUTER JOIN "Referral" ON "VisitorExtra"."ID"="Referral"."FromVisitorID","Visitor" WHERE "VisitorExtra"."ID"="Visitor"."ID" AND "VisitorExtra"."ID"= 325903; NOTICE: QUERY PLAN: Hash Join (cost=14584.37..59037.79 rows=57747 width=76) -> Merge Join (cost=0.00..36732.65 rows=57747 width=44) -> Merge Join (cost=0.00..29178.16 rows=10681 width=36) -> Nested Loop (cost=0.00..10505.74 rows=6674 width=28) -> Nested Loop (cost=0.00..435.29 rows=177 width=20) -> Nested Loop (cost=0.00..15.70 rows=55 width=12) -> Index Scan using VisitorExtra_pkey on VisitorExtra (cost=0.00..3.01 rows=1width=4) -> Index Scan using Tidbit_VisitorID_key on Tidbit (cost=0.00..12.67 rows=2 width=8) -> Index Scan using ProgramEvent_VisitorID_key on ProgramEvent(cost=0.00..7.57 rows=2 width=8) -> Index Scan using Interest_VisitorID_key on Interest (cost=0.00..56.66 rows=19 width=8) -> Index Scan using VisitorInternetDeviceAssoc_Visi on VisitorInternetDeviceAssoc (cost=0.00..16402.90 rows=174887 width=8) -> Index Scan using Referral_FromVisitorID_key on Referral (cost=0.00..6323.41 rows=87806 width=8) -> Hash (cost=6061.79..6061.79 rows=317379 width=32) -> Seq Scan on Visitor (cost=0.00..6061.79 rows=317379 width=32) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] slow group by query
I have vacuum analyzed recently. I do it after a large number of inserts and after indexing. This database is in the development stages so there is a lot of data loading at this time. We are also using 7.2.3. Here is the explain analyze: Group (cost=637.18..696.52 rows=593 width=22) (actual time=982.67..67581.85 row s=435 loops=1) -> Sort (cost=637.18..637.18 rows=5934 width=22) (actual time=833.27..844.78 rows=6571 loops=1) -> Index Scan using msa_code_tbl_bls_msa_key, msa_code_tbl_bls_msa_key, msa_code_tbl_bls_msa_key, msa_code_tbl_bls_msa_key, msa_code_tbl_bls_msa_key on tbl_bls_msa b (cost=0.00..265.30 rows=5934 width=22) (actual time=0.80..367.64 rows=6571 loops=1) SubPlan -> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa bls (cost=0.00 ..53.80 rows=1 width=10) (actual time=16.46..30.36 rows=1 loops=435) -> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa bls (cost=0.00 ..53.80 rows=1 width=10) (actual time=16.54..30.41 rows=1 loops=435) -> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa bls (cost=0.00 ..53.80 rows=1 width=10) (actual time=16.53..30.69 rows=1 loops=435) -> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa bls (cost=0.00 ..53.80 rows=1 width=10) (actual time=16.19..30.66 rows=1 loops=435) -> Index Scan using msa_code_tbl_bls_msa_key on tbl_bls_msa bls (cost=0.00 ..53.80 rows=1 width=10) (actual time=16.19..30.80 rows=1 loops=435) Total runtime: 67589.69 msec EXPLAIN Thanks for the help. At 10:21 AM 11/19/2002 -0500, Tom Lane wrote: Ellen Cyran <[EMAIL PROTECTED]> writes: > Is there any way to make this query faster? I have indexes on year, > msa_code, and sic. I've also tried it with > an index on the combined group by columns. I've made both sort_mem and > shared_buffers bigger, but still this query > takes 40 seconds when I select 4 msa_codes and 7 minutes when I select 40 > msa_codes. Hm, have you vacuum analyzed lately? Those cost estimates seem awfully low for a query that is taking 40 sec. Also, if you're using 7.2 (which you should be ;-)) then showing EXPLAIN ANALYZE results would be more useful than plain EXPLAIN. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems invoking psql. Help please.
I have reinstalled before. I wonder though, how I ensure that I have cleanly un-installed it first, so that I leave no residue from the previously botched installation around to mess things up the next time. -- Hugh Esco At 09:03 AM 11/19/02 +, Luis Sousa wrote: Tom Lane wrote: Start over: delete your PG installation and reinstall the Debian package. It seems very clear that you've got an incomplete package. regards, tom lane I agree with Tom Lane. Probably is the best thing to do. When you install all it over, in theory, all the problems will solve by them selfs. Luis Sousa ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] slow group by query
I had to modify your query somewhat, but the one below that is pretty much
the same took
about 12 seconds so once I run it on five years it will take just as
long. Thanks for the
suggestion though.
select distinct on (b.msa_code, b.sic, b.own, b.ind_div)
b.msa_code, b.sic, b.own, b.ind_div, y1975.emp from
tbl_bls_msa as b left outer join (select msa_code, sic, own, ind_div, emp
from tbl_bls_msa as bls
where bls.year='1975' ) as y1975
on (b.msa_code=y1975.msa_code and
b.sic=y1975.sic and b.own=y1975.own and b.ind_div=y1975.ind_div)
where b.msa_code in ('1680', '1640', '0040', '0120', '0080');
I would be interested in knowing more about what postgres extensions are
available. Where
are those documented at?
Have you tried doing the subqueries in from? Right now you're running
each subquery once for each output row AFAICS.
Maybe something like (doing only one year for example - and using a
postgres extension), would work...
select distinct on (b.msa_code, b.sic, b.own, b.ind_div)
b.msa_code, b.sic, b.own, b.ind_div, y1975.emp1975 from
tbl_bls_msa as b left outer join (select emp from tbl_bls_mas as bls
where bls.year='1975') y1975 on (b.msa_code=y1975.msa_code and
b.sic=y1975.sic and b.own=y1975.own and b.ind_div=y1975.ind_div)
where msa_code in ('1680', '1640', '0040', '0120', '0080');
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] slow group by query
Ellen Cyran <[EMAIL PROTECTED]> writes:
> Here is the explain analyze:
> Group (cost=637.18..696.52 rows=593 width=22) (actual time=982.67..67581.85
>rows=435 loops=1)
>-> Sort (cost=637.18..637.18 rows=5934 width=22) (actual time=833.27..844.78
>rows=6571 loops=1)
Well, we don't have to read any further than that to see that all the
time is being spent in the final Group step --- and since grouping 6500
rows is surely not taking very long, the true cost must be in the
evaluation of the SELECT's output targetlist (which the estimated costs
ignore, since the planner can't do much to change it). In other words,
what's hurting you are those subselects in the SELECT list. You're
doing two thousand separate subselects (435*5) --- evidently they take
about 30 msec apiece, which isn't that bad by itself, but it adds up.
What you've basically got here is what Joe Celko calls a "crosstab
by subqueries" (_SQL For Smarties_, section 23.6.3). You might want
to buy his book, which shows several other ways to do crosstab queries,
but the one that seems most directly useful is to GROUP BY and use
aggregate functions to extract the values you want in each crosstab
column:
SELECT msa_code, sic, own, ind_div,
max(case when year = '1975' then emp else null end) as emp1975,
max(case when year = '1976' then emp else null end) as emp1976,
max(case when year = '1977' then emp else null end) as emp1977,
max(case when year = '1978' then emp else null end) as emp1978,
max(case when year = '1990' then emp else null end) as emp1990
FROM tbl_bls_msa
WHERE msa_code in ('1680','1640','0040','0120','0080')
GROUP BY msa_code, sic, ind_div, own ;
If I understand your data schema correctly, only one row in a
msa_code, sic, ind_div, own group will have a particular year
value, so the case/max structure extracts that value, or gives
NULL if there's no such row. (MIN would have worked as well;
with a numeric field you could use SUM too.)
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems invoking psql. Help please.
Did you install your package using apt-get ? All the instalations that I do are using those tools from debian. You have to see all the packages that you have instaled in your computer like: dpkg -l | grep postgresql ii postgresql 7.2.1-2Object-relational SQL database, descended fr ii postgresql-cli 7.2.1-2Front-end programs for PostgreSQL ii postgresql-con 7.2.1-2Additional facilities for PostgreSQL Then you do: dpkg --purge postgresql. You can now run the first command to see if something is still installed. If some are instaled, the you remove it using again dpkg --purge. Luis Sousa Hugh Esco wrote: I have reinstalled before. I wonder though, how I ensure that I have cleanly un-installed it first, so that I leave no residue from the previously botched installation around to mess things up the next time. -- Hugh Esco At 09:03 AM 11/19/02 +, Luis Sousa wrote: Tom Lane wrote: Start over: delete your PG installation and reinstall the Debian package. It seems very clear that you've got an incomplete package. regards, tom lane I agree with Tom Lane. Probably is the best thing to do. When you install all it over, in theory, all the problems will solve by them selfs. Luis Sousa ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) smime.p7s Description: S/MIME Cryptographic Signature
Re: [SQL] Proposal of hierarchical queries, a la Oracle
Evgen Potemkin wrote: Joe, i've made it already,as send first 'Proposal ...' message, but found a small bug. within nearest days i'll fix it, and post the patch to pgsql-patches. Please note that there was no patch attached to your initial proposal (assuming this is the message you are referring to): http://archives.postgresql.org/pgsql-sql/2002-11/msg00226.php -- I think that's why people proposed so many alternatives to you. In any case, we'll look forward to your patch! Regards, Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] slow group by query
That's a whole lot faster. The query on 40 msa_codes that took
7 minutes, now only takes 10 seconds.
Thanks a lot.
At 11:54 AM 11/19/2002 -0500, Tom Lane wrote:
Ellen Cyran <[EMAIL PROTECTED]> writes:
> Here is the explain analyze:
> Group (cost=637.18..696.52 rows=593 width=22) (actual
time=982.67..67581.85 rows=435 loops=1)
>-> Sort (cost=637.18..637.18 rows=5934 width=22) (actual
time=833.27..844.78 rows=6571 loops=1)
Well, we don't have to read any further than that to see that all the
time is being spent in the final Group step --- and since grouping 6500
rows is surely not taking very long, the true cost must be in the
evaluation of the SELECT's output targetlist (which the estimated costs
ignore, since the planner can't do much to change it). In other words,
what's hurting you are those subselects in the SELECT list. You're
doing two thousand separate subselects (435*5) --- evidently they take
about 30 msec apiece, which isn't that bad by itself, but it adds up.
What you've basically got here is what Joe Celko calls a "crosstab
by subqueries" (_SQL For Smarties_, section 23.6.3). You might want
to buy his book, which shows several other ways to do crosstab queries,
but the one that seems most directly useful is to GROUP BY and use
aggregate functions to extract the values you want in each crosstab
column:
SELECT msa_code, sic, own, ind_div,
max(case when year = '1975' then emp else null end) as emp1975,
max(case when year = '1976' then emp else null end) as emp1976,
max(case when year = '1977' then emp else null end) as emp1977,
max(case when year = '1978' then emp else null end) as emp1978,
max(case when year = '1990' then emp else null end) as emp1990
FROM tbl_bls_msa
WHERE msa_code in ('1680','1640','0040','0120','0080')
GROUP BY msa_code, sic, ind_div, own ;
If I understand your data schema correctly, only one row in a
msa_code, sic, ind_div, own group will have a particular year
value, so the case/max structure extracts that value, or gives
NULL if there's no such row. (MIN would have worked as well;
with a numeric field you could use SUM too.)
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Using VIEW to simplify code...
Right now I dynamicly generate the SQL for an incredibly ugly 4 table join based on user information client-side. I got the bright idea to create a view and then run a MUUUCH simpler client-side query on that view. The problem is that PostgreSQL apparantly runs the view FIRST and then applies the criteria AFTER assembling the whole view. I was hoping that it would rewrite the "select" in the view to include the criteria BEFORE running the view. That way it could take advantage of the indexes the way my giant-and-hard-to-maintain-client-generated-sql does. Any thoughts or suggestions? If you MUST have the giant-and-hard-to-maintain-client-generated-sql statement and its related explain, I'll produce it. I cringe at the thought of having to redact that monster, tho. CG __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Using VIEW to simplify code...
Chris Gamache <[EMAIL PROTECTED]> writes: > Right now I dynamicly generate the SQL for an incredibly ugly 4 table join > based on user information client-side. I got the bright idea to create a view > and then run a MUUUCH simpler client-side query on that view. The problem is > that PostgreSQL apparantly runs the view FIRST and then applies the criteria > AFTER assembling the whole view. Not necessarily; it starts with that (which after all is what you wrote) and tries to transform it into something more efficient. But it's not as smart as a person ;-). > If you MUST have the giant-and-hard-to-maintain-client-generated-sql > statement and its related explain, I'll produce it. If you want help, you must provide details. The PG version number is relevant also. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Using VIEW to simplify code...
> If you want help, you must provide details. The PG version number is > relevant also. Understood. PostgreSQL 7.2.3. Here's the generated client side sql: select case when (hasflag(ut.flags,1) or (current_timestamp - '1 day'::interval < trl.trans_date)) then case when trl.trans_data like '%RNF'then ' ' else 'Free' end else case when trl.trans_data like '%RNF' then ' ' else case when ct.misc_charge = '0'::money then 'Free' else 'View for ' || to_char(ct.misc_charge::float8,'FM$9990D90') || '' end end end as " ", trl.trans_date::timestamp(0) as "Date", tl.longtype as "Type", trl.trans_data as "Query Data", to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged", trl.user_reference_id as "Reference ID" from trans_log_1 trl, addtypelong tl, user_table ut, company_table ct where ((trl.username='myuser') and (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60 days'::interval) and (tl.shorttype=trl.trans_type) ) union all select case when (hasflag(ut.flags,16) or (current_timestamp - '1 day'::interval < trl.trans_date)) then case when trl.trans_data like '%RNF' then ' ' else 'Free' end else case when trl.trans_data like '%RNF' then ' ' else case when ct.misc_charge = '0'::money then 'Free' else 'View for ' || to_char(ct.misc_charge::float8,'FM$9990D90') || '' end end end as " ", trl.trans_date::timestamp(0) as "Date", tl.longtype as "Type", trl.trans_data as "Query Data", to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged", trl.user_reference_id as "Reference ID" from trans_log_2 trl, addtypelong tl, user_table ut, company_table ct where ((trl.username='myuser') and (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60 days'::interval) and (tl.shorttype=trl.trans_type) ) union all select case when trans_type = 'NS' then ' ' else case when (hasflag(ut.flags,16) or (current_timestamp - '1 day'::interval < trl.trans_date)) then case when trl.trans_data like '%RNF' then ' ' else 'Free' end else case when trl.trans_data like '%RNF' then ' ' else case when ct.misc_charge = '0'::money then 'Free' else ' ' end end end end as " ", trl.trans_date::timestamp(0) as "Date", case when trl.trans_type = 'NS' then 'Name' else 'Archive: ' || tl.longtype end as "Type", trl.trans_data as "Query Data", to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged", trl.user_reference_id as "Reference ID" from trans_log_3 trl, addquerytype tl, user_table ut, company_table ct where ((trl.username='myuser') and (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60 days'::interval) and (tl.querytype=trl.trans_type) ) union all select case when (fdf is null or fdf='') then ' ' else 'Free' end as " ", trl.trans_date::timestamp(0) as "Date", 'FORM: ' || trl.trans_type as "Type", trl.trans_data as "Query Data", to_char(trl.trans_charge, 'FM$9990D90') as "Charged", user_reference_id as "Reference ID" from trans_log_4 trl, user_table ut, company_table ct where ((trl.username='myuser') and (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60 days'::interval) ) order by 2 desc, 4 LIMIT 20 OFFSET 0; Explain: Limit (cost=4339.83..4339.83 rows=20 width=158) -> Sort (cost=4339.83..4339.83 rows=285 width=158) -> Append (cost=2477.60..4328.19 rows=285 width=158) -> Subquery Scan *SELECT* 1 (cost=2477.60..2578.56 rows=187 width=157) -> Hash Join (cost=2477.60..2578.56 rows=187 width=157) -> Seq Scan on company_table ct (cost=0.00..80.41 rows=1041 width=32) -> Hash (cost=2477.13..2477.13 rows=187 width=125) -> Hash Join (cost=287.56..2477.13 rows=187 width=125
Re: [SQL] Using VIEW to simplify code...
On Tue, 19 Nov 2002, Chris Gamache wrote: > > If you want help, you must provide details. The PG version number is > > relevant also. > > Understood. PostgreSQL 7.2.3. 7.3 will be better for this. There were questions about the safety of pushing clauses down in queries with union and intersect and earlier versions wouldn't push down because we weren't sure it was safe. Except will still be problematic, but union and intersect should be much happier. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Using VIEW to simplify code...
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Tue, 19 Nov 2002, Chris Gamache wrote: >> Understood. PostgreSQL 7.2.3. > 7.3 will be better for this. There were questions about the safety > of pushing clauses down in queries with union and intersect and > earlier versions wouldn't push down because we weren't sure it was > safe. Except will still be problematic, but union and intersect should be > much happier. Yeah, the UNIONs in the view are definitely the big problem. Can you try on 7.3rc1 and see how it goes? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Using VIEW to simplify code...
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 19 Nov 2002, Chris Gamache wrote: > >> Understood. PostgreSQL 7.2.3. > > > 7.3 will be better for this. There were questions about the safety > > of pushing clauses down in queries with union and intersect and > > earlier versions wouldn't push down because we weren't sure it was > > safe. Except will still be problematic, but union and intersect should be > > much happier. > > Yeah, the UNIONs in the view are definitely the big problem. Can you > try on 7.3rc1 and see how it goes? That's something to look forward to! I'm going to have to hold off upgrading until y'all feel like 7.3rc1 should become 7.3.0. I wish we had a development environment to use, but we don't. I'll let you know how things fare after that. CG __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.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
[SQL] trying to learn plpqsql... so please forgive..
Maybe this should be sent to novice... I was not certain, but if it should, please tell me so. The matter at hand is this: When I do an 'INSERT INTO VALUES ' and on the table is a serial primary key named p_key. As I want this number to be auto-generated, but use it as a 'customer number', I want to create this function to return the value of this insert. I thought/hoped that this would work, but as there are some people dependant on this database, I dare not try out too much ;-) This would be the first time I used plpgsql, so I am not so certain about what I do. CREATE FUNCTION add_cust() RETURNS INT4 AS ' -- SERIAL data type is really an INT4 (and some more). BEGIN RETURN NEW.p_key; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER add_cust BEFORE INSERT ON table FOR EACH ROW EXECUTE PROCEDURE add_cust(); Someone willing to evaluate this for me, and telling me if it is safe to use as it is? or things I may do wrong? TIA, Michiel ---(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: [SQL] Problems invoking psql. Help please.
I did this tonight dpkg --purge postgresql apt-get install postgresql and am now still getting the following: biko:/usr/bin# psql -U postgres No database specified biko:/usr/bin# psql -U postgres template1 Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin# psql -U postgres template0 Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin# psql -U postgres ggp_test Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin# My pg_hba.conf temporarily reads: local all trust host all 127.0.0.1 255.255.255.255trust host template1 192.168.2.21 255.255.255.0 trust host all 0.0.0.0 0.0.0.0 reject (Eventually, before I take this live, I'll figure out the crypt part). The postgresql installation is at 192.168.10. I'm working from a shell at 192.168.2.21. But I also tried running this from the application server at 2.10 as well and got the same errors. And now pgAdmin II is giving me a connection error: An error has occured in pgAdmin II:frmConnect.cmdConnect_Click Number -2147467259 Description: Could not connect to the server; Could not connect to remote socket. /etc/postgresql/postgresql.conf includes a line reading: tcpip_socket = 1 which I assume means that I _should_ be able to make a TCP connection across the network. Although I cannot seem to do so. Working in Debian 3.0 Woody, with postgreSQL 7.2. Still looking for clues, in fact I seem to be looking for more clues now than I was yesterday. -- Hugh At 05:20 PM 11/19/02 +, you wrote: Did you install your package using apt-get ? All the instalations that I do are using those tools from debian. You have to see all the packages that you have instaled in your computer like: dpkg -l | grep postgresql ii postgresql 7.2.1-2Object-relational SQL database, descended fr ii postgresql-cli 7.2.1-2Front-end programs for PostgreSQL ii postgresql-con 7.2.1-2Additional facilities for PostgreSQL Then you do: dpkg --purge postgresql. You can now run the first command to see if something is still installed. If some are instaled, the you remove it using again dpkg --purge. Luis Sousa Hugh Esco wrote: I have reinstalled before. I wonder though, how I ensure that I have cleanly un-installed it first, so that I leave no residue from the previously botched installation around to mess things up the next time. -- Hugh Esco At 09:03 AM 11/19/02 +, Luis Sousa wrote: Tom Lane wrote: Start over: delete your PG installation and reinstall the Debian package. It seems very clear that you've got an incomplete package. regards, tom lane I agree with Tom Lane. Probably is the best thing to do. When you install all it over, in theory, all the problems will solve by them selfs. Luis Sousa ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problems invoking psql. Help please.
>> biko:/usr/bin# psql -U postgres template1 Maybe try : biko:/usr/bin# psql tempate1 -U postgres I use PG on Debian too. I much prefer to compile from source. It's very easy why don't you give it a try. I've never had a problem compiling from source, just make sure to follow the instruction's carefully in the INSTALL file. Good luck Regards Rudi. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problems invoking psql. Help please.
Does "Could not execv" mean that I do not have rights to execute this script? biko:/usr/bin# ls -al | grep psql lrwxrwxrwx1 root root 10 Oct 10 16:24 psql -> pg_wrapper biko:/usr/bin# This seems to say that Other users, like postgres, should be able to execute it. I'm confused, here. -- Hugh I did this tonight dpkg --purge postgresql apt-get install postgresql and am now still getting the following: biko:/usr/bin# psql -U postgres No database specified biko:/usr/bin# psql -U postgres template1 Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin# psql -U postgres template0 Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin# psql -U postgres ggp_test Could not execv /usr/lib/postgresql/bin/psql biko:/usr/bin# My pg_hba.conf temporarily reads: local all trust host all 127.0.0.1 255.255.255.255trust host template1 192.168.2.21 255.255.255.0 trust host all 0.0.0.0 0.0.0.0 reject (Eventually, before I take this live, I'll figure out the crypt part). The postgresql installation is at 192.168.10. I'm working from a shell at 192.168.2.21. But I also tried running this from the application server at 2.10 as well and got the same errors. And now pgAdmin II is giving me a connection error: An error has occured in pgAdmin II:frmConnect.cmdConnect_Click Number -2147467259 Description: Could not connect to the server; Could not connect to remote socket. /etc/postgresql/postgresql.conf includes a line reading: tcpip_socket = 1 which I assume means that I _should_ be able to make a TCP connection across the network. Although I cannot seem to do so. Working in Debian 3.0 Woody, with postgreSQL 7.2. Still looking for clues, in fact I seem to be looking for more clues now than I was yesterday. -- Hugh At 05:20 PM 11/19/02 +, you wrote: Did you install your package using apt-get ? All the instalations that I do are using those tools from debian. You have to see all the packages that you have instaled in your computer like: dpkg -l | grep postgresql ii postgresql 7.2.1-2Object-relational SQL database, descended fr ii postgresql-cli 7.2.1-2Front-end programs for PostgreSQL ii postgresql-con 7.2.1-2Additional facilities for PostgreSQL Then you do: dpkg --purge postgresql. You can now run the first command to see if something is still installed. If some are instaled, the you remove it using again dpkg --purge. Luis Sousa Hugh Esco wrote: I have reinstalled before. I wonder though, how I ensure that I have cleanly un-installed it first, so that I leave no residue from the previously botched installation around to mess things up the next time. -- Hugh Esco At 09:03 AM 11/19/02 +, Luis Sousa wrote: Tom Lane wrote: Start over: delete your PG installation and reinstall the Debian package. It seems very clear that you've got an incomplete package. regards, tom lane I agree with Tom Lane. Probably is the best thing to do. When you install all it over, in theory, all the problems will solve by them selfs. Luis Sousa ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problems invoking psql. Help please.
Here are the results from reversing the arguments. hesco@biko:~$ su postgres Password: postgres@biko:/home/hesco$ cd postgres@biko:~$ cd /usr/bin postgres@biko:/usr/bin$ psql tempate1 -U postgres Could not execv /usr/lib/postgresql/bin/psql postgres@biko:/usr/bin$ psql template1 -U postgres Could not execv /usr/lib/postgresql/bin/psql postgres@biko:/usr/bin$ If I compile from source, will the apt-get database know what I've done? Or will I have to do the updates from source as well? -- Hugh At 04:40 PM 11/20/02 +1000, you wrote: >> biko:/usr/bin# psql -U postgres template1 Maybe try : biko:/usr/bin# psql tempate1 -U postgres I use PG on Debian too. I much prefer to compile from source. It's very easy why don't you give it a try. I've never had a problem compiling from source, just make sure to follow the instruction's carefully in the INSTALL file. Good luck Regards Rudi. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problems invoking psql. Help please.
Hugh Esco <[EMAIL PROTECTED]> writes: >> biko:/usr/bin# ls -al | grep psql >> lrwxrwxrwx1 root root 10 Oct 10 16:24 psql -> pg_wrapper > This seems to say that Other users, like postgres, should be able to > execute it. I'm confused, here. The permissions attached to a symbolic link are meaningless, in all Unix variants I've dealt with. You need to look at the permissions of the linked-to object (here, pg_wrapper) instead... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Problems invoking psql. Help please.
That is indeed true. Also I see you do everything as root, try creating a new user especially for postgres databases. create a directory with root in /usr/loca/pgsql/ named data, or any other directory that is in $PGDATA. chown the directory to the postgres user, log in as the postgres user and try to initdb, postmaster (-i!) and connect... I think the problem lies here somewhere... Michiel At 02:13 20-11-2002 -0500, Tom Lane wrote: Hugh Esco <[EMAIL PROTECTED]> writes: >> biko:/usr/bin# ls -al | grep psql >> lrwxrwxrwx1 root root 10 Oct 10 16:24 psql -> pg_wrapper > This seems to say that Other users, like postgres, should be able to > execute it. I'm confused, here. The permissions attached to a symbolic link are meaningless, in all Unix variants I've dealt with. You need to look at the permissions of the linked-to object (here, pg_wrapper) instead... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(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
