#2 v1.0.1, UNIQUE INDEX

v1.0
closed
nobody
None
5
2012-10-16
2012-10-14
Darkhack
No

I would use dbmail3 with postgresXC
but when I create the base from
dbmail-3.0.2\sql\postgresql\create_tables.pgsql

I have this instruction :
CREATE UNIQUE INDEX dbmail_users_name_idx ON dbmail_users(userid);
with this error :
ERROR: Unique index of partitioned table must contain the hash/modulo distribution column.

have to make some edition for compatibility?
thanks for your answer

Discussion

  • Darkhack
    Darkhack
    2012-10-14

    So I complained, is easy.
    But I thing here you are a very beautiful project.
    congratulation every body.
    you are single.

     
  • Darkhack
    Darkhack
    2012-10-15

    all right,
    after many search, I found some information aboute pg_am table
    with amcanunique parameters.
    I hope to find quickly.

     
  • Darkhack
    Darkhack
    2012-10-15

    some details,
    I have saw pg_am table with amcanunique and amcanmulticol is true
    with btree.

    now we create this table :

    CREATE SEQUENCE dbmail_user_idnr_seq;
    CREATE TABLE dbmail_users (
    user_idnr INT8 DEFAULT nextval('dbmail_user_idnr_seq'),
    userid VARCHAR(100) NOT NULL,
    passwd VARCHAR(130) NOT NULL,
    client_idnr INT8 DEFAULT '0' NOT NULL,
    maxmail_size INT8 DEFAULT '0' NOT NULL,
    curmail_size INT8 DEFAULT '0' NOT NULL,
    maxsieve_size INT8 DEFAULT '0' NOT NULL,
    cursieve_size INT8 DEFAULT '0' NOT NULL,
    encryption_type VARCHAR(20) DEFAULT '' NOT NULL,
    last_login TIMESTAMP DEFAULT '1979-11-03 22:05:58' NOT NULL,
    PRIMARY KEY (user_idnr)
    );
    CREATE UNIQUE INDEX dbmail_users_pkey ON dbmail_users USING btree (user_idnr)

    Or

    CREATE SEQUENCE dbmail_user_idnr_seq;
    CREATE TABLE dbmail_users (
    user_idnr INT8 DEFAULT nextval('dbmail_user_idnr_seq'),
    userid VARCHAR(100) NOT NULL,
    passwd VARCHAR(130) NOT NULL,
    client_idnr INT8 DEFAULT '0' NOT NULL,
    maxmail_size INT8 DEFAULT '0' NOT NULL,
    curmail_size INT8 DEFAULT '0' NOT NULL,
    maxsieve_size INT8 DEFAULT '0' NOT NULL,
    cursieve_size INT8 DEFAULT '0' NOT NULL,
    encryption_type VARCHAR(20) DEFAULT '' NOT NULL,
    last_login TIMESTAMP DEFAULT '1979-11-03 22:05:58' NOT NULL,
    PRIMARY KEY (user_idnr)
    ,UNIQUE (userid)
    );

    this two methode finish with :
    ERROR: Unique index of partitioned table must contain the hash/modulo distribution column

     
  • Darkhack
    Darkhack
    2012-10-15

    begin to have an good answer.
    all right, so by default postgres xc distribute the new value of an row with the hash and modulo
    about the constraint (unique/reference...).
    with that you can have an most powerfull resultat about an synchronisation of node.
    but he can have the support of two differents unique column, and that is logical (so i see an little).
    for that we can create with DISTRIBUTE BY REPLICATION.
    I suppose, you distribute all value of column for the new row before validate the insert... write operation.

    the answer should be :

    CREATE SEQUENCE dbmail_user_idnr_seq;
    CREATE TABLE dbmail_users (
    user_idnr INT8 DEFAULT nextval('dbmail_user_idnr_seq'),
    userid VARCHAR(100) NOT NULL,
    passwd VARCHAR(130) NOT NULL,
    client_idnr INT8 DEFAULT '0' NOT NULL,
    maxmail_size INT8 DEFAULT '0' NOT NULL,
    curmail_size INT8 DEFAULT '0' NOT NULL,
    maxsieve_size INT8 DEFAULT '0' NOT NULL,
    cursieve_size INT8 DEFAULT '0' NOT NULL,
    encryption_type VARCHAR(20) DEFAULT '' NOT NULL,
    last_login TIMESTAMP DEFAULT '1979-11-03 22:05:58' NOT NULL,
    PRIMARY KEY (user_idnr)
    ) DISTRIBUTE BY REPLICATION;
    CREATE UNIQUE INDEX dbmail_users_pkey ON dbmail_users USING btree (user_idnr);
    CREATE UNIQUE INDEX dbmail_users_name_idx ON dbmail_users USING btree (userid);

    and that is work...
    so fine.
    If you can have some comment about this solution/understanding.

     
  • mason_s
    mason_s
    2012-10-15

    A unique index needs to have the distribution column in it, if it is for a distributed table (non-replicated). That is a classic example- wanting a unique user id and unique email address in the same table.

    Michael Paquier is doing work at the moment to support distributed constraints like this, not just distributed unique constraints, but for foreign keys, too.

     
  • Darkhack
    Darkhack
    2012-10-16

    thanks for your answer.
    Now for work with dbmail 3, I have to wait
    the returning clause has been finished.
    I don't know, If it's better to begin to put the hand in the source code of dbmail
    or wait about this returning function?
    So if you want I'm here for test the returning beta function.
    thanks again.

     
  • Darkhack
    Darkhack
    2012-10-16

    • status: open --> closed