Re: [HACKERS] pg_depend dependency and concurrent DDL issues in PG 8.3.x
>> I see that all these issues have been fixed and committed by Tom via >> git commitid: 281a724d on 6th June, 2008. Was wondering why this fix >> is not in these supported branches like 8.3.13 for example. Kinda >> confused.. > > We don't usually back-patch such large changes. Oh ok. Thanks. Regards, Nikhils -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend dependency and concurrent DDL issues in PG 8.3.x
On Thu, Mar 3, 2011 at 6:09 AM, Nikhil Sontakke wrote: > I see that all these issues have been fixed and committed by Tom via > git commitid: 281a724d on 6th June, 2008. Was wondering why this fix > is not in these supported branches like 8.3.13 for example. Kinda > confused.. We don't usually back-patch such large changes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_depend dependency and concurrent DDL issues in PG 8.3.x
Hi, Am referring to the following conversation: http://archives.postgresql.org/pgsql-bugs/2007-12/msg00190.php To summarize, in 8.3.x due to improper locking and concurrency issues in the DROP OBJECT codepath, for example if one tries to drop an index while dropping the table from another session, we end up with orphaned index objects. There are similar issues related to orphaned triggers, "tuple concurrent update" errors etc. in that thread. > s1=> CREATE TABLE x(i integer); > > s2=> BEGIN; > s2=> CREATE UNIQUE INDEX x_pkey ON x(i); > > s1=> DROP TABLE x; > (Session hangs) > > s2=> COMMIT I see that all these issues have been fixed and committed by Tom via git commitid: 281a724d on 6th June, 2008. Was wondering why this fix is not in these supported branches like 8.3.13 for example. Kinda confused.. Regards, Nikhils -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
On Fri, Jan 14, 2011 at 09:39, Joel Jacobson wrote: > 2011/1/12 Alvaro Herrera : >> I think this code should live in the Wiki somewhere: >> http://wiki.postgresql.org/wiki/Snippets > > This file contains only the relevant remapping of pg_depend, folding > the internal linkages properly: > > https://github.com/gluefinance/pov/blob/master/sql/schema/pov/views/pg_depend_remapped.sql > > It can be tested stand-alone and does not require any other components > from the pov project. > > Can I create a wiki snippet myself or do I need some kind of admin access? Absolutely, no admin access required. As long as you have (or create) a community account, you can edit or create pages. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
2011/1/12 Alvaro Herrera : > I think this code should live in the Wiki somewhere: > http://wiki.postgresql.org/wiki/Snippets This file contains only the relevant remapping of pg_depend, folding the internal linkages properly: https://github.com/gluefinance/pov/blob/master/sql/schema/pov/views/pg_depend_remapped.sql It can be tested stand-alone and does not require any other components from the pov project. Can I create a wiki snippet myself or do I need some kind of admin access? -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
2011/1/13 David Fetter : > Please put a self-contained example on the snippets page, and please > also to check that it actually runs before doing so. You'd mangled > some aliases in the query you sent, which leads me to believe you > hadn't actually tried running it. I actually hadn't really solved the problem at the time I wrote my last email, it turned out I had to do things a bit differently to avoid running into problems with corner cases. I will put together a self-contained example like you suggested and get back shortly :-) -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
On Wed, Jan 12, 2011 at 09:09:31PM +0100, Joel Jacobson wrote: > (sorry for top posting, No worries. > iPhone + drunk) A dangerous combination indeed. I hear water, NSAIDs and time can help with the hangover ;) > pg_depend_before is a select * from pg_depend before creating the > test db model Please put a self-contained example on the snippets page, and please also to check that it actually runs before doing so. You'd mangled some aliases in the query you sent, which leads me to believe you hadn't actually tried running it. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
(sorry for top posting, iPhone + drunk) pg_depend_before is a select * from pg_depend before creating the test db model Sent from my iPhone On 12 jan 2011, at 20:36, David Fetter wrote: > On Wed, Jan 12, 2011 at 08:06:24PM +0100, Joel Jacobson wrote: >> 2011/1/12 Tom Lane : >>> I've sometimes found it useful to think of internal dependencies as >>> acting like normal dependencies pointing in the other direction. >>> I'm not sure that would do much to solve your problem, but it might >>> be worth trying. >> >> Tom, you are a genious! No, seriously, I mean it, this is awesome, it >> worked! YES! You totally saved my day! Thank you! Finally! I'm so >> happy! :-) :-) :-) >> >> This was the little piece of code: >> >> CASE WHEN DepType ~ '^(a|ni|in|an|na)$' THEN >>--- Swap edges >> ELSE >>-- Do not swap edges >> END >> >> Look at the attached svg graph how beautiful the automatically >> generated graph look like now! :-) >> >> The tsort of the objects now sort all the normal objects in a creatable >> order! >> >> Here is the result of the tsort (only including the normal objects >> (the one I care about (I don't have to create the internal/auto >> objects, nor drop them))): >> >> The query below can both produce a DOT-format graph and a tsort of the >> creatable order of objects: >> >> WITH >> NewObjectOids AS ( >>SELECT * FROM pg_depend WHERE deptype <> 'p' >>EXCEPT >>SELECT * FROM pg_depend_before > > To what does pg_depend_before refer? > > Cheers, > David. > -- > David Fetter http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fet...@gmail.com > iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
On Wed, Jan 12, 2011 at 08:06:24PM +0100, Joel Jacobson wrote: > 2011/1/12 Tom Lane : > > I've sometimes found it useful to think of internal dependencies as > > acting like normal dependencies pointing in the other direction. > > I'm not sure that would do much to solve your problem, but it might > > be worth trying. > > Tom, you are a genious! No, seriously, I mean it, this is awesome, it > worked! YES! You totally saved my day! Thank you! Finally! I'm so > happy! :-) :-) :-) > > This was the little piece of code: > > CASE WHEN DepType ~ '^(a|ni|in|an|na)$' THEN > --- Swap edges > ELSE > -- Do not swap edges > END > > Look at the attached svg graph how beautiful the automatically > generated graph look like now! :-) > > The tsort of the objects now sort all the normal objects in a creatable order! > > Here is the result of the tsort (only including the normal objects > (the one I care about (I don't have to create the internal/auto > objects, nor drop them))): > > The query below can both produce a DOT-format graph and a tsort of the > creatable order of objects: > > WITH > NewObjectOids AS ( > SELECT * FROM pg_depend WHERE deptype <> 'p' > EXCEPT > SELECT * FROM pg_depend_before To what does pg_depend_before refer? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
Excerpts from Joel Jacobson's message of mié ene 12 16:06:24 -0300 2011: > The query below can both produce a DOT-format graph and a tsort of the > creatable order of objects: > > WITH > NewObjectOids AS ( > SELECT * FROM pg_depend WHERE deptype <> 'p' > EXCEPT > SELECT * FROM pg_depend_before > ), I think this code should live in the Wiki somewhere: http://wiki.postgresql.org/wiki/Snippets -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
On Wed, Jan 12, 2011 at 2:06 PM, Joel Jacobson wrote: > Tom, you are a genious! No, seriously, I mean it, this is awesome, it > worked! YES! You totally saved my day! Thank you! Finally! I'm so > happy! :-) :-) :-) Hey, guys, I think it worked...! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
Joel Jacobson writes: > 2011/1/12 Tom Lane : >> This isn't particularly *useful*, maybe, but it's hardly "impossible". >> And if we analyzed function dependencies in any detail, circular >> dependencies among functions would be possible (and useful). > Thanks Tom for clarifying, this makes me even more motivated into > implementing the "creation order"-algorithm using only sql/plpgsql and > pg_depend. > If you have any ideas on how to do this, in addition to reading the > dependency.c and pg_dump_sort.c source code, they would be highly > appreciated. I've sometimes found it useful to think of internal dependencies as acting like normal dependencies pointing in the other direction. I'm not sure that would do much to solve your problem, but it might be worth trying. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
2011/1/12 Tom Lane : > This isn't particularly *useful*, maybe, but it's hardly "impossible". > And if we analyzed function dependencies in any detail, circular > dependencies among functions would be possible (and useful). Thanks Tom for clarifying, this makes me even more motivated into implementing the "creation order"-algorithm using only sql/plpgsql and pg_depend. If you have any ideas on how to do this, in addition to reading the dependency.c and pg_dump_sort.c source code, they would be highly appreciated. Any tips on articles on graph algorithms which not only take edges (node->node) as indata, but also a "edge type" as indata (i.e. the deptype in pg_depend) would also be very useful. I have only found algorithms to do sorting on "normal" directional graphs, where all edges are threated the same. -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
Joel Jacobson writes: > Also, circular dependencies seems impossible for some object classes, > such as functions, views, constraints and triggers. regression=# create table tt(f1 int, f2 int); CREATE TABLE regression=# create view v1 as select * from tt; CREATE VIEW regression=# create view v2 as select * from v1; CREATE VIEW regression=# create or replace view v1 as select * from v2; CREATE VIEW regression=# drop view v1; ERROR: cannot drop view v1 because other objects depend on it DETAIL: view v2 depends on view v1 HINT: Use DROP ... CASCADE to drop the dependent objects too. regression=# drop view v2; ERROR: cannot drop view v2 because other objects depend on it DETAIL: view v1 depends on view v2 HINT: Use DROP ... CASCADE to drop the dependent objects too. This isn't particularly *useful*, maybe, but it's hardly "impossible". And if we analyzed function dependencies in any detail, circular dependencies among functions would be possible (and useful). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
2011/1/12 Alvaro Herrera : > FWIW this idea fails when you consider stuff such as circular foreign > keys (and I suppose there are other, more common cases). If you really > want something general you need to break those apart. (This is the > explanation for the “break the loop” code in pg_dump I imagine) Good point. Yes, the algorithm must be able to "break the loop", i.e. remove the edges causing the loops. This has already been properly solved in pg_dump, but I frankly don't really understand exactly how this is done, at least not the "general rule" on how to do it, even after spending hours studying the source code. Also, circular dependencies seems impossible for some object classes, such as functions, views, constraints and triggers. None of them can possibly refer to them self. If you only need to create/drop objects of these classes (like in my case within the pov-project), it's not important to "break the loop" in a clever way, i.e. simply removing any of the edges, not necessarily the best suited one, will do for my purpose. I have updated the example with two circular relations: -- Circular dependencies: CREATE TABLE tselfref ( id int not null PRIMARY KEY, parentid int not null REFERENCES tselfref(id) ); CREATE TABLE tcircular ( id int not null PRIMARY KEY, id2 int not null REFERENCES tselfref(id) ); ALTER TABLE tselfref ADD COLUMN id2 int not null REFERENCES tcircular ( id ); I have also updated pd_depend.[sql|dot|png|svg] and pg_depend_actual.[dot|png|svg] with the circular references. The dotted edges in pg_depend_actual are the edges which must be removed to "break the loop". Any ideas on how to design an algorithm to transform the digraph pg_depend into pg_depend_actual are highly appreciated. -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
Excerpts from Joel Jacobson's message of mié ene 12 07:07:35 -0300 2011: > The automatically created objects, such as primary key indexes, > constraints and triggers, have been ignored in this graph, as they are > implicitly created when creating the "base objects". FWIW this idea fails when you consider stuff such as circular foreign keys (and I suppose there are other, more common cases). If you really want something general you need to break those apart. (This is the explanation for the “break the loop” code in pg_dump I imagine) -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
2011/1/12 Florian Pflug : > I suggest you try to node-folding strategy and see how far it gets you. Good suggestion! :-) That's exactly what I've been trying to do, but failed miserably :-( I have written a thorough description of my problem and put it on my github: https://github.com/gluefinance/pov/tree/master/doc In the example, pg_depend.sql, we want to reach the following conclusions in an algorithmic way, only by using pg_depend as in-data: table t1, function f1, sequence s1 have no dependencies, other than the public schema table t2 depends on t1 and table t3 depends on f1 view v1 depends on t1 and view v2 depends on t2 view v3 depends on both v1 and v2 view v4 depends on v3 and f1 The topological tree automatically generated from the data in pg_depend is presented in pg_depend.dot, pg_depend.svg and pg_depend.png. The actual topological tree (possible order of creation) created manually is presented in pg_depend_actual.dot, pg_depend_actual.svg and pg_depend_actual.png. The automatically created objects, such as primary key indexes, constraints and triggers, have been ignored in this graph, as they are implicitly created when creating the "base objects". Objective: Define a general algorithm taking ONLY the pg_depend data as input, generating a valid topological directional graph, including at least the nodes in pg_depend_actual.dot, but might as well include all nodes, although it is not necessary, it's certainly won't hurt. It will be necessary to look not only at the nodes (objects) and edges (obj->refobj), but also the deptype for each edge. List of files: pg_depend.sql : A small but sufficient database model of tables, sequences, functions and views. pg_depend.dot : Digraph in DOT language (plaintext format), generated automatically only by using data from pg_data. pg_depend.png : PNG generated using GraphViz with pg_depend.dot as input pg_depend.svg : SVG generated using GraphViz with pg_depend.dot as input pg_depend_actual.dot : Digraph in DOT language (plaintext format), generated manually, shows the actual possible order of creation, only including the "base objects". pg_depend_actual.png : PNG generated using GraphViz with pg_depend_actual.dot as input pg_depend_actual.svg : SVG generated using GraphViz with pg_depend_actual.dot as input -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
On Jan11, 2011, at 23:55 , Joel Jacobson wrote: > 2011/1/11 Florian Pflug : >> Could you give an example of the kind of trouble you're experiencing trying >> to use a topological sort? > > Let's say you have a table t and a view v. > The view v is defined as select * from t; > If we put all objects in a tree, with the public schema as the root, > both v and t will directly under the root, but in reality, v cannot be > created before t. AFAICS, you get the following dependencies (apart from the obvious NORMAL dependencies from the pg_class entries of t and v on public) t (pg_class) <--[INTERNAL]-- t (pg_type) /°\ | [NORMAL] | _RETURN (pg_rewrite) || [NORMAL] [INTERNAL] || \./ \./ v (pg_class) <--[INTERNAL]-- v (pg_type) INTERNAL dependencies mark objects which spring into existence once the referenced (target in my diagram) object is created. You can thus fold a node I (the INTERNALly-depending object) into a node O (the object created by the user) if there is an INTERNAL dependency from I to O. The diagram then becomes v (pg_class) --[NORMAL]--> t (pg_class) Which correctly states that t must be created before v. > This is the reason why a normal topological sort doesn't work. > You have to look at the deptype and sort nodes having "internal" edges > between them differently. I suggest you try to node-folding strategy and see how far it gets you. > I guess it's time for plan B, sorting based on oid, no biggie, it will > work for my purpose, but it's damn ugly. That will probably crash and burn once the OIDs have wrapped around once. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
2011/1/11 Florian Pflug : > Could you give an example of the kind of trouble you're experiencing trying > to use a topological sort? Let's say you have a table t and a view v. The view v is defined as select * from t; If we put all objects in a tree, with the public schema as the root, both v and t will directly under the root, but in reality, v cannot be created before t. This is the reason why a normal topological sort doesn't work. You have to look at the deptype and sort nodes having "internal" edges between them differently. The pg_dump source code of course contains all the logic necessary to do the trick, but it's not that easy to follow. I guess it's time for plan B, sorting based on oid, no biggie, it will work for my purpose, but it's damn ugly. -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
On Jan11, 2011, at 16:54 , Joel Jacobson wrote: > Has anyone written a in-depth description on how to traverse the pg_depend > tree? > The 'a' and 'i' deptype really makes it hard to figure out the > dependency order, a topological sort does not work. Could you give an example of the kind of trouble you're experiencing trying to use a topological sort? best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
Joel Jacobson writes: > I need to figure out the order of creation of all objects, not just > the dependencies for a single object. In that case try pg_dump's pg_dump_sort.c. You will never get "the" order of creation of objects, because that isn't tracked; but you can find out what a safe order would be. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
2011/1/11 Tom Lane : > Try reading the code in src/backend/catalog/dependency.c. I've tried but failed to figure it out anyway. The focus in dependency.c is to find out dependencies of a given object. What I want to do is something slighly different. I need to figure out the order of creation of all objects, not just the dependencies for a single object. Basically, I want to do "order by oid", but since you cannot trust the oid order (like we did in pre-7.3), I need to get the sorted list using pg_depend somehow. I guess I could make findDependentObjects() callable from sql and call it for each and every object, but that's a quite big project, I was hoping it was possible to do it in plain sql, or at least only by relying on plpgsql/plperl. I've implemented tsort() in plperl, but that didn't really help me much, since you need to jump around in the tree when you encounter 'internal' and 'auto' nodes. My last resort is to sort by oid, but I really don't want to do that since it would render the entire solution unreliable and I would never feel comfortable using it in the production environment. This is the last component I need in order to complete the work on the pov-project http://www.github.com/gluefinance/pov I would highly appreciate your help, I feel a bit lame since I've spent over two days working on this. It's not difficult if you are allowed to build specialized queries for each class, but my goal is a general query only relying on pg_depend. -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_depend explained
Joel Jacobson writes: > Has anyone written a in-depth description on how to traverse the pg_depend > tree? Try reading the code in src/backend/catalog/dependency.c. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_depend explained
Has anyone written a in-depth description on how to traverse the pg_depend tree? The 'a' and 'i' deptype really makes it hard to figure out the dependency order, a topological sort does not work. My latest attempt involved trying to group by all objects connected to each other via deptype 'a' or 'i', and replacing all such nodes in the tree with the "source node", i.e. the node which according to the topological order could be created first. Am I on the right path, trying to "fuse" the internal/auto objects together, replacing them with the top most object in the tree? Or is there a simplier way to figure out the order in which objects can be created? I need a general solution, not a custom-made query for each regclass, which is quite trivial but feels far from bullet proof, I want something only relying on pg_depend, since it should be the safest method of them all. -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
RE: OID wraparound (was Re: [HACKERS] pg_depend)
> -Original Message- > Zeugswetter Andreas SB > > > As I mentioned already I'm implementing updatable cursors > > in ODBC and have half done it. If OIDs would be optional > > my trial loses its validity but I would never try another > > implementation. > > But how can you do that ? The oid index is only created by > the dba for specific tables, thus your update would do an update > with a where restriction, that is not indexed. > This would be darn slow, no ? > Please look at my another(previous ?) posting to pgsql-hackers. I would use both TIDs and OIDs, TIDs for fast access, OIDs for identification. > How about instead selecting the primary key and one of the tid's > (I never remember which, was it ctid ?) instead, so you can validate > when a row changed between the select and the update ? > Xmin is also available for row-versioning. But now I'm wondering if TID/xmin are guranteed to keep such characteriscs. Even Object IDentifier is about to lose the existence. Probably all-purpose application mustn't use system columns at all though I've never heard of it in other dbms-s. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Thursday 19 July 2001 06:08, you wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > I think it should be off on user tables by default, but kept on system > tables just for completeness. It could be added at table creation time > or from ALTER TABLEL ADD. It seems we just use them too much for system > stuff. pg_description is just one example. and what difference should it make, to have a few extra hundred or thousand OIDs used by system tables, when I insert daily some ten thousand records each using an OID for itself? Why not make OIDs 64 bit? Might slow down a little on legacy hardware, but in a couple of years we'll all run 64 bit hardware anyway. I believe that just using 64 bit would require the least changes to Postgres. Now, why would that look that obvious to me and yet I saw no mentioing of this in the recent postings. Surely it has been discussed before, so which is the point I miss or don't understand? I would need 64 bit sequences anyway, as it is predictable that our table for pathology results will run out of unique IDs in a couple of years. Horst ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian <[EMAIL PROTECTED]> writes: > What about moving the oid column out of the tuple header. This saves 4 > bytes in the header in cases where there is no oid on the table. No it doesn't --- at least not on machines where MAXALIGN is eight bytes. I don't think this is worth the trouble... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
AW: OID wraparound (was Re: [HACKERS] pg_depend)
> As I mentioned already I'm implementing updatable cursors > in ODBC and have half done it. If OIDs would be optional > my trial loses its validity but I would never try another > implementation. But how can you do that ? The oid index is only created by the dba for specific tables, thus your update would do an update with a where restriction, that is not indexed. This would be darn slow, no ? How about instead selecting the primary key and one of the tid's (I never remember which, was it ctid ?) instead, so you can validate when a row changed between the select and the update ? Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tom mentioned what should be stored in the OID system column if no oid's are in the table. He also mentioned that he doesn't want a variable-length tuple header so will always have an oid system column. What about moving the oid column out of the tuple header. This saves 4 bytes in the header in cases where there is no oid on the table. If they ask for an OID in a table, make it the first column of a table. Also, if they have asked for oid's on the table, odds are they want SELECT * to show it. Also, how about a GUC option that controls whether tables are created with OID's by default. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
On Fri, 20 Jul 2001, Hiroshi Inoue wrote: > Bill Studenmund wrote: > > > > "How does postgres know that the new table a is sufficiently like the old > > table that it should be used?" > > > > By making the reattachment automatic, you are saying that once we make an > > object of a given name and make objects depend on it, we can never have > > another object of the same name but different. Because PG is going to try > > to re-attach the dependants for you. > > > > That's different than current behavior, and strikes me as the system being > > overly helpful (a class of behavior I personally find very annoying). > > > > Please understand I like the idea of being ABLE to do this reattachment. I > > can see a lot of places where it would be VERY useful. > > It doesn't seem preferable that the default(unadorned) DROP > allows reattachement after the DROP. The default(unadorned) DROP > should be the same as DROP RESTRICT(or CASCADE because the current > behabior is halfway CASCADE?). How about adding another keyword > to allow reattachment after the DROP ? Hmmm... My preference is for the subsequent CREATE to indicate if reattach should happen or not. But I'm not sure if that would leave dangling depend entries around. > All depende(a?)nt objects must be re-complied after the > reattachment and the re-compilation would fail if the new table > isn't sufficiently like the old one. > > Anyway my opinion seems in a minority as usual. Only partly. I think everyone likes the idea of being able to reattach later, an idea you came up with. :-) Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> Could you use CTID instead of OID? > > > I am using both. > > TIDs for fast access and OIDs for identification. > > Unfortunately TIDs are transient and they aren't > > that reliable as for identification. > > Hmm ... within a transaction I think they'd be reliable enough, > but for long-term ID I agree they're not. What behavior do you > need exactly;do you need to be able to find the updated version > of a row you originally inserted? What I was about to do in the case e.g. UPDATE is the following. 1) UPDATE .. set .. where CTID = saved_ctid and OID = saved_oid; If one row was updated it's OK and return. 2) Otherwise something has changed and the update operation would fail. However the driver has to try to find the updated version of the row in case of keyset-driven cursors by the query SELECT CTID, .. from .. where CTID = currtid2(table_name, saved_ctid) and OID = saved_oid; If a row was found, the content of cursors' buffer is replaced and return. 3) If no row was found, the row may be deleted. Or we could issue another query SELECT CTID, .. from .. where OID = saved_oid; though the performance is doubtful. The OIDs are (mainly) to prevent updating the wrong records. > What would it take to use a > user-defined primary key instead of OID? Yes it could be. In fact M$ provides the ODBC cursor library in that way and we have used it(indirectly) for a long time. It's the reason why ODBC users don't complain about the non-existence of updatable cursors that often. Must I repeat the implementation ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
On Fri, Jul 20, 2001 at 08:45:05AM +0900, Hiroshi Inoue wrote: > > It doesn't seem preferable that the default(unadorned) DROP > allows reattachement after the DROP. The default(unadorned) DROP > should be the same as DROP RESTRICT(or CASCADE because the current > behabior is halfway CASCADE?). How about adding another keyword > to allow reattachment after the DROP ? > All depende(a?)nt objects must be re-complied after the > reattachment and the re-compilation would fail if the new table > isn't sufficiently like the old one. > > Anyway my opinion seems in a minority as usual. > How about making that functionality happen with ALTER REPLACE as Tom suggested? If I'm wanting to change an underlying table, how likely is it that I don't have the replacement ready right now? So, instead of: DROP WITH INTENT TO REPLACE CREATE it's just: ALTER REPLACE All nice and transactional: if the attempt to reattach one of the subordinate objects fails, you roll back to the old one. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_depend
Bill Studenmund wrote: > > On Thu, 19 Jul 2001, Hiroshi Inoue wrote: > > > > This step I disagree with. Well, I disagree with the automated aspect > of > > > the update. How does postgres know that the new table a is sufficiently > > > like the old table that it should be used? A way the DBA could say, "yeah, > > > restablish that," would be fine. > > > > > > > You could DROP a table with CASCADE or RESTRICT keyword if > > you hate the behavior. > > You didn't answer the question. :-) > > "How does postgres know that the new table a is sufficiently like the old > table that it should be used?" > > By making the reattachment automatic, you are saying that once we make an > object of a given name and make objects depend on it, we can never have > another object of the same name but different. Because PG is going to try > to re-attach the dependants for you. > > That's different than current behavior, and strikes me as the system being > overly helpful (a class of behavior I personally find very annoying). > > Please understand I like the idea of being ABLE to do this reattachment. I > can see a lot of places where it would be VERY useful. It doesn't seem preferable that the default(unadorned) DROP allows reattachement after the DROP. The default(unadorned) DROP should be the same as DROP RESTRICT(or CASCADE because the current behabior is halfway CASCADE?). How about adding another keyword to allow reattachment after the DROP ? All depende(a?)nt objects must be re-complied after the reattachment and the re-compilation would fail if the new table isn't sufficiently like the old one. Anyway my opinion seems in a minority as usual. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pg_depend
On Thu, 19 Jul 2001, Hiroshi Inoue wrote: > > This step I disagree with. Well, I disagree with the automated aspect of > > the update. How does postgres know that the new table a is sufficiently > > like the old table that it should be used? A way the DBA could say, "yeah, > > restablish that," would be fine. > > > > You could DROP a table with CASCADE or RESTRICT keyword if > you hate the behavior. You didn't answer the question. :-) "How does postgres know that the new table a is sufficiently like the old table that it should be used?" By making the reattachment automatic, you are saying that once we make an object of a given name and make objects depend on it, we can never have another object of the same name but different. Because PG is going to try to re-attach the dependants for you. That's different than current behavior, and strikes me as the system being overly helpful (a class of behavior I personally find very annoying). Please understand I like the idea of being ABLE to do this reattachment. I can see a lot of places where it would be VERY useful. My vote though is to just make reattachment a seperate step or something you flag, like in the CREATE TABLE, say attach me to everything wanting a table of this name. Make it something you have to indicate you want. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Philip Warner <[EMAIL PROTECTED]> writes:
> At 00:00 19/07/01 -0400, Tom Lane wrote:
>> INSERT INTO foo ... RETURNING x,y,z,...
> That would have been me; at the time we also talked about
> UPDATE...RETURNING and Jan proposed allowing UPDATE...RETURNING
> {[Old.|New.]Attr,...}
Hm. I'm less excited about UPDATE ... RETURNING since it would seem
that SELECT FOR UPDATE followed by UPDATE would get that job done
in a somewhat-less-nonstandard manner. But anyway ---
Thinking about this some more, it seems that it's straightforward enough
for a plain INSERT, but I don't understand what's supposed to happen if
the INSERT is replaced by an ON INSERT DO INSTEAD rule. The rule might
not contain an INSERT at all, or it might contain several INSERTs into
various tables with no simple relationship to the original. What then?
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: OID wraparound (was Re: [HACKERS] pg_depend)
J-P wrote: > > I need to create a new system table like pg_log to > > implement a replication scheme. The big problem is > how > > I could get an OID for it, a unique OID that is > > reserved for that table??? Hiroshi Inoue wrote: > > > Do you need the following ? > > visco=# select oid from pg_class where relname = > 'pg_log'; > oid > -- > 1269 > (1 row) > > I'm afraid of misunderstanding. Sorry my question was wrongly asked. What I need is a unique OID for my new system table that is reserved for that table? A new Id that is not used by anything else, and that will never be used. (The reference to pg_log was just to show the similarity of what I need). N.B. I can't just #select oid from pg_class and take one that is not there, since I don't know if the oid I choose will be used by something else in the system?? Thanks for your help, J-P ___ Do You Yahoo!? Get your free @yahoo.ca address at http://mail.yahoo.ca ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
> Yes, nowhere near, and yes. Sequence objects require disk I/O to > update; the OID counter essentially lives in shared memory, and can > be bumped for the price of a spinlock access. Sequences also cache values (32 afair) - ie one log record is required for 32 nextval-s. Sequence' data file is updated at checkpoint time, so - not so much IO. I really think that using sequences for system tables IDs would be good. Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Could you use CTID instead of OID? > I am using both. > TIDs for fast access and OIDs for identification. > Unfortunately TIDs are transient and they aren't > that reliable as for identification. Hmm ... within a transaction I think they'd be reliable enough, but for long-term ID I agree they're not. What behavior do you need exactly; do you need to be able to find the updated version of a row you originally inserted? What would it take to use a user-defined primary key instead of OID? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
At 12:37 18/07/01 -0400, Tom Lane wrote: >Philip Warner <[EMAIL PROTECTED]> writes: >> At 11:38 18/07/01 -0400, Tom Lane wrote: >>> I'd just make the dependency be from view_a to a and keep things >>> simple. What's so wrong with recompiling the view for *every* change >>> of the underlying table? > >> Not a problem for views, but when you get to constraints on large tables, >> re-evaluating all the constraints unnecessarily could be a nightmare, and >> especially frustrating when you just dropped an irrelevant attr. > >Huh? You seem to be thinking that we'd need to re-check the constraint >at each row of the table, but I don't see why we'd need to. I was just >envisioning re-parsing the constraint source text. I'm paranoid, but there could be a case for doing so, especially if we allow CHAR(n) to become CHAR(m) where m < n. Or any similar data-affecting field change. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
>>>Bruce Momjian said: [...] > > No, we won't, because OID wrap is an issue already for any long-uptime > > installation. (64-bit XIDs are not a real practical answer either, > > btw.) > > Have we had a wraparound yet? Just for the record, I had an OID overflow on production database (most middleware crashed mysteriously but no severe data loss) about a month ago. This was on 7.0.2 which probably had some bug ... preventing real wrap to happen. No new allocations (INSERTs that used autoincrementing sequences) were possible in most tables. Anyway, I had to dump/restore the database - several hours downtime. The database is not very big in size (around 10 GB in the data directory), but contains many objects (logs) and many objects are inserted/deleted from the database - in my opinion at not very high rate. Many tables are also created/dropped during processing. What is worrying is that this database lived about half a year only... In my opinion, making OIDs optional would help things very much. In my case, I don't need OIDs for log databases. Perhaps it would additionally help if OIDs are separately increasing for each database - not single counter for the entire PostgreSQL installation. Regards, Daniel ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > As I mentioned already I'm implementing updatable cursors > > in ODBC and have half done it. If OIDs would be optional > > my trial loses its validity but I would never try another > > implementation. > > Could you use CTID instead of OID? > I am using both. TIDs for fast access and OIDs for identification. Unfortunately TIDs are transient and they aren't that reliable as for identification. But the transience of TIDs are useful for row-versioning fortunately. The combination of OID and TID has been my plan since I introduced Tid scan. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
At 00:00 19/07/01 -0400, Tom Lane wrote:
>that someone (maybe Larry R? I forget now) proposed before:
>
> INSERT INTO foo ... RETURNING x,y,z,...
>
That would have been me; at the time we also talked about
UPDATE...RETURNING and Jan proposed allowing UPDATE...RETURNING
{[Old.|New.]Attr,...}
Needless to say, I'd love to see it implemented.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au |/ \|
|----
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tom Lane wrote: >Lamar Owen <[EMAIL PROTECTED]> writes: > >> >> > > > >Another possibility, given that any app using a feature like this is >nonportable anyway, is to extend the INSERT statement along the lines >that someone (maybe Larry R? I forget now) proposed before: > > INSERT INTO foo ... RETURNING x,y,z,... > >where x,y,z, etc are expressions in the variables of the inserted >tuple(s). This could be made to look like a SELECT at the protocol >level, which would mean that it wouldn't break client libraries or >require a protocol bump, and it's *way* more flexible than any >hardwired decision about what columns to return. It wouldn't have >any problem with multiple tuples inserted by an INSERT ... SELECT, >either. > This would be a good thing (tm). I use Oracle quite extensively as well as PG and Oracle's method of "RETURNING :avalue" is very good for returning values from newly inserted rows. There was some talk a while back about [not?] implementing variable binding. This seems to become very closely related to that. It would seem to solve the problem of having a unique identifier returned for inserts. I'm sure it would please quite a few people in the process, especially ones moving across from Oracle. (kill two birds with one stone) > > regards, tom lane > Ashley Cambrell ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Wednesday 18 July 2001 07:49 pm, Tom Lane wrote: > I don't think we should discourage use of OIDs quite as vigorously > as you propose ;-). Just playing devil's advocate. As I said, I am one who is using OID's in a client now but who is willing to forgo that feature for large-system stability. > All I want is to not expend OIDs on things that > have no need for one. That, together with clarifying exactly how > unique OIDs should be expected to be, seems to me that it will solve > 99% of the problem. 99% solved for 1% effort... The other 1% would take alot more effort. I think you're barking up the right tree, as usual, Tom. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Thursday 19 July 2001 12:00 am, Tom Lane wrote: > Lamar Owen <[EMAIL PROTECTED]> writes: > > However, the utility of INSERT returning a unique identifier to the > > inserted row needs to be addressed -- I would prefer it return the > Another possibility, given that any app using a feature like this is > nonportable anyway, is to extend the INSERT statement along the lines > that someone (maybe Larry R? I forget now) proposed before: > INSERT INTO foo ... RETURNING x,y,z,... > where x,y,z, etc are expressions in the variables of the inserted I like this one. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > As I mentioned already I'm implementing updatable cursors > in ODBC and have half done it. If OIDs would be optional > my trial loses its validity but I would never try another > implementation. Could you use CTID instead of OID? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen <[EMAIL PROTECTED]> writes: > However, the utility of INSERT returning a unique identifier to the > inserted row needs to be addressed -- I would prefer it return the > defined PRIMARY KEY value for the tuple just inserted, if a PRIMARY > KEY is defined. If no PRIMARY KEY is defined, return a unique > identifier (even a temporary one like the ctid) so that I have that > information for use later in the application. The utility of that > feature should not be underestimated. That's something that needs to be thought about, all right. I kinda like the idea of returning the ctid, because it is (a) very low overhead, which is nice for something that the client may not actually need, and (b) the tuple can be retrieved *very* quickly given a tid, much more so than was possible with OID. OTOH, if you want to use a tid you'd best use it right away, before someone else can update the row... The major problem with any change away from returning OID is that it'll break client libraries and apps. How much pain do we want to cause ourselves in that line? Certainly, to return anything besides/instead of OID we'd have to change the FE/BE protocol. IIRC, there are a number of other things pending that require protocol changes, so gathering them all together and updating the protocol isn't necessarily a bad thing. But I don't think we have time for it in the 7.2 cycle, unless we slip the schedule past the beta-by-end-of-August that I believe we're shooting for. Another possibility, given that any app using a feature like this is nonportable anyway, is to extend the INSERT statement along the lines that someone (maybe Larry R? I forget now) proposed before: INSERT INTO foo ... RETURNING x,y,z,... where x,y,z, etc are expressions in the variables of the inserted tuple(s). This could be made to look like a SELECT at the protocol level, which would mean that it wouldn't break client libraries or require a protocol bump, and it's *way* more flexible than any hardwired decision about what columns to return. It wouldn't have any problem with multiple tuples inserted by an INSERT ... SELECT, either. 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: OID wraparound (was Re: [HACKERS] pg_depend)
I wrote: > > Tom Lane wrote: > > > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > > I don't love current OIDs. However they have lived in PostgreSQL's > > > world too long and few people have pointed out that there's no magic > > > around OIDs. I agree to change OIDs to be per class but strongly > > > object to let OIDs optional. > > > > Uh ... what? I don't follow what you are proposing here. > > > > I couldn't think of the cases that we need database-wide > uniqueness. So the uniqueness of OIDs could be only within > a table. But I object to the option that tables could have > no OIDs. > It seems that I'm the only one who objects to optional OIDs as usual:-). IMHO OIDs are not for system but for users. OIDs have lived in PostgreSQL world from the first(???). Isn't it sufficiently long for users to believe that OIDs are unique (at least per table) ? As I mentioned already I'm implementing updatable cursors in ODBC and have half done it. If OIDs would be optional my trial loses its validity but I would never try another implementation. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pg_depend
Bill Studenmund wrote: > > On Wed, 18 Jul 2001, Hiroshi Inoue wrote: > > > Oops I made a mistake. > > Reference name is needed not an object name, > > i.e > > object relid > > object oid > > relerence relid > > reference oid > > reference name > > > > create table a (...); > > create view view_a as select .. from a; > > > > Then we have an pg_depend entry e.g. > > > > pg_class_relid > > oid of the view_a > > pg_class_relid > > oid of the table a > > 'a' the name of the table > > > > and so on. > > > > drop table a; (unadorned drop). > > > > Then the above entry would be changed to > > > > pg_class_relid(unchanged) > > oid of the view_s(unchagned) > > pg_class_relid(unchanged) > > InvalidOid > > 'a' the name of the table(unchanged) > > > > create table a (...); > > > > Then the pg_depend entry would be > > > > pg_class_relid(unchanged) > > oid of the view_s(unchagned) > > pg_class_relid(unchanged) > > the oid of the new table a > > 'a' the name of the table(unchanged) > > This step I disagree with. Well, I disagree with the automated aspect of > the update. How does postgres know that the new table a is sufficiently > like the old table that it should be used? A way the DBA could say, "yeah, > restablish that," would be fine. > You could DROP a table with CASCADE or RESTRICT keyword if you hate the behavior. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
>> What's wrong with 64-bit oids (except extra 4bytes)? > Portability, mostly. Oh, there's one other small problem: breaking the on-the-wire protocol. We send OIDs as column datatype identifiers, so an 8-byte-OID backend would not interoperate with clients that didn't also think OID is 8 bytes. Aside from client/server compatibility issues, that raises the portability ante a good deal --- not only your server machine has to have 'long long' support, but so do all your application environments. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > What about pg_log? It will easily become a huge file. Currently the > only solution is re-installing whole database, that is apparently > unacceptable for very big installation like 1TB. That's part of the XID wraparound issue, which is a separate discussion... but yes, I want to do something about that for 7.2 also. > So are we going to remove OID? No, only make it optional for user tables. > I see following in the SQL99 draft (not > sure it actually becomes a part of the SQL99 standard, though). Can we > implement the "Object identifier" without the current oid mechanism? As near as I can tell, SQL99's idea of OIDs has little to do with ours anyway. Note that they want to assign an OID to an "instance of an abstract data type". Thus, if you created a table with several columns each of which is one or another kind of ADT, then each column value would contain an associated OID --- the OID is assigned to each value, not to table rows. My suspicion is that SQL99-style OIDs would be implemented as a separate counter, and would be 8 bytes from the get-go. > What's wrong with 64-bit oids (except extra 4bytes)? Portability, mostly. I'm not ready to tell platforms without 'long long' that we don't support them at all anymore. If they don't have int8, or someday they don't have SQL99 OIDs, that's one thing, but zero functionality is something else. I'm also somewhat concerned about the speed price of widening Datum to 8 bytes on machines where that's not a well-supported datatype --- note that we'll pay for that almost everywhere, not only in Oid manipulations. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
From: Tom Lane <[EMAIL PROTECTED]> Subject: OID wraparound (was Re: [HACKERS] pg_depend) Date: Wed, 18 Jul 2001 13:52:45 -0400 Message-ID: <[EMAIL PROTECTED]> > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Yikes, I am not sure we are ready to make oids optional. > > We've discussed it enough, it's time to do it. I have an ulterior plan > here: I want 7.2 not to have any limitations that prevent it from being > used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed > now, or nearly. What about pg_log? It will easily become a huge file. Currently the only solution is re-installing whole database, that is apparently unacceptable for very big installation like 1TB. > The other stumbling blocks for continuous runs are OID > wraparound and XID wraparound. We've got unique indexes on OIDs for all > system catalogs that need them (we were short a couple as of 7.1, btw), > but OID wrap is still likely to lead to unwanted "duplicate key" > failures. So we still need a way to reduce the system's appetite for > OIDs. In a configuration where OIDs are used only where *necessary*, > it'd be a long time till wrap. I also intend to do something about XID > wrap next month... So are we going to remove OID? I see following in the SQL99 draft (not sure it actually becomes a part of the SQL99 standard, though). Can we implement the "Object identifier" without the current oid mechanism? - 4.10 Object identifier An object identifier OID is a value generated when an object is created, to give that object an immutable identity. It is unique in the known universe of objects that are instances of abstract data types, and is conceptually separate from the value, or state, of the instance. The object identifier type is described by an object identifier type descriptor. An object identifier type descriptor contains: - an indication that this is an object identifier type; and - the name of the abstract data type within which the object identifier type is used. The object identifier type is only used to define the OID pseudo- column implicitly defined in object ADTs within an ADT definition. ___ An OID literal exists for an object identifier type only if the associated abstract data type was defined WITH OID VISIBLE. The OID value is materialized as a character string with an implementation- defined length and character set SQL_TEXT. - >> Will we have cheap 64-bit oids by the time oid wraparound becomes an >> issue? > >No, we won't, because OID wrap is an issue already for any long-uptime >installation. (64-bit XIDs are not a real practical answer either, >btw.) What's wrong with 64-bit oids (except extra 4bytes)? -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > What do we do with other columns that need descriptions and don't have > > oid column. > >> > >> Like what? > > > Depends what other system tables you are intending to remove oid's for? > > Nothing that requires a description ;-) You are a sly one. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian <[EMAIL PROTECTED]> writes: > What do we do with other columns that need descriptions and don't have > oid column. Like what? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian <[EMAIL PROTECTED]> writes: > What do we do with other columns that need descriptions and don't have > oid column. >> >> Like what? > Depends what other system tables you are intending to remove oid's for? Nothing that requires a description ;-) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > I don't love current OIDs. However they have lived in PostgreSQL's > > world too long and few people have pointed out that there's no magic > > around OIDs. I agree to change OIDs to be per class but strongly > > object to let OIDs optional. > > Uh ... what? I don't follow what you are proposing here. > I couldn't think of the cases that we need database-wide uniqueness. So the uniqueness of OIDs could be only within a table. But I object to the option that tables could have no OIDs. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > What do we do with other columns that need descriptions and don't have > > oid column. > > Like what? Depends what other system tables you are intending to remove oid's for? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
"Ross J. Reedstrom" <[EMAIL PROTECTED]> writes: > On Wed, Jul 18, 2001 at 04:06:28PM -0400, Tom Lane wrote: >> My thought is to make OID generation optional on a per-table basis, and >> disable it on system tables that don't need unique OIDs. (OID would >> read as NULL on any row for which an OID wasn't generated.) > How about generalizing this to user defineable system attributes? OID > would just be a special case: it's really just a system 'serial' isn't it? Hmm. Of the existing system attributes, OID is the only one that's conceivably optional --- ctid,xmin,xmax,cmin,cmax are essential to the functioning of the system. (tableoid doesn't count here, since it's a "virtual" attribute that doesn't occupy any storage space on disk, and thus making it optional wouldn't buy anything.) So there's no gain to be seen in that direction. In the other direction, I have no desire to buy into adding creation timestamp or anything else in this go-round. Maybe sometime in the future. BTW, I'm not intending to change the on-disk format of tuple headers; if no OID is assigned to a row, the OID field will still be there, it'll just be 0. Given that it's only four bytes, it's probably not worth dealing with a variable header format to suppress the space usage. (On machines where MAXALIGN is 8 bytes, there likely wouldn't be any savings anyway.) I wouldn't much care for dealing with a variable tuple header format to support creation timestamp either, and that leads to the conclusion that it's just going to be a user field anyway. People who need it can do it with a trigger ... > I'm not sure the special behavior of returning NULL for oid on a table > without one is going to be useful: any client code that expects everything > to have an oid is unlikely to handle NULL better than an error. Well, I can see three possible choices: return NULL, return zero, or don't create an OID entry in pg_attribute at all for such a table (I *think* that would be sufficient to prevent people from accessing the OID column, but am not sure). Of these I'd think the first is least likely to break stuff. However, you might be right that breaking stuff is preferable to the possibility of an app that thinks it knows what it's doing causing major data lossage because it doesn't. 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: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian wrote: > > > > If you want to make oids optional on user tables, > > > we can vote on that. > > > > Let's vote. I'm proposing optional oids for 2-3 years, > > so you know how I'll vote -:) > > OK, we need to vote on whether Oid's are optional, and whether we can > have them not created by default. > I don't love current OIDs. However they have lived in PostgreSQL's world too long and few people have pointed out that there's no magic around OIDs. I agree to change OIDs to be per class but strongly object to let OIDs optional. It's a big pain for generic applications to lose OIDs. In fact I'm implementing updatable cursors in ODBC using OIDs and Tids. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> >> I meant we use them in many cases to link entries, and in > >> pg_description for descriptions and lots of other things > >> that may use them in the future for system table use. > > pg_description is a point I hadn't thought about --- it uses OIDs > to refer to pg_attribute entries. However, pg_description is pretty > broken in its assumptions about OIDs anyway. I'm inclined to change > it to be indexed by > > (object type ID, object OID, attributenumber) > > the same way that Philip proposed indexing pg_depend. Among other > things, that'd make it much cheaper to drop comments during a DROP > TABLE. You could just scan on (object type ID, object OID), and get > both the table and all its columns in a single indexscan search, > not one per column as happens now. Remember most pg_description comments are not on column but on functions and stuff. That attributenumber is not going to apply there. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen <[EMAIL PROTECTED]> writes: > Now for a question: OID creation seems to be a low-overhead task. Is the > creation of SERIAL PRIMARY KEY values as efficient? Or will we be shooting > ourselves in the performance foot if frequently-accessed system tables go > from OID usage to SERIAL PRIMARY KEY usage? Yes, nowhere near, and yes. Sequence objects require disk I/O to update; the OID counter essentially lives in shared memory, and can be bumped for the price of a spinlock access. I don't think we should discourage use of OIDs quite as vigorously as you propose ;-). All I want is to not expend OIDs on things that have no need for one. That, together with clarifying exactly how unique OIDs should be expected to be, seems to me that it will solve 99% of the problem. 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: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian <[EMAIL PROTECTED]> writes: > Remember most pg_description comments are not on column but on functions > and stuff. That attributenumber is not going to apply there. Sure, it'd just be zero for non-column items. 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: OID wraparound (was Re: [HACKERS] pg_depend)
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > I don't love current OIDs. However they have lived in PostgreSQL's > world too long and few people have pointed out that there's no magic > around OIDs. I agree to change OIDs to be per class but strongly > object to let OIDs optional. Uh ... what? I don't follow what you are proposing here. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Wed, Jul 18, 2001 at 04:06:28PM -0400, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Is the idea to make oid's optional, with them disabled by default on > > user tables? > > My thought is to make OID generation optional on a per-table basis, and > disable it on system tables that don't need unique OIDs. (OID would > read as NULL on any row for which an OID wasn't generated.) How about generalizing this to user defineable system attributes? OID would just be a special case: it's really just a system 'serial' isn't it? We occasionally get calls for other system type attributes that would be too expensive for every table, but would be useful for individual tables. One is creation_timestamp. Or this could be a route to bringing timetravel back in: start_date stop_date, anyone? > > It remains to be debated exactly how users should control the choice for > user tables, and which choice ought to be the default. I don't have a > strong opinion about that either way, and am prepared to hear > suggestions. Two ways come to mind: either special WITH options, at the end, or a new per attribute SYSTEM keyword: CREATE TABLE <...> WITH OIDS CREATE TABLE <...> WITH TIMETRAVEL CREATE TABLE <...> WITH DATESTAMP CREAT TABLE foo (oid oid SYSTEM, created timestamp SYSTEM DEFAULT CURRENT_TIMESTAMP, my_id serial, my_field text); So, basically it just creates the type and gives it a negative attnum. The 'oid system' case would need to be treated specially, hooking the oid up to the system wide counter. I'm not sure the special behavior of returning NULL for oid on a table without one is going to be useful: any client code that expects everything to have an oid is unlikely to handle NULL better than an error. In fact, in combination with the MS-Access compatability hack of '= NULL' as 'IS NULL', I see a potential great loss of data: SELECT oid,* from some_table; UPDATE some_table set field1=$field1, field2=$field2, <...> WHERE oid = $oid; if $oid is NULL ... There goes the entire table. Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
currval() could work nicely, but thats an additional query. Currently OID (in php among others) can be retrieved along with the insert response which is instantly retrievable. This makes for a very quick middleware enforced foreign key entry in other databases. Returning the entire primary key of the last row inserted without doing additional queries -- this is a known element which could be cached -- could be very useful in these situations. With tables requiring multi-key elements we do a second select asking for currval()s of the sequences. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> To: "Rod Taylor" <[EMAIL PROTECTED]> Cc: "Lamar Owen" <[EMAIL PROTECTED]>; "Tom Lane" <[EMAIL PROTECTED]>; "PostgreSQL-development" <[EMAIL PROTECTED]> Sent: Wednesday, July 18, 2001 5:06 PM Subject: Re: OID wraparound (was Re: [HACKERS] pg_depend) > > If OIDs are dropped a mechanism for retrieving the primary key of the > > last insert would be greatly appreciated. Heck, it would be useful > > now (rather than returning OID). > > > > I much prefer retrieving the sequence number after the insert than > > before insert where the insert uses it. Especially when trigger > > muckary is involved. > > Doesn't currval() work for your needs. > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > Remember most pg_description comments are not on column but on functions > > and stuff. That attributenumber is not going to apply there. > > Sure, it'd just be zero for non-column items. What do we do with other columns that need descriptions and don't have oid column. Make the attribute column mean something else? I just don't see a huge gain here and lots of confusion. User tables are a different story. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
>> I meant we use them in many cases to link entries, and in >> pg_description for descriptions and lots of other things >> that may use them in the future for system table use. pg_description is a point I hadn't thought about --- it uses OIDs to refer to pg_attribute entries. However, pg_description is pretty broken in its assumptions about OIDs anyway. I'm inclined to change it to be indexed by (object type ID, object OID, attributenumber) the same way that Philip proposed indexing pg_depend. Among other things, that'd make it much cheaper to drop comments during a DROP TABLE. You could just scan on (object type ID, object OID), and get both the table and all its columns in a single indexscan search, not one per column as happens now. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian <[EMAIL PROTECTED]> writes: > I think it should be off on user tables by default, but kept on system > tables just for completeness. Clearly, certain system tables *must* have OIDs --- pg_class, pg_type, pg_operator, etc --- because we use those OIDs to refer to objects. These are exactly the same tables that have unique indexes on OID. However, I don't see the point of consuming OIDs for entries in, say, pg_listener. The notion that it must have OIDs simply because it's a system table seems silly. pg_attribute is on the edge --- are table columns objects in their own right, deserving of a separate OID, or not? So far I don't see any really good reason why they should have one. Since the goal is to minimize OID consumption, not assigning OIDs to pg_attribute entries seems like a good idea. I don't think this is just a marginal hack. ISTM the main source of OID consumption for an up-and-running system (if it has no large user tables with OIDs) will be creation of temp tables. We can expend two OIDs per temp table (pg_class and pg_type), or we can expend N+9 for an N-column temp table (the seven system attributes plus the N user ones plus pg_class and pg_type). That's *at least* a 5x difference in steady-state rate of OID consumption. If that doesn't get your attention, it should. 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: OID wraparound (was Re: [HACKERS] pg_depend)
[trimmed cc:list] On Wednesday 18 July 2001 17:09, Bruce Momjian wrote: > OK, we need to vote on whether Oid's are optional, and whether we can > have them not created by default. [All the below IMHO] OID's should be optional. System tables that absolutely have to have OIDs may keep them. No new OID usage, period. Use some other unique primary key. Default user tables to no OIDs. Document other means by which rows that are otherwise identical can be made unique, for the purpose of expunging duplicates (ctids or whatever is appropriate). Allow a SET DEFAULT CREATE OIDS style option for those who just _must_ have OIDS -- and integrate with GUC. Document that OID wrap can occur, and that it can cause Bad Things to happen. Allow a CREATE TABLE WITH OIDS to supplement the above option setting. Now for a question: OID creation seems to be a low-overhead task. Is the creation of SERIAL PRIMARY KEY values as efficient? Or will we be shooting ourselves in the performance foot if frequently-accessed system tables go from OID usage to SERIAL PRIMARY KEY usage? > I meant we use them in many cases to link entries, and in pg_description > for descriptions and lots of other things that may use them in the > future for system table use. If I may be so bold: we discourage users from using OIDs as a SERIAL PRIMARY KEY, yet the system does it en masse. I say all that knowing full well that I am using OIDs in my own applications :-) I guess I'll just need to switch to proper SERIALs and PRIMARY KEYs. Of course, if I wanted to be stubborn, I'd just use the GUC option to enable OIDs system-wide by default However, the utility of INSERT returning a unique identifier to the inserted row needs to be addressed -- I would prefer it return the defined PRIMARY KEY value for the tuple just inserted, if a PRIMARY KEY is defined. If no PRIMARY KEY is defined, return a unique identifier (even a temporary one like the ctid) so that I have that information for use later in the application. The utility of that feature should not be underestimated. Such a return value would of course have to be returned as a tuple with all the necessary metadata to process the return value -- this is probably not a trivial change. Of course, I may be missing some essential usage of OID's and I reserve the right to be wrong. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Larry Rosenman <[EMAIL PROTECTED]> writes: > Maybe ctid needs to be documented better? I think it's documented about as well as OID is, actually --- see http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-syntax-columns.html which AFAIR is the only formal documentation of any of the system columns. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Didn't know about that one, at least from the reading of the docs... Thanks, You answered the question. I knew OID's weren't unique, but they are likely to be able to distinguish between 2 rows in the same table. Maybe ctid needs to be documented better? LER >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 7/18/01, 4:32:28 PM, Tom Lane <[EMAIL PROTECTED]> wrote regarding Re: OID wraparound (was Re: [HACKERS] pg_depend) : > Bruce Momjian <[EMAIL PROTECTED]> writes: > >> Also, without OID's, how do you fix EXACT duplicate records that happen > >> by accident? > > How about tid's? SELECT tid FROM tab1. > "SELECT ctid", actually, but that is still the fallback. (Actually > it always was --- OIDs aren't necessarily unique either, Larry.) > regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: OID wraparound (was Re: [HACKERS] pg_depend)
> OK, we need to vote on whether Oid's are optional, > and whether we can have them not created by default. Optional OIDs: YES No OIDs by default: YES > > > However, OID's keep our system tables together. > > > > How?! If we want to find function with oid X we query > > pg_proc, if we want to find table with oid Y we query > > pg_class - we always use oids in context of "class" > > to what an object belongs. This means that two tuples > > from different system tables could have same oid values > > and everything would work perfectly. > > I meant we use them in many cases to link entries, and in > pg_description for descriptions and lots of other things > that may use them in the future for system table use. So, add class' ID (uniq id from pg_class) when linking. Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian <[EMAIL PROTECTED]> writes: >> Also, without OID's, how do you fix EXACT duplicate records that happen >> by accident? > How about tid's? SELECT tid FROM tab1. "SELECT ctid", actually, but that is still the fallback. (Actually it always was --- OIDs aren't necessarily unique either, Larry.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> > If you want to make oids optional on user tables, > > we can vote on that. > > Let's vote. I'm proposing optional oids for 2-3 years, > so you know how I'll vote -:) OK, we need to vote on whether Oid's are optional, and whether we can have them not created by default. > > > However, OID's keep our system tables together. > > How?! If we want to find function with oid X we query > pg_proc, if we want to find table with oid Y we query > pg_class - we always use oids in context of "class" > to what an object belongs. This means that two tuples > from different system tables could have same oid values > and everything would work perfectly. I meant we use them in many cases to link entries, and in pg_description for descriptions and lots of other things that may use them in the future for system table use. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: OID wraparound (was Re: [HACKERS] pg_depend)
> If you want to make oids optional on user tables, > we can vote on that. Let's vote. I'm proposing optional oids for 2-3 years, so you know how I'll vote -:) > However, OID's keep our system tables together. How?! If we want to find function with oid X we query pg_proc, if we want to find table with oid Y we query pg_class - we always use oids in context of "class" to what an object belongs. This means that two tuples from different system tables could have same oid values and everything would work perfectly. There is no magic around OIDs. Vadim ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
> If OIDs are dropped a mechanism for retrieving the primary key of the > last insert would be greatly appreciated. Heck, it would be useful > now (rather than returning OID). > > I much prefer retrieving the sequence number after the insert than > before insert where the insert uses it. Especially when trigger > muckary is involved. Doesn't currval() work for your needs. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> Also, without OID's, how do you fix EXACT duplicate records that happen > by accident? How about tid's? SELECT tid FROM tab1. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > Is the idea to make oid's optional, with them disabled by default on > > user tables? > > My thought is to make OID generation optional on a per-table basis, and > disable it on system tables that don't need unique OIDs. (OID would > read as NULL on any row for which an OID wasn't generated.) > > It remains to be debated exactly how users should control the choice for > user tables, and which choice ought to be the default. I don't have a > strong opinion about that either way, and am prepared to hear > suggestions. I think it should be off on user tables by default, but kept on system tables just for completeness. It could be added at table creation time or from ALTER TABLEL ADD. It seems we just use them too much for system stuff. pg_description is just one example. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Also, without OID's, how do you fix EXACT duplicate records that happen by accident? LER >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 7/18/01, 3:46:30 PM, Rod Taylor <[EMAIL PROTECTED]> wrote regarding Re: OID wraparound (was Re: [HACKERS] pg_depend) : > If OIDs are dropped a mechanism for retrieving the primary key of the > last insert would be greatly appreciated. Heck, it would be useful > now (rather than returning OID). > I much prefer retrieving the sequence number after the insert than > before insert where the insert uses it. Especially when trigger > muckary is involved. > -- > Rod Taylor > Your eyes are weary from staring at the CRT. You feel sleepy. Notice > how restful it is to watch the cursor blink. Close your eyes. The > opinions stated above are yours. You cannot imagine why you ever felt > otherwise. > - Original Message - > From: "Tom Lane" <[EMAIL PROTECTED]> > To: "Lamar Owen" <[EMAIL PROTECTED]> > Cc: "Bruce Momjian" <[EMAIL PROTECTED]>; "PostgreSQL-development" > <[EMAIL PROTECTED]> > Sent: Wednesday, July 18, 2001 4:30 PM > Subject: Re: OID wraparound (was Re: [HACKERS] pg_depend) > > Lamar Owen <[EMAIL PROTECTED]> writes: > > > On Wednesday 18 July 2001 16:06, Tom Lane wrote: > > >> It remains to be debated exactly how users should control the > choice for > > >> user tables, and which choice ought to be the default. I don't > have a > > >> strong opinion about that either way, and am prepared to hear > > >> suggestions. > > > > > SET OIDGEN boolean for database-wide default policy. > > > CREATE TABLE WITH OIDS for individual tables? CREATE TABLE > WITHOUT OIDS? > > > > Something along that line, probably. > > > > > ?? Is this sort of thing addressed by any SQL standard (Thomas?)? > > > > OIDs aren't standard, so the standards are hardly likely to help us > > decide how they should work. > > > > I think the really critical choice here is how much backwards > > compatibility we want to keep. The most backwards-compatible way, > > obviously, is OIDs on by default and things work exactly as they > > do now. But if we were willing to bend things a little then some > > interesting possibilities open up. One thing I've been wondering > > about is whether an explicit WITH OIDS spec ought to cause automatic > > creation of a unique index on OID for that table. ISTM that any > > application that wants OIDs at all would want such an index... > > > > regards, tom lane > > > > ---(end of > broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to > [EMAIL PROTECTED] > > > ---(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 ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
On Wednesday 18 July 2001 16:06, Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > Is the idea to make oid's optional, with them disabled by default on > > user tables? > It remains to be debated exactly how users should control the choice for > user tables, and which choice ought to be the default. I don't have a > strong opinion about that either way, and am prepared to hear > suggestions. SET OIDGEN boolean for database-wide default policy. CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS? ?? Is this sort of thing addressed by any SQL standard (Thomas?)? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian <[EMAIL PROTECTED]> writes: > Is the idea to make oid's optional, with them disabled by default on > user tables? My thought is to make OID generation optional on a per-table basis, and disable it on system tables that don't need unique OIDs. (OID would read as NULL on any row for which an OID wasn't generated.) It remains to be debated exactly how users should control the choice for user tables, and which choice ought to be the default. I don't have a strong opinion about that either way, and am prepared to hear suggestions. 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: OID wraparound (was Re: [HACKERS] pg_depend)
If OIDs are dropped a mechanism for retrieving the primary key of the last insert would be greatly appreciated. Heck, it would be useful now (rather than returning OID). I much prefer retrieving the sequence number after the insert than before insert where the insert uses it. Especially when trigger muckary is involved. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Lamar Owen" <[EMAIL PROTECTED]> Cc: "Bruce Momjian" <[EMAIL PROTECTED]>; "PostgreSQL-development" <[EMAIL PROTECTED]> Sent: Wednesday, July 18, 2001 4:30 PM Subject: Re: OID wraparound (was Re: [HACKERS] pg_depend) > Lamar Owen <[EMAIL PROTECTED]> writes: > > On Wednesday 18 July 2001 16:06, Tom Lane wrote: > >> It remains to be debated exactly how users should control the choice for > >> user tables, and which choice ought to be the default. I don't have a > >> strong opinion about that either way, and am prepared to hear > >> suggestions. > > > SET OIDGEN boolean for database-wide default policy. > > CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS? > > Something along that line, probably. > > > ?? Is this sort of thing addressed by any SQL standard (Thomas?)? > > OIDs aren't standard, so the standards are hardly likely to help us > decide how they should work. > > I think the really critical choice here is how much backwards > compatibility we want to keep. The most backwards-compatible way, > obviously, is OIDs on by default and things work exactly as they > do now. But if we were willing to bend things a little then some > interesting possibilities open up. One thing I've been wondering > about is whether an explicit WITH OIDS spec ought to cause automatic > creation of a unique index on OID for that table. ISTM that any > application that wants OIDs at all would want such an index... > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen <[EMAIL PROTECTED]> writes: > On Wednesday 18 July 2001 16:06, Tom Lane wrote: >> It remains to be debated exactly how users should control the choice for >> user tables, and which choice ought to be the default. I don't have a >> strong opinion about that either way, and am prepared to hear >> suggestions. > SET OIDGEN boolean for database-wide default policy. > CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS? Something along that line, probably. > ?? Is this sort of thing addressed by any SQL standard (Thomas?)? OIDs aren't standard, so the standards are hardly likely to help us decide how they should work. I think the really critical choice here is how much backwards compatibility we want to keep. The most backwards-compatible way, obviously, is OIDs on by default and things work exactly as they do now. But if we were willing to bend things a little then some interesting possibilities open up. One thing I've been wondering about is whether an explicit WITH OIDS spec ought to cause automatic creation of a unique index on OID for that table. ISTM that any application that wants OIDs at all would want such an index... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> Bruce Momjian <[EMAIL PROTECTED]> writes: > > Yikes, I am not sure we are ready to make oids optional. > > We've discussed it enough, it's time to do it. I have an ulterior plan > here: I want 7.2 not to have any limitations that prevent it from being > used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed > now, or nearly. The other stumbling blocks for continuous runs are OID > wraparound and XID wraparound. We've got unique indexes on OIDs for all > system catalogs that need them (we were short a couple as of 7.1, btw), > but OID wrap is still likely to lead to unwanted "duplicate key" > failures. So we still need a way to reduce the system's appetite for > OIDs. In a configuration where OIDs are used only where *necessary*, > it'd be a long time till wrap. I also intend to do something about XID > wrap next month... If you want to make oids optional on user tables, we can vote on that. However, OID's keep our system tables together. Though we don't need them on every system table, it seems they should be on all system tables just for completeness. Are we really losing a significant amount of oids through system tables? > > Do we return unused oids back to the pool on backend exit yet? > > Since WAL, and that was never a fundamental answer anyway. > > > Will we have cheap 64-bit oids by the time oid wraparound becomes an > > issue? > > No, we won't, because OID wrap is an issue already for any long-uptime > installation. (64-bit XIDs are not a real practical answer either, > btw.) Have we had a wraparound yet? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
> Lamar Owen <[EMAIL PROTECTED]> writes: > > ... these two issues of ID wrap need to be addressed -- my gut feel is > > that the reports of OID/XID wrap are going to skyrocket within 6 months as > > bigger and bigger installations try out PostgreSQL/RHDB > > Yes, my thoughts exactly. We're trying to play in the big leagues now. > I don't believe we can put these problems off any longer. Is the idea to make oid's optional, with them disabled by default on user tables? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > I don't see any value in dropping oid from pg_attribute.
>
> Conservation of OIDs. Assigning an OID to every row of pg_attribute
> chews up lots of OIDs, for a table that should never be referenced by
> OID --- its primary key is (table OID, attribute number).
>
> Right now this isn't really significant, but if/when we have an option
> to suppress OID generation for user tables, I have every intention of
> applying it to a bunch of the system tables as well. pg_attribute is
> a prime candidate.
>
> ("When" probably means "next month", btw. This is on my 7.2 list...)
Yikes, I am not sure we are ready to make oids optional. System table
oid's seem like the last place to try and preserve oids. Do we return
unused oids back to the pool on backend exit yet? (I don't see it on
the TODO list.) That seems like a much more profitable place to start.
Will we have cheap 64-bit oids by the time oid wraparound becomes an
issue?
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Wednesday 18 July 2001 13:52, Tom Lane wrote: > here: I want 7.2 not to have any limitations that prevent it from being > used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed > now, or nearly. The other stumbling blocks for continuous runs are OID Go for it, Tom. After the posting the other day about the 200GB data per week data load, this _really_ needs to be done. It won't directly affect me, as my needs are a little more modest (just about anything looks modest compared to _that_ data load). Petty limitations such as these two need to go away, and soon -- we're getting used by big installations now. This isn't Stonebraker's research Postgres anymore. The 7.1 removal of previous limitations was nearly overdue -- and these two issues of ID wrap need to be addressed -- my gut feel is that the reports of OID/XID wrap are going to skyrocket within 6 months as bigger and bigger installations try out PostgreSQL/RHDB (fact is that many are going to try it out _because_ it has been relabeled by Red Hat). The MySQL/NuSphere articles illustrate that -- the NuSphere guy goes as far as saying that the support of _Red_Hat_ is what gives PG credibilitiy -- and, you have to admit, RH's adoption of PG does increase, in many circles, PG's credibility. Of course, PG has credibility with me for other reasons -- it was, IMHO, just a matter of time before Red Hat saw the PostgreSQL Light. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen <[EMAIL PROTECTED]> writes: > ... these two issues of ID wrap need to be addressed -- my gut feel is > that the reports of OID/XID wrap are going to skyrocket within 6 months as > bigger and bigger installations try out PostgreSQL/RHDB Yes, my thoughts exactly. We're trying to play in the big leagues now. I don't believe we can put these problems off any longer. 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: [HACKERS] pg_depend
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > Reference name is needed not an object name, > > Only if we want to support the notion that > drop-and-recreate-with-same-name > means that references from other objects should now apply to the new > object. I do not think that that's really a good idea, at least not > without a heck of a lot of compatibility checking. It'd be way too easy > to create cases where the properties of the new object do not match > what the referring object expects. > For example, we would process the following step to drop a column. select (all columns except a column) from a into b; drop table a; alter table b rename to a; But we would lose all relelvant objects. Though we may be able to solve this problem by implementing *drop column* properly, we couldn't solve this kind of problems at once. In fact neither *drop column* nor *cluster* is solved. We could always have (at least) the second best way by allowing drop-and-recreate-with-same-name revival. > The majority of the cases I've heard about where this would be useful > are for functions, and we could solve that a lot better with an ALTER > FUNCTION command that allows changing the function body (but not the > name, arguments, or result type). > > BTW, name alone is not a good enough referent for functions... you'd > have to store the argument types too. > ??? Isn't an entry pg_proc_relid the oid of the function pg_type_relid the oid of an argument type the name of the argument type made ? regards, Hiroshi Inoue ---(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: [HACKERS] pg_depend
Bruce Momjian <[EMAIL PROTECTED]> writes:
> I don't see any value in dropping oid from pg_attribute.
Conservation of OIDs. Assigning an OID to every row of pg_attribute
chews up lots of OIDs, for a table that should never be referenced by
OID --- its primary key is (table OID, attribute number).
Right now this isn't really significant, but if/when we have an option
to suppress OID generation for user tables, I have every intention of
applying it to a bunch of the system tables as well. pg_attribute is
a prime candidate.
("When" probably means "next month", btw. This is on my 7.2 list...)
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: [HACKERS] pg_depend
"Hiroshi Inoue" <[EMAIL PROTECTED]> writes: >> BTW, name alone is not a good enough referent for functions... you'd >> have to store the argument types too. > ??? Isn't an entry > pg_proc_relid > the oid of the function > pg_type_relid > the oid of an argument type > the name of the argument type > made ? That's the entry that was dropped, no? Given a pg_depend row pointing at a function named foo, with an OID that no longer exists, how will you tell which of the (possibly many) functions named foo is wanted? 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])
OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian <[EMAIL PROTECTED]> writes: > Yikes, I am not sure we are ready to make oids optional. We've discussed it enough, it's time to do it. I have an ulterior plan here: I want 7.2 not to have any limitations that prevent it from being used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed now, or nearly. The other stumbling blocks for continuous runs are OID wraparound and XID wraparound. We've got unique indexes on OIDs for all system catalogs that need them (we were short a couple as of 7.1, btw), but OID wrap is still likely to lead to unwanted "duplicate key" failures. So we still need a way to reduce the system's appetite for OIDs. In a configuration where OIDs are used only where *necessary*, it'd be a long time till wrap. I also intend to do something about XID wrap next month... > Do we return unused oids back to the pool on backend exit yet? Since WAL, and that was never a fundamental answer anyway. > Will we have cheap 64-bit oids by the time oid wraparound becomes an > issue? No, we won't, because OID wrap is an issue already for any long-uptime installation. (64-bit XIDs are not a real practical answer either, btw.) 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: [HACKERS] pg_depend
On Wed, 18 Jul 2001, Hiroshi Inoue wrote: > Oops I made a mistake. > Reference name is needed not an object name, > i.e > object relid > object oid > relerence relid > reference oid > reference name > > create table a (...); > create view view_a as select .. from a; > > Then we have an pg_depend entry e.g. > > pg_class_relid > oid of the view_a > pg_class_relid > oid of the table a > 'a' the name of the table > > and so on. > > drop table a; (unadorned drop). > > Then the above entry would be changed to > > pg_class_relid(unchanged) > oid of the view_s(unchagned) > pg_class_relid(unchanged) > InvalidOid > 'a' the name of the table(unchanged) > > create table a (...); > > Then the pg_depend entry would be > > pg_class_relid(unchanged) > oid of the view_s(unchagned) > pg_class_relid(unchanged) > the oid of the new table a > 'a' the name of the table(unchanged) This step I disagree with. Well, I disagree with the automated aspect of the update. How does postgres know that the new table a is sufficiently like the old table that it should be used? A way the DBA could say, "yeah, restablish that," would be fine. Which is better, a view which is broken as the table it was based off of was dropped (even though there's a table of the same name now) or a view which is broken because there is now a table whose name matches its old table's name, but has different columns (either names or types)? I'd say #1. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
> > > >Wouldn't that work simply by using the oid for the column in pg_attribute > >as the primary dependency, rather than the table itself, from pg_class? So, > >the dependency chain would be: > > > >view -> attribute -> table > > > >So your examples would 'just work', I think. > > > > True. We need to remember to store both sets of dependencies (used attrs as > well as the table dependency). TODO update with column labels: * Add pg_depend table for dependency recording; use sysrelid, oid, depend_sysrelid, depend_oid, name -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
Hiroshi Inoue <[EMAIL PROTECTED]> writes: > Reference name is needed not an object name, Only if we want to support the notion that drop-and-recreate-with-same-name means that references from other objects should now apply to the new object. I do not think that that's really a good idea, at least not without a heck of a lot of compatibility checking. It'd be way too easy to create cases where the properties of the new object do not match what the referring object expects. The majority of the cases I've heard about where this would be useful are for functions, and we could solve that a lot better with an ALTER FUNCTION command that allows changing the function body (but not the name, arguments, or result type). BTW, name alone is not a good enough referent for functions... you'd have to store the argument types too. 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: [HACKERS] pg_depend
Philip Warner <[EMAIL PROTECTED]> writes: > I think any deisgn needs to cater for attr dependencies. eg. I don't really see a need to recognize dependencies at finer than table level. I'd just make the dependency be from view_a to a and keep things simple. What's so wrong with recompiling the view for *every* change of the underlying table? We could support attr-level dependencies within the proposed pg_depend layout if we made pg_attribute one of the allowed object categories. However, I'd prefer not to make OID of pg_attribute rows be a primary key for that table (in the long run I'd like to not assign OIDs at all to pg_attribute, as well as other tables that don't need OIDs). So the better way to do it would be to make the pg_depend entries include attribute numbers. But I really think this is unnecessary complexity. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_depend
> Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > Reference name is needed not an object name, > > Only if we want to support the notion that drop-and-recreate-with-same-name > means that references from other objects should now apply to the new > object. I do not think that that's really a good idea, at least not > without a heck of a lot of compatibility checking. It'd be way too easy > to create cases where the properties of the new object do not match > what the referring object expects. > > The majority of the cases I've heard about where this would be useful > are for functions, and we could solve that a lot better with an ALTER > FUNCTION command that allows changing the function body (but not the > name, arguments, or result type). > > BTW, name alone is not a good enough referent for functions... you'd > have to store the argument types too. I assume the name was only for reference use so you could give the user an idea of what is missing. Clearly you don't use that to recreate anything, or I hope not. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
At 11:38 18/07/01 -0400, Tom Lane wrote: >Philip Warner <[EMAIL PROTECTED]> writes: >> I think any deisgn needs to cater for attr dependencies. eg. > >I don't really see a need to recognize dependencies at finer than table >level. I'd just make the dependency be from view_a to a and keep things >simple. What's so wrong with recompiling the view for *every* change >of the underlying table? > Not a problem for views, but when you get to constraints on large tables, re-evaluating all the constraints unnecessarily could be a nightmare, and especially frustrating when you just dropped an irrelevant attr. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(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: [HACKERS] pg_depend
> Philip Warner <[EMAIL PROTECTED]> writes: > > I think any deisgn needs to cater for attr dependencies. eg. > > I don't really see a need to recognize dependencies at finer than table > level. I'd just make the dependency be from view_a to a and keep things > simple. What's so wrong with recompiling the view for *every* change > of the underlying table? What about other objects. Foreign keys? Serial? > We could support attr-level dependencies within the proposed pg_depend > layout if we made pg_attribute one of the allowed object categories. > However, I'd prefer not to make OID of pg_attribute rows be a primary > key for that table (in the long run I'd like to not assign OIDs at all > to pg_attribute, as well as other tables that don't need OIDs). So the > better way to do it would be to make the pg_depend entries include > attribute numbers. But I really think this is unnecessary complexity. I liked the pg_attribute references for some uses. I agree doing that for a view seems overly complex. I don't see any value in dropping oid from pg_attribute. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pg_depend
Philip Warner <[EMAIL PROTECTED]> writes: > At 11:38 18/07/01 -0400, Tom Lane wrote: >> I'd just make the dependency be from view_a to a and keep things >> simple. What's so wrong with recompiling the view for *every* change >> of the underlying table? > Not a problem for views, but when you get to constraints on large tables, > re-evaluating all the constraints unnecessarily could be a nightmare, and > especially frustrating when you just dropped an irrelevant attr. Huh? You seem to be thinking that we'd need to re-check the constraint at each row of the table, but I don't see why we'd need to. I was just envisioning re-parsing the constraint source text. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
