[ADMIN] deadlock problem in Ad serving..
Hi, I use postgresql in serving Ads and counting impression of the ad banners. whenever an Ad is served I do a update. update banner_stats set imp=imp+1 where uniqid=4330 in a transaction. In a busy web environment like ours there are high chances that the same page is displyed concurrently to two different viewers. I have been able to verify using two psql sessions that unless one transaction commits the update, the update from the second client (also in a transaction block) keeps wating to happen. as a result such updates get queued up and i finally get "dead lock detected" error. ~~ Error: DBD::Pg::st execute failed: ERROR: deadlock detected at /usr/local/perlapache/lib/perl/Banner.pm line 71, line 7. ~~ can anyone tell me how shud i go ahead tosolve this problem regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] deadlock problem in Ad serving..
> ~~ > Error: DBD::Pg::st execute failed: ERROR: deadlock detected at > /usr/local/perlapache/lib/perl/Banner.pm line 71, line 7. > ~~ It is a genuine error, occurs while two or more transaction process tries to update/delete a same record simultaneously. You can overcome this by locking the table in share row exclusive mode also. More details can be found in the lock command documentation. regards, bhuvaneswaran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] deadlock problem in Ad serving..
thanks for ur response bhuvan, i will read that doc and try it again. regds mallah. On Monday 20 January 2003 03:32 pm, Bhuvan A wrote: > > ~~ > > Error: DBD::Pg::st execute failed: ERROR: deadlock detected at > > /usr/local/perlapache/lib/perl/Banner.pm line 71, line 7. > > ~~ > > It is a genuine error, occurs while two or more transaction process tries > to update/delete a same record simultaneously. You can overcome this by > locking the table in share row exclusive mode also. More details can be > found in the lock command documentation. > > regards, > bhuvaneswaran > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Triggers taking much time to insert data
Hi All, I have table with almost 3,500 records with 9 fields which is similar structure to Table1. From that I am porting to following structure Table 1. T1f1 integer serial (primary key), T1f2 varchar(100) (unique), T1f3 integer default 0, T1f4 text, T1f5 boolean default 'f', T1f6 boolean default 'f', T1f7 boolean default 'f', T1f8 boolean default 'f', T1f9 integer In my new database I inserted some more tables which referes to Table1 Table2 T2f1 integer references X1, T2f2 integer references X2, T2f3 integer references Table1 primary key (T2f2,T2f3) So, I wrote a Trigger on Table1 to insert records in Table2 (max. five insertions will occur) Where X1 is will grow atleast 25 records with each insert in Table1 and X2 is having 10 records, Table3 T3f1 integer references X1, T3f2 integer references X3, T3f3 integer references Table1 primary key (T3f2,T3f3) So, same above Trigger on Table1 insert records in Table3 (max. 20 insertions will occur) Where X3 is having 70 records. Along with this I am Searching Table3 on two keys T3f2,T3f3 for existance before inserting. I am Table3 before inserting for duplication. Table4 T4f1 integer references X1, T4f2 integer primary key (T4f1,T4f2) So, I wrote Insert Trigger for Table2 and Table3 to insert in Table4 Table2 Trigger inserts atleast 5 records in Table4. Table3 Trigger inserts atleast 25 records in Table4. Table5 T5f1 integer T5f2 integer T5f3 integer T5f4 integer T5f5 boolean T5f6 boolean T5f6 boolean T5f7 boolean T5f8 boolean So, I have a Trigger on Table4 to insert into Table5, So to insert a record in Table5 i am doing max (n-1) searches in Table4 atleast maximum of 6 insertions occur. So to insert a single record in Table1 initially it took 3 seconds directly from console, if i was running from php program it was taking 6 seconds, As Data growing in each table insertions will became more complex. It is taking almost 20seconds to insert a single record. I am porting data similar structure of Table1 to this new database. In total 6 hours i could not able to port more than 1500 records I have porting program in php. Hope I had given complete information. How can I slove this problem. Please some body give solution. Thanks & Regards, Sreedhar "Faith, faith, faith in ourselves, faith, faith in God, this is the secret of greatness. If you have faith in all the three hundred and thirty millions of your mythological gods, and in all the gods which foreigners have now and again introduced into your midst, and still have no faith in yourselves, there is no salvation for you. " (III. 190) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] deadlock problem in Ad serving..
Bhuvan A <[EMAIL PROTECTED]> writes: >> ~~ >> Error: DBD::Pg::st execute failed: ERROR: deadlock detected at >> /usr/local/perlapache/lib/perl/Banner.pm line 71, line 7. >> ~~ > It is a genuine error, occurs while two or more transaction process tries > to update/delete a same record simultaneously. You can overcome this by > locking the table in share row exclusive mode also. That will just move his problem somewhere else. I think the only real answer is to use shorter transactions (one per page, not one per several pages). regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Installing on Sun machine
On Fri, Jan 17, 2003 at 04:29:37PM +, Adam Witney wrote: > This was the error message issued by that program: > ld.so.1: /usr/local/pgsql7.3.1/bin/postgres: fatal: libreadline.so.4: open > failed: No such file or directory Looks like it can't find your readline libraries. Time to poke at your LD_LIBRARY_PATH, and make sure that you actually have readline installed. A -- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada <[EMAIL PROTECTED]> M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] deadlock problem in Ad serving..
On Mon, 20 Jan 2003, Tom Lane wrote: > > Bhuvan A <[EMAIL PROTECTED]> writes: > >> Error:...deadlock detected... > > ... You can overcome this by locking the table in share row > > exclusive mode also... > > ...use shorter transactions (one per page, not one per several pages). Hmm... with his query: "update banner_stats set imp=imp+1 where uniqid=4330" Is it true that the problem happens when updates are done in a different order by two transactions like this: trans.1: "update banner_stats set imp=imp+1 where uniqid=4330" trans.2: "update banner_stats set imp=imp+1 where uniqid=10" trans.1: "update banner_stats set imp=imp+1 where uniqid=10" trans.2: "update banner_stats set imp=imp+1 where uniqid=4330" If so, then could the problem be avoided if in his application logic he always did the updates in the same order? ... I.e. Make sure the each transaction does the updates in the same order by sorting his updates based on uniqid in the client? Ron ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] database monitoring tool availability
are there any tools for postgres that would assist a dba in the monitoring of a postgres database? for example, send alerts when data files are filling up, send alerts if the database goes down, etc. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] About PostgreSQL 7.3.1 Installation
Dear Sir, I'm a graduate student studying computer science in the Univ. of Edinburgh. After I donwload the 'PostgreSQL 7.3.1', I run the command "/configure" in its directory on my computer as root. However, the result is ":bad interpreter: no such file or directory". My OS is redhat 7.3 and gmake version is 3.79.1. Could you please tell how to install PostgreSQL on my computer? Thanks a lot. yours sincerely, Jiang Li ** Emailto : [EMAIL PROTECTED] MSc in Computer Science The University of Edinburgh Telephone : +0044-131-667-6000-82570 ** ---(end of broadcast)--- TIP 3: 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
Re: [ADMIN] deadlock problem in Ad serving..
> > ~~ > > Error: DBD::Pg::st execute failed: ERROR: deadlock detected at > > /usr/local/perlapache/lib/perl/Banner.pm line 71, line 7. > > ~~ > > It is a genuine error, occurs while two or more transaction process tries > to update/delete a same record simultaneously. You can overcome this by > locking the table in share row exclusive mode also. More details can be > found in the lock command documentation. Since your using DBI all you need to do is turn AutoCommit off when you load DBI and call commits after you verify your transaction succeeded. Turning off AutoCommit wraps a 'begin transaction' around everything. More info: http://www.perldoc.com/cpan/DBI.html#Transactions ---(end of broadcast)--- TIP 3: 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
Re: [ADMIN] deadlock problem in Ad serving..
Ron Mayer <[EMAIL PROTECTED]> writes: > Is it true that the problem happens when updates are done > in a different order by two transactions like this: > trans.1: "update banner_stats set imp=imp+1 where uniqid=4330" > trans.2: "update banner_stats set imp=imp+1 where uniqid=10" > trans.1: "update banner_stats set imp=imp+1 where uniqid=10" > trans.2: "update banner_stats set imp=imp+1 where uniqid=4330" Yeah, I would assume that that's the problem. > If so, then could the problem be avoided if in his application > logic he always did the updates in the same order? ... I.e. Make > sure the each transaction does the updates in the same order by > sorting his updates based on uniqid in the client? If I understood correctly, he's tracking webpage hits; so the updates are going to correspond to the sequence in which visitors move to different webpages. I don't think he can force a particular order (and if he could, there'd be little need for the per-page counts anyway). I suppose he could hold all the updates in a temp table and apply them in a sorted order at end of transaction, but that seems like a pain. I would suggest using a separate transaction for each webpage visited. Holding a transaction open across multiple page traversals is widely considered bad news for a number of reasons, not only this one. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] deadlock problem in Ad serving..
On Mon, 20 Jan 2003, Tom Lane wrote: > > If I understood correctly, he's tracking webpage hits; so the updates > are going to correspond to the sequence in which visitors move to > different webpages. Ah... I was thinking he was counting banners served within a single page (perhaps a banner on top and a banner on the bottom), and doing accounting of which banners were shown. In that case it might have been interesting to keep some of his information in a transaction. start transaction... insert_a_new_cookie_record_if_it_didn't_exits.. record the top_banner... record the bottom_banner... end transaction... I've done something like that to count how many distinct users saw particular ads. In this case sorting the small number (2) of banners in his application logic would be easy. > I would suggest using a separate transaction for each webpage visited. > Holding a transaction open across multiple page traversals is widely > considered bad news for a number of reasons, not only this one. I understand this part. Ron ---(end of broadcast)--- TIP 3: 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
Re: [ADMIN] Database logging.... Recycle server logs ???
"Tom Lane" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > "codeWarrior" <[EMAIL PROTECTED]> writes: > > I noticed that the server generated logfile gets real big real fast -- (15 > > queries and it's already at 1.5MB) -- Is there a switch / option to recycle > > the server logs periodically ??? > > The recommended procedure is not to use pg_ctl's -l switch, but to pipe > output from it into a log-rotating script. The rotatelogs script from > the Apache distribution works well, or you can roll your own. See > http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/logfile-maintena nce.html > > regards, tom lane > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Thanks Tom -- Of course -- I located that section of the manual immediately after my post... I will use logrotate ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Deadlock Checker doesn't see foreign-key locks
It appears to me that the Deadlock Checker doesn't see (and thus release) foreign-key-based locks (see below for details). Am I missing something? Is there a configuration item I am unaware of? As far as I can tell (yes, I tested thoroughly) if I create a normal deadlock situation as follows, the deadlock detector will work properly: Thread A: BEGIN WORK; UPDATE [table A, row X] Thread B: BEGIN WORK; UPDATE [table B, row Y] Thread A: UPDATE [table B, row Y] Thread B: UPDATE [table A, row X] This is recognized by the deadlock detector (after the specified delay), one of the two is rolled back, and the other completes happily. HOWEVER, if I have a foreign-key-related lock, as follows, it is not recognized: Thread A: BEGIN WORK; UPDATE [table A, row W] /* This has a foreign key into table F, row P */ Thread B: BEGIN WORK; UPDATE [table B, row Y] /* This has a foreign key into table G, row Q */ Thread A: UPDATE [table B, row Z] /* This has a foreign key into table F, row P */ Thread B: UPDATE [table A, row X] /* This has a foreign key into table G, row Q */ Note that none of the UPDATEs step on the same actual row of the same table, but they step (and lock) the same rows in the same tables via foreign keys. In this case (specifically tested), there is no deadlock detection. NOTE - I am currently using v7.2.1, although my search through the release notes lead me to believe that the problem was not addressed subsequently. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Failed to initialize lc_messages to ''
Hi all, I just tried a fresh install of pg 7.3.1 on a SuSE 8.1 box. I keep getting messages like Failed to initialize lc_messages to '' during initdb. I already tried --locale=C but no help. Anyone any ideas? regards GB ---(end of broadcast)--- TIP 3: 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
Re: [ADMIN] restore/dup OIDs HELP!
Thanks man; Iappreciate the data. Stephan Szabo wrote: > > On Thu, 9 Jan 2003, Jack Flak wrote: > >> Stephan, >> >> That's very interesting! I didn't even know about these other "hidden" >> fields. How many others are there? > > Let's see, I think the set is > ctid, oid, xmin, cmin, xmax, cmax, tableoid > > IIRC, > ctid is basically like a physical pointer to where the row actually is > oid you know > xmin - transaction that made the row > cmin - command counter in that transaction that made the row > xmax, cmax - like xmin, cmin but for the transaction that removes it > tableoid - which table it belongs to (for inheritance) > > Cmin and xmax share storage, and it looks like xmin/xmax are of a type > that you can't actually do much with from an SQL statement. > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] OT: seeking query help, where?
On Thu, 16 Jan 2003, Tim Lynch wrote: > First, sorry for the OT, flame me off-list! > > I'm a sysadmin being impressed into dba service. i've been getting along > pretty well writing queries and making reports, but i've got some questions. > suggestions for a good list/forum for help? > > from two tables both with email_addr columns, i want a distinct list of all > email_addrs in one column. what i do now is select distinct on each and then > sort -u the results. > I like it! My sort of solution. Only I don't know the -u switch, I'd have done `cat blah* | sort | uniq` but it's obviously the same idea. As for the query that would do it, I believe that's already been answered I just thought I'd stand up for the good old fashioned unix ways. :) -- Nigel J. Andrews ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] OT: seeking query help, where?
UNION! thanks everyone! i'll take this stuff up on the pgsql-sql list in the future. union does intra- and inter- table distinct, notice `red' and `two': test=> select str from foo ; str - one two two red (4 rows) test=> select str from bar ; str red orange yellow green blue indigo violet red (8 rows) test=> select str from foo union ( select str from bar ) ; str blue green indigo one orange red two violet yellow (9 rows) ---(end of broadcast)--- TIP 3: 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
[ADMIN]
Dear Sir, I have downloaded fallowing files from http://www.postgresql.org 1. mx-2.0.1-1.i386.rpm 2. postgresql-odbc-7.1.2-5PGDG.i386.rpm 3. postgresql-7.1.2-5PGDG.i386.rpm 4. postgresql-perl-7.1.2-5PGDG.i386.rpm 5. postgresql-contrib-7.1.2-5PGDG.i386.rpm 6. postgresql-python-7.1.2-5PGDG.i386.rpm 7. postgresql-devel-7.1.2-5PGDG.i386.rpm 8. postgresql-server-7.1.2-5PGDG.i386.rpm 9. postgresql-docs-7.1.2-5PGDG.i386.rpm 10. postgresql-tcl-7.1.2-5PGDG.i386.rpm 11. postgresql-jdbc-7.1.2-5PGDG.i386.rpm 12. postgresql-test-7.1.2-5PGDG.i386.rpm 13. postgresql-libs-7.1.2-5PGDG.i386.rpm 14. postgresql-tk-7.1.2-5PGDG.i386.rpm and Tried to install on Red Hat Linux release 7.2 (Enigma) Kernel 2.4.7-10 on an i686, I got the fallowing Errors. [root@majcompnew p7.2]# rpm -i postgresql-contrib-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql = 7.1.2 is needed by postgresql-contrib-7.1.2-5PGDG libcrypto.so.1 is needed by postgresql-contrib-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-contrib-7.1.2-5PGDG libssl.so.1 is needed by postgresql-contrib-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-contrib-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql = 7.1.2 is needed by postgresql-contrib-7.1.2-5PGDG libcrypto.so.1 is needed by postgresql-contrib-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-contrib-7.1.2-5PGDG libssl.so.1 is needed by postgresql-contrib-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-devel-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql-libs = 7.1.2 is needed by postgresql-devel-7.1.2-5PGDG libcrypto.so.1 is needed by postgresql-devel-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-devel-7.1.2-5PGDG libssl.so.1 is needed by postgresql-devel-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-docs-7.1.2-5PGDG.i386.rpm [root@majcompnew p7.2]# rpm -i postgresql-jdbc-7.1.2-5PGDG.i386.rpm [root@majcompnew p7.2]# rpm -i postgresql-libs-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-libs-7.1.2-5PGDG libssl.so.1 is needed by postgresql-libs-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-perl-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-perl-7.1.2-5PGDG libssl.so.1 is needed by postgresql-perl-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-python-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-python-7.1.2-5PGDG libssl.so.1 is needed by postgresql-python-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-server-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql = 7.1.2 is needed by postgresql-server-7.1.2-5PGDG libcrypto.so.1 is needed by postgresql-server-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-server-7.1.2-5PGDG libssl.so.1 is needed by postgresql-server-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-tcl-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-tcl-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-tcl-7.1.2-5PGDG libssl.so.1 is needed by postgresql-tcl-7.1.2-5PGDG [root@majcompnew p7.2]# rpm -i postgresql-test-7.1.2-5PGDG.i386.rpm error: failed dependencies: postgresql = 7.1.2 is needed by postgresql-test-7.1.2-5PGDG oot@majcompnew p7.2]# rpm -i postgresql-tk-7.1.2-5PGDG.i386.rpm error: failed dependencies: libcrypto.so.1 is needed by postgresql-tk-7.1.2-5PGDG libpgtcl.so.2 is needed by postgresql-tk-7.1.2-5PGDG libreadline.so.4.1 is needed by postgresql-tk-7.1.2-5PGDG libssl.so.1 is needed by postgresql-tk-7.1.2-5PGDG I would appreciate if you could provide me help in installing PostGreSQL on Red Hat Linux release 7.2. Thanks and Regards, Sharanayya G M Majoris Systems Pvt. Ltd. Bangalore
[ADMIN] pg_restore problem
I got this error message: pg_restore: [archiver] input file does not appear to be a valid archive after the command: pg_restore -d mydb_name mydb_name.dump.tar the tar file was created with the command pg_dump -b -f mydb_name.dump.tar -F t mydb_name == This is exactly what is said in the Man pages in pg_dump and pg_restore. I am using postgres 7.3.1 My question: Is this a bug in the new release? Or I am totally stupid. Help would be appreciated. Kemin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] Registering all User Operations
Hi: I need to register all the operations (INSERT/UPDATE/DELETE) that users does. Something like an log file for the backbone (like a /var/log/messages, where all done is registered). Can any help me? Thanks. -- SAMTEK, Consultores Informática y Telecomunicaciones Calificado como Agente Tecnológico por la Consejería de Empleo y Desarrollo Tecnológico RAITEC (Red Andaluza de Innovación y Tecnología) Adolfo M. Pachón Rodríguez Director de Sistemas C/ Salado, Portal 11, Local 8 41010 - Sevilla tlf: 95 428 6045 fax: 95 427 8500 http://www.samtek.es mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Do Something before Abort on Trigger ???
On Wed, 15 Jan 2003, Yudha Setiawan wrote:
> Somebody gimme your hand plz.
>
> Using my previous Database I used to be like that;
> " ALTER TRIGGER tr_T_DtlPO ON dbo.T_DtlPO FOR UPDATE AS
> bla..bla..bla...
> IF @OldQty <> @NewQty BEGIN
> ROLLBACK
> INSERT INTO T_My_ListError(fc_code,fv_descript)
>VALUES('12345','No Authority to Change Qty')
> END"
> So I'v already write the error code to table T_My_ListError before aborting this
>session
>
>-
> And I Tried on Postgre like that;
> " create or replace function fn_tr_t_dtlpo returns trigger as'
> begin
> bla..bla...;
> if new.fn_qty != old.fn_qty then
> raise exception ''Error 12345'';
> insert into t_my_listerror(fc_code,fv_descript)
> values(''12345'',''No Authority to Change Qty'');
> end if;
> end;' language 'plpgsql';
> "
> But I Couldn't get any records at all on t_my_listerror. Even when put the insert
>statement
> (insert into t_my_listerror...bla..bla) before raise exception.
Well you're aborting the transaction, your function is part of the transaction,
so any changes it makes to t_my_listerror will be aborted (or rolled
back). Ah, I see that you're raising the exception before you insert even. Why
would you expect control to reach the insert statement?
I imagine the only remotely sensible way to achieve what you want is to log to
file. That would need a trusted language installed. I can't see using the
standard postgresql log output being terrible useful for you, besides, you seem
to be using the exception notice to return information somewhere.
--
Nigel J. Andrews
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Do Something before Abort on Trigger ???
I'm going to CC this to the -general list as well, it's possible that -admin isn't the right list for this thread. You can not rollback or commit within a function. That would require nested transactions which aren't implemented yet. Obviously that precludes use of your 'begin work' in your function. All this means that you can not store your history records and have the transaction rollback. However, you are using a before trigger (I presume the created function name is a typo below) so to store a history but not the new data you can return NULL from your function. To proceed with the insert simply return NEW, with whatever modifications of it's data you require. So you have something along the lines of: create function aaa ( ) returns trigger as ' begin insert into log_table values ( new.fn_value ); if fn_value < 10 then insert into log_table values ( new.fn_value * 10 ); return null; endif return new; end ' language 'plpgsql'; Hope this helps you (and that I haven't made any nasty misdirection mistakes). -- Nigel J. Andrews On Fri, 17 Jan 2003, Yudha Setiawan wrote: > > - Original Message - > From: "Nigel J. Andrews" <[EMAIL PROTECTED]> > To: "Yudha Setiawan" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Friday, January 17, 2003 3:40 PM > Subject: Re: [ADMIN] Do Something before Abort on Trigger ??? > > > > Well you're aborting the transaction, your function is part of the > transaction, > > so any changes it makes to t_my_listerror will be aborted (or rolled > > back). Ah, I see that you're raising the exception before you insert even. > Why > > would you expect control to reach the insert statement? > > > > I imagine the only remotely sensible way to achieve what you want is to > log to > > file. That would need a trusted language installed. I can't see using the > > standard postgresql log output being terrible useful for you, besides, you > seem > > to be using the exception notice to return information somewhere. > > > > > -- > > Nigel J. Andrews > > > What big of thanks of me for you for the Adviced. It's a smart solutions but > One things you should know, we don't wanna take a long time to Porting > the Front-End. We have a lot of Job to be done and the "Time is Getting > Closer". > Our application is used to be taking a record from t_my_listerror for > knowing > the Error and then Showed to the User. We have a table for List Error Named > T_BDE_Error. Before i reads this reply, I've tried this one; > > " > drop function fn_tr_bi_pulse() cascade; > create or replace function fn_tr_bi_yyy() returns trigger as ' > begin > begin work; > insert into d_history.t_history_value (fn_value,fv_descript) > values(new.fn_values,''For History''); > if (new.fn_value * 20) >= 1000 then > insert into d_history.t_history_value (fn_value,fv_descript) > values(new.fn_values,''Invalid Values''); > rollback; > else > commit; > end if; > return new; > end;' language 'plpglsql'; > create trigger tr_bi_pulse before insert on d_transaction.t_pulse for each > row > execute procedure fn_tr_bi_pulse(); > " > There was no Error Show-up when i Compiling, but when i tried to inserting a > record > An Error is Show-Up. Do you have any ideas...??? Gimme your another best > ones; > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN]
Hello all, I am new to postgreSQL. I have installed postgreSQL 7.1.3-2 on Linux 7.2. If I login as root (administrator) and change user as postgres I can create database on /usr/local/pgsql/data which I created and changed its owner as postgres. root# mkdir usr/local/pgsql/data root# chown postgres /usr/local/pgsql/data root# su postgres bash-2.3# initdb -D /usr/local/pgsql/data bash-2.3# postmaster -D /usr/local/pgsql/data > logfile 2 >&1 & bash-2.3# createdb testdb bash-2.3# psql testdb However, when I login as a normal linux user I cannot create database. So it is a user management problem. zf2linux# createdb mydb FATAL 1: user 'zf2linux' does not exist I have tried to create a user of zf2linux in postgreSQL but the error still occurs. If I try to change to postgres from zf1linux it asks for password. zf2linux# su postgres password: ** incorrect password. My pg_hba.conf is left as the default format: local all trust hostall 127.0.0.1 255.255.255.255 trust I must miss something before I allow other users to access the postgreSQL server and account. Could you please help me with the problem? Thanks for your attention and will sum. Geoff ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN]
Hello all, I am new to postgreSQL. I have installed postgreSQL 7.1.3-2 on Linux 7.2. If I login as root (administrator) and change user as postgres I can create database on /usr/local/pgsql/data which I created and changed its owner as postgres. root# mkdir usr/local/pgsql/data root# chown postgres /usr/local/pgsql/data root# su postgres bash-2.3# initdb -D /usr/local/pgsql/data bash-2.3# postmaster -D /usr/local/pgsql/data > logfile 2 >&1 & bash-2.3# createdb testdb bash-2.3# psql testdb However, when I login as a normal linux user I cannot create database. So it is a user management problem. zf2linux# createdb mydb FATAL 1: user 'zf2linux' does not exist I have tried to create a user of zf2linux in postgreSQL but the error still occurs. If I try to change to postgres from zf1linux it asks for password. zf2linux# su postgres password: ** incorrect password. My pg_hba.conf is left as the default format: local all trust hostall 127.0.0.1 255.255.255.255 trust I must miss something before I allow other users to access the postgreSQL server and account. Could you please help me with the problem? Thanks for your attention and will sum. Geoff University of St Andrews ---(end of broadcast)--- TIP 3: 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
Re: [ADMIN] Stalled post to pgsql-admin
Hello all, I am new to postgreSQL. I have installed postgreSQL 7.1.3-2 on Linux 7.2. If I login as root (administrator) and change user as postgres I can create database on /usr/local/pgsql/data which I created and changed its owner as postgres. root# mkdir usr/local/pgsql/data root# chown postgres /usr/local/pgsql/data root# su postgres bash-2.3# initdb -D /usr/local/pgsql/data bash-2.3# postmaster -D /usr/local/pgsql/data > logfile 2 >&1 & bash-2.3# createdb testdb bash-2.3# psql testdb However, when I login as a normal linux user I cannot create database. So it is a user management problem. zf2linux# createdb mydb FATAL 1: user 'zf2linux' does not exist I have tried to create a user of zf2linux in postgreSQL but the error still occurs. If I try to change to postgres from zf1linux it asks for password. zf2linux# su postgres password: ** incorrect password. My pg_hba.conf is left as the default format: local all trust hostall 127.0.0.1 255.255.255.255 trust I must miss something before I allow other users to access the postgreSQL server and account. Could you please help me with the problem? Thanks for your attention and will sum. Geoff Dr. Zhiqiang Feng School of Geography & Geosciences Irving Building University of St Andrews St Andrews Fife KY16 9AL tel: 01334 463951 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: 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
Re: [ADMIN] Stalled post to pgsql-admin
Hi Zhiqiang, You might take a look at the following link: http://www.tek-tips.com/viewthread.cfm?SQID=449717&SPID=699&page=1 After you have postgres user properly setup, you can use pgaccess to configure other users. LelandJ - Original Message - From: "Zhiqiang Feng" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, January 20, 2003 7:52 AM Subject: Re: [ADMIN] Stalled post to pgsql-admin > > Hello all, > > I am new to postgreSQL. I have installed postgreSQL 7.1.3-2 on Linux 7.2. > If I login as root (administrator) and change user as postgres I can create > database on /usr/local/pgsql/data which I created and changed its owner as > postgres. > > root# mkdir usr/local/pgsql/data > root# chown postgres /usr/local/pgsql/data > root# su postgres > bash-2.3# initdb -D /usr/local/pgsql/data > bash-2.3# postmaster -D /usr/local/pgsql/data > logfile 2 >&1 & > bash-2.3# createdb testdb > bash-2.3# psql testdb > > However, when I login as a normal linux user I cannot create database. So > it is a user management problem. > > zf2linux# createdb mydb > FATAL 1: user 'zf2linux' does not exist > > I have tried to create a user of zf2linux in postgreSQL but the error still > occurs. > If I try to change to postgres from zf1linux it asks for password. > > zf2linux# su postgres > password: ** > incorrect password. > > My pg_hba.conf is left as the default format: > > local all trust > host all 127.0.0.1 255.255.255.255 trust > > I must miss something before I allow other users to access the postgreSQL > server and account. Could you please help me with the problem? > > Thanks for your attention and will sum. > > Geoff > > Dr. Zhiqiang Feng > School of Geography & Geosciences > Irving Building > University of St Andrews > St Andrews Fife KY16 9AL > tel: 01334 463951 > > [EMAIL PROTECTED] > > > ---(end of broadcast)--- > TIP 3: 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN]
First of all your "su postgres" problem. You will note, that the first time you executed "su postgres" you were root (and thus didn't need a password), the second time you were a non-root user, zf2linux, and thus you DO need a password. The second problem is who postgreSQL thinks you are. What is not obvious when starting, is that when you run a postgreSQL command from a shell (as opposed to running a DB command inside a psql session), the DB assumes that your Linux user name is a valid postgreSQL user name, and that's who you want to be identified as. So executing "zf2linux> psql template1" is asking postgreSQL to run psql for you connecting you as user zf2linux; whereas running "zf2linux> psql -U postgres template1" is asking postgreSQL to run psql for you connecting you as user postgres (most postgreSQL commands accept a "-U " parameter that lets you specify what user you want to run the command as (postgreSQL user, that is, not linux user) ). Bottom line: the simple solution is to su (to root), then su to postgres, then do what you need to do - create real users for the DB, possibly one called "zf2linux". Enjoy, Andrew 1/20/03 11:34:40 AM, Zhiqiang Feng <[EMAIL PROTECTED]> wrote: >Hello all, > >I am new to postgreSQL. I have installed postgreSQL 7.1.3-2 on Linux 7.2. >If I login as root (administrator) and change user as postgres I can create >database on /usr/local/pgsql/data which I created and changed its owner as >postgres. > > root# mkdir usr/local/pgsql/data > root# chown postgres /usr/local/pgsql/data > root# su postgres > bash-2.3# initdb -D /usr/local/pgsql/data > bash-2.3# postmaster -D /usr/local/pgsql/data > logfile 2 >&1 & > bash-2.3# createdb testdb > bash-2.3# psql testdb > >However, when I login as a normal linux user I cannot create database. So >it is a user management problem. > > zf2linux# createdb mydb > FATAL 1: user 'zf2linux' does not exist > >I have tried to create a user of zf2linux in postgreSQL but the error still >occurs. >If I try to change to postgres from zf1linux it asks for password. > > zf2linux# su postgres > password: ** > incorrect password. > >My pg_hba.conf is left as the default format: > local all trust > host all 127.0.0.1 255.255.255.255 trust > >I must miss something before I allow other users to access the postgreSQL >server and account. Could you please help me with the problem? > >Thanks for your attention and will sum. > >Geoff > >University of St Andrews > > > >---(end of broadcast)--- >TIP 3: 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 > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Transactions and HTTP
Hi, Writing a web-based application using PostgreSQL as the back-end database. The master create functions look something like this: 1. Accept ID from user. 2. Verify that ID doesn't exist in database. Start transaction. Create blank record with ID as key. 3. Accept remaining data for record from user. 4. Enter data into newly-created blank record. End transaction. Firstly,is it possible to have a transaction spanning multiple instances of the CGI/mod_perl invocations? Second, there are issues if the user session terminates for some reason (e.g. user goes offline after the blank record has been created, etc.) and I end up with a locked blank record in the database. Would appreciate pointers to best practises in handling this paradigm. Unrelated, but I find at least 3 different interfaces to PostgreSQL from Perl: DBI, Pg and PgSQL. I'm using Pg at the moment, since that ships with PgSQL by default with Red Hat, but are there any reasons to choose one over the other? Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ It is the mind that moves ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] deadlock problem in Ad serving..
Hi, Guys thanks for the response.. I am using perl DBI. In fact i have tried doing the commit immediately after the update in the script then i saw my pg_stat_activity reporting lots of queued update transactions. I would like to discuss this problem further but not able to do so at the moment. I have temporarily disabled impression counting. I am very thankful to you guys for the interest / response once again and will keep you posted. regds mallah. On Monday 20 January 2003 08:48 pm, Tom Lane wrote: > Bhuvan A <[EMAIL PROTECTED]> writes: > >> ~~ > >> Error: DBD::Pg::st execute failed: ERROR: deadlock detected at > >> /usr/local/perlapache/lib/perl/Banner.pm line 71, line 7. > >> ~~ > > > > It is a genuine error, occurs while two or more transaction process tries > > to update/delete a same record simultaneously. You can overcome this by > > locking the table in share row exclusive mode also. > > That will just move his problem somewhere else. I think the only real > answer is to use shorter transactions (one per page, not one per several > pages). > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
