Re: [GENERAL] looking for a globally unique row ID

> On Sep 16, 2017, at 11:18 AM, Rafal Pietrak <rafal@ztk-rp.eu> wrote:
>
> Dear robjsargent@gmail.com,
>
> W dniu 16.09.2017 o 17:19, Gmail pisze:
>>
>>
>>> On Sep 16, 2017, at 8:23 AM, Rafal Pietrak <rafal@ztk-rp.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.ru#90261791-b731-a516-ab2a-dafb97df4464@postgrespro.ru
>>>>
<https://www.postgresql.org/message-id/flat/90261791-b731-a516-ab2a-dafb97df4464%40postgrespro.ru#90261791-b731-a516-ab2a-dafb97df4464@postgrespro.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
>
- quick question: do you have more than one table inheriting from T2?

We all skin cats different ways, of course.
Often with such things, especially with ORM tools, one puts a "type"  field in the master table indicating which exact
implementationif referenced for each row. Helps in reporting counts as well.   I assume the columns "c text" are just
placeholdersfor discussion, not the actual structure.    
> ... 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.
We pick our poisons, I guess.  I sure might be tempted to denormalize some of those if it made life much easier/faster.
Hard to say from what's been presented - still assuming we're seeing pseudo-tables. 
>
>> 3 Your remaining problem is your workflow
>
> Sorry I don't understand this point.

I meant that your document tables are fine (as described above) and that you were still having trouble with the
persistenceaspects of what happens to the documents. 
>
>> 4 You have an academic interest in multi-table indexing
>>
>
> Yes. so what?
>
Nothing.  Just trying to see if I'm following your thread.
> 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
>
All your documents are represented in your "T1" table.  So your processing can always refer to that table - which is
excellent. T1 guarantees unique ids across all other T-tables.  Activity related records have no need to be under the
sameunique ID space (though personally I'm a fan of UUID anyway).  I'm not seeing where you would benefit from the
titleof this thread. 



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