Re: [SQL] Hrm...why is this wrong?
Ken Corey wrote: > Wow! Answering emails on a Sunday? Someone should be giving you an award or > something. > > On Sunday 04 February 2001 8:13 pm, you wrote: > > Ken Corey <[EMAIL PROTECTED]> writes: > > > When the select at the bottom of this email is executed, I'm getting the > > > message: > > > ERROR: parser: parse error at or near "$1" > > > > I don't get that; I get > > ERROR: Attribute 'username_in' not found > > which is about what I'd expect for the given function text; maybe you > > didn't transcribe it accurately? > > That's strange...perhaps the difference was a problem with my table > definition? *shrug* I also had made a few mistakes, so once I got those > fixed, the code seems to work again. > > > Anyway, an invaluable technique for debugging plpgsql functions is to > > start psql with debug level 2, so that the queries the plpgsql executor > > feeds to the SQL engine get logged in the postmaster log. (If you don't > > run the postmaster with a logfile, you should...) For example: > > Hey, that's perfect. It's okay just so long as the debugging out goes > *somewhere*...:^) Another lesser known trick is to add #option dump at the top of the function body (before DECLARE). This causes the PL/pgSQL compiler to dump out the entire function after parsing where you can see all the SPI queries that later will get executed. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [SQL] Is this a bug, or is it just me?
Josh Berkus wrote: > Tom et al. > > Discovered this quirk in foriegn keys: > > In the preliminary version of a database, I added foriegn > key constraints to a number of tables, linking them to a > column in a shared reference table (status.status) that was > only one-half of a composite primary key (and thus the > values were not unique). When I tried to delete a row > containing a "2" in the status column from the status > relation, I received a Foreign Key violation error event > though there were other "2"'s in the table still present. > > So ... is this a bug in forign key implementation, or just > my fault for keying off a non-unique value? > > And, if the latter, is there a way I can construct a foreign > key constraint that keys onto a view or query? The referenced columns of a foreign key constraint shall have a unique constraint. That's how it is in the SQL specs. So it is a bug that the system let's you specify the constraint at all. I think Stephan fixed it for 7.1. OTOH Postgres doesn't (and shouldn't) enforce it after, because if it would, you couldn't drop/create a corrupted index. And no, you can't actually reference to a view or anything else than a table. That is, because the system wouldn't know how to check for the DELETE/UPDATE cases on the base tables building the view if the removal of a key would violate existing references. For such custom setups, we have procedural languages and triggers. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
[SQL] Re: PL/pgsql EXECUTE 'SELECT INTO ...'
Tom Lane wrote: > I have looked a little bit at what it'd take to make SELECT INTO inside > an EXECUTE work the same as it does in plain plpgsql --- that is, the > INTO should reference plpgsql variables, not a destination table. > It looks to me like this is possible but would require some nontrivial > re-engineering inside plpgsql. What I'm visualizing is that EXECUTE > should read its string argument not just as an SPI_exec() string, but > as an arbitrary plpgsql proc_stmt. This would offer some interesting > capabilities, like building a whole FOR-loop for dynamic execution. > But there are a number of problems to be surmounted, notably arranging > for the parsetree built by the plpgsql compiler not to be irretrievably > memory-leaked. (That ties into something I'd wanted to do anyway, > which is to have the plpgsql compiler build its trees in a memory > context associated with the function, not via malloc().) > > This does not look like something to be tackling when we're already > in late beta, unfortunately. So we have to decide what to do for 7.1. > If we do nothing now, and then implement this feature in 7.2, we will > have a backwards compatibility problem: EXECUTE 'SELECT INTO ...' > will completely change in meaning. > > I am inclined to keep our options open by forbidding EXECUTE 'SELECT > INTO ...' for now. That's more than a tad annoying, because that leaves > no useful way to do a dynamically-built SELECT, but if we don't forbid > it I think we'll regret it later. You can do something like FOR record_var IN EXECUTE LOOP ... END LOOP; In this case, the executed over SPI_exec() must return tuples (0-n). Otherwise you'll get a runtime error. Inside the loop you have access to the tuples via the record. Is that the dynamically-built SELECT capability you've been missing? There's not that much need for mucking with temp tables in EXECUTE as all this discussion looks to me. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
RE: [SQL] Re: PL/pgsql EXECUTE 'SELECT INTO ...'
Title: RE: [SQL] Re: PL/pgsql EXECUTE 'SELECT INTO ...' What I wrote wasn't about temp tables, it was about selecting into plpgsql variables. It would appear that Jan's syntax gets around this problem. MikeA -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: 08 February 2001 13:30 To: Tom Lane Cc: Jan Wieck; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [SQL] Re: PL/pgsql EXECUTE 'SELECT INTO ...' Tom Lane wrote: > I have looked a little bit at what it'd take to make SELECT INTO inside > an EXECUTE work the same as it does in plain plpgsql --- that is, the > INTO should reference plpgsql variables, not a destination table. > It looks to me like this is possible but would require some nontrivial > re-engineering inside plpgsql. What I'm visualizing is that EXECUTE > should read its string argument not just as an SPI_exec() string, but > as an arbitrary plpgsql proc_stmt. This would offer some interesting > capabilities, like building a whole FOR-loop for dynamic execution. > But there are a number of problems to be surmounted, notably arranging > for the parsetree built by the plpgsql compiler not to be irretrievably > memory-leaked. (That ties into something I'd wanted to do anyway, > which is to have the plpgsql compiler build its trees in a memory > context associated with the function, not via malloc().) > > This does not look like something to be tackling when we're already > in late beta, unfortunately. So we have to decide what to do for 7.1. > If we do nothing now, and then implement this feature in 7.2, we will > have a backwards compatibility problem: EXECUTE 'SELECT INTO ...' > will completely change in meaning. > > I am inclined to keep our options open by forbidding EXECUTE 'SELECT > INTO ...' for now. That's more than a tad annoying, because that leaves > no useful way to do a dynamically-built SELECT, but if we don't forbid > it I think we'll regret it later. You can do something like FOR record_var IN EXECUTE LOOP ... END LOOP; In this case, the executed over SPI_exec() must return tuples (0-n). Otherwise you'll get a runtime error. Inside the loop you have access to the tuples via the record. Is that the dynamically-built SELECT capability you've been missing? There's not that much need for mucking with temp tables in EXECUTE as all this discussion looks to me. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
Re: [HACKERS] Re: [SQL] PL/PGSQL function with parameters
Tom Lane wrote: > Michael Ansley <[EMAIL PROTECTED]> writes: > > CREATE FUNCTION table_count(varchar) RETURNS integer AS ' > > DECLARE > > SQL varchar; > > RES integer; > > BEGIN > > SQL = ''SELECT * INTO temp1 FROM '' || $1; > > EXECUTE SQL; > > SELECT count(*) INTO RES FROM temp1; > > RETURN(RES) > > END; > > ' > > LANGUAGE 'plpgsql'; > > > What I couldn't get it to do was to select directly into the variable RES. > > I tried this, and it seems that "SELECT ... INTO foo" is not executed > correctly by EXECUTE --- the INTO is handled as an ordinary select-into- > table construct rather than plpgsql's select-into-variable. > > While I have not looked closely, I seem to recall that plpgsql handles > INTO by stripping that clause out of the statement before it's passed to > the SQL engine. Evidently that's not happening in the EXECUTE case. > > Jan, do you agree this is a bug? Is it reasonable to try to repair it > for 7.1? If we do not change the behavior of EXECUTE now, I fear it > will be too late --- some people will come to depend on the existing > behavior. EXECUTE simply takes the string expression and throws it into SPI_exec() without parsing. Changing that for 7.1 is *not* possible. The above can be accomplished by DECLARE ROW record; RES integer; BEGIN FOR ROW IN EXECUTE ''SELECT count(*) AS N FROM '' || $1 LOOP RES := N; END LOOP; RETURN RES; END; Not as elegant as it should be, but at least possible. There's much to be done for a future version of PL/pgSQL, but better support for dynamic SQL needs alot of functionality added to the main parser and the SPI manager in the first place. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
[GENERAL] Re: Aggregates and joined tables...
Hello, I have upgraded my DB to 7.0.3, but there is still the problem. I think that it may be a bug. Joining 3 tables was not possible... It says "fa1 should be in aggregate too" ! While joining 2 tables gives wrong results ... ie, if the results should be like this: fa1 sum - --- ali 100 omid 200 shaya 50 then I get this: fa1 sum - --- ali 350 omid 350 shaya 350 Any more idea ? Omid Omoomi >>The platform is PostgreSQL 6.5.2 on i386-unknown-freebsd3.2, compiled by >>gcc 2.7.2.1 . >>The story is that I need to join two tables with an aggregate function. >>Here is a sample model : >>Table A consist of ( fa1 , fa2 ) >>Table B consist of ( fb1 , fb2 ) >>Table C consist of ( fc1 , fc2 ,fc3 ) >>I want to write a query which looks like this : >>select fa1 - sum(fc3) >>from A,B,C >>where fa1=fb1 and fb2=fc2 >>group by fa1 ; >>unfortunately I get this in result: >>ERROR: Illegal use of aggregates or non-group column in target list >Works fine in current sources. Try upgrading to something newer than 6.5.2 ... _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Re: [SQL] PL/PGSQL function with parameters
Josh Berkus wrote: > Tom, Jan, Michael, > > > While I have not looked closely, I seem to recall that plpgsql handles > > INTO by stripping that clause out of the statement before it's passed to > > the SQL engine. Evidently that's not happening in the EXECUTE case. > > > > Jan, do you agree this is a bug? Is it reasonable to try to repair it > > for 7.1? If we do not change the behavior of EXECUTE now, I fear it > > will be too late --- some people will come to depend on the existing > > behavior. > > If you think that's the best way. What we're really all wanting is a wy > in PL/pgSQL to pass a parameter as an object name. Doing it *without* > using EXECUTE would be even better than modifying EXECUTE to accomdate > SELECT ... INTO variable. > > If we can write queries that address tables by OID, that would give us a > quick workaround ... get the OID from pg_class, then pass it to the > query as variables of type OID: > > SELECT column1_oid, column2_oid FROM table_oid > WHERE column2_oid = variable1 > ORDER BY column1_oid; > > OF course, having PL/pgSQL do this automatically would be even better, > but I suspect would require a *lot* of extra programming by Jan. Couple of problems here: 1. The main parser, which is used in turn by the SPI stuff, doesn't allow parameters passed in for object- identifiers. 2. I'm not sure if *all* statements are really supported by SPI_prepare() plus SPI_execp(). EXECUTE currently uses SPI_exec() to directly invoke the querystring. 3. PL/pgSQL needs a clean way to identify statements that shall not be cached. First things that come to mind are - statements using temporary objects - statements invoking utility commands (or generally any DDL) - statements having parameters for object-identifiers If identified as such non-cacheable query, PL/pgSQL doesn't use SPI_saveplan() but recreates a new plan every time. 4. PL handlers in general should have a registering mechanism for a callback function. On any schema change (i.e. shared syscache invalidation) this function is called, causing the PL handler to invalidate *ALL* function bytecodes and cached plans. Keeping track of things like "var table.att%TYPE" used in a function would be a mess - so better throw away anything. Yes, that's a *lot* to do. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [SQL] Re: PL/pgsql EXECUTE 'SELECT INTO ...'
Jan Wieck <[EMAIL PROTECTED]> writes: >> I am inclined to keep our options open by forbidding EXECUTE 'SELECT >> INTO ...' for now. That's more than a tad annoying, because that leaves >> no useful way to do a dynamically-built SELECT, but if we don't forbid >> it I think we'll regret it later. > You can do something like > FOR record_var IN EXECUTE LOOP > ... > END LOOP; Okay, that solves the concern I had about not being able to get the result of an EXECUTEd select at all. I'll go ahead and forbid EXECUTE 'SELECT INTO' for the time being, and we can talk about improving plpgsql later. regards, tom lane
Re: [HACKERS] Re: [SQL] PL/PGSQL function with parameters
> Josh Berkus wrote: >> If you think that's the best way. What we're really all wanting is a wy >> in PL/pgSQL to pass a parameter as an object name. Doing it *without* >> using EXECUTE would be even better than modifying EXECUTE to accomdate >> SELECT ... INTO variable. >> >> If we can write queries that address tables by OID, that would give us a >> quick workaround ... get the OID from pg_class, then pass it to the >> query as variables of type OID: >> >> SELECT column1_oid, column2_oid FROM table_oid >> WHERE column2_oid = variable1 >> ORDER BY column1_oid; This is completely pointless, AFAICS. If you don't know what table is to be selected from, then you can't do *any* semantic checking or planning in advance, so you might as well just do the entire processing at runtime. That's exactly what EXECUTE does. I don't see any functional advantage in an intermediate step between plpgsql's normal behavior (caching of query plans) and EXECUTE. If it bought some readability over constructing a query string for EXECUTE, then maybe, but dealing in table and column OIDs is not my idea of a pleasant or readable way to program ... regards, tom lane
Re: [SQL] Query never returns ...
After you load the data, you need to run vacuum analzye. That'll get statistics on the current data in the table. Of course, I'm not sure that'll help in this case. On Thu, 8 Feb 2001, Brice Ruth wrote: > Stephan, > > Here is what EXPLAIN shows: > > NOTICE: QUERY PLAN: > > Sort (cost=0.02..0.02 rows=1 width=64) > -> Nested Loop (cost=0.00..0.01 rows=1 width=64) > -> Seq Scan on tblmedcond (cost=0.00..0.00 rows=1 width=36) > -> Seq Scan on tblsidedruglink (cost=0.00..0.00 rows=1 width=28) > > As for vacuum analyze - prior to running into these problems, I deleted > all data from the database (using delete from ) and then ran > vacuumdb -a, after which I loaded the data into the tables using 'copy > ... from' - there have been no updates to the database since then - > merely selects.
Re: [SQL] Query never returns ...
What does explain show for the query and have you run
vacuum analyze recently on the tables?
On Thu, 8 Feb 2001, Brice Ruth wrote:
> The following query:
>
> SELECT
> tblSIDEDrugLink.DrugID,
> tblSIDEDrugLink.MedCondID,
> tblMedCond.PatientName AS MedCondPatientName,
> tblMedCond.ProfessionalName AS MedCondProfessionalName,
> tblSIDEDrugLink.Frequency,
> tblSIDEDrugLink.SeverityLevel
> FROM
> tblSIDEDrugLink,
> tblMedCond
> WHERE
> (tblSIDEDrugLink.DrugID IN ('DG-18698')) AND
> (tblSIDEDrugLink.MedCondID = tblMedCond.MedCondID)
> ORDER BY
> tblSIDEDrugLink.DrugID,
> tblSIDEDrugLink.Frequency,
> tblSIDEDrugLink.SeverityLevel,
> tblSIDEDrugLink.MedCondID;
>
> seems to not be liked by PostgreSQL. Table 'tblSIDEDrugLink' has the
> following structure:
>
> CREATE TABLE TBLSIDEDRUGLINK
> (
> DRUGID VARCHAR(10) NOT NULL,
> MEDCONDID VARCHAR(10) NOT NULL,
> FREQUENCY INT2,
> SEVERITYLEVEL INT2,
> CONSTRAINT PK_TBLSIDEDRUGLINK PRIMARY KEY (DRUGID, MEDCONDID)
> );
>
> with the following index:
> CREATE INDEX IX_TBLSIDEDRUGLINK1 ON TBLSIDEDRUGLINK (MEDCONDID);
>
> This table has 153,288 rows.
>
> Table 'tblMedCond' has the following structure:
>
> CREATE TABLE TBLMEDCOND
> (
> MEDCONDID VARCHAR(10) NOT NULL,
> PROFESSIONALNAMEVARCHAR(58),
> PATIENTNAME VARCHAR(58),
> CONSTRAINT PK_TBLMEDCOND PRIMARY KEY (MEDCONDID)
> );
>
> This table has 1,730 rows.
>
> The query above is made by a third-party API that I don't have the
> source for, so I can't modify the query in the API, though the
> third-party has been quite willing to help out - they may even ship me a
> 'special' version of the API if there's something in this query that
> PostgreSQL for some reason doesn't implement efficiently enough.
>
> If it would help anyone to see the query plan or such - I can modify the
> logs to show that, just let me know.
>
> Btw - I've let this query run for a while & I haven't seen it complete
> ... s ... I don't know if it would ever complete or not.
>
> Any help at all is as always, appreciated.
>
> Sincerest regards,
> --
> Brice Ruth
> WebProjkt, Inc.
> VP, Director of Internet Technology
> http://www.webprojkt.com/
>
Re: [SQL] Query never returns ...
All right ... after reading up on the documentation for vacuum, I understand why that's necessary. I've run vacuum analyze on all the tables, now. Here are the more realistic results from explain: NOTICE: QUERY PLAN: Sort (cost=62.46..62.46 rows=14 width=64) -> Nested Loop (cost=0.00..62.19 rows=14 width=64) -> Index Scan using pk_tblsidedruglink on tblsidedruglink (cost=0.00..33.82 rows=14 width=28) -> Index Scan using pk_tblmedcond on tblmedcond (cost=0.00..2.01 rows=1 width=36) The query runs lightning fast now ... THANK YOU!!! :) -Brice Stephan Szabo wrote: > > After you load the data, you need to run vacuum analzye. That'll > get statistics on the current data in the table. Of course, I'm > not sure that'll help in this case. > > On Thu, 8 Feb 2001, Brice Ruth wrote: > > > Stephan, > > > > Here is what EXPLAIN shows: > > > > NOTICE: QUERY PLAN: > > > > Sort (cost=0.02..0.02 rows=1 width=64) > > -> Nested Loop (cost=0.00..0.01 rows=1 width=64) > > -> Seq Scan on tblmedcond (cost=0.00..0.00 rows=1 width=36) > > -> Seq Scan on tblsidedruglink (cost=0.00..0.00 rows=1 width=28) > > > > As for vacuum analyze - prior to running into these problems, I deleted > > all data from the database (using delete from ) and then ran > > vacuumdb -a, after which I loaded the data into the tables using 'copy > > ... from' - there have been no updates to the database since then - > > merely selects. -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
On Wed, Feb 07, 2001 at 10:38:53AM -0600, Brice Ruth wrote: > Is there a simple (unix) command I can run on text files to convert > cr/lf to lf? The way I did it seemed pretty ass-backward to me (not to > mention time consuming). > > -Brice On many systems (linux at least) there is a command dos2unix, which is actually an alias for `recode ibmpc:`. But that will take care of more than just CR, e.g. umlauts, diacritics, etc.. Albert. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
Re: [SQL] Re: Query never returns ...
Brice - What does EXPLAIN say for that query? With empty tables, I get two index scans, a merge join, and two sorts. I'm guessing wildly that you've got a non-optimal sort strategy happening somehow, given the four fold ORDER BY clause. Ross Here's the empty version: NOTICE: QUERY PLAN: Sort (cost=84.25..84.25 rows=100 width=64) -> Merge Join (cost=8.30..80.93 rows=100 width=64) -> Index Scan using pk_tblmedcond on tblmedcond (cost=0.00..60.00 rows=1000 width=36) -> Sort (cost=8.30..8.30 rows=10 width=28) -> Index Scan using pk_tblsidedruglink on tblsidedruglink (cost=0.00..8.14 rows=10 width=28) EXPLAIN On Thu, Feb 08, 2001 at 10:19:43AM -0600, Brice Ruth wrote: > FYI - I let the query run for 11.5 minutes before killing it off. It > had consumed 11 minutes, 18 seconds of CPU time (reported by ps). The > following messages are from the server log, I'm pasting in all messages > directly following the query: >
Re: [SQL] Re: Query never returns ...
Brice, This sounds like a problem with you postgresql install or your HDD rather than a problem with your query ... which appears to be fine. I would reccomend taking it up with PGSQL Inc. (or Great Bridge) pay-for support if this is a commercial project. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [SQL] Re: Query never returns ...
Ross, Thanx to Stephan's help, I found out that after loading the tables w/ data, I had to run vacuum analyze to inform the optimizer of the amount of data in the table (amongst other things, I imagine). After running that on all the tables, the query performs fine. -Brice "Ross J. Reedstrom" wrote: > > Brice - > What does EXPLAIN say for that query? With empty tables, I get two index scans, > a merge join, and two sorts. I'm guessing wildly that you've got a non-optimal > sort strategy happening somehow, given the four fold ORDER BY clause. > > Ross > > Here's the empty version: > > NOTICE: QUERY PLAN: > > Sort (cost=84.25..84.25 rows=100 width=64) > -> Merge Join (cost=8.30..80.93 rows=100 width=64) > -> Index Scan using pk_tblmedcond on tblmedcond (cost=0.00..60.00 >rows=1000 width=36) > -> Sort (cost=8.30..8.30 rows=10 width=28) > -> Index Scan using pk_tblsidedruglink on tblsidedruglink >(cost=0.00..8.14 rows=10 width=28) > > EXPLAIN > > On Thu, Feb 08, 2001 at 10:19:43AM -0600, Brice Ruth wrote: > > FYI - I let the query run for 11.5 minutes before killing it off. It > > had consumed 11 minutes, 18 seconds of CPU time (reported by ps). The > > following messages are from the server log, I'm pasting in all messages > > directly following the query: > > -- Brice Ruth WebProjkt, Inc. VP, Director of Internet Technology http://www.webprojkt.com/
Re: [SQL] PL/PGSQL function with parameters
Tom, Jan, > This is completely pointless, AFAICS. If you don't know what table > is to be selected from, then you can't do *any* semantic checking or > planning in advance, so you might as well just do the entire processing > at runtime. That's exactly what EXECUTE does. I don't see any > functional advantage in an intermediate step between plpgsql's normal > behavior (caching of query plans) and EXECUTE. If it bought some > readability over constructing a query string for EXECUTE, then maybe, > but dealing in table and column OIDs is not my idea of a pleasant or > readable way to program ... Well, given that between you and Jan you have addressed dynamic querying, it seems that there is no point in tinkering further. Always great to find that a problem has already been solved. If I wasn't up to my hairline in behind-schedule projects, I'd offer to write this up for the User's Manual. Actually, consider that a medium-term commitment ... before the end of the year, I'll write a much longer PL/pgSQL chapter which Jan can review & correct. (I think I'm in a postion to do so, as the current app uses a large assortment of PL/pgSQL functions as pseudo-middleware). -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Re: [GENERAL] Re: [SQL] Query never returns ...
Um, no.
You should run vacuum analyze AFTER you loaded up the data, otherwise,
your table statistics will be all wrong (it'll contain 'empty table'
statistics).
-alex
On Thu, 8 Feb 2001, Brice Ruth wrote:
> Stephan,
>
> Here is what EXPLAIN shows:
>
> NOTICE: QUERY PLAN:
>
> Sort (cost=0.02..0.02 rows=1 width=64)
> -> Nested Loop (cost=0.00..0.01 rows=1 width=64)
> -> Seq Scan on tblmedcond (cost=0.00..0.00 rows=1 width=36)
> -> Seq Scan on tblsidedruglink (cost=0.00..0.00 rows=1 width=28)
>
> As for vacuum analyze - prior to running into these problems, I deleted
> all data from the database (using delete from ) and then ran
> vacuumdb -a, after which I loaded the data into the tables using 'copy
> ... from' - there have been no updates to the database since then -
> merely selects.
>
> -Brice
>
> Stephan Szabo wrote:
> >
> > What does explain show for the query and have you run
> > vacuum analyze recently on the tables?
> >
> > On Thu, 8 Feb 2001, Brice Ruth wrote:
> >
> > > The following query:
> > >
> > > SELECT
> > > tblSIDEDrugLink.DrugID,
> > > tblSIDEDrugLink.MedCondID,
> > > tblMedCond.PatientName AS MedCondPatientName,
> > > tblMedCond.ProfessionalName AS MedCondProfessionalName,
> > > tblSIDEDrugLink.Frequency,
> > > tblSIDEDrugLink.SeverityLevel
> > > FROM
> > > tblSIDEDrugLink,
> > > tblMedCond
> > > WHERE
> > > (tblSIDEDrugLink.DrugID IN ('DG-18698')) AND
> > > (tblSIDEDrugLink.MedCondID = tblMedCond.MedCondID)
> > > ORDER BY
> > > tblSIDEDrugLink.DrugID,
> > > tblSIDEDrugLink.Frequency,
> > > tblSIDEDrugLink.SeverityLevel,
> > > tblSIDEDrugLink.MedCondID;
> > >
> > > seems to not be liked by PostgreSQL. Table 'tblSIDEDrugLink' has the
> > > following structure:
> > >
> > > CREATE TABLE TBLSIDEDRUGLINK
> > > (
> > > DRUGID VARCHAR(10) NOT NULL,
> > > MEDCONDID VARCHAR(10) NOT NULL,
> > > FREQUENCY INT2,
> > > SEVERITYLEVEL INT2,
> > > CONSTRAINT PK_TBLSIDEDRUGLINK PRIMARY KEY (DRUGID, MEDCONDID)
> > > );
> > >
> > > with the following index:
> > > CREATE INDEX IX_TBLSIDEDRUGLINK1 ON TBLSIDEDRUGLINK (MEDCONDID);
> > >
> > > This table has 153,288 rows.
> > >
> > > Table 'tblMedCond' has the following structure:
> > >
> > > CREATE TABLE TBLMEDCOND
> > > (
> > > MEDCONDID VARCHAR(10) NOT NULL,
> > > PROFESSIONALNAMEVARCHAR(58),
> > > PATIENTNAME VARCHAR(58),
> > > CONSTRAINT PK_TBLMEDCOND PRIMARY KEY (MEDCONDID)
> > > );
> > >
> > > This table has 1,730 rows.
> > >
> > > The query above is made by a third-party API that I don't have the
> > > source for, so I can't modify the query in the API, though the
> > > third-party has been quite willing to help out - they may even ship me a
> > > 'special' version of the API if there's something in this query that
> > > PostgreSQL for some reason doesn't implement efficiently enough.
> > >
> > > If it would help anyone to see the query plan or such - I can modify the
> > > logs to show that, just let me know.
> > >
> > > Btw - I've let this query run for a while & I haven't seen it complete
> > > ... s ... I don't know if it would ever complete or not.
> > >
> > > Any help at all is as always, appreciated.
> > >
> > > Sincerest regards,
> > > --
> > > Brice Ruth
> > > WebProjkt, Inc.
> > > VP, Director of Internet Technology
> > > http://www.webprojkt.com/
> > >
>
>
Re: [SQL] Query never returns ...
Stephan,
Here is what EXPLAIN shows:
NOTICE: QUERY PLAN:
Sort (cost=0.02..0.02 rows=1 width=64)
-> Nested Loop (cost=0.00..0.01 rows=1 width=64)
-> Seq Scan on tblmedcond (cost=0.00..0.00 rows=1 width=36)
-> Seq Scan on tblsidedruglink (cost=0.00..0.00 rows=1 width=28)
As for vacuum analyze - prior to running into these problems, I deleted
all data from the database (using delete from ) and then ran
vacuumdb -a, after which I loaded the data into the tables using 'copy
... from' - there have been no updates to the database since then -
merely selects.
-Brice
Stephan Szabo wrote:
>
> What does explain show for the query and have you run
> vacuum analyze recently on the tables?
>
> On Thu, 8 Feb 2001, Brice Ruth wrote:
>
> > The following query:
> >
> > SELECT
> > tblSIDEDrugLink.DrugID,
> > tblSIDEDrugLink.MedCondID,
> > tblMedCond.PatientName AS MedCondPatientName,
> > tblMedCond.ProfessionalName AS MedCondProfessionalName,
> > tblSIDEDrugLink.Frequency,
> > tblSIDEDrugLink.SeverityLevel
> > FROM
> > tblSIDEDrugLink,
> > tblMedCond
> > WHERE
> > (tblSIDEDrugLink.DrugID IN ('DG-18698')) AND
> > (tblSIDEDrugLink.MedCondID = tblMedCond.MedCondID)
> > ORDER BY
> > tblSIDEDrugLink.DrugID,
> > tblSIDEDrugLink.Frequency,
> > tblSIDEDrugLink.SeverityLevel,
> > tblSIDEDrugLink.MedCondID;
> >
> > seems to not be liked by PostgreSQL. Table 'tblSIDEDrugLink' has the
> > following structure:
> >
> > CREATE TABLE TBLSIDEDRUGLINK
> > (
> > DRUGID VARCHAR(10) NOT NULL,
> > MEDCONDID VARCHAR(10) NOT NULL,
> > FREQUENCY INT2,
> > SEVERITYLEVEL INT2,
> > CONSTRAINT PK_TBLSIDEDRUGLINK PRIMARY KEY (DRUGID, MEDCONDID)
> > );
> >
> > with the following index:
> > CREATE INDEX IX_TBLSIDEDRUGLINK1 ON TBLSIDEDRUGLINK (MEDCONDID);
> >
> > This table has 153,288 rows.
> >
> > Table 'tblMedCond' has the following structure:
> >
> > CREATE TABLE TBLMEDCOND
> > (
> > MEDCONDID VARCHAR(10) NOT NULL,
> > PROFESSIONALNAMEVARCHAR(58),
> > PATIENTNAME VARCHAR(58),
> > CONSTRAINT PK_TBLMEDCOND PRIMARY KEY (MEDCONDID)
> > );
> >
> > This table has 1,730 rows.
> >
> > The query above is made by a third-party API that I don't have the
> > source for, so I can't modify the query in the API, though the
> > third-party has been quite willing to help out - they may even ship me a
> > 'special' version of the API if there's something in this query that
> > PostgreSQL for some reason doesn't implement efficiently enough.
> >
> > If it would help anyone to see the query plan or such - I can modify the
> > logs to show that, just let me know.
> >
> > Btw - I've let this query run for a while & I haven't seen it complete
> > ... s ... I don't know if it would ever complete or not.
> >
> > Any help at all is as always, appreciated.
> >
> > Sincerest regards,
> > --
> > Brice Ruth
> > WebProjkt, Inc.
> > VP, Director of Internet Technology
> > http://www.webprojkt.com/
> >
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
Re: [SQL] Query never returns ...
Brice Ruth <[EMAIL PROTECTED]> writes: > As for vacuum analyze - prior to running into these problems, I deleted > all data from the database (using delete from ) and then ran > vacuumdb -a, after which I loaded the data into the tables using 'copy > ... from' - there have been no updates to the database since then - > merely selects. That was the wrong order to do things in :-(. The VACUUM ANALYZE posted statistics showing all your tables as empty, and the planner is now faithfully choosing plans that are good for tiny tables --- ie, minimal startup cost and don't worry about per-tuple cost. There has been some talk of having stats automatically updated by COPY, but right now it doesn't happen. So the correct procedure is to do a VACUUM ANALYZE on a table *after* you do any sizable data additions. BTW, people have occasionally stated on the list that you need to redo VACUUM ANALYZE after adding/dropping indexes, but that's not true, at least not in the present state of the world. VACUUM ANALYZE only posts stats about the data in the table(s). The planner always looks at the current set of indices for a table, together with the last-posted data statistics, to choose a plan. regards, tom lane
Re: [HACKERS] PL/pgsql EXECUTE 'SELECT INTO ...'
Tom Lane writes: > I am inclined to keep our options open by forbidding EXECUTE 'SELECT > INTO ...' for now. That's more than a tad annoying, because that leaves > no useful way to do a dynamically-built SELECT, but if we don't forbid > it I think we'll regret it later. You can always use CREATE TABLE AS. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] PL/pgsql EXECUTE 'SELECT INTO ...'
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane writes: >> I am inclined to keep our options open by forbidding EXECUTE 'SELECT >> INTO ...' for now. That's more than a tad annoying, because that leaves >> no useful way to do a dynamically-built SELECT, but if we don't forbid >> it I think we'll regret it later. > You can always use CREATE TABLE AS. Does SPI_exec() support that? (Tries it ... seems to work ...) Cool. OK, we have the bases covered then; there's no need to allow SELECT INTO inside EXECUTE until we can make it work as expected. regards, tom lane
[SQL] fetching the id of a new row
Hi, How might I insert a new row into a table and return the id of the new row all in the same SQL statement? The id is generated by a sequence. Up to now I've been getting the nextval of the sequence first and then inserting with the id in a second SQL exec. Is there a faster way (in a general case, without writing SQL or plpgsql functions)? Thanks
Re: [SQL] parse error in create index
You can use two quote characters to get a single quote in the quoted
string, so ''month''
On Thu, 8 Feb 2001, Hubert Palme wrote:
> Stephan Szabo wrote:
> >
> > Functional indexes cannot currently take constant values to the function,
> > so it's complaining about the constant 'month'. The current workaround is
> > probably to create a function that does the date_part('month', ) for
> > you and then use that function in the index creation.
>
> Hmm... Perhaps, it's better I post to the novice group, because I'm new
> to SQL.
>
> Anyway -- That's my trial:
>
> adressen=> CREATE FUNCTION geb_monat (date) RETURNS integer AS
> adressen-> 'SELECT date_part('month', $1)::integer;'
> adressen-> LANGUAGE 'sql';
> ERROR: parser: parse error at or near "month"
>
> The point are the nested strings, I guess. How can I render a "'" in an
> SQL string?
>
> Thanks for your help!
[SQL] Index Problem
I want to query this... --> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12; this query doesn't refer the index that made by this query. --> CREATE INDEX idx_bbs ON bbs (ref, step); but, i change the query that "ref desc" to "ref asc". then query refer the index, and i can see a result very fast. :-( so, i want to view an result that one column ordered by ascending, and oterh column ordered by descending using index. what do i do? how make an index? == == ¿ì¸® ÀÎÅͳÝ, Daum Æò»ý ¾²´Â ¹«·á E-mail ÁÖ¼Ò ÇѸÞÀÏ³Ý Áö±¸ÃÌ ÇÑ±Û °Ë»ö¼ºñ½º Daum FIREBALL http://www.daum.net
Re: [SQL] Index Problem
"Kim Yunhan" <[EMAIL PROTECTED]> writes: > I want to query this... > --> SELECT * FROM bbs ORDER BY ref desc, step ASC LIMIT 12; > this query doesn't refer the index that made by this query. > --> CREATE INDEX idx_bbs ON bbs (ref, step); Well, no. The ordering the query is asking for has nothing to do with the ordering of the index, so the index is no help. The simplest answer would be to restructure your data so that the order you are interested in corresponds to the natural index order. If you don't like that answer, you could consider making a "reverse" operator class that sorts the datatype of "ref" in reverse order, and then building an index on (ref reverse_ops, step). I think the planner would be smart enough to realize that it could use such an index for your query ... but it's a sufficiently off-the-wall case that I doubt anyone's ever tried it. Lemme know if it works ;-) regards, tom lane
