Re: [SQL] strangest thing happened
Joe Conway schrieb: I am the only developer, DBA etc.. for a small project. Today (yesterday was everything was perfect) many of the sequence numbers fell behind what is the actual PK value. For example the invoice PK sequence current value = 1056 but the table PK was 1071. Nobody (other than myself) knows how to edit/access the postgres server. So 1. Does anyone know how this could have happened?? Other than human interaction. I've never heard of this happening. Are you certain nothing bypassed the sequence and directly inserted a PK value? Maybe the sequence is defined with "cycle" and starts again? Very unlikly, but a possibility. Otherwise i would expect an bad backup/restore, direct insert of serials or manipulated them afterwards (by insert, update or an function). Gretings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
On 7/7/2010 5:41 PM, John wrote:
> On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote:
>
>> I would be looking at the log files for the Inserts into that table as a
>> means to track down what is the cause. If there are no log files or
>> don't have enough detail, crank up the logging level and wait for it to
>> happen again???
>>
>
> That is scary - let it happen again I'm not keeping enough info in the
> log. I actually turned off most of the info the log files are gathering
> because the system has been running for 6-7 months without an issue. I just
> got a call around noon telling me something was going wrong. That's when I
> discovered the sequences were the wrong values. I'm sure there has to be
> some sort of real explanation - but I don't know what it is.
>
> Johnf
>
>
Sometimes we just don't have a choice but to let things become broke
again to figure out what is the cause.
I had an odd case where Parent records could become deleted every once
in a while. For what appeared to be no rhyme or reason
The app had around 1000 functions in pg/psql not counting triggers, so
figuring out the cause by just staring at the code was not practical.
The log was already was set to record
log_statement (all)
log_line_prefix ('User %u, DB%d, Client%r, PID %p, Time %m, SID %c,
LineCount %l , TID %x);
log_destination ('csvlog')
The logs rotated out every 30 days. So I had good sample of the
commands sent to PG to figure out what went wrong
As the statements are logged all i had to do was search for
Delete from cohead where cohead_id =
this gave me the transaction ID and the Session ID to start backtracking
to see if a Trigger or Function issued the delete. Once I had figured
out that it was pg/psql procedure, i needed to figure out where in the
application called this seriously miss thought out DELETE Sales Order
function. I threw in a RAISE EXCEPTION in the psql and waited for the
Data entry people to come screaming.
Around 3 weeks later a data entry girl came and found me asking to
explain why her computer ordered her to come find me ASAP and why
nothing else matter but stop and find me.
With that error, I now the call stack from the app to figure out the
serious of events that allowed a Sales Order to be deleted.
After all was said and done there was a total of 50 records deleted out
of 60,000 (not even 0.1%) not a big deal unless you are customer who's
order was deleted.
My experience has taught me never turn off logging because we never when
we may need it.
All legitimate Magwerks Corporation quotations are sent in a .PDF file
attachment with a unique ID number generated by our proprietary quotation
system. Quotations received via any other form of communication will not be
honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally
privileged, confidential or other information proprietary to Magwerks
Corporation and is intended solely for the use of the individual to whom it
addresses. If the reader of this e-mail is not the intended recipient or
authorized agent, the reader is hereby notified that any unauthorized viewing,
dissemination, distribution or copying of this e-mail is strictly prohibited.
If you have received this e-mail in error, please notify the sender by replying
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
<>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
Quoth jo...@jfcomputer.com (John): > On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote: > > I would be looking at the log files for the Inserts into that table as a > > means to track down what is the cause. If there are no log files or > > don't have enough detail, crank up the logging level and wait for it to > > happen again??? > > > That is scary - let it happen again I'm not keeping enough info in the > log. I actually turned off most of the info the log files are gathering > because the system has been running for 6-7 months without an issue. I just > got a call around noon telling me something was going wrong. That's when I > discovered the sequences were the wrong values. I'm sure there has to be > some sort of real explanation - but I don't know what it is. There are several possible causes: - Something somewhere is inserting values directly into the serial columns, without using the sequence. This can be prevented by REVOKEing INSERT and UPDATE on the relevant columns for all users. If this causes problems anywhere in your app, those are good places to start looking for bugs. - Something somewhere is manipulating the sequence. This can be prevented by REVOKEing UPDATE on all sequences for all users. You may need some additional GRANTs of USAGE on sequences if parts of the app were relying on UPDATE to call nextval(). Obviously if your app routinely drops and creates tables you will need to arrange for these permissions to be applied every time. - The database has become corrupted, perhaps by a badly-done backup and restore. (I would not expect taking a backup alone to cause corruption, but if the backup isn't done right the backed-up copy may be corrupt.) Have you done a restore recently? - Something I haven't thought of :). - A bug in Pg. While this is *extremely* unlikely, it must be mentioned as a possibility. Ben -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
On Wed, Jul 7, 2010 at 9:59 PM, John wrote: > I am the only developer, DBA etc.. for a small project. Today (yesterday > was > everything was perfect) many of the sequence numbers fell behind what is > the > actual PK value. For example the invoice PK sequence current value = 1056 > but the table PK was 1071. Nobody (other than myself) knows how to > edit/access the postgres server. So > > 1. Does anyone know how this could have happened?? Other than human > interaction. > > 2. Does anyone have a script to reset the sequences to match the tables? > > Thanks in advance, > > Johnf > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > John, If the insert is performed to triggered table, and that trigger returns null (doesn't insert) then the sequence will increment, but no data will be inserted. If needed I will send you some code examples where it happened to me many times. Sincerely -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
Re: [SQL] strangest thing happened
On Wednesday 07 July 2010 03:14:40 pm Justin Graf wrote: > I would be looking at the log files for the Inserts into that table as a > means to track down what is the cause. If there are no log files or > don't have enough detail, crank up the logging level and wait for it to > happen again??? That is scary - let it happen again I'm not keeping enough info in the log. I actually turned off most of the info the log files are gathering because the system has been running for 6-7 months without an issue. I just got a call around noon telling me something was going wrong. That's when I discovered the sequences were the wrong values. I'm sure there has to be some sort of real explanation - but I don't know what it is. Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
On 7/7/2010 3:42 PM, Ross J. Reedstrom wrote: > > Justin, you're missing that John reported that the sequences are > _behind_ the table. This only happens for me if I've been doing > bulk data loads. Then I use: > > select setval(sequence_name,max(serial_id_column)) from table_with_serial_id; > > You do need to trackdown how this might have happened, though. Any > clever code doing it's own 'serial' incrementing? > > Ross > Yes i did miss read his statement, oops =-O The highest PK value in the table is 1071 but the next sequence is 1056. That's interesting and could be a big problem Quoteing JonF I'm thinking/guessing it had something to do with vacumn or the backup. The backup is a windows product "exec" and I'm using a special plug-in from exec for the Linux backup. But I still can't see this actually happening. -- BakupExec HMMM. Are you doing a file level backup, meaning backing up PGDATA folder or are you doing pg_dump?? I don't think its a backup issue, unless you have done a restore. Which this would say there are more problems else where Are there invoices that use up numbers 1056 to 1071 in that table??? Does the app allow for resetting Sequence in a admin interface??? Many apps have such features and someone could have accidentally rest the value??? I would be looking at the log files for the Inserts into that table as a means to track down what is the cause. If there are no log files or don't have enough detail, crank up the logging level and wait for it to happen again??? All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. <> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
On 07/07/2010 12:59 PM, John wrote:
> I am the only developer, DBA etc.. for a small project. Today (yesterday was
> everything was perfect) many of the sequence numbers fell behind what is the
> actual PK value. For example the invoice PK sequence current value = 1056
> but the table PK was 1071. Nobody (other than myself) knows how to
> edit/access the postgres server. So
>
> 1. Does anyone know how this could have happened?? Other than human
> interaction.
I've never heard of this happening. Are you certain nothing bypassed the
sequence and directly inserted a PK value?
> 2. Does anyone have a script to reset the sequences to match the tables?
Not heavily tested, but something like this might do the trick:
8<--
CREATE OR REPLACE FUNCTION adjust_seqs(namespace text)
RETURNS text AS $$
DECLARE
rec record;
startvalbigint;
sql text;
seqname text;
BEGIN
FOR rec in EXECUTE 'select table_name, column_name, column_default
from information_schema.columns
where table_schema = ''' || namespace || '''
and column_default like ''nextval%''' LOOP
seqname := pg_get_serial_sequence(rec.table_name, rec.column_name);
sql := 'select max(' || rec.column_name || ') + 1 from ' ||
rec.table_name;
EXECUTE sql INTO startval;
IF startval IS NOT NULL THEN
sql := 'ALTER SEQUENCE ' || seqname || ' RESTART WITH ' ||
startval;
EXECUTE sql;
RAISE NOTICE '%', sql;
END IF;
END LOOP;
RETURN 'OK';
END;
$$ LANGUAGE plpgsql STRICT;
select adjust_seqs('public');
8<--
HTH,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support
signature.asc
Description: OpenPGP digital signature
Re: [SQL] strangest thing happened
On Wed, Jul 07, 2010 at 04:25:13PM -0500, Justin Graf wrote:
> Are you using PG's sequence/auto increment???
>
> If so.
> Once PG fires off the nextval() for the sequence that number is
> considered used and gone even if the transaction that called nextval()
> is rolled back
>
> Depending on how the app is written nextval() might be called, but allow
> the User to cancel the invoice creation before the insert into table is
> completed eating up Invoice numbers
>
> To reset Sequences number call
> Select setval('Sequence_Name', VAlue_To_Set_To);
>
> Most people ignore this kind of annoyance when sequence numbers jump.
> Now if it happens all the time where every X hours eating up Z number of
> sequence numbers then one needs to dig into the logs and figure out what
> is calling nextval()
>
> Search the logs to see what is calling nextval('My_Sequence')
>
> You may need to turn up logging to find it.
Justin, you're missing that John reported that the sequences are
_behind_ the table. This only happens for me if I've been doing
bulk data loads. Then I use:
select setval(sequence_name,max(serial_id_column)) from table_with_serial_id;
You do need to trackdown how this might have happened, though. Any
clever code doing it's own 'serial' incrementing?
Ross
--
Ross Reedstrom, Ph.D. reeds...@rice.edu
Systems Engineer & Admin, Research Scientistphone: 713-348-6166
The Connexions Project http://cnx.orgfax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
>
>
> On 7/7/2010 2:59 PM, John wrote:
> > I am the only developer, DBA etc.. for a small project. Today (yesterday
> > was
> > everything was perfect) many of the sequence numbers fell behind what is the
> > actual PK value. For example the invoice PK sequence current value = 1056
> > but the table PK was 1071. Nobody (other than myself) knows how to
> > edit/access the postgres server. So
> >
> > 1. Does anyone know how this could have happened?? Other than human
> > interaction.
> >
> > 2. Does anyone have a script to reset the sequences to match the tables?
> >
> > Thanks in advance,
> >
> > Johnf
> >
> >
>
>
>
> All legitimate Magwerks Corporation quotations are sent in a .PDF file
> attachment with a unique ID number generated by our proprietary quotation
> system. Quotations received via any other form of communication will not be
> honored.
>
> CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain
> legally privileged, confidential or other information proprietary to Magwerks
> Corporation and is intended solely for the use of the individual to whom it
> addresses. If the reader of this e-mail is not the intended recipient or
> authorized agent, the reader is hereby notified that any unauthorized
> viewing, dissemination, distribution or copying of this e-mail is strictly
> prohibited. If you have received this e-mail in error, please notify the
> sender by replying to this message and destroy all occurrences of this e-mail
> immediately.
> Thank you.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
Yes I'm using auto_increment (serial data type). No function, method inserts
PK's anywhere in my code. I'm thinking/guessing it had something to do with
vacumn or the backup. I have been using Postgres for a number of years now
and I never seen this in the past. So I'm really at a loss as how this could
have occurred. The backup is a windows product "exec" and I'm using a
special plug-in from exec for the Linux backup. But I still can't see this
actually happening.
Well I have it running for the moment and I'll have to account black magic as
the cause.
Johnf
On Wednesday 07 July 2010 02:25:13 pm Justin Graf wrote:
> Are you using PG's sequence/auto increment???
>
> If so.
> Once PG fires off the nextval() for the sequence that number is
> considered used and gone even if the transaction that called nextval()
> is rolled back
>
> Depending on how the app is written nextval() might be called, but allow
> the User to cancel the invoice creation before the insert into table is
> completed eating up Invoice numbers
>
> To reset Sequences number call
> Select setval('Sequence_Name', VAlue_To_Set_To);
>
> Most people ignore this kind of annoyance when sequence numbers jump.
> Now if it happens all the time where every X hours eating up Z number of
> sequence numbers then one needs to dig into the logs and figure out what
> is calling nextval()
>
> Search the logs to see what is calling nextval('My_Sequence')
>
> You may need to turn up logging to find it.
>
> On 7/7/2010 2:59 PM, John wrote:
> > I am the only developer, DBA etc.. for a small project. Today (yesterday
> > was everything was perfect) many of the sequence numbers fell behind what
> > is the actual PK value. For example the invoice PK sequence current
> > value = 1056 but the table PK was 1071. Nobody (other than myself) knows
> > how to edit/access the postgres server. So
> >
> > 1. Does anyone know how this could have happened?? Other than human
> > interaction.
> >
> > 2. Does anyone have a script to reset the sequences to match the tables?
> >
> > Thanks in advance,
> >
> > Johnf
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] strangest thing happened
Are you using PG's sequence/auto increment???
If so.
Once PG fires off the nextval() for the sequence that number is
considered used and gone even if the transaction that called nextval()
is rolled back
Depending on how the app is written nextval() might be called, but allow
the User to cancel the invoice creation before the insert into table is
completed eating up Invoice numbers
To reset Sequences number call
Select setval('Sequence_Name', VAlue_To_Set_To);
Most people ignore this kind of annoyance when sequence numbers jump.
Now if it happens all the time where every X hours eating up Z number of
sequence numbers then one needs to dig into the logs and figure out what
is calling nextval()
Search the logs to see what is calling nextval('My_Sequence')
You may need to turn up logging to find it.
On 7/7/2010 2:59 PM, John wrote:
> I am the only developer, DBA etc.. for a small project. Today (yesterday was
> everything was perfect) many of the sequence numbers fell behind what is the
> actual PK value. For example the invoice PK sequence current value = 1056
> but the table PK was 1071. Nobody (other than myself) knows how to
> edit/access the postgres server. So
>
> 1. Does anyone know how this could have happened?? Other than human
> interaction.
>
> 2. Does anyone have a script to reset the sequences to match the tables?
>
> Thanks in advance,
>
> Johnf
>
>
All legitimate Magwerks Corporation quotations are sent in a .PDF file
attachment with a unique ID number generated by our proprietary quotation
system. Quotations received via any other form of communication will not be
honored.
CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally
privileged, confidential or other information proprietary to Magwerks
Corporation and is intended solely for the use of the individual to whom it
addresses. If the reader of this e-mail is not the intended recipient or
authorized agent, the reader is hereby notified that any unauthorized viewing,
dissemination, distribution or copying of this e-mail is strictly prohibited.
If you have received this e-mail in error, please notify the sender by replying
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
<>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] strangest thing happened
I am the only developer, DBA etc.. for a small project. Today (yesterday was everything was perfect) many of the sequence numbers fell behind what is the actual PK value. For example the invoice PK sequence current value = 1056 but the table PK was 1071. Nobody (other than myself) knows how to edit/access the postgres server. So 1. Does anyone know how this could have happened?? Other than human interaction. 2. Does anyone have a script to reset the sequences to match the tables? Thanks in advance, Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
