Обсуждение: increment_by@Žê×

This is a follow-up to my previous email. Using
phppgadmin to look at the sequence causing problems,
one of the columns is "increment_by@���". How can I
change it back or otherwise fix it? What happened
anyhow?

TIA,
John

__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

John Smith <john_smith_45678@yahoo.com> writes:
> This is a follow-up to my previous email. Using
> phppgadmin to look at the sequence causing problems,
> one of the columns is "increment_by@���". How can I
> change it back or otherwise fix it? What happened
> anyhow?

Sounds like a data corruption problem :-(.  You might want to try a
"select * from pg_attribute" and see if any other rows look obviously
bogus.

As for fixing it, you might be able to get away with an ALTER TABLE
RENAME column --- I'm not sure if the system will let you apply that to
a sequence or not, but it'd be worth trying.  Failing that, a direct
UPDATE on the messed-up row of pg_attribute ought to do the trick.

Or you could just drop and recreate the affected sequence.  There's not
all that much state in a sequence ...

            regards, tom lane

This did the trick:

update pg_attribute set attname='increment_by' where
attname like 'increment_by@%';

Not sure what the problem with this was:

update pg_attribute set attname='increment_by' where
attrelid=457191;
ERROR:  Cannot insert a duplicate key into unique
index pg_attribute_relid_attnam_index

John


--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> John Smith <john_smith_45678@yahoo.com> writes:
> > This is a follow-up to my previous email. Using
> > phppgadmin to look at the sequence causing
> problems,
> > one of the columns is "increment_by@���". How can
> I
> > change it back or otherwise fix it? What happened
> > anyhow?
>
> Sounds like a data corruption problem :-(.  You
> might want to try a
> "select * from pg_attribute" and see if any other
> rows look obviously
> bogus.
>
> As for fixing it, you might be able to get away with
> an ALTER TABLE
> RENAME column --- I'm not sure if the system will
> let you apply that to
> a sequence or not, but it'd be worth trying.
> Failing that, a direct
> UPDATE on the messed-up row of pg_attribute ought to
> do the trick.
>
> Or you could just drop and recreate the affected
> sequence.  There's not
> all that much state in a sequence ...
>
>             regards, tom lane


__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com

John Smith <john_smith_45678@yahoo.com> writes:
> This did the trick:
> update pg_attribute set attname='increment_by' where
> attname like 'increment_by@%';

Good.

> Not sure what the problem with this was:
> update pg_attribute set attname='increment_by' where
> attrelid=457191;
> ERROR:  Cannot insert a duplicate key into unique
> index pg_attribute_relid_attnam_index

There would be multiple rows with that attrelid, so the error is correct
(and fortunate ;-)).  You'd have had to specify both attrelid and attnum
to have a unique key.

            regards, tom lane