Re: [GENERAL] replicating many to one
On Thu, Jun 4, 2015 at 12:14 PM, Doiron, Daniel wrote: > The four source/master servers all have different databases, lets call them > A, B, C, and D. We'd like to replicate them to one cluster that will hold > ABCD databases. You can use londiste [1] or slony [2] to do that. [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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Increased I/O / Writes
On Sun, May 8, 2016 at 7:07 PM, drum.lu...@gmail.com wrote: > Is there a way to detect the queries that are causing that? > > I can use pg_stat_statements to get the most usage queries, but I was > wondering how can I find the queries that are causing that much IO? Take a look 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) 888-1979 gray...@gmail.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] 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['new'][colNameVar] -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 6: explain analyze is your friend
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
Hi, All
I have following case insensitivity problem with regular expressions:
pgdb:~ # locale |grep LC_CTYPE
LC_CTYPE=ru_RU.UTF-8
pgdb:~ # /opt/PostgreSQL/bin/psql -d test -U postgres
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# show client_encoding;
client_encoding
-
UTF8
(1 row)
test=# create table test_table ( t_string varchar );
CREATE TABLE
test=# insert into test_table values ('Йцукен');
INSERT 0 1
test=# insert into test_table values ('йцукен');
INSERT 0 1
test=# select * from test_table where t_string ilike 'Й%';
t_string
--
Йцукен
йцукен
(2 rows)
test=# select * from test_table where t_string ~* E'Й';
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
Hi, All
I have following case insensitivity problem with regular expressions:
pgdb:~ # locale |grep LC_CTYPE
LC_CTYPE=ru_RU.UTF-8
pgdb:~ # /opt/PostgreSQL/bin/psql -d test -U postgres
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
test=# show client_encoding;
client_encoding
-
UTF8
(1 row)
test=# create table test_table ( t_string varchar );
CREATE TABLE
test=# insert into test_table values ('Йцукен');
INSERT 0 1
test=# insert into test_table values ('йцукен');
INSERT 0 1
test=# select * from test_table where t_string ilike 'Й%';
t_string
--
Йцукен
йцукен
(2 rows)
test=# select * from test_table where t_string ~* E'Й';
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 appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] pg_cancel_backend() does not work with buzz queries
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 restart postgres server. Moreover I have to terminate the process using HUP signal to stop the server. transport=# select version(); version - PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) (1 row) transport=# select datname, usename, procpid, current_query::char(15), waiting, query_start from pg_catalog.pg_stat_activity where procpid = 20530; datname | usename | procpid | current_query | waiting | query_start ---+--+-+-+-+--- transport | belostotskaya_la | 20530 | select * from c | f | 2007-10-02 05:05:28.908687+04 (1 row) transport=# select pg_catalog.pg_cancel_backend(20530); pg_cancel_backend --- t (1 row) transport=# select datname, usename, procpid, current_query::char(15), waiting, query_start from pg_catalog.pg_stat_activity where procpid = 20530; datname | usename | procpid | current_query | waiting | query_start ---+--+-+-+-+--- transport | belostotskaya_la | 20530 | select * from 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 restart postgres server. Moreover I have to > terminate the process using HUP signal to stop the server. > > transport=# select version(); > version > - > PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) > 3.3.3 (SuSE Linux) > (1 row) > > transport=# select datname, usename, procpid, current_query::char(15), > waiting, query_start from pg_catalog.pg_stat_activity where procpid = > 20530; > datname | usename | procpid | current_query | waiting | >query_start > ---+--+-+-+-+--- > transport | belostotskaya_la | 20530 | select * from c | f | > 2007-10-02 05:05:28.908687+04 > (1 row) > > transport=# select pg_catalog.pg_cancel_backend(20530); > pg_cancel_backend > --- > t > (1 row) > > transport=# select datname, usename, procpid, current_query::char(15), > waiting, query_start from pg_catalog.pg_stat_activity where procpid = > 20530; > datname | usename | procpid | current_query | waiting | >query_start > ---+--+-+-+-+--- > transport | belostotskaya_la | 20530 | select * from c | f | > 2007-10-02 05:05:28.908687+04 > (1 row) > > -- > Regards, > Sergey Konoplev > -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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 client still think it's connected? > > >>3. Is that query using up CPU, or just idling? > > >>4. Anything odd in pg_locks for the problem pid? > > > > >1. No it isn't. I have few functions (plpgsql, plpython) that cause > > >such situations more often than another but they are called more often > > >also. > > > > OK, so there's no real pattern. That would suggest it's not a particular > > query-plan that's got something wrong. > > > > Do you always get this problem inside a function? > > Does pl/python listen to SIGINT during execution of functions? If not, > that'd be an explanation - if it's stuck inside a pl/python function... > > AFAIK, pl/pgsql does listen for SIGINT during execution, but I don't nkow > abuot plpython. How can we find it out? > > 4. You have to cancel the query from the command-line using "kill -9 > > " > > That's not cancel, that's taking a sledgehammer to your server :( Yes I know it but I have no choice :( ---(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
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 the functions to find out if anyone else can reproduce > the problem. > It happens with simple queries also. For example: select * from ( select d.*, cto.full_name, cast(st.name || ', ' || r.name as varchar) as cityname from drivers d join cars_trailers_owners cto on d.cars_trailers_owner_id = cto.id join settles st on d.settle_id = st.id join regions r on st.region_id = r.id order by sname, name, pname ) as sq -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 6: explain analyze is your friend
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 > >>> below the > >>> text you're replying to. > >> Thanx for your advice. I'm just absolutely worned out. Sorry. > > > > Know that feeling - let's see if we can't sort this out. > > > >>>>> 1. Is it always the same query? > >>>>> 2. Does the client still think it's connected? > >>>>> 3. Is that query using up CPU, or just idling? > >>>>> 4. Anything odd in pg_locks for the problem pid? > >>>> 1. No it isn't. I have few functions (plpgsql, plpython) that cause > >>>> such situations more often than another but they are called more > >>>> often > >>>> also. > >>> OK, so there's no real pattern. That would suggest it's not a > >>> particular > >>> query-plan that's got something wrong. > >>> > >>> Do you always get this problem inside a function? > >> As far as I remember I do. > > > > Hmm - check Magnus' thoughts on pl/python. Can't comment on Python > > myself. Are you sure it's not always the same few function(s) that > > cause this problem? > > > >>>> 2. The client just waits for query and buzz. > >>>> 3. They are using CPU in usual way and their pg_lock activity > >>>> seems normal. > >>> So the backend that appears "stuck" is still using CPU? > >> Yes but the metter is that this procedures usualy use CPU just a > >> little so I can't find out if there is some oddity or not. > > > > OK, so it's not that it's stuck in a loop wasting a lot of CPU > > In order to get at least some idea of what these processes are (or, > are not) doing, run an strace (or your OS's equivalent) on the > process before killing it. Let us know what you see there. > That is what I've got using strace with the buzzed process: pgdb:~ # strace -dirfvx -p 19313 Process 19313 attached - interrupt to quit [wait(0x137f) = 19313] pid 19313 stopped, [SIGSTOP] [wait(0x57f) = 19313] pid 19313 stopped, [SIGTRAP] 0.00 [e410] send(8, "\x00\x01\x74\xff\xff\xff\xff\x00\x00\x00\x01\x66\xff\xff"..., 8192, 0 [Output stoped here and after half hour I interupted strace] cleanup: looking at pid 19313 Process 19313 detached pgdb:~ # -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
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's easier to read if you reply below the > >> text you're replying to. > > > > Thanx for your advice. I'm just absolutely worned out. Sorry. > > Know that feeling - let's see if we can't sort this out. > > >>>> 1. Is it always the same query? > >>>> 2. Does the client still think it's connected? > >>>> 3. Is that query using up CPU, or just idling? > >>>> 4. Anything odd in pg_locks for the problem pid? > >>> 1. No it isn't. I have few functions (plpgsql, plpython) that cause > >>> such situations more often than another but they are called more often > >>> also. > >> OK, so there's no real pattern. That would suggest it's not a particular > >> query-plan that's got something wrong. > >> > >> Do you always get this problem inside a function? > > > > As far as I remember I do. > > Hmm - check Magnus' thoughts on pl/python. Can't comment on Python > myself. Are you sure it's not always the same few function(s) that cause > this problem? Yes I'm shure. I've noticed about 10 queries and procedure calls buzzing at least. > > >>> 2. The client just waits for query and buzz. > >>> 3. They are using CPU in usual way and their pg_lock activity seems > >>> normal. > >> So the backend that appears "stuck" is still using CPU? > > > > Yes but the metter is that this procedures usualy use CPU just a > > little so I can't find out if there is some oddity or not. > > OK, so it's not that it's stuck in a loop wasting a lot of CPU > > >> So - the symptoms are: > [snip] > > Exactly. > > So - we need to solve two mysteries > 1. Why are these functions not returning? > 2. Why does SIGINT not interrupt them? > > >> Are you happy that your hardware and drivers are OK? There aren't > >> problems with any other servers on this machine? > > > > Yes I'm quite happy. My hardware is: 2 double-core Xeon, 8Gb RAM, > > RAID5. What about other software... it's dedicated PG server so I have > > no problem with it. > > Well, the places I'd look would be: > 1. Hardware (you're happy that's fine, and it's not quite the problems > I'd expect) > 2. Drivers (same as #1) > 3. Client connectivity (but you say the client is fine) > 4. External interactions (see below) > 5. Bug in PG extension (pl/python) I think it's not only lp/python problem cos I saw pl/pgsql and simple queries also. For example: select * from ( select d.*, cto.full_name, cast(st.name || ', ' || r.name as varchar) as cityname from drivers d join cars_trailers_owners cto on d.cars_trailers_owner_id = cto.id join settles st on d.settle_id = st.id join regions r on st.region_id = r.id order by sname, name, pname ) as sq > 6. Bug in PG core code > > Do any of your functions interact with the outside world - fetch > webpages or similar? It could be they're waiting for that. If you're > using a library that could hang waiting for a response and also block > SIGINT at the same time that would explain everything. No, most of them don't. -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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: > > >> 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. > > > > > > Thanx for your advice. I'm just absolutely worned out. Sorry. > > > > Know that feeling - let's see if we can't sort this out. > > > > >>>> 1. Is it always the same query? > > >>>> 2. Does the client still think it's connected? > > >>>> 3. Is that query using up CPU, or just idling? > > >>>> 4. Anything odd in pg_locks for the problem pid? > > >>> 1. No it isn't. I have few functions (plpgsql, plpython) that cause > > >>> such situations more often than another but they are called more often > > >>> also. > > >> OK, so there's no real pattern. That would suggest it's not a particular > > >> query-plan that's got something wrong. > > >> > > >> Do you always get this problem inside a function? > > > > > > As far as I remember I do. > > > > Hmm - check Magnus' thoughts on pl/python. Can't comment on Python > > myself. Are you sure it's not always the same few function(s) that cause > > this problem? > > Yes I'm shure. I've noticed about 10 queries and procedure calls > buzzing at least. > > > > > >>> 2. The client just waits for query and buzz. > > >>> 3. They are using CPU in usual way and their pg_lock activity seems > > >>> normal. > > >> So the backend that appears "stuck" is still using CPU? > > > > > > Yes but the metter is that this procedures usualy use CPU just a > > > little so I can't find out if there is some oddity or not. > > > > OK, so it's not that it's stuck in a loop wasting a lot of CPU > > > > >> So - the symptoms are: > > [snip] > > > Exactly. > > > > So - we need to solve two mysteries > > 1. Why are these functions not returning? > > 2. Why does SIGINT not interrupt them? > > > > >> Are you happy that your hardware and drivers are OK? There aren't > > >> problems with any other servers on this machine? > > > > > > Yes I'm quite happy. My hardware is: 2 double-core Xeon, 8Gb RAM, > > > RAID5. What about other software... it's dedicated PG server so I have > > > no problem with it. > > > > Well, the places I'd look would be: > > 1. Hardware (you're happy that's fine, and it's not quite the problems > > I'd expect) > > 2. Drivers (same as #1) > > 3. Client connectivity (but you say the client is fine) > > 4. External interactions (see below) > > 5. Bug in PG extension (pl/python) > > I think it's not only lp/python problem cos I saw pl/pgsql and simple > queries also. For example: > select * from ( >select d.*, cto.full_name, cast(st.name || ', ' || r.name as > varchar) as cityname >from >drivers d >join cars_trailers_owners cto on >d.cars_trailers_owner_id = cto.id >join settles st on >d.settle_id = st.id >join regions r on >st.region_id = r.id >order by sname, name, pname > ) as sq > > > 6. Bug in PG core code > > > > Do any of your functions interact with the outside world - fetch > > webpages or similar? It could be they're waiting for that. If you're > > using a library that could hang waiting for a response and also block > > SIGINT at the same time that would explain everything. > > No, most of them don't. > > -- > Regards, > Sergey Konoplev > I've managed to repeat the situation and found out where the problem is. I run on of the heaviest queries on a client application (Delphi, psqlodbc.8.01.0101) then without waiting for it (the query run time is about 5 minutes) I interrupted the client using task manager. There was backend process still running on my server: pgdb:/base/PG-Data # ps -ef |awk '/postgres.*konoplev.*SELECT/' postgres 8590 8073 2 15:46 ?00:00:36 postgres: konoplev transport localhost(35442) SELECT root 8973 7642 0 16:10 pts/000:00:00 awk /postgres.*konoplev.*SELECT/ pgdb:/base/PG-D
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.0.1:5432 127.0.0.1:35442 > > CLOSE_WAIT 8590/postgres: kono > > CLOSE_WAIT means that the client (in this case SSH) has shutdown() its > end of the connection and evidently postgresql hasn't noticed. However, > SSH has not closed its socket entirely, because then the write would > fail. > > Can you strace the SSH daemon, my bet is that it's also stuck on a > write(), to the original client. It would also be interesting to know > what the original client is doing, since it's obviously still alive. > Looks like somewhere along the chain a program called shutdown() but is > no longer reading incoming data... > > Hope this helps, I've done strace and noticed that this SHH daemon is repeating next output: pid 10511 stopped, [SIGTRAP] 10.485688 [e410] rt_sigprocmask(SIG_BLOCK, [CHLD], [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] [], 8) = 0 [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] 0.000578 [e410] rt_sigprocmask(SIG_SETMASK, [], [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] NULL, 8) = 0 [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] 0.000468 [e410] read(4, [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] "\x0e\xd7\x62\xdb\xc8\x97\xbb\xbc\x52\xe6\xe1\xab\x6a\xcc"..., 16384) = 32 [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] 0.000533 [e410] select(25, [4 5 19 21 22], [22], NULL, NULL [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] ) = 1 (out [22]) [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] 0.000705 [e410] rt_sigprocmask(SIG_BLOCK, [CHLD], [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] [], 8) = 0 [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] 0.000481 [e410] rt_sigprocmask(SIG_SETMASK, [], [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] NULL, 8) = 0 [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] 0.000447 [e410] write(22, "\x51\x00\x00\x00\x11\x53\x65\x6c\x65\x63\x74\x20\x27\x4f"..., 18 [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] ) = 18 [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] 0.000876 [e410] select(25, [4 5 19 21 22], [], NULL, NULL [wait(0x57f) = 10511] pid 10511 stopped, [SIGTRAP] ) = 1 (in [22]) [wait(0x57f) = 10511] Original client was interrupted by me before the backend has hung and I've found neither its process (in task manager) nor corresponding netstat output line. By the way, I've also noticed that the SSH daemon's (which is in FIN_WAIT2) timer is "off" and took a look at tcp_fin_timeout... pgdb:~ # netstat -pnao |grep 37465 tcp1 131072 127.0.0.1:5432 127.0.0.1:37465 CLOSE_WAIT 24855/postgres: kon unkn-4 (41.30/0/0) tcp73728 0 127.0.0.1:37465 127.0.0.1:5432 FIN_WAIT2 10511/sshd: dcsshcl off (0.00/0/0) pgdb:~ # cat /proc/sys/net/ipv4/tcp_fin_timeout 60 ...which is 60. I wonder are there another variables which affect on FIN_WAIT2 timeout? -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Dynamic expressions set in "order by" clause
Hello, I have a procedure which takes few arguments. The arguments must affect expressions set of "order by" clause. For instance in one case ordering must be performed by 1st, 2nd (desc) and 3rd fields but in another by 3rd, 1st and 2nd fields. Is there a way to manage it without using 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
> Not that I've ever heard of. Well, you move the procedure up into > the application level but inside the database you'll need a procedure > to handle the branching on the argument values with each branch The thing is that it's internal query and its result isn't retuned from the procedure. > You could write a set of expressions that yield proper order by field > in one case and say null in another. Say we want order by columns > if the first function argument is '1' and by desc, col1, col2> if it is '2', this can be achieved as: Thank you for your suggestion but unfortunately it doesn't cover whole the problem. There are a lot of conditions in my query I have to check to define the set of ordering fields/expressions so it's extremely hard to do and debug. Today I've come to a solution but I need help of gurus. Let me try to explain it. If all the columns we need to sort by were the same type (for example float8) we would be able to form a sorting fields array and sort by it. Instead of "desc" we would be able to use "-" operator. select * from ( values (1.27, 23.46, 56.2, 76.1), (4.35, 6.76, 45.3, 1.6) ) as sq order by case when false then array[-column1, column3] when true then array[column1] else array[column2, -column4, column1] end So the only we need is any_type_to_float8_mapping functions family. I digged Google 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 it's worth doing and/or is there another solutions to do such mapping? -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] create table as select VS create table; insert as select
Hi, On 29 October 2010 11:46, Jacqui Caren-home wrote: > I have inherited an application that populates a number of > temp.y tables using create table ... as select ... What is the principle of creating this temp.y tables? May be table partitioning is better to implement here - http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html > > This is taking roughly five to ten minutes to run > > As this process hammers the database, I can only run benchmarks at night so > am asking here if anyone know if > > create table ...; then insert into ... as select... ; would be faster. > > or if anyone can suggest an alternative I may have missed. > > I am happy to move code server side if need be. > > TIA > > Jacqui > > I know these is not a lot of detail in the above - the system is under NDA > and I need to check with 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.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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp -- 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] index in desc order
On 2 November 2010 12:57, AI Rumman wrote: > But I am using Postgresql 8.1. Is it possible here? I am afraid not. You could try to do the index using kind of 1/field trick but I am not sure if it performs better than backward index scan in general. > > On Tue, Nov 2, 2010 at 3:42 PM, Szymon 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 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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looking for a Generic lightweight job queueing (stack) implementation.
On 7 November 2010 15:51, Allan Kamau wrote: > Hi, > I am looking for an easy to use job queueing system. Where a job is a PgQ may be? http://wiki.postgresql.org/wiki/PGQ_Tutorial > > Allan. > > -- > 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 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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Importing/Appending to Existing Table
On 2 February 2011 03:28, Rich Shepard wrote: > I have an existing table with 15,492 rows and want to add additional rows > from a .csv file. If I use 'COPY from with delimiter > as ":" csv quote as "'" ' will this overwrite existing rows in the table 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 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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/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/pub/mirror/OpenOffice.org/contrib/dictionaries/ http://sphinxsearch.com/downloads/dicts/ http://wordnet.princeton.edu/wordnet/download mythes /usr/share/myspell/dicts/ myspell /usr/share/mythes/ Now it will require to do some scripting around format converting. -- 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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 ]-- ts_age | 00:07:15.52997 state | active query_age | 00:07:15.529945 [...] waiting | f [...] query | select foo(...) -[ RECORD 2 ]-- ts_age | 00:06:37.844036 state | active query_age | 00:06:37.844036 [...] waiting | t [...] query | BEGIN; SET LOCAL statement_timeout TO 1000; DROP INDEX public.idx1; ALTER INDEX public.idx2 RENAME TO idx1; END; The interesting thing is that the transaction sets local statement_timeout to 1 second and its age was more than 6 minutes. The foo() is a complex plpython function containing all the specter of mod queries and using dblink(). I didn't manage to reproduce the problem with a simple test. psql -XAte
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 (see the record 2 below), that, as I guess, was
> waiting for the function call (record 1).
>
> -[ RECORD 1 ]--
> ts_age | 00:07:15.52997
> state | active
> query_age | 00:07:15.529945
> [...]
> waiting | f
> [...]
> query | select foo(...)
>
> -[ RECORD 2 ]--
> ts_age | 00:06:37.844036
> state | active
> query_age | 00:06:37.844036
> [...]
> waiting | t
> [...]
> query | BEGIN;
> SET LOCAL statement_timeout TO 1000;
> DROP INDEX public.idx1;
> ALTER INDEX public.idx2 RENAME TO idx1;
> END;
>
> The interesting thing is that the transaction sets local
> statement_timeout to 1 second and its age was more than 6 minutes. The
> foo() is a complex plpython function containing all the specter of mod
> queries and using dblink(). I didn't manage to reproduce the problem
> with a simple test.
>
> psql -XAte < \timing
> CREATE LANGUAGE plpythonu;
> CREATE TABLE test (t text);
> CREATE INDEX test_idx ON test (t);
> EOF
> sleep 1
> psql -XAte < \timing
> CREATE OR REPLACE FUNCTION test_plpy()
> RETURNS void LANGUAGE 'plpythonu' AS \$\$
> import time
> plpy.execute("INSERT INTO test VALUES ('a')")
> plpy.execute("ALTER TABLE test ADD i integer")
> plpy.execute("SELECT dblink_exec('dbname=grayhemp', 'DROP TABLE test')")
> plpy.execute("SELECT * FROM dblink('', 'SELECT pg_sleep(3)') AS t (t text)")
> \$\$;
> SELECT test_plpy();
> EOF
> sleep 1
> psql -XAte < \timing
> BEGIN;
> SET LOCAL statement_timeout TO 1000;
> DROP INDEX test_idx;
> END;
> EOF
>
> Any ideas why could it happen and what should I do to prevent this in future?
>
> --
> 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
^^^
--
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 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
On Fri, Jul 18, 2014 at 6:15 PM, David G Johnston wrote: >> query | BEGIN; >> SET LOCAL statement_timeout TO 1000; >> DROP INDEX public.idx1; >> ALTER INDEX public.idx2 RENAME TO idx1; >> END; > > If I read this correctly you sent the entire begin...end as a single > compound statement and so, depending on how you did this, the actual SET > LOCAL command never got executed since the entire command is waiting for the > necessary locks before it can be executed. Right, I send it as a single command. > Your sample test doesn't appear to correctly exercise this behavior. Are > you maybe using -c in the problem case? Or a client library besides psql > that would behave in this manner? In this case I use DBD::Pg, but I haven't found any notes about such kind of behavior. > Note that the fact that "query" is a compound statement is why I claim the > above... So, If I separate the commands everything will will work as expected, correct? > > David J. > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/statement-timeout-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 -- 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 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
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 to send the DROP/ALTER index commands until the SET LOCAL > command returns successfully 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 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
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 to send the DROP/ALTER index commands until the SET LOCAL >> command returns successfully then both of those commands will die if they >> exceed the timeout specified. So, you were right, when I send the BEGIN/SET LOCAL/DROP/END as a single command the statement timeout doesn't work. Below is the test reproducing the problem. psql -XAte < 'idle' AND pid <> pg_backend_pid(); -[ RECORD 1 ]-+--- pid | 20071 backend_start | 2014-07-29 22:21:17.322722-07 xact_start| 2014-07-29 22:21:17.32666-07 query_start | 2014-07-29 22:21:17.328291-07 state_change | 2014-07-29 22:21:17.328293-07 waiting | f state | active query | SELECT pg_sleep(100); age | 00:00:06.855373 -[ RECORD 2 ]-+--- pid | 20085 backend_start | 2014-07-29 22:21:18.330979-07 xact_start| 2014-07-29 22:21:18.332332-07 query_start | 2014-07-29 22:21:18.332332-07 state_change | 2014-07-29 22:21:18.332332-07 waiting | t state | active query | BEGIN;SET LOCAL statement_timeout TO 1000;DROP TABLE test;END; age | 00:00:05.849701 The age of the compound statement is more than the specified statement timeout. -- 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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgToolkit 1.0.2 release testing
Hi, The 1.0.2 release of the PgToolkit is on the way. Some significant improvements of the tables and indexes bloat reducing tool have been made. Testers are very welcome. Use this link to get the testing version https://github.com/grayhemp/pgtoolkit/branches/v1.0testing Report bugs and suggestions either to me directly or on the issue page https://github.com/grayhemp/pgtoolkit/issues. List of changes: - Fixed the non working statement timeout in the reindexing process - Made it use `DROP INDEX CONCURRENTLY` if version is `>=9.2` - Fixed the randomness of the SET statements order 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.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.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] Converting char to varchar automatically
BTW, where can I find a list of type1->type2 pairs that doesn't require full table lock for conversion? ps. Sorry for top posting. On Mon, Oct 6, 2014 at 4:20 PM, Tom Lane wrote: > Melvin Davidson writes: >> Also, don't forget to test for relkind = 'r'. My bad from before. > > In principle you need to ignore attisdropped columns as well. > > Thinking about Jim's point about speed: it'd be wise to collapse any > updates for multiple columns in the same table into one ALTER command, > so that you only rewrite the table once, not 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 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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 > doesn't mean that a lesser lock is possible. Oh, sorry, it was a typo, I meant "that doesn't require a full table rewrite". -- 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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Converting char to varchar automatically
On Tue, Oct 7, 2014 at 11:02 AM, Tom Lane wrote: > Roughly speaking it's the pairs that have a binary (WITHOUT FUNCTION) > coercion according to pg_cast, although we have special logic for a few > cases such as varchar(M) -> varchar(N). That ones? select t1.typname, t2.typname 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) 888-1979 gray...@gmail.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] How to synchronize tables in remote (production) and local databases in case if the structure of local database's table has been modified
On Wed, Oct 8, 2014 at 12:49 AM, Vitaly Isaev wrote: > I am trying to figure out how to dump the contents of several selected > tables from server in order to update the tables on development > workstations. The biggest challenge is that the tables I'm trying to > synchronize may be diverged (developers may add - but not delete - new > fields to the tables through the Django ORM, while schema of the production > database remains unchanged for a long time). The COPY trick will probably help you. Note that I specify a column list in the last COPY statement. skonoplev@[local]:5432 ~=# create table t (i integer); CREATE TABLE skonoplev@[local]:5432 ~=# insert into t select * from generate_series(1, 5); INSERT 0 5 skonoplev@[local]:5432 ~=# copy t to '/tmp/t.dump'; COPY 5 skonoplev@[local]:5432 ~=# truncate t; TRUNCATE TABLE skonoplev@[local]:5432 ~=# 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/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray...@gmail.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] Understanding and implementing a GiST Index
On Thu, Oct 9, 2014 at 12:09 AM, Connor Wolf wrote: > I'm trying to implement a custom indexing system using the GiST index > framework, and I have a few questions. > Basically, I'm trying to implement a system that allows me to search across > a set of 64 bit integers by hamming distance. This is intended to be used in > searching for similar images, where the 64 bit integer is actually a phash > of an image, and similarity between two images is reflected in the hamming > distance between two integers. Have you seen the smlar extension? http://www.pgcon.org/2012/schedule/events/443.en.html http://sigaev.ru/git/gitweb.cgi?p=smlar.git;a=blob;hb=HEAD;f=README http://railsware.com/blog/2012/05/10/effective-similarity-search-in-postgresql/ > > Anyways, The appropriate approach here is to use something called a BK tree, > for which I've written some test code and I think I have a decent grip of > (my test code seems to work, in any event). > > That said: > > Is there a simple piece of example-code for implementing a GiST index for a > single index? I've been looking through the /contrib/ directory, > particularly the /contrib/btree_gist/ files, but it looks like 90% of the > complexity there is related to supporting all the column types Postgres has, > rather then actually tied to producing a functional index. > Once I have something compiling, how can I check to be sure that I'm > actually using the indexing module I created? I can enable my compiled > extension using `CREATE EXTENSION`, but is there an option for `CREATE INDEX > test_index ON testing USING gist (val);` that lets me specify *which* GiST > index is actually employed? Is this even a valid question? > This is probably something that's available in one of the system tables > somewhere, but I haven't had much luck with google finding out where. > Testing: What's the appropriate way to examine the generated tree structure > of the index? I certainly went through a few bugs with my test tree system > (and that was in python!). Is there any way to examine the index structure > for debugging purposes? > Also, it looks like `ereport()` is the proper way to emit debugging > information. Is this correct? > In that vein, is there any way to have information 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 idea what I'm doing. > > Thanks, > Connor -- 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 mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running multiple instances off one set of binaries
On Sat, Feb 9, 2013 at 7:39 PM, Karl Denninger wrote: > Am I correct in that I can do this by simply initdb-ing the second instance > with a different data directory structure, and when starting it do so with a > different data directory structure? You are correct. > And that as long 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.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] REINDEX deadlock - Postgresql -9.1
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 ever. On debugging the issue we found that > 3 connections are going in to some dead lock state. > > idle in transaction > REINDEX waiting > SELECT waiting > > All these connections are made in the same minute. Once in deadlock state we > are not able to make new connections to db.(So not able to view pg_locks > also). New connections appears as 'startup waiting' in ps output. Initially > we suspected is the result of not closing a > connection. But it seems it got stuck after creating a connection and is not > able to proceed. The 'idle in transaction' means that someone started a transaction (BEGIN) and did not finished it (COMMIT/ROLLBACK) in the first connections. The 'startup waiting' message means that something got an exclusive lock on some system catalogs. You should not allow persistent or long running 'idle in transaction's that could affect tables that are actively used by other connections mostly if these tables are system ones. You need 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 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.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] Upsert Functionality using CTEs
On Sun, Feb 10, 2013 at 5:23 PM, Tim Uckun wrote: > This works pretty good except for when the top 100 records have > duplicated email address (two sales for the same email address). How is it assumed to work when the migrating email already exists in people? > > I am wondering what the best strategy is for dealing with this > scenario. Doing the records one at a time would work but obviously it > would be much slower. There are no other columns I can rely on to > make the record more unique either. > > > -- > 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 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 -- 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] REINDEX deadlock - Postgresql -9.1
On Sun, Feb 10, 2013 at 9:55 PM, Anoop K wrote: > We analyzed the application side. It doesn't seem to be create a transaction > and keep it open. StackTraces indicate that it is BLOCKED in JDBC > openConnection. > > Any JDBC driver issue or other scenarios which can result in transaction> ? There are no other scenarios for 'idle in transaction'. Unfortunately I am not a JDBC specialist but googling 'postgresql jdbc idle in transaction' shows a lot of threads where people discuss such things. I am pretty sure there is an answer among them. > > 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 ever. On debugging the issue we found that >> > 3 connections are going in to some dead lock state. >> > >> > idle in transaction >> > REINDEX waiting >> > SELECT waiting >> > >> > All these connections are made in the same minute. Once in deadlock >> > state we >> > are not able to make new connections to db.(So not able to view pg_locks >> > also). New connections appears as 'startup waiting' in ps output. >> > Initially >> > we suspected is the result of not closing a >> > connection. But it seems it got stuck after creating a connection and is >> > not >> > able to proceed. >> >> The 'idle in transaction' means that someone started a transaction >> (BEGIN) and did not finished it (COMMIT/ROLLBACK) in the first >> connections. The 'startup waiting' message means that something got an >> exclusive lock on some system catalogs. >> >> You should not allow persistent or long running 'idle in transaction's >> that could affect tables that are actively used by other connections >> mostly if these tables are system ones. You need 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 415 867 9984 >> Russia, Moscow +7 901 903 0499 >> Russia, Krasnodar +7 988 888 1979 >> >> Skype: gray-hemp >> Jabber: gray...@gmail.com > > -- 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 -- 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] Join query query
On Wed, Feb 13, 2013 at 3:01 PM, Andrew Taylor wrote: > And ended up with a table 13,708,233 rows long with what looks like plenty > of duplicated rows. Some but not all are duplicated. What can I do to sort > this out? It means that (e, n) pairs are not unique in A and B and you got a superposition of them. If you have 5 equal pairs in A and 7 same pairs with in B you will get 35 combinations as a result. And BTW when you use LEFT JOIN if there are rows in A that have no matching pairs in B you will get one row for each of them where lan and lon are NULLs. See the join_type section 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 -- 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] Failing backups, canceling statement due to conflict with recovery
On Wed, Feb 13, 2013 at 12:53 AM, Stuart Bishop wrote: > I'm unable to offload my backups to one of my PG 9.1 hot standbys > using purely streaming replication. After a few hours, usually on the > same large table, pg_dump is failing with 'ERROR: canceling statement > due to conflict with recovery'. > > From my reading from the documentation, this should not be possible as > my hot standby has 'hot_standby_feedback = on' in its postgresql.conf. hot_standby_feedback affects VACUUM only to prevent it from removing dead rows on master that might cause the cleanup conflict. It has no deal with other hard conflicts like in case of DROP TABLE etc. Yo could try increasing max_standby_streaming_delay that will "pause" applying WAL data on replica but it still does not guarantee successful result and moreover could cause lagging far 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 to do pg_dump on master at least on <=9.2. -- 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 -- 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] Determining if an hstore is empty
On Mon, Feb 11, 2013 at 11:36 AM, Wells Oliver wrote:
> What's the canonical way of doing this? Various failed attempts include:
Just compare an empty hstore with yours.
[local]:5432 grayhemp@grayhemp=# select ''::hstore = ''::hstore,
''::hstore = '{a=>1}'::hstore;
?column? | ?column?
--+--
t| f
(1 row)
>
> select array_length(%%'a=>1'::hstore - 'a=>1'::hstore, 1)
>
> select array_length(%%('a=>1'::hstore - 'a=>1'::hstore), 1)
>
> select 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 'a=>1'::hstore - 'a=>1'::hstore is null
>
> Etc.
>
> --
> Wells Oliver
> wellsoli...@gmail.com
--
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
--
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] Graphing query results from within psql.
On Wed, Feb 13, 2013 at 5:17 PM, Aleksey Tsalolikhin
wrote:
> Below is an example of feeding query output to gnuplot without leaving psql.
> I'd like to call it as "select graph(select * from example)", just for fun.
> What do I need to learn to do that, please? Can I create a function that
> uses "\o"? I think not, because a function runs server-side and \o is a
> client side feature.
plpython/plperl/etc plus this way of calling
select just_for_fun_graph('select ... from ...', 'My Graph', 78, 24, ...)
will do the trick.
>
> This is not a production issue, just learning, for fun.
>
> # psql -U postgres
> psql (8.4.15)
> Type "help" for help.
>
> postgres=# \t
> Showing only tuples.
> postgres=# \a
> Output format is unaligned.
> postgres=# \f ' '
> Field separator is " ".
> postgres=# select * from example;
> 1 1
> 2 2
> 3 3
> 4 4
> postgres=# \o | /usr/bin/gnuplot
> postgres=# select 'set title "My Graph"; set terminal dumb 78 24; set key
> off; set ylabel "Time"; set xlabel "Servers";' || 'plot ''-'' with lines;' ;
> select * from example;
> postgres=# \o
>
> My Graph
> Time
> 4
> ++--+--+---+--+---+-**
> + + + + ++
> +
> | |
> 3.5 ++ ++
> | |
> | |
> 3 ++ ***++
> ||
> ||
> 2.5 ++ ++
> ||
> ||
> 2 ++*** ++
> | |
> | |
> 1.5 ++ ++
> | |
> + + + + + + +
> 1 **--+--+---+--+---+-++
> 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. http://cf3.eventbrite.com/
--
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
--
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] Failing backups, canceling statement due to conflict with recovery
On Wed, Feb 13, 2013 at 8:59 PM, Stuart Bishop wrote: > Something that might be interesting that I neglected to mention, the > DETAIL of the error message is random; on production my failures end > up with one of these three: > > DETAIL: User query might have needed to see row versions that must be > removed. > DETAIL: User was holding a relation lock for too long. > DETAIL: User was holding shared buffer pin for too long. I think it can only be solved by increasing max_standby_streaming_delay or by setting it to -1. What about VACUUM from your test case. Probably it is not the matter of it, but the matter of what is happening in the connection. Try replace the VACUUM with SELECT pg_sleep() or may be start a transaction without/with query inside, or something else. Try to simulate different stuff from the activity that happens on your server to find out what causes which DETAILs. >> Personally I recommend to do pg_dump on master at least on <=9.2. > > Anything in particular in 9.2? I've been seeing a lot of replication > related fixes in 9.1 patch releases and had been planning on sticking > with 9.1 for the next 18 months. 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. -- 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 -- 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] PGbouncer and batch vs real-time pools
On Thu, Feb 14, 2013 at 9:09 AM, François Beausoleil wrote: > I believe the important user is the one pgbouncer uses to connect to the real > PostgreSQL server. That user + dbname is what creates a new pool. The > client's name (the one the app connects as) is irrelevant since I hard-code > what user I want to use. > > Did I read the configuration manual right? Any advice from people "familiar > with the matter"? Yes, you understand it right. It all the connections between pgbouncer and postgres will be done with the user you specified in the configuration 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 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.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] PgBouncer with many databases
On Mon, Feb 18, 2013 at 3:59 PM, komunca wrote: > Just to say it in another way: Is there a way to dynamically add database to > PgBouncer Yes it is possible to do. You need to add the following to the [databases] section in your pgbouncer config file. * = host=localhost >From the pgbouncer documentation http://pgbouncer.projects.pgfoundry.org/doc/config.html: "*" acts as fallback database: if the exact name does not exist, its value is taken as connect string for requested database. > > > Thanks > > > > -- > View this message in context: > 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: > 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 Skype: gray-hemp Jabber: gray...@gmail.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] Foreign Exclusion Constraints
On Wed, Feb 20, 2013 at 2:34 PM, Nathan Boley wrote: > I'm trying to store exons that must fit within a chromosome, but the > chromosomes all have different lengths, so I want a check constraint > so that location is contained within CHR_RANGE where CHR_RANGE is > taken from the chromosomes table. > > Can I add that constraint without a trigger? No, you can't. There is nothing wrong in doing it with triggers BTW. > > The archives seem to say no, but I thought I'd ask... > > http://www.postgresql.org/message-id/1288033876.6278.6.ca...@vanquo.pezone.net > http://www.postgresql.org/message-id/CACECd8i4P4iNqUii4Lqsw0qSthawhh3gE=atmbdsjn25qrx...@mail.gmail.com > http://www.postgresql.org/message-id/1678334.8llTyI05Te@hek506 > > Thanks, > Nathan Boley > > > -- > 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] timeofday() and clock_timestamp() produce different results when casting to timestamptz
Hi all, Today after upgrading to 9.2 from 9.0 I faced a very odd incompatibility that seems to me as a bug: smoking_test=# select timeofday(), clock_timestamp(); timeofday |clock_timestamp -+--- Sat Mar 23 11:20:54.023796 2013 MSK | 2013-03-23 11:20:54.023815+04 smoking_test=# select timeofday()::timestamptz, clock_timestamp()::timestamptz; timeofday |clock_timestamp ---+--- 2013-03-23 12:21:03.995653+04 | 2013-03-23 11:21:03.995695+04 As you can see after casting timeofday() to timestamp with time zone it adds one hour to the timestamp when clock_timestamp() behaves normally. Timezone is Europe/Moscow. Version information: smoking_test=# select version(); 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://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 -- 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] bloating index, pg_restore
Hello, On Wed, Mar 27, 2013 at 9:56 AM, salah jubeh wrote: > I have a database which is bloated because of vacuum full, so you find > indexes bigger than the table itself. Table can not be bloated because of vacuum full, it removes bloat from the table and its indexes. The fact that an index is larger then the table it it built on does not say that something is bloated. Use the pgstattuple extension to determine bloat http://www.postgresql.org/docs/9.2/static/pgstattuple.html. > I have dumped this database and restored it without reindixing and it was > extremely slow. So, 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: gray-hemp Jabber: gray...@gmail.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] ts_tovector() to_query()
On Thu, Mar 28, 2013 at 2:12 PM, Kevin Grittner wrote:
>> What I want to do is return items that have 'Robocop' or 'Robocop
>> and DVD' or 'Robocop and Collection' or 'Robocop and DVD and
>> collection'
>
> SELECT m.* FROM movies m
> WHERE to_tsvector(m.item_title) @@ to_tsquery('Robocop & (DVD |
> Collection)')
It wont return items that have 'Robocop' entry only.
[local]:5432 postgres@postgres=#
select to_tsvector('robocop') @@ to_tsquery('robocop & (dvd | collection)');
?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?
--
t
(1 row)
--
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: gray-hemp
Jabber: gray...@gmail.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] Log messages regarding automatic vacuum and exclusive locks
On Tue, Apr 23, 2013 at 8:50 AM, wrote: > Good morning. I'm seeing several of the following log messages each morning > (for example, there were five this morning, spaced approximately one minute > apart, with the closest interval between messages being 44 seconds). > They're occurring during a daily cycle of deleting all rows from a table and > then repopulating it from another database; the first message came If all the rows are deleted from the table every time to repopulate it later, I would suggest using TRUNCATE table_name; instead DELETE FROM table_name. TRUNCATE does not left any free space in the table so autovacuum will have nothing to do with it. > LOG: automatic vacuum of table "x.y.z": could not (re)acquire exclusive > lock for truncate scan Hence you will not get this messages and BTW it will be faster. > > I'm using default settings for autovacuum, with server version 9.2.4. > > Some other smaller tables produce the same messages during the same process, > but apparently less frequently. > > Any thoughts on the cause? Chapters 18 and 23 of the documentation provided > some hints, 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 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: gray-hemp Jabber: gray...@gmail.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] run COPY as user other than postgres
On Tue, Apr 23, 2013 at 1:11 PM, Kirk Wythers wrote: > I would like to run the COPY command as a user other than "postgres". I find > it a bit of a pain (or at least requiring an extra step or two) to have the > postgres user own the files that I am creating with COPY TO. Here is a simple > example where the location '/some/path/to/file/file.csv' is owned by another > user and it would be very spiffy if I could run the COPY TO as that user. Any > ideas? sudo chown anotheruser:postgres /some/path/to/file/file.csv sudo chmod 664 /some/path/to/file/file.csv This will set the file's group to postgres and allow it's members to write to the file. > > > > COPY ( > SELECT * FROM > some_table > WHERE > 2012 = EXTRACT (YEAR FROM some_column) --AND value IS 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 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: gray-hemp Jabber: gray...@gmail.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] Replication terminated due to PANIC
On Wed, Apr 24, 2013 at 5:05 PM, Adarsh Sharma wrote: > I have a Postgresql 9.2 instance running on a CentOS6.3 box.Yesterday i > setup a hot standby by using pgbasebackup. Today i got the below alert from > standby box : > > [1] (from line 412,723) > 2013-04-24 23:07:18 UTC [13445]: [6-1] user= db= host= PANIC: > _bt_restore_page: cannot add item to page > > When i check, the replication is terminated due to slave DB shutdown. From > the logs i can see below messages :- I am not sure that it is your situation but take a look at this thread: http://www.postgresql.org/message-id/CAL_0b1t=WuM6roO8dki=w8dhh8p8whhohbpjreymmqurocn...@mail.gmail.com There is a patch by Andres Freund in the end of the discussion. Three weeks have passed after I installed the patched version and it looks like the patch fixed my issue. > > 2013-04-24 23:17:16 UTC [26989]: [5360083-1] user= db= host= ERROR: could > not open file "global/14078": No such file or directory > 2013-04-24 23:17:16 UTC [26989]: [5360084-1] user= db= host= CONTEXT: > writing block 0 of relation global/14078 > 2013-04-24 23:17:16 UTC [26989]: [5360085-1] user= db= 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 has faced above issue ? > > Thanks -- 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: gray-hemp Jabber: gray...@gmail.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] CLUSTER, REINDEX and VACUUM on batch ops
On Wed, Apr 24, 2013 at 8:49 AM, François Beausoleil wrote: > Presently, I'm migrating each partition individually to add NOT NULL, set a > default value and update the table to have correct values. Essentially, I'm > doing this: > > ALTER TABLE parent ADD COLUMN new_field int; -- adds the field to all child > tables - runs quickly > > -- the bulk of the data transfer > for each partition in partitions: > BEGIN; > UPDATE partition SET new_field = 0; > ALTER TABLE partition > ALTER COLUMN new_field SET NOT NULL > , ALTER COLUMN new_field SET DEFAULT 0; > COMMIT; > > CLUSTER partition USING partition_pkey; > REINDEX TABLE partition; > VACUUM ANALYZE partition; > done > > After I've clustered the table, must I reindex and vacuum as well? It is > unclear to me if clustering a table reindexes or not: the docs at > http://www.postgresql.org/docs/current/static/sql-cluster.html are silent on > the matter, but do mention that an ANALYZE is in order. CLUSTER does full table rewrite including all its indexes so REINDEX is not required after it. It is mentioned in the docs implicitly: << When an index scan is used, a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes. When a sequential scan and sort is used, a temporary sort file is also created, so that the peak temporary space requirement is as much as double the table size, plus the index sizes. >> BTW, you do not need to do the ALTERs for each partition. What you need to do is: 1. add the 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 rather then partially overloaded in partitions. -- 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: gray-hemp Jabber: gray...@gmail.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] Confusing error message.
On Wed, Apr 24, 2013 at 4:21 AM, Dmitriy Igrishin wrote: > I've spend some time to find a bug in the application, > which performed query with entire quoted schema-qualified > relation name (i.e. "schema.relation" instead of "schema"."relation" > 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 "бла.бла"; ^ It looks ambiguous indeed. -- 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: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Open transaction with 'idle' (not 'idle in transaction') status
Hi,
PostgreSQL 9.2.2, Ubuntu 11.10, Linux 3.0.0.
A couple of days ago I noticed a strange output from a cron command I
use to terminate long transactions.
psql --no-psqlrc --single-transaction -d
postgres -t -c "SELECT
pg_terminate_backend(pid),now(),now()-xact_start as duration,* from
pg_stat_activity where (now() - pg_stat_activity.xact_start) > '60
min'::interval and usename NOT IN ('postgres', 'slony', 'backuper')" |
grep -v '^$'
t| 2013-04-22 17:50:01.452166+04 |
01:00:41.024359 | 16402 | sports | 21945 | 57857517 |
push_io_notifications.app | | 127.0.0.1 |
| 44784 | 2013-04-22 16:49:20.417845+04 | 2013-04-22
16:49:20.427807+04 | 2013-04-22 16:49:20.427807+04 | 2013-04-22
16:49:20.427838+04 | f | idle | LISTEN fb_marker_insert;
Everything is fine here except 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 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: gray-hemp
Jabber: gray...@gmail.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] Strange locking problem
On Tue, May 21, 2013 at 12:24 PM, Moshe Jacobson wrote: > I wanted to see what was happening in that transaction ID that might be > causing this lock to be held, but I was unsure how. I know I can look in > pg_locks for the other transaction, but that will not tell me what statement > is executing in that transaction. pg_stat_activity does not have a > transaction ID column. > > How can I see what statement is executing in a transaction? You can join pg_locks and pg_stat_activity by pid (or procpid = pid if your version <9.2). SELECT ... current_query ... 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) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.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] Rule for all the tables in a schema
On Wed, May 22, 2013 at 10:34 PM, Sajeev Mayandi wrote: > Is there a way, I can say create a rule for all the tables in an schema? > This will avoid writing complicated functions. You can use DO block if your postgres version is >=9.0. DO $$ DECLARE _tablename text BEGIN FOR 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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Find all the the SELECT statements that occured
On Thu, May 23, 2013 at 2:37 PM, Nik Tek wrote: > I have a question on how to find all the SELECT statements that have > occurred in the database. I don't want any DML(Insert/Update/Delete) > statements to be captured. This is for knowing how many selects statements > occur within 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: 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Seq Scan cost shown to be (cost=10000000000.00..10000000001.10)
On Mon, May 27, 2013 at 12:42 AM, Amit Langote wrote: > I set enable_seqscan=off and also accidentally dropped the only index [...] > Seq Scan on testdata (cost=100.00..101.10 rows=2 width=71) [...] > Although, I suspect the (dropped index + enable_seqscan) causes this, > 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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Most efficient way to initialize a standby server
Try this step-by-step instruction https://code.google.com/p/pgcookbook/wiki/Streaming_Replication_Setup. I constantly update it when discovering useful things, including low bandwidth issues. On Mon, May 27, 2013 at 5:08 PM, Edson Richter wrote: > Since 9.0 days I do use script with rsync for transfer. And sometimes the > servers get out of sync (due large processing in master database and huge > network latency), and I have to reinitialize the standby server. WAL stream is not compressed and quite bloated by its nature. You can use SSH tunnel with compression, described in the mentioned above instruction, and redirect your replication through it. > Lately , this script take about an hour to copy all data (23GB) over the > standby server, and I would like to know if there is a more efficient way > (perhaps, using pg_basebackup?) to reinitilize the standby server. AFAIK pg_basebackup does not use compression either when transferring data. In this case you can also use compressed SSH tunnel with pg_basebackup or rsync with compression enabled. I would also like to recommend not to set the compression level too high, because your CPU might be a bottleneck in 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 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.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] vacuum_cost_delay and autovacuum_cost_delay
On Tue, Jun 4, 2013 at 5:06 PM, ascot.m...@gmail.com wrote: > if I change the value of "vacuum_cost_delay", what is the impact of it on > autovacuum side? If autovacuum_vacuum_cost_delay is not -1 then autovacuum will use this value, and there will be no effect of changing vacuum_cost_delay on autovacuum. However, if it is -1 than it will use the value of vacuum_cost_delay. http://www.postgresql.org/docs/9.2/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY > > regards > > -- > 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: 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Really poor gist index performance with tstzrange types
On Tue, Jun 11, 2013 at 5:13 PM, Tom Lane wrote: > Joe Van Dyk writes: >> Am I doing something silly? Or is the row-estimation for gist indexes not >> even close in this case? > > 9.2 didn't have any logic for estimating range << conditions. I see > reasonable estimates for this case in HEAD, though, presumably thanks > to work by Alexander Korotkov. I just wanted to add that rewriting the << condition the way shown below might partially solve the problem. where tstzrange(now(), now(), '[]') < duration and not tstzrange(now(), now(), '[]') && duration And here is the result. [local]:5432 grayhemp@grayhemp=# explain analyze select count(*) from f where tstzrange(now(), now(), '[]') << duration; QUERY PLAN Aggregate (cost=2720.17..2720.18 rows=1 width=0) (actual time=109.161..109.163 rows=1 loops=1) -> Seq Scan on f (cost=0.00..2636.84 rows=1 width=0) (actual time=109.148..109.148 rows=0 loops=1) Filter: (tstzrange(now(), now(), '[]'::text) << duration) Rows Removed by Filter: 10 Total runtime: 109.210 ms (5 rows) Time: 109.837 ms [local]:5432 grayhemp@grayhemp=# explain analyze select count(*) from f where tstzrange(now(), now(), '[]') < duration and not tstzrange(now(), now(), '[]') && duration; QUERY PLAN Aggregate (cost=2646.39..2646.40 rows=1 width=0) (actual time=0.042..0.043 rows=1 loops=1) -> Bitmap Heap Scan on f (cost=926.55..2563.48 rows=33164 width=0) (actual time=0.035..0.035 rows=0 loops=1) Recheck Cond: (tstzrange(now(), now(), '[]'::text) < duration) Filter: (NOT (tstzrange(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.098 ms (7 rows) Time: 0.801 ms -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Get multiple columns with counts from one table.
On Wed, Jun 12, 2013 at 3:09 PM, chuydb wrote: > Im trying to get something like this: (one column with my types grouped > together and multiple columns with the count vales for each place) > I get: > | type| home| school | work| > cafe| friends | mall| > --- > | one | 2 | | 2 > | | | | > | two | | 1 | > | 1 | | | > | three | | | 2 > | | | | > | four| | | > | 1 | | | > | five| | | > | 1 | 1 | | > | six | | | > | | | 1 | First you need to get the counts by grouping by type and place together, and then crosstab(text source_sql, text category_sql) from 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 (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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Triggers NOT running as table owner
On Thu, Jun 27, 2013 at 4:58 AM, Sandro Santilli wrote: > According to release notes of 8.3.18 (yeah, old docs) > a trigger runs with the the table owner permission. > > This is the only document I found about this matter: > http://www.postgresql.org/docs/8.3/static/release-8-3-18.html > > > Require execute permission on the trigger function for CREATE TRIGGER > (Robert Haas) > > This missing check could allow another user to execute a trigger > function with forged input data, by installing it on a table he > owns. This is only of significance for trigger functions marked > SECURITY DEFINER, since otherwise trigger functions run as the table > owner anyway. (CVE-2012-0866) > > But, while I'd need this to be true, I can't confirm this is the case. > > Did I misinterpret the note above ? Looks like you did. It means that the patch adds an execute permission check on functions that are called by triggers. There was no such check before the patch, so it was kind of a security hole, because anyone could call the function by just using it in a trigger on ones own table. So trigger functions that are marked with 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.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: gray-hemp Jabber: gray...@gmail.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] Transaction control in shards through PLPROXY
On Wed, Jul 10, 2013 at 10:20 PM, Granthana Biswas wrote: > Inspite of being aware that PLPROXY does autocommit for DML functions called > on shards, I was wondering if there is any way around to put a set of DML > functions called from Router on shards inside a transaction so that all > 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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction control in shards through PLPROXY
On Sun, Jul 14, 2013 at 10:56 PM, Granthana Biswas wrote: > Thank you for your reply. Have you implemented this while sharding your > database? Did it cause any performance issues? I used it for just several mission critical featured in one project, not widely. What about performance issues - 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:51 AM, Sergey Konoplev wrote: >> >> On Wed, Jul 10, 2013 at 10:20 PM, Granthana Biswas >> wrote: >> > Inspite of being aware that PLPROXY does autocommit for DML functions >> > called >> > on shards, I was wondering if there is any way around to put a set of >> > DML >> > functions called from Router on shards inside a transaction so that all >> > 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 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 > > -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique index corruption
On Wed, Jul 24, 2013 at 11:50 AM, pg noob wrote: > In PostgreSQL 8.4... > > I am wondering if autovacuum will periodically rebuild indexes? It doesn't rebuild indexes, it marks empty index pages for reuse. > If not, how advisable is it to reindex periodically? Here described the recommendations and explanations for 8.4 http://www.postgresql.org/docs/8.4/static/routine-reindex.html > After the problem was discovered we found that a reindex would fail because > there were > duplicate ID values. > > Our thought is that if we had a scheduled cron job that would periodically > do a reindex this > corruption might have been caught sooner by detecting the reindex failure. Periodical reindex is a very dubious technique to monitor database corruption. Honestly, I have never heard of any standard or recommended practice of doing it. However, there is a tool that pretends to do so (https://github.com/tvondra/pg_check), but I do not now what state it is currently and if it is production ready. > If a reindex is something that should be done frequently as part of regular > maintenance > why isn't there a mode of autovacuum that does this automatically? Or maybe > there is and > I just don't know about it..? It is not necessary to reindex to be a part of regular maintenance. The main goal of autovacuum is to effectively reuse space and to update statistics. If autovacuum is configured properly reindex is not required. 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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rule Question
On Wed, Jul 24, 2013 at 11:44 PM, Andrew Bartley wrote: > Hope this question is not too stupid but.. > > I am trying to do something like this > > create table cats (a text,b text); > > create rule cats_test as on update to cats do set a = new.b; > > Can i manipulate column "a" sort of like this... or is there a better way. > > I would like to do this as the construction of the new.b value is complex > and time consuming, so I just want to do it once. > > update cats > set b = something_complex_and_time_consuming(b); AFAIK, the 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 pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Speed up Switchover
On Thu, Jul 25, 2013 at 1:03 AM, TJ wrote: > I am looking for a way of speeding up the process of switching over of > severs. > At the moment we are switching over via the trigger file, reconfiguring our > applications, patching or updating the old primary and rsyncing the data > over to the old primary. > > I was wondering if there was an easier way to get the primary setup as a > secondary without having to rsync the data as it can take up to 10 hours. pg_rewind (https://github.com/vmware/pg_rewind) is what you need. >From its docs: pg_rewind is a tool for synchronizing a PostgreSQL data directory with another PostgreSQL data directory that was forked from the first one. The result is equivalent to rsyncing the first data directory (referred to as the old cluster from now on) with the second one (the new cluster). The advantage of pg_rewind over rsync 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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Speed up Switchover
On Thu, Jul 25, 2013 at 11:00 PM, Samrat Revagade wrote: >>> secondary without having to rsync the data as it can take up to 10 hours. >> >> pg_rewind (https://github.com/vmware/pg_rewind) is what you need. > > But I think it has a problem regarding the hint bits which Robert Hass > pointed out. > You can still solve hint bit problem by enabling new checksum feature, But > you have to face consequences such as performance overhead. Or, if I understand it correct, you can just make sure that there is no activity on the old master. Right? > > 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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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/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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fastest Index/Algorithm to find similar sentences
On Sat, Jul 27, 2013 at 10:04 AM, Janek Sendrowski wrote:
> If I'm searching for a sentence like "The tiger is the largest cat species"
> for example.
> I can only find the sentences, which include the words "tiger, largest, cat,
> species", but I also like to have the sentences with only three or even two
> of these words.
You can use & (AND), | (OR), and ! (NOT) operators in tsquery, so you
can achieve what you want just like this:
[local]:5432 grayhemp@grayhemp=# select to_tsquery('tiger | largest |
cat | species') @@ 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/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: gray-hemp
Jabber: gray...@gmail.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] Installing 9.2 on Ubuntu from packages: what is the current recommendation?
On Fri, Aug 2, 2013 at 4:34 AM, Tim Bowden wrote: > Normally I install from source and create dummy packages as needed to > satisfy dependencies, however I had an attack of the cbf's and decided > to go looking for packages for Ubuntu 13.04 raring. I discovered > apt.postgresql.org only does LTS releases. Is this the long term plan, > or will intermediate releases also be supported in the future? > > Are packages for 13.04 being done by anyone? raring-backports doesn't > have anything (yet?). Here is the answer http://askubuntu.com/a/289388 > > 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: 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Exit code -1073741819
On Tue, Aug 6, 2013 at 4:17 PM, Carlos Henrique Reimer wrote: > I have tried to drop the index and the reindex procedure but both fail with > the same exit code. > > Copied the data directory to another partition on same HD but same results. > > Next change window will install PG 8.2.23 in another Windows box and copy > the data directory to the new box. > > Hope the error will not be propagated to the new box. If it wont help try to find out which rows lead to the failure, and copy your data from this table to a new one with the same structure filtering this rows. Then drop the old one and rename the new one. You might also need to drop all the FKs preliminary before doing this and restore them after. To find out which rows are bad use manual binary search (http://en.wikipedia.org/wiki/Binary_search_algorithm) by PK. To copy data use CREATE TABLE newone (LIKE ...) and then INSERT INTO newone SELECT ... WHERE id NOT IN (...). > > > Reimer > > > On Mon, Aug 5, 2013 at 10:42 AM, Adrian Klaver > wrote: >> >> On 08/05/2013 06:24 AM, Carlos Henrique Reimer wrote: >>> >>> Hi, >>> >>> Yes, I agree with you that it must be upgraded to a supported version >>> but as the developer has not homologated the system to some new PG >>> versions yet I need to find out some way to fix it with 8.2. >>> >>> Will try to install PG in another windows box, copying the data >>> directories over the network and see if I can at least take a pg_dump >>> from the database as it is currently not possible. >>> >>> Another possibility is to copy the data directory from the windows box >>> to a linux with PG 8.2 and start the database there, does this approach >>> has any possibility of success? >> >> >> No. The files are not binary compatible across OS and architectures. >> >> You mentioned that creating indexes on this table fails. >> >> Have you tried reindexing 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> >> >> >> >> -- >> Adrian Klaver >> adrian.kla...@gmail.com > > > > > -- > Reimer > 47-3347-1724 47-9183-0547 msn: carlos.rei...@opendb.com.br -- 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hierarchical numeric data type
On Tue, Aug 6, 2013 at 2:36 PM, Derek Poon wrote: > The performance impact of the enhanced comparator would probably be > negligible, compared to I/O bottlenecks. A bigger issue would be backwards > compatibility, especially for ltrees with existing btree indexes. > > Feedback? 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 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Exit code -1073741819
On Wed, Aug 7, 2013 at 2:46 PM, Carlos Henrique Reimer wrote: > Could finally fix it. Used the binary search approach to identify the wrong > tuples and removed them by ctid, 9 rows were removed and all of them > belonged to the same block. It is good. I still highly recommend to recreate the table, because the corruption might implicitly affect page headers too. > I believe it is not easy to identify the root cause for the corruption but > does any one have some directions I could follow to identify the root cause > in order to prevent it to happen again? Check logs, both system and postgres, for suspicious activity, find out if there were any power problems, server resets, etc. Upgrade your cluster to the latest version first of all, install a RAID controller with BBU, perform periodical SQL backups, and the PITR backups to be able to restore on a particular 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to avoid Force Autovacuum
On Wed, Aug 7, 2013 at 2:46 AM, Vishalakshi Navaneethakrishnan wrote: > Daily once we are executing "Vacuum Freeze analyze" -- To prevent > transaction id wraparound > using this command > vacuumdb -F -z -h localhost -U postgres dbname It is not necessary to do. Autovacuum does it itself where and when needed. > Even sometimes autovacuum running on the databases and increase the load > (Above 200) very much and the server was unresponsive > > I have seen the autovacum worker process in top command, > While i executing pg_stat_activity as postgres user, 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.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 subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pl/Python runtime overhead
On Wed, Aug 7, 2013 at 7:43 AM, Seref Arikan
wrote:
> When a pl/python based function is invoked, does it keep a python runtime
> running across calls to same function? That is, if I use connection pooling,
> can I save on the python runtime initialization and loading costs?
You can use the following wrapping technique to cache function's body,
that will save you some resources and time. It stores the main() in SD
(session data) built-in object and retrieves it when stored, so
plpython does not need to process it every time stored function is
called.
CREATE OR REPLACE FUNCTION some_plpython_function()
RETURNS integer
LANGUAGE plpythonu
AS $function$
""" An example of a function's body caching and error handling """
sdNamespace = 'some_plpython_function'
if sdNamespace not in SD:
def main():
""" The function is assumed to be cached in SD and reused """
result = None
# Do whatever you need here
return result
# Cache body in SD
SD[sdNamespace] = main
try:
return SD[sdNamespace]()
except Exception, e:
import traceback
plpy.info(traceback.format_exc())
$function$;
I can also recommend you to cache query plans, as plpython does not do
it itself. The code below also works with SD to store prepared plans
and retrieve them. This allows you to avoid preparing every time you
are executing the same query. Just like plpgsql does, but manually.
if SD.has_key('%s_somePlan' % sdNamespace):
somePlan = SD['%s_planName' % sdNamespace]
else:
somePlan = plpy.prepare(...)
> Are there any documents/books etc you'd recommend to get a good
> understanding of extending postgres with languages like 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 from the trees.
AFAIK, this one is the best one
http://www.postgresql.org/docs/9.2/interactive/plpython.html.
--
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 subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Self referencing composite datatype
On Wed, Aug 7, 2013 at 4:57 AM, Sameer Thakur wrote: > I wanted to create a composite datatype to represent a Node. So it would > have a few attributes and an array of type Node which is the children of > this node. > create type Node as (r integer, s integer, children Node []); > But i get error type Node[] does not exist. I understand that Node is not > defined hence the error. > But how do i get around this problem? I just wonder how are you going to use this kind of types? In 9.3 you will be able to use foreign keys with arrays like it is describe here http://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ eg. create table node as ( id integer primary key, r integer, s integer, children integer[] element references node ); so you could download 9.3rc2 and experimant with it. Now (on <=9.2.x) you can create the table 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) 888-1979 gray...@gmail.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] 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-1979 gray...@gmail.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] How to avoid Force Autovacuum
On Wed, Aug 7, 2013 at 9:32 PM, Vishalakshi Navaneethakrishnan wrote: > cat /etc/issue > CentOS release 6.3 (Final) > > cat /proc/meminfo |grep Hugepagesize > Hugepagesize: 2048 kB Please show what commands below print. cat /proc/meminfo | grep -i huge cat /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 (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 triggers. > > > Or, instead of attempting to reference all child nodes from the parent, > reference the parent node from each child node. > That's been supported in PG versions like forever and can be queried fairly > efficiently using recursive CTE's since PG 9. That particular moment I thought it was about graphs. Later OP mentioned tree, so yes, it is better to use parent reference here. -- 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 subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to avoid Force Autovacuum
On Thu, Aug 8, 2013 at 11:18 AM, Kevin Grittner wrote: > There were some fixes for autovacuum problems in 9.2.3. Some other > fixes will be coming when 9.2.5 is released. Many of your problems > are likely to go away by staying up-to-date on minor releases. > > By setting this so high, you are increasing the amount of work > autovacuum will need to do when it does work on a table. A smaller > value tends to give less "bursty" performance. Also, any small, > frequently-updated tables may bloat quite a bit in 5 > transactions. > > Each autovacuum worker will allocate this much RAM. If all of your > autovacuum workers wake up at once, would losing 2GB for each one > from your cache cause a significant performance hit? (Since you > didn't say how much RAM the machine has, it's impossible to tell.) > > What does running this in psql this show?: > > \x on > select * from pg_database where datname = 'template0'; > select * from pg_stat_database where datname = 'template0'; In addition to Kevin's notes, I think it is also worth to look at the result of the query below. 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-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance of ORDER BY RANDOM to select random rows?
On Wed, Aug 7, 2013 at 7:01 PM, Victor Hooi wrote: > also seems to suggest that using ORDER BY RANDOM() will perform poorly on > Postgres. > > I'm just wondering if this is still the case? > > I just ran those benchmarks on my system (Postgres 9.2.4), and using ORDERY > BY RANDOM did not seem substantially to generating random integers in Python > and picking those out (and handling non-existent rows). > > Has Postgres's behaviour for ORDER BY RANDOM change sometime recently? Unfortunately, It has not. However, there always is a workaround. You can get a random results fast by WITH RECURSIVE query. WITH RECURSIVE r AS ( WITH b AS (SELECT min(id), max(id) FROM table1) ( SELECT id, min, max, array[]::integer[] AS a, 0 AS n FROM table1, b WHERE id > min + (max - min) * random() LIMIT 1 ) UNION ALL ( SELECT t.id, min, max, a || t.id, r.n + 1 AS n FROM table1 AS t, r WHERE t.id > min + (max - min) * random() AND t.id <> all(a) AND r.n + 1 < 10 LIMIT 1 ) ) SELECT t.id FROM table1 AS t, r WHERE r.id = t.id; The general idea is that we get a random value between min(id) and max(id) and then get the first row with id bigger than this value. Then we repeat until we get 10 of such rows, checking that this id has not been retrieved earlier. Surely, the probability of appearing one or another value in the result 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 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 subscription: http://www.postgresql.org/mailpref/pgsql-general
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 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 subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fastest Index/Algorithm to find similar sentences
On Sun, Aug 11, 2013 at 6:20 AM, Janek Sendrowski wrote:
> the ranking functions are nice, but if I search for one more word, which is
> not included in the sentence I'm searchingfor , It doesn't find the sentence.
I have already wrote you earlier about it. You can solve the problem
by using & (AND), | (OR), and ! (NOT) operators in tsquery.
select to_tsquery('tiger | largest | cat | species') @@
to_tsvector('The tiger is the largest cat');
?column?
--
t
The query contains one word more than the sentence (the word is
"species"), 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 changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance of ORDER BY RANDOM to select random rows?
On Sun, Aug 11, 2013 at 9:59 PM, Victor Hooi wrote: > Hmm, aha, so the ORDER BY RANDOM behaviour hasn't changed - just to confirm > - this means that Postgres will duplicate the table, add a new column, > generate random numbers for every record, then sort by that new column, > right? It doesn't duplicate the table, it sec scans it and uses top-N sort if we use limit, and memory or disc sort depending on the data size if we don't use limit. > I've just read the above anecdotally on the internet, but I'm curious if the > actual implementation is documented somewhere officially apart from the > source? Running the query through EXPLAIN didn't seem to tell me much > additional information. I can not say about official docs, but you will find a good sorting explanation here http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/ > @Sergey - Thanks for the tip about using WITH RECURSIVE. I'm actually doing > something similar in my application code in Django - basically take the max > id, then generate a random integer between 0 and max id. However, it is > dependent on how evenly distributed the 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 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 subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Strange message from pg_receivexlog
Hi all, My WAL archiving script based on pg_receivexlog reported the following error several days ago (just ignore everything before 'pg_receivexlog', it's a message my script generates). Thu Aug 15 18:33:09 MSK 2013 ERROR archive_wal.sh: Problem occured during WAL archiving: pg_receivexlog: could not send feedback packet: server closed the connection unexpectedly At the same time postgres reported this error in log: 2013-08-15 18:32:51 MSK 30945 postgres@[unknown] from [local] [vxid:53/0 txid:0] [streaming 2A97/6FA48000] LOG: terminating walsender process due to replication timeout Both pg_receivexlog and postgres run at the same machive, pg_receivexlog connects to postgres locally. /var/log/messages has absolutely nothing about it. I also have a hot standby on another machine connecting to the same master, but there is nothing strange in its logs either. Any thoughts 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 subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange message from pg_receivexlog
On Tue, Aug 20, 2013 at 2:10 AM, Magnus Hagander wrote: > It looks like something outside postgres or pg_receivexlog did > terminate the connection. pg_receievexlog noticed it was closed, > whereas postgres waited for the timeout (but probably would've noticed > if it had actually had some other data to send maybe?). Do you have > some iptables connection tracking or something like that which could > be interfering? AFAIU, just standard things: -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --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 (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 subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Strange message from pg_receivexlog
On Wed, Aug 21, 2013 at 5:09 AM, Fujii Masao wrote: >> Thu Aug 15 18:33:09 MSK 2013 ERROR archive_wal.sh: Problem occured >> during WAL archiving: pg_receivexlog: could not send feedback packet: >> server closed the connection unexpectedly >> >> At the same time postgres reported this error in log: >> >> 2013-08-15 18:32:51 MSK 30945 postgres@[unknown] from [local] >> [vxid:53/0 txid:0] [streaming 2A97/6FA48000] LOG: terminating >> walsender process due to replication timeout > > Is the value of replication_timeout sufficiently-larger than the > status-interval > of pg_receivexlog? The replication_timeout is 60s. The archive_wal.sh (script-wrapper around pg_receivexlog) reports its status straight away as it falls with an error. Below is the explanation of how it works. This is the core of archive_wal.sh: ( flock -xn 543 || exit 0 result=$($PGRECEIVEXLOG -n -D $WAL_ARCHIVE_DIR 2>&1) || \ die "Problem occured during WAL archiving: $result." ) 543>$WAL_LOCK_FILE And it is set to run by cron once a minute reporting me by email on occasions: MAILTO=gray...@gmail.com * * * * * /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-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 end loop; elsif then for select from where order by field3, field1 desc, field2 desc loop end loop; else for select from where order by field4 loop end loop; end if; that way for select from where order by case when then @>field1 @ then @field1 @>field2 else @ end loop; It looks better, doesn't it? Also it provides Oracle like OVER PARTITION effect select * from ( values (1.2, '2007-11-23 12:00'::timestamp, true), (1.4, '2007-11-23 12:00'::timestamp, true), (1.2, '2007-11-23 12:00'::timestamp, false), (1.4, '2007-01-23 12:00'::timestamp, false), (3.5, '2007-08-31 13:35'::timestamp, false) ) _ order by @column3 else @>column2 @ conditional_ordering.sql Description: Binary data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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 creates operators > > > > @< - ascending ordering > > @> - descending ordering > > Thank you for the advice. I've put it down in my organizer. -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Wrong rows count estimation (explain, gist, tsearch)
Hi, community
I have a table containing column for FTS and an appropriate index:
zzz=# \d search_table
...
obj_tsvector | tsvector |
not null default ''::tsvector
...
"i_search_table__tsvector_1" gist (obj_tsvector) WHERE obj_status_did = 1
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 faster, and every time
when obj_tsvector is used estimation rows count is extremely differ
from actual (eg. 6821 vs 372012). I played with SET STATISTICS but
have no success.
zzz=# EXPLAIN ANALYZE SELECT count(1) FROM search_table WHERE
obj_status_did = 1 AND obj_tsvector @@ (make_tsquery('(музыка)',
'utf8_russian'));
QUERY PLAN
---
Aggregate (cost=25226.63..25226.64 rows=1 width=0) (actual
time=14832.455..14832.455 rows=1 loops=1)
-> Bitmap Heap Scan on search_table (cost=465.16..25209.57
rows=6821 width=0) (actual time=3202.390..14731.096 rows=371026
loops=1)
Recheck Cond: (obj_status_did = 1)
Filter: (obj_tsvector @@ '''музыка'''::tsquery)
-> Bitmap Index Scan on i_search_table__tsvector_1
(cost=0.00..463.45 rows=6821 width=0) (actual 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 problem? Thanx.
--
Regards,
Sergey Konoplev
--
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] 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 |
> not null default ''::tsvector
> ...
> "i_search_table__tsvector_1" gist (obj_tsvector) WHERE obj_status_did = 1
>
>
> 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 faster, and every time
> when obj_tsvector is used estimation rows count is extremely differ
> from actual (eg. 6821 vs 372012). I played with SET STATISTICS but
> have no success.
>
> zzz=# EXPLAIN ANALYZE SELECT count(1) FROM search_table WHERE
> obj_status_did = 1 AND obj_tsvector @@ (make_tsquery('(музыка)',
> 'utf8_russian'));
>
> QUERY PLAN
> ---
> Aggregate (cost=25226.63..25226.64 rows=1 width=0) (actual
> time=14832.455..14832.455 rows=1 loops=1)
> -> Bitmap Heap Scan on search_table (cost=465.16..25209.57
> rows=6821 width=0) (actual time=3202.390..14731.096 rows=371026
> loops=1)
> Recheck Cond: (obj_status_did = 1)
> Filter: (obj_tsvector @@ '''музыка'''::tsquery)
> -> Bitmap Index Scan on i_search_table__tsvector_1
> (cost=0.00..463.45 rows=6821 width=0) (actual 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 problem? Thanx.
>
> --
> Regards,
> Sergey Konoplev
>
--
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/
--
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] 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 faster, and every time >> when obj_tsvector is used estimation rows count is extremely differ >> from actual (eg. 6821 vs 372012). I played with SET STATISTICS but >> have no success. > > 8.3 has just a stub estimator for @@. You might have better results > with 8.4. In the particular example you're showing, though, I don't > think the poor rowcount estimate is making any difference to the > plan choice. Thanx, Tom. Will try 8.4 > > regards, tom lane > -- Regards, Sergey Konoplev -- PostgreSQL articles in english & russian http://gray-hemp.blogspot.com/search/label/postgresql/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best practices for effective_io_concurrency
Hi, All I read documentation (http://www.postgresql.org/docs/8.4/interactive/runtime-config-resource.html) and googled effective_io_concurrency but have not found any expanded explanation of what it actually is. I feel it rater significant for PG performance and would like to ask gurus to provide 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 -- 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] 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 are hoping. Currently the code only > kicks in when you're doing a Bitmap Heap Scan, which is really helpful for > them, but of no help for any other type of query. And I've only seen it > actually work at all on Linux. It might work on BSD and Mac OS X systems, > certainly doesn't do anything on Solaris and Windows. Thanx for the details. > >> 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? > > That's is almost all correct for 8.4. The settings mentioned there that > start with the name "edb" are not available to tweak > (edb_prefetch_indexscans and edb_enable_icach) in standard PostgreSQL, the > rest of it is good background and advice. The basic idea is that you start > with setting the value to the number of working drives in the disk array the > database is on and see if I/O performance goes up and/or query speed drops > afterwards. If it does you might try further increases beyond that even. > > As for why there isn't a better tuning guide than just those simple > guidelines, it's not that easy to show a situation where the type of bitmap > scan this parameter impacts is used on a generated data set, even though > it's not that uncommon in real-world data. It's hard both to make generic > suggestions here and to even demonstrate the feature at work. Well wouldn't using loosy indexes be the reason to increase this parameter? And would it somehow affect (I mean negative) another queries which doesn't lead to Bitmap Heap Scans? -- Regards, Sergey Konoplev -- PostgreSQL articles in english & russian http://gray-hemp.blogspot.com/search/label/postgresql/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Index Scan/Bitmap Index Scan for queries with FTS and ORDER+LIMIT
Hi, All
Well what do we have?
8 core, 32 GB, RAID 10, CentOS 5.2, Pg 8.3
A query using tsearch in WHERE block with ORDER and LIMIT:
select * from test_table where obj_tsvector @@
make_tsquery('some_words', 'utf8_russian') and obj_status_did = 1
order by obj_created desc limit 10;
Two indexes - one for FTS, another for simple scan:
"i_test_table__tsvector_1" gist (obj_tsvector) WHERE obj_status_did = 1
"i_test_table__created" btree (obj_created) WHERE obj_status_did = 1
It's clear that using i_test_table__created index is better when
querying words occurring frequently in test_table and
i_test_table__tsvector_1 in opposite case. But with enable_indexscan
turned on planner force to use i_test_table__created that is worth for
querying sparce words.
Is there a way (or workaround) to make it use i_test_table__created
for frequent and i_test_table__tsvector_1 for sparse words? May be
some PG internal that would give us a hint whether the words is
frequent or not?
Here is a test that reflects the problem:
test_db=# set enable_indexscan to on;
SET
test_db=# explain analyze select * from test_table where obj_tsvector
@@ make_tsquery('frequent_words', 'utf8_russian') and obj_status_did =
1 order by obj_created desc limit 10;
QUERY PLAN
-
Limit (cost=0.00..2843.83 rows=10 width=621) (actual
time=0.830..6.360 rows=10 loops=1)
-> Index Scan Backward using i_test_table__created on test_table
(cost=0.00..2235820.48 rows=7862 width=621) (actual time=0.829..6.355
rows=10 loops=1)
Filter: (obj_tsvector @@ '''frequent_words'''::tsquery)
Total runtime: 6.407 ms
(4 rows)
test_db=#
test_db=# set enable_indexscan to off;
SET
test_db=# explain analyze select * from test_table where obj_tsvector
@@ make_tsquery('frequent_words', 'utf8_russian') and obj_status_did =
1 order by obj_created desc limit 10;
ERROR: canceling statement due to statement timeout
test_db=#
test_db=# set enable_indexscan to on;
SET
test_db=# explain analyze select * from test_table where obj_tsvector
@@ make_tsquery('sparse_words', 'utf8_russian') and obj_status_did = 1
order by obj_created desc limit 10;
ERROR: canceling statement due to statement timeout
test_db=#
test_db=# set enable_indexscan to off;
SET
test_db=# explain analyze select * from test_table where obj_tsvector
@@ make_tsquery('sparse_words', 'utf8_russian') and obj_status_did = 1
order by obj_created desc limit 10;
QUERY PLAN
-
Limit (cost=29038.86..29038.89 rows=10 width=621) (actual
time=344.218..344.223 rows=10 loops=1)
-> Sort (cost=29038.86..29058.52 rows=7862 width=621) (actual
time=344.217..344.220 rows=10 loops=1)
Sort Key: obj_created
Sort Method: top-N heapsort Memory: 43kB
-> Bitmap Heap Scan on test_table (cost=469.20..28868.97
rows=7862 width=621) (actual time=292.314..344.176 rows=21 loops=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=2208 loops=1)
Index Cond: (obj_tsvector @@ '''sparse_words'''::tsquery)
Total runtime: 344.289 ms
(10 rows)
--
Regards,
Sergey Konoplev
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
