Обсуждение: dropping sequences
Hi,
I have a database with a few tables in it (this is just a "learning"
database). Originally I set these tables up with sequences on them
which increment the primary key of the table when a new record is
inserted. I would like to drop these sequences.
1. First I wanted to copy the database to a different database so that
the original could remain untouched if a screw something up. The way I
did this was to use pg_dump on the original database and then load the
export file into the new database. Is there another (not necessarily
better) way to do this, such as from within psql without dumping to the
file system? (Copying directly from one DB to another.)
2. Now I would like to drop the sequences, but I am told that I cannot:
"ERROR: Cannot drop sequence news_news_id_seq because table news column
news_id requires it
You may drop table news column news_id instead"
So, is the solution to drop the column first, then drop the sequence,
then re-create the column by using ALTER TABLE ? Something tells me
that this will cause a problem due to referential integrity constraints
that I set up on some of the tables' primary keys.
Pardon if these are newb questions but my experience has been in MySQL
which does not offer these features, so I am trying to learn how to use
them.
Regards,
Erik
Erik Price <eprice@ptc.com> writes:
> So, is the solution to drop the column first, then drop the sequence,
> then re-create the column by using ALTER TABLE ?
Perhaps what you are really looking to do is just to reset the sequence
counters with setval() ?
regards, tom lane
Tom Lane wrote: > Erik Price <eprice@ptc.com> writes: > >>So, is the solution to drop the column first, then drop the sequence, >>then re-create the column by using ALTER TABLE ? > > > Perhaps what you are really looking to do is just to reset the sequence > counters with setval() ? Believe it or not, I really want to remove the sequences altogether. It may sound strange. I'm experimenting. Erik
On Thu, 19 Jun 2003, Erik Price wrote: > Hi, > > I have a database with a few tables in it (this is just a "learning" > database). Originally I set these tables up with sequences on them > which increment the primary key of the table when a new record is > inserted. I would like to drop these sequences. > > 1. First I wanted to copy the database to a different database so that > the original could remain untouched if a screw something up. The way I > did this was to use pg_dump on the original database and then load the > export file into the new database. Is there another (not necessarily > better) way to do this, such as from within psql without dumping to the > file system? (Copying directly from one DB to another.) If they are in the same cluster (i.e. running on the same machine under the same postmaster) you can use this: CREATE DATABASE newdb with template olddb; > 2. Now I would like to drop the sequences, but I am told that I cannot: > > "ERROR: Cannot drop sequence news_news_id_seq because table news column > news_id requires it > You may drop table news column news_id instead" > > So, is the solution to drop the column first, then drop the sequence, > then re-create the column by using ALTER TABLE ? The easiest way to do this is to alter table yourtablehere alter column colwithseq drop default; which will disconnect the sequence from the table. Note that the you can do it the other way too. > Something tells me > that this will cause a problem due to referential integrity constraints > that I set up on some of the tables' primary keys. It could. It's probably just easier to drop the default. > Pardon if these are newb questions but my experience has been in MySQL > which does not offer these features, so I am trying to learn how to use > them. Hey, we all started somewhere, and Postgresql is a much more persnickity database than MySQL. That's a feature by the way, it's usually trying to stop you from doing the wrong thing. :-)
scott.marlowe wrote: > If they are in the same cluster (i.e. running on the same machine under > the same postmaster) you can use this: > > CREATE DATABASE newdb with template olddb; Great, and can I assume that when I issue "CREATE DATABASE;" this same command is executed but, implicitly, "with template template0" added? I read in the docs that "template0" is the "default" database type unless overidden. > The easiest way to do this is to > > alter table yourtablehere alter column colwithseq drop default; > > which will disconnect the sequence from the table. Note that the you can > do it the other way too. Okay, so the "DEFAULT" of a table is an item that can be dropped, independently of the table itself, within an ALTER COLUMN statement. I didn't know that. Thanks Scott. Erik
Erik Price <eprice@ptc.com> writes: > Great, and can I assume that when I issue "CREATE DATABASE;" this same > command is executed but, implicitly, "with template template0" added? > I read in the docs that "template0" is the "default" database type > unless overidden. Actually, I'm pretty sure it's "template1". Where in the docs does it say "template0"? -Doug
Doug McNaught wrote: > Actually, I'm pretty sure it's "template1". Where in the docs does it > say "template0"? Maybe it is template1. I have a DB on my system named template0, but I might have created that right when I first installed PostgreSQL as an experiment and forgot about it. I also have a template1 database. Erik
Erik Price <eprice@ptc.com> writes: > Doug McNaught wrote: > > > Actually, I'm pretty sure it's "template1". Where in the docs does it > > say "template0"? > > Maybe it is template1. I have a DB on my system named template0, but > I might have created that right when I first installed PostgreSQL as > an experiment and forgot about it. I also have a template1 database. There are both. template1 is the default, and you can add stuff to it so newly created databases have the right contents. template0 is the "virgin" copy that you use if you accidentally drop or screw up template1. :) -Doug
On Fri, 20 Jun 2003, Erik Price wrote: > > > Doug McNaught wrote: > > > Actually, I'm pretty sure it's "template1". Where in the docs does it > > say "template0"? > > Maybe it is template1. I have a DB on my system named template0, but I > might have created that right when I first installed PostgreSQL as an > experiment and forgot about it. I also have a template1 database. Actually, template0 is the "oh dear god, what have I done to template1" recovery database that the initdb script (now program) creates when run. template1 is the "hey, if i add a language here, all my new databases get it too" database. You generally can't connect to tempalte0, by the way. Just all FYI.
On Fri, Jun 20, 2003 at 11:14:25AM -0600, scott.marlowe wrote: > Actually, template0 is the "oh dear god, what have I done to template1" > recovery database that the initdb script (now program) creates when run. I think template0 is also used to compute differences that should be written by pg_dump or pg_dumpall. I don't recall the exact details... -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Aprender sin pensar es inutil; pensar sin aprender, peligroso" (Confucio)
Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > On Fri, Jun 20, 2003 at 11:14:25AM -0600, scott.marlowe wrote: >> Actually, template0 is the "oh dear god, what have I done to template1" >> recovery database that the initdb script (now program) creates when run. > I think template0 is also used to compute differences that should be > written by pg_dump or pg_dumpall. I don't recall the exact details... This is all covered in TFM ... http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=manage-ag-templatedbs.html regards, tom lane
I'm just getting around to dropping the sequence on my table (after a
hiatus)...
> I would expect that you would first need to
>
> ALTER TABLE ... COLUMN ... DROP DEFAULT
>
> to remove the dependency on the sequence. Then drop sequence should work.
That doesn't seem to work, strangely -- I've executed the following command:
ALTER TABLE news ALTER COLUMN news_id DROP DEFAULT
Fine, and now my table looks like this:
be_db=# \d news;
Table "public.news"
+-[ RECORD 1 ]----------------------------+
| Column | news_id |
| Type | integer |
| Modifiers | not null |
+-[ RECORD 2 ]----------------------------+
| Column | news_date |
| Type | timestamp without time zone |
| Modifiers | not null |
+-[ RECORD 3 ]----------------------------+
| Column | expire_date |
| Type | date |
| Modifiers | not null |
+-[ RECORD 4 ]----------------------------+
| Column | news_title |
| Type | character varying(64) |
| Modifiers | not null default '' |
+-[ RECORD 5 ]----------------------------+
| Column | news_info |
| Type | text |
| Modifiers | not null |
+-[ RECORD 6 ]----------------------------+
| Column | user_id |
| Type | integer |
| Modifiers | not null |
+-----------+-----------------------------+
Indexes: news_pkey primary key btree (news_id)
Foreign Key constraints: $1 FOREIGN KEY (user_id) REFERENCES
users(user_id) ON UPDATE NO ACTION ON D
ELETE NO ACTION
But when I try to drop the sequence altogether:
be_db=# DROP SEQUENCE news_news_id_seq;
ERROR: Cannot drop sequence news_news_id_seq because table news column
news_id requires it
You may drop table news column news_id instead
Anybody have a suggestion? I just want to remove these sequences from
the database altogether. Am I going to have to drop the news_id column
altogether?
Erik
Erik Price <eprice@ptc.com> writes:
> But when I try to drop the sequence altogether:
> be_db=# DROP SEQUENCE news_news_id_seq;
> ERROR: Cannot drop sequence news_news_id_seq because table news column
> news_id requires it
That's intentional: the sequence is part of the implementation of a
serial column, and you should not be able to remove it separately.
(You really shouldn't be able to dork with the column's DEFAULT
expression either, but we don't currently have a way to enforce that.)
> Anybody have a suggestion? I just want to remove these sequences from
> the database altogether. Am I going to have to drop the news_id column
> altogether?
If you are really intent on deconstructing the sequence mechanism for
this column, you could find and delete the pg_depend row that represents
the dependency. Details are left as an exercise for the student ...
regards, tom lane