row_to_json bug with index only scans: empty keys!
От | Ross Reedstrom |
---|---|
Тема | row_to_json bug with index only scans: empty keys! |
Дата | |
Msg-id | 20141107155135.GA11578@rice.edu обсуждение исходный текст |
Ответы |
Re: row_to_json bug with index only scans: empty keys!
(Andrew Dunstan <andrew@dunslane.net>)
|
Список | pgsql-hackers |
This is a serious bug in 9.3.5 and 9.4 beta3: row_to_json() yields empty strings for json keys if the data is fulfilled by an index only scan. Example: testjson=# select count(*) from document_acl;count ------- 426 (1 row) testjson=# SELECT row_to_json(combined_rows) FROM ( SELECT uuid, user_id AS uid, permission FROM document_acl_text AS acl WHERE uuid = '8f774048-8936-4d7f-aa38-1974c91bbef2' ORDER BY user_id ASC, permission ASC ) as combined_rows; row_to_json ---------------------------------------------------------------------{"":"8f774048-8936-4d7f-aa38-1974c91bbef2","":"admin","":"publish"} (1 row) testjson=# explain SELECT row_to_json(combined_rows) FROM ( SELECT uuid, user_id AS uid, permission FROM document_acl_text AS acl WHERE uuid = '8f774048-8936-4d7f-aa38-1974c91bbef2' ORDER BY user_id ASC, permission ASC ) as combined_rows; QUERY PLAN ----------------------------------------------------------------------------------------------------------------Subquery Scanon combined_rows (cost=0.27..8.30 rows=1 width=76) -> Index Only Scan using document_acl_text_pkey on document_acl_textacl (cost=0.27..8.29 rows=1 width=52) Index Cond: (uuid = '8f774048-8936-4d7f-aa38-1974c91bbef2'::text)Planningtime: 0.093 ms (4 rows) # set enable_indexonlyscan to off; SET testjson=# SELECT row_to_json(combined_rows) FROM ( SELECT uuid, user_id AS uid, permission FROM document_acl_text AS acl WHERE uuid = '8f774048-8936-4d7f-aa38-1974c91bbef2' ORDER BY user_id ASC, permission ASC ) as combined_rows; row_to_json ------------------------------------------------------------------------------------------{"uuid":"8f774048-8936-4d7f-aa38-1974c91bbef2","user_id":"admin","permission":"publish"} (1 row) tjson=# explain SELECT row_to_json(combined_rows) FROM ( SELECT uuid, user_id AS uid, permission FROM document_acl_text AS acl WHERE uuid = '8f774048-8936-4d7f-aa38-1974c91bbef2' ORDER BY user_id ASC, permission ASC ) as combined_rows; QUERY PLAN -----------------------------------------------------------------------------------------------------------Subquery Scanon combined_rows (cost=0.27..8.30 rows=1 width=76) -> Index Scan using document_acl_text_pkey on document_acl_textacl (cost=0.27..8.29 rows=1 width=52) Index Cond: (uuid = '8f774048-8936-4d7f-aa38-1974c91bbef2'::text)Planningtime: 0.095 ms (4 rows) We have a table defined as so: CREATE TYPE permission_type AS ENUM ( 'publish' ); create table "document_acl" ( "uuid" UUID, "user_id" TEXT, "permission" permission_type NOT NULL, PRIMARYKEY ("uuid", "user_id", "permission"), FOREIGN KEY ("uuid") REFERENCES document_controls ("uuid") ); The uuid and enums make no difference - I've made an all text version as well, same problem. testjson=# select version(); version ---------------------------------------------------------------------------------------------------------PostgreSQL 9.4beta3on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit (1 row) Ross -- Ross Reedstrom, Ph.D. reedstrm@rice.edu Systems Engineer & Admin, Research Scientist phone: 713-348-6166 Connexions http://cnx.org fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
В списке pgsql-hackers по дате отправления: