Re: 1.10 beta 1 - Mailing list pgadmin-hackers

Ashesh Vashi a écrit :
> Hi Guillaume,
>
> Thanks for reviewing the patch.
>
> Guillaume Lelarge wrote:
>> Ashesh Vashi a écrit :
>>> Hi Dave,
>>>
>>> Please find the patch for the Dependency bug
>> I just checked this patch. I find disturbing that it finds a dependency
>> to a nextval function instead of a dependency to the sequence. Moreover,
>> the first table has two dependencies : on the sequence and on the
>> nextval function of this sequence.
> I think - you're right.
> But, I could not find the direct dependency of the sequence on the table.
>>  This patch is a first step, but I think we need to go further.
> Definitely, even I felt the same.
> I need some guidance for it.
>
> Could you please help on this?
>


Here are the definition of the two tables:

CREATE TABLE t1 (id serial NOT NULL);
CREATE TABLE t2 (LIKE t INCLUDING DEFAULTS);

Quite simple. t1_id_seq must be a dependency of t2 (ie you can't drop
t1_id_seq, or t1, without dropping t2 or at least change its sequence).

With this next query, we get every pg_attrdef dependencies:

ioguix=# select * from pg_depend where classid = 2604;
 classid | objid  | objsubid | refclassid | refobjid | refobjsubid | deptype
---------+--------+----------+------------+----------+-------------+---------
    2604 | 122030 |        0 |       1259 |   122027 |           1 | a
    2604 | 122030 |        0 |       1259 |   122025 |           0 | n
    2604 | 122034 |        0 |       1259 |   122031 |           1 | a
    2604 | 122034 |        0 |       1259 |   122025 |           0 | n
(4 lignes)

This one gives us the target column (122030 and 122034 are results of
the previous query, column objid) :

ioguix=# select relname||'.'||attname
  from pg_class cl, pg_attribute att, pg_attrdef atd
  where atd.oid in (122030, 122034)
    and atd.adrelid=att.attrelid
    and atd.adnum=att.attnum
    and cl.oid=att.attrelid;
 ?column?
----------
 t1.id
 t2.id
(2 lignes)

This one gives us the referenced object (122027, 122025, 122031 are
results of the attrdef query, column refobjid) :

ioguix=# select oid, relname from pg_class where oid in (122027, 122025,
122031);
  oid   |  relname
--------+-----------
 122025 | t1_id_seq
 122027 | t1
 122031 | t2
(3 lignes)

In the first query (the attrdef one), you'll notice t1_id_seq appears
two times, one for t1.id and one for t2.id.

I don't quite know how to build a query that will give us t1_id_seq as a
dependency to t2.

I don't actually have the time to work on this right now. But it must be
something with these queries.

Hope it helps. Ping me if you need more details.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com