Re: [GENERAL] Deadlocks caused by referential integrity checks

> "SS" == Stephan Szabo <[EMAIL PROTECTED]> writes: SS> If transaction 1 inserts a child row that references row A, then SS> transaction 2 does a child row that references row B and they both then go SS> to do child rows that reference the other, in the current implementation, SS> there's no w

Re: [GENERAL] performance of IN (subquery)

Greg Stark <[EMAIL PROTECTED]> writes: > It's orthogonal. My point was that I have a bigger problem, but even if I > address it by switching away from plpgsql, or I guess by using EXECUTE, I > would still have a problem. I didn't realize you could run analyze in a > transaction, but even being able

Re: [GENERAL] How is this possible? (more on deadlocks)

> "CM" == Carlos Moreno <[EMAIL PROTECTED]> writes: CM> Ok, now I'm really intrigued by what looks to me CM> (possibly from a naive point of view) like a bug, CM> or rather, a limitation on the implementation. [[ ... ]] CM> I don't know about the internals of how transactions CM> and locks an

Re: [GENERAL] help with trigger

Maybe I've missed it but can someone plese help me with this? Brgds and thanks in advance, --- Dino Vliet <[EMAIL PROTECTED]> wrote: > Hi folks, > I'm new to PostgreSQL and am busy tring to work with > it. Of pl/pgsql I know even less and that's the part > I > have a question on right now. I hav

[GENERAL] pg_autovacuum start-script

I'm about to try to implement a simple pg_autovacuum script that can be used in conjunction with or integrated entirely with the contrib start-scripts for postgres. I just want to check that what I'm doing has the appropriate sanity checks. The behavior I'm considering is: if pg_ctl status retu

Re: R: R: [GENERAL] space taken by a row & compressed data

Joe Conway <[EMAIL PROTECTED]> writes: > I don't know of a builtin way to do that from SQL, but the attached seems to > work for me. Cool. Thank you. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister

Re: [GENERAL] performance of IN (subquery)

Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > I'm not about to run analyze in the middle of the data generation > > (which wouldn't work anyways since it's in a transaction). > > Since 7.3 or 7.4, you *can* run ANALYZE in the middle of a transaction. > The cach

[GENERAL] job for sql, pl/pgsql,gawk,perl or ??

Dear all, I have this problem with getting information out of my database and I don't know if it is my knowledge of sql or that this is something that can't be done in sql. I have the following table: id fdateprod pricestat nr_items sdate x1 23-11-2003123 456 yes 7

Re: [GENERAL] performance of IN (subquery)

Paul Tillotson wrote: If pgautovacuum does ANALYZE and we can get most people to use pgautovacuum, I think this problem will go away. It does. We won't get most people to use it for 8.0 since it is still a contrib project, but hopefully it will be built-in for 8.1. ---(e

Re: [GENERAL] performance of IN (subquery)

Joseph Shraibman <[EMAIL PROTECTED]> writes: > Markus Bertheau wrote: >> It re-plans the query at every run. > According to the docs is specifically doesn't. You're confusing SQL EXECUTE statement with the quite different plpgsql EXECUTE statement ... regards, tom lane -

Re: [GENERAL] Strange difference in query execution time

Jeremy Jongsma <[EMAIL PROTECTED]> writes: > My questions are: > 1) How can #3 take 30 times as long as #2, given that in my databse they > have the exact same number of rows returned and are drawn from the exact > same tables? > 2) How is it possible for #3, a subset of #1 with a very simple WH

Re: [GENERAL] performance of IN (subquery)

According to the docs is specifically doesn't. http://www.postgresql.org/docs/7.4/static/sql-prepare.html When the PREPARE statement is executed, the specified statement is parsed, rewritten, and planned. When an EXECUTE command is subsequently issued, the prepared statement need only be executed

Re: [GENERAL] Stored procedure failure

Michal Hlavac <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I can't reproduce a crash here, but perhaps that's because you have not >> supplied any sample data... > I attached file with data, where it fails... Thanks. It turns out the main reason I couldn't reproduce it was I was testing in C

Re: [GENERAL] performance of IN (subquery)

Title: RE: [GENERAL] performance of IN (subquery) Have you thought about using existence checking:  WHERE EXISTS (SELECT '1' FROM FOO2 WHERE BAZ = BAZ2) If the index exists on BAZ2 you might get away with a quick index only check. Duane -Original Message- From: Kevin Murphy [mail

Re: R: R: [GENERAL] space taken by a row & compressed data

Joe Conway wrote: Greg Stark wrote: How do you check to see how many records, or ideally which records, are being toasted and/or stored externally? I don't know of a builtin way to do that from SQL, but the attached seems to work for me. FWIW, this version has a bit more robust type checking. T

Re: [GENERAL] performance of IN (subquery)

On Fri, 27 Aug 2004 11:09:26 -0400, Joseph Shraibman <[EMAIL PROTECTED]> wrote: > How does EXECUTE solve the cached-plan business? It re-plans the query at every run. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't

Re: [GENERAL] upgrading minor versions

thanks for the heads up about the pg_config stuff... I wondered about the difference.I have several boxes that were installed via ports (which have the binaries reside in /usr/local/pgsql) and some hand installed from the tar (which are in usr/local/bin). At least that explains why I have

Re: R: R: [GENERAL] space taken by a row & compressed data

Greg Stark wrote: How do you check to see how many records, or ideally which records, are being toasted and/or stored externally? I don't know of a builtin way to do that from SQL, but the attached seems to work for me. Joe checktoast.tar.gz Description: GNU Zip compressed data --

Re: [GENERAL] upgrading minor versions

On 8/27/2004 12:44 PM, Jeff Amiel wrote: Ok..still no dice. still shows 7.4.2 when I select version()... I must surely be doing something silly here... You try to install 7.4.5 from souces over the FreeBSD Port build. The problem is that the options to configure need to be the same. You can s

Re: [GENERAL] upgrading minor versions

Duh. I never restarted the server. sheesh. thanks to all. Jeff Doug McNaught wrote: Did you shut down and restart the server after the upgrade? Are you sure the new binaries went where you think they did? -Doug ---(end of broadcast)--- TIP 4: Do

Re: [GENERAL] upgrading minor versions

Jeff Amiel <[EMAIL PROTECTED]> writes: > So I downloaded postgresql-7.4.5.tar.gz > > uncompressed... > configure... > make > make install > > I run psql and it gives me the" Welcome to psql 7.4.5, the PostgreSQL > intera

Re: [GENERAL] upgrading minor versions

Ok..still no dice. still shows 7.4.2 when I select version()... I must surely be doing something silly here... Jan Wieck wrote: On 8/27/2004 12:21 PM, Jeff Amiel wrote: This may sound like a silly question I am currently running 7.4.2. I would like to upgrade to 7.4.5... So I downloaded pos

Re: [GENERAL] upgrading minor versions

On 8/27/2004 12:21 PM, Jeff Amiel wrote: This may sound like a silly question I am currently running 7.4.2. I would like to upgrade to 7.4.5... So I downloaded postgresql-7.4.5.tar.gz uncompressed... configure... try

[GENERAL] upgrading minor versions

This may sound like a silly question I am currently running 7.4.2. I would like to upgrade to 7.4.5... So I downloaded postgresql-7.4.5.tar.gz uncompressed... configure... make make install I run psql and it gives m

Re: [GENERAL] performance of IN (subquery)

Tom Lane wrote: Greg Stark <[EMAIL PROTECTED]> writes: I'm not about to run analyze in the middle of the data generation (which wouldn't work anyways since it's in a transaction). Since 7.3 or 7.4, you *can* run ANALYZE in the middle of a transaction. The cached-plan business is a problem, I agre

[GENERAL] Strange difference in query execution time

I have a view, vw_tc_user_acccess, for determing user access to certain objects. On my machine, I get the following query execution times: 1. SELECT * FROM vw_tc_user_access: 33.04ms 2. SELECT * FROM vw_tc_user_access WHERE object_type = 'FORUM': 3.49ms 3. SELECT * FROM vw_tc_user_access WHERE o

Re: [GENERAL] performance of IN (subquery)

Is there anything for the TODO here? --- Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > I'm not about to run analyze in the middle of the data generation > > (which wouldn't work anyways since it's in a transac

Re: [GENERAL] performance of IN (subquery)

Greg Stark <[EMAIL PROTECTED]> writes: > I'm not about to run analyze in the middle of the data generation > (which wouldn't work anyways since it's in a transaction). Since 7.3 or 7.4, you *can* run ANALYZE in the middle of a transaction. The cached-plan business is a problem, I agree, but I thin

Re: [GENERAL] performance of IN (subquery)

Jon Lapham <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I've thought about this before. One simple trick would be to get rid >> of the current pg_class reltuples/relpages fields in favor of a >> tuples-per-page estimate, which could be multiplied by >> RelationGetNumberOfBlocks() during planni

Re: [GENERAL] performance of IN (subquery)

2. Instability of plans. Right now, the planner will not change plans underneath you --- you have to issue an explicit VACUUM or ANALYZE to change the terms of discussion. That would stop being true if physical file size were always taken into account. Maybe this is a problem, or maybe

[GENERAL] Audit

Hi list. I just want to send to you an sql file containing tools for audit the UPDATE and DELETE statements in a database by saving all the modifications made by a network/system/database user. I hope you find it useful El Santi = Santiago Cassina Responder a: [EMAIL PROTECTED] _

Re: [GENERAL] Gentoo for production DB server?

On Tue, 2004-08-24 at 15:01, Greg Donald wrote: > when it obviously was not. The price was right and I knew going in I > wasn't getting a perfectly stable distro, but nevertheless they left me > with a broken machine on several occasions. Having a slightly faster > machine isn't worth the headach

[GENERAL] grid things and postgres

Hi, I am working on the G System project (BSD licenced) and we are going to be (eventually) implementing a distributed database to hold data for the game that (eventually) will be developed. People have (though I don't think anyone has seriously investigated yet) suggested that we use a grid wi