signifance of these freeside-daily errors? (not vacuum warnings)

[Scott Edwards]

Well this is not the first or the last occurance of such an error.

Other similar errors:

ERROR: duplicate key violates unique constraint "domain_record_pkey"
ERROR: duplicate key violates unique constraint "cust_svc_pkey"
ERROR: duplicate key violates unique constraint "radius_usergroup_pkey"

There are numerous others.  At first I solved this via piecemail. 
Shortly after that it was apparent that this problem was likely
occuring on all sequences.  The problem was exasperated when I leaped
to CVS HEAD.

08:03 < Supaplex> is there programatic way to learn of what
constraints and sequences are pinning on?
08:04 < Supaplex> for example, when i had this error: ERROR: duplicate
key violates unique constraint "cust_svc_pkey". I had to search for
the constraint in a text dump, get the table and column name in
effect, and guess at the implicit sequence name  in use
08:05 < Supaplex> after that I could run: select
setval('cust_svc_svcnum_seq',(select (svcnum+1) as svcnum from
cust_svc order by svcnum DESC limit 1)); and that constraint was
fixed.
08:05 < lyken> you using a pgdump from 7.4.x ?
08:05 < Supaplex> I'd like to do this for all the sequences
08:05 < Supaplex> yea
08:06 < Supaplex> 7.4.7
08:06 < lyken> it dumpes stuff all out of order..
08:07 < Supaplex> so I need to make a fresh dump, and reload in the proper order
08:07 < Supaplex> is there a bug reference on this somewhere?
08:09 < lyken> its not a bug
08:09 < lyken> its a feature!
08:09 < lyken> 8.0 is a smarter dump
08:09 < Supaplex> lol
08:10 < lyken> if you can
08:10 < lyken> install the pgdump from v8
08:10 < aron> yes, highly recommended
08:10 < Supaplex> that implies upgrading anything 7.4.x to 8. right?
08:10 < aron> or if you have to put 8 on another machine and dump from there
08:10 < lyken> well you can install 8
08:11 < lyken> and just use the dump
08:11 < aron> you could build 8.0 in another dir
08:11 < Supaplex> humm
08:11 < Supaplex> sounds like something to throw at my usermode linux machine
08:11 < lyken> yup
08:11 < lyken> and use the pg_dump from 8 to dump the 7.4 db
08:12 < Supaplex> then reload with 7 from the 8 dump?
08:12 < aron> I prefer to do it on another machnine, in case I am
sleepy and not paying attention :)
08:12 < lyken> Supaplex: yup
08:12 < Supaplex> awesome
08:12 < lyken> however some of the 8 stuff might need to be modifeid/removed
08:12 < lyken> such as the alter database owner i think it is
08:13 < lyken> is in 8 but not 7.4.x
08:13 < Supaplex> well, I can always load into a new db name on the 7 side.
08:13 < aron> ummm won't 8 $ quote ?
08:13 < aron> you may have to turn that off
08:13 < lyken> oh yeh that too
08:13 < lyken> hehe
08:14 < lyken> i had to change like one line in my pg 8 dump to work in 7
08:14 < lyken> and tha twas the alter owner stuff
08:14 < aron> actually I would use this as an excellent opportunity to
upgrade to 8 :)
08:14 < Supaplex> not a bad idea

As it turns out, I never upgraded to or used postgreql 8.  A perl
oneliner I built fixes my issue.

grep pg_catalog.setval 2005041900-freeside157cvsHEAD.sql | perl -e
'while(<>){chomp; next unless ($_ =~ /['\'']([^'\'']+)['\'']/); my
$seq=$1; next unless ($seq =~ /^(.*)_([a-z]+)_seq$/); print "select
setval('\''$seq'\'',(select ($2+1) as $2 from $1 order by $2 DESC
limit 1));\n";}' > fixsequences.sql

I must express caveat emptor.  There is certianly NO WARRANTY. You
should backup your database before doing this, and well, if it breaks
I cannot help you.

Thank you,


Scott Edwards
-- 
Daxal Communications - http://www.daxal.com
Surf the USA - http://www.surfthe.us



On 4/3/05, Scott Edwards <supadupa at gmail.com> wrote:
> ----------  Forwarded Message  ----------
> 
> Subject: Cron <freeside> /usr/local/bin/freeside-daily scott
> Date: Saturday 02 April 2005 09:00 pm
> From: root (Cron Daemon)
> To: freeside
> 
> WARNING:  skipping "pg_shadow" --- only table or database owner can vacuum
> it
> [snippage]
> DBD::Pg::st execute failed: ERROR:  duplicate key violates unique constraint
> "pg_statistic_relid_att_index" at /usr/local/bin/freeside-daily line 63.
> ERROR:  duplicate key violates unique constraint
> "pg_statistic_relid_att_index" at /usr/local/bin/freeside-daily line 63.
> 
> -------------------------------------------------------
> 
> Are these errors critical?  I'm using debian sarge (updated
> frequently) and Freeside 1.4.1. I haven't found references for this
> issue after searching the mailing lists.  I'm not sure what caused
> this, or when it started because I only have a 1 week window on the
> logs.
> 
> Thank you,
> 
> 
> Scott Edwards
> --
> Daxal Communications - http://www.daxal.com
> Surf the USA - http://www.surfthe.us
>