[HACKERS] Interesting issue with SFR in PL/pgSQL ...
I am about to port a large database application from 7.4.x* to 8.0 (mainly to test 8.0). There is an interesting thing I have come across: CREATE OR REPLACE FUNCTION xy(int4) RETURNS SETOF RECORD AS ' DECLARE v_isALIAS FOR $1; v_loop int4; v_rec RECORD; BEGIN v_loop := 0; SELECT INTO v_rec 0; WHILE (v_loop v_is) LOOP SELECT INTO v_rec v_loop; v_loop := v_loop + 1; RETURN NEXT v_rec; END LOOP; RETURN NEXT v_rec; RETURN v_rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM xy(0) AS (id int4); SELECT * FROM xy(1) AS (id int4); This function works nicely in 7.4.x (even without the last RETURN NEXT). 8.0 returns an error. [EMAIL PROTECTED] tmp]$ psql microtec -h epi t.sql ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near v_rec at character 324 ERROR: function xy(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ERROR: function xy(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. 7.4.1 works nicely ... [EMAIL PROTECTED] tmp]$ psql microtec -h epi -p t.sql CREATE FUNCTION id 0 (1 row) id 0 0 (2 rows) I have tried to look it up in the source code (gramy.y line 1144) but I don't really know what the new check which has been added in 8.0 is good for. Can anybody give me a hint how it is supposed to be? Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Disabling bgwriter on my notebook
Tom Lane wrote: There is some debug output available from the ARC code, but I dunno if its output is actually useful ;-). Try http://developer.postgresql.org/docs/postgres/runtime-config.html#GUC-DEBUG-SHARED-BUFFERS debug_shared_buffers (integer) Number of seconds between ARC reports. If set greater than zero, emit ARC statistics to the log every so many seconds. Zero (the default) disables reporting. It seems it doesn't really do, what it says. Output is only sent during query execution, no output is sent when no query is active, so it's really hard to say what happens to buffers during idle time. Aside from that I don't believe that the output can answer questions about the efficiency of bgwriter... DEBUG: ARC T1target= 194 B1len= 779 T1len= 180 T2len= 820 B2len= 208 DEBUG: ARC total = 99% B1hit= 18% T1hit= 6% T2hit= 75% B2hit= 0% DEBUG: ARC clean buffers at LRU T1= 180 T2= 820 Anyone? Regarding current default settings - at this page: http://developer.postgresql.org/~wieck/vacuum_cost/ Jan, you used different settings for tests here. Can you explain your point of view? bgwriter_delay = 50 (now default 200) bgwriter_percent = 2(now default 1) bgwriter_maxpages = 200 (now default 100) Regards, Michael Paesold ---(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] Log line prefix on win32
On my german Windows XP Professional, %t in log_line_prefix produces 2004-09-18 14:23:26 Westeuropische Sommerzeit This is rather long and ugly. It is already on the open item list: * shorten timezone for %t log_line_prefix Additionally I would suggest something like %z. Most of the time, time zone information is irrelevant for me in server logs. Best Regards, Michael Paesold ---(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] pg_dump --exclude-schema=foo
Quoth [EMAIL PROTECTED] (Tom Lane): Chris Browne [EMAIL PROTECTED] writes: We have discovered an interesting locking scenario with Slony-I that is pointing to a use for the ability to exclude certain schemas from pg_dump. The situation is that when a full pg_dump kicks off, a Slony-I create sync event, which expects to LOCK slony_schema.sl_event;, is blocked from getting the exclusive lock that it desires. Perhaps this should be seen as a Slony bug. Does it really need an AccessExclusive lock, or would an Exclusive lock do? You could be right; that's still To Be Determined. It nonetheless appears like a useful idea in general to be able to specify inclusion/exclusion of multiple schemas. -- (reverse (concatenate 'string gro.mca @ enworbbc)) http://www.ntlug.org/~cbbrowne/languages.html You can only examine 10 levels of pushdown, because that's all the fingers you have to stick in the listing. -- Anonymous programmer - TOPS-10 Crash Analysis Guide ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Disabling bgwriter on my notebook
Hi everybody, I have started testing PostgreSQL on windows. Now that I have managed to import a dump of one of our production databases to postgres on my notebook, there is one issue that came up immediatly after running VACUUM FULL: pgwriter is keeping my disks busy and disturbs me :-(. The more I like pgwriter for a production system, the less I like it on my desktop during developement. So I thought I would just be able to disable this, but that seems not the case. #bgwriter_delay = 200 # 10-5000 milliseconds #bgwriter_percent = 1 # 1-100% of dirty buffers #bgwriter_maxpages = 100 # 1-1000 buffers max at once bgwriter_percent can't be set to 0, neither can bgwriter_maxpages be, nor can I set bgwriter_delay to something like 15 or 30 minutes... For sympathy or pity for developers who are using postgres on a laptop or desktop system, I would suggest that this is classified as a convenience bug and fixed in a reasonable way. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
Hi all, I was implement the transaction idle timeout function in PostgreSQL (version 7.4.5 and 8.0.0beta2) test=# SET trans_idle_timeout=10; SET test=# BEGIN; BEGIN (ps) ... postgres: sygma test [local] idle in transaction (3) postgres: sygma test [local] idle in transaction (2) postgres: sygma test [local] idle in transaction (1) postgres: sygma test [local] idle in transaction (aborted) test=# INSERT INTO test (a,b) VALUES (1,'a'); ERROR: current transaction is aborted, commands ignored until end of transaction block The code is very simple and stable. Changed files: include/storage/proc.h backend/libpq/pqcomm.c backend/libpq/be-secure.c backend/utils/misc/guc.c TODO: SSL connection, manual, psql help tab-competition Do you interest it? Regards, Sygma ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] tweaking MemSet() performance - 7.4.5
On Sep 17, 2004, at 3:55 PM, Tom Lane wrote: Marc Colosimo [EMAIL PROTECTED] writes: I'm using 7.4.5 on Mac OS X (G5) and was profiling it to see why it is SO SLOW at committing inserts and deletes into a large database. One of the many slowdowns was from MemSet. I found an old (2002) thread about this and retried the tests (see below). The main point is that the system memset crushes pg's!! Hmm. I tried to duplicate this on my G4 laptop, and found that they were more or less on a par for small-to-middling block sizes (using gcc -O2). Darwin's memset code must have some additional tweaks for use on G5 hardware. Good for Apple --- this is the sort of thing that OS vendors *ought* to be doing. The fact that we can beat the system memset on so many platforms is an indictment of those platforms. Is it possible to add a define to call the system memset at build time! This probably isn't the case on other systems. Feel free to hack the definition of MemSet in src/include/c.h. See the comments for it for more context. Note that for small compile-time-constant block sizes (a case your test program doesn't test, but it's common in pgsql), gcc with a sufficiently high optimization setting can unroll the loop into a linear sequence of words zeroings. I would expect that to beat the system memset up to a few dozen words, no matter how tense the memset coding is. So you probably want to think in terms of reducing MEMSET_LOOP_LIMIT rather than diking out the macro code altogether. Or maybe reduce MemSet to memset(...) but leave MemSetAligned and/or MemSetTest/MemSetLoop as-is. In any case, reporting results without mentioning the compiler and optimization level in use isn't going to convince anybody ... Oops, I used the same setting as in the old hacking message (-O2, gcc 3.3). If I understand what you are saying, then it turns out yes, PG's MemSet is faster for smaller blocksizes (see below, between 32 and 64). I just replaced the whole MemSet with memset and it is not very low when I profile. I could squeeze more out of it if I spent more time trying to understand it (change MEMSET_LOOP_LIMIT to 32 and then add memset after that?). I'm now working one understanding Spin Locks and friends. Putting in a sync call (in s_lock.h) is really a time killer and bad for performance (it takes up 35 cycles). run on a single processor G5 (1.8Gz, other was on a DP 2Gz G5) pgMemSet: * 4 0.070u 0.000s 0:00.15 46.6% 0+0k 0+0io 0pf+0w * 8 0.090u 0.000s 0:00.16 56.2% 0+0k 0+0io 0pf+0w * 16 0.120u 0.000s 0:00.17 70.5% 0+0k 0+0io 0pf+0w * 32 0.180u 0.000s 0:00.29 62.0% 0+0k 0+0io 0pf+0w * 64 0.450u 0.000s 0:00.92 48.9% 0+0k 0+0io 0pf+0w memset: * 4 0.170u 0.010s 0:00.44 40.9% 0+0k 0+0io 0pf+0w * 8 0.190u 0.000s 0:00.42 45.2% 0+0k 0+0io 0pf+0w * 16 0.190u 0.010s 0:00.39 51.2% 0+0k 0+0io 0pf+0w * 32 0.200u 0.000s 0:00.39 51.2% 0+0k 0+0io 0pf+0w * 64 0.260u 0.000s 0:00.38 68.4% 0+0k 0+0io 0pf+0w Marc ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Wed, 15 Sep 2004 13:50:43 -0400, [EMAIL PROTECTED] (Tom Lane) wrote: Well, (a) I ran out of time, and (b) I wasn't sure what the most convenient API would be. Should we create something that packages together a Parse and a Describe Statement, or expose those as separate things? There's surely no technical difficulty once you've got some consensus on what the API should look like. regards, tom lane Having a separate Parse and Describe would fit neatly with the ODBC model. Hopefully the ODBC driver will get worked on in the medium term to use libpq. Regards, Gary. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Interesting issue with SFR in PL/pgSQL ...
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: CREATE OR REPLACE FUNCTION xy(int4) RETURNS SETOF RECORD AS ' ... RETURN v_rec; ... ' LANGUAGE 'plpgsql'; ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near v_rec at character 324 You were never supposed to do that, although prior releases did not check for the mistake. See http://www.postgresql.org/docs/7.4/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING : When a PL/pgSQL function is declared to return SETOF sometype, the : procedure to follow is slightly different. In that case, the individual : items to return are specified in RETURN NEXT commands, and then a final : RETURN command with no argument is used to indicate that the function : has finished executing. ERROR: function xy(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. 8.0 not only detects the error, but does so during CREATE FUNCTION (because it's treated as a syntax error). regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
On Fri, Sep 17, 2004 at 08:47:02AM +0200, Szima G?bor wrote: I was implement the transaction idle timeout function in PostgreSQL (version 7.4.5 and 8.0.0beta2) It sounds interesting to me (for use in libpqxx, the C++ API), but perhaps for a slightly unusual reason. When a connection to the backend is lost just as you're waiting for the result of a COMMIT, you can't be sure if the transaction was rolled back or not. If I could know (not influence, just know) when a transaction times out, then I could wait for this amount of time, reconnect to the backend, and check for some record left in a special table by the transaction. If it's not there, I'll know for sure (insofar as anything can ever be sure) that the transaction was not committed. This is still guesswork in the current situation. There's one assumption, however, and that is that the transaction will time out even if (part of) the timeout time was spent processing rather than waiting for a command. I'm not sure how many people would be willing to pay that price. Perhaps a connection timeout would be more appropriate for this sort of thing. Jeroen ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tweaking MemSet() performance - 7.4.5
Marc Colosimo wrote: Oops, I used the same setting as in the old hacking message (-O2, gcc 3.3). If I understand what you are saying, then it turns out yes, PG's MemSet is faster for smaller blocksizes (see below, between 32 and 64). I just replaced the whole MemSet with memset and it is not very low when I profile. Could you check what the OS-X memset function does internally? One trick to speed up memset it to bypass the cache and bulk-write directly from write buffers to main memory. i386 cpus support that and in microbenchmarks it's 3 times faster (or something like that). Unfortunately it's a loss in real-world tests: Typically a structure is initialized with memset and then immediately accessed. If the memset bypasses the cache then the following access will cause a cache line miss, which can be so slow that using the faster memset can result in a net performance loss. I could squeeze more out of it if I spent more time trying to understand it (change MEMSET_LOOP_LIMIT to 32 and then add memset after that?). I'm now working one understanding Spin Locks and friends. Putting in a sync call (in s_lock.h) is really a time killer and bad for performance (it takes up 35 cycles). That's the price you pay for weakly ordered memory access. Linux on ppc uses eieio, on ppc64 lwsync is used. Could you check if they are faster? -- Manfred ---(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: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: It sounds interesting to me (for use in libpqxx, the C++ API), but perhaps for a slightly unusual reason. When a connection to the backend is lost just as you're waiting for the result of a COMMIT, you can't be sure if the transaction was rolled back or not. If I could know (not influence, just know) when a transaction times out, then I could wait for this amount of time, reconnect to the backend, and check for some record left in a special table by the transaction. If it's not there, I'll know for sure (insofar as anything can ever be sure) that the transaction was not committed. This is still guesswork in the current situation. I don't see any reason for guesswork. Remember the PID of the backend you were connected to. On reconnect, look in pg_stat_activity to see if that backend is still alive; if so, sleep till it's not. Then check to see if your transaction committed or not. No need for anything so dangerous as a timeout. 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: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
On Sat, Sep 18, 2004 at 12:43:05PM -0400, Tom Lane wrote: I don't see any reason for guesswork. Remember the PID of the backend you were connected to. On reconnect, look in pg_stat_activity to see if that backend is still alive; if so, sleep till it's not. Then check to see if your transaction committed or not. No need for anything so dangerous as a timeout. I didn't know you could do that! I'll look into it. Thanks. Jeroen ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] tweaking MemSet() performance - 7.4.5
Manfred Spraul [EMAIL PROTECTED] writes: That's the price you pay for weakly ordered memory access. Linux on ppc uses eieio, on ppc64 lwsync is used. Could you check if they are faster? I recall looking at lwsync and being concerned about portability problems --- older assemblers will fail to recognize it. I'd want to see some hard evidence that changing sync to lwsync would be a significant performance win before taking any portability risk here. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
On Sat, Sep 18, 2004 at 12:43:05PM -0400, Tom Lane wrote: I don't see any reason for guesswork. Remember the PID of the backend you were connected to. On reconnect, look in pg_stat_activity to see if that backend is still alive; if so, sleep till it's not. Then check to see if your transaction committed or not. No need for anything so dangerous as a timeout. Looks like that only works if stats_command_string is set (and backend version is at least 7.2), correct? I couldn't find this table in the online documentation, but can I assume that the query will have finished executing (whether for good or for bad) when its current_query is either empty or null? Jeroen ---(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] libpq and prepared statements progress for 8.0
On Sep 17, 2004, at 6:23 PM, Oliver Jowett wrote:
template1= prepare s1(int) as select typname from pg_type where oid
= $1;
PREPARE
template1= execute s1('16');
typname -
bool
(1 row)
You're still telling it the type via that int.
Regards,
David
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: On Sat, Sep 18, 2004 at 12:43:05PM -0400, Tom Lane wrote: I don't see any reason for guesswork. Remember the PID of the backend you were connected to. On reconnect, look in pg_stat_activity to see if that backend is still alive; if so, sleep till it's not. Looks like that only works if stats_command_string is set (and backend version is at least 7.2), correct? No, stats_command_string need not be set, only stats_start_collector. I couldn't find this table in the online documentation, http://www.postgresql.org/docs/7.4/static/monitoring-stats.html#MONITORING-STATS-VIEWS-TABLE but can I assume that the query will have finished executing (whether for good or for bad) when its current_query is either empty or null? No, because of the reporting delay. I would recommend waiting for the backend's row in pg_stat_activity to disappear entirely. Under normal circumstances that should occur quickly. If there's a communications problem, it might take awhile to detect connection loss ... but if there's a communications problem, you probably aren't going to be able to open a new connection, either. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
On Sat, Sep 18, 2004 at 02:32:32PM -0400, Tom Lane wrote: No, because of the reporting delay. I would recommend waiting for the backend's row in pg_stat_activity to disappear entirely. Under normal circumstances that should occur quickly. If there's a communications problem, it might take awhile to detect connection loss ... but if there's a communications problem, you probably aren't going to be able to open a new connection, either. Unfortunately, a communications problem is exactly the kind of scenario we were talking about in the first place! Might be a misguided firewall, for instance. (In which case we'd want the TCP connection to time out quickly on the server as well to avoid piling up dead backends, but that's another matter). BTW is there really no other way to see if a query (for the same user who's looking) is still executing? It'd be really helpful if the check could be done from the same client process that lost the connection in the first place. Jeroen ---(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] transaction idle timeout in 7.4.5 and 8.0.0beta2
On Sat, Sep 18, 2004 at 02:32:32PM -0400, Tom Lane wrote: No, stats_command_string need not be set, only stats_start_collector. BTW, I've got this set (I'm even running as postgres) but still I get the command string not enabled message instead of current_query. :( Jeroen ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: On Sat, Sep 18, 2004 at 02:32:32PM -0400, Tom Lane wrote: No, stats_command_string need not be set, only stats_start_collector. BTW, I've got this set (I'm even running as postgres) but still I get the command string not enabled message instead of current_query. :( It has to be set in the backend you want the info from ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
On Sat, Sep 18, 2004 at 03:41:24PM -0400, Tom Lane wrote: No, stats_command_string need not be set, only stats_start_collector. BTW, I've got this set (I'm even running as postgres) but still I get the command string not enabled message instead of current_query. :( It has to be set in the backend you want the info from ... I set it in /etc/postgresql/postgresql.conf; then I restarted the postmaster just to be sure, and checked from psql. Same message, even about the same session: [EMAIL PROTECTED]:~$ sudo su postgres -c 'postgresql-8.0/bin/psql template1' Password: Welcome to psql 8.0.0beta1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename |current_query | query_start ---+---+-+--+--+--+- 1 | template1 | 11559 |1 | postgres | command string not enabled | (1 row) template1=# show stats_start_collector; stats_start_collector --- on (1 row) Jeroen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: On Sat, Sep 18, 2004 at 02:32:32PM -0400, Tom Lane wrote: If there's a communications problem, it might take awhile to detect connection loss ... but if there's a communications problem, you probably aren't going to be able to open a new connection, either. Unfortunately, a communications problem is exactly the kind of scenario we were talking about in the first place! Might be a misguided firewall, for instance. (In which case we'd want the TCP connection to time out quickly on the server as well to avoid piling up dead backends, but that's another matter). Well, I think it would time out quickly --- anyway on the order of minutes not hours. By hypothesis, the situation you're worried about is where the backend was unable to send you a COMMIT acknowledgement message. The kernel is going to realize that it didn't get an ACK back, and is going to retry a few times, and is then going to declare the connection lost. The case where you may have a very long delay before detection of connection loss is where the backend is sitting idle with nothing to send. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq and prepared statements progress for 8.0
David Wheeler wrote:
On Sep 17, 2004, at 6:23 PM, Oliver Jowett wrote:
template1= prepare s1(int) as select typname from pg_type where oid
= $1;
PREPARE
template1= execute s1('16');
typname -
bool
(1 row)
You're still telling it the type via that int.
Well, obviously. I haven't modified the backend code to accept 'unknown'
in PREPARE..
My point was the client does *not* need to know the type inferred by the
PREPARE in the 'unknown' case to make use of the resulting statement. It
can pass all parameters as text and use the type inference that happens
on EXECUTE -- as is happening in the EXECUTE quoted above.
-O
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
Jeroen T. Vermeulen [EMAIL PROTECTED] writes: On Sat, Sep 18, 2004 at 03:41:24PM -0400, Tom Lane wrote: No, stats_command_string need not be set, only stats_start_collector. BTW, I've got this set (I'm even running as postgres) but still I get the command string not enabled message instead of current_query. :( Oh, we're talking at cross-purposes. I was saying that you only need stats_start_collector in order to have a row in pg_stat_activity indicating that the backend is still there. If you're going to insist on examining the query column rather than just the PID, then yes you need stats_command_string. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] transaction idle timeout in 7.4.5 and 8.0.0beta2
On Sat, Sep 18, 2004 at 04:05:26PM -0400, Tom Lane wrote: Well, I think it would time out quickly --- anyway on the order of minutes not hours. By hypothesis, the situation you're worried about is where the backend was unable to send you a COMMIT acknowledgement message. The kernel is going to realize that it didn't get an ACK back, and is going to retry a few times, and is then going to declare the connection lost. The case where you may have a very long delay before detection of connection loss is where the backend is sitting idle with nothing to send. That's one load off my mind, thanks. Jeroen ---(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] libpq and prepared statements progress for 8.0
On Sep 18, 2004, at 1:09 PM, Oliver Jowett wrote: Well, obviously. I haven't modified the backend code to accept 'unknown' in PREPARE.. Right, and that's what we're looking for. My point was the client does *not* need to know the type inferred by the PREPARE in the 'unknown' case to make use of the resulting statement. It can pass all parameters as text and use the type inference that happens on EXECUTE -- as is happening in the EXECUTE quoted above. Yes, that's fine, but it's PREPARE that's at issue here, not EXECUTE. Regards, David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
David Wheeler wrote: On Sep 18, 2004, at 1:09 PM, Oliver Jowett wrote: Well, obviously. I haven't modified the backend code to accept 'unknown' in PREPARE.. Right, and that's what we're looking for. My point was the client does *not* need to know the type inferred by the PREPARE in the 'unknown' case to make use of the resulting statement. It can pass all parameters as text and use the type inference that happens on EXECUTE -- as is happening in the EXECUTE quoted above. Yes, that's fine, but it's PREPARE that's at issue here, not EXECUTE. I think you misunderstand what I'm saying. Tom reckons that PREPARE (at the SQL level) taking unknown types is not useful as there is no feedback mechanism along the lines of the V3 protocol Describe messages to let the client find out what types were inferred by the PREPARE. I am saying this doesn't matter as the client can still use the resulting statement just fine without knowing the types. So allowing 'unknown' in PREPARE *is* useful. Not that I'm volunteering to implement it, though -- the JDBC driver does not need this functionality and I'm way too short on time anyway :( -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq and prepared statements progress for 8.0
Oliver Jowett [EMAIL PROTECTED] writes: Tom reckons that PREPARE (at the SQL level) taking unknown types is not useful as there is no feedback mechanism along the lines of the V3 protocol Describe messages to let the client find out what types were inferred by the PREPARE. I am saying this doesn't matter as the client can still use the resulting statement just fine without knowing the types. So allowing 'unknown' in PREPARE *is* useful. Well, that was not quite my point, but I guess I wasn't clear. My reasoning was more like this: 1. What we have now doesn't do what DBD::Pg needs. 2. We can fix it with some-small-amount-of-work in libpq (to add some API), or with some-probably-also-small-amount-of-work in the backend (to kluge up SQL PREPARE to allow unknown). 3. The libpq-side solution is more generally useful, because it can support feedback about the resolved datatypes. 4. Therefore, we should fix it in libpq. Note that point 3 is not dependent on whether DBD::Pg in particular needs this functionality --- somebody out there certainly will. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] SAVEPOINT SQL conformance
Developer docs have this (in SAVEPOINT command reference): SQL requires a savepoint to be destroyed automatically when another savepoint with the same name is established. In PostgreSQL, the old savepoint is kept, though only the more recent one will be used when rolling back or releasing. (Releasing the newer savepoint will cause the older one to again become accessible to ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT.) I read through the code in transam/xact.c recently. Now thinking about it again, I am wondering, if this non-standard behaviour is really good. I have found at least one case against it: Imagine a program that wants to insert some (hundret/thousand) rows into a table. The program expects some rows to be duplicates, but does not know which, so it will just try... BEGIN; SAVEPOINT a; INSERT INTO ... SAVEPOINT a; INSERT INTO ... SAVEPOINT a; ... (encountering an error it would just ROLLBACK TO a;) According to the standard this is exactly the same as: BEGIN; SAVEPOINT a; INSERT INTO ... RELEASE SAVEPOINT a; SAVEPOINT a; INSERT INTO ... If the first example code is used (which I would use if I did not think about postgresql's exception), the subxact state stack in xact.c will grow and grow and grow... whereas in the case of compliance with the standard, it will not. (or if you use the second example). I have found some discussion in the archives that could explain, why it's reasonable that postgres does not conform to the standard (although it's probably not). Bruce Momjian wrote: And consider this case: BEGIN; ... SAVEPOINT x; SELECT func_call(); SELECT func_call(); COMMIT; Now if func_call has a savepoint, it is really nested because it can't know whether the savepoint X will be used to roll back, so its status is dependent on the status of X. Now, if we used savepoints in func_call, what happens in the second function call when we define a savepoint with the same name? I assume we overwrite the original, but using nested transaction syntax seems much clearer. Weird things can happen if savepoints have the same name accidentially. Nevertheless, this is true in any case, wether a savepoint is overwritten by a savepoint with the same name or not -- the other part will not know of the first savepoint -- which will cause problems eventually. If nobody can give a really good reason for the current behaviour, I would really suggest to change to standard compliance. Best Regards, Michael Paesold P.S. I know that there is still the problem of shared memory growth because of the transaction id locks, but lets focus on one problem at a time :-). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] SAVEPOINT SQL conformance
Michael Paesold [EMAIL PROTECTED] writes: If the first example code is used (which I would use if I did not think about postgresql's exception), the subxact state stack in xact.c will grow and grow and grow... whereas in the case of compliance with the standard, it will not. This is fairly irrelevant though, as the state stack entry is only a small part of the resources consumed by an uncommitted subtransaction. I don't really think it outweighs the argument you quoted about accidental collisions of savepoint names causing problems. On the other hand, we do have provisions in the code for savepoint naming levels, and so maybe a better answer to the collision issue is to support savepoint levels more completely. (But that's not standard either.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SAVEPOINT SQL conformance
Michael Paesold wrote: BEGIN; SAVEPOINT a; INSERT INTO ... SAVEPOINT a; INSERT INTO ... SAVEPOINT a; ... (encountering an error it would just ROLLBACK TO a;) According to the standard this is exactly the same as: BEGIN; SAVEPOINT a; INSERT INTO ... RELEASE SAVEPOINT a; SAVEPOINT a; INSERT INTO ... While that's true in this particular case, you can't do that transformation in the general case. Consider: BEGIN SAVEPOINT a -- work SAVEPOINT b -- work SAVEPOINT a -- work ROLLBACK TO b -- work This is valid: the standard says that the second SAVEPOINT a destroys and recreates the savepoint a, but doesn't say that it destroys intervening savepoints. In contrast, RELEASE SAVEPOINT explicitly says that it destroys the specified savepoint and all savepoints established since the specified savepoint. If you converted the second SAVEPOINT a into RELEASE SAVEPOINT a; SAVEPOINT a then savepoint b would be incorrectly destroyed. It'd work for the (common?) case where there are no intervening savepoints, though. -O ---(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] SAVEPOINT SQL conformance
Tom Lane wrote: This is fairly irrelevant though, as the state stack entry is only a small part of the resources consumed by an uncommitted subtransaction. I don't really think it outweighs the argument you quoted about accidental collisions of savepoint names causing problems. Perhaps I am wrong, but I think the problem of name collision exists anyway, at least to some extent. The current behaviour will help in this case: BEGIN; ... SAVEPOINT a; SELECT func(); ... COMMIT; where func does: SAVEPOINT a; RELEASE or ROLLBACK TO a; But it will not help, if func only does: SAVEPOINT a; on error ROLLBACK TO a; (but no release path) Then, if an error occurs after the function call, an the programm executes ROLLBACK TO a; it will rollback to a state that existed inside the function... rather bad again. And... in PL/pgSQL you will use EXCEPTION blocks rather than SAVEPOINT directly... will there are still the other languages. I just wanted to show that it is still not _that_ save to use colliding savepoint names. Regards, Michael Paesold ---(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
