Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

169739c01d4525027c087d14 And also this query: SELECT name,version,source FROM pg_settings WHERE source NOT IN ('default','override'); -- Victor Yegorov

Re: [GENERAL] core system is getting unresponsive because over 300 cpu load

ou can find more details in this post: http://blog.postgresql- consulting.com/2017/03/deep-dive-into-postgres-stats_27.html (You might want to reset 'shared' stats here.) -- Victor Yegorov

Re: [GENERAL] Converting inherited partitions into declarative ones

2017-09-29 20:32 GMT+03:00 Victor Yegorov <vyego...@gmail.com>: > > Is it possible to avoid Full Scan here? I have TBs worth of data in > partitions, > so it'll takes ages to switch to the declarative partitioning the way > things stand now. > OK, looking at the sou

[GENERAL] Converting inherited partitions into declarative ones

ning the way things stand now. Thanks in advance. -- Victor Yegorov

Re: [GENERAL] londiste3: removing a node that does not exist

cation WHERE queue_name='q-2rm'; And restarted worker after that. -- Victor Yegorov

Re: [GENERAL] Limit Heap Fetches / Rows Removed by Filter in Index Scans

On Sat, Aug 20, 2016 at 1:13 AM, Francisco Olarte <fola...@peoplecall.com> wrote: > Hi Victor: > > > On Fri, Aug 19, 2016 at 7:02 PM, Victor Blomqvist <v...@viblo.se> wrote: > > What I want to avoid is my query visiting the whole 1m rows to get a > result, >

Re: [GENERAL] Limit Heap Fetches / Rows Removed by Filter in Index Scans

On Fri, Aug 19, 2016 at 6:01 PM, Francisco Olarte <fola...@peoplecall.com> wrote: > Hi Victor: > > On Fri, Aug 19, 2016 at 7:06 AM, Victor Blomqvist <v...@viblo.se> wrote: > > Is it possible to break/limit a query so that it returns whatever results > > found afte

Re: [GENERAL] Limit Heap Fetches / Rows Removed by Filter in Index Scans

On Fri, Aug 19, 2016 at 1:31 PM, Sameer Kumar <sameer.ku...@ashnik.com> wrote: > > > On Fri, 19 Aug 2016, 1:07 p.m. Victor Blomqvist, <v...@viblo.se> wrote: > >> Hi, >> >> Is it possible to break/limit a query so that it returns whatever results >

[GENERAL] Limit Heap Fetches / Rows Removed by Filter in Index Scans

ral 100k or more, and in those cases I would like to have a limit to my query. Thanks! /Victor

Re: [GENERAL] Retrieving value of column X days later

hough, as window functions are evaluated after the `WHERE` clause. -- Victor Y. Yegorov

[GENERAL] Re: [GENERAL] ФС против Постгреса для файлов

абор, допустим, одинаковых файликов для ФС и Постгреса. > Отдачу сделать на базе nginx и , мне хотелось, Lua модуля. Что еще > учесть для тестов? > This is an English speaking forum. Please, use `pgsql-ru-general` instead for posts on Russian. -- Victor Y. Yegorov

Re: [GENERAL] Corrupted Dabatabase

You probably have a HDD problem. Try a "cat /proc/mounts" and see if partition is mounted read only. -- Original Message -- From: "Pau Marc Muñoz Torres" To: "pgsql general" Sent: 27/6/2016 4:28:35 PM Subject: [GENERAL] Corrupted

Re: [GENERAL] Ascii Elephant for text based protocols - Final

12178b121715122a172a1b2317d91a172a17f71b1a1912177') ), str AS ( SELECT string_agg(repeat(translate(substr(s, p, 1), '123456789ab', '(/>)<+ o_|\'), ('x'||lpad(substr(s, p+1, 1), 8, '0'))::bit(32)::int), '') line FROM src, generate_series(1, 182, 2) p ) SELECT substr(line, p, 21) slon FROM str, generate_series(1, 189, 21) p; -- Victor Y. Yegorov

Re: [GENERAL] Slow query when the select list is big

some of these parameters, I will try all tomorow. -- Original Message -- From: "Karl Czajkowski" <kar...@isi.edu> To: "Sterpu Victor" <vic...@caido.ro> Cc: "PostgreSQL General" <pgsql-general@postgresql.org> Sent: 9/5/2016 8:47:12 PM Sub

Re: [GENERAL] Slow query when the select list is big

> To: "Sterpu Victor" <vic...@caido.ro> Cc: "Rob Imig" <rimi...@gmail.com>; "PostgreSQL General" <pgsql-general@postgresql.org>; "David G. Johnston" <david.g.johns...@gmail.com> Sent: 9/5/2016 10:04:54 AM Subject: Re: [GENERAL] S

Re: [GENERAL] Slow query when the select list is big

. -- Original Message -- From: "Sterpu Victor" <vic...@caido.ro> To: "Sterpu Victor" <vic...@caido.ro>; "David Rowley" <david.row...@2ndquadrant.com> Cc: "Rob Imig" <rimi...@gmail.com>; "PostgreSQL General" <pg

Re: [GENERAL] Slow query when the select list is big

2016-05-09 11:01 GMT+03:00 Sterpu Victor <vic...@caido.ro>: > I went to 2.4 seconds by joining first the tables that produce many rows. As you're changing your query quite often, it'd be handy, if you could post both: - new query version - it's `EXECUTE (analyze, buffers)` outpu

Re: [GENERAL] Slow query when the select list is big

1.validfrom DESC LIMIT 20 OFFSET 0 -- Original Message -- From: "Sterpu Victor" <vic...@caido.ro> To: "David Rowley" <david.row...@2ndquadrant.com> Cc: "Rob Imig" <rimi...@gmail.com>; "PostgreSQL General" <pgsql-general@postgr

Re: [GENERAL] Slow query when the select list is big

from)<= DATE('2016-05-01') )) ORDER BY J1031101.validfrom DESC LIMIT 20 OFFSET 0 -- Original Message -- From: "David Rowley" <david.row...@2ndquadrant.com> To: "Sterpu Victor" <vic...@caido.ro> Cc: "Rob Imig" <rimi...@gmail.com>; "PostgreS

[GENERAL] Slow query when the select list is big

I have a big query that takes about 7 seconds to run(time sending the data to the client is not counted). Postgres uses 100% of 1 CPU when solving this query. I tried to run the query on a HDD and on a SSD with no difference. HDD show about 10% usage while the query runs. The query has a big

Re: [GENERAL] Get sum of sums

RX Bytes" DESC LIMIT 10; I am not sure bout the LIMIT though, I hope window function will be calculated after the LIMIT is applied. -- Victor Y. Yegorov

Re: [GENERAL] Slow join over three tables

Can you post output of `EXPLAIN (analyze, buffers)`, please? It'd be good to check how many buffers are hit/read during Index Scans. I have had a somewhat similar case, when due to misconfigured autovacuum (not aggressive enough for the increased volume) I got bloat accumulated in the indexes. Perhaps, REINDEX can help here. -- Victor Y. Yegorov

[GENERAL] Types of blocks in the `pg_stat_statements`

