Thread: 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@2ndquadrant.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@2ndquadrant.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_dependd 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"
"\nAND 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@2ndquadrant.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@2ndquadrant.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