Re: [HACKERS] Call for 7.5 feature completion
On Aug 27, 2005, at 2:45 AM, Heikki Linnakangas wrote:
* support for Tutorial D as an alternative to SQL. It would be
great for educational purposes.
++
I'd also like to see temporal/interval/period support a la Date/
Darwen/Lorentzos ("Temporal Data and the Relational Model").
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] EXPLAIN with view: bogus varno: 5
I'm not sure it's relevant to Michaels' case, but I see message (8.1dev)
ERROR: bogus varno: 2
tp=# explain select name_qualified from place, to_tsquery('moscow') as query
where fts_index @@ query;
ERROR: bogus varno: 2
In my case, this select produces core dump while being rewritten works fine.
tp=# select name_qualified from place
where fts_index @@ to_tsquery('moscow');
Oleg
On Fri, 26 Aug 2005, Michael Fuhr wrote:
Running EXPLAIN on a view that has an aggregate and uses an index
results in the error "bogus varno: 5". At least I think the aggregate
and index are necessary -- removing either from the following example
allows EXPLAIN to succeed:
test=> CREATE TABLE foo (x integer);
CREATE TABLE
test=> CREATE INDEX foo_x_idx ON foo (x);
CREATE INDEX
test=> CREATE VIEW fooview1 AS SELECT count(*) FROM foo WHERE x < 10;
CREATE VIEW
test=> CREATE VIEW fooview2 AS SELECT * FROM foo WHERE x < 10;
CREATE VIEW
test=> CREATE VIEW fooview3 AS SELECT count(*) FROM foo;
CREATE VIEW
test=> CREATE VIEW fooview4 AS SELECT * FROM foo;
CREATE VIEW
test=> \set VERBOSITY verbose
test=> EXPLAIN SELECT * FROM fooview1;
ERROR: XX000: bogus varno: 5
LOCATION: get_rte_for_var, ruleutils.c:2478
test=> EXPLAIN SELECT * FROM fooview2;
QUERY PLAN
--
Bitmap Heap Scan on foo (cost=3.50..22.41 rows=713 width=4)
Recheck Cond: (x < 10)
-> Bitmap Index Scan on foo_x_idx (cost=0.00..3.50 rows=713 width=0)
Index Cond: (x < 10)
(4 rows)
test=> EXPLAIN SELECT * FROM fooview3;
QUERY PLAN
-
Aggregate (cost=36.75..36.75 rows=1 width=0)
-> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=0)
(2 rows)
test=> EXPLAIN SELECT * FROM fooview4;
QUERY PLAN
---
Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4)
(1 row)
test=> DROP INDEX foo_x_idx;
DROP INDEX
test=> EXPLAIN SELECT * FROM fooview1;
QUERY PLAN
Aggregate (cost=38.53..38.53 rows=1 width=0)
-> Seq Scan on foo (cost=0.00..36.75 rows=713 width=0)
Filter: (x < 10)
(3 rows)
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 6: explain analyze is your friend
Re: [HACKERS] Call for 7.5 feature completion
Tom Lane wrote: > Bruce Momjian writes: > > Ron Mayer wrote: > >> * more sane math with intervals. For example, try: > >> select '0.01 years'::interval, '0.01 months'::interval; > > > Added to TODO: > > > Fix SELECT '0.01 years'::interval, '0.01 months'::interval; > > Arguably, both of those things should be rejected as errors. > What is a fraction of a year? Or a month? What does the standard say we should do with these? Isn't this like interval division, which does work? -- 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 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] EXPLAIN with view: bogus varno: 5
Running EXPLAIN on a view that has an aggregate and uses an index results in the error "bogus varno: 5". At least I think the aggregate and index are necessary -- removing either from the following example allows EXPLAIN to succeed: test=> CREATE TABLE foo (x integer); CREATE TABLE test=> CREATE INDEX foo_x_idx ON foo (x); CREATE INDEX test=> CREATE VIEW fooview1 AS SELECT count(*) FROM foo WHERE x < 10; CREATE VIEW test=> CREATE VIEW fooview2 AS SELECT * FROM foo WHERE x < 10; CREATE VIEW test=> CREATE VIEW fooview3 AS SELECT count(*) FROM foo; CREATE VIEW test=> CREATE VIEW fooview4 AS SELECT * FROM foo; CREATE VIEW test=> \set VERBOSITY verbose test=> EXPLAIN SELECT * FROM fooview1; ERROR: XX000: bogus varno: 5 LOCATION: get_rte_for_var, ruleutils.c:2478 test=> EXPLAIN SELECT * FROM fooview2; QUERY PLAN -- Bitmap Heap Scan on foo (cost=3.50..22.41 rows=713 width=4) Recheck Cond: (x < 10) -> Bitmap Index Scan on foo_x_idx (cost=0.00..3.50 rows=713 width=0) Index Cond: (x < 10) (4 rows) test=> EXPLAIN SELECT * FROM fooview3; QUERY PLAN - Aggregate (cost=36.75..36.75 rows=1 width=0) -> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=0) (2 rows) test=> EXPLAIN SELECT * FROM fooview4; QUERY PLAN --- Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4) (1 row) test=> DROP INDEX foo_x_idx; DROP INDEX test=> EXPLAIN SELECT * FROM fooview1; QUERY PLAN Aggregate (cost=38.53..38.53 rows=1 width=0) -> Seq Scan on foo (cost=0.00..36.75 rows=713 width=0) Filter: (x < 10) (3 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [pgsql-advocacy] Spikewatch testing
Jim, > Can anyone step up for this now that things will hopefully calm down a > bit during beta? IIRC you just needed a commitment from a commiter, > right? Pretty much. Also, we need to write up test submission guidelines: basically just documentation on how to create a new regression test. It's on my TODO list ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-advocacy] Spikewatch testing
On Fri, Aug 26, 2005 at 01:49:34PM -0700, Josh Berkus wrote: > Jim, > > > Spikewatch is testing a number of different open-source components, > > including ours. If you click on a green datapoint, you can see what our > > actual code-coverage was for that test (presumably they're running the > > regression tests). Unfortunately, the banner ad I saw for them showed > > MySQL (along with a bunch of other things), but not PostgreSQL. > > Yeah, there's portions of the web interface that still have wrong > referents. I'll bug them again about that. > > > Is it worth trying to promote this as a way to promote PostgreSQL? Also, > > is it worth trying to improve our test coverage? > > Actually, they'll be running a contest (with prizes up to $2500) for > improved test coverage for OSS applications. I've been trying to get > someone to commit to helping me on the contest, so that PostgreSQL can > participate. Can anyone step up for this now that things will hopefully calm down a bit during beta? IIRC you just needed a commitment from a commiter, right? -- 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] [pgsql-advocacy] Spikewatch testing
Josh Berkus wrote: Is it worth trying to promote this as a way to promote PostgreSQL? Also, is it worth trying to improve our test coverage? Actually, they'll be running a contest (with prizes up to $2500) for improved test coverage for OSS applications. I've been trying to get someone to commit to helping me on the contest, so that PostgreSQL can participate. What is involved in this? Maybe if you give specifics one of us can commit to helping :-) Best Wishes, Chris Travers Metatron Technology Consulting ---(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
What everybody else said. :) But if it comes to voting... Anything to improve parallelism is good. Anything reducing blocking (ie: CLUSTER, VACUUM FULL) is good Improved handling of sort_mem (I think this will hit bizgres first) merge :) STATISTICS ON INDEXES! (specifically multi-field indexes) Multiple query plans for bound parameters. Materialized views. I don't know the history behind why Slony is trigger-based, but I think both materialized views and replication would benefit greatly from having a means to tie into WAL (or something similar) instead of using triggers. I would expect this to result in a dramatic speed improvement over triggers, since you would no longer be double-logging. A slick way to do this would be to tie-in to WAL writes that meet certain criteria (namely that they hit a specified table) and store those seperately on-disk. These would be played-back as needed. This mechanism should be useful for both replication and MViews. If you look at one of Oracle's replicaiton options, it's actually just a form of MViews that are on remote machines. Even if we stick with something trigger-based for now I think we should provide a base mechanism that works for both MViews and replication. -- 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 Momjian writes: > Ron Mayer wrote: >> * more sane math with intervals. For example, try: >> select '0.01 years'::interval, '0.01 months'::interval; > Added to TODO: > Fix SELECT '0.01 years'::interval, '0.01 months'::interval; Arguably, both of those things should be rejected as errors. What is a fraction of a year? Or a month? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TODO list comments
On Fri, Aug 26, 2005 at 03:44:18PM -0400, Bruce Momjian wrote: > Jim C. Nasby wrote: > > 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? > > Yes, that would be nice, though some times the threads are pretty long > and I try to digest the agreed-upon solution. Where would we put the > URLs? In the TODO file? Yeah, the digestification is good, and I hope it continues. But it's also good to be able to refer back to the original thread in it's entirety. My thought was to make the TODO item itself a link to the search (or ideally the thread itself). The advantage of just linking to the search is that would allow a clever CGI to just parse through the TODO and linkify the TODO items. -- 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] [pgsql-advocacy] Spikewatch testing
Jim, > Spikewatch is testing a number of different open-source components, > including ours. If you click on a green datapoint, you can see what our > actual code-coverage was for that test (presumably they're running the > regression tests). Unfortunately, the banner ad I saw for them showed > MySQL (along with a bunch of other things), but not PostgreSQL. Yeah, there's portions of the web interface that still have wrong referents. I'll bug them again about that. > Is it worth trying to promote this as a way to promote PostgreSQL? Also, > is it worth trying to improve our test coverage? Actually, they'll be running a contest (with prizes up to $2500) for improved test coverage for OSS applications. I've been trying to get someone to commit to helping me on the contest, so that PostgreSQL can participate. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
Josh Berkus writes: > Oh, yeah I forgot: > > -- windowing functions (e.g. RANK, RANK OVER, LAST 10) Include this URL or one like it in any TODO about this: http://publib.boulder.ibm.com/infocenter/rb63help/topic/com.ibm.redbrick.doc6.3/sqlrg/sqlrg36.htm#sii-06-62323 It would be wonderful having this stuff but I'll say just skimming it was giving me headaches imagining how much work it would be to do right. Just having a few windowing functions like rank() and functions like running averages would go a long way to making people happy though. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Any MIPS assembly experts in the house?
I wrote: > Can anyone spot the problem? If not I fear we'll have to revert this. After a bit of reading MIPS documentation, I found out that the proposed patch is exactly backward: it returns 1 if it gets the lock and 0 if the lock is already held :-( Because callers will loop on a nonzero return, the second iteration falls through, which is why the thing isn't an infinite loop. Only problem is when we hit the lock at an instant when somebody else already has it. Given the short duration of our spinlock holds, it was probably quite a coincidence that Stefan's machine got a failure almost immediately. We might have had the problem lurking for awhile. I'll try to commit something that really works in a little bit. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page
> -Original Message- > From: John Hansen [mailto:[EMAIL PROTECTED] > Sent: 26 August 2005 20:03 > To: Merlin Moncure; pgsql-hackers@postgresql.org > Cc: Dave Page > Subject: RE: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page > > Merlin Moncure Wrote: > > > ... Be sure to mix in a request for > > better Unicode support at the same time, Dave loves that. > > As do I... :) Feel free to subscribe and join in - we could use someone that actually knows a little about Unicode ;-) I'm sure your damp string could cope with the extra bits!! Regards Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page
> -Original Message- > From: Merlin Moncure [mailto:[EMAIL PROTECTED] > Sent: 26 August 2005 15:42 > To: pgsql-hackers@postgresql.org > Cc: Dave Page > Subject: RE: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page > > Congratulations, dave. We should probably be cross-posting > this to the > odbc list. Thanks Merlin :-) > Be sure to mix in a request for better Unicode support at > the same time, Dave loves that. Grrr. Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Spikewatch testing
Spikewatch is testing a number of different open-source components, including ours. If you click on a green datapoint, you can see what our actual code-coverage was for that test (presumably they're running the regression tests). Unfortunately, the banner ad I saw for them showed MySQL (along with a bunch of other things), but not PostgreSQL. Is it worth trying to promote this as a way to promote PostgreSQL? Also, is it worth trying to improve our test coverage? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Any MIPS assembly experts in the house?
Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> which makes it seem highly probable that this recently committed patch >> to convert the MIPS out-of-line spinlock code into inline assembler >> isn't right: >> http://archives.postgresql.org/pgsql-committers/2005-08/msg00319.php > As the owner of said machine I was about to report the problem - but on > a subsequent run of the buildfarm-script(to get access to the compiled > source for further debugging and testing) it completed without an error. Unfortunately, that just says that the failure is intermittent, which is pretty much exactly what you'd expect from not-quite-right locking code. Try running the parallel regression tests several times in a row. (The serial test mode is unlikely to exhibit any problem.) I tend to do this by installing the test postmaster and then doing "make installcheck-parallel" over and over, rather than repeating "make check"; repeatedly building a test installation is just wasting time for this sort of thing. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Any MIPS assembly experts in the house?
Tom Lane wrote:
> I see the latest buildfarm result from a mipsel machine is failing:
> http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lionfish&dt=2005-08-26%2005:30:07
>
> and the failure is this:
>
> TRAP: FailedAssertion("!(lock->shared > 0)", File: "lwlock.c", Line: 456)
> LOG: server process (PID 10112) was terminated by signal 6
>
> which makes it seem highly probable that this recently committed patch
> to convert the MIPS out-of-line spinlock code into inline assembler
> isn't right:
> http://archives.postgresql.org/pgsql-committers/2005-08/msg00319.php
>
> Can anyone spot the problem? If not I fear we'll have to revert this.
As the owner of said machine I was about to report the problem - but on
a subsequent run of the buildfarm-script(to get access to the compiled
source for further debugging and testing) it completed without an error.
Stefan
---(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
Ron Mayer wrote: > * more sane math with intervals. For example, try: > select '0.01 years'::interval, '0.01 months'::interval; Added to TODO: Fix SELECT '0.01 years'::interval, '0.01 months'::interval; -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO list comments
Andrew Dunstan wrote: > > > 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. Added to TODO: o Allow multi-line column values to align in the proper columns If the second output column value is 'a\nb', the 'b' should appear in the second display column, rather than the first column as it does now. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] TODO questions
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 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. We should issue a warning in the server logs if someone tries to modify a postmastger-only value on reload. Added to TODO: o Issue a warning if a change-on-restart-only postgresql.conf value is modified and the server config files are reloaded o Mark change-on-restart-only values in postgresql.conf -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TODO list comments
Jim C. Nasby wrote: > 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? Yes, that would be nice, though some times the threads are pretty long and I try to digest the agreed-upon solution. Where would we put the URLs? In the TODO file? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TODO list comments
Hannu Krosing wrote: > 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. Yes, but I can imagine allowing a CASCADE behavior as well. > > 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. I am not sure on that one because the table does have the constraint at the time the child is created. Comments? > 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. Right, TODO item removed. > > > 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. Well, I beefed up the item: * Track dependencies in function bodies and recompile/invalidate This is particularly important for references to temporary tables in PL/PgSQL because PL/PgSQL caches query plans. The only workaround in PL/PgSQL is to use EXECUTE. > 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. Interesting approach but is it worth the added complexity? One issue this does bring up is that functions themselves might invalidate their own cached query plan by dropping a table and receating it. In those cases, your solution would be the only valid one, or throw an error. I added some more text: * Track dependencies in function bodies and recompile/invalidate This is particularly important for references to temporary tables in PL/PgSQL because PL/PgSQL caches query plans. The only workaround in PL/PgSQL is to use EXECUTE. One complexity is that a function might itself drop and recreate dependent tables, causing it to invalidate its own query plan. > 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. Perhaps. > And it may make more sense to do CLUSTER instead of VACUUM FULL in at > least some of these cases. Cluster modifies the heap while reindex does not. This makes cluster a much heavier operation. > (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) Not sure. > > > * 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. Not sure, CLUSTER is still heavier. That doesn't mean it shouldn't be used, but the administrator should automatically consider CLUSTER in place of VACUUM FULL for large updates. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 3
Re: [HACKERS] TODO list comments
Alvaro Herrera wrote: > 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. TODO updated: o Improve psql's handling of multi-line queries Currently, while \e saves a single query as one entry, interactive queries are saved one line at a time. Ideally all queries whould be saved like \e does. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page
Merlin Moncure Wrote: > ... Be sure to mix in a request for > better Unicode support at the same time, Dave loves that. As do I... :) ... John ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Any MIPS assembly experts in the house?
I see the latest buildfarm result from a mipsel machine is failing:
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lionfish&dt=2005-08-26%2005:30:07
and the failure is this:
TRAP: FailedAssertion("!(lock->shared > 0)", File: "lwlock.c", Line: 456)
LOG: server process (PID 10112) was terminated by signal 6
which makes it seem highly probable that this recently committed patch
to convert the MIPS out-of-line spinlock code into inline assembler
isn't right:
http://archives.postgresql.org/pgsql-committers/2005-08/msg00319.php
Can anyone spot the problem? If not I fear we'll have to revert this.
Also, upon looking more closely at the new inline code, it looks like
it's designed to *loop* if it fails to get the lock, which is *wrong*.
The assembly fragment should test once and fall out. This doesn't
explain the regression test failure, it would just cause stuck-lock
detection to not work.
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] TODO list comments
Great updates! Let me comment on each one. > I made a pass over the TODO list to see what was out of date. > > > * Allow administrators to safely terminate individual sessions either > > via an SQL function or SIGTERM > > > > Currently SIGTERM of a backend can lead to lock table corruption. > > This comment may be out of date. Suggest > > Lock table corruption following SIGTERM of an individual backend > has been reported in 8.0. A possible cause is fixed in 8.1, but > it is unknown whether other trouble spots exist. This item is > mainly a matter of doing adequate testing rather than of writing > any new code. Done. > > > o Allow postgresql.conf values to be set so they can not be changed > > by the user > > Is that really a good idea? The ones that are unsafe are restricted already. Well, a typical case would be log_statement, but I see that is super-user now. I guess we are OK, removed. If we get more problems, we can re-add something later. > > * %Remove Money type, add money formatting for decimal type > > There's a fair-size contingent that doesn't want Money removed > completely, but just reimplemented as an I/O wrapper around type > numeric. Maybe that's even what you mean by the TODO item, but > it's not clear. Please at least mention the alternative. Updated: * Improve the MONEY data type Change the MONEY data type to use DECIMAL internally, with special locale-aware output formatting. > > o %Allow MIN()/MAX() on arrays > > This is done. OK. > > o Modify array literal representation to handle array index lower bound > > of other than one > > This too. OK. > > > o Add security checking for large objects > > > > Currently large objects entries do not have owners. Permissions can > > only be set at the pg_largeobject table level. > > This comment is wrong: trying to set the permissions on pg_largeobject > would have no effect whatsoever on the lo_xxx functions, so there is not > even a partial solution available now. Oh, comment removed. > > o Auto-delete large objects when referencing row is deleted > > This should note that contrib/lo already offers a solution. Done. > > * %Have views on temporary tables exist in the temporary namespace > > * Allow temporary views on non-temporary tables > > Both of these are done in 8.1. OK. > > * %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. Agreed, updated to: * Allow VIEW/RULE recompilation when the underlying tables change > > * %Allow TRUNCATE ... CASCADE/RESTRICT > > Huh? What would that do? I assume it is just like DELETE CASCADE, but it TRUNCATES rather than DELETE. Description added. > > * Make row-wise comparisons work per SQL spec > > This could probably be marked as a % item. Done. > > o Currently the system uses the operating system COPY command to > > create a new database. Add ON COMMIT capability to CREATE TABLE AS > > SELECT > > This seems a bit garbled, and anyway the first part is done. Yep, garbled. I have removed the first part. > > o %Add ALTER DOMAIN TYPE > > To do what, exactly? This is unclear. I assume it would allow the underlying data type to be changed. Updated text: o Add ALTER DOMAIN to modify the underlying data type > > o -Allow objects to be moved to different schemas > > This is only partly done --- the 8.1 patch didn't cover all object types. Updated to: o Add missing object types for ALTER ... SET SCHEMA > > 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. Merged and updated: o %Prevent child tables from altering or dropping constraints like CHECK that were inherited from the parent table > > 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. Removed and description added to dependency item: * Track dependencies in function bodies and recompile/invalidate This is particularly important for references to temporary tables in PL/PgSQL because PL/PgSQL caches query plans. The only workaround in PL/PgSQL is to use EXECUTE. > > o Add table function support to pltcl, plperl, plpython? > > Isn't this done for
Re: [HACKERS] Call for 7.5 feature completion
Hi all, Our organizations are doing a lot of real time reporting involving queries with multiple tables, and large tables. I found that two features are very nice to have: - Table Partition - Materialized view Thanks, J On 8/26/05, Ron Mayer <[EMAIL PROTECTED]> wrote: > Alvaro Herrera wrote: > > > Or, slightly different, what are people's most wanted features? > > Things I would have found useful in the past year or so include: > > Standards stuff: > > * Updateable views (easier to use Ruby/Rails's ActiveRecord on legacy data) > * The elementary OLAP stuff > > Contrib related stuff: > > * Contrib/xml2 working with XML Namespaces. > * Some sort of GIST index for querying XML data (XPath? SQL/XML?) > > * The array functions and indexes from contrib/intarray >and contrib/intagg made more general to work with other >data types. (I find these contrib modules quite useful) > > Annoyances: > > * more sane math with intervals. For example, try: >select '0.01 years'::interval, '0.01 months'::interval; > > Ease of use: > > * Nice defaults for autovacuum and checkpoints and bgwriter >that automatically avoid big I/O spikes by magically >distributing I/O in a nice way. > > Easier COPY for client library authors: > > * A way to efficiently insert many values like COPY from STDIN >from client libraries that don't support COPY from STDIN. >Perhaps it could happen through the apparently standards >compliant >"INSERT INTO table VALUES (1,2),(3,4),(5,6)" [feature id F641] >or perhaps through a new >COPY tablename FROM STRING 'a big string instead of stdin' >feature that would be easier for clients to support? > >It seems in most new client libraries COPY FROM STDIN >stays broken for quite a long time. Would a >alternative COPY FROM A_BIG_STRING be easier for them >to support and therefore available more often? > > Meta-stuff > > * A failover plus load-balancing (pgpool+slony?) >installer for dummies that handles simple cases. > > * A single place to find all the useful non-core stuff >like projects on pgfoundry, gborg, contrib, and >various other places around the net (PL/R PL/Ruby Postgis). >Perhaps if the postgresql website had a small wiki >somewhere where anyone could add links with a short >description to any such projects it'd be easier to >know what's out there... > > * Nice APIs and documentation [probably already exists] >to continue encouraging projects like PostGIS and PL/R >that IMHO are the biggest advantage of postgresql over >the commercial vendors' offerings. > > Oh, and seeing everyone else's response, I suppose I should > add MERGE though I haven't actually noticed a need yet. :-) > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Call for 7.5 feature completion
Alvaro Herrera wrote: Or, slightly different, what are people's most wanted features? Things I would have found useful in the past year or so include: Standards stuff: * Updateable views (easier to use Ruby/Rails's ActiveRecord on legacy data) * The elementary OLAP stuff Contrib related stuff: * Contrib/xml2 working with XML Namespaces. * Some sort of GIST index for querying XML data (XPath? SQL/XML?) * The array functions and indexes from contrib/intarray and contrib/intagg made more general to work with other data types. (I find these contrib modules quite useful) Annoyances: * more sane math with intervals. For example, try: select '0.01 years'::interval, '0.01 months'::interval; Ease of use: * Nice defaults for autovacuum and checkpoints and bgwriter that automatically avoid big I/O spikes by magically distributing I/O in a nice way. Easier COPY for client library authors: * A way to efficiently insert many values like COPY from STDIN from client libraries that don't support COPY from STDIN. Perhaps it could happen through the apparently standards compliant "INSERT INTO table VALUES (1,2),(3,4),(5,6)" [feature id F641] or perhaps through a new COPY tablename FROM STRING 'a big string instead of stdin' feature that would be easier for clients to support? It seems in most new client libraries COPY FROM STDIN stays broken for quite a long time. Would a alternative COPY FROM A_BIG_STRING be easier for them to support and therefore available more often? Meta-stuff * A failover plus load-balancing (pgpool+slony?) installer for dummies that handles simple cases. * A single place to find all the useful non-core stuff like projects on pgfoundry, gborg, contrib, and various other places around the net (PL/R PL/Ruby Postgis). Perhaps if the postgresql website had a small wiki somewhere where anyone could add links with a short description to any such projects it'd be easier to know what's out there... * Nice APIs and documentation [probably already exists] to continue encouraging projects like PostGIS and PL/R that IMHO are the biggest advantage of postgresql over the commercial vendors' offerings. Oh, and seeing everyone else's response, I suppose I should add MERGE though I haven't actually noticed a need yet. :-) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: VACUUM/t_ctid bug (was Re: [HACKERS] GiST concurrency commited)
Finded problem in GiST isn't too simple to resolve. I'm working on it. The
problem is about update query...
Tom Lane wrote:
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
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for 7.5 feature completion
On Thu, 25 Aug 2005, Alvaro Herrera wrote: Or, slightly different, what are people's most wanted features? Since you asked: * concurrent, partial vacuum that would for example only scan pages that happen to be in memory * index-only scans * database assertions * lightwight PITR that wouldn't require to shut down and restore a backup. I'm thinking something like "REWIND TO xid 12345". It could be implemented by just setting already-committed transactions as aborted in the clog (vacuum and commit status hint bits need to be disabled beforehand). This would be very handy for automatic regression testing applications. You could load the test database just once, then run test case, rewind, run another test case, rewind and so on. As more disruptive longer-term things: * multiple alternative access plans for prepared statements. For example, if you have a query like "SELECT * FROM history WHERE timestamp BETWEEN ? AND ?", the optimal access plan depends a lot on the parameters. Postgres could keep all the plans that are optimal for some combination of parameters, and choose the most efficient one at execution time depending on the parameters. The execution side would actually be quite simple to implement. Introduce a new conditional node type that has > 1 child nodes, and a condition that is evaluated at execution time and determines which child node to use. Determining the conditions would require big changes to the planner and estimation routines. * support for Tutorial D as an alternative to SQL. It would be great for educational purposes. - Heikki ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Call for 7.5 feature completion
On Fri, 2005-08-26 at 13:13 -0400, Nicholas Walker wrote: > >You can't use savepoints, you can trap errors which is implemented using > >savepoints. You still might want to write code like this: > > > >BEGIN > > > > > > > >SAVEPOINT foo; > > > > > > > >IF SOME_ERROR_CODE = 1234 THEN > > ROLLBACK TO SAVEPOINT foo; > >END > > > >... > I agree, and I think savepoints would be much more usefull if you could > call them from pl/pgsql... Maybe if PL/pgSQL had user-defined exceptions then the language's identity of savepoints and exception blocks would be a little easier to work with. Is anything happening with the patch for user-defined exceptions, posted at http://archives.postgresql.org/pgsql-patches/2005-06/msg00475.php (and also discussed elsewhere)? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Stuff running slooow
On Fri, Aug 26, 2005 at 11:42:04AM -0400, Chris Browne wrote: > [EMAIL PROTECTED] (Alvaro Herrera) writes: > > 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. > > We'd discover ourselves inundated with questions about Dueling > Banjos... This should explain it: http://homepages.nildram.co.uk/~blagger/the_duel.html Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(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
Dennis Bjorklund wrote: On Thu, 25 Aug 2005, Josh Berkus wrote: 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. You can't use savepoints, you can trap errors which is implemented using savepoints. You still might want to write code like this: BEGIN SAVEPOINT foo; IF SOME_ERROR_CODE = 1234 THEN ROLLBACK TO SAVEPOINT foo; END ... You can write code like this if you issue each command from the client, say using libpq, but not in pl/pgsql. I agree, and I think savepoints would be much more usefull if you could call them from pl/pgsql... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for 7.5 feature completion
* Alvaro Herrera ([EMAIL PROTECTED]) wrote: > Or, slightly different, what are people's most wanted features? MERGE. Stephen signature.asc Description: Digital signature
Re: [HACKERS] Stuff running slooow
[EMAIL PROTECTED] (Alvaro Herrera) writes: > 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. We'd discover ourselves inundated with questions about Dueling Banjos... -- let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;; http://cbbrowne.com/info/spiritual.html "Parentheses? What parentheses? I haven't noticed any parentheses since my first month of Lisp programming. I like to ask people who complain about parentheses in Lisp if they are bothered by all the spaces between words in a newspaper..." -- Kenny Tilton <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Welcome Core Team member Dave Page
On Thu, Aug 25, 2005 at 05:59:33PM -0700, 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! Congratulations, Dave! :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(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
On Thu, Aug 25, 2005 at 07:13:18PM -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? > > Has PostgreSQL started slowing down in getting new features, and > concentrating mostly on performance issues? Along with all the other cool stuff people have already mentioned, I'd like to see composite types handled better--they're not quite first-class objects yet. Also nice to have: * optional interface which sends a row typeoid along with each row in a result set * more visibility from RULEs into the expression tree generated by the parser and/or other RULEs * SQL/MED (or at least things that would make it easier to implement) * Debugging hooks into all the PLs * Some way of estimating a "query progress meter" for long-running queries * MULTISET, COLLECT, UNNEST, FUSION, INTERSECT oh, and MERGE! MERGE! MERGE! MERGE! MERGE! MERGE! Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: VACUUM/t_ctid bug (was Re: [HACKERS] GiST concurrency commited)
On R, 2005-08-26 at 16:47 +0400, Teodor Sigaev wrote: > > 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 ? > > I didn't find any problem with your patch during testing with 1e8 > statements... Thank You Very Much ! -- Hannu Krosing <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Stuff running slooow
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > 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. The several days should be a thing of the past now. Most queues get cleaned out at least once a day. The duplicate thing is a pain, but I'd rather err on the side of having two messages than missing one entirely. > 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. Even just adding that text to the welcome message would help a lot for the current system. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200508261048 https://www.biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEARECAAYFAkMPLI4ACgkQvJuQZxSWSsiqnwCeO+xpU///TTXxCqXt1MRWu2Im TAkAoK6OliABug6B2Tej0ktSVA2JpDlA =e0rZ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
On N, 2005-08-25 at 19:13 -0400, Alvaro Herrera 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? my pet wishes are : 24/7 OLTP related things * vacuums that ignore other vacuums when deciding what tuples to free (should be mostly done, my patch wasleft to 8.2 due to some doubts by Tom) * non-blocking CREATE INDEX / REINDEX (so indexes can be added to huge tables on busy databases without downtime) * related to last one - command to promote UNIQUE INDEX to PRIMARY KEY. * multiple WAL's, assignable to objects (similaŕ to tablespaces). * better 64-bit support inside db engine. * real background vacuuming, using something like FSM, may be integrated with background writer. * VACUUM FULL/CLUSTER added behaviour of leaving pages half-empty (or any.other-percentage-empty) for good update behaviour. OLAP stuff * table partitioning to move forward. * archive tables (append (==insert) only, only one writer at a time, vacuum needed after rollbacked insert, visibility determined by "last valid ctid" marker, so will not need most of header fields either). * index-only scans over archive tables (possible without altering current index structure, as visibility can be determined by ctid which is already present in index leaf). > Has PostgreSQL started slowing down in getting new features, and > concentrating mostly on performance issues? I can't think of this as new features vs. performance thing as many of the new features *are* largely about performance, both on database engine and on user side. -- Hannu Krosing <[EMAIL PROTECTED]> ---(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] [ANNOUNCE] Welcome Core Team member Dave Page
> > 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. Congratulations, dave. We should probably be cross-posting this to the odbc list. Be sure to mix in a request for better Unicode support at the same time, Dave loves that. ;) Merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ANNOUNCE] Its Beta Time Again ... 8.1 Beta 1 Now Available
Hi Marc, can you please tell me why you changed the user/group concept to the role concept? I cannot see any advantages, though I see, that the complete user administration needs to be modified when upgrading to the next version. As I am on vacation beginning next week, please respond also to [EMAIL PROTECTED] . Thanks in advance, Antje Stejskal -Ursprüngliche Nachricht- Von: Marc G. Fournier [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 26. August 2005 01:28 An: pgsql-announce@postgresql.org Cc: pgsql-general@postgresql.org Betreff: [ANNOUNCE] Its Beta Time Again ... 8.1 Beta 1 Now Available Back on the 1st of July, after almost 6 months of development since 8.0 was released, development on 8.1 was frozen. Now, after spending the past few weeks processing through outstanding patches applicable to 8.1, we now enter our beta testing period, where we need help from the community at large. PostgreSQL 8.1 will bring in alot of fairly large features, including: - Improve concurrent access to the shared buffer cache - Add in-memory bitmaps which allows multiple indexes to be merged in a single query - Add two-phase commit - Create a new role system that replaces users and groups - Move /contrib/pgautovacuum into the main server - Add shared row level locks using SELECT ... FOR SHARE - many many others listed in the HISTORY file As with all major releases, we need to put the above changes through some extensive testing, in as many scenarios as possible, so encourage both testing, as well as bug reports. PostgreSQL 8.1 Beta 1 is now available via: http://www.postgresql.org/ftp/source/v8.1beta Please report any bugs with this beta to: pgsql-bugs@postgresql.org Or through our Bug Reporting Tool available at: http://www.postgresql.org/support/submitbug 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Welcome Core Team member Dave Page
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 6: explain analyze is your friend
Re: [HACKERS] Call for 7.5 feature completion
Alvaro wrote: > Or, slightly different, what are people's most wanted features? 1. Proper row constructor, such that select (1,2,1) > (2,1,1); returns the right answer, and select * from t where (t1,t2,t3) > (c1, c2, c3) order by t1,t2,t3 limit 1 returns the right answer and uses a index on t1,t2,t3 if it exists. this is on the TODO. 2. In the planner, a parameterized limit for prepared statements to assume a small value (like 1). 3. Ability to create arrays of composite types (and nest them). > Has PostgreSQL started slowing down in getting new features, and > concentrating mostly on performance issues? nope! Merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] no duration logging when using JDBC?
Hi! When using JDBC (8.0.311) to connect to a 8.0.3 database, with log_duration = true nothing happens. For psql connections, it works fine, but not for JDBC connections. Surely a bug? Is it fixed in the 8.1 branch? Also, as I mailed about a while back, when using prepared statements, the parameters for each sql query are not logged, only the query itself. There must be a way to get the parameters as well. Cheers, Palle ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] SSL client crt verification
"Knut P Lehre" <[EMAIL PROTECTED]> writes: > Server: 7.4.8 on Red Hat EL4. Client psql 8.0.3 on WinXP. Using a > test server.crt and server.key, as described in 8.0 docs 16.8, I can > activate SSL encryption (WinXP 8.0.3 psql reports "SSL Connection" > at connect), and as expected, the server log reports that root.crt > is not found. If I copy server.crt to root.crt and start up server > again, the missing root.crt message disappears. But I can still > connect with psql with no postgresql.crt on client (docs > 27.13). Apparently, the root.crt, which in this case is a copy of > server.crt, is not interpreted as a CA crt, but how does the server > know? Isn't a CA cert just a self signed crt? Why is there not a > warning that client authentication will not take place, when it > apparently does not? Does the server only check that a root.crt file > is present, and not that is contains valid information? You really need a separate root CA cert and server key. The server key should be signed by the root CA, which is self-signed. The root CA private key should be kept secure somewhere else--the database server doesn't need access to it. I just went though the whole exercise of creating a CA and setting up an app for client cert authentication, so I am reasonably sure the above is your problem. For client auth to work, you need: * Root CA certificate available to both client and server * Server cert signed by root CA, with CN=server hostname * Client cert signed by root CA, available to client to present to server. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page
So, welcome Dave Page as the newest member of Core! Congratulations Dave.!! We memorize this day firmly. :-) Hiroshi Saito pgAdmin team ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] SSL client crt verification
Tried doc search, pgsql-general and #postgresql. Server: 7.4.8 on Red Hat EL4. Client psql 8.0.3 on WinXP. Using a test server.crt and server.key, as described in 8.0 docs 16.8, I can activate SSL encryption (WinXP 8.0.3 psql reports "SSL Connection" at connect), and as expected, the server log reports that root.crt is not found. If I copy server.crt to root.crt and start up server again, the missing root.crt message disappears. But I can still connect with psql with no postgresql.crt on client (docs 27.13). Apparently, the root.crt, which in this case is a copy of server.crt, is not interpreted as a CA crt, but how does the server know? Isn't a CA cert just a self signed crt? Why is there not a warning that client authentication will not take place, when it apparently does not? Does the server only check that a root.crt file is present, and not that is contains valid information? Comments appreciated KPL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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 ? I didn't find any problem with your patch during testing with 1e8 statements... -- 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
Re: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page
On Aug 26, 2005, at 8:41 PM, Tino Wildenhain wrote: Gavin M. Roy schrieb: Congrats Dave! Yes, congrats! Congratulations, Dave! :) 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] [ANNOUNCE] Welcome Core Team member Dave Page
Gavin M. Roy schrieb: Congrats Dave! Yes, congrats! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [ANNOUNCE] Welcome Core Team member Dave Page
Good on ya, Dave! ... John > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Gavin M. Roy > Sent: Friday, August 26, 2005 1:51 PM > To: pgsql-hackers@postgresql.org > Subject: 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 > > ---(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] [ANNOUNCE] Its Beta Time Again ... 8.1 Beta 1 Now Available
Well, I got all 98 tests passing on slackware 10, must be about time for RC
isn't it? :-)
Robert Treat
On Thursday 25 August 2005 19:28, Marc G. Fournier wrote:
> Back on the 1st of July, after almost 6 months of development since 8.0
> was released, development on 8.1 was frozen.
>
> Now, after spending the past few weeks processing through outstanding
> patches applicable to 8.1, we now enter our beta testing period, where we
> need help from the community at large.
>
> PostgreSQL 8.1 will bring in alot of fairly large features, including:
>
> - Improve concurrent access to the shared buffer cache
> - Add in-memory bitmaps which allows multiple indexes to be merged in
> a single query
> - Add two-phase commit
> - Create a new role system that replaces users and groups
> - Move /contrib/pgautovacuum into the main server
> - Add shared row level locks using SELECT ... FOR SHARE
> - many many others listed in the HISTORY file
>
> As with all major releases, we need to put the above changes through some
> extensive testing, in as many scenarios as possible, so encourage both
> testing, as well as bug reports.
>
> PostgreSQL 8.1 Beta 1 is now available via:
>
> http://www.postgresql.org/ftp/source/v8.1beta
>
> Please report any bugs with this beta to:
>
> pgsql-bugs@postgresql.org
>
> Or through our Bug Reporting Tool available at:
>
> http://www.postgresql.org/support/submitbug
>
>
>
>
> 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
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Stuff running slooow
Tom Lane wrote: 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. If it's possible, cool. What I have seen from other mailing lists (possibly newer versions of the same software?) is a "Cancel message" link in the e-mail telling about moderation status. So one can cancel the message, subscribe to the list and send the message again. At least better than what we have now. Best Regards, Michael Paesold ---(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
On Thu, 25 Aug 2005, Josh Berkus wrote: > > 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. You can't use savepoints, you can trap errors which is implemented using savepoints. You still might want to write code like this: BEGIN SAVEPOINT foo; IF SOME_ERROR_CODE = 1234 THEN ROLLBACK TO SAVEPOINT foo; END ... You can write code like this if you issue each command from the client, say using libpq, but not in pl/pgsql. -- /Dennis Björklund ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
