[MDEV-14005] Remove need for Partition Key to be part of Primary Key Created: 2017-10-04 Updated: 2022-04-25
Status:	Stalled
Project:	MariaDB Server
Component/s:	Partitioning
Fix Version/s:	None

Type:

Task

Priority:

Major

Reporter:

Alvin Richards (Inactive)

Assignee:

Alexey Botchkov

Resolution:

Unresolved

Votes:

Labels:

None

Description

Problem

A Partition key needs to be part of the Primary key, if not then the following error is raised

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Rationale

Other RDBMS (e.g. Oracle) support the ability to create a Partition Key that is not part of the Primary Key.

Use case:
Financial Transactions. Partition by status (not transaction Id), because the business logic is focused on a sub-set of the transaction status (e.g. "In Progress"). For example, and Order could go "Booking" -> "Allocation" -> "Complete". The majority of the data will be in the terminal state of "Complete", but the business processing will be in the "Booking" and "Allocation" states. Partitioning could mean better performance / latency on a smaller sub-set of data.

Workaround

None (system wise). The application logic could be re-coded to migrate data between tables of the various states.

Reproduce

The example below can be reworked for LIST, LIST COLUMNS, RANGE and RANGE COLUMNS. Each will generate the error reported above.

create table foo (

  id int(10),

  status varchar(1),

  CONSTRAINT foo_pk PRIMARY KEY (id)

PARTITION BY LIST COLUMNS (status)

  PARTITION a values in ("A"),

  PARTITION b values in ("B"),

  PARTITION c values in ("C"),

  PARTITION d DEFAULT

);

Solution

Support Partition Keys that are not Primary keys (i.e. remove the current restriction)

Comments

Comment by Alvin Richards (Inactive) [ 2017-10-04 ]

Additional Information

1. Datatype restrictions
Some Partitions types cannot have VARCHAR datatypes as Partition Keys

ERROR 1659 (HY000): Field 'status' is of a not allowed type for this type of partitioning

2. Primary Keys
Primary Key clauses can be omitted from the CREATE TABLE which then allows Partition Keys to be defined on any column

3. Adding Unique Keys
if you a try to add unique keys, then the unique key must include the Partition Key, otherwise you get the error

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

Comment by Sergei Golubchik [ 2017-10-12 ]

The reason for this restriction — it guarantees that two rows from different partitions can never cause unique key conflicts. That is, on any, say, INSERT the engine only needs to ensure uniqueness within a partition, there is no need to search for conflicting values in other partitions.

To remove this restriction, we'll need to implement the search for conflicting values in other partitions.

(note the possible race condition when two threads insert conflicting values in different partitions at about the same time)

Comment by Aurélien LEQUOY [ 2017-10-18 ]

it's funny i sent this problem some years ago too but was closed considering normal behavior .

have to think about spider, because we can push directly in partition.

One nice feature should be to autocreate partition with BY LIST COLUMNS with a max cardinality when this cardinality reached put all new value in DEFAULT.

Comment by Alexey Botchkov [ 2017-12-04 ]

Patch proposal.
It doesn't look for subpartitions and other partitioning types - i'd like to agree the overall solution
http://lists.askmonty.org/pipermail/commits/2017-December/011690.html

Comment by Aurélien LEQUOY [ 2017-12-04 ]

it's will be in version 10.2.12 ?

Comment by Sergei Golubchik [ 2017-12-06 ]

Unlikely, it's a new feature. We cannot add it to a GA release. It'll be done in 10.3

Comment by Sergei Golubchik [ 2017-12-15 ]

holyfoot, where's the patch?

Comment by Alexey Botchkov [ 2018-01-24 ]

Patch proposal
http://lists.askmonty.org/pipermail/commits/2018-January/011877.html

Comment by Eric Herman [ 2018-08-03 ]

Similar to Oracle MySQL bug#29840 "A PRIMARY KEY must include all columns in the table's partitioning function"
https://bugs.mysql.com/bug.php?id=29840

Comment by Alexey Botchkov [ 2018-08-21 ]

http://lists.askmonty.org/pipermail/commits/2018-August/012814.html

Comment by Alexey Botchkov [ 2019-02-03 ]

email 'Re: d1ccc60: MDEV-14005 Remove ne...'