Re: [GENERAL] hundreds of schema vs hundreds of databases
Albe Laurenz wrote: Advantages of many databases: - Each database is smaller. - No danger of one user accessing another user's data (because of misconfigured permissions and similar). - Guaranteed independence of each user's data. - More scalable: If you decide that one machine or one cluster is not enough to handle the load, you can easily transfer some of the databases somewhere else. Advantages of one database with many schemata: - Fewer databases to administrate. Using different databases for each user incurs the full overhead of creating and maintaining a database: all the system tables and all the memory required to keep a database open. If the OP is allowing direct SQL access to each user, then the risks you identify above must be addressed, but tbey can fairly simply by using scripts to create each new user. I'd opt for using schemas unless there is a compelling evidence that different databases are required. -- Guy Rouillier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.3
Raymond O'Donnell wrote: I realise that (i) this is something of a FAQ, and (ii) the definitive answer is "When it's ready", but when (roughly) is 8.3 planned to be released? I'm not one of the developers, but I found the following information here http://www.postgresql.org/developer/roadmap: The next release of PostgreSQL is planned to be the 8.3 release. As of April 1, 2007, the code for 8.3 is in feature freeze. All patches have been submitted for review and application. Patches submitted after this date will be held for a later version. The remaining rough timeline for the development of 8.3 is: * mid-May, 2007 - All patches applied, beta testing begins * July, 2007 - Release of 8.3.0 -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] multimaster
Alexander Staubo wrote: As it stands today, horizontally partitioning a database into multiple separate "shards" is incredibly invasive on the application architecture, and typically relies on brittle and non-obvious hacks such as configuring sequence generators with staggered starting numbers, omitting referential integrity constraints, sacrificing transactional semantics, and moving query aggregation into the app level. On top of this, dumb caches such as Memcached are typically layered to avoid hitting the database in the first place. Have you looked at raidb? http://c-jdbc.objectweb.org. Narrow niche, but if it happens to be the one you are in, then it's an option. I took a quick look at the user's page, and both of them were using PostgreSQL. -- Guy Rouillier ---(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] multimaster
PFC wrote: Have you looked at raidb? http://c-jdbc.objectweb.org. Narrow niche, but if it happens to be the one you are in, then it's an option. I took a quick look at the user's page, and both of them were using PostgreSQL. I just love those Java guys. The world starts and ends with Java. How do you execute a psql script with that thing ? How do you actually use any non-java stuff with it ? That's why I said "Narrow niche, but if it happens to be the one you're in ...". I do find your response rather peculiar. The objectweb guys saw a need and filled it in the domain they work in - Java. Should the Java folks complain because Perl scripts on CPAN are not accessible from Java? -- Guy Rouillier ---(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] Encrypted column
Ranieri Mazili wrote: Hello, I need to store users and passwords on a table and I want to store it encrypted, but I don't found documentation about it, how can I create a table with columns "user" and "password" with column "password" encrypted and how can I check if "user" and "password" are correct using a sql query ? Many people consider two-way encryption to be insecure; two-way encryption means you can decrypt a value if you know the key, and it is insecure because you usually have to put the key into the source code. That means at least one person in your company, the programmer maintaining the source code, can learn all of your users' passwords. One way around that is to hash the value instead. Then to validate, at runtime you hash the user-entered password using the same hash function, and validate that it matches the stored hash. No one in your company ever knows end-user passwords. -- Guy Rouillier ---(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: Creditcard Number Security was Re: [GENERAL] Encrypted column
Richard P. Welty wrote: Guy Fraser wrote: Have you thought about setting up an account with PayPal, and having people pay through PayPal? Let PayPal deal with the security, and credit card info, after all it's what they do. at the day job, when we switched from paypal (who we found very undependable) to authorize.net, we were very pleased to discover that authorize.net would take care of the credit card numbers for us, so we didn't have to try to secure them beyond the usual requirements while the numbers are in transit. i would definitely recommend outsourcing for this if at all possible. Paypal has a perception issue - they are perceived as being tightly linked with eBay. That's a problem in the corporate arena. If my stock broker were to tell me they do all their financial transactions through Paypal, I'd probably wonder if they were a legitimate corporation. Do any of these outsourcers indemnify corporate customers against fraud or data loss? -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Creditcard Number Security was Re: [GENERAL] Encrypted column
Alvaro Herrera wrote: My "bank" is also "not a bank" (they say they are not "FDIC insured" which I think is the actual problem at hand). Do I have to be worried? Depends what you use it for. If this is an online bank that you use only for online transactions and you maintain a balance of say $800, you probably don't have to worry. If on the other hand this is your only bank and you have your life savings in there, you most definitely should consider the risks to which you are exposing your finances. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using the power of the GPU
Billings, John wrote: Does anyone think that PostgreSQL could benefit from using the video card as a parallel computing device? Well, I'm not one of the developers, and one of them may have this particular scratch, but in my opinion just about any available fish has to be bigger than this one. Until someone comes out with a standardized approach for utilizing whatever extra processing power exists on a GPU in a generic fashion, I can't see much payback for writing special code for the NVIDIA 8800. -- Guy Rouillier ---(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] [pgsql-advocacy] [PERFORM] [ADMIN] Postgres VS Oracle
Joshua D. Drake wrote: 2. Oracle, Microsoft, and IBM have a "lot" to fear in the sense of a database like PostgreSQL. We can compete in 90-95% of cases where people would traditionally purchase a proprietary system for many, many thousands (if not hundreds of thousands) of dollars. Well, I'm sure that is part of it, perhaps the major part. But part of also is likely to be avoiding every shlub with a computer doing some off-the-wall comparison showing X to be 1000 times "better" than Oracle, SQL Server or DB2; then the corresponding vendor has to spend endless time and money refuting all these half-baked comparisons. -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Writing most code in Stored Procedures
Steve Manes wrote: I'm fairly hardcore about keeping as much business logic as I can in the database. In fact, I only do SELECTs from the application, and usually via Views. All inserts, updates and deletes are via procs. ... And, yes, it's faster. Particularly if business logic decisions have to be made in context with a transaction. I have a thread I started ages ago over on the PERFORM list that I'm sadly just now being able to provide some insight on. I'll be replying on that thread in more detail, but the short of it turns out to be that at least in this one application, using stored procs for inserts is slowing down the app considerably. The app does high volume inserts and updates, about 16 million rows a day. By switching from stored procs to inline inserts, elapsed time dropped from 2595 seconds to 991 seconds for a test run. So the moral of the story is that, as anyone who has worked professionally for a number of years knows, no magic bullets exist. General guidelines can be helpful, but each scenario must be individually investigated. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Writing most code in Stored Procedures
Steve Manes wrote: Moving all the application-bound inserts into stored procedures didn't achieve nearly the performance enhancement I'd assumed I'd get, which I figured was due to the overhead of the procs themselves. That's the conclusion I'm coming to as well for my app with very high insert/update rates. Is there a PG knowledgebase where this kind of information might be useful? -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Writing most code in Stored Procedures
Ron Johnson wrote: So why is Perl-SP-INSERT so much slower than Perl-SQL-INSERT? (I can imagine that the SP code path would be longer, but since IO is the slowest part of the system, I'm surprised that it's *that* much slower.) I'm guessing that since PG allows overloaded SP names, the slowness is coming from resolving which SP to run. But that is just a guess. In my environment, I don't *have* overloaded SPs, only a single version of a given name. But when I was doing the conversion from Oracle, it took me a couple tries to get the SP signatures correct, so I'm pretty sure PG is still going through the resolution logic, even if you only have a single instance of a given name. -- Guy Rouillier ---(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] history table
Robin Helgelin wrote: Hi, I want to save history for a few tables using triggers on update and creation. What's the best approach to do this in a webapp environment where I want to save which webapp user that is doing the change, not the postgresql user? Well, you haven't told us much about your webapp. Are you using connection pooling? If so, then you'll need to provide the webapp userid as an additional parameter to your database updates. If you are not using connection pooling, such that your webapp userids are connecting as themselves, then the problem becomes much easier; you've got the correct userid to log by just looking at the connection details. -- Guy Rouillier ---(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] history table
Robin Helgelin wrote: On 8/21/07, Guy Rouillier <[EMAIL PROTECTED]> wrote: Well, you haven't told us much about your webapp. Are you using connection pooling? If so, then you'll need to provide the webapp userid as an additional parameter to your database updates. If you are not using connection pooling, such that your webapp userids are connecting as themselves, then the problem becomes much easier; you've got the correct userid to log by just looking at the connection details. Yes, this is where I'm too new to postgresql, how do I tell the database which user is logged in to the webapp? A session parameter? There will be connection pooling, but if I know how to solve the previous question I don't think it's hard to get it working with the pool. Well, I can't find a way to set a variable associated with a connection, so probably the easiest thing to do is to add an "updated_by" column to your regular table (i.e., the non-history version.) Then just include the userid from your webapp as the value for that column. Your history table can then be updated by just copying the entire row from the base table whenever an insert or update occurs. If you don't like the idea of adding an "updated_by" column to your base table, then you can wrap the insert inside of a stored proc and pass the userid value to the stored proc. The proc can update the base table without the userid, then update the history table with it. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] an other provokative question??
Greg Smith wrote: the idea of one database approach always being right just isn't true anyway. Or one software development approach or one application runtime approach or ... . One of the major contributions of the relational model compared to the myriad network model implementations prevalent at the time was that the relation model was a straightforward approach whose theory was easily grasped and applied. The model made *people* more productive; certainly, network implementations could run circles around the early relational implementations, so it didn't make database servers more productive. Any alternative DBMS approach would have to provide a similar level of human productivity. Given relational's staying power over the intervening 25+ years (with many more people available to research alternatives and much more available computing power), that appears to be a significant challenge. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SLEEP in posgresql
Jasbinder Singh Bali wrote: Hi, I have a while loop and I want to re-iterate after every 't' seconds. I was reading up on the postgresql documentation that says pg_sleep(t) should be handy. However i doesn't work. Hmmm, I'm looking at section 9.9.5 Delaying Execution in the PostgreSQL 8.2.0 Documentation. Following the example presented there, I fired up psql and ran the following: postgres=# select current_timestamp; select pg_sleep(3); select current_timestamp; now 2007-10-09 23:50:32.649-04 (1 row) pg_sleep -- (1 row) now 2007-10-09 23:50:35.774-04 (1 row) Seems to be working. What version are you using and on what platform? -- Guy Rouillier ---(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] can I define own variables?
Tom Lane wrote: Sam Mason <[EMAIL PROTECTED]> writes: On Sat, Oct 13, 2007 at 12:18:45AM +0200, Andreas wrote: can I define connection-global variables within a ODBC connection ? You could use a temporary table, keyed on the variable name. Not particularly nice to use, but it works. You can create accessor functions reasonably easily if you want. Another possibility, if you only need to store and retrieve values and not do anything especially interesting with them, is to abuse the "custom GUC variable" facility. This is meant to provide placeholder parameter settings for dynamically-loaded extension modules, but there's nothing stopping you from setting and reading a variable that in fact will never be used by any extension module. To do this, you need to set custom_variable_classes in postgresql.conf, perhaps custom_variable_classes = user_vars and then you can do things like SET user_vars.foo = whatever; SHOW user_vars.bar; Of course, this is a hack of the first water, and you should expect that it might break sometime in the future. But I don't think we'd break it without providing some alternative solution. I've had several occasions when a user-defined variable would have come in handy. What is the scope of user_vars as you've defined them above? Are they unique to a connection? A user_id? Or defined globally? Ideally, they would be connection-unique. One place they would come in really handy is with web-apps using connection pooling. I could stuff the userid of the person who connected to my secure website into a user_var, then the database could access that to track user actions. Right now, that is cumbersome to do, since the current_user is always the same: the userid for the connection pool. I'm actually surprised that such a common usage scenario has not forced connection-unique user-defined variables into the spec. -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] can I define own variables?
Tom Lane wrote: Guy Rouillier <[EMAIL PROTECTED]> writes: Tom Lane wrote: Another possibility, if you only need to store and retrieve values and not do anything especially interesting with them, is to abuse the "custom GUC variable" facility. I've had several occasions when a user-defined variable would have come in handy. What is the scope of user_vars as you've defined them above? They'd act like any other GUC variable. The scope of a SET would be the current connection, but there are other ways to set them with larger scope. 8.2.0 on Windows doesn't like "user_vars" as a value for custom_variable_classes, but "uservars" is okay. I set that up, and using the SHOW command returns the set value. I searched the archives and I couldn't identify a way to retrieve these values in a trigger function. When I try to execute the following: CREATE OR REPLACE FUNCTION assign_user_name() RETURNS trigger AS $$ BEGIN NEW.user_name := (SHOW uservars.user_name); RETURN NEW; END; $$ LANGUAGE plpgsql; it complains: ERROR: syntax error at or near "uservars" LINE 1: SELECT (SHOW uservars.user_name) ^ QUERY: SELECT (SHOW uservars.user_name) CONTEXT: SQL statement in PL/PgSQL function "assign_user_name" near line 2 ** Error ** ERROR: syntax error at or near "uservars" SQL state: 42601 Context: SQL statement in PL/PgSQL function "assign_user_name" near line 2 I tried the same with a standard GUC (deadlock_timeout) and got the same error, so this has nothing to do with custom classes. How can I retrieve GUCs within a pl/pgsql function? Thanks. -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] can I define own variables?
Tom Lane wrote:
Guy Rouillier <[EMAIL PROTECTED]> writes:
I set that up, and using the SHOW command returns the set value. I
searched the archives and I couldn't identify a way to retrieve these
values in a trigger function.
http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE
Thank you, Tom. Since "select current_setting('uservars.user_name')"
and "SHOW uservars.user_name" are both PostgreSQL extensions, why not
allow them to be used interchangeably? In short, why isn't the
following legal in a PL/pgsql procedure:
NEW.user_name := (SHOW uservars.user_name);
--
Guy Rouillier
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to set config param temporarily?
Kynn Jones wrote: Is there a less laborious approach? The root of needing to go through all this song and dance is that I don't know of any way to set up a simple temporary variable to hold a value. The temporary table is the closest I can come up to implementing a temporary variable. Is there a simpler approach? See the message thread "can I define own variable?" for a discussion of how to create a temporarily variable. Took place over the last week. -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] downloading and installing postgreSQL
Ken Johansson wrote: Im installing the latest software ( postgresql-8.2.5-1-binaries-no-installer.) on windows xp but i dont know what file to click to install - this seems very ambiguous. Can anyone help? You don't mention platform, but Win32 is the only one with a no-installer binary, so I guess we can assume you are installing on some approved version of Windows. Why did you elect a no-installer version? The regular version comes with an installer and is very easy to install. I see the following note on the download page: "postgresql-8.2.5-1-binaries-no-installer.zip is a zip of the PostgreSQL installation directory. It does not include any of the bundled apps or drivers and is intended for expert users only!" It's a zip file, you just unzip it where you want it installed. But since you don't know what to do with it, I'd suggest you go with postgresql-8.2.5-1.zip instead. Unzip in a temporary directory, read the README file, then double click the MSI file (or just type the name at a command prompt if you use that.) -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] looking for some real world performance numbers
Dave Cramer wrote: snacktime wrote: I'm working through the architecture design for a new product. We have a small group working on this. It's a web app that will be using ruby on rails. The challenge I'm running into is that the latest conventional wisdom seems to be that since obviously databases don't scale on the web, you should just not use them at all. I have a group of otherwise very bright people trying to convince me that a rdbms is not a good place to store relational data because eventually it won't scale. And of course we don't even have version 1 of our product out of the door. I'll admit we do have a very good chance of actually getting tons of traffic, but my position is to use a rdbms for relational data, and then if and when it won't scale any more, deal with it then. So what would really help me is some real world numbers on how postgresql is doing in the wild under pressure. If anyone cares to throw some out I would really appreciate it. I missed the original post on this, so I'm replying to Dave's response. To the OP, I don't know where you obtain your conventional wisdom from, but I'd look for another source. Just about any site you might visit that handles lots of data has a DBMS of some sort behind it; given that IMS and Adabase have been out of favor for 20 years, most of those DBMSs are relational. So if it can work for your bank, E*Trade and eBay, chances are it can work for you. As far as real world numbers, we have a data-intensive app (network data collection for a telecom company) that is currently inserting about 16 million rows a day. I benchmarked PG for that app and with some tweaking, PG could handle it. The current app uses stored procedures for all inserts, and PG didn't do well with that approach; substituting embedded inserts fixed that problem. So PG can definitely "handle" very large transaction volumes. As with any DBMS and any application, you may encounter challenges (like the one I point out with using stored procs for high-volume inserts) that require you to address with some thought. -- Guy Rouillier ---(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] looking for some real world performance numbers
Ow Mun Heng wrote: On Sun, 2007-10-21 at 22:54 -0400, Guy Rouillier wrote: The current app uses stored procedures for all inserts, and PG didn't do well with that approach; substituting embedded inserts fixed that problem. So PG can definitely "handle" very Can you explain what is embedded inserts? Insert via embedded SQL insert statements in our Java code, as opposed to embedded SQL stored proc invocations, which in turn do the inserts. The existing code base used the latter approach, which didn't work well with PG. I suspect it has to do with PG's stored proc overload capability. The short of it is that *any* DBMS you use will have its own quirks that you become acquainted with and learn to work around. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How install postgreSQL in another drive than C:?
Josi Perez wrote: I'm trying install postgreSQL in an external HD, but I don't know how to change the root directory. By default the installation uses drive C: Is it possible to change to F: (for example)? Sure. Although you don't identify which version you are trying to install or the platform you are trying to install on, you mention "drive C:" so we can safely assume that you are talking about some version of Windows. We can hopefully assume since you are at this stage of the game that you are using the prebuilt binary with installer. In that case, when you see the "Installment options" pane during the installation, look at the bottom of the window. You should see "Current location" with a destination of "C:\Program Files\PostgreSQL\\". Click the Browse button and change the destination. -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Install plJava
João Paulo Zavanela wrote: The file pljava.dll exist in directory, why this error? Someone can help me? PL/Java has it's own mailing list here: http://gborg.postgresql.org/mailman/listinfo/pljava-dev I think it is still active, but I'm not sure. Sorry, I'm short on time. Search the archives there; I think this problem has come up before. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] I want to search my project source code
Matthew Wilson wrote: I have a lot of code -- millions of lines at this point, written over the last 5 years. Everything is in a bunch of nested folders. At least once a week, I want to find some code that uses a few modules, so I have to launch a find + grep at the top of the tree and then wait for it to finish. I wonder if I could store our source code in a postgresql table and then use full text searching to index. Then I hope I could run a query where I ask for all files that use modules X, Y, and Z. DBMSs are great tools for the right job, but IMO this is not the right job. I can't see how a database engine, with all it's transactional overhead and many other layers, will ever beat a simple grep performance-wise. I've used Eclipse for refactoring, but having done it once, I'm sticking with grep. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Temporary, In-memory Postgres DB?
Michelle Konzack wrote: I run an Opteron 140 with 8 GByte of memory and sometimes I have problems with too less memory... but unfortunatly I have not found a Singel-Opteron Mainboard which support more then 8 GByte of memory where I prefere to use 16-32 GByte... Tyan makes a server motherboard with a single Opteron socket and 8 DIMM slots: http://www.tyan.com/product_board_detail.aspx?pid=229 -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Hijack!
Gregory Williamson wrote: * a lot of us have to use what ever the company provides as mail server. Exchange sucks but I'd rather not quit my job just because _you_ have a problem reading mail that does not conform to the "T" to your expectations. I'm guessing you use Outlook to connect to your Exchange server. In that case, Outlook is the one that top posts, not Exchange. I found a utility which can address the Outlook posting style: http://home.in.tum.de/~jain/software/outlook-quotefix/ That site has a similar add-on for Outlook Express. Now, a gripe rightly attributable to the to PG mailing list setup is that every time I reply, I have to: (1) use reply all, because reply is set to go to the individual rather than the list (2) delete all the individual addressees so only the list is left, then change that from CC to TO (3) change my from identity to the one used for the list; although the list always posts to the identity I have set up for mailing lists, for some reason Thunderbird selects a different identity when I reply. -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Sun acquires MySQL
Tom Lane wrote: The bottom line is: if you're doing computationally expensive non-SQL-query operations, plpgsql is simply the wrong language for the job ... and it's not like there are not plenty of others to choose from. I'd expect plperl or even pltcl to be faster for such things (I have no idea about the speed of other scripting languages such as python or ruby). Or pl/java. Also, if what you're doing fits within its capabilities, pl/R is an interesting alternative. Unfortunately, I think the stored procedure implementation in PG itself introduces significant overhead. See thread "Writing most code in Stored Procedures" from August 2007. I converted an application from that BigDBMS we are not allowed to mention to PG. Code is Java, stored procs were written in PL/Java. On the exact same hardware, I couldn't get any where near the throughput I was getting in BigDBMS. The procs are trivial - just wrappers for insert statements. After I exhausted all alternatives, I replaced the stored proc invocation in the code with inserts. Then, PG was able to achieve the same throughput as BigDBMS. -- Guy Rouillier ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] It it possible to get this result in one query?
Sure: select t3.id, coalesce ( t1.title, t2.title, t3.title ), coalesce ( case when t1.title is not null then 'table_one,' else null end, case when t2.title is not null then 'table_two,' else null end, '' ) || 'table_three' from table_three t3 left outer join table_two t2 using (id) left outer join table_one t1 using (id) On 10/14/2010 8:13 PM, Nick wrote: I guess I should mention that im basically searching for a way to recusively coalesce the title. So I want to search the second table and table_one (id,title) 1 | new one table_two (id,title) 2 | new two table_three (id,title) 1 | one 2 | two 3 | three Id like an sql statement that returns... 1 | new one | [table_one,table_three] 2 | new two | [table_two,table_three] 3 | three | [table_three] On Oct 14, 4:49 pm, Nick wrote: Is it possible to get the results of this snip of a function without using a function? All tables include an id and title column. tables := ARRAY[table_one,table_two,table_three]::VARCHAR; CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types VARCHAR[]); FOR t IN ARRAY_LOWER(tables,1) .. ARRAY_UPPER(tables,1) LOOP FOR r IN EXECUTE 'SELECT id, title FROM ' || tables[t] LOOP IF (SELECT TRUE FROM final_results WHERE id = r.id LIMIT 1) THEN UPDATE final_results SET r_types = array_append(r_types,tables[t]) WHERE id = r.id; ELSE INSERT INTO final_results (id,title,r_types) VALUES (r.id,r.title,ARRAY[tables.t]); END LOOP; END LOOP; -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] It it possible to get this result in one query?
Sure, did you look in the documentation? select t3.id, coalesce ( t1.title, t2.title, t3.title ), string_to_array(coalesce ( case when t1.title is not null then 'table_one,' else null end, case when t2.title is not null then 'table_two,' else null end, '' ) || 'table_three', ',') from table_three t3 left outer join table_two t2 using (id) left outer join table_one t1 using (id) On 10/15/2010 2:55 AM, Nick wrote: Thanks Guy, is it possible to get the 3rd column result as an array instead of string? -Nick On Oct 14, 9:27 pm, guyr-...@burntmail.com (Guy Rouillier) wrote: Sure: select t3.id, coalesce ( t1.title, t2.title, t3.title ), coalesce ( case when t1.title is not null then 'table_one,' else null end, case when t2.title is not null then 'table_two,' else null end, '' ) || 'table_three' from table_three t3 left outer join table_two t2 using (id) left outer join table_one t1 using (id) On 10/14/2010 8:13 PM, Nick wrote: I guess I should mention that im basically searching for a way to recusively coalesce the title. So I want to search the second table and table_one (id,title) 1 | new one table_two (id,title) 2 | new two table_three (id,title) 1 | one 2 | two 3 | three Id like an sql statement that returns... 1 | new one | [table_one,table_three] 2 | new two | [table_two,table_three] 3 | three | [table_three] On Oct 14, 4:49 pm, Nickwrote: Is it possible to get the results of this snip of a function without using a function? All tables include an id and title column. tables := ARRAY[table_one,table_two,table_three]::VARCHAR; CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types VARCHAR[]); FOR t IN ARRAY_LOWER(tables,1) .. ARRAY_UPPER(tables,1) LOOP FOR r IN EXECUTE 'SELECT id, title FROM ' || tables[t] LOOP IF (SELECT TRUE FROM final_results WHERE id = r.id LIMIT 1) THEN UPDATE final_results SET r_types = array_append(r_types,tables[t]) WHERE id = r.id; ELSE INSERT INTO final_results (id,title,r_types) VALUES (r.id,r.title,ARRAY[tables.t]); END LOOP; END LOOP; -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint: string length must be 32 chars
On 10/16/2010 12:48 PM, Alexander Farber wrote: snake=> create table gps ( id varchar(32) primary key CONSTRAINT id_length char_length(id)=32, stamp timestamp DEFAULT current_timestamp, pos point); ERROR: syntax error at or near "char_length" LINE 2: id varchar(32) primary key CONSTRAINT id_length char_length(... Sorry, any ideas? (I know it's a stupid question) See the documentation section 5.3.1 Check Constraints. The condition needs to be in parentheses. And in order to name your constraint, you must use the CONSTRAINT variant. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 9.0.1 StackBuilder wants to install pgJDBC v8.4
I've been running PG 9.0 beta 4 on my local workstation, and decided to finally upgrade tonight to the released version 9.0.1-1 on Windows. I used the one-click installer; many thanks to EnterpriseDB for providing these. Installation completed without issue, then offered to run StackBuilder. I ran it, thinking I would use it to download the latest JDBC driver. I was surprised to see it offer pgJDBC v8.4-701-2. The pgJDBC project site has version 9.0-801 available. Why is StackBuilder listing an old 8.4 JDBC version during a 9.0 install when a 9.0 version is available? Doesn't StackBuilder get the list of available files dynamically off the web? Thanks. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why isn't Java support part of Postgresql core?
On 9/18/2014 2:44 PM, cowwoc wrote: Yes, that's what I meant. I just wanted to reinforce the fact that you don't need to bundle multiple JVMs (Oracle, OpenJDK and GCJ). You'd pick one and bundle it alongside PG and pl/java. I've been following along as an interested observer, having used PL/Java in the past, and developing with Java for a living. I don't think bundling is a good idea. Gili, as you fully understand, Java is a moving target. Important vulnerabilities are discovered and updates are pushed out to address. So, any bundled version would be subject to possibly rapid obsolescence. Then there are organizational constraints or concerns. Some will only use official JDKs from Oracle/Sun, others will only use OpenJDK. Some won't move to a new major version until at least the .1 release, others stick with their Java 6 company-wide standard even though that version is officially EOL'd. So, in my opinion the least contentious way to go would be to have a set of instructions that inform the end user to install the JDK or JRE of their choice, subject to defined constraints. Then make PL/Java as painless as possible to install. This should not be a problem with larger organizations, since most use centrally-administered software configuration. Thanks. -- Guy Rouillier --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.2 timestamp function syntax error
I don't understand the error resulting from the following progression on 9.2 (specifically "EnterpriseDB 9.2.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit"): select sysdate => timestamp without time zone select timestamptz(sysdate) => timestamp with time zone select timestamp(timestamptz(sysdate)) => ERROR: syntax error at or near "timestamptz" OR select timestamp(sysdate::timestamptz)) => ERROR: syntax error at or near "sysdate" I see a function in pg_catalog with signature timestamp(timestamp with time zone). Why isn't it being applied? Thanks. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database (Schema) Objects?
On 3/5/2013 2:03 AM, Stefan Keller wrote: Hi Oracle defines database (schema) objects and "non-schema" objects (see [1]). Is there also such a thing in Postgres? Yes. See, for example, CREATE USER, CREATE TABLESPACE and CREATE SCHEMA. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.2 timestamp function syntax error
On 3/5/2013 9:52 AM, Tom Lane wrote:
Adrian Klaver writes:
This does not work:
test=> select timestamp(now()::timestampz);
ERROR: syntax error at or near "now"
timestamp(something) is a type name. Per the comment in gram.y:
* The type names appearing here are not usable as function names
* because they can be followed by '(' in typename productions, which
* looks too much like a function call for an LR(1) parser.
In PgAdmin, here is how timestamp() is defined:
CREATE OR REPLACE FUNCTION "timestamp"(timestamp with time zone)
RETURNS timestamp without time zone AS
'timestamptz_timestamp'
LANGUAGE internal STABLE STRICT
COST 1;
ALTER FUNCTION "timestamp"(timestamp with time zone)
OWNER TO postgres;
COMMENT ON FUNCTION "timestamp"(timestamp with time zone) IS 'convert
timestamp with time zone to timestamp';
and here is now timestamptz() is defined:
CREATE OR REPLACE FUNCTION timestamptz(timestamp without time zone)
RETURNS timestamp with time zone AS
'timestamp_timestamptz'
LANGUAGE internal STABLE STRICT
COST 1;
ALTER FUNCTION timestamptz(timestamp without time zone)
OWNER TO postgres;
COMMENT ON FUNCTION timestamptz(timestamp without time zone) IS 'convert
timestamp to timestamp with time zone';
Ugh, I just noticed the quotation marks around the timestamp function.
This works:
select "timestamp"(now()::timestamptz); => timestamp without time zone
This is a subtlety bound to be lost on most. Why is there both a
function and a type name with the same name? I suppose I could define a
synonym to make the function name distinct, but this seems like
something that should be addressed.
Thanks.
--
Guy Rouillier
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL connect with Visual C++
On 3/4/2013 11:17 PM, dhaval257 wrote: Hello friends, I am new to postgres. I am doing Project on Image processing in OpenCV(IDE i am using is Visual C++ 2010). I have downloaded PostgreSQL 8.4 and installed it successfully. I want to know how to connect postgres with visual C++. You don't mention if you are using ODBC or a direction connection to the pgsql library. I'll assume the latter. I did a Google search for "postgresql visual c" and found this example: http://www.askyb.com/cpp/c-postgresql-example/ If you are just starting with PostgreSQL, you might as well start with the latest version. You can get 9.2.3 from the web site (http://www.postgresql.org/). -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] selecting for type cast failures
On 3/7/2013 8:08 PM, Natalie Wenz wrote:
I am working on updating some of our tables to use appropriate native
data types; they were all defined as text when they were created
years ago.
What I am running into, though, is there are some records that have
bad data in them, where they can't be successfully converted to int,
or float, or boolean, for example.
Is there a straightforward way to identify offending records?
I've been able to identify some with things like "...not similar to
'(0|1)'..." for the boolean fields, and "...not similar to
'[0-9]{1,}'..." for int. Are regular expressions the best approach
here or is there a better way?
I did some quick searching also, looks like regular expressions are your
way to go. Here is one for isInteger, for example:
varchar ~ '^[0-9]+$'
--
Guy Rouillier
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help me to clear postgres problem
On 3/25/2013 7:35 AM, jayaram s wrote: Hello I have installed PostgreSQL 8.4.1 in my PC. For the requirement of data migration I again want to install "PostgreSQL enterprise DB 9.2". I couldn't install it because I have select option "postgresql compatible" on "configuration mode". So prompt wants me to enter "password". I have enter my existing postgres password "postgres'. But I couldn't install. An error message displayed as*"service user account 'postgres' couldnot be created". Please help me to clear the problem* Are you intentionally trying to install PostgresPlus Advanced Server? If you are working just on your PC, you should be able to use the PostgreSQL installer: http://www.enterprisedb.com/products-services-training/pgdownload#windows The password the PPAS installer is asking you for is the password to your EnterpriseDB account, not a local Windows account. You need to register an EnterpriseDB account before you can install PPAS. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ODBC constructs
On 5/21/2013 1:11 AM, Dev Kumkar wrote: Thanks for the comments. Yes objective is to work in C and found libpq useful but am not sure about psqlODBC. It would be really great to get comments from community here regarding comparison between both of them and also performance perspective. Based on my reading so far libpq is good from performance perspective. One advantage of an abstraction layer is that it allows you to switch the underlying DBMS with few if any changes to your code. ODBC is just one of many available abstraction layers. So, one thing to consider is if your application is intended to be at all generic, or if it is specifically targeted to PostgreSQL. If the former, then you should consider an abstraction layer; if not, then libpq is fine. Another determinant for many people is what they already know. If I'm doing a quick, one-time app and I know ODBC but I don't know libpq, I might choose to just stick with what I know and use ODBC. So, really, the first decision you should make is how general you want your app to be, and how long you expect to use it. Other decisions flow from there. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [ODBC] [GENERAL] ODBC constructs
On 5/21/2013 3:32 PM, Dev Kumkar wrote: Well to work with psqlODBC, will I need to recompile the psqlODBC binary and any additional stuff to get ODBC working? No, you don't need to recompile any libraries. You just use them. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MinGW compiled client library
On 8/13/2013 5:25 PM, Michael Cronenworth wrote: On 08/13/2013 01:40 PM, John R Pierce wrote: They use Microsoft Visual C (I forget the exact version, but I believe most recent versions are supported, including the "Express" versions). Then MinGW should be capable of producing the same binaries. I'm curious what led you to that conclusion. The MinGW site itself does not make such a claim. See http://www.mingw.org/wiki/MixingCompilers. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql book - practical or something newer?
Robert Treat wrote: Just so you know, I approached OReally about writing a PostgreSQL Cookbook, and they turned it down. They did offer me some other titles, but those don't seem to have gone anywhere. As someone else pointed out in this thread, very much of what you need to know has been previously discussed at one point; the hard part is finding it. What we need is for some of the people with the big brains ;) to come up with some new kind of "hyperbook". That would be the documentation in some form similar to what it is today, but somehow connected to the discussions that happen in the mailing lists. That way, when something really insightful or helpful gets said in the mailing lists, it can get connected to a particular place in the documentation. Then over time, the doc maintainers can take the best of those and incorporate them directly into the docs at the appropriate place. This would not only benefit those looking for information, but also those hearty and knowledgeable souls (like Tom) who patiently provide it repeatedly as the same questions pop up every couple weeks/months. Plus, the documentation would grow and become much more useful over time. Then, instead of repeating answers to repeating questions, we can just point to the appropriate place in the docs. The "unattached" discussions could identify sections lacking in the docs; i.e., if enough unattached discussions accumulate for a particular topic, then that probably indicates the need for a new section in the docs on that topic. To be honest, I think a hyperbook would be easier to implement with forums than with mailing lists. The former are permanently resident in a known place, while the latter are out there in the ether (or in some unorganized archive that is notoriously hard to link to.) -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [Pljava-dev] pljava.dll - bogus error
Kris Jurka wrote: On Fri, 1 Feb 2008, James Calfee wrote: Please improve error reporting for this: org.postgresql.util.PSQLException: ERROR: could not load library "C:/Program Files/PostgreSQL/8.2/lib/pljava.dll": The specified module could not be found. Unfortunately this error message comes from the server which gets it from windows. So pljava is at least two steps removed from it, so we're in no position to improve it. Perhaps the server guys can coax some more details out of windows. I looked on archives for both mailing lists and could not find this email thread. This error happened to me years ago and I don't remember what the real issue was. Could you point to the actual problem and I'll see if Windows can identify it? -- Guy Rouillier ---(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] 8.3 Feature List mentions Slony 2.0
This is really a web site error report, but I don't see any links on the site to report such errors. On the 8.3 Features List page here: http://www.postgresql.org/about/press/features83.html, it mentions "Version 2.0 of Slony-I, our most popular replication system, now uses the new replication hooks in PostgreSQL 8.3." But if you follow the link, the resulting page only talks about 1.2.12. -- Guy Rouillier ---(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] postgresql book - practical or something newer?
Greg Smith wrote: On Mon, 4 Feb 2008, Dave Page wrote: We intentionally have not done that as we wanted to ensure that all documentation published under postgresql.org was appropriately moderated first. OK, so hosting a probably inaccurate in many ways (at first) community documentation project wiki is inappropriate for a postgresql.org page; completely understandable. That "moderated first" thing is part of the problem with using Techdocs I already mentioned. Can anyone think of another place a community docs wiki could go at? I definitely think it should go on the official PostgreSQL site somewhere - that's where the community is. The documentation page already lists versions of the official docs "with comments". Isn't this an expansion of that? Anyone with a community account is free to post a comment. If all those comments are moderated, then I'd suggest either adding a "Community Version" directly on that page, or adding one to the community page off of Techdocs. -- Guy Rouillier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Feedback on PLJava
Pascal Cohen wrote: Hello Are there users of PL/Java ? I would like to get some feedback on using it. As we could use triggers and as we have already some kinds of 'applicative' triggers written in Java, I was wondering if I could just reuse the code and plug it Pascal, yes, many people employ PL/Java; it works very well. PL/Java is maintained in PGFoundary here: http://pgfoundry.org/projects/pljava/. It has a dedicated mailing lists. Not sure what you mean by "applicative" triggers, but you'll probably be able to reduce the amount of code you maintain if you use real triggers. -- Guy Rouillier ---(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 Docs - Community Documentation
Greg Smith wrote: Thanks to Joshua Drake and Command Prompt for providing hosting space and even having an appropriate domain. To cut off one question I expect to pop up, yes it would be nice to have this integrated with the main postgresql.org site and its existing account structure. But since Joshua was the only person who answered my request for hosting space I used the server he volunteered. We'd be glad to move this to somewhere more official if that were available, I'm focused on creating the content and don't care where it lives at. Greg et al, thanks very much for taking this idea and making it a reality. Since I was one of the ones who requested it, I'll sign up for an account right away (done). Regarding "where it lives", what is the constraint that prevents it from residing on the main site? Space? Money? I see one addition that would be helpful to newcomers to this tool such as myself. The Main Page jumps right into the PG related material; "Getting Started" refers to getting started with PG, not with getting started contributing to the community documentation. I discovered that clicking the "About PostreSQL Docs" link on the bottom of the page produces a short intro to the purpose of the site. Could we also get added to this page a sentence or two about the software that runs the site, and a pointer to documentation on that software? I know, I know, this is "user-contributed" documentation, so I'm free to add that myself. Hopefully you appreciate my Catch-22 ;). "About PostgreSQL Docs" is an important topic, and should be elevated to the same list as "Main Page". -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL Docs - Community Documentation
Dave Page wrote: A postgresql.org wiki to replace techdocs is being worked on, per recent discussion on -www, however it takes a little more effort than a standalone one as we need to integrate it properly into the existing infrastructure. Dave, what is the intention of this wiki, as opposed to the Community Documentation that just got kicked off? -- Guy Rouillier ---(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] Watch your PlanetPostgreSQL.org blogs
Dave Page wrote: Well there's a major part of your misunderstanding. planetpostgresql.org IS NOT a community project. It is a project started and run by a very well liked and respected community member (with the assistance of a few others), that the community - read web team, plus others - support and encourage. If it were a community project it would be under postgresql.org, as all our sites are, (with the exception of pgFoundry) And with the new exception of the community documentation recently started at http://www.postgresqldocs.org. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Watch your PlanetPostgreSQL.org blogs
Tom Lane wrote: Guy Rouillier <[EMAIL PROTECTED]> writes: And with the new exception of the community documentation recently started at http://www.postgresqldocs.org. Which in fact has got only the weakest claim to be a "community" project. If it actually were such, in the sense of having been started with community-wide discussion and approval, it would have been set up under postgresql.org. Well, color me confused. I don't pretend to understand what all this is about, and as just a user of PG, I don't necessarily have the need to know. I'm just trying to give back to the community in whatever small way I can, and I thought community documentation would offer me the opportunity to do that. The only thing I think I have a right to ask is that whatever contributions I may make not be a waste of effort because the PG decision-makers have decided that a certain repository is now "official", and the previous one is defunct. So I'd ask those decision-makers to come up with a single consistent story for us run-of-the-mill community members. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] does postgresql works on distributed systems?
Roberts, Jon wrote: He's talking about having the raw database files on a file server (eg SMB share). DB's like firebird and sqlite can handle this way of accessing the data using the embedded engines. Active-active, multiple server databases are either a shared nothing or a shared disk system. Oracle, for instance is a shared disk system where multiple database instances can connect to the same underlying disk. I'm not sure the point you are making. We have all our Oracle databases stored on a NetApp, so I think this is the kind of configuration you are discussing. However, each Oracle instance on a single server completely owns the files on the NetApp related to that instance. All Oracle instances on all servers share the same NetApp, but that's because it's just a big file server. In the event of a DB server failure, we can bring up the same instance on a backup DB server, but then *it* completely owns all files related to that instance. Only one instance can be accessing the files related to that instance at any point in time. The same could be done with PostgreSQL. As I said, the NetApp is just a fileserver. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please remove me from the list!
Marcelo Giovane wrote: Please, remove me from the list! Marcelo Giovane You may remove yourself. At the bottom of *every* message sent from the mail list server are these instructions: Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does derby have an embedded Mode like Derby ?
Paul Taylor wrote: I am testing the code that extracts information from a read only database. These are UNIT tests so only interested in getting the right results given a particular set of data, anything else is a distraction. I don't understand your test environment. If all your code is doing is extracting info from a database, why would you want to have database management embedded in your test? Simply have a test database already configured and running, then run your JUnit tests against that existing database. That simple approach would obviously work across all platforms. The only reason I can see for doing DDL in a unit test would be if the end product will be doing such DDL. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accessing a database form another database
Bill Thoen wrote: I've got some GIS tables that I want to use across several projects and currently I've got each project in a separate database. So say I've got two databases, one for the Spacely Sprookets project and one for the Cogswell Cogs contract, but I want to access my WorldMap tables in my GIS database to make maps for both of these projects. How do I reference a table that's in another database? Or should I organize my PostgreSQL data differently? You can use dblink for this purpose: http://www.postgresonline.com/journal/index.php?/archives/44-Using-DbLink-to-access-other-PostgreSQL-Databases-and-Servers.html -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How useful is the money datatype?
Rich Shepard wrote: On Sun, 4 Oct 2009, Sam Mason wrote: Withing PG procedures at least in pgsql it is impossible to do 'money' calculations without a loss of precision. The point is that on *any* computer it's impossible to perform arbitrary calculations to infinite precision (i.e. "without a loss of precision as you put it). Monetary values have always been an issue with computers. For a while, at least in the mainframe world of decades ago, binary-coded decimals (BCD) were a working approach. Yes, packed decimal is a standard way to handle money with no loss of precision. And "for a while" would be over 50 years, as packed decimal is still in use today. All banks rely on it. The best way to avoid loss of precision with decimal is to use decimal representation, and not convert to binary at all. There is no reason why PG could not support packed decimal. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How useful is the money datatype?
Christophe Pettus wrote: On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote: There is no reason why PG could not support packed decimal. Is that not NUMERIC? No, that is not NUMERIC. All numeric types are stored as binary representations. Packed decimal is not. Perhaps an example would clarify. The number 1234 would be represented as follows: binary: 10011010010 packed decimal: 12 34 Packed decimal needs to be able to represent 10 distinct characters, 0-9, so it uses half a byte for each. So a 4 digit number can be represented in 2 bytes (for simplicity, I'm ignoring sign. That takes a half byte.) The IBM implementation provides a corresponding arithmetic library to use packed decimal. These numbers are never converted to binary, so there is no loss in precision. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How useful is the money datatype?
Bruce Momjian wrote: Guy Rouillier wrote: Christophe Pettus wrote: On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote: There is no reason why PG could not support packed decimal. Is that not NUMERIC? No, that is not NUMERIC. All numeric types are stored as binary representations. Packed decimal is not. Perhaps an example would clarify. The number 1234 would be represented as follows: I think you are wrong. The Postgres documentation say: You are correct, I am wrong, as private emails also pointed out. I should read more carefully. This list is rapidly self-correcting ;). Thanks. The IBM implementation provided language libraries (usually COBOL) that also supported packed decimal, so precision was maintained throughout the entire application stack. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Free Tool to design Postgres Databases
Andre Lopes wrote: Hi, I'am searching for a free tool to design a Postgre Database. There is something similar to MySQL Workbench? Best Regards, Andre. From 9/11/2009 "Open source database design tool , alternative to MicroOLDAP" Open ModelSphere: http://www.modelsphere.org/ From 5/24/2008 "best er modeling tool for postgreSQL": /** GNU-GPL **/ OpenSystemArchitect http://www.codebydesign.com/ Power*Architect http://code.google.com/p/power-architect/ Druid http://druid.sourceforge.net/ ERW http://erw.dsi.unimi.it/ Dia http://live.gnome.org/Dia XML to DDL (python scripts) http://xml2ddl.berlios.de/ Graphiz - I believe I've read of people using it to reverse engineer from DDL http://graphviz.org/ /** Multiple versions/licenses (each has a "free" and a "not-free" version) **/ DBVisualizer http://www.minq.se/products/dbvis/ Toad http://www.toadsoft.com/ /** Not-free **/ Microsoft Visio - (If you already have it, it does work quite well) DataStudio - 30 day trial http://www.aquafold.com DBWrench - 30 day trial http://www.dbwrench.com/ From 3/13/2008 "ER diagram tool" http://druid.sf.net/ http://schemaspy.sourceforge.net/ http://uml.sourceforge.net/index.php And on it goes. Keep searching and you'll find more. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] oids on disk not in pg_class
We have a fairly large (1 TB) database we put on all SSDs because of a very high insert and update rate (). As our business has grown, we've been running into space constraints, so we went looking for files we might be able to delete. We found a large number (662 out of 1465 total ) and size (219 GB) of files in the data directory whose name does not correspond to an oid in the pg_class system catalog table. That amount of space would address our current space constraint problems. Some of these tables are recent (from today), while others are quite old (large volume in August and May, with some smaller ones as far back as February. What kinds of data are written to disk without being registered in pg_class? How can we determine which (if any) are safe to delete? Thanks. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] oids on disk not in pg_class
On 10/7/2013 5:58 PM, Steve Atkins wrote: On Oct 7, 2013, at 2:48 PM, Guy Rouillier wrote: We have a fairly large (1 TB) database we put on all SSDs because of a very high insert and update rate (38 million rows/day). As our business has grown, we've been running into space constraints, so we went looking for files we might be able to delete. We found a large number (662 out of 1465 total ) and size (219 GB) of files in the data directory whose name does not correspond to an oid in the pg_class system catalog table. That amount of space would address our current space constraint problems. Some of these tables are recent (from today), while others are quite old (large volume in August and May, with some smaller ones as far back as February. You need to be looking at pg_class.relfilenode, not pg_class.oid. They're often the same value, but often not. Steve, thanks for the quick reply. I reran the calculations using relfilenode instead of oid; they are now showing 214 unrecorded filenodes consuming 163 GB. The older tables (on or before May) are no longer showing as unrecorded. Of the filenodes that are still not matching pg_class.relfilenode, I see just one that is consuming the majority of the space: 614804 (153 files, 163 GB). Failed to mention our runtime platform: we are running Enterprise DB 9.2.1.3 on 64-bit Linux (Oracle Linux Server release 6.3). EDB pointed us to this note in the PG documentation (http://www.postgresql.org/docs/9.2/static/storage-file-layout.html): Note that while a table's filenode often matches its OID, this is not necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER and some forms of ALTER TABLE, can change the filenode while preserving the OID. Avoid assuming that filenode and table OID are the same. Also, for certain system catalogs including pg_class itself, pg_class.relfilenode contains zero. The actual filenode number of these catalogs is stored in a lower-level data structure, and can be obtained using the pg_relation_filenode() function. So, I ran "select pg_relation_filenode(614804)" and got no results. Any suggestions on how I can uncover the identify of this node? Thanks much. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] oids on disk not in pg_class
On 10/7/2013 6:46 PM, David Kerr wrote: On Mon, Oct 07, 2013 at 06:32:57PM -0400, Guy Rouillier wrote: - So, I ran "select pg_relation_filenode(614804)" and got no results. Any - suggestions on how I can uncover the identify of this node? - - Thanks much. You could try oid2name: http://www.postgresql.org/docs/current/static/oid2name.html David, thanks for the suggestion. I ran oid2name on all 3 databases in this PG instance (including postgres) and filenode 614804 is not reported in any of them. Judging by the size of the files and the number of them, I'm pretty sure this is one of our monthly stats tables. We record usage stats, keeping a separate table for each month; we keep just 3 months online because of the size. I ran cat against the first file in the series (by date), and the data I could see seems to confirm that it is a stats table. Somehow, it has gotten orphaned. Does the physical file have any useful information (in this scenario) in the first N bytes, and if so, is there a utility that can print out that header info? These huge stats files (about 260 GB per month, including index) have a single primary key. I'm guessing that the index files got orphaned at the same time the data files did; obviously, I'd like to remove those as well if I can identify which they are. Thanks. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Forms for entering data into postgresql
On 10/12/2013 6:15 PM, Chuck Davis wrote: For those of us who grew up on real computers the only appropriate way to get from one input field to the next is hitting the enter key. You Well, I grew up with a real computer. There was no enter key on the 029 key punch; that only came along later for those spoiled kids who wanted to use a C-R-T. But using keyboards and CRTs would hardly constitute real computer use. Punch cards. know what that does in a browser...makes efficient data input impossible. NOBODY should have to hit the tab key to move the cursor to the next field. Using the mouse is insulting enough to move from one That's a curious contention. The earliest 3270 had both dedicated tab and back tab keys. drop down to the next (which can also be done by hitting the enter key in a real application). If you are a hunt-and-peck typist, of course, it probably doesn't make any difference. Stuff gets to the database by being input by somebody. For Accounts Payable (AP) that is usually a clerk who enters orders/invoices all day. There are many input fields involved for item, rate, units, etc., etc. including sometimes lengthy descriptions. That's how stuff gets into the database and doing that in a browser is extremely tedious and VERY inefficient. That's very one-dimensional thinking. A browser-based app can do anything that a desktop app can do, especially with Ajax eliminating round trip requirements. If you want short hand command-line data entry like the Sabre system, that can be provided in a browser app. But as others have pointed out, browser apps have traditionally been targeted at broader audiences. For people who are only checking inventory, checking invoice status, order status, credit status, etc. a browswer interface is superb. Why bother writing a real application for something that trivial? The point is, use the right tool for the task. It's not always a browser and those who think so are showing their ignorance of a huge and varied technology world. On that we agree. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to determine max int
On 11/11/2013 7:14 PM, Melvin Call wrote: Good evening, I could swear I read somewhere that the default integer size on 64-bit systems running 9.1 is eight bytes, or the equivalent of a bigint. But reading through the user guide it seems that it is indeed still just four-bytes. Can anyone verify that for me, or point me to a way to verify it? I see nothing in pg_settings which is where I thought to look. Not a big deal, I just need to know for the data dictionary. The documentation seems pretty clear on integer data types: http://www.enterprisedb.com/docs/en/9.2/pg/datatype-numeric.html#DATATYPE-INT From the documentation, the range of integer data types is not platform-specific, other than for warning about BIGINT. Perhaps you are remembering compiler documentation, where the default integer size typically *is* platform-dependent? -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSD Drives
We used 4x OCZ Deneva 2 in a RAID configuration. Worked well for us for over 2 years with no hardware issues. We switched to SSD because we had a very write-intensive application (30 million rows/day) that spinning disks just couldn't keep up with. On 4/2/2014 6:09 PM, Shaun Thomas wrote: On 04/02/2014 04:55 PM, Bret Stern wrote: Care to share the SSD hardware you're using? We use these: http://www.fusionio.com/products/iodrive2/ The older versions of these cards can read faster than a RAID-10 of 80x15k RPM SAS drives, based on our tests from a couple yeas ago. Writes aren't *quite* as fast, but still much better than even a large RAID array. They ain't cheap, though. You can expect to pay around $15k USD per TB, I believe. There are other similar products from other vendors which may have different cost/performance ratios, but I can only vouch for stuff I've personally tested. Our adventure with these cards was a presentation at Postgres Open in 2011. Slides are here: https://wiki.postgresql.org/images/c/c5/Nvram_fun_profit.pdf -- Guy Rouillier --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] which function should i invoke to create a table and insert tuples?
On 5/17/2010 10:31 AM, sunpeng wrote: hi,when i do experiment on postgresql 8.4,i need to create a table and insert some tuples,which function should i invoke? for example,i want to create a table with "create table test (uid int,catcode int)" and insert tuples with "insert into test values(1,1)". thanks millions! What do you mean by function? Are you trying to do this from PgAdmin, from a stored proc or from some flavor of source code? If either of the latter two, which language? -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need Some Recent Information on the Differences between Postgres and MySql
On 6/25/2010 4:22 AM, John Gage wrote:
There are features, are there not, that Postgres has that MySQL does not
have?
Yes, a big one would be data integrity. Most people would not consider
data integrity an optional feature in a DBMS, but apparently MySQL does.
Try this in MySQL:
create table t1 (f1 varchar(10))
insert into t1 values('this is a long string')
select * from t1
When I do this on a version 5.0.44 MySQL DB (the latest I have
conveniently available), MySQL processes the insert without complaint,
silently truncating the string to "this is a". Why people aren't
bothered by this is beyond me.
--
Guy Rouillier
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL Query Help Please !
On 7/6/2010 3:06 AM, GrGsM wrote:
Hi all
I am using the following query for data to be displayed in crosstab :
SELECT closedate,status,
SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
SUM (CASE WHEN empcode = 'NT-050' THEN 1 ELSE 0 END) AS NT050,
SUM (CASE WHEN empcode = 'NT-062' THEN 1 ELSE 0 END) AS NT062
FROM dbcleaning
WHERE status = 'Fixed'
AND empcode IN
('NT-028',
'NT-031',
'NT-050',
'NT-062')
GROUP BY closedate, status
Now i need a column in the same result of the query which shows the
difference between the two columns .
SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) -
SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) as NT028_NT031
Or if you prefer, define an outer select with your select in the FROM
clause; in the outer select, you can select each of the 4 columns plus
any difference you like.
--
Guy Rouillier
--
Guy Rouillier
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] IBATIS support for postgres cursors
On 8/23/2010 12:11 PM, atul.g...@globaldatapoint.com wrote: Hi, Can cursors be returned to IBATIS. If so how. This is what I have written. This does not work. Please help. There is a dedicated mailing list for MyBatis (renamed from iBATIS) over at http://www.mybatis.org. This question would probably be better addressed there. I use both PG and MyBatis so I saw your question. REF is not an acceptable MyBatis jdbcType. I'm trying with type OTHER as documented here: http://jdbc.postgresql.org/documentation/83/callproc.html#callproc-resultset-setof But I'm running into an issue which I'll pursue on the MyBatis mailing list. Note that with the current refcursor implementation, there is no advantage over returning SETOF from your function. Both materialize the entire result set before returning to the caller. So, if you can't get refcursor to work, I'd suggest switching to SETOF. I'll get back to you when I find out why OTHER is not working. Or else you can join the MyBatis mailing list. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] IBATIS support for postgres cursors
On 8/26/2010 5:27 AM, atul.g...@globaldatapoint.com wrote: Hi Guy, Thanks for your reply. We were able to work out with type OTHER. How did you get OTHER to work? Did you define your own TypeHandler? -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL Question - "Recursion"
ktr73 wrote: Hi - Was wondering if anyone could help / had some thoughts. I am building a model for a client, and right now doing customer attrition modeling. Basically, the number of customers in this period is equal to: Beg # Customers + customers added this period - attrition Ending # Customers Obviously getting the beginning number of customers and # of added has been easy via SQL. Also, the above is fairly straightforward in a spreadsheet program like Excel. But I can't seem to come to grips with how to model the attrition line item in SQL, as it's based on last months ending balance ... which in turn is based on beg + add - attr = end for the previous period, and so on. So it seems to be a recursive function as far as I can tell ... any idea how to model this via SQL? I can get running totals for the # of adds with a correlated subquery, but I can't seem to figure out how to build the attrition into that running total. Don't understand why you need recursion. Seems like attrition can be calculated. You say you can get the beginning number of customers. The ending number of customers for month N = beginning number of customers for month N+1. So, if you have the beginning, ending and # customers added, then attrition = ending - beginning - #added. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle and Postgresql
M2Y wrote: Why most enterprises prefer Oracle than Postgres even though it is free and has a decent enough user community. Databases are a critical part of many companies' business. I work for telecom company, and if we were to lose our databases, we'd be out of business, period. So, "free and decent enough" are not good enough. If you are going to bet your business on anything, you want to be as sure as possible that "it" is reliable and that you can expect quick action if "it" should break. I tried to introduce PG into our business 4 years ago. Managers listened to the benefits but went with Oracle. No one ever explained why, but I've worked in this industry for 30 years and have a pretty good idea. Oracle is a big company, and we have a 24/7 support contract with them. If something goes wrong any time of the day, we can call them and open a support issue. PG is a great piece of work and equal in performance and features to commercial DBMSs. You can get support contracts from several companies. But there is no single entity that "owns" PG. CTOs/CIOs like to sleep at night. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
Garry Saddington wrote: It does work but returns a column called case. How can I return the case column as 'postcode'? select CASE WHEN postcode ilike '%OO%' THEN '' END as postcode from addresses -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Annoying Reply-To
Bill Moran wrote: You can resent it or not, but this _is_ a personal thing. It's personal because you are the only one complaining about it. Despite the large number of people on this list, I don't see anyone jumping in to defend you. I'm another in the crowd that had this same discussion when I joined years ago. I had the same point of view as Mikkel, but I've adapted to the community way of doing things. When I use "Reply All" in Thunderbird, it adds a "To:" to each of the individuals in the discussion, and a "CC:" to the list. Since I personally don't like receiving multiple copies of emails from this list, I delete all of the "To:" addressees and change the list from "CC:" to "To:". Would be nice if everyone did the same. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Session variables (how do I pass session data to a function)
Philip W. Dalrymple wrote: What I want to do is to add to this another field that will be NULL UNLESS the session sets a value to the "user" for that the middle-wear system is acting for. This can be done via a setting. See http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE If you'd like to read a fuller discussion of this very topic, search for a thread titled "can I define own variables" in the archives. I had to do the same thing back then. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Annoying Reply-To
Aidan Van Dyk wrote: But now, if the list munged my reply-to, how would you get back to me? I wouldn't ;). The whole point of a mailing list is to have discussions with the list. If I wanted to correspond with you directly, I wouldn't use the list for that. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Annoying Reply-To
Raymond O'Donnell wrote: On 23/10/2008 19:09, Angel Alvarez wrote: No one, ive seen, seems to be perfect nor thunderbird. By the way kmail has 4 options (reply, reply to all, reply to author, reply to list) in addition to be able to use list headers included in the message. Here's a "reply to list" add-on for ThunderBird - it's marked experimental, but may be worth a try: https://addons.mozilla.org/en-US/thunderbird/addon/4455 Works great! Thanks, Ray - no more complaints from me ;). Anyone using Thunderbird to read this list would benefit from this add-on. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
Andrus wrote: I have separate production server running "PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0, pie-8.7.9)" This is a bad idea. I run Gentoo at home on a workstation, and I like the approach it uses. But it is really targeted to the individual user who likes to be on the bleeding edge. *Everything* on Gentoo is compiled from source; that's the whole point of the distribution. This is not what you want in a production platform in a business environment. You should be looking at a distribution that has a support package (even if you elect not to use it), a minimum number of years that each version will be supported, and a predictable maintenance schedule. You have many to choose from: Redhat and its free derivatives like CentOS, Ubuntu with its supported releases, etc. To answer your question directly, you won't find a prepackaged solution to running simultaneous version of PG (or any other software package) on Gentoo. That's not how Gentoo is designed to be used. Having said that, I remember reading about slots, which may allow what you are trying to do. But I've never investigated. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrading side by side in Gentoo
Erik Jones wrote: P.S. To whomever said that Gentoo for for single users running cutting edge software, poppycock. That was me. Andrus said in a former post on this thread: >> I have ... no experiences on Linux. I stand by my assertion that his company should not be running Gentoo in a production environment. I have quite a bit of experience in Gentoo and other distros, and for Andrus's situation, I continue to recommend a binary distro with committed multi-year support. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] please cansel my subscription
Qiang, you can do this for yourself. At the bottom of every email sent to the list are instructions for modifying your subscription, including unsubscribing. I'll copy and paste them for you here: To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Qiang wrote: thank you! -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] db backup script in gentoo
Andrus wrote: There are no console users in this server other than root. /home directory is empty. console access is used only some times in year. Andrus, I don't know how this conversation ended up on a PG mailing list, but Gentoo maintains many user forums where people are happy to answer questions about that distro: http://www.gentoo.org -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] virtualidx exclusive lock
Uwe Schroeder wrote: On Sunday 08 November 2009 11:38:28 pm Uwe Schroeder wrote: (why it uses a transaction for a simple select is the other thing). Every database interaction happens within a transaction. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Something like Oracle Forms, but Open Source to use with PostgreSQL?
On 12/20/2009 4:13 PM, Andre Lopes wrote: Hi, I need to know if there is something like Oracle Forms in the Open Source world that works with PostgreSQL. If do you know something, please let me know. Some quick Googling found this if you are looking for a desktop solution: http://groups.fsf.org/wiki/Oracle_Forms or this if you are looking for a web solution: http://stackoverflow.com/questions/179849/best-solution-for-migration-from-oracle-forms-6i-to-the-web -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FM format modifier does not remove leading zero from year
On 1/5/2010 10:54 AM, Tom Lane wrote: Adrian Klaver writes: From what I could see in the source code (src/backend/utils/adt/formatting.c) the year portion of the string is not run through the FM modifier. A fix would mean a patch to the above AFAIK. Should it be? Can anyone check how this works on Oracle? Oracle states clearly in the SQL Reference manual: "A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier." I get the following results: select to_char(DATE'2009-1-1','FMDD.FMMM.FMYY') from dual; 1.01.9 select to_char(DATE'2009-1-1','FM DD.MM.YY') from dual 1.1.9 -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FM format modifier does not remove leading zero from year
On 1/6/2010 3:29 PM, Tom Lane wrote: Guy Rouillier writes: Oracle states clearly in the SQL Reference manual: "A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier." *Toggles* the effect of the modifier? Egad, what drunken idiot chose that specification? Eh, tomato, tomahto. If you assume that someone will strip leading zeroes consistently, the Oracle approach makes sense. That would be a reasonable assumption to make; why would I strip the zero off the month but leave it on the day? So, in the unusual case that you want to do such a thing, you are asked to use a second occurrence of FM to turn zero suppression back off. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ubuntu for servers (was TurnKey PostgreSQL)
Liraz Siri wrote: Solaris is awesome (dtrace rocks!), but I still prefer Debian/Linux for the same reasons I prefer PostgreSQL over MySQL - its lack of dependence on any single company. OpenSolaris? -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ubuntu for servers (was TurnKey PostgreSQL)
Joshua D. Drake wrote: BSD is dying. We all are, sooner or later ;) -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why would I want to use connection pooling middleware?
Kirk Strauser wrote: I understand why pooling within a process itself is a good thing. However, say I have two users running the same program on different desktop machines. At present, those applications connect with the same username/password that's tied to the program and not the actual user. It seems like if Abby and Barb end up sharing the same connection from the pool, and Abby runs some giant report query, then Barb would get held back while she waits for it to finish. Is that true? Even if not, what would be the advantage in the two of them sharing a connection? Connections are pooled on the client end, not on the server end. So, you'd be able to pool connections on your web server, and should, for reasons documented by others. However, since Abby and Barb are using different computers, you won't achieve anything by introducing pooling into your desktop application. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Gregory Stark wrote: I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at FOSDEM 2009 this year. I have a pretty good idea what some them are of course, but I would be interested to hear if people have any complaints from personal experience. What would be most interesting is if you can explain an example of when the problem caused real inconvenience to you, since sometimes it's hard to see from a theoretical description where the real harm lies. So, what do people say? Is Postgres perfect in your world or does it do some things which rub you the wrong way? Feel free to respond on-list or if you prefer in personal emails. I do intend to use the ideas you give in my presentation so mark anything you wouldn't be happy to see in a slide at a conference some day. Back in March 2005, I started an email thread titled "Debugging deadlocks". Most of the experienced PGers participated in that thread. The basic issue at that time was that inserting a row into a table with a foreign key placed an exclusive row-level lock (SELECT FOR UPDATE) on the reference table (the table to which the foreign key refers). If you happen to do inserts on two different tables, each with a foreign key to the same reference table, deadlocks are pretty easy to create. This is especially true if the reference table has low cardinality, which is often the case. I don't know if this situation has been improved since that time. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Gregory Stark wrote: Christopher Browne writes: - Managing jobs (e.g. - "pgcron") A number of people have mentioned a job scheduler. I think a job scheduler entirely inside Postgres would be a terrible idea. PgFoundry already has a project called "Job Scheduler". -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Greg Stark wrote: My only point was that this would be very different from Oracle-style job scheduler implemented *inside* the database using database-specific code and requiring database-specific code to interact with the outside world. That's just reimplementing the whole world using the database as a weird operating system which is someone else's game. And someone else might want to play that game inside PG ;). Seriously, we already have programs running inside the DB (stored procs), so why not jobs? I can think of several useful applications. I have an application with a high volume of inserts (60M + per day). Maybe I can conceive of some way to reorganize the previous day's data at 2 am each morning that will provide much better performance. Since all that activity is inside the database, why not schedule it inside the DB also? It's the same logic to justify stored procs. Sure, I can accomplish the same thing via cron and external scripts. But that's less secure, since I need to store my connection params in the script. And if I've got 5 different servers running cron jobs, then my schedule is distributed over those 5 boxes, which becomes a management issue. As has been pointed out here, the schedule could be kept in the DB, which would address that. Having a scheduler in the DB to run those jobs is just the next step. Different stokes, as they say. All about choice. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Craig Ringer wrote: An internal job scheduler with the ability to fire jobs on certain events as well as on a fixed schedule could be particularly handy in conjunction with true stored procedures that could explicitly manage transactions. Craig, what kind of "events" are you thinking about? Triggers are already pieces of code that run upon "certain events", namely insert, update or delete events. What others do you have in mind? -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
Karsten Hilbert wrote: Craig, what kind of "events" are you thinking about? Triggers are already pieces of code that run upon "certain events", namely insert, update or delete events. What others do you have in mind? That's a good point, actually. I can't think of much you can't do with a trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. I would find ON CONNECT/DISCONNECT triggers very useful. Probably this is more similar to database-wide assertions. But a job scheduler would not help with that. Perhaps you intended your comment to fall into the "pet peeves" bucket rather than the "job scheduler" bucket. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql
André Silva wrote: Hi I've installed the postgresql in my computer but i've forgotten my password. i've tried everything, i've been in your page that requires a e-mail to send information to reset the password but i never get the e-mail so far.(http://www.postgresql.org/community/lostpwd) i've tried to make a new username but the ones that i've tried already exist. What can i do? format my pc? tell me something because i really need this Well, you certainly don't need to reformat your PC. You didn't provide much information that would enable people to help you, such as the version of PostgreSQL, what if any customization you have already done, if you've put things in your database you need access to, etc. In the absence of all that, probably the quickest way for you to get access to a database would be to simply create a new one. A more descriptive subject to your emails would also encourage people to respond. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Enumerating a row set
On Thu, Mar 26, 2009 at 06:42:45PM -0400, George Sakkis wrote: Hi all, Is there a function similiar to Python's enumerate() [1] ? Searching the docs didn't reveal any relevant builtin but I hope it's doable in pgsql. I found this via Google: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/ -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PQsetdb
Mohsen Pahlevanzadeh wrote: > My program is using both MySQL & PostgreSQL.I don't have problem with > Mysql. But i have problem with pgsql. I think Richard's point is that you are not linking in any PostgreSQL libs. >> Mohsen Pahlevanzadeh wrote: >>> I recieve following error: >>> [EMAIL PROTECTED] sql2sql]# make >>> g++ -c -I/usr/include/mysql -I/usr/include sql2sql.cpp; >> ^^ >>> g++ -L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib >>> g++ -o >> ^ ^^^ >> Those aren't going to help, are they? >> >> -- >>Richard Huxton >>Archonet Ltd >> -- Guy Rouillier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] library files
Mohsen Pahlevanzadeh wrote: > Dears,Now i can't link any library to my executable file,Please guide > me that i can use mysql along with pgsql.(mysql needs to add library > to executable file) Yours,Mohsen Have you read the section of the documentation dealing with building C programs to talk with PostgreSQL? See section 27.15. Building libpq Programs. Seems pretty clearly laid out there. -- Guy Rouillier ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] basic trigger using OLD not working?
Title: Message The RAISE statement requires a % marker for each variable you want to include in the output text. --Guy Rouillier -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rick CaseySent: Thursday, February 24, 2005 5:51 PMTo: pgsql-general@postgresql.orgSubject: Re: [GENERAL] basic trigger using OLD not working?Thanks much! That met with partial success; but getting closer.The error message about OLD went away (thankfully!), but there is still no data from the OLD variable. Here is the code again:create trigger PEDIGREES_hist_del_trigAFTER DELETEon PEDIGREESFOR EACH ROWEXECUTE PROCEDURE logPedigreesDel();CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS 'begin RAISE EXCEPTION ''OLD.famindid = '', OLD.famindid; return OLD;end;' LANGUAGE plpgsql;Which when it fires results in:cgb_lab_data=# \i testphdtrig.sqlpsql:testphdtrig.sql:1: ERROR: OLD.famindid =Regards,rickTom Lane wrote: Rick Casey <[EMAIL PROTECTED]> writes: Here is the code that creates the delete trigger: create trigger PEDIGREES_hist_del_trig AFTER DELETE on PEDIGREES EXECUTE PROCEDURE logPedigreesDel(); I think you forgot FOR EACH ROW. By default, the above creates a STATEMENT trigger, in which you don't have access to individual rows. regards, tom lane
Re: [GENERAL] exporting table for load into oracle
miguel angel rojas aquino wrote: > hi everybody > > first, it is not that i'm migrating from postgresql to oracle, it's > just that we have a payroll system (we are a software development > company) that currently runs on postgresql, but the goal is that it > runs on other databases too (for our internal needs it runs on > postgresql, and two of our clients run in postgresql too), so now i > need to test it on oracle > > i've done a dump of our data, but we are having troubles with the date > fields, as pg_dump just dumps dates as '-mm-dd', but when loading > into oracle, it just can't handle dates this way, it needs an explicit > mask (to_date) when importing the sql dump > > so the question is, there is a way to instruct pg_dump to dump dates > with the to_date function included? Not a direct answer, but how's about using CSV files instead of dumps? You can instruct SQL Loader about the format of incoming dates via the control file (ctl.) BTW, I just did a migration in the other direction using CSV files. I must say I never cease to be impressed by the robustness of PostgreSQL. Oracle exported dates like this: "9/14/2004 6:40:21 PM". PG took 'em right in with no special instructions, and produced the correct timestamp. Hats off to the PG developers! Thanks for all your work. -- Guy Rouillier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
