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

Поиск
Список
Период
Сортировка
От Rahila Syed
Тема Re: Assignment of valid collation for SET operations on queries with UNKNOWN types.
Дата
Msg-id CAH2L28vRHT97hSwhn5krS9zfuqhUyTBsnLcTFdvMTQZx752XDA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Assignment of valid collation for SET operations on queries with UNKNOWN types.  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Assignment of valid collation for SET operations on queries with UNKNOWN types.  (Michael Paquier <michael.paquier@gmail.com>)
Список pgsql-hackers
Дерево обсуждения
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 columns 
with literal constants as TEXT for view creation.

Following views can be created with literal columns resolved as TEXT.

postgres=# create view v as select 'abc' a;
CREATE VIEW
postgres=# create view v1 as select 'def' a;
CREATE VIEW
postgres=# \d+ v1;
                            View "public.v1"
 Column | Type | Collation | Nullable | Default | Storage  | Description
--------+------+-----------+----------+---------+----------+-------------
 a      | text |           |          |         | extended |
View definition:
 SELECT 'def'::text AS a;

This allows following queries to run successfully which wasn't the case earlier.

postgres=# select a from v UNION select a from v1;
  a 
-----
 abc
 def
(2 rows)

AND

postgres=# select * from v order by 1;
  a 
-----
 abc
(1 row)

Kindly give your opinion.

Thank you,
Rahila Syed.




On Thu, Nov 17, 2016 at 8:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rahila Syed <rahilasyed90@gmail.com> 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:  Proceeding with relation creation anyway.
> CREATE VIEW

We really ought to make that a hard error.  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.

> Attached WIP patch does that. Kindly let me know your opinion.

This is a seriously ugly kluge that's attacking the symptom not the
problem.  Or really, a symptom not the problem.  There are lots of
other symptoms, for instance

regression=# select * from v order by 1;
ERROR:  failed to find conversion function from unknown to text

                        regards, tom lane

Вложения

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: pgcrypto compilation error due to stack-allocated EVP_CIPHER_CTX
Следующее
От: Fabien COELHO
Дата:
Сообщение: Re: PSQL commands: \quit_if, \quit_unless