how to restrict dba to access sensitive data

[Patrizio Bassi]

Hi All,

i took a look into docs but could not find if pgsql has anything similar to
Oracle Database Vault ( for instance cfr.
https://www.oracle.com/technetwork/database/options/database-vault/overview/ds-security-dv-12cr2-3493647.pdf
) to restrict high privileged accounts from accessing tables with sensitive
data. Encryption is not an option unfortunately.

Thank you
Patrizio

Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Nicola Contu]

Here is the strace as requested for pg11

Thanks

Il giorno mar 5 mar 2019 alle ore 17:47 Thomas Munro 
ha scritto:

> On Wed, Mar 6, 2019 at 4:22 AM Nicola Contu 
> wrote:
> >
> > Not sure what you are requesting exactly but here is the strace for the
> start of the pg_ctl
>
> I meant that you could run the server itself in the foreground under
> strace, like so:
>
> $ strace -f -c /usr/local/pgsql11.2/bin/postgres -D /db/pgsql11/data/
>
> Then perform your testing, and finally stop it with pg_ctl from
> another window (or hit ^C in this window) and strace should spit out a
> table of system calls with some counters.  We might be able to see why
> v11 is spending so much more time executing system calls than v10 for
> your workload, or at least which systems calls they are, assuming you
> run the same transactions against both versions.
>
> --
> Thomas Munro
> https://enterprisedb.com
>
[postgres@STAGING-CMD1 ~]$  strace -f -c /usr/local/pgsql11.2/bin/postgres -D 
/db/pgsql11/data/
2019-03-06 10:07:47 GMT [] [163892]: [1-1] db=,user= LOG:  listening on IPv4 
address "0.0.0.0", port 5432
2019-03-06 10:07:47 GMT [] [163892]: [2-1] db=,user= LOG:  listening on IPv6 
address "::", port 5432
2019-03-06 10:07:47 GMT [] [163892]: [3-1] db=,user= LOG:  listening on Unix 
socket "/tmp/.s.PGSQL.5432"
strace: Process 163894 attached
2019-03-06 10:07:48 GMT [] [163894]: [1-1] db=,user= LOG:  database system was 
shut down at 2019-03-06 10:06:22 GMT
strace: Process 163895 attached
strace: Process 163896 attached
strace: Process 163897 attached
strace: Process 163898 attached
strace: Process 163899 attached
strace: Process 163900 attached
2019-03-06 10:07:48 GMT [] [163892]: [4-1] db=,user= LOG:  database system is 
ready to accept connections
strace: Process 163908 attached
strace: Process 163915 attached
strace: Process 163928 attached
strace: Process 163931 attached
strace: Process 163943 attached
strace: Process 163960 attached
strace: Process 163976 attached
2019-03-06 10:09:08 GMT [] [163895]: [1-1] db=,user= LOG:  checkpoint starting: 
xlog
strace: Process 163989 attached
strace: Process 164002 attached
2019-03-06 10:09:42 GMT [] [163895]: [2-1] db=,user= LOG:  checkpoint complete: 
wrote 201350 buffers (15.4%); 0 WAL file(s) added, 0 removed, 0 recycled; 
write=33.879 s, sync=0.208 s, total=34.093 s; sync files=30, longest=0.142 s, 
average=0.006 s; distance=1228942 kB, estimate=1228942 kB
strace: Process 164015 attached
2019-03-06 10:09:58 GMT [] [163895]: [3-1] db=,user= LOG:  checkpoint starting: 
xlog
strace: Process 164035 attached
strace: Process 164050 attached
2019-03-06 10:10:26 GMT [] [163895]: [4-1] db=,user= LOG:  checkpoint complete: 
wrote 126477 buffers (9.6%); 0 WAL file(s) added, 0 removed, 0 recycled; 
write=27.650 s, sync=0.015 s, total=27.668 s; sync files=30, longest=0.004 s, 
average=0.000 s; distance=1226813 kB, estimate=1228729 kB
strace: Process 164066 attached
2019-03-06 10:10:43 GMT [] [163895]: [5-1] db=,user= LOG:  checkpoint starting: 
xlog
strace: Process 164079 attached
strace: Process 164102 attached
2019-03-06 10:11:08 GMT [] [163895]: [6-1] db=,user= LOG:  checkpoint complete: 
wrote 125741 buffers (9.6%); 1 WAL file(s) added, 0 removed, 0 recycled; 
write=24.804 s, sync=0.077 s, total=24.947 s; sync files=30, longest=0.015 s, 
average=0.002 s; distance=1229772 kB, estimate=1229772 kB
strace: Process 164223 attached
2019-03-06 10:11:22 GMT [] [163895]: [7-1] db=,user= LOG:  checkpoint starting: 
xlog
strace: Process 164347 attached
2019-03-06 10:11:46 GMT [] [163895]: [8-1] db=,user= LOG:  checkpoint complete: 
wrote 133784 buffers (10.2%); 1 WAL file(s) added, 0 removed, 0 recycled; 
write=23.399 s, sync=0.141 s, total=23.595 s; sync files=29, longest=0.027 s, 
average=0.004 s; distance=1227832 kB, estimate=1229578 kB
strace: Process 164460 attached
2019-03-06 10:11:52 GMT [[local]] [163931]: [1-1] db=cmdstaging,user=postgres 
LOG:  duration: 209593.079 ms  statement: update service_order set customer_pon 
= now();
strace: Process 164575 attached
strace: Process 164694 attached
2019-03-06 10:12:28 GMT [] [163928]: [1-1] db=,user= LOG:  automatic vacuum of 
table "cmdstaging.public.service_order": index scans: 1
pages: 0 removed, 171162 remain, 413 skipped due to pins, 0 skipped 
frozen
tuples: 123264 removed, 1300328 remain, 0 are dead but not yet 
removable, oldest xmin: 1172166
buffer usage: 890073 hits, 250961 misses, 270986 dirtied
avg read rate: 7.826 MB/s, avg write rate: 8.451 MB/s
system usage: CPU: user: 12.96 s, system: 10.24 s, elapsed: 250.52 s
^Cstrace: Process 163892 detached
2019-03-06 10:12:29 GMT [] [163892]: [5-1] db=,user= LOG:  received fast 
shutdown request
strace: Process 163895 detached
strace: Process 163896 detached
strace: Process 163897 detached
strace: Process 163898 detached
strace: Process 163899 detached
strace: Process 163900 detached
strace: Process 163928 detached
strace: Process 163931 detached
% time

Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Nicola Contu]

This is instead the strace of another server running the same version
compiled  but that is even slower.



Il giorno mer 6 mar 2019 alle ore 11:14 Nicola Contu 
ha scritto:

> Here is the strace as requested for pg11
>
> Thanks
>
> Il giorno mar 5 mar 2019 alle ore 17:47 Thomas Munro <
> thomas.mu...@gmail.com> ha scritto:
>
>> On Wed, Mar 6, 2019 at 4:22 AM Nicola Contu 
>> wrote:
>> >
>> > Not sure what you are requesting exactly but here is the strace for the
>> start of the pg_ctl
>>
>> I meant that you could run the server itself in the foreground under
>> strace, like so:
>>
>> $ strace -f -c /usr/local/pgsql11.2/bin/postgres -D /db/pgsql11/data/
>>
>> Then perform your testing, and finally stop it with pg_ctl from
>> another window (or hit ^C in this window) and strace should spit out a
>> table of system calls with some counters.  We might be able to see why
>> v11 is spending so much more time executing system calls than v10 for
>> your workload, or at least which systems calls they are, assuming you
>> run the same transactions against both versions.
>>
>> --
>> Thomas Munro
>> https://enterprisedb.com
>>
>
% time seconds  usecs/call callserrors syscall
-- --- --- - - 
2019-03-06 11:04:56 GMT [127.0.0.1(46276)] [14799]: [2-1] 
db=cmdv3preprod,user=cmdpreprodapp FATAL:  terminating connection due to 
administrator command
 32.50  143.010306   7  21044095   lseek
 26.21  115.354045  14   8144577   read
  6.18   27.185578  16   166988910 sendto
  5.29   23.300584  57407528   fdatasync
  4.93   21.709522   9   2313529824174 recvfrom
  3.31   14.547568  19765897   write
  2.73   12.007486  14867088 14494 epoll_wait
  2.189.597460  15659871 84097 futex
  1.858.147759  14567414   close
  1.777.767832  18437656 11319 open
  1.536.749496 161 42009   wait4
  1.396.110869 226 26994   setsid
  1.315.760971  15396402   brk
  1.165.083198  29175022   munmap
  0.903.954892  18224829   epoll_ctl
  0.893.928084  17227227 1 mmap
  0.773.376280  11298018   rt_sigaction
  0.632.778183  16178446   kill
  0.632.763563  17159928   rt_sigprocmask
  0.522.306854  12190988   fstat
  0.421.850528 128 14468   sync_file_range
  0.351.534351  52 29591 18762 select
  0.351.532532  16 96298   epoll_create1
  0.321.4151602331   607   fsync
  0.291.263220  21 61605 35503 stat
  0.281.227967 125  982728 fallocate
  0.271.195613   8153557 38629 rt_sigreturn
  0.261.123353 112 10013   173 unlink
  0.241.060130  20 52112   mprotect
  0.110.487515  18 26994   clone
  0.100.458161  11 41080   fadvise64
  0.100.440217  16 26991 1 access
  0.090.406644   4109169   fcntl
  0.040.188007 173  1088   rename
  0.040.181400  18  9829   ftruncate
  0.030.119387   4 26995   pipe
  0.010.036748   1 26995   set_robust_list
  0.010.023510  24  1000   dup
  0.000.019099  13  1425   setitimer
  0.000.017918  18   981   setsockopt
  0.000.015479  26   595   accept
  0.000.010666  18   602   getsockname
  0.000.010172  50   202   getdents
  0.000.004645  28   165   openat
  0.000.003369  7346   link
  0.000.002171  4350   mremap
  0.000.000213  71 3 2 mkdir
  0.000.000189  32 6   getcwd
  0.000.000179  30 6   bind
  0.000.000153  1015   getrusage
  0.000.000152  30 5   chdir
  0.000.000133  1211   socket
  0.000.000119  24 5   lstat
  0.000.000102  13 8 2 connect
  0.000.83  83 1   rmdir
  0.000.70  23 3   geteuid
  0.000.68  23 3   listen
  0.000.56  28 2   umask
  0.000.46  15 3   getrlimit
  0.000.42  42 1   execve
  0.000.37  37 1   

Re: query has no destination for result data

[Tom Lane]

Rob Sargent  writes:
> One of my plpgsql functions is now throwing this error:

> ERROR:  query has no destination for result data
> HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
> CONTEXT:  PL/pgSQL function optimal_pvalue_mono(text,text,integer,double 
> precision,integer) line 65 at SQL statement

> The code referenced at line 65 is the last line in a "for row in query” 
> construct as follows:
> 54  for segp in
> 55select s.id, s.firstmarker, s.lastmarker, 
> 56   v.ipv,
> 57   array_length(p.probands,1) as pbs,
> 58   s.lastmarker - s.firstmarker as mks
> 59from segment s 
> 60 join imputed_pvalue_t v on s.id = v.segment_id
> 61 join probandset p on s.probandset_id = p.id
> 62 join probandset_group_member m on p.id = m.member_id
> 63where s.markerset_id = mkset
> 64  and m.group_id = pbsgid
> 65order by ipv, pbs, mks
> 66  LOOP

I think perhaps you've miscounted lines somehow, or are looking into
the wrong function.  The "at SQL statement" bit indicates that plpgsql
thinks it's dealing with a run-of-the-mill SQL command, not a FOR loop;
moreover, looking at the source code shows that "query has no destination
for result data" is thrown only in exec_stmt_execsql, which ditto.

Another possibility, perhaps, is that there's some syntax error a little
bit above what you've shown us, such that this stanza isn't being seen
as a FOR loop at all, but as more lines in a SQL command that started
earlier.  I'm not entirely sure how such a case would have got past
parsing and into execution, but it's worth thinking about.

In any case, this isn't solvable with just what you've shown us here.

regards, tom lane

Re: query has no destination for result data

[Ron]

On 3/6/19 1:45 AM, Rob Sargent wrote:
[snip]
This construct had been working until recent changes but I cannot relate 
the message to any deformity in the current schema or code.

Any pointers appreciated.


What were the recent changes?

--
Angular momentum makes the world go 'round.

Re: query has no destination for result data

[Rob Sargent]

> On Mar 6, 2019, at 6:29 AM, Tom Lane  wrote:
> 
> Rob Sargent  writes:
>> One of my plpgsql functions is now throwing this error:
> 
>> ERROR:  query has no destination for result data
>> HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
>> CONTEXT:  PL/pgSQL function optimal_pvalue_mono(text,text,integer,double 
>> precision,integer) line 65 at SQL statement
> 
>> The code referenced at line 65 is the last line in a "for row in query” 
>> construct as follows:
>>54  for segp in
>>55select s.id, s.firstmarker, s.lastmarker, 
>>56   v.ipv,
>>57   array_length(p.probands,1) as pbs,
>>58   s.lastmarker - s.firstmarker as mks
>>59from segment s 
>>60 join imputed_pvalue_t v on s.id = v.segment_id
>>61 join probandset p on s.probandset_id = p.id
>>62 join probandset_group_member m on p.id = m.member_id
>>63where s.markerset_id = mkset
>>64  and m.group_id = pbsgid
>>65order by ipv, pbs, mks
>>66  LOOP
> 
> I think perhaps you've miscounted lines somehow, or are looking into
> the wrong function.  The "at SQL statement" bit indicates that plpgsql
> thinks it's dealing with a run-of-the-mill SQL command, not a FOR loop;
> moreover, looking at the source code shows that "query has no destination
> for result data" is thrown only in exec_stmt_execsql, which ditto.
> 
> Another possibility, perhaps, is that there's some syntax error a little
> bit above what you've shown us, such that this stanza isn't being seen
> as a FOR loop at all, but as more lines in a SQL command that started
> earlier.  I'm not entirely sure how such a case would have got past
> parsing and into execution, but it's worth thinking about.
> 
> In any case, this isn't solvable with just what you've shown us here.
> 
>   regards, tom lane

Thank you sir, I shall keep digging. If it comes to that I can certainly share 
all three functions involved. I presume the answer to the issue of a zero row 
result is No it won’t cause this error.

@Ron: Did you hit send a little early?

Re: query has no destination for result data

[Rob Sargent]

> On Mar 6, 2019, at 6:32 AM, Ron  wrote:
> 
> On 3/6/19 1:45 AM, Rob Sargent wrote:
> [snip]
>> This construct had been working until recent changes but I cannot relate the 
>> message to any deformity in the current schema or code.
>> Any pointers appreciated.
> 
> What were the recent changes?
> 
> -- 
> Angular momentum makes the world go 'round.
> 

diff optimalMonoPed.sql  
optimalMonoPed.sql.~20359ea9e67ddf009db89b94140f67988862f247~
13,14d12
<   imkr  int;
<   jmkr  int;
53c51
< --
---
> --  
67,68d64
< select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where ordinal 
between segp.firstmarker and segp.lastmarker;
< raise notice 'seg % start=% i=% end=% j=%', segp.id, segp.firstmarker, 
imkr, segp.lastmarker, jmkr;

In other words two variables dropped along with the select which set them for 
their only use in a NOTICE, plus white space on a comment line.

So yes the problem must be in the caller.

A not on line numbers: Using \ef on this function presents a slight variation 
of my code: it rearranges the “language plpgsql” from after the final END; (old 
style I guess) to before the AS.  So line 65 is actually what I thought was 
line 64.
Still not the real problem of course.  (I’ll update my ways re: coding 
functions)

Re: query has no destination for result data

[Adrian Klaver]

On 3/5/19 11:45 PM, Rob Sargent wrote:

I’m using 10.7. Does an empty result set generate this error by any chance.

One of my plpgsql functions is now throwing this error:

select * from genome_threshold_mono('11-O3C.pbs','1-O3C_chr',1.96,
100);


The above is the function you started with.
See below for more.


NOTICE:  group id is 5eed8d65-d39a-4f72-97a3-ca391b84880d
NOTICE:  New threshold: 66128154-d128-4e66-bb8e-e9c9ee5ae89d
NOTICE:  doing chrom 11
NOTICE:  2019-03-06 00:21:17.253375-07: markerset id is
9a8f7487-bd64-4d43-9adf-5ae1c6744e60(1-O3C_chr11.Loci.ld), people
(5eed8d65-d39a-4f72-97a3-ca391b84880d) id is 11-O3C.pbs
NOTICE:  table "collected" does not exist, skipping
NOTICE:  table "mrkidx" does not exist, skipping
NOTICE:  2019-03-06 00:21:17.295142-07: working with 28607 markers
NOTICE:  2019-03-06 00:21:17.383835-07: added 3514 segments to
imputed_pvalue_t
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM
instead.


Below you have two other functions in play:

optimal_pvalue_mono()
genome_threshold_mono()

If I am following correctly it is  line 30 in genome_threshold_mono() 
you want to take a look at.





CONTEXT:  PL/pgSQL function
optimal_pvalue_mono(text,text,integer,double precision,integer) line
65 at SQL statement
SQL statement "insert into goptset
         select * from optimal_pvalue_mono(people_name, mvec.name,
mvec.chrom, conf, maxi)"
PL/pgSQL function genome_pvalue_mono(text,text,double
precision,integer) line 19 at SQL statement
SQL statement "insert into threshold_segment(id,threshold_id,
segment_id, smooth_pvalue)
          select uuid_generate_v4(), tid, f.segment_id, f.pval
          from genome_pvalue_mono(pbs_name, markers_rx, conf, maxi)
as f"
PL/pgSQL function genome_threshold_mono(text,text,double
precision,integer) line 30 at SQL statement


The code referenced at line 65 is the last line in a "for row in query” 
construct as follows:


     54 for segp in
     55   select s.id , s.firstmarker, s.lastmarker,
     56          v.ipv,
     57          array_length(p.probands,1) as pbs,
     58          s.lastmarker - s.firstmarker as mks
     59   from segment s
     60        join imputed_pvalue_t v on s.id  =
v.segment_id
     61        join probandset p on s.probandset_id = p.id 
     62        join probandset_group_member m on p.id 
= m.member_id
     63   where s.markerset_id = mkset
     64         and m.group_id = pbsgid
     65   order by ipv, pbs, mks
     66 LOOP

Plugging in the appropriate values for an example run generates a proper 
dataset (~1300 rows)as far as I can tell.
This construct had been working until recent changes but I cannot relate 
the message to any deformity in the current schema or code.

Any pointers appreciated.




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

Re: query has no destination for result data

[Adrian Klaver]

On 3/6/19 7:12 AM, Rob Sargent wrote:




On Mar 6, 2019, at 6:32 AM, Ron  wrote:

On 3/6/19 1:45 AM, Rob Sargent wrote:
[snip]

This construct had been working until recent changes but I cannot relate the 
message to any deformity in the current schema or code.
Any pointers appreciated.


What were the recent changes?

--
Angular momentum makes the world go 'round.



diff optimalMonoPed.sql  
optimalMonoPed.sql.~20359ea9e67ddf009db89b94140f67988862f247~
13,14d12
<   imkr  int;
<   jmkr  int;
53c51
< --
---

--

67,68d64
< select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where ordinal 
between segp.firstmarker and segp.lastmarker;
< raise notice 'seg % start=% i=% end=% j=%', segp.id, segp.firstmarker, 
imkr, segp.lastmarker, jmkr;

In other words two variables dropped along with the select which set them for 
their only use in a NOTICE, plus white space on a comment line.

So yes the problem must be in the caller.

A not on line numbers: Using \ef on this function presents a slight variation 
of my code: it rearranges the “language plpgsql” from after the final END; (old 
style I guess) to before the AS.  So line 65 is actually what I thought was 
line 64.


I believe language plpgsql is not considered part of the function body 
so it is not included in the line count:


https://www.postgresql.org/docs/10/plpgsql-structure.html

When tracking a line number down I usually do:

\ef some_function line_number

which counts the line in the function body not the file. So for example:

Using set nu in Vi:

1 CREATE OR REPLACE FUNCTION public.ts_update()
2  RETURNS trigger
3  LANGUAGE plpgsql
4 AS $function$
5 BEGIN
6 NEW.ts_update := timeofday();
7 RETURN NEW;
8 END;
9 $function$

\ef ts_update 4

CREATE OR REPLACE FUNCTION public.ts_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
NEW.ts_update := timeofday();
RETURN NEW;  <--- This row is marked
END;
$function$




Still not the real problem of course.  (I’ll update my ways re: coding 
functions)






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

Re: query has no destination for result data

[Pavel Stehule]

I believe language plpgsql is not considered part of the function body
> so it is not included in the line count:
>
> https://www.postgresql.org/docs/10/plpgsql-structure.html
>
> When tracking a line number down I usually do:
>
> \ef some_function line_number
>
> which counts the line in the function body not the file. So for example:
>
> Using set nu in Vi:
>
> 1 CREATE OR REPLACE FUNCTION public.ts_update()
> 2  RETURNS trigger
> 3  LANGUAGE plpgsql
> 4 AS $function$
> 5 BEGIN
> 6 NEW.ts_update := timeofday();
> 7 RETURN NEW;
> 8 END;
> 9 $function$
>
> \ef ts_update 4
>
> CREATE OR REPLACE FUNCTION public.ts_update()
>   RETURNS trigger
>   LANGUAGE plpgsql
> AS $function$
> BEGIN
>  NEW.ts_update := timeofday();
> RETURN NEW;  <--- This row is marked
> END;
> $function$
>
>
or

\sf+ functioname

Regards

Pavel

Re: query has no destination for result data

[Adrian Klaver]

On 3/6/19 7:37 AM, Pavel Stehule wrote:







or

\sf+ functioname


Cool, I learned something new.



Regards

Pavel



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

Re: query has no destination for result data

[Rob Sargent]

> On Mar 6, 2019, at 7:41 AM, Adrian Klaver  wrote:
> 
> On 3/6/19 7:37 AM, Pavel Stehule wrote:
> 
>> or
>> \sf+ functioname
> 
> Cool, I learned something new.
> 
>> Regards
>> Pavel
> 
> 

using \ef function 65 puts the cursor on the first line of the loop. So a 
debugging statement got in the way!  I don’t need the variables set other than 
to keep track of what’s going on in the loop. They’re gone now and so it the 
problem (and hopefully I’ll correct my ways).
Thank you all.

  raise notice '%: added % segments to imputed_pvalue_t', clock_timestamp(), 
rcount;
--
  for segp in
select s.id, s.firstmarker, s.lastmarker, 
   v.ipv,
   array_length(p.probands,1) as pbs,
   s.lastmarker - s.firstmarker as mks
from segment s 
 join imputed_pvalue_t v on s.id = v.segment_id
 join probandset p on s.probandset_id = p.id
 join probandset_group_member m on p.id = m.member_id
where s.markerset_id = mkset
  and m.group_id = pbsgid
order by ipv, pbs, mks
  LOOP
select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where ordinal 
between segp.firstmarker and segp.lastmarker;
raise notice 'seg % start=% i=% end=% j=%', segp.id, segp.firstmarker, 
imkr, segp.lastmarker, jmkr;
delete from mrkidx where ordinal between segp.firstmarker and 
segp.lastmarker;
get diagnostics rcount = ROW_COUNT;
segsdone = segsdone + 1;
if rcount > 0 then
   insert into collected values(segp.id, segp.ipv);
   totalinserts = totalinserts + rcount;
   if totalinserts = mkrcnt then  -- really totalDELETES
  raise notice '%: no markers left on %th segment %', 
clock_timestamp(), segsdone, segp.id;
  exit;
   end if;
end if;
  end loop;

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

EXCLUDE USING hash(i WITH =)

[Erwin Brandstetter]

The manual currently advises:
https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

EXCLUDE [ USING *index_method* ] ( *exclude_element* WITH *operator* [, ...
> ] ) *index_parameters* [ WHERE ( *predicate* ) ][...]Although it's
> allowed, there is little point in using B-tree or hash indexes with an
> exclusion constraint, *because this does nothing that an ordinary unique
> constraint doesn't do better*. So in practice the access method will
> always be GiST or SP-GiST.


However, hash indexes do not support UNIQUE:
https://www.postgresql.org/docs/current/indexes-unique.html

Currently, only B-tree indexes can be declared unique.
>

But an exclusion constraint with "USING hash" seems to do exactly that
(more expensively, granted), handling hash collisions gracefully. Demo
(original idea by user FunctorSalad on stackoverflow:
https://stackoverflow.com/questions/47976185/postgresql-ok-to-use-hash-exclude-constraint-for-uniqueness/47976504?noredirect=1#comment96799970_47976504
):

CREATE TABLE exclude_hast_test(
  i int,
  EXCLUDE USING hash(i WITH =)
);

INSERT INTO exclude_hast_test VALUES (213182),(1034649);  --  hashint4()
collision!

More detailed fiddle:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=8a9fc48f74f93f8aed0964f3796a0b04

Would seem particularly attractive for values too large for btree indexes.
An index on a hash value is the recommended workaround, but an exclusion
constraint also handles hash collisions automatically. (Or even for any wide
column to keep index size low.)

Hence my questions:

- Why does an exclusion constraint with "USING hash(i WITH =)" enforce
uniqueness, while we still can't create a "UNIQUE index ... USING hash .."?
- Why would the manual discourage its use? Should I file a documentation
bug?

Regards
Erwin

Re: query has no destination for result data

[Adrian Klaver]

On 3/6/19 8:19 AM, Rob Sargent wrote:



On Mar 6, 2019, at 7:41 AM, Adrian Klaver > wrote:


On 3/6/19 7:37 AM, Pavel Stehule wrote:


or
\sf+ functioname


Cool, I learned something new.


Regards
Pavel





using \ef function 65 puts the cursor on the first line of the loop. So 
a debugging statement got in the way!  I don’t need the variables set 
other than to keep track of what’s going on in the loop. They’re gone 
now and so it the problem (and hopefully I’ll correct my ways).


Where you maybe needing SELECT INTO?:

https://www.postgresql.org/docs/10/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

So something like(not tested):

select min(ordinal), max(ordinal) into imkr, jmkr from mrkidx where 
ordinal between segp.firstmarker and segp.lastmarker;



Thank you all.

   raise notice '%: added % segments to imputed_pvalue_t',
clock_timestamp(), rcount;
--
   for segp in
     select s.id , s.firstmarker, s.lastmarker,
            v.ipv,
            array_length(p.probands,1) as pbs,
            s.lastmarker - s.firstmarker as mks
     from segment s
          join imputed_pvalue_t v on s.id  = v.segment_id
          join probandset p on s.probandset_id = p.id 
          join probandset_group_member m on p.id  =
m.member_id
     where s.markerset_id = mkset
           and m.group_id = pbsgid
     order by ipv, pbs, mks
   LOOP
_select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where
ordinal between segp.firstmarker and segp.lastmarker;_
     raise notice 'seg % start=% i=% end=% j=%', segp.id
, segp.firstmarker, imkr, segp.lastmarker, jmkr;
     delete from mrkidx where ordinal between segp.firstmarker and
segp.lastmarker;
     get diagnostics rcount = ROW_COUNT;
     segsdone = segsdone + 1;
     if rcount > 0 then
        insert into collected values(segp.id ,
segp.ipv);
        totalinserts = totalinserts + rcount;
        if totalinserts = mkrcnt then  -- really totalDELETES
           raise notice '%: no markers left on %th segment %',
clock_timestamp(), segsdone, segp.id ;
           exit;
        end if;
     end if;
   end loop;


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





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

Monitor the ddl and dml activities in logs

[Nanda Kumar]

Hello Team,

I would like to know where I can monitor the ddl and dml operations happens in 
the production environment .

FYI - We are using Postgres 9.6 RDS database .

Regards
Nanda Kumar.M
SmartStream Technologies
Eastland Citadel | 5th Floor | 102 Hosur Road | Banglore 560 095 | India
nanda.ku...@smartstream-stp.com | 
www.smartstream-stp.com
Mob

+91 99720 44779

Tel

+91 80617 64107




The information in this email is confidential and may be legally privileged. It 
is intended solely for the addressee. Access to this email by anyone else is 
unauthorised. If you are not the intended recipient, any disclosure, copying, 
distribution or any action taken or omitted to be taken in reliance on it, is 
prohibited and may be unlawful.

Re: Monitor the ddl and dml activities in logs

[Ben Chobot]

> On Mar 6, 2019, at 5:58 AM, Nanda Kumar  
> wrote:
> 
> Hello Team,
>  
> I would like to know where I can monitor the ddl and dml operations happens 
> in the production environment .

The documentation is your friend, particularly 
https://www.postgresql.org/docs/9.6/runtime-config-logging.html. By setting 
log_statement appropriately, you can see ddl and dml in your server logs.

Re: Monitor the ddl and dml activities in logs

[Adrian Klaver]

On 3/6/19 5:58 AM, Nanda Kumar wrote:

Hello Team,

I would like to know where I can monitor the ddl and dml operations 
happens in the production environment .


FYI - We are using Postgres 9.6 RDS database .


https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.PostgreSQL.html


Regards

*Nanda Kumar.M*

SmartStream Technologies

Eastland Citadel | 5th Floor | 102 Hosur Road | Banglore 560 095 | India

nanda.ku...@smartstream-stp.com  
| www.smartstream-stp.com


Mob



+91 99720 44779

Tel



+91 80617 64107


The information in this email is confidential and may be legally 
privileged. It is intended solely for the addressee. Access to this 
email by anyone else is unauthorised. If you are not the intended 
recipient, any disclosure, copying, distribution or any action taken or 
omitted to be taken in reliance on it, is prohibited and may be unlawful.



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

Re: query has no destination for result data

[Rob Sargent]

> On Mar 6, 2019, at 10:29 AM, Adrian Klaver  wrote:
> 
> On 3/6/19 8:19 AM, Rob Sargent wrote:
>>> On Mar 6, 2019, at 7:41 AM, Adrian Klaver >> > wrote:
>>> 
 On 3/6/19 7:37 AM, Pavel Stehule wrote:
 
 or
 \sf+ functioname
>>> 
>>> Cool, I learned something new.
>>> 
 Regards
 Pavel
>>> 
>>> 
>> using \ef function 65 puts the cursor on the first line of the loop. So a 
>> debugging statement got in the way!  I don’t need the variables set other 
>> than to keep track of what’s going on in the loop. They’re gone now and so 
>> it the problem (and hopefully I’ll correct my ways).
> 
> Where you maybe needing SELECT INTO?:
> 
> https://www.postgresql.org/docs/10/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
> 
> So something like(not tested):
> 
> select min(ordinal), max(ordinal) into imkr, jmkr from mrkidx where ordinal 
> between segp.firstmarker and segp.lastmarker;
> 
Quite likely but I really don’t need the assignment so the problem is gone. 


>> Thank you all.
>>   raise notice '%: added % segments to imputed_pvalue_t',
>>clock_timestamp(), rcount;
>>--
>>   for segp in
>> select s.id , s.firstmarker, s.lastmarker,
>>v.ipv,
>>array_length(p.probands,1) as pbs,
>>s.lastmarker - s.firstmarker as mks
>> from segment s
>>  join imputed_pvalue_t v on s.id  = v.segment_id
>>  join probandset p on s.probandset_id = p.id 
>>  join probandset_group_member m on p.id  =
>>m.member_id
>> where s.markerset_id = mkset
>>   and m.group_id = pbsgid
>> order by ipv, pbs, mks
>>   LOOP
>>_select imkr=min(ordinal), jmkr=max(ordinal) from mrkidx where
>>ordinal between segp.firstmarker and segp.lastmarker;_
>> raise notice 'seg % start=% i=% end=% j=%', segp.id
>>, segp.firstmarker, imkr, segp.lastmarker, jmkr;
>> delete from mrkidx where ordinal between segp.firstmarker and
>>segp.lastmarker;
>> get diagnostics rcount = ROW_COUNT;
>> segsdone = segsdone + 1;
>> if rcount > 0 then
>>insert into collected values(segp.id ,
>>segp.ipv);
>>totalinserts = totalinserts + rcount;
>>if totalinserts = mkrcnt then  -- really totalDELETES
>>   raise notice '%: no markers left on %th segment %',
>>clock_timestamp(), segsdone, segp.id ;
>>   exit;
>>end if;
>> end if;
>>   end loop;
>>> -- 
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: write on standby

[Adrian Klaver]

On 3/5/19 9:16 PM, Andreas Kretschmer wrote:



Am 06.03.19 um 00:34 schrieb Julie Nishimura:

Hello there,
Is it possible for a test app to connect to the standby dB of an 
active-Standby dB pair?


that's possible, but ...


While both continue to be connected and replicating? What if it’s 
needed to write tmp tables that are later dropped?


... that's not. Not with streaming replication, but ...



Can it be done in hot standby scenario on standby? Version is 9.6.2



... it would be possible with logical replication (trigger based 
solutions like slony or lindiste or logical replication using pglogical 
from us, 2ndQ).


https://www.2ndquadrant.com/en/resources/pglogical/


You can also use BDR2 with PG9.6, but this is only available for our 
customers.


Out of curiosity how did:

https://www.postgresql.org/message-id/20150831193033.GL2912%40alvherre.pgsql

https://www.postgresql.org/message-id/cah+ga0qo0q9njhkxaoo8cv-qr9cuw6ltdgdb33pq16ru88f...@mail.gmail.com

get to?:

https://www.2ndquadrant.com/en/resources/postgres-bdr-2ndquadrant/




Regards, Andreas




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

Re: Non-pausing table scan on 9.6 replica?

[Mark Fletcher]

Andreas, Sameer,

Thank you for replying. I did not understand the purpose of
hot_standby_feedback, and your explanations helped. I turned it on, and the
pausing stopped.

Thanks,
Mark

Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes

[Thomas Munro]

Adding Noah to thread.

On Wed, Feb 27, 2019 at 11:28 AM Tom Lane  wrote:
> Thomas Munro  writes:
> > I don't see pthread_is_threaded_np() on any non-Apple systems in my
> > lab.
>
> Yeah, I thought that might be a Mac thing.  I wonder if POSIX has any
> usable equivalent.

I don't see anything like that (the concept doesn't seem very
portable).  I couldn't find a way on Glibc (but I'm not saying there
isn't one hiding somewhere).  FreeBSD has a thing much like macOS's
(and I think some more BSDs do too); it's set to true by libthr when
the first thread is created, to make libc start locking various stuff.

The macOS one probably isn't a good canary to protect us from OpenLDAP
creating threads since on typical macOS builds we're using Apple's
LDAP thing (which cybersquats libldap.dylib and libldap_r.dylib via
symlinks).  So adding a FreeBSD check seems like a good idea, because
at least one FreeBSD system in our buildfarm runs the ldap checks on
real OpenLDAP (elver).

> > Clearly libdap_r is *capable* of creating threads: it contains a
> > function ldap_pvt_thread_create(), and we can see that slapd and other
> > OpenLDAP things use that, but AFAICT that's a private facility not
> > intended for end users to call, so there's no danger if you just use
> > the documented LDAP client API.
>
> That seems promising, but I'd sure be happier if we could cross-check
> that there's still just one thread at the completion of authentication.

Ok, here's that patch again with a commit message and with the
configure version warning removed, and a make-sure-we're-not-threaded
patch for FreeBSD.

I'm not sure what to do about the LDAP test in
contrib/dblink/sql/dblink.sql.  Do we still want this?

I propose this for master only, for now.  I also think it'd be nice to
consider back-patching it after a while, especially since this
reported broke on CentOS/RHEL7, a pretty popular OS that'll be around
for a good while.  Hmm, I wonder if it's OK to subtly change library
dependencies in a minor release; I don't see any problem with it since
I expect both variants to be provided by the same package in every
distro but we'd certainly want to highlight this to the package
maintainers if we did it.

--
Thomas Munro
https://enterprisedb.com


0001-Test-__isthreaded-on-FreeBSD-and-friends.patch
Description: Binary data


0002-Use-the-same-libldap-variant-in-the-frontend-and-bac.patch
Description: Binary data

python install location

[Alan Nilsson]

How does postgres determine which install of python to use in conjunction with 
plpythonu?  We have a CentOS6 machine with python 2.6 and 2.7 installed on it.  
The 2.6 version is in the canonical location and the version which gets used 
when using the plpython extension.

Is there a way, in postgres, short of rebuilding that we can tell postgres 
which install of python to use when invoking plpython?

thanks
alan

Re: python install location

[Adrian Klaver]

On 3/6/19 4:12 PM, Alan Nilsson wrote:

How does postgres determine which install of python to use in conjunction with 
plpythonu?  We have a CentOS6 machine with python 2.6 and 2.7 installed on it.  
The 2.6 version is in the canonical location and the version which gets used 
when using the plpython extension.

Is there a way, in postgres, short of rebuilding that we can tell postgres 
which install of python to use when invoking plpython?


https://www.postgresql.org/docs/11/plpython-python23.html



thanks
alan






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

Re: Non-pausing table scan on 9.6 replica?

[Sameer Kumar]

On Thu, Mar 7, 2019 at 5:16 AM Mark Fletcher  wrote:

> Andreas, Sameer,
>
> Thank you for replying. I did not understand the purpose of
> hot_standby_feedback, and your explanations helped. I turned it on, and the
> pausing stopped.
>

Great!
But do bear in mind that this is also not without its own implications. As
pointed out by Andreas, this could lead to bloats.

Which version of PostgreSQL are you using? Sorry if I have missed that
information.


>  Thanks,
> Mark
>

Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes

[Noah Misch]

On Thu, Mar 07, 2019 at 10:45:56AM +1300, Thomas Munro wrote:
> On Wed, Feb 27, 2019 at 11:28 AM Tom Lane  wrote:
> > Thomas Munro  writes:
> > > I don't see pthread_is_threaded_np() on any non-Apple systems in my
> > > lab.
> >
> > Yeah, I thought that might be a Mac thing.  I wonder if POSIX has any
> > usable equivalent.
> 
> I don't see anything like that (the concept doesn't seem very
> portable).

I'm not aware of one.

> > > Clearly libdap_r is *capable* of creating threads: it contains a
> > > function ldap_pvt_thread_create(), and we can see that slapd and other
> > > OpenLDAP things use that, but AFAICT that's a private facility not
> > > intended for end users to call, so there's no danger if you just use
> > > the documented LDAP client API.
> >
> > That seems promising, but I'd sure be happier if we could cross-check
> > that there's still just one thread at the completion of authentication.
> 
> Ok, here's that patch again with a commit message and with the
> configure version warning removed, and a make-sure-we're-not-threaded
> patch for FreeBSD.
> 
> I'm not sure what to do about the LDAP test in
> contrib/dblink/sql/dblink.sql.  Do we still want this?

Mike, does the dblink test suite not fail on your system?  It's designed to
catch this exact problem.

Has anyone else reproduced this?

> I propose this for master only, for now.  I also think it'd be nice to
> consider back-patching it after a while, especially since this
> reported broke on CentOS/RHEL7, a pretty popular OS that'll be around
> for a good while.  Hmm, I wonder if it's OK to subtly change library
> dependencies in a minor release; I don't see any problem with it since
> I expect both variants to be provided by the same package in every
> distro but we'd certainly want to highlight this to the package
> maintainers if we did it.

It's not great to change library dependencies in a minor release.  If every
RHEL 7 installation can crash this way, changing the dependencies is probably
the least bad thing.

Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Thomas Munro]

On Wed, Mar 6, 2019 at 11:14 PM Nicola Contu  wrote:
> Here is the strace as requested for pg11

How does it compare to v10 running the same test?

-- 
Thomas Munro
https://enterprisedb.com

Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Thomas Munro]

On Thu, Mar 7, 2019 at 12:12 AM Nicola Contu  wrote:
> This is instead the strace of another server running the same version 
> compiled  but that is even slower.

Huh.  That's a lot of lseek().  Some of these will be for random
reads/writes and will go way in v12, and some will be for probing the
size of relations while planning, and some while executing scans.  I
bet you could make some of them go away by using prepared statements.
Does the query in your test involve many partitions/tables?

% time seconds  usecs/call callserrors syscall
-- --- --- - - 
 32.50  143.010306   7  21044095   lseek
 26.21  115.354045  14   8144577   read
  6.18   27.185578  16   166988910 sendto
  5.29   23.300584  57407528   fdatasync
  4.93   21.709522   9   2313529824174 recvfrom
  3.31   14.547568  19765897   write
  2.73   12.007486  14867088 14494 epoll_wait
  2.189.597460  15659871 84097 futex
  1.858.147759  14567414   close
  1.777.767832  18437656 11319 open

The other results had 1 usec lseek(), and much fewer of them relative
to the number of reads and writes.  BTW, are you comparing v10 and v11
on the same hardware, kernel, filesystem?  Just wondering if there
could be some change in syscall overhead on different kernel patch
levels or something like that: we see 7 usec vs 1 usec in those two
files (though I have no idea how reliable these times are) and if
we're going to call it 21 million times at some point it might
matter...

-- 
Thomas Munro
https://enterprisedb.com

Re: Question about pg_upgrade from 9.2 to X.X

[Perumal Raj]

Awesome, thanks Sergei and Justin,

Finally, I am able to upgrade the DB from 9.2 to 9.6 successfully  after
dropping Schema (reorg) without library issue.
Also , I have installed -Contrib. package for Version:10 and upgraded to
version 10.7 too.

On both the cases , I have used  --link option and it took just fraction of
seconds ( I feel 'Zero' Downtime effect )

Any pointers for pg_repack schema creation ?
Will there be any impact in the future , Since i used --link option ?

Regards,
Raju








On Tue, Mar 5, 2019 at 8:21 AM Justin Pryzby  wrote:

> On Tue, Mar 05, 2019 at 08:09:12AM -0800, Perumal Raj wrote:
> > Thanks Sergei/Justin for the continues update.
> >
> > So reorg Schema might be created as part of some scripts prior to 9.2
> > Version ?
>
> I'm guessing they were probably created in 9.2.
>
> > These are the functions in DB not the Extension. However these functions
> > will not run as the associated libraries are not exists in System now
> (9.2)
> > and I hope no impact to system.
>
> I guess someone installed pgreorg, ran its scripts to install its functions
> into the DB, and then removed pgreorg without removing its scripts.
>
> > One Question need your address,
> >
> > Prior to 9.2 to 9.6 upgrade , I have tried 9.2 10.7 upgrade and failed
> > similar error(you can refer beginning o the post ).
> >
> > > could not load library "$libdir/hstore": ERROR:  could not access file
> "$libdir/hstore": No such file or directory
> > > could not load library "$libdir/adminpack": ERROR:  could not access
> file "$libdir/adminpack": No such file or directory
> > > could not load library "$libdir/uuid-ossp": ERROR:  could not access
> file "$libdir/uuid-ossp": No such file or directory
> >
> > These Extension seems to be standard. What is the use of these function
> and
> > do we have any alternative in Higher version or Enhanced object if i drop
> > it in 9.2 and continue upgrade to 10.7 Version.
>
> See Sergei's response:
>
> https://www.postgresql.org/message-id/7164691551378448%40myt3-1179f584969c.qloud-c.yandex.net
>
> You probably want to install this package for the new version (9.6 or 10 or
> 11).
>
> [pryzbyj@TS-DB ~]$ rpm -ql postgresql11-contrib |grep -E
> '(uuid-ossp|adminpack|hstore)\.control'
> /usr/pgsql-11/share/extension/adminpack.control
> /usr/pgsql-11/share/extension/hstore.control
> /usr/pgsql-11/share/extension/uuid-ossp.control
>
> Justin
>

partial data migration

[Julie Nishimura]

Hello psql friends,
We need to migrate only 6 months worth of data from one instance to another. 
What would be the easiest way to do it? In Oracle, I would set up dblink. What 
about postgresql?

Thank you!

Re: partial data migration

[Ron]

On 3/7/19 1:54 AM, Julie Nishimura wrote:

Hello psql friends,
We need to migrate only 6 months worth of data from one instance to 
another. What would be the easiest way to do it? In Oracle, I would set up 
dblink. What about postgresql?


postgres_fdw

--
Angular momentum makes the world go 'round.