Обсуждение: Unable to drop sequence due to dependency?

I can't drop a sequence.  I get the error "cannot drop sequence
<sequence name> because other objects depend on it".  I've tried to use
the pg_depend table to find out what object(s) depend on it, and have
found nothing.

I tried drop cascade in a transaction, hoping to see a list of objects
dropped in the cascade, but only got "DROP SEQUENCE".

I tried these two queries:

select * from pg_depend where objid = 1011680210;
select * from pg_depend where refobjid = 1011680210;

The first one returned one row with refobjid = 2200.  That's a
namespace, so this appears to be the reverse of what I want.

The second one returned two rows with objid = 1011687585 and 1011680211;
   OID 1011680211 is the type (in pg_type) that matches this sequence,
so this also appears to be the opposite of what I want.  I couldn't find
OID 1011687585 anywhere.

Any hints?  What type of objects can depend on a sequence?  How can I
find out which ones depend on this particular one?


SELECT version()...
PostgreSQL 8.1.4 on x86_64-redhat-linux-gnu, compiled by GCC
x86_64-redhat-linux-gcc (GCC) 4.1.0 20060304 (Red Hat 4.1.0-3)



Thanks!

-Glen

Glen Parker <glenebob@nwlink.com> writes:
> I can't drop a sequence.  I get the error "cannot drop sequence
> <sequence name> because other objects depend on it".  I've tried to use
> the pg_depend table to find out what object(s) depend on it, and have
> found nothing.

What's the whole contents of the rows with refobjid matching the
sequence's OID?  In particular, classid::regclass would tell you
where to look for the dependent object.

> Any hints?  What type of objects can depend on a sequence?

Typically, the default expression for a serial column based on the
sequence.  It's a bit odd that the DROP doesn't tell you about it
though.

            regards, tom lane

Tom Lane wrote:
> What's the whole contents of the rows with refobjid matching the
> sequence's OID?  In particular, classid::regclass would tell you
> where to look for the dependent object.

oms=# select * from pg_depend where objid = 1011680210;
  classid |   objid    | objsubid | refclassid | refobjid | refobjsubid
| deptype
---------+------------+----------+------------+----------+-------------+---------
     1259 | 1011680210 |        0 |       2615 |     2200 |           0 | n
(1 row)

oms=# select * from pg_depend where refobjid = 1011680210;
  classid |   objid    | objsubid | refclassid |  refobjid  |
refobjsubid | deptype
---------+------------+----------+------------+------------+-------------+---------
     2604 | 1011687585 |        0 |       1259 | 1011680210 |
0 | n
     1247 | 1011680211 |        0 |       1259 | 1011680210 |
0 | i
(2 rows)


  > Typically, the default expression for a serial column based on the
> sequence.  It's a bit odd that the DROP doesn't tell you about it
> though.


For some reason I got into my head the notion that a sequence could be
dropped even if reference by a field default.  This seems pretty silly now.

-Glen

Glen Parker <glenebob@nwlink.com> writes:
> For some reason I got into my head the notion that a sequence could be
> dropped even if reference by a field default.  This seems pretty silly now.

It was true once upon a time, but we have an enforcement mechanism now
to catch that.  Did you find the default you needed to get rid of?
That 2604 row should be it.

            regards, tom lane

Tom Lane wrote:
> Glen Parker <glenebob@nwlink.com> writes:
>> For some reason I got into my head the notion that a sequence could be
>> dropped even if reference by a field default.  This seems pretty silly now.
>
> It was true once upon a time, but we have an enforcement mechanism now
> to catch that.  Did you find the default you needed to get rid of?
> That 2604 row should be it.


I did find it.  2604 is "pg_attrref", which now that you point it out
makes a certain amount of sense, but I would have had no idea what to do
with that an hour ago.

Thanks :-)

-Glen