Re: [GENERAL] Alter view with psql command line
On Fri, Feb 10, 2017 at 02:26:18PM -0300, Leonardo M. Ramé wrote: > El 10/02/17 a las 14:17, Adrian Klaver escribió: > > On 02/10/2017 09:09 AM, Leonardo M. Ramé wrote: > > > Hi, is there a way to alter a view using *psql*?, something like what > > > \ef does for functions. > > > > In 9.6: > > > > That's why in 9.1 I didn't find that command... You can use the 9.6 client without problems on 9.1, well, apart from the fact that 9.1 is already past its end of life. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pg Tcl - is it dying out?
On Wed, Jan 30, 2013 at 05:45:06PM -0500, Carlo Stonebanks wrote: As our production system are being upgraded to Windows 7 64-bit we are finding that our Tcl apps which use the PgTcl client libraries are now failing (couldn't load library libpgtcl.dll: invalid argument). We have tried downloading the latest binaries (which are 32 bit) but the problems persist. There is little activity on the sourceforge page for the lib. This is pretty strictly a problem for the PgTcl project and not for the PostgreSQL project or any other associated projects. Was there something constructive you might have been asking about that you forgot to include in this email? If so, what was it? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: MODERATOR WARNING Re: [GENERAL] Exception Handling in C-Language Functions?
OK :)
Is there a way to do this automatically?
Cheers,
David.
On Mon, Dec 03, 2012 at 03:14:54AM -0300, Alvaro Herrera wrote:
MODERATOR WARNING
I noticed that this guy Rahul seems to be reinjecting old list emails
somehow. Please don't approve anything coming from him. Observe this
example:
http://postgresql.1045698.n5.nabble.com/Re-GENERAL-MS-Access-and-Stored-procedures-td1843848.html
http://postgresql.1045698.n5.nabble.com/Re-GENERAL-MS-Access-and-Stored-procedures-td5734652.html
The original message was posted in 2005!
The message here is this one (also in 2005):
http://postgresql.1045698.n5.nabble.com/GENERAL-Exception-Handling-in-C-Language-Functions-td1843896.html
I have no idea what's going on. Maybe it's something to do with Nabble.
There are others pending moderation in pgsql-admin and pgsql-hackers too.
rahul143 wrote:
I have the created a C-Language function (code is below). Now, I
wonder: How do I handle exceptions, for example if malloc cannot assign
the necessary memory? Do palloc and pfree handle such a case
cleanly? Should I simply use an assert?
#include postgres.h
#include string.h
#include stdlib.h
#include fmgr.h
#include libinn.h
PG_FUNCTION_INFO_V1(ffiinews_uwildmat);
/* Wrapper for INN's function uwildmat. Needs parameters in UTF-8. */
Datum ffiinews_uwildmat(PG_FUNCTION_ARGS) {
VarChar *text = PG_GETARG_VARCHAR_P(0);
VarChar *pattern = PG_GETARG_VARCHAR_P(1);
int text_len = VARSIZE(text)-VARHDRSZ;
int pattern_len = VARSIZE(pattern)-VARHDRSZ;
char *tmp_text = (char *)malloc(text_len+1);
if (tmp_text == NULL)
; /* What now? */
char *tmp_pattern = (char *)malloc(pattern_len+1);
if (tmp_pattern == NULL)
; /* What now? */
strncpy(tmp_text, VARDATA(text), text_len);
tmp_text[text_len] = '\0';
strncpy(tmp_pattern, VARDATA(pattern), pattern_len);
tmp_pattern[pattern_len] = '\0';
bool matches = uwildmat(tmp_text, tmp_pattern);
-
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/GENERAL-Exception-Handling-in-C-Language-Functions-tp5734656.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Moving from Java 1.5 to Java 1.6
On Thu, Oct 04, 2012 at 12:22:55PM +0530, Swayam Prakash Vemuri wrote: Hi We have an application which uses postgresql 7.4.5. You have a very large problem. The 7.4 series went out of support two years ago at 7.4.30, which means that you have known data corruption and crash bugs, and would even if you were to upgrade to 7.4.30 immediately. You need to upgrade to a supported version and put systems in place to do upgrades of every component in the system on a regular basis, as they all have finite lifetimes. Now when we moved to Java 1.6, we are seeing lots of jdbc driver related compilation issues like shown at end of this email. Those appear to be Java issues pretty strictly. Question is can we just only upgrade jdbc driver alone or its better to move completely to a new postgres version. ? Are there any docs that explain about how to migrate ? Use 9.2.1's pg_dump to get your data out of the running 7.4 database and then restore it to the 9.2.1 database. For each database on the 7.4 machine, run the following on the 9.2 machine: pg_dump -h name.of.7.4.machine.com -U postgres -Fc --file=mydb.dump mydb To restore on the 9.2 machine: pg_restore -C mydb.dump Hope this helps. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Test, please ignore.
$subject! -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Chaining inserts ... This would be cool
On Tue, Apr 24, 2012 at 08:12:10AM +1000, Chris Angelico wrote: On Tue, Apr 24, 2012 at 6:49 AM, Nick Apperson apper...@gmail.com wrote: There are obviously workarounds for this, but I'm wondering why the following query shouldn't work. It seems like it should. With MVCC already present on the back-end, I can't see any reason other than additional parsing routines that this couldn't work: INSERT INTO old_login_id_to_new_account_id(new_account_id, old_login_id) INSERT INTO accounts(id, username, password_hash, email) SELECT DEFAULT, username, password_hash, email FROM logins_old RETURNING id, logins_old.id; That's possible using WITH. I made a statement that creates an invoice and its lines (with the lines all having a foreign-key reference to the owning invoice) more or less the same way: WITH inv AS (insert into ... returning id), constants AS (values (...),(...),(...)) INSERT INTO invoicelines (columnlist) SELECT inv.id,constants.* FROM inv,constants Something like that. I do remember running into trouble with the multi-row insert (can't use multiple rows of literals with SELECT, and can't fetch data from a WITH expression with VALUES), so it had to go to the extra level of structure. If you're inserting just one row into each, this should be easy. Of course, the question I never asked (never bothered to, really) was: Is it really any better than simply doing the first insert and retrieving the ID in my application? :) One crucial difference is the number of round trips to the database. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] exclusive OR possible within a where clause?
On Thu, Oct 13, 2011 at 07:49:59PM -0400, Tom Lane wrote: David Salisbury salisb...@globe.gov writes: Short version, is there a way to implement an exclusive OR in a where clause? The boolean operator will do the trick. (x = y) (a = b) regards, tom lane Factoring in NULLable columns, that's: (x IS NOT DISTINCT FROM y) (a IS NOT DISTINCT FROM b) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] live metadata changes v8.3.4
On Tue, Sep 27, 2011 at 01:51:42PM -0700, Gauthier, Dave wrote: Hi: How does one make a metadata change to a DB that's actively being used. Specifically, I want to drop a view, drop some columns from a table that's used in the view, recreate the view without those columns. In the past, I've resorted to connecting as a super user, running select procpid from pg_stat_activity... then pg_ctl kill ABRT procpid. This would create a window where I could get in and make the change. But it also created some angry users whos processes got killed. You have the choice between taking those users offline and not doing the change. V8.3.4 on linux. Upgrade to 8.3.16 immediately, if not sooner. Oh, and start planning the 9.1 migration, too. December of 2012 is closer than you think. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [Solved] Generic logging system for pre-hstore using plperl triggers
On Tue, Sep 27, 2011 at 04:52:08PM -0300, Diego Augusto Molina wrote: 2011/9/27, Diego Augusto Molina diegoaugustomol...@gmail.com: Honestly, I don't remember why I used triggers instead of rules in the audit and audet tables. I remember now, that's because in my case, operations over tuples are done very lightly (one or two in the same sentence at a time). So, for a case as such, rules end up beeing more expensive than triggers (right?). There's an even better reason not to use rules: they're going away in a not too distant version of PostgreSQL. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [pgadmin-support] Help for Migration
On Tue, Sep 06, 2011 at 12:17:28PM +, mamatha_kagathi_c...@dell.com wrote: Hi, I am trying to migrate a very small MS SQL Server Database (with 200 records max, 20 tables, 10 stored procedures) to PostgreSQL. I tried browsing through internet to find technical steps but I found some blogs with vague discussion for same. Kindly let me know where to look for the information or even better if I can get some document from the community for the same. The table structures shouldn't be a problem to do fairly mechanically, and at worst you can simply hand-type in the data. The stored procedures will be a problem at two levels: 1. You'll have to translate them into a language PostgreSQL can use from (I'm guessing here, but it's usually a good guess in these situations) T-SQL. 2. PostgreSQL functions, which are similar in many ways to stored procedures, have a fundamental difference: they can't control transactions. Any stored procedures that have a COMMIT or ROLLBACK in them will have to be re-architected in a fundamental way. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 needed with PostgreSQL clustering/switching from MySQL
On Tue, Jun 21, 2011 at 05:07:10AM +, Vikram Vaswani wrote: Hello I'm new to PostgreSQL, coming at it from a MySQL background. I'm currently looking at switching one of our applications (which currently uses MySQL) over to PostgreSQL and had some questions. We're considering the switch because of issues we have faced when using MySQL in a clustered scenario and we're hoping that switching to PostgreSQL will help us resolve these issues. Our three biggest pain points with MySQL are: 1. MySQL's NDB engine (used for clustering) cannot index textual data stored in a BLOB field PostgreSQL's full text capability is quite good. There are better specialty (non-relational) engines out there, and some proprietary engines that do more (or at least different) things, so you'll need to assess carefully what type of text searching you want to do, and what you'll trade that capability for. 2. When configuring a MySQL cluster, there is a memory limit on the number of objects (tables and fields). We often have problems when importing new tables, wherein we need to increase the memory limit for the server's NDB engine before it allows us to import. This can be a problem for dynamically-generated tables, as we cannot accurately forecast the number of database objects in advance in these cases. Dynamically generated tables are generally a problem at the design level. Neither PostgreSQL nor any other engine will solve that. 3. MySQL's NDB engine doesn't support or enforce foreign keys. This, PostgreSQL does extremely well. So my first question is, I'd like to know if PostgreSQL has similar issues when running in a clustered scenario. I'm not entirely sure what you mean by a clustered scenario, but I'd like to digress into the matter of multi-master replication. It can be fast, so long as it doesn't have to be correct, or it can be correct, so long as your users are willing to wait, but it can't be both fast and correct at once. In 90%+ of cases, it's neither fast nor correct. I guess what I'm saying here is that you should not design systems that depend on unicorn steak, skyhooks, magic pixie dust, or fast, accurate multi-master replication. Second, on reviewing the manual and some sites, it seems that there are a number of different OSS solutions for implementing failover and clustering with PostgreSQL, but no official version. As of 9.0, there is built-in asynchronous replication, which can be streamed (lower lag times) if you like. You might also want to consider some of the other solutions. http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling Is this understanding correct? If yes, which solution is best suited for running PostgreSQL in a private cloud, with clustering/failover support? Please to understand that you need to set priorities for these things and decide which you might sacrifice in order to get the others. Thank you, Vikram THIS EMAIL ANY ATTACHED FILES ARE PRIVATE CONFIDENTIAL If you are not the addressee, any disclosure, reproduction, copying, distribution, or any other dissemination or use of this communication is strictly prohibited. If you have received this transmission in error please notify the sender immediately and then delete this email. Email transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of email transmission. If verification is required please request a hard copy version. In future, please to elide these disclaimers. The serve no legal or practical purpose, but they do give people they annoy a convenient excuse not to reply. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] unnest with generate_subscripts and same array
On Thu, May 26, 2011 at 05:17:06PM -0700, Carlos Fuentes wrote: Hello, Given that these are the only one array_col in play, is select unnest(array_col), generate_subscripts(array_col) from table_with_array_col ; guaranteed to gave the subscripts match the array element? In all the testing I've done it's worked, but I don't know if I was just lucky :) That would be more of the SQL standard UNNEST, with the WITH ORDINALITY clause. We don't have it yet :/ Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [HACKERS] PostgreSQL Core Team
Kudos! Cheers, David. On Wed, Apr 27, 2011 at 07:48:48PM +0100, Dave Page wrote: I'm pleased to announce that effective immediately, Magnus Hagander will be joining the PostgreSQL Core Team. Magnus has been a contributor to PostgreSQL for over 12 years, and played a major part in the development and ongoing maintenance of the native Windows port, quickly becoming a committer to help with his efforts. He's one of the project's webmasters and sysadmins and also contributes to related projects such as pgAdmin. In his spare time, he serves as President of the Board of PostgreSQL Europe. Regards, Dave. -- Dave Page PostgreSQL Core Team http://www.postgresql.org/ -- Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 realize ROW_NUMBER() in 8.3?
On Wed, Apr 20, 2011 at 11:51:25AM -0400, Emi Lu wrote: Hello, ROW_NUMBER() is only ready in 8.4. For 8.3, is there a simple way to get row_number select row_number(), col1, col2... FROM tableName Thanks a lot! 丁叶 Your best bet is to upgrade to a modern version of PostgreSQL. While you will of course need to do tests with your applications, 9.0 has no significant backward-incompatibility with 8.3. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] postgres conferences missing videos?
On Mon, Mar 21, 2011 at 09:09:35PM +0100, Aljoša Mohorović wrote: On Mon, Mar 21, 2011 at 8:23 PM, Vick Khera vi...@khera.org wrote: Someone has to do lots of work to tape the talks, get proper permissions from the presenters, and then host the videos. Often this would land on the shoulders of the conference organizers, who are already working hard just to pull off the live show. never said that it's not so just that i'm surprised/disappointed that it's so low priority. Who proposes, volunteers! How are you going to help? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] postgres conferences missing videos?
On Mon, Mar 21, 2011 at 11:55:20PM +0100, Aljoša Mohorović wrote: On Mon, Mar 21, 2011 at 9:45 PM, David Fetter da...@fetter.org wrote: Who proposes, volunteers! How are you going to help? being on a different continent and unable to attend doesn't actually enable me to do something. Not so, by a long shot. If this is actually important to you, there are plenty of ways you could help, not least by funding the effort. Capable videographers are not cheap, and if you think we can get away with having amateurs, try watching a 45-minute talk recorded by an amateur, or better still, try coordinating the efforts of at least three people per talk, as you'll need massive redundancy. When professionals volunteer their time, they still have expenses like travel, storage media, etc., and you can help defray those. What you really, really need to stop doing is proposing that others do an enormous amount of work for your benefit without offering to help. You have two choices here that would be constructive: offer to help (much better) or keep quiet (at least not whiny). Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Table inheritance foreign key problem
On Wed, Dec 22, 2010 at 12:32:44AM -0500, Andy Chambers wrote: Hi, One of the caveats described in the documentation for table inheritance is that foreign key constraints cannot cover the case where you want to check that a value is found somewhere in a table or in that table's descendants. It says there is no good workaround for this. For some values of, good, there actually is. http://people.planetpostgresql.org/dfetter/index.php?/archives/51-Partitioning-Is-Such-Sweet-Sorrow.html http://people.planetpostgresql.org/dfetter/index.php?/archives/59-Partitioning-Glances.html Cheers, David (hoping PostgreSQL will be able to infer how to automate this some day). -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] What is the name pseudo column
On Wed, Dec 15, 2010 at 01:50:54PM -0600, Jack Christensen wrote: I was just surprised when accidentally selecting a non-existent name column there was no error -- instead something came back. select accounts.name from accounts limit 1 - (1,65522,1,0.00,,2010-07-22 09:57:26.281172-05,2) It appears it tries to return the entire row in an array (but longer rows get truncated). I've searched Google and the PG docs but I haven't had any luck. What happened here is that you ran into PostgreSQL's charming habit of using the argument.function notation, so you called the name function, i.e. the one that casts to name, on the entire row from your accounts table. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] What is the name pseudo column
On Wed, Dec 15, 2010 at 03:43:45PM -0800, Adrian Klaver wrote: On Wednesday 15 December 2010 1:27:19 pm David Fetter wrote: On Wed, Dec 15, 2010 at 01:50:54PM -0600, Jack Christensen wrote: I was just surprised when accidentally selecting a non-existent name column there was no error -- instead something came back. select accounts.name from accounts limit 1 - (1,65522,1,0.00,,2010-07-22 09:57:26.281172-05,2) It appears it tries to return the entire row in an array (but longer rows get truncated). I've searched Google and the PG docs but I haven't had any luck. What happened here is that you ran into PostgreSQL's charming habit of using the argument.function notation, so you called the name function, i.e. the one that casts to name, on the entire row from your accounts table. Cheers, David. In the for what is worth department that behavior is going away in 9.1. See here for a detailed explanation: http://www.depesz.com/index.php/2010/11/08/waiting-for-9-1-removed-autocast-footgun/#more-1908 I'd missed this bit of good news :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pgadmin for Fedora 14?
On Mon, Nov 29, 2010 at 04:17:44PM -0500, Jerry LeVan wrote: Hi, Is there a Fedora 14 rpm for pgadmin that works out of the box for pg 8 and pg 9? To get pgAdmin3, do the following as root: yum install pgadmin3 Just so as to abate some confusion, the major releases of PostgreSQL are 9.0, 8.4, 8.3, etc., not 9 and 8 :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] [pgsql-www] Forums at postgresql.com.au
On Mon, Nov 22, 2010 at 10:40:34AM +1100, Elliot Chance wrote: It does surprise me a bit that when I (or someone else) signs up to a mailing list (not postgres specifically) that there is no fine print or agreement that says something along the lines of Your email address will be plastered all over the internet, guaranteed to be picked up by spiders, make sure you have a good anti-spam. If you imagine that not signing up for a mailing list in any way alleviates this need, I have a bridge to sell you. It connects Manhattan with Brooklyn. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Alter table to on update cascade
On Wed, Nov 17, 2010 at 11:32:32AM -0500, Aram Fingal wrote: I have a table where I should have declared a foreign key with ON UPDATE CASCADE and didn't. Now I want to fix that. From the documentation on www.postgresql.org, about ALTER TABLE it's not at all clear how to do this or even whether you can do this. You can do it like this: BEGIN; ALTER TABLE foo DROP CONSTRAINT your_constraint; ALTER TABLE foo ADD FOREIGN KEY ...; COMMIT; Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Re: Storing old and new tuple values after an UPDATE, INSERT or DELETE
On Wed, Nov 17, 2010 at 07:37:09AM -0800, rmd22 wrote: Yes I have tried it with triggers but I have to do it without using triggers. Since in my workplace someone has already done that and for some reason (may be because triggers are expensive i suppose...not sure though), hence they want me to do it by modifying the source code. Don't go there. Instead, do this: 1. Get a stated reason, other than I said so, for not using triggers. My top bet is that they'll claim a performance issue. 2. Decide on criteria for evaluating the claim, whatever it is. Make sure that the criteria are not insane. Insane criteria look like, We can't take so much as a 0.001% performance hit anywhere in the system, no matter what it gets us in return. 3. Test the claim. At the end of this, you'll either have tablelog on its way to production, or a solid evidence that you need to find another gig. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Re: Storing old and new tuple values after an UPDATE, INSERT or DELETE
On Wed, Nov 17, 2010 at 09:17:39AM -0800, rmd22 wrote: Yes it's the performance issue. I am going to talk to them about the triggers tomorrow. On the other hand I would still like to know if it is possible to do it by modifying the execMain.c or nodeModifyTable.c files? Yes, but it's insanely risky, and an enormous amount of work that by asking that question you're showing you're not qualified to do. And also what about fast path interface? Is it possible to use it instead? Before you go anywhere like this, do some testing on things that work for the vast majority of people. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] good settings for DB parameters such as shared_buffers, checkpoint_segment in Postrgesql 9
On Thu, Nov 11, 2010 at 08:30:16AM -0500, Vick Khera wrote: On Thu, Nov 11, 2010 at 2:59 AM, AI Rumman rumman...@gmail.com wrote: Server Specification: dual-core 4 cpu RAM: 32 GB OS: Centos What will be good settings for DB parameters such as shared_buffers, checkpoint_segment and etc. I'll take this one ... :) On my 24GB quad-core Opteron servers running FreeBSD 8.1, with big external fibre connected RAID array, I use the following changes relative to the default 9.0.1 postgresql.conf. You probably don't need to adjust the prepared transactions setting, unless you use them :-) The default config is pretty darned good, compared to what used to ship with older releases like 8.1 :) listen_addresses = '*' max_connections = 200 shared_buffers = 4200MB max_prepared_transactions = 100 # guideline: same number as max_connections This should be either 0 (no 2PC) or the bounded from below by max_connections. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 8.2.3
On Wed, Nov 10, 2010 at 09:30:46AM -0800, Jason wrote: Thanks for the replies. Yes - we're aware that there are newer versions of PostgreSQL out there. If it were completely up to us we would be using 8.2.18 or even 8.4. The problem is - we need to install on a network that has a rather involved approval process for all software tools that are introduced. 8.2.3 was previously approved. Getting a newer version of PostgreSQL approved would probably take time that we do not have given the time-critical nature of our effort. Is 8.2.3 still available anywhere on the PostgreSQL site? I couldn't find it. No, and for good reason. That your organization's process is onerous and silly does not by any means imply that the PostgreSQL project needs to take any steps to accommodate itself to that process. What you need to do is start that process and work to make it shorter for PostgreSQL upgrades, or failing that, find something to do with your life, because processes like that are a bright red warning sign of the kind of dysfunction that tanks organizations, no matter how big or important they are. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] what can depend on index
On Tue, Oct 26, 2010 at 10:13:04AM +0200, Szymon Guz wrote: Hi, today I noticed that in the documentation there is DROP INDEX CASCADE. I've got one question: what is that for? What can depend on index? A foreign key can, if the index is unique. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Problem with initdb: creates database which do not exists
On Tue, Oct 19, 2010 at 08:48:13AM +0200, Torsten Zühlsdorff wrote: Thom Brown schrieb: initdb creates a database cluster, not a database. [..] Now i'm feeling like fool - this is so obviously. -.- I will stop posting stressed to the Usenet. Yay, an NNTP user :) We've all been there. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] NoSQL -vs- SQL
On Tue, Oct 19, 2010 at 12:36:44PM -0400, Chris Browne wrote: dp...@pgadmin.org (Dave Page) writes: On Tue, Oct 12, 2010 at 2:58 AM, Peter C. Lai pe...@simons-rock.edu wrote: On 2010-10-11 05:57:37PM -0600, David Boreham wrote: On 10/11/2010 5:46 PM, Carlos Mennens wrote: Just wondering how you guys feel about NoSQL and I just wanted to share the following article... http://www.linuxjournal.com/article/10770 Looking to read your feedback and / or opinions. http://www.xtranormal.com/watch/6995033/ (warning: may not be sfw). Someone should (or probalby has) made one that sounds exactly the same, except for replacign the Mongo guy with MySQL and the MySQL guy with PostgreSQL. That might be more apopros all around ;) Someone did indeed do that: http://nigel.mcnie.name/blog/mysql-is-a-database (also nsfw, iirc) Alas, while it's somewhat funny, it's mighty clear that it's a second-degree derivation, which rather diminishes its power. It gets confused as to who's the questioner, which yanks some sense out of it. At the start, the MySQL aficionado is the speaker, taking questions, but at the end, somehow the Postgres guy ends up thanking everyone for their questions. To make this work requires that it be rather carefully done; sadly, sufficient care doesn't seem to have been taken :-(. Maybe they stored it in MySQL ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Postgres won't start after setting ssl=on
On Sun, Oct 10, 2010 at 12:08:13AM -0700, Mike Christensen wrote: While I do appreciate the vote of confidence, rest assured you will never see a post from me that starts with So I've been hacking the pg code and... Actually, we get *plenty* of those. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] What was new in 8.4 8.3?
On Thu, Oct 07, 2010 at 10:14:26AM -0400, Greg Smith wrote: Scott Ribe wrote: The what's new in 9.0 document on the wiki is great. Is there anything similar for 8.4 8.3 so on? I keep my list of links to interesting articles on the features in each version here: http://wiki.postgresql.org/wiki/Version_History Haven't updated that yet to include anything but the one big 9.0 article you were referring to so far. Should we have a 9.1 one? There's already been at least one large, new feature, namely INSTEAD OF triggers. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Implicit CAST is not working in Postgresql 8.4
On Tue, Sep 28, 2010 at 12:37:46PM +0600, AI Rumman wrote: I migrated data from Postgresql 8.1 to Postgresql 8.4 using pg_dump. But now I found that, most of the queries in my applicaiton are being failed. Invesitigating the problem, I found that no function is available in the DB to CAST INT to TEXT etc. This is due to sloppy coding in your code base, which was, unknown to you, capable of producing surprising, and by surprising, I mean glaringly wrong answers. You need to find the places where your code base contains this slop and clean it up. I can't really recommend that you put in workarounds, as they don't actually fix the bugs you've found. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Commitfest: The Good, The Bad, and the Ugly
Folks, We're almost half way through the commitfest, and so I'll start with: The Good: - Most patches still in play have a reviewer. - It's possible for one person to post 5 reviews in a day. Robert Haas actually did this on his own time yesterday. - New people have been reviewing patches, at least up to the Submission criteria. The Bad: - There is 1 (one) patch marked Committed or Ready for Committer, where neither the author nor reviewer is a committer. This basically means we have approximately one RRReviewer. The Ugly: - Patches are not getting even basic QA. The Bad and the Ugly are fixable, and here's how. At the moment, we've got 7 basic review criteria http://wiki.postgresql.org/wiki/Reviewing_a_Patch, 5 of which can be accomplished with C skills somewhere between 0 and tiny. These are: 1. Submission review (skills needed: patch, English comprehension) 2. Usability review (skills needed: test-fu, ability to find and read spec) 3. Feature test (skills needed: patch, configure, make, pipe errors to log) 4. Performance review (skills needed: ability to time performance) 5. Coding review (skills needed: guideline comparison, experience with portability issues, minor C-reading skills) I'd like to set as a goal that every patch in this commitfest get those levels of review. You do not need C skills[1]. You do not need to be a genius database engine hacker[2]. You just need to be diligent and want to move the project ahead. If you haven't yet, get signed in and start reviewing patches. Sign in with your community login, and let's get going :) https://commitfest.postgresql.org/action/commitfest_view?id=7 In case you were wondering, what I'm doing here is part of step 7. If you think that getting all outstanding patches through step 5 is not doable, let me know. If you think it is, this is your chance to help make it happen. Write back either way. Cheers, David. [1] If you do have them, help out with step 6, too. [2] If you are one, help out with step 6, too. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 dump only the the data without schema?
On Sat, Sep 25, 2010 at 10:50:25AM +0100, Andre Lopes wrote: Hi, I need to generate the dump of a PostgreSQL database only with the data with INSERT's. It is possible to do this? Yes, but are you sure you need to do this? It's *very* slow. What are you using this for? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Exclusion constraint issue
On Fri, Sep 24, 2010 at 05:22:15PM -0400, Tom Lane wrote: Eric McKeeth eldi...@gmail.com writes: why would I get the following error, since the period() function is in fact declared as immutable? test=# ALTER TABLE test3 ADD exclude using gist(period(effect_date::timestamptz, expire_date::timestamptz) with ); ERROR: functions in index expression must be marked IMMUTABLE period() might be immutable, but those casts from date to timestamptz are not, because they depend on the TimeZone parameter. How hard would it be to point out the first expression found to be mutable? All of them? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Exclusion constraint issue
On Sun, Sep 26, 2010 at 10:15:00AM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Fri, Sep 24, 2010 at 05:22:15PM -0400, Tom Lane wrote: period() might be immutable, but those casts from date to timestamptz are not, because they depend on the TimeZone parameter. How hard would it be to point out the first expression found to be mutable? I looked at that yesterday. It would take significant restructuring of the code involved :-( ... the place that throws the error doesn't know exactly what subnode was found to be mutable, and IIRC it hasn't got access to the original command string anyway. How much restructuring are we talking about here? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] value
On Wed, Sep 15, 2010 at 03:16:55PM +, Gissur Þórhallsson wrote: Hi there, I have a somewhat peculiar problem. To begin with, here are links to my schema and rules: my_table and associated rules http://postgresql.pastebin.com/0eCSuvkU and my_table_history http://postgresql.pastebin.com/cGm617Cp [etc.] Does anybody have any idea what is going on? Yes. You're using RULEs where TRIGGERs would do. Change to TRIGGERs. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] workaround steps for autovaccum problem
On Wed, Sep 15, 2010 at 02:39:26AM +0530, tamanna madaan wrote: Hi All I am using postgres-8.1.2. I am getting the following error while autovacuum. Please upgrade your software to PostgreSQL 8.1.21 and try again. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] workaround steps for autovaccum problem
On Wed, Sep 15, 2010 at 05:30:51AM +0530, tamanna madaan wrote: I know upgrading postgres will resolve the problem permanently . But I wanted some workaround for now before I actually upgrade. I want a pony, but I'm not getting one. Upgrade PostgreSQL :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Post Install / Secure PostgreSQL
On Fri, Sep 10, 2010 at 11:53:12AM -0400, Carlos Mennens wrote: On Fri, Sep 10, 2010 at 11:33 AM, Richard Broersma richard.broer...@gmail.com wrote: I don't believe there is a script like this. However, I would say that out of the box, PostgreSQL is so secure that some people cannot figure out how to log in. :) I agree and I am just now learning this. I can't seem to find out how to login to the database. I am using 'psql -U root' however during my installation there may have been a default password used which I am not aware of. I need to read the docs and see how to login to the database. This is where MySQL's crazily-insecure-by-default assumptions are messing you up. The root user has nothing to do with PostgreSQL, except in the sense that root installs software. Thereafter, the postgres (or pgsql on some of the BSDs) user is the database superuser. Once it's installed, try: su - postgres psql -l Happy PostgreSQLing :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Frustration with \copy
On Fri, Sep 10, 2010 at 10:25:52AM -0700, Ralph Smith wrote: Yes, we are planning to upgrade to 8.3, but now I'm stuck w/ 7.4. Don't stay stuck there too long. It's about to end its life, as are 8.0 and 8.1, later this year. http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy Should I just INSERT? Nope. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] regexp on null
On Fri, Sep 10, 2010 at 10:25:55AM -0700, Richard Broersma wrote: On Fri, Sep 10, 2010 at 9:56 AM, Gauthier, Dave dave.gauth...@intel.com wrote: Ya, I kinda knew about these approaches. The problem ahs to do with novice users who don't know about coalesce or or;ing a check ofr nulls. I was hoping there was some special regexp expression that would match to a null. Perhaps the easiest was to help such users is to make the column(s) in question NOT NULL DEFAULT ''. I think it's a very, very bad idea to make a default like that. I'm aware that it's common in one of those Oracle properties, but it needs to stay confined there. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] error while autovacuuming
On Fri, Sep 10, 2010 at 12:53:10AM +0530, tamanna madaan wrote: Hi Scott Sorry to bug you again. I know that upgrading to postgres-8.1.21 will be my best bet. But I have my own limitations because of which I just want to apply a patch with a single fix which is for autovacuum error. Your idea is silly. Change the binary, restart, and have done. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] error while autovacuuming
On Fri, Sep 10, 2010 at 02:35:29AM +0530, tamanna madaan wrote: Hi David You mean to say , change the binary to postgres-8.1.21 and then restart postgres . that's it ?? please confirm.. Yes. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Table update problem works on MySQL but not Postgres
On Tue, Aug 31, 2010 at 07:56:23PM -0400, Raymond C. Rodgers wrote: Let me stress that this is not a bug in PostgreSQL; if anything at all, it's only a lack of a stupid feature. PostgreSQL's version involves UPDATE ... FROM. Use an ORDER BY in the FROM clause like this: UPDATE mydemo SET cat_order = m.cat_order+1 FROM ( SELECT cat_order, client_id FROM mydemo WHERE client_id = 1 AND cat_order = 0 ORDER BY cat_order) m WHERE mydemo.cat_order = m.cat_order AND mydemo.client_id = m.client_id More details on PostgreSQL's UPDATE are at: http://www.postgresql.org/docs/current/static/sql-update.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Restore referencial integrity
On Sun, Aug 29, 2010 at 11:30:57PM -0300, Carlos Henrique Reimer wrote: Hi, We had by mistake dropped the referencial integrety between two huge tables Agora o elefante vai pegar! ;) and now I'm facing the following messages when trying to recreate the foreign key again: alter table posicoes_controles add CONSTRAINT protocolo FOREIGN KEY (protocolo) REFERENCES posicoes (protocolo) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE; ERROR: insert or update on table posicoes_controles violates foreign key constraint protocolo DETAIL: Key (protocolo)=(338525035) is not present in table posicoes. ** Erro ** ERROR: insert or update on table posicoes_controles violates foreign key constraint protocolo SQL state: 23503 Detalhe: Key (protocolo)=(338525035) is not present in table posicoes. As the error message tells, the table posicoes_controles has values in column protocolo that are not present in column protocolo of table posicoes. This happened because some programs removed rows from table posicoes while the referencial integrity was dropped. Now I need to remove all rows from table posicoes_controles that has not corresponding row in table posicoes. As these are huge tables, almost 100GB each, and the server hardware restricted (4GB RAM) I would like a suggestion of which command or commands should be used from the performance perspective. First, if pescioes_controles doesn't already have an index on protocolo, create such an index. You can do something like CREATE INDEX CONCURRENTLY ON pescioes_controles(protocolo); After you have finished the indexing, you'll need to schedule some down time, cut off all other access to the server, and then run something like the following: BEGIN; DELETE FROM pescioes_controles WHERE NOT EXISTS ( SELECT 1 FROM pesicoes WHERE pesicoes.protocolo = pescioes_controles.protocolo ); ALTER TABLE posicoes_controles add CONSTRAINT protocolo FOREIGN KEY (protocolo) REFERENCES posicoes (protocolo) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE; COMMIT; Hope this helps :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Restore referencial integrity
On Mon, Aug 30, 2010 at 05:04:36PM -0300, Carlos Henrique Reimer wrote: Hi Thank David and Georg for your suggestions. Yes, there is an index now defined on column protocolo in table posicoes_controles. Legal! I've selected two suggested commands to compare which would be more performatic and which will run faster: Option 1) explain delete from posicoes_controles where protocolo not in (select protocolo from posicoes); Seq Scan on posicoes_controles (cost=9954587.42..1185225908771206.50 rows=189513428 width=6) Filter: (NOT (subplan)) SubPlan - Materialize (cost=9954587.42..15255636.80 rows=381199038 width=4) - Seq Scan on posicoes (cost=0.00..8084329.38 rows=381199038 width=4) Option 2) explain delete FROM posicoes_controles WHERE NOT EXISTS ( SELECT 1 FROM posicoes WHERE posicoes.protocolo = posicoes_controles.protocolo ); Seq Scan on posicoes_controles (cost=0.00..9560672015.05 rows=189419047 width=6) Filter: (NOT (subplan)) SubPlan - Index Scan using pk_posicoes_protocolo on posicoes (cost=0.00..25.19 rows=1 width=0) Index Cond: (protocolo = $0) I'm not an explain specialist but I understood the second option will run much more faster. It probably will. EXISTS returns immediately when it finds the first row. Let me know if I understood the explain for the second option: 1) Run a seq scan on posicoes_controles and get the protocolo key to access posicoes_protocolo 2) For each row accessed in item 1 run an index scan on posicoes to check if the key is in the table posicoes 3) If the parent found is not found on posicoes then remove the row from posicoes_controles Am I thinking correctly? I believe so. Cheers, David (whose pt_BR is pretty w34k) -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] select from pipe-delimited field
On Mon, Aug 23, 2010 at 05:44:09PM -0500, san man wrote: Hello all, I am trying to do a SELECT operation with a WHERE condition. However, the column with which I am trying to do the comparison has several values which are pipe-delimited. I want to return a match(true) if the WHERE condition matches any of the bar-delimited values. You'll want to normalize this table into two or more tables, at some point. For example, SELECT id WHERE synonyms = 'word'; Here synonyms is a pipe-delimited field and I want to match word with any of the values of the synonyms fields. Try the LIKE function. http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-LIKE Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] deadlock
On Tue, Aug 10, 2010 at 11:35:48PM -0700, John R Pierce wrote: We've got an app, I don't know all the details of the schema offhand, but its using date partitioned tables, its heavily multithreaded and processing continuous events... Under load, production (overseas) is getting a SQL deadlock... Process 20333: DROP table data_details_20100718 Process 20333 waits for AccessExclusiveLock on relation 29609 of database 16384; blocked by process 20307. Process 20307: select * from data_daily where f1 =$1 and f2=$2 and f3=$3 and f4=$4 and ... Process 20307 waits for AccessShareLock on relation 28523 of database 16384; blocked by process 20333. I'm -assuming- that the table being dropped is a partition of the other table. I've asked the developers (my coworkers) to confirm, and for any details of how they are doing the partitions. That seems super likely, given its name and the fact that it's being dropped. does anyone have any suggestions for what to look for, or what sort of common partition management mistakes in the application could lead to this sort of deadlock? DDL is a don't do it at peak load event. More realistically, it's more like a down time event. Maybe when we have real partitioning... Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Using AND in query
On Sun, Aug 08, 2010 at 01:55:19AM -0700, John R Pierce wrote: The condition and table.item = 'laptop' and table.item = 'Desktop' says: I want all rows where the column item has the value 'Laptop' and *at the same time* has the value 'Desktop' Which clearly cannot be the case (a column can only have a single value) So you need to join all Laptop rows to all Desktop rows to get what you want. why not use OR ? ... AND (table.item = 'laptop' OR table.item='Desktop') ... OR doesn't account for duplicates. Two laptops on the same date would cause a false positive. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Using AND in query
On Sun, Aug 08, 2010 at 12:33:40PM -0700, David Fetter wrote: On Sun, Aug 08, 2010 at 01:55:19AM -0700, John R Pierce wrote: The condition and table.item = 'laptop' and table.item = 'Desktop' says: I want all rows where the column item has the value 'Laptop' and *at the same time* has the value 'Desktop' Which clearly cannot be the case (a column can only have a single value) So you need to join all Laptop rows to all Desktop rows to get what you want. why not use OR ? ... AND (table.item = 'laptop' OR table.item='Desktop') ... OR doesn't account for duplicates. Two laptops on the same date would cause a false positive. Thinking this over a little more, it's probably fastest to combine the approaches, i.e. use both a WHERE clause and a HAVING clause. For example: SELECT TID, Date FROM table WHERE item = ANY(ARRAY['Desktop','Laptop']) GROUP BY TID, Date HAVING ARRAY['Desktop','Laptop'] @ array_agg(item); Cheers, David -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Using AND in query
On Sat, Aug 07, 2010 at 12:40:41PM -0700, aravind chandu wrote: Hello every one, I have encountered a problem while working .I have a sample table with the following data TID Date Item T100 8/1/2010 Laptop T100 8/1/2010 Desktop T101 8/1/2010 Laptop T102 8/1/2010 Desktop T103 8/2/2010 Laptop T103 8/2/2010 Desktop T104 8/2/2010 Laptop need the data when a person bought laptop desktop on the sameday. This is actually relatively straight-forward using modern PostgreSQL. Rather than counting, use direct aggregation to compare, so: SELECT TID, Date FROM table GROUP BY TID, Date HAVING ARRAY['Laptop','Desktop'] @ array_agg(item); That last line checks whether the array created by array_agg contains at least the elements Laptop and Desktop. If you need an equals comparison rather than the above contains or equals, you can sort both arrays canonically using the array_sort function below and then compare them with =. CREATE OR REPLACE FUNCTION array_sort(ANYARRAY) RETURNS ANYARRAY LANGUAGE SQL AS $$ SELECT ARRAY(SELECT * FROM unnest($1) ORDER BY 1); $$; The = query would look like this: SELECT TID, Date FROM table GROUP BY TID, Date HAVING array_sort(ARRAY['Laptop','Desktop']) = array_sort(array_agg(item)); Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Could you teach me, How can we specify password when using psql....
On Fri, Aug 06, 2010 at 01:35:58PM +0900, 노현석 wrote: hi.. when using oracle sqlplus.. we can specify password. $ sqlplus system/manager Could you teach me, How can we specify password when using psql $ psql -p 5432 -h rac2 -d mydb -U hsnoh Password for user hsnoh: Thanks.. Overall, it's better to use a .pgpass (pgpass.conf on Windows) http://www.postgresql.org/docs/current/static/libpq-pgpass.html It's also possible, but not recommended, to set an environment variable. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 database procedures?
On Wed, Aug 04, 2010 at 07:38:15AM -0400, zhong ming wu wrote: On Wed, Aug 4, 2010 at 7:32 AM, Machiel Richards machi...@rdc.co.za wrote: Good day all I am looking for some info / resources where I can learn how to write database procedures, functions,etc? Do you have any particular tasks in mind? Since PostgreSQL lets you use the vast majority of common programming languages to do this, it would be helpful if you mentioned one or more you're familiar with. It's possible to write VIEWs, which are essentially a way not to write queries over and over again manually. http://www.postgresql.org/docs/current/static/sql-createview.html Next step up would probably be functions in SQL, which are a lot like VIEWs, only they can take parameters. As SQL is Turing complete, functions in SQL can do quite a lot. http://www.postgresql.org/docs/current/static/xfunc-sql.html Then there's trigger functions and the associated triggers. http://www.postgresql.org/docs/current/static/triggers.html You can keep going from there, up to and including using PostgreSQL components in some other system :) I am a total newbie to this and will need to learn from scratch Would appreciate the help a lot This list is one good place to get help. Another is the IRC channel on freenode irc://irc.freenode.net/postgresql Machiel RTFM http://www.postgresql.org/docs/8.4/interactive/xplang.html As The Fine Manual is very extensive, telling people just to Read it from some arbitrary point is just barely more helpful than not specifying one, i.e. not terribly. Perhaps asking a few more questions would be. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Nodes and trees...
On Tue, Aug 03, 2010 at 02:01:58PM +0200, Jason Schauberger wrote: Dear fellow Postgres users, :-) please consider this table: CREATE TABLE nodes ( id int PRIMARY KEY, parent int REFERENCES nodes(id) ); Generally, you'll want to separate the nodes table from the edges table, as in: CREATE TABLE nodes (id INTEGER PRIMARY KEY); CREATE TABLE edges ( tail INTEGER NOT NULL REFERENCES nodes(id), head INTEGER NOT NULL REFERENCES nodes(id), PRIMARY KEY(tail, head), CHECK (tail head) ); Then you might want to prevent other kinds of issues (more uniqueness, must be forest, etc.) with other constraints, but let's not go there for now. In this table, each node *can* have a parent node. You can picture the whole set of rows of this table as one or more trees with nodes and the root of the tree is the node which has no parent node (that is, parent is NULL). Now here's my objective: I want to *quickly* find all nodes that have the same root node. Given a root node, i.e. one which appears only as a tail in the edges table, you'd do something like this: WITH descendants AS ( SELECT head FROM edges WHERE tail=1 /* the root node */ UNION SELECT e.head FROM edges e JOIN descendants d ON (e.tail = d.head) ) SELECT * FROM descendants; You might want to index edges.tail and edges.head. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] order in which rules are executed
On Wed, Jul 28, 2010 at 10:16:45PM +0530, Ranjeeth Nagarajan wrote: Hello All, I have the below query regarding Rules in PostgreSQL: If I have a table which has multiple rules defined, are the rules executed in the order in which they are defined? Or are they executed in some random order? They're executed in alphabetical order, to the extent that that is deterministic, which is not very, and that's not even the wackiest thing about them. If you have any alternative of any nature, do NOT use rules. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 dump
On Mon, Jul 26, 2010 at 08:58:59AM -0700, Scott Frankel wrote: Hi all, Is it possible to perform an SQL Dump without using pg_dump? No, but there may be more options for using pg_dump than you have looked at. One example would be to use pg_dump on one with an SSH tunnel to the other one's local PostgreSQL port (5432 by default, but check which yours is). For example: ssh -fNR 5432:localhost: postg...@your.host.dom would let you connect to localhost: with pg_dump and any other PostgreSQL tools. Cheers, David. I have a special case situation wherein my application has access to a remotely-hosted PG (8.3) database, but does not have access to its admin tools. (There's a longer backstory here that I'm happy to explain if necessary.) I'm looking for an efficient way to dump all the data in the DB without having to SELECT * on each table. Thanks in advance! Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] cache lookup failed for function 19119
On Thu, Jul 15, 2010 at 10:21:52AM -0400, Merlin Moncure wrote: On Thu, Jul 15, 2010 at 2:34 AM, tamanna madaan tamanna.ma...@globallogic.com wrote: Hi All I am using postgres-8.1.2 . And getting this error “cache lookup failed for function 19119”. Can anyone please let me know what could have gone wrong. How can a function go missing . And which function Its talkig about ?? its some postgres’s internal function or a user defined function ?? How can I get function name corresponding 19119 The function is either gone (it was deleted manally from pg_proc for example), dropped, added, etc. or there is some other problem. You might be able to fix the problem by recreating the function (create/replace) that is calling the function in question (your database log should be giving you some context). You are on 8.1.2 which is crazy. you need to immediately get the latest bugfix release for the 8.1 series. You might want to consider a dump/reload...read the release notes for the 8.1 series here: http://www.postgresql.org/docs/8.1/static/release.html. You might also want to note that 8.1's end of life is in November, so start planning the upgrade to 9.0 right now. You will likely need to clean up some client code in order for that to work, as modern versions of PostgreSQL don't allow some of the sloppy and dangerous things (casting automatically to and from text, e.g.) that former versions did. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 help doing a CSV import
On Wed, Jul 14, 2010 at 01:20:25PM +, Tim Landscheidt wrote: Craig Ringer cr...@postnewspapers.com.au wrote: I am in the process of moving a FoxPro based system to PostgreSQL. We have several tables that have memo fields which contain carriage returns and line feeds that I need to preserve. I thought if I converted these into the appropriate \r and \n codes that they would be imported as carriage returns and line feeds, but instead they are stored in the database as \r and \n. PostgreSQL doesn't process escapes in CSV import mode. You can reformat the data into the non-csv COPY format, which WILL process escapes. Or you can post-process it after import to expand them. Unfortunately PostgreSQL doesn't offer an option to process escapes when CSV mode COPY is requested. I posted a little Python script that reads CSV data and spits out COPY-friendly output a few days ago. It should be trivially adaptable to your needs, you'd just need to change the input dialect options. See the archives for the script. Another option is a small Perl script or something similar that connects to both the FoxPro and the PostgreSQL database and transfers the data with parameterized INSERT. The ad- vantage of this is that you have tight control of charsets, date formats, EOL conventions Co. and do not have to won- der whether this and that file is in this and that stage of the conversion process, the disadvantage is obviously that you lose any speed benefit of bulk COPY. You can do your transformations and hand the stream off to the COPY interface. See the pg_putcopydata() section of the DBD::Pg manual for examples. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Planner features, discussion
On Wed, Jul 14, 2010 at 08:47:35AM +0800, Craig Ringer wrote: On 13/07/2010 10:52 PM, Greg Smith wrote: I heard a scholarly treatment of that topic from Jim Nasby recently, where he proposed a boolean GUC to toggle the expanded search behavior to be named plan_the_shit_out_of_it. I was thinking that something like duplicate subquery/function elimitation might be handy, though an extension to WITH would eliminate the need for it (see below). Consider code like this: SELECT (SELECT somequery) FROM ... WHERE (SELECT SOMEQUERY) somevalue ORDER BY (SELECT somequery) that invokes some non-trivial somequery several times. I often wanted to simplify it, and it wasn't always practical to convert it to add (SELECT somequery) to the join list. I expected that with 8.4 I'd be able to write something more along the lines of: WITH result = (SELECT somequery) SELECT result FROM ... WHERE result somevalue ORDER BY result; which makes such an optimization less than necessary. Why complicate the planner when you can fix your SQL? However, in the case above the subquery needs to be referenced from a scalar context not as a join, and WITH expressions don't seem to be useful for scalar results. The names defined by WITH are only visible as FROM targets. So this doesn't work: = WITH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval FROM generate_series(1,10) AS x; ERROR: column constval does not exist LINE 1: ...TH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval F... You missed the CROSS JOIN, which you could make implicit, even though implicit CROSS JOINs are bad coding style: WITH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval FROM generate_series(1,10) AS x CROSS JOIN aconstant; x | constval +-- 1 |1 2 |1 3 |1 4 |1 5 |1 6 |1 7 |1 8 |1 9 |1 10 |1 (10 rows) ... so you're forced to fall back on adding it as an additional join expression - which isn't always reasonable or possible. Why not? Extending WITH to be useful for defining constants and single-evaluation variables like the above would be really, really nice, and would avoid some ugly SQL mangling and any need for compliated planner features that try to match up and combine subquery trees. I'm all for extending WITH, as are some others. See this thread for the latest: http://archives.postgresql.org/pgsql-hackers/2010-07/msg00463.php Cheers, David (who's not mentioning extending WITH to include DCL or DDL yet...oops! ;) -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [pgsql-advocacy] Anyone in Madison?
On Wed, Jul 07, 2010 at 02:31:04PM -0700, Josh Berkus wrote: Folks, I'll be unexpectedly in Madison next week for an onsite contract. Is there a user group in Madison I could meet up with? Maybe do a quick session on 9.0? As there are almost as many Madisons as Springfields, it may help to mention that Josh is going to the one in Wisconsin. Cheers, David -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Find users that have ALL categories
On Wed, Jun 30, 2010 at 12:11:35AM -0700, Nick wrote: Is this the most efficient way to write this query? Id like to get a list of users that have the categories 1, 2, and 3? SELECT user_id FROM user_categories WHERE category_id IN (1,2,3) GROUP BY user_id HAVING COUNT(*) = 3 users_categories (user_id, category_id) 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 3 | 1 4 | 1 4 | 2 4 | 3 The result should produce 1 4. The above method depends on (user_id, category_id) being unique, and excludes users with, say, categories 1, 2, 3 and 4. Are you sure that that latter is what you want? This is, I believe, a little clearer as to what it's actually doing, and doesn't exclude user_ids with more matches: SELECT user_id FROM user_categories GROUP BY user_id HAVING array_agg(category_id) @ ARRAY[1,2,3] ORDER BY user_id; /* Not really needed, but could be handy */ In 9.0, you'll be able to use the following to get only exact matches: SELECT user_id FROM user_categories GROUP BY user_id HAVING array_agg(category_id ORDER BY category_id) = ARRAY[1,2,3] ORDER BY user_id; /* Not really needed, but could be handy */ Until then, you can make an array_sort() function like this: CREATE OR REPLACE FUNCTION array_sort(ANYARRAY) RETURNS ANYARRAY LANGUAGE SQL STRICT AS $$ SELECT ARRAY( SELECT unnest($1) AS i ORDER BY i ); $$; then use it like this: SELECT user_id FROM user_categories GROUP BY user_id HAVING array_sort(array_agg(category_id)) = ARRAY[1,2,3] ORDER BY user_id; to get only exact matches. As to speed, you'd have to test on your actual data sets. Indexing user_id may help here. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Find users that have ALL categories
On Thu, Jul 01, 2010 at 12:37:55PM +0100, Sam Mason wrote: On Thu, Jul 01, 2010 at 04:26:38AM -0700, David Fetter wrote: On Wed, Jun 30, 2010 at 12:11:35AM -0700, Nick wrote: Is this the most efficient way to write this query? Id like to get a list of users that have the categories 1, 2, and 3? SELECT user_id FROM user_categories WHERE category_id IN (1,2,3) GROUP BY user_id HAVING COUNT(*) = 3 The above method depends on (user_id, category_id) being unique, and excludes users with, say, categories 1, 2, 3 and 4. Are you sure that that latter is what you want? AFAICT, the above code will include a user with categories 1 to 4. Why do you think otherwise? If the (user_id,category_id) combination isn't unique, it's easy to change the HAVING clause into HAVING COUNT(DISTINCT category_id) = 3. Oops. You're right, of course. That's what I get for posting before waking up. ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] DBI::Oracle problems
On Wed, Jun 30, 2010 at 10:10:02AM +1000, Howard Rogers wrote: I am stumped, despite working on this for a week! I am trying to create a 64-bit postgresql 8.4 database server which can retrieve data from various 64-bit Oracle 10gR2 and 11gR2 databases. Try downloading the latest version of DBI-Link using the Download Source link at http://github.com/davidfetter/DBI-Link There is also a low-traffic mailing list for the project, where questions like this are more on point :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 Fri, Jun 25, 2010 at 09:44:04AM +0100, Dave Page wrote: It could also be argued that having a storage engine API means that the query planner/optimiser cannot have nearly as much knowledge about how the data is stored and what access characteristics it may have thus preventing it from being as well optimised as Postgres. Having it divided off at the place where it's divided in MySQL is certainly such a barrier. Having a storage API, as PostgreSQL used to have, and will have again with SQL/MED, doesn't necessarily present such a barrier. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Converting a Simple Database from MySQL to PostgreSQL in 40 hours?
On Thu, Jun 24, 2010 at 03:41:08PM -0700, Wang, Mary Y wrote: Hi, My internal customer has a new project and is considering using MySQL. Knowing that I'm a Postgres person, the customer is open to considering Postgres at a later date if the cost to transition is less than 40 hours. The database will probably be relatively small and simple (still in the planning stage). I personally think it's not possible to convert even a simple database from MySQL to Postgres in less than 40 hours. Has anyone done the conversion before? If so, what do you think? This depends on a great many factors including: * The size of the data * What it's currently stored on for MySQL (a single slow spindle with a couple of TB on it may take time) * What it will be stored on for PostgreSQL * What MySQL idioms won't translate directly to PostgreSQL Generally, getting the schema and data moved over are the first two steps in a much longer process, wherein all the apps use the database as an active database rather than a passive one, the latter being all MySQL really allows. Hope this helps :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] A thought about other open source projects
On Mon, Jun 21, 2010 at 08:35:02AM -0400, Lew wrote: David Goodenough wrote: I don't support anyone has written a how to write database agnostic code guide? That way its not a matter of porting, more a matter of starting off right. There is no real way to write database[-]agnostic SQL, although of course middleware code can and should be. Database-agnostic middleware is not a practical or desirable goal for the same reason that database-agnostic SQL isn't. The original reasoning behind the radical experiment of database-agnostic was an attempt to defend against the depredations of vendors of proprietary RDBMSs, who tended to use strong-arm tactics any time they felt they could get away with it. As a strategy, database-agnostic has failed because the only two (combinable) ways to implement it are enormously expensive even to create, and super-linearly expensive to maintain. I've covered these below: http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html Fortunately, another strategy whose effect is to defend against the above-mentioned strong-arm tactics--making a wide selection of non-proprietary RDBMSs--has succeeded. Just pick an RDBMS and max out its capabilities. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] A thought about other open source projects
On Mon, Jun 21, 2010 at 04:14:10PM +0100, David Goodenough wrote: On Monday 21 June 2010, Lew wrote: Sim Zacks wrote: database agnostic code is theoretically a great idea. However, you lose most of the advantages of the chosen database engine. For example, if you support an engine that does not support relational integrity you cannot use delete cascades. The most efficient way is to have a separate backend module per database (or db version) supported. The quickest way is to write code that will work on any db but won't take advantage of db-specific features. David Goodenough wrote: This is what I am trying to encourage. I am asking about the best way to encourage it. You want to encourage the use of databases that don't support relational integrity? no, I want to encourage The quickest way is to write code that will work on any db but won't take advantage of db-specific features. As with phrases like, the quickest way to grill a unicorn steak, that it can be stated in a few words does not make in possible. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] A thought about other open source projects
On Mon, Jun 21, 2010 at 01:55:36PM -0400, Brad Nicholson wrote: Scott Marlowe wrote: As with phrases like, the quickest way to grill a unicorn steak, that it can be stated in a few words does not make in possible. Exactly. The big issue here is that nobody's saying what kind of app they want to write. Or what sort of performance requirements are tied to that app. It's not performance requirements that tend to tank such projects, but the amount of maintenance involved. Extending the app gets quadratically painful. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] High Availability with Postgres
On Sun, Jun 20, 2010 at 07:34:10PM +0300, Elior Soliman wrote: Hello, My company looking for some solution for High availability with Postgres. Our optional solution is as follows : Two DB servers will be using a common external storage (with raid). Stop right there. This is the Oracle way of doing things, and it doesn't work for PostgreSQL. Both servers are going to use the same DB files on the storage (as active/passive) Now I'm trying to understand how Postgres can work with this configuration. I.e : It does not. There are plenty of ways to get that broad spectrum of sometimes contradictory things people mean when they use the phrase HA with PostgreSQL, but you must first define your requirements. Once you have done so, it will be possible to create strategies for achieving same. What are the actual requirements? Things that would be nice to have? What are your priorities for both? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] A thought about other open source projects
On Sun, Jun 20, 2010 at 10:08:34AM +0100, David Goodenough wrote: On Sunday 20 June 2010, Peter Eisentraut wrote: On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote: These projects need help to realise that adding Postgresql is not a big job, especially for those using JDBC which can already connect to all DBs. It strikes me that if the project could write a few pages gleaned from other porting operations, then whenever a project like this is found they can be pointed to these pages and shown how easy it is to do. http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreS QL Excellent, I had not realised this existed. I will point any projects I meet which have not found Postrgesql goodness at this page. Thank you. I don't support anyone has written a how to write database agnostic code guide? I have. :) http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html That way its not a matter of porting, more a matter of starting off right. You're assuming that the goal of database agnostic code is reasonable. I'd take a hard look at the trade-offs first. Database agnostic code sounds like a reasonable idea until you've had to maintain such code for a few years. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] use window as field name in 8.4
On Tue, Jun 15, 2010 at 08:58:52AM -0600, Peter Lee wrote: I am trying to upgrade our postgresql from 8.3 to 8.4. I found the window as field name makes many errors during pg_restore. - like item.window. Is there any way I can restore the dump file from 8.3 without errors. Use 8.4's pg_dump on the running 8.3 database, and your problem will be fixed :) Cheers, David (oh, and pg_dump -Fc will let pg_restore, an enormously powerful utility, do its magic). -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Move data from DB2 to Postgres any software/solutions/approach?
Deepak, You can use DBI-Link to make writeable views of tables in DB2 (or other data store) from PostgreSQL. You can use the same linkage to materialize those views, if you like. The latest version of the software is on GitHub http://github.com/davidfetter/DBI-Link You can also join the low-traffic mailing list at http://pgfoundry.org/projects/dbi-link/ Cheers, David. On Fri, Jun 04, 2010 at 02:33:53PM -0700, DM wrote: Sorry i didnt frame my question properly earlier, we are looking for solution to do real time replication from db2 to postgres, its different from migration. Eventually we want to move away from DB2. Intention is to create a subset of a db2 database on postgres and allow users to access the postgres database. Thanks Deepak On Fri, Jun 4, 2010 at 2:23 PM, DM dm.a...@gmail.com wrote: Thanks Robert, Is there any tools available. Thanks Deepak On Fri, Jun 4, 2010 at 2:19 PM, Richard Broersma richard.broer...@gmail.com wrote: On Fri, Jun 4, 2010 at 2:13 PM, DM dm.a...@gmail.com wrote: We want to replicate /move data form db2 to postgres is there any software / solutions / approach available to do this? Here is a link on the postgresql wiki. Hopefully it has some useful information. http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#IBM_DB2 -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Is it possible to make the order of output the same as the order of input parameters?
On Tue, Jun 01, 2010 at 06:16:06PM -0400, m. hvostinski wrote: Hi, I have a simple query like: SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10) The problem is that I need to retrieve the rows in the same order as the set of ids provided in the select statement. Can it be done? Sure, but it can be a little cumbersome to set up at first. WITH t(a) AS (VALUES (ARRAY[23, 56, 2, 12, 10])), s(i) AS (SELECT generate_subscripts((SELECT a FROM t)::integer[], 1)) SELECT i, a[i] FROM s CROSS JOIN t; will give you the indexes along with the elements, and you can then sort by those. If you happen to know in advance that you'll only have integers, you can do this: CREATE OR REPLACE FUNCTION index_list(integer[]) RETURNS TABLE(i integer, e integer) LANGUAGE SQL AS $$ WITH t(a) AS (VALUES ($1)), s(i) AS (SELECT generate_subscripts((SELECT a FROM t)::integer[], 1)) SELECT i, a[i] FROM s CROSS JOIN t; $$; You can then use that set-returning function in your query. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] server-side extension in c++
On Wed, Jun 02, 2010 at 10:11:37AM +0800, Craig Ringer wrote: On 02/06/10 09:23, Bruce Momjian wrote: Tom Lane wrote: Craig Ringer cr...@postnewspapers.com.au writes: On 01/06/10 11:05, Tom Lane wrote: I'd be interested to see a section like this written by someone who'd actually done a nontrivial C++ extension and lived to tell the tale. I can't speak up there - my own C++/Pg backend stuff has been fairly trivial, and has been where I can maintain a fairly clean separation of the C++-exposed and the Pg-backend-exposed parts. I was able to keep things separate enough that my C++ compilation units didn't include the Pg backend headers; they just exposed a pure C public interface. The Pg backend-using compilation units were written in C, and talked to the C++ part over its exposed pure C interfaces. Yeah, if you can design your code so that C++ never has to call back into the core backend, that eliminates a large chunk of the pain. Should we be documenting design ideas like this one? I have incorporated the new ideas into the C++ documentation section, and removed the comment block in the attached patch. If you're going to include that much, I'd still really want to warn people about exception/error handling too. It's important. I made brief mention of it before, but perhaps some more detail would help if people really want to do this. ( BTW, all in all, I agree with Tom Lane - the best answer is don't. Sometimes you need to access functionality from C++ libraries, but unless that's your reason I wouldn't ever consider doing it. ) Here's a rough outline of the rules I follow when mixing C/C++ code, plus some info on the longjmp error handling related complexities added by Pg: Letting an exception thrown from C++ code cross into C code will be EXTREMELY ugly. The C++-to-C boundaries *must* have unconditional catch blocks to convert thrown exceptions into appropriate error codes, even if the C++ code in question never knowingly throws an exception. C++ may throw std::bad_alloc on failure of operator new(), among other things, so the user must _always_ have an unconditional catch. Letting an exception propagate out to the C-based Pg backend is rather likely to result in a backend crash. If the C++ libraries you are using will put up with it, compile your C++ code with -fno-exceptions to make your life much, much easier, as you can avoid worrying about this entirely. OTOH, you must then check for NULL return from operator new(). If you can't do that: My usual rule is that any extern C function *must* have an unconditional catch. I also require that any function that may be passed as a function pointer to C code must be extern C and thus must obey the previous rule, so that covers function pointers and dlopen()ed access to functions. Similarly, calling Pg code that may use Pg's error handling from within C++ is unsafe. It should be OK if you know for absolute certain that the C++ call tree in question only has plain-old-data (POD) structs and simple variables on the stack, but even then it requires caution. C++ code that uses Pg calls can't do anything it couldn't do if you were using 'goto' and labels in each involved function, but additionally has to worry about returning and passing non-POD objects between functions in a call chain by value, as a longjmp may result in dtors not being properly called. The best way to get around this issue is not to call into the Pg backend from C++ code at all, instead encapsulating your C++ functionality into cleanly separated modules with pure C interfaces. If you don't #include any Pg backend headers into any compilation units compiled with the C++ compiler, that should do the trick. If you must mix Pg calls and C++, restrict your C++ objects to the heap (ie use pointers to them, managed with new and delete) and limit your stack to POD variables (simple structs and built-in types). Note that this means you can't use std::auto_ptr, std::tr1:shared_ptr, RAII lock management, etc in C++ code that may call into the Pg backend. Is PostGIS following these guidelines? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Is it possible to make the order of output the same as the order of input parameters?
On Wed, Jun 02, 2010 at 03:33:16PM +0100, Sam Mason wrote: On Wed, Jun 02, 2010 at 06:28:14AM -0700, David Fetter wrote: On Tue, Jun 01, 2010 at 06:16:06PM -0400, m. hvostinski wrote: I have a simple query like: SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10) The problem is that I need to retrieve the rows in the same order as the set of ids provided in the select statement. Can it be done? Sure, but it can be a little cumbersome to set up at first. WITH t(a) AS (VALUES (ARRAY[23, 56, 2, 12, 10])), s(i) AS (SELECT generate_subscripts((SELECT a FROM t)::integer[], 1)) SELECT i, a[i] FROM s CROSS JOIN t; Isn't this fun; here's another version using window functions (from PG 8.4 onwards) this time: SELECT c.* FROM customer c, ( SELECT *, row_number() OVER () FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord) WHERE c.id = x.val ORDER BY x.ord; How about both, along with a modern JOIN? WITH t AS ( VALUES(ARRAY[23, 56, 2, 12, 10]) ), s AS ( SELECT id, row_number() OVER () AS ord FROM UNNEST((SELECT * FROM t)::int[]) AS r(id) ) SELECT c.* FROM customer c JOIN s USING(id) ORDER BY s.ord; And a similar function to the above :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Is it possible to make the order of output the same as the order of input parameters?
On Wed, Jun 02, 2010 at 11:06:06AM -0400, m. hvostinski wrote: Thanks to all for the feedback. I keep getting impressed by how flexible PostgreSQL is. Any ideas which query should perform better? I put together all the suggested approaches below. Testing beats theorizing any day. The array-based approaches are there pretty much for convenience, i.e. for not having to input the numbers more than once, as they could easily get mistyped if you need to repeat them. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] server-side extension in c++
On Wed, Jun 02, 2010 at 05:41:10PM +0100, Mark Cave-Ayland wrote: David Fetter wrote: Is PostGIS following these guidelines? In short, no. Due to various problems in the early days with C++ exceptions generated by the GEOS library causing problems in C (and also ABI changes forcing a recompile of any GEOS linked library), a thin intermediate C++ layer called libgeos_c was added to GEOS. For each public C++ function, libgeos_c declares a similarly-named wrapper with extern C that just executes the underlying C++ function. If an underlying error such as an exception occurs, the libgeos_c wrapper returns false, and a simple handler allows the C caller to retrieve the related error string. While it does seem quite inelegant, I don't believe any problems linking between C/C++ have been reported on any compiler/platform since this was put into place. It's good to have actual working code in production to bolster the case that the design is sound. How much work would it be to refactor libgeos_c to use a catch-all exception handler? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] server-side extension in c++
On Tue, Jun 01, 2010 at 02:13:02PM +0800, Craig Ringer wrote: On 01/06/10 11:05, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Tom Lane wrote: Personally I would reduce this section to Don't. Well, I would have avoided this mine-trap except we have this 9.0 release note item: Allow use of productnameC++/ functions in backend code (Kurt Harriman, Peter Eisentraut) I'd be interested to see a section like this written by someone who'd actually done a nontrivial C++ extension and lived to tell the tale. I can't speak up there - my own C++/Pg backend stuff has been fairly trivial, and has been where I can maintain a fairly clean separation of the C++-exposed and the Pg-backend-exposed parts. I was able to keep things separate enough that my C++ compilation units didn't include the Pg backend headers; they just exposed a pure C public interface. The Pg backend-using compilation units were written in C, and talked to the C++ part over its exposed pure C interfaces. This was very much pain-free, but I certainly wouldn't want to try to use C++ code tightly intermixed with Pg backend-using code. It'd be a nightmare. These two paragraphs, suitably changed to be more like the rest of the docs, would be a great start for people interested in using C++. Would some short bits of sample code help? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Write-able CTEs, Update-able views, Hierarchical data, and optimistic locking
On Sat, May 29, 2010 at 08:21:46PM -0700, Richard Broersma wrote: On Sat, May 29, 2010 at 6:25 PM, David Fetter da...@fetter.org wrote: I wondering if write-able CTE's will be the silver bullet that will make rule based update-able views based multiple vertically partitioned table robust. By robust, I mean to elimination the update anomalies that can occur from the view point client side optimistic locking where the virtual row appears to be inconsistently updated. I'm not sure I understand. Sorry about that, unreadable text is was happens when I don't proof read before sending. When the concurrency issues in writeable CTEs get fixed, they could become a mechanism for doing what you describe, but I suspect there would be significant work involved in harnessing them to that task. Actually I wasn't aware of the concurrency issue of write-able CTE's. The concern, as I understand it, has to do with modifications to the current snapshot. I'm sure someone who knows the code better can go into more detail. Marko? The concern I have specifically relates to update-able views that were based upon joined tables (using these views was an attempt to hide the complexity of Generalization Hierarchies from the client side application). Updates to these kinds of views can give the appearance of non-atom updates on the view's virtual row. Also, if the view's reported row update count doesn't match what the client side software expects, the client automatically rolls back the transaction and reports a concurrent update error. However, when this happens some of the underlying rule's update statements were in fact processed, so the refreshed row in the view appears to have an non-atomic update even though the client rolls back the transaction. The following email was my first discovery that these kinds of update-able view were not get-along well with client side optimistic locking. http://archives.postgresql.org/pgsql-odbc/2006-12/msg00029.php I'm not trying to be obtuse, but I am not understanding how you connect this issue, which has to do with the way PostgreSQL's RULE system works, with writeable CTEs, which have approximately nothing in common with the issue except in that they, too, need to deal with the PostgreSQL RULE system, the fixing of which I have written about here: http://archives.postgresql.org/pgsql-hackers/2009-10/msg00249.php Please help me by making explicit the connection(s) you see between the writeable VIEWs and writeable CTEs, apart from that first word. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Write-able CTEs, Update-able views, Hierarchical data, and optimistic locking
On Sat, May 29, 2010 at 01:29:50PM -0700, Richard Broersma wrote: This might be a premature question considering write-able CTEs are not in core, but... I wondering if write-able CTE's will be the silver bullet that will make rule based update-able views based multiple vertically partitioned table robust. By robust, I mean to elimination the update anomalies that can occur from the view point client side optimistic locking where the virtual row appears to be inconsistently updated. I'm not sure I understand. When the concurrency issues in writeable CTEs get fixed, they could become a mechanism for doing what you describe, but I suspect there would be significant work involved in harnessing them to that task. They'll be pretty nice even without the automated view stuff, though :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Write-able CTEs, Update-able views, Hierarchical data, and optimistic locking
On Sat, May 29, 2010 at 09:38:30PM -0400, Bruce Momjian wrote: David Fetter wrote: On Sat, May 29, 2010 at 01:29:50PM -0700, Richard Broersma wrote: This might be a premature question considering write-able CTEs are not in core, but... I wondering if write-able CTE's will be the silver bullet that will make rule based update-able views based multiple vertically partitioned table robust. By robust, I mean to elimination the update anomalies that can occur from the view point client side optimistic locking where the virtual row appears to be inconsistently updated. I'm not sure I understand. When the concurrency issues in writeable CTEs get fixed, they could become a mechanism for doing what you describe, but I suspect there would be significant work involved in harnessing them to that task. They'll be pretty nice even without the automated view stuff, though :) If the user wants to submit it, fine, but neither Tom nor I are excited about it. Could you clarify what you mean by, it in the sentence above? At the developer meeting, we put Writeable CTEs as one of the achievable 9.1 targets, and Tom encouraged me to see that the patch gets fixed up and resubmitted for the first reviewfest, i.e. the middle of next month. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Moving from Mysql
On Sat, May 22, 2010 at 11:06:02PM -0400, Stephen Frost wrote: * Luis Daniel Lucio Quiroz (luis.daniel.lu...@gmail.com) wrote: 1. whar are equivalent for these commands: in mysql: mysqldump mydata_base_name pg_dump (pg_restore to restore from the dump, if you use a non-SQL format for it, which can give you the ability to do a parallel-restore) The way to call pg_dump so it goes to the most flexible format in pg_dump -Fc. You can find out more about this format in the man page, if you're using a unix-like system, or in the on-line docs: http://www.postgresql.org/docs/current/static/app-pgdump.html mysql mydata_base_name script.sql psql With the output of pg_dump -Fc, you'll be using pg_restore, which is more complex, but much more flexible. http://www.postgresql.org/docs/current/static/app-pgrestore.html 2. any link to read about how to admin pgsql with mysql backgraounds, The PG documentation is really quite good: http://www.postgresql.org/docs/8.4/ Here are a few more specific ones, some of which may apply to your situation: http://sql-info.de/mysql/gotchas.html http://www.raditha.com/mysql/mysql2pgsql.php http://www.in-nomine.org/~asmodai/mysql-to-pgsql.html http://blog.gtuhl.com/2010/04/15/not-a-fan-of-mysql/ http://pgfoundry.org/projects/mysql2pgsql/ http://search.cpan.org/search?query=SQL%3A%3ATranslatormode=all http://www.metatrontech.com/wpapers/mysql2postgresql.pdf http://www.data-conversions.net/products.php?prod_num=5dest=MENUID=200 http://pgfoundry.org/projects/mysqlcompat 3. how users are managed in pgsql, i need to create a specifiq username for db, but how? PG Roles (users and groups) are managed on a per-cluster level. There isn't a really good way to do them at a per-database level today. A cluster in PG is a full PG instance and a single cluster contains multiple databases. You can manage which databases users are allowed to connect to though, check out the GRANT command. Also check out host-based authentication, which you control with an external file called pg_hba.conf: http://www.postgresql.org/docs/current/static/client-authentication.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Alter column position
On Sat, May 22, 2010 at 09:34:50AM -0400, Patrick Rutkowski wrote: I'm curious, is there any latest word on this? (Note that I've read fully the link http://wiki.postgresql.org/wiki/Alter_column_position as well as all links stemming from it). No one's working on it, to my knowledge. While the SELECT * case doesn't interest me too much, it would be part of the infrastructure needed for PostgreSQL to optimize storage by placing all fixed-length columns before any variable-length ones. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [HACKERS] Retiring from the PostgreSQL core team
On Thu, May 13, 2010 at 12:24:47AM -0400, Jan Wieck wrote: To whom it may concern, this is to inform the PostgreSQL community of my retirement from my PostgreSQL core team position. Over the past years I have not been able to dedicate as much time to PostgreSQL as everyone would have liked. The main reason for that was that I was swamped with other work and private matters and simply didn't have time. I did follow the mailing lists but did not participate much. Looking at my publicly visible involvement over the last two years or so, there is little that would justify me being on the core team today. I was not involved in the release process, in patch reviewing, organizing and have contributed little. However, in contrast to other previous core team members, I do not plan to disappear. Very much to the contrary. I am right now picking up some things that have long been on my TODO wish list and Afilias is doubling down on the commitment to PostgreSQL and Slony. We can and should talk about that stuff next week at PGCon in Ottawa. I will also stay in close contact with the remaining core team members, many of whom have become very good friends over the past 15 years. The entire core team, me included, hoped that it wouldn't come to this and that I could have returned to active duty earlier. Things in my little sub universe didn't change as fast as we all hoped and we all think it is best now that I focus on getting back to speed and do some serious hacking. I hope to see many of you in Ottawa. You can run, but you can't hide ;) Thanks for your deep and broad contributions so far, and I'm sure I speak for many when I say we're looking forward to upcoming ones. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pulling data from a constraint def
On Thu, May 13, 2010 at 12:33:08AM -0400, Tom Lane wrote: Josh Kupershmidt schmi...@gmail.com writes: On Wed, May 12, 2010 at 12:58 AM, David Fetter da...@fetter.org wrote: If you've measured a performance issue for a table that tiny, it's a bug that needs fixing in PostgreSQL. �What measurements have you done so far? Just for fun, I tried it out myself. Here are the times I got on my modest laptop: CHECK constraint: * 500k INSERTs: 3.8 seconds * 500k UPDATEs: 6.0 seconds Foreign Key: * 500k INSERTs: 18.7 seconds * 500k UPDATEs: 21.2 seconds I'm surprised no one has yet suggested an ENUM type. I didn't suggest it because I didn't know about it, but because I've found ENUM to be a trap for the unwary. Very seldom are people absolutely certain that they'll have one particular list of things forever. The list may grow or shrink, or the order may change, and in those cases where the list changes somehow, ENUM causes more problems than it solves. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Pulling data from a constraint def
On Thu, May 13, 2010 at 08:08:31PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Thu, May 13, 2010 at 12:33:08AM -0400, Tom Lane wrote: I'm surprised no one has yet suggested an ENUM type. I didn't suggest it because I didn't know about it, but because I've found ENUM to be a trap for the unwary. Very seldom are people absolutely certain that they'll have one particular list of things forever. The list may grow or shrink, or the order may change, and in those cases where the list changes somehow, ENUM causes more problems than it solves. Well, the inability to change the list of values is certainly an unpleasant limitation, but is it so fatal that we should hide the feature from people who could possibly use it? I think not. It's enough of a foot-gun that I would not even mention it to start with, except to discuss its problems. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Inheritance efficiency
On Fri, Apr 30, 2010 at 08:44:26AM +0200, Vincenzo Romano wrote: Should I move to an enterprise grade version of PostgreSQL? The enterprise grade version of PostgreSQL is the community version. Proprietary forks exist, but they don't fix this kind of problem. :) Hmmm ... I think this is the kind of problems that keeps PostgreSQL away from the enterprise grade world. The ability to cope with thousands of DB objects like (child-)tables, indexes, functions and so on with O(1) or at least O(log(n)) complexity is among the key points. For example, the Linux kernel made the big jump with server hardware thanks also to the O(1) schedulers. In this specific case, if you think about inheritance for partitioning and you stick with the example idea of one partition per month, then the current solution is more than OK. In the real world, that is not really the general case, especially in the enterprise grade world, where maybe you partition with both a time stamp and another column, like product code ranges and prefixes ... Is there any planning about this improvement? Could it be possible to just make some changes (adding indexes) to the information schema to gain this enterprise gradeness? Your assertion that PostgreSQL is not enterprise grade is simply false. For years, it has been and continues to be used as the basis of extremely large mission-critical systems. That said, if you wish to make changes, or propose that some be made, please feel free to do so after 9.0 comes out. In the mean time, please test 9.0beta1 along with any ensuing betas and release candidates, and report back the results of the aforementioned testing. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Inheritance efficiency
On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote: No info about this point (partial indexes)? Is also this geared with linear algorithms ? Should I move to an enterprise grade version of PostgreSQL? The enterprise grade version of PostgreSQL is the community version. Proprietary forks exist, but they don't fix this kind of problem. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] partitioned lookup table?
On Fri, Apr 16, 2010 at 04:48:18PM +0200, Andreas Kretschmer wrote: Hi @all, A question, found in the german PG-Forum: is it possible to partitionate a lookup-table? What i mean is: test=# create table foo(i int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE test=*# create table bla ( i int references foo); CREATE TABLE test=*# insert into foo values (1); INSERT 0 1 test=*# insert into bla values (1); INSERT 0 1 test=*# create table foo_2 () inherits (foo); CREATE TABLE test=*# alter table foo_2 add primary key (i); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index foo_2_pkey for table foo_2 ALTER TABLE Kinda. http://people.planetpostgresql.org/dfetter/index.php?/archives/51-Partitioning-Is-Such-Sweet-Sorrow.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] autoscale cluster
On Wed, Apr 14, 2010 at 06:24:00PM +0200, Jesus arteche wrote: hi everyone, I'm looking for info about autoscale a cluster. Reassess this goal in the cold light of reason. First, find out what trade-offs people make in order to get this effect. In the unlikely event that, after finding out how the trick is accomplished, you still have this goal, you'll know how. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] best practice in archiving CDR data
On Mon, Mar 29, 2010 at 02:08:23PM +, Edgardo Portal wrote: On 2010-03-29, Juan Backson juanback...@gmail.com wrote: --0016e64ccb10fb54050482f07924 Content-Type: text/plain; charset=ISO-8859-1 Hi, I am using Postgres to store CDR data for voip switches. The data size quickly goes about a few TBs. What I would like to do is to be able to regularly archive the oldest data so only the most recent 6 months of data is available. All those old data will be stored in a format that can be retrieved back either into DB table or flat files. Does anyone know how should I go about doing that? Is there any existing tool that can already do that? FWIW, I partition by ISO week, use INSERT RULEs to route CDRs Just generally, triggers are much better than RULEs for this kind of thing. The underlying functions can be made quite efficient. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] text search in 8.1
On Mon, Feb 22, 2010 at 02:47:00PM +0600, AI Rumman wrote: Does Postgresql 8.1 support Full Text Search? If yes, please provide the link about documentation. It's available as an add-on, but since 8.1 is so close to its end of life, consider moving to 8.4 first, or if the project is out past Q3 of this year, to 9.0. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] GROUP BY column alias?
On Fri, Feb 19, 2010 at 12:07:42AM -0500, Tom Lane wrote: Lew no...@lwsc.ehost-services.com writes: Eric B. Ridge wrote: That explains it. Thanks. Breaks the rule of least surprise, but it is SQL. SQL:1999 and later use a slightly different definition which is not entirely upward compatible with SQL-92. In most cases, however, PostgreSQL will interpret an ORDER BY or GROUP BY expression the same way SQL:1999 does. The current SQL standard *supersedes* all previous ones. There isn't a hierarchy in the sense of higher levels of compliance that our docs implicitly and falsely assume in many spots, and we need to make them stop including this idea. The only standard actually worth citing today is SQL:2008, and the day the next one comes out, we need to change all our references to cite it. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Possible to set postgres in case insensitive mode ?
On Mon, Feb 01, 2010 at 07:35:45PM +0200, Moe wrote: On Sat, Jan 30, 2010 at 4:44 AM, Scott Marlowe scott.marl...@gmail.comwrote: On Fri, Jan 29, 2010 at 7:40 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Fri, Jan 29, 2010 at 2:52 PM, Moe mohamed5432154...@gmail.com wrote: Is it possible to set postgres in case insensitive mode ? If so, how? What part, exactly, do you want to be case insensitive? I assume you mean a text / varchar type? Look for citext, I believe it's a contrib module, until 9.0 is out, which will include it natively. It's here: http://pgfoundry.org/projects/citext/ But it doesn't work in 8.3 or 8.4, only 8.2 and before. So either run that or wait for 9.0 I guess. Sorry, I forgot I posted the message. What I mean was that a query select * from where email = ? could match on both upper and lower case emails, such myem...@hotmail.com or myem...@hotmail.com I know I can use the lower(...) function but this is not an option when using hibernate. MySql is by default case insensitive, I just figured there'd be an option to turn it on in PG as well. You can use citext, a supplied module. What about 9.0 ? How is that going to be offered ? When it's ready! :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] postgres external table
On Sun, Jan 17, 2010 at 07:27:34PM -0800, Amy Smith wrote: all is there a external table create method ( similar to oracle external table ) ? where to find the information ? There is a project on pgfoundry which has had some activity lately that's similar. You might also try DBI-Link. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] FOSDEM dinner
On Thu, Jan 14, 2010 at 09:51:34AM +0200, Dave Coventry wrote: Yes, I'm in South Africa, which might make it problematic! You're on the right land mass, assuming you count being able to cross the Suez canal on foot ;) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] replication from multiple master servers to a single read-only slave
On Mon, Jan 11, 2010 at 03:02:18PM -0800, Omar Mehmood wrote: I'm wondering if it's possible to have a setup with multiple master servers replicating to a single slave. I can guarantee that each server will generate unique PK values for all tables and all the data is partitioned (logically by server) across the servers. I would simply like to have a read-only slave that is a picture of all the servers' data (relatively up to date). The individual master servers never need to know about each other's data (i.e. they do not _need_ to sync with each other, nor do I want them to be sync'd). Would it be possible to use PostgreSQL PITR feature to support this functionality ? No, but you could use something like Slony to do this. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 psql source code can be protected?
On Wed, Jan 06, 2010 at 05:09:06PM +0100, Marius Pitigoi wrote: Hello, Is there a way to protect psql source code? For example oracle has wrap utility. I want to deploy my DB on a hosting company server. But they can see my functions code (they have root privileges) and this is what I want to avoid. The appropriate place to protect your IP is in legal agreements. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] set-level update fails with unique constraint violation
On Sun, Jan 03, 2010 at 10:16:10AM +0100, Roman Neuhauser wrote: # scott.marl...@gmail.com / 2010-01-02 11:23:24 -0700: On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser neuhau...@sigpipe.cz wrote: # da...@fetter.org / 2009-12-31 08:04:58 -0800: On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql@sigpipe.cz wrote: Hello, this fails with duplicate key value: CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? This will work in 8.5: CREATE TABLE x ( i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; thanks, this might be a bearable workaround in some cases provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE. what I really want is a mode that fires the constraint check at the end of the statement. What advantage would there be to a constraint that fires right after to one that fires at the end of the transaction? What? I didn't say that. I'm saying that I want IMMEDIATE constraint that is atomic with regard to the statement. It's obvious that UPDATE x SET i = i + 1 cannot break a UNIQUE constraint on x.i lest the constraint checking is not atomic. I can see how such non-atomic checking can be good performance-wise, but I'm more interested in logical correctness. At least one of us hasn't understood the situation. :) There is a problem in all released versions of PostgreSQL where, when you issue that UPDATE, it is checked at each row. If at any given row, the UDPATE causes a conflict, the statement fails, even though the whole UPDATE would have succeeded if it had completed. The DEFERRED uniqueness constraints in 8.5alpha3 fix this problem. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
