[HACKERS] [EMAIL PROTECTED]: PGconn ?]
- Forwarded message from Mike Aubury <[EMAIL PROTECTED]> -
From: Mike Aubury <[EMAIL PROTECTED]>
To: Michael Meskes <[EMAIL PROTECTED]>
Subject: PGconn ?
Date: Wed, 30 Jan 2008 19:51:00 +
Any chance of adding this (or something similar) for the next RC?
ecpglib/connect.c :
PGconn* ECPGget_PGconn(const char *connection_name) {
struct connection * con;
con=ecpg_get_connection(connection_name);
if (con==NULL) return NULL;
return con->connection;
}
and then in include/ecpglib.h
#define HAS_ECPGget_PGconn
PGconn *ECPGget_PGconn(const char *connection_name);
--
Mike Aubury
Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Murlain Business Centre, Union Street, Chester, CH1 1QP
- End forwarded message -
I don't like the idea of adding a feature albeit a very small one to 8.3
at this point in time without even a small discussion. Mike explained to
me that he needs to access the PGconn structure of the current
connection. He used to get it by using an undocumented but exported
internal function in libecpg. However, with us cleaning up the namespace
this function now disappeared and I refused to reactivate it because it
only gave a pointer to an undocumented internal structure. However, the
approach he's proposing now (using the PGconn structure instead) works
inside the documented PGSQL framework and thus whould be fine. Is it
okay to add this to 8.3. now? Yes, I know, I need to add it to the
export list too.
In the long run I would like to implement whatever is missing in ecpg
and deprecate this function, but this isn't doable for 8.3.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] [EMAIL PROTECTED]: PGconn ?]
Michael Meskes <[EMAIL PROTECTED]> writes: > From: Mike Aubury <[EMAIL PROTECTED]> > Any chance of adding this (or something similar) for the next RC? > I don't like the idea of adding a feature albeit a very small one to 8.3 > at this point in time without even a small discussion. Mike explained to > me that he needs to access the PGconn structure of the current > connection. He used to get it by using an undocumented but exported > internal function in libecpg. However, with us cleaning up the namespace > this function now disappeared and I refused to reactivate it because it > only gave a pointer to an undocumented internal structure. However, the > approach he's proposing now (using the PGconn structure instead) works > inside the documented PGSQL framework and thus whould be fine. Is it > okay to add this to 8.3. now? Yes, I know, I need to add it to the > export list too. > In the long run I would like to implement whatever is missing in ecpg > and deprecate this function, but this isn't doable for 8.3. It's too late for 8.3 --- the tarball is already wrapped, and we won't consider a re-wrap short of some truly sickening bug report showing up in the next day or so. I'd suggest thinking in terms of a proper fix for 8.4. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Limit changes query plan
On Fri, Feb 01, 2008 at 12:08:56PM +0100, Gaetano Mendola wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi all, > I'm using 8.2.6 and I'm observing a trange behaviour using > offset and limits. Please post EXPLAIN ANALYZE output so we can see what's actually taking the time. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution > inevitable. > -- John F Kennedy signature.asc Description: Digital signature
[HACKERS] Limit changes query plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm using 8.2.6 and I'm observing a trange behaviour using offset and limits. This are the two queries that are puzzling me: explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 ; QUERY PLAN - Limit (cost=175044.75..175071.04 rows=10518 width=90) -> Sort (cost=175044.75..175071.04 rows=10518 width=90) Sort Key: c.nctr, c.nctn, c.ncts, c.rvel -> Hash Join (cost=25830.72..174342.12 rows=10518 width=90) Hash Cond: (c.id = dt.card_id) -> Bitmap Heap Scan on t_oa_2_00_card c (cost=942.36..148457.19 rows=101872 width=90) Recheck Cond: (ecp = 18) -> Bitmap Index Scan on i7_t_oa_2_00_card (cost=0.00..916.89 rows=101872 width=0) Index Cond: (ecp = 18) -> Hash (cost=22743.45..22743.45 rows=171593 width=8) -> Bitmap Heap Scan on t_oa_2_00_dt dt (cost=2877.26..22743.45 rows=171593 width=8) Recheck Cond: (_from <= 1550) Filter: (_to >= 1500) -> Bitmap Index Scan on i_oa_2_00_dt_from (cost=0.00..2834.36 rows=182546 width=0) Index Cond: (_from <= 1550) explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5; QUERY PLAN - Limit (cost=0.00..2125.12 rows=5 width=90) -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) -> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90) Filter: (ecp = 18) -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from <= 1550)) using the limit I have an execution time of minutes vs a some seconds. What am I missing here ? Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHov3I7UpzwH2SGd4RApR+AJ0dG/+0MoB3PMD1kRgQt0BisHwQBACgzVwC BN/SBWrvVxVE9eBLK0C1Pnw= =9Ucp -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Limit changes query plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Martijn van Oosterhout wrote: > On Fri, Feb 01, 2008 at 12:08:56PM +0100, Gaetano Mendola wrote: >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> Hi all, >> I'm using 8.2.6 and I'm observing a trange behaviour using >> offset and limits. > > Please post EXPLAIN ANALYZE output so we can see what's actually taking > the time. The analyze is still running (I launched it 30 mins ago), I'll post it as soon I have it. Disabling the nested_loop ( set enable_nestloop = false ) the query with the limit has now the same execution time without the limit. I don't get why a limit is going to change the query plan and most of all decreasing the performances. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHowXA7UpzwH2SGd4RAomqAJ409579Jk7d5FYWf92PjOYDRxWNIQCggg1w 1WJcVmn2g1MASBGh9OtCQ0Q= =h2Z6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Limit changes query plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gregory Stark wrote: > "Gaetano Mendola" <[EMAIL PROTECTED]> writes: > >> I don't get why a limit is going to change the query plan and most of all >> decreasing >> the performances. > > Until we see the explain analyze it won't be clear what exactly is going on. > But in theory a LIMIT can definitely change the plan because the planner knows > it won't need to generate all the rows to satisfy the LIMIT. > > In the plans you gave note that the plan for the unlimited query has a Sort so > it has to produce all the records every time. The second query produces the > records in order so if the LIMIT is satisfied quickly then it can save a lot > of work. > > It's evidently guessing wrong about the limit being satisfied early. The > non-indexed restrictions might be pruning out a lot more records than the > planner expects. Or possibly the table is just full of dead records. > Here the analyze result: explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5; QUERY PLAN --- Limit (cost=0.00..2125.12 rows=5 width=90) (actual time=3399923.424..3399960.174 rows=5 loops=1) -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) (actual time=3399923.420..3399960.156 rows=5 loops=1) -> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90) (actual time=3399892.632..3399896.773 rows=50 loops=1) Filter: (ecp = 18) -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50) Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from <= 1550)) Total runtime: 3399960.277 ms explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 ; QUERY PLAN - Limit (cost=175044.75..175071.04 rows=10518 width=90) (actual time=2425.138..2435.633 rows=3298 loops=1) -> Sort (cost=175044.75..175071.04 rows=10518 width=90) (actual time=2425.134..2428.812 rows=3298 loops=1) Sort Key: c.nctr, c.nctn, c.ncts, c.rvel -> Hash Join (cost=25830.72..174342.12 rows=10518 width=90) (actual time=797.540..2382.900 rows=3298 loops=1) Hash Cond: (c.id = dt.card_id) -> Bitmap Heap Scan on t_oa_2_00_card c (cost=942.36..148457.19 rows=101872 width=90) (actual time=70.212..1507.429 rows=97883 loops=1) Recheck Cond: (ecp = 18) -> Bitmap Index Scan on i7_t_oa_2_00_card (cost=0.00..916.89 rows=101872 width=0) (actual time=53.340..53.340 rows=97883 loops=1) Index Cond: (ecp = 18) -> Hash (cost=22743.45..22743.45 rows=171593 width=8) (actual time=726.597..726.597 rows=89277 loops=1) -> Bitmap Heap Scan on t_oa_2_00_dt dt (cost=2877.26..22743.45 rows=171593 width=8) (actual time=86.181..593.275 rows=89277 loops=1) Recheck Cond: (_from <= 1550) Filter: (_to >= 1500) -> Bitmap Index Scan on i_oa_2_00_dt_from (cost=0.00..2834.36 rows=182546 width=0) (actual time=80.863..80.863 rows=201177 loops=1) Index Cond: (_from <= 1550) Total runtime: 2440.396 ms Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHoytQ7UpzwH2SGd4RAujPAKDkM53sirwNFa7jH/Q3R2y1/QAcKQCgn9VH pUSwTkR3c963BoCbNwG+W6Y= =s7Vr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Limit changes query plan
"Gaetano Mendola" <[EMAIL PROTECTED]> writes: > I don't get why a limit is going to change the query plan and most of all > decreasing > the performances. Until we see the explain analyze it won't be clear what exactly is going on. But in theory a LIMIT can definitely change the plan because the planner knows it won't need to generate all the rows to satisfy the LIMIT. In the plans you gave note that the plan for the unlimited query has a Sort so it has to produce all the records every time. The second query produces the records in order so if the LIMIT is satisfied quickly then it can save a lot of work. It's evidently guessing wrong about the limit being satisfied early. The non-indexed restrictions might be pruning out a lot more records than the planner expects. Or possibly the table is just full of dead records. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Limit changes query plan
"Greg Stark" <[EMAIL PROTECTED]> writes: >> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 >> rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50) >> Index Cond: (dt.card_id = c.id) >> Filter: ((_to >= 1500) AND (_from <= 1550)) >> Total runtime: 3399960.277 ms > Also, are 1500 and 1550 user-supplied parameters or are they part of a small > set of possible values? You could consider having a partial index on "card_id > WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to match > exactly as long as they include all the records the query needs. That side of the join isn't where the problem is, though. If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel would probably fix the performance issue very nicely. 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] FW: bitemporal functionality for PostgreSQL
On Feb 1, 2008, at 10:42 AM, Luke Porter wrote: All Is there an interest in developing bitemporal functionality in PostgreSQL Regards Luke I can only speak for myself, but- definitely! Based on the googling I did on "bitemporal database", I kind of do this already with PostgreSQL. Some of my tables are insert-only and each row includes a committed time timestamp. That way, I don't need a separate audit log table, and "fixing" someone's mistake is as simple as copying old rows. The downside to this is that I need a view to represent the current "truth" and calculating the truth is more expensive than a simple table would be. Can you explain in more detail or provide references to how PostgreSQL could potentially handle temporal data better? One idea I had would be to blow the transaction ID up to 128 bits (no more wrapping!) and have it represent the nanoseconds since the epoch. Cheers, M ---(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] FW: bitemporal functionality for PostgreSQL
All Is there an interest in developing bitemporal functionality in PostgreSQL Regards Luke > From: [EMAIL PROTECTED]> Subject: Re: bitemporal functionality for PostgreSQL> To: [EMAIL PROTECTED]> Date: Fri, 1 Feb 2008 10:08:03 -0500> > Luke Porter wrote:> > > > Bruce> > > > I have a compelling solution to provide a database with bitemporal> > functionality. It is a comprehensive spec. Is there any interest> > in PostgreSQL providing this functionality. The area is sometimes> > referred to as "temporal agility". It has recently been covered> > as an emerging requirement by Garther and C J Date has recently> > lectured on the area (My solution was demo'd at the same event> > he spoke at). It is a transforming approach to data management> > in that it completely removes the need for the developer to> > address the time dimension - the database does it all.> > Yes, I think there would be interest. I would ask on the hackers email> list.> > --> Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us> EnterpriseDB http://postgres.enterprisedb.com> > + If your life is a hard drive, Christ can be your backup. + _ Telly addicts unite! http://www.searchgamesbox.com/tvtown.shtml
Re: [HACKERS] FW: bitemporal functionality for PostgreSQL
Luke Porter <[EMAIL PROTECTED]> writes: > Is there an interest in developing bitemporal functionality in PostgreSQL Is that anything like the late, mostly unlamented "time travel" feature? regards, tom lane PS: in general, defining what you want with one word and assuming everyone will grok the full meaning and implications of that is a good way to get ignored on this list. Assuming we have never heard of "bitemporal" is a better starting point. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Limit changes query plan
Gaetano Mendola <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: >> It's evidently guessing wrong about the limit being satisfied early. The >> non-indexed restrictions might be pruning out a lot more records than the >> planner expects. Or possibly the table is just full of dead records. > Here the analyze result: > explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, > pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON > (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY > nctr,nctn,ncts,rvel offset 0 limit 5; > > QUERY PLAN > --- > Limit (cost=0.00..2125.12 rows=5 width=90) (actual > time=3399923.424..3399960.174 rows=5 loops=1) > -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) (actual > time=3399923.420..3399960.156 rows=5 loops=1) > -> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c > (cost=0.00..3927779.56 rows=101872 width=90) (actual > time=3399892.632..3399896.773 rows=50 loops=1) > Filter: (ecp = 18) > -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt > (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50) > Index Cond: (dt.card_id = c.id) > Filter: ((_to >= 1500) AND (_from <= 1550)) > Total runtime: 3399960.277 ms It's guessing that there are 101872 rows altogether that have ecp = 18. Is that about right? If not, raising the statistics target for the table might fix the problem. If it is about right, then you may be stuck --- the problem then could be that the rows with ecp=18 aren't uniformly scattered in the i_oa_2_00_card_keys ordering, but are clustered near the end. Greg's comment about dead rows might be correct too --- the actual runtime for the indexscan seems kinda high even if it is scanning most of the table. Also, if this query is important enough, clustering by that index would improve matters, at the cost of possibly slowing down other queries that use other indexes. 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] [PATCHES] Better default_statistics_target
"Kevin Grittner" <[EMAIL PROTECTED]> writes: > On Thu, Jan 31, 2008 at 10:19 PM, in message > <[EMAIL PROTECTED]>, Robert Treat > <[EMAIL PROTECTED]> wrote: >> Bad plans from boosting to 100 or less? Or something much higher? > I boosted on a large number of columns based on domains. County > number columns (present in most tables) were set to 80. Some > columns were set all the way to 1000. When performance tanked, we > didn't have time to experiment, so we just backed it all out. > Perhaps I could do some more controlled testing soon against 8.3, > to narrow it down and confirm the current status of the issue. I > do seem to recall that simple queries weren't suffering, it was > those which joined many tables which had multiple indexes. That fits with the idea that eqjoinsel() is a main culprit. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Limit changes query plan
> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt > (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50) > Index Cond: (dt.card_id = c.id) > Filter: ((_to >= 1500) AND (_from <= 1550)) > Total runtime: 3399960.277 ms Also, are 1500 and 1550 user-supplied parameters or are they part of a small set of possible values? You could consider having a partial index on "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to match exactly as long as they include all the records the query needs. Another possibility is using something like "cube" from contrib to build a GIST index on <_to,_from>. I think you would need to load gist_btree as well for the first column on card_id. It doesn't help every use case though, you would have to experiment. But before experimenting with either of those things, what does "VACUUM VERBOSE t_oa_2_00_dt" say?
Re: [HACKERS] and waiting
"Gurjeet Singh" <[EMAIL PROTECTED]> writes: > I saw a strange behaviour on one of the production boxes. The > pg_stat_activity shows a process as and yet 'waiting' !!! On top of > it (understandably, since its IDLE), there are no entries for this pid in > pg_locks! Hmm, I can reproduce something like this by aborting a wait for lock. It seems the problem is that WaitOnLock() is ignoring its own good advice, assuming that it can do cleanup work after waiting. 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] Limit changes query plan
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > "Greg Stark" <[EMAIL PROTECTED]> writes: >>> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 >>> rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50) >>> Index Cond: (dt.card_id = c.id) >>> Filter: ((_to >= 1500) AND (_from <= 1550)) >>> Total runtime: 3399960.277 ms > >> Also, are 1500 and 1550 user-supplied parameters or are they part of a small >> set of possible values? You could consider having a partial index on >> "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have >> to match exactly as long as they include all the records the query needs. > > That side of the join isn't where the problem is, though. > > If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel > would probably fix the performance issue very nicely. > As always you are right, creating the index "ivan" btree (ecp, nctr, nctn, ncts, rvel) that query with the limit responds now in the blink of an eye: > explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, > pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5; QUERY PLAN - --- Limit (cost=0.00..370.03 rows=5 width=90) (actual time=0.102..0.608 rows=5 loops=1) -> Nested Loop (cost=0.00..778392.80 rows=10518 width=90) (actual time=0.099..0.594 rows=5 loops=1) -> Index Scan using ivan on t_oa_2_00_card c (cost=0.00..235770.34 rows=101872 width=90) (actual time=0.024..0.134 rows=50 loops=1) Index Cond: (ecp = 18) -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=50) Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from <= 1550)) Total runtime: 0.700 ms (8 rows) Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHo1SB7UpzwH2SGd4RAhTeAJ0WL49jjUgCWSrNopV/8L+rbOLaEgCfTDlh crAHZYxxTYz6VqTDggqW7x0= =dKey -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Backward reading
PostgreSQL allows "backward reading" tuples writing the tuple's length after and before the tuple proper, in case a 'randomAccess' is requested. Is there any example of backward reading tuples into PostgreSQL code? Thanks.
Re: [HACKERS] FW: bitemporal functionality for PostgreSQL
On Fri, 2008-02-01 at 15:42 +, Luke Porter wrote: > All > > Is there an interest in developing bitemporal functionality in > PostgreSQL > I am very interested in this topic, and I maintain the pgsql-temporal project at: http://pgfoundry.org/projects/temporal/ It's missing good docs and a few other things that I'd like, but it provides a good time interval type, including lots of useful operators, and GiST index support functions. For instance, you can do queries like: SELECT att1 FROM mytable WHERE during @> '2001-05-11 01:01:01'::timestamptz; which is a simple way to get all records where "during" contains the point in time '2001-05-11 01:01:01'. It's also indexable with GiST, meaning that query will perform well in a variety of situations. I'm going to spend some time getting the docs up to speed so people can actually use it. Regards, Jeff Davis ---(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] [PATCHES] Better default_statistics_target
>>> On Thu, Jan 31, 2008 at 10:19 PM, in message <[EMAIL PROTECTED]>, Robert Treat <[EMAIL PROTECTED]> wrote: > On Thursday 31 January 2008 09:55, Kevin Grittner wrote: >> >> I can confirm that I have had performance tank because of boosting >> the statistics target for selected columns. It appeared to be time >> spent in the planning phase, not a bad plan choice. Reducing the >> numbers restored decent performance. > > Bad plans from boosting to 100 or less? Or something much higher? I boosted on a large number of columns based on domains. County number columns (present in most tables) were set to 80. Some columns were set all the way to 1000. When performance tanked, we didn't have time to experiment, so we just backed it all out. Perhaps I could do some more controlled testing soon against 8.3, to narrow it down and confirm the current status of the issue. I do seem to recall that simple queries weren't suffering, it was those which joined many tables which had multiple indexes. -Kevin ---(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] Backward reading
On Fri, 2008-02-01 at 19:25 +0100, [EMAIL PROTECTED] wrote: > PostgreSQL allows "backward reading" tuples writing the tuple's length > after and before the tuple proper, in case a 'randomAccess' is > requested. > > Is there any example of backward reading tuples into PostgreSQL code? Don't think so, but we don't always use randomAccess anyway. Sounds like we might be able to drop the length at the end of each tuple in those cases... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] BUG #3921: CREATE TABLE / INCLUDING INDEXES fails with permission denied
"Andrew Gilligan" <[EMAIL PROTECTED]> writes: > test=> CREATE TABLE t2 (LIKE t1 INCLUDING INDEXES); > ERROR: permission denied for tablespace pg_default How annoying :-(. We could work around this particular manifestation with a patch to make generateClonedIndexStmt() not set index->tableSpace if the source index is in the database's default tablespace. However, it seems to me there are a number of definitional oddities in this area, so I'm bouncing this over to -hackers for some discussion. 1. DefineIndex() treats an omitted tablespace clause differently from explicitly specifying the tablespace that is the database's default: if you explicitly specify the space then you must have permissions on it, otherwise you don't need any. (This is the same behavior as in DefineRelation incidentally.) Maybe this isn't such a hot idea, and we should treat the two cases interchangeably? 2. Because heap_create() replaces MyDatabaseTableSpace with zero before storing reltablespace into pg_class, it is impossible to tell after the fact whether a table or index was created with an explicit tablespace specification that happened to match the DB's default, or whether the tablespace clause was omitted. This seems inconsistent with point 1, if we decide to maintain the current behavior that the cases are not treated identically. However we can't just change this, because of the way that CREATE DATABASE works. 3. Should LIKE INCLUDING INDEXES even try to copy the source index's tablespace? It doesn't try to copy the table's tablespace. (However, the new table's tablespace can be specified elsewhere in the command, whereas there's noplace to put a per-index tablespace spec.) IIRC it was reported as a bug that LIKE didn't copy index tablespaces, and we just "fixed" that without much thought, but maybe the point needs more careful consideration. Thoughts? 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] Backward reading
"Simon Riggs" <[EMAIL PROTECTED]> writes: > On Fri, 2008-02-01 at 19:25 +0100, [EMAIL PROTECTED] wrote: >> PostgreSQL allows "backward reading" tuples writing the tuple's length >> after and before the tuple proper, in case a 'randomAccess' is >> requested. >> >> Is there any example of backward reading tuples into PostgreSQL code? > > Don't think so, but we don't always use randomAccess anyway. Sounds like > we might be able to drop the length at the end of each tuple in those > cases... We already do. We only generate the "frozen" tape when we think it might be necessary. I think the easiest (possibly only?) way to trigger this case is to run the query in a cursor like: postgres=# set enable_indexscan = off; SET postgres=# explain select * from h order by i; QUERY PLAN Sort (cost=61772.22..62022.20 rows=4 width=512) Sort Key: i -> Seq Scan on h (cost=0.00..7666.94 rows=4 width=512) (3 rows) postgres=# begin; BEGIN postgres=# declare c cursor for select * from h order by i; DECLARE CURSOR postgres=# fetch 5 from c; i | r ---+-- 1 | 10352 2 | 15034 3 | 91904 4 | 89058 5 | 87001 (5 rows) postgres=# fetch backward 5 from c; i | r ---+-- 4 | 89058 3 | 91904 2 | 15034 1 | 10352 (4 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(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] and waiting
The situation seems pretty bad!! Here are the steps to reproduce in 'PostgreSQL 8.3beta2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux)': session 1: begin; session 1: update test set a = 112 where a = 112; session 2: update test set a = 113 where a = 112; --waits session 1: select * from pg_stat_activity; -- executed this a few times before executing 'select version()' and then following: session 1: -- see end of mail for this query procpid | current_query | waiting | duration | backend_start -++-+--+--- 12577 | update test set a = 113 where a = 112; | t | -00:01: 35.782881 | 2008-02-01 13:36:15.31027-08 11975 | select * from pg_stat_activity ; | f | -00:01: 52.554697 | 2008-02-01 13:30:40.396392-08 (2 rows) session 1: select * from pg_locks locktype| database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted ---+--+--+--+---++---+-+---+--+--- -+---+--+- transactionid | | | | || 390 | | | | 2/14 | 12577 | ShareLock| f transactionid | | | | || 390 | | | | 1/9 | 11975 | ExclusiveLock| t Then, session 2: ^C Cancel request sent ERROR: canceling statement due to user request session1: procpid | current_query | waiting | duration | backend_start -++-+--+--- 12577 | update test set a = 113 where a = 112; | t | -00:01: 35.782881 | 2008-02-01 13:36:15.31027-08 11975 | select * from pg_stat_activity ; | f | -00:01: 52.554697 | 2008-02-01 13:30:40.396392-08 (2 rows) session 1: select * from pg_locks ; session 1: select pg_backend_pid(); pg_backend_pid 11975 The last mentioned output of shows session 1 executing 'select * from p_s_a', whereas the _is_ being executed in session 1!!! This result is consistently returned for a while, and later... session 2: select pg_backend_pid(); pg_backend_pid 12577 session 1: procpid | current_query | waiting |duration | backend_start -+---+-+-+--- 11975 | in transaction | f | 00:06:08.671029 | 2008-02-01 13:30:40.396392-08 (1 row) After a while again: session 1: -- notice 2 not 1; 'select *' comes back to haunt!!! procpid | current_query | waiting | duration | backend_start -++-+--+--- 12577 | update test set a = 113 where a = 112; | t | -00:01: 35.782881 | 2008-02-01 13:36:15.31027-08 11975 | select * from pg_stat_activity ; | f | -00:01: 52.554697 | 2008-02-01 13:30:40.396392-08 (2 rows) session 1: -- 1 back in action procpid | current_query | waiting | duration | backend_start -++-+--+--- 12577 | update test set a = 113 where a = 112; | t | -00:01: 35.782881 | 2008-02-01 13:36:15.31027-08 11975 | select * from pg_stat_activity ; | f | -00:01: 52.554697 | 2008-02-01 13:30:40.396392-08 (2 rows) The is: select procpid, current_query::varchar(50), waiting, now() - query_start as duration, backend_start from pg_stat_activity where current_query <> '' and current_query not like '%DONT COUNT ME1 %' order by duration desc limit 10; The is: select procpid, current_query::varchar(50), waiting, now() - query_start as duration, backend_start from pg_stat_activity where current_query not like '%DONT COUNT ME1 %' order by duration desc limit 10; Found more bugs than I was looking for, to reproduce!!! The reporter also made an observation (on 8.2.4) that there were deadlocks detected at around the same time. Looked at WaitOnLock(), and clearly there's a problem, but is it at the same/only place we are suspecting it to be? Best regards, PS: Ran the ies 1 and 2 again, just before hitting 'send', and the result is the same: procpid | current_query | waiting | duration | backend_start -++-+--+--- 12577 | update test set a = 113 where a = 112; | t | -00:01: 35.782881 | 2008-02-01 13:36:15.31027-08 11975 | select *
Re: [HACKERS] BUG #3921: CREATE TABLE / INCLUDING INDEXES fails with permission denied
"Tom Lane" <[EMAIL PROTECTED]> writes: > 1. DefineIndex() treats an omitted tablespace clause differently from > explicitly specifying the tablespace that is the database's default: > if you explicitly specify the space then you must have permissions on > it, otherwise you don't need any. (This is the same behavior as in > DefineRelation incidentally.) Maybe this isn't such a hot idea, and > we should treat the two cases interchangeably? I always thought that was absolutely bizarre. Security should never depend on *how* you refer to an object. You should either have access to the object or not regardless of how you refer to it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Backward reading
-- From: "Gregory Stark" <[EMAIL PROTECTED]> Sent: Friday, February 01, 2008 10:31 PM To: "Simon Riggs" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; Subject: Re: [HACKERS] Backward reading Is there any example of backward reading tuples into PostgreSQL code? Don't think so, but we don't always use randomAccess anyway. Sounds like we might be able to drop the length at the end of each tuple in those cases... We already do. We only generate the "frozen" tape when we think it might be necessary. Thanks for your reply. I need to read tuples backward in order to rearrange runs on tapes in a different way than what Postres does now. Has that of "frozen tape" something to do with it? Regards, Manolo. I think the easiest (possibly only?) way to trigger this case is to run the query in a cursor like: postgres=# set enable_indexscan = off; SET postgres=# explain select * from h order by i; QUERY PLAN Sort (cost=61772.22..62022.20 rows=4 width=512) Sort Key: i -> Seq Scan on h (cost=0.00..7666.94 rows=4 width=512) (3 rows) postgres=# begin; BEGIN postgres=# declare c cursor for select * from h order by i; DECLARE CURSOR postgres=# fetch 5 from c; i | r ---+-- 1 | 10352 2 | 15034 3 | 91904 4 | 89058 5 | 87001 (5 rows) postgres=# fetch backward 5 from c; i | r ---+-- 4 | 89058 3 | 91904 2 | 15034 1 | 10352 (4 rows) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 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
[HACKERS] configurability of OOM killer
This page http://linux-mm.org/OOM_Killer says that you can hint the OOM killer to be more deferential towards certain processes. I am wondering if we can set the system up so that it skips postmaster, bgwriter etc, and feels more preference towards normal backends (but then, we would try to give them less points than other regular processes). That could make the system more robust overall, even if the sysadmin hasn't configured it. Incidentally, the same page notes that points are substracted from processes with raw IO capability; which means *r*cle is probably avoiding this problem altogether. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] and waiting
"Gurjeet Singh" <[EMAIL PROTECTED]> writes: > The situation seems pretty bad!! I think at least part of your problem is not understanding that a single transaction sees a frozen snapshot of pg_stat_activity. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] configurability of OOM killer
Alvaro Herrera <[EMAIL PROTECTED]> writes: > This page > http://linux-mm.org/OOM_Killer Egad. Whoever thought *this* was a good idea should be taken out and shot: The independent memory size of any child (except a kernel thread) is added to the score: /* * Processes which fork a lot of child processes are likely * a good choice. We add the vmsize of the childs if they * have an own mm. This prevents forking servers to flood the * machine with an endless amount of childs */ In other words, server daemons are preferentially killed, and the parent will *always* get zapped in place of its child (since the child cannot have a higher score). No wonder we have to turn off OOM kill. 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] configurability of OOM killer
Tom Lane wrote: Alvaro Herrera <[EMAIL PROTECTED]> writes: This page http://linux-mm.org/OOM_Killer Egad. Whoever thought *this* was a good idea should be taken out and shot: The independent memory size of any child (except a kernel thread) is added to the score: /* * Processes which fork a lot of child processes are likely * a good choice. We add the vmsize of the childs if they * have an own mm. This prevents forking servers to flood the * machine with an endless amount of childs */ In other words, server daemons are preferentially killed, and the parent will *always* get zapped in place of its child (since the child cannot have a higher score). No wonder we have to turn off OOM kill. That was pretty much my reaction. And it looks like you can't turn it off for postgres processes because that works by process group and we call setsid(), so we aren't in a single process group. cheers andrew ---(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] and waiting
On Feb 1, 2008 3:56 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Gurjeet Singh" <[EMAIL PROTECTED]> writes: > > The situation seems pretty bad!! > > I think at least part of your problem is not understanding that a single > transaction sees a frozen snapshot of pg_stat_activity. > > It does! I assumed that pg_stat_activity produced the transaction-independent snapshot of internal memory structures! Is that the case with pg_locks too!? I hope not. BTW, we cannot say that the pg_stat_activity behaves in a consistent manner (transactions-wise). From what I could infer, this view's results are frozen when you first query the view, not when the transaction started (which is how other (normal) relations behave). It's a bit confusing, and should be documented if this is the way it is intended to work; Something along the lines of : "In a transaction, this view will repeatedly show the same results that were returned by it's first invocation in the transaction." in a less confusing way :) So we are back to the original problem... Canceling a 'waiting' transaction does not revert the session's 'waiting' state back to 'false' (consistently reproducible). -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad 18° 32' 57.25"N, 73° 56' 25.42"E - Pune 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco * http://gurjeet.frihost.net Mail sent from my BlackLaptop device
Re: [HACKERS] Truncate Triggers
On Thu, Jan 31, 2008 at 11:45:55AM +, Simon Riggs wrote: > On Thu, 2008-01-31 at 10:22 +, Gregory Stark wrote: > > "Decibel!" <[EMAIL PROTECTED]> writes: > > > > > CLUSTER isn't DDL. Most forms of ALTER TABLE are. And CREATE blah, etc. > > > > Fwiw I would call CLUSTER DDL. Note that it does make a change that's > > visible > > in the table definition afterwards. > > > > There are plenty of DDL commands which modify data (CREATE INDEX, ATLER > > TABLE > > ALTER COLUMN TYPE). The defining characteristic of DDL is not that it > > doesn't > > modify the data but that it does modify the table definition. > > > > By that definition CLUSTER is DDL and TRUNCATE is DDL if you look at the > > implementation rather than the user-visible effects. > > Surely the question is more simple: do we want triggers on it? > > There's a clear case for TRUNCATE to have a triggers. > > Is there a clear case for any other statements (however you categorise > them)? If so, lets hear it, please. Having "triggers" on ALTER table means that replication systems can replicate changes automatically. Having triggers on CREATE, ALTER, DROP mean that you can audit DDL, or if desired prevent it. Searching the mailing lists for "ddl trigger" for the last year turns up 87 hits. This is something that a lot of people have been asking for. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpsnn3dolOrW.pgp Description: PGP signature
