Re: [GENERAL] trigger question
mikeo <[EMAIL PROTECTED]> writes: > in oracle, the triggers were smart enough to know not to reference > an old value on insert in an "insert or update" trigger procedure, > apparently. > this is the original oracle trigger that works fine > with the same insert statement: > CREATE OR REPLACE TRIGGER rates_hist_trigger > before insert or update on rates > for each row > WHEN (old.rt_valid <> 'P' or new.rt_valid not in ('Y','N')) Hmm. It sounds to me like Oracle treats the OLD fields as being NULL if the context is INSERT, which is something we could certainly do at the price of losing some error detection capability --- ie, if that really had been a typo as I first thought, the system wouldn't flag it for you. Not sure which way is better. Comments anyone? regards, tom lane
Re: [GENERAL] trigger question
At 10:33 AM 6/27/00 -0400, Tom Lane wrote: >mikeo <[EMAIL PROTECTED]> writes: >> CREATE function rates_hist_function() >> returns opaque >> as 'BEGIN >>if ( old.rt_valid <> ''P'' or new.rt_valid not in (''Y'',''N'')) > > >> i get this error: >> ERROR: record old is unassigned yet > >> since this trigger is for both insert or update, why does it expect >> the "OLD" value to already exist, as it would not for insert? > >Because you referenced it in the function code. Am I missing something? > > regards, tom lane > maybe. in oracle, the triggers were smart enough to know not to reference an old value on insert in an "insert or update" trigger procedure, apparently. this is the original oracle trigger that works fine with the same insert statement: CREATE OR REPLACE TRIGGER rates_hist_trigger before insert or update on rates for each row WHEN (old.rt_valid <> 'P' or new.rt_valid not in ('Y','N')) begin insert into rates_hist values (:new.cut_id,:new.ct_key,:new.rtm_id,:new.rt_sell_factor, :new.rt_sell_msg_cost,:new.rt_sell_init_sec,:new.rt_sell_init_cost, :new.rt_sell_addl_sec,:new.rt_sell_addl_cost,:new.rt_buy_factor, :new.rt_buy_msg_cost,:new.rt_buy_init_sec,:new.rt_buy_init_cost, :new.rt_buy_addl_sec,:new.rt_buy_addl_cost,:new.rt_valid,:new.rse_id, :new.wu_id, sysdate, :new.rt_usoc_def_factor ); end; / i can easily get around this using rules. my main objective is to not have to change too much code as we migrate over to postgres from oracle and that is not too much of a change. thanks, mikeo
Re: [GENERAL] Conversion from MS Access to Postgresql
Hi Mihai, Microsoft products store false as (0) and true as (-1) (Why? I don't know!). Apparently storing a (-1) requires more than bool provides. So, you can either edit all of your Access code so that it interprets (1) as true (practically, I don't recommend this), or you can migrate your yes/no fields to int2. David Boerwinkle -Original Message- From: Mihai Gheorghiu <[EMAIL PROTECTED]> To: Stephen Davies <[EMAIL PROTECTED]>; G.L.Lim <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Tuesday, June 27, 2000 9:07 AM Subject: Re: [GENERAL] Conversion from MS Access to Postgresql >I tried to export an Access Yes/No field to pgsql boolean and got an error >message. >By default, Access (and the ODBC driver) exports Yes/No to bpchar. However, >I want to use bool. >Any suggestions? >Thanks, > >Mihai > > >-Original Message- >From: Stephen Davies <[EMAIL PROTECTED]> >To: G.L.Lim <[EMAIL PROTECTED]> >Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> >Date: Monday, June 26, 2000 7:54 PM >Subject: Re: [GENERAL] Conversion from MS Access to Postgresql > > >>G'day. >> >>Having just done it, I can confirm that converting an Access database >>to PostgreSQL is very easy. >> >>All I did was as follows: >> >>1. create an empty Postgresql database, >>2. cretae an ODBC DSN on the Access machine pointing at the empty >>database, >>3, fire up Access and Save/As/Export all tables to the ODBC target. >> >>Job done:-)) >> >>In fact, I had to do some massage to the results as the export process >>does not create indexes nor sequences and I decidesd to change some of >>the generated data types. >> >>This was also easy using the pgdump utility to dump the data and the >>definition for editing. >> >>Be aware that any column names that include upper case letters in >>Access will keep their capitalisation and require quotes in Postgresql. >> >>HTH, >>Stephen. >>"G.L.Lim" <[EMAIL PROTECTED]> wrote: >>> Hi, >>> >>> I am curently using MS Access and would like to convert my existing >Access >>> database to Postgresql database (maybe into Postgresql dump file first if >>> neccessary). Is there any utility or program that can do that? Please >>> advise. >>> >>> Thank you. >>> >>> Regards, >>> Geok Leng >> >> >> >> >> >>Stephen Davies Consulting [EMAIL PROTECTED] >>Adelaide, South Australia. Voice: 08-8177 1595 >>Computing & Network solutions. Fax: 08-8177 0133 >> >> >
Re: [GENERAL] FATAL 1: Database dbname=template1 does not exist in pg_database
Tom Lane schrieb: > Read it again: the error message is complaining because you tried to > access a database named "dbname=template1". Better check the syntax > on the connection subroutine you are using --- looks like a confusion > between positional parameters and keyword-style connect strings... In the meantime I figured out, was was going wrong with my postgresql installation. I made a big mistake configuring the config.inc.php3 file There I had wrong entries for user and stduser. After correcting this phpPGadmin work quite well. C.
Re: [GENERAL] puzzled by the docs
Hernan Gonzalez <[EMAIL PROTECTED]> writes: > The PostgreSQL Administrator's Guide which appears on the web > http://www.postgresql.org/docs/admin/index.html > is fairly different from the one which is packed with > the 7.0.2 distribution. > Which is the good one? The files appearing under http://www.postgresql.org/docs/ are a snapshot of current development, not the docs that go with the most recent release. Any changes you see are work that will be in the next major release (ie, 7.1). We have been planning to rearrange the website so that the main docs page shows the most recent release, and the development snapshot appears someplace else, but I guess Vince hasn't got round to it yet ... regards, tom lane
Re: [GENERAL] How to dump from Postgre
The BitBucket on unix is /DEV/NULL, or the place where things will disappear. >>> Herbert Liechti <[EMAIL PROTECTED]> 06/27/00 09:35AM >>> Thomas Lockhart wrote: > > Yeah, it just vanished. Not my fault. The bitbucket ate it. And what is a bitbucket? My dictioniary knows nothing about this word. Just for all the people who's mother tongue is not english. :-) - Herbie
Re: [GENERAL] How to dump from Postgre
Thomas Lockhart wrote: > > Yeah, it just vanished. Not my fault. The bitbucket ate it. And what is a bitbucket? My dictioniary knows nothing about this word. Just for all the people who's mother tongue is not english. :-) - Herbie
Re: [GENERAL] trigger question
mikeo <[EMAIL PROTECTED]> writes: > CREATE function rates_hist_function() > returns opaque > as 'BEGIN >if ( old.rt_valid <> ''P'' or new.rt_valid not in (''Y'',''N'')) > i get this error: > ERROR: record old is unassigned yet > since this trigger is for both insert or update, why does it expect > the "OLD" value to already exist, as it would not for insert? Because you referenced it in the function code. Am I missing something? regards, tom lane
[GENERAL] puzzled by the docs
The PostgreSQL Administrator's Guide which appears on the web http://www.postgresql.org/docs/admin/index.html is fairly different from the one which is packed with the 7.0.2 distribution. Which is the good one? I'm puzzled because the web version mentions the release 7.0.2 (the other one, doesn't). And the section 'Logging and Debuging' (in chap. 7 from the web version), mentions a 'postgres.conf' configuration file, some settings that are of interest for me (as 'LOG_PID' and 'LOG_TIMESTAMP') but the distributed doc, instead, speaks of the 'pg_option' file, and doesn't mention those settings... Help! Am I missing something stupid? Hernan Gonzalez Buenos Aires, Argentina
Re: [GENERAL] Why isn't that null
Tom Lane <[EMAIL PROTECTED]> writes: [...] > > If I change the function statement as follows: > > > IF NEW.vkp = 0 THEN > >... > > END IF; > > > it works > > Shouldn't that give a type error of some sort? You didn't say > what type vkp is, but if it's a string type then comparing it > against a numeric shouldn't work IMHO. no it isn't. It is a float8 type. I changed my program in that way that the query includes NULL instead of ''. Bis dann Matthias -- Matthias Teege -- [EMAIL PROTECTED] -- http://emugs.de make world not war PGP-Key auf Anfrage
Re: [GENERAL] Limit for an transaction
Matthias Teege wrote: > > Moin, > > ist there any limit for the length of an transaction? I'am > using postgreSQL 6.5.1 with PHP and the apache log says: > > NOTICE: (transaction aborted): queries ignored until END Usually that appears right after one of the queries you did failed for some reason (usually a typo in the query). Since the query didn't complete sucessfully it must be aborted, and this it ignores all queries until END. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://cupid.suninternet.com/~kleptog/
Re: [GENERAL] Conversion from MS Access to Postgresql
I tried to export an Access Yes/No field to pgsql boolean and got an error message. By default, Access (and the ODBC driver) exports Yes/No to bpchar. However, I want to use bool. Any suggestions? Thanks, Mihai -Original Message- From: Stephen Davies <[EMAIL PROTECTED]> To: G.L.Lim <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Monday, June 26, 2000 7:54 PM Subject: Re: [GENERAL] Conversion from MS Access to Postgresql >G'day. > >Having just done it, I can confirm that converting an Access database >to PostgreSQL is very easy. > >All I did was as follows: > >1. create an empty Postgresql database, >2. cretae an ODBC DSN on the Access machine pointing at the empty >database, >3, fire up Access and Save/As/Export all tables to the ODBC target. > >Job done:-)) > >In fact, I had to do some massage to the results as the export process >does not create indexes nor sequences and I decidesd to change some of >the generated data types. > >This was also easy using the pgdump utility to dump the data and the >definition for editing. > >Be aware that any column names that include upper case letters in >Access will keep their capitalisation and require quotes in Postgresql. > >HTH, >Stephen. >"G.L.Lim" <[EMAIL PROTECTED]> wrote: >> Hi, >> >> I am curently using MS Access and would like to convert my existing Access >> database to Postgresql database (maybe into Postgresql dump file first if >> neccessary). Is there any utility or program that can do that? Please >> advise. >> >> Thank you. >> >> Regards, >> Geok Leng > > > > > >Stephen Davies Consulting [EMAIL PROTECTED] >Adelaide, South Australia. Voice: 08-8177 1595 >Computing & Network solutions. Fax: 08-8177 0133 > >
Re: [GENERAL] How to dump from Postgre
> Yeah, it just vanished. Not my fault. The bitbucket ate it. Ah, so sorry. Use pg_dump. Hope you find your docs soon ;) - Thomas
Re: [GENERAL] How to dump from Postgre
Generally a lot of the questions here on the mailing lists are answered in the documentation. So read the docs first ;-) It's: "pg_dump databaseName > someFile.sql" "Morten W. Petersen" wrote: > > The PostgreSQL documentation is invisible? > > Yeah, it just vanished. Not my fault. The bitbucket ate it. > > -Morten =)
Re: [GENERAL] DateTime fields
*/ On Tue, 27 Jun 2000, Dale Anderson wrote: > Here is am example of what I am doing. > > danderso=# \d test >Table "test" > Attribute | Type| Modifier > ---+---+-- > time | timestamp | > > danderso=# insert into test values (to_timestamp('1200 PM JUN 27 2000','HHMI PM MON >DD ')); > INSERT 22825 1 > danderso=# select * from test; > time > > 2000-06-28 00:00:00-05 > (1 row) > And for me: test=# select to_timestamp('1200 PM JUN 27 2000', 'HHMI PM MON DD '); to_timestamp 2000-06-27 12:00:00+02 but don't worry, you are right, I have NEW FIXED version. I commit it tomorrow. (Tom wanted show you that standard date/time routines are right only.) Karel
[GENERAL] BLOB DBI func() interface under postgres
Hello, In DBD::Pg one can read (line 134): $lobj_fd = $dbh->func($lobjId, $mode, 'lo_open'); But how is the LOB retrieved in the first place? If I pass the OID of an existing LOB instance from a table the returned $lobj_fd is null. What kind of $lobjId is one supposed to pass to this function to open a LOB? The aim is to be able to read a LOB from a postgres DB without having to lo_export the object to a file first. Can that be done with the $dbh->func() interface? (using lo_open, lo_read, etc ..) TIA -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr Hi, I am an alien .sig, and at the moment I am having sex to your mind, by looking at your smile I can see that you like it.
Re: [GENERAL] DateTime fields
Here is am example of what I am doing. danderso=# \d test Table "test" Attribute | Type| Modifier ---+---+-- time | timestamp | danderso=# insert into test values (to_timestamp('1200 PM JUN 27 2000','HHMI PM MON DD ')); INSERT 22825 1 danderso=# select * from test; time 2000-06-28 00:00:00-05 (1 row) danderso=# Thanks, Dale. >>> Tom Lane <[EMAIL PROTECTED]> 06/26/00 05:41PM >>> "Dale Anderson" <[EMAIL PROTECTED]> writes: > I noticed that the DateTime fields and the to_timestamp function > interpret 12 PM as 00:00 and 12 AM as 12:00. Huh? regression=# select '2000-06-12 12:00 am' ::datetime; ?column? 2000-06-12 00:00:00-04 (1 row) regression=# select '2000-06-12 12:00 pm' ::datetime; ?column? 2000-06-12 12:00:00-04 (1 row) Looks fine to me. Could you be more precise about what you're trying to do? regards, tom lane
[GENERAL] trigger question
hi, i've created a function as follows: drop function rates_hist_function(); CREATE function rates_hist_function() returns opaque as 'BEGIN if ( old.rt_valid <> ''P'' or new.rt_valid not in (''Y'',''N'')) then new.rt_timestamp = now(); insert into rates_hist values ( new.cut_id,new.ct_key,new.rtm_id,new.rt_sell_factor, new.rt_sell_msg_cost,new.rt_sell_init_sec,new.rt_sell_init_cost, new.rt_sell_addl_sec,new.rt_sell_addl_cost,new.rt_buy_factor, new.rt_buy_msg_cost,new.rt_buy_init_sec,new.rt_buy_init_cost, new.rt_buy_addl_sec,new.rt_buy_addl_cost,new.rt_valid,new.rse_id, new.wu_id,new.rt_timestamp, new.rt_usoc_def_factor ); end if; return new; END;' language 'plpgsql'; and i call it from this trigger: drop TRIGGER rates_hist_trigger on rates; CREATE TRIGGER rates_hist_trigger after insert or update on rates for each row execute procedure rates_hist_function(); when i attempt to insert a row into the rates table using this statement: insert into rates(cut_id,ct_key,rt_valid,...,rt_timestamp,rt_usoc_def_factor) values ('mikeo',123456,'x',...,now(),1.35); i get this error: ERROR: record old is unassigned yet since this trigger is for both insert or update, why does it expect the "OLD" value to already exist, as it would not for insert? second, is there a way to tell a function that it's inserting or updating, as in oracle's "if updating..."? thanks, mikeo
Re: [GENERAL] How to dump from Postgre
> The PostgreSQL documentation is invisible? Yeah, it just vanished. Not my fault. The bitbucket ate it. -Morten =)
Re: [GENERAL] Transactions and web applications
Lincoln Yeoh writes: > At 10:01 PM 26-06-2000 -0400, Michael Mayo wrote: > >- Original Message - > >From: "Lincoln Yeoh" <[EMAIL PROTECTED]> > >> What are the recommended ways to do transactions in web applications? > Let me illustrate the problem with an example: > > page 1 > user logs in > page 2 > user does A > page 3 > user does B > page 4 > user does C > page 5 > user says, oops forget the whole thing, please rollback. Or user just says > forget it and closes browser. Or browser crashes at page 4. Typically, web applications do not need to store the intermediate results in the database. You can store them as hidden form variables on the in-between pages or as cookies on the browser. Only when all the data is ready to be passed to the database do you need to worry about the transaction, and then it's only done when the webserver handles that particular page. This way, you limit your transaction to the time it takes to handle the final "Submit" page. If you're doing serious web/db stuff, you may want to look at the numerous resources available at http://www.arsdigita.com/asj (formerly http://photo.net/wtr/). > Do those Java object thingies help for these problems? :-) Brian -- Brian Baquiran <[EMAIL PROTECTED]> http://www.baquiran.com/ AIM: bbaquiran Work: (632)718 Home: (632)9227123
Re: [GENERAL] How to dump from Postgre
On Tue, 27 Jun 2000, Morten W. Petersen wrote: > How do you dump from the Postgre database? > (i.e. as with MySQL, where you have mysqldump) The PostgreSQL documentation is invisible? Karel
[GENERAL] How to dump from Postgre
How do you dump from the Postgre database? (i.e. as with MySQL, where you have mysqldump) -Morten
Re: [GENERAL] Why isn't that null
Matthias Teege <[EMAIL PROTECTED]> writes: > I have al small problem with ISNULL in a trigger > function. My definition looks like: > IF NEW.vkp ISNULL THEN >... > The following SQL query didnt cause this if statement: > insert into aufpos(auftrag, artikel, best_menge, vkp, > lieferwoche, cuser, ctime, uuser, utime) values > ('175','8501900','12','','3500','matthias', > now (), 'matthias',now ()) This seems correct to me --- an empty string '' is certainly not the same thing as NULL. > If I change the function statement as follows: > IF NEW.vkp = 0 THEN >... > END IF; > it works Shouldn't that give a type error of some sort? You didn't say what type vkp is, but if it's a string type then comparing it against a numeric shouldn't work IMHO. regards, tom lane
Re: [GENERAL] Transactions and web applications
At 10:01 PM 26-06-2000 -0400, Michael Mayo wrote: >- Original Message - >From: "Lincoln Yeoh" <[EMAIL PROTECTED]> >> What are the recommended ways to do transactions in web applications? >> >> Right now I have persistent database connections, but currently they are >> shared by multiple sessions. So I make sure that database level >> transactions are closed after each page/http request. >> >> In theory I could tie database connections to sessions, but then if I have >> 1000 outstanding (not necessarily active) sessions that would mean 1000 >> database connections, and 1000 postgres backend processes right? Ouch! >However, are you sure that you're not overestimating your requirements? >Assuming the average database connection takes 2 seconds to complete, it >would require a load of 500 hits/sec to reach that 1000 figure you >state...only the largest of sites get that kind of traffic. If your site >does get that popular, it is likely you will have the $$ to invest in enough >hardware to support it. Let me illustrate the problem with an example: page 1 user logs in page 2 user does A page 3 user does B page 4 user does C page 5 user says, oops forget the whole thing, please rollback. Or user just says forget it and closes browser. Or browser crashes at page 4. What you suggest (database connection takes 2 second) seems to be CGI style: for each webpage, connect to database, do stuff then disconnect. You will not be able to take full advantage of database level transactions with this approach- because with most databases when you disconnect there'll be a rollback or commit. Whereas if I try to use database level transactions to handle actual transactions (reservations, rollbacks etc) for a webapp, if a user visits the site, does something and then quits halfway, there's no way to know about it until the timeout. If the timeout value is 15 minutes, that's 900 seconds of waiting before rollback/continuing the database transaction. If I get 2 connections per second and people don't log out I hit that 1000 figure in 500 seconds. For postgres, each connection will involve a separate backend taking say about 1MB. Bad news if: database_connection_limit < timeout x peak_connections_per_second. So this approach is not suitable for serving the whole world, might work for a controlled number of internal users (tellers, helpdesks etc). Right now I use application level transactions, with shared persistent database connections for performance. What I want to know is if I'm missing something? Is there another way to do things? >> Because of this it looks like I may actually have to do application level >> transactions instead of being able to rely solely on database level >> transactions. > >I fail to see how application-level tranactions will help you here. >Even if you do app-level transactions, you will still need a database engine >to hold and query whatever data you have. Maybe you are suggesting building >your own database engine built into your app. IMHO, this would be a rather The application still uses the database. What I mean by application level transactions is that the rollback/commit stuff, transactional isolation will be handled by the application (at the application layer). For example if there's a commit, I then update all relevant records to indicate they're committed. i.e. update tablex set committed=true where transactionid=14452; And that's not what I call "help", just something I had to put up with :).. For MySQL I had to do things differently: no database level transactions so can't update X tables atomically. Every row had a transactionid but no committed flag. Had a transaction table keeping track of which transactions are valid. This allowed me to mark rows in X tables as committed, just by updating a row in the transaction table. But it meant that every select has to be joined with the transaction table. Fortunately I didn't have to deal with uniqueness problems and all that, that would have been more painful. Also not all tables needed transactions. And I didn't have to write my own WAL :). Do those Java object thingies help for these problems? Cheers, Link.
Re: [GENERAL] Conversion from MS Access to Postgresql
"Len Morgan" <[EMAIL PROTECTED]> writes: > ... I cannot seem to make Postgres join two > tables when the type of one is char(9) and the other is character > varying(9). The machine seems to go into an endless loop. What? Specific example, please. > A similar problem I have is with fix precision fields. I want to > export a numeric(10,2) number from Access but they always end up > numeric(30,6). I don't think our 6.5.* ODBC driver knows anything about numeric, so you're probably going to get default numeric precision if you go through it. You might have better luck with 7.0. regards, tom lane
Re: [GENERAL] DateTime fields
On Mon, 26 Jun 2000, Tom Lane wrote: > "Dale Anderson" <[EMAIL PROTECTED]> writes: > > I noticed that the DateTime fields and the to_timestamp function > > interpret 12 PM as 00:00 and 12 AM as 12:00. > > Huh? > > Looks fine to me. No, you overlook "to_timestamp()" in query. This routine really has bug in PM/AM interpretation. Karel
Re: [GENERAL] Why isn't that null
Hi, May be this insert will work with your test in your function insert into aufpos(auftrag, artikel, best_menge, vkp, lieferwoche, cuser, ctime, uuser, utime) values ('175','8501900','12',NULL,'3500','matthias', now (), 'matthias',now ()) Nicolas Matthias Teege a écrit : > > Moin, > > I have al small problem with ISNULL in a trigger > function. My definition looks like: > > IF NEW.vkp ISNULL THEN >select t1.las into i_kd >from auftrag t1, aufpos t2 where >t2.auftrag = t1.id; >select get_vkp(i_kd, NEW.artikel) into f_vkp; >NEW.vkp:=f_vkp; > END IF; > > The following SQL query didnt cause this if statement: > > insert into aufpos(auftrag, artikel, best_menge, vkp, > lieferwoche, cuser, ctime, uuser, utime) values > ('175','8501900','12','','3500','matthias', > now (), 'matthias',now ()) > > If I change the function statement as follows: > > IF NEW.vkp = 0 THEN >... > END IF; > > it works but '0' is a leagal value but not '' (empty). The > function should only be called if NEW.vkp ist empty. > > Many thanks > Matthias
[GENERAL] Why isn't that null
Moin, I have al small problem with ISNULL in a trigger function. My definition looks like: IF NEW.vkp ISNULL THEN select t1.las into i_kd from auftrag t1, aufpos t2 where t2.auftrag = t1.id; select get_vkp(i_kd, NEW.artikel) into f_vkp; NEW.vkp:=f_vkp; END IF; The following SQL query didnt cause this if statement: insert into aufpos(auftrag, artikel, best_menge, vkp, lieferwoche, cuser, ctime, uuser, utime) values ('175','8501900','12','','3500','matthias', now (), 'matthias',now ()) If I change the function statement as follows: IF NEW.vkp = 0 THEN ... END IF; it works but '0' is a leagal value but not '' (empty). The function should only be called if NEW.vkp ist empty. Many thanks Matthias
[GENERAL] Plan question..
databasename=# explain select DISTINCT (case when resubmitted > created then resubmitted else created end),a.app_id, a.appcode, a.firstname, a.middlename, a.lastname, a.state, a.degree1, a.d1date, a.degree2, a.d2date, a.salary, a.skill1, a.skill2, a.skill3, a.objective, a.employer, a.sic1, a.sic2, a.sic3, a.prefs1, a.prefs2, a.sells from applicants as a,applicants_resumes as ar,resumes_fti as rf where (a.created::date > '01-06-2000' or a.resubmitted::date > '01-06-2000') and (rf.string ~'^test' ) and ar.app_id=a.app_id and rf.id=ar.oid order by (case when resubmitted > created then resubmitted else created end) desc limit 10 offset 0; NOTICE: QUERY PLAN: Unique (cost=3981.58..4396.74 rows=722 width=220) -> Sort (cost=3981.58..3981.58 rows=7220 width=220) -> Hash Join (cost=1751.00..3518.84 rows=7220 width=220) -> Hash Join (cost=1665.98..2634.96 rows=6132 width=216) -> Seq Scan on applicants a (cost=0.00..585.74 rows=6132 width=208) -> Hash (cost=1638.38..1638.38 rows=11038 width=8) -> Seq Scan on applicants_resumes ar (cost=0.00..1638.38 rows=11038 width=8) -> Hash (cost=4.97..4.97 rows=32024 width=4) -> Index Scan using resumes_fti_index on resumes_fti rf (cost=0.00..4.97 rows=32024 width=4) EXPLAIN Both the app_id and the OID columns are indexed and I have done a VACUUM ANALYZE in the applicants_resumes table yet I still get a seq scan on them I'm trying to understand how this works so I can index the right columns -- could someone please school me? :-) When I enable likeplanning (in contrib) I get a totally differen plan but over all it's slower. Thanks! -Mitch
[GENERAL] connection time out
hello , is there any connection time out parameter to be set while starting the postmaster.the connection to postgresql are timed out after some 5 minutes. i am running postgresql 6.5.3 on freeBSD 3.4 and accessing it using jdbc. why i get this problem ? can anyone help me out ???!!!