Re: [HACKERS] timeout implementation issues
Heh pardon me but... I was under the impression that for a transaction either all commands succeed or all commands fail, atleast according to everything I've ever read. So followign that all SETs done within the scope of a BEGIN/COMMIT pair should only take effect if the whole set finishes, if not the system shoudl roll back to the way it was before the BEGIN. I might be missing something though, I just got onto the list and there might be other parts of the thread I missed Karel Zak wrote: >On Mon, Apr 08, 2002 at 01:03:41PM -0400, Tom Lane wrote: > >>The search_path case is the main reason why I'm intent on changing >>the behavior of SET; without that, I'd just leave well enough alone. >> > > Is there more variables like "search_path"? If not, I unsure if one > item is good consideration for change others things. > >>Possibly some will suggest that search_path shouldn't be a SET variable >>because it needs to be able to be rolled back on error. But what else >>should it be? It's definitely per-session status, not persistent >> > > It's good point. Why not make it more transparent? You want > encapsulate it to standard and current SET statement, but if it's > something different why not use for it different statement? > >SET SESSION search_path TO 'something'; > > (...or something other) > >Karel > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
On Mon, Apr 08, 2002 at 12:28:18PM -0400, Peter Eisentraut wrote: > Bruce Momjian writes: > > > OK, probably good time for summarization. First, consider this: > > > > BEGIN WORK; > > SET something; > > query fails; > > SET something else; > > COMMIT WORK; > > > > Under current behavior, the first SET is honored, while the second is > > ignored because the transaction is in ABORT state. I can see no logical > > reason for this behavior. > > But that is not a shortcoming of the SET command. The problem is that the > system does not accept any commands after one command has failed in a > transaction even though it could usefully do so. > > > The jdbc timeout issue is this: > > > > > > BEGIN WORK; > > SET query_timeout=20; > > query fails; > > SET query_timeout=0; > > COMMIT WORK; > > > > In this case, with our current code, the first SET is done, but the > > second is ignored. > > Given appropriate functionality, you could rewrite this thus: > > BEGIN WORK; > SET FOR THIS TRANSACTION ONLY query_timeout=20; > query; > COMMIT WORK; If I compare Peter's and Bruce's examples the Peter is still winner :-) Sorry, but a code with "set-it-after-abort" seems ugly. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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: [HACKERS] timeout implementation issues
On Mon, Apr 08, 2002 at 01:03:41PM -0400, Tom Lane wrote: > The search_path case is the main reason why I'm intent on changing > the behavior of SET; without that, I'd just leave well enough alone. Is there more variables like "search_path"? If not, I unsure if one item is good consideration for change others things. > Possibly some will suggest that search_path shouldn't be a SET variable > because it needs to be able to be rolled back on error. But what else > should it be? It's definitely per-session status, not persistent It's good point. Why not make it more transparent? You want encapsulate it to standard and current SET statement, but if it's something different why not use for it different statement? SET SESSION search_path TO 'something'; (...or something other) Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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: [HACKERS] timeout implementation issues
Karel Zak <[EMAIL PROTECTED]> writes: > It's good point. Why not make it more transparent? You want > encapsulate it to standard and current SET statement, but if it's > something different why not use for it different statement? > SET SESSION search_path TO 'something'; But a plain SET is also setting the value for the session. What's the difference? Why should a user remember that he must use this syntax for search_path, and not for any other variables (or perhaps only one or two other ones, further down the road)? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] notification: pg_notify ?
Tom Lane wrote: > It is *not* reliable, at least not in the sense of "the message is > guaranteed to be delivered even if there's a system crash". Which is > the normal meaning of "reliable" in SQL environments. If you want that That is exactly what I mean by "reliable". Please correct me if I'm wrong but the buffer overrun problem in the new LISTEN/NOTOFY mechanism means that it is perfectly possible that sending backend may drop all or some of the pending NOTIFY messages in case of such an overrun. If this is the case then this new mechanism would be step backward in terms of functionality relative to the current implementation. There will be no guaranty even in a no-crash case. > level of reliability, you need to pass your messages by storing them > in a regular table. > That is exactly what I do in my application. I store messages in a regular table and then send a notify to other clients. But I'd like to have a guaranty that without system crash all my notifies will be delivered. I use this method when I need to send some additional information except the notice's name. Another case is similar to your cache invalidation example. The big difference is that I need to maintain a kind of cache for the large number of big tables and I need to know promptly when these tables change. I can't afford to update this cache frequently enough in case of polling. And when there is no NOTIFY delivery guaranty the only solution is polling. Occasional delivery of NOTIFY messages may only improve in some sense the polling strategy. One can not rely on them. > LISTEN/NOTIFY can optimize your message passing by avoiding unnecessary > polling of the table in the normal no-crash case. But they are not a Guaranteed delivery in the normal no-crash case avoids polling completely in case of cache invalidation scenario. DB crash recovery is a very complex task for an application. Some time a recovery is not possible at all. But for cache invalidation a DB crash is nothing more than cache reinitialisation (you will get this crash notification without LISTEN/NOTIFY message ;) Even stronger: you can't receive a crash notification with LISTEN/NOTIFY mechanism). And again, this no-crash case guaranty is already here! We don't need to do anything! > substitute for having a table, and I don't see a reason to bog them down Sure their are not substitute, and I'm not the one who proposed to extend LISTEN/NOTIFY mechanism with additional information ;) This whole thread was started to extend LISTEN/NOTIFY mechanism to support optional messages. If we are agree that LISTEN/NOTIFY is not a substitute for having a table for such a messages, then what is the purpose to reimplement this feature with a loss of functionality? > with an intermediate level of reliability that isn't buying anything. > If you mean reliability in no-crash case then it gives a lot - it eliminates need for polling completely. And once again, we already have this level of reliability. What exactly PG will get with this new LISTEN/NOTIFY mechanism? If the profit has so great value, let's implement it as an additional feature, not as a replacement of the existing one with loss of functionality. Regards Mikhail Terekhov ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
Michael Loftis writes:
> I was under the impression that for a transaction either all commands
> succeed or all commands fail, atleast according to everything I've ever
> read.
That's an urban legend.
A transaction guarantees (among other things) that all modifications to
the database with the transaction are done atomicly (either all or done or
none). This does not extend to the commands that supposedly initiate such
modifications.
Take out a database other than PostgreSQL and do
BEGIN; -- or whatever they use; might be implicit
INSERT INTO existing_table ('legal value');
barf;
COMMIT;
The INSERT will most likely succeed. The reason is that "barf" does not
modify or access the data in the database, so it does not affect the
transactional integrity of the database.
We are trying to make the same argument for SET. SET does not modify the
database, so it doesn't have to fall under transaction control.
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
This is a good bug report. I can fix pg_upgrade by adding clog files containing zeros to pad out to the proper length. However, my guess is that most people have already upgrade to 7.2.X, so there isn't much value in fixing it now. I have updated pg_upgrade CVS for 7.3, and hopefully we will have it working and well tested by the time 7.3 is released. Compressed clog was new in 7.2, so I guess it is no surprise I missed that change in pg_upgrade. In 7.3, pg_clog will be moved over from the old install, so this shouldn't be a problem with 7.3. Thanks for the report. Sorry I don't have a fix. --- Brian Hirt wrote: > I've started playing around with 7.2 on one of my development machines. > I decided to try the pg_upgrade program, something I usually never do. > > Anyway, I followed the steps in the pg_upgrade (going from 7.1.3 to > 7.2), and then when I started the database up after the upgrade finished > and vacuumed one of my tables, i get these error messages from the > postmaster. After this point I cannot restart the postmaster without > resetting the xlog. > > I've kept the PGDATA directory around incase someone thinks this is > worth looking into, i would be more than happy to help out. > > If i migrate the data over manually like a always do (pg_dump then > pg_restore), i don't have any problems. Part of the problem might be > path names for shared libraries specified in CREATE FUNCTION; I started > using pg back when it was version 6 before '$libdir' was supported and I > haven't bothered to take the absolute path names out yet -- i've just > updated it with each release (each release is installed in a different > location in case i need to roll back, and so i can test multiple version > at one time). not sure if pg_upgrade even checks for this. > > oby/pgsql@loopy pg_upgrade]$ /moby/pgsql-7.2/bin/postmaster -i -o -F -B > 256 -D/mo > DEBUG: database system was shut down at 2002-02-14 12:20:53 MST > DEBUG: checkpoint record is at 1/A710 > DEBUG: redo record is at 1/A710; undo record is at 1/A710; > shutdown TRUE > DEBUG: next transaction id: 589031; next oid: 19512 > DEBUG: database system is ready > > > > DEBUG: --Relation developer-- > DEBUG: Pages 669: Changed 0, Empty 0; Tup 51508: Vac 0, Keep 0, UnUsed > 0. > Total CPU 0.07s/0.03u sec elapsed 0.11 sec. > DEBUG: Analyzing developer > FATAL 2: read of clog file 0, offset 139264 failed: Success > DEBUG: server process (pid 17786) exited with exit code 2 > DEBUG: terminating any other active server processes > NOTICE: Message from PostgreSQL backend: > The Postmaster has informed me that some other backend > died abnormally and possibly corrupted shared memory. > I have rolled back the current transaction and am > going to terminate your database system connection and exit. > Please reconnect to the database system and repeat your query. > DEBUG: all server processes terminated; reinitializing shared memory > and semaphores > DEBUG: database system was interrupted at 2002-02-14 12:20:58 MST > DEBUG: checkpoint record is at 1/A710 > DEBUG: redo record is at 1/A710; undo record is at 1/A710; > shutdown TRUE > DEBUG: next transaction id: 589031; next oid: 19512 > DEBUG: database system was not properly shut down; automatic recovery > in progress > DEBUG: redo starts at 1/A750 > FATAL 2: read of clog file 0, offset 139264 failed: Success > DEBUG: startup process (pid 17788) exited with exit code 2 > DEBUG: aborting startup due to startup process failure > [postgres@loopy pg_upgrade]$ > [postgres@loopy pg_upgrade]$ > [postgres@loopy pg_upgrade]$ > [postgres@loopy pg_upgrade]$ df -k > Filesystem 1k-blocks Used Available Use% Mounted on > /dev/hda8 248895192496 43549 82% / > /dev/hda131079 4988 24487 17% /boot > /dev/hda5 24080660 6601476 17479184 28% /home > /dev/hda6 5044156 1930892 2857032 41% /usr > /dev/hda9 248895133875102170 57% /var > /dev/hdd1 59919196 39090008 20829188 66% /disk > oby/pgsql@loopy pg_upgrade]$ /moby/pgsql-7.2/bin/postmaster -i -o -F -B > 256 -D/mo > DEBUG: database system was interrupted being in recovery at 2002-02-14 > 12:21:06 MST > This probably means that some data blocks are corrupted > and you will have to use the last backup for recovery. > DEBUG: checkpoint record is at 1/A710 > DEBUG: redo record is at 1/A710; undo record is at 1/A710; > shutdown TRUE > DEBUG: next transaction id: 589031; next oid: 19512 > DEBUG: database system was not properly shut down; automatic recovery > in progress > DEBUG: redo starts at 1/A750 > FATAL 2: read of clog file 0, offset 139264 failed: Success > DEBUG: startup process (pid 17793) exited with exit code 2 > DEBUG: aborting startup due to
Re: [HACKERS] timeout implementation issues
Peter Eisentraut wrote:
> Michael Loftis writes:
>
> > I was under the impression that for a transaction either all commands
> > succeed or all commands fail, atleast according to everything I've ever
> > read.
>
> That's an urban legend.
>
> A transaction guarantees (among other things) that all modifications to
> the database with the transaction are done atomicly (either all or done or
> none). This does not extend to the commands that supposedly initiate such
> modifications.
>
> Take out a database other than PostgreSQL and do
>
> BEGIN; -- or whatever they use; might be implicit
> INSERT INTO existing_table ('legal value');
> barf;
> COMMIT;
>
> The INSERT will most likely succeed. The reason is that "barf" does not
> modify or access the data in the database, so it does not affect the
> transactional integrity of the database.
Ewe, we do fail that test.
> We are trying to make the same argument for SET. SET does not modify the
> database, so it doesn't have to fall under transaction control.
OK, we have three possibilities:
o All SETs are honored in an aborted transaction
o No SETs are honored in an aborted transaction
o Some SETs are honored in an aborted transaction (current)
I think the problem is our current behavior. I don't think anyone can
say our it is correct (only honor SET before the transaction reaches
abort state). Whether we want the first or second is the issue, I think.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
I wouldn't be so quick to assume that almost everyone has upgraded by now. I know we have not, at least not in production. On Tuesday 09 April 2002 02:14 pm, Bruce Momjian wrote: > This is a good bug report. I can fix pg_upgrade by adding clog files > containing zeros to pad out to the proper length. However, my guess is > that most people have already upgrade to 7.2.X, so there isn't much > value in fixing it now. I have updated pg_upgrade CVS for 7.3, and > hopefully we will have it working and well tested by the time 7.3 is > released. > > Compressed clog was new in 7.2, so I guess it is no surprise I missed > that change in pg_upgrade. In 7.3, pg_clog will be moved over from the > old install, so this shouldn't be a problem with 7.3. > > Thanks for the report. Sorry I don't have a fix. ---(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: [HACKERS] notification: pg_notify ?
Mikhail Terekhov <[EMAIL PROTECTED]> writes: > Please correct me if I'm wrong but the buffer overrun problem in the new > LISTEN/NOTOFY mechanism means that it is perfectly possible that sending > backend may drop all or some of the pending NOTIFY messages in case of such > an overrun. You would be guaranteed to get *some* notify. You wouldn't be guaranteed to receive the auxiliary info that's proposed to be added to the basic message type; also you might get notify reports for conditions that hadn't actually been signaled. > If this is the case then this new mechanism would be step > backward in terms of functionality relative to the current implementation. The current mechanism is hardly perfect; it drops multiple occurrences of the same NOTIFY. Yes, the behavior would be different, but that doesn't immediately translate to "a step backwards". > That is exactly what I do in my application. I store messages in a regular > table and then send a notify to other clients. But I'd like to have a > guaranty that without system crash all my notifies will be delivered. Please re-read the proposal. It will not break your application. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
* Mattew T. O'Connor ([EMAIL PROTECTED]) [020409 15:34]: > I wouldn't be so quick to assume that almost everyone has upgraded by now. I > know we have not, at least not in production. yeah, what he said. Test, QA and development yes, production, no. -Brad ---(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: [HACKERS] timeout implementation issues
Bruce Momjian wrote: > > OK, we have three possibilities: > > o All SETs are honored in an aborted transaction > o No SETs are honored in an aborted transaction > o Some SETs are honored in an aborted transaction (current) > > I think the problem is our current behavior. I don't think anyone can > say our it is correct (only honor SET before the transaction reaches > abort state). Whether we want the first or second is the issue, I think. I think the current state is not that bad at least is better than the first. I don't think it's a *should be* kind of thing and we shouldn't stick to it any longer. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
Tom Lane wrote: > > Karel Zak <[EMAIL PROTECTED]> writes: > > It's good point. Why not make it more transparent? You want > > encapsulate it to standard and current SET statement, but if it's > > something different why not use for it different statement? > > > SET SESSION search_path TO 'something'; > > But a plain SET is also setting the value for the session. What's > the difference? Why should a user remember that he must use this > syntax for search_path, and not for any other variables (or perhaps > only one or two other ones, further down the road)? ISTM what Karel meant is that if the search_path is a much more significant variable than others you had better express the difference using a different statement. I agree with Karel though I don't know how siginificant the varible is. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timeout implementation issues
Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > OK, we have three possibilities: > > > > o All SETs are honored in an aborted transaction > > o No SETs are honored in an aborted transaction > > o Some SETs are honored in an aborted transaction (current) > > > > I think the problem is our current behavior. I don't think anyone can > > say our it is correct (only honor SET before the transaction reaches > > abort state). Whether we want the first or second is the issue, I think. > > I think the current state is not that bad at least > is better than the first. Oops does the first mean rolling back the variables on abort ? If so I made a mistake. The current is better than the second. regards, Hiroshi Inoue ---(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: [HACKERS] Strange problem when upgrading to 7.2 with pg_upgrade.
Bradley McLean wrote: > * Mattew T. O'Connor ([EMAIL PROTECTED]) [020409 15:34]: > > I wouldn't be so quick to assume that almost everyone has upgraded by now. I > > know we have not, at least not in production. > > yeah, what he said. Test, QA and development yes, production, no. The question is anyone who has delayed installing 7.2 will be using pg_upgrade. Odds are they will not, and clearly we can't get enough testing on pg_upgrade to be sure it will work well with 7.2.X. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
Hiroshi Inoue wrote: > Hiroshi Inoue wrote: > > > > Bruce Momjian wrote: > > > > > > OK, we have three possibilities: > > > > > > o All SETs are honored in an aborted transaction > > > o No SETs are honored in an aborted transaction > > > o Some SETs are honored in an aborted transaction (current) > > > > > > I think the problem is our current behavior. I don't think anyone can > > > say our it is correct (only honor SET before the transaction reaches > > > abort state). Whether we want the first or second is the issue, I think. > > > > I think the current state is not that bad at least > > is better than the first. > > Oops does the first mean rolling back the variables on abort ? > If so I made a mistake. The current is better than the second. The second means all SET's are rolled back on abort. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timeout implementation issues
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Hiroshi Inoue wrote: > > > > > > Bruce Momjian wrote: > > > > > > > > OK, we have three possibilities: > > > > > > > > o All SETs are honored in an aborted transaction > > > > o No SETs are honored in an aborted transaction > > > > o Some SETs are honored in an aborted transaction (current) > > > > > > > > I think the problem is our current behavior. I don't think anyone can > > > > say our it is correct (only honor SET before the transaction reaches > > > > abort state). Whether we want the first or second is the issue, I think. > > > > > > I think the current state is not that bad at least > > > is better than the first. > > > > Oops does the first mean rolling back the variables on abort ? > > If so I made a mistake. The current is better than the second. > > The second means all SET's are rolled back on abort. I see. BTW what varibles are rolled back on abort currently ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
Hiroshi Inoue wrote: > > > Oops does the first mean rolling back the variables on abort ? > > > If so I made a mistake. The current is better than the second. > > > > The second means all SET's are rolled back on abort. > > I see. > BTW what varibles are rolled back on abort currently ? Currently, none, though the SET commands after the query aborts are ignored, which is effectively the same as rolling them back. BEGIN WORK; SET x=3; failed query; SET x=5; COMMIT; In this case, x=3 at end of query. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timeout implementation issues
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > > Oops does the first mean rolling back the variables on abort ? > > > > If so I made a mistake. The current is better than the second. > > > > > > The second means all SET's are rolled back on abort. > > > > I see. > > BTW what varibles are rolled back on abort currently ? > > Currently, none, ??? What do you mean by o Some SETs are honored in an aborted transaction (current) ? Is the current state different from o All SETs are honored in an aborted transaction ? regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timeout implementation issues
Hiroshi Inoue wrote: > Bruce Momjian wrote: > > > > Hiroshi Inoue wrote: > > > > > Oops does the first mean rolling back the variables on abort ? > > > > > If so I made a mistake. The current is better than the second. > > > > > > > > The second means all SET's are rolled back on abort. > > > > > > I see. > > > BTW what varibles are rolled back on abort currently ? > > > > Currently, none, > > ??? What do you mean by >o Some SETs are honored in an aborted transaction (current) > ? > Is the current state different from > o All SETs are honored in an aborted transaction > ? In the case of: BEGIN WORK; SET x=1; bad query that aborts transaction; SET x=2; COMMIT WORK; Only the first SET is done, so at the end, x = 1. If all SET's were honored, x = 2. If no SETs in an aborted transaction were honored, x would equal whatever it was before the BEGIN WORK above. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] notification: pg_notify ?
On Tue, 9 Apr 2002, Tom Lane wrote: > Mikhail Terekhov <[EMAIL PROTECTED]> writes: > > Please correct me if I'm wrong but the buffer overrun problem in the new > > LISTEN/NOTOFY mechanism means that it is perfectly possible that sending > > backend may drop all or some of the pending NOTIFY messages in case of such > > an overrun. > > You would be guaranteed to get *some* notify. You wouldn't be > guaranteed to receive the auxiliary info that's proposed to be added to > the basic message type; also you might get notify reports for conditions > that hadn't actually been signaled. I poked around the notify code and had a think about the ideas which have been put forward. I think the buffer overrun issue can be addressed by allowing users to define the importance of the notify they are making. Eg: NOTIFY HARSH If there is to be a buffer overrun, all conditions are notified and the buffer is, eventually, reset. NOTIFY SAFE (Yes, bad keywords). This on the other hand would check if there is to be a buffer overrun and (after a SendPostmasterSignal(PMSIGNAL_WAKEN_CHILDREN) fails to reduce the buffer) it would invalidate the transaction with an elog(ERROR). This can be done since AtCommit_Notify() is run before RecordTransactionCommit(). This does not deal with recovery from a crash. The only way it could is by plugging the listen and notify signals into the xlog. This seems very messy though. Gavin ---(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: [HACKERS] timeout implementation issues
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > Bruce Momjian wrote: > > > > > > Hiroshi Inoue wrote: > > > > > > Oops does the first mean rolling back the variables on abort ? > > > > > > If so I made a mistake. The current is better than the second. > > > > > > > > > > The second means all SET's are rolled back on abort. > > > > > > > > I see. > > > > BTW what varibles are rolled back on abort currently ? > > > > > > Currently, none, > > > > ??? What do you mean by > >o Some SETs are honored in an aborted transaction (current) > > ? > > Is the current state different from > > o All SETs are honored in an aborted transaction > > ? > > In the case of: > > BEGIN WORK; > SET x=1; > bad query that aborts transaction; > SET x=2; > COMMIT WORK; > > Only the first SET is done, so at the end, x = 1. If all SET's were > honored, x = 2. If no SETs in an aborted transaction were honored, x > would equal whatever it was before the BEGIN WORK above. IMHO o No SETs are honored in an aborted transaction(current) The first SET isn't done in an aborted transaction. regards, Hiroshi Inoue ---(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
[HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
Hi all,
I've attached a patch for doing BETWEEN SYM/ASYM, however it just doesn't
work!!!
test=# select 2 between 1 and 3;
?column?
--
t
(1 row)
test=# select 2 between 3 and 1;
?column?
--
f
(1 row)
test=# select 2 between symmetric 3 and 1;
ERROR: parser: parse error at or near "3"
test=# select 2 between asymmetric 3 and 1;
ERROR: parser: parse error at or near "3"
test=# select 2 not between 3 and 1;
?column?
--
t
(1 row)
test=# select 2 not between symmetric 3 and 1;
ERROR: parser: parse error at or near "3"
Can anyone see what's wrong?
Chris
Index: src/backend/parser/gram.y
===
RCS file: /projects/cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.301
diff -c -r2.301 gram.y
*** src/backend/parser/gram.y 2002/04/09 20:35:51 2.301
--- src/backend/parser/gram.y 2002/04/10 02:58:38
***
*** 346,352
IMMEDIATE, INITIALLY, INOUT,
OFF, OUT,
PATH_P, PENDANT,
! REPLACE, RESTRICT,
TRIGGER,
WITHOUT
--- 346,352
IMMEDIATE, INITIALLY, INOUT,
OFF, OUT,
PATH_P, PENDANT,
! REPLACE, RESTRICT, SYMMETRIC, ASYMMETRIC,
TRIGGER,
WITHOUT
***
*** 4916,4923
| a_expr BETWEEN b_expr AND b_expr %prec BETWEEN
{
$$ = makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=", $1, $3),
! makeA_Expr(OP, "<=", $1, $5));
}
| a_expr NOT BETWEEN b_expr AND b_expr %prec BETWEEN
{
--- 4916,4942
| a_expr BETWEEN b_expr AND b_expr %prec BETWEEN
{
$$ = makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=", $1, $3),
! makeA_Expr(OP, "<=", $1, $5));
!
! }
! | a_expr BETWEEN ASYMMETRIC b_expr AND b_expr %prec
BETWEEN
! {
! $$ = makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=", $1, $4),
! makeA_Expr(OP, "<=", $1, $6));
!
! }
! | a_expr BETWEEN SYMMETRIC b_expr AND b_expr%prec
BETWEEN
! {
! $$ = makeA_Expr(OR, NULL,
! makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=", $1, $4),
! makeA_Expr(OP, "<=", $1, $6)),
! makeA_Expr(AND, NULL,
! makeA_Expr(OP, ">=", $1, $6),
! makeA_Expr(OP, "<=", $1, $4))
! );
}
| a_expr NOT BETWEEN b_expr AND b_expr %prec BETWEEN
{
***
*** 4925,4930
--- 4944,4966
makeA_Expr(OP, "<", $1, $4),
makeA_Expr(OP, ">", $1, $6));
}
+ | a_expr NOT BETWEEN ASYMMETRIC b_expr AND b_expr %prec
+BETWEEN
+ {
+ $$ = makeA_Expr(OR, NULL,
+ makeA_Expr(OP, "<", $1, $5),
+ makeA_Expr(OP, ">", $1, $7));
+ }
+ | a_expr NOT BETWEEN SYMMETRIC b_expr AND b_expr%prec
+BETWEEN
+ {
+ $$ = makeA_Expr(AND, NULL,
+ makeA_Expr(OR, NULL,
+ makeA_Expr(OP, "<", $1, $5),
+ makeA_Expr(OP, ">", $1, $7)),
+ makeA_Expr(OR, NULL,
+ makeA_Expr(OP, "<", $1, $7),
+ makeA_Expr(OP, ">", $1, $5))
+ );
+ }
| a_expr IN in_expr
Re: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
On Wed, 10 Apr 2002, Christopher Kings-Lynne wrote: > Hi all, > > I've attached a patch for doing BETWEEN SYM/ASYM, however it just doesn't > work!!! > > test=# select 2 between 1 and 3; > ?column? > -- > t > (1 row) > > test=# select 2 between 3 and 1; > ?column? > -- > f > (1 row) > > test=# select 2 between symmetric 3 and 1; > ERROR: parser: parse error at or near "3" > test=# select 2 between asymmetric 3 and 1; > ERROR: parser: parse error at or near "3" Chris, You seem to have forgotten to update keywords.c. Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
> Chris, > > You seem to have forgotten to update keywords.c. OK - works perfectly now :) Now I'm going to play with making the SYMMERIC and ASYMMETRIC keywords less reserved... Can someone comment on my use of %prec BETWEEN? Is that still correct now that we have the extra BETWEEN forms? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
Hiroshi Inoue wrote: > > > ??? What do you mean by > > >o Some SETs are honored in an aborted transaction (current) > > > ? > > > Is the current state different from > > > o All SETs are honored in an aborted transaction > > > ? > > > > In the case of: > > > > BEGIN WORK; > > SET x=1; > > bad query that aborts transaction; > > SET x=2; > > COMMIT WORK; > > > > Only the first SET is done, so at the end, x = 1. If all SET's were > > honored, x = 2. If no SETs in an aborted transaction were honored, x > > would equal whatever it was before the BEGIN WORK above. > > IMHO > o No SETs are honored in an aborted transaction(current) > > The first SET isn't done in an aborted transaction. Well, yes, when I say aborted transaction, I mean the entire transaction, not just the part after the abort happens. All non-SET commands in the transaction are rolled back already. I can't think of a good argument for our current behavior. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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: [HACKERS] timeout implementation issues
Hiroshi Inoue wrote: > > > ??? What do you mean by > > >o Some SETs are honored in an aborted transaction (current) > > > ? > > > Is the current state different from > > > o All SETs are honored in an aborted transaction > > > ? > > > > In the case of: > > > > BEGIN WORK; > > SET x=1; > > bad query that aborts transaction; > > SET x=2; > > COMMIT WORK; > > > > Only the first SET is done, so at the end, x = 1. If all SET's were > > honored, x = 2. If no SETs in an aborted transaction were honored, x > > would equal whatever it was before the BEGIN WORK above. > > IMHO > o No SETs are honored in an aborted transaction(current) > > The first SET isn't done in an aborted transaction. I guess my point is that with our current code, there is a distinction that SETs are executed before a transaction aborts, but are ignored after a transaction aborts, even if the SETs are in the same transaction. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
On Wed, 10 Apr 2002, Christopher Kings-Lynne wrote: > > Chris, > > > > You seem to have forgotten to update keywords.c. > > OK - works perfectly now :) > > Now I'm going to play with making the SYMMERIC and ASYMMETRIC keywords less > reserved... > > Can someone comment on my use of %prec BETWEEN? Is that still correct now > that we have the extra BETWEEN forms? Yes. Have a look at the precedence table near the top of gram.y: %left UNION EXCEPT %left INTERSECT %left JOIN UNIONJOIN CROSS LEFT FULL RIGHT INNER_P NATURAL %left OR %left AND %right NOT %right '=' %nonassoc '<' '>' %nonassoc LIKE ILIKE %nonassoc ESCAPE %nonassoc OVERLAPS %nonassoc BETWEEN %nonassoc IN %left POSTFIXOP /* dummy for postfix Op rules */ [...] This is the order of precedence for rules which contain these operators. For example, if an expression contains: a AND b AND c it is evaluated as: ((a AND b) AND c) On the other hand: a OR b AND c is evaluated as: ((a OR b) AND c) since OR has a lower order of precedence. Now, consider: select 2 between asymmetric 3 and 1 Without the %prec BETWEEN 3 and 1 is given precedence over between. This will break your code. Gavin ---(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: [HACKERS] timeout implementation issues
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Take out a database other than PostgreSQL and do
> BEGIN; -- or whatever they use; might be implicit
> INSERT INTO existing_table ('legal value');
> barf;
> COMMIT;
> The INSERT will most likely succeed. The reason is that "barf" does not
> modify or access the data in the database, so it does not affect the
> transactional integrity of the database.
No; this example is completely irrelevant to our discussion. The reason
that (some) other DBMSes will allow the INSERT to take effect in the
above case is that they have savepoints, and the failure of the "barf"
command only rolls back to the savepoint not to the start of the
transaction. It's a generally-acknowledged shortcoming that we don't
have savepoints ... but this has no relevance to the question of whether
SETs should be rolled back or not. If we did have savepoints then I'd
be saying that SETs should roll back to a savepoint just like everything
else.
Please note that even in those other databases, if one replaces the
COMMIT with ROLLBACK in the above scenario, the effects of the INSERT
*will* roll back. Transpose this into current Postgres, and replace
INSERT with SET, and the effects do *not* roll back. How is that a
good idea?
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] timeout implementation issues
Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Take out a database other than PostgreSQL and do
>
> > BEGIN; -- or whatever they use; might be implicit
> > INSERT INTO existing_table ('legal value');
> > barf;
> > COMMIT;
>
> > The INSERT will most likely succeed. The reason is that "barf" does not
> > modify or access the data in the database, so it does not affect the
> > transactional integrity of the database.
>
> No; this example is completely irrelevant to our discussion. The reason
Actually, we could probably prevent transaction abort on syntax(yacc)
errors, but the other errors like mistyped table names would be hard to
prevent a rollback, so I guess we just roll back on any error.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup.| Drexel Hill, Pennsylvania 19026
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout implementation issues
Bruce Momjian wrote: > > Hiroshi Inoue wrote: > > > > ??? What do you mean by > > > >o Some SETs are honored in an aborted transaction (current) > > > > ? > > > > Is the current state different from > > > > o All SETs are honored in an aborted transaction > > > > ? > > > > > > In the case of: > > > > > > BEGIN WORK; > > > SET x=1; > > > bad query that aborts transaction; > > > SET x=2; > > > COMMIT WORK; > > > > > > Only the first SET is done, so at the end, x = 1. If all SET's were > > > honored, x = 2. If no SETs in an aborted transaction were honored, x > > > would equal whatever it was before the BEGIN WORK above. > > > > IMHO > > o No SETs are honored in an aborted transaction(current) > > > > The first SET isn't done in an aborted transaction. > > I guess my point is that with our current code, there is a distinction > that SETs are executed before a transaction aborts, but are ignored > after a transaction aborts, even if the SETs are in the same > transaction. Not only SET commands but also most commands are ignored after a transaction aborts currently. SET commands are out of transactional control but it doesn't mean they are never ignore(rejecte)d. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] timeout implementation issues
Bruce Momjian <[EMAIL PROTECTED]> writes: > In the case of: > BEGIN WORK; > SET x=1; > bad query that aborts transaction; > SET x=2; > COMMIT WORK; > Only the first SET is done, so at the end, x = 1. Perhaps even more to the point: SET x=0; BEGIN; SET x=1; bad query; SET x=2; ROLLBACK; Now x=1. How is this sensible? regards, tom lane ---(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: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > Can someone comment on my use of %prec BETWEEN? Is that still correct now > that we have the extra BETWEEN forms? Looks fine. AFAICS we want all these forms to have the binding precedence assigned to BETWEEN. If you don't do the %prec thing then the productions will have the precedence of their rightmost terminal symbol, ie, AND, ie, wrong. regards, tom lane ---(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: [HACKERS] timeout implementation issues
Bruce Momjian <[EMAIL PROTECTED]> writes: > Actually, we could probably prevent transaction abort on syntax(yacc) > errors, but the other errors like mistyped table names would be hard to > prevent a rollback, so I guess we just roll back on any error. I don't think that what we categorize as an error or not is very relevant to the discussion, either. The real point is: should SET have rollback behavior similar to other SQL commands, or not? If we had savepoints, or ignorable syntax errors, or other frammishes this question would still be the same. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] BETWEEN SYMMETRIC/ASYMMETRIC
> "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > > Can someone comment on my use of %prec BETWEEN? Is that still > correct now > > that we have the extra BETWEEN forms? > > Looks fine. AFAICS we want all these forms to have the binding > precedence assigned to BETWEEN. If you don't do the %prec thing > then the productions will have the precedence of their rightmost > terminal symbol, ie, AND, ie, wrong. OK, I've proven that I cannot move the SYM/ASYM keywords anything lower than totally reserved without causing shift/reduce errors. Is this acceptable? Also, Tom (or anyone): in regards to your previous email, should I just go back to using opt_symmetry to shorten the number of productions, since I have to make them reserved words anyway? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] timeout implementation issues
... > Please note that even in those other databases, if one replaces the > COMMIT with ROLLBACK in the above scenario, the effects of the INSERT > *will* roll back. Transpose this into current Postgres, and replace > INSERT with SET, and the effects do *not* roll back. How is that a > good idea? Well, as you should have concluded by now, "good" is not the same for everyone ;) Frankly, I've been happy with the current SET behavior, but would also be willing to consider most of the alternatives which have been suggested, including ones you have dismissed out of hand. Constraints which seem to have been imposed include: 1) All commands starting with "SET" must have the same transactional semantics. I'll agree that it might be nice for consistancy, but imho is not absolutely required. 2) No commands which could be expected to start with "SET" will start with some other keyword. If we do have "set class" commands which have different transactional semantics, then we could explore alternative syntax for specifying each category. 3) "SET" commands must respect transactions. I'm happy with the idea that these commands are out of band and take effect immediately. And if they take effect even in the middle of a failing/failed transaction, that is OK too. The surrounding code would have reset the values anyway, if necessary. I do have a concern about how to implement some of the SET commands if we *do* respect transactional semantics. For example, SET TIME ZONE saves the current value of an environment variable (if available), and would need *at least* a "before transaction" and "after transaction started" pair of values. How would we propagate SET variables to transaction-specific structures, clearing or resetting them later? Right now these variables are pretty independent and can be accessed through global storage; having transactional semantics means that the interdependencies between different variable types in the SET handlers may increase. - Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
