[HACKERS] Updatable views
Hi, Is anybody working on Bernd's updatable views patch? Right now I'm having a look at it -- mainly fixing some cosmetic issues right now, intending to eventually do an actual code review. I'll post an updated patch at some point -- if someone wants to have a look or do some intensive review, just ask and I'll send whatever I have. -- Alvaro Herrerahttp://www.PlanetPostgreSQL.org "Digital and video cameras have this adjustment and film cameras don't for the same reason dogs and cats lick themselves: because they can." (Ken Rockwell) ---(end of broadcast)--- TIP 1: 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] segfault on rollback
On Sat, 12 Aug 2006, Tom Lane wrote: "Sergey E. Koposov" <[EMAIL PROTECTED]> writes: I recently have seen the segfault with Postgres 8.1.4. I'm betting that portal->sourceText has already been deallocated when exec_execute_message tries to print the log message. Getting an actual segfault from that would very probably be hard to reproduce, but if you build with --enable-cassert it should not be too hard to reproduce corruption of the log message, ie, display of garbage instead of "ROLLBACK" as the command text. Please try that and see if you can generate a self-contained test case. I succeeded to get the self contained case, but in java (it is just 15 lines, but it crashes the backend). (probably I should forward the mail pgsql-jdbc...) import java.sql.*; public class xx { public static void main(String args[]) throws Exception { Class.forName("org.postgresql.Driver"); Connection dbcon = DriverManager.getConnection("jdbc:postgresql://localhost:5432/template1","postgres",""); dbcon.setAutoCommit(false); Statement stmt = dbcon.createStatement(); stmt.execute("create table xx(a int, b double precision)"); dbcon.rollback(); } } I'm not sure that it is possible to reproduce without java, since JDBC do its own query preparing and a lot of other stuff internally which should be non trivial to reproduce without jdbc... Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
The long-lost pg_upgrade (was:Re: [HACKERS] 8.2 features status)
On Friday 04 August 2006 02:20, Josh Berkus wrote: > Aren't I, the marketing geek, supposed to be the one whining about > this? [snip] > > * In-place upgrades (pg_upgrade) > BTW, I may get Sun to contribute an engineer for this; will get you posted. Long time no post. This statement really caught my attention; bravo if true upgrading can happen, and someone can be put on it and do it right. As Tom said, a little farther down the thread, we have talked over this many times. I specifically remember, oh, about a dozen times I personally have 'gadflied' this issue. As one who now has a, let's see: [EMAIL PROTECTED] ~]# du /var/lib/pgsql/data -s 16668528/var/lib/pgsql/data [EMAIL PROTECTED] ~]# Yes, a 16GB inventory database, with in-database large object images. Anyway, as one who does not look forward to migrating this the old-fashioned way (I can just imagine how fas^H^H^Hslow a restore of all those large objects is going to be; backup is slow enough (about 50 minutes on this Xeon 2.4GHz box)), in place upgrade would cut this considerably; the database is not a complex one, just a largish one. It's, let's see, only holding a little less than 5,000 items with associated lo images (due to many factors, this is handled through ODBC from Microsoft Access; it is a kludge, and a big one, but it works very smoothly from the users' points of view, where item images are literally 'dragged and dropped' from the digital camera straight to the database). So, anyway, looking forward to seeing some progress in this department... :-) -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu ---(end of broadcast)--- TIP 1: 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] [PATCHES] Adding fulldisjunctions to the contrib
On 8/12/06, Tom Lane <[EMAIL PROTECTED]> wrote: More seriously: the current state of affairs is that the full-disjunction code exists as a pgfoundry project. If it's indeed the second greatest thing since sliced bread, then I think we could assume that people will find it and use it from pgfoundry. That goes back to assuming people not only know about pgfoundry, but are similarly willing to search it. The question that's on the table is whether it needs to be in contrib right now. I have not seen either a technical argument or popularity argument why it ought to move into contrib. In addition to knowing that Tzahi has put a *very* significant amount of work into his research as well as this code over the past few months, I have to agree with several items stated by "Agent M". This is the *first* implementation of this concept in any database system, so there's not going to be anyone jumping up and down singing it's praises just yet. However, when people do get a chance to play with it, I believe we'll have a number of them saying how useful it is. There are several contrib modules still included in the system that aren't that heavily used... I don't see the harm in including this one for at least this release. If no one uses it, take it out for 8.3. IMHO, this is just a really cool piece of technology that provides functionality which can't be done any other way; why not give it a chance? -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] segfault on rollback
"Sergey E. Koposov" <[EMAIL PROTECTED]> writes: > I recently have seen the segfault with Postgres 8.1.4. I'm betting that portal->sourceText has already been deallocated when exec_execute_message tries to print the log message. Getting an actual segfault from that would very probably be hard to reproduce, but if you build with --enable-cassert it should not be too hard to reproduce corruption of the log message, ie, display of garbage instead of "ROLLBACK" as the command text. Please try that and see if you can generate a self-contained test case. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Adding fulldisjunctions to the contrib
AgentM <[EMAIL PROTECTED]> writes: > You won't find anyone to vouch for it because this is the first > implementation of full disjunctions in any database. That doesn't > mean it isn't useful- it means no one is using it because it hasn't > existed until now. > This is the point where one needs to decide whether PostgreSQL is a > copier of features from other databases or whether it can lead with a > few unique features of its own. Somewhere along here we need to remember that "most new ideas are bad". More seriously: the current state of affairs is that the full-disjunction code exists as a pgfoundry project. If it's indeed the second greatest thing since sliced bread, then I think we could assume that people will find it and use it from pgfoundry. The question that's on the table is whether it needs to be in contrib right now. I have not seen either a technical argument or popularity argument why it ought to move into contrib. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] list archives not being updated?
Fixed ... the 'auto run' was commented out when I was rebuilding it all for the pre-July / post-July changes (old vs new) and failed to uncomment the cron job after ... should be updated within the next hour or so ... On Sat, 12 Aug 2006, Andrew Dunstan wrote: It has just been pointed out to me that the list archives seem to have stopped being updated last Wednesday. Any idea why? cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Adding fulldisjunctions to the contrib
AgentM wrote: > > On Aug 12, 2006, at 6:01 , Tzahi Fadida wrote: > > > On Saturday 12 August 2006 07:22, Bruce Momjian wrote: > >> I am still waiting for someone to tell us that they would use this > >> capability for a real-world problem. > > Notice that if you google "full disjunction" that the first link is > this project. > > You won't find anyone to vouch for it because this is the first > implementation of full disjunctions in any database. That doesn't > mean it isn't useful- it means no one is using it because it hasn't > existed until now. > > This is the point where one needs to decide whether PostgreSQL is a > copier of features from other databases or whether it can lead with a > few unique features of its own. OK, that is helpful. Now, does any current user think they will use full disjunctions? Is that a fair question? The point is not whether it should work with PostgreSQL, but whether we ship it in /contrib, or it is on pgfoundry. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SIg11 on suse linux
Francisco Figueiredo Jr. wrote: Hi all, A friend is getting sig11 on suse linux while trying to establish any connection to postgresql. Have you seen anything like that? He is using Postgresql 8.1.4. He compiled Postgresql with a common ./configure --with--ssl - --prefix=/usr/local After compiling and running initdb as soon as he tries to run createdb, he get sig11 on server. I tried the same here and I could get it working ok. The difference is that I'm using Gentoo. Do you know if there is some detail about compiling Postgresql on suse? Have him get a stack trace from the core file that should have been produced. Also, I see opensuse has postgresql 8.1.4 packages available at http://download.opensuse.org/distribution/SL-OSS-factory/inst-source/suse/i586/ cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Adding fulldisjunctions to the contrib
On Aug 12, 2006, at 6:01 , Tzahi Fadida wrote: On Saturday 12 August 2006 07:22, Bruce Momjian wrote: I am still waiting for someone to tell us that they would use this capability for a real-world problem. Notice that if you google "full disjunction" that the first link is this project. You won't find anyone to vouch for it because this is the first implementation of full disjunctions in any database. That doesn't mean it isn't useful- it means no one is using it because it hasn't existed until now. This is the point where one needs to decide whether PostgreSQL is a copier of features from other databases or whether it can lead with a few unique features of its own. ---(end of broadcast)--- TIP 1: 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] Buildfarm owners: check if your HEAD build is stuck
Tom Lane wrote: A number of the buildfarm machines have been failing HEAD builds at the "make check" stage since last night, with complaints like this one from emu: == pgsql.21911/src/test/regress/log/postmaster.log === FATAL: lock file "/tmp/.s.PGSQL.55678.lock" already exists HINT: Is another postmaster (PID 23692) using socket file "/tmp/.s.PGSQL.55678"? What's happened is that that GUC patch that was in the tree for a few hours broke postmaster startup on some machines (for as-yet-unidentified reasons). The postmaster does actually start and establish its lockfiles, but it never gets to the stage of being able to accept connections. After the buildfarm script rm -rf's the build tree, the postmaster process is still there but "disembodied" (its executable file is probably gone, for example, or at least in the state of zero remaining directory links). But it's still got that socket file and lockfile in /tmp, and this prevents another postmaster from starting with the same port number. If you've got this situation, you'll need to do a manual "kill" on the PID mentioned in the lock file before things will start working again. (pg_ctl won't work because it looks for the data directory postmaster.pid file, which is long gone.) More generally you might want to look through a ps listing for unexpected postgres-owned processes. I'm not sure whether there's anything much we can do to prevent such problems in future. Maybe it'd be reasonable for pg_regress to do a kill -9 on its postmaster child process if it gives up waiting for the postmaster to accept connections. That's amazingly ugly, and well diagnosed. BTW, buildfarm processes would typically not be postgres owned, at least not on my machines. I run either as myself or as a special buildfarm user. I'm trying to think how we could harden the buildfarm script to avoid such situations, although I am so far without any great revelations. The idea of getting pg_regress to send a signal isn't bad - what if the PID gets reused, since we know not all systems allocate PIDs in a cyclical fashion? Also, I see the pg-regress code has this comment: /* * Fail immediately if postmaster has exited * * XXX is there a way to do this on Windows? */ As I understand it, the way to do it is to call OpenProcess() - if that succeeds then it is still there. I guess if needed we could even do that in src/port/kill.c so that kill(pid,0) would work. But I would want confirmation from the Windows gurus. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] segfault on rollback
Hello -hackers, I recently have seen the segfault with Postgres 8.1.4. The situation in which the segfault occur is quite complicated (a lot of commands in one transaction from JDBC), but Here I show the gdb core dump of it. From looking in my application, it seems that the core dump occurs when the application issue the ROLLBACK command in the end of large transaction. (when I do Connection.rollback() from JDBC) Also I must notice that the segfault only occur if log_min_duration_statement is set to 0 if it is set to -1, the segfault do not occur. Also I must say that I specially removed all the C functions calls from the transaction, to be sure that I'm not corrupting the memory myself. Program received signal SIGSEGV, Segmentation fault. 0xb7dbd6db in strlen () from /lib/tls/libc.so.6 (gdb) bt #0 0xb7dbd6db in strlen () from /lib/tls/libc.so.6 #1 0xb7d91ef1 in vfprintf () from /lib/tls/libc.so.6 #2 0xb7db0700 in vsnprintf () from /lib/tls/libc.so.6 #3 0x08179c47 in appendStringInfoVA (str=0xbfd639f0, fmt=0x83d43c0 "duration: %ld.%03ld ms statement: %sEXECUTE %s [PREPARE: %s]", args=0xbfd63a24 "Y") at stringinfo.c:125 #4 0x08290471 in errmsg ( fmt=0x834b0c0 "duration: %ld.%03ld ms statement: %sEXECUTE %s [PREPARE: %s]") at elog.c:647 #5 0x08202bd2 in exec_execute_message (portal_name=0x8421a6c "", max_rows=1) at postgres.c:1825 #6 0x0820498a in PostgresMain (argc=4, argv=0x83d2754, username=0x83d2658 "cas_user_tmp") at postgres.c:3268 #7 0x081d3528 in BackendRun (port=0x83eaed0) at postmaster.c:2856 #8 0x081d2b3f in BackendStartup (port=0x83eaed0) at postmaster.c:2500 #9 0x081d0bc9 in ServerLoop () at postmaster.c:1230 #10 0x081d0474 in PostmasterMain (argc=1, argv=0x83bb000) at postmaster.c:941 #11 0x081821d8 in main (argc=1, argv=0x83bb000) at main.c:265 (gdb) bt full #0 0xb7dbd6db in strlen () from /lib/tls/libc.so.6 No symbol table info available. #1 0xb7d91ef1 in vfprintf () from /lib/tls/libc.so.6 No symbol table info available. #2 0xb7db0700 in vsnprintf () from /lib/tls/libc.so.6 No symbol table info available. #3 0x08179c47 in appendStringInfoVA (str=0xbfd639f0, fmt=0x83d43c0 "duration: %ld.%03ld ms statement: %sEXECUTE %s [PREPARE: %s]", args=0xbfd63a24 "Y") at stringinfo.c:125 avail = 255 nprinted = 256 #4 0x08290471 in errmsg ( fmt=0x834b0c0 "duration: %ld.%03ld ms statement: %sEXECUTE %s [PREPARE: %s]") at elog.c:647 args = 0xbfd63a24 "Y" success = -65 '©' fmtbuf = 0x83d43c0 "duration: %ld.%03ld ms statement: %sEXECUTE %s [PREPARE: %s]" buf = { data = 0x83d42b4 "duration: 89.073 ms statement: EXECUTE [PREPARE: ", '\177' ..., len = 0, maxlen = 256, cursor = 0} edata = (ErrorData *) 0x83ab3a0 oldcontext = 0x83d1f00 ---Type to continue, or q to quit--- #5 0x08202bd2 in exec_execute_message (portal_name=0x8421a6c "", max_rows=1) at postgres.c:1825 usecs = 89073 dest = DestRemoteExecute receiver = (DestReceiver *) 0x8421b78 portal = 0x842c114 completed = 1 '\001' completionTag = "ROLLBACK\000:ж©Ф\033\030\bэ:ж©m\032B\b\004\000\000\000%\n \b╓\201?\b\000\001\000\000Х:ж©Ё\031\030\b\000\000\000\001э:ж©\004\000\000\000\224\035\030\b" start_t = {tv_sec = 1155432847, tv_usec = 699143} stop_t = {tv_sec = 1155432847, tv_usec = 788216} save_log_duration = 0 '\0' save_log_min_duration_statement = 0 save_log_statement_stats = 0 '\0' execute_is_fetch = 0 '\0' #6 0x0820498a in PostgresMain (argc=4, argv=0x83d2754, username=0x83d2658 "cas_user_tmp") at postgres.c:3268 portal_name = 0x8421a6c "" max_rows = 1 flag = -1 dbname = 0x83dd198 "cas" userDoption = 0x0 ---Type to continue, or q to quit--- secure = 0 '\0' errs = 0 debug_flag = -1 guc_names = (List *) 0x0 guc_values = (List *) 0x0 ctx = PGC_USERSET gucsource = PGC_S_CLIENT am_superuser = 0 '\0' tmp = 0x83d232c "" firstchar = 69 stack_base = 0 '\0' input_message = {data = 0x8421a6c "", len = 5, maxlen = 256, cursor = 5} local_sigjmp_buf = {{__jmpbuf = {138325712, 137883360, 1, -107647, -1076479248, 136332736}, __mask_was_saved = 1, __saved_mask = {__val = { 1073347075, 4294967294, 0, 0, 0, 0, 0, 7004, 0, 8, 0, 0, 138225460, 138224272, 138223360, 3218488264, 4294967295, 138223360, 3085359680, 3084618868, 138225440, 138224272, 3084378772, 138326168, 3085312352, 200, 138249720, 138241528, 3085367328, 137883360, 1, 3218488344 send_rfq = 0 '\0' #7 0x081d3528 in BackendRun (port=0x83eaed0) at postmaster.c:2856 status = 0 remote_host = "127.0.0.1\000ж╥\000ЮФ╥\004\aГ╥\004\aГ╥xAж©bЦщ╥\004\aГ╥\00---Type to continue, or q to quit--- 0\000\000\000hAж©╠\201*\b<\032Г╥\000\000\000\000\004\aГ╥\002\024\000\000
[HACKERS] list archives not being updated?
It has just been pointed out to me that the list archives seem to have stopped being updated last Wednesday. Any idea why? cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Forcing current WAL file to be archived
Ühel kenal päeval, L, 2006-08-12 kell 10:59, kirjutas Tom Lane: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Ühel kenal päeval, K, 2006-08-09 kell 10:57, kirjutas Tom Lane: > >> Insert points to the next byte to be written within the internal WAL > >> buffers. The byte(s) preceding it haven't necessarily gotten out of > >> those buffers yet. Write points to the end of what we've actually > >> written to the kernel, > > > I assume that it also points to the byte after what is written to > > kernel, or is it tha last byte written ? > > Right, it's really first-unwritten-byte for all three pointers. > The two newly added functions to convert WAL locations to filenames > use XLByteToPrevSeg(), so they should do the right thing here > (see comments in src/include/access/xlog_internal.h). How do they behave exactly at the file boundary ? That is will it point 1 byte past end of old file, or byte 0 of the new one ? > regards, tom lane -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Buildfarm owners: check if your HEAD build is stuck
On Sat, Aug 12, 2006 at 11:29:46AM -0400, Tom Lane wrote: > What's happened is that that GUC patch that was in the tree for a few > hours broke postmaster startup on some machines (for as-yet-unidentified > reasons). The postmaster does actually start and establish its > lockfiles, but it never gets to the stage of being able to accept > connections. I don't know if it's related, but coverity just started picking up a use-after-free in parse_value() in guc.c. At the end of the switch (case PGC_STRING) there's a free(newval) followed by an assignment of newval to retval->stringval a few lines further down. They mark it as line 3956 of revision 1.335. It may not be possible though, coverity is not omnicient. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] extension for sql update
On Fri, Aug 11, 2006 at 05:11:03PM -0500, Jim C. Nasby wrote: > On Fri, Aug 11, 2006 at 10:59:45AM -0400, Bruce Momjian wrote: > > Peter Eisentraut wrote: > > > Bruce Momjian wrote: > > > > Are we sure we don't want the patch for a non-subquery version of SET > > > > ROW for 8.2? > > > > > > > > o Allow UPDATE tab SET ROW (col, ...) = (...) for updating > > > > multiple columns > > > > > > It seems to be moderately useful as a notational convenience for > > > now. > > > > > > Is it too hard to rip it back out once the full row support > > > arrives? That seems speculation at best anyway. > > > > That's what I was thinking. Glad someone else replied. ;-) > > If you're looking for votes, +1. I'll gladly take a subset of the > SQL standard UPDATE table SET (...) = (...) over having nothing. +1 here, too. :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: 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] psql and INSERT/UPDATE/DELETE RETURNING
On Fri, Aug 11, 2006 at 10:58:12PM -0400, Tom Lane wrote: > Currently, psql does not show the command completion tag if it gets a > PGRES_TUPLES_OK result. This means you won't see the tag for a command > with RETURNING, eg > > regression=# insert into int8_tbl values(1,2),(3,4); > INSERT 0 2 > regression=# insert into int8_tbl values(1,2),(3,4) returning *; > q1 | q2 > + > 1 | 2 > 3 | 4 > (2 rows) > > regression=# > > The tag is actually being sent by the backend, it's just not displayed. > > Do we like this behavior? The number-of-tuples part of the tag is > certainly redundant with the table display, but perhaps it's good to > have a reminder that the rows you are looking at were just INSERTed, > UPDATEd, or DELETEd. I haven't checked the code but I imagine it'd > not be very difficult to change the behavior if we wish. > > Comments? Having both would be good :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: 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] SIg11 on suse linux
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, A friend is getting sig11 on suse linux while trying to establish any connection to postgresql. Have you seen anything like that? He is using Postgresql 8.1.4. He compiled Postgresql with a common ./configure --with--ssl - --prefix=/usr/local After compiling and running initdb as soon as he tries to run createdb, he get sig11 on server. I tried the same here and I could get it working ok. The difference is that I'm using Gentoo. Do you know if there is some detail about compiling Postgresql on suse? Thanks in advance. - -- Regards, Francisco Figueiredo Jr. http://fxjr.blogspot.com Npgsql Lead Developer http://pgfoundry.org/projects/npgsql MonoBrasil Project Founder Member http://monobrasil.softwarelivre.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.4 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iQEVAwUBRN4zKP7iFmsNzeXfAQI2jgf+Ih+cFc+HuzmNskNPqqNWmRppeQI3sSLA /fLlhDjI3R2JjX0M+fp69Q5ZikWy+muCuV1d7iI8SoqgtwsuQZS4xiAZUXa5wbXS 78/bCjUVixG4n4dDXm/WbkYyg2U0o6bAAGz6LW4xi1kHgvnTCFwyYYSz9fzAshff Z6CtARHxColWvaZeCyOVHkrqFj8og1MzitqnW8KCR2tYnnlECTwZB6zdaE9rgobm 5IUmH3dlFZ0j0S4i0jAR+MSUMKfoZk8N4NZaT6dcHBtrHlGt0BiQT7bLX4Jhb48I cuHkwyWdy7gShIHjDNStMXq/E7IdVR6+Ptn+XQb1e48SDtIzu/2phg== =xIOz -END PGP SIGNATURE- ___ Novidade no Yahoo! Mail: receba alertas de novas mensagens no seu celular. Registre seu aparelho agora! http://br.mobile.yahoo.com/mailalertas/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Buildfarm owners: check if your HEAD build is stuck
A number of the buildfarm machines have been failing HEAD builds at the "make check" stage since last night, with complaints like this one from emu: == pgsql.21911/src/test/regress/log/postmaster.log === FATAL: lock file "/tmp/.s.PGSQL.55678.lock" already exists HINT: Is another postmaster (PID 23692) using socket file "/tmp/.s.PGSQL.55678"? What's happened is that that GUC patch that was in the tree for a few hours broke postmaster startup on some machines (for as-yet-unidentified reasons). The postmaster does actually start and establish its lockfiles, but it never gets to the stage of being able to accept connections. After the buildfarm script rm -rf's the build tree, the postmaster process is still there but "disembodied" (its executable file is probably gone, for example, or at least in the state of zero remaining directory links). But it's still got that socket file and lockfile in /tmp, and this prevents another postmaster from starting with the same port number. If you've got this situation, you'll need to do a manual "kill" on the PID mentioned in the lock file before things will start working again. (pg_ctl won't work because it looks for the data directory postmaster.pid file, which is long gone.) More generally you might want to look through a ps listing for unexpected postgres-owned processes. I'm not sure whether there's anything much we can do to prevent such problems in future. Maybe it'd be reasonable for pg_regress to do a kill -9 on its postmaster child process if it gives up waiting for the postmaster to accept connections. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Forcing current WAL file to be archived
Hannu Krosing <[EMAIL PROTECTED]> writes: > Ãhel kenal päeval, K, 2006-08-09 kell 10:57, kirjutas Tom Lane: >> Insert points to the next byte to be written within the internal WAL >> buffers. The byte(s) preceding it haven't necessarily gotten out of >> those buffers yet. Write points to the end of what we've actually >> written to the kernel, > I assume that it also points to the byte after what is written to > kernel, or is it tha last byte written ? Right, it's really first-unwritten-byte for all three pointers. The two newly added functions to convert WAL locations to filenames use XLByteToPrevSeg(), so they should do the right thing here (see comments in src/include/access/xlog_internal.h). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Forcing current WAL file to be archived
Ühel kenal päeval, K, 2006-08-09 kell 10:57, kirjutas Tom Lane: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Ühel kenal päeval, K, 2006-08-09 kell 12:56, kirjutas Simon Riggs: > >> Methinks it should be the Write pointer all of the time, since I can't > >> think of a valid reason for wanting to know where the Insert pointer is > >> *before* we've written to the xlog file. Having it be the Insert pointer > >> could lead to some errors. > > > What is the difference ? > > Insert points to the next byte to be written within the internal WAL > buffers. The byte(s) preceding it haven't necessarily gotten out of > those buffers yet. Write points to the end of what we've actually > written to the kernel, I assume that it also points to the byte after what is written to kernel, or is it tha last byte written ? > and there's also a Flush pointer that points > to the end of what we believe is down on disk. > > Simon's point is that if you're going to use pg_current_xlog_location() > to control partial shipping of xlog files, you probably want to know > about the Write location, because that indicates the limit of what > is visible to an external process. Yes, that is what I need > regards, tom lane -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] psql and INSERT/UPDATE/DELETE RETURNING
Currently, psql does not show the command completion tag if it gets a PGRES_TUPLES_OK result. This means you won't see the tag for a command with RETURNING, eg regression=# insert into int8_tbl values(1,2),(3,4); INSERT 0 2 regression=# insert into int8_tbl values(1,2),(3,4) returning *; q1 | q2 + 1 | 2 3 | 4 (2 rows) regression=# The tag is actually being sent by the backend, it's just not displayed. Do we like this behavior? The number-of-tuples part of the tag is certainly redundant with the table display, but perhaps it's good to have a reminder that the rows you are looking at were just INSERTed, UPDATEd, or DELETEd. I haven't checked the code but I imagine it'd not be very difficult to change the behavior if we wish. Comments? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [COMMITTERS] pgsql: Allow commenting of variables in postgresql.conf to restore them
[EMAIL PROTECTED] (Bruce Momjian) writes: > Log Message: > --- > Allow commenting of variables in postgresql.conf to restore them to > defaults. Please revert these patches, as they have broken the build completely (see buildfarm). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [COMMITTERS] pgsql: plperl: Allow conversion from perl to postgresql array in OUT
[EMAIL PROTECTED] (Bruce Momjian) writes: > pgsql/src/pl/plperl/sql: > plperl.sql (r1.7 -> r1.8) > > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/sql/plperl.sql.diff?r1=1.7&r2=1.8) Shouldn't there be an 'expected' update to go with the test-file update? The few buildfarm members that aren't dying because of the GUC patch are failing on this one. I'd commit a fixed expected file if I could get HEAD to work locally, but ... regards, tom lane ---(end of broadcast)--- TIP 1: 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] [PATCHES] Adding fulldisjunctions to the contrib
On Saturday 12 August 2006 07:22, Bruce Momjian wrote: > I am still waiting for someone to tell us that they would use this > capability for a real-world problem. I suggest looking into web applications. The example here http://www.technion.ac.il/~tzahi/soc.html shows a possible 3 separate web resources. I.e. heterogeneous sources. Naturally, since the sources did not know each other in advance, they did not form relations that would not end up cyclic in the scheme graph. XMLs are usually like these. Obviously you have to turn them into relations first of course. In addition, i have recently added a feature where you give alias to column names so if you have "country" column and a "state" column that really means country, you can do "country=public.relation_with_state.state,..." dictionary style. This is commonly needed in web applications. Here is another example (improvising :) ): site1: user_name,email,favorite_book_isbn site2: user_name,email,favorite_chat_room site3: user_name,credit_card So, let's say i wanted to advertise discounts using a certain credit card for certain books, i would do FD(site1,site2,site3). Natural join will give - so you get data on people who read some books and visit certain chat rooms and users credit cards. FD will give - some people did not buy books but have a credit card and a chat room so you want to advertise anyway. Some people did buy books and uses a certain credit cards but you don't know where they chat, however, you know you want to adv some best seller that most buy anyway. certain people did buy books and visit chat rooms but you can't offer a specific discount, so you will advertise all credit cards. ... However, caution. FD is a very,very expensive operation even with the new algorithms so it is best to do FD separately and put the results into a table and use that table. Unless of course, as common to web applications, the relations are quite small (few thousands of rows) and they don't connect strongly. In this cases, on my p1.6 it comes out about 2-3 secs. However, i can generate the same experiment with strong connectivity between the relations and it can take hours to compute. On the other hand i have seen experiments with 100 thousans of records that finished in a matter of minutes so it all depends on how many join combination there are in the data. > > --- > > Tzahi Fadida wrote: > > On Friday 11 August 2006 07:18, Bruce Momjian wrote: > > > I have looked over this addition, and I think I finally understand it. > > > Given three tables, A, B, C, which join as A->B, B->C, C->A, you can > > > really join them as A->B->C, and A->C->B. What full disjunction does > > > is to perform both of those joins, and return a one row for each join. > > > Here > > > > What it does is to return all the possible natural joins, i.e.: > > A > > B > > C > > A,B > > A,C > > ... > > A,B,C > > > > And, it removes any redundant information so that if we have a tuple > > that already contains another tuple's information that tuple is > > discarded. Also, note that the full disjunction algorithm i implemented > > is commonly used in cases where the scheme graph is cyclic > > and thus, you cannot use natural full outer join > > to compute the FD. > > > > Finally, you can FD(A,B,C,D,...) any number of relations (limited to 32 > > in the implementation) with no regard to the order between them. > > > > A case study and comparison can be found here: > > http://www.technion.ac.il/~tzahi/soc.html > > > > > is an example from the README: > > > > > > Example of an input and output of a full disjunctions: > > > INPUT: > > > > > > --A---|---B---|---C-- > > > X---Y-|-Y---Z-|-X---Z > > > a-|-b-|-b-|-c-|-a-|-d > > > > > > A,B and C are relations. X,Y and Z are attributes. a,b,c and d are > > > values. > > > > > > Note that A,B and C are connected in a cycle. That is: > > > A is connected to B on attribute Y, > > > B is connected to C on attribute Z, > > > C is connected to A on attribute X. > > > > > > The output of the full disjunctions FD(A,B,C): > > > > > >FD > > > X---Y---Z > > > a-|-b-|-c > > > a-|-b-|-d > > > > > > This code is pretty complex, so I can see why it should be in /contrib. > > > Are there reasonable use cases for this capability? > > > > > > --- > > > > > > > > > Tzahi Fadida wrote: > > > > Hi, > > > > I wish to add the fulldisjunctions function to the contrib. > > > > With the help of Jonah, we (or rather he :) created a patch with > > > > regression tests. The function is finished programmatically but > > > > still a little more code documentation touches and improved error > > > > messages are needed. All the rest was extensively tested. > > > > > > > > Attached is t
Re: [HACKERS] [PATCHES] Adding fulldisjunctions to the contrib
On Friday 11 August 2006 07:18, Bruce Momjian wrote: > I have looked over this addition, and I think I finally understand it. > Given three tables, A, B, C, which join as A->B, B->C, C->A, you can > really join them as A->B->C, and A->C->B. What full disjunction does is > to perform both of those joins, and return a one row for each join. Here What it does is to return all the possible natural joins, i.e.: A B C A,B A,C ... A,B,C And, it removes any redundant information so that if we have a tuple that already contains another tuple's information that tuple is discarded. Also, note that the full disjunction algorithm i implemented is commonly used in cases where the scheme graph is cyclic and thus, you cannot use natural full outer join to compute the FD. Finally, you can FD(A,B,C,D,...) any number of relations (limited to 32 in the implementation) with no regard to the order between them. A case study and comparison can be found here: http://www.technion.ac.il/~tzahi/soc.html > is an example from the README: > > Example of an input and output of a full disjunctions: > INPUT: > > --A---|---B---|---C-- > X---Y-|-Y---Z-|-X---Z > a-|-b-|-b-|-c-|-a-|-d > > A,B and C are relations. X,Y and Z are attributes. a,b,c and d are > values. > > Note that A,B and C are connected in a cycle. That is: > A is connected to B on attribute Y, > B is connected to C on attribute Z, > C is connected to A on attribute X. > > The output of the full disjunctions FD(A,B,C): > >FD > X---Y---Z > a-|-b-|-c > a-|-b-|-d > > This code is pretty complex, so I can see why it should be in /contrib. > Are there reasonable use cases for this capability? > > --- > > Tzahi Fadida wrote: > > Hi, > > I wish to add the fulldisjunctions function to the contrib. > > With the help of Jonah, we (or rather he :) created a patch with > > regression tests. The function is finished programmatically but > > still a little more code documentation touches and improved error > > messages are needed. All the rest was extensively tested. > > > > Attached is the patch. > > > > Works great. Just compiled from a fresh cvs which i patched with the > > attached diff. ran the fulldijsjunction.sql in the > > share/contrib/fulldisjunction and let it run and it works great. > > 10x. > > > > -- > > Regards, > > Tzahi. > > -- > > Tzahi Fadida > > Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info > > WARNING TO SPAMMERS: ?see at > > http://members.lycos.co.uk/my2nis/spamwarning.html > > [ Attachment, skipping... ] > > > ---(end of broadcast)--- > > TIP 3: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faq -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: plperl: Allow conversion from perl to
Tom Lane wrote: > [EMAIL PROTECTED] (Bruce Momjian) writes: > > pgsql/src/pl/plperl/sql: > > plperl.sql (r1.7 -> r1.8) > > > > (http://developer.postgresql.org/cvsweb.cgi/pgsql/src/pl/plperl/sql/plperl.sql.diff?r1=1.7&r2=1.8) > > Shouldn't there be an 'expected' update to go with the test-file update? > The few buildfarm members that aren't dying because of the GUC patch are > failing on this one. I'd commit a fixed expected file if I could get > HEAD to work locally, but ... OK, I have backed out both plperl patches and returned the email to the patch queue. (Strange both the guc and plperl patches didn't generate any warnings here. I don't think I can test plperl here but I know the guc was tested.) -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] New variable server_version_num
David Fetter wrote: > On Sat, Jul 29, 2006 at 09:14:16PM -0400, Greg Sabino Mullane wrote: > > Today on IRC David Fetter and some others were discussing version > > numbers and we realized that although libpq now provides the version > > of Postgres as a number, this is still a wheel that is being > > reinvented by apps many times over, as it is not available any other > > way. Hence, a small patch to provide a new variable > > "server_version_num", which is almost the same as "server_version" > > but uses the handy PG_VERSION_NUM which allows apps to do things > > like if ($version >= 80200) without having to parse apart the value > > of server_version themselves. > > What's the status on applying this patch? It is still in my mailbox. I am thinking it should be added. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Adding fulldisjunctions to the contrib
I am still waiting for someone to tell us that they would use this capability for a real-world problem. --- Tzahi Fadida wrote: > On Friday 11 August 2006 07:18, Bruce Momjian wrote: > > I have looked over this addition, and I think I finally understand it. > > Given three tables, A, B, C, which join as A->B, B->C, C->A, you can > > really join them as A->B->C, and A->C->B. What full disjunction does is > > to perform both of those joins, and return a one row for each join. Here > > What it does is to return all the possible natural joins, i.e.: > A > B > C > A,B > A,C > ... > A,B,C > > And, it removes any redundant information so that if we have a tuple > that already contains another tuple's information that tuple is discarded. > Also, note that the full disjunction algorithm i implemented > is commonly used in cases where the scheme graph is cyclic > and thus, you cannot use natural full outer join > to compute the FD. > > Finally, you can FD(A,B,C,D,...) any number of relations (limited to 32 in > the implementation) with no regard to the order between them. > > A case study and comparison can be found here: > http://www.technion.ac.il/~tzahi/soc.html > > > is an example from the README: > > > > Example of an input and output of a full disjunctions: > > INPUT: > > > > --A---|---B---|---C-- > > X---Y-|-Y---Z-|-X---Z > > a-|-b-|-b-|-c-|-a-|-d > > > > A,B and C are relations. X,Y and Z are attributes. a,b,c and d are > > values. > > > > Note that A,B and C are connected in a cycle. That is: > > A is connected to B on attribute Y, > > B is connected to C on attribute Z, > > C is connected to A on attribute X. > > > > The output of the full disjunctions FD(A,B,C): > > > >FD > > X---Y---Z > > a-|-b-|-c > > a-|-b-|-d > > > > This code is pretty complex, so I can see why it should be in /contrib. > > Are there reasonable use cases for this capability? > > > > --- > > > > Tzahi Fadida wrote: > > > Hi, > > > I wish to add the fulldisjunctions function to the contrib. > > > With the help of Jonah, we (or rather he :) created a patch with > > > regression tests. The function is finished programmatically but > > > still a little more code documentation touches and improved error > > > messages are needed. All the rest was extensively tested. > > > > > > Attached is the patch. > > > > > > Works great. Just compiled from a fresh cvs which i patched with the > > > attached diff. ran the fulldijsjunction.sql in the > > > share/contrib/fulldisjunction and let it run and it works great. > > > 10x. > > > > > > -- > > > Regards, > > > Tzahi. > > > -- > > > Tzahi Fadida > > > Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info > > > WARNING TO SPAMMERS: ?see at > > > http://members.lycos.co.uk/my2nis/spamwarning.html > > > > [ Attachment, skipping... ] > > > > > ---(end of broadcast)--- > > > TIP 3: Have you checked our extensive FAQ? > > > > > >http://www.postgresql.org/docs/faq > > -- > Regards, > Tzahi. > -- > Tzahi Fadida > Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info > WARNING TO SPAMMERS: ?see at > http://members.lycos.co.uk/my2nis/spamwarning.html -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql: Allow commenting of variables in
Tom Lane wrote: > [EMAIL PROTECTED] (Bruce Momjian) writes: > > Log Message: > > --- > > Allow commenting of variables in postgresql.conf to restore them to > > defaults. > > Please revert these patches, as they have broken the build completely > (see buildfarm). OK, I have backed out both guc patches and returned the email to the patch queue. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.2 features status
On Fri, Aug 11, 2006 at 05:27:46PM -0400, Alvaro Herrera wrote: > > Does that "rails thing" also have a bug tracker that integrates with > > mailing lists? IIRC the show-stopper on a bug tracker was finding one > > that allowed people to still use mailing lists. > > AFAIU the showstopper was that people wanted to be able to _control_ the > bugtracker using email only, i.e. not forcing you to open a web browser > to do stuff like adding comments or attachments to a bug, or closing, > etc. The only bugtracker I know that allows that is debbugs, which a nice system IMHO, but I'm sure people have differing opinions about that... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature