Re: trouble making PG use my Perl

[Steven Lembark]

> I can make Pg come up, initdb, that sort of stuff just fine. But we
> also use the Perl extension and we have references to Perl modules
> that are in *our* Perl and not the system one. Yes, we compile our
> own Perl like we provide our own Pg because Centos uses much older
> versions.
> 
> The issue is that I've not been able to make Pg use our Perl
> (in /opt/perl) instead of the system one (in /usr). I've tried
> changing the env-vars in multiple places, the most obvious being
> the /etc/init.d script, but none of that helps. When we compiled our
> own Pg, I could set this with "export PERL=/opt/perl/bin/perl" before
> the "configure --with-perl" command. Setting PERL in the init.d file
> doesn't help either.

If you use the centos pre-compiled glob then you'll get their 
pre-compiled paths to their pre-compiled Perl which, among
other things, is compiled with all optimization turned off, 
with 5.00503 compatibility turned *on*, and a host of other
pure idiocies that make their perl unsuitable for human use.

Simplest fix is probably rolling your own: PG's build cycle 
is quite manageable, as is Perl's, and you'll get something
that is reasonably optimized for your platform and use.


-- 
Steven Lembark3646 Flora Place
Workhorse ComputingSt. Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508

Re: trouble making PG use my Perl

[Steven Lembark]

On Thu, 27 Feb 2020 20:42:36 +
Kevin Brannen  wrote:

> Thanks Tom, I can see your point. With the right change to
> LD_LIBRARY_PATH, I can make `ldd plperl.so` point to my Perl, but as
> you say, I'm probably playing with fire to expect it all to be 100%
> compatible between Perl 5.10.1 (Centos 6 default) and 5.30.1 (mine).

Note that you don't *want* to be binary compatible with the junk
they distribute.

Unless you really expect to have copies of 5.00503 running?
No, probably not. 

RH and Debian distros are distriuted by heavy Python users who
go out of their way to hamstring Perl at every step. Unless 
things have changed massively, the Perl they distribute is not
only out of date it's nearly broken out of the box. Try running
"perl -V" sometime and review the "config_args" values.

enjoi

-- 
Steven Lembark3646 Flora Place
Workhorse ComputingSt. Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508

Re: trouble making PG use my Perl

[Steven Lembark]

On Thu, 27 Feb 2020 15:07:29 -0500
Tom Lane  wrote:

> You might be able to override that with LD_LIBRARY_PATH, but it's
> a pain, and it will certainly not work if your homebrew libperl
> isn't 100% ABI-compatible with the system one.
> 
> Personally I'd build plperl against the Perl you want to use it with.
> The rest of PG isn't dependent on Perl, so you could use the community
> install for the rest of it if you like.

Funny thing is that both PG and Perl are easy enough to build
from scratch and the centos compile of Perl at least is both
ancient and horrid enough (5.00503 compatibility, really?) that
it's easier to just shell-script both builds and run it overnight.

Q: How un-optimized and ancient is the PG on centos?

-- 
Steven Lembark3646 Flora Place
Workhorse ComputingSt. Louis, MO 63110
lemb...@wrkhors.com+1 888 359 3508

Re: Need to find the no. of connections for a database

[sivapostg...@yahoo.com]

 Since the no. of entry screens to be locked might increase with 
enhancement(s), the approach could be  1.  On completion of all entries by all, 
 say for a day.2.  When opening the report, check whether only one user has 
logged in.  Close the report window, if there are more than one users.3.  Flag 
somewhere in the db, that the processing has started.4.  Check at the opening 
event of the 'tobe locked screens', whether this flag set in point 3.  If set, 
quit the screen.5.  Create a report from the entered data.
6.  Create / Modify required entries from the values arrived in the report. [ 
long process ]7.  Once completed, commit all data.  Reset the flag set in point 
3.8.  Ensure that no data entered for the processed period.  Data entered will 
be for another date.  Data cannot [should] not entered for the processed date.
If this works out, we need to find a way to count the no. of users logged in 
the database.   
On Friday, 28 February, 2020, 11:35:48 am IST, Rob Sargent 
 wrote:  
 
 
On 2/27/20 10:38 PM, rob stone wrote:
>
> If the menu is built from a table in your database, then when
> "Processing report" starts you could set a flag (boolean) against those
> items so that if anybody tried to log-in or access those items, you
> could simply display a message along the lines of "Processing report is
> running. Please try again later".
>
> When "Processing report" finishes, it just clears that flag.
>
> HTH,
> Rob
>

Conversely, the OP could be asking for a way to turn "process report" to 
active state when no one is using the other features: each of them could 
set a lock/counter I guess and when all counters are zero enable process 
button.  The notion of waiting for "all clear" from the app or the db 
before running reports does seem odd though.  Do certain actions from 
the app leave the database in an inconsistent state and break the report 
were it run at the same time?


  

Re: A question relative to creating an audit table

[stan]

On Thu, Feb 27, 2020 at 10:30:15PM +0100, Andrei Zhidenkov wrote:
> Why not to pass TG_TABLE_SCHEMA and TG_TABLE_NAME in its arguments?
> 
> > On 27. Feb 2020, at 22:28, stan  wrote:
> > 
> > I ma considering setting up a function, and triggers to put a record in an
> > audit table when certain tables are altered. I pretty much think I know how
> > to do this, with one exception.
> > 
> > Can a function, called by a trigger, determine what table it was called
> > for?
> > 

I suppose I could do that. I have in mind a single function to accomplish the
audit log, but I will have to have unique triggers for each table. I was
just hoping not to have to do that. Are these values that I could read
automatically? Or do I have to hard code them?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: A question relative to creating an audit table

[Andrei Zhidenkov]

Please, read chapter 43.10.1 of the PostgreSQL documentation 
(https://www.postgresql.org/docs/11/plpgsql-trigger.html 
). Just to be sure we 
are on the same page.

> On 28. Feb 2020, at 14:59, stan  wrote:
> 
> On Thu, Feb 27, 2020 at 10:30:15PM +0100, Andrei Zhidenkov wrote:
>> Why not to pass TG_TABLE_SCHEMA and TG_TABLE_NAME in its arguments?
>> 
>>> On 27. Feb 2020, at 22:28, stan  wrote:
>>> 
>>> I ma considering setting up a function, and triggers to put a record in an
>>> audit table when certain tables are altered. I pretty much think I know how
>>> to do this, with one exception.
>>> 
>>> Can a function, called by a trigger, determine what table it was called
>>> for?
>>> 
> 
> I suppose I could do that. I have in mind a single function to accomplish the
> audit log, but I will have to have unique triggers for each table. I was
> just hoping not to have to do that. Are these values that I could read
> automatically? Or do I have to hard code them?
> 
> -- 
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
>   -- Benjamin Franklin

Re: Recursive CTEs and randomness - is there something I'm missing?

[Tom Lane]

=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?=  writes:
> The SQL:

> WITH RECURSIVE rand (num, md, a_2_s) AS
> (
>   SELECT
> 1,
> MD5(RANDOM()::TEXT),
> ARRAY_TO_STRING(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
>   FROM GENERATE_SERIES(1, 5)), '')
>   UNION
> SELECT num + 1,
> MD5(RANDOM()::TEXT),
> ARRAY_TO_STRING(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
>   FROM GENERATE_SERIES(1, 5)), '')
>   FROM rand
>   WHERE num < 5
> )
> SELECT * FROM rand;

> A typical result is shown below:

> 1  974ee059a1902e5ca1ec73c91275984b GYXYS
> 2  6cf5a974d5859eae23cdb9c310e3a3bf   YFDPT
> 3  fa6be95eb720fe6f80c7c8fb6ba11171 YFDPT
> 4  fa54913b0bb43de0025b153fd71a5334  YFDPT
> 5  523fab9bdc6c4c51a89e0d901273fb69   YFDPT

> The fact that the last 4 are identical is not a coincidence. If I put
> 100 in the GENERATE_SERIES, I still get the same result, the first and
> second records are different, but ALL subsequent instances of the
> ARRAY_TO_STRING are identical!

Yeah, it's weird.  A look at EXPLAIN VERBOSE offers some insight:

   QUERY PLAN   


 CTE Scan on rand  (cost=4.75..5.37 rows=31 width=68)
   Output: rand.num, rand.md, rand.a_2_s
   CTE rand
 ->  Recursive Union  (cost=0.13..4.75 rows=31 width=68)
   ->  Result  (cost=0.13..0.15 rows=1 width=68)
 Output: 1, md5((random())::text), array_to_string($1, ''::text)
 InitPlan 1 (returns $1)
   ->  Function Scan on pg_catalog.generate_series  
(cost=0.00..0.13 rows=5 width=32)
 Output: chr((('65'::double precision + round((random() 
* '25'::double precision::integer)
 Function Call: generate_series(1, 5)
   ->  WorkTable Scan on rand rand_1  (cost=0.13..0.40 rows=3 width=68)
 Output: (rand_1.num + 1), md5((random())::text), 
array_to_string($2, ''::text)
 Filter: (rand_1.num < 5)
 InitPlan 2 (returns $2)
   ->  Function Scan on pg_catalog.generate_series 
generate_series_1  (cost=0.00..0.13 rows=5 width=32)
 Output: chr((('65'::double precision + round((random() 
* '25'::double precision::integer)
 Function Call: generate_series(1, 5)
(17 rows)

The ARRAY sub-selects are being done as initplans, not subplans,
which means they're only done once not once per row.  This is correct
so far as the planner is concerned because those sub-selects are
"uncorrelated", ie they use no values from the outer query.

There is room to argue that because the sub-selects contain volatile
functions, they ought not be optimized into initplans.  We have
traditionally not considered that, however, and I'm afraid that a
lot of people have written queries that depend on it.  For example,
there's lore out there that replacing
WHERE mycol < random()
with
WHERE mycol < (SELECT random())
will freeze the random() result as a single value rather than
computing a new value for each row, which sometimes is what you
need.  These days, better practice would be to put the random()
call in a CTE, but there's still a lot of code out there that
does it as above.

For your immediate problem, since you don't care that much
(I suppose) about exactly how the strings are generated, you
could fix the issue by making the sub-selects depend on
"num" somehow.  Or possibly there's a way to do it without
a sub-select.  On the whole this looks like a mighty expensive
way to generate a random string, so I'd be inclined to look
for other implementations.

regards, tom lane

Re: Recursive CTEs and randomness - is there something I'm missing?

[Adrian Klaver]

On 2/28/20 9:45 AM, Tom Lane wrote:

=?UTF-8?B?UMOzbCBVYSBMYW/DrW5lY2jDoWlu?=  writes:

The SQL:



WITH RECURSIVE rand (num, md, a_2_s) AS
(
   SELECT
 1,
 MD5(RANDOM()::TEXT),
 ARRAY_TO_STRING(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
   FROM GENERATE_SERIES(1, 5)), '')
   UNION
 SELECT num + 1,
 MD5(RANDOM()::TEXT),
 ARRAY_TO_STRING(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer)
   FROM GENERATE_SERIES(1, 5)), '')
   FROM rand
   WHERE num < 5
)
SELECT * FROM rand;



A typical result is shown below:



1  974ee059a1902e5ca1ec73c91275984b GYXYS
2  6cf5a974d5859eae23cdb9c310e3a3bf   YFDPT
3  fa6be95eb720fe6f80c7c8fb6ba11171 YFDPT
4  fa54913b0bb43de0025b153fd71a5334  YFDPT
5  523fab9bdc6c4c51a89e0d901273fb69   YFDPT



The fact that the last 4 are identical is not a coincidence. If I put
100 in the GENERATE_SERIES, I still get the same result, the first and
second records are different, but ALL subsequent instances of the
ARRAY_TO_STRING are identical!


Yeah, it's weird.  A look at EXPLAIN VERBOSE offers some insight:

QUERY PLAN

  CTE Scan on rand  (cost=4.75..5.37 rows=31 width=68)
Output: rand.num, rand.md, rand.a_2_s
CTE rand
  ->  Recursive Union  (cost=0.13..4.75 rows=31 width=68)
->  Result  (cost=0.13..0.15 rows=1 width=68)
  Output: 1, md5((random())::text), array_to_string($1, 
''::text)
  InitPlan 1 (returns $1)
->  Function Scan on pg_catalog.generate_series  
(cost=0.00..0.13 rows=5 width=32)
  Output: chr((('65'::double precision + 
round((random() * '25'::double precision::integer)
  Function Call: generate_series(1, 5)
->  WorkTable Scan on rand rand_1  (cost=0.13..0.40 rows=3 width=68)
  Output: (rand_1.num + 1), md5((random())::text), 
array_to_string($2, ''::text)
  Filter: (rand_1.num < 5)
  InitPlan 2 (returns $2)
->  Function Scan on pg_catalog.generate_series 
generate_series_1  (cost=0.00..0.13 rows=5 width=32)
  Output: chr((('65'::double precision + 
round((random() * '25'::double precision::integer)
  Function Call: generate_series(1, 5)
(17 rows)

The ARRAY sub-selects are being done as initplans, not subplans,
which means they're only done once not once per row.  This is correct
so far as the planner is concerned because those sub-selects are
"uncorrelated", ie they use no values from the outer query.

There is room to argue that because the sub-selects contain volatile
functions, they ought not be optimized into initplans.  We have
traditionally not considered that, however, and I'm afraid that a
lot of people have written queries that depend on it.  For example,
there's lore out there that replacing
WHERE mycol < random()
with
WHERE mycol < (SELECT random())
will freeze the random() result as a single value rather than
computing a new value for each row, which sometimes is what you
need.  These days, better practice would be to put the random()
call in a CTE, but there's still a lot of code out there that
does it as above.

For your immediate problem, since you don't care that much
(I suppose) about exactly how the strings are generated, you
could fix the issue by making the sub-selects depend on
"num" somehow.  Or possibly there's a way to do it without
a sub-select.  On the whole this looks like a mighty expensive
way to generate a random string, so I'd be inclined to look
for other implementations.


An off the cuff Python solution:

CREATE OR REPLACE FUNCTION public.upper_random()
 RETURNS character varying
 LANGUAGE plpythonu
AS $function$
from string import ascii_uppercase as au
import random
return  ''.join(random.sample(au,5))
$function$

SELECT
num,
MD5(RANDOM()::TEXT),
upper_random()
FROM
generate_series(1, 5) AS t(num);

 num |   md5| upper_random
-+--+--
   1 | 5896a9e3efa53027873d7999e58904ae | TRGEB
   2 | 9f9677c32a64b6eae73759a69e1acfff | TFQHG
   3 | 5aefda5b498215065e01ba697d79caee | KYBZS
   4 | 1605b7fa54fef9bdc5c49f9b79810e07 | EUDML
   5 | 4ba59880c1c67bca1d1f184bda5350b6 | RFPGO





regards, tom lane





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