Re: [BUGS] BUG #5869: postgresql corrupts unquoted non-ascii chars in column aliases
On 03.03.2011 16:08, Robert Haas wrote: On Tue, Feb 8, 2011 at 2:19 AM, David Schmitt wrote: Example query: SELECT column as zurück FROM table; results in corruption of the "ü" (umlaut u). This causes Npgsql to fail to match up the columns in the result set (see http://pgfoundry.org/tracker/?func=detail&aid=1010988&group_id=1000140&atid= 590) and pgadminIII doesn't display the alias in the result pane. That link doesn't show anything interesting when I look at it. What exactly do you mean by corrupted? There are some garbage bytes instead of the umlaut. The same query works with the same tools against a 8.4.5 running on Debian squeeze. If the alias is quoted like this: SELECT column as "zurück" FROM table; the query also works against the DB running on windows. PS: I tried downloading the newest version of the one click installer for windows from EnterpriseDB, and noticed that since recently that site requires a registration :-( I think this has been fixed. Please let me know if not. I presume there is a new 8.4.5 version at enterprisedb.com ? I'll have a look at it later. Best Regards, David -- dasz.at OG Tel: +43 (0)664 2602670 Web: http://dasz.at Klosterneuburg UID: ATU64260999 FB-Nr.: FN 309285 g FB-Gericht: LG Korneuburg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5869: postgresql corrupts unquoted non-ascii chars in column aliases
The following bug has been logged online: Bug reference: 5869 Logged by: David Schmitt Email address: da...@dasz.at PostgreSQL version: 8.4.5 Operating system: Windows 7 Description:postgresql corrupts unquoted non-ascii chars in column aliases Details: Example query: SELECT column as zurück FROM table; results in corruption of the "ü" (umlaut u). This causes Npgsql to fail to match up the columns in the result set (see http://pgfoundry.org/tracker/?func=detail&aid=1010988&group_id=1000140&atid= 590) and pgadminIII doesn't display the alias in the result pane. The same query works with the same tools against a 8.4.5 running on Debian squeeze. If the alias is quoted like this: SELECT column as "zurück" FROM table; the query also works against the DB running on windows. PS: I tried downloading the newest version of the one click installer for windows from EnterpriseDB, and noticed that since recently that site requires a registration :-( -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5673: Optimizer creates strange execution plan leading to wrong results
On 9/23/2010 9:53 PM, Tom Lane wrote: David Schmitt writes: Executing the attached example.sql on a fresh database demonstrates the problem I'm seeing: Found it. If you need a patch right away, it's a one-liner: diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c index 2d86da3..b7cf0b8 100644 --- a/src/backend/optimizer/path/allpaths.c +++ b/src/backend/optimizer/path/allpaths.c @@ -425,7 +425,7 @@ set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, childpath = childrel->cheapest_total_path; if (IsA(childpath, AppendPath)) subpaths = list_concat(subpaths, - ((AppendPath *) childpath)->subpaths); + list_copy(((AppendPath *) childpath)->subpaths)); else subpaths = lappend(subpaths, childpath); Thank you very very much, you made my day! *phew* Best Regards, David -- dasz.at OG Tel: +43 (0)664 2602670 Web: http://dasz.at Klosterneuburg UID: ATU64260999 FB-Nr.: FN 309285 g FB-Gericht: LG Korneuburg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5673: Optimizer creates strange execution plan leading to wrong results
On 9/23/2010 5:33 PM, Tom Lane wrote: "David Schmitt" writes: Description:Optimizer creates strange execution plan leading to wrong results Please supply a self-contained example demonstrating the incorrect results. The information you've provided is completely inadequate for investigating this problem report. A SQL script that creates some test tables and executes a query that gives wrong answers would be helpful. Executing the attached example.sql on a fresh database demonstrates the problem I'm seeing: postg...@db:~$ dropdb test; createdb test; psql test < example.sql [load dump] [execute query] ID | CreatedOn | ChangedOn | ExportGuid | fk_CreatedBy | fk_ChangedBy | fk_Property | ID | CreatedOn | ChangedOn | ExportGuid | fk_CreatedBy | fk_ChangedBy | fk_Property | C1 | C2 | C3 | C4 | fk_EnumVal +++--+--+--+-++++--+--+--+-+++++ 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | | 783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | | 783 | 60 | f | t | f | 3 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | | 783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | | 783 | 60 | f | t | f | 3 (2 rows) [remove pk] [execute query again] ALTER TABLE ID | CreatedOn | ChangedOn | ExportGuid | fk_CreatedBy | fk_ChangedBy | fk_Property | ID | CreatedOn | ChangedOn | ExportGuid | fk_CreatedBy | fk_ChangedBy | fk_Property | C1 | C2 | C3 | C4 | fk_EnumVal +++--+--+--+-++++--+--+--+-+++++ 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | | 783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | | 783 | 60 | f | t | f | 3 (1 row) Hopefully, this helps you debugging it! Thanks for your time and work on the otherwise really great PostgreSQL. Best Regards, David -- dasz.at OG Tel: +43 (0)664 2602670 Web: http://dasz.at Klosterneuburg UID: ATU64260999 FB-Nr.: FN 309285 g FB-Gericht: LG Korneuburg -- -- PostgreSQL database dump -- SET statement_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = off; SET check_function_bodies = false; SET client_min_messages = warning; SET escape_string_warning = off; SET default_with_oids = false; CREATE OR REPLACE FUNCTION uuid_generate_v4() RETURNS uuid AS '$libdir/uuid-ossp', 'uuid_generate_v4' LANGUAGE 'c' VOLATILE STRICT COST 1; -- -- Name: CurrentDateTimeDefaultValues; Type: TABLE; Schema: dbo; Owner: zbox; Tablespace: -- CREATE TABLE "CurrentDateTimeDefaultValues" ( "ID" integer NOT NULL ); -- -- Name: DefaultPropertyValues; Type: TABLE; Schema: dbo; Owner: zbox; Tablespace: -- CREATE TABLE "DefaultPropertyValues" ( "ID" integer NOT NULL, "CreatedOn" timestamp without time zone, "ChangedOn" timestamp without time zone, "ExportGuid" uuid DEFAULT public.uuid_generate_v4() NOT NULL, "fk_CreatedBy" integer, "fk_ChangedBy" integer, "fk_Property" integer NOT NULL ); -- -- Name: DefaultPropertyValues_ID_seq; Type: SEQUENCE; Schema: dbo; Owner: zbox -- CREATE SEQUENCE "DefaultPropertyValues_ID_seq" START WITH 1 INCREMENT BY 1 NO MAXVALUE NO MINVALUE CACHE 1; -- -- Name: DefaultPropertyValues_ID_seq; Type: SEQUENCE OWNED BY; Schema: dbo; Owner: zbox -- ALTER SEQUENCE "DefaultPropertyValues_ID_seq" OWNED BY "DefaultPropertyValues"."ID"; -- -- Name: DefaultPropertyValues_ID_seq; Type: SEQUEN
[BUGS] BUG #5673: Optimizer creates strange execution plan leading to wrong results
The following bug has been logged online:
Bug reference: 5673
Logged by: David Schmitt
Email address: da...@dasz.at
PostgreSQL version: 8.4.4
Operating system: Windows 7
Description:Optimizer creates strange execution plan leading to
wrong results
Details:
Background: The following statement is a slightly modfied version of a
statement created by the Entity Framework OR-Mapper when doing a specific
query involving the DefaultPropertyValues base class and the three child
classes CurrentDateTimeDefaultValues, NewGuidDefaultValues,
EnumDefaultValues. There are 60 unique rows in DefaultProperty Values, each
of which is referenced by exactly one row in either
CurrentDateTimeDefaultValues(5 rows), NewGuidDefaultValues(54 rows), or
EnumDefaultValues (1 row). All tables have an ID column and appropriate
PK/FK constraints modeling the inheritance.
Analysis: In the first case the optimizer creates a single Append operation
for the three sequential table scans (lines 10-13) and creates an additional
Index Scan on EnumDefaultValues (lines 14,15) which results in 61 rows
returned in 09. Particularily suspicious are the tow references to the
Extent5 alias on lines 13 and 14. In the second run, I dropped the primary
key on EnumDefaultValues. Now the Append on line 09 returns 60 rows and the
execution plan references EnumDefaultValues only once.
select *
from "dbo"."DefaultPropertyValues" AS "Extent1"
INNER JOIN "dbo"."DefaultPropertyValues" AS "Extent2" ON
"Extent1"."ID"="Extent2"."ID"
LEFT JOIN (
SELECT "UnionAll1"."ID" AS "C1","UnionAll1"."C1" AS
"C2","UnionAll1"."C2"
AS "C3","UnionAll1"."C3" AS "C4","UnionAll1"."C4" AS "fk_EnumVal" FROM (
SELECT "Extent3"."ID" AS "ID",TRUE AS "C1",FALSE AS
"C2",FALSE AS
"C3",CAST (NULL AS int4) AS "C4"
FROM "dbo"."CurrentDateTimeDefaultValues" AS
"Extent3"
UNION ALL
SELECT "Extent4"."ID" AS "ID", TRUE AS "C1",FALSE AS
"C2",TRUE AS
"C3",CAST (NULL AS int4) AS "C4"
FROM "dbo"."NewGuidDefaultValues" AS "Extent4")
AS "UnionAll1"
UNION ALL
SELECT "Extent5"."ID" AS "ID",FALSE AS "C1",TRUE AS "C2",FALSE
AS
"C3","Extent5"."fk_EnumValue" AS "fk_EnumValue"
FROM "dbo"."EnumDefaultValues" AS "Extent5") AS
"UnionAll2"
ON "Extent2"."ID"="UnionAll2"."C1"
WHERE ("Extent1"."fk_Property" IS NOT NULL ) AND ("Extent1"."fk_Property"=
(((783
Result:
60;"2010-09-20 13:22:17.773308";"2010-09-20
13:22:17.773308";"ecbd6901-13e8-4beb-a6a2-0548eba345a8"783;60;"2010-09-20
13:22:17.773308";"2010-09-20
13:22:17.773308";"ecbd6901-13e8-4beb-a6a2-0548eba345a8"783;60;f;t;f;3
60;"2010-09-20 13:22:17.773308";"2010-09-20
13:22:17.773308";"ecbd6901-13e8-4beb-a6a2-0548eba345a8"783;60;"2010-09-20
13:22:17.773308";"2010-09-20
13:22:17.773308";"ecbd6901-13e8-4beb-a6a2-0548eba345a8"783;60;f;t;f;3
EXPLAIN ANALYZE:
01 "Nested Loop Left Join (cost=1.76..7.42 rows=1 width=100) (actual
time=0.121..0.134 rows=2 loops=1)"
02 " Join Filter: ("Extent2"."ID" = "Extent3"."ID")"
03 " -> Hash Join (cost=1.76..3.60 rows=1 width=96) (actual
time=0.061..0.061 rows=1 loops=1)"
04 "Hash Cond: ("Extent2"."ID" = "Extent1"."ID")"
05 "-> Seq Scan on "DefaultPropertyValues" "Extent2"
(cost=0.00..1.60 rows=60 width=48) (actual time=0.008..0.013 rows=60
loops=1)"
06 "-> Hash (cost=1.75..1.75 rows=1 width=48) (actual
time=0.025..0.025 rows=1 loops=1)"
07 " -> Seq Scan on "DefaultPropertyValues" "Extent1"
(cost=0.00..1.75 rows=1 width=48) (actual time=0.023..0.024 rows=1
loops=1)"
08 "Filter: (("fk_Property" IS NOT NULL) AND
("fk_Property" = 783))"
09 " -> Append (cost=0.00..4.07 rows=61 width=11) (actual
time=0.005..0.046 rows=61 loops=1)"
10 "-> Ap
Re: [BUGS] BUG #5549: Feature: plpgsql should allow DECLARE cursor FOR EXECUTE '...'
On 7/9/2010 4:53 PM, Tom Lane wrote: "David Schmitt" writes: This is not implemented: DECLARE working_cursor NO SCROLL CURSOR FOR EXECUTE $$SELECT something $$ || tbl || $$ ... $$; What's wrong with OPEN FOR EXECUTE? The proposed addition seems a bit weird anyway since it presumes nontrivial calculation to be done during variable initialization. I've investigated further and found that the correct formulation is: FOR rec IN EXECUTE $$SELECT something $$ || tbl || $$ ... $$ LOOP END LOOP If I understand the documentation correctly this does exactly what I need: open and close a cursor automatically with the dynamic statement and loop over its result set. Actually, this is even more consise and to the point that what I had in mind first. This syntax is documented in the lower half of "Looping Through Query Results"[1]. It is not mentioned in "Looping Through a Cursor's Result"[2] where I would have (naively) expected it. My confusion seems to arise from the fact that FOR loops are described first without mentioning cursors at all and then a "different" FOR is introduced specifically for use with cursors. Thanks for your time and work, David Schmitt [1]http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING [2]http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP -- dasz.at OG Tel: +43 (0)664 2602670 Web: http://dasz.at Klosterneuburg UID: ATU64260999 FB-Nr.: FN 309285 g FB-Gericht: LG Korneuburg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5549: Feature: plpgsql should allow DECLARE cursor FOR EXECUTE '...'
The following bug has been logged online: Bug reference: 5549 Logged by: David Schmitt Email address: da...@dasz.at PostgreSQL version: 8 and 9 Operating system: n/a Description:Feature: plpgsql should allow DECLARE cursor FOR EXECUTE '...' Details: This is not implemented: - CREATE OR REPLACE FUNCTION foo(tbl text) RETURNS void AS $BODY$ DECLARE working_cursor NO SCROLL CURSOR FOR EXECUTE $$SELECT something $$ || tbl || $$ ... $$; BEGIN FOR rec IN working_cursor LOOP -- ... END LOOP; END$BODY$ LANGUAGE 'plpgsql' VOLATILE; - The obvious workaround is hand-coding the LOOP with OPEN ... FOR EXECUTE and manual FETCHing, but it would be great to have this missing piece supported in syntax and avoid duplicating the error-prone hand-coding. Thanks for your time and work! -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
