Re: [GENERAL] Postgres 9.6 Parallel Query

[Rafia Sabih]

On Thu, Apr 27, 2017 at 6:38 PM, basti  wrote:
> Hello,
>
> is there a recomment to set max_worker_processes and
> max_parallel_workers_per_gather per cpu core or thread?
>
This largely depends on what type of processing is done by parallel
workers, e.g. if the task is I/O bound then having workers equal to
h/w threads would be beneficial since less time will spent in CPU,
however if the task is CPU bound then workers more than cpu cores is
not likely to give much performance improvement as they'll be
contending for CPU cycles.

-- 
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.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]

[Igor Korot]

Hi, John et al,

On Wed, May 10, 2017 at 11:02 PM, John R Pierce  wrote:
> On 5/10/2017 7:45 PM, Igor Korot wrote:
>>
>> I found
>> this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
>> but now I need
>> to connect this with information_schema.columns.
>>
>> What is best way to do it?
>>
>> Or maybe that query I referenced is completely wrong?
>
>
>
> if you're using pg_catalog stuff there's little point in using the
> information_schema views, which exist for compatability with the SQL
> standard.
>
> information_schema.columns is a view, like...

Like I said, what I expect to see from the query is:

id | integer | | 5| 2 | 0 | P |
name | varchar | 50| 2 | | | | 

So I need the information about the field and whether the field is a
primary/foreign key or not.

And this is according to the schema.table.

Thank you.

>
> View definition:
>  SELECT current_database()::information_schema.sql_identifier AS
> table_catalog,
> nc.nspname::information_schema.sql_identifier AS table_schema,
> c.relname::information_schema.sql_identifier AS table_name,
> a.attname::information_schema.sql_identifier AS column_name,
> a.attnum::information_schema.cardinal_number AS ordinal_position,
> pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data AS
> column_default,
> CASE
> WHEN a.attnotnull OR t.typtype = 'd'::"char" AND t.typnotnull
> THEN 'NO'::text
> ELSE 'YES'::text
> END::information_schema.yes_or_no AS is_nullable,
> CASE
> WHEN t.typtype = 'd'::"char" THEN
> CASE
> WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN
> 'ARRAY'::text
> WHEN nbt.nspname = 'pg_catalog'::name THEN
> format_type(t.typbasetype, NULL::integer)
> ELSE 'USER-DEFINED'::text
> END
> ELSE
> CASE
> WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN
> 'ARRAY'::text
> WHEN nt.nspname = 'pg_catalog'::name THEN
> format_type(a.atttypid, NULL::integer)
> ELSE 'USER-DEFINED'::text
> END
> END::information_schema.character_data AS data_type,
> information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_numb
> er AS character_maximum_length,
> information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_nu
> mber AS character_octet_length,
> information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_nu
> mber AS numeric_precision,
> information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardi
> nal_number AS numeric_precision_radix,
> information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_number
>  AS numeric_scale,
> information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.cardinal_n
> umber AS datetime_precision,
> information_schema._pg_interval_type(information_schema._pg_truetypid(a.*,
> t.*), information_schema._pg_truetypmod(a.*,
> t.*))::information_schema.character_data
> AS interval_type,
> NULL::integer::information_schema.cardinal_number AS interval_precision,
> NULL::character varying::information_schema.sql_identifier AS
> character_set_catalog,
> NULL::character varying::information_schema.sql_identifier AS
> character_set_schema,
> NULL::character varying::information_schema.sql_identifier AS
> character_set_name,
> CASE
> WHEN nco.nspname IS NOT NULL THEN current_database()
> ELSE NULL::name
> END::information_schema.sql_identifier AS collation_catalog,
> nco.nspname::information_schema.sql_identifier AS collation_schema,
> co.collname::information_schema.sql_identifier AS collation_name,
> CASE
> WHEN t.typtype = 'd'::"char" THEN current_database()
> ELSE NULL::name
> END::information_schema.sql_identifier AS domain_catalog,
> CASE
> WHEN t.typtype = 'd'::"char" THEN nt.nspname
> ELSE NULL::name
> END::information_schema.sql_identifier AS domain_schema,
> CASE
> WHEN t.typtype = 'd'::"char" THEN t.typname
> ELSE NULL::name
> END::information_schema.sql_identifier AS domain_name,
> current_database()::information_schema.sql_identifier AS udt_catalog,
> COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS
> udt_schema,
> 

Re: [GENERAL]

[John R Pierce]

On 5/10/2017 7:45 PM, Igor Korot wrote:

I found this:https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
but now I need
to connect this with information_schema.columns.

What is best way to do it?

Or maybe that query I referenced is completely wrong?



if you're using pg_catalog stuff there's little point in using the 
information_schema views, which exist for compatability with the SQL 
standard.


information_schema.columns is a view, like...

View definition:
 SELECT current_database()::information_schema.sql_identifier AS 
table_catalog,

nc.nspname::information_schema.sql_identifier AS table_schema,
c.relname::information_schema.sql_identifier AS table_name,
a.attname::information_schema.sql_identifier AS column_name,
a.attnum::information_schema.cardinal_number AS ordinal_position,
pg_get_expr(ad.adbin, 
ad.adrelid)::information_schema.character_data AS column_default,

CASE
WHEN a.attnotnull OR t.typtype = 'd'::"char" AND 
t.typnotnull THEN 'NO'::text

ELSE 'YES'::text
END::information_schema.yes_or_no AS is_nullable,
CASE
WHEN t.typtype = 'd'::"char" THEN
CASE
WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN 
'ARRAY'::text
WHEN nbt.nspname = 'pg_catalog'::name THEN 
format_type(t.typbasetype, NULL::integer)

ELSE 'USER-DEFINED'::text
END
ELSE
CASE
WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN 
'ARRAY'::text
WHEN nt.nspname = 'pg_catalog'::name THEN 
format_type(a.atttypid, NULL::integer)

ELSE 'USER-DEFINED'::text
END
END::information_schema.character_data AS data_type,
information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, 
t.*), information_schema._pg_truetypmod(a.*, 
t.*))::information_schema.cardinal_numb

er AS character_maximum_length,
information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, 
t.*), information_schema._pg_truetypmod(a.*, 
t.*))::information_schema.cardinal_nu

mber AS character_octet_length,
information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, 
t.*), information_schema._pg_truetypmod(a.*, 
t.*))::information_schema.cardinal_nu

mber AS numeric_precision,
information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, 
t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardi

nal_number AS numeric_precision_radix,
information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, 
t.*), information_schema._pg_truetypmod(a.*, 
t.*))::information_schema.cardinal_number

 AS numeric_scale,
information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, 
t.*), information_schema._pg_truetypmod(a.*, 
t.*))::information_schema.cardinal_n

umber AS datetime_precision,
information_schema._pg_interval_type(information_schema._pg_truetypid(a.*, 
t.*), information_schema._pg_truetypmod(a.*, 
t.*))::information_schema.character_data

AS interval_type,
NULL::integer::information_schema.cardinal_number AS 
interval_precision,
NULL::character varying::information_schema.sql_identifier AS 
character_set_catalog,
NULL::character varying::information_schema.sql_identifier AS 
character_set_schema,
NULL::character varying::information_schema.sql_identifier AS 
character_set_name,

CASE
WHEN nco.nspname IS NOT NULL THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS collation_catalog,
nco.nspname::information_schema.sql_identifier AS collation_schema,
co.collname::information_schema.sql_identifier AS collation_name,
CASE
WHEN t.typtype = 'd'::"char" THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS domain_catalog,
CASE
WHEN t.typtype = 'd'::"char" THEN nt.nspname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_schema,
CASE
WHEN t.typtype = 'd'::"char" THEN t.typname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_name,
current_database()::information_schema.sql_identifier AS udt_catalog,
COALESCE(nbt.nspname, 
nt.nspname)::information_schema.sql_identifier AS udt_schema,
COALESCE(bt.typname, t.typname)::information_schema.sql_identifier 
AS udt_name,
NULL::character varying::information_schema.sql_identifier AS 
scope_catalog,
NULL::character varying::information_schema.sql_identifier AS 
scope_schema,
NULL::character varying::information_schema.sql_identifier AS 
scope_name,
NULL::integer::information_schema.cardinal_number AS 
maximum_cardinality,

a.attnum::information_schema.sql_identifier AS dtd_identifier,
'NO'::character varying::information_schema.yes_or_no AS 
is_self_referencing,

   

Re: [GENERAL]

[Igor Korot]

Hi, guys,

On Sun, May 7, 2017 at 1:40 PM, Igor Korot  wrote:
> David,
>
> On Sun, May 7, 2017 at 8:57 AM, David Rowley
>  wrote:
>> On 8 May 2017 at 00:42, Igor Korot  wrote:
>>> Basically what I'd like to see is the definition of each column and
>>> whether this column is
>>> part of primary/foreign key or not.
>>
>> information_schema.table_constraints is of no use to you then. There
>> are no details about which column(s) the constraint applies to.
>>
>> Likely you'll want to look at pg_constraint for contype in('p','f')
>> and unnest(conkey) and join that to information_schema.columns. You
>> may also need to think about pg_constraint.confkey, depending on if
>> you want to know if the column is referencing or referenced in a
>> foreign key constraint.
>
> I checked pg_constraint view, but I didn't see anything to join to.
> I do see a table name, but no schema or column name.
>
> Any idea on the query syntax?

I found this: https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns,
but now I need
to connect this with information_schema.columns.

What is best way to do it?

Or maybe that query I referenced is completely wrong?

Please help.

Thank you.

>
> Thank you.
>
>>
>>
>>
>> --
>>  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] Querying a policy

[Stephen Frost]

Jeff,

* Jean-Francois Bernier (jean.francois.bern...@boreal-is.com) wrote:
> We are evaluating migrating our software RLS to Postgres by using policies.

Neat!

> Having a "FOR UPDATE POLICY" on a table, I was wondering if there is a way to 
> know, before trying an Update and getting an error, if the current row can be 
> updated ?

Unfortunately, not as easily as it seems you would like, currently,
though perhaps we could change that..

> The goal is to show or hide the edit button in my software forms or lists.

Right, makes sense.

> I know that this query can return the CHECK condition of my POLICY:
> SELECT pg_get_expr(polwithcheck, polrelid, true) FROM pg_policy;

Yup.

> But is there a simpler way to get the ids the current user can read and the 
> ones that he can update?

Well, have you considered using the expression from the above query to
add a column to your SELECT query that results in a column that
indicates if the row is updatable or not..?  That is, construct your
query by doing:

SELECT pg_get_expr(polwithcheck, polrelid, true) FROM pg_policy ; into a
variable in your application, then:

"SELECT * , " . variable . " from ..."

The same could be done through a view, potentially, or perhaps with a
plpgsql function, but I'm guessing that negates some of the
"cleanliness" that you get with RLS and base tables.

I certainly like the idea in general.  I will caution that, to be fair,
just because the WITH CHECK clause says a given row can be modified at
SELECT time doesn't guarantee that the same row will be updatable in
some later transaction, as it depends on just what the policy is.

In any case, very cool to hear about people working to use RLS!  Would
love to chat further about your use-case and see what we can do to make
RLS easier to use.

Thanks!

Stephen


signature.asc
Description: Digital signature

[GENERAL] Querying a policy

[Jean-Francois Bernier]

Hi all!

We are evaluating migrating our software RLS to Postgres by using policies.

Having a "FOR UPDATE POLICY" on a table, I was wondering if there is a way to 
know, before trying an Update and getting an error, if the current row can be 
updated ?

The goal is to show or hide the edit button in my software forms or lists.

I know that this query can return the CHECK condition of my POLICY:
SELECT pg_get_expr(polwithcheck, polrelid, true) FROM pg_policy;

But is there a simpler way to get the ids the current user can read and the 
ones that he can update?

Thank you!

Jeff

Re: [GENERAL] Python versus Other Languages using PostgreSQL

[John R Pierce]

On 5/10/2017 2:43 PM, Adrian Klaver wrote:

1) Uber
2)Yahoo
3) Instagram 


and, each of those giant businesses has their own entirely custom 
'platforms', so its not really fair to call them 'largest web platforms' 
as each of those custom platforms is in use at only one business.   
Sure, largest web SITES by traffic, usage.


--
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] Python versus Other Languages using PostgreSQL

[Adrian Klaver]

On 05/10/2017 12:46 PM, Paul Hughes wrote:

Adrian Klaver Wrote:


Many on this list(myself included) will want to know how you came to that 
conclusion and I am speaking as someone who uses Python, Django and Postgres.


I came to that conclusion when I saw a list of the top 15 websites
(based on traffic). On that list, *all* of the sites that used
PostgreSQL as their primary data store, also used Python as their
primary back-end language. Those three sites are/were Yahoo, Instagram
and Uber. 3 out of 3 is a pretty high statistical correlation when


What list was that?

I ask because:

1) Uber
Why Uber Engineering Switched from Postgres to MySQL
https://eng.uber.com/mysql-migration/

2)Yahoo
AFAIK Yahoo uses just the Postgresql SQL parser over their own database. 
Also Yahoo makes extensive use of JavaScript and other technologies 
beside Python.


3) Instagram
Probably still Python/Postgres based though now they are part of 
Facebook it will be interesting to see if that changes.




you're only talking about a sample of 15. Given that, my saying, "I
noticed that most of the largest web platforms that use PostgreSQL as
their primary database, also use Python as their primary back-end
language.", was a fair observation. An observation is just that (one
blind man feeling an elephant). Saying "I noticed that" is a *very*
different thing than making an assertion.





--
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] Python versus Other Languages using PostgreSQL

[Gavin Flower]

Hi Paul,

See comments at the end...

On 10/05/17 08:00, Paul Hughes wrote:
Thank you all for taking the time to answer my questions. I've been 
out of the programming world for a long time, so I am back to being a 
newbie.
Even if you stay in the game, technology changes - so one has to keep 
learning and adapting.


When I started mighty MainFrames roamed the planet - now a low end smart 
phone has over a thousand times more memory & processing power, than the 
first MainFrame (ICL 4/72) I ever programmed in COBOL .  Now I use Java 
& other newer languages on a Linux box with a processor chip with more 
cache than any MainFrame's main memory that I ever programmed.


I was told this is the place for newcomers to ask questions. I 
apologize if my questions did not contain the necessary sophistication 
or nuance for some to answer.


I don't think that is too much of a problem, haven't seen anyone tell 
you to RTFM!!!


[...]

It is normal on this list not to top post, but rather to add comments at 
the end (so people can see the context) - though interspersed comments 
in the body of the text is okay when appropriate!



Cheers,
Gavin




--
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] Logical decoding CPU-bound w/ large number of tables

[Andres Freund]

Hi Tom, Mathieu,

