Re: [HACKERS] What X86/X64 OS's do we need coverage for?
Hi, ... if the kernel of the OS has Xen support, there will be no performance penalty (only 2%-3%) (Para-virtualization). Otherwise, there will be full-virtualization, and we should expect a performance loss about 30% for each guest OS (like Windows). I may be wrong but I thought that the guest OS kernel only needs special support if the underlying CPU doesn't have virtualization support which pretty much all the new Intel and AMD chips have. No? You need that CPU support if you want to do full virtualization at all. Otherwise you can only use para-virtualization. Para-virtualization has much better performance, but full virtualization is more flexible because you don't need special kernel support in the guest. - Sander ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Anyone using POSIX time zone offset capability?
Hi, The POSIX timezone notation as understood by the zic code includes the possibility of zoneabbrev[+-]hh[:mm[:ss]] but the meaning is that hh:mm:ss *is* the offset from GMT, and zoneabbrev is being defined as the abbreviation for that offset. What the datetime.c code is doing is trying to find the zoneabbrev in a built-in timezone table, and then adding the two together. This is simply wacko. I think that if anyone has ever tried to use this notation they would have noticed this misinterpretation of the specs. Given where the code stands now, I think the best solution is to rip out DecodePosixTimezone and instead pass the syntax off to the zic code (which can handle it via tzparse()). Since the datetime input parser is ultimately only interested in the GMT offset value, this would mean that the zoneabbrev part would become a noise word. Sounds like a good idea to me. Sander ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Anyone using POSIX time zone offset capability?
Hi, Sander Steffann [EMAIL PROTECTED] writes: What the datetime.c code is doing is trying to find the zoneabbrev in a built-in timezone table, and then adding the two together. This is simply wacko. I think that if anyone has ever tried to use this notation they would have noticed this misinterpretation of the specs. Well, it'd work without surprise for the case of GMT+-n, which is undoubtedly the most common case ... H. I hadn't thought of that, but then: with the changes you proposed they would still get what they expect. Even though that notation would not conform to the POSIX docs. Still seems like a good idea :) Sander ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [Pgsqlrpms-hackers] [HACKERS] Safer auto-initdb for RPM initscript
Hi, If this were a bulletproof solution then I'd consider it anyway, but AFAICS it's got the very same vulnerabilities as the flag-file method, ie, if you RPM install or upgrade while your mountable data directory is offline, you can still get screwed. Isn't the most bulletproof solution to make initdb more careful about overwriting an existing data directory? - Sander ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: pg_upgrade (was: [HACKERS] 8.2 features status)
Hi, [ concerning handling a change in a single datatype's representation ] 1. Under old system, munge the system catalog to add code for new inet type with new OID. Probably needs a shared lib (if you could create type input/output function with pl/pgsql it would help here). 2. Execute ALTER COLUMN TYPE USING to switch to the new type. 3. Shutdown old server 4. Complete catalog changes 5. Startup new version Probably easier and more flexible just to include both versions of the datatype in the new release, and tell people they'd better ALTER COLUMN TYPE sometime before updating to release N+2. Sounds very nice to have for me! Add an easy way to see which columns are still in the old format, and upgrading will be so much easier. - Sander ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] UUID's as primary keys
Hi, Just MHO: 1) The added 128-bit type should take the form of: c) UUID, with only encode/decode/indexable - generic except for the name of the type, and the encoding format. 2) According to your answer in 1), the added 128-bit type should be: a) In core first. 1c is what I would need. 1b or 1a would be nice to have. - Sander ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Regrading TODO item alerting pg_hba.conf from SQL
Hi, Tom Lane writes: Martijn van Oosterhout kleptog@svana.org writes: For simple systems then you could have a short pg_hba.conf to limit the IP addresses users can connect on, and the DB stores what databases they have access to... Right, you'd still have a pg_hba.conf, but it would hopefully be short and sweet, not doing much more than listing which addresses you want to allow connections from and what the authentication mechanisms ought to be. From another message from Tom: How would you suggest the SQL syntax be like for example. Just another privilege name in the existing GRANT/REVOKE ON DATABASE syntax. Sounds like a good idea to me. Make pg_hba.conf simpler and administered by the admin, and give the database owner the tools to decide who gets to connect. Nice! Sander ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FKs on temp tables: hard, or just omitted?
Hi, Martijn van Oosterhout kleptog@svana.org writes: You solve it by allowing other backends to lock and examine your temporary tables. But AIUI temporary tables are not stored in shared memory so how do you get a consistant view of it? Not unsolvable, but very tricky. Right, the problem isn't that it can't be done, it's that it can't be done without giving up most of the performance advantages of temp tables. Which seems like a bad tradeoff, at least to me ... Ah, now I understand the problem :-) And I think you are right. It would be a very bad tradeoff. Sander ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] FKs on temp tables: hard, or just omitted?
You can have foreign keys between temp tables, just not between temp and permanent tables. The latter case is either fairly silly, or technically hard, depending on which direction you have in mind. A temp table referencing a permanent table wouldn't be very silly IMHO... Sander. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] inet increment w/ int8
Hi, I modified the TODO. I think we only need an INT4. I realize INT8 would be for IPV6 but I can't imagine a network that has more than INT4 hosts (not part of the network address). Actually increment the host address isn't a well-defined concept for IPV6. The host part of the address (if you're on an Ethernet) is generally the 64 bit MAC address. So if the network card dies the machine has a new IPv6 address and you just update your DNS? Do you update your routing tables? There are standards defined for automatically determining the IPv6 address of a host (Stateless Address Autoconfiguration). These include a standard for Privacy Extensions for Stateless Address Autoconfiguration in IPv6 where the host-part of the IPv6 address changes over time to make it more difficult to identify a single user. The net-part of the IPv6 address can be determined by Router Advertisements. By default an IPv6 address is divided as follows: first 32 bits: ISP next 16 bits: customer next 16 bits: subnet rest (64 bits): host So an ISP gets a /32 from ARIN/RIPE/LACNIC/APNIC/AfriNIC, which assigns a /48 to a customer, which assigns a /64 to each separate network. There are ISPs that have so many customers that they got more than a /32, and if a customer needs more than 16 bits for subnets they can get a bigger block than a /48. This addressing scheme means that even a home-user is a customer and gets a /48 with 16 bits for subnetting. There are discussions going on about giving home users a /56 block instead, but I haven't heard a final decision about that yet (in the RIPE region). From http://www.tcpipguide.com/free/t_IPv6InterfaceIdentifiersandPhysicalAddressMapping.htm: The IEEE has also defined a format called the 64-bit extended unique identifier, abbreviated EUI-64. It is similar to the 48-bit MAC format, except that while the OUI remains at 24 bits, the device identifier becomes 40 bits instead of 24. This provides gives each manufacturer 65,536 times as many device addresses within its OUI. A form of this format, called modified EUI-64, has been adopted for IPv6 interface identifiers. To get the modified EUI-64 interface ID for a device, you simply take the EUI-64 address and change the 7th bit from the left (the universal/local or U/L bit) from a zero to a one. Because the 7th bit is always a one with auto-configuration, addresses with 7th bit zero are still free to be manually assigned. I hope this helps a little... Sander. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Feature freeze date for 8.1
Hi, What to people think about having an optional maintenance window so that autovac only takes action during an approved time. This sounds like a realy good idea to me! Sander. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] attempt at a multi file commit, to seee how it
Hi, Not sure if I like the URLs, myself ... opinions? All those links look messy IMHO. Maybe it's possible to give one link for every directory instead one for every file? Something like: Log Message: --- attempt at a multi file commit, to seee how it formats Modified Files: -- pgsql-server/contrib: (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/contrib/) README pgsql-server/contrib/rserv: (http://developer.postgresql.org/cvsweb.cgi/pgsql-server/contrib/rserv/) ApplySnapshot.in CleanLog.in GetSyncID.in Etc. Just a thought... Sander. ---(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] Little mess in RPM RH ?
Hi, Inside the RH9.0 coesist the 7.4.0-0.3 and 7.4.0-0.5 versions looking on other RH directory version I found that actually 7.4.0-0.2 7.4.0-0.3 7.4.0-0.4 7.4.0-0.5 are existing. Inside the SRPMS directory is present only the 7.4.0-0.2 version! This is because when I built the RPMs for RedHat there were little differences between the different RedHat releases that caused the builds to fail. I had to make minor adjustments for each platform, which is why there are so many different release versions. The contents of the RPMs is identical, only the build instructions differ. The SRPMs for all these versions are at http://opensource.nederland.net/PostgreSQL/. Lamar Owen (who creates the original SRPM) knows what has to be changed for each platform, so I am confident that we won't have this many release again for the next release. I'm able to put my hand on RH 8.0, RH 9.0, RHAS 2.1 and RHAS 3.0 and RH 7.3 systems, if you need some help let me know. Thanks for the offer. I can't do RHEL 3.0 yet, so if you could build the RPMs for that platform that would be great. I still have a RH 6.2 machine in case anybody still wants that. Thanks, Sander. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Resurrecting pg_upgrade
Hi, Alternative thought: just recommend that if possible, people take a filesystem dump of their old PGDATA directory after stopping the old postmaster. This would be sufficient for retreating to the prior version if needed. It might or might not be slower than copying all the files to a new PGDATA ... Filesystem-level snapshots make this very easy. Combined with: Dave Smith [EMAIL PROTECTED] writes: Why not go the other way. 1) Dump the schemas. 2) Initdb with the new schemas in a tmp PGDATA 3) backup the schemas in the current PGDATA 4) move the new schemas from the new db into the current one. Then it would be possible to: 1) Stop old postmaster 2) Make a filesystem snapshot 3) Upgrade the schemas in-place (as described above) 4) Start new postmaster *) On error: revert filesystem to snapshot Would be very nice for those who can use filesystem snapshots. Sander. ---(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] PlPython
Hi Tom, I am inclined to rename plpython to plpythonu, by analogy to pltclu. The advantage of doing so is that (a) the name change makes it somewhat more obvious that there's a fundamental behavioral change, and (b) assuming that the Python folk someday figure out a secure version of RExec, we'd want to reinstitute the trusted version of plpython, but perhaps not take away the untrusted one. Sounds good. On the other hand, this would create headaches for people who are trying to load dump files that declare plpython or contain plpython-language functions. But since plpython is untrusted now this probably would not work anyway... I can't think of any non-kluge solution to this (kluge solutions would include putting special-case code into CREATE FUNCTION to change 'plpython' to 'plpythonu' ...) I think this would only make it more confusing. Changing plpython from trusted to untrusted is a big change, so some extra attention from the db-admin and users is good IMHO. Bye, Sander. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] No more RH7.3 RPMs?
Hi, On Thursday 29 May 2003 07:26, ow wrote: RH7.3 is a supported distribution for at least 6 months. By Red Hat, but not necessarily by us. That being said: Any plans to add Postgres 7.3.3 RPMs for RH7.3? Yes. Please understand that I only have at my disposal machines running Red Hat 9 (my personal notebook), Red Hat 8.0 (a couple of servers I admin), and Aurora 1.0 (0.42) (my personal SPARC servers). I do not have a RH7.3 box, so I can't directly support RH 7.3. Please also understand that I am not being paid by anyone at this time to do RPM releases -- it is a completely volunteer operation. That being said, I have volunteers who build RPMs for the older distributions. That would be me :) Sorry I was a little slow with providing RH73 packages! RH 7.3 RPMs should be available today, with RH 6.2 RPMs possibly also available today. Someone else has already built RPMs for RH73 and Lamar has already uploaded them to ftp.postgresql.org. I just completed the RH62 packages. Lamar will put them on the FTP server, but until then they can be picked up from http://www.steffann.nl/PostgreSQL/v7.3.3/ if somebody needs them quickly. Bye! Sander. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Numbering of the next release: 8.0 vs 7.4
Hi, Would it be cool to decide on the version numbering of our next release like this: + If it looks like we'll have Win32 and/or PITR recovery in time for the next release, we call it PostgreSQL 8.0 + If not, we call it 7.4 Wouldn't a new FE/BE protocol be a better reason to call it 8.0? Raising the major version number together with introducing a new protocol which causes incompatibilities between new clients and older servers seems like a logical combination... Just a thought... :) Sander. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] RPMS for RedHat 6.2/7.3/8.0 ready
Hi all, Yesterday I tried to help Lamar by building RedHat 6.2, 7.3 and 8.0 (with default tcl) RPMS for the 7.3.1 release. Lamar was going to put them on ftp.postgresql.org, but it seems that he is away for Christmas... Can anybody else please put them on the ftp server? The RPMS are at http://www.steffann.nl/PostgreSQL/v7.3.1/ Thanks, Sander. ---(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] Why is MySQL more chosen over PostgreSQL?
Hi And what's the problem with networkcard_products being a separate table that shares a key with the products table? CREATE TABLE products (product_id int, ...) CREATE TABLE networkcard_products_data (product_id int, ...) CREATE VIEW networkcard_products AS SELECT products.product_id, ... FROM products JOINT networkcard_products_data USING (product_id) What functionality does table inheritance offer that this traditional relational method of doing things doesn't? Well, if you also have soundcard_products, in your example you could have a product which is both a networkcard AND a soundcard. No way to restrict that a product can be only one 'subclass' at a time... If you can make that restriction using the relational model, you can do the same as with subclasses. But afaict that is very hard to do... Sander. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Vote totals for SET in aborted transaction
What about a SET variable that controls the behaviour of SET variables :-) Or two commands for the same thing: - a SET command that behaves as it does now - a TSET command that is transaction-aware Ouch... :-) Sander ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schema (namespace) privilege details
Hi, Curt Sampson [EMAIL PROTECTED] writes: On Fri, 19 Apr 2002, Sander Steffann wrote: I can't think of a reason that [creation of] temp tables should be prevented. Maybe to keep hostile users from filling up your disk? That does come to mind --- but if you've let hostile users into your database, filling your disk is not exactly the smallest problem they could cause. They can very easily cause DOS problems just based on overconsumption of CPU cycles, or on crashing your server constantly. (Cm'on, we all know that can be done.) Even more to the point, is there nothing in your database that you'd not want published to the entire world? There's got to be a certain amount of trust level between you and the persons you allow SQL-command-level access to your database. If not, you ought to be interposing another level of software. My current proposal for schema protection does include a TEMP-table- creation right ... but to be honest I am not convinced that it'd be worth the trouble to implement it. Comments anyone? I see your point, but I think Curt is right... If users are always allowed to make temp tables, you can't give someone real read-only access to the DB. I agree that there has to be more protection to prevent other abuses, but at least the disk is safe. Sander ---(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 (namespace) privilege details
Hi Tom, One of the things I'd like this mechanism to do is answer the request we've heard so often about preventing users from creating new tables. If the DBA revokes write access on the public namespace from a particular user, and doesn't create a personal schema for that user, then under this proposal that user would have noplace to create tables --- except TEMP tables in his temp schema. Is that sufficient, or do the folks who want this also want a way to prevent TEMP table creation? I can't think of a reason that temp tables should be prevented. Being able to prevent a user from creating permanent objects is good IMHO. Another thing that would be needed to prevent users from creating new tables is to prevent them from creating schemas for themselves. I am not sure how to handle that --- should the right to create schemas be treated as a user property (a column of pg_shadow), or should it be attached somehow to the database (and if the latter, how)? Connecting this right to a database sounds like the right thing to do. (ISP case: allow a user to do with his database whatever he wants, as long as he stays away from other databases) But I don't know a good way to do it... Should the owner of a database (assume he's not a superuser) have the right to drop any schema in his database, even if he doesn't own it? I can see arguments either way on that one. I think that if he owns it, he should be able to control it... Someone owning a database should be responsible enough to manage it. I hope these comments can help you, Sander. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
