Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

[David Rowley]

On 29 April 2017 at 11:37, David G. Johnston  wrote:
>> > Perhaps there are reasons why this optimization is not safe that I
>> > haven't
>> > thought about?
>>
>> Yeah, I think so. What happens if an A row cannot find a match in B or
>> C? This version of the query will end up returning fewer rows due to
>> that, but the original version would consider other rows with a higher
>> rank.
>>
>> We've danced around a bit with using foreign keys as proofs that rows
>> will exist for other optimisations in the past, but it's tricky ground
>> since foreign keys are not updated immediately, so there are windows
>> where they may not actually hold true to their word.
>
>
> I read this query as having a relation cardinality of one-to-one mandatory -
> which precludes the scenario described.

What makes you say so?

It's pretty easy to show how the queries are not the same.

create table a (
  id int primary key,
  b_id int not null,
  val int not null,
  rank int not null
);

create table b (
  id int primary key,
  c_id int not null,
  val int not null
);

create table c (
  id int primary key,
  val int not null
);
insert into a select x,x,x,x from generate_series(1,150) x;
insert into b select x,x,x from generate_series(51,150) x;
insert into c select x,x from generate_series(51,150) x;

SELECT A.val, B.val, C.val FROM A
   JOIN B ON A.b_id = B.id
   JOIN C ON B.c_id = C.id
   ORDER BY A.rank
   LIMIT 100; -- returns 100 rows

 SELECT D.val, B.val, C.val FROM
   (SELECT * FROM A ORDER BY A.rank LIMIT 100) AS D
   JOIN B ON D.b_id = B.id
   JOIN C ON B.c_id = C.id
   LIMIT 100; -- returns 50 rows


> Is the above saying that, today, there is no planning benefit to setting up
> two deferrable references constraints to enforce the non-optional
> requirement?

There is no place in the planner where a foreign key is used as a
proof that a joined row must exist, with the exception of row
estimations for statistics.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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] [PERFORM] Questionaire: Common WAL write rates on busy servers.

[Tomas Vondra]

On 04/28/2017 01:34 AM, Andres Freund wrote:

On 2017-04-28 01:29:14 +0200, Tomas Vondra wrote:

I can confirm this observation. I bought the Intel 750 NVMe SSD last year,
the device has 1GB DDR3 cache on it (power-loss protected), can do ~1GB/s of
sustained O_DIRECT sequential writes. But when running pgbench, I can't push
more than ~300MB/s of WAL to it, no matter what I do because of
WALWriteLock.


Hm, interesting.  Even if you up wal_buffers to 128MB, use
synchronous_commit = off, and play with wal_writer_delay/flush_after?



I think I've tried things like that, but let me do some proper testing. 
I'll report the numbers in a few days.


regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
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] query planner placement of sort/limit w.r.t. joins

[David G. Johnston]

On Fri, Apr 28, 2017 at 3:24 PM, David Rowley 
wrote:

> On 29 April 2017 at 07:59, Dave Vitek  wrote:
> > Is what I want in the query planner's vocabulary?  It would need to
> exploit
> > the fact that the _id columns are not nullable, and either exploit the
> > uniqueness of the id columns or do an extra LIMIT step after the join.  I
> > think I want it to effectively give the same result I expect (haven't
> > tested) it would give for:
>
> Unfortunately, it's not a plan that the current planner will consider.
>
> > SELECT D.val, B.val, C.val FROM
> >(SELECT * FROM A ORDER BY A.rank LIMIT 100) AS D
> >JOIN B ON D.b_id = B.id
> >JOIN C ON B.c_id = C.id
> >LIMIT 100;
> >
> > Perhaps there are reasons why this optimization is not safe that I
> haven't
> > thought about?
>
> Yeah, I think so. What happens if an A row cannot find a match in B or
> C? This version of the query will end up returning fewer rows due to
> that, but the original version would consider other rows with a higher
> rank.
>
> We've danced around a bit with using foreign keys as proofs that rows
> will exist for other optimisations in the past, but it's tricky ground
> since foreign keys are not updated immediately, so there are windows
> where they may not actually hold true to their word.
>

​​I read this query as having a relation cardinality of one-to-one
mandatory - which precludes the scenario described.

Is the above saying that, today, there is no planning benefit to setting up
two deferrable references constraints to enforce the non-optional
requirement?

I know I'm guilty of not enforcing the non-optional part of the
constraint.  Mostly due to not really realizing it but also having to deal
the added syntax to perform inserts.  ORMs I suspect generally would be
unaccommodating here as well...

David J.​

[GENERAL] Reset privileges to builtin defaults

[Diego Augusto Molina]

Hi, I've been charged with the administration of a couple of big
databases in an old 8.1 cluster. The databases have many
administrative issues, privileges being the most prominent for my job.
All objects have privileges for specific personal end-user roles
(dozens and dozens of privileges each object) instead of using profile
roles with the correct privileges and membership granted to specific
personal roles. I have identified the profiles and granted them to the
personal roles. Now I need to reset all privileges on all objects to
the builtin defaults and grant the correct privileges to the profile
roles.

Part of the job is also migrating to version 9.6 (current stable as of
today), but as far as I can see in the documentation there's no clean
way of achieving my task not even in that version, so postponing it
will not help. So I decided to take a risk and modify system catalogs
in a test 8.1 environment with success up to what I can see. What I
did is to create three functions that take an OID: one for databases,
one for functions and the last for relations (ordinary table,
sequence, view with extended capability for materialized view,
composite type or foreign table only for the case I'd use these
functions in a more recent cluster some time in the future). They all
perform three basic things:
1) Set the ACL field of the object to NULL in the correct catalog.
2) Delete the ACL dependencies of the object on any role by deleting
from "pg_shdepend".
3) Returning a useful thing: TRUE when privileges are set to the
builtin defaults (wether or not the function actually had to do
something) and FALSE when something goes wrong. They're declared
STRICT, so NULL is returned for NULL input.

These functions could be improved by marking them SECURITY DEFINER and
letting a role reset the privileges of it's owned objects. Also, other
functions should be added for other kind of objects like foreign
servers and such. Certainly, many things might also be useful for
general use but for my current purposes they suffice (although I
wouldn't bother to make these changes as an exercice if someone is
interested and, of course, if I'm on the riht way). I added two kind
of "properties" to the functions defined by "-- @PROTECT: OWNERSHIP
PRIVILEGES" in the code. That makes these functions immune to the
privilege reset function for functions (funny English). You might also
guess I have another function to change the ownership, but that's
another story. Also, superuser owned SECURITY DEFINER functions are
untouched and managed manually for now and only because of my
particular needs.

I tried these functions in the test environment and they seem to work
properly in every use case I could think of, although I would be more
comfortable if an experienced someone gives me it's impressions on
these before I go into production. Please, do!

Here goes the code:

/* BEGIN CODE */
CREATE OR REPLACE FUNCTION reset_privileges_on_database(oid) RETURNS
BOOLEAN AS $BODY$
-- @PROTECT: OWNERSHIP PRIVILEGES

-- Reset privileges on the database
  UPDATE pg_database
SET datacl = NULL
WHERE
  datacl IS NOT NULL AND  -- Only change when necessary
  oid = $1;   -- Change the requested database

-- Delete all ACL dependencies that might have been introduced
  DELETE
FROM pg_shdepend AS s
USING pg_class AS c -- USING list item #1
WHERE
  ( -- Join conditions for the USING list items
  s.classid = c.oid -- When introduced USING list item #1
) AND
  ( -- Specific conditions that normally make up the whole WHERE
clause in a SELECT statement
  s.dbid = 0AND -- We're deleting a shared
object's (databases) dependencies
  s.deptype = 'a'   AND -- The dependencies we're
deleting are ACLs
  c.relname = 'pg_database' AND -- The shared object we're
deleting dependencies of is a database
  s.objid = $1  -- Only delete dependencies of
the requested database
);

-- Return value
  SELECT EXISTS (
  SELECT 1
FROM pg_database
WHERE oid = $1 -- Only the requested database
);
$BODY$ LANGUAGE sql VOLATILE STRICT;
ALTER FUNCTION reset_privileges_on_database(oid) OWNER TO postgres;
REVOKE ALL ON FUNCTION reset_privileges_on_database(oid) FROM public;
COMMENT ON FUNCTION reset_privileges_on_database(oid) IS
'Diego Molina (20170427): Reset all privileges on a database given by
its OID to the builtin defaults.
Return values:
  * NULL: if NULL argument.
  * TRUE: Privileges have been reset.
  * FALSE: Privileges have not been reset. Reason: the given database
does not exist.';

CREATE OR REPLACE FUNCTION reset_privileges_on_function(oid) RETURNS
BOOLEAN AS $BODY$
-- @PROTECT: OWNERSHIP PRIVILEGES

-- Reset privileges on the function
  UPDATE pg_proc AS p
SET proacl = NULL
FROM pg_authid AS o -- FROM list item #1 (function's owner)
WHERE
  ( -- Join conditions
  p.proowner = 

Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

[David Rowley]

On 29 April 2017 at 07:59, Dave Vitek  wrote:
> Is what I want in the query planner's vocabulary?  It would need to exploit
> the fact that the _id columns are not nullable, and either exploit the
> uniqueness of the id columns or do an extra LIMIT step after the join.  I
> think I want it to effectively give the same result I expect (haven't
> tested) it would give for:

Unfortunately, it's not a plan that the current planner will consider.

> SELECT D.val, B.val, C.val FROM
>(SELECT * FROM A ORDER BY A.rank LIMIT 100) AS D
>JOIN B ON D.b_id = B.id
>JOIN C ON B.c_id = C.id
>LIMIT 100;
>
> Perhaps there are reasons why this optimization is not safe that I haven't
> thought about?

Yeah, I think so. What happens if an A row cannot find a match in B or
C? This version of the query will end up returning fewer rows due to
that, but the original version would consider other rows with a higher
rank.

We've danced around a bit with using foreign keys as proofs that rows
will exist for other optimisations in the past, but it's tricky ground
since foreign keys are not updated immediately, so there are windows
where they may not actually hold true to their word.


-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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

[GENERAL] query planner placement of sort/limit w.r.t. joins

[Dave Vitek]

Hi all,

I have a query I'd like to speed up.  I am wondering whether the query 
planner is capable of coming up with a certain kind of plan for this 
query, and if I might tickle it into doing so, or if I have to 
explicitly use subqueries to get what I want.


Imagine we have tables A, B, and C each having a one to one relationship.

SELECT A.val, B.val, C.val FROM A
   JOIN B ON A.b_id = B.id
   JOIN C ON B.c_id = C.id
   ORDER BY A.rank
   LIMIT 100;


Assume there are btree indices for all the "id" columns, but there is no 
index on "rank."  None of the fields are nullable.  id columns are 
unique.  Assume that there are about 1M tuples in the result set without 
the LIMIT.  ANALYZE has run recently.


In this case, I want postgres to do a sequential scan and a top-N sort 
against A since the ORDER BY only depends on columns of A, and then do 
100 index scans to implement the joins.


What I'm observing is that it is doing sequential scans on A, B, and C, 
joining, and then sorting.  The cost of the sequential scans of B and C 
is large enough to be painful.


Is what I want in the query planner's vocabulary?  It would need to 
exploit the fact that the _id columns are not nullable, and either 
exploit the uniqueness of the id columns or do an extra LIMIT step after 
the join.  I think I want it to effectively give the same result I 
expect (haven't tested) it would give for:


SELECT D.val, B.val, C.val FROM
   (SELECT * FROM A ORDER BY A.rank LIMIT 100) AS D
   JOIN B ON D.b_id = B.id
   JOIN C ON B.c_id = C.id
   LIMIT 100;

Perhaps there are reasons why this optimization is not safe that I 
haven't thought about?


The query is being generated for use by an ORM, so changing it to use a 
subquery for A and the ORDER BY+LIMIT is not so easy (plus, with 
different user input, it might need B or C in the ORDER BY).



- Dave



--
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] Error in PostgreSQL Log

[Jerry Sievers]

Sachin Srivastava  writes:

> Dear Team,
>
> Please suggest why we are getting the below error in our PostgreSQL
> DB log.
>
> We are using postgresql version = 9.2
>
>
> ERROR:  prepared transaction with identifier "131077_AP//

I dont have such an ancient version of Pg to test with but if indeed
your application is not using bogus or already committed  prepared xact
IDs, then did perhaps someone/thing mangle files in pg_twophase?

HTH

> CqqfIep65ipZAmdpAAIcgjE=_AP//
> CqqfIep65ipZAmdpAAIciwAA" does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> CqqfIep65ipZAmdpAAIcgjE=_AP//
> CqqfIep65ipZAmdpAAIciwAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6U+K0bPBZAmdRAAIpmTE=_AP//
> Cqq6U+K0bPBZAmdRAAIpoAAA" does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6U+K0bPBZAmdRAAIpmTE=_AP//
> Cqq6U+K0bPBZAmdRAAIpoAAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6UmqUdDpZAmc2AAJPFDE=_AP//
> Cqq6UmqUdDpZAmc2AAJPGgAA" does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6UmqUdDpZAmc2AAJPFDE=_AP//
> Cqq6UmqUdDpZAmc2AAJPGgAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6QiDZF3BZAjkeABef2TE=_AP//
> Cqq6QiDZF3BZAjkeABef4QAA" does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6QiDZF3BZAjkeABef2TE=_AP//
> Cqq6QiDZF3BZAjkeABef4QAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6QiDZF3BZAjkeABen1DE=_AP//
> Cqq6QiDZF3BZAjkeABen2AAA" does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6QiDZF3BZAjkeABen1DE=_AP//
> Cqq6QiDZF3BZAjkeABen2AAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6abHV5e9ZAmJCAAN/7DE=_AP//
> Cqq6abHV5e9ZAmJCAAOAHwAA" does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6abHV5e9ZAmJCAAN/7DE=_AP//
> Cqq6abHV5e9ZAmJCAAOAHwAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6U+K0bPBZAmdRAAJtczE=_AP//
> Cqq6U+K0bPBZAmdRAAJthQAA" does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6U+K0bPBZAmdRAAJtczE=_AP//
> Cqq6U+K0bPBZAmdRAAJthQAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> CqqfIep65ipZAmdpAAKVDjE=_AP//
> CqqfIep65ipZAmdpAAKVFQAA" does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> CqqfIep65ipZAmdpAAKVDjE=_AP//
> CqqfIep65ipZAmdpAAKVFQAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> CqqfIep65ipZAmdpAAK4bDE=_AP//
> CqqfIep65ipZAmdpAAK4cAAA" does not exist
> "postgresql-Fri.log" 313L, 30355C
>
> Regards,
> SSR
>
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
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] Error in PostgreSQL Log

[David G. Johnston]

On Friday, April 28, 2017, Sachin Srivastava 
wrote:
>
> Please suggest why we are getting the below error in our PostgreSQL DB log.
>
> ERROR:  prepared transaction with identifier "131077_AP//
> CqqfIep65ipZAmdpAAIcgjE=_AP//CqqfIep65ipZAmdpAAIciwAA"
> does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> CqqfIep65ipZAmdpAAIcgjE=_AP//CqqfIep65ipZAmdpAAIciwAA'
>

The error seems self-explanatory...

Without any context no one is going to be able to guess why you have an
application trying to rollback a non-existent two-phase
prepared transaction.  But likely the problem is not something under
PostgreSQL control.

David J.

Re: [GENERAL] Error in PostgreSQL Log

[Sachin Srivastava]

I have already tuned below parameters as

max_prepared_transactions = 500
max_connection = 2000



Regards,
SSR




On Sat, Apr 29, 2017 at 12:15 AM, Sachin Srivastava  wrote:

> Dear Team,
>
> Please suggest why we are getting the below error in our PostgreSQL DB log.
>
> We are using postgresql version = 9.2
>
>
> ERROR:  prepared transaction with identifier "131077_AP//
> CqqfIep65ipZAmdpAAIcgjE=_AP//CqqfIep65ipZAmdpAAIciwAA"
> does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> CqqfIep65ipZAmdpAAIcgjE=_AP//CqqfIep65ipZAmdpAAIciwAA'
> ERROR:  prepared transaction with identifier "131077_AP//Cqq6U+
> K0bPBZAmdRAAIpmTE=_AP//Cqq6U+K0bPBZAmdRAAIpoAAA" does
> not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//Cqq6U+
> K0bPBZAmdRAAIpmTE=_AP//Cqq6U+K0bPBZAmdRAAIpoAAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6UmqUdDpZAmc2AAJPFDE=_AP//Cqq6UmqUdDpZAmc2AAJPGgAA"
> does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6UmqUdDpZAmc2AAJPFDE=_AP//Cqq6UmqUdDpZAmc2AAJPGgAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6QiDZF3BZAjkeABef2TE=_AP//Cqq6QiDZF3BZAjkeABef4QAA"
> does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6QiDZF3BZAjkeABef2TE=_AP//Cqq6QiDZF3BZAjkeABef4QAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6QiDZF3BZAjkeABen1DE=_AP//Cqq6QiDZF3BZAjkeABen2AAA"
> does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6QiDZF3BZAjkeABen1DE=_AP//Cqq6QiDZF3BZAjkeABen2AAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> Cqq6abHV5e9ZAmJCAAN/7DE=_AP//Cqq6abHV5e9ZAmJCAAOAHwAA"
> does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> Cqq6abHV5e9ZAmJCAAN/7DE=_AP//Cqq6abHV5e9ZAmJCAAOAHwAA'
> ERROR:  prepared transaction with identifier "131077_AP//Cqq6U+
> K0bPBZAmdRAAJtczE=_AP//Cqq6U+K0bPBZAmdRAAJthQAA" does
> not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//Cqq6U+
> K0bPBZAmdRAAJtczE=_AP//Cqq6U+K0bPBZAmdRAAJthQAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> CqqfIep65ipZAmdpAAKVDjE=_AP//CqqfIep65ipZAmdpAAKVFQAA"
> does not exist
> STATEMENT:  ROLLBACK PREPARED '131077_AP//
> CqqfIep65ipZAmdpAAKVDjE=_AP//CqqfIep65ipZAmdpAAKVFQAA'
> ERROR:  prepared transaction with identifier "131077_AP//
> CqqfIep65ipZAmdpAAK4bDE=_AP//CqqfIep65ipZAmdpAAK4cAAA"
> does not exist
> "postgresql-Fri.log" 313L, 30355C
>
> Regards,
> SSR
>
>

[GENERAL] Error in PostgreSQL Log

[Sachin Srivastava]

Dear Team,

Please suggest why we are getting the below error in our PostgreSQL DB log.

We are using postgresql version = 9.2


ERROR:  prepared transaction with identifier
"131077_AP//CqqfIep65ipZAmdpAAIcgjE=_AP//CqqfIep65ipZAmdpAAIciwAA"
does not exist
STATEMENT:  ROLLBACK PREPARED
'131077_AP//CqqfIep65ipZAmdpAAIcgjE=_AP//CqqfIep65ipZAmdpAAIciwAA'
ERROR:  prepared transaction with identifier
"131077_AP//Cqq6U+K0bPBZAmdRAAIpmTE=_AP//Cqq6U+K0bPBZAmdRAAIpoAAA"
does not exist
STATEMENT:  ROLLBACK PREPARED
'131077_AP//Cqq6U+K0bPBZAmdRAAIpmTE=_AP//Cqq6U+K0bPBZAmdRAAIpoAAA'
ERROR:  prepared transaction with identifier
"131077_AP//Cqq6UmqUdDpZAmc2AAJPFDE=_AP//Cqq6UmqUdDpZAmc2AAJPGgAA"
does not exist
STATEMENT:  ROLLBACK PREPARED
'131077_AP//Cqq6UmqUdDpZAmc2AAJPFDE=_AP//Cqq6UmqUdDpZAmc2AAJPGgAA'
ERROR:  prepared transaction with identifier
"131077_AP//Cqq6QiDZF3BZAjkeABef2TE=_AP//Cqq6QiDZF3BZAjkeABef4QAA"
does not exist
STATEMENT:  ROLLBACK PREPARED
'131077_AP//Cqq6QiDZF3BZAjkeABef2TE=_AP//Cqq6QiDZF3BZAjkeABef4QAA'
ERROR:  prepared transaction with identifier
"131077_AP//Cqq6QiDZF3BZAjkeABen1DE=_AP//Cqq6QiDZF3BZAjkeABen2AAA"
does not exist
STATEMENT:  ROLLBACK PREPARED
'131077_AP//Cqq6QiDZF3BZAjkeABen1DE=_AP//Cqq6QiDZF3BZAjkeABen2AAA'
ERROR:  prepared transaction with identifier
"131077_AP//Cqq6abHV5e9ZAmJCAAN/7DE=_AP//Cqq6abHV5e9ZAmJCAAOAHwAA"
does not exist
STATEMENT:  ROLLBACK PREPARED
'131077_AP//Cqq6abHV5e9ZAmJCAAN/7DE=_AP//Cqq6abHV5e9ZAmJCAAOAHwAA'
ERROR:  prepared transaction with identifier
"131077_AP//Cqq6U+K0bPBZAmdRAAJtczE=_AP//Cqq6U+K0bPBZAmdRAAJthQAA"
does not exist
STATEMENT:  ROLLBACK PREPARED
'131077_AP//Cqq6U+K0bPBZAmdRAAJtczE=_AP//Cqq6U+K0bPBZAmdRAAJthQAA'
ERROR:  prepared transaction with identifier
"131077_AP//CqqfIep65ipZAmdpAAKVDjE=_AP//CqqfIep65ipZAmdpAAKVFQAA"
does not exist
STATEMENT:  ROLLBACK PREPARED
'131077_AP//CqqfIep65ipZAmdpAAKVDjE=_AP//CqqfIep65ipZAmdpAAKVFQAA'
ERROR:  prepared transaction with identifier
"131077_AP//CqqfIep65ipZAmdpAAK4bDE=_AP//CqqfIep65ipZAmdpAAK4cAAA"
does not exist
"postgresql-Fri.log" 313L, 30355C

Regards,
SSR

Re: [GENERAL] PostgreSQL Required Monitoring

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andrew Kerber
Sent: Friday, April 28, 2017 12:58 PM
To: Scott Marlowe 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL Required Monitoring


Attention: This email was sent from someone outside of Perceptron. Always 
exercise caution when opening attachments or clicking links from unknown 
senders or when receiving unexpected emails.

Yes, that was the first item on my list (disk space)...

On Fri, Apr 28, 2017 at 11:56 AM, Scott Marlowe 
> wrote:
On Fri, Apr 28, 2017 at 8:39 AM, Andrew Kerber 
> wrote:
> I am a fairly experienced Oracle DBA, and we are starting to move in to the
> PostgreSQL world.  I would expect the standard monitoring items are required
> for mission critical postgres apps, Ie, disk space, wal log space, log
> monitoring, process counts,software running, connection available on the
> correct port, CPU usage.
>
> Are there additional PostgreSQL specific items that need to be monitored?
> if so, what items?

Amid all the other recommendations standard OS level monitoring is a
good idea. You don't wanna run out of space on drives etc.



--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

And there lies another difference with Oracle: Postgres does not allocate disk 
space for data files “in advance” (like Oracle does).

Regards,
Igor Neyman

Re: [GENERAL] PostgreSQL Required Monitoring

[Andrew Kerber]

Yes, that was the first item on my list (disk space)...

On Fri, Apr 28, 2017 at 11:56 AM, Scott Marlowe 
wrote:

> On Fri, Apr 28, 2017 at 8:39 AM, Andrew Kerber 
> wrote:
> > I am a fairly experienced Oracle DBA, and we are starting to move in to
> the
> > PostgreSQL world.  I would expect the standard monitoring items are
> required
> > for mission critical postgres apps, Ie, disk space, wal log space, log
> > monitoring, process counts,software running, connection available on the
> > correct port, CPU usage.
> >
> > Are there additional PostgreSQL specific items that need to be monitored?
> > if so, what items?
>
> Amid all the other recommendations standard OS level monitoring is a
> good idea. You don't wanna run out of space on drives etc.
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] PostgreSQL Required Monitoring

[Scott Marlowe]

On Fri, Apr 28, 2017 at 8:39 AM, Andrew Kerber  wrote:
> I am a fairly experienced Oracle DBA, and we are starting to move in to the
> PostgreSQL world.  I would expect the standard monitoring items are required
> for mission critical postgres apps, Ie, disk space, wal log space, log
> monitoring, process counts,software running, connection available on the
> correct port, CPU usage.
>
> Are there additional PostgreSQL specific items that need to be monitored?
> if so, what items?

Amid all the other recommendations standard OS level monitoring is a
good idea. You don't wanna run out of space on drives etc.


-- 
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] PostgreSQL Required Monitoring

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andrew Kerber
Sent: Friday, April 28, 2017 12:22 PM
To: John R Pierce 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PostgreSQL Required Monitoring


Attention: This email was sent from someone outside of Perceptron. Always 
exercise caution when opening attachments or clicking links from unknown 
senders or when receiving unexpected emails.

yes, replication monitoring is high on the oracle list also, just forgot to 
mention it.  I ran into a similar transaction issue in Oracle when they were 
running queries across database links and not committing.  Its a little known 
fact that any oracle query that runs across a database link starts a 
transaction even without any Insert/update/delete command, so I had to explain 
that to my developers.

On Fri, Apr 28, 2017 at 11:04 AM, John R Pierce 
> wrote:
On 4/28/2017 7:39 AM, Andrew Kerber wrote:
I am a fairly experienced Oracle DBA, and we are starting to move in to the 
PostgreSQL world.  I would expect the standard monitoring items are required 
for mission critical postgres apps, Ie, disk space, wal log space, log 
monitoring, process counts,software running, connection available on the 
correct port, CPU usage.

the nagios project has a rather handy monitoring script, check_postgres, this 
is a perl script that can be invoked from most any configurable monitoring 
framework, and has options to do 100s of different sorts of things, returning 
simple terse text output that can be parsed by said monitoring framework.

Are there additional PostgreSQL specific items that need to be monitored?  if 
so, what items?


its always a good idea to watch for stale 'idle in transaction' connections, as 
they gum up the important VACUUM processing.   you can make a simple query 
against pg_stat_activity to find the oldest 'idle in transaction', and if there 
are any more than, say, 1 hour old, its worth tracking down why they are 
happening and hammering the developers to fix it.oracle developers working 
in java seem to generate a lot of these (speaking from experience) if they 
aren't careful to avoid it.   Postgres JDBC starts a transaction on a simple 
SELECT, and if the app then just sits there doing nothing, that transaction 
stays open indefinitely.   I had a lot of pushback from developers insisting 
that SELECT's should not need commit.

the one big thing I don't see mentioned in your list above is monitoring 
replication

--
john r pierce, recycling bits in santa cruz




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



--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

I’d monitor not only “idle in transaction” but also just “idle” connections 
(and their state change – how long they’ve been idle), especially considering 
that unlike Oracle Postgres doesn’t have (yet) built-in connection pooler, so 
there is risk to run out of connections. There are very good add-on poolers 
such as PgBouncer, PgPool.

Regards,
Igor Neyman

Re: [GENERAL] PostgreSQL Required Monitoring

[Andrew Kerber]

yes, replication monitoring is high on the oracle list also, just forgot to
mention it.  I ran into a similar transaction issue in Oracle when they
were running queries across database links and not committing.  Its a
little known fact that any oracle query that runs across a database link
starts a transaction even without any Insert/update/delete command, so I
had to explain that to my developers.

On Fri, Apr 28, 2017 at 11:04 AM, John R Pierce  wrote:

> On 4/28/2017 7:39 AM, Andrew Kerber wrote:
>
>> I am a fairly experienced Oracle DBA, and we are starting to move in to
>> the PostgreSQL world.  I would expect the standard monitoring items are
>> required for mission critical postgres apps, Ie, disk space, wal log space,
>> log monitoring, process counts,software running, connection available on
>> the correct port, CPU usage.
>>
>
> the nagios project has a rather handy monitoring script, check_postgres,
> this is a perl script that can be invoked from most any configurable
> monitoring framework, and has options to do 100s of different sorts of
> things, returning simple terse text output that can be parsed by said
> monitoring framework.
>
>
> Are there additional PostgreSQL specific items that need to be monitored?
>> if so, what items?
>>
>
>
> its always a good idea to watch for stale 'idle in transaction'
> connections, as they gum up the important VACUUM processing.   you can make
> a simple query against pg_stat_activity to find the oldest 'idle in
> transaction', and if there are any more than, say, 1 hour old, its worth
> tracking down why they are happening and hammering the developers to fix
> it.oracle developers working in java seem to generate a lot of these
> (speaking from experience) if they aren't careful to avoid it.   Postgres
> JDBC starts a transaction on a simple SELECT, and if the app then just sits
> there doing nothing, that transaction stays open indefinitely.   I had a
> lot of pushback from developers insisting that SELECT's should not need
> commit.
>
> the one big thing I don't see mentioned in your list above is monitoring
> replication
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] PostgreSQL Required Monitoring

[John R Pierce]

On 4/28/2017 7:39 AM, Andrew Kerber wrote:
I am a fairly experienced Oracle DBA, and we are starting to move in 
to the PostgreSQL world.  I would expect the standard monitoring items 
are required for mission critical postgres apps, Ie, disk space, wal 
log space, log monitoring, process counts,software running, connection 
available on the correct port, CPU usage.


the nagios project has a rather handy monitoring script, check_postgres, 
this is a perl script that can be invoked from most any configurable 
monitoring framework, and has options to do 100s of different sorts of 
things, returning simple terse text output that can be parsed by said 
monitoring framework.



Are there additional PostgreSQL specific items that need to be 
monitored?  if so, what items? 



its always a good idea to watch for stale 'idle in transaction' 
connections, as they gum up the important VACUUM processing.   you can 
make a simple query against pg_stat_activity to find the oldest 'idle in 
transaction', and if there are any more than, say, 1 hour old, its worth 
tracking down why they are happening and hammering the developers to fix 
it.oracle developers working in java seem to generate a lot of these 
(speaking from experience) if they aren't careful to avoid it.   
Postgres JDBC starts a transaction on a simple SELECT, and if the app 
then just sits there doing nothing, that transaction stays open 
indefinitely.   I had a lot of pushback from developers insisting that 
SELECT's should not need commit.


the one big thing I don't see mentioned in your list above is monitoring 
replication


--
john r pierce, recycling bits in santa cruz



--
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] PostgreSQL Required Monitoring

[Jan Keirse]

On Fri, Apr 28, 2017 at 4:39 PM, Andrew Kerber 
wrote:

> I am a fairly experienced Oracle DBA, and we are starting to move in to
> the PostgreSQL world.  I would expect the standard monitoring items are
> required for mission critical postgres apps, Ie, disk space, wal log space,
> log monitoring, process counts,software running, connection available on
> the correct port, CPU usage.
>
> Are there additional PostgreSQL specific items that need to be monitored?
> if so, what items?
>
>
​In addition to what has already been mentioned I also monitor pg_locks .​
Not just counts but also mode, duration and non granted locks.

-- 


 DISCLAIMER 

http://www.tvh.com/glob/en/email-disclaimer

"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this
message."

Re: [GENERAL] PostgreSQL Required Monitoring

[Melvin Davidson]

On Fri, Apr 28, 2017 at 10:46 AM, Joshua D. Drake 
wrote:

> On 04/28/2017 07:39 AM, Andrew Kerber wrote:
>
>> I am a fairly experienced Oracle DBA, and we are starting to move in to
>> the PostgreSQL world.  I would expect the standard monitoring items are
>> required for mission critical postgres apps, Ie, disk space, wal log
>> space, log monitoring, process counts,software running, connection
>> available on the correct port, CPU usage.
>>
>> Are there additional PostgreSQL specific items that need to be
>> monitored?  if so, what items?
>>
>
> Pretty much anything that starts with pg_stat_
>
> JD
>
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
> +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Everyone appreciates your honesty, until you are honest with them.
> Unless otherwise stated, opinions are my own.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

One thing I always monitor is index sizes, usage and status. See attached
pg_stat_all_indexes.sh

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


pg_stat_all_indexes.sh
Description: Bourne shell script

-- 
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] PostgreSQL Required Monitoring

[Joshua D. Drake]

On 04/28/2017 07:39 AM, Andrew Kerber wrote:

I am a fairly experienced Oracle DBA, and we are starting to move in to
the PostgreSQL world.  I would expect the standard monitoring items are
required for mission critical postgres apps, Ie, disk space, wal log
space, log monitoring, process counts,software running, connection
available on the correct port, CPU usage.

Are there additional PostgreSQL specific items that need to be
monitored?  if so, what items?


Pretty much anything that starts with pg_stat_

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[GENERAL] PostgreSQL Required Monitoring

[Andrew Kerber]

I am a fairly experienced Oracle DBA, and we are starting to move in to the
PostgreSQL world.  I would expect the standard monitoring items are
required for mission critical postgres apps, Ie, disk space, wal log space,
log monitoring, process counts,software running, connection available on
the correct port, CPU usage.

Are there additional PostgreSQL specific items that need to be monitored?
if so, what items?

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

Re: [GENERAL] GDAL OUT-DB RASTER SUPPORT

[Adrian Klaver]

On 04/28/2017 07:09 AM, Osahon Oduware wrote:

Hi All,

I am getting a black image when I try to display PostGIS out-db rasters.
I discovered that it has to do with GDAL support for out-db rasters as
stated in the link below for a similar issue (over 1 year back):
https://hub.qgis.org/issues/12133

The last comment by Jürgen Fischer states "... only black images. So
it's a dependency issue (GDAL complains ERROR 1: This raster has outdb
storage. This feature isn't still available)."

I have also been told in the link below that "out-db support is not
implemented yet in the GDAL PostGISRaster driver.":
https://lists.osgeo.org/pipermail/gdal-dev/2017-April/046512.html

I would like to know how to solve this issue of displaying postgis
out-db rasters in QGIS?



Seems to me that it is about GDAL having support in their PostGISRaster 
driver for file located raster images.


There is an issue for it:

https://trac.osgeo.org/gdal/ticket/3234

According to it out-db support was implemented in PostGIS 2.0. So I 
would say you need to ask the PostGIS list what the situation really is. 
This list(--general) is not going not be of much help as what is you are 
asking about is outside the code the core Postgres community maintains.



--
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] Postgres 9.6.2 and pg_log - solved

[Mark Watson]

De : pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] De la part de Mark Watson
Envoyé : Monday, April 24, 2017 3:39 PM
À : David G. Johnston
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log


De : David G. Johnston [mailto:david.g.johns...@gmail.com]
Envoyé : Monday, April 24, 2017 3:15 PM
À : Mark Watson
Cc : (pgsql-general@postgresql.org)
Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log

On Mon, Apr 24, 2017 at 11:27 AM, Mark Watson 
> wrote:

log_destination = 'stderr' # Valid values are 
combinations of

# stderr, csvlog, syslog, and eventlog,

# depending on platform.  csvlog

# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on# Enable capturing of stderr and 
csvlog

# into log files. Required to be on for

# csvlogs.

# (change requires restart)


​I'm out of ideas...

David J.​
Not to worry. This week I’m uninstalling and reinstalling postgres 9.6. I’ll do 
some tests and let this list know.

Mark Watson
Solved (sort of)
After a complete uninstall and fresh install of 9.6.2,  everything is behaving 
as normal. I have a tendency to chalk it off to perhaps an artifact from 
updating 9.6 beta => 9.6 RC => 9.6.0 => 9.6.1 => 9.6.2. Anyway, I’m not keen on 
repeating the updates. By the way, thanks all for the excellent work with 9.6 !

Mark Watson

[GENERAL] clarification about async streaming replication and replication slots

[Tom DalPozzo]

Hi,
I need a clarification about async streaming replication and replication
slots.
I have a master and a standby both with server process running but with
network disconnected between them.

In my primary, I see:
  pg_current_xlog_location = B/48DFF790
  and, through pg_replication_slots, that standby restart_lsn =  B/48DFF6E8.

In my standby, I see:
 pg_last_xlog_receive_location = B/4800
 pg_last_xlog_replay_location =  B/48DFF6E8

My questions are:
1) why is  pg_last_xlog_receive_location< pg_last_xlog_replay_location ?
The standby should receive some parts from master's WAL, write them down to
its WAL  and then replay them, right?
2) Can I compare  master's   pg_current_xlog_location against
pg_replication_slots.restart_lsn
in order to check if the standby is synchronized?
Regards
Pupillo

Re: [GENERAL] LIMIT clause slowing down query in some cases, accelerating in others

[Vik Fearing]

On 04/26/2017 08:11 PM, Klaus P. Pieper wrote:
>
> Running PostgreSQL 9.6 on a Windows Server.
>
> Table “t” is kind of a materialized view with > 100 columns and 2.24
> Mio rows. Queries are generated by an ORM framework – fairly difficult
> to modify.
>
> Vacuum analyze was carried out – no impact.
>
>  
>
> The framework generates queries like this:
>
>  
>
> select N0."uorderid" from "t" N0
>
> where (N0."szzip" like E'33%')
>
> order by N0."szzip" asc nulls last LIMIT 128 OFFSET 0
>

The planner has to choose whether to use an index for filtering or an
index for sorting.  If you're always doing prefix searches like in your
two examples, then you want an index which can do both.

CREATE INDEX ON t (szzip text_pattern_ops, uorderid);

I invite you to read the documentation about text_pattern_ops at
https://www.postgresql.org/docs/current/static/indexes-opclass.html

-- 
Vik Fearing  +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support