[HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed from OK to ContribCheck failure
Something broke snake again :-(. Looks like tsearch2 through the haze of my Lemsip... I hate winter :-( /D -Original Message- From: PG Build Farm [mailto:[EMAIL PROTECTED] Sent: 10 February 2006 02:20 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: PGBuildfarm member snake Branch HEAD Status changed from OK to ContribCheck failure The PGBuildfarm member snake had the following event on branch HEAD: Status changed from OK to ContribCheck failure The snapshot timestamp for the build that triggered this notification is: 2006-02-10 02:00:00 The specs of this machine are: OS: Windows / Server 2003 SP1 Arch: i686 Comp: gcc / 3.4.2 For more information, see http://www.pgbuildfarm.org/cgi-bin/show_history.pl?nm=snakebr=HEAD ---(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] FW: PGBuildfarm member snake Branch HEAD Status changed from OK to ContribCheck failure
On Feb 10, 2006, at 17:24 , Dave Page wrote: Something broke snake again :-(. Looks like tsearch2 through the haze of my Lemsip... I hate winter :-( Hope you feel better soon! I've been taking 1500 to 2000mg of vitamin C daily to try to stay healthy. As for snake, I can't offer much. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Upcoming re-releases
On 2/9/06, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Maybe this should be a configure flag, just like the port number is. It is ... that isn't the issue, the problem is exactly that Debian chooses to exercise the option to make their installations different from everyone else's. It is exatly distributor's job to give consistent system. I would not like to use a distro that just does './configure;make;make install' without any overview. Especially considering that upstream defaults are bad. OTOH as upstream job is _not_ to care about consistent system - as it is not possible - then for upstream the backwards compatibility is the most important thing. It is likely that PostgreSQL upstream can move the default only when most distros have already changed to sane setting. Oh, and I personally like that self-compiled PostgreSQL defaults to other locations than system one. Lessens danger of using experimental stuff on useful data. -- marko ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed
*** *** 2463,2469 a href=http://www.google.com/foo.bar.html; target=_blankYES nbsp;/a ff-bg script ! document.write(15); /script /body /html --- 2463,2469 a href=http://www.google.com/foo.bar.html; target=_blankYES nbsp;/a ff-bg script ! \x09document.write(15); /script /body /html \x09 is a '\t'. Is it now prohibited(non-printable) symbol? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Compiling UDF DLL under Win32
hm, I'm still stuck: can't compile extension with mingw compiler. I use Dev-CPP IDE (4.9.9.2) with MingW 3.7. Has it been tested to compile extensions under XP with PostgresQL 8.1.2 ? Al I get are compiler errors: gcc.exe -c fd.c -o fd.o -IC:/Dev-Cpp/include -ID:/.Work/postgresql/postgresql-8.1.2/src/include -ID:/.Work/postgresql/postgresql-8.1.2/src/include/port/win32 -ID:/.Work/postgresql/postgresql-8.1.2/src/include -ID:/.Work/postgresql/postgresql-8.1.2/src/include/port/win32 -DBUILDING_DLL=1 -DWIN32 -DWIN32_CLIENT_ONLY=0 D:/.Work/postgresql/postgresql-8.1.2/src/include/port/win32.h:181: error: redefinition of typedef 'pid_t' C:/Dev-Cpp/include/sys/types.h:75: error: previous declaration of 'pid_t' was here In file included from D:/.Work/postgresql/postgresql-8.1.2/src/include/postgres.h:48, from fd.c:8: D:/.Work/postgresql/postgresql-8.1.2/src/include/c.h:328: error: redefinition of typedef 'sig_atomic_t' C:/Dev-Cpp/include/signal.h:46: error: previous declaration of 'sig_atomic_t' was here In file included from D:/.Work/postgresql/postgresql-8.1.2/src/include/access/tupdesc.h:18, from D:/.Work/postgresql/postgresql-8.1.2/src/include/utils/rel.h:17, from D:/.Work/postgresql/postgresql-8.1.2/src/include/storage/bufmgr.h:21, from D:/.Work/postgresql/postgresql-8.1.2/src/include/storage/bufpage.h:18, from D:/.Work/postgresql/postgresql-8.1.2/src/include/access/htup.h:17, from D:/.Work/postgresql/postgresql-8.1.2/src/include/utils/tqual.h:18, from D:/.Work/postgresql/postgresql-8.1.2/src/include/access/relscan.h:18, from D:/.Work/postgresql/postgresql-8.1.2/src/include/nodes/execnodes.h:17, from D:/.Work/postgresql/postgresql-8.1.2/src/include/executor/execdesc.h:19, from D:/.Work/postgresql/postgresql-8.1.2/src/include/executor/executor.h:17, from fd.c:10 . . . I was wondering if I can use VC compiler to compile UDF DLL ? If yes then I am wondering why I am getting linking errors: Compiling... fd.c Linking... Creating library Debug/fd.lib and object Debug/fd.exp fd.obj : error LNK2001: unresolved external symbol _CurrentMemoryContext Debug/fd.dll : fatal error LNK1120: 1 unresolved externals It seems that linker is not able to find symbol CurrentMemoryContext. This variable is declared in palloc.h, but I can't find the implementation of this variable in libpostgres.a ? No, you need to use mingw to compile extensions. //Magnus ---(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 ---(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] Scrollable cursors and Sort performance
I'm interested in the behaviour of ExecSort, which *for all queries* prepares the sort result for randomAccess, even when part of a plan that will *never* go backwards/rewind etc. A recent performance test shows this output from mid-way through a heap sort with trace_sort=on (the query itself is not relevant here) LOG: 0: finished writing final run 65 to tape 64: CPU 57.50s/484.51u sec elapsed 597.46 sec LOG: 0: finished merge step: CPU 107.90s/653.53u sec elapsed 941.83 sec Which shows that the *unnecessary* final merge takes 344 secs, adding approximately 60% to the elapsed time of the query and nearly doubling the CPU requirement. [Aside: you'll notice the above test was performed with my recent sort improvement patch applied, but the behaviour of ExecSort is identical in both cases. However in the current cvstip case, you simply don't notice the extra expense of the request for randomAccess because of the additional time taken by the sort] So, why does the planner think random access is required? Well, only for use in queries; CREATE INDEX for example does not force this. To allow support for CURSORs, of course. From the code, we never call ExecSort with a direction other than Forward unless we are issuing a FETCH with a direction other than one identified internally as FETCH_FORWARD. According to the SQL standard, that can only happen when a scrollable cursor has been declared using DECLARE ... SCROLL. The current PostgreSQL manual says the following: FETCH: The cursor should be declared with the SCROLL option if one intends to use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a positive count. For simple queries PostgreSQL will allow backwards fetch from cursors not declared with SCROLL, but this behavior is best not relied on. If the cursor is declared with NO SCROLL, no backward fetches are allowed. DECLARE: The SCROLL option should be specified when defining a cursor that will be used to fetch backwards. This is required by the SQL standard. However, for compatibility with earlier versions, PostgreSQL will allow backward fetches without SCROLL, if the cursor's query plan is simple enough that no extra overhead is needed to support it. However, application developers are advised not to rely on using backward fetches from a cursor that has not been created with SCROLL. If NO SCROLL is specified, then backward fetches are disallowed in any case. The current behaviour is to plan every query as if it would allow backwards scans, then immediately disallow backwards scans *if* it fails the no extra overhead test later on in the Declare Cursor processing. [portalcmds.c:PerformCursorOpen()]. (i.e. you pay, but get no benefit) My suggestion is that the backwards-compatible behaviour of allowing backwards/absolute FETCHes *without* a specific SCROLL command be deprecated in the next release, so that the default is *disallow*. We've warned people and now its time to turn it off by default. (This would be re-enabled using default_cursor_scroll = on). If that is not acceptable, then we should re-evaluate the idea that sorts *always* allow backward scans [execAmi.c:ExecSupportsBackwardScan()], replacing this either with *never* or some kind of query cost test (but that seems much less preferable). Materialize already provides the infrastructure required to do this. This will then allow us to use the firm knowledge that a plan will only ever be scanned in a Forwards direction at plan time. ...and that will allow us to optimize out the rather large step taken during Sort to freeze the result unnecessarily for randomAccess. This will then give a good perfomance gain for larger joins and aggregations, neither of which would ever allow backwards scans using them current method anyway. I intend to add a short patch to pass down the cursor state during planning, so that when it is explicitly specified the sort node is able to recognise this and avoid work. Also, to add a GUC to force the not-explicitly-specified case to be the same as the NO SCROLL case, as the standard requires. Comments? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_hba.conf alternative
I did consider that, but the software we use (which again uses postgresql) atm only supports local connection to the database. I am the database admin, the other admins just manage stuff like user accounts, checking logs, etc... Unfortunately there is no other way to set it up, and like I mentioned government security is not required. I did however statically code the pg_hba.conf file into pg binaries. The only way I found to access the db now would be to replace the binary and possibly sniffing traffic. But we're not worried about that. They not really criminally minded people. thx for everyones help anyway ; korry wrote: Why would you not simply set this up on a seperate machine to which only the trusted admins had access? Most data centers I am familiar with use single purpose machines anyway. If someone is trusted as root on your box they can screw you no matter what you do. Pretending otherwise is just folly. Agreed - that would be a much better (easier and more secure) solution where practical. -- Korry ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Compiling UDF DLL under Win32
Are you using pgxs + gmake? That's the best way to set up a UDF. We recently made changes to ensure that pgxs works on Windows, and several people have used it. This could easily blow up on you: -IC:/Dev-Cpp/include - why do you need to use the IDE's include files? If you really need an IDE or something like it, native Windows XEmacs works reasonably. cheers andrew anonymus.crux wrote: hm, I'm still stuck: can't compile extension with mingw compiler. I use Dev-CPP IDE (4.9.9.2) with MingW 3.7. Has it been tested to compile extensions under XP with PostgresQL 8.1.2 ? Al I get are compiler errors: gcc.exe -c fd.c -o fd.o -IC:/Dev-Cpp/include -ID:/.Work/postgresql/postgresql-8.1.2/src/include -ID:/.Work/postgresql/postgresql-8.1.2/src/include/port/win32 -ID:/.Work/postgresql/postgresql-8.1.2/src/include -ID:/.Work/postgresql/postgresql-8.1.2/src/include/port/win32 -DBUILDING_DLL=1 -DWIN32 -DWIN32_CLIENT_ONLY=0 D:/.Work/postgresql/postgresql-8.1.2/src/include/port/win32.h:181: error: redefinition of typedef 'pid_t' C:/Dev-Cpp/include/sys/types.h:75: error: previous declaration of 'pid_t' was here In file included from D:/.Work/postgresql/postgresql-8.1.2/src/include/postgres.h:48, from fd.c:8: D:/.Work/postgresql/postgresql-8.1.2/src/include/c.h:328: error: redefinition of typedef 'sig_atomic_t' C:/Dev-Cpp/include/signal.h:46: error: previous declaration of 'sig_atomic_t' was here In file included from D:/.Work/postgresql/postgresql-8.1.2/src/include/access/tupdesc.h:18, from D:/.Work/postgresql/postgresql-8.1.2/src/include/utils/rel.h:17, from D:/.Work/postgresql/postgresql-8.1.2/src/include/storage/bufmgr.h:21, from D:/.Work/postgresql/postgresql-8.1.2/src/include/storage/bufpage.h:18, from D:/.Work/postgresql/postgresql-8.1.2/src/include/access/htup.h:17, from D:/.Work/postgresql/postgresql-8.1.2/src/include/utils/tqual.h:18, from D:/.Work/postgresql/postgresql-8.1.2/src/include/access/relscan.h:18, from D:/.Work/postgresql/postgresql-8.1.2/src/include/nodes/execnodes.h:17, from D:/.Work/postgresql/postgresql-8.1.2/src/include/executor/execdesc.h:19, from D:/.Work/postgresql/postgresql-8.1.2/src/include/executor/executor.h:17, from fd.c:10 ---(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] PostgreSQL 8.0.6 crash
Rick Gigger [EMAIL PROTECTED] writes: However if hashagg truly does not obey the limit that is supposed to be imposed by work_mem then it really ought to be documented. Is there a misunderstanding here and it really does obey it? Or is hashagg an exception but the other work_mem associated operations work fine? Or is it possible for them all to go out of bounds? hashagg is the exception. It should be fixed, not documented, but no one's got round to that. Well, it is clearly a pathalogical condition. Fixed? Sure, but someone should document it so that others don't stumble across it. One point to consider is that if the planner's estimate is as far off as exhibited in the OP's example, a hashagg that does spill to disk is likely to take so long that he'll be back here complaining that the query never terminates ;-). That's not fair, now is it? This isn't about the OP (me), it is about PostgreSQL behaving badly. In most practical situations, I think exceeding work_mem is really the best solution, as long as it's not by more than 10x or 100x. It's when the estimate is off by many orders of magnitude that you've got a problem. Running out of memory is not necessarily the worst response ... as long as the system doesn't kill the process in response to that. I don't agree with you here. Many PostgreSQL installations use PostgreSQL as part of a larger whole. Adjusting work_mem should give the admin some control over the memory footprint of the system. It is documented as the limit a specific function path will use before spilling to disk. I set up a lot of systems and I write a lot of software that uses PostgreSQL. Periodically I run across features/problems/limitations of PostgreSQL and post them. This was/is an example of where the behavior of PostgreSQL is clearly unacceptable. OK, yes, this problem goes away with an ANALYZE, but it isn't clear how anyone could have known this, and unexpected behavior is bad in any product. In your statement, he'll be back here complaining that the query never terminates, that's not true. A long query typically gets examined with explain (or in Oracle, explain plan) and evaluated from there. When the process exhibits runaway memory use, that's a problem. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] how is that possible
After a typo, I've just noticed the following : ~ 14:58:33: createdb test CREATE DATABASE ~ 14:58:42: psql test Welcome to psql 8.1.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# create table t1 (i int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t1_pkey for table t1 CREATE TABLE test=# create table t2 (i int references t2 1 on deley te cascade on update cascade ); CREATE TABLE test=# insert into t2 values (default); INSERT 0 1 test=# select * from t1; i --- (0 rows) test=# select * from t2; i --- (1 row) test=# \q should'nt the insert fail or have I mised something? postgresql 812 unixware 7.1.4 -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] how is that possible
ohp@pyrenet.fr wrote: test=# create table t1 (i int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t1_pkey for table t1 CREATE TABLE test=# create table t2 (i int references t2 1 on deley te cascade on update cascade ); The t2.i column is nullable. There is no bug here. Declare NOT NULL if that's what you want. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] how is that possible
On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: After a typo, I've just noticed the following : ~ 14:58:33: createdb test CREATE DATABASE ~ 14:58:42: psql test Welcome to psql 8.1.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# create table t1 (i int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t1_pkey for table t1 CREATE TABLE test=# create table t2 (i int references t2 1 on deley te cascade on update cascade ); CREATE TABLE test=# insert into t2 values (default); INSERT 0 1 test=# select * from t1; i --- (0 rows) test=# select * from t2; i --- (1 row) test=# \q should'nt the insert fail or have I mised something? Why do you think it should have failed? It looks okay to me. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed from OK to ContribCheck failure
On 2/10/06, Dave Page dpage@vale-housing.co.uk wrote: Something broke snake again :-(. Looks like tsearch2 through the haze of my Lemsip... I hate winter :-( AFAIR the reason for different length of psql's '' header lines was database encoding being UNICODE not SQL_ASCII. Can this be the case there? -- marko ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed from OK to ContribCheck failure
On 2/10/06, Marko Kreen [EMAIL PROTECTED] wrote: On 2/10/06, Dave Page dpage@vale-housing.co.uk wrote: Something broke snake again :-(. Looks like tsearch2 through the haze of my Lemsip... I hate winter :-( AFAIR the reason for different length of psql's '' header lines was database encoding being UNICODE not SQL_ASCII. Can this be the case there? Oh, now I see it's everywhere. Probably intentional change then. -- marko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Scrollable cursors and Sort performance
Simon Riggs [EMAIL PROTECTED] writes: Which shows that the *unnecessary* final merge takes 344 secs, adding approximately 60% to the elapsed time of the query and nearly doubling the CPU requirement. The merge step would certainly have to happen anyway, so this claim is not justified. You have not presented any evidence about how much of the reported time is actually I/O related. 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] FW: PGBuildfarm member snake Branch HEAD Status changed from OK to ContribCheck failure
-Original Message- From: Marko Kreen [mailto:[EMAIL PROTECTED] Sent: 10 February 2006 15:07 To: Dave Page Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed from OK to ContribCheck failure On 2/10/06, Dave Page dpage@vale-housing.co.uk wrote: Something broke snake again :-(. Looks like tsearch2 through the haze of my Lemsip... I hate winter :-( AFAIR the reason for different length of psql's '' header lines was database encoding being UNICODE not SQL_ASCII. Can this be the case there? Oh, didn't spot those (barely functioning at all today though!). No-one has even logged into Snake for a week or more though, so I would still suspect a source change as the culprit. Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] how is that possible
My understanding is that null or not, their should have been a foreign key violation. Maybe I misunderstood. On Fri, 10 Feb 2006, Stephan Szabo wrote: Date: Fri, 10 Feb 2006 06:48:02 -0800 (PST) From: Stephan Szabo [EMAIL PROTECTED] To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] how is that possible On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: After a typo, I've just noticed the following : ~ 14:58:33: createdb test CREATE DATABASE ~ 14:58:42: psql test Welcome to psql 8.1.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# create table t1 (i int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t1_pkey for table t1 CREATE TABLE test=# create table t2 (i int references t2 1 on deley te cascade on update cascade ); CREATE TABLE test=# insert into t2 values (default); INSERT 0 1 test=# select * from t1; i --- (0 rows) test=# select * from t2; i --- (1 row) test=# \q should'nt the insert fail or have I mised something? Why do you think it should have failed? It looks okay to me. -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Backslashes in string literals
On Thu, Feb 9, 2006 at 10:31 pm, in message [EMAIL PROTECTED], Bruce Momjian pgman@candle.pha.pa.us wrote: OK, I got it working. The fix is to add GUC_REPORT to guc.c for standard_conforming_strings. See the same flag on session_authorization. That will cause libpq to see any changes made to that variable. Sorry I didn't know that detail before. Fantastic! I added that flag on my end, and everything I've tried is working perfectly, except: I found that I didn't get my expected file 100% right with my hand-crafted attempt. You're probably already on that, but just in case it might save you a few minutes -- attached is a good patch for the expected file to go with the new regression test script for strings. -Kevin strings.out.patch Description: Binary data ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed
The failure, I think, it because of the newline patch we got for psql yesterday. I am seeking a diff from pgcrypto to fix it. My openssl is too old. --- Dave Page wrote: -Original Message- From: Marko Kreen [mailto:[EMAIL PROTECTED] Sent: 10 February 2006 15:07 To: Dave Page Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] FW: PGBuildfarm member snake Branch HEAD Status changed from OK to ContribCheck failure On 2/10/06, Dave Page dpage@vale-housing.co.uk wrote: Something broke snake again :-(. Looks like tsearch2 through the haze of my Lemsip... I hate winter :-( AFAIR the reason for different length of psql's '' header lines was database encoding being UNICODE not SQL_ASCII. Can this be the case there? Oh, didn't spot those (barely functioning at all today though!). No-one has even logged into Snake for a week or more though, so I would still suspect a source change as the culprit. Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PostgreSQL 8.0.6 crash
On Thu, Feb 09, 2006 at 03:13:22PM -0500, Greg Stark wrote: Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: Greg Stark [EMAIL PROTECTED] writes: It doesn't seem like a bad idea to have a max_memory parameter that if a backend ever exceeded it would immediately abort the current transaction. See ulimit (or local equivalent). As much as setting ulimit in shell scripts is fun, I have to admit that I really don't see it happening very much. For one thing it requires admin access to the startup scripts to arrange this. And it's always cluster-wide. Having a GUC parameter would mean it could be set per-session. Even if the GUC parameter were just implemented by calling setrlimit it might be useful. Trying to tune work_mem is extremely difficult in PostgreSQL, because you are constantly running the risk of sending the server into a swap-storm. Having a set-able per-backend memory limit would allow a lot more flexability in setting work_mem, because you could now ensure that you wouldn't push the server into serious swapping. Even better would be a means to set a cluster-wide memory limit, but of course that's substantially more work. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 8.0.6 crash
On Thu, Feb 09, 2006 at 05:04:38PM -0500, Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: When people talk about disabling the OOM killer, it doesn't stop the SIGKILL behaviour, Yes it does, because the situation will never arise. it just causes the kernel to return -ENOMEM for malloc() much much earlier... (ie when you still actually have memory available). Given the current price of disk, there is no sane reason not to have enough swap space configured to make this not-a-problem. The OOM kill mechanism was a reasonable solution for running systems that were not expected to be too reliable anyway on small hardware, but if you're trying to run a 24/7 server you're simply incompetent if you don't disable it. BTW, I was shocked when I found out that FreeBSD actually has an OOM killer itself. Yet I've never heard of anyone having problems with it. Granted, the FreeBSD OOM could be better designed to pick the right process to kill, but I'd bet that the real reason you never hear about it is because FreeBSD admins are clued enough to a) setup a reasonable amount of swap and b) do a better job of monitoring memory usage so that you don't start swapping in the first place. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] PostgreSQL 8.0.6 crash
On Fri, Feb 10, 2006 at 09:57:12AM -0500, Mark Woodward wrote: In most practical situations, I think exceeding work_mem is really the best solution, as long as it's not by more than 10x or 100x. It's when the estimate is off by many orders of magnitude that you've got a problem. Running out of memory is not necessarily the worst response ... as long as the system doesn't kill the process in response to that. I don't agree with you here. Many PostgreSQL installations use PostgreSQL as part of a larger whole. Adjusting work_mem should give the admin some control over the memory footprint of the system. It is documented as the limit a specific function path will use before spilling to disk. And even when PostgreSQL has the server all to itself, having a hashagg spill to disk is *way* better than pushing the machine into a swap storm. At least if you spill the hashagg you only have one backend running at a snail's pace; a swap storm means next to nothing gets done. This was/is an example of where the behavior of PostgreSQL is clearly unacceptable. OK, yes, this problem goes away with an ANALYZE, but it isn't clear how anyone could have known this, and unexpected behavior is bad in any product. Care to submit a documentation patch before releases are bundled (I think on Sunday?) At least then people would be aware that work_mem is just a suggestion to hash_aggs. I'd do a patch myself but I doubt I'll have time before the release. :( -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Scrollable cursors and Sort performance
On Fri, 2006-02-10 at 10:13 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Which shows that the *unnecessary* final merge takes 344 secs, adding approximately 60% to the elapsed time of the query and nearly doubling the CPU requirement. The merge step would certainly have to happen anyway, so this claim is not justified. You have not presented any evidence about how much of the reported time is actually I/O related. You are right that the last read off disk and merge steps would still be required, if the full result set were to be read. However, I am pointing out a task in addition to that. Reading a large file from disk and then writing it back down *when there is no benefit* seems like a task we would want to avoid, whatever the size and however (sequential/random) the I/Os are spent. We need not debate the CPU time differences. The cited test sorted 1561238 data blocks, or 12.7 GB. Which at 120 MB/s would take 100 seconds one-way on a stand-alone system. I think a very large chunk of the elapsed time could reasonably be accounted for from I/O costs, since it would need to read then write all of that data. In the test, the post-sort retrieval of rows took 150 secs, indicating a sequential retrieval rate of 85 MB/sec, so there is no reason to believe that a slow disk over-emphasised performance loss for this case. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Scrollable cursors and Sort performance
On Fri, Feb 10, 2006 at 01:32:44PM +, Simon Riggs wrote: I intend to add a short patch to pass down the cursor state during planning, so that when it is explicitly specified the sort node is able to recognise this and avoid work. Also, to add a GUC to force the not-explicitly-specified case to be the same as the NO SCROLL case, as the standard requires. So is this only an issue if you're using a cursor, or does this affect plain SELECT ... ORDER BY as well? Reason I'm asking is that users should be able to explicitly be able to turn the extra step off somehow. I'm not clear if NO SCROLL is sufficient to do that or not. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Backslashes in string literals
Kevin Grittner wrote: On Thu, Feb 9, 2006 at 10:31 pm, in message [EMAIL PROTECTED], Bruce Momjian pgman@candle.pha.pa.us wrote: OK, I got it working. The fix is to add GUC_REPORT to guc.c for standard_conforming_strings. See the same flag on session_authorization. That will cause libpq to see any changes made to that variable. Sorry I didn't know that detail before. Fantastic! I added that flag on my end, and everything I've tried is working perfectly, except: I found that I didn't get my expected file 100% right with my hand-crafted attempt. You're probably already on that, but just in case it might save you a few minutes -- attached is a good patch for the expected file to go with the new regression test script for strings. Oh, what I normally do is to look at regression.diff, and if that looks OK, I just apply it to the expected file like this: cd expected patch ../regression.diff -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL 8.0.6 crash
On Fri, Feb 10, 2006 at 10:01:18AM -0600, Jim C. Nasby wrote: BTW, I was shocked when I found out that FreeBSD actually has an OOM killer itself. Yet I've never heard of anyone having problems with it. Granted, the FreeBSD OOM could be better designed to pick the right process to kill, but I'd bet that the real reason you never hear about it is because FreeBSD admins are clued enough to a) setup a reasonable amount of swap and b) do a better job of monitoring memory usage so that you don't start swapping in the first place. Hmm, I do wonder what FreeBSDs overcommit policy is. For example on my computer right now the total allocated VM is approximately 3 times the actual memory in the computer and about twice if you include swap. By a strict policy of overcommit my computer wouldn't complete the boot sequence, whereas as currently it runs without using any swap. Disabling overcommit has a serious cost in that most of your VM will never be used. Are people really suggesting that I can't run a few daemons, X and a web-browser on FreeBSD without allocating 3 times my physical memory in swap? However, my real question is: while trying to find info about FreeBSDs overcommit policy, I just get lot of people complaining about freebsd killing random processes. Does anyone know a site that describes how it works? I understand Linux's overcommit policy just fine. Disclaimer: The Linux OOM killer has never killed the wrong process for me, so I don't have any bad experiences with overcommit. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Scrollable cursors and Sort performance
On Fri, 2006-02-10 at 10:22 -0600, Jim C. Nasby wrote: On Fri, Feb 10, 2006 at 01:32:44PM +, Simon Riggs wrote: I intend to add a short patch to pass down the cursor state during planning, so that when it is explicitly specified the sort node is able to recognise this and avoid work. Also, to add a GUC to force the not-explicitly-specified case to be the same as the NO SCROLL case, as the standard requires. So is this only an issue if you're using a cursor, or does this affect plain SELECT ... ORDER BY as well? Reason I'm asking is that users should be able to explicitly be able to turn the extra step off somehow. I'm not clear if NO SCROLL is sufficient to do that or not. It effects all sorts, whether or not they are even cursors. If a cursor is defined NO SCROLL, which is the SQL Standard implicit default, then we are safe to assume there will be no rewinds or backward scans. The PostgreSQL current implicit default is SCROLL, which means that no part of the executor can currently make useful assumptions about scan direction, so this is a wider issue than just sorts. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 8.0.6 crash
On Fri, Feb 10, 2006 at 09:57:12AM -0500, Mark Woodward wrote: In most practical situations, I think exceeding work_mem is really the best solution, as long as it's not by more than 10x or 100x. It's when the estimate is off by many orders of magnitude that you've got a problem. Running out of memory is not necessarily the worst response ... as long as the system doesn't kill the process in response to that. I don't agree with you here. Many PostgreSQL installations use PostgreSQL as part of a larger whole. Adjusting work_mem should give the admin some control over the memory footprint of the system. It is documented as the limit a specific function path will use before spilling to disk. And even when PostgreSQL has the server all to itself, having a hashagg spill to disk is *way* better than pushing the machine into a swap storm. At least if you spill the hashagg you only have one backend running at a snail's pace; a swap storm means next to nothing gets done. This was/is an example of where the behavior of PostgreSQL is clearly unacceptable. OK, yes, this problem goes away with an ANALYZE, but it isn't clear how anyone could have known this, and unexpected behavior is bad in any product. Care to submit a documentation patch before releases are bundled (I think on Sunday?) At least then people would be aware that work_mem is just a suggestion to hash_aggs. I'd do a patch myself but I doubt I'll have time before the release. :( I would be glad too. What's the process? ---(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] Scrollable cursors and Sort performance
Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2006-02-10 at 10:13 -0500, Tom Lane wrote: The merge step would certainly have to happen anyway, so this claim is not justified. You have not presented any evidence about how much of the reported time is actually I/O related. You are right that the last read off disk and merge steps would still be required, if the full result set were to be read. However, I am pointing out a task in addition to that. Reading a large file from disk and then writing it back down *when there is no benefit* seems like a task we would want to avoid, whatever the size and however (sequential/random) the I/Os are spent. We need not debate the CPU time differences. If the cost of avoiding it were zero, then sure. But propagating the needed information down to the sort step is not a zero-effort thing, and therefore I'd like to see an argument for it that's not got obvious logical holes. Your analysis of when randomAccess is required needs work, in any case, since you forgot about ExecReScan. Not to mention mark/restore. I also don't care for the proposal to replace Sort with Sort/Materialize in cases where random access is needed: that will certainly be *slower* than what we do now. When you are talking about penalizing some cases to make other ones faster, you definitely need an argument without holes in it. I suspect that the right thing is not to see this as a planner issue at all, but to try to drive the choice off the context in which the plan gets invoked. Possibly we could pass a need random access boolean down through the ExecInitNode calls (I seem to recall some prior discussion of doing something like that, in the context of telling Materialize that it could be a no-op in some cases). Lastly, there isn't any obvious reason that I can see for having to change the default assumption about cursors. Most queries don't go through cursors. For those that do, we already document that specifying NO SCROLL can be a performance win, so any app that's not saying that when it could has only itself to blame. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] how is that possible
On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: My understanding is that null or not, their should have been a foreign key violation. Not as far as I can tell. MATCH (without partial or full) returns true if any column in the row value constructor is NULL. MATCH FULL returns true if all columns in the row value constructor are NULL and returns false if it's a mix of NULLs and non-NULLs. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Backslashes in string literals
Bruce Momjian pgman@candle.pha.pa.us writes: Oh, what I normally do is to look at regression.diff, and if that looks OK, I just apply it to the expected file like this: cd expected patch ../regression.diff Oh, that explains a few things ... It's much better to just copy the result file over the expected file once you've decided it's OK. The regression.diff file is inexact because of the diff switches that are used. If you need to update expected variants your machine doesn't generate, make a fresh regular diff off the expected and actual, and apply that to the other variants. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL 8.0.6 crash
Martijn van Oosterhout kleptog@svana.org writes: Disclaimer: The Linux OOM killer has never killed the wrong process for me, so I don't have any bad experiences with overcommit. You haven't tried real hard. What I've seen recently when I do something that makes a PG backend go overboard is that the kernel zaps both the misbehaving backend and the bgwriter process. No idea what it's got against the bgwriter, but the behavior's been pretty consistent under recent Fedora 4 kernels ... (This is on a development machine, not a server, so I'm not particularly worried by leaving the default overcommit policy in place. I wouldn't do that on a server --- but it's not worth my time to change it on a devel machine.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Scrollable cursors and Sort performance
On Fri, 2006-02-10 at 11:58 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2006-02-10 at 10:13 -0500, Tom Lane wrote: The merge step would certainly have to happen anyway, so this claim is not justified. You have not presented any evidence about how much of the reported time is actually I/O related. You are right that the last read off disk and merge steps would still be required, if the full result set were to be read. However, I am pointing out a task in addition to that. Reading a large file from disk and then writing it back down *when there is no benefit* seems like a task we would want to avoid, whatever the size and however (sequential/random) the I/Os are spent. We need not debate the CPU time differences. If the cost of avoiding it were zero, then sure. But propagating the needed information down to the sort step is not a zero-effort thing, and therefore I'd like to see an argument for it that's not got obvious logical holes. I also don't care for the proposal to replace Sort with Sort/Materialize in cases where random access is needed: that will certainly be *slower* than what we do now. When you are talking about penalizing some cases to make other ones faster, you definitely need an argument without holes in it. That wasn't the initial proposal... Your analysis of when randomAccess is required needs work, in any case, since you forgot about ExecReScan. Not to mention mark/restore. OK - other thoughts there for later also. I suspect that the right thing is not to see this as a planner issue at all, but to try to drive the choice off the context in which the plan gets invoked. Possibly we could pass a need random access boolean down through the ExecInitNode calls (I seem to recall some prior discussion of doing something like that, in the context of telling Materialize that it could be a no-op in some cases). Yeh, that was me just being a little vague on implementation, but handing off from planner to executor via the Plan node is what I was hacking at now. I'll follow your recommendation and do it for the general case. Propagating it down should allow a few similar optimizations. Any others please shout 'em in everybody. Lastly, there isn't any obvious reason that I can see for having to change the default assumption about cursors. Most queries don't go through cursors. For those that do, we already document that specifying NO SCROLL can be a performance win, so any app that's not saying that when it could has only itself to blame. The obvious reason is why force people to go out of their way for a performance win? This is the same as OIDs, AFAICS. Some people used them in their programs - well fine, they can keep 'em. Most people didn't and don't and will appreciate having their programs speed up. Not everybody gets the chance to change the SQL in an application program, however much they might want to and know they should. Third party software is most software. The only way to please both is to have a GUC, whatever it is set to by default. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] PostgreSQL 8.0.6 crash
Martijn van Oosterhout kleptog@svana.org writes: Disabling overcommit has a serious cost in that most of your VM will never be used. Are people really suggesting that I can't run a few daemons, X and a web-browser on FreeBSD without allocating 3 times my physical memory in swap? There's a possibility you're misreading your process info there. The X server maps large areas of graphics memory in its address space which shows up as virtual memory used in ps on some OSes. I'm not sure if BSD is included there. That's not memory that can ever be swapped out and it doesn't take up any memory from the non-video memory. That said, it's true that some applications allocate much more memory than needed. There's a bit of a feedback loop here. Because application writers know that OSes overcommit they don't worry about avoiding unnecessary allocations. Sun's original Java memory management system just started off with allocating 20MB before it ran a single bytecode for example. That's why merely allocating tons of swap doesn't necessarily protect you. It's still possible for a process (or several processes if you allocate more swap than you have address space) to mmap gigabytes of memory without touching it and then start touching those pages. Hopefully the OOM killer targets the offender but there's no real way for it to guarantee it. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Scrollable cursors and Sort performance
On Fri, Feb 10, 2006 at 04:48:42PM +, Simon Riggs wrote: If a cursor is defined NO SCROLL, which is the SQL Standard implicit default, then we are safe to assume there will be no rewinds or backward scans. The PostgreSQL current implicit default is SCROLL, which means that no part of the executor can currently make useful assumptions about scan direction, so this is a wider issue than just sorts. Umm, the documentation says: PostgreSQL will allow backward fetches without SCROLL, if the cursor's query plan is simple enough that no extra overhead is needed to support it. So if the default is SCROLL someone needs to fix the docs because that's not what it says. It says that *some plans* can be fetched backwards even if you don't say scroll. The documentation clearly says we don't need to support backwards searches without scroll if it causes problems. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Scrollable cursors and Sort performance
Simon Riggs [EMAIL PROTECTED] writes: Not everybody gets the chance to change the SQL in an application program, however much they might want to and know they should. Third party software is most software. Right. You are proposing to *break* some applications in order to make other ones faster. How popular do you think you will be? 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
[HACKERS] Spaces in psql output (Was: FW: PGBuildfarm member snake Branch HEAD Status changed)
Bruce Momjian wrote: The failure, I think, it because of the newline patch we got for psql yesterday. I am seeking a diff from pgcrypto to fix it. My openssl is too old. A side affect of this newline patch is that all fields are now filled with white space up to the displayed column width, even for the last (or only column). I guess this is somehow required for consistent display. Otherwise, it makes copypaste from a psql session more painful, because of all the added white-space. I hope that psql output intended for script output using the available flags (i.e. not the nice display output) is unaffected? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_hba.conf alternative
But why do they need access to the files in the file system? Why not put them on the local box but don't give them permissions to edit the pg_hba file? They should still be able to connect. On Feb 9, 2006, at 5:56 PM, Q Beukes wrote: I did consider that, but the software we use (which again uses postgresql) atm only supports local connection to the database. I am the database admin, the other admins just manage stuff like user accounts, checking logs, etc... Unfortunately there is no other way to set it up, and like I mentioned government security is not required. I did however statically code the pg_hba.conf file into pg binaries. The only way I found to access the db now would be to replace the binary and possibly sniffing traffic. But we're not worried about that. They not really criminally minded people. thx for everyones help anyway ; korry wrote: Why would you not simply set this up on a seperate machine to which only the trusted admins had access? Most data centers I am familiar with use single purpose machines anyway. If someone is trusted as root on your box they can screw you no matter what you do. Pretending otherwise is just folly. Agreed - that would be a much better (easier and more secure) solution where practical. -- Korry ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 8.0.6 crash
Greg Stark [EMAIL PROTECTED] writes: That's why merely allocating tons of swap doesn't necessarily protect you. It's still possible for a process (or several processes if you allocate more swap than you have address space) to mmap gigabytes of memory without touching it and then start touching those pages. So? If the swap exists to back that memory, there's no problem. It might be slow, but it will not fail. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Scrollable cursors and Sort performance
On Fri, 2006-02-10 at 19:14 +0100, Martijn van Oosterhout wrote: On Fri, Feb 10, 2006 at 04:48:42PM +, Simon Riggs wrote: If a cursor is defined NO SCROLL, which is the SQL Standard implicit default, then we are safe to assume there will be no rewinds or backward scans. The PostgreSQL current implicit default is SCROLL, which means that no part of the executor can currently make useful assumptions about scan direction, so this is a wider issue than just sorts. Umm, the documentation says: PostgreSQL will allow backward fetches without SCROLL, if the cursor's query plan is simple enough that no extra overhead is needed to support it. So if the default is SCROLL someone needs to fix the docs because that's not what it says. It says that *some plans* can be fetched backwards even if you don't say scroll. The documentation clearly says we don't need to support backwards searches without scroll if it causes problems. Changing the docs is not the problem here. I don't understand the point you are making and how it effects the issue. The problem is knowing before the sort is executed whether the sort result will ever be used in the future by a backward scan. We can only do this by definition, restricting the future use of a FETCH. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] What do the Windows pg hackers out there like for dev tools?
Subject line says it all. I'm going to be testing changes under both Linux and WinXP, so I'm hoping those of you that do M$ hacking will pass along your list of suggestions and/or favorite (and hated so I know what to avoid) tools. TiA, Ron ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Spaces in psql output (Was: FW: PGBuildfarm member snake Branch HEAD Status changed)
On Fri, Feb 10, 2006 at 08:06:53PM +0100, Michael Paesold wrote: A side affect of this newline patch is that all fields are now filled with white space up to the displayed column width, even for the last (or only column). I guess this is somehow required for consistent display. Otherwise, it makes copypaste from a psql session more painful, because of all the added white-space. I hope that psql output intended for script output using the available flags (i.e. not the nice display output) is unaffected? My intention was to only change formatted output. Unformatted should be unchanged from previous. The extra spaces is an interesting side-effect. In the past it would only have worked for the last column anyway, right? Anyway, it is a fixable issue and I'd consider doing it if people think it's worth it. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] how is that possible
Many thanks for explaining. I learned something today... On Fri, 10 Feb 2006, Stephan Szabo wrote: Date: Fri, 10 Feb 2006 08:59:51 -0800 (PST) From: Stephan Szabo [EMAIL PROTECTED] To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] how is that possible On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: My understanding is that null or not, there should have been a foreign key violation. Not as far as I can tell. MATCH (without partial or full) returns true if any column in the row value constructor is NULL. MATCH FULL returns true if all columns in the row value constructor are NULL and returns false if it's a mix of NULLs and non-NULLs. -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Spaces in psql output (Was: FW: PGBuildfarm member snake Branch HEAD Status changed)
Martijn van Oosterhout wrote: On Fri, Feb 10, 2006 at 08:06:53PM +0100, Michael Paesold wrote: A side affect of this newline patch is that all fields are now filled with white space up to the displayed column width, even for the last (or only column). My intention was to only change formatted output. Unformatted should be unchanged from previous. The extra spaces is an interesting side-effect. In the past it would only have worked for the last column anyway, right? Right, my explanation was not correct. It should have been the last column is now also filled with spaces. Of course all but the last column were always filled with spaces. Anyway, it is a fixable issue and I'd consider doing it if people think it's worth it. I personally don't like the added spaces (feels inefficient), but that is only a matter of taste, so you can rather ignore it. I am not sure about people who perhaps rely on the output format in scripts (even if it's bad to rely on that specific output format, because the output of psql can be changed to be more suitable for scripts). For multi-column output things have not really changed anyway. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Spaces in psql output (Was: FW: PGBuildfarm member snake Branch HEAD Status changed)
Martijn van Oosterhout kleptog@svana.org writes: The extra spaces is an interesting side-effect. In the past it would only have worked for the last column anyway, right? Of course. Anyway, it is a fixable issue and I'd consider doing it if people think it's worth it. I think it would be a good idea to expect this patch to cause zero change in psql output except in the cases where there are actually control characters in the data. Otherwise there are likely to be complaints. (I'm already unhappy at the prospect that this means every single regression test's output has changed, even if diff --ignore-space is hiding them.) I'd settle for stripping trailing blanks on the last line of a multiline field value, if that'd be any easier than stripping them for all lines. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Scrollable cursors and Sort performance
On Fri, 2006-02-10 at 14:04 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Not everybody gets the chance to change the SQL in an application program, however much they might want to and know they should. Third party software is most software. Right. You are proposing to *break* some applications in order to make other ones faster. How popular do you think you will be? How does this differ from OIDs? I'm suggesting that people have a choice. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Backslashes in string literals
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Oh, what I normally do is to look at regression.diff, and if that looks OK, I just apply it to the expected file like this: cd expected patch ../regression.diff Oh, that explains a few things ... It's much better to just copy the result file over the expected file once you've decided it's OK. The regression.diff file is inexact because of the diff switches that are used. I am confused. patch dosen't make an indentical file? Example? If you need to update expected variants your machine doesn't generate, make a fresh regular diff off the expected and actual, and apply that to the other variants. Ah, good point. I often forget about those. -- 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 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] [COMMITTERS] pgsql: Enable pg_ctl to give up admin privileges when starting the
* Tom Lane ([EMAIL PROTECTED]) wrote: Enable pg_ctl to give up admin privileges when starting the server under Windows (if newer than NT4, else works same as before). I don't suppose we could consider doing this for Unix-based systems too? I think it'd certainly be nice. It's also how quite a few other Unix daemons operate. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Backslashes in string literals
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: It's much better to just copy the result file over the expected file once you've decided it's OK. The regression.diff file is inexact because of the diff switches that are used. I am confused. patch dosen't make an indentical file? Example? Not when the diff it's given to work from ignores spaces ... 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] how is that possible
IMHO null values shouldn't verify foreign keys constraints... Gustavo. 2006/2/10, ohp@pyrenet.fr ohp@pyrenet.fr: Many thanks for explaining. I learned something today... On Fri, 10 Feb 2006, Stephan Szabo wrote: Date: Fri, 10 Feb 2006 08:59:51 -0800 (PST) From: Stephan Szabo [EMAIL PROTECTED] To: ohp@pyrenet.fr Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] how is that possible On Fri, 10 Feb 2006 ohp@pyrenet.fr wrote: My understanding is that null or not, there should have been a foreign key violation. Not as far as I can tell. MATCH (without partial or full) returns true if any column in the row value constructor is NULL. MATCH FULL returns true if all columns in the row value constructor are NULL and returns false if it's a mix of NULLs and non-NULLs. -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Backslashes in string literals
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: It's much better to just copy the result file over the expected file once you've decided it's OK. The regression.diff file is inexact because of the diff switches that are used. I am confused. patch dosen't make an indentical file? Example? Not when the diff it's given to work from ignores spaces ... Ah, interesting. I had not realized that. -- 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 6: explain analyze is your friend
Re: [HACKERS] What do the Windows pg hackers out there like for dev tools?
On 2/10/06, Ron [EMAIL PROTECTED] wrote: Subject line says it all. I'm going to be testing changes under both Linux and WinXP, so I'm hoping those of you that do M$ hacking will pass along your list of suggestions and/or favorite (and hated so I know what to avoid) tools. If you mean hacking postgresql source code, you pretty much have to use the built in make/build system...this more or less rules out IDEs and such. I like UltraEdit for a text editor. Another good choice for editor is source insight. Winmerge is a fantastic tool and you may want to check out wincvs/tortoisesvn if you want to do checkouts from the gui. Of course, to make/build postgresql in windows, you can go with cygwin or mingw. cygwin is a bit easier to set up and has a more of a unix flavor but mignw allows you to compile native executables. The upcoming windows vista will most likely be able to compile postgresql without an external build system. Merlin ---(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] What do the Windows pg hackers out there like for dev
Ron wrote: Subject line says it all. I'm going to be testing changes under both Linux and WinXP, so I'm hoping those of you that do M$ hacking will pass along your list of suggestions and/or favorite (and hated so I know what to avoid) tools. Testing only? So you really only need to build and run on Windows... I was doing exactly this about a year ago and used Mingw. The only annoyance was that I could compile everything on Linux in about 3 minutes (P4 2.8Ghz), but had to wait about 60-90 minutes for the same thing on Windows 2003 Server! (also a P4 2.8Ghz...). So I used to build a 'go for coffee' task into the build and test cycle. Cheers Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Getting the length of varlength data using PG_DETOAST_DATUM_SLICE or similar?
Hello, could anyone tell me, for a user contributed variable length data type,
how can you access the length of the data without pulling the entire thing from
disk? Is there a function or macro for this?
As a first cut, I tried using the PG_DETOAST_DATUM_SLICE macro, but to no avail.
grep'ing through the release source for version 8.1.2, I find very little
usage of the PG_GETARG_*_SLICE and PG_DETOAST_DATUM_SLICE macros (and hence
little clue how they are intended to be used.) The only files where I find them
referenced are:
doc/src/sgml/xfunc.sgml
src/backend/utils/adt/varlena.c
src/include/fmgr.h
I am writing a variable length data type and trying to optimize the disk usage
in certain functions. There are cases where the return value of the function
can be determined from the length of the data and a prefix of the data without
fetching the whole data from disk. (The prefix alone is insufficient -- I need
to also know the length for the optimization to work.)
The first field of the data type is the length, as follows:
typedef struct datatype_foo {
int32 length;
char data[];
} datatype_foo;
But when I fetch the function arguments using
datatype_foo * a = (datatype_foo *)
PG_DETOAST_DATUM_SLICE(PG_GETARG_DATUM(0),0,BLCKSZ);
the length field is set to the length of the fetched slice, not the length of
the data as it exists on disk. Is there some other function that gets the length
without pulling more than the first block?
Thanks for any insight,
--Mark
---(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] Getting the length of varlength data using PG_DETOAST_DATUM_SLICE
Have you looked at the 8.1.X buildin function pg_column_size()?
---
Mark Dilger wrote:
Hello, could anyone tell me, for a user contributed variable length data
type,
how can you access the length of the data without pulling the entire thing
from
disk? Is there a function or macro for this?
As a first cut, I tried using the PG_DETOAST_DATUM_SLICE macro, but to no
avail.
grep'ing through the release source for version 8.1.2, I find very little
usage of the PG_GETARG_*_SLICE and PG_DETOAST_DATUM_SLICE macros (and hence
little clue how they are intended to be used.) The only files where I find
them
referenced are:
doc/src/sgml/xfunc.sgml
src/backend/utils/adt/varlena.c
src/include/fmgr.h
I am writing a variable length data type and trying to optimize the disk
usage
in certain functions. There are cases where the return value of the function
can be determined from the length of the data and a prefix of the data
without
fetching the whole data from disk. (The prefix alone is insufficient -- I
need
to also know the length for the optimization to work.)
The first field of the data type is the length, as follows:
typedef struct datatype_foo {
int32 length;
char data[];
} datatype_foo;
But when I fetch the function arguments using
datatype_foo * a = (datatype_foo *)
PG_DETOAST_DATUM_SLICE(PG_GETARG_DATUM(0),0,BLCKSZ);
the length field is set to the length of the fetched slice, not the length of
the data as it exists on disk. Is there some other function that gets the
length
without pulling more than the first block?
Thanks for any insight,
--Mark
---(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
--
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: Don't 'kill -9' the postmaster
Re: [HACKERS] Getting the length of varlength data using
It looks like pg_column_size gives you the actual size on disk, ie after
compression.
What looks interesting for you would be byteaoctetlen or the function it
wraps, toast_raw_datum_size. See src/backend/access/heap/tuptoaster.c.
pg_column_size calls toast_datum_size, while byteaoctetlen/textoctetlen
calls toast_raw_datum_size.
On Sat, 11 Feb 2006, Bruce Momjian wrote:
Have you looked at the 8.1.X buildin function pg_column_size()?
---
Mark Dilger wrote:
Hello, could anyone tell me, for a user contributed variable length data
type,
how can you access the length of the data without pulling the entire thing
from
disk? Is there a function or macro for this?
As a first cut, I tried using the PG_DETOAST_DATUM_SLICE macro, but to no
avail.
grep'ing through the release source for version 8.1.2, I find very little
usage of the PG_GETARG_*_SLICE and PG_DETOAST_DATUM_SLICE macros (and hence
little clue how they are intended to be used.) The only files where I find
them
referenced are:
doc/src/sgml/xfunc.sgml
src/backend/utils/adt/varlena.c
src/include/fmgr.h
I am writing a variable length data type and trying to optimize the disk
usage
in certain functions. There are cases where the return value of the
function
can be determined from the length of the data and a prefix of the data
without
fetching the whole data from disk. (The prefix alone is insufficient -- I
need
to also know the length for the optimization to work.)
The first field of the data type is the length, as follows:
typedef struct datatype_foo {
int32 length;
char data[];
} datatype_foo;
But when I fetch the function arguments using
datatype_foo * a = (datatype_foo *)
PG_DETOAST_DATUM_SLICE(PG_GETARG_DATUM(0),0,BLCKSZ);
the length field is set to the length of the fetched slice, not the length
of
the data as it exists on disk. Is there some other function that gets the
length
without pulling more than the first block?
Thanks for any insight,
--Mark
---(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
--
Contrary to popular belief, penguins are not the salvation of modern
technology. Neither do they throw parties for the urban proletariat.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Getting the length of varlength data using PG_DETOAST_DATUM_SLICE
Bruce Momjian wrote: Have you looked at the 8.1.X buildin function pg_column_size()? Thanks Bruce for the lead. I didn't know what to grep for; this helps. The header comment for that function says Return the size of a datum, possibly compressed I take it the uncompressed length is not available -- that this is as close as I'm going to get. I haven't traced through the function yet; maybe it does what I need. I'll look at this some more now that I have a starting point. Thanks again! mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
