Re: Permanent alias for postgresql table - Mailing list pgsql-sql

From Mina R Waheeb
Subject Re: Permanent alias for postgresql table
Date
Msg-id 275890790903131138s49d4ed86i437256af857d41a6@mail.gmail.com
Whole thread Raw
In response to Re: Permanent alias for postgresql table  (Lennin Caro <lennin.caro@yahoo.com>)
List pgsql-sql
Hi,
   Somewhere the query should be rewritten, on the server side with a VIEW or FUNCTION or in the query generator (SQL client side). If you worry about the performance create a test case and test the performance, Also EXPLAIN could help you. The VIEW is optimized by the server and it will be better than custom FUNCTION, go VIEW if you don't frequently create a new version of the table.

Its hard to apply versioning concept in current SQL model/implementation, By using this approach you will lose a lot of the engine features and you will have to rewrite them on your own for example foreign keys. If you really have a lot of objects represented in tables and you plan to have unknown number of versions for each table/object then I believe you should reconsider your approach.

Best regards,
Mina R Waheeb


On Thu, Mar 12, 2009 at 6:54 PM, Lennin Caro <lennin.caro@yahoo.com> wrote:

-- On Thu, 3/12/09, Marco Lechner <marco.lechner@geographie.uni-freiburg.de> wrote:

> From: Marco Lechner <marco.lechner@geographie.uni-freiburg.de>
> Subject: Re: [SQL] Permanent alias for postgresql table
> To: pgsql-sql@postgresql.org
> Date: Thursday, March 12, 2009, 2:26 PM
> Hi Mina,
>
> thanks for your answer. I thought about that, but don't
> views decrease performance, because they are
> "calculated"
> on access?
>
> Marco
>
> On Thu, 12 Mar 2009 13:34:39 +0000
>  Mina R Waheeb <syncer@gmail.com> wrote:
> > Use views,
> >
> > mytablev1 and we have a view mytable selecting * from
> > mytablev1
> > and when we need to update the target we alter the
> view
> > to select from
> > mytablev2
> >
> > On Thu, Mar 12, 2009 at 12:59 PM, Marco Lechner <
> > marco.lechner@geographie.uni-freiburg.de> wrote:
> >
> > > Hi list,
> > >
> > > I'm searching for a way to create permanent
> alias for
> > > tablenames in postgresql. We are storing various
> > versions
> > > of a routable network in postgresql (postgis,
> > pgrouting)
> > > and access a certain version with a bunch of
> > php-skripts.
> > > We like to use aliases for the "currently
> used tables"
> > oo
> > > be able to relink the current tables rapidly by
> > changing
> > > the alias target.
> > >
> > > Any idea - or is this approach nonsense?
> > >
> > > Marco
> > >
> > > --
> > > Sent via pgsql-sql mailing list
> > (pgsql-sql@postgresql.org)
> > > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-sql
> > >
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql

you can use partition table, whit a column whit the version of the data and create the partition for this column....




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

pgsql-sql by date:

Previous
From: Steve Midgley
Date:
Subject: Re: select count of all overlapping geometries and return 0 if none.
Next
From: Peter Eisentraut
Date:
Subject: Re: select count of all overlapping geometries and return 0 if none.