Re: [GENERAL] Adding ON UPDATE CASCADE to an existing foreign key

Stephan Szabo wrote: On Thu, 4 May 2006, Rich Doughty wrote: I have a foreign key constraint that I'd like to alter. I'd rather not drop and re-create it due to the size of the table involved. All I need to do is add an ON UPDATE CASCADE. Is it ok to set confupdtype to 'c&#x

[GENERAL] Adding ON UPDATE CASCADE to an existing foreign key constraint

r is it safer to drop and recreate the constraint? PG Version 8.0.3 Thanks a lot - Rich Doughty ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] How do I prevent binding to TCP/IP port outside of

--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- - Rich Doughty ---(end of broadcast)

Re: [GENERAL] verifying database integrity - fsck for pg?

bout a row-by-row comparison between the source and the copy. It's rather a case of a tool to check the datafiles' integrity (such as fsck, myisamchk, svnadmin verify etc). If the fact that pg_dumpall returned successfully, then i would hope that all the data is present and correc

Re: [GENERAL] verifying database integrity - fsck for pg?

Tom Lane wrote: Rich Doughty <[EMAIL PROTECTED]> writes: We are currently migrating a cluster between hosts. I'd like to verify that the new database has been transferred reliably and that the datafiles are in tact. pg_dump both databases and diff the results, perhaps? i had

[GENERAL] verifying database integrity - fsck for pg?

ion 8.0 Thanks, - Rich Doughty ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] View with an outer join - is there any way to optimise

Tom Lane wrote: Rich Doughty <[EMAIL PROTECTED]> writes: I have a view vw_tokens defined as ... I cannot however perform a meaningful join against this view. ... PG forms the full output of the view. You seem to be wishing that PG would push the INNER JOIN down inside the nested LEFT

Re: [GENERAL] View with an outer join - is there any way to optimise

urning function, or just write the queries manually. -- - Rich Doughty ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message

Re: [GENERAL] View with an outer join - is there any way to optimise

string comparison optimization, but I generally avoid string comparisons when I am dealing with a few known values, as would be the case in a status table. interesting thought. of course, i'd rather postgres didn't do a full sequential scan ;-) Rich Doughty wrote: I ha

[GENERAL] View with an outer join - is there any way to optimise this?

nd: ((sarreport_id)::integer = 9) Setting enable_mergejoin and enable_hashjoin to off results in a nested but still forms the view output. I can achieve the results i need be eliminating the view and writing the query manually but for various reasons i'd prefer to query a view. Any advice is greatly appreciated. i'm starting to wonder if the using a view in this instance is futile. Many thanks -- - Rich Doughty ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [GENERAL] massive performance hit when using "Limit 1"

Rich Doughty wrote: Richard Huxton wrote: Rich Doughty wrote: [snip] Try the same query but with a low retailer_id (100 or something) and see if it goes a lot quicker. If that is what the problem is, try changing the ORDER BY to something like "_s.retailer_id, _t.value, _t.number

Re: [GENERAL] massive performance hit when using "Limit 1"

Richard Huxton wrote: Rich Doughty wrote: This one goes nuts and doesn't return. is there any way i can force a query plan similar to the one above? EXPLAIN SELECT _t.* FROM tokens.ta_tokens _t INNER JOIN tokens.ta_tokens_stock _s ON _t.token_id = _s.token_id

Re: [GENERAL] massive performance hit when using "Limit 1"

Rich Doughty wrote: can anyone explain the reason for the difference in the following 2 query plans, or offer any advice? the two queries are identical apart from the limit clause. [snip] fwiw, join order makes no difference here either. i get a slightly different plan, but with LIMIT 1

[GENERAL] massive performance hit when using "Limit 1"

- PostgreSQL 8.0.3 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.2 20050821 (prerelease) (Debian 4.0.1-6) Thanks a lot, - Rich Doughty ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [GENERAL] Outer join query plans and performance

Rich Doughty wrote: I'm having some significant performance problems with left join. Can anyone give me any pointers as to why the following 2 query plans are so different? [snip] knew i'd forgotten something... select version();

[GENERAL] Outer join query plans and performance

ined on h1, but it isn't. It only joins on t. can anyone give any tips on improving the performance of the second query (aside from changing the join order manually)? Thanks -- - Rich Doughty ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] perl and insert

ry this (although the performance gains depend on database you're using) my $st = $dbh->prepare ('INSERT INTO table (line) VALUES (?)'); while (my $line = ) { $st->execute ($line); } - Rich Doughty ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Increasing the length of a varchar domain

thread on this a few months back, but can't seem to find it anymore. PG version 7.4.2 (I know, needs updating...) Any advice? - Rich Doughty ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org