Re: [GENERAL] Fully-automatic streaming replication failover when master dies?

it has an excellent automatic failover mechanism, which originates not from its noSQLness and could be theoretically implemented in any other databases (including PostgreSQL), not for a holy war. On Sun, Jan 26, 2014 at 8:50 AM, Scott Marlowe wrote: > Please don't top post in technical d

Re: [GENERAL] Fully-automatic streaming replication failover when master dies?

Failover is NOT about the RAID or SAN robusness mostly. It's about datacenters connectivity and network issues. If you lose one datacenter (it happens, and there is no aid for it), you should redirect all traffic to another DC ASAP and failover the master DB to it. When the disconnected DC is up ag

Re: [GENERAL] Fully-automatic streaming replication failover when master dies?

the work for all the machines). At least MongoDB does the work well, and with almost zero configuration. My question was about a ready and well-tested solutions, do they exist. On Thu, Jan 23, 2014 at 1:54 AM, John R Pierce wrote: > On 1/22/2014 1:35 PM, Dmitry Koterov wrote: > >>

[GENERAL] Fully-automatic streaming replication failover when master dies?

Hello. I googled 1 hour approximately, but have not found a ready solution for this. So maybe this feature is in PostgreSQL todo-list, or something similar exists somewhere... Before the actual question, I'd like to give a small analogy. What I mostly love in MongoDB is that it supports a fully t

[GENERAL] Replication: GZIP compression in WAL sender/receiver processes communication?

Hello. Is there a way to compress the traffic between master and slave during the replication?.. The streaming gzip would be quite efficient for that. (WAL archiving is not too good for this purpose because of high lag. I just need to minimize the cross-datacenter traffic keeping the replication

[GENERAL] Simple queries slowdown, maybe related to 3-minute long " in transaction"?

Hello. Sometimes I see a strange slowdown on my PG 9.1 server: it looks like the simplest queries which typically take 1ms or less (e.g. selection of a row by its primary key) take 300ms or even more. It is related to all queries within the connection, not the single one: once upon a time all fast

Re: [GENERAL] Interval "1 month" is equals to interval "30 days" - WHY?

BTW there are a much more short version of this: CREATE OR REPLACE FUNCTION int_equal(interval, interval) RETURNS boolean IMMUTABLE STRICT LANGUAGE sql AS 'SELECT $1::text = $2::text'; On Wed, Aug 8, 2012 at 4:51 PM, Albe Laurenz wrote: > Then maybe you should use something like this for equali

Re: [GENERAL] Interval "1 month" is equals to interval "30 days" - WHY?

equality is typically used in JOINs and unique indices). Now I have to use CREATE UNIQUE INDEX test ON tbl(interval_col::TEXT) and use the same casting to TEXT in all JOINS and searches - this is very ugly. On Wed, Aug 8, 2012 at 1:54 PM, Albe Laurenz wrote: > Dmitry Koterov wrot

Re: [GENERAL] Interval "1 month" is equals to interval "30 days" - WHY?

...and even worse: SELECT ('1 year'::interval) = ('360 days'::interval); --> TRUE :-) SELECT ('1 year'::interval) = ('365 days'::interval); --> FALSE :-) On Tue, Aug 7, 2012 at 4:42 PM, Dmitry Koterov wrote: > Hello. > > I've just d

[GENERAL] Interval "1 month" is equals to interval "30 days" - WHY?

Hello. I've just discovered a very strange thing: SELECT '1 mon'::interval = '30 days'::interval --> TRUE??? This returns TRUE (also affected when I create an unique index using an interval column). Why? I know that Postgres stores monthes, days and seconds in interval values separately. So h

Re: [GENERAL] Difference between speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE

I've performed many times. I thought that it was because of re-planning of STABLE functions on each call according to real passed values... If STABLE functions has frozen plans too (independent to its real passed arguments values), how could we explain so much difference in performance replacin

[GENERAL] Difference between speed of 2 functions: SQL+STABLE and PLPGSQL+EXECUTE

Hello. For example, I have 2 functions like these: CREATE OR REPLACE FUNCTION first(a INTEGER, b INTEGER, ...) RETURNS ... AS $body$ ...any SQL which uses $1, $2 etc. arguments, plus LIMIT $3... $body$ LANGUAGE 'sql' *STABLE* and CREATE OR REPLACE FUNCTION second(a INTEGER, b INTEGER, ...)

Re: [GENERAL] Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger

; EXCEPTION WHEN unique_violation THEN DELETE FROM ... WHERE ; END; END LOOP; construction helps. There seems to be no way to implement the same using triggers only. On Thu, Apr 26, 2012 at 3:39 PM, Dmitry Koterov wrote: > I'm not sure the cause is that DELETE does not

Re: [GENERAL] Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger

active (if I commit first and commit second, the third fails with "duplicate key" error). Are there any universal method which could be implemented purely in a trigger?.. On Fri, Jan 27, 2012 at 3:45 PM, Julian v. Bock wrote: > Hi > > >>>>> "DK" ==

[GENERAL] Stange "duplicate key value violates unique constraint" after "delete" at ON UPDATE trigger

Hello. PostgreSQL 8.4. I am trying to create a trigger which removes "old" rows from the table just before inserting a new one. The table has an UNIQUE INDEX on a column, that's why I need to remove an old item with the same value of the column before inserting a new one. If I work without transa

Re: [GENERAL] Dump a database excluding one table DATA?

option to pg_dump to skip several tables data restoration. :-) On Fri, Aug 19, 2011 at 12:44 AM, Dmitry Koterov wrote: > Thanks, "pg_dump --data-only --disable-triggers" is the king. > > (Unfortunately it is not supported by pg_dumpall, but it is entirely > another story.

Re: [GENERAL] Dump a database excluding one table DATA?

Thanks, "pg_dump --data-only --disable-triggers" is the king. (Unfortunately it is not supported by pg_dumpall, but it is entirely another story. :-) On Fri, Aug 19, 2011 at 12:36 AM, Adrian Klaver wrote: > On Thursday, August 18, 2011 1:23:25 pm Dmitry Koterov wrote: > > 1

Re: [GENERAL] Dump a database excluding one table DATA?

On Thursday, August 18, 2011 12:49:45 pm Dmitry Koterov wrote: > > Hello. > > > > Is there any way (or hack) to dump the whole database, but to exclude the > > DATA from a table within this dump? (DDL of the table should not be > > excluded: after restoring the data the ex

[GENERAL] Dump a database excluding one table DATA?

Hello. Is there any way (or hack) to dump the whole database, but to exclude the DATA from a table within this dump? (DDL of the table should not be excluded: after restoring the data the excluded table should look "empty".) I see -T switch of pg_dump, but seems -T excludes the data AND the DDL o

Re: [GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

y thing which I need is to make mutex around only one update statement. On Fri, Jul 1, 2011 at 12:01 AM, Dmitry Koterov wrote: > OK. > > Possible next solution is ON AFTER UPDATE trigger: > > BEGIN > LOCK TABLE tbl IN SHARE UPDATE EXCLUSIVE MODE; >UPDATE tbl SET uniq_

[GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

Hello. I need to create an auto-increment field on a table WITHOUT using sequences: CREATE TABLE tbl( name TEXT, uniq_id INTEGER ); Each INSERT to this table must generate a new uniq_id which is distinct from all others. The problem is that these INSERTs are rolled back oftenly (i.e. they a

Re: [GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

0, 2011 at 10:55 PM, A.M. wrote: > > On Jun 30, 2011, at 2:40 PM, Dmitry Koterov wrote: > > > Hello. > > > > I need to create an auto-increment field on a table WITHOUT using > sequences: > > This problem pops up a lot for invoice sequence numbers for the tax of

[GENERAL] How to create "auto-increment" field WITHOUT a sequence object?

Hello. I need to create an auto-increment field on a table WITHOUT using sequences: CREATE TABLE tbl( name TEXT, uniq_id INTEGER ); Each INSERT to this table must generate a new uniq_id which is distinct from all others. The problem is that these INSERTs are rolled back oftenly (i.e. they a

[GENERAL] Split pg_dump dump into files and then combine it back

Hello. Is there a tool (or a way) to parse a pg_dump'ed (structure only) dump file into smaller files (each function in its own file, its table in its own etc.) with ability to combine these files later into the proper dump file? The main problem is dependencies. Sometimes functions are defined b

Re: [GENERAL] Visual DATA editor for PostgreSQL?

Thanks, but seems Lightning Admin does not support foreign keys choice (at least, its demo version). Any other solutions? On Thu, Dec 31, 2009 at 10:47 AM, Tony Caduto < tony_cad...@amsoftwaredesign.com> wrote: > Dmitry Koterov wrote: > >> Hello. >> >> Is there

[GENERAL] Visual DATA editor for PostgreSQL?

Hello. Is there a GUI utility to visually edit Postgres DATA (not a database schema!), which allows at least: - insert/update rows using screen windowed forms (possibly ugly auto-generated forms, but - still forms) - insert foreign key references by selecting them from a list (not by typing the ke

[GENERAL] Bgwriter and pg_stat_bgwriter.buffers_clean aspects

Hello. I am trying to tune PostgreSQL write parameters to make writing operation fast on a heavy-loaded database (a lot of inserts/updates). After resetting the pg_stat_bgwriter statistics (I do it by deleting global/pgstat.stat file and restarting PostgreSQL) I monitor the following: # select *

[GENERAL] Bgwriter and pg_stat_bgwriter.buffers_clean aspects

Hello. I am trying to tune PostgreSQL write parameters to make writing operation fast on a heavy-loaded database (a lot of inserts/updates). After resetting the pg_stat_bgwriter statistics (I do it by deleting global/pgstat.stat file and restarting PostgreSQL) I monitor the following: # select *

[GENERAL] Planner hints in SELECT queries?

Hello. Sometimes I have to create the following SQL code: SET something=off; SET other=off; SELECT * FROM ... ORDER BY id LIMIT 10; RESET something; RESET something; (e.g. "something" may be equal to "seq_page_cost=10"). I propose to add the SELECT clause to do it natively, like this: SELE

Re: [GENERAL] Is index enough to do simple fetch, or table is always used too?

On Sun, Dec 14, 2008 at 3:36 PM, David Rowley wrote: > 2008/12/14 Dmitry Koterov : > > The question: if the table "tbl" scanned to fetch "id" and calculate > > md5(id), or the value of "id" is brought directly from "idx" index with >

[GENERAL] Is index enough to do simple fetch, or table is always used too?

Hello. Suppose I have the following index: CREATE INDEX idx ON tbl USING btree (abc, def, id) and perform the query with index scan: SELECT md5(id) FROM tbl WHERE abc=1 AND def=2 LIMIT 200 *The question:* if the table "tbl" scanned to fetch "id" and calculate md5(id), or the value of "id" is

[GENERAL] Propose: enum with all registered table names?

Hello. Sometimes I want to save in a table reference to another table's name. E.g. I am creating an accounting system which (on triggers) logs which record is changed and in which table: CREATE TABLE log( tbl XXX, id INTEGER, blahblah ); Nowadays XXX may be: 1) Table name. But it is quite

[GENERAL] Announce: PGUnit - xUnit test framework for pl/pgsql

Hello. Hope this will be helpful for agile developers. http://en.dklab.ru/lib/dklab_pgunit/ PGUnit is a xUnit-style framework for stored procedures in PostgreSQL 8.3+. It allows database developers to write automated tests for existed stored procedures or develop procedures using concepts of Tes

Re: [GENERAL] Indirect access to NEW or OLD records

s cannot take type record". So, I cannot ever create an utility function to call it as: value := extract_field_as_varchar(NEW, 'field'); Seems I have to write an entire trigger in plperl to access NEW record indirectly? On Thu, Sep 25, 2008 at 10:08 PM, Tom Lane <[EMAIL PR

[GENERAL] Indirect access to NEW or OLD records

Hello. I have a variable with a field name and want to extract this field value from NEW record: DECLARE field VARCHAR = 'some_field'; BEGIN ... value := NEW.{field}; -- ??? END; Is it possible in pl/pgsql? I have found one speed-inefficient solution: convert NEW to string and then

[GENERAL] Is there bigintarray?

Hello. We have a good intarray contrib module which contains a lot of features: additional functions, operators with GIN support etc. Are there plans for bigintarray?

Re: [GENERAL] TSearch2: find a QUERY that does match a single document

> > explain analyze >> select * from test.test_tsq >> where to_tsvector('40x40') @@ q >> > > why do you need tsvector @@ q ? Much better to use tsquery = tsquery > > test=# explain analyze select * from test_tsq where q = > '40x40'::tsque> >

[GENERAL] TSearch2: find a QUERY that does match a single document

Hello. TSearch2 allows to search a table of tsvectors by a single tsquery. I need to solve the reverse problem. *I have a large table of tsquery. I need to find all tsqueries in that table that match a single document tsvector: * CREATE TABLE "test"."test_tsq" ( "id" SERIAL, "q" TSQUERY NOT N

Re: [GENERAL] Changing between ORDER BY DESC and ORDER BY ASC

http://www.mail-archive.com/pgsql-general@postgresql.org/msg111788.htmlprobably won't match an index, because ASC or DESC ordering depends NOT on the table's data, but on the function parameter. Unfortunately the planner does not recognize the following case: CREATE TABLE "public"."prime" ( "nu

[GENERAL] Open source PostgreSQL type parsers in PHP?

Hello. Is there any open source and well-tested PHP libraries to parse and build the following PostgreSQL data types? - arrays (including N-dimensional): {a,b,c} - hstore: "a=>b, c=>d" - ROW: ("a","b","(""c"",""d"")") E.g. I have some PHP variable and want to build corresponding PostgreSQL value

Re: [GENERAL] How to execute 'set session role' from plpgsql function?

Just a suggestion: EXECUTE 'SET SESSION ROLE wishedrole'; won't help? 2008/8/17 Oleg Vasylenko <[EMAIL PROTECTED]> > Hi,everybody! > > I wish to have a function with code above, but compiller generate > syntactic error at the line "SET SESSION ROLE wishedrole;". > > How to pass the wishedrole

Re: [GENERAL] selecting data from subquery in same order

You may use something like this in a stored function: DECLARE a INTEGER[]; BEGIN a := '{2341548, 2325251, 2333130, 2015421,2073536, 2252374, 2273219, 2350850, 2367318, 2032977, 2032849}'; select * from users where id = any(a) order by idx(a, id); END; Or in the plain SQL: select

Re: [GENERAL] How to modify ENUM datatypes? (The solution)

Done. Now it's BSD licensed. :-) On Wed, Aug 13, 2008 at 4:57 PM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > On Wed, Aug 13, 2008 at 5:20 AM, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > About LGPL - I don't know. > > But the license is not a problem, t

Re: [GENERAL] How to get many data at once?

Try to use SELECT ARRAY(SELECT t_data FROM THETABLE WHERE t_ref_id = '1') AS v; In PHP you may fetch all matched values as a single string and then - use explode() to split it into values (possibly with later stripslashes). It is much faster than fetching a thousands of rows. On Thu, Aug 7, 200

Re: [GENERAL] How to modify ENUM datatypes? (The solution)

3, 2008 at 4:25 AM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > On Tue, Aug 12, 2008 at 5:40 PM, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > Here is the solution about "on the fly" ALTER ENUM: > > http://en.dklab.ru/lib/dklab_postgresql_enum/ > > > &

Re: [GENERAL] How to modify ENUM datatypes? (The solution)

Here is the solution about "on the fly" ALTER ENUM: http://en.dklab.ru/lib/dklab_postgresql_enum/ Usage: *-- Add a new element to the ENUM "on the fly". SELECT enum.enum_add('my_enum', 'third');* *-- Remove an element from the ENUM "on the fly". SELECT enum.enum_del('my_enum', 'first');* Possib

[GENERAL] How to UPDATE in ROW-style?

Hello. How could I write something like: DECLARE r table%ROWTYPE; ... UPDATE table SET (table.*) = (SELECT r.*) WHERE id = r.id; *This *syntax is invalid, and I cannot find another proper way to do it without explicit enumeration of table's columns. I don't want to explicitly specify tabl

Re: [GENERAL] How to speedup intarray aggregate function?

become 30ms instead of 1600 ms (50 times faster). Few days later, after complex testing, I'll publish complete patches in pgsql-hackers maillist. On 10/10/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > Wow, seems I've found that! > > * Beginning in PostgreSQL

Re: [GENERAL] How to speedup intarray aggregate function?

errmsg("bigint out of range"))); *arg = result; PG_RETURN_POINTER(arg); } ... } On 10/10/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > Thanks for your comment. > > I see two possible solution directions: > > > 1.

Re: [GENERAL] How to speedup intarray aggregate function?

global array buffer for later acces with array_buffer_get(). On 10/10/07, Filip Rembiałkowski <[EMAIL PROTECTED]> wrote: > > 2007/10/10, Dmitry Koterov <[EMAIL PROTECTED]>: > > Hello. > > > > I created an aggregate: > > > > CREATE AGGREGATE intarray_

[GENERAL] How to speedup intarray aggregate function?

Hello. I created an aggregate: CREATE AGGREGATE intarray_aggregate_push (_int4) ( STYPE = _int4, SFUNC = intarray_push_array, INITCOND = '{}' ); (or - I may use _int_union instead of intarray_push_array, its speed is practically the same in my case). This aggregate merges together a list o

[GENERAL] How to make LIKE to use index in "abc%" query?

Hello. I run explain analyze SELECT id FROM "table" WHERE name LIKE 'dt%'; having a btree index on "name" column. But unfortunately it uses seqscan instead of index scan, it's too slow. I had read some mailing archives about that problem, but have not found a solution. How to fix this LIKE beha

[GENERAL] Sthange things happen: SkyTools pgbouncer is NOT a balancer

Hello. We discovered some time ago that pgbouncer is NOT a balancer, because it cannot spread connections/queries to the same database to multiple servers. It's unbeliveable, but it's a fact! So, database name in the config MUST be unique. E.g. if we write bardb = host=192.168.0.1 dbname=bardb b

Re: [GENERAL] How to switch off Snowball stemmer for tsearch2?

> > write your own dictionary, which implements any logic you need. In your > case it's just a wrapper around ispell, which will returns original string > not stem. See example > http://www.sai.msu.su/~megera/postgres/fts/doc/fts-intdict-xmp.html > and russian article > http://www.sai.msu.su/~meger

Re: [GENERAL] How to switch off Snowball stemmer for tsearch2?

> > > Now > > > > select lexize('ru_ispell_cp1251', 'Дмитриев') -> "Дмитрий" > > select lexize('ru_ispell_cp1251', 'Иванов') -> "Иван" > > - it is completely wrong! > > > > I have a database with all Russian name, is it possible to use it (how?) > to > > if you have such database why just don't wri

Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery

Also, the controller is configured to use 75% of its memory for write caching and 25% - for read caching. So reads cannot flood writes. On 8/23/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > > I have written a small perl script to check how slow is fsync for Smart > > >

Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery

> > > I have written a small perl script to check how slow is fsync for Smart > > Array E200i controller. Theoretically, because of write cache, fsync > MUST > > cost nothing, but in practice it is not true > > That theory is fundamentally flawed; you don't know what else is in the > operating syst

Re: [GENERAL] How to switch off Snowball stemmer for tsearch2?

uot; to "Ivan" even if the ispell dicrionary contains an element for "Ivan"? So, this pseudo-code logic is needed: function new_lexize($string) { $stem = lexize('ru_ispell_cp1251', $string); if ($stem in names_database) return $string; else return $stem; } May

Re: [GENERAL] How to switch off Snowball stemmer for tsearch2?

stem guessing if a word is not in the dictionary. On 8/22/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote: > > On Wed, 22 Aug 2007, Dmitry Koterov wrote: > > > Hello. > > > > We use ispell dictionaries for tsearch2 (ru_ispell_cp1251).. > > Now Snowball ste

[GENERAL] How to switch off Snowball stemmer for tsearch2?

Hello. We use ispell dictionaries for tsearch2 (ru_ispell_cp1251).. Now Snowball stemmer is also configured. How to properly switch OFF Snowball stemmer for Russian without turning off ispell stemmer? (It is really needed, because "Ivanov" is not the same as "Ivan".) Is it enough and correct to s

Re: [GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery

This script is here: postgresql-8.2.3\src\tools\fsync\test_fsync.c On 8/22/07, Phoenix Kiula <[EMAIL PROTECTED]> wrote: > > Hi, > > > On 23/08/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > > And here are results of built-in Postgres test script: > > &g

[GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery

postgresql.conf, but after that Postgres database had completely crashed. :-) On 8/22/07, Dmitry Koterov <[EMAIL PROTECTED] > wrote: > > All settings seems to be fine. Mode is writeback. > > We temporarily (for tests only on test machine!!!) put pg_xlog into RAM > drive (to completely ex

[GENERAL] Postgres, fsync and RAID controller with 100M of internal cache & dedicated battery

Hello. We are trying to use HP CISS contoller (Smart Array E200i) with internal cache memory (100M for write caching, built-in power battery) together with Postgres. Typically under a heavy load Postgres runs checkpoint fsync very slow: checkpoint buffers dirty=16.8 MB (3.3%) write=24.3 ms sync=6

Re: [GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

One difference in SQL syntax is that FireBird could join stored procedures like this: SELECT b.* FROM get_ids() a LEFT JOIN get_data(a.ID) ON 1=1 (where a.ID parameter is passed from the previous set as a next procedure parameter), but Postgres cannot. On 8/21/07, Tony Caduto <[EMAIL PROTE

Re: [GENERAL] Deadlocks caused by a foreign key constraint

locks the whole row. On 8/15/07, Decibel! <[EMAIL PROTECTED]> wrote: > > On Fri, Aug 10, 2007 at 09:38:36PM +0400, Dmitry Koterov wrote: > > Hello. > > > > I have a number of deadlock because of the foreign key constraint: > > > > Assume we have 2 tables: A

[GENERAL] How to trap exceptions inside PL/Perl functions?

Hello. In PL/PGSQL I could write: BEGIN SELECT * FROM non_existed_table; EXCEPTION WHEN ... THEN ... END; How to do it in PL/Perl? I tried the standard for Perl trapping method: eval { spi_exec_query("SELECT * FROM non_existed_table"); }; if ($@) { ... } but it does not work - it says th

Re: [GENERAL] Persistent connections in PHP

Pconnects are absolutely necessary if we use tsearch2, because it initializes its dictionaries on a first query in a session. It's a very heavy process (500 ms and more). So, if we do not use pconnect, we waste about 500 ms on each DB connection. Too much pain. Or, of course, pconnect may be repla

Re: [GENERAL] Creating a row during a select

Try to read about CREATE RULE in the documentation. On 8/14/07, Michal Paluchowski <[EMAIL PROTECTED]> wrote: > > > Hi, > > is there a way to have PostgreSQL insert a new row into a table during > a SELECT query if no row is found by that query? > > > -- > Best regards, > Michal

[GENERAL] Deadlocks caused by a foreign key constraint

Hello. I have a number of deadlock because of the foreign key constraint: Assume we have 2 tables: A and B. Table A has a field fk referenced to B.idas a foreign key constraint. -- transaction #1 BEGIN; ... INSERT INTO A(x, y, fk) VALUES (1, 2, 666); ... END; -- transaction #2 BEGIN; UPDATE B

[GENERAL] Update a single row without firing its triggers?

Hello. Suppose I have a table tbl with columns (a, b, c, counter). And I have 5 ON UPDATE triggers assigned to this table. They process (a, b, c) columns, but never depend on counter. I need to update counter field, but I know that it is totally independent, so - for performance reason I want to

Re: [GENERAL] Does slonik EXECUTE SCRIPT call waits for comands termination?

} This script waits until all slaves are in sync with the master. On 6/1/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: Hello. Seems when I use EXECUTE SCRIPT<http://slony.info/documentation/stmtddlscript.html>and slonik reports PGRES_TUPLES_OK updates may NOT be finished y

Re: [GENERAL] Does slonik EXECUTE SCRIPT call waits for comands termination?

Sorry for mistake, wrong mailing list. On 6/1/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: Hello. Seems when I use EXECUTE SCRIPT<http://slony.info/documentation/stmtddlscript.html>and slonik reports PGRES_TUPLES_OK updates may NOT be finished yet on all slaves. I ran a long

[GENERAL] Does slonik EXECUTE SCRIPT call waits for comands termination?

Hello. Seems when I use EXECUTE SCRIPTand slonik reports PGRES_TUPLES_OK updates may NOT be finished yet on all slaves. I ran a long ALTER TABLE statement (about 3 minutes), master updated immediately after I had seen PGRES_TUPLES_OK, but slave

Re: [GENERAL] TWO SAME TABLES, ONE UPDATED. HOW TO SYNC THE OTHER?

This query will run quite slow if tables are large, so - you may in addition create a trigger-updated TIMESTAMP columns and search for changed data through the recent created/updated elements only. On 13 May 2007 02:21:30 -0700, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: On May 11, 11:06 pm,

Re: [GENERAL] What about SkyTools?

is not very handy.) Seems SkyTools developed for static schemas only, without an ability to modify the schema. Am I wrong? On 4/12/07, Robert Treat <[EMAIL PROTECTED]> wrote: On Wednesday 11 April 2007 12:08, Dmitry Koterov wrote: > Hello. > > Have anybody used SkyTools in product

[GENERAL] What about SkyTools?

Hello. Have anybody used SkyTools in production environment? What's the impression? In practice - is it now more preferrable than Slony or not yet?

Re: [GENERAL] How to speedup CHECKPOINTs?

still beats the performance, but less. On 3/29/07, CAJ CAJ <[EMAIL PROTECTED]> wrote: On 3/28/07, Joseph S wrote: > > Dmitry Koterov wrote: > > > And the general question - why SELECT queries slowdown during the > > CHECKPOINT? I thought that Postgres is a versio

Re: [GENERAL] How to speedup CHECKPOINTs?

No. Disk read activity is ALWAYS 0, the system has a lot of disk cache. On 3/28/07, Joseph S wrote: Dmitry Koterov wrote: > And the general question - why SELECT queries slowdown during the > CHECKPOINT? I thought that Postgres is a version-based database and read > queries

Re: [GENERAL] Strange behaviour under heavy load

: hmm, looks like checkpoint ? Check checkpoint settings in postgresql.conf. Oleg On Tue, 27 Mar 2007, Dmitry Koterov wrote: > Hello. > > I have found that sometimes heavy loaded PostgreSQL begins to run all the > queries slower than usual, sometimes - 5 and more times slower. I cannot

[GENERAL] How to speedup CHECKPOINTs?

Hello. The quintessence of the problem explained in previous letters: CHECKPOIND command works quite slow, disk write activity is high just after it is executed, and diring the CHECKPOINT SELECT queries work many times slower than usual. I experimended with bgwriter, but it affects the CHECKPOIN

Re: [GENERAL] Strange behaviour under heavy load

Disc write activity timeline looks like the following: CHECKPOINT -> Large disc write (30-40M/s), 1 second -> 4-5 seconds: no read and write activity at all, but server works slow -> 2-3 seconds: disc write activity about 4-5 M/s -> restore normal speed On 3/28/07, Dmitry Kot

Re: [GENERAL] Strange behaviour under heavy load

New information. When I run CHECKPOINT manually in psql console, effect is fully reproduced. So - it is a checkpoint slowdown. The quesion is - how to make CHECKPOINT work faster? On 3/27/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: How to tune them? Now I have: checkpoint_segmen

[GENERAL] Strange behaviour under heavy load

Hello. I have found that sometimes heavy loaded PostgreSQL begins to run all the queries slower than usual, sometimes - 5 and more times slower. I cannot reprocude that, but symptoms are the following: queries work very fast for 5-10 minutes, and after that - significant slowdown (every query, ev

Re: [GENERAL] Temporarily disable all table indices

Thanks! *pg_indexes.indexdef* is exactly what I was looking for! On 3/27/07, Erik Jones <[EMAIL PROTECTED]> wrote: On Mar 26, 2007, at 5:24 PM, Dmitry Koterov wrote: Hello. I need to perform a mass operation (UPDATE) on each table row. E.g. - modify one table column: UPDATE t

[GENERAL] Temporarily disable all table indices

Hello. I need to perform a mass operation (UPDATE) on each table row. E.g. - modify one table column: UPDATE tbl SET tbl_text = MD5(tbl_id); The problem is that if this table contains a number of indices, such UPDATE is very very slow on large table. I have to drop all indices on the table, th

Re: [GENERAL] Check the existance of temporary table

In stored procedures I used something like BEGIN CREATE TEMPORARY TABLE tmp ... EXCEPTION WHEN ... THEN ... END See pg error codes for details (I don't remember exactly, but maybe it is a dumplicate_table or duplicate_object exception). On 3/25/07, Tom Lane <[EMAIL PROTECTED]> wrote: "d

Re: [GENERAL] Planner tuning

I don't know exactly is it your case, but sometimes SET enable_sort = off; speeds up some queries by the factor of hundred. But in some cases this command slows down operations, so I TEMPORARILY switch enable_sort on and off for some queries. It affects the query plan greatly. On 3/20/07, Alban

Re: [GENERAL] Creation of a read-only role.

Oh, sorry for the previous question - I can create a scheme-changes-disallowed role by revoking the "CREATE" permission from all the database schemas. The issue seems to be closed now, thanks. On 3/18/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > actually - you dont need

Re: [GENERAL] Creation of a read-only role.

t;[EMAIL PROTECTED]> wrote: On 3/16/07, Dmitry Koterov <[EMAIL PROTECTED]> wrote: > Overall, we need 3 roles: > 1. Administrator: can do anything with a database (by default this user is > already exists - "postgres"). > 2. Read-only: can only read. Runs on all slave no

[GENERAL] Creation of a read-only role.

Hello. When we start using of any replication system (e.g. Slony) we need to create a "read-only" role for access the database. This role must be able to read anything, but should NOT be able to INSERT, UPDATE or DELETE for all database objects. Overall, we need 3 roles: 1. Administrator: can d

Re: [GENERAL] How to disable displaying of a NOTICE context?

ding is a good idea... No documentation about it at all. On 3/13/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Dmitry Koterov" <[EMAIL PROTECTED]> writes: > Is there any way to disable CONTEXT displaying with still enabled NOTICEs > text? You could do "\set VERBOSITY terse" regards, tom lane

[GENERAL] How to disable displaying of a NOTICE context?

Hello. With debug purposes I use RAISE NOTICE ... inside my stored functions/triggers. But there is a little problem: if I run this function/trigger inside psql command-line client (e.g.), it shows not only a notice text, but also a caller context (started with "CONTEXT" substring) which looks qu

Re: [GENERAL] Incorrect "ERROR: database "xxx" is being accessed by other users"

s" and "ROLLBACK PREPARED ..." to fix this) in a new PG version. Is it possible? On 3/13/07, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Tue, Mar 13, 2007 at 11:41:46AM +0300, Dmitry Koterov wrote: > Yes, I have one! > How to remove it now? I tried DEALLOCATE for

Re: [GENERAL] Incorrect "ERROR: database "xxx" is being accessed by other users"

Yes, I have one! How to remove it now? I tried DEALLOCATE for gid returned by select * from pg_prepared_xacts; but it says "prepared statement does not exist"... Database restart does not reset the prepared transaction... On 3/13/07, Tom Lane <[EMAIL PROTECTED]> wrote:

[GENERAL] Incorrect "ERROR: database "xxx" is being accessed by other users"

Hello. I have a database which I cannot delete (the same for rename): # psql -d template1 =# drop database xxx; ERROR: database "xxx" is being accessed by other users But it is NOT accessed by anybody, because: 1. ps ax|grep post shows that there is NO active connections to database xxx; 2. I