Thread: OID Usage
Hi ... I am using postgresql 7.4 on a pontencial large DB system, and I am quite happy of the performance of this database, as for now. Only one thing worrys me, and I like to get some pease to my mind about this. I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK constaints, but I use OID in one special situation. When I insert a single row into a table, I like my low level code to be kompatible with mysql ( mysql_insert_id ), and fetch the row that I just inserted. This I do by using the PGoidValue function, and then select the row by the oid. This works quite nice .... but when a table get large, it become a big search (seq scan) so I have added an index on oid's on the table where I use this trick, and this have helper :-) This is the ONLY use I have for the oid's, and I don't use them for anything else ! Now, are there any danger in using this method ? And if there is, how can I do this trick without knowing the layout of the table I insert into ? Regards /BL
On Jan 14, 2005, at 16:03, Bo Lorentsen wrote: > Now, are there any danger in using this method ? And if there is, how > can I do this trick without knowing the layout of the table I insert > into ? You can use currval() to get the sequence value that was pulled from your insert. You can check the documentation for usage, as well as searching the archives for discussions of using OIDs as part of your database logic. Hope this helps. Michael Glaesemann grzm myrealbox com
Michael Glaesemann wrote: > You can use currval() to get the sequence value that was pulled from > your insert. You can check the documentation for usage, as well as > searching the archives for discussions of using OIDs as part of your > database logic. I know this, but i like not to know anything about the metadata of the table i use. Basicly using the same functionality, as given in mysql in the mysql_insert_id, as I use the same low level code for both DB's (until my boss give in totally to PG :-)). /BL
Hi,
On Fri, 14 Jan 2005, Bo Lorentsen wrote:
> Michael Glaesemann wrote:
>
>> You can use currval() to get the sequence value that was pulled from your
>> insert. You can check the documentation for usage, as well as searching
>> the archives for discussions of using OIDs as part of your database logic.
>
> I know this, but i like not to know anything about the metadata of the table
> i use. Basicly using the same functionality, as given in mysql in the
> mysql_insert_id, as I use the same low level code for both DB's (until my
> boss give in totally to PG :-)).
why should your application not want to know about the metadata of it's
own tables ? That sounds quite strange when you think about it.
If you name your sequences in a generic way you can alway construct the
name of the sequence from the name of the table and the id column.
We use this in our php framework
function insert_id()
{
global $pg_conn;
if(isset($pg_conn)) {
$query = sprintf("SELECT currval('%s_%s_seq') AS id",$this->table,$this->id_column);
$result = @pg_query($pg_conn,$query);
$row = pg_fetch_assoc($result);
return strval($row["id"]);
} else {
return 0;
}
}
Greetings
Christian
--
Christian Kratzer ck@cksoft.de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
On Fri, Jan 14, 2005 at 10:32:18AM +0100, Christian Kratzer wrote:
> $query = sprintf("SELECT currval('%s_%s_seq') AS
> id",$this->table,$this->id_column);
PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
returns the sequence name for a particular column so you don't have
to construct it. This is useful when a table or column has been
renamed, in which case the above will probably break.
CREATE TABLE foo (fooid serial);
ALTER TABLE foo RENAME TO bar;
ALTER TABLE bar RENAME fooid TO barid;
\d bar
Table "public.bar"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
barid | integer | not null default nextval('public.foo_fooid_seq'::text)
SELECT pg_get_serial_sequence('bar', 'barid');
pg_get_serial_sequence
------------------------
public.foo_fooid_seq
(1 row)
INSERT INTO bar VALUES (DEFAULT);
SELECT currval(pg_get_serial_sequence('bar', 'barid'));
currval
---------
1
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Christian Kratzer wrote: > why should your application not want to know about the metadata of it's > own tables ? That sounds quite strange when you think about it. Well, the ideer is to be compatible with mysql at the same level in the code. This works nicely, as I have descriped, but I am concerned if there is any strains attached to this method. It is all found in the : http://lue.dk/prj/dbc/index.html > If you name your sequences in a generic way you can alway construct > the name of the sequence from the name of the table and the id column. > > We use this in our php framework > > function insert_id() > { > global $pg_conn; > if(isset($pg_conn)) { > $query = sprintf("SELECT currval('%s_%s_seq') > AS id",$this->table,$this->id_column); > $result = @pg_query($pg_conn,$query); > $row = pg_fetch_assoc($result); > return strval($row["id"]); > } else { > return 0; > } > } Thanks, but this demands you to have the table and id_column name in your hand, and I don't right now. Also ... the "currval" function are specifik to postgresql, and there are nothing like it in mysql that can make any garanti for getting row for newly inserted data. You can access autoincrement values in mysql, but no garanties are given about its value (someone else have inserted a new in the same table). But thanks for your interrest., anyway. /BL
Michael Fuhr wrote: >PostgreSQL 8.0 will have a pg_get_serial_sequence() function that >returns the sequence name for a particular column so you don't have >to construct it. This is useful when a table or column has been >renamed, in which case the above will probably break. > > Quite nice but not what I need, as I still need to know the id column name. But thanks anyway. /BL
On Fri, Jan 14, 2005 at 11:39:54AM +0100, Bo Lorentsen wrote: > Thanks, but this demands you to have the table and id_column name in > your hand, and I don't right now. You can create a function to get the sequence name attached to a table. Of course, you should take into account the fact that there could be more than one (two serial fields in a table are rare but not impossible), but if your tables have only one sequence you should be OK. Something with select relname, relkind from pg_depend join pg_class on (oid = objid) where pg_depend.refobjid = 'foo'::regclass and relkind = 'S'; (only lightly tested). Then you can use that to construct your argument to the nextval() function. > Also ... the "currval" function are specifik to postgresql, and there > are nothing like it in mysql that can make any garanti for getting row > for newly inserted data. You can access autoincrement values in mysql, > but no garanties are given about its value (someone else have inserted a > new in the same table). This doesn't happen with sequences on Postgres. The value you get is guaranteed to be the one the sequence generated for you. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Those who use electric razors are infidels destined to burn in hell while we drink from rivers of beer, download free vids and mingle with naked well shaved babes." (http://slashdot.org/comments.pl?sid=44793&cid=4647152)
Bo Lorentsen <bl@netgroup.dk> writes:
> I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK
> constaints, but I use OID in one special situation. When I insert a
> single row into a table, I like my low level code to be kompatible with
> mysql ( mysql_insert_id ), and fetch the row that I just inserted. This
> I do by using the PGoidValue function, and then select the row by the
> oid. This works quite nice .... but when a table get large, it become a
> big search (seq scan) so I have added an index on oid's on the table
> where I use this trick, and this have helper :-)
The thing you have to worry about is the possibility of duplicate OIDs
once your DB has been running long enough for the OID counter to wrap
around (2^32 OIDs). You should make sure that index is specifically
declared as UNIQUE, so that any attempt to insert a duplicate OID will
fail. That might be enough for you, or you might want to add logic to
your application to retry automatically after such a failure.
regards, tom lane
On Fri, Jan 14, 2005 at 11:47:25AM +0100, Bo Lorentsen wrote: > Michael Fuhr wrote: > > >PostgreSQL 8.0 will have a pg_get_serial_sequence() function that > >returns the sequence name for a particular column so you don't have > >to construct it. This is useful when a table or column has been > >renamed, in which case the above will probably break. > > > Quite nice but not what I need, as I still need to know the id column name. You could query the system catalogs for the table's primary key, either on the client side or in a server-side function. The pg_attrdef table even has the default value's nextval() expression with the sequence name, which could be converted into a currval() call. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr wrote: >You could query the system catalogs for the table's primary key, >either on the client side or in a server-side function. The >pg_attrdef table even has the default value's nextval() expression >with the sequence name, which could be converted into a currval() >call. > > This is not a bad ideer, I will take a look in the "pg_attrdef" table to see what I can find, and some good caching may help alot too :-) But, does this mean that the oid sollution I have decriped (and implimentet) have some unknown problems, or will oid's become obsolete in the near future ? /BL
Alvaro Herrera wrote: >You can create a function to get the sequence name attached to a table. >Of course, you should take into account the fact that there could be >more than one (two serial fields in a table are rare but not >impossible), but if your tables have only one sequence you should be OK. > > Are there a way to find and test if it is a primary key ? >Something with > >select relname, relkind >from pg_depend join pg_class on (oid = objid) >where pg_depend.refobjid = 'foo'::regclass > and relkind = 'S'; > > Hmm, need to play more around using the "pg_" system tables. Are they all well documentet, or need I some guessing ? >(only lightly tested). Then you can use that to construct your argument >to the nextval() function. > > :-) >This doesn't happen with sequences on Postgres. The value you get is >guaranteed to be the one the sequence generated for you. > > I know, and this is one of the reasons for not using MySQL :-) /BL
Tom Lane wrote: >The thing you have to worry about is the possibility of duplicate OIDs >once your DB has been running long enough for the OID counter to wrap >around (2^32 OIDs). You should make sure that index is specifically >declared as UNIQUE, so that any attempt to insert a duplicate OID will >fail. That might be enough for you, or you might want to add logic to >your application to retry automatically after such a failure. > > Ahh, yes ... this was what I thought may have be the problem, Not that 2^32 is a small number, but as time goes by on a busy system, this will happened one day. Unique index is a good plan, it will make an error but no data will be harmed then ! How does PG itself handle a search on an duplicated oid, without a index ... return two rows ? Will there be a future substitute for PGoidValue that is more reliable, like a rowid ? Thanks anyway ! /BL
On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote:
>
> But, does this mean that the oid sollution I have decriped (and
> implimentet) have some unknown problems, or will oid's become obsolete
> in the near future ?
The PostgreSQL documentation discourages the use of OIDs for primary
keys. For example, the "Object Identifier Types" section in the
"Data Types" chapter says:
The oid type is currently implemented as an unsigned four-byte
integer. Therefore, it is not large enough to provide database-wide
uniqueness in large databases, or even in large individual tables.
So, using a user-created table's OID column as a primary key is
discouraged. OIDs are best used only for references to system
tables.
The "System Columns" section of the "Data Definition" chapter says:
OIDs are 32-bit quantities and are assigned from a single
cluster-wide counter. In a large or long-lived database, it is
possible for the counter to wrap around. Hence, it is bad practice
to assume that OIDs are unique, unless you take steps to ensure that
they are unique.
The CREATE TABLE documentation in the Reference part says:
Once the counter wraps around, uniqueness of OIDs can no longer be
assumed, which considerably reduces their usefulness. Specifying
WITHOUT OIDS also reduces the space required to store the table on
disk by 4 bytes per row of the table, thereby improving performance.
The 8.0 Release Notes say the following under "Deprecated Features":
By default, tables in PostgreSQL 8.0 and earlier are created with
OIDs. In the next release, this will _not_ be the case: to create a
table that contains OIDs, the WITH OIDS clause must be specified or
the default_with_oids configuration parameter must be enabled.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote: > But, does this mean that the oid sollution I have decriped (and > implimentet) have some unknown problems, or will oid's become obsolete > in the near future ? It means using OIDs as you described has very well known problems and they will break on you eventually. You can mitigate the damage by creating a UNIQUE index on the oid column but you'd better be sure your application can handle the side-effects. OIDs won't become obsolete, but they'll probably no longer be enabled by default at some stage. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote: > Alvaro Herrera wrote: > > >You can create a function to get the sequence name attached to a table. > >Of course, you should take into account the fact that there could be > >more than one (two serial fields in a table are rare but not > >impossible), but if your tables have only one sequence you should be OK. > > > Are there a way to find and test if it is a primary key ? pg_index has an indisprimary column. > Hmm, need to play more around using the "pg_" system tables. > > Are they all well documentet, or need I some guessing ? See the "System Catalogs" chapter in the documentation. If you run "psql -E" you'll see the queries that psql executes for commands like "\d foo". Those commands query the system catalogs. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Fri, Jan 14, 2005 at 12:20:50PM -0700, Michael Fuhr wrote: > On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote: > > Alvaro Herrera wrote: > > > > >You can create a function to get the sequence name attached to a table. > > >Of course, you should take into account the fact that there could be > > >more than one (two serial fields in a table are rare but not > > >impossible), but if your tables have only one sequence you should be OK. > > > > > Are there a way to find and test if it is a primary key ? > > pg_index has an indisprimary column. Yeah, though things get hairy that way because you have to peek at pg_attribute to match the objsubid in pg_depend; and self-join pg_class to get to the index itself. Not sure if it all can be done in a single query. > If you run "psql -E" you'll see the queries that psql executes for > commands like "\d foo". Those commands query the system catalogs. Sadly, there's hardly anything there that uses pg_depend. -- "I dream about dreams about dreams", sang the nightingale under the pale moon (Sandman)
Michael Fuhr wrote: >The PostgreSQL documentation discourages the use of OIDs for primary >keys. For example, the "Object Identifier Types" section in the >"Data Types" chapter says: > > ... Thanks for taking you the time to snip this together, I think I will try to find a way to find the propper primary key (using pg_* tables), and if this uses the "nextval", I may be able to retrive the currently inserted row by using currval. >The 8.0 Release Notes say the following under "Deprecated Features": > > Why have this not happend before ? The "PGoidValue need to be depricated too. And why is it not substitutet with something else ? /BL
Martijn van Oosterhout wrote: >It means using OIDs as you described has very well known problems and >they will break on you eventually. You can mitigate the damage by >creating a UNIQUE index on the oid column but you'd better be sure your >application can handle the side-effects. > > Ok, Tom told me about the same :-( But why are oid's still in PG, that are they good for ? Will there be a real unique row id, like there is in Oracle, or will this be keept as an internal value only ? >OIDs won't become obsolete, but they'll probably no longer be enabled >by default at some stage. > > Is this because some old application's using oid's in somewhat small dataset ? >Hope this helps, > > It did thanks. /BL
Michael Fuhr wrote: >See the "System Catalogs" chapter in the documentation. > > Ok, I think I will compile all the given information in this thread, to make a new and more non oid'ish solution, as the dataset I manage are going to grow quite a lot :-) >If you run "psql -E" you'll see the queries that psql executes for >commands like "\d foo". Those commands query the system catalogs. > > > This may be very usefull, thanks. /BL
Alvaro Herrera wrote: >Yeah, though things get hairy that way because you have to peek at >pg_attribute to match the objsubid in pg_depend; and self-join pg_class >to get to the index itself. Not sure if it all can be done in a single >query. > > Sounds like my task, to make an oid free insert/select, is going to be very interesting :-) /BL
On Sat, Jan 15, 2005 at 12:06:41AM +0100, Bo Lorentsen wrote: > Martijn van Oosterhout wrote: > >It means using OIDs as you described has very well known problems and > >they will break on you eventually. You can mitigate the damage by > >creating a UNIQUE index on the oid column but you'd better be sure your > >application can handle the side-effects. > > > Ok, Tom told me about the same :-( But why are oid's still in PG, that > are they good for ? Will there be a real unique row id, like there is in > Oracle, or will this be keept as an internal value only ? They're still there because the system tables use them. But for user tables you use sequences which go up to 64 bit and have protection against wraparound. > Is this because some old application's using oid's in somewhat small > dataset ? Basically, OIDs are not useful in user tables, they're not unique, have no special priveledges w.r.t. other columns. All they do is take up extra storage space. Basically, the use of OIDs has been discouraged for a long time now and it's finally getting to the stage where they'll be disabled on user tables by default. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Sat, Jan 15, 2005 at 12:06:41AM +0100, Bo Lorentsen wrote: > Ok, Tom told me about the same :-( But why are oid's still in PG, that > are they good for ? Will there be a real unique row id, like there is in > Oracle, or will this be keept as an internal value only ? Most system catalogs use OIDs as primary keys. So they cannot just disappear. But on user tables, there's not a lot of use for them IMHO. There's no internal row id on Postgres; having one would mean more storage requirements. If you want one, you know where to get it ... if not, you may as well save the space. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "No hay cielo posible sin hundir nuestras raíces en la profundidad de la tierra" (Malucha Pinto)
It's not very hard to do. I just got rid them. It took me about a day. Our
application is an X-Windows front end written is C. I wrote a function to
return the next value of the serial key for any table. Here is the select
statement buitl with sprintf:
"SELECT relname FROM pg_class WHERE relkind = \'S\' AND
relname = \'%s_recid_seq\':
All our sequences are called "recid" and since the naming convention is
<table_name>_recid_seq, it's easy to get the name of the right sequence. You
might as well go ahead and do it. You'll feel better after you do ;o)
On Friday 14 January 2005 06:13 pm, Bo Lorentsen saith:
> Alvaro Herrera wrote:
> >Yeah, though things get hairy that way because you have to peek at
> >pg_attribute to match the objsubid in pg_depend; and self-join pg_class
> >to get to the index itself. Not sure if it all can be done in a single
> >query.
>
> Sounds like my task, to make an oid free insert/select, is going to be
> very interesting :-)
>
> /BL
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
On Fri, Jan 14, 2005 at 05:10:10PM -0300, Alvaro Herrera wrote: > On Fri, Jan 14, 2005 at 12:20:50PM -0700, Michael Fuhr wrote: > > On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote: > > > Alvaro Herrera wrote: > > > > > > >You can create a function to get the sequence name attached to a table. > > > >Of course, you should take into account the fact that there could be > > > >more than one (two serial fields in a table are rare but not > > > >impossible), but if your tables have only one sequence you should be OK. > > > > > > > Are there a way to find and test if it is a primary key ? > > > > pg_index has an indisprimary column. > > Yeah, though things get hairy that way because you have to peek at > pg_attribute to match the objsubid in pg_depend; and self-join pg_class > to get to the index itself. Not sure if it all can be done in a single > query. If you do manage to write a function that will do this I hope you can share it with the community. IMHO PostgreSQL could do with more functions for querying the system catalogs. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Fri, Jan 14, 2005 at 06:39:25PM -0600, Jim C. Nasby wrote:
>
> If you do manage to write a function that will do this I hope you can
> share it with the community. IMHO PostgreSQL could do with more
> functions for querying the system catalogs.
Here's a first attempt at a view that shows tables and their primary
key columns and sequences. I chose a view instead of a function
because a view shows everything in the database with a single query,
which simplifies visual examination of the results. Modify it or
convert it to a function as needed.
The view assumes single-column primary keys defined as SERIAL types.
Properly handling other situations would be a desirable enhancement.
I've done only trivial testing, so if anybody finds a situation
where the view fails (taking the above assumption into account)
then please describe it.
CREATE OR REPLACE VIEW pk_sequence AS
SELECT n.nspname AS tableschema,
c.relname AS tablename,
a.attname AS pkcol,
n2.nspname AS seqschema,
c2.relname AS seqname
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
JOIN pg_index AS i ON i.indrelid = c.oid AND i.indisprimary IS TRUE
JOIN pg_attribute AS a ON a.attrelid = c.oid AND a.attnum = i.indkey[0]
JOIN pg_depend AS d ON d.refobjid = c.oid AND d.refobjsubid = i.indkey[0]
JOIN pg_class AS c2 ON c2.oid = d.objid AND c2.relkind = 'S'
JOIN pg_namespace AS n2 ON n2.oid = c2.relnamespace;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
> I've done only trivial testing, so if anybody finds a situation
> where the view fails (taking the above assumption into account)
> then please describe it.
Nice !
As a sidenote, I have a table with a primary key which is not a sequence,
and this query displays the non-existing sequence name. It would be easy
to check if the sequence exists (yet another join !), only display
sequences that exist ;)...
On Sat, Jan 15, 2005 at 09:02:12AM +0100, PFC wrote: > > As a sidenote, I have a table with a primary key which is not a > sequence, and this query displays the non-existing sequence name. It would > be easy to check if the sequence exists (yet another join !), only display > sequences that exist ;)... Hmmm...that's odd, since the query gets the sequence name through a series of inner joins that go back go pg_class -- if the sequence doesn't exist then where is the name coming from? I did notice that the query should add "AND attisdropped IS FALSE" to the join with pg_attribute, but I don't see how that would affect this case. Can you spot where the mistake is? What does "\d tablename" show for the table in question? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Alvaro Herrera wrote: >Most system catalogs use OIDs as primary keys. So they cannot just >disappear. But on user tables, there's not a lot of use for them IMHO. > > Ok, I think it is about time it is stated more clearly in the documentation. >There's no internal row id on Postgres; having one would mean more >storage requirements. If you want one, you know where to get it ... if >not, you may as well save the space. > > So, how does a index relate to a row ? There have to be some way of addressing a row ? /BL
Michael Fuhr wrote: >Here's a first attempt at a view that shows tables and their primary >key columns and sequences. I chose a view instead of a function >because a view shows everything in the database with a single query, >which simplifies visual examination of the results. Modify it or >convert it to a function as needed. > > This is just what I need to avoid to much headaches regarding PG system tables, and it works on all my tables, as expected. >The view assumes single-column primary keys defined as SERIAL types. > > is this the "c2.relkind = 'S'" in the view ? This seem to be what I need to convert my function to be a oid free version, many thanks. /BL
PFC wrote: > As a sidenote, I have a table with a primary key which is not a > sequence, and this query displays the non-existing sequence name. It > would be easy to check if the sequence exists (yet another join !), > only display sequences that exist ;)... Hmm, I just tried the same, and got a differant result : create table test_table ( id INTEGER PRIMARY KEY, name varchar( 100 )); SELECT seqname, seqschema from pk_sequence WHERE tablename = 'test_table'; seqname | seqschema ---------+----------- (0 rows) So it works as expected here ! /BL
On Sat, Jan 15, 2005 at 05:11:16PM +0100, Bo Lorentsen wrote: > Alvaro Herrera wrote: > >Most system catalogs use OIDs as primary keys. So they cannot just > >disappear. But on user tables, there's not a lot of use for them IMHO. > > > Ok, I think it is about time it is stated more clearly in the documentation. But where in the documentation did you see anything saying that they were unique? I imagine you just inferred that from somewhere. I'm not sure where the documentation should be changed since nowhere actually recommends them in any way. > >There's no internal row id on Postgres; having one would mean more > >storage requirements. If you want one, you know where to get it ... if > >not, you may as well save the space. > > > So, how does a index relate to a row ? There have to be some way of > addressing a row ? Using the CTID, which locates the physical tuple as (block,num). When you update a tuple, or vacuum moves it its CTID will change, so it's not terribly useful from a user's point of view. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Sat, Jan 15, 2005 at 05:53:08PM +0100, Bo Lorentsen wrote: > Michael Fuhr wrote: > >The view assumes single-column primary keys defined as SERIAL types. > > is this the "c2.relkind = 'S'" in the view ? No, that means the pg_class entry is a sequence. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La rebeld�a es la virtud original del hombre" (Arthur Schopenhauer)
On Sat, Jan 15, 2005 at 05:53:08PM +0100, Bo Lorentsen wrote:
> Michael Fuhr wrote:
>
> >The view assumes single-column primary keys defined as SERIAL types.
> >
> is this the "c2.relkind = 'S'" in the view ?
That restricts the view to show only dependent objects that are
sequences ('S'). Defining a column as SERIAL puts a row in pg_depend
that establishes the link between the table and the sequence; if
you explicitly create a sequence and declare the primary key to be
INTEGER with a default value of nextval('sequence_name') then you
don't get the pg_depend link, even though that method is functionally
equivalent to SERIAL. So the view is flawed in that it won't show
a row for the latter case, hence my warning about the view's
assumption.
Other methods are possible. One way might involve joining with
pg_attrdef and extracting the sequence name from the adsrc column.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Martijn van Oosterhout wrote: >But where in the documentation did you see anything saying that they >were unique? I imagine you just inferred that from somewhere. I'm not >sure where the documentation should be changed since nowhere actually >recommends them in any way. > > Hmm, how about as a comment near by the PGoidValue function ? >Using the CTID, which locates the physical tuple as (block,num). When >you update a tuple, or vacuum moves it its CTID will change, so it's >not terribly useful from a user's point of view. > > Hmm, so a data row update also update the CTID in all indexes, too. I see what you mean ! >Hope this helps, > > It did, thanks. /BL
Uh, sorry, my mistake !
I had put SERIAL instead of an INTEGER in the table definition !
You just removed a bug in my schema ;)
> On Sat, Jan 15, 2005 at 09:02:12AM +0100, PFC wrote:
>>
>> As a sidenote, I have a table with a primary key which is not a
>> sequence, and this query displays the non-existing sequence name. It
>> would
>> be easy to check if the sequence exists (yet another join !), only
>> display
>> sequences that exist ;)...
>
> Hmmm...that's odd, since the query gets the sequence name through
> a series of inner joins that go back go pg_class -- if the sequence
> doesn't exist then where is the name coming from? I did notice
> that the query should add "AND attisdropped IS FALSE" to the join
> with pg_attribute, but I don't see how that would affect this case.
>
> Can you spot where the mistake is? What does "\d tablename" show
> for the table in question?
>
On Sat, Jan 15, 2005 at 07:10:48PM +0100, Bo Lorentsen wrote: > >Using the CTID, which locates the physical tuple as (block,num). When > >you update a tuple, or vacuum moves it its CTID will change, so it's > >not terribly useful from a user's point of view. > > > Hmm, so a data row update also update the CTID in all indexes, too. I > see what you mean ! Not quite, a single index entry needs to point to any number of rows, which may or may not be visible depending on your transaction, so they form a sort of linked list. But indeed, not terribly useful for your purpose... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Sat, Jan 15, 2005 at 07:10:48PM +0100, Bo Lorentsen wrote:
>> Hmm, so a data row update also update the CTID in all indexes, too. I=20
>> see what you mean !
> Not quite, a single index entry needs to point to any number of rows,
> which may or may not be visible depending on your transaction, so they
> form a sort of linked list.
No, an index entry contains just one CTID. An update makes a new
version of the row (stored at a new CTID location) and also makes new
index entries pointing at that CTID. In the general case this must be
so, since the new version might well contain different values for the
indexed fields; but we do not try to optimize the case where the indexed
field didn't change.
regards, tom lane
On Sat, Jan 15, 2005 at 04:00:19PM -0500, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > On Sat, Jan 15, 2005 at 07:10:48PM +0100, Bo Lorentsen wrote: > >> Hmm, so a data row update also update the CTID in all indexes, too. I=20 > >> see what you mean ! > > > Not quite, a single index entry needs to point to any number of rows, > > which may or may not be visible depending on your transaction, so they > > form a sort of linked list. > > No, an index entry contains just one CTID. An update makes a new > version of the row (stored at a new CTID location) and also makes new > index entries pointing at that CTID. In the general case this must be > so, since the new version might well contain different values for the > indexed fields; but we do not try to optimize the case where the indexed > field didn't change. Out of curiosity, what clears out the old index tuples? Vacuum? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <decibel@decibel.org> writes:
> Out of curiosity, what clears out the old index tuples? Vacuum?
Right.
regards, tom lane
On Sat, Jan 15, 2005 at 04:21:24PM -0500, Tom Lane wrote: > "Jim C. Nasby" <decibel@decibel.org> writes: > > Out of curiosity, what clears out the old index tuples? Vacuum? > > Right. Which reminds me that you wanted to make VACUUM FULL do the equivalent of a REINDEX instead of retail deletion of index entries ... is that still the idea? Would it do that always, or only under certain conditions? -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Some men are heterosexual, and some are bisexual, and some men don't think about sex at all... they become lawyers" (Woody Allen)
Alvaro Herrera wrote:
> On Sat, Jan 15, 2005 at 04:21:24PM -0500, Tom Lane wrote:
> > "Jim C. Nasby" <decibel@decibel.org> writes:
> > > Out of curiosity, what clears out the old index tuples? Vacuum?
> >
> > Right.
>
> Which reminds me that you wanted to make VACUUM FULL do the equivalent
> of a REINDEX instead of retail deletion of index entries ... is that
> still the idea? Would it do that always, or only under certain
> conditions?
Well, our TODO list still has under "Vacuum":
* Improve speed with indexes
For large table adjustements during vacuum, it is faster to reindex
rather than update the index.
--
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
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Which reminds me that you wanted to make VACUUM FULL do the equivalent
> of a REINDEX instead of retail deletion of index entries ... is that
> still the idea? Would it do that always, or only under certain
> conditions?
It's still on the to-do list. I ws envisioning that it would switch
strategies based on how many tuples it needed to move --- the REINDEX
is only a win if you're moving a large fraction of the table. But the
details are all still TBD.
regards, tom lane
Martijn van Oosterhout wrote: >Not quite, a single index entry needs to point to any number of rows, >which may or may not be visible depending on your transaction, so they >form a sort of linked list. But indeed, not terribly useful for your >purpose... > > This make's sense, I keep forgetting the versioning of data. tnx /BL