partition table query allocate much memory

[tao tony]

hi guys,

I'm not sure it was a bug,but a query on partition table allocate nearly 100GB 
memory is not normal.pid :119775 as below

[cid:part1.2A42CFAD.715BF014@outlook.com]

SQL:

SELECT DISTINCT etl_source,gid FROM zh_erp.buy2 WHERE gid NOT IN (SELECT gid 
FROM zh_erp.goodsh);

I had modified the query to:SELECT count(DISTINCT etl_source,gid) FROM 
zh_erp.buy2 WHERE gid NOT IN (SELECT gid FROM zh_erp.goodsh);

This problem appear again.

buy2:102GB,438382597 rows,118 partitions.

etl_source VARCHAR,gid INTEGER.

goodsh:20GB,24692534 rows,118 partitions.

gid INTEGER.

dbversion：PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.1 
20170829 (Red Hat 7.2.1-1), 64-bit

mem and paralle setting:

shared_buffers = 8GB

work_mem = 2GB

max_worker_processes = 48   # (change requires restart)
max_parallel_workers_per_gather = 4 # taken from max_parallel_workers
max_parallel_workers = 8# maximum number of 
max_worker_processes that


I reset the work_mem:when it was 1GB or 2GB,the problem appeared,while it set  
to 64NB,128MB,and 512MB,it will run in parallel mode and the memory less than 
10GB.

I'm not sure it was a bug and I also cloud not explain why it allocated so much 
memory.Dosn't each sub partition table allocated the size of work_mem memory 
and not free it?

When work_mem=1GB or more,the  query plan is  a HashAggregate.otherwise it was 
Unique and running on parallel mode.


Thanks!

copy error with json/jsonb types

[tao tony]

hi dears,


I'm using copy and jdbc copyin to build load data to pg,data type in table is 
jsonb.postgresql version are 9.6 and 10.0.

Some records with escape characters would be failed with error"ERROR:  invalid 
input syntax for type json"

but when using insert they could be executed successfully.

please kindly tell me how to fix this issue?

here is the table.

hdb=# \d salejsonb
 Table "public.salejsonb"
 Column  | Type  | Collation | Nullable | Default
-+---+---+--+-
 jsonval | jsonb |   |  |


below is th sample data:

{"uuid":"b93b0307-c71b-4c07-8f29-e490cad3e5f1","version":0,"createInfo":{"time":"2017-11-11T08:59:47.000+0800","operator":{"id":"13832122566","fullName":"马秀菊","qualifiedId":"13832122566"}},"lastModifyInfo":{"time":"2017-11-11T08:59:47.000+0800","operator":{"id":"13832122566","fullName":"马秀菊","qualifiedId":"13832122566"}},"number":"17-002","shop":"a02a7070","posNo":"1","amount":14.1000,"discountAmount":0.,"changeAmount":0.,"qty":0.4700,"lines":[{"uuid":"14fdcfe8-6199-4273-91a9-158d719722ae","line":1,"shopSku":"c0229a90-f6c0-41b6-9cd7-a5f85e371b08","shopSkuBarcode":"055","platformSku":"","skuName":"蒸蛋糕（散）","skuMunit":"公斤","price":30.,"qty":0.4700,"amount":14.1000,"costPrice":18.,"saleLine":"d47ea60e-da40-410c-80e6-7716eae2e592"},{"uuid":"c443ef5c-865d-458b-bb0a-af3f11446413","line":2,"shopSku":"c892cc8b-8bec-4fb2-95c3-b2466e09d6fb","shopSkuBarcode":"053","platformSku":"","skuName":"达利园派\\蛋糕（散）","skuMunit":"公斤","price":24.,"qty":0.,"amount":0.,"costPrice":16.,"saleLine":"1e114c97-83a3-4dce-8655-a17f21aeb39b"},{"uuid":"58be2eab-dd49-4b9e-983f-c17a7d3a4c7e","line":3,"shopSku":"c892cc8b-8bec-4fb2-95c3-b2466e09d6fb","shopSkuBarcode":"053","platformSku":"","skuName":"达利园派\\蛋糕（散）","skuMunit":"公斤","price":24.,"qty":0.,"amount":0.,"costPrice":16.,"saleLine":"09efe69a-7b61-4c67-a8c3-05c859e4210d"},{"uuid":"7a6a9613-e3b5-44d4-bbc1-b9ae2d6bdde8","line":4,"shopSku":"cf3ecdb1-6c1c-4536-ac25-14fd774f377e","shopSkuBarcode":"050","platformSku":"","skuName":"伟龙饼干（散）","skuMunit":"公斤","price":14.,"qty":0.,"amount":0.,"costPrice":9.6000,"saleLine":"bb56bfd0-39a5-45d8-9f01-5d2785ed17ac"},{"uuid":"195e4c03-b327-4df2-88ac-291070314a64","line":5,"shopSku":"b0880e17-6121-4d39-87c2-d55cd43c3313","shopSkuBarcode":"035","platformSku":"","skuName":"黑皮雪饼仙贝（散）","skuMunit":"公斤","price":22.,"qty":0.,"amount":0.,"costPrice":7.,"saleLine":"8fb2b7d5-4aff-4867-95dc-0bfb41c4b6e7"}],"payments":[{"uuid":"3da970b3-7cdc-4b3b-9256-b55c2c01a6ad","line":1,"paymentMethod":"CASH","paidAmount":14.1000,"payingAmount":0.,"payChannel":"CASH","created":"2017-11-11T08:59:47.000+0800"}],"sale":"a919eb97-5d2f-411b-9348-1f4a8902e807","saleNumber":"17-041","shiftName":"早班","shiftNameDate":"2017-11-11T00:00:00.000+0800","_dcbustype":"salereturn"}


{"uuid":"ba98867d-f05c-4b3e-9b2c-81cd1a46ee41","version":0,"createInfo":{"time":"2017-11-23T08:53:09.000+0800","operator":{"id":"1890201","fullName":"201","qualifiedId":"1890201"}},"lastModifyInfo":{"time":"2017-11-23T08:53:09.000+0800","operator":{"id":"1890201","fullName":"201","qualifiedId":"1890201"}},"number":"171123-002","encryptNumber":"171123-7449","shop":"a03a8283","posNo":"1","shiftName":"早班","shiftNameDate":"2017-11-23T00:00:00.000+0800","amount":2.,"discountAmount":0.,"changeAmount":0.,"qty":1.,"remark":"收银员手动点已付","lines":[{"uuid":"6ea72092-5db0-4973-a3b6-8f464c42758d","line":1,"shopSku":"9ebe9e92-13d0-4b7a-9493-9b841d3b4fe7","shopSkuBarcode":"6944591800691","platformSku":"4E878E6DFFBB49268CCB9B8CE46F5344","skuName":"创越漱口杯","skuMunit":"只","price":2.,"skuSalePrice":3.,"qty":1.,"amount":2.,"costPrice":2.2200,"saleLineFavItems":[{"uuid":"c6fbf7f4-ce5a-4a39-a623-aecf45a4bc02","price":0,"amount":0,"discount":1,"favType":"ChangePriceLineFavItem","favAmount":1.,"causeType":"MatchQfRuleFavCause","causeJson":"{\"tenant\":\"f62ba32a-b432-4786-9cf9-f7805d71dc40\",\"_cause_type_\":\"MatchQfRuleFavCause\",\"activity\":{\"id\":\"f01adece-30ea-4e62-a0c5-23f2d1c64b80\",\"namespace\":\"com.qianfan123.mp::Activity\"}}","activity":"f01adece-30ea-4e62-a0c5-23f2d1c64b80","activityNamespace":"com.qianfan123.mp::Activity","dependOnLines":[]}]}],"payments":[{"uuid":"b6cd982e-fdc5-416e-94d2-c54d61dbeeca","line":3,"paymentMethod":"ALIPAY","paidAmount":0.0100,"payingAmount":0.0100,"state":"PROCESSING","artificialState":"PAID","tranId":"0be234ea-6560-4146-8b09-3b96f1ff5ed9","payChannel":"ONLINE","created":"2017-11-23T08:53:09.000+0800"},{"uuid":"23d60861-a207-4f36-a0b4-f73381fb6f5f","line":4,"paymentMethod":"CASH","paidAmount":1.9900,"payingAmount":0.,"payChannel":"CASH","created":"2017-11-23T08:53:09.000+0800"}],"favDetails":[],"favItems":[],"_dcbustype":"sale"}