Re: [HACKERS] [pgsql-advocacy] Audio interview
On Tue, Feb 07, 2006 at 11:43:40PM -0500, Bruce Momjian wrote: I did an audio interview today, and it is online now: http://bsdtalk.blogspot.com/2006/02/bsdtalk015-interview-with-postgresql.html Great interview. You hit a lot of the high points :) You mentioned in-place upgrade scripts. Are those in contrib/ somewhere? On GBorg? On PgFoundry? If not, could you put them somewhere? As far as converting them from shell to Perl, I'm sure you'll find a flock of volunteers to help. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] sql row constructor...works!
Well, I've tested it a bit: db=# select version(); version PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13) (1 row) db=# select (1,3) (2,3); ?column? -- f (1 row) db=# select (3,3) (2,3); ?column? -- f (1 row) db=# select (3,4) (2,3); ?column? -- t (1 row) It seems to me that (x,y) (a,b) means (x a AND y b) ... which is not exactly what you wanted... or I'm missing something ? Cheers, Csaba. On Wed, 2006-02-08 at 03:35, Merlin Moncure wrote: On Feb 8, 2006, at 11:17 , Merlin Moncure wrote: The proper SQL construct without row constructor is: select * from t where a = a1 and (a a1 or b= b1) and (a a1 or b b1 or c c1) order by a,b,c limit 1^ no offset necessary confused yet? This is interesting! Could you also provide the equivalent *with* a row constructor? (or did I miss that somewhere?) select * from t where (a,b,c) (a1, b1, c1) order by a,b,c limit 1; [plus full usage of key on t(a,b,c)] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] sql row constructor...works!
On Wed, Feb 08, 2006 at 10:38:59AM +0100, Csaba Nagy wrote: Well, I've tested it a bit: db=# select version(); version PostgreSQL 8.1.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13) (1 row) It's in CVS HEAD, not 8.1 It seems to me that (x,y) (a,b) means (x a AND y b) ... which is not exactly what you wanted... or I'm missing something ? Yes, it changed because someone pointed out that the behaviour in 8.1 was wrong. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] streamlined standby procedure
What do you find difficult about the current method? That's got to be the first discussion point. The main problem I have is the complexity of setup. It involves a lot of additional scripting which you have to get it right to be actually reliable. The documentation is giving a rough idea on how to do it, but it is quite some work to make it work, and you can't really tell that is reliable... Another issue is that unless you got the archive_command right in the master server from the beginning, you will have to restart the server once you decide to build your standby... the archive_command is a start-up time parameter. This could be of course alleviated by always using a stub script as archive command, and let it do nothing if you don't have a standby, and then modify it to start archiving to the right place once you start building one, or if you want to move it to another machine. But this is also not documented, and you have to figure it out for yourself. And responding to Tom's other post regarding a real standby mode, where you could stop the standby and then later resume it still in standby mode: I would actually have a good use for it tonight :-) We will migrate our application to a new version, which involves some changes in the data base. Now it would be nice to stop the standby BEFORE doing these changes, and if the migration fails for some reason, start up the standby and use it with our old application version. But if the migration succeeds, I want to start up the standby still as standby, and make it resume standby operation... rebuilding it will take half day at least. So a standby which can be isolated for a while would actually be useful. OK, now that I'm thinking a bit more about this, I could achieve this by fiddling with the restore_command so it stops delivering the logs for a while. But again it is not straightforward. The whole point of this is that starting up a standby should be as simple as pointing the standby machine to the primary server, without shell scripting gimmicks (which are OS specific and therefore hard to document in a generic way), without the need of fiddling with the primary's configuration (see archive command), without the need to restart the primary if the archive command was not right in the first place. And to make it easy to start up one more standby if needed, or isolate it for a while when doing some risky work on the primary. It's about user friendliness and flexibility. It's not that it can't do the work right now, but it's really hard to do it... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Expression index with function based on current_user?
I have private tables such as this (Very simplified): CREATE TABLE private.t_foo ( ... ); REVOKE ALL ON private.t_foo FROM public; Different users have access to different data in the table, so I do this view: CREATE VIEW foo AS SELECT * FROM private.t_foo WHERE private.haveaccess(); GRANT ALL ON foo TO public; Using rules I then make the view updatable (In reality views fetch and write data to multiple tables usually). This works fine, but I have some thought on performance in the future. The function private.haveaccess()'s result depends on the currently logged in user, is it still possible to create an expression index over that function? // Fredrik Olsson ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Expression index with function based on current_user?
The function private.haveaccess()'s result depends on the currently logged in user, is it still possible to create an expression index over that function? // Fredrik Olsson Hello, All functions and operators used in an index definition must be immutable, that is, their results must depend only on their arguments and never on any outside influence. ... And your function is vollatile = you can't to do expression index. Regards Pavel Stehule _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Expression index with function based on current_user?
Ühel kenal päeval, K, 2006-02-08 kell 12:26, kirjutas Fredrik Olsson: I have private tables such as this (Very simplified): CREATE TABLE private.t_foo ( ... ); REVOKE ALL ON private.t_foo FROM public; Different users have access to different data in the table, so I do this view: CREATE VIEW foo AS SELECT * FROM private.t_foo WHERE private.haveaccess(); GRANT ALL ON foo TO public; Using rules I then make the view updatable (In reality views fetch and write data to multiple tables usually). This works fine, but I have some thought on performance in the future. The function private.haveaccess()'s result depends on the currently logged in user, is it still possible to create an expression index over that function? No, but you probably can crete index on static function user_has_access(username) and then use that func in private.haveaccess() Hannu ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] streamlined standby procedure
On 2/7/06 1:19 PM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Rawnsley [EMAIL PROTECTED] writes: IMHO the #1 priority in the current PITR/WAL shipping system is to make the standby able to tolerate being shut down and restarted, i.e. actually having a true standby mode and not the current method of doing it only on startup. How is shutting down the standby a good idea? Seems like that will block the master too --- or at least result in WAL log files piling up rapidly. If the standby goes off-line, abandoning it and starting from a fresh base backup when you are ready to restart it seems like the most likely recovery path. For sure I don't see this as the #1 priority. regards, tom lane I wasn't suggesting this in the context of Csaba's auto-ship plan (and, to be clear, not #1 in the context of the entire database development. Just PITR). For one, sometimes you have no choice about the standby being shut down, but most of the time you can plan for that. As for Csaba's question of why I would want to create a copy of a standby, its the easiest way to create development and testing snapshots at standby locations, and for making paranoid operations people confident that your standby procedures are working. I do it with my Oracle (pardon the 'O' word) installations all the time, and I despise being able to do something with Oracle that I can't with PG. I ship WAL logs around in batches independent of the archive command to several locations. Either I : A) let the logs 'pile up' on the standby (crap has to pile up somewhere), and apply them should the standby be needed (could take some time should the 'pile' be large). The only way here to keep the recover time short is to re-image the database frequently and ship it around. Not nice with big databases. B) Do the blocking recover command to continually apply the logs as they get moved around. While this can generate good clever points, its a rig. Fragile. To me the question isn't 'How is shutting down the standby a good idea?', its 'How is shutting down the standby not a bad idea?'. Different points of view, I suppose - In my situation the standby going offline is not a catastrophic event like the primary would be; its even a useful thing. If there was some rman-style thing like people have suggested to auto-ship logs around, then yeah, dealing with an offline standby could be a tricky thing (but would need some solution anyway). But hell, Slony and Mammoth can tolerate it, I just would like log shipping to handle it also. Maybe it isn't #1 priority, but its something I view as a limitation, and not just lacking a feature. Its something I can't control. As I originally mentioned, the customizable archive/restore feature is great, superior to dealing with it in Oracle. But the standby mode makes the Oracle setup more bulletproof. -- Andrew Rawnsley Chief Technology Officer Investor Analytics, LLC (740) 587-0114 http://www.investoranalytics.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pg_hba.conf alternative
Hello, Is there not some other alternative to pg_hba.conf? I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. With pg_hba.conf that is not possible, as they just change all the conf lines to trust auth and viola they have access to the database without passwords. Is there a more secure alternative to this? The perfect scenario being to deny everyone include root access to a database without a password. regards, Quintin Beukes ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_hba.conf alternative
Hi, On Wed, 2006-02-08 at 14:34 +0200, Q Beukes wrote: I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. As they have access to whole filesystem, they can access anything, even if you enable password auth (they'd switch to trust auth and reload postmaster). They can also copy the data dir to another server and search the You should either trust your sysadms, or work with people who you trust. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_hba.conf alternative
I think this was discussed many times on this list, and the main conclusion was: if you don't trust your DB machine's admin, any security measure against him will be only illusory. The sysadmin can in any case access the data, you can just make this harder, you can't prevent that. So you better get admins who you trust... On Wed, 2006-02-08 at 13:34, Q Beukes wrote: Hello, Is there not some other alternative to pg_hba.conf? I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. With pg_hba.conf that is not possible, as they just change all the conf lines to trust auth and viola they have access to the database without passwords. Is there a more secure alternative to this? The perfect scenario being to deny everyone include root access to a database without a password. regards, Quintin Beukes ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] sql row constructor...works!
* Martijn van Oosterhout (kleptog@svana.org) wrote: It's in CVS HEAD, not 8.1 It seems to me that (x,y) (a,b) means (x a AND y b) ... which is not exactly what you wanted... or I'm missing something ? Yes, it changed because someone pointed out that the behaviour in 8.1 was wrong. Sounds like a bug, will it be in 8.1.3 or do we have to wait till 8.2 for it? Sounds very interesting indeed... Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_hba.conf alternative
On Wed, Feb 08, 2006 at 02:34:29PM +0200, Q Beukes wrote: Is there not some other alternative to pg_hba.conf? I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. With pg_hba.conf that is not possible, as they just change all the conf lines to trust auth and viola they have access to the database without passwords. Or they just copy the whole database to another machine and access it that way. Or copy your backups. Or hack the application accessing the data (the application has the password in it, right?). If can stop them doing those things you can stop them altering pg_hba.conf too so your problem is solved. Is there a more secure alternative to this? The perfect scenario being to deny everyone include root access to a database without a password. Well, you could change the source to remove struct auth, but then they'd just compile their own version and overwrite the system one. Yes, we're looking for alternatives for pg_hba.conf, but what you want is to dam a river with sheets of paper. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] sql row constructor...works!
On Wed, Feb 08, 2006 at 07:49:32AM -0500, Stephen Frost wrote: * Martijn van Oosterhout (kleptog@svana.org) wrote: It's in CVS HEAD, not 8.1 It seems to me that (x,y) (a,b) means (x a AND y b) ... which is not exactly what you wanted... or I'm missing something ? Yes, it changed because someone pointed out that the behaviour in 8.1 was wrong. Sounds like a bug, will it be in 8.1.3 or do we have to wait till 8.2 for it? Sounds very interesting indeed... Well, the bug is really that we accept the syntax but do the wrong thing. I don't know when it was added but the quick fix would be to refuse the syntax. I think the changes to make it work were too large to be in a point release. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[HACKERS] Situation with delphi7 x postgresql 8.1.2
Hi, I develop under Delphi7(Build 8.1) pgexpress 4.01 and postgreSQL 8.1.2 OS is windows XP Professional or Windows 2003 Server. In my project i use dbexpress components TSQLConnection, TSQLQuery - TDataSetProvider - TClientDataSet. In some situations when the TClientDataSet excute action "TClientDataSet.Open" this freeze the aplication and CPU Usage is over 95%. This situation happen only when the server is native windows. If necessary i have one project and database for example. Thanks, Márcio [EMAIL PROTECTED]
Re: [HACKERS] pg_hba.conf alternative
Hello, Is there not some other alternative to pg_hba.conf? I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. If they have full access, then they have FULL access. With pg_hba.conf that is not possible, as they just change all the conf lines to trust auth and viola they have access to the database without passwords. You are looking for a security that can not exit in your scenario. Is there a more secure alternative to this? The perfect scenario being to deny everyone include root access to a database without a password. They only way to secure data is to remove all access to it. If you don't trust your admins, then you have the wrong admins. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_hba.conf alternative
Q Beukes wrote: Hello, Is there not some other alternative to pg_hba.conf? I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. With pg_hba.conf that is not possible, as they just change all the conf lines to trust auth and viola they have access to the database without passwords. Is there a more secure alternative to this? The perfect scenario being to deny everyone include root access to a database without a password. This is an illusion, as plenty of security experts will tell you. Password auth is a losing game for high security in the first place. So this comment shows that you haven't thought this out properly. If you want the data hidden from system administrators, you need to have the client encrypt it before storing it. Of course, that will have massive implications for your application. There are no simple solutions. See here for why: http://www.acm.org/classics/sep95/ cheers andrew ---(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] [pgsql-advocacy] Audio interview
David Fetter wrote: On Tue, Feb 07, 2006 at 11:43:40PM -0500, Bruce Momjian wrote: I did an audio interview today, and it is online now: http://bsdtalk.blogspot.com/2006/02/bsdtalk015-interview-with-postgresql.html Great interview. You hit a lot of the high points :) You mentioned in-place upgrade scripts. Are those in contrib/ somewhere? On GBorg? On PgFoundry? If not, could you put them /contrib/pgupgrade somewhere? As far as converting them from shell to Perl, I'm sure you'll find a flock of volunteers to help. Yea, but the problem with modifying the disk pages is still a problem. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_hba.conf alternative
Q Beukes wrote: Hello, Is there not some other alternative to pg_hba.conf? I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. With pg_hba.conf that is not possible, as they just change all the conf lines to trust auth and viola they have access to the database without passwords. Is there a more secure alternative to this? The perfect scenario being to deny everyone include root access to a database without a password. This is an illusion, as plenty of security experts will tell you. Password auth is a losing game for high security in the first place. So this comment shows that you haven't thought this out properly. If you want the data hidden from system administrators, you need to have the client encrypt it before storing it. Of course, that will have massive implications for your application. And even then, your admins will probably have access to the application source and, if they want, can get data. The unpopular reality is that if you must keep something secret, you can't give access to it to anyone who is not trusted to keep the secret. The best bet is to have one system that has the secret data, managed by those who are trusted. It means that the trusted people are on the hook for backups and preventive maintenence, but secrets aren't free. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] FW: PGBuildfarm member asp Branch HEAD Status changed from OK to Make failure
It looks like all buildfarm members are failing this morning... Here is an example. -rocco -Original Message- From: PG Build Farm [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 08, 2006 5:32 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: PGBuildfarm member asp Branch HEAD Status changed from OK to Make failure The PGBuildfarm member asp had the following event on branch HEAD: Status changed from OK to Make failure The snapshot timestamp for the build that triggered this notification is: 2006-02-08 10:23:00 The specs of this machine are: OS: AIX / 5.2 Arch: powerpc Comp: gcc / 3.3.2 For more information, see http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=aspbr=HEAD ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_hba.conf alternative
Well, I am not looking for 100% security. I know that full access if full access, and that even if you were to encrypt the system through Postgre the determined person WILL always be able to get it out if they have system level access. All I wanted to do was to prevent the basic SQL/Linux literate user from accessing the databases. At the moment it is very easy for them to access the data. I trust that they wont go as far as overwriting the system with custom compiled version, or copying the data and so forth. It just that we would feel much better if we knew the data wasn't as open as it is now, with a simple pg restart it is all open? Can this only be done by maybe modifying the source to make pg_hba fields statically compiled into the executable? Martijn van Oosterhout wrote: On Wed, Feb 08, 2006 at 02:34:29PM +0200, Q Beukes wrote: Is there not some other alternative to pg_hba.conf? I have the problem where the system administrators at our company obviously have access to the whole filesystem, and our database records needs to be hidden even from them. With pg_hba.conf that is not possible, as they just change all the conf lines to trust auth and viola they have access to the database without passwords. Or they just copy the whole database to another machine and access it that way. Or copy your backups. Or hack the application accessing the data (the application has the password in it, right?). If can stop them doing those things you can stop them altering pg_hba.conf too so your problem is solved. Is there a more secure alternative to this? The perfect scenario being to deny everyone include root access to a database without a password. Well, you could change the source to remove struct auth, but then they'd just compile their own version and overwrite the system one. Yes, we're looking for alternatives for pg_hba.conf, but what you want is to dam a river with sheets of paper. Have a nice day, ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_hba.conf alternative
Q Beukes wrote: Well, I am not looking for 100% security. I know that full access if full access, and that even if you were to encrypt the system through Postgre the determined person WILL always be able to get it out if they have system level access. All I wanted to do was to prevent the basic SQL/Linux literate user from accessing the databases. At the moment it is very easy for them to access the data. mechanism is there for a reason: I trust that they wont go as far as overwriting the system with custom compiled version, or copying the data and so forth. It just that we would feel much better if we knew the data wasn't as open as it is now, with a simple pg restart it is all open? Can this only be done by maybe modifying the source to make pg_hba fields statically compiled into the executable? Of course it would be possible to hardcode the values - it's a SMOC. But nobody round here is likely to do the work reuired, since nobody believes it's worth doing, I believe. This mechanism you object to is there for a reason: if you lock yourself out of the database you can recover from the error. The solution you are proposing is therefore a huge footgun. And your user with basic linux/sql knowledge would still be able to see data fly by, for example, logging statements, or watching network traffic. How hard is it to run ethereal, after all, or tail a log file? There is even a module for ethereal that understands the postgres wire protocol. You aren't asking for security - you are asking for the illusion of security, which many would argue is worse than no security at all. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_hba.conf alternative
Q Beukes schrieb: Well, I am not looking for 100% security. I know that full access if full access, and that even if you were to encrypt the system through Postgre the determined person WILL always be able to get it out if they have system level access. All I wanted to do was to prevent the basic SQL/Linux literate user from accessing the databases. At the moment it is very easy for them to access the data. I trust that they wont go as far as overwriting the system with custom compiled version, or copying the data and so forth. It just that we would feel much better if we knew the data wasn't as open as it is now, with a simple pg restart it is all open? Can this only be done by maybe modifying the source to make pg_hba fields statically compiled into the executable? Instead, you might want to read about SELinux. You can protect files even to root (unless they reboot ;) but really you should have only trusted people have admin accounts. How comes you have somebody untrusted as admin? Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] sql row constructor...works!
* Martijn van Oosterhout (kleptog@svana.org) wrote: On Wed, Feb 08, 2006 at 07:49:32AM -0500, Stephen Frost wrote: Sounds like a bug, will it be in 8.1.3 or do we have to wait till 8.2 for it? Sounds very interesting indeed... Well, the bug is really that we accept the syntax but do the wrong thing. I don't know when it was added but the quick fix would be to refuse the syntax. I think the changes to make it work were too large to be in a point release. Ah, ok, I misunderstood. Looking forwrad to having it (and having it work correctly!) in 8.2 :) Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] adding a new catalog
Hi I am trying to add a new catalog to the system. I had followed the instructions in the comments. Now I can see the definition of the new catalog table and its index in file postgres.bki after doing make. However, initdb still did not create the new catalog table. From the debug information of initdb, it only creates other catalogs. What steps did I miss here? -- andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] adding a new catalog
andrew wrote: I am trying to add a new catalog to the system. I had followed the instructions in the comments. Now I can see the definition of the new catalog table and its index in file postgres.bki after doing make. However, initdb still did not create the new catalog table. From the debug information of initdb, it only creates other catalogs. What steps did I miss here? Are you sure that the postgres.bki file that initdb is picking up contains your modifications? i.e. did you make install in the whole source tree? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Expression index with function based on current_user?
Fredrik Olsson [EMAIL PROTECTED] writes: CREATE VIEW foo AS SELECT * FROM private.t_foo WHERE private.haveaccess(); GRANT ALL ON foo TO public; If the function is parameterless as you show, what is it accomplishing that could not be done with grant/revoke on the view? If it is not parameterless, you had better be more specific about what it depends on. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Upcoming re-releases
The core committee has agreed that it's about time for a new set of update releases (8.1.3, 8.0.7, etc). Barring surprises, we'll wrap Sunday evening with expectation of general announcement Tuesday. Any pending patches out there for the back branches? regards, tom lane ---(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] sql row constructor...works!
On 2/8/06, Tom Lane [EMAIL PROTECTED] wrote: Just for the record, it's not the row constructor stuff that just got fixed, it's row-value comparison. We were able to construct rows correctly before, but we didn't compare them in the correct column- by-column fashion. Please call it by the right name in your blog to avoid future confusion. right..that was poor phrasing on my part. will fix merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FW: PGBuildfarm member asp Branch HEAD Status changed from OK to Make failure
On Wed, Feb 08, 2006 at 09:43:48AM -0500, Rocco Altier wrote: It looks like all buildfarm members are failing this morning... Here is an example. Looks like someone did something silly in the bison file for ecpg. It's complaining about an undeclared yydebug... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] adding a new catalog
oh, my mistake. I only do make install-bin. Now it is successfully created. Thanks. On 2/8/06, Alvaro Herrera [EMAIL PROTECTED] wrote: andrew wrote: I am trying to add a new catalog to the system. I had followed the instructions in the comments. Now I can see the definition of the new catalog table and its index in file postgres.bki after doing make. However, initdb still did not create the new catalog table. From the debug information of initdb, it only creates other catalogs. What steps did I miss here? Are you sure that the postgres.bki file that initdb is picking up contains your modifications? i.e. did you make install in the whole source tree? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Upcoming re-releases
* Tom Lane ([EMAIL PROTECTED]) wrote: The core committee has agreed that it's about time for a new set of update releases (8.1.3, 8.0.7, etc). Barring surprises, we'll wrap Sunday evening with expectation of general announcement Tuesday. Any pending patches out there for the back branches? I'd really like to see the multiple DB connections with different Kerberos credentials go in to 8.1.3. It solved the problem we were having authenticating to PostgreSQL using Kerberos from Apache. We were also able to get phppgadmin to use Kerberos authentication with this patch (which is very nice). That patch also went into the 8.1.2-2 release of the Debian packages (along with the pg_restore patch which was already committed to CVS). Havn't heard of any problems with it so far, though 8.1.2-2 only hit the Debian mirrors yesterday. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Upcoming re-releases
Hi, On Wed, 2006-02-08 at 11:28 -0500, Stephen Frost wrote: I'd really like to see the multiple DB connections with different Kerberos credentials go in to 8.1.3. It solved the problem we were having authenticating to PostgreSQL using Kerberos from Apache. We were also able to get phppgadmin to use Kerberos authentication with this patch (which is very nice). That patch also went into the 8.1.2-2 release of the Debian packages [OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Upcoming re-releases
* Devrim GUNDUZ ([EMAIL PROTECTED]) wrote: On Wed, 2006-02-08 at 11:28 -0500, Stephen Frost wrote: I'd really like to see the multiple DB connections with different Kerberos credentials go in to 8.1.3. It solved the problem we were having authenticating to PostgreSQL using Kerberos from Apache. We were also able to get phppgadmin to use Kerberos authentication with this patch (which is very nice). That patch also went into the 8.1.2-2 release of the Debian packages [OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] Guess you don't use Debian much. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Upcoming re-releases
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: Any pending patches out there for the back branches? I'd really like to see the multiple DB connections with different Kerberos credentials go in to 8.1.3. That's a new feature, not a bug fix. I'd be against back-patching it even if it had been in HEAD long enough to get some meaningful amount of testing ... and since it's not even in HEAD yet ... regards, tom lane ---(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] Upcoming re-releases
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: Any pending patches out there for the back branches? I'd really like to see the multiple DB connections with different Kerberos credentials go in to 8.1.3. That's a new feature, not a bug fix. I'd be against back-patching it even if it had been in HEAD long enough to get some meaningful amount of testing ... and since it's not even in HEAD yet ... It's a bug. PostgreSQL properly supports using different authentication credentials across seperate pg_connect()'s for all of the other authentication types. The only reason it doesn't for Kerberos is because of improper use of static variables which aren't reset between the authentication requests. The patch fixes this and cleans up the static variable handling. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Upcoming re-releases
* Devrim GUNDUZ ([EMAIL PROTECTED]) wrote: [OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] And it's days like these that make me happy to be running Debian. My thanks go to Martin for his excellent work. Enjoy, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Upcoming re-releases
The core committee has agreed that it's about time for a new set of update releases (8.1.3, 8.0.7, etc). Barring surprises, we'll wrap Sunday evening with expectation of general announcement Tuesday. Any pending patches out there for the back branches? I'd like to see the bugfix part of http://archives.postgresql.org/pgsql-patches/2006-01/msg00172.php (postmster_win32_admincheck.patch) if possible. It's a bugfix, and it helps even without the rest of the patch - for third party tools. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Situation with delphi7 x postgresql 8.1.2
Hi, I develop under Delphi7(Build 8.1) pgexpress 4.01 and postgreSQL 8.1.2 OS is windows XP Professional or Windows 2003 Server. In my project i use dbexpress components TSQLConnection, TSQLQuery - TDataSetProvider - TClientDataSet. In some situations when the TClientDataSet excute action TClientDataSet.Open this freeze the aplication and CPU Usage is over 95%. This situation happen only when the server is native windows. If necessary i have one project and database for example. this is more appropriate in pgsql-general. anyways, turn on your query logging and find if the problem is begin generated from your app or the dbexpress middleware. sounds to me like the ususal tcp problem which is usually configuration related. also check out zeos database components for a fantastic set of components for delphi. Merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upcoming re-releases
[OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] This is not a Debian thing. Lots of distributions do it. I wouldn't be surprised if RedHat did it as well. Joshua D. Drake Regards, ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Upcoming re-releases
On Wed, 8 Feb 2006, Tom Lane wrote: The core committee has agreed that it's about time for a new set of update releases (8.1.3, 8.0.7, etc). Barring surprises, we'll wrap Sunday evening with expectation of general announcement Tuesday. Any pending patches out there for the back branches? I still think this should be applied to back branches. The patches queue is really quite a bit behind. http://archives.postgresql.org/pgsql-hackers/2006-01/msg00175.php Kris Jurka ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Upcoming re-releases
* Joshua D. Drake ([EMAIL PROTECTED]) wrote: [OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] This is not a Debian thing. Lots of distributions do it. I wouldn't be surprised if RedHat did it as well. I'm not sure they do but they're not really a fair comparison as they have a somewhat privileged position due to Tom (not that I'm complaining, honestly I think it's wonderful that RH has an apparently dedicated person for Postgres and it's outstanding that it's someone as intelligent and knowledgable as Tom). It's certainly not uncommon for distributions in general to patch programs which make them not-quite pure upstream. Then again, just a recompile can break things too so it's not like pristine source is always an option. It's also true, as has been pointed out before, that Debian uses --enable-integer-datetimes (or whatever the flag is) which isn't enabled by default. For the vast majority of Debian users this is correct and better than the default but it does mean that a PostgreSQL default-options compile will generate a postmaster that can't work with Debian data files. Then again, if you omit --enable-krb5 and the Debian package doesn't then if you move to a default-compiled version you might not be able to authenticate to your database anymore either. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [pgsql-advocacy] Audio interview
On Wed, Feb 08, 2006 at 09:00:46AM -0500, Bruce Momjian wrote: David Fetter wrote: On Tue, Feb 07, 2006 at 11:43:40PM -0500, Bruce Momjian wrote: You mentioned in-place upgrade scripts. Are those in contrib/ somewhere? On GBorg? On PgFoundry? If not, could you put them /contrib/pg_upgrade I see it in the attic, but not in CVS TIP. Is there some way to get it back? Or should it go somewhere else until it's at least slightly working? somewhere? As far as converting them from shell to Perl, I'm sure you'll find a flock of volunteers to help. Yea, but the problem with modifying the disk pages is still a problem. I understand that not everybody will choose this path, but we've gone to a *lot* of trouble--and as you pointed out, have benefitted directly from the effort--to provide pointy-hair checkboxes like the Windows port. In-place upgrade is one of those checkboxes, and I'm pretty confident that getting it working will have at a minimum the same benefits to the rest of the code that making the Windows port did. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgsql-advocacy] Audio interview
David Fetter wrote: On Wed, Feb 08, 2006 at 09:00:46AM -0500, Bruce Momjian wrote: David Fetter wrote: On Tue, Feb 07, 2006 at 11:43:40PM -0500, Bruce Momjian wrote: You mentioned in-place upgrade scripts. Are those in contrib/ somewhere? On GBorg? On PgFoundry? If not, could you put them /contrib/pg_upgrade I see it in the attic, but not in CVS TIP. Is there some way to get it back? Or should it go somewhere else until it's at least slightly working? There is a pgfoundry project, but it appears to be dead: http://pgfoundry.org/projects/pgupgrade This would be a very fine project for someone to pick up (maybe one of the corporate supporters could sponsor someone to work on it?) cheers andrew ---(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] Upcoming re-releases
On Wed, Feb 08, 2006 at 06:36:10PM +0200, Devrim GUNDUZ wrote: On Wed, 2006-02-08 at 11:28 -0500, Stephen Frost wrote: I'd really like to see the multiple DB connections with different Kerberos credentials go in to 8.1.3. It solved the problem we were having authenticating to PostgreSQL using Kerberos from Apache. We were also able to get phppgadmin to use Kerberos authentication with this patch (which is very nice). That patch also went into the 8.1.2-2 release of the Debian packages [OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] Debian had the patch to enable ident over unix domain sockets well before core did. So long that I didn't even realise it was a patch until I compiled my own version. The patch had been submitted upstream but fell between the cracks. As a Debian user it something I like. The changelog entry says: * Add debian/patches/12-krb5-multiusers.patch: - Fix krb5 credential handling in libpq for multiple connections with different users: Don't keep credentials in global variables, but pass them around in a new krb5_info struct. - Patch from Stephen Frost, proposed to be adopted upstream. I consider it a form of preventative bug fixing. Somebody on Debian is bound to complain about it sooner or later, so may as well fix it now. There is a group who likes having kerberos working properly... I imagine if a similar bug affected SSL connections, people would be jumping up and down to have it fixed. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] [pgsql-advocacy] Audio interview
On Feb 8, 2006, at 7:00 AM, Bruce Momjian wrote: David Fetter wrote: On Tue, Feb 07, 2006 at 11:43:40PM -0500, Bruce Momjian wrote: I did an audio interview today, and it is online now: http://bsdtalk.blogspot.com/2006/02/bsdtalk015-interview-with- postgresql.html Great interview. You hit a lot of the high points :) You mentioned in-place upgrade scripts. Are those in contrib/ somewhere? On GBorg? On PgFoundry? If not, could you put them /contrib/pgupgrade somewhere? As far as converting them from shell to Perl, I'm sure you'll find a flock of volunteers to help. Yea, but the problem with modifying the disk pages is still a problem. Maybe this is totally crazy, but for those not using slony but are using incremental backup and want to upgrade without doing a time consuming dump / reload (this is not actually a problem for me as my data is not so large that a dump reload is a huge problem) would it be possible to apply pgupgrade to the physical backup before you restore, then also alter each WAL record as it is restored so that it restores all new pages in the new format. Then you could do all the work on a different box and quickly switch over to it after the restore is complete. You could eliminate most of the downtime. Is that even feasible? Not something that would help me now but it might make some people very happy (and maybe someday I will need it as well.) Rick ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [pgsql-advocacy] PGUpgrade WAS: Audio interview
Andrew, This would be a very fine project for someone to pick up (maybe one of the corporate supporters could sponsor someone to work on it?) We looked at it for Greenplum but just couldn't justify putting it near the top of the priority list. The work/payoff ratio is terrible. One justification for in-place upgrades is to be faster than dump/reload. However, if we're assuming the possibility of new/modified header fields which could then cause page splits on pages which are 90% capacity, then this time savings would be on the order of no more than 50% of load time, not the 90% of load time required to justify the programming effort involved -- especially when you take into account needing to provide multiple conversions, e.g. 7.3--8.1, 7.4 -- 8.1, etc. The second reason for in-place upgrade is for large databases where the owner does not have enough disk space for two complete copies of the database. Again, this is not solvable; if we want in-place upgrade to be fault-tolerant, then we need the doubled disk space anyway (you could do a certain amount with compression, but you'd still need 150%-175% space so it's not much help). Overall, it would be both easier and more effective to write a Slony automation wrapper which does the replication, population, and switchover for you. --Josh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-advocacy] PGUpgrade WAS: Audio interview
On Wed, 2006-02-08 at 11:55 -0800, Josh Berkus wrote: One justification for in-place upgrades is to be faster than dump/reload. However, if we're assuming the possibility of new/modified header fields which could then cause page splits on pages which are 90% capacity, then this time savings would be on the order of no more than 50% of load time Well, if you need to start shuffling heap tuples around, you also need to update indexes, in addition to rewriting all the heap pages. This would require work on the order of VACUUM FULL in the worst case, which is pretty expensive. However, we don't change the format of heap or index pages _that_ often. An in-place upgrade script that worked when the heap/index page format has not changed would still be valuable -- only the system catalog format would need to be modified. The second reason for in-place upgrade is for large databases where the owner does not have enough disk space for two complete copies of the database. Again, this is not solvable; if we want in-place upgrade to be fault-tolerant, then we need the doubled disk space anyway When the heap/index page format hasn't changed, we would only need to backup the system catalogs, which would be far less expensive. -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [pgsql-advocacy] PGUpgrade WAS: Audio interview
On Feb 8, 2006, at 12:55 PM, Josh Berkus wrote: Andrew, This would be a very fine project for someone to pick up (maybe one of the corporate supporters could sponsor someone to work on it?) We looked at it for Greenplum but just couldn't justify putting it near the top of the priority list. The work/payoff ratio is terrible. One justification for in-place upgrades is to be faster than dump/ reload. However, if we're assuming the possibility of new/modified header fields which could then cause page splits on pages which are 90% capacity, then this time savings would be on the order of no more than 50% of load time, not the 90% of load time required to justify the programming effort involved -- especially when you take into account needing to provide multiple conversions, e.g. 7.3-- 8.1, 7.4 -- 8.1, etc. I just posted an idea for first upgrading a physical backup of the data directory that you would create when doing Online backups and then also altering the the WAL log records as they are applied during recovery. That way the actual load time might still be huge but since it could run in parallel with the running server it would probably eliminate 99% of the downtime. Would that be worth the effort? Also all the heavy lifting could be offloaded to a separate box while your production server just keeps running unaffected. The second reason for in-place upgrade is for large databases where the owner does not have enough disk space for two complete copies of the database. Again, this is not solvable; if we want in-place upgrade to be fault-tolerant, then we need the doubled disk space anyway (you could do a certain amount with compression, but you'd still need 150%-175% space so it's not much help). Yeah, anyone who has so much data that they need this feature but isn't willing to back it up is crazy. Plus disk space is cheap. Overall, it would be both easier and more effective to write a Slony automation wrapper which does the replication, population, and switchover for you. Now that is something that I would actually use. I think that a little bit of automation would greatly enhance the number of users using slony. Rick ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-advocacy] PGUpgrade WAS: Audio interview
Josh Berkus josh@agliodbs.com writes: This would be a very fine project for someone to pick up (maybe one of the corporate supporters could sponsor someone to work on it?) We looked at it for Greenplum but just couldn't justify putting it near the top of the priority list. The work/payoff ratio is terrible. I agree that doing pgupgrade in full generality is probably not worth the investment required. However, handling the restricted case where no changes are needed in user tables or indexes would be considerably easier, and I think it would be worth doing. If such a tool were available, I don't think it'd be hard to get consensus on organizing our releases so that it were applicable more often than not. We could postpone changes that would affect user table contents until we'd built up a backlog that would all go into one release. Even a minimal commitment in that line would probably result in pgupgrade working for at least every other release, and that would be enough to make it worthwhile if you ask me ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-advocacy] PGUpgrade WAS: Audio interview
Ühel kenal päeval, K, 2006-02-08 kell 15:51, kirjutas Tom Lane: Josh Berkus josh@agliodbs.com writes: This would be a very fine project for someone to pick up (maybe one of the corporate supporters could sponsor someone to work on it?) We looked at it for Greenplum but just couldn't justify putting it near the top of the priority list. The work/payoff ratio is terrible. I agree that doing pgupgrade in full generality is probably not worth the investment required. However, handling the restricted case where no changes are needed in user tables or indexes would be considerably easier, and I think it would be worth doing. How hard would it be to modify postgres so that it can handle multiple heap page formats ? This could come handy for pgupgrade, but my real interest would be to have several task-specific formats supported even in non-upgrade situations, such as a more compact heap page format for read-only archive/analysis tables. -- Hannu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-advocacy] PGUpgrade WAS: Audio interview
Tom, If such a tool were available, I don't think it'd be hard to get consensus on organizing our releases so that it were applicable more often than not. We could postpone changes that would affect user table contents until we'd built up a backlog that would all go into one release. Even a minimal commitment in that line would probably result in pgupgrade working for at least every other release, and that would be enough to make it worthwhile if you ask me ... We could even make that our first/second dot difference in the future. That is, 8.2 will be pg-upgradable from 8.1 but 9.0 will not. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Situation with delphi7 x postgresql 8.1.2
zeos was better than dbexpress with postgresql and mysql in my test applications...connections were several seconds faster with zeos Gustavo. 2006/2/8, Merlin Moncure [EMAIL PROTECTED]: Hi, I develop under Delphi7(Build 8.1) pgexpress 4.01 and postgreSQL 8.1.2 OS is windows XP Professional or Windows 2003 Server. In my project i use dbexpress components TSQLConnection, TSQLQuery - TDataSetProvider - TClientDataSet. In some situations when the TClientDataSet excute action TClientDataSet.Open this freeze the aplication and CPU Usage is over 95%. This situation happen only when the server is native windows. If necessary i have one project and database for example. this is more appropriate in pgsql-general. anyways, turn on your query logging and find if the problem is begin generated from your app or the dbexpress middleware. sounds to me like the ususal tcp problem which is usually configuration related. also check out zeos database components for a fantastic set of components for delphi. Merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Upcoming re-releases
[OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] And it's days like these that make me happy to be running Debian. My thanks go to Martin for his excellent work. Heh don't log into #postgresql then - we have all pretty much been convinced after years of newbie support that Debian is the son of the devil when it comes to PostgreSQL :) Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Upcoming re-releases
Martijn van Oosterhout kleptog@svana.org writes: On Wed, Feb 08, 2006 at 06:36:10PM +0200, Devrim GUNDUZ wrote: So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. I consider it a form of preventative bug fixing. As against which, you have to consider the probability that the Debian patch breaks something. With a maintainer who is not one of the main PG developers accepting patches that haven't yet been reviewed (much less beta-tested) by the community, that risk seems far from negligible. (Now Red Hat certainly also puts in patches that aren't yet released upstream, but we try to avoid getting ahead of upstream patch development.) regards, tom lane ---(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] Upcoming re-releases
Devrim GUNDUZ wrote: So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. I think the other important thing to consider is that this patch went into debian's unstable branch, not stable. Kris Jurka ---(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
[HACKERS] Schema search for default operator classes (was: [ADMIN] Cross schema Primary Key Indexes problem with datatype in the public schema)
http://archives.postgresql.org/pgsql-admin/2006-02/msg00084.php reports a problem with default btree operator classes that are not in pg_catalog: you can create a UNIQUE or PRIMARY KEY constraint that depends on such an opclass, but then when you pg_dump and try to reload, you get something like pg_restore: [archiver (db)] could not execute query: ERROR: data type public.uniqueidentifier has no default operator class for access method btree HINT: You must specify an operator class for the index or define a default operator class for the data type. Command was: ALTER TABLE ONLY table_1 ADD CONSTRAINT table_1_luuid_pkey PRIMARY KEY (luuid); The problem is that pg_dump sets up a restrictive search path during the restore, basically just the schema of the object being restored (plus the implicit reference to pg_catalog). There are good reasons for that behavior and I'm disinclined to mess with it --- but meanwhile, GetDefaultOpClass only looks at operator classes that are in the current search path. So if the desired opclass is not in pg_catalog and also not in the same schema as the table being restored, you lose. Given that we only allow one default opclass for a datatype regardless of schema (see DefineOpClass), it's not really necessary for GetDefaultOpClass to restrict its search. I can think of some corner cases involving multiple binary-compatible-datatype matches where the restriction might give a unique answer when an unrestricted search would not, but I kinda doubt this would ever arise in practice. The only other solution I can see is to extend the ADD CONSTRAINT syntax to allow explicit specification of an opclass for each column. This might be a good thing to do in itself, but it looks like a new feature to me, rather than something we could reasonably apply as a bug fix. It would certainly be a much larger code change (affecting both pg_dump and the backend) than changing the behavior of GetDefaultOpClass. And it'd not fix the problem for existing dump files, either. So I'm leaning towards removing the search-path dependency of GetDefaultOpClass. Comments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upcoming re-releases
* Christopher Kings-Lynne ([EMAIL PROTECTED]) wrote: [OT] So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. [/OT] And it's days like these that make me happy to be running Debian. My thanks go to Martin for his excellent work. Heh don't log into #postgresql then - we have all pretty much been convinced after years of newbie support that Debian is the son of the devil when it comes to PostgreSQL :) Oh, pah, I'm there already, as 'Snow-Man' and I've heard all about it. Sorry that Debian/stable releases havn't been coming out as frequently as they really should have been. We're working on that, honest! Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Upcoming re-releases
* Tom Lane ([EMAIL PROTECTED]) wrote: Martijn van Oosterhout kleptog@svana.org writes: On Wed, Feb 08, 2006 at 06:36:10PM +0200, Devrim GUNDUZ wrote: So Debian has a patch that is not in 8.1.2? I can't believe that they are doing that -- personally I'm against to add any patch into binaries that is not in the core. I consider it a form of preventative bug fixing. As against which, you have to consider the probability that the Debian patch breaks something. With a maintainer who is not one of the main PG developers accepting patches that haven't yet been reviewed (much less beta-tested) by the community, that risk seems far from negligible. While I appriciate the core developer's expertise I don't think lack of being a core member alone makes Martin's critique of the patch somehow less valuable. I've also posted the patch to both -hackers and -patches and I'd love for the community to review it. And, to be fair, it's going into Debian/unstable and won't be in a stable release without further testing by the Debian/unstable users and Debian/testing users (once it propagates there). Unless there are serious problems with it though I expect it to be in the next stable Debian release (currently slated for the fall, iirc). It wouldn't go into an update to the current Debian/stable as it's not a security fix. I'm still very much of the opinion it's a bug and it's not terribly complicated of a fix when you look at it. The patch looks bigger than the actual change really is because of the structure references. Those pieces aren't actually changed beyond referencing the structure variable instead of the static variable though. (Now Red Hat certainly also puts in patches that aren't yet released upstream, but we try to avoid getting ahead of upstream patch development.) Debian in general doesn't like to differ much from upstream and so it would certainly be nice to have the patch accepted into *some* point release which could be included in the next stable Debian release. It seems unlikely 8.2 will be out with enough time for it go through Debian's testing before the next stable Debian release. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Upcoming re-releases
Kris Jurka wrote: On Wed, 8 Feb 2006, Tom Lane wrote: The core committee has agreed that it's about time for a new set of update releases (8.1.3, 8.0.7, etc). Barring surprises, we'll wrap Sunday evening with expectation of general announcement Tuesday. Any pending patches out there for the back branches? I still think this should be applied to back branches. The patches queue is really quite a bit behind. http://archives.postgresql.org/pgsql-hackers/2006-01/msg00175.php Yes, it is, no question. I am working on the INET + INT patch now. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] [pgsql-advocacy] Audio interview
David Fetter wrote: On Wed, Feb 08, 2006 at 09:00:46AM -0500, Bruce Momjian wrote: David Fetter wrote: On Tue, Feb 07, 2006 at 11:43:40PM -0500, Bruce Momjian wrote: You mentioned in-place upgrade scripts. Are those in contrib/ somewhere? On GBorg? On PgFoundry? If not, could you put them /contrib/pg_upgrade I see it in the attic, but not in CVS TIP. Is there some way to get it back? Or should it go somewhere else until it's at least slightly working? I think from cvsweb you can get to the Attic files. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
