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

[Gmail]

> On Sep 16, 2017, at 11:18 AM, Rafal Pietrak  wrote:
> 
> Dear robjsarg...@gmail.com,
> 
> W dniu 16.09.2017 o 17:19, Gmail pisze:
>> 
>> 
>>> On Sep 16, 2017, at 8:23 AM, Rafal Pietrak  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-dafb97df4...@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 implementation if referenced for each 
row. Helps in reporting counts as well.   I assume the columns "c text" are 
just placeholders for 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 persistence aspects 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 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.



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

Re: [GENERAL] Remove useless joins (VARCHAR vs TEXT)

[David Rowley]

On 17 September 2017 at 08:07, Kim Rose Carlsen  wrote:
> It seems there are some difference in VARCHAR vs TEXT when postgres tries to
> decide if a LEFT JOIN is useful or not. I can't figure out if this is
> intentional because there are some difference between TEXT and VARCHAR that
> I dont know about or if it's a bug.
>
>
> I would expect both examples to produce same query plan
>
>
> a)
>
> create table a (id varchar primary key);
> create table b (id varchar primary key);
>
> explain   select a.*
>  from a
> left join (select distinct id from b) as b
>on a.id = b.id;
>
>
> QUERY PLAN
> --
>  Hash Right Join  (cost=67.60..113.50 rows=1360 width=32)
>Hash Cond: ((b.id)::text = (a.id)::text)
>->  HashAggregate  (cost=27.00..40.60 rows=1360 width=32)
>  Group Key: b.id
>  ->  Seq Scan on b  (cost=0.00..23.60 rows=1360 width=32)
>->  Hash  (cost=23.60..23.60 rows=1360 width=32)
>  ->  Seq Scan on a  (cost=0.00..23.60 rows=1360 width=32)
> (7 rows)
>
> b)
>
> create table a (id text primary key);
>
> create table b (id text primary key);
>
> explain   select a.*
>  from a
> left join (select distinct id from b) as b
>on a.id = b.id;
>
>   QUERY PLAN
> --
>  Seq Scan on a  (cost=0.00..23.60 rows=1360 width=32)

Yeah, it looks like the code to check for distinctness in the subquery
fails to consider that the join condition may contain RelabelTypes
instead of plain Vars.

The join would be removed if you'd written:

explain select a.* from a left join b on a.id = b.id;

so really the subquery version should be too.

I'm undecided if this should be classed as a bug or just a missed
optimisation. Certainly, the original code should have done this, so
I'm leaning slightly towards this being a bug.

The attached fixes.

(CC'd -hackers since we're starting to discuss code changes. Further
discussion which includes -hackers should drop the general list)

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


join_removal_subquery_fix.patch
Description: Binary data

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

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

[rob stone]

On Sat, 2017-09-16 at 19:18 +0200, Rafal Pietrak wrote:
> Dear robjsarg...@gmail.com,
> 
> 
> 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:
> 
> 
Hello Rafal,


I've been trying to follow this discussion but now I'm totally
confused. (Some people might say that this is my normal state.)

However, what do you mean by the following:-

17 "process tables"?

multiplicated -- does this mean replicated?

any of the 12 leaf tables I'm using  -- what is a "leaf" table?

collapse some of the hierarchy at the expense of some columns getting
NULL for certain rows  --  does this mean if you have two input fields
(field A and field B) that if field A is not null and field B is null
the data is inserted into one table and if it's the inverse you insert
into an entirely different table?



IMHO, you need an UML diagram that not only sets out your workflow but
will also provide the basis for your schema.
Keep in mind that a hierarchy can be 'n' tables deep. The foreign keys
point back upwards until you finally reach the parent.

You mention payments being made. Users make mistakes. They can post a
payment to the wrong account and later it has to be reversed. These
things can be modelled via your UML diagram.

Cheers,
Rob


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

[GENERAL] Remove useless joins (VARCHAR vs TEXT)

[Kim Rose Carlsen]

Hi


It seems there are some difference in VARCHAR vs TEXT when postgres tries to 
decide if a LEFT JOIN is useful or not. I can't figure out if this is 
intentional because there are some difference between TEXT and VARCHAR that I 
dont know about or if it's a bug.


I would expect both examples to produce same query plan


a)

create table a (id varchar primary key);
create table b (id varchar primary key);

explain   select a.*
 from a
left join (select distinct id from b) as b
   on a.id = b.id;


QUERY PLAN
--
 Hash Right Join  (cost=67.60..113.50 rows=1360 width=32)
   Hash Cond: ((b.id)::text = (a.id)::text)
   ->  HashAggregate  (cost=27.00..40.60 rows=1360 width=32)
 Group Key: b.id
 ->  Seq Scan on b  (cost=0.00..23.60 rows=1360 width=32)
   ->  Hash  (cost=23.60..23.60 rows=1360 width=32)
 ->  Seq Scan on a  (cost=0.00..23.60 rows=1360 width=32)
(7 rows)


b)

create table a (id text primary key);

create table b (id text primary key);

explain   select a.*
 from a
left join (select distinct id from b) as b
   on a.id = b.id;

  QUERY PLAN
--
 Seq Scan on a  (cost=0.00..23.60 rows=1360 width=32)


- Kim Carlsen

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

[Rafal Pietrak]

Dear robjsarg...@gmail.com,

W dniu 16.09.2017 o 17:19, Gmail pisze:
> 
> 
>> On Sep 16, 2017, at 8:23 AM, Rafal Pietrak  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-dafb97df4...@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

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


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

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

[Gmail]

> On Sep 16, 2017, at 8:23 AM, Rafal Pietrak  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-dafb97df4...@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
2 You don't have enough document instances to warrant partitioning
3 Your remaining problem is your workflow
4 You have an academic interest in multi-table indexing

Does  this sum up the situation?
Have you shared your current schema?









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

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

[Rafal Pietrak]

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-dafb97df4...@postgrespro.ru
> 
> 

I can see the discussion is packed with implementation details. That's
promising :)

Thenx!

-R


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

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

[Adam Brusselback]

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-dafb97df4...@postgrespro.ru

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

[Rafal Pietrak]

W dniu 16.09.2017 o 12:43, Tomas Vondra pisze:
> On 09/16/2017 11:57 AM, Rafal Pietrak wrote:
>>
>> 
>>  I'M NOT POSTGRES HACKER. THIS IS JUST NA INTUITION.
>> 
>>
>> W dniu 15.09.2017 o 21:30, Christopher Browne pisze:
>>> On 15 September 2017 at 14:45, Adam Brusselback
>>>  wrote:
[---]
>> This is probably postgresql-hackers knowledge, but I'd appreciate if
>> you elaborate: why "concurrent vacuum" of two table with common index
>> is such a problem?
>>
> 
> Because it means vacuums on partitions will clash on the shared (global)
> index, unlike today with only local indexes. Vacuum happens in about
> three basic phases:
> 
> 1) Remove dead tuples from tables, reduce them to item pointers and
> stash the row IDs in a list.
> 
> 2) Scan all indexes on the table, remove items with IDs on the list.
> 
> 3) Scan the table again, finally removing the item pointers.
> 
> Currently this happens on partition level, so the individual vacuums
> don't interfere by modifying the same index. With global indexes (at
> least when implemented as a single object) that would no longer be the
> case, and the consequences are a bit terrifying ...

This looks hard. True.

I'm wondering if the "global index" problem was deeply discussed before
(like on postgres-hackers) ... or discussions died out early due to all
this implementation obstacles?

In the later case, I'd like to tackle (shake) it a little here.
Obviously not to the point of implementation, but may be just to file
some pros and cons of one way or another to implement it.

So regarding the 1/2/3 above. Is it feasible (?) would it work (?) if:
1. concurrent vacuum is executed concurrently like today.
2. obviously all those descendent & being vacuumed tables will get a
lock by the vacuum process.
3. global index is at this point "opened", so access to other
descendents proceed?
4. the global index may experience some locking due to the above "other
descendent tables" actions, but tuple pointers relating to tables
currently being vacuumed are not effected no need to lock??
5. the concurrent processed doing (1) above may take long, but eventualy
they finish and all the collected lists of tuples for the "global index"
to purge are ready.
6. now the process of vacuuming the global index may proceed, as only
this index need updates, and it shouldn't take long - all the work
downstairs is done. Thus the global index will only get blocked for a
minimum amount of time.


[---]
> 
> It's a bit foolish to say you would live with the performance hit when
> you don't know how serious would it be. It may be fine but it may also
> be much worse than you expected.

very true. I admit.

But on the other hand, today you have a situation, without any tools for
such occasion. IMHO that's worse.

And with thousands of partitions we are talking billions of records.
This is not an average Joe's (like myself) scenario. Event if my 6
levels of inheritance rise 10-fold (which is very unlikely and on the
other hand ... very large), I'm way away from thousands.


