Re: [HACKERS] how to insure libpq(dll/so) for thread-safety?
"wjzeng" wrote: > In pgsql/src/interfaces/libpq/fe-exec.c, there are two variables: > - > static int static_client_encoding = PG_SQL_ASCII; > static bool static_std_strings = false; > > If enable_thread_safety is "no", how to insure libpq(dll/so) for > thread-safety? Use PQescape[String|Bytea]Conn() instead of PQescape[String|Bytea](). The static variables are used only in those deprecated functions. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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 to fix two little typo in charset.sgml
Dickson S. Guedes wrote: Hi all, Attached is a patch to fix a command line example in charset.sgml. No, the options really are called LC_COLLATE and LC_CTYPE now. They were renamed on 6th of April, just before beta1 -- Heikki Linnakangas EnterpriseDB 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] PATCH to fix two little typo in charset.sgml
Hi all, Attached is a patch to fix a command line example in charset.sgml. I hope it is correct. []s -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br fix_typo_lc_collatein_charset_sgml.patch.bz2 Description: application/bzip signature.asc Description: Esta é uma parte de mensagem assinada digitalmente
Re: [HACKERS] Problem with estimating pages for a table
On Wed, May 13, 2009 at 6:08 AM, Cristina M wrote: > Hello, > I posted to the general list, and didn't receive any replies. Therefore, I > am trying this list now, hopefully this is the right mailing list for this > type of questions. > I am trying to compute the no of pages of a table. I am using the formula : You haven't given us a lot of information on what you want to do with this, but if by any chance it's helpful to get the actual number of pages for some particular table, you can do it like this: select relpages from pg_class where oid = 'name_of_the_table'::regclass; There is also a handy function pg_relation_size(). ...Robert -- 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] Problem with estimating pages for a table
Cristina M wrote: > I posted to the general list, and didn't receive any replies. > Therefore, I am trying this list now, hopefully this is the right > mailing list for this type of questions. > > I am trying to compute the no of pages of a table. I am using the formula : > > pages = ( columns width + 28) * no. of rows / block size Keep in mind that if you have varchar(1000) and store 30 bytes of text, it will use 30+4, not 1000+4. Very long attributes may be compressed and/or stored in a side table called the TOAST table; only a pointer to it remains on the base table (which is some 20 bytes long I think). Also keep in mind that there's a lot of space lost to alignment considerations, so don't expect things to match down to the last byte. -- 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] Problem with estimating pages for a table
Cristina M escreveu: > - for table t2(l_orderkey int, l_partkey int, l_quantiy, l_tax, > l_extendedprice, l_discount) I got an error of 42 %. > I suspect you have NULLs in your table; they're stored as bitmaps, so they use little space. -- Euler Taveira de Oliveira http://www.timbira.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] pg_views definition format
On Wed, 13 May 2009, Kevin Field wrote:
Or would the only way to do this be to actually create a view and then
call pg_get_viewdef() and then delete the view?
Just make it a temporary view and then it drops when the session ends.
Here's a working shell example that transforms a view into the parsed form
and returns it:
$ v="select * from pg_views"
$ p=`psql -Atc "create temporary view x as ${v}; select
pg_get_viewdef('x'::regclass);"`
$ echo $p
SELECT pg_views.schemaname, pg_views.viewname, pg_views.viewowner,
pg_views.definition FROM pg_views;
--
* Greg Smith gsm...@gregsmith.com 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] pg_views definition format
On May 13, 12:52 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Kev writes:
> > ... I was surprised
> > to find that some of my views of the form:
> > select.from b left join a on a.id=b.id
> > ...were being translated to this:
> > SELECT..FROM (B LEFT JOIN a ON ((a.id = b.id)))
> > ...before being stored in the table pg_views is derived from. My
> > surprise is at the double parentheses around "a.id = b.id". Is that
> > supposed to be that way? Is it likely to change?
>
> There isn't any such "table". What pg_views is showing you is a reverse
> compilation of the internal parsetree for the rule. Whether there are
> parentheses in a given place is dependent on whether the code thinks it
> might be safe to omit them ... and I think in the non-prettyprinted
> format the answer is always "no". For instance with pg_views itself:
>
> regression=# select pg_get_viewdef('pg_views'::regclass);
>
>pg_get_viewdef
>
> SELECT n.nspname AS schemaname, c.relname AS viewname,
> pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition
> FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
> WHERE (c.relkind = 'v'::"char");
> (1 row)
>
> regression=# select pg_get_viewdef('pg_views'::regclass, true);
> pg_get_viewdef
> ---
> SELECT n.nspname AS schemaname, c.relname AS viewname,
> pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition
> FROM pg_class c
> LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
>WHERE c.relkind = 'v'::"char";
> (1 row)
>
> Same parsetree, but the latter case is working a bit harder to make
> it look nice. The default case is overparenthesizing intentionally
> to make dead certain the rule will be parsed the same way if it's
> dumped and reloaded.
>
> regards, tom lane
That's handy to know about pg_views. I'm still not sure how I should
code my script to make it future-proof though (because things of the
form "((a))" seem beyond dead-certain...) unless...is there some
function I can call to parse and then recompile the SQL, so I can feed
in my generated code in any format I like and then have it translate?
Or would the only way to do this be to actually create a view and then
call pg_get_viewdef() and then delete the view?
--
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] pg_views definition format
On May 13, 12:41 pm, kevin.gritt...@wicourts.gov ("Kevin Grittner")
wrote:
> Kevin Field wrote:
> > One other thing I'm just curious about, "!=" gets replaced with
> > "<>"...how come? (Feels more VB-ish than C-ish, so I was surprised
> > that that would be the official/preferred reconstruct)
>
> "<>" is the SQL standard operator. "!=" is a PostgreSQL extension,
> for the convenience and comfort of those more used to it.
Ahh, that makes sense. Thanks, guys.
--
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] New trigger option of pg_standby
On Wed, 2009-05-13 at 16:47 -0400, Tom Lane wrote: > Simon Riggs writes: > > recovery_end_command is performed *after* the UpdateControlFile() once > > the we are DB_IN_PRODUCTION. > > Hmm, shouldn't it be after the last checkpoint Definitely. > but before we go to DB_IN_PRODUCTION? I think it can be either, so I'll go with your proposal. (I'm aware Fujii-san is asleep right now, so we should expect another viewpoint before tomorrow). -- 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] New trigger option of pg_standby
Simon Riggs writes: > recovery_end_command is performed *after* the UpdateControlFile() once > the we are DB_IN_PRODUCTION. Hmm, shouldn't it be after the last checkpoint but before we go to DB_IN_PRODUCTION? I have to admit I've not been following this closely though, so I may be missing something. 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] New trigger option of pg_standby
On Wed, 2009-05-13 at 21:26 +0300, Heikki Linnakangas wrote: > This whole thing can be considered to be a new feature. recovery.conf will contain a new optional parameter: recovery_end_command (string) This parameter specifies a shell command that will be executed once only at the end of recovery. This parameter is optional. The purpose of the recovery_end_command is to provide a mechanism for cleanup following replication or recovery. Any %r is replaced by the name of the file containing the last valid restart point. That is the earliest file that must be kept to allow a restore to be restartable, so this information can be used to truncate the archive to just the minimum required to support restart of the current restore. %r would only be used in a warm-standby configuration (see Section 24.4). Write %% to embed an actual % character in the command. recovery_end_command is performed *after* the UpdateControlFile() once the we are DB_IN_PRODUCTION. This behaviour ensures that a crash prior to the final checkpoint will continue to see the trigger file. Once we are safe, we can remove the trigger file safely. We also can now ignore any complexity surrounding whether WAL files are full or not, and whether WAL files were restored from the archive or from the local directory. Comments? -- 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] New trigger option of pg_standby
On Wed, 2009-05-13 at 15:05 -0400, Andrew Dunstan wrote: > Frankly, if anything it should move from contrib to the core proper. I > regard it as an essential utility, not an optional extra. I like that idea. -- 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] GEQO: ERX
On Wed, May 13, 2009 at 4:14 PM, Tobias Zahn wrote: > Hello, > thank you for posting the paper, it was quite interesting to read. I > think it would be a good idea to give the two-phase optimization a try. > As far as I know and understand the current (geqo) optimizer source, > many important parts are already there. For example, we can calculate > the costs of a given join order. Therefore, it would only be necessary > to write an algorithm witch chooses the right input for the cost function. > I would be interested in your opinion. I'm very interested in any improvements we can make to planning large join nests. Unfortunately the paper seems to conclude that it's not really feasible to use heuristics, as had been my hope, but I'd be very interested in any other approaches we can come up with. I probably do not have time to implement anything myself, but I'm happy to help with ideas and code review. ...Robert -- 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] libxml incompatibility
Alvaro Herrera writes: > It seems that if you load libxml into a backend for whatever reason (say > you create a table with a column of type xml) and then create a plperlu > function that "use XML::LibXML", we get a segmentation fault. I've applied a patch for this in HEAD. It fixes the reported case, but since I'm not a big user of either Perl or XML, it would be good to get some more testing done ... 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] GEQO: ERX
Hello, thank you for posting the paper, it was quite interesting to read. I think it would be a good idea to give the two-phase optimization a try. As far as I know and understand the current (geqo) optimizer source, many important parts are already there. For example, we can calculate the costs of a given join order. Therefore, it would only be necessary to write an algorithm witch chooses the right input for the cost function. I would be interested in your opinion. Regards, Tobias Robert Haas schrieb: > On Sat, May 2, 2009 at 11:37 AM, Tom Lane wrote: >> Tobias Zahn writes: >>> I didn't not get any response to my initial message below. Now I am >>> wondering if nobody is into the optimizer or if my question was just too >>> stupid. Could you please give me some clues? Your help would really be >>> appreciated. >> Well, nobody's into GEQO very much. I took a quick look and didn't >> think that deleting the ERX support would save anything noticeable, >> but you're welcome to try it if you think different. >> >> The real problem with working on GEQO, in my humble opinion, is that >> it's throwing good effort after bad. That module doesn't need marginal >> fixing, it needs throwing away and rewriting from scratch. Bad enough >> that it's convoluted and full of dead (experimental?) code; but I don't >> even believe that it's based on a good analogy. The planning problem >> is not all that much like traveling salesman problems, so heuristics >> designed for TSP are of pretty questionable usefulness to start with. >> That complaint could have been refuted if the module performed well, >> but in fact if you check the archives you'll find many many complaints >> about it --- its ability to find good plans seems to be mostly dependent >> on luck. >> >> My knowledge of AI search algorithms is about 20 years obsolete, but >> last I heard simulated annealing had overtaken genetic algorithms for >> many purposes. It might be interesting to try a rewrite based on SA; >> or maybe there's something better out there now. > > There's a 1997 article on this topic that's pretty interesting. > > Heuristic and randomized optimization for the join ordering problem > http://reference.kfupm.edu.sa/content/h/e/heuristic_and_randomized_optimization_fo_87585.pdf > > Here's the basic conclusion: > > "If good solutions are of highest importance, Two-Phase Optimization, > the algorithm that performed best in our experiments, is a very good > choice; other Simulated Annealing variants, for instance Toured > Simulated Annealing (TSA, LVZ93]), that we did not implement, are > likely to achieve quite similar results. The 'pure' Simulated > Annealing algorithm has a much higher running time without yielding > significantly better solutions. If short running time is more > important, Iterative Improvement (IIIO), the genetic algo- rithm > (BushyGenetic), and, to a lesser extent, Two-Phase Optimization (2PO) > are feasible alternatives." > > I'm not sure if there's anything more recent out there. > > ...Robert > -- 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] New trigger option of pg_standby
Simon Riggs wrote: On Wed, 2009-05-13 at 14:53 -0400, Tom Lane wrote: Heikki Linnakangas writes: Tom Lane wrote: Does this conclusion mean that changing pg_standby is no longer on the table for 8.4? It certainly smells more like a new feature than a bug fix. This whole thing can be considered to be a new feature. It's working as designed. But people seem to be surprised about the current behavior (me included), and we don't currently provide the behavior that most people actually want. I think we should fix it for 8.4. Well, that's okay by me if it can be done in a timely fashion. Bear in mind that we are planning to wrap beta2 not much more than 24 hours from now. I'll write it now then, so it can be reviewed tomorrow. Thanks, Simon! -- Heikki Linnakangas EnterpriseDB 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] New trigger option of pg_standby
On Wed, 2009-05-13 at 14:53 -0400, Tom Lane wrote: > Heikki Linnakangas writes: > > Tom Lane wrote: > >> Does this conclusion mean that changing pg_standby is no longer > >> on the table for 8.4? It certainly smells more like a new feature > >> than a bug fix. > > > This whole thing can be considered to be a new feature. It's working as > > designed. But people seem to be surprised about the current behavior (me > > included), and we don't currently provide the behavior that most people > > actually want. I think we should fix it for 8.4. > > Well, that's okay by me if it can be done in a timely fashion. Bear in > mind that we are planning to wrap beta2 not much more than 24 hours from > now. I'll write it now then, so it can be reviewed tomorrow. -- 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] New trigger option of pg_standby
Tom Lane wrote: Heikki Linnakangas writes: That's a lot more drastic change to make in beta. Besides, the proposed fix required backend changes. I think we should keep it in contrib. (At least for this release: If we get more integrated replication options in 8.5, that would be a good time to move pg_standby out of contrib if that's what we want.) The proposed fix requires coordinated changes in the core and pg_standby. That would be a lot *harder* if pg_standby were external. Since we've evidently not gotten this API quite right yet, I think we should be keeping pg_standby in contrib until we do, ie the API has been stable for awhile ... Agreed. Frankly, if anything it should move from contrib to the core proper. I regard it as an essential utility, not an optional extra. cheers 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] New trigger option of pg_standby
Heikki Linnakangas writes: > That's a lot more drastic change to make in beta. Besides, the proposed > fix required backend changes. I think we should keep it in contrib. (At > least for this release: If we get more integrated replication options in > 8.5, that would be a good time to move pg_standby out of contrib if > that's what we want.) The proposed fix requires coordinated changes in the core and pg_standby. That would be a lot *harder* if pg_standby were external. Since we've evidently not gotten this API quite right yet, I think we should be keeping pg_standby in contrib until we do, ie the API has been stable for awhile ... 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] New trigger option of pg_standby
Heikki Linnakangas writes: > Tom Lane wrote: >> Does this conclusion mean that changing pg_standby is no longer >> on the table for 8.4? It certainly smells more like a new feature >> than a bug fix. > This whole thing can be considered to be a new feature. It's working as > designed. But people seem to be surprised about the current behavior (me > included), and we don't currently provide the behavior that most people > actually want. I think we should fix it for 8.4. Well, that's okay by me if it can be done in a timely fashion. Bear in mind that we are planning to wrap beta2 not much more than 24 hours from now. 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] New trigger option of pg_standby
On Wed, 2009-05-13 at 14:14 -0400, Andrew Dunstan wrote: > pg_standby is useful and needs to be correct. My suggestion was designed to provide this. A misunderstanding. > And its existence as a > standard module is one of the things that has made me feel confident > about recommending people to use the PITR stuff. I'll be very annoyed if > it were to get pulled. If we cannot make it correct within core, then I will make it correct somewhere else, beta or not. Other than that, I have no wish to remove it from contrib. -- 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] New trigger option of pg_standby
Andrew Dunstan wrote: We're in Beta. You can't just go yanking stuff like that. Beta testers will be justifiably very annoyed. Please calm down. pg_standby is useful and needs to be correct. And its existence as a standard module is one of the things that has made me feel confident about recommending people to use the PITR stuff. I'll be very annoyed if it were to get pulled. Since mentioned in the docs, I consider it at least the semi-official tool for pgsql PITR handling. But as this discussion reveals, the api is flawed, and will not allow guaranteed consistency (whatever pg_standby tries) until fixed. While this may not be a bug of the restore_script call, the pitr procedure in total is partially broken (in the sense that it doesn't provide what most users expect in a secure way) and thus needs to be fixed. It seems a fix can't be provided without extending the api. Regards, Andreas -- 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] New trigger option of pg_standby
Simon Riggs wrote: On Wed, 2009-05-13 at 13:01 -0400, Tom Lane wrote: Heikki Linnakangas writes: Does someone want to take a stab at writing a patch for that? No, not if there is a likelihood the work would be wasted. There always is. (I would've wrote the patch myself right away, but I'm extremely busy at the moment. :-( Might take one more day before I get the time to finish it, and we don't have much time) Does this conclusion mean that changing pg_standby is no longer on the table for 8.4? It certainly smells more like a new feature than a bug fix. I don't really understand this comment. Why would fixing a memory leak be worthwhile when fixing a potential for data loss be a deferrable activity? Because the data loss is working as designed and documented, even though the design is not what most people want and the documentation could say that more prominently. That said, I'm in favor of changing this for 8.4. I will set-up pg_standby as an external module and we can change it from there. No more discussions-for-8.4 and I can update as required to support each release. So let's just remove it from contrib and be done. Counterthoughts? That's a lot more drastic change to make in beta. Besides, the proposed fix required backend changes. I think we should keep it in contrib. (At least for this release: If we get more integrated replication options in 8.5, that would be a good time to move pg_standby out of contrib if that's what we want.) -- Heikki Linnakangas EnterpriseDB 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] New trigger option of pg_standby
On Wed, 2009-05-13 at 21:26 +0300, Heikki Linnakangas wrote: > I think we should fix it for 8.4. Agreed. -- 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] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
On Wed, May 13, 2009 at 03:12:51PM +0200, Pavel Stehule wrote: > 2009/5/13 Joshua Tolley : > > On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote: > >> 2009/5/13 Joshua Tolley : > >> > On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote: > >> >> this patch has some bugs but it is good prototype (it's more stable > >> >> than old patch): > >> > > >> > I'm not sure if you're at the point that you're interested in bug > >> > reports, but > >> > here's something that didn't behave as expected: > >> > > >> > 5432 j...@josh*# create table gsettest (prod_id integer, cust_id integer, > >> > quantity integer); > >> > CREATE TABLE > >> > 5432 j...@josh*# insert into gsettest select floor(random() * 10)::int, > >> > floor(random() * 20)::int, floor(random() * 10)::int from > >> > generate_series(1, > >> > 100); > >> > INSERT 0 100 > >> > 5432 j...@josh*# select prod_id, cust_id, sum(quantity) from gsettest > >> > group by > >> > cube (prod_id, cust_id) order by 1, 2; > >> > prod_id | cust_id | sum > >> > -+-+- > >> > 5 | 7 | 4 > >> > 8 | 16 | 3 > >> > 9 | 19 | 8 > >> > 4 | 13 | 3 > >> > 8 | 8 | 15 > >> > 5 | 2 | 4 > >> > 7 | 6 | 7 > >> > 6 | 6 | 3 > >> > > >> > > >> > Note that the results aren't sorted. The following, though, works around > >> > it: > >> > >> I thing, so result should not be sorted - it's same like normal group by. > > > > Normal GROUP BY wouldn't have ignored the ORDER BY clause I included. > > sorry, now I understand - simply it is a bug. I fixed it Where's the new patch? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com 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] New trigger option of pg_standby
On Wed, 2009-05-13 at 14:14 -0400, Andrew Dunstan wrote: > pg_standby is useful and needs to be correct. And its existence as a > standard module is one of the things that has made me feel confident > about recommending people to use the PITR stuff. I'll be very annoyed if > it were to get pulled. Although I am not advocating one position or another there are benefits to removing it. It would be nice to continue to enhance pg_standby without the limitations of the core release schedule. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] New trigger option of pg_standby
Tom Lane wrote: Heikki Linnakangas writes: I don't think we're going to get this to work reliably without extending the interface between the backend and restore_command. We've discussed many methods and there's always some nasty corner-case like that. I think we should leave back-branches as is, and go with Simon's suggestion to add new "recovery_end_command" that's run when the recovery is finished. That's simpler and more reliable than any of the other approaches we've discussed, and might become handy for other purposes as well. Does someone want to take a stab at writing a patch for that? Does this conclusion mean that changing pg_standby is no longer on the table for 8.4? It certainly smells more like a new feature than a bug fix. This whole thing can be considered to be a new feature. It's working as designed. But people seem to be surprised about the current behavior (me included), and we don't currently provide the behavior that most people actually want. I think we should fix it for 8.4. -- Heikki Linnakangas EnterpriseDB 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] New trigger option of pg_standby
Simon Riggs wrote: I will set-up pg_standby as an external module and we can change it from there. No more discussions-for-8.4 and I can update as required to support each release. So let's just remove it from contrib and be done. Counterthoughts? We're in Beta. You can't just go yanking stuff like that. Beta testers will be justifiably very annoyed. Please calm down. pg_standby is useful and needs to be correct. And its existence as a standard module is one of the things that has made me feel confident about recommending people to use the PITR stuff. I'll be very annoyed if it were to get pulled. cheers 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] New trigger option of pg_standby
Simon Riggs writes: > I will set-up pg_standby as an external module and we can change it from > there. No more discussions-for-8.4 and I can update as required to > support each release. So let's just remove it from contrib and be done. Huh? The proposed fix involves a backend change, so I don't see how removing pg_standby from the distribution frees you from the constraints of our versioning. 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] New trigger option of pg_standby
On Wed, 2009-05-13 at 13:01 -0400, Tom Lane wrote: > Heikki Linnakangas writes: > > I don't think we're going to get this to work reliably without extending > > the interface between the backend and restore_command. We've discussed > > many methods and there's always some nasty corner-case like that. Agreed. > > I think we should leave back-branches as is, and go with Simon's > > suggestion to add new "recovery_end_command" that's run when the > > recovery is finished. That's simpler and more reliable than any of the > > other approaches we've discussed, and might become handy for other > > purposes as well. That is the cleanest way, though we cannot really avoid acting for backbranches also. > > Does someone want to take a stab at writing a patch for that? No, not if there is a likelihood the work would be wasted. > Does this conclusion mean that changing pg_standby is no longer > on the table for 8.4? It certainly smells more like a new feature > than a bug fix. I don't really understand this comment. Why would fixing a memory leak be worthwhile when fixing a potential for data loss be a deferrable activity? I will set-up pg_standby as an external module and we can change it from there. No more discussions-for-8.4 and I can update as required to support each release. So let's just remove it from contrib and be done. Counterthoughts? -- 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] New trigger option of pg_standby
Heikki Linnakangas writes: > I don't think we're going to get this to work reliably without extending > the interface between the backend and restore_command. We've discussed > many methods and there's always some nasty corner-case like that. > I think we should leave back-branches as is, and go with Simon's > suggestion to add new "recovery_end_command" that's run when the > recovery is finished. That's simpler and more reliable than any of the > other approaches we've discussed, and might become handy for other > purposes as well. > Does someone want to take a stab at writing a patch for that? Does this conclusion mean that changing pg_standby is no longer on the table for 8.4? It certainly smells more like a new feature than a bug fix. 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] pg_views definition format
Kev writes:
> ... I was surprised
> to find that some of my views of the form:
> select.from b left join a on a.id=b.id
> ...were being translated to this:
> SELECT..FROM (B LEFT JOIN a ON ((a.id = b.id)))
> ...before being stored in the table pg_views is derived from. My
> surprise is at the double parentheses around "a.id = b.id". Is that
> supposed to be that way? Is it likely to change?
There isn't any such "table". What pg_views is showing you is a reverse
compilation of the internal parsetree for the rule. Whether there are
parentheses in a given place is dependent on whether the code thinks it
might be safe to omit them ... and I think in the non-prettyprinted
format the answer is always "no". For instance with pg_views itself:
regression=# select pg_get_viewdef('pg_views'::regclass);
pg_get_viewdef
SELECT n.nspname AS schemaname, c.relname AS viewname,
pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition
FROM (pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE
(c.relkind = 'v'::"char");
(1 row)
regression=# select pg_get_viewdef('pg_views'::regclass, true);
pg_get_viewdef
---
SELECT n.nspname AS schemaname, c.relname AS viewname,
pg_get_userbyid(c.relowner) AS viewowner, pg_get_viewdef(c.oid) AS definition
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'v'::"char";
(1 row)
Same parsetree, but the latter case is working a bit harder to make
it look nice. The default case is overparenthesizing intentionally
to make dead certain the rule will be parsed the same way if it's
dumped and reloaded.
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] pg_views definition format
Kevin Field wrote: One other thing I'm just curious about, "!=" gets replaced with "<>"...how come? (Feels more VB-ish than C-ish, so I was surprised that that would be the official/preferred reconstruct) <> is the official SQL standard notation for "not equals", AFAIK. != is not. cheers 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] pg_views definition format
Kevin Field wrote: > One other thing I'm just curious about, "!=" gets replaced with > "<>"...how come? (Feels more VB-ish than C-ish, so I was surprised > that that would be the official/preferred reconstruct) "<>" is the SQL standard operator. "!=" is a PostgreSQL extension, for the convenience and comfort of those more used to it. -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] pg_views definition format
On May 13, 11:31 am, Kev wrote: > Hi, > > I have a script that automatically generates the SQL to create some > views. I'd like it to check whether its generated SQL matches the SQL > returned by "select definition from pg_views where...". I've guessed > most of the rules just by looking at the output, but I was surprised > to find that some of my views of the form: > > select.from b left join a on a.id=b.id > > ...were being translated to this: > > SELECT..FROM (B LEFT JOIN a ON ((a.id = b.id))) > > ...before being stored in the table pg_views is derived from. My > surprise is at the double parentheses around "a.id = b.id". Is that > supposed to be that way? Is it likely to change? > > Thanks, > Kev One other thing I'm just curious about, "!=" gets replaced with "<>"...how come? (Feels more VB-ish than C-ish, so I was surprised that that would be the official/preferred reconstruct) -- 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] Windows installation service
Hi, I'm having 'service' issues too: Windows XP SP3 user account used for service is not a member of admin group. Errors starting service, but dbengine is started and available. If i try to stop/start with the pgctl batch files created during install, same type of problem... PC had 8.2-5.1 installed originally, I upgraded it to 8.3-6 (without stopping or removing the 8.2 service) I have since removed the 8.2 installation and deleted the 8.2 folder from Program Files, and also have changed the environment variables (that aren't updated automatically.) I think I can 'fix' by removing completely and reinstalling fresh, but i'd like to know how to not have to do this... -- View this message in context: http://www.nabble.com/Windows-installation-service-tp22989478p23522185.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] pg_views definition format
Hi, I have a script that automatically generates the SQL to create some views. I'd like it to check whether its generated SQL matches the SQL returned by "select definition from pg_views where...". I've guessed most of the rules just by looking at the output, but I was surprised to find that some of my views of the form: select.from b left join a on a.id=b.id ...were being translated to this: SELECT..FROM (B LEFT JOIN a ON ((a.id = b.id))) ...before being stored in the table pg_views is derived from. My surprise is at the double parentheses around "a.id = b.id". Is that supposed to be that way? Is it likely to change? Thanks, Kev -- 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] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
2009/5/13 Joshua Tolley : > On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote: >> 2009/5/13 Joshua Tolley : >> > On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote: >> >> this patch has some bugs but it is good prototype (it's more stable >> >> than old patch): >> > >> > I'm not sure if you're at the point that you're interested in bug reports, >> > but >> > here's something that didn't behave as expected: >> > >> > 5432 j...@josh*# create table gsettest (prod_id integer, cust_id integer, >> > quantity integer); >> > CREATE TABLE >> > 5432 j...@josh*# insert into gsettest select floor(random() * 10)::int, >> > floor(random() * 20)::int, floor(random() * 10)::int from >> > generate_series(1, >> > 100); >> > INSERT 0 100 >> > 5432 j...@josh*# select prod_id, cust_id, sum(quantity) from gsettest >> > group by >> > cube (prod_id, cust_id) order by 1, 2; >> > prod_id | cust_id | sum >> > -+-+- >> > 5 | 7 | 4 >> > 8 | 16 | 3 >> > 9 | 19 | 8 >> > 4 | 13 | 3 >> > 8 | 8 | 15 >> > 5 | 2 | 4 >> > 7 | 6 | 7 >> > 6 | 6 | 3 >> > >> > >> > Note that the results aren't sorted. The following, though, works around >> > it: >> >> I thing, so result should not be sorted - it's same like normal group by. > > Normal GROUP BY wouldn't have ignored the ORDER BY clause I included. > sorry, now I understand - simply it is a bug. I fixed it Thank You Pavel > - Josh > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkoKxLQACgkQRiRfCGf1UMOj/wCgkPnRiheRr+BNPLBCjzA9XlFW > 0rsAoI0eOGSGlxIv0eNB8zqum7kw/Cqw > =FCTz > -END PGP SIGNATURE- > > -- 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] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote: > 2009/5/13 Joshua Tolley : > > On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote: > >> this patch has some bugs but it is good prototype (it's more stable > >> than old patch): > > > > I'm not sure if you're at the point that you're interested in bug reports, > > but > > here's something that didn't behave as expected: > > > > 5432 j...@josh*# create table gsettest (prod_id integer, cust_id integer, > > quantity integer); > > CREATE TABLE > > 5432 j...@josh*# insert into gsettest select floor(random() * 10)::int, > > floor(random() * 20)::int, floor(random() * 10)::int from generate_series(1, > > 100); > > INSERT 0 100 > > 5432 j...@josh*# select prod_id, cust_id, sum(quantity) from gsettest group > > by > > cube (prod_id, cust_id) order by 1, 2; > > prod_id | cust_id | sum > > -+-+- > > 5 | 7 | 4 > > 8 | 16 | 3 > > 9 | 19 | 8 > > 4 | 13 | 3 > > 8 | 8 | 15 > > 5 | 2 | 4 > > 7 | 6 | 7 > > 6 | 6 | 3 > > > > > > Note that the results aren't sorted. The following, though, works around it: > > I thing, so result should not be sorted - it's same like normal group by. Normal GROUP BY wouldn't have ignored the ORDER BY clause I included. - Josh signature.asc Description: Digital signature
Re: [HACKERS] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
hello everybody, from my side the goal of this discussion is to extract a consensus so that we can go ahead and implement this issue for 8.5. our customer here needs a solution to this problem and we have to come up with something which can then make it into PostgreSQL core. how shall we proceed with the decision finding process here? i am fine with a GUC and with an grammar extension - i just need a decision which stays unchanged. comments and votes are welcome. many thanks, hans -- Cybertec Schönig & Schönig GmbH Professional PostgreSQL Consulting, Support, Training Gröhrmühlgasse 26, A-2700 Wiener Neustadt Web: www.postgresql-support.de -- 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] DROP TABLE vs inheritance
Alex Hunsaker writes: > FWIW i just tested this with ~100 clients doing begin; ALTER TABLE > test_lock ADD COLUMN commit; here is the timing. Is there some other > concern that im not seeing? The situation where someone quickly acquires the lock isn't much of an issue, because they'll drop it almost immediately after the permissions check fails. However consider a scenario like this: 1. Legitimate user U1 does a SELECT on table T and then goes to sleep with open transaction. 2. Nefarious user U2 does LOCK TABLE T (exclusively). He blocks behind U1's transaction, since the permissions check won't happen till he gets the lock. 3. Now, everybody else trying to use table T will queue up behind U2's request. Their operations might have been perfectly able to run in parallel with U1's AccessShareLock, but they'll wait behind an ungranted AccessExclusiveLock. So it's definitely not a purely academic concern. However, there isn't any part of this behavior that we can change without breaking other stuff :-( 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] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
Robert Haas writes: > But that leads me to a question - does the existing HashAggregate code > make any attempt to obey work_mem? No. 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] how to insure libpq(dll/so) for thread-safety?
Hi, In pgsql/src/interfaces/libpq/fe-exec.c, there are two variables: - static int static_client_encoding = PG_SQL_ASCII; static bool static_std_strings = false; If enable_thread_safety is "no", how to insure libpq(dll/so) for thread-safety? thanks wjzeng -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Problem with estimating pages for a table
Hello, I posted to the general list, and didn't receive any replies. Therefore, I am trying this list now, hopefully this is the right mailing list for this type of questions. I am trying to compute the no of pages of a table. I am using the formula : pages = ( columns width + 28) * no. of rows / block size For each varchar column - I add an extra 4 bytes For each numeric column - I add an extra 8 bytes Add a 28 bytes row overhead. For example if i have a table with col1: integer, col2: varchar, col3 varchar, I will get: pages = (col1width + col2width + 4 + col3width + 4 + 28)* no. of rows / block size The problem is that I have some problems for some tables where i have numeric and varchar columns. I tested on TPC-H database. - for table t1(c_custkey, int, c_nationkey int, c_acctbal numeric) i got similar result with the real no of pages. Here c_acctbal has 8 byte, and i added the extra 8 bytes. - for table t2(l_orderkey int, l_partkey int, l_quantiy, l_tax, l_extendedprice, l_discount) I got an error of 42 %. The last 4 columns are numeric and i added an extra 8 bytes for each of them -> 32 bytes. (colwidths + 32 + 28)*no.of rows/ block size I would have got a correct value, if i had added only 4 total bytes.. instead of the 32: (colwidths + 4 + 28)*no.of rows/ block size One more question. I do not understand how to use the aligment value property. Does it depend on the position of attribute in the table? I am using Postgres 8.3 Thank you very much for any help in this regard, Cristina
Re: [HACKERS] New trigger option of pg_standby
Fujii Masao wrote: On Tue, May 12, 2009 at 8:15 PM, Heikki Linnakangas wrote: Here's another idea: Let's modify xlog.c so that when the server asks for WAL file X, and restore_command returns "not found", the server will not ask for any WAL files >= X again (in that recovery session). Presumably if X doesn't exist, no later files will exist either. That would be pretty simple change, and it would allow us to go with the simpler implementation in pg_standby and just remove the trigger file immediately when it returns "not found" (instead of removing it when history file is requested). That would make it safe to copy extra WAL files into pg_xlog, even in fast failover mode. Does anyone see a hole in that idea? Probably yes. The trigger file would remain after failover if the restored WAL file has the invalid record which means the end of WAL. In this case, "not found" is not returned. Yep. That's not pleasant either :-(. I don't think we're going to get this to work reliably without extending the interface between the backend and restore_command. We've discussed many methods and there's always some nasty corner-case like that. I think we should leave back-branches as is, and go with Simon's suggestion to add new "recovery_end_command" that's run when the recovery is finished. That's simpler and more reliable than any of the other approaches we've discussed, and might become handy for other purposes as well. Does someone want to take a stab at writing a patch for that? -- Heikki Linnakangas EnterpriseDB 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] New trigger option of pg_standby
Hi, On Tue, May 12, 2009 at 8:15 PM, Heikki Linnakangas wrote: > Fujii Masao wrote: >> >> On Thu, Apr 23, 2009 at 4:49 PM, Heikki Linnakangas >> wrote: >>> >>> This is getting complicated, though. I guess it would be enough to >>> document >>> that you mustn't copy any extra files into pg_xlog if you use a fast >>> trigger. >> >> Agreed. I added this note into document. >> >> Attached is the updated patch. I also fixed my bug which >> pg_standby returns 0 even if the requested file fails to be >> restored in smart mode. >> >> This patch is ready to commit, I think. Please review this. > > Looking at this again.. > > Deleting the trigger file when a history file is requested: > >> /* >> * Get rid of the trigger file at the end of archive >> recovery. >> * Otherwise, it would unexpectedly cause the subsequent >> warm-standby to >> * end. >> * >> * Here is the right place to remove the trigger file since >> a timeline >> * history file is requested only at the beginning and end >> of archive >> * recovery. >> */ > > changes the behavior in a subtle way: if you create trigger file before > starting recovery, it will be deleted when the recovery is started and no > failover is done. Currently, it will end the recovery immediately. > > That makes me uncomfortable to back-patch this. That change in behavior > might be hard to work-around: the process that creates the trigger file > would have to make sure that the server has started recovery before creating > the file. > > Here's another idea: Let's modify xlog.c so that when the server asks for > WAL file X, and restore_command returns "not found", the server will not ask > for any WAL files >= X again (in that recovery session). Presumably if X > doesn't exist, no later files will exist either. That would be pretty simple > change, and it would allow us to go with the simpler implementation in > pg_standby and just remove the trigger file immediately when it returns "not > found" (instead of removing it when history file is requested). That would > make it safe to copy extra WAL files into pg_xlog, even in fast failover > mode. > > Does anyone see a hole in that idea? Probably yes. The trigger file would remain after failover if the restored WAL file has the invalid record which means the end of WAL. In this case, "not found" is not returned. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] display previous query string of idle-in-transaction
After taking look at our monitoring system i think some hint about previous SQL might be useful. dbadb70db_nameWARNING1long transactions, duration > 2690min user=postgres pid=7887 waiting=False query= in transaction Currently i have no idea what exactly did i kill without digging in logs which might have rotated anyway by now. regards, Asko On Tue, May 12, 2009 at 6:37 PM, decibel wrote: > On Mar 27, 2009, at 2:36 AM, Simon Riggs wrote: > >> Not really. I want to understand the actual problem with >> idle-in-transaction so we can consider all ways to solve it, rather than >> just focus on one method. >> > > > I have to distinct problems with idle in transaction. One is reporting > users / the tools they're using. I'll often find transactions that have been > open for minutes or hours. But, that's not a big deal for me, because that's > only impacting londiste slaves, and I have no problem just killing those > backends. > > What does concern me is seeing idle in transaction from our web servers > that lasts anything more than a few fractions of a second. Those cases worry > me because I have to wonder if that's happening due to bad code. Right now I > can't think of any way to figure out if that's the case other than a lot of > complex logfile processing (assuming that would even work). But if I knew > what the previous query was, I'd at least have half a chance to know what > portion of the code was responsible, and could then look at the code to see > if the idle state was expected or not. > -- > Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > > > > -- > 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] DROP TABLE vs inheritance
On Tue, May 12, 2009 at 14:40, Alex Hunsaker wrote: > Hrm on second thought I think your right. They only get the lock > until the permission check, and I have a hard time seeing how someone > can take real advantage of that. The owner that is trying to lock > table should get the lock almost immediately even if there are say a > few hundred non-owner clients trying to lock it. FWIW i just tested this with ~100 clients doing begin; ALTER TABLE test_lock ADD COLUMN commit; here is the timing. Is there some other concern that im not seeing? (pre 100 clients) => LOCK table test_lock; LOCK TABLE Time: 1.955 ms (now with 100 non-owner clients trying to do ALTER TABLE) => LOCK TABLE test_lock; LOCK TABLE Time: 71.746 ms *shrugs* -- 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] DROP TABLE vs inheritance
On Mon, May 11, 2009 at 21:18, Tom Lane wrote: > However, he can do that anyway via ALTER TABLE, which > will happily take out AccessExclusiveLock before it checks any > permissions. So I'm not seeing the point of risking unsafe behavior > in LOCK TABLE. I would rather fix ALTER TABLE to do something similar to test and test-and-set... From a quick look TRUNCATE also seems to be prone to this. -- 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] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
On Tue, May 12, 2009 at 2:21 AM, Pavel Stehule wrote: >> Moreover, I guess you don't even need to buffer tuples to aggregate by >> different keys. What you have to do is only to prepare more than one >> hash tables (, or set up sort order if the plan detects hash table is >> too large to fit in the memory), and one time seq scan will do. The >> trans values are only to be stored in the memory, not the outer plan's >> results. It will win greately in performance. > > it was my first solution. But I would to prepare one non hash method. > But now I thinking about some special executor node, that fill all > necessary hash parallel. It's special variant of hash agreggate. I think HashAggregate will often be the fastest method of executing this kind of operation, but it would be nice to have an alternative (such as repeatedly sorting a tuplestore) to handle non-hashable datatypes or cases where the HashAggregate would eat too much memory. But that leads me to a question - does the existing HashAggregate code make any attempt to obey work_mem? I know that the infrastructure is present for HashJoin/Hash, but on a quick pass I didn't notice anything similar in HashAggregate. And on a slightly off-topic note for this thread, is there any compelling reason why we have at least three different hash implementations in the executor? HashJoin/Hash uses one for regular batches and one for the skew batch, and I believe that HashAggregate does something else entirely. It seems like it might improve code maintainability, if nothing else, to unify these to the extent possible. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
