Re: looking for a globally unique row ID

From: Rafal Pietrak <rafal(at)ztk-rp(dot)eu>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: looking for a globally unique row ID
Date: 2017-09-16 17:18:54
Message-ID: 1ed891a6-3edf-d51c-6a76-d0772560b0a9@ztk-rp.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Dear robjsargent(at)gmail(dot)com,

W dniu 16.09.2017 o 17:19, Gmail pisze:
>
>
>> On Sep 16, 2017, at 8:23 AM, Rafal Pietrak <rafal(at)ztk-rp(dot)eu> wrote:
>>
>>
>>
>> W dniu 16.09.2017 o 15:45, Adam Brusselback pisze:
>>> Here is the last discussion I saw on
>>> it: https://www.postgresql.org/message-id/flat/90261791-b731-a516-ab2a-dafb97df4464%40postgrespro(dot)ru#90261791-b731-a516-ab2a-dafb97df4464(at)postgrespro(dot)ru
>>> <https://www.postgresql.org/message-id/flat/90261791-b731-a516-ab2a-dafb97df4464%40postgrespro(dot)ru#90261791-b731-a516-ab2a-dafb97df4464(at)postgrespro(dot)ru>
>>>
>>
>> I can see the discussion is packed with implementation details. That's
>> promising :)
>>
>> Thenx!
>>
>>
> For those of us scoring at home, here's what I have:
> 1 You have a 6-deep hierarchy over 17 document types
> you concede that standard master-detail/inheritance accomplishes what you need w.r.t to documents

I do have 17 "process tables" ... they are "class-B" tables, they DONT
need any hierarchy. One of them contain payment details and has FK do a
document (in one of the 12 tables of "class-A", which are in 6 levels of
hierachy) which this payment covers. They get multiplicated ONLY because
PK in those 12 "class-A" tables must be accessed separately. And those I
have. It goes like this:

CREATE TABLE T1 (id int, b date); -- level 1
CREATE TABLE T2 (c text) INHERITS (T1);
CREATE TABLE T3 (d text) INHERITS (T1);
CREATE TABLE T4 (e text, tm date) INHERITS (T1);
CREATE TABLE T5 (f text) INHERITS (T1); -- level 2
CREATE TABLE T6 (ca text) INHERITS (T2);
CREATE TABLE T7 (db text, db2 text) INHERITS (T3);
CREATE TABLE T8 (ec text, db4 test) INHERITS (T4);
CREATE TABLE T9 (fd text) INHERITS (T5); -- level 3
CREATE TABLE T10 (db11 text) INHERITS (T7); -- level 4

... still counting? And I haven't yet touch any of the 12 leaf tables
I'm using.

> 2 You don't have enough document instances to warrant partitioning

I don't. My couple of thousands of documents is just nothing. I don't
have "enough documents" (that's why I say I can live with a performance
hit). true, true, true.

But I do have enough document-content variety for that. I could collapse
some of the hierarchy at the expense of some columns getting NULL for
certain rows - but that's just nonesens. I'm not doing that.

> 3 Your remaining problem is your workflow

Sorry I don't understand this point.

> 4 You have an academic interest in multi-table indexing
>

Yes. so what?

As nobody have tried to advise me to change my schema, from my point of
view the discussion goes just fine. And I've got some very interesting
answers. Is there a problem with that?

What exactly are you trying to say?

-R

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kim Rose Carlsen 2017-09-16 20:07:07 Remove useless joins (VARCHAR vs TEXT)
Previous Message Gmail 2017-09-16 15:19:28 Re: looking for a globally unique row ID