Re: [GENERAL]
Hello 2008/7/17 ram subbu [EMAIL PROTECTED]: Hi All, Here i am facing a peculiar scenario. I have a query which always result in 3 rows. There are two columns in the query result. I have to make the result of 3 rows as a one row select * from tab1; col1---col2 --- 15 238 350 the result should be select array_to_string(array(select col1 || '-' || col2 from tab1), '/'); regards Pavel Stehule col 1-5/2-38/3-50 Is there any possibilities? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Standard binaries compilier - how to know?
Hello! Is there any opportunity to find out what compilier is used to build standard binaries from http://www.postgresql.org/ftp/binary/v8.3.3/win32/ (postgresql-8.3.3-1-binaries-no-installer.zip MSVC or MinGW(gcc)? I have some problems with C-procedures compilation (PG 8.3, WinXP). I've got the information from mailing list (thanks to all who had answered) that the cause of these problems could be the using of different compiliers. But now my library doesn't work correctly in both variants (MSVC and MinGW)... :( Regards, Marina. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Standard binaries compilier - how to know?
2008/7/17 el dorado [EMAIL PROTECTED]: Hello! Is there any opportunity to find out what compilier is used to build standard binaries from http://www.postgresql.org/ftp/binary/v8.3.3/win32/ (postgresql-8.3.3-1-binaries-no-installer.zip MSVC or MinGW(gcc)? I have some problems with C-procedures compilation (PG 8.3, WinXP). I've got the information from mailing list (thanks to all who had answered) that the cause of these problems could be the using of different compiliers. But now my library doesn't work correctly in both variants (MSVC and MinGW)... :( SELECT version(); It was actually built with VC++ 2005, fully service-packed. That said, it is possible to mix'n'match compilers, provided you don't pass crt pointers around. Both Slony and PostGIS are still built with Mingw/Msys. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unable to drop a constraint
Hello Tom, Il giorno 16/lug/08, alle ore 16:40, Tom Lane ha scritto: Enrico Sirola [EMAIL PROTECTED] writes: I have two tables, one has a field with a foreign key pointing to the primary key of another table. When I to drop the first table, I get the following error: test=# drop table user; ERROR: customer_pkey is an index I don't think you're being entirely truthful with us, because user is a reserved word. If you'd typed exactly that, you would have gotten regression=# drop table user; ERROR: syntax error at or near user LINE 1: drop table user; ^ You are right, the table name is slightly different, anyway I simply searched the original table name and replaced it with user. You will now be able to reproduce it anyway, because I can't reproduce the behaviour myself recreating the schema on another cluster. I'm not certain if this is just pilot error or if there really is something odd going on, but obfuscating what you're doing doesn't make it easier to decipher. Please show what you really typed and what you really got back. psql \d output for the two tables might be helpful too. please consider the old psql session, just s/sps_user/user and s/ sps_customer/customer Here's what you requested: -bash-3.1$ psql sps_test Welcome to psql 8.3.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit sps_test=# set role sps; SET sps_test= \d List of relations Schema | Name| Type | Owner +---+---+--- public | schema_version| table | slony sps| enabled_customer | view | sps sps| group_to_customer | table | sps sps| sps_customer | table | sps sps| sps_user | table | sps (5 rows) sps_test= \d sps_user Table sps.sps_user Column|Type | Modifiers -+- +-- name| character varying(32) | not null role| name| not null password| password| created | timestamp(3) with time zone | not null default (now())::timestamp(3) with time zone valid_until | timestamp(3) with time zone | not null default 'infinity'::timestamp(3) with time zone email | character varying(100) | Indexes: sps_user_pkey PRIMARY KEY, btree (name) sps_test= \d sps_customer Table sps.sps_customer Column|Type | Modifiers --+- +-- name | name| not null description | text| ftp_site | character varying | ftp_user | character varying | ftp_password | character varying | created | timestamp(3) with time zone | not null default (now())::timestamp(3) with time zone valid_until | timestamp(3) with time zone | not null default 'infinity'::timestamp(3) with time zone Indexes: sps_customer_pkey PRIMARY KEY, btree (name) Thanks for your help, e. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unable to drop a constraint
Hi Michael, Il giorno 16/lug/08, alle ore 18:21, Michael Glaesemann ha scritto: On 2008-07-16, at 7:18 AM, Enrico Sirola wrote: Hi, I'm using postgresql version 8.3.1 I have two tables, one has a field with a foreign key pointing to the primary key of another table. When I to drop the first table, I get the following error: test=# drop table user; ERROR: customer_pkey is an index Are you using slony perchance? This looks like an issue with slony blocking DDL operations: h I don't use slony anymore, but these tables were part of a replication set in the past It seems a good starting point, thanks a lot, e. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Last chance to sign up for PDXPUG Day! You know you want to!
See! these fabulous talks: http://pugs.postgresql.org/view/pdxpugtalksview Meet! those PDXPUGers you keep hearing about! Enjoy! tasty food beverages at the afterparty! Sign up here: http://spreadsheets.google.com/viewform?key=paoTJ9uEi8vIqumUzwzrCAwemail=true Please sign up for free admission to the after-party at the Gotham Tavern, and to reserve a tshirt. We are requesting a $20 donation that will be given to Software in the Public Interest for attendance. You can pay with check or cash when you arrive at the conference. See you there! gabrielle -- PDXPUG: http://pugs.postgresql.org/pdx -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] query optimization
Hello:
This posting is a follow up to this posting from July 15th.
http://archives.postgresql.org/pgsql-general/2008-07/msg00569.php
Given the following select statement
select sec.*
from security sec , positions_gsco
where positions_gsco.securitykey is NULL and
upper(positions_gsco.producttype) = 'OP' and
upper(substring(productid,1,3)) = 'CFD' and
getsectypekey('CFD') = sec.securitytypekey and
positions_gsco.taskrunkey = 359 and
positions_gsco.issuecurrency = sec.securityissuecurrISO and
positions_gsco.strikeprice = sec.strikeprice and
positions_gsco.expirationdate = sec.expirationdate and
( positions_gsco.underlyingisin = sec.underlyingisin or
positions_gsco.underlyingcusip = sec.underlyingcusip or
positions_gsco.underlyingbloombergticker = sec.
underlyingbloomberg ) ;
Run as is this statement never returns .
Additional info:
select * from positions_gsco
where ((securitykey IS NULL) AND
(upper(substring((productid)::text, 1, 3)) = 'CFD'::text) AND
(upper((producttype)::text) = 'OP'::text) AND (taskrunkey =
359))
Returns 2538 rows in 1625ms
select * from security
where (getsectypekey('CFD'::bpchar) = securitytypekey);
returns 2538 rows in 1078ms
so we are not dealing with very large datasets.
Security has an index defined as follows:
CREATE INDEX security_sectypekey ON security USING btree
(securitytypekey);
These is a total of 11443 rows in security.
If I change the getsectypekey('CFD') in the above statement to be
either ' (select getsectypekey('CFD') ) ' or '5'
I get 2632 rows in approx 4300ms. There may be an issue here with
number of rows returned. I'll look into that.
But the point is it returns in a reasonable number of seconds.
So here are the questions for the PSQL gurus:
Is getsectypekey('CFD') executing for every join (or possible join)
between positions_gsco and security?
Causing a scan of security for every possible join.
Does ' (select getsectypekey('CFD') ) ' cause the getsectype()
function to be executed once and thus
allowing the index on security to be used.
And of couse '5' makes things simple. The index on security is used.
Am I posting this in the right. If not please help me correct my error
and point me to the correct spot.
Thanks for taking a look at my issue.
Best Regards
Kevin Duffy
[GENERAL] deleting the master but not the detail
hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched But the delete command doesn't let me delete the master as long as there are details referencing it. ON DELETE RESTRICT | NO ACTION won't let me delete the master CASCADE | SET NULL | SET DEFAULT will modify the details Any idea ow to do it without the use of triggers? SQL Server is got the option ON DELETE DO NOTHING or something like that, that just oversees the deletion, but I didn't find it for postgres. _ P.D. Checa las nuevas fotos de mi Space http://home.services.spaces.live.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] postmaster.pid not visible
Moving to -general. -hackers is for discussion about PG development. On Jul 16, 2008, at 1:10 AM, cinu wrote: Hi All, I installed PostgreSQL-8.3.1 on my Suse Linux machine You should upgrade; I'm pretty sure 8.3 is up to 8.3.3 now. , it went on fine without any problems and I was able to create and access the database, even I was able to start, restart and check the status of the service. Since it is my local machine and people are remotly connecting to the database on my local machine, I used to keep the machine up and running. Today I came and checked and It was telling me that the service of postgres is not running, so I went and checked the postmaster.pid file it was not in the data folder, but I was able to get to the psql prompt and execute standard sql statements, even people were able to connect remotly and access the databse on my machine. The only difficult that I was facing was that I was unable to restart or stop the service. So with the help of the ps -ef | grep postgres command I was able to trace out the pid and then manually kill the pid with the kill -9 command, after this I was able to restart, stop or check the status of the service. Don't use kill -9. There's almost never a reason to do that, and hasn't been for probably 20 years or more. Can anyone throw light on why the postmaster.pid was not visible, the other intresting factor that I observed was that the postgres service was running on the 5432 port this was visible from the /tmp location. Also I would like to know if theer is any other alternative with which i can restart the service and retain the postmaster.pid file. My guess would be that something went in and removed the .pid file. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] deleting the master but not the detail
am Thu, dem 17.07.2008, um 11:11:00 -0500 mailte Ismael folgendes:
hi
I have one of those master-detail relationships here and I need to be able
to delete the master but leave the details untouched
But the delete command doesn't let me delete the master as long as
there are details referencing it.
ON DELETE RESTRICT | NO ACTION won't let me delete the master
CASCADE | SET NULL | SET DEFAULT will modify the details
Any idea ow to do it without the use of triggers?
DROP the constraint. For example:
test=# create table master (id serial primary key, m text);
NOTICE: CREATE TABLE will create implicit sequence master_id_seq for serial
column master.id
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index master_pkey
for table master
CREATE TABLE
test=*# insert into master (m) values ('master1');
INSERT 0 1
test=*# insert into master (m) values ('master2');
INSERT 0 1
test=*# create table slave (id int references master, s text);
CREATE TABLE
test=*# insert into slave values (1, 'slave 1');
INSERT 0 1
test=*# insert into slave values (2, 'slave 2');
INSERT 0 1
test=*# \d slave
Tabelle »public.slave«
Spalte | Typ | Attribute
+-+---
id | integer |
s | text|
Fremdschlüssel-Constraints:
»slave_id_fkey« FOREIGN KEY (id) REFERENCES master(id)
test=*# alter table slave drop constraint slave_id_fkey;
ALTER TABLE
test=*# drop table master;
DROP TABLE
test=*# select * from slave;
id |s
+-
1 | slave 1
2 | slave 2
(2 Zeilen)
test=*#
Hope that helps, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
On 17/07/2008 17:11, Ismael wrote: ON DELETE RESTRICT | NO ACTION won't let me delete the master CASCADE | SET NULL | SET DEFAULT will modify the details Can you just drop the constraint that's doing the referential integrity? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Because I *still* need to verify that NEW records in the details table direct to something that exists ON DELETE RESTRICT | NO ACTION won't let me delete the master CASCADE | SET NULL | SET DEFAULT will modify the details Can you just drop the constraint that's doing the referential integrity? Ray. Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. -Doug _ PlugPlay te trae en exclusiva los mejores conciertos de la red http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
On Thu, Jul 17, 2008 at 12:11 PM, Ismael [EMAIL PROTECTED] wrote: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] query optimization
What does the output of explain select sec.* ... have to say? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
On Thu, Jul 17, 2008 at 10:52 AM, Douglas McNaught [EMAIL PROTECTED] wrote: On Thu, Jul 17, 2008 at 12:11 PM, Ismael [EMAIL PROTECTED] wrote: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. Generally I'd agree, but it might be useful to set the child record referencing id field to NULL in this circumstance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
It seems like 3 vs 1 so you win :) I'll drop the constraint and verify the integrity of the new records manually tanks On Thu, Jul 17, 2008 at 10:52 AM, Douglas McNaught wrote: On Thu, Jul 17, 2008 at 12:11 PM, Ismael wrote: hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. Generally I'd agree, but it might be useful to set the child record referencing id field to NULL in this circumstance. _ Tenemos lo que búscas…JUEGOS. http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] TODO list and hyphen
Hi, I was looking at the TODO: http://www.postgresql.org/docs/faqs.TODO.html A hyphen, -, marks changes that will appear in the upcoming 8.4 release. Well, making a search for the - sign is complicated... it's obviously used for a lot of other things... could you use another character? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] postmaster.pid not visible
Decibel! [EMAIL PROTECTED] writes: Moving to -general. -hackers is for discussion about PG development. On Jul 16, 2008, at 1:10 AM, cinu wrote: Can anyone throw light on why the postmaster.pid was not visible, My guess would be that something went in and removed the .pid file. I was wondering if the postmaster was running in some other data directory than the OP thought. I've never heard of anything just randomly removing a .pid file from a data directory; and it would be extremely dangerous if that did happen, since the pidfile is the main interlock against starting two postmasters in the same directory. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
hi I have one of those master-detail relationships here and I need to be able to delete the master but leave the details untouched when you create the table with an FK constraint, use the ON DELETE SET NULL option, or SET DEFAULT. And read the docs on CREATE TABLE: http://www.postgresql.org/docs/8.3/static/sql-createtable.html Then remove the referential integrity constraint, since it's obviously incompatible with your business requirements. Wrong. See above. Unless I misunderstand, then I apologize for the noise -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] TODO list and hyphen
-Allow administrators to safely terminate individual sessions either via an SQL function or SIGTERM Can't you already do this with pg_cancel_backend() or kill child pid? Or is this for something else altogether? -salman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] in query variables....
Hi all, this is from the is there a way file. Is there a way to get the name of the relation or relations in a query from within the query? Like some kind of magic variable...? for example : select *, some_magic_variable from thistable where some_magic_variable would be replaced by thistable. Rhys Peace Love|Live Long Prosper -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
On Thu, 17 Jul 2008, Ismael wrote: So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but making it only prevent the deletions caused by the referential constraints might be difficult. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] postmaster.pid not visible
On Thu, Jul 17, 2008 at 2:43 PM, Tom Lane [EMAIL PROTECTED] wrote: I was wondering if the postmaster was running in some other data directory than the OP thought. I've never heard of anything just randomly removing a .pid file from a data directory; and it would be extremely dangerous if that did happen, since the pidfile is the main interlock against starting two postmasters in the same directory. Maybe somebody's cleanup script was deleting old files in /var/run (which I agree is a really bad idea). -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] deleting the master but not the detail
So is there no other way to do it but to verify the integrity using triggers and drop the referential constraints? Well, you could do something using a before delete trigger on the referencing table that returns NULL to avoid the delete as well, but making it only prevent the deletions caused by the referential constraints might be difficult. That's right, It's easier to verify the existence of the master before inserting something in the details than controlling the way something gets deleted. After all, is only a PERFORM * FROM WHERE NEW. IF NOT FOUND THEN RISE EXCEPTION 'that doesn't exists';... _ PlugPlay te trae en exclusiva los mejores conciertos de la red http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] in query variables....
Hi all, this is from the is there a way file. Is there a way to get the name of the relation or relations in a query from within the query? Like some kind of magic variable...? for example : select *, from thistable where would be replaced by thistable. Rhys Peace Love|Live Long Prosper No as far as I know, but you can create a stored procedure and pass as parameter the table and then create your own query and execute it from within the stored procedure http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html EXECUTE 'UPDATE '|| some_magic_variable || ' SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue); I haven't tried it thought. _ Tenemos lo que búscas…JUEGOS. http://club.prodigymsn.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] in query variables....
Rhys A.D. Stewart escribió: Is there a way to get the name of the relation or relations in a query from within the query? Like some kind of magic variable...? for example : select *, some_magic_variable from thistable where some_magic_variable would be replaced by thistable. select *, tableoid::regclass from thistable -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] autovacuum logging 8.1
Is there a way to disable some of the autovacuum logging? I don't want to see the below log over and over again in the logfile. postgres[85482]: [1-1] : LOG: autovacuum: processing database test_com Thanks, Mike -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] query optimization
Kevin Duffy wrote:
So here are the questions for the PSQL gurus:
Is getsectypekey(‘CFD’) executing for every join (or possible join)
between positions_gsco and security?
Causing a scan of security for every possible join.
Does ‘ (select getsectypekey('CFD') ) ‘ cause the getsectype()
function to be executed once and thus
allowing the index on security to be used.
And of couse ‘5’ makes things simple. The index on security is used.
Am I posting this in the right. If not please help me correct my error
and point me to the correct spot.
Is the function stable or volatile?
As Scott Marlowe suggested, you need to look at the explain results to
find out what the plan is in each case.
klint.
--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350
Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reducing memory usage of insert into select operations?
Redhat 4 postgresql 8.3.3 Memory: 12GB While doing a couple of operations of the type insert into table select from some other table The OS triggered the out of memory killer (oom-killer). After some research and trial/error I found it was the inserts. I see one of the inserts is using up 12GB! How can I reduce the usage? Postgresql.conf settings. shared_buffers = 3GB temp_buffers = 64MB # min 800kB work_mem = 256MB# min 64kB maintenance_work_mem = 1GB Reducing work_mem would help? The table I am selecting from has a few hundred million rows. The table I am inserting into has partitions. I am benchmarking breaking up a large table into smaller partitions. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] TODO list and hyphen
salman wrote: -Allow administrators to safely terminate individual sessions either via an SQL function or SIGTERM Can't you already do this with pg_cancel_backend() or kill child pid? Cancel backend only cancels the current query but later queries are still executed. Kill was never officially supported as a way to terminate a backend, but it will be in 8.4 and there is an SQL function to do it too. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] TODO list and hyphen
Scara Maccai wrote: Hi, I was looking at the TODO: http://www.postgresql.org/docs/faqs.TODO.html A hyphen, -, marks changes that will appear in the upcoming 8.4 release. Well, making a search for the - sign is complicated... it's obviously used for a lot of other things... could you use another character? I think a search for ' -' will show you the items you want. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reducing memory usage of insert into select operations?
On Thu, Jul 17, 2008 at 7:21 PM, Francisco Reyes [EMAIL PROTECTED] wrote: Redhat 4 postgresql 8.3.3 Memory: 12GB While doing a couple of operations of the type insert into table select from some other table The OS triggered the out of memory killer (oom-killer). Is this a 32-bit installation or 64-bit? 3GB of shared_buffers is way too big for a 32-bit setup. -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reducing memory usage of insert into select operations?
On Thu, Jul 17, 2008 at 9:27 PM, Francisco Reyes [EMAIL PROTECTED] wrote: Douglas McNaught writes: Is this a 32-bit installation or 64-bit? 3GB of shared_buffers is way too big for a 32-bit setup. 64-bit. The machine has 12GB of RAM so shared-buffers is about 1/3. Dedicated DB server. Ahh, good. Just wanted to answer the obvious question first. Some people set shared_buffers really high on 32-bit systems and then are surprised when it doesn't work well. It does seem that reducing work_mem might help you, but others on this list are much more expert than I in diagnosing this sort of problem. It would probably be helpful for you to post the EXPLAIN output from your query, so they can see which part of the plan causes the large memory usage. -Doug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reducing memory usage of insert into select operations?
Douglas McNaught writes: It does seem that reducing work_mem might help you, but others on this I reduced it from 256MB to 64MB. It seems it is helping. At 256MB the usage per DB connection instance was upwards of 12GB. At 64MB so far is around 7GB. I just reduced it further to 32MB and see how that works. It would probably be helpful for you to post the EXPLAIN output from Hash Join (cost=712213.57..27293913.33 rows=234402352 width=24) Hash Cond: (coh.id = ids.id) - Hash Join (cost=551387.26..18799378.16 rows=234402352 width=22) Hash Cond: (coh.user_id = ca.user_id) - Seq Scan on customer_original_historical coh (cost=0.00..6702501.40 rows=234402352 width=47) Filter: (yearmo '200703'::bpchar) - Hash (cost=268355.67..268355.67 rows=14637567 width=32) - Seq Scan on cards ca (cost=0.00..268355.67 rows=14637567 width=32) - Hash (cost=77883.25..77883.25 rows=5055525 width=6) - Seq Scan on customer_ids ids (cost=0.00..77883.25 rows=5055525 width=6) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Reducing memory usage of insert into select operations?
Douglas McNaught writes: Is this a 32-bit installation or 64-bit? 3GB of shared_buffers is way too big for a 32-bit setup. 64-bit. The machine has 12GB of RAM so shared-buffers is about 1/3. Dedicated DB server. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
