Re: [HACKERS] TODO questions
Jim C. Nasby [EMAIL PROTECTED] writes: On Wed, Aug 24, 2005 at 10:03:28PM -0700, Ron Mayer wrote: The most unambiguous behavior would be to not have commented out values in the config file at all. That only makes sense if you also remove the concept of default values; something I don't think we want to do. Well, the hardwired default values are really only there to ensure sanity if the config file fails to provide values. We already have to make sure that the hardwired defaults match what it says in postgresql.conf.sample, if only for documentation reasons. So I'm not seeing a strong argument here. 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] Win32 Thread safetyness
On 2005-08-24, Dave Page dpage@vale-housing.co.uk wrote:
Which relates to:
static unsigned long
pq_threadidcallback(void)
{
return (unsigned long) pthread_self();
}
This is an abuse of pthread_t - it is explicitly not guaranteed in the spec
that pthread_t is an integer type, or even a scalar type; it's permitted to
be a structure. The only valid operations on pthread_t values are assignment
and passing to functions (including pthread_equal() for equality comparison).
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] TODO list comments
On Aug 25, 2005, at 10:58 AM, Tom Lane wrote: * %Remove CREATE CONSTRAINT TRIGGER This was used in older releases to dump referential integrity constraints. Do we really want to remove it, and thereby guarantee we can't load dumps from those old releases? Also, I believe CONSTRAINT TRIGGERS are the only way to provide transaction level (rather than statement level) referential integrity. I've used this in the past. The SQL command reference page mentions that it's not for general use, but it'd be a shame to remove it before there's an alternative way to provide transaction level referential integrity. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TODO list comments
On K, 2005-08-24 at 21:58 -0400, Tom Lane wrote: * %Allow TRUNCATE ... CASCADE/RESTRICT Huh? What would that do? Maybe this was meant truncating of tables with dependent foreign keys ? AFAIR this was solved by allowing truncating several tables in one command even if they have FK relationships between themselves. This is only partly done --- the 8.1 patch didn't cover all object types. o %Disallow dropping of an inherited constraint ... o %Prevent child tables from altering constraints like CHECK that were inherited from the parent table These seem to be duplicates, or at least in need of merging. It should probably mention about weird inheritance behaviour of CREATE CONSTRAINT ON ONLY tablename - it is not propagated to existing child tables, but is inherited when creating new ones. Also, I don't think this should be done at all, at least not before we have proper partitioned table support ready. I could live with it creating a warning about not being future-compatible. o Handle references to temporary tables that are created, destroyed, then recreated during a session, and EXECUTE is not used This requires the cached PL/PgSQL byte code to be invalidated when an object referenced in the function is changed. This is redundant with the Dependency Checking item about regenerating cached plans. Or maybe not completely, depending on how you do it. If temp table itself is created inside the same pl/pgsql function, then there could still be a way to do the planning/optimising only once and then substitute temp table oids when running the function. The table structure in this case is quaranteed to be the same during each run of the function, it's just that the temp table and index oids should be treated as local variables. Done this way, it gives real benefits in terms of cached query plans, instead of just preventing newcomers from shooting themselves in foot by not using EXECUTE. * Improve speed with indexes For large table adjustements during vacuum, it is faster to reindex rather than update the index. This applies only to VACUUM FULL, so it probably needs to be reworded. In case we implement concurrent/non-blocking CREATE INDEX at some point, this might be a good idea for lazy VACUUM as well. And it may make more sense to do CLUSTER instead of VACUUM FULL in at least some of these cases. (btw. CLUSTER seems to be another function which my concurrent vacuuming patch should be extended to cover, at least on client side, like CREATE INDEX) * Auto-vacuum o %Suggest VACUUM FULL if a table is nearly empty It seems like a fairly bad idea for auto-vacuum to do a VACUUM FULL ever, given the locking effects. And how is a background daemon going to suggest anything? It could write to the postmaster log but it's entirely likely the user would never notice. With current implementations of commands, doing (some equivalent of) CLUSTER here seems a better idea than VACUUM FULL, as it also un-bloats indexes. Not sure of of transactional behaviour though. -- Hannu Krosing [EMAIL PROTECTED] ---(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] Stuff running slooow
Well, if hardware or bandwidth becomes an issue I suspect we could easily get donations to improve things. IIRC we have plenty of spare both hardware and bandwidth on the box donated by Pervasive. But it runs Linux so you can't just move freebsd VMs across, which is why it's only used as a web frontent and to run planetpostgresql.org at the moment. //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Win32 Thread safetyness
Unfortunately I just found that we still cannot build in thread safety mode on Windows, due to an error on my part - specifically, I concentrated on libpq, not realising that ecpglib is also thread aware. It seems that ecpglib uses far more of pthreads than libpq does, so our mini implementation used in libpq just won't cut it. I've bitten the bullet (well, more of a jelly bean actually) and started rewriting things to use the official win32 pthreads library, however I ran into an error that I'm not sure about: Yuck. This sucks :-( I was very much hoping we could avoid an other build *and* runtime dependency. Which will be a cascading runtime dependency to each and every program that uses libpq. double-:-( Anyway, one other concern: Do we *know* how this will interact with win32 native threads? Meaning will a libpq built against the pthreads library be safe for *native threads*. Because you can definitly expect most of the win32 apps that need thread-safeness to be usign native threads - I've so far not come across a single program that's native win32 that uses pthreads, whereas almost every program written uses native threads (though most often not in a way that would need a threadsafe libpq) //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Proposed patch to getaddrinfo.c to support
It, or some related patch appears to have broken the build on buildfarm member snake. I haven't had time to investigate. /D -Original Message- From: Bruce Momjianpgman@candle.pha.pa.us Sent: 25/08/05 01:14:54 To: Tom Lane[EMAIL PROTECTED] Cc: Andrew Dunstan[EMAIL PROTECTED], Chuck McDevitt[EMAIL PROTECTED], pgsql-patches@postgresql.orgpgsql-patches@postgresql.org, PostgreSQL-developmentpgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] Proposed patch to getaddrinfo.c to support Does this fix IPv6 on Win32? --- Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Context diff, please, diff -c. It needed dos2unix and pgindent as well. Here's a cleaned patch. Thanks to Chuck for doing this work. Applied, thanks. regards, tom lane -- 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: 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 -Unmodified Original Message- Does this fix IPv6 on Win32? --- Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Context diff, please, diff -c. It needed dos2unix and pgindent as well. Here's a cleaned patch. Thanks to Chuck for doing this work. Applied, thanks. regards, tom lane -- 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: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO questions
On Thu, 25 Aug 2005, Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: The most unambiguous behavior would be to not have commented out values in the config file at all. Yeah, Robert Treat suggested that upthread, and I think it's been pushed by others too. The only argument I can see against it is that it'll take longer for the system to process such a file; but do we really care about a few more microseconds to respond to SIGHUP? This has probably been mentioned already, but it makes it much harder to see which values have been altered from their default values. At the very least, the default values should be in the comments together with the min and max values. Also, suppose that we change the default value of parameter in a new release. That change wouldn't be picked up by upgraders that keep using their old config file. I don't know how big an issue that is in practice. We could have a special value DEFAULT that would mean that the variable should be set to it's default. The config file would look like this: shared_buffers = DEFAULT # default 1000 work_mem = DEFAULT# default 1024 ... That would avoid the surprise that commenting a line and reloading doesn't actually do anything, while also making it obvious which values have been modified. This does not address the problem that changing PGC_POSTMASTER values in the file won't do anything without a postmaster restart. Greg Stark's suggestion of marking each PGC_POSTMASTER variable with a warning comment in postgresql.conf seems reasonable to me, though. If we keep the current behaviour, could we also issue a runtime warning if a PGC_POSTMASTER variable was changed? Also, could we issue a warning if a variable is not at it's default value, but is missing from the new config file? - Heikki ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: VACUUM/t_ctid bug (was Re: [HACKERS] GiST concurrency commited)
http://archives.postgresql.org/pgsql-patches/2005-08/msg00304.php Could you perhaps test this patch as well, while you already have a setup for testing parallel vacuums under big loads ? Ok, I'll do it. Or perhaps you can share the setup/scripts/data so that I could run your test myself as well ? Statements generator: http://www.sigaev.ru/gist/concur.pl Usage: ./concur.pl -d DATABASE [-n N] [-c N] [-l] -d DATABASE - DATABASE name -n N- number of rows -c N- number of flow -l - linear mode Script guarantees the same result (ie the same data in table) with the same -n and -c options, result doesn't depend on -l option. Also script guarantees absence of dead locks. % perl concur.pl -d qq -n 10 -c 4 Start: parallel mode with 4 flows 3 flow finish. Stats: ni:25000 nu:555 nd:81 nv:4(nf:1) nt:232 0 flow finish. Stats: ni:25000 nu:554 nd:77 nv:2(nf:0) nt:247 1 flow finish. Stats: ni:25000 nu:548 nd:65 nv:4(nf:1) nt:249 2 flow finish. Stats: ni:25000 nu:552 nd:79 nv:7(nf:2) nt:263 All flow finish; status: 0; elapsed time: 159.25 sec Script prints some statistics per flow: ni - number of insert's statements (sum of this should be equal to -n N) nu - -/- update's statements nd - -/- delete's statements nv - -/- vacuum (including full vacuum) nf - number of full vacuum nt - number of transactions Simple wrapper for manipulating test table and loop testing (you should edit it to right paths): http://www.sigaev.ru/gist/concur.sh Those scripts was wrote to test GiST concurrency. I suspect it's needed to make some changes in generator to increase number of updates and vacuums for your goal. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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] API like mysql_ping
Do we have an API like mysql_ping to check whether the server is up and running after the connection has been established? I checked the PostgreSQL docs but of no use. Is there any work around for this? Regards, Siva Kumar.K
Re: [HACKERS] API like mysql_ping
PQstatus perhaps? http://www.postgresql.org/docs/8.0/interactive/libpq-status.html Chris Sivakumar K wrote: Do we have an API like mysql_ping to check whether the server is up and running after the connection has been established? I checked the PostgreSQL docs but of no use. Is there any work around for this? /*/ Regards, /*/ /*/ Siva Kumar.K /*/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] API like mysql_ping
Christopher Kings-Lynne wrote: PQstatus perhaps? http://www.postgresql.org/docs/8.0/interactive/libpq-status.html This only returns the last status, not the current. pgAdmin uses SELECT 1 for this. Regards, Andreas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] API like mysql_ping
Sivakumar K wrote: Do we have an API like mysql_ping to check whether the server is up and running after the connection has been established? At the protocol level, you could send Sync and wait for ReadyForQuery. -O ---(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] TODO questions
Tom Lane wrote: So, the low-tech solution to these gripes seems to be: * uncomment all the entries in postgresql.conf * add comments to flag the values that can't be changed by SIGHUP Can we agree on taking these measures? Doesn't this still mean that a SIGHUP may give you a different configuration to a full restart, even if you don't touch any of the values that can't be changed via SIGHUP? Perhaps we could stick with the current commented-out-defaults scheme if SIGHUP reset all settings to their compiled-in defaults before rereading the config file, i.e. the same logic as normal startup would do (except for those cases where we can't change a setting at runtime). -O ---(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] TODO list comments
On Wed, 2005-08-24 at 21:58, Tom Lane wrote:
o Add pg_dumpall custom format dumps.
This is probably best done by combining pg_dump and pg_dumpall
into a single binary.
This is probably obsoleted by events, too. Now that we can dump blobs
in text mode, I see no reason that we ever need to do this.
pg_restore's only real reason to live is to support selective restore
(ie, pulling out just a few objects from an existing dump) and I do not
see that you need that for pg_dumpall dumps.
Being able to restore just the database users without restoring all
databases? (There are other ways that could be accomplished, like
adding user information to pg_dump, but it's one scenario anyway)
Actually the argument that you would have to do both a pg_dumpall of the
cluster and a pg_dump of each database in order to obtain this
functionality seems so user unfriendly it seems like something to persue
on those grounds alone (imho).
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO questions
On Thu, Aug 25, 2005 at 12:09:21 +0300, Heikki Linnakangas [EMAIL PROTECTED] wrote: This has probably been mentioned already, but it makes it much harder to see which values have been altered from their default values. At the very least, the default values should be in the comments together with the min and max values. I think it can be expected that people who care will comment out the original line and add a new one for the changed setting. Or if worse comes to worse they can get a fresh copy of postgresql.conf and run diff. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] API like mysql_ping
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 PQstatus perhaps? http://www.postgresql.org/docs/8.0/interactive/libpq-status.html This only returns the last status, not the current. pgAdmin uses SELECT 1 for this. Better still: PQtransactionStatus, followed by a quick PQexec of SELECT 'pingtest' if it returns PQTRANS_IDLE. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508250940 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEARECAAYFAkMNymsACgkQvJuQZxSWSsjm2gCgt+4pdyd5GiFjyJeDqNSbR14C svMAn0OxrtEqcFgDruogLdmhvavokdSb =nist -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO questions
* Andrew Dunstan ([EMAIL PROTECTED]) wrote: Tom Lane wrote: But the contrary position is that a comment is a comment, not something that should act to change the state of the postmaster. I think that's a mis-statement of the issue, as I understand it, which seems to me to be this: Should the absence of an explicit setting in the config file result in keeping the previous setting of the item or in resetting it to the default value? Of course, the question is made more complex by the fact that you can set things by means other than the config file, and having your settings revert to some default might indeed cause some surprises. Notwithstanding that, I'm fairly firmly in the revert to default camp - I think on balance it conforms to the principle of least surprise. I agree w/ Andrew on this, revert-to-default *is* what's expected by most users/admins/etc, at least that I know. If we want something to be a differential against the running config then let's pull out whatever can be changed during runtime and write a .sql script that can be called from the backend (set via the .conf) upon start and SIGHUP. I think there'd be a great deal more understanding of how things work if we then tell people that if they want to change things, change the .sql and then run it, or run those commands themselves by hand; while the .conf contains only those things that require a postmaster full restart to be changed. Just my 2c. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] TODO questions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Not in any conf I have ever seen. If I comment out a parameter I expect that the parameter will either be disabled or set to the default depending on the parameter. Or throw an error. I'm not sure what the big deal is with defaults - everyone should be tuning their conf files intially anyway, the defaults are just there to get you started. If you need a history, put postgresql.conf into your local revisioning system, starting with the supplied one (the default) before you make any changes. If everything is uncommented, it all becomes clear exactly what will happen on reload. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508250958 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEARECAAYFAkMNzrsACgkQvJuQZxSWSsgbPgCgqTEOjdxhJ1TCA/Fnl9UGFo/C 6lwAoOc5HGvR1kciqOSbZMaghUakmCpy =+3h5 -END PGP SIGNATURE- ---(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] TODO list comments
On Thu, 2005-08-25 at 15:50 +0900, Michael Glaesemann wrote:
* %Remove CREATE CONSTRAINT TRIGGER
Do we really want to remove it,
Also, I believe CONSTRAINT TRIGGERS are the only way to provide
transaction level (rather than statement level) referential
integrity.
Don't deferrable foreign keys give you transaction-level referential
integrity? From the SET CONSTRAINTS doc:
Synopsis
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
Description
SET CONSTRAINTS sets the behavior of constraint checking within the
current transaction. IMMEDIATE constraints are checked at the end of
each statement. DEFERRED constraints are not checked until transaction
commit.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] TODO list comments
On Thu, Aug 25, 2005 at 01:53:32PM -, Greg Sabino Mullane wrote: Tom Lane asked: o Improve psql's handling of multi-line queries Uh, what's wrong with it? This item seems far too vague. I think perhaps this means adding multi-line support to the tab-completion? Only thing I can think of, cause other than that, multi-line queries work just fine. The saved history is also not cool about multiline queries. If you enter them interactively (or by pasting), they are entered as several entries. If you edit them with \e, they are entered as a single unit. It would be also nice to have M-# to work well -- currently it inserts a #, which works in bash but is obviously wrong in psql. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Los románticos son seres que mueren de deseos de vida ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Win32 Thread safetyness
Magnus Hagander [EMAIL PROTECTED] writes: Yuck. This sucks :-( I was very much hoping we could avoid an other build *and* runtime dependency. Which will be a cascading runtime dependency to each and every program that uses libpq. double-:-( That seems like a clear nonstarter :-( Can we confine the damage to stuff that uses ecpg, rather than adding a dependency to everything that uses libpq? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] API like mysql_ping
Oliver Jowett [EMAIL PROTECTED] writes: Sivakumar K wrote: Do we have an API like mysql_ping to check whether the server is up and running after the connection has been established? At the protocol level, you could send Sync and wait for ReadyForQuery. At the client level, the easiest thing is probably to execute an empty query string. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO list comments
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane asked: o Improve psql's handling of multi-line queries Uh, what's wrong with it? This item seems far too vague. I think perhaps this means adding multi-line support to the tab-completion? Only thing I can think of, cause other than that, multi-line queries work just fine. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508250952 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEARECAAYFAkMNzTAACgkQvJuQZxSWSshB8gCgvOU3rZi1uwFnwXO2zVz6KjUG TUwAn3VoHGbqGkP1bRItMgVFE3vPQkkf =rA0w -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO questions
On Thu, Aug 25, 2005 at 02:09:10AM -0400, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: On Wed, Aug 24, 2005 at 10:03:28PM -0700, Ron Mayer wrote: The most unambiguous behavior would be to not have commented out values in the config file at all. That only makes sense if you also remove the concept of default values; something I don't think we want to do. Well, the hardwired default values are really only there to ensure sanity if the config file fails to provide values. We already have to make sure that the hardwired defaults match what it says in postgresql.conf.sample, if only for documentation reasons. So I'm not seeing a strong argument here. So then what happens if someone accidentally deletes something from the config file? Or in-advertently comments it out? IMHO, the best way to 'fix' this in the short term is to put a big warning/notice in the config file letting people know what happens when a setting is commented out/missing. In the long term we should go through the same process for reading config settings on a reload as we do on startup (with the obvious exception of startup-only parameters). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO list comments
Oliver Elphick olly@lfix.co.uk writes: It would be better to show the columns aligned (perhaps without showing separators for other columns so as not to give the impression that the other columns contain null or empty strings): junk=# select * from xyz; id |name| address | del_addr ++---+-- 1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels, | Newport, | Swinkley, | Gwent | XX3 5CX (1 row) I think the above is unacceptable because it looks indistinguishable from a valid but quite different dataset. (No, the 1 row doesn't make it better; as soon as there's more than one row you can't tell what you have. And leaving out the first | doesn't help if all the columns are multiline.) It might be OK without any separators on the added lines, though: id |name| address | del_addr ++---+-- 1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels, Newport,Swinkley, Gwent XX3 5CX (1 row) Or perhaps use a different separator: junk=# select * from xyz; id |name| address | del_addr ++---+-- 1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels, ++ Newport, + Swinkley, ++ Gwent + XX3 5CX (1 row) Not sure how hard this would be to program, or what sort of overhead it might impose to check for the case. My recollection is that psql's table-layout code is pretty slow and ugly already ... 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] Stuff running slooow
On Thu, Aug 25, 2005 at 09:58:21AM +0200, Magnus Hagander wrote: Well, if hardware or bandwidth becomes an issue I suspect we could easily get donations to improve things. IIRC we have plenty of spare both hardware and bandwidth on the box donated by Pervasive. But it runs Linux so you can't just move freebsd VMs across, which is why it's only used as a web frontent and to run planetpostgresql.org at the moment. If the OS it's running is a legitimate issue I can work on changing that. By VMs do you mean jails? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stuff running slooow
On Thu, Aug 25, 2005 at 09:58:21AM +0200, Magnus Hagander wrote: Well, if hardware or bandwidth becomes an issue I suspect we could easily get donations to improve things. IIRC we have plenty of spare both hardware and bandwidth on the box donated by Pervasive. But it runs Linux so you can't just move freebsd VMs across, which is why it's only used as a web frontent and to run planetpostgresql.org at the moment. Sorry, itchy send-button... For sending email, there are packages that allow a remote machine to handle email sending duties. This would allow off-loading SMTP duties from the box running the mailman web stuff. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stuff running slooow
-Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: Thursday, August 25, 2005 1:31 PM To: Jim Nasby Cc: Marc G. Fournier; pgsql-hackers@postgresql.org Subject: RE: [HACKERS] Stuff running slooow Well, if hardware or bandwidth becomes an issue I suspect we could easily get donations to improve things. IIRC we have plenty of spare both hardware and bandwidth on the box donated by Pervasive. But it runs Linux so you can't just move freebsd VMs across, which is why it's only used as a web frontent and to run planetpostgresql.org at the moment. Sorry, itchy send-button... For sending email, there are packages that allow a remote machine to handle email sending duties. This would allow off-loading SMTP duties from the box running the mailman web stuff. I beleive this is already done. For example, your mail came to me through flake.decibel.org. //Magnus Which is my machine at home, which is what I generally use for list traffic. The actual mail-chain for list traffic is svr1.postgresql.org to svr2. Maybe svr2 is handling the bulk emailing already. The delay seems to be on svr1 in any case. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stuff running slooow
Well, if hardware or bandwidth becomes an issue I suspect we could easily get donations to improve things. IIRC we have plenty of spare both hardware and bandwidth on the box donated by Pervasive. But it runs Linux so you can't just move freebsd VMs across, which is why it's only used as a web frontent and to run planetpostgresql.org at the moment. Sorry, itchy send-button... For sending email, there are packages that allow a remote machine to handle email sending duties. This would allow off-loading SMTP duties from the box running the mailman web stuff. I beleive this is already done. For example, your mail came to me through flake.decibel.org. Eh. That would be me looking at the mail that didn't pass the listserver :-) Picking one that does, thouhg, my mails typicall pass through a box at commandprompt.com, so the argument holds while the example was broken. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stuff running slooow
Well, if hardware or bandwidth becomes an issue I suspect we could easily get donations to improve things. IIRC we have plenty of spare both hardware and bandwidth on the box donated by Pervasive. But it runs Linux so you can't just move freebsd VMs across, which is why it's only used as a web frontent and to run planetpostgresql.org at the moment. Sorry, itchy send-button... For sending email, there are packages that allow a remote machine to handle email sending duties. This would allow off-loading SMTP duties from the box running the mailman web stuff. I beleive this is already done. For example, your mail came to me through flake.decibel.org. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO list comments
Tom Lane wrote: Or perhaps use a different separator: junk=# select * from xyz; id |name| address | del_addr ++---+-- 1 | Joe Bloggs | 1 Hindhead Villas,| 2 The Laurels, ++ Newport, + Swinkley, ++ Gwent + XX3 5CX (1 row) That's a terrific idea, and, incidentally, just the sort of project that might well suit a beginning hacker, since the code is pretty isolated. Not sure how hard this would be to program, or what sort of overhead it might impose to check for the case. My recollection is that psql's table-layout code is pretty slow and ugly already ... If people want speed they shouldn't use psql as a client anyway. I don't see this as much of an obstacle. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO list comments
On Wed, Aug 24, 2005 at 09:58:04PM -0400, Tom Lane wrote: * %Allow RULE recompilation Eh? Perhaps you meant automatically regenerate cached plans when needed, in which case it's redundant with the Dependency Checking entries. Whatever it means, this doesn't seem a particularly simple item. Hrm... I read that as allowing CREATE OR REPLACE on rules, but of course that already exists. http://lnk.nu/search.postgresql.org/3mt.search * %Allow TRUNCATE ... CASCADE/RESTRICT Huh? What would that do? http://archives.postgresql.org/pgsql-hackers/2003-08/msg01045.php o %Add ALTER DOMAIN TYPE To do what, exactly? This is unclear. http://archives.postgresql.org/pgsql-hackers/2004-05/msg00985.php o Remove unnecessary abstractions in pg_dump source code Like which? I *think* this is reffering to how pg_dump makes some assumptions about what things are system objects. http://archives.postgresql.org/pgsql-committers/2005-08/msg00203.php doesn't help a heck of a lot... Can we add an interface to the TODO list that contains search links back to the mailing lists? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stuff running slooow
-Original Message- From: Magnus Hagander[EMAIL PROTECTED] Sent: 25/08/05 19:36:51 To: Jim C. Nasby[EMAIL PROTECTED] Cc: Marc G. Fournier[EMAIL PROTECTED], pgsql-hackers@postgresql.orgpgsql-hackers@postgresql.org Subject: Re: [HACKERS] Stuff running slooow Picking one that does, thouhg, my mails typicall pass through a box at commandprompt.com, so the argument holds while the example was broken. There are a few distribution servers, another of which is the Pervasive box. /D -Unmodified Original Message- Well, if hardware or bandwidth becomes an issue I suspect we could easily get donations to improve things. IIRC we have plenty of spare both hardware and bandwidth on the box donated by Pervasive. But it runs Linux so you can't just move freebsd VMs across, which is why it's only used as a web frontent and to run planetpostgresql.org at the moment. Sorry, itchy send-button... For sending email, there are packages that allow a remote machine to handle email sending duties. This would allow off-loading SMTP duties from the box running the mailman web stuff. I beleive this is already done. For example, your mail came to me through flake.decibel.org. Eh. That would be me looking at the mail that didn't pass the listserver :-) Picking one that does, thouhg, my mails typicall pass through a box at commandprompt.com, so the argument holds while the example was broken. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Stuff running slooow
Eh. That would be me looking at the mail that didn't pass the listserver :-) Picking one that does, thouhg, my mails typicall pass through a box at commandprompt.com, so the argument holds while the example was broken. Well one thing I can tell you is that it definately appears as if the mailing lists need help. It seems to take a LONG time for a post to show up for any particular person, when it should be almost instantaenous. I know the problem is not on our end as I am watching messages being relayed faster than I can read them but somewhere there is a bottleneck. I have mentioned previously that I would really like to see the email lists move to mailman (long list of reasons why but I don't want to start a war). The long and short is I have never understood why it takes so long for posts to show up. Sincerely, Joshua D. Drake //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Win32 Thread safetyness
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 25 August 2005 16:35 To: Magnus Hagander Cc: Dave Page; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Win32 Thread safetyness Magnus Hagander [EMAIL PROTECTED] writes: Yuck. This sucks :-( I was very much hoping we could avoid an other build *and* runtime dependency. Which will be a cascading runtime dependency to each and every program that uses libpq. double-:-( That seems like a clear nonstarter :-( Can we confine the damage to stuff that uses ecpg, rather than adding a dependency to everything that uses libpq? Yeah, that could be done with a little makefile hacking - we can use our emulation for libpq and normal pthreads for ecpglib. However, I'm still unsure how to handle the problem I posted about. From what Andrew Supernews has posted about pthread_t not being guaranteed to be an int, the appropriate fix would be to stop casting it to long. I haven't looked at the implications of that yet though - any thoughts before I do? Regards, Dave. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stuff running slooow
On Thu, Aug 25, 2005 at 09:07:32PM +0100, Dave Page wrote: -Original Message- From: Magnus Hagander[EMAIL PROTECTED] Sent: 25/08/05 19:36:51 To: Jim C. Nasby[EMAIL PROTECTED] Cc: Marc G. Fournier[EMAIL PROTECTED], pgsql-hackers@postgresql.orgpgsql-hackers@postgresql.org Subject: Re: [HACKERS] Stuff running slooow Picking one that does, thouhg, my mails typicall pass through a box at commandprompt.com, so the argument holds while the example was broken. There are a few distribution servers, another of which is the Pervasive box. So I take it the bottleneck is the box running the mailing list? BTW, things are back to normal today... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Stuff running slooow
-Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: 25 August 2005 21:24 To: Dave Page Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Stuff running slooow So I take it the bottleneck is the box running the mailing list? Usually that, or av.hub.org which does the centralised anti virus/anti spam (iirc). Regards, Dave. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stuff running slooow
On Thu, Aug 25, 2005 at 09:26:25PM +0100, Dave Page wrote: So I take it the bottleneck is the box running the mailing list? Usually that, or av.hub.org which does the centralised anti virus/anti spam (iirc). Does it scan every single incomming email? It might make more sense to have the mailing list software first validate that the email is from a valid subscriber. What exactly runs on svr1, since that's where the bottleneck was yesterday? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stuff running slooow
On Thu, 25 Aug 2005, Jim C. Nasby wrote: On Thu, Aug 25, 2005 at 09:07:32PM +0100, Dave Page wrote: -Original Message- From: Magnus Hagander[EMAIL PROTECTED] Sent: 25/08/05 19:36:51 To: Jim C. Nasby[EMAIL PROTECTED] Cc: Marc G. Fournier[EMAIL PROTECTED], pgsql-hackers@postgresql.orgpgsql-hackers@postgresql.org Subject: Re: [HACKERS] Stuff running slooow Picking one that does, thouhg, my mails typicall pass through a box at commandprompt.com, so the argument holds while the example was broken. There are a few distribution servers, another of which is the Pervasive box. So I take it the bottleneck is the box running the mailing list? BTW, things are back to normal today... Yup, did a bunch of work on it last night ... identified some 'out of whack' processes that were hogging a bit more CPU then they should, and moved them ... its part of some ongoing work I've been doing to clean things up ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stuff running slooow
On Thu, 25 Aug 2005, Joshua D. Drake wrote: The long and short is I have never understood why it takes so long for posts to show up. I'm looking into that one right now ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stuff running slooow
-Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: 25 August 2005 21:46 To: Dave Page Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Stuff running slooow On Thu, Aug 25, 2005 at 09:26:25PM +0100, Dave Page wrote: So I take it the bottleneck is the box running the mailing list? Usually that, or av.hub.org which does the centralised anti virus/anti spam (iirc). Does it scan every single incomming email? It might make more sense to have the mailing list software first validate that the email is from a valid subscriber. What exactly runs on svr1, since that's where the bottleneck was yesterday? Marc can best answer those. Svr1 is not just the listserver though - it's also developer.postgresql.org which releases and snapshots are built on, developers keep their websites (and may also use for general use) and the primary ftp site is built on. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stuff running slooow
On Thu, 25 Aug 2005, Jim C. Nasby wrote: On Thu, Aug 25, 2005 at 09:26:25PM +0100, Dave Page wrote: So I take it the bottleneck is the box running the mailing list? Usually that, or av.hub.org which does the centralised anti virus/anti spam (iirc). Does it scan every single incomming email? It might make more sense to have the mailing list software first validate that the email is from a valid subscriber. I have just about every anti- filter that I can find enabled on postfix itself, but its postfix itself that does the content filtering, not the mailing list software ... so thet anti-virus is hit before the mailing list software even sees it ... we also have spamassassin running, and filters in place to reject any messages that trigger that ... As a couple of ppl have found out by becoming 'moderators' for the mailing lists, there are *alot* of messages through the server that aren't list subscribers, but are legit emails ... The process for email right now is: scan for virus - analyze/tag for spam - pass to majordomo majordomo then checks the spam tags and disregards based on a set of rules, and what is left either has to wait for moderator approval/reject *or* send onto the list, depending on if someone is subscribed or not ... If y'all would like, I can eliminate the anti-virus/anti-spam checks and just let it all go through though ... *evil grin* Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pg_dump fails to set index ownership
Regarding the removal of ALTER INDEX OWNER commands from pg_dump, indexes are now restored with the wrong ownership if the user doing the restore is different than the user who owned the original index (if this sounds familiar, I reported the same problem for 8.0.0rc4 in January). ALTER INDEX OWNER no longer works, and ALTER TABLE OWNER won't change the index ownership if the table ownership doesn't actually change (i.e., nothing happens if the new owner and the old owner are the same). Should CREATE INDEX automatically set index ownership to be the same as the table ownership? Or did I miss past discussion about that? Seems like this ought to be fixed before beta1 is announced so it doesn't bite people who are trying 8.1 for the first time. postgres=# CREATE ROLE test LOGIN PASSWORD 'test'; CREATE ROLE postgres=# CREATE DATABASE test1; CREATE DATABASE postgres=# CREATE DATABASE test2; CREATE DATABASE postgres=# \c test1 test Password for user test: You are now connected to database test1 as user test. test1= CREATE TABLE foo (id serial PRIMARY KEY, val text); NOTICE: CREATE TABLE will create implicit sequence foo_id_seq for serial column foo.id NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE test1= CREATE INDEX foo_val_idx ON foo (val); CREATE INDEX test1= \q % pg_dump -U postgres test1 | psql -U postgres test2 SET SET SET COMMENT SET SET SET CREATE TABLE ALTER TABLE setval 1 (1 row) ALTER TABLE CREATE INDEX REVOKE REVOKE GRANT GRANT % psql -q -U test test2 Password for user test: test2= \d List of relations Schema |Name| Type | Owner ++--+--- public | foo| table| test public | foo_id_seq | sequence | test (2 rows) test2= \di List of relations Schema |Name | Type | Owner | Table +-+---+--+--- public | foo_pkey| index | postgres | foo public | foo_val_idx | index | postgres | foo (2 rows) test2= DROP INDEX foo_val_idx; ERROR: must be owner of relation foo_val_idx test2= \c test2 postgres Password for user postgres: You are now connected to database test2 as user postgres. test2=# ALTER INDEX foo_val_idx OWNER TO test; WARNING: cannot change owner of index foo_val_idx HINT: Change the ownership of the index's table, instead. ALTER INDEX test2=# ALTER TABLE foo OWNER TO test; ALTER TABLE test2=# \di List of relations Schema |Name | Type | Owner | Table +-+---+--+--- public | foo_pkey| index | postgres | foo public | foo_val_idx | index | postgres | foo (2 rows) test2=# ALTER TABLE foo OWNER TO postgres; ALTER TABLE test2=# ALTER TABLE foo OWNER TO test; ALTER TABLE test2=# \di List of relations Schema |Name | Type | Owner | Table +-+---+---+--- public | foo_pkey| index | test | foo public | foo_val_idx | index | test | foo (2 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: VACUUM/t_ctid bug (was Re: [HACKERS] GiST concurrency commited)
Teodor Sigaev [EMAIL PROTECTED] writes:
http://www.sigaev.ru/gist/concur.pl
http://www.sigaev.ru/gist/concur.sh
BTW, these scripts seem to indicate that there's a GIST or
contrib/intarray problem in the 8.0 branch. I was trying to use 'em
to test REL8_0_STABLE branch tip to verify my t_ctid chain backpatch,
and I pretty consistently see Problem with update:
Start: parallel mode with 4 flows
Problem with update {77,77}:0 count:1 at concur.pl line 91.
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect().
Problem with update {43,24}:3 count:1 at concur.pl line 91.
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect().
Problem with update {43,43}:2 count:1 at concur.pl line 91.
Issuing rollback() for database handle being DESTROY'd without explicit
disconnect().
1 flow finish. Stats: ni:75000 nu:1661 nd:216 nv:13(nf:3) nt:780
All flow finish; status: 255; elapsed time: 265.48 sec
Is this something that can be fixed for 8.0.4?
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Stuff running slooow
If y'all would like, I can eliminate the anti-virus/anti-spam checks and just let it all go through though ... *evil grin* Would not bother me in the least. I have protective measures as I am sure most others do as well. :) Sincerely, Joshua D. Drake Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stuff running slooow
Marc G. Fournier wrote: As a couple of ppl have found out by becoming 'moderators' for the mailing lists, there are *alot* of messages through the server that aren't list subscribers, but are legit emails ... Perhaps that shouldn't be allowed? Would it help things if all non-subscriber emails are just bounced / dropped immediately, before anti-virus etc...Seems this would save a lot of CPU time and more importantly people time reviewing potentially legit emails. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stuff running slooow
On Thu, 25 Aug 2005, Matthew T. O'Connor wrote: Marc G. Fournier wrote: As a couple of ppl have found out by becoming 'moderators' for the mailing lists, there are *alot* of messages through the server that aren't list subscribers, but are legit emails ... Perhaps that shouldn't be allowed? Would it help things if all non-subscriber emails are just bounced / dropped immediately, before anti-virus etc...Seems this would save a lot of CPU time and more importantly people time reviewing potentially legit emails. Not sure how you would accomplish this, since postfix is what handles the anti-virus/anti-spam processing, before the list software even sees it ... and, since not all mail through that system is for the lists, you can't just drop all messages not from subscribers ... the issues, though, aren't with the anti-virus/anti-spam ... somehow, the messages seem to be getting 'stuck' within the list software itself, which is what I'm trying to trace through ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stuff running slooow
On Thu, 25 Aug 2005, Joshua D. Drake wrote: If y'all would like, I can eliminate the anti-virus/anti-spam checks and just let it all go through though ... *evil grin* Would not bother me in the least. I have protective measures as I am sure most others do as well. :) Remembering back to the time I *oopsed* and approved all messages in the moderator queue, and the # of ppl emailing me about getting a whack of spam, I don't imagine everyone has such in place :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_dump fails to set index ownership
Michael Fuhr [EMAIL PROTECTED] writes: Regarding the removal of ALTER INDEX OWNER commands from pg_dump, indexes are now restored with the wrong ownership if the user doing the restore is different than the user who owned the original index pg_dump is not the source of the problem. We should instead arrange that an index's relowner value is copied directly from the parent table during CREATE INDEX. This is probably more important now with roles, since GetUserId() might not have a lot to do with the table's owner ID. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stuff running slooow
Marc G. Fournier wrote: On Thu, 25 Aug 2005, Joshua D. Drake wrote: If y'all would like, I can eliminate the anti-virus/anti-spam checks and just let it all go through though ... *evil grin* Would not bother me in the least. I have protective measures as I am sure most others do as well. :) Remembering back to the time I *oopsed* and approved all messages in the moderator queue, and the # of ppl emailing me about getting a whack of spam, I don't imagine everyone has such in place :) O.k. that is probably true, but Matt had a good suggestion. If you are not subscribed it immediately bounces. I think that is a very good idea. It would take some load off of the system and the moderaters. Sincerely, Joshua D. Drake Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO list comments
On Aug 25, 2005, at 11:29 PM, Matt Miller wrote: On Thu, 2005-08-25 at 15:50 +0900, Michael Glaesemann wrote: * %Remove CREATE CONSTRAINT TRIGGER Do we really want to remove it, Also, I believe CONSTRAINT TRIGGERS are the only way to provide transaction level (rather than statement level) referential integrity. Don't deferrable foreign keys give you transaction-level referential integrity? From the SET CONSTRAINTS doc: Sorry, I misspoke. What I'm thinking of is not referential integrity in the sense of foreign keys, but assertions, which PostgreSQL does not yet support. Say for example you have a table that contains time periods marked by a start_date and an end_date and you want there to be no gaps between the different time periods in the table for a given key. When doing updates, deletes, or inserts on this table, you'll need to check to make sure there are no gaps when the transaction is finished. However, there may indeed be gaps during the transaction as start_dates and end_dates are updated. Triggers can be written to enforce this kind of integrity, but they'll only work if they're deferrable. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Stuff running slooow
On Thu, Aug 25, 2005 at 15:01:25 -0700, Joshua D. Drake [EMAIL PROTECTED] wrote: O.k. that is probably true, but Matt had a good suggestion. If you are not subscribed it immediately bounces. I think that is a very good idea. It would take some load off of the system and the moderaters. That makes the lists less usable for people asking questions. Are messages from usenet still being gated to the lists? If so that will also be affected by such a change. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Stuff running slooow
Dave Page dpage@vale-housing.co.uk writes: So I take it the bottleneck is the box running the mailing list? Usually that, or av.hub.org which does the centralised anti virus/anti spam (iirc). Yesterday's problem seemed to be av.hub.org; svr1 was pretty nearly idle as far as I could tell. I don't have a login on av to see what conditions were like there, though. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stuff running slooow
On Thu, 25 Aug 2005, Bruno Wolff III wrote: On Thu, Aug 25, 2005 at 15:01:25 -0700, Joshua D. Drake [EMAIL PROTECTED] wrote: O.k. that is probably true, but Matt had a good suggestion. If you are not subscribed it immediately bounces. I think that is a very good idea. It would take some load off of the system and the moderaters. That makes the lists less usable for people asking questions. Are messages from usenet still being gated to the lists? If so that will also be affected by such a change. Two reasons why 'auto-bouncing' won't work ... 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] Stuff running slooow
On Thu, Aug 25, 2005 at 06:01:23PM -0300, Marc G. Fournier wrote: Yup, did a bunch of work on it last night ... identified some 'out of whack' processes that were hogging a bit more CPU then they should, and moved them ... its part of some ongoing work I've been doing to clean things up ... Hrm... the web interface at mail.postgresql.org is pretty slow right now... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Call for 7.5 feature completion
Bruce, on May 17, 2004, you wrote: So, yea, I am frustrated. I know these features are hard and complex, but I want them for PostgreSQL, and I want them as soon as possible. I guess what really bugs me is that we are so close to having these few remaining big features, and because they are so complex, they are taking a lot longer to arrive than previous features, and sometimes see a year pass without progress on some items, and that bugs me. This discussion was taking place as we closed the 7.5 development cycle, and we weren't getting PITR, tablespaces, nested transactions, 2PC, the Win32 port, in the release. We have all those things now. We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? Or, slightly different, what are people's most wanted features? Has PostgreSQL started slowing down in getting new features, and concentrating mostly on performance issues? -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) A male gynecologist is like an auto mechanic who never owned a car. (Carrie Snow) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Call for 7.5 feature completion
We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? Table partitioning is pretty big but I believe we have that already for 8.2 per Greenplum. Better aggregate performance Real materialized views Anything that goes zoom, zoom, zoom of course! Not to mention the WHOLE todo list :) Sincerely, Joshua D. Drake Or, slightly different, what are people's most wanted features? Has PostgreSQL started slowing down in getting new features, and concentrating mostly on performance issues? -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
On Thu, 25 Aug 2005, Alvaro Herrera wrote: Bruce, on May 17, 2004, you wrote: So, yea, I am frustrated. I know these features are hard and complex, but I want them for PostgreSQL, and I want them as soon as possible. I guess what really bugs me is that we are so close to having these few remaining big features, and because they are so complex, they are taking a lot longer to arrive than previous features, and sometimes see a year pass without progress on some items, and that bugs me. This discussion was taking place as we closed the 7.5 development cycle, and we weren't getting PITR, tablespaces, nested transactions, 2PC, the Win32 port, in the release. We have all those things now. We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? Or, slightly different, what are people's most wanted features? SQL: Grouping sets Recursive queries Window functions Updatable views Updatable cursors Materialised views Debug-able PL/PgSQL -- EXPLAIN [ANALYZE] functionality, step through? Cost estimation for functions -- perhaps a pipe dream, I know Performance: Better bulk load 'Continuous' vacuum at a fraction of the IO cost of normal vacuum Multimaster replication General OLTP throughput improvements -- where and how, I'm not sure. Indexes: Bitmap indexes (as opposed to bitmap scans) --- There are other things which would be cool to see, but these are high on the list. Thanks, Gavin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
On 8/25/05 4:13 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? Or, slightly different, what are people's most wanted features? Table partitioning would be huge, but it looks like that is on its way. Index-organized tables are the most significant thing at the top of my wish list, as that would generate a huge performance increase for much of what I spend my time on with PostgreSQL. There is currently no good way to approximate it. J. Andrew Rogers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Stuff running slooow
On Thu, Aug 25, 2005 at 02:45:02PM -0700, Joshua D. Drake wrote: If y'all would like, I can eliminate the anti-virus/anti-spam checks and just let it all go through though ... *evil grin* Would not bother me in the least. I have protective measures as I am sure most others do as well. :) The archives would fill with junk. From skimming other projects' archives, IMHO that is very undesirable. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) The important things in the world are problems with society that we don't understand at all. The machines will become more complicated but they won't be more complicated than the societies that run them.(Freeman Dyson) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for 7.5 feature completion
Alvaro, We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? -- on-disk bitmaps and composite indexes (due out for Bizgres in about a month) -- More table partitioning stuff -- materialized view support -- streams (per TelegraphCQ) -- database ASSERTIONS -- clustering (SlonyII) -- multi-threaded/process query execution (i.e. one query, multiple processors) -- more robust message queing -- SQL99 TYPES (aka Packages) -- Recursive Joins -- MERGE -- interactive/automated database performance tuning -- index-only access -- automated creation of updatable views Has PostgreSQL started slowing down in getting new features, and concentrating mostly on performance issues? Hmmm, I don't think so. We will eventually, but there's still plenty of features left. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Call for 7.5 feature completion
Gavin Sherry wrote: Or, slightly different, what are people's most wanted features? SQL: Grouping sets Recursive queries Window functions Updatable views Updatable cursors Materialised views Debug-able PL/PgSQL -- EXPLAIN [ANALYZE] functionality, step through? Cost estimation for functions -- perhaps a pipe dream, I know and Merge (which I know Gavin also wants). cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for 7.5 feature completion
Alvaro, -- on-disk bitmaps and composite indexes (due out for Bizgres in about a month) -- More table partitioning stuff -- materialized view support -- streams (per TelegraphCQ) -- database ASSERTIONS -- clustering (SlonyII) -- multi-threaded/process query execution (i.e. one query, multiple processors) -- more robust message queing -- SQL99 TYPES (aka Packages) -- Recursive Joins -- MERGE -- interactive/automated database performance tuning -- index-only access -- automated creation of updatable views Oh, yeah I forgot: -- windowing functions (e.g. RANK, RANK OVER, LAST 10) -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Call for 7.5 feature completion
On Thu, 2005-08-25 at 19:13 -0400, Alvaro Herrera wrote: Bruce, on May 17, 2004, you wrote: So, yea, I am frustrated. I know these features are hard and complex, but I want them for PostgreSQL, and I want them as soon as possible. I guess what really bugs me is that we are so close to having these few remaining big features, and because they are so complex, they are taking a lot longer to arrive than previous features, and sometimes see a year pass without progress on some items, and that bugs me. This discussion was taking place as we closed the 7.5 development cycle, and we weren't getting PITR, tablespaces, nested transactions, 2PC, the Win32 port, in the release. We have all those things now. We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? Or, slightly different, what are people's most wanted features? I have an immediate use for: * Identity/generator support (per standard) * Merge (update/insert as required) * Multi-CPU sorts. Take a large single sort like an index creation and split the work among multiple CPUs. -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for 7.5 feature completion
Rod Taylor wrote: * Multi-CPU sorts. Take a large single sort like an index creation and split the work among multiple CPUs. This really implies threading, doesn't it? And presumably it would have many possible uses besides this one for doing parallel work, e.g. maybe the planner could evaluate several alternative plans in parallel. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Call for 7.5 feature completion
We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? Or, slightly different, what are people's most wanted features? Oh, and MERGE :D Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Call for 7.5 feature completion
On Thu, 2005-08-25 at 21:27 -0400, Andrew Dunstan wrote: Rod Taylor wrote: * Multi-CPU sorts. Take a large single sort like an index creation and split the work among multiple CPUs. This really implies threading, doesn't it? And presumably it would have many possible uses besides this one for doing parallel work, e.g. maybe the planner could evaluate several alternative plans in parallel. I don't think threading is needed. I pictured PostgreSQL spawning one process per CPU explicitly for sorting which standard backends could use as required to do batch work. Not necessarily easy to do but it would sure be handy. -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? Or, slightly different, what are people's most wanted features? * Recursive unions (ie. WITH recursive) * CUBE ROLLUP * The rest of the oracle analytic functions (http://www.akadia.com/services/ora_analytic_functions.html) I'm happy with new features and performance, but I'd really like to be able to do complex analysis of the huge statistics tables I have :) Chris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Call for 7.5 feature completion
Rod Taylor wrote: On Thu, 2005-08-25 at 19:13 -0400, Alvaro Herrera wrote: Bruce, on May 17, 2004, you wrote: So, yea, I am frustrated. I know these features are hard and complex, but I want them for PostgreSQL, and I want them as soon as possible. I guess what really bugs me is that we are so close to having these few remaining big features, and because they are so complex, they are taking a lot longer to arrive than previous features, and sometimes see a year pass without progress on some items, and that bugs me. This discussion was taking place as we closed the 7.5 development cycle, and we weren't getting PITR, tablespaces, nested transactions, 2PC, the Win32 port, in the release. We have all those things now. We have gone a long way now, even though it was only a year ago. My question for everyone on this list is: What are the few remaining big features that you see missing for PostgreSQL? Or, slightly different, what are people's most wanted features? I have an immediate use for: * Identity/generator support (per standard) * Merge (update/insert as required) * Multi-CPU sorts. Take a large single sort like an index creation and split the work among multiple CPUs. I am just a novice end user, but I would like to see: SavePoints be able to use within functions. ( I think this involves making procedures that execute outside of a transaction) Cross Database references. (Available through dblink, but it would be better if it was supported natively) The ability to say create function foo () returns setof record(int, int , int). I believe this is coming in the next release though. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
Rod Taylor wrote: On Thu, 2005-08-25 at 21:27 -0400, Andrew Dunstan wrote: Rod Taylor wrote: * Multi-CPU sorts. Take a large single sort like an index creation and split the work among multiple CPUs. This really implies threading, doesn't it? And presumably it would have many possible uses besides this one for doing parallel work, e.g. maybe the planner could evaluate several alternative plans in parallel. I don't think threading is needed. I pictured PostgreSQL spawning one process per CPU explicitly for sorting which standard backends could use as required to do batch work. This is one area where PostgreSQL needs a lot of work to catch up to the competition. Oracle, DB2, Ingres, even SQL Server Enterprise edition all have parallel query capabilities. I have an older 8-processor Sun Enterprise 3500, as an example. It still has use with other vendors' database products due to their parallel feature set (make -j 9 is nice too), but behaves like the boat-anchor it is w.r.t. PostgreSQL. Mike Mascari ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for 7.5 feature completion
Nicholas, You are a novice user, aren't you? ;-) I am just a novice end user, but I would like to see: SavePoints be able to use within functions. ( I think this involves making procedures that execute outside of a transaction) Nope, supported in 8.0 for PL/pgSQL. Not sure about other languages. Cross Database references. (Available through dblink, but it would be better if it was supported natively) You'll have to argue this one. We don't have them on purpose. Generally when people want cross-database queries the problem is that they really should be using schema. The ability to say create function foo () returns setof record(int, int , int). I believe this is coming in the next release though. Well, we've had this for 3 versions as CREATE TYPE ... CREATE FUNCTION. INOUT parameters in 8.1 will give you the above, effectively. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page
Congrats Dave! On Aug 25, 2005, at 5:59 PM, Josh Berkus wrote: Project members: On behalf of the PostgreSQL Core Team, I welcome Dave Page. Dave has been the head of the pgODBC project for a couple of years, started the pgAdmin project in 1998, has been our lead webmaster for three years, and is now (split with Magnus) in charge of the Win32 packaging. In these latter two roles, Dave's help is indispensable to the PostgreSQL release process, so we decided it was time for him to have the title for the work he's already doing. We believe that anyone who's been around the project for a few years will agree. So, welcome Dave Page as the newest member of Core! -- --Josh Berkus Josh Berkus PostgreSQL Project Core Team www.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Gavin M. Roy 800 Pound Gorilla [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stuff running slooow
I've forwarded this onto the Mj2 Developers ... it might even be doable now, they've built a, at times, painfully configurable system ... On Fri, 26 Aug 2005, Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: O.k. that is probably true, but Matt had a good suggestion. If you are not subscribed it immediately bounces. I think that is a very good idea. It would take some load off of the system and the moderaters. That won't do, as some other folks noted. But what I'd really like to see is a hack that, when someone subscribes to a list, goes through the moderator queue and auto-approves any pending messages from that someone. What we see way way too much of is the following sequence of events: 1. Newbie sends a question to a list. 2. Question goes into the moderator's queue because it's from a non subscriber. 3. Newbie figures this out (maybe right away or maybe not), subscribes, and reposts his question. 4. People answer. 5. Some while later (usually several days, which means that Marc is badly overworked :-(), the original question gets approved and we see a duplicate appearing on the list. There is nothing optimal about this from the point of view of the newbie, nor the moderator, nor the list membership (who have to be able to recognize delayed duplicate questions when they see 'em). Plus it clutters the archives. An auto-approval mechanism would fix all this (though we'd probably need to add something to the standard list-welcome message mentioning that you shouldn't repeat any questions you already sent in). I have no idea how hard it is to do, but it sure seems like it would make things more pleasant all around. regards, tom lane Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Stuff running slooow
Joshua D. Drake [EMAIL PROTECTED] writes: O.k. that is probably true, but Matt had a good suggestion. If you are not subscribed it immediately bounces. I think that is a very good idea. It would take some load off of the system and the moderaters. That won't do, as some other folks noted. But what I'd really like to see is a hack that, when someone subscribes to a list, goes through the moderator queue and auto-approves any pending messages from that someone. What we see way way too much of is the following sequence of events: 1. Newbie sends a question to a list. 2. Question goes into the moderator's queue because it's from a non subscriber. 3. Newbie figures this out (maybe right away or maybe not), subscribes, and reposts his question. 4. People answer. 5. Some while later (usually several days, which means that Marc is badly overworked :-(), the original question gets approved and we see a duplicate appearing on the list. There is nothing optimal about this from the point of view of the newbie, nor the moderator, nor the list membership (who have to be able to recognize delayed duplicate questions when they see 'em). Plus it clutters the archives. An auto-approval mechanism would fix all this (though we'd probably need to add something to the standard list-welcome message mentioning that you shouldn't repeat any questions you already sent in). I have no idea how hard it is to do, but it sure seems like it would make things more pleasant all around. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
