pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: OIDs as keys
26+ messages / 9 participants
[nested] [flat]
* Re: OIDs as keys
@ 2003-02-26 13:58 Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 16:02 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 26+ messages in thread
From: Richard Huxton @ 2003-02-26 13:58 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; pgsql-performance
On Wednesday 26 Feb 2003 1:04 pm, daniel alvarez wrote:
> In some older mails in the archive I found rumors about difficulcies that
> might
> occur when OIDs are used as an integral part of a data model.
>
> I am considering the option of placing an index on the already existing oid
> and using
> it as the primary key for all tables (saves some space and a sequence
> lookup). This
> includes saving the oid in foreign keys (virtual ones, not actually
> declared references).
> I read that using OID in keys is generally a bad idea. Is it really? Why
> exactly?
OIDs are not even guaranteed to be there any more - you can create a table
WITHOUT OIDs if you want to save some space. If you want a numeric primary
key, I'd recommend int4/int8 attached to a sequence - it's much clearer
what's going on then.
> Are there any disadvantages as to reliability or performance apart from
> accidentally
> forgetting to use the -o option with pg_dump? If so, please give details.
>
> I felt especially worried by a postgres developer's statement in another
> archived mail:
> "As far as I know, there is no reason oid's have to be unique, especially
> if they are in different tables."
> (http://archives.postgresql.org/pgsql-hackers/1998-12/msg00570.php)
>
> How unique are oids as of version 7.3 of postgres ?
OIDs are unique per object (table) I believe, no more so. See chapter 5.10 of
the user guide for details. They are used to identify system objects and so
the fact that a function and a table could both have the same OID should
cause no problems.
> Is it planned to keep oids semantically the same in future releases of
> postgres?
Couldn't say - don't see why not.
> Will the oid type be extended so that oids can be larger than 4 bytes (if
> this is still
> correct for 7.3) and do not rotate in large systems?
Strikes me as unlikely, though I'm not a developer. Look into 8-byte
serial/sequences.
--
Richard Huxton
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
@ 2003-02-26 14:59 ` daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-26 16:10 ` Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 17:17 ` Re: OIDs as keys Josh Berkus <josh@agliodbs.com>
1 sibling, 3 replies; 26+ messages in thread
From: daniel alvarez @ 2003-02-26 14:59 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
> > I am considering the option of placing an index on the already existing
oid
> > and using it as the primary key for all tables (saves some space and a
sequence
> > lookup). This includes saving the oid in foreign keys (virtual ones, not
actually
> > declared references). I read that using OID in keys is generally a bad
idea.
> > Is it really? Why exactly?
> OIDs are not even guaranteed to be there any more - you can create a table
> WITHOUT OIDs if you want to save some space. If you want a numeric primary
> key, I'd recommend int4/int8 attached to a sequence - it's much clearer
what's
> going on then.
Of course this is a cleaner solution. I did not know that oids can be
supressed and
was looking for a way to make space usage more efficient. Trying to get rid
of user-
defined surrogate primary keys and substitute them by the already existing
OID is
obviously the wrong approch, as postgres already defines a cleaner option.
There can also be some problems when using replication, because one needs to
make
sure that OIDs are the same on all machines in the cluster.
Why should user-defined tables have OIDs by default? Other DBMS use ROWIDs
as the physical storage location used for pointers in index leafs, but this
is equivalent
to Postgres TIDs. To the user an OID column is not different than any other
column
he can define himself. I'd find it more natural if the column wasn't there
at all.
Daniel Alvarez
--
+++ GMX - Mail, Messaging & more http://www.gmx.net +++
Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage!
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
@ 2003-02-26 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2003-02-26 15:56 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
daniel alvarez <d-alvarez@gmx.de> writes:
> Why should user-defined tables have OIDs by default?
At this point it's just for historical reasons. There actually is a
proposal on the table to flip the default to WITHOUT OIDS, but so far
it's not been accepted because of worries about compatibility. See
the pghackers archives a few weeks back.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-27 04:00 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: Christopher Kings-Lynne @ 2003-02-27 04:00 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
> daniel alvarez <d-alvarez@gmx.de> writes:
> > Why should user-defined tables have OIDs by default?
>
> At this point it's just for historical reasons. There actually is a
> proposal on the table to flip the default to WITHOUT OIDS, but so far
> it's not been accepted because of worries about compatibility. See
> the pghackers archives a few weeks back.
Shall I include a patch to pg_dump that will explicitly set WITH OIDS when I
submit this SET STORAGE dumping patch?
Chris
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-02-27 06:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2003-02-27 06:35 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Shall I include a patch to pg_dump that will explicitly set WITH OIDS when I
> submit this SET STORAGE dumping patch?
Not if you want it to be accepted ;-)
We pretty much agreed we did not want that in the prior thread.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-27 07:00 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: Christopher Kings-Lynne @ 2003-02-27 07:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> > Shall I include a patch to pg_dump that will explicitly set WITH OIDS
when I
> > submit this SET STORAGE dumping patch?
>
> Not if you want it to be accepted ;-)
>
> We pretty much agreed we did not want that in the prior thread.
The patch I submitted did not include OID stuff, I decided that it's better
to submit orthogonal patches :)
Chris
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-02-27 07:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-28 01:51 ` Re: OIDs as keys Ron Mayer <ron@intervideo.com>
0 siblings, 2 replies; 26+ messages in thread
From: Tom Lane @ 2003-02-27 07:06 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> The patch I submitted did not include OID stuff, I decided that it's better
> to submit orthogonal patches :)
Right. But the problem with switching the OID default is not a matter
of code --- it's of working out what the compatibility issues are.
As I recall, one thing people did not want was for pg_dump to plaster
WITH OIDS or WITHOUT OIDS on every single CREATE TABLE, as this would
pretty much destroy any shot at loading PG dumps into any other
database. What we need is an agreement on the behavior we want (making
the best possible compromise between this and other compatibility
desires). After that, the actual patch is probably trivial, while in
advance of some consensus on the behavior, offering a patch is a waste
of time.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-27 07:19 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Christopher Kings-Lynne @ 2003-02-27 07:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
> Right. But the problem with switching the OID default is not a matter
> of code --- it's of working out what the compatibility issues are.
> As I recall, one thing people did not want was for pg_dump to plaster
> WITH OIDS or WITHOUT OIDS on every single CREATE TABLE, as this would
> pretty much destroy any shot at loading PG dumps into any other
> database.
Ummm...what about SERIAL columns, ALTER TABLE / SET STATS, SET STORAGE,
custom types, 'btree' in CREATE INDEX, SET SEARCH_PATH, '::" cast operator,
stored procedures, rules, etc. - how is adding WITH OIDS going to change
that?!
> What we need is an agreement on the behavior we want (making
> the best possible compromise between this and other compatibility
> desires). After that, the actual patch is probably trivial, while in
> advance of some consensus on the behavior, offering a patch is a waste
> of time.
Sure.
Chris
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2003-02-27 07:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-06 21:11 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 26+ messages in thread
From: Tom Lane @ 2003-02-27 07:30 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> As I recall, one thing people did not want was for pg_dump to plaster
>> WITH OIDS or WITHOUT OIDS on every single CREATE TABLE, as this would
>> pretty much destroy any shot at loading PG dumps into any other
>> database.
> Ummm...what about SERIAL columns, ALTER TABLE / SET STATS, SET STORAGE,
> custom types, 'btree' in CREATE INDEX, SET SEARCH_PATH, '::" cast operator,
> stored procedures, rules, etc. - how is adding WITH OIDS going to change
> that?!
It's moving in the wrong direction. We've been slowly eliminating
unnecessary nonstandardisms in pg_dump output; this puts in a new one
in a quite fundamental place. You could perhaps expect another DB
to drop commands it didn't understand like SET SEARCH_PATH ... but if
it drops all your CREATE TABLEs, you ain't got much dump left to load.
I'm not necessarily wedded to the above argument myself, mind you;
but it is a valid point that needs to be weighed in the balance of
what we're trying to accomplish.
The bottom line is that "code first, design later" is no way to
approach this problem.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-03-05 13:54 ` Neil Conway <neilc@samurai.com>
2003-03-05 14:05 ` Re: OIDs as keys Rod Taylor <rbt@rbt.ca>
2003-03-05 15:20 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 26+ messages in thread
From: Neil Conway @ 2003-03-05 13:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
On Thu, 2003-02-27 at 02:30, Tom Lane wrote:
> It's moving in the wrong direction. We've been slowly eliminating
> unnecessary nonstandardisms in pg_dump output; this puts in a new one
> in a quite fundamental place. You could perhaps expect another DB
> to drop commands it didn't understand like SET SEARCH_PATH ... but if
> it drops all your CREATE TABLEs, you ain't got much dump left to load.
Rather than specifying the use of OIDs by WITH OIDS clauses for each
CREATE TABLE in a dump, couldn't we do it by adding a SET command that
toggles the 'use_oids' GUC option prior to every CREATE TABLE? That way,
a user concerned with portability could fairly easily strip out (or just
ignore) the SET commands.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
@ 2003-03-05 14:05 ` Rod Taylor <rbt@rbt.ca>
1 sibling, 0 replies; 26+ messages in thread
From: Rod Taylor @ 2003-03-05 14:05 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
On Wed, 2003-03-05 at 08:54, Neil Conway wrote:
> On Thu, 2003-02-27 at 02:30, Tom Lane wrote:
> > It's moving in the wrong direction. We've been slowly eliminating
> > unnecessary nonstandardisms in pg_dump output; this puts in a new one
> > in a quite fundamental place. You could perhaps expect another DB
> > to drop commands it didn't understand like SET SEARCH_PATH ... but if
> > it drops all your CREATE TABLEs, you ain't got much dump left to load.
>
> Rather than specifying the use of OIDs by WITH OIDS clauses for each
> CREATE TABLE in a dump, couldn't we do it by adding a SET command that
> toggles the 'use_oids' GUC option prior to every CREATE TABLE? That way,
> a user concerned with portability could fairly easily strip out (or just
> ignore) the SET commands.
Toggling the SET command prior to each table creation? Thats an
excellent idea. It should also allow us to easily transition to the
default being off after a release or two.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
@ 2003-03-05 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:13 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Tom Lane @ 2003-03-05 15:20 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
Neil Conway <neilc@samurai.com> writes:
> Rather than specifying the use of OIDs by WITH OIDS clauses for each
> CREATE TABLE in a dump, couldn't we do it by adding a SET command that
> toggles the 'use_oids' GUC option prior to every CREATE TABLE?
Seems better than cluttering the CREATE TABLE itself with them, I guess.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-05 15:20 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-03-06 21:13 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:22 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
0 siblings, 1 reply; 26+ messages in thread
From: Bruce Momjian @ 2003-03-06 21:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Neil Conway <neilc@samurai.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Rather than specifying the use of OIDs by WITH OIDS clauses for each
> > CREATE TABLE in a dump, couldn't we do it by adding a SET command that
> > toggles the 'use_oids' GUC option prior to every CREATE TABLE?
>
> Seems better than cluttering the CREATE TABLE itself with them, I guess.
It would be good to somehow SET the use_oids GUC value on restore start,
and just use SET when the table is different than the default, but then
there is no mechanism to do that when you restore a single table.
--
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-05 15:20 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:13 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-03-06 21:22 ` Neil Conway <neilc@samurai.com>
2003-03-07 01:50 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: Neil Conway @ 2003-03-06 21:22 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
On Thu, 2003-03-06 at 16:13, Bruce Momjian wrote:
> It would be good to somehow SET the use_oids GUC value on restore start,
> and just use SET when the table is different than the default, but then
> there is no mechanism to do that when you restore a single table.
What if the default value changes?
IMHO, running a SET per CREATE TABLE isn't too ugly...
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-05 15:20 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:13 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:22 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
@ 2003-03-07 01:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-08 20:01 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
0 siblings, 1 reply; 26+ messages in thread
From: Bruce Momjian @ 2003-03-07 01:50 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
Neil Conway wrote:
> On Thu, 2003-03-06 at 16:13, Bruce Momjian wrote:
> > It would be good to somehow SET the use_oids GUC value on restore start,
> > and just use SET when the table is different than the default, but then
> > there is no mechanism to do that when you restore a single table.
>
> What if the default value changes?
>
> IMHO, running a SET per CREATE TABLE isn't too ugly...
Not ugly, but a little noisy. However, my idea of having a single SET
at the top is never going to work, so I don't have a better idea.
The killer for me is that you are never going to know the GUC default
when you are loading, so we are _always_ going to have that SET for each
table.
I suppose we could set the default to off, and set it ON in the dump
only when we want OID. If they set GUC to on, they will get oid's from
the load, but it will cut down on the cruft and over time, they will
only have the SET for cases where they really want an oid.
--
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-05 15:20 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:13 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:22 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-07 01:50 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-03-08 20:01 ` daniel alvarez <d-alvarez@gmx.de>
2003-03-08 20:09 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: daniel alvarez @ 2003-03-08 20:01 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: neilc@samurai.com; tgl@sss.pgh.pa.us; chriskl@familyhealth.com.au; dev@archonet.com; pgsql-performance
> Neil Conway wrote:
> > On Thu, 2003-03-06 at 16:13, Bruce Momjian wrote:
> > > It would be good to somehow SET the use_oids GUC value on restore
> start,
> > > and just use SET when the table is different than the default, but
> then
> > > there is no mechanism to do that when you restore a single table.
> >
> > What if the default value changes?
> >
> > IMHO, running a SET per CREATE TABLE isn't too ugly...
>
> Not ugly, but a little noisy. However, my idea of having a single SET
> at the top is never going to work, so I don't have a better idea.
Why isn't this done on a per-session basis? Having a session setting for the
common case and a CREATE-TABLE clause for the specifics sounds natural.
When a single table needs to be restored all one needs to to is changing the
session setting before running the CREATE command. The alternative clause
in CREATE-TABLE statements would be used as a cleaner way of expressing
the same thing without affecting the session, when the statement's text can
be entered manually (as opposed to loading it from an existing dumpfile).
The default for the session setting could be set in the configuration file
then.
regards, Daniel Alvarez Arribas <d-alvarez@gmx.de>
--
+++ GMX - Mail, Messaging & more http://www.gmx.net +++
Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage!
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-05 15:20 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:13 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:22 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-07 01:50 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-08 20:01 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
@ 2003-03-08 20:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-08 20:16 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
0 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2003-03-08 20:09 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; neilc@samurai.com; chriskl@familyhealth.com.au; dev@archonet.com; pgsql-performance
daniel alvarez <d-alvarez@gmx.de> writes:
>> Not ugly, but a little noisy. However, my idea of having a single SET
>> at the top is never going to work, so I don't have a better idea.
> Why isn't this done on a per-session basis?
Because pg_dump can't know what the session default will be when the
dump is reloaded. The scheme you are proposing will only succeed in
making pg_dump unreliable.
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-05 15:20 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:13 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:22 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-07 01:50 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-08 20:01 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-03-08 20:09 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-03-08 20:16 ` daniel alvarez <d-alvarez@gmx.de>
2003-03-10 16:05 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: daniel alvarez @ 2003-03-08 20:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgman@candle.pha.pa.us; neilc@samurai.com; chriskl@familyhealth.com.au; dev@archonet.com; pgsql-performance
> daniel alvarez <d-alvarez@gmx.de> writes:
> >> Not ugly, but a little noisy. However, my idea of having a single SET
> >> at the top is never going to work, so I don't have a better idea.
>
> > Why isn't this done on a per-session basis?
>
> Because pg_dump can't know what the session default will be when the
> dump is reloaded. The scheme you are proposing will only succeed in
> making pg_dump unreliable.
Ouch. Why is this? Doesn't it read the config because of portability
reasons?
--
+++ GMX - Mail, Messaging & more http://www.gmx.net +++
Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage!
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-05 15:20 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:13 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:22 ` Re: OIDs as keys Neil Conway <neilc@samurai.com>
2003-03-07 01:50 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-08 20:01 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-03-08 20:09 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-08 20:16 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
@ 2003-03-10 16:05 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 26+ messages in thread
From: Bruce Momjian @ 2003-03-10 16:05 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; neilc@samurai.com; chriskl@familyhealth.com.au; dev@archonet.com; pgsql-performance
daniel alvarez wrote:
> > daniel alvarez <d-alvarez@gmx.de> writes:
> > >> Not ugly, but a little noisy. However, my idea of having a single SET
> > >> at the top is never going to work, so I don't have a better idea.
> >
> > > Why isn't this done on a per-session basis?
> >
> > Because pg_dump can't know what the session default will be when the
> > dump is reloaded. The scheme you are proposing will only succeed in
> > making pg_dump unreliable.
>
> Ouch. Why is this? Doesn't it read the config because of portability
> reasons?
Remember the dump output is just an SQL script, so there is no 'logic'
in the script, and it can be loaded right into psql.
--
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-03-06 21:11 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:14 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 26+ messages in thread
From: Bruce Momjian @ 2003-03-06 21:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
Tom Lane wrote:
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> >> As I recall, one thing people did not want was for pg_dump to plaster
> >> WITH OIDS or WITHOUT OIDS on every single CREATE TABLE, as this would
> >> pretty much destroy any shot at loading PG dumps into any other
> >> database.
>
> > Ummm...what about SERIAL columns, ALTER TABLE / SET STATS, SET STORAGE,
> > custom types, 'btree' in CREATE INDEX, SET SEARCH_PATH, '::" cast operator,
> > stored procedures, rules, etc. - how is adding WITH OIDS going to change
> > that?!
>
> It's moving in the wrong direction. We've been slowly eliminating
> unnecessary nonstandardisms in pg_dump output; this puts in a new one
> in a quite fundamental place. You could perhaps expect another DB
> to drop commands it didn't understand like SET SEARCH_PATH ... but if
> it drops all your CREATE TABLEs, you ain't got much dump left to load.
Why was the schema path called search_path rather than schema_path?
Standards?
--
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:11 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-03-06 21:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:16 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 26+ messages in thread
From: Tom Lane @ 2003-03-06 21:14 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Why was the schema path called search_path rather than schema_path?
Nobody suggested anything different ... it's a bit late now ...
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:11 ` Re: OIDs as keys Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:14 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-03-06 21:16 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 26+ messages in thread
From: Bruce Momjian @ 2003-03-06 21:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; pgsql-performance
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Why was the schema path called search_path rather than schema_path?
>
> Nobody suggested anything different ... it's a bit late now ...
I started to think about it when we were talking about a config_path
variable. Search path then looked confusing. :-(
--
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Re: OIDs as keys Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Re: OIDs as keys Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-28 01:51 ` Ron Mayer <ron@intervideo.com>
1 sibling, 0 replies; 26+ messages in thread
From: Ron Mayer @ 2003-02-28 01:51 UTC (permalink / raw)
To: pgsql-performance; +Cc: daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>
Tom wrote:
>
>As I recall, one thing people did not want was for pg_dump to plaster
>WITH OIDS or WITHOUT OIDS on every single CREATE TABLE, as this would
>pretty much destroy any shot at loading PG dumps into any other
>database.
Has there been any talk about adding a flag to pg_dump to explicitly
ask for a standard format? (sql3? sql99? etc?) Ideally for me, such
a flag would produce a "portable" dump file of the subset that did
follow the standard, and also produce a separate log file that could
contain any constructs that could not be standardly dumped.
If such a flag existed, it might be the easiest way to load to other
databases, and people might be less opposed to plastering more postgres
specific stuff to the default format.
If I were going to try to write portable dumps for other databases, I'd
want as few postgresisms in the big file as possible. The separate log file
would make it easier for me to make hand-ported separate files to set up
functions, views, etc.
Yes, I know that would restrict the functionality I could
depend on, including types, sequences, etc. However if I were in an
environment where developers did prototyping on postgres / mssql /etc
and migrated functionality to whatever the company's official
standard system was, I think developers would want to constrain
themselves to standards as much as possible, and this option may help
them do so.
Ron
PS: I'm not sure if I'm volunteering or not, unless someone tells
me how easy/hard it would be. Last thing I tried was harder
than it first appeared.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
@ 2003-02-26 16:10 ` Richard Huxton <dev@archonet.com>
2 siblings, 0 replies; 26+ messages in thread
From: Richard Huxton @ 2003-02-26 16:10 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; +Cc: pgsql-performance
On Wednesday 26 Feb 2003 2:59 pm, daniel alvarez wrote:
>
> Why should user-defined tables have OIDs by default? Other DBMS use ROWIDs
> as the physical storage location used for pointers in index leafs, but this
> is equivalent
> to Postgres TIDs. To the user an OID column is not different than any other
> column
> he can define himself. I'd find it more natural if the column wasn't there
> at all.
I believe the plan is to phase them out, but some people are using them, so
the default is still to create them. Imagine if you were using OIDs as keys
and after a dump/restore they were all gone...
--
Richard Huxton
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` Re: OIDs as keys daniel alvarez <d-alvarez@gmx.de>
@ 2003-02-26 17:17 ` Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 26+ messages in thread
From: Josh Berkus @ 2003-02-26 17:17 UTC (permalink / raw)
To: daniel alvarez <d-alvarez@gmx.de>; Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
Daniel,
> There can also be some problems when using replication, because one needs
> to make
> sure that OIDs are the same on all machines in the cluster.
See the "uniqueidentifier" contrib package if you need a universally unique id
for replication.
> I'd find it more natural if the column wasn't there
> at all.
Probably by Postgres 8.0, it won't be.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: OIDs as keys
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
@ 2003-02-26 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 26+ messages in thread
From: Tom Lane @ 2003-02-26 16:02 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: daniel alvarez <d-alvarez@gmx.de>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
>> How unique are oids as of version 7.3 of postgres ?
> OIDs are unique per object (table) I believe, no more so.
Even then, you should only assume uniqueness if you put a unique index
on the table's OID column to enforce it. (The system catalogs that use
OID all have such indexes.) Without that, you might have duplicates
after the OID counter wraps around.
>> Will the oid type be extended so that oids can be larger than 4 bytes (if
>> this is still correct for 7.3) and do not rotate in large systems?
> Strikes me as unlikely, though I'm not a developer.
I tend to agree. At one point that was a live possibility, but now
we're more likely to change the default for user tables to WITHOUT OIDS
and declare the problem solved. Making OIDs 8 bytes looks like too much
of a performance hit for non-64-bit machines. (Not to mention machines
that haven't got "long long" at all; right now the only thing that
doesn't work for them is type int8, and I'd like it to stay that way,
at least for a few more years.)
regards, tom lane
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2003-03-10 16:05 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-26 13:58 Re: OIDs as keys Richard Huxton <dev@archonet.com>
2003-02-26 14:59 ` daniel alvarez <d-alvarez@gmx.de>
2003-02-26 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 04:00 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 06:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:00 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 07:19 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-02-27 07:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-05 13:54 ` Neil Conway <neilc@samurai.com>
2003-03-05 14:05 ` Rod Taylor <rbt@rbt.ca>
2003-03-05 15:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:13 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:22 ` Neil Conway <neilc@samurai.com>
2003-03-07 01:50 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-08 20:01 ` daniel alvarez <d-alvarez@gmx.de>
2003-03-08 20:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-08 20:16 ` daniel alvarez <d-alvarez@gmx.de>
2003-03-10 16:05 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:11 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-03-06 21:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-06 21:16 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-02-28 01:51 ` Ron Mayer <ron@intervideo.com>
2003-02-26 16:10 ` Richard Huxton <dev@archonet.com>
2003-02-26 17:17 ` Josh Berkus <josh@agliodbs.com>
2003-02-26 16:02 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox