On Sunday 03 July 2011 07:47:01 Chris Travers wrote:
> On Sun, Jul 3, 2011 at 7:25 AM, Ireneusz Pluta <ipluta@wp.pl> wrote:
> > You may use dense_rank() (or even rank()) window function to map your
> > sequence-with-gaps to a no-gap-id which will be used for exports.
>
> The typical case where gapless numbering comes up is something like this:
>
> In Greece, you go get invoice paper from the tax office which is
> numbered in sequence and the government gets a list of the invoice
> forms you have purchased. You then print the invoices on those paper
> forms, and must number the invoices sequentially and without gaps. In
> the case of an audit, all paper forms obtained must be accounted for
> as must all gaps in numbering. You MUST be able to connect each
> sequential invoice number (internally generated) to each invoice form
> (numbered at the tax office).
>
> In this case you really have no choice but to lock some records,
> generate a new gapless id, and save/print it. Naturally this causes
> the sorts of problems mentioned.
So the problem with Ireneuz's solution is that the mapping isn't stable
enough. Then how about creating this mapping on disk whenever an export is
done (assuming exports are much less frequent than inserts) ?
* create table idmap(realid integer references realdata(id), gaplessid
integer);
* insert into realdata with the usual sequence
* whenever an export of new data is requested :
* lock table idmap
* select * from realdata where id > (select max(realid) from idmap) and
realdata.timestamp < now() -
'safety_margin_for_inserts_likely_to_rollback'::interval order by id for
update;
* insert into idmap
* unlock table idmap
* select gaplessid,data from idmap left join realdata
Depending on your insert/export ratio this might be more efficient. And of
course you can't delete rows 6 months later, but you knew that :p
--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320
Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.dephily@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.
