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 Scan on combined_rows (cost=0.27..8.30 rows=1 width=76)
-> Index Only Scan using document_acl_text_pkey on document_acl_text acl
(cost=0.27..8.29 rows=1 width=52)
Index Cond: (uuid = '8f774048-8936-4d7f-aa38-1974c91bbef2'::text)
Planning time: 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 Scan on combined_rows (cost=0.27..8.30 rows=1 width=76)
-> Index Scan using document_acl_text_pkey on document_acl_text acl
(cost=0.27..8.29 rows=1 width=52)
Index Cond: (uuid = '8f774048-8936-4d7f-aa38-1974c91bbef2'::text)
Planning time: 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,
PRIMARY KEY ("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.4beta3 on 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. reeds...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
