elein <[EMAIL PROTECTED]> writes:
> The technique for a single part gapless sequence and a two-part gapless
> sequence has been published today at http://www.varlena.com/GeneralBits.
>
> I'd be interested to hear of high concurrency usage that would break it
> or be notably slow.
>
> --elein
> [EM
The technique for a single part gapless sequence and a two-part gapless
sequence has been published today at http://www.varlena.com/GeneralBits.
I'd be interested to hear of high concurrency usage that would break it
or be notably slow.
--elein
[EMAIL PROTECTED]
---(end o
On 8/17/06, Merlin Moncure <[EMAIL PROTECTED]> wrote:
On 8/17/06, Brad Nicholson <[EMAIL PROTECTED]> wrote:
> > > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE;
> > > The first-to-obtain the gapless sequence transaction will establish
> > > a lock onthe "tax_id" row. The other tr
On Thu, 2006-08-17 at 15:13 -0500, Scott Marlowe wrote:
> On Thu, 2006-08-17 at 15:07, Merlin Moncure wrote:
> > On 8/17/06, Brad Nicholson <[EMAIL PROTECTED]> wrote:
> >
> > > > > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE;
> > > > > The first-to-obtain the gapless sequence tra
On Thu, 2006-08-17 at 16:07 -0400, Merlin Moncure wrote:
> On 8/17/06, Brad Nicholson <[EMAIL PROTECTED]> wrote:
>
> > > > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE;
> > > > The first-to-obtain the gapless sequence transaction will establish
> > > > a lock onthe "tax_id" row.
On Thu, 2006-08-17 at 15:07, Merlin Moncure wrote:
> On 8/17/06, Brad Nicholson <[EMAIL PROTECTED]> wrote:
>
> > > > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE;
> > > > The first-to-obtain the gapless sequence transaction will establish
> > > > a lock onthe "tax_id" row. The ot
On 8/17/06, Brad Nicholson <[EMAIL PROTECTED]> wrote:
> > Hmm, I think you are wrong. There is a SELECT ... FOR UPDATE;
> > The first-to-obtain the gapless sequence transaction will establish
> > a lock onthe "tax_id" row. The other transaction will block until
> > the first transaction finish
On Thu, 2006-08-17 at 12:12 -0400, Merlin Moncure wrote:
> On 8/17/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> > On 8/17/06, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > > On 8/16/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> > > > -- then create a function to retrieve the values:
> > > > CR
On 8/17/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
On 8/17/06, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> On 8/16/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> > -- then create a function to retrieve the values:
> > CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$
> > DECLARE
Just in case no one else has brought it up- 8.1+ supports 2PC and
savepoints, so one alternative would be to run your standard
insertion operations in a prepared transaction or savepoint block. If
you get so far as being able to prepare the transaction/complete the
savepoint block, you shou
Jorge Godoy wrote:
Berend Tober <[EMAIL PROTECTED]> writes:
A business requirement is to generate table rows that have uniformly
increasing, whole number sequences, i.e., the "gap-less" sequence.
This is something that I'll also have to code ;-) But the sequence for
"employees" would also b
On 8/17/06, Merlin Moncure <[EMAIL PROTECTED]> wrote:
On 8/16/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> -- then create a function to retrieve the values:
> CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$
> DECLARE
>n integer;
> BEGIN
>SELECT INTO n gseq_va
Berend Tober <[EMAIL PROTECTED]> writes:
> A business requirement is to generate table rows that have uniformly
> increasing, whole number sequences, i.e., the "gap-less" sequence. In this
> particular case the situation requires multiple such sequences within the same
> table -- for each employee
elein <[EMAIL PROTECTED]> writes:
> If this is true the solution for a transactional, gapless sequence is possible
> for table.gl_id where updated from count.gl_id. It is simple. However, it
> *depends* on the fact that the second transaction getting the newly updated
> record from the first tr
"Dawid Kuroczko" <[EMAIL PROTECTED]> writes:
> I did not test the code right now, but I've written something similar to
> it some time ago, and it worked fine. Remember to vacuum gapless_seq
> table frequently and don't expect stellar performance from it.
Interesting approach... And I don't exp
On Wednesday 16 August 2006 10:59 am, elein wrote:
> On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote:
> > On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
> > > On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
> > > > Jorge Godoy wrote:
> > > > > Chris <[EMAIL PROTECTED]
On 8/16/06, Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
-- then create a function to retrieve the values:
CREATE FUNCTION gseq_nextval(t text) RETURNS integer AS $$
DECLARE
n integer;
BEGIN
SELECT INTO n gseq_value+1 FROM gapless_seq WHERE gseq_name = t
FOR UPDATE;
UPDA
On 8/12/06, Jorge Godoy <[EMAIL PROTECTED]> wrote:
I was trying to solve a problem on an old system and realized that there might
be some better approach for doing what I need.
We have some documents that need to be ordered sequentially and without gaps.
I could use a sequence, but if the transa
elein wrote:
On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote:
On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
Wouldn't SELECT ... FOR UPDATE give you the row lock you need without
locking the table?
If this is t
On Mon, Aug 14, 2006 at 02:46:17PM -0700, Adrian Klaver wrote:
> On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
> > On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
> > > Jorge Godoy wrote:
> > > > Chris <[EMAIL PROTECTED]> writes:
> > > >>I'm not sure what type of lock you'd need t
On Monday 14 August 2006 02:46 pm, Adrian Klaver wrote:
> > Let current max id = x
> >
> > Transaction 1 (t1) does a select max(id) for update, gets a lock on the
> > last tuple at the time of the select, and gets x as a value for max id
> >
> > Transaction 2 (t2) does a select max(id) for update,
Brad Nicholson wrote:
On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
Jorge Godoy wrote:
Chris <[EMAIL PROTECTED]> writes:
I'm not sure what type of lock you'd need to make sure no other transactions
updated the table (see
http://www.postgresql.org/docs/8.1/interactive/sql-lock.h
On Monday 14 August 2006 01:59 pm, Brad Nicholson wrote:
> On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
> > Jorge Godoy wrote:
> > > Chris <[EMAIL PROTECTED]> writes:
> > >>I'm not sure what type of lock you'd need to make sure no other
> > >> transactions updated the table (see
> > >>htt
On Mon, 2006-08-14 at 16:08 -0400, Berend Tober wrote:
> Jorge Godoy wrote:
>
> > Chris <[EMAIL PROTECTED]> writes:
> >
> >
> >>I'm not sure what type of lock you'd need to make sure no other transactions
> >>updated the table (see
> >>http://www.postgresql.org/docs/8.1/interactive/sql-lock.html
Harald Fuchs <[EMAIL PROTECTED]> writes:
> In article <[EMAIL PROTECTED]>,
> Jorge Godoy <[EMAIL PROTECTED]> writes:
>
>> Harald Fuchs <[EMAIL PROTECTED]> writes:
>>> Why putting gapless numbers into the database at all? Just calculate them
>>> at
>>> query time.
>
>> And how would you retrieve
In article <[EMAIL PROTECTED]>,
Scott Ribe <[EMAIL PROTECTED]> writes:
>> Why putting gapless numbers into the database at all? Just
>> calculate them at query time.
> There is ABSOLUTELY NO WAY that would be acceptable for accounting or legal
> purposes. It would be the same as fabricating the
In article <[EMAIL PROTECTED]>,
Jorge Godoy <[EMAIL PROTECTED]> writes:
> Harald Fuchs <[EMAIL PROTECTED]> writes:
>> Why putting gapless numbers into the database at all? Just calculate them at
>> query time.
> And how would you retrieve the record that corresponds to invoice number
> #16355, f
In article <[EMAIL PROTECTED]>,
Richard Broersma Jr <[EMAIL PROTECTED]> writes:
> I am curious, can you calculate something like this using only sql? Or you
> you need to employee a
> procedural language like plpsgql?
You could use something like
SELECT (SELECT count(*) FROM tbl t2 WHERE t2.i
Jorge Godoy wrote:
Chris <[EMAIL PROTECTED]> writes:
I'm not sure what type of lock you'd need to make sure no other transactions
updated the table (see
http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in theory"
something like this should work:
begin;
select id from table
> Why putting gapless numbers into the database at all? Just calculate them at
> query time.
There is ABSOLUTELY NO WAY that would be acceptable for accounting or legal
purposes. It would be the same as fabricating the numbers during an audit.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerby
Harald Fuchs <[EMAIL PROTECTED]> writes:
> Why putting gapless numbers into the database at all? Just calculate them at
> query time.
And how would you retrieve the record that corresponds to invoice number
#16355, for example? Recalculating few records is fine, but millions of them
everytime y
> > AgentM <[EMAIL PROTECTED]> writes:
> >> Since the gapless numbers are purely for the benefit of the tax people, you
> >> could build your db with regular sequences as primary keys and then
> >> regularly
> >> (or just before tax-time) insert into a table which maps the gapless
> >> sequence
In article <[EMAIL PROTECTED]>,
Jorge Godoy <[EMAIL PROTECTED]> writes:
> AgentM <[EMAIL PROTECTED]> writes:
>> Since the gapless numbers are purely for the benefit of the tax people, you
>> could build your db with regular sequences as primary keys and then
>> regularly
>> (or just before tax-t
AgentM <[EMAIL PROTECTED]> writes:
> Since the gapless numbers are purely for the benefit of the tax people, you
> could build your db with regular sequences as primary keys and then regularly
> (or just before tax-time) insert into a table which maps the gapless sequence
> to the real primary k
Since the gapless numbers are purely for the benefit of the tax
people, you could build your db with regular sequences as primary
keys and then regularly (or just before tax-time) insert into a table
which maps the gapless sequence to the real primary key.
-M
---(
Jorge Godoy wrote:
> Chris <[EMAIL PROTECTED]> writes:
>
> > I'm not sure what type of lock you'd need to make sure no other transactions
> > updated the table (see
> > http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in
> > theory"
> > something like this should work:
> >
> > b
Michael Fuhr <[EMAIL PROTECTED]> writes:
> Automatically use indexes for MIN() and MAX() (Tom)
>
> In previous releases, the only way to use an index for MIN()
> or MAX() was to rewrite the query as SELECT col FROM tab ORDER
> BY col LIMIT 1. Index usage now happens automatica
On Mon, Aug 14, 2006 at 09:09:51AM -0300, Jorge Godoy wrote:
> Chris <[EMAIL PROTECTED]> writes:
> > P.S. I'm sure in older versions this query wouldn't use an index:
> > select max(id) from table;
>
> It doesn't. You'd have to do what you did: "order by desc limit 1" to
> have it using indexes.
Chris <[EMAIL PROTECTED]> writes:
> I'm not sure what type of lock you'd need to make sure no other transactions
> updated the table (see
> http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in theory"
> something like this should work:
>
> begin;
> select id from table order by id
Jorge Godoy wrote:
Jorge Godoy <[EMAIL PROTECTED]> writes:
Is there a better way to guarantee that there will be no gaps in my sequence
if something goes wrong with my transaction?
From the overwhelming feedback I assume there isn't a better way yet...
Thanks. I'll see how I can improve
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jorge Godoy wrote:
> Ron Johnson <[EMAIL PROTECTED]> writes:
>
>> Pre-allocate records. The (primary key?) field would have the
>> numbers already filled in, but all the rest of the fields in each
>> record be NULL, blanks, zeros or indicator values
Ron Johnson <[EMAIL PROTECTED]> writes:
> Pre-allocate records. The (primary key?) field would have the
> numbers already filled in, but all the rest of the fields in each
> record be NULL, blanks, zeros or indicator values ("~~",
> -9, etc).
>
> Then create a single-field table c
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Jorge Godoy wrote:
> Jorge Godoy <[EMAIL PROTECTED]> writes:
>
>> Is there a better way to guarantee that there will be no gaps in my sequence
>> if something goes wrong with my transaction?
>
> From the overwhelming feedback I assume there isn't a
Hi,
On Sun, 13 Aug 2006, Jorge Godoy wrote:
Christian Kratzer <[EMAIL PROTECTED]> writes:
I would at least try to assign multiple such numbers in batches to mimize
contention on the row you store the counter in.
What do you mean here? How would you guarantee that on of the receiver
transac
Christian Kratzer <[EMAIL PROTECTED]> writes:
> I would at least try to assign multiple such numbers in batches to mimize
> contention on the row you store the counter in.
What do you mean here? How would you guarantee that on of the receiver
transactions didn't rollback and left a gap in the "s
Hi,
On Sat, 12 Aug 2006, chris smith wrote:
On 8/12/06, Jorge Godoy <[EMAIL PROTECTED]> wrote:
Is there a better way to guarantee that there will be no gaps in my
sequence
if something goes wrong with my transaction?
Why does it matter?
I assume there is a reason you need it like this..
Jorge Godoy <[EMAIL PROTECTED]> writes:
> Is there a better way to guarantee that there will be no gaps in my sequence
> if something goes wrong with my transaction?
>From the overwhelming feedback I assume there isn't a better way yet...
Thanks. I'll see how I can improve the model then to se
Thomas Kellerer <[EMAIL PROTECTED]> writes:
> What do you do if a document gets deleted? Renumber the "following" documents
> so that no gaps are present in the already used ids?
There's no deletion possibility. A RULE sets a column named "active" to
"False" instead (I can set it manually or let
"chris smith" <[EMAIL PROTECTED]> writes:
> Why does it matter?
>
> I assume there is a reason you need it like this..
Of course there is. It is a project requirement and also a law requirement
that there's no unused number and that they be chronologically ordered as
well. This is also part of
Jorge Godoy wrote on 12.08.2006 01:33:
I was trying to solve a problem on an old system and realized that there might
be some better approach for doing what I need.
We have some documents that need to be ordered sequentially and without gaps.
I could use a sequence, but if the transaction fails
On 8/12/06, Jorge Godoy <[EMAIL PROTECTED]> wrote:
Hi!
I was trying to solve a problem on an old system and realized that there might
be some better approach for doing what I need.
We have some documents that need to be ordered sequentially and without gaps.
I could use a sequence, but if the
Hi!
I was trying to solve a problem on an old system and realized that there might
be some better approach for doing what I need.
We have some documents that need to be ordered sequentially and without gaps.
I could use a sequence, but if the transaction fails then when I rollback the
sequence
52 matches
Mail list logo
