Thread: default to WITHOUT OIDS?
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
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_idINTO
: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: > 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 >
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) >
Neil Conway wrote:<br /><blockquote cite="mid1042241157.743.17.camel@tokyo" type="cite"><pre wrap="">On Fri,
2003-01-10at 18:17, Ashley Cambrell wrote: </pre><blockquote type="cite"><pre wrap="">The problem with getting rid of
OIDsas 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) </pre></blockquote><pre wrap="">
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. </pre></blockquote><br /> First problem though is that you have to know the sequence name that
isautogenerated from the serial<br /><br /> secondly, I thought that sequences worked outside of transactions..<br
/><br/> process 1<br /><br /> INSERT INTO testtable(text1) VALUES('blah1'); -- testid_seq now 1<br /><br /> process
2<br/><br /> INSERT INTO testtable(text1) VALUES('blah2'); -- testid_seq now 2<br /> SELECT currval('testid_seq'); --
returns2<br /><br /> process 1<br /> SELECT currval('testid_seq'); -- returns 2<br /><br /> ??<br /><br /><blockquote
cite="mid1042241157.743.17.camel@tokyo"type="cite"><pre wrap="">
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)... </pre></blockquote> The users who don't know any better and expect
thatpg_getlastoid should return the last oid will have trouble. There really should be alternative first, like binding
variablesand RETURNING INTO. Is it on the TODO list by any chance? <br /><blockquote
cite="mid1042241157.743.17.camel@tokyo"type="cite"><pre wrap="">
Cheers,
Neil </pre></blockquote> Ashley Cambrell<br />
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 OIDsthen?> > My understanding is that they're used to uniquely identify entries in> system catalogs. If there's a good reasonto 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, Pennsylvania19073