Lists: | pgsql-general |
---|
From: | David <david(at)vanlaatum(dot)id(dot)au> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | ian(at)niw(dot)com(dot)au |
Subject: | Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 05:48:00 |
Message-ID: | 20090428054800.GA17477@daboyz.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Our internal task database is doing something odd in that the sequence is incrementing by 2 instead of 1 and I can't find any reason why I have checked the
sequence itself to see if it had somehow got set to increment by 2 but no. The table in question has a number of both before and after triggers that do
various bits of business logic as well as some rewrite rules to track field changes. Using 8.3.0. Can anyone think of a reason why?
--
From: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 06:17:08 |
Message-ID: | gt6711$kmp$1@ger.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
David, 28.04.2009 07:48:
> Our internal task database is doing something odd in that the
> sequence is incrementing by 2 instead of 1 and I can't find any
> reason why I have checked the sequence itself to see if it had
> somehow got set to increment by 2 but no. The table in question has a
> number of both before and after triggers that do various bits of
> business logic as well as some rewrite rules to track field changes.
> Using 8.3.0. Can anyone think of a reason why?
>
My first guess would be rolled back transactions
Thomas
From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 06:24:46 |
Message-ID: | 20090428062446.GB13320@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
In response to David :
> Our internal task database is doing something odd in that the sequence is incrementing by 2 instead of 1 and I can't find any reason why I have checked the
> sequence itself to see if it had somehow got set to increment by 2 but no. The table in question has a number of both before and after triggers that do
> various bits of business logic as well as some rewrite rules to track field changes. Using 8.3.0. Can anyone think of a reason why?
Wild guess: there are an other call to nextval() in one of the triggers.
But without the code it is hard to guess...
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
From: | David <david(at)vanlaatum(dot)id(dot)au> |
---|---|
To: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 06:38:19 |
Message-ID: | 20090428063819.GB17477@daboyz.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Looked for that can't find anything and there is no reason why someone would have added that.
On Tue, Apr 28, 2009 at 08:24:46AM +0200, A. Kretschmer wrote:
> In response to David :
> > Our internal task database is doing something odd in that the sequence is incrementing by 2 instead of 1 and I can't find any reason why I have checked the
> > sequence itself to see if it had somehow got set to increment by 2 but no. The table in question has a number of both before and after triggers that do
> > various bits of business logic as well as some rewrite rules to track field changes. Using 8.3.0. Can anyone think of a reason why?
>
> Wild guess: there are an other call to nextval() in one of the triggers.
> But without the code it is hard to guess...
>
>
> Regards, Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | David <david(at)vanlaatum(dot)id(dot)au> |
Cc: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 07:01:39 |
Message-ID: | dcc563d10904280001n13857088xe50ab33b5db850db@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Tue, Apr 28, 2009 at 12:38 AM, David <david(at)vanlaatum(dot)id(dot)au> wrote:
> Looked for that can't find anything and there is no reason why someone would have added that.
Well, unless you've got some example code for us to look at, it's
kinda hard to tell you what's wrong.
Often, you see people do something like this:
insert into autoincrementingtable values (DEFAULT, 'a', 500);
select nextval('autoinctable_seq');
yada yada yada
or something like that. i.e. there's an implicit call to the nextval
with the insert, and another explicit one somewhere in your code.
From: | David <david(at)vanlaatum(dot)id(dot)au> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 07:19:55 |
Message-ID: | 20090428071955.GC17477@daboyz.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Ok got permission to send the code I think I have got all the relevent bits.
On Tue, Apr 28, 2009 at 01:01:39AM -0600, Scott Marlowe wrote:
> On Tue, Apr 28, 2009 at 12:38 AM, David <david(at)vanlaatum(dot)id(dot)au> wrote:
> > Looked for that can't find anything and there is no reason why someone would have added that.
>
> Well, unless you've got some example code for us to look at, it's
> kinda hard to tell you what's wrong.
>
> Often, you see people do something like this:
>
> insert into autoincrementingtable values (DEFAULT, 'a', 500);
> select nextval('autoinctable_seq');
> yada yada yada
>
> or something like that. i.e. there's an implicit call to the nextval
> with the insert, and another explicit one somewhere in your code.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Attachment | Content-Type | Size |
---|---|---|
sql.txt | text/plain | 17.4 KB |
From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|
To: | "David" <david(at)vanlaatum(dot)id(dot)au> |
Cc: | pgsql-general(at)postgresql(dot)org,ian(at)niw(dot)com(dot)au |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 07:31:26 |
Message-ID: | cfafb930-5dd9-4d48-a3ef-9e5131c12466@mm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
David wrote:
> the sequence is incrementing by 2 instead of 1 and I can't
> find any reason why I have checked the
> sequence itself to see if it had somehow got set to increment
> by 2 but no.
> The table in question has a number of both before and after triggers
> that do various bits of business logic as well as some rewrite rules
> to track field changes.
> Using 8.3.0. Can anyone think of a reason why?
I'd say rewrite rules are your prime suspect.
Check out for example:
http://archives.postgresql.org/pgsql-sql/2007-03/msg00334.php
Cordialement,
--
Daniel
From: | David <david(at)vanlaatum(dot)id(dot)au> |
---|---|
To: | Daniel Verite <daniel(at)manitou-mail(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org, ian(at)niw(dot)com(dot)au |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 07:36:14 |
Message-ID: | 20090428073614.GD17477@daboyz.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Cool thanks that makes sense was hard to pin down because we didn't notice for a while.
On Tue, Apr 28, 2009 at 09:31:26AM +0200, Daniel Verite wrote:
> David wrote:
>
> >the sequence is incrementing by 2 instead of 1 and I can't
> >find any reason why I have checked the
> >sequence itself to see if it had somehow got set to increment
> >by 2 but no.
> >The table in question has a number of both before and after triggers
> >that do various bits of business logic as well as some rewrite rules
> >to track field changes.
> >Using 8.3.0. Can anyone think of a reason why?
>
> I'd say rewrite rules are your prime suspect.
> Check out for example:
> http://archives.postgresql.org/pgsql-sql/2007-03/msg00334.php
>
> Cordialement,
> --
> Daniel
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 11:37:26 |
Message-ID: | gt6ppm$j17$2@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 2009-04-28, David <david(at)vanlaatum(dot)id(dot)au> wrote:
> Our internal task database is doing something odd in that the sequence is incrementing by 2 instead of 1 and I can't find any reason why I have checked the
> sequence itself to see if it had somehow got set to increment by 2 but no. The table in question has a number of both before and after triggers that do
> various bits of business logic as well as some rewrite rules to track field changes. Using 8.3.0. Can anyone think of a reason why?
possibly a rule involves the the sequence, thus it's value is computed twice.
From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Sequence Incrementing by 2 insted of 1 |
Date: | 2009-04-28 11:58:20 |
Message-ID: | gt6r0s$kao$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 2009-04-28, David <david(at)vanlaatum(dot)id(dot)au> wrote:
> Ok got permission to send the code I think I have got all the relevent bits.
>
>CREATE TABLE currentcall (
> cadc_taskno integer DEFAULT nextval(('currentcall_cadc_taskno_seq'::text)::regclass) NOT NULL,
> coversight character varying(4),
...
>CREATE RULE currentcall_insert AS ON INSERT TO currentcall DO INSERT INTO
> taskchangelog (tasknum, newstatus, newtech, status, newpriority,
> newestcomplete, newclass, newnewtask) VALUES (new.cadc_taskno,
^^^^^^^^^^^^^^^
> new.cstatus, new.ctechalloc, new.ccustcomment, new.cfprior,
> new.cestcomplete, new.cjobclass, (new.cnewtask = 'Y'::bpchar));
if you're inserting DEFAULT into the cadc_taskno column (or not
explicitly setting that column) the re-writer will substitute
nextval(('currentcall_cadc_taskno_seq'::text)::regclass)
And that will be evaluated once for the insert and again for the rule.
this is documented somewhere.... I think