pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field.
4+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2022-02-15 2523928b28 Reject change of output-column collation in CREATE OR REPLACE VIEW.
* BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field.
@ 2022-02-15 08:51 PG Bug reporting form <noreply@postgresql.org>
2022-02-15 15:42 ` Re: BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field. Japin Li <japinli@hotmail.com>
2022-02-15 16:12 ` Re: BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2022-02-15 08:51 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: pageorge@unice.fr
The following bug has been logged on the website:
Bug reference: 17404
Logged by: Pierre-Aurélien GEORGES
Email address: pageorge@unice.fr
PostgreSQL version: 12.4
Operating system: Debian 8.3.0-6 (x86_64-pc-linux-gnu)
Description:
3 STEPS TO REPRODUCE :
========================
create TABLE my_table (my_text TEXT);
create VIEW my_view AS SELECT my_text COLLATE "C" FROM my_table;
create OR REPLACE view my_view AS SELECT my_text COLLATE "en_US.utf8" FROM
my_table;
EXPECTED RESULT :
===================
The COLLATION of "my_view"."my_text" should have been changed to
"en_US.utf8".
- OR -
I should have got an error message telling me that it is not possible.
OBSERVED RESULT :
===================
Nothing (it silently fails).
select table_schema, table_name, column_name, collation_name from
information_schema.columns where collation_name is not null and
table_name='my_view';
clearly shows that the COLLATION has not been changed as requested.
What the doc says about CREATE OR REPLACE VIEW :
=============================================
"The new query must generate the same columns that were generated by the
existing view query (that is, the same column names in the same order and
with the *same data types*), but it may add additional columns to the end of
the list.
*The calculations giving rise to the output columns may be completely
different*."
It doesn't say wether the COLLATION is considered being part of the "data
types" or not, i.e. it doesn't say wether it's possible to change the
COLLATION of an existing field or not.
My tests (under Postgres v. 12.4) trying to do so showed that postgres says
nothing and seems to accept the SQL statement, but nothing is changed in the
database (it silently fails). From the user perspective, I consider such a
silent failure the WORST situation possible : I would prefer either 1) to
get an error message saying that it's not possible to change the COLLATION
of an existing field - OR even better - 2) to actually change the COLLATION
of the existing field, as requested.
For this reason, I have also reported a documentation issue.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field.
2022-02-15 08:51 BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field. PG Bug reporting form <noreply@postgresql.org>
@ 2022-02-15 15:42 ` Japin Li <japinli@hotmail.com>
2022-02-15 16:00 ` Re: BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field. Japin Li <japinli@hotmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Japin Li @ 2022-02-15 15:42 UTC (permalink / raw)
To: pageorge@unice.fr; pgsql-bugs@lists.postgresql.org
On Tue, 15 Feb 2022 at 16:51, PG Bug reporting form <noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17404
> Logged by: Pierre-Aurélien GEORGES
> Email address: pageorge@unice.fr
> PostgreSQL version: 12.4
> Operating system: Debian 8.3.0-6 (x86_64-pc-linux-gnu)
> Description:
>
> 3 STEPS TO REPRODUCE :
> ========================
> create TABLE my_table (my_text TEXT);
> create VIEW my_view AS SELECT my_text COLLATE "C" FROM my_table;
> create OR REPLACE view my_view AS SELECT my_text COLLATE "en_US.utf8" FROM
> my_table;
>
> EXPECTED RESULT :
> ===================
> The COLLATION of "my_view"."my_text" should have been changed to
> "en_US.utf8".
> - OR -
> I should have got an error message telling me that it is not possible.
>
> OBSERVED RESULT :
> ===================
> Nothing (it silently fails).
>
> select table_schema, table_name, column_name, collation_name from
> information_schema.columns where collation_name is not null and
> table_name='my_view';
> clearly shows that the COLLATION has not been changed as requested.
>
> What the doc says about CREATE OR REPLACE VIEW :
> =============================================
> "The new query must generate the same columns that were generated by the
> existing view query (that is, the same column names in the same order and
> with the *same data types*), but it may add additional columns to the end of
> the list.
> *The calculations giving rise to the output columns may be completely
> different*."
>
> It doesn't say wether the COLLATION is considered being part of the "data
> types" or not, i.e. it doesn't say wether it's possible to change the
> COLLATION of an existing field or not.
> My tests (under Postgres v. 12.4) trying to do so showed that postgres says
> nothing and seems to accept the SQL statement, but nothing is changed in the
> database (it silently fails). From the user perspective, I consider such a
> silent failure the WORST situation possible : I would prefer either 1) to
> get an error message saying that it's not possible to change the COLLATION
> of an existing field - OR even better - 2) to actually change the COLLATION
> of the existing field, as requested.
>
> For this reason, I have also reported a documentation issue.
I find the checkViewTupleDesc() function do not check the collation[1]. So it
will not update the collation of columns.
[1]
static void
checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc)
{
int i;
if (newdesc->natts < olddesc->natts)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("cannot drop columns from view")));
for (i = 0; i < olddesc->natts; i++)
{
Form_pg_attribute newattr = TupleDescAttr(newdesc, i);
Form_pg_attribute oldattr = TupleDescAttr(olddesc, i);
/* XXX msg not right, but we don't support DROP COL on view anyway */
if (newattr->attisdropped != oldattr->attisdropped)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("cannot drop columns from view")));
if (strcmp(NameStr(newattr->attname), NameStr(oldattr->attname)) != 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("cannot change name of view column \"%s\" to \"%s\"",
NameStr(oldattr->attname),
NameStr(newattr->attname)),
errhint("Use ALTER VIEW ... RENAME COLUMN ... to change name of view column instead.")));
/* XXX would it be safe to allow atttypmod to change? Not sure */
if (newattr->atttypid != oldattr->atttypid ||
newattr->atttypmod != oldattr->atttypmod)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
errmsg("cannot change data type of view column \"%s\" from %s to %s",
NameStr(oldattr->attname),
format_type_with_typemod(oldattr->atttypid,
oldattr->atttypmod),
format_type_with_typemod(newattr->atttypid,
newattr->atttypmod))));
/* We can ignore the remaining attributes of an attribute... */
}
/*
* We ignore the constraint fields. The new view desc can't have any
* constraints, and the only ones that could be on the old view are
* defaults, which we are happy to leave in place.
*/
}
--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field.
2022-02-15 08:51 BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field. PG Bug reporting form <noreply@postgresql.org>
2022-02-15 15:42 ` Re: BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field. Japin Li <japinli@hotmail.com>
@ 2022-02-15 16:00 ` Japin Li <japinli@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Japin Li @ 2022-02-15 16:00 UTC (permalink / raw)
To: Japin Li <japinli@hotmail.com>; +Cc: pageorge@unice.fr; pgsql-bugs@lists.postgresql.org
On Tue, 15 Feb 2022 at 23:42, Japin Li <japinli@hotmail.com> wrote:
> On Tue, 15 Feb 2022 at 16:51, PG Bug reporting form <noreply@postgresql.org> wrote:
>> 3 STEPS TO REPRODUCE :
>> ========================
>> create TABLE my_table (my_text TEXT);
>> create VIEW my_view AS SELECT my_text COLLATE "C" FROM my_table;
>> create OR REPLACE view my_view AS SELECT my_text COLLATE "en_US.utf8" FROM
>> my_table;
>>
>> EXPECTED RESULT :
>> ===================
>> The COLLATION of "my_view"."my_text" should have been changed to
>> "en_US.utf8".
>> - OR -
>> I should have got an error message telling me that it is not possible.
>>
>> OBSERVED RESULT :
>> ===================
>> Nothing (it silently fails).
>>
>> select table_schema, table_name, column_name, collation_name from
>> information_schema.columns where collation_name is not null and
>> table_name='my_view';
>> clearly shows that the COLLATION has not been changed as requested.
>>
>> What the doc says about CREATE OR REPLACE VIEW :
>> =============================================
>> "The new query must generate the same columns that were generated by the
>> existing view query (that is, the same column names in the same order and
>> with the *same data types*), but it may add additional columns to the end of
>> the list.
>> *The calculations giving rise to the output columns may be completely
>> different*."
>>
>> It doesn't say wether the COLLATION is considered being part of the "data
>> types" or not, i.e. it doesn't say wether it's possible to change the
>> COLLATION of an existing field or not.
>> My tests (under Postgres v. 12.4) trying to do so showed that postgres says
>> nothing and seems to accept the SQL statement, but nothing is changed in the
>> database (it silently fails). From the user perspective, I consider such a
>> silent failure the WORST situation possible : I would prefer either 1) to
>> get an error message saying that it's not possible to change the COLLATION
>> of an existing field - OR even better - 2) to actually change the COLLATION
>> of the existing field, as requested.
>>
>> For this reason, I have also reported a documentation issue.
>
> I find the checkViewTupleDesc() function do not check the collation[1]. So it
> will not update the collation of columns.
>
Sorry for unfinished content.
After checkViewtupledesc() checking, DefineVirtualRelation() only insert the new
attributes for the view, and do not touch the old attributes, so the collation
have no chance to change[2].
[2]
/*
* If new attributes have been added, we must add pg_attribute entries
* for them. It is convenient (although overkill) to use the ALTER
* TABLE ADD COLUMN infrastructure for this.
*
* Note that we must do this before updating the query for the view,
* since the rules system requires that the correct view columns be in
* place when defining the new rules.
*
* Also note that ALTER TABLE doesn't run parse transformation on
* AT_AddColumnToView commands. The ColumnDef we supply must be ready
* to execute as-is.
*/
if (list_length(attrList) > rel->rd_att->natts)
{
ListCell *c;
int skip = rel->rd_att->natts;
foreach(c, attrList)
{
if (skip > 0)
{
skip--;
continue;
}
atcmd = makeNode(AlterTableCmd);
atcmd->subtype = AT_AddColumnToView;
atcmd->def = (Node *) lfirst(c);
atcmds = lappend(atcmds, atcmd);
}
/* EventTriggerAlterTableStart called by ProcessUtilitySlow */
AlterTableInternal(viewOid, atcmds, true);
/* Make the new view columns visible */
CommandCounterIncrement();
}
--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field.
2022-02-15 08:51 BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field. PG Bug reporting form <noreply@postgresql.org>
@ 2022-02-15 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-02-15 16:12 UTC (permalink / raw)
To: pageorge@unice.fr; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> create TABLE my_table (my_text TEXT);
> create VIEW my_view AS SELECT my_text COLLATE "C" FROM my_table;
> create OR REPLACE view my_view AS SELECT my_text COLLATE "en_US.utf8" FROM
> my_table;
> The COLLATION of "my_view"."my_text" should have been changed to
> "en_US.utf8".
> - OR -
> I should have got an error message telling me that it is not possible.
Yeah, that's a bug, it should have thrown an error.
(Changing the collation is impractical for the same reasons that
changing the output datatype is impractical.)
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-02-15 16:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-02-15 08:51 BUG #17404: CREATE OR REPLACE VIEW does not properly change the COLLATION of an existing field. PG Bug reporting form <noreply@postgresql.org>
2022-02-15 15:42 ` Japin Li <japinli@hotmail.com>
2022-02-15 16:00 ` Japin Li <japinli@hotmail.com>
2022-02-15 16:12 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox