Re: Inverse of pg_get_serial_sequence?

Hi,

We have pg_get_serial_sequence() mapping (relation, colum) to the
sequence. What I'm missing right now is the inverse. I.e. given a
sequence tell me the owner.
describe.c has a query for that, and it's not too hard to write, but it
still seems 'unfriendly' not to provide it.

Does anybody dislike adding a function for that?

I can't really think of a good name (not that pg_get_serial_sequence is
well named). pg_get_serial_sequence_owner(serial regclass, OUT rel
regclass, OUT colname name) maybe?

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund-3 wrote
> Hi,
>
> We have pg_get_serial_sequence() mapping (relation, colum) to the
> sequence. What I'm missing right now is the inverse. I.e. given a
> sequence tell me the owner.
> describe.c has a query for that, and it's not too hard to write, but it
> still seems 'unfriendly' not to provide it.
>
> Does anybody dislike adding a function for that?
>
>
> I can't really think of a good name (not that pg_get_serial_sequence is
> well named). pg_get_serial_sequence_owner(serial regclass, OUT rel
> regclass, OUT colname name) maybe?

On a pure consistency basis: pg_get_sequence_serial(...) [though probably
plural: _serials(...)]

I'd drop the serial part altogether for the more appropriate:

pg_get_sequence_ownedby(...)

Given that ALTER SEQUENCE ... OWNED BY ... Is the corresponding SQL

The inverse of what you proposed above would probably be more like:

pg_get_owned_sequence(...)

Reminder: sequences can be unowned.

Ownership and usage via default are separate things though: do you have need
to know all users of a sequence or only the single one that is defined as
it's owner?

pg_get_sequence_users(...) [or serials: as noted first]

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Inverse-of-pg-get-serial-sequence-tp5816933p5816993.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 2014-08-29 17:55:38 -0700, David G Johnston wrote:
> Andres Freund-3 wrote
> > Hi,
> >
> > We have pg_get_serial_sequence() mapping (relation, colum) to the
> > sequence. What I'm missing right now is the inverse. I.e. given a
> > sequence tell me the owner.
> > describe.c has a query for that, and it's not too hard to write, but it
> > still seems 'unfriendly' not to provide it.
> >
> > Does anybody dislike adding a function for that?
> >
> >
> > I can't really think of a good name (not that pg_get_serial_sequence is
> > well named). pg_get_serial_sequence_owner(serial regclass, OUT rel
> > regclass, OUT colname name) maybe?
>
> On a pure consistency basis: pg_get_sequence_serial(...) [though probably
> plural: _serials(...)]

Yea, but that's just horrid.

> I'd drop the serial part altogether for the more appropriate:
>
> pg_get_sequence_ownedby(...)

My problem is that that possibly be confused with the user owning the
sequence :/

> Reminder: sequences can be unowned.

Don't you say.

> Ownership and usage via default are separate things though: do you have need
> to know all users of a sequence or only the single one that is defined as
> it's owner?

I'd rather know all its users, but that's not really possible in the
general case without guessing. I'll settle for the column that's
declared as owning it. Even if we had a interface for guessing I'd not
want it to be the same as the one returning the declared owner.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Andres Freund-3 wrote
> On 2014-08-29 17:55:38 -0700, David G Johnston wrote:
>> Andres Freund-3 wrote
>
>>
>> pg_get_sequence_ownedby(...)
>
> My problem is that that possibly be confused with the user owning the
> sequence :/

Though as soon as that person reads the output their misunderstanding would
be obvious.

I think it's fine but "ownedbycol" or "owningcol" would be ok.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Inverse-of-pg-get-serial-sequence-tp5816933p5816996.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Fri, Aug 29, 2014 at 1:26 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> We have pg_get_serial_sequence() mapping (relation, colum) to the
> sequence. What I'm missing right now is the inverse. I.e. given a
> sequence tell me the owner.
> describe.c has a query for that, and it's not too hard to write, but it
> still seems 'unfriendly' not to provide it.
>
> Does anybody dislike adding a function for that?

I'll go out on a limb and say that it sounds like pointless catalog
bloat to me. I am all in favor of adding things like this where the
SQL query is painful to write (e.g. things involving pg_depend) but if
it's a simple SELECT query then, eh, not really excited about it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 2014-09-03 09:31:50 -0400, Robert Haas wrote:
> On Fri, Aug 29, 2014 at 1:26 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> > We have pg_get_serial_sequence() mapping (relation, colum) to the
> > sequence. What I'm missing right now is the inverse. I.e. given a
> > sequence tell me the owner.
> > describe.c has a query for that, and it's not too hard to write, but it
> > still seems 'unfriendly' not to provide it.
> >
> > Does anybody dislike adding a function for that?
>
> I'll go out on a limb and say that it sounds like pointless catalog
> bloat to me. I am all in favor of adding things like this where the
> SQL query is painful to write (e.g. things involving pg_depend) but if
> it's a simple SELECT query then, eh, not really excited about it.

There's not really a simple select for it, is there? psql uses:

/* Get the column that owns this sequence */
printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
"\n pg_catalog.quote_ident(relname) || '.' ||"
"\n pg_catalog.quote_ident(attname)"
"\nFROM pg_catalog.pg_class c"
"\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
"\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
"\nINNER JOIN pg_catalog.pg_attribute a ON ("
"\n a.attrelid=c.oid AND"
"\n a.attnum=d.refobjsubid)"
"\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
"\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
"\n AND d.objid=%s"
"\n AND d.deptype='a'",
oid);

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Wed, Sep 3, 2014 at 10:44 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2014-09-03 09:31:50 -0400, Robert Haas wrote:
>> On Fri, Aug 29, 2014 at 1:26 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
>> > We have pg_get_serial_sequence() mapping (relation, colum) to the
>> > sequence. What I'm missing right now is the inverse. I.e. given a
>> > sequence tell me the owner.
>> > describe.c has a query for that, and it's not too hard to write, but it
>> > still seems 'unfriendly' not to provide it.
>> >
>> > Does anybody dislike adding a function for that?
>>
>> I'll go out on a limb and say that it sounds like pointless catalog
>> bloat to me. I am all in favor of adding things like this where the
>> SQL query is painful to write (e.g. things involving pg_depend) but if
>> it's a simple SELECT query then, eh, not really excited about it.
>
> There's not really a simple select for it, is there? psql uses:
>
> /* Get the column that owns this sequence */
> printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
> "\n pg_catalog.quote_ident(relname) || '.' ||"
> "\n pg_catalog.quote_ident(attname)"
> "\nFROM pg_catalog.pg_class c"
> "\nINNER JOIN pg_catalog.pg_depend d ON c.oid=d.refobjid"
> "\nINNER JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace"
> "\nINNER JOIN pg_catalog.pg_attribute a ON ("
> "\n a.attrelid=c.oid AND"
> "\n a.attnum=d.refobjsubid)"
> "\nWHERE d.classid='pg_catalog.pg_class'::pg_catalog.regclass"
> "\n AND d.refclassid='pg_catalog.pg_class'::pg_catalog.regclass"
> "\n AND d.objid=%s"
> "\n AND d.deptype='a'",
> oid);

Oh, OK. Yeah, that's kind of hairy.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company