[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,

[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 lists

[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_ip

[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

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= CREATE

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] 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

[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

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 proved to issue

[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

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 is equal to a value

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 check such that 1 field

[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

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, George

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

[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

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 to populate a table such that it only contains the unique rows, all

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 insert into a database. Each entry consists of: INT BOOLEAN INT BOOLEAN I

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 things