[SQL] FIFO Queue Problems

I'm having a race condition with a FIFO queue program that I've created... CREATE TABLE fifo ( id serial, data varchar(100), status int4 DEFAULT 0 ); I have a program that claims a row for itself my $processid = $$; my $sql_update =

[SQL] Different size in the DATA directory

Hi, in my production server a "du -hs" in the DATA directory returns 21GB in a newly installed server, after I restored the dumps from the production server, the "du -hs" gives me just 8GB   why is there such a diference???   what should I do (besides buying bigger and bigger HDs) ???   []'

Re: [SQL] Different size in the DATA directory

Hi, in my production server a "du -hs" in the DATA directory returns 21GB in a newly installed server, after I restored the dumps from the production server, the "du -hs" gives me just 8GB why is there such a diference??? what should I do (besides buying bigger and bigger HDs) ??? VACUUM? --

Re: [SQL] Different size in the DATA directory

I do daily vacuums on the production server | > Hi, in my production server a "du -hs" in the DATA directory | > returns 21GB | > in a newly installed server, after I restored the dumps from the | > production server, the "du -hs" gives me just 8GB | > | > why is there such a diference??? | >

Re: [SQL] Different size in the DATA directory

On Fri, 1 Nov 2002, [iso-8859-1] Maurício Sessue Otta wrote: > I do daily vacuums on the production server You haven't said what version you're running, but if you're using 7.2 and non-FULL vacuums you also will want to make sure that you have the free space map settings in postgresql.conf are la

Re: [SQL] Different size in the DATA directory

Maurício Sessue Otta wrote: Hi, in my production server a "du -hs" in the DATA directory returns 21GB in a newly installed server, after I restored the dumps from the production server, the "du -hs" gives me just 8GB why is there such a diference??? what should I do (besides buying bigger and b

Re: [SQL] FIFO Queue Problems

On Friday 01 Nov 2002 2:56 pm, Chris Gamache wrote: > I'm having a race condition with a FIFO queue program that I've created... > I have a program that claims a row for itself [code sample] > The problem occurrs when two of the processes grab the exact same row at > the exact same instant. It h

Re: [SQL] Selecting * from the base table but getting the inheriteds

On Wed, Oct 30, 2002 at 10:25:17AM +0100, James Adams wrote: > Yea it would be easier to have everything in one table filling unused with > nulls, but I was trying to avoid that because of the wasted space. > But I think I'll do it that way after all :~] > Thanks for your help Don't fret too

Re: [SQL] FIFO Queue Problems

On Fri, Nov 01, 2002 at 06:56:30 -0800, Chris Gamache <[EMAIL PROTECTED]> wrote: > > The problem occurrs when two of the processes grab the exact same row at the > exact same instant. It happens roughly 1 out of 1000 times. I'm not sure if > setting the transactions to serializable would fix the

Re: [SQL] FIFO Queue Problems

Chris Gamache <[EMAIL PROTECTED]> writes: > I have a program that claims a row for itself > my $processid = $$; > my $sql_update = < UPDATE fifo > set status=$processid > WHERE id = (SELECT min(id) FROM fifo WHERE status=0); > EOS > The problem occurrs when two of the processes

Re: [SQL] Database Design tool

On Wed, 30 Oct 2002 14:35:23 + (UTC), [EMAIL PROTECTED] (Johannes Lochmann) wrote: >> Can anybody take me reference on Database design tool with PostgreSQL >> support. You can use PowerDesigner from Sybase. Windows only. A fully functional trial version (45 days) is downloadable at www.sybas

Re: [SQL] [GENERAL] Database Design tool

Viacheslav N Tararin wrote: Hi. Can anybody take me reference on Database design tool with PostgreSQL support. thanks. There's another one very good called Data Architect: http://www.thekompany.com/products/dataarchitect/ -- Diogo de Oliveira Biazus [EMAIL PROTECTED] Ikono Sistemas e Autom

[SQL] HA PostgreSQL

I am trying to develop a plan for a high-availability (HA) implementation of a database using PostgreSQL. One wrinkle; the data we receive is performance data, and occassional loss of some measurements is Ok for us. [I know, this is not in the main stream of database users :-)]. I have looke

Re: [SQL] FIFO Queue Problems

Do you need to unlock the table fifo when you are done? On Fri, 2002-11-01 at 11:51, Tom Lane wrote: > Chris Gamache <[EMAIL PROTECTED]> writes: > > I have a program that claims a row for itself > > > my $processid = $$; > > my $sql_update = < > UPDATE fifo > > set status=$processid

Re: [SQL] FIFO Queue Problems

On Fri, Nov 01, 2002 at 14:18:37 -0500, Wei Weng <[EMAIL PROTECTED]> wrote: > Do you need to unlock the table fifo when you are done? Locks only apply for the duration of a transaction. When you commit or roleback the lock will be released. ---(end of broadcast)-

Re: [SQL] FIFO Queue Problems

Wei Weng <[EMAIL PROTECTED]> writes: > Do you need to unlock the table fifo when you are done? That happens at COMMIT. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECT

[SQL] making queries more effecient

Hi. I have this query that I have been trying to reduce to a single statement, but haven't figured out how. Am I missing something? CREATE TEMP TABLE temp20561149207391 AS SELECT DISTINCT ON ("VisitorID") "VisitorID","Type" FROM "ProgramEvent" WHERE "ProgramID" = 10 ORDER BY "VisitorID","Created"

Re: [SQL] unnecessary updates

Another way to approach this would be to add a trigger to your table in the database, that rejects updates that don't change any values. You'd basically have to hard code that same logic (new.a != old.a or new.b != old.b ...) and it'd fire on every update, so you're talking about trading computatio

[SQL] select syntax question

This is what is on postgresql's manual page: http://www.postgresql.org/idocs/index.php?sql-select.html SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ AS output_name ] [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ]

Re: [SQL] select syntax question

On Fri, Nov 01, 2002 at 16:33:32 -0500, Wei Weng <[EMAIL PROTECTED]> wrote: > This is what is on postgresql's manual page: > > According to this syntax, SELECT DISTINCT COUNT(ID) FROM test > should be valid while SELECT COUNT(DISTINCT ID) FROM test otherwise. > > while in fact, both are valid.

Re: [SQL] making queries more effecient

This should be all you need. insert into VisitorPointer839 ("VisitorID") select VisitorID from ProgramEvent Where ProgramID = 10 and Type = 0 group by VisitorID You dont need order by because its not important the order it goes in the database, just the order that it comes out. I have found that

[SQL] Subtracting time fields

Hello all, I have two fields in my database access_time and release_time.I would like to calculate the "duration" between these two values, but can't figure out what to do. I've tried something like this but it doesn't work: select access_time, release_time, time(access_time) - time(releas

Re: [SQL] making queries more effecient

Peter, For the rest of our sakes, in the future, please format your SQL before posting it to request help. > CREATE TEMP TABLE temp20561149207391 AS SELECT DISTINCT ON ("VisitorID") > "VisitorID","Type" FROM "ProgramEvent" WHERE "ProgramID" = 10 ORDER BY > "VisitorID","Created" DESC;INSERT INTO

Re: [SQL] HA PostgreSQL

On Fri, 2002-11-01 at 13:26, Charles H. Woloszynski wrote: > I am trying to develop a plan for a high-availability (HA) > implementation of a database using PostgreSQL. One wrinkle; the data we > receive is performance data, and occassional loss of some measurements > is Ok for us. [I know, th

Re: [SQL] Subtracting time fields

On Fri, Nov 01, 2002 at 17:05:26 -0500, Kevin Old <[EMAIL PROTECTED]> wrote: > Hello all, > > I have two fields in my database access_time and release_time.I > would like to calculate the "duration" between these two values, but > can't figure out what to do. > > I've tried something like

Re: [SQL] making queries more effecient

Chad, > Im not sure I can get my head around the difference between doing your > subselect > > INSERT INTO "VisitorPointer839" ("VisitorID") > SELECT "VisitorID" FROM ( > SELECT DISTINCT ON ("VisitorID") "VisitorID","Type" > FROM "ProgramEvent" WHERE "ProgramID" = 10 > ORDER BY "VisitorID","

Re: [SQL] How do I get rid of these messages?

7.3 beta has client_min_messages which can show only WARNING and not NOTICE. --- Bhuvan A wrote: > > How do I get rid of the messages like "NOTICE: CREATE TABLE / PRIMARY > > KEY will create implicit index 'test_pkey' for t

Re: [SQL] Different size in the DATA directory

Tim Perdue wrote: > I had this same problem with 7.1.x, even doing full vacuums on > SourceForge.net last year. > > I assumed after a while that there was some hidden bug where file bloat > occurred, despite the vacuum. After 3 months or so, you'd have to > dump/restore the entire db and you'd

Re: [SQL] select syntax question

Yes, a big difference. The first returns the distinct COUNT values, but there is only one, of course. The second returns the number of distinct values in the column. --- Wei Weng wrote: > This is what is on postgresql's ma

[SQL] Timezone issue with date_part

In trying to debug some code, I've come across this SQL issue that's causing my problem. I've got two epoch time values that I have to compare. Time #1 seems to be working straightforwardly enough, but a tricky timezone-related error has surfaced with Time #2. Looking at the straight timestamp: