[BUGS] BUG #8143: Backend segmentation fault in pg_trgm
The following bug has been logged on the website: Bug reference: 8143 Logged by: Joel Roller Email address: jrol...@rjobrien.com PostgreSQL version: 9.2.4 Operating system: Debian 6.0.7 + squeeze-pgdg Description: Howdy. We've come across a specific query and query plan that causes a repeatable segmentation fault on the postgresql backend. Thanks, -joel PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit from deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main -- System Information: Debian Release: 6.0.7 APT prefers oldstable-updates APT policy: (500, 'oldstable-updates'), (500, 'oldstable') Architecture: amd64 (x86_64) Kernel: Linux 2.6.32-5-amd64 (SMP w/1 CPU core) Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8) Shell: /bin/sh linked to /bin/dash Versions of packages postgresql-contrib-9.2 depends on: ii libc6 2.11.3-4 Embedded GNU C Library: Shared lib ii libossp-uuid16 1.6.2-1 OSSP uuid ISO-C and C++ - shared l ii libpq5 9.2.4-1.pgdg60+1 PostgreSQL C client library ii libssl0.9.80.9.8o-4squeeze14 SSL shared libraries ii libxml22.7.8.dfsg-2+squeeze7 GNOME XML library ii libxslt1.1 1.1.26-6+squeeze3 XSLT 1.0 processing library - runt ii postgresql-9.2 9.2.4-1.pgdg60+1 object-relational SQL database, ve ii zlib1g 1:1.2.3.4.dfsg-3 compression library - runtime -- Crashing query: select k.kw from keywords k, group_page_map gp where gp.country = '99' and gp.state is null and gp.city is null and gp.group_id = k.group_id and k.kw like 'AB%' and k.kw like 'ABCD%'; -- Output: The connection to the server was lost. Attempting reset: Failed. ! QUERY PLAN - Nested Loop (cost=0.00..137.43 rows=1 width=11) Join Filter: (k.group_id = gp.group_id) - Seq Scan on group_page_map gp (cost=0.00..92.81 rows=1 width=4) Filter: ((state IS NULL) AND (city IS NULL) AND ((country)::text = '99'::text)) - Index Scan using tgm_kw_crash on keywords k (cost=0.00..44.49 rows=10 width=15) Index Cond: (((kw)::text ~~ 'AB%'::text) AND ((kw)::text ~~ 'ABCD%'::text)) # gdb /usr/lib/postgresql/9.2/bin/postgres postgres-12458.core GNU gdb (GDB) 7.3-debian Copyright (C) 2011 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later http://gnu.org/licenses/gpl.html This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type show copying and show warranty for details. This GDB was configured as x86_64-linux-gnu. For bug reporting instructions, please see: http://www.gnu.org/software/gdb/bugs/... Reading symbols from /usr/lib/postgresql/9.2/bin/postgres...Reading symbols from /usr/lib/debug/usr/lib/postgresql/9.2/bin/postgres...done. done. [New LWP 12458] warning: Can't read pathname for load map: Input/output error. [Thread debugging using libthread_db enabled] Core was generated by `postgres: roller htcrash [local] SELECT '. Program terminated with signal 11, Segmentation fault. #0 0x7ff4b9d9b5a7 in pfree (pointer=0x7ff4bb377818) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/utils/mmgr/mcxt.c:659 659 /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/utils/mmgr/mcxt.c: No such file or directory. in /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/utils/mmgr/mcxt.c (gdb) bt #0 0x7ff4b9d9b5a7 in pfree (pointer=0x7ff4bb377818) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/utils/mmgr/mcxt.c:659 #1 0x7ff4b2f93c79 in gtrgm_consistent (fcinfo=0x7fff759b7a40) at /tmp/buildd/postgresql-9.2-9.2.4/build/../contrib/pg_trgm/trgm_gist.c:245 #2 0x7ff4b9d81372 in FunctionCall5Coll (flinfo=0x7ff4bb377878, collation=3140974616, arg1=optimized out, arg2=7088982889980756224, arg3=140689089722480, arg4=140689089754640, arg5=140735166512862) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/utils/fmgr/fmgr.c:1407 #3 0x7ff4b9aa8955 in gistindex_keytest (recheck_p=optimized out, offset=optimized out, page=optimized out, tuple=optimized out, scan=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/access/gist/gistget.c:139 #4 gistScanPage (scan=optimized out, pageItem=optimized out, myDistances=optimized out, tbm=optimized out, ntids=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/access/gist/gistget.c:312 #5 0x7ff4b9aa9077 in gistgettuple (fcinfo=optimized out) at /tmp/buildd/postgresql-9.2-9.2.4/build/../src/backend/access/gist/gistget.c:494 #6 0x7ff4b9d81603 in FunctionCall2Coll (flinfo=0x7ff4bb377878, collation=3140974616, arg1=0, arg2=7088982889980756224)
[BUGS] Inconsistent PL error handling
Whilst working on a build issue with pl/python, I noticed an inconsistency in the way the server reacts to attempts to use PLs for which the interpreter doesn't exist. Not sure how feasible it would be to fix this, but the Python case doesn't seem ideal: psql.bin (9.3beta1) Type help for help. postgres=# CREATE LANGUAGE plperl; ERROR: could not load library /opt/PostgreSQL/9.3/lib/postgresql/plperl.so: libperl.so: cannot open shared object file: No such file or directory postgres=# CREATE LANGUAGE plpython3u; CREATE LANGUAGE postgres=# CREATE FUNCTION pyversion() RETURNS text AS $$ import sys return sys.version $$ LANGUAGE 'plpython3u'; The connection to the server was lost. Attempting reset: Failed. ! The server was compiled with both Perl and Python support using the ActiveState packages on 64 bit Linux on the build machine, but the runtime machine had neither installed (as would often be the case for installer packages). -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 #8143: Backend segmentation fault in pg_trgm
jrol...@rjobrien.com writes: We've come across a specific query and query plan that causes a repeatable segmentation fault on the postgresql backend. Ah, I see it: gistrescan() is trying to preserve the per-scankey fn_extra values to allow caching, but what it's doing does not work if more than one scankey refers to the same consistentFn, ie, the same index column. A bit surprising we've not seen this before, because I think that code has been like that for awhile. Will fix, thanks for the report! regards, tom lane -- 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 #8144: Problem with rank window function and CTEs
The following bug has been logged on the website: Bug reference: 8144 Logged by: Marc Munro Email address: m...@bloodnok.com PostgreSQL version: 9.2.4 Operating system: Linux 3.6.3 (debian wheezy) Description: I have a query in which I want to use the result of a window function to isolate the most relevant results. While I was trying to develop and test the query, I discovered what looks like a bug in the results of the rank() function. This has been tried with the same results on 9.1.9 and 9.2.4 I have two versions of the query, the first works as expected while the second produces incorrect rank values. I have tried various work-arounds but they all seem to trigger the same behaviour once a certain degree of complexity is reached. This version of the query seems to work, producing the expected rank values: with recursive parent_orgs(parent_org_id, org_id) as ( select pr.first_party_id, pr.second_party_id from party_rels pr where pr.relationship_type_id in (1009, 1010, 1011, 1012, 1013) ), ancestor_orgs( ancestor_org_id, org_id, depth) as ( select org_id, org_id, 0 from parent_orgs union all select p.parent_org_id, a.org_id, a.depth + 1 from ancestor_orgs a join parent_orgs p on p.org_id = a.ancestor_org_id ) select ao.org_id, oi.item_id, oi.seq_id, oi.complete, ao.ancestor_org_id, ao.depth, rank() over (partition by oi.item_id order by ao.depth) from ancestor_orgs ao join oitems oi on oi.org_id = ao.ancestor_org_id where ao.org_id = 20150; org_id | item_id | seq_id | complete | ancestor_org_id | depth | rank +-++--+-+---+-- 20150 | 1 | 1 | t| 20139 | 4 |1 20150 | 2 | 1 | t| 20139 | 4 |1 20150 | 200146 | 1 | t| 20146 | 3 |1 20150 | 200147 | 1 | t| 20146 | 3 |1 20150 | 200148 | 1 | t| 20146 | 3 |1 20150 | 200149 | 2 | t| 20146 | 3 |1 20150 | 200150 | 1 | t| 20146 | 3 |1 20150 | 200151 | 1 | t| 20146 | 3 |1 20150 | 200152 | 1 | t| 20146 | 3 |1 20150 | 200153 | 7 | t| 20150 | 0 |1 20150 | 200153 | 1 | t| 20146 | 3 |2 20150 | 200154 | 1 | t| 20146 | 3 |1 [ rows removed for brevity ] (38 rows) This version, which should be equivalent, yields crazy rank values: with recursive parent_orgs(parent_org_id, org_id) as ( select pr.first_party_id, pr.second_party_id from party_rels pr where pr.relationship_type_id in (1009, 1010, 1011, 1012, 1013) ), ancestor_orgs( ancestor_org_id, org_id, depth) as ( select org_id, org_id, 0 from parent_orgs union all select p.parent_org_id, a.org_id, a.depth + 1 from ancestor_orgs a join parent_orgs p on p.org_id = a.ancestor_org_id ), visible_org_items(org_id, item_id, seq_id, complete, ancestor_org_id, depth, rank) as ( select ao.org_id, oi.item_id, oi.seq_id, oi.complete, ao.ancestor_org_id, ao.depth, rank() over (partition by oi.item_id order by ao.depth) from ancestor_orgs ao join oitems oi on oi.org_id = ao.ancestor_org_id ) select * from visible_org_items where org_id = 20150; org_id | item_id | seq_id | complete | ancestor_org_id | depth | rank +-++--+-+---+-- 20150 | 1 | 1 | t| 20139 | 4 | 21 20150 | 2 | 1 | t| 20139 | 4 | 21 20150 | 200146 | 1 | t| 20146 | 3 |9 20150 | 200147 | 1 | t| 20146 | 3 |9 20150 | 200148 | 1 | t| 20146 | 3 |9 20150 | 200149 | 2 | t| 20146 | 3 |9 20150 | 200150 | 1 | t| 20146 | 3 |9 20150 | 200151 | 1 | t| 20146 | 3 |9 20150 | 200152 | 1 | t| 20146 | 3 |9 20150 | 200153 | 7 | t| 20150 | 0 |1 20150 | 200153 | 1 | t| 20146 |
Re: [BUGS] Completely broken replica after PANIC: WAL contains references to invalid pages
On Tue, Apr 2, 2013 at 11:26 AM, Andres Freund and...@2ndquadrant.com wrote: The attached patch fixes this although I don't like the way it knowledge of the point up to which StartupSUBTRANS zeroes pages is handled. One month has passed since the patched version was installed in our production environment and can confirm that everything works perfect. Thank you very much for your prompt help, Andres. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- 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 #8144: Problem with rank window function and CTEs
m...@bloodnok.com writes: I have a query in which I want to use the result of a window function to isolate the most relevant results. While I was trying to develop and test the query, I discovered what looks like a bug in the results of the rank() function. This has been tried with the same results on 9.1.9 and 9.2.4 Well, you didn't provide enough information for somebody else to reproduce the problem, but just looking at this I see no particular reason to think there's a bug. In the first case, the where ao.org_id = 20150 clause filters rows before the rank() is taken, whereas in the second case it filters after the rank() function. I think the larger rank values indicate that there are other rows with different org_id but the same item_id, which you won't see in the final output in either case --- but in the second query, the rank() function does see them. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
