From 1e7c4bb0049732ece651d993d03bb6772e5d281a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Wed, 25 Jan 2017 09:17:18 -0500 Subject: Change unknown-type literals to type text in SELECT and RETURNING lists. Previously, we left such literals alone if the query or subquery had no properties forcing a type decision to be made (such as an ORDER BY or DISTINCT clause using that output column). This meant that "unknown" could be an exposed output column type, which has never been a great idea because it could result in strange failures later on. For example, an outer query that tried to do any operations on an unknown-type subquery output would generally fail with some weird error like "failed to find conversion function from unknown to text" or "could not determine which collation to use for string comparison". Also, if the case occurred in a CREATE VIEW's query then the view would have an unknown-type column, causing similar failures in queries trying to use the view. To fix, at the tail end of parse analysis of a query, forcibly convert any remaining "unknown" literals in its SELECT or RETURNING list to type text. However, provide a switch to suppress that, and use it in the cases of SELECT inside a set operation or INSERT command. In those cases we already had type resolution rules that make use of context information from outside the subquery proper, and we don't want to change that behavior. Also, change creation of an unknown-type column in a relation from a warning to a hard error. The error should be unreachable now in CREATE VIEW or CREATE MATVIEW, but it's still possible to explicitly say "unknown" in CREATE TABLE or CREATE (composite) TYPE. We want to forbid that because it's nothing but a foot-gun. This change creates a pg_upgrade failure case: a matview that contains an unknown-type column can't be pg_upgraded, because reparsing the matview's defining query will now decide that the column is of type text, which doesn't match the cstring-like storage that the old materialized column would actually have. Add a checking pass to detect that. While at it, we can detect tables or composite types that would fail, essentially for free. Those would fail safely anyway later on, but we might as well fail earlier. This patch is by me, but it owes something to previous investigations by Rahila Syed. Also thanks to Ashutosh Bapat and Michael Paquier for review. Discussion: https://postgr.es/m/CAH2L28uwwbL9HUM-WR=hromW1Cvamkn7O-g8fPY2m=_7muJ0oA@mail.gmail.com --- doc/src/sgml/ref/create_view.sgml | 7 +++--- doc/src/sgml/typeconv.sgml | 50 ++++++++++++++++++++++++++++++++++++++- 2 files changed, 53 insertions(+), 4 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 8641e1925ed..a83d9564e5a 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -251,9 +251,10 @@ CREATE VIEW [ schema . ] view_name AS WITH RECUR CREATE VIEW vista AS SELECT 'Hello World'; - is bad form in two ways: the column name defaults to ?column?, - and the column data type defaults to unknown. If you want a - string literal in a view's result, use something like: + is bad form because the column name defaults to ?column?; + also, the column data type defaults to text, which might not + be what you wanted. Better style for a string literal in a view's + result is something like: CREATE VIEW vista AS SELECT text 'Hello World' AS hello; diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml index c031c01ed35..63d41f03f3f 100644 --- a/doc/src/sgml/typeconv.sgml +++ b/doc/src/sgml/typeconv.sgml @@ -984,7 +984,8 @@ domain's base type for all subsequent steps. If all inputs are of type unknown, resolve as type text (the preferred type of the string category). -Otherwise, unknown inputs are ignored. +Otherwise, unknown inputs are ignored for the purposes +of the remaining rules. @@ -1076,6 +1077,53 @@ but integer can be implicitly cast to real, the union result type is resolved as real. + + + +<literal>SELECT</literal> Output Columns + + + SELECT + determination of result type + + + +The rules given in the preceding sections will result in assignment +of non-unknown data types to all expressions in a SQL query, +except for unspecified-type literals that appear as simple output +columns of a SELECT command. For example, in + + +SELECT 'Hello World'; + + +there is nothing to identify what type the string literal should be +taken as. In this situation PostgreSQL will fall back +to resolving the literal's type as text. + + + +When the SELECT is one arm of a UNION +(or INTERSECT or EXCEPT) construct, or when it +appears within INSERT ... SELECT, this rule is not applied +since rules given in preceding sections take precedence. The type of an +unspecified-type literal can be taken from the other UNION arm +in the first case, or from the destination column in the second case. + + + +RETURNING lists are treated the same as SELECT +output lists for this purpose. + + + + + Prior to PostgreSQL 10, this rule did not exist, and + unspecified-type literals in a SELECT output list were + left as type unknown. That had assorted bad consequences, + so it's been changed. + + -- cgit v1.2.3