Thread: Invoice number

Invoice number

From
Kaare Rasmussen
Date:
Hi

My, my. It works. How many times have I tried this mailing list with no 
success :-(

I'm wondering how people creates guaranteed sequential numbers - in my case 
for invoice numbers.

- Sequences are not rollback'able.
- It seems overkill to have a table just for this.
- What else?

tia


Re: Invoice number

From
Volker Paul
Date:
Hi,

> I'm wondering how people creates guaranteed sequential numbers - in my case
> for invoice numbers.

See the PostgreSQL book p. 85 and 250, and the online doc about serials:
CREATE TABLE person ( id SERIAL, name TEXT );


Volker Paul


Re: Invoice number

From
Mike Castle
Date:
On Thu, Dec 21, 2000 at 11:10:00AM +0100, Kaare Rasmussen wrote:
> - Sequences are not rollback'able.

Did you mean SERIAL instead of sequence here?

If so, why is no rollbackable an issue?  All you should need is unique
numbers.  Not necessarily exactly sequential numbers.

mrc
--       Mike Castle       Life is like a clock:  You can work constantly dalgoda@ix.netcom.com  and be right all the
time,or not work at all
 
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc   We are all of us living in the shadow of
Manhattan. -- Watchmen
 


Re: Invoice number

From
"Josh Berkus"
Date:
Kaare,

> How many times have I tried this
> mailing list with no 
> success :-(

Hey!  It's a peer-to-peer mailing list.  You want guarenteed
answers, pay for support (I do)  <shrug>

> I'm wondering how people creates guaranteed sequential
> numbers - in my case 
> for invoice numbers.
> 
> - Sequences are not rollback'able.
> - It seems overkill to have a table just for this.
> - What else?

Given a full set of business rules for invoice numbers, I
could probably throw you a solution.  Gods know that I've
devised enough invoicing systems in the past.  Please post
more detail.

-Josh Berkus



Re: Invoice number

From
John Hasler
Date:
Mike Castle writes:
> If so, why is no rollbackable an issue?  All you should need is unique
> numbers.  Not necessarily exactly sequential numbers.

Sometimes business rules require that every member of a sequence of such
things as invoice numbers be accounted for.

Speculation:
Would it be possible to log SERIAL's as they are issued?  It might be
sufficient to just record the user id, though it would be more useful to
log some indication of what the number was used for (or at least whether
or not it was used at all).
-- 
John Hasler
john@dhh.gt.org (John Hasler)
Dancing Horse Hill
Elmwood, WI


Re: Invoice number

From
"Oliver Elphick"
Date:
Mike Castle wrote: >On Thu, Dec 21, 2000 at 11:10:00AM +0100, Kaare Rasmussen wrote: >> - Sequences are not
rollback'able.> >Did you mean SERIAL instead of sequence here? > >If so, why is no rollbackable an issue?  All you
shouldneed is unique >numbers.  Not necessarily exactly sequential numbers.
 

For invoice numbers, it matters.

Numbers missing from such a sequence are likely to provoke questions from
auditors and taxmen; why borrow trouble?

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "For a child will be born to us, a son will be given to     us; And the
governmentwill rest on His shoulders; And     His name will be called Wonderful Counsellor, Mighty      God, Eternal
Father,Prince of Peace."                                        Isaiah 9:6 
 




Re: Invoice number

From
Mike Castle
Date:
On Thu, Dec 21, 2000 at 05:50:43PM +0000, Oliver Elphick wrote:
> Mike Castle wrote:
>   >On Thu, Dec 21, 2000 at 11:10:00AM +0100, Kaare Rasmussen wrote:
>   >> - Sequences are not rollback'able.
>   >
>   >Did you mean SERIAL instead of sequence here?
>   >
>   >If so, why is no rollbackable an issue?  All you should need is unique
>   >numbers.  Not necessarily exactly sequential numbers.
> 
> For invoice numbers, it matters.
> 
> Numbers missing from such a sequence are likely to provoke questions from
> auditors and taxmen; why borrow trouble?

What do you do on the following scenario:

Client 1 is placing an order, gets invoice #1.
Client 2 is simultaneously placing an order, and gets invoice #2.

Client 1 changes mind and cancels order.  Invoice #1 is not used.  Invoice
#2 is.

Client 3 comes along.  Do they use invoice #1, out of order, or invoice #3?

I suppose of the assignment of the invoice number is set up in such a way
as it is the very last action performed just before the commit, you should
be able to ensure that indeed the situation of having to deal with a
rollback would never occur (outside of a system crash between assignment of
invoice and commit, and that could be explained to auditors).

[What happens in a paper world if a cup of coffee is spilt on some
invoices, and these precious items are thrown in the trash?]

mrc
--       Mike Castle       Life is like a clock:  You can work constantly dalgoda@ix.netcom.com  and be right all the
time,or not work at all
 
www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc   We are all of us living in the shadow of
Manhattan. -- Watchmen
 


Re: Invoice number

From
Kaare Rasmussen
Date:
> What do you do on the following scenario:

I don't enter !

> Client 1 is placing an order, gets invoice #1.

Wrong! He gets an order number

> Client 1 changes mind and cancels order.  Invoice #1 is not used.  Invoice
> #2 is.
> Client 3 comes along.  Do they use invoice #1, out of order, or invoice #3?

If Invoice 1 is cancelled for some reason, it is still an invoice. The right 
thing is to produce a credit note to balance off the cancelled invoice. 

For smaller quantities, I believe it is OK just to file the cancelled invoice 
with a clear note that it's been cancelled. But I have a feeling my 
accountant doesn't agree :-)

-- 
Kaare Rasmussen            --Linux, spil,--        Tlf:        3816 2582
Kaki Data                tshirts, merchandize      Fax:        3816 2501
Howitzvej 75               �ben 14.00-18.00        Email: kar@webline.dk
2000 Frederiksberg        L�rdag 11.00-17.00       Web:      www.suse.dk


Re: Invoice number

From
John Hasler
Date:
Mike Castle writes:
> Client 3 comes along.  Do they use invoice #1, out of order, or invoice
> #3?

It shouldn't matter, as long as every number is accounted for.

Seems to me that a trigger could make a log entry every time the serial is
incremented.  Workable?

> What happens in a paper world if a cup of coffee is spilt on some
> invoices, and these precious items are thrown in the trash?

They are returned to accounting with an explanatory note, the numbers are
logged as "voided", and the spoiled forms are shredded.
-- 
John Hasler
john@dhh.gt.org
Dancing Horse Hill
Elmwood, Wisconsin


Re: Invoice number

From
George Moga
Date:
Mike Castle wrote:

> What do you do on the following scenario:
>
> Client 1 is placing an order, gets invoice #1.
> Client 2 is simultaneously placing an order, and gets invoice #2.
>
> Client 1 changes mind and cancels order.  Invoice #1 is not used.  Invoice
> #2 is.
>
> Client 3 comes along.  Do they use invoice #1, out of order, or invoice #3?

You must use invoice #3, because #1 was already used for another client and it
remain printed on paper.
I made an billing application (Tcl/Tk + PostgreSQL) and invoice numbers are
generated by a sequence. It works with multiple billing location. When an
operator generate an invoice, he work without a number ... the number is
generated in the moment of saving and printing. In this mode, is posible to
cancel a invoice before the number is generated and is imposible to duplicate
numbers.
When a numer is generated, operators can not cancel printing. In this case, one
number represent always one printed invoice. If the invoice is canceled, it's
"paper version" is canceled too.


> I suppose of the assignment of the invoice number is set up in such a way
> as it is the very last action performed just before the commit, you should
> be able to ensure that indeed the situation of having to deal with a
> rollback would never occur (outside of a system crash between assignment of
> invoice and commit, and that could be explained to auditors).
>

I use "begin transaction" ... "commit" (abort) in time to write positions in
invoice because I want to decrease (on increase) stocks on line. In this mode
the other uses get the real stock (I do not use a "stocks" table ... stocks are
calculated "on-line" ... sum(in)-sum(out) on that product for all prices who are
active at that moment) for the products who are in curent invoice. When invoice
is saved positions remain in database and is generated an invoice number or, if
invoice is canceled, all this position are deleted, stocks returns to original
values and no invoice number are generated.


>
> [What happens in a paper world if a cup of coffee is spilt on some
> invoices, and these precious items are thrown in the trash?]
>

I cancel all this invoices but I never thrown them to trash because, in ROMANIA,
printed invoces already have a printed number (with safety marks) and when
taxman came to check he want to see all this numbers, valids or canceled.


>
> mrc
> --
>        Mike Castle       Life is like a clock:  You can work constantly
>   dalgoda@ix.netcom.com  and be right all the time, or not work at all
> www.netcom.com/~dalgoda/ and be right at least twice a day.  -- mrc
>     We are all of us living in the shadow of Manhattan.  -- Watchmen

I hope this can help ...
Sorry for my poor english.


George Moga,   Data Systems SRL   Slobozia, ROMANIA




Re: Invoice number

From
Stephan Szabo
Date:
> I'm wondering how people creates guaranteed sequential numbers - in my case 
> for invoice numbers.
> 
> - Sequences are not rollback'able.
> - It seems overkill to have a table just for this.
> - What else?

You'll probably need a table (although you may be able to get away with
only one for all of these you want to do).  The big issue here is locking
since a second transaction looking to get a number needs to wait for 
an earlier transaction that has already gotten a number to either commit
or rollback to reuse the number if necessary.



Re: Invoice number

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
Thus spake Oliver Elphick
>   >If so, why is no rollbackable an issue?  All you should need is unique
>   >numbers.  Not necessarily exactly sequential numbers.
> 
> For invoice numbers, it matters.
> 
> Numbers missing from such a sequence are likely to provoke questions from
> auditors and taxmen; why borrow trouble?

I solved this exact problem once before in another database (Progres) but
it should work here too.  What I did was select a maximum number of
concurrent transactions (I picked 10) and created a table with two
columns, a token and a number.  For every sequence that I needed I
seeded the table with 10 rows each with the name of the sequence so
that my next routine could reference it and with the number column
set from 1 to 10.  When I need a new number I simply find the smallest
number for that token, lock the row, use the number in my work and
commit everything when I was done.  In Postgres I was able to scan
through each number in order stopping at the first unlocked one so the
process didn't block.  Not sure how to apply that here.  Perhaps a
third column that you plug in your process ID or something so that you
do something like this.

UPDATE numbers SET reserve = [my ID]   WHERE seqname = [token] ANDseqnum = (SELECT MIN(seqnum) FROM numbers
WHEREseqname = [token] AND            reserve IS NULL);
 

You would have to have some way of cleaning these up pretty quickly if your
app crashed or failed to set reserve back to NULL.  The other issue here is
that strictly speaking you may not get your numbers sequentially but you
won't skip any.  In my experience the accountants have been OK with that.

Hmmm.  Perhaps "SELECT ... FOR UPDATE" would work instead of that extra
column.  Still need a way of skipping locked records though.

Perhaps in a future version of PostgreSQL we can have a first class serial
type that handles all of this.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.


Optimization recommendations request

From
"Joe Conway"
Date:
Hello,

I'm working on an application where I need to design for one table to grow
to an extremely large size. I'm already planning to partition the data into
multiple tables, and even possibly multiple servers, but even so each table
may need to grow to the 10 - 15 million tuple range. This table will be used
for a keyed lookup and it is very important that the query return in well
under a second. I've done a small test using a dual ppro 200 server with 512
MB RAM, running RedHat Linux 6.1, and a late November 7.1 pre-beta snapshot
of PostgreSQL (PGOPTS="-D/opt/pgsql/data -i  -B 25000"). I used a test table
with about 5 million tuples.

Details:

CREATE TABLE foo(   guid varchar(20) not null,   ks varchar(20) not null
);

--> insert 5,000,000 rows, both guid and ks are 20 byte sha-1 hashes
-- tried this first
-- create index foo_idx1 on foo(guid);
-- then tried
create index foo_idx1 on foo using HASH (guid);

SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d';

The query currently takes in excess of 40 seconds. I would appreciate any
suggestions for optimizing to bring this down substantially.

Thanks in advance,

Joe Conway



Re: Optimization recommendations request

From
Stephan Szabo
Date:
What does explain show for your query?

On Sat, 23 Dec 2000, Joe Conway wrote:

> Hello,
> 
> I'm working on an application where I need to design for one table to grow
> to an extremely large size. I'm already planning to partition the data into
> multiple tables, and even possibly multiple servers, but even so each table
> may need to grow to the 10 - 15 million tuple range. This table will be used
> for a keyed lookup and it is very important that the query return in well
> under a second. I've done a small test using a dual ppro 200 server with 512
> MB RAM, running RedHat Linux 6.1, and a late November 7.1 pre-beta snapshot
> of PostgreSQL (PGOPTS="-D/opt/pgsql/data -i  -B 25000"). I used a test table
> with about 5 million tuples.
> 
> Details:
> 
> CREATE TABLE foo(
>     guid varchar(20) not null,
>     ks varchar(20) not null
> );
> 
> --> insert 5,000,000 rows, both guid and ks are 20 byte sha-1 hashes
> -- tried this first
> -- create index foo_idx1 on foo(guid);
> -- then tried
> create index foo_idx1 on foo using HASH (guid);
> 
> SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d';
> 
> The query currently takes in excess of 40 seconds. I would appreciate any
> suggestions for optimizing to bring this down substantially.



Re: Optimization recommendations request

From
"Joe Conway"
Date:
Subject: Re: [SQL] Optimization recommendations request


>
> What does explain show for your query?
>

I sent this a week ago using the wrong (i.e. not the one I signed up with)
reply-to address, so it didn't make it to the list until just now. In the
meantime I ran explain and noticed that the index wasn't being used. So I
ran vacuum analyze and now I'm getting the expected performance (and explain
shows the index being used). If I understand the logged statistics
correctly, I'm getting results returned in anywhere from about 3 to 45 ms,
depending on cache hit rate. I also repeated my test with 15 million records
with similar results. Not bad at all!

I am still interested in any generic optimization tips for very large
tables.

Thanks for taking the time to reply!

Joe



Re: Optimization recommendations request

From
Tom Lane
Date:
"Joe Conway" <joe@conway-family.com> writes:
> create index foo_idx1 on foo using HASH (guid);

> SELECT ks FROM foo WHERE guid = 'f9ee1930f4010ba34cc5ca6efd27d988eb4f921d';

> The query currently takes in excess of 40 seconds. I would appreciate any
> suggestions for optimizing to bring this down substantially.

Why are you using a hash index?  btree is a lot better for every
conceivable purpose.

It would also be a good idea to check to make sure that the query is
using the index (see EXPLAIN).  You didn't mention whether you'd done
a VACUUM ANALYZE, so there's a risk the planner will make the wrong
choice.
        regards, tom lane