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
