Re: [HACKERS] Best practices: MERGE
On Mon, 2005-03-07 at 19:34 -0800, David Fetter wrote: Although the SQL:2003 command MERGE has not yet been implemented in PostgreSQL, I'm guessing that there are best practices for how to implement the MERGE functionality. To recap, MERGE means (roughly) INSERT the tuple if no tuple matches certain criteria, otherwise UPDATE using similar criteria. Don't understand that way round... I thought the logic was: UPDATE WHERE . (locate row) IF NOT FOUND THEN INSERT (new row) You can create a procedure to do that, but MERGE would work better. ISTM that would require writing some new code that was a mix of heap_update and heap_insert logic for the low level stuff would be required. The existing heap_update code is most similar, since the logic is roughly UPDATE WHERE (locate row) IF FOUND THEN INSERT (new row version) though with various changes to row visibility stuff. One might aim to do this in two stages: 1. initially support a single row upsert such as MySQL's REPLACE command 2. a full implementation of MERGE that used set logic as per the spec ... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Best practices: MERGE
On Tue, Mar 08, 2005 at 12:27:21PM +0800, Christopher Kings-Lynne wrote: Luckily, PG 8 is available for this. Do you have a short example? No, and I think it should be in the manual as an example. You will need to enter a loop that uses exception handling to detect unique_violation. Pursuant to an IRC discussion to which Dennis Bjorklund and Christopher Kings-Lynne made most of the contributions, please find enclosed an example patch demonstrating an UPSERT-like capability. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ? upsert.diff Index: doc/src/sgml/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.61 diff -c -r1.61 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 14 Feb 2005 00:54:26 - 1.61 --- doc/src/sgml/plpgsql.sgml 8 Mar 2005 08:19:31 - *** *** 2003,2008 --- 2003,2042 don't use literalEXCEPTION/ without need. /para /tip + example id=plpgsql-upsert-example + para + This example uses an literalEXCEPTION/ to commandUPDATE/ or + commandINSERT/, as appropriate. + + programlisting + CREATE TABLE db (a INT PRIMARY KEY, b TEXT); + + CREATE FUNCTION merge_db (key INT, data TEXT) RETURNS VOID AS + $$ + BEGIN + LOOP + UPDATE db SET b = data WHERE a = key; + IF found THEN + RETURN; + END IF; + + BEGIN + INSERT INTO db(a,b) VALUES (key, data); + RETURN; + EXCEPTION WHEN unique_violation THEN + -- do nothing + END; + END LOOP; + END; + $$ + LANGUAGE plpgsql; + + SELECT merge_db (1, 'david'); + SELECT merge_db (1, 'dennis'); + /programlisting + + /para + /example /sect2 /sect1 ---(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] Cost of XLogInsert CRC calculations
On Mon, 2005-03-07 at 20:50 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Well, we're using the CRC in 3 separate places... (1) for xlog records (2) for complete blocks copied to xlog (3) for control files For (1), records are so short that probably CRC16 would be sufficient without increasing the error rate noticeably. I think I'd like to keep (3) at CRC64...its just too important. Plus thats slightly less code to change. The control files are so short that CRC16 would be plenty. My money is on (2) being the source of most of that run-time anyway, Undoubtedly, so there's not going to be much win from micro-optimization by having several different CRC functions. Agreed. I would go for CRC32 across the board, myself. Sold. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Best practices: MERGE
You can create a procedure to do that, but MERGE would work better. ISTM that would require writing some new code that was a mix of heap_update and heap_insert logic for the low level stuff would be required. The existing heap_update code is most similar, since the logic is roughly UPDATE WHERE (locate row) IF FOUND THEN INSERT (new row version) though with various changes to row visibility stuff. One might aim to do this in two stages: 1. initially support a single row upsert such as MySQL's REPLACE command 2. a full implementation of MERGE that used set logic as per the spec ... The main issue is dealing with merging into unique index race conditions. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ERROR: unrecognized node type in PostgresMain( )
Perhaps you should rebuild the backend with -g (see --enable-debug) so that gdb can actually be somewhat helpful. It's usually a good idea to have --enable-cassert turned on when hacking C code, too. When I rebuilded the backend with --enable-debug and --enable-cassert the database stops and restarts, before it reaches the breakpoint at create_plan() in createplan.c. So I can't figure out what is happening. (errors came out at function Assert()) (My bet is that control got as far as executor startup, which you already said you hadn't fixed to understand this new node type, so the error is exactly what I'd expect.) As far as I can see, the Executor starts with the invocation of the function ExecutorStart() which calls CreateExecutorState() and then ExecInitNode() Is this right? At the ExecInitNode() I checked the messages unrecognized node type and I saw that the ERROR happens before this part of code. Can you please suggest were to look?Any idea? It's looks like that I miss something :). thanks !! -martha mc --- [EMAIL PROTECTED] writes: The problem is that while, Im running the postgres with gdb and I set a breakpoint at the function create_plan() of createplan.c, after some step commands,the gdb says that it cant recongnize the node 121 in the PostgresMain() function. That's not what gdb said at all; all you know is that the error happened someplace where you didn't have control. (My bet is that control got as far as executor startup, which you already said you hadn't fixed to understand this new node type, so the error is exactly what I'd expect.) Perhaps you should rebuild the backend with -g (see --enable-debug) so that gdb can actually be somewhat helpful. It's usually a good idea to have --enable-cassert turned on when hacking C code, too. FWIW, I don't personally ever do debugging in a standalone backend, unless the problem is one that keeps a normal backend from starting. It's much more pleasant to use a regular psql session to issue SQL commands, and attach to the connected backend with gdb in another terminal window. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] About b-tree usage
let me, i have turned enable_seqscan to off, in order to discourage optimizer to choose seq_scan whenever an idex_scan can be used. But in this case, why optimizer don't chooses seq_scan (discourage is different than prevent) ? At many cases i need only a small fragment of raws to be retrieved. But this extreme case is a real-scenario (not the most frequent but real). I try to find a way to achieve good performence even for the extreme case. Is there any way? ps. In bibliografy, there is a different alternative for indices. except th simple approach of attr_val, rid is the alternative attr_val, set of rids. The second means the attaches to each discrete attr_val the set o rid's of all raws with same attr_val. Is this alternative taken into account in postgres? On Mon, 7 Mar 2005, Jeff Davis wrote: In that case, sequential scan is faster, but perhaps the planner doesn't know that ahead of time. Try turning on more statistics if you haven't already, and then run ANALYZE again. If the planner sees a range, perhaps it assumes that it is a highly selective range, when in fact, it consists of all of the tuples. Also, make sure enable_seqscan is true (in case you turned it off for testing or something and forgot). A seqscan is usually faster when a large proportion of the tuples are returned because: (1) It uses sequential I/O; whereas an index might access tuples in a random order. (2) It doesn't have to read the index's disk pages at all. I suspect you don't need to return all the tuples in the table. If you include the details of a real scenario perhaps the people on the list could be more helpful. ---(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] About b-tree usage
Ioannis Theoharis wrote: let me, i have turned enable_seqscan to off, in order to discourage optimizer to choose seq_scan whenever an idex_scan can be used. But in this case, why optimizer don't chooses seq_scan (discourage is different than prevent) ? You probably know that PostgreSQL uses a cost-based optimizer. The optimizer chooses different plans based on the cost it calculates for them. enable_seqscan = OFF is very primitive but effective: it tells the optimizer to raise the cost of a sequential scan to a value going towards infinity. When it comes to the choice between seq scan and index scan, the optimizer will now always choose the index scan. It does not known anymore if sequential scan would be cheaper -- *you* have told the optimizer that it is not. Only when there is no other way except seq scan to execute your query at all, then the optimizer must choose this very costly path. An example is an unqualified SELECT * FROM table; -- there is no path with an index here. I hope that answers your first question. As you see, enable_seqscan = OFF should not be used for production systems, but only for debugging. Perhaps it's useful to set at query level, but not in postgresql.conf. Best Regards, Michael Paesold ---(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] About b-tree usage
Hi, if you're using a pg version prio to 8.0 your pitfall might also be a conversion between int and bigint datatypes. So if you're doing somthing like SELECT a.x, b.y, c.y FROM a, b WHERE a.x = b.x; and a.x is INT4 and b.x is INT8 (or BIGINT) the planner counts this as a data conversion and uses a full table scan. Greetings, Klaus Ioannis Theoharis wrote: let me, i have turned enable_seqscan to off, in order to discourage optimizer to choose seq_scan whenever an idex_scan can be used. But in this case, why optimizer don't chooses seq_scan (discourage is different than prevent) ? At many cases i need only a small fragment of raws to be retrieved. But this extreme case is a real-scenario (not the most frequent but real). I try to find a way to achieve good performence even for the extreme case. Is there any way? ps. In bibliografy, there is a different alternative for indices. except th simple approach of attr_val, rid is the alternative attr_val, set of rids. The second means the attaches to each discrete attr_val the set o rid's of all raws with same attr_val. Is this alternative taken into account in postgres? On Mon, 7 Mar 2005, Jeff Davis wrote: In that case, sequential scan is faster, but perhaps the planner doesn't know that ahead of time. Try turning on more statistics if you haven't already, and then run ANALYZE again. If the planner sees a range, perhaps it assumes that it is a highly selective range, when in fact, it consists of all of the tuples. Also, make sure enable_seqscan is true (in case you turned it off for testing or something and forgot). A seqscan is usually faster when a large proportion of the tuples are returned because: (1) It uses sequential I/O; whereas an index might access tuples in a random order. (2) It doesn't have to read the index's disk pages at all. I suspect you don't need to return all the tuples in the table. If you include the details of a real scenario perhaps the people on the list could be more helpful. ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ERROR: unrecognized node type in PostgresMain( )
[EMAIL PROTECTED] writes: Perhaps you should rebuild the backend with -g (see --enable-debug) so that gdb can actually be somewhat helpful. It's usually a good idea to have --enable-cassert turned on when hacking C code, too. When I rebuilded the backend with --enable-debug and --enable-cassert the database stops and restarts, before it reaches the breakpoint at create_plan() in createplan.c. So I can't figure out what is happening. (errors came out at function Assert()) That indicates a problem you need to *fix*, not ignore. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] How to cast VARCHAR to BYTEA and vice-versa?
Hello all, I have a table with a VARCHAR column that I need to convert to a BYTEA. How do I cast VARCHAR to BYTEA? The following doesn't seem to work as it yields the 'cannot cast varchar to bytea' error message: varchar_data::bytea On the same topic, how do I do the reverse, that is, how to I cast from BYTEA back to VARCHAR? Thanks, -Michael Moran ---(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] How to cast VARCHAR to BYTEA and vice-versa?
Thank you, Tom. Yes, the exact bytes in the varchar datum (without encoding) is what I would like to become the bytes in the BYTEA. So, how do I create a cast WITHOUT FUNCTION as you mention below? I assume plpgsql is required, right? Is there anyway this can be done in a VIEW without having to use plpgsql in a Function (a.k.a. stored procedure)? Is there no simple cast or conversion method I can call that does this for me? Best Regards, Michael Moran _ From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tue 3/8/2005 9:18 AM To: Moran.Michael Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] How to cast VARCHAR to BYTEA and vice-versa? Moran.Michael [EMAIL PROTECTED] writes: How do I cast VARCHAR to BYTEA? I think it would work to create a cast WITHOUT FUNCTION, assuming that the semantics you want is that the exact bytes in the varchar datum become the bytes in the bytea (no encoding or backslashing conversions). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] fool-toleranced optimizer
Hi there, I just noticed a little optimizer problem - in second query there is unused 'tycho t2' table alias which gets backend buried. This is artificial query, I just tried to check if optimizier could recognize this. tycho=# explain analyze select t.pm_ra,t.pm_dec from tycho t where t.pm_ra 20.2 and t.pm_ra 18; QUERY PLAN - Index Scan using pm_ra_idx on tycho t (cost=0.00..9821.83 rows=2613 width=8) (actual time=0.061..12.518 rows=1466 loops=1) Index Cond: ((pm_ra 20.2::double precision) AND (pm_ra 18::double precision)) Total runtime: 14.726 ms (3 rows) tycho=# explain analyze select t.pm_ra,t.pm_dec from tycho t, tycho t2 where t.pm_ra 20.2 and t.pm_ra 18; ... It's doing Nested Loop, probably, so I didn' wait until it completed Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, 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]
Re: [HACKERS] fool-toleranced optimizer
Oleg Bartunov wrote: I just noticed a little optimizer problem - in second query there is unused 'tycho t2' table alias which gets backend buried. It's not an unused table alias, it is specifying the cartesian product of `tycho' with itself. I don't see how this is an optimizer problem: it's a perfectly legitimate query, albeit one that is unlikely to execute very quickly. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] How to cast VARCHAR to BYTEA and vice-versa?
On Tue, Mar 08, 2005 at 02:16:52PM -0800, Moran.Michael wrote: Yes, the exact bytes in the varchar datum (without encoding) is what I would like to become the bytes in the BYTEA. So, how do I create a cast WITHOUT FUNCTION as you mention below? I assume plpgsql is required, right? Did you check the reference page for CREATE CAST already? -- Alvaro Herrera ([EMAIL PROTECTED]) Por suerte hoy explotó el califont porque si no me habrÃa muerto de aburrido (Papelucho) ---(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] How to cast VARCHAR to BYTEA and vice-versa?
How do I cast VARCHAR to BYTEA? I think it would work to create a cast WITHOUT FUNCTION, assuming that the semantics you want is that the exact bytes in the varchar datum become the bytes in the bytea (no encoding or backslashing conversions). Are pg_escape_bytea and pg_unescape_bytea available from the backend as well as libpq? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Where to see the patch queue (was Re: [PATCHES] Patch
Robert Treat wrote: On Thursday 03 March 2005 19:08, Neil Conway wrote: Thomas F.O'Connell wrote: committers, myself included, deserve some blame for not making more rapid progress on the queue of unapplied patches for 8.1. In the meanwhile, the queue should be easier for folks to find (why is the pgpatches queue the only one linked from postgresql.org, but it is almost empty?) Wow, I hadn't realized just how big that queue had gotten. I've got no qualms adding another link to the website, but can we get a scheme wrt to links for the various patche queues? Is pgpatches for the current stable branch and pgpatches2 for the current development branch? (Well, that can't be true since before 8.0 release they were both for unstable branches...) Incidentally, I think DEV_FAQ 1.4 should be modified to include mentioning the patch queues. Done: 1.4) What do I do after choosing an item to work on? Send an email to pgsql-hackers with a proposal for what you want to do (assuming your contribution is not trivial). Working in isolation is not advisable: others may be working on the same TODO item; you may have misunderstood the TODO item; your approach may benefit from the review of others. A web site is maintained for patches that are ready to be applied, http://momjian.postgresql.org/cgi-bin/pgpatches, and those that are being kept for the next release, http://momjian.postgresql.org/cgi-bin/pgpatches2. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] postgreSQL-8.0.1 compilation with icc-8.1 on Itanium-2
Does the Intel compiler not support inline assembler?
_InterlockedExchange() is a function call and we prefer to have asm()
code if we can get it.
---
Vikram Kalsi wrote:
Just an update, the __INTEL_COMPILER is true on Itanium if icc is
being used. So, the following worked for me-
---BEGIN OLD
s_lock.h-
#if defined(__ia64__) || defined(__ia64) /* __ia64 used by ICC compiler? */
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
#define TAS(lock) tas(lock)
static __inline__ int
tas(volatile slock_t *lock)
{
long intret;
__asm__ __volatile__(
xchg4 %0=%1,%2\n
: =r(ret), +m(*lock)
: r(1)
: memory);
return (int) ret;
}
#endif /* __ia64__ || __ia64 */
-END OLD
s_lock.h-
---BEGIN NEW
s_lock.h-
#if defined(__ia64__) || defined(__ia64) /* __ia64 used by ICC compiler? */
/* Intel Itanium */
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
#define TAS(lock) tas(lock)
#if defined(__INTEL_COMPILER)
static __inline__ int
tas(volatile slock_t *lock)
{
int ret;
ret = _InterlockedExchange(lock,1);
return ret;
}
#else/* __INTEL_COMPILER */
static __inline__ int
tas(volatile slock_t *lock)
{
long intret;
__asm__ __volatile__(
xchg4 %0=%1,%2\n
: =r(ret), +m(*lock)
: r(1)
: memory);
return (int) ret;
}
#endif /* __INTEL_COMPILER */
#endif /* __ia64__ || __ia64 */
-END NEW
s_lock.h-
Thanks and Regards,
On Fri, 4 Mar 2005 00:57:15 -0500, Vikram Kalsi [EMAIL PROTECTED] wrote:
Tom, Peter,
I have been able to compile and sucessfully run pgSQL after replacing
the asm statement in postgresql-8.0.1/src/include/storage/s_lock.h
with an equivalent intrinsic for the Itanium platform-
-BEGIN OLD
s_lock.h
#if defined(__ia64__) || defined(__ia64) /* __ia64 used by ICC compiler? */
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
#define TAS(lock) tas(lock)
static __inline__ int
tas(volatile slock_t *lock)
{
long intret;
__asm__ __volatile__(
xchg4 %0=%1,%2\n
: =r(ret), +m(*lock)
: r(1)
: memory);
return (int) ret;
}
#endif /* __ia64__ || __ia64 */
---END OLD
s_lock.h
-BEGIN NEW
s_lock.h--
#if defined(__ia64__) || defined(__ia64) /* __ia64 used by ICC compiler? */
#define HAS_TEST_AND_SET
typedef unsigned int slock_t;
#define TAS(lock) tas(lock)
static __inline__ int
tas(volatile slock_t *lock)
{
int ret;
ret = _InterlockedExchange(lock,1);
return ret;
}
#endif /* __ia64__ || __ia64 */
---END NEW
s_lock.h--
The binary appears to be stable and the tpc-H benchmark executed
successfully against it as well. I also ran the regression test but
the following tests failed, the reasons for which I haven't
investigated yet
(http://www.cse.psu.edu/~kalsi/files/regression.diffs)-
test create_function_1... FAILED
test create_type ... FAILED
test create_table ... FAILED
test create_function_2... FAILED
test triggers ... FAILED
test create_operator ... FAILED
test create_view ... FAILED
test transactions ... FAILED
test misc ... FAILED
test select_views ... FAILED
test rules... FAILED
test plpgsql ... failed (ignored)
test copy2... FAILED
test rangefuncs ... FAILED
test conversion ... FAILED
test stats... FAILED
The _InterlockedExchange() function is defined in ia64intrin.h header file
int _InterlockedExchange(volatile int *Target, long value)
Do an exchange operation atomically. Maps to the xchg4 instruction.
More information is available at
http://www.intel.com/software/products/compilers/clin/docs/ug_cpp/lin1072.htm
Also, some other points to note, _ICC wasn't defined on my
installation when I was using icc by setting env var CC=icc. So, when
I tried to put a #if
Re: [HACKERS] postgreSQL-8.0.1 compilation with icc-8.1 on Itanium-2
Vikram Kalsi wrote: The _InterlockedExchange() function is defined in ia64intrin.h header file int _InterlockedExchange(volatile int *Target, long value) Do an exchange operation atomically. Maps to the xchg4 instruction. More information is available at http://www.intel.com/software/products/compilers/clin/docs/ug_cpp/lin1072.htm Also, some other points to note, _ICC wasn't defined on my installation when I was using icc by setting env var CC=icc. So, when I tried to put a #if defined for using asm() for gcc and _InterlockedExchange(), it didn't work. So, after this change gcc compilation fails. Oh, I see _InterlockedExchange is inlined assembler. Let me work on a patch and post it to you. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] postgreSQL-8.0.1 configure --enable-thread-safety with
The Intel compiler complains about global variables that are not marked either static or extern. They are remarks so I think you are OK with that. The attached patch should remove the warnings but I am not applying it because a non-static/extern global variable should be fine in C code. The larger problem is that we are not picking up the proper thread flags for the Intel C compiler. Any idea what they are? Please try compiling in src/tools/thread manually to get the flags working and report back. It isn't find the thread library functions like pthread_join. --- Vikram Kalsi wrote: Hi, I am trying to build postgresql-8.0.1 with icc-8.1.028 on a Linux RHEL AS3 SMP Itanium2 machine and I get an error as follows when I run configure --enable-thread-safety as follows- shellexport CC=icc shellexport CFLAGS=-static -fPIC shellexport LDFLAGS=-L/opt/intel_cc_80/lib shellexport CPPFLAGS=-I/opt/intel_cc_80/include shellconfigure --prefix=$MY_HOME/dbms/pgsql --enable-thread-safety --disable-shared --with-low-memory --with-pgport=5410 .. .. .. configure:18836: icc -o conftest -static -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wold-style-definition -Wendif-labels -fno-strict-aliasing -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DIN_CONFIGURE -D_GNU_SOURCE -L/opt/intel_cc_80/lib conftest.c -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl -lm -lbsd 5 ./src/tools/thread/thread_test.c(75): remark #1418: external definition with no prior declaration char *temp_filename_1; ^ ./src/tools/thread/thread_test.c(76): remark #1418: external definition with no prior declaration char *temp_filename_2; ^ ./src/tools/thread/thread_test.c(78): remark #1418: external definition with no prior declaration pthread_mutex_t init_mutex = PTHREAD_MUTEX_INITIALIZER; ^ ./src/tools/thread/thread_test.c(80): remark #1418: external definition with no prior declaration volatile int thread1_done = 0; ^ ./src/tools/thread/thread_test.c(81): remark #1418: external definition with no prior declaration volatile int thread2_done = 0; ^ ./src/tools/thread/thread_test.c(83): remark #1418: external definition with no prior declaration volatile int errno1_set = 0; ^ ./src/tools/thread/thread_test.c(84): remark #1418: external definition with no prior declaration volatile int errno2_set = 0; ^ ./src/tools/thread/thread_test.c(105): remark #1418: external definition with no prior declaration bool platform_is_threadsafe = true; ^ /tmp/iccQ3B36U.o(.text+0x1d2): In function `main': : undefined reference to `pthread_mutex_lock' /tmp/iccQ3B36U.o(.text+0x202): In function `main': : undefined reference to `pthread_create' /tmp/iccQ3B36U.o(.text+0x232): In function `main': : undefined reference to `pthread_create' /tmp/iccQ3B36U.o(.text+0x2e2): In function `main': : undefined reference to `pthread_mutex_unlock' /tmp/iccQ3B36U.o(.text+0x302): In function `main': : undefined reference to `pthread_join' /tmp/iccQ3B36U.o(.text+0x322): In function `main': : undefined reference to `pthread_join' /tmp/iccQ3B36U.o(.text+0x602): In function `func_call_1': : undefined reference to `pthread_mutex_lock' /tmp/iccQ3B36U.o(.text+0x612): In function `func_call_1': : undefined reference to `pthread_mutex_unlock' /tmp/iccQ3B36U.o(.text+0x872): In function `func_call_2': : undefined reference to `pthread_mutex_lock' /tmp/iccQ3B36U.o(.text+0x882): In function `func_call_2': : undefined reference to `pthread_mutex_unlock' configure:18839: $? = 1 configure: program exited with status 1 configure: failed program was: #line 18830 configure #include confdefs.h #include ./src/tools/thread/thread_test.c configure:18853: result: no configure:18863: error: *** Thread test program failed. Your platform is not thread-safe. *** Check the file 'config.log'for the exact reason. *** *** You can use the configure option --enable-thread-safety-force *** to force threads to be enabled. However, you must then run *** the program in src/tools/thread and add locking function calls *** to your applications to guarantee thread safety. The complete log is online at http://www.cse.psu.edu/~kalsi/files2/config.log The same works when I use gcc(3.2.3) and configure also works with icc-8.1 if I dont use --enable-thread-safety! Can anybody see if I am doing it wrong? Any suggestions for resolving this error? Thanks, -Vikram
[HACKERS] two questions related to tablespace in PG8.0.1
Here are two questions related to PG8.0.1: 1. durability of create tablespace - what happens if several checkpoints done after create tablespace then system crashes - without redo, will the PG_VERSION file and symlinks survive in win32? Seems checkpoint didn't sync the content of PG_VERSION file. 2. possible race on set_short_version(location) while creating tablespace - what if two processes reach this point at the same time? So directory emptiness check will not fail and both will create their own PG_VERSION file ... Thanks, Qingqing ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] two questions related to tablespace in PG8.0.1
Qingqing Zhou [EMAIL PROTECTED] writes: Here are two questions related to PG8.0.1: 1. durability of create tablespace - what happens if several checkpoints done after create tablespace then system crashes - without redo, will the PG_VERSION file and symlinks survive in win32? Seems checkpoint didn't sync the content of PG_VERSION file. There is no such thing as crash without redo: that is what WAL is all about. The creation of the tablespace will be correctly replayed from WAL. (Of course, this claim depends on various assumptions about whether fsync behaves per spec ... but if it does not, tablespace creation is hardly the only thing that will fail.) 2. possible race on set_short_version(location) while creating tablespace - what if two processes reach this point at the same time? There is no race --- the point of that code is to ensure that if two users concurrently try to create two tablespaces pointing at the same directory, only one will succeed. In any case, since tablespace creation requires superuser permissions, there is no issue about whether the user might be malicious ... an attacker who has gained database superuser can already break things in arbitrary ways. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] postgreSQL-8.0.1 configure --enable-thread-safety with
Bruce Momjian wrote: The attached patch should remove the warnings but I am not applying it because a non-static/extern global variable should be fine in C code. What's the harm in applying it? Variables and functions not used outside the compilation unit in which they are defined _should_ be marked static; it's not required, but I think it's good style. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] postgreSQL-8.0.1 configure --enable-thread-safety with icc-8.1 on RHEL-AS3 Itanium-2 gives error
I was ignoring the warnings anyway. I didn't look into that much but after upgrading to RHEL AS4, I am able to compile successfully with --enable-thread-safety Thanks, On Tue, 8 Mar 2005 23:28:20 -0500 (EST), Bruce Momjian pgman@candle.pha.pa.us wrote: The Intel compiler complains about global variables that are not marked either static or extern. They are remarks so I think you are OK with that. The attached patch should remove the warnings but I am not applying it because a non-static/extern global variable should be fine in C code. The larger problem is that we are not picking up the proper thread flags for the Intel C compiler. Any idea what they are? Please try compiling in src/tools/thread manually to get the flags working and report back. It isn't find the thread library functions like pthread_join. --- Vikram Kalsi wrote: Hi, I am trying to build postgresql-8.0.1 with icc-8.1.028 on a Linux RHEL AS3 SMP Itanium2 machine and I get an error as follows when I run configure --enable-thread-safety as follows- shellexport CC=icc shellexport CFLAGS=-static -fPIC shellexport LDFLAGS=-L/opt/intel_cc_80/lib shellexport CPPFLAGS=-I/opt/intel_cc_80/include shellconfigure --prefix=$MY_HOME/dbms/pgsql --enable-thread-safety --disable-shared --with-low-memory --with-pgport=5410 .. .. .. configure:18836: icc -o conftest -static -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wold-style-definition -Wendif-labels -fno-strict-aliasing -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -DIN_CONFIGURE -D_GNU_SOURCE -L/opt/intel_cc_80/lib conftest.c -lz -lreadline -ltermcap -lcrypt -lresolv -lnsl -ldl -lm -lbsd 5 ./src/tools/thread/thread_test.c(75): remark #1418: external definition with no prior declaration char *temp_filename_1; ^ ./src/tools/thread/thread_test.c(76): remark #1418: external definition with no prior declaration char *temp_filename_2; ^ ./src/tools/thread/thread_test.c(78): remark #1418: external definition with no prior declaration pthread_mutex_t init_mutex = PTHREAD_MUTEX_INITIALIZER; ^ ./src/tools/thread/thread_test.c(80): remark #1418: external definition with no prior declaration volatile int thread1_done = 0; ^ ./src/tools/thread/thread_test.c(81): remark #1418: external definition with no prior declaration volatile int thread2_done = 0; ^ ./src/tools/thread/thread_test.c(83): remark #1418: external definition with no prior declaration volatile int errno1_set = 0; ^ ./src/tools/thread/thread_test.c(84): remark #1418: external definition with no prior declaration volatile int errno2_set = 0; ^ ./src/tools/thread/thread_test.c(105): remark #1418: external definition with no prior declaration bool platform_is_threadsafe = true; ^ /tmp/iccQ3B36U.o(.text+0x1d2): In function `main': : undefined reference to `pthread_mutex_lock' /tmp/iccQ3B36U.o(.text+0x202): In function `main': : undefined reference to `pthread_create' /tmp/iccQ3B36U.o(.text+0x232): In function `main': : undefined reference to `pthread_create' /tmp/iccQ3B36U.o(.text+0x2e2): In function `main': : undefined reference to `pthread_mutex_unlock' /tmp/iccQ3B36U.o(.text+0x302): In function `main': : undefined reference to `pthread_join' /tmp/iccQ3B36U.o(.text+0x322): In function `main': : undefined reference to `pthread_join' /tmp/iccQ3B36U.o(.text+0x602): In function `func_call_1': : undefined reference to `pthread_mutex_lock' /tmp/iccQ3B36U.o(.text+0x612): In function `func_call_1': : undefined reference to `pthread_mutex_unlock' /tmp/iccQ3B36U.o(.text+0x872): In function `func_call_2': : undefined reference to `pthread_mutex_lock' /tmp/iccQ3B36U.o(.text+0x882): In function `func_call_2': : undefined reference to `pthread_mutex_unlock' configure:18839: $? = 1 configure: program exited with status 1 configure: failed program was: #line 18830 configure #include confdefs.h #include ./src/tools/thread/thread_test.c configure:18853: result: no configure:18863: error: *** Thread test program failed. Your platform is not thread-safe. *** Check the file 'config.log'for the exact reason. *** *** You can use the configure option --enable-thread-safety-force *** to force threads to be enabled. However, you must then run *** the program in src/tools/thread and add locking function calls *** to your applications to guarantee thread safety.
[HACKERS] Current CVS parallel test lock
Running latest cvs on freebsd gives me errors in test strings and test errors, and completely hangs at this point: parallel group (14 tests): limit prepare sequence copy2 truncate rowtypes polymorphism temp domain conversion rangefuncs without_oid alter_table Chris parallel group (13 tests): text name varchar char oid boolean int2 int8 float4 int4 float8 bit numeric boolean ... ok char ... ok name ... ok varchar ... ok text ... ok int2 ... ok int4 ... ok int8 ... ok oid ... ok float4 ... ok float8 ... ok bit ... ok numeric ... ok test strings ... FAILED test numerology ... ok parallel group (20 tests): comments time lseg reltime path timetz abstime circle tinterval box point type_sanity polygon interval inet timestamp timestamptz date oidjoins opr_sanity point... ok lseg ... ok box ... ok path ... ok polygon ... ok circle ... ok date ... ok time ... ok timetz ... ok timestamp... ok timestamptz ... ok interval ... ok abstime ... ok reltime ... ok tinterval... ok inet ... ok comments ... ok oidjoins ... ok type_sanity ... ok opr_sanity ... ok test geometry ... ok test horology ... ok test insert ... ok test create_function_1... FAILED test create_type ... ok test create_table ... ok test create_function_2... ok test copy ... ok parallel group (7 tests): create_operator create_aggregate vacuum triggers constraints inherit create_misc constraints ... FAILED triggers ... ok create_misc ... ok create_aggregate ... ok create_operator ... ok inherit ... ok vacuum ... ok parallel group (2 tests): create_view create_index create_index ... ok create_view ... ok test sanity_check ... ok test errors ... FAILED test select ... ok parallel group (18 tests): select_distinct_on select_into update random namespace select_having select_distinct case select_implicit btree_index aggregates union subselect portals join arrays transactions hash_index select_into ... ok select_distinct ... ok select_distinct_on ... ok select_implicit ... ok select_having... ok subselect... ok union... ok case ... ok join ... ok aggregates ... ok transactions ... ok random ... ok portals ... ok arrays ... ok btree_index ... ok hash_index ... ok update ... ok namespace... ok test privileges ... ok test misc ... ok parallel group (5 tests): select_views portals_p2 cluster foreign_key rules select_views ... ok portals_p2 ... ok rules... ok foreign_key ... ok cluster ... ok parallel group (14 tests): limit prepare sequence copy2 truncate rowtypes polymorphism temp domain conversion rangefuncs without_oid alter_table*** ./expected/strings.out Sun Mar 14 12:25:17 2004 --- ./results/strings.out Wed Mar 9 14:48:33 2005 *** *** 19,26 ' - third line' AS Illegal comment within continuation; ERROR: syntax error at or near ' - third line' at character 75 - LINE 3: ' - third line' - ^ -- -- test conversions between various string types -- E021-10 implicit casting among the character data types --- 19,24 == *** ./expected/create_function_1.outThu Oct 21 11:12:15 2004 --- ./results/create_function_1.out Wed Mar 9 14:48:38 2005 *** *** 56,63 CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql AS 'not even SQL'; ERROR: syntax error at or near not at character 62 - LINE 2: AS 'not even SQL'; - ^ CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql AS 'SELECT 1, 2, 3;'; ERROR: return type mismatch in function declared to return integer --- 56,61 == *** ./expected/constraints.out Thu Oct 21 11:12:15 2004 ---
Re: [HACKERS] two questions related to tablespace in PG8.0.1
There is no such thing as crash without redo: that is what WAL is all about. The creation of the tablespace will be correctly replayed from WAL. (Of course, this claim depends on various assumptions about whether fsync behaves per spec ... but if it does not, tablespace creation is hardly the only thing that will fail.) Yes, if replayed, the creation will be ok. But the case I mentioned will not replay the WAL. The point is that current mdsync() implementation does not take care of streams, so the files opened by AllocateFile() will not get flushed. Most files like pg_fsm.cache are ok, since we don't expect them to survive after crash. But is PG_VERSION in creation of tablespace ok? 2. possible race on set_short_version(location) while creating tablespace - what if two processes reach this point at the same time? There is no race --- the point of that code is to ensure that if two users concurrently try to create two tablespaces pointing at the same directory, only one will succeed. In any case, since tablespace creation requires superuser permissions, there is no issue about whether the user might be malicious ... an attacker who has gained database superuser can already break things in arbitrary ways. understood. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Current CVS parallel test lock
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Running latest cvs on freebsd gives me errors in test strings and test errors, and completely hangs at this point: Time for a clean rebuild? I've not seen any failures, neither has the build farm ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Current CVS parallel test lock
Christopher Kings-Lynne [EMAIL PROTECTED]
Running latest cvs on freebsd gives me errors in test strings and test
errors, and completely hangs at this point:
I got a similar problem and system hangs randomly(esp. in test strings) in
win32-mingw. The problem I found is in pgunlink(). There is a potential
deadloop in
while (unlink(path))
{
...
}
However, if we wait long enough( I waited for 20 mins or so), the system
finally go on. I guess that's because of the hot-long-discussed win32's
inability of removing file-in-use problem(though FILE_SHARE_DELETE flag is
added). Shall we add a time-limit or something to handle this situation?
parallel group (14 tests): limit prepare sequence copy2 truncate
rowtypes polymorphism temp domain conversion rangefuncs without_oid
alter_table
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Current CVS parallel test lock
Time for a clean rebuild? I've not seen any failures, neither has the build farm ... cvs up gmake clean ./configure --prefix=/home/chriskl/local --enable-depend --enable-debug --enable-cassert --with-perl --with-pam --with-openssl gmake check Still hangs. Gets new failure in create function and constraints. Seems to be differences in how it's indicating syntax errors. My src/bin/psql is in the 7_4_STABLE branch, but that shouldn't affect anything. Chris parallel group (13 tests): text name varchar char oid boolean int8 int2 float4 int4 float8 bit numeric boolean ... ok char ... ok name ... ok varchar ... ok text ... ok int2 ... ok int4 ... ok int8 ... ok oid ... ok float4 ... ok float8 ... ok bit ... ok numeric ... ok test strings ... FAILED test numerology ... ok parallel group (20 tests): comments time reltime path timetz circle point lseg box tinterval polygon inet abstime type_sanity interval timestamp date oidjoins timestamptz opr_sanity point... ok lseg ... ok box ... ok path ... ok polygon ... ok circle ... ok date ... ok time ... ok timetz ... ok timestamp... ok timestamptz ... ok interval ... ok abstime ... ok reltime ... ok tinterval... ok inet ... ok comments ... ok oidjoins ... ok type_sanity ... ok opr_sanity ... ok test geometry ... ok test horology ... ok test insert ... ok test create_function_1... FAILED test create_type ... ok test create_table ... ok test create_function_2... ok test copy ... ok parallel group (7 tests): create_operator create_aggregate constraints vacuum triggers inherit create_misc constraints ... FAILED triggers ... ok create_misc ... ok create_aggregate ... ok create_operator ... ok inherit ... ok vacuum ... ok parallel group (2 tests): create_view create_index create_index ... ok create_view ... ok test sanity_check ... ok test errors ... FAILED test select ... ok parallel group (18 tests): select_distinct_on select_into update random namespace select_having select_distinct btree_index case hash_index aggregates union select_implicit portals join arrays transactions subselect select_into ... ok select_distinct ... ok select_distinct_on ... ok select_implicit ... ok select_having... ok subselect... ok union... ok case ... ok join ... ok aggregates ... ok transactions ... ok random ... ok portals ... ok arrays ... ok btree_index ... ok hash_index ... ok update ... ok namespace... ok test privileges ... ok test misc ... ok parallel group (5 tests): portals_p2 select_views cluster foreign_key rules select_views ... ok portals_p2 ... ok rules... ok foreign_key ... ok cluster ... ok parallel group (14 tests): limit prepare sequence copy2 truncate polymorphism rowtypes temp rangefuncs conversion domain without_oid alter_table*** ./expected/strings.out Sun Mar 14 12:25:17 2004 --- ./results/strings.out Wed Mar 9 15:19:16 2005 *** *** 19,26 ' - third line' AS Illegal comment within continuation; ERROR: syntax error at or near ' - third line' at character 75 - LINE 3: ' - third line' - ^ -- -- test conversions between various string types -- E021-10 implicit casting among the character data types --- 19,24 == *** ./expected/create_function_1.outWed Mar 9 15:18:46 2005 --- ./results/create_function_1.out Wed Mar 9 15:19:21 2005 *** *** 56,63 CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql AS 'not even SQL'; ERROR: syntax error at or near not at character 62 - LINE 2: AS 'not even SQL'; - ^ CREATE FUNCTION test1 (int) RETURNS int LANGUAGE sql AS 'SELECT 1, 2, 3;'; ERROR: return type mismatch
Re: [HACKERS] Current CVS parallel test lock
On Wed, Mar 09, 2005 at 03:24:20PM +0800, Christopher Kings-Lynne wrote: gmake clean Have you tried gmake distclean? I just built the latest HEAD on FreeBSD 4.11-STABLE and all tests passed. Configure options were: ./configure --prefix=/usr/local/pgsql81 \ --with-pgport=5481 \ --with-openssl \ --with-perl \ --with-python \ --with-tcl \ --with-tclconfig=/usr/local/lib/tcl8.4 \ --enable-debug \ --enable-cassert -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Current CVS parallel test lock
Have you tried gmake distclean? I just built the latest HEAD on FreeBSD 4.11-STABLE and all tests passed. Configure options were: Hrm, I just did a gmake install; gmake installcheck - that worked fine. Then I did gmake check again and now that works fine... It must have been picking up something from my previously installed pgsql. Chris ---(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
