Re: [GENERAL] Re: ERROR: collation "latin1_general_ci" for encoding "UTF8" does not exist

On 08/27/2015 10:51 AM, ss wrote: Thanks for the reply Adrian. I have been given mysql schema and i have to create table using postgres..this is the information I have. Could u still help me ? If not then please let me know precisely that what should I ask the task giver in order to make you und

Re: [GENERAL] Does PLPythonU support COPY table FROM string?

On 08/28/2015 10:12 AM, Dominik Czarnota wrote: Oh... I have copied stacktrace generated from Python script which connects to the db using psycopg2 driver, so that's where this misleading psycopg2 error came from... About the list origin - I have to calculate a lot of stuff for each stock on sto

Re: [GENERAL] wal files stay in the pg_xlog dir

On 08/28/2015 01:59 PM, kingl wrote: Hi Adrian Thank you for your prompt reply. For more in depth information take a look here: http://www.postgresql.org/docs/9.4/interactive/wal-configuration.html which deals with the WAL configuration settings and explains what you are seeing. To get up t

Re: [GENERAL] wal files stay in the pg_xlog dir

Hi Adrian Thank you for your prompt reply. In the pg_xlog there are 2,015 wal files now. repmgr recommends to keep 5000 wal files however for our env that would be an overkill so i changed it to 2000. the other issue is that the standby node has only 1345 wal files in the pg_xlog, i thought tha

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 1:23 PM Jeff Janes wrote: > You should RESET the autovacuum_vacuum_scale_factor for the table. You > don't want it to be vacuumed aggressively, just autoanalyzed aggressively. > Sorry if my copy-paste error led you astray on that. > No problem, done, thank you. There is

Re: [GENERAL] wal files stay in the pg_xlog dir

On 08/28/2015 01:07 PM, kingl wrote: To whom it may concern: We have a 2 nodes postgres cluster, postgres server v9.3.8 and repmgr is used to enable the cluster function. barman v1.4.1 is used to take backup of the master postgres node. everything seems to be working except the wal files in pg_

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 12:25 PM, Steve Kehlet wrote: > On Fri, Aug 28, 2015 at 12:10 PM Jeff Janes wrote: > >> Did you change the system-wide autovacuum_analyze_scale_factor? If so, >> don't do that. You can use a table's storage parameters to set a custom >> autovacuum_analyze_scale_factor j

[GENERAL] wal files stay in the pg_xlog dir

To whom it may concern: We have a 2 nodes postgres cluster, postgres server v9.3.8 and repmgr is used to enable the cluster function. barman v1.4.1 is used to take backup of the master postgres node. everything seems to be working except the wal files in pg_xlog on node1 keeps accumulating. ther

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 11:18 AM Tom Lane wrote: > It looks like a VACUUM will do the cleanup during the first ginbulkdelete > call, so you could probably handle this by running a manual "VACUUM > VERBOSE" with the smallest possible maintenance_work_mem, and canceling it > as soon as you see some

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 10:27 AM Tom Lane wrote: > Steve Kehlet writes: > > Yeah head scratch. That is really weird. Still gathering data, any way I > > can see for sure when these cleanup cycles are occurring? > > contrib/pgstattuple has a function that can report the current size of the > pend

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 12:10 PM Jeff Janes wrote: > Did you change the system-wide autovacuum_analyze_scale_factor? If so, > don't do that. You can use a table's storage parameters to set a custom > autovacuum_analyze_scale_factor just for individual tables. So just the > table with the troub

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 11:06 AM, Steve Kehlet wrote: > On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes wrote: > >> > Vacuum is overkill (and can be extremely slow to run a large gin index), >> you just need to get it to autoanalyze by changing the per-table setting of >> "autovacuum_vacuum_scale_f

Re: [GENERAL] Does PLPythonU support COPY table FROM string?

Oh... I have copied stacktrace generated from Python script which connects to the db using psycopg2 driver, so that's where this misleading psycopg2 error came from... About the list origin - I have to calculate a lot of stuff for each stock on stock exchange market. Each calculation requires quot

Re: [GENERAL] Does PLPythonU support COPY table FROM string?

I am launching it from postgres plpythonu function (postgres version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit). The error can be reproduced using the SQL below: DROP TABLE IF EXISTS test; CREATE TABLE test(field1 integer, field2 integer); CRE

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 11:06 AM, Steve Kehlet wrote: > On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes wrote: > > > >> Vacuum is overkill (and can be extremely slow to run a large gin index), >> you just need to get it to autoanalyze by changing the per-table setting of >> "autovacuum_vacuum_scal

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

Steve Kehlet writes: > On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes wrote: >> Note that a manual ANALYZE will *not* clear the pending list, it has to be >> an autoanalyze. > This is a brain bender, I didn't know there were differences, and this eats > away a little bit at my confidence in underst

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 10:11 AM, Tom Lane wrote: > Steve Kehlet writes: > > This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 > > hours) slowness with UPDATEs on a table that has a GIN index on a JSONB > > column. During these episodes, UPDATEs that normally take < 1sec ta

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 10:42 AM Jeff Janes wrote: > Or what I usually do in a case like this is clone the database to a >>> test/QA server then run pg_upgrade to get that running on 9.5, then hope >>> what I learn transfers back to production. >> >> I'll save this great idea. > But the symptom

