Re: [GENERAL] UNLOGGED TEMPORARY tables?

temp_buffers = 16MB -- View this message in context: http://postgresql.1045698.n5.nabble.com/UNLOGGED-TEMPORARY-tables-tp5749477p5749656.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make c

Re: [GENERAL] UNLOGGED TEMPORARY tables?

New test with temporary table and unlogged Total rows 600k Table size after copy 121MB temp_buffers / copy speed 16MB - 12999 rows/s 128MB - 13005 rows/s 256MB - 13258 rows/s 512MB - 13399 rows/s 1GB - 13145 rows/s Unlogged table - 1 rows/s I don't undestand why previous test showed the d

[GENERAL] UNLOGGED TEMPORARY tables?

I was tested write speed to temporary and unlogged tables and noticed that unlogged tables was a much faster Postgres 9.2.2 Write speed Temporary 14.5k/s UNLOGGED 50k/s Before test I was convinced that temporary tables in postgres >= 9.1 are unlogged -- View this message in context: http:

[GENERAL] SP-GiST indexing for box types?

Can Postgres support SP-GiST indexes on box type? when I try create index I got error ERROR: data type box has no default operator class for access method "spgist" -- View this message in context: http://postgresql.1045698.n5.nabble.com/SP-GiST-indexing-for-box-types-tp5743798.html Sent from

Re: [GENERAL] How to store clickmap points?

I finally store points in structure with arrays, and pack it once at day. create type t_point as ( x smallint, y smallint, hits integer ); CREATE TABLE clickmap ( page_id integer, date date, points t_point[] ); This method save 6x more space than previous Thanks for all! -

Re: [GENERAL] How to store clickmap points?

> Instead of storing x/y, have you considered referencing a region of pixels? The bigger the region, the larger your possible savings. Good idea, but I don't always have all points and regions will not be fully filled -- View this message in context: http://postgresql.1045698.n5.nabble.com/Ho

Re: [GENERAL] How to store clickmap points?

> convert it into a heatmap at the end of each day. How to do it with Postgresql? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-store-clickmap-points-tp5739121p5740076.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via

Re: [GENERAL] Bug: dblink_send_query not work on 9.2?

Thank you! It's now works perfectly! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Bug-dblink-send-query-not-work-on-9-2-tp5739365p5739526.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-gene

[GENERAL] Bug: dblink_send_query not work on 9.2?

I have problem with dblink_send_query function on Postgres 9.2.2 This work do $$ begin perform dblink.dblink_connect('internal'); perform dblink.dblink_exec('internal', 'set application_name=''dblink'';', true); perform dblink.dblink_disconnect(); end; $$ But this not do $$ begin perf

[GENERAL] How to store clickmap points?

Hi, I want to store clickmap points (X, Y and hits value) for website I currently have table like this CREATE TABLE clickmap ( page_id integer, date date, x smallint, y smallint, hits integer ) But this generated about 1M rows per day. Can Postgres have better method to store this d

Re: [GENERAL] How to remove n-first elements from array?

I use it for page hits aggregations, It's very fast, my arrays contains no more than 1000 elements (array_upper-array_lower <=1000) and array bounds is mapped to time. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-remove-n-first-elements-from-array-tp5736765p

Re: [GENERAL] How to remove n-first elements from array?

Thank you! More general, but that lose bounds of array Result is NOTICE: {3,4,5} but I want NOTICE: [3:5]{3,4,5} -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-remove-n-first-elements-from-array-tp5736765p5736894.html Sent from the PostgreSQL - genera

Re: [GENERAL] problem with large inserts

First try put inserts to temporary table, a then insert from temporary to your table -- View this message in context: http://postgresql.1045698.n5.nabble.com/problem-with-large-inserts-tp5736844p5736867.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via p

Re: [GENERAL] How to remove n-first elements from array?

I found solution do $$ declare v_array integer[] := array[1,2,3,4,5]; v_array2 integer[]; v_delete_elements integer :=2; v_new_index integer; begin v_new_index := array_lower(v_array, 1) + v_delete_elements; v_array2 := array_fill(v_array[v_new_index], ARRAY[1], ARRAY[v_new_index])

[GENERAL] The fastes way to sum array of integers

Hi, I sum values of array integers by query select sum(t) from unnest(array[1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0]) as t cross join generate_series(1, 100); Total runtime: 10020.699 ms But simmilar method to count array's elements is dramatically faster select sum(array_length(

[GENERAL] How to remove n-first elements from array?

Hi, I have array for example do $$ declare v_arr integer[] := array[1, 2, 3, 4, 5]; begin end; $$ I want to remove 2 first element and get array with non-changed indexes (subscript) new array := '[3:5]={3, 4, 5}'::integer[] It is possible in pl/pgsql? -- View this message in context: htt

[GENERAL] fetch from cursor in pg_stats_activity and related SQL query

Hi, Is it possible to get information about SQL query for given session? The view pg_stats_activity shows only "FETCH ALL IN " Thanks Regards aasat -- View this message in context: http://postgresql.1045698.n5.nabble.com/fetch-from-cursor-in-pg-stats-activity-and-related

[GENERAL] Re: Is there a way to ask PostgreSQL for the name of the computer it's running on?

CREATE OR REPLACE FUNCTION get_hostname() RETURNS text AS $BODY$use Sys::Hostname; return hostname;$BODY$ LANGUAGE plperlu VOLATILE; -- View this message in context: http://postgresql.1045698.n5.nabble.com/Is-there-a-way-to-ask-PostgreSQL-for-the-name-of-the-computer-it-s-running-on-tp571253

Re: [GENERAL] SELECT INTO create new table, how to turn off this feature?

Sorry, my mistake, Postgres 9.0 for unexists variables throwing error "myvariable" is not a known variable -- View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-INTO-create-new-table-how-to-turn-off-this-feature-tp5713267p5713279.html Sent from the PostgreSQL - general m

[GENERAL] SELECT INTO create new table, how to turn off this feature?

Hi, Is it possible to turn off future "SELECT INTO" that create new table? I use it on pl/pgsql function, and I have mamy mistakes with variable name in SQL thanks Wojtek -- View this message in context: http://postgresql.1045698.n5.nabble.com/SELECT-INTO-create-new-table-how-to-turn-off-this-

[GENERAL] Fetch from refcursor and transactions

Hi, I have question about fetching data from refcursor, why this operation required to be inside transaction? for example with transaction begin; select * from foo(); fetch all from ""; commit; when I try this without transaction I get error select * from foo(); fetch all from ""; ERROR: