Re: [GENERAL] UPDATE an updatable view

[Ladislav Lenart]

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 thrown. Am I just misunderstanding how things work? Is there any way to
>>> test to see if the UPDATE statement contained a reference to empname? If the
>>> answer is no, I can certainly work with that, but before I go on I wanted
>>> to make sure I wasn't missing anything.
>>
>>
>> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted
> and the NEW one inserted with the OLD values unless they where explicitly
> changed. So
> 
> Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
> a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
> called MVCC. Thanks for setting me straight!
> 
>> in your test  NEW.empname is still 'John Doe' and therefore NOT NULL. That
> test would only work if someone explicitly set empname = NULL in the update. 
> If
> you want to check whether the value has not been changed then:
>>
>> IF NEW.empname = OLD.empname THEN
> 
> That's exactly the solution I hit on. Back to work, and thanks again.

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




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] relpages for pg_toast.* tables

[Paul Ramsey]

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 problem.

The `relpages` data for the toast tables doesn't seem to get updated.
Not when I run a general 'ANALYZE' call, and when I specifically call
ANALYZE on the toast tables, it tells me "WARNING:  skipping
"pg_toast_4597532" --- cannot analyze non-tables or special system
tables". Well, OK then.

Presumably this is by design, but it makes it impossible to get a true
guesstimate (as of latest ANALYZE) of size. Are there any known
workarounds?

Thanks,
P


-- 
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] relpages for pg_toast.* tables

[Paul Ramsey]

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 at 6:16 AM, Paul Ramsey  wrote:
> 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 problem.
>
> The `relpages` data for the toast tables doesn't seem to get updated.
> Not when I run a general 'ANALYZE' call, and when I specifically call
> ANALYZE on the toast tables, it tells me "WARNING:  skipping
> "pg_toast_4597532" --- cannot analyze non-tables or special system
> tables". Well, OK then.
>
> Presumably this is by design, but it makes it impossible to get a true
> guesstimate (as of latest ANALYZE) of size. Are there any known
> workarounds?
>
> Thanks,
> P


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Anderson Abreu]

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 what has: humility to learn what you teach, patience to
teachwhat
they learned to their fellow man and faith to believe in what you don't
understand. Learn a little more every day and use it every day for the good
"(Jigoro Kano)

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

[Melvin Davidson]

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 
wrote:

> 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 what has: humility to learn what you teach, patience to
> teachwhat they learned to their fellow man and faith to believe in what you
> don't understand. Learn a little more every day and use it every day for
> the good "(Jigoro Kano)
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

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

[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 specific:

1) Do you have a preferred programmng/scripting language?

2) Are you trying to schema versioning or are you rolling out complete 
packages?


3) What Oses are you dealing with and what method of remote connection?

4) Are the servers all the same version of Postgres(or are they even all 
Postgres servers), so do you have to take into account version differences?





Can do thiswithshellscript.

I would like to knowifthis is the only way?


No, that is the problem:)




Thanks & Regards


---

Anderson Abreu
andersonab...@gmail.com 

"Thejudokaiswhathas:humilitytolearnwhatyouteach,patienceto teachwhat
theylearnedtotheir fellow manandfaithtobelieve in whatyou don't
understand.Learna little more every dayanduse it every dayfor the
good"(Jigoro Kano)



--
Adrian Klaver
adrian.kla...@aklaver.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] Execute DDL across multiple servers in an automated manner

[Anderson Abreu]

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 specific:
>
> 1) Do you have a preferred programmng/scripting language?
>

No, I don't have a preferred language. I thought in a package similar the
pgbouncer.


>
> 2) Are you trying to schema versioning or are you rolling out complete
> packages?
>

Well, a schema versioning is interesting. Exists any package to postgresql?


>
> 3) What Oses are you dealing with and what method of remote connection?
>

My OS is ubuntu 14.04 in two hundred servers

>
> 4) Are the servers all the same version of Postgres(or are they even all
> Postgres servers), so do you have to take into account version differences?
>

All servers are in same version, PostgreSQL 9.4.

Thanks & Regards,
Anderson Abreu


>
>
>
>> Can do thiswithshellscript.
>>
>> I would like to knowifthis is the only way?
>>
>
> No, that is the problem:)
>
>
>>
>> Thanks & Regards
>>
>>
>> ---
>>
>> Anderson Abreu
>> andersonab...@gmail.com 
>>
>> "Thejudokaiswhathas:humilitytolearnwhatyouteach,patienceto teachwhat
>> theylearnedtotheir fellow manandfaithtobelieve in whatyou don't
>> understand.Learna little more every dayanduse it every dayfor the
>> good"(Jigoro Kano)
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

[GENERAL] Define two factor authentication for Postgresql Server

[Nima Azizzadeh]

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 has,what user knows,where user is or what user is).
for example:
The first factor is "password(what user knows)" and the second is "USB
device(what user has)". I need to force Postgresql to check both for
authenticate user and connect him to the server. I send you a screenshot
from pgAdmin server authenticate screen and I'm going to implement 2 factor
authentication for this. I not talking about OS authentication. All
authenticate operation should operate from Postgresql.

I already try this for login into pgAdmin through password and USB:
I installed pamusb pakages :

sudo apt-get install pamusb-tools libpam-usb

Although I can add devices on my pamusb config file :
pamusb-conf --add-device MyDevice

and I can define pamusb users. I added this lines to pamusb config between
 tags :
 MyDevice 

My guess : I think I should write module in /etc/pam.d and edit pg_hba.conf
file to define login method for local users :
local allall  pam pamservice=mypam
but I don't know how to write module to force both authentication methods
for this(both are required).

Any help would be appreciated...

-- 
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] UPDATE an updatable view

[David Nelson]

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 expecting it to be NULL and an exception
to
> >>> be thrown. Am I just misunderstanding how things work? Is there any
way to
> >>> test to see if the UPDATE statement contained a reference to empname?
If the
> >>> answer is no, I can certainly work with that, but before I go on I
wanted
> >>> to make sure I wasn't missing anything.
> >>
> >>
> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is
deleted and the NEW one inserted with the OLD values unless they where
explicitly changed. So
> >
> > Shoot, I went totally brain-dead on that one. I forgot that I'm
actually doing
> > a DELETE/INSERT, and the behaviour makes perfect sense in that light.
It's
> > called MVCC. Thanks for setting me straight!
>
> Huh?
>
> I think any DB platform regardless of how it does MVCC is going to leave
> existing fields as-is in an update if same fields aren't specified.
>
> This has nothing specifically to do with Postgres, MVCC, updatable views
> etc. IMO.
>

You are of course correct, but I could swear I've done an updatable view oin
another system in the past and could count on having the values for column
names that were not called in the UPDATE statement on the view as being
either
NULL or maybe undefined values. But I haven't done an updable view in
anything
other than PostgreSQL in so long that I most likely dreamed that up
(because,
as you seem to be saying, that wouldn't make much sense). So I humbly
retract
that part of my reply. Regardless, the DELETE/INSERT explanation makes it
all clear, and I've gotten the trigger function seemingly working the way
I need for it to. At least it's passed all 30 something tests I've thought
of to throw at it. Still testing though. And I'm sure the one test I fail to
think of will be the first thing the front-end developers throw at it. It
always works that way...

Regards

> >> in your test  NEW.empname is still 'John Doe' and therefore NOT NULL.
That test would only work if someone explicitly set empname = NULL in the
update. If you want to
> > check whether the value has not been changed then:
> >>
> >> IF NEW.empname = OLD.empname THEN
> >
> > That's exactly the solution I hit on. Back to work, and thanks again.
> >
> >>
> >>>
> >>> Thanks!
> >>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.kla...@aklaver.com
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
> p: 312.241.7800

Re: [GENERAL] [R] Issues with RPostgres

[Abraham Mathew]

"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 be an issue. Unfortunately, I've had issues
>> setting a normal user id and then having postgres identify the password
>> associated with it. Using postgres was the only thing that worked.
>>
>> ## CONNECT TO LOCALHOST DATABASE:
>> drv <- dbDriver("PostgreSQL")
>> con <- dbConnect(drv, dbname="postgres", host='localhost',
>>  port='5432', user='postgres', password='brothers')
>> dbDisconnect(con)
>>
>>
>>
> ​OK. So, looking at the above, you are saying that the user is "postgres"
> and that the table "test" is in the database "postgres" (which is likely
> also owned by "postgres"). I'm unsure from your response that this is
> correct. Is "test" in the "postgres" data base? If not, you might get away
> with:
>
> # replace proper-database in the following with the correct data base
> name, which contains the "test" table
> con <- dbConnect(drv, dbname="proper-database", host="localhost",
> port='5432', user='postgres', password='brothers')
>
>
> Normally, when I am logged in a myself, my code usually looks a bit like:
>
> dbname <- Sys.info['user']; # the data base name in PostgreSQL is the same
> as my login id
> con <- dbConnect(drv,dbname​=dbname); # connect to PostgreSQL on the local
> host as myself (implicit).
>  ds_summary(con, "test", vars=c("Age"), y=c("Class"))
>
> In my pg_hba.conf file, I have a line like:
>
> local   all all trust
>
> Which says that everyone coming in who are on the local host are
> automatically logged in as their Linux (in my case) id.
>
> Your code is connecting via TCPIP because you have the host= & port=
> parameters. This is not normally needed for users running on the same
> physical machine as the PostgreSQL data base server. So I'm too lazy to do
> it [grin].
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>



-- 


*Abraham MathewData Ninja and Statistical Modeler*



*Minneapolis, MN720-648-0108@abmathewksAnalytics_Blog
*

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

[ss]

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
then collation is used to sort the data) ..may be he mean sorting the data
on alphabetical order or something similar
On Aug 27, 2015 11:00 PM, "Adrian Klaver-4 [via PostgreSQL]" <
ml-node+s1045698n5863613...@n5.nabble.com> wrote:

> On 08/27/2015 06:47 AM, ss wrote:
>
> > I have started writting query since a day on postGreS my query is :
> >
> >  CREATE TABLE IF NOT EXISTS utilisateurs (
> >SEQU_NK   SERIAL PRIMARY KEY NOT NULL UNIQUE,
> >UTILISATEUR_NK bigint NOT NULL,
> >NOM varchar(25) COLLATE latin1_general_ci NOT NULL DEFAULT '',
> >PASSE varchar(40) COLLATE latin1_general_ci NOT NULL ,
> >RACCOURCI varchar(5) COLLATE latin1_general_ci NOT NULL,
> >DROITS varchar(4) COLLATE latin1_general_ci NOT NULL DEFAULT
> '' ,
> >
> > CONSTRAINT   UTILISATEUR_NK UNIQUE (UTILISATEUR_NK,NOM)
> >  ) ;
> >  COMMENT ON COLUMN utilisateurs.PASSE IS 'md5';
> >  COMMENT ON COLUMN utilisateurs.DROITS IS 'ceps';
> >
> > And the error is :
> >
> >  ERROR:  collation "latin1_general_ci" for encoding "UTF8" does not
> exist
> >  LINE 4:   NOM varchar(25) COLLATE latin1_general_ci NOT NULL
> DEFAULT...
> >^
> >  ** Error **
> >
> >  ERROR: collation "latin1_general_ci" for encoding "UTF8" does not
> exist
> >  SQL state: 42704
> >  Character: 143
> >
> > Could some one please help me ?
>
>  From what I can find latin1_general_ci is a MySQL collation:
>
> https://dev.mysql.com/doc/refman/5.1/en/charset-mysql.html
>
> so on that alone I would not expect it to work.
>
> What sort of collation are you trying to achieve?
>
> >
> >
> >
> > --
> > View this message in context:
> http://postgresql.nabble.com/ERROR-collation-latin1-general-ci-for-encoding-UTF8-does-not-exist-tp5863581.html
> > Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> >
> >
>
>
> --
> Adrian Klaver
> [hidden email] 
>
>
> --
> Sent via pgsql-general mailing list ([hidden email]
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.nabble.com/ERROR-collation-latin1-general-ci-for-encoding-UTF8-does-not-exist-tp5863581p5863613.html
> To start a new topic under PostgreSQL - general, email
> ml-node+s1045698n1843780...@n5.nabble.com
> To unsubscribe from ERROR: collation "latin1_general_ci" for encoding
> "UTF8" does not exist, click here
> 
> .
> NAML
> 
>




--
View this message in context: 
http://postgresql.nabble.com/ERROR-collation-latin1-general-ci-for-encoding-UTF8-does-not-exist-tp5863581p5863626.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] UPDATE an updatable view

[David Nelson]

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 not provide one, I was expecting it to be NULL and an exception
to
> >>> be thrown. Am I just misunderstanding how things work? Is there any
way to
> >>> test to see if the UPDATE statement contained a reference to empname?
If the
> >>> answer is no, I can certainly work with that, but before I go on I
wanted
> >>> to make sure I wasn't missing anything.
> >>
> >>
> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is
deleted
> > and the NEW one inserted with the OLD values unless they where
explicitly
> > changed. So
> >
> > Shoot, I went totally brain-dead on that one. I forgot that I'm
actually doing
> > a DELETE/INSERT, and the behaviour makes perfect sense in that light.
It's
> > called MVCC. Thanks for setting me straight!
> >
> >> in your test  NEW.empname is still 'John Doe' and therefore NOT NULL.
That
> > test would only work if someone explicitly set empname = NULL in the
update. If
> > you want to check whether the value has not been changed then:
> >>
> >> IF NEW.empname = OLD.empname THEN
> >
> > That's exactly the solution I hit on. Back to work, and thanks again.
>
> 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

Thanks Ladislav. To be able to capture updates to NULL, I ended up going
with

IF NEW.empname IS DISTINCT FROM OLD.empname THEN ...

[GENERAL] Does PLPythonU support COPY table FROM string?

[Dominik Czarnota]

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.ProgrammingError) spiexceptions.SyntaxError:
syntax error at or near "val1"
LINE 2: val1,val2,val3
^

