[HACKERS] XLOG_BLCKSZ vs. wal_buffers table
I would have gotten this out sooner but I'm having trouble with our infrastructure. Here's a link to a table of data I've started putting together regarding XLOG_BLCKSZ and wal_buffers on a 4-way Opteron system: http://developer.osdl.org/markw/pgsql/xlog_blcksz.html There are a couple of holes in the table but I think it shows enough evidence to say that with dbt2 having a larger XLOG_BLCKSZ improves the overall throughput of the test. I'm planning on continuing to increase XLOG_BLCKSZ and wal_buffers to determine when the throughput starts to level out or drop off, and then start experimenting with varying BLCKSZ. Let me know if there are other things that would be more interesting to experiment with first. Mark ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] RELKIND_SPECIAL
How about we remove RELKIND_SPECIAL? It was there only to support the XactLockTable hack, but we don't need that anymore. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Is a SERIAL column a "black box", or not?
[EMAIL PROTECTED] wrote: > On Sun, Apr 30, 2006 at 09:14:53AM -0700, Mark Dilger wrote: > >>Tom Lane wrote: >> >>>1. A serial column is a "black box" that you're not supposed to muck with >>>the innards of. This philosophy leads to the proposal that we disallow >>>modifying the column default expression of a serial column, and will >>>ultimately lead to thoughts like trying to hide the associated sequence >>>from direct access at all. >> >>It would be madness to prevent people from accessing the associated sequence. >>Assume the following schema: >> >> CREATE TABLE a (a_id SERIAL NOT NULL UNIQUE, ...); >> CREATE TABLE b (a_fk INTEGER REFERENCES a(a_id), ...); >> >>Now, if I need to insert into both tables a and b, how do I do it? After >>inserting into table a, if I can't access the sequence to get currval, I'll >>need >>to do a select against the table to find the row that I just inserted (which >>could be slow), and if the columns other than a_id do not uniquely identify a >>single row, then I can't do this at all. > > > Not madness. Just evidence of another problem, which is where the insert > that returns results comes in... That might help in the above situation but seriously restricts the way in which a user can organize their code. Personally, I don't use the currval solution above, but rather call nextval first, cache the answer, and use it for both the insertion in table a and in table b. If I don't get the value from the sequence until the insertion is performed on table a, I have to structure my code for that. Lots of people might have to rework their code to handle such a change. Of course, you can argue that if I don't like this I should skip using SERIAL and just explicitly use sequences. But the person coding against the schema may not be the same person who defined it. (And yes, I stopped using SERIAL in any schema I define a long time ago -- but I still run into it.) mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is a SERIAL column a "black box", or not?
I strongly agree with #2. The case at hand is where someone wants a serial column with different defaults (wraparound, min, max) than the standard serial. To achieve this an alter sequence is all that is necessary. If it were not possible to do this so simply, then the user would have to do #2 by hand. This is not hard for experienced users but leaves out the middle group--just past beginners. In general using our own tools to implement things such as sequences for serials and rules for views is a postgres strength. The dependencies seem to bear a closer look though. A drop table cascade should probably drop the sequence. I think a link between a sequence and a column is necessary. But it should be independent of names, etc. I'm not sure how we mark those dependencies now. Also permissions needs a closer look from the discussion that follows. I don't have strong opinions on that issue. --elein On Sat, Apr 29, 2006 at 05:54:19PM -0400, Tom Lane wrote: > In some recent activity on the patches list about responding to bug #2073, > http://archives.postgresql.org/pgsql-bugs/2005-11/msg00303.php > we've been discussing various possible tweaks to the behavior of dropping > or modifying a serial column. The hacks involved with SERIAL seem to me > to be getting uglier and uglier, and I think it's time to take a step > back and consider what we really want SERIAL to act like. > > It seems to me there are two basic philosophies at war here: > > 1. A serial column is a "black box" that you're not supposed to muck with > the innards of. This philosophy leads to the proposal that we disallow > modifying the column default expression of a serial column, and will > ultimately lead to thoughts like trying to hide the associated sequence > from direct access at all. > > 2. A serial declaration is just a "macro" for setting up a sequence and a > column default expression. This was the original viewpoint and indeed is > still what it says in the documentation: > http://developer.postgresql.org/docs/postgres/datatype-numeric.html#DATATYPE-SERIAL > This is nice and simple and easy to understand, but it leads to > usually-undesirable behaviors like having the sequence still be there if > the column is dropped. > > We started with #2 and have been moving slowly towards #1, but I think > there's a limit to how far we want to go in that direction. A black box > approach isn't especially user-friendly in my opinion; it's not solving > any problems, it's just refusing to deal with the implications of ALTER > TABLE and friends. What's more, the further we go in that direction the > more problems we'll have in pg_dump. We've already got issues there; > for example, if someone renames a serial's sequence or tweaks its > sequence parameters, this will not be preserved by dump/restore. > > I'm wondering if we shouldn't reverse this trend and try to go back to > a fairly pure version of philosophy #2. It'd certainly make pg_dump's > life a lot easier if it could dump a serial sequence as just an ordinary > sequence, instead of having to make sure it's created via SERIAL. > > One original argument for putting in a hidden dependency centered around > the fact that if you dropped the sequence, you'd break the column > default. But we have a much better answer to that as of PG 8.1: the > nextval() invocation is itself dependent on the sequence by means of the > regclass-literal mechanism. We don't need the extra dependency to prevent > that. > > The other concern the hidden dependency addresses is the idea that the > sequence ought to be silently dropped if the table (or just the column) > is dropped. I wonder though if that behavior is really worth the > increasing contortions we're going through to try to make things work > conveniently/transparently in other respects. We're buying simplicity > for one case at the cost of tremendous complication for other cases. > > In short, I think there's a reasonably good case to be made for losing the > hidden dependency and re-adopting the viewpoint that saying SERIAL is > *exactly* the same as making a sequence and then making a default > expression that uses the sequence. Nothing behind the curtain. > > Comments, other opinions? > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Sun, Apr 30, 2006 at 09:14:53AM -0700, Mark Dilger wrote: > Tom Lane wrote: > > 1. A serial column is a "black box" that you're not supposed to muck with > > the innards of. This philosophy leads to the proposal that we disallow > > modifying the column default expression of a serial column, and will > > ultimately lead to thoughts like trying to hide the associated sequence > > from direct access at all. > > It would be madness to prevent people from accessing the associated sequence. > Assume the following schema: > >CREATE TABLE a (a_id SERIAL NOT NULL UNIQUE, ...); >CREATE TABLE b (a_fk INTEGER REFERENCES a(a_id), ...); > > Now, if I need to insert into both tables a and b, how do I do it? After > inserting into table a, if I can't access the sequence to get currval, I'll > need > to do a select against the table to find the row that I just inserted (which > could be slow), and if the columns other than a_id do not uniquely identify a > single row, then I can't do this at all. Not madness. Just evidence of another problem, which is where the insert that returns results comes in... Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Altering view ownership doesn't work ...
Martijn van Oosterhout writes: > On Sun, Apr 30, 2006 at 12:34:42PM -0400, Tom Lane wrote: >> 2. Run setRuleCheckAsUser during rule load rather than rule store. > FWIW, I think #2 is better also. Actually, I'm sitting here realizing the problem is more complicated than I thought :-(. The spanner in the works is the existence of the RULE privilege --- a table owner can grant someone else the right to add rules to his table. As things currently work, when the someone else does so, it's *his* OID not the table owner's that gets put into the rule's checkAsUser fields. Thus for example the someone else could add a logging rule that makes entries into a table that the actual table owner has no permissions for. Whether or not you consider that sort of thing useful, it would certainly be bad to use the table owner's OID for such permission checks, because then granting RULE privilege on any table would be tantamount to handing over every permission the table owner has --- the grantee would be able to install arbitrary SQL to be executed as the table owner. So really the RULE privilege only makes sense if a rule is considered to be a separate object with separate ownership. So it seems we either have to abandon the separate RULE privilege (and just say that only table owners can install rules, and the rules are always executed as though by the current owner), or we have to promote rules to be fully separately owned objects. The latter will be a horrid mess, in particular it will break existing dump files that just ALTER the table's owner and don't go through altering ownership of individual rules. (No, we can't have ALTER TABLE OWNER automatically recurse to the individual rules, that'd just create the same Trojan-horse situation where a malicious rule now has the privileges it didn't have to start with.) I'm inclined to think that the best choice is to drop the separate RULE privilege. It's an interesting feature but I gauge its actual usefulness by the fact that I didn't even realize it worked like that. regards, tom lane ---(end of broadcast)--- TIP 1: 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] Altering view ownership doesn't work ...
On Sun, Apr 30, 2006 at 12:34:42PM -0400, Tom Lane wrote: > 2. Run setRuleCheckAsUser during rule load rather than rule store. > > #2 is a lot simpler, and would fix the problem for existing broken rules > whereas #1 would not, so I'm kind of inclined to go with that. I doubt > there'd be any meaningful performance hit --- parsing the stored form > of a rule is relatively expensive anyway, so we cache the results. FWIW, I think #2 is better also. It's the easiest way to ensure the correct result and the performence isn't enough of a problem to worry about doing it a different way. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[HACKERS] Altering view ownership doesn't work ...
... because nowhere does it update the "checkAsUser" fields in the view's query to be the OID of the new owner. This means that permission checks about whether the view can access its underlying tables will still be done as the old owner. An example: regression=# create user u1; CREATE ROLE regression=# create user u2; CREATE ROLE regression=# \c - u1 You are now connected to database "regression" as user "u1". regression=> create table t1(f1 int); CREATE TABLE regression=> create view v1 as select * from t1; CREATE VIEW regression=> grant select on v1 to u2; GRANT -- at this point u2 can select from v1 but not directly from t1 regression=> \c - postgres You are now connected to database "regression" as user "postgres". regression=# alter table v1 owner to u2; ALTER TABLE regression=# \c - u2 You are now connected to database "regression" as user "u2". regression=> select * from v1; f1 (0 rows) -- this is WRONG, u2 should not have any ability to select from t1 The same problem applies to all rules, really, not only a view's ON SELECT rule. This is particularly bad because pg_dump is relying heavily on ALTER OWNER these days. After a dump/restore, it is likely that every view's "original owner" will be a superuser, and thus that all permission checking is effectively disabled for accesses from views. It wouldn't be too much of a stretch to call that a security loophole. I can think of two basic ways to fix this: 1. Add a bunch of code to ALTER OWNER to update every rule attached to the target table. 2. Run setRuleCheckAsUser during rule load rather than rule store. #2 is a lot simpler, and would fix the problem for existing broken rules whereas #1 would not, so I'm kind of inclined to go with that. I doubt there'd be any meaningful performance hit --- parsing the stored form of a rule is relatively expensive anyway, so we cache the results. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Tom Lane wrote: > 1. A serial column is a "black box" that you're not supposed to muck with > the innards of. This philosophy leads to the proposal that we disallow > modifying the column default expression of a serial column, and will > ultimately lead to thoughts like trying to hide the associated sequence > from direct access at all. It would be madness to prevent people from accessing the associated sequence. Assume the following schema: CREATE TABLE a (a_id SERIAL NOT NULL UNIQUE, ...); CREATE TABLE b (a_fk INTEGER REFERENCES a(a_id), ...); Now, if I need to insert into both tables a and b, how do I do it? After inserting into table a, if I can't access the sequence to get currval, I'll need to do a select against the table to find the row that I just inserted (which could be slow), and if the columns other than a_id do not uniquely identify a single row, then I can't do this at all. mark ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Sun, Apr 30, 2006 at 12:28:50 +0200, > > Since a real stumbling block with the macro approach seems to be the > granting of permissions maybe we should work on that problem. For > example, making SERIAL be a macro that expands to: > > id integer default nextval(sequence) SECURITY DEFINER, > > Which would mean that the default expression would be executed as the > creator of the table, thus obviating the need to grant explicit > permission to the sequence. I suggested a long time ago that default expressions should always be executed as the owner of the table. This got shot down, but I don't remember if it was because people thought the idea was bad in itself or if it was the work involved (which I wasn't in a position to do). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Constraint Exclusion + Joins?
On Fri, 28 Apr 2006, Brandon Black wrote: I dug around in CVS to have a look for this, and I did eventually find it (well, I found the corresponding docs patch that removed the note about not working for joins). I see it's in MAIN but not in 8_1_STABLE. Does that mean it's headed for 8.2.x when that comes about? (Sorry, I'm not terribly familiar with how you guys handle all of this). Yes. - Heikki ---(end of broadcast)--- TIP 1: 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] Is a SERIAL column a "black box", or not?
On Sun, Apr 30, 2006 at 11:06:05AM +0200, Magnus Hagander wrote: > If it's not obvious yet :-P, I'd be in favour of having SERIAL as > black-box as possible, and then just use manual CREATE SEQUENCE and > DEFAULT nextval() for when you need a more advanced case. But that's as > seen from a user perspective, without regard for backend complexity. That's where I sit as well. SERIAL as a macro has no value to me. I'd rather write it out in full, and make it obvious to the caller, what I'm doing. This way, I get to choose the sequence name instead of having it generated for me, and the GRANT expression makes more sense. If SERIAL generated an 'anonymous' SEQUENCE, that was a real black box, that had the same permissions as the table, I'd be tempted to use it again. I also see the db_dump example as proving more that the box isn't black enough, than proving that the black box approach is wrong. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 1: 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] Is a SERIAL column a "black box", or not?
On Sun, Apr 30, 2006 at 01:42:37PM +0300, Hannu Krosing wrote: > Ühel kenal päeval, L, 2006-04-29 kell 19:41, kirjutas > [EMAIL PROTECTED]: > > On Sat, Apr 29, 2006 at 05:54:19PM -0400, Tom Lane wrote: > > > In short, I think there's a reasonably good case to be made for losing the > > > hidden dependency and re-adopting the viewpoint that saying SERIAL is > > > *exactly* the same as making a sequence and then making a default > > > expression that uses the sequence. Nothing behind the curtain. > > > > > > Comments, other opinions? > > I find it user-unfriendly that I must grant select/update to the > > SERIAL, separate than from the table. I don't really see anything > > friendly about treating the object as separate. > just define nextval() as SECURITY DEFINER If I understand correctly - I think that hides the problem, rather than solving it. :-) Shouldn't the SERIAL have the same permissions as the TABLE in the general case? SECURITY DEFINER would give everybody full access? > > I do see the benefits with regard to simplified implementation, and > > flexibility. > > As a compromise, I could see either choice being correct. I don't > > see either direction as being both user friendly and simple. > You can be user friendly and simple only if the user wants to do simple > things, or if you can exactly predict what a user wants, else you have > to grant some power to the user, and that involves complexity or at > least a learning curve. Yes. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 1: 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] Finding the correct type
Martijn van Oosterhout wrote: On Sun, Apr 30, 2006 at 12:50:23PM +0200, Thomas Hallgren wrote: I'm writing a UDT that takes a varchar argument that represents the name of a type. The caller may optionally qualify with a namespace, i.e. "pg_catalog.varchar", or "public.address". Is there a c-function somewhere that will return the pg_type that corresponds to the name (with respect to the current setting of search_path in case the name is unqualified)? If you want it as a C string, something like format_type_be() would suffice. Not it's designed for use in error messages so it makes no particular to clean up after itself. Another possibility is the output function for the regtype type, ie regtypeout. Hope this helps, Well, regtypeout led me to regtypein which in turn led me to parseTypeString which seems to be exactly what I want. Thanks, Thomas Hallgren ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Finding the correct type
On Sun, Apr 30, 2006 at 12:50:23PM +0200, Thomas Hallgren wrote: > I'm writing a UDT that takes a varchar argument that represents the name > of a type. The caller may optionally qualify with a namespace, i.e. > "pg_catalog.varchar", or "public.address". Is there a c-function > somewhere that will return the pg_type that corresponds to the name > (with respect to the current setting of search_path in case the name is > unqualified)? If you want it as a C string, something like format_type_be() would suffice. Not it's designed for use in error messages so it makes no particular to clean up after itself. Another possibility is the output function for the regtype type, ie regtypeout. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[HACKERS] Finding the correct type
I'm writing a UDT that takes a varchar argument that represents the name of a type. The caller may optionally qualify with a namespace, i.e. "pg_catalog.varchar", or "public.address". Is there a c-function somewhere that will return the pg_type that corresponds to the name (with respect to the current setting of search_path in case the name is unqualified)? Regards, Thomas Hallgren ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Ühel kenal päeval, L, 2006-04-29 kell 19:41, kirjutas [EMAIL PROTECTED]: > On Sat, Apr 29, 2006 at 05:54:19PM -0400, Tom Lane wrote: > > In short, I think there's a reasonably good case to be made for losing the > > hidden dependency and re-adopting the viewpoint that saying SERIAL is > > *exactly* the same as making a sequence and then making a default > > expression that uses the sequence. Nothing behind the curtain. > > > > Comments, other opinions? > > I find it user-unfriendly that I must grant select/update to the > SERIAL, separate than from the table. I don't really see anything > friendly about treating the object as separate. just define nextval() as SECURITY DEFINER > I do see the benefits with regard to simplified implementation, and > flexibility. > > As a compromise, I could see either choice being correct. I don't > see either direction as being both user friendly and simple. You can be user friendly and simple only if the user wants to do simple things, or if you can exactly predict what a user wants, else you have to grant some power to the user, and that involves complexity or at least a learning curve. - Hannu ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Rod Taylor wrote: If SERIAL is going to be kept long term, then it should be the macro version so it doesn't appear too duplicated. I concur with this. But to really break out from the current middle ground, you must implement the IDENTITY and also document the SERIAL macro as deprecated. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 1: 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] Is a SERIAL column a "black box", or not?
On Sat, Apr 29, 2006 at 05:54:19PM -0400, Tom Lane wrote: > In some recent activity on the patches list about responding to bug #2073, > http://archives.postgresql.org/pgsql-bugs/2005-11/msg00303.php > we've been discussing various possible tweaks to the behavior of dropping > or modifying a serial column. The hacks involved with SERIAL seem to me > to be getting uglier and uglier, and I think it's time to take a step > back and consider what we really want SERIAL to act like. > > It seems to me there are two basic philosophies at war here: Since a real stumbling block with the macro approach seems to be the granting of permissions maybe we should work on that problem. For example, making SERIAL be a macro that expands to: id integer default nextval(sequence) SECURITY DEFINER, Which would mean that the default expression would be executed as the creator of the table, thus obviating the need to grant explicit permission to the sequence. If you wanted to be tricky you could also add something like: ON DROP CASCADE SEQUENCE sequence This pretty much turns default expressions into actual objects. I don't know if we want to do that. That would imply creating a CREATE DEFAULT command, which is probably going too far (though it would be nice and easy for pg_dump). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Tom Lane wrote: In short, I think there's a reasonably good case to be made for losing the hidden dependency and re-adopting the viewpoint that saying SERIAL is *exactly* the same as making a sequence and then making a default expression that uses the sequence. Nothing behind the curtain. I speak more as a user than a hacker, but I do still lurk here ;) The way sequences are handled is imho one of the strongest features. The possiblity to query nextval is bordering on divine. I have however stopped using serials for anything else than quick mockup examples. The work of defining the sequence itself and setting acl's is imho trivial compared to consistency. I would actually suggest throwing a warning, that sequences are the proper way of doing it when people use serials - maybe even mark serial-types as obsolete in the docs. I strongly subscribe to the principle of least astonishment, and that means either pure sequences, a mysqlesqe auto_increment or both - but I fail to see, how the "macro"thing serial will ever work that way. It goes without saying, that I dislike auto_increment. Svenne smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] Is a SERIAL column a "black box", or not?
On Sat, 2006-04-29 at 23:15 -0400, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > Do both. Return SERIAL to being a macro and implement the SQL IDENTITY > > construct as the black box version. > > Doesn't SQL IDENTITY have a number of properties that are significantly > different from serial/nextval? I wasn't really volunteering to > implement a large new feature to make this happen ;-) Yes. Including a few really nice properties and a really ugly workaround. I didn't mean to imply that you should write it. I just meant that the spec already has an automatic sequence generator which is black-box. If SERIAL is going to be kept long term, then it should be the macro version so it doesn't appear too duplicated. > Also, I'm not sure how "black boxy" it can be without buying right back > into the pg_dump problems. pg_dump has to be able to see what's inside, > I think. Not sure which pg_dump problem you're referring to. A fully black box generator would completely hide the column default and sequence. Pg_dump and users can create and modify foreign keys without knowledge of the trigger implementation, the same would be true here. For the spec, the ugly workaround is "OVERRIDING SYSTEM VALUE" which allows a table owner to override the ALWAYS GENERATE designation -- essentially the same as a trigger bypass switch for bulk data loads. -- ---(end of broadcast)--- TIP 1: 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] Is a SERIAL column a "black box", or not?
> We started with #2 and have been moving slowly towards #1, > but I think there's a limit to how far we want to go in that > direction. A black box approach isn't especially > user-friendly in my opinion; it's not solving any problems, > it's just refusing to deal with the implications of ALTER > TABLE and friends. I think it's a matter of user-friendliness for *who*. A black box would definitly be a lot more user-friendly for a beginner, or someone who really doesn't care for more than just an auto-numbering column (which I'm sure is a lot of cases). For example, I've lost count of the number of times I've had to explain to people "yes, I know you just created a table with a column, but when you need to GRANT permissions you need to do it twice - once for the column and once for the sequence you didn't know you created". I don't recall any of these cases ending with "hey, what a handy feature that I can tweak the sequence independently". For an expert user it's certainly handy, though. > What's more, the further we go in that > direction the more problems we'll have in pg_dump. We've > already got issues there; for example, if someone renames a > serial's sequence or tweaks its sequence parameters, this > will not be preserved by dump/restore. If it was a "proper black box", that wouldn't happen, since there would be no way to make those changes, right? So that argument would really be helped in either direction, with the problem mainly showing in the "middle ground" where we are now. > The other concern the hidden dependency addresses is the idea > that the sequence ought to be silently dropped if the table > (or just the column) is dropped. I wonder though if that > behavior is really worth the increasing contortions we're > going through to try to make things work > conveniently/transparently in other respects. We're buying > simplicity for one case at the cost of tremendous > complication for other cases. I bet loads of databases would be filled with no-longer-used sequences in this case. But that may not really be a problem, since they don't exactly occupy loads of space when they just sit there... > In short, I think there's a reasonably good case to be made > for losing the hidden dependency and re-adopting the > viewpoint that saying SERIAL is > *exactly* the same as making a sequence and then making a > default expression that uses the sequence. Nothing behind > the curtain. That certainly does have the merit of being very predictable behaviour - which is good. Another note is that it's definitly going to make it harder for people coming in from other databases, that have IDENTITY or AUTO_NUMBER or whatever the feature is called there. They're likely to go even more "what?!" than now... If it's not obvious yet :-P, I'd be in favour of having SERIAL as black-box as possible, and then just use manual CREATE SEQUENCE and DEFAULT nextval() for when you need a more advanced case. But that's as seen from a user perspective, without regard for backend complexity. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Is a SERIAL column a "black box", or not?
Rod Taylor <[EMAIL PROTECTED]> writes: > Do both. Return SERIAL to being a macro and implement the SQL IDENTITY > construct as the black box version. Doesn't SQL IDENTITY have a number of properties that are significantly different from serial/nextval? I wasn't really volunteering to implement a large new feature to make this happen ;-) Also, I'm not sure how "black boxy" it can be without buying right back into the pg_dump problems. pg_dump has to be able to see what's inside, I think. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] inet increment with int
FYI, 8.2 will have this and more based on this applied patch:
Add INET/CIDR operators: and, or, not, plus int8, minus int8, and inet
minus inet.
Stephen R. van den Berg
---
Patrick Welche wrote:
> Ilya Kovalenko posted some code at in a thread starting at
>
> http://archives.postgresql.org/pgsql-hackers/2005-04/msg00417.php
>
> which lead to the TODO item:
>
> * Allow INET + INT4 to increment the host part of the address, or
> throw an error on overflow
>
> I think that the naively coded function attached does what is needed, e.g.,
>
> CREATE OR REPLACE FUNCTION inet_inc(inet, int4)
> RETURNS inet
> AS '/tmp/inet.so','inet_inc'
> LANGUAGE C STRICT;
>
> CREATE OPERATOR + (
> leftarg = inet,
> rightarg = int4,
> procedure = inet_inc
> );
>
> test=# select '192.168.0.1/24'::inet + 300;
> ERROR: Increment (300) too big for network (/24)
> test=# select '192.168.0.1/24'::inet + 254;
> ?column?
> --
> 192.168.0.255/24
> (1 row)
>
> test=# select '192.168.0.1/24'::inet + 255;
> ERROR: Increment (255) takes address (192.168.0.1) out of its network (/24)
> test=# select '192.168.0.1/24'::inet + -2;
> ERROR: Increment (-2) takes address (192.168.0.1) out of its network (/24)
> test=# select '255.255.255.254/0'::inet + 2;
> ERROR: Increment (2) takes address (255.255.255.254) out of its network (/0)
>
> and just for fun:
>
> create table list (
> host inet
> );
>
> insert into list values ('192.168.0.1/24');
> insert into list values ('192.168.0.2/24');
> insert into list values ('192.168.0.4/24');
> insert into list values ('192.168.0.5/24');
> insert into list values ('192.168.0.6/24');
> insert into list values ('192.168.0.8/24');
> insert into list values ('192.168.0.9/24');
> insert into list values ('192.168.0.10/24');
> insert into list values ('192.168.1.1/24');
> insert into list values ('192.168.1.3/24');
>
> select host+1 from list
> where host+1 <<= '192.168.1.0/24'
>and not exists
>( select 1
>from list
> where host=host+1
> and host << '192.168.1.0/24' )
> limit 1;
>
>
>
> If you agree that this is the right thing, I can code it less
> naively, (Ilya rightly uses ntohl/htonl), create the operator's
> commutator, provide a patch which makes it a built-in, and some
> obvious documentation.
>
> Cheers,
>
> Patrick
[ Attachment, skipping... ]
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
