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

W dniu 17.09.2017 o 03:30, Gmail pisze:
> 
> 
>> 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:

[---------------------]
>> 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?

Pls pls pls. Don't. This leads nowhere.

What if I say "yes" ... but for the same question regarding T3 you would
get: "no".

This is just and indication how incorrect your "counting at home" is.

> 
> 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
exactimplementation if referenced for each row. Helps in reporting counts as well.   I assume the columns "c text" are
justplaceholders for discussion, not the actual structure.   
 

YES!

>> ... 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.
 

I've been there ... and back. If I may, I'd like to avoid any discussion
of whether my schema is sound or it stinks.

As you may have noticed, I've put significant effort to focus the
discussion on my actual question: the "global index" (which btw I didn't
know is called this way here - if I new, I'd probably could have google
it instead). This was intentional. I like my schema design very much and
I'm unwilling to part with it.

>>
>>> 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.
 

Hmmm... still not so clear. But I'll try:

1. I don't really have problems with "persistence aspects ... of what
happens". This I've implemented by copying the inheritance structure of
"class-A" tables into "class-B" tables. "persistence" is kept in order
by numerous FKs.

2. I do have a problem, that this results in my schema "being larger
then expected" ... but that's just a nuisance - the developer of new
feature (e.i myself) has more headaches then it should.

If that's what you asked for, so the answer is: "No I don't have a
problem with my workflow".

>>
>>> 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 
 

;7

no, it doesn't.

T1 is empty. It's just a head of inheritance tree.

There is no guarantee (index on T1 will have no entries). But naturally
there are ways to "smartly" partition the ID space allocated to
subtables of T1.

Only I was asking for database tools to GUARANTEE that. And across of
future UPDATEs too (not just INSERTS).

need to be under the same unique ID space (though personally I'm a fan
of UUID anyway).  I'm not seeing where you would benefit from the title
of this thread.

To some extend, you are right here.

And missing the "global unique ID" (as per subject of the posted
question) I'm currently using "a sort of" this king of workaround. And
in fact one of the responses in this thread showed me a better way to
implement that workaround - this is really, really good.

But regretably it doesn't help me avoid the explosion of the number of
required "class-B" tables. There is no way to make FK from those
"class-B" tables into subtables of T1 ... no matter how smart I'll be in
partitioning the ID space among them.

-R


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