Inverse of pg_get_serial_sequence?

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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);

Greetings,

Andres Freund

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers