Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Wed, Jan 25, 2017 at 9:37 AM, Tom Lane wrote: > Pushed, thanks for the reviews! I think this is a nice improvement. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Ashutosh Bapat writes: > On Wed, Jan 25, 2017 at 10:54 AM, Michael Paquier > wrote: >> On Wed, Jan 25, 2017 at 12:46 AM, Tom Lane wrote: >>> Here's an updated patch with doc changes. Aside from that one, >>> I

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Wed, Jan 25, 2017 at 10:54 AM, Michael Paquier wrote: > On Wed, Jan 25, 2017 at 12:46 AM, Tom Lane wrote: >> I wrote: >>> Michael Paquier writes: The table of Pseudo-Types needs to be updated as well with unknown

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Wed, Jan 25, 2017 at 2:28 PM, Ashutosh Bapat wrote: > On Wed, Jan 25, 2017 at 10:54 AM, Michael Paquier > wrote: >> On Wed, Jan 25, 2017 at 12:46 AM, Tom Lane wrote: >>> I wrote: Michael Paquier

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Wed, Jan 25, 2017 at 10:54 AM, Michael Paquier wrote: > On Wed, Jan 25, 2017 at 12:46 AM, Tom Lane wrote: >> I wrote: >>> Michael Paquier writes: The table of Pseudo-Types needs to be updated as well with unknown

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Wed, Jan 25, 2017 at 12:46 AM, Tom Lane wrote: > I wrote: >> Michael Paquier writes: >>> The table of Pseudo-Types needs to be updated as well with unknown in >>> datatype.sgml. > >> Check. > > Here's an updated patch with doc changes. Aside

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

I wrote: > Michael Paquier writes: >> The table of Pseudo-Types needs to be updated as well with unknown in >> datatype.sgml. > Check. Here's an updated patch with doc changes. Aside from that one, I tried to spell "pseudo-type" consistently, and I changed one place

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Ashutosh Bapat writes: > On Mon, Jan 23, 2017 at 9:56 PM, Tom Lane wrote: >> I've grepped the code for references to UNKNOWNOID and TYPTYPE_PSEUDO, >> and I can't find any places where the behavior would change in a way >> that we don't want.

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Michael Paquier writes: > As unknown is a pseudo type, I don't think you need > TYPCATEGORY_UNKNOWN in pg_type.h or even the mention to the unknown > type in catalogs.sgml as that becomes a pseudo-type. I wondered whether to remove TYPCATEGORY_UNKNOWN but thought it

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Mon, Jan 23, 2017 at 9:56 PM, Tom Lane wrote: > I wrote: >> Ashutosh Bapat writes: >>> UNKNOWN is not exactly a pseudo-type. > >> Well, as I said to Michael just now, I think we should turn it into one >> now that we're disallowing it in

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Tue, Jan 24, 2017 at 1:26 AM, Tom Lane wrote: > I wrote: >> Ashutosh Bapat writes: >>> UNKNOWN is not exactly a pseudo-type. > >> Well, as I said to Michael just now, I think we should turn it into one >> now that we're disallowing it in

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

I wrote: > Ashutosh Bapat writes: >> UNKNOWN is not exactly a pseudo-type. > Well, as I said to Michael just now, I think we should turn it into one > now that we're disallowing it in tables, because "cannot be used as a > table column" is more or less the

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Ashutosh Bapat writes: > Following error message might be misleading, > postgres=# create table t1 (a unknown); > ERROR: column "a" has pseudo-type unknown > UNKNOWN is not exactly a pseudo-type. Well, as I said to Michael just now, I think we should turn it

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Michael Paquier writes: > One thing though: even with this patch, it is still possible to define > a domain with unknown as underlying type and have a table grab it: > create domain name as unknown; > create table foo_name (a name); > I think that this case should be

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Mon, Jan 23, 2017 at 4:23 AM, Tom Lane wrote: > I wrote: >> I spent some time fooling with this today and came up with the attached >> patch. I think this is basically the direction we should go in, but >> there are various loose ends: > > Here's an updated patch that's

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Mon, Jan 23, 2017 at 7:53 AM, Tom Lane wrote: >> 2. I didn't do anything about docs, either, though maybe no change >> is needed. One user-visible change from this is that queries should >> never return any "unknown"-type columns to clients anymore. But I >> think that is

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

