Re: [BUGS] BUG #2830: Wrong results for prepared statements
On Sat, Dec 16, 2006 at 5:15 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: Prepared SELECT/UPDATE/DELETE statements produce wrong results if executed while target table is being clustered. The short answer is don't CLUSTER while the table is in live use ... CLUSTER re- inserts all the rows in the table into a fresh table. This means that all the rows appear to have been inserted by the CLUSTER transaction, and therefore that a transaction that scans the table afterward with a snapshot taken before the CLUSTER committed will not see those rows. This really should be documented in the CLUSTER command. Having been falsely reassured by the following statement in the documentation, I have clustered tables in live use. When a table is being clustered, an ACCESS EXCLUSIVE lock is acquired on it. This prevents any other database operations (both reads and writes) from operating on the table until the CLUSTER is finished. We have crontab jobs to do daily clusters on some of our small tables with high update rates. The databases are 24/7 with fairly high usage (query requests in the 10s of millions per day). Should we be load shifting off of a server before doing the sub-second CLUSTER on a small, high update table? The difference between EXECUTE and SELECT behavior here is just a chance matter of exactly where the snap is taken during the parse/execute code path --- your SELECT works because it blocks for AccessShareLock on the table before it sets the snap. But SELECT would fail just the same way within a serializable transaction that had already set its snapshot. Ouch! I don't suppose there is any reasonable way to cover this with locks? -Kevin ---(end of broadcast)--- TIP 1: 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
[BUGS] BUG #2835: Table inheritance and statement level trigger.
The following bug has been logged online: Bug reference: 2835 Logged by: William ZHANG Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: Linux Description:Table inheritance and statement level trigger. Details: create language plpgsql; create table foo(a int); create table y(a int); insert into y values(1); create table yy(b int) inherits(y); create or replace function func() returns trigger as $func$ begin insert into foo values(1); return null; end; $func$ language plpgsql; create trigger tr after update on y for each statement execute procedure func(); update y set a = a + 1; select * from foo; -- foo is empty, but I think it should have one row. -- trigger after delete failed, either. -- trigger after insert is ok. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #2836: SPI_execute_plan failed on pl/pgsql function that worked on 8.1
The following bug has been logged online: Bug reference: 2836 Logged by: Albert Cervera i Areny Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Debian GNU/Linux Description:SPI_execute_plan failed on pl/pgsql function that worked on 8.1 Details: I get the following error on a function that worked correctly on 8.1: ERROR: SPI_execute_plan failed executing query UPDATE pg_catalog.pg_settings SET setting= $1 WHERE name='search_path': Unrecognized SPI code 0 CONTEXT: PL/pgSQL function ventas_por_grupo_conjunta_tmp line 23 at SQL statement SQL function ventas_por_grupo_conjunta statement 1 The function tries to reestablish the search_path with the following UPDATE command: $body$ SELECT setting INTO path FROM pg_catalog.pg_settings WHERE name='search_path'; ...here the rest of the function... UPDATE pg_catalog.pg_settings SET setting=path WHERE name='search_path $body$ I've been able to work around this by using: EXECUTE 'UPDATE pg_catalog.pg_settings SET setting=''' || path || ''' WHERE name=''search_path'''; instead. Hope this helps in finding out the problem. I don't think the behaviour is intended. Please, contact if you want me to test any patches. Thanks! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2837: i received ERROR: failed to build any 7-way joins
The following bug has been logged online: Bug reference: 2837 Logged by: canli Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.0 Operating system: Linux ES 4 Description:i received ERROR: failed to build any 7-way joins Details: When I execute a query: SELECT n1.numer AS wydany_nr, n2.numer AS zwrocony_nr FROM operacje o LEFT JOIN (wydane_numery wt1 JOIN boki_ numery bn1 USING(id_nr_w_bok) JOIN numery n1 USING(id_numeru )) ON(wt1.id_operacji = o.id_operacji AND wt1.wydanie_zwrot = 'W') LEFT JOIN (wydane_numery wt2 JOIN boki_numery bn2 USING(id_nr_w_bok) JOIN numery n2 USING(id_numeru )) ON(wt2.id_operacji = o.id_operacji AND wt2.wydanie_zwrot = 'Z') WHERE o.id_operacji = 349736 i received an error: failed to build any 7-way joins ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] BUG #2838: Compile error tab-complete.c on Mac OS X
The following bug has been logged online: Bug reference: 2838 Logged by: Thomas Jahnsen Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2 Operating system: Mac OS X 10.4.8 PPC Description:Compile error tab-complete.c on Mac OS X Details: I try to compile PostgreSQL 8.2, but it fails in the file tab-complete.c (src/bin/psql/tab-complete.c). It reports an undeclared function call; 'rl_filename_completion_function' First, some info on my system: Apple iMac G5 2.1 GHz Mac OS X 10.4.8 PPC Apple Developer Tools version 2.4.1 (latest) Compiler: powerpc-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5367) This is the error message it gets to when you call make - this skips a lot of what happens beforehand: -- Error Message Start gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -DFRONTEND -I. -I../../../src/interfaces/libpq -I../../../src/bin/pg_dump -I../../../src/include -c -o tab-complete.o tab-complete.c tab-complete.c: In function 'psql_completion': tab-complete.c:574: warning: implicit declaration of function 'rl_completion_matches' tab-complete.c:574: warning: assignment makes pointer from integer without a cast tab-complete.c:578: warning: assignment makes pointer from integer without a cast tab-complete.c:583: warning: assignment makes pointer from integer without a cast tab-complete.c:591: warning: assignment makes pointer from integer without a cast tab-complete.c:607: warning: assignment makes pointer from integer without a cast tab-complete.c:617: warning: assignment makes pointer from integer without a cast tab-complete.c:628: warning: assignment makes pointer from integer without a cast tab-complete.c:638: warning: assignment makes pointer from integer without a cast tab-complete.c:648: warning: assignment makes pointer from integer without a cast tab-complete.c:654: warning: assignment makes pointer from integer without a cast tab-complete.c:667: warning: assignment makes pointer from integer without a cast tab-complete.c:675: warning: assignment makes pointer from integer without a cast tab-complete.c:684: warning: assignment makes pointer from integer without a cast tab-complete.c:694: warning: assignment makes pointer from integer without a cast tab-complete.c:704: warning: assignment makes pointer from integer without a cast tab-complete.c:714: warning: assignment makes pointer from integer without a cast tab-complete.c:724: warning: assignment makes pointer from integer without a cast tab-complete.c:729: warning: assignment makes pointer from integer without a cast tab-complete.c:735: warning: assignment makes pointer from integer without a cast tab-complete.c:744: warning: assignment makes pointer from integer without a cast tab-complete.c:749: warning: assignment makes pointer from integer without a cast tab-complete.c:762: warning: assignment makes pointer from integer without a cast tab-complete.c:768: warning: assignment makes pointer from integer without a cast tab-complete.c:778: warning: assignment makes pointer from integer without a cast tab-complete.c:784: warning: assignment makes pointer from integer without a cast tab-complete.c:791: warning: assignment makes pointer from integer without a cast tab-complete.c:800: warning: assignment makes pointer from integer without a cast tab-complete.c:806: warning: assignment makes pointer from integer without a cast tab-complete.c:818: warning: assignment makes pointer from integer without a cast tab-complete.c:822: warning: assignment makes pointer from integer without a cast tab-complete.c:828: warning: assignment makes pointer from integer without a cast tab-complete.c:837: warning: assignment makes pointer from integer without a cast tab-complete.c:843: warning: assignment makes pointer from integer without a cast tab-complete.c:852: warning: assignment makes pointer from integer without a cast tab-complete.c:861: warning: assignment makes pointer from integer without a cast tab-complete.c:870: warning: assignment makes pointer from integer without a cast tab-complete.c:879: warning: assignment makes pointer from integer without a cast tab-complete.c:886: warning: assignment makes pointer from integer without a cast tab-complete.c:892: warning: assignment makes pointer from integer without a cast tab-complete.c:902: warning: assignment makes pointer from integer without a cast tab-complete.c:910: warning: assignment makes pointer from integer without a cast tab-complete.c:914: warning: assignment makes pointer from integer without a cast tab-complete.c:921: warning: assignment makes pointer from integer without a cast tab-complete.c:931: warning: assignment makes pointer from integer without a cast tab-complete.c:935: warning: assignment makes pointer from integer without a cast tab-complete.c:944: warning: assignment makes pointer from integer without a cast tab-complete.c:949:
[BUGS] No error when FROM is missing in subquery
Following query is considered as correct, no missing from error has been reported (so, entire table will be updated and on update triggers will be fired for every row): update item set obj_id = obj_id where obj_id in (select obj_id where item_point is null order by obj_modified limit 10) Is it a bug? If no, maybe to produce warning in such cases? -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
ok, sorry, I've realized that it's yet another example of outer reference, Tom will say read any SQL book again :-) http://archives.postgresql.org/pgsql-bugs/2006-12/msg00115.php On 12/19/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: Following query is considered as correct, no missing from error has been reported (so, entire table will be updated and on update triggers will be fired for every row): update item set obj_id = obj_id where obj_id in (select obj_id where item_point is null order by obj_modified limit 10) Is it a bug? If no, maybe to produce warning in such cases? -- Best regards, Nikolay -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
On 12/19/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: Following query is considered as correct, no missing from error has been reported (so, entire table will be updated and on update triggers will be fired for every row): update item set obj_id = obj_id where obj_id in (select obj_id where item_point is null order by obj_modified limit 10) Is it a bug? If no, maybe to produce warning in such cases? On 12/18/06, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: ok, sorry, I've realized that it's yet another example of outer reference, Tom will say read any SQL book again :-) http://archives.postgresql.org/pgsql-bugs/2006-12/msg00115.php not really... AFAIK, the FROM clause is mandatory per SQL... older releases of postgres fill the missing from clause if it was easy to determine, in recent releases it's mandatory unless you specify the opposite in postgresql.conf with the add_missing_from parameter -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] No error when FROM is missing in subquery
Is it a bug? If no, maybe to produce warning in such cases? oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... no clue which ones :-/ - thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
On 12/18/06, Thomas H. [EMAIL PROTECTED] wrote: Is it a bug? If no, maybe to produce warning in such cases? oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... and the UPDATE was? also the limit applies only to the subselect, it has nothing to do with the upper query so the upper query can return more than number of rows specified in the subselect... no clue which ones :-/ LIMIT is often meaningfull only in conjuction with ORDER BY -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] No error when FROM is missing in subquery
oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... and the UPDATE was? that was done by the application with the returned recordset. also the limit applies only to the subselect, it has nothing to do with the upper query so the upper query can return more than number of rows specified in the subselect... IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... LIMIT is often meaningfull only in conjuction with ORDER BY yep but not here. all i wanted to do is to get names from 2 movies and run an *observed* edit on them. what did pgsql actually do with that subquery? did it return all records for which mov_name match '%, %'? - thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] No error when FROM is missing in subquery
On 12/18/06, Thomas H. [EMAIL PROTECTED] wrote: oups. just thumbled over this as well when i forgot a FROM in a WHERE ... IN () and damaged quite some data. the bad query went like this: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 and returned 3706 records out of ~13... and the UPDATE was? that was done by the application with the returned recordset. also the limit applies only to the subselect, it has nothing to do with the upper query so the upper query can return more than number of rows specified in the subselect... IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... have you tested the query in psql? what results do you get? -- regards, Jaime Casanova Programming today is a race between software engineers striving to build bigger and better idiot-proof programs and the universe trying to produce bigger and better idiots. So far, the universe is winning. Richard Cook ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] No error when FROM is missing in subquery
SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... have you tested the query in psql? what results do you get? the data is damaged so the result isn't the same... regenearting it now from a backup. from first tests i would say it returned records with names that match the WHERE in the subselect. i guess what happened is: it took each record in movies.names, then run the subquery for that record which resulted in WHERE mov_id IN (mov_id) = true for records with a ', ' in the name and WHERE mov_id IN () = false for all others. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2837: i received ERROR: failed to build any 7-way joins
canli [EMAIL PROTECTED] writes: i received an error: failed to build any 7-way joins I think this is the same problem already reported here: http://archives.postgresql.org/pgsql-bugs/2006-12/msg00092.php and patched here: http://archives.postgresql.org/pgsql-committers/2006-12/msg00102.php regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] No error when FROM is missing in subquery
Thomas H. [EMAIL PROTECTED] writes: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) the subselect is missing a FROM table. in that case, pgsql seemed to also ignore the LIMIT 2 It didn't ignore anything. Each execution of the sub-select returned 1 row, containing the current mov_id from the outer query. So basically this would've selected everything passing the LIKE condition. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] No error when FROM is missing in subquery
Also check that the mov_id column exists in the table/view that you are running the SELECT DISTINCT against. Pgsql does not throw an error (at least prior to 8.2) if the column referenced by the select statement for the IN clause does not exist. It will run only SELECT * FROM movies.names in this case. Mike On Tue, 2006-12-19 at 06:01 +0100, Thomas H. wrote: SELECT * FROM movies.names WHERE mov_id IN (SELECT DISTINCT mov_id WHERE mov_name like '%, %' LIMIT 2) IF the subquery would only have returned 2 ids, then there would be at most like +/-10 records affected. each mov_id can hold one or more (usuals up to 5) names. but here, the subquery seemed to return ~3700 distinct mov_ids, thus around 37000 names where damaged by the following programmatical updates instead of only a hands full... have you tested the query in psql? what results do you get? the data is damaged so the result isn't the same... regenearting it now from a backup. from first tests i would say it returned records with names that match the WHERE in the subselect. i guess what happened is: it took each record in movies.names, then run the subquery for that record which resulted in WHERE mov_id IN (mov_id) = true for records with a ', ' in the name and WHERE mov_id IN () = false for all others. - thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 1: 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: [BUGS] No error when FROM is missing in subquery
mike [EMAIL PROTECTED] writes: Pgsql does not throw an error (at least prior to 8.2) if the column referenced by the select statement for the IN clause does not exist. My, there's a lot of misinformation in this thread. The reason there's no error thrown is that the reference to mov_id in the sub-SELECT is a perfectly legal outer reference to the mov_id column available from the upper SELECT. If the column truly did not exist anywhere in the tables used in the query, it would have thrown an error. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #2838: Compile error tab-complete.c on Mac OS X
Thomas Jahnsen [EMAIL PROTECTED] writes: I try to compile PostgreSQL 8.2, but it fails in the file tab-complete.c (src/bin/psql/tab-complete.c). It reports an undeclared function call; 'rl_filename_completion_function' Please be specific about the version of libreadline (or Apple's libedit) you are using. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
You are right as usual My apologies yet again. I have wrongly assumed that the lower statement would run first since it is enclosed in parenthesis. On Tue, 2006-12-19 at 00:48 -0500, Tom Lane wrote: mike [EMAIL PROTECTED] writes: Pgsql does not throw an error (at least prior to 8.2) if the column referenced by the select statement for the IN clause does not exist. My, there's a lot of misinformation in this thread. The reason there's no error thrown is that the reference to mov_id in the sub-SELECT is a perfectly legal outer reference to the mov_id column available from the upper SELECT. If the column truly did not exist anywhere in the tables used in the query, it would have thrown an error. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
