[HACKERS] v3 protocol string encoding
Couple of quick protocol questions: 1) What encoding is used for strings sent and received during the startup phase? I can set client_encoding to a known value as a parameter in the startup packet, but the protocol spec doesn't appear to say how the startup packet itself and the various strings sent/received during startup (e.g. authentication, error messages) are encoded. 2) At what point in the stream does a client_encoding change take effect -- immediately after the corresponding ParameterStatus message, or at some other point? -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested xacts: looking for testers and review
On Sat, 29 May 2004, Alvaro Herrera wrote: On Sat, May 29, 2004 at 08:25:27AM -0700, Stephan Szabo wrote: BTW: For the deferred trigger stuff, I am guessing you haven't touched that at all in the current patch? I wonder if the following would work assuming that we want deferred triggers to run at outer transaction end? Ah, this seems to work. I'll implement it and I'll let you know how it goes. Ugh... There's one further wrinkle I hadn't thought about, imagine the following: begin; -- here the transaction does something that makes deferred trigger -- entries begin; set constraints all immediate; -- we now run through doing the deferred trigger items rollback; -- we need to unmark that the deferred items from the -- outer transaction have been run. However, in general, -- it might not be all entries nor all entries that are marked -- as done. I'm not sure how expensive it is to check if a given subxact has committed, but maybe instead of just done/not done, we need to say something like what xid marked the trigger and instead of if (!(event-dte_event (TRIGGER_DEFERRED_DONE | TRIGGER_DEFERRED_CANCELED))) inside deferredTriggerInvokeEvents we do something like: if (!((event-dte_event (TRIGGER_DEFERRED_DONE | TRIGGER_DEFERRED_CANCELED)) /*something to check that the marking xact is either myself or a committed subxact*/)) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Converting postgresql.conf parameters to kilobytes
Hi, I was toying around with idea of converting all the memory related parameters in postgresql.conf to kilobytes for simplicity and uniformity. Attached is a proof of concept patch that converts shared_buffers to kilobytes using assign_hook. It compiled all-right but I experienced a strange behavior. At the time of initdb, it demanded 69MB of shared memory. I had to bump up SHMMAX from 32MB to 128MB to get initdb through. Certainly I did something wrong somewhere but I don't know what and where. This is linux 2.6.4. The postgresql.conf is properly altered and shows 8000(Though the description around is no longer in sync.) I also don't know where to put the assign_hook. I have put in guc.c for the time being. Only other int hook I found was assign_max_stack_depth which is in postgres.c Any comments/pointers? Regards, Shridhar *** src/backend/utils/misc/guc.c.orig Sun May 30 17:14:08 2004 --- src/backend/utils/misc/guc.c Sun May 30 18:02:10 2004 *** *** 106,111 --- 106,112 static bool assign_stage_log_stats(bool newval, bool doit, GucSource source); static bool assign_log_stats(bool newval, bool doit, GucSource source); + static bool assign_shared_buffers(int newval, bool doit, GucSource source); /* * Debugging options *** *** 967,973 NULL }, NBuffers, ! 1000, 16, INT_MAX, NULL, NULL }, { --- 968,974 NULL }, NBuffers, ! 1000, 16, INT_MAX, assign_shared_buffers, NULL }, { *** *** 5130,5133 --- 5131,5147 } + static bool assign_shared_buffers(int newval, bool doit, GucSource source) + { + + if(doit) + { + if(BLCKSZ 0) + NBuffers = (newval*1024)/BLCKSZ; + else + return(false); + } + + return(true); + } #include guc-file.c *** src/bin/initdb/initdb.c.orig Sun May 30 17:26:01 2004 --- src/bin/initdb/initdb.c Sun May 30 17:26:51 2004 *** *** 886,892 snprintf(repltok, sizeof(repltok), max_connections = %d, n_connections); conflines = replace_token(conflines, #max_connections = 100, repltok); ! snprintf(repltok, sizeof(repltok), shared_buffers = %d, n_buffers); conflines = replace_token(conflines, #shared_buffers = 1000, repltok); snprintf(repltok, sizeof(repltok), lc_messages = '%s', lc_messages); --- 886,892 snprintf(repltok, sizeof(repltok), max_connections = %d, n_connections); conflines = replace_token(conflines, #max_connections = 100, repltok); ! snprintf(repltok, sizeof(repltok), shared_buffers = %d, (n_buffers*BLCKSZ)/1024); conflines = replace_token(conflines, #shared_buffers = 1000, repltok); snprintf(repltok, sizeof(repltok), lc_messages = '%s', lc_messages); ---(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] CVS tip compiler error with --enable-thread-safety
Hi, Platform Slackware linux 9.1/ Linux 2.6.4 I did a make distclean and ./configure --enable-thread-safety. The build aborted with following error messages make[4]: Leaving directory `/home/shridhar/postgresql/pgsql/src/port' gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DFRONTEND -I../../../src/interfaces/libpq -I../../../src/include -D_GNU_SOURCE -c -o initdb.o initdb.c rm -f exec.c ln -s ../../../src/port/exec.c . gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DFRONTEND -I../../../src/interfaces/libpq -I../../../src/include -D_GNU_SOURCE -c -o exec.o exec.c gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations initdb.o exec.o -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,-rpath,/usr/local/pgsql/lib -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl -lm -lpgport -o initdb ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_getspecific' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_once' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_key_create' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_setspecific' collect2: ld returned 1 exit status make[3]: *** [initdb] Error 1 make[3]: Leaving directory `/home/shridhar/postgresql/pgsql/src/bin/initdb' The relevant configure messages read - checking whether pthreads work without any flags... no checking whether pthreads work with -Kthread... no checking whether pthreads work with -kthread... no checking for the pthreads library -llthread... no checking whether pthreads work with -pthread... yes - Here is relevant portion of src/Makefile.global - PTHREAD_CFLAGS = -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS PTHREAD_LIBS= LIBS = -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl -lm - It worked after I manually added -lpthread to LIBS and did a make clean;make Regards Shridhar ---(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] CVS tip compiler error with --enable-thread-safety
Shridhar Daithankar wrote: Hi, Platform Slackware linux 9.1/ Linux 2.6.4 I did a make distclean and ./configure --enable-thread-safety. The build aborted with following error messages make[4]: Leaving directory `/home/shridhar/postgresql/pgsql/src/port' gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DFRONTEND -I../../../src/interfaces/libpq -I../../../src/include -D_GNU_SOURCE -c -o initdb.o initdb.c rm -f exec.c ln -s ../../../src/port/exec.c . gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DFRONTEND -I../../../src/interfaces/libpq -I../../../src/include -D_GNU_SOURCE -c -o exec.o exec.c gcc -O2 -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations initdb.o exec.o -L../../../src/interfaces/libpq -lpq -L../../../src/port -Wl,-rpath,/usr/local/pgsql/lib -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl -lm -lpgport -o initdb ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_getspecific' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_once' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_key_create' ../../../src/interfaces/libpq/libpq.so: undefined reference to `pthread_setspecific' collect2: ld returned 1 exit status make[3]: *** [initdb] Error 1 make[3]: Leaving directory `/home/shridhar/postgresql/pgsql/src/bin/initdb' The relevant configure messages read - checking whether pthreads work without any flags... no checking whether pthreads work with -Kthread... no checking whether pthreads work with -kthread... no checking for the pthreads library -llthread... no checking whether pthreads work with -pthread... yes - Here is relevant portion of src/Makefile.global - PTHREAD_CFLAGS= -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS PTHREAD_LIBS = LIBS = -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl -lm - It worked after I manually added -lpthread to LIBS and did a make clean;make OK, I have applied the following patch which should fix it. Turns out I wasn't using the thread libs as part of library creation. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ? pg_config_paths.h ? _xlk Index: Makefile === RCS file: /cvsroot/pgsql-server/src/port/Makefile,v retrieving revision 1.14 diff -c -c -r1.14 Makefile *** Makefile25 May 2004 01:00:30 - 1.14 --- Makefile30 May 2004 14:06:29 - *** *** 16,25 include $(top_builddir)/src/Makefile.global override CPPFLAGS := -I$(top_builddir)/src/port $(CPPFLAGS) - ifdef LIBOBJS all: libpgport.a - endif libpgport.a: $(LIBOBJS) $(AR) $(AROPT) $@ $^ --- 16,24 include $(top_builddir)/src/Makefile.global override CPPFLAGS := -I$(top_builddir)/src/port $(CPPFLAGS) + LIBS += $(PTHREAD_LIBS) all: libpgport.a libpgport.a: $(LIBOBJS) $(AR) $(AROPT) $@ $^ ---(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] v3 protocol string encoding
Oliver Jowett [EMAIL PROTECTED] writes: 1) What encoding is used for strings sent and received during the startup phase? The startup packet itself will not get any encoding conversion AFAIR, so one way to look at it is that the data therein must be in server encoding. In practice, there are no strings therein that really need conversion anyway. (If you use characters outside 7-bit-ASCII for user or database names, you're going to have much worse problems than just this one.) Any client_encoding received from the client is not going to be applied until after the authentication exchange is complete, so the rest of that is going to be in server encoding as well. The only part of this that seems like it might be an issue is a failure ERROR message would be in server encoding, but the client wouldn't have any good way to know what that is ... 2) At what point in the stream does a client_encoding change take effect -- immediately after the corresponding ParameterStatus message, or at some other point? ParameterStatus is sent when the change is made. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Converting postgresql.conf parameters to kilobytes
Shridhar Daithankar [EMAIL PROTECTED] writes: I was toying around with idea of converting all the memory related parameters in postgresql.conf to kilobytes for simplicity and uniformity. Why is that a good idea? 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] Converting postgresql.conf parameters to kilobytes
Hi, I was toying around with idea of converting all the memory related parameters in postgresql.conf to kilobytes for simplicity and uniformity. Attached is a proof of concept patch that converts shared_buffers to kilobytes using assign_hook. It compiled all-right but I experienced a strange behavior. At the time of initdb, it demanded 69MB of shared memory. I had to bump up SHMMAX from 32MB to 128MB to get initdb through. Certainly I did something wrong somewhere but I don't know what and where. This is linux 2.6.4. The postgresql.conf is properly altered and shows 8000(Though the description around is no longer in sync.) I also don't know where to put the assign_hook. I have put in guc.c for the time being. Only other int hook I found was assign_max_stack_depth which is in postgres.c Any comments/pointers? Regards, Shridhar Changing the format of numbers is tricky. I wouldn't change it, I'd allow for a suffix, i.e. 1024K or 1M Additionally, the configuration patch I submited and I think Bruce has already or will merge, allows for a configuration function. A specific keyword can be handled by a handler function. This is how I got include to work, but I would modify *all* the number handlers in GUC to accept the suffix rather than explicitly change anything that currently works. It is more consistent, more readble i.e. 128 vs 128M. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Idea about better configuration options for sort memory
On 12 Feb 2004, Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Hmmm ... maybe query_work_mem and maintenance_work_mem, or something similar? I'll go with these unless someone has another proposal ... dml_sort_mem and ddl_sort_mem ? I like those. Are they an accurte representation of what's going on? If so, I'd go with these, as they are more easily recognizable by folks who've worked with dbs for a while. On the other hand, they're probably less recognizable to the newbies. ---(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] Delaying the planning of unnamed statements until Bind
On Sat, 22 May 2004, Greg Stark wrote: Tom Lane [EMAIL PROTECTED] writes: I think it's wishful thinking to assume that picking an indexscan is the right thing when we don't know any better. If we don't know any better then any solution is going to be wishful thinking. It's kind of like a bridge game. If you don't know where the card is but you need it somewhere in order to win, then you have to assume it's there and hope for the best. If the index is wrong then the query was going to be slow either way. If the index was right and you chose not to use it you're risking making it slow unnecessarily and potentially when it was absoluetely required to be fast. Could we try to plan for both cases? How about calculating the cutoff point where the seqscan becomes faster than indexscan, creating a plan with both paths, and picking which path to take at execute time? More generally, keep *all* paths that make sense with *some* combination of parameter values, and determine rules on which path to use with which parameter values. For example, if the query plan looks currently like this: template1=# prepare b (int) AS SELECT * FROM foo WHERE bar $1 ORDER BY bar*10; PREPARE template1=# explain EXECUTE b (2); QUERY PLAN - Sort (cost=19.71..20.28 rows=230 width=4) Sort Key: (bar * 10) - Index Scan using fooo on foo (cost=0.00..10.69 rows=230 width=4) Index Cond: (bar $1) (4 rows) It would become something like this: template1=# prepare b (int) AS SELECT * FROM foo WHERE bar $1 ORDER BY bar*10; PREPARE template1=# explain EXECUTE b (2); QUERY PLAN - Sort (cost=19.71..20.28 rows=230 width=4) Sort Key: (bar * 10) if $1 400 then - Index Scan using fooo on foo (cost=0.00..10.69 rows=230 width=4) Index Cond: (bar $1) else - Seq Scan on foo (cost=0.00..14.17 rows=629 width=4) Filter: (bar 100) This means that execute stage would look at $1, and choose the seq scan if it's 400, otherwise use the seq scan. I haven't looked at the planner code, I don't know how hard it would be to implement, but I think it's something to consider. Until we figure how to do the above, I think the plan-on-execute mode would be very handy. However, it should not be on by default, IMHO. I'm worried about plan stability if we enable it by default. It could lead to nasty, hard to reproduce performance problems. Think about this scenario: A long-running server application prepares the query SELECT * FROM foo WHERE timestamp $1. 99% of the transactions that use the prepared query are online transactions that need to be very quick. They use parameter values very close to now(), and should do an indexscan. The rest are reports, running the same query with a parameter value of now() - 1 month. The reports should optimally use seqscan, but the slowness of indexscan is acceptable too. The application goes down every night for maintenance purposes, and is restarted in the morning. If the first transaction in the morning happens to be a report, all the following online transactions will use a seqscan, and become veeery slow. The operator gets angry phone calls, and reboots the system. Everything starts to work ok. Also keep in mind that at least some application servers have a client-side prepared statement cache, so that even if the application used a non-prepared statement for the reports, the middleware prepares it anyway. - Heikki ---(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] implemented missing bitSetBit() and bitGetBit()
Peter Eisentraut wrote: Neil Conway wrote: David Helgason [EMAIL PROTECTED] writes: I needed these, so I went and implemented them myself. I didn't see any followup to this: do we want to include this in the main tree, contrib/, or not at all? getbit sounds a lot like what substring() does. So perhaps setbit could actually be handled by replace()? That would be a more general solution (since it would handle more than one bit at a time). Added to TODO: * Allow substring/replace() to get/set bit values -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] yet another contrib module
Oleg can You take a look at my diffs for tsearch2 (win32 build) ? Since the changes are trivial, can You make those changes and commit together with schema support? Regards ! - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Oleg Bartunov [EMAIL PROTECTED] Cc: Pgsql Hackers [EMAIL PROTECTED] Sent: Friday, May 28, 2004 9:23 PM Subject: Re: [HACKERS] yet another contrib module Sounds fine. -- - Oleg Bartunov wrote: Hello, June 1 is pretty close now, so I'm asking about yet another contrib module, pg_trgm which is rather mature and quite useful. Is't worth to put it into 7.5 contrib ? trgm - Trigram matching for PostgreSQL -- The pg_trgm contrib module provides functions and index classes for determining the similarity of text based on trigram matching. Also, we plan to submit schema support to contrib/tsearch2 this weekend. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Transaction aborts on syntax error.
Bruce Momjian [EMAIL PROTECTED] writes: Imagine this: BEGIN WORK; LOCK oldtab; CREATE_X TABLE newtab AS SELECT * FROM oldtab; DELETE oldtab; COMMIT In this case, you would want the database to abort on a syntax error, right? Certainly not if I was typing this from the command line. Imagine the frustration if the typo was in DELETE oldtab and the create statement took hours. I would want the application to receive the error in a clean API that provides an option to automatically initiate a rollback whenever the client receives an error. In an application I would expect the database layer to provide a clean API to catch the error. Preferably one making it hard to avoid aborting the transaction and rolling back except intentionally. The best interface in most languages is to throw an exception. In any case it's up to the application to decide how to handle the error. Tom's explanation of the implementation issues makes perfect sense. Though I do wonder whether it would be possible to detect certain degenerate cases of queries that haven't caused any database changes at all before they errored out. This wouldn't help if you do a delete that causes an error after deleting a few thousand records, but it would catch the low hanging fruits of syntax errors. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposed Query Planner TODO items
Mark, It's run #60 and the links are towards the bottom of the page under the Run log data heading. The results from the power test is power_query.result and thuput_qs1.result, etc. for each stream in the throughput test. I'm confused. Were you able to get the original-form query #19 to complete, or not? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] CVS tip problems
CVS tip built on Debian unstable, i386, Linux 2.6.5 SMP. gcc 3.3.3 ./configure --with-openssl --with-pam --with-krb5 --with-gnu-ld --with-python --with-perl --with-tcl --with-pgport=5342 --enable-thread-safety --enable-nls --enable-integer-datetimes --enable-debug --enable-cassert --enable-depend 1. There are regression failures on timestamptz and horology which seem to have come about either on input or output of timestamps with fractional seconds. I tried various inputs and found that certain timestamps with fractional seconds had one second added to the time. This appears to be confined to the period from midnight at the start of Dec 14 1901 GMT to midnight at the start of Jan 01 2000 GMT junk=# select cast('Dec 13 15:59:59.50 1901 PST' as timestamptz); timestamptz 1901-12-13 23:59:59.50 (1 row) junk=# select cast('Dec 13 16:00:59.50 1901 PST' as timestamptz); timestamptz --- 1901-12-14 00:01:00.50+00 (1 row) junk=# select cast('Dec 13 23:59:59.50 1901 GMT' as timestamptz); timestamptz 1901-12-13 23:59:59.50 (1 row) junk=# select cast('Dec 14 00:00:00.50 1901 GMT' as timestamptz); timestamptz --- 1901-12-14 00:00:01.50+00 (1 row) I tried debugging this but got a segmentation fault and apparent stack corruption in gdb, with the reported break point not anywhere I had set one. I don't know what to do about that. 2. If the postmaster is not running, there is garbage in psql's error message: [EMAIL PROTECTED] export PGPORT=5342 [EMAIL PROTECTED] export PATH=/usr/local/pgsql/bin:$PATH [EMAIL PROTECTED] psql junk psql: could not connect to server: ,[EMAIL PROTECTED][EMAIL PROTECTED] Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5342? [EMAIL PROTECTED] psql -h localhost junk psql: could not connect to server: ,[EMAIL PROTECTED][EMAIL PROTECTED] Is the server running on host localhost and accepting TCP/IP connections on port 5342? 3. There is a compilation warning that a constant will not fit into a long in adt.c. There are two more files where INT64CONST() is required but not supplied. Patch attached. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA Do all things without murmurings and disputings; that ye may be blameless and harmless, the sons of God, without rebuke, in the midst of a crooked and perverse nation, among whom ye shine as lights in the world.Philippians 2:14,15 Index: src/backend/utils/adt/date.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/date.c,v retrieving revision 1.97 diff -c -r1.97 date.c *** src/backend/utils/adt/date.c 21 May 2004 05:08:01 - 1.97 --- src/backend/utils/adt/date.c 30 May 2004 21:11:56 - *** *** 1461,1467 } else if (result 0) { ! days = (-result + INT64CONST(864 - 1)) / INT64CONST(864); result += days * INT64CONST(864); } #else --- 1461,1467 } else if (result 0) { ! days = (-result + INT64CONST(864) - 1) / INT64CONST(864); result += days * INT64CONST(864); } #else Index: src/backend/utils/adt/datetime.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/datetime.c,v retrieving revision 1.128 diff -c -r1.128 datetime.c *** src/backend/utils/adt/datetime.c 21 May 2004 05:08:01 - 1.128 --- src/backend/utils/adt/datetime.c 30 May 2004 21:11:56 - *** *** 1209,1215 tmask |= DTK_TIME_M; #ifdef HAVE_INT64_TIMESTAMP ! dt2time((time * 864), tm-tm_hour, tm-tm_min, tm-tm_sec, fsec); #else dt2time((time * 86400), --- 1209,1215 tmask |= DTK_TIME_M; #ifdef HAVE_INT64_TIMESTAMP ! dt2time((time * INT64CONST(864)), tm-tm_hour, tm-tm_min, tm-tm_sec, fsec); #else dt2time((time * 86400), *** *** 1960,1966 tmask |= DTK_TIME_M; #ifdef HAVE_INT64_TIMESTAMP ! dt2time((time * 864), tm-tm_hour, tm-tm_min, tm-tm_sec, fsec); #else dt2time((time * 86400), --- 1960,1966 tmask |= DTK_TIME_M; #ifdef HAVE_INT64_TIMESTAMP ! dt2time((time * INT64CONST(864)), tm-tm_hour, tm-tm_min, tm-tm_sec, fsec); #else dt2time((time * 86400), Index: src/interfaces/ecpg/pgtypeslib/dt_common.c === RCS
Re: [HACKERS] v3 protocol string encoding
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: 1) What encoding is used for strings sent and received during the startup phase? The startup packet itself will not get any encoding conversion AFAIR, so one way to look at it is that the data therein must be in server encoding. In practice, there are no strings therein that really need conversion anyway. (If you use characters outside 7-bit-ASCII for user or database names, you're going to have much worse problems than just this one.) The encoding of user database names was my main concern. If they can only be 7-bit ASCII in practice, that's easy.. 2) At what point in the stream does a client_encoding change take effect -- immediately after the corresponding ParameterStatus message, or at some other point? ParameterStatus is sent when the change is made. Are the strings in the ParameterStatus encoded with the old or new client_encoding? I need to know the point in the stream to switch encodings. I suppose this is only an issue if there are pairs of encodings where client_encoding or the encoding names encode differently in the two encodings. Is it safe to assume that 7-bit ASCII is always encoded unchanged regardless of the encoding in use? -O ---(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] Delaying the planning of unnamed statements until Bind
Heikki Linnakangas wrote: I'm worried about plan stability if we enable it by default. It could lead to nasty, hard to reproduce performance problems. Think about this scenario: This is my main concern with the approach Tom suggested. Also keep in mind that at least some application servers have a client-side prepared statement cache, so that even if the application used a non-prepared statement for the reports, the middleware prepares it anyway. It's less of an issue if we only use this behaviour when binding the unnamed statement. The unnamed statement is quite special and is unlikely to be reused accidentally (for one thing, you can only have one unnamed statement..) The patch I posted to -patches earlier uses this approach. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Transaction aborts on syntax error.
Rod Taylor wrote: BEGIN WORK; LOCK oldtab; CREATE_X TABLE newtab AS SELECT * FROM oldtab; DELETE oldtab; COMMIT In this case, you would want the database to abort on a syntax error, right? Certainly not if I was typing this from the command line. Imagine the frustration if the typo was in DELETE oldtab and the create statement took hours. I suppose we could have a SET that psql could set when it was interactive and skip rollback on syntax errors, but that is pretty exotic. Also consider that other errors could abort a query aside from syntax errors, like deadlocks. Can this be done entirely on the client side? Have psql silently wrap every statement going out with a BEGIN and a COMMIT or ROLLBACK depending on whether there was an error or not? It depends on subtransactions but those are bound to appear eventually, and be infinitely more useful. Yep, we could do it in the client like we do for autocommit. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] yet another contrib module
On Sun, 30 May 2004, Darko Prenosil wrote: Oleg can You take a look at my diffs for tsearch2 (win32 build) ? Since the changes are trivial, can You make those changes and commit together with schema support? Where we can get the diff ? Regards ! - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Oleg Bartunov [EMAIL PROTECTED] Cc: Pgsql Hackers [EMAIL PROTECTED] Sent: Friday, May 28, 2004 9:23 PM Subject: Re: [HACKERS] yet another contrib module Sounds fine. -- - Oleg Bartunov wrote: Hello, June 1 is pretty close now, so I'm asking about yet another contrib module, pg_trgm which is rather mature and quite useful. Is't worth to put it into 7.5 contrib ? trgm - Trigram matching for PostgreSQL -- The pg_trgm contrib module provides functions and index classes for determining the similarity of text based on trigram matching. Also, we plan to submit schema support to contrib/tsearch2 this weekend. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] v3 protocol string encoding
Oliver Jowett [EMAIL PROTECTED] writes: The encoding of user database names was my main concern. If they can only be 7-bit ASCII in practice, that's easy.. Well, you can *try* using other encodings, but there are enough known problems that I don't think it will work pleasantly unless client and server encodings are the same all the time. 2) At what point in the stream does a client_encoding change take effect -- immediately after the corresponding ParameterStatus message, or at some other point? ParameterStatus is sent when the change is made. Are the strings in the ParameterStatus encoded with the old or new client_encoding? Okay, make that sent just after the change is made. So it looks like you should receive a string in the new encoding. I can't offhand think of a way to test this though --- are any of the reported settings interesting from an encoding standpoint? Is it safe to assume that 7-bit ASCII is always encoded unchanged regardless of the encoding in use? Hm. This is true for all the backend-safe encodings but I believe not for all the supported client encodings. Tatsuo might have more of a clue than me about likely failure cases. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] v3 protocol string encoding
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: 2) At what point in the stream does a client_encoding change take effect -- immediately after the corresponding ParameterStatus message, or at some other point? ParameterStatus is sent when the change is made. Are the strings in the ParameterStatus encoded with the old or new client_encoding? Okay, make that sent just after the change is made. So it looks like you should receive a string in the new encoding. I can't offhand think of a way to test this though --- are any of the reported settings interesting from an encoding standpoint? This timing makes it harder for a client to recognize a change in client_encoding -- how is it supposed to know to change encoding before interpreting the ParameterStatus message? I'd like to add some robustness to the JDBC driver such that if the user changes client_encoding, the driver throws an error rather than garbling data (it is expecting client_encoding = 'UNICODE'). If the user can set client_encoding such that the driver won't recognize the ParameterStatus message (i.e. the string client_encoding does not encode as it would in UNICODE), it's not so useful. I don't know if there is such an encoding, however. Is it safe to assume that 7-bit ASCII is always encoded unchanged regardless of the encoding in use? Hm. This is true for all the backend-safe encodings but I believe not for all the supported client encodings. Tatsuo might have more of a clue than me about likely failure cases. By backend-safe do you mean can be used as a database encoding? If so, it solves my problem, which is handling the switchover from default client_encoding (== database encoding) to UNICODE in the JDBC driver's connection setup code. I can initially use 7-bit ASCII regardless of the actual database encoding, and switch to UNICODE when possible (this is what the current driver does in most cases, I'm just verifying that the assumptions it makes are correct). -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])