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

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

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

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

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:

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:

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

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

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

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

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

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

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

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

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