MIME-Version: 1.0
References: <b3a73eca-ec3b-4b64-b6c2-82418bb16701@manitou-mail.org>
 <b7d8c88b-d175-44ca-a323-ec2f5c3cd37d@joeconway.com> <8620df11-96e4-4ca3-8f3c-33a479260961@joeconway.com>
 <4162f7f7-6fd6-4720-98e2-89f80e3de2ed@joeconway.com> <CAEZATCWh29787xf=4NgkoixeqRHrqi0Qd33Z6_-F8t2dZ0yLCQ@mail.gmail.com>
 <153b71b8-b6fb-4930-aaff-a7334cb3f6d6@joeconway.com> <CC6F036C-08BB-4A7B-ACBF-F99393E8A492@yandex-team.ru>
 <8ef5f5d6-38db-4624-8eef-2d96c9eba9d4@joeconway.com> <CACJufxGMSbpXCCoCb_u5EULo5y2sfMgASkRk2pmw9WRu2qOCXw@mail.gmail.com>
 <CACJufxGjJXKfPZoyFqgvYTBYW3GmB5WA+0H0zXkfYjrc7au7qg@mail.gmail.com>
 <CACJufxET6LJWbywU_odC_k3vYwEKhNQjiWTDk=37ecQG5mFk5Q@mail.gmail.com> <CACJufxFvOQWcGpiaFkOxMGPc=g85h=-LdyB_2wXBSVQCO9RAHQ@mail.gmail.com>
In-Reply-To: <CACJufxFvOQWcGpiaFkOxMGPc=g85h=-LdyB_2wXBSVQCO9RAHQ@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Fri, 13 Sep 2024 22:42:00 +0800
Message-ID: <CACJufxH=3PreKDHuoskn0GQmxUcw-n0k3ogN1CD4CvFbLBy-VA@mail.gmail.com>
Subject: Re: Emitting JSON to file using COPY TO
To: Joe Conway <mail@joeconway.com>
Cc: "Andrey M. Borodin" <x4mmm@yandex-team.ru>, Dean Rasheed <dean.a.rasheed@gmail.com>, 
	Daniel Verite <daniel@manitou-mail.org>, Andrew Dunstan <andrew@dunslane.net>, 
	Davin Shearer <davin@apache.org>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000a018430622013d53"
Archived-At: <https://www.postgresql.org/message-id/CACJufxH%3D3PreKDHuoskn0GQmxUcw-n0k3ogN1CD4CvFbLBy-VA%40mail.gmail.com>
Precedence: bulk

--000000000000a018430622013d53
Content-Type: text/plain; charset="UTF-8"

Hi.

in ExecutePlan
we have:

    for (;;)
    {
        ResetPerTupleExprContext(estate);
        slot = ExecProcNode(planstate);
        if (!TupIsNull(slot))
        {
            if((slot != NULL) && (slot->tts_tupleDescriptor != NULL)
            && (slot->tts_tupleDescriptor->natts > 0)
            && (slot->tts_tupleDescriptor->attrs->attname.data[0] == '\0'))
            elog(INFO, "%s:%d %s this slot first attribute attname is
null", __FILE_NAME__, __LINE__, __func__);
        }
        if (TupIsNull(slot))
            break;
        if (sendTuples)
        {
            if (!dest->receiveSlot(slot, dest))
                break;
        }


dest->receiveSlot(slot, dest) is responsible for sending values to destination,
for COPY TO it will call copy_dest_receive, CopyOneRowTo.

For the copy to format json, we need to make sure
in "dest->receiveSlot(slot, dest))", the slot->tts_tupleDescriptor has
proper information.
because we *use* slot->tts_tupleDescriptor->attrs->attname as the json key.

For example, if (slot->tts_tupleDescriptor->attrs->attname.data[0] == '\0')
then output json may look like:  {"":12}
which is not what we want.


in ExecutePlan i use
elog(INFO, "%s:%d %s this slot first attribute attname is null",
__FILE_NAME__, __LINE__, __func__);
to find sql queries that attribute name is not good.

based on that, i found out many COPY TO (FORMAT JSON) queries will either
error out or the output json key be empty string
if in CopyOneRowTo we didn't copy the cstate->queryDesc->tupDesc
to the slot->tts_tupleDescriptor


You can test it yourself.
first `git am v12-0001-introduce-json-format-for-COPY-TO.patch`
after that, comment out the memcpy call in CopyOneRowTo, just like the
following:
        if(!cstate->rel)
        {
            // memcpy(TupleDescAttr(slot->tts_tupleDescriptor, 0),
            //     TupleDescAttr(cstate->queryDesc->tupDesc, 0),
            //     cstate->queryDesc->tupDesc->natts *
sizeof(FormData_pg_attribute));

build and test with the attached script.
you will see COPY TO FORMAT JSON, lots of cases where the json key
becomes an empty string.


I think this thread related issues has been resolved.

--000000000000a018430622013d53
Content-Type: application/sql; name="scratch31.sql"
Content-Disposition: attachment; filename="scratch31.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_m10t5pk20>
X-Attachment-Id: f_m10t5pk20

c2NyZWF0ZSB0eXBlIGNvbXBvc3R5cGUgYXMgKHggaW50LCB5IHZhcmNoYXIpOwoKY3JlYXRlIG9y
IHJlcGxhY2UgZnVuY3Rpb24gY29tcG9zKCkgcmV0dXJucyBjb21wb3N0eXBlIGFzICQkCmJlZ2lu
CiAgcmV0dXJuICgxLCAnaGVsbG8nKTsKZW5kOwokJCBsYW5ndWFnZSBwbHBnc3FsOwoKY3JlYXRl
IG9yIHJlcGxhY2UgZnVuY3Rpb24gY29tcG9zMSgpIHJldHVybnMgY29tcG9zdHlwZSBhcyAkJApi
ZWdpbgogIHJldHVybiAoMSwgJ2hlbGxvJzo6dmFyY2hhcik7CmVuZDsKJCQgbGFuZ3VhZ2UgcGxw
Z3NxbDsKCmNyZWF0ZSBvciByZXBsYWNlIGZ1bmN0aW9uIGYyKCkgcmV0dXJucyBzZXRvZiBpbnQg
YXMgJCQKYmVnaW4KICByZXR1cm4gcXVlcnkgc2VsZWN0IDEgdW5pb24gYWxsIHNlbGVjdCAyOwpl
bmQ7CiQkIGxhbmd1YWdlIHBscGdzcWw7CgpjcmVhdGUgb3IgcmVwbGFjZSBmdW5jdGlvbiBmMygp
IHJldHVybnMgc2V0b2YgaW50IGFzICQkCmJlZ2luCiAgcmV0dXJuIHF1ZXJ5IHNlbGVjdCAxIHVu
aW9uIHNlbGVjdCAyOwplbmQ7CiQkIGxhbmd1YWdlIHBscGdzcWw7CgpjcmVhdGUgb3IgcmVwbGFj
ZSBmdW5jdGlvbiBmNChpbiBpIGludCwgb3V0IGogaW50KSByZXR1cm5zIHNldG9mIGludCBhcyAk
JApiZWdpbgogIGogOj0gaSsxOwogIHJldHVybiBuZXh0OwogIGogOj0gaSsyOwogIHJldHVybiBu
ZXh0OwogIHJldHVybjsKZW5kJCQgbGFuZ3VhZ2UgcGxwZ3NxbDsKCmNyZWF0ZSBvciByZXBsYWNl
IGZ1bmN0aW9uIHJldHVybnNyZWNvcmQoaW50KSByZXR1cm5zIHJlY29yZCBsYW5ndWFnZSBwbHBn
c3FsIGFzCiQkCmJlZ2luCnJldHVybiByb3coJDEsJDErMSk7CmVuZCAkJDsKCmNyZWF0ZSBvciBy
ZXBsYWNlIGZ1bmN0aW9uIG15X2YzKGEgaW50ZWdlcikKcmV0dXJucyB0YWJsZSh4eHl5IGludCkg
bGFuZ3VhZ2UgcGxwZ3NxbCBhcwokZnVuY3Rpb24kCmJlZ2luCiAgICByZXR1cm4gcXVlcnkgc2Vs
ZWN0IGE7CmVuZDsKJGZ1bmN0aW9uJDsKCgoKCmNvcHkoU0VMRUNUICogRlJPTSBnZW5lcmF0ZV9z
ZXJpZXMoJys0NTY3ODkwMTIzNDU2Nzg5Jzo6aW50OCwgJys0NTY3ODkwMTIzNDU2Nzk5Jzo6aW50
OCkpIHRvIHN0ZG91dCAoZm9ybWF0IGpzb24pOwpjb3B5KHNlbGVjdCAqIGZyb20gbXlfZjMoMSkp
IHRvIHN0ZG91dCAoZm9ybWF0IGpzb24pOwpjb3B5KHNlbGVjdCBteV9mMygxKSkgdG8gc3Rkb3V0
IChmb3JtYXQganNvbik7CmNvcHkoc2VsZWN0ICogZnJvbSBmMigpKSB0byBzdGRvdXQgKGZvcm1h
dCBqc29uKTsKY29weShzZWxlY3QgKiBmcm9tIGYzKCkpIHRvIHN0ZG91dCAoZm9ybWF0IGpzb24p
Owpjb3B5KHNlbGVjdCAqIGZyb20gZjQoMTEpKSB0byBzdGRvdXQgKGZvcm1hdCBqc29uKTsKY29w
eShzZWxlY3QgY29tcG9zMSgpKSB0byBzdGRvdXQgKGZvcm1hdCBqc29uKTsKY29weShzZWxlY3Qg
KiBmcm9tIGNvbXBvczEoKSkgdG8gc3Rkb3V0IChmb3JtYXQganNvbik7CmNvcHkoc2VsZWN0ICog
ZnJvbSByZXR1cm5zcmVjb3JkKDQyKSBhcyByKHh4eDEgaW50LCB6MSBpbnQpKSB0byBzdGRvdXQg
KGZvcm1hdCBqc29uKTs=
--000000000000a018430622013d53--