pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVersionning (was: Whole-row comparison)
6+ messages / 2 participants
[nested] [flat]
* Versionning (was: Whole-row comparison)
@ 2007-06-01 17:07 christian.roche.ext@nsn.com
0 siblings, 1 reply; 6+ messages in thread
From: christian.roche.ext@nsn.com @ 2007-06-01 17:07 UTC (permalink / raw)
To: pgsql-sql
Hi all,
first let me tell you that this nice "whole-row comparison" feature of
postgres 8.2 allowed me to create a versionned database model very
neatly. The SQL statement that inserts in the destination table only
the one rows that are new or that have changed since last time is very
simply written:
INSERT INTO bsc_table
SELECT nextval('version_seq'), <fields>
FROM load.bsc_table AS ld LEFT JOIN bsc_view AS nt USING (obj_id)
WHERE nt.obj_id IS NULL OR row(nt.*) <> row(ld.*);
bsc_view is a view that returns the latest version of each object in the
bsc table:
CREATE VIEW bsc_view AS
SELECT <fields>
FROM bsc_table
WHERE (obj_id, ver_id) IN (SELECT obj_id, max(ver_id) FROM bsc_table
GROUP BY obj_id);
This is all nice as long as I only want to access the very last version
of the table. However what should be done if I now need to access an
earlier version ? The most elegant way would be to pass a ver_id
parameter to bsc_view, something like :
CREATE VIEW bsc_view(int) AS
SELECT <fields>
FROM bsc_table
WHERE (obj_id, ver_id) IN
(SELECT obj_id, max(ver_id)
FROM bsc_table
WHERE ver_id <= $1
GROUP BY obj_id));
However postgres doesn't allow parameters in views as far as I know. I
guess I could create a function returning a set of rows, but then I
would lose most advantages of rewritten views, especially optimization,
right ?
I've contemplated reusing an awful hack from my Access era, namely using
a single-rowed table to store the parameter and joining the view on it.
The parameter would be updated before the view is called; this would
work but would definitely be ugly. Can someone think of a better way to
do that ?
Thanks a lot,
Christian
-----Original Message-----
I'm trying to implement a "versionned" storage for a datawarehouse
system, meaning I have tables which store daily information about
objects and I would like to have a row inserted (i.e. a new version)
only if it differs from the most recent existing row. For instance
instead of storing
version attribute1 attribute2
1 x y
2 x y
3 x y
4 z y
5 z y
6 z t
we would only keep the diffs :
version attribute1 attribute2
1 x y
4 z y
6 z t
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Versionning (was: Whole-row comparison)
@ 2007-06-01 17:47 Andrew Sullivan <ajs@crankycanuck.ca>
parent: christian.roche.ext@nsn.com
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Sullivan @ 2007-06-01 17:47 UTC (permalink / raw)
To: pgsql-sql
On Fri, Jun 01, 2007 at 08:07:46PM +0300, christian.roche.ext@nsn.com wrote:
> I've contemplated reusing an awful hack from my Access era, namely using
> a single-rowed table to store the parameter and joining the view on it.
> The parameter would be updated before the view is called; this would
> work but would definitely be ugly. Can someone think of a better way to
> do that ?
I sort of don't see how that hack would be any different from a SRF.
You'd lose the planner benefits anyway, I think, because you'd have
to plan for the generic case where the data could be anything, no?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Versionning (was: Whole-row comparison)
@ 2007-06-01 18:07 christian.roche.ext@nsn.com
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 6+ messages in thread
From: christian.roche.ext@nsn.com @ 2007-06-01 18:07 UTC (permalink / raw)
To: ajs@crankycanuck.ca; +Cc: pgsql-sql
Hi Andrew,
I must admit I don't really understand what you say. I don't know what
SRF stand for, and what you say about generic case is not clear to me,
sorry.
My idea is that using a parameter table allows me to keep using a view,
which is optimized for instance when used against a WHERE condition.
For example, I could write :
CREATE VIEW bsc_view AS
SELECT <fields>
FROM bsc_table
WHERE (obj_id, ver_id) IN
(SELECT obj_id, max(ver_id) FROM bsc_table, param_table
WHERE ver_id <= param_table.ver_id
GROUP BY obj_id));
and the following statement would be optimized:
UPDATE param_table SET ver_id = xxx;
SELECT * FROM bsc_view WHERE obj_id = yyy;
which would not be the case would I have used a multi-row function.
Does this make sense ?
Thanks a lot,
Christian
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of ext Andrew Sullivan
Sent: Friday, June 01, 2007 17:47
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Versionning (was: Whole-row comparison)
On Fri, Jun 01, 2007 at 08:07:46PM +0300, christian.roche.ext@nsn.com
wrote:
> I've contemplated reusing an awful hack from my Access era, namely
> using a single-rowed table to store the parameter and joining the view
on it.
> The parameter would be updated before the view is called; this would
> work but would definitely be ugly. Can someone think of a better way
> to do that ?
I sort of don't see how that hack would be any different from a SRF.
You'd lose the planner benefits anyway, I think, because you'd have to
plan for the generic case where the data could be anything, no?
A
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Versionning (was: Whole-row comparison)
@ 2007-06-01 18:51 Andrew Sullivan <ajs@crankycanuck.ca>
parent: christian.roche.ext@nsn.com
0 siblings, 1 reply; 6+ messages in thread
From: Andrew Sullivan @ 2007-06-01 18:51 UTC (permalink / raw)
To: pgsql-sql
On Fri, Jun 01, 2007 at 09:07:10PM +0300, christian.roche.ext@nsn.com wrote:
>
> Hi Andrew,
>
> I must admit I don't really understand what you say. I don't know what
> SRF stand for, and what you say about generic case is not clear to me,
> sorry.
Sorry, it stands for set returning function. I thought someone
upthread suggested that instead of a view.
> and the following statement would be optimized:
>
> UPDATE param_table SET ver_id = xxx;
> SELECT * FROM bsc_view WHERE obj_id = yyy;
>
> which would not be the case would I have used a multi-row function.
>
> Does this make sense ?
Yes, but I don't think it's true. Because you change the value of
ver_id all the time, the actual result can't be collapsed to a
constant, so you end up having to execute the query with the
additional value, and you still have to plan that. The same thing is
true of a function, which will have its plan prepared the first time
you execute it. (I could be wrong about this; I suppose the only way
would be to try it.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
However important originality may be in some fields, restraint and
adherence to procedure emerge as the more significant virtues in a
great many others. --Alain de Botton
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Versionning (was: Whole-row comparison)
@ 2007-06-04 10:40 christian.roche.ext@nsn.com
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 1 reply; 6+ messages in thread
From: christian.roche.ext@nsn.com @ 2007-06-04 10:40 UTC (permalink / raw)
To: ajs@crankycanuck.ca; +Cc: pgsql-sql
Hi Andrew,
what is worrying me is that if I use a SRF, any additional WHERE
condition would not be taken into account before executing the
underlying query, e.g., in this request using a view, the WHERE
condition would be considered in the final query :
UPDATE params
SET version = ver_id;
SELECT *
FROM bsc_list_view
WHERE obj_id = 'xxx';
because the bsc_list_view would be expanded to the underlying request,
while using a SRF, the whole table would be scaned before the WHERE
condition is applied:
SELECT *
FROM bsc_list_srf(ver_id)
WHERE obj_id = 'xxx';
This is what I mean when I say that the optimization would be lost when
using a SRF. Now what is the "Right Thing To Do" in this particular
case ? The nicest thing would really to have parametrized view. Is
there any fundamental reason why such a beast does not exist, or is it
only postgres (compared to higher-level RDBMS) ?
Thanks a lot !
Christian
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of ext Andrew Sullivan
Sent: Friday, June 01, 2007 18:51
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Versionning (was: Whole-row comparison)
Yes, but I don't think it's true. Because you change the value of
ver_id all the time, the actual result can't be collapsed to a constant,
so you end up having to execute the query with the additional value, and
you still have to plan that. The same thing is true of a function,
which will have its plan prepared the first time you execute it. (I
could be wrong about this; I suppose the only way would be to try it.)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Versionning (was: Whole-row comparison)
@ 2007-06-04 13:12 Andrew Sullivan <ajs@crankycanuck.ca>
parent: christian.roche.ext@nsn.com
0 siblings, 0 replies; 6+ messages in thread
From: Andrew Sullivan @ 2007-06-04 13:12 UTC (permalink / raw)
To: pgsql-sql
On Mon, Jun 04, 2007 at 01:40:18PM +0300, christian.roche.ext@nsn.com wrote:
> case ? The nicest thing would really to have parametrized view. Is
> there any fundamental reason why such a beast does not exist, or is it
> only postgres (compared to higher-level RDBMS) ?
I don't think there's a fundamental reason, no. But why couldn't you
change your query to issue the SRF directly, with the parameter:
SELECT * FROM some_srf(param1, param2)?
A
--
Andrew Sullivan | ajs@crankycanuck.ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-06-04 13:12 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-01 17:07 Versionning (was: Whole-row comparison) christian.roche.ext@nsn.com
2007-06-01 17:47 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-01 18:07 ` christian.roche.ext@nsn.com
2007-06-01 18:51 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-06-04 10:40 ` christian.roche.ext@nsn.com
2007-06-04 13:12 ` Andrew Sullivan <ajs@crankycanuck.ca>
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