> 
> I ran into a number of people who used global indexes on other DBs, and
> their position is mostly "I'll never use that, because it pretty much
> eliminates all benefits of partitioning." So we need to thread pretty
> carefully here, not to run into the same limitations.

True again.

And again, there should be a way to do it right :)


-R


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

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

[Tomas Vondra]

On 09/16/2017 11:57 AM, Rafal Pietrak wrote:
> 
> 
>   I'M NOT POSTGRES HACKER. THIS IS JUST NA INTUITION.
> 
> 
> W dniu 15.09.2017 o 21:30, Christopher Browne pisze:
>> On 15 September 2017 at 14:45, Adam Brusselback
>>  wrote:
> 
> [---]
>>
>> b) Referencing which index entries can be dropped (e.g. - vacuumed out)
>> is a fair bit more complex because the index entries depend on multiple
>> tables.  This adds, um, concurrency complications, if data is being deleted
>> from multiple tables concurrently.  (Over-simplifying question:  "When
>> a table that participates in the sharing is vacuumed, does the shared
>> index get vacuumed?  What if two such tables are vacuumed concurrently?")
> 
> This is probably postgresql-hackers knowledge, but I'd appreciate if
> you elaborate: why "concurrent vacuum" of two table with common index
> is such a problem?
> 

Because it means vacuums on partitions will clash on the shared (global)
index, unlike today with only local indexes. Vacuum happens in about
three basic phases:

1) Remove dead tuples from tables, reduce them to item pointers and
stash the row IDs in a list.

2) Scan all indexes on the table, remove items with IDs on the list.

3) Scan the table again, finally removing the item pointers.

Currently this happens on partition level, so the individual vacuums
don't interfere by modifying the same index. With global indexes (at
least when implemented as a single object) that would no longer be the
case, and the consequences are a bit terrifying ...

> 1. why cannot it be "serialized on demand" in such case/exception
> (e.i the case of tables being bond by a common index)? In other
> words, can the initial concurrency be turned into serialized
> commands?
> 

It can. But serialization is pretty much the arch enemy of scalability.
Amdahl's law and all that ...

What's particularly bad on the vacuum serialization is that it does not
serialize client requests but maintenance tasks. That's quite dangerous
IMNSHO, as it may easily result in bloat explosion.

> 2. why common index cannot be implemented as "split files" - locking 
> with their respective tables the usual way? The problem of
> concurrent locking would vanish at the expense of performance hit ...
> but if someone desperately needs such "global index", I'd bet he/she
> will live with performance hit. I would.
> 

It can be implemented by split files. But that once again introduces
massive locking (imagine hundreds or thousands of partitions), and thus
overhead.

It's a bit foolish to say you would live with the performance hit when
you don't know how serious would it be. It may be fine but it may also
be much worse than you expected.

I ran into a number of people who used global indexes on other DBs, and
their position is mostly "I'll never use that, because it pretty much
eliminates all benefits of partitioning." So we need to thread pretty
carefully here, not to run into the same limitations.

regards

-- 
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

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

[Rafal Pietrak]

W dniu 16.09.2017 o 09:33, Jehan-Guillaume de Rorthais pisze:
> 
[-]
> 
> I wrote something about this some years ago, this might do the trick for you, 
> maybe with some adjustments depending on your schema. The main idea should 
> help anyway. See:
> 
> http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html
> 

For the document management schema I need a way to avoid partitioning of
"process-tables", and still be able to FK from it into the document
class forest of inherited tables. Just uniqueness of IDs across that
forest is not enough ... although nice to have, I admit. Good & handy
article. I'll keep a reference to it.

Thenx,

-R


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

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

[Rafal Pietrak]

I'M NOT POSTGRES HACKER. THIS IS JUST NA INTUITION.


W dniu 15.09.2017 o 21:30, Christopher Browne pisze:
> On 15 September 2017 at 14:45, Adam Brusselback
>  wrote:

