Thread: Why is my view ddl being altered by postgres?

I am new to this list, and pretty new to postgres.  I have used Oracle,
DB2, MS Sql Server, etc., for several years, but I still run into things
unique to postgres that stump me.

I am running Postgres 8.0.13 on a 2.6.5-xx smp Linux kernel.

I tried rewriting an existing view based on a multi-way join into
several smaller views, to keep the RAM footprint of a reporting query as
small as possible.  One of these views (in a series) is being
corrupted/changed by postgres.

 I am creating the view via a web tool (phppgadmin) with this ddl:

CREATE VIEW vw_data_3 AS
SELECT DISTINCT vw2.other_table_id, vw2.somedata1, vw2.somedata2,
vw2.somedata3, vw2.somedata4,
CASE
  WHEN table1.value::text = 'V001'::text
     THEN 1
     ELSE 0
  END AS value1,
CASE
  WHEN table1.value::text = 'V002'::text
     THEN 1
     ELSE 0
  END AS value2,
CASE
  WHEN table1.value::text = 'V003'::text
     THEN 1
     ELSE 0
  END AS value3
from vw_data_2 vw2
LEFT OUTER JOIN table1
 ON vw2.other_table_id = table1.other_table_id
ORDER BY vw2.other_table_id;


However, looking at the definition of this view in phppgadmin, the ORDER
BY clause gets messed up:



SELECT DISTINCT vw2.other_table_id, vw2.somedata1, vw2.somedata2,
vw2.somedata3, vw2.somedata4,
        CASE
            WHEN table1.value::text = 'V001'::text THEN 1
            ELSE 0
        END AS value1,
        CASE
            WHEN table1.value::text = 'V002'::text THEN 1
            ELSE 0
        END AS value2,
        CASE
            WHEN table1.value::text = 'V003'::text THEN 1
            ELSE 0
        END AS value3
   FROM vw_data_2 vw2
   LEFT JOIN  table1 ON vw2.other_table_id = table1.other_table_id
  ORDER BY vw2.station_transactionid, vw2.accepted, vw2.rejected,
vw2.bulk, vw2.individual,
   CASE
       WHEN table1.value::text = 'V001'::text THEN 1
       ELSE 0
   END,
   CASE
       WHEN table1.value::text = 'V002'::text THEN 1
       ELSE 0
   END,
   CASE
       WHEN table1.value::text = 'V003'::text THEN 1
       ELSE 0
   END;


If there is something postgres-centric that I am missing, please let me
know.

--- Brent Friedman <bfriedman@scanonline.com> wrote:
> I tried rewriting an existing view based on a multi-way join into
> several smaller views, to keep the RAM footprint of a reporting query as
> small as possible.  One of these views (in a series) is being
> corrupted/changed by postgres.

If you don't get a reply soon, you might want to repost this email on the Postgresql General
mailing list.  There are more people on that mailing list that are able to help trouble-shoot
problems with postgresql.

Regards,
Richard Broersma Jr.

Brent Friedman <bfriedman@scanonline.com> writes:
>  I am creating the view via a web tool (phppgadmin) with this ddl:

> CREATE VIEW vw_data_3 AS
> SELECT DISTINCT vw2.other_table_id, vw2.somedata1, vw2.somedata2,
> vw2.somedata3, vw2.somedata4,
> CASE
>   WHEN table1.value::text = 'V001'::text
>      THEN 1
>      ELSE 0
>   END AS value1,
> CASE
>   WHEN table1.value::text = 'V002'::text
>      THEN 1
>      ELSE 0
>   END AS value2,
> CASE
>   WHEN table1.value::text = 'V003'::text
>      THEN 1
>      ELSE 0
>   END AS value3
> from vw_data_2 vw2
> LEFT OUTER JOIN table1
>  ON vw2.other_table_id = table1.other_table_id
> ORDER BY vw2.other_table_id;

> However, looking at the definition of this view in phppgadmin, the ORDER
> BY clause gets messed up:

It's not "messed up", it's just doing what it has to do to implement the
DISTINCT.  Namely, sort all the rows into order.  The original ORDER BY
is still satisfied.

This is an implementation detail that maybe we should make more effort
to hide, but it's not wrong.

            regards, tom lane

Brent Friedman skrev:
>
> I tried rewriting an existing view based on a multi-way join into
> several smaller views, to keep the RAM footprint of a reporting query
> as small as possible.  One of these views (in a series) is being
> corrupted/changed by postgres.

> SELECT DISTINCT vw2.other_table_id, vw2.somedata1, vw2.somedata2,
> vw2.somedata3, vw2.somedata4,
[...]
> ORDER BY vw2.station_transactionid, vw2.accepted, vw2.rejected, vw2.bulk,
> vw2.individual,

Did you by any chance forget to anonymize the field names in the last line?

Nis