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

Hi,

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

Regards
Dave

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.

Been there, done that! ;-p
(see page 38 of our original Case Study at
http://www.iua.org.uk/conference/Aut...06.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.

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

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

rthdavid wrote:

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

--
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.

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

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

On Nov 18, 2009, at 6:57 AM, Karl Schendel wrote:

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

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 <mleo (AT) kettleriverconsulting (DOT) com>



--
Peter Gale
pgale61 (AT) gmail (DOT) com

On Nov 19, 2009, at 3:19 PM, Mike Leo wrote:
Not at all. I have seen something similar done elsewhere,
although I can't recall the details any more.

Karl