Обсуждение: name truncation problem in 7.0.0

Maybe someone can confirm what looks like a long-name-truncation bug in
7.0.0?  I haven't tested it on newer releases, nor have I dug into the
code.  The problem appears to be that a different truncation algorithm is
used for creating the truncated sequence name than the algorithm used
when doing the 'drop sequence', e.g.,

    'process_state_subscripti_id_seq'
    'process_state_subscription_id_s'

Here's my test case:

% createdb testdb
CREATE DATABASE

% psql -e -d testdb -f ~ed/pgbug
CREATE TABLE process_state_subscription (
    id                          SERIAL,
    process_id                  INTEGER,
    process_state_id            INTEGER,
    person_id                   INTEGER NOT NULL
);
psql:/home/ed/pgbug:8: NOTICE:  CREATE TABLE will create implicit
sequence 'process_state_subscripti_id_seq' for SERIAL column
'process_state_subscription.id'
psql:/home/ed/pgbug:8: NOTICE:  CREATE TABLE/UNIQUE will create implicit
index 'process_state_subscripti_id_key' for table
'process_state_subscription'
CREATE
DROP SEQUENCE process_state_subscription_id_seq;
psql:/home/ed/pgbug:10: NOTICE:  identifier
"process_state_subscription_id_seq" will be truncated to
"process_state_subscription_id_s"
psql:/home/ed/pgbug:10: ERROR:  Relation
'process_state_subscription_id_s' does not exist


Regards,
Ed Loehr

Ed Loehr <eloehr@austin.rr.com> writes:
> Maybe someone can confirm what looks like a long-name-truncation bug in
> 7.0.0?

I see no bug here; it told you what name it planned to use for the
sequence:

> psql:/home/ed/pgbug:8: NOTICE:  CREATE TABLE will create implicit
> sequence 'process_state_subscripti_id_seq' for SERIAL column
> 'process_state_subscription.id'

so this is not surprising:

> DROP SEQUENCE process_state_subscription_id_seq;
> psql:/home/ed/pgbug:10: NOTICE:  identifier
> "process_state_subscription_id_seq" will be truncated to
> "process_state_subscription_id_s"
> psql:/home/ed/pgbug:10: ERROR:  Relation
> 'process_state_subscription_id_s' does not exist

It's not a bug that the sequence name is formed with a rule more complex
than "truncate table_field_seq at the right" ... if we did that, you'd
have a problem with sequences for tables with names longer than 32
characters ...

            regards, tom lane

Tom Lane wrote:
>
> Ed Loehr <eloehr@austin.rr.com> writes:
> > Maybe someone can confirm what looks like a long-name-truncation bug in
> > 7.0.0?
>
> I see no bug here; it told you what name it planned to use for the
> sequence:
>
> > psql:/home/ed/pgbug:8: NOTICE:  CREATE TABLE will create implicit
> > sequence 'process_state_subscripti_id_seq' for SERIAL column
> > 'process_state_subscription.id'
>
> so this is not surprising:
>
> > DROP SEQUENCE process_state_subscription_id_seq;
> > psql:/home/ed/pgbug:10: NOTICE:  identifier
> > "process_state_subscription_id_seq" will be truncated to
> > "process_state_subscription_id_s"
> > psql:/home/ed/pgbug:10: ERROR:  Relation
> > 'process_state_subscription_id_s' does not exist
>
> It's not a bug that the sequence name is formed with a rule more complex
> than "truncate table_field_seq at the right" ... if we did that, you'd
> have a problem with sequences for tables with names longer than 32
> characters ...

Hmmm.  OK, I think I understand, but it sure makes for some ugliness in
guessing what the name of the SERIAL-generated sequence name will be in
order to drop it.

Is there a clean way I can bump the 32-char limit to something much
larger to support my verbosity?  Maybe NAMEDATALEN in
src/include/postgres_ext.h?  Assuming sufficient memory/disk, are there
other concerns about bumping it to, say, 64 or even 1024?  It's cramping
my style.

Regards,
Ed Loehr

Ed Loehr <eloehr@austin.rr.com> writes:
> Is there a clean way I can bump the 32-char limit to something much
> larger to support my verbosity?  Maybe NAMEDATALEN in
> src/include/postgres_ext.h?

Yup, see the archives.

> Assuming sufficient memory/disk, are there
> other concerns about bumping it to, say, 64 or even 1024?  It's cramping
> my style.

64 or 100 or so seems reasonable, I think 1024 would be excessive ...

            regards, tom lane