Обсуждение: sequence skips 30 values, how?
We are trying to track down an issue with our PostgreSQL application. We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. We have a situation where the postgres backend process drops core and dies. We've tracked this to an unusual situation where a sequence value that is being created during the process that is causing the core file generation. The thing that is bizarre is that the sequence value skips 30+ entries. How is this even possible? Any suggestions would be greatly appreciated. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
am Wed, dem 31.01.2007, um 7:43:05 -0500 mailte Geoffrey folgendes: > dies. We've tracked this to an unusual situation where a sequence value > that is being created during the process that is causing the core file > generation. The thing that is bizarre is that the sequence value skips > 30+ entries. > > How is this even possible? Any suggestions would be greatly appreciated. A sequence don't guaranteed a gapless behavior. Simple example: test=# create table gap (id serial); NOTICE: CREATE TABLE will create implicit sequence "gap_id_seq" for serial column "gap.id" CREATE TABLE test=*# insert into gap values (default) test-# ; INSERT 0 1 test=*# commit; COMMIT test=# insert into gap values (default); INSERT 0 1 test=*# insert into gap values (default); INSERT 0 1 test=*# rollback; ROLLBACK test=# insert into gap values (default); INSERT 0 1 test=*# commit; COMMIT test=# select * from gap; id ---- 1 4 (2 rows) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Geoffrey wrote: > We are trying to track down an issue with our PostgreSQL application. > We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. > > We have a situation where the postgres backend process drops core and > dies. We've tracked this to an unusual situation where a sequence value > that is being created during the process that is causing the core file > generation. The thing that is bizarre is that the sequence value skips > 30+ entries. Sequences are WAL-logged in chunks. So if you ask for a number and then the process crashes, on recovery the sequence will resume generating numbers at the start of the next chunk. I very much doubt that this causes the crash itself; you should keep looking. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Geoffrey wrote: > We are trying to track down an issue with our PostgreSQL application. We > are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. > > We have a situation where the postgres backend process drops core and > dies. We've tracked this to an unusual situation where a sequence value > that is being created during the process that is causing the core file > generation. The thing that is bizarre is that the sequence value skips > 30+ entries. > > How is this even possible? Any suggestions would be greatly appreciated. Why is that bizarre? If in the crashing session 30+ records were inserted - or more accurately, if nextval() was called 30+ times on that particular sequence - of course the sequence skips 30+ values. Nothing strange about that. The backend crashes from something you do in that session. -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
Hi, Geoffrey wrote: > We are trying to track down an issue with our PostgreSQL application. We > are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. > > We have a situation where the postgres backend process drops core and > dies. Are there some log messages of the dying process, especially just before it dies? Did you try to open the core file in a debugger? What do you do in that backend to make it crash? Regards Markus
In response to Geoffrey <esoteric@3times25.net>: > We are trying to track down an issue with our PostgreSQL application. > We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. > > We have a situation where the postgres backend process drops core and > dies. We've tracked this to an unusual situation where a sequence value > that is being created during the process that is causing the core file > generation. The thing that is bizarre is that the sequence value skips > 30+ entries. > > How is this even possible? Any suggestions would be greatly appreciated. Don't know why your workers are dropping cores: backtraces and the like would probably help sort that out. However, when a transaction requests a new sequence, then aborts (for whatever reason) that sequence isn't going to back up. My understanding is that the overhead to making sequences transaction aware and able to avoid gaps is more than anyone has determined the benefit to be. What is the problem with gaps? If you're afraid of running out of numbers, switch to BIGSERIAL. -- Bill Moran Collaborative Fusion Inc.
On 1/31/07, Geoffrey <esoteric@3times25.net> wrote: > We are trying to track down an issue with our PostgreSQL application. > We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. > > We have a situation where the postgres backend process drops core and > dies. We've tracked this to an unusual situation where a sequence value > that is being created during the process that is causing the core file > generation. The thing that is bizarre is that the sequence value skips > 30+ entries. > > How is this even possible? Any suggestions would be greatly appreciated. > In addition to previous suggestions (rollback/server crashes), perhaps a hint where you can look at. I personally had an experience like this with sequences in Oracle. An default oracle sequence caches 20 records in memory, which caused the unused records to be "gone" on a server reboot, and worse, in case the sequence was "swapped" out of memory because it wasn't used for a while. I don't know if you used the "cache" statement while creating your sequence. But in case you did, this can also explain why you have "missing" sequence records. But you must have explicitly set the cache option, since the default of postgresql is 1 [1], not 20 [2]. Please note, my findings with cache are based on the behavour of Oracle. I didn't test this with postgresql. Regards, Wessel van Norel [1] http://www.postgresql.org/docs/8.1/interactive/sql-createsequence.html [2] http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_6014.htm#sthref5342
Geoffrey <esoteric@3times25.net> writes:
> We are trying to track down an issue with our PostgreSQL application.
> We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3.
> We have a situation where the postgres backend process drops core and
> dies.
Please provide a stack trace from that coredump ...
regards, tom lane
Slightly OT. That documentation page of postgresql contains an invalid
example. Not sure if I should report it in here, but well, there you
go.
CREATE SEQUENCE serial START 101;
SELECT nextval('serial');
nextval
---------
114
So you start at 101 and get 114, how nice ;-)
Regards,
Wessel van Norel
Tom Lane wrote: > Geoffrey <esoteric@3times25.net> writes: >> We are trying to track down an issue with our PostgreSQL application. >> We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. > >> We have a situation where the postgres backend process drops core and >> dies. > > Please provide a stack trace from that coredump ... > > regards, tom lane It follows. Note, the references to /usr/local/pcm170/... are from a 3rd party application we have built into our backend. I'm sure I know what I'll hear regarding that issue. :) We are in the process up upgrading to a newer version of this product. stack trace follows: #0 0x001968a4 in vfprintf () from /lib/tls/libc.so.6 #1 0x001b7d24 in vsnprintf () from /lib/tls/libc.so.6 #2 0x0019d0a4 in snprintf () from /lib/tls/libc.so.6 #3 0x002d29c2 in pcm_getmiles_s () from /esc/pgrnd/prog/libpcmiler.so #4 0x081082c4 in ExecMakeFunctionResult () #5 0x08109e1a in ExecEvalExpr () #6 0x08107f5d in init_fcache () #7 0x08108062 in ExecMakeFunctionResult () #8 0x08109e1a in ExecEvalExpr () #9 0x0810a089 in ExecEvalExprSwitchContext () #10 0x002c7d30 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #11 0x002c6b00 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #12 0x002c42a8 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #13 0x002c40c9 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #14 0x002c4015 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #15 0x002c4502 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #16 0x002c40da in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #17 0x002c4015 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #18 0x002c3e25 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #19 0x002c3071 in plpgsql_exec_function () from /usr/lib/pgsql/plpgsql.so #20 0x002c0004 in plpgsql_call_handler () from /usr/lib/pgsql/plpgsql.so #21 0x081082c4 in ExecMakeFunctionResult () #22 0x08109e1a in ExecEvalExpr () ---Type <return> to continue, or q <return> to quit--- #23 0x0810ab54 in ExecCleanTargetListLength () #24 0x0810adba in ExecProject () #25 0x0810ae88 in ExecScan () #26 0x081105d9 in ExecIndexScan () #27 0x08106d70 in ExecProcNode () #28 0x081055fd in ExecEndPlan () #29 0x081049d8 in ExecutorRun () #30 0x08118794 in spi_printtup () #31 0x08118686 in spi_printtup () #32 0x08116b62 in SPI_execp () #33 0x002c598f in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #34 0x002c4193 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #35 0x002c4015 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #36 0x002c4502 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #37 0x002c40da in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #38 0x002c4015 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #39 0x002c4502 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #40 0x002c40da in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #41 0x002c4015 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #42 0x002c4502 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #43 0x002c40da in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #44 0x002c4015 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #45 0x002c4502 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so ---Type <return> to continue, or q <return> to quit--- #46 0x002c40da in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #47 0x002c4015 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #48 0x002c4502 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #49 0x002c40da in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #50 0x002c4015 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #51 0x002c3e25 in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #52 0x002c378a in plpgsql_exec_trigger () from /usr/lib/pgsql/plpgsql.so #53 0x002c0063 in plpgsql_call_handler () from /usr/lib/pgsql/plpgsql.so #54 0x080f283e in FreeTriggerDesc () #55 0x080f37b7 in ExecARUpdateTriggers () #56 0x080f3bbe in ExecARUpdateTriggers () #57 0x080f3d1f in DeferredTriggerEndQuery () #58 0x08179d08 in pg_plan_queries () #59 0x08178904 in pg_plan_queries () #60 0x0817b1c1 in PostgresMain () #61 0x08154c40 in ClosePostmasterPorts () #62 0x08154633 in ClosePostmasterPorts () #63 0x08152b98 in PostmasterMain () #64 0x0815225e in PostmasterMain () #65 0x08121f63 in main () -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
DelGurth <delgurth@gmail.com> writes:
> Slightly OT. That documentation page of postgresql contains an invalid
> example. Not sure if I should report it in here, but well, there you
> go.
> CREATE SEQUENCE serial START 101;
> SELECT nextval('serial');
> nextval
> ---------
> 114
> So you start at 101 and get 114, how nice ;-)
I think the assumption is that some time has passed (and some use of the
sequence) in between those steps.
regards, tom lane
Geoffrey <esoteric@3times25.net> writes:
> Tom Lane wrote:
>> Please provide a stack trace from that coredump ...
> It follows. Note, the references to /usr/local/pcm170/... are from a
> 3rd party application we have built into our backend. I'm sure I know
> what I'll hear regarding that issue. :) We are in the process up
> upgrading to a newer version of this product. stack trace follows:
> #0 0x001968a4 in vfprintf () from /lib/tls/libc.so.6
> #1 0x001b7d24 in vsnprintf () from /lib/tls/libc.so.6
> #2 0x0019d0a4 in snprintf () from /lib/tls/libc.so.6
> #3 0x002d29c2 in pcm_getmiles_s () from /esc/pgrnd/prog/libpcmiler.so
> #4 0x081082c4 in ExecMakeFunctionResult ()
Looks to me like pcm_getmiles_s is passing a bad pointer to snprintf,
or perhaps providing an insufficiently large output buffer. You'll
need to take it up with the author of that function --- it's highly
unlikely that this is a bug in the core Postgres code.
regards, tom lane
As others have said, sequences can have gaps. In fact, the thought of a gap-free sequence is scary to me. Unless you do very few inserts, "gap-free sequence" is pretty much synonymous with "not scalable". If your goal is to generate a unique number for each row (which is usually the case), then gaps should be fine. Though I must admit I have occasionally wished for sequences with a GAPFREE option...For small, static look-up tables that I update once in a blue moon. It's just easier on the eyes to have 1,2,3,4,5 than 1, 25, 2405, 95720, 59028598253. Mark On Jan 31, 7:43 am, esote...@3times25.net (Geoffrey) wrote: > We are trying to track down an issue with our PostgreSQL application. > We are running PostgreSQL 7.4.13 on Red Hat Enterprise ES 3. > > We have a situation where the postgres backend process drops core and > dies. We've tracked this to an unusual situation where a sequence value > that is being created during the process that is causing the core file > generation. The thing that is bizarre is that the sequence value skips > 30+ entries. > > How is this even possible? Any suggestions would be greatly appreciated. > > -- > Until later, Geoffrey
"Angva" <angvaw@gmail.com> writes: > As others have said, sequences can have gaps. In fact, the thought of > a gap-free sequence is scary to me. Unless you do very few inserts, > "gap-free sequence" is pretty much synonymous with "not scalable". If > your goal is to generate a unique number for each row (which is > usually the case), then gaps should be fine. > > Though I must admit I have occasionally wished for sequences with a > GAPFREE option...For small, static look-up tables that I update once > in a blue moon. It's just easier on the eyes to have 1,2,3,4,5 than 1, > 25, 2405, 95720, 59028598253. There's a recipe for gapless sequences on Varlena (if you can access it from your end...) that was the result of discussions here. There's a performance hit, of course, but it works. -- Jorge Godoy <jgodoy@gmail.com>