Re: [HACKERS] migrate data 6.5.3 - 8.3.1
Tom Lane wrote: alexander lunyov [EMAIL PROTECTED] writes: I want to try new pg_dump to connect to old server, but i can't - old postgres doesn't listening to network socket. It won't work anyway: modern versions of pg_dump are only designed to work with servers back to 7.0. I see from the rest of the thread that you tried to bludgeon it into submission, but I'd very strongly recommend that you abandon that approach and use 6.5's pg_dump. Ok, i already have the dumps made with 6.5 pg_dump, but what should i do with those errors on AGGREGATEs? Do they really exist in 8.3, so i can just cut them off from dumps and happily restore dumps without them? A further suggestion is that you use -d or even -D option on the dump. I think there have been some corner-case changes in COPY data format since 6.5 days; which might or might not bite you, but why take the chance ... Thank you, i'll redo dumps with this option just to make sure everything is fine, but despite errors on AGGREGATEs and some types errors all data was restored correctly even without -d/-D option. But i'll redo them anyway. -- alexander lunyov -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal sql: labeled function params
2008/8/17 Hannu Krosing [EMAIL PROTECTED]: On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote: Hannu it's not possible in plpgsql, because we are not able iterate via record. just add function for iterating over record :) it's not easy, when iterating should be fast - when record's field has different types, than isn't possible cache execution plan. Pavel create or replace function json(r record) returns varchar as $$ select '[' || array_to_string( array( select (getfieldnames(r))[i]|| ':' || getfieldvalue(r,i) from generate_subscripts(r,1) g(i)) ,',') || ']' $$ language sql immutable strict; (this is a straight rewrite of your original sample, one can also do it in a simpler way, with a function returning SETOF (name, value) pairs) postgres=# select json(name='Zdenek',age=30); json -- [name:Zdenek,age:30] (1 row) postgres=# select json(name, age) from person; json -- [name:Zdenek,age:30] (1 row) BTW, json actually requires quoting names/labels, so the answer should be [name:Zdenek,age:30] 2008/8/17 Hannu Krosing [EMAIL PROTECTED]: On Sun, 2008-08-17 at 11:08 -0400, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: Actually the most natural syntax to me is just f(name=value) similar to how UPDATE does it. It has the added benefit of _not_ forcing us to make a operator reserved (AFAIK = can't be used to define new ops) *What* are you thinking? I think that we could achieve what Pavel was after by allowing one to define something similar to keyword arguments in python. maybe allow input RECORD type, which is instantiated at call time by giving extra arguments to function call: CREATE FUNCTION f_kw(r record) and then if you call it like this: SELECT ... f_kw(name='bob', age=7::int) then function gets as its input a record which can be accessed in pl/pgsql like r.name r.age and if terseness is really appreciated then the it could also be called like this SELECT ... f_kw(name, age) from people where name='bob'; which is rewritten to SELECT ... f_kw(name=name, age=age) from people where name='bob'; not sure if we should allow defining SETOF RECORD and then enable calling it with SELECT * FROM f_kw( VALUES(name='bob', age=7::int), VALUES(name='bill', age=42::int ); or somesuch -- Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] any psql static binary for iphone ?
Am Sunday, 17. August 2008 schrieb Oleg Bartunov: is there psql static binary, which I can use on my iphone (version 1) ? I have no idea, but just as a thought, using phpPgAdmin might be a good workaround. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal sql: labeled function params
Am Saturday, 16. August 2008 schrieb Hannu Krosing: A label is the same thing as variable/attribute/argument name in all programming languages I can think of. Why do you need two kinds of argument names in postgreSQL ? maybe you are after something like keyword arguments in python ? http://docs.python.org/tut/node6.html#SECTION00672 keyword arguments are a way of saying that you don't know all variable names (or labels if you prefer) at function defining time and are going to pass them in when calling. I think we are beginning to talk about the same thing. (Meaning you and me -- not sure about the rest. :) ) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] any psql static binary for iphone ?
Peter Eisentraut wrote: Am Sunday, 17. August 2008 schrieb Oleg Bartunov: is there psql static binary, which I can use on my iphone (version 1) ? I have no idea, but just as a thought, using phpPgAdmin might be a good workaround. postgres seems to compile nicely on the iphone. compilations stops at gram.c however :) the file is just too big to compile on 96MB of RAM :). first the screen turns to black and then it reboots. so far i have not seen how i can add a swap file to the iphone and i was too lazy to cross compile *g*. but until gram.c - no warning; no errors *g*. regards, hans -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, A-2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql-support.de, www.postgresql-support.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Automatic Client Failover
On Fri, 2008-08-15 at 14:25 -0400, Bruce Momjian wrote: Simon Riggs wrote: Implementation would be to make PQreset() try secondary connection if the primary one fails to reset. Of course you can program this manually, but the feature is that you wouldn't need to, nor would you need to request changes to 27 different interfaces either. I assumed share/pg_service.conf would help in this regard; place the file on a central server and modify that so everyone connects to another server. Perhaps we could even add round-robin functionality to that. I do want to keep it as simple as possible, but we do need a way that will work without reconfiguration at the time of danger. It needs to be preconfigured and tested, then change controlled so we all know it works. OK, so using share/pg_service.conf as an implementation example, how would this work? The application supplies multiple service names and libpq tries attaching to each one in the list until one works? This could work in one of two ways (maybe more) * supply a group for each service. If main service goes down, try other services in your group * supply a secondary service for each main service. If primary goes down we look at secondary service It might also be possible to daisy-chain the retries, so after trying the secondary/others we move onto the secondary's secondary in much the same way that a telephone hunt group works. This was suggested as a complementary feature alongside other server-side features I'm working on. I'm not thinking of doing this myself, since I know much less about the client side code than I'd need to do this in the time available. Also, I'm not sure whether it is unpopular or simply misunderstood. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] IN vs EXISTS equivalence
Hello I did some fast test on pagila database. 8.4 postgres=# explain analyze select * from film f where exists (select film_id from film_actor where f.film_id = film_id); QUERY PLAN -- Hash Join (cost=117.01..195.42 rows=966 width=390) (actual time=36.011..43.314 rows=997 loops=1) Hash Cond: (f.film_id = film_actor.film_id) - Seq Scan on film f (cost=0.00..65.00 rows=1000 width=390) (actual time=0.027..1.971 rows=1000 loops=1) - Hash (cost=104.94..104.94 rows=966 width=2) (actual time=35.886..35.886 rows=997 loops=1) - HashAggregate (cost=95.28..104.94 rows=966 width=2) (actual time=32.650..34.139 rows=997 loops=1) - Seq Scan on film_actor (cost=0.00..81.62 rows=5462 width=2) (actual time=0.081..14.232 rows=5462 loops=1) Total runtime: 45.373 ms (7 rows) 8.3 postgres=# explain select * from film f where exists (select film_id from film_actor where f.film_id = film_id); QUERY PLAN -- Seq Scan on film f (cost=0.00..4789.34 rows=500 width=390) Filter: (subplan) SubPlan - Index Scan using idx_fk_film_id on film_actor (cost=0.00..28.35 rows=6 width=2) Index Cond: ($0 = film_id) (5 rows) postgres=# explain analyze select * from film f where not exists (select film_id from film_actor where f.film_id = film_id); QUERY PLAN Hash Anti Join (cost=149.90..240.24 rows=34 width=390) (actual time=25.473..28.169 rows=3 loops=1) Hash Cond: (f.film_id = film_actor.film_id) - Seq Scan on film f (cost=0.00..65.00 rows=1000 width=390) (actual time=0.027..1.898 rows=1000 loops=1) - Hash (cost=81.62..81.62 rows=5462 width=2) (actual time=24.398..24.398 rows=5462 loops=1) - Seq Scan on film_actor (cost=0.00..81.62 rows=5462 width=2) (actual time=0.035..12.400 rows=5462 loops=1) Total runtime: 28.866 ms postgres=# explain analyze select * from film f where not exists (select film_id from film_actor where f.film_id = film_id); QUERY PLAN --- Seq Scan on film f (cost=0.00..4789.34 rows=500 width=390) (actual time=5.874..22.654 rows=3 loops=1) Filter: (NOT (subplan)) SubPlan - Index Scan using idx_fk_film_id on film_actor (cost=0.00..28.35 rows=6 width=2) (actual time=0.016..0.016 rows=1 loops=1000) Index Cond: ($0 = film_id) Total runtime: 22.835 ms (6 rows) postgres=# explain analyze select * from film f where film_id in (select film_id from film_actor); QUERY PLAN -- Hash Join (cost=117.01..195.42 rows=966 width=390) (actual time=43.151..53.688 rows=997 loops=1) Hash Cond: (f.film_id = film_actor.film_id) - Seq Scan on film f (cost=0.00..65.00 rows=1000 width=390) (actual time=0.021..6.765 rows=1000 loops=1) - Hash (cost=104.94..104.94 rows=966 width=2) (actual time=43.091..43.091 rows=997 loops=1) - HashAggregate (cost=95.28..104.94 rows=966 width=2) (actual time=34.754..36.275 rows=997 loops=1) - Seq Scan on film_actor (cost=0.00..81.62 rows=5462 width=2) (actual time=0.024..15.746 rows=5462 loops=1) Total runtime: 55.291 ms postgres=# explain analyze select * from film f where film_id in (select film_id from film_actor); QUERY PLAN Nested Loop IN Join (cost=0.00..175.25 rows=986 width=390) (actual time=0.090..14.272 rows=997 loops=1) - Seq Scan on film f (cost=0.00..65.00 rows=1000 width=390) (actual time=0.014..1.877 rows=1000 loops=1) - Index Scan using idx_fk_film_id on film_actor (cost=0.00..0.54 rows=6 width=2) (actual time=0.007..0.007 rows=1 loops=1000) Index Cond: (film_actor.film_id = f.film_id) Total runtime: 15.902 ms (5 rows) 8.4 postgres=# explain analyze select * from film f where film_id not in (select film_id from film_actor); QUERY PLAN Seq Scan on film f (cost=95.28..162.78 rows=500 width=390) (actual
Re: [HACKERS] Patch: plan invalidation vs stored procedures
2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? It is protection to server's hang? Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgres-R
[EMAIL PROTECTED] 写道: I wish to set up the Postgres-R environment, could you please let me know the steps for setting it up. Thanks. yeah, actually, i have not been successful to set up this, but let me give some information for you. 1. download the postgresql snapshot source code from here: http://www.postgresql.org/ftp/snapshot/dev/ (this is a daily tarball) 2. Get the corresponding patch for postgres-r from: http://www.postgres-r.org/downloads/ 3. apply the patch for snapshot source, and configure like this: ./configure --enable-replication make make install 4. install the GCS ensemble, according the document : http://www.cs.technion.ac.il/dsl/projects/Ensemble/doc.html 5. start ensemble daemon and gossip if neccessary ( yes, make sure the two nodes can 'GCS' each other) 3. Assume that you have two nodes, start up postgresql and create a database 'db', and create a table 'tb' for testing which should be have a primary key for all nodes. 4. At the origin node, execute the command at psql console: alter database db start replication in group gcs; (which means the database 'db' is the origin and the group 'gcs' is the GCS group name) 5. At the subscriber node, execute the command: alter database db accept replication from group gcs; Hope information above would be helpful, and keep in touch. leiyonghua -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] any psql static binary for iphone ?
I haven't looked at it but there's this: http://www.postgresql.org/about/news.988 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal sql: labeled function params
On Mon, 2008-08-18 at 08:53 +0200, Pavel Stehule wrote: 2008/8/17 Hannu Krosing [EMAIL PROTECTED]: On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote: Hannu it's not possible in plpgsql, because we are not able iterate via record. just add function for iterating over record :) it's not easy, when iterating should be fast - when record's field has different types, than isn't possible cache execution plan. the iterator should convert them to some common type like TEXT Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [SPAM?]: Re: [HACKERS] proposal sql: labeled function params
On Mon, 2008-08-18 at 10:51 +0300, Peter Eisentraut wrote: Am Saturday, 16. August 2008 schrieb Hannu Krosing: A label is the same thing as variable/attribute/argument name in all programming languages I can think of. Why do you need two kinds of argument names in postgreSQL ? maybe you are after something like keyword arguments in python ? http://docs.python.org/tut/node6.html#SECTION00672 keyword arguments are a way of saying that you don't know all variable names (or labels if you prefer) at function defining time and are going to pass them in when calling. I think we are beginning to talk about the same thing. (Meaning you and me -- not sure about the rest. :) ) Yes, I noticed that. Maybe we are onto something ;) The exact moment I sent my mail away, I received yours. Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
What I'm interested in is auto-tuning, not necessarily overhauling GUCS, which happens to be the subject of this thread :-) Having done a SELECT * FROM pg_settings, all the information you need seems to be there... Maybe I'm being over-simplistic here, but the important bit is knowing how you should tune stuff - and this is what I'm hoping to learn through this process. Now, you could probably sidestep UI and GUCS concerns by moving the auto-tuning process inside the database. You don't need fancy GUIs for guessing configuration parameters, and if you can already do that, coming up with a GUI should be pretty straightforward. For example, I see no reason why you couldn't capture the logic of tuning in a couple of PL/Python functions to look up usage stats, size of indices etc. PL/Python being an untrusted language, you could even write a new postgresql.conf file to disk, with the suggested alterations. Cheap, quick and cheerful! Perhaps the auto-tuning conversation should take place in a separate thread, how do you feel about changing the subject line? The most insteresting bit is discussing and testing tuning strategies. This, of course, is related to the [admin] and [perform] side of things, but there is also a development dimension. As soon as there is a tuning strategy published, a number of tools will certainly follow. Michael Yes, there's a grand plan for a super-wizard that queries the database for size, index, and statistics information for figure out what to do; I've been beating that drum for a while now. Unfortunately, the actual implementation is blocked behind the dreadfully boring work of sorting out how to organize and manage the GUC information a bit better, and the moderately boring work of building a UI for modifying things. If you were hoping to work on the sexy autotuning parts without doing some of the grunt work, let me know if you can figure out how so I can follow you.
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
Tom Lane wrote:
A further suggestion is that you use -d or even -D option on the dump.
I think there have been some corner-case changes in COPY data format
since 6.5 days; which might or might not bite you, but why take the
chance ...
%/usr/local/pgsql/bin/pg_dump -D itt_user itt_user.dump
Backend sent D message without prior T
Backend sent D message without prior T
...
... (about 2 screens of same messages)
...
Backend sent D message without prior T
Backend sent D message without prior T
and then it hangs.
Then i've tried it with -d option:
%/usr/local/pgsql/bin/pg_dump -d itt_user itt_user.dump
Killed
I didn't killed pg_dump, so i think it was killed by system after
pg_dump grows out of some system limit. Size of itt_user.dump is
something about 2Mb (1974272), last strings in that file are:
INSERT INTO ip_log VALUES ('1.1.1.1','user1',30);
INSERT INTO ip_log VALUES ('1.1.1.2','user2',50);
INSERT INTO ip_log VALUES
I crossed my fingers for those dumps i did previously to work.
--
alexander lunyov
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Compatibility types, type aliases, and distinct types
I have been hacking around for a while trying to create some example Oracle compatibility types. Canonical examples: varchar2 and number. With the new features in 8.3 and 8.4, such as user-definable typmods and type categories, it appears to be actually possible to create a type equivalent to numeric or varchar entirely in user space. Cool. Actually doing this, however, appears to be shockingly complicated. You need to redefine all the input/output/send/receive functions and all the cast functions and casts and then tie them all together. I don't expect that this is something a user would succeed in, and not even an experienced developer would want to type all that in. I actually had to write a script to generate all that code. So while thinking about how to make this simpler I remembered the distinct type feature of SQL, which works quite similarly, namely the new type has the same structure as the old type, but is a separate entity. It looks like CREATE TYPE newtype AS oldtype; This feature by itself could be quite useful, and then we could simply add something like CREATE TYPE newtype AS oldtype WITH CASTS; to copy all the casts as well, so the new type can be used in contexts where the old type could be used. There is also another possible way one might want to create a compatibility type. Instead of creating a new type, create an alias for an existing type, much like we currently have built-in mappings for int - int4, bigint - int8, etc. The difference here is that the type you put in is not the same as the one you get dumped out. So depending on taste and requirements, a user might want to choose the distinct type or the alias route. What do you think about adding this kind of support to PostgreSQL? Obviously, some details need to be worked out, but most of this is actually straightforward catalog manipulation. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] So what about XSLT?
Am Friday, 15. August 2008 schrieb Tom Lane: well, contrib/xml2/xslt_proc.c has 172 lines. So I suggest we just import that to core and drop the rest of the module as redundant. I assume that wouldn't provide the functionality Peter wants; else the above would have happened already in 8.3. Well, another part of my hesitation was the question whether we want to deal with yet another library. But since libxslt has since snuck into configure and is well-established, most of that concern goes away. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] any psql static binary for iphone ?
On Mon, 18 Aug 2008, Gregory Stark wrote: I haven't looked at it but there's this: http://www.postgresql.org/about/news.988 Yes, I know it. But, it's not free and one should use iTunes program to buy it from AppStore. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compatibility types, type aliases, and distinct types
* Peter Eisentraut ([EMAIL PROTECTED]) wrote: There is also another possible way one might want to create a compatibility type. Instead of creating a new type, create an alias for an existing type, much like we currently have built-in mappings for int - int4, bigint - int8, etc. The difference here is that the type you put in is not the same as the one you get dumped out. So depending on taste and requirements, a user might want to choose the distinct type or the alias route. The alias route gets me thinking about Oracle synonyms.. That'd be nice to have in PG for a number of object types. Most recently I was wishing I could create a schema synonym, though being able to do tables/views would have worked as well in that case, just a bit more work. What do you think about adding this kind of support to PostgreSQL? Obviously, some details need to be worked out, but most of this is actually straightforward catalog manipulation. I like the concept. Not sure how much I'd end up using it, personally. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Compatibility types, type aliases, and distinct types
In my experience synonyms as well as rules are hacks and should be avoided althou there are cases where they can save some work for dba's during transitions from one situation to better one. There is also another possible way one might want to create a compatibility type. Instead of creating a new type, create an alias for an existing type, much like we currently have built-in mappings for int - int4, bigint - int8, etc. The difference here is that the type you put in is not the same as the one you get dumped out. So depending on taste and requirements, a user might want to choose the distinct type or the alias route. Example or two would be helpful here where you expect this kind of functionality be useful. Could you use it for defining Oracle compatibel varchar2 and how would it work then? On Mon, Aug 18, 2008 at 3:33 PM, Stephen Frost [EMAIL PROTECTED] wrote: * Peter Eisentraut ([EMAIL PROTECTED]) wrote: There is also another possible way one might want to create a compatibility type. Instead of creating a new type, create an alias for an existing type, much like we currently have built-in mappings for int - int4, bigint - int8, etc. The difference here is that the type you put in is not the same as the one you get dumped out. So depending on taste and requirements, a user might want to choose the distinct type or the alias route. The alias route gets me thinking about Oracle synonyms.. That'd be nice to have in PG for a number of object types. Most recently I was wishing I could create a schema synonym, though being able to do tables/views would have worked as well in that case, just a bit more work. What do you think about adding this kind of support to PostgreSQL? Obviously, some details need to be worked out, but most of this is actually straightforward catalog manipulation. I like the concept. Not sure how much I'd end up using it, personally. Thanks, Stephen -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkipbCgACgkQrzgMPqB3kiinmwCfROrhdu8YDpzsJvOtvpSW147O SOQAn3y/4MGadFz9VqDsmcm8fiKuxsn5 =gdfU -END PGP SIGNATURE-
Re: [HACKERS] any psql static binary for iphone ?
On Mon, Aug 18, 2008 at 4:02 AM, Hans-Juergen Schoenig [EMAIL PROTECTED] wrote: Peter Eisentraut wrote: Am Sunday, 17. August 2008 schrieb Oleg Bartunov: is there psql static binary, which I can use on my iphone (version 1) ? I have no idea, but just as a thought, using phpPgAdmin might be a good workaround. postgres seems to compile nicely on the iphone. compilations stops at gram.c however :) the file is just too big to compile on 96MB of RAM :). first the screen turns to black and then it reboots. so far i have not seen how i can add a swap file to the iphone and i was too lazy to cross compile *g*. but until gram.c - no warning; no errors *g*. iirc you don't have to compile gram.c for psql? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] any psql static binary for iphone ?
postgres seems to compile nicely on the iphone. compilations stops at gram.c however :) the file is just too big to compile on 96MB of RAM :). first the screen turns to black and then it reboots. so far i have not seen how i can add a swap file to the iphone and i was too lazy to cross compile *g*. but until gram.c - no warning; no errors *g*. iirc you don't have to compile gram.c for psql? merlin no, not for psql ... i wanted to give pgbench a try. just plain curiosity. hans -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, A-2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql-support.de, www.postgresql-support.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] any psql static binary for iphone ?
On Mon, Aug 18, 2008 at 9:16 AM, Hans-Juergen Schoenig [EMAIL PROTECTED] compilations stops at gram.c however :) the file is just too big to compile on 96MB of RAM :). first the screen turns to black and then it reboots. so far i have not seen how i can add a swap file to the iphone and i was too lazy to cross compile *g*. but until gram.c - no warning; no errors *g*. iirc you don't have to compile gram.c for psql? no, not for psql ... i wanted to give pgbench a try. just plain curiosity. you of all people should know what to do next :-) http://osdir.com/ml/db.postgresql.advocacy/2004-03/msg00018.html merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgres-R
Thanks for the information.
For Step5 (starting ensemble daemon).-
I set the multicast address to both nodes (Node 1 Node 2 eth0: 224.0.0.9/4)
before starting the ensemble. And started the server application mtalk in node
1 node 2 and then client application in node 1 node 2. But the count of
members ('nmembers') show as 1. This is the output of the client program
'c_mtalk'. Seeing this, I'am assuming that the applications are not merged.
Could you please let me know how did you proceed with the setup of ensemble?
regards,
Niranjan
-Original Message-
From: ext leiyonghua [mailto:[EMAIL PROTECTED]
Sent: Monday, August 18, 2008 2:58 PM
To: K, Niranjan (NSN - IN/Bangalore); Markus Wanner;
pgsql-hackers@postgresql.org
Subject: Re: Postgres-R
[EMAIL PROTECTED] 写道:
I wish to set up the Postgres-R environment, could you please let me know the
steps for setting it up.
Thanks.
yeah, actually, i have not been successful to set up this, but let me give some
information for you.
1. download the postgresql snapshot source code from here:
http://www.postgresql.org/ftp/snapshot/dev/
(this is a daily tarball)
2. Get the corresponding patch for postgres-r from:
http://www.postgres-r.org/downloads/
3. apply the patch for snapshot source, and configure like this:
./configure --enable-replication
make make install
4. install the GCS ensemble, according the document :
http://www.cs.technion.ac.il/dsl/projects/Ensemble/doc.html
5. start ensemble daemon and gossip if neccessary ( yes, make sure the two
nodes can 'GCS' each other)
3. Assume that you have two nodes, start up postgresql and create a database
'db', and create a table 'tb' for testing which should be have a primary key
for all nodes.
4. At the origin node, execute the command at psql console:
alter database db start replication in group gcs; (which means the database
'db' is the origin and the group 'gcs' is the GCS group name)
5. At the subscriber node, execute the command:
alter database db accept replication from group gcs;
Hope information above would be helpful, and keep in touch.
leiyonghua
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compatibility types, type aliases, and distinct types
Peter Eisentraut [EMAIL PROTECTED] writes: So while thinking about how to make this simpler I remembered the distinct type feature of SQL, which works quite similarly, namely the new type has the same structure as the old type, but is a separate entity. It looks like CREATE TYPE newtype AS oldtype; This feature by itself could be quite useful, and then we could simply add something like CREATE TYPE newtype AS oldtype WITH CASTS; This seems like a great way to get lost in ambiguous function hell ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Make the pg_stat_activity view call a SRF
Tom Lane wrote: Thanks, and while I approve of that TODO, that's not actually the one I was talking about in the email. The one I was talking about was change builtin set-returning functions to use OUT parameters so you can query them without knowing the result format or something like that. So, please keep the one you added, but add this one as well. Uh, I need more details on this. Can you give an example? Good: regression=# select * from pg_get_keywords(); word| catcode |catdesc ---+-+--- abort | U | Unreserved absolute | U | Unreserved access| U | Unreserved ... Not so good: regression=# select * from pg_show_all_settings(); ERROR: a column definition list is required for functions returning record There's no longer any very good reason for built-in SRFs to not define their own output record type. TODO updated: * Fix all set-returning system functions so they support a wildcard target list SELECT * FROM pg_get_keywords() works but SELECT * FROM pg_show_all_settings() does not. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
On Aug 18, 2008, at 1:05 AM, Magnus Hagander wrote: Josh Berkus wrote: Steve, First pass is done. Needs a little cleanup before sharing. I spent a fair while down OS-specific-hardware-queries rathole, but I'm better now. Gods, I hope you gave up on that. You want to use SIGAR or something. If it's going to be C++, and reasonably cross platform, and a pg tool, why not try to build something as a module in pgAdmin? Certainly going to get you a larger exposure... And I'm sure the pgAdmin team would be happy to have it! I'm attempting to build it as something that can be used in several places. Where there's most need for it is as an install time option in installers, particularly on Windows. There's no reason the same underlying code couldn't also go into pgAdmin, of course. At the moment the code is a bit Qt specific, reducing that is part of the cleanup. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Steve Atkins wrote: On Aug 18, 2008, at 1:05 AM, Magnus Hagander wrote: Josh Berkus wrote: Steve, First pass is done. Needs a little cleanup before sharing. I spent a fair while down OS-specific-hardware-queries rathole, but I'm better now. Gods, I hope you gave up on that. You want to use SIGAR or something. If it's going to be C++, and reasonably cross platform, and a pg tool, why not try to build something as a module in pgAdmin? Certainly going to get you a larger exposure... And I'm sure the pgAdmin team would be happy to have it! I'm attempting to build it as something that can be used in several places. Where there's most need for it is as an install time option in installers, particularly on Windows. Well, if it was in pgadmin, it would be there more or less by default on Windows. And very easy to get in on other platforms, since pgadmin is already packaged there. Plus, all the dependencies are already there on said platforms. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal sql: labeled function params
Is this a TODO? --- Hannu Krosing wrote: On Mon, 2008-08-18 at 08:53 +0200, Pavel Stehule wrote: 2008/8/17 Hannu Krosing [EMAIL PROTECTED]: On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote: Hannu it's not possible in plpgsql, because we are not able iterate via record. just add function for iterating over record :) it's not easy, when iterating should be fast - when record's field has different types, than isn't possible cache execution plan. the iterator should convert them to some common type like TEXT Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal sql: labeled function params
On Mon, 2008-08-18 at 11:19 -0400, Bruce Momjian wrote: Is this a TODO? I don't think we have a TODO yet. Maybe a TBD :) --- Hannu Krosing wrote: On Mon, 2008-08-18 at 08:53 +0200, Pavel Stehule wrote: 2008/8/17 Hannu Krosing [EMAIL PROTECTED]: On Sun, 2008-08-17 at 17:59 +0200, Pavel Stehule wrote: Hannu it's not possible in plpgsql, because we are not able iterate via record. just add function for iterating over record :) it's not easy, when iterating should be fast - when record's field has different types, than isn't possible cache execution plan. the iterator should convert them to some common type like TEXT Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal sql: labeled function params
There may be a TODO in this thread somewhere, but I think this particular suggestion has drifted pretty far from the problem that Pavel was trying to solve. ...Robert On Mon, Aug 18, 2008 at 11:19 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Is this a TODO? it's not possible in plpgsql, because we are not able iterate via record. just add function for iterating over record :) it's not easy, when iterating should be fast - when record's field has different types, than isn't possible cache execution plan. the iterator should convert them to some common type like TEXT -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SeqScan costs
Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: On Tue, 2008-08-12 at 15:46 -0400, Tom Lane wrote: This is only going to matter for a table of 1 block (or at least very few blocks), and for such a table it's highly likely that it's in RAM anyway. So I'm unconvinced that the proposed change represents a better model of reality. I think the first block of a sequential scan is clearly a random access. If that doesn't represent reality well then perhaps we need to tackle both problems together. The point I was trying to make (evidently not too well) is that fooling around with fundamental aspects of the cost models is not something that should be done without any evidence. We've spent ten years getting the system to behave reasonably well with the current models, and it's quite possible that changing them to be more accurate according to a five-minute analysis is going to make things markedly worse overall. I'm not necessarily opposed to making this change --- it does sound kinda plausible --- but I want to see some hard evidence that it does more good than harm before we put it in. I don't want to see this thread completely drop because it also seems pretty plausible to me too. So what kind of evidence do we need? I'm thinking a query like select (select count(*) from 1pagetable) as n1, (select count(*) from 2pagetable) as n2, (select count(*) from 3pagetable) as n3, ... from fairlylargetable for various maximum size subquery tables would give an idea of how much cpu time is spent thrashing through the sequential scans. If we raise the cost of small sequential scans do the resulting costs get more accurate or do they get out of whack? Perhaps what's also needed here is to measure just how accurate the cpu_* costs are. Perhaps they need to be raised somewhat if we're underestimating the cost of digging through 200 tuples on a heap page and the benefit of a binary search on the index tuples. People lower random_page_cost because we're not doing a good job estimating how much of a table is in cache. Agreed, the elephant in the room is that we lack enough data to model caching effects with any degree of realism. It looks like we *do* discount the page accesses in index_pages_fetched based on effective_cache_size. But that's the *only* place we use effective_cache_size. We aren't discounting sequential scan or heap page accesses even when the entire table is much smaller than effective_cache_size and therefore hopefully cached. We need to think about this. I'm a bit concerned that if we assume small tables are always cached that we'll run into problems on the poor but common schema design that has hundreds of tiny tables. But that seems like a narrow use case and not worth assuming we *never* get any cache effects on sequential scans at all. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SeqScan costs
On Mon, 2008-08-18 at 16:44 +0100, Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: On Tue, 2008-08-12 at 15:46 -0400, Tom Lane wrote: This is only going to matter for a table of 1 block (or at least very few blocks), and for such a table it's highly likely that it's in RAM anyway. So I'm unconvinced that the proposed change represents a better model of reality. I think the first block of a sequential scan is clearly a random access. If that doesn't represent reality well then perhaps we need to tackle both problems together. The point I was trying to make (evidently not too well) is that fooling around with fundamental aspects of the cost models is not something that should be done without any evidence. We've spent ten years getting the system to behave reasonably well with the current models, and it's quite possible that changing them to be more accurate according to a five-minute analysis is going to make things markedly worse overall. I'm not necessarily opposed to making this change --- it does sound kinda plausible --- but I want to see some hard evidence that it does more good than harm before we put it in. I don't want to see this thread completely drop because it also seems pretty plausible to me too. So what kind of evidence do we need? I'm thinking a query like select (select count(*) from 1pagetable) as n1, (select count(*) from 2pagetable) as n2, (select count(*) from 3pagetable) as n3, ... from fairlylargetable for various maximum size subquery tables would give an idea of how much cpu time is spent thrashing through the sequential scans. If we raise the cost of small sequential scans do the resulting costs get more accurate or do they get out of whack? Sounds OK. I've not given up on this yet... Perhaps what's also needed here is to measure just how accurate the cpu_* costs are. Perhaps they need to be raised somewhat if we're underestimating the cost of digging through 200 tuples on a heap page and the benefit of a binary search on the index tuples. Well, that's a can of worms you just opened. I'm trying to suggest a specific fix to a specific problem. People lower random_page_cost because we're not doing a good job estimating how much of a table is in cache. Agreed, the elephant in the room is that we lack enough data to model caching effects with any degree of realism. It looks like we *do* discount the page accesses in index_pages_fetched based on effective_cache_size. But that's the *only* place we use effective_cache_size. We aren't discounting sequential scan or heap page accesses even when the entire table is much smaller than effective_cache_size and therefore hopefully cached. That is about block reuse within the same scan, that's why it only happens there. It doesn't assume the indexes are already cached, it just says that they will be if we scan heap blocks in index order. We need to think about this. I'm a bit concerned that if we assume small tables are always cached I've not suggested that and we don't currently assume that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] migrate data 6.5.3 - 8.3.1
alexander lunyov [EMAIL PROTECTED] writes: Tom Lane wrote: I wonder if you need these self defined aggregates at all, most or all of them are in 8.3 already. They aren't self defined in 6.5 either. So i can't just delete those AGGREGATEs? I think what is happening is that he's trying to force a 7.x pg_dump to dump from the 6.5 server (with -i no doubt), and it's just tremendously confused about what's what and what it should dump. These errors occurs when i'm trying to restore dump from 6.5 on 8.3. I took a trip down memory lane looking at the 6.5 pg_dump code ... the way it decides whether an object is built-in or user-defined is by looking at its OID, using the rule that any OID less than template1's OID must be built in. Which actually ought to mostly work. I speculate that template1 got dropped and recreated sometime during this installation's history. That would still not produce the observed symptoms, except that the OID comparisons are all done using signed integer comparisons. So if the new template1 had an OID past 2 billion, pretty much everything would look like it was user-defined. Were you getting bogus duplicate type and operator definitions as well as aggregates? Can we see the results of select oid, datname from pg_database in the 6.5 installation? There might be some other candidate you could rename to template1 to get this to work better. Of course, since you got the data migrated you might not care anymore. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SeqScan costs
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: I'm not necessarily opposed to making this change --- it does sound kinda plausible --- but I want to see some hard evidence that it does more good than harm before we put it in. I don't want to see this thread completely drop because it also seems pretty plausible to me too. So what kind of evidence do we need? I'm thinking a query like select (select count(*) from 1pagetable) as n1, (select count(*) from 2pagetable) as n2, (select count(*) from 3pagetable) as n3, ... from fairlylargetable Well, no, because there's no freedom of choice there. What we want is to find out how this change impacts seqscan vs indexscan choices for small tables, and then whether those choices get better or worse. Perhaps what's also needed here is to measure just how accurate the cpu_* costs are. Perhaps they need to be raised somewhat if we're underestimating the cost of digging through 200 tuples on a heap page and the benefit of a binary search on the index tuples. Possibly. I doubt anyone's ever taken a hard look at the cpu_xxx values. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] any psql static binary for iphone ?
On Monday 18 August 2008 03:47:06 Peter Eisentraut wrote:
Am Sunday, 17. August 2008 schrieb Oleg Bartunov:
is there psql static binary, which I can use on my iphone (version 1) ?
I have no idea, but just as a thought, using phpPgAdmin might be a good
workaround.
I've played with this a little and it works ok, though navigating the tree is
a little tricky. I think it might be better to disable the tree bits all
together (you can navigate within the main window as well), but I've not
spent that much time on it. Note, if anyone wants to donate a iphone to the
phppgadmin project, I'd be happy to spend some time making this work
better. :-)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
On Mon, 18 Aug 2008, Michael Nacos wrote: Having done a SELECT * FROM pg_settings, all the information you need seems to be there... See http://archives.postgresql.org/pgsql-hackers/2008-06/msg00209.php You sound like you're at rung 2 on the tool author ladder I describe there, still thinking about the fun part of tuning but not yet aware of the annoying postgresql.conf management issues that show up in the field that motivate many of the GUCS changes suggested. Coping with user and system-generated comments is one difficult part that people normally don't consider, dealing with bad settings the server won't start with is another. I did make one mistake in that message, which is that the context field of pg_settings already exposes when a setting can be changed. And it is possible to get the value for a setting as entered by the admin by joining pg_settings against what current_setting returns, which is one part of handling the import/change/export cycle while keeping useful units intact. Maybe I'm being over-simplistic here, but the important bit is knowing how you should tune stuff - and this is what I'm hoping to learn through this process. The tuning references at the bottom of http://wiki.postgresql.org/wiki/GUCS_Overhaul provide more detail here than anyone has been able to automate so far. There's certainly room to improve on the suggestions there with some introspection of the database, I'm trying to stay focused on something to help newbies whose settings are completely wrong first. As soon as there is a tuning strategy published, a number of tools will certainly follow. Josh Berkus published one in 2005 and zero such tools have been produced since then, even though it looked to him then (like it does to you now and like it did to me once) that such a tool would easily follow: http://pgfoundry.org/docman/?group_id=1000106 The bright side here is that you don't have to waste time tinkering in this area to find out where the dead ends are like Josh and I independantly did. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compatibility types, type aliases, and distinct types
On Monday 18 August 2008 17:26:16 Tom Lane wrote: This feature by itself could be quite useful, and then we could simply add something like CREATE TYPE newtype AS oldtype WITH CASTS; This seems like a great way to get lost in ambiguous function hell ... I don't understand this point. No new overloaded functions are being defined. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench duration option
Greg Smith [EMAIL PROTECTED] writes: On Tue, 12 Aug 2008, Tom Lane wrote: This seems like a fairly bad idea, because it introduces a gettimeofday() call per transaction. There's already lots of paths through pgbench that introduce gettimeofday calls all over the place. I fail to see how this is any different. You haven't thought about it very hard then. The gettimeofday calls that are in there are mostly at run startup and shutdown. The ones that can occur intra-run are associated with * the seldom-used log-each-transaction option, which pretty obviously is a drag on performance anyway; or * the seldom-used \sleep command, which also obviously affects pgbench's ability to process transactions fast. I repeat my concern that transaction rates measured with this patch will be significantly different from those seen with the old code, and that confusion will ensue, and that it's not hard to see how to avoid that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Text Selectivity Operators in String Types
Howdy, A while back, thanks to feedback from RhodiumToad on #postgresql, I added selectivity functions to the citext operators: CREATE OPERATOR = ( LEFTARG= CITEXT, RIGHTARG = CITEXT, COMMUTATOR = =, NEGATOR= , PROCEDURE = citext_eq, RESTRICT = eqsel, JOIN = eqjoinsel, HASHES, MERGES ); However, it looks like these might be less than ideal: http://www.depesz.com/index.php/2008/08/10/waiting-for-84-case-insensitive-text-citext/ From what depesz sees, the eqsel and eqjoinsel functions might be a bit too aggressive in recommending the use of indexes. So I was wondering, since in CVS HEAD citext is able to identify its type category as string, might there also be some way to use the same estimator functions in the citext operators as are used for text? Thanks, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Compatibility types, type aliases, and distinct types
Peter Eisentraut [EMAIL PROTECTED] writes: On Monday 18 August 2008 17:26:16 Tom Lane wrote: This feature by itself could be quite useful, and then we could simply add something like CREATE TYPE newtype AS oldtype WITH CASTS; This seems like a great way to get lost in ambiguous function hell ... I don't understand this point. No new overloaded functions are being defined. If the type has no functions of its own, then the only way to make it easily usable is to throw in implicit conversions *in both directions* between it and the type it's an alias for. You're going to find that that's a problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Greg Smith [EMAIL PROTECTED] writes: On Mon, 18 Aug 2008, Michael Nacos wrote: Having done a SELECT * FROM pg_settings, all the information you need seems to be there... See http://archives.postgresql.org/pgsql-hackers/2008-06/msg00209.php You sound like you're at rung 2 on the tool author ladder I describe there, still thinking about the fun part of tuning but not yet aware of the annoying postgresql.conf management issues that show up in the field that motivate many of the GUCS changes suggested. Coping with user and system-generated comments is one difficult part that people normally don't consider, Because coping with free-form user-edited text is a losing game. People don't consider it because it's a dead-end. Instead you have one file for user-edited configuration and a separate file for computer generated configuration. You never try to automatically edit a user-edited file -- that way lies madness. dealing with bad settings the server won't start with is another. A tuning interface can't be turing complete and detect all possible misconfigurations. To do that it would have to be as complex as the server. In any case worrying about things like this before you have a tuning interface that can do the basics is putting the cart before the horse. As soon as there is a tuning strategy published, a number of tools will certainly follow. Josh Berkus published one in 2005 and zero such tools have been produced since then, even though it looked to him then (like it does to you now and like it did to me once) that such a tool would easily follow: http://pgfoundry.org/docman/?group_id=1000106 The entire target market for such a thing is DBAs stuck on hosted databases which don't have shell access to their machines. Perhaps the overlap between that and the people who can write a server-side module which dumps out a config file according to some rules is just too small? I do think you and others make it less likely every time you throw up big insoluble problems like above though. As a consequence every proposal has started with big overly-complex solutions trying to solve all these incidental issues which never go anywhere instead of simple solutions which directly tackle the main problem. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Text Selectivity Operators in String Types
David E. Wheeler [EMAIL PROTECTED] writes: From what depesz sees, the eqsel and eqjoinsel functions might be a bit too aggressive in recommending the use of indexes. He presented absolutely 0 evidence to back up that opinion. So I was wondering, since in CVS HEAD citext is able to identify its type category as string, might there also be some way to use the same estimator functions in the citext operators as are used for text? Those are the same estimator functions. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Greg, The entire target market for such a thing is DBAs stuck on hosted databases which don't have shell access to their machines. That's incorrect. The main reason for having a port-based API (such as the SQL command line) for managing your server is that it makes it much easier to manage a large number of servers. Right now, if you want to survey your databases, tables, approx disk space, query activity, etc., you can do that all through port 5432. You can't manage most of your server settings that way, and definitely can't manage the *persistent* settings. When you're trying to manage 1000 PostgreSQL servers, this is not a minor issue. With the growing cloud sector, the lack of easy server parameter management is hurting PostgreSQL's adoption for hosted applications. This isn't a new complaint, and is a big part of the reason why 90% of web hosts still don't offer PostgreSQL. I've heard complaints about our manageability problems from more vendors than I can count. HOWEVER, it's completely possible to get a 1st-generation config tool out there without first implementing port-based config access. For one thing, there's Puppet. So that's what I'm intending to do. I do think you and others make it less likely every time you throw up big insoluble problems like above though. It's not an insoluble problem. It's a political problem; several people don't want to add this functionality to the project. As a consequence every proposal has started with big overly-complex solutions trying to solve all these incidental issues which never go anywhere instead of simple solutions which directly tackle the main problem. What, in your opinion, is the main problem? I'm not sure we agree on that. -- --Josh Josh Berkus PostgreSQL San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Gregory Stark [EMAIL PROTECTED] writes: The entire target market for such a thing is DBAs stuck on hosted databases which don't have shell access to their machines. Perhaps the overlap between that and the people who can write a server-side module which dumps out a config file according to some rules is just too small? There's a veritable boatload of stuff we do that assumes shell access (how many times have you seen cron jobs recommended, for instance?). So I'm unconvinced that modify the config without shell access is really a goal that is worth lots of effort. In any case, there's already adequate support for sucking postgresql.conf out of the machine and putting it back: pg_read_file(), pg_file_write(), pg_reload_conf(). So at the end of the day remote access isn't a factor in this at all. I do think you and others make it less likely every time you throw up big insoluble problems like above though. As a consequence every proposal has started with big overly-complex solutions trying to solve all these incidental issues which never go anywhere instead of simple solutions which directly tackle the main problem. The impression I get every time this comes up is that various people have different problems they want to solve that (they think) require redesign of the way GUC works. Those complicated solutions arise from attempting to satisfy N different demands simultaneously. The fact that many of these goals aren't subscribed to by the whole community to begin with doesn't help to ease resolution of the issues. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Text Selectivity Operators in String Types
On Aug 18, 2008, at 12:18, Tom Lane wrote: David E. Wheeler [EMAIL PROTECTED] writes: From what depesz sees, the eqsel and eqjoinsel functions might be a bit too aggressive in recommending the use of indexes. He presented absolutely 0 evidence to back up that opinion. So I was wondering, since in CVS HEAD citext is able to identify its type category as string, might there also be some way to use the same estimator functions in the citext operators as are used for text? Those are the same estimator functions. Oh. Okay then. That was easy. Thanks! Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] IN vs EXISTS equivalence
On Sun, Aug 17, 2008 at 4:29 PM, in message [EMAIL PROTECTED], Tom Lane [EMAIL PROTECTED] wrote: If you're still interested in testing CVS HEAD's handling of EXISTS, I've about finished what I wanted to do with it. Thanks. I'm very interested; unfortunately I can't get to it until at least Friday. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Josh Berkus [EMAIL PROTECTED] writes: Greg, The entire target market for such a thing is DBAs stuck on hosted databases which don't have shell access to their machines. That's incorrect. The main reason for having a port-based API (such as the SQL command line) for managing your server is that it makes it much easier to manage a large number of servers. Right now, if you want to survey your databases, tables, approx disk space, query activity, etc., you can do that all through port 5432. You can't manage most of your server settings that way, and definitely can't manage the *persistent* settings. When you're trying to manage 1000 PostgreSQL servers, this is not a minor issue. This I don't understand. If you're managing lots of servers running lots of software the last thing you want to have to do is write a custom method for updating the configuration of each service. In that use case you would prefer to just use rsync/svn/git to push the new config file to all the machines anyways. With the growing cloud sector, the lack of easy server parameter management is hurting PostgreSQL's adoption for hosted applications. This isn't a new complaint, and is a big part of the reason why 90% of web hosts still don't offer PostgreSQL. I've heard complaints about our manageability problems from more vendors than I can count. These are both use cases which fall in the category I described where you want to allow users to configure the system through an automated interface. We can do that today by generating the automatically generated section and including that in postgresql.conf as an include file. As a consequence every proposal has started with big overly-complex solutions trying to solve all these incidental issues which never go anywhere instead of simple solutions which directly tackle the main problem. What, in your opinion, is the main problem? I'm not sure we agree on that. The main problem that I've seen described is what I mentioned before: allowing adjusting the postgresql.conf GUC settings by remote users who don't have shell access. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Greg, The main problem that I've seen described is what I mentioned before: allowing adjusting the postgresql.conf GUC settings by remote users who don't have shell access. Oh, ok. I think we're in agreement, though. I don't think that's the *1st* problem to be solved, but it's definitely important. -- --Josh Josh Berkus PostgreSQL San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
On Mon, Aug 18, 2008 at 8:51 PM, Gregory Stark [EMAIL PROTECTED] wrote: The main problem that I've seen described is what I mentioned before: allowing adjusting the postgresql.conf GUC settings by remote users who don't have shell access. Which pgAdmin has done perfectly well for years, as long as the config is all in one file. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Extending varlena
Folks, As the things stored in databases grow, we're going to start needing to think about database objects that 4 bytes of size can't describe. People are already storing video in lo and bytea fields. To date, the sizes of media files have never trended downward. What would need to happen for the next jump up from where varlena is now, to 8 bytes? Would we want to use the bit-stuffing model that the current varvarlena uses? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
On Mon, 18 Aug 2008, Gregory Stark wrote: Because coping with free-form user-edited text is a losing game. People don't consider it because it's a dead-end. Right, that's impossible technology to build, which is why I had to plan all these screen shots showing tools that handle that easily for Apache's very similar configuration file: http://www.apache-gui.com/apache-windows.html http://kochizz.sourceforge.net/quelques-captures-decran/ Instead you have one file for user-edited configuration and a separate file for computer generated configuration. It wouldn't be so difficult if the system generated postgresql.conf didn't have all this extra junk in it, which is part of what an overhaul plans to simplify. The way the file gets spit out right now encourages some of the awful practices people develop in the field. A tuning interface can't be turing complete and detect all possible misconfigurations. To do that it would have to be as complex as the server. Thank you for supporting the case for why changes need to be to the server code itself, to handle things like validating new postgresql.conf files before they get loaded. I try not to bring that up lest it complicate things further. The entire target market for such a thing is DBAs stuck on hosted databases which don't have shell access to their machines. I've never setup a hosted database on a system I don't have shell access to, so I have no idea where you get the impression that was a primary goal of anything I've said. It just so happens that improving what tuning you can do over port 5432 helps that crowd out too, that's a bonus as I see it. Ask me about EnterpriseDB's On-Demand Production Tuning ...nah, too easy, I'll just let that go. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
David Fetter [EMAIL PROTECTED] writes: What would need to happen for the next jump up from where varlena is now, to 8 bytes? Dealing with upwards-of-4GB blobs as single Datums isn't remotely sane, and won't become so in the near (or even medium) future. So I don't see the point of doing all the work that would be involved in making this go. What would make more sense is to redesign the large-object stuff to be somewhat modern and featureful, and provide stream-access APIs (think lo_read, lo_seek, etc) that allow offsets wider than 32 bits. The main things I think we'd need to consider besides just the access API are - permissions features (more than none anyway) - better management of orphaned objects (obsoleting vacuumlo) - support 16TB of large objects (maybe partition pg_largeobject?) - dump and restore probably need improvement to be practical for such large data volumes regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench duration option
On Mon, 18 Aug 2008, Tom Lane wrote: the seldom-used log-each-transaction option, which pretty obviously is a drag on performance anyway I always recommend that people run with log each transaction turned on, beause it's the only way to gather useful latency information. I think runs that measure TPS without even considering that are much less useful. The fact that it's seldom used is something I consider a problem with pgbench. I repeat my concern that transaction rates measured with this patch will be significantly different from those seen with the old code Last time I tried to quantify the overhead of logging with timestamps on I couldn't even measure its impact, it was lower than the usual pgbench noise. That was on Linux. Do you have a suggestion for a platform that it's a problem on that I might be able to use? I'd like to do some measurements. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote: 2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? Not in the long run, but not invalidating the functions (current behaviour) postpones seeing the results of function change until DBA manually restarts the error-producing client. It is protection to server's hang? Can't understand this question :( If you mean, does the change protect against hanging the server, then no, currently the server does not actually hang, it just becomes unusable until reconnect :( - Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Dave Page wrote: On Mon, Aug 18, 2008 at 8:51 PM, Gregory Stark [EMAIL PROTECTED] wrote: The main problem that I've seen described is what I mentioned before: allowing adjusting the postgresql.conf GUC settings by remote users who don't have shell access. Which pgAdmin has done perfectly well for years, as long as the config is all in one file. I'll argue it's not done it perfectly well (it's not particularly user-friendly), but it has certainly *done* it... //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
2008/8/18 Hannu Krosing [EMAIL PROTECTED]: On Mon, 2008-08-18 at 11:05 +0200, Pavel Stehule wrote: 2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? Not in the long run, but not invalidating the functions (current behaviour) postpones seeing the results of function change until DBA manually restarts the error-producing client. It is protection to server's hang? Can't understand this question :( If you mean, does the change protect against hanging the server, then no, currently the server does not actually hang, it just becomes unusable until reconnect :( Hi I am sorry, but it's really new feature and not bug fix Pavel - Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Does it change of result some queries? Patch in itself is not changing what the queries return. It just gets rid of error condition from which Postgres itself is not able to recover. It is protection to server's hang? For users of stored procedures it is protection from downtime. For Skype it has been around 20% of databse related downtime this year. On Mon, Aug 18, 2008 at 12:05 PM, Pavel Stehule [EMAIL PROTECTED]wrote: 2008/8/18 Dimitri Fontaine [EMAIL PROTECTED]: Hi, Le lundi 18 août 2008, Andrew Dunstan a écrit : On Sat, Aug 16, 2008 at 09:40:19PM -0400, Tom Lane wrote: This is not the kind of patch we put into stable branches. So what? That is not the only criterion for backpatching. I fail to understand why this problem is not qualified as a bug. Does it change of result some queries? It is protection to server's hang? Regards, -- dim
Re: [HACKERS] Extending varlena
On Mon, Aug 18, 2008 at 04:22:56PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: What would need to happen for the next jump up from where varlena is now, to 8 bytes? Dealing with upwards-of-4GB blobs as single Datums isn't remotely sane, and won't become so in the near (or even medium) future. So I don't see the point of doing all the work that would be involved in making this go. OK What would make more sense is to redesign the large-object stuff to be somewhat modern and featureful, and provide stream-access APIs (think lo_read, lo_seek, etc) that allow offsets wider than 32 bits. Great! The main things I think we'd need to consider besides just the access API are - permissions features (more than none anyway) Would ROLEs work, or are you thinking of the per-row and per-column access controls people sometimes want? - better management of orphaned objects (obsoleting vacuumlo) - support 16TB of large objects (maybe partition pg_largeobject?) - dump and restore probably need improvement to be practical for such large data volumes That, and the usual upgrade-in-place :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
On 8/18/08, Magnus Hagander [EMAIL PROTECTED] wrote: Dave Page wrote: On Mon, Aug 18, 2008 at 8:51 PM, Gregory Stark [EMAIL PROTECTED] wrote: The main problem that I've seen described is what I mentioned before: allowing adjusting the postgresql.conf GUC settings by remote users who don't have shell access. Which pgAdmin has done perfectly well for years, as long as the config is all in one file. I'll argue it's not done it perfectly well (it's not particularly user-friendly), but it has certainly *done* it... I mean it's able to read write the config file correctly. I agree the ui is, umm, sub-optimal. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Overhauling GUCS
Tom Lane escribió: Gregory Stark [EMAIL PROTECTED] writes: The entire target market for such a thing is DBAs stuck on hosted databases which don't have shell access to their machines. Perhaps the overlap between that and the people who can write a server-side module which dumps out a config file according to some rules is just too small? There's a veritable boatload of stuff we do that assumes shell access (how many times have you seen cron jobs recommended, for instance?). So I'm unconvinced that modify the config without shell access is really a goal that is worth lots of effort. Actually, lots of people are discouraged by suggestions of using cron to do anything. The only reason cron is suggested is because we don't have any other answer, and for many people it's a half-solution. An integrated task scheduler in Pg would be more than welcome. Also, remember that pgAdmin already comes with a pgAgent thing. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PostgreSQL Conference: West - Call for Papers
The second annual PostgreSQL Conference: West is being held on October 10th through October 12th 2008 in the The Native American Student Community Center at Portland State University. We are currently accepting papers and you can submit your talks here: http://www.postgresqlconference.org/west08/talk_submission/ We have already seen submissions on Tsearch2 as well as pgTap. Do you have something you would like to share about PostgreSQL? Now is the time! This year West will be providing its proceeds to the Postgresql.us. Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
David Fetter wrote: Folks, As the things stored in databases grow, we're going to start needing to think about database objects that 4 bytes of size can't describe. People are already storing video in lo and bytea fields. To date, the sizes of media files have never trended downward. I always find these requests puzzling. Is it really useful to store the data for a jpeg, video file or a 10GB tar ball in a database column? Does anyone actually search for byte sequences within those data streams (maybe if it were text)? I would think that the metadata is what gets searched: title, track, name, file times, size, etc... Database storage is normally pricey, stocked with 15K drives, so wasting that expensive storage with non-searchable binary blobs doesn't make much sense. Why not offload the data to a file system with 7200 RPM SATA drives and store a reference to it in the db? Keep the db more compact and simpler to manage. Andrew Chernow eSilo, LLC -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench duration option
Greg Smith wrote: On Mon, 18 Aug 2008, Tom Lane wrote: I repeat my concern that transaction rates measured with this patch will be significantly different from those seen with the old code Last time I tried to quantify the overhead of logging with timestamps on I couldn't even measure its impact, it was lower than the usual pgbench noise. There's a hardware deficiency on certain machines -- I think it's old ones. I don't know if machines that would currently be used in production would contain such a problem. In any case, I think using SIGALRM as proposed by Tom is a very easy way out of the problem. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
On Mon, Aug 18, 2008 at 06:09:13PM -0400, Andrew Chernow wrote: David Fetter wrote: Folks, As the things stored in databases grow, we're going to start needing to think about database objects that 4 bytes of size can't describe. People are already storing video in lo and bytea fields. To date, the sizes of media files have never trended downward. I always find these requests puzzling. Is it really useful to store the data for a jpeg, video file or a 10GB tar ball in a database column? It is if you need transaction semantics. Think medical records, etc. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
David Fetter wrote: On Mon, Aug 18, 2008 at 06:09:13PM -0400, Andrew Chernow wrote: David Fetter wrote: Folks, As the things stored in databases grow, we're going to start needing to think about database objects that 4 bytes of size can't describe. People are already storing video in lo and bytea fields. To date, the sizes of media files have never trended downward. I always find these requests puzzling. Is it really useful to store the data for a jpeg, video file or a 10GB tar ball in a database column? It is if you need transaction semantics. Think medical records, etc. Cheers, David. I see that, although developing the middleware between db and fs is rather trival. I think that is the puzzling part. It just feels akward to me to just stuff it in the db. You can do more by distributing. Anyways (back on topic), I am in favor of removing limits from any section of the database ... not just your suggestion. The end-user application should impose limits. Andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
Andrew, I always find these requests puzzling. Is it really useful to store the data for a jpeg, video file or a 10GB tar ball in a database column? Some people find it useful. Because LOs are actually easier to manage in PG than in most other DBMSes, right now that's a significant source of PostgreSQL adoption. I'd like to encourage those users by giving them more useful LO features. -- --Josh Josh Berkus PostgreSQL San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
I always find these requests puzzling. Is it really useful to store the data for a jpeg, video file or a 10GB tar ball in a database column? One use case is that it can use the existing postgresql protocol, and does not require extra filesystem mounts, extra error handling, and other complexity. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
Andrew Chernow [EMAIL PROTECTED] writes: Anyways (back on topic), I am in favor of removing limits from any section of the database ... not just your suggestion. The end-user application should impose limits. That's nice as an abstract principle, but there are only so many hours in the day, so we need to prioritize which limits we're going to get rid of. The 4-byte limit on individual Datum sizes does not strike me as a limit that's going to be significant for practical use any time soon. (I grant David's premise that people will soon want to work with objects that are larger than that --- but not that they'll want to push them around as indivisible, store-and-fetch-as-a-unit field values.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
Tom Lane wrote: Andrew Chernow [EMAIL PROTECTED] writes: Anyways (back on topic), I am in favor of removing limits from any section of the database ... not just your suggestion. The end-user application should impose limits. That's nice as an abstract principle, but there are only so many hours in the day, so we need to prioritize which limits we're going to get rid of. The 4-byte limit on individual Datum sizes does not strike me as a limit that's going to be significant for practical use any time soon. (I grant David's premise that people will soon want to work with objects that are larger than that --- but not that they'll want to push them around as indivisible, store-and-fetch-as-a-unit field values.) regards, tom lane Yeah, my comments were overly general. I wasn't suggesting attention be put on one limit over another. I was only saying that the act of removing a limit (of which many are arbitrary) is most often a good one. andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
Jeff Davis wrote: I always find these requests puzzling. Is it really useful to store the data for a jpeg, video file or a 10GB tar ball in a database column? One use case is that it can use the existing postgresql protocol, So can what I am suggesting. How about a user-defined C function in the backend that talks to the fs and uses SPI to sync info with a record? Now the operation is behind a transaction. Yet, one must handle fs orphans from evil crash cases. Just one solution, but other more creative cats may have better ideas. the point is, it can be done without too much effort. A little TLC :) Andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
David Fetter [EMAIL PROTECTED] writes: On Mon, Aug 18, 2008 at 04:22:56PM -0400, Tom Lane wrote: The main things I think we'd need to consider besides just the access API are - permissions features (more than none anyway) Would ROLEs work, or are you thinking of the per-row and per-column access controls people sometimes want? Well, obviously roles are the entities that receive permissions, but on what do we base granting permissions to LOs? With the current model that a LO is an independent entity that is merely referenced (or not) by OIDs in the database, it seems like we'd have to grant/revoke permissions to individual LOs, identified by OID; which sure seems messy to me. People don't really want to name their LOs by OID anyway --- it's just a convention that's forced on them by the current implementation. I was kinda wondering about something closer to the TOAST model, where a blob is only referenceable from a value that's in a table field; and that value encapsulates the name of the blob in some way that needn't even be user-visible. This'd greatly simplify the cleanup-dead-objects problem, and we could avoid addressing the permissions problem at all, since regular SQL permissions on the table would serve fine. But it's not clear what regular SQL fetch and update behaviors should be like for such a thing. (Fetching or storing the whole blob value is right out, IMHO.) ISTR hearing of concepts roughly like this in other DBs --- does it ring a bell for anyone? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
David Fetter [EMAIL PROTECTED] writes: On Mon, Aug 18, 2008 at 06:09:13PM -0400, Andrew Chernow wrote: I always find these requests puzzling. Is it really useful to store the data for a jpeg, video file or a 10GB tar ball in a database column? It is if you need transaction semantics. Think medical records, etc. The basic problem with outside-the-DB data storage is keeping it in sync with your inside-the-DB metadata. In a slowly changing dataset you can probably get away with external storage, but if there's lots of updates then allowing the DB to manage the storage definitely makes life easier. This is not to say that you want SQL-style operations on the blobs; in fact I think you probably don't, which is why I was pointing to a LO-style API. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench duration option
Alvaro Herrera [EMAIL PROTECTED] writes: Greg Smith wrote: Last time I tried to quantify the overhead of logging with timestamps on I couldn't even measure its impact, it was lower than the usual pgbench noise. There's a hardware deficiency on certain machines -- I think it's old ones. I don't know if machines that would currently be used in production would contain such a problem. My understanding is that it's basically cheap PC hardware (with clock interfaces based on old ISA bus specs) that has the issue in a significant way. I wouldn't expect you to see it on a serious database server. But lots of people still do development on cheap PC hardware, which is why I think this is worth worrying about. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: plan invalidation vs stored procedures
Asko Oja [EMAIL PROTECTED] writes: For users of stored procedures it is protection from downtime. For Skype it has been around 20% of databse related downtime this year. Perhaps Skype needs to rethink how they are modifying functions. The reason that this case wasn't covered in 8.3 is that there didn't seem to be a use-case that justified doing the extra work. I still haven't seen one. Other than inline-able SQL functions there is no reason to invalidate a stored plan based on the fact that some function it called changed contents. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
On Mon, Aug 18, 2008 at 07:31:04PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: On Mon, Aug 18, 2008 at 04:22:56PM -0400, Tom Lane wrote: The main things I think we'd need to consider besides just the access API are - permissions features (more than none anyway) Would ROLEs work, or are you thinking of the per-row and per-column access controls people sometimes want? Well, obviously roles are the entities that receive permissions, but on what do we base granting permissions to LOs? With the current model that a LO is an independent entity that is merely referenced (or not) by OIDs in the database, it seems like we'd have to grant/revoke permissions to individual LOs, identified by OID; which sure seems messy to me. People don't really want to name their LOs by OID anyway --- it's just a convention that's forced on them by the current implementation. I was kinda wondering about something closer to the TOAST model, where a blob is only referenceable from a value that's in a table field; and that value encapsulates the name of the blob in some way that needn't even be user-visible. This vaguely reminds me of Sybase's hidden primary keys. This'd greatly simplify the cleanup-dead-objects problem, and we could avoid addressing the permissions problem at all, since regular SQL permissions on the table would serve fine. But it's not clear what regular SQL fetch and update behaviors should be like for such a thing. (Fetching or storing the whole blob value is right out, IMHO.) ISTR hearing of concepts roughly like this in other DBs --- does it ring a bell for anyone? Informix has some pretty good blob-handling: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlr.doc/sqlrmst101.htm Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
David Fetter wrote ... This'd greatly simplify the cleanup-dead-objects problem, and we could avoid addressing the permissions problem at all, since regular SQL permissions on the table would serve fine. But it's not clear what regular SQL fetch and update behaviors should be like for such a thing. (Fetching or storing the whole blob value is right out, IMHO.) ISTR hearing of concepts roughly like this in other DBs --- does it ring a bell for anyone? Informix has some pretty good blob-handling: http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqlr.doc/sqlrmst101.htm Agreed. I used Informix a few years back in a system that scanned both sides of multi-page financial documents; we stored them in Informix' blobs, which IIRC could be tuned to be given number of bytes. We found that 90% of our images fit in a given size and since Informix raw disk access let them move up the whole blob in a single pass, it was quite fast, and gave us all the warmth and fuzziness of ACID functionality. But we didn't fetch parts of the BLOB -- metadata lived in its own table. There is/was an Illustra/Informix blade which let you in theory do some processing of images (indexing) but that seems like a very specialized case. Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.)
Re: [HACKERS] Postgres-R
hi,
Assume that we have two node
node 0 , 192.168.0.2
node 1 , 192.168.0.3
1. add a host entry in /etc/hosts for hostname resolving.
2. add the host list in configuration 'ensemble.conf' for gossip service:
ENS_GOSSIP_HOSTS=node0:node1
3. set the envrionment variable ENS_CONFIG_FILE
export ENS_CONFIG_FILE=/xxx/xxx/ensemble.conf
4. start ensemble gossip
5. try 'c_mtalk' and happy.
this is a simplest case for me, hehe!
leiyonghua
K, Niranjan (NSN - IN/Bangalore) 写道:
Thanks for the information.
For Step5 (starting ensemble daemon).-
I set the multicast address to both nodes (Node 1 Node 2 eth0: 224.0.0.9/4)
before starting the ensemble. And started the server application mtalk in
node 1 node 2 and then client application in node 1 node 2. But the count
of members ('nmembers') show as 1. This is the output of the client program
'c_mtalk'. Seeing this, I'am assuming that the applications are not merged.
Could you please let me know how did you proceed with the setup of ensemble?
regards,
Niranjan
-Original Message-
From: ext leiyonghua [mailto:[EMAIL PROTECTED]
Sent: Monday, August 18, 2008 2:58 PM
To: K, Niranjan (NSN - IN/Bangalore); Markus Wanner;
pgsql-hackers@postgresql.org
Subject: Re: Postgres-R
[EMAIL PROTECTED] 写道:
I wish to set up the Postgres-R environment, could you please let me know
the steps for setting it up.
Thanks.
yeah, actually, i have not been successful to set up this, but let me give
some information for you.
1. download the postgresql snapshot source code from here:
http://www.postgresql.org/ftp/snapshot/dev/
(this is a daily tarball)
2. Get the corresponding patch for postgres-r from:
http://www.postgres-r.org/downloads/
3. apply the patch for snapshot source, and configure like this:
./configure --enable-replication
make make install
4. install the GCS ensemble, according the document :
http://www.cs.technion.ac.il/dsl/projects/Ensemble/doc.html
5. start ensemble daemon and gossip if neccessary ( yes, make sure the two
nodes can 'GCS' each other)
3. Assume that you have two nodes, start up postgresql and create a database
'db', and create a table 'tb' for testing which should be have a primary key
for all nodes.
4. At the origin node, execute the command at psql console:
alter database db start replication in group gcs; (which means the database
'db' is the origin and the group 'gcs' is the GCS group name)
5. At the subscriber node, execute the command:
alter database db accept replication from group gcs;
Hope information above would be helpful, and keep in touch.
leiyonghua
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Improving non-joinable EXISTS subqueries
The examples that Kevin Grittner put up awhile back included several uses of EXISTS() in places where it couldn't be turned into a semijoin, eg in the query's targetlist. I was musing a bit about whether we could improve those scenarios. I would like to get 8.4 to the point where we could say as a blanket performance recommendation prefer EXISTS over IN. The semantic gotchas associated with NOT IN make it hard to optimize well, not to mention being a perennial bane of novices; so if we could just point people in the other direction without qualification I think we'd be better off. But how much work would it be to get there? The single place where IN wins over EXISTS as of CVS HEAD is that a non-join-optimizable IN clause can still be turned into a hashed subplan, which greatly reduces the cost of making IN tests for a large number of upper-query rows. It looks to me that with the current planning infrastructure it wouldn't be very difficult to turn EXISTS (with hashable comparisons to upper variables in its WHERE) into a similar kind of plan. The problem is that *that isn't necessarily a win*. Consider something like SELECT x, y, EXISTS(SELECT * FROM tab1 WHERE tab1.a = tab2.z) FROM tab2 WHERE ...; Given that there's an index on tab1.a, the current planning for this will produce what's essentially a nestloop-with-inner-indexscan plan: for each tab2 row selected by the outer query, we'll do an indexscan probe into tab1 to see if there's a match. This is an ideal plan as long as the outer query doesn't select very many tab2 rows. We could transform this into the equivalent of a hashed implementation of SELECT x, y, z IN (SELECT a FROM tab1) FROM tab2 WHERE ...; which would result in loading all of tab1 into a hashtable and then probing the hashtable for each tab2 row. Now, that wins big if there are many selected tab2 rows (and tab1 isn't too big to fit in an in-memory hashtable). But it loses big if the outer query only needs to probe for a few values --- we won't repay the cost of building the hashtable. So I think it'd be a really bad idea to make this change blindly. For everyone whose query got speeded up, someone else's would be slowed down --- in fact, for apps that are tuned to PG's existing behavior, you could expect that it'd mostly be the latter case. The problem then is to make the choice of plan with some intelligence. The bit of information that we lack in order to do that is an idea of how many times the outer query will call the EXISTS subquery. Right now, all subqueries that can't be pulled up as joins are planned fully during SS_process_sublinks(), which is called long before we can have any idea about that. I looked into whether it's feasible to postpone planning subqueries till later on in planning. I think it's probably structurally possible without an enormous amount of work, but it's not exactly trivial either. Even given that we postpone planning/costing subqueries until we really need to know the cost, we're not out of the woods. For an EXISTS appearing in a join condition, it's entirely possible that different join sequences will result in executing the EXISTS wildly different numbers of times. Re-planning the EXISTS subquery each time we consider a different upper-query join sequence seems entirely impractical on speed grounds. So it seems like what we'd need to do is * During planner startup, generate Paths (we'd need no more level of detail) for both the retail and hashed version of each EXISTS subquery. From these, estimate the startup cost of the hashed version (ie, time to execute the un-qualified subquery once and load the hash table) and the per-upper-row costs of each approach. Stash these costs somewhere handy. * While forming upper-query paths, estimate the costs of each approach on-the-fly for every path, based on the estimated number of rows in the input paths. Use the cheaper case while figuring the cost of that upper path. * While building the final Plan, instantiate whichever subquery Plan is a winner in the context of the chosen upper path. I don't think any of this is out of reach, but it'd be a nontrivial bit of implementation effort (maybe a week or three) and it also looks like there might be a measurable planning slowdown for any query involving subqueries. I'm not sure yet how much of this is just moving existing work around and how much will actually represent new planning expense. But certainly, costing EXISTS subqueries two different ways isn't going to be free. So ... I'm wondering if this actually touches anyone's hot-button, or if we should just file it in the overflowing pile of Things That Might Be Nice To Do Someday. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench duration option
Tom Lane [EMAIL PROTECTED] wrote: My understanding is that it's basically cheap PC hardware (with clock interfaces based on old ISA bus specs) that has the issue in a significant way. I wouldn't expect you to see it on a serious database server. But lots of people still do development on cheap PC hardware, which is why I think this is worth worrying about. Ok, I rewrote the patch to use SIGALRM instead of gettimeofday. The only thing I worried about is portability issue. POSIX functions like alarm() or setitimer() are not available at least on Windows. I expect alarm() is available on all platforms except Win32 and used CreateTimerQueue() instead on Win32 in the new patch. (We have own implementation of setitimer() in the server core, but pgbench cannot use the function because it is a client application.) Comments welcome and let me know if there are still some problems. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center pgbench-duration_v2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgbench duration option
ITAGAKI Takahiro wrote: The only thing I worried about is portability issue. POSIX functions like alarm() or setitimer() are not available at least on Windows. I expect alarm() is available on all platforms except Win32 and used CreateTimerQueue() instead on Win32 in the new patch. (We have own implementation of setitimer() in the server core, but pgbench cannot use the function because it is a client application.) It wouldn't be unheard of to allow using timer.c into client domain (cf. src/port/) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improving non-joinable EXISTS subqueries
Tom Lane wrote: I don't think any of this is out of reach, but it'd be a nontrivial bit of implementation effort (maybe a week or three) and it also looks like there might be a measurable planning slowdown for any query involving subqueries. I'm not sure yet how much of this is just moving existing work around and how much will actually represent new planning expense. But certainly, costing EXISTS subqueries two different ways isn't going to be free. The typical comment around here is that it's usually a huge win when a bit of CPU time is spent in buying I/O savings. Since most servers are I/O bound anyway, and since most servers nowadays are typically oversized in CPU terms, this strikes me as the good tradeoff to be making. In any case, most of the time EXISTS queries are expensive queries, so spending more time planning them is probably good. It'd be a shame to spend a lot of time planning queries that are trivial in execution costs, but I wouldn't expect this to be the case here. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Design for Synchronous Replication/ WAL Streaming
For various reasons others have not been able to discuss detailed designs in public. In an attempt to provide assistance with that I'm providing my design notes here - not hugely detailed, just rough sketches of how it can work. This may also help identify coordination points and help to avert the code equivalent of a traffic jam later in this release cycle. Sync rep consists of 3 main parts: * WAL sending * WAL transmitting * WAL receiving WAL apply is essentially the same, so isn't discussed here. WAL sending - would be achieved by having WAL writer issue calls to transmit data. Individual backends would perform XLogInsert() to insert a commit WAL record, then queue themselves up to wait for WAL writer to perform the transmit up to the desired LSN (according to parameter settings for synchronous_commit etc). The local WAL write and WAL transmit would be performed together by the WAL writer, who would then wake up backends once the log has been written as far as the requested LSN. Very similar code to LWlocks, but queued on LSN, not lock arrival. Should be possible to make queue in strict LSN order to avoid complexity on wake-up. This then provides Group Commit feature at same time as ensuring efficient WAL transmit. WAL transmit - network layer is handled by plugin, as suggested by Itagaki/Koichi. Requirements are efficient transfer of WAL, similar configurability to other aspects of Postgres, including security. Various approaches possible * direct connect using new protocol * implement slight protocol changes into standard PostgreSQL client, similar to COPY streaming, just with slightly different initiation. Allows us to use same config, security options as now with postmaster handling initial connection. Plugin architecture allows integration with various vendor supplied options. Hopefully Postgres gets working functionality as default. WAL receiving - separate process on standby server. Started by an option in recovery.conf to receive streaming WAL rather than use files. Separation of Startup process from WALReceiver process required to ensure fast response to incoming network packets without slowing down WAL apply, which needs to go fast to keep up with stream. WALreceiver process would receive WAL and then write them to WAL buffers and also to disk in the normal WAL files. Data buffered in WAL buffers allows Startup process to read data within ReadRecord() from shared memory rather than from files, so minimising changes required for Startup process. Writing to WAL buffers also allows addition of a WAL bgreader process that can pre-fetch buffers required later for WAL apply. (That was a point of discussion previously, but its not a huge part of the design and can be added as a performance feature fairly late, if we need it). Data is written to disk to ensure the standby node can restart from last restartpoint if it should crash, re-reading all WAL files and then beginning to receive WAL from remote primary again. Files written and cleaned up in exactly same way as on normal server: keep last two restartpoints worth of xlogs, then cleanup at restartpoint time. Integration point between this and Hot Standby is around postmaster states and when the WALReceiver starts. That is the same time I expect the bgwriter to start, so I will submit patch in next few days to get that aspect sorted out. If anybody is going to refactor xlog.c to avoid collisions, it had better happen in next couple of weeks. Probably has to be Tom that does this. Suggested splits: * xlog stuff that happens in normal backends (some changes for WAL streaming) * recovery architecture stuff StartupXlog etc, checkpoints * redo apply (major changes for WAL streaming) * xlog rmgr stuff Also need to consider how the primary node acts when standby is not available. Should it hang, waiting certain time for recovery, or should it continue to run in degraded mode? Probably another parameter. Anyway, all of the above is a strawman design to assist everybody begin to understand how this might all fit together. No doubt there are other possible approaches. My personal concerns are that we minimise things that prevent various developers from working alongside each other on related features. So if the above design doesn't match what is being worked on, then at least lets examine where the integration points are, please. I hope and expect others are working on the WAL streaming design and if something occurs to prevent that then I will provide time singly/part of team to ensure this happens for 8.4. I'll be posting more design stuff over next few weeks on Hot Standby also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
On Mon, 2008-08-18 at 16:22 -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: What would need to happen for the next jump up from where varlena is now, to 8 bytes? Dealing with upwards-of-4GB blobs as single Datums isn't remotely sane, and won't become so in the near (or even medium) future. So I don't see the point of doing all the work that would be involved in making this go. What would make more sense is to redesign the large-object stuff to be somewhat modern and featureful, and provide stream-access APIs (think lo_read, lo_seek, etc) that allow offsets wider than 32 bits. The main things I think we'd need to consider besides just the access API are - permissions features (more than none anyway) - better management of orphaned objects (obsoleting vacuumlo) - support 16TB of large objects (maybe partition pg_largeobject?) - dump and restore probably need improvement to be practical for such large data volumes Sounds like a good list. Probably also using a separate Sequence to allocate numbers rather than using up all the Oids on LOs would be a good plan. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
Simon Riggs [EMAIL PROTECTED] writes: Probably also using a separate Sequence to allocate numbers rather than using up all the Oids on LOs would be a good plan. Well, assuming that your Large Objects are actually Large, you aren't going to need as many OIDs as all that ;-) However: I was chewing on this point a few days ago, and it seemed to me that essentially duplicating the functionality of the OID generator wasn't likely to be a win. What seems more practical is to extend the internal next-OID counter to 64 bits, and allow callers to get either the full 64 bits or just the lowest 32 bits depending on what they need. This change would actually be entirely transparent to 32-bit callers, and the extra cycles to manage a 64-bit counter would surely be lost in the noise compared to acquiring/releasing OidGenLock. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extending varlena
On Mon, 2008-08-18 at 23:43 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Probably also using a separate Sequence to allocate numbers rather than using up all the Oids on LOs would be a good plan. Well, assuming that your Large Objects are actually Large, you aren't going to need as many OIDs as all that ;-) However: I was chewing on this point a few days ago, and it seemed to me that essentially duplicating the functionality of the OID generator wasn't likely to be a win. What seems more practical is to extend the internal next-OID counter to 64 bits, and allow callers to get either the full 64 bits or just the lowest 32 bits depending on what they need. This change would actually be entirely transparent to 32-bit callers, and the extra cycles to manage a 64-bit counter would surely be lost in the noise compared to acquiring/releasing OidGenLock. Sounds very cool. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposed Resource Manager Changes
I would like to make a simple change to Resource Manager code to introduce a plugin that can redefine or extend Rmgrs. Providing an rmgr plugin in this way will also allow: * filtering/control over apply of certain WAL records. We might decide to skip all but certain tables, indexes, tablespaces or databases, allowing a partial rebuild facility during PITR. * ability to build a WAL debug utility that can stop/start/inspect WAL records as we move thru WAL during recovery, or selectively output specific record types or debug messages every N records. Would allow a WAL debugger client GUI to be built into pgadmin, for example. * dynamic addition of new index types, since we will be able to snap-in any new index types via plugins, then insert into pg_am. * additional actions alongside existing recovery actions. For example, pre-processing of directories for new tablespace creation. * new WAL records to allow issuing processing instructions to the standby server with user defined meaning: activate enterprise device #72, coordinate with user software x, send SNMP trap 1157, to allow us to measure delay between when it was sent on primary and when it was processed on standby. We could do each of the above in different ways, though it seems most straightforward to provide a plugin that allows both extension and redefinition of the RmgrTable. Proposed way recognises that there is room for up to 255 rmgrs, since the RmgrId is a single byte field on the WAL record. * redefine RmgrTable as fixed size array of 255 elements that is malloc'd into Startup process at beginning of StartupXlog() * first few entries are fixed and we reserve first 32 elements for future use by Postgres Core. * values 33-127 are available by centrally managed registration to help avoid conflicts in Postgres projects on pgfoundry * values 128+ are user defined * startup sets up fixed rmgrs, then calls plugin if it exists to modify and/or add new rmgrs * a new option to define behaviour if we receive an unhandled rmgrid. Current behaviour is to declare this an invalid WAL record * we might also check rmgrids when we enter XLogInsert() to ensure everything written can be read if we crash, not sure whether people will think that is overkill or essential (I'd say essential, but people may fear performance problems). Sample plugin showing filtering of WAL records for a specific databaseid would be provided with patch. (and yes, I see it will fall to me to document all of these new possible types of plugin, so we have a new chapter on Server Extensibility). Your thoughts? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Publish GUC flags to custom variables
Hello, Postgres supports to add custom GUC variables on runtime, but we cannot use GUC flags in them. This patch adds the flags argument to DefineCusomXxx() functions. The flags were always 0 until now. GUC flags are useful for variables with units. Users will be able to add configuration parameters somothing like memory-size or time-duration more easily. I have a plan to use the feature in SQL tracing and analyzing add-on for postgres. Also, the auto-explain patch suggested in the last commit-fest could be re-implemented as a plug-in instead of a core-feature using the custom variable with units and ExecutorRun_hook. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center custom_guc_flags.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WITH RECURSIVE patches 0818
On Mon, Aug 18, 2008 at 04:38:52PM +0900, Tatsuo Ishii wrote: Hi, Here is the latest WITH RECURSIVE patches against CVS HEAD. Besides syncing to CVS HEAD, followings are main differences from previous one: Thanks for the new patch :) I think I may have found another bug: WITH RECURSIVE t(i,j) AS ( VALUES (1,2) UNION ALL SELECT t2.i, t.j FROM ( SELECT 2 AS i UNION ALL /* Wrongly getting detected, I think */ SELECT 3 AS i ) AS t2 JOIN t ON (t2.i = t.i) ) SELECT * FROM t; ERROR: attribute number 2 exceeds number of columns 1 Is there some way to ensure that in the case of WITH RECURSIVE, the query to the right of UNION ALL follows only the SQL:2008 rules about not having outer JOINs, etc. in it, but otherwise make it opaque to the error-checking code? I know I didn't explain that well, but the above SQL should work and the error appears to stem from the parser's looking at the innermost UNION ALL instead of the outermost. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
