Re: Programmatically duplicating a schema

[Adrian Klaver]

On 03/12/2018 08:23 PM, matt.f...@internode.on.net wrote:

Hi all,

What is a reliable way to programmatically & generically populate an 
empty schema with all the objects in the public schema as a template?


We are using the multi tenancy ruby gem Apartment ( 
https://github.com/influitive/apartment ), which was recently broken by 
the changes made to pg_dump to address CVE-2018-1058 
https://nvd.nist.gov/vuln/detail/CVE-2018-1058


Apartment attempts to duplicate the public schema whenever creating a 
new schema by running:


pg_dump -s -x -0 -n public

to get the SQL statements needed to recreate the public schema & then 
executes the pg_dump's sql output after creating & switching to the new 
schema ( via set search_path to ; )


After the fix to CVE-2018-1058, all table references in pg_dump's output 
(including within SQL of stored procedures) are prefixed by the public. 
schema, which means you cannot just reuse this output in a different 
schema context without first manually changing the sql.
As a temporary fix so we can handle new customers in production, we are 
using a regex search/replace for public. in the pg_dump output, but 
clearly this is not a reliable solution for a generic gem such as Apartment.


Is there a different way this gem could now be achieving this?
Without a flag on pg_dump to force the old format, or a CREATE SCHEMA 
 WITH TEMPLATE  kind of statement, we can't 
see any alternative more reliable than the regex search/replace.


Wild idea:

1) Roll back to:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;h=c987089c48afba99df0c3c2dc45fd69238b52705;hb=2840d201c6a62beb86d9671a66eeec56183d261b

2) Use that to build pre-10.3 version of pg_dump and use that to dump 
your schema. Basically long way to to force old format. Is fragile 
though as that version will lose contact with changes.



Less wild idea and previously suggested upstream.

1) Check your schema objects into scripts that are checked into version 
control w/o schema qualifications.


2) Use scripts to populate new schema.



The open issue on the Apartment gem for 
context: https://github.com/influitive/apartment/issues/532


Thanks in advance.



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

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson > wrote:


On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:

On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson > wrote:

v8.4.12

This is *very* old version, not supported by the community for many
years. Check https://www.postgresql.org/ to seecurrentlysupported
versions.
You need to upgrade it.


Don't even think I'm in control of when -- or even if -- the customer
decides to upgrade.

That being the case, do you have an answer to the question?


Those queries from wiki for table and index bloat estimation are for 
estimation only. In many cases they show very wrong results. Better (yet 
not ideal) approach is using pgstattuple extension (though I'm not sure it 
existed back in 2009).


Can you provide table and index definition and, if you can, some sample data?


Sadly, no sample data.  (It's all PCI controlled.)

Index idx_item_mapping_rp7_y2016m03itemmapping_custom_userfield_801 has 40% 
bloat.


Thanks.

--
Angular momentum makes the world go 'round.
TAPd=# \d tms.item_mapping_rp7_y2013m03
   Table 
"tms.item_mapping_rp7_y2013m03"
 Column |Type | 
Modifiers  
+-+
 item_mapping_id| integer | not null default 
nextval('tms.item_mapping_item_mapping_id_seq'::regclass)
 item_seq_no| numeric(10,0)   | 
 account_no | character varying(255)  | 
 amount | numeric(21,2)   | 
 gross_amount   | numeric(21,2)   | 
 net_amount | numeric(21,2)   | 
 amount_3   | numeric(21,2)   | 
 custom_userfield_401   | character varying(255)  | 
 custom_userfield_402   | character varying(255)  | 
 custom_userfield_403   | character varying(255)  | 
 custom_userfield_404   | character varying(255)  | 
 custom_userfield_405   | character varying(255)  | 
 custom_userfield_416   | character varying(16)   | 
 custom_userfield_417   | character varying(16)   | 
 custom_userfield_418   | character varying(16)   | 
 custom_userfield_419   | character varying(16)   | 
 custom_userfield_420   | numeric(10,0)   | 
 custom_userfield_421   | numeric(10,0)   | 
 custom_userfield_422   | numeric(10,0)   | 
 custom_userfield_423   | numeric(21,2)   | 
 custom_userfield_424   | numeric(21,2)   | 
 custom_userfield_425   | numeric(21,2)   | 
 custom_userfield_426   | numeric(10,0)   | 
 custom_userfield_431   | character varying(16)   | 
 custom_userfield_432   | character varying(16)   | 
 custom_userfield_433   | character varying(16)   | 
 custom_userfield_434   | character varying(16)   | 
 custom_userfield_436   | character varying(16)   | 
 custom_userfield_437   | numeric(10,0)   | 
 custom_userfield_438   | numeric(10,0)   | 
 custom_userfield_439   | numeric(10,0)   | 
 custom_userfield_440   | numeric(10,0)   | 
 custom_userfield_441   | numeric(10,0)   | 
 custom_userfield_442   | numeric(10,0)   | 
 custom_userfield_443   | numeric(10,0)   | 
 custom_userfield_444   | character varying(1)| 
 custom_userfield_445   | character varying(1)| 
 custom_userfield_446   | character varying(1)| 
 custom_userfield_447   | character varying(1)| 
 custom_userfield_448   | character varying(1)| 
 custom_userfield_449   | character varying(1)| 
 custom_userfield_450   | character varying(1)| 
 custom_userfield_451   | character varying(1)| 
 custom_userfield_452   | character varying(1)| 
 custom_userfield_453   | numeric(21,2)   | 
 custom_userfield_454   | numeric(21,2)   | 
 custom_userfield_455   | numeric(21,2)   | 
 custom_userfield_456   | numeric(21,2)   | 
 custom_userfield_457   | numeric(21,2)   | 
 custom_userfield_458   | numeric(21,2)   | 
 custom_userfield_459   | numeric(21,2)   | 
 custom_userfield_460   | numeric(21,2)   | 
 cds_invoice_seq_number | numeric(10,0)   | 
 created_on | timestamp without time zone | not null
 created_by | integer | 
 modified_on| timestamp without time zone | 
 modified_by| integer | 
 type_id| numeric(8,0)

Re: Reindex doesn't eliminate bloat

[Nikolay Samokhvalov]

On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson  wrote:

> On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
>
> On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson 
> wrote:
>
>> v8.4.12
>>
>
> This is *very* old version, not supported by the community for many years.
> Check https://www.postgresql.org/ to see currently supported versions.
> You need to upgrade it.
>
>
> Don't even think I'm in control of when -- or even if -- the customer
> decides to upgrade.
>
> That being the case, do you have an answer to the question?
>

Those queries from wiki for table and index bloat estimation are for
estimation only. In many cases they show very wrong results. Better (yet
not ideal) approach is using pgstattuple extension (though I'm not sure it
existed back in 2009).

Can you provide table and index definition and, if you can, some sample
data?

Programmatically duplicating a schema

[matt . figg]

Hi all,
What is a reliable way to programmatically & generically populate an
empty schema with all the objects in the public schema as a template?
We are using the multi tenancy ruby gem Apartment (
https://github.com/influitive/apartment ), which was recently broken
by the changes made to pg_dump to address CVE-2018-1058
https://nvd.nist.gov/vuln/detail/CVE-2018-1058
Apartment attempts to duplicate the public schema whenever creating a
new schema by running:
pg_dump -s -x -0 -n public
to get the SQL statements needed to recreate the public schema & then
executes the pg_dump's sql output after creating & switching to the
new schema ( via set search_path to ; )
After the fix to CVE-2018-1058, all table references in pg_dump's
output (including within SQL of stored procedures) are prefixed by the
public. schema, which means you cannot just reuse this output in a
different schema context without first manually changing the sql.As a
temporary fix so we can handle new customers in production, we are
using a regex search/replace for public. in the pg_dump output, but
clearly this is not a reliable solution for a generic gem such as
Apartment.
Is there a different way this gem could now be achieving this?Without
a flag on pg_dump to force the old format, or a CREATE SCHEMA  WITH
TEMPLATE  kind of statement, we can't see any alternative more
reliable than the regex search/replace. 
The open issue on the Apartment gem for
context: https://github.com/influitive/apartment/issues/532
Thanks in advance.

Sv: Re: Logical decoding on standby

[Andreas Joseph Krogh]

På tirsdag 13. mars 2018 kl. 01:08:03, skrev Andreas Kretschmer <
andr...@a-kretschmer.de >:
On 13 March 2018 00:58:27 CET, Andreas Kretschmer  
wrote:
 >On 12 March 2018 21:18:22 CET, Andreas Joseph Krogh
 > wrote:
 >>Anybody knows if $subject will make it into v11?
 >> 
 >>--
 >>Andreas Joseph Krogh
 >
 >Why do you think you needs this?
 >
 >Regards, Andreas

 Let me explain my question. One of the key aspects of logical replication is, 
that you can define what to replicate. That wouldn't work in this way, that's 
why i'm asking.

 Regards, Andreas
 
I have streaming-replication of a cluster containing many databases to a 
standby-server. I need a reporting-server which only needs a subset of some 
(large) tables of one database and am planning to use the built-in logical 
replication for that. It is my understanding that logical replication will 
cause more wal-traffic so I'm trying to offload wal-traffic from the primary. I 
thought using logical replication from the standby would help with that but 
realized it's not supported in v10. Im I wrong in planning this way?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: Logical decoding on standby

[David G. Johnston]

On Mon, Mar 12, 2018 at 5:08 PM, Andreas Kretschmer  wrote:

> Let me explain my question. One of the key aspects of logical replication
> is, that you can define what to replicate. That wouldn't work in this way,
> that's why i'm asking.


One of the key aspects of "standby" is that it is ready to be a drop-in
replacement for the "active" server, and today our logical replication
facility is not capable of ensuring that property and won't be for v11 that
I can tell.  I'm not certain that said capability is even a goal at present.

IOW, why do you need a "standby" that isn't a drop-in replacement for a
primary - i.e., can have a filter on what replicated data it accepts?

David J.

Re: Logical decoding on standby

[Andreas Kretschmer]

On 12 March 2018 21:18:22 CET, Andreas Joseph Krogh  wrote:
>Anybody knows if $subject will make it into v11?
> 
>--
>Andreas Joseph Krogh

Why do you think you needs this? 

Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/12/2018 05:55 PM, Adrian Klaver wrote:

On 03/12/2018 03:05 PM, Ron Johnson wrote:

v8.4.12

According to this (https://pastebin.com/TJB32n5M) query, which I thought 
I got from https://wiki.postgresql.org/wiki/Index_Maintenance, a list of 
indexes and their bloat is generated.


After reindexing a table with a large amount of reported bloat (column 
bloat_pct says 29%), re-running the query shows no change in the amount 


First I am not seeing a column bloat_pct in the query you linked to, so 
are you sure that is the actual query you used?


Sorry.  bloat_pct is renamed bloat_ratio.



of bloat.  This is a historical table, and VACUUM VERBOSE shows that 
there's nothing to free up.


Is this something that I must live with, or am I misinterpreting the query?


Honestly I have not worked my way in depth through the query you show, 
though I did notice it uses pg_stats. What happens if run ANALYZE 
(https://www.postgresql.org/docs/8.4/static/sql-analyze.html) to update 
the stats?



I did ANALYZE VERBOSE on the underlying table.  No change.


--
Angular momentum makes the world go 'round.

Re: Reindex doesn't eliminate bloat

[Adrian Klaver]

On 03/12/2018 03:05 PM, Ron Johnson wrote:

v8.4.12

According to this (https://pastebin.com/TJB32n5M) query, which I thought 
I got from https://wiki.postgresql.org/wiki/Index_Maintenance, a list of 
indexes and their bloat is generated.


After reindexing a table with a large amount of reported bloat (column 
bloat_pct says 29%), re-running the query shows no change in the amount 


First I am not seeing a column bloat_pct in the query you linked to, so 
are you sure that is the actual query you used?


of bloat.  This is a historical table, and VACUUM VERBOSE shows that 
there's nothing to free up.


Is this something that I must live with, or am I misinterpreting the query?


Honestly I have not worked my way in depth through the query you show, 
though I did notice it uses pg_stats. What happens if run ANALYZE 
(https://www.postgresql.org/docs/8.4/static/sql-analyze.html) to update 
the stats?




Thanks,




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

Re: query_to_xml() returns invalid XML when query returns no rows

[Peter Eisentraut]

On 3/12/18 08:18, Thomas Kellerer wrote:
> I am not sure if this qualifies as a bug: 
> 
> query_to_xml() returns an empty XML document when the query returns no rows, 
> e.g:
> 
>select query_to_xml('select 42 where false', false, true, '');
> 
> The problem with this is, that if the resulting XML is then fed into e.g. the 
> xpath() function, that function fails because the "empty" document is an 
> invalid XML:

That's because you have the tableforest argument set to true.  If you
want a proper XML document, then you should write

select query_to_xml('select 42 where false', false, false, '');

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

Re: Reindex doesn't eliminate bloat

[Ron Johnson]

On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson > wrote:


v8.4.12

This is *very* old version, not supported by the community for many years. 
Check https://www.postgresql.org/ to seecurrentlysupported versions.

You need to upgrade it.


Don't even think I'm in control of when -- or even if -- the customer 
decides to upgrade.


That being the case, do you have an answer to the question?


--
Angular momentum makes the world go 'round.

Re: Reindex doesn't eliminate bloat

[Nikolay Samokhvalov]

On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson  wrote:

> v8.4.12
>

This is *very* old version, not supported by the community for many years.
Check https://www.postgresql.org/ to see currently supported versions.
You need to upgrade it.

Reindex doesn't eliminate bloat

[Ron Johnson]

v8.4.12

According to this (https://pastebin.com/TJB32n5M) query, which I thought I 
got from https://wiki.postgresql.org/wiki/Index_Maintenance, a list of 
indexes and their bloat is generated.


After reindexing a table with a large amount of reported bloat (column 
bloat_pct says 29%), re-running the query shows no change in the amount of 
bloat.  This is a historical table, and VACUUM VERBOSE shows that there's 
nothing to free up.


Is this something that I must live with, or am I misinterpreting the query?

Thanks,

--
Angular momentum makes the world go 'round.

Logical decoding on standby

[Andreas Joseph Krogh]

Anybody knows if $subject will make it into v11?
 
--
Andreas Joseph Krogh

pglogical lag due to replication timeout

[greigwise]

Hello all.

I've recently configured logical replication on postgres 9.6.5 using
pglogical 2.1.1.

The problem I'm seeing is pretty constant stream of these errors on the
service side in the postgres logs:

LOG:  terminating walsender process due to replication timeout

After so many of these, I can see on the replication target side, that
things are not being updated.  Eventually, the source server seems to retry
and then all the updates go through, but it seems like there's more lag than
there really should be due to all these "replication timeouts".  

I'm not seeing anything on the network side, it's really a pretty fast
connection between the 2 servers, I can't see that being the issue.  Plus I
have the wal_sender_timeout set to 600 (10 minutes) which seems like it
should be way overkill.

Any advice on this?

Thanks,
Greig Wise



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: psql in a bash function

[Eric Raskin]

Yes, you need double quotes around $@, as in "$@".

https://www.gnu.org/software/bash/manual/html_node/Special-Parameters.html



Sent from my Verizon, Samsung Galaxy smartphone


 Original message 
From: Ron Johnson  
Date: 3/12/18 2:15 PM (GMT-05:00) 
To: pgsql-general  
Subject: psql in a bash function 



 Hi,
 
 Because I need to log into many servers, I created functions as keyboard 
shortcuts (not aliases, since I will want to embed these shortcuts in other 
functions).
 
 psqlxyz ()
 {
     echo "P1=$1";
     echo "P2=$2";
     psql -U postgres -h XYZ $@
 }
 
 This is the (simple, test) command that I want to run, which works when run 
explicitly using psql, but not my function.  Any ideas why the function isn't 
properly passing the "-c" and '"select ..."' to psql?
 
 $ psql -U postgres -h XYZ -c "select oid, datname from pg_database;"
    oid   |    datname    
 -+
    1 | template1
    11563 | template0
    11564 | postgres
    16404 | test1
  3039800 | ABCD
   319011 | EFGH
   649861 | IJKL
 (7 rows)
 
 $ psqldba -c '"select oid, datname from pg_database;"'
 P1=-c
 P2="select oid, datname from pg_database;"
 psql: warning: extra command-line argument "datname" ignored
 psql: warning: extra command-line argument "from" ignored
 psql: warning: extra command-line argument "pg_database;"" ignored
 psql: FATAL:  database "oid," does not exist
 
 Thanks
 
 
-- 
 Angular momentum makes the world go 'round.

Re: psql in a bash function

[Melvin Davidson]

On Mon, Mar 12, 2018 at 2:14 PM, Ron Johnson  wrote:

> Hi,
>
> Because I need to log into many servers, I created functions as keyboard
> shortcuts (not aliases, since I will want to embed these shortcuts in other
> functions).
>
> psqlxyz ()
> {
> echo "P1=$1";
> echo "P2=$2";
> psql -U postgres -h XYZ $@
> }
>
> This is the (simple, test) command that I want to run, which works when
> run explicitly using psql, but not my function.  Any ideas why the function
> isn't properly passing the "-c" and '"select ..."' to psql?
>
> $ psql -U postgres -h XYZ -c "select oid, datname from pg_database;"
>oid   |datname
> -+
>1 | template1
>11563 | template0
>11564 | postgres
>16404 | test1
>  3039800 | ABCD
>   319011 | EFGH
>   649861 | IJKL
> (7 rows)
>
> $ psqldba -c *'*"select oid, datname from pg_database;"*'*
> P1=*-c*
> P2=*"select oid, datname from pg_database;"*
> psql: warning: extra command-line argument "datname" ignored
> psql: warning: extra command-line argument "from" ignored
> psql: warning: extra command-line argument "pg_database;"" ignored
> psql: FATAL:  database "oid," does not exist
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>

Ron,

Here is a model that works in any LINUX environment. You can tweak for your
commands:
Please include your PostgreSQL version and O/S in future submissions to
this list.

#!/bin/bash
# Reports sizes for all or selected database

PORT=""
USER=""
DBNAME="%"
usage() {
echo "Usage: $0 [-d  -U  -p ]"
exit 1
}

while getopts "d:p:uU:" OPT;
do case "${OPT}" in
  d) DBNAME=$OPTARG
 ;;
  p) PORT="-p $OPTARG"
 ;;
  U) USER="-U $OPTARG"
 ;;
  u) usage
 ;;
[?]) usage
   esac;
done

if [ "$DBNAME" = "" ]
  then
usage
exit 1
fi


psql $PORT $USER postgres <<_EOF_
SELECT txid_current() AS txid_current;
SELECT datname,
   rolname as owner,
   pg_size_pretty(pg_database_size(datname) )as size_pretty,
   pg_database_size(datname) as size,
   (SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
  FROM pg_database)  AS total,
   (pg_database_size(datname) / (SELECT SUM(
pg_database_size(datname))
   FROM pg_database) ) *
100::numeric(6,3) AS pct
  FROM pg_database d
  JOIN pg_authid a ON a.oid = datdba
  WHERE datname LIKE '%$DBNAME%'
ORDER BY datname;
_EOF_


-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

psql in a bash function

[Ron Johnson]

Hi,

Because I need to log into many servers, I created functions as keyboard 
shortcuts (not aliases, since I will want to embed these shortcuts in other 
functions).


psqlxyz ()
{
    echo "P1=$1";
    echo "P2=$2";
    psql -U postgres -h XYZ $@
}

This is the (simple, test) command that I want to run, which works when run 
explicitly using psql, but not my function.  Any ideas why the function 
isn't properly passing the "-c" and '"select ..."' to psql?


$ psql -U postgres -h XYZ -c "select oid, datname from pg_database;"
   oid   |    datname
-+
   1 | template1
   11563 | template0
   11564 | postgres
   16404 | test1
 3039800 | ABCD
  319011 | EFGH
  649861 | IJKL
(7 rows)

$ psqldba -c *'*"select oid, datname from pg_database;"*'*
P1=*-c*
P2=*"select oid, datname from pg_database;"*
psql: warning: extra command-line argument "datname" ignored
psql: warning: extra command-line argument "from" ignored
psql: warning: extra command-line argument "pg_database;"" ignored
psql: FATAL:  database "oid," does not exist

Thanks

--
Angular momentum makes the world go 'round.

can someone send a design document of "parallel work" to me?

[lin]

Hi, all,
 I hava read the code for "parallel work" of PostgreSQL 9.6,  I hope 
someone can give me a  design document of "parallel work".


Thanks,
Lin

query_to_xml() returns invalid XML when query returns no rows

[Thomas Kellerer]

I am not sure if this qualifies as a bug: 

query_to_xml() returns an empty XML document when the query returns no rows, 
e.g:

   select query_to_xml('select 42 where false', false, true, '');

The problem with this is, that if the resulting XML is then fed into e.g. the 
xpath() function, that function fails because the "empty" document is an 
invalid XML:

So the following query:

select xpath('/row/col/text()', query_to_xml('select 42 as col where 
false', false, true, ''));

fails with "ERROR:  could not parse XML document"

I would have expected query_to_xml() to return NULL if the query returns no 
rows, rather than an invalid XML document.

Note that IS DOCUMENT is false for the empty XML returned, so it can be trapped 
in a query. 
But if query_to_xml() is nested somehow (like the above) this is really 
complicated to apply and I find it a bit surprising that query_to_xml() returns 
invalid XML at all

Tested with 10.2 on Windows 10

Thomas