However the same thing works in psql.

After some googling I found out that it doesn't work inside sql functions
because there is no STDIN (
http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
).


I've got working solution which was saving tmp file and then calling `COPY
table FROM 'path'` but that's not cool.

Did I miss something in plpythonu api? If not, where can I post "feature
request" for that?
Or can you point me place where I could dig into the code and implement it?



Dominik Czarnota

Re: [GENERAL] UPDATE an updatable view

[Igor Neyman]

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. Am I just misunderstanding how 
>>> things work? Is there any way to test to see if the UPDATE statement 
>>> contained a reference to empname? If the answer is no, I can 
>>> certainly work with that, but before I go on I wanted to make sure I 
>>> wasn't missing anything.
>>
>>
>> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is 
>> deleted
> and the NEW one inserted with the OLD values unless they where 
> explicitly changed. So
> 
> Shoot, I went totally brain-dead on that one. I forgot that I'm 
> actually doing a DELETE/INSERT, and the behaviour makes perfect sense 
> in that light. It's called MVCC. Thanks for setting me straight!
> 
>> in your test  NEW.empname is still 'John Doe' and therefore NOT NULL. 
>> That
> test would only work if someone explicitly set empname = NULL in the 
> update. If you want to check whether the value has not been changed then:
>>
>> IF NEW.empname = OLD.empname THEN
> 
> That's exactly the solution I hit on. Back to work, and thanks again.

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

___

Right.  And that's why you use very nice option provided by PG:

IF NEW.empname IS DISTINCT FROM OLD.empname THEN

which again you probably know :)

Regards,
Igor Neyman



-- 
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] UPDATE an updatable view

[David Nelson]

> 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
>
> ___
>
> Right.  And that's why you use very nice option provided by PG:
>
> IF NEW.empname IS DISTINCT FROM OLD.empname THEN
>
> which again you probably know :)
>
> Regards,
> Igor Neyman

Yep, I got there pretty quickiy. IS [NOT] DISTINCT FROM is pretty cool
stuff!

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

[Adrian Klaver]

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 so explicit error:
ProgrammingError: (psycopg2.ProgrammingError) spiexceptions.SyntaxError:
syntax error at or near "val1"
LINE 2: val1,val2,val3
 ^


The above is a psycopg2 error, so where are you running this and how?



However the same thing works in psql.

After some googling I found out that it doesn't work inside sql
functions because there is no STDIN (
http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
).


I've got working solution which was saving tmp file and then calling
`COPY table FROM 'path'` but that's not cool.

Did I miss something in plpythonu api? If not, where can I post "feature
request" for that?
Or can you point me place where I could dig into the code and implement it?



Dominik Czarnota



--
Adrian Klaver
adrian.kla...@aklaver.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] Does PLPythonU support COPY table FROM string?

[Adrian Klaver]

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;
CREATE TABLE test(field1 integer, field2 integer);

CREATE OR REPLACE FUNCTION example() RETURNS VOID AS
$$
 plpy.execute("COPY test (field1, field2) FROM STDIN
DELIMITER',';\n1,2\n\\.")
$$ LANGUAGE 'plpythonu';

select example();


Maybe there is a way to get into the underlying psycopg2 driver and call
some method that would do COPY FROM, but I can't find anything related
to it in the docs.


plpythonu does not use psycopg2. When I run your test code on 9.4.4 I get:

aklaver@test=> select example();
ERROR:  spiexceptions.SyntaxError: syntax error at or near "1"
LINE 2: 1,2
^
QUERY:  COPY test (field1, field2) FROM STDIN DELIMITER',';
1,2
\.
CONTEXT:  Traceback (most recent call last):
  PL/Python function "example", line 2, in 
plpy.execute("COPY test (field1, field2) FROM STDIN 
DELIMITER',';\n1,2\n\\.")

PL/Python function "example"


It is a similar error, but it is not coming from psycopg2, so I am still 
not sure how you got the error below?




2015-08-28 17:15 GMT+02:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>:

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 so explicit error:
ProgrammingError: (psycopg2.ProgrammingError)
spiexceptions.SyntaxError:
syntax error at or near "val1"
LINE 2: val1,val2,val3
  ^


The above is a psycopg2 error, so where are you running this and how?



However the same thing works in psql.

After some googling I found out that it doesn't work inside sql
functions because there is no STDIN (

http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
).


I've got working solution which was saving tmp file and then calling
`COPY table FROM 'path'` but that's not cool.

Did I miss something in plpythonu api? If not, where can I post
"feature
request" for that?
Or can you point me place where I could dig into the code and
implement it?



Dominik Czarnota



--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.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] Does PLPythonU support COPY table FROM string?

[Adrian Klaver]

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;
CREATE TABLE test(field1 integer, field2 integer);

CREATE OR REPLACE FUNCTION example() RETURNS VOID AS
$$
 plpy.execute("COPY test (field1, field2) FROM STDIN
DELIMITER',';\n1,2\n\\.")
$$ LANGUAGE 'plpythonu';

select example();


Maybe there is a way to get into the underlying psycopg2 driver and call
some method that would do COPY FROM, but I can't find anything related
to it in the docs.


The only thing I can think of is using io.BytesIO() to create an in 
memory file and then reading from that. I have done it using psycopg2, 
but not in plpythonu so I would mark this untested.


Otherwise, could you explain more where the list is coming from and it's 
size as there may be other ways to attack this.




2015-08-28 17:15 GMT+02:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>:

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 so explicit error:
ProgrammingError: (psycopg2.ProgrammingError)
spiexceptions.SyntaxError:
syntax error at or near "val1"
LINE 2: val1,val2,val3
  ^


The above is a psycopg2 error, so where are you running this and how?



However the same thing works in psql.

After some googling I found out that it doesn't work inside sql
functions because there is no STDIN (

http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
).


I've got working solution which was saving tmp file and then calling
`COPY table FROM 'path'` but that's not cool.

Did I miss something in plpythonu api? If not, where can I post
"feature
request" for that?
Or can you point me place where I could dig into the code and
implement it?



Dominik Czarnota



--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.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] Execute DDL across multiple servers in an automated manner

[Adrian Klaver]

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
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?


No, I don't have a preferred language. I thought in a package similar
the pgbouncer.


So something where you point at a database proxy and it would distribute 
the changes to all the databases behind it?


I don't know of such a thing, but maybe someone else does.




2) Are you trying to schema versioning or are you rolling out
complete packages?


Well, a schema versioning is interesting. Exists any package to postgresql?


I just finished a schema version change using Sqitch(http://sqitch.org/) 
 It is not Postgres specific, but that is what I am deploying against. 
My needs are simple and I am not deploying against two hundred servers. 
There may be way to make that work using Sqitch, but that would probably 
be a question for the Sqitch list:


https://groups.google.com/forum/#!forum/sqitch-users

Other schema version systems I know of are Alembic(Python) Rails(ruby).




3) What Oses are you dealing with and what method of remote connection?


My OS is ubuntu 14.04 in two hundred servers


Probably should take a look at one of the automated deployment packages:

Chef https://www.chef.io/chef/

Puppet https://puppetlabs.com/

Salt http://saltstack.com/

There are probably others, these are just the ones I know about. They 
are general purpose tools but have Postgres specific features. As an 
examples:


http://docs.saltstack.com/en/latest/ref/modules/all/salt.modules.postgres.html

https://github.com/puppetlabs/puppetlabs-postgresql#custom-resource-postgresql_psql




4) Are the servers all the same version of Postgres(or are they even
all Postgres servers), so do you have to take into account version
differences?


All servers are in same version, PostgreSQL 9.4.

Thanks & Regards,
Anderson Abreu




Can do thiswithshellscript.

I would like to knowifthis is the only way?


No, that is the problem:)



Thanks & Regards


---

Anderson Abreu
andersonab...@gmail.com 
>

"Thejudokaiswhathas:humilitytolearnwhatyouteach,patienceto teachwhat
theylearnedtotheir fellow manandfaithtobelieve in whatyou don't
understand.Learna little more every dayanduse it every dayfor the
good"(Jigoro Kano)



--
Adrian Klaver
adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.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] Execute DDL across multiple servers in an automated manner

[Anderson Abreu]

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
>>
>> 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?
>>
>>
>> No, I don't have a preferred language. I thought in a package similar
>> the pgbouncer.
>>
>
> So something where you point at a database proxy and it would distribute
> the changes to all the databases behind it?
>
> I don't know of such a thing, but maybe someone else does.
>
>
>>
>> 2) Are you trying to schema versioning or are you rolling out
>> complete packages?
>>
>>
>> Well, a schema versioning is interesting. Exists any package to
>> postgresql?
>>
>
> I just finished a schema version change using Sqitch(http://sqitch.org/)
> It is not Postgres specific, but that is what I am deploying against. My
> needs are simple and I am not deploying against two hundred servers. There
> may be way to make that work using Sqitch, but that would probably be a
> question for the Sqitch list:
>
> https://groups.google.com/forum/#!forum/sqitch-users
>
> Other schema version systems I know of are Alembic(Python) Rails(ruby).
>
>
>>
>> 3) What Oses are you dealing with and what method of remote
>> connection?
>>
>>
>> My OS is ubuntu 14.04 in two hundred servers
>>
>
> Probably should take a look at one of the automated deployment packages:
>
> Chef https://www.chef.io/chef/
>
> Puppet https://puppetlabs.com/
>
> Salt http://saltstack.com/
>
> There are probably others, these are just the ones I know about. They are
> general purpose tools but have Postgres specific features. As an examples:
>
>
> http://docs.saltstack.com/en/latest/ref/modules/all/salt.modules.postgres.html
>
>
> https://github.com/puppetlabs/puppetlabs-postgresql#custom-resource-postgresql_psql
>
>
>>
>> 4) Are the servers all the same version of Postgres(or are they even
>> all Postgres servers), so do you have to take into account version
>> differences?
>>
>>
>> All servers are in same version, PostgreSQL 9.4.
>>
>> Thanks & Regards,
>> Anderson Abreu
>>
>>
>>
>>
>> Can do thiswithshellscript.
>>
>> I would like to knowifthis is the only way?
>>
>>
>> No, that is the problem:)
>>
>>
>>
>> Thanks & Regards
>>
>
Thanks all,

I try your suggestions


Regras

>
>>
>> ---
>>
>> Anderson Abreu
>> andersonab...@gmail.com 
>> >
>>
>> "Thejudokaiswhathas:humilitytolearnwhatyouteach,patienceto
>> teachwhat
>> theylearnedtotheir fellow manandfaithtobelieve in whatyou don't
>> understand.Learna little more every dayanduse it every dayfor the
>> good"(Jigoro Kano)
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Steve Kehlet]

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 something and finally got released.

My team and I have done the usual stuff looking at the system itself and
its underlying infrastructure, and we don't think that's it. It does seem
to be localized to just this table.

We're reading about GIN fast updates and noted that:

As of PostgreSQL 8.4, GIN is capable of postponing much of this work by
> inserting new tuples into a temporary, unsorted list of pending entries.
> When the table is vacuumed, or if the pending list becomes too large
> (larger than work_mem), the entries are moved to the main GIN data
> structure using the same bulk insert techniques used during initial index
> creation. This greatly improves GIN index update speed, even counting the
> additional vacuum overhead. Moreover the overhead work can be done by a
> background process instead of in foreground query processing.
> The main disadvantage of this approach is that searches must scan the list
> of pending entries in addition to searching the regular index, and so a
> large list of pending entries will slow searches significantly. Another
> disadvantage is that, while most updates are fast, an update that causes
> the pending list to become "too large" will incur an immediate cleanup
> cycle and thus be much slower than other updates.


I am wondering if the UPDATEs are hitting some blockage on the table due to
this cleanup cycle. Is this a possibility, and if so how can I investigate
this? What queries can I run or stats can I check to see if this pending
list is approaching work_mem, or we're hitting this cleanup cycle? Is there
any logging I can enable to see when these cleanups are occurring?

One test we are doing right now is running VACUUMs continuously on the
table to see if this slowness stops happening. We should know within a few
hours. If this seems to help, we'll try turning off FASTUPDATE on the index.

Any other ideas or suggestions would be appreciated, thanks!

My non-default postgres settings are [here in a gist](
https://gist.github.com/skehlet/fd4945f9c9ce6ed075f1).

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

[Tom Lane]

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, like they were all
> blocked on something and finally got released.

Hm ... have you tried checking pg_locks to see if they're blocked on
something identifiable?

You might be right that this is caused by flushing the GIN pending list,
but I thought that that was not supposed to block concurrent insertions.
What I'd expect to see is *one* insert taking significantly longer than
normal, but no effect on concurrent operations.  Also, 2-4 minutes sounds
much longer than should be needed to flush a 10MB pending list, anyway.

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

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

[Steve Kehlet]

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 take
> > upwards of 2-4 minutes, and all finish simultaneously, like they were all
> > blocked on something and finally got released.
>
> 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://gist.github.com/skehlet/fbf5f52e18149e14e520) and nothing has shown
up related to these queries (there were some other normal unrelated
results, so I believe the job+query itself are working). After several
incidents I believe it would have logged something.


> You might be right that this is caused by flushing the GIN pending list,
> but I thought that that was not supposed to block concurrent insertions.
> What I'd expect to see is *one* insert taking significantly longer than
> normal, but no effect on concurrent operations.  Also, 2-4 minutes sounds
> much longer than should be needed to flush a 10MB pending list, anyway.


Yeah head scratch. That is really weird. Still gathering data, any way I
can see for sure when these cleanup cycles are occurring?

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

[Tom Lane]

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 so often,
it'd be pretty easy to confirm or refute whether these episodes are
associated with list cleanup.

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

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

[Tom Lane]

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://gist.github.com/skehlet/fbf5f52e18149e14e520) and nothing has shown
> up related to these queries (there were some other normal unrelated
> results, so I believe the job+query itself are working).

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,
which is certainly wrong.  I think you could probably just drop the check
on locktype.  You might want to tighten the WHERE to "WHERE
blockingl.granted AND NOT blockedl.granted", as well.

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

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

[Jeff Janes]

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 minutes, and all finish simultaneously, like they were all
> blocked on something and finally got released.
>
> My team and I have done the usual stuff looking at the system itself and
> its underlying infrastructure, and we don't think that's it. It does seem
> to be localized to just this table.
>
> We're reading about GIN fast updates and noted that:
>
> As of PostgreSQL 8.4, GIN is capable of postponing much of this work by
>> inserting new tuples into a temporary, unsorted list of pending entries.
>> When the table is vacuumed, or if the pending list becomes too large
>> (larger than work_mem), the entries are moved to the main GIN data
>> structure using the same bulk insert techniques used during initial index
>> creation. This greatly improves GIN index update speed, even counting the
>> additional vacuum overhead. Moreover the overhead work can be done by a
>> background process instead of in foreground query processing.
>> The main disadvantage of this approach is that searches must scan the
>> list of pending entries in addition to searching the regular index, and so
>> a large list of pending entries will slow searches significantly. Another
>> disadvantage is that, while most updates are fast, an update that causes
>> the pending list to become "too large" will incur an immediate cleanup
>> cycle and thus be much slower than other updates.
>
>
> I am wondering if the UPDATEs are hitting some blockage on the table due
> to this cleanup cycle. Is this a possibility, and if so how can I
> investigate this? What queries can I run or stats can I check to see if
> this pending list is approaching work_mem, or we're hitting this cleanup
> cycle? Is there any logging I can enable to see when these cleanups are
> occurring?
>

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. If you don't mind
doing some hacking, you could probably get the new pageinspect to run on
9.4.

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.

But the symptoms you describe are exactly what I expect from these clean up
problems, so I would just assume that that is the problem.

The easiest solution is to turn of fastupdate for that index.  Each update
will then be individually slower, but you won't have the periodic lock up
you currently do.  If that doesn't work for you, then you can change the
autoanalyze settings for the table so that it get auto-analyzed very
frequently.


>
> One test we are doing right now is running VACUUMs continuously on the
> table to see if this slowness stops happening. We should know within a few
> hours. If this seems to help, we'll try turning off FASTUPDATE on the index.
>

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_factor" to zero and instead using
"autovacuum_analyze_threshold" to control the autovacuum process for that
table.  Note that a manual ANALYZE will *not* clear the pending list, it
has to be an autoanalyze.  (Manual VACUUM will clear the pending list, but
you might have trouble getting manual VACUUM to complete fast enough)



> Any other ideas or suggestions would be appreciated, thanks!
>


The bottleneck is generally a lot of random IO.  Can you put the gin index
on SSD?

Cheers,

Jeff

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

[Tom Lane]

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,
> which is certainly wrong.

Oh, wait, scratch that: I was confusing locktype with mode.  I'm still
suspicious about whether this is covering all cases though.  It's
certainly not trying very hard to make sure the locks match up; though
that would tend to lead to too many reports not too few.  Still, you could
be missing a lock conflict on some other kind of lock.  I'd suggest
writing this part along the lines of

(blockingl.locktype,
 blockingl.database,
 blockingl.relation,
 blockingl.page,
 blockingl.tuple,
 blockingl.virtualxid,
 blockingl.transactionid,
 blockingl.classid,
 blockingl.objid,
 blockingl.objsubid)
 IS NOT DISTINCT FROM
(blockedl.locktype,
 blockedl.database,
 blockedl.relation,
 blockedl.page,
 blockedl.tuple,
 blockedl.virtualxid,
 blockedl.transactionid,
 blockedl.classid,
 blockedl.objid,
 blockedl.objsubid)

which should reliably give the right answer.

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

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

[Tom Lane]

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [R] Issues with RPostgres

[Adrian Klaver]

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 sure what that covered. You might want to 
restate what your issue is, in order to facilitate an answer.





On Thu, Aug 27, 2015 at 4:13 PM, John McKown
mailto:john.archie.mck...@gmail.com>> wrote:

On Thu, Aug 27, 2015 at 3:50 PM, Abraham Mathew
mailto:mathewanalyt...@gmail.com>>wrote:


Yeah, thought that could be an issue. Unfortunately, I've had
issues setting a normal user id and then having postgres
identify the password
associated with it. Using postgres was the only thing that worked.

## CONNECT TO LOCALHOST DATABASE:
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname="postgres", host='localhost',
  port='5432', user='postgres', password='brothers')
dbDisconnect(con)



​OK. So, looking at the above, you are saying that the user is
"postgres" and that the table "test" is in the database "postgres"
(which is likely also owned by "postgres"). I'm unsure from your
response that this is correct. Is "test" in the "postgres" data
base? If not, you might get away with:

# replace proper-database in the following with the correct data
base name, which contains the "test" table
con <- dbConnect(drv, dbname="proper-database", host="localhost",
port='5432', user='postgres', password='brothers')


Normally, when I am logged in a myself, my code usually looks a bit
like:

dbname <- Sys.info['user']; # the data base name in PostgreSQL is
the same as my login id
con <- dbConnect(drv,dbname​=dbname); # connect to PostgreSQL on the
local host as myself (implicit).
  ds_summary(con, "test", vars=c("Age"), y=c("Class"))

In my pg_hba.conf file, I have a line like:

local   all all trust

Which says that everyone coming in who are on the local host are
automatically logged in as their Linux (in my case) id.

Your code is connecting via TCPIP because you have the host= & port=
parameters. This is not normally needed for users running on the
same physical machine as the PostgreSQL data base server. So I'm too
lazy to do it [grin].

--

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you
will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown




--
*Abraham Mathew
Data Ninja and Statistical Modeler
*
*Minneapolis, MN
720-648-0108
@abmathewks
Analytics_Blog *



--
Adrian Klaver
adrian.kla...@aklaver.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 investigate GIN fast updates and cleanup cycles?

[Steve Kehlet]

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 symptoms you describe are exactly what I expect from these clean
> up problems, so I would just assume that that is the problem.
>
> The easiest solution is to turn of fastupdate for that index.  Each update
> will then be individually slower, but you won't have the periodic lock up
> you currently do.
>

That would be fine and we will try this.


> 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_factor" to zero and instead using
>

Did you mean autovacuum_analyze_scale_factor or does it not matter?

I'm trying to force an autovacuum/autoanalyze this way but unfortunately
for me I have autovacuum_max_workers at the default of 3 and there are
apparently many tables in line for autovacuuming in front of the table I
want :-(. I'm playing whack-a-mole killing them and hoping the table I want
will come up.

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 understand things, but I'll just
accept it for now.


>  (Manual VACUUM will clear the pending list, but you might have trouble
> getting manual VACUUM to complete fast enough)
>

You are exactly right the manual VACUUM is taking forever.

>

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

[Jeff Janes]

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 take
> > upwards of 2-4 minutes, and all finish simultaneously, like they were all
> > blocked on something and finally got released.
>
> Hm ... have you tried checking pg_locks to see if they're blocked on
> something identifiable?
>
> You might be right that this is caused by flushing the GIN pending list,
> but I thought that that was not supposed to block concurrent insertions.
> What I'd expect to see is *one* insert taking significantly longer than
> normal, but no effect on concurrent operations.


If they all have the same work_mem setting, then they all hit the pending
list limit at the same time and all of them try to do the clean up.  None
of them realize that other clean-ups are in progress until one process gets
far enough along to actually start truncating away the pending list pages.
At that point the others will soon detect the concurrent clean up and bail
out, but the performance damage has already been done.

This isn't just a performance problem, it could also lead to very rare
correctness bugs that are being discussed on "hackers" (
http://www.postgresql.org/message-id/55d183ff.60...@iki.fi).



> Also, 2-4 minutes sounds
> much longer than should be needed to flush a 10MB pending list, anyway.
>

I've seen it take longer than that for 4MB pending lists, once the gin
index doesn't fit in RAM.  10MB is 1280 pages. If that holds 1280 tuples
and each tuple has 50 keys, that would be 64,000 keys.  If the keys don't
have a lot of internal overlap inserting them would take 6.4 minutes with
10,000 rpm drives, assuming all the non-leaf pages are already in memory.
There is no pre-fetching (not even with multiple backends trying to do the
same work at the same time), so a RAID won't speed things up.

Cheers,

Jeff

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

[Tom Lane]

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 understand things, but I'll just
> accept it for now.

A look at the source code says Jeff's right: either manual or auto VACUUM
will clear the list, and so will auto ANALYZE, but not manual ANALYZE.
Not sure why we did that, but it's clearly intentional.

>> (Manual VACUUM will clear the pending list, but you might have trouble
>> getting manual VACUUM to complete fast enough)

> You are exactly right the manual VACUUM is taking forever.

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 something reported about the GIN index.

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

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

[Jeff Janes]

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_factor" to zero and instead using
>>
>
> Did you mean autovacuum_analyze_scale_factor or does it not matter?
>


Yes, sorry.  Those long names make my eyes go bleary.

Cheers,

Jeff

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

[Dominik Czarnota]

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);

CREATE OR REPLACE FUNCTION example() RETURNS VOID AS
$$
plpy.execute("COPY test (field1, field2) FROM STDIN
DELIMITER',';\n1,2\n\\.")
$$ LANGUAGE 'plpythonu';

select example();


Maybe there is a way to get into the underlying psycopg2 driver and call
some method that would do COPY FROM, but I can't find anything related to
it in the docs.

2015-08-28 17:15 GMT+02:00 Adrian Klaver :

> 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 so explicit error:
>> ProgrammingError: (psycopg2.ProgrammingError) spiexceptions.SyntaxError:
>> syntax error at or near "val1"
>> LINE 2: val1,val2,val3
>>  ^
>>
>
> The above is a psycopg2 error, so where are you running this and how?
>
>
>
>> However the same thing works in psql.
>>
>> After some googling I found out that it doesn't work inside sql
>> functions because there is no STDIN (
>> http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
>> ).
>>
>>
>> I've got working solution which was saving tmp file and then calling
>> `COPY table FROM 'path'` but that's not cool.
>>
>> Did I miss something in plpythonu api? If not, where can I post "feature
>> request" for that?
>> Or can you point me place where I could dig into the code and implement
>> it?
>>
>>
>> 
>> Dominik Czarnota
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Dominik Czarnota]

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 quotes from the
database - so to reduce io and not to fetch them everytime from Python,
I've created a plpythonu function that calculates everything for each stock.
As the results also have to stay in the database, I need to use the fastest
possible method to insert a lot of data.

Another problem for those calculations would be parallelizing them somehow
(I hope postgres clustering will do the work), but that's totally unrelated
to the problem we are discussing now.


2015-08-28 17:59 GMT+02:00 Adrian Klaver :

> 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;
>> CREATE TABLE test(field1 integer, field2 integer);
>>
>> CREATE OR REPLACE FUNCTION example() RETURNS VOID AS
>> $$
>>  plpy.execute("COPY test (field1, field2) FROM STDIN
>> DELIMITER',';\n1,2\n\\.")
>> $$ LANGUAGE 'plpythonu';
>>
>> select example();
>>
>>
>> Maybe there is a way to get into the underlying psycopg2 driver and call
>> some method that would do COPY FROM, but I can't find anything related
>> to it in the docs.
>>
>
> The only thing I can think of is using io.BytesIO() to create an in memory
> file and then reading from that. I have done it using psycopg2, but not in
> plpythonu so I would mark this untested.
>
> Otherwise, could you explain more where the list is coming from and it's
> size as there may be other ways to attack this.
>
>
>> 2015-08-28 17:15 GMT+02:00 Adrian Klaver > >:
>>
>>
>> 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 so explicit error:
>> ProgrammingError: (psycopg2.ProgrammingError)
>> spiexceptions.SyntaxError:
>> syntax error at or near "val1"
>> LINE 2: val1,val2,val3
>>   ^
>>
>>
>> The above is a psycopg2 error, so where are you running this and how?
>>
>>
>>
>> However the same thing works in psql.
>>
>> After some googling I found out that it doesn't work inside sql
>> functions because there is no STDIN (
>>
>> http://www.postgresql.org/message-id/000501c5acc9$83747aa0$0501a8c0@plexus
>> ).
>>
>>
>> I've got working solution which was saving tmp file and then
>> calling
>> `COPY table FROM 'path'` but that's not cool.
>>
>> Did I miss something in plpythonu api? If not, where can I post
>> "feature
>> request" for that?
>> Or can you point me place where I could dig into the code and
>> implement it?
>>
>>
>> 
>> Dominik Czarnota
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

[Jeff Janes]

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_factor" to zero and instead using
>>
>
> Did you mean autovacuum_analyze_scale_factor or does it not matter?
>
> I'm trying to force an autovacuum/autoanalyze this way but unfortunately
> for me I have autovacuum_max_workers at the default of 3 and there are
> apparently many tables in line for autovacuuming in front of the table I
> want :-(. I'm playing whack-a-mole killing them and hoping the table I want
> will come up.
>

I overlooked the significance of this paragraph the first time I
responded.  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 troublesome gin index:

alter table some_table_with_gin_index set (autovacuum_analyze_scale_factor
=0);
alter table some_table_with_gin_index set (autovacuum_analyze_threshold
=100);

Killing existing vacuums won't help (at least not until change your system
wide setting back and so the above instead) because it will likely just
restart on the same table it was on when you killed it.

Cheers,

Jeff

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

[Steve Kehlet]

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 troublesome gin index:
>

No I did it just to the problematic table:

ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor=0);
ALTER TABLE my_table SET (autovacuum_analyze_scale_factor=0);

My system-wide autovacuum_analyze_threshold is 50 so I think that's ok.

There is a bulk load going on right now so a lot of tables are needing
vacuuming. I really need to increase my autovacuum_max_workers.


>
> Killing existing vacuums won't help (at least not until change your system
> wide setting back and so the above instead) because it will likely just
> restart on the same table it was on when you killed it.
>

You're right, I gave up trying, and am back running a manual VACUUM. It's
slow, but I can't bounce the db right now.

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

[Steve Kehlet]

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
> pending list for a GIN index.  If you were to capture that every so often,
> it'd be pretty easy to confirm or refute whether these episodes are
> associated with list cleanup.
>

Thank you, I found it, this could be very helpful, we'll try it out on some
dev boxes.

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

[Steve Kehlet]

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 something reported about the GIN index.


Since I'm back to running VACUUM VERBOSE by hand, can you clarify for me
how reducing maintenance_work_mem (currently 512MB) will speed it up? Will
it work in smaller chunks? So just do something like:

set maintenance_work_mem = '32MB';
VACUUM VERBOSE my_table';

How do I determine the smallest value possible? Just start small (32MB?)
and see if it fails, and increase until it doesn't?

[GENERAL] wal files stay in the pg_xlog dir

[kingl]

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.

there is a backup file everytime the backup happens under the pg_xlog
directory. 

-rw--- 1 postgres postgres  327 Aug 28 02:33
0023000F0075.0028.backup 

pg_xlog/archive_status shows that archive is up to date.

-rw--- 1 postgres postgres 16777216 Aug 28 15:46
0023000F009B
drwx-- 2 postgres postgres86016 Aug 28 15:47 archive_status
-rw--- 1 postgres postgres 16777216 Aug 28 16:00
0023000F009C

inside of archive_status the latest is 09B
-rw--- 1 postgres postgres 0 Aug 28 15:46 0023000F009B.done

it seems that wal files are not being removed after archive. i read
archive_cleanup? do i need to set it up for wal files clean up? if so how to
do that? 

Thank you in advance!

postgresql conf file on master node

/etc/postgresql/9.3/main/postgresql.conf


#--
# WRITE AHEAD LOG
#--

# - Settings -

wal_level = 'hot_standby'   # minimal, archive, or
hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or
off
#synchronous_commit = on# synchronization level; on, off, or
local
#wal_sync_method = fsync# the default is the first option
# supported by the operating system:
#   open_datasync
#   fdatasync (default on Linux)
#   fsync
#   fsync_writethrough
#   open_sync
#full_page_writes = on  # recover from partial page writes
wal_buffers = 16MB  # min 32kB, -1 sets based on
shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms   # 1-1 milliseconds

#commit_delay = 0   # range 0-10, in microseconds
#commit_siblings = 5# range 1-1000

# - Checkpoints -

checkpoint_segments = 32# in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 5min  # range 30s-1h
checkpoint_completion_target = 0.7  # checkpoint target duration, 0.0 -
1.0
#checkpoint_warning = 30s   # 0 disables

# - Archiving -

archive_mode = on   # allows archiving to be done
# (change requires restart)
archive_command = 'cd . && rsync -a %p
barman@barman01:/var/lib/barman/postgresnode01/incoming/%f'
# command to use to archive a logfile segment
#archive_timeout = 0# force a logfile segment switch after this
# number of seconds; 0 disables

#--
# REPLICATION
#--

# - Master Server -

# These settings are ignored on a standby server

max_wal_senders = 10# max number of walsender processes
# (change requires restart)
#wal_sender_delay = 1s  # walsender cycle time, 1-1 milliseconds
wal_keep_segments = 2000# in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0   # number of xacts by which cleanup is
delayed
#replication_timeout = 60s  # in milliseconds; 0 disables
#synchronous_standby_names = '' # standby servers that provide sync rep
# comma-separated list of application_name
# from standby(s); '*' = all

# - Standby Servers -

# These settings are ignored on a master server

hot_standby = on# "on" allows queries during
recovery
# (change requires restart)
#max_standby_archive_delay = 30s# max delay before canceling queries
# when reading WAL from archive;
# -1 allows indefinite delay
#max_standby_streaming_delay = 30s  # max delay before canceling queries
# when reading streaming WAL;
# -1 allows indefinite delay
#wal_receiver_status_interval = 10s # send replies at least this often
# 0 disables
#hot_standby_feedback = off # send info from standby to prevent

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

[Jeff Janes]

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 just for individual tables.  So just the
>> table with the troublesome gin index:
>>
>
> No I did it just to the problematic table:
>
> ALTER TABLE my_table SET (autovacuum_vacuum_scale_factor=0);
> ALTER TABLE my_table SET (autovacuum_analyze_scale_factor=0);
>

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.



>
> My system-wide autovacuum_analyze_threshold is 50 so I think that's ok.
>

autovacuum_analyze_scale_factor is the more important one.  Only when that
is close to zero does autovacuum_analyze_threshold matter very much.



> There is a bulk load going on right now so a lot of tables are needing
> vacuuming. I really need to increase my autovacuum_max_workers.
>

But those workers all share the same IO throttling amongst themselves.
Increasing it mostly just gives you more workers all working more slowly.
Assuming your IO subsystem can handle it, you are better off lowering
autovacuum_vacuum_cost_delay, which can be done without a server restart
(although the change won't take full effect until the existing workers go
away and restart).  I also set vacuum_cost_page_hit and
vacuum_cost_page_miss to zero and rely exclusively on vacuum_cost_page_dirty
to do the throttling.

Cheers,

Jeff

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

[Adrian Klaver]

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_xlog on node1
keeps accumulating.


Well below you have:

wal_keep_segments = 2000

That means at least 2000 WAL files will be kept around.

How many files do you see in pg_xlog?



there is a backup file everytime the backup happens under the pg_xlog
directory.

-rw--- 1 postgres postgres  327 Aug 28 02:33
0023000F0075.0028.backup

pg_xlog/archive_status shows that archive is up to date.

-rw--- 1 postgres postgres 16777216 Aug 28 15:46
0023000F009B
drwx-- 2 postgres postgres86016 Aug 28 15:47 archive_status
-rw--- 1 postgres postgres 16777216 Aug 28 16:00
0023000F009C

inside of archive_status the latest is 09B
-rw--- 1 postgres postgres 0 Aug 28 15:46 0023000F009B.done

it seems that wal files are not being removed after archive. i read
archive_cleanup? do i need to set it up for wal files clean up? if so how to
do that?

Thank you in advance!

postgresql conf file on master node

/etc/postgresql/9.3/main/postgresql.conf


#--
# WRITE AHEAD LOG
#--

# - Settings -

wal_level = 'hot_standby'   # minimal, archive, or
hot_standby
 # (change requires restart)
#fsync = on # turns forced synchronization on or
off
#synchronous_commit = on# synchronization level; on, off, or
local
#wal_sync_method = fsync# the default is the first option
 # supported by the operating system:
 #   open_datasync
 #   fdatasync (default on Linux)
 #   fsync
 #   fsync_writethrough
 #   open_sync
#full_page_writes = on  # recover from partial page writes
wal_buffers = 16MB  # min 32kB, -1 sets based on
shared_buffers
 # (change requires restart)
#wal_writer_delay = 200ms   # 1-1 milliseconds

#commit_delay = 0   # range 0-10, in microseconds
#commit_siblings = 5# range 1-1000

# - Checkpoints -

checkpoint_segments = 32# in logfile segments, min 1, 16MB
each
#checkpoint_timeout = 5min  # range 30s-1h
checkpoint_completion_target = 0.7  # checkpoint target duration, 0.0 -
1.0
#checkpoint_warning = 30s   # 0 disables

# - Archiving -

archive_mode = on   # allows archiving to be done
 # (change requires restart)
archive_command = 'cd . && rsync -a %p
barman@barman01:/var/lib/barman/postgresnode01/incoming/%f'
 # command to use to archive a logfile segment
#archive_timeout = 0# force a logfile segment switch after this
 # number of seconds; 0 disables

#--
# REPLICATION
#--

# - Master Server -

# These settings are ignored on a standby server

max_wal_senders = 10# max number of walsender processes
 # (change requires restart)
#wal_sender_delay = 1s  # walsender cycle time, 1-1 milliseconds
wal_keep_segments = 2000# in logfile segments, 16MB each; 0 disables
#vacuum_defer_cleanup_age = 0   # number of xacts by which cleanup is
delayed
#replication_timeout = 60s  # in milliseconds; 0 disables
#synchronous_standby_names = '' # standby servers that provide sync rep
 # comma-separated list of application_name
 # from standby(s); '*' = all

# - Standby Servers -

# These settings are ignored on a master server

hot_standby = on# "on" allows queries during
recovery
 # (change requires restart)
#max_standby_archive_delay = 30s# max delay before canceling queries
 # when reading WAL from archive;
 # -1 allows indefinite delay
#max_standby_streaming_delay = 30s  # max delay before canceling queries
 # when reading streaming WAL;
 # -1 allows indefinite delay
#wal_receiver_status_i

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

[Steve Kehlet]

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 a bulk load going on right now so a lot of tables are needing
>> vacuuming. I really need to increase my autovacuum_max_workers.
>>
>
> But those workers all share the same IO throttling amongst themselves.
> Increasing it mostly just gives you more workers all working more slowly.
>

Ah, you're right, that won't help.


> Assuming your IO subsystem can handle it, you are better off lowering
> autovacuum_vacuum_cost_delay, which can be done without a server restart
> (although the change won't take full effect until the existing workers go
> away and restart).  I also set vacuum_cost_page_hit and
> vacuum_cost_page_miss to zero and rely exclusively on vacuum_cost_page_dirty
> to do the throttling.
>

Thank you for these great suggestions, I will play with them.

>

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

[kingl]

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 that both nodes' pg_xlog should be exact the same?

thank you again.



--
View this message in context: 
http://postgresql.nabble.com/wal-files-stay-in-the-pg-xlog-dir-tp5863782p5863791.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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 to speed on WAL in general start here:


http://www.postgresql.org/docs/9.4/interactive/wal.html

For




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.


That looks about right.  The wal_keep_segments = 2000  is a nomimal 
number subject to the configuration settings explained in above link. 
Monitor this number for a period it should stay in the neighborhood of 2000.




the other issue is that the standby node has only 1345 wal files in the
pg_xlog, i thought that both nodes' pg_xlog should be exact the same?


The links above go into the full explanation. The short version is that 
WAL files are generally meant to be recycled. On the master you are 
overriding the recycling to a degree by using wal_keep_segments to force 
Postgres to keep at least 2000 files whether they can be recycled or 
not. The standby does not have that restriction so it is recycling the 
files more aggressively.




thank you again.



--
View this message in context: 
http://postgresql.nabble.com/wal-files-stay-in-the-pg-xlog-dir-tp5863782p5863791.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
Adrian Klaver
adrian.kla...@aklaver.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] Does PLPythonU support COPY table FROM string?

[Adrian Klaver]

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 stock exchange market. Each calculation requires quotes from
the database - so to reduce io and not to fetch them everytime from
Python, I've created a plpythonu function that calculates everything for
each stock.
As the results also have to stay in the database, I need to use the
fastest possible method to insert a lot of data.


This means moving the results to another table?

Have you looked at plpy.cursor here:

http://www.postgresql.org/docs/9.4/interactive/plpython-database.html


Another problem for those calculations would be parallelizing them
somehow (I hope postgres clustering will do the work), but that's
totally unrelated to the problem we are discussing now.





--
Adrian Klaver
adrian.kla...@aklaver.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] Re: ERROR: collation "latin1_general_ci" for encoding "UTF8" does not exist

[Adrian Klaver]

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 understand the
question? (If I am right then collation is used to sort the data) ..may
be he mean sorting the data on alphabetical order or something similar



Well without knowing what the desired end result is, it is hard to 
suggest a solution. I can point you at the localization/character 
support section of the documentation so you can see how Postgres handles 
this:


http://www.postgresql.org/docs/9.4/interactive/charset.html

As to specific questions:

1) What language do they want the data to be collated in?

2) What is the result that is desired?

3) Using psql and doing \l what does it show for the database(s) you are 
working with?



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general