[HACKERS] RelOptInfo-reltargetlist
Hi all, apologize if this is a trivial question but I'd like to understand what values are stored in the RelOptInfo-reltargetlist or, better, how I can check such values in the information schema to understand what they refer to. I've tried to look in the pg_attribute table but I was not able to find a match. In other words, how can I check what is the oid of the listcell pointing to in the database schema? Thanks, Luca ---(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] PAM authentication fails for local UNIX users
Hi all, http://archives.postgresql.org/pgsql-admin/2003-02/msg00301.php I also try to address the same issue.. I run postmaster as postgres user and pg_hba.conf includes the following entry: local all dhanaraj pam However, the authentication fails for this unix local user, whereas it works for LDAP users. bash-3.00$ psql -h superdesktop.india.sun.com -U dhanaraj mydb Password for user dhanaraj: psql: FATAL: PAM authentication failed for user dhanaraj The following error message that I could see in the server log: .. LOG: pam_authenticate failed: Conversation failure FATAL: PAM authentication failed for user dhanaraj LOG: pam_authenticate failed: No account present for user FATAL: PAM authentication failed for user dhanaraj The non-root user does not have the permission to read other unix local user password. I found two solutions: 1. usermod -K defaultpriv=Basic,file_dac_read postgres - Gives privilege to read all files. This solution works. Is it the right way to do? 2. chmod +s processName - This does not work, because postgres never allows this. Is there anyother solution to this problem? Thanks Dhanaraj ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PAM authentication fails for local UNIX users
Dhanaraj M wrote: Hi all, http://archives.postgresql.org/pgsql-admin/2003-02/msg00301.php I also try to address the same issue.. I run postmaster as postgres user and pg_hba.conf includes the following entry: local all dhanaraj pam However, the authentication fails for this unix local user, whereas it works for LDAP users. bash-3.00$ psql -h superdesktop.india.sun.com -U dhanaraj mydb Password for user dhanaraj: psql: FATAL: PAM authentication failed for user dhanaraj The following error message that I could see in the server log: .. LOG: pam_authenticate failed: Conversation failure FATAL: PAM authentication failed for user dhanaraj LOG: pam_authenticate failed: No account present for user FATAL: PAM authentication failed for user dhanaraj The non-root user does not have the permission to read other unix local user password. I found two solutions: 1. usermod -K defaultpriv=Basic,file_dac_read postgres - Gives privilege to read all files. This solution works. Is it the right way to do? 2. chmod +s processName - This does not work, because postgres never allows this. Is there anyother solution to this problem? Usage questions really don't belong on -hackers - in future please use -general. Both your proposed solutions are utterly insecure. See http://itc.musc.edu/wiki/PostgreSQL for some discussion of using PAM for postgres auth. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PAM authentication fails for local UNIX users
Andrew Dunstan wrote: Dhanaraj M wrote: The non-root user does not have the permission to read other unix local user password. I found two solutions: 1. usermod -K defaultpriv=Basic,file_dac_read postgres - Gives privilege to read all files. This solution works. Is it the right way to do? 2. chmod +s processName - This does not work, because postgres never allows this. Is there anyother solution to this problem? Usage questions really don't belong on -hackers - in future please use -general. Both your proposed solutions are utterly insecure. The problem what Dhanaraj tries to address is how to secure solve problem with PAM and local user. Other servers (e.g. sshd) allow to run master under root (with limited privileges) and forked process under normal user. But postgresql requires start as non-root user. It limits to used common pattern. There is important question: Is current requirement to run postgresql under non-root OK? If yes, than we must update PAM documentation to explain this situation which will never works secure. Or if we say No, it is stupid limitation (in case when UID 0 says nothing about user's privileges) then we must start discussion about solution. See http://itc.musc.edu/wiki/PostgreSQL for some discussion of using PAM for postgres auth. It also offer also same insecure solution to add read permission on shadow for postgresql user. Zdenek ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PAM authentication fails for local UNIX users
Zdenek Kotala wrote: The problem what Dhanaraj tries to address is how to secure solve problem with PAM and local user. Other servers (e.g. sshd) allow to run master under root (with limited privileges) and forked process under normal user. But postgresql requires start as non-root user. It limits to used common pattern. There is important question: Is current requirement to run postgresql under non-root OK? If yes, than we must update PAM documentation to explain this situation which will never works secure. Or if we say No, it is stupid limitation (in case when UID 0 says nothing about user's privileges) then we must start discussion about solution. For now I think we should update the docs. You really can't compare postgres with sshd - ssh connections are in effect autonomous. I suspect the changes involved in allowing us to run as root and then give up privileges safely would be huge, and the gain quite small. I'd rather see an HBA fallback mechanism, which I suspect might overcome most of the problems being encountered here. cheers andrew ---(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] random crashes on -HEAD for a few days now
at least one of my buildfarm members (emu) is crashing on what seems totally unrelated regression tests for a few days now: http://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=emubr=HEAD it took me about 10 tries to reproduce that manually and I'm getting the following stacktrace: #0 varbit_out (fcinfo=0x88c75000) at varbit.c:549 549 x = *sp; (gdb) bt #0 varbit_out (fcinfo=0x88c75000) at varbit.c:549 #1 0x1c217930 in FunctionCall1 (flinfo=0x1, arg1=2294763520) at fmgr.c:1195 #2 0x1c036fae in printtup (slot=0x88c730b0, self=0x7ecf4bc8) at printtup.c:326 #3 0x1c10ab13 in ExecSelect (slot=0x88c730b0, dest=0x88c75000, estate=0x88c7301c) at execMain.c:1427 #4 0x1c10a8b3 in ExecutePlan (estate=0x88c7301c, planstate=0x88c731b4, operation=CMD_SELECT, numberTuples=0, direction=-2000203776, dest=0x7ecf4bc8) at execMain.c:1353 #5 0x1c109793 in ExecutorRun (queryDesc=0x7fc60574, direction=ForwardScanDirection, count=0) at execMain.c:243 #6 0x1c19b917 in PortalRunSelect (portal=0x7fd9f01c, forward=1 '\001', count=0, dest=0x7ecf4bc8) at pquery.c:943 #7 0x1c19b63e in PortalRun (portal=0x7fd9f01c, count=2147483647, isTopLevel=1 '\001', dest=0x7ecf4bc8, altdest=0x7ecf4bc8, completionTag=0xcfbd1e50 ) at pquery.c:797 #8 0x1c19709b in exec_simple_query ( query_string=0x7e4e301c SELECT v,\n SUBSTRING(v FROM 2 FOR 4) AS sub_2_4,\n SUBSTRING(v FROM 7 FOR 13) AS sub_7_13,\n SUBSTRING(v FROM 6) AS sub_6\n FROM VARBIT_TABLE;) at postgres.c:962 #9 0x1c199fe2 in PostgresMain (argc=4, argv=0x894395c0, username=0x89439454 mastermind) at postgres.c:3529 #10 0x1c171fbe in BackendRun (port=0x88ac1400) at postmaster.c:3177 #11 0x1c171864 in BackendStartup (port=0x88ac1400) at postmaster.c:2800 #12 0x1c16f8af in ServerLoop () at postmaster.c:1272 #13 0x1c16ee85 in PostmasterMain (argc=6, argv=0xcfbd224c) at postmaster.c:1027 #14 0x1c12bf74 in main (argc=6, argv=0xcfbd224c) at main.c:188 Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] RelOptInfo-reltargetlist
Luca Ferrari [EMAIL PROTECTED] writes: apologize if this is a trivial question but I'd like to understand what values are stored in the RelOptInfo-reltargetlist or, better, how I can check such values in the information schema to understand what they refer to. I've tried to look in the pg_attribute table but I was not able to find a match. In other words, how can I check what is the oid of the listcell pointing to in the database schema? That list doesn't contain OIDs ... it contains Var nodes. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PAM authentication fails for local UNIX users
On Mon, August 20, 2007 19:52, Andrew Dunstan wrote: I'd rather see an HBA fallback mechanism, which I suspect might overcome most of the problems being encountered here. I implemented a form of that once, so on local connections you could do ident mapping with fallback to PAM or some other password authentication. That seemed useful, e.g. for granting non-interactive access to a program running under a dedicated user and requiring a password from everyone else. The implementation also allowed for a bit more flexibility in the auth mechanism. The main objections I recall were: 1. The protocol doesn't allow for multiple authentication prompts. My own proposal didn't have that problem since it only introduced an optional ident authentication that continued looking for a matching rule if the current user name was not in the given map, but it's a problem for more general approaches. 2. For real, fully generalized fallback, you'd also need to overhaul the HBA config file format completely. 3. It was considered unsafe to add even the most limited of fallback options, because the HBA config is designed to pick just one auth mechanism for any connection attempt, based on only the first three columns of the config file. An admin who didn't understand the new auth mechanism could use it to write an unsafe HBA configuration, provided it also broke the existing go from specific-permissive to general-restrictive design guideline. Personally I think it'd take some careful aim to shoot yourself in the foot like that, but IIRC it was enough for an I don't like it vote. Jeroen ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Why NESTED LOOP Not Allowed for FULL and RIGHT Join.
Hi,
can anyone let me know that why Nested Loop path is not allowed if jointype
are JOIN_RIGHT or JOIN_FULL?
At match_unsorted_outer(), we having case where nestjoinOK = false if there
are
JOIN_RIGHT or JOIN_FULL.
match_unsorted_outer()
{
case JOIN_RIGHT:
case JOIN_FULL:
nestjoinOK = false;
..
}
wondering why ?
Regards,
Rushabh Lathia
[EMAIL PROTECTED]
Re: [HACKERS] random crashes on -HEAD for a few days now
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: at least one of my buildfarm members (emu) is crashing on what seems totally unrelated regression tests for a few days now: I was wondering about that ... it took me about 10 tries to reproduce that manually and I'm getting the following stacktrace: #0 varbit_out (fcinfo=0x88c75000) at varbit.c:549 549 x = *sp; Just eyeballing that code, it looks like it will try to fetch the byte immediately beyond the end of the bit array, when the number of bits is an exact multiple of 8. This is unlikely to cause a problem but it *could* happen that the input is right up against the end of memory. Could you check whether that is what happened here? (The important question is whether the input seems to be sane, ie, len isn't huge.) 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] Why NESTED LOOP Not Allowed for FULL and RIGHT Join.
Rushabh Lathia [EMAIL PROTECTED] writes: can anyone let me know that why Nested Loop path is not allowed if jointype are JOIN_RIGHT or JOIN_FULL? How are you going to track which of the inner-relation rows never find a join partner and hence need to be emitted on their own? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] random crashes on -HEAD for a few days now
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: at least one of my buildfarm members (emu) is crashing on what seems totally unrelated regression tests for a few days now: I was wondering about that ... it took me about 10 tries to reproduce that manually and I'm getting the following stacktrace: #0 varbit_out (fcinfo=0x88c75000) at varbit.c:549 549 x = *sp; Just eyeballing that code, it looks like it will try to fetch the byte immediately beyond the end of the bit array, when the number of bits is an exact multiple of 8. This is unlikely to cause a problem but it *could* happen that the input is right up against the end of memory. Could you check whether that is what happened here? (The important question is whether the input seems to be sane, ie, len isn't huge.) end of memory sounds familiar to: http://archives.postgresql.org/pgsql-hackers/2005-06/msg00819.php which is how emu is (still) set up. as for len it seems to be 0: #0 varbit_out (fcinfo=0x88c75000) at varbit.c:549 s = (VarBit *) 0x88c75000 result = 0x84d33128 r = 0x84d33128 sp = (bits8 *) 0x88c75000 Address 0x88c75000 out of bounds x = 0 '\0' i = 0 k = 0 len = 0 Stefan ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] INSERT/SELECT and excessive foreign key checks
On 19-aug-2007, at 12:38, Tom Lane wrote: Hack is the right word. People keep proposing variants of the idea that the executor should optimize updates on the basis of examining the query tree to see whether columns changed or not, and they're always wrong. You don't know what else might have been done to the row by BEFORE triggers. but that's something it can check for. if there are BEFORE triggers on the table, don't do it. An additional problem with your proposal is that it fails to consider other changes that might be happening concurrently -- eg, what if some other backend deletes a source row after you copy it, and commits before you do? There would be an interval with no committed row having that FK value, and no one holding a row lock on the referenced PK row, so some third transaction could delete the PK row. so if it checks those FKs being carried over also only once, that would close that hole, right? it would just be nice to not have to disable triggers altogether in this case. there is a person twiddling his/her thumbs while all this checking and re-checking is going on. Lodewijk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] random crashes on -HEAD for a few days now
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: as for len it seems to be 0: #0 varbit_out (fcinfo=0x88c75000) at varbit.c:549 s = (VarBit *) 0x88c75000 result = 0x84d33128 r = 0x84d33128 sp = (bits8 *) 0x88c75000 Address 0x88c75000 out of bounds x = 0 '\0' i = 0 k = 0 len = 0 Hmm ... s and sp really shouldn't be equal, nor equal to fcinfo, but it's likely that the compiler optimized them into the same register. We need to confirm what actually got passed as the argument. Could you go to frame 1 and see what is in its local fcinfo var, in particular see what args[0] is? I'm betting it's 0x88c75000 minus 8 ... if so, look at what is in those last 8 bytes. If that's int32 8 followed by int32 0, then indeed we have a zero-length bitstring at the end of memory. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PAM authentication fails for local UNIX users
Andrew Dunstan wrote: Zdenek Kotala wrote: The problem what Dhanaraj tries to address is how to secure solve problem with PAM and local user. Other servers (e.g. sshd) allow to run master under root (with limited privileges) and forked process under normal user. But postgresql requires start as non-root user. It limits to used common pattern. There is important question: Is current requirement to run postgresql under non-root OK? If yes, than we must update PAM documentation to explain this situation which will never works secure. Or if we say No, it is stupid limitation (in case when UID 0 says nothing about user's privileges) then we must start discussion about solution. For now I think we should update the docs. I agree. I suspect the changes involved in allowing us to run as root and then give up privileges safely would be huge, and the gain quite small. The main problem there is that there are a lot of different ways how to do it and there is not standard. For example on Solaris applications use RBAC functionality to handle privileges and this is not available on other platforms and so on... I'd rather see an HBA fallback mechanism, which I suspect might overcome most of the problems being encountered here. The question is why don't use fallback functionality guaranteed by PAM and naming services. It seems that only fallback to or from password auth makes sense. Other could be handled by PAM/naming. Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] random crashes on -HEAD for a few days now
Tom Lane wrote:
Stefan Kaltenbrunner [EMAIL PROTECTED] writes:
as for len it seems to be 0:
#0 varbit_out (fcinfo=0x88c75000) at varbit.c:549
s = (VarBit *) 0x88c75000
result = 0x84d33128
r = 0x84d33128
sp = (bits8 *) 0x88c75000 Address 0x88c75000 out of bounds
x = 0 '\0'
i = 0
k = 0
len = 0
Hmm ... s and sp really shouldn't be equal, nor equal to fcinfo, but
it's likely that the compiler optimized them into the same register.
We need to confirm what actually got passed as the argument. Could you
go to frame 1 and see what is in its local fcinfo var, in particular see
what args[0] is? I'm betting it's 0x88c75000 minus 8 ... if so, look at
what is in those last 8 bytes. If that's int32 8 followed by int32 0,
then indeed we have a zero-length bitstring at the end of memory.
with a bit of help from alvaro:
(gdb) frame 1
#1 0x1c217930 in FunctionCall1 (flinfo=0x1, arg1=2294763520) at fmgr.c:1195
1195result = FunctionCallInvoke(fcinfo);
(gdb) info args
flinfo = (FmgrInfo *) 0x1
arg1 = 2294763520
(gdb) info locals
fcinfo = {flinfo = 0x84d33028, context = 0x0, resultinfo = 0x0,
isnull = 0 '\0', nargs = 1, arg = {2294763512, 16, 2377208416, 1,
2343471056, 2343471056, 4294967295, 2342861632, 0, 0, 3485276712,
470248306, 11, 1, 4294967295, 257, 2294762772, 6, 2294762772,
227882802,
197, 0, 0, 20480, 3703223788, 4098, 4294967295, 0, 1, 0, 3485276792,
471883625, 470050980, 1560, 227725220, 764289000, 3703223788,
2228453376,
3485276872, 3485276864, 2234862596, 2263890620, 3485277048, 471889873,
2234862596, 2263890620, 1560, 3485277024, 5, 533, 533, 0, 0,
3485276904,
4294967295, 2228432896, 2263890588, 483, 4031427043, 9314280, 0,
262142,
0, 471661208, 184, 65538, 0, 2137853048, 0, 1560, 0 repeats 21 times,
2228433032, 2228433044, 1565, 3485277048, 471950646, 1565, 0, 0, 0},
argnull =
\000\001\000\000\000\000\000\000\030\006\000\000\2100?204\000\000\000\000\003\000\000\000\230\033??220L5\205\000\004\000\000???\033??220L5\205\b\000\000\000X\022?213?\033??216?\\034\220L5\205\b\000\000\000\002\000\000\000@\022\034
\000\000\000X\022?213??210?D\005\034??210}
result = 2228432924
(gdb) x/16x 0x88c75000 - 8
0x88c74ff8: 0x0020 0x Cannot access memory at
address 0x88c75000
is that what you are interested in ?
Stefan
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] Status of 8.3 patches
Here is the current 8.3 patch status: http://developer.postgresql.org/index.php/Todo:PatchStatus As you can see we have two major patches remaining, tsearch2 and HOT. Tom is working on tsearch2 now and Paven just posted an updated HOT patch. (Only the GIT (group index tuples) patch didn't make it into 8.3.) There are a few minor patches left: o Automatic adjustment of bgwriter_lru_maxpages We show this as waiting for performance results. I am thinking we should hold this for 8.4. o Error correction for n_dead_tuples This shows as waiting on another patch. Again, I am thinking to keep it for 8.4. o Per function search_path Tom will complete this one. o Table function support Neil, where are you on this? -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://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
[HACKERS] Function quote_literal broken in CATALOG_VERSION_NO 200707251
Just FYI: I took the latest snapshot CATALOG_VERSION_NO 200707251 and function quote_literal throws an error: # select quote_literal(1); 2007-08-20 18:50:17 PDT ERROR: function quote_literal(integer) does not exist at character 8 2007-08-20 18:50:17 PDT HINT: No function matches the given name and argument types. You might need to add explicit type casts. 2007-08-20 18:50:17 PDT STATEMENT: select quote_literal(1); ERROR: function quote_literal(integer) does not exist LINE 1: select quote_literal(1); I went back to an older snapshot CATALOG_VERSION_NO 200705211 and it works with the same SunStudio compiler and options: postgres=# select quote_literal(1); quote_literal --- '1' (1 row) -Jignesh ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Status of 8.3 patches
Bruce Momjian wrote: o Automatic adjustment of bgwriter_lru_maxpages We show this as waiting for performance results. I am thinking we should hold this for 8.4. Agreed. I spent close to a week trying different benchmarks and configurations and simple test cases on a test server and my laptop, and couldn't demonstrate bgwriter making a positive impact in any configuration I tried. The theory behind the patch is sound, but it looks like a lot more testing and analysis is needed. o Error correction for n_dead_tuples This shows as waiting on another patch. Again, I am thinking to keep it for 8.4. It was waiting on the vacuum oldestxmin patch, which didn't make it to 8.3. I don't care for the patch myself, but it was submitted well before feature freeze and deserves a review. It looks good to me at first glance. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Status of 8.3 patches
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Heikki Linnakangas wrote: Bruce Momjian wrote: o Automatic adjustment of bgwriter_lru_maxpages We show this as waiting for performance results. I am thinking we should hold this for 8.4. Agreed. I spent close to a week trying different benchmarks and configurations and simple test cases on a test server and my laptop, and couldn't demonstrate bgwriter making a positive impact in any configuration I tried. The theory behind the patch is sound, but it looks like a lot more testing and analysis is needed. Wouldn't real world testing be needed to actually gain insight to this patch? Joshua D. Drake o Error correction for n_dead_tuples This shows as waiting on another patch. Again, I am thinking to keep it for 8.4. It was waiting on the vacuum oldestxmin patch, which didn't make it to 8.3. I don't care for the patch myself, but it was submitted well before feature freeze and deserves a review. It looks good to me at first glance. - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGyfZjATb/zqfZUUQRAltsAKClIbgh+r2ktW9MM8EO/rfp/TrsWACgq34w RdjYafRztMrnxztwlpRhWzQ= =Yfiq -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Status of 8.3 patches
Heikki Linnakangas wrote: Bruce Momjian wrote: o Error correction for n_dead_tuples This shows as waiting on another patch. Again, I am thinking to keep it for 8.4. It was waiting on the vacuum oldestxmin patch, which didn't make it to 8.3. I don't care for the patch myself, but it was submitted well before feature freeze and deserves a review. It looks good to me at first glance. My opinion as well. -- Alvaro Herrerahttp://www.advogato.org/person/alvherre La fuerza no está en los medios físicos sino que reside en una voluntad indomable (Gandhi) ---(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] PGparam extension version 0.3
Attached is version 0.3 of the proposed PGparam extension to the libpq
API. I think we are wrapping up our changes in the short term and
will begin using our api for our internal projects. There were a lot
of changes and reorganizations, but the big features are that client
side geometry types were introduced and we folded the PGparam struct
into PGconn which simplifies the interface in our opinion. See the
attached changes file for a complete list. Also attached is a small
test which is a good overview of how the proposed API changes work.
The code has been reorganized into a proper patch so that things are
injected into libpq in what we think are the right places along with
an updated makefile and exports.txt.
There are many things we discussed but did not implement because of
time concerns, for example client side support for binary arrays and a
queryf interface which would map input parameters into the various put
functions. These are exciting things but fairly complex features and
may require some reorganization of code on the backend to do properly.
Hopefully this will help developers who would like to use the high
performance binary interface or optimize access to the database from
their particular language. Assuming the code is acceptable to the
community, we will keep the patch up to date through the 8.4 cycle and
write the documentation.
Things are obviously really busy right now with HOT and getting 8.3
locked down...but comments and suggestions are most welcome.
merlin
pg_param.tgz
Description: GNU Zip compressed data
#if defined(_WIN32) || defined(_WIN64)
# define U64FMT %I64u
typedef unsigned __int64 myuint64_t;
#else
# define U64FMT %llu
typedef unsigned long long myuint64_t;
#endif
//#include pg.h
#include /esilo/src/pgsql/src/interfaces/libpq/libpq-fe.h
#include stdlib.h
#include string.h
#include limits.h
#ifndef TRUE
# define TRUE 1
#endif
#ifndef FALSE
# define FALSE 0
#endif
#define countof(array) (sizeof(array)/sizeof(array[0]))
#define TEST_TBLNAME param_test
static const char *create_table =
CREATE TABLE TEST_TBLNAME (
a_char\char\,
a_bool boolean,
a_int2 int2,
a_int4 int4,
a_int8 int8,
a_float4 float4,
a_float8 float8,
a_text text,
a_nulltext text,
a_byteabytea,
a_macaddr macaddr,
a_pointpoint,
a_lseg lseg,
a_box box,
a_circle circle,
a_path path,
a_polygon polygon);
static const char *insert_command =
INSERT INTO TEST_TBLNAME VALUES
($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17);
static int getresfmt(int argc, char **argv);
int main(int argc, char **argv)
{
int i;
char ch = 0;
int b = 0;
short i2 = 0;
int i4 = 0;
myuint64_t i8;
float f4 = 0;
double f8 = 0;
char *text;
int text_isnull;
unsigned char *bytea2;
unsigned char bytea[4] = {1, 10, 220, 255};
PGmacaddr mac;
PGpoint pt;
PGlseg lseg;
PGbox box;
PGcircle circle;
PGpath path;
PGpolygon polygon;
PGconn *conn;
PGresult *res;
ExecStatusType status;
PGpoint points[64];
int resultFormat = getresfmt(argc, argv);
conn = PQconnectdb(hostaddr=127.0.0.1 user=postgres);
if(PQstatus(conn) != CONNECTION_OK)
{
printf(connection failure\n);
return 1;
}
res = PQexec(conn, create_table);
PQclear(res);
/* clear test table */
res = PQexec(conn, DELETE FROM TEST_TBLNAME);
PQclear(res);
i8 = ULLONG_MAX;
PQputchar(conn, UCHAR_MAX);
PQputbool(conn, TRUE);
PQputint2(conn, USHRT_MAX);
PQputint4(conn, UINT_MAX);
PQputint8(conn, i8); /* pqlib has no 64-bit type. */
PQputfloat4(conn, 111.234f);
PQputfloat8(conn, .234567);
PQputtext(conn, This is some text);
PQputnull(conn);
PQput(conn, bytea, (int)sizeof(bytea), InvalidOid, 1);
mac.a = 1;
mac.b = 2;
mac.c = 3;
mac.d = 4;
mac.e = 5;
mac.f = 6;
PQputmacaddr(conn, mac);
pt.x = -11.23;
pt.y = 23.11;
PQputpoint(conn, pt);
lseg.pts[0].x = 6712;
lseg.pts[0].y = 2517.89;
lseg.pts[1].x = 9087.125;
lseg.pts[1].y = 7821.987;
PQputlseg(conn, lseg);
box.high.x = 100;
box.high.y = 10;
box.low.x = 10;
box.low.y = 1;
PQputbox(conn, box);
circle.center.x = 100;
circle.center.y = 200;
circle.radius = 300;
PQputcircle(conn, circle);
path.closed = 0; /* open path */
path.npts = countof(points);
path.pts = points;
for(i=0; i path.npts; i++)
{
path.pts[i].x = i;
path.pts[i].y = i+1;
}
PQputpath(conn, path);
polygon.npts = 16;
polygon.pts = points;
if(!PQputpolygon(conn, polygon))
printf(Polygon: %s\n, PQerrorMessage(conn));
/* Execute query */
PQparamExec(conn, insert_command, 1, res);
/* check result */
status = PQresultStatus(res);
if(status != PGRES_COMMAND_OK status != PGRES_TUPLES_OK)
{
printf(%s\n, res ? PQresultErrorMessage(res) : PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
return 1;
}
printf(\nGetting results in %s
[HACKERS] Problem with locks
I'm debugging this problem with stalled transactions waiting on locks which
have already been granted and I'm puzzled by something. What is this PGPROC
entry from?
It's not a real process (pid is 0) and it's not garbage either (prev and next
both point to a real PGPROC entry, namely MyProc which as you can see is
waiting for this PGPROC).
(gdb) p *(PGPROC*)(ShmemBase + MyProc-links.prev)
$2 = {links = {prev = 2520941752, next = 2520941752}, sem = {semId = 1, semNum
= 0}, waitStatus = 0, xid = 0, xmin = 0, pid = 0,
databaseId = 0, roleId = 0, inCommit = 0 '\0', inVacuum = 0 '\0',
isAutovacuum = 0 '\0', lwWaiting = 0 '\0', lwExclusive = 2 '\002',
lwWaitLink = 0x0, waitLock = 0x2, waitProcLock = 0x0, waitLockMode = 0,
heldLocks = 0, myProcLocks = {{prev = 0, next = 1}, {
prev = 4294967296, next = 0}, {prev = 3028785127, next = 360654}, {prev =
73183493944770560, next = 137438953600}, {
prev = 2520929504, next = 2520929072}, {prev = 2520940536, next =
2520940536}, {prev = 1, next = 0}, {prev = 0,
next = 4294967296}, {prev = 4294967296, next = 0}, {prev = 2, next = 0},
{prev = 0, next = 0}, {prev = 1, next = 4294967296}, {
prev = 0, next = 4000475635}, {prev = 360707, next = 73183493944770560},
{prev = 128, next = 2520933680}, {prev = 2520933680,
next = 2520914616}}, subxids = {overflowed = -72 '�', nxids = 0, xids =
{0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0,
0, 0, 1, 0, 0, 1, 0, 0, 1626942462, 0, 16384, 16430, 0, 16777216, 2, 0,
2520930944, 0, 2520930944, 0, 2520914784, 0, 2520914784,
0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0}}}
(gdb) p MyProc-ShmemBase
$3 = (PGPROC *) 0xff9ac36e36e264b8
(gdb) p (unsigned long)MyProc-ShmemBase
$4 = 2520941752
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Status of 8.3 patches
Joshua D. Drake wrote: Heikki Linnakangas wrote: Bruce Momjian wrote: o Automatic adjustment of bgwriter_lru_maxpages We show this as waiting for performance results. I am thinking we should hold this for 8.4. Agreed. I spent close to a week trying different benchmarks and configurations and simple test cases on a test server and my laptop, and couldn't demonstrate bgwriter making a positive impact in any configuration I tried. The theory behind the patch is sound, but it looks like a lot more testing and analysis is needed. Wouldn't real world testing be needed to actually gain insight to this patch? I would expect a fairly static benchmark workload to benefit from having a bgwriter, more so than more unpredictable real world applications. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Status of 8.3 patches
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Heikki Linnakangas wrote: Joshua D. Drake wrote: Heikki Linnakangas wrote: Bruce Momjian wrote: o Automatic adjustment of bgwriter_lru_maxpages We show this as waiting for performance results. I am thinking we should hold this for 8.4. Agreed. I spent close to a week trying different benchmarks and configurations and simple test cases on a test server and my laptop, and couldn't demonstrate bgwriter making a positive impact in any configuration I tried. The theory behind the patch is sound, but it looks like a lot more testing and analysis is needed. Wouldn't real world testing be needed to actually gain insight to this patch? I would expect a fairly static benchmark workload to benefit from having a bgwriter, more so than more unpredictable real world applications. Hmmm, I find that real world applications are quite predictable over time. Certainly you have spikes (good pr, whatever) but in general with a little bit of monitoring it is quite possible to evaluate a generally expected result. I guess my point is, if the patch looks good and does not appear to hurt anything, why not apply it? At least that way we can start to review the progress of the feature itself as it starts to see use. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGyftwATb/zqfZUUQRAkwjAJ9xGlzYci6dT3jJy5PrPMYxZOmt0ACffeUm Br+UQp+k4XVjpyQDSIba4hk= =jDki -END PGP SIGNATURE- ---(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] Problem with locks
Gregory Stark wrote:
I'm debugging this problem with stalled transactions waiting on locks which
have already been granted and I'm puzzled by something. What is this PGPROC
entry from?
It's not a real process (pid is 0) and it's not garbage either (prev and next
both point to a real PGPROC entry, namely MyProc which as you can see is
waiting for this PGPROC).
Doesn't this look like your pointer arithmetic being borked? There are
too many values that look invalid.
(gdb) p *(PGPROC*)(ShmemBase + MyProc-links.prev)
$2 = {links = {prev = 2520941752, next = 2520941752}, sem = {semId = 1,
semNum = 0}, waitStatus = 0, xid = 0, xmin = 0, pid = 0,
databaseId = 0, roleId = 0, inCommit = 0 '\0', inVacuum = 0 '\0',
isAutovacuum = 0 '\0', lwWaiting = 0 '\0', lwExclusive = 2 '\002',
lwWaitLink = 0x0, waitLock = 0x2, waitProcLock = 0x0, waitLockMode = 0,
heldLocks = 0, myProcLocks = {{prev = 0, next = 1}, {
prev = 4294967296, next = 0}, {prev = 3028785127, next = 360654}, {prev
= 73183493944770560, next = 137438953600}, {
prev = 2520929504, next = 2520929072}, {prev = 2520940536, next =
2520940536}, {prev = 1, next = 0}, {prev = 0,
next = 4294967296}, {prev = 4294967296, next = 0}, {prev = 2, next =
0}, {prev = 0, next = 0}, {prev = 1, next = 4294967296}, {
prev = 0, next = 4000475635}, {prev = 360707, next =
73183493944770560}, {prev = 128, next = 2520933680}, {prev = 2520933680,
next = 2520914616}}, subxids = {overflowed = -72 '�', nxids = 0, xids
= {0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0,
0, 0, 1, 0, 0, 1, 0, 0, 1626942462, 0, 16384, 16430, 0, 16777216, 2, 0,
2520930944, 0, 2520930944, 0, 2520914784, 0, 2520914784,
0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0}}}
(gdb) p MyProc-ShmemBase
$3 = (PGPROC *) 0xff9ac36e36e264b8
(gdb) p (unsigned long)MyProc-ShmemBase
$4 = 2520941752
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
---(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] Status of 8.3 patches
Joshua D. Drake wrote: Heikki Linnakangas wrote: Joshua D. Drake wrote: Heikki Linnakangas wrote: Bruce Momjian wrote: o Automatic adjustment of bgwriter_lru_maxpages We show this as waiting for performance results. I am thinking we should hold this for 8.4. Agreed. I spent close to a week trying different benchmarks and configurations and simple test cases on a test server and my laptop, and couldn't demonstrate bgwriter making a positive impact in any configuration I tried. The theory behind the patch is sound, but it looks like a lot more testing and analysis is needed. Wouldn't real world testing be needed to actually gain insight to this patch? I would expect a fairly static benchmark workload to benefit from having a bgwriter, more so than more unpredictable real world applications. Hmmm, I find that real world applications are quite predictable over time. Certainly you have spikes (good pr, whatever) but in general with a little bit of monitoring it is quite possible to evaluate a generally expected result. I guess my point is, if the patch looks good and does not appear to hurt anything, why not apply it? At least that way we can start to review the progress of the feature itself as it starts to see use. If it doesn't appear to have any positive effect, why would we apply it? If we apply the patch, how would you monitor it's effectiveness in a live database? There's nothing to compare against. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] Status of 8.3 patches
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Heikki Linnakangas wrote: Joshua D. Drake wrote: Heikki Linnakangas wrote: Bruce Momjian wrote: o Automatic adjustment of bgwriter_lru_maxpages We show this as waiting for performance results. I am thinking we should hold this for 8.4. Agreed. I spent close to a week trying different benchmarks and configurations and simple test cases on a test server and my laptop, and couldn't demonstrate bgwriter making a positive impact in any configuration I tried. The theory behind the patch is sound, but it looks like a lot more testing and analysis is needed. Wouldn't real world testing be needed to actually gain insight to this patch? I would expect a fairly static benchmark workload to benefit from having a bgwriter, more so than more unpredictable real world applications. Hmmm, I find that real world applications are quite predictable over time. Certainly you have spikes (good pr, whatever) but in general with a little bit of monitoring it is quite possible to evaluate a generally expected result. I guess my point is, if the patch looks good and does not appear to hurt anything, why not apply it? At least that way we can start to review the progress of the feature itself as it starts to see use. Yeah, you mean like commit_delay. It really worked great, that reviewing of a feature, you know. It only took 3 years until someone realized that it didn't work as advertised. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC En las profundidades de nuestro inconsciente hay una obsesiva necesidad de un universo lógico y coherente. Pero el universo real se halla siempre un paso más allá de la lógica (Irulan) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Status of 8.3 patches
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alvaro Herrera wrote: Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Heikki Linnakangas wrote: Joshua D. Drake wrote: I guess my point is, if the patch looks good and does not appear to hurt anything, why not apply it? At least that way we can start to review the progress of the feature itself as it starts to see use. Yeah, you mean like commit_delay. It really worked great, that reviewing of a feature, you know. It only took 3 years until someone realized that it didn't work as advertised. You can not compare the relevant smallness of use from three years ago to the explosion of use at present. It is certainly unfortunate that commit_delay didn't work as advertised, but then again had we not applied it, we would have never known in the first place and now we have the opportunity to fix or remove it. You can compare other such features that many people don't touch that are starting to show promise over time such as cpu_tuple_cost. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGyf4bATb/zqfZUUQRAnPOAKCP8wLnXIJ5B7cYdHGBudZC+bALrACeL6HI 6ZYXLsKD/MSgdnqVkGK/THY= =HqYq -END PGP SIGNATURE- ---(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] Status of 8.3 patches
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Heikki Linnakangas wrote: Joshua D. Drake wrote: Heikki Linnakangas wrote: Joshua D. Drake wrote: Heikki Linnakangas wrote: Bruce Momjian wrote: o Automatic adjustment of bgwriter_lru_maxpages \ I would expect a fairly static benchmark workload to benefit from having a bgwriter, more so than more unpredictable real world applications. Hmmm, I find that real world applications are quite predictable over time. Certainly you have spikes (good pr, whatever) but in general with a little bit of monitoring it is quite possible to evaluate a generally expected result. I guess my point is, if the patch looks good and does not appear to hurt anything, why not apply it? At least that way we can start to review the progress of the feature itself as it starts to see use. If it doesn't appear to have any positive effect, why would we apply it? We don't know if it has a positive effect. My understanding is that your testing shows that it does not appear to have a negative effect. Those are different things don't you think? If we apply the patch, how would you monitor it's effectiveness in a live database? There's nothing to compare against. Well that's valid. A production database you really don't want to be fiddling. *shrug* I hate to see potential lost, but if it isn't a good fit Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGyf6oATb/zqfZUUQRAjLrAJ9lknJRIoAe7EsFWlD3PQeXPZXjMgCdE2ze bGNC5JpSE2DMQuKWrOf9fqI= =rL19 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Status of 8.3 patches
Joshua D. Drake wrote: I guess my point is, if the patch looks good and does not appear to hurt anything, why not apply it? At least that way we can start to review the progress of the feature itself as it starts to see use. I don't think that's a very good criterion. We need to have good evidence that the change has positive benefit. We don't want to be doing speculative changes. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 8.3 beta testing suggestions welcome
I've been lobbying management here for us to allocate some resources to testing 8.3 once it hits beta. If it is approved, it might happen on a time frame too short to get much feedback before the tests, so I'm throwing the question out here now: what would people like us to bang on? The box most likely to be used for the testing is a bit old, but still, it is SMP and we would be throwing real-world traffic at it, so it should be of some value. It has 4 2 GHz Xeon MP CPUs, 6 GB RAM, and a RAID controller with 256 MB battery-backed RAM cache. The 230 GB database would be sitting on a 407 GB RAID 5 array. In addition to the PostgreSQL instance there would be two Java middle tiers running on the box. One middle tier is for modifying data based on transactions received from 72 source databases; this load is about 1 million database transactions on a typical work day, with an average of maybe 20 INSERT, UPDATE, and DELETE statements per transaction. (We don't typically have many deletes.) The other middle tier uses a login which only has SELECT rights to support our web site. We have about 2 million web hits per day generating about 10 million database transactions. We can play the actual HTTP requests from our log through a bank of renderers to get a real mix of queries from production. We're particularly interested in seeing what configuration changes we may have to make to achieve optimal performance with the checkpoints and background writer in the new release. When we first went to PostgreSQL our biggest problem was that dirty buffers would accumulate in shared memory until a checkpoint, and then overrun the controllers cache. This would cause disk reads to queue up behind the writes, and queries which normally ran in a millisecond or two were timing out at our renderers' 20 second limit. The problem went away completely when we used a very aggressive background writer configuration, to put the dirty pages in front of the OS file system right away, so that its algorithms and the controller cache could deal with things before they got out of hand. We could run some tests with just the read-only web load, if that is useful, or push the update load alone. We could pace input. My guess is that the most useful tests would involve letting both run as fast as the machine can handle it with various configurations and see what throughput and timeout counts we get. Any thoughts or suggestions welcome, particularly about what configurations to try. -Kevin ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Status of 8.3 patches
Joshua D. Drake wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: Heikki Linnakangas wrote: Joshua D. Drake wrote: I guess my point is, if the patch looks good and does not appear to hurt anything, why not apply it? At least that way we can start to review the progress of the feature itself as it starts to see use. Yeah, you mean like commit_delay. It really worked great, that reviewing of a feature, you know. It only took 3 years until someone realized that it didn't work as advertised. You can not compare the relevant smallness of use from three years ago to the explosion of use at present. It is certainly unfortunate that commit_delay didn't work as advertised, but then again had we not applied it, we would have never known in the first place and now we have the opportunity to fix or remove it. I don't think we can work like that anymore. For a performance patch, you ought to have at least a one repeatable test case where the patch improves performance. Then you can start talking about tradeoffs with code complexity, possible performance losses in other less common use cases etc, but if you can't demonstrate any meaningful benefit whatsoever in any use case, it's dead on arrival. You can compare other such features that many people don't touch that are starting to show promise over time such as cpu_tuple_cost. That's different. Even though choosing the right plan surely has an effect on performance, cpu_tuple_cost more like a functional feature than a performance feature.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] 8.3 beta testing suggestions welcome
Kevin Grittner wrote: I've been lobbying management here for us to allocate some resources to testing 8.3 once it hits beta. If it is approved, it might happen on a time frame too short to get much feedback before the tests, so I'm throwing the question out here now: what would people like us to bang on? That would be great! We're particularly interested in seeing what configuration changes we may have to make to achieve optimal performance with the checkpoints and background writer in the new release. When we first went to PostgreSQL our biggest problem was that dirty buffers would accumulate in shared memory until a checkpoint, and then overrun the controllers cache. This would cause disk reads to queue up behind the writes, and queries which normally ran in a millisecond or two were timing out at our renderers' 20 second limit. The problem went away completely when we used a very aggressive background writer configuration, to put the dirty pages in front of the OS file system right away, so that its algorithms and the controller cache could deal with things before they got out of hand. Yes, the load distributed checkpoints definitely should help with that. I'd like to see how well it works for you with the default bgwriter settings. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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] more problems with the money type
while playing with the binary transport of the money type we found another bug. The following code segfaults the server on 8.3cvs: select '3'::money * 2; this was an accidental discovery by us but is the second serious bug we found with the money type without looking very hard...probably stemming from the bump to 64 bit in jan 07 (8.2 does not have this behavior). aside: since the money type was deprecated, why was it bumped to 64 bits? merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 8.3 beta testing suggestions welcome
Kevin Grittner [EMAIL PROTECTED] writes: When we first went to PostgreSQL our biggest problem was that dirty buffers would accumulate in shared memory until a checkpoint, and then overrun the controllers cache. This would cause disk reads to queue up behind the writes, and queries which normally ran in a millisecond or two were timing out at our renderers' 20 second limit. The problem went away completely when we used a very aggressive background writer configuration, to put the dirty pages in front of the OS file system right away, so that its algorithms and the controller cache could deal with things before they got out of hand. Sounds like a tailor-mode use case for precisely what Heikki was complaining about. He couldn't find a case in 8.3 where tuning the bgwriter to be more aggressive helped at all. With the load distributed checkpoints I think the symptoms would be different but the disease may still be there. Since checkpoints will try not to swamp your i/o bandwidth any longer you shouldn't get these terrible spikes. However the theory with bgwriter is that setting it to be very aggressive will reduce the response time even outside the checkpoints by avoiding the need for individual backends to evict dirty pages. So it would be interesting to know with 8.3 whether the average response time even outside of checkpoints is reduced by having a more aggressive bgwriter policy. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] more problems with the money type
On Mon, 20 Aug 2007 17:32:42 -0400 Merlin Moncure [EMAIL PROTECTED] wrote: while playing with the binary transport of the money type we found another bug. The following code segfaults the server on 8.3cvs: select '3'::money * 2; What does SELECT 2 * '3'::money; do? If that works try changing 64 to 32 in the function cash_mul_int4. Let me know and I will commit the fix as soon as I get CVS access again. aside: since the money type was deprecated, why was it bumped to 64 bits? See the archives. -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(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] more problems with the money type
What does SELECT 2 * '3'::money; do?
That works.
try changing 64 to 32 in the function cash_mul_int4
That also worked.
Datum
cash_mul_int4(PG_FUNCTION_ARGS)
{
Cashc = PG_GETARG_CASH(0);
/*int64 i = PG_GETARG_INT64(1);*/
int32 i = PG_GETARG_INT32(1);
Cashresult;
result = c * i;
PG_RETURN_CASH(result);
}
See submitted patch that fixes cash_send and cash_recv as well.
Patch: http://archives.postgresql.org/pgsql-patches/2007-08/msg00117.php
Andrew
D'Arcy J.M. Cain wrote:
On Mon, 20 Aug 2007 17:32:42 -0400
Merlin Moncure [EMAIL PROTECTED] wrote:
while playing with the binary transport of the money type we found
another bug. The following code segfaults the server on 8.3cvs:
select '3'::money * 2;
What does SELECT 2 * '3'::money; do? If that works try changing 64
to 32 in the function cash_mul_int4. Let me know and I will commit
the fix as soon as I get CVS access again.
aside: since the money type was deprecated, why was it bumped to 64 bits?
See the archives.
---(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] more problems with the money type
Division segfaults server as well - SELECT '3'::money / 2 - for the same reason
multiplication did.
/* cash_div_int4()
* Divide cash by 4-byte integer.
*
*/
Datum
cash_div_int4(PG_FUNCTION_ARGS)
{
Cashc = PG_GETARG_CASH(0);
int64 i = PG_GETARG_INT64(1);
Cashresult;
if (i == 0)
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg(division by zero)));
result = rint(c / i);
PG_RETURN_CASH(result);
}
Should be int32 i = PG_GETARG_INT32(1); just like cash_mul_int4().
Andrew
Andrew Chernow wrote:
What does SELECT 2 * '3'::money; do?
That works.
try changing 64 to 32 in the function cash_mul_int4
That also worked.
Datum
cash_mul_int4(PG_FUNCTION_ARGS)
{
Cashc = PG_GETARG_CASH(0);
/*int64i = PG_GETARG_INT64(1);*/
int32 i = PG_GETARG_INT32(1);
Cashresult;
result = c * i;
PG_RETURN_CASH(result);
}
See submitted patch that fixes cash_send and cash_recv as well.
Patch: http://archives.postgresql.org/pgsql-patches/2007-08/msg00117.php
Andrew
D'Arcy J.M. Cain wrote:
On Mon, 20 Aug 2007 17:32:42 -0400
Merlin Moncure [EMAIL PROTECTED] wrote:
while playing with the binary transport of the money type we found
another bug. The following code segfaults the server on 8.3cvs:
select '3'::money * 2;
What does SELECT 2 * '3'::money; do? If that works try changing 64
to 32 in the function cash_mul_int4. Let me know and I will commit
the fix as soon as I get CVS access again.
aside: since the money type was deprecated, why was it bumped to 64
bits?
See the archives.
---(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
---(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] Developer's profile
Hi developers, I'm about to send mails to some of PostgreSQL developers to know their profiles. This work is part of JPUG(Japan PostgreSQL Users Group) activities. I believe the result will be used by JPUG for making a future plan to encourage PostgreSQL developments. Please take a time to answer questions in the mail. Questionnaires in the mail will include: - What kind of projects are you woking on? - What are your major contributions to PostgreSQL development? - How long have you been involved in PostgreSQL development? - How much time do you spend for development in a day? - Are you doing the development in working time or private time? - Do your company encourage PostgreSQL development? - What will help you to do more PostgreSQL development? Each survey result will not be put in public unless an explicit permission is given by the developer. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] tsearch2 patch status report
I've applied version 0.58 of the patch with a lot of further editorializing. I feel fairly confident now in the code that interfaces between tsearch and the rest of the system, but a lot of the lowest-level guts of tsearch (mainly in src/backend/tsearch/*.c and src/backend/utils/adt/ts*.c) left my eyes glazing over. Perhaps someone else can make an extra review pass over that stuff. I am quite confident that this commit broke the MSVC build, which seems to need to know individually about each shared library ... Magnus, can you do something about that? We'll see what other portability problems emerge from the buildfarm. The main thing that is lacking at the moment is documentation. The stuff Bruce has been working on will be good introductory material, but we've got basically zip in reference material. I'll do some work on that over the next couple of days, but there's probably room for more hands. Also, we need to decide what to do with contrib/tsearch2, which is currently DOA because of conflicts with the new core code. We could either rip it out entirely, or try to convert it into a compatibility package. In view of the renamings of functions we agreed to do, I think there is some scope for a compatibility package, but I have no time to work on that. This is, by a wide margin, the largest single patch ever to hit the Postgres CVS tree. Congratulations to Oleg and Teodor on seeing it through! regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Status of 8.3 patches
Heikki Linnakangas [EMAIL PROTECTED] wrote: Bruce Momjian wrote: o Automatic adjustment of bgwriter_lru_maxpages We show this as waiting for performance results. I am thinking we should hold this for 8.4. Agreed. I spent close to a week trying different benchmarks and configurations and simple test cases on a test server and my laptop, and couldn't demonstrate bgwriter making a positive impact in any configuration I tried. The theory behind the patch is sound, but it looks like a lot more testing and analysis is needed. Agreed, too. However, I don't think it is a performance feature practically; it is just for an advertisement: We will be freed from the tuning of bgwriter in 8.3! Does anyone have a way to measure the performance difference by bgwriter_lru_xxx ? I have no performance results not only of the patch but also of those parameters. I'd like to use those test cases to compare manual and automatic tunings of lru parameters for 8.4. o Error correction for n_dead_tuples This shows as waiting on another patch. Again, I am thinking to keep it for 8.4. It was waiting on the vacuum oldestxmin patch, which didn't make it to 8.3. I don't care for the patch myself, but it was submitted well before feature freeze and deserves a review. It looks good to me at first glance. I think there is no stopper to the patch, too. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(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] tsearch2 patch status report
Tom Lane wrote: I am quite confident that this commit broke the MSVC build, which seems to need to know individually about each shared library ... Magnus, can you do something about that? We'll see what other portability problems emerge from the buildfarm. You broke my shiny new MinGW and Cygwin buildfarm members too :-) (MSVC is on the way - I'll try to have it up in a few days). cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] tsearch2 patch status report
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: Also, we need to decide what to do with contrib/tsearch2, which is currently DOA because of conflicts with the new core code. We could either rip it out entirely, or try to convert it into a compatibility package. In view of the renamings of functions we agreed to do, I think there is some scope for a compatibility package, but I have no time to work on that. I saw we leave it as a stub with a readme pointing to the docs. Sincerely, Joshua D. Drake This is, by a wide margin, the largest single patch ever to hit the Postgres CVS tree. Congratulations to Oleg and Teodor on seeing it through! regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGyk80ATb/zqfZUUQRAhbQAJwP95u10LTZ/apiUELtT2GthIZHfQCdGxDh JRfdszL69TQOBD/6hlVZLuA= =h9E9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] random crashes on -HEAD for a few days now
Stefan Kaltenbrunner [EMAIL PROTECTED] writes:
(gdb) info locals
fcinfo = {flinfo = 0x84d33028, context = 0x0, resultinfo = 0x0,
isnull = 0 '\0', nargs = 1, arg = {2294763512, 16, 2377208416, 1,
...
(gdb) x/16x 0x88c75000 - 8
0x88c74ff8: 0x0020 0x Cannot access memory at
address 0x88c75000
is that what you are interested in ?
Yup, that seems pretty conclusive. Patch committed --- thanks for
tracking it down!
regards, tom lane
---(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] tsearch2 patch status report
Andrew Dunstan [EMAIL PROTECTED] writes: You broke my shiny new MinGW and Cygwin buildfarm members too :-) Yeah, I was just looking at that. I seem to recall that the fu01.o(.idata$3+0xc): undefined reference to `libpostgres_a_iname' bleat is a symptom of a reference to a variable that isn't marked DLLIMPORT ... but CurrentMemoryContext certainly is, so there's not anything here sufficient to fix it. I trust someone with access to a Windows build environment will dig into that. regards, tom lane ---(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] Function quote_literal broken in CATALOG_VERSION_NO 200707251
Jignesh K. Shah [EMAIL PROTECTED] writes: # select quote_literal(1); 2007-08-20 18:50:17 PDT ERROR: function quote_literal(integer) does not exist at character 8 quote_literal(integer) never existed in any previous release, either. What you are seeing is a side effect of the removal of implicit casts to text. IMHO rejecting this query is a good thing. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] more problems with the money type
On Mon, 20 Aug 2007 20:00:47 -0400 Andrew Chernow [EMAIL PROTECTED] wrote: What does SELECT 2 * '3'::money; do? That works. try changing 64 to 32 in the function cash_mul_int4 That also worked. See submitted patch that fixes cash_send and cash_recv as well. Patch: http://archives.postgresql.org/pgsql-patches/2007-08/msg00117.php I am still waiting for Magnus to restore my CVS access following the move. Can someone else commit these fixes? -- D'Arcy J.M. Cain [EMAIL PROTECTED] | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem with locks
Gregory Stark [EMAIL PROTECTED] writes: I'm debugging this problem with stalled transactions waiting on locks which have already been granted and I'm puzzled by something. What is this PGPROC entry from? I seem to recall that there's a dummy sentinel header in proc lists ... is that what you're looking at? 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] more problems with the money type
Andrew Chernow [EMAIL PROTECTED] writes: Division segfaults server as well - SELECT '3'::money / 2 - for the same reason multiplication did. Yup. A quick scan doesn't show any other mistaken int32-int64 replacements in the file, but maybe someone wants to look closer? Patch applied. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch2 patch status report
Tom Lane wrote: The main thing that is lacking at the moment is documentation. The stuff Bruce has been working on will be good introductory material, but we've got basically zip in reference material. I'll do some work on that over the next couple of days, but there's probably room for more hands. Oleg and Teodor did provide reference documentation. You can see the SGML here: http://momjian.us/expire/textsearch/SGML/ref/ The SQL commands were in a state of flux so I haven't worked on them yet. I can start now. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Status of 8.3 patches
Heikki Linnakangas [EMAIL PROTECTED] writes: Bruce Momjian wrote: o Error correction for n_dead_tuples This shows as waiting on another patch. Again, I am thinking to keep it for 8.4. It was waiting on the vacuum oldestxmin patch, which didn't make it to 8.3. I don't care for the patch myself, but it was submitted well before feature freeze and deserves a review. It looks good to me at first glance. This patch was originally submitted before we realized that pg_stats failed to distinguish the effects of committed vs rolled-back transactions (which was fixed about three months ago); and we also recently fixed several other bugs such as losing stats data for shared catalogs. So there's a significant probability that the errors it was trying to compensate for are already fixed. Also, I'm still quite unhappy that the patch converts the tracking of n_dead_tuples into a dead-reckoning system in which incremental changes are continually applied without any feedback that'd prevent the value from diverging arbitrarily far from reality. Murphy's law says that the value *will* diverge, if you don't have any negative feedback in the loop to force it to track reality. There may be something to be done here, but there's not any evidence at hand that CVS HEAD actually suffers from a problem in tracking n_dead_tuples, and even if it does I do not think that this particular patch is a good fix. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch2 patch status report
Bruce Momjian [EMAIL PROTECTED] writes: Oleg and Teodor did provide reference documentation. You can see the SGML here: http://momjian.us/expire/textsearch/SGML/ref/ The SQL commands were in a state of flux so I haven't worked on them yet. I can start now. OK. I whacked around the command syntax a bit in order to cut down the number of keywords the grammar needed to know about. (Every new keyword creates a distributed cost in the size and speed of the parser, so we shouldn't create 'em when we don't have to.) So I guess I'm on the hook to get the command syntax reference pages done while the reality is fresh in my mind. Will get on it tomorrow. The other areas that need work include datatype and function reference documentation --- how do you want to attack that? Should we create new sect1 sections in those chapters? 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] Status of 8.3 patches
On Tue, 21 Aug 2007, ITAGAKI Takahiro wrote: Does anyone have a way to measure the performance difference by bgwriter_lru_xxx ? I have no performance results not only of the patch but also of those parameters. I'd like to use those test cases to compare manual and automatic tunings of lru parameters for 8.4. The version of this patch I submitted at http://archives.postgresql.org/pgsql-patches/2007-05/msg00142.php puts statistics into the pg_stat_bgwriter structure so you can compare how much work is being done by the background writer vs. the backends. Even if there is no explicit change in the BW behavior, I would very much like to see that part get committed so people can actually tune more easily by hand using the stock PG in 8.3. Heikki didn't really like the way I passed that data around internally, but I never got a suggestion for doing it a better way I thought was an improvement. Heikki's Bgwriter strategies thread used that to compare the various approaches, which I did quite a bit of as well but didn't bother publishing the results as they weren't any more interesting than his: http://archives.postgresql.org/pgsql-hackers/2007-07/msg00144.php I have a set of scripts to automate pgbench to collect this data automatically and compare various settings. Now that I see all the big patches are settling down, I'll collect all that up and pass it along. I've been mulling over options here for a month now, and I'm not done with this patch yet; I'll take Bruce's message as a call to urgent action to finish and submit my final results ASAP. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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] Status of 8.3 patches
Tom Lane [EMAIL PROTECTED] wrote: o Error correction for n_dead_tuples Also, I'm still quite unhappy that the patch converts the tracking of n_dead_tuples into a dead-reckoning system in which incremental changes are continually applied without any feedback that'd prevent the value from diverging arbitrarily far from reality. Murphy's law says that the value *will* diverge, if you don't have any negative feedback in the loop to force it to track reality. There is *no feedback loop* in the patch. It will clear the stats at the beginning of vacuum, and leave n_dead_tuples collected during the vacuum. Even if some errors are left after the vacuum, they will be cleared at the next vacuum. Errors should not be accumulated through repeated vacuums. There may be something to be done here, but there's not any evidence at hand that CVS HEAD actually suffers from a problem in tracking n_dead_tuples, and even if it does I do not think that this particular patch is a good fix. The problem is in the cost-based delayed vacuum. We turned cost-delay on as default and will encourage to use autovacuum at 8.3. Dead tuple ratio is not predictable from autovacuum_vacuum_scale_factor in the current behavior; It might make DBA feel unhappy. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
