Dynamic table names and DBIx::Class

einhverfr has asked for the wisdom of the Perl Monks concerning the following question:

This is not a normal situation but I am working with a partitioned table and would like to route selects against the parent table to the partitions directly (in order to avoid the db planner requiring access to the other tables for constraint exclusion reasons.

Ideally primary key would have a field in a multi-column pkey, something like "partion_id" and the table name would be something like "mytable_" . $self->partition_id.

What is the best way to accomplish this?

Edit: Selecting on the main table works from an application perspective but it has unpleasant effects on database administration tasks (ones which lock any underlying tables block *all* queries on the parent table) and one can't just use a view because the view doesn't get to decide without doing exactly what the current query is doing (a union through all with constraint exclusion). Subclassing isn't an option because this is a component of a complex system and subclassing for every partition will get ridiculous quickly. So I am looking to see if there is a way I can tie the table to the object instead of the package.

Comment on Dynamic table names and DBIx::Class

Replies are listed 'Best First'.
Re: Dynamic table names and DBIx::Class by erix (Prior) on Jun 25, 2015 at 21:41 UTC
In postgres you should be able to just access the 'underlying' partitions, I think. But the whole thing sounds strange: the planner 'requiring' access to other tables/partitions? Why is that a problem? Could you explain a a little more? Could you say which database system you use?	[reply]
Re^2: Dynamic table names and DBIx::Class by einhverfr (Friar) on Jun 26, 2015 at 06:45 UTC
The problem is that the planner has to get an access share lock on all partitions in order to determine whether constraint exclusion applies. This means if you cluster or reindex a table, it blocks access to the partition set. So that's what I am trying to avoid. It is PostgreSQL. This is wandering out of the Perl side but what happens is that a select on the parent table (if not using from only) requires access share locks on all partitions so that the planner can determine whether constraint exclusion applies. This makes sense: if you are running an alter table, the planner should probably wait and see whether constraint exclusion really applies. But it also means that many maintenance operations can't work while you have open transactions that have ever touched a partition (even for planning). The table was partitioned because different partitions have different distributions of data and this allows better planning of queries but now we still can't do db maintenance as we'd like to unless we can solve this problem and it looks like the easiest way to do that is to move the exclusion into the query by manually selecting the underlying table.	[reply]
Re: Dynamic table names and DBIx::Class by GotToBTru (Prior) on Jun 25, 2015 at 20:54 UTC
Does your database even support such a thing? In my experience, partitioning is implemented in the DB such that queries are written exactly the same for partitioned and non-partitioned tables. Even if you're willing to hack DBI, access to the individual partitions is not available, or certainly discouraged. Dum Spiro Spero	[reply]
Re^2: Dynamic table names and DBIx::Class by einhverfr (Friar) on Jun 26, 2015 at 06:48 UTC
The DB (PostgreSQL) does support it. And in fact I have moved a fair bit of the code to a module that doesn't go through DBIx::Class just to avoid this. But DBIx::Class calls still block when an unused partition is locked and that's what I am trying to avoid.	[reply]