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

W dniu 18.09.2017 o 17:26, Jehan-Guillaume (ioguix) de Rorthais pisze: > On Sat, 16 Sep 2017 12:15:46 +0200 > Rafal Pietrak wrote: > >> W dniu 16.09.2017 o 09:33, Jehan-Guillaume de Rorthais pisze: [-] > > About FK, just look at pgsql trigger code around real FK and adjust to your

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

On Sat, 16 Sep 2017 12:15:46 +0200 Rafal Pietrak wrote: > 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 mai

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

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

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 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: [-] >> PK in those 12 "class-A"

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

W dniu 16.09.2017 o 22:20, rob stone pisze: > > > On Sat, 2017-09-16 at 19:18 +0200, Rafal Pietrak wrote: >> Dear robjsarg...@gmail.com, [-] >> >> > Hello Rafal, Hi, > > > I've been trying to follow this discussion but now I'm totally > confused. (Some people might say that this

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

> 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

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

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", whic

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

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

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

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

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

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 >

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

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

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

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

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 b

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

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

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

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 b

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

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 d

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

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 uni

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

W dniu 15.09.2017 o 20:49, Rob Sargent pisze: > > > On 09/15/2017 12:45 PM, 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 dis

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

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

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

On 09/15/2017 12:45 PM, 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 th

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

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

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

Isn't this typically handled with an inheritance (parent-children) setup. MasterDocument has id, subtype and any common columns (create date etc) then dependents use the same id from master to complete the data for a given type. This is really common in ORM tools. Not clear from the descripti

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

W dniu 15.09.2017 o 13:36, Alban Hertroys pisze: > On 15 September 2017 at 11:03, Rafal Pietrak wrote: > [-] > > For example, if we define: > create table master_table ( > year int > , month int > , example text > , primary key (year, month) > ); > > create child201

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

> > > Hmm... > > 2 4 6 8 10 > > 3 6 > > 5 10 > > Adding a prime as an increment is not sufficient to guarantee uniqueness! > > You have to ensure that the product of the 2 smallest primes you use > is greater than any number you'd need to generate.  With such large > primes you may run out of

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

On 15 September 2017 at 11:03, Rafal Pietrak wrote: >> Isn't this typically handled with an inheritance (parent-children) >> setup. MasterDocument has id, subtype and any common columns (create >> date etc) then dependents use the same id from master to complete the >> data for a given type. Th

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

W dniu 14.09.2017 o 23:15, Rob Sargent pisze: > > > On 09/14/2017 02:39 PM, Rafal Pietrak wrote: >> >> W dniu 14.09.2017 o 19:30, Rob Sargent pisze: >>> >>> On 09/14/2017 11:11 AM, Rafal Pietrak wrote: [--] >> >> Throwing actual numbers: 12 basic classes of documents; 17 tables

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

On Thu, 14 Sep 2017 17:02:05 -0500, Merlin Moncure wrote: >... With sequences, the database *guarantees* that the >identifier is unique with no exceptions; there never will be a unique >value. Can you give a hypothetical example of how you think they >wouldn't work? Jumping in here, but a month

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

On 09/14/2017 03:27 PM, David G. Johnston wrote: On Thu, Sep 14, 2017 at 12: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 uniqu

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

On Thu, Sep 14, 2017 at 12: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/app

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

On Thu, Sep 14, 2017 at 12:11 PM, Rafal Pietrak wrote: > 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 multip

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

On 15/09/17 06:15, Kenneth Marshall wrote: On Thu, Sep 14, 2017 at 07:11:19PM +0200, Rafal Pietrak wrote: 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 UPDATE

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

On Sep 14, Rafal Pietrak modulated: > My main objective is the "guarantee". Which I've tried to indicate > referring to "future UPDATEs". > With a well-behaved application, it is sufficient to define each ID column as: id int8 UNIQUE NOT NULL DEFAULT nextval('sharedsequence') and ensure tha

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

On 09/14/2017 02:39 PM, Rafal Pietrak wrote: W dniu 14.09.2017 o 19:30, Rob Sargent pisze: On 09/14/2017 11:11 AM, Rafal Pietrak wrote: [--] So I'm stuck with seriously incomplete solution. that's why I have an impression, that I'm going into entirely wrong direction here.

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

W dniu 14.09.2017 o 19:30, Rob Sargent pisze: > > > On 09/14/2017 11:11 AM, Rafal Pietrak wrote: >> [--] >> So I'm stuck with seriously incomplete solution. >> >> that's why I have an impression, that I'm going into entirely wrong >> direction here. >> >> > So you care if the s

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

On Thu, Sep 14, 2017 at 07:11:19PM +0200, Rafal Pietrak wrote: > > 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 func

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

On 09/14/2017 11:11 AM, Rafal Pietrak wrote: 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

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

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

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

> On Sep 14, 2017, at 8:38 AM, Karl Czajkowski wrote: > > On Sep 14, vinny modulated: > >> If it is only one database, on one server, then couldn't you just >> use one sequence? >> If oyu prefix the value with some identifier of the current table >> then you cannot get duplicates >> across tabl

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

On Sep 14, vinny modulated: > If it is only one database, on one server, then couldn't you just > use one sequence? > If oyu prefix the value with some identifier of the current table > then you cannot get duplicates > across tables even if you reset the sequence. > I didn't follow the whole thr

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

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

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

On 2017-09-14 15:06, Rafal Pietrak wrote: W dniu 14.09.2017 o 10:57, George Neuner pisze: On Thu, 14 Sep 2017 09:45:59 +0200, 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

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

W dniu 14.09.2017 o 10:57, George Neuner pisze: > On Thu, 14 Sep 2017 09:45:59 +0200, 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 d

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

On Thu, 14 Sep 2017 09:45:59 +0200, 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/applic

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

On Thu, Sep 14, 2017 at 4:45 PM, Rafal Pietrak wrote: > 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 f

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

On 9/14/2017 12:45 AM, Rafal Pietrak wrote: 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.

[GENERAL] looking for a globally unique row ID

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 v