Re: [GENERAL] A simple question about Read committed isolation level
Tom Lane дµÀ: weiping he <[EMAIL PROTECTED]> writes: txn1: txn2: begin; begin; update table_a set col= col + 1; update table_a set col = col + 1; end; end; if two transaction begin at exact the same time, what's the result of 'col' after both transactions committed in Read committed level? it's 3 or 2? My understanding is the result is 3, If the second xact to lock the row is READ COMMITTED, you get 3. If it's SERIALIZABLE you get an error. In no case will you silently lose an update. dose is mean that I must use some kind of lock ( ... FOR UPDATE for example) to lock that row to get the result 3 in READ COMMITTED level? My understanding is even in MVCC environment, the update action would still be executed sequencly (by means of some kind of lock). What confused me is: in MVCC environment, what's the procedure of postgresql to use lock when two transaction update to the same row at the same time? thanks you. Laser ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] how to group by a joined query?
Weiping He wrote: suppose I've got two table: laser_uni=# \d t1 Table "public.t1" Column | Type | Modifiers +--+--- name | text | addr | text | laser_uni=# \d t2 Table "public.t2" Column | Type | Modifiers +-+--- name | text| len| integer | of | integer | and I want to use join to select out data and then group by one column, like this: laser_uni=# select t1.name, t1.addr, t2.name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by t2.name; ERROR: Attribute t1.name must be GROUPed or used in an aggregate function seems the I must gorup all those fields: laser_uni=# select t1.name as t1name, t1.addr as t1addr, t2.name as t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by t1.name, t1.addr, t2.name, t2.len, t2.of; t1name | t1addr | t2name | len | of +++-+ || henry | 2 | 4 || laser | 4 | 4 (2 rows) is it specification compliant or postgresql specific? Thanks reread the docs, seems use DISTINCE ON clause solved my problem: select distinct on( t2.len) t1.name as t1name, t1.addr as t1addr, t2.name as t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name; Thanks Laser ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] how to group by a joined query?
suppose I've got two table: laser_uni=# \d t1 Table "public.t1" Column | Type | Modifiers +--+--- name | text | addr | text | laser_uni=# \d t2 Table "public.t2" Column | Type | Modifiers +-+--- name | text| len| integer | of | integer | and I want to use join to select out data and then group by one column, like this: laser_uni=# select t1.name, t1.addr, t2.name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by t2.name; ERROR: Attribute t1.name must be GROUPed or used in an aggregate function seems the I must gorup all those fields: laser_uni=# select t1.name as t1name, t1.addr as t1addr, t2.name as t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by t1.name, t1.addr, t2.name, t2.len, t2.of; t1name | t1addr | t2name | len | of +++-+ || henry | 2 | 4 || laser | 4 | 4 (2 rows) is it specification compliant or postgresql specific? Thanks Laser ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] compile error on slackware 9.0 while --enable-thread-safety
while remove --enable-thread-safety everything ok. what's the matter? the error output: ---8<- make[2]: Entering directory `/usr/laser/postgresql-7.4beta1/src/port' gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../src/include -c -o path.o path.c gcc -O2 -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../src/include -c -o threads.o threads.c threads.c: In function `pqGetpwuid': threads.c:49: too few arguments to function `getpwuid_r' threads.c:49: warning: assignment makes pointer from integer without a cast threads.c: In function `pqGethostbyname': threads.c:74: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type threads.c:74: too few arguments to function `gethostbyname_r' threads.c:74: warning: assignment makes pointer from integer without a cast ---8<- Thanks and regards Laser ---(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: [GENERAL] Can postgres supports Chinese GB18030?
LitelWang wrote: It is useful for me to use Chinese tone sort order . Any version on Cygwin? Thanks for any advice . I never try GB18030 in Cygwin, but in Linux or other Unix system, you may use gb18030 as client side encoding and use UNICODE as backend encoding, and it's pretty good. regards Laser ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] any body using Solaris8 with postgresql 7.3.3
Tom Lane wrote: Weiping He <[EMAIL PROTECTED]> writes: Later I use: pg_ctl start >pgrun.log 2>&1 to start the program, and it runs ok. but, then the pmdie 2... Hm. My first thought was that you needed a We should check the theory though. After you start the postmaster using the above command, if you type control-C (or whatever your favorite interrupt character is) on the same terminal, does the postmaster shut down? regards, tom lane Ok, I'll test it. But needs more time, cause I can't access that box now, it's in office. report back later. Thanks Laser ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] any body using Solaris8 with postgresql 7.3.3
Tom Lane wrote: Weiping He <[EMAIL PROTECTED]> writes: I've met a wierd problem on a Solaris 8/sparc box with postgresql 7.3.3: the server would automatically shutdown after a period of time of not operating. The log show something like this: pmdie 2 Assuming signal 2 is SIGINT on Solaris (look in /usr/include/signal.h to make sure, but that's pretty standard), this would indicate that something is sending SIGINT to the postmaster. The postmaster will interpret that as a fast shutdown request. So the problem is not with the postmaster, but with whatever is sending the signal. I suspect this isn't a platform problem so much as a setup mistake. How are you launching the postmaster? Is it possible it's still connected to a controlling terminal? (If so, the shell would probably SIGINT the postmaster anytime you typed control-C.) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html emm, I'll check that. My first start method is: pg_ctl start -l ~/pgrun.log (I've export PGDATA=/pgsqldata) but it reports: pg_ctl: test: argument expected I'm using ksh I think, which I've also test in 7.4-devl version, the same result, ISTM a little bug in pg_ctl script, but still didn't dig into it to see if I can found the problem. Later I use: pg_ctl start >pgrun.log 2>&1 to start the program, and it runs ok. but, then the pmdie 2... Thanks Laser ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] any body using Solaris8 with postgresql 7.3.3
Martin Marques wrote: On Vie 04 Jul 2003 03:52, Weiping He wrote: Hi, all, I've met a wierd problem on a Solaris 8/sparc box with postgresql 7.3.3: the server would automatically shutdown after a period of time of not operating. The log show something like this: pmdie 2 anybody using this combo ever met this kind of problem? I've had (and have actually) PostgreSQL 7.3.x on Solaris/SPARC and never saw this problem. Comment a bit more about the problem. Configuration? Installed from source? etc From my point of view PostgreSQL is great, even on Solaris (I have some Linux boxes with PG as well). yes, I install it from the source, compiled using gcc 2.95.3, the locale should be default (en_US I think), the box got 1G ram and 2 cpus, so I don't think there is any resource problem (I've set shmmax to about 800M and adjust the postgresql.conf setting accordingly), now I'm suspecting the limit of file descriptior which is only 512 on my box, but, I still don't know why it would automatically shutdown. yeah, I also think PostgreSQL is great, but my experience with PostgreSQL mainly on linux/bsd/AIX, not so familiar with Solaris... Any hints? Thanks for your help Laser ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
