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 1jJc2L-0002eT-7v for pgsql-bugs@arkaria.postgresql.org; Wed, 01 Apr 2020 12:00:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jJc2K-0001u8-5Q for pgsql-bugs@arkaria.postgresql.org; Wed, 01 Apr 2020 12:00:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from <9erthalion6@gmail.com>) id 1jJc2J-0001u1-VQ for pgsql-bugs@lists.postgresql.org; Wed, 01 Apr 2020 11:59:59 +0000 Received: from mail-wr1-x441.google.com ([2a00:1450:4864:20::441]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from <9erthalion6@gmail.com>) id 1jJc2H-00074d-Q0 for pgsql-bugs@lists.postgresql.org; Wed, 01 Apr 2020 11:59:59 +0000 Received: by mail-wr1-x441.google.com with SMTP id 31so30188257wrs.3 for ; Wed, 01 Apr 2020 04:59:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to; bh=fRja7KLIGIPkDBrwktv1zwlKigU+vZOj92wixjgcP64=; b=CiFq/zR5CDqjutQ3QiaZ1vpIcU6iHVmgdMiJnJa3NmebBVymy8WUnvaK1eAt1LWT0m bnDJbYsfSwATXQx2vm/radzC7bDrGg9wRd8C19DdeAMd7ZmbaWh5TB6dj6aVVThKne6V jEQfRaGwvkmZfqGvbw5KKveNb9NmfAWR/BOO3uWQxXAfl6FERdzCrCIQryPeVsA/RGvg rMjlzZOeQCejrcSOClQnLgb/RzLb9WTHYWF7SzPdEDubDO+cvnm3hyTVARNEqJSoAJ2a kbDKfidcUSX7DNPtjv8D112CnFef/PP8EmpvhnOlDQ5+qt0ok6XXyThVvRjbewtFza9m JOqQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to; bh=fRja7KLIGIPkDBrwktv1zwlKigU+vZOj92wixjgcP64=; b=YwYzSKGcKXUYBmQfLbJTnVgP4WP9GX1jcLjUzut4Qr/b9EUV1v4PS5NmmVXFqC7UFE RMeKqlRzwg3m9Y8blLojKptLDhD6J+m3DXHBTjJl0lv2wQqkCGSctRsomnSPFQ2Lyyap 8Opo3BZZTH/k5jy7BgeXc2pYaF41HwQ0UTslA+IHAnzSt3OSbmGeArQe0HJ3QvU6qMyX qn89Fx9tMgS8EQ4PzwRcwY77hEXr+jYmCHNOSpbTaAM8SJWFnJrvVZfYDT9nbQWCAOie ys5B2xMJpQHPcP336AYZv/qFYdEc1fAKiDbCUFPlIsocwDPZEWOUY9wCe+x8Pmt3Da61 AS2A== X-Gm-Message-State: ANhLgQ2AX2Fulv1cRJKBXBPLAg4iJfUDdN03JmfJFVtxk5nta3atcqhs TfB08ObvZt1LyoFRS1UKeN4= X-Google-Smtp-Source: ADFU+vt9QXq+PPhLQzw9+bRWaWwr47kSpx3fIeTugnYa+lYFn+7qKZb8yMjd4R4znckNrnqvy/3V4A== X-Received: by 2002:adf:e90b:: with SMTP id f11mr13411036wrm.65.1585742397023; Wed, 01 Apr 2020 04:59:57 -0700 (PDT) Received: from localhost (dslb-094-222-004-201.094.222.pools.vodafone-ip.de. [94.222.4.201]) by smtp.gmail.com with ESMTPSA id v26sm2719239wra.7.2020.04.01.04.59.55 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 01 Apr 2020 04:59:56 -0700 (PDT) Date: Wed, 1 Apr 2020 14:01:17 +0200 From: Dmitry Dolgov <9erthalion6@gmail.com> To: Michael Paquier Cc: exclusion@gmail.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16325: Assert failure on partitioning by int for a text value with a collation Message-ID: <20200401120117.qkmjxi3xrumbnd3s@localhost> References: <16325-809194cf742313ab@postgresql.org> <20200328125303.4me6vetvzgebobw5@localhost> <20200401070842.GD142683@paquier.xyz> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="bzmyfww5xtna7xsu" Content-Disposition: inline In-Reply-To: <20200401070842.GD142683@paquier.xyz> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --bzmyfww5xtna7xsu Content-Type: text/plain; charset=us-ascii Content-Disposition: inline > On Wed, Apr 01, 2020 at 04:08:42PM +0900, Michael Paquier wrote: > > On Sat, Mar 28, 2020 at 01:53:03PM +0100, Dmitry Dolgov wrote: > > Thanks for reporting! Looks like transformPartitionBoundValue needs to > > check that the source collumn is collatable, then we get more expected > > result: > > > > =# create table part_coll partition of parted for values in ('1' collate "POSIX"); > > ERROR: 42804: collations are not supported by type integer > > We have here a side effect of 7c079d74, that has given the possibility > to pass down general expressions for partition bounds. Yep. That's why I've placed a test for this fix close to those introduced in this commit. > > + if (type_is_collatable(colType)) > > + { > > + if (!OidIsValid(exprCollOid)) > > + ereport(ERROR, > > + (errcode(ERRCODE_INDETERMINATE_COLLATION), > > + errmsg("could not determine which collation to use for partition expression"), > > + errhint("Use the COLLATE clause to set the collation explicitly."))); > > + } > > + else > > + { > > + if (OidIsValid(exprCollOid)) > > + ereport(ERROR, > > + (errcode(ERRCODE_DATATYPE_MISMATCH), > > + errmsg("collations are not supported by type %s", > > + format_type_be(colType)))); > > + } > > + > > It seems to me that the first error cannot happen, and should not > happen. When the expression is transformed, there is a lookup at the > collation used via transformCollateClause() -> LookupCollation() -> > get_collation_oid() which would never return an invalid OID if the > collation exists, throwing an error instead. The reason why a similar > check exists in tablecmds.c is that there can be a collation override, > no? Good point, I believe you're right. This means that the fix can be reduced as in attachments. --bzmyfww5xtna7xsu Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="part_value_collations_v2.patch" diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index c1911411d0..403f8148ae 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -4069,6 +4069,16 @@ transformPartitionBoundValue(ParseState *pstate, Node *val, { Oid exprCollOid = exprCollation(value); + /* + * No need to check if exprCollOid is invalid, since if it would be, + * then transformCollateClause will throw an error earlier + */ + if (!type_is_collatable(colType)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("collations are not supported by type %s", + format_type_be(colType)))); + if (OidIsValid(exprCollOid) && exprCollOid != DEFAULT_COLLATION_OID && exprCollOid != partCollation) diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index c5e95edbed..abd1bfe2b9 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -594,6 +594,8 @@ CREATE TABLE list_parted ( CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1'); CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2); CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1)); +CREATE TABLE part_p4 PARTITION OF list_parted FOR VALUES IN ('1' collate "POSIX"); +ERROR: collations are not supported by type integer CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); \d+ list_parted Partitioned table "public.list_parted" diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 00ef81a685..3fe77a7711 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -522,6 +522,7 @@ CREATE TABLE list_parted ( CREATE TABLE part_p1 PARTITION OF list_parted FOR VALUES IN ('1'); CREATE TABLE part_p2 PARTITION OF list_parted FOR VALUES IN (2); CREATE TABLE part_p3 PARTITION OF list_parted FOR VALUES IN ((2+1)); +CREATE TABLE part_p4 PARTITION OF list_parted FOR VALUES IN ('1' collate "POSIX"); CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); \d+ list_parted --bzmyfww5xtna7xsu--