Re: [GENERAL] Partitioning and constraint exclusion

[Sylvain Rabot]

On Tue, 2011-06-21 at 12:25 -0400, Tom Lane wrote:
> Sylvain Rabot  writes:
> > On Postgres 9.1beta2 when i run this code the first select will use
> > contraint exclusion but the second will not.
> > This apparently has something to do with the size of the array
> > returned by the fake immutable function.
> 
> See predtest.c:
> 
> /*
>  * Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
>  * likely to require O(N^2) time, and more often than not fail anyway.
>  * So we set an arbitrary limit on the number of array elements that
>  * we will allow to be treated as an AND or OR clause.
>  * XXX is it worth exposing this as a GUC knob?
>  */
> #define MAX_SAOP_ARRAY_SIZE   100
> 
> While you could possibly increase that, I think that your approach is
> bound to have terrible performance anyway.

Indeed, data is going to store millions of records for possibly tens of
thousands different id_users per partitions.

I tried not using array with :

CREATE OR REPLACE FUNCTION data_users(
in_data text,
in_id_user  integer
) RETURNS integer[] AS $__$

BEGIN
PERFORM data INTO v_return FROM data_partitioning WHERE data = in_data AND 
users @> ARRAY[id_id_user];

IF FOUND THEN
RETURN in_id_user;
ELSE
RETURN -1;
END IF;
END;

$__$ LANGUAGE plpgsql IMMUTABLE;

CREATE TABLE data_1 (
CHECK (id_user = data_users('data_1', id_user))
) inherits (data);

CREATE TABLE data_2 (
CHECK (id_user = data_users('data_2', id_user))
) inherits (data);

But constraint exclusion is not working. It seems that I can't use
id_user in both side of the expression.

Is there a way to use constraint exclusion with dynamic partitioning and
constraint more complex than basic range check ?

> 
>   regards, tom lane
> 




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Partitioning and constraint exclusion

[Sylvain Rabot]

Hi list,

I'm trying to do some partitioning on a table but I have a problem
with constraint exclusion.

Here the following code : https://gist.github.com/1038133

(this code is just an example to explain the problem)

As you can see I declare a fake immutable function in order to make
constraint exclusion work even
if the function is not immutable at all.

On Postgres 9.1beta2 when i run this code the first select will use
contraint exclusion but the second will not.
This apparently has something to do with the size of the array
returned by the fake immutable function.

Is there a way to force the use of constraint exclusion no matter the
size of the array used in the check constraint ?

Regards.

-- 
Sylvain

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Maintenance commands on standby servers

[Sylvain Rabot]

On Tue, 2011-02-08 at 21:05 +0900, Fujii Masao wrote:
> On Tue, Feb 8, 2011 at 4:04 AM, Sylvain Rabot  wrote:
> > Is it possible to run maintenance commands like ANALYZE, VACUUM, CLUSTER
> > on a standby server ?
> 
> No.
> 
> Since the effect of the maintenance command on the primary server
> is also replicated, you don't need to do that on the standby.
> 
> Regards,
> 

Thank you for your explanation.

-- 
Sylvain Rabot 


signature.asc
Description: This is a digitally signed message part

[GENERAL] Maintenance commands on standby servers

[Sylvain Rabot]

Hi,

Is it possible to run maintenance commands like ANALYZE, VACUUM, CLUSTER
on a standby server ?

The documentations says that those operations can not be done in
recovery mode, but that "some of these commands are actually allowed
during "read only" mode transactions on the primary" [1].

[1]
http://www.postgresql.org/docs/9.0/static/hot-standby.html#HOT-STANDBY-ADMIN

Regards.

-- 
Sylvain Rabot 


signature.asc
Description: This is a digitally signed message part