I wrote: > I spent some time fooling with this today and came up with the attached > patch. I think this is basically the direction we should go in, but > there are various loose ends: Here's an updated patch that's rebased against today's HEAD and addresses most of the loose ends: > 1. I

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Wed, Jan 18, 2017 at 10:55 AM, Michael Paquier wrote: > On Sun, Jan 8, 2017 at 10:55 AM, Tom Lane wrote: >> Ashutosh Bapat writes: >>> On Tue, Jan 3, 2017 at 5:57 PM, Rahila Syed wrote:

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Sun, Jan 8, 2017 at 10:55 AM, Tom Lane wrote: > Ashutosh Bapat writes: >> On Tue, Jan 3, 2017 at 5:57 PM, Rahila Syed wrote: >>> Are you suggesting extending the patch to include coercing from unknown to >>> text

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Ashutosh Bapat writes: > On Tue, Jan 3, 2017 at 5:57 PM, Rahila Syed wrote: >> Are you suggesting extending the patch to include coercing from unknown to >> text for all possible cases where a column of unknown type is being created? > I

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Tue, Jan 3, 2017 at 5:57 PM, Rahila Syed wrote: > Thank you all for inputs. > Kindly help me clarify the scope of the patch. > >>However, I thought the idea was to silently coerce affected columns from >>unknown to text. This doesn't look like the behavior we want: > >

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Thank you all for inputs. Kindly help me clarify the scope of the patch. >However, I thought the idea was to silently coerce affected columns from >unknown to text. This doesn't look like the behavior we want: This patch prevents creation of relation with unknown columns and in addition fixes

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Fri, Dec 30, 2016 at 1:30 PM, Ashutosh Bapat wrote: > On Thu, Dec 29, 2016 at 8:18 PM, Tom Lane wrote: >> Ashutosh Bapat writes: >>> The way this patch has been written, it doesn't allow creating tables >>>

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Thu, Dec 29, 2016 at 8:18 PM, Tom Lane wrote: > Ashutosh Bapat writes: >> The way this patch has been written, it doesn't allow creating tables >> with unknown type columns, which was allowed earlier. > > Yes, that's an intentional change;

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Ashutosh Bapat writes: > The way this patch has been written, it doesn't allow creating tables > with unknown type columns, which was allowed earlier. Yes, that's an intentional change; creating such tables (or views) has never been anything but a foot-gun.

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

The way this patch has been written, it doesn't allow creating tables with unknown type columns, which was allowed earlier. That breaks backward compatibility. Users, who have created such tables will face problems while loading dumps from earlier versions. pg_upgrade might be an issue, but we may

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Wed, Dec 14, 2016 at 7:02 PM, Rahila Syed wrote: >>There is a similar code pattern for materialized views, see >>create_ctas_nodata() where the attribute list is built > create_ctas_nodata() is for creation of materialized views WITH NO DATA. > For other materialized

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Hello, Thank you for comments. >There is a similar code pattern for materialized views, see >create_ctas_nodata() where the attribute list is built create_ctas_nodata() is for creation of materialized views WITH NO DATA. For other materialized views and CREATE TABLE AS, column definitions are

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Wed, Dec 7, 2016 at 4:24 PM, Michael Paquier wrote: > On Tue, Dec 6, 2016 at 10:42 PM, Rahila Syed wrote: >> Thank you for suggestion. Attached is a patch which resolves the columns >> with literal constants as TEXT for view creation. You

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

On Tue, Dec 6, 2016 at 10:42 PM, Rahila Syed wrote: > Hello, > >>And ideally fix things so >>that the type of the view column will be resolved as text, so that you >>don't hit this condition in the first place; but there is no good that >>comes out of allowing a view to be

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Hello, >And ideally fix things so >that the type of the view column will be resolved as text, so that you >don't hit this condition in the first place; but there is no good that >comes out of allowing a view to be created like this Thank you for suggestion. Attached is a patch which resolves the

Re: [HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Rahila Syed writes: > CASE 2: > postgres=# create view v as select 'abc' a; > 2016-11-16 15:28:48 IST WARNING: column "a" has type "unknown" > 2016-11-16 15:28:48 IST DETAIL: Proceeding with relation creation anyway. > WARNING: column "a" has type "unknown" > DETAIL:

[HACKERS] Assignment of valid collation for SET operations on queries with UNKNOWN types.

Following UNION of two queries with constant literals runs successfully. CASE 1: postgres=# SELECT 'abc' UNION SELECT 'bcd' ; ?column? -- abc bcd (2 rows) whereas when these literals are part of a view, the UNION fails. CASE 2: postgres=# create view v as select 'abc' a; 2016-11-16