Обсуждение: 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