` and `hit` also speaks for this). But what `local` blocks mean? -- Victor Y. Yegorov

Re: [GENERAL] Unique values on multiple tables

-- Original Message -- From: "Emre Hasegeli" <e...@hasegeli.com> To: "Sterpu Victor" <vic...@caido.ro> Cc: "PostgreSQL General" <pgsql-general@postgresql.org> Sent: 28/3/2016 12:06:23 PM Subject: Re: [GENERAL] Unique values on multiple

Re: [GENERAL] Unique values on multiple tables

I think I fixed the problem by executing the function AFTER insert or update but I'm not sure. Until now the execution was before insert or update. -- Original Message -- From: "Sterpu Victor" <vic...@caido.ro> To: "PostgreSQL General" <pgsql-general@postgre

[GENERAL] Unique values on multiple tables

Hello I have 2 tables and I must make asure unique values like this. table1 id nr - integer table2 id id_table1 - FK in Table 1 valid_from - timestamp There must be unique values for: - nr - from table1 and - YEAR(MIN(valid_from)) from table 2 I already made this with a function but in rare

Re: [GENERAL] Including SQL files

1 available please and if not - > could you share your approaches here? > If you're processing your files with Postgres' `psql` tool, you can use `\i` directive to include other files. -- Victor Y. Yegorov

Re: [GENERAL] Subtract one array from another, both with non-unique elements

except(ARRAY['A','A','B','B','C'], ARRAY['A','B']); arrexcept --- {A,B,C} (1 row) But it doesn't preserves the order of the elements, not sure if this is important. -- Victor Y. Yegorov

Re: [GENERAL] index problems (again)

your index before it finds the row that fits. Right now (9.5 and earlier versions) I do not know of any options that would not require fixing your queries. P.S. Maybe `Upper pathification` patch, that is being considered for 9.6, can deal with such cases. -- Victor Y. Yegorov

Re: [GENERAL] index problems (again)

gs WHERE scdate BETWEEN 20160219 AND 20160221; ? -- Victor Y. Yegorov

Re: [GENERAL] CONCAT returns null

The problem was from PG Admin that is not displaing cells with a high amount of data. In the application the query is working well. -- Original Message -- From: "Sterpu Victor" <vic...@caido.ro> To: "PostgreSQL General" <pgsql-general@postgresql.org> Sen

[GENERAL] CONCAT returns null

Hello I have this concat: CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom, '-MM-DD'), f2.nrfo, TO_CHAR(fd7.validfrom, '-MM-DD'), DATE(fd5.validto)-DATE(fd1.validfrom)) that works fine but when I change to this(I added a ' with '): ARRAY_AGG(CONCAT(f.nrfo, '/', TO_CHAR(fd1.validfrom,

Re: [GENERAL] bpchar, text and indexes

that to a text value, sn_c has to be casted > to text and then the index (which contains blank padded values) can not be > used any more. > Well, for `varchar` type Postgres is able to do `varchar` -> `bpchar` cast for my constant. I do not understand why for `text` it cannot and casts col

[GENERAL] bpchar, text and indexes

see in `pg_cast`, setup for `varchar` is pretty much the same: `varchar` => `bpchar` is also binary coercible. So why for `varchar` behaviour is different? Thanks in advance. -- Victor Y. Yegorov

Re: [GENERAL] Query plan not updated after dropped index

On Thu, Feb 18, 2016 at 11:05 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Victor Blomqvist <v...@viblo.se> writes: > > We just had a major issue on our databases, after a index was replaced a > > user defined function didnt change its query plan to use the ne

Re: [GENERAL] Query plan not updated after dropped index

The end goal is to get rid of index bloat. If there is a better way to handle this Im all ears! /Victor On Thu, Feb 18, 2016 at 5:21 PM, Oleg Bartunov <obartu...@gmail.com> wrote: > > > On Thu, Feb 18, 2016 at 11:17 AM, Victor Blomqvist <v...@viblo.se> wrote: > >

[GENERAL] Query plan not updated after dropped index

LIMIT 100 OFFSET 0; And this is my modification that made it work again: RETURN QUERY SELECT * FROM table WHERE bigint_column = X AND 1=1 LIMIT 100 OFFSET 0; Obviously we are now worried why this happened and how we can avoid it in the future? We run Postgres 9.3 on CentOS 6. Thanks! Victor

[GENERAL] Windows performance

Hello Why is Postgres so slow on Windows compared to linux? Can I do something to match the performance? I have 2 servers: - one is Windows 8, CPU XEON, 8 CORES, 32G of RAM - my test query runs in 17 seconds - the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs in 2 seconds

Re: [GENERAL] Windows performance

There are some differences that I haven't mentioned. Postgres on Linux is PostgreSQL 9.1.4 64 bit Postgres on Windows is PostgreSQL 9.5.0, compiled by Visual C++ build 1800, 64-bit The query is very big but I pasted it at the end of the mail with the EXPLAIN ANALYZE. I runned the queries many

Re: [GENERAL] A motion

reat value to the -general list. Let things settle down a bit for a while — I'm quite sure list will come back to it's common shape. Please, do not go :) -- Victor Y. Yegorov

Re: [GENERAL] Code of Conduct: Is it time?

inds me of what we do for the GSoC, where developers volunteer for mentoring students. Something similar would be handy in general, perhaps with a web interface similar to the CommitFest's one. -- Victor Y. Yegorov

Re: [GENERAL] Unique index problem

lt;pavel.steh...@gmail.com> To: "Sterpu Victor" <vic...@caido.ro> Cc: "Marc Mamin" <m.ma...@intershop.de>; "PostgreSQL General" <pgsql-general@postgresql.org>; "Andreas Kretschmer" <akretsch...@spamfence.net>; "Scott Marlowe"

Re: [GENERAL] Unique index problem

Thank you. I used the syntax with 2 indexes, it works for me. But why does NULL != NULL? -- Original Message -- From: "Marc Mamin" <m.ma...@intershop.de> To: "Sterpu Victor" <vic...@caido.ro> Cc: "PostgreSQL General" <pgsql-general@pos

[GENERAL] Unique index problem

Hello I created a unique index that doesn't seem to work when one column is NULL. Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from, id_lab_sample_types); Now I can run this insert twice and I

[GENERAL] "Immediate shutdown if postmaster.pid is removed" not mentioned in Release Notes

I noticed, that 9.5 release notes (beta2) do not mention commit 7e2a18a9161fee7e67642863f72b51d77d3e996. I think this one should be added. -- Victor Y. Yegorov

Re: [GENERAL] Index contains unexpected zero page at block

not relevant for this I think) /Victor On Thu, Dec 17, 2015 at 12:22 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Victor Blomqvist <v...@viblo.se> writes: > >> From time to time I get this and similar errors in my Postgres log file: > > < 2015-12-1

[GENERAL] Index contains unexpected zero page at block

Is this anything I should be worried about, and if so, what can I do to fix it? Will it be fixed with a newer version of Postgres? Thanks! Victor

Re: [GENERAL] DISTINCT in STRING_AGG

are the coordinates for each node and the result must be ordered by this to obtain the correct result. -- Original Message -- From: "Geoff Winkless" <pgsqlad...@geoff.dj> To: "Sterpu Victor" <vic...@caido.ro> Cc: "PostgreSQL General" <pgsql-gene

Re: [GENERAL] DISTINCT in STRING_AGG

uot;1399031" 1399031;"1399032,1399033" Is there a better way? I usualy try to avoid subqueries. -- Original Message -- From: "Geoff Winkless" <pgsqlad...@geoff.dj> To: "Sterpu Victor" <vic...@caido.ro> Cc: "PostgreSQL General" <pgsql-gen

[GENERAL] DISTINCT in STRING_AGG

Hello Can I make a distinct STRING_AGG? This is my query : SELECT atjs.id, STRING_AGG(CAST(aqjs1.id AS VARCHAR), ',' ORDER BY aqjs1.to_left) AS children FROM administration.ad_query_join_select atjs JOIN administration.ad_query aq ON (aq.id=atjs.id_ad_query) LEFT JOIN

[GENERAL] ARRAY_AGG and ORDER

Hello I need to order an array using another column in table ad_query_join_select. I need something like this but this is not a valid SQL: SELECT array_to_string(array_agg(aqjs.id ORDER BY aqjs.to_left), ',') AS str, aq.name FROM ad_query aq JOIN ad_query_join_select aqjs ON

Re: [GENERAL] ARRAY_AGG and ORDER

Yes, thank you. :) -- Original Message -- From: "Sterpu Victor" <vic...@caido.ro> To: "Geoff Winkless" <pgsqlad...@geoff.dj> Sent: 11/26/2015 2:51:48 PM Subject: Re[2]: [GENERAL] ARRAY_AGG and ORDER Yes, thank you. :) -- Original Message --

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

On Wed, Nov 4, 2015 at 1:31 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Victor Blomqvist <v...@viblo.se> writes: > > In case any of you are interested of recreating this problem, I today had > > the time to create a short example that reproduce the error every time I >

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

On Mon, Oct 12, 2015 at 10:15 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/12/2015 06:53 AM, Tom Lane wrote: > >> Andres Freund <and...@anarazel.de> writes: >> >>> On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote: >>> >&g

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

changes when there are people in the office. /Victor On Mon, Oct 12, 2015 at 10:15 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/12/2015 06:53 AM, Tom Lane wrote: > >> Andres Freund <and...@anarazel.de> writes: >> >>> On 2015-10-09

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

On Sat, Oct 10, 2015 at 10:00 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/09/2015 08:30 PM, Victor Blomqvist wrote: > >> Note that these errors most of the time only happens very briefly at the >> same time as the ALTER is run. When I did some experim

[GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

-number-of-columns-errors Thanks! Victor

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

; Thanks for your help so far! /Victor On Fri, Oct 9, 2015 at 10:49 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/09/2015 07:31 AM, Albe Laurenz wrote: > >> Adrian Klaver wrote: >> >>> For the reason why this is happening see: >>>>>

Re: [GENERAL] Why does this SQL work?

to prefix all your columns with tables aliases. -- Victor Y. Yegorov

Re: [GENERAL] Subselect with no records results in final empty set

To: pgsql-general@postgresql.org Sent: 1/29/2015 10:52:25 PM Subject: Re: [GENERAL] Subselect with no records results in final empty set On 1/29/2015 12:36 PM, Sterpu Victor wrote: ON(null) never matched. NULL is neither true nor false. ON somefieldinthejoin IS NULL would be a valid syntax. except

[GENERAL] Can I unite 2 selects?

Hello Can I write a query where I receive a single result set from many queries? Something like this: SELECT (SELECT 1 AS t1, 2 AS t2), (SELECT 3 AS t3) I tried exactly this but the error is: ERROR: subquery must return only one column But I don't see why it must have only one column. Thank

[GENERAL] Subselect with no records results in final empty set

Hello I have this select where the last subselect will return a empty set and because of this the whole select will be empty. How can I change this syntax so I will have a row result even if the last select is empty? SELECT * FROM (SELECT 1 AS t1, 2 AS t2) as t, (SELECT 3 AS t3) as s, (SELECT *

Re: [GENERAL] Can I unite 2 selects?

Thank you. This is the syntax I was looking for. -- Original Message -- From: Adrian Klaver adrian.kla...@aklaver.com To: Sterpu Victor vic...@caido.ro; PostgreSQL General pgsql-general@postgresql.org Sent: 1/29/2015 9:09:31 PM Subject: Re: [GENERAL] Can I unite 2 selects? On 01/29/2015

Re: [GENERAL] Subselect with no records results in final empty set

: 1/29/2015 10:03:38 PM Subject: Re: [GENERAL] Subselect with no records results in final empty set Sterpu Victor wrote Hello I have this select where the last subselect will return a empty set and because of this the whole select will be empty. How can I change this syntax so I will have

Re: [GENERAL] Subselect with no records results in final empty set

I changed the final query to SELECT * FROM (SELECT 1 AS t1, 2 AS t2) AS t1 LEFT JOIN (SELECT * FROM atc WHERE id = '1231222') AS t2 ON (1=1) ON(null) never matched. -- Original Message -- From: Sterpu Victor vic...@caido.ro To: David G Johnston david.g.johns...@gmail.com; pgsql-general

[GENERAL] Postgresql 9.4 session vars

Hello In previous versions I was able to define session vars in postgresql.conf with the option custom_variable_classes but this option is no longer available. Is there a replacement for this in postgres 9.4? Thank you --- This email has been checked for viruses by Avast antivirus software.

[GENERAL] ALTER TYPE ... ADD VALUE issue

('bad', 'good'); CREATE TYPE COMMIT; COMMIT ALTER TYPE enum_type ADD VALUE 'so-so' AFTER 'bad'; ERROR: ALTER TYPE ... ADD cannot run inside a transaction block What is wrong here? -- Victor Y. Yegorov

Re: [GENERAL] ALTER TYPE ... ADD VALUE issue

block. -- Victor Y. Yegorov

[GENERAL] Does writing new records while massive update will generate lock ?

INDEXEDTABLE.email_id=MYBIGTABLE.email_id) WHERE date is null; This transaction is still running and will end in several days. It only uses 1 core. My question is : Can I add new records in the table or will it generate locks ? I am using postgresql *8.4* Thanks for your help ! Regards, Victor

Re: [GENERAL] TZ_OFFSET

2014-07-28 13:19 GMT+03:00 Ramesh T rameshparnandit...@gmail.com: select TZ_OFFSET ('US/Eastern'); its'returning like function tz_offset('us/Eastern') does not exist; SELECT * FROM pg_catalog.pg_timezone_names WHERE name='US/Eastern'; -- Victor Y. Yegorov

Re: [GENERAL] Query with error - DOW FROM timestamp

Yes, thank you. -- Original Message -- From: Albe Laurenz laurenz.a...@wien.gv.at To: Victor Sterpu vic...@caido.ro; PostgreSQL General pgsql-general@postgresql.org Sent: 5/23/2014 12:51:30 PM Subject: RE: [GENERAL] Query with error - DOW FROM timestamp Victor Sterpu wrote: When I

[GENERAL] Query with error - DOW FROM timestamp

Hello When I run the query from down I receive an error. How can I write this query to receive the day ot the week. SELECT EXTRACT(DOW FROM timestamp TO_TIMESTAMP('14-10-2011', 'DD-MM-')); Thank you

[GENERAL] Constraint exclusion for timestamp expression

. How can I enforce pruning to kick in for the initial expressions? -- Victor Y. Yegorov

Re: [GENERAL] Constraint exclusion for timestamp expression

2014-05-20 14:26 GMT+03:00 Tom Lane t...@sss.pgh.pa.us: Victor Yegorov vyego...@gmail.com writes: How can I enforce pruning to kick in for the initial expressions? You already found out: use constants. The planner can't remove partitions on the basis of clauses involving volatile, or even

[GENERAL] Correct syntax

How would I write sutch a query? SELECT to_timestamp ('10-10-2013 15:00', 'DD-MM- HH24:MI') + interval REPLACE('1.30', '.', ':')||' hours' This gives error at REPLACE. Thank you.

[GENERAL] Lock problem

Hello I have a problem that it seems to be very hard to debug. Problem is from some postgresql locks. I use PostgreSQL 9.1.8. I runned this query to fid the locks: SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS blocking_pid, ka.usename AS blocking_user, a.current_query AS

Re: [GENERAL] Lock problem

-- Original Message -- From: Tom Lane t...@sss.pgh.pa.us To: Victor Sterpu vic...@caido.ro Cc: pgsql-general@postgresql.org Sent: 4/2/2014 6:31:13 PM Subject: Re: [GENERAL] Lock problem Victor Sterpu vic...@caido.ro writes: I have a problem that it seems to be very hard to debug

Re: [GENERAL] Lock problem

-- Original Message -- From: Merlin Moncure mmonc...@gmail.com To: Victor Sterpu vic...@caido.ro Cc: PostgreSQL General pgsql-general@postgresql.org Sent: 4/2/2014 6:49:28 PM Subject: Re: [GENERAL] Lock problem On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu vic...@caido.ro wrote

Re: [GENERAL] Lock problem

-- Original Message -- From: Igor Neyman iney...@perceptron.com To: Victor Sterpu vic...@caido.ro; pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: 4/2/2014 6:29:17 PM Subject: RE: [GENERAL] Lock problem From: pgsql-general-ow...@postgresql.org [mailto:pgsql

Re: [GENERAL] Lock problem

I followed all your advice and it is obiuos that this log will show exactly what I need to debug the situation. Great tip, thank you. -- Original Message -- From: Merlin Moncure mmonc...@gmail.com To: Victor Sterpu vic...@caido.ro Cc: PostgreSQL General pgsql-general@postgresql.org Sent

Re: [GENERAL] Lock problem

transaction will never be commited 3. from the server point of view there is a unfinished transaction that will block future statements Is this normal behaviour? -- Original Message -- From: Victor Sterpu vic...@caido.ro To: Merlin Moncure mmonc...@gmail.com Cc: PostgreSQL General pgsql

Re: [GENERAL] Lock problem

I'm sure is not right, but is a there a server side solution for such sitations? A configuration - timeout for idle transactions. -- Original Message -- From: Victor Sterpu vic...@caido.ro To: Victor Sterpu vic...@caido.ro; Merlin Moncure mmonc...@gmail.com Cc: PostgreSQL General

Re: [GENERAL] Join Bad Performance on different data types

JOIN graph2 gcr ON gcr.id=ANY(first.detail_id) JOIN graph3 gtd ON gtd.id=gcr.t_ids JOIN graph1 glt ON glt.id=ANY(gtd.id_list) 4. Try disabling materialization, like `set enable_material=off`. -- Victor Y. Yegorov

Re: [GENERAL] Join Bad Performance on different data types

in postgresql.conf to disable seqscan. Could you, kindly, also post `EXPLAIN` output of your original and modified queries also, leaving out all `enable_...` setting at their defaults. Just explain, without `analyze`. -- Victor Y. Yegorov

[GENERAL] Question on Sort node's actual rows

Buffers: shared hit=13 This is: PostgreSQL 9.3.1 on x86_64-apple-darwin12.5.0, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00), 64-bit -- Victor Y. Yegorov

[GENERAL] Unexpected update behaviour

works (but takes hours on my full data); - if I convert this UPDATE into a SELECT, I get the row to be updated. Could you kindly explain me why the query as it is updates no records? -- Victor Y. Yegorov create.sql Description: Binary data update.sql Description: Binary data -- Sent via

Re: [GENERAL] Breaking up a PostgreSQL COPY command into chunks?

? (Not challenging it, I just want to understand everything). Cheers, Victor On Fri, Nov 8, 2013 at 6:36 PM, Francisco Olarte fola...@peoplecall.comwrote: On Fri, Nov 8, 2013 at 5:09 AM, Victor Hooi victorh...@yahoo.com wrote: They think that it might be limited by the network, and how fast

[GENERAL] Breaking up a PostgreSQL COPY command into chunks?

counting the results?). Or are there other approaches you guys could recommend? Cheers, Victor

Re: [GENERAL] Breaking up a PostgreSQL COPY command into chunks?

this might fix it? And you'd recommend tweaking these values over trying to chunk up the COPY/SELECT, is that right? I've just realised the LIMIT/ORDER thing may not work well to paginate, since there may be new records, or deleted records between each time I call it? Cheers, Victor On Fri, Nov 8

Re: [GENERAL] Breaking up a PostgreSQL COPY command into chunks?

are connected over the internet). Cheers, Victor On Fri, Nov 8, 2013 at 1:44 PM, Victor Hooi victorh...@yahoo.com wrote: Hi, Hmm, ok, I'll pass that onto our DBA/operations guys, and see if that helps. Do these settings still work if you only have a single Postgres instance? (I'll need to check out

Re: [GENERAL] Idle transactions in PostgreSQL 9.2.4

://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW -- Victor Y. Yegorov

Re: [GENERAL] Pretend update

within the same transaction. Dohhh... It is possible to use RETURNING clause of the UPDATE statement and avoid SELECT. And yes, it is necessary to do this within a transaction and roll it back after. -- Victor Y. Yegorov

Re: [GENERAL] Performance of ORDER BY RANDOM to select random rows?

other database backends that do things differently? (I know that SQL Server suggests using NEWID to do things - http://msdn.microsoft.com/en-us/library/cc441928.aspx). Cheers, Victor On Fri, Aug 9, 2013 at 10:43 AM, Sergey Konoplev gray...@gmail.com wrote: On Wed, Aug 7, 2013 at 7:01 PM, Victor

[GENERAL] Performance of ORDER BY RANDOM to select random rows?

? Cheers, Victor

Re: [GENERAL] postgresql query

--- 2000 -1000 How do we decide wether 2000 or -1000 refers to the “In quantity”? Can you show a complete table's structure, please? -- Victor Y. Yegorov

Re: [GENERAL] Sum raw with the same continuous flags

activity. Inspired by this answer: http://stackoverflow.com/a/10624628/1154462 -- Victor Y. Yegorov

Re: [GENERAL] Cat the query be tuned further ?

. -- Victor Y. Yegorov

Re: [GENERAL] Using a CTE for an update

query slowdown. -- Victor Y. Yegorov

Re: [GENERAL] Seq Scan cost shown to be (cost=10000000000.00..10000000001.10)

, PostgreSQL just sets really high costs for the operations affected (like the one you see). As SeqScan is the only possible way to execute your query, it is still choosen. Somewhat related thread: http://www.postgresql.org/message-id/4d5b06ac.2020...@lelarge.info -- Victor Y. Yegorov

Re: [GENERAL] Referencing parts captured by round brackets in a regex in 8.4.13

: Use the escape string syntax for backslashes, e.g., E'\\'. ?column? -- t (1 row) Please, check this part of the docs (note the Caution): http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-BACKSLASH-TABLE -- Victor Y. Yegorov -- Sent via pgsql-general mailing

  1   2   >