Skip site navigation (1) Skip section navigation (2)

Re: How to use views&rules to dynamically choose which table to update

From: Ashley Moran <work(at)ashleymoran(dot)me(dot)uk>
To: David Fetter <david(at)fetter(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to use views&rules to dynamically choose which table to update
Date: 2006-03-31 23:40:37
Message-ID: 3B7CD1B0-46C3-4B95-BC49-C77E7C179E07@ashleymoran.me.uk (view raw or flat)
Thread:
Lists: pgsql-general
Hi David

On Apr 01, 2006, at 12:13 am, David Fetter wrote:
> Wow.  That's confusing.

You're telling me! :D

> How about using table partitioning for this?
>
> <http://www.postgresql.org/docs/current/static/ddl-partitioning.html>


Thanks for your suggestion.  I've had a look at partitioning but the  
problem is this:

Every night we get a new set of data relating to cars (makes, models,  
prices, options etc) and the same for vans, and one day bikes, wagons  
and caravans.  Each has an identical schema, so I want to treat all  
the data as coming from the same source.  BUT - the primary keys used  
in the tables are not unique across all the datasets.  So for  
example, as Ford Focus (car) might have the same ID as an Iveco Daily  
(van).  I think this alone precludes table partitioning (nice feature  
though! I bet it's useful for really heavyweight databases).

Now the current application is written in C# and NHibernate (yuk) so  
to avoid duplicating the already sprawling code and configuration, I  
had the idea of creating a view to UNION all the data from the  
different datasets and prepend a column to distinguish which class of  
vehicle it relates to.  So a row would be identified ('car', 203) to  
distinguish it from the van/bike/spaceship with id 203.

The rewrite I'm proposing will be in Ruby.  Unit tests in Ruby on  
Rails have a habit of just chucking data at the table it thinks wants  
it.  So I figured I could reverse the behaviour of the view to let me  
insert data into the individual tables, and my app would not realise  
it was using multiple tables to fulfil the query (data in or out)  
using a single model class.

Maybe I will need to do something really arcane - I could perhaps  
dynamically generate classes in my app to use to load the test data,  
but that would involved poring over the Rails source to see how  
everything works.  I was hoping there would be a nice simple (oh I  
laugh now) way of doing things in Postgres itself.  Right now, I  
don't know which approach is more mind-bending!

Regards
Ashley

In response to

pgsql-general by date

Next:From: chris smithDate: 2006-03-31 23:51:53
Subject: Re: giving users access to specific databases
Previous:From: Magnus HaganderDate: 2006-03-31 23:21:29
Subject: Re: pgsql continuing network issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group