Re: [HACKERS] CIC and deadlocks
Tom Lane wrote: Pavan Deolasee [EMAIL PROTECTED] writes: Good point. I'm envisioning a procarray.c function along the lines of bool TransactionHasSnapshot(xid) which returns true if the xid is currently listed in PGPROC and has a nonzero xmin. CIC's cleanup wait loop would check this and ignore the xid if it returns false. Your point means that this function would have to take exclusive not shared lock while scanning the procarray, which is kind of annoying, but it seems not fatal since CIC isn't done all that frequently. Tom, If you haven't finished this yet, would you like me to work on this ? If I do it, I would mostly follow the path you suggested above, unless I run into something else. Thanks, Pavan -- EnterpriseDBhttp://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Query
Hi, As a part of my university project, I am trying to modify the postgres code to support parallel system. As the first step I have partitioned the data on different processors. And have kept a master node to process all the query requests. Whenever my master node is queried I need to push my query onto the backend processors. For this I need a way of connect to different backends via my master node's backend. I have tried different functions like: do_connect SendQuery PQconnectdb etc. But all of them give the same compilation error of not being able to find the reference to the functions. Now if I include command.o and common.opresent in src/bin/psql, I get the error of not referencing other functions. I there a way of accessing other backends through the master backend. Thanks Regards, Shaunak Godbole -- It is not the aptitude but the attitude that decides a persons altitude Shaunak Godbole Senior Undergraduate Computer Science Dept. IIT Powai, Mumbai 400076 Ph no: +91 98695 41960
Re: [PATCHES] [HACKERS] Arrays of Complex Types
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: How would we do that? Not create the array types in bootstrap mode? Or just special-case pg_statistic? Not generate them in bootstrap mode works for me. IIRC, there's code somewhere in there that allows anyarray to pass as a column type in bootstrap mode, so that seems to fit ... OK, summarising what looks to me like a consensus position, ISTM the plan could be: . fix makeArrayTypeName() or similar to make it try harder to generate a unique non-clashing name . remove the existing 62 instead of 63 name length restrictions . autogenerate array types for all explicitly or implicitly created composite types other than for system catalog objects. . defer for the present any consideration of a CREATE TYPE foo AS ARRAY ... command. Regarding catalog objects, we might have to try a little harder than just not generating in bootstrap mode - IIRC we generate system views (including pg_stats) in non-bootstrap mode. Maybe we just need to exempt anything in the pg_catalog namespace. What would happen if a user created a view over pg_statistic? Should the test be to avoid arrays for things that depend on the catalogs? Or maybe we should go to the heart of the problem and simply check for pseudo-types directly. cheers andrew ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Arrays of Complex Types
Andrew Dunstan [EMAIL PROTECTED] writes: Regarding catalog objects, we might have to try a little harder than just not generating in bootstrap mode - IIRC we generate system views (including pg_stats) in non-bootstrap mode. Maybe we just need to exempt anything in the pg_catalog namespace. What would happen if a user created a view over pg_statistic? Nothing: regression=# create view vvv as select * from pg_statistic; ERROR: column stavalues1 has pseudo-type anyarray which means we do have an issue for the pg_stats view. Now that I look instead of guessing, the existing test in CheckAttributeType is not on bootstrap mode but standalone mode: /* Special hack for pg_statistic: allow ANYARRAY during initdb */ if (atttypid != ANYARRAYOID || IsUnderPostmaster) ereport(ERROR, (errcode(ERRCODE_INVALID_TABLE_DEFINITION), errmsg(column \%s\ has pseudo-type %s, attname, format_type_be(atttypid; so for consistency we should use the same condition to suppress types for system catalogs. Or maybe we should go to the heart of the problem and simply check for pseudo-types directly. Actually we may have an issue already: regression=# create table zzz (f1 pg_statistic); CREATE TABLE I couldn't make it misbehave in a short amount of trying: regression=# insert into zzz values(row(0,0,0,0,0,0,0,0,0,0,0,0,0,array[1,2],null,null,null,array[12,13],null,null,null)); ERROR: ROW() column has type integer[] instead of type anyarray but I don't feel comfortable about this at all. Maybe CheckAttributeType should be made to recurse into composite columns. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] CIC and deadlocks
Pavan Deolasee [EMAIL PROTECTED] writes: If you haven't finished this yet, would you like me to work on this ? If I do it, I would mostly follow the path you suggested above, unless I run into something else. I'm not intending to work on it. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Query
Shaunak Godbole [EMAIL PROTECTED] writes: I there a way of accessing other backends through the master backend. It seems you're trying to reinvent contrib/dblink. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] select ('{}'::text[])[1] returns NULL -- is it correct?
Thinking about XPath's output in cases such as 'SELECT xpath('/a', 'b
/');' I've realized that in such cases an empty array should be
returned (now we have NULL for such cases).
Why? Because database _knows_ that there is no element -- this is not
NULL's case (unknown).
Then I've examined how the work with arrays in Postgres is organized.
And now the result of the following query seems to be a little bit
strange for me:
xmltest=# select ('{}'::text[])[1] IS NULL;
?column?
--
t
(1 row)
As I can see here, when I ask for element that doesn't exist, the
database returns NULL for me. Maybe it's well-known issue (and
actually I understood this behaviour before), but strictly speaking it
seems wrong for me: the database _knows_ that there is no element, so
why NULL?
Actually, I do not know what output value would be the best for this
case (and I understand that it'd be very painful to change the
behaviour because of compatibility issues), so my questions are:
1. is it worth to trigger at least notice message (WARNING?) for such cases?
2. what should I do with XPath function? There is strong analogy
between its case and array's case in my mind... Should I leave NULLs,
or empty arrays are better?
BTW, is there any better way to select empty array as a constant
(better then my '{}'::text[])?
--
Best regards,
Nikolay
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] select ('{}'::text[])[1] returns NULL -- is it correct?
Nikolay Samokhvalov [EMAIL PROTECTED] writes: As I can see here, when I ask for element that doesn't exist, the database returns NULL for me. Maybe it's well-known issue (and actually I understood this behaviour before), but strictly speaking it seems wrong for me: the database _knows_ that there is no element, so why NULL? This is operating as designed, per http://www.postgresql.org/docs/8.2/static/arrays.html : An array subscript expression will return null if either the array : itself or any of the subscript expressions are null. Also, null is : returned if a subscript is outside the array bounds (this case does not : raise an error). For example, if schedule currently has the dimensions : [1:3][1:2] then referencing schedule[3][3] yields NULL. Similarly, an : array reference with the wrong number of subscripts yields a null rather : than an error. AFAIR it's always been like that, so changing it seems exceedingly likely to break some peoples' applications. It's not completely without analogies in SQL, anyway: consider the behavior of INSERT when fewer columns are provided than the table has. Pretending that elements outside the stored range of the array are null is not all that different from silently adding nulls to a row-to-be-stored. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Adjusting index special storage for pg_filedump's convenience
Historically, pg_filedump http://sources.redhat.com/rhdb/utilities.html has relied on the size of a page's special space to determine which kind of index it is looking at (btree, hash, etc) so that it can dump the contents of the special space nicely. This is pretty ugly of course, but there isn't a whole lot of other context available. (Before you say why not look at the metapage?, consider that we may be looking at a segment file that doesn't contain the metapage, and gist and gin don't use one anyway.) As of 8.2 it's entirely broken for gist because gist and btree now have the same-size special space, ie 16 bytes; and it looks like bitmap indexes will too. We put in a workaround a long time ago to make it possible to tell the difference between btree and hash special space, which are also the same size: there's an unused 16 bits in hash special space that we fill with a specific value. As of 8.2 this doesn't work as well as it used to, because the corresponding space in a btree page is now used for a vacuum cycle ID and so there's 1 chance in 65536 of a false match. Still, it's a lot better than nothing. I'd like to tweak things for 8.3 so that pg_filedump can work reasonably well again. It looks like the hash solution would work for gist, gin, and bitmap: rearranging fields would allow us to put in a 16-bit ID field in all three cases. (For bitmap, I'm assuming that bm_hrl_words_used could be reduced to 16 bits without problem --- it is a per-page count not something larger, right?) One problem with that is that with four special values, there'd be 1 chance in 16384 of misidentifying a btree page because of chance values of the vacuum cycle ID. This can be improved a bit if we put the flags fields (for those index types that have 'em) in a consistent place too: we can disbelieve that an index is of type X if it doesn't have a flags value that fits. I don't see any way to make it completely bulletproof without enlarging the special space, which seems an unreasonable price to pay. But even one chance in 16K is way better than the current situation. Thoughts, objections, better ideas? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Query
Ühel kenal päeval, E, 2007-04-09 kell 10:56, kirjutas Tom Lane: Shaunak Godbole [EMAIL PROTECTED] writes: I there a way of accessing other backends through the master backend. It seems you're trying to reinvent contrib/dblink. Or you may want to use pl/proxy ( https://developer.skype.com/SkypeGarage/DbProjects/PlProxy ) which can be used for data partitioning between different hosts/backends if the interface to data is postgresql functions. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
The other platform I've whined about missing for some time is HP-UX, especially on PA-RISC. But that's a whole different story. there are more obscure and rare platforms(both in terms that might be a win for the buildfarm but HP-UX is really missing. Hello, I have access to a PA-RISC machine running HP-UX 11.11. Unfortunately the machine is on a dedicated network and has no Internet access. It should be possible to create a mirror of the CVS repository on my machine (which has access to both the Internet and the dedicated network) so that the HP-UX server could get the sources from my machine. But I am not sure whether the results could be reported back to the buildfarm. Cheers, Adrian Maier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] select ('{}'::text[])[1] returns NULL -- is it correct?
Nikolay Samokhvalov wrote: 2. what should I do with XPath function? There is strong analogy between its case and array's case in my mind... Should I leave NULLs, or empty arrays are better? Empty array appears to be correct. The fact that arrays don't appear to work as you might like is a different problem that should not affect the specification of the XPath functionality. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
On Mon, 9 Apr 2007, Adrian Maier wrote: The other platform I've whined about missing for some time is HP-UX, especially on PA-RISC. But that's a whole different story. there are more obscure and rare platforms(both in terms that might be a win for the buildfarm but HP-UX is really missing. Hello, I have access to a PA-RISC machine running HP-UX 11.11. Unfortunately the machine is on a dedicated network and has no Internet access. It should be possible to create a mirror of the CVS repository on my machine (which has access to both the Internet and the dedicated network) so that the HP-UX server could get the sources from my machine. But I am not sure whether the results could be reported back to the buildfarm. I think I'll be able to set up my HP-UX 11.11 box here, as soon as it gets fixed, and assuming either the bundled compiler will work or I can get GCC on it. This will take a week or 2, but I have permission now. (This box can get out to the internet via our proxy). LER Cheers, Adrian Maier -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Mon, Apr 09, 2007 at 10:40:49AM -0400, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Regarding catalog objects, we might have to try a little harder than just not generating in bootstrap mode - IIRC we generate system views (including pg_stats) in non-bootstrap mode. Maybe we just need to exempt anything in the pg_catalog namespace. What would happen if a user created a view over pg_statistic? Nothing: regression=# create view vvv as select * from pg_statistic; ERROR: column stavalues1 has pseudo-type anyarray which means we do have an issue for the pg_stats view. Now that I look instead of guessing, the existing test in CheckAttributeType is not on bootstrap mode but standalone mode: /* Special hack for pg_statistic: allow ANYARRAY during initdb */ if (atttypid != ANYARRAYOID || IsUnderPostmaster) ereport(ERROR, (errcode(ERRCODE_INVALID_TABLE_DEFINITION), errmsg(column \%s\ has pseudo-type %s, attname, format_type_be(atttypid; so for consistency we should use the same condition to suppress types for system catalogs. Groovy :) Or maybe we should go to the heart of the problem and simply check for pseudo-types directly. Actually we may have an issue already: regression=# create table zzz (f1 pg_statistic); CREATE TABLE I couldn't make it misbehave in a short amount of trying: regression=# insert into zzz values(row(0,0,0,0,0,0,0,0,0,0,0,0,0,array[1,2],null,null,null,array[12,13],null,null,null)); ERROR: ROW() column has type integer[] instead of type anyarray but I don't feel comfortable about this at all. Maybe CheckAttributeType should be made to recurse into composite columns. That'd be great :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
Adrian Maier wrote: I have access to a PA-RISC machine running HP-UX 11.11. Unfortunately the machine is on a dedicated network and has no Internet access. It should be possible to create a mirror of the CVS repository on my machine (which has access to both the Internet and the dedicated network) so that the HP-UX server could get the sources from my machine. But I am not sure whether the results could be reported back to the buildfarm. The buildfarm has support for reporting via a proxy server. An appropriately configured instance of squid on the same machine that has the CVS mirror should do the trick. Look for BF_PROXY in the buildfarm config file. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] PGPROCs of autovac workers (was Re: [PATCHES] autovacuum multiworkers, patch 5)
Alvaro Herrera wrote: Thanks! I had already incorporated the foreach_worker changes into my code, and later realized that there's an important bug regarding the PGPROC of the workers, so I've reworked the patch, which meant that the foreach_worker() macro went away completely. FWIW, the problem was that it is difficult to keep the max_connections control and still allow extra connections for autovacuum so that it doesn't hinder regular operation. The first thing I tried was enlarging the PGPROC array, but the problem with that is that the max_connection tests get unwieldy (it would have to cycle through all used PGPROCs and count the autovacuum ones). So I'm now leaning towards having autovacuum keep their PGPROCs separately, similarly to what the 2-phase code does, the main difference being that 2PC doesn't have semaphores, while these ones will because they need to acquire locks. This needs a bit of rejigger in InitProcess() so that it acquires a PGPROC from ProcGlobal if a regular backend, or from autovac's array otherwise. This has not been very invasive. If there's an objection to this, and/or better ideas, please speak quickly! -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
Larry Rosenman ler@lerctr.org writes: I think I'll be able to set up my HP-UX 11.11 box here, as soon as it gets fixed, and assuming either the bundled compiler will work or I can get GCC on it. If the bundled compiler is still the same non-ANSI-C weakling that was bundled in HPUX 10, there's no chance. It would be great to have a buildfarm member using HP's real ANSI-spec C compiler though. I still do a lot of my own development on HPUX 10 + gcc, so I'm not particularly worried about lack of that combination in the buildfarm. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] elog(FATAL) vs shared memory
Tom Lane wrote: Stuart Bishop [EMAIL PROTECTED] writes: After a test is run, the test harness kills any outstanding connections so we can drop the test database. Without this, a failing test could leave open connections dangling causing the drop database to block. Just to make it perfectly clear: we don't consider SIGTERMing individual backends to be a supported operation (maybe someday, but not today). That's why you had to resort to plpythonu to do this. I hope you don't have anything analogous in your production databases ... No - just the test suite. It seems the only way to terminate any open connections, which is a requirement for hooking PostgreSQL up to a test suite or any other situation where you need to drop a database *now* rather than when your clients decide to disconnect (well... unless we refactor to start a dedicated postgres instance for each test, but our overheads are already pretty huge). -- Stuart Bishop [EMAIL PROTECTED] http://www.canonical.com/ Canonical Ltd.http://www.ubuntu.com/ signature.asc Description: OpenPGP digital signature
Re: [HACKERS] elog(FATAL) vs shared memory
Tom Lane wrote: Stuart Bishop [EMAIL PROTECTED] writes: After a test is run, the test harness kills any outstanding connections so we can drop the test database. Without this, a failing test could leave open connections dangling causing the drop database to block. Just to make it perfectly clear: we don't consider SIGTERMing individual backends to be a supported operation (maybe someday, but not today). That's why you had to resort to plpythonu to do this. I hope you don't have anything analogous in your production databases ... Ah, that could explain it. With the recent patches it seems to be working OK, but I guess we should find a more standard way to rejig the db during the test runs. Mark
Re: [HACKERS] What X86/X64 OS's do we need coverage for?
On Mon, 9 Apr 2007, Tom Lane wrote: Larry Rosenman ler@lerctr.org writes: I think I'll be able to set up my HP-UX 11.11 box here, as soon as it gets fixed, and assuming either the bundled compiler will work or I can get GCC on it. If the bundled compiler is still the same non-ANSI-C weakling that was bundled in HPUX 10, there's no chance. It would be great to have a buildfarm member using HP's real ANSI-spec C compiler though. I still do a lot of my own development on HPUX 10 + gcc, so I'm not particularly worried about lack of that combination in the buildfarm. Looks like we are a DSPP member, so I might be able to get the aCC bundle for free, and if so, I'll set it up with that. Thanks, LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Mon, Apr 09, 2007 at 10:14:41AM -0400, Andrew Dunstan wrote: . defer for the present any consideration of a CREATE TYPE foo AS ARRAY ... command. What is the rationale for allowing people to name the array type. When I originally proposed the syntax I presumed that the array name would be kept internal and hidden from the user, just that it would exist after that command. What possible reason is there for allowing the user to give the array type a name? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [PATCHES] [HACKERS] Arrays of Complex Types
Martijn van Oosterhout wrote: On Mon, Apr 09, 2007 at 10:14:41AM -0400, Andrew Dunstan wrote: . defer for the present any consideration of a CREATE TYPE foo AS ARRAY ... command. What is the rationale for allowing people to name the array type. When I originally proposed the syntax I presumed that the array name would be kept internal and hidden from the user, just that it would exist after that command. What possible reason is there for allowing the user to give the array type a name? Have a nice day, Some type systems have named array types, some don't. I can live happily with either. Are array types anonymous in the standard? At any rate, the point of the remark was to take this off the table for now. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Effects of GUC settings on automatic replans
On Mar 25, 2007, at 12:31 PM, Tom Lane wrote: Jim Nasby [EMAIL PROTECTED] writes: On Mar 21, 2007, at 5:11 AM, Tom Lane wrote: constraint_exclusion Hrm... wasn't that option added in case there was a bug in the exclusion code? Well, the bug was a lack of ways to get rid of plans that were no longer valid because of constraint changes; a problem that no longer exists now that the invalidation mechanism is there. (Hm, I think the docs need some updates now...) The other argument was that you might not want the costs of searching for contradictory constraints if your workload was such that the search never or hardly ever succeeds. That still justifies the existence of this GUC variable, I think, but I don't see that it's a reason to force replanning if the variable is changed. Certainly it's not any more interesting than any of the other variables affecting planner behavior. I'm doubtful that there are any cases where not doing the search would be worth the time saved, since it'd mean you'd be getting data out of most/all partitions at that point... If we are going to leave the GUC I think we should default it to ON. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Changing semantics of autovacuum_cost_limit
On Mar 26, 2007, at 2:01 AM, Galy Lee wrote: As AUTOVACUUM is having multiple workers now, the semantics of autovacuum_cost_limit also need to be redefined. Currently, autovacuum_cost_limit is the accumulated cost that will cause one single worker vacuuming process to sleep. It is used to restrict the I/O consumption of a single vacuum worker. When there are N workers, the I/O consumption by autovacuum workers can be increased by N times. This autovacuum_cost_limit semantics produces unpredictable I/O consumption for multiple-autovacuum-workers. One simple idea is to set cost limit for every worker to: autovacuum_cost_limit / max_autovacuum_workers. But for scenarios which have fewer active workers, it is obvious unfair to active workers. So a better way is to set cost limit of every active worker to: autovacuum_cost_limit/autovacuum_active_workers. This ensures the I/O consumption of autovacuum is stable. Worker can be extended to have its own cost_limit on share memory. When a worker is brought up or a worker has finished its work, launcher recalculates: worker_cost_limit= (autovacuum_cost_limit/ autovacuum_active_workers) and sets new value for each active workers. The above approach requires launcher can change cost delay setting of workers on-the-fly. This can be achieved by forcing VACUUM refers to the cost delay setting in its worker’s share memory every vacuum_delay_point. Any comments or suggestions? Well, ideally we'd set cost limit settings on a per-tablespace basis... but I agree that what you propose is probably the best bet for multiple daemons short of doing per-tablespace stuff. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Partitioned tables constraint_exclusion
See Simon's reply... timestamptz math is *not* IMMUTABLE, because sessions are free to change their timezone at any time. I bet you can get some invalid results using that function with a clever test case. On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote: Weslee Bilodeau wrote: Mainly its because the value comes from a reporting system that has minimal brains, it passes values it gets from the user directly into a query. IE, they enter '1 month', which I use to populate the interval value, ts ( NOW() - $VALUE ) But, in the example I did a timestamp - interval, the exact date, not NOW() - Still didn't work. I'm guessing anything that has to think, math, etc is not valid for constrain_exclusion? Its not in the docs anywhere, so trying to isolate what can and can't be done. This works - CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE STRICT AS $$ SELECT NOW() - $1 $$ LANGUAGE SQL ; SELECT count(*) FROM master WHERE var_ts now_interval( '1 month' ); This doesn't work - SELECT count(*) FROM master WHERE var_ts ( NOW() - '1 month'::interval ); This works for me, as the reporting system I know doesn't change timezones, and function cache doesn't last longer then the current select? But, its basically the exact same logic in both cases? Weslee ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Effects of GUC settings on automatic replans
Jim Nasby [EMAIL PROTECTED] writes: On Mar 25, 2007, at 12:31 PM, Tom Lane wrote: The other argument was that you might not want the costs of searching for contradictory constraints if your workload was such that the search never or hardly ever succeeds. That still justifies the existence of this GUC variable, I think, but I don't see that it's a reason to force replanning if the variable is changed. Certainly it's not any more interesting than any of the other variables affecting planner behavior. I'm doubtful that there are any cases where not doing the search would be worth the time saved, since it'd mean you'd be getting data out of most/all partitions at that point... You've got some kind of blinders on, Jim ... queries against large partitioned tables are not the only ones in the world, or even most of them. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Mon, Apr 09, 2007 at 04:07:16PM -0400, Andrew Dunstan wrote: Some type systems have named array types, some don't. I can live happily with either. Are array types anonymous in the standard? Yes, they're anonymous in the standard. That doesn't mean we can't give them names if we wanted... At any rate, the point of the remark was to take this off the table for now. Sure, once the array types are created automatically the command becomes completely redundant. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Partitioned tables constraint_exclusion
Jim Nasby wrote: See Simon's reply... timestamptz math is *not* IMMUTABLE, because sessions are free to change their timezone at any time. I bet you can get some invalid results using that function with a clever test case. I'm pretty sure it could easily be broken. But to make it easier for me, I know that the reporting system connects, runs the query, and disconnects. So I'm so far safe using my current system. If the system had persistent connections and changed timezones a lot, it might however cause problems. Its been the only way that I could get it to be smart enough to not use the tables outside its range. With the tables growing 2+ million rows a day, approaching 1 billion rows, its helps performance a lot. This works at least until the ongoing discussion of partitioned tables hopefully improves things in this area. On Mar 26, 2007, at 3:48 PM, Weslee Bilodeau wrote: This works - CREATE FUNCTION now_interval( interval ) RETURNS timestamptz IMMUTABLE STRICT AS $$ SELECT NOW() - $1 $$ LANGUAGE SQL ; SELECT count(*) FROM master WHERE var_ts now_interval( '1 month' ); This doesn't work - SELECT count(*) FROM master WHERE var_ts ( NOW() - '1 month'::interval ); This works for me, as the reporting system I know doesn't change timezones, and function cache doesn't last longer then the current select? But, its basically the exact same logic in both cases? Weslee ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) Weslee ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Group Commit
Heikki Linnakangas [EMAIL PROTECTED] writes: I've been working on the patch to enhance our group commit behavior. The patch is a dirty hack at the moment, but I'm settled on the algorithm I'm going to use and I know the issues involved. One question that just came to mind is whether Simon's no-commit-wait patch doesn't fundamentally alter the context of discussion for this. Aside from the prospect that people won't really care about group commit if they can just use the periodic-WAL-sync approach, ISTM that one way to get group commit is to just make everybody wait for the dedicated WAL writer to write their commit record. With a sufficiently short delay between write/fsync attempts in the background process, won't that net out at about the same place as a complicated group-commit patch? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Adjusting index special storage for pg_filedump's convenience
On Mon, 9 Apr 2007, Tom Lane wrote: We put in a workaround a long time ago to make it possible to tell the difference between btree and hash special space, which are also the same size: there's an unused 16 bits in hash special space that we fill with a specific value. As of 8.2 this doesn't work as well as it used to, because the corresponding space in a btree page is now used for a vacuum cycle ID and so there's 1 chance in 65536 of a false match. Still, it's a lot better than nothing. Sounds... reasonable. Especially if you add the flags test below. I'd like to tweak things for 8.3 so that pg_filedump can work reasonably well again. It looks like the hash solution would work for gist, gin, and bitmap: rearranging fields would allow us to put in a 16-bit ID field in all three cases. (For bitmap, I'm assuming that bm_hrl_words_used could be reduced to 16 bits without problem --- it is a per-page count not something larger, right?) Yes, I've reduced this already but hadn't in previous patches, from memory. I'd add a filler of uint16 now. Got a number I should use? One problem with that is that with four special values, there'd be 1 chance in 16384 of misidentifying a btree page because of chance values of the vacuum cycle ID. This can be improved a bit if we put the flags fields (for those index types that have 'em) in a consistent place too: we can disbelieve that an index is of type X if it doesn't have a flags value that fits. I don't see any way to make it completely bulletproof without enlarging the special space, which seems an unreasonable price to pay. But even one chance in 16K is way better than the current situation. Sounds like the only workable approach. Thanks, Gavin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Group Commit
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: I've been working on the patch to enhance our group commit behavior. The patch is a dirty hack at the moment, but I'm settled on the algorithm I'm going to use and I know the issues involved. One question that just came to mind is whether Simon's no-commit-wait patch doesn't fundamentally alter the context of discussion for this. Aside from the prospect that people won't really care about group commit if they can just use the periodic-WAL-sync approach, ISTM that one way to get group commit is to just make everybody wait for the dedicated WAL writer to write their commit record. With a sufficiently short delay between write/fsync attempts in the background process, won't that net out at about the same place as a complicated group-commit patch? This is a good point. commit_delay was designed to allow multiple transactions to fsync with a single fsync. no-commit-wait is going to do this much more effectively (the client doesn't have to wait for the other transations). The one thing commit_delay gives us that no-commit-wait does not is the guarantee that a commit returned to the client is on disk, without any milliseconds delay. The big question is who is going to care about the milliseconds delay and is using a configuration that is going to benefit from commit_delay. Basically, commit_delay always had a very limited use-case, but now with no-commit-wait, commit_delay has an even smaller use-case. I think the big question is whether commit_delay is ever going to be generally useful. I tried to find out what release commit_delay was added, and remembered that the feature was so questionable we did not mention its addition in the 7.1 release notes. After six years, we are still unsure about the feature. Another big question is whether commit_delay is _ever_ going to be useful, and with no-commit-wait being added, commit_delay looks even more questionable and perhaps it should just be removed in 8.3. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Adjusting index special storage for pg_filedump's convenience
Gavin Sherry [EMAIL PROTECTED] writes: On Mon, 9 Apr 2007, Tom Lane wrote: ... I don't see any way to make it completely bulletproof without enlarging the special space, which seems an unreasonable price to pay. But even one chance in 16K is way better than the current situation. Sounds like the only workable approach. Actually, I realized after writing that that it *is* possible to make it bulletproof: all we have to do is make the BTCycleId wrap around at a little less than 64K, which adds about one line of code and doesn't materially change its reliability. That leaves a few bitpatterns free for IDs of other index types with no chance of collision. I made hash use 0xFF80 and gist 0xFF81; please use 0xFF82 for bitmaps. (GIN turns out not to need a code because its special space is a different size, so we can tell it apart anyway.) See patch already committed here: http://archives.postgresql.org/pgsql-committers/2007-04/msg00125.php regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Group Commit
On Mon, 9 Apr 2007, Bruce Momjian wrote: The big question is who is going to care about the milliseconds delay and is using a configuration that is going to benefit from commit_delay. I care. WAL writes are a major bottleneck when many clients are committing near the same time. Both times I've played with the commit_delay settings I found it improved the peak throughput under load at an acceptable low cost in latency. I'll try to present some numbers on that when I get time, before you make me cry by taking it away. An alternate mechanism that tells the client the commit is done when it hasn't hit disk is of no use for the applications I work with, so I haven't even been paying attention to no-commit-wait. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Group Commit
Greg Smith [EMAIL PROTECTED] writes: An alternate mechanism that tells the client the commit is done when it hasn't hit disk is of no use for the applications I work with, so I haven't even been paying attention to no-commit-wait. Agreed, if you need committed to mean committed then no-wait isn't going to float your boat. But the point I was making is that the infrastructure Simon proposes (ie, a separate wal-writer process) might be useful for this case too, with a lot less extra code than Heikki is thinking about. Now maybe that won't work, but we should certainly not consider these as entirely-independent patches. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Group Commit
On Mon, 9 Apr 2007, Bruce Momjian wrote: The big question is who is going to care about the milliseconds delay and is using a configuration that is going to benefit from commit_delay. I care. WAL writes are a major bottleneck when many clients are committing near the same time. Both times I've played with the commit_delay settings I found it improved the peak throughput under load at an acceptable low cost in latency. I'll try to present some numbers on that when I get time, before you make me cry by taking it away. Totally agreed here. I experienced throughput improvement by using commit_delay too. An alternate mechanism that tells the client the commit is done when it hasn't hit disk is of no use for the applications I work with, so I haven't even been paying attention to no-commit-wait. Agreed too. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
