Folks,

Is it a good idea for CREATE TABLE to default to WITHOUT OIDS, rather
than WITH OIDS? Naturally, this would (a) be some time in the future
(7.5, perhaps) and (b) only apply to user tables.

The two advantages I can see are:

(1) Makes the storage of most tables more efficient; while you *can*
specify WITHOUT OIDS, a lot of people won't know to, or will forget.

(2) Reduces the usage of the OID counter, making OID wraparound less
common.

The main disadvantage I can see is just backward compatibility. In order
to improve that, we could add a GUC var "use_oids_default" (or some
other name), which would control whether a CREATE TABLE defaults to WITH
or WITHOUT OIDS. In 7.4, the GUC var would default to false (so there
would be no change in behavior), but in 7.5 we could switch it to true.

Comments?

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




10 Jan 2003 16:50:26 -0500
Neil Conway <neilc@samurai.com>
default to WITHOUT OIDS?
Neil Conway wrote:

>Folks,
>
>Is it a good idea for CREATE TABLE to default to WITHOUT OIDS, rather
>than WITH OIDS? Naturally, this would (a) be some time in the future
>(7.5, perhaps) and (b) only apply to user tables.
><snip>
>
>The main disadvantage I can see is just backward compatibility. In order
>to improve that, we could add a GUC var "use_oids_default" (or some
>other name), which would control whether a CREATE TABLE defaults to WITH
>or WITHOUT OIDS. In 7.4, the GUC var would default to false (so there
>would be no change in behavior), but in 7.5 we could switch it to true.
>
>Comments?
>
The problem with getting rid of OIDs as default is there is then no way
to get the primary key of a just inserted row with out OIDs (as far as I
know)

Oracle has the ability to bind variable which allows you to use
RETURNING INTO, Postgresql only has (eg in PHP) pg_getlastoid.

eg

assuming:

Pg:
CREATE TABLE testtable(test_id serial, text1 text);

Oracle:
CREATE TABLE testtable(test_id integer, text1 VARCHAR(255));
CREATE SEQUENCE test_id_seq INCREMENT BY 1;

Postgresql
    $sql = "INSERT INTO testtable(text1) ";
    $sql .= "VALUES(" . $some_str . ") ";
    $result = pg_exec($conn, $sql);
    $oid = pg_getlastoid($result);

    $sql_pk = "SELECT test_id FROM testtable WHERE oid = '$oid'";
    $result_array = pg_fetch_array($result, 0);
    $pk = $result_array[0];

Oracle
    $sql = "INSERT INTO testtable(test_id, text1) ";
    $sql .= "VALUES(test_id_seq.nextval, :text1) RETURNING  test_id INTO
:test_id";
    $stmt = ociparse($conn,$sql);
    ocibindbyname($stmt, :text1) &$some_str, -1);
    ocibindbyname($stmt, ':test_id', &$test_id, 32);
    ociexecute($stmt,OCI_DEFAULT);

No OIDs, no way to get a handle to a just inserted row.

>
>Cheers,
>
>Neil
>  
>
Ashley Cambrell


Sat, 11 Jan 2003 10:17:55 +1100
Ashley Cambrell <ash@freaky-namuh.com>
Re: default to WITHOUT OIDS?
On Fri, 2003-01-10 at 18:17, Ashley Cambrell wrote:
> The problem with getting rid of OIDs as default is there is then no way
> to get the primary key of a just inserted row with out OIDs (as far as I
> know)

Use currval() on the PK sequence -- if you call it from within the query
that inserted a row, it is guaranteed to give you the last sequence
value that it generated.

However, I agree that one of the drawbacks of this scheme would be
breaking the OID in the status string returned by INSERT and similar
commands. Not too big a deal, IMHO (users can still get the same effect
by specifying WITH OIDS, or toggling the GUC var)...

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




10 Jan 2003 18:25:58 -0500
Neil Conway <neilc@samurai.com>
Re: default to WITHOUT OIDS?
Neil Conway <neilc@samurai.com> writes:
> Is it a good idea for CREATE TABLE to default to WITHOUT OIDS, rather
> than WITH OIDS?

Are you intending that pg_dump will always attach either WITH OIDS or
WITHOUT OIDS to its CREATE TABLE commands?

If we do not do so, the behavior of a dump and reload will be dependent
on the setting of the GUC variable at reload time, which seems like a
big hazard.  On the other hand, if we do do that then (a) pg_dump output
becomes even less portable than it is now, and (b) upgraded databases
will still have OIDs, which renders the "improved storage efficiency"
argument a bit thin.

			regards, tom lane

Fri, 10 Jan 2003 18:44:54 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: default to WITHOUT OIDS? 
On Fri, 2003-01-10 at 18:44, Tom Lane wrote:
> Are you intending that pg_dump will always attach either WITH OIDS or
> WITHOUT OIDS to its CREATE TABLE commands?

Now that I think about it, I'd think pg_dump should attach one or the
other to all CREATE TABLE commands, regardless of the GUC variable
setting.

> On the other hand, if we do do that then (a) pg_dump output
> becomes even less portable than it is now, and (b) upgraded databases
> will still have OIDs, which renders the "improved storage efficiency"
> argument a bit thin.

Those are both valid points, but I still think that eventually
defaulting to WITHOUT OIDS is the right way to go.

Personally, I don't think (a) is that important (if it *is* important,
we're doing pretty poorly in that regard right now).

As for (b), I think it ultimately boils down to the need to strike a
balance between backward compatibility and new features/fixes. It's
definately true that if the GUC var only applies to newly created tables
(and not dumped ones), it will take longer for the change to take
effect. But I'm not so sure there's anything wrong with that: if we
provide an ALTER TABLE variant to allow users to remove the OIDs from an
extant table, then we're effectively leaving the decision of when to
make the transition entirely within the user's hands.

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




10 Jan 2003 19:31:55 -0500
Neil Conway <neilc@samurai.com>
Re: default to WITHOUT OIDS?
The real question is how tables are dumped from 7.3 and below.  Does it
always explicitly specify 'WITH OIDS' for tables with OIDs?

If not, this would have little benefit for me I guess...

I still vote for the ability to drop OIDs from a table :)

Chris

On 10 Jan 2003, Neil Conway wrote:

> Folks,
>
> Is it a good idea for CREATE TABLE to default to WITHOUT OIDS, rather
> than WITH OIDS? Naturally, this would (a) be some time in the future
> (7.5, perhaps) and (b) only apply to user tables.
>
> The two advantages I can see are:
>
> (1) Makes the storage of most tables more efficient; while you *can*
> specify WITHOUT OIDS, a lot of people won't know to, or will forget.
>
> (2) Reduces the usage of the OID counter, making OID wraparound less
> common.
>
> The main disadvantage I can see is just backward compatibility. In order
> to improve that, we could add a GUC var "use_oids_default" (or some
> other name), which would control whether a CREATE TABLE defaults to WITH
> or WITHOUT OIDS. In 7.4, the GUC var would default to false (so there
> would be no change in behavior), but in 7.5 we could switch it to true.
>
> Comments?
>
> Cheers,
>
> Neil
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Sat, 11 Jan 2003 10:22:20 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: default to WITHOUT OIDS?
So what actually is the point of OIDs then?  If you set OIDs ff by default
and use currval, what's the point of having OIDs at all?

Chris

On 10 Jan 2003, Neil Conway wrote:

> On Fri, 2003-01-10 at 18:17, Ashley Cambrell wrote:
> > The problem with getting rid of OIDs as default is there is then no way
> > to get the primary key of a just inserted row with out OIDs (as far as I
> > know)
>
> Use currval() on the PK sequence -- if you call it from within the query
> that inserted a row, it is guaranteed to give you the last sequence
> value that it generated.
>
> However, I agree that one of the drawbacks of this scheme would be
> breaking the OID in the status string returned by INSERT and similar
> commands. Not too big a deal, IMHO (users can still get the same effect
> by specifying WITH OIDS, or toggling the GUC var)...
>
> Cheers,
>
> Neil
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


Sat, 11 Jan 2003 10:27:43 +0800 (WST)
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: default to WITHOUT OIDS?
Neil Conway wrote:

>On Fri, 2003-01-10 at 18:17, Ashley Cambrell wrote:
>  
>
>>The problem with getting rid of OIDs as default is there is then no way
>>to get the primary key of a just inserted row with out OIDs (as far as I
>>know)
>>    
>>
>
>Use currval() on the PK sequence -- if you call it from within the query
>that inserted a row, it is guaranteed to give you the last sequence
>value that it generated.
>  
>

First problem though is that you have to know the sequence name that is
autogenerated from the serial

secondly, I thought that sequences worked outside of transactions..

process 1

INSERT INTO testtable(text1) VALUES('blah1');  -- testid_seq now 1

process 2

INSERT INTO testtable(text1) VALUES('blah2'); -- testid_seq now 2
SELECT currval('testid_seq'); --  returns 2

process 1
SELECT currval('testid_seq'); --  returns 2

??

>However, I agree that one of the drawbacks of this scheme would be
>breaking the OID in the status string returned by INSERT and similar
>commands. Not too big a deal, IMHO (users can still get the same effect
>by specifying WITH OIDS, or toggling the GUC var)...
>  
>
The users who don't know any better and expect that pg_getlastoid should
return the last oid will have trouble.  There really should be
alternative first, like binding variables and RETURNING INTO.  Is it on
the TODO list by any chance? 

>Cheers,
>
>Neil
>  
>
Ashley Cambrell







Neil Conway wrote:

On Fri, 2003-01-10 at 18:17, Ashley Cambrell wrote:
  

The problem with getting rid of OIDs as default is there is then no way
to get the primary key of a just inserted row with out OIDs (as far as I
know)
    


Use currval() on the PK sequence -- if you call it from within the query
that inserted a row, it is guaranteed to give you the last sequence
value that it generated.
  


First problem though is that you have to know the sequence name that is
autogenerated from the serial

secondly, I thought that sequences worked outside of transactions..

process 1

INSERT INTO testtable(text1) VALUES('blah1');  -- testid_seq now 1

process 2

INSERT INTO testtable(text1) VALUES('blah2'); -- testid_seq now 2
SELECT currval('testid_seq');  --  returns 2

process 1
SELECT currval('testid_seq'); --  returns 2

??



However, I agree that one of the drawbacks of this scheme would be
breaking the OID in the status string returned by INSERT and similar
commands. Not too big a deal, IMHO (users can still get the same effect
by specifying WITH OIDS, or toggling the GUC var)...
  

The users who don't know any better and expect that pg_getlastoid
should return the last oid will have trouble.  There really should be
alternative first, like binding variables and RETURNING INTO.  Is it on
the TODO list by any chance?  


Cheers,

Neil
  

Ashley Cambrell
Sat, 11 Jan 2003 14:14:33 +1100
Ashley Cambrell <ash@freaky-namuh.com>
Re: default to WITHOUT OIDS?
On Fri, 2003-01-10 at 21:27, Christopher Kings-Lynne wrote:
> So what actually is the point of OIDs then?

My understanding is that they're used to uniquely identify entries in
system catalogs. If there's a good reason to make use of OIDs on user
tables, I can't see it...

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




11 Jan 2003 00:35:22 -0500
Neil Conway <neilc@samurai.com>
Re: default to WITHOUT OIDS?
On Fri, 2003-01-10 at 22:14, Ashley Cambrell wrote:
> First problem though is that you have to know the sequence name that
> is autogenerated from the serial

... which is not a legitimate problem, IMHO.

> secondly, I thought that sequences worked outside of transactions

They do, but obviously not in the way you think:

client1=> create table test (a serial);
client1=> insert into test default values;
client1=> select currval('test_a_seq'); ===> returns 1
client2=> insert into test default values;
client2=> select currval('test_a_seq'); ===> returns 2
client1=> select currval('test_a_seq'); ===> (still) returns 1

In any case, using OIDs to uniquely identify rows in user tables isn't a
good idea, IMHO:

(a) OIDs on user tables aren't unique, unless you manually create a
unique index

(b) If you create a unique index and the OID counter wraps around,
you're going to be in trouble

(c) OIDs are only 4-byte (and this isn't likely to change anytime soon,
AFAIK), so wraparound isn't out of the question for many installations

> The users who don't know any better and expect that pg_getlastoid
> should return the last oid will have trouble.

I don't see why: for one thing, the OID field is *already* optional (and
the PHP documentation for the function you're referring to mentions this
explicitely). And if they need to use OIDs in their tables, they can (a)
specify WITH OIDS (b) enable the GUC var so that WITH OIDS is the
default.

> There really should be alternative first, like binding variables and
> RETURNING INTO.  Is it on the TODO list by any chance?

IIRC, someone (Philip W?) mentioned they might implement something like
this...