[---]
> 
> b) Referencing which index entries can be dropped (e.g. - vacuumed out)
> is a fair bit more complex because the index entries depend on multiple
> tables.  This adds, um, concurrency complications, if data is being deleted
> from multiple tables concurrently.  (Over-simplifying question:  "When
> a table that participates in the sharing is vacuumed, does the shared
> index get vacuumed?  What if two such tables are vacuumed concurrently?")

This is probably postgresql-hackers knowledge, but I'd appreciate if you
elaborate: why "concurrent vacuum" of two table with common index is
such a problem?

1. why cannot it be "serialized on demand" in such case/exception (e.i
the case of tables being bond by a common index)? In other words, can
the initial concurrency be turned into serialized commands?

2. why common index cannot be implemented as "split files" - locking
with their respective tables the usual way? The problem of concurrent
locking would vanish at the expense of performance hit ... but if
someone desperately needs such "global index", I'd bet he/she will live
with performance hit. I would.

-R


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

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

[Rafal Pietrak]

W dniu 15.09.2017 o 21:30, Christopher Browne pisze:
> On 15 September 2017 at 14:45, Adam Brusselback
>  wrote:
>>> I cannot image a single postgres index covering more than one physical
>>> table. Are you really asking for that?
>>
>>
>> While not available yet, that is a feature that has had discussion before.
>> Global indexes are what i've seen it called in those discussions.  One of
>> the main use cases is to provide uniqueness across multiple tables, which
>> would also allow things like foreign keys on partitioned tables.
> 
> It certainly does come up periodically; it seems like a challengingly 
> different
> thing to implement (as compared to "regular" indexes), from two perspectives:
> 
> a) The present index implementation only needs to reference tuples from one
> table, so the tuple references can be direct heap references.
> 
> If multiple tables (partitions being the most obvious case) were to be 
> covered,
> each index entry would also require indication of which table it comes from.
> 
> b) Referencing which index entries can be dropped (e.g. - vacuumed out)
> is a fair bit more complex because the index entries depend on multiple
> tables.  This adds, um, concurrency complications, if data is being deleted
> from multiple tables concurrently.  (Over-simplifying question:  "When
> a table that participates in the sharing is vacuumed, does the shared
> index get vacuumed?  What if two such tables are vacuumed concurrently?")
> 
> This has added up to make it not an easy thing to implement.
> 
> To be sure, if a shared index required greatly worsened locking to do
> maintenance, or suffered from inability to keep it tidy, that would make the
> feature of rather less interest...
> 

I feel obliged to add my one final comment:

If the above rocks and logs in the way, so if all this is to be
implemented by an SQL level programmer as functions  when
application level decisions requires it (say from the lack of
imagination or knowledge of SQL/RDBM proper designs), that SQL level
programmer will have to code most of those pitfalls at higher level -
most probably incompletely. (I'm in particular not able to pinpoint all
the cases that needs to be covered - like by triggers - to have the
global uniqueness checked in all the corner cases scenarios).

But following the signature remark of the email I'm currently replaying
to: is there a way to partition the problem?


-R


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

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

[Jehan-Guillaume de Rorthais]

Le 14 septembre 2017 19:11:19 GMT+02:00, Rafal Pietrak  a 
écrit :
>
>
>W dniu 14.09.2017 o 15:54, Merlin Moncure pisze:
>> On Thu, Sep 14, 2017 at 2:45 AM, Rafal Pietrak 
>wrote:
>>> Hello everybody,
>>>
>>> Can anybody help me find a way to implement an ID which:
>>>
>>> 1. guarantees being unique across multiple tables.
>>>
>>> 2. guarantees its uniqueness not only during INSERT, but also during
>the
>>> lifetime of the database/application (e.i. during future UPDATES).
>>>
>>> 3. guarantees persistence of value across database
>backup/restore/upgrade.
>>>
>>> an obvious candidate - a single SERIAL() (same serial) used in every
>>> table that needs that ID does not guarantee (2).
>> 
>> A shared sequence meets all of those requirements.  I tend to prefer
>
>Not really.
>
>As I said, I'm not looking for performance or "fair probability" of
>planetary-wide uniqueness.
>
>My main objective is the "guarantee". Which I've tried to indicate
>referring to "future UPDATEs".
>
>What I mean here is functionality similar to "primary key", or "unique
>constraint". Whenever somebody (application, like faulty application
>IMPORTANT!) tries to INSERT or UPDATE a not unique value there (which
>in
>fact could possibly be generated earlier by UUID algorithms, or even a
>sequence), if that value is among table that uses that (misterious)
>"global primary key"; that application just fails the transaction like
>any other "not unique" constraint failing.

I wrote something about this some years ago, this might do the trick for you, 
maybe with some adjustments depending on your schema. The main idea should help 
anyway. See:

http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html

(no, I never wrote the second part about fk :-/) 



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