[BUGS] BUG #3299: pgadmin3 explain partial object name displayed
The following bug has been logged online: Bug reference: 3299 Logged by: Ferdinand Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: OpenSuSE 10.2 Description:pgadmin3 explain partial object name displayed Details: Hi! Pgadmin3 1.6.3 on SusE 10.2 x86_64 Query -> Explain -> Analyze does not show the complete name of an object containing a blank "Table" is displayed as "Table" "Sub Table" is displayed as "Sub note the missing > Table"< ! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] ERROR: invalid input syntax for integer: ""
Following query fails in pgAdmin which is OK because the field PLZZ
contains characters:
select * from "PERSONEN" where (CAST("PERSONEN"."PLZZ" AS INTEGER) >=
7 );
but if using the same query with a cursor ist works:
START TRANSACTION;
DECLARE c21112234 SCROLL CURSOR FOR select * from "PERSONEN" where
(CAST("PERSONEN"."PLZZ" AS INTEGER) >= 7;
COMMIT;
Why??
In my opinion this query should also fail with cursors.
I use libpq with PG 8.2.1.
The field PLZZ is of type varchar.
---(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] ERROR: invalid input syntax for integer: ""
"Pit M." <[EMAIL PROTECTED]> writes:
> Following query fails in pgAdmin which is OK because the field PLZZ
> contains characters:
> select * from "PERSONEN" where (CAST("PERSONEN"."PLZZ" AS INTEGER) >=
> 7 );
> but if using the same query with a cursor ist works:
> START TRANSACTION;
> DECLARE c21112234 SCROLL CURSOR FOR select * from "PERSONEN" where
> (CAST("PERSONEN"."PLZZ" AS INTEGER) >= 7;
> COMMIT;
> In my opinion this query should also fail with cursors.
It would have failed if you had run the cursor far enough to fetch one
of the bad rows.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] [ADMIN]
I already found that it's possible to store passwords in the file pgpass.conf in the %APPDATA% directory. But that file isn't stored in that location. In fact it doesn't exist in my disk. I already checked the "Store password" Filipe, You need to create that file yourself. Using the format you found in the documentation, you can put your login information in the .pgpass file in the user's home directory, or under windows it's under "%APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile)." [1] Hope that helps! Cheers, -J [1] http://developer.postgresql.org/pgdocs/postgres/libpq-pgpass.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] ERROR: invalid input syntax for integer: ""
It would have failed if you had run the cursor far enough to fetch one of the bad rows. regards, tom lane The difference is that in one case the query fails and in the other the FETCH command fails. Our problem is that if a query succeeds we use a count(*) of that query to display the result count in the status bar of our application - the select works but the count(*) fails :-) When we use this query on MS SQL-Server the query fails directly though we also use cursors here. So the SQL-Server somehow checks all the data only for the query -> perhaps because we used a server side cursor. Will there be any improvements with cursors in PG 8.3? Thanks a lot tom ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] ERROR: invalid input syntax for integer: ""
Pit M. wrote: > >> It would have failed if you had run the cursor far enough to fetch one >> of the bad rows. >> >> regards, tom lane >> > The difference is that in one case the query fails and in the other the > FETCH command fails. > > > Our problem is that if a query succeeds we use a count(*) of that query > to display the result count in the status bar of our application - the > select works but the count(*) fails :-) > When we use this query on MS SQL-Server the query fails directly though > we also use cursors here. So the SQL-Server somehow checks all the data > only for the query -> perhaps because we used a server side cursor. IIRC, the behavior of MSSQL will depend on the query plan. If it's a plan that requires doesn't require materialization at all, it won't figure it out until you get there. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] ERROR: invalid input syntax for integer: ""
Magnus Hagander <[EMAIL PROTECTED]> writes: > IIRC, the behavior of MSSQL will depend on the query plan. If it's a > plan that requires doesn't require materialization at all, it won't > figure it out until you get there. ... which is pretty much what we do, too. regards, tom lane ---(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] priority index on planner (feature request)
Dear Postgresql Team: I have some strange behavior on simple queries (i don't know if is a bug, but i prefer to make a feature request), buy we need to tell to the planner a different index priority. For example, i have a table called "calls" with 11 million records, and i need to take the min() and max() values of the primary key with the criteria of another field (index too)... explain ANALYZE select id from calls where campaign_id = 15603 order by id limit 1; QUERY PLAN Limit (cost=0.00..211.17 rows=1 width=4) (actual time=186253.403..186253.404 rows=1 loops=1) -> Index Scan using calls_pkey on calls (cost=0.00..656740.94 rows=3110 width=4) (actual time=186253.398..186253.398 rows=1 loops=1) Filter: (campaign_id = 15603) Total runtime: 186253.449 ms (4 rows) you can see a big amount of time to make the request!!!, but i see the planner is doing the right think, i need more priority on the criteria filter index. Let me show you a little workaround: explain ANALYZE select id from calls where campaign_id = 15603 and campaign_id = 15603 order by id limit 1; QUERY PLAN Limit (cost=3.05..3.05 rows=1 width=4) (actual time=2.954..2.956 rows=1 loops=1) -> Sort (cost=3.05..3.05 rows=1 width=4) (actual time=2.948..2.948 rows=1 loops=1) Sort Key: id -> Index Scan using in_calls_campaign_id on calls (cost=0.00..3.04 rows=1 width=4) (actual time=0.042..1.396 rows=736 loops=1) Index Cond: ((campaign_id = 15603) AND (campaign_id = 15603)) Total runtime: 3.062 ms (6 rows) Why i repeat the condition on the WHERE clause?... Is for instruct the planner to bring more priority to a more complex statement. ummm much better, and the request for the parser and the planner i to use this kind of syntax to give priority to a query: select id from calls where (campaign_id = 15603) order by id limit 1; if you note in other parser operations like a math calculation, the use of parenthesis bring priority in the sql statement. Is posible to make a parenthesis notation to instruct the planner to make another inference in the execution priority? I use PostgreSQL 8.1.3 and 8.2.3 Thanks in advanced Fernando Romo ([EMAIL PROTECTED]). signature.asc Description: OpenPGP digital signature
[BUGS] BUG #3300: priority index on planner (feature request)
The following bug has been logged online: Bug reference: 3300 Logged by: Fernando Romo Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: CentOS 4.4/5 Description:priority index on planner (feature request) Details: Dear Postgresql Team: I have some strange behavior on simple queries (i don't know if is a bug, but i prefer to make a feature request), buy we need to tell to the planner a different index priority. For example, i have a table called "calls" with 11 million records, and i need to take the min() and max() values of the primary key with the criteria of another field (index too)... explain ANALYZE select id from calls where campaign_id = 15603 order by id limit 1; QUERY PLAN Limit (cost=0.00..211.17 rows=1 width=4) (actual time=186253.403..186253.404 rows=1 loops=1) -> Index Scan using calls_pkey on calls (cost=0.00..656740.94 rows=3110 width=4) (actual time=186253.398..186253.398 rows=1 loops=1) Filter: (campaign_id = 15603) Total runtime: 186253.449 ms (4 rows) you can see a big amount of time to make the request!!!, but i see the planner is doing the right think, i need more priority on the criteria filter index. Let me show you a little workaround: explain ANALYZE select id from calls where campaign_id = 15603 and campaign_id = 15603 order by id limit 1; QUERY PLAN Limit (cost=3.05..3.05 rows=1 width=4) (actual time=2.954..2.956 rows=1 loops=1) -> Sort (cost=3.05..3.05 rows=1 width=4) (actual time=2.948..2.948 rows=1 loops=1) Sort Key: id -> Index Scan using in_calls_campaign_id on calls (cost=0.00..3.04 rows=1 width=4) (actual time=0.042..1.396 rows=736 loops=1) Index Cond: ((campaign_id = 15603) AND (campaign_id = 15603)) Total runtime: 3.062 ms (6 rows) Why i repeat the condition on the WHERE clause?... Is for instruct the planner to bring more priority to a more complex statement. ummm much better, and the request for the parser and the planner i to use this kind of syntax to give priority to a query: select id from calls where (campaign_id = 15603) order by id limit 1; if you note in other parser operations like a math calculation, the use of parenthesis bring priority in the sql statement. Is posible to make a parenthesis notation to instruct the planner to make another inference in the execution priority? I use PostgreSQL 8.1.3, 8.2.3 and 8.2.4 Thanks in advanced Fernando Romo ([EMAIL PROTECTED]). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