Cheers,

Neil
-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC




11 Jan 2003 00:40:18 -0500
Neil Conway <neilc@samurai.com>
Re: default to WITHOUT OIDS?
Neil Conway writes:

> > On the other hand, if we do do that then (a) pg_dump output
> > becomes even less portable than it is now, and (b) upgraded databases
> > will still have OIDs, which renders the "improved storage efficiency"
> > argument a bit thin.

> Personally, I don't think (a) is that important (if it *is* important,
> we're doing pretty poorly in that regard right now).

It is important, and if you write your database in a portable fashion then
it does a pretty good job at it.  Some effort was put into 7.3 to make it
so, and I would hate to abandon it.

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 12 Jan 2003 17:20:38 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: default to WITHOUT OIDS?
>>>Neil Conway said:
 > On Fri, 2003-01-10 at 21:27, Christopher Kings-Lynne wrote:
 > > So what actually is the point of OIDs then?
 > 
 > My understanding is that they're used to uniquely identify entries in
 > system catalogs. If there's a good reason to make use of OIDs on user
 > tables, I can't see it...

What happens if you have an existing database and want to load new tables, 
that rely on their OIDs (the OIDs of the rows actually) to refer to data in 
other tables (the 'old' way)?

Normally, one would dump the old tables 'with oids' and copy to the new 
database 'with oids'. Chances are, there will be duplicate OIDs in the 
database - in the existing and new tables....

Daniel


Mon, 13 Jan 2003 10:43:38 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: default to WITHOUT OIDS? 
If ever this happens, same should be considered for tables created via the 
SELECT INTO statement. These are in many cases 'temporary' in nature and do 
not need OIDs (while making much use of the OIDs counter).

Daniel


Mon, 13 Jan 2003 10:45:30 +0200
Daniel Kalchev <daniel@digsys.bg>
Re: default to WITHOUT OIDS? 
Daniel Kalchev <daniel@digsys.bg> writes:
> If ever this happens, same should be considered for tables created via the 
> SELECT INTO statement. These are in many cases 'temporary' in nature and do 
> not need OIDs (while making much use of the OIDs counter).

SELECT INTO does create tables without OIDs, as of 7.3.  We've already
had complaints about that ;-)

			regards, tom lane

Mon, 13 Jan 2003 10:15:26 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: default to WITHOUT OIDS? 
Tom Lane wrote:
> Daniel Kalchev <daniel@digsys.bg> writes:
> 
>>If ever this happens, same should be considered for tables created via the 
>>SELECT INTO statement. These are in many cases 'temporary' in nature and do 
>>not need OIDs (while making much use of the OIDs counter).
> 
> 
> SELECT INTO does create tables without OIDs, as of 7.3.  We've already
> had complaints about that ;-)

I very recently updated one of my servers to 7.3.1. Various MS tools have 
started to give me guff when trying to access views in databases on that 
server through ODBC. Especially, MS Query (yes, I have some Excel users 
needing that) started complaining that "this table has no OID", which 
really means that the ODBC driver complaints that ...

Is that a side effect of the above problem ?

Sincerely,

					Emmanuel Charpentier



Mon, 20 Jan 2003 07:13:00 +0100
Emmanuel Charpentier <charpent@bacbuc.dyndns.org>
Re: default to WITHOUT OIDS? Possible related problem
Emmanuel Charpentier wrote:
> Tom Lane wrote:
> > Daniel Kalchev <daniel@digsys.bg> writes:
> > 
> >>If ever this happens, same should be considered for tables created via the 
> >>SELECT INTO statement. These are in many cases 'temporary' in nature and do 
> >>not need OIDs (while making much use of the OIDs counter).
> > 
> > 
> > SELECT INTO does create tables without OIDs, as of 7.3.  We've already
> > had complaints about that ;-)
> 
> I very recently updated one of my servers to 7.3.1. Various MS tools have 
> started to give me guff when trying to access views in databases on that 
> server through ODBC. Especially, MS Query (yes, I have some Excel users 
> needing that) started complaining that "this table has no OID", which 
> really means that the ODBC driver complaints that ...
> 
> Is that a side effect of the above problem ?

Yes.  We think we may have a fix in 7.3.2.

-- 
  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

Sat, 25 Jan 2003 21:16:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: default to WITHOUT OIDS? Possible related problem