Re: [HACKERS] Remove implicit unique index creation on SERIAL columns?
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: I agree 100%. If you want an index, unique constraint, or primary key on a SERIAL, I think you should explicitly add it. SERIAL should give me a column that automatically increments -- no more, no less. Hmm, do you also want to eliminate the implicit NOT NULL constraint? I think that efficiency and orthogonality are adequate reasons for dissociating UNIQUE from SERIAL. The efficiency argument is pretty weak in the case of the NOT NULL part, though, so maybe backwards compatibility should win out there. To be honest I wasn't thinking about NOT NULL. I'd agree with leaving that in place. Maybe I should restate my comment above: SERIAL should give me a column that automatically increments -- no more, no less -- and it should not allow me to override the value that it gives. Hence an implicit NOT NULL, but also an implicit rejection of a manual insert/update of that field (how hard would this be to do?). I know this causes problems for dumped and reloaded data. In MSSQL this is gotten around by allowing the properties of the data type to be altered, e.g. in MSSQL you can turn the IDENTITY property on or off (in fact, I just checked and MSSQL won't allow you to turn on IDENTITY for a column unless you turn off ALLOW NULLS). You can also specify an exception to the rule when running BCP (the bulk loader command line program). The reason I think this behavior is good, is that it helps prevent toe loss from stray bullets. E.g. you manually add a row where you've specified some value that has not yet been reached by the sequence -- then when someday the sequence reaches said value, your insert fails on a duplicate primary key insertion attempt. If you really need to be able to insert or update a field with an explicit value *sometimes* (and you really know what you're doing), then use a plain sequence and a default, not a SERIAL. Anyway, just my thoughts. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Better handling of parse errors
Gavin Sherry writes: In that case, attached is a patch which locates the beginning of the offending token more efficiently (per your suggestion of using scanbuf). In the regression tests there are a couple of cases that could be improved: In strings.sql: -- illegal string continuation syntax SELECT 'first line' ' - next line' /* this comment is not allowed here */ ' - third line' AS Illegal comment within continuation; ERROR: parser: parse error at or near ' - third line' at character 89 Character 89 is the end of the third line line, but the parse error is at the beginning of that line. In create_function_1.sql: CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql AS 'not even SQL'; ERROR: parser: parse error at or near not at character 1 Clearly confusing. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Open 7.3 items
Tom Lane writes: BTW, I just thought of a small improvement to your patch that eliminates some of the ugliness. Suppose that when we recognize an attempt to connect as a global user (ie, feature flag is on and last character of username is '@'), we strip off the '@' before proceeding. I'm missing how hard it is to change last character of username is @ to no @ in username. This would seem to be a two-line change somewhere. I'm concerned that we leave essentially no migration path, that is, the ability to turn the feature on to try it out without immediately breaking every application. -- Peter Eisentraut [EMAIL PROTECTED] ---(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] [SECURITY] DoS attack on backend possible (was: Re:
Hi, -- [EMAIL PROTECTED] wrote: What about checking the input for backslash, quote, and double quote (\')? If you are not taking care of those in input then crashing the backend is going to be the least of your worries. with Perl and *using placeholders and bind values*, the application developer has not to worry about this. So, usually I don't check the values in my applications (e.g. if only values between 1 and 5 are allowed and under normal circumstances only these are possible), it's the task of the database (check constraint). Ciao Alvar -- ** ODEM ist für den poldi Award nominiert! http://www.poldiaward.de/ ** http://www.poldiaward.de/index.php?display=detailcat=audiitem=24 ** http://odem.org/ ** Mehr Projekte: http://alvar.a-blast.org/ ---(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] Remove implicit unique index creation on SERIAL columns?
Hi -*- Joe Conway [EMAIL PROTECTED] [ 2002-08-18 06:36 ]: Maybe I should restate my comment above: SERIAL should give me a column that automatically increments -- no more, no less -- and it should not allow me to override the value that it gives. Hence an implicit NOT NULL, but also an implicit rejection of a manual insert/update of that field (how hard would this be to do?). I know this causes problems for dumped and reloaded data. In MSSQL this is gotten around by allowing the properties of the data type to be altered, e.g. in MSSQL you can turn the IDENTITY property on or off (in fact, I just checked and MSSQL won't allow you to turn on IDENTITY for a column unless you turn off ALLOW NULLS). You can also specify an exception to the rule when running BCP (the bulk loader command line program). The reason I think this behavior is good, is that it helps prevent toe loss from stray bullets. E.g. you manually add a row where you've specified some value that has not yet been reached by the sequence -- then when someday the sequence reaches said value, your insert fails on a duplicate primary key insertion attempt. If you really need to be able to insert or update a field with an explicit value *sometimes* (and you really know what you're doing), then use a plain sequence and a default, not a SERIAL. I like the functionality you are suggesting. However, when I started thinking about what size this field should be, the idea of keeping the current SERIAL data type and having a SERIAL flag (CREATE TABLE foo (id int serial))... so it's pretty much the same as a seqence, but enforced so that you can't tamper with it. The best part about this is obviously that it doesn't change behaviour of the old SERIAL. Am I right about this, or am I missing something here? -- Regards, Tolli [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Remove implicit unique index creation on SERIAL
On Sun, 2002-08-18 at 02:35, Joe Conway wrote: Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: I agree 100%. If you want an index, unique constraint, or primary key on a SERIAL, I think you should explicitly add it. SERIAL should give me a column that automatically increments -- no more, no less. Hmm, do you also want to eliminate the implicit NOT NULL constraint? I think that efficiency and orthogonality are adequate reasons for dissociating UNIQUE from SERIAL. The efficiency argument is pretty weak in the case of the NOT NULL part, though, so maybe backwards compatibility should win out there. To be honest I wasn't thinking about NOT NULL. I'd agree with leaving that in place. Maybe I should restate my comment above: SERIAL should give me a column that automatically increments -- no more, no less -- and it should not allow me to override the value that it gives. Hence an implicit NOT NULL, but also an implicit rejection of a manual insert/update of that field (how hard would this be to do?). I don't like not overriding the value. A dataload example is a perfect reason why you would want to. Anyway, this would require placing 2 triggers on the table in order to prevent changes of the value. Personally I prefer the method that SAPdb uses. They make the column a fixed() type (integer) and set the default. Nothing about NOT NULL or UNIQUE. Anyway, I think SERIAL is about assisting creation of a entry, not enforcing it. Enforcement is trivial for those who don't mind the additional overhead. ---(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] Open 7.3 issues
On Fri, 16 Aug 2002, Bruce Momjian wrote: Peter Eisentraut wrote: Bruce Momjian writes: Socket permissions - only install user can access db by default unix_socket_permissions in postgresql.conf This is dead. Removed, still on TODO. Daft question but isn't this an administrator's issue? Forcing a complete override of umask isn't usually considered a decent, friendly thing to do and the sys admin can change the permission bits. Besides, what was the proposed scheme? user/group writable world not I presume. I wouldn't even know how to port that to un unixy systems like Win32. -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Remove implicit unique index creation on SERIAL columns?
Joe Conway [EMAIL PROTECTED] writes: I know this causes problems for dumped and reloaded data. Yup. In MSSQL this is gotten around by allowing the properties of the data type to be altered, e.g. in MSSQL you can turn the IDENTITY property on or off Rather pointless if it can be turned off, wouldn't you say? What I would do if I wanted such a guarantee is to make insertions go through a view rule that prevents the serial column from being supplied directly. That's a general technique that applies to more than just serial columns, and it is also easy to control who has privileges to bypass the view. AFAICT this IDENTITY thingie is an extremely limited kluge. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Better handling of parse errors
Peter Eisentraut [EMAIL PROTECTED] writes: In strings.sql: -- illegal string continuation syntax SELECT 'first line' ' - next line' /* this comment is not allowed here */ ' - third line' AS Illegal comment within continuation; ERROR: parser: parse error at or near ' - third line' at character 89 Character 89 is the end of the third line line, but the parse error is at the beginning of that line. This is fixed as of my later commit. In create_function_1.sql: CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql AS 'not even SQL'; ERROR: parser: parse error at or near not at character 1 Clearly confusing. Character 1 is correct as of the context that the parser is working in, namely the function body. I don't think we can do much to change that, but perhaps we could make the message read like ERROR: parser: parse error at or near not at character 1 of function body This would require giving the parser some sort of context-identifying string to tack onto the message, but that doesn't seem too hard. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
Peter Eisentraut [EMAIL PROTECTED] writes: I'm concerned that we leave essentially no migration path, that is, the ability to turn the feature on to try it out without immediately breaking every application. Uh ... what? I fail to understand your objection. AFAICS the only apps that could be broken are scripts that have usernames hardwired into them ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Remove implicit unique index creation on SERIAL
In MSSQL this is gotten around by allowing the properties of the data type to be altered, e.g. in MSSQL you can turn the IDENTITY property on or off Rather pointless if it can be turned off, wouldn't you say? What I would do if I wanted such a guarantee is to make insertions go through a view rule that prevents the serial column from being In the future if we have per column privileges, we could disable insert and update on serial columns by default for everyone but the table owner and super-users (to accommodate dataloads). Till then a trigger or view will do the job for those who wish to add them. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Better handling of parse errors
On Sun, 18 Aug 2002, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: In strings.sql: -- illegal string continuation syntax SELECT 'first line' ' - next line' /* this comment is not allowed here */ ' - third line' AS Illegal comment within continuation; ERROR: parser: parse error at or near ' - third line' at character 89 Character 89 is the end of the third line line, but the parse error is at the beginning of that line. This is fixed as of my later commit. In create_function_1.sql: CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql AS 'not even SQL'; ERROR: parser: parse error at or near not at character 1 Clearly confusing. Character 1 is correct as of the context that the parser is working in, namely the function body. I don't think we can do much to change that, but perhaps we could make the message read like ERROR: parser: parse error at or near not at character 1 of function body This would require giving the parser some sort of context-identifying string to tack onto the message, but that doesn't seem too hard. Tom, Reworking the code to taken into account token_start seems to work. elog(ERROR, parser: %s at or near \%s\ at character %i, message,token_start ? token_start : yytext, token_start ? (unsigned int)(token_start - scanbuf + 1) : (unsigned int)(yytext - scanbuf + 1)); I will submit a patch once I do some more testing to find other possible situations where this plays up. Gavin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Remove implicit unique index creation on SERIAL columns?
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: In MSSQL this is gotten around by allowing the properties of the data type to be altered, e.g. in MSSQL you can turn the IDENTITY property on or off Rather pointless if it can be turned off, wouldn't you say? Not really. Turning it off takes an explict act of the dba (ALTER TABLE) for a special circumstance such as importing data. In the normal case while it's turned on, it is the developers who need and get the foot teflon (INSERT/UPDATE). What I would do if I wanted such a guarantee is to make insertions go through a view rule that prevents the serial column from being supplied directly. That's a general technique that applies to more than just serial columns, and it is also easy to control who has privileges to bypass the view. I thought about the view rule myself. Nice, but beyond the grasp of inexperienced users, who I think SERIAL is target for. AFAICT this IDENTITY thingie is an extremely limited kluge. It is extremely limited -- that's why sequences are so much better (IMHO). But SERIAL is for those who want something simple to use, don't want to think about the implementation details, and don't mind it being limited. Otherwise why have it at all -- just make everyone use sequences. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
On Sat, 17 Aug 2002, Bruce Momjian wrote: OK, I think we are doing this backwards. Instead of adding '@' to global users, and then removing it in the backend, why don't we have local users end with '@', that way, global users continue to connect just as they have before, and local users connect with @, so dave@db1 connects as 'dave@' and if he has other database access, he can use the same 'dave@' name. That removes some of the uglification, I think. Then why was it when I mentioned global users not having the @ you shot it down as not possible? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking http://www.camping-usa.com http://www.cloudninegifts.com http://www.meanstreamradio.com http://www.unknown-artists.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
On Sat, 17 Aug 2002, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, I think we are doing this backwards. Instead of adding '@' to global users, and then removing it in the backend, why don't we have local users end with '@', that way, global users continue to connect just as they have before, and local users connect with @, so dave@db1 connects as 'dave@' and if he has other database access, he can use the same 'dave@' name. No, *that* would be backwards. In installations that are using this feature, the vast majority of the users are going to be local ones. And the global users will be the presumably-more-sophisticated admins. Putting the onus of the '@' decoration on the local users instead of the global ones is exactly the wrong way to go. Unsophisticated users is hardly a reason. After all they do have an @ in their email address. If they're told the username is foo@ then their username is foo@. What's so difficult about that? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking http://www.camping-usa.com http://www.cloudninegifts.com http://www.meanstreamradio.com http://www.unknown-artists.com == ---(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] Open 7.3 issues
Nigel J. Andrews [EMAIL PROTECTED] writes: Daft question but isn't this an administrator's issue? The feature wasn't going to change; the argument was just about whether to change the factory-default permissions mask for the socket. An admin could override the default in any case (and probably should, in many cases). The argument for 0700 was that it is secure, the argument against was that it's useless. No one sane runs their apps as the postgres user, therefore you'd always be having to open it up anyway. We thought a little about 0770 but the problem is that the actual behavior is extremely context-dependent. On some Unixes this is just what you want, on others it's little different from 0777. In any case depending on group membership to control database access means that the DBA *must* have root privileges, which is not an assumption we like to make. So unless someone comes up with a new idea, we're sticking with 0777 for backwards-compatibility reasons. But certainly socket permissions ought to be on the short list of things to think about during every database install. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Remove implicit unique index creation on SERIAL columns?
Rod Taylor wrote: In MSSQL this is gotten around by allowing the properties of the data type to be altered, e.g. in MSSQL you can turn the IDENTITY property on or off Rather pointless if it can be turned off, wouldn't you say? What I would do if I wanted such a guarantee is to make insertions go through a view rule that prevents the serial column from being In the future if we have per column privileges, we could disable insert and update on serial columns by default for everyone but the table owner and super-users (to accommodate dataloads). That sounds like the best way-to-go. Till then a trigger or view will do the job for those who wish to add them. Agreed. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [PATCHES] [HACKERS] Better handling of parse errors
Gavin Sherry [EMAIL PROTECTED] writes: Reworking the code to taken into account token_start seems to work. Yes, I did that last night ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: Removing Libraries (Was: Re: [HACKERS] Open 7.3 issues)
Marc G. Fournier writes: Okay, here is what I'd like to suggest ... Bruce, let's start off really simple ... go create a project for libpq++ (I believe someone even volunteered to maintain it?) and let me know once created, and I'll move the CVS directory over for libpq++ and out of the pgsql-server directory I said that I would lend a hand in preparing the build system, but I'm not going to start a project on gborg if I have to agree to a two-page terms of service agreement and I don't get to choose the exact wording of the license. I would prefer it if libpq++ would stay in the cvs tree at cvs.postgresql.org. There's no point in starting an entirely new infrastructure elsewhere. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] Better handling of parse errors
Tom Lane writes: Character 1 is correct as of the context that the parser is working in, namely the function body. I don't think we can do much to change that, but perhaps we could make the message read like ERROR: parser: parse error at or near not at character 1 of function body That would be better. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Open 7.3 items
Tom Lane writes: Peter Eisentraut [EMAIL PROTECTED] writes: I'm concerned that we leave essentially no migration path, that is, the ability to turn the feature on to try it out without immediately breaking every application. Uh ... what? I fail to understand your objection. AFAICS the only apps that could be broken are scripts that have usernames hardwired into them ... I'm completely lost between all the proposals about where the @ is going to be specified, added, or removed. What happens on the client side and what happens on the server side? All I would like to see is that I can turn on this feature and nothing changes as long as I don't add any local users. Yes, that includes hard-wired user names on the client side. Of course there are various degrees of hard-wiring, but what if the ISP admin updates to 7.3 and wants to turn on the feature for new clients? Does he tell all his existing clients that they must update their user names? Possibly, these users got their database access with a shell account and don't specify the user name at all because it defaults to the OS user name. Does that continue to work? -- Peter Eisentraut [EMAIL PROTECTED] ---(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] Open 7.3 items
Peter Eisentraut [EMAIL PROTECTED] writes: I'm completely lost between all the proposals about where the @ is going to be specified, added, or removed. What happens on the client side and what happens on the server side? Well, the way things stand as of CVS tip is that (assuming you have this feature turned on in postgresql.conf): * If a connection request has a username with a trailing '@' (and no embedded '@'), then the '@' is stripped and connection proceeds. * Otherwise, '@dbname' is appended to the given username and connection proceeds. So a global user foo has to say username=foo@ in his connection request, but he's just foo in pg_shadow. A local user foo has to say foo in his connection request, and he's foo@somedb in pg_shadow. All I would like to see is that I can turn on this feature and nothing changes as long as I don't add any local users. Yes, that includes hard-wired user names on the client side. Well, we could have that by inverting the use of '@'; but as I commented before, it makes more sense to me to make the global users say '@' than to make the local users do so, because I think in an installation that wants this feature there will be lots more local than global users. I really don't put that much weight on the compatibility argument you make --- not that I don't see your point, but that I don't think it outweighs convenience of day-to-day use after one has gotten the system set up. (Also, compatibility cuts both ways: it seems just as likely to me that the clients with hardwired usernames are going to be ones you want to connect as local users, as that they are going to be ones you want to connect as global users. Maybe more likely, if you grant the assumption that there will be more local than global users.) It might be worth recalling the reason that we are going through this pushup in the first place: Marc wants to be able to assign the same username to two different users who want to access two different databases. If he would be happy with the answer give them two different usernames, we'd not be having this discussion at all. Do you think he will be happy with the answer you can give them the same username as long as it ends in '@'? I think it's highly unlikely that he'll be satisfied with that --- he wants to *not* have constraints on the names he gives out for local users. Of course there are various degrees of hard-wiring, but what if the ISP admin updates to 7.3 and wants to turn on the feature for new clients? Does he tell all his existing clients that they must update their user names? Possibly, these users got their database access with a shell account and don't specify the user name at all because it defaults to the OS user name. Does that continue to work? It works great if the ISP intends to make them all local users, which seems more likely to me than the other case. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Better handling of parse errors
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Character 1 is correct as of the context that the parser is working in, namely the function body. I don't think we can do much to change that, but perhaps we could make the message read like ERROR: parser: parse error at or near not at character 1 of function body That would be better. After a quick look through the sources, it seems we could fairly easily do this: callers of pg_parse_and_rewrite() and some related routines could pass a string like SQL function body, which would get plugged into the parse-error message. Two issues though: * Is this okay from an internationalization point of view? We can gettext() the SQL function body string but I don't know if there are serious problems with pasting that into parse error at or near %s at character %d of %s On the other hand I'm not comfortable with having the far-end caller supply that whole string, either, since most of it is the lexer's responsibility. * The natural thing to say in _SPI_execute's call is SPI query, but this will probably not go over big with plpgsql users, who will see that and probably have no idea what SPI is. But I'm very loathe to change the SPI API so that plpgsql can pass down the context string --- that'll break existing user functions that use SPI. Do we want to uglify the SPI API to the extent of having parallel calls that just add a context string parameter? Is there a better way? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] assigning to NULL?
I'm trying to make postGIS work with pg7.3devel. But a problem is occuring that did not appear in pg7.2. When I execute: ALTER TABLE geotest ADD CHECK ( geometrytype(geopoint)='POINT' OR NULL=geopoint); I get: ERROR: copyObject: don't know how to copy node type 506 But when I execute: ALTER TABLE geotest ADD CHECK ( geometrytype(geopoint)='POINT'); It works fine, which, due to the error message it seems that it is trying to assign rather to NULL, rather than compare (else what object needs to be copied in NULL=geopoint?). Is this a bug, a change in NULL, or a change in user defined datatypes? Thanks; Eric ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] assigning to NULL?
[EMAIL PROTECTED] writes: I get: ERROR: copyObject: don't know how to copy node type 506 This is a bug in someone's recent patch ... but you don't want to say NULL=geopoint anyway, do you? Surely it should be geopoint IS NULL. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] About to update the PostgreSQL Functions in the official PHP Manual
Hi everyone, Conni is the author of a German PostgreSQL book, and is also a member of the PHP documentation team. She's updating some parts of the official PHP manual at present, an she'll be up to the PostgreSQL section pretty soon. Does anyone have stuff they'd like to see included, etc? :-) Regards and best wishes, Justin Clift Original Message Subject: [GENERAL] About to update the PostgreSQL-Functions in the PHP Manual Date: Sun, 18 Aug 2002 13:32:21 +0200 From: Cornelia Boenigk [EMAIL PROTECTED] Hi everyone, I am in the process of updating some parts of the PHP manual, and in the next few days the PostgreSQL Functions area is where I'll be up to. Is there anything which anyone would like to be added, clarified, updated, or similar for this? :-) Regards Conni ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Open 7.3 items
[EMAIL PROTECTED] (Tom Lane) wrote * If a connection request has a username with a trailing '@' (and no embedded '@'), then the '@' is stripped and connection proceeds. * Otherwise, '@dbname' is appended to the given username and connection proceeds. snip It might be worth recalling the reason that we are going through this pushup in the first place: Marc wants to be able to assign the same username to two different users who want to access two different databases. If he would be happy with the answer give them two different usernames, we'd not be having this discussion at all. Do you think he will be happy with the answer you can give them the same username as long as it ends in '@'? I think it's highly unlikely that he'll be satisfied with that --- he wants to *not* have constraints on the names he gives out for local users. What about usernames that have trailing or embedded @'s? I mean you are eseentially making the @ a magic character. I admit I havent looked at the source, but doesnt this method effectively put a constraint on the use of @? What if an isp, that could use this feature, already has usernames with @'s in them (say a customers email address, etc)? Will they need to assign all new usernames to make this thing function? What if you want to give one person (one username) access to 2 db's? Does that mean, under the current scheme, that the two accounts you create can have the same username but have different passwords? What if you want to erase the one account (do you have to remember to erase all n accounts you created with the same username, or all n except the ones that were never mean to be the same person but share the same username)? Normally a user has a unique name. Does anyone see a problem if/when the whole db access thing becomes part of the privileges system? If you implement the multiple users same username, then you'll have to reassign all but one of the users to new usernames. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Ident nodetype considered harmful
I've just noticed that a bunch of places use strVal() to access the name field of an Ident node. (Try changing strVal() to #define strVal(v)(AssertMacro(IsA(v, String)), ((Value *)(v))-val.str) and watch the fur fly...) This works, at the moment, because Ident and Value nodes have a similar layout. But it sure looks like trouble waiting to happen. As far as I can tell, there's no really good reason to keep Ident around at all. The only thing it's still used for is lists of column names in a couple of statement types --- which is something that we do elsewhere with lists of String nodes. Indeed I imagine that the buggy places got that way because someone copied-and-pasted code that was legitimately dealing with a list of column names in String form. Also, strVal() and makeString() provide handy notational infrastructure that we don't have for Ident. Considering these points, I'd rather rip out Ident and replace its uses with String nodes than try to clean up the misuses of strVal(). Any objections out there? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] TRUNCATE TODO Item
# Disallow TRUNCATE on tables that are involved in referential constraints The above is on the TODO list. Are there any thoughts as to what we want to do with this? The patch I submitted earlier received heavy feedback saying it wasn't wanted due to rules and triggers (foreign keys) being difficult to drop and re-create. Foreign keys no longer have this issue, as such I'd like to re-apply the check and prevention of TRUNCATE when a foreign key constraint exists. Primary reason is to prevent users from shooting their own feet -- yes, I have structure owners who use Access, MSSql, MySQL, and Oracle on a regular basis. Oracle and MSSql both (if memory serves) prevents this case, so it comes as a surprise the first time they run it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function result cacheing - any comments?
OK - I assume from everybody else's silence that they either (a) agree with the idea, or (b) think Tom hit the idea on the head, so they feel they don't need to respond. So what I would like to do is implement a simple version of this to attempt to justify my claims of performance gains. The sort of trivial places where I think gains *may* be had are: create table departments(id integer, name text, manager_id integer); create table people(id integer, department_id, name text); create function get_manager_name(integer) returns text as 'select name from departments d, people p where d.id = $1 and p.id = d.manager_id'; select name,get_manager_name(department_id) from people; This is obviously a case where a LOJ or column-select would do the trick, *but* it does represent a class of problems that people frequently write procedures to perform a single (sometimes complex) action. Using a function also encapsulates some knowledge of the data structures, resulting in more maintainable code. eg. even the above simple example becomes a lot less readable and maintainable: select name, (select m.name from departments d, people m where d.id = p.department_id and m.id = d.manager_id) as manager_name from people p; if a function is not used. My theory is that if such a piece of code gets a performance gain, then the code is probably worth including, assuming that the function manager does not need to be butchered to achieve the desired goal. Does that sound reasonable? So the obvious question is - in the opinion of people who know the code, can a function-result-cache be implemented with a lifetime of a single statement, without butchering the function manager? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /() __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Removing Libraries (Was: Re: [HACKERS] Open 7.3 issues)
On Sun, 18 Aug 2002, Peter Eisentraut wrote: Marc G. Fournier writes: Okay, here is what I'd like to suggest ... Bruce, let's start off really simple ... go create a project for libpq++ (I believe someone even volunteered to maintain it?) and let me know once created, and I'll move the CVS directory over for libpq++ and out of the pgsql-server directory I said that I would lend a hand in preparing the build system, but I'm not going to start a project on gborg if I have to agree to a two-page terms of service agreement and I don't get to choose the exact wording of the license. I didn't ask you to, I asked Bruce to ... and the license should stay BSD ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Function result cacheing - any comments?
Philip Warner wrote: So the obvious question is - in the opinion of people who know the code, can a function-result-cache be implemented with a lifetime of a single statement, without butchering the function manager? I don't know if I fully understand what you're proposing, but if I understand it correctly, I think the table function feature in current sources does just what you want already. If you can write your function as a table function, the results are put in a tuplestore for the duration of the statement, and rescanned when needed. Your example ends up looking like this: create table departments(id integer, name text, manager_id integer); insert into departments values(1, 'manufacturing', 1); insert into departments values(2, 'accounting', 2); create table people(id integer, department_id, name text); insert into people values(1, 1, 'mfg boss'); insert into people values(2, 2, 'acct boss'); insert into people values(3, 1, 'mfg emp'); insert into people values(4, 2, 'acct emp'); create type manager_names as (dept_id int, name text); create function get_manager_names() returns setof manager_names as 'select d.id, p.name from departments d, people p where p.id = d.manager_id' language sql; select p.name, m.name as boss from people p, get_manager_names() m where p.department_id = m.dept_id; name| boss ---+--- mfg boss | mfg boss mfg emp | mfg boss acct boss | acct boss acct emp | acct boss (4 rows) Is this anything close what you had in mind? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
