Thread: Sequence names have 64 character limit?
Whenever I attempt to create a sequence whether this is done directly via CREATE SEQUENCE or indrirectly vial declaring a column as SERIAL, PG truncates it to 64 characters. Is this a documented limitation? I can’t find a reference to it. Is it possible to change this limitation?
Our table names are and fully descriptive, and our code depends on a predictable sequence naming convention of the PG default <table>_<column>_seq.
Yes, I know that it’s not a great idea to depend on consistent naming conventions!
Carlo
Carlo Stonebanks <carlo@stonebanks.ca> writes: > Whenever I attempt to create a sequence whether this is done directly > via CREATE SEQUENCE or indrirectly vial declaring a column as SERIAL, > PG truncates it to 64 characters. Is this a documented limitation? I > cant find a reference to it. Is it possible to change this > limitation? See the "name" data type. As for enlarging it; I'll bet yes but you'll have to compile from source to do this. Someone on the hackers list can better explain :-) > Our table names are and fully descriptive, and our code depends on a > predictable sequence naming convention of the PG default > <table>_<column>_seq. > > Yes, I know that its not a great idea to depend on consistent naming > conventions! > > Carlo > -- Jerry Sievers Postgres DBA/Development Consulting e: gsievers19@comcast.net p: 305.321.1144
On 03/31/2011 02:13 PM, Jerry Sievers wrote: > Carlo Stonebanks<carlo@stonebanks.ca> writes: > >> Whenever I attempt to create a sequence whether this is done directly >> via CREATE SEQUENCE or indrirectly vial declaring a column as SERIAL, >> PG truncates it to 64 characters. Is this a documented limitation? I >> cant find a reference to it. Is it possible to change this >> limitation? > > See the "name" data type. > > As for enlarging it; I'll bet yes but you'll have to compile from source > to do this. > > Someone on the hackers list can better explain :-) See: http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html 4.1.1. Identifiers and Key Words > >> Our table names are and fully descriptive, and our code depends on a >> predictable sequence naming convention of the PG default >> <table>_<column>_seq. >> >> Yes, I know that its not a great idea to depend on consistent naming >> conventions! >> >> Carlo >> > -- Adrian Klaver adrian.klaver@gmail.com
Thanks Adrian and Jerry. Technically, the best way to know which sequence a column is dependent on is to actually query for it. I have functions which query information_schema.columns and run a regex_replace to extract the sequence name from the defaulting nextval() expression. This is better than demanding that sequence names are predictable, but I wonder if there isn't a better query to run that doesn't require parsing texts? Obviously PG knows about the sequence's relation, probably via a dependency that finds it by rendering the text to regclass to an OID... but this stuff makes me nervous. So, a query that returns the sequence name (as text, you can cast to regclass!) associated with a particular column, that would return NUL if there was none... I don't suppose anyone has written such a query before...? Yes, I'd write a function around it! Thanks again, Carlo -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: March 31, 2011 5:29 PM To: Jerry Sievers Cc: Carlo Stonebanks; pgsql-general@postgresql.org Subject: Re: [GENERAL] Sequence names have 64 character limit? On 03/31/2011 02:13 PM, Jerry Sievers wrote: > Carlo Stonebanks<carlo@stonebanks.ca> writes: > >> Whenever I attempt to create a sequence whether this is done directly >> via CREATE SEQUENCE or indrirectly vial declaring a column as SERIAL, >> PG truncates it to 64 characters. Is this a documented limitation? I >> cant find a reference to it. Is it possible to change this >> limitation? > > See the "name" data type. > > As for enlarging it; I'll bet yes but you'll have to compile from source > to do this. > > Someone on the hackers list can better explain :-) See: http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html 4.1.1. Identifiers and Key Words > >> Our table names are and fully descriptive, and our code depends on a >> predictable sequence naming convention of the PG default >> <table>_<column>_seq. >> >> Yes, I know that its not a great idea to depend on consistent naming >> conventions! >> >> Carlo >> > -- Adrian Klaver adrian.klaver@gmail.com
Carlo Stonebanks <carlo@stonebanks.ca> writes: > Thanks Adrian and Jerry. > > Technically, the best way to know which sequence a column is dependent on is > to actually query for it. I have functions which query > information_schema.columns and run a regex_replace to extract the sequence > name from the defaulting nextval() expression. This is better than demanding > that sequence names are predictable, but I wonder if there isn't a better > query to run that doesn't require parsing texts? Obviously PG knows about > the sequence's relation, probably via a dependency that finds it by > rendering the text to regclass to an OID... but this stuff makes me nervous. Nervous or otherwise, if your sequences are owned by the tables either by their implicit creation with SERIAL keyword or a later ALTER SEQUENCE OWNED BY, then querying the class and depend catalogues is your most reliable bet. -- Jerry Sievers Postgres DBA/Development Consulting e: gsievers19@comcast.net p: 305.321.1144
On 03/31/2011 02:47 PM, Carlo Stonebanks wrote: > Thanks Adrian and Jerry. > > Technically, the best way to know which sequence a column is dependent on is > to actually query for it. I have functions which query > information_schema.columns and run a regex_replace to extract the sequence > name from the defaulting nextval() expression. This is better than demanding > that sequence names are predictable, but I wonder if there isn't a better > query to run that doesn't require parsing texts? Obviously PG knows about > the sequence's relation, probably via a dependency that finds it by > rendering the text to regclass to an OID... but this stuff makes me nervous. > > So, a query that returns the sequence name (as text, you can cast to > regclass!) associated with a particular column, that would return NUL if > there was none... I don't suppose anyone has written such a query before...? > > Yes, I'd write a function around it! > > Thanks again, > > Carlo Well in 8.2+ for sequences created by the SERIAL datatype there is the function: pg_get_serial_sequence(table_name, column_name) The benefit of using SERIAL is that it sets up a dependency. If you specify the sequence via CREATE SEQUENCE then AFAIK you are left looking up the default and parsing it. -- Adrian Klaver adrian.klaver@gmail.com
Once more, thanks! Of course, using SERIAL is the ideal method, but I can't have the programs in question fail because someone neglected to set up a dependency. Other people may be slobs, but I'm afraid I can't be. In fact, my very first attempt to use pg_get_serial_sequence(table_name, column_name) returned a NULL result, which tells me that it's a reality I have to deal with. I'll stick with the column default parsing method... Adrian, Jerry - thanks very much for the info. Carlo -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: March 31, 2011 6:15 PM To: Carlo Stonebanks Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Sequence names have 64 character limit? On 03/31/2011 02:47 PM, Carlo Stonebanks wrote: > Thanks Adrian and Jerry. > > Technically, the best way to know which sequence a column is dependent on is > to actually query for it. I have functions which query > information_schema.columns and run a regex_replace to extract the sequence > name from the defaulting nextval() expression. This is better than demanding > that sequence names are predictable, but I wonder if there isn't a better > query to run that doesn't require parsing texts? Obviously PG knows about > the sequence's relation, probably via a dependency that finds it by > rendering the text to regclass to an OID... but this stuff makes me nervous. > > So, a query that returns the sequence name (as text, you can cast to > regclass!) associated with a particular column, that would return NUL if > there was none... I don't suppose anyone has written such a query before...? > > Yes, I'd write a function around it! > > Thanks again, > > Carlo Well in 8.2+ for sequences created by the SERIAL datatype there is the function: pg_get_serial_sequence(table_name, column_name) The benefit of using SERIAL is that it sets up a dependency. If you specify the sequence via CREATE SEQUENCE then AFAIK you are left looking up the default and parsing it. -- Adrian Klaver adrian.klaver@gmail.com