[Info-Ingres] Dropping and creating views

[Mike Leo]

Gang,

I've got a situation where tables are physically partitioned in a
database
into years:

y05_sales_order
y06_sales_order
y07_sales_order
y08_sales_order
y09_sales_order

Well, you get the idea. This design cannot be easily changed as there
is a
ton of ABF code that needs to work against it for years to come.

It doesn't mesh well with today's O/R mapping tools. But I found a
nice way
to do it using private views.

In our application, the user is connected to the database using her
actual credentials, so they have the luxury of creating private views
like

create view my_sales_order as select * from y06_sales_order

When the user of the app selects a different year to work with, we drop
all the private views and create new ones.

My question is, does this present a concurrency issue? Will all this
creating and dropping views cause contention on the system tables?

I don't pretend to understand the complex locking that is done during
DDL, but I'm sure somebody out there does.

Whaddaya think?

Mikey

[da...@skybeam.plus.com]

Hi,

Not certain how 'private' they can be, but might synonyms be an alternative?

Regards
Dave

> _______________________________________________
> Info-Ingres mailing list
> Info-...@kettleriverconsulting.com
> http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
>

[Roy Hann]

Mike Leo wrote:

I think it is worth a try, and I also had the idea of using synonyms
that Dave mentioned.

And here's another idea. How about creating a single physical table
with rule-based partitioning, then create views on the partitioned table
to give the old apps the original schema of yearly tables? This
would avoid any contention over the catalogues.

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

[rthdavid]

> And here's another idea.  How about creating a single physical
table
> with rule-based partitioning, then create views on the partitioned table
> to give the old apps the original schema of yearly tables? This
> would avoid any contention over the catalogues.

Been there, done that! ;-p
(see page 38 of our original Case Study at
http://www.iua.org.uk/conference/Autumn2006/AAH_112006.pdf#page=38)

Forget private views and synonyms if you really want to avoid horrible
contention on iitree and other catalogs.
A single physical table partitioned up with views on partitions is a
very clever solution that can also offer performance benefits.

The only downside of Ingres' current implementation of table
partitioning is that it is somewhat limited at the moment.
Ingres needs to have the ability to ONLINE MODIFY, TRUNCATE, MERGE,
DROP, ADD, BULK COPY and INDEX partitions.

[Karl Schendel]

On Nov 18, 2009, at 12:12 AM, Mike Leo wrote:

> [snip]

> create view my_sales_order as select * from y06_sales_order
>
> When the user of the app selects a different year to work with, we drop
> all the private views and create new ones.
>
> My question is, does this present a concurrency issue? Will all this
> creating and dropping views cause contention on the system tables?

A view create in Ingres is relatively heavy-weight, because of all
of the catalogs that must be touched. Very roughly, Ingres will:
- take an X control lock on the base table(s) (not good for concurrency)
- write view info into iiqrytext, iitree (both btree, page or row locks)
- write dependency info into iidbdepends and possibly iipriv (btree, page/row locks)
- write the view "table" info into iirelation / iiattribute (physical page
locks, page written and lock dropped at unfix)

You'll get [leaf] page locks on the btree catalogs unless you have session
or system isolation level set to repeatable_read or lower. Obviously you'll
need lock level set to row as well, for row-locking.

You can't force row locking in the core catalogs, but those page locks
aren't persistent (unlike the other locks).

The nastiest one is probably the X control lock on the base tables.
That has the effect of blocking selects on those tables, and I think
it even blocks in readlock=nolock mode (i haven't tried it recently).
I'm not real sure why Ingres feels the need to X-control-lock the
base tables; one would think that an S control lock would do.
But, that's the way we've always done it. I might put that one on
my to-do-someday list.

I like Roy's suggestion of one partitioned table with views on top
to simulate the old way. As for DBA tools for partitioned tables,
yes, we still need some of them. (I was going to do some when
I was at Datallegro, but we discovered a way to do most of the
necessary functions in the DSQL layer on top of Ingres,
with plenty of catalog hackery. I ended up having other
priorities to work on.)

Karl

[Roy Hann]

rthdavid wrote:

> A single physical table partitioned up with views on partitions is a
> very clever solution that can also offer performance benefits.
>
> The only downside of Ingres' current implementation of table
> partitioning is that it is somewhat limited at the moment.
> Ingres needs to have the ability to ONLINE MODIFY, TRUNCATE, MERGE,
> DROP, ADD, BULK COPY and INDEX partitions.

I agree most of those sound very useful but I'd be surprised if not
having them were a real show-stopper.

[Mike Leo]

On Nov 18, 2009, at 1:44 AM, Roy Hann wrote:

> [snip]

> And here's another idea. How about creating a single physical table
> with rule-based partitioning, then create views on the partitioned
> table
> to give the old apps the original schema of yearly tables? This
> would avoid any contention over the catalogues.
>
> --
> Roy
>

Roy,

I really like this idea, but I fear it will require quite a bit
of retrofit on the ABF side.

First, this client is currently running 2.6, but they are planning to
move to Ingres 2006 soon (they are on VMS).

The second problem is there is no date field in any of these tables,
but they are partition on "data for a particular year".

If the ABF application didn't need to update, this could be dealt
with via VIEWS, but I can't think of how to make the changes transparent
to "updates".

Any ideas?

Mikey

[Mike Leo]

Karl,

Synonyms are interesting. I did some testing, monitoring locks with
IPM,
and I noticed something.

If I create a view on each of the year-based tables (let's say in the
DBA's schema),
and then I create local synonyms for each user on the desired view, I
only see locks
being created on the view, and NOT on the ultimate base table.

And I noticed that creating synonyms is about 3 times "lighter", at
least lock-wise.

What do you think of that? Too much of a hack?

Seriously. What do you think?

Thanks,

Mikey

[Peter Gale]

Mikey,

Synonyms are the way to go here. They are not just lighter on locking but also have less impact on the system catalogs.

Bear in mind that the synonym is resolved at the time the SQL is executed, so if you create another DB object (view, dbproc etc) based on the synonym its the table name the synonym points to that is stored in the object definition. Changing the synonym will NOT change what the view or dbproc refers to.

Peter

2009/11/19 Mike Leo <ml...@kettleriverconsulting.com>

_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres

--
Peter Gale
pga...@gmail.com

[Karl Schendel]

On Nov 19, 2009, at 3:19 PM, Mike Leo wrote:
>
> Synonyms are interesting. I did some testing, monitoring locks with
> IPM,
> and I noticed something.
>
> If I create a view on each of the year-based tables (let's say in the
> DBA's schema),
> and then I create local synonyms for each user on the desired view, I
> only see locks
> being created on the view, and NOT on the ultimate base table.
>
> And I noticed that creating synonyms is about 3 times "lighter", at
> least lock-wise.
>
> What do you think of that? Too much of a hack?

Not at all. I have seen something similar done elsewhere,
although I can't recall the details any more.

Karl

[Roy Hann]

Mike Leo wrote:

>
> On Nov 18, 2009, at 1:44 AM, Roy Hann wrote:
>
>> [snip]
>> And here's another idea. How about creating a single physical table
>> with rule-based partitioning, then create views on the partitioned
>> table
>> to give the old apps the original schema of yearly tables? This
>> would avoid any contention over the catalogues.

> I really like this idea, but I fear it will require quite a bit

> of retrofit on the ABF side.
>
> First, this client is currently running 2.6, but they are planning to
> move to Ingres 2006 soon (they are on VMS).
>
> The second problem is there is no date field in any of these tables,
> but they are partition on "data for a particular year".

I guess it makes the implementation a bit more complicated, but adding a
year column to the consolidated table shouldn't be too hard, then simply
leave it out of the target list of columns in the view definitions.

> If the ABF application didn't need to update, this could be dealt
> with via VIEWS, but I can't think of how to make the changes transparent
> to "updates".

Provided your view definition is only a restriction based on year and
you project away just the new year column it will be an updateable
view. (The rules are the same as for an updateable cursor.)

[Mike Leo]

Roy,

OK, this intrigued me.  So I thought about it.  I need to preserve the "contract" of the

base tables (that don't have the "in year" data) from not only the select point of view, but from 

the insert and update point of view as well.

Then I wouldn't have to modify the ABF code.

Well, I tried it.  And at first I was discouraged:

* create table sales(customer varchar(20) not null, amount decimal(10,2) not null,  in_year int2 with null);

create view y05_sales as select customer, amount from sales where in_year = '2005';

insert into y05_sales values ('Hank', 22.22);

select * from sales;

select * from y05_sales;

* * * * * * * * * \g

Executing . . .

(1 row)

+--------------------+------------+------+

|customer            |amount      |in_yea|

+--------------------+------------+------+

|Hank                |       22.22|      |

+--------------------+------------+------+

(1 row)

+--------------------+------------+

|customer            |amount      |

+--------------------+------------+

+--------------------+------------+

(0 rows)

continue

* 

 

I tried it with in_year defined as "not null", and "with check option".  However, both delivered 

exceptions.

After only a couple of hours of dorking around, I came up with this:

* create table sales(customer varchar(20) not null, amount decimal(10,2) not null,  in_year int2 with null);
create view y05_sales_with_year as select * from sales where in_year = '2005';
create view y05_sales as select customer, amount from y05_sales_with_year;
* * * \g
Executing . . .

continue
* create procedure y05_sales_with_year_insert_proc(INOUT in_year int2) as begin in_year=2005; end ;
* \g
Executing . . .

continue
* create rule y05_sales_with_year_rule
  before insert on y05_sales_with_year
  execute procedure y05_sales_with_year_insert_proc(in_year = new.in_year);

* * * * \g
Executing . . .

continue
* insert into y05_sales values ('Hank', 22.22);
* \g
Executing . . .

(1 row)
continue
* select * from sales;
* \g
Executing . . .


+--------------------+------------+------+
|customer            |amount      |in_yea|
+--------------------+------------+------+
|Hank                |       22.22|  2005|
+--------------------+------------+------+
(1 row)
continue
* select * from y05_sales;
* \g
Executing . . .


+--------------------+------------+
|customer            |amount      |
+--------------------+------------+
|Hank                |       22.22|
+--------------------+------------+
(1 row)
continue
* 

Tell me what you think.

Cheers,

Mike Leo

[Roy Hann]

Mike Leo wrote:

I should have realized that when you wrote that the ABF applications
"need to update" that you were using the word update to mean all of
INSERT, DELETE, and UPDATE, not just UPDATE.

What you have done plainly does work. It might be very cool indeed but
I'm going to have to do a bit of reading to convince myself that it
should have worked. Was this an inspired shot in the dark or did you
know it should work?

[Mike Leo]

Roy,

Actually, I thought of doing this a few days ago, but I was
pretty certain you could not put an insert rule on a view.

I even told one of my co-workers that "I wish this was Postgres
so I could put a rule on the view".

Then you said it would be "no problem", and you've never steered me
wrong on this kind of stuff before. So I started trying it, and I ran
into various issues. On a whim I tried to put an insert rule on the
the view. I almost s*i* when it worked!

Then I realized I had to expose the new "in_year" column in the
view, which I didn't like because I was worried about the side-effect
of it appearing in the column list for "select * ...." (Who ever thought
that select * was a good idea? Shoot em!)

So, in an inspired shot in the dark, I created a view on that view
that didn't have the column. And sure enough, it worked slicker than
snot running down the sweater of a 5 year old rink rat.

It is very very cool.

Mikey

[John Smedley]

Just don't read the documentation for CREATE RULE J

rule_name

Specifies the name of the rule. Rules cannot be defined against views, only against base tables.

John

-----Original Message-----
From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Mike Leo
Sent: 20 November 2009 09:08
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Dropping and creating views

On Nov 20, 2009, at 2:41 AM, Roy Hann wrote:

Roy,

Actually, I thought of doing this a few days ago, but I was

pretty certain you could not put an insert rule on a view.

I even told one of my co-workers that "I wish this was Postgres

so I could put a rule on the view".

Then you said it would be "no problem", and you've never steered me

wrong on this kind of stuff before.  So I started trying it, and I ran

into various issues.  On a whim I tried to put an insert rule on the

the view.  I almost s*i* when it worked!

Then I realized I had to expose the new "in_year" column in the

view, which I didn't like because I was worried about the side-effect

of it appearing in the column list for "select * ...." (Who ever thought

that select * was a good idea?  Shoot em!)

So, in an inspired shot in the dark, I created a view on that view

that didn't have the column.  And sure enough, it worked slicker than

snot running down the sweater of a 5 year old rink rat.

It is very very cool.

Mikey

[Mike Leo]

I did read that last week.  But I've never trusted documentation.

;-)

On Nov 20, 2009, at 3:23 AM, John Smedley wrote:

Just don't read the documentation for CREATE RULE J

rule_name




Specifies the name of the rule. Rules cannot be defined against views, only against base tables.

John

-----Original Message-----
From: info-ingr...@kettleriverconsulting.com [mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Mike Leo
Sent: 20 November 2009 09:08
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Dropping and creating views

On Nov 20, 2009, at 2:41 AM, Roy Hann wrote:

Roy,

[Roy Hann]

Mike Leo wrote:

> Actually, I thought of doing this a few days ago, but I was
> pretty certain you could not put an insert rule on a view.

That is exactly the bit that bothers me.

> I even told one of my co-workers that "I wish this was Postgres
> so I could put a rule on the view".
>
> Then you said it would be "no problem", and you've never steered me
> wrong on this kind of stuff before. So I started trying it, and I ran
> into various issues. On a whim I tried to put an insert rule on the
> the view. I almost s*i* when it worked!
>
> Then I realized I had to expose the new "in_year" column in the
> view, which I didn't like because I was worried about the side-effect
> of it appearing in the column list for "select * ...." (Who ever thought
> that select * was a good idea? Shoot em!)
>
> So, in an inspired shot in the dark, I created a view on that view
> that didn't have the column. And sure enough, it worked slicker than
> snot running down the sweater of a 5 year old rink rat.

That part I would have expected would work (or hoped, I should say)
because the queries get rewritten during parsing to remove references to
the view and replace them with the query that defines the view.

[Roy Hann]

John Smedley wrote:

> Just don't read the documentation for CREATE RULE :-)

>
> rule_name
>
> Specifies the name of the rule. Rules
> cannot be defined against views, only against base tables.

If you don't mind me saying so, removing that restriction is a
rather massive new feature that deserves to get some attention.

Any idea which rev introduced it?

[Paul Mason]

> -----Original Message-----
> From: info-ingr...@kettleriverconsulting.com [mailto:info-
> ingres-...@kettleriverconsulting.com] On Behalf Of Roy Hann
> Sent: 20 November 2009 09:56
> To: info-...@kettleriverconsulting.com
> Subject: Re: [Info-Ingres] Dropping and creating views
>

> John Smedley wrote:
>
> > Just don't read the documentation for CREATE RULE :-)
> >
> > rule_name
> >
> > Specifies the name of the rule. Rules
> > cannot be defined against views, only against base tables.
>
> If you don't mind me saying so, removing that restriction is a
> rather massive new feature that deserves to get some attention.
>
> Any idea which rev introduced it?
>

I just tested it in 2.0 and it worked.

I don't have any easily accessible earlier versions to try.

Paul

[John Smedley]

Hi,

Paul Mason created a rule on a view in Ingres II 2.0 (that was about
1997) and it works

John

-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Roy
Hann
Sent: 20 November 2009 09:56
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Dropping and creating views

John Smedley wrote:

--
Roy

[Roy Hann]

Paul Mason wrote:

>> John Smedley wrote:
>>
>> > Just don't read the documentation for CREATE RULE :-)
>> >
>> > rule_name
>> >
>> > Specifies the name of the rule. Rules
>> > cannot be defined against views, only against base tables.
>>
>> If you don't mind me saying so, removing that restriction is a
>> rather massive new feature that deserves to get some attention.
>>
>> Any idea which rev introduced it?
>>
>
> I just tested it in 2.0 and it worked.

I am flabbergasted.

I hope Mikey and I aren't the only ones who didn't know about this. :-(

[John Smedley]

22-dec-92 (andre) - Added support for creating rules on views

Hum, must have missed that one, I will log a doc bug.

John

-----Original Message-----
From: info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] On Behalf Of Roy
Hann
Sent: 20 November 2009 11:22
To: info-...@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Dropping and creating views

Paul Mason wrote:

I am flabbergasted.

--
Roy

[Roy Hann]

John Smedley wrote:

> 22-dec-92 (andre) - Added support for creating rules on views
>
> Hum, must have missed that one, I will log a doc bug.

Good man.

1992 huh? So it's been possible for longer than I've had access to
what we used to call KME back then. Words fail me. (But I'm smiling.
This is like finding treasure buried in the garden.)