[GENERAL] 8.2.1 TSearch changes?
Several /contrib/tsearch2 fixes (Teodor) Is there a more detailed list available? Does it obsolete tsearch_snowball_82.gz? Thanks! -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.1 TSearch changes?
On Tue, 9 Jan 2007, Hannes Dorbath wrote: Several /contrib/tsearch2 fixes (Teodor) Is there a more detailed list available? fixes thesaurus dictionary and rank function. Does it obsolete tsearch_snowball_82.gz? No, it's still needed. This is incompatible change, so we can't apply it for minor version. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Autovacuum Improvements
On Mon, 2007-01-08 at 22:29, Chris Browne wrote: [snip] > Based on the three policies I've seen, it could make sense to assign > worker policies: > > 1. You have a worker that moves its way through the queue in some sort of >sequential order, based on when the table is added to the queue, to >guarantee that all tables get processed, eventually. > > 2. You have workers that always pull the "cheapest" tables in the >queue, perhaps with some sort of upper threshold that they won't go >past. > > 3. You have workers that alternate between eating from the two ends of the >queue. > > Only one queue is needed, and there's only one size parameter > involved. > Having multiple workers of type #2 seems to me to solve the problem > you're concerned about. This sounds better, but define "cheapest" in #2... I actually want to continuously vacuum tables which are small, heavily recycled (insert/update/delete), and which would bloat quickly. So how do you define the cost function for having these tables the "cheapest" ? And how will you define the worker thread count policy ? Always 1 worker per category, or you can define the number of threads in the 3 categories ? Or you still have in mind time window policies with allowed number of threads per worker category ? (those numbers could be 0 to disable a a worker category). Other thing, how will the vacuum queue be populated ? Or the "queue" here means nothing, all workers will always go through all tables to pick one based on their own criteria ? My concern here is that the current way of checking 1 DB per minute is not going to work with category #2 tables, they really have to be vacuumed continuously sometimes. Cheers, Csaba. ---(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: [GENERAL] [OT] Advice needed on using postgres in commercial
On Mon, 2007-01-08 at 20:11, Ron Mayer wrote: [snip] > That's unlikely to work anyway. Organizations protecting valuable data > using technical approaches (DVDs, etc) find it gets out anyway. > Since you'll ship a client that can decrypt the data anyway, anyone with > a debugger could decrypt it (unless you only want it to run on Trusted > computing platform / palladium computers). Hmm, I do hope those techniques will never be good enough to stop hackers cracking them. But this is a philosophical and off topic question... the point is, I don't believe there is any kind of software/hardware out there that can't be cracked once it gets in hostile hands. On to the off topic thing, I really think all data should be legally forced to be free... research would have to change and maybe stumble a bit in the beginning, but I'm completely sure all interested parties would be forced to better cooperate and that would boost the advancement of science in the long term. Hiding research results will not work these days, so companies would be forced to do it in cooperation with all other players... of course not convenient for todays big corporations, but maybe they should disappear anyway. Cheers, Csaba. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Array constructor requires one argument
Hi,
Is there a particular reason why the array constructor requires at
least one argument? It's a bit irriating to have to special case zero
length arrays.
# select array[];
ERROR: syntax error at or near "]" at character 14
# select array[1];
array
---
{1}
(1 row)
# select '{}'::int4[];
int4
--
{}
(1 row)
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
[GENERAL] Confused by misleading error message on SP creation
This is PostgreSQL 8.1.4, and I am utterly lost. This function started in Informix and worked perfectly. Migrating to postgres resulted in about a *10,000* x slow down as it resorts to doing a sequential scan. In the sql below the addition of client id and report id to the initial select are an attempt to force an index; without them the SQL always results in a dog of a plan. The error message: psql:e.sql:54: ERROR: syntax error at or near "$2" at character 15 QUERY: SELECT $1 $2 := $3 CONTEXT: SQL statement in PL/PgSQL function "client_year_usage_det" near line 29 psql:e.sql:54: LINE 1: SELECT $1 $2 := $3 psql:e.sql:54: Is of *NO* use. Could someone please explain what the hell it means ? There is no construct $1 $2 := $3 *anywhere*. There is no SELECT on line 54. Or line 29. What the heck is going here ? This is one of the few things about postgres that I absolutely despise. The SPL support is crappy and horribly hard to debug. The hapless .sql file: --CREATE TYPE clyud_t AS ( sp_rptdate DATE, sp_appid CHAR(10), sp_is_subs INTEGER, sp_use_siu INTEGER, sp_hits DECIMAL, sp_s ius DECIMAL, sp_amount DECIMAL, sp_contractid INTEGER); --DROP TYPE clyud_read_t; --CREATE TYPE clyud_read_t AS (report_id INTEGER, sp_rptdate DATE, client_id CHAR(10), contract_id INTEGER, appid CHAR(10), sp_is_subs INTEGER, sp_use_siu INTEGER, hits DECIMAL, sius DECIMAL, total_amnt DECIMAL); CREATE OR REPLACE FUNCTION client_year_usage_det (CHAR(10), INTEGER, INTEGER) RETURNS SETOF clyud_t AS ' DECLARE p_client_id ALIAS FOR $1; p_year ALIAS FOR $2; p_showall ALIAS FOR $3; sp_year INTEGER; sp_tyr INTEGER; sp_sdate DATE; sp_edate DATE; sp_is_subs INTEGER; sp_use_siu INTEGER; clyud_rec clyud_t; clyu_inrec clyud_read_t; BEGIN IF ((p_year IS NULL) OR (p_year = 0)) THEN sp_year := (SELECT EXTRACT(YEAR FROM CURRENT_DATE)); ELSE sp_year := p_year; END IF; sp_tyr := sp_year + 1; sp_sdate := sp_year || ''-'' ||''01-01''; sp_edate := sp_tyr || ''-'' ||''01-01''; RAISE NOTICE ''showall is %, sdate is % and edate is %'', p_showall, sp_sdate, sp_edate; FOR clyu_inrec IN SELECT w.report_id,b.report_s_date,w.client_id,w.contract_id,w.appid,w.is_subscribed,w.use_sius,SUM(w.hits),SUM(w.sius),SUM(w.total_amnt) FROM reporting.crs_rpt_work w, reporting.billing_reports b WHERE w.report_id IN (SELECT b.report_id FROM reporting.billing_reports WHERE b.report_s_date >= sp_sdate AND b.report_s_date < sp_edate) AND w.client_id = p_client_id GROUP BY 1, 2, 3, 4, 5, 6, 7 ORDER BY 1 DESC, 2 DESC, 2, 3, 4 LOOP RAISE NOTICE ''a) date % appid % hits %'',clyu_inrec.sp_rptdate, clyu_inrec.appid, clyu_inrec.hits; clyud_rec.sp_rptdate := clyu_inrec.sp_rptdate; clyud_rec.sp_appid := clyu_inrec.appid; clyud_rec.sp_is_subs := clyu_inrec.sp_is_subs clyud_rec.sp_use_siu := clyu_inrec.sp_use_siu; clyud_rec.sp_hits := clyu_inrec.hits; IF (clyu_inrec.sp_use_siu <> 1) THEN clyud_rec.sius := clyu_inrec.hits; ELSE clyud_rec.sp_sius := clyu_inrec.sius; END IF; clyud_rec.sp_contractid := clyu_inrec.contract_id; RETURN NEXT clyud_rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Confused by misleading error message on SP creation
On Tue, Jan 09, 2007 at 03:42:47AM -0800, Gregory S. Williamson wrote: > This is PostgreSQL 8.1.4, and I am utterly lost. > The error message: > psql:e.sql:54: ERROR: syntax error at or near "$2" at character 15 > QUERY: SELECT $1 $2 := $3 > CONTEXT: SQL statement in PL/PgSQL function "client_year_usage_det" near > line 29 > psql:e.sql:54: LINE 1: SELECT $1 $2 := $3 > psql:e.sql:54: > > Is of *NO* use. Could someone please explain what the hell it means ? > There is no construct $1 $2 := $3 *anywhere*. There is no SELECT on > line 54. Or line 29. What the heck is going here ? Anything with a dollar sign is a parameter substituted by pl/pgsql. Anyway, at line 29 of the function I see this: > clyud_rec.sp_is_subs := clyu_inrec.sp_is_subs Missing semicolon at end of line. I think the $1 is the last paramater there, and $2 and $3 the command on the next line. As for speed decrease, you should trying working out which bit is slow... If it's choosing a seq scan, maybe you havn't ANALYZE'd? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Confused by misleading error message on SP creation
Doh ! Thanks for the swift response ... bet you are right. Getting punch drunk on too many electrons ... G -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Tue 1/9/2007 3:55 AM To: Gregory S. Williamson Cc: pgsql-general@postgresql.org Subject:Re: [GENERAL] Confused by misleading error message on SP creation On Tue, Jan 09, 2007 at 03:42:47AM -0800, Gregory S. Williamson wrote: > This is PostgreSQL 8.1.4, and I am utterly lost. > The error message: > psql:e.sql:54: ERROR: syntax error at or near "$2" at character 15 > QUERY: SELECT $1 $2 := $3 > CONTEXT: SQL statement in PL/PgSQL function "client_year_usage_det" near > line 29 > psql:e.sql:54: LINE 1: SELECT $1 $2 := $3 > psql:e.sql:54: > > Is of *NO* use. Could someone please explain what the hell it means ? > There is no construct $1 $2 := $3 *anywhere*. There is no SELECT on > line 54. Or line 29. What the heck is going here ? Anything with a dollar sign is a parameter substituted by pl/pgsql. Anyway, at line 29 of the function I see this: > clyud_rec.sp_is_subs := clyu_inrec.sp_is_subs Missing semicolon at end of line. I think the $1 is the last paramater there, and $2 and $3 the command on the next line. As for speed decrease, you should trying working out which bit is slow... If it's choosing a seq scan, maybe you havn't ANALYZE'd? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database "postgres"
woger151 wrote: Thanks for the quick response! And if your email address is correct, at a very early hour in the morning. Our Tom never sleeps. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Index vacuum improvements in 8.2
Joseph Shraibman wrote: > 8.0.x has the problem that VACUUM FULL on a table does not reclaim space > from the indexes, and I have to issue a separate REINDEX command. Has > this been fixed in later versions? No. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Questions about horizontal partitioning
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/07 20:39, Tom Lane wrote: > John Sales <[EMAIL PROTECTED]> writes: >> By doing this, I'm hoping that the query optimizer is smart >> enough to see that if a query comes in and requests only the >> six columns (that are in the narrower table) that PostgreSQL >> won't have to load the wider table into the buffer pool, and >> thereby actually have to only access about 10% the amount of >> disk that it presently does. > >> Is this a sound theory? > > No. It still has to touch the second table to confirm the > existence of rows to join to. But if a query /requests *only* the six columns (that are in the narrower table)/, why will the optimizer care about the other 224 columns? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFo5ZfS9HxQb37XmcRAtDRAJ41kKEN1Dv1iKXosTjy6IvMZKGccACfcZc9 e4pV+u0uLFisHcLu/gyuCvE= =q44l -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Questions about horizontal partitioning
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/07 20:39, Tom Lane wrote: John Sales <[EMAIL PROTECTED]> writes: By doing this, I'm hoping that the query optimizer is smart enough to see that if a query comes in and requests only the six columns (that are in the narrower table) that PostgreSQL won't have to load the wider table into the buffer pool, and thereby actually have to only access about 10% the amount of disk that it presently does. Is this a sound theory? No. It still has to touch the second table to confirm the existence of rows to join to. But if a query /requests *only* the six columns (that are in the narrower table)/, why will the optimizer care about the other 224 columns? It would. A query that uses an inner join implies that a matching entry must exist in both tables - so the join must occur, otherwise you could be returning rows that don't satisfy the join condition. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com Expert PostgreSQL Training: http://www.otg-nc.com/training-courses/category.php?cat_id=8 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFo5ZfS9HxQb37XmcRAtDRAJ41kKEN1Dv1iKXosTjy6IvMZKGccACfcZc9 e4pV+u0uLFisHcLu/gyuCvE= =q44l -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Questions about horizontal partitioning
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/09/07 07:28, Chander Ganesan wrote: > Ron Johnson wrote: >> >> On 01/08/07 20:39, Tom Lane wrote: >> >>> John Sales <[EMAIL PROTECTED]> writes: >>> By doing this, I'm hoping that the query optimizer is smart enough to see that if a query comes in and requests only the six columns (that are in the narrower table) that PostgreSQL won't have to load the wider table into the buffer pool, and thereby actually have to only access about 10% the amount of disk that it presently does. Is this a sound theory? >>> No. It still has to touch the second table to confirm the >>> existence of rows to join to. >>> >> >> But if a query /requests *only* the six columns (that are in the >> narrower table)/, why will the optimizer care about the other 224 >> columns? >> > It would. A query that uses an inner join implies that a matching entry > must exist in both tables - so the join must occur, otherwise you could > be returning rows that don't satisfy the join condition. Sure, if you were selecting those 6 columns from the "inner join view".Ah, now that I reread the OP, I see that that's what he seems to mean. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFo5tdS9HxQb37XmcRApwEAKDiqD86q3sh5eePFrgH3+o4LbTAYwCg1Oys 3/WT7eJvbxfE4RDY3E99NAo= =ix6x -END PGP SIGNATURE- ---(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: [GENERAL] Questions about horizontal partitioning
On 09/01/07, Ron Johnson <[EMAIL PROTECTED]> wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/07 20:39, Tom Lane wrote: > John Sales <[EMAIL PROTECTED]> writes: >> By doing this, I'm hoping that the query optimizer is smart >> enough to see that if a query comes in and requests only the >> six columns (that are in the narrower table) that PostgreSQL >> won't have to load the wider table into the buffer pool, and >> thereby actually have to only access about 10% the amount of >> disk that it presently does. > >> Is this a sound theory? > > No. It still has to touch the second table to confirm the > existence of rows to join to. But if a query /requests *only* the six columns (that are in the narrower table)/, why will the optimizer care about the other 224 columns? If you are doing an inner join (read normal join) the column has to exist in both tables to be in the final result. If your doing an outer join it depends upon its type (left, right or full) and then postgres may not optimise it out. Peter Childs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Questions about horizontal partitioning
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/09/07 07:28, Chander Ganesan wrote: Ron Johnson wrote: On 01/08/07 20:39, Tom Lane wrote: John Sales <[EMAIL PROTECTED]> writes: By doing this, I'm hoping that the query optimizer is smart enough to see that if a query comes in and requests only the six columns (that are in the narrower table) that PostgreSQL won't have to load the wider table into the buffer pool, and thereby actually have to only access about 10% the amount of disk that it presently does. Is this a sound theory? No. It still has to touch the second table to confirm the existence of rows to join to. But if a query /requests *only* the six columns (that are in the narrower table)/, why will the optimizer care about the other 224 columns? It would. A query that uses an inner join implies that a matching entry must exist in both tables - so the join must occur, otherwise you could be returning rows that don't satisfy the join condition. Sure, if you were selecting those 6 columns from the "inner join view".Ah, now that I reread the OP, I see that that's what he seems to mean. In theory, if the table with 6 columns was the child of the table with 200+ columns, and a PK-FK relationship existed, then the optimizer wouldn't need to check for the existence of the rows, since the PK-FK relationship would indicate that the rows did/do exist. However, I don't *think* that the optimizer takes that into account (though with PostgreSQL you never know, it's pretty smart stuff ;-) ) . Of course, that might make insert operations difficult, but if you are using a view to perform inserts you could probably handle that fairly easily in the on insert rule... -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com Expert PostgreSQL training: http://www.otg-nc.com/training-courses/category.php?cat_id=8 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFo5tdS9HxQb37XmcRApwEAKDiqD86q3sh5eePFrgH3+o4LbTAYwCg1Oys 3/WT7eJvbxfE4RDY3E99NAo= =ix6x -END PGP SIGNATURE- ---(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: [GENERAL] Array constructor requires one argument
Martijn van Oosterhout writes: > Is there a particular reason why the array constructor requires at > least one argument? Define the data type of SELECT ARRAY[]; 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: [GENERAL] Array constructor requires one argument
On Tue, Jan 09, 2007 at 10:02:09AM -0500, Tom Lane wrote:
> Martijn van Oosterhout writes:
> > Is there a particular reason why the array constructor requires at
> > least one argument?
>
> Define the data type of
>
> SELECT ARRAY[];
The same type as:
SELECT NULL;
or
SELECT '{}';
We allow the deferring of the decision of the type for normal strings,
why not in this case also? In fact, you could just make it an unknown
literal with just two curly vrace and you'd be all set.
Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Autovacuum Improvements
>-Original Message- >From: Alvaro Herrera [mailto:[EMAIL PROTECTED] >Sent: dinsdag 9 januari 2007 3:43 >To: Joris Dobbelsteen >Cc: Chris Browne; pgsql-general@postgresql.org >Subject: Re: [GENERAL] Autovacuum Improvements > >Joris Dobbelsteen wrote: >> Why not collect some information from live databases and >perform some >> analysis on it? > >We already capture and utilize operational data from >databases: [snip] What I was point out is that you have collected the metrics required to establish a maintenance policy (for vacuum in this case). This mean others can collect it too. >But that data alone is not sufficient. You (the DBA) have to >provide the system with timing information (e.g., at what time >is it appropriate to vacuum huge tables). [snip] That would be your expected result of what you want to happen. So there are a few more metrics that the DBA has to set. What is wrong with building a model and performing some analysis on this model. If it suits you situation it that would be a starting point. >Capturing data about someone's database is not going to help >someone else's vacuuming strategy, because their usage >patterns are potentially so different; and there are as many >usage patterns as Imelda Marcos had shoes (well, maybe not >that many), so any strategy that considers only two particular >pairs of shows is not going to fly. We need to provide enough >configurability to allow DBAs to make the vacuumer fit their situation. Now we have at least one different model, lets mix in other captures and situations. So it cannot be done with only YOUR data, I fully agree. But if you have sufficient data you can find the generalization of the model to make it work (resonable) in sufficient situations. Of course models need time to evolve, but so does the implementation currently at a slow rate. From do it yourself, to scripts, to the current autovacuum integration (which is good). From doing all tables sequentially to having some intelligence by update thresholds, to what will be next. I think you should better solve the problem is this ways, as models are relative easy to compare compared to arguments without analyzable/simulatible data. - Joris ---(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: [GENERAL] Array constructor requires one argument
Martijn van Oosterhout writes:
> On Tue, Jan 09, 2007 at 10:02:09AM -0500, Tom Lane wrote:
>> Define the data type of
>> SELECT ARRAY[];
> The same type as:
> SELECT NULL;
Hardly, because whatever type NULL has, it's not an array type.
> In fact, you could just make it an unknown
> literal with just two curly vrace and you'd be all set.
Nope. '{}' without any other decoration isn't an array at all.
You're confusing external representation with what the system
(thinks it) knows about the type of an expression.
As an example, if we did what I think you're proposing, this
would succeed:
SELECT ARRAY[]::text;
whereas it certainly ought not, because there is no cast from
any array type to a scalar text value.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Questions about horizontal partitioning
Chander Ganesan <[EMAIL PROTECTED]> writes: > In theory, if the table with 6 columns was the child of the table with > 200+ columns, and a PK-FK relationship existed, then the optimizer > wouldn't need to check for the existence of the rows, since the PK-FK > relationship would indicate that the rows did/do exist. No, that's still not right. With a LEFT JOIN you know that each row of the narrow table will produce at least one row in the join view. What you don't know is whether the row could produce more than one join row --- ie, is there more than one wide-table row that joins to it? To optimize away the join, the planner would have to find a unique constraint on the wide table's join column(s). This is certainly doable in principle, though I find it questionable whether the planner should spend cycles on every join query checking for something that won't be true in the vast majority of real-world queries. The main reason we have not considered it to date is that the correctness of the plan would then depend on a constraint that could get dropped --- but the plan would not fail when run, as indeed it wouldn't be touching that table at all. We really need some plan-invalidation infrastructure to force re-planning whenever a table's schema changes, and only then will it be safe for the planner to start depending on constraints for correctness- related decisions. (This is why constraint exclusion is off by default at the moment.) I've been wanting plan invalidation for a long time ... maybe it will finally happen in 8.3. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] TRIGGER BEFORE INSERT
Rafal Pietrak <[EMAIL PROTECTED]> writes: > 1. either the new value of "test_days.dnia" as already present in the > NEW row, is not visible to "UPDATE test_utarg" sub-statement of the same > transaction. But earlier versions of Postgres did allow for that > visibility. > 2. or the constrainets in earlier postgres were checked on trigger > transaction COMMIT, not along the way; so the constraint violation > didn't occure then. Current versions of PG check foreign keys at the end of each insert/update/delete statement, so your before-insert trigger is in fact erroneous: the referenced key does not yet exist in the target table. I think 7.2 did constraint checking only when the entire interactive command finished, but there were enough cases where that was wrong that we changed it. Consider declaring the foreign-key constraint as DEFERRED. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Operator performance question
Hi all, I need your help on a small performance problem. I have a table of which I have to do a bunch of counts of various conditions. The worst case scenario where I have to iterate over every record in the table performs just a little bit too slow (800ms). That particular query will be hit a lot (it will be on the index of our web app). PostgreSQL uses a sequential scan (it should IMO) - I think my bottleneck is in the operators on the various columns. My queries look like this: SELECT COUNT(NULLIF(max_persons BETWEEN 5 AND 8, false)) AS "persons 5-8", -- And other variations COUNT(NULLIF(country_id = 74, false)) AS "LOCATION_NETHERLANDS", -- Basically for every country in Europe COUNT(NULLIF(specifications & '0100', 0::bit(32))) AS "washing machine", -- And a bunch more of these; the bit mask is almost fully covered COUNT(*) AS all FROM table; The plan is: QUERY PLAN --- Aggregate (cost=7371.23..7371.55 rows=1 width=18) (actual time=803.374..803.376 rows=1 loops=1) -> Seq Scan on fewo_property_location (cost=0.00..828.84 rows=41538 width=18) (actual time=0.036..147.211 rows=41492 loops=1) Filter: ((location_id IS NOT NULL) AND (property_state_id = 3)) Total runtime: 804.398 ms (4 rows) The table definition is like: Column | Type | Modifiers ---+--+-- property_id | integer | not null property_state_id | integer | not null location_id | integer | min_persons | smallint | not null max_persons | smallint | not null specifications| bit(32) | default (0)::bit(32) country_id| integer | Indexes: "fewo_property_location_pkey" PRIMARY KEY, btree (property_id) "fewo_property_location_country_idx" btree (country_id) WHERE location_id IS NOT NULL "fewo_property_location_country_location_idx" btree (country_id, location_id) CLUSTER "fewo_property_location_location_online_idx" btree (location_id) WHERE location_id IS NOT NULL AND property_state_id = 3 "fewo_property_location_property_location_idx" btree (property_id, location_id) WHERE location_id IS NOT NULL AND property_state_id = 3 "fewo_property_location_specifications_idx" btree (specifications) Foreign-key constraints: "fewo_property_location_location_id_fkey" FOREIGN KEY (location_id) REFERENCES fewo_location(location_id) MATCH FULL "fewo_property_location_property_state_id_fkey" FOREIGN KEY (property_state_id) REFERENCES fewo_property_state(property_state_id) MATCH FULL My conclusion is that this query time is mostly limited to the somewhat complex COUNT expressions. Is there any way to do this more efficiently? For the record, if I constrain this query to specific countries it performs in about 80ms (10x as fast). The hardware is a dual Opteron64x2, 4G RAM and some kind of RAID setup (software, don't know what type) running in a Xen host - it's our development DB-server. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(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: [GENERAL] SELECT INTO using Views?
Thanks for the reply! That worked, but I'm running into one other issue that I'm having some trouble resolving... Problem: We want all values in the measurement view (and table once I copy it into there) to be shown in ints vs. floats/decimals. In my View (called 'measurement'), there is a calculated column, area_sq, that is defined as type float8. This column is calculated in the following manner: a.area * su.units_per_sqfoot::integer AS area_sq, where a.area is a float8 and su.units_per_sqfoot is a float8 that I'm casting to an INT. When I execute this, it is returning a float. If I cast the entire operation to an INT: (a.area * su.units_per_sqfoot::integer)::integer AS area_sq or by (a.area * su.units_per_sqfoot)::integer AS area_sq, I'm getting an 'ERROR: integer out of range' error returned when I run my SELECT statement: SELECT e.elementid, da.projectname, da.square_unit AS square_unit_sq, (a.area * su.units_per_sqfoot)::integer AS area_sq, e.slope AS slope_inches, sa.slopearea * cu.units_per_cufoot::integer AS slopearea_sq, da.linear_unit AS linear_unit_lin, (p.perimeter::integer * lu.units_per_foot::double precision)::integer AS perimeter_lin, da.cubic_unit AS cubic_unit_cu, e.height * lu.units_per_foot::integer AS height_lin, e.height::double precision * a.area * cu.units_per_cufoot::integer AS volume_cu, da.drawingid FROM ((SELECT perimeter.elementid, perimeter.perimeter FROM elementdata.perimeter UNION SELECT length.elementid, length.length AS perimeter FROM elementdata.length) UNION SELECT circumference.elementid, circumference.circumference AS perimeter FROM elementdata.circumference) p LEFT JOIN elementdata.area a USING (elementid)) LEFT JOIN element e USING (elementid)) LEFT JOIN elementdata.slopearea sa USING (elementid)) JOIN layer la USING (layerid)) JOIN drawing da USING (drawingid)) JOIN globaldata.linear_units lu USING (linear_unit)) JOIN globaldata.square_units su USING (square_unit)) JOIN globaldata.cubic_units cu USING (cubic_unit)); All of the casts in the SELECT statement appear to be working except for the one for the area_sq and slopearea_sq and both of these columns are defined as 'float8', whereas the other ones are either defined as numeric or int4. Thoughts and/or ideas without having to redo other tables in the database? Thanks for your replies and assistance, it is all greatly appreciated! -Jeanna -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Merlin Moncure Sent: Monday, January 08, 2007 6:28 PM To: Jeanna Geier Cc: pgsql-general Subject: Re: [GENERAL] SELECT INTO using Views? On 1/9/07, Jeanna Geier <[EMAIL PROTECTED]> wrote: > Hello List! > > I have a question regarding SELECT INTO... > > Can it be used with Views? I have a View that is populated (~35,000 rows) > that I want to create a Table from the data in it > > So, would I be able to copy the data from the View to the Table using the > SELECT INTO command? Administrator=# create temp view v as select 'postgresql r0x0r'::text; CREATE VIEW Administrator=# create temp table t as select * from v; SELECT merlin ---(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 6: explain analyze is your friend
Re: [GENERAL] Operator performance question
Shouldn't these be using HAVING? SELECT COUNT(max_persons) ... GROUP BY NULL HAVING max_persons >= 5 AND max_persons <= 8; -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alban Hertroys Sent: Tuesday, January 09, 2007 11:07 AM To: Postgres General Subject: [GENERAL] Operator performance question Hi all, I need your help on a small performance problem. I have a table of which I have to do a bunch of counts of various conditions. The worst case scenario where I have to iterate over every record in the table performs just a little bit too slow (800ms). That particular query will be hit a lot (it will be on the index of our web app). PostgreSQL uses a sequential scan (it should IMO) - I think my bottleneck is in the operators on the various columns. My queries look like this: SELECT COUNT(NULLIF(max_persons BETWEEN 5 AND 8, false)) AS "persons 5-8", -- And other variations COUNT(NULLIF(country_id = 74, false)) AS "LOCATION_NETHERLANDS", -- Basically for every country in Europe COUNT(NULLIF(specifications & '0100', 0::bit(32))) AS "washing machine", -- And a bunch more of these; the bit mask is almost fully covered COUNT(*) AS all FROM table; The plan is: QUERY PLAN --- Aggregate (cost=7371.23..7371.55 rows=1 width=18) (actual time=803.374..803.376 rows=1 loops=1) -> Seq Scan on fewo_property_location (cost=0.00..828.84 rows=41538 width=18) (actual time=0.036..147.211 rows=41492 loops=1) Filter: ((location_id IS NOT NULL) AND (property_state_id = 3)) Total runtime: 804.398 ms (4 rows) The table definition is like: Column | Type | Modifiers ---+--+-- property_id | integer | not null property_state_id | integer | not null location_id | integer | min_persons | smallint | not null max_persons | smallint | not null specifications| bit(32) | default (0)::bit(32) country_id| integer | Indexes: "fewo_property_location_pkey" PRIMARY KEY, btree (property_id) "fewo_property_location_country_idx" btree (country_id) WHERE location_id IS NOT NULL "fewo_property_location_country_location_idx" btree (country_id, location_id) CLUSTER "fewo_property_location_location_online_idx" btree (location_id) WHERE location_id IS NOT NULL AND property_state_id = 3 "fewo_property_location_property_location_idx" btree (property_id, location_id) WHERE location_id IS NOT NULL AND property_state_id = 3 "fewo_property_location_specifications_idx" btree (specifications) Foreign-key constraints: "fewo_property_location_location_id_fkey" FOREIGN KEY (location_id) REFERENCES fewo_location(location_id) MATCH FULL "fewo_property_location_property_state_id_fkey" FOREIGN KEY (property_state_id) REFERENCES fewo_property_state(property_state_id) MATCH FULL My conclusion is that this query time is mostly limited to the somewhat complex COUNT expressions. Is there any way to do this more efficiently? For the record, if I constrain this query to specific countries it performs in about 80ms (10x as fast). The hardware is a dual Opteron64x2, 4G RAM and some kind of RAID setup (software, don't know what type) running in a Xen host - it's our development DB-server. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(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 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Operator performance question
Alban Hertroys <[EMAIL PROTECTED]> writes: > My conclusion is that this query time is mostly limited to the somewhat > complex COUNT expressions. Is there any way to do this more efficiently? Offhand I would bet on the bitstring-AND operations being the bottleneck; you could test this by comparing the speed of queries that are doing different mixes of the same number of COUNT()s. If you're happy with a fixed-width 32-bit field, consider using an integer field and integer & operations, instead of bitstring. Bitstring is a pass-by-reference type and so inherently a lot less efficient than an integer. Another suggestion is to replace count(nullif(boolean_expr, false)) with sum((boolean_expr)::int) I think this would be a marginal speed win at best (basically replacing a Const and a NullIf node with a Cast node), but it just seems to me to be more natural ... it took me a bit to figure out what your query was trying to accomplish. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] SELECT INTO using Views?
"Jeanna Geier" <[EMAIL PROTECTED]> writes: > If I cast the entire operation to an INT: ... > I'm getting an 'ERROR: integer out of range' error returned when I run my > SELECT statement: Perhaps use floor() instead? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Autovacuum Improvements
Csaba Nagy wrote: Other thing, how will the vacuum queue be populated ? Or the "queue" here means nothing, all workers will always go through all tables to pick one based on their own criteria ? My concern here is that the current way of checking 1 DB per minute is not going to work with category #2 tables, they really have to be vacuumed continuously sometimes. Without getting into all the details, the autovacuum naptime is a GUC variable right now, so it can be much more frequent than the current default which is 60 seconds. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Autovacuum Improvements
On Tue, 2007-01-09 at 17:31, Matthew T. O'Connor wrote: > Without getting into all the details, the autovacuum naptime is a GUC > variable right now, so it can be much more frequent than the current > default which is 60 seconds. Hmm, for some reason I thought the granularity is minutes, but it is indeed in seconds... one more thing learned. Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Autovacuum Improvements
On Tue, 2007-01-09 at 17:36, Csaba Nagy wrote: > On Tue, 2007-01-09 at 17:31, Matthew T. O'Connor wrote: > > Without getting into all the details, the autovacuum naptime is a GUC > > variable right now, so it can be much more frequent than the current > > default which is 60 seconds. > > Hmm, for some reason I thought the granularity is minutes, but it is > indeed in seconds... one more thing learned. OK, so after checking my config, it is still not optimal because it refers to all the data bases in the cluster. I have setups where I have multiple data bases in the same cluster, with various degrees of activity... some of them should be checked continuously, some rarely... so now if I let the default 60 seconds, each data base will be checked in db_count * (60 + vacuum time) seconds. This is not optimal... some of the DBs have way much activity than others. Those I would like to be checked say each 10 seconds, the rest each 5 minutes... Cheers, Csaba. ---(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: [GENERAL] SELECT INTO using Views?
On Tue, Jan 09, 2007 at 10:10:46 -0600, Jeanna Geier <[EMAIL PROTECTED]> wrote: > > If I cast the entire operation to an INT: > (a.area * su.units_per_sqfoot::integer)::integer AS area_sq > or by > (a.area * su.units_per_sqfoot)::integer AS area_sq, > I'm getting an 'ERROR: integer out of range' error returned when I run my > SELECT statement: This suggests that the values are too large to be represented in int4. Can you switch to using int8? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Questions about horizontal partitioning
On Tue, Jan 09, 2007 at 08:28:29 -0500, Chander Ganesan <[EMAIL PROTECTED]> wrote: > It would. A query that uses an inner join implies that a matching entry > must exist in both tables - so the join must occur, otherwise you could > be returning rows that don't satisfy the join condition. While this might not be worth doing, a foreign key constraint could be used to establish the existance of the matching row, so that you wouldn't actually need to look in the other table to verify that if you didn't need any of the other columns in the other table. There will be some tricky cases for this, such as when there are deferred constraints or when updating the foreign key field. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] TRIGGER BEFORE INSERT
On Tue, 2007-01-09 at 10:44 -0500, Tom Lane wrote:
> Rafal Pietrak <[EMAIL PROTECTED]> writes:
> > 1. either the new value of "test_days.dnia" as already present in the
> > NEW row, is not visible to "UPDATE test_utarg" sub-statement of the same
> > transaction. But earlier versions of Postgres did allow for that
> > visibility.
> > 2. or the constrainets in earlier postgres were checked on trigger
> > transaction COMMIT, not along the way; so the constraint violation
> > didn't occure then.
>
> Current versions of PG check foreign keys at the end of each
> insert/update/delete statement, so your before-insert trigger is in fact
> erroneous: the referenced key does not yet exist in the target table.
> I think 7.2 did constraint checking only when the entire interactive
> command finished, but there were enough cases where that was wrong
> that we changed it.
>
> Consider declaring the foreign-key constraint as DEFERRED.
No luck here.
I've changed the trigger function to have triggers deferred, like the
following:
database=# CREATE OR REPLACE FUNCTION prado() RETURNS trigger AS $$
DECLARE wydano INTEGER; BEGIN SET CONSTRAINTS ALL DEFERRED ; UPDATE
test_utarg SET dnia=new.id WHERE tm BETWEEN new.dnia AND new.dnia
+'1day'::interval; GET DIAGNOSTICS wydano := ROW_COUNT; new.total :=
wydano; RETURN new; END; $$ LANGUAGE plpgsql;
and the results are still the same:
database=# INSERT INTO test_days (dnia) VALUES ('2007-01-06');
ERROR: insert or update on table "test_utarg" violates foreign key
constraint "test_utarg_dnia_fkey"
DETAIL: Key (dnia)=(3) is not present in table "test_days".
CONTEXT: SQL statement "UPDATE test_utarg SET dnia= $1 WHERE tm
BETWEEN $2 AND $3 +'1day'::interval"
PL/pgSQL function "prado" line 1 at SQL statement
But I've never before used a deferred constraints - so may be I haven't
set it up correctly, in the above definition. Have I?
But actually, I've found a workaround: I've encapsulated the above
functionality inside of a function, which:
1. does an INSERT
2. subsequently does a SELECT of what i've just inserted (currently I'm
stuck with postgres v8.1.4 - so I cannot use INSERT ... RETURNING).
3. then I UPDATE the logtable
4. then I UPDATE the record INSERTED in step (1).
Originally, I had this functionality in a single "TRIGGER BEFORE"
function (OK, it fired UPDATE within - but I had the 'fresh' ROW of data
from step (1) all along with me, inside of that trigger function - no
need to SELECT/UPDATE it in separate statements).
So I get a performance panelty against my original schema.
Is there a way to optimise?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is there a tracking trace tool like the "SQL Analizer" in MS sqlserver.?
Not exactly. SQL Analyzer also includes live monitoring of whatever queries are coming into the database. You can achieve something similar by enabling query logging in the settings. On 1/8/07, Ian Harding <[EMAIL PROTECTED]> wrote: There is no GUI tool that I know of, but there is EXPLAIN which gives the same information. - Ian On 1/8/07, guillermo arias <[EMAIL PROTECTED]> wrote: > is there a tracking trace tool in postgre? like the "SQL Analizer" in MS > sqlserver. > > I have downloaded the PGAdmin III and i have not found any tool like this. > > Thanks > > > > Get your FREE, LinuxWaves.com Email Now! --> http://www.LinuxWaves.com > Join Linux Discussions! --> http://Community.LinuxWaves.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
[GENERAL] Postgres Replication
Hi everybody, I have two computers with a Postgres Database each. I want one of them to be the replica of the other one; let's say I want a Master to Master replication in order to use either one (but only one at a time) as the main database: in case of failure, switch. The ideal synchronization way would be Synchronous. However, these two computers are going to be next to each other, so the asynchronous synchronization would be fast enough (I don't really know. Can you tell so?) for the case synchronous sync is not available. What I have found so far is Daffodil and Slony-I. Daffodil's name doesn't even appear in Postgresql.org, which is not the case for Slony-I. So there's a big point in favor to Slony-I. Has anybody researched on this that can point me in the right direction? Thanks a lot, Daniel Crespo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is there a tracking trace tool like the "SQL Analizer" in MS sqlserver.?
Jeffrey, Not exactly. SQL Analyzer also includes live monitoring of whatever queries are coming into the database. You can achieve something similar by enabling query logging in the settings. that is within Tools / View Server Status of pgAdmin III Of course, Query Command String has to be enabled on the server; and the security implications of that setting have to be considered before :) best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords. ---(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: [GENERAL] Autovacuum Improvements
Csaba Nagy wrote: On Tue, 2007-01-09 at 17:36, Csaba Nagy wrote: On Tue, 2007-01-09 at 17:31, Matthew T. O'Connor wrote: Without getting into all the details, the autovacuum naptime is a GUC variable right now, so it can be much more frequent than the current default which is 60 seconds. Hmm, for some reason I thought the granularity is minutes, but it is indeed in seconds... one more thing learned. OK, so after checking my config, it is still not optimal because it refers to all the data bases in the cluster. I have setups where I have multiple data bases in the same cluster, with various degrees of activity... some of them should be checked continuously, some rarely... so now if I let the default 60 seconds, each data base will be checked in db_count * (60 + vacuum time) seconds. This is not optimal... some of the DBs have way much activity than others. Those I would like to be checked say each 10 seconds, the rest each 5 minutes... Agreed, this is the point of this whole thread that there are lots of setups where autovacuum could do better. My point was only that as we move forward with these multiple queue / multiple worker process setups etc, that we already have some infrastructure to make things go faster. ---(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: [GENERAL] Postgres Replication
On Tue, 2007-01-09 at 07:36, dcrespo wrote: > Hi everybody, > > I have two computers with a Postgres Database each. I want one of them > to be the replica of the other one; let's say I want a Master to Master > replication in order to use either one (but only one at a time) as the > main database: in case of failure, switch. The ideal synchronization > way would be Synchronous. However, these two computers are going to be > next to each other, so the asynchronous synchronization would be fast > enough (I don't really know. Can you tell so?) for the case synchronous > sync is not available. > > What I have found so far is Daffodil and Slony-I. Daffodil's name > doesn't even appear in Postgresql.org, which is not the case for > Slony-I. So there's a big point in favor to Slony-I. > > Has anybody researched on this that can point me in the right > direction? Possibly. Depending on your biz requirements, you may be better served with a hot failover setup, where both machines can mount the same storage array and if the primary server fails, the secondary server mounts its partitions and starts up postgresql, and takes over its IPs etc... There are hazards with this kind of setup, because if two postmasters run on the same data store it will corrupt it beyond repair, etc... slony works well for what you're talking about, but you'll need to come up with a switchover plan that meets you needs. You could use possibly use pgpool as long as its caveats aren't a show stopper (can't insert with random, individual inserts with things like now() might be a little different, insert order might not be the same on both machines, etc... I haven't used daffodil, but have heard of it. There's also c-jdbc and a few others. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is there a tracking trace tool like the "SQL Analizer"
Harald Armin Massa wrote: Jeffrey, Not exactly. SQL Analyzer also includes live monitoring of whatever queries are coming into the database. You can achieve something similar by enabling query logging in the settings. that is within Tools / View Server Status of pgAdmin III Of course, Query Command String has to be enabled on the server; and the security implications of that setting have to be considered before :) best wishes, Harald Hi, I may be wrong but: Enabling query logging (in the options dialog) will only log the SQL pgAdmin III sends to the server, it will not capture the query for a selected process in the server status dialog. If you want something like the trace in MS SQL server Lightning Admin has something similar: http://www.amsoftwaredesign.com/onlinehelp/pgla/server_status.htm You can select the process ID you want, then check the Capture selected rows query option and all the queries for that process that become visible will be logged to a memo control, you can then copy the captured queries to where ever you like. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(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: [GENERAL] is there a tracking trace tool like the "SQL Analizer"
> Hi, > I may be wrong but: > Enabling query logging (in the options dialog) will only log the SQL > pgAdmin III sends to the server, it will not capture the query for a > selected process > in the server status dialog. > If you want something like the trace in MS SQL server Lightning Admin > has something similar: > http://www.amsoftwaredesign.com/onlinehelp/pgla/server_status.htm > You can select the process ID you want, then check the Capture selected > rows query option and all the queries for that process that become > visible will be > logged to a memo control, you can then copy the captured queries to > where ever you like. Out of curiosity, how does that work? I mean, how does it pick up when a new query has been issued? Do you use some server-proc to tail the logfile or such? //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Array constructor requires one argument
Tom Lane wrote: Martijn van Oosterhout writes: On Tue, Jan 09, 2007 at 10:02:09AM -0500, Tom Lane wrote: Define the data type of SELECT ARRAY[]; The same type as: SELECT NULL; Hardly, because whatever type NULL has, it's not an array type. Here's a link to the initial discussion on this topic: http://archives.postgresql.org/pgsql-hackers/2003-06/msg01195.php Unless we can convince ourselves that array-of-UNKNOWN is not dangerous, I don't think this is easily solved. Joe ---(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
[GENERAL] ERROR: invalid memory alloc request size, and others
We recently upgraded from 8.1.4 to 8.2.0 on Fedora Core 6, and are now seeing a few rather ominous-looking messages. The problem started with this one, during an update involving a rather complex view: ERROR: invalid memory alloc request size 1174405120 Then, when attempting to re-run the same update statement: PANIC: cannot abort transaction 8682091, it was already committed and then again: PANIC: right sibling's left-link doesn't match I ran reindex on the tables in question, which fixed the problem in the short term and allowed the update to complete, but then I got the memory allocation error and these overnight: ERROR: invalid page header in block 3362 of relation "index_clin_dal_staff_id" ERROR: invalid page header in block 2325 of relation "index_clin_dal_batch" I will gladly provide additional information to help track down and hopefully solve this problem, but at this point I'm not sure what else to include. Any help would be much appreciated. Thanks, Jonathan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] index creation on 7.3
If I create an index on a table that needs to be vacuumed in 7.3, will the dead rows get indexed as well? ---(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: [GENERAL] is there a tracking trace tool like the "SQL Analizer
> --- Original Message --- > From: Tony Caduto <[EMAIL PROTECTED]> > To: Harald Armin Massa <[EMAIL PROTECTED]> > Sent: 1/9/07, 7:15:52 PM > Subject: Re: [GENERAL] is there a tracking trace tool like the "SQL Analizer" > > Harald Armin Massa wrote: > > Jeffrey, > > > >> Not exactly. SQL Analyzer also includes live monitoring of whatever > >> queries > >> are coming into the database. You can achieve something similar by > >> enabling > >> query logging in the settings. > > > > that is within Tools / View Server Status of pgAdmin III > > > > Of course, Query Command String has to be enabled on the server; and > > the security implications of that setting have to be considered before > > :) > > > > best wishes, > > > > Harald > Hi, > I may be wrong but: > Enabling query logging (in the options dialog) will only log the SQL > pgAdmin III sends to the server, it will not capture the query for a > selected process > in the server status dialog. That's not what Harald was referring to. The server status dialog monitors the entire server via pg_stat*. /D ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ERROR: invalid memory alloc request size, and others
On Tue, 2007-01-09 at 13:38, Jonathan Hedstrom wrote: > We recently upgraded from 8.1.4 to 8.2.0 on Fedora Core 6, and are now > seeing a few rather ominous-looking messages. > > The problem started with this one, during an update involving a rather > complex view: > > ERROR: invalid memory alloc request size 1174405120 > > Then, when attempting to re-run the same update statement: > > PANIC: cannot abort transaction 8682091, it was already committed > > and then again: > > PANIC: right sibling's left-link doesn't match > > > I ran reindex on the tables in question, which fixed the problem in the > short term and allowed the update to complete, but then I got the memory > allocation error and these overnight: > > ERROR: invalid page header in block 3362 of relation > "index_clin_dal_staff_id" > ERROR: invalid page header in block 2325 of relation "index_clin_dal_batch" > > I will gladly provide additional information to help track down and > hopefully solve this problem, but at this point I'm not sure what else > to include. First step, update to 8.2.1, it came out yesterday, and there were a few bugs that got stomped. Don't know if they are related to your problem, but having the latest version is usually a "good thing". Also, schedule some maintenance window for your server to run memtest86 and possibly something to check for bad blocks on your drives. Often errors like the invalid memory alloc request size you're seeing, and the link doesn't match one are caused by bad hardware. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] is there a tracking trace tool like the "SQL Analizer
Dave Page wrote: That's not what Harald was referring to. The server status dialog monitors the entire server via pg_stat*. /D Hi Dave, It was not just a response to Harald's comment, but also the one right above his :-) "You can achieve something similar by enabling query logging in the settings." Which Harald then continued with this: "that is within Tools / View Server Status of pgAdmin III" So someone reading that may have thought that you could turn on query logging in Tools/ Server Status.(which as far as I can tell you can't) So yes, you can turn on SQL logging in the main options dialog, but that will not capture the query values in the server status dialog which what Harald was "seeming" to say. I guess everyone can read into something differently :-) Later, --- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Array constructor requires one argument
Joe Conway <[EMAIL PROTECTED]> writes: > Unless we can convince ourselves that array-of-UNKNOWN is not dangerous, > I don't think this is easily solved. One possibility that might handle Martijn's problem is to allow the construct ARRAY[]::type[] that is, you can have an empty ARRAY construct only if you immediately cast it to a specific array type. The application typically knows what type the data is anyway, and if it just always plasters on the cast then the syntax would work regardless of whether there are any elements or not. 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: [GENERAL] ERROR: invalid memory alloc request size, and others
Scott Marlowe wrote: > On Tue, 2007-01-09 at 13:38, Jonathan Hedstrom wrote: > >> We recently upgraded from 8.1.4 to 8.2.0 on Fedora Core 6, and are now >> seeing a few rather ominous-looking messages. >> >> The problem started with this one, during an update involving a rather >> complex view: >> >> ERROR: invalid memory alloc request size 1174405120 >> >> Then, when attempting to re-run the same update statement: >> >> PANIC: cannot abort transaction 8682091, it was already committed >> >> and then again: >> >> PANIC: right sibling's left-link doesn't match >> >> >> I ran reindex on the tables in question, which fixed the problem in the >> short term and allowed the update to complete, but then I got the memory >> allocation error and these overnight: >> >> ERROR: invalid page header in block 3362 of relation >> "index_clin_dal_staff_id" >> ERROR: invalid page header in block 2325 of relation "index_clin_dal_batch" >> >> I will gladly provide additional information to help track down and >> hopefully solve this problem, but at this point I'm not sure what else >> to include. >> > > First step, update to 8.2.1, it came out yesterday, and there were a few > bugs that got stomped. Don't know if they are related to your problem, > but having the latest version is usually a "good thing". > > I noticed that 8.2.1 had been released shortly after I sent off my initial email. I've upgraded and hopefully that will take care of it. > Also, schedule some maintenance window for your server to run memtest86 > and possibly something to check for bad blocks on your drives. Often > errors like the invalid memory alloc request size you're seeing, and the > link doesn't match one are caused by bad hardware. I'll try to run these tests soon, but this is a production server, so scheduling downtime takes a bit of planning. I should also mention that this is the exact same server we've been running 8.1.4 on for about 6 months without a problem (doing the same nightly update causing the problem etc), so I'm a bit skeptical about having suddenly developed hardware issues. Thanks, Jonathan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Autovacuum Improvements
Joris Dobbelsteen wrote: > Now we have at least one different model, lets mix in other captures and > situations. So it cannot be done with only YOUR data, I fully agree. > But if you have sufficient data you can find the generalization of the > model to make it work (resonable) in sufficient situations. > Of course models need time to evolve, but so does the implementation > currently at a slow rate. From do it yourself, to scripts, to the > current autovacuum integration (which is good). From doing all tables > sequentially to having some intelligence by update thresholds, to what > will be next. > > I think you should better solve the problem is this ways, as models are > relative easy to compare compared to arguments without > analyzable/simulatible data. To be frank, I'm not sure I understand what you're saying here. I'm sure more analysis is good; that's easy to agree with. However, I don't want to be trapped in a design that's too hard to implement, or too hard for DBAs to manage. There have been proposals to add these knobs: - maximum number of simultaneous processes (and make it more than 1) - times of day on which to change parameters (i.e. disable vacuum altogether, or make it more agressive or less) Do you have further ideas? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] PGPASS.CONF ¿is there a way to protect it?
Hello, i am using PostgreSQL 8.2 in a Windows XP pc. I know that this file PGPASS.CONF stores the users with password, but because this is a plain txt file, it is easy for "curious" people to read the contents.By other way, if i do not write the password there, my users will have to write it when trying to connect to the PostgreSQL, and it is not desirable when i have an application in Windows.I have to say that my WinXP pc has the C: partition with FAT32, and the J: partition is NTFS. The PostgreSQL engine and database are installed in J:My question is ¿how can i protect PGPASS.CONF? or if you have another idea for protecting the passwords i will be very thankfull to read it.byeGuillermo Arias, Lima- Perú Get your FREE, LinuxWaves.com Email Now! --> http://www.LinuxWaves.comJoin Linux Discussions! --> http://Community.LinuxWaves.com
Re: [GENERAL] ERROR: invalid memory alloc request size, and others
Scott Marlowe <[EMAIL PROTECTED]> writes: > On Tue, 2007-01-09 at 13:38, Jonathan Hedstrom wrote: >> We recently upgraded from 8.1.4 to 8.2.0 on Fedora Core 6, and are now >> seeing a few rather ominous-looking messages. > First step, update to 8.2.1, it came out yesterday, and there were a few > bugs that got stomped. Definitely good advice, although none of these messages look related to the known fixes. > Also, schedule some maintenance window for your server to run memtest86 > and possibly something to check for bad blocks on your drives. +1 ... I have not seen any instance of "invalid page header" that could be traced to a Postgres bug. The cases I've been able to study all seemed to involve either flaky hardware or kernel-level bugs (such as dumping a fragment of some unrelated file into a Postgres table :-() regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PGPASS.CONF ¿is there a way to p
guillermo arias wrote: > Hello, i am using PostgreSQL 8.2 in a Windows XP pc. I know that this > file PGPASS.CONF stores the users with password, but because this is a > plain txt file, it is easy for "curious" people to read the contents. > By other way, if i do not write the password there, my users will have > to write it when trying to connect to the PostgreSQL, and it is not > desirable when i have an application in Windows. > I have to say that my WinXP pc has the C: partition with FAT32, and the > J: partition is NTFS. The PostgreSQL engine and database are installed in J: > > My question is ¿how can i protect PGPASS.CONF? or if you have another > idea for protecting the passwords i will be very thankfull to read it. Use NTFS file permissions. If your XP is installed on C:, and you have FAT32 on C:, you basically have zero security on your system anyway, so there is not much point in protecting this one file - it'd be trivial to get that and other data anyway. Now, if XP is installed on != C:, you can redirect the user profile directories to the NTFS drive and protect those (check google for help on how) - but it really makes no sense if XP is on a FAT32 drive. If so, your only real solution is to convert to NTFS and make sure you get the proper security set on the files. //Magnus ---(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: [GENERAL] ERROR: invalid memory alloc request size, and others
Tom Lane wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > >> Also, schedule some maintenance window for your server to run memtest86 >> and possibly something to check for bad blocks on your drives. >> > > +1 ... I have not seen any instance of "invalid page header" that could > be traced to a Postgres bug. The cases I've been able to study all > seemed to involve either flaky hardware or kernel-level bugs (such as > dumping a fragment of some unrelated file into a Postgres table :-() > > regards, tom lane > Since it sounds like this is either a hardware or a kernel issue, we're wondering if our downtime would be better spent rebooting to the standard FC6 kernel, or trying some of the aforementioned hardware tests... We are running a xen kernel: 2.6.18-1.2798.fc6xen and getting these kernel errors in our logs: Jan 7 18:51:23 ws116 kernel: SKB BUG: Invalid truesize (4012) len=16384, sizeof(sk_buff)=172 Jan 7 18:51:23 ws116 kernel: SKB BUG: Invalid truesize (4012) len=16384, sizeof(sk_buff)=172 Jan 9 08:52:12 ws116 kernel: SKB BUG: Invalid truesize (4012) len=16384, sizeof(sk_buff)=172 Jan 9 13:07:35 ws116 kernel: SKB BUG: Invalid truesize (4012) len=16384, sizeof(sk_buff)=172 (The memory alloc error first occured early in the morning on the 8th). Thanks, Jonathan ---(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: [GENERAL] Array constructor requires one argument
On Tue, Jan 09, 2007 at 04:09:21PM -0500, Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: > > Unless we can convince ourselves that array-of-UNKNOWN is not dangerous, > > I don't think this is easily solved. > > One possibility that might handle Martijn's problem is to allow the > construct > > ARRAY[]::type[] > > that is, you can have an empty ARRAY construct only if you immediately > cast it to a specific array type. The application typically knows what > type the data is anyway, and if it just always plasters on the cast then > the syntax would work regardless of whether there are any elements or not. +1 for doing it this way :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Postgres Replication
If you only want to use one database at a time you might look into using DRBD. It's a linux block-level package that is like raid-1 over the network. On Tue, 9 Jan 2007, dcrespo wrote: Hi everybody, I have two computers with a Postgres Database each. I want one of them to be the replica of the other one; let's say I want a Master to Master replication in order to use either one (but only one at a time) as the main database: in case of failure, switch. The ideal synchronization way would be Synchronous. However, these two computers are going to be next to each other, so the asynchronous synchronization would be fast enough (I don't really know. Can you tell so?) for the case synchronous sync is not available. What I have found so far is Daffodil and Slony-I. Daffodil's name doesn't even appear in Postgresql.org, which is not the case for Slony-I. So there's a big point in favor to Slony-I. Has anybody researched on this that can point me in the right direction? Thanks a lot, Daniel Crespo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie Constraint ?
On Mon, Jan 08, 2007 at 14:55:29 -0600, Jeanna Geier <[EMAIL PROTECTED]> wrote: > > Not quite sure what the: CONSTRAINT "Relationship182" is exactly... can > anyone help me with this one? Haven't seen this one yet... It is the name of that particular constraint. You would use that if you were going to delete the constraint. If it hadn't been explicitly named, it would have gotten a default name. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ERROR: invalid memory alloc request size, and others
Jonathan Hedstrom <[EMAIL PROTECTED]> writes: > We are running a xen kernel: 2.6.18-1.2798.fc6xen When did you start doing that ... any relation to the time when the problems started? I've heard some unkind remarks about the stability of Xen, though I have no direct knowledge about it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] COPY FROM and sequences
On Mon, Jan 08, 2007 at 20:20:42 -0500, Matthew Terenzio <[EMAIL PROTECTED]> wrote: > Is it true that you can't use COPY FROM to fill a table with a SERIAL > type column? > > Or rather, how does one approach that situation most effectively? In older versions of postgres you couldn't, in recent versions, you can provide an explicit column list to COPY and the other columns will get default values. You can't do this row by row though; there is no equivalent to DEFAULT as used in INSERT statements for COPY. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ERROR: invalid memory alloc request size, and others
Jonathan Hedstrom wrote: Tom Lane wrote: Scott Marlowe <[EMAIL PROTECTED]> writes: Also, schedule some maintenance window for your server to run memtest86 and possibly something to check for bad blocks on your drives. +1 ... I have not seen any instance of "invalid page header" that could be traced to a Postgres bug. The cases I've been able to study all seemed to involve either flaky hardware or kernel-level bugs (such as dumping a fragment of some unrelated file into a Postgres table :-() regards, tom lane Since it sounds like this is either a hardware or a kernel issue, we're wondering if our downtime would be better spent rebooting to the standard FC6 kernel, or trying some of the aforementioned hardware tests... We are running a xen kernel: 2.6.18-1.2798.fc6xen This is the base Xen kernel from the FC 6 release. There have been 3 updates released since then (most recently 01-Jan). I see a number of Xen fixes in the changelog, and I know that the major factor in the slippage of the FC 6 release was getting Xen into the distro -- so I would definitely expect some Xen bugs in the initial cut from the release. Simplest advice I can think of: if you don't need Xen, go back to the stock (albeit most recent update) kernel. If you do need Xen, try the most recent update of the stock kernel anyway. If the problems persist, you've at least eliminated one variable. If they go away, you've got the culprit. Note that I don't use Xen, so I'm not completely up-to-date... Last I knew there were issues preventing Xen from being included in the upstream Linux kernel (vendors are patching it in individually), and that speaks volumes as to the "newness" of the technology. Andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Array constructor requires one argument
On Tue, Jan 09, 2007 at 04:09:21PM -0500, Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: > > Unless we can convince ourselves that array-of-UNKNOWN is not dangerous, > > I don't think this is easily solved. > > One possibility that might handle Martijn's problem is to allow the > construct > > ARRAY[]::type[] This was in fact the solution I came up with. It also helps with the confusion, because the error didn't state the actual problem. At least this could could say "empty array needs typecast". I don't think it would be too hard to fix, I'll look tomorrow. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] ERROR: invalid memory alloc request size, and others
Jonathan Hedstrom wrote: Scott Marlowe wrote: On Tue, 2007-01-09 at 13:38, Jonathan Hedstrom wrote: We recently upgraded from 8.1.4 to 8.2.0 on Fedora Core 6, and are now seeing a few rather ominous-looking messages. [ SNIP ] Also, schedule some maintenance window for your server to run memtest86 and possibly something to check for bad blocks on your drives. Often errors like the invalid memory alloc request size you're seeing, and the link doesn't match one are caused by bad hardware. I'll try to run these tests soon, but this is a production server, so scheduling downtime takes a bit of planning. I should also mention that this is the exact same server we've been running 8.1.4 on for about 6 months without a problem (doing the same nightly update causing the problem etc), so I'm a bit skeptical about having suddenly developed hardware issues. Not having experienced any of the above issues myself, the following are totally off-the-cuff: You indicated you are now running Fedora Core 6, and that your previous 8.1.4 configuration was running for ~6 months prior to upgrading. I assume that because it is a production server, the FC 6 upgrade was performed at the same time as the 8.2 upgrade. You indicated that you're running on the exact same hardware as before, so unless you opened the machine, relocated it, etc. as part of the upgrade, hardware issues would seem unlikely. FC 6 brings a number of changes to the table -- the biggest one being the 2.6.18 Linux kernel. The 2.6.18 kernel updates were released for FC 5 in the middle of October, but as you are talking about a production server, I would imagine you did not upgrade at that time. Have you applied all current FC 6 updates? I definitely recommend making sure you are current with all updates. A quick check shows that FC 6 has released almost as many package updates as since its release as FC 5 has since it was released (6 months more time). I know there were a number of issues that were queued up and released right after the FC 6 release, to avoid slipping the release date any further than it had already slipped. If you can't attribute the issues to any software problems, I would definitely start looking at the hardware, as Scott & Tom suggested. As unlikely as hardware issues might seem, that seems to be the most frequent time those little buggers manage to show their teeth :^) Hope this helps... Andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Questions about horizontal partitioning
On Tue, Jan 09, 2007 at 10:33:52 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > > No, that's still not right. With a LEFT JOIN you know that each row of > the narrow table will produce at least one row in the join view. What > you don't know is whether the row could produce more than one join row > --- ie, is there more than one wide-table row that joins to it? Thanks for pointing that out. I only thought of half of the problem. > To optimize away the join, the planner would have to find a unique > constraint on the wide table's join column(s). This is certainly doable > in principle, though I find it questionable whether the planner should > spend cycles on every join query checking for something that won't be > true in the vast majority of real-world queries. The main reason we In this case the test would only be applied when no columns were being used in a table being joined to. Since that is also an unusual case, if that case could be quickly checked for, then it might conceivably be worth doing the more expensive test for the proper not null foreign key relation and unique constraint. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Array constructor requires one argument
Martijn van Oosterhout writes: > On Tue, Jan 09, 2007 at 04:09:21PM -0500, Tom Lane wrote: >> One possibility that might handle Martijn's problem is to allow the >> construct >> ARRAY[]::type[] > I don't think it would be too hard to fix, I'll look tomorrow. It could be pretty ugly, because type assignment normally proceeds bottom-up :-(. What you might have to do is make the raw grammar representation of ARRAY[] work like A_Const does, ie, there's a slot to plug in a typecast. That's pretty much vestigial now for A_Const, if memory serves, but it'd be needful if ARRAY[] has to be able to "see" the typecast that would otherwise be above it in the parse tree. Come to think of it, there is an additional possible benefit to doing it this way, which is that if ARRAY[] knows a target array type then it can just coerce all the elements to that without bothering with trying to induce a common type. So you should be able to save a few cycles in the non-empty case, too. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Autovacuum Improvements
Alvaro Herrera wrote: To be frank, I'm not sure I understand what you're saying here. I'm sure more analysis is good; that's easy to agree with. However, I don't want to be trapped in a design that's too hard to implement, or too hard for DBAs to manage. +1 > There have been proposals to add these knobs: - maximum number of simultaneous processes (and make it more than 1) - times of day on which to change parameters (i.e. disable vacuum altogether, or make it more agressive or less) Of all the autovacuum improvement discussions that I've seen recently the one additional knob that sounds like a good idea to me is a way to specify "HOT" tables that get special attention from autovacuum. There has been a lot of conjecture as to a good implementation, and I don't have a position on that, but I do think it's a good idea. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Questions about horizontal partitioning
Bruno Wolff III <[EMAIL PROTECTED]> writes: > In this case the test would only be applied when no columns were being used > in a table being joined to. Since that is also an unusual case, if that case > could be quickly checked for, then it might conceivably be worth doing the > more expensive test for the proper not null foreign key relation and unique > constraint. Yeah. Right offhand I think that would be fairly expensive too :-(. The problem is that the query *DOES* have a reference to at least one column of the wide table, namely its join key. So you'd have to distinguish whether any references appear "above" the join. In the current planner structure I think that this information is computed, but not until it's far too late to be removing joins from the tree. 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: [GENERAL] index creation on 7.3
And a related question: should a vacuum also clean out indexes on 7.3? On Tue, 9 Jan 2007, Ben wrote: If I create an index on a table that needs to be vacuumed in 7.3, will the dead rows get indexed as well? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ERROR: invalid memory alloc request size, and others
Andrew Kroeger wrote: > This is the base Xen kernel from the FC 6 release. There have been 3 > updates released since then (most recently 01-Jan). I see a number of > Xen fixes in the changelog, and I know that the major factor in the > slippage of the FC 6 release was getting Xen into the distro -- so I > would definitely expect some Xen bugs in the initial cut from the > release. > We had downloaded the kernel updates, but after doing so, forgot to reboot in order to use the updated kernel... > Simplest advice I can think of: if you don't need Xen, go back to the > stock (albeit most recent update) kernel. If you do need Xen, try the > most recent update of the stock kernel anyway. If the problems > persist, you've at least eliminated one variable. If they go away, > you've got the culprit. > We downloaded the most recent stock FC6 kernel and rebooted to that. Hopefully this will take care of the issue. I reindexed the tables related to the failing update. Other than that, is there any cleanup work I should do relating to these errors? Thanks for all the quick responses. -Jonathan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] index creation on 7.3
On Tue, 2007-01-09 at 17:17, Ben wrote: > And a related question: should a vacuum also clean out indexes on 7.3? Should, might not. There definitely was a problem in 7.2 with indexes on ever increasing values where the indexes would grow on one side of the btree only. In 7.2 I had a table of about 80k in size with an 80 meg index at one point. a reindex will fix that problem. I skipped 7.3 and went to 7.4, where that was fixed. Not sure if 7.3 fixed that problem or not. Even with 7.4 and on, if an index becomes sparsely populated you can have rather bloated indexes. I.e. if you delete 99 out of every 100 entries in a table, and you can fit >100 entries in an index for a row's index, then the indexes will be 99% empty, but the space they take up can't be easily collapsed. This might or might not have gotten better with more recent versions. > > If I create an index on a table that needs to be vacuumed in 7.3, will the > > dead rows get indexed as well? Yes. I think. I'm pretty sure that's still true in 8.2. as well. The way indexes work is that the index points to the oldest version of a tuple in existence, said tuple which then points to the next newer version and so on. So, definitely for old versions of rows that still exist they would be "indexed" in a manner of speaking. Not sure about deleted rows. I'm guessing that the db engine can't tell if they're deleted or not because it can't be sure there's not an older transaction in progress than the deleted tuple easily. ---(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: [GENERAL] ERROR: invalid memory alloc request size, and others
Tom Lane wrote: > Jonathan Hedstrom <[EMAIL PROTECTED]> writes: > >> We are running a xen kernel: 2.6.18-1.2798.fc6xen >> > > When did you start doing that ... any relation to the time when the > problems started? > We started using the xen kernel on the 3rd, and there wasn't any indication of a problem until the 8th, so that means the update ran w/o problem on the 4th and 5th (it doesn't run over the weekend). We have decided to run the stock fc6 kernel for now. -Jonathan begin:vcard fn:Jonathan Hedstrom n:Hedstrom;Jonathan org:Downtown Emergency Service Center;Information Services email;internet:[EMAIL PROTECTED] title:Data Systems Administrator tel;work:(206) 464-1570 ext. 3014 version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] index creation on 7.3
Scott Marlowe <[EMAIL PROTECTED]> writes: > On Tue, 2007-01-09 at 17:17, Ben wrote: > If I create an index on a table that needs to be vacuumed in 7.3, will the > dead rows get indexed as well? > Yes. I think. I'm pretty sure that's still true in 8.2. as well. The > way indexes work is that the index points to the oldest version of a > tuple in existence, said tuple which then points to the next newer > version and so on. IIRC, CREATE INDEX will ignore tuples that are "definitely dead", ie, too old to be visible to any open transaction. It *will* (and must) index tuples that are recently dead but might still be visible to some open transaction. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL Connections?
Hello All, I am running PostgresSQL 8.2 on Redhat Linux 4. When I look for the processes that are using postgres using ps aux|more I got lots of Idle processes with servers own IP address. Can anybody please tell me why this is happening. Also there are multiple processes for my single client(same IP) of which maximum are idle. are these processes means connection or there is a single connection starting many processes. I am having max connection =200 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Advice needed on using postgres in commercial product
On Jan 8, 2007, at 2:11 PM, Ron Mayer wrote: If not postgres, what other database could possibly do this, if any? I really don't want to have to write our own RDBMS. :) Doesn't seem much like a database question. I'd say ask on some trusted computing (google Trusted Computing) list if you want a technical solution or a lawyer if you want a legal one. Valentina claims to be able to securely encrypt your data for the entire database or individual fields/records. http://www.paradigmasoft.com/en/technology/TECH_SECURITY John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
