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 string-expr LOOP ... END LOOP; In this case, the string-expr 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 string-expr LOOP ... END LOOP; In this case, the string-expr 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 string-expr 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 tblname) 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 ...
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 tblname) 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 ...
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] 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 tblname) 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', arg) 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
