Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
On Sat, Aug 27, 2005 at 12:50:44AM -0400, Bruce Momjian wrote: Jim C. Nasby wrote: I wonder whether it would be saleable to have an option to work around this feature. I'm thinking one of two directions: 1) An alternate type of view that just stores the text of the view and is interpreted at time of use like: CREATE DYNAMIC VIEW foo AS (SELECT * FROM tab) or 2) A command to recompile a view which would go back to the original source and reinterpret it like: ALTER VIEW foo RECOMPILE Or I guess you could have the latter and then add the former as a view that automatically recompiles any time a object it depends on is altered. I agree that CREATE DYNAMIC would be a good thing to have. It would certainly save me time on some of my projects. Can we TODO this? How is this different from materialized views, which is already on the TODO list? The idea behind the DYNAMIC VIEW is that if you made a DDL change in the table it could be reflected in the view. So for example, if you defined a view as SELECT * FROM table; and then added a field to the table that field would also show up in the view. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Bruce Momjian pgman@candle.pha.pa.us writes: How is this different from materialized views, which is already on the TODO list? It's entirely unrelated. Materialized views are about having all the data stored in advance. They're really tables that have some sort of process to keep the data in them in sync with other tables according to a view definition. These dynamic views are really just normal views operationally. The only difference is what happens to them when DDL changes are made to the objects they depend on. In normal SQL standard views column references are resolved at creation time and continue to point to the same physical column despite column renames. And select * doesn't change when new columns are added. What these users and myself would prefer is something that remembers the original view definition text and reinterprets it according to the new definition of the underlying tables. So if I swap two columns by renaming them I could recompile the view and it would swap which columns were used where. And if I add new columns select * would include the new columns. I'm starting to be a little skeptical about CREATE DYNAMIC VIEW. I think what would be better to proceed conservatively and just add a ALTER VIEW RECOMPILE. That at least gives the user a way to easily recover the original intention without having to reenter the view definition manually. It would also be useful to have a warning when any DDL is done to a column being used in a view or adding a new column in any table where a view on the table had a select *. That would be useful independently of any automagic recompile feature. Even if the user has to go fetch the original view definition from his DDL file (which hopefully he saved) the warning will at least make it more likely he'll remember to do so. IF you find there's support for these ideas from the powers that be then the TODOs would look something like: o Add support for ALTER VIEW RECOMPILE which would recreate a view definition using the original SQL DDL definition that originally created it. o Add warning whenever DDL to a table affects a view dependent on that table. Such as when a column is altered that is referenced in the view or when a column is added if a select * appears in the view. o Add some option to CREATE VIEW to cause the above ALTER VIEW RECOMPILE to automatically happen whenever DDL to a table affects the view. I think the first of these two are no-brainers if they're implemented well. The third seems less likely to garner immediate support. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] drop table before create
am 26.08.2005, um 2:11:30 +0430 mailte Lee Harr folgendes: I have not been able to work out how to do this is Postgres 8 (pseudo-code) if exists table foo drop table foo; end create table foo; If I go with drop table foo; create table foo; then it barfs on an empty db. Perhaps this will help you: http://www.milwaukeesoft.com/forums/viewtopic.php?t=79sid=e9281cc9522a6b3c91b33407692c5db4 Regards, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] About ERROR: must be *superuser* to COPY to or from a file
On Sat, Aug 27, 2005 at 01:20:29AM -0400, Greg Stark wrote: Of course that method only applies to a subset of PG users, and completely excludes the Windows side. It might also conflict with security policies that forbid PG from reading and writing outside its own data directory. Well that's already out with the advent of tablespaces. Hell, even before tablespaces Postgres had CREATE DATABASE WITH LOCATION = You can grant permission to create databases to regular users. But these users couldn't choose where the database would end up. The target directory had to be blessed (initdb) by the admin first. For that matter it might be handy to be able to grant permission to regular users to load or dump files to arbitrary locations. The security consequences would have to be documented but I don't think they're so bad that you can say nobody should ever be granting the privilege. Well, they would have access to every world readable file on the system, ie /etc, /usr, /lib, ... most files are world readable. There's a lot of discussion about this, yet no-one has demonstrated that COPY FROM STDIN isn't just as good and avoids all the issues entirely. Checking UIDs is just a hack which would only make a difference if you're on the same machine as the server which by definition is the situation with the least to gain. Copying over a UNIX domain socket is hardly likely to be a bottleneck. As far as I can see the consequence is limited to allowing non-privileged users to *read* data owned by Postgres which basically means being able to read logs and table contents. It doesn't allow regular users to escalate their privileges beyond that (barring poor password choices or passwords in logs). So you can grant this privilege to any user you don't mind having *read* access to the entire database. In many installations I can imagine plenty of users that have read access to the entire database but not write access. Err, anyone who can read pg_shadow can get enough info to login as any user in any database in that cluster, so yes, it does allow people to escalate their privelidges. Not to mention being able to read certificates and pg_ident files and such... There's still no reason why an admin who considered it safe couldn't just create a SECURITY DEFINER function to do it on behalf of the user. The server doesn't need any changes. -- 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. pgpzJlHTqUJsX.pgp Description: PGP signature
Re: [GENERAL] postgresql performance degradation over time....
Bruno, thanks for the reply, we did run vaccum on it.. and we do it regulary to maintain its performance but its not giving the expected results. I dont know but if we delete the entire database and restore it with the dump, then things seems to improve a _LOT_. Isnt vaccum suppose to do the same task for us ?? what could be going any idea ?? tx in advance -sunil On 8/26/05, Bruno Wolff III [EMAIL PROTECTED] wrote: On Fri, Aug 26, 2005 at 22:13:04 +0530, sunil arora [EMAIL PROTECTED] wrote: Hi folks, this is my first post to this emailing list. We are using postgres-7.4 in a Server based application which requires frequent updates and inserts of the database. We have observed a substantial fall in the performance of database server over the time. It works fine for some initial days and then its performace falls... and goes on falling as the days passes by ( there is not much increase in the size of the database) I have heard that updates and deletes in any DB bring in some sort of fregmentation in the database, and we are using vaccum feature ot the postgres to defragment. We have also tweaked the performance parameters in postgresql.conf ... but its not helping. Are you vacuuming the database? If you haven't been, you will probably need to do a vacuum full now to get things down to a reasonable size. You should have regularly scheduled vacuum runs to allow for reuse of deleted tuples. In 8.0 there is a contrib package that does automated vacuum scheduling. In the upcoming 8.1 release (just in beta) that feature is part of the core distribution. If you haven't already, you should read through the server administration part of the documention. ---(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: [GENERAL] Postgresql replication
Chris Travers wrote: 1) Efficiency of network throughput 2) Tolerance to attempts at repeat transactions before replication (emptying an account multiple times) 3) Availability of a transaction. We ended up having to give up #1. It's possible to have our transactions routed to multiple servers before it becomes a final transaction. User1 might request a payment on ServerA. User2 then marks the payment as approved on ServerB. ServerC is authoritative and checks the bank/budget balances before posting as final. After each of these steps requires replication of the latest changes to all other servers. (In theory, the first 2 steps only require replication to the authoritative server but we do so anyways so all servers can act as backups for each other -- pending transactions still need to be restored in case of total DB failure.) There's definitely a delay in terms of getting from point A to point Z; duplicate servers. But there's guaranteed financial integrity, users can connect to any server the load balancer picks and no server requires any other server/connection to be up for individual user tranactions to occur. ---(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: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Well, I just added to TODO: * Allow VIEW/RULE recompilation when the underlying tables change Is dynamic view a industry-standard name? If so, I will add it to the TODO. Updated TODO is: * Allow VIEW/RULE recompilation when the underlying tables change Another issue is whether underlying table changes should be reflected in the view, e.g. should SELECT * show additional columns if they are added after the view is created. --- Greg Stark wrote: Bruce Momjian pgman@candle.pha.pa.us writes: How is this different from materialized views, which is already on the TODO list? It's entirely unrelated. Materialized views are about having all the data stored in advance. They're really tables that have some sort of process to keep the data in them in sync with other tables according to a view definition. These dynamic views are really just normal views operationally. The only difference is what happens to them when DDL changes are made to the objects they depend on. In normal SQL standard views column references are resolved at creation time and continue to point to the same physical column despite column renames. And select * doesn't change when new columns are added. What these users and myself would prefer is something that remembers the original view definition text and reinterprets it according to the new definition of the underlying tables. So if I swap two columns by renaming them I could recompile the view and it would swap which columns were used where. And if I add new columns select * would include the new columns. I'm starting to be a little skeptical about CREATE DYNAMIC VIEW. I think what would be better to proceed conservatively and just add a ALTER VIEW RECOMPILE. That at least gives the user a way to easily recover the original intention without having to reenter the view definition manually. It would also be useful to have a warning when any DDL is done to a column being used in a view or adding a new column in any table where a view on the table had a select *. That would be useful independently of any automagic recompile feature. Even if the user has to go fetch the original view definition from his DDL file (which hopefully he saved) the warning will at least make it more likely he'll remember to do so. IF you find there's support for these ideas from the powers that be then the TODOs would look something like: o Add support for ALTER VIEW RECOMPILE which would recreate a view definition using the original SQL DDL definition that originally created it. o Add warning whenever DDL to a table affects a view dependent on that table. Such as when a column is altered that is referenced in the view or when a column is added if a select * appears in the view. o Add some option to CREATE VIEW to cause the above ALTER VIEW RECOMPILE to automatically happen whenever DDL to a table affects the view. I think the first of these two are no-brainers if they're implemented well. The third seems less likely to garner immediate support. -- greg -- 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql performance degradation over time....
On Sat, Aug 27, 2005 at 18:19:54 +0530, sunil arora [EMAIL PROTECTED] wrote: Bruno, thanks for the reply, we did run vaccum on it.. and we do it regulary to maintain its performance but its not giving the expected results. Did you do VACUUM FULL or just plain VACUUM? I dont know but if we delete the entire database and restore it with the dump, then things seems to improve a _LOT_. Isnt vaccum suppose to do the same task for us ?? what could be going any idea ?? It sounds like you have a lot of dead tuples or index bloat. I think 7.4 had the main index bloat issue fixed, but I think that it was still possible to get bloated indexes in some circumstances. So it might be worth trying to reindex the tables. Note that plain VACUUM only does the job it is supposed to if your FSM setting is large enough to handle all of the dead tuples in a table. It also doesn't move valid tuples around to allow the underlying files to be reduced to the minimum size needed. If things have gotten bad enough you want to do a VACUUM full. (Cluster can be a faster way to do this, but for only a couple of Gigs of data, it may not be worth the trouble.) ---(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: [GENERAL] Postgresql Function Cookbook/General howto
Tony Caduto wrote: Hi Bruce, That would be great :-) Thank you very much. I will keep the author information etc intact. Sure, I put them at: http://candle.pha.pa.us/cookbook/ I will keep them online for 2 weeks. -- 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: [GENERAL] Postgresql Function Cookbook/General howto
pgman wrote: Tony Caduto wrote: Hi Bruce, That would be great :-) Thank you very much. I will keep the author information etc intact. Sure, I put them at: http://candle.pha.pa.us/cookbook/ I will keep them online for 2 weeks. I also created a tarball: ftp://candle.pha.pa.us/pub/postgresql/plpgsql_cookbook.tar.gz That will remain for a long time. -- 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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Jim C. Nasby [EMAIL PROTECTED] writes: How is this different from materialized views, which is already on the TODO list? The idea behind the DYNAMIC VIEW is that if you made a DDL change in the table it could be reflected in the view. So for example, if you defined a view as SELECT * FROM table; and then added a field to the table that field would also show up in the view. But why exactly is this a good idea? It seems like an absolutely horrible idea to me. It is oft-repeated advice that you don't use SELECT * ever in production programming, because your applications will break as soon as any columns are added (or removed, even if they don't make any use of those columns). The proposed dynamic view facility would move that instability of results right into the views. What's more, I cannot see any benefit to be gained over just issuing the expanded query directly. You couldn't layer a normal view over a dynamic view (not having any idea what columns it'll return), nor even a prepared statement, because those things nail down specific result columns too. So it's just an awkwardly expressed form of query macro that can only be used in interactively-issued commands. I think the burden of proof is on the proponents of this idea to show that it's sensible, and it doesn't deserve to be in TODO just because one or two people think it'd be nice. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] A strange problem
Hi, I'm new to postgresql. Anytime I type the following command to the database to run, it give me no result record if table 'country' is empty but can get result if 'country' is not empty. Is this so strange? select A.* from test.currency A, test.country B where A.curr_cd='USD' ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] A strange problem
On Sat, 27 Aug 2005, Tang Tim Hei wrote: Hi, I'm new to postgresql. Anytime I type the following command to the database to run, it give me no result record if table 'country' is empty but can get result if 'country' is not empty. Is this so strange? Not really. You're doing a cartesian join between test.currency and test.country. If there are no rows in test.country, there are no rows in the output of the from clause. select A.* from test.currency A, test.country B where A.curr_cd='USD' ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A strange problem
Tang Tim Hei [EMAIL PROTECTED] writes: Hi, I'm new to postgresql. Anytime I type the following command to the database to run, it give me no result record if table 'country' is empty but can get result if 'country' is not empty. Is this so strange? select A.* from test.currency A, test.country B where A.curr_cd='USD' You're doing an unconstrained join on currency and country, and if one of those two tables is empty the result will be empty. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Tom Lane wrote: But why exactly is this a good idea? Especially since it seems one could write PLPGSQL functions to emulate this if it was so important. The lack of PLPGSQL functions to do this seems to indicate that it is not worth the trouble, It is oft-repeated advice that you don't use SELECT * ever in production programming, because your applications will break as soon as any columns are added (or removed, even if they don't make any use of those columns). With all due respect, I often use SELECT * in production programming becuase the languages I write in allow me to return the results as a hash table. In this case, things only break if you delete a needed column, and added columns have no real effect unless they are of substantial size (but this is a separate issue). With a good RAD environment (such as Python, Perl, or PHP), I don't think that SELECT * is unduly dangerous. The proposed dynamic view facility would move that instability of results right into the views. Again, this would not be *that* hard to do with PLPGSQL and a function like redefine_view(name, text) which would store the definitions of the views in a temporary table, and then rebuild dependant views. Again the fact that it is not commonly done indicates simply that there is no need. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql replication
William Yu wrote: Chris Travers wrote: 1) Efficiency of network throughput 2) Tolerance to attempts at repeat transactions before replication (emptying an account multiple times) 3) Availability of a transaction. We ended up having to give up #1. It's possible to have our transactions routed to multiple servers before it becomes a final transaction. User1 might request a payment on ServerA. User2 then marks the payment as approved on ServerB. ServerC is authoritative and checks the bank/budget balances before posting as final. After each of these steps requires replication of the latest changes to all other servers. (In theory, the first 2 steps only require replication to the authoritative server but we do so anyways so all servers can act as backups for each other -- pending transactions still need to be restored in case of total DB failure.) Ok. I see where you are going with this. It is an interesting problem. Multimaster Async Replication will give you a problem in that it allows the attack you are describing due to the asynchronous nature of the replication. If I were trying to prevent this sort of attack, I would try to build into this some sort of account authority which can manage these transactions. Origionally I was thinking of the home server as the obvious place to start if it is available. But if it is not, then you would need some infrastructure to track attempted withdrawals and handle them appropriately. Such servers could cache requests and if they see duplicates or many requests coming from many servers on the same account could flag that. One option might be to have a rotational authority (i.e. home-server, then next, then next) in a ring so that an unavailable server still allows reasonable precautions to be held against emptying the account many times. Basically, if the servers aren't talking to eachother at the time of the transaction, then they are not going to know about duplicates. You have to have some setup beyond your replication to handle this. Otherwise you will have some issues with delays causing the security risks you deem unacceptable. Your question seems to be How do I set up multimaster async replication such that a person cannot empty his account on each server and the answer is that this is an inherent limitation of multimaster async replication. This also means that you will have to have some sort of other verification process for such transactions beyond what is locally available on the replicants. There's definitely a delay in terms of getting from point A to point Z; duplicate servers. But there's guaranteed financial integrity, users can connect to any server the load balancer picks and no server requires any other server/connection to be up for individual user tranactions to occur. The delay will by definition defeat any guarantee of financial integrity if you are allowing read-write operations to the replica without checking with some sort of central authority. At very least, the central authority should look for suspicious patterns. Again, it may be possible to do some failover here, but I don't think you can do without *some* sort of centralized control. (Note, here load balancing is handled by the distribution of accounts. A down server simply means that the next server in the ring will take over its remote verification role). This doesn;t make the security issue go away, but it may reduce it to an acceptable level. I.e. it is still possible for duplicates to be submitted just before and after a home server goes down, but this is a lot better than being able to have one transaction repeated on each server and then dealing with the massively overdrawn account. Best Wishes, Chris Travers Metatron Technology Consulting ---(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
[GENERAL] An update rule affecting an after insert trigger
Hey Folks, Scratching my head on this one wanted to know if anyone else had encounter it. I have a table that has an update rule and an after insert trigger on it. The rule checks to see if certain things happened during the update and goes and does another insert to a secondary table. The trigger is an after insert trigger to call a stored plpgsql function. The problem I am seeing is that I cannot get the trigger to fire. I did a simple test just to verify that triggers are working in general and they are but nothing for this one. I have even intentionally broken the stored procedure just to see if I was making it to that logic but nothing. Does anyone know what might cause this to occur? Simrin Grewal ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Bruce Momjian pgman@candle.pha.pa.us writes: Well, I just added to TODO: * Allow VIEW/RULE recompilation when the underlying tables change Is dynamic view a industry-standard name? If so, I will add it to the TODO. DYNAMIC is something I made up. ALTER VIEW RECOMPILE is Oraclese but I'm not sure what we're talking about here is exactly the same purpose. I'm not sure it even does anything in Oracle any more. It used to be that *any* DDL on underlying tables caused view on them to become invalid and produce errors until they were recompiled. I think that's changed and recompile may be a noop now on Oracle. Updated TODO is: * Allow VIEW/RULE recompilation when the underlying tables change Another issue is whether underlying table changes should be reflected in the view, e.g. should SELECT * show additional columns if they are added after the view is created. I think we're 100% certain that it should not do this by default. The spec requires it. What's unknown is whether there should be an option to do it automatically. In my humble opinion there's no downside to having a facility for users to do it manually though. Hence why I separated it into three points: o Add warning whenever DDL to a table affects a view dependent on that table. Such as when a column is altered that is referenced in the view or when a column is added if a select * appears in the view. o Add some option to CREATE VIEW to cause the above ALTER VIEW RECOMPILE to automatically happen whenever DDL to a table affects the view. I think the first of these two are no-brainers if they're implemented well. The third seems less likely to garner immediate support. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Dumb question about 8.1 beta test
The notes on participating in the 8.1 beta suggest creating a dump using both an old and new copy of pg_dump. Does this mean we can't use pg_dumpall or that we have to restore both dumps? (Or is that just a way of testing what works and what doesn't between older dump files and the beta release?) -- Mike Nolan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql replication
Our own personal IM :) Chris Travers wrote: The delay will by definition defeat any guarantee of financial integrity if you are allowing read-write operations to the replica without checking with some sort of central authority. At very least, the central authority should look for suspicious patterns. Again, it may be possible to do some failover here, but I don't think you can do without *some* sort of centralized control. Actually this is the easy part. When the home server finally issues payments, it only issues what it knows about and what can be verified as OK. Any transactions that are currently being entered on another server will appear after the next replication cycle and it will be verified afterwards. If the previous payment issuing cycle used up all the money, the new requests are kept in pending until money is put in. This does allow for newer requests that happen to be executed on home servers to possibly take precendence over old requests but there is no requirement in the business process that payments must come out in any specific order. This doesn;t make the security issue go away, but it may reduce it to an acceptable level. I.e. it is still possible for duplicates to be submitted just before and after a home server goes down, but this is a lot better than being able to have one transaction repeated on each server and then dealing with the massively overdrawn account. The home server going down is the trickiest issue. Because when a server disappears, is that because it went down temporarily? For good? A temporary internet problem where nobody can get access to it? Or an internet routing issue where just the connection between those two servers is severed? If it's the last, users might still be doing stuff on ServerA with ServerA is posting financials but ServerB thinks the server is down and decides to take over ServerA's duties. Of course, in ServerA's view, it's ServerB and ServerC that's down -- not itself. Maybe we can mitigate this by having more servers at more data centers around the world so everybody can monitor everybody. At some point, if you have N servers and N-1 servers say ServerA is down, it probably is down. With a high enough N, ServerA could probably decisively decide it was the server severed from the internet and refuse to post any financials until connection to the outside world was restore + some extra threshold. This last problem, which luckily occurs rarely, we do by hand right now. We're not ready to run this on full auto because we only have 2 data centers (with multiple servers within each data center). The servers do not have enough info to know which server is actually down in order to auto-promote/demote. It does require staff that's not just in 1 location though because our primary office going down w/ our local datacenter would mean nobody there could do the switchover. (Assuming major natural disaster that kept us from using our laptops at the local Starbucks to do the work.) ---(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: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Tom Lane [EMAIL PROTECTED] writes: Jim C. Nasby [EMAIL PROTECTED] writes: How is this different from materialized views, which is already on the TODO list? The idea behind the DYNAMIC VIEW is that if you made a DDL change in the table it could be reflected in the view. So for example, if you defined a view as SELECT * FROM table; and then added a field to the table that field would also show up in the view. But why exactly is this a good idea? It seems like an absolutely horrible idea to me. It is oft-repeated advice that you don't use SELECT * ever in production programming, because your applications will break as soon as any columns are added (or removed, even if they don't make any use of those columns). The proposed dynamic view facility would move that instability of results right into the views. Just because something is oft-repeated doesn't make it good advice. I am convinced that advice originates in the fact that many databases handled select * very poorly. These other databases often had limitations like having it produce errors or even incorrect results if the underlying table was changed. From a programming aesthetics point of view it's downright important to use it. Not using it forces the programmer to distribute knowledge about columns and how they will be used throughout many more layers of programming than otherwise necessary. If Far from breaking as soon as columns are added or removed, the use of select * insulates the application from the changes. I can add a column to my front-end templates without having to modify every layer below it. Or can add a column to a database and use it immediately in the front-end without modifying every layer in between. What's more, I cannot see any benefit to be gained over just issuing the expanded query directly. You couldn't layer a normal view over a dynamic view (not having any idea what columns it'll return), nor even a prepared statement, because those things nail down specific result columns too. So it's just an awkwardly expressed form of query macro that can only be used in interactively-issued commands. I think we have two different ideas of what we're talking about. I'm talking about absolutely normal views. They can be used in the same ways and behave the same as normal views. I'm just suggesting adding a command that would do exactly the same thing as having the user issue a CREATE OR REPLACE VIEW with the exact same definition text as originally used. The point here is to give a user an out who would otherwise be completely stuck. If he didn't save the original view definition text he has to now reverse engineer what was intended from the reconstructed view definition that pg_dump gives which isn't always obvious. I think the burden of proof is on the proponents of this idea to show that it's sensible, and it doesn't deserve to be in TODO just because one or two people think it'd be nice. I think, given the confusion shown by myself and this other user, that the evidence is there that the spec behaviour violates the principle of least surprise and warrants warnings. I think just about any time these warnings would be fire there's a better than 50% chance the programmer is about to be bitten by a nasty surprise. The alter view recompile is the thing one or two people think would be nice. I can offer a use case for alter view recompile that might be somewhat more convincing than hand waving: Consider the case of someone who has a large growing table with log records. He wants to periodically rotate it out and start a fresh table. Much like what logrotate does for files. Now any view on that table will follow the renamed table instead of using the fresh new table. If the user doesn't keep around a complete DDL definition for the table he can't even fix the problem robustly. He has to try to reconstruct all the views and hopefully get their definitions right. In fact the situation is quite similar to the situation with daemons that don't reopen their log files regularly. In those cases however those daemons invariably support reopening their log files on some even like kill -HUP. This is precisely because restarting the daemon is intrusive and error prone, just as having to reconstruct the view definitions from scratch would be. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] About ERROR: must be *superuser* to COPY to or from a file
Martijn van Oosterhout kleptog@svana.org writes: On Sat, Aug 27, 2005 at 01:20:29AM -0400, Greg Stark wrote: For that matter it might be handy to be able to grant permission to regular users to load or dump files to arbitrary locations. The security consequences would have to be documented but I don't think they're so bad that you can say nobody should ever be granting the privilege. Well, they would have access to every world readable file on the system, ie /etc, /usr, /lib, ... most files are world readable. There's a lot of discussion about this, yet no-one has demonstrated that COPY FROM STDIN isn't just as good and avoids all the issues entirely. Well they're world-readable. So, uh, huh? Checking UIDs is just a hack which would only make a difference if you're on the same machine as the server which by definition is the situation with the least to gain. Copying over a UNIX domain socket is hardly likely to be a bottleneck. You keep saying that. Why do you think so? Every bit of data has to be read in by one process, copied into the kernel again, a context switch has to happen, and then it has to be copied out of the kernel into another process, and then written back again by the second process. If the machine is cpu-bound it will cut its throughput dramatically. Context switches are expensive. You would have to have a fast disk subsystem for this to really be an issue, and be copying a lot of data for the slowdown to be really annoying. But there are plenty of applications that fit that exact description. Data ware houses spend most of their waking hours loading huge data sets using very large and very fast raid arrays. If you think checking uid is a hack (I don't see why it's at all hackish) then I would suggest the really clean way of handling this is to simply pass the fd along the unix domain socket. But it's not supported on nearly as many variants of unix as simply checking the uid. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Dumb question about 8.1 beta test
Mike Nolan [EMAIL PROTECTED] writes: The notes on participating in the 8.1 beta suggest creating a dump using both an old and new copy of pg_dump. Does this mean we can't use pg_dumpall or that we have to restore both dumps? (Or is that just a way of testing what works and what doesn't between older dump files and the beta release?) (1) There's no reason not to use pg_dumpall. (2) I think the point of the suggestion is to make sure we have some test coverage for both ways of doing the upgrade ... but that doesn't mean each tester has to do it both ways. Pick one. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] About ERROR: must be *superuser* to COPY to or from a file
Martijn van Oosterhout kleptog@svana.org writes: There's a lot of discussion about this, yet no-one has demonstrated that COPY FROM STDIN isn't just as good and avoids all the issues entirely. In any case here's some quick results from my system. There seems to a greater than 21% slowdown associated with piping the data through two processes instead of reading directly. This file is small enough to have probably fit entirely within cache, but then on the other hand I don't have the kind of fast RAID arrays data warehouses live with. A fast raid array would mean both that reading in the raw data would be more like my situation here where reading in the data is a small part of the time and *also* that writing out the data which my machine had to stream to a typical consumer level drive would take less time and that's probably the dominant time in this test. bash-3.00$ for i in `seq 1 10` ; do psql -d stark -c 'truncate table t' ; time psql -d stark -c '\copy t from '''postalcodes.dat''' with delimiter '''\t ; done 21 | grep real real0m5.223s real0m5.262s real0m5.322s real0m5.613s real0m5.394s real0m5.221s real0m5.365s real0m5.445s real0m5.247s real0m5.238s bash-3.00$ for i in `seq 1 10` ; do psql -d stark -c 'truncate table t' ; time psql -U postgres -d stark -c 'copy t from '''/home/stark/src/saleslookout/postalcodes.dat''' with delimiter '''\t ; done 21 | grep real real0m4.011s real0m4.058s real0m4.308s real0m4.498s real0m4.220s real0m4.049s real0m4.131s real0m4.488s real0m4.166s real0m4.152s -- greg ---(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: [GENERAL] An update rule affecting an after insert trigger
On Sat, Aug 27, 2005 at 11:08:26AM -0700, Simrin Grewal wrote: I have a table that has an update rule and an after insert trigger on it. The rule checks to see if certain things happened during the update and goes and does another insert to a secondary table. The trigger is an after insert trigger to call a stored plpgsql function. The problem I am seeing is that I cannot get the trigger to fire. You say you have an *update* rule and an *insert* trigger on the same table. Is that correct? Are you doing an INSERT when you expect the trigger to be called? It would be easier to help if we could see a complete but minimal example that demonstates the problem. That is, the smallest possible example that somebody could load into an empty database and see the behavior you're seeing. Also, what version of PostgreSQL are you using? I have even intentionally broken the stored procedure just to see if I was making it to that logic but nothing. Adding RAISE statements with debugging messages might be preferable to breaking the code. -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Jim C. Nasby [EMAIL PROTECTED] writes: How is this different from materialized views, which is already on the TODO list? The idea behind the DYNAMIC VIEW is that if you made a DDL change in the table it could be reflected in the view. So for example, if you defined a view as SELECT * FROM table; and then added a field to the table that field would also show up in the view. But why exactly is this a good idea? It seems like an absolutely horrible idea to me. It is oft-repeated advice that you don't use SELECT * ever in production programming, because your applications will break as soon as any columns are added (or removed, even if they don't make any use of those columns). The proposed dynamic view facility would move that instability of results right into the views. Just because something is oft-repeated doesn't make it good advice. I am convinced that advice originates in the fact that many databases handled select * very poorly. These other databases often had limitations like having it produce errors or even incorrect results if the underlying table was changed. So it seems to be an implimentation, not a data problem. I will also state that some applications go off numeric column indexes which cause problems if a column is deleted. Imagine SELECT * FROM customers ORDER BY 2; Drop the first column and replace it with another column at the end of the table and suddenly the ordering changes Similarly if you go off column numbers, suddenly your data types are off and your application doesn't know what to do with the data. This is why this is OK if you get it as a hash table, but not if you get it as a simple array. From a programming aesthetics point of view it's downright important to use it. Not using it forces the programmer to distribute knowledge about columns and how they will be used throughout many more layers of programming than otherwise necessary. If Far from breaking as soon as columns are added or removed, the use of select * insulates the application from the changes. I can add a column to my front-end templates without having to modify every layer below it. Or can add a column to a database and use it immediately in the front-end without modifying every layer in between. Well said. Now, let me give you an example. I created a view for a retail management application I maintain for inventory activity on a daily basis. I then created another view to compile these into quarterly reports. The workaround is to keep the views on a separate .sql file and replay them agains thte database when you want to change something on an underlying view. I don't know. On this matter I am fairly undecided. I think it would be useful to have it, but it is a really minor enhancement I think, and I would rather see the core developers focus on other more pressing matters. IMO, this is not a bad idea. It just is not that necessary at the moment. Especially since such a framework could be written pretty easily in plpgsql. Indeed I would rather see a prototype in plgsql than in the backend at the moment. But this is just me. I think we have two different ideas of what we're talking about. I'm talking about absolutely normal views. They can be used in the same ways and behave the same as normal views. I'm just suggesting adding a command that would do exactly the same thing as having the user issue a CREATE OR REPLACE VIEW with the exact same definition text as originally used. So, why not write a plgsql function that does as follows: CREATE FUNCTION define_view(name, text) returns BOOL AS ' DECLARE vname ALIAS FOR $1; vdef ALIAS FOR $2; BEGIN INSERT INTO def_view (view_name, view_definition) values (vname, vdef); EXECUTE ''CREATE OR REPLACE VIEW ''||vname||'' AS ''||vdef; RETURN TRUE; END; ' LANGUAGE PLPGSQL; CREATE FUNCTION recompile_view (name) RETURNS BOOL AS ' DECLARE vname ALIAS FOR $1; vdef TEXT; BEGIN SELECT INTO vdef view_definition FROM def_view WHERE view_name = vname; EXECUTE ''CREATE OR REPLACE VIEW ''||vname||'' AS ''||vdef; RETURN TRUE; END; ' LANGUAGE PLPGSQL; You might have to extend this to track and maintain rules for inserting and updating on the view. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 6: explain analyze is your friend
回覆： Re: [GENERAL] A strange problem
寄件者: Stephan Szabo [EMAIL PROTECTED] 日期: 2005/08/27 星期六 下午 11:25:49 HKT 收件者: Tang Tim Hei [EMAIL PROTECTED] 副本: pgsql-general@postgresql.org 主旨: Re: [GENERAL] A strange problem On Sat, 27 Aug 2005, Tang Tim Hei wrote: Hi, I'm new to postgresql. Anytime I type the following command to the database to run, it give me no result record if table 'country' is empty but can get result if 'country' is not empty. Is this so strange? Not really. You're doing a cartesian join between test.currency and test.country. If there are no rows in test.country, there are no rows in the output of the from clause. select A.* from test.currency A, test.country B where A.curr_cd='USD' In the above command, I just add another table reference to it and it gives me two different results. Even I add columns like B.* to it, it do the same things too. Is it not consistance? In real world, I don't know the 'country' table is empty or not. Sometime it is empty and sometime it is not empty, this will confuse me whether my program has bug or not. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: 回覆： Re: [GENERAL] A strange problem
On Sun, 28 Aug 2005, Tang Tim Hei wrote: ?H: Stephan Szabo [EMAIL PROTECTED] : 2005/08/27 ?P ?U?? 11:25:49 HKT ??: Tang Tim Hei [EMAIL PROTECTED] : pgsql-general@postgresql.org ?D??: Re: [GENERAL] A strange problem On Sat, 27 Aug 2005, Tang Tim Hei wrote: Hi, I'm new to postgresql. Anytime I type the following command to the database to run, it give me no result record if table 'country' is empty but can get result if 'country' is not empty. Is this so strange? Not really. You're doing a cartesian join between test.currency and test.country. If there are no rows in test.country, there are no rows in the output of the from clause. select A.* from test.currency A, test.country B where A.curr_cd='USD' In the above command, I just add another table reference to it and it gives me two different results. I'm not 100% sure what you mean, but if you mean that basically adding the reference to test.country to a statement that looked like select A.* from test.currency A where A.curr_cd='USD', yes it gives different results, but it also means something different. Even I add columns like B.* to it, it do the same things too. Is it not consistance? In real world, I don't know the 'country' table is empty or not. Well, you have to write your queries to do what you want depending on such things. For example, the above doesn't constrain the join from currency and country and so you get multiple copies of the USD currency info for each country. If you want to constrain the currency and country information (for example, say A.country=B.id if you had that sort of information) you need to decide what happens if there is no country that matches the country. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ?^???G Re: A strange problem
On Sun, Aug 28, 2005 at 08:46:56 +0800, Tang Tim Hei [EMAIL PROTECTED] wrote: In the above command, I just add another table reference to it and it gives me two different results. Even I add columns like B.* to it, it do the same things too. Is it not consistance? In real world, I don't know the 'country' table is empty or not. Sometime it is empty and sometime it is not empty, this will confuse me whether my program has bug or not. You might want some sort of outer join in that case. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgresql replication
William Yu [EMAIL PROTECTED] writes: Chris Browne wrote: I'm pretty sure that they _don't_ track balance updates for each transaction that applies to a customer's account. You could, via one form of trickery or another, overdraw your account by a fairly hefty amount, and they probably won't notice for a day or even three. But once they notice/estimate that the Gentle Caller has built up some dangerously high balance, they'll warn of impending discontinuation of service if some sum isn't paid by some date. This works for companies that have some degree of power over their customers. E.g. pay up or we disconnect your service. Return your overdrafts/pay your fees or we mess up your credit. This doesn't work if it's a small company who's account has been emptied. Sure the bank will refuse to honor the check but then that company will be hit with overdraw penalties and possible legal penalties to the payee for the bounced check. The warning threshold system is easy to implement but there will always be corner cases where the warning is not soon enough or a single payment wipes out ~ 100% of the account. Warn too often and it'll be ignored by people as a boy crying wolf alarm. In a context where there is no degree of power over their customers, I would hardly think that the presence/lack of automated controls or presence/lack of balance synchronization is particularly material. In other words, if trying to apply policies is going to forcibly break, then building the data system may have been a futile exercise in the first place. And trying to distribute the system may again be a *business* mistake that admits no technical solution. If that's the case, then we can conclude that replication is no solution, and that the organization should improve connectivity for their single centralized system. That may be the case, but is the uninteresting case, as it amounts to throwing our hands up, saying no answers; you can't have a distributed system, and giving up on trying anything further. No point to further discussion... -- let name=cbbrowne and tld=acm.org in String.concat @ [name;tld];; http://cbbrowne.com/info/linux.html There is no reason anyone would want a computer in their home. -- Ken Olson, Pres. and founder of Digital Equipment Corp. 1977 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
My first idea when this was mentioned was more like ALTER TABLE CASCADE where CASCADE meant recompile all the views that depend on that table. Not that I think any of this is a good idea, but if it was going to be done, that's what would make the most sense to me. - Ian ---(end of broadcast)--- TIP 6: explain analyze is your friend
