Thread: Moving sequences to another schema

Moving sequences to another schema

From
Bernd Helmle
Date:
I'm still playing around with the ALTER OBJECT SET SCHEMA stuff. I managed 
to alter indexes and constraints as well for tables, but with SERIAL 
sequences there is one little problem:

When altering a sequence created by a SERIAL column type (i do this by 
examining pg_depend to avoid moving any other sequences that are 
'foreign'), i need to recreate the default expression for the SERIAL column 
(stored in pg_attrdef.adbin). Is there an API to do that, or do i have to 
recreate the executable expression tree from scratch? Or am i missing 
something completely...

TIA

-- 

 Bernd


Re: Moving sequences to another schema

From
Alvaro Herrera
Date:
On Tue, Jun 28, 2005 at 01:43:27AM +0200, Bernd Helmle wrote:

> When altering a sequence created by a SERIAL column type (i do this by 
> examining pg_depend to avoid moving any other sequences that are 
> 'foreign'), i need to recreate the default expression for the SERIAL column 
> (stored in pg_attrdef.adbin). Is there an API to do that, or do i have to 
> recreate the executable expression tree from scratch? Or am i missing 
> something completely...

I think this is done by AddRelationRawConstraints.  You'd have to get
the parsetree of the default expression.  I think you could get that by
applying raw_parser() to pg_attrdef.adsrc.

-- 
Alvaro Herrera (<alvherre[a]surnet.cl>)
"The Postgresql hackers have what I call a "NASA space shot" mentality.Quite refreshing in a world of "weekend drag
racer"developers."
 
(Scott Marlowe)


Re: Moving sequences to another schema

From
Christopher Kings-Lynne
Date:
> When altering a sequence created by a SERIAL column type (i do this by 
> examining pg_depend to avoid moving any other sequences that are 
> 'foreign'), i need to recreate the default expression for the SERIAL 
> column (stored in pg_attrdef.adbin). Is there an API to do that, or do i 
> have to recreate the executable expression tree from scratch? Or am i 
> missing something completely...

Does ALTER TABLE/RENAME code help you?  You can rename sequences with 
that...

Chris



Re: Moving sequences to another schema

From
Tom Lane
Date:
Alvaro Herrera <alvherre@surnet.cl> writes:
> I think this is done by AddRelationRawConstraints.  You'd have to get
> the parsetree of the default expression.  I think you could get that by
> applying raw_parser() to pg_attrdef.adsrc.

Not adsrc --- that's not trustworthy.

In practice I think you could just assume you know what the default
expression ought to be, and store a new one without looking at the old.
        regards, tom lane


Re: Moving sequences to another schema

From
Bernd Helmle
Date:
--On Dienstag, Juni 28, 2005 09:38:56 +0800 Christopher Kings-Lynne 
<chriskl@familyhealth.com.au> wrote:

> Does ALTER TABLE/RENAME code help you?  You can rename sequences with
> that...

Hmm, that doesn't cover pg_attrdef.adbin. I think the best way is to create 
the default expressions from scratch, as Tom already mentioned.

-- 

 Bernd


Re: Moving sequences to another schema

From
Bernd Helmle
Date:
--On Dienstag, Juni 28, 2005 02:01:33 -0400 Tom Lane <tgl@sss.pgh.pa.us> 
wrote:

> Not adsrc --- that's not trustworthy.
>

Yes, that's documented in the docs, too.

> In practice I think you could just assume you know what the default
> expression ought to be, and store a new one without looking at the old.

I think i'll go for that (need to figure out how to do that first, but it 
shouldn't be so hard). Whats the least that should go to -patches for 
feature release on 1 July (only to know, if i can hold timeline or not).??

-- 

 Bernd


Re: Moving sequences to another schema

From
Tom Lane
Date:
Bernd Helmle <mailings@oopsware.de> writes:
> Whats the least that should go to -patches for 
> feature release on 1 July (only to know, if i can hold timeline or not).??

Something reasonably complete, working, credible.  We'll cut you slack
on documentation changes and regression tests, and if it has a bug or
three that's what beta testing is for; but it has to at least look like
it will work.
        regards, tom lane


Re: Moving sequences to another schema

From
Bernd Helmle
Date:
--On Dienstag, Juni 28, 2005 01:43:27 +0200 Bernd Helmle 
<mailings@oopsware.de> wrote:

> When altering a sequence created by a SERIAL column type (i do this by
> examining pg_depend to avoid moving any other sequences that are
> 'foreign'), i need to recreate the default expression for the SERIAL
> column (stored in pg_attrdef.adbin).

I currently recognized that a SERIAL column doesn't only create an implicit 
sequence, it creates an implicit composite type with the same name, too. I 
think this is the same for CREATE SEQUENCE?

-- 

 Bernd


Re: Moving sequences to another schema

From
Michael Glaesemann
Date:
On Jun 30, 2005, at 8:13 PM, Bernd Helmle wrote:

> I currently recognized that a SERIAL column doesn't only create an  
> implicit sequence, it creates an implicit composite type with the  
> same name, too. I think this is the same for CREATE SEQUENCE?

Sequences are just special types of tables. All tables have a  
corresponding composite type, so sequences do as well.

CREATE TABLE foo (foo_id serial); is a shorthand for

CREATE SEQUENCE foo_foo_id_seq;
CREATE TABLE foo (foo_id integer default nextval 
('foo_foo_id_seq'::text));

Using SERIAL implies CREATE SEQUENCE.

Does this help?

Michael Glaesemann
grzm myrealbox com