Re: [GENERAL] Postgres Database size
I have been looking for such a function. Having Just upgraded to 8.2, this function is a very welcome addition to my arsenal of tools. Many thanks! - Naz. Reece Hart wrote: On Sun, 2007-03-18 at 00:40 +0530, Mageshwaran wrote: how to find the size of a particular database in postgres... The old way was to use du or similar. Recent versions (I believe =8.1, but check the release notes to be sure) provide several useful functions for this: pg_column_size pg_database_size pg_relation_size pg_size_pretty pg_tablespace_size pg_total_relation_size For example: [EMAIL PROTECTED] select datname,pg_size_pretty(pg_database_size(oid)) from pg_database ; datname | pg_size_pretty -+ postgres| 3905 kB csb | 113 GB template0 | 3840 kB csb-dev | 124 GB csb-dev-snapshot-2007-03-08 | 123 GB csb_02_02_2007 | 121 GB template1 | 3840 kB
Re: [GENERAL] planning issue
Jonathan Vanasco wrote: hoping someone may be able to offer advice:. SELECT * FROM table_a WHERE id != 10001 AND ( ( field_1 ilike '123' ) OR ( field_2 ilike 'abc' ) ) You seem to use that ilike expression merely as a case-insensitive equals. May as well use that in combination with indices on lower(field_[12]). It's probably faster than like or a regex match. -- 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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] issue with SELECT settval(..);
Christian Schröder wrote:
Alain Roger wrote:
insert into immense.statususer (statususer_id, statususer_type) values
(SELECT nextval( 'statususer_statususer_id_seq' ),'customer');
The correct syntax would be:
insert into immense.statususer (statususer_id, statususer_type) values
((SELECT nextval( 'statususer_statususer_id_seq' )),'customer');
Well, that original query was almost right, it just didn't need the
values statement and the parenthesis:
insert into immense.statususer (statususer_id, statususer_type)
SELECT nextval('statususer_statususer_id_seq'), 'customer';
But as mentioned; using nextval directly is better.
--
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 5: don't forget to increase your free space map settings
[GENERAL] Client/Server [max connections]
Hi list, Could someone tell me the experience of many connections to PostgreSQL ? I am my afraid if when PostgreSQL have many connections (i.e: 1,000) it could cost much for the solution. Could someone tell me if my afraid is justifiable ? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Client/Server [max connections]
It depends on the amount of memory you have... -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 3/19/07, Ezequias R. da Rocha [EMAIL PROTECTED] wrote: Hi list, Could someone tell me the experience of many connections to PostgreSQL ? I am my afraid if when PostgreSQL have many connections (i.e: 1,000) it could cost much for the solution. Could someone tell me if my afraid is justifiable ? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UPGRADATION TO 8.1
So what's he meant to do? Write a longer question just so the mandatory disclamer that his company attaches to his e-mail takes up a lower percentage of his e-mail? (or should he not ask the question at all?) Regards, Ben Btw, I personally find a payload/noise ratio of 1/6 atrocious, and not very kind regarding the mailing-list. ---(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] Stored Procedure / function and their result
Hi,
I would like to know if there is a better way how to retrieve result from a
stored procedure (function) than to use 'AS res(col1 varchar, col2
timestamp,..)'
for example, here is a stored procedure :
CREATE OR REPLACE FUNCTION SP_A_003(username VARCHAR)
RETURNS SETOF RECORD AS
$BODY$
DECLARE
myrec RECORD;
BEGIN
FOR myrec IN
select
users.user_name,
users.user_firstname,
accounts.account_login,
statususer.statususer_type
from accounts, users, statususer
where
accounts.account_login = $1
AND
accounts.account_id = users.user_account_id
AND
users.user_status_id = statususer.statususer_id
LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
...
here is how i call it :
select * from sp_a_003('my_user_name')
as result
(
name varchar,
firstname varchar,
userlogin varchar,
statustype varchar
);
to understand well, in my stored procedure i only select a part of each
table (so i build a composite record) therefore i understood that SETOF
RECORD AS was the best solution for that.
however the result call is catastrophic when stored procedure returns
several fields. when it is more than 2 fields i'm already angry to write :
as result
(
name varchar,
firstname varchar,
userlogin varchar,
statustype varchar,
);
I would like to avoid this as result (...), so is there a better solution
?
thanks a lot,
--
Alain
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
Re: [GENERAL] Stored Procedure / function and their result
On Mon, Mar 19, 2007 at 01:54:14PM +0100, Alain Roger wrote: Hi, I would like to know if there is a better way how to retrieve result from a stored procedure (function) than to use 'AS res(col1 varchar, col2 timestamp,..)' Sure, create a type with the relevent field name and use that in you function declaration: CREATE TYPE mytype AS S res(col1 varchar, col2 timestamp,..); for example, here is a stored procedure : CREATE OR REPLACE FUNCTION SP_A_003(username VARCHAR) RETURNS SETOF RECORD AS And change that to: RETURNS SETOF mytype AS And you're done. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] UPGRADATION TO 8.1
Ben Trewern wrote: So what's he meant to do? Write a longer question just so the mandatory disclamer that his company attaches to his e-mail takes up a lower percentage of his e-mail? (or should he not ask the question at all?) Perhaps the OP's posit should have been sent to pgsql-admin@postgresql.org instead? :) -- Walter ICBM coordinates: Latitude: 35.6 Longitude: -81 ---(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] Design / Implementation problem
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Naz Gassiep Sent: zondag 18 maart 2007 14:45 To: Naz Gassiep Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Design / Implementation problem Here it is again with more sensible wrapping: *** The Scenario *** We are running a customer loyalty program whereby customers earn points for purchasing products. Each product has a value of points that are earned by purchasing it, and a value of points required to redeem it. In order to prevent customers from stockpiling points, we want to place an expiry date on points so that unused points expire and are lost if they are not redeemed within a certain period of time. This will be calculated on a FIFO basis, I.e., the oldest points will expire first. We will assume the expiry period is 12 months. *** The Problem *** Ascertaining which points to expire is fairly conceptually simple. At any given point in time, the points expired is simply the balance on hand at the start of the period, less redemptions in that period. If the redemptions is less than the balance at open, not all points that were available on that date were used, and the difference is the expiry. This can be done periodically, say, at the start of every month. However there are a few problems with doing it periodically 1. The runs are likely to be too large to be manageable. A DB with tens of thousands of customers and many hundreds of thousands or even millions of sales in the records tables will require several queries and some application calculation to compute. If it takes 2 seconds to compute each balance of a 20,000 strong customer base, that's over 11 hours of heavy lifting in the DB, which will likely result in severely degraded performance during those hours. This problem can only get worse as time goes on, and hardware upgrade requirements just to accommodate a 12 hour window once a month is the sign of an app not designed to scale well. 2. Calculating the balance on the fly would be more effective, as it is unlikley that many customers will check their balance on a regular basis. It is likely that a small fraction of customers will check their balance in a given month, meaning that calculating it on the fly would both spread the load over time as well as reduce the total load, even if on the fly calculation results in significantly higher per-customer calculation time. 3. The app is a web app, and it would be preferable to contain business logic within the database itself or the current app codebase. Spreading application logic into an external mechanism such as cron or an external daemon would be undesirable unless there was no other way. *** A Possible Solution *** [snip] *** The Question *** Is there a way to design the DB schema as well as the query in such a manner that calculating the point balance on the fly is not an unfeasibly heavy duty calculation to be done at every page view? *** My Answer *** I could think of a simple solution that might work, at the cost of a little storage space. This gives an advantage in computational overhead. For every time you award points, track two things: * Awarded points... * Points remaining from the awarded ones. Obviously equal to awarded points at insertion time * Date they are awarded (or the expirary date, that doesn't matter). When you are subtracting points just update the the non-expired remaining points, with the oldest first. From the problem I think you can do it on-the-fly without too much overhead. You can plug in your scheme how to account the points: Per-order, add to the order table... Per-period, add a table for the points only... Of course it really depends on how much data you are expecting. Overhead will be 'fixed' for per-period and otherwise scale with orders/customer. [snip] Maybe this helps a bit, - Joris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] How to recognize obsolete WAL logs
Hi all, I participate on realization of warm standby pg 8.2.3, according to documentation. Mostly all works fine, but one problem remains. The recovering rdbms eats transfered wal logs fluently, but I cannot see the way how to recognize if the recovered wal log file is really processed into db and, consequently, is obsolete and does not need to be archived for this recovery. Specially, when recovering rdbms is stopped, some wal log can be processed (and automatically deleted), but the rdbms asks the same file after restart in recovery mode. Is it some way how to recognize such logs? I know that I should keep base backup AND all wal logs after pg_start_backup, but my question is NOT about safe archiving, but about log shipping. Jan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to recognize obsolete WAL logs
On 3/19/07, Jan Poslusny [EMAIL PROTECTED] wrote: Hi all, I participate on realization of warm standby pg 8.2.3, according to documentation. Mostly all works fine, but one problem remains. The recovering rdbms eats transfered wal logs fluently, but I cannot see the way how to recognize if the recovered wal log file is really processed into db and, consequently, is obsolete and does not need to be archived for this recovery. Specially, when recovering rdbms is stopped, some wal log can be processed (and automatically deleted), but the rdbms asks the same file after restart in recovery mode. Is it some way how to recognize such logs? I know that I should keep base backup AND all wal logs after pg_start_backup, but my question is NOT about safe archiving, but about log shipping. you should check out the pg_standby utility. They solved the problem by letting you set the maximum amount of files in your archive transfer folder, which should be the maximum amount of WAL traffic you would see between checkpoints plus a good safety margin. It works... merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Design / Implementation problem
hmmm. just a general notice: A customer loyalty program, which expires earned points, not to let the customer win anything valuable? If I were your client, I wouldn't be happy with this. 2007/3/18, Naz Gassiep [EMAIL PROTECTED]: We are running a customer loyalty program whereby customers earn points for purchasing products. Each product has a value of points that are earned by purchasing it, and a value of points required to redeem it. In order to prevent customers from stockpiling points, we want to place an expiry date on points so that unused points expire and are lost if they are not redeemed within a certain period of time. This will be calculated on a FIFO basis, I.e., the oldest points will expire first. We will assume the expiry period is 12 months. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] deleting a foreign key that has no references
Hello, I'm using 8.1.8, and I have a situation where a record in one table is only meaningful when it is referenced via foreign key by one or more records in any one of several tables. So, really what I want is when one of the referring records is deleted, to have a trigger check to see if it was the last one to use that foreign key, and if so, to delete that other record, too. My first implementation of this functionality was to write a trigger function that executed a COUNT(*) on all of the tables that could have a reference in them. That became way too slow for the number of records in these tables. Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the foreign key constraint, and then trying to catch the exception thrown when a deletion attempt is made on the record. However, it seems that this PL/pgsql snippet fails to catch such an error: BEGIN EXCEPTION WHEN RAISE_EXCEPTION THEN RETURN NULL; WHEN OTHERS THEN RETURN NULL; END; But, really, I just want to be able to test to see how many references there are to a key. Is there some way to do that? Thank you, Glen Mabey ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Design / Implementation problem
Filip Rembiałkowski [EMAIL PROTECTED] writes: hmmm. just a general notice: A customer loyalty program, which expires earned points, not to let the customer win anything valuable? If I were your client, I wouldn't be happy with this. On the other hand, having the possibility is better than having nothing... This is common to force the customer to buy more and more often. You have 12 months to earn 3000 points. If you have 2850 points, then you'll consider buying thing to earn 150 more points to win something... But if you don't have any incentive, then why should you care buying something now? This is very common with miles for flights. If you fly often, you get upgrades, discounts, etc. If you don't, then you pay the fare as everybody else. -- Jorge Godoy [EMAIL PROTECTED] ---(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] DST problem on Windows Server
Margaret Gillon offered the following kind support: We had to run a special utility from Windows... I will email instructions our admin put together... Hi Margaret, I heard from my client this morning and that did the trick! Thanks again for you help, Regards, George ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] planning issue
create a function lower index and instead of calling ilike call ~
lower('123')
To clarify a little:
CREATE INDEX table_a_lower_field_1_idx on table_a ((lower(field_1)));
CREATE INDEX table_a_lower_field_2_idx on table_a ((lower(field_2)));
SELECT
*
FROM
table_a
WHERE
id != 10001
AND
(
( lower(field_1) = '123' )
OR
( lower(field_2) = 'abc' )
)
To put my own two cents in, I always try to make sure I use lower()
in the query on everything I'm comparing, as Josh originally
suggested, so I would do this:
lower(field_2) = lower('abc')
This ensures that both sides of the comparison are being downcased
the same way - otherwise there might be a discrepancy due to
collation differences, etc., between the client and the server sides.
This seems silly in this example, but I think it's a good habit.
- John Burger
MITRE
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Planner tuning
I have a query here for which postgres chooses a nested loop, for which it takes ~19s to complete in the worst-case (measured). However, if I disable nestloops it completes in ~400ms... It seems pretty obvious that the planner underestimates the cost of nestloops here, is there some way to tweak this? This is using PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13) The server is on some kind of xen domain. I have absolutely no idea what impact that has for various planner parameters (except that it'll probably not perform as well as an unxenned server), it may be relevant. The plans with and w/o nestloops respectively are attached (to prevent wrapping). -- 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 // QUERY PLAN Aggregate (cost=3946.48..3946.56 rows=1 width=62) (actual time=19940.602..19940.604 rows=1 loops=1) - Nested Loop (cost=3641.37..3946.40 rows=1 width=62) (actual time=559.955..19917.625 rows=969 loops=1) Join Filter: (outer.property_id = inner.property_id) - Hash Join (cost=3348.15..3549.62 rows=1 width=50) (actual time=525.618..552.537 rows=969 loops=1) Hash Cond: (outer.property_id = inner.property_id) - HashAggregate (cost=3066.41..3212.93 rows=3663 width=12) (actual time=506.511..519.870 rows=3522 loops=1) - Bitmap Heap Scan on fewo_property_availability_month property_availability_month (cost=232.19..2517.02 rows=36626 width=12) (actual time=6.238..234.237 rows=37316 loops=1) Recheck Cond: (300 = country_id) - Bitmap Index Scan on fewo_property_availability_month_country_property_idx (cost=0.00..232.19 rows=36626 width=0) (actual time=6.181..6.181 rows=37316 loops=1) Index Cond: (300 = country_id) - Hash (cost=281.74..281.74 rows=2 width=14) (actual time=19.052..19.052 rows=1683 loops=1) - Nested Loop (cost=0.00..281.74 rows=2 width=14) (actual time=0.068..14.000 rows=1683 loops=1) - Index Scan using fewo_location_ancestry_full_idx on fewo_location_ancestry ancestor (cost=0.00..49.34 rows=9 width=4) (actual time=0.024..0.172 rows=41 loops=1) Index Cond: ((ancestor_id = 309) AND (ancestor_type_id = 12) AND (child_type_id = 10)) - Index Scan using fewo_property_location_country_location_idx on fewo_property_location property_location (cost=0.00..25.80 rows=2 width=18) (actual time=0.009..0.169 rows=41 loops=41) Index Cond: ((property_location.country_id = 300) AND (outer.child_id = property_location.location_id)) Filter: (property_state_id = 3) - HashAggregate (cost=293.22..347.72 rows=2180 width=12) (actual time=0.038..11.221 rows=3522 loops=969) - Bitmap Heap Scan on fewo_period_type_property period_type_property (cost=24.68..256.98 rows=3624 width=12) (actual time=0.674..11.258 rows=3522 loops=1) Recheck Cond: (300 = country_id) - Bitmap Index Scan on fewo_period_type_property_country_property_idx (cost=0.00..24.68 rows=3624 width=0) (actual time=0.659..0.659 rows=3522 loops=1) Index Cond: (300 = country_id) Total runtime: 19941.453 ms (23 rows) QUERY PLAN -- Aggregate (cost=4278.42..4278.50 rows=1 width=62) (actual time=412.843..412.845 rows=1 loops=1) - Hash Join (cost=4076.02..4278.33 rows=1 width=62) (actual time=362.413..403.271 rows=969 loops=1) Hash Cond: (outer.location_id = inner.child_id) - Hash Join (cost=4026.66..4228.36 rows=24 width=66) (actual time=362.074..392.155 rows=3522 loops=1) Hash Cond: (outer.property_id = inner.property_id) - HashAggregate (cost=3066.41..3212.93 rows=3663 width=12) (actual time=264.426..276.010 rows=3522 loops=1)
[GENERAL] anyone heard of/use SurfRAID Triton product with postgres?
Does anyone here run Pg on a SurfRAID Triton RAID array? If so, please let me know how satisfied you are with the performance, and what kind of performance you get (operations/second, data transfer/ second, etc.) I'm looking at their fibre attached 16 sata disk solution. Thanks! =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Planner tuning
Alban Hertroys wrote: I have a query here for which postgres chooses a nested loop, for which it takes ~19s to complete in the worst-case (measured). However, if I disable nestloops it completes in ~400ms... It seems pretty obvious that the planner underestimates the cost of nestloops here, is there some way to tweak this? Ok, I think I've figured this one out. A few order by's after the groups in my subqueries helped a _lot_. I'm now back under 1s again. This is using PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13) The server is on some kind of xen domain. I have absolutely no idea what impact that has for various planner parameters (except that it'll probably not perform as well as an unxenned server), it may be relevant. The plans with and w/o nestloops respectively are attached (to prevent wrapping). ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- 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 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] deleting a foreign key that has no references
On 3/19/07, Glen W. Mabey [EMAIL PROTECTED] wrote: But, really, I just want to be able to test to see how many references there are to a key. Is there some way to do that? write a triggers which do that. depesz -- http://www.depesz.com/ - nowy, lepszy depesz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Possible planner bug?
On Fri, 2007-03-16 at 21:30 -0400, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: The planner chooses what looks to me like a very strange plan for this query: Exactly which PG version is this? = select version(); version PostgreSQL 8.2.3 on i386-portbld-freebsd6.1, compiled by GCC cc (GCC) 3.4.6 [FreeBSD] 20060305 Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] deleting a foreign key that has no references
On Mon, 19 Mar 2007, Glen W. Mabey wrote: Hello, I'm using 8.1.8, and I have a situation where a record in one table is only meaningful when it is referenced via foreign key by one or more records in any one of several tables. So, really what I want is when one of the referring records is deleted, to have a trigger check to see if it was the last one to use that foreign key, and if so, to delete that other record, too. My first implementation of this functionality was to write a trigger function that executed a COUNT(*) on all of the tables that could have a reference in them. That became way too slow for the number of records in these tables. Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the foreign key constraint, and then trying to catch the exception thrown when a deletion attempt is made on the record. However, it seems that this PL/pgsql snippet fails to catch such an error: BEGIN EXCEPTION WHEN RAISE_EXCEPTION THEN RETURN NULL; WHEN OTHERS THEN RETURN NULL; END; Was that the actual function you used or just a shortened version? A function like that with a delete of the referenced table in the body for the appropriate key appeared to have reasonable behavior on my 8.2 system with an immediate constraint, but I didn't do very much testing. One issue is that to test the insert of a row into the referenced table you'd probably need to defer a check that the row is referenced in order to have time to insert referencing rows. But, really, I just want to be able to test to see how many references there are to a key. Is there some way to do that? Currently, not apart from selecting on the referencing table. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] deleting a foreign key that has no references
Glen W. Mabey [EMAIL PROTECTED] writes: What I'm hoping to find out is whether there is some way to directly find out how many (using a SELECT query) references there are to a key. There's no hidden shortcut for that, no. 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
[GENERAL] DBD:Pg for Windows (PostgreSQL+Perl)
Hello I want to install Bugzilla on our intranet. I don't want to install MySQL just because of a bug tracking system so I was quite pleased to read that Bugzilla has declared full support for PostgreSQL. Yet I was unable to find the module that would make PostgreSQL 8.x work with Perl (which is Bugzilla based on) - neither in PostgreSQL installation nor on the web. I believe share/contrib/ in PostgreSQL install directory would be nice place to put the compiled module in. Meanwhile, has anyone working (ActiveState) Perl with some recent version of PostgreSQL on Windows, so you could send the Perl DBD:Pg module to me PLZ? (I don't have any experience with Perl but I believe using PPM I would be able to install the module if I had one. :o) Have a nice day J.Záruba
Re: [GENERAL] deleting a foreign key that has no references
Glen W. Mabey wrote:
On Mon, Mar 19, 2007 at 04:51:57PM +0100, hubert depesz lubaczewski wrote:
On 3/19/07, Glen W. Mabey [EMAIL PROTECTED] wrote:
I'm using 8.1.8, and I have a situation where a record in one table
is
only meaningful when it is referenced via foreign key by one or more
records in any one of several tables.
So, really what I want is when one of the referring records is
deleted,
to have a trigger check to see if it was the last one to use that
foreign key, and if so, to delete that other record, too.
My first implementation of this functionality was to write a trigger
function that executed a COUNT(*) on all of the tables that could
have a
reference in them. That became way too slow for the number of
records
in these tables.
Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the
foreign
key constraint, and then trying to catch the exception thrown when a
deletion attempt is made on the record. However, it seems that this
PL/pgsql snippet fails to catch such an error:
BEGIN EXCEPTION
WHEN RAISE_EXCEPTION THEN
RETURN NULL;
WHEN OTHERS THEN
RETURN NULL;
END;
But, really, I just want to be able to test to see how many
references there are to a key. Is there
some way to do that?
write a triggers which do that.
I understand that a trigger should be written, and I have already
implemented two such triggers, as described above.
What I'm hoping to find out is whether there is some way to directly
find out how many (using a SELECT query) references there are to a key.
This query will return the list of foreign keys which refer to primary keys:
SELECT
g as DB,n.nspname as PK_schema,pc.relname as
PK_table,pa.attname as PK_column,
n.nspname as FK_schema,c.relname as FK_table,a.attname as
FK_column,b.n as FK_column_number, f.conname as FK_name,
pr.conname as PK_name
FROM
current_database()g,pg_catalog.pg_attribute a,pg_catalog.pg_attribute
pa,pg_catalog.pg_class c,pg_catalog.pg_class pc,pg_catalog.pg_namespace n,
pg_catalog.pg_namespace pn,pg_catalog.pg_constraint f left join
pg_catalog.pg_constraint pr on(f.conrelid=pr.conrelid and pr.contype='p'),
(SELECT * FROM
generate_series(1,current_setting('max_index_keys')::int,1))b(n)
WHERE
n.oid=c.relnamespace AND pn.oid=pc.relnamespace AND pc.oid=f.confrelid
AND c.oid=f.conrelid AND pa.attrelid=f.confrelid AND a.attrelid=f.conrelid
AND pa.attnum=f.confkey[b.n]AND a.attnum=f.conkey[b.n]AND
f.contype='f'AND f.conkey[b.n]0 AND has_schema_privilege(n.oid,
'USAGE'::text);
Add conditions to the pr.conname and you will get what you need
---(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] DBD:Pg for Windows (PostgreSQL+Perl)
Jaroslav Záruba wrote: Hello I want to install Bugzilla on our intranet. I don't want to install MySQL just because of a bug tracking system so I was quite pleased to read that Bugzilla has declared full support for PostgreSQL. Yet I was unable to find the module that would make PostgreSQL 8.x work with Perl (which is Bugzilla based on) - neither in PostgreSQL installation nor on the web. I believe share/contrib/ in PostgreSQL install directory would be nice place to put the compiled module in. Meanwhile, has anyone working (ActiveState) Perl with some recent version of PostgreSQL on Windows, so you could send the Perl DBD:Pg module to me PLZ? Is this on Win32? DBD::Pg is a perl module, you can get it from CPAN. Joshua D. Drake (I don't have any experience with Perl but I believe using PPM I would be able to install the module if I had one. :o) Have a nice day J.Záruba -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] deleting a foreign key that has no references
On 3/19/07, Glen W. Mabey [EMAIL PROTECTED] wrote: write a triggers which do that. I understand that a trigger should be written, and I have already implemented two such triggers, as described above. no, i think i didn't make myself clear. let's use this situation: we have tables: create table x (id serial primary key, some_text text); create table y (id serial primary key, x_id int4 not null references x (id), some_field text); where table x is your table in which you want to make some deletes, and table y is some table that has foreign key to it. now, you add to table x a field: alter table x add column refcount int4 not null default 0; and then we add a trigger: CREATE OR REPLACE FUNCTION some_trg() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN IF TG_OP = 'INSERT' THEN UPDATE x SET refcount = refcount + 1 WHERE id = NEW.x_id; ELSIF TG_OP = 'UPDATE' THEN IF NEW.x_id OLD.x_id THEN UPDATE x SET refcount = refcount + 1 WHERE id = NEW.x_id; UPDATE x SET refcount = refcount - 1 WHERE id = OLD.x_id; END IF; ELSIF TG_OP = 'DELETE' THEN UPDATE x SET refcount = refcount - 1 WHERE id = OLD.x_id; END IF; RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql'; CREATE TRIGGER some_trg AFTER INSERT OR UPDATE OR DELETE ON y FOR EACH ROW EXECUTE PROCEDURE some_trg(); then - you have to populate the refcount field with current value, but this is easily doable, and as far as i know you already are doing it in your code. so - the trigger keeps the refcount up to date. it is quite lightweight, so shouldn't be a problem. and what's more important - size of the table trigger is on doesn't matter. simple, and working. depesz -- http://www.depesz.com/ - nowy, lepszy depesz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DBD:Pg for Windows (PostgreSQL+Perl)
Joshua D. Drake wrote: Jaroslav Záruba wrote: Hello I want to install Bugzilla on our intranet. I don't want to install MySQL just because of a bug tracking system so I was quite pleased to read that Bugzilla has declared full support for PostgreSQL. Yet I was unable to find the module that would make PostgreSQL 8.x work with Perl (which is Bugzilla based on) - neither in PostgreSQL installation nor on the web. I believe share/contrib/ in PostgreSQL install directory would be nice place to put the compiled module in. Meanwhile, has anyone working (ActiveState) Perl with some recent version of PostgreSQL on Windows, so you could send the Perl DBD:Pg module to me PLZ? Is this on Win32? DBD::Pg is a perl module, you can get it from CPAN. Doh, I see now the question in the subject ;) http://www.cs.cmu.edu/~blangner/perl/dbd_pg.html Joshua D. Drake (I don't have any experience with Perl but I believe using PPM I would be able to install the module if I had one. :o) Have a nice day J.Záruba -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] Planner tuning
Alban Hertroys [EMAIL PROTECTED] writes: It seems pretty obvious that the planner underestimates the cost of nestloops here, is there some way to tweak this? The real problem is the factor-of-a-thousand underestimate of the size of this join: - Nested Loop (cost=0.00..281.74 rows=2 width=14) (actual time=0.068..14.000 rows=1683 loops=1) - Index Scan using fewo_location_ancestry_full_idx on fewo_location_ancestry ancestor (cost=0.00..49.34 rows=9 width=4) (actual time=0.024..0.172 rows=41 loops=1) Index Cond: ((ancestor_id = 309) AND (ancestor_type_id = 12) AND (child_type_id = 10)) - Index Scan using fewo_property_location_country_location_idx on fewo_property_location property_location (cost=0.00..25.80 rows=2 width=18) (actual time=0.009..0.169 rows=41 loops=41) Index Cond: ((property_location.country_id = 300) AND (outer.child_id = property_location.location_id)) Filter: (property_state_id = 3) Have you got up-to-date ANALYZE stats for both of these tables? Maybe increasing the statistics targets for them would help. You may be kind of stuck because of the lack of cross-column statistics --- I suppose these columns are probably rather highly correlated --- but you should at least try pulling the levers you've got. One thought is that country_id is probably entirely determined by location_id, and possibly ancestor_type_id is determined by ancestor_id. If so you should be leaving them out of the queries and indexes; they're not doing anything for you except fooling the planner about the net selectivity of the conditions. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Own messages for constraints?
On Fri, 2007-03-16 at 20:19 +0100, Kacper Chrapa wrote: Hi ! Is it possible in postgres to define own message for some constraint? Example: If i create check constraint on zip_code column , i want to get a message: Zip code is invalid.Please,input code in format: nn-nnn and I want to send this message to the end user. It will be much better(in my case) than violates constraint zip_code_check :-) . I can make this validation in trigger (and send msg to application by notice or raise),but in this case i will duplicate validation rules (in trigger and in constraint).Effect: Lower performance(i think) and rules in two places... There's no custom message for a CHECK constraint violation. You can use an AFTER trigger instead of a CHECK constraint (but that may have a performance impact - test for your application). You can also give descriptive names to your CHECK constraint which may help. It's not a good idea to pass database errors directly back to the user anyway. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Own messages for constraints?
On 3/19/07, Jeff Davis [EMAIL PROTECTED] wrote: You can use an AFTER trigger instead of a CHECK constraint (but that may have a performance impact - test for your application). are you sure you meant AFTER? why? generally data-checks should be in before triggers. i guess. depesz -- http://www.depesz.com/ - nowy, lepszy depesz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] cannot get build (initdb) to work after trying for a week
(list re-added) you need a timezone directory, not just timezonesets. So your problem is why that's not being installed when you do make install. You need to check the output from the make install command to see if there are any errors or warnings related to that. (It's done when make install reaches the src/timezone directory) The PATH should have nothing to do with it. //Magnus Eric Fish wrote: Thanks. I just compared my Mac and PC installations. Mac installation (which works correctly) and has a bunch of entries, including other directories, under share/postgresql/timezone. The PC installation (which doesn't work) has a directory share/timezonesets with similar, but far fewer entries (all .txt files). Also tried prior to running initdb: export TZ=PST which didn't work either. Was thinking that maybe it's my PATH on Windows that could be fouling things up. I added the necessary bin dirs for pg and msys/mingw but have all types of MS items in the path too (which would be difficult to clean out). But I'm wondering if that's it. -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: Sunday, March 18, 2007 7:11 AM To: Eric Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] cannot get build (initdb) to work after trying for a week Eric wrote: I am getting truly desperate! I am trying to build any version of Postgres on Windows from source. First experience with Postgres source. I have struggled for days trying to solve a problem. Would be willing to pay for the solution!!! The following error occurred when issuing a vanilla initdb command on my Windows system. FATAL: could not select a suitable default timezone DETAIL: It appears that your GMT time zone uses leap seconds. PostgreSQL does not support leap seconds. I built this from 8.1.8 source under msys/mingw. Also tried 8.2.2. Everything builds fine but initdb always reports the above error and quits. This could happen if the timezone files aren't properly installed. This should normally happen when you do make install, but verify that they are actually present (in share/timezone). I also had to create a typedef int ssize_t in a couple of files to get the compile finished. Maybe this is related. Other than that, have not touched any code. I don't think that's related, but it's a clear indicator that something in your environment is broken. Because it should build without any such changes. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Own messages for constraints?
On Mon, Mar 19, 2007 at 07:08:41PM +0100, hubert depesz lubaczewski wrote: On 3/19/07, Jeff Davis [EMAIL PROTECTED] wrote: You can use an AFTER trigger instead of a CHECK constraint (but that may have a performance impact - test for your application). are you sure you meant AFTER? why? generally data-checks should be in before triggers. i guess. In an AFTER trigger you can be sure you're seeing what actually got inserted. In a BEFORE trigger other triggers after you could still modify the data... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] UPGRADATION TO 8.1
On 3/20/07, Ben Trewern [EMAIL PROTECTED] wrote: So what's he meant to do? Write a longer question just so the mandatory disclamer that his company attaches to his e-mail takes up a lower percentage of his e-mail? (or should he not ask the question at all?) Maybe use a gmail account for mailing lists like I do? Also makes mass-mailing of out-of-office messages less likely ... sounds like a bloody good idea. Regards, Ben Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Own messages for constraints?
On 3/19/07, Martijn van Oosterhout kleptog@svana.org wrote: In an AFTER trigger you can be sure you're seeing what actually got inserted. In a BEFORE trigger other triggers after you could still modify the data... yes but in after trigger the only thing you can do is to raise exception. you cannot fix the data, issue warning, or simply stop the insert/update without breaking the transaction. depesz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Possible planner bug?
Jeff Davis [EMAIL PROTECTED] writes:
I have two indexes defined on syslog_p:
syslog_p_severity_ts_idx btree (severity, ts)
syslog_p_ts_where_severity_1_idx btree (ts) WHERE severity = 1
The planner chooses what looks to me like a very strange plan for this
query:
= explain analyze select * from syslog where severity=1;
The attached crude hack makes it not do that, but I wonder whether it
won't prevent use of bitmap AND in some cases where we'd rather it did
use an AND. Want to try it out?
Possibly a more salient question is why are you bothering with two
indexes defined like that. Now that I look closely, they seem pretty
redundant.
regards, tom lane
Index: indxpath.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/indxpath.c,v
retrieving revision 1.217
diff -c -r1.217 indxpath.c
*** indxpath.c 17 Mar 2007 00:11:04 - 1.217
--- indxpath.c 19 Mar 2007 18:22:23 -
***
*** 787,793
foreach(cell2, list2)
{
! if (lfirst(cell2) == datum1)
return true;
}
}
--- 787,793
foreach(cell2, list2)
{
! if (equal(lfirst(cell2), datum1))
return true;
}
}
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Own messages for constraints?
On Mon, 2007-03-19 at 19:08 +0100, hubert depesz lubaczewski wrote: On 3/19/07, Jeff Davis [EMAIL PROTECTED] wrote: You can use an AFTER trigger instead of a CHECK constraint (but that may have a performance impact - test for your application). are you sure you meant AFTER? why? generally data-checks should be in before triggers. i guess. If you do the check BEFORE, you have to make sure that no other BEFORE triggers that execute afterward modify the data again. Assuming your AFTER trigger is on INSERT and UPDATE, there is no way for a subsequent AFTER trigger to modify the data to be invalid. So an AFTER trigger is more of an assurance that your data is valid. Note that AFTER triggers need to queue up, so if you do a huge update and have an AFTER trigger, it might use a lot of memory. BEFORE triggers don't have that problem. If you're very concerned about this you could use a BEFORE trigger and just make sure that no other trigger will cause a problem. Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] TSearch2 Problems
Hi,
I am having some problems with TSearch2 queries since upgrading from 8.0
to 8.2.
This is the query:
select count(*) from card, to_tsquery('default','test') as q where
(q @@ card.fts);
This works fine on 8.0 but gives the following error in 8.2:
ERROR: No dictionary with name 'en_stem'
SQL state: XX000
Any help will be great. Thanks
Howard.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] deleting a foreign key that has no references
On Mon, Mar 19, 2007 at 09:46:22AM -0700, Stephan Szabo wrote: On Mon, 19 Mar 2007, Glen W. Mabey wrote: Hello, I'm using 8.1.8, and I have a situation where a record in one table is only meaningful when it is referenced via foreign key by one or more records in any one of several tables. So, really what I want is when one of the referring records is deleted, to have a trigger check to see if it was the last one to use that foreign key, and if so, to delete that other record, too. My first implementation of this functionality was to write a trigger function that executed a COUNT(*) on all of the tables that could have a reference in them. That became way too slow for the number of records in these tables. Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the foreign key constraint, and then trying to catch the exception thrown when a deletion attempt is made on the record. However, it seems that this PL/pgsql snippet fails to catch such an error: BEGIN EXCEPTION WHEN RAISE_EXCEPTION THEN RETURN NULL; WHEN OTHERS THEN RETURN NULL; END; Was that the actual function you used or just a shortened version? A function like that with a delete of the referenced table in the body for the appropriate key appeared to have reasonable behavior on my 8.2 system with an immediate constraint, but I didn't do very much testing. One issue is that to test the insert of a row into the referenced table you'd probably need to defer a check that the row is referenced in order to have time to insert referencing rows. Okay, it turns out that I only had not implemented the exception catch appropriately. Here's what worked: BEGIN DELETE FROM Cuts WHERE Cuts.id = OLD.cut_id; EXCEPTION WHEN OTHERS THEN NULL; END; RETURN NULL; andyk: Thank you for the SELECT string you contributed. Unfortunately, I could not understand what it was doing -- it was way over my head WRT psql proficiency. So, I don't know whether it would have worked. At any rate, thank you all for your suggestions. Testing for an error seems to be the simplest and easiest way to accomplish what I need to do, and it seems to be fairly fast, too. Best Regards, Glen Mabey ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Possible planner bug?
On Mon, 2007-03-19 at 14:31 -0400, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: I have two indexes defined on syslog_p: syslog_p_severity_ts_idx btree (severity, ts) syslog_p_ts_where_severity_1_idx btree (ts) WHERE severity = 1 The planner chooses what looks to me like a very strange plan for this query: = explain analyze select * from syslog where severity=1; The attached crude hack makes it not do that, but I wonder whether it won't prevent use of bitmap AND in some cases where we'd rather it did use an AND. Want to try it out? Possibly a more salient question is why are you bothering with two indexes defined like that. Now that I look closely, they seem pretty redundant. You're right; they are. I was testing this partial index because I was getting a bad plan due to the relationship of the data distribution between severity and ts. Essentially, I'm expiring old data from this table, and tuples with greater severity stick around longer. Running a DELETE to expire tuples with severity=1 generates a bad plan because it sees a large number of tuples with severity=1, and also a large number of tuples where (ts now()-'3 days'). However, since I just ran the DELETE a minute ago, there are actually very few such tuples; the tuples older than 3 days are almost all of a greater severity. My experiment was to see if I could get PostgreSQL to realize this by creating a partial index where severity=1. If it's just a partial index, there are no stats on the data distribution, but I make it a functional partial index, postgres keeps stats on it. However, I don't think it's able to use those stats the way I need it to (I didn't expect it to, but I thought I'd try). Anyway, during this whole process I saw that plan and got confused. And it didn't do it in 8.1, so I thought I'd bring it up on the list. This is probably a textbook case for partitioning based on severity (there are only 8). I may end up doing that after I convince myself I won't lose out in some other way. What about your patch is a crude hack, by the way? At first glance it looks like you're using a more correct test. 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: [GENERAL] TSearch2 Problems
On Mon, 19 Mar 2007, Howard Cole wrote:
Hi,
I am having some problems with TSearch2 queries since upgrading from 8.0 to
8.2.
This is the query:
select count(*) from card, to_tsquery('default','test') as q where (q @@
card.fts);
This works fine on 8.0 but gives the following error in 8.2:
ERROR: No dictionary with name 'en_stem'
show us output of
select * from pg_ts_dict;
SQL state: XX000
Any help will be great. Thanks
Howard.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
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] Own messages for constraints?
On Mon, 2007-03-19 at 19:26 +0100, hubert depesz lubaczewski wrote: On 3/19/07, Martijn van Oosterhout kleptog@svana.org wrote: In an AFTER trigger you can be sure you're seeing what actually got inserted. In a BEFORE trigger other triggers after you could still modify the data... yes but in after trigger the only thing you can do is to raise exception. you cannot fix the data, issue warning, or simply stop the insert/update without breaking the transaction. If you only issue a warning, it's not a constraint because data violating the constraint still goes in. And you can issue a warning in an AFTER trigger. Fixing the data is probably something that should be done in a different place (like the application correcting the data). It also begs the question: If the data can be fixed, why is the original form not acceptable anyway (i.e. fixed in the datatype's input function)? I assume by stop the insert/update without breaking the transaction you mean a return NULL from the BEFORE trigger, thereby not inserting the row. COMMIT should mean yes, I successfully completed what you asked, and that usually means that the data was actually inserted. You're correct that you have more flexibility with a BEFORE trigger in many ways. However, be careful using those strategies to constrain data. Generally you do want it to break the transaction if the data you're trying to insert is invalid. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] TSearch2 Problems
show us output of select * from pg_ts_dict; simple;dex_init(internal);;dex_lexize(internal,internal,integer);Simple example of dictionary. en_stem;snb_en_init(internal);contrib/english.stop;snb_lexize(internal,internal,integer);English Stemmer. Snowball. ru_stem_koi8;snb_ru_init_koi8(internal);contrib/russian.stop;snb_lexize(internal,internal,integer);Russian Stemmer. Snowball. KOI8 Encoding ru_stem_utf8;snb_ru_init_utf8(internal);contrib/russian.stop.utf8;snb_lexize(internal,internal,integer);Russian Stemmer. Snowball. UTF8 Encoding ispell_template;spell_init(internal);;spell_lexize(internal,internal,integer);ISpell interface. Must have .dict and .aff files synonym;syn_init(internal);;syn_lexize(internal,internal,integer);Example of synonym dictionary thesaurus_template;thesaurus_init(internal);;thesaurus_lexize(internal,internal,integer,internal);Thesaurus template, must be pointed Dictionary and DictFile ---(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] TSearch2 Problems
Oleg, My problem may have been solved by doing a vacuum full analyse. I shall check tomorrow and get back to you. Thanks, Howard. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Possible planner bug?
Jeff Davis [EMAIL PROTECTED] writes: What about your patch is a crude hack, by the way? At first glance it looks like you're using a more correct test. The surrounding code and comments would need to be changed to reflect what's going on. 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] deleting a foreign key that has no references
On Mon, 2007-03-19 at 11:12 -0500, Glen W. Mabey wrote: What I'm hoping to find out is whether there is some way to directly find out how many (using a SELECT query) references there are to a key. In the easy case when your schema doesn't change often, you can just hard code a query of the FK tables and add up the row counts. I bet something like 'select count(*) from (select * from FKtable1 UNION ALL select * from FKtable2 ... )' will work (and I'm guessing that the UNION ALL will optimize well). Obviously, you want indexes on the FKs. The harder and more general case is to build such a query dynamically from pg_depends. A good start would be to write a function that returns an sql query like the above to count the referents of PKtable(PKcolumn). If you can declare this function stable or immutable (I'm not sure of this), then it might not be too painful to generate the query within the trigger itself. Otherwise, you might have to store/update these queries in a separate table after every DDL change. See the pg_depends documentation at http://www.postgresql.org/docs/8.2/interactive/catalog-pg-depend.html -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter. ---(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] deleting a foreign key that has no references
On Mon, 2007-03-19 at 13:03 -0700, Reece Hart wrote: The harder and more general case is to build such a query dynamically from pg_depends ... See the pg_depends documentation at Apologies. I intended to write pg_constraint and the documentation at http://www.postgresql.org/docs/8.2/interactive/catalog-pg-constraint.html -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 ./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush kernel warning: universe consuming too many resources. Killing. universe killed due to catastrophic leadership. Try -uspres carter.
Re: [GENERAL] UPGRADATION TO 8.1
So what's he meant to do? Avoiding terms like UPGRADATION would be an excellent start. Stuart. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UPGRADATION TO 8.1
Andrej Ricnik-Bay wrote: On 3/20/07, Ben Trewern [EMAIL PROTECTED] wrote: So what's he meant to do? Write a longer question just so the mandatory disclamer that his company attaches to his e-mail takes up a lower percentage of his e-mail? (or should he not ask the question at all?) Maybe use a gmail account for mailing lists like I do? Also makes mass-mailing of out-of-office messages less likely ... sounds like a bloody good idea. And if webmail is not allowed in his place of business? This whole comment thread is pointless. If you aren't going to help the guy, don't respond. Joshua D. Drake Regards, Ben Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UPGRADATION TO 8.1
Mageshwaran wrote: Hi, In our company there are many postgres servers in 7.X version,we are in the process of upgrading to 8.1 version, can anyone tell me the method to do this upgradation. You will want to use the 8.1 version of pg_dump/pg_dumpall to connect to the 7.4 version of postgresql to do the restore. There is pretty good docs on it here: http://www.postgresql.org/docs/8.2/static/backup.html You will want to be careful though, some things have changed since 7.4. IIRC, timestamp/timestamptz changes, and float changes as well. Sincerely, Joshua D. Drake Regards J Mageshwara DBA ** DISCLAIMER ** -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Optimizing warm standby. Will this work? [PITR WAL]
I was planning to setup a warm standby using WAL Files and my current
setup seems to work.
Here is what I do in the current setup
On the primary:
1. On the primary, setup continuous archive every 2 minutes. The WAL
files are stored on a known location.
2. On the primary : pg_start_backup('label');
3. Wait for 30 seconds
4. On the primary : pg_stop_backup();
5. Tar, gzip the data directory except the pg_xlog on the primary and
ship it to the standby.
On the Standby:
1. unzip, untar the data directory obtained from the primary
2. remove everything from pg_xlog.
3. Copy the wal files from the known location to pg_xlog
4. Move to recovery.conf and restart the db.
The optimization step I am looking for is as follows, instead of doing
a tar/gzip of the data directory, I am planning to do a pg_dumpall of
the primary. That is, the new steps will be:
0. Ensure that no application connects to the db.
1. On the primary, do a pg_dumpall
2. Do steps 1..4 as done earlier on the primary
3. Ship the pg_dumpall file
On the secondary
1. dropdb and restore from the supplied pg_dumpall
2. Stop db, remove everything from pg_xlog, copy the WAL Files
3. Move to recovery.conf and restart the db.
That way, I figure I do not have to move the entire data directory
around. I just need the base schema and data contents. The question
is, will the above setup work? Any gotchas?
Regards
Dhaval
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] PostgreSQL Party, July 22nd 2007 (Day before OSCON 2007)
Hello, We are planning a PostgreSQL party for the weekend before OSCON in Portland Oregon. The 22nd is a Sunday, with OSCON beginning on the 23rd which is Monday. Although the exact venue has not been decided it will likely be a hotel near the convention center. We are also reaching out to other communities, such as Python, Django, PHP, LedgerSMB etc... If you are a FOSS member with ties to a community that utilizes or supports our database, you are invited! Some outstanding questions: 1. Should their be food? 2. Should their be entertainment of the liquid adult variety? 3. Should their be music? Or do people just want to basically chill, and talk with some other great people about the best FOSS database that exists? If you are interested in attending please submit an RSVP to: [EMAIL PROTECTED] Full Name Email Company (if you are representing) Would be appreciated. Sincerely, Joshua D. Drake P.S. There might be a nominal door charge (5.00) to help offset costs any excess would of course be donated directly to the PostgreSQL project. -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] Reference Type in PostgreSQL
Hello all, I'm new in PostgreSQL... I would want to know like PostgreSQL manages the type reference that defines the standard SQL:1999. I want to define the type of attribute like a reference at other type. For example, in Oracle8i the definition is: -- Type Department CREATE OR REPLACE TYPE Department_type AS OBJECT ( code NUMBER(5), name VARCHAR(40) ); -- Type Employee CREATE OR REPLACE TYPE Employee_type AS OBJECT ( code NUMBER(5), name VARCHAR2(40), department REF Department_type -- Reference to Department object type ); * * How can I define it in PostgreSQL? I haven't found it in the manuals. Thank you for help. -- Elena -- Elena
Re: [GENERAL] cannot get build (initdb) to work after trying for a week
To respond to Alvaro's post, I did try setting TZ=PST, no luck there. As Magnus points out, the install doesn't appear complete because of the missing timezone dir. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] cannot get build (initdb) to work after trying for a week
Thanks for responding Magnus. I did reinstall both msys and mingw, as well as blow away the source and start over from scratch. Maybe there is a stray file or directory that's not getting deleted on the clean. Also, does clean just clean up the compile environment, or does it also clean up the install? I did manually remove directories too but I suppose I could have missed something. Out of desperation, I decided to try everything on my wife's mac, remembering that os x is unix. Didn't take long at all to download all the dev tools I needed, get a clean compile and get things running there. Can't remember if it was in this forum, but a few people asked why I was downloading source and not binary - I am experimenting with a different indexing (multi-dimensional) method. I looked hard at GiST, but it doesn't look like GiST supports multi-dimensional indexing methods unless all the dimensions are in a single column. I need to see each column in the index before making a decision about search paths to take, branches to follow, etc. GiST appears to support one column at a time - i.e. if my index is lastname, firstname, then take a look at lastname, and if that's a tie, then look at firstname. But it won't let you look at lastname and firstname at the same time, then make decisions. Other than that, looks like an outstanding feature. Finally, as a newcomer to PG, I'm in search of general development resources. Any good places to go besides the PG documentation and the pgsql newsgroups? Regards, Eric ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] java.net.SocketException: Connection reset
Hello!! I am working with hermes 2 postgresql and I obtain this mistake I am thinking this mistake is because it not conect very well. Can someone help me?? thanks very mach 2007-03-15 11:02:51 [Thread-6] ERROR cecid.ebms.spa Error in collecting message from mail boxhk.hku.cecid.piazza.commons.net.ConnectionException: Unable to connect to incoming mail server by javax.mail.MessagingException: Connect failed; nested exception is: java.net.SocketException: Connection reset at hk.hku.cecid.piazza.commons.net.MailReceiver.connect(MailReceiver.java:66) at hk.hku.cecid.ebms.spa.task.MailCollector.getTaskList(MailCollector.java:49) at hk.hku.cecid.piazza.commons.module.ActiveTaskModule.execute(ActiveTaskModule.java:137) at hk.hku.cecid.piazza.commons.module.ActiveModule.run(ActiveModule.java:205) at java.lang.Thread.run(Thread.java:534)Caused by: javax.mail.MessagingException: Connect failed; nested exception is: java.net.SocketException: Connection reset at com.sun.mail.pop3.POP3Store.protocolConnect(POP3Store.java:120) at javax.mail.Service.connect(Service.java:255) at javax.mail.Service.connect(Service.java:134) at javax.mail.Service.connect(Service.java:86) at hk.hku.cecid.piazza.commons.net.MailReceiver.connect(MailReceiver.java:63) ... 4 more _ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview
[GENERAL] cache - timing
I am trying to measure the time taken for a number of queries using \timing . All queries on my table other than the first one are pretty fast. This is likely due to caching. Is there any way to clear the internal cache of Postgres. Should I be worried about the entire OS cache also? Appreciate the help! Akanksha ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] java.net.SocketException: Connection reset
Sorry, but I can't seen anything postgres related in those errors messages, all seems to point at e-Mail issues, not database connectivity. Cheers, Andrej ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Reference Type in PostgreSQL
On Mon, 2007-03-19 at 11:30 +0100, Elena wrote: Hello all, I'm new in PostgreSQL... I would want to know like PostgreSQL manages the type reference that defines the standard SQL:1999. I want to define the type of attribute like a reference at other type. PostgreSQL doesn't allow references/pointers. OIDs are the closest thing to a reference in PostgreSQL. From _An Introduction to Database Systems_ by C.J. Date, p. 872: The blunder [The Second Great Blunder] consists of mixing pointers and relations. So not everyone thinks that references/pointers in a relation value are a good idea. I don't know how the PostgreSQL developers feel about it, but I haven't seen a lot of demand for this feature on these lists. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [PHP] phpPgAdmin - prior version available?
On 3/18/07, Bob Hartung [EMAIL PROTECTED] wrote: Hi all, I have been struggling with phpPgAdmin 4.1 - login failures. There does not yet seem to be a fix. Where can I find a prior version for FC6 - rpm, tar.gz etc. Thanks, Bob Try this one, http://ftp.uni-koeln.de/mirrors/fedora/linux/extras/6/ppc/phpPgAdmin-4.0.1-7.fc6.noarch.rpm phpPgAdmin 4.0.1 Tijnema -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
