row_to_json bug with index only scans: empty keys! - Mailing list 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