introduce "default_use_oids"

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

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