Re: User-defined print format for extension-defined types in psql output

[Laurenz Albe]

Peter Devoy wrote:
> To make inspecting PostGIS tables in psql easier I have written a
> function which outputs PostGIS Geometry objects as a string of ASCII
> art.
> 
> Please does anyone know if there is there some way I can have my
> function called automatically by psql instead of me writing it into
> the query each time?  E.g. a user defined \pset option or some sort of
> extension API which gives me access to the print logic or
> 'presentation layer' of psql.
> 
> By 'automatically' I mean through association with the PostGIS geometry types.

All I can think of is changing the output function of the PostGIS data types,
but that would require a C function.

Yours,
Laurenz Albe

Re: Dependency tree to tie type/function deps to a table

[Jeremy Finzel]

Here is my current effort.  I would love feedback in case I've missed
something.  I also know there is perhaps a little redundancy in the
recursion but it's looking quick enough.  The query below has the relname
of the base table hardcoded but I would generalize this once happy with it.

WITH RECURSIVE base AS (
  SELECT DISTINCT
1 AS level,
classid,
objid,
refclassid,
refobjid,
ev_class,
cv.relname AS view_name
  FROM pg_depend d
  /
  Get the view oid and name if it's a view
   */
LEFT JOIN pg_rewrite r
  ON d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
  AND r.oid = d.objid
LEFT JOIN pg_class cv
  ON cv.oid = r.ev_class
  /
  This is designed to look for a single object's dependencies for use with
drop/recreate
  But could perhaps be tweaked if we want to look for something else or
multiple base objects
   */
  WHERE refobjid = (SELECT oid
FROM pg_class
WHERE relname = 'foo1' AND relpersistence = 'p')
--Ignore cases where view oid = refobjid
AND (d.refobjid <> r.ev_class OR r.ev_class IS NULL)
  UNION ALL
  SELECT DISTINCT
level + 1 AS level,
d.classid,
d.objid,
d.refclassid,
d.refobjid,
r.ev_class,
cv.relname AS view_name
  FROM pg_depend d
  INNER JOIN base b
  /***
  If it's a view, get the view oid from pg_rewrite to look for that
dependency
  instead of the rule.  Otherwise, use classid and objid as-is.
   */
   ON CASE
WHEN b.ev_class IS NULL THEN d.refclassid = b.classid
ELSE d.refclassid = (SELECT oid FROM pg_class WHERE relname =
'pg_class')
  END
   AND
  CASE
WHEN b.ev_class IS NULL THEN d.refobjid = b.objid
ELSE d.refobjid = b.ev_class
  END
  LEFT JOIN pg_rewrite r
ON d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
AND r.oid = d.objid
  LEFT JOIN pg_class cv
ON cv.oid = r.ev_class
  WHERE
--prevent infinite recursion - probably should be removed if the query
is right
  level < 10
--no identical matches with base
  AND NOT (d.classid = b.classid AND d.objid = b.objid AND d.refclassid
= b.refclassid AND d.refobjid = b.refobjid)
--Ignore cases where view oid = refobjid
  AND (d.refobjid <> r.ev_class OR r.ev_class IS NULL)
)

/***
Since we know there are at least a few duplicates in classid + objid,
only find unique cases, but find row_number order.
 */
, distinct_objs AS (
SELECT DISTINCT ON (classid, objid)
classid, objid, view_name, ev_class, rn
FROM
(SELECT *,
   ROW_NUMBER() OVER() AS rn
FROM base) brn
ORDER BY classid, objid, rn
)

, objects_we_want_to_recreate AS
(
SELECT
  /***
  Describe/identify view instead of rule if it's a view, otherwise, take
classid and objid as-is
   */
  CASE WHEN view_name IS NOT NULL
THEN pg_describe_object((SELECT oid FROM pg_class WHERE relname =
'pg_class'), d.ev_class, 0)
ELSE pg_describe_object(classid, objid, 0)
END AS desc_obj,
  CASE WHEN view_name IS NOT NULL
THEN (pg_identify_object((SELECT oid FROM pg_class WHERE relname =
'pg_class'), d.ev_class, 0)).type
ELSE (pg_identify_object(classid, objid, 0)).type
END AS ident_type,
  CASE WHEN view_name IS NOT NULL
THEN (pg_identify_object((SELECT oid FROM pg_class WHERE relname =
'pg_class'), d.ev_class, 0)).identity
ELSE (pg_identify_object(classid, objid, 0)).identity
END AS ident_identity,
  classid,
  objid,
  view_name,
  rn
FROM distinct_objs d
LEFT JOIN pg_type t
ON d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_type')
AND t.oid = d.objid
LEFT JOIN pg_class tc
ON tc.oid = t.typrelid
WHERE ((t.typtype <> 'b' --ignore base types
and tc.relkind = 'c' --no need to manually drop and recreate types
tied to other relkinds
   )
   or t.oid is null)
)

SELECT * FROM objects_we_want_to_recreate ORDER BY rn DESC;

Here is a little example:

CREATE TABLE foo1 (id int);
CREATE TABLE foo2 (id int);
CREATE VIEW foo3 AS
SELECT f.id, f2.id AS id2
FROM foo1 f CROSS JOIN foo2 f2;
CREATE VIEW foo4 AS
SELECT f.id, f2.id AS id2
FROM foo1 f CROSS JOIN foo3 f2;
CREATE VIEW foo5 AS
SELECT * FROM foo4;
CREATE FUNCTION foo() RETURNS SETOF foo1 AS 'SELECT * FROM foo1;' LANGUAGE
SQL;
CREATE FUNCTION foo6() RETURNS SETOF foo5 AS 'SELECT * FROM foo5;' LANGUAGE
SQL;
CREATE MATERIALIZED VIEW foo8 AS
SELECT * FROM foo1;
CREATE TYPE foo9 AS (foo foo1, bar text);

And query results:
*desc_obj* *ident_type* *ident_identity* *classid* *objid* *view_name* *rn*
function foo6() function public.foo6() 1255 24182 19
composite type foo9 composite type public.foo9 1259 24187 11
view foo5 view public.foo5 2618 24180 foo5 8
function foo() function public.foo() 1255 24181 6
materialized view foo8 materialized view public.foo8 2618 24186 foo8 4
view foo4 view public.foo4 2618 24176 foo4 3
view foo3 view public.foo3 2618 24172 foo3 2

If I drop these in order of appearance, it all works and finally lets me
drop table foo1 

Clarification on PL/pgSQL query plan caching

[George Woodring]

We were experiencing insert slowdowns at the beginning of the day when we
add new tables.  As part of our data insert process, we have a read
function and we decided to modify it to use EXECUTE to avoid plan caching.
Our assumption was was the adding the table would invalidate the plan for
the current running connection.

This fixed our issue, but the surprising side affect is that it took 100
sec off of our runtime at the other parts of the day.

I have added the before and after examples, I am wondering on the *why*
writing it in the before example is bad?  Function only has one plan?

BEFORE:
  IF ptype = 'data' THEN
 SELECT lasttime, lastval INTO mcurr FROM d_current WHERE lasttime
> pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC LIMIT 1;
  ELSIF ptype = 'idata' THEN
 SELECT lasttime, lastval INTO mcurr FROM c_current WHERE
lasttime > pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC
LIMIT 1;
  ELSIF ptype = 'ddata' THEN
 SELECT lasttime, lastval INTO mcurr FROM c_current WHERE lasttime
> pdate - '1 hour'::interval AND id = pid  ORDER BY lasttime DESC LIMIT 1;
  ELSIF ptype = 'ldata' THEN
 SELECT lasttime, lastvall INTO mcurr FROM dl_current WHERE
lasttime > pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC
LIMIT 1;
  ELSE
 SELECT lasttime, lastval INTO mcurr FROM current WHERE lasttime >
pdate - '1 hour'::interval AND id = pid ORDER BY lasttime DESC LIMIT 1;
  END IF;
  IF NOT FOUND THEN
 -- RAISE DEBUG 'No previous data found for pgid=%', pgid;
 return NULL;
  END IF;

AFTER:
  limiter timestamptz := pdate - '1 hour'::interval;

  IF ptype = 'data' THEN
 table_name := 'd_current';
  ELSIF ptype = 'ldata' THEN
 table_name := 'dl_current';
  ELSIF ptype = 'idata' THEN
 table_name := 'c_current';
  ELSIF ptype = 'ddata' THEN
 table_name := 'c_current';
  END IF;

  EXECUTE 'SELECT lasttime, lastval FROM ' || table_name || ' WHERE
lasttime > $1 AND id = $2 ORDER BY lasttime DESC LIMIT 1' INTO mcurr USING
limiter, pid;
  IF mcurr IS NULL THEN
 -- RAISE DEBUG 'No previous data found for pgid=%', pgid;
 return NULL;
  END IF;

Thanks,
George Woodring
iGLASS Networks
www.iglass.net

pgbench

[Olga Lytvynova-Bogdanova]

Hello,
Is there a way to integrate pgbench with TeamCity? If yes, could you share
very briefly how to do this?
Which volumes of data does pgbench support?
Much appreciated for the attention.

Replication questions - read-only and temporary read/write slaves

[Tiffany Thang]

Hi,
In PostgreSQL, would it be possible to

1. set up a read-only slave database? The closest solution I could find is
Hot Standby but the slave would not be accessible until after a failover.

2. temporary convert a read-only slave in read-write mode for testing
read/write workloads? Currently in Oracle, we can temporary open our
read-only standby database in read-write mode to occasionally test our
read-write workloads. We would stop the log apply on the standby database,
convert the read-only database to read-write,
perform our read/write test, discard all the changes after testing and
reopen and resync the standby database in read-only mode. Is there a
similar feature in PostgreSQL or are there ways to achieve something close
to our needs?

Thanks.

Tiff

Re: Replication questions - read-only and temporary read/write slaves

[Magnus Hagander]

On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang 
wrote:

> Hi,
> In PostgreSQL, would it be possible to
>
> 1. set up a read-only slave database? The closest solution I could find is
> Hot Standby but the slave would not be accessible until after a failover.
>

Hot Standby will give you a standby database that is accessible, but in
read-only mode. This sounds like what you're looking for.



> 2. temporary convert a read-only slave in read-write mode for testing
> read/write workloads? Currently in Oracle, we can temporary open our
> read-only standby database in read-write mode to occasionally test our
> read-write workloads. We would stop the log apply on the standby database,
> convert the read-only database to read-write,
> perform our read/write test, discard all the changes after testing and
> reopen and resync the standby database in read-only mode. Is there a
> similar feature in PostgreSQL or are there ways to achieve something close
> to our needs?
>

No, you can't do this with postgres natively.

You could snapshot your filesystem before opening it and then roll back to
that snapshot, or something like that, but you cannot do it with just
PostgreSQL functionality.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: Replication questions - read-only and temporary read/write slaves

[Tiffany Thang]

Thanks Magnus. I did not realize I could use the Hot Standby in read-only
mode.

For #2, would it be possible to open the Hot Standby in read/write after
breaking the replication and taking a snapshot or can Hot Standby only be
open in read/write after a failover? I hoping I can use the same Hot
Standby for both #1 and #2.

Thanks again.


On Fri, Dec 15, 2017 at 12:55 PM, Magnus Hagander 
wrote:

> On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang 
> wrote:
>
>> Hi,
>> In PostgreSQL, would it be possible to
>>
>> 1. set up a read-only slave database? The closest solution I could find
>> is Hot Standby but the slave would not be accessible until after a failover.
>>
>
> Hot Standby will give you a standby database that is accessible, but in
> read-only mode. This sounds like what you're looking for.
>
>
>
>> 2. temporary convert a read-only slave in read-write mode for testing
>> read/write workloads? Currently in Oracle, we can temporary open our
>> read-only standby database in read-write mode to occasionally test our
>> read-write workloads. We would stop the log apply on the standby database,
>> convert the read-only database to read-write,
>> perform our read/write test, discard all the changes after testing and
>> reopen and resync the standby database in read-only mode. Is there a
>> similar feature in PostgreSQL or are there ways to achieve something close
>> to our needs?
>>
>
> No, you can't do this with postgres natively.
>
> You could snapshot your filesystem before opening it and then roll back to
> that snapshot, or something like that, but you cannot do it with just
> PostgreSQL functionality.
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/ 
>  Work: https://www.redpill-linpro.com/ 
>

Re: Replication questions - read-only and temporary read/write slaves

[Stefano]

For n.2, you can promote the standby to became a standalone (r/w) server.
This may be done via "pg_ctl -D $PGDATA promote" or, if in the
recovery.conf a "triggerfile" definition has been set, touch-ing the
triggerfile.
see https://www.postgresql.org/docs/current/static/standby-settings.html
https://www.postgresql.org/docs/10/static/app-pg-ctl.html

regards

2017-12-15 19:30 GMT+01:00 Tiffany Thang :

> Thanks Magnus. I did not realize I could use the Hot Standby in read-only
> mode.
>
> For #2, would it be possible to open the Hot Standby in read/write after
> breaking the replication and taking a snapshot or can Hot Standby only be
> open in read/write after a failover? I hoping I can use the same Hot
> Standby for both #1 and #2.
>
> Thanks again.
>
>
> On Fri, Dec 15, 2017 at 12:55 PM, Magnus Hagander 
> wrote:
>
>> On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang 
>> wrote:
>>
>>> Hi,
>>> In PostgreSQL, would it be possible to
>>>
>>> 1. set up a read-only slave database? The closest solution I could find
>>> is Hot Standby but the slave would not be accessible until after a failover.
>>>
>>
>> Hot Standby will give you a standby database that is accessible, but in
>> read-only mode. This sounds like what you're looking for.
>>
>>
>>
>>> 2. temporary convert a read-only slave in read-write mode for testing
>>> read/write workloads? Currently in Oracle, we can temporary open our
>>> read-only standby database in read-write mode to occasionally test our
>>> read-write workloads. We would stop the log apply on the standby database,
>>> convert the read-only database to read-write,
>>> perform our read/write test, discard all the changes after testing and
>>> reopen and resync the standby database in read-only mode. Is there a
>>> similar feature in PostgreSQL or are there ways to achieve something close
>>> to our needs?
>>>
>>
>> No, you can't do this with postgres natively.
>>
>> You could snapshot your filesystem before opening it and then roll back
>> to that snapshot, or something like that, but you cannot do it with just
>> PostgreSQL functionality.
>>
>> --
>>  Magnus Hagander
>>  Me: https://www.hagander.net/ 
>>  Work: https://www.redpill-linpro.com/ 
>>
>
>


-- 
/* === */

"Il libero scambio è come la libera volpe nel libero pollaio"

Serge Latouche, Bergamo, Maggio 2015
/* === */

Re: Dependency tree to tie type/function deps to a table

[bricklen]

On Fri, Dec 15, 2017 at 6:44 AM, Jeremy Finzel  wrote:

> Here is my current effort.  I would love feedback in case I've missed
> something.
>

Perhaps you'll find the version on the wiki useful as reference, it's an
older version of the dependencies, and can be found at
https://wiki.postgresql.org/wiki/Pg_depend_display​

Re: Replication questions - read-only and temporary read/write slaves

[Tiffany Thang]

Thanks!

On Fri, Dec 15, 2017 at 1:56 PM, Stefano  wrote:

> For n.2, you can promote the standby to became a standalone (r/w) server.
> This may be done via "pg_ctl -D $PGDATA promote" or, if in the
> recovery.conf a "triggerfile" definition has been set, touch-ing the
> triggerfile.
> see https://www.postgresql.org/docs/current/static/standby-settings.html
> https://www.postgresql.org/docs/10/static/app-pg-ctl.html
>
> regards
>
> 2017-12-15 19:30 GMT+01:00 Tiffany Thang :
>
>> Thanks Magnus. I did not realize I could use the Hot Standby in read-only
>> mode.
>>
>> For #2, would it be possible to open the Hot Standby in read/write after
>> breaking the replication and taking a snapshot or can Hot Standby only be
>> open in read/write after a failover? I hoping I can use the same Hot
>> Standby for both #1 and #2.
>>
>> Thanks again.
>>
>>
>> On Fri, Dec 15, 2017 at 12:55 PM, Magnus Hagander 
>> wrote:
>>
>>> On Fri, Dec 15, 2017 at 6:03 PM, Tiffany Thang 
>>> wrote:
>>>
 Hi,
 In PostgreSQL, would it be possible to

 1. set up a read-only slave database? The closest solution I could find
 is Hot Standby but the slave would not be accessible until after a 
 failover.

>>>
>>> Hot Standby will give you a standby database that is accessible, but in
>>> read-only mode. This sounds like what you're looking for.
>>>
>>>
>>>
 2. temporary convert a read-only slave in read-write mode for testing
 read/write workloads? Currently in Oracle, we can temporary open our
 read-only standby database in read-write mode to occasionally test our
 read-write workloads. We would stop the log apply on the standby database,
 convert the read-only database to read-write,
 perform our read/write test, discard all the changes after testing and
 reopen and resync the standby database in read-only mode. Is there a
 similar feature in PostgreSQL or are there ways to achieve something close
 to our needs?

>>>
>>> No, you can't do this with postgres natively.
>>>
>>> You could snapshot your filesystem before opening it and then roll back
>>> to that snapshot, or something like that, but you cannot do it with just
>>> PostgreSQL functionality.
>>>
>>> --
>>>  Magnus Hagander
>>>  Me: https://www.hagander.net/ 
>>>  Work: https://www.redpill-linpro.com/ 
>>>
>>
>>
>
>
> --
> /* === */
>
> "Il libero scambio è come la libera volpe nel libero pollaio"
>
> Serge Latouche, Bergamo, Maggio 2015
> /* === */
>

Re: pgbench

[John R Pierce]

On 12/15/2017 7:37 AM, Olga Lytvynova-Bogdanova wrote:
Is there a way to integrate pgbench with TeamCity? If yes, could you 
share very briefly how to do this?


I would suspect this is a question for TeamCity, not for postgresql.   I 
don't even know what TeamCity actually is (google says 'Continuous 
Integration' but thats just a buzz phrase to me). Can it run shell 
scripts?




Which volumes of data does pgbench support?
I'm unclear what you mean by 'which volumes of data' ?   do you mean, 
how large of a dataset does pgbench generate and use?   thats totally 
configurable with commandline parameters, from a few dozen kilobytes to 
many gigabytes.



--
john r pierce, recycling bits in santa cruz

Re: PgBackRest question?

[chiru r]

Hi All,

Thanks,I am thinking about a specific recovery case.

Lets assume Heavy transactional system we configured.
It is generating WAL 2000/hr and recycling automatically in pg_wal
directory.

QA :

Sunday -- 11 PM  -- Full backup done.
Monday -- 11 PM  -- Differential Backup done
Tuesday-- 10 AM incremental backup is done
Note :  Every  2 hrs  incremental backup scheduled on system.

For example, if we want to restore to DEV server:

We want to recover database as of 11:30 AM Tuesday on DEV server using QA
backups.

Is it possible to restore using pgbackrest tool for this scenario?.

How pgbackrest keeps track of transactions since the last backup? Where it
stores transaction information for recovery ?.

Thanks,
Chiru


On Wed, Dec 13, 2017 at 5:45 PM, Stephen Frost  wrote:

> Greetings,
>
> * chiru r (chir...@gmail.com) wrote:
> > The pgbackrest.conf  configuration file has the option *repo-path* to
> mention
> > the mount point to store the backups and wal archive files.
>
> That's correct, that's where the backups and the WAL are stored.
>
> > We are looking for an option, Can we store online backups and wal archive
> > files separately in different directories?.
>
> That's an interesting requirement- why would you need them to be
> different directories?
>
> I'll point out that PostgreSQL backups absolutely require the WAL in
> order to be able to be restored (at least the WAL generated during the
> backup) in order to reach consistency.  If the two were to be seperated,
> you'd probably still want the backups to 'stand alone' and that would
> mean duplicating all of the WAL which is created during the backup and
> storing it with the backup.
>
> The first question really is why you're looking for this though..?  If
> there's a good use-case for it, we could look at adding that it as an
> option.
>
> Thanks!
>
> Stephen
>