Message-ID: <4C34E764.3040703@joeconway.com>
Date: Wed, 07 Jul 2010 13:45:24 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.8.0.12) Gecko/20071019 Fedora/1.5.0.12-3.fc6 pango-text
	Thunderbird/1.5.0.12 Mnenhy/0.7.5.666
MIME-Version: 1.0
To: John <johnf@jfcomputer.com>
CC: pgsql-sql@postgresql.org
Subject: Re: strangest thing happened
References: <201007071259.03294.johnf@jfcomputer.com>
In-Reply-To: <201007071259.03294.johnf@jfcomputer.com>
OpenPGP: id=B699434A
Content-Type: multipart/signed; micalg=pgp-sha256;
	protocol="application/pgp-signature";
	boundary="------------enigDB45A76D8D494AB1EAC6616C"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigDB45A76D8D494AB1EAC6616C
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On 07/07/2010 12:59 PM, John wrote:
> I am the only developer, DBA etc.. for a small project.  Today (yesterd=
ay was=20
> everything was perfect) many of the sequence numbers fell behind what i=
s the=20
> actual PK value.   For example the invoice PK sequence current value =3D=
 1056=20
> but the table PK was 1071.  Nobody (other than myself) knows how to=20
> edit/access the postgres server.  So
>=20
> 1. Does anyone know how this could have happened?????? Other than human=
=20
> 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=
?=20

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;
  startval    bigint;
  sql         text;
  seqname     text;
BEGIN
  FOR rec in EXECUTE 'select table_name, column_name, column_default
                      from information_schema.columns
                      where table_schema =3D ''' || namespace || '''
                      and column_default like ''nextval%''' LOOP

    seqname :=3D pg_get_serial_sequence(rec.table_name, rec.column_name);=

    sql :=3D 'select max(' || rec.column_name || ') + 1 from ' ||
                                                        rec.table_name;
    EXECUTE sql INTO startval;
    IF startval IS NOT NULL THEN
      sql :=3D '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

--=20
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & Support


--------------enigDB45A76D8D494AB1EAC6616C
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.14 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iQIcBAEBCAAGBQJMNOdkAAoJEDfy90M199hlzeIP/i7Y5qwzjJQulKDMYJcONyCD
V+MBwJmS2exw0Qjhgz1XaVIx8DW3iKsCc1er4usOoOFlMm6uolC2H1OTIaHGaLMr
8w4ZRNojJWXgF/+CwN2+Nn5xhBtmQyyVqy2twAd2Y3lEhi718KObLcv6xGKD9U73
qb8eGhiNaYGQSaRXSGAkP/NMSCudSqQDauQG1omv19qERAwbBPA1TQiUrW6bqgAW
g/auw82GmLhPGcqaeqEPPygO8RmePrEO4jQs37IGx/UpfoMwgF5YrvnB4WivHRfP
AuPU6+9rMCRLjKLnC3TgQZbmLy4+Ch5M57Kzx6o7UxzO6xluFQPdHyND5l4AKwUl
kKtsYbnOgd9wKYlXIiORpAJxbfsKXz0jcRYUPAC0Fo1GhfEp7CBMFBchZ4U8eCJ1
E90WpiwaFOBLhxAgZ904FLR26Ftb4GaFELIiDgOL8bnqLXmunLmqiKG8AjxpXAtF
0VDxCH+3bNXQpjR7MmytKy3mGFWgBCQurNSuMQqowjXuxlwKnJC6KulUY23hL+35
YK76/mtA8XDEIrmzTyw7UFqErsG2YyagFFCiq3+CDMuIDZiYIvnaiIJoMqiu0oTX
aDcbwsS4ZjWGw6jeXZulC5zGNdGFuo9fBc8KsueJpvd97W6Fa8R30uzOlrrliPFB
UIDjvLnyEqgBgiQYmtx1
=jJLD
-----END PGP SIGNATURE-----

--------------enigDB45A76D8D494AB1EAC6616C--