Re: using pg_basebackup for point in time recovery

[Ron]

On 06/21/2018 12:27 AM, Michael Paquier wrote:
[snip]

Attached is a patch which includes your suggestion.  What do you think?
As that's an improvement, only HEAD would get that clarification.


You've *got* to be kidding.

Fixing an ambiguously or poorly worded bit of *documentation* should 
obviously be pushed to all affected versions.


--
Angular momentum makes the world go 'round.

Re: using pg_basebackup for point in time recovery

[Michael Paquier]

Hi Pierre,

On Wed, Jun 20, 2018 at 08:06:31AM +, Pierre Timmermans wrote:
> Hi Michael

You should avoid top-posting on the Postgres lists, this is not the
usual style used by people around :)

> Thanks for the confirmation. Your rewording removes the confusion. I
> would maybe take the opportunity to re-instate that pg_dump cannot be
> used for PITR, so in the line of 
> "These are backups that could be used for point-in-time recovery if
> combined with a WAL archive able to recover up to the wanted recovery
> point.  These backups are typically much faster to backup and restore
> than pg_dump for large deployments but can result as well in larger
> backup sizes, so the speed of one method or the other is to evaluate
> carefully first. Consider also that pg_dump backups cannot be used for
> point-in-time recovery."

Attached is a patch which includes your suggestion.  What do you think?
As that's an improvement, only HEAD would get that clarification.

>  Maybe the confusion stems from the fact that if you restore a
> standalone (self-contained) pg_basebackup then - by default - recovery
> is done with the recovery_target immediate option, so if one needs
> point-in-time recovery he has to edit the recovery.conf and brings the
> archives..

Perhaps.  There is really nothing preventing one to add a recovery.conf
afterwards, which is also why pg_basebackup -R exists.  I do that as
well for some of the framework I work with and maintain.
--
Michael
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index 982776ca0a..ccc0a66bf3 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -1430,12 +1430,15 @@ restore_command = 'cp /mnt/server/archivedir/%f %p'
  Standalone Hot Backups
 
  
-  It is possible to use PostgreSQL's backup facilities to
-  produce standalone hot backups. These are backups that cannot be used
-  for point-in-time recovery, yet are typically much faster to backup and
-  restore than pg_dump dumps.  (They are also much larger
-  than pg_dump dumps, so in some cases the speed advantage
-  might be negated.)
+  It is possible to use PostgreSQL's backup
+  facilities to produce standalone hot backups.  These are backups that
+  could be used for point-in-time recovery if combined with a WAL
+  archive able to recover up to the wanted recovery point.  These backups
+  are typically much faster to backup and restore than pg_dump for large
+  deployments but can result as well in larger backup sizes, so the
+  speed of one method or the other is to evaluate carefully first.  Note
+  also that pg_dump backups cannot be used
+  for point-in-time recovery.
  
 
  


signature.asc
Description: PGP signature

Re: Using DSN Connection and knowing windows username

[Łukasz Jarych]

Thank you Adrian,

In the meantime just an idea, but could you capture the system user in a
> table in Access and use that to pass on to Postgres?


Brilliant ! simple and genious!

The purpose of it is to have history log table with DML and DDL changes
using triggers.

Best,
Luke




2018-06-21 0:11 GMT+02:00 Adrian Klaver :

> On 06/20/2018 07:06 AM, Łukasz Jarych wrote:
>
>> David G,
>>
>> thank you.
>> Can you confirm if i am thinking correctly ?
>>
>> So I can set up authetification to know which user is logged on and use
>> this as postgresql user?
>>
>
> Only if the system user is a postgres user or can be mapped to one:
>
> https://www.postgresql.org/docs/10/static/auth-username-maps.html
>
>
>> But i think it will be not possible to use DSN connection with this.
>>
>
> If you are talking about the ODBC DSN you use to create the linked table
> in Access then you are correct you are limited to whatever user is
> specified in the ODBC Manager.
>
> It would help to know what you plan to use the user name for?
>
> In the meantime just an idea, but could you capture the system user in a
> table in Access and use that to pass on to Postgres?
>
>
>
>> Best ,
>> Luke
>>
>> 2018-06-20 15:34 GMT+02:00 David G. Johnston > >:
>>
>> On Wednesday, June 20, 2018, Łukasz Jarych > > wrote:
>>
>> How to know in postgresql which specific windows user is using
>> database?
>>
>> You cannot.  All the server knows is the specific user credentials
>> it is authenticating.
>>
>> That said you can authenticate those credentials in such a way so
>> that knowing the signed on user you would also know who they are in
>> any environment that uses the same authentication source - and if
>> that source supplies their Windows identity you are golden. The
>> specific setups involved here are outside my experience, though.
>>
>> David J.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: SQL Query never ending...

[David G. Johnston]

On Wed, Jun 20, 2018 at 3:34 PM, Fabrízio de Royes Mello <
fabri...@timbira.com.br> wrote:

> And use some external service like pastebin.com to send long SQL
> statements.
>

​Or just attach a text file - those are allowed on these lists.
​
David J.

Re: SQL Query never ending...

[Fabrízio de Royes Mello]

2018-06-20 18:35 GMT-03:00 DiasCosta :
>
> Hi all,
> can someone help me?
>
> I don't know if this is the correct list for this matter. If I'm wrong,
please bear with me and point me in right direction.
>

Here is a good start...



> I have a large query which, largely after more than 24 hours running,
doesn't come to an end;
> However I can see, using system tools, that the postgres process keeps,
although  slowly, reading and writing bytes and the "afinity" dedicated
cores are at 6.25% .
>
> I tried https://www.depesz.com/ but the query was rejected.
>

Are you talking about http://explain.depesz.com ?? If yes you should copy
and paste the EXPLAIN output of your query... If you don't do it yet,
please do it and send to us the results .

And use some external service like pastebin.com to send long SQL statements.

Regards,

--
   Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Re: Using DSN Connection and knowing windows username

[Adrian Klaver]

On 06/20/2018 07:06 AM, Łukasz Jarych wrote:

David G,

thank you.
Can you confirm if i am thinking correctly ?

So I can set up authetification to know which user is logged on and use 
this as postgresql user?


Only if the system user is a postgres user or can be mapped to one:

https://www.postgresql.org/docs/10/static/auth-username-maps.html



But i think it will be not possible to use DSN connection with this.


If you are talking about the ODBC DSN you use to create the linked table 
in Access then you are correct you are limited to whatever user is 
specified in the ODBC Manager.


It would help to know what you plan to use the user name for?

In the meantime just an idea, but could you capture the system user in a 
table in Access and use that to pass on to Postgres?





Best ,
Luke

2018-06-20 15:34 GMT+02:00 David G. Johnston >:


On Wednesday, June 20, 2018, Łukasz Jarych mailto:jarys...@gmail.com>> wrote:

How to know in postgresql which specific windows user is using
database? 



You cannot.  All the server knows is the specific user credentials
it is authenticating.

That said you can authenticate those credentials in such a way so
that knowing the signed on user you would also know who they are in
any environment that uses the same authentication source - and if
that source supplies their Windows identity you are golden. The
specific setups involved here are outside my experience, though.

David J.





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

SQL Query never ending...

[DiasCosta]

Hi all,
can someone help me?

I don't know if this is the correct list for this matter. If I'm wrong, 
please bear with me and point me in right direction.



I have a large query which, largely after more than 24 hours running, 
doesn't come to an end;
However I can see, using system tools, that the postgres process keeps, 
although  slowly, reading and writing bytes and the "afinity" dedicated 
cores are at 6.25% .


I tried https://www.depesz.com/ but the query was rejected.

-- Scenario:
-- OS: Windows 12 R2 Standard
-- RAM: 128GB
-- CPU: Intel Xeon E5-2640 v4 @2.40GH (2 processors) (16 cores)
-- PostgreSQL 9.6
-- Database  category: OLAP (Tables used in the query are not dynamic 
and the statistics for all of them are up to date)

--
***
-- The query you'll see later bellow uses the following tables:
--
-- This facts table has around 150 rows.
CREATE TEMPORARY TABLE  analise_transac_mes
( ctrl_cod_valida integer NOT NULL,
  cod_controlo integer NOT NULL,
  causa character varying(300),
  Fornecedor text,
  ordem integer,
  num_serie text,
  titulo text,
  tipo_produto text,
  data_dia_hora text,
  cod_viatura text,
  cod_licenca text,
  val_inval_excl character varying(12),
  mes character varying(25),
  tipo_licenca character varying(25),
  data_controlo timestamp without time zone NOT NULL DEFAULT now(),
  utilizador character varying(30) DEFAULT "current_user"(),
  senha_entrega character varying(12),
  senha_fornecedor_entrega character varying(12),
  proc_extra character(3),
  quando date DEFAULT now(),
  cod_cliente character varying(15),
  tem_venda character varying(6),
  CONSTRAINT pk_analise_transac_mes PRIMARY KEY (ctrl_cod_valida);
--
SELECT pg_prewarm('TT_Analise_Transac_Oper_Mes');
--
-- With following indexes:
CREATE INDEX IF NOT EXISTS TT_Analise_Transac_Oper_Mes_Fornecedor ON 
TT_Analise_Transac_Oper_Mes(Fornecedor);
CREATE INDEX IF NOT EXISTS TT_Analise_Transac_Oper_Mes_encomenda ON 
TT_Analise_Transac_Oper_Mes(encomenda);
CREATE INDEX IF NOT EXISTS TT_Analise_Transac_Oper_Mes_val_inval_excl ON 
TT_Analise_Transac_Oper_Mes(val_inval_excl);
CREATE INDEX IF NOT EXISTS TT_Analise_Transac_Oper_Mes_proc_extra ON 
TT_Analise_Transac_Oper_Mes(proc_extra);
CREATE INDEX IF NOT EXISTS TT_Analise_Transac_Oper_Mes_senha_entrega ON 
TT_Analise_Transac_Oper_Mes(senha_entrega);
CREATE INDEX IF NOT EXISTS 
TT_Analise_Transac_Oper_Mes_senha_fornecedor_entrega ON 
TT_Analise_Transac_Oper_Mes(senha_fornecedor_entrega);

--
--

-- Following table has  1800 rows
--
CREATE TABLE bilhetica_base_2017_01.lst_km_por_etapa_2017
(
  cod_encomenda text NOT NULL,
  encomenda character varying(150),
  encomenda_amtl character varying(150),
  Fornecedor character varying(60) NOT NULL,
  etapa_km numeric(13,9),
  mes_ref character varying(15) NOT NULL,
  utilizador character varying(30) DEFAULT "current_user"(),
  data timestamp without time zone DEFAULT now(),
  notas character varying,
  caracter character(1),
  senha_entrega character varying(12),
  senha_fornecedor_entrega character varying(12),
  CONSTRAINT pk_lst_km_por_etapa_2017 PRIMARY KEY (cod_encomenda, 
Fornecedor, mes_ref),
  CONSTRAINT dv_lst_km_por_etapa_caracter CHECK (caracter = ANY 
(ARRAY[NULL::bpchar, 'P'::bpchar, 'D'::bpchar]))

);

SELECT pg_prewarm('TT_Km_por_Etapa_2017');
--
--With following indexes:
CREATE INDEX i_Km_por_Etapa_Fornecedor  ON TT_Km_por_Etapa_2017 
(Fornecedor);

CREATE INDEX i_Km_por_Etapa_Mes_Ref  ON TT_Km_por_Etapa_2017 (Mes_Ref);
CREATE INDEX i_Km_por_Etapa_Cod_encomenda  ON TT_Km_por_Etapa_2017  
(Cod_encomenda);

CREATE INDEX i_Km_por_Etapa_encomenda  ON TT_Km_por_Etapa_2017 (encomenda);
--
--

 -- This table has  90 rows
CREATE TABLE bilhetica_base_2017_01.encomendas_n2v_2017
(
  senha_fornecedor_entrega text,
  senha_entrega text,
  cod_encomenda character varying(12) NOT NULL,
  desig_encomenda_aml text,
  desig_encomenda_polis_antigo text,
  desig_encomenda_polis_novo text,
  encomenda_base text,
  modalidade text,
  tipo_aml text,
  tipo_polis text,
  tarifa text,
  ultima_actualizacao text,
  ano_corrente text,
  pvp_ano_corrente numeric(7,3),
  desconto_ano_corrente numeric(6,2),
  mes_pvp_ano_corrente character varying(13),
  pvp_ref_ano_corrente numeric(7,3),
  mes_pvp_ref_ano_corrente character varying(13),
  siit_4_18_e_sub23 character varying(25),
  entra_nas_contas character(1),
  etapa_km_julho numeric(6,3),
  mes_ref character varying(13) NOT NULL,
  versao text,
  notas_aml text,
  notas_polis text,
  notas text,
  CONSTRAINT pk_encomendas_n2v_2017 PRIMARY KEY (cod_encomenda, mes_ref));
--
SELECT pg_prewarm('TT_encomendas_N2V_2017');
--
-- With following indexes;
CREATE INDEX i_encomendas_n2v_2017_senha_entrega  ON 
TT_encomendas_N2v_2017  (senh

Re: Suggestion about logging only every n-th statement

[Adrien Nayrat]

On 06/20/2018 03:08 PM, Andreas Kretschmer wrote:
> I would suggest using of pg_stat_statements for that.

I agree, but sometimes it is not sufficient. For example, if you need query's
parameters.

-- 
Adrien NAYRAT




signature.asc
Description: OpenPGP digital signature

Re: Is there a way to be notified on the CREATE TABLE execution?

[David G. Johnston]

On Wed, Jun 20, 2018 at 1:02 PM, Alvaro Herrera 
wrote:

> On 2018-Jun-20, Igor Korot wrote:
>
> > [quote]
> > In order to create an event trigger, you must first create a function
> > with the special return type event_trigger. This function need not
> > (and may not) return a value; the return type serves merely as a
> > signal that the function is to be invoked as an event trigger.
> > [/quote]
> >
> > So, the function has to be created and compiled.
>

​The section talking about C-language event triggers leads with:​

​"""
This section describes the low-level details of the interface to an event
trigger function. This information is only needed when writing event
trigger functions in C. If you are using a higher-level language then these
details are handled for you. In most cases you should consider using a
procedural language before writing your event triggers in C. The
documentation of each procedural language explains how to write an event
trigger in that language.
"""  ​

​In short, I agree that cursory reading of the main event trigger chapter
could ​lead one to conclude that they are implemented in "C"; but all of
the relevant information is available in the docs and correctly pointed too
and accessible if one reads carefully or looks for it explicitly.  I don't
see how it can be much improved simply and there doesn't seem to be enough
confusion (or use) to warrant significant effort in that area.


> Event trigger functions can be written in plpgsql.  You can use
> pg_event_trigger_ddl_commands() in the ddl_command_end event to obtain a
> few tidbits about the DDL that was just executed.
>

​There are a number of examples of a pl/pgsql function returning an
"event_trigger" ​pseudo-type: but I cannot locate an official statement
that doing so is valid.  I was expecting a paragraph at [1] but it is not
there.  Adding one and linking it to the overall event trigger chapter as
well as the event trigger section of the pl/pgsql chapter seems warranted.

[1]
https://www.postgresql.org/docs/10/static/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS


David J.

Re: Postgres 10.4 crashing when using PLV8

[Mukesh Chhatani]

Thanks David for the response, I have opened a issue with PLV8 team.

Let me know if I should report this bug to postgres or not, since I was not
sure thus I sent email earlier.

Regards,
Mukesh

On Wed, Jun 20, 2018 at 3:02 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Jun 20, 2018 at 12:46 PM, Mukesh Chhatani <
> chhatani.muk...@gmail.com> wrote:
>
>> I am trying to use the PLV8 via function and while using the function
>> created via PLV8 in one of the create materialized view, postgres crashes,
>> attached is the log file with DEBUG5 turned on.
>>
>
> ​These are not the correct place to post possible bug reports for
> third-party extensions.  plV8 has an active Issues listing on GitHub and
> you should post your observations there.  They can report upstream to us if
> there is indeed something specific in core causing their code to fail.
>
> Additionally, in the future if you find that your email is indeed a core
> PostgreSQL issue please just submit it to one of the available lists.  If
> you are unsure pgsql-general is a safe choice.  For this email enough
> information has been provided that our official pgsql-bugs email list (or
> form) would be acceptable and indeed preferred.
>
> David J.​
>
>

Re: Can PostgreSQL create new WAL files instead of reusing old ones?

[Jerry Jelinek]

As Dave described in his original email on this topic, we'd like to avoid
recycling WAL files since that can cause performance issues when we have a
read-modify-write on a file that has dropped out of the cache.

I have implemented a small change to allow WAL recycling to be disabled. It
is visible at:
https://cr.joyent.us/#/c/4263/

I'd appreciate getting any feedback on this.

Thanks,
Jerry

Re: Is there a way to be notified on the CREATE TABLE execution?

[Alvaro Herrera]

On 2018-Jun-20, Igor Korot wrote:

> [quote]
> In order to create an event trigger, you must first create a function
> with the special return type event_trigger. This function need not
> (and may not) return a value; the return type serves merely as a
> signal that the function is to be invoked as an event trigger.
> [/quote]
> 
> So, the function has to be created and compiled.

Event trigger functions can be written in plpgsql.  You can use
pg_event_trigger_ddl_commands() in the ddl_command_end event to obtain a
few tidbits about the DDL that was just executed.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Postgres 10.4 crashing when using PLV8

[David G. Johnston]

On Wed, Jun 20, 2018 at 12:46 PM, Mukesh Chhatani  wrote:

> I am trying to use the PLV8 via function and while using the function
> created via PLV8 in one of the create materialized view, postgres crashes,
> attached is the log file with DEBUG5 turned on.
>

​These are not the correct place to post possible bug reports for
third-party extensions.  plV8 has an active Issues listing on GitHub and
you should post your observations there.  They can report upstream to us if
there is indeed something specific in core causing their code to fail.

Additionally, in the future if you find that your email is indeed a core
PostgreSQL issue please just submit it to one of the available lists.  If
you are unsure pgsql-general is a safe choice.  For this email enough
information has been provided that our official pgsql-bugs email list (or
form) would be acceptable and indeed preferred.

David J.​

Re: Is there a way to be notified on the CREATE TABLE execution?

[Igor Korot]

Hi, Francisco,

On Wed, Jun 20, 2018 at 12:22 PM, Francisco Olarte
 wrote:
> Igor:
>
> On Wed, Jun 20, 2018 at 7:07 PM, Igor Korot  wrote:
>> Just one more question:
>> This page https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
>> does not reference
>> Windows/MSVC/MinGW as a way to compile the code.
>
> Sorry, I don't do windows.
>
> You do not need C extension functions anyway, unless your usage
> pattern is truly bizarre a triger for ddl could be written in any pl.

>From the 
>https://www.postgresql.org/docs/current/static/event-trigger-definition.html:

[quote]
In order to create an event trigger, you must first create a function
with the special return type event_trigger. This function need not
(and may not) return a value; the return type serves merely as a
signal that the function is to be invoked as an event trigger.
[/quote]

So, the function has to be created and compiled.

Am I missing something?

Thank you.

>
> Francisco Olarte.

Postgres 10.4 crashing when using PLV8

[Mukesh Chhatani]

Hello Team,

I am trying to use the PLV8 via function and while using the function
created via PLV8 in one of the create materialized view, postgres crashes,
attached is the log file with DEBUG5 turned on.

SQL which is breaking the code and SQL function is attached.

Creating materialized view - mat_view_by_product - is the one which causes
the crash.

I have tested the same in below environments

1. Test 1 - Passed
Mac , Postgres - 10.4, PLV8 - 2.1.0

2. Test 2 - Passed
AWS RDS , Postgres - 9.6.6 , PLV8 - 1.5.0

3. Test 3 - Fail, This test was passing earlier on 10.3 but fails on 10.4
AWS EC2 , Postgres - 10.4, PLV8 - 2.3.4

4. Test 4 - Fail
AWS RDS , Postgres - 10.3 , PLV8 - 2.1.0

Please let me know if any more information is required to assist in this
problem.

Thanks for your help.
2018-06-20 19:06:18 UTC [1955]: [235-1] user=,db=,app=,client= DEBUG:  forked 
new backend, pid=2861 socket=11
2018-06-20 19:06:18 UTC [2861]: [1-1] 
user=[unknown],db=[unknown],app=[unknown],client=localhost LOG:  connection 
received: host=localhost port=37222
2018-06-20 19:06:18 UTC [2861]: [2-1] 
user=[unknown],db=[unknown],app=[unknown],client=localhost DEBUG:  SSL 
connection from "(anonymous)"
2018-06-20 19:06:18 UTC [2861]: [3-1] 
user=postgres,db=procured,app=[unknown],client=localhost DEBUG:  postgres 
child[2861]: starting with (
2018-06-20 19:06:18 UTC [2861]: [4-1] 
user=postgres,db=procured,app=[unknown],client=localhost DEBUG:   postgres
2018-06-20 19:06:18 UTC [2861]: [5-1] 
user=postgres,db=procured,app=[unknown],client=localhost DEBUG:  )
2018-06-20 19:06:18 UTC [2861]: [6-1] 
user=postgres,db=procured,app=[unknown],client=localhost DEBUG:  InitPostgres
2018-06-20 19:06:18 UTC [2861]: [7-1] 
user=postgres,db=procured,app=[unknown],client=localhost DEBUG:  my backend ID 
is 3
2018-06-20 19:06:18 UTC [2861]: [8-1] 
user=postgres,db=procured,app=[unknown],client=localhost DEBUG:  
StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, 
xid/subid/cid: 0/1/0
2018-06-20 19:06:18 UTC [2861]: [9-1] 
user=postgres,db=procured,app=[unknown],client=localhost DEBUG:  received 
password packet
2018-06-20 19:06:18 UTC [2861]: [10-1] 
user=postgres,db=procured,app=[unknown],client=localhost LOG:  connection 
authorized: user=postgres database=procured SSL enabled (protocol=TLSv1.2, 
cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
2018-06-20 19:06:18 UTC [2861]: [11-1] 
user=postgres,db=procured,app=psql,client=localhost DEBUG:  
CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, 
xid/subid/cid: 0/1/0
2018-06-20 19:06:18 UTC [2830]: [2-1] user=,db=,app=,client= DEBUG:  snapshot 
of 0+0 running transaction ids (lsn 0/72FC0F20 oldest xid 782 latest complete 
781 next xid 782)
2018-06-20 19:06:27 UTC [2861]: [12-1] 
user=postgres,db=procured,app=psql,client=localhost DEBUG:  StartTransaction(1) 
name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
2018-06-20 19:06:27 UTC [2861]: [13-1] 
user=postgres,db=procured,app=psql,client=localhost DEBUG:  building index 
"pg_toast_49152_index" on table "pg_toast_49152"
2018-06-20 19:06:27 UTC [2861]: [14-1] 
user=postgres,db=procured,app=psql,client=localhost DEBUG:  no icu dir
2018-06-20 19:06:27 UTC [2861]: [15-1] 
user=postgres,db=procured,app=psql,client=localhost CONTEXT:  PL/pgSQL function 
histogram_merge(histogram,histogram) line 3 at RETURN
2018-06-20 19:06:27 UTC [1955]: [236-1] user=,db=,app=,client= DEBUG:  reaping 
dead processes
2018-06-20 19:06:27 UTC [1955]: [237-1] user=,db=,app=,client= DEBUG:  server 
process (PID 2861) was terminated by signal 11: Segmentation fault
2018-06-20 19:06:27 UTC [1955]: [238-1] user=,db=,app=,client= DETAIL:  Failed 
process was running: CREATE MATERIALIZED VIEW mat_view_by_product AS
SELECT
facility_alias_id,
group_type_id,
product_id,
po_date_month,histogram_agg(histogram) AS histogram,
sum(total_spend) AS total_spend,
min(min_price) AS min_price,
max(max_price) AS max_price,
min(min_po_date) AS min_po_date,
max(max_po_date) AS max_po_date,
(array_agg(most_recent_price ORDER BY max_po_date))[1] AS 
most_recent_price,
sum(total_eaches) AS total_eaches
FROM
mat_view_by_catalog
GROUP BY
facility_alias_id,
group_type_id,
product_id,
po_date_month;
2018-06-20 19:06:27 UTC [1955]: [239-1] user=,db=,app=,client= LOG:  server 
process (PID 2861) was terminated by signal 11: Segmentation fault
2018-06-20 19:06:27 UTC [1955]: [240-1] user=,db=,app=,client= DETAIL:  Failed 
process was running: CREATE MATERIALIZED VIEW mat_view_by_product AS
SELECT
facility_alias_id,
group_type_id,
product_id,
po_date_month,histogram_agg(histogram) AS histogram,
sum(total_spend) AS total_spend,
min(min_price)

Re: Is postorder tree traversal possible with recursive CTE's?

[Hellmuth Vargas]

Hi
It may not be the most elegant solution butworks!


with recursive pizza (name, step, ingredient, quantity, unit, rel_qty,
path, weight)
as (
select
name, step, ingredient, quantity, unit
,   quantity::numeric(10,2)
,   step::text
,   case when unit = 'g' then quantity::numeric(10,2) else null
end
  from recipe
 where name = 'pizza'
union all
select
recipe.name, recipe.step, recipe.ingredient,
recipe.quantity, recipe.unit
,   (pizza.rel_qty * recipe.quantity)::numeric(10,2)
,   pizza.path || '.' || recipe.step
,   case when recipe.unit = 'g' then (pizza.rel_qty *
recipe.quantity)::numeric(10,2) else null end
  from pizza
  join recipe on (recipe.name = pizza.ingredient)
), parcial_weight as
(
select a.path,sum(b.weight) as sum_weight
from pizza as a cross join pizza as b
where b.path ilike a.path || '%'
group by 1
order by path

)
select a.path, a.ingredient, a.quantity, a.rel_qty, a.unit,
a.weight,b.sum_weight as partial_ weigh,sum(a.weight) over() as total_weight
  from pizza as a
  left join parcial_weight as b on a.path=b.path
 order by a.path;



 path  |  ingredient  | quantity | rel_qty | unit  | weight |
partial_weight | total_weight
---+--+--+-+---+++--
 1 | tomato sauce | 1.00 |1.00 | pcs   || 113.00 |
 315.50
 1.1   | tomato   |   100.00 |  100.00 | g | 100.00 | 100.00 |
 315.50
 1.2   | basil|10.00 |   10.00 | g |  10.00 |  10.00 |
 315.50
 1.3   | salt | 3.00 |3.00 | g |   3.00 |   3.00 |
 315.50
 2 | pizza bottom | 1.00 |1.00 | pcs   || 202.50 |
 315.50
 2.2   | dough| 1.00 |1.00 | pcs   || 200.00 |
 315.50
 2.2.1 | flour|   150.00 |  150.00 | g | 150.00 | 150.00 |
 315.50
 2.2.2 | water|50.00 |   50.00 | g |  50.00 |  50.00 |
 315.50
 2.2.3 | salt | 1.00 |1.00 | pinch |||
 315.50
 2.3   | carbon   | 2.50 |2.50 | g |   2.50 |   2.50 |
 315.50
(10 rows)




El mié., 20 de jun. de 2018 a la(s) 10:54, Alban Hertroys (
haram...@gmail.com) escribió:

> On 19 June 2018 at 21:14, Hellmuth Vargas  wrote:
> >
> > Hi
> >
> > with partial sum:
> >
> > with recursive pizza (name, step, ingredient, quantity, unit, rel_qty,
> path,
> > weight)
> > as (
> > select
> > name, step, ingredient, quantity, unit
> > ,   quantity::numeric(10,2)
> > ,   step::text
> > ,   case when unit = 'g' then quantity::numeric(10,2) else
> null
> > end
> >   from recipe
> >  where name = 'pizza'
> > union all
> > select
> > recipe.name, recipe.step, recipe.ingredient,
> > recipe.quantity, recipe.unit
> > ,   (pizza.rel_qty * recipe.quantity)::numeric(10,2)
> > ,   pizza.path || '.' || recipe.step
> > ,   case when recipe.unit = 'g' then (pizza.rel_qty *
> > recipe.quantity)::numeric(10,2) else null end
> >   from pizza
> >   join recipe on (recipe.name = pizza.ingredient)
> > )
> > select path, ingredient, quantity, rel_qty, unit, weight,sum(weight)
> > over(partition by split_part(path,'.',1)) as parcial_weight, sum(weight)
> > over() as total_weight
> >   from pizza
> >  order by path;
> >
> >  path  |  ingredient  | quantity | rel_qty | unit  | weight |
> parcial_weight
> > | total_weight
> >
> ---+--+--+-+---+++--
> >  1 | tomato sauce | 1.00 |1.00 | pcs   ||
>  113.00
> > |   313.00
> >  1.1   | tomato   |   100.00 |  100.00 | g | 100.00 |
>  113.00
> > |   313.00
> >  1.2   | basil|10.00 |   10.00 | g |  10.00 |
>  113.00
> > |   313.00
> >  1.3   | salt | 3.00 |3.00 | g |   3.00 |
>  113.00
> > |   313.00
> >  2 | pizza bottom | 1.00 |1.00 | pcs   ||
>  200.00
> > |   313.00
> >  2.2   | dough| 1.00 |1.00 | pcs   ||
>  200.00
> > |   313.00
> >  2.2.1 | flour|   150.00 |  150.00 | g | 150.00 |
>  200.00
> > |   313.00
> >  2.2.2 | water|50.00 |   50.00 | g |  50.00 |
>  200.00
> > |   313.00
> >  2.2.3 | salt | 1.00 |1.00 | pinch ||
>  200.00
> > |   313.00
> > (9 rows)
>
> I just realized that this solution contains a problem. It only adds
> weights at the top-level of the ingredients.
> That works for this particular example, but not if, for example, a
> 'pizza bottom' contains 200.00g of 'dough' and 2.50g of 'carbon'.
>
> The correct values in that case would be:
> 2: Pizza Bottom: 150 + 50 + 2.50 = 202.50g

Trouble matching a nested value in JSONB entries

[Enrico Thierbach]

Hi list,

I have some trouble matching a value in a JSONB object against multiple 
potential matches.


Lets say, I have a table with an id, and a metadata JSONB column, which 
holds data like the following


1 | {"group_id": 1}
2 | {“group_id": 1}
3 | {“group_id": 2}
4 | {“group_id": 3}

I would like to run a query which gives me the result of `SELECT id FROM 
mytable WHERE metadata->>’group_id’ IN (1,2)`. Now, obviously I 
could use this query, but I
would like to get away without an explicit index on 
`metadata->>’group_id’`, and I was hoping to find something using 
the JSONB containment operators, with support

of a gist or gin index.

The following seems to work

select * from mytable where (metadata @> '{"group_id":1}')

but only with a single value to match.

I could, of course, also “denormalize” the query a la

select * from mytable where (metadata @> '{"group_id":1}') OR 
(metadata @> '{"group_id”:2}’)


but this seems to call for long execution times; also, depending on the 
number of different tag names and values to match this could really 
explode into quite a

large query.

Stackoverflow suggests the use of ANY

select * from mytable where (tags->'group_id' @> ANY( ARRAY 
['1','3']::jsonb[] ) );


https://dba.stackexchange.com/questions/130699/postgresql-json-query-array-against-multiple-values

This seems to work - but doesn’t that require a group_id specific 
index again?


Anything I overlooked?

Best,
/eno

PS: Please note that I am currently at postgres 9.5. An update, if 
necessary, would be possible though.


--
me at github: https://github.com/radiospiel
me at linked.in: https://www.linkedin.com/in/radiospiel

Re: Is there a way to be notified on the CREATE TABLE execution?

[Melvin Davidson]

On Wed, Jun 20, 2018 at 1:28 PM, Francisco Olarte 
wrote:

> Melvin:
>
> Maybe old eyes, but ...
>
> On Wed, Jun 20, 2018 at 7:03 PM, Melvin Davidson 
> wrote:
> >>Is there a way to be notified on the CREATE TABLE execution?
> > Here is sample code that will notify for a CREATE or DROP table:
>
> Doesn't this trigger just log the events? I think it's missing
> something like "Notify create_or_drop" somewhere after the logging (
> so a listener knows to look at the log table ).
>
> ( Normally I would use something like this, listen to a channel, do a
> first scan from the saved last tbl_cl_key, and then do another scan
> from the last each time listen fires, it seems the difficult work is
> done but it misses the notify to signal listeners and avoid having to
> rescan on a timer or a similar thing ).
>
> ( Maybe I missed the line, it would not be the first time, that's why I
> ask ).
>
> Francisco Olarte.
>

> Maybe old eyes, but ...
>I think it's missing
>something like "Notify create_or_drop" somewhere after the logging (
>so a listener knows to look at the log table ).

Uh, I said it was a SAMPLE. It's not that difficult to add RAISE INFO... or
NOTIFY...
which, if you look, is commented out with generic information..
Of course, the exact message is left to the creator.

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

Re: Is there a way to be notified on the CREATE TABLE execution?

[Francisco Olarte]

Melvin:

Maybe old eyes, but ...

On Wed, Jun 20, 2018 at 7:03 PM, Melvin Davidson  wrote:
>>Is there a way to be notified on the CREATE TABLE execution?
> Here is sample code that will notify for a CREATE or DROP table:

Doesn't this trigger just log the events? I think it's missing
something like "Notify create_or_drop" somewhere after the logging (
so a listener knows to look at the log table ).

( Normally I would use something like this, listen to a channel, do a
first scan from the saved last tbl_cl_key, and then do another scan
from the last each time listen fires, it seems the difficult work is
done but it misses the notify to signal listeners and avoid having to
rescan on a timer or a similar thing ).

( Maybe I missed the line, it would not be the first time, that's why I ask ).

Francisco Olarte.

Re: Is there a way to be notified on the CREATE TABLE execution?

[Francisco Olarte]

Igor:

On Wed, Jun 20, 2018 at 7:07 PM, Igor Korot  wrote:
> Just one more question:
> This page https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
> does not reference
> Windows/MSVC/MinGW as a way to compile the code.

Sorry, I don't do windows.

You do not need C extension functions anyway, unless your usage
pattern is truly bizarre a triger for ddl could be written in any pl.

Francisco Olarte.

Re: Is there a way to be notified on the CREATE TABLE execution?

[Igor Korot]

Thx, Francisco.
It makes sense now.

Just one more question:

This page https://www.postgresql.org/docs/current/static/xfunc-c.html#DFUNC
does not reference
Windows/MSVC/MinGW as a way to compile the code.

How should I do it?

Thx.


On Wed, Jun 20, 2018 at 11:44 AM, Francisco Olarte
 wrote:
> On Wed, Jun 20, 2018 at 5:20 PM, Igor Korot  wrote:
>>>  https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>>> Your scenario suggests you may wish to avail yourself of the Listen and
>>> Notify commands as well.
>>
>> I did look at the Listen/Notify.
>> Unfortunately the listening is done on the channel versus listening
>> for the specific event.
>
> Channels are cheap. You just listen on "whatever" and in the event
> trigger you notify "whatever", payload is optional.
>
> The event trigger is the one which takes care of filtering the event
> and notifying selectively.
>
> You can use a channel per event.
>
> Francisco Olarte.

Re: Is there a way to be notified on the CREATE TABLE execution?

[Melvin Davidson]

>Is there a way to be notified on the CREATE TABLE execution?

Here is sample code that will notify for a CREATE or DROP table:

CREATE TABLE public.tbl_create_log
(
  tbl_cl_key bigint NOT NULL DEFAULT
nextval('tbl_create_log_tbl_cl_key_seq'::regclass),
  tbl_cre8_time timestamp without time zone DEFAULT now(),
  log_table_schema name,
  log_table_name name,
  log_session_user name,
  CONSTRAINT tbl_create_log_pk PRIMARY KEY (tbl_cl_key)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.tbl_create_log
  OWNER TO postgres;
GRANT ALL ON TABLE public.tbl_create_log TO postgres;


CREATE OR REPLACE FUNCTION public.fn_notify_ddl()
  RETURNS event_trigger AS
$BODY$
DECLARE
obj record;
BEGIN
/*
RAISE INFO 'Type: %', TG_TAG;
RAISE INFO 'Command: %', current_query();
RAISE INFO 'DB Name: %', current_database();
RAISE INFO 'DB User: %', session_user;
RAISE INFO 'DB Port: %', inet_server_port();
RAISE INFO 'Server Host: %', inet_server_addr();
RAISE INFO 'Client Host: %', inet_client_addr();
*/
FOR obj IN SELECT *
 FROM pg_event_trigger_ddl_commands() LOOP
IF obj.command_tag = 'CREATE TABLE'
OR obj.command_tag = 'DROP TABLE'THEN
--  RAISE INFO 'we got a % event for object "%"', obj.command_tag,
obj.object_identity;
  INSERT INTO tbl_create_log
  ( log_table_schema,
log_table_name,
log_session_user
  )
  SELECT n.nspname,
 c.relname,
 session_user
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE c.oid = obj.objid
 AND c.relkind = 'r';
END IF;
END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.fn_notify_ddl()
  OWNER TO postgres;

CREATE EVENT TRIGGER table_created_dropped ON DDL_COMMAND_END
EXECUTE PROCEDURE public.fn_notify_ddl();

ALTER EVENT TRIGGER table_created_dropped
OWNER TO postgres;

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

Re: Is there a way to be notified on the CREATE TABLE execution?

[Francisco Olarte]

On Wed, Jun 20, 2018 at 5:20 PM, Igor Korot  wrote:
>>  https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>> Your scenario suggests you may wish to avail yourself of the Listen and
>> Notify commands as well.
>
> I did look at the Listen/Notify.
> Unfortunately the listening is done on the channel versus listening
> for the specific event.

Channels are cheap. You just listen on "whatever" and in the event
trigger you notify "whatever", payload is optional.

The event trigger is the one which takes care of filtering the event
and notifying selectively.

You can use a channel per event.

Francisco Olarte.

Re: Is postorder tree traversal possible with recursive CTE's?

[Alban Hertroys]

On 19 June 2018 at 21:14, Hellmuth Vargas  wrote:
>
> Hi
>
> with partial sum:
>
> with recursive pizza (name, step, ingredient, quantity, unit, rel_qty, path,
> weight)
> as (
> select
> name, step, ingredient, quantity, unit
> ,   quantity::numeric(10,2)
> ,   step::text
> ,   case when unit = 'g' then quantity::numeric(10,2) else null
> end
>   from recipe
>  where name = 'pizza'
> union all
> select
> recipe.name, recipe.step, recipe.ingredient,
> recipe.quantity, recipe.unit
> ,   (pizza.rel_qty * recipe.quantity)::numeric(10,2)
> ,   pizza.path || '.' || recipe.step
> ,   case when recipe.unit = 'g' then (pizza.rel_qty *
> recipe.quantity)::numeric(10,2) else null end
>   from pizza
>   join recipe on (recipe.name = pizza.ingredient)
> )
> select path, ingredient, quantity, rel_qty, unit, weight,sum(weight)
> over(partition by split_part(path,'.',1)) as parcial_weight, sum(weight)
> over() as total_weight
>   from pizza
>  order by path;
>
>  path  |  ingredient  | quantity | rel_qty | unit  | weight | parcial_weight
> | total_weight
> ---+--+--+-+---+++--
>  1 | tomato sauce | 1.00 |1.00 | pcs   || 113.00
> |   313.00
>  1.1   | tomato   |   100.00 |  100.00 | g | 100.00 | 113.00
> |   313.00
>  1.2   | basil|10.00 |   10.00 | g |  10.00 | 113.00
> |   313.00
>  1.3   | salt | 3.00 |3.00 | g |   3.00 | 113.00
> |   313.00
>  2 | pizza bottom | 1.00 |1.00 | pcs   || 200.00
> |   313.00
>  2.2   | dough| 1.00 |1.00 | pcs   || 200.00
> |   313.00
>  2.2.1 | flour|   150.00 |  150.00 | g | 150.00 | 200.00
> |   313.00
>  2.2.2 | water|50.00 |   50.00 | g |  50.00 | 200.00
> |   313.00
>  2.2.3 | salt | 1.00 |1.00 | pinch || 200.00
> |   313.00
> (9 rows)

I just realized that this solution contains a problem. It only adds
weights at the top-level of the ingredients.
That works for this particular example, but not if, for example, a
'pizza bottom' contains 200.00g of 'dough' and 2.50g of 'carbon'.

The correct values in that case would be:
2: Pizza Bottom: 150 + 50 + 2.50 = 202.50g
2.2: Dough: 150 + 50 = 200 g
2.2.1: flour: 150 g
2.2.2: water: 50 g
2.2.3: salt: (null) g
2.3: Carbon: 2.50 g

What probably would work is to keep the path in an array and track the
(cumulative) sum at each depth in the path in another array. After
that, we can take the MAX of each array element using a similar window
function as in the original post, I think. The level of the tree would
then be the index into the array.

...Let's look at that tomorrow, before my head explodes ;)

> El mar., 19 de jun. de 2018 a la(s) 11:49, Hellmuth Vargas
> (hiv...@gmail.com) escribió:
>>
>> Hi
>>
>> with recursive pizza (name, step, ingredient, quantity, unit, rel_qty,
>> path, weight)
>> as (
>> select
>> name, step, ingredient, quantity, unit
>> ,   quantity::numeric(10,2)
>> ,   step::text
>> ,   case when unit = 'g' then quantity::numeric(10,2) else
>> null end
>>   from recipe
>>  where name = 'pizza'
>> union all
>> select
>> recipe.name, recipe.step, recipe.ingredient,
>> recipe.quantity, recipe.unit
>> ,   (pizza.rel_qty * recipe.quantity)::numeric(10,2)
>> ,   pizza.path || '.' || recipe.step
>> ,   case when recipe.unit = 'g' then (pizza.rel_qty *
>> recipe.quantity)::numeric(10,2) else null end
>>   from pizza
>>   join recipe on (recipe.name = pizza.ingredient)
>> )
>> select path, ingredient, quantity, rel_qty, unit, weight, sum(weight)
>> over() as total_weight
>>   from pizza
>>  order by path;
>>
>>  path  |  ingredient  | quantity | rel_qty | unit  | weight | total_weight
>>
>> ---+--+--+-+---++--
>>  1 | tomato sauce | 1.00 |1.00 | pcs   ||   313.00
>>  1.1   | tomato   |   100.00 |  100.00 | g | 100.00 |   313.00
>>  1.2   | basil|10.00 |   10.00 | g |  10.00 |   313.00
>>  1.3   | salt | 3.00 |3.00 | g |   3.00 |   313.00
>>  2 | pizza bottom | 1.00 |1.00 | pcs   ||   313.00
>>  2.2   | dough| 1.00 |1.00 | pcs   ||   313.00
>>  2.2.1 | flour|   150.00 |  150.00 | g | 150.00 |   313.00
>>  2.2.2 | water|50.00 |   50.00 | g |  50.00 |   313.00
>>  2.2.3 | salt | 1.00 |1.00 | pinch ||   313.00
>> (9 rows)
>>
>>
>>
>>
>>
>> El mar

Re: tsvector field length limitation

[Jonathan Marks]

What if we just didn’t use positional arguments at all? I.e. we just populate 
the tsvector with lexemes only?

> On Jun 20, 2018, at 10:49 AM, Tom Lane  wrote:
> 
> Jonathan Marks  writes:
>> ... we run into the max tsvector length requirement "The length of a 
>> tsvector (lexemes + positions) must be less than 1 megabyte”
> 
>> Is there any way to disable or increase that limit in Postgres 10.3?
> 
> No; it's forced by the representation used for tsvector, which stores
> lexeme offsets in 20-bit fields (cf WordEntry in
> src/include/tsearch/ts_type.h).  Perhaps that was short-sighted but
> I don't foresee it changing anytime soon.  You'd more or less need
> a whole new datatype ("bigtsvector"?) to make it happen.
> 
>   regards, tom lane

Re: Is there a way to be notified on the CREATE TABLE execution?

[Igor Korot]

Hi, David,

On Tue, Jun 19, 2018 at 5:13 PM, David G. Johnston
 wrote:
> On Tuesday, June 19, 2018, Igor Korot  wrote:
>>
>> Hi, ALL,
>> Consider a scenario:
>>
>> 1. A software that uses libpq is executing.
>> 2. Someone opens up a terminal and creates a table.
>> 3. A software needs to know about this new table.
>
>
> I'd start here:
>
>  https://www.postgresql.org/docs/10/static/sql-createeventtrigger.html
>
> Your scenario suggests you may wish to avail yourself of the Listen and
> Notify commands as well.

I did look at the Listen/Notify.
Unfortunately the listening is done on the channel versus listening
for the specific event.

I also looked at the
https://www.postgresql.org/docs/9.1/static/libpq-example.html#LIBPQ-EXAMPLE-2,
but am not sure how to create an appropriate event.

Thank you.

>
> David J.
>
>
>

Re: tsvector field length limitation

[Tom Lane]

Jonathan Marks  writes:
> ... we run into the max tsvector length requirement "The length of a tsvector 
> (lexemes + positions) must be less than 1 megabyte”

> Is there any way to disable or increase that limit in Postgres 10.3?

No; it's forced by the representation used for tsvector, which stores
lexeme offsets in 20-bit fields (cf WordEntry in
src/include/tsearch/ts_type.h).  Perhaps that was short-sighted but
I don't foresee it changing anytime soon.  You'd more or less need
a whole new datatype ("bigtsvector"?) to make it happen.

regards, tom lane

Re: Plan output: actual execution time not considering loops?

[Thomas Kellerer]

Tom Lane schrieb am 20.06.2018 um 16:03:
>> Consider the following execution plan:
>> ...
>>     ->  Aggregate  (cost=26.87..26.87 rows=1 width=32) (actual 
>> time=0.012..0.012 rows=1 loops=70)
>>   ->  Bitmap Heap Scan on orders o2  (cost=3.45..26.85 rows=8 
>> width=8) (actual time=0.004..0.008 rows=8 loops=70)
>>     ->  Bitmap Index Scan on orders_customer_id_order_date_idx  
>> (cost=0.00..3.45 rows=8 width=0) (actual time=0.003..0.003 rows=8 
>> loops=70)
> 
>> My expectation would have been that the "Aggregate" step shows the actual 
>> time as a product of the number of loops.
> 
> No, that looks fine to me.  The rule of thumb for reading this is total
> time spent in/below this node is "actual time" times "number of loops".

OK, if that is the rule I can live with that ;)

> It seems a bit odd that the Agg node would account for a third of the
> total execution time when it's only processing 8 rows on average ...
> but maybe it's a really expensive aggregate.

But it's processing those 8 rows 700.000 times - so the total time seems 
correct. 

FWIW, the query looks like this:

select customer_id, 
   amount, 
   sales_person_id
from orders o1
where amount = (select max(o2.amount)
from orders o2
where o2.customer_id = o1.customer_id);

It's not a real world query - it's just there to illustrate the drawbacks of 
co-related sub-queries.
 
> Another thought is that the EXPLAIN ANALYZE instrumentation itself
> can account for significant per-node-invocation overhead.  If the
> total execution time drops significantly when you add "timing off"
> to the EXPLAIN options, then that's probably a factor in making
> the Agg node look relatively expensive.

"timing off" doesn't really change the execution time (it's about 60ms faster 
without)

Thanks for the answer, I am not really concerned about the query performance 
itself, just about the plan ;) 

Thomas

tsvector field length limitation

[Jonathan Marks]

Hi folks —

We utilize Postgres’ full text search system pretty heavily in our team’s 
operations and often index tens of millions of records with varying lengths of 
text. In most cases, the text we need to index is pretty short (no more than. 
hundreds of words) but in rare cases a single record is very very long (high 
hundreds of thousands of words or longer). With those records, we run into the 
max tsvector length requirement "The length of a tsvector (lexemes + positions) 
must be less than 1 megabyte”

I understand the performance implications of having very long tsvectors (our 
gin index updates are pretty terrible in some cases) but would really 
appreciate it if the max tsvector length were larger (even 5MB would make a 
huge difference) or if that error were a stern warning rather than a hard error.

Is there any way to disable or increase that limit in Postgres 10.3? Perhaps in 
a future version?

Thank you!
Jonathan

Re: Using DSN Connection and knowing windows username

[Łukasz Jarych]

David G,

thank you.
Can you confirm if i am thinking correctly ?

So I can set up authetification to know which user is logged on and use
this as postgresql user?

But i think it will be not possible to use DSN connection with this.

Best ,
Luke

2018-06-20 15:34 GMT+02:00 David G. Johnston :

> On Wednesday, June 20, 2018, Łukasz Jarych  wrote:
>>
>> How to know in postgresql which specific windows user is using database?
>>
>
> You cannot.  All the server knows is the specific user credentials it is
> authenticating.
>
> That said you can authenticate those credentials in such a way so that
> knowing the signed on user you would also know who they are in any
> environment that uses the same authentication source - and if that source
> supplies their Windows identity you are golden. The specific setups
> involved here are outside my experience, though.
>
> David J.
>
>

Re: Plan output: actual execution time not considering loops?

[Tom Lane]

Thomas Kellerer  writes:
> Consider the following execution plan:
> ...
>     ->  Aggregate  (cost=26.87..26.87 rows=1 width=32) (actual 
> time=0.012..0.012 rows=1 loops=70)
>   ->  Bitmap Heap Scan on orders o2  (cost=3.45..26.85 rows=8 
> width=8) (actual time=0.004..0.008 rows=8 loops=70)
>     ->  Bitmap Index Scan on orders_customer_id_order_date_idx  
> (cost=0.00..3.45 rows=8 width=0) (actual time=0.003..0.003 rows=8 
> loops=70)

> My expectation would have been that the "Aggregate" step shows the actual 
> time as a product of the number of loops.

No, that looks fine to me.  The rule of thumb for reading this is total
time spent in/below this node is "actual time" times "number of loops".

It seems a bit odd that the Agg node would account for a third of the
total execution time when it's only processing 8 rows on average ...
but maybe it's a really expensive aggregate.

Another thought is that the EXPLAIN ANALYZE instrumentation itself
can account for significant per-node-invocation overhead.  If the
total execution time drops significantly when you add "timing off"
to the EXPLAIN options, then that's probably a factor in making
the Agg node look relatively expensive.

regards, tom lane

Re: Load data from a csv file without using COPY

[Adrian Klaver]

On 06/19/2018 04:20 PM, Ravi Krishna wrote:
Thanks all for replying.  I see that I did not explain my requirement in 
detail.  So let me

explain it in detail.

1. Currently we have a legacy app running in DB2/LUW. Application writes 
to it either via Java program

     or uses a custom ETL scripts using a vendor product.
2. We want to migrate it to DB2 and eliminate vendor ETL tool.
3. We now have a catch-22 situation.  Should we spend time porting the 
app to PG without first verifying
     that PG can perform as well as DB2. In other words, if some sort of 
testing rules out PG as a good
     replacement for DB2, why even bother to port.  Of course that does 
not prove conclusively that if PG
     passes the test, then it would mean that the app will work just as 
fine.  But at least basic test will tell

    that we are not on a wrong path.
4. What I am planning is:
     4.a Get a set of large tables exported as a pipe delimited text file.
     4.b Load them in both DB2 and PG on a similar h/w
     4.c  Run OLAP queries.

4.b is to test i/o. Our app is sensitive to the load times and some of 
the tables are really wide.
4.c is to test maturity of PG in handling complex OLAP SQLs. From what I 
have read, while PG
      optimizer is very good in handling OLTP, it is not, as yet, as 
good in OLAP queries.


I just want to keep the testing tool same in 4.b for both db2 and pg. If 


To me, 4.b means find the tool that minimizes the load time in each 
database. This is not necessarily the same tool for each.



COPY is the only way,
we will use it with something comparable on the DB2 side.




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

Re: Using DSN Connection and knowing windows username

[David G. Johnston]

On Wednesday, June 20, 2018, Łukasz Jarych  wrote:
>
> How to know in postgresql which specific windows user is using database?
>

You cannot.  All the server knows is the specific user credentials it is
authenticating.

That said you can authenticate those credentials in such a way so that
knowing the signed on user you would also know who they are in any
environment that uses the same authentication source - and if that source
supplies their Windows identity you are golden. The specific setups
involved here are outside my experience, though.

David J.

Re: Suggestion about logging only every n-th statement

[Andreas Kretschmer]

On 20 June 2018 13:51:25 CEST, Janning Vygen  wrote:
>Back in 2009 I made a suggestion which is not implemented yet but would
>
>still be very valuable and easy to implement in my opinion (not for me 
>as I am not speaking C):
>
>https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%40kicktipp.de#200903161426.56662.vy...@kicktipp.de
>
>We still can't afford to log each and every statement as we have too 
>many and we don't want to afford more disk performance/space. Therefore
>
>we log only statements slower than 50ms.
>
>But for analyzing usage patterns it would be very nice to have this 
>combined with a sample_rate for logging.
>
>   logging_sample_rate = n
>
>So each n-th statement will get logged regardless of execution time.
>
>What do you think?
>
>regards
>Janning

I would suggest using of pg_stat_statements for that.

Regards, Andreas


-- 
2ndQuadrant - The PostgreSQL Support Company

Re: Suggestion about logging only every n-th statement

[Janning Vygen]

Am 20.06.2018 um 14:53 schrieb Adrien NAYRAT:

On 06/20/2018 01:51 PM, Janning Vygen wrote:
Back in 2009 I made a suggestion which is not implemented yet but 
would still be very valuable and easy to implement in my opinion (not 
for me as I am not speaking C):


https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%40kicktipp.de#200903161426.56662.vy...@kicktipp.de 



FYI in made this patch which seems do what you want :
https://www.postgresql.org/message-id/flat/c30ee535-ee1e-db9f-fa97-146b9f62caed%40anayrat.info#c30ee535-ee1e-db9f-fa97-146b9f62c...@anayrat.info 



I will add an entry in september's commit fest.


excellent. Exactly what I want!

regards
Janning

Re: Suggestion about logging only every n-th statement

[Adrien NAYRAT]

On 06/20/2018 01:51 PM, Janning Vygen wrote:
Back in 2009 I made a suggestion which is not implemented yet but would 
still be very valuable and easy to implement in my opinion (not for me 
as I am not speaking C):


https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%40kicktipp.de#200903161426.56662.vy...@kicktipp.de 



We still can't afford to log each and every statement as we have too 
many and we don't want to afford more disk performance/space. Therefore 
we log only statements slower than 50ms.


But for analyzing usage patterns it would be very nice to have this 
combined with a sample_rate for logging.


   logging_sample_rate = n

So each n-th statement will get logged regardless of execution time.

What do you think?

regards
Janning




Hello Janning,

FYI in made this patch which seems do what you want :
https://www.postgresql.org/message-id/flat/c30ee535-ee1e-db9f-fa97-146b9f62caed%40anayrat.info#c30ee535-ee1e-db9f-fa97-146b9f62c...@anayrat.info


I will add an entry in september's commit fest.

Regards,

Re: Suggestion about logging only every n-th statement

[Geoff Winkless]

On Wed, 20 Jun 2018 at 12:51, Janning Vygen  wrote:

> But for analyzing usage patterns it would be very nice to have this
> combined with a sample_rate for logging.
>
>logging_sample_rate = n
>
> So each n-th statement will get logged regardless of execution time.
>

I think you would need to introduce a randomizing element ​if you wanted to
make it statistically valid. Logging every n'th statement could (depending
on your usage pattern) be catastrophically inaccurate.

Geoff

Using DSN Connection and knowing windows username

[Łukasz Jarych]

Hi,

i am using windows server 2012 and connecting from Access FE to Postgresql
using DSN connection.

When i am using "select current_user;" in postgresql i have always user
"postgres" because it is my default user for database.

How to know in postgresql which specific windows user is using database?
How to solve this?

I tried to use method from here:

https://www.cafe-encounter.net/p2034/postgres-using-integrated-security-on-windows-on-localhost

but this is only to avoid inputing password by specific windows server
user.
(and this is not working also).

Please help,
Best,
Jacek

Suggestion about logging only every n-th statement

[Janning Vygen]

Back in 2009 I made a suggestion which is not implemented yet but would 
still be very valuable and easy to implement in my opinion (not for me 
as I am not speaking C):


https://www.postgresql.org/message-id/flat/200903161426.56662.vygen%40kicktipp.de#200903161426.56662.vy...@kicktipp.de

We still can't afford to log each and every statement as we have too 
many and we don't want to afford more disk performance/space. Therefore 
we log only statements slower than 50ms.


But for analyzing usage patterns it would be very nice to have this 
combined with a sample_rate for logging.


  logging_sample_rate = n

So each n-th statement will get logged regardless of execution time.

What do you think?

regards
Janning

Plan output: actual execution time not considering loops?

[Thomas Kellerer]

Consider the following execution plan:

Seq Scan on orders o1  (cost=0.00..18818840.86 rows=3500 width=16) (actual 
time=0.033..8625.104 rows=99906 loops=1)
  Filter: (amount = (SubPlan 1))
  Rows Removed by Filter: 600094
  Buffers: shared hit=7719778
  SubPlan 1
    ->  Aggregate  (cost=26.87..26.87 rows=1 width=32) (actual 
time=0.012..0.012 rows=1 loops=70)
  Buffers: shared hit=7714631
  ->  Bitmap Heap Scan on orders o2  (cost=3.45..26.85 rows=8 width=8) 
(actual time=0.004..0.008 rows=8 loops=70)
    Recheck Cond: (customer_id = o1.customer_id)
    Heap Blocks: exact=5597311
    Buffers: shared hit=7714631
    ->  Bitmap Index Scan on orders_customer_id_order_date_idx  
(cost=0.00..3.45 rows=8 width=0) (actual time=0.003..0.003 rows=8 loops=70)
  Index Cond: (customer_id = o1.customer_id)
  Buffers: shared hit=2117320
Planning time: 0.136 ms
Execution time: 8628.724 ms

My expectation would have been that the "Aggregate" step shows the actual time 
as a product of the number of loops.

The Bitmap Heap Scan takes 0.008ms for each execution, so shouldn't the "actual 
time" for the "Aggregate" step be 0.008ms * 70 (= 5600ms)?

The plan was generated using Postgres 10.4 (on Windows 10, but I think that is 
irrelevant)

Thomas

Re: PostgreSQL Volume Question

[Thomas Kellerer]

Data Ace schrieb am 15.06.2018 um 18:26:
> Well I think my question is somewhat away from my intention cause of
> my poor understanding and questioning :(
> 
> Actually, I have 1TB data and have hardware spec enough to handle
> this amount of data, but the problem is that it needs too many join
> operations and the analysis process is going too slow right now.
> 
> I've searched and found that graph model nicely fits for network data
> like social data in query performance.
> 
> Should I change my DB (I mean my DB for analysis)? or do I need some
> other solutions or any extension?


AgensGraph is a Postgres fork implemententing a graph database supporting 
Cypher as the query language while at the same time still supporting SQL 
(and even queries mixing both)

I have never used it, but maybe it's worth a try.

http://bitnine.net/agensgraph/

Thomas

Re: using pg_basebackup for point in time recovery

[Pierre Timmermans]

Hi Michael
Thanks for the confirmation. Your rewording removes the confusion. I would 
maybe take the opportunity to re-instate that pg_dump cannot be used for PITR, 
so in the line of
"These are backups that could be used for point-in-time recovery if
combined with a WAL archive able to recover up to the wanted recovery
point.  These backups are typically much faster to backup and restore
than pg_dump for large deployments but can result as well in larger
backup sizes, so the speed of one method or the other is to evaluate
carefully first. Consider also that pg_dump backups cannot be used for 
point-in-time recovery."

 Maybe the confusion stems from the fact that if you restore a standalone 
(self-contained) pg_basebackup then - by default - recovery is done with the 
recovery_target immediate option, so if one needs point-in-time recovery he has 
to edit the recovery.conf and brings the archives..

Thanks and regards, 
Pierre 

On Wednesday, June 20, 2018, 5:38:56 AM GMT+2, Michael Paquier 
 wrote:  
 
 Hi Pierre,

On Tue, Jun 19, 2018 at 12:03:58PM +, Pierre Timmermans wrote:
> Here is the doc, the sentence that I find misleading is "There are
> backups that cannot be used for point-in-time recovery", also
> mentioning that they are faster than pg_dumps add to confusion (since
> pg_dumps cannot be used for PITR):
> https://www.postgresql.org/docs/current/static/continuous-archiving.html

Yes, it is indeed perfectly possible to use such backups to do a PITR
as long as you have a WAL archive able to replay up to the point where
you want the replay to happen, so I agree that this is a bit confusing.
This part of the documentation is here since the beginning of times,
well 6559c4a2 to be exact.  Perhaps we would want to reword this
sentence as follows:
"These are backups that could be used for point-in-time recovery if
combined with a WAL archive able to recover up to the wanted recovery
point.  These backups are typically much faster to backup and restore
than pg_dump for large deployments but can result as well in larger
backup sizes, so the speed of one method or the other is to evaluate
carefully first."

I am open to better suggestions of course.
--
Michael
  

Re: Is there a way to be notified on the CREATE TABLE execution?

[Pierre Timmermans]

I believe you could use an event trigger in postgres to capture the fact that a 
table was created: 
https://www.postgresql.org/docs/current/static/event-triggers.html
In the trigger you would then have to code whatever is needed to notify the 
external software (via a REST call or by posting something in a messaging bus, 
...)


Regards, Pierre
 

On Wednesday, June 20, 2018, 12:08:48 AM GMT+2, Igor Korot 
 wrote:  
 
 Hi, ALL,
Consider a scenario:

1. A software that uses libpq is executing.
2. Someone opens up a terminal and creates a table.
3. A software needs to know about this new table.

I presume this is a DBMS-specific...

Thank you.