Thread: How to use views&rules to dynamically choose which table to update
I'm still relatively new to Postgres (at least when it comes to
clever stuff - especially rules) so I hope I've missed something here.
Basically I'm still trying to combine multiple databases with
identical schemas into one schema, adding a column to each table to
indicate which schema it came from. (I'm prototyping an app in Ruby
on Rails so I want to have only one set of model classes, instead of
5). So I have views defined like this:
SELECT 'schema1'::varchar(10), * from schema1.table1
UNION ALL
SELECT 'schema2'::varchar(10), * from schema2.table1
etc...
These tables are all from a data feed we pay for, and is updated
nightly. It is separate from my application database.
Now, I want to take advantage of Rails' unit tests on these tables,
because I need to simulate changes in the data feed. So I thought
maybe I could add rules to the views, so Rails can load its test
fixtures into the model I defined and not realise it is feeding
multiple back-end tables.
This is my effort in a test database, so you can see what I'm trying
to do:
CREATE SCHEMA english;
CREATE TABLE english."names" (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE SCHEMA french;
CREATE TABLE french."names" (
id serial NOT NULL PRIMARY KEY,
name character varying(50)
);
CREATE VIEW "names" AS
SELECT ('english'::character varying)::character varying(20)
AS "language", * FROM english."names";
UNION ALL
SELECT ('french'::character varying)::character varying(20)
AS "language", * FROM french."names";
CREATE RULE insert_english AS
ON INSERT TO "names"
WHERE (((new."language")::character varying(20))::text =
(('english'::character varying)::character varying
(20))::text)
DO INSTEAD INSERT INTO english."names" (name) VALUES (new.name);
CREATE RULE insert_french AS
ON INSERT TO "names"
WHERE (((new."language")::character varying(20))::text =
(('french'::character varying)::character varying(20))::text)
DO INSTEAD INSERT INTO french."names" (name) VALUES (new.name);
(Please forgive any mistakes above - I cobbled it together from a
backup file)
Now if I some french names and some english names into the relvant
tables, the view works fine on SELECT, but on INSERT I get this error:
ERROR: cannot insert into a view
HINT: You need an unconditional ON INSERT DO INSTEAD rule.
Which suggests that what I want to do is impossible. Does anyone
know of a way to do this? If I can do it in the database I can
probably save hours of hacking the unit tests in Rails.
Thanks
Ashley
On Sat, Apr 01, 2006 at 12:04:26AM +0100, Ashley Moran wrote:
> I'm still relatively new to Postgres (at least when it comes to
> clever stuff - especially rules) so I hope I've missed something here.
>
> Basically I'm still trying to combine multiple databases with
> identical schemas into one schema, adding a column to each table to
> indicate which schema it came from. (I'm prototyping an app in Ruby
> on Rails so I want to have only one set of model classes, instead of
> 5). So I have views defined like this:
>
> SELECT 'schema1'::varchar(10), * from schema1.table1
> UNION ALL
> SELECT 'schema2'::varchar(10), * from schema2.table1
>
> etc...
>
> These tables are all from a data feed we pay for, and is updated
> nightly. It is separate from my application database.
>
> Now, I want to take advantage of Rails' unit tests on these tables,
> because I need to simulate changes in the data feed. So I thought
> maybe I could add rules to the views, so Rails can load its test
> fixtures into the model I defined and not realise it is feeding
> multiple back-end tables.
>
> This is my effort in a test database, so you can see what I'm trying
> to do:
>
> CREATE SCHEMA english;
> CREATE TABLE english."names" (
> id serial NOT NULL PRIMARY KEY,
> name character varying(50)
> );
>
> CREATE SCHEMA french;
> CREATE TABLE french."names" (
> id serial NOT NULL PRIMARY KEY,
> name character varying(50)
> );
>
> CREATE VIEW "names" AS
> SELECT ('english'::character varying)::character varying(20)
> AS "language", * FROM english."names";
> UNION ALL
> SELECT ('french'::character varying)::character varying(20)
> AS "language", * FROM french."names";
>
>
> CREATE RULE insert_english AS
> ON INSERT TO "names"
> WHERE (((new."language")::character varying(20))::text =
> (('english'::character varying)::character varying
> (20))::text)
> DO INSTEAD INSERT INTO english."names" (name) VALUES (new.name);
Wow. That's confusing. How about using table partitioning for this?
<http://www.postgresql.org/docs/current/static/ddl-partitioning.html>
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!
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
On Sat, 1 Apr 2006, Ashley Moran wrote:
> I'm still relatively new to Postgres (at least when it comes to
> clever stuff - especially rules) so I hope I've missed something here.
>
> Basically I'm still trying to combine multiple databases with
> identical schemas into one schema, adding a column to each table to
> indicate which schema it came from. (I'm prototyping an app in Ruby
> on Rails so I want to have only one set of model classes, instead of
> 5). So I have views defined like this:
>
> SELECT 'schema1'::varchar(10), * from schema1.table1
> UNION ALL
> SELECT 'schema2'::varchar(10), * from schema2.table1
>
> etc...
>
> These tables are all from a data feed we pay for, and is updated
> nightly. It is separate from my application database.
>
> Now, I want to take advantage of Rails' unit tests on these tables,
> because I need to simulate changes in the data feed. So I thought
> maybe I could add rules to the views, so Rails can load its test
> fixtures into the model I defined and not realise it is feeding
> multiple back-end tables.
>
> This is my effort in a test database, so you can see what I'm trying
> to do:
>
> CREATE SCHEMA english;
> CREATE TABLE english."names" (
> id serial NOT NULL PRIMARY KEY,
> name character varying(50)
> );
>
> CREATE SCHEMA french;
> CREATE TABLE french."names" (
> id serial NOT NULL PRIMARY KEY,
> name character varying(50)
> );
>
> CREATE VIEW "names" AS
> SELECT ('english'::character varying)::character varying(20)
> AS "language", * FROM english."names";
> UNION ALL
> SELECT ('french'::character varying)::character varying(20)
> AS "language", * FROM french."names";
>
>
> CREATE RULE insert_english AS
> ON INSERT TO "names"
> WHERE (((new."language")::character varying(20))::text =
> (('english'::character varying)::character varying
> (20))::text)
> DO INSTEAD INSERT INTO english."names" (name) VALUES (new.name);
>
> CREATE RULE insert_french AS
> ON INSERT TO "names"
> WHERE (((new."language")::character varying(20))::text =
> (('french'::character varying)::character varying(20))::text)
> DO INSTEAD INSERT INTO french."names" (name) VALUES (new.name);
What should it do if you try to insert something that is neither french
nor english? I think an unconditional instead nothing rule might work
to supplement the two conditional ones if doing nothing is okay, but I
haven't tried.
>
> (Please forgive any mistakes above - I cobbled it together from a
> backup file)
>
> Now if I some french names and some english names into the relvant
> tables, the view works fine on SELECT, but on INSERT I get this error:
>
> ERROR: cannot insert into a view
> HINT: You need an unconditional ON INSERT DO INSTEAD rule.
>
> Which suggests that what I want to do is impossible. Does anyone
> know of a way to do this? If I can do it in the database I can
> probably save hours of hacking the unit tests in Rails.
>
> Thanks
> Ashley
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
On Apr 01, 2006, at 12:57 am, Stephan Szabo wrote: > What should it do if you try to insert something that is neither > french > nor english? I think an unconditional instead nothing rule might work > to supplement the two conditional ones if doing nothing is okay, but I > haven't tried. Wahey! The empty unconditional insert fixed it. I never cease to be amazed with the stuff postgres can do when it's properly trained. If the data is not english or french, (or in the real case, a car, van or bike etc) it's garbage and can be discarded, so I'm happy with this. It will only used for internal testing anyway. Thank you both for your time reading my very cryptic question :) Ashley