Обсуждение: Cosmetically-varying casts added to view definitions

On 07/30/2018 02:26 PM, Ken Tanzer wrote:
> Hi.  As background/context, I'm working on a script to take a series of 
> databases and make them timezone-aware.  This basically involves saving 
> all the view definitions, dropping all the views, changing all the 
> timestamp columns without time zones to TS with TZ, and then recreating 
> all the views.  As a sanity check on all of this, I compared the 
> resulting view definitions to what existed before starting.  Most of 
> them were equivalent, with a few exceptions.
> 
> These exceptions all seem to be where slightly different casting is used 
> when the view definition gets created.  For example, starting with this 
> view (payment_form_code is a varchar(20):
> 
> ag_reach=> CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE 
> payment_form_code IN ('CREDIT_CARD','OTHER'));
> 
> CREATE VIEW
> ag_reach=> \d+ test_tmp
>                   View "public.test_tmp"
>    Column  |  Type   | Modifiers | Storage | Description
> ----------+---------+-----------+---------+-------------
>   ?column? | integer |           | plain   |
> View definition:
>   SELECT 1
>     FROM l_payment_form
>    WHERE l_payment_form.payment_form_code::text = ANY (ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character
varying]::text[]);
> 
> I wasn't very surprised by that.  But when I take the view definition as 
> stored in PG above, and create a new view:
> 
> ag_reach=> CREATE VIEW test_tmp2 AS SELECT 1 FROM l_payment_form WHERE 
> l_payment_form.payment_form_code::text = ANY
> 
> (ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character varying]::text[]);
> 
> CREATE VIEW
> 
> ag_reach=> \d+ test_tmp2
>                  View "public.test_tmp2"
>    Column  |  Type   | Modifiers | Storage | Description
> ----------+---------+-----------+---------+-------------
>   ?column? | integer |           | plain   |
> View definition:
>   SELECT 1
>     FROM l_payment_form
>    WHERE l_payment_form.payment_form_code::text = ANY (ARRAY['CREDIT_CARD'::character varying::text,
'OTHER'::charactervarying::text]);
 
> 
>   you'll see it now casts the individual array elements to text, rather 
> than the whole array as in test_tmp.

Which is where it stops from my test:

CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE season IN 
('annual', 'perennial'));

View "public.cp_view"
   Column  |  Type   | Collation | Nullable | Default | Storage | 
Description
----------+---------+-----------+----------+---------+---------+-------------
  ?column? | integer |           |          |         | plain   |
View definition:
  SELECT 1
    FROM cell_per
   WHERE cell_per.season::text = ANY (ARRAY['annual'::character varying, 
'perennial'::character varying]::text[]);

CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE 
cell_per.season::text = ANY (ARRAY['annual'::character varying, 
'perennial'::character varying]::text[]));

View "public.cp_view"
   Column  |  Type   | Collation | Nullable | Default | Storage | 
Description
----------+---------+-----------+----------+---------+---------+-------------
  ?column? | integer |           |          |         | plain   |
View definition:
  SELECT 1
    FROM cell_per
   WHERE cell_per.season::text = ANY (ARRAY['annual'::character 
varying::text, 'perennial'::character varying::text]);


CREATE  VIEW cp_view AS (SELECT 1 FROM cell_per WHERE 
cell_per.season::text = ANY (ARRAY['annual'::character varying::text, 
'perennial'::character varying::text]));

View "public.cp_view"
   Column  |  Type   | Collation | Nullable | Default | Storage | 
Description
----------+---------+-----------+----------+---------+---------+-------------
  ?column? | integer |           |          |         | plain   |
View definition:
  SELECT 1
    FROM cell_per
   WHERE cell_per.season::text = ANY (ARRAY['annual'::character 
varying::text, 'perennial'::character varying::text]);

Seems to be simplifying down to an end point.

> 
> This doesn't impair the view's functionality, so I can't necessarily 
> complain.  But it does make it harder for me to know if the views were 
> recreated correctly.  I'd be curious to know what is going on here, and 
> if there's any way to avoid this behavior.

The only way I can think of to maintain a consistent definition is to 
always create the view from the original definition:

CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE 
payment_form_code IN ('CREDIT_CARD','OTHER'));

> 
> Thanks!
> 
> Ken
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 07/30/2018 04:29 PM, Ken Tanzer wrote:
> On Mon, Jul 30, 2018 at 4:10 PM Adrian Klaver <adrian.klaver@aklaver.com 

> Thanks Adrian.  I do have what are supposed to be the original view 
> definitions, but I'm less than 100% confident they are accurate and 
> up-to-date, which is why I thought to use the actual definitions as 
> stored.  Might have to rethink that one though, or just take a leap of 
> faith that the views will functionally be the same!

Or write a test or tests to verify they are the same.

> 
> Cheers,
> Ken
> 
> p.s.,  I forgot to mention this in my original post, but for the record, 
> PG version 9.6.9.
> -- 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 07/30/2018 04:57 PM, Ken Tanzer wrote:
> On Mon, Jul 30, 2018 at 4:52 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 07/30/2018 04:29 PM, Ken Tanzer wrote:
>      > On Mon, Jul 30, 2018 at 4:10 PM Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 
>      > Thanks Adrian.  I do have what are supposed to be the original view
>      > definitions, but I'm less than 100% confident they are accurate and
>      > up-to-date, which is why I thought to use the actual definitions as
>      > stored.  Might have to rethink that one though, or just take a
>     leap of
>      > faith that the views will functionally be the same!
> 
>     Or write a test or tests to verify they are the same.
> 
> 
> How would you do that exactly?  I can see verifying that they return the 
> same rows given existing data, but not that this would be true with 
> different data.

Well if the different definitions of the view are returning the exact 
same data currently that would mean to me their selection criteria are 
the same. Any data in the future would be subjected to the same criteria 
so there would not be a problem. That is unless you want to change the 
criteria in the future and which point you would need to decide which 
view definition to base the changes off.

> 
> Did you mean comparing the returned results, writing some view-specific 
> tests on a case-by-case basis, or something way way better that I'm not 
> grasping?

I would say comparing the results. You know what you want the view to 
return, so select from it for known values and see if it returns what 
you expect.

> 
> Thanks!
> 
> Ken
> -- 
> AGENCY Software


-- 
Adrian Klaver
adrian.klaver@aklaver.com