Re: Dynamic table names and DBIx::Class

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?

Comment on Re: Dynamic table names and DBIx::Class

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

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]