Обсуждение: Cannot drop sequence...

Поиск
Список
Период
Сортировка

Cannot drop sequence...

От
D0
Дата:
agi_db=# create table analytics_budget
agi_db-# (
agi_db(# analytics_budget_id serial not null primary key,
agi_db(# triangulation int2,
agi_db(# sales_order_id int4 not null references
sales_order(sales_order_id) default
atoi(currval('sales_order_sales_order_id'))
agi_db(# );
NOTICE:  CREATE TABLE will create implicit sequence
'analytics_bud_analytics_bud_seq' for SERIAL column
'analytics_budget.analytics_budget_id'
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index
'analytics_budget_pkey' for table 'analytics_budget'
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR:  Relation 'analytics_bud_analytics_bud_seq' already exists
agi_db=# drop sequence analytics_bud_analytics_bud_seq;
ERROR:  Relation 'analytics_bud_analytics_bud_seq' does not exist
agi_db=# drop sequence analytics_budget_analytics_budget_seq;
NOTICE:  identifier "analytics_budget_analytics_budget_seq" will be
truncated to "analytics_budget_analytics_budg"
ERROR:  Relation 'analytics_budget_analytics_budg' does not exist



So... How do I get rid of this sequence?

Also how do I get a listing of the sequences already created?

TIA
--
Jason Nielsen                              Aero-Graphics, Inc.
Computer Grunt (Systems Administrator)    www.aero-graphics.com

Re: Cannot drop sequence...

От
Serge Canizares
Дата:
> Also how do I get a listing of the sequences already created?
>
> TIA


\ds

You can always get a list of commands with \?, and help with \h.

Serge

Re: Cannot drop sequence...

От
Tom Lane
Дата:
D0 <jnielsen@aero-graphics.com> writes:
> ERROR:  Relation 'analytics_bud_analytics_bud_seq' already exists
> agi_db=# drop sequence analytics_bud_analytics_bud_seq;
> ERROR:  Relation 'analytics_bud_analytics_bud_seq' does not exist

What PG version are you running?  This looks like the classic catch-22
from pre-7.1 versions wherein you tried to roll back a DROP operation.
The pg_class entry for the dropped sequence rolls back just fine, but
the physical file is already gone, leading to no end of confusion when
you try to manipulate the object later.

If that's what it is, you can resolve the inconsistency and make the
DROP possible with something like

    touch $PGDATA/base/agi_db/analytics_bud_analytics_bud_seq

            regards, tom lane