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

Lists: pgsql-novice
From: Brent Friedman <bfriedman(at)scanonline(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Why is my view ddl being altered by postgres?
Date: 2007-09-28 19:06:48
Message-ID: 46FD50C8.3090602@scanonline.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-novice

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.


From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: Brent Friedman <bfriedman(at)scanonline(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Why is my view ddl being altered by postgres?
Date: 2007-09-28 20:05:57
Message-ID: 756132.74835.qm@web31804.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-novice


--- Brent Friedman <bfriedman(at)scanonline(dot)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.


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brent Friedman <bfriedman(at)scanonline(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Why is my view ddl being altered by postgres?
Date: 2007-09-28 21:08:16
Message-ID: 19204.1191013696@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-novice

Brent Friedman <bfriedman(at)scanonline(dot)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


From: Nis Jørgensen <nis(at)superlativ(dot)dk>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Why is my view ddl being altered by postgres?
Date: 2007-10-01 10:19:05
Message-ID: fdqhiv$q40$1@sea.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-novice

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