immutable function querying table for partitioning - Mailing list pgsql-general
| From | Vijaykumar Jain |
|---|---|
| Subject | immutable function querying table for partitioning |
| Date | |
| Msg-id | CAM+6J965=DUVtouJeBoRg7mjeU8Fmw_1XxVzE5TK4MFbn2O_Sg@mail.gmail.com Whole thread Raw |
| Responses |
Re: immutable function querying table for partitioning
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| List | pgsql-general |
hi,
"IMMUTABLE indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value."
"IMMUTABLE must be a pure function, whose results depend only on its inputs. This is a very strict requirement; they cannot call other non-immutable functions, they cannot access tables, they cannot access the value of configuration properties, etc"
--
I was playing around with a setup of having a lookup table for partitioning.
Basically, I wanted to be able to rebalance partitions based on my lookup table.
-- create a lookup and assign shard nos to ids
test=# create table pt(id int, sh int);
CREATE TABLE
test=# insert into pt select x, 1 from generate_series(1, 10) x;
INSERT 0 10
test=# insert into pt select x, 2 from generate_series(10, 20) x;
INSERT 0 11
test=# insert into pt select x, 3 from generate_series(20, 30) x;
INSERT 0 11
test=# table pt;
id | sh
----+----
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1
6 | 1
7 | 1
8 | 1
9 | 1
10 | 1
10 | 2
11 | 2
12 | 2
13 | 2
14 | 2
15 | 2
16 | 2
17 | 2
18 | 2
19 | 2
20 | 2
20 | 3
21 | 3
22 | 3
23 | 3
24 | 3
25 | 3
26 | 3
27 | 3
28 | 3
29 | 3
30 | 3
(32 rows)
-- create a function that would lookup the id and return sh number
test=# create or replace function get_shard(t_id int) returns int as $$
test$# begin
test$# declare shno int;
test$# select distinct sh into shno from pt where id = t_id;
test$# return shno;
test$# end; $$ language plpgsql;
CREATE FUNCTION
test=# select get_shard(10);
get_shard
-----------
1
(1 row)
test=# select get_shard(30);
get_shard
-----------
3
(1 row)
-- ok it seems the function has to be immutable, so i am just making it one
-- despite querying a lookup table that would have contents changed later
test=# create table t (id int) partition by list ( get_shard(id) );
ERROR: functions in partition key expression must be marked IMMUTABLE
test=# create or replace function get_shard(t_id int) returns int as $$
declare shno int;
begin
select distinct sh into shno from pt where id = t_id;
return shno;
end; $$ language plpgsql IMMUTABLE;
CREATE FUNCTION
-- partition table using the lookup function
test=# create table t (id int) partition by list ( get_shard(id) );
CREATE TABLE
-- if function returns 1 then partition t1 and so on
test=# create table t1 partition of t for values in ( 1 );
CREATE TABLE
test=# create table t2 partition of t for values in ( 2 );
CREATE TABLE
test=# create table t3 partition of t for values in ( 3 );
CREATE TABLE
-- validate partitioning is working by inserting data
test=# insert into t select x from generate_series(1, 25) x;
INSERT 0 25
test=# \dt t1
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
test=# table t1
test-# ;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
test=# table t1;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
test=# table t2;
id
----
11
12
13
14
15
16
17
18
19
20
(10 rows)
test=# table t3;
id
----
21
22
23
24
25
(5 rows)
-- now we want to move all data from sh=3 to sh=2, so we update lookup table
test=# table pt;
id | sh
----+----
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1
6 | 1
7 | 1
8 | 1
9 | 1
10 | 1
10 | 2
11 | 2
12 | 2
13 | 2
14 | 2
15 | 2
16 | 2
17 | 2
18 | 2
19 | 2
20 | 2
20 | 3
21 | 3
22 | 3
23 | 3
24 | 3
25 | 3
26 | 3
27 | 3
28 | 3
29 | 3
30 | 3
(32 rows)
test=# update pt set sh = 2 where sh = 3;
UPDATE 11
--- now since the lookup table is update, a noop update would get new shards for ids and rebalance them accordingly.
test=# update t set id = id ;
UPDATE 25
-- validate tupes moved to respective new shards.
test=# table t1;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
test=# table t2;
id
----
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
(15 rows)
test=# table t3;
id
----
(0 rows)
now,
"IMMUTABLE must be a pure function, whose results depend only on its inputs. This is a very strict requirement; they cannot call other non-immutable functions, they cannot access tables, they cannot access the value of configuration properties, etc"
I am making a table query in an immutable function, which is used for partitioning.
although it does not result in error and it works, what is it that might cause trouble.
ignore the performance issues, i understand having an access exclusive table lock on pt would block partition forever.
Also, I plan to update shard lookup only when there are not DML on partition tables.
What am I doing wrong.
ignore, as always, if it does not make sense :)
Thanks,
Vijay
Mumbai, India
pgsql-general by date: