Quantcast

How to generate unique invoice numbers for each day

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to generate unique invoice numbers for each day

Andrus Moor
Invoice numbers have format  yymmddn

where n is sequence number in day staring at 1 for every day.

command

SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
'^[0-9]*'),'')::int),0)+1
FROM invoice
where date= ?invoicedate

is used to get next free invoice number if new invoice is saved.

If multiple invoices are saved concurrently from different processes, they
will probably get same number.

How to get unique invoice number for some day in 8.1+ when multiple users
create new invoices ?

Andrus.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Jorge Godoy-2
Use a sequence.

--
Jorge Godoy     <[hidden email]>


2011/1/15 Andrus Moor <[hidden email]>
Invoice numbers have format  yymmddn

where n is sequence number in day staring at 1 for every day.

command

SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), '^[0-9]*'),'')::int),0)+1
FROM invoice
where date= ?invoicedate

is used to get next free invoice number if new invoice is saved.

If multiple invoices are saved concurrently from different processes, they will probably get same number.

How to get unique invoice number for some day in 8.1+ when multiple users create new invoices ?

Andrus.

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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers foreach day

Andrus Moor
There are 365 days in year.
Do you really think pre-creating sequence for every day for every year is best solution ?
 
Andrus.
----- Original Message -----
Sent: Saturday, January 15, 2011 8:41 PM
Subject: ***SPAM*** Re: [GENERAL] How to generate unique invoice numbers foreach day

Use a sequence.

--
Jorge Godoy     <[hidden email]>


2011/1/15 Andrus Moor <[hidden email]>
Invoice numbers have format  yymmddn

where n is sequence number in day staring at 1 for every day.

command

SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), '^[0-9]*'),'')::int),0)+1
FROM invoice
where date= ?invoicedate

is used to get next free invoice number if new invoice is saved.

If multiple invoices are saved concurrently from different processes, they will probably get same number.

How to get unique invoice number for some day in 8.1+ when multiple users create new invoices ?

Andrus.

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

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers foreach day

Jorge Godoy-2
Why would you do that?

You can always reset the sequence at the end of the day.

--
Jorge Godoy     <[hidden email]>


On Sat, Jan 15, 2011 at 17:09, Andrus Moor <[hidden email]> wrote:
There are 365 days in year.
Do you really think pre-creating sequence for every day for every year is best solution ?
 
Andrus.
----- Original Message -----
Sent: Saturday, January 15, 2011 8:41 PM
Subject: ***SPAM*** Re: [GENERAL] How to generate unique invoice numbers foreach day

Use a sequence.

--
Jorge Godoy     <[hidden email]>


2011/1/15 Andrus Moor <[hidden email]>
Invoice numbers have format  yymmddn

where n is sequence number in day staring at 1 for every day.

command

SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), '^[0-9]*'),'')::int),0)+1
FROM invoice
where date= ?invoicedate

is used to get next free invoice number if new invoice is saved.

If multiple invoices are saved concurrently from different processes, they will probably get same number.

How to get unique invoice number for some day in 8.1+ when multiple users create new invoices ?

Andrus.

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


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers foreach day

Tomas Vondra
If the gaps (user gets a number from a sequence and then rollbacks the
transaction) are not a problem, then the sequences (reset every day) are
probably the best solution.

If the gaps are a problem (which is usually the case with invoicing
systems), then you need to manage that on your own, e.g. using a table
with one row for sequence (but that might be a bottleneck with multiple
users and a lot of invoices).

Tomas

Dne 15.1.2011 20:32, Jorge Godoy napsal(a):

> Why would you do that?
>
> You can always reset the sequence at the end of the day.
>
> --
> Jorge Godoy     <[hidden email] <mailto:[hidden email]>>
>
>
> On Sat, Jan 15, 2011 at 17:09, Andrus Moor <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     There are 365 days in year.
>     Do you really think pre-creating sequence for every day for every
>     year is best solution ?
>      
>     Andrus.
>
>         ----- Original Message -----
>         *From:* Jorge Godoy <mailto:[hidden email]>
>         *To:* Andrus Moor <mailto:[hidden email]>
>         *Cc:* [hidden email]
>         <mailto:[hidden email]>
>         *Sent:* Saturday, January 15, 2011 8:41 PM
>         *Subject:* ***SPAM*** Re: [GENERAL] How to generate unique
>         invoice numbers foreach day
>
>         Use a sequence.
>
>         --
>         Jorge Godoy     <[hidden email] <mailto:[hidden email]>>
>
>
>         2011/1/15 Andrus Moor <[hidden email] <mailto:[hidden email]>>
>
>             Invoice numbers have format  yymmddn
>
>             where n is sequence number in day staring at 1 for every day.
>
>             command
>
>             SELECT COALESCE(MAX(nullif(substring( substring(tasudok from
>             7), '^[0-9]*'),'')::int),0)+1
>             FROM invoice
>             where date= ?invoicedate
>
>             is used to get next free invoice number if new invoice is saved.
>
>             If multiple invoices are saved concurrently from different
>             processes, they will probably get same number.
>
>             How to get unique invoice number for some day in 8.1+ when
>             multiple users create new invoices ?
>
>             Andrus.
>
>             --
>             Sent via pgsql-general mailing list
>             ([hidden email]
>             <mailto:[hidden email]>)
>             To make changes to your subscription:
>             http://www.postgresql.org/mailpref/pgsql-general
>
>
>


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers foreach day

Andrus Moor
In reply to this post by Jorge Godoy-2
Invoices can entered also some days forward or back. Users enters invoice date and expected program to generate next sequential number for this day.
Different users can enter invoices for different days.
 
Andrus.
 
----- Original Message -----
Sent: Saturday, January 15, 2011 9:32 PM
Subject: Re: [GENERAL] How to generate unique invoice numbers foreach day

Why would you do that?

You can always reset the sequence at the end of the day.

--
Jorge Godoy     <[hidden email]>


On Sat, Jan 15, 2011 at 17:09, Andrus Moor <[hidden email]> wrote:
There are 365 days in year.
Do you really think pre-creating sequence for every day for every year is best solution ?
 
Andrus.
----- Original Message -----
Sent: Saturday, January 15, 2011 8:41 PM
Subject: ***SPAM*** Re: [GENERAL] How to generate unique invoice numbers foreach day

Use a sequence.

--
Jorge Godoy     <[hidden email]>


2011/1/15 Andrus Moor <[hidden email]>
Invoice numbers have format  yymmddn

where n is sequence number in day staring at 1 for every day.

command

SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), '^[0-9]*'),'')::int),0)+1
FROM invoice
where date= ?invoicedate

is used to get next free invoice number if new invoice is saved.

If multiple invoices are saved concurrently from different processes, they will probably get same number.

How to get unique invoice number for some day in 8.1+ when multiple users create new invoices ?

Andrus.

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


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers foreach day

Tomas Vondra
In that case you have to manage the IDs on your own, the sequences won't
help you in this (unless you really create one sequence for each day,
which does not seem like a good solution to me).

A really simple solution might be to do a BEFORE INSERT trigger that
checks the last ID inserted for the day, locks that ID somehow (e.g. the
record with the ID) so that you don't get duplicate IDs and uses that ID.

Another solution is to manage the IDs on your own in a separate table,
i.e. create a table

CREATE TABLE invoice_numbers (
   invoice_date DATE NOT NULL PRIMARY KEY,
   invoice_last INTEGER NOT NULL DEFAULT 0
);

and then something like

BEGIN;

   UPDATE invoice_numbers SET invoice_last = (invoice_last + 1)
    WHERE invoice_date = '2010-11-...' RETURNING invoice_last;

   ... use the new invoice_last value

COMMIT;

But again, this might be a concurrency bottleneck if there's a lot of
invoices created at the same time by different users (or if the
transaction is long).

Tomas

Dne 15.1.2011 21:04, Andrus Moor napsal(a):

> Invoices can entered also some days forward or back. Users enters
> invoice date and expected program to generate next sequential number for
> this day.
> Different users can enter invoices for different days.
>  
> Andrus.
>  
>
>     ----- Original Message -----
>     *From:* Jorge Godoy <mailto:[hidden email]>
>     *To:* Andrus Moor <mailto:[hidden email]>
>     *Cc:* [hidden email]
>     <mailto:[hidden email]>
>     *Sent:* Saturday, January 15, 2011 9:32 PM
>     *Subject:* Re: [GENERAL] How to generate unique invoice numbers
>     foreach day
>
>     Why would you do that?
>
>     You can always reset the sequence at the end of the day.
>
>     --
>     Jorge Godoy     <[hidden email] <mailto:[hidden email]>>
>
>
>     On Sat, Jan 15, 2011 at 17:09, Andrus Moor <[hidden email]
>     <mailto:[hidden email]>> wrote:
>
>         There are 365 days in year.
>         Do you really think pre-creating sequence for every day for
>         every year is best solution ?
>          
>         Andrus.
>
>             ----- Original Message -----
>             *From:* Jorge Godoy <mailto:[hidden email]>
>             *To:* Andrus Moor <mailto:[hidden email]>
>             *Cc:* [hidden email]
>             <mailto:[hidden email]>
>             *Sent:* Saturday, January 15, 2011 8:41 PM
>             *Subject:* ***SPAM*** Re: [GENERAL] How to generate unique
>             invoice numbers foreach day
>
>             Use a sequence.
>
>             --
>             Jorge Godoy     <[hidden email] <mailto:[hidden email]>>
>
>
>             2011/1/15 Andrus Moor <[hidden email]
>             <mailto:[hidden email]>>
>
>                 Invoice numbers have format  yymmddn
>
>                 where n is sequence number in day staring at 1 for every
>                 day.
>
>                 command
>
>                 SELECT COALESCE(MAX(nullif(substring( substring(tasudok
>                 from 7), '^[0-9]*'),'')::int),0)+1
>                 FROM invoice
>                 where date= ?invoicedate
>
>                 is used to get next free invoice number if new invoice
>                 is saved.
>
>                 If multiple invoices are saved concurrently from
>                 different processes, they will probably get same number.
>
>                 How to get unique invoice number for some day in 8.1+
>                 when multiple users create new invoices ?
>
>                 Andrus.
>
>                 --
>                 Sent via pgsql-general mailing list
>                 ([hidden email]
>                 <mailto:[hidden email]>)
>                 To make changes to your subscription:
>                 http://www.postgresql.org/mailpref/pgsql-general
>
>
>


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers foreach day

Rich Shepard
In reply to this post by Andrus Moor
On Sat, 15 Jan 2011, Andrus Moor wrote:

> There are 365 days in year. Do you really think pre-creating sequence for
> every day for every year is best solution ?

Andrus,

   I just saw this thread so my idea may not work for you. What I'd do is use
the Julian date (that is, the sequential day from 1-365/6) followed by 1, 2,
or 3 digits depending on how many invoices are generated each day. Makes
sorting and comparisons simple.

Rich

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Jasen Betts-5
In reply to this post by Andrus Moor
On 2011-01-15, Andrus Moor <[hidden email]> wrote:

> Invoice numbers have format  yymmddn
>
> where n is sequence number in day staring at 1 for every day.
>
> command
>
> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
> '^[0-9]*'),'')::int),0)+1
> FROM invoice
> where date= ?invoicedate
>
> is used to get next free invoice number if new invoice is saved.
>
> If multiple invoices are saved concurrently from different processes, they
> will probably get same number.
>
> How to get unique invoice number for some day in 8.1+ when multiple users
> create new invoices ?

use a sequence, reset the sequence each night.

--
⚂⚃ 100% natural

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Peter Geoghegan
This is an very common and well understood problem. Take a look at this:

http://www.varlena.com/GeneralBits/130.php

--
Regards,
Peter Geoghegan

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Tomas Vondra
In reply to this post by Jasen Betts-5
Dne 16.1.2011 03:29, Jasen Betts napsal(a):

> On 2011-01-15, Andrus Moor <[hidden email]> wrote:
>> Invoice numbers have format  yymmddn
>>
>> where n is sequence number in day staring at 1 for every day.
>>
>> command
>>
>> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
>> '^[0-9]*'),'')::int),0)+1
>> FROM invoice
>> where date= ?invoicedate
>>
>> is used to get next free invoice number if new invoice is saved.
>>
>> If multiple invoices are saved concurrently from different processes, they
>> will probably get same number.
>>
>> How to get unique invoice number for some day in 8.1+ when multiple users
>> create new invoices ?
>
> use a sequence, reset the sequence each night.

This was already proposed but he can't do that as the users may issue
invoices for different days (not just for 'today'). Which means the user
may create an invoice for monday, then another one for tuesday and then
another one for monday again. A single sequence can't solve this.

So either he has to create a sequence for each day, but he'll get a lot
of sequences (and it's a bit messy), and there will be gaps (not sure if
this is acceptable).

Or he can use the solution proposed in the General Bits 130 (which is
basically the solution I've already proposed) and maintain gapless
sequences using a table.

Tomas

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Andy Colson
In reply to this post by Andrus Moor
On 01/15/2011 12:13 PM, Andrus Moor wrote:

> Invoice numbers have format yymmddn
>
> where n is sequence number in day staring at 1 for every day.
>
> command
>
> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), '^[0-9]*'),'')::int),0)+1
> FROM invoice
> where date= ?invoicedate
>
> is used to get next free invoice number if new invoice is saved.
>
> If multiple invoices are saved concurrently from different processes, they will probably get same number.
>
> How to get unique invoice number for some day in 8.1+ when multiple users create new invoices ?
>
> Andrus.
>

I understand this is a business rule, and you cant change it.

My opinion is you are doing it wrong.  Don't encode more than one bit of data into one column.  You have two bits of data, date and invoice number.  Use TWO columns!  Let the invoice number auto-inc and never reset it.  You want to find invoices on some day, query the date field.  You want to find a specific invoice, query the invoice number.  Dont combine the two.  Why write a bunch of bug ridden code that encodes and decodes the two fields into one?

But I know... you cant change it.  Someone up the chain thinks its just the bee's knees.  I had to say it though.  Its been said.  I will go in peace.

-Andy

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Andrus Moor
Andy,

>> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
>> '^[0-9]*'),'')::int),0)+1
>> FROM invoice
>> where date= ?invoicedate
>>
>> is used to get next free invoice number if new invoice is saved.
>>
>> If multiple invoices are saved concurrently from different processes,
>> they will probably get same number.

> I understand this is a business rule, and you cant change it.

Yes. This is customer requirement and I cannot change it.
Is  it reasonable/how to implement the following:

1. plpgsql command obtains some lock
2. It uses

 SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
'^[0-9]*'),'')::int),0)+1
 FROM invoice
 where date= ?invoicedate

to get next number for invoice date day

3. It adds new invoice with this numbder to database

4. It releases the lock.

Or is it better to maintain separate sequence or separate table of free
numbers for every day ?

using sequence approach:

Application checks for sequence name in form invoiceno_yyyymmdd
if sequence does not exist it is created. For concurrent adding second
caller gets sequence exists exception and in this case this query can
re-executed.
Next value is obtained from sequence

sequneces older than 1 year should be purded automatically since max 1 year
backward numbers may created.
Which is best solution ?

using new record approach:

1. lock table
2. in this day sequnece does notr exist, add it
3. get next value for day, increment number in this table
4. unlock the table.

Which command should be used to obtain exclusise write access to table (lock
some reosurce or semaphore) ?

Andrus.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Andy Colson
On 01/16/2011 11:00 AM, Andrus Moor wrote:

> Andy,
>
>>> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7), '^[0-9]*'),'')::int),0)+1
>>> FROM invoice
>>> where date= ?invoicedate
>>>
>>> is used to get next free invoice number if new invoice is saved.
>>>
>>> If multiple invoices are saved concurrently from different processes, they will probably get same number.
>
>> I understand this is a business rule, and you cant change it.
>
> Yes. This is customer requirement and I cannot change it.
OR... can you go back to your customer and tell them they wont like this.  Really really they should let you do it correctly.  I find people dont change because they dont have to, not because there is an actual reason.  Many times, given a description of how hard and how messy something will be to code, I have convinced people that a simple business change and simple code is really the best approach.  But I have hit walls.  Things I could not change, but I did try.


> Is it reasonable/how to implement the following:

Sorry, I have no idea.

-Andy

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Andrus Moor
>> Yes. This is customer requirement and I cannot change it.
> OR... can you go back to your customer and tell them they wont like this.
> Really really they should let you do it correctly.  I find people dont
> change because they dont have to, not because there is an actual reason.
> Many times, given a description of how hard and how messy something will
> be to code, I have convinced people that a simple business change and
> simple code is really the best approach.  But I have hit walls.  Things I
> could not change, but I did try.

My Visual FoxPro application  works OK in this case.
I used FLOCK() to lock invoice header table (FLOCK() waits indefinitely
until lock is obtained and reads fresh data from disk),

used

SELECT MAX( CAST( SUBSTRING(invoiceno,8) AS INT ) )+1
FROM invoices
WHERE date= m.invoice_date

to get next free number, inserted invoice and unlocked the table.

Customer expects Postgres to be more powerful than FoxPro . He don't
understand why this stops working after upgrade.

Andrus.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Radosław Smogura
In reply to this post by Andrus Moor
I will sugest to:
1. Delete point 1.
2. In point 2. add FOR UPDATE
3. Use READ COMMITED TRANSACTION ISOLATION LEVEL

Don't lock tables, You wrote you can generate invoices for few days backward,
so you don't need locking whole table.

Don't use seqences, as sequence value will don't get back when transaction is
rolled back (You need to prevent gaps).

Locking with UPDATE, or FOR UPDATE is much more portable.

If you generate invoices in massive operation, probably when process runs no
one will be able to create invoice, but you don't need to create multi thread
application.

In any approach preventing gaps, locking is required. This is real life
situation; imagine you have two coworkers and then they need to create
invoices, so they looks in ledger (or a last day copy of ledger in their
offices; international company, but no Internet, only fax and telephone) and
checks last number used, what should be done next?

"Andrus Moor" <[hidden email]> Sunday 16 January 2011 18:00:58

> Andy,
>
> >> SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
> >> '^[0-9]*'),'')::int),0)+1
> >> FROM invoice
> >> where date= ?invoicedate
> >>
> >> is used to get next free invoice number if new invoice is saved.
> >>
> >> If multiple invoices are saved concurrently from different processes,
> >> they will probably get same number.
> >
> > I understand this is a business rule, and you cant change it.
>
> Yes. This is customer requirement and I cannot change it.
> Is  it reasonable/how to implement the following:
>
> 1. plpgsql command obtains some lock
> 2. It uses
>
>  SELECT COALESCE(MAX(nullif(substring( substring(tasudok from 7),
> '^[0-9]*'),'')::int),0)+1
>  FROM invoice
>  where date= ?invoicedate
>
> to get next number for invoice date day
>
> 3. It adds new invoice with this numbder to database
>
> 4. It releases the lock.
>
> Or is it better to maintain separate sequence or separate table of free
> numbers for every day ?
>

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Alban Hertroys-3
In reply to this post by Andrus Moor
On 16 Jan 2011, at 18:56, Andrus Moor wrote:

> My Visual FoxPro application  works OK in this case.
> I used FLOCK() to lock invoice header table (FLOCK() waits indefinitely until lock is obtained and reads fresh data from disk),
>
> used
>
> SELECT MAX( CAST( SUBSTRING(invoiceno,8) AS INT ) )+1
> FROM invoices
> WHERE date= m.invoice_date
>
> to get next free number, inserted invoice and unlocked the table.

If you really need gapless sequences, then you would do something rather similar in Postgres.

Instead of the whole database file you only lock the file for the table containing the sequence. That's called serialization, something you can't get around if you require gapless sequences. The documentation does a much better job at explaining serialization than I could, I'm sure.
That's also why people were suggesting ways around that requirement, as it's a costly feature with regards to database performance.

There are blogs about how to create gapless sequences in Postgres, so I won't go into detail about them.

> Customer expects Postgres to be more powerful than FoxPro . He don't understand why this stops working after upgrade.


And he is right too! To the customer the database is probably that thing that they put their data in and that does all the magic stuff for them. To you it's that thing that you implement all that magic stuff in! Half of what the customer calls his database is what you implemented.

Your customer doesn't care about details like that though, he cares about a working system. The database (the part that we call the database) does it's thing rather well in 99.99% of the cases - usually when the database (as seen from the customer's point of view) doesn't do what it's supposed to do, the problem is in the business logic that you put in that database.

So to summarise: What's failing her isn't the database, it's you.

Where you failed, heck, I don't know... You probably didn't get all the requirements right, or you forgot to test this particular part of the application, or whatever. Don't fuss about it too much though, it's almost impossible to not fail somewhere with complicated applications like this. Consider it a lesson learned.

Just don't blame the database for it, especially not on a mailing-list about that database ;)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d333dbf11702139115944!



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Andrus Moor
In reply to this post by Radosław Smogura
Thank you.

> 2. In point 2. add FOR UPDATE
> 3. Use READ COMMITED TRANSACTION ISOLATION LEVEL
>
> Don't lock tables, You wrote you can generate invoices for few days
> backward,
> so you don't need locking whole table.
>
> Don't use seqences, as sequence value will don't get back when transaction
> is
> rolled back (You need to prevent gaps).
>
> Locking with UPDATE, or FOR UPDATE is much more portable.
>
> If you generate invoices in massive operation, probably when process runs
> no
> one will be able to create invoice, but you don't need to create multi
> thread
> application.

> In any approach preventing gaps, locking is required. This is real life
> situation; imagine you have two coworkers and then they need to create
> invoices, so they looks in ledger (or a last day copy of ledger in their
> offices; international company, but no Internet, only fax and telephone)
> and
> checks last number used, what should be done next?

Using read commited isolation level requires knowing in start of transaction
will it perform new invoice adding or not. This requires changing program
logic a lot.
Currently script which creates day seq numbers runs inside transaction .
Transaction starter does not know will special isolation required or not.
Changing blindly all transactions to use this isolation level decreases
perfomance and may lead to deadlocks.

In my case I can assume that transaction newer fails since business rules
are verified and this is simple insert (inrare cases if it  fails due to
disk failure etc then gaps are allowed).
Can this knowledge used to create simpler solution ?

Andrus.


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Tomas Vondra
Dne 16.1.2011 22:44, Andrus Moor napsal(a):

> Thank you.
>
>> 2. In point 2. add FOR UPDATE
>> 3. Use READ COMMITED TRANSACTION ISOLATION LEVEL
>>
>> Don't lock tables, You wrote you can generate invoices for few days
>> backward,
>> so you don't need locking whole table.
>>
>> Don't use seqences, as sequence value will don't get back when
>> transaction
>> is
>> rolled back (You need to prevent gaps).
>>
>> Locking with UPDATE, or FOR UPDATE is much more portable.
>>
>> If you generate invoices in massive operation, probably when process runs
>> no
>> one will be able to create invoice, but you don't need to create multi
>> thread
>> application.
>
>> In any approach preventing gaps, locking is required. This is real life
>> situation; imagine you have two coworkers and then they need to create
>> invoices, so they looks in ledger (or a last day copy of ledger in their
>> offices; international company, but no Internet, only fax and telephone)
>> and
>> checks last number used, what should be done next?
>
> Using read commited isolation level requires knowing in start of
> transaction will it perform new invoice adding or not. This requires
> changing program logic a lot.
> Currently script which creates day seq numbers runs inside transaction .
> Transaction starter does not know will special isolation required or not.
> Changing blindly all transactions to use this isolation level decreases
> perfomance and may lead to deadlocks.

I really am not sure what you mean by this. The isolation levels are
implemented in the database, you don't need to change the application at
all. And there are only two isolation levels in PostgreSQL - READ
COMMITTED and SERIALIZABLE, where the READ COMMITTED is the less
restrictive one (and default).

So everything runs (at least) in READ COMMITTED mode, no matter what you
do. You don't need to change anything.

Yes, locking may in some cases lead to deadlocks, that's true. For
example creating several invoices (for different days) in a single
transaction may lead to a deadlock. But that's a feature, not a bug.

And you can get around this by creating all the invoices in the same
order (e.g. sorted by date) - this prevents deadlocks.

> In my case I can assume that transaction newer fails since business
> rules are verified and this is simple insert (inrare cases if it  fails
> due to disk failure etc then gaps are allowed).
> Can this knowledge used to create simpler solution ?

Locking when updating the very same value is inevitable. If you update
the same row from two sessions, one of them has to wait until the other
one commits or rolls back. You can't get around this in a transactional
environment.

In Oracle you could solve this using an autonomous transaction, but
there's nothing like that in PostgreSQL.

So if you don't want to use the approach proposed in General Bits 130
(the one with gapless sequences implemented using a table), the only
option I'm aware of is to create one sequence for each day.

Tomas

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to generate unique invoice numbers for each day

Thomas Kellerer
Tomas Vondra wrote on 16.01.2011 23:41:
> Yes, locking may in some cases lead to deadlocks, that's true. For
> example creating several invoices (for different days) in a single
> transaction may lead to a deadlock. But that's a feature, not a bug.

Hmm, a single transaction cannot deadlock itself as far as I know.
A deadlock can only happen between two different transactions (T1 locks R1, waits for R2, T2 locks R2 waits for R1)

Regards
Thomas



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
12
Loading...