Re: [GENERAL] [R] Issues with RPostgres

On 08/27/2015 02:19 PM, Abraham Mathew wrote: "test" is not in the postgres database. In fact, there is no table or column named "test" The user is "postgres" and the dbname is also "postgres" The only part of your original post that hit the list was what John excerpted in his reply. Not su

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

Jeff Janes writes: > That is hard to do in 9.4. PostgreSQL 9.5 will add the gin_metapage_info > function to the pageinspect module which makes it easier. pgstatginindex() is there in 9.4 ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

I wrote: > BTW, I think your query is probably missing some cases: > ( blockingl.transactionid=blockedl.transactionid > OR > (blockingl.relation=blockedl.relation AND > blockingl.locktype=blockedl.locktype) > ) > This supposes that locks of different strengths don't block each other, >

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 10:00 AM, Steve Kehlet wrote: > This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 > hours) slowness with UPDATEs on a table that has a GIN index on a JSONB > column. During these episodes, UPDATEs that normally take < 1sec take > upwards of 2-4 minute

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

Steve Kehlet writes: > On Fri, Aug 28, 2015 at 10:11 AM Tom Lane wrote: >> Hm ... have you tried checking pg_locks to see if they're blocked on >> something identifiable? > Yes, I should have mentioned that, I have a cronjob going every minute > dumping out [blocked/blocking queries]( > https://

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

Steve Kehlet writes: > Yeah head scratch. That is really weird. Still gathering data, any way I > can see for sure when these cleanup cycles are occurring? contrib/pgstattuple has a function that can report the current size of the pending list for a GIN index. If you were to capture that every s

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

On Fri, Aug 28, 2015 at 10:11 AM Tom Lane wrote: > Steve Kehlet writes: > > This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 > > hours) slowness with UPDATEs on a table that has a GIN index on a JSONB > > column. During these episodes, UPDATEs that normally take < 1sec tak

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

Steve Kehlet writes: > This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 > hours) slowness with UPDATEs on a table that has a GIN index on a JSONB > column. During these episodes, UPDATEs that normally take < 1sec take > upwards of 2-4 minutes, and all finish simultaneously,

[GENERAL] how to investigate GIN fast updates and cleanup cycles?

This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4 hours) slowness with UPDATEs on a table that has a GIN index on a JSONB column. During these episodes, UPDATEs that normally take < 1sec take upwards of 2-4 minutes, and all finish simultaneously, like they were all blocked on

Re: [GENERAL] Execute DDL across multiple servers in an automated manner

2015-08-28 13:40 GMT-03:00 Adrian Klaver : > On 08/28/2015 07:09 AM, Anderson Abreu wrote: > >> >> 2015-08-28 10:55 GMT-03:00 Adrian Klaver > >: >> >> On 08/28/2015 06:35 AM, Anderson Abreu wrote: >> >> Hi all, >> >> I usePostgreSQL9.4 >> >>

Re: [GENERAL] Execute DDL across multiple servers in an automated manner

On 08/28/2015 07:09 AM, Anderson Abreu wrote: 2015-08-28 10:55 GMT-03:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>: On 08/28/2015 06:35 AM, Anderson Abreu wrote: Hi all, I usePostgreSQL9.4 I'm looking for somepackage/library/plugintoexecute DDLacross

Re: [GENERAL] Does PLPythonU support COPY table FROM string?

On 08/28/2015 08:32 AM, Dominik Czarnota wrote: I am launching it from postgres plpythonu function (postgres version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit). The error can be reproduced using the SQL below: DROP TABLE IF EXISTS test; CREAT

Re: [GENERAL] Does PLPythonU support COPY table FROM string?

On 08/28/2015 08:32 AM, Dominik Czarnota wrote: I am launching it from postgres plpythonu function (postgres version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit). The error can be reproduced using the SQL below: DROP TABLE IF EXISTS test; CREAT

Re: [GENERAL] Does PLPythonU support COPY table FROM string?

On 08/28/2015 05:58 AM, Dominik Czarnota wrote: Hello, Is there any possibility to make COPY from list of records in PLPythonU? I've tried to simply call `plpy.execute(query)` with such query: COPY table (field1, field2, field3) FROM STDIN DELIMITER','; val1,val2,val3 \. But it fails with not

Re: [GENERAL] UPDATE an updatable view

> Just for the sake of completeness... > > If the value (empname in the above example) can be NULL, the compare does not work, because > > SELECT NULL = NULL > > returns NULL which is treated as FALSE. > > But I am sure you know this :-) > > > HTH, > > Ladislav Lenart > > __

Re: [GENERAL] UPDATE an updatable view

On 27.8.2015 18:35, David Nelson wrote: >>> So in the UPDATE statement, I only provided a value for last_user. >>> But the first test of the trigger function tests for a NULL value of >>> NEW.empname. Since I did not provide one, I was expecting it to be >>> NULL and an exception to be thrown. A

[GENERAL] Does PLPythonU support COPY table FROM string?

Hello, Is there any possibility to make COPY from list of records in PLPythonU? I've tried to simply call `plpy.execute(query)` with such query: COPY table (field1, field2, field3) FROM STDIN DELIMITER','; val1,val2,val3 \. But it fails with not so explicit error: ProgrammingError: (psycopg2.Pr

Re: [GENERAL] UPDATE an updatable view

On Fri, Aug 28, 2015 at 6:39 AM, Ladislav Lenart wrote: > > Hello. > > > On 27.8.2015 18:35, David Nelson wrote: > >>> So in the UPDATE statement, I only provided a value for last_user. But the > >>> first test of the trigger function tests for a NULL value of > >>> NEW.empname. Since > >>> I did

[GENERAL] Re: ERROR: collation "latin1_general_ci" for encoding "UTF8" does not exist

Thanks for the reply Adrian. I have been given mysql schema and i have to create table using postgres..this is the information I have. Could u still help me ? If not then please let me know precisely that what should I ask the task giver in order to make you understand the question? (If I am right

Re: [GENERAL] [R] Issues with RPostgres

"test" is not in the postgres database. In fact, there is no table or column named "test" The user is "postgres" and the dbname is also "postgres" On Thu, Aug 27, 2015 at 4:13 PM, John McKown wrote: > On Thu, Aug 27, 2015 at 3:50 PM, Abraham Mathew > wrote: > >> >> Yeah, thought that could b

Re: [GENERAL] UPDATE an updatable view

On Thu, Aug 27, 2015 at 1:21 PM, Jerry Sievers wrote: > > David Nelson writes: > > >>> So in the UPDATE statement, I only provided a value for last_user. But the > >>> first test of the trigger function tests for a NULL value of > >>> NEW.empname. Since > >>> I did not provide one, I was expectin

[GENERAL] Define two factor authentication for Postgresql Server

Hello, I'm going to create two factor authentication for pgadmin server... I'm using postgresql 9.4 with pgadmin III on Linux Mint 17.2 32bit... I already have 1 password authentication but For better security, I just want to force 2 of them. The authentication factors could be any things(what user

Re: [GENERAL] Execute DDL across multiple servers in an automated manner

2015-08-28 10:55 GMT-03:00 Adrian Klaver : > On 08/28/2015 06:35 AM, Anderson Abreu wrote: > >> Hi all, >> >> I usePostgreSQL9.4 >> >> I'm looking for somepackage/library/plugintoexecute DDLacross multiple >> serversin an automated manner. >> > > This covers a lot of ground. Could you be more spec

Re: [GENERAL] Execute DDL across multiple servers in an automated manner

On 08/28/2015 06:35 AM, Anderson Abreu wrote: Hi all, I usePostgreSQL9.4 I'm looking for somepackage/library/plugintoexecute DDLacross multiple serversin an automated manner. This covers a lot of ground. Could you be more specific: 1) Do you have a preferred programmng/scripting language? 2

Re: [GENERAL] Execute DDL across multiple servers in an automated manner

In Linux, you can use Terminator http://gnometerminator.blogspot.com/p/introduction.html to execute a script simultaneously across multiple terminals. Alternatively, you can write a script that loops through host connections to execute the DDL. On Fri, Aug 28, 2015 at 9:35 AM, Anderson Abreu

[GENERAL] Execute DDL across multiple servers in an automated manner

Hi all, I use PostgreSQL 9.4 I'm looking for some package/library/plugin to execute DDL across multiple servers in an automated manner. Can do this with shellscript. I would like to know if this is the only way? Thanks & Regards --- Anderson Abreu andersonab...@gmail.com "The judoka is wh

Re: [GENERAL] relpages for pg_toast.* tables

In the spirit of "asking the question leads you to the answer", while ANALYZE doesn't update stats on toast tables, VACUUM does. So running VACUUM ANALYZE on the parent table updates all the relevant "relpages" slots and the space summary turns out passable guesstimates. P. On Fri, Aug 28, 2015 a

[GENERAL] relpages for pg_toast.* tables

I've been trying to write up a "pg_total_relation_size()" replacement that just uses the system tables (sum up relpages for tables, indexes and toast tables), thereby avoiding the overhead of running stat() on thousands of filesystem tables, but I've come up against what seems to be an unsuperable

Re: [GENERAL] UPDATE an updatable view

Hello. On 27.8.2015 18:35, David Nelson wrote: >>> So in the UPDATE statement, I only provided a value for last_user. But the >>> first test of the trigger function tests for a NULL value of >>> NEW.empname. Since >>> I did not provide one, I was expecting it to be NULL and an exception to >>> be