Обсуждение: ALTER TABLE - UNIQUE constraint

Hi all,

I am new to PostgreSQL

how can i give the following satement in PostgreSQL

ALTER TABLE tblDayTransaction WITH NOCHECK ADD
  UNIQUE  NON
 (
  empid,
  ppid,
  transdate
 )

regards,
sreedhar

I am running 7.2 on FreeBSD-current.  I have an web page that I have been
playing with for tracking stocks online.  It has a table which contains
all of my calculations.  Once a day I pull down a report from MarketWatch
of all stocks that meet a criterion I have defined.  Once every 5 minutes
I download new stock data, and compute buy/sell recommendations. Anyway,
that is all working.  Now for my problem:

I download my list from MarketWatch and then go and pull historical
data for all the new stocks in the list.  The program that gets the
historical data does:

BEGIN WORK ;
SELECT stockid FROM stocks WHERE ticker = 'M$FT' FOR UPDATE ;
UPDATE stocks SET nextupdate = NOW + INTERVAL '1 DAY' ;
....Insert 7 years of stock data....
UPDATE stocks SET nextupdate = NOW() ;
COMMIT WORK ;

The program that gets the 5 minute updates does:

SELECT stockid FROM stocks WHERE nextupdate <= NOW() ;
-- and for each stockid it finds above
BEGIN WORK ;
....Insert new data...
COMMIT WORK ;

Looks simple enough, right?

Anyway, while the historical data INSERTs are occurring, no other
application can insert data into any of my tables, so my 5 minute update
sits there and diddles around forever, and I don't get recommendations.
My understanding from reading the docs is that 'FOR UPDATE' gets a 'ROW
SHARE' lock, and updates and inserts get a 'ROW EXCLUSIVE' lock.  I cannot
understand why all my apps, except the historical data app, are showing
'waiting' in a 'ps' command.  Is the problem that after I do the fist
"UPDATE" command it promotes the lock to 'ROW EXCLUSIVE', and then the 5
minute update cannot even look at the value of 'nextupdate' until after
the inserts have completed?  I set 'nextupdate' to tomorrow specifically
to prevent the 5 minute update from doing anything until the historical
data is available.

Any ideas?

- brian


Wm. Brian McCane                    | Life is full of doors that won't open
Search http://recall.maxbaud.net/   | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"

Brian McCane <bmccane@mccons.net> writes:
> BEGIN WORK ;
> SELECT stockid FROM stocks WHERE ticker = 'M$FT' FOR UPDATE ;
> UPDATE stocks SET nextupdate = NOW + INTERVAL '1 DAY' ;
> ....Insert 7 years of stock data....
> UPDATE stocks SET nextupdate = NOW() ;
> COMMIT WORK ;

What exactly does the "Insert" step do?  Why are you bothering to UPDATE
stocks.nextupdate twice in this transaction?  It's not like any other
transaction will be able to see the now + 1 day setting.  Do you really
mean to update *all* rows of stocks here?

> The program that gets the 5 minute updates does:

> SELECT stockid FROM stocks WHERE nextupdate <= NOW() ;
> -- and for each stockid it finds above
> BEGIN WORK ;
> ....Insert new data...
> COMMIT WORK ;

Again, what exactly does the "Insert" step do?  Does it change
stocks.nextupdate?  (One would hope so, else there's a tight loop here.)
If it does, won't it block at that point waiting for the other
transaction's updates to be committed?

            regards, tom lane