Lists: | pgsql-hackers |
---|
From: | Bernd Helmle <mailings(at)oopsware(dot)de> |
---|---|
To: | PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Moving sequences to another schema |
Date: | 2005-06-27 23:43:27 |
Message-ID: | 7C497D6E6EE1BE19A3D28337@sparkey.oopsware.intra |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Alvaro Herrera <alvherre(at)surnet(dot)cl> |
---|---|
To: | Bernd Helmle <mailings(at)oopsware(dot)de> |
Cc: | PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Moving sequences to another schema |
Date: | 2005-06-28 01:23:36 |
Message-ID: | 20050628012336.GC15765@surnet.cl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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)
From: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | Bernd Helmle <mailings(at)oopsware(dot)de> |
Cc: | PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Moving sequences to another schema |
Date: | 2005-06-28 01:38:56 |
Message-ID: | 42C0AA30.7060304@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
> 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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)surnet(dot)cl> |
Cc: | Bernd Helmle <mailings(at)oopsware(dot)de>, PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Moving sequences to another schema |
Date: | 2005-06-28 06:01:33 |
Message-ID: | 11195.1119938493@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Alvaro Herrera <alvherre(at)surnet(dot)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
From: | Bernd Helmle <mailings(at)oopsware(dot)de> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Moving sequences to another schema |
Date: | 2005-06-28 21:00:48 |
Message-ID: | 0C7153D3C100C765CBB224AE@sparkey.oopsware.intra |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
--On Dienstag, Juni 28, 2005 09:38:56 +0800 Christopher Kings-Lynne
<chriskl(at)familyhealth(dot)com(dot)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
From: | Bernd Helmle <mailings(at)oopsware(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Alvaro Herrera <alvherre(at)surnet(dot)cl>, PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Moving sequences to another schema |
Date: | 2005-06-28 21:01:08 |
Message-ID: | E111B521ED2D9144FE8576F2@sparkey.oopsware.intra |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
--On Dienstag, Juni 28, 2005 02:01:33 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bernd Helmle <mailings(at)oopsware(dot)de> |
Cc: | Alvaro Herrera <alvherre(at)surnet(dot)cl>, PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Moving sequences to another schema |
Date: | 2005-06-29 03:36:55 |
Message-ID: | 4321.1120016215@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Bernd Helmle <mailings(at)oopsware(dot)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
From: | Bernd Helmle <mailings(at)oopsware(dot)de> |
---|---|
To: | Bernd Helmle <mailings(at)oopsware(dot)de> |
Cc: | PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Moving sequences to another schema |
Date: | 2005-06-30 11:13:37 |
Message-ID: | 2DF000A0E60B0FD258FD75A7@sparkey.oopsware.intra |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
--On Dienstag, Juni 28, 2005 01:43:27 +0200 Bernd Helmle
<mailings(at)oopsware(dot)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
From: | Michael Glaesemann <grzm(at)myrealbox(dot)com> |
---|---|
To: | Bernd Helmle <mailings(at)oopsware(dot)de> |
Cc: | PostgreSQL-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Moving sequences to another schema |
Date: | 2005-06-30 16:01:27 |
Message-ID: | 932563F9-E00D-4423-988A-6BE95FB50131@myrealbox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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