Re: [HACKERS] Much Ado About COUNT(*)
Bruce Momjian wrote: Added to TODO based on this discusion:... * Speed up COUNT(*) One think I think would help lots of people is if the documentation near the COUNT aggregate explained some of the techniques using triggers to maintain a count for tables where this is important. For every one person who reads the mailinglist archives, I bet there are dozens who merely read the product documentation and never find the workaround/solution. Perhaps a note below the table here: http://www.postgresql.org/docs/8.0/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE would be a good place. If it is already somewhere in the docs; perhaps the page I linked should refer to the other page as well. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Two-phase commit for 8.1
Heikki, What is still missing to complete the 2PC patch?. Regards, Hans Heikki Linnakangas wrote: On Wed, 19 Jan 2005, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: If the patch is ready to be committed early in the cycle, I'd say most definitely ... just depends on how late in the cycle its ready ... My recollection is that it's quite far from being complete. I had hoped to spend some time during the 8.1 cycle helping Heikki finish it up, but if we stick to the 2-month-dev-cycle idea I'm afraid there's no way it'll be done in time. I thought that some time would probably amount to a solid man-month or so, and there's no way I can spend half my time on just one feature for this cycle. If Heikki wants this in for 8.1, the right thing to do is vote against the short-dev-cycle idea. But we need a plausible answer about what to do about ARC to make that credible... I'm not sure what I want. If the 8.1 cycle really is a short one, say 3 months, then I have no problem waiting for 8.2. But we have a very bad track record regarding short-dev-cycles. I honestly don't believe we can get 8.1 released before July. - Heikki ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 8.1 development cycle (was a couple of other threads
So are we looking at a 8.1 in June and a 8.2 in say August of 2006? Or something else? I know that it is hard to completely pin these things down but it would be really helpful :) I really don't know why this short dev cycle thing keeps coming back... People don't want to upgrade their major production database servers every 6 months. It'll be 6 months at my work before we move off 7.4, and that's only because they have a reasonable postgres expert onsite (me)... Chris ---(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] Two-phase commit for 8.1
On Sun, 23 Jan 2005, Hans-Jürgen Schönig wrote: Heikki, What is still missing to complete the 2PC patch?. Here's my TODO on things that need to be done: * large objects * guc variables * notify/listen Large objects and notify/listen should be quite straightforward. GUC variables need some thinking, but shouldn't be much work. As the patch gets more attention, I'm sure more issues will come up. - Heikki ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] can plpgsql returns more flexibe value ?
Hello, Kris Jurka! Perhaps you should look into the refcursor type, which will allow you to return anything you want without specifying it. You can't do things like a join between to refcursor outputs, but it does allow for more return flexibility. Kris Jurka Can I use DataAdapter.Fill() with refcursor. :-( I don't want to fetch data one bye one. Thanks Regards Arnold.Zhu [EMAIL PROTECTED] 2005-01-23 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Two-phase commit for 8.1
On Sun, Jan 23, 2005 at 01:37:30PM +0200, Heikki Linnakangas wrote: As the patch gets more attention, I'm sure more issues will come up. I see the changes to the lock manager are huge. Can you explain what's the idea behind those? Do you release the locks and then reacquire them, or do you reassign them to a pseudo process? Are there possibilities of deadlock somewhere? -- Alvaro Herrera ([EMAIL PROTECTED]) Thou shalt study thy libraries and strive not to reinvent them without cause, that thy code may be short and readable and thy days pleasant and productive. (7th Commandment for C Programmers) ---(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] 8.1 development cycle (was a couple of other threads
On Sunday 23 January 2005 05:23, Christopher Kings-Lynne wrote:
So are we looking at a 8.1 in June and a 8.2 in say August of 2006?
Or something else?
I know that it is hard to completely pin these things down but it would
be really
helpful :)
I really don't know why this short dev cycle thing keeps coming back...
People don't want to upgrade their major production database servers
every 6 months. It'll be 6 months at my work before we move off 7.4,
and that's only because they have a reasonable postgres expert onsite
(me)...
Because there is a strong desire to get rid of ARC ASAP. Which I am
comfortable with IF there is an agreement that 8.1 wont require an initdb. I
certainly can't take down my production servers for the amount of time needed
for dump/reload, so if that is going to be required then I'm sure we'll not
do an upgrade until 8.1 is released, and I'd imagine a lot of others would
fall into this as well.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Locale agnostic unicode text
On Sat, 22 Jan 2005 17:09:42 -0500, Tom Lane [EMAIL PROTECTED] wrote: This time setlocale() was needed to get the behaviour I needed (database initdb'ed to 'C', my order set to 'pl_PL', or whatever locale I need at given moment). I would imagine that the performance is spectacularly awful :-(. Have you benchmarked it? A large sort on a unitext column, for instance, would be revealing. True. Yet it would be still better than nothing (C). Actually I was thinking that maybe functional indexes could be used to boost the speed (at least for ordering). ...but I would like to force ORDER BY using operators provided by me without this 'USING ' clause. Hmm, the existence of the default btree operator class should be sufficient. If You (or anyone) could try that SQL file and try to find missing clause... :) I guess that the case is that DOMAIN unitext is not quite another type, so text's default operators sometimes take precedence over unitext's own. :) CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$ utf8::decode($_[0]); return lc($_[0]); $$ LANGUAGE plperlu IMMUTABLE; AFAIK upper/lower cannot be considered to be locale-independent (see Turkish I/i business for a counterexample). I imagine it is not possible to make 'one size fits all' lower(), yet perl's uc()/lc() in my opinion for some cases is still better than choosing one locale or using C locale. Regards, Dawid ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
* Tom Lane ([EMAIL PROTECTED]) wrote:
Stephen Frost [EMAIL PROTECTED] writes:
Here's a proof-of-concept pretty much untested (it compiles) patch
against HEAD for review of the general approach I'm taking to
merging pg_shadow and pg_group. This is in order to support group
ownership and eventually roles. This patch includes my grammar and
get_grosysid move patches, and so conflicts with them.
One point is that you can't simply whack pg_shadow around and eliminate
pg_group, because that will break lord-knows-how-much client software
that looks at these tables. What I'm envisioning is to create a new
system catalog (say pg_role) that holds the New Truth, and then make
pg_shadow and pg_group be predefined views on this catalog that provide
as much backwards compatibility as we can manage.
Ok. Can I get some help defining what the New Truth will look like
then? I understand users and groups pretty well but I'm not 100% sure
about roles. Is it as simple as what my changed pg_shadow looks like?
What's the difference between a role, a user and a group? Can a role
log in/have a password? Can a role own an object? If a role owns an
object, can any users who have that role {drop, create index, etc} it?
Once we get the layout of pg_role defined I think I'll be able to make
much better progress towards what you're looking for. :)
A related point is that I hope soon to get rid of type AclId and
usesysid/grosysid/rolesysid and start identifying roles by Oids.
Alright. That doesn't sound too bad.
This is connected to Alvaro's work to create proper dependencies
for object owners and privilege entries: once that exists and you
can't drop a referenced role, there will be no need to allow explicit
setting of the SYSID for a new user. Not sure if you want to do any
of the associated changes in your patch, but if int4 is bugging you
then feel free to change it.
Ok, I probably will. Should I be concerned with trying to make
'smallish' patches that build upon each other (ie: change to pg_role
first, then change AclId to Oid, or whatever) or will one larger patch
that takes care of it all be ok?
Thanks,
Stephen
signature.asc
Description: Digital signature
Re: [HACKERS] Two-phase commit for 8.1
On Sun, 23 Jan 2005, Alvaro Herrera wrote: On Sun, Jan 23, 2005 at 01:37:30PM +0200, Heikki Linnakangas wrote: As the patch gets more attention, I'm sure more issues will come up. I see the changes to the lock manager are huge. Can you explain what's the idea behind those? Do you release the locks and then reacquire them, or do you reassign them to a pseudo process? I reassign them to a pseudo process (persistedLocksProc). Much of the changes in lock.c are just about moving code around. Some copy-paste code has been put in functions. LockAcquire has been changed to take PGPROC as an argument, so that locks can be acquired on behalf of the pseudo process. Then there's completely new code for persisting locks on PREPARE TRANSACTION and reacquiring them on recovery. If it helps, I could try to split it into two patches, one with code rearrangements that don't change current behaviour, and then the actual 2PC stuff on top of that. Are there possibilities of deadlock somewhere? Not that I know of. - Heikki ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] 8.1 development cycle (was a couple of other threads
On Sun, 23 Jan 2005, Robert Treat wrote: On Sunday 23 January 2005 05:23, Christopher Kings-Lynne wrote: So are we looking at a 8.1 in June and a 8.2 in say August of 2006? Or something else? I know that it is hard to completely pin these things down but it would be really helpful :) I really don't know why this short dev cycle thing keeps coming back... People don't want to upgrade their major production database servers every 6 months. It'll be 6 months at my work before we move off 7.4, and that's only because they have a reasonable postgres expert onsite (me)... Because there is a strong desire to get rid of ARC ASAP. Which I am comfortable with IF there is an agreement that 8.1 wont require an initdb. I certainly can't take down my production servers for the amount of time needed for dump/reload, so if that is going to be required then I'm sure we'll not do an upgrade until 8.1 is released, and I'd imagine a lot of others would fall into this as well. Agreed, but there is also a fair amount of ppl that won't upgraded to 8.0 in the first place, and will wait for the first 8.1, and if we can keep that cycle short enough, waiting the few extra months will be alot easier for them ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
Stephen Frost [EMAIL PROTECTED] writes: Ok. Can I get some help defining what the New Truth will look like then? I understand users and groups pretty well but I'm not 100% sure about roles. I looked through SQL99 a bit (see 4.31 Basic security model) and think I now have some handle on this. According to the spec a role is more or less exactly what we think of as a group, with the extension that roles can have other roles as members (barring circularity). In particular the spec draws a distinction between user identifiers and role identifiers, although this distinction seems very nearly 100% useless because the two sorts of identifiers can be used almost interchangeably (an authorization identifier means either one, and in most places authorization identifier is what is relevant). AFAICT the only really solid reason for the distinction is that you have to log in initially as a user and not as a role. That strikes me as a security policy --- it's analogous to saying you can't log in directly as root but have to su to root from your personal login --- which may be a good thing for a given site to enforce but IMHO it should not be hard-wired into the security mechanism. The implementation reason for not having a hard distinction is mainly that we want to have a single unique-identifier space for both users and roles. This simplifies representation of ACLs (which will no longer need extra bits to identify whether an entry references a user or a group) and allows us to have groups as members of other groups without messy complication there. It's not entirely clear to me whether the spec allows roles to be directly owners of objects, but I think we should allow it. So I'm envisioning something like CREATE TABLE pg_role ( rolname name, -- name of role rolsuperboolean,-- superuser? rolcreateuser boolean,-- can create more users? rolcreatedb boolean,-- can create databases? rolcatupdateboolean,-- can hack system catalogs? rolcanlogin boolean,-- can log in as this role? rolvaliduntil timestamptz,-- password rolpassword text, -- password expiration time rolmembers oid[], -- OIDs of members, if any roladminboolean[], -- do members have ADMIN OPTION rolconfig text[] -- ALTER USER SET guc = value ) WITH OIDS; Some notes: It might be better to call this by some name other than pg_role, since what it defines is not exactly roles in the sense that SQL99 uses; but I don't have a good idea what to use instead. pg_authorization would work but it's unwieldy. OIDs of rows in this table replace AclIds. I'm supposing that we should separate superuserness from can create users (presumably a non-superuser with rolcreateuser would only be allowed to create non-super users). The lack of distinction on this point has been a conceptual problem for newbies for a long time, and an admin issue too. As long as we are hacking this table we should fix it. If you want to enforce a hard distinction between users and roles (groups) then you'd prohibit rolcanlogin from being true when rolmembers is nonempty, but as said above I'm not sure the system should enforce that. rolpassword, rolvaliduntil, and rolconfig are irrelevant if not rolcanlogin. The roladmin[] bool array indicates whether members were granted admission WITH ADMIN OPTION, which means they can grant membership to others (analogous to WITH GRANT OPTION for individual privileges). I'm not sure this is sufficient ... we may need to record who granted membership to each member as well, in order to process revocation. It might be better to lose the rolmembers/roladmin columns and instead represent membership in a separate table, roughly CREATE TABLE pg_role_members ( roleoid, member oid, grantor oid, admin_optionbool, primary key (role, member, grantor) ); This is cleaner from a relational theory point of view but is probably harder for the system to process. One advantage is that it is easier to find out which roles does user X belong to? ... but I'm not sure we care about making that fast. One thing that needs to be thought about before going too far is exactly how ACL rights testing will work, particularly in the face of roles being members of other roles. That is the one performance-critical operation that uses these data structures, so we ought to design around making it fast. Ok, I probably will. Should I be concerned with trying to make 'smallish' patches that build upon each other (ie: change to pg_role first, then change AclId to Oid, or whatever) or will one larger patch that takes care of it all be ok? Smaller patches are easier to review, for sure. Also, you'll need to coordinate with Alvaro's work on dependencies for global objects.
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
I may be missing something here, but haven't we always stated that using 'SELECT *' should be frown'd upon for the most part? Is there a reason why adding a column/field to an existing view should be considered a bad thing? As long as we don't remove existing colums that an app could be using, but only adding a column, there shouldn't be any issues with backwards compatibility, shoudl there? On Sun, 23 Jan 2005, Josh Berkus wrote: Tom, Any new schemas introduced by PG itself will be named pg_something. This is not open to negotiation --- it's what we've promised to users to avoid tromping on their schema namespace. I can see the sense in that. So, there's four ways I can see to do things: 1) leave the existing views (pg_tables, pg_views, etc.) the way they are except for adding columns. Create new views based on the naming scheme of the old. 2) create new views in pg_catalog, using new names. The problem with this is that the most intuitive names (pg_tables, pg_views) are taken by the old views and I'm not sure what to name the new ones. 3) create a new schema with the system views in it, called for example pg_system_views. This seems cluttered to me; a whole new schema just for a dozen views? 4) ignore backwards compatibility and just re-write the old views. I can hear the shouting already ... So, a choice of annoying options. Does anyone else on the channel have opinions? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2
Troels, Others, Generally: Nice. But have you considered if the INFORMATION_SCHEMA could be used? Unfortunately, the INFORMATION_SCHEMA currently has a major problem in its usefulness in PostgreSQL: http://troels.arvin.dk/db/rdbms/#cli-list_of_tables-postgresql-gotchas Actually, I did. However, the format and columns of INFORMATION_SCHEMA are defined by the SQL Standard, which will not cover a lot of PostgreSQL objects (such as custom types or operators) and covers a lot of others in rather awkward form. For that matter, your own editorial points out that we should really be UPPERCASEing all of the object names in information_schema, which would be SQL-spec but not generally useful. This reminds me: It would be nice if it were somehow possible to determine when (if ever) statistics have been gathered for a given schema object. This needs changes to more than VIEWs, though. Well, you can always query pg_stats. Do you propose that typemodifiers be one column? - If would prefer if it were several columns. And it would be useful if it were easy to determine if a column is - solely - or part of - a uniqueness constraint - solely - or part of - a foreign key (pointing where?) - if it is subject to a (set of) CHECK constraints Yeah, I gave this some thought. The problem as I see it is that in the future we may have additional types of typemodifiers which aren't covered, and I don't want to get in the habit of adding more and more columns to the view. However, that's not really an excuse; it might be better to: pg_columns -- new view schemaname tablename columnname datatype notnull references (name which links pg_foreignkeys, or boolean?) default constraints (array, references pg_constraints) othermodifiers (string of other column modifiers, for when such exist) comment In a way, though, it might be better for references to be a boolean column, and users can query pg_foriegnkeys to find the exact reference. BTW, People, I really don't see the point in prodiving a dual list -- that is, a list of OIDs in addition to the list of names provided in the columns of each view. The idea of these views is to keep the users *away* from technical details like OIDs, which can and will change with the advancing versions of PostgreSQL. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Extending System Views: proposal for 8.1/8.2
Hi, On Sun, Jan 23, 2005 at 12:16:31PM -0800, Josh Berkus wrote: 4) ignore backwards compatibility and just re-write the old views. I can hear the shouting already ... So, a choice of annoying options. Does anyone else on the channel have opinions? Isn't it a usefull option to introduce a postgresql-conf parameter to set the pg-views version? I mean, in a pg 7.x-comaptibility-mode you would only see the known views with their old content. If you set it to 8.x, you will see the new versions. So developers will get more time to change their applications from the old views to the new ones while being able to use new features. Regards, Yann ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
Stephan, On Sun, Jan 23, 2005 at 03:14:04PM -0500, Tom Lane wrote: Smaller patches are easier to review, for sure. Also, you'll need to coordinate with Alvaro's work on dependencies for global objects. If you want, I can send you the current patch so you can see what has changed in it, maybe merge it with some work of yours for separate submittal. -- Alvaro Herrera ([EMAIL PROTECTED]) Siempre hay que alimentar a los dioses, aunque la tierra esté seca (Orual) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Much Ado About COUNT(*)
On Sun, Jan 23, 2005 at 12:36:10AM -0800, Ron Mayer wrote: Bruce Momjian wrote: Added to TODO based on this discusion:... * Speed up COUNT(*) One think I think would help lots of people is if the documentation near the COUNT aggregate explained some of the techniques using triggers to maintain a count for tables where this is important. For every one person who reads the mailinglist archives, I bet there are dozens who merely read the product documentation and never find the workaround/solution. Perhaps a note below the table here: http://www.postgresql.org/docs/8.0/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE would be a good place. If it is already somewhere in the docs; perhaps the page I linked should refer to the other page as well. Does anyone have working code they could contribute? It would be best to give at least an example in the docs. Even better would be something in pgfoundry that helps build a summary table and the rules/triggers you need to maintain it. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
I'm going to reply to 3 emails in one here... Out of Josh's 4 options, I think a new schema makes the most sense. Start with a clean plate. Yes, we'll end up with an ugly schema name, but after the exiting pg_catalog is removed in a few versions, we can go back to pg_catalog. The idea of using a GUC to control which version of the schema you get is also very interesting, though I don't know how workable it is. It does have a downside, though... if you have a bunch of code that's using pg_catalog, you'd have no choice but to migrate all of it at once. If you have both the old and new versions of these tables/views present at the same time then you can slowly migrate that code over. Only adding columns to the existing views/tables is also an interesting possibility. One issue is that it probably wouldn't work very well for the tables in pg_catalog, though I guess SELECT rules could be written to handle those. The other issue is that while SELECT * in code is almost always a bad idea, SELECT * is human-friendly. I suspect that basically every field in the existing tables/views will be renamed, which means SELECT * in psql will now give you 2x the number of columns you need. Though, this could be avoided by having a seperate set of 'human-readable' views. Having a seperate set of human-readable views would also mean we could provide more human-friendly formatting in one version, while the other version has everything you'd need to make it the most useful from a programatic standpoint. An example that comes to mind is the earlier discussion about function parameters. In a human-readable version of pg_function, you'd probably just want schema_name, function_name, parameters[], and return_type, where both parameters[] and return_type would be the name of the types. But if you're looking at functions programatically, it would be good to also have parameter_oids[] and return_type_oid. Personally, I'm leaning towards option 3: add new fields to pg_catalog.* to support the new naming convention and new features, and add pg_human. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2
On Sun, Jan 23, 2005 at 12:43:15PM -0800, Josh Berkus wrote: BTW, People, I really don't see the point in prodiving a dual list -- that is, a list of OIDs in addition to the list of names provided in the columns of each view. The idea of these views is to keep the users *away* from technical details like OIDs, which can and will change with the advancing versions of PostgreSQL. It's a question of if these views will also be used programatically. ISTM that OIDs are the preffered method of refering to things in code (in fact, aren't there some functions that only take OIDs?). If we want to make names the cannonical way to reference things in code, then I agree that there's not much use to OIDs. Is the long term plan to remove OIDs entirely? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Shortcut for defining triggers
Sorry if this is old, but I couldn't find it in the archives... How difficult would it be to provide a means to define a trigger in one statement? Something like a combination of CREATE TRIGGER and CREATE FUNCTION? Being able to define them seperately is awesome for generic cases where you can use one function for a bunch of different tables, but it's a pain in the cases where you need a unique trigger for one table. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
Jim C. Nasby [EMAIL PROTECTED] writes: Start with a clean plate. Yes, we'll end up with an ugly schema name, but after the exiting pg_catalog is removed in a few versions, we can go back to pg_catalog. Huh? pg_catalog isn't going away, and none of this discussion has anything to do with changing the system catalogs themselves. In any case, creating these views with the idea that we will change their locations later is a nonstarter. People are going to be putting the fully qualified paths into their applications. The idea of using a GUC to control which version of the schema you get is also very interesting, though I don't know how workable it is. It could be spelled schema_path ... otherwise I don't see any way to do it. But I'm not sure it helps any to make two separate schemas. Most admin-type apps wouldn't want to depend on the value of schema_path (psql sure wouldn't, for instance) so they'd still have to change if only to nail down the schema they want in each query. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2
Jim C. Nasby [EMAIL PROTECTED] writes: Is the long term plan to remove OIDs entirely? No. OIDs will be the real primary keys of most system catalogs for the foreseeable future. The only discussion that's going on concerns deprecating their use in user tables. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
* Alvaro Herrera ([EMAIL PROTECTED]) wrote: On Sun, Jan 23, 2005 at 03:14:04PM -0500, Tom Lane wrote: Smaller patches are easier to review, for sure. Also, you'll need to coordinate with Alvaro's work on dependencies for global objects. If you want, I can send you the current patch so you can see what has changed in it, maybe merge it with some work of yours for separate submittal. Yeah, I'd appriciate seeing it and seeing if/how much it conflicts with what I'm working on. I'll probably be going back to scratch since there isn't really anything worthwhile in my patch to build on working towards what Tom's laid out. Not a problem though, it didn't take me very long to code. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Merge pg_shadow pg_group -- UNTESTED
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Ok. Can I get some help defining what the New Truth will look like then? I understand users and groups pretty well but I'm not 100% sure about roles. I looked through SQL99 a bit (see 4.31 Basic security model) and think Ah, I was looking through SQL2003 recently, I don't think much has changed in that area though. I now have some handle on this. According to the spec a role is more or less exactly what we think of as a group, with the extension that roles can have other roles as members (barring circularity). Right, ok. In particular the spec draws a distinction between user identifiers and role identifiers, although this distinction seems very nearly 100% useless because the two sorts of identifiers can be used almost interchangeably (an authorization identifier means either one, and in most places authorization identifier is what is relevant). AFAICT the only really solid reason for the distinction is that you have to log in initially as a user and not as a role. That strikes me as a security policy --- it's analogous to saying you can't log in directly as root but have to su to root from your personal login --- which may be a good thing for a given site to enforce but IMHO it should not be hard-wired into the security mechanism. Ok, I agree, though personally I don't like the idea of permitting role-logins, but no need to have the security system force it. The implementation reason for not having a hard distinction is mainly that we want to have a single unique-identifier space for both users and roles. This simplifies representation of ACLs (which will no longer need extra bits to identify whether an entry references a user or a group) and allows us to have groups as members of other groups without messy complication there. The other difference would seem to be that user identifiers can't be granted to users whereas role identifiers can be. Following this, rolmembers must be NULL if rolcanlogin is true, no? That breaks if roles can log in though. Or should we just allow granting of user identifiers to other users- but if we do should the user be permitted to do that? It's not entirely clear to me whether the spec allows roles to be directly owners of objects, but I think we should allow it. I agree, and in fact group/role ownership is what I'm specifically interested in, though I'd like role support too and so I'm happy to implement it along the way. :) So I'm envisioning something like CREATE TABLE pg_role ( rolname name, -- name of role rolsuperboolean,-- superuser? rolcreateuser boolean,-- can create more users? rolcreatedb boolean,-- can create databases? rolcatupdateboolean,-- can hack system catalogs? rolcanlogin boolean,-- can log in as this role? rolvaliduntil timestamptz,-- password rolpassword text, -- password expiration time rolmembers oid[], -- OIDs of members, if any roladminboolean[], -- do members have ADMIN OPTION rolconfig text[] -- ALTER USER SET guc = value ) WITH OIDS; It might be better to call this by some name other than pg_role, since what it defines is not exactly roles in the sense that SQL99 uses; but I don't have a good idea what to use instead. pg_authorization would work but it's unwieldy. Hmmm, I agree pg_role isn't quite right. pg_auth would be shorter than pg_authorization, but it isn't intuitive what it is. How about pg_ident? It's not users or roles, but it's identifiers. Perhaps pg_authid? OIDs of rows in this table replace AclIds. ok. I'm supposing that we should separate superuserness from can create users (presumably a non-superuser with rolcreateuser would only be allowed to create non-super users). The lack of distinction on this point has been a conceptual problem for newbies for a long time, and an admin issue too. As long as we are hacking this table we should fix it. Agreed. If you want to enforce a hard distinction between users and roles (groups) then you'd prohibit rolcanlogin from being true when rolmembers is nonempty, but as said above I'm not sure the system should enforce that. Right, but there's still the issue of granting users to users. rolpassword, rolvaliduntil, and rolconfig are irrelevant if not rolcanlogin. Right. The roladmin[] bool array indicates whether members were granted admission WITH ADMIN OPTION, which means they can grant membership to others (analogous to WITH GRANT OPTION for individual privileges). I'm not sure this is sufficient ... we may need to record who granted membership to each member as well, in order to process revocation. I think we'll probably need to record who granted membership too, to prevent circulation as well as revocation
Re: [HACKERS] [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2
Jim, It's a question of if these views will also be used programatically. ISTM that OIDs are the preffered method of refering to things in code (in fact, aren't there some functions that only take OIDs?). If we want to make names the cannonical way to reference things in code, then I agree that there's not much use to OIDs. Hmmm I think that you and I have different ideas about the purpose of the system views. My idea is to provide a stable (through multiple versions of pg), human-readable view of the system objects. You obviously want to do more -- I'd like details on what that more is, so that we can talk about it. Is the long term plan to remove OIDs entirely? No, but we want to discourage users from using them actively. Where they're apparent, users will be inclined to write code that references OIDs *by number* which will survive neither backup/restore, nor upgrades in pg versions. So where we can encourage users to refer to objects by name, we should. I see the OIDs, in fact, as one of the reasons to create the additional system views -- so that users aren't confused by them. If there are functions that need OIDs, my inclination would be to write shell functions for those that accept fully-qualified object names. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2
On Sun, Jan 23, 2005 at 02:37:28PM -0800, Josh Berkus wrote: Jim, It's a question of if these views will also be used programatically. ISTM that OIDs are the preffered method of refering to things in code (in fact, aren't there some functions that only take OIDs?). If we want to make names the cannonical way to reference things in code, then I agree that there's not much use to OIDs. Hmmm I think that you and I have different ideas about the purpose of the system views. My idea is to provide a stable (through multiple versions of pg), human-readable view of the system objects. You obviously want to do more -- I'd like details on what that more is, so that we can talk about it. Really, my only goal is to make using the system views/tables programatically easier by coming up with a better naming convention. This isn't directly related to the human-readable stuff, other than fields that would be common between both sets of views. Perhaps a good way to accomplish both goals is to have the set of human-readable views, and to add columns to the system tables/views that conform with the new, more logical naming convention. This way people accessing system information programmatically can use pg_catalog (and migrate to the new naming convention), while people who are doing ad-hoc queries can just hit the human-readable stuff. Make sense? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2
Jim, Perhaps a good way to accomplish both goals is to have the set of human-readable views, and to add columns to the system tables/views that conform with the new, more logical naming convention. This way people accessing system information programmatically can use pg_catalog (and migrate to the new naming convention), while people who are doing ad-hoc queries can just hit the human-readable stuff. If you think that anyone on this list is going to let us re-name columns in the system *tables*, you're on more pain meds than I realized ... And in what way is using fully qualified names programmatically a problem? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Much Ado About COUNT(*)
Jim C. Nasby wrote: Does anyone have working code they could contribute? It would be best to give at least an example in the docs. Even better would be something in pgfoundry that helps build a summary table and the rules/triggers you need to maintain it. http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html#PLPGSQL-TRIGGER-SUMMARY-EXAMPLE regards Mark ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-hackers-win32] [HACKERS] %2$, %1$ gettext placeholder replacement is not working under Win32
Greetings,
I would like to submit a new version of src/port/snprintf.c
It passes regression tests on Linux and Win32 and
prints all of %n$ messages finely.
I added printf() function too because --help usage-type
output is printed with printf().
I have no experience with autoconf so I would ask you
for help on what changes to do to include libpgport to
src/Makefile.global and src/Makefile.shlib.
PostgreSQL uses snprintf() in more places than I expected.
So these changes need a through testing. I have no 64-bit
to able to run regression test on it would be nice to run
it on a 64-bit platform too.
Best regards,
Nicolai Tufar
/*
* Copyright (c) 1983, 1995, 1996 Eric P. Allman
* Copyright (c) 1988, 1993
* The Regents of the University of California. All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions
* are met:
* 1. Redistributions of source code must retain the above copyright
*notice, this list of conditions and the following disclaimer.
* 2. Redistributions in binary form must reproduce the above copyright
*notice, this list of conditions and the following disclaimer in the
*documentation and/or other materials provided with the distribution.
* 3. All advertising materials mentioning features or use of this software
*must display the following acknowledgement:
* This product includes software developed by the University of
* California, Berkeley and its contributors.
* 4. Neither the name of the University nor the names of its contributors
*may be used to endorse or promote products derived from this software
*without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE REGENTS AND CONTRIBUTORS ``AS IS'' AND
* ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL THE REGENTS OR CONTRIBUTORS BE LIABLE
* FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
* DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
* OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
* HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
* LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
* OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
* SUCH DAMAGE.
*/
/* might be in either frontend or backend */
#include postgres_fe.h
#ifdef ENABLE_THREAD_SAFETY
#error The replacement snprintf() is not thread-safe. \
Your platform must have a thread-safe snprintf() to compile with threads.
#endif
#if !defined(WIN32) !defined(__CYGWIN__)
#include sys/ioctl.h
#endif
#include sys/param.h
/*
* We do all internal arithmetic in the widest available integer type,
* here called long_long (or ulong_long for unsigned).
*/
#ifdef HAVE_LONG_LONG_INT_64
typedef long long long_long;
typedef unsigned long long ulong_long;
#else
typedef long long_long;
typedef unsigned long ulong_long;
#endif
#ifndef NL_ARGMAX
#define NL_ARGMAX 4096
#endif
/*
** SNPRINTF, VSNPRINT -- counted versions of printf
**
** These versions have been grabbed off the net. They have been
** cleaned up to compile properly and support for .precision and
** %lx has been added.
*/
/**
* Original:
* Patrick Powell Tue Apr 11 09:48:21 PDT 1995
* A bombproof version of doprnt (dopr) included.
* Sigh. This sort of thing is always nasty do deal with. Note that
* the version here does not include floating point. (now it does ... tgl)
*
* snprintf() is used instead of sprintf() as it does limit checks
* for string length. This covers a nasty loophole.
*
* The other functions are there to prevent NULL pointers from
* causing nast effects.
**/
/*static char _id[] = $PostgreSQL: pgsql/src/port/snprintf.c,v 1.4 2004/08/29
05:07:02 momjian Exp $;*/
static char *end;
static int SnprfOverflow;
int snprintf(char *str, size_t count, const char *fmt,...);
int vsnprintf(char *str, size_t count, const char *fmt,
va_list args);
int printf(const char *format, ...);
static void dopr(char *buffer, const char *format, va_list args);
int
printf(const char *fmt,...)
{
int len;
va_list args;
static char*buffer[4096];
char* p;
va_start(args, fmt);
len = vsnprintf((char*)buffer, (size_t)4096, fmt, args);
va_end(args);
p = (char*)buffer;
for(;*p;p++)
putchar(*p);
return len;
}
int
snprintf(char *str, size_t count, const char
[HACKERS] Goals for 8.1
What are the goals for 8.1? ---(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] [pgsql-hackers] Re: Extending System Views: proposal for 8.1/8.2
On Sun, Jan 23, 2005 at 02:53:11PM -0800, Josh Berkus wrote: Jim, Perhaps a good way to accomplish both goals is to have the set of human-readable views, and to add columns to the system tables/views that conform with the new, more logical naming convention. This way people accessing system information programmatically can use pg_catalog (and migrate to the new naming convention), while people who are doing ad-hoc queries can just hit the human-readable stuff. If you think that anyone on this list is going to let us re-name columns in the system *tables*, you're on more pain meds than I realized ... What I figured. And in what way is using fully qualified names programmatically a problem? It's not a problem; my only complaint is that the field names are awkward as hell, which is why I suggested a new naming convention. If it comes down to it, I'll settle for better names in the human readable stuff and hope it eventually can be migrated to pg_catalog stuff. I just figured changing both at the same time might make more sense. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
