pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedAfter upgrade from version9.4.1 to 10.15, changes on the table structure
2+ messages / 2 participants
[nested] [flat]
* After upgrade from version9.4.1 to 10.15, changes on the table structure
@ 2021-03-24 11:09 Mundla, Sankar <Sankar.Mundla@rsa.com>
2021-03-24 14:26 ` Re: After upgrade from version9.4.1 to 10.15, changes on the table structure Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Mundla, Sankar @ 2021-03-24 11:09 UTC (permalink / raw)
To: pgsql-bugs
Hi,
I have created the table with the constraint like below from the where my postgres version is 9.4.1.
CREATE TABLE com_rep.am_dummy_simulate (
id character varying(32) NOT NULL,
label_key character varying(1020) NOT NULL,
is_editable_ind boolean NOT NULL,
domain_object_type character varying(32) NOT NULL
);
ALTER TABLE com_rep.am_dummy_simulate ADD CONSTRAINT CK_am_dummy_simulation CHECK(DOMAIN_OBJECT_TYPE IN ('PRINCIPAL','TOKEN') );
Below my table structure:
db=# \d com_rep.am_dummy_simulate
Table "com_rep.am_dummy_simulate"
Column | Type | Collation | Nullable | Default
--------------------+-------------------------+-----------+----------+---------
id | character varying(32) | | not null |
label_key | character varying(1020) | | not null |
is_editable_ind | boolean | | not null |
domain_object_type | character varying(32) | | not null |
Check constraints:
"ck_am_dummy_simulation" CHECK (domain_object_type::text = ANY (ARRAY['PRINCIPAL'::character varying, 'TOKEN'::character varying]::text[]))
Now I upgrade to 10.5 using pg_upgrade utility.
db-# \d am_dummy_simulate
Table "com_rep.am_dummy_simulate"
Column | Type | Modifiers
--------------------+-------------------------+-----------
id | character varying(32) | not null
label_key | character varying(1020) | not null
is_editable_ind | boolean | not null
domain_object_type | character varying(32) | not null
Check constraints:
"ck_am_dummy_simulation" CHECK (domain_object_type::text = ANY (ARRAY['PRINCIPAL'::character varying::text, 'TOKEN'::character varying::text]))
Why is this behavior ? I am suspecting pg_restore is doing some manipulation here.
When I checked the upgrade log,pg_dump is exporting like below.
CREATE TABLE com_rep.am_dummy_simulate (
id character varying(32) NOT NULL,
label_key character varying(1020) NOT NULL,
is_editable_ind boolean NOT NULL,
domain_object_type character varying(32) NOT NULL,
CONSTRAINT ck_am_dummy_simulation CHECK (((domain_object_type)::text = ANY ((ARRAY['PRINCIPAL'::character varying, 'TOKEN'::character varying])::text[])))
);
Any explanation will be help full.
Regards,
Sankar
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: After upgrade from version9.4.1 to 10.15, changes on the table structure
2021-03-24 11:09 After upgrade from version9.4.1 to 10.15, changes on the table structure Mundla, Sankar <Sankar.Mundla@rsa.com>
@ 2021-03-24 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2021-03-24 14:26 UTC (permalink / raw)
To: Mundla, Sankar <Sankar.Mundla@rsa.com>; +Cc: pgsql-bugs
"Mundla, Sankar" <Sankar.Mundla@rsa.com> writes:
> [ dump and reload converts ]
> "ck_am_dummy_simulation" CHECK (domain_object_type::text = ANY (ARRAY['PRINCIPAL'::character varying, 'TOKEN'::character varying]::text[]))
> [ into ]
> "ck_am_dummy_simulation" CHECK (domain_object_type::text = ANY (ARRAY['PRINCIPAL'::character varying::text, 'TOKEN'::character varying::text]))
> Why is this behavior ?
pg_dump, like psql, prefers to show implicit casts explicitly, to
reduce the risk of the expression being interpreted differently
when reloaded. So while you wrote the array without any cast
to begin with, you get the first form in the dump.
However, the parser treats array[...]::something[] differently
from a bare array[]. The cast to "something" is applied to each
array element immediately. The reason for that is that if we
just transformed array[...] without applying the knowledge that
the final type must be "something", we might get an unnecessary
parse failure from inability to resolve a common type for the
array elements. (The parser has no way to know, of course, that
some previous cycle of processing successfully resolved a common
type for the elements. This behavior is unnecessary in this
context, but it does help for manually-entered expressions.)
So you end up with something that looks a little different.
It's semantically equivalent though, so I see no bug here.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2021-03-24 14:26 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-24 11:09 After upgrade from version9.4.1 to 10.15, changes on the table structure Mundla, Sankar <Sankar.Mundla@rsa.com>
2021-03-24 14:26 ` 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