Re: [GENERAL] replicating many to one

hat. [1] http://skytools.projects.pgfoundry.org/skytools-3.0/ [2] http://www.slony.info/ -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general maili

Re: [GENERAL] Increased I/O / Writes

at this tool: https://github.com/grayhemp/pgcookbook/blob/master/statement_statistics_collecting_and_reporting.md -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp https://github.com/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 8

Re: [GENERAL] Dynamic Log tigger (plpgsql)

My Question: How can I do "OLD.columnName != NEW.columnName" if I don't know what the columnNames are at Compile Time? I have the columnName in a variable. I suggest you use plpython. In this case you'll be able to do it. TD['old'][colNameVar] != TD['ne

Re: [GENERAL] Foreign key constraint question

Well, what about using inheritence and relation identifiers? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

[GENERAL] Case insensitivity problem

27;; t_string -- Йцукен (1 row) I expected the same result in second select. Can anybody explain me what's wrong? -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

[GENERAL] Case insensitivity problem

Й'; t_string -- Йцукен (1 row) I expected the same result in second select. Can anybody explain me what's wrong? -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an a

[GENERAL] pg_cancel_backend() does not work with buzz queries

c | f | 2007-10-02 05:05:28.908687+04 (1 row) -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

I'm sorry I mean not HUP but KILL 2007/10/3, Sergey Konoplev <[EMAIL PROTECTED]>: > Hi all, > > I often face with buzz queries (see below). I've looked through pg > manual and huge amount of forums and mail archives and found nothing. > The only solution is to rest

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

> > Don't forget to cc: the list. > > Try not to top-post replies, it's easier to read if you reply below the > > text you're replying to. > > > > Sergey Konoplev wrote: > > >>1. Is it always the same query? > > >>2. Does the c

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

2007/10/3, Alvaro Herrera <[EMAIL PROTECTED]>: > Sergey Konoplev escribió: > > > > AFAIK, pl/pgsql does listen for SIGINT during execution, but I don't nkow > > > abuot plpython. > > > > How can we find it out? > > Let's see one of th

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

2007/10/3, Erik Jones <[EMAIL PROTECTED]>: > On Oct 3, 2007, at 6:47 AM, Richard Huxton wrote: > > > Sergey Konoplev wrote: > >>> Don't forget to cc: the list. > >>> Try not to top-post replies, it's easier to read if you reply > >>&

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

Hello again, Sorry for the deal with my answer it was realy hectic week so I couldn't even check my mail. 2007/10/3, Richard Huxton <[EMAIL PROTECTED]>: > Sergey Konoplev wrote: > >> Don't forget to cc: the list. > >> Try not to top-post replies, it&#x

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

2007/10/17, Sergey Konoplev <[EMAIL PROTECTED]>: > Hello again, > > Sorry for the deal with my answer it was realy hectic week so I > couldn't even check my mail. > > 2007/10/3, Richard Huxton <[EMAIL PROTECTED]>: > > Sergey Konoplev wrote: > > >

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

2007/10/23, Martijn van Oosterhout <[EMAIL PROTECTED]>: > On Tue, Oct 23, 2007 at 09:56:26AM +0400, Sergey Konoplev wrote: > > I took a look at TCP state with netstat: > > > > pgdb:/base/PG-Data # netstat -pna |grep 8590 > > tcp1 0 127.0.

[GENERAL] Dynamic expressions set in "order by" clause

ng dynamic queries (execute '...') and code duplicating? -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/

Re: [GENERAL] Dynamic expressions set in "order by" clause

ogle for the solution and have found convert_to_scalar() function in selfuncs.c. Also I've found mention about a patch witch provide this function to be used from SQL (http://www.postgresql.org/community/weeklynews/pwn20070805.html). The question is how can I use it from SQL it

Re: [GENERAL] Installation of postgres server-8.4

Start with it http://www.postgresql.org/docs/8.4/interactive/admin.html On 20 October 2010 11:14, sameer malve wrote: > Hi sir, > >    Can you please guide me for installation of postgres server-8.4. > > > Thanks & Regards, > Sameer M. Malve > -- Sergey

Re: [GENERAL] create table as select VS create table; insert as select

my employer before I give out any system details. > > Before you ask it is not a big customer - just a very paranoid one :-) > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.o

Re: [GENERAL] index in desc order

On 2 November 2010 12:36, AI Rumman wrote: > Is it possible to create an index in descending order? > Yes it is - http://www.postgresql.org/docs/current/interactive/indexes-ordering.html -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/gr

Re: [GENERAL] index in desc order

zymon Guz wrote: >> >> >> On 2 November 2010 10:36, AI Rumman wrote: >>> >>> Is it possible to create an index in descending order? >> >> yes... >> create index i on t(i desc); >> >> regards >> Szymon > -- Sergey

Re: [GENERAL] Looking for a Generic lightweight job queueing (stack) implementation.

To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp -- Sent via pgsql-general mailing list (pg

Re: [GENERAL] Importing/Appending to Existing Table

able or > append rows? No it wont overwrite, it will append rows. > > Rich > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sergey Konoplev Bl

[GENERAL] Synonym/thesaurus dictionaries for FTS

Hi, Are there any publicly available synonym/thesaurus dictionaries for FTS? Thank you. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general

Re: [GENERAL] Synonym/thesaurus dictionaries for FTS

FYI On Wed, Jul 9, 2014 at 4:58 PM, Sergey Konoplev wrote: > Are there any publicly available synonym/thesaurus dictionaries for FTS? So, I've found several worth attention open projects providing synonyms and thesaurus dictionaries. http://archive.services.openoffice.org/pu

[GENERAL] statement_timeout doesn't work

Hi, PostgreSQL 9.2.7, Linux 2.6.32 Several days ago I found one of my servers out of connections, pg_stat_activity showed that everything was waiting for the DROP/ALTER INDEX transaction (see the record 2 below), that, as I guess, was waiting for the function call (record 1). -[ RECORD 1 ]

Re: [GENERAL] statement_timeout doesn't work

No hope here? On Tue, Jul 15, 2014 at 9:49 PM, Sergey Konoplev wrote: > Hi, > > PostgreSQL 9.2.7, Linux 2.6.32 > > Several days ago I found one of my servers out of connections, > pg_stat_activity showed that everything was waiting for the DROP/ALTER > INDEX transaction (s

Re: [GENERAL] statement_timeout doesn't work

meout-doesn-t-work-tp5811704p5812037.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] statement_timeout doesn't work

essfully then both of those commands will die if they > exceed the timeout specified. Thank you. I'll try it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.com -- Sent vi

Re: [GENERAL] statement_timeout doesn't work

On Mon, Jul 21, 2014 at 11:32 AM, Sergey Konoplev wrote: > On Mon, Jul 21, 2014 at 10:16 AM, David G Johnston > wrote: >>> So, If I separate the commands everything will will work as expected, >>> correct? >> >> I would assume so. >> >> If you wait

[GENERAL] PgToolkit 1.0.2 release testing

in database adapters - Made it to process TOAST tables and indexes providing bloat information and rebuilding instructions - Set an additional protection against the "incorrect result of cleaning" error -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.li

Re: [GENERAL] Converting char to varchar automatically

ot once per column. > > 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 -- Kind regards, Sergey Konoplev Post

Re: [GENERAL] Converting char to varchar automatically

On Tue, Oct 7, 2014 at 10:16 AM, Tom Lane wrote: > Sergey Konoplev writes: >> BTW, where can I find a list of type1->type2 pairs that doesn't >> require full table lock for conversion? > > There aren't any. Sometimes you can skip a table rewrite, but that &g

Re: [GENERAL] Converting char to varchar automatically

name from pg_cast, pg_type as t1, pg_type as t2 where t1.oid = castsource and t2.oid = casttarget and castmethod = 'b' order by 1, 2; -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988)

Re: [GENERAL] How to synchronize tables in remote (production) and local databases in case if the structure of local database's table has been modified

# alter table t add s text; ALTER TABLE skonoplev@[local]:5432 ~=# copy t(i) from '/tmp/t.dump'; COPY 5 skonoplev@[local]:5432 ~=# select * from t; i | s ---+--- 1 | 2 | 3 | 4 | 5 | (5 rows) -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/

Re: [GENERAL] Understanding and implementing a GiST Index

that is on the operations > of an entire query? Looking at the number of tree nodes touched for a scan > would be nice (and I would not be surprised if there is already a facility > for it). > > Project code is here if anyone is interested, any help would be great. I > have very little i

Re: [GENERAL] Running multiple instances off one set of binaries

ong as there are no collisions (E.g. port numbers) this works > fine? Sure. -- Sergey Konoplev Database and software architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

eed to find out what caused this 'idle in transaction', in the other words why the transaction was not finished, to solve the problem. > > Any clues .. > > Thanks > Anoop -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 4

Re: [GENERAL] Upsert Functionality using CTEs

general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

> Anoop > > > On Mon, Feb 11, 2013 at 11:16 AM, Sergey Konoplev wrote: >> >> On Wed, Feb 6, 2013 at 1:28 AM, Anoop K wrote: >> > We are hitting a situation where REINDEX is resulting in postgresql to >> > go to >> > dead lock state for e

Re: [GENERAL] Join query query

tion here http://www.postgresql.org/docs/9.2/static/sql-select.html. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.com --

Re: [GENERAL] Failing backups, canceling statement due to conflict with recovery

behind that is not your goal. > My goal is to reliably make pg_dump backups from a hot standby without > the hot standby lagging far behind the master. Also slave does not guarantee that it is always up-to-date. There could be issues like network problems, etc. Personally I recommend

Re: [GENERAL] Determining if an hstore is empty

array_length(avals('a=>1'::hstore - 'a=>1'::hstore), 1); > > select array_length(akeys('a=>1'::hstore - 'a=>1'::hstore), 1); > > select skeys('a=>1'::hstore - 'a=>1'::hstore) is null > > select 

Re: [GENERAL] Graphing query results from within psql.

1 1.5 2 2.5 3 3.5 4 > Servers > > postgres=# > > Best, > Aleksey > > -- > CFEngine Trainings: > Los Angeles, Feb 25 - 28. http://cf3la.eventbrite.com > New Jersey, Apr 29 - May 2.

Re: [GENERAL] Failing backups, canceling statement due to conflict with recovery

s. Nothing significant AFAIK. > I'm still unsure if this is supposed to work, and this is a bug in > PostgreSQL or Ubuntu, or if I'm just misreading the documentation. I would not say it is a bug. I think it just was not supposed to be a functionality of standby servers. -- Serge

Re: [GENERAL] PGbouncer and batch vs real-time pools

ration file does not mater what user you used to connect to pgbouncer from your application. And yes it means that you will have these two pools only. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 04

Re: [GENERAL] PgBouncer with many databases

> http://postgresql.1045698.n5.nabble.com/PgBouncer-with-many-databases-tp5745729.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:

Re: [GENERAL] Foreign Exclusion Constraints

l-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 S

[GENERAL] timeofday() and clock_timestamp() produce different results when casting to timestamptz

(); version --- PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit -- Sergey Konoplev Database and Software Architect http

Re: [GENERAL] bloating index, pg_restore

my question what is the relation between bloated > database and pg_restore. > > Regards -- Kind regards, Sergey Konoplev Database and Software Consultant Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: g

Re: [GENERAL] ts_tovector() to_query()

#x27;); ?column? -- f (1 row) But to_tsquery('robocop | (robocop & (dvd | collection))') will do the trick. [local]:5432 postgres@postgres=# select to_tsvector('robocop') @@ to_tsquery('robocop | (robocop & (dvd | collection))'); ?column? ---

Re: [GENERAL] Log messages regarding automatic vacuum and exclusive locks

ts, but nothing conclusive. > > > Thanks in advance, > > Dominic Jones > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev

Re: [GENERAL] run COPY as user other than postgres

NOT NULL > ) > TO '/some/path/to/file/file.csv' WITH CSV HEADER; > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev Data

Re: [GENERAL] Replication terminated due to PANIC

host= WARNING: could > not write block 0 of global/14078 > 2013-04-24 23:17:16 UTC [26989]: [5360086-1] user= db= host= DETAIL: > Multiple failures --- write error might be permanent. > > I checked in global directory of master, the directory 14078 doesn't exist. > > Anyone

Re: [GENERAL] CLUSTER, REINDEX and VACUUM on batch ops

column to the parent, 2. set the default constraint on the column of the parent, 3. update the column in partitions to the value, 4. set the not null constraint on the parent. It will be better from the point of view of inheritance as the new column will be fully inherited from the parent r

Re: [GENERAL] Confusing error message.

or just schema.relation), and the error handler printed to the log an > error message a confusing message. > > Thoughts? +1 [local]:5432 grayhemp@grayhemp=# table "бла.бла"; ERROR: relation "бла.бла" does not exist LINE 1: table "бла.бла";

[GENERAL] Open transaction with 'idle' (not 'idle in transaction') status

cept the status of the process. It is "idle" despite xact_start was not null. I expected it should always be "idle in transaction" in such cases. Are there any exceptions from this rule? May be something connected with LISTEN? -- Kind regards, Sergey Konoplev Database and Softwa

Re: [GENERAL] Strange locking problem

... FROM pg_locks AS l LEFT JOIN pg_stat_activity AS a ON -- procpid = pid -- <9.2 a.pid = l.pid -- >=9.2 ... -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 88

Re: [GENERAL] Rule for all the tables in a schema

SELECT INTO _tablename tablename FROM pg_tables WHERE schemaname = 'schemaname' LOOP EXECUTE 'CREATE RULE ... TO $1 ...' USING _tablename; END LOOP; END $$; For <9.0 you can use shell script with psql to do the same. -- Kind regards, Sergey Konopl

Re: [GENERAL] Rule for all the tables in a schema

On Wed, May 22, 2013 at 11:49 PM, Chris Travers wrote: > For pre-9.0, just explicitly create, run, and drop a pl/pgsql function. > Much easier than a shell script. +1, good point. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp

Re: [GENERAL] Find all the the SELECT statements that occured

n the database in an 1 hour interval or on a average. > > Would this simple or possible in postgres? Take a look at the pg_stat_statements module. http://www.postgresql.org/docs/9.2/static/pgstatstatements.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: htt

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

> is the cost shown in explain output some kind of default max or > something like that for such abnormal cases? When you set enable_xxx=off, it not actually disables the xxx operation, it sets the start cost to the high value (100). -- Kind regards, Sergey Konoplev PostgreSQL Con

Re: [GENERAL] Most efficient way to initialize a standby server

this case, and it might lead to even worth transfer speed that without compression. I usually set compression level to 1 and it works quite good. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7

Re: [GENERAL] vacuum_cost_delay and autovacuum_cost_delay

resql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype

Re: [GENERAL] Really poor gist index performance with tstzrange types

e(now(), now(), '[]'::text) && duration)) -> Bitmap Index Scan on f_duration_idx1 (cost=0.00..918.26 rows=1 width=0) (actual time=0.030..0.030 rows=0 loops=1) Index Cond: (tstzrange(now(), now(), '[]'::text) < duration) Total runtime: 0.

Re: [GENERAL] Get multiple columns with counts from one table.

the tablefunc module will help you to get this. http://www.postgresql.org/docs/9.2/static/tablefunc.html#AEN144882 It is documented pretty good and has a lot of useful examples. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1

Re: [GENERAL] Triggers NOT running as table owner

th SECURITY DEFINER could be used to access to the objects of their owners illegally. > > --strk; > > http://strk.keybit.net > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.pos

Re: [GENERAL] Transaction control in shards through PLPROXY

> updates on shards can be rolled back if any one among the set fails? It is called two-phase commit. You need to consult with this [1] section of documentation. [1] http://www.postgresql.org/docs/9.2/static/sql-prepare-transaction.html -- Kind regards, Sergey Konoplev PostgreSQL Consul

Re: [GENERAL] Transaction control in shards through PLPROXY

s - it could cause ones, mostly if somebody forget to do commit/rollback as it still holds locks. Moreover it involves application<->DBs communications and persistence, so it is surely might affect performance. > > Warm regards, > GB > > > On Mon, Jul 15, 2013 at 10

Re: [GENERAL] unique index corruption

. However, if you have some high/bulk-update/delete operations autovacuum might not manage with bloat, and in this case you can use this tool pgcompactor (https://code.google.com/p/pgtoolkit/) in conjunction with pgstattuple extension or pg_repack (https://github.com/reorg/pg_repack). -- Kind

Re: [GENERAL] Rule Question

best way here is to use trigger that does new.a = new.b. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via p

Re: [GENERAL] Speed up Switchover

is that pg_rewind uses the WAL to determine changed data blocks, and does not require reading through all files in the cluster. That makes it a lot faster when the database is large and only a small portion of it differs between the clusters. -- Kind regards, Sergey Konoplev PostgreSQL Consul

Re: [GENERAL] Speed up Switchover

You can find the discussion about that on following link: > http://www.postgresql.org/message-id/flat/ca+tgmoy4j+p7jy69ry8gposmmdznyqu6dtionprcxavg+sp...@mail.gmail.com#ca+tgmoy4j+p7jy69ry8gposmmdznyqu6dtionprcxavg+sp...@mail.gmail.com -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA

Re: [GENERAL] Fastest Index/Algorithm to find similar sentences

On Thu, Jul 25, 2013 at 3:54 PM, Janek Sendrowski wrote: > The Fulltextsearch is not really suitable because it doesn't have a tolerance. What do you exactly mean by tolerance here? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/

Re: [GENERAL] Fastest Index/Algorithm to find similar sentences

to_tsvector('The tiger is the largest cat'); ?column? -- t Or may be I understand something wrong again? > > Janek > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/m

Re: [GENERAL] Installing 9.2 on Ubuntu from packages: what is the current recommendation?

; Thanks, > Tim Bowden > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile:

Re: [GENERAL] Exit code -1073741819

ing or dropping the index to see if that helps? >> >> >>> >>> >>> Thank you! >>> >>> >>> >>> Reimer >>> 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br >>> <mailto:carlos.rei...@opendb.com.br&

Re: [GENERAL] Hierarchical numeric data type

k? Suggestions? Use integer arrays. It works just like you need select array_to_string(c, '.') from (values (array[1,10,2]), (array[1,5,3])) as sq(c) order by c; array_to_string - 1.5.3 1.10.2 and it is pretty fast when indexed. -- Kind regards, Sergey Konoplev Post

Re: [GENERAL] Exit code -1073741819

lar moment of time. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@

Re: [GENERAL] How to avoid Force Autovacuum

, i have seen the pid of > autovacuum process in the result but the query filed is "Empty" Was autovacuum the only process that you saw in pg_stat_activity? What OS do you use? Do you use huge pages? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linked

Re: [GENERAL] Pl/Python runtime overhead

ke python? I'd really > like to get a good grip of the architecture of this type of extension, and > possibly attempt to introduce a language of my own choosing. The docs I've > seen so far are mostly too specific, making it a bit for hard for me to see > the forest fro

Re: [GENERAL] Self referencing composite datatype

without FK create table node as ( id integer primary key, r integer, s integer, children integer[] ); and check integrity by triggers. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 88

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 7:11 PM, Sergey Konoplev wrote: > so you could download 9.3rc2 and experimant with it. Sorry, 9.3beta2 of course. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1

Re: [GENERAL] How to avoid Force Autovacuum

/sys/kernel/mm/transparent_hugepage/enabled cat /sys/kernel/mm/transparent_hugepage/defrag -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list

Re: [GENERAL] Self referencing composite datatype

On Wed, Aug 7, 2013 at 11:38 PM, Alban Hertroys wrote: > On Aug 8, 2013, at 4:11, Sergey Konoplev wrote: >> create table node as ( >> id integer primary key, >> r integer, s integer, >> children integer[] >> ); >> >> and check integrity by

Re: [GENERAL] How to avoid Force Autovacuum

select name, setting from pg_settings where name ~ 'vacuum' and setting <> reset_val; -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-gen

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

t depends on the distribution of id values in the table, but in the most cases I faced it works good. I had an idea to play with pg_stats.histogram_bounds to work around the described issue, but it was never so critical for tasks I solved. -- Kind regards, Sergey Konoplev PostgreSQL Consultant an

Re: [GENERAL] How to avoid Force Autovacuum

On Thu, Aug 8, 2013 at 10:59 PM, Vishalakshi Navaneethakrishnan wrote: > Now the problem is autovacuum.. why it was invoked and increased the load? > How to avoid this? Upgrade to the latest minor version 9.2.4 first. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA

Re: [GENERAL] Fastest Index/Algorithm to find similar sentences

), and it successfully finds it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

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

record IDs are - in our case, if we > delete a large number of records, it might affect things. You can try to look at pg_stats.histogram_bounds to work the issue around, however it is just my assumption, I have newer tried it. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA

[GENERAL] Strange message from pg_receivexlog

houghts what it was? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your sub

Re: [GENERAL] Strange message from pg_receivexlog

tate --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited Nothing looks suspicious for me. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1

Re: [GENERAL] Strange message from pg_receivexlog

* * * /bin/bash /var/lib/pgsql/tmsdb/archive_wal.sh >>/var/log/tmsdb/archive_wal.log -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-ge

[GENERAL] Conditional ordering operators

Hello everybody. I've written a script (see attachment) which creates operators @< - ascending ordering @> - descending ordering that allows you to replace code like this if then for select from where order by field1 desc, field2 loop

Re: [GENERAL] Conditional ordering operators

On 2/12/08, Decibel! <[EMAIL PROTECTED]> wrote: > You should start a project for this on pgFoundry. It looks very useful! > > On Feb 6, 2008, at 1:15 PM, Sergey Konoplev wrote: > > > Hello everybody. > > > > I've written a script (see attachment) which

[GENERAL] Wrong rows count estimation (explain, gist, tsearch)

time=2919.257..2919.257 rows=372012 loops=1) Index Cond: (obj_tsvector @@ '''музыка'''::tsquery) Total runtime: 14832.555 ms (7 rows) PG version - 8.3.7, STATISTICS is set to 500 for the column. What's wrong with it? Is it possible to solve the prob

Re: [GENERAL] Wrong rows count estimation (explain, gist, tsearch)

BTW, dead tupples <5% On Mon, Sep 28, 2009 at 11:09 AM, Sergey Konoplev wrote: > Hi, community > > I have a table containing column for FTS and an appropriate index: > > zzz=# \d search_table > ... > obj_tsvector                              | tsvector            

Re: [GENERAL] Wrong rows count estimation (explain, gist, tsearch)

On Mon, Sep 28, 2009 at 6:26 PM, Tom Lane wrote: > Sergey Konoplev writes: >> The table filled with about 7.5E+6 rows. Most of them have different >> from default values in obj_tsvector column. I use "estimated rows >> count trick" to make search results counter

[GENERAL] Best practices for effective_io_concurrency

some more description here. It would be great if someone provide his experience. Also I've found some info in EnterpriseDB documentation (http://www.enterprisedb.com/docs/en/8.3R2/perf/Performance_Guide-16.htm). Is it all actual for PG8.4? Thank you. -- Regards, Sergey Konoplev -- Sen

Re: [GENERAL] Best practices for effective_io_concurrency

On Mon, Oct 19, 2009 at 7:12 PM, Greg Smith wrote: > On Mon, 19 Oct 2009, Sergey Konoplev wrote: > >> I feel it rater significant for PG performance and would like to ask gurus >> to provide some more description here. > > It's probably not as significant as you ar

[GENERAL] Index Scan/Bitmap Index Scan for queries with FTS and ORDER+LIMIT

ps=1) Recheck Cond: (obj_status_did = 1) Filter: (obj_tsvector @@ '''sparse_words'''::tsquery) -> Bitmap Index Scan on i_test_table__tsvector_1 (cost=0.00..467.23 rows=7862 width=0) (actual time=290.202..290.202 rows=220

  1   2   3   >