Re: [HACKERS] PostgreSQL, NetBSD and NFS
On Fri, 31 Jan 2003, D'Arcy J.M. Cain wrote: On Thursday 30 January 2003 12:07, Tom Lane wrote: Perhaps the next thing to do is to strace (ktrace, trace, truss, whatever system-call tracing utility you got) the postmaster and child processes. If we could determine what system call is hanging up, we might be a little closer to solving the mystery. Ktrace. Yes, am doing another test at the moment - using 100Mb to 100Mb and TCP option to the mount. Before I was using the default UDP and going 100Mb to 1000 Mb. If this works I will try my guaranteed fail next and will add ktrace. In fact, I will do that regardless. Look at the -t option to ktrace. It controls what ktrace looks at (syscalls, NAMEI lookups, etc.). Most importantly, you might want to NOT include the 'i' option in there, which is in there by default. It logs the data of all i/o transfers, which baloons the logs. While you may need the data in the end, tracing w/o 'i' could show you the syscalls around the failure which might be enough. Take care, Bill ---(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] schema support, was Package support for Postgres
On Fri, 26 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: I guess to get at my point, I can ask this question, Will schema support invalidate existing PostgreSQL database designs. I would like the answer to be no. I would like our users to be able to dump a pre-schema-release db, upgrade, and then restore into a schema-aware PostgreSQL. And have their restore work. I think this can work. Assume a database like this: user1: CREATE TABLE foo ( ); user2: CREATE TABLE bar ( ); The dump of this would be something like: \c - user1 CREATE TABLE foo ( ); \c - user2 CREATE TABLE bar ( ); So the tables would be created in the appropriate schema context for each user. The remaining problem then is that the two schemas user1 and user2 would need to be created first, but we could make this implicit somewhere. For instance, a user creation would automatically create a schema for the user in template1. Or at least the dump could be automatically massaged to this effect. But right now, we can have different users owning things in one database. So there will be restores out there which will have different users owning things in the same restored-to schema, which will be DEFAULT. This would fundamentally undermine what an SQL schema is and don't help interoperability a bit. If we want to implement our own namespace mechanism we can call it NAMESPACE. But if we want something called SCHEMA then we should implement it the way it's standardized, and there is certainly a tight coupling between schemas and ownership. In fact, as I've said already, a schema *is* the ownership; a user is just a weird PostgreSQL invention. Hmmm I've been looking into this, and you are right. All of the views in INFORMATION_SCHEMA that I looked at contain text like WHERE (SCHEMA_OWNER = CURRENT_USER OR SCHEMA_OWNER IN (SELECT ROLL_NAME FROM ENABLED_ROLES) ) So then we'll need a tool to massage old-style dumps to: 1) create the schema, and 2) path all of the schemas together by default. Well, at least a number of tables won't gain a new colum as a result of this; the owner column will become the schema_id column. :-) Take care, Bill ---(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] Proposed new create command, CREATE OPERATOR CLASS
On Wed, 24 Oct 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: [ revised proposal for CREATE OPERATOR CLASS syntax ] I don't like the idea of writing a bunch of consecutive commas (and having to count them correctly) for cases where we're inserting noncontigous amopstrategy or amprocnum numbers. Perhaps the syntax for the elements of the lists could be [ integer ] operator [ ( argtype, argtype ) ] [ RECHECK ] [ integer ] funcname ( argtypes ) where if the integer is given, it is the strategy/procnum for this entry, and if it's not given then it defaults to 1 for the first item and previous-entry's-number-plus-one for later items. That would work. Or just require the integer all the time. That seems a lot less mistake-prone, really. Concision is not a virtue in the case of a command as specialized as this. Is there really anything wrong with CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree WITH 1 ||, 2 ||=, 3 ||=, 4 ||=, 5 || AND 1 complex_abs_cmp(complex, complex); Not really. Especially when there are ones which are 3, 6, 7, 8, 20 floating around. :-) (One could imagine adding system catalogs that give symbolic names to the strategy/procnum numbers for each access method, and then allowing names instead of integers in this command. I'm not sure whether GiST has sufficiently well-defined strategy numbers to make that work, but even if not, I like this better than a positional approach to figuring out which operator is which.) Something like that (having a catalog of what the different operators are supposed to be) would be nice. Especially for the support procs, so that CREATE OPERATOR CLASS could make sure you gave the right ones for each number. I decided to change that to an operator followed by needs_recheck to indicate a recheck is needed. needs_recheck is not handled as a keyword, but as an IDENT which is examined at parse time. Ugh. Make it a keyword. As long as it can be a TokenId there is no downside to doing so, and doing it that way eliminates interesting issues about case folding etc. (Did you know that case folding rules are slightly different for keywords and identifiers?) Ok. Will do. Yes, I know the case folding is different, though I'm not 100% sure how so. I assume it's something like for identifiers, acents such get folded to unaccented characters? I still like RECHECK better than NEEDS_RECHECK, but that's a minor quibble. RECHECK is one word. I'll go with it. Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] schema support, was Package support for Postgres
On Thu, 25 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: Mainly because when we introduce schemas, all SQL transactions will have to be performed in the context of *some* schema. I think DEFAULT was the name you mentioned for when there was no schema matching the username. As DEFAULT (or whatever we call it) will be made by the PG super user (it will actually be added as part of initdb), then that means that only the super user will own functions. If you want to own the function you should create it in your schema. If you want to create a function and let someone else own it, then ask someone else for write access to their schema. (This should be a rare operation and I don't think SQL provides for it, so we can ignore it in the beginning.) If there is no schema you have write access to then you cannot create things. People have been dying for that kind of feature, and schemas will enable us to have it. I think I understand your descriptions of what you will be *able* to do with schemas. And also that they may describe how you *should* do thing with schema. I'm not disagreeing with you about that. But that's not the angle I'm working. I guess to get at my point, I can ask this question, Will schema support invalidate existing PostgreSQL database designs. I would like the answer to be no. I would like our users to be able to dump a pre-schema-release db, upgrade, and then restore into a schema-aware PostgreSQL. And have their restore work. Since the admin is restoring a db which was made before schema support, there are no CREATE SCHEMA commands in it (or certainly not ones which do a real schema create - right now CREATE SCHEMA is a synonym for CREATE DATABASE). So the restore will create everything in the DEFAULT schema (The schema where creates done w/o a CREATE SCHEMA go). But right now, we can have different users owning things in one database. So there will be restores out there which will have different users owning things in the same restored-to schema, which will be DEFAULT. So we have to have (or just retail) the ability to have different users owning things in one schema. Think about it this way: In its simplest implementation (which is in fact the Entry Level SQL92, AFAIR), a schema can only have the name of the user that owns it. I suspect that this is because SQL has no CREATE USER, so CREATE SCHEMA is sort of how you become a user that can do things. At the same time, schemas would space off the things each user creates, and if you want to access someone else's stuff you have to prefix it with the user's name user.table, sort of like ~user/file. The generic namespace nature of schemas only comes from the fact that in higher SQL92 levels a user can own more than one schema with different names. (Interesting thesis: It might be that our users are in fact schemas (minus the parser changes) and we can forget about the whole thing.) Hmmm... I don't think so, but hmmm.. Now what does this spell for the cooperative development environments you described? Difficult to tell, but perhaps some of these would do, none of which are standard, AFAIK: * schemas owned by groups/roles I think that schemas owned by roles are part of SQL99. * access privileges to schemas, perhaps some sort of sticky bit functionality Or we have to special case the DEFAULT schema. Which strikes me as a bad thing to do. I don't necessarily think of the DEFAULT schemas as a real schema. It might just be there so that *some* schema context is set if you don't have one set otherwise, but you don't necessarily have write access to it. But it might not be necessary at all. While if we were starting over, we might be able to (maybe should have) design(ed) things so we don't need it, I think a DEFAULT schema would help give users of the schema-aware PostgreSQL an experience similar to what they have now. And getting back to where this all started, I think we do need to have the ability to have users other than the schema owner own things in the schema, so we should keep the owner id column in the pg_package table. I'm not against, when things are all said and done, having the default be that only the schema owner can add things. But that's a policy decision. :-) Take care, Bill ---(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] Proposed new create command, CREATE OPERATOR CLASS
On Thu, 25 Oct 2001, Teodor Sigaev wrote: Make me right if I mistake. When we was developing operator @@, I saw that postgres don't use index in select if operation has not commutator. But operator with different types in argument can't be commutator with itself. So I maked operator ~~ only for postgres can use index access for operator @@. There is no any difficulties to adding index support for operator ~~. The same things is with contrib/tsearch module. But I think that there is not any other necessity in presence ~~. So only one of the two needs to go into pg_amop, correct? Then everything else is fine. Take care, Bill ---(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] CREATE OR REPLACE VIEW/TRIGGER
On Tue, 23 Oct 2001, Bruce Momjian wrote: Dear all, Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in PostgreSQL 7.2? Probably not, it's rather late in the cycle (isn't beta imminent?). Oh, I'd vote for OR REPLACE as there's already an opt_or_replace non-terminal in the parser. Adding an optional OR DROP might displease yacc, and also follows in the same vein as what we have for CREATE FUNCTION. Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These features are needed for pgAdmin II (we could also provide a patch for PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for pseudo-modification solutions (which is definitely not a good solution). Our current CREATE OR REPLACE FUNCTION perserves the OID of the function. Is there similar functionality you need where a simple DROP (ignore the error), CREATE will not work? If possible, it's nice to not have commands whose error codes you ignore. That way if you see an error, you know you need to do something about it. Take care, Bill ---(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
[HACKERS] Proposed new create command, CREATE OPERATOR CLASS
I'd like to propose a new command, CREATE OPERATOR CLASS. Its purpose is to create a named operator class, so that you can create new types of index ops. Also, its inclusion would remove the section of the documentation where we tell people how to manually manipulate the system tables. Since schema support is going to change some of the details of the system tables in important ways, I think it's better to move away from manual updates. The command is basically an instrumentation of the documentation on how to add new operator classes. Here's the syntax I'd like to propose: CREATE OPERATOR CLASS name [DEFAULT] FOR TYPE typename USING access method WITH list of operators AND list of support functions New keywords are CLASS (SQL99 reserved word) and REPEATABLE (SQL99 non-reserved word, see below for usage). name is the class's name, and typename is the type to be indexed. access method is the assosciated access method from pg_am (btree, rtree, hash, gist). The presence of [DEFAULT] indicates that this operator class shold be made the default operator class for the type. list of operators is a comma-delimited list of operator specs. An operator spec is either an operator or an operator followed by the keyword REPEATABLE. The presence of REPEATABLE indicates that amopreqcheck should be set to true for this operator. Each item in this list will generate an entry in pg_amop. list of support functions is a comma-seperated list of functions used to assist the index method. Each item in this list will generate an item in pg_amproc. I agree that I think it is rare that anything will set REPEATABLE, but the point of this effort is to keep folks from mucking around with the system tables manually, so we should support making any reasonable entry in pg_amop. Here's an example based on the programmer's guide. We've created the type complex, and have comparison functions complex_abs_lt, complex_abs_le, complex_abs_eq, complex_abs_gt, complex_abs_ge. Then let us have created operators ||, ||=, ||=, ||, ||= based on them. We also have the complex_abs_cmp helper function. To create the operator class, the command would be: CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree with ||, ||=, ||=, ||=, || and complex_abs_cmp; Among other things, complex_abs_ops would be the default operator class for the complex type after this command. An example using REPEATABLE would be: CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree with || REPEATABLE, ||=, ||=, ||=, || REPEATABLE and complex_abs_cmp; Note: I don't think the above command will create a correct operator class, it just shows how to add REPEATABLE. The alternative to REPEATABLE would be something like hit_needs_recheck after the operator. Suggestions? Thoughts? Take care, Bill ---(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] Proposed new create command, CREATE OPERATOR CLASS
On Tue, 23 Oct 2001, Bill Studenmund wrote: Here's the syntax I'd like to propose: CREATE OPERATOR CLASS name [DEFAULT] FOR TYPE typename USING access method WITH list of operators AND list of support functions Hmmm.. Teach me to read the docs. :-) There's no way to set opckeytype. So hwo about: CREATE OPERATOR CLASS name [DEFAULT] FOR TYPE typename [AS stored type] USING access method WITH list of operators AND list of support functions With AS stored type present, the opckeytype column gets set to that type name's oid. New keywords are CLASS (SQL99 reserved word) and REPEATABLE (SQL99 non-reserved word, see below for usage). name is the class's name, and typename is the type to be indexed. access method is the assosciated access method from pg_am (btree, rtree, hash, gist). The presence of [DEFAULT] indicates that this operator class shold be made the default operator class for the type. list of operators is a comma-delimited list of operator specs. An operator spec is either an operator or an operator followed by the keyword REPEATABLE. The presence of REPEATABLE indicates that amopreqcheck should be set to true for this operator. Each item in this list will generate an entry in pg_amop. I decided to change that to an operator followed by needs_recheck to indicate a recheck is needed. needs_recheck is not handled as a keyword, but as an IDENT which is examined at parse time. list of support functions is a comma-seperated list of functions used to assist the index method. Each item in this list will generate an item in pg_amproc. I agree that I think it is rare that anything will set REPEATABLE, but the point of this effort is to keep folks from mucking around with the system tables manually, so we should support making any reasonable entry in pg_amop. Take care, Bill ---(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] Proposed new create command, CREATE OPERATOR CLASS
On Wed, 24 Oct 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: I'd like to propose a new command, CREATE OPERATOR CLASS. Seems like a good idea. operator spec is either an operator or an operator followed by the keyword REPEATABLE. The presence of REPEATABLE indicates that amopreqcheck should be set to true for this operator. This is bogus, since REPEATABLE is a very poor description of the meaning of amopreqcheck; to the extent that it matches the meaning at all, it's backwards. Don't pick a keyword for this solely on the basis of what you can find that's already reserved by SQL99. Given the restricted syntax, the keyword could be a TokenId anyway, so it's not really reserved; accordingly there's no need to limit ourselves to what SQL99 says we can reserve. Perhaps use RECHECK? That would fit the field more closely... I was writing a note saying that as this one came in. Yes, it's now a TokenId, and I look for the text needs_recheck. I agree that I think it is rare that anything will set REPEATABLE, but the point of this effort is to keep folks from mucking around with the system tables manually, so we should support making any reasonable entry in pg_amop. Then you'd better add support for specifying an opckeytype, too. BTW these things are not all that rare; there are examples right now in contrib. Yep, I noticed that. CREATE OPERATOR CLASS complex_abs_ops DEFAULT FOR TYPE complex USING btree with ||, ||=, ||=, ||=, || and complex_abs_cmp; This syntax is obviously insufficient to identify the procedures, since it doesn't show argument lists (and we do allow overloading). Less So then funcname(type list) [, funcname(type list)] would be the way to go? obviously, it's not sufficient to identify the operators either. I think you're implicitly assuming that only binary operators on the specified type will ever be members of index opclasses. That does not seem like a good assumption to wire into the syntax. Perhaps borrow Well, the requirement of binarity is something which is explicit in our example documentation, and so that's why I used it. the syntax used for DROP OPERATOR, which is ugly but not ambiguous: operator (type, type) operator (type, NONE) operator (NONE, type) We could allow an operator without any parenthesized args to imply a binary op on the specified type, which would certainly be the most common case. Do any of the access methods really support using non-binary operators? BTW, is there any need to support filling nonconsecutive amopstrategy or amprocnum slots? This syntax can't do that. GiST seems to have a pretty loose idea of what set of strategy numbers you can have, so there might possibly be a future need for that. I can add support for skipping operators, if needed. A comma followed by a comma would indicate a null name. Oh gross. I just looked at contrib/intarray, and it defines two entries in pg_amop for amopstrategy number 20. They do happen to be commutators of each other. Look for the @@ and ~~ operators. Wait a second, how can you do that? Doesn't that violate pg_amop_opc_strategy_index ? It's supposed to make pairs of amopclaid and amopstrategy be unique. Confused Also, it might be better to use a syntax in the style of CREATE OPERATOR, with a list of param = value notations, because that's more easily extensible if we change the opclass stuff again. CREATE OPERATOR CLASS classname ( basetype = complex, default, operator1 = || , ... proc1 = complex_abs_cmp ); However, specifying the proc arglists in this style would be awfully tedious :-(. I can't think of anything better than proc1arg1 = complex, proc1arg2 = complex, ... which is mighty ugly. Which is why I didn't use it. :-) If we can't make the other syntax work, then we can go with a DefineStmt type syntax. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER
On Tue, 23 Oct 2001, Bruce Momjian wrote: If possible, it's nice to not have commands whose error codes you ignore. That way if you see an error, you know you need to do something about it. Folks, is this a valid reason for adding OR REPLACE to all CREATE object commands? Sounds good to me. :-) Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] schema support, was Package support for Postgres
On Wed, 24 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: So I am a naive programmer because I mention intent above? No. Sorry, that's the way it came across. As you've said that was not your intent, please disregard my response; I was responding to something you did not mean. So if we have INFORMATION_SCHEMA with the right vies in it, we are fine doing whatever we want. I think some interpretation of the SQL standard can be used to prove that a new schema should not contain any objects. So you're going to have to stick to the two predefined schemas to put the system catalogs in. Then again, other interpretations may be used to prove other things. But to me the intent of the standard is clear that system catalogs are meant to go into the defintion schema, and I don't see a reason why this could not be so. I had been thining that we could have the built-in objects (functions, types, operators, etc.) in whatever was the default.master package, but it looks like SQL99 doesn't like that. You're right that built-in things have to be in a different schema than user-added things. Section 10.4 contains text: ii) If RN contains a schema name SN, then Case: 1) If SN is INFORMATION_SCHEMA, then the single candidate routine of RI is the built-in function identified by routine name. Actually 4.24 is more exact. It defines a built-in function as a routine which is returned from teh query: SELECT DISTINCT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = INFORMATION_SCHEMA Actually, since we have to have an INFORMATION_SCHEMA, and INFORMATION_SCHEMA gets thrown around a lot, I think it'd be easiest to make INFORMATION_SCHEMA the schema containing built-in things. Otherwise (among other things) we have to replace DEFINTION_SCHEMA with INFORMATION_SCHEMA in the above-defined view (and in a lot of other places). Thoughts? I stil think we can't do that, since someone other than the schema owner can add a package to a schema. :-) Or at least that's the assumption I'm running on; we allow users other than PGUID to create functions (and operators and aggregates and types) in the default (whatever it will be called) schema, so why shouldn't they be allowed to add packages? Because SQL says so. All objects in a schema belong to the owner of the schema. In simple setups you have one schema per user with identical names. This has well-established use patterns in other SQL RDBMS. Then implimenting schemas will cause a backwards-incompatabile change regarding who can add/own functions (and operators and ..). Mainly because when we introduce schemas, all SQL transactions will have to be performed in the context of *some* schema. I think DEFAULT was the name you mentioned for when there was no schema matching the username. As DEFAULT (or whatever we call it) will be made by the PG super user (it will actually be added as part of initdb), then that means that only the super user will own functions. That's not how things are now, and imposing that on upgrading users will likely cause pain. Think about a dump/restore upgrade from 7.2 to 7.3. Right now users other than PGUID can own functions (and triggers, etc.). When you do the restore, though, since your dump had no schema support, it all goes into DEFAULT. Which will be owned by PGUID. So now we either have a schema with things owned by a user other than the schema owner, or we have a broken restore. Or we have to special case the DEFAULT schema. Which strikes me as a bad thing to do. For now, I'd suggest letting users other than a schema owner own things in a schema, and later on add controls over who can add things to a schema. Then when you do a CREATE SCHEMA command, you will implicitly be adding restrictions prohibiting someone other than the owner from adding things (including packages/subschemas). I agree that this might not be what everyone would want, but it seems extensible. However, I feel we're trying to design too many things at once. Let's do schemas first the way they're in the SQL standard, and then we can try to tack on ownership or subschemas or package issues. Well, the packages changes can easily be turned into schema support for functions and aggregates, so we are part way there. Also, the packages changes illustrate how to make system-wide internal schema changes of the type adding SQL schemas will need. Plus, packages as they are now are useful w/o schema support. And there's the fact that schemas were wanted for 7.2, and didn't happen. Withouth external adgitation, will they happen for 7.3? Given the size of the job, I understand why they didn't happen (the package changes so far represent over 3 months of full-time programming). We've got some momentum now, I'd say let's run with it. :-) Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] For John Havard, please ignore otherwise
Your [EMAIL PROTECTED] address bounced. Do you have another one? Thanks, Bill ---(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] schema support, was Package support for Postgres
On Tue, 23 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: Why? Operators are used differently than functions. I don't think so. Operators are a syntacticaly convenience for functions. That's what they always have been and that's what they should stay. How does what you say disagree with what I said? Operators certainly have a lot more structure to them than a function call does. That's why you give the restriction and join functions, and you hand them commutation and negation operators. The optimizer uses all of these tools to make what you want to have happen (adding for instance) happen as efficiently as possible. It will re-write what you said in a different manner, if that different manner holds the same intent yet is more efficient. Conceptually the main determiner of what function you want is the name, at least as far as from what I can tell from talking with all the programmers I know. Yes, we make sure the types match (are part of the primary key), but the name is the main concept. Operators, however, are more intent-based. The '+' operator means I want these two things added together. I don't care so much what types are involved, I want adding to happen. That's a difference of intent. And that's the reason that I think different namespacing rules make sense. Naive developers all program by intent. If I invoke a + operator then I expect it to add. If I call a sqrt() function then I expect it to calculate the square root. If I execute an INSERT statement then I would prefer that I did not delete anything. Designing systems to work by intent can be construed as an aspect of user-friendliness. But the more knowledgeable programmer is mildly aware of what's going on behind the scenes: Both + and sqrt are just names for function code that may or may not do what you think they do. So this applies to both functions and operators. So I am a naive programmer because I mention intent above? That is very condescending, Peter, and strikes me as inappropriate. Are you really so out of things to say that you have to resort to condescension? At what point have you tried to determine how experienced a programmer I am? You've never asked me for my resume, or what projects I've worked on before this. Your comments indicate to me that you have not yet tried the patch I sent in, and if you have, I really doubt you've made packages with it. So how can you judge? The fact I disagree with you? Also, in your naive vs more knowledgeable programmer comparison, you mention user-friendliness in the naive part, the bad part. Do you really think that user-friendliness is a bad thing? I hope not. I think that user-friendliness is an important part of programming. It means that your tools or programmatic interfaces have (or lack) an appropriateness to the task at hand. It's not just for command lines or GUIs. I've worked with different libraries and programming packages, and I have experienced the ones where the design and layout make the library/package useful, and ones where the design and layout get in the way. Hmmm... Thining about it, I now think you're right, that we should have a way to handle pathing. A package author should be able to set the path used for routines in the package, though. The built-in schemas is called DEFINITION_SCHEMA. Why is it different from the DEFAULT you get when you log into a database which doesn't have a schema whose name matches your username? Because SQL says so. Actually I'm not so sure. See the note to Thomas, especially the last sentance of section 21.1. It seems that if we have an INFORMATION_SCHEMA which contains all the views in the spec, and our system tables have the right behaviors, then we are fine. Actually, the text in section 20.1, Introduction to Information Schema and Definition Schema is more direct: The views of the Information Schema are viewed tables defined in terms of the base tables of the Definition Schema. The only purpose of the Definition Schema is to provide a data model to support the Information Schema and to assist understanding. An SQL-implementation need do no more than simulate the existance of the Definition Schema as viewed through the Information Schema views. So if we have INFORMATION_SCHEMA with the right vies in it, we are fine doing whatever we want. Not necessarily. A user other than the one who owns the schema can add a package to it. It's the same thing as why we keep track of who added a function. :-) Blech, I meant you can replace the owner column with the schema column. That's actually what I thought you said. :-) I stil think we can't do that, since someone other than the schema owner can add a package to a schema. :-) Or at least that's the assumption I'm running on; we allow users other than PGUID to create functions (and operators and aggregates and types) in the default (whatever it will be called) schema, so why shouldn't they be allowed to add packages
Re: [HACKERS] schema support, was Package support for Postgres
On Tue, 23 Oct 2001, Thomas Lockhart wrote: (I've been following the thread, at least casually ;) intent-based. The '+' operator means I want these two things added together. I don't care so much what types are involved, I want adding to happen. That's a difference of intent. And that's the reason that I think different namespacing rules make sense. But operators *are* functions underneath the covers. So different namespacing rules seem like a recipe for missed associations and unexpected results. Underneath the covers, yes. But those covers make the difference in what I'm thinking of. An operator isn't just one function call, it can be multiple ones. And not just multiple itterations, but multiple different ones depending on what the optimizer is doing. That's why you can give an operator more than just the procedure operator. You also give it a join proc and a restrict proc, and you tie it in with a commutator, negator, and two sort operators. When you use an operator, you're specifying an intent, and all of these parts of an operator's definition help make that intent happen. The problem though is that if operators are namespaced the same as functions, then we destroy one of the benefits of packages - a seperate namespace for functions. Can you think of a specific example where this namespacing causes problems? The functions and aggregates are namespaced off of the containing schema, but the types and operators aren't. Inside the package, you have access to everything in the package. In the enclosing schema, you have immediate access to the types and operators, and can get at the functions and aggregates by packname.. Part of it is that I only expect a package to add operators for types it introduced. So to be considering them, you had to have done something that ties in the type in the package. Like you had to make a column in a table using it. I'd expect schemas/packages to have operators and functions for existing types, not just new ones. That is certainly how our extensibility features are used; we are extensible in several dimensions (types, functions, operators) and they do not all travel together. We can't guess at the future intent of a package developer, and placing limitations or assumptions about what *must* be in a package just limits future (unexpected or suprising) uses. Please play with the patch and try it. There is no restriction in the patch that operators (and functions aggregates) can only be for types new to the package. You can add operators for built-in types, and you can even add operators for other user-specified types too. And pg_dump will make sure that a user-defiend type used in a package will get dumped before the package. The absolute path scoping and lookup scheme is defined in SQL99. I'm not sure I understand the issue in the last paragraph: you seem to be making the point that absolute paths are Bad because package developers don't know what those paths might be. But otoh allowing absolute paths (and/or embedding them into a package) gives the developer *precise* control over what their package calls and what the behaviors are. istm that if a package developer needs to specify precisely the resources his package requires, then he can do that. And if he wants to leave it flexible and determined by scoping and pathing rules, then he can do that too. afaik relative pathing is not specified in the standard, but we might want to consider how we would implement that as an extension and whether that gives more power to the packager or developer. I've found the spec, and am still studying it. Though what I've found so far is a schema search path. My main interest is for the package itself to be the first thing searched. After that, whatever search path is appropriate for the schema seems like the right thing to do. So, besides the fact I think we should do schemas as per the spec, I think using the schema search path is the right thing to do. The built-in schemas is called DEFINITION_SCHEMA. Why is it different from the DEFAULT you get when you log into a database which doesn't have a schema whose name matches your username? It may not be. But SQL99 specifies the name. Actually, the most interesting thing I saw was in the start of chapter 21 (the chapter on the DEFINITION_SCHEMA) at the bottom of section 21.1. The specification provides only a model of the base tables that are required, and does not imply that an SQL-implimentation shall provide the functionality in the manner described in this clause. As I understand that, we are free to impliment things as we wish. We just need to have the pieces/functionality described therein. We *don't* have to use the names or exact formats used in the spec. As a concrete example of what I mean, I believe that we are free to pg_attribute as it is and still comply with section 21.7 ATTRIBUTES base table. UDT_NAME we impliment with attrelid, ATTRIBUTE_NAME we do with
Re: [HACKERS] namespaces
On Sun, 21 Oct 2001, Serguei Mokhov wrote: - Original Message - From: Bill Studenmund [EMAIL PROTECTED] Sent: Friday, October 19, 2001 2:04 PM Quick question: would it be possible then create a 'system' package and 'system' (or 'master' if you will) schema (when it's implemented), move over all the system tables (pg_*) into the master schema and functions into the 'system' package, so that no name conflicts will arise when creating types, functions, tables, etc with the same names as system ones? Yes. That is part of my plan actually. :-) Oh, one reason that needs to happen is that everything needs to be in a package or a schema; for the tables where they do namespacing the schema or package is part of the primary key. Hmm. I see. Then there won't be a problem of creating any DB object with the system name. It will work, though if you start creating tables named pg_class, I think you might make your head hurt. Also, your own int4 type might not be such a good idea... In the patch I sent in last week, Yeah, I remember that one. Took me a couple of minutes to download. You know, it never hurts to compress things: then the patch would be ~10 times less in size, and you wouldn't have to worry about PINE messing up with your code in the message body... :) And that would reduce the bounce rate too. Just a kind and gentle cry to reduce the size of patches sent to my mailbox and save some bandwidth on the way :) Ok. :-) Next time I will either compress it or I'll mail in a URL. all of the built-in functions and aggregates are in the standard package, and you can infact reference them as standard.foo. When you refer to it just foo(), and you have foo() defined in more than one package, how do you resolve this? Do you also have a notion of a global package and sub-packages? There is a very simple search path system. If you are in a package (in a function that is part of a package), you look for foo in that package. If you don't find it there, you look in stadard. If it's not there, you don't find it. To look in other packages than the one you're in, you have to say which one it is. With schemas, if your package is not in master or whatever it is called, you look first in your package, then in your_schema.standard, then in master.standard. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] schema support, was Package support for Postgres
On Sun, 21 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: The big one for now is how should you log into one schema or another? psql database.schema ? Each user has a default schema, which is by default the schema with the same name as the user name, or if no such schema exists, it's the DEFAULT schema (which I believe is what Oracle calls it). Then there should be something like set schema path. I don't think schemas should be a connection parameter. -- That would be my ideas anyway. I can see advantages for both; if you just connect to a database that has schemas, you get a schema with your name if it's there, and a default otherwise. But I can see definite advantages to being able to specify. Whenever you look up a function or aggregate, you give the oid of the package to look in in addition to the name (and types). Having the package id in the index provides the namespacing. Whenever you look up a type or operator, you don't have to give a package id. While I understand that package.+ is silly, anything that make operators and functions work fundamentally differently is suspicious. A common search mechanism that works for everything in packages (or subschemas, which I'd prefer) would/should/could allow you to do without those prefixes. Why? Operators are used differently than functions. That strikes me as a good reason to namespace them differently. Conceptually the main determiner of what function you want is the name, at least as far as from what I can tell from talking with all the programmers I know. Yes, we make sure the types match (are part of the primary key), but the name is the main concept. Operators, however, are more intent-based. The '+' operator means I want these two things added together. I don't care so much what types are involved, I want adding to happen. That's a difference of intent. And that's the reason that I think different namespacing rules make sense. Part of it is that I only expect a package to add operators for types it introduced. So to be considering them, you had to have done something that ties in the type in the package. Like you had to make a column in a table using it. Another take on that is that I expect the main user of (direct) function calls calling package functions will be other functions in that package, while the main users of operators will be places which have used a type from said package. Like queries pulling things out of tables using that type. So the function namespacing is a concenience/tool primarily for the package developer, while the operator and type namespacing is more a convenience for the end application developer. Also, you seem to be wanting a path-search ability that is something like the PATH environment variable. This pathing is fundamentally different; to use unix terms, it is .:... The fundamental difference is that there are no absolute paths. The searching is totally location (of routine) dependant. To add something like an absolute path would totally break the whole motivation for packages. The idea is to give a developer an area overwhich s/he has total name control, but if s/he needs built-in routines, s/he doesn't need to say standard. to get at them. If we allow something like absolute paths in the package namespacing, then we totally destroy that. Because a package developer can't be sure what pathing is going on, s/he really has no clue what packages will get found in what order. So then you have to be explicit in the name of all the functions you use (otherwise if a user essentially puts something other than . at the head of the path, then you don't get routines in your own package), or run the risk of getting all sorts of run-time errors. A feature designed to make writing packages easier now makes them harder. That strikes me as a step backwards. There is a built-in schema, master. It will have a fixed oid, probalby 9 or 11. The built-in schemas is called DEFINITION_SCHEMA. Why is it different from the DEFAULT you get when you log into a database which doesn't have a schema whose name matches your username? The only other part (which is no small one) is to add namespacing to the rest of the backend. I expect that will mean adding a schema column to pg_class, pg_type, and pg_operator. Yup. But you can replace the owner package with the schema column, because the owner property will be transferred to the schema. Not necessarily. A user other than the one who owns the schema can add a package to it. It's the same thing as why we keep track of who added a function. :-) Take care, Bill ---(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] Package support for Postgres
On Sat, 20 Oct 2001, Rod Taylor wrote: But what if you want a C function to set a variable which can be accessed using an SQL, perl, PLpgSQL or other function type? Shouldn't a global variable be global between all types of functions? No. Doing that requires that all languages have the same internal storage of variables. And it's more than just an int4 takes up 4 bytes. Look in the plpgsql source, at struct PLpgSQL_var. There is a fair amount of into about a variable. While we could harmonize the info storage, making globals global across all languages would also would mean breaking down a lot of the isolation between PLs. Right now they are their own independent entities. To tie them together like this would, in my opinion, make them fragilly-interconnected. My suggestion is to just add a get and a set routine in one language, and have it store the global. :-) Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Package support for Postgres
On Sat, 20 Oct 2001, Peter Eisentraut wrote: Yes, you're right. Actually, sharing data across PostgreSQL C functions is trivial because you can just use global variables in your dlopen modules. Exactly. That's why I never envisioned C or internal functions using package global variables. :-) Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] namespaces
On Sat, 20 Oct 2001, Serguei Mokhov wrote: It means that when you want to use one of the built in functions (date_part, abs, floor, sqrt etc.) you don't have to prefix it with standard.. You can just say date_part(), abs(), floor(), sqrt(), etc. The only time you need to prefix a call with standard. is if you want to exclude any so-named routines in your own package. Quick question: would it be possible then create a 'system' package and 'system' (or 'master' if you will) schema (when it's implemented), move over all the system tables (pg_*) into the master schema and functions into the 'system' package, so that no name conflicts will arise when creating types, functions, tables, etc with the same names as system ones? Yes. That is part of my plan actually. :-) In the patch I sent in last week, all of the built-in functions and aggregates are in the standard package, and you can infact reference them as standard.foo. Moving types, operators, and relations (and whatever else should go there) into master was part of my plan for schemas. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] namespaces, was Package support for Postgres
On Sat, 13 Oct 2001, Bill Studenmund wrote:
On Sat, 13 Oct 2001, Tom Lane wrote:
I also wonder how the fixed, single-level namespace search path you
describe interacts with the SQL rules for schema search. (I don't
actually know what those rules are offhand; haven't yet read the schema
parts of the spec in any detail...)
Should be independent. The searching only happens when you are not in the
standard package, and you give just a function name for a function.
The searching would only happen in the current schems. If
you give a schema name, then I'd expect PG to look in that schema, in
standard, for that function. If you give both a schema and package name,
then PG would look in that package in that schema.
My description of namespaces seems to have caused a fair bit of confusion.
Let me try again.
The ability of the package changes to automatically check standard when
you give an ambiguous function name while in a package context is a
convenience for the procedure author. Nothing more.
It means that when you want to use one of the built in functions
(date_part, abs, floor, sqrt etc.) you don't have to prefix it with
standard.. You can just say date_part(), abs(), floor(), sqrt(), etc.
The only time you need to prefix a call with standard. is if you want to
exclude any so-named routines in your own package.
I've attached a copy of a package I wrote as part of testing package
initializers and package global variables. It is an adaptation of the
Random package described in Chapter 8 of _Oracle8 PL/SQL Programming_ by
Scott Urman. Other than adapting it to PostgreSQL, I also tweaked the
RandMax routine to give a flat probability.
Note the use of date_part() in the BODY AS section, and the use of rand()
in randmax(). Both of these uses are the ambiguous sort of function naming
which can trigger the multiple searching. Since they are in plpgsql code,
they get parsed in the context of the random package. So when each of them
gets parsed, parse_func first looks in the random package. For rand(), it
will find the rand() function and use it. But for date_part(), since there
isn't a date_part function in the package, we use the one in standard.
If we didn't have this ability, one of the two calls would need to have
had an explicit package with it. There are two choices (either standard.
would be needed for date_part(), or random. for rand()), but I think
both would lead to problems. Either choice makes the syntax heavy, for
little gain. Also, if we scatter the package name throughout the package,
if we ever want to change it, we have more occurences to change.
Does that make more sense?
Take care,
Bill
create or replace package random as
declare v_seed 'float8', v_Multiplier 'float8', v_incriment 'float8'
language 'plpgsql'
body as '
begin
v_Multiplier := 22695477;
v_incriment := 1;
v_seed := date_part(''epoch'', timestamp ''now'');
return NULL;
end;
'language 'plpgsql'
function changeseed (float8) returns float8 as '
begin
v_seed := $1;
return 0;
end;
' language 'plpgsql'
function rand () returns float8 as '
begin
v_seed := (v_multiplier * v_seed + v_incriment) % ( 2::float8 ^ 32);
return (v_seed/(2^16)) % 32768::float8;
end;
' language 'plpgsql'
function randmax (float8) returns float8 as '
begin
return rand() * $1 / 32768 + 1;
end;
' language 'plpgsql';
---(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] schema support, was Package support for Postgres
On Sat, 13 Oct 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: The other choice is to just give the function's name. The first place Postgres will look is in the package context used for parsing. If it's not there (and that context wasn't standard), then it will look in standard. Hmm. How does/will all of this interact with SQL-style schemas? The reason I'm concerned is that if we want to retain the present convention that the rowtype of a table has the same name as the table, I think we are going to have to make type names schema-local, just like table names will be. And if type names are local to schemas then so must be the functions that operate on those types, and therefore also operators (which are merely syntactic sugar for functions). Ahhh... There's the operators == sugar comment. I agree with you above; types and functions need to be schema-specific. This seems like it will overlap and possibly conflict with the decisions you've made for packages. It also seems possible that a package *is* a schema, if schemas are defined that way --- does a package bring anything more to the table? I'm repeating myself a little. :-) Packages aren't schemas. What they bring to the table is they facilitate making stored procedures (functions). You can have twelve different developers working on twenty different packages, with no fear of name conflicts. The package names will have to be different, so there can be functions with the same names in different pacakges. This ability isn't that important in small development projects, but is really important for big ones. Think about big db applications, like Clarify. Any project with multiple procedure authors. Without something like packages, you'd need to spend a lot of effort coordinating names such so that they didn't conflict. With packages, it's rather easy. Also, I think PostgreSQL can challenge the commercial databases for these applications. But to do so, changing over to PG will need to be easy. Having packages there will greatly help. I'd like to see schemas implemented per the spec in 7.3, so we need to coordinate all this stuff. For the most part, I think packages and schemas are orthogonal. I'm taking a cue from Oracle here. Oracle considers packages to be a schema-specific object. Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Package support for Postgres
On Thu, 18 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: Honestly, I do not understand why global variables have been such a sore point for you. My point is that the proposed package support introduces two features that are a) independent, and b) already exist, at least in design. Schemas are already planned as a namespace mechanism. Global variables in PLs already exist in some PLs. Others can add it if they like. There aren't any other features introduced by package support that I can see or that you have explicitly pointed out. Then my explanations didn't click. Please let me try again. The main feature of package support is that it greatly facilitates developing large, complicated db applications. Like ones which require multiple full-time developers to develop. I think PostgreSQL has the internals to run these apps, and it should provide a developement environment to encourage them. That's what packages are about. I have never developed an application that large. But I have talked to our DBAs who have worked with such things in Oracle, and a few who have worked on (developed) such large applications. They all have agreed that something akin to packages is needed to make it work. The seperate namespaces (for function names and for variables) mean that different programmers don't have to coordinate the names of functions. Or that the names have to have some de-ambiguating prefix to make them different. All that has to happen is that different packages have different names. When you through in the idea of developers releasing libraries (packages) on the net, the minimality of coordination is even more important. The fact (for PostgreSQL i.e. this implimentation) that types and operators aren't namespaced off means that they effectively leak into the enclosing database (or schema when we have them) so that making and supporting new types can be the aim/result of the package. For comaprison with other languages, packages strike me as comparable to libraries (in C) or modules (say in Perl or Python). Neither libraries nor modules realy do anything that can't be achieved otherwise in the language. Yet they are a prefered method of developing code, especially reused code. When you're making a program/application, you don't need to concern yourself with (many) details about the code; you use the module and that's it. Likewise here, an application developer/integrator need only load a module, and then all the routines in it are available. You don't for instance have to worry if the routines have names which overlap ones you were using, or ones used worse yet by another set of routines you want to use. I think Jean-Michael's comments were right. While I'm not sure if things will be as overwhelming as he predicted, packages (even as implimented in my patch) will help people develop code libraries for PostgreSQL. And that will make PostgreSQL applications easier. Also, as I've come to understand what schemas are and aren't, I've realized that they can be readily leveraged to help with schema support. Schemas, at least according to the SQL92 spec I have looked at (I'd love to see a later spec), are namespaces only for tables and views (and character sets and a number of other things which PostreSQL doesn't support). They don't touch on functions. Sure, PostgreSQL could decide to do something with functions, but if we do, we're improvising, and I see no reason to improvise differently than other DBMSs have done. There may be one, but I don't see it. Also, as I understand schemas (which could be wrong), there is a difference in emphasis between schemas and packages. Schemas are a way to partition your database, so that different parts of an application see only a subsection of the whole database. You can have some parts only able to access one or another schema, while other parts can access multiple schemas. Packages however are designed to help you build the tools to make the applications work (providing toolchests of code for instance). It's like schemas are a more top-down design element, and packages are bottom-up. Where I see the interaction is that we want to have different schemas have schema-specific functions, we just have a package implicitly assosciated with each schema which contains the traditional functions and aggregates (and types and operators) of that schema. So the two questions I ask myself are: 1. Are package namespaces better than schemas? The answer to that is no, because schemas are more standard and more general. See above; I never said packages were better than schemas (nor worse). I said they were different parts of the puzzle. I think they are both important and valuable. 2. Are global variables via packages better than the existing setups? My answer to that is again no, because the existing setups respect language conventions, maintain the separation of the backend and the language handlers, and of course they are already
Re: [HACKERS] schema support, was Package support for Postgres
On 19 Oct 2001, Gunnar [iso-8859-1] Rønning wrote: * Bill Studenmund [EMAIL PROTECTED] wrote: | | Packages aren't schemas. What they bring to the table is they facilitate | making stored procedures (functions). You can have twelve different | developers working on twenty different packages, with no fear of name | conflicts. The package names will have to be different, so there can be | functions with the same names in different pacakges. Hmm. But if we had schema support can't we just package those procedures into a schema with a given name ? Maybe my stored procedures needs some other resources as well that should not conflict with other packages, like temp tables or such. It then seems to me that using schemas can solve everything that packages do and more ? Assuming that schema support covers functions (which Tom, I, evidently you, and Oracle think it should but which isn't mentioned at least in SQL92), you could do that. And if you're adding tables, you probably should. But a lot of times you don't need to go to the effort of namespacing off a whole new schema, and I can think of some cool things to do when you don't. One example is a large, complicated db app with multiple programmers. For each general area of the app, you can create a package. That way you modularize the code into more managable pieces. But since the are all in the same schema, they can maintain/interact with the same tables. So that's an arguement for packages/subschemas. | For the most part, I think packages and schemas are orthogonal. I'm taking | a cue from Oracle here. Oracle considers packages to be a schema-specific | object. What is really the difference functionality wise of making a subschema and package ? In both cases you deal with the namespace issues. A matter of what is subspaced. I'd assume that a subschema namespaces off everything a schema does. A package however only namespaces off functions and aggregates. Packages, at least as I've implimented them, do *not* namespace off types nor operators they contain. Technically, the package oid is a key in the name index for pg_proc and pg_aggregate, while it is not for pg_type and pg_operator. I admit, I took a minor liberty here. Oracle packages do have types, but Oracle types are not as rich as PostgreSQL's So when I was translating packages, I made the types in them match PostgreSQL's. Also, since I'd added aggregates and types, adding operators seemed like a reasonable thing. Both from the point of view of the parser (they are all done about the same way), and from the point of utility. PostgreSQL's ability to add types is really cool, and the ability to add operators makes new types convenient to use. If packages could add types and support functions but not operators, that'd seem lame. The reason that packages don't namespace off types and operators is I think it makes them more useful. Think about the complex number example in the programmer's guide. I can think of scientific applications which could use them. But having to say package.complex for the type would be combersome. And even worse, having to say package.+ or package.- would be bad. And package.* might be ambiguous to the parser! So that's why I made pacakges not be subschemas. Packages were designed to help with writing stored procedures, and to do it well. :-) Take care, Bill ---(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] schema support, was Package support for Postgres
On 19 Oct 2001, Gunnar [iso-8859-1] Rønning wrote: * Tom Lane [EMAIL PROTECTED] wrote: | | Yeah. I am wondering whether we couldn't support Oracle-style packages | as a thin layer of syntactic sugar on top of schemas. I am concerned | about the prospect that foo.bar might mean either object bar in | schema foo or object bar in package foo. Agreed, and in Sybase you may declare a procedure in a schema(or database which is the Sybase term). If you want it global you declare it in the master schema. Oh cool. I knew that Oracle used standard for the name of the built-in package, but I didn't know a name for the built-in schema. master sounds good. Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] schema support, was Package support for Postgres
On Fri, 19 Oct 2001, Tom Lane wrote: Yeah. I am wondering whether we couldn't support Oracle-style packages as a thin layer of syntactic sugar on top of schemas. I am concerned about the prospect that foo.bar might mean either object bar in schema foo or object bar in package foo. See my note to Gunnar for why I think packages should be inside of schemas rather than renamed schemas. Types and expecially operators would be much more useful to the enclosing schema that way (I think). Yes, there is an ambiguity between schema foo and package foo. I can think of a few ways to deal with this. 1) Do whatever Oracle does, assuming it's not grotesque. Yes, I've said that a lot. But I think PostgreSQL can really take some applications away from the commercial DBMSs, and Oracle is #1 in that market. So Oracle represents Prior Art of least surprise. :-) 2) If there is both a schema named foo and a package named foo, then foo.bar should always take foo to be the schema. If we let a package in the local schema named foo be found before the schema foo, then we would get different results in said schema and another one (which didn't have a package named foo in it). 3) Don't let schemas and packages have the same name. I actually believe this is what Oracle does, though I haven't checked. I _have_ checked that packages and tables can't have the same name, and built that into the packages patches. I think requiring schemas to have names different from tables and packages is a good thing, and would reduce ambiguity. As an aside the reason I suspect this is what Oracle does is that Oracle has a system table which contains a list of named objects. Tables and packages show up as entries in this table, and I'd expect schemas would too. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] schema support, was Package support for Postgres
On Tue, 16 Oct 2001, Bill Studenmund wrote: I still think that schemas and packages are different, but I now think they are interrelated. And that it shouldn't be too hard to leverage the package work into schema support. Still a lot of work, but the package work has shown how to go from one to two in a number of ways. :-) First off, do you (Tom) have a spec for schema support? I think that would definitly help things. I found an on-line copy of the SQL92 spec, and I've been looking at it. I think it wouldn't be _that_ much more work to add shema support to what I've done for packages. Not trivial, but certainly not double the work. But I have some questions. The big one for now is how should you log into one schema or another? psql database.schema ? Here's a plan for schema support. But first let me review what packages have. Right now (in my implimentation), packages have added a standard package (oid 10) which contains all of the built-in procedures, aggregates, types, and operators. Whenever you use the normal CREATE commands, you add a procedure, aggregate, operator, or type in the standard package. There is a new table, pg_package, which lists the name of each installed package and its owner. standard is owned by PGUID. packages are referenced by the oid of the row describing the package in this table. Whenever you look up a function or aggregate, you give the oid of the package to look in in addition to the name (and types). Having the package id in the index provides the namespacing. Whenever you look up a type or operator, you don't have to give a package id. Whenever you call the parser to parse a command, you pass it the package context (oid) in which the parsing takes place. If you are typing in commands in psql, that package id is 10, or standard. Likewise for sql or plpgsql routines not in a package. If you are in an sql or plpgsql routine which is in a package, the package's oid is passed in. That's what has package routines look in the package first. The parser also notes if you gave a package id or not (package.foo vs foo). If you were in a package context and were not exact (foo in a procedure in a package for instance), then all of the places which look up functions will try standard if they don't find a match. There is a table, pg_packglobal, which contains package globals for the different PLs. It contains 5 columns. The first three are the package oid, the language oid, and a sequence number. They are indexed. The two others are variable name and variable type (of PostgreSQL type name and text respectively). PLs for which these variables don't make sense are free to ignore them. Extending this for schema support. Executive summary: all of the above becomes the infrastructure to let different schemas have schema-private functions and aggregates. We add a new table, pg_schema, which lists the schemas in this database. It would contain a name column, an owner column, something to indicate character set (?), and other stuff I don't know of. Schemas are referenced internally by the oid of the entry in this table. There is a built-in schema, master. It will have a fixed oid, probalby 9 or 11. The master schema will own the standard package oid 10, which contains all of the built-in functions, and ones added by create function/etc. Each new schema starts life with a standard package of its own. This package is the one which holds functions aggregates made with normal commands (create function, create aggregate) when you're logged into that schema. pg_package grows two more columns. One references the schema containing the package. The other contains the oid of the parent package. The idea is this oid is the next oid to look in when you are doing an inexact oid search. It's vaguely like .. on a file system. For master.standard, this column is 0, indicating no further searching. For say foo.standard (foo is a schema), it would be the oid of master.standard (10). Likewise for a package baz in the master schema, it would be master.standard. For a package in a schema, it would be the oid of the standard package of the schema. As an example, say the foo schema had a package named bup. For baz.bup, this column would have the oid of baz.standard. Right now I'm in the process of redoing the parser changes I made so that the scanner doesn't need to recognize package names. When this is done, the parser will be able to deal with schema.function and package.function. Oh, also schema.table.attr too. schema.package.function won't be hard, but it will be messy. The only other part (which is no small one) is to add namespacing to the rest of the backend. I expect that will mean adding a schema column to pg_class, pg_type, and pg_operator. Hmmm... We probably also need a command to create operator classes, and the tables it touches would need a schema column too, and accesses will need to be schema savy. Well, that's a lot for now. Thoughts? Take care, Bill
Re: [HACKERS] schema support, was Package support for Postgres
On Sun, 14 Oct 2001, Bill Studenmund wrote:
On Mon, 15 Oct 2001, Tom Lane wrote:
Bill Studenmund [EMAIL PROTECTED] writes:
For the most part, I think packages and schemas are orthogonal. I'm taking
a cue from Oracle here. Oracle considers packages to be a schema-specific
object.
Nonetheless, it's not clear to me that we need two independent concepts.
Given a name search path that can go through multiple schemas, it seems
to me that you could get all the benefits of a package from a schema.
I've been thinking about this. I've changed my mind. Well, I've come to
realize that you can have multiple schemas in one db, so that multiple
schema support != one db reaching into another.
I still think that schemas and packages are different, but I now think
they are interrelated. And that it shouldn't be too hard to leverage the
package work into schema support. Still a lot of work, but the package
work has shown how to go from one to two in a number of ways. :-)
First off, do you (Tom) have a spec for schema support? I think that would
definitly help things.
Second, can you help me with gram.y? I'm trying to get gram.y to deal with
figuring out if you've typed in packagename.function name, rather than
relying on the lexer to notice you've typed ${identifier}\.${identifier}
where the first identifier is a package name send a terminal saying so.
Twelve r/r conflicts. They involve a conflict between ColId and something
else, and focus on not knowing what reduction to take when seeing a '[',
',', or ')'. Thoughts?
Take care,
Bill
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Package support for Postgres
On Sat, 13 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: session-specific package variables, I think this is assuming a little too much about how a PL might operate. Some PLs already support this in their own language-specific way, with or without packages. Thus, I don't think packages should touch this. Actually, I think you could easily set up session variables in the package initializer function. Could you please give me an example of how to do this, say for plperl or plpython? Just showing how two functions made with CREATE FUNCTION can use global variables will be fine. This example will help me understand how they work. Take care, Bill ---(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
[HACKERS] Package support diffs
are on their way to the patches list. Given the mail delay we've been
seeing, they'll take a while to get there. Oh, it turns out there _is_ a
size limit for patches, so it'll need to get approved.
There are still a few warts in the code.
1) One wart is that I needed to make an identifier for the oid for the
standard package. The oid in question is 10, and the identifier is
STANDARDPackageId. I think I will change it to StandardPackageId.
The question I have is in which file should I store the define defining
it?
2) Another problem is dealing with the ambiguity between
relation.attribute and package.functionname. The present code does it by
changing scan.l to recognize ${identifier}\.${identifier}, and if the
first identifier isn't a key word, look to see if it is a package (scan
pg_packages for the name). If so, the scanner returns a different token,
PACKID, than IDENT.
I'll see what I can do about moving all of this into the parser, and
defering the pg_packages scan until later.
I think I got rid of all of the debugging comments; please let me know if
I didn't.
Take care,
Bill
---(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] Package support for Postgres
On Wed, 17 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: Yes, I want a namespace below schemas. The difference between packages and schemas is that schemas encapsulate everything. As Tom pointed out, that includes types (and I'd assume operators too). Packages do not encapsulate types and operators. Of course nobody is forcing you to put types into subschemas. But the user would have the freedom to spread things around as he sees fit. ??? Then PL/pgSQL should be fixed. But that doesn't need a such a large Why is PL/pgSQL broken? Maybe read fixed as enhanced. The problem is not creating persistent storage; the issue is that the langyage was designed to not use it. What you're proposing could be done, but would effectivly be shoving the change in with a hammer. Also, any other PLs which are based on languages with strict namespaces will have the same problem. Other PLs have shown that storing global data in a language-typical way *is* possible. I read your argumentation as PL/pgSQL is not designed to have global variables, so I'm going to implement 'packages' as a way to make some anyway. Either PL/pgSQL is not designed for it, then there should not be any -- at all. Or it can handle them after all, but then it's the business of the language handler to deal with it. Do you really think that my employer paid me for three months to come up with an 800k diff _just_ to add global variables to PL/pgSQL? While part of it, global variables are only one part of the work. I would actually say it is a minor one. Honestly, I do not understand why global variables have been such a sore point for you. PLs for which they don't make sense like this don't have to do it, and Oracle, on whom our Pl/pgSQL was based, thinks that they make perfect sense for the language we copied. Also, remember that this is an implimentation of Oracle packages for Postgres. One of our goals was to make it so that you can mechanically transform an Oracle package into a Postgres one, and vis versa. This implimentation does a good job of that. To make the change you suggest would not. My concern with that is that then we have to make sure to dump it in the same order you entered it. pg_dump can do dependency ordering if you ask it nicely. ;-) When we implement schemas we'll have to make sure it works anyway. Thinking about pg_dump when designing backend features is usually not worthwhile. The thing is what you're talking about is more than just dependency ordering (which I taught pg_dump to do for packages). doing things in the order you list to me means that things get dumped in the exact same order. Say you added some functions and then some operators and then some functions. If order matters, then the operators should get generated in the dump before the functions, even though there's no dependency-reason to do so. Maybe I'm taking that a bit more literal than you mean, but how it comes across to me is unnecessarily difficult. We cah achieve the same thing other ways. I did however take your point that BEFORE TYPE FUNCTION should go away; the patch I sent in does not have it. In the patch, stanzas in the CREATE PACKAGE command are gathered, and done in sequence according to kind. First the global variables are defined, then the initialization routines, then functions which are needed for types in the package, then types, then functions (other than the ones already done), aggregates, and operators. Take care, Bill ---(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] Package support for Postgres
On Sun, 14 Oct 2001, Peter Eisentraut wrote: I have been pondering a little about something I called package, completely independent of anything previously implemented. What I would like to get out of a package is the same thing I get out of package systems on operating systems, namely that I can remove all the things that belong to the package with one command. Typical packages on PostgreSQL could be the PgAccess admin tables or the ODBC catalog extensions. One might think that this could also be done with schemas. I'm thinking using schemas for this would be analogous to installing one package per directory. Now since we don't have to deal with command search paths or file system mount points there might be nothing wrong with that. Packages typically also have post-install/uninstall code, as does this proposed implementation, so that would have to be fit in somewhere. This is basically where my thinking has stopped... ;-) Now I'm also confused as to what this package system really represents: Is it a namespace mechanisms -- but Oracle does have schemas; or is it a package manager like I had in mind -- for that it does too many things that don't belong there; or is it a mechanism to set up global variables -- that already exists and doesn't need packages. It is an implimentation of Oracle Packages for PostgreSQL, taking advantage of some of PostgreSQL's abilities (the aggregates operators in a package bit is new). It is a tool to help developers create large projects and/or reuse code. It is not schema support; schema support operates on a level above package support. It is also not the package support you had in mind. That support is different. What you describe above is packaging which primarily helps the admin, while this packaging primarily helps the procedure developer. That difference in emphasis is why this package support does things an administrator-focused package system wouldn't. Also, please note that while many of PostgreSQL's procedure languages might not need global variable support, PL/pgSQL does. Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] How do I get the current time in seconds in the unix
On Mon, 15 Oct 2001, Christopher Kings-Lynne wrote: Hmmm. I don't know why date_part isn't working, but I now only use the EXTRACT syntax for maximum SQL compatibility. ie. Do this instead: v_seed := EXTRACT (EPOCH FROM CURRENT_TIMESTAMP); Unfortunatly that gives the same error. I think the problem is that the underlying code isn't liking the EPOCH timezone. Tom mentioned he had patches. Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ecpg - GRANT bug
On Tue, 16 Oct 2001, Lee Kindness wrote:
And the patch below corrects a pet peeve I have with ecpg, all errors
and warnings are output with a line number one less than reality...
I think this patch is wrong. Wouldn't it be better to make the line number
in yylineno be correct? Also, there are users of the line number in pcg.l
which you didn't change.
Looking at it, I don't see why the line number is off. It is initialized
to 1 at the begining and whenever a new file is included. In the generated
code, it is incrimented whenever a '\n' is found. Strange...
Take care,
Bill
Lee.
*** ./interfaces/ecpg/preproc/preproc.y.orig Tue Oct 16 10:19:27 2001
--- ./interfaces/ecpg/preproc/preproc.y Tue Oct 16 10:19:49 2001
***
*** 36,49
switch(type)
{
case ET_NOTICE:
! fprintf(stderr, %s:%d: WARNING: %s\n, input_filename, yylineno,
error);
break;
case ET_ERROR:
! fprintf(stderr, %s:%d: ERROR: %s\n, input_filename, yylineno, error);
ret_value = PARSE_ERROR;
break;
case ET_FATAL:
! fprintf(stderr, %s:%d: ERROR: %s\n, input_filename, yylineno, error);
exit(PARSE_ERROR);
}
}
--- 36,52
switch(type)
{
case ET_NOTICE:
! fprintf(stderr, %s:%d: WARNING: %s\n, input_filename,
! yylineno + 1, error);
break;
case ET_ERROR:
! fprintf(stderr, %s:%d: ERROR: %s\n, input_filename,
! yylineno + 1, error);
ret_value = PARSE_ERROR;
break;
case ET_FATAL:
! fprintf(stderr, %s:%d: ERROR: %s\n, input_filename,
! yylineno + 1, error);
exit(PARSE_ERROR);
}
}
---(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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Package support for Postgres
On Tue, 16 Oct 2001, Peter Eisentraut wrote: Bill Studenmund writes: I disagree. Views and tables are the purview of schemas, which as I mentioned to Tom, strike me as being different from packages. Well, obviously schemas are a namespacing mechanism for tables and views. And apparently the packages you propose are (among other things) a namespacing mechanism for functions. But the fact is that schemas already provide a namespacing mechanism for functions. (That's what SQL says and that's how it's going to happen.) Now perhaps you want to have a namespacing mechanism *below* schemas. But then I think this could be done with nested schemas, since the sub-schemas would really be the same concept as a top-level schema. That would be a much more general mechanism. Yes, I want a namespace below schemas. The difference between packages and schemas is that schemas encapsulate everything. As Tom pointed out, that includes types (and I'd assume operators too). Packages do not encapsulate types and operators. That's what makes them different from a sub-schema (assuming a sub-schema is a schema within a schema). Obviously there is a large number of ideas that make life easier. But I'm still missing a clear statement what exactly the design idea behind these packages is. So far I understood namespace and global variables for PL/pgSQL. For the namespace thing we've already got a different design. For global variables, see below. See above. I agree that some PLs might do things their own way and so package variables won't be as useful. If these variables are not appropriate to a PL, it can ignore them. PL/pgSQL is a counter-example, though, showing that something needs to be done. Then PL/pgSQL should be fixed. But that doesn't need a such a large Why is PL/pgSQL broken? It has a very clean design element; you enter a code block, you get a new namespace. You can declare variables in that namespace if you want. When you use a variable name, PL/pgSQL looks in the current namespace, then the parent, and so on. You exit a code block, the namespace goes away. That's how C works, for instance. concept as packages. It could be as easy as DECLARE GLOBAL ... BEGIN ... END It is not set up to support global variables; each code block generates its own namespace, and removes it on the way out. Thus I can not see a clean way to add package global variables to say the initialization routine - this routine's exit code would need to not destroy the context. That strikes me as a mess. The language handler should have no problem creating persistent storage -- I don't see that as a problem. If the language is misdesigned that it cannot be done (which I doubt, but consider the theoretical case) then the language should be replaced by something better, but please keep in mind that it's a PL/pgSQL problem only. Maybe if you're from an Oracle background this separation is not quite as natural. The problem is not creating persistent storage; the issue is that the langyage was designed to not use it. What you're proposing could be done, but would effectivly be shoving the change in with a hammer. Also, any other PLs which are based on languages with strict namespaces will have the same problem. Look at C for instance. What you're describing is the equivalent to letting a function or procedure in C declare global variables. That's not now the language works, and no one seems to mind. :-) Right, that's why I suggested allowing the CREATE statements in any order so you could order them yourself to have the function before the types or whatever you want. My concern with that is that then we have to make sure to dump it in the same order you entered it. Right now, in general, pg_dump dumps objects in stages; all of the languages are dumped, then all of the types, then the functions, and so on. Functions needed for types and languages get dumped right before the type or language which needs it. If we go with strict package order mattering, then pg_dump needs to be able to recreate that order. That means that it has to look in pg_proc, pg_operator, pg_type, and pg_aggreagate, sort things (in the package being dumped) by oid, and dump things in order of increasing oid. Nothing else in pg_dump works like that. I'd rather not start. I have however come up with another way to make BEFORE TYPE FUNCTION go away. I'll just scan the types in a package (I doubt there will be many), get a set of candidate names, and scan the functions in the package for them. If they are found, they get added before the types do. So then the decision as to when a function should get added is implicit, rather than explicit. I'll see about adding this before I send in the patch (it is the only thing left). Take care, Bill ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail
Re: [HACKERS] Package support for Postgres
On Sat, 13 Oct 2001, Jean-Michel POURE wrote: What do folks think? Take care, Bill Hello Bill, The community have been waiting for packages for a long time. I don't believe you did it!!! IMHO most applications do not fully benefit from the power of PostgreSQL because transactions are performed at application lever (PHP/asp/Java/Application server). Sometimes, libraries are mapped to database structure, which is nonsense when a simple view with left joins can solve a problem. Most applications should be developed/ported at PostgreSQL level using the full range of available tools (transactions, triggers, views, foreign keys, rules and off course PL/pgSQL). This is much easier and powerful. Then, all you need is to display information using a good object-oriented language (Java/PHP). With the help of packages, a lot of developers will probably release GPL libraries and PostgreSQL will become the #1 database in the world. Yep. PostgreSQL is within reach of really challenging the commercial databases. I think the core developers are working on the changes needed to challenge the commercial db's in terms of speed and performance for big datastores (WAL, working to prevent OID rollover, etc.). Packages address a different side of what will be needed to challenge the big boys - better stored procedure support. :-) At pgAdmin team, we were thinking of developing packages at client level. This is nonsense when reading your paper. The ability of defining context levels is a great feature. Question: how do you map package to PostgreSQL objects (tables, views, triggers)? Is there any possibility of defining templates? Can this be added to packages in the future with little impact on PostgreSQL internals? Packages don't really map to DB objects (tables, views, triggers) at the moment. Have you used Oracle much? These packages are a direct translation of Oracle packages, with a few PostgreSQL extentions thrown in (Oracle doesn't have PostgreSQL's ability to add aggregates, operators, and system types AFAIK, so their packages likewise don't, and types in packages AFAIK are package-specific). I forget who said it, but operators (and aggregates) are basically just sugar wrapped around functions; these packages are another form of sugar wrapped around functions. To start adding views and tables and triggers makes packages more than just special sugar around functions. Also, my big concern is that if we start adding tables and views and triggers to packages, pg_dump becomes a nightmare. Now, we can only thank you for bringing Packages to PostgreSQL. You're welcome. Take care, Bill ---(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] schema support, was Package support for Postgres
On Mon, 15 Oct 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: For the most part, I think packages and schemas are orthogonal. I'm taking a cue from Oracle here. Oracle considers packages to be a schema-specific object. Nonetheless, it's not clear to me that we need two independent concepts. Given a name search path that can go through multiple schemas, it seems to me that you could get all the benefits of a package from a schema. I'm not necessarily averse to accepting Oracle's syntax for declaring packages --- if we can make it easier for Oracle users to port to Postgres, that's great. But I'm uncomfortable with the notion of implementing two separate mechanisms that seem to do the exact same thing, ie, control name visibility. I'm at a loss as to what to say. I think that what packages do and what schemas do are different - they are different kinds of namespaces. That's why they should have different mechanisms. Packages are for making it easier to write stored procedures for large programming projects or for code reuse. Schemas, well, I need to learn more. But they strike me more as a tool to partition entire chunks of a database. Also, packages have a whole concept of initialization routines and global variables, which strike me as having no place alongside tables and views. Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] schema support, was Package support for Postgres
On Mon, 15 Oct 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: For the most part, I think packages and schemas are orthogonal. I'm taking a cue from Oracle here. Oracle considers packages to be a schema-specific object. Nonetheless, it's not clear to me that we need two independent concepts. Given a name search path that can go through multiple schemas, it seems to me that you could get all the benefits of a package from a schema. About the best response to this I can come up with is that in its present implimentation, types and operators are not scoped as package-specific. If you declare a type in a package, that type is usable anywhere; you don't have to say package.type. If we did packages via schemas, as I understand it, you would (and should). We both agree that types and the functions that operate on them should be schema-specific. Thus operators should be schema-specific. If we did packages via schemas, I don't see how we would get at operators in packages. If you create a new integer type, would you really want to have to type 3 packname. table.attr to do a comparison? So I guess that's the reason; this package implimentation creates types and operators in the same namespace as built-in types and operators. As I understand schemas, user types (and thus operators) should exist in a schema-specific space. I can see reasons for both, thus I think there is a place for two independent concepts. Take care, Bill ---(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] Package support for Postgres
On Sat, 13 Oct 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: For functions and aggregates, things are a little more complicated. First off, there is a package called standard which contains all types, aggregates, operators, and functions which aren't in a specific package. This includes all of the standard Postgres routines, and anything created with CREATE FUNCTION, CREATE AGGREGATE, CREATE OPERATOR, and CREATE TYPE. Secondly, parsing is always done in terms of a specified package context. If we are parsing an equation in a routine inside of a package, then the package context is that package. If we are just typing along in psql, then the package context is standard. When you specify a function or aggregate, you have two choices. One is to specify a package, and a function in that package, like nametest.process to specify the process function in the nametest package. The other choice is to just give the function's name. The first place Postgres will look is in the package context used for parsing. If it's not there (and that context wasn't standard), then it will look in standard. Hmm. How does/will all of this interact with SQL-style schemas? Independent as I understand it. Schemas (as I understand Oracle schemas) operate at a level above the level where packages operate. The reason I'm concerned is that if we want to retain the present convention that the rowtype of a table has the same name as the table, I think we are going to have to make type names schema-local, just like table names will be. And if type names are local to schemas then so must be the functions that operate on those types, and therefore also operators (which are merely syntactic sugar for functions). This seems like it will overlap and possibly conflict with the decisions you've made for packages. It also seems possible that a package *is* a schema, if schemas are defined that way --- does a package bring anything more to the table? I don't think it conflicts. My understanding of schemas is rather simplistic and practical. As I understand it, they correspond roughly to databases in PG. So with schema support, one database can essentially reach into another one. Package support deals with the functions (and types and in this case aggregates and operators) that schema support would find in the other schemas/databases. I also wonder how the fixed, single-level namespace search path you describe interacts with the SQL rules for schema search. (I don't actually know what those rules are offhand; haven't yet read the schema parts of the spec in any detail...) Should be independent. The searching only happens when you are not in the standard package, and you give just a function name for a function. The searching would only happen in the current schems. If you give a schema name, then I'd expect PG to look in that schema, in standard, for that function. If you give both a schema and package name, then PG would look in that package in that schema. Also, both operators and functions normally go through ambiguity resolution based on the types of their inputs. How does the existence of a name search path affect this --- are candidates nearer the front of the search path preferred? Offhand I'm not sure if they should get any preference or not. There is no name spacing for operators in my implimentation as to have one strikes me as reducing the utility of having types and operators in a package. For functions (and aggregates), I tried to touch on that in the latter part of my message; that's what the example with process(changer(4)) was about. PG will try to type coerce a function in the current package before it looks in standard. So yes, candidates nearer the front are prefered. I'd like to see schemas implemented per the spec in 7.3, so we need to coordinate all this stuff. Sounds good. I don't think it will be that hard, though. :-) Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] How do I get the current time in seconds in the unix epoch?
I have a function in PL/pgSQL which needs the current time in seconds
expressed as an int4. In 7.1 I was able to get this (I thought) with
date_part(''epoch'', timestamp ''now'') . That doesn't seem to work for me
in last week's -current.
Here's the PLpgSQL:
v_seed := date_part(''epoch'', timestamp ''now'');
And here's the output:
NOTICE: Error occurred while executing PL/pgSQL function
NOTICE: line 4 at assignment
ERROR: Timestamp with time zone units 'epoch' not recognized
What's the best way to do this?
Take care,
Bill
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] cvs problem
On Mon, 1 Oct 2001, Bruce Momjian wrote: Marc, it worked, but now I'm again getting: cvs server: failed to create lock directory for /projects/cvsroot/pgsql/contrib/pgcrypto/expected' (/projects/cvsroot/pgsql/contrib/pgcrypto/expected/#cvs.lock): Permission denied cvs server: failed to obtain dir lock in repository /projects/cvsroot/pgsql/contrib/pgcrypto/expected' cvs [server aborted]: read lock failed - giving up Seems, again wrong permissions Those are directories I just created. They have the same permission as all the other files here. Maybe there is a problem with CVS server creating stuff with the wrong permission. Please also check things on the anoncvs server (if it's different). Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] SIGCHLD handler in Postgres C function.
On Sun, 22 Jul 2001, Tatsuo Ishii wrote: [EMAIL PROTECTED] writes: I have written a postgres C function that uses a popen linux system call. Orginally when I first tried it I kept getting an ECHILD. I read a little bit more on the pclose function and the wait system calls and discoverd that on LINUX if the signal handler for SIGCHLD is set to SIG_IGN you will get the ECHILD error on pclose(or wait4 for that matter). So I did some snooping around in the postgres backend code and found that in the traffic cop that the SIGCHLD signal handler is set to SIG_IGN. So in my C function right before the popen call I set the signal handler for SIGCHLD to SIG_DFL and right after the pclose I set it back to SIG_IGN. I tested this and it seems to solve my problem. Just ignore ECHILD. It's not messy at all. :-) It sounds like your kernel is using SIG_IGN to do the same thing as the SA_NOCLDWAIT flag in *BSD (well NetBSD at least). When a child dies, it gets re-parrented to init (which is wait()ing). init does the child-died cleanup, rather than the parent needing to. That way when the parent runs wait(), there is no child, so you get an ECHILD. All ECHILD is doing is saying there was no child. Since we aren't really waiting for the child, I don't see how that's a problem. Take care, Bill ---(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] SIGCHLD handler in Postgres C function.
On Mon, 30 Jul 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: All ECHILD is doing is saying there was no child. Since we aren't really waiting for the child, I don't see how that's a problem. You're missing the point: on some platforms the system() call is returning a failure indication because of ECHILD. It's system() that's broken, not us, and the issue is how to work around its brokenness without sacrificing more error detection than we have to. I think I do get the point. But perhaps I didn't make my point well. :-) I think the problem is that on some OSs, setting SIGCHLD to SIG_IGN actually triggers automatic child reaping. So the problem is that we are: 1) setting SIGCHLD to SIG_IGN, 2) Calling system(), and 3) thinking ECHILD means something was really wrong. I think 4.4BSD systems will do what we expect (as the NO_CHLDWAIT flag requests child reaping), but linux systems will give us the ECHILD. Looking at source on the web, I found: kernel/signal.c:1042 * Note the silly behaviour of SIGCHLD: SIG_IGN means that the * signal isn't actually ignored, but does automatic child * reaping, while SIG_DFL is explicitly said by POSIX to force * the signal to be ignored. So we get automatic reaping on Linux systems (which isn't bad). If automatic reaping happens, system will give us an ECHILD as the waitpid (or equivalent) will not have found a child. :-) My suggestion is just leave the ifs as if ((error == 0) || (error == ECHLD)) (or the inverse). Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] SIGCHLD handler in Postgres C function.
On Mon, 30 Jul 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: Looking at source on the web, I found: kernel/signal.c:1042 * Note the silly behaviour of SIGCHLD: SIG_IGN means that the * signal isn't actually ignored, but does automatic child * reaping, while SIG_DFL is explicitly said by POSIX to force * the signal to be ignored. Hmm, interesting. If you'll recall, the start of this thread was a proposal to change our backends' handling of SIGCHLD from SIG_IGN to SIG_DFL (and get rid of explicit tests for ECHILD). I didn't quite see why changing the handler should make a difference, but above we seem to have the smoking gun. Which kernel, and which version, is the above quote from? Linux kernel source, 2.4.3, I think i386 version (though it should be the same for this bit, it's supposed to be MI). Check out http://lxr.linux.no/source/ I do recall the reason for the thread. :-) I see three choices: 1) Change back to SIG_DFL for normal behavior. I think this will be fine as we run w/o problem on systems that lack this behavior. If turning off automatic child reaping would cause a problem, we'd have seen it already on the OSs which don't automatically reap children. Will a backend ever fork after it's started? 2) Change to DFL around system() and then change back. 3) Realize that ECHILD means that the child was auto-reaped (which is an ok think and, I think, will only happen if the child exited w/o error). Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: plpgsql: Debug function?
On 26 Jul 2001, Turbo Fredriksson wrote: Tom == Tom Lane [EMAIL PROTECTED] writes: Would that be the 'debug_print_query = true' in posgresql.conf? It IS true, but still nothing in the syslog... I'm not sure. But I expect the logging would go out the postmaster's logs, not necessarily syslog. Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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: [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: [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
On Tue, 17 Jul 2001, Tom Lane wrote: Seems like a bad idea; it'll slow down deletes quite a lot, no? Do you really want to (for example) parse every SQL function in the system to see if it refers to a table being dropped? Why would we want to do that work over again for every such delete, rather than doing it once when an object is created and storing the info in a table? Also consider that what you are proposing is (at least) an O(N^2) algorithm when there are a large number of objects. I think it's actually O(N^M) where there are N system objects and a chain of M dependencies (A depends on B which depends on C = M = 3). Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_depend
On Tue, 17 Jul 2001, Peter Eisentraut wrote: Tom Lane writes: The alternative to pg_depend is to do a brute force scan of all the system catalogs looking for dependent objects. In that case, you'd know what you are looking at, but if we extract the dependencies as a separate table, I don't see how you'd know without being told. The former is what I'm advocating. Why? It's grossly inefficient and requires lots of effort. And scales horribly to adding new things which can depend on others. Following that argument (admittedly to an extreme conclusion), we should rip out index support. After all, all of the info in the index is stored in the table, we don't need to duplicate it elsewhere. pg_depend is a concise way to encode dependencies. We do all of the work at insert, where we know what depends on what. To not have pg_depend means that on delete, we have to scan EVERYTHING to see what depends on what we're dropping. If we find something (and are CASCADEing), we have to check and see if _it_ depends on anything (another complete scan). We have to keep doing complete scans until we find nothing. Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
On Tue, 17 Jul 2001, Tom Lane wrote: Bill Studenmund [EMAIL PROTECTED] writes: I think it's actually O(N^M) where there are N system objects and a chain of M dependencies (A depends on B which depends on C = M = 3). It's probably not *that* bad. It's reasonable to assume that only a small number of objects actually depend directly on any one object you might want to delete. (Performance of deleting, say, the int4 datatype is probably not of major interest ;-) ...) Only for those objects, not for all N, would you need to descend to the next level of search. Ah yes. It'll be O(ND) where D is the number of dependers (the number of leaves in the dependency tree). Nonetheless, a properly indexed pg_depend table would allow you to find these objects directly, and again to find their dependents directly, etc. The brute force approach would require a rather expensive scan over all the system catalogs, plus nontrivial analysis for some types of system objects such as functions. Repeating that for each cascaded delete is even less appetizing than doing it once. Indeed. Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
On Tue, 17 Jul 2001, Peter Eisentraut wrote: Alex Pilosov writes: I'm not so convinced on that idea. Assume you're dropping object foo. You look at pg_depend and see that objects 145928, 264792, and 1893723 depend on it. Great, what do you do now? I believe someone else previously suggested this: drop type object [RESTRICT | CASCADE] to make use of dependency info. That was me. The point, however, was, given object id 145928, how the heck to you know what table this comes from? You have three columns, depender, dependee, and the third the oid of the entry of pg_class describing what the depender is. Oh, actually you'd probably need four columns, depender, dependee, depender in pg_class, and dependee in pg_class. Take care, Bill ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Buffer access rules, and a probable bug
Sorry for the delay. On Tue, 3 Jul 2001, Tom Lane wrote: [EMAIL PROTECTED] (Nathan Myers) writes: Also, as hints, would it be Bad(tm) if an attempt to clear one failed? Clearing hint bits is also an exclusive-lock-only operation. Notice I specified that *setting* them is the only case allowed to be done with shared lock. One problem though is that if you don't have a spin lock around the flag, you can end up clearing it inadvertenty. i.e. two backends go to update (different) bit flags. They each load the current value, and each set the (different) bit they want to set. They then store the new value they each have come up with. The second store will effectively clear the bit set in the first store. ?? Take care, Bill ---(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] [OT] Any major users of postgresql?
On Tue, 3 Jul 2001, Joe Brenner wrote: The reason I'm asking is that the place that I work is actually contemplating reverting from Oracle's expensive bugs to MySQL's (supposedly) cheap ones. They'd consider postgresql, but they figure that with MySQL they can at least point to sites that pump a fair amount of data with it (e.g. mp3.com). Please help save me from a life without referential integrity... sourceforge for one. They were using MySQL, then changed. Also, look at the postgres web site - there is an article there were someome did a speed comparison between PG MySQL. Postgres came out on top, even in places where folks thought MySQL would win. Also, it depends on what your application is. If there is any amount of DB updates, PG will easily be the best choice. :-) Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] shared library strangeness?
On Tue, 22 May 2001, Bruce Momjian wrote: I am always confused when to bump the minor and when the major. I also was not sure how significant the change would be for apps. We added const, and I changed the return type of one function from short to int. Seems like ConnectionBad was also changed. Sorry for the delay. You need to bump the minor whenever you add to the library. You need to bump the major whenever you delete from the library or change(*) the interface to a function. i.e. if a program links against the library, as long as the routine names it linked against behave as it expected at compile time, you don't need to bump the major. (*) NetBSD (and I think other OSs too) use a gcc-ism, RENAME, to be able to change the interface seen by new programs w/o changing the minor number. What you do is prototype the function as you want it now, and have a __RENAME(new_name) at the end of the prototype. When you build the library, you have a routine having the old footprint and old name, and a new routine with the new footprint and named new_name. Old programs look for the old name, and get what they expect. New programs look for the new name, and also get what they expect. I'm not sure if Postgres needs to go to that much trouble. Take care, Bill ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Detecting readline in configure
On Mon, 21 May 2001, Peter Eisentraut wrote: Tom Lane writes: checking for libreadline ... no checking for libedit ... no * * NOTICE: I couldn't find libreadline nor libedit. You will * not have history support in psql. * This may be useful as well, but it doesn't help those doing unattended builds, such as RPMs and *BSD ports. In that case you need to abort to notify the user that things didn't go the way the package maker had planned. *BSD ports/packages shouldn't have much of a problem. They can encode dependencies, both in the binary package and in the build-from-source process. So if the package maker did things right, the packaging system would have either squalked, or tried to install libreadline before running configure. Take care, Bill ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Test results for postgresql-7.1RC2 on NetBSD/macppc 1.5
A friend of mine (Matthew Green) mentioned that 7.1RC2 had NetBSD/powerpc down as unttested, and asked me to test it. So here are the results: On my NetBSD/macppc system running NetBSD 1.5, gmake check reported that 2 of 62 tests failed. I've attached regression.diff to this message. The two that failed were geometry and horology. From looking at the output, the horology test seemed to get confused about daylight savings time. The geometry test seems to be a difference in the last digit in two of the numbers: Expect: Got: -1.33012701887967 -1.33012701887966 0.5081028 0.5081027 Take care, Bill *** ./expected/geometry-positive-zeros.out Tue Sep 12 14:07:16 2000 --- ./results/geometry.out Thu Apr 5 03:04:38 2001 *** *** 445,451 -+- | ((-3,0),(-2.59807621135076,1.500442),(-1.499116,2.59807621135842),(1.53102359017709e-11,3),(1.501768,2.59807621134311),(2.59807621136607,1.49779),(3,-3.06204718035418e-11),(2.59807621133545,-1.503094),(1.496464,-2.59807621137373),(-4.59307077053127e-11,-3),(-1.50442,-2.5980762113278),(-2.59807621138138,-1.495138)) | ((-99,2),(-85.6025403783588,52.01473),(-48.97054,88.602540378614),(1.051034,102),(51.05893,88.6025403781036),(87.6025403788692,51.92634),(101,1.897932),(87.6025403778485,-48.10313),(50.88214,-84.6025403791243),(0.8468976,-98),(-49.14732,-84.6025403775933),(-85.6025403793795,-47.83795)) ! | ((-4,3),(-3.33012701891794,5.500737),(-1.498527,7.3301270189307),(1.002552,8),(3.502946,7.33012701890518),(5.33012701894346,5.496317),(6,2.994897),(5.33012701889242,0.4948437),(3.494107,-1.33012701895622),(0.9923449,-2),(-1.507366,-1.33012701887967),(-3.33012701896897,0.5081028)) | ((-2,2),(-1.59807621135076,3.500442),(-0.4991161,4.59807621135842),(1.001531,5),(2.501768,4.59807621134311),(3.59807621136607,3.49779),(4,1.996938),(3.59807621133545,0.4969062),(2.496464,-0.598076211373729),(0.9954069,-1),(-0.5044197,-0.598076211327799),(-1.59807621138138,0.5048616)) | ((90,200),(91.3397459621641,205.0015),(95.00295,208.660254037861),(100.0051,210),(105.0059,208.66025403781),(108.660254037887,204.9926),(110,199.9898),(108.660254037785,194.9897),(104.9882,191.339745962088),(99.98469,190),(94.98527,191.339745962241),(91.3397459620621,195.0162)) | ((0,0),(13.3974596216412,50.01473),(50.02946,86.602540378614),(100.051,100),(150.0589,86.6025403781036),(186.602540378869,49.92634),(200,-1.02068239345139e-09),(186.602540377848,-50.10313),(149.8821,-86.6025403791243),(99.8469,-100),(49.85268,-86.6025403775933),(13.3974596206205,-49.83795)) --- 445,451 -+- | ((-3,0),(-2.59807621135076,1.500442),(-1.499116,2.59807621135842),(1.53102359017709e-11,3),(1.501768,2.59807621134311),(2.59807621136607,1.49779),(3,-3.06204718035418e-11),(2.59807621133545,-1.503094),(1.496464,-2.59807621137373),(-4.59307077053127e-11,-3),(-1.50442,-2.5980762113278),(-2.59807621138138,-1.495138)) | ((-99,2),(-85.6025403783588,52.01473),(-48.97054,88.602540378614),(1.051034,102),(51.05893,88.6025403781036),(87.6025403788692,51.92634),(101,1.897932),(87.6025403778485,-48.10313),(50.88214,-84.6025403791243),(0.8468976,-98),(-49.14732,-84.6025403775933),(-85.6025403793795,-47.83795)) ! |
