Re: [GENERAL] OID
Firstly, please don't send email in only HTML, it tends to get them
marked as spam.
> SPAM: Hit! (3.2 points) HTML-only mail, with no text version
Anyway, as to your question:
> Im planning on using OID for referencing in certain part of the
> system i am building as in this case using normal pk -> fk would
> be inefficient as i have to have once table reference multi tables,
> but i have concerns that if a Database is exported and reconstructed
> the OID's will change making referencing impossible.
There is no use of OIDs that cannot be better served by sequences. I
presume the issue is that in your multiple tables you may have the same
PK appear. Well, there is no reason why a sequence should be tied to a
single table. For example:
CREATE SEQUENCE my_global_counter;
CREATE table_1 ( id int4 default nextval('my_global_counter') primary key, ...
CREATE table_2 ( id int4 default nextval('my_global_counter') primary key, ...
CREATE table_3 ( id int4 default nextval('my_global_counter') primary key, ...
Is this what you're looking for?
--
Martijn van Oosterhout <[EMAIL PROTECTED]> 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.
pgpxmNzO7G1Kk.pgp
Description: PGP signature
Re: [GENERAL] OID
On Friday 09 June 2006 06:34 am, "Bert" <[EMAIL PROTECTED]> thus
communicated:
--> Hi list
--> What is the comment to add OIDs to a already existing table, or i have
--> to recreate the table?
-->
--> Thanks,
--> Clemens
-->
-->
Well, according on \h ALTER TABLE on version 7.4.6 all you can do is SET
WITHOUT OIDS.
master=# \h alter table
Command: ALTER TABLE
Description: change the definition of a table
Syntax:
ALTER TABLE [ ONLY ] name [ * ]
ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
ALTER TABLE [ ONLY ] name [ * ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER TABLE [ ONLY ] name [ * ]
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ALTER TABLE [ ONLY ] name [ * ]
SET WITHOUT OIDS
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name
ALTER TABLE [ ONLY ] name [ * ]
ADD table_constraint
ALTER TABLE [ ONLY ] name [ * ]
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER TABLE name
OWNER TO new_owner
ALTER TABLE name
CLUSTER ON index_name
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] OID
Thanks, so it seems that i can delete the OIDs but not insert them okay
so i have to recreate the table.
Terry Lee Tucker schrieb:
> On Friday 09 June 2006 06:34 am, "Bert" <[EMAIL PROTECTED]> thus
> communicated:
> --> Hi list
> --> What is the comment to add OIDs to a already existing table, or i have
> --> to recreate the table?
> -->
> --> Thanks,
> --> Clemens
> -->
> -->
> Well, according on \h ALTER TABLE on version 7.4.6 all you can do is SET
> WITHOUT OIDS.
>
> master=# \h alter table
> Command: ALTER TABLE
> Description: change the definition of a table
> Syntax:
> ALTER TABLE [ ONLY ] name [ * ]
> ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
> ALTER TABLE [ ONLY ] name [ * ]
> DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
> ALTER TABLE [ ONLY ] name [ * ]
> ALTER [ COLUMN ] column { SET DEFAULT expression | DROP DEFAULT }
> ALTER TABLE [ ONLY ] name [ * ]
> ALTER [ COLUMN ] column { SET | DROP } NOT NULL
> ALTER TABLE [ ONLY ] name [ * ]
> ALTER [ COLUMN ] column SET STATISTICS integer
> ALTER TABLE [ ONLY ] name [ * ]
> ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
> ALTER TABLE [ ONLY ] name [ * ]
> SET WITHOUT OIDS
> ALTER TABLE [ ONLY ] name [ * ]
> RENAME [ COLUMN ] column TO new_column
> ALTER TABLE name
> RENAME TO new_name
> ALTER TABLE [ ONLY ] name [ * ]
> ADD table_constraint
> ALTER TABLE [ ONLY ] name [ * ]
> DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
> ALTER TABLE name
> OWNER TO new_owner
> ALTER TABLE name
> CLUSTER ON index_name
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] OID
On Fri, Jun 09, 2006 at 04:27:41AM -0700, Bert wrote: > Thanks, so it seems that i can delete the OIDs but not insert them okay > so i have to recreate the table. I think a better question would be: why are you using OIDs in the first place? You'll almost certainly be much happier with a serial column instead. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
RE: [GENERAL] OID
>I'm designing a database schema and have questions about OID. > First, I assume that OID are system assigned? [yes] > Second, Can I use OID as a primary key? [no] Perhaps in ignorance, I have used the system assigned OID as a non-topical primary key. Instead of using the "PRIMARY KEY" key words, I explicitly created an index on the OID column. > Third, Is the OID invariant under an import/export process? [no] > Fourth, Can I create table with an OID type for referencing > another table? [no] You can create a row of type OID. I have used them as foreign keys the times I used the OID field as the primary key.
RE: [GENERAL] OID
> I'm designing a database schema and have questions about OID. > > First, I assume that OID are system assigned? [yes] yes - correct assumption > Second, Can I use OID as a primary key? [no] yes(qualified) - incorrect assumption Currently PostgreSQL doesn't really do anything special with primary key columns, so you can achieve the same affect, by declaring a UNIQUE INDEX on the same columns. > Third, Is the OID invariant under an import/export process? [no] yes(qualified) - incorrect assumption see: pg_dump --help (specifically the -o option). > Fourth, Can I create table with an OID type for referencing another > table? [no] yes - incorrect assumption This is part of the reason for the -o switch in pg_dump. > I puy my assumed answers in brackets. Please let me know if I > am mistaken. Well, only 75%. > Thanks! You're welcome. > Clark -DEJ
Re: [GENERAL] oid
Toomas Gavrilin writes: > Is there any way to get rid of postgres automatic generation of > system attribute column 'oid'? No. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] oid
On Thu, 19 Apr 2001, Toomas Gavrilin wrote: > Sorry if my question sounds stupid, but ... > Is there any way to get rid of postgres automatic generation of > system attribute column 'oid'? The problem is, that we've used for years > in our system tables attribute called oid (could be described as > varbit(16)), since there hasn't been any problems with other database > platforms. I don't believe so. There's been some talk about making oid optional on tables in the future (although that might not free up the name). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] OID question
Chris Kratz <[EMAIL PROTECTED]> writes: > We aren't sure at this point if the problem we are having has to do with the > fact that we drop and reload the test db quite often and so we are running > into OID wraparound, or if it has to do with the fact that we recently went > to 8.1 on this test machine and OID is now handled differently on reload. We did change the handling of OIDs in 8.1, specifically this patch: http://archives.postgresql.org/pgsql-committers/2005-08/msg00109.php which ensued from this discussion: http://archives.postgresql.org/pgsql-hackers/2005-08/msg00074.php Pre-8.1 versions contained a kluge that made COPY FROM ... WITH OIDS increase the OID counter to be at least the maximum of the OIDs loaded into the table. The lack of this hack is what's making the collision problem manifest almost immediately for you. However, you would have had a problem eventually anyway, because the OID counter is only 32 bits and will eventually wrap around. So the first thing to ask yourself is whether depending on OID uniqueness is a sane design decision at all. IMHO the answer is no unless (1) the tables you need OIDs for will never exceed 2^32 rows, or even reach any significant fraction of that, and (2) you aren't assuming global uniqueness of OIDs but only per-table uniqueness. (NOTE: you can create a database-wide unique identifier by combining tableoid and row OID, if necessary.) Assuming you meet those sanity checks, the way to do this in 8.1 is to create a unique index on OID for each table you need unique OIDs in. This not only is needed anyway to enforce the design assumption, but the presence of such an index triggers the code we added to 8.1 to select an unused OID. http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ddl.sgml.diff?r1=1.42&r2=1.43 regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] OID question
Thank you Tom for your concise response. Your explanation is very helpfull. We were aware of the wraparound issues, but this has worked so well for us that we haven't explored changing it yet. Having a unique identifier per row is most usefull in our situation and we didn't think we were anywhere near wraparound on our live machine, hence we've let it ride. It sounds like we will need to start thinking about migrating to the tableoid+rowoid at some point. Thanks again, -Chris On Wednesday 01 February 2006 09:53 am, you wrote: > Chris Kratz <[EMAIL PROTECTED]> writes: > > We aren't sure at this point if the problem we are having has to do with > > the fact that we drop and reload the test db quite often and so we are > > running into OID wraparound, or if it has to do with the fact that we > > recently went to 8.1 on this test machine and OID is now handled > > differently on reload. > > We did change the handling of OIDs in 8.1, specifically this patch: > http://archives.postgresql.org/pgsql-committers/2005-08/msg00109.php > which ensued from this discussion: > http://archives.postgresql.org/pgsql-hackers/2005-08/msg00074.php > > Pre-8.1 versions contained a kluge that made COPY FROM ... WITH OIDS > increase the OID counter to be at least the maximum of the OIDs loaded > into the table. The lack of this hack is what's making the collision > problem manifest almost immediately for you. However, you would have > had a problem eventually anyway, because the OID counter is only 32 bits > and will eventually wrap around. > > So the first thing to ask yourself is whether depending on OID > uniqueness is a sane design decision at all. IMHO the answer is > no unless (1) the tables you need OIDs for will never exceed 2^32 > rows, or even reach any significant fraction of that, and (2) you > aren't assuming global uniqueness of OIDs but only per-table uniqueness. > (NOTE: you can create a database-wide unique identifier by combining > tableoid and row OID, if necessary.) > > Assuming you meet those sanity checks, the way to do this in 8.1 is > to create a unique index on OID for each table you need unique OIDs > in. This not only is needed anyway to enforce the design assumption, > but the presence of such an index triggers the code we added to 8.1 > to select an unused OID. > http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ddl.sgml.diff >?r1=1.42&r2=1.43 > > regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] oid wraparound
On Tue, 26 Apr 2005 07:24 pm, Hubert Fröhlich wrote: > Hi list, > > some time ago, there was a discussion about oid wraparound. See > http://archives.postgresql.org/pgsql-general/2002-10/msg00561.php . > > Those days, we had PostgreSQL 7.1 and 7.2, and we had to be careful > oids approaching 2^32 (2.14 billion) > > Now, we have 8.0. What does the situation look like? Where do I have to > be careful: > > OID > 2billion? 4billion? > > What about the danger of TID wraparounds? (databases are VACUUMed regularly) > With 8.0 you only need to make sure you do database wide vacuums every 1 billion transactions or so. If you do that, then there is not problem when the XID (Transaction ID) wraps around. Postgresql will know which transaction were in the past, and which were in the future. Regards Russell Smith. > ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] oid wraparound
Hubert Fröhlich wrote: Those days, we had PostgreSQL 7.1 and 7.2, and we had to be careful oids approaching 2^32 (2.14 billion) Now, we have 8.0. What does the situation look like? With the default settings, there is exactly the same risk of OID wraparound as in earlier releases. However, you can set the "default_with_oids" configuration parameter to false to significantly reduce OID consumption, to the point that you probably won't need to worry about it. It will mean that tables will not have OIDs by default, so you should specify WITH OIDS when creating tables that need OIDs if necessary (although think twice before doing this, as there are only a few good reasons to use OIDs in user tables). (This setting will default to false in 8.1) -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] oid wraparound
Thanks, Neil. Hubert Fröhlich wrote: Those days, we had PostgreSQL 7.1 and 7.2, and we had to be careful oids approaching 2^32 (2.14 billion) Now, we have 8.0. What does the situation look like? With the default settings, there is exactly the same risk of OID wraparound as in earlier releases. However, you can set the "default_with_oids" configuration parameter to false to significantly reduce OID consumption, to the point that you probably won't need to worry about it. It will mean that tables will not have OIDs by default, so you should specify WITH OIDS when creating tables that need OIDs if necessary (although think twice before doing this, as there are only a few good reasons to use OIDs in user tables). What good reasons to use OIDs in user tables are still left? * For speeding up some special types of queries? -- Mit freundlichen Grüßen / With kind regards Hubert Fröhlich --- Dr.-Ing. Hubert Fröhlich Bezirksfinanzdirektion München Alexandrastr. 3, D-80538 München, GERMANY Tel. :+49 (0)89 / 2190 - 2980 Fax :+49 (0)89 / 2190 - 2997 hubert dot froehlich at bvv dot bayern dot de ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] OID Question
On Thu, 11 Nov 2004, Terry Lee Tucker wrote: > Is it ok to put a unique index on the oid for my tables? We are in the process > of moving from Progress Software to PostgreSQL. In the Progress world, you > can always uniquely, and quickly find a record by using their version of oid, > which is recid. I remember reading somewhere that the oid could be > duplicated across the cluster, but would not be duplicated in a single table. > Maybe I dreamed it. What is the recommendation regarding this and why? Well, some system tables that want unique oids already do this. One issue with making a unique index on oid is that after oid counter rollover you'll potentially get intermittent constraint violations due to duplicate oids that you need to be able to handle. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID Question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 11 November 2004 07:04 am, Terry Lee Tucker wrote: > Greetings, > > Here is a simple question: > > Is it ok to put a unique index on the oid for my tables? We are in the > process of moving from Progress Software to PostgreSQL. In the Progress > world, you can always uniquely, and quickly find a record by using their > version of oid, which is recid. I remember reading somewhere that the oid > could be duplicated across the cluster, but would not be duplicated in a > single table. Maybe I dreamed it. What is the recommendation regarding this > and why? OID's are unique per database as long as they don't turn over - meaning oid's have a limited range - AFAIK 32 bit integer, so yes, inside a table you could use the oid (assuming you created the table WITH OIDS) to identify a record. HOWEVER: bad design. oid's are likely to go away at some point down the road (maybe in postgresql 12.0 or so :-)) ). Therefor, and since you're migrating anyways, it would be better to add a primary key column to each table. Unless I have a real need for a primary key, I usually just add a "id" column (as PK with default from a sequence) to my tables. Therefor I can always use the id column to identify records. This is portable and easy to migrate if you need to upgrade to a newer version of postgresql - or if you strike it rich and have to go to oracle or db2 it's still portable. Also: having a real column with a key if more failsafe. I once (long long ago) used oids as foreign keys. I remember back then dumping and restoring the db was a pain. Now you could use the "dump oids" option, but if you forget that and you restore the DB your relationship model will be a big pile of trash because the oid's change on restore. I'd recommend to go the extra mile and add at least a unique key column to your tables. Hope that helps UC -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBk6gwjqGXBvRToM4RAu5kAKCbMcYtk7qs3xv+UyrgD0RftGBpbwCgrHPi r8mynfAyne7lRETGLIMCz5E= =Dl/9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] OID Question
On Thu, Nov 11, 2004 at 10:04:30 -0500, Terry Lee Tucker <[EMAIL PROTECTED]> wrote: > Greetings, > > Here is a simple question: > > Is it ok to put a unique index on the oid for my tables? We are in the > process Yes, but you may occasionally have insert failures if the oid wraps around and you try to reuse one on an insert. > of moving from Progress Software to PostgreSQL. In the Progress world, you > can always uniquely, and quickly find a record by using their version of oid, > which is recid. I remember reading somewhere that the oid could be You should probably just use a normal column named recid and not try to use the special oid column to do this. > duplicated across the cluster, but would not be duplicated in a single table. > Maybe I dreamed it. What is the recommendation regarding this and why? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID Question
Terry Lee Tucker wrote: Greetings, Here is a simple question: Is it ok to put a unique index on the oid for my tables? We are in the process of moving from Progress Software to PostgreSQL. In the Progress world, you can always uniquely, and quickly find a record by using their version of oid, which is recid. I remember reading somewhere that the oid could be duplicated across the cluster, but would not be duplicated in a single table. Maybe I dreamed it. What is the recommendation regarding this and why? Just create a "recid" field of type SERIAL and add a unique constraint. You can create tables WITHOUT OIDS if you want to reclaim some space in the process. OIDs will wrap around and are only guaranteed to be unique in system-tables IIRC. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID Question
Many thanks to all who have responded. I hope to be as helpful to the list someday as each of you have been. Thanks... On Thursday 11 November 2004 10:04 am, Terry Lee Tucker saith: > Greetings, > > Here is a simple question: > > Is it ok to put a unique index on the oid for my tables? We are in the > process of moving from Progress Software to PostgreSQL. In the Progress > world, you can always uniquely, and quickly find a record by using their > version of oid, which is recid. I remember reading somewhere that the oid > could be duplicated across the cluster, but would not be duplicated in a > single table. Maybe I dreamed it. What is the recommendation regarding this > and why? > > Version: > PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 > 20030502 (Red Hat Linux 3.2.3-42) > > Thanks for your input... > -- > > Work: 1-336-372-6812 > Cell: 1-336-363-4719 > email: [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- Quote: 80 "Let us pray for the salvation of all of those who live in their totalitarian darkness -- pray that they will discover the joy of knowing God. But until they do, let us be aware that while they preach the supremacy of the state, declare its omnipotence over individual man, and predict its eventual domination of all peoples on the earth, they are the locus of evil in the modern world." --Ronald Reagan Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID Usage
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 [EMAIL PROTECTED]
CK Software GmbHhttp://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID Usage
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/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID Usage
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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] OID Usage
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 (<[EMAIL PROTECTED]>) "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) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID Usage
Bo Lorentsen <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID Usage
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/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID Usage
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/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OID Usage
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 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. pgpmo4AGLZZKj.pgp Description: PGP signature
Re: [GENERAL] OID Usage
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/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID Usage
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) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID Usage
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
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID Usage
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 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. pgpPQoQyQG9Jw.pgp Description: PGP signature
Re: [GENERAL] OID Usage
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 (<[EMAIL PROTECTED]>) "No hay cielo posible sin hundir nuestras raíces en la profundidad de la tierra"(Malucha Pinto) ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID Usage
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 _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: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID Usage
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 [EMAIL PROTECTED] 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?" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID Usage
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/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] OID Usage
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 ;)... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID Usage
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/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] OID Usage
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 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. pgp7IeNcdRW7l.pgp Description: PGP signature
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID Usage
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 (<[EMAIL PROTECTED]>) "La rebeldía es la virtud original del hombre" (Arthur Schopenhauer) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID Usage
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/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID Usage
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? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID Usage
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 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. pgpz8wW8NfbFe.pgp Description: PGP signature
Re: [GENERAL] OID Usage
Martijn van Oosterhout 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] OID Usage
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Out of curiosity, what clears out the old index tuples? Vacuum? Right. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID Usage
On Sat, Jan 15, 2005 at 04:00:19PM -0500, Tom Lane wrote: > Martijn van Oosterhout 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 [EMAIL PROTECTED] 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?" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] OID Usage
On Sat, Jan 15, 2005 at 04:21:24PM -0500, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> 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 (<[EMAIL PROTECTED]>) "Some men are heterosexual, and some are bisexual, and some men don't think about sex at all... they become lawyers" (Woody Allen) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] OID Usage
Alvaro Herrera wrote: > On Sat, Jan 15, 2005 at 04:21:24PM -0500, Tom Lane wrote: > > "Jim C. Nasby" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] OID Usage
Alvaro Herrera <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] OID Usage
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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID question
Chris Sutton wrote: > > I would like to use the oid as a unique key for a table but in creating > a test table and playing around with inserts, things don't seem to be > working as I thought they would. > > Where am I going wrong? There is a hidden column named 'oid'. Try "select oid, desc from test". Regards, Ed Loehr P.S.: An alternative approach worthy of consideration is to use a SEQUENCE object and possibly the SERIAL type.
Re: [GENERAL] OID rollover?
> What happens to my database when the OID rolls over? > > If the answer is "doom", is there anything I can do about it? > Actually, no one has every reported a roll-over, so I can't say for sure. They are unsigned ints, so it should go past 2 gigs. If it goes past 4 gigs, it rolls to zero again. Having duplicate oids in the same table is really not a crisis, though there could be come oid collision in the system tables that could cause problems. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] OID rollover?
On Sun, 9 Apr 2000, Charles Martin wrote: > What happens to my database when the OID rolls over? A simple-minded calculation yields that you would have to create about 1 million new (not updated) records every day for more than 10 years to exhaust the oid space. By the time that becomes a problem we'll all have 64 bit machines in anticipation of Y2038 anyway. > If the answer is "doom", is there anything I can do about it? Dump and reload your database. Unless you are actually *using* all of them (in which case you'd need more than 100 GB to simply store the tuple control structures), then "doom" is a feature. :) -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] OID problem
Patrick Hess <[EMAIL PROTECTED]> writes: > The only problem I have is related to this OID stuff -- I read a bit > about this and found a way to solve my initial problems by creating my > tables with "WITHOUT OIDS" -- works fine and CMS runs like on the > original plattform (even faster but that's an other story). > Now my real problem is that the CMS itself creates new tables for new > asset types and does not create the tables using "WITHOUT OIDS". Why do you care? > Now my question: Is it possible somehow to switch for a complete > database to WITHOUT OIDS so that tables are created without oid by > default? There is a switch for that in CVS tip, but not in existing releases IIRC. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Oid problem
On Thursday 08 April 2004 15:21, Sky wrote: > Problem: > pg_loopen() unable to open PostgreSQL large object in... > > Solution from another mailing list: > My blob oid was very big. > I had to cast to (float) the oid parameter of pg_loopen. Sorry, bigger than what? Why float? > Question: > The oid is a type, isn't it? Yes > I tried to use the oid in a function: > > CREATE FUNCTION addfoto(character varying, oid) > RETURNS INTEGER > AS > ' > BEGIN; > INSERT INTO foto(filename, image) VALUES ( $1, $2 ); > COMMIT; > SELECT 1 AS RESULT; > ' > LANGUAGE 'sql'; Assuming the definition of "foto" is OK, looks fine from here. > So, What can I do, and how, how can I "CAST" ?? Sorry - dont understand, but you can cast by: SELECT foo::int8 ... SELECT CAST(foo as int8) ... Is the problem that you want to handle oid's (0 .. 4 billion) and you are using int4 (-2 billion .. +2 billion). In which case int8 might be a better choice than int4. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Oid problem
Richard Huxton írta: On Thursday 08 April 2004 15:21, Sky wrote: Problem: pg_loopen() unable to open PostgreSQL large object in... Solution from another mailing list: My blob oid was very big. I had to cast to (float) the oid parameter of pg_loopen. Sorry, bigger than what? Why float? Watch these links: http://archives.postgresql.org/pgsql-php/2003-09/msg00077.php http://archives.postgresql.org/pgsql-php/2003-09/msg00079.php I found it by google. This error message is my problem: pg_loopen() unable to open large object / PHP4 / I tried to use the oid in a function: CREATE FUNCTION addfoto(character varying, oid) RETURNS INTEGER AS ' BEGIN; INSERT INTO foto(filename, image) VALUES ( $1, $2 ); COMMIT; SELECT 1 AS RESULT; ' LANGUAGE 'sql'; Assuming the definition of "foto" is OK, looks fine from here. So, What can I do, and how, how can I "CAST" ?? Sorry - dont understand, but you can cast by: Watch the links above. SELECT foo::int8 ... SELECT CAST(foo as int8) ... Is the problem that you want to handle oid's (0 .. 4 billion) and you are using int4 (-2 billion .. +2 billion). In which case int8 might be a better choice than int4. Sorry but I don't understand, where do I use int4 ?? Here is my table definition too: CREATE TABLE foto( filename CHARACTER VARYING, image OID ); HTH Many thanks! -- Sky sky AT sylvio .hu Debian Group - Association of Hungarian Linux Users Accessibility Project leader gpg --keyserver hkp://pgp.mit.edu --recv-keys 314D1B0C fingerprint = B211 E8C7 C6C2 18B2 9F30 ADF6 EDD9 A7FA 314D 1B0C ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Oid to text...
On 22 Nov 2004 14:25:26 +0200, Katsaros Kwn/nos <[EMAIL PROTECTED]> wrote: > Hi, > > Given the Oid restype of a Resdom "object", is there any system table I > could query in order to retrieve the text representation of this type? > > If for example a Resdom restype is 23 how can I get the string "integer" > or something like that? Are these mappings stored anyware? pg_catalog.pg_type ? Ian Barwick ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Oid to text...
"Katsaros Kwn/nos" <[EMAIL PROTECTED]> writes: > Given the Oid restype of a Resdom "object", is there any system table I > could query in order to retrieve the text representation of this type? If you're talking about C code inside the backend, format_type_be() is the usual subroutine. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] OID IS INVALID?
Mark Wilson wrote: > > Hello, > > I've been using version 6.4.2 for quite a while with absolutely no > problems. Yesterday morning, a block on the disk containing all of my > databases went bad. If a disc happens to get a bad sector, it is advisable to throw it away at once, because this means that surface got a scratch which grows larger every time the disc head moves over it. Trash your disc, and be happy again :) -- Leon. - "This may seem a bit weird, but that's okay, because it is weird." - Perl manpage.
Re: [GENERAL] OID IS INVALID?
Mark Wilson wrote: > > Last night I reformatted the disk, create a new filesystem and restored > the last good full backup (from about a week ago) hoping I could get a ^^^ What did you use for backup ? pg_dump/copy or you cp-ed datafiles to another place ? > version of the system back on the disk prior to this event. Unfortunately > I'm still given the same exact errors shown above. Does anyone have an > idea of what I can to do fix these problems. I really need to get the > database back online quickly. Either disk is still bad or you didn't use pg_dump/copy for backup. Vadim
Re: [GENERAL] OID IS INVALID?
Dear All, THis is a test, please ignore.. sorry to border.. Jacky Hui
RE: [GENERAL] OID vs SERIAL
Oracle has a rowid column automatically built into every row. Most vendors suggest using primary keys for all tables instead of a "rowid". -Original Message- From: Dirk Lutzebaeck [SMTP:[EMAIL PROTECTED]] Sent: Monday, August 30, 1999 2:15 AM To: Jay Bloodworth Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] OID vs SERIAL Does anyone know if the big boys like Oracle, Informix, Sybase, DB2 and so on support something like OIDs? This would be an interesting portability issue. Dirk
Re: [GENERAL] OID output problems
On Tue, 2 May 2000, surfer girl wrote:
> Hello,
>
> I am trying to write a program that will input and output images from a Postgres
>database using PHP.
>
> The inputting seems to be going fine - well, it seems to be fine, since I can't
>test it because it's the output I'm stuck on.
>
> What I've got so far is:
>
> Header("Content-type: image/gif");
> (DATABASE CONNECT STUFF HERE)
> pg_exec ($conn, "BEGIN");
> $result = pg_Exec($conn, "SELECT file FROM file WHERE key_fileid = '7'");
> $oid = pg_Result($result, 0, "file");
> $handle = pg_loopen($conn, $oid, "r");
> pg_loreadall($handle);
> pg_exec ($conn, "COMMIT");
> ?>
>
> What this does is print up a broken image in the browser.
I known this proble. You are probably right. A problem (IMHO) is in PHP.
Try use insdead pg_readall() this code with pg_loread():
$data = pg_loread($handle, 500 /* or a exactly size */);
echo $data;
Karel
Re: [GENERAL] OID output problems
On Tue, 02 May 2000, surfer girl wrote: > Hello, > > I am trying to write a program that will input and output images from a Postgres >database using PHP. I made a very simple display image routine as follows: --- --- -- Robert B. Easter [EMAIL PROTECTED]
Re: [GENERAL] OID output problems
Hi Robert,
Thanks for the code. I tried the code, and it works fine (no errors) except instead of
getting an image output, it outputs "/tmp/php08543baa" to the browser or something
similar (just like my code). Am I missing something where I should be transferring a
temp file to something web-readable - or is my Postgres doing something funny? Sorry
if this sounds dense but I've followed a number of examples and I thought the actual
image was supposed to be sent directly to the browser using pg_loreadall.
Thanks again!
--- "Robert B. Easter" <[EMAIL PROTECTED]> wrote:
>/*
>Takes arg: imageid=oid of image
>*/
>
>$conn = pg_pconnect("user=username dbname=databasename");
>
>pg_exec($conn, "begin");
>@ $fd = pg_loopen($conn, $imageid, "r");
>if($fd) {
>$hdr = pg_loread($fd, 4);
>switch($hdr) {
>case "\xFF\xD8\xFF\xE0":
>Header( "Content-type: image/jpeg" );
>break;
>case "GIF8":
>Header( "Content-type: image/gif" );
>break;
>case "\x89PNG":
>Header( "Content-type: image/png" );
>break;
>}
>echo $hdr;
>pg_loreadall($fd);
>pg_exec($conn, "end");
>}
>else {
>echo "Query rejected.";
>}
>
>pg_close($conn);
>?>
>---
>--
>Robert B. Easter
>[EMAIL PROTECTED]
_
Get [EMAIL PROTECTED] email Today!
Visit http://mail.getitgear.com
Re: [GENERAL] OID output problems
On Tue, 02 May 2000, surfer girl wrote: > Hi Robert, > > Thanks for the code. I tried the code, and it works fine (no errors) except instead >of getting an image output, it outputs "/tmp/php08543baa" to the browser or something >similar (just like my code). Am I missing something where I should be transferring a >temp file to something web-readable - or is my Postgres doing something funny? Sorry >if this sounds dense but I've followed a number of examples and I thought the actual >image was supposed to be sent directly to the browser using pg_loreadall. Try recompiling php 4.0RC1 or whatever is current with the latest Apache source (1.3.12). I'm thinking maybe you are not running php as compiled into the server. You can install Apache first as normal and make sure that all of its bin files are on the PATH so that apxs is available. Then compile php (this is how I did it): ./configure --with-apxs --with-pgsql --without-mysql --with-xml --enable-track-vars make make install Then check your httpd.conf file: LoadModule php4_module libexec/libphp4.so AddModule mod_php4.c DirectoryIndex index.html index.php AddType application/x-httpd-php .php AddType application/x-httpd-source .phps Good luck.
Re: [GENERAL] OID output problems
--- "Robert B. Easter" <[EMAIL PROTECTED]> wrote:
>
>Try recompiling php 4.0RC1 or whatever is current with the latest Apache
>source (1.3.12). I'm thinking maybe you are not running php as compiled into
>the server.
Thanks - I had PHP compiled into the server - though I may try the recompile as a last
resort (before the major last resort of just keeping images as files and putting the
filename and location into the db).
Someone asked if my INPUT was correct. Here's what I've got:
pg_Exec($conn, "BEGIN");
$oid = pg_locreate($conn);
$handle = pg_loopen($conn, $oid, "w");
pg_lowrite($handle, $file);
pg_loclose($handle);
$result = pg_Exec($conn, "INSERT INTO file (file, filetype, permissions, key_imageid)
VALUES ('$oid', '$filetype','$permissions','$imageid')" );
pg_Exec($conn, "COMMIT");
Unfortunately there aren't tons of examples around for this stuff so I don't have much
to compare this to. (Based this off the Addison Wesley book).
Thanks
_
Get It Gear --> http://www.getitgear.com
Re: [GENERAL] OID output problems
On Tue, May 02, 2000 at 10:13:14PM -0800, surfer girl wrote: > --- "Robert B. Easter" <[EMAIL PROTECTED]> wrote: > > > >Try recompiling php 4.0RC1 or whatever is current with the latest Apache > >source (1.3.12). I'm thinking maybe you are not running php as compiled into > >the server. > > Thanks - I had PHP compiled into the server - though I may try the recompile as a >last resort (before the major last resort of just keeping images as files and putting >the filename and location into the db). > > Someone asked if my INPUT was correct. Here's what I've got: > > pg_Exec($conn, "BEGIN"); > $oid = pg_locreate($conn); > $handle = pg_loopen($conn, $oid, "w"); > pg_lowrite($handle, $file); Hmm, based on my reading of the php4 docs, this will right the contents of the variable 'file' to the lo, expecting it to be a null terminated string. I'm not sure how you're supposed to get binary data in there. Is 'file' by any chance, the name of your file, not the contents? Ross -- Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
Re: [GENERAL] OID output problems
The documentation on this is so scarse it's hard to figure out what the right format is supposed to be for all this. After much searching (websites, various mailing list archives), I found the "note" that PHP has a special variable "userfile" for file uploads. This I have changed, and a straight upload to a file works OK, but the Postgres input does not. "$userfile" actually points to a file such as "/tmp/php12595baa" - now, my question is, how do I get the actual BINARY FILE into the database, and not the NAME of the TEMP FILE. (This, at least, explains the bizarre output). What I had put for my input was taken out of the Addison Wesley book but the explanation was not enough and frankly it's not working. I have yet to find another example of how to do this, and the function reference is cryptic at best. Any ideas would be absolutely appreciated. Thanks. --- "Ross J. Reedstrom" <[EMAIL PROTECTED]> > wrote: >> pg_Exec($conn, "BEGIN"); >> $oid = pg_locreate($conn); >> $handle = pg_loopen($conn, $oid, "w"); >> pg_lowrite($handle, $file); > >Hmm, based on my reading of the php4 docs, this will right the contents of the >variable >'file' to the lo, expecting it to be a null terminated string. I'm not sure how you're >supposed to get binary data in there. Is 'file' by any chance, the name of your file, >not the contents? > >Ross >-- >Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> >NSBRI Research Scientist/Programmer >Computer and Information Technology Institute >Rice University, 6100 S. Main St., Houston, TX 77005 _ Get It Gear --> http://www.getitgear.com
Re: [GENERAL] OID output problems
surfer girl wrote:
>
> The documentation on this is so scarse it's hard to figure out what the right format
>is supposed to be for all this. After much searching (websites, various mailing list
>archives), I found the "note" that PHP has a special variable "userfile" for file
>uploads.
It has a few.
/*
echo "Original file name is $userfile_name\n";
echo "File size is $userfile_size\n";
echo "File type is $userfile_type\n";
echo "Temporary file name is $userfile\n";
*/
> This I have changed, and a straight upload to a file works OK, but the Postgres
>input does not. "$userfile" actually points to a file such as "/tmp/php12595baa" -
>now, my question is, how do I get the actual BINARY FILE into the database, and not
>the NAME of the TEMP FILE. (This, at least, explains the bizarre output).
>
> What I had put for my input was taken out of the Addison Wesley book but the
>explanation was not enough and frankly it's not working. I have yet to find another
>example of how to do this, and the function reference is cryptic at best.
>
> Any ideas would be absolutely appreciated. Thanks.
>
> --- "Ross J. Reedstrom" <[EMAIL PROTECTED]>
> > wrote:
> >> pg_Exec($conn, "BEGIN");
> >> $oid = pg_locreate($conn);
> >> $handle = pg_loopen($conn, $oid, "w");
> >> pg_lowrite($handle, $file);
> >
> >Hmm, based on my reading of the php4 docs, this will write the contents of the
>variable
> >'file' to the lo, expecting it to be a null terminated string. I'm not sure how
>you're
> >supposed to get binary data in there. Is 'file' by any chance, the name of your
>file,
> >not the contents?
PHP doesn't really have an "open this file into memory" function, but it _does_ allow
you to burst lines of a file in, with fgets/fputs, or to build an array out
of the file contents. So, you could run a loop (metacoded, not yet tested):
if($filetowrite = fopen ("$userfile", "rb") //rb is read binary, right?
{
while (!feof($userfile)) //as long as it's not end of file
{
$stringtowrite = fgets($userfile); //get a string
pg_lowrite($handle, $file); //write that string
/* Offhand, I have no idea how fgets will handle the chars in your */
/* file. It's supposed to read to a newline, so you might have to */
/* Put one back in, a "/n" in the string if your BLOB, however,*/
/* has no lines, well that's another problem. */
}
}
All this being said, a frequent recommendation from the PHP list is to store the
file, on your filesystem, in the filesystem database, outside of an additional
database layer (yes, filesystems are databases). Combining this recommendation
with the limits imposed by PostgreSQL on row size, it's fairly impractical to
be doubling up on the work to write into a postgres BLOB, rather than having
BLOB storage outside the DB itself, with DB pointing to it (via filename). Of
course, you loose the ability for the DB engine to search _within_ the BLOB...
HTH,
-Bop
--
Brought to you from boop!, the dual boot Linux/Win95 Compaq Presario 1625
laptop, currently running RedHat 6.1. Your bopping may vary.
Re: [GENERAL] OID and joins
DaVinci writes: > Can I use OIDs for joins between tables without losing functionality? Sure. > The problems I am afraid of are: > > - pg_dump/psql: Can I recover OIDs from security copies?. pg_dump -o > - Indexes: joins between tables linked with OIDs use indexes from > OIDs?, should I have to make that indexes or they are automatic?. You have to make one. > - Any other limitation I don't achieve to view?. Nope, this is really a relatively standard thing. The oid column isn't special as compared to other columns, aside from the fact that it is automatically created. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] OID and joins
On Wed, Oct 18, 2000 at 06:18:07PM +0200, Peter Eisentraut wrote: > > - Any other limitation I don't achieve to view?. > > Nope, this is really a relatively standard thing. The oid column isn't > special as compared to other columns, aside from the fact that it is > automatically created. ...and isn't preserved in backups by default, causes problems while seleting from one table into another for purposes of removing a column, and isn't preserved during cluster generation... ...oh yeah, and it isn't standard, so it'l make it hard to migrate to a different database system should you need to do that for some reason... -- Adam Haberlach|ASCII /~\ [EMAIL PROTECTED] | Ribbon \ / Against http://www.newsnipple.com | CampaignX HTML '88 EX500 |/ \ E-mail
Re: [GENERAL] OID of current function
Jim C. Nasby wrote:
Is there an easy way to get the OID of the currently running function?
(IE: the function you're in when you execute the code to see what
function you're in, if that makes any sense).
In what language? In C you can use:
Datum your_function(PG_FUNCTION_ARGS)
{
Oid funcOid = fcinfo->flinfo->fn_oid;
...
}
Regards,
Thomas Hallgren
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OID of current function
On Wed, Jan 12, 2005 at 04:08:28PM +0100, Thomas Hallgren wrote:
> Jim C. Nasby wrote:
> >Is there an easy way to get the OID of the currently running function?
> >(IE: the function you're in when you execute the code to see what
> >function you're in, if that makes any sense).
>
> In what language? In C you can use:
>
> Datum your_function(PG_FUNCTION_ARGS)
> {
> Oid funcOid = fcinfo->flinfo->fn_oid;
> ...
> }
This would be in plpgsql.
Some other info:
What I'm trying to do is use contrib/userlock to serialize access to a
function. The only effective way to come up with a unique lock number
that I've been able to think of is to use the OID of the function
itself.
What I find somewhat interesting is every other database I've used that
exposes some kind of 'object ID' has a set of functions to map between
an object name and it's ID, and vice-versa. It seems like this is
something that would be good for PostgreSQL to have.
--
Jim C. Nasby, Database Consultant [EMAIL PROTECTED]
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?"
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] OID of current function
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > What I find somewhat interesting is every other database I've used that > exposes some kind of 'object ID' has a set of functions to map between > an object name and it's ID, and vice-versa. regression=# create function myfunc(int) returns int as 'select $1' language sql; CREATE FUNCTION regression=# SELECT 'myfunc(int)'::regprocedure::oid; oid 431373 (1 row) regression=# select 431373::regprocedure; regprocedure - myfunc(integer) (1 row) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OID as Primary Key
Jonas Bengtsson <[EMAIL PROTECTED]> writes: > I have problems with using OID as PK. This strikes me as a fairly bad idea in any case. What happens if you must dump and reload your database? Better to use a SERIAL column as primary key. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] OID as Primary Key
On Thu, 22 Mar 2001, Jonas Bengtsson wrote: > Hello, > I have problems with using OID as PK. I have created a > UNIQUE INDEX on the oid on a relation called CourseEvents. > The relation that is supposed to have a FK to CourseEvents > is CourseEventsForums and it has a field called ceid which > has oid as dtatype. > But when I try to create a FK to that table I get following > error: > --- > PostgreSQL said: ERROR: UNIQUE constraint matching given > keys for referenced table "courseevents" not found > Your query: > ALTER TABLE CourseEventForums ADD CONSTRAINT > RefCourseEvents43 > FOREIGN KEY (ceid) > REFERENCES CourseEvents(oid) > --- > What is wrong? The message is a bit misleading. Oid isn't a "user" column, and currently you can only create references to user columns. Given the issues involved with oid (since it's system wide it rolls over faster than a serial, you have to make sure to dump with oids, the fact that you have much less control over its value if you need to do something), I'm not 100% sure that's a bad thing, although IIRC references to oid are on the todo list. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] OID as Primary Key
Jonas Bengtsson wrote: > > Can't you do a dump with the oid's? > > But when I want to know the primary key of the inserted row > I have to do an extra select query. If I use oid I just use > pg_getlastoid() in php. > And it is redundant data to store another integer. > > Comments? I have often used OID as primary key, usually for the reason's you cite. IMHO, in the long run, the two major extra worries are often not worth it: 1) You must explicitly dump and restore OIDs all the time 2) You loose generic SQL compatibility On the other hand, we've never had a failure from theses causes. In short, using OIDs seems to work well enough for us, but the downsides in practice have been larger than we expected, and the upsides smaller. In our case, the extra serial would be a small part of the overall data, so I kind of wish I had not used OIDs. Just my $0.02 worth. -- Karl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] OID and PK/FK KEYS
A better solution is to use the serial data type. OID is depreciated and may go away. http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-SERIAL On 19 Oct 2004 07:54:36 -0700, Raffaele Spizzuoco <[EMAIL PROTECTED]> wrote: > Hi! > > I'm from Italy, and sorry about my english... > I have a question that I know it is already said in the groups but I > have however some doubts > I have seen it is technically possible to use OID as PRIMARY KEY and > as FOREIGN KEY but it is correct to do so for the database's logical > integrity? > Is it better I use in any case other keys and not oid to avoid the > possible wraparound? or the wraparound is an extreme case and so I can > use quietly OID as PRIMARY and FOREIGN KEY? > > Thanks > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] OID Perfomance - Object-Relational databases
Hi everyone, I just came across a thread in the pgsql archives from October 2000, and found this post particularly interesting: http://archives.postgresql.org/pgsql-sql/2000-10/msg00044.php ...because I had already been designing something similar but on a larger scale, for an integrated information system with scheduling, accounting, internal messaging, inventory, safety management, and slew of other things. Initially when I read through the pgsql docs, I decided that I didn't ever want to deal with wraparound, and so I wouldn't use OIDs. But as I've been designing and implementing prototypes of this system, I've found it less than elegant to have universal references, because I always need to have a table name involved. Recently I clued in that OIDs could solve this, so I went looking through the archives and found that thread. I'm just wondering a few things. First, how would Michael/Tom's suggestion (to have a single sequence used by multiple tables) work exactly, i.e., if I had a number from that sequence, how would I know which table it belonged to without checking *all* of the tables for a row with that ID number? Josh said he would try the idea and report back on performance...how did this all turn out? If there's an answer to that question, then is it conceivable to use an int8 sequence across, say, 100 tables (which there may well be in the pool of things I would want to be able to arbitrarily reference by the time I'm done this project) as a substitute for OIDs until there are 64-bit OIDs? Either way, are 64-bit OIDs planned within the next couple years? I could only find discussion on this from 2000, and the announcement that OIDs were optional after a certain version. Thanks, Kev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] OID Perfomance - Object-Relational databases
"Kevin Field" <[EMAIL PROTECTED]> writes: > Either way, are 64-bit OIDs planned within the next couple years? No, they're not planned at all. That line of thought has pretty much died off, to the point where OIDs in user tables are not just deprecated but not there at all by default. There are basically two ways to attack the problem of a database-wide unique ID: * Use a single int8 sequence for the whole database; * Use the combination of table OID and row OID (or, perhaps, an int8 sequence for the row identifier, if you need more than a billion or so rows in the table). The good thing about a two-part unique ID is that you can tell by inspection which table the object is in, which is pretty handy. But of course it's a bit ugly notationally, since you have to deal with two fields not one. The problem of dumping and restoring raw OIDs is still as bad as it was in 2000. However there's now the "regclass" datatype that can provide a symbolic display of table OID. If I were doing this today I'd use a regclass column for the table part of a unique ID, and per-table serial or bigserial counters for the row part. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] OID Perfomance - Object-Relational databases
> There are basically two ways to attack the problem of a database-wide > unique ID: > > * Use a single int8 sequence for the whole database; > > * Use the combination of table OID and row OID (or, perhaps, an int8 > sequence for the row identifier, if you need more than a billion or > so rows in the table). There's a third way: int8 serial, lower 48 bits for row id, upper 16 bits appended with your own table id. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] OID of type by name.
SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 AND typname::regtype = 'integer'; Many thanks to Florian Pflug. 2010/12/8 Dmitriy Igrishin > Hey general@, > > SELECT oid FROM pg_type WHERE typname = 'integer'; > oid > - > (0 rows) > > SELECT oid FROM pg_type WHERE typname = 'int4'; > oid > - > 23 > (1 row) > > How can I get OID by name rather than alias ? > > -- > // Dmitriy. > > > -- // Dmitriy.
Re: [GENERAL] OID of type by name.
Dmitriy Igrishin writes: >> How can I get OID by name rather than alias ? > SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 AND > typname::regtype = 'integer'; Seems like the hard way --- if you think carefully about what regtype is doing, you'll realize that this is incredibly inefficient, as well as a lot of typing. I usually do SELECT 'integer'::regtype::oid when I need a quick numeric lookup. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OID of type by name.
Yeah, thank you very much! I've found it already too, but not post back! Thanks! 2010/12/8 Tom Lane > Dmitriy Igrishin writes: > >> How can I get OID by name rather than alias ? > > > SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 AND > > typname::regtype = 'integer'; > > Seems like the hard way --- if you think carefully about what regtype > is doing, you'll realize that this is incredibly inefficient, as well > as a lot of typing. I usually do SELECT 'integer'::regtype::oid > when I need a quick numeric lookup. > >regards, tom lane > -- // Dmitriy.
Re: [GENERAL] OID of type by name.
Actually, all I need is to: SELECT oid::regtype, oid FROM pg_type WHERE ... to make cache of OIDs. 2010/12/8 Dmitriy Igrishin > Yeah, thank you very much! > > I've found it already too, but not post back! > > Thanks! > > 2010/12/8 Tom Lane > > Dmitriy Igrishin writes: >> >> How can I get OID by name rather than alias ? >> >> > SELECT oid FROM pg_type WHERE typtype = 'b' AND typarray <> 0 AND >> > typname::regtype = 'integer'; >> >> Seems like the hard way --- if you think carefully about what regtype >> is doing, you'll realize that this is incredibly inefficient, as well >> as a lot of typing. I usually do SELECT 'integer'::regtype::oid >> when I need a quick numeric lookup. >> >>regards, tom lane >> > > > > -- > // Dmitriy. > > > -- // Dmitriy.
Re: Re: [GENERAL] OID as Primary Key
Can't you do a dump with the oid's? But when I want to know the primary key of the inserted row I have to do an extra select query. If I use oid I just use pg_getlastoid() in php. And it is redundant data to store another integer. Comments? /Jonas B > Jonas Bengtsson <[EMAIL PROTECTED]> writes: > > I have problems with using OID as PK. > > This strikes me as a fairly bad idea in any case. What happens if you > must dump and reload your database? Better to use a SERIAL column as > primary key. > > regards, tom lane > Mvh, Jonas Bengtsson ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Re: [GENERAL] OID as Primary Key
--- Jonas Bengtsson <[EMAIL PROTECTED]> wrote: > Can't you do a dump with the oid's? Yes, but I would never suggest it. > But when I want to know the primary key of the inserted row > I have to do an extra select query. If I use oid I just use > pg_getlastoid() in php. > And it is redundant data to store another integer. You should not use oid's as primary keys just because you want to save a little HD space. I contributed a some code on PHPBuilder for a function that returns the insert id of a SERIAL primary key. You can view it here: http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304 . It works well for me and it does not kludge my code like using the oid does. Brent __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: Re: [GENERAL] OID as Primary Key
Title: RE: Re: [GENERAL] OID as Primary Key And you can get the last SERIAL inserted using currval. MikeA >> -Original Message- >> From: Brent R. Matzelle [mailto:[EMAIL PROTECTED]] >> Sent: 22 March 2001 16:36 >> To: [EMAIL PROTECTED] >> Subject: Re: Re: [GENERAL] OID as Primary Key >> >> >> --- Jonas Bengtsson <[EMAIL PROTECTED]> wrote: >> > Can't you do a dump with the oid's? >> >> Yes, but I would never suggest it. >> >> > But when I want to know the primary key of the inserted row >> > I have to do an extra select query. If I use oid I just use >> > pg_getlastoid() in php. >> > And it is redundant data to store another integer. >> >> You should not use oid's as primary keys just because you want >> to save a little HD space. >> >> I contributed a some code on PHPBuilder for a function that >> returns the insert id of a SERIAL primary key. You can view it >> here: >> http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304 >> . It works well for me and it does not kludge my code like >> using the oid does. >> >> Brent >> >> __ >> Do You Yahoo!? >> Get email at your own domain with Yahoo! Mail. >> http://personal.mail.yahoo.com/ >> >> ---(end of >> broadcast)--- >> TIP 1: subscribe and unsubscribe commands go to >> [EMAIL PROTECTED] >> _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
