Re: [GENERAL] "Keyed" sequence?

2016-04-28 Thread Berend Tober 

Adrian Klaver wrote:

On 04/28/2016 11:48 AM, Israel Brewster wrote:


On Apr 28, 2016, at 10:39 AM, Vik Fearing  wrote:

On 04/28/2016 08:30 PM, Israel Brewster wrote:

This is probably crazy talk, but in Postgresql is there any way to have
a "keyed" sequence? That is, I have a table with a list of departments.
While *relatively* static, technically speaking this is a dynamic
list -
someone certainly could decide to add a department, or combine two
departments into one new one, whatever. In any case, I would ideally
like to have a sequence per department, but since the list is dynamic
it's not as simple as creating 33 independent sequences and then having
a lookup table somewhere, although I guess I could implement something
of the sort with triggers.


What would be the point of this?  Why not just one sequence for all
departments?


continuity and appearance, not to mention simple logical progression.
In this case, the sequence is being used to generate a PO number.
Company style indicates that a PO number is a department code followed
by a unique number. With one sequence for all departments, you could
(will) end up with discontinuous PO numbers in any given department.
It would be nice if, after issuing PO number 15-1, the next PO in
department 15 was 2, if for no other reason than the accounting
department could easily see that they aren't missing any. With one
sequence, there will quite likely not be a PO number 2 for any given
department, so that department has no easy way to keep track of their
PO's based on PO number.


Here is a similar idea:

http://www.postgresql.org/message-id/44e376f6.7010...@seaworthysys.com





BAM!! You beat me to it!!

I have repeated that pattern multiple times and it is the exact use case 
the OP has.





--
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] "Keyed" sequence?

2016-04-28 Thread Israel Brewster 
On Apr 28, 2016, at 11:21 AM, David G. Johnston  
wrote:
> 
> On Thu, Apr 28, 2016 at 12:09 PM, Israel Brewster  > wrote:
> 
> >
> > It'll kill your performance, but if aesthetics are that important to you...
> 
> They're not *that* important. I was just asking if there was a way to do this 
> easily.
> 
> ​While the performance argument might be true it is heavily dependent upon 
> concurrency.  I'm doubting a PO system in the typical company has enough 
> concurrency, and is sensitivity enough to small delays,​ ​that giving up the 
> benefit of sequential numbering would be a worthwhile trade-off.

I'm thinking the same - especially considering that we aren't exactly a huge 
company.

> 
> David J.
> ​

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---

Re: [GENERAL] "Keyed" sequence?

2016-04-28 Thread David G. Johnston 
On Thu, Apr 28, 2016 at 12:09 PM, Israel Brewster 
wrote:

>
> >
> > It'll kill your performance, but if aesthetics are that important to
> you...
>
> They're not *that* important. I was just asking if there was a way to do
> this easily.
>

​While the performance argument might be true it is heavily dependent upon
concurrency.  I'm doubting a PO system in the typical company has enough
concurrency, and is sensitivity enough to small delays,​

​that giving up the benefit of sequential numbering would be a worthwhile
trade-off.

David J.
​

Re: [GENERAL] "Keyed" sequence?

2016-04-28 Thread Israel Brewster 

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---


BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
 

> On Apr 28, 2016, at 11:01 AM, Vik Fearing  wrote:
> 
> On 04/28/2016 08:48 PM, Israel Brewster wrote:
>>> 
>>> On Apr 28, 2016, at 10:39 AM, Vik Fearing  wrote:
>>> 
>>> What would be the point of this?  Why not just one sequence for all
>>> departments?
>> 
>> continuity and appearance, not to mention simple logical progression. In 
>> this case, the sequence is being used to generate a PO number. Company style 
>> indicates that a PO number is a department code followed by a unique number. 
>> With one sequence for all departments, you could (will) end up with 
>> discontinuous PO numbers in any given department. It would be nice if, after 
>> issuing PO number 15-1, the next PO in department 15 was 2, if for no other 
>> reason than the accounting department could easily see that they aren't 
>> missing any. With one sequence, there will quite likely not be a PO number 2 
>> for any given department, so that department has no easy way to keep track 
>> of their PO's based on PO number.
> 
> You're not guaranteed that even with individual sequences.

True, in the event that an insert fails or the like, there would be a gap of 
one number. However, with a single sequence you are guaranteed to have gaps, 
and guaranteed that they will be significant.

> 
> What' you're looking for is a gapless sequence, which is best simulated
> with a table.  In your case, I'd just add a column to your existing
> departments table holding the next number to use.

Yeah, that looks like it could be the way to go. Thanks.

> 
> It'll kill your performance, but if aesthetics are that important to you...

They're not *that* important. I was just asking if there was a way to do this 
easily.

> -- 
> Vik Fearing  +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
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] "Keyed" sequence?

2016-04-28 Thread Andreas Joseph Krogh 
På torsdag 28. april 2016 kl. 20:30:20, skrev Israel Brewster <
isr...@ravnalaska.net >:
This is probably crazy talk, but in Postgresql is there any way to have a 
"keyed" sequence? That is, I have a table with a list of departments. While 
*relatively* static, technically speaking this is a dynamic list - someone 
certainly could decide to add a department, or combine two departments into one 
new one, whatever. In any case, I would ideally like to have a sequence per 
department, but since the list is dynamic it's not as simple as creating 33 
independent sequences and then having a lookup table somewhere, although I 
guess I could implement something of the sort with triggers. 
Is there any elegant way of accomplishing this? Something like SELECT 
nextval('department_seq['ops'])?
 
Maybe this will help you: 
http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
 
Then you can do:
 "id" bigint NOT NULL DEFAULT insta5.next_id(), 
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] "Keyed" sequence?

2016-04-28 Thread Vik Fearing 
On 04/28/2016 08:48 PM, Israel Brewster wrote:
>>
>> On Apr 28, 2016, at 10:39 AM, Vik Fearing  wrote:
>>
>> What would be the point of this?  Why not just one sequence for all
>> departments?
> 
> continuity and appearance, not to mention simple logical progression. In this 
> case, the sequence is being used to generate a PO number. Company style 
> indicates that a PO number is a department code followed by a unique number. 
> With one sequence for all departments, you could (will) end up with 
> discontinuous PO numbers in any given department. It would be nice if, after 
> issuing PO number 15-1, the next PO in department 15 was 2, if for no other 
> reason than the accounting department could easily see that they aren't 
> missing any. With one sequence, there will quite likely not be a PO number 2 
> for any given department, so that department has no easy way to keep track of 
> their PO's based on PO number.

You're not guaranteed that even with individual sequences.

What' you're looking for is a gapless sequence, which is best simulated
with a table.  In your case, I'd just add a column to your existing
departments table holding the next number to use.

It'll kill your performance, but if aesthetics are that important to you...
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


-- 
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] "Keyed" sequence?

2016-04-28 Thread John McKown 
On Thu, Apr 28, 2016 at 1:48 PM, Israel Brewster 
wrote:

> >
> > On Apr 28, 2016, at 10:39 AM, Vik Fearing  wrote:
> >
> > On 04/28/2016 08:30 PM, Israel Brewster wrote:
> >> This is probably crazy talk, but in Postgresql is there any way to have
> >> a "keyed" sequence? That is, I have a table with a list of departments.
> >> While *relatively* static, technically speaking this is a dynamic list -
> >> someone certainly could decide to add a department, or combine two
> >> departments into one new one, whatever. In any case, I would ideally
> >> like to have a sequence per department, but since the list is dynamic
> >> it's not as simple as creating 33 independent sequences and then having
> >> a lookup table somewhere, although I guess I could implement something
> >> of the sort with triggers.
> >
> > What would be the point of this?  Why not just one sequence for all
> > departments?
>
> continuity and appearance, not to mention simple logical progression. In
> this case, the sequence is being used to generate a PO number. Company
> style indicates that a PO number is a department code followed by a unique
> number. With one sequence for all departments, you could (will) end up with
> discontinuous PO numbers in any given department. It would be nice if,
> after issuing PO number 15-1, the next PO in department 15 was 2, if for no
> other reason than the accounting department could easily see that they
> aren't missing any. With one sequence, there will quite likely not be a PO
> number 2 for any given department, so that department has no easy way to
> keep track of their PO's based on PO number.
>

​Have you looked at an UPDATE ... RETURNING ... to generate a new PO
number? I don't know the structure of the table involved, but something
like the following occurs to me:


UPDATE table SET DEPT_LAST_PO = 1 + DEPT_LAST_PO RETURNING DEPT_LAST_PO;

example transcript:

# create table dept (dept_name text, dept_last_po int default 0);
CREATE TABLE
# insert into dept values('finance'); -- default dept_last_po to 0
INSERT 0 1
# update dept set dept_last_po = 1 + dept_last_po where dept_name='finance'
returning dept_last_po;
 dept_last_po
--
1
(1 row)

UPDATE 1


>
> > --
> > Vik Fearing  +33 6 46 75 15 36
> > http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
> >
> >


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown

Re: [GENERAL] "Keyed" sequence?

2016-04-28 Thread Adrian Klaver 

On 04/28/2016 11:48 AM, Israel Brewster wrote:


On Apr 28, 2016, at 10:39 AM, Vik Fearing  wrote:

On 04/28/2016 08:30 PM, Israel Brewster wrote:

This is probably crazy talk, but in Postgresql is there any way to have
a "keyed" sequence? That is, I have a table with a list of departments.
While *relatively* static, technically speaking this is a dynamic list -
someone certainly could decide to add a department, or combine two
departments into one new one, whatever. In any case, I would ideally
like to have a sequence per department, but since the list is dynamic
it's not as simple as creating 33 independent sequences and then having
a lookup table somewhere, although I guess I could implement something
of the sort with triggers.


What would be the point of this?  Why not just one sequence for all
departments?


continuity and appearance, not to mention simple logical progression. In this 
case, the sequence is being used to generate a PO number. Company style 
indicates that a PO number is a department code followed by a unique number. 
With one sequence for all departments, you could (will) end up with 
discontinuous PO numbers in any given department. It would be nice if, after 
issuing PO number 15-1, the next PO in department 15 was 2, if for no other 
reason than the accounting department could easily see that they aren't missing 
any. With one sequence, there will quite likely not be a PO number 2 for any 
given department, so that department has no easy way to keep track of their 
PO's based on PO number.


Here is a similar idea:

http://www.postgresql.org/message-id/44e376f6.7010...@seaworthysys.com




--
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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







--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] "Keyed" sequence?

2016-04-28 Thread Israel Brewster 
> 
> On Apr 28, 2016, at 10:39 AM, Vik Fearing  wrote:
> 
> On 04/28/2016 08:30 PM, Israel Brewster wrote:
>> This is probably crazy talk, but in Postgresql is there any way to have
>> a "keyed" sequence? That is, I have a table with a list of departments.
>> While *relatively* static, technically speaking this is a dynamic list -
>> someone certainly could decide to add a department, or combine two
>> departments into one new one, whatever. In any case, I would ideally
>> like to have a sequence per department, but since the list is dynamic
>> it's not as simple as creating 33 independent sequences and then having
>> a lookup table somewhere, although I guess I could implement something
>> of the sort with triggers.
> 
> What would be the point of this?  Why not just one sequence for all
> departments?

continuity and appearance, not to mention simple logical progression. In this 
case, the sequence is being used to generate a PO number. Company style 
indicates that a PO number is a department code followed by a unique number. 
With one sequence for all departments, you could (will) end up with 
discontinuous PO numbers in any given department. It would be nice if, after 
issuing PO number 15-1, the next PO in department 15 was 2, if for no other 
reason than the accounting department could easily see that they aren't missing 
any. With one sequence, there will quite likely not be a PO number 2 for any 
given department, so that department has no easy way to keep track of their 
PO's based on PO number.

> -- 
> Vik Fearing  +33 6 46 75 15 36
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] "Keyed" sequence?

2016-04-28 Thread Vik Fearing 
On 04/28/2016 08:30 PM, Israel Brewster wrote:
> This is probably crazy talk, but in Postgresql is there any way to have
> a "keyed" sequence? That is, I have a table with a list of departments.
> While *relatively* static, technically speaking this is a dynamic list -
> someone certainly could decide to add a department, or combine two
> departments into one new one, whatever. In any case, I would ideally
> like to have a sequence per department, but since the list is dynamic
> it's not as simple as creating 33 independent sequences and then having
> a lookup table somewhere, although I guess I could implement something
> of the sort with triggers.

What would be the point of this?  Why not just one sequence for all
departments?
-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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

[GENERAL] "Keyed" sequence?

2016-04-28 Thread Israel Brewster 
This is probably crazy talk, but in Postgresql is there any way to have a "keyed" sequence? That is, I have a table with a list of departments. While *relatively* static, technically speaking this is a dynamic list - someone certainly could decide to add a department, or combine two departments into one new one, whatever. In any case, I would ideally like to have a sequence per department, but since the list is dynamic it's not as simple as creating 33 independent sequences and then having a lookup table somewhere, although I guess I could implement something of the sort with triggers.Is there any elegant way of accomplishing this? Something like SELECT nextval('department_seq['ops'])?
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD