[GENERAL] help optimizing query

Hi all, I'm looking for helping optimizing a query. It currently requires two passes on the data per query, when I'd like to try and write it to only require a single pass. Here's the high level, it's parsing flow level network traffic and it's interested in 4 fields: src_ip, dst_ip, src_p

[GENERAL] foreign key constraints with inhertiance, hack suggestions?

Hi, First, yes I have read the "5.8.1. Caveats" section that this support does not exist. I agree with the document that this is "a serious limitation of the inheritance feature" Has there been any effort to support this in the near future versions of postgresql? I searched the mailing list

Re: [GENERAL] get username of user calling function?

David Legault wrote: See the EXECUTE function in the pl/pgSQL language in the docs for dynamic queries. So it turns out that in a SECURITY DEFINER the current_user is the owner of the function. I had to use session_user and it works now :) - George ---(end of bro

Re: [GENERAL] get username of user calling function?

A. Kretschmer wrote: You can use the current_user - variable. Select current_user; I'm trying to create a function in which users can only kill their own processes, it works perfectly if i hardcode a username in such as this: CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select

Re: [GENERAL] creating a function with a variable table name

do I need to use PREPARE with it also? A. Kretschmer wrote: am Thu, dem 01.03.2007, um 11:17:46 -0500 mailte George Nychis folgendes: Hey all, I'm trying to create a function in which the table a query is run on is variable, but I guess this is not as easy as I thought. BEGIN dp=>

[GENERAL] get username of user calling function?

Hi, Is it possible to get the username of the user calling a function? Just as a test, a function which would return the user their username. Thanks! George ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropr

[GENERAL] creating a function with a variable table name

Hey all, I'm trying to create a function in which the table a query is run on is variable, but I guess this is not as easy as I thought. BEGIN dp=> CREATE FUNCTION stats_addr_dst(date,text) dp-> RETURNS setof addr_count dp-> AS 'SELECT ip,sum(dst_packets) dp'> FROM( dp'> (SELECT dst_

[GENERAL] giving a user permission to kill their processes only

Hey all, So the pg_cancel_backend() function by default is only available to super users, so I decided to write a wrapper function around, use a SECURITY DEFINER, and GRANT my user privilege to use the wrapper. BEGIN; CREATE FUNCTION kill_process(integer) RETURNS boolean AS 'select pg_cancel_

Re: [GENERAL] performance of partitioning?

George Nychis wrote: cedric wrote: Le mardi 27 février 2007 15:00, George Nychis a écrit : Hey all, So I have a master table called "flows" and 400 partitions in the format "flow_*" where * is equal to some epoch. Each partition contains ~700,000 rows and has a chec

Re: [GENERAL] performance of partitioning?

cedric wrote: Le mardi 27 février 2007 15:00, George Nychis a écrit : Hey all, So I have a master table called "flows" and 400 partitions in the format "flow_*" where * is equal to some epoch. Each partition contains ~700,000 rows and has a check such that 1 field

[GENERAL] performance of partitioning?

Hey all, So I have a master table called "flows" and 400 partitions in the format "flow_*" where * is equal to some epoch. Each partition contains ~700,000 rows and has a check such that 1 field is equal to a value: "flows_1107246900_interval_check" CHECK ("interval" = '2005-02-01 03:35:0

Re: [GENERAL] dropping a master table and all of its partitions?

Tom Lane wrote: George Nychis <[EMAIL PROTECTED]> writes: Here is an exact script which generates this every single time... After you're done running the ruby script: DROP TABLE testflows CASCADE; I tweaked the ruby script to emit the SQL commands into a script file, which prov

Re: [GENERAL] dropping a master table and all of its partitions?

: George Nychis wrote: Sure I can do that, but why is this happening? Is this normal behavior? Well that is the better question. If it is indeed doing what you say it is doing, I would say it is a bug. However you have not mentioned several important items, like what postgresql version you are

Re: [GENERAL] dropping a master table and all of its partitions?

Sure I can do that, but why is this happening? Is this normal behavior? - George Erik Jones wrote: Did you use some kind of sensical naming convention for the child tables? If so, couldn't you write a script to loop through and drop them one at a time? On Feb 26, 2007, at 6:42 PM, G

[GENERAL] dropping a master table and all of its partitions?

Hey everyone, I created a master table, and created ~2000 partitions for it. *no* data is in any of these partitions. I am trying to drop the master and all of the partitions with a cascade: DROP TABLE master CASCADE; Except after about 30 seconds my memory usage (4GB) jumps to 99%, and after

Re: [GENERAL] insert only unique values in to a table, ignore rest?

Jeremy Haile wrote: >> Note that things will go faster if you do your initial data load using >> "copy from stdin" for the initial bulk data load. individual inserts in >> postgresql are quite costly compared to mysql. It's the transactional >> overhead. by grouping them together you can make

Re: [GENERAL] insert only unique values in to a table, ignore rest?

Scott Marlowe wrote: > On Mon, 2007-01-08 at 15:52, George Nychis wrote: >> Scott Marlowe wrote: >>> On Mon, 2007-01-08 at 14:58, George Nychis wrote: >>>> Hi, >>>> >>>> I have approximately 2 billion data entries that I would like to ins

Re: [GENERAL] insert only unique values in to a table, ignore rest?

Scott Marlowe wrote: > On Mon, 2007-01-08 at 14:58, George Nychis wrote: >> Hi, >> >> I have approximately 2 billion data entries that I would like to insert into >> a database. >> Each entry consists of: >> INT BOOLEAN INT BOOLEAN >> >> I want

[GENERAL] insert only unique values in to a table, ignore rest?

Hi, I have approximately 2 billion data entries that I would like to insert into a database. Each entry consists of: INT BOOLEAN INT BOOLEAN I want to populate a table such that it only contains the unique rows, all other data should be thrown out. I would say a significant amount of the inser