Re: [SQL] odbc drivers
> Hi; > I've got PostgreSQL 7.2 and I would like to use MSAccess2k as a > front end; in a LAN, because some of my colleagues just use Windows. > Where could I find a ODBC driver for Postgresql to be use under windows2000? > > thanks and regards What about http://odbc.postgresql.org ? Isn't it enough? Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems invoking psql. Help please.
On Sat, Nov 16, 2002 at 02:11:58PM -0500, Hugh Esco wrote: > Hey folks: > > I've copied the shell dialogue below. > Everything in: /usr/lib/postgresql/bin is owned by root:root. It's default instalation in Debian Woody and it works fine > >postgres@biko:/home/hesco$ psql > >env: /usr/lib/postgresql/bin/readpgenv: Permission denied Is your readpgenv executable? Try chmod 755 readpgenv Regards, Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems invoking psql. Help please.
> You seem to have a very bizarre setup there --- there is no such thing > as "readpgenv" in the standard Postgres distribution, and > /usr/lib/postgresql/bin/ isn't the standard place to put the executable > files either. Perhaps the above is normal for the Debian package of > Postgres, but I'm afraid you'll have to ask the Debian packager for > help. Nobody using other platforms is likely to be able to help... I have Debian and Postgres installed from .deb package. Postgres is installed in /usr/lib/postgresql by default and it contains readpgenv. Psql stops working as described, when I remove executable attribute from readpgenv. readpgenv is a bash script and has only 3 lines: #!/bin/bash . /etc/postgresql/postgresql.env env postgresql.env file is an export of PGDATA/PGLIB/PGACCES_HOME variables 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.
> 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$ Check the permissions. Psql is only a symbolic link to pg_wrapper. You should have: ls -al /usr/bin/pg_wrapper -rwxr-xr-x1 root root 6584 sie 25 23:55 /usr/bin/pg_wrapper > 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? No. If you want to create package .deb from your sources, look at debian packages source site. There is special debian patch in postgres directory. Apply it, compile your sources and create .deb package. The last step is to install this package with dpkg. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] full join in view
On Tue, Jan 14, 2003 at 04:27:22PM +0200, Tambet Matiisen wrote: > > First I would like to say, that I'm quite amazed. You even guessed table names >right! :) I did not expect such an in-depth analysis in such a short time. Thanks, >Tomasz! It wasn't difficult - these names where in foreign keys definition. > > > > > > > Tambet Matiisen wrote: > > > > > > Is dor_kst_id the same as kdt_kst_id and as mat_id? After > > some database > > practicing I found, that using the same name in all tables is > > much more > > comfortably > > > This way I can refer most columns without prefixing them with table alias. But it's >anyway good habit to use table aliases, so this is not that important. I think in >next project I try it in your way. If you have joins like this: table1 join table2 using (field1) duplicates of field1 disappears and you don't need table name. > > > For each material (materjalid) and koostud (koostud) you want to find > > some current value (koostude_detaild) and compare it to some sum > > (documentid...)? > > I'm not sure if I understand well your view, but here is my > > version of > > this view - without subquery: > > > > I tried to save few scans by not including "koostud" and "materjalid" in my original >query. Based on yours, I created a new version: If you need only not null-kdt_id and dor_id, then just change your joins into inner joins. If you are sure, that you will get only not-null results, you don't need to include koostud and marerjalid. The result is: CREATE OR REPLACE VIEW v_tegelikud_kulud2 AS SELECT kdt.kdt_kst_id as kst_id, kdt.kdt_mat_id as mat_id, max(kdt.detaili_nr) AS detaili_nr, max(kdt.arv) AS arv, max(kdt.kulu) AS kulu, max(kdt.yhik) AS yhik, max(kdt.koefitsent) AS koefitsent, max(kdt.eeldatav_hind) AS eeldatav_hind, sum(dor.kogus * dor.koefitsent::numeric) AS kogus, sum(dor.kokku) AS kokku FROM koostude_detailid kdt JOIN dokumentide_read dor ON kdt.kdt_kst_id = dor.dor_kst_id AND kdt.kdt_mat_id = dor.dor_mat_id AND EXISTS ( SELECT 1 FROM dokumendid dok WHERE dor.dor_dok_id = dok.dok_id AND dok.tyyp = 30 AND dok.kinnitaja IS NOT NULL ) GROUP BY kst.kst_id, mat.mat_id; > But there are still few things that worry me: > 1. Cross join between koostud and materjalid. Table "materjalid" may have up to >1 rows and only 20-30 of them are actually needed. You don't need it anymore. Anyway I thought, that you have in your query "mat_id=.. and kst_id=.." > 2. Indeces on "koostude_detailid" and "dokumentide_read" are not used. Probably my >tables do not contain enough rows. Maybe I should generate more test data first. > 3. The cost of this query is twice as big, as my original query. It seems to me, >that SubPlan is causing this. I tried to move it to subquery, but then the optimizer >chose a totally different execution plan and seemingly was not able to use indeces of >"dokumentide_read" table. The version with subquery: > Now it should work better. Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] To use a VIEW or not to use a View.....
On Thu, Jan 23, 2003 at 08:53:53AM -0800, Stephan Szabo wrote: > On Wed, 22 Jan 2003, Tom Lane wrote: > > I haven't looked at code yet but tried examples like Tomasz's and some > simple ones and have gotten reasonable seeming output for the estimates > given accurate statistics (joining two estimate 3 outputs, getting 8 for > the estimated rows, joining that with another copy getting 50 some odd > where in this case the real would be 81). Not that I did a > particularly thorough test. I hope to get a chance over the next couple > of days to look and run more tests. > > Tomasz, if you have the chance, you might want to try CVS and see what it > does for the queries you've been working with. Not too easy. Currently I have only windows machine with Postgresql/cygwin. I use dial-up for accessing internet, which isn't nice to use. I will try this if I find some free computer to install postgresql/linux ;-) Anyway I already gave up this kind of query, especially I can't use CVS as production server (should I?) Regards, 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] How to return records from a function
Did you look at http://techdocs.postgresql.org/guides/SetReturningFunctions ? You need Postgresql 7.3 to do this. Regards, 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] Cancelling Queries
On Thu, Mar 06, 2003 at 06:25:29PM -0500, Mark Mitchell wrote: > I have a Perl program that executes PostgreSQL queries through DBI. > Is there any way to cancel a query once its started. If I could at least > somehow get the PID of the child process postmaster starts I could kill > that. > > This may be a better question for the Perl programming list but I > thought I'd ask you guys too > > Mark Mitchell Use transactions. Just before query finish you can confirm query (commit) or cancel it (rollback) Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Cancelling Queries
On Thu, Mar 06, 2003 at 08:31:56AM -0600, Mark Mitchell wrote: > These are select queries that may have been keyed incorrectly that I > need to cancel. So a transaction won't help in this case. What about setting maximum query execution time in postgresql configuration? There are some options helpful for deadlock cases. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] trouble with query
On Tue, Mar 18, 2003 at 03:47:55PM +0100, alexj wrote:
> Hi,
>
> I'm looking how can I do to insert multiple values from a
> complexe query.
>
> What I want to do is something like that :
>
> INSERT INTO est_planifie_jour (id,ref_activite,ref_ressource,ref_jour)
> (SELECT nextval('est_p_id_p') FROM est_planifie),
> (SELECT id_activite FROM activite WHERE
> nom = 'SGBD 02 cours théorique),
> (SELECT ref_ressource FROM personne,groupe,fait_partie
> WHERE groupe.nom ='cycle 1b' AND
> fait_partie.ref_groupe = groupe.id_groupe AND
> personne.id_personne = fait_partie.ref_personne),
> (SELECT id_jour FROM jour WHERE jour.id_jour = 3)
>
You should rewrite your insert to receive multiple rows from single
select. This query shoud be similiar to this below:
insert into ... (..)
select
nextval('est_p_id_p'),
id_activite,
ref_resource,
ref_jour
from
activite, personne,groupe,fait_partie,jour
where
activite.nom='..' and fait_partie.ref_groupe=groupe.id_groupe
and groupe.nom='..'
and jour.id_jour=3
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Seq Scans when index expected to be used
On Sat, 29 Nov 2003 08:49:24 -0800 (PST), ow wrote > explain select b, c, a > from test > group by b, c, a > having count(*) > 1 I'm not sure about 7.4 aggregate improvements, but <=7.3 didn't work good with aggregates at all. Maybe it's not directly an answer to your question, but try theses queries: select t1.b,t1.c,t1.a from test t1 join test t2 using (b,c,a) where t2.id<>t1.id group by t1.b,t1.c,t1.a or select a,b,c from test t1 where exists (select * from test t2 where t2.a=t1.a and t2.b=t1.b and t2.c=t1.c and t1.id<>t2.id) group by a,b,c Regards, Tomasz Myrta ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
