Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p77cf-0003Nc-Cd for pgsql-hackers@arkaria.postgresql.org; Mon, 19 Dec 2022 04:19:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p77cd-0000DL-1Q for pgsql-hackers@arkaria.postgresql.org; Mon, 19 Dec 2022 04:19:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p77cc-0000D5-Ih for pgsql-hackers@lists.postgresql.org; Mon, 19 Dec 2022 04:19:26 +0000 Received: from mail-yb1-xb2b.google.com ([2607:f8b0:4864:20::b2b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p77cY-0001w1-Mt for pgsql-hackers@postgresql.org; Mon, 19 Dec 2022 04:19:25 +0000 Received: by mail-yb1-xb2b.google.com with SMTP id c124so3918173ybb.13 for ; Sun, 18 Dec 2022 20:19:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=Sy3E0nqVjEtZ4iFs2DWSvfWawcfaTzrDtbzc1YhE/Pw=; b=ctYGztAsJ2NM29nt82+dG0IqYuj9CXCnoMw9vRdldxr+WiXvrb7A0O7XK1hupQGCiW 9WyQCe4/0Lj7wy+l3/QPQDCeg4nFEfOjzBMRjrSq279rBYLJfPTKn3bCUrGrQNdjUvYc MpQnL8Njob3+ldMFcEf2UfyXfbiCxQE9IpYP1KKES9H2qTHMw523C8khve4xlLAbcWWv jaaPGvK2+WXEEsY00h1bvfdnflGbKCx7Ua4ddodRFNIzybgk/M1I1FwfLaozMx6Hjic/ XJd8khzNAjhl7kt3nNNd8Nja5WRUl73NxhLOl3MAGhuWiZqS0X4LnSKbXNZFlNqkzI1z qvsQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Sy3E0nqVjEtZ4iFs2DWSvfWawcfaTzrDtbzc1YhE/Pw=; b=JiPt4o0Du+EgFqvVvz4drH4SJ+XkcssFhwgek7dQYhcw1fiZC6CXIxuY1gKVV2WeLU sLRPyhpQC76TYuz4zxxSq7qRzpt1vaWBNgFXIcYSG6fbhglZ2KKfmE0rNOkDeW9Tro5W Lq3haWnpQyfVaaKS0jLDDW0h2Q73fNCTvenWx4lc5T5WnyIP8fMSSwky1fH/AX9fFBIM IcjdCM7PljG6hkSmLN2ocggWTSt+f1Z+w+IT/Xmfb93LanS8IECFv99ADPcrkTT+7GAM 45kYOOri4jlMV4B61kr/7MVXQjNEadl2brTu2zIsc3o9FKZQMMGweLs4kOObza4C/IwF eNHA== X-Gm-Message-State: ANoB5pmm8Hjq2zI0QuN5yo2VImhiPNG5OJzurujX/q5hvobT10g8NceA 01DUiQt1ceOEjqMNJFYdKgfkqE74Op4uIqdWTbtI3pjNTV7AfQ== X-Google-Smtp-Source: AA0mqf741QkmHUh+6ooVhwe6SIGpmqFmjOSpjY/DZxzeB8/ectYE1N47ZCVsKJDIH7pUBwbh585+MF1o3EC3YJIsQDI= X-Received: by 2002:a25:d3ce:0:b0:721:bb95:8899 with SMTP id e197-20020a25d3ce000000b00721bb958899mr3861756ybf.598.1671423561174; Sun, 18 Dec 2022 20:19:21 -0800 (PST) MIME-Version: 1.0 From: Andrey Borodin Date: Sun, 18 Dec 2022 20:19:10 -0800 Message-ID: Subject: GROUP BY ALL To: pgsql-hackers Content-Type: multipart/mixed; boundary="000000000000a24ee005f0269f72" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a24ee005f0269f72 Content-Type: text/plain; charset="UTF-8" Hi hackers! I saw a thread in a social network[0] about GROUP BY ALL. The idea seems useful. I always was writing something like select datname, usename, count(*) from pg_stat_activity group by 1,2; and then rewriting to select datname, usename, query, count(*) from pg_stat_activity group by 1,2; and then "aaahhhh, add a number at the end". With the proposed feature I can write just select datname, usename, count(*) from pg_stat_activity group by all; PFA very dummy implementation just for a discussion. I think we can add all non-aggregating targets. What do you think? Best regards, Andrey Borodin. [0] https://www.linkedin.com/posts/mosha_duckdb-firebolt-snowflake-activity-7009615821006131200-VQ0o/ --000000000000a24ee005f0269f72 Content-Type: application/octet-stream; name="v1-0001-Implement-GROUP-BY-ALL.patch" Content-Disposition: attachment; filename="v1-0001-Implement-GROUP-BY-ALL.patch" Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: f_lbua9l290 RnJvbSBlNWY5Y2E4OWQ1Nzc5MjYxNTVjYzk0ZTBlYTViNWJiZmVmYmQzMzFkIE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBBbmRyZXkgQm9yb2RpbiA8eGZvcm1tbUBhbWF6b24uY29tPgpE YXRlOiBTdW4sIDE4IERlYyAyMDIyIDE5OjUyOjQ4IC0wODAwClN1YmplY3Q6IFtQQVRDSCB2MV0g SW1wbGVtZW50IEdST1VQIEJZIEFMTAoKLS0tCiBzcmMvYmFja2VuZC9wYXJzZXIvYW5hbHl6ZS5j ICAgICAgICAgfCAgMSArCiBzcmMvYmFja2VuZC9wYXJzZXIvZ3JhbS55ICAgICAgICAgICAgfCAx NCArKysrKysrKysrKysrKwogc3JjL2JhY2tlbmQvcGFyc2VyL3BhcnNlX2FnZy5jICAgICAgIHwg MjMgKysrKysrKysrKysrKysrKysrKysrKy0KIHNyYy9iYWNrZW5kL3V0aWxzL2FkdC9ydWxldXRp bHMuYyAgICB8ICAzICsrKwogc3JjL2JhY2tlbmQvdXRpbHMvbWlzYy9xdWVyeWp1bWJsZS5jIHwg IDEgKwogc3JjL2luY2x1ZGUvbm9kZXMvcGFyc2Vub2Rlcy5oICAgICAgIHwgIDIgKysKIDYgZmls ZXMgY2hhbmdlZCwgNDMgaW5zZXJ0aW9ucygrKSwgMSBkZWxldGlvbigtKQoKZGlmZiAtLWdpdCBh L3NyYy9iYWNrZW5kL3BhcnNlci9hbmFseXplLmMgYi9zcmMvYmFja2VuZC9wYXJzZXIvYW5hbHl6 ZS5jCmluZGV4IDY2ODhjMmE4NjUuLjcxZDEyZWFkNzkgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5k L3BhcnNlci9hbmFseXplLmMKKysrIGIvc3JjL2JhY2tlbmQvcGFyc2VyL2FuYWx5emUuYwpAQCAt MTM0Nyw2ICsxMzQ3LDcgQEAgdHJhbnNmb3JtU2VsZWN0U3RtdChQYXJzZVN0YXRlICpwc3RhdGUs IFNlbGVjdFN0bXQgKnN0bXQpCiAJCQkJCQkJCQkJCUVYUFJfS0lORF9HUk9VUF9CWSwKIAkJCQkJ CQkJCQkJZmFsc2UgLyogYWxsb3cgU1FMOTIgcnVsZXMgKi8gKTsKIAlxcnktPmdyb3VwRGlzdGlu Y3QgPSBzdG10LT5ncm91cERpc3RpbmN0OworCXFyeS0+Z3JvdXBBbGwgPSBzdG10LT5ncm91cEFs bDsKIAogCWlmIChzdG10LT5kaXN0aW5jdENsYXVzZSA9PSBOSUwpCiAJewpkaWZmIC0tZ2l0IGEv c3JjL2JhY2tlbmQvcGFyc2VyL2dyYW0ueSBiL3NyYy9iYWNrZW5kL3BhcnNlci9ncmFtLnkKaW5k ZXggYjFhZTVmODM0Yy4uODRmOGE0MTQ2YSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvcGFyc2Vy L2dyYW0ueQorKysgYi9zcmMvYmFja2VuZC9wYXJzZXIvZ3JhbS55CkBAIC0xMzUsNiArMTM1LDcg QEAgdHlwZWRlZiBzdHJ1Y3QgU2VsZWN0TGltaXQKIHR5cGVkZWYgc3RydWN0IEdyb3VwQ2xhdXNl CiB7CiAJYm9vbAkJZGlzdGluY3Q7CisJYm9vbAkJYWxsOwogCUxpc3QJICAgKmxpc3Q7CiB9IEdy b3VwQ2xhdXNlOwogCkBAIC0xMjU4MCw2ICsxMjU4MSw3IEBAIHNpbXBsZV9zZWxlY3Q6CiAJCQkJ CW4tPndoZXJlQ2xhdXNlID0gJDY7CiAJCQkJCW4tPmdyb3VwQ2xhdXNlID0gKCQ3KS0+bGlzdDsK IAkJCQkJbi0+Z3JvdXBEaXN0aW5jdCA9ICgkNyktPmRpc3RpbmN0OworCQkJCQluLT5ncm91cEFs bCA9ICgkNyktPmFsbDsKIAkJCQkJbi0+aGF2aW5nQ2xhdXNlID0gJDg7CiAJCQkJCW4tPndpbmRv d0NsYXVzZSA9ICQ5OwogCQkJCQkkJCA9IChOb2RlICopIG47CkBAIC0xMjU5Nyw2ICsxMjU5OSw3 IEBAIHNpbXBsZV9zZWxlY3Q6CiAJCQkJCW4tPndoZXJlQ2xhdXNlID0gJDY7CiAJCQkJCW4tPmdy b3VwQ2xhdXNlID0gKCQ3KS0+bGlzdDsKIAkJCQkJbi0+Z3JvdXBEaXN0aW5jdCA9ICgkNyktPmRp c3RpbmN0OworCQkJCQluLT5ncm91cEFsbCA9ICgkNyktPmFsbDsKIAkJCQkJbi0+aGF2aW5nQ2xh dXNlID0gJDg7CiAJCQkJCW4tPndpbmRvd0NsYXVzZSA9ICQ5OwogCQkJCQkkJCA9IChOb2RlICop IG47CkBAIC0xMzA3NCwxNCArMTMwNzcsMjUgQEAgZ3JvdXBfY2xhdXNlOgogCQkJCQlHcm91cENs YXVzZSAqbiA9IChHcm91cENsYXVzZSAqKSBwYWxsb2Moc2l6ZW9mKEdyb3VwQ2xhdXNlKSk7CiAK IAkJCQkJbi0+ZGlzdGluY3QgPSAkMyA9PSBTRVRfUVVBTlRJRklFUl9ESVNUSU5DVDsKKwkJCQkJ bi0+YWxsID0gZmFsc2U7CiAJCQkJCW4tPmxpc3QgPSAkNDsKIAkJCQkJJCQgPSBuOwogCQkJCX0K KwkJCXwgR1JPVVBfUCBCWSBBTEwKKwkJCQl7CisJCQkJCUdyb3VwQ2xhdXNlICpuID0gKEdyb3Vw Q2xhdXNlICopIHBhbGxvYyhzaXplb2YoR3JvdXBDbGF1c2UpKTsKKworCQkJCQluLT5hbGwgPSB0 cnVlOworCQkJCQluLT5kaXN0aW5jdCA9IGZhbHNlOworCQkJCQluLT5saXN0ID0gTklMOworCQkJ CQkkJCA9IG47CisJCQkJfQogCQkJfCAvKkVNUFRZKi8KIAkJCQl7CiAJCQkJCUdyb3VwQ2xhdXNl ICpuID0gKEdyb3VwQ2xhdXNlICopIHBhbGxvYyhzaXplb2YoR3JvdXBDbGF1c2UpKTsKIAogCQkJ CQluLT5kaXN0aW5jdCA9IGZhbHNlOworCQkJCQluLT5hbGwgPSBmYWxzZTsKIAkJCQkJbi0+bGlz dCA9IE5JTDsKIAkJCQkJJCQgPSBuOwogCQkJCX0KZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3Bh cnNlci9wYXJzZV9hZ2cuYyBiL3NyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9hZ2cuYwppbmRleCAz ZWY5ZThlZTVlLi44ODI2ODI5ZGJjIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9wYXJzZXIvcGFy c2VfYWdnLmMKKysrIGIvc3JjL2JhY2tlbmQvcGFyc2VyL3BhcnNlX2FnZy5jCkBAIC0yMCw2ICsy MCw3IEBACiAjaW5jbHVkZSAibm9kZXMvbWFrZWZ1bmNzLmgiCiAjaW5jbHVkZSAibm9kZXMvbm9k ZUZ1bmNzLmgiCiAjaW5jbHVkZSAib3B0aW1pemVyL29wdGltaXplci5oIgorI2luY2x1ZGUgInBh cnNlci9hbmFseXplLmgiCiAjaW5jbHVkZSAicGFyc2VyL3BhcnNlX2FnZy5oIgogI2luY2x1ZGUg InBhcnNlci9wYXJzZV9jbGF1c2UuaCIKICNpbmNsdWRlICJwYXJzZXIvcGFyc2VfY29lcmNlLmgi CkBAIC0xMDcyLDcgKzEwNzMsMjcgQEAgcGFyc2VDaGVja0FnZ3JlZ2F0ZXMoUGFyc2VTdGF0ZSAq cHN0YXRlLCBRdWVyeSAqcXJ5KQogCU5vZGUJICAgKmNsYXVzZTsKIAogCS8qIFRoaXMgc2hvdWxk IG9ubHkgYmUgY2FsbGVkIGlmIHdlIGZvdW5kIGFnZ3JlZ2F0ZXMgb3IgZ3JvdXBpbmcgKi8KLQlB c3NlcnQocHN0YXRlLT5wX2hhc0FnZ3MgfHwgcXJ5LT5ncm91cENsYXVzZSB8fCBxcnktPmhhdmlu Z1F1YWwgfHwgcXJ5LT5ncm91cGluZ1NldHMpOworCUFzc2VydChwc3RhdGUtPnBfaGFzQWdncyB8 fCBxcnktPmdyb3VwQ2xhdXNlIHx8IHFyeS0+aGF2aW5nUXVhbCB8fCBxcnktPmdyb3VwaW5nU2V0 cyB8fCBxcnktPmdyb3VwQWxsKTsKKworCUFzc2VydCgoIXFyeS0+Z3JvdXBBbGwpIHx8IChxcnkt Pmdyb3VwQ2xhdXNlID09IE5VTEwpKTsKKworCWlmIChxcnktPmdyb3VwQWxsKQorCXsKKwkJSW5k ZXggaWR4ID0gMTsKKwkJSW5kZXggc2dlX2lkeCA9IDE7CisJCWZvcmVhY2gobCwgcXJ5LT50YXJn ZXRMaXN0KQorCQl7CisJCQlUYXJnZXRFbnRyeSAqdGxlID0gbGZpcnN0KGwpOworCQkJaWYgKElz QSh0bGUtPmV4cHIsIFZhcikpCisJCQl7CisJCQkJT2lkCXJlc3R5cGUgPSBleHByVHlwZSgoTm9k ZSAqKSB0bGUtPmV4cHIpOworCQkJCVNvcnRHcm91cENsYXVzZSAqc2djID0gbWFrZVNvcnRHcm91 cENsYXVzZUZvclNldE9wKHJlc3R5cGUsIGZhbHNlKTsKKwkJCQlzZ2MtPnRsZVNvcnRHcm91cFJl ZiA9IHNnZV9pZHgrKzsKKwkJCQlxcnktPmdyb3VwQ2xhdXNlID0gbGFwcGVuZChxcnktPmdyb3Vw Q2xhdXNlLCBzZ2MpOworCQkJCXRsZS0+cmVzc29ydGdyb3VwcmVmID0gaWR4Kys7CisJCQl9CisJ CX0KKwl9CiAKIAkvKgogCSAqIElmIHdlIGhhdmUgZ3JvdXBpbmcgc2V0cywgZXhwYW5kIHRoZW0g YW5kIGZpbmQgdGhlIGludGVyc2VjdGlvbiBvZiBhbGwKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5k L3V0aWxzL2FkdC9ydWxldXRpbHMuYyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9ydWxldXRpbHMu YwppbmRleCBhMjBhMWIwNjliLi5kMGQ0NzExYzUzIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC91 dGlscy9hZHQvcnVsZXV0aWxzLmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3J1bGV1dGls cy5jCkBAIC01OTU5LDYgKzU5NTksOSBAQCBnZXRfYmFzaWNfc2VsZWN0X3F1ZXJ5KFF1ZXJ5ICpx dWVyeSwgZGVwYXJzZV9jb250ZXh0ICpjb250ZXh0LAogCQlpZiAocXVlcnktPmdyb3VwRGlzdGlu Y3QpCiAJCQlhcHBlbmRTdHJpbmdJbmZvU3RyaW5nKGJ1ZiwgIkRJU1RJTkNUICIpOwogCisJCWlm IChxdWVyeS0+Z3JvdXBBbGwpCisJCQlhcHBlbmRTdHJpbmdJbmZvU3RyaW5nKGJ1ZiwgIkFMTCAi KTsKKwogCQlzYXZlX2V4cHJraW5kID0gY29udGV4dC0+c3BlY2lhbF9leHBya2luZDsKIAkJY29u dGV4dC0+c3BlY2lhbF9leHBya2luZCA9IEVYUFJfS0lORF9HUk9VUF9CWTsKIApkaWZmIC0tZ2l0 IGEvc3JjL2JhY2tlbmQvdXRpbHMvbWlzYy9xdWVyeWp1bWJsZS5jIGIvc3JjL2JhY2tlbmQvdXRp bHMvbWlzYy9xdWVyeWp1bWJsZS5jCmluZGV4IDBhY2U3NGRlNzguLmZlYWM5YWE4YjIgMTAwNjQ0 Ci0tLSBhL3NyYy9iYWNrZW5kL3V0aWxzL21pc2MvcXVlcnlqdW1ibGUuYworKysgYi9zcmMvYmFj a2VuZC91dGlscy9taXNjL3F1ZXJ5anVtYmxlLmMKQEAgLTI1NCw2ICsyNTQsNyBAQCBKdW1ibGVR dWVyeUludGVybmFsKEp1bWJsZVN0YXRlICpqc3RhdGUsIFF1ZXJ5ICpxdWVyeSkKIAlKdW1ibGVF eHByKGpzdGF0ZSwgKE5vZGUgKikgcXVlcnktPnJldHVybmluZ0xpc3QpOwogCUp1bWJsZUV4cHIo anN0YXRlLCAoTm9kZSAqKSBxdWVyeS0+Z3JvdXBDbGF1c2UpOwogCUFQUF9KVU1CKHF1ZXJ5LT5n cm91cERpc3RpbmN0KTsKKwlBUFBfSlVNQihxdWVyeS0+Z3JvdXBBbGwpOwogCUp1bWJsZUV4cHIo anN0YXRlLCAoTm9kZSAqKSBxdWVyeS0+Z3JvdXBpbmdTZXRzKTsKIAlKdW1ibGVFeHByKGpzdGF0 ZSwgcXVlcnktPmhhdmluZ1F1YWwpOwogCUp1bWJsZUV4cHIoanN0YXRlLCAoTm9kZSAqKSBxdWVy eS0+d2luZG93Q2xhdXNlKTsKZGlmZiAtLWdpdCBhL3NyYy9pbmNsdWRlL25vZGVzL3BhcnNlbm9k ZXMuaCBiL3NyYy9pbmNsdWRlL25vZGVzL3BhcnNlbm9kZXMuaAppbmRleCA2MTEyY2Q4NWM4Li41 YTBiMWE0M2NmIDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS9ub2Rlcy9wYXJzZW5vZGVzLmgKKysr IGIvc3JjL2luY2x1ZGUvbm9kZXMvcGFyc2Vub2Rlcy5oCkBAIC0xNzAsNiArMTcwLDcgQEAgdHlw ZWRlZiBzdHJ1Y3QgUXVlcnkKIAogCUxpc3QJICAgKmdyb3VwQ2xhdXNlOwkvKiBhIGxpc3Qgb2Yg U29ydEdyb3VwQ2xhdXNlJ3MgKi8KIAlib29sCQlncm91cERpc3RpbmN0OwkvKiBpcyB0aGUgZ3Jv dXAgYnkgY2xhdXNlIGRpc3RpbmN0PyAqLworCWJvb2wJCWdyb3VwQWxsOwogCiAJTGlzdAkgICAq Z3JvdXBpbmdTZXRzOwkvKiBhIGxpc3Qgb2YgR3JvdXBpbmdTZXQncyBpZiBwcmVzZW50ICovCiAK QEAgLTE3MzcsNiArMTczOCw3IEBAIHR5cGVkZWYgc3RydWN0IFNlbGVjdFN0bXQKIAlOb2RlCSAg ICp3aGVyZUNsYXVzZTsJLyogV0hFUkUgcXVhbGlmaWNhdGlvbiAqLwogCUxpc3QJICAgKmdyb3Vw Q2xhdXNlOwkvKiBHUk9VUCBCWSBjbGF1c2VzICovCiAJYm9vbAkJZ3JvdXBEaXN0aW5jdDsJLyog SXMgdGhpcyBHUk9VUCBCWSBESVNUSU5DVD8gKi8KKwlib29sCQlncm91cEFsbDsKIAlOb2RlCSAg ICpoYXZpbmdDbGF1c2U7CS8qIEhBVklORyBjb25kaXRpb25hbC1leHByZXNzaW9uICovCiAJTGlz dAkgICAqd2luZG93Q2xhdXNlOwkvKiBXSU5ET1cgd2luZG93X25hbWUgQVMgKC4uLiksIC4uLiAq LwogCi0tIAoyLjM3LjAgKEFwcGxlIEdpdC0xMzYpCgo= --000000000000a24ee005f0269f72--