Re: [GENERAL] Memory Issue with array_agg?
Hello please, can you send some example or test? Regards Pavel Stehule 2013/8/19 Robert Sosinski rsosin...@ticketevolution.com When using array_agg on a large table, memory usage seems to spike up until Postgres crashes with the following error: 2013-08-17 18:41:02 UTC [2716]: [2] WARNING: terminating connection because of crash of another server process 2013-08-17 18:41:02 UTC [2716]: [3] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2013-08-17 18:41:02 UTC [2716]: [4] HINT: In a moment you should be able to reconnect to the database and repeat your command. I've definitely isolated it down to using array_agg, as when I changed the query to use string_agg, it worked fine. I also tried using array_agg on a few different queries, all yielding the same issue. Swapping in string_agg fixed the issue once more. This particular table has over 2 million rows and is 1.2 Gigs, and when I ran the query while viewing htop, the virtual size of the Postgres process ballooned to 13.9G until crashing. The version of Postgres I am using is: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.2-2ubuntu1) 4.7.2, 64-bit Any help would be much appreciated, thanks! -Robert
Re: [GENERAL] Denormalized field
On 08/18/2013 05:56 AM, Robert James wrote: I have a slow_function. My table has field f, and since slow_function is slow, I need to denormalize and store slow_function(f) as a field. What's the best way to do this automatically? Can this be done with triggers? (On UPDATE or INSERT, SET slow_function_f = slow_function(new_f) ) Yes, I would use a trigger for this. How? Like so: alter table t add column slow_function_f datatype; update t set slow_function_f = slow_function(f); create function slow_function_trigger() returns trigger as $$ begin new.slow_function_f = slow_function(new.f); return new; end; $$ language plpgsql; create trigger slow_function_trigger before insert or update of f, slow_function_f on t for each row execute procedure slow_function_trigger(); Note: I wrote this directly in my mail client so there might be an error or two. Will creating an index on slow_function(f) do this? No, creating an index won't do all that for you. And now you should just create the index on t.slow_function_f, not on slow_function(t.f). -- Vik -- 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] Denormalized field
On Sun, Aug 18, 2013 at 5:56 AM, Robert James srobertja...@gmail.com wrote: What's the best way to do this automatically? Can this be done with triggers? (On UPDATE or INSERT, SET slow_function_f = slow_function(new_f) ) How? Define a before trigger that updates your column. For instance: CREATE OR REPLACE FUNCTION f_trigger() RETURNS TRIGGER AS $$ BEGIN NEW.f_field := f_function( NEW.pk ); RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER tr_foo BEFORE INSERT OR UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE f_trigger(); Of course, adjust the trigger and the trigger function to check against some conditions (e.g., insert, update, nulls). Will creating an index on slow_function(f) do this? You can create the index on the function result, assuming it is immutable. Luca -- 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 on a record variable
Hi Janek,
Hi,
ok :)
I suppose you have a table 'table' with 'col' (text), 'dede' (text) and
'vectors' (tsvector) as fields. In this case, you can do
SELECT levenshtein(col, 'string') FROM table AS lev WHERE
levenshtein(col, 'string') 10 AND LENGTH(dede) BETWEEN x AND y AND
plainto_tsquery('string') @@ vectors;
Hope it can help.
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.brocc...@2ndquadrant.it | www.2ndQuadrant.it
--
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] Memory Issue with array_agg?
Hi Pavel,
What kind of example do you need? I cant give you the actual data I have
in the table, but I can give you an example query and the schema attached
below. From there, I would just put in 2 million rows worth 1.2 Gigs of
data. Average size of the the extended columns (using the pg_column_size
function) in bytes are:
guid: 33
name: 2.41
currency: 4
fields: 120.32
example query:
-- find duplicate records using a guid
select guid, array_agg(id) from orders group by guid;
example schema:
Table public.things
Column |Type | Modifiers
| Storage | Stats target | Description
+-+-+--+--+-
id | integer | not null default
nextval('things_id_seq'::regclass) | plain| |
version| integer | not null
| plain| |
created_at | timestamp without time zone | not null
| plain| |
updated_at | timestamp without time zone | not null
| plain| |
foo_id | integer | not null
| plain| |
bar_id | integer | not null
| plain| |
baz_id | integer | not null
| plain| |
guid | character varying | not null
| extended | |
name | character varying | not null
| extended | |
price | numeric(12,2) | not null
| main | |
currency | character varying | not null
| extended | |
amount | integer | not null
| plain| |
the_date | date| not null
| plain| |
fields | hstore |
| extended | |
Indexes:
things_pkey PRIMARY KEY, btree (id)
things_foo_id_idx btree (foo_id)
things_bar_id_idx btree (bar_id)
things_baz_id_idx btree (baz_id)
things_guid_uidx UNIQUE, btree (guid)
things_lpad_lower_name_eidx btree (lpad(lower(name::text), 10,
'0'::text))
things_price_idx btree (price)
Foreign-key constraints:
things_foo_id_fkey FOREIGN KEY (foo_id) REFERENCES foos(id)
things_bar_id_fkey FOREIGN KEY (bar_id) REFERENCES bars(id)
things_baz_id_fkey FOREIGN KEY (baz_id) REFERENCES bazs(id)
Triggers:
timestamps_trig BEFORE INSERT OR UPDATE ON things FOR EACH ROW EXECUTE
PROCEDURE timestamps_tfun()
Let me know if you need anything else.
Thanks,
On Mon, Aug 19, 2013 at 3:29 AM, Pavel Stehule pavel.steh...@gmail.comwrote:
Hello
please, can you send some example or test?
Regards
Pavel Stehule
2013/8/19 Robert Sosinski rsosin...@ticketevolution.com
When using array_agg on a large table, memory usage seems to spike up
until Postgres crashes with the following error:
2013-08-17 18:41:02 UTC [2716]: [2] WARNING: terminating connection
because of crash of another server process
2013-08-17 18:41:02 UTC [2716]: [3] DETAIL: The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-08-17 18:41:02 UTC [2716]: [4] HINT: In a moment you should be able
to reconnect to the database and repeat your command.
I've definitely isolated it down to using array_agg, as when I changed
the query to use string_agg, it worked fine. I also tried using array_agg
on a few different queries, all yielding the same issue. Swapping in
string_agg fixed the issue once more.
This particular table has over 2 million rows and is 1.2 Gigs, and when I
ran the query while viewing htop, the virtual size of the Postgres
process ballooned to 13.9G until crashing.
The version of Postgres I am using is: PostgreSQL 9.2.4 on
x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.2-2ubuntu1)
4.7.2, 64-bit
Any help would be much appreciated, thanks!
-Robert
Re: [GENERAL] Memory Issue with array_agg?
2013/8/19 Robert Sosinski rsosin...@ticketevolution.com
Hi Pavel,
What kind of example do you need? I cant give you the actual data I have
in the table, but I can give you an example query and the schema attached
below. From there, I would just put in 2 million rows worth 1.2 Gigs of
data. Average size of the the extended columns (using the pg_column_size
function) in bytes are:
guid: 33
name: 2.41
currency: 4
fields: 120.32
example query:
-- find duplicate records using a guid
select guid, array_agg(id) from orders group by guid;
how much distinct guid is there, and how much duplicates
??
regards
Pavel
example schema:
Table public.things
Column |Type | Modifiers
| Storage | Stats target | Description
+-+-+--+--+-
id | integer | not null default
nextval('things_id_seq'::regclass) | plain| |
version| integer | not null
| plain| |
created_at | timestamp without time zone | not null
| plain| |
updated_at | timestamp without time zone | not null
| plain| |
foo_id | integer | not null
| plain| |
bar_id | integer | not null
| plain| |
baz_id | integer | not null
| plain| |
guid | character varying | not null
| extended | |
name | character varying | not null
| extended | |
price | numeric(12,2) | not null
| main | |
currency | character varying | not null
| extended | |
amount | integer | not null
| plain| |
the_date | date| not null
| plain| |
fields | hstore |
| extended | |
Indexes:
things_pkey PRIMARY KEY, btree (id)
things_foo_id_idx btree (foo_id)
things_bar_id_idx btree (bar_id)
things_baz_id_idx btree (baz_id)
things_guid_uidx UNIQUE, btree (guid)
things_lpad_lower_name_eidx btree (lpad(lower(name::text), 10,
'0'::text))
things_price_idx btree (price)
Foreign-key constraints:
things_foo_id_fkey FOREIGN KEY (foo_id) REFERENCES foos(id)
things_bar_id_fkey FOREIGN KEY (bar_id) REFERENCES bars(id)
things_baz_id_fkey FOREIGN KEY (baz_id) REFERENCES bazs(id)
Triggers:
timestamps_trig BEFORE INSERT OR UPDATE ON things FOR EACH ROW EXECUTE
PROCEDURE timestamps_tfun()
Let me know if you need anything else.
Thanks,
On Mon, Aug 19, 2013 at 3:29 AM, Pavel Stehule pavel.steh...@gmail.comwrote:
Hello
please, can you send some example or test?
Regards
Pavel Stehule
2013/8/19 Robert Sosinski rsosin...@ticketevolution.com
When using array_agg on a large table, memory usage seems to spike up
until Postgres crashes with the following error:
2013-08-17 18:41:02 UTC [2716]: [2] WARNING: terminating connection
because of crash of another server process
2013-08-17 18:41:02 UTC [2716]: [3] DETAIL: The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2013-08-17 18:41:02 UTC [2716]: [4] HINT: In a moment you should be able
to reconnect to the database and repeat your command.
I've definitely isolated it down to using array_agg, as when I changed
the query to use string_agg, it worked fine. I also tried using array_agg
on a few different queries, all yielding the same issue. Swapping in
string_agg fixed the issue once more.
This particular table has over 2 million rows and is 1.2 Gigs, and when
I ran the query while viewing htop, the virtual size of the Postgres
process ballooned to 13.9G until crashing.
The version of Postgres I am using is: PostgreSQL 9.2.4 on
x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.2-2ubuntu1)
4.7.2, 64-bit
Any help would be much appreciated, thanks!
-Robert
[GENERAL] thank you
Ya i got the answer here is the code SELECT * FROM (SELECT row_number() over(), * FROM employee) t1 right outer JOIN (SELECT row_number() over(), * FROM managers) t2 on t1.row_number=t2.row_number Thank you -- View this message in context: http://postgresql.1045698.n5.nabble.com/Here-is-my-problem-tp5766954p5767787.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Memory Issue with array_agg?
At the moment, all guids are distinct, however before I zapped the
duplicates, there were 280 duplicates.
Currently, there are over 2 million distinct guids.
-Robert
On Mon, Aug 19, 2013 at 11:12 AM, Pavel Stehule pavel.steh...@gmail.comwrote:
2013/8/19 Robert Sosinski rsosin...@ticketevolution.com
Hi Pavel,
What kind of example do you need? I cant give you the actual data I have
in the table, but I can give you an example query and the schema attached
below. From there, I would just put in 2 million rows worth 1.2 Gigs of
data. Average size of the the extended columns (using the pg_column_size
function) in bytes are:
guid: 33
name: 2.41
currency: 4
fields: 120.32
example query:
-- find duplicate records using a guid
select guid, array_agg(id) from orders group by guid;
how much distinct guid is there, and how much duplicates
??
regards
Pavel
example schema:
Table public.things
Column |Type |
Modifiers | Storage | Stats target | Description
+-+-+--+--+-
id | integer | not null default
nextval('things_id_seq'::regclass) | plain| |
version| integer | not null
| plain| |
created_at | timestamp without time zone | not null
| plain| |
updated_at | timestamp without time zone | not null
| plain| |
foo_id | integer | not null
| plain| |
bar_id | integer | not null
| plain| |
baz_id | integer | not null
| plain| |
guid | character varying | not null
| extended | |
name | character varying | not null
| extended | |
price | numeric(12,2) | not null
| main | |
currency | character varying | not null
| extended | |
amount | integer | not null
| plain| |
the_date | date| not null
| plain| |
fields | hstore |
| extended | |
Indexes:
things_pkey PRIMARY KEY, btree (id)
things_foo_id_idx btree (foo_id)
things_bar_id_idx btree (bar_id)
things_baz_id_idx btree (baz_id)
things_guid_uidx UNIQUE, btree (guid)
things_lpad_lower_name_eidx btree (lpad(lower(name::text), 10,
'0'::text))
things_price_idx btree (price)
Foreign-key constraints:
things_foo_id_fkey FOREIGN KEY (foo_id) REFERENCES foos(id)
things_bar_id_fkey FOREIGN KEY (bar_id) REFERENCES bars(id)
things_baz_id_fkey FOREIGN KEY (baz_id) REFERENCES bazs(id)
Triggers:
timestamps_trig BEFORE INSERT OR UPDATE ON things FOR EACH ROW
EXECUTE PROCEDURE timestamps_tfun()
Let me know if you need anything else.
Thanks,
On Mon, Aug 19, 2013 at 3:29 AM, Pavel Stehule
pavel.steh...@gmail.comwrote:
Hello
please, can you send some example or test?
Regards
Pavel Stehule
2013/8/19 Robert Sosinski rsosin...@ticketevolution.com
When using array_agg on a large table, memory usage seems to spike up
until Postgres crashes with the following error:
2013-08-17 18:41:02 UTC [2716]: [2] WARNING: terminating connection
because of crash of another server process
2013-08-17 18:41:02 UTC [2716]: [3] DETAIL: The postmaster has
commanded this server process to roll back the current transaction and
exit, because another server process exited abnormally and possibly
corrupted shared memory.
2013-08-17 18:41:02 UTC [2716]: [4] HINT: In a moment you should be
able to reconnect to the database and repeat your command.
I've definitely isolated it down to using array_agg, as when I changed
the query to use string_agg, it worked fine. I also tried using array_agg
on a few different queries, all yielding the same issue. Swapping in
string_agg fixed the issue once more.
This particular table has over 2 million rows and is 1.2 Gigs, and when
I ran the query while viewing htop, the virtual size of the Postgres
process ballooned to 13.9G until crashing.
The version of Postgres I am using is: PostgreSQL 9.2.4 on
x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.7.2-2ubuntu1)
4.7.2, 64-bit
Any help would be much appreciated, thanks!
-Robert
Re: [GENERAL] Select performance variation based on the different combinations of using where lower(), order by, and limit
On Sun, Aug 18, 2013 at 4:46 PM, Tyler Reese juke...@gmail.com wrote: I haven't heard of raising the statistics target, so I'll read up on that. A few days ago, all 4 cases were responding equally fast. I had been messing around with the postgres settings, and I went and dropped all of the indexes and recreated them just to see what would happen. I wouldn't think that recreating the indexes would cause case 4 to go slow, but that's the symptom I am seeing now. Should I be running analyze on a table after it has been reindexed? PostgreSQL keeps statistics on the table's columns with the table, and they survive a re-index. But the column used by the function-based index is not a real table column. Those statistics are kept with the index, not the table, and they do not survive the re-index. So you should analyze the table in order to reacquire those statistics. Since the problem is that you no longer had statistics at all for that column, there is probably no need to increase the statistics target, just doing the analyze should get you back in business. Arguably PostgreSQL's autovacuum logic should be better about dealing with expression-based indices. But for now, a manual analyze is needed when a new expression-based index is created, or when an existing one is re-indexed. Cheers, Jeff -- 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] please suggest i need to test my upgrade
On Wed, Aug 14, 2013 at 7:15 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote: This is the first thing that comes to mind: http://petereisentraut.blogspot.co.at/2008/03/readding-implicit-casts-in-postgresql.html But you may encounter other incompatibilities. Read the release nots of all major releases between 8.2 and 9.1, but most of all, test your application well. Our application has moved from Pg 7.1 thru various releases over the years to the current 9.2. The implicit cast issue and the default representation of bytea columns are the only thing that have required our application to change. Any reason you're not moving to 9.2 instead of 9.1?
[GENERAL] Create a deferrably-unique index
I'm trying to create a unique index where the unique constraint is `deferrable initially immediate`. But I don't see any way to do this in the syntax of the `create index` command. It looks like the only way to do it is via `alter table foo add unique`. Is that right, or can I do it as part of `create index`? If I have to use `alter table add unique`, is there any way I can make sure the implicitly-created index also has a `where` clause? Or is it impossible to create an index that is unique + deferrable + partial? Thank you, Paul -- _ Pulchritudo splendor veritatis. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] AccessShareLock on pg_authid
Hi, Processes are failing due to the following error on Postgresql 8.3.5: FATAL: lock AccessShareLock on object 0/1260/0 is already held 1260 oid belongs to pg_authid. This error is not coming for every transaction. I have found these two links related to the above error but not quite helpful: http://web.archiveorange.com/archive/v/alpsnhads7a0RcdRyB5t http://www.postgresql.org/message-id/7386.1315434...@sss.pgh.pa.us Has anyone come across this issue and debugged it successfully? Thanks Regards, GB
Re: [GENERAL] Create a deferrably-unique index
Paul Jungwirth p...@illuminatedcomputing.com writes: I'm trying to create a unique index where the unique constraint is `deferrable initially immediate`. But I don't see any way to do this in the syntax of the `create index` command. It looks like the only way to do it is via `alter table foo add unique`. Is that right, or can I do it as part of `create index`? Deferrability is a property of a constraint, not an index, so you can only specify it for indexes that are associated with constraints. Yes, that limits the kinds of indexes that can be used ... 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] Create a deferrably-unique index
Deferrability is a property of a constraint, not an index Yes, but creating a unique constraint implicitly creates an index, and creating a unique index implicitly creates a constraint. So I'm wondering whether I can create a pair where the index is partial and the constraint is deferrable. It sounds like the answer is no? Is there a workaround where I first create one and then alter the other one? Thanks, Paul -- _ Pulchritudo splendor veritatis. -- 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] Create a deferrably-unique index
Paul Jungwirth p...@illuminatedcomputing.com writes: Deferrability is a property of a constraint, not an index Yes, but creating a unique constraint implicitly creates an index, and creating a unique index implicitly creates a constraint. No, it doesn't. I'm using constraint in a technical sense here, that is something that is recorded as a constraint in the system catalogs. regression=# select count(*) from pg_constraint; count --- 34 (1 row) regression=# create table foo(f1 int unique); CREATE TABLE regression=# select count(*) from pg_constraint; count --- 35 (1 row) regression=# create table bar(f1 int); CREATE TABLE regression=# create unique index on bar(f1); CREATE INDEX regression=# select count(*) from pg_constraint; count --- 35 (1 row) The index on bar didn't create a constraint. 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] Denormalized field
On Mon, Aug 19, 2013 at 4:27 AM, Vik Fearing vik.fear...@dalibo.com wrote: Yes, I would use a trigger for this. snip This is definitely the right answer, but keep in mind that this will slow down your inserts since it calls slow_function for each insert. Make sure you can afford that performance hit.
