Thread: 7.0.0 long name truncation problem
When the name of a sequence auto-generated from a SERIAL type would be
longer than 32 chars, it appears that "CREATE TABLE" uses a different
name truncation algorithm than "DROP SEQUENCE". Example below. Note
the difference between the following:
'process_state_subscripti_id_seq'
'process_state_subscription_id_s'
Might be fixed in 7.1, I dunno. Can anyone confirm this is a problem?
Regards,
Ed Loehr
% 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
> When the name of a sequence auto-generated from a SERIAL type would be > longer than 32 chars, it appears that "CREATE TABLE" uses a different > name truncation algorithm than "DROP SEQUENCE". Example below. Note > the difference between the following: > > 'process_state_subscripti_id_seq' > 'process_state_subscription_id_s' The problem is that the CREATE TABLE statement uses the table name, field name and an identifier "_seq" to generate the sequence name. Because it knows those values, it is able to intelligently truncate values. The DROP SEQUENCE statement doesn't know the table name, the field name, or even that the sequence is being used for a SERIAL field. All it knows is that the name can't be longer than 32 characters. So when you feed it a string, the only thing it can really do: truncate the end. It *might* be possible to parse the string based on separators (underscores) except that in your example, you use underscores in your table/field names as well, so what's it to do? > Might be fixed in 7.1, I dunno. Can anyone confirm this is a problem? It's a problem for people like you and me, but it's expected behavior. Personally, I'd love to see someone add a DROP SERIAL that would accept the Table and Field name and then generate the DROP SEQUENCE statement for you (hint, hint *g*). Greg
This article could be huge for those looking to sell Postgres to a company. I'm printing out a copy for myself ;) Open Source Code: A Corporate Building Block (ZDNet) http://dailynews.yahoo.com/h/zd/20010514/tc/open_source_code_a_corporate_building_block_1.html Brent __________________________________________________ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/
Earlier I posted with my problems about the WAL logs eating up all my diskspace. I tried the solutions offered--checkpoint after a big copy and shortening the time between flushes. They helped somewhat. Unfortunately, the problem snow seems to happen when I vacuum-analyze after a big delete. Even if the vacuum takes more than 2 minutes (the amount of time between flushes that I set in postgresql.conf), the logs continue to grow. Currently the vacuum has been running for about 20 minutes after a delete of about 2,000,000 records out of 5,000,000, and the WAL logs are at about 1.5 G. Versions: RH 6.2, PG 7.1.1 Is there some way to set the number of log files before a flush/checkpoint thing? We are going to go to a bigger machine next week, but this is quite an inconvenience, and it would probably benefit the DB as a whole to place some limit on the size of the WAL. I would code it myself, but I can't yet (next year, after I finish a couple of Stevens' books....). If there is any thing else I can do to help, please let me know. Thanks, W
webb sprague <wsprague@o1.com> writes:
> Unfortunately, the problem snow seems to happen when I vacuum-analyze
> after a big delete. Even if the vacuum takes more than 2 minutes (the
> amount of time between flushes that I set in postgresql.conf), the
> logs continue to grow.
I don't think there's any way around that at the moment :-(. The old
log segments cannot be deleted as long as they include information about
as-yet-uncommitted transactions, and the VACUUM is one big transaction.
It would theoretically be possible to delete old log segments from
before the last checkpoint (or better, next-to-last checkpoint) if
we were only concerned about using the logs for crash recovery.
I imagine Vadim will object to that, however, since he has in mind
using the logs for UNDOing aborted transactions sometime soon.
A more likely solution path will be to eliminate VACUUM or break it
into smaller transactions...
regards, tom lane
Tom, Given what you say below, I think there is a documentation bug then. In Section 9.3 of the Administrators Guide it says: "After a checkpoint has been made, any log segments written before the redo record are removed, so checkpoints are used to free disk space in the WAL directory." This should be changed to correctly document the current functionality, which is essentially something like: "After a checkpoint has been made, log segments written before the redo record *may* be removed if they don't contain data for active transactions... " (and IIRC the code actually keeps the logs around for two checkpoints before attempting to delete them). The current doc really implies that the fix the disk space usage problem you need more frequent checkpoints, but as this case points out that may not fix the problem. thanks, --Barry Tom Lane wrote: > webb sprague <wsprague@o1.com> writes: > >> Unfortunately, the problem snow seems to happen when I vacuum-analyze >> after a big delete. Even if the vacuum takes more than 2 minutes (the >> amount of time between flushes that I set in postgresql.conf), the >> logs continue to grow. > > > I don't think there's any way around that at the moment :-(. The old > log segments cannot be deleted as long as they include information about > as-yet-uncommitted transactions, and the VACUUM is one big transaction. > > It would theoretically be possible to delete old log segments from > before the last checkpoint (or better, next-to-last checkpoint) if > we were only concerned about using the logs for crash recovery. > I imagine Vadim will object to that, however, since he has in mind > using the logs for UNDOing aborted transactions sometime soon. > > A more likely solution path will be to eliminate VACUUM or break it > into smaller transactions... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
Barry Lind <barry@xythos.com> writes:
> Given what you say below, I think there is a documentation bug then. In
> Section 9.3 of the Administrators Guide it says:
> "After a checkpoint has been made, any log segments written before the
> redo record are removed, so checkpoints are used to free disk space
> in the WAL directory."
Hmm, it should have said "undo record" ...
regards, tom lane
Docs updated. > Barry Lind <barry@xythos.com> writes: > > Given what you say below, I think there is a documentation bug then. In > > Section 9.3 of the Administrators Guide it says: > > > "After a checkpoint has been made, any log segments written before the > > redo record are removed, so checkpoints are used to free disk space > > in the WAL directory." > > Hmm, it should have said "undo record" ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hello, We at OpenDocs Publishing have decided to place our entire book titled, PostgreSQL: The Elephant Never forgets online during its development. As always you can get to all things related with this book by pointing your web browser here: http://www.opendocspublishing.com/entry.lxp?lxpe=92 PLEASE REMEMBER that this is not a finished product but we are actively seeking feedback to make it a better book. Flame us if you must (although not preferrable) as we really want all the feedback we can get. The more feedback we get, the better the book will be in the long run for everyone. Thanks!
Dixit <pgsql-general@commandprompt.com> (le Tue, 26 Jun 2001 18:58:34 -0700 (PDT)) : » Hello, » » We at OpenDocs Publishing have decided to place our entire book titled, » PostgreSQL: The Elephant Never forgets online during its development. As » always you can get to all things related with this book by pointing your » web browser here: » » http://www.opendocspublishing.com/entry.lxp?lxpe=92 » » PLEASE REMEMBER that this is not a finished product but we are actively » seeking feedback to make it a better book. Flame us if you must (although » not preferrable) as we really want all the feedback we can get. The more » feedback we get, the better the book will be in the long run for everyone. » » Thanks! For sure it is under development : when accessing the URL : Not Found The requested URL /projects/postgres/style.css was not found on this server. -- THierry Besancon
It works in IE 5.5 SP1. (Had the same problem, but have both installed). :-) Regards and best wishes, Justin Clift Thierry Besancon wrote: > > Dixit <pgsql-general@commandprompt.com> (le Tue, 26 Jun 2001 18:58:34 -0700 (PDT)) : > > » Hello, > » > » We at OpenDocs Publishing have decided to place our entire book titled, > » PostgreSQL: The Elephant Never forgets online during its development. As > » always you can get to all things related with this book by pointing your > » web browser here: > » > » http://www.opendocspublishing.com/entry.lxp?lxpe=92 > » > » PLEASE REMEMBER that this is not a finished product but we are actively > » seeking feedback to make it a better book. Flame us if you must (although > » not preferrable) as we really want all the feedback we can get. The more > » feedback we get, the better the book will be in the long run for everyone. > » > » Thanks! > > For sure it is under development : > > when accessing the URL : > > Not Found > > The requested URL /projects/postgres/style.css was not found on this server. > > -- THierry Besancon > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster