Re: [GENERAL] referential integrity constraints not checked inside
Christian Rank wrote: create table a (n integer); create table b (n integer); alter table a add primary key (n); alter table b add foreign key (n) references a(n); Have you considered using on delete cascade in table b? -- jimoe at sohnen-moe dot com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_xlog becomes extremely large during CREATE INDEX
On Thu, 2004-05-13 at 06:10, Tom Lane wrote: Jeffrey W. Baker [EMAIL PROTECTED] writes: Oh sure, it's bleating. Apparently my computer is too fast: I don't think the checkpoint process is completing. May 12 16:37:08 mistral postgres[506]: [174-1] LOG: server process (PID 16403) was terminated by signal 6 You need to find out why these aborts (presumably Assert failures) are occurring. Having just looked at the source code, I see that Assert messages are never sent to syslog only to stderr. So you'll want to set things up to capture the postmaster's stderr instead of discarding it. The server process aborted when the device filled up. -jwb ---(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: [GENERAL] pg_xlog becomes extremely large during CREATE INDEX
On Thu, 2004-05-13 at 09:28, Tom Lane wrote: Jeffrey W. Baker [EMAIL PROTECTED] writes: Sorry, my last mail got cut off. The server aborted because it couldn't write the xlog. Looks like I omitted this from my last mail: Selective quoting of the log output? Naughty naughty. However, that still doesn't explain how you got into the current state. Had you once had checkpoint_segments set much higher than the current value of 24? On looking at the code I see that it doesn't make any attempt to prune future log segments after a decrease in checkpoint_segments, so if a previous misconfiguration had allowed the number of future segments to get really large, that could be the root of the issue. The database where this happened was freshly initialized and this was one of its first operations. I think I will write a small example script so you can reproduce it locally. Give me a few hours or so. -jwb ---(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: [GENERAL] pg_xlog becomes extremely large during CREATE INDEX
Jeffrey W. Baker [EMAIL PROTECTED] writes: Sorry, my last mail got cut off. The server aborted because it couldn't write the xlog. Looks like I omitted this from my last mail: Selective quoting of the log output? Naughty naughty. However, that still doesn't explain how you got into the current state. Had you once had checkpoint_segments set much higher than the current value of 24? On looking at the code I see that it doesn't make any attempt to prune future log segments after a decrease in checkpoint_segments, so if a previous misconfiguration had allowed the number of future segments to get really large, that could be the root of the issue. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Cancel query based on a timeout
Hi Carl, Thanks a lot for sending me that code example. I have forwarded it to my colleague (as I said, It's not my project I'm just the one who's getting his mailbox bombarded with postgres mail :) ). He was kind of busy lately with another project so he couldn't delve into it right away. As soon as I hear from him I'll let you know if it's applicable in our situation. About your statement: /qoute I'm assuming that your ODBC stack is multithread-safe since the example requires one thread to abort the SQL statement executing in another thread. /end qoute I don't quite understand what you mean by ODBC stack and it beïng multithread-safe. The threading is done in Delphi, so is handled by our own app. Since it's not my project I haven't studied you code example in-depth, but it looks to me that there's gone some good work and thinking into it. So once again, thank you for the (much apreciated) help. Stijn Vanroye FAR Courier B.V. IT Department Weerterveld 61 6231NC Meerssen (The Netherlands) -Original Message- From: Carl E. McMillin [mailto:[EMAIL PROTECTED] Sent: dinsdag 11 mei 2004 20:03 To: Stijn Vanroye Cc: [EMAIL PROTECTED]; Bob; 'Bill Martin'; Joe Burks; verbus counts Subject: RE: [GENERAL] Cancel query based on a timeout Hi Stijn, ..By using threading we could let the client do the counting for the timeout, but we can't figure out how exactly you stop/reset a server-side proces (or query) from the client... Check out the test-scenario I've attached. It demonstrates how to use Java, JDBC, and threads to allow a timeout-thread to kill an overrunning SQL statement. Please let me know if it satisfies. I'm assuming that your ODBC stack is multithread-safe since the example requires one thread to abort the SQL statement executing in an other thread. The example uses one database (test) composed of one table (table1) having one column (field1 INTEGER). ...I would very much like to thank you for your answer/help. It's one of the most extensive ones I've got so far :-). You are very welcome! Carl |};-) -Original Message- From: Stijn Vanroye [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 12:25 AM To: [EMAIL PROTECTED] Cc: Carl E. McMillin; Roy Janssen; Hassanein Altememy Subject: RE: [GENERAL] Cancel query based on a timeout -Original Message- From: Carl E. McMillin [mailto:[EMAIL PROTECTED] Sent: maandag 10 mei 2004 17:31 To: Stijn Vanroye; [EMAIL PROTECTED] Subject: RE: [GENERAL] Cancel query based on a timeout Hi, Also Hi, We are working on a similar problem - timeouts of long-running requests. We are also currently using 7.3.4 for Postgres, but we are using Java, JDBC, and PL/PGSql. Question: Does Delphi have structured exception-handling? The solution we've found requires the ability to descriminate between exception-types and the ability to catch and throw execeptions in a safe fashion. If Delphi does have SHE, then I think our solution could be mapped into Delphi without much trouble. Delphi does indeed have some nice features for exception handling (and throwing). The solution we've come up with is to add a waiting procedure table to the database, along with some stored-procedures to manage the table. We call this the waitingproc subsystem. The waitingproc subsystem can then be used by client-side code (thru JDBC calls; ODBC should be able to handle the particular subset we are dealing with here) to detect when a server-side process overruns its alloted time. The client then has the option to abort the transaction and/or restart the server-side process. When you give the client the option to abort and/or restart the server-side process, excactly how does you client do that? Is there a certain command, or do you use something inhereted in the transaction? You see, altough your solution seems to be a very usable and not to mention creative one, it might be a little much in our case, since we only have this problem in this one perticular case with one perticular query. By using threading we could let the client do the counting for the timeout, but we can't figure out how exactly you stop/reset a server-side proces (or query) from the client. Of course, the problem we are throwing the waitingproc subsystem at is not your problem, but I think some of the core concepts/procedures are usuable. Let me know if you'd like the Java and PL/PGSql source. The Java code is fairly extensive and is mixed in with other business-logic, but I can help you thru the rough parts. I would very much like to thank you for your answer/help. It's one of the most extensive ones I've got so far :-). Actually I'm not the one tackeling this problem, I'm just the one following the postgresql mailinglist, but I've forwarded your answer to the
Re: [GENERAL] LISTEN/NOTIFY with JDBC
On Wed, 12 May 2004, Glenn Sullivan wrote: Thanks for the response. I have been using Sun's JDBC. Do I take it that I need to create and use the Postgres JDBC to get the ability to do NOTIFY/LISTEN? You have been using the JDBC API provided by Sun as a number of interfaces (java.sql.*) which are implemented behind the scenes by the postgresql JDBC driver in concrete classes. Listen/Notify is a postgresql extension not covered in the standard java.sql.* API, so must cast the standard interfaces to postgresql implementations so that you can access the additional functionality provided. For example when using the postgresql JDBC driver java.sql.Connection is actually an instance of org.postgresql.PGConnection, so you can cast your Connection to a PGConnection to use the notification API. Unfortunately the PGConnection and other extensions are either poorly documented or completely undocumented. Combining my example with a brief look at the source code for PGConnection and PGNotification should hopefully get you going. Kris Jurka ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Functionality in database or external langauge
I have some experience with following scenario: 1. Pg or other RDBMS realizes data integrity and transaction isolation, only. Potentially, only _very_ well known and oldschool rules are implemented here, for instance user sessions, which are not strictly related with real bussiness rules. Additionally, helper (updatable) views and stored functions are implemented to simplify and support the next point. 2. Something as application server realizes bussiness rules. -These processes are fully privileged (because trusted) to perform actions on data, but they do not use Pg-superuser account. Typically, there are httpd daemons with mod_perl. They could recognise user privileges and perform or refuse requested operations on data. 3. Client side realizes GUI and the user inserted data basic validation. -Typically web browsers, not trusted. We use this to realize light or not-heavy-weight database driven webs. Dennis Gearon wrote: I'd like to get people's feelings about the topic. At one extreme is to use table locking and external language queries to even do referential integerity - a la Old (present?) MySQL/PHP. A more realistic low end is to use Postgres or something more towards heavy iron (if necessary) and use referential integrity, data integrity, check cababilities. At the other end is to only allow access to normal operation of the database via procedures for only predefined operations on the data, with different levels of authority in different scripts with different users. It is a lot easier to change databases is the functionality is in the external langauge. Both the access and the data integerity and business rule enforcement is a lot better with only procedure access. What's everyone's experience with these paradigms? ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] referential integrity constraints not checked inside PL/pgSQL functions?
Hello, I came across the following problem with integrity constraints and PL/pgSQL (PostgreSQL version used: 7.4.2): I defined the following tables, constraints and data: create table a (n integer); create table b (n integer); alter table a add primary key (n); alter table b add foreign key (n) references a(n); insert into a values (1); insert into b values (1); When trying to execute delete from a; this is denied, since the integrity constraint would be violated. So far, so good. Now I defined the following function: create function f () returns void as ' begin delete from a; delete from b; return; end; ' language plpgsql; I would expect that select f(); yields an error message about constraint violation when executing 'delete from a;'. However, the function is executed without errors, and the tables a and b are empty after this operation. It seems that the validity of (integrity) constraints is not checked inside a function, only after executing a function. Is this a bug or a feature? Thanks in advance for your advice, -- Dr. Christian Rank Rechenzentrum Universität Passau Innstr. 33 D-94032 Passau GERMANY Tel.: 0851/509-1838 Fax: 0851/509-1802 PGP public key see http://www.rz.uni-passau.de/mitarbeiter/rank ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] referential integrity constraints not checked inside PL/pgSQL functions?
On Thu, May 13, 2004 at 11:41:24AM +0200, Christian Rank wrote: create function f () returns void as ' begin delete from a; delete from b; return; end; ' language plpgsql; I would expect that select f(); yields an error message about constraint violation when executing 'delete from a;'. Off the top of my head, the constraints would be checked when the transaction ends, i.e., after both the delete from a and delete from b happened. Split into 2 transactions? Cheers, Patrick ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] dbmirror
On Wed, May 12, 2004 at 05:53:05PM -0700, Gregory S. Williamson wrote: Fred -- Yes, the slave database(s) can be safely used in a R/O mode, Does it also block write transactions in those slaves? The ability for clients to write into the slave replicated tables is a problem, because it makes promoting a slave node somewhat risky. Slony-I has a trick to solve this problem, BTW. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] referential integrity constraints not checked inside
Patrick Welche wrote: On Thu, May 13, 2004 at 11:41:24AM +0200, Christian Rank wrote: create function f () returns void as ' begin delete from a; delete from b; return; end; ' language plpgsql; I would expect that select f(); yields an error message about constraint violation when executing 'delete from a;'. Off the top of my head, the constraints would be checked when the transaction ends, i.e., after both the delete from a and delete from b happened. Split into 2 transactions? Thanks for this suggestion, but I think this does not solve the issue, since according to the docs, the validity of a constraint should be checked after each statement unless this behaviour is altered with a SET CONSTRAINTS statement. Anyway, the select f(); is in my case not executed in transactional context (not embraced by START TRANSACTION; ... COMMIT;). Regards, Christian -- Dr. Christian Rank Rechenzentrum Universität Passau Innstr. 33 D-94032 Passau GERMANY Tel.: 0851/509-1838 Fax: 0851/509-1802 PGP public key see http://www.rz.uni-passau.de/mitarbeiter/rank ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_xlog becomes extremely large during CREATE INDEX
Jeffrey W. Baker [EMAIL PROTECTED] writes: Oh sure, it's bleating. Apparently my computer is too fast: I don't think the checkpoint process is completing. May 12 16:37:08 mistral postgres[506]: [174-1] LOG: server process (PID 16403) was terminated by signal 6 You need to find out why these aborts (presumably Assert failures) are occurring. Having just looked at the source code, I see that Assert messages are never sent to syslog only to stderr. So you'll want to set things up to capture the postmaster's stderr instead of discarding it. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] referential integrity constraints not checked inside PL/pgSQL functions?
Supposing you drop the delete from b; from the function, you'll find that the function fails with much the same error message you had before. Evidently that foreign key check gets _deferred_ in the context of the stored procedure. It is indeed checked; just not at the point you expect it to be checked at. -- select 'cbbrowne' || '@' || 'ntlug.org'; http://www3.sympatico.ca/cbbrowne/advocacy.html Rules of the Evil Overlord #89. After I captures the hero's superweapon, I will not immediately disband my legions and relax my guard because I believe whoever holds the weapon is unstoppable. After all, the hero held the weapon and I took it from him. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] referential integrity constraints not checked inside
Christian Rank [EMAIL PROTECTED] writes: ... according to the docs, the validity of a constraint should be checked after each statement unless this behaviour is altered with a SET CONSTRAINTS statement. Statement means interactive command in that context --- in other words, the constraints won't be checked until after control returns from your function. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] referential integrity constraints not checked inside
Tom Lane wrote: Christian Rank [EMAIL PROTECTED] writes: ... according to the docs, the validity of a constraint should be checked after each statement unless this behaviour is altered with a SET CONSTRAINTS statement. Statement means interactive command in that context --- in other words, the constraints won't be checked until after control returns from your function. Thanks very much, that clarifies this behaviour of PostgreSQL - it's definitively a feature, not a bug :-) -- Dr. Christian Rank Rechenzentrum Universität Passau Innstr. 33 D-94032 Passau GERMANY Tel.: 0851/509-1838 Fax: 0851/509-1802 PGP public key see http://www.rz.uni-passau.de/mitarbeiter/rank ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] DBI remote connection problem
When I try to connect to a remote server using DBI I get an error expected authentication request from server, but received S Has anyone encountered this? I am not quite sure what it is refering to? Sally _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] After update complete set
Hello list, Is there a way in pg to fire a function when a complete (not row by row) set is updated/inserted/deleted, for instance. update order_detail set confirmed='S' where modifieddate=current_date; Then after all the affected rows are updated, I need some code to uptaded other tables from the information of the updated rows in the details table. Thanks in advance. -- Sinceramente, Josué Maldonado. Cuando los ricos se hacen la guerra, son los pobres los que mueren. Jean Paul Sartre. Filósofo y escritor francés. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] setting local time without restarting server
Hi, Is there a function that would allow me to set the local time while the server is running? I can't seem to find anything on this... Thanks, Peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] setting local time without restarting server
On Thu, May 13, 2004 at 12:56:48 -0600, Anony Mous [EMAIL PROTECTED] wrote: Hi, Is there a function that would allow me to set the local time while the server is running? I can't seem to find anything on this... You probably want to set the timezone, not the local time. You can do that with something like: SET TIMEZONE='EST5EDT'; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] dbmirror
Does dbmirror do that? No, it does not. It also doesn't support promoting a slave database to a master; that has to be done manually, so I wouldn't consider that too big a problem. Worse in my opinion is that sequences don't get updated... so a slave that tries to do an insert on a replicated table (for example, when it gets manually promoted to master) will find the sequence not where the master left it, but where it was loaded. Every sequence has to be manually updated before the database is usable. dbmirror was never intended to be anything but a poor man's replication... and it worked remarkably well for that purpose. Now it's time to look forward to Slony-I :) Greg Andrew Sullivan wrote: On Wed, May 12, 2004 at 05:53:05PM -0700, Gregory S. Williamson wrote: Fred -- Yes, the slave database(s) can be safely used in a R/O mode, Does it also block write transactions in those slaves? The ability for clients to write into the slave replicated tables is a problem, because it makes promoting a slave node somewhat risky. Slony-I has a trick to solve this problem, BTW. A ---(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: [GENERAL] setting local time without restarting server
Thanks, but I do want to set the system time. It's actually for testing purposes. Our software looks to the db server to obtain the local date and time, however, to test various cases we need to fiddle with this value. I'd like to do it without having to first change the system time, and then re-start the postmaster. Sorry, I should have clarified this in my first e-mail. -Peter -Original Message- From: Bruno Wolff III [mailto:[EMAIL PROTECTED] Sent: May 13, 2004 2:07 PM To: Anony Mous Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] setting local time without restarting server On Thu, May 13, 2004 at 12:56:48 -0600, Anony Mous [EMAIL PROTECTED] wrote: Hi, Is there a function that would allow me to set the local time while the server is running? I can't seem to find anything on this... You probably want to set the timezone, not the local time. You can do that with something like: SET TIMEZONE='EST5EDT'; ---(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
