Re: [HACKERS] [PATCHES] default database creation with initdb
Tom Lane wrote: Another point is that Dave added code to pg_dumpall to not dump the postgres database. This seems mistaken to me, so I did not include it in the applied patch: if someone is doing real work in postgres then they'll be pretty annoyed if it's not backed up. But perhaps the question needs debate. Any thoughts? You are correct, in my opinion. If one is allowed to add objects to the postgres database, than it must obviously be backuped. Otherwise this is just another way to shoot yourself in the foot. From an outsiders point of view, the postgres database could just look like roots home directory in /root,... would you exclude that from backups? Best Regards, Michael Paesold ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] default database creation with initdb
On Tuesday 21 June 2005 00:12, Tom Lane wrote:
Dave Page dpage@vale-housing.co.uk writes:
OK, new patch posted to -patches that updates all the utilities as well.
If I read the code correctly, the database name will be hardwired to
postgres regardless of the default super user name correct?
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] [PATCHES] default database creation with initdb
-Original Message- From: Robert Treat [mailto:[EMAIL PROTECTED] Sent: 21 June 2005 08:10 To: Tom Lane Cc: Dave Page; Andrew Dunstan; Andreas Pflug; Magnus Hagander; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] default database creation with initdb On Tuesday 21 June 2005 00:12, Tom Lane wrote: Dave Page dpage@vale-housing.co.uk writes: OK, new patch posted to -patches that updates all the utilities as well. If I read the code correctly, the database name will be hardwired to postgres regardless of the default super user name correct? Yes - that's intentional so that pgAdmin/phpPgAdmin et al. can reasonably expect it to be there. Regards, Dave ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Schedule for 8.1 feature freeze
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: 21 June 2005 05:04 To: PostgreSQL-development Subject: [HACKERS] Schedule for 8.1 feature freeze We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. What about Andreas' instrumentation stuff? This has been going on since before 8.0 and it would good to get it in 8.1 given the amount of extra functionality it allows us to offer users that prefer a GUI interface. I realise there probably won't be time to fix pg_terminate_backend, or convince people that it offers the admin the lesser of two evils (my limited understanding being that there is a chance of it not clearing some locks, vs, having to shut down the whole server to kill a single connection) - can we at least get the other functions applied? Thanks, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Schedule for 8.1 feature freeze
We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. I don't beleive we have :-( The server instrumentation patch is not yet addressed. Last I checked there was the objections to pg_terminate_backend (which are certainly valid,though I and many other still claim this is in daily use at *a lot* of installations), and nothing substantial to the rest of the patch. yet it hasn't even made it to the queue (the patch would still be very useful with just the parts except pg_terminate_backend). I beleive Andreas is working on separating out the pg_terminate_backend part to a separate patch to make it easier for whomever would apply it. There is also the ICU patch. this is *very* much needed for win32, and from how I read it it's also needed for other platforms. Last I heard Palle planned to have it fixed up before feature freeze (I'm not sure exactly what more needed to be fixed). If not then we have to do something else about unicode on win32 (perhaps under the flag of bugfix, but I'm sure it will be more invasive than most would like). But I realy think a cross-platform thing like ICU is much better. And there's a pending patch that redoes signal handling on win32. I haven't had the time to check it through myself, as I've been unexpectedly-out-of-town several times lately, but it was posted some time ago and shoudl probalby be at least considered. (I'm not 100% sure it's a worthwhile simplification myself, but I'll have to look into it muc more careful before I can form an actual opinion on it) Finally a heads-up: I'm looking at an updated patch to remove the Kerberos V4 support. I posted to both hackers and general a month ago asking for people who use it for exactly zero responses. It's not quite done yet, but I plan to have it done before July 1st. But it's definitly not something to hold up a freeze date for if I'm not. Is there an Open Issues list yet, as you usually prepare for releases? If so, can you please put these items up on it? //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] default database creation with initdb
Yes - that's intentional so that pgAdmin/phpPgAdmin et al. can reasonably expect it to be there. Problem is, how the hell do I know it's there before I connect? Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] default database creation with initdb
-Original Message- From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED] Sent: 21 June 2005 08:57 To: Dave Page Cc: Robert Treat; Tom Lane; Andrew Dunstan; Andreas Pflug; Magnus Hagander; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] default database creation with initdb Yes - that's intentional so that pgAdmin/phpPgAdmin et al. can reasonably expect it to be there. Problem is, how the hell do I know it's there before I connect? Well obviously you don't (any more than you know that template1 is accessible until you try), but in time it will be on more and more systems as people upgrade by when it will make a reasonable default for clients. Alternatively, try to connect to it first, and then fall back to template1 (much as libpq negotiates protocol versions with the server). Regards, Dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] default database creation with initdb
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 21 June 2005 05:13 To: Dave Page Cc: Andrew Dunstan; Andreas Pflug; Robert Treat; Magnus Hagander; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] default database creation with initdb Dave Page dpage@vale-housing.co.uk writes: OK, new patch posted to -patches that updates all the utilities as well. Applied. Thanks. Another point is that Dave added code to pg_dumpall to not dump the postgres database. This seems mistaken to me, so I did not include it in the applied patch: if someone is doing real work in postgres then they'll be pretty annoyed if it's not backed up. But perhaps the question needs debate. Any thoughts? My reading of that code was that I merely stopped it dumping the CREATE DATABASE statement (and the ACL) for the database, /not/ the actual contents - in the same way as is done for template1. The theory being that if you are reloading from into a freshing initdb'ed cluster, postgres will already exist so doesn't need to be recreated. Regards, Dave ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] HOOKS for Synchronous Replication
Thank you for the comments. First of all your patch does not conform with the project style. Please have a look at how other files are indented, in particular regarding brace position and ereport() arguments (any function arguments really, but in ereport your problems are more visible). Also, always use ereport() for user messages, elog() for conditions that involve can't-happen situations (server bugs, like not finding a tuple in a catalog that should be there, etc). OK !!! I am going to correct these problems. Also there are some changes that you certainly don't want committed. For example why are you removing the TransState from xact.c? I simply moved it to the xact.h... Basically, it was done to be able to access the current transaction state from other files. Thus, I can avoid to rebuild the structure that stores the global triggers in the middle of a transaction. However, the lack of this functionality it is not critical since the superuser is the only person that can change such triggers. Also, consider using the XactCallback mechanism instead of inventing your own. Unfortunately, the XactCallback is called after commit which means that a transaction cannot be rolled back. For synchronous replication, it is necessary to have a before commit event. Moreover, the addition of a callback function implies modifications to the source code, am I wrong ? These global triggers could be easily adapted to call functions before and after commit without changing the code. On a different front, have you considered talking to the people behind Slony-II to see if they'd have some use for your hooks? I would like to share the hooks with them and other things that I have been done. Best regards, Alfranio Junior. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Schedule for 8.1 feature freeze
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: 21 June 2005 05:04 To: PostgreSQL-development Subject: [HACKERS] Schedule for 8.1 feature freeze We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. Bruce, we're working on GiST concurrency which is based on GiST recovery code already submitted. Hopefully, we'll submit patch before July 1. There is also one problem we already discussed - we have no gist opclasses in core we could use for regression tests ! Probably, it's a time we need to add some opclasses from intarray or rtree_gist to core ? btw, there was a noise about funding of our work, but I've contacted only with PostGis guys about real contribution. I'm just wondered if any commercial clones do really understand the importance of our work. Should I write some explanation what're recovery and concurrency for GiST ? Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] HOOKS for Synchronous Replication
First of all your patch does not conform with the project style. Please
have a look at how other files are indented, in particular regarding
brace position and ereport() arguments (any function arguments really,
but in ereport your problems are more visible). Also, always use
ereport() for user messages, elog() for conditions that involve
can't-happen situations (server bugs, like not finding a tuple in a
catalog that should be there, etc).
I think it is ok now.
However, I corrected the indentation manually.
I could not run some of the tools, namely the entab.
/usr/lib/gcc-lib/i386-redhat-linux/3.3.3/include/varargs.h:4:2: #error
GCC no longer implements varargs.h.
/usr/lib/gcc-lib/i386-redhat-linux/3.3.3/include/varargs.h:5:2: #error
Revise your code to use stdarg.h.
halt.c:23: error: syntax error before va_dcl
halt.c:24: error: syntax error before '{' token
Linux alfranio.lsd.di.uminho.pt 2.6.8-1.521 #1 Mon Aug 16 09:01:18 EDT
2004 i686 i686 i386 GNU/Linux
(GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
Do you have any idea ?
Best regards,
Alfranio Junior.
---(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] GiST concurrency
Now I basically finished recovery for GiST (of course, it's need a hard testing)
and go to concurrency. As it described in Kornaker, Mohan and Hellerstein's
paper
(http://www.sai.msu.su/~megera/postgres/gist/papers/concurrency/sigmod97-gist.pdf)
it's need a way to get global LSN, in our case - XLogRecPtr of last changed
page. As I understand, I can't use ProcLastRecPtr because it is one-process
wide, I need value stored in shared memory. So, may I add method to xlog.c like
this:
/*
* The returning recptr is the beginning of the current record to fill.
* This value is already stored as LSN for changed data pages.
*/
XLogRecPtr
GetCurrentRecPtr(void) {
XLogCtlInsert *Insert = XLogCtl-Insert;
XLogRecPtr RecPtr;
LWLockAcquire(WALInsertLock, LW_SHARED);
INSERT_RECPTR(RecPtr, Insert, Insert-curridx);
LWLockRelease(WALInsertLock);
return RecPtr;
}
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] query plan ignoring check constraints
On Tue, Jun 21, 2005 at 09:46:50 +1000,
John Hansen [EMAIL PROTECTED] wrote:
Someone Wrote:
Should not check constraint act as the first filter? The index should
ideally be scanned only when the check constraint is passed by the
search
criteria but surprisingly it did not happen. The explain analyze
showed
cost for index scans of subtables that cannot contain rows matching
the
search criteria.
Obviously, indexes on columns with a check constraint, should be
qualified with the same check constraint.
I think the real problem is that check constraints on tables aren't
used by the optimizer. Given that, what you have below is expected.
There has been talk about that in the past, but I haven't heard anything
recently about someone considering implenting that.
For your problem consider not using a partial index. It isn't going to
save anything if it has a constraint matching that of the table.
test=# CREATE TABLE test (
foo text check(foo IN ('YES','NO'))
);
CREATE TABLE
test=# CREATE INDEX text_foo_idx ON test (foo) WHERE foo IN('YES','NO');
CREATE INDEX
test=# INSERT INTO test VALUES ('YES');
INSERT 280188 1
test=# INSERT INTO test VALUES ('NO');
INSERT 280189 1
test=# INSERT INTO test VALUES ('no');
ERROR: new row for relation test violates check constraint
test_foo_check
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'YES';
QUERY PLAN
Index Scan using text_foo_idx on test (cost=0.00..5.82 rows=7
width=32) (actual time=0.369..0.376 rows=1 loops=1)
Index Cond: (foo = 'YES'::text)
Total runtime: 0.490 ms
(3 rows)
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'no';
QUERY PLAN
Seq Scan on test (cost=0.00..25.38 rows=7 width=32) (actual
time=0.358..0.358 rows=0 loops=1)
Filter: (foo = 'no'::text)
Total runtime: 0.421 ms
(3 rows)
test=#
... John
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] query plan ignoring check constraints
Bruno Wolff III [mailto:[EMAIL PROTECTED] Wrote I think the real problem is that check constraints on tables aren't used by the optimizer. Given that, what you have below is expected. There has been talk about that in the past, but I haven't heard anything recently about someone considering implenting that. For your problem consider not using a partial index. It isn't going to save anything if it has a constraint matching that of the table. Ahh, I get it now,... If a column has a CHECK (col IN (1,2,3)) and a query says .. WHERE col = 4; then the planner should know that the query will return 0 rows, right? ... John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schedule for 8.1 feature freeze
* Bruce Momjian (pgman@candle.pha.pa.us) wrote: We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. Bruce, I'd really like to see role support added into 8.1. I've sent Alvaro and Tom versions of the patch in the past and I was planning on submitting it to -patches soon. There's a few remaining issue but I don't think they'll take very long to clean up; I've just been unfortunately pretty busy lately. I'm hopeful that I'll be able to spend some time on it this week and next week to hopefully address at least the remaining issues on my list. Lookups need to do multi-level role resolution (for owner and other ACL areas) (Not very hard, and mostly isolated to the acl code) Need to implement per-backend role-member cacheing (Not very hard, basically same kind of way pg_namespace does schema_path) Support 'grant role to role' (Shouldn't be too difficult, mainly just parser work) Support 'with admin option' (Somewhat difficult, but not necessary for the same functionality we have today) Support 'granted by' (Not very hard, just parser work really) Fix other parsers (ecpg, etc) based on updates to backend/parser. (Worked on this some already, should be done real soon now) Distinguish 'create role' permissions from 'superuser'? (Not sure if this makes sense) Modify \du, \dg to use new tables (They work already using the backwards-compat views, just would be nice to update them to the new schema). ? Add \dr, \dm (Should be easy and would be nice I think, though perhaps not required) Add documentation for CREATE ROLE/etc Document new system catalogs (pg_authid, pg_auth_members) (Documentation updates) There are views for pg_shadow/pg_group and things seem to be happy with them. CREATE USER/CREATE GROUP work more-or-less as expected. An interesting side-effect is that if you do 'create group' and then look in the 'group' table you don't see the group till you actually put users in it. Not entirely sure if anything actually depends on that (You can, after all, still grant rights to the role which was created by CREATE GROUP, and do ALTER GROUP on it, etc). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] query plan ignoring check constraints
On Tue, Jun 21, 2005 at 21:54:34 +1000, John Hansen [EMAIL PROTECTED] wrote: Bruno Wolff III [mailto:[EMAIL PROTECTED] Wrote I think the real problem is that check constraints on tables aren't used by the optimizer. Given that, what you have below is expected. There has been talk about that in the past, but I haven't heard anything recently about someone considering implenting that. For your problem consider not using a partial index. It isn't going to save anything if it has a constraint matching that of the table. Ahh, I get it now,... If a column has a CHECK (col IN (1,2,3)) and a query says .. WHERE col = 4; then the planner should know that the query will return 0 rows, right? In an ideal world yes; in the current world no. However if you have a normal index on the table, an index scan that finds no rows isn't terribly expensive. You only want to use partial indexes when they don't cover the whole table. They make sense to enforce uniqueness of a column under some condition and when you can save significant space (becuase the condition is only satisfied for a small fraction of rows). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] query plan ignoring check constraints
Bruno Wolff III [mailto:[EMAIL PROTECTED] Wrote: You only want to use partial indexes when they don't cover the whole table. They make sense to enforce uniqueness of a column under some condition and when you can save significant space (becuase the condition is only satisfied for a small fraction of rows). Yes, I know that,. I misunderstood the original post as a request for queries NOT to use indexes where it doesn't match the table contents. .. John ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] query plan ignoring check constraints
On Tue, Jun 21, 2005 at 22:11:25 +1000, John Hansen [EMAIL PROTECTED] wrote: I misunderstood the original post as a request for queries NOT to use indexes where it doesn't match the table contents. I think that is what they were asking, but I don't think they wanted to see a sequential scan as the alternative. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] default database creation with initdb
Tom Lane wrote: One thing that neither Dave nor I wanted to touch is pg_autovacuum. If that gets integrated into the backend by feature freeze then the question is moot, but if it doesn't then we'll have to decide whether autovac should preferentially connect to template1 or postgres. Neither choice seems real appealing to me: if autovac connects to template1 then it could interfere with CREATE DATABASE, but if it connects to postgres then it could fail if postgres isn't there. Now the latter does not bother me if autovac is considered a client, but it does bother me if autovac is considered part of the backend. I think that template1 and template0 can reasonably be considered special from the point of view of the backend --- but I really don't want postgres to be special in that way. I'm still hoping that autovac will get integrated so this will be moot, but just in case. Perhaps pg_autovacuum should try to connect to the postgres database and if the connection fails, then it will try to connect to template1. This way autovacuum will work whether the postgres database is there or not. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Escape handling in strings
Oliver Jowett [EMAIL PROTECTED] writes: Bruce Momjian wrote: I have received very few replies to my suggestion that we implement E'' for escaped strings, so eventually, after a few major releases, we can have '' treat backslashes literally like the SQL standard requires. Just checking: with this plan, a client needs to know what server version is in use to correctly escape strings, correct? That is, there is no escape mechanism that works correctly for both old and new servers? When the change happens, yes, it will be non compatible. I don't recommend thinking of it as a server version check though --- we will put in a read-only GUC variable (like the one for integer datetimes) and you can check it through the parameter reporting mechanism. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Compiling tsearch2 on AIX
Tom: Got it working! I had to modify this line in the tsearch2 Makefile from SHLIB_LINK := -lm to SHLIB_LINK := -lm -lpgport it works like a charm! thanks for your help! On 5/18/05, Mag Gam [EMAIL PROTECTED] wrote: Thanks for the reply Tom! I managed to get the cvs version and I made more progress compiling tsearch2 Here is where it errors out. touch libtsearch2.a ../../src/backend/port/aix/mkldexport.sh libtsearch2.a libtsearch2.exp gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -fno-strict-aliasing -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE -o libtsearch2.so libtsearch2.a -L../../src/port -L../../src/port -lm -Wl,-bI:../../src/backend/postgres.imp -Wl,-bE:libtsearch2.exp ld: 0711-317 ERROR: Undefined symbol: .get_share_path ld: 0711-317 ERROR: Undefined symbol: .pg_strncasecmp ld: 0711-317 ERROR: Undefined symbol: .pg_strcasecmp ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. collect2: ld returned 8 exit status gmake: *** [libtsearch2.so] Error 1 Any ideas? TIA On 5/17/05, Tom Lane [EMAIL PROTECTED] wrote: Mag Gam [EMAIL PROTECTED] writes: I am trying to compile PostgreSQL 8.0.3/tsearch2 contrib module on AIX 5.2 ML 3 /opt/freeware/lib/gcc-lib/powerpc-ibm-aix5.2.0.0/3.3.2/include/stdio.h:484: error: conflicting types for `fgetpos64' /opt/freeware/lib/gcc-lib/powerpc-ibm-aix5.2.0.0/3.3.2/include/stdio.h:310: error: previous declaration of `fgetpos64' I'm just guessing, but this smells to me like a problem with largefile- vs-not-largefile support. We discovered long ago that when pg_config.h defines _FILE_OFFSET_BITS or _LARGEFILE_SOURCE, it's critical that the compiler see that *before* it includes stdio.h. Unfortunately it seems this hard-won knowledge didn't get propagated into tsearch2 until just recently :-(. Try applying the tsearch2 changes linked here: http://archives.postgresql.org/pgsql-committers/2005-05/msg00068.php and let us know if that helps. I have not back-patched these changes into existing release branches, but if it turns out to address a real problem-seen-in-the-field then that should get done ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] O_DIRECT for WAL writes
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I tested two combinations, - fsync_direct: O_DIRECT+fsync() - open_direct: O_DIRECT+O_SYNC to compare them with O_DIRECT on my linux machine. The pgbench results still shows a performance win: scale| DBsize | open_sync | fsync=false | O_DIRECT only| fsync_direct | open_direct -++---+--+--+--+--- 10 | 150MB | 252.6 tps | 263.5(+ 4.3%)| 253.4(+ 0.3%)| 253.6(+ 0.4%)| 253.3(+ 0.3%) 100 | 1.5GB | 102.7 tps | 117.8(+14.7%)| 147.6(+43.7%)| 148.9(+45.0%)| 150.8(+46.8%) 60runs * pgbench -c 10 -t 1000 on one Pentium4, 1GB mem, 2 ATA disks, Linux 2.6.8 Unfortunately, I cannot believe these numbers --- the near equality of fsync off and fsync on means there is something very wrong with the measurements. What I suspect is that your ATA drives are doing write caching and thus the fsyncs are not really waiting for I/O at all. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] GiST concurrency
Teodor Sigaev [EMAIL PROTECTED] writes: Now I basically finished recovery for GiST (of course, it's need a hard testing) and go to concurrency. As it described in Kornaker, Mohan and Hellerstein's paper (http://www.sai.msu.su/~megera/postgres/gist/papers/concurrency/sigmod97-gist.pdf) it's need a way to get global LSN, in our case - XLogRecPtr of last changed page. As I understand, I can't use ProcLastRecPtr because it is one-process wide, I need value stored in shared memory. If the method needs a truly global LSN, then it is broken --- the only way you could have such a value and have it stay good long enough to do anything with it is to block all other backends from inserting any new WAL records. Which is the very antithesis of concurrency. I think you probably misunderstood the paper. It looks to me like the proposal in the paper is to use the LSN assigned to the WAL record that represents a page split operation. Which you get from the XLogInsert --- there's no need for an extra call. 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] [PATCHES] default database creation with initdb
On Tuesday 21 June 2005 04:01, Dave Page wrote:
-Original Message-
From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]
Sent: 21 June 2005 08:57
To: Dave Page
Cc: Robert Treat; Tom Lane; Andrew Dunstan; Andreas Pflug;
Magnus Hagander; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [PATCHES] default database creation with initdb
Yes - that's intentional so that pgAdmin/phpPgAdmin et al. can
reasonably expect it to be there.
Problem is, how the hell do I know it's there before I connect?
Well obviously you don't (any more than you know that template1 is
accessible until you try), but in time it will be on more and more
systems as people upgrade by when it will make a reasonable default for
clients.
Alternatively, try to connect to it first, and then fall back to
template1 (much as libpq negotiates protocol versions with the server).
You know, since we don't maintain static connections (http is our friend)
connecting to template1 really isn't a problem for phppgadmin users. At
least I can't remember anyone ever having complained about it.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Schedule for 8.1 feature freeze
Stephen Frost [EMAIL PROTECTED] writes: I'd really like to see role support added into 8.1. I've sent Alvaro and Tom versions of the patch in the past and I was planning on submitting it to -patches soon. There's a few remaining issue but I don't think they'll take very long to clean up; I've just been unfortunately pretty busy lately. I'm hopeful that I'll be able to spend some time on it this week and next week to hopefully address at least the remaining issues on my list. Stephen, it is not going to be acceptable to sit on that patch until June 30 --- there are other things depending on it. If you don't have time for it now, send in what you have so that someone else can pick up the ball. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] default database creation with initdb
Dave Page dpage@vale-housing.co.uk writes: Another point is that Dave added code to pg_dumpall to not dump the postgres database. My reading of that code was that I merely stopped it dumping the CREATE DATABASE statement (and the ACL) for the database, /not/ the actual contents - in the same way as is done for template1. The theory being that if you are reloading from into a freshing initdb'ed cluster, postgres will already exist so doesn't need to be recreated. D'oh ... you're right of course. Will fix (and add some comments). I wonder though if this code isn't a little broken. It should skip the CREATE DATABASE certainly, but what about the ACL and dumpDatabaseConfig parts? I suspect those got added in at a handy place without enough thought taken as to whether they should be excluded for template1. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Schedule for 8.1 feature freeze
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: I'd really like to see role support added into 8.1. I've sent Alvaro and Tom versions of the patch in the past and I was planning on submitting it to -patches soon. There's a few remaining issue but I don't think they'll take very long to clean up; I've just been unfortunately pretty busy lately. I'm hopeful that I'll be able to spend some time on it this week and next week to hopefully address at least the remaining issues on my list. Stephen, it is not going to be acceptable to sit on that patch until June 30 --- there are other things depending on it. If you don't have time for it now, send in what you have so that someone else can pick up the ball. Sorry, I thought that's what I had been doing when sending to you/Alvaro/-hackers earlier.. I'll send in what I've got and I guess see what happens. I'm planning on still working on it when I have time available so if someone else picks it up to work on it, please let me know so we can coordinate. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] default database creation with initdb
Robert Treat [EMAIL PROTECTED] writes: You know, since we don't maintain static connections (http is our friend) connecting to template1 really isn't a problem for phppgadmin users. At least I can't remember anyone ever having complained about it. Sure you have: people have complained about CREATE DATABASE failing with source database template1 is being accessed by other users often enough. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] default database creation with initdb
Tom Lane wrote: Robert Treat [EMAIL PROTECTED] writes: You know, since we don't maintain static connections (http is our friend) connecting to template1 really isn't a problem for phppgadmin users. At least I can't remember anyone ever having complained about it. Sure you have: people have complained about CREATE DATABASE failing with source database template1 is being accessed by other users often enough. And other GUIs certainly do keep nailed up connections. cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Schedule for 8.1 feature freeze
Bruce Momjian pgman@candle.pha.pa.us writes: We have addressed all the open issues for 8.1 except for auto-vacuum, Aside from the other stuff people mentioned, I have these things on my to-look-at list: * Fix pg_dump to be able to dump large objects in text dumps * Fix inherited constraints (per discussion around 5/20) * Fix reporting of table/column numbers for cursors (per DeSoi, 4/9) * statement_timeout does not behave very reasonably for V3 messages And I'd really like to see pg_role and shared dependencies get in... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] GiST concurrency
If the method needs a truly global LSN, then it is broken --- the only way you could have such a value and have it stay good long enough to do anything with it is to block all other backends from inserting any new WAL records. Which is the very antithesis of concurrency. Global LSN needs to recognize page split produced another process by search algorithm, no more. I think you probably misunderstood the paper. It looks to me like the proposal in the paper is to use the LSN assigned to the WAL record that represents a page split operation. Which you get from the XLogInsert --- there's no need for an extra call. You partially right, I don't read it with care chaper 10.1 last paragraph :( quotation To alleviate the traffic on this high-frequency counter (LSN - teodor), descending operations can memorize the node's LSN instead. /quotation So, value of global LSN isn't needed. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Compiling tsearch2 on AIX
Mag Gam [EMAIL PROTECTED] writes: I had to modify this line in the tsearch2 Makefile from SHLIB_LINK := -lm to SHLIB_LINK := -lm -lpgport Hmm, that's annoying. I was about to commit this change but it actively fails on my machine: /usr/ccs/bin/ld: DP relative code in file ../../src/port/libpgport.a(exec.o) - shared library must be position independent. Use +z or +Z to recompile. make: *** [libtsearch2.sl.0] Error 1 So we need to think harder. I wonder why the libpgport symbols aren't getting picked up from the backend itself on your machine? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Escape handling in strings
FYI On 06/20/2005 11:12:20 PM, Bruce Momjian wrote: [ BCC to general. ] I have received very few replies to my suggestion that we implement E'' for escaped strings, so eventually, after a few major releases, we can have '' treat backslashes literally like the SQL standard requires. I assume this is because most people say, yea, it is going to be a pain, and yea, we should probably do it. A summary of the plan is at: http://candle.pha.pa.us/cgi-bin/pgescape To my mind your recommendation for 8.3 8.3 - Have non-'E' strings treat backslashes literally. would require a major version change, to 9.0 instead of 8.3, as it breaks forward and backward compatability. But that's just the way I see version numbering working, not necessarly how postgresql does it. Karl [EMAIL PROTECTED] Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Escape handling in strings
AgentM [EMAIL PROTECTED] writes: What I am really hoping for is that PQexecParams() [in later versions of libpq] can figure it out for itself so client code doesn't need fixing. That is the plan, right? Out-of-line parameters are not an issue at all --- only string literals embedded in the SQL query. 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] [PATCHES] default database creation with initdb
On Tuesday 21 June 2005 10:04, Tom Lane wrote:
Robert Treat [EMAIL PROTECTED] writes:
You know, since we don't maintain static connections (http is our friend)
connecting to template1 really isn't a problem for phppgadmin users. At
least I can't remember anyone ever having complained about it.
Sure you have: people have complained about CREATE DATABASE failing with
source database template1 is being accessed by other users often
enough.
I meant wrt phppgadmin for us theres no real need to change the default
connection. obviously others will differ.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [GENERAL] index selection by query planner
Rohit Gaddi [EMAIL PROTECTED] writes: I have a table with two indices on the same column, one of which is a partial index. I would like the query planner to use the partial index whenever the query condition lies in the range of the partial index as it would yield better performance. Is there any way to enforce the ordering for the indices? How does the query planner decide which index to use when a particular query is fired? 'Explain Analyze' showed the total index being used in a situation that could be fulfiled by the partial index. When you're asking this sort of question you should actually *show* the EXPLAIN ANALYZE results. I would also suggest showing the comparison to the other plan, which you can get in a nondestructive way like this: EXPLAIN ANALYZE ; -- plan using total index here BEGIN; DROP INDEX total_index; EXPLAIN ANALYZE ; -- plan using partial index here, we hope ROLLBACK; Also, this is really on-topic for pgsql-performance, not either of the two lists you have chosen. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Schedule for 8.1 feature freeze
Bruce Momjian wrote: We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. FYI - plperl status. I am trying to get the following 2 items done by feature freeze: . convert returned perl array to pg array (see patch recently sent for discussion) . validator function The remaining memory issue we have is where plperl does a huge select via SPI. Abhijit Menon-Sen was working on this but is stumped on how to proceed (see his previous message). We have a patch from CommandPrompt which apparently improves the situation, although it doesn't solve the basic problem. If we don't make progress with Abhijit's work, I will look at working that up by July 1. There is also the tiny patch to trap lexical warnings I submitted not long ago still outstanding. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Escape handling in strings
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Bruce Momjian wrote: I have received very few replies to my suggestion that we implement E'' for escaped strings, so eventually, after a few major releases, we can have '' treat backslashes literally like the SQL standard requires. Just checking: with this plan, a client needs to know what server version is in use to correctly escape strings, correct? That is, there is no escape mechanism that works correctly for both old and new servers? When the change happens, yes, it will be non compatible. I don't recommend thinking of it as a server version check though --- we will put in a read-only GUC variable (like the one for integer datetimes) and you can check it through the parameter reporting mechanism. Right, the GUC read-only variables are already in the patch URL I posted. -- 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 8: explain analyze is your friend
Re: [HACKERS] Schedule for 8.1 feature freeze
I'll do my best to submit bitmap index AM patch next week for your review. -- Victor Y. Yegorov ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Server instrumentation patch
Dave Page wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: 21 June 2005 05:04 To: PostgreSQL-development Subject: [HACKERS] Schedule for 8.1 feature freeze We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. What about Andreas' instrumentation stuff? This has been going on since before 8.0 and it would good to get it in 8.1 given the amount of extra functionality it allows us to offer users that prefer a GUI interface. I realise there probably won't be time to fix pg_terminate_backend, or convince people that it offers the admin the lesser of two evils (my limited understanding being that there is a chance of it not clearing some locks, vs, having to shut down the whole server to kill a single connection) - can we at least get the other functions applied? [ CC to Andreas.] OK, let me address this, but you might not like what I have to say. ;-) Basically, Andreas' approach for 8.0 was to develop a patch (without posting a proposal or interface), and then argue why pgadmin needs it, but without addressing the real concerns about the patch. Saying pgadmin needs it just isn't enough to get a patch in. There are the issues of security and maintainability that have to be addressed, and in the limited time we had to do this in 8.0, it was clear the patch should not be applied. Now, in 8.1, the same thing has happened. Two weeks before feature freeze, with no discussion, the patch appears, and makes no reference to concerns raised during the 8.0 discussion. pg_terminate_backend is even in the patch, and there is no mention or attempt to address concerns we had in 8.0. The move of dbsize into the backend is similar. He moves the parts of dbsize the pgadmin needs into the backend, but makes no mention or change to /contrib/dbsize to adjust it to the movement of the code. He has since posted and updated version that fixes this, I think, but again, we have to discuss how this is to be done --- do we move all the dbsize functions into the backend, some, or none? Do the other dbsize functions stay in /contrib or get deleted? This needs discussion, not a patch. And because there are so many assumptions made in the patch, the patch committers look unreasonable asking for X changes to his patch, when in fact he made X assumptions in the patch and never asked anyone before developing the patch about those assumptions. Basically, I think this is a great example of how _not_ to do things in the community: o don't post your proposal for discussion o don't mention controversial issues in your patch o don't fully address code migrations in your patch It seems like the goal is to throw in the patch on the hopes that no one will remember or realize the problems before it gets into CVS. What has to happen now is that we need to restart the server instrumentation discussion with a proposal of what needs to be added to the server, and why, and then we can deal with any concerns raised. The same is true with the dbsize patch. Bad news, yea, but I think it is the only way to move forward. -- 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: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] O_DIRECT for WAL writes
Takahiro, scale| DBsize | open_sync | fsync=false | O_DIRECT only| fsync_direct | open_direct -++---+--+--+--+ --- 10 | 150MB | 252.6 tps | 263.5(+ 4.3%)| 253.4(+ 0.3%)| 253.6(+ 0.4%)| 253.3(+ 0.3%) 100 | 1.5GB | 102.7 tps | 117.8(+14.7%)| 147.6(+43.7%)| 148.9(+45.0%)| 150.8(+46.8%) 60runs * pgbench -c 10 -t 1000 on one Pentium4, 1GB mem, 2 ATA disks, Linux 2.6.8 This looks pretty good. I'd like to try it out on some of our tests. Will get back to you on this, but it looks to me like the O_DIRECT results are good enough to consider accepting the patch. What filesystem and mount options did you use for this test? - Are both fsync_direct and open_direct necessary? MySQL seems to use only O_DIRECT+fsync() combination. MySQL doesn't support as many operating systems as we do. What OSes and versions will support O_DIRECT? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schedule for 8.1 feature freeze
Andrew Dunstan wrote: There is also the tiny patch to trap lexical warnings I submitted not long ago still outstanding. OK, I missed that one. I see it at: http://archives.postgresql.org/pgsql-patches/2005-06/msg00280.php However, I don't see the new regession files attached to that URL. Would you repost please? -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] Schedule for 8.1 feature freeze
Magnus Hagander wrote: Is there an Open Issues list yet, as you usually prepare for releases? If so, can you please put these items up on it? Yes, it is at: This item has been added to the 7.4 open items list: http://candle.pha.pa.us/cgi-bin/pgopenitems It did contain only pgautovacuum, but has been updated with the new items. -- 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PROPOSAL FE/BE extension to handle IN/OUT parameters
The current situation with IN/OUT parameters requires that considerable juggling is required on the client end to not pass the OUT parameters in the query. This could be alleviated by adding two messages for stored procedure calls 1) PrepareCall which sent the types, and direction of the parameters 2) BindCall which sends the binds the parameters to the above While I have this working with the jdbc driver, the problem I foresee is that when we do finally implement something like what we have above. The current driver will be quite difficult to maintain. Additionally it will be difficult with the current scheme to return out parameters and a result set. Is it possible to get this into 8.1, or is this a total non-starter Dave Cramer [EMAIL PROTECTED] www.postgresintl.com ICQ #14675561 jabber [EMAIL PROTECTED] ph (519 939 0336 ) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Why is checkpoint so costly?
Folks, Going over some performance test results at OSDL, our single greatest performance issue seems to be checkpointing.Not matter how I fiddle with it, checkpoints seem to cost us 1/2 of our throughput while they're taking place. Overally, checkpointing costs us about 25% of our performance on OLTP workloads. Example: http://khack.osdl.org/stp/302671/results/0/ Can we break down everything that happens during a checkpoint so that we can see where this huge cost is coming from? Checkpointing should be limited to fsyncing to disk and marking WAL files as recyclable, but there seems to be something more. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Strange logic for partial index proving
Sweating over the logic of the theorem prover, I notice it doesn't actually bother to complete an accurate test. I can't see that it produces an error, but I thought I would raise it, if only to share my annoyance at the realisation of how it does things. :-( drop table tenk; create table tenk (col1 int); insert into tenk select generate_series(1,1); create index idx1 on tenk (col1) where col1 1 and col1 10; explain select * from tenk where col1 5 and col1 -5; QUERY PLAN Bitmap Heap Scan on tenk (cost=2.05..49.87 rows=50 width=4) Recheck Cond: ((col1 5) AND (col1 -5)) - Bitmap Index Scan on idx1 (cost=0.00..2.05 rows=50 width=0) Index Cond: ((col1 5) AND (col1 -5)) (4 rows) ...thus it uses an index which does *not* match the query clause to test the impossible condition and thus returns the correct answer of zero. Seems fairly quick also :-) AFAICS this is just a feature of the theorem prover and it never returns an incorrect answer. Anybody think differently? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Space reuse and autovacuum
Gavin Sherry wrote: On Wed, 15 Jun 2005, Bruce Momjian wrote: I am going to start working on it. I am concerned it is a big job. I will post questions as I find them, and the one below is a good one. I'm wondering if effort is being misdirected here. I remember when Mark Wong at OSDL was running pg_autovacuum during a dbt run, he was seeing significant performance loss -- I think on the order of 30% to 40% (I will try and dig up a link to the results). I think these results can be dramatically improved if the focus is on a more effective vacuum. Let's look at what TODO has for vacuum and how autovacuum fits that: Vacuum == * Improve speed with indexes For large table adjustements during vacuum, it is faster to reindex rather than update the index. This is something we should figure out how to do automatically. * Reduce lock time by moving tuples with read lock, then write lock and truncate table Moved tuples are invisible to other backends so they don't require a write lock. However, the read lock promotion to write lock could lead to deadlock situations. The deadlock problem here seems bad. * -Add a warning when the free space map is too small Done. * Maintain a map of recently-expired rows This allows vacuum to target specific pages for possible free space without requiring a sequential scan. I think of this as a secondary Free-space-map (FSM), where instead of recording rows/pages that have free space, we records rows/pages that have expired rows that might be free for reuse if all transactions where the are visible are completed. * Auto-fill the free space map by scanning the buffer cache or by checking pages written by the background writer This could be used to populate the secondary FSM above. * Create a bitmap of pages that need vacuuming Instead of sequentially scanning the entire table, have the background writer or some other process record pages that have expired rows, then VACUUM can look at just those pages rather than the entire table. In the event of a system crash, the bitmap would probably be invalidated. This is an alternative to the FSM that tracks _all_ possible free space rather than just a limited amount like a seconary FSM in shared memory. * Auto-vacuum o Move into the backend code o Use free-space map information to guide refilling o Do VACUUM FULL if table is nearly empty? It seems no matter what TODO items we complete above, we will need some type of automatic vacuum to direct filling the free space map. It might be done using a different method than a sequential scan vacuum, but it will be needed, so we are good to integrate autovacuum then improve how it does its job in future releases. -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] Strange logic for partial index proving
Simon Riggs [EMAIL PROTECTED] writes: Sweating over the logic of the theorem prover, I notice it doesn't actually bother to complete an accurate test. Sure it does. create index idx1 on tenk (col1) where col1 1 and col1 10; explain select * from tenk where col1 5 and col1 -5; [ uses that index ] This is a perfectly legitimate situation. col1 5 implies col1 1 and col1 -5 implies col1 10, therefore the query WHERE condition implies the index predicate, therefore the index contains all tuples that could pass the WHERE condition, therefore the index is usable. Kindly do not break this. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Why is checkpoint so costly?
Josh Berkus josh@agliodbs.com writes: Can we break down everything that happens during a checkpoint so that we can see where this huge cost is coming from? Checkpointing should be limited to fsyncing to disk and marking WAL files as recyclable, but there seems to be something more. I already asked you to measure the thing I think is the likely candidate (to wit, dumping full page images into WAL). 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] PROPOSAL FE/BE extension to handle IN/OUT parameters
Dave Cramer [EMAIL PROTECTED] writes: The current situation with IN/OUT parameters requires that considerable juggling is required on the client end to not pass the OUT parameters in the query. This could be alleviated by adding two messages for stored procedure calls 1) PrepareCall which sent the types, and direction of the parameters 2) BindCall which sends the binds the parameters to the above Is it possible to get this into 8.1, or is this a total non-starter Changing the protocol is a nonstarter at this late date in the release cycle. I previously offered you a hack that would accomplish the same thing (or at least it looks like the same thing to me): ignore parameters of type VOID when looking up a function. Is that unusable from your end? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] pg_terminate_backend idea
I had an idea about how to possibly solve the pg_terminate_backend issue. How about we add a field to PGPROC (or is there a better place?) called shouldExit. pg_terminate_backend will set this field to true in the target backend, and then send the normal cancel query signal. The receiving backend will check for this flag in PostgresMain, and perform a proc_exit(0) if it is set. This way, the net effect of doing pg_terminate_backend() will be that of query cancel followed by the lost connection to client path. Both of which should be well tested by now. I hacked up a really quick test, and it seems to be working in theory. But it still requires me to send some data (such as a dummy query) to the backend before it exits. This is because server side libpq blocks when reading and ignores signals at this time. I believe the fix for this would be to pass a flag down to the libpq routines that we want to be abort in case of signal+flag, set only when doing the main call to recv, so we can kill idle process. But I naturally wanted to float the idea here before digging in with further coding. Does this idea have any merit, or does it just seem stupid? ;-) (hey, if it is, at least it was a try..) //Magnus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PROPOSAL FE/BE extension to handle IN/OUT parameters
Dave Cramer [EMAIL PROTECTED] writes: Yeah, I think that might work if I understand it correctly. Assuming I would be able to prepare, and bind all the parameters, and the OUT parameters would be ignored. FWIW, I proposed adding to the protocol, not modifying the existing messages, so it would be backward compatible and not break existing clients. What I have in mind shouldn't break any existing clients either. There is no use for VOID parameter symbols at the moment, so assigning a special behavior to them won't break any existing code. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PROPOSAL FE/BE extension to handle IN/OUT parameters
Yeah, I think that might work if I understand it correctly. Assuming I would be able to prepare, and bind all the parameters, and the OUT parameters would be ignored. FWIW, I proposed adding to the protocol, not modifying the existing messages, so it would be backward compatible and not break existing clients. Dave On 21-Jun-05, at 5:14 PM, Tom Lane wrote: Dave Cramer [EMAIL PROTECTED] writes: The current situation with IN/OUT parameters requires that considerable juggling is required on the client end to not pass the OUT parameters in the query. This could be alleviated by adding two messages for stored procedure calls 1) PrepareCall which sent the types, and direction of the parameters 2) BindCall which sends the binds the parameters to the above Is it possible to get this into 8.1, or is this a total non-starter Changing the protocol is a nonstarter at this late date in the release cycle. I previously offered you a hack that would accomplish the same thing (or at least it looks like the same thing to me): ignore parameters of type VOID when looking up a function. Is that unusable from your end? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Why is checkpoint so costly?
On Tue, Jun 21, 2005 at 12:00:56PM -0700, Josh Berkus wrote: Folks, Going over some performance test results at OSDL, our single greatest performance issue seems to be checkpointing.Not matter how I fiddle with it, checkpoints seem to cost us 1/2 of our throughput while they're taking place. Overally, checkpointing costs us about 25% of our performance on OLTP workloads. Example: http://khack.osdl.org/stp/302671/results/0/ Can we break down everything that happens during a checkpoint so that we can see where this huge cost is coming from? Checkpointing should be limited to fsyncing to disk and marking WAL files as recyclable, but there seems to be something more. Not only you have to fsync the files; you have to write them before as well. If the bgwriter is not able to keep up then at checkpoint time there is a lot of writing to do. One idea is to fiddle with bgwriter settings, or did you do that already? I see this for the URL above: bgwriter_delay | 200 bgwriter_maxpages | 100 bgwriter_percent | 1 Maybe it should be more aggressive. Another thing to blame is the dump-whole-pages-after-checkpoint business. Maybe the load you are seeing is not completely during checkpoint, but right after it as well. How do you tell from the results that the checkpoint is complete? -- Alvaro Herrera (alvherre[a]surnet.cl) El miedo atento y previsor es la madre de la seguridad (E. Burke) ---(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] Why is checkpoint so costly?
Alvaro, Tom, bgwriter_delay | 200 bgwriter_maxpages | 100 bgwriter_percent | 1 Maybe it should be more aggressive. Yeah, a bgwriter progression is running now. I don't expect it to make much difference. Most of sync impact is syncing the FS cache, which the bgwriter doesn't touch. Another thing to blame is the dump-whole-pages-after-checkpoint business. Maybe the load you are seeing is not completely during checkpoint, but right after it as well. How do you tell from the results that the checkpoint is complete? I can't relate that to the performance numbers, unfortunately. I think that the paging is probably the cause, but I don't know what to do about it. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PROPOSAL FE/BE extension to handle IN/OUT parameters
Dave Cramer [EMAIL PROTECTED] writes:
Yeah, I think that might work if I understand it correctly.
Assuming I would be able to prepare, and bind all the parameters, and
the OUT parameters
would be ignored.
This is what I've got in mind:
regression=# create function myfunc(f1 int, f2 int, out sum int, out prod int)
regression-# language plpgsql strict immutable as $$
regression$# begin
regression$# sum := f1 + f2;
regression$# prod := f1 * f2;
regression$# end$$;
CREATE FUNCTION
regression=# select * from myfunc(11,22);
sum | prod
-+--
33 | 242
(1 row)
Using PREPARE/EXECUTE as a SQL-level substitute for Parse/Bind/Execute
messages, the CVS-tip behavior is
regression=# prepare foo(int,int,void,void) as
regression-# select * from myfunc($1,$2,$3,$4);
ERROR: function myfunc(integer, integer, void, void) does not exist
and with the attached patch you'd get
regression=# prepare foo(int,int,void,void) as
regression-# select * from myfunc($1,$2,$3,$4);
PREPARE
regression=# execute foo(11,22,null,null);
sum | prod
-+--
33 | 242
(1 row)
Does that solve your problem?
regards, tom lane
*** src/backend/parser/parse_func.c.origMon May 30 21:03:23 2005
--- src/backend/parser/parse_func.c Tue Jun 21 17:43:51 2005
***
*** 64,69
--- 64,70
Oid rettype;
Oid funcid;
ListCell *l;
+ ListCell *nextl;
Node *first_arg = NULL;
int nargs = list_length(fargs);
int argn;
***
*** 85,90
--- 86,118
errmsg(cannot pass more than %d arguments to
a function,
FUNC_MAX_ARGS)));
+ /*
+* Extract arg type info in preparation for function lookup.
+*
+* If any arguments are Param markers of type VOID, we discard them
+* from the parameter list. This is a hack to allow the JDBC driver
+* to not have to distinguish input and output parameter symbols
+* while parsing function-call constructs. We can't use foreach()
+* because we may modify the list ...
+*/
+ argn = 0;
+ for (l = list_head(fargs); l != NULL; l = nextl)
+ {
+ Node *arg = lfirst(l);
+ Oid argtype = exprType(arg);
+
+ nextl = lnext(l);
+
+ if (argtype == VOIDOID IsA(arg, Param))
+ {
+ fargs = list_delete_ptr(fargs, arg);
+ nargs--;
+ continue;
+ }
+
+ actual_arg_types[argn++] = argtype;
+ }
+
if (fargs)
{
first_arg = linitial(fargs);
***
*** 99,105
*/
if (nargs == 1 !agg_star !agg_distinct list_length(funcname)
== 1)
{
! Oid argtype = exprType(first_arg);
if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
--- 127,133
*/
if (nargs == 1 !agg_star !agg_distinct list_length(funcname)
== 1)
{
! Oid argtype = actual_arg_types[0];
if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
***
*** 117,134
}
/*
!* Okay, it's not a column projection, so it must really be a
!* function. Extract arg type info in preparation for function lookup.
!*/
! argn = 0;
! foreach(l, fargs)
! {
! Node *arg = lfirst(l);
!
! actual_arg_types[argn++] = exprType(arg);
! }
!
! /*
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles inheritance, and
* returns the funcid and type and set or singleton status of the
--- 145,151
}
/*
!* Okay, it's not a column projection, so it must really be a function.
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles inheritance, and
* returns the funcid and type and set or singleton status of the
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_terminate_backend idea
Magnus Hagander [EMAIL PROTECTED] writes: But it still requires me to send some data (such as a dummy query) to the backend before it exits. This is because server side libpq blocks when reading and ignores signals at this time. I believe the fix for this would be to pass a flag down to the libpq routines that we want to be abort in case of signal+flag, set only when doing the main call to recv, so we can kill idle process. Yech! That code is messy enough already, lets not pile another kluge atop it in order to handle something that's not even being requested AFAIR. In any case the correct way to solve the problem is to find out what's being left corrupt by SIGTERM, rather than install more messiness in order to avoid facing the real issue ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Strange logic for partial index proving
On Tue, Jun 21, 2005 at 10:33:45PM +0100, Simon Riggs wrote: On Tue, 2005-06-21 at 16:29 -0400, Tom Lane wrote: create index idx1 on tenk (col1) where col1 1 and col1 10; explain select * from tenk where col1 5 and col1 -5; [ uses that index ] This is a perfectly legitimate situation. Like I said, its correct. I didn't suggest changing it. col1 5 implies col1 1 and col1 -5 implies col1 10, therefore the query WHERE condition implies the index predicate, therefore the index contains all tuples that could pass the WHERE condition, therefore the index is usable. ...all tuples that pass the WHERE condition, like none. Guess I'm not Mr Logic. Has anyone looked at how hard it would be to identify impossible conditions as part of planning the query? In this case, you obviously can't get any results, so there's no point in even planning anything. Of course this is a somewhat nonsensical example, but I suspect that there are cases where QBE or other front-ends will generate queries that contain some impossible conditions that can be eliminated. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] The contrib hit list
Looking over the thread Josh started a few days ago, it seems there is consensus to move these contrib modules into separate projects on pgfoundry.org: adddepend dbase dbmirror fulltextindex mSQL-interface mac oracle tips and to kill these outright: array ipc_check miscutil mysql noupdate pg_dumplo pg_upgrade string tools tsearch xml There was also a suggestion to move findoidjoins to src/tools, since that's where it really belongs. Finally, various people have expressed interest in writing replacements for these: reindexdb userlock If I don't hear any objections, I'll cvs delete the candidates for outright kills in a day or two, and the pgfoundry candidates as soon as someone moves them over (not being much of a pgfoundry user, I'd prefer to let someone else set those projects up). I'll move findoidjoins too. Anyone want to get to work on replacing reindexdb or userlock? It'd be real nice to get rid of userlock so that we could have a clean story on all-BSD-license for 8.1. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Removing Kerberos 4
Magnus Hagander [EMAIL PROTECTED] writes: This patch removes Kerberos version 4 support from the backend and libpq. Per previous mail, I sent a mail to both hackers and -general about a month ago asking for ppl who use it, for zero responses. I also looked back in the archives and it seems it has been asked before and also not responded, so I think it's safe to say it's not in widespread use ATM. Finally, kerberos version 4 is deprecated by the kerberos people - for security reasons amongst others. Last chance for any Kerberos 4 users to speak up --- otherwise I'll apply this soon. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Strange logic for partial index proving
Jim C. Nasby [EMAIL PROTECTED] writes: Has anyone looked at how hard it would be to identify impossible conditions as part of planning the query? The question in my mind is not so much how hard it would be as how many cycles we would waste trying to prove things that won't be true for 99.999% of queries. There is always a tradeoff involved when you add more processing to the planner, and in this case I can't believe that it would be a win. Simon is looking at a different and much more constrained case (WHERE clause provably inconsistent with check constraints of individual tables in an inheritance hierarchy), and so the risk of wasted processing doesn't loom so large. Note also that when the contradictory constraints are on a column of a btree index, the amount you save by recognizing the condition in the planner isn't all that great, since the btree index code discovers it during plan startup anyway. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Schedule for 8.1 feature freeze
On Jun 21, 2005, at 1:03 PM, Bruce Momjian wrote: We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. I'm hoping to add documentation and regression tests for interval- day over the next couple of days and have that ready for 8.1 as well. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] major, minor and micro version
On Wed, Jun 22, 2005 at 12:36:23AM +0200, Andreas 'ads' Scherbaum wrote: Hello, is it possible to get the PG version splitted up into major, minor and micro version in the future? For now, only PG_VERSION is defined (at least, what i can see) and it is not possible to use PG_VERSION at compile time to determine the actual PG version. Example: In a module i want to use some of the new features from 8.0, but if the user is compiling the module in a 7.x source tree, the old (but slower) functions must be used (SPI_exec versus SPI_execute). This has been requested before, and while I don't remember whether the petition has been accepted, I do remember that the customary workaround was to use the CATALOG_VERSION_NO symbol from src/include/catversion.h. This number is supposed to keep unchanged across a major.minor release. -- Alvaro Herrera (alvherre[a]surnet.cl) When the proper man does nothing (wu-wei), his thought is felt ten thousand miles. (Lao Tse) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Why is checkpoint so costly?
On Tue, Jun 21, 2005 at 02:45:32PM -0700, Josh Berkus wrote: Another thing to blame is the dump-whole-pages-after-checkpoint business. Maybe the load you are seeing is not completely during checkpoint, but right after it as well. How do you tell from the results that the checkpoint is complete? I can't relate that to the performance numbers, unfortunately. I think that the paging is probably the cause, but I don't know what to do about it. Tom gave instructions in a mail (to you I think) to patch the xlog.c file so page dumps stop happening. I'm too lazy to search for that mail (I deleted my local copy) but if you find it in your mailbox, resend it to me and I'll produce a patch for you to test. (I'd produce the patch myself but I don't know the xlog code well enough to find the right spot quickly.) -- Alvaro Herrera (alvherre[a]surnet.cl) Jason Tesser: You might not have understood me or I am not understanding you. Paul Thomas: It feels like we're 2 people divided by a common language... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Schedule for 8.1 feature freeze
On Jun 21, 2005, at 1:03 PM, Bruce Momjian wrote: We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. I'm hoping to add documentation and regression tests for interval- day over the next couple of days and have that ready for 8.1 as well. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Why is checkpoint so costly?
Alvaro, Tom gave instructions in a mail (to you I think) to patch the xlog.c file so page dumps stop happening. I'm too lazy to search for that mail (I deleted my local copy) but if you find it in your mailbox, resend it to me and I'll produce a patch for you to test. (I'd produce the patch myself but I don't know the xlog code well enough to find the right spot quickly.) Found it. Testing now. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Schedule for 8.1 feature freeze
People: Oh, and in implementing the COPY stuff we realized that there weren't any regression tests for encodings. If we can figure out how to do them, we'll add those. Are new regression tests permitted during beta? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] The contrib hit list
Sounds good to me... just wondering where dblink went? Is it going to remain a part of the dist? Sorry if this was already answered, I haven't had the time to check the archive. -Jonah Tom Lane wrote: Looking over the thread Josh started a few days ago, it seems there is consensus to move these contrib modules into separate projects on pgfoundry.org: adddepend dbase dbmirror fulltextindex mSQL-interface mac oracle tips and to kill these outright: array ipc_check miscutil mysql noupdate pg_dumplo pg_upgrade string tools tsearch xml There was also a suggestion to move findoidjoins to src/tools, since that's where it really belongs. Finally, various people have expressed interest in writing replacements for these: reindexdb userlock If I don't hear any objections, I'll cvs delete the candidates for outright kills in a day or two, and the pgfoundry candidates as soon as someone moves them over (not being much of a pgfoundry user, I'd prefer to let someone else set those projects up). I'll move findoidjoins too. Anyone want to get to work on replacing reindexdb or userlock? It'd be real nice to get rid of userlock so that we could have a clean story on all-BSD-license for 8.1. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Server instrumentation patch
Bruce Momjian wrote: Dave Page wrote: Basically, Andreas' approach for 8.0 was to develop a patch (without posting a proposal or interface), and then argue why pgadmin needs it, but without addressing the real concerns about the patch. Extending the logging was to get a means of reading the log file without console access, with *any* client. The proposal to develop the generic file functions came from a Mr Bruce Momjian. Saying pgadmin needs it just isn't enough to get a patch in. Never said that. It's needed by dbadmins without console access. There are the issues of security and maintainability that have to be addressed, All issues were discussed and solved. and in the limited time we had to do this in 8.0, it was clear the patch should not be applied. Now, in 8.1, the same thing has happened. Two weeks before feature freeze, I posted it on June 1st. with no discussion, the patch appears, and makes no reference to concerns raised during the 8.0 discussion. RTFM. The lengthy original discussion which addressed _all_ issues is referenced. pg_terminate_backend is even in the patch, and there is no mention or attempt to address concerns we had in 8.0. I never intended to address the issues, I wanted to address the every day problem to kill a backend without killing the server. Drop it, for god's sake. The move of dbsize into the backend is similar. He moves the parts of dbsize the pgadmin needs into the backend, but makes no mention or change to /contrib/dbsize to adjust it to the movement of the code. He has since posted and updated version that fixes this, I think, but again, we have to discuss how this is to be done --- do we move all the dbsize functions into the backend, some, or none? Do the other dbsize functions stay in /contrib or get deleted? This needs discussion, not a patch. And because there are so many assumptions made in the patch, the patch committers look unreasonable asking for X changes to his patch, when in fact he made X assumptions in the patch and never asked anyone before developing the patch about those assumptions. This was discussed lengthy starting May 11th, except for the broken dbsize functions. My post is the result from that. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schedule for 8.1 feature freeze
On Tue, 21 Jun 2005, Josh Berkus wrote: People: Oh, and in implementing the COPY stuff we realized that there weren't any regression tests for encodings. If we can figure out how to do them, we'll add those. Are new regression tests permitted during beta? Yes, most definitely ... especially if they happen to pick up a bug at the same time :) Anything that improves *testing* or *documentation* should be no-brainers for addition during the release process, since they both improve the end product without affecting the backend code itself ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PROPOSAL FE/BE extension to handle IN/OUT parameters
I think it makes my code cleaner, I'll give you an update tomorrow
Dave
On 21-Jun-05, at 5:49 PM, Tom Lane wrote:
Dave Cramer [EMAIL PROTECTED] writes:
Yeah, I think that might work if I understand it correctly.
Assuming I would be able to prepare, and bind all the parameters, and
the OUT parameters
would be ignored.
This is what I've got in mind:
regression=# create function myfunc(f1 int, f2 int, out sum int,
out prod int)
regression-# language plpgsql strict immutable as $$
regression$# begin
regression$# sum := f1 + f2;
regression$# prod := f1 * f2;
regression$# end$$;
CREATE FUNCTION
regression=# select * from myfunc(11,22);
sum | prod
-+--
33 | 242
(1 row)
Using PREPARE/EXECUTE as a SQL-level substitute for Parse/Bind/Execute
messages, the CVS-tip behavior is
regression=# prepare foo(int,int,void,void) as
regression-# select * from myfunc($1,$2,$3,$4);
ERROR: function myfunc(integer, integer, void, void) does not exist
and with the attached patch you'd get
regression=# prepare foo(int,int,void,void) as
regression-# select * from myfunc($1,$2,$3,$4);
PREPARE
regression=# execute foo(11,22,null,null);
sum | prod
-+--
33 | 242
(1 row)
Does that solve your problem?
regards, tom lane
*** src/backend/parser/parse_func.c.origMon May 30 21:03:23 2005
--- src/backend/parser/parse_func.cTue Jun 21 17:43:51 2005
***
*** 64,69
--- 64,70
Oidrettype;
Oidfuncid;
ListCell *l;
+ ListCell *nextl;
Node *first_arg = NULL;
intnargs = list_length(fargs);
intargn;
***
*** 85,90
--- 86,118
errmsg(cannot pass more than %d arguments to a
function,
FUNC_MAX_ARGS)));
+ /*
+ * Extract arg type info in preparation for function lookup.
+ *
+ * If any arguments are Param markers of type VOID, we
discard them
+ * from the parameter list. This is a hack to allow the JDBC
driver
+ * to not have to distinguish input and output parameter
symbols
+ * while parsing function-call constructs. We can't use
foreach()
+ * because we may modify the list ...
+ */
+ argn = 0;
+ for (l = list_head(fargs); l != NULL; l = nextl)
+ {
+ Node *arg = lfirst(l);
+ Oidargtype = exprType(arg);
+
+ nextl = lnext(l);
+
+ if (argtype == VOIDOID IsA(arg, Param))
+ {
+ fargs = list_delete_ptr(fargs, arg);
+ nargs--;
+ continue;
+ }
+
+ actual_arg_types[argn++] = argtype;
+ }
+
if (fargs)
{
first_arg = linitial(fargs);
***
*** 99,105
*/
if (nargs == 1 !agg_star !agg_distinct list_length
(funcname) == 1)
{
! Oidargtype = exprType(first_arg);
if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
--- 127,133
*/
if (nargs == 1 !agg_star !agg_distinct list_length
(funcname) == 1)
{
! Oidargtype = actual_arg_types[0];
if (argtype == RECORDOID || ISCOMPLEX(argtype))
{
***
*** 117,134
}
/*
! * Okay, it's not a column projection, so it must really be a
! * function. Extract arg type info in preparation for
function lookup.
! */
! argn = 0;
! foreach(l, fargs)
! {
! Node *arg = lfirst(l);
!
! actual_arg_types[argn++] = exprType(arg);
! }
!
! /*
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles
inheritance, and
* returns the funcid and type and set or singleton status of
the
--- 145,151
}
/*
! * Okay, it's not a column projection, so it must really be a
function.
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles
inheritance, and
* returns the funcid and type and set or singleton status of
the
---(end of
broadcast)---
TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_terminate_backend idea
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: But it still requires me to send some data (such as a dummy query) to the backend before it exits. This is because server side libpq blocks when reading and ignores signals at this time. I believe the fix for this would be to pass a flag down to the libpq routines that we want to be abort in case of signal+flag, set only when doing the main call to recv, so we can kill idle process. Yech! That code is messy enough already, lets not pile another kluge atop it in order to handle something that's not even being requested AFAIR. I ran into the same problem back when I was trying to implement an idle-in-transaction timeout, so solving this might be useful in more than one place.. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] pl/pgsql: END verbosity
In PL/PgSQL, END LOOP is used to terminate loop blocks, and END IF is used to terminate IF blocks. This is needlessly verbose: we could simply accept END in both cases without syntactic ambiguity. I'd like to make this change, so that END can be used to terminate any kind of block. There's no need to remove support for the present syntax, of course, so there's no backward compatibility concern. Oracle's PL/SQL does require END IF and END LOOP, but folks interested in maximum compatibility can always use those forms if they like. Any objections? -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] The contrib hit list
Jonah H. Harris [EMAIL PROTECTED] writes: Sounds good to me... just wondering where dblink went? Is it going to remain a part of the dist? dblink stays, and so does anything else I didn't mention. (There are actually various proposals to do additional things, but these (a) require actual effort on someone's part and (b) don't necessarily have consensus.) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pl/pgsql: END verbosity
Neil, In PL/PgSQL, END LOOP is used to terminate loop blocks, and END IF is used to terminate IF blocks. This is needlessly verbose: we could simply accept END in both cases without syntactic ambiguity. I'd like to make this change, so that END can be used to terminate any kind of block. There's no need to remove support for the present syntax, of course, so there's no backward compatibility concern. Oracle's PL/SQL does require END IF and END LOOP, but folks interested in maximum compatibility can always use those forms if they like. No problem from me. Since the parser checks for block closure for all block types, I can't see how this would be a problem. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Schedule for 8.1 feature freeze
Josh Berkus josh@agliodbs.com writes: Oh, and in implementing the COPY stuff we realized that there weren't any regression tests for encodings. If we can figure out how to do them, we'll add those. Are new regression tests permitted during beta? Certainly. Feature freeze is about features, not correctness ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Server instrumentation patch
Andreas Pflug wrote: Bruce Momjian wrote: Dave Page wrote: Basically, Andreas' approach for 8.0 was to develop a patch (without posting a proposal or interface), and then argue why pgadmin needs it, but without addressing the real concerns about the patch. Extending the logging was to get a means of reading the log file without console access, with *any* client. The proposal to develop the generic file functions came from a Mr Bruce Momjian. Yes, you are right I helped with the initial file stuff, but not with the terminate nor the dbsize. I can't remember if I got involved before or after the initial patch, so you are right. Saying pgadmin needs it just isn't enough to get a patch in. Never said that. It's needed by dbadmins without console access. Needed and having it added are different issues. As I remember there were security concerns about having the backend able to read/write random files. There are the issues of security and maintainability that have to be addressed, All issues were discussed and solved. I am not aware they were all addressed, and if you had terminate in there, which was clearly not addressed, I question whether the others issues are addressed too. I think we need to re-discuss the idea of these functions. and in the limited time we had to do this in 8.0, it was clear the patch should not be applied. Now, in 8.1, the same thing has happened. Two weeks before feature freeze, I posted it on June 1st. Uh, you are right it wasn't June 22, but it was June 10, not June 1: http://archives.postgresql.org/pgsql-patches/2005-06/msg00226.php with no discussion, the patch appears, and makes no reference to concerns raised during the 8.0 discussion. RTFM. The lengthy original discussion which addressed _all_ issues is referenced. Is that true? I don't remember that conclusion myself. Do others? pg_terminate_backend is even in the patch, and there is no mention or attempt to address concerns we had in 8.0. I never intended to address the issues, I wanted to address the every day problem to kill a backend without killing the server. Drop it, for god's sake. Well, here you are saying you didn't address concerns about terminate, and just posted it because it was needed. That is my point. The move of dbsize into the backend is similar. He moves the parts of dbsize the pgadmin needs into the backend, but makes no mention or change to /contrib/dbsize to adjust it to the movement of the code. He has since posted and updated version that fixes this, I think, but again, we have to discuss how this is to be done --- do we move all the dbsize functions into the backend, some, or none? Do the other dbsize functions stay in /contrib or get deleted? This needs discussion, not a patch. And because there are so many assumptions made in the patch, the patch committers look unreasonable asking for X changes to his patch, when in fact he made X assumptions in the patch and never asked anyone before developing the patch about those assumptions. This was discussed lengthy starting May 11th, except for the broken dbsize functions. My post is the result from that. Really? Where? I don't remember anything about it. -- 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: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PROPOSAL - User's exception in PL/pgSQL
On Tue, 21 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: I wont to prohibit synonyms in exception (every exception has unique sqlstate). I don't think that's a particularly good idea --- maybe if SQL had been designed according to your worldview, it'd be like that, but it isn't and you can't retroactively force it to be. The SQLSTATEs are deliberately designed to be fairly coarse, not unique. I believe the design intention is to distinguish between two cases when it's likely that client application code would do something different in the two cases. Not to be unique for uniqueness' sake. it's can be source of bugs. For me, uniqueness sqlstates is 20 lines more. Ok. I will send patch without unique states. Pavel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_terminate_backend idea
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: But it still requires me to send some data (such as a dummy query) to the backend before it exits. This is because server side libpq blocks when reading and ignores signals at this time. I believe the fix for this would be to pass a flag down to the libpq routines that we want to be abort in case of signal+flag, set only when doing the main call to recv, so we can kill idle process. Yech! That code is messy enough already, lets not pile another kluge atop it in order to handle something that's not even being requested AFAIR. In any case the correct way to solve the problem is to find out what's being left corrupt by SIGTERM, rather than install more messiness in order to avoid facing the real issue ... I am confused. Are you talking about the client SIGTERM or the server? I thought we agreed that using the cancel functionality, which we know works and is tested, to do backend terminate was the right approach. TODO has: * Allow administrators to safely terminate individual sessions Right now, SIGTERM will terminate a session, but it is treated as though the postmaster has paniced and shared memory might not be cleaned up properly. A new signal is needed for safe termination because backends must first do a query cancel, then exit once they have run the query cancel cleanup routine. I don't see us ever able to handle backend terminate in any other way. Are you complaining about the issue with terminating the client? I had not considered 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 8: explain analyze is your friend
Re: [HACKERS] Problem with dblink regression test
Jim C. Nasby [EMAIL PROTECTED] writes: It appears that the dblink regression test defaults to port 5432. I've been trying to get platypus to compile clean on HEAD and 8_0 and it's been failing on dblink. There are several buildfarm machines failing like this. I think a possible solution is for the postmaster to do putenv(PGPORT=nnn) so that libpq instances running in postmaster children will default to the local installation's actual port rather than some compiled-in default port. This is certainly not without its downsides, but if you are running a postmaster at a nondefault port then I think you ought to be aware that leaving dblink to choose a default port is a fragile idea. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_terminate_backend idea
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: In any case the correct way to solve the problem is to find out what's being left corrupt by SIGTERM, rather than install more messiness in order to avoid facing the real issue ... I am confused. Are you talking about the client SIGTERM or the server? I am talking about Rod Taylor's reports that SIGTERM'ing individual backends tends to lead to lock table corrupted crashes awhile later. Now, I've been playing the part of Chicken Little on this for awhile, but seeing an actual report of problems from the field certainly strengthens my feelings about it. What I think we need to do is find a way to isolate and fix the behavior Rod is seeing. It may be that the bug occurs only for SIGTERM, or it may be that it's a general problem that a SIGTERM just increases the probability of seeing. In any case I think we have to solve it, not create new mechanisms to try to ignore it. TODO has: * Allow administrators to safely terminate individual sessions Right now, SIGTERM will terminate a session, but it is treated as though the postmaster has paniced and shared memory might not be cleaned up properly. That statement is entirely incorrect. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problem with dblink regression test
Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: It appears that the dblink regression test defaults to port 5432. I've been trying to get platypus to compile clean on HEAD and 8_0 and it's been failing on dblink. There are several buildfarm machines failing like this. I think a possible solution is for the postmaster to do putenv(PGPORT=nnn) so that libpq instances running in postmaster children will default to the local installation's actual port rather than some compiled-in default port. This is certainly not without its downsides, but if you are running a postmaster at a nondefault port then I think you ought to be aware that leaving dblink to choose a default port is a fragile idea. Thoughts? (Sorry for the slow response, I'm away from home again, this time in South Korea) I think most people would expect that if they don't specify a port, they would be talking to the same postmaster that they are running under on whatever port it is using, not some compiled in default. So your proposal makes perfect sense to me. Then the dblink regression test would not specify a port at all, correct? Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pl/pgsql: END verbosity
Neil Conway said: In PL/PgSQL, END LOOP is used to terminate loop blocks, and END IF is used to terminate IF blocks. This is needlessly verbose: we could simply accept END in both cases without syntactic ambiguity. I'd like to make this change, so that END can be used to terminate any kind of block. There's no need to remove support for the present syntax, of course, so there's no backward compatibility concern. Oracle's PL/SQL does require END IF and END LOOP, but folks interested in maximum compatibility can always use those forms if they like. Any objections? I'm unkeen. I see no technical advantage - it's just a matter of taste. We advertise that plpgsql is similar to plsql - we should not do anything to make that less so IMNSHO. Terseness is not always good, redundancy is not always bad. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_terminate_backend idea
On Tue, 2005-06-21 at 23:34 -0400, Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: In any case the correct way to solve the problem is to find out what's being left corrupt by SIGTERM, rather than install more messiness in order to avoid facing the real issue ... I am confused. Are you talking about the client SIGTERM or the server? I am talking about Rod Taylor's reports that SIGTERM'ing individual backends tends to lead to lock table corrupted crashes awhile later. Now, I've been playing the part of Chicken Little on this for awhile, but seeing an actual report of problems from the field certainly strengthens my feelings about it. What I think we need to do is find a way to isolate and fix the behavior Rod is seeing. It may be that the bug occurs only for SIGTERM, or it may be that it's a general problem that a SIGTERM just increases the probability of seeing. In any case I think we have to solve it, not create new mechanisms to try to ignore it. If it helps, it seems to occur primarily (perhaps always) when there are schema changes being performed when the SIGTERM is issued. I don't remember seeing them on Intel or on v7.2 (we didn't stay on 7.4 very long), but on a fairly well loaded Solaris machine (v880 with between 100 and 200 connections) it happens enough that we automatically schedule a server restart during the first opportunity when we need to kill connections in this way This is generally when the server doesn't recognize the client has dropped -- pgpool can be clumsy with connections). TODO has: * Allow administrators to safely terminate individual sessions Right now, SIGTERM will terminate a session, but it is treated as though the postmaster has paniced and shared memory might not be cleaned up properly. That statement is entirely incorrect. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Server instrumentation patch
On Tue, 21 Jun 2005, Bruce Momjian wrote: I am not aware they were all addressed, and if you had terminate in there, which was clearly not addressed, I question whether the others issues are addressed too. I think we need to re-discuss the idea of these functions. Just curious, but if 'all issues were discussed', maybe instead of 're-discussing' it, why not just read through the archives that should be available of that discussion ... ? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Problem with dblink regression test
Tom Lane said: Jim C. Nasby [EMAIL PROTECTED] writes: It appears that the dblink regression test defaults to port 5432. I've been trying to get platypus to compile clean on HEAD and 8_0 and it's been failing on dblink. There are several buildfarm machines failing like this. I think a possible solution is for the postmaster to do putenv(PGPORT=nnn) so that libpq instances running in postmaster children will default to the local installation's actual port rather than some compiled-in default port. This is certainly not without its downsides, but if you are running a postmaster at a nondefault port then I think you ought to be aware that leaving dblink to choose a default port is a fragile idea. This seems to be my day for getting confused. If this diagnosis were correct, wouldn't every buildfarm member be failing at the ContribCheck stage (if they get that far)? They all run on non standard ports and all run the contrib installcheck suite if they can (this is required, not optional). So if they show OK then they do not exhibit the problem. Also, while the PGPORT= trick looks sort of OK, we need to check it will work on Windows - I am far from sure it will. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pl/pgsql: END verbosity
Andrew Dunstan [EMAIL PROTECTED] writes: Neil Conway said: Any objections? I'm unkeen. I see no technical advantage - it's just a matter of taste. We advertise that plpgsql is similar to plsql - we should not do anything to make that less so IMNSHO. Terseness is not always good, redundancy is not always bad. That was my reaction too, though I'm too tired at this hour to phrase it so well ;-). The long-term point in my mind is that removing syntactical redundancy always reduces the ability to detect errors or report errors acccurately; and it may limit our freedom to introduce new features later. Consider for example the possibility that Oracle's next release adds some new frammish that can't be duplicated because we chose not to distinguish various forms of END xxx ... 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] pl/pgsql: END verbosity
Andrew Dunstan wrote: I'm unkeen. I see no technical advantage - it's just a matter of taste. There is no technical advantage to case insensitive keywords, or dollar quoting, or a variety of other programming language features that don't change functionality but exist to make using the programming language easier. We advertise that plpgsql is similar to plsql - we should not do anything to make that less so IMNSHO. Do you *really* mean that? This principle would mean we should reject patches like the CONTINUE statement patch I just applied, for example, as PL/SQL has no such construct. In any case, I think you are overestimating the value of strict PL/SQL compatibility. IMHO, PL/PgSQL should be a useful procedural programming language first, and a reimplementation of PL/SQL second. We should provide an equivalent feature (not necessarily with the same syntax) for all of PL/SQL's useful features, but I don't see the value in copying Oracle when PL/SQL's implementation of a feature is ugly, broken, or inconsistent with the rest of Postgres. It's not as if complete source-level compatibility with PL/SQL has been a goal for PL/PgSQL anyway (and besides, there are other people, like EnterpriseDB, who can provide that for those who need it). Terseness is not always good, redundancy is not always bad. Granted -- but why is redundancy a good thing here? -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pl/pgsql: END verbosity
Tom Lane wrote: The long-term point in my mind is that removing syntactical redundancy always reduces the ability to detect errors or report errors acccurately Lexical scoping is unambiguous in a language like PL/PgSQL. Since it is simple to determine whether a given END matches an IF, LOOP, or BEGIN, I don't see how it would reduce our ability to detect errors or report errors accurately. Consider for example the possibility that Oracle's next release adds some new frammish that can't be duplicated because we chose not to distinguish various forms of END xxx ... As lexical scoping is still unambiguous, we could actually add a K_LOOP / K_IF token to the input stream, if that would make you happier :) (Of course I'm not suggesting this -- the point is that as far as the parser is concerned, we should have precisely the same information for disambiguating the input as we used to have.) BTW, I notice that Oracle actually allows: label LOOP -- ... END LOOP label; whereas we don't allow the optional label following END LOOP. Which goes to my general point: this frammish has existed in PL/SQL for a while, but it's not as if people are clamoring for us to implement it. I would wager that most people care about having *equivalent* features to PL/SQL, not exactly identical syntax. For example, the lack of autonomous transactions is something people have asked for in the past, because it *does* make porting PL/SQL applications more difficult. I can't see anyone losing any sleep because we are slightly more relaxed about the input we accept. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Schedule for 8.1 feature freeze
Bruce Momjian wrote: We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. It would be nice to upgrade to autoconf 2.59 before the freeze (although it would probably be okay to do this post-freeze but pre-beta). -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
