Thread: introduce "default_use_oids"
This patch adds a new GUC var, "default_use_oids", which follows the proposal for eventually deprecating OIDs on user tables that I posted earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or WITHOUT OIDS when dumping a table. The documentation has been updated. Comments are welcome. (This patch is for the 7.5 queue.) -Neil
Attachment
This, and the email thead, were added to the queue for 7.5:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Neil Conway wrote:
> This patch adds a new GUC var, "default_use_oids", which follows the
> proposal for eventually deprecating OIDs on user tables that I posted
> earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or
> WITHOUT OIDS when dumping a table. The documentation has been updated.
>
> Comments are welcome.
>
> (This patch is for the 7.5 queue.)
>
> -Neil
>
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Tue, 30 Sep 2003 20:00:22 -0400, Neil Conway <neilc@samurai.com>
wrote:
>This patch adds a new GUC var, "default_use_oids"
Shouldn't it be honoured by CREATE TABLE AS SELECT ... ?
This checkin might contain hints to the places that have to be
modified:
2003-01-23 tgl
* src/backend/executor/execMain.c 1.199:
* src/backend/executor/execMain.c [REL7_3_STABLE] 1.180.2.1:
* src/backend/executor/execUtils.c 1.96:
* src/backend/executor/execUtils.c [REL7_3_STABLE] 1.90.2.1:
* src/include/nodes/execnodes.h 1.92:
* src/include/nodes/execnodes.h [REL7_3_STABLE] 1.75.2.1:
Change CREATE TABLE AS / SELECT INTO to create the new table with
OIDs, for backwards compatibility with pre-7.3 behavior. Per
discussion on pgsql-general and pgsql-hackers.
Servus
Manfred
On Fri, 2003-10-10 at 05:56, Manfred Koizar wrote: > On Tue, 30 Sep 2003 20:00:22 -0400, Neil Conway <neilc@samurai.com> > wrote: > >This patch adds a new GUC var, "default_use_oids" > > Shouldn't it be honoured by CREATE TABLE AS SELECT ... ? Good catch. I've attached an updated patch. I also included a few improvements to the docs. I think it would be a good idea to extend CREATE TABLE AS to allow WITH OIDS and WITHOUT OIDS to be specified, so that it provides a better option for people who need OIDs in their CREATE TABLE AS-generated tables than manually setting the default_use_oids GUC var (CREATE TABLE AS should also accept ON COMMIT ..., for that matter). But implementing this will require changing the internal representation of CREATE TABLE AS to be something more than just a wrapper over SelectStmt, I believe. I haven't made this change in the attached patch, but I'll probably do it before 7.5 is released. Any comments? -Neil
Attachment
This has been saved for the 7.5 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Neil Conway wrote:
> On Fri, 2003-10-10 at 05:56, Manfred Koizar wrote:
> > On Tue, 30 Sep 2003 20:00:22 -0400, Neil Conway <neilc@samurai.com>
> > wrote:
> > >This patch adds a new GUC var, "default_use_oids"
> >
> > Shouldn't it be honoured by CREATE TABLE AS SELECT ... ?
>
> Good catch. I've attached an updated patch. I also included a few
> improvements to the docs.
>
> I think it would be a good idea to extend CREATE TABLE AS to allow WITH
> OIDS and WITHOUT OIDS to be specified, so that it provides a better
> option for people who need OIDs in their CREATE TABLE AS-generated
> tables than manually setting the default_use_oids GUC var (CREATE TABLE
> AS should also accept ON COMMIT ..., for that matter). But implementing
> this will require changing the internal representation of CREATE TABLE
> AS to be something more than just a wrapper over SelectStmt, I believe.
> I haven't made this change in the attached patch, but I'll probably do
> it before 7.5 is released. Any comments?
>
> -Neil
>
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Neil Conway wrote:
> On Fri, 2003-10-10 at 05:56, Manfred Koizar wrote:
> > On Tue, 30 Sep 2003 20:00:22 -0400, Neil Conway <neilc@samurai.com>
> > wrote:
> > >This patch adds a new GUC var, "default_use_oids"
> >
> > Shouldn't it be honoured by CREATE TABLE AS SELECT ... ?
>
> Good catch. I've attached an updated patch. I also included a few
> improvements to the docs.
>
> I think it would be a good idea to extend CREATE TABLE AS to allow WITH
> OIDS and WITHOUT OIDS to be specified, so that it provides a better
> option for people who need OIDs in their CREATE TABLE AS-generated
> tables than manually setting the default_use_oids GUC var (CREATE TABLE
> AS should also accept ON COMMIT ..., for that matter). But implementing
> this will require changing the internal representation of CREATE TABLE
> AS to be something more than just a wrapper over SelectStmt, I believe.
> I haven't made this change in the attached patch, but I'll probably do
> it before 7.5 is released. Any comments?
>
> -Neil
>
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian writes: > Your patch has been added to the PostgreSQL unapplied patches list at: > > http://momjian.postgresql.org/cgi-bin/pgpatches > > I will try to apply it within the next 48 hours. This parameter ought to be called "default_with_oids", to reflect the actual effect. > > --------------------------------------------------------------------------- > > > Neil Conway wrote: > > On Fri, 2003-10-10 at 05:56, Manfred Koizar wrote: > > > On Tue, 30 Sep 2003 20:00:22 -0400, Neil Conway <neilc@samurai.com> > > > wrote: > > > >This patch adds a new GUC var, "default_use_oids" > > > > > > Shouldn't it be honoured by CREATE TABLE AS SELECT ... ? > > > > Good catch. I've attached an updated patch. I also included a few > > improvements to the docs. > > > > I think it would be a good idea to extend CREATE TABLE AS to allow WITH > > OIDS and WITHOUT OIDS to be specified, so that it provides a better > > option for people who need OIDs in their CREATE TABLE AS-generated > > tables than manually setting the default_use_oids GUC var (CREATE TABLE > > AS should also accept ON COMMIT ..., for that matter). But implementing > > this will require changing the internal representation of CREATE TABLE > > AS to be something more than just a wrapper over SelectStmt, I believe. > > I haven't made this change in the attached patch, but I'll probably do > > it before 7.5 is released. Any comments? > > > > -Neil > > > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > -- Peter Eisentraut peter_e@gmx.net
Agreed. --------------------------------------------------------------------------- Peter Eisentraut wrote: > Bruce Momjian writes: > > > Your patch has been added to the PostgreSQL unapplied patches list at: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches > > > > I will try to apply it within the next 48 hours. > > This parameter ought to be called "default_with_oids", to reflect the > actual effect. > > > > > --------------------------------------------------------------------------- > > > > > > Neil Conway wrote: > > > On Fri, 2003-10-10 at 05:56, Manfred Koizar wrote: > > > > On Tue, 30 Sep 2003 20:00:22 -0400, Neil Conway <neilc@samurai.com> > > > > wrote: > > > > >This patch adds a new GUC var, "default_use_oids" > > > > > > > > Shouldn't it be honoured by CREATE TABLE AS SELECT ... ? > > > > > > Good catch. I've attached an updated patch. I also included a few > > > improvements to the docs. > > > > > > I think it would be a good idea to extend CREATE TABLE AS to allow WITH > > > OIDS and WITHOUT OIDS to be specified, so that it provides a better > > > option for people who need OIDs in their CREATE TABLE AS-generated > > > tables than manually setting the default_use_oids GUC var (CREATE TABLE > > > AS should also accept ON COMMIT ..., for that matter). But implementing > > > this will require changing the internal representation of CREATE TABLE > > > AS to be something more than just a wrapper over SelectStmt, I believe. > > > I haven't made this change in the attached patch, but I'll probably do > > > it before 7.5 is released. Any comments? > > > > > > -Neil > > > > > > > [ Attachment, skipping... ] > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 8: explain analyze is your friend > > > > > > -- > Peter Eisentraut peter_e@gmx.net > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Updated patch applied. Thanks. --------------------------------------------------------------------------- Neil Conway wrote: > This patch adds a new GUC var, "default_use_oids", which follows the > proposal for eventually deprecating OIDs on user tables that I posted > earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or > WITHOUT OIDS when dumping a table. The documentation has been updated. > > Comments are welcome. > > (This patch is for the 7.5 queue.) > > -Neil > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Mon, Dec 01, 2003 at 05:07:40PM -0500, Bruce Momjian wrote: > Neil Conway wrote: > > This patch adds a new GUC var, "default_use_oids", which follows the > > proposal for eventually deprecating OIDs on user tables that I posted > > earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or > > WITHOUT OIDS when dumping a table. The documentation has been updated. > > > > Comments are welcome. Hum, sorry to be late, but wasn't one of the supposed strenghts of pg_dump supposed to be that you could take a dump and load it on a different RDBMS? I haven't tried it so I don't know if it works, but this patch takes out the ability to do that -- no one else will accept WITH/WITHOUT OIDS, so the dump will have to be modified. Is a switch provided to stop the emission of those modifiers? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Escucha y olvidarás; ve y recordarás; haz y entenderás" (Confucio)
Alvaro Herrera writes: > On Mon, Dec 01, 2003 at 05:07:40PM -0500, Bruce Momjian wrote: > > > Neil Conway wrote: > > > This patch adds a new GUC var, "default_use_oids", which follows the > > > proposal for eventually deprecating OIDs on user tables that I posted > > > earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or > > > WITHOUT OIDS when dumping a table. The documentation has been updated. > > > > > > Comments are welcome. > > Hum, sorry to be late, but wasn't one of the supposed strenghts of > pg_dump supposed to be that you could take a dump and load it on a > different RDBMS? I haven't tried it so I don't know if it works, but > this patch takes out the ability to do that -- no one else will accept > WITH/WITHOUT OIDS, so the dump will have to be modified. Is a switch > provided to stop the emission of those modifiers? I agree with that. By default, WITH/WITHOUT OIDS should not be dumped. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote:
> Alvaro Herrera writes:
>
> > On Mon, Dec 01, 2003 at 05:07:40PM -0500, Bruce Momjian wrote:
> >
> > > Neil Conway wrote:
> > > > This patch adds a new GUC var, "default_use_oids", which follows the
> > > > proposal for eventually deprecating OIDs on user tables that I posted
> > > > earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or
> > > > WITHOUT OIDS when dumping a table. The documentation has been updated.
> > > >
> > > > Comments are welcome.
> >
> > Hum, sorry to be late, but wasn't one of the supposed strenghts of
> > pg_dump supposed to be that you could take a dump and load it on a
> > different RDBMS? I haven't tried it so I don't know if it works, but
> > this patch takes out the ability to do that -- no one else will accept
> > WITH/WITHOUT OIDS, so the dump will have to be modified. Is a switch
> > provided to stop the emission of those modifiers?
>
> I agree with that. By default, WITH/WITHOUT OIDS should not be dumped.
Yes, I see that now:
CREATE TABLE x (
y integer
) WITH OIDS;
We need a solution to this. One idea is to use SET to change the
default_with_oids setting when a table changes characteristics.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > We need a solution to this. I'm really not sure we do: the SQL produced by pg_dump was totally non-portable before, and it is slightly less portable now. ISTM that you will almost always need to do some post-processing of pg_dump's output to have any hope of loading it into another RDBMS in any case. A flag for pg_dump that told it to produce standards-compliant SQL would be a cool thing to have, but that would provide a solution to a problem that has always existed, not one that is introduced by this patch. > One idea is to use SET to change the default_with_oids setting when > a table changes characteristics. It is easy to do this if people think it would improve things. I didn't do it myself because I didn't see how it helped: SET is not in the standard either. It is just as trivial to use sed/perl/etc. to remove "SET default_with_oids ..." as it is to remove "WITH/WITHOUT OIDS". -Neil
Neil Conway writes: > I'm really not sure we do: the SQL produced by pg_dump was totally > non-portable before, Significant effort has been invested to make pg_dump output portable, and I've not had any problems with it last time I tried it. Please explain why you think it's "totally" non-portable. -- Peter Eisentraut peter_e@gmx.net
> Hum, sorry to be late, but wasn't one of the supposed strenghts of > pg_dump supposed to be that you could take a dump and load it on a > different RDBMS? I haven't tried it so I don't know if it works, but > this patch takes out the ability to do that -- no one else will accept > WITH/WITHOUT OIDS, so the dump will have to be modified. Is a switch > provided to stop the emission of those modifiers? There are so many other incompatibilities in our dumps anyway! Anyway, our first loyalty is to PostgreSQL... If someone can't run a sed script to 's/WITH OIDS//g', then they're going to have a LOT of problems... Chris
> Significant effort has been invested to make pg_dump output portable, and > I've not had any problems with it last time I tried it. Please explain > why you think it's "totally" non-portable. Functions, indexes, operators, types, aggregates, users, groups, databases, inheritance, clustering, col stats, col storage, ... What IS compatible? Very basic table definitions? Chris
Christopher Kings-Lynne writes: > > Significant effort has been invested to make pg_dump output portable, and > > I've not had any problems with it last time I tried it. Please explain > > why you think it's "totally" non-portable. > > Functions, indexes, operators, types, aggregates, users, groups, > databases, inheritance, clustering, col stats, col storage, ... > > What IS compatible? Very basic table definitions? If I want to develop a portable application or I want to port an application, then I am of course only going to use portable constructs, that is, tables and views, and possibly sequences. I'm not talking theory here -- I've actually done it and made several changes to pg_dump along the way to make the output portable. This is an actual feature that is being destroyed. I'm sure there are other ways to phase out OIDs in dumps. For example, we could set the default mode at the top (easily deleted, much safer than running a global search and replace) and then add WITH/WITHOUT OIDS only to those tables that deviate from the default. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut wrote: > If I want to develop a portable application or I want to port an > application, then I am of course only going to use portable constructs, > that is, tables and views, and possibly sequences. I'm not talking theory > here -- I've actually done it and made several changes to pg_dump along > the way to make the output portable. This is an actual feature that is > being destroyed. > > I'm sure there are other ways to phase out OIDs in dumps. For example, we > could set the default mode at the top (easily deleted, much safer than > running a global search and replace) and then add WITH/WITHOUT OIDS only > to those tables that deviate from the default. Agreed. By using SET, you could still pipe the file through another database --- the SETs would fail, but the CREATE TABLE commands would work. With WITH/WITHOUT OIDS, the CREATE TABLEs would fail. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Peter Eisentraut wrote: > Neil Conway writes: > > > I'm really not sure we do: the SQL produced by pg_dump was totally > > non-portable before, > > Significant effort has been invested to make pg_dump output portable, and > I've not had any problems with it last time I tried it. Please explain > why you think it's "totally" non-portable. Also, I think we have to have a SET before every CREATE TABLE. If we don't how does it work if we restore a single table from the dump? We must handle this type of thing with SET SESSION AUTHORIZATION to make sure we are the proper owner, so it seems we could do the same with oids. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Also, I think we have to have a SET before every CREATE TABLE.
No, only when the value changes from last time. This is not rocket
science, it's the way pg_dump handles SETs already.
regards, tom lane
> If I want to develop a portable application or I want to port an > application, then I am of course only going to use portable constructs, > that is, tables and views, and possibly sequences. I'm not talking theory > here -- I've actually done it and made several changes to pg_dump along > the way to make the output portable. This is an actual feature that is > being destroyed. OK. > I'm sure there are other ways to phase out OIDs in dumps. For example, we > could set the default mode at the top (easily deleted, much safer than > running a global search and replace) and then add WITH/WITHOUT OIDS only > to those tables that deviate from the default. Ok, more thought is required then. Chris
Peter Eisentraut <peter_e@gmx.net> writes: > I'm sure there are other ways to phase out OIDs in dumps. Okay, fair enough -- I'll submit a patch to change this. -Neil
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Also, I think we have to have a SET before every CREATE TABLE. > > No, only when the value changes from last time. This is not rocket > science, it's the way pg_dump handles SETs already. Yea, that was my point of SET SESSION AUTHORIAZTION --- we already have code to track current user and issue proper set, and somehow restoring individual tables also works in those cases --- let's do the same with oids. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
I see we still are dumping CREATE TABLE using WITH OIDS, rather than using the more portable SET default_with_oids. This needs fixing. --------------------------------------------------------------------------- Alvaro Herrera wrote: > On Mon, Dec 01, 2003 at 05:07:40PM -0500, Bruce Momjian wrote: > > > Neil Conway wrote: > > > This patch adds a new GUC var, "default_use_oids", which follows the > > > proposal for eventually deprecating OIDs on user tables that I posted > > > earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or > > > WITHOUT OIDS when dumping a table. The documentation has been updated. > > > > > > Comments are welcome. > > Hum, sorry to be late, but wasn't one of the supposed strenghts of > pg_dump supposed to be that you could take a dump and load it on a > different RDBMS? I haven't tried it so I don't know if it works, but > this patch takes out the ability to do that -- no one else will accept > WITH/WITHOUT OIDS, so the dump will have to be modified. Is a switch > provided to stop the emission of those modifiers? > > -- > Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) > "Escucha y olvidar?s; ve y recordar?s; haz y entender?s" (Confucio) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes: > I see we still are dumping CREATE TABLE using WITH OIDS, rather than > using the more portable SET default_with_oids. This needs fixing. Yes, I know. If you're eager for it, please consider implementing it yourself. -Neil
Neil Conway wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I see we still are dumping CREATE TABLE using WITH OIDS, rather than > > using the more portable SET default_with_oids. This needs fixing. > > Yes, I know. If you're eager for it, please consider implementing it > yourself. Or I will consider removing the feature because we never agreed to the current behavior. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Alvaro Herrera wrote: > On Mon, Dec 01, 2003 at 05:07:40PM -0500, Bruce Momjian wrote: > > > Neil Conway wrote: > > > This patch adds a new GUC var, "default_use_oids", which follows the > > > proposal for eventually deprecating OIDs on user tables that I posted > > > earlier to pgsql-hackers. pg_dump now always specifies WITH OIDS or > > > WITHOUT OIDS when dumping a table. The documentation has been updated. > > > > > > Comments are welcome. > > Hum, sorry to be late, but wasn't one of the supposed strenghts of > pg_dump supposed to be that you could take a dump and load it on a > different RDBMS? I haven't tried it so I don't know if it works, but > this patch takes out the ability to do that -- no one else will accept > WITH/WITHOUT OIDS, so the dump will have to be modified. Is a switch > provided to stop the emission of those modifiers? Fixed with my patch from yesterday. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073