Re: [GENERAL] When to use cascading deletes?

On 06/11/2009 11:33 AM, Eric Schwarzenbach wrote: My rule of thumb for when to use to not use cascading deletes is this: If the what the record represents can essentially be thought of a "part of" what the record that it references represents, I use cascading deletes. If what the record represen

Re: [GENERAL] PGSQL or other DB?

Scott Marlowe wrote: On Sat, Jan 31, 2009 at 2:13 AM, Erik Jones wrote: On Jan 30, 2009, at 11:37 AM, durumdara wrote: - I can add/modify a table, or a field to a table without "full lock" on the table (like DBISAM restructure). Like in FireBird, where the "add field" change only the table

Re: [GENERAL] Performance of views

Tom Lane wrote: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: >> Note that, at least in older versions, MySQL completely materialized a >> temporary table from a view, then used that for the view. This is >> horribly inefficient, and results in a lot of people thinking views >> are slow. Not sure

Re: [GENERAL] MySQL LAST_INSERT_ID() to Postgres

Masis, Alexander (US SSA) wrote: > >"SELECT CURRVAL( > pg_get_serial_sequence('my_tbl_name','id_col_name'));" > Any reason why you can't just do this? CREATE FUNCTION last_insert_id() RETURNS bigint AS $$ SELECT lastval(); $$ LANGUAGE SQL VOLATILE; -- Sent via pgsql-general mailing list

Re: [GENERAL] TODO list and "hyphen"

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: > Scara Maccai wrote: >> Hi, >> >> I was looking at the TODO: >> >> http://www.postgresql.org/docs/faqs.TODO.html >> >> >> "A hyphen, "-", marks changes that will appear in the upcoming 8.4 release." >> >> Well, making a search for

Re: [GENERAL] Sun acquires MySQL

Merlin Moncure wrote: > On Jan 21, 2008 2:47 AM, Alex Turner <[EMAIL PROTECTED]> wrote: > In other words, pl/pgsql's speed disadvantages only matter if a > significant portion of the work is in doing things other than > executing queries and handling the results. Certainly, these types of > proble

[GENERAL] Sun acquires MySQL

http://blogs.mysql.com/kaj/sun-acquires-mysql.html/ What does this mean for Sun's support of Postgres? -- Russ. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Transactional DDL

Harpreet Dhaliwal wrote: > > > On 6/2/07, *Jasbinder Singh Bali* <[EMAIL PROTECTED] > > wrote: > > > > On 6/2/07, *Michael Glaesemann* < [EMAIL PROTECTED] > > wrote: > > > On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

Re: [GENERAL] Preferred Installation/Data Directories

Rich Shepard wrote: > On Fri, 27 Apr 2007, Ron Johnson wrote: > >> Why put "active" data under /usr? Ever since user data was moved to >> home, >> /usr has only had system stuff in it. Or is that still a viable BSDism? > > Good question, Ron! But, in a multiuser system with many users of the

Re: [GENERAL] PostgreSQL primary (sequence) key issue (Ruby/Rails)

Joshua D. Drake wrote: On 01/03/07, Andrew Madu < [EMAIL PROTECTED]> wrote: Hi Dave, my apologies for contacting you off list but i'm having a spot of bother with postgreSQL sequence setup in rails. In addition to what is mentioned below, I have place the following line of code in my The defin

Re: [GENERAL] postgresql vs mysql

Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 My definition is, "toy used/trumpeted by pseudo-professionals as a professional tool, when it just doesn't measure up". /me Tries really hard to resist responding /me Fails I'm sorry, but having just been described as a 'pseud

Re: [GENERAL] postgresql vs mysql

This can (I discovered yesterday) be fixed by adding ONLY_FULL_GROUP_BY to the sql_mode setting. As Ron mentioned though that can be happily overridden on a per-session basis so it's not as 'strict' as it makes out... Chad Wagner wrote: On 2/20/07, *gustavo halperin* <[EMAIL PROTECTED]

Re: [GENERAL] How I can read-back a serial value just inserted?

Robert Treat wrote: On Thursday 18 January 2007 00:57, Adam Rich wrote: If it's a recent PG: select lastval() See this: http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html probably more important that you see this: http://people.planetpostgresql.org/xzilla/index.php?/arc

Re: [GENERAL] MySQL drops support for most distributions

John D. Burger wrote: The good thing is that there are several companies supporting Postgres, so whatever one of them does it does not affect the market as a whole. Surely there are also third-party companies that provide "support" for MySqueal in some similar sense? There probably are, b

Re: [GENERAL] Performance figures from DbMail list

Jeff Davis wrote: On Fri, 2006-12-08 at 22:04 +0100, Mikael Carneholm wrote: This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free "database" in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone

Re: [GENERAL] FK locking still too strong

Csaba Nagy wrote: Hi all, While postgres 8.x improved a lot the locking issues related to foreign keys, the locking used is still stronger than needed. The following test case deadlocks on postgres but goes through on oracle: preparation of tables: create table test1(col_fk integer primary

Re: [GENERAL] Simple OUTER JOIN doubt

Sandro Dentella wrote: > On Fri, Oct 27, 2006 at 02:42:06PM -0500, Russ Brown wrote: >> Looks to me like it is because you're referencing vota_punteggio in the >> WHERE clause. >> >> Try something like this: >> >>SELECT f.id, f.titolo, p.voto >

Re: [GENERAL] Simple OUTER JOIN doubt

Sandro Dentella wrote: > I've a very simple task. I thought I knew how to solve it but there's > something I'm surely missing. > > I got film and scores for the film. In table "film" and "punteggio" > (score). I want a join returning all the films and the votes > expressed by the user(s). If the

Re: [GENERAL] Optimising a query requiring seqscans=0

On Thu, 2006-09-21 at 23:39 -0400, Jim Nasby wrote: > On Sep 14, 2006, at 11:15 AM, Russ Brown wrote: > > We recently upgraded our trac backend from sqlite to postgres, and I > > decided to have a little fun and write some reports that delve into > > trac's subversion c

[GENERAL] Optimising a query requiring seqscans=0

Hi, We recently upgraded our trac backend from sqlite to postgres, and I decided to have a little fun and write some reports that delve into trac's subversion cache, and got stuck with a query optimisation problem. Table revision contains 2800+ rows Table node_change contains 37+. rev is a '

Re: [GENERAL] Friendly catalog views

On Mon, 2006-05-15 at 17:12 -0500, Jim C. Nasby wrote: > On Wed, May 10, 2006 at 02:08:46PM -0300, Bruno Almeida do Lago wrote: > > Sometime ago I saw a project with the purpose of creating Oracle views over > > the PostgreSQL catalog. > > > > I can't remember/find it anymore. Do you know if it's

Re: [GENERAL] GUI Interface

On Fri, 2006-05-12 at 07:31 -0500, Tony Caduto wrote: > Dave Page wrote: > > Only runs on Windows though doesn't it? > > > > Regards, Dave > > > > > Hardly a limitation since Linux and other Unix based operating systems > account for a trivial percent of the desktop market. I would guess that

Re: [GENERAL] GUI Interface

On Sat, 2006-05-13 at 17:27 +0100, Dave Page wrote: > > > Why don't you just drop win32 support and leave the commercial > market > > to us proprietary coders :-) Seriously, there are now more than > enough > > commercial admin tools available to support the PG windows market. I can't find th

Re: [GENERAL] Syntax problem for a newbie

On Fri, 2006-05-05 at 12:49 -0400, Tom Lane wrote: > "Fred" <[EMAIL PROTECTED]> writes: > > I have a syntax problem but I don't find the clue. > > Actually I adapt an mySQL query to a postgreSQL but I got a message > > error that I can't interpret. > > > SELECT g.id, t1.name, substring(g.path, 1,

Re: [GENERAL] Advantages of PostgreSQL over MySQL 5.0

On Wed, 2006-03-22 at 16:36 -0500, Merlin Moncure wrote: > Chis Browne wrote: > > PostgreSQL is likely to be way slower if you submit streams of little > > queries, each an independent transaction... > > When I get around to it I plan on debunking this ;). I recently did > extensive internal benc

Re: [GENERAL] Benchmark-Comparison PostGreSQL vs. SQL Server

On Thu, 25 Nov 2004 11:36:33 +0100 "Robert Soeding" <[EMAIL PROTECTED]> wrote: > Hi, this is my first question here, and also, it's somewhat delicate. > So please be patient. > My question is, CAN PostGreSQL perform in the SQL Server area when it > comes to speed? In other words, are there explan

Re: [GENERAL] Special offer with a possible dontation to the

On Wed, 2006-03-01 at 13:44 -0600, Tony Caduto wrote: > As a special promotion we are offering PG Lightning Admin for the blow > out price of 5 dollars per copy. > > Our goal is to reach 1000 sales by the first day of spring, and 1 > dollar per sale will go to the Postgresql project, but only if w

Re: [GENERAL] How to find release notes

On Thu, 02 Feb 2006 16:15:43 + Adam Witney <[EMAIL PROTECTED]> wrote: > On 2/2/06 4:06 pm, "Russ Brown" <[EMAIL PROTECTED]> wrote: > > > Ah, there is it. Thanks! Just me not looking hard enough. > > > > However, I would argue that I shouldn&#x

[GENERAL] How to find release notes

Hi, I'm looking at www.postgrsql.org and wondering how to find the release notes for 8.1.2. I can see the link for the 8.1.2 release, but that just links to a directory of the release tarballs. Actually, all that would be needed here would be a text file in that directory containing the release n

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, 5 Jan 2006 22:25:21 +0100 Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Am Donnerstag, 5. Januar 2006 21:58 schrieb Scott Marlowe: > > But it's not consistent. Imagine we do the one where we take one > > from peter and give it to paul. If paul's account is stored in an > > int, and is at

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, 5 Jan 2006 15:11:49 -0500 Jaime Casanova <[EMAIL PROTECTED]> wrote: > On 1/5/06, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: > > > > > > [EMAIL PROTECTED] wrote on 01/05/2006 01:59:52 PM: > > > > > so the problem is that MySQL _forces_ a consistent state but in > > > the process it vio

Re: [GENERAL] 'Official' definition of ACID compliance?

On Thu, 05 Jan 2006 08:22:01 -0800 Steve Crawford <[EMAIL PROTECTED]> wrote: > Alan Garrison wrote: > > Russ Brown wrote: > >> Does anyone know where I can find the 'official' definition of > >> what it meant by ACID compliance? > >> > >

[GENERAL] 'Official' definition of ACID compliance?

Does anyone know where I can find the 'official' definition of what it meant by ACID compliance? We're having a discussion about it that we could do with resolving. In particular, the key point is what it meant by the 'C' part. I maintain that MySQL is not ACID compliant because it will (among oth

Re: [GENERAL] DBlink documentation

On Sat, 17 Dec 2005 08:21:29 -0600, Douglas McNaught <[EMAIL PROTECTED]> wrote: Andre Truter <[EMAIL PROTECTED]> writes: On Sat, 2005-12-17 at 07:04 -0700, Leonel Nunez wrote: download the source for your PostgreSQL version unpackit and check onthe contrib/dblink dir Yes, th

Re: [GENERAL] is this a bug or I am blind?

On Fri, 16 Dec 2005 11:59:48 -0600, Csaba Nagy <[EMAIL PROTECTED]> wrote: On Fri, 2005-12-16 at 18:52, Tom Lane wrote: Csaba Nagy <[EMAIL PROTECTED]> writes: > ... So "tyty" and "tty" could be arguably both taken as double "ty", > except that the official form is "tty"... but from a pronunciati

[GENERAL] Congratulations on 8.1

I'd just like to congratulate the team on the excellent job that they've done on Postgresql 8.1. Yet again I'm amazed by the progress made in each and every major release of this product I've seen since I started using it (around version 7.0). To single out the changes that jump out at me t

Re: [GENERAL] PostgreSQL's bug tracker

On Mon, 10 Oct 2005 23:23:45 -0700, Tino Wildenhain <[EMAIL PROTECTED]> wrote: But trac (http://www.edgewall.com/trac/ ) does support it. I'm running it for quite a time and very much like it. Of course, trac is SVN only, so this might be a show stopper unfortunately :( Regards Tino -

Re: [GENERAL] Replication

Scott Marlowe wrote: On Fri, 2005-09-16 at 12:51, Russ Brown wrote: Scott Ribe wrote: How about a third choice: you can also use a proven, reliable and tested replication solution that is included in the core system because the core system basiclly provides it anyway. Sure, but that one

Re: [GENERAL] Replication

Scott Ribe wrote: How about a third choice: you can also use a proven, reliable and tested replication solution that is included in the core system because the core system basiclly provides it anyway. Sure, but that one is spelled "Sybase", not "MySQL" ;-) It's amazing how misunderstood m

Re: [GENERAL] Replication

Scott Marlowe wrote: I don't know, but someone in your organization seems to be. Not just my organisation. I'm afraid this is an opinion I've seen in many places. Let me present it as a simple devil's choice, which would you rather have, proven replication, that works, but requires you to

Re: [GENERAL] Replication

Simon Riggs wrote: Barry, You can use PITR to archive transaction logs to a second server that is kept in standby mode. This will cope with any number of tables and cope with dynamic changes to tables. This is fairly straightforward and very low overhead. Set archive_command to a program that

Re: [GENERAL] Quotation marks in queries

Tony Caduto wrote: > The easiest solution is just not to use caps or spaces in your > table/object names, there is no advantage to doing so. > People just need to get over the fact that having caps in a name make it > easier to read. > > My Test Table should be my_test_table, the naming makes no

Re: [GENERAL] Quotation marks in queries

How difficult would it be to add a configuration option (at the global, database or session level) to make PostgreSQL transparently quote identifiers for you? That would be a simple way to allow users to use case-sensitive names without quoting everything manually or changing deep parts of the back

Re: [GENERAL] COnsidering a move away from Postgres

Tony Caduto wrote: > There are very nice inexpensive alternatives to PG Admin III. Because > of it's cross platform nature it is severly lacking in many areas. > In what way does making software cross-plaform cause it to be 'lacking in many areas'? > Check out PG Lightning Admin at: > http://ww

Re: [GENERAL] PostgreSQL sequence within function

Tony Caduto wrote: > All you where really mising was a semi colon afer nextval('myseq') and > the begin end. > > CREATE or REPLACE FUNCTION getSeq() > RETURNS int AS > $$ > begin > RETURN nextval('myseq'); > end; > $$ > LANGUAGE 'plpgsql'; > > Clark Allan wrote: > This just made me think. If I

Re: [GENERAL] Version Control?

John DeSoi wrote: Russ, On Jun 10, 2005, at 2:34 PM, Russ Brown wrote: This certainly is a far more complex problem than I originally thought it was. I'd like any solution to be able to work in any version control system, and to be applicable to any database engine (my employer is a

Re: [GENERAL] Version Control?

Peter Fein wrote: Come on, we can't be the first people with this problem? Version control's been around for what, 20, 30 years? Somebody must have tried to do this before, even if it's with something other than Postgres... That's what really surprises me too! I've looked around quite a bit

Re: [GENERAL] Version Control?

John DeSoi wrote: On Jun 10, 2005, at 11:38 AM, Peter Fein wrote: This would obviously have to be pretty damn clever. Amongs the difficulties would be ensuring that the patch applies changes in the correct order (e.g. add column before adding foreign key). It's hard, but I don't believe it's

Re: [GENERAL] Version Control?

On 6/9/05, elein <[EMAIL PROTECTED]> wrote: > On Thu, Jun 09, 2005 at 04:16:46PM -0500, John Browne wrote: > > How would you handle the migration of the data with these user > > scripts? Dump it to a temp table? > > > > If your scripts are correct, you should be able to load > your base scripts a

Re: [GENERAL] Version Control?

Thomas Kellerer wrote: Russ Brown wrote on 09.06.2005 23:12: Currently we just store a dump of the data structure. However, what I think is really needed is a specialist diff tool which works out the commands needed to move from one schema to another. That would be *extremely* useful, but

Re: [GENERAL] Version Control?

Peter Fein wrote: Hi- Any general tips on using version control (CVS, SVN) while doing database design? My thought was to do a text-mode dump (including populated code tables) from PGAdmin. How do people do this? Currently we just store a dump of the data structure. However, what I think is

[GENERAL] Fyracle?

I've just been sent this link by our DBA (pro MySQL but even more Anti-Postgres): http://www.janus-software.com/fb_fyracle.html I want to know how they've calculated the numbers that they're created those pie graphs from. The only thing I can think of that Postgres doesn't have for 'transactions'

Re: [GENERAL] PG Lightning Admin running on Linux via WINE 2005524

On 6/3/05, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Tony Caduto wrote: > > Can't do that, it's a Delphi 7 application. > > Have you heard of Kylix? You should be able to do an almost straight > native port to Linux. > I didn't get chance to try it at home under Wine, but a pative port using

Re: [GENERAL] PG Lightning Admin running on Linux via WINE 2005524

Just tried it here at work with crossover office 3.0.0 and it was far too slow to be usable (plus I had issues with text boxes showing through to the window below). I'll give it a go at home this weekend with a more recent version of Wine and see if that improves things (I do actually want to be a

Re: [GENERAL] writting a large store procedure

Tony Caduto wrote: Sean, I am tooting my own horn here, but I would recomend PG Lightning Admin. It has a incredible function editor (and query editor) that is based on Synedit(synedit.sourceforge.net), and has full code completion that includes all the Postgres built in functions,exception nam

Re: [GENERAL] [ODBC] Adventures with P2P and Scripts in Windows

Jeff Eckermann wrote: --- [EMAIL PROTECTED] wrote: I DID Succeed in doing the sort of things I want in VBA script in MSAccess, but that does me no good at work because we cant get the money to purchase copies of Access. I own a copy of Visual Basic, and if I could find a good example of

Re: [GENERAL] [HACKERS] plPHP in core?

Scott Marlowe wrote: What I would much prefer is a matrix that shows all the features a PL should / could have, and which PLs have those features, which features are currently being implemented, who maintains them if they are maintained, if they aren't maintained, etc.. So that when it comes time

Re: [GENERAL] PostgreSQL and .NET

Thomas Hallgren wrote: Pavel Stehule wrote: Hello maybe http://gborg.postgresql.org/project/plmono/projdisplay.php but I am not know more Judging from the CVS, that project hasn't had any activity at all the last 14 months or so. Is it still active? Has it been moved? I've had a browse of the 4 e

Re: [GENERAL] PostgreSQL and .NET

Tony Caduto wrote: I think he means PL C# I thought I saw a project started for PL C# or PL Mono on Gborg. plmono sounds very interesting. It would basically mean you could program procedures in any language that has a .NET bytecode compiler, including C#, C++, VB(!) and I believe java. I wouldn'

Re: [GENERAL] Group By and wildcards...

Jon Lapham wrote: Ugh. Since I do not want to have to re-write all my aggregate function containing queries upon modifications to the table definitions (and I do not want to write multi-thousand character long SELECT statements), maybe it is easier to use a temp table intermediary? Ugly... ugl

Re: [GENERAL] regular expressions in query

elein wrote: No doubt someone more adept at perl can write this function as a one-liner. create or replace function just_digits(text) returns text as $$ my $innum = $_[0]; $innum =~ s/\D//g; return $innum; $$ language 'plperl' SELECT telephone FROM addresses WHE

Re: RES: [GENERAL] NewsForge Poll: Favorite open source database?

Issues with the link aside, there's an interesting comment entitled "MySQL has the better license", which reads: Someday PostgreSQL might be "embraced and extended" by a predatory company that will turn it into a closed product, because it uses the (defective IMHO) BSD license. That can't happen w

Re: [GENERAL] PG8 final when

On Mon, 20 Dec 2004 14:13:40 +0100, Együd Csaba <[EMAIL PROTECTED]> wrote: > Hi, > as far as I can remember I somewhere read an article - maybe somwhere on a > dbforum or the postgresql.org - which stated that after the RC1 the final > version is required to be announced at around 15. dec. May be I

Re: [GENERAL] how to edit a function from psql?

What would be really useful would be a command that would return the actual SQL needed to create a function. At present the output from \df+ needs to be pieced together to create a new CREATE OR REPLACE string. A command that returnes that string for you would allow you to copy and paste it in, do

[GENERAL]

with the OUTER JOIN syntax)? I am not an expert, but this is what I recall from following the list. Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL

[GENERAL] Join efficiency

Hello all, Recently a post on this list made me think a bit about the way in which I write my queries. I have always written queries with ordinary joins in this manner: SELECT * FROM a, b WHERE a.x=b.x; However I recently saw an laternative syntax: SELECT * FROM a JOIN b ON a.x=b.x; Is there any

Re: [GENERAL] INSERTS and Queries

On Mon, 01 Mar 2004 13:36:35 +, C G <[EMAIL PROTECTED]> wrote: Dear All, I can use insert with 'select' if I do this INSERT INTO TABLE t1 (col1) SELECT stuff FROM t2; But I want to insert more than one thing into the table, e.g. INSERT INTO TABLE t1 (col1,col2) SELECT stuff FROM t2 , 100;

Re: [GENERAL] windows distribution

On Fri, 19 Dec 2003 12:11:51 -0800 (PST), Jeff Eckermann <[EMAIL PROTECTED]> wrote: --- Nikola Skoric <[EMAIL PROTECTED]> wrote: Is there windows distribution of PostgreSQL? I recieve contradictory information :-) Some people say there is, some there isn't. So, is there? I've been browsing www.po

Re: [GENERAL] pltcl problem

On Wed, 24 Sep 2003 15:02:49 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: Russ Brown <[EMAIL PROTECTED]> writes: Actually, I have done something stupid. I'm basically running the error log through a grep for DEBUG, and what I'm seeing is chunks of the function create being d

Re: [GENERAL] pltcl problem

Can we see the whole function definition? I think you are somehow managing to include this string in a quoted argument of some other elog command, but without context it's hard to say more. regards, tom lane Actually, I have done something stupid. I'm basically running the error log through a

Re: [GENERAL] pltcl problem

Can we see the whole function definition? I think you are somehow managing to include this string in a quoted argument of some other elog command, but without context it's hard to say more. regards, tom lane I thought that too but I can't see anything obvious (but then I am a tcl novice :-). T

[GENERAL] Difficulty with quotes

Hi, I'm trying to write a very simple SQL function, but I'm having problems with it. My function is as follows: CREATE OR REPLACE FUNCTION fnCPUpdateVolunteerType(int, text) RETURNS INTEGER AS ' UPDATE vblVolunteerType SET vchDisplayName = ''$2'' WHERE intVolunteerTypeID= $1;