On 2017-05-10 17:02:11 -0400, Tom Lane wrote:
> Mathieu Fenniak  writes:
> > Andres, it seems like the problem is independent of having large data
> > manipulations mixed with schema changes.  The below test case demonstrates
> > it with just schema changes.
> 
> > Tom, I've tested your patch, and it seems to have a positive impact for
> > sure.  I've documented a test case to reproduce this issue (below), and
> > your avoid-search-in-catcache-invalidate.patch reduces the test case time
> > from 63 seconds per run to 27 seconds per run.
> 
> Thanks for the test case.

Indeed!


> +   59.28%59.05%150349  postmaster   postgres 
>  [.] hash_seq_search
> +8.40% 8.36% 21250  postmaster   postgres 
>  [.] CallSyscacheCallbacks
> +6.37% 6.34% 16115  postmaster   postgres 
>  [.] LocalExecuteInvalidationMessage
> +5.69% 5.67% 14418  postmaster   postgres 
>  [.] CatCacheInvalidate
> +3.14% 3.12%  7942  postmaster   postgres 
>  [.] SysCacheInvalidate
> +1.72% 1.71%  4354  postmaster   postgres 
>  [.] ReorderBufferCommit
> +1.37% 1.33%  3512  postmaster   postgres 
>  [.] hash_search_with_hash_value
> +1.15% 1.15%  2913  postmaster   postgres 
>  [.] InvalidateCatalogSnapshot
> 
> I looked at the hash_seq_search time a bit more, and realized that
> actually there's a pretty easy fix for that, which is to reduce
> the initial size of RelfilenodeMapHash from 1024 entries to 64.

Heh, that's not a bad idea. Given the table resizes automatically, there
seems little reason not to go there.

> You could quibble about where to set that exactly, but 1024 is
> just way too many --- in your example, there are never more than
> 5 entries in the hash, despite the presence of 1 tables in
> the database.

That's likely because there's no DML.


> We're at a point of diminishing returns here; I think any further
> improvement would require reducing the number of invalidation calls,
> as Andres was speculating about upthread.  Still, this shows that
> it doesn't take very much work to get a 10X improvement in the
> overhead associated with inval activities.  We've never seen this
> overhead stick out quite this much before, and maybe logical replication
> will always be an atypical workload, but I think this may be worth
> committing even if Andres does managed to cut the number of calls.

I've seen these callsites prominently in profiles not using logical
decoding.  Never quite as big as here, but still.  Temp table heavy
workloads IIRC are one of the easy way to trigger it.


> It would be interesting to see how much these patches help for your real
> use-case, as opposed to this toy example.  Assuming that the results are
> positive, I would advocate for back-patching these changes as far as 9.4
> where logical decoding came in.

+1.


> BTW, I also noticed that we're getting scarily close to exceeding
> MAX_SYSCACHE_CALLBACKS.  There are 30-some calls to
> CacheRegisterSyscacheCallback in our code, and while I think not all of
> them can be reached in a single process, we demonstrably get as high as 21
> registered callbacks in some regression test runs.  That's not leaving a
> lot of daylight for add-on modules.  The second patch attached includes
> increasing MAX_SYSCACHE_CALLBACKS from 32 to 64.  I think we'd be well
> advised to apply and back-patch that, even if we don't use the rest of
> the patch.

+1 - I'd advocate for doing so all the way.

Greetings,

Andres Freund


-- 
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] Logical decoding CPU-bound w/ large number of tables

[Tom Lane]

Mathieu Fenniak  writes:
> Andres, it seems like the problem is independent of having large data
> manipulations mixed with schema changes.  The below test case demonstrates
> it with just schema changes.

> Tom, I've tested your patch, and it seems to have a positive impact for
> sure.  I've documented a test case to reproduce this issue (below), and
> your avoid-search-in-catcache-invalidate.patch reduces the test case time
> from 63 seconds per run to 27 seconds per run.

Thanks for the test case.  Your machine is apparently a bit faster than
mine, or else Assert overhead is really hurting in this case, because
on a debug-enabled build of HEAD I see the replication time as about
115 seconds, with the principal culprits according to "perf" being

+   53.11%52.96%253596  postmaster   postgres   
   [.] CatalogCacheIdInvalidate
+   31.15%31.06%148779  postmaster   postgres   
   [.] hash_seq_search
+5.70% 5.69% 27223  postmaster   postgres   
   [.] CallSyscacheCallbacks
+3.36% 3.35% 16057  postmaster   postgres   
   [.] LocalExecuteInvalidationMessage
+1.32% 1.32%  6306  postmaster   postgres   
   [.] ReorderBufferCommit

I concur that the previously-posted patch to avoid searching in
CatalogCacheIdInvalidate makes for about a 2X speedup; for me,
that brings the test case down to 55 seconds, and now the top
hits are

+   59.28%59.05%150349  postmaster   postgres   
   [.] hash_seq_search
+8.40% 8.36% 21250  postmaster   postgres   
   [.] CallSyscacheCallbacks
+6.37% 6.34% 16115  postmaster   postgres   
   [.] LocalExecuteInvalidationMessage
+5.69% 5.67% 14418  postmaster   postgres   
   [.] CatCacheInvalidate
+3.14% 3.12%  7942  postmaster   postgres   
   [.] SysCacheInvalidate
+1.72% 1.71%  4354  postmaster   postgres   
   [.] ReorderBufferCommit
+1.37% 1.33%  3512  postmaster   postgres   
   [.] hash_search_with_hash_value
+1.15% 1.15%  2913  postmaster   postgres   
   [.] InvalidateCatalogSnapshot

I looked at the hash_seq_search time a bit more, and realized that
actually there's a pretty easy fix for that, which is to reduce
the initial size of RelfilenodeMapHash from 1024 entries to 64.
You could quibble about where to set that exactly, but 1024 is
just way too many --- in your example, there are never more than
5 entries in the hash, despite the presence of 1 tables in
the database.  I also checked it while running the regression tests,
and while a few transactions get up to several hundred entries,
they're mostly less than 50.  So rather than build a second index
structure for that hashtable, I propose we just do what's in the first
attached patch.

With that, I was down to about 21s run time, and now perf says

+   23.17%23.08% 21254  postmaster   postgres   
 [.] CallSyscacheCallbacks
+   16.97%16.91% 15579  postmaster   postgres   
 [.] LocalExecuteInvalidationMessage
+   16.10%16.03% 14766  postmaster   postgres   
 [.] CatCacheInvalidate
+   12.45%12.42% 11474  postmaster   postgres   
 [.] hash_seq_search
+8.66% 8.64%  7959  postmaster   postgres   
 [.] SysCacheInvalidate
+4.72% 4.70%  4331  postmaster   postgres   
 [.] ReorderBufferCommit
+3.16% 3.14%  2894  postmaster   postgres   
 [.] InvalidateCatalogSnapshot
+2.56% 2.50%  2344  postmaster   postgres   
 [.] hash_search_with_hash_value
+1.27% 1.27%  1169  postmaster   postgres   
 [.] RelfilenodeMapInvalidateCallbac

Looking at CallSyscacheCallbacks, it's got exactly the same
disease of linearly scanning a list to find relevant entries,
when we could easily pre-index the list.  The second patch
attached gets me to 13s, with profile

+   22.78%22.70% 14568  postmaster   postgres   
  [.] CatCacheInvalidate
+   17.75%17.69% 11355  postmaster   postgres   
  [.] 

Re: [GENERAL] Python versus Other Languages using PostgreSQL

[Paul Hughes]

Adrian Klaver Wrote:

>>Many on this list(myself included) will want to know how you came to that
conclusion and I am speaking as someone who uses Python, Django and
Postgres.

I came to that conclusion when I saw a list of the top 15 websites (based
on traffic). On that list, *all* of the sites that used PostgreSQL as their
primary data store, also used Python as their primary back-end language.
Those three sites are/were Yahoo, Instagram and Uber. 3 out of 3 is a
pretty high statistical correlation when you're only talking about a sample
of 15. Given that, my saying, "I noticed that most of the largest web
platforms that use PostgreSQL as their primary database, also use Python as
their primary back-end language.", was a fair observation. An observation
is just that (one blind man feeling an elephant). Saying "I noticed that"
is a *very* different thing than making an assertion.



Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Wed, May 10, 2017 at 7:37 AM, Adrian Klaver 
wrote:

> On 05/09/2017 01:00 PM, Paul Hughes wrote:
>
>> Thank you all for taking the time to answer my questions. I've been out
>> of the programming world for a long time, so I am back to being a newbie. I
>> was told this is the place for newcomers to ask questions. I apologize if
>> my questions did not contain the necessary sophistication or nuance for
>> some to answer.
>>
>
> It has more to do with making unsubstantiated assertions, namely:
>
> "I noticed that most of the largest web platforms that use PostgreSQL as
> their primary database, also use Python as their primary back-end language.
> ..."
>
> Many on this list(myself included) will want to know how you came to that
> conclusion and I am speaking as someone who uses Python, Django and
> Postgres.
>
>
>
>> George, your answer makes the most sense. Thank you!  You said:
>>
>> Most cloud servers come preconfigured with some variation of either

>>> the "LAMP" or "LAPP" stack: i.e. Linux, Apache, MySQL or Postgresql,
>> PHP or Python.
>>
>> There needs to be a compelling *application* reason to install

>>> something else: a JVM (or Apache-Tomcat vs regular Apache),
>> Ruby/Rails, Groovy/Grails, etc.
>>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
Paul Hughes
Executive Director
Vivation International
1-800-514-8483
Skype: Vivation2
p...@vivation.com
http://www.vivation.com/

Re: [GENERAL] relation create time

[Melvin Davidson]

On Wed, May 10, 2017 at 12:31 PM, Peter Eisentraut <
peter.eisentr...@2ndquadrant.com> wrote:

> On 5/10/17 12:05, Hu, Patricia wrote:
> > I am trying to find out when a table was created in postgresql. Thought
> > it would be easy (coming from Oracle world), but haven’t had any luck,
> > especially since we are on RDS and can’t peek at the timestamp on the
> > file system. Is this information stored anywhere in the catalog?
>
> It is not.
>
> > Or I
> > need to store it myself? Is there any plan to add such meta data
> > information to the catalog as a feature? Thanks a lot!
>
> You could write an event trigger to record it.
>
> --
> Peter Eisentraut  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
>



*One option would be to set log_statement = 'ddl' , and make sure
log_line_prefix includes time, then you can review the log for creation
time.*



*Also, I have been requesting that feature for a long time, so if you want
to add your support, vote for it
athttps://postgresql.uservoice.com/forums/21853-general?filter=top=2
*

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

Re: [GENERAL] relation create time

[Hu, Patricia]

Thanks for your prompt reply Peter. 

Sure I could write a trigger to capture and store it, but for such common 
functionalities seems to me it would be best to have it in the engine, vs. each 
application having to write its own trigger and reinvent the wheel. 

Is there any concerns on adding it as a feature, or is it just backlog? Has a 
feature request been filed for this?

Thanks,
Patricia 


-Original Message-
From: Peter Eisentraut [mailto:peter.eisentr...@2ndquadrant.com] 
Sent: Wednesday, May 10, 2017 12:32 PM
To: Hu, Patricia; pgsql-general@postgresql.org
Subject: [EXTERNAL] Re: [GENERAL] relation create time

On 5/10/17 12:05, Hu, Patricia wrote:
> I am trying to find out when a table was created in postgresql. 
> Thought it would be easy (coming from Oracle world), but haven't had 
> any luck, especially since we are on RDS and can't peek at the 
> timestamp on the file system. Is this information stored anywhere in the 
> catalog?

It is not.

> Or I
> need to store it myself? Is there any plan to add such meta data 
> information to the catalog as a feature? Thanks a lot!

You could write an event trigger to record it.

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

Confidentiality Notice::  This email, including attachments, may include 
non-public, proprietary, confidential or legally privileged information.  If 
you are not an intended recipient or an authorized agent of an intended 
recipient, you are hereby notified that any dissemination, distribution or 
copying of the information contained in or transmitted with this e-mail is 
unauthorized and strictly prohibited.  If you have received this email in 
error, please notify the sender by replying to this message and permanently 
delete this e-mail, its attachments, and any copies of it immediately.  You 
should not retain, copy or use this e-mail or any attachment for any purpose, 
nor disclose all or any part of the contents to any other person. Thank you.


-- 
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] character encoding of the postgres database

[Sandeep Gupta]

Thank you.

On Wed, May 10, 2017 at 12:54 PM, Adrian Klaver
 wrote:
> On 05/10/2017 08:48 AM, Sandeep Gupta wrote:
>>
>> Currently, the postgres database by has SQL_ASCII encoding.
>>
>> psql -p 5771 postgres -l
>> List of databases
>>Name|  Owner  | Encoding  | Collate | Ctype |  Access privileges
>> ---+-+---+-+---+-
>>  postgres  | sandeep | SQL_ASCII | C   | C |
>>  template0 | sandeep | SQL_ASCII | C   | C | =c/sandeep +
>>| |   | |   | sandeep=CTc/sandeep
>>  template1 | sandeep | SQL_ASCII | C   | C | =c/sandeep +
>>| |   | |   | sandeep=CTc/sandeep
>> (3 rows)
>>
>> Is it possible to start the postgres database with UTF-8 encoding, instead
>> of modifying it later.
>
>
> See Peter's post, just remember that SQL_ASCII is essentially no encoding so
> be prepared for issues. I would test before doing this on live data.
>
>
>>
>> Thanks
>> sandeep
>>
>>
>
>
> --
> 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] logging of application level user in audit trigger

[Rajesh Mallah]

Ok , i do see there is already the handling of application_name in the
trigger ,
this is quite sufficient for my current needs.

regds
mallah.

On Wed, May 10, 2017 at 10:58 PM, Rajesh Mallah 
wrote:

> Hi Scott / List ,
>
> Thanks for the response,
>
> the application_name  usage seems to be more natural as it is something
> inbuilt.
> the audit trigger repo seems to have got update only in past 2 years .
>
> are there other more active projects doing the same thing ?
> in my opinion (which may be flawed) pgaudit seems to be logging only with
> no possibility
> of knowing the old record.
>
> I think audit trail of tables is something that is frequently required ,
> it would be
> nice if some inbuilt (core) feature comes someday in postgresql for
> auditing
>
>
> regds
> mallah.
>
>
>
>
>
> On Wed, May 10, 2017 at 1:17 AM, Scott Mead  wrote:
>
>>
>>
>> On Tue, May 9, 2017 at 2:50 PM, Rajesh Mallah 
>> wrote:
>>
>>> Hi ,
>>>
>>> I am referring to   audit trigger as described in
>>>
>>> https://wiki.postgresql.org/wiki/Audit_trigger_91plus OR
>>> https://wiki.postgresql.org/wiki/Audit_trigger
>>>
>>> Although there are documented limitations for these systems , but
>>> I would like to mention and seek suggestion on a limitation that I feel
>>> is biggest .
>>>
>>>
>>> It is very a common design pattern in  web-applications that the same
>>> database
>>> user is shared for making database changes by different "logged in
>>> users" of the
>>> web application.
>>>
>>> I feel the core of audit is all about "who"  , "when" and "what" .
>>>
>>> In the current audit trigger the "who" is essentially the ROLE which is
>>> the actor of
>>> the trigger , but in most scenarios the user associated with the
>>> web-application session
>>> is the one that is seeked.
>>>
>>> In one of my past projects I passed the web-user to the trigger by
>>> setting a postgres
>>> custom variable during the database connection and reading it inside the
>>> trigger
>>> and storing it in the audit log table.
>>>
>>
>> This is a good method, and one of the best for just straight auditing.
>> The other trick I've seen is to use the 'application_name' field.
>> Developers would issue:
>>
>> SET application_name = "app_user:app_name';
>>
>> This can be read from pg_stat_activity.application_name.  I believe
>> you'd be able to read that in a procedure with 'show application_name';
>> and, you can see it live in pg_stat_activity as well.
>>
>> select application_name, count(*)
>>   FROM pg_stat_activity
>>  GROUP by application_name;
>>
>>   You'd be able to see each user/app pair and the number of sessions that
>> were using to the DB at a given time.
>>
>> --Scott
>>
>>
>>
>>>
>>> I am curious how others deal with the same issue , is there better or
>>> more inbuilt solutions
>>> to store the application level user in the audit trail records.
>>>
>>> Regds
>>> mallah.
>>>
>>> ( https://www.redgrape.tech )
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>> --
>> --
>> Scott Mead
>> Sr. Architect
>> *OpenSCG *
>> http://openscg.com
>>
>
>

Re: [GENERAL] logging of application level user in audit trigger

[Rajesh Mallah]

Hi Scott / List ,

Thanks for the response,

the application_name  usage seems to be more natural as it is something
inbuilt.
the audit trigger repo seems to have got update only in past 2 years .

are there other more active projects doing the same thing ?
in my opinion (which may be flawed) pgaudit seems to be logging only with
no possibility
of knowing the old record.

I think audit trail of tables is something that is frequently required , it
would be
nice if some inbuilt (core) feature comes someday in postgresql for auditing


regds
mallah.





On Wed, May 10, 2017 at 1:17 AM, Scott Mead  wrote:

>
>
> On Tue, May 9, 2017 at 2:50 PM, Rajesh Mallah 
> wrote:
>
>> Hi ,
>>
>> I am referring to   audit trigger as described in
>>
>> https://wiki.postgresql.org/wiki/Audit_trigger_91plus OR
>> https://wiki.postgresql.org/wiki/Audit_trigger
>>
>> Although there are documented limitations for these systems , but
>> I would like to mention and seek suggestion on a limitation that I feel
>> is biggest .
>>
>>
>> It is very a common design pattern in  web-applications that the same
>> database
>> user is shared for making database changes by different "logged in users"
>> of the
>> web application.
>>
>> I feel the core of audit is all about "who"  , "when" and "what" .
>>
>> In the current audit trigger the "who" is essentially the ROLE which is
>> the actor of
>> the trigger , but in most scenarios the user associated with the
>> web-application session
>> is the one that is seeked.
>>
>> In one of my past projects I passed the web-user to the trigger by
>> setting a postgres
>> custom variable during the database connection and reading it inside the
>> trigger
>> and storing it in the audit log table.
>>
>
> This is a good method, and one of the best for just straight auditing.
> The other trick I've seen is to use the 'application_name' field.
> Developers would issue:
>
> SET application_name = "app_user:app_name';
>
> This can be read from pg_stat_activity.application_name.  I believe you'd
> be able to read that in a procedure with 'show application_name'; and, you
> can see it live in pg_stat_activity as well.
>
> select application_name, count(*)
>   FROM pg_stat_activity
>  GROUP by application_name;
>
>   You'd be able to see each user/app pair and the number of sessions that
> were using to the DB at a given time.
>
> --Scott
>
>
>
>>
>> I am curious how others deal with the same issue , is there better or
>> more inbuilt solutions
>> to store the application level user in the audit trail records.
>>
>> Regds
>> mallah.
>>
>> ( https://www.redgrape.tech )
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
>
> --
> --
> Scott Mead
> Sr. Architect
> *OpenSCG *
> http://openscg.com
>

Re: [GENERAL] Upgrading postgresql minor version

[David G. Johnston]

On Wed, May 10, 2017 at 9:34 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, May 10, 2017 at 8:13 AM, Ron Ben  wrote:
>
>> Not possible
>> https://www.postgresql.org/download/linux/debian/
>>
>> To upgrade I do: apt-get install postgresql-9.3
>> There is no way to "roll back" from here.
>> I can not choose which version to install, it install the latest version
>> packed for debian. Currently its 9.3.16
>>
>> The docuntation says that upgrading minor version is just "replacing the
>> executables", so there has to be a way to save them on the side for roll
>> back.
>>
>
> ​I see that the binaries involved are located under "/usr/​lib/postgresql"
> - in directories matching up with the version-cluster naming scheme the the
> apt packages employ.  In theory backing up and restoring the relevant
> directories under there should work.  This would only work for minor
> version switching.
>

​My bad - its not cluster specific ... which makes sense as there is no way
to update just individual clusters.

David J.​

Re: [GENERAL] character encoding of the postgres database

[Adrian Klaver]

On 05/10/2017 08:48 AM, Sandeep Gupta wrote:

Currently, the postgres database by has SQL_ASCII encoding.

psql -p 5771 postgres -l
List of databases
   Name|  Owner  | Encoding  | Collate | Ctype |  Access privileges
---+-+---+-+---+-
 postgres  | sandeep | SQL_ASCII | C   | C |
 template0 | sandeep | SQL_ASCII | C   | C | =c/sandeep +
   | |   | |   | sandeep=CTc/sandeep
 template1 | sandeep | SQL_ASCII | C   | C | =c/sandeep +
   | |   | |   | sandeep=CTc/sandeep
(3 rows)

Is it possible to start the postgres database with UTF-8 encoding, instead
of modifying it later.


See Peter's post, just remember that SQL_ASCII is essentially no 
encoding so be prepared for issues. I would test before doing this on 
live data.





Thanks
sandeep





--
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] Upgrading postgresql minor version

[David G. Johnston]

On Wed, May 10, 2017 at 8:13 AM, Ron Ben  wrote:

> Not possible
> https://www.postgresql.org/download/linux/debian/
>
> To upgrade I do: apt-get install postgresql-9.3
> There is no way to "roll back" from here.
> I can not choose which version to install, it install the latest version
> packed for debian. Currently its 9.3.16
>
> The docuntation says that upgrading minor version is just "replacing the
> executables", so there has to be a way to save them on the side for roll
> back.
>

​I see that the binaries involved are located under "/usr/​lib/postgresql"
- in directories matching up with the version-cluster naming scheme the the
apt packages employ.  In theory backing up and restoring the relevant
directories under there should work.  This would only work for minor
version switching.

If not, the documntation is insuffecent. We all know that sometimes even
> the smallest change can cause troubles.
>

​Maybe, but as noted its not the PostgreSQL project's ​documentation that
is lacking here - since you are not installing from source.

I've never seen whether the upgrade process touches any other files, or
exactly what it touches for that matter, but that should should be enough
info to experiment.  It would be interesting to use Docker (or an
equivalent) and perform an update and see what what the new layer shows as
having been changed.

David J.

Re: [GENERAL] relation create time

[Peter Eisentraut]

On 5/10/17 12:05, Hu, Patricia wrote:
> I am trying to find out when a table was created in postgresql. Thought
> it would be easy (coming from Oracle world), but haven’t had any luck,
> especially since we are on RDS and can’t peek at the timestamp on the
> file system. Is this information stored anywhere in the catalog?

It is not.

> Or I
> need to store it myself? Is there any plan to add such meta data
> information to the catalog as a feature? Thanks a lot!

You could write an event trigger to record it.

-- 
Peter Eisentraut  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] character encoding of the postgres database

[Peter Eisentraut]

On 5/10/17 11:48, Sandeep Gupta wrote:
> Currently, the postgres database by has SQL_ASCII encoding.

> Is it possible to start the postgres database with UTF-8 encoding, instead
> of modifying it later.

This is done when initdb is run, with the --locale and/or --encoding option.

-- 
Peter Eisentraut  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

[GENERAL] relation create time

[Hu, Patricia]

I am trying to find out when a table was created in postgresql. Thought it 
would be easy (coming from Oracle world), but haven't had any luck, especially 
since we are on RDS and can't peek at the timestamp on the file system. Is this 
information stored anywhere in the catalog? Or I need to store it myself? Is 
there any plan to add such meta data information to the catalog as a feature? 
Thanks a lot!

Thanks,
Patricia

Confidentiality Notice::  This email, including attachments, may include 
non-public, proprietary, confidential or legally privileged information.  If 
you are not an intended recipient or an authorized agent of an intended 
recipient, you are hereby notified that any dissemination, distribution or 
copying of the information contained in or transmitted with this e-mail is 
unauthorized and strictly prohibited.  If you have received this email in 
error, please notify the sender by replying to this message and permanently 
delete this e-mail, its attachments, and any copies of it immediately.  You 
should not retain, copy or use this e-mail or any attachment for any purpose, 
nor disclose all or any part of the contents to any other person. Thank you.

Re: [GENERAL] Select from tableA - if not exists then tableB

[Brian Dunavant]

On Tue, May 9, 2017 at 6:00 PM, Patrick B  wrote:
> SELECT
> split_part(n1.path::text, '/'::text, 18)::integer AS id,
> split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
> lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
> '-1000-1000-3000-6000'::uuid AS guid,
> n1.bytes AS byte_count,
> n1.last_modified AS last_modified
>   FROM tablea n1
>   JOIN tableb s2 ON s2.path = n1.path
>
> Where tablec is the new one. AS you can see, there is no reference for the
> new tablec on that query, so I need to:
>
> - Get the data from the new table,
> - if it is not in there, then go to old table (query above).


I'm assuming tablec is supposed to replace tablea.

Being a view makes it trickier.  You can still do it with:

SELECT
  split_part(n1.path::text, '/'::text, 18)::integer AS id,
  split_part(n1.path::text, '/'::text, 14)::integer AS clientid,
  lower(n1.md5::text)::character(32) AS md5, 0 AS cont,
  '-1000-1000-3000-6000'::uuid AS guid,
  n1.bytes AS byte_count,
  n1.last_modified AS last_modified
FROM (
 select DISTINCT ON (id) [columns] from (
select [columns/pads], 1 as tableorder from tablec
union all
select [columns/pads], 2 as tableorder from tablea
 ) t
 ORDER BY id, tableorder
   ) n1
  JOIN tableb s2 ON s2.path = n1.path;

This will cause it to prefer the data in tablec, but use any id's in
tablea that aren't in tablec .

This may be very slow, as i'm not sure if predicate pushdown would
happen here, so this may cause full table scans of both tablea and
tablec possibly making performance bad if those are large tables.   It
should do what you are asking for though.


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

[GENERAL] character encoding of the postgres database

[Sandeep Gupta]

Currently, the postgres database by has SQL_ASCII encoding.

psql -p 5771 postgres -l
List of databases
   Name|  Owner  | Encoding  | Collate | Ctype |  Access privileges
---+-+---+-+---+-
 postgres  | sandeep | SQL_ASCII | C   | C |
 template0 | sandeep | SQL_ASCII | C   | C | =c/sandeep +
   | |   | |   | sandeep=CTc/sandeep
 template1 | sandeep | SQL_ASCII | C   | C | =c/sandeep +
   | |   | |   | sandeep=CTc/sandeep
(3 rows)

Is it possible to start the postgres database with UTF-8 encoding, instead
of modifying it later.

Thanks
sandeep


-- 
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] Logical decoding CPU-bound w/ large number of tables

[Mathieu Fenniak]

Hi Andres, Tom,

Andres, it seems like the problem is independent of having large data
manipulations mixed with schema changes.  The below test case demonstrates
it with just schema changes.

Tom, I've tested your patch, and it seems to have a positive impact for
sure.  I've documented a test case to reproduce this issue (below), and
your avoid-search-in-catcache-invalidate.patch reduces the test case time
from 63 seconds per run to 27 seconds per run.  Here's an updated sample
capture with the patch applied:
https://gist.github.com/mfenniak/cef36699deb426efd8e433027553a37e

Here's the test case I've been using for the above measurements:

a) creating 10,000 tables in 200 schemas

DO $$
DECLARE
i INTEGER;
j INTEGER;
schema TEXT;
BEGIN
FOR i IN 1..200 LOOP
schema := 'test_' || i;
EXECUTE format('CREATE SCHEMA IF NOT EXISTS %I', schema);
FOR j IN 1..500 LOOP
EXECUTE format('CREATE TABLE IF NOT EXISTS %I.%I (f1 int, f2
int)', schema, 'test_' || j);
END LOOP;
END LOOP;
END;
$$;

b) start logical replication w/ pg_recvlogical, printing out timestamps of
messages and timestamps of transactions:

  pg_recvlogical --create-slot --start -f - --if-not-exists
--plugin=test_decoding --slot=cputest --dbname=mathieu.fenniak
--username=postgres --option=include-timestamp | gawk '{ print
strftime("%Y-%m-%d %H:%M:%S"), $0; fflush(); }'

c) drop a few schemas in a transaction

begin;
drop schema test_1 cascade;
drop schema test_2 cascade;
drop schema test_3 cascade;
drop schema test_4 cascade;
drop schema test_5 cascade;
drop schema test_6 cascade;
commit;


Six schema drops had a 63s second delay between the actual commit and
pg_recvlogical outputting it w/ stock 9.5.4:
2017-05-10 08:35:12 BEGIN 956
2017-05-10 08:36:15 COMMIT 956 (at 2017-05-10 08:35:12.106773-06)

Six schema drops w/ Tom's patch (applied to 9.5.4) had a 27 second delay:
2017-05-10 08:48:08 BEGIN 942
2017-05-10 08:48:35 COMMIT 942 (at 2017-05-10 08:48:08.182015-06)

(other test details: wal_level=logical; max_wal_senders=5;
wal_sender_timeout = 3600s; max_locks_per_transaction = 65536;
max_replication_slots = 5)



On Fri, May 5, 2017 at 7:38 PM, Andres Freund  wrote:

> On 2017-05-05 21:32:27 -0400, Tom Lane wrote:
> > Andres Freund  writes:
> > > On 2017-05-05 14:24:07 -0600, Mathieu Fenniak wrote:
> > >> It appears that most of the time is spent in the
> > >> RelfilenodeMapInvalidateCallback and CatalogCacheIdInvalidate cache
> > >> invalidation callbacks, both of which appear to be invalidating caches
> > >> based upon the cache value.
> >
> > > I think optimizing those has some value (and I see Tom is looking at
> > > that aspect, but the bigger thing would probably be to do fewer
> lookups.
> >
> > I'm confused --- the lookup side of things is down in the noise in
> > Mathieu's trace.
>
> Err, sorry. Completely mangled that sentence.  Executing fewer
> invalidations.  We currently are likely re-executing the same set of
> invalidations constantly in Mathieu's case.
>
> Background: When decoding a transaction during logical decoding we're
> currently re-executing *all* a transaction's own cache invalidations, if
> it has any, at every new command-id observed in the WAL stream.  That's
> because currently invalidations are only sent at commit, so we don't
> know from "when" they are.  But I think there's some very low-hanging
> fruits reducing the frequency at which those are executed.
>
> In many cases where there's just a few schema changes in a transaction,
> this doesn't hurt badly.  But if you have a transaction that does a
> bootload of schema changes *and* a has a lot of other changes, it gets
> expensive.
>
> Mathieu: The above also indicates a possible workaround, you can try
> separating larger amounts of data manipulations from schema changes,
> into separate transactions.
>
> Greetings,
>
> Andres Freund
>

Re: [GENERAL] Upgrading postgresql minor version

[Alan Hodgson]

On Wednesday 10 May 2017 17:13:50 Ron Ben wrote:
> Not possible 
> https://www.postgresql.org/download/linux/debian/
>  
> To upgrade I do: apt-get install postgresql-9.3
> There is no way to "roll back" from here.
> I can not choose which version to install, it install the latest version
> packed for debian. Currently its 9.3.16 
> The docuntation says that upgrading minor version is just "replacing the
> executables", so there has to be a way to save them on the side for roll
> back. If not, the documntation is insuffecent. We all know that sometimes
> even the smallest change can cause troubles.
> 

Download the old packages from the repository. You can use dpkg to manually 
downgrade them if needed.  You can also use dpkg to get a list of the files in 
any particular package if you want to save them manually.

Or you should ask your system administrator for help.

This is not a PostgreSQL issue and that's why it (and many other things) are 
not in the docs. Every version of Linux uses a different package manager. The 
system administrator needs to know how to use it.


-- 
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] Upgrading postgresql minor version

[Andreas Joseph Krogh]

På onsdag 10. mai 2017 kl. 17:13:50, skrev Ron Ben >:
Not possible 
https://www.postgresql.org/download/linux/debian/
 
To upgrade I do: apt-get install postgresql-9.3
There is no way to "roll back" from here.
I can not choose which version to install, it install the latest version 
packed for debian. Currently its 9.3.16
 
The docuntation says that upgrading minor version is just "replacing the 
executables", so there has to be a way to save them on the side for roll back.
If not, the documntation is insuffecent. We all know that sometimes even the 
smallest change can cause troubles.


 
Well - if you choose apt as you package-manager then this is really not an 
issue for PostgreSQL. What you're really asking is how to make apt install (or 
downgrade) a specific minor-version even if an updated version exists in the 
package-repo. Maybe you'll have more luck asking that other places than a 
PG-mailing-list.

 --
 Andreas Joseph Krogh
 

Re: [GENERAL] Upgrading postgresql minor version

[Ron Ben]

Not possible 
https://www.postgresql.org/download/linux/debian/
 
To upgrade I do: apt-get install postgresql-9.3
There is no way to "roll back" from here.
I can not choose which version to install, it install the latest version packed for debian. Currently its 9.3.16
 
The docuntation says that upgrading minor version is just "replacing the executables", so there has to be a way to save them on the side for roll back.
If not, the documntation is insuffecent. We all know that sometimes even the smallest change can cause troubles.ב מאי 10, 2017 18:02, Andreas Joseph Krogh כתב:På onsdag 10. mai 2017 kl. 16:55:50, skrev Ron Ben :




 
I think you miss understood me.

pg_dump dumps the data. the tables, functions and the data saved in them.

I have daily backups for this so i'm not worried.

 

What i'm woried about are the "executables files". These files are what is actualy being updated when you update the version.

I want to be able to roll them back in case of upgrade failure without rolling back the data.

 

 

Basicly what I want is to save on the side all the files that will be change by the update and in case of failure just to replace them back.

Is it doable? How?





 

What about just re-installing the previous version the same way you installed the "patch-release"-version?


--
Andreas Joseph Krogh



 

Re: [GENERAL] Upgrading postgresql minor version

[Andreas Joseph Krogh]

På onsdag 10. mai 2017 kl. 16:55:50, skrev Ron Ben >:
  I think you miss understood me.
pg_dump dumps the data. the tables, functions and the data saved in them.
I have daily backups for this so i'm not worried.
 
What i'm woried about are the "executables files". These files are what is 
actualy being updated when you update the version.
I want to be able to roll them back in case of upgrade failure without rolling 
back the data.
 
 
Basicly what I want is to save on the side all the files that will be change 
by the update and in case of failure just to replace them back.
Is it doable? How?



 
What about just re-installing the previous version the same way you installed 
the "patch-release"-version?

 --
 Andreas Joseph Krogh
 

Re: [GENERAL] Upgrading postgresql minor version

[Ron Ben]

I think you miss understood me.
pg_dump dumps the data. the tables, functions and the data saved in them.
I have daily backups for this so i'm not worried.
 
What i'm woried about are the "executables files". These files are what is actualy being updated when you update the version.
I want to be able to roll them back in case of upgrade failure without rolling back the data.
 
 
Basicly what I want is to save on the side all the files that will be change by the update and in case of failure just to replace them back.
Is it doable? How?
ב מאי 10, 2017 16:27, Adrian Klaver כתב:On 05/10/2017 06:08 AM, Ron Ben wrote:> I'm about to upgrade my postgresql to the latest 9.3 version> On my test server eveything works.> However I want to save a backup of my production server before the > upgrade...> I'm not sure how I do that.https://www.postgresql.org/docs/9.3/static/app-pg-dumpall.html> It says that only system files are changed during upgrade... which > folders exactly I need to save on the side for roll back?I would say that would depend on the nature of the release changes.> I know I can take a snap shoot of my server before the update but i'm > affried that rolling back to this snap shoot will also roll back the > data itself?> Since after the update the users continue to work I don't want to lose > thier data.Well if the upgrade takes then you don't need to rollback. If it did not then the users did not get any work done or any data they entered would be considered suspect. If you are concerned then take another pg_dumpall before rolling back.> How do I do that?-- Adrian Klaveradrian.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] Python versus Other Languages using PostgreSQL

[Adrian Klaver]

On 05/09/2017 01:00 PM, Paul Hughes wrote:
Thank you all for taking the time to answer my questions. I've been out 
of the programming world for a long time, so I am back to being a 
newbie. I was told this is the place for newcomers to ask questions. I 
apologize if my questions did not contain the necessary sophistication 
or nuance for some to answer.


It has more to do with making unsubstantiated assertions, namely:

"I noticed that most of the largest web platforms that use PostgreSQL as 
their primary database, also use Python as their primary back-end 
language. ..."


Many on this list(myself included) will want to know how you came to 
that conclusion and I am speaking as someone who uses Python, Django and 
Postgres.




George, your answer makes the most sense. Thank you!  You said:


Most cloud servers come preconfigured with some variation of either

the "LAMP" or "LAPP" stack: i.e. Linux, Apache, MySQL or Postgresql,
PHP or Python.


There needs to be a compelling *application* reason to install

something else: a JVM (or Apache-Tomcat vs regular Apache),
Ruby/Rails, Groovy/Grails, etc.






--
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] Upgrading postgresql minor version

[Jan de Visser]

On Wednesday, May 10, 2017 9:08:16 AM EDT Ron Ben wrote:
> 

Ron, 

You need to figure out how you can make your email client send something else 
than base64 encoded HTML with right-aligned text. Your messages are so hard to 
parse for me I just ignore them, and I assume there's other people that do the 
same.

Attached a screenshot of one of your messages.



-- 
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] Python versus Other Languages using PostgreSQL

[Paul Hughes]

Thank you all for taking the time to answer my questions. I've been out of
the programming world for a long time, so I am back to being a newbie. I
was told this is the place for newcomers to ask questions. I apologize if
my questions did not contain the necessary sophistication or nuance for
some to answer.

George, your answer makes the most sense. Thank you!  You said:

>>Most cloud servers come preconfigured with some variation of either
the "LAMP" or "LAPP" stack: i.e. Linux, Apache, MySQL or Postgresql,
PHP or Python.

>>There needs to be a compelling *application* reason to install
something else: a JVM (or Apache-Tomcat vs regular Apache),
Ruby/Rails, Groovy/Grails, etc.

>


Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Tue, May 9, 2017 at 7:54 AM, George Neuner  wrote:

> On Mon, 8 May 2017 14:26:02 -0700, Paul Hughes 
> wrote:
>
> >Hello,
> >
> >I noticed that most of the largest web platforms that use PostgreSQL as
> >their primary database, also use Python as their primary back-end
> language.
> >Yet, according to every benchmark I could find over the last couple of
> >years, back-end languages like PHP, HHVM, and Node.JS outperform Python by
> >2x to 8x!
> >
> >So here are my questions:
> >
> >1) Why do the largest web applications that use PostgreSQL also use
> Python,
> >even though Python is significantly slower than it's biggest competitors?
>
> Most cloud servers come preconfigured with some variation of either
> the "LAMP" or "LAPP" stack: i.e. Linux, Apache, MySQL or Postgresql,
> PHP or Python.
>
> There needs to be a compelling *application* reason to install
> something else: a JVM (or Apache-Tomcat vs regular Apache),
> Ruby/Rails, Groovy/Grails, etc.
>
>
> >2) Can PostgreSQL just as easily be used with PHP or Node.js? If not, why
> >not?
>
> Absolutely.  The DBMS is (programming) language neutral - the only
> requirement is that a client speak the wire protocol.  That can be
> done natively, or via a library/driver.
>
>
> >3) Can PostgreSQL be made to work seamlessly to take advantage of the
> >superior performance of HHVM or Node.js?
>
> Not really sure what you're asking.  The application running under
> HHVM or node.js is completely separate from Postgresql.
>
> Both PHP and Javascript (generically and for node.js specifically)
> have libraries for Postgresql.
>
>
> George
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Paul Hughes
Executive Director
Vivation International
1-800-514-8483
Skype: Vivation2
p...@vivation.com
http://www.vivation.com/

Re: [GENERAL] Upgrading postgresql minor version

[Adrian Klaver]

On 05/10/2017 06:08 AM, Ron Ben wrote:

I'm about to upgrade my postgresql to the latest 9.3 version
On my test server eveything works.
However I want to save a backup of my production server before the 
upgrade...

I'm not sure how I do that.


https://www.postgresql.org/docs/9.3/static/app-pg-dumpall.html

It says that only system files are changed during upgrade... which 
folders exactly I need to save on the side for roll back?


I would say that would depend on the nature of the release changes.

I know I can take a snap shoot of my server before the update but i'm 
affried that rolling back to this snap shoot will also roll back the 
data itself?
Since after the update the users continue to work I don't want to lose 
thier data.


Well if the upgrade takes then you don't need to rollback. If it did not 
then the users did not get any work done or any data they entered would 
be considered suspect. If you are concerned then take another pg_dumpall 
before rolling back.




How do I do that?



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

[GENERAL] Upgrading postgresql minor version

[Ron Ben]

I'm about to upgrade my postgresql to the latest 9.3 version
 
On my test server eveything works.
However I want to save a backup of my production server before the upgrade...
I'm not sure how I do that.
 
It says that only system files are changed during upgrade... which folders exactly I need to save on the side for roll back?
I know I can take a snap shoot of my server before the update but i'm affried that rolling back to this snap shoot will also roll back the data itself?
Since after the update the users continue to work I don't want to lose thier data.
 
How do I do that?

Re: [GENERAL] Python versus Other Languages using PostgreSQL

[Karsten Hilbert]

On Mon, May 08, 2017 at 05:45:53PM -0700, Paul Hughes wrote:

> Why are Postgres and Python so married,

I dare say that's a misconception.

However, Python "works so well", that "professional amateurs"
(like myself) who gravitate towards PostgreSQL for the
obvious reasons might tend to chose Python for the very same
reasons which may seem to create a bias.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
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] Convert from LATIN9 to UTF-8 / WAL Replication

[Magnus Hagander]

On Wed, May 10, 2017 at 5:05 AM, Fabrízio de Royes Mello <
fabri...@timbira.com.br> wrote:

>
> Em ter, 9 de mai de 2017 às 17:40, basti 
> escreveu:
>
>> Hello,
>>
>> I must convert a Latin9 Pg-cluster (Version 9.1) in utf-8 with minimal
>> downtime, if possible.
>> My idea is to use WAL replication for that.
>> Is it usable for that or in other words, can WAL Replication handle
>> different encoding on master/slave?
>
>
> Short answer: no!
>
>
> Is there perhaps an other way to do that?
>
>
> I never tried it, but you can use some logical replication tool like Slony.
>
>
> Is it possible to use on Master Version 9.1 and on Slave 9.6 (it will
>> get the new master in the next step)?
>>
>>
> Unfortunately no because on 9.1 version we don't have support for logical
> replication yet. We start supporting it since 9.4 using pglogical
> extension. As I said before you should consider use one external
> replication tool like Slony to do this job.
>
>
In a scenario like this it might also be worth upgrading to 9.6 using
pg_upgrade while maintaining the latin9 encoding, and then do a second
migratino which changes the encoding from 9.6 w latin9 to 9.6 w utf8. That
way you can get access to the newer tools such as pglogical to help you do
that.

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

Re: [GENERAL] Python versus Other Languages using PostgreSQL

[Dick Kniep]

As a long time user of the combination, Postgresql, Python, Django, 
Flask etc. here are my 2 cents:


The frameworks of python; Django (enormous) or Flask (smaller) and there 
are more, are of superb quality, battle hardened and are used in many 
many companies and high volume sites around the world. The rigid 
implementation of the mvc concept makes for readable, maintainable code. 
This is far far more important than execution speed, because:


- most modern application use databases where the IO time is always much 
much more important than the speed of processing.


- The power (enormous) of the current hardware is such that extra cycles 
do not cost much


- Price (virtually nothing) of the current hardware allows for swift 
adding of extra hardware if necessary


- Price of developers is very very high, so any gain in development 
speed directly returns to the company as MUCH less expense


- The reliability of the Postgresql has always been a very strong point 
(I myself use it since 2002 and have had hardly any problems with it) 
even under bad circumstances. The loss of data because of a malfunction 
of the database does simply not occur. This comes at a cost (raw speed), 
but the cost of loosing data is much much higher than a little extra 
hardware.


Cheers


On 09-05-17 16:48, Francisco Olarte wrote:

On Tue, May 9, 2017 at 1:44 PM, vinny  wrote:

In fact, I don't think many companies/developers even choose a language
or database, but rather just use whatever they have experience in.

That is choosing. You choose them because you know them.


Francisco Olarte.






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