Обсуждение: function execution problem - plpgsql
Hello folks I would like to ask the following thing: I have two functions: funcA() drops a row from table A, funcB() drops a row from table B that references table A. funcA() calls funcB() and issues a delete command after returning from funcB(). I get an error, stating that it can not be done, because there is a row in B that can not be deleted with deleting the particular row from A. So now: Why does this happen? Is it because a function is a transaction? why do series of update/delete operations work fine in a single function, but fail as soon as one of these operations is performed by function? Is there any way to avoid this behavior? My functions return values, error codes infact, so I need the return values, because I do not allow the users to directly insert, update or delete records in the tables, only via functions. I could define a trigger that fires before deleting the row from table A and calls funcB(), raises an exception if anything happened, catch the exception if funcA(), if the return value was not correct, but with this approach I loose the error value, because in PgSQL 8.0 there is no SQLERRM I could analyse. Please enlighten me Thanks Zoltan __________________________________________________ http://www.email.azet.sk - 2 000 MB na Vase e-maily!
# silvanus@azet.sk / 2005-07-21 10:18:05 +0200:
> I have two functions: funcA() drops a row from table A, funcB() drops a
> row from table B that references table A. funcA() calls funcB() and
> issues a delete command after returning from funcB(). I get an error,
> stating that it can not be done, because there is a row in B that can
> not be deleted with deleting the particular row from A.
>
> So now: Why does this happen? Is it because a function is a transaction?
> why do series of update/delete operations work fine in a single
> function, but fail as soon as one of these operations is performed by
> function? Is there any way to avoid this behavior?
Show us definitions of the tables and functions in question.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
Hi, We've been having trouble with a pgSQL 8.0.3 install on a WXP machine. Install goes fine, DB works until we restart the machine, then we can't connect. First time we thought something was corrupted because we had a power outage, we uninstalled/reinstalled and it was fine until, again, we decided to reboot. We were thinking it was a problem with permissions, but it doesn't make much sense to me that it's working fine before we restart. So, what can I send you to better describe my problem? Do you think it could be a problem with permissions? How can I test to find out what the problem might be? Thank you. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Audrey Bergeron-Morin wrote: >We've been having trouble with a pgSQL 8.0.3 install >on a WXP machine. Install goes fine, DB works until we >restart the machine, then we can't connect. First time >we thought something was corrupted because we had a >power outage, we uninstalled/reinstalled and it was >fine until, again, we decided to reboot. We were >thinking it was a problem with permissions, but it >doesn't make much sense to me that it's working fine >before we restart. > >So, what can I send you to better describe my problem? >Do you think it could be a problem with permissions? >How can I test to find out what the problem might be? > > Have you looked at the postgersql log files to see if there are any errors? Sounds like one possibility is that the PID file is not getting deleted on reboot and needs to be manually removed so that the postmaster restarts. We need more information to diagnose the problem, and the log files are a good place to start.
--- "Matthew T. O'Connor" <matthew@zeut.net> wrote: > Audrey Bergeron-Morin wrote: > > >We've been having trouble with a pgSQL 8.0.3 > install > >on a WXP machine. Install goes fine, DB works until > we > >restart the machine, then we can't connect. First > time > >we thought something was corrupted because we had a > >power outage, we uninstalled/reinstalled and it was > >fine until, again, we decided to reboot. We were > >thinking it was a problem with permissions, but it > >doesn't make much sense to me that it's working > fine > >before we restart. > > > >So, what can I send you to better describe my > problem? > >Do you think it could be a problem with > permissions? > >How can I test to find out what the problem might > be? > > > > > > Have you looked at the postgersql log files to see > if there are any > errors? Sounds like one possibility is that the PID > file is not getting > deleted on reboot and needs to be manually removed > so that the > postmaster restarts. We need more information to > diagnose the problem, > and the log files are a good place to start. There were a couple of things in the logs, including anomalous shutdowns. I have absolutely no idea what is relevant and what is not, so I copied everything below. I also found in the EventLogs, under System, an error saying RCManClient couldn't start because of an invalid path. Again, I have no idea if it is relevant. In any case, I copied the logs written since we rebooted (around 14:05) below. Thank you for your help, and let me know what info I can send you to help find the problem... ********* 2005-07-21 11:01:25 LOG: database system was interrupted at 2005-07-11 14:24:13 Est 2005-07-21 11:01:25 LOG: checkpoint record is at 0/D33C48 2005-07-21 11:01:25 LOG: redo record is at 0/D33C48; undo record is at 0/0; shutdown FALSE 2005-07-21 11:01:25 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 11:01:25 LOG: database system was not properly shut down; automatic recovery in progress 2005-07-21 11:01:25 LOG: record with zero length at 0/D33C88 2005-07-21 11:01:25 LOG: redo is not required 2005-07-21 11:01:25 LOG: database system is ready 2005-07-21 11:02:04 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:05 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:06 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:07 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:38 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:38 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:39 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:02:40 FATAL: password authentication failed for user "jbondc" 2005-07-21 11:03:18 FATAL: password authentication failed for user "jbondc" 2005-07-21 14:04:53 LOG: received fast shutdown request 2005-07-21 14:04:54 LOG: shutting down 2005-07-21 14:04:54 LOG: database system is shut down 2005-07-21 14:04:58 LOG: logger shutting down ******************************** 2005-07-21 11:02:36 LOG: database system was interrupted at 2005-07-21 11:01:25 Est 2005-07-21 11:02:36 LOG: checkpoint record is at 0/D33C88 2005-07-21 11:02:36 LOG: redo record is at 0/D33C88; undo record is at 0/0; shutdown TRUE 2005-07-21 11:02:36 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 11:02:36 LOG: database system was not properly shut down; automatic recovery in progress 2005-07-21 11:02:36 LOG: record with zero length at 0/D33CC8 2005-07-21 11:02:36 LOG: redo is not required 2005-07-21 11:02:36 LOG: database system is ready 2005-07-21 14:04:53 LOG: received fast shutdown request 2005-07-21 14:04:54 LOG: shutting down 2005-07-21 14:04:55 LOG: database system is shut down 2005-07-21 14:04:58 LOG: logger shutting down ********************************* 2005-07-21 14:06:10 LOG: database system was shut down at 2005-07-21 14:04:54 Est 2005-07-21 14:06:10 LOG: checkpoint record is at 0/D33D48 2005-07-21 14:06:10 LOG: redo record is at 0/D33D48; undo record is at 0/0; shutdown TRUE 2005-07-21 14:06:10 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 14:06:11 LOG: database system is ready 2005-07-21 14:28:28 LOG: received fast shutdown request 2005-07-21 14:28:28 LOG: shutting down 2005-07-21 14:28:28 LOG: database system is shut down 2005-07-21 14:28:28 LOG: logger shutting down *********************************** 2005-07-21 14:29:10 LOG: database system was shut down at 2005-07-21 14:28:28 Est 2005-07-21 14:29:10 LOG: checkpoint record is at 0/D33D88 2005-07-21 14:29:10 LOG: redo record is at 0/D33D88; undo record is at 0/0; shutdown TRUE 2005-07-21 14:29:10 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 14:29:10 LOG: database system is ready 2005-07-21 14:29:54 LOG: received fast shutdown request 2005-07-21 14:29:54 LOG: shutting down 2005-07-21 14:29:54 LOG: database system is shut down 2005-07-21 14:29:55 LOG: logger shutting down **************************************** 2005-07-21 14:29:56 LOG: database system was shut down at 2005-07-21 14:29:54 Est 2005-07-21 14:29:56 LOG: checkpoint record is at 0/D33DC8 2005-07-21 14:29:56 LOG: redo record is at 0/D33DC8; undo record is at 0/0; shutdown TRUE 2005-07-21 14:29:56 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 14:29:56 LOG: database system is ready 2005-07-21 15:04:50 LOG: received fast shutdown request 2005-07-21 15:04:50 LOG: shutting down 2005-07-21 15:04:50 LOG: database system is shut down 2005-07-21 15:04:52 LOG: logger shutting down ************************************* 2005-07-21 15:06:03 LOG: database system was shut down at 2005-07-21 15:04:50 Est 2005-07-21 15:06:03 LOG: checkpoint record is at 0/D33E48 2005-07-21 15:06:03 LOG: redo record is at 0/D33E48; undo record is at 0/0; shutdown TRUE 2005-07-21 15:06:03 LOG: next transaction ID: 3313; next OID: 33614 2005-07-21 15:06:03 LOG: database system is ready __________________________________ Yahoo! Mail for Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail
Another question on the same matter: Do I need XP SP2 for pgSQL 8.0.3 to work correctly? I have all the security patches, but not SP2. ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs
Hi again, I still can't connect. I need someone to tell me what I can try to discover what the problem is. Again, here's the problem: pgSQL 8.0.3 install on WinXP SP1. The install works fine. The DB starts and works until we restart the computer. When the machine reboots, we can't connect to the DB anymore. Our developer thinks it might be a problem with permissions on the account. I think it might be something else, especially since I can't telnet 127.0.0.1 5432 after reboot. Here are some other symptoms: If I shut down the service, then try to start it up again, sometimes it simply refuses to start again (bad user/pwd). I have to give it the user and pwd again, and then it seems to start fine. Seems like it's not remembering the info. In the EventLog, I have one application error: 2005-07-26 16:27:57 LOG: logger shutting down That seems to happen every time I shut down the service. There is no error on startup. pgSQL logs seem normal (to my untrained eye) 2005-07-26 16:24:52 LOG: database system was shut down at 2005-07-26 16:24:47 Est 2005-07-26 16:24:52 LOG: checkpoint record is at 0/D33F08 2005-07-26 16:24:52 LOG: redo record is at 0/D33F08; undo record is at 0/0; shutdown TRUE 2005-07-26 16:24:52 LOG: next transaction ID: 3313; next OID: 33614 2005-07-26 16:24:52 LOG: database system is ready 2005-07-26 16:27:56 LOG: received fast shutdown request 2005-07-26 16:27:56 LOG: shutting down 2005-07-26 16:27:56 LOG: database system is shut down 2005-07-26 16:27:57 LOG: logger shutting down 2005-07-26 16:28:17 LOG: database system was shut down at 2005-07-26 16:27:56 Est 2005-07-26 16:28:17 LOG: checkpoint record is at 0/D33F48 2005-07-26 16:28:17 LOG: redo record is at 0/D33F48; undo record is at 0/0; shutdown TRUE 2005-07-26 16:28:17 LOG: next transaction ID: 3313; next OID: 33614 2005-07-26 16:28:17 LOG: database system is ready Now, if I try to connect with pgAdmin III: An error has occured: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432? The answer to that is, apparently, no, because I can't telnet 127.0.0.1 5432... Trying to connect doesn't generate either a pg log or an event log, so I have to guess there is really nothing happening. AND I can't seem to find anything relevant on the Internet... Thanks for any help you can provide. ____________________________________________________ Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs