Thread: Permanent alias for postgresql table

Permanent alias for postgresql table

From
"Marco Lechner"
Date:
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


Re: Permanent alias for postgresql table

From
Thomas Kellerer
Date:
Marco Lechner, 12.03.2009 13:59:
> 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?

A view? 

CREATE VIEW constant_table_name
AS 
SELECT *
FROM current_table

Thomas



Re: Permanent alias for postgresql table

From
"Marco Lechner"
Date:
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 +0000Mina 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
> >



Re: Permanent alias for postgresql table

From
Joshua Tolley
Date:
On Thu, Mar 12, 2009 at 03:26:47PM +0100, Marco Lechner wrote:
> Hi Mina,
>
> thanks for your answer. I thought about that, but don't
> views decrease performance, because they are "calculated"
> on access?

The query gets rewritten a bit, but it's not a big deal. A more
important concern might be that to make it so you can add / modify data
in the table, you'll need to create rules to rewrite UPDATE and INSERT
queries on that view to instead affect the underlying table.

- Josh / eggyknap

Re: Permanent alias for postgresql table

From
Thomas Kellerer
Date:
Marco Lechner, 12.03.2009 15:26:
> Hi Mina,
> 
> thanks for your answer. I thought about that, but don't
> views decrease performance, because they are "calculated"
> on access?

I'm not sure what you mean with "calculated". A view is just a SQL query.
There is no difference in executing the SQL query that's behind a view or the view itself. Except for the minimal time
ittakes to retrieve the view definition. 
 

But I would never sacrifice easy of development or usage for the microseconds of overhead the VIEW generates. 

And the "overhead" (if at all) will be neglectable compared to the time it takes to return the result.

Thomas



Re: Permanent alias for postgresql table

From
Lennin Caro
Date:
-- 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....

     


Re: Permanent alias for postgresql table

From
Mina R Waheeb
Date:
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