Re: How to install check_postgres on CentOS 8?

[Peter J. Holzer]

On 2020-02-26 15:11:38 +0100, rai...@ultra-secure.de wrote:
> I'm trying to install the check_postgres RPM from the official
> postgresql.org repository onto CentOS 8.1
> 
> It says:
> 
> Error:
>  Problem: cannot install the best candidate for the job
>   - nothing provides perl-DateTime-Format-DateParse needed by
> check_postgres-2.25.0-1.rhel8.noarch

So check_postgres depends on a package which isn't in any of your yum
repositories.

It's been some time that I last used RHEL (RHEL 6 was the last I used),
but there were some optional repositories (actually, I think they were
called "channels") with "application specific" packages. Among them was
a developer repo whith lots of additional Perl modules (and other stuff,
but the Perl modules were what is relevant here).

If RHEL 8 (and by extension, CentOS 8) still has that structure, you may
need to configure those repos.

There is also EPEL ("Extra Packages for Redhat Linux"), which contains
packages from Fedora. Maybe perl-DateTime-Format-DateParse is in EPEL?

hp


-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

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

[Peter J. Holzer]

On 2020-02-27 07:41:36 +, sivapostg...@yahoo.com wrote:
> Before taking a few reports, we need to ensure that only one connection is 
> made
> to the database and all other computers need to close the connection to that
> database.   This is to prevent any additional entry(ies) that could be made
> during the course of the report taking.

Do you have control over those reports or are they generated by a
third-party tool?

If the former, the best way is probably to just run them all in a single
REPEATABLE READ transaction. Then they will all reflect the state of the
database at the start of the transaction, regardless of what other
clients are doing in the meantime.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

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

[Ravi Krishna]

> 
> Before taking a few reports, we need to ensure that only one connection is 
> made to the database and all other computers need to close the connection to 
> that database.   This is to prevent any additional entry(ies) that could be 
> made during the course of the report taking.  This single-user mode is to be 
> there till the report is taken and few entries are passed.  
> 
> How to do it?Is it possible to switch to single-user mode from 
> application and back to multi-user mode once the work is completed?  Or any 
> other solution available?
> 
> Happiness Always
> BKR Sivaprakash
> 

This concept is outdated.  You should set your application to correct isolation 
level to get a consistent state.

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

[sivapostg...@yahoo.com]

 Well,
I need to prevent other users from entering any transaction till I finish 
taking reports from my application.  All users will be using the same 
application, from which this report is supposed to be printed.  
If they enter any data, those data also need to be taken into account in this 
report.   The process is somehow lengthy that starts from arriving some 
cumulative value(s) and based on this value, some lengthy process is done.  Any 
data entered in between will affect this report as well the process we do.  
For this same situation, while using SQL Server, we used to count the number of 
users of that database and if it's greater than one, we don't start the 
process.  By checking the same no. of users in vantage point, we could achieve 
the required result.
I tried the same way in Postgres, but I could not get the correct connection 
list, by using this query.
SELECT pid, datname, usename, application_name, client_hostname, client_port, 
backend_start, query_start, query, stateFROM pg_stat_activityWHERE datname = 
'databasename'And   state = 'active'
I expect this query to add the number, when there is another connection from 
one computer.  I couldn't get it when I tried this query from PGAdmin and 
connected this database from another machine.  It's just a connection and no 
query was executed from that machine.
I think setting isolation level will not work out. Switching to single user 
mode, if available, will be better.  
Any ideas ?
Happiness AlwaysBKR Sivaprakash
On Thursday, 27 February, 2020, 04:34:46 pm IST, Ravi Krishna 
 wrote:  
 
 > 
> Before taking a few reports, we need to ensure that only one connection is 
> made to the database and all other computers need to close the connection to 
> that database.  This is to prevent any additional entry(ies) that could be 
> made during the course of the report taking.  This single-user mode is to be 
> there till the report is taken and few entries are passed.  
> 
> How to do it?    Is it possible to switch to single-user mode from 
> application and back to multi-user mode once the work is completed?  Or any 
> other solution available?
> 
> Happiness Always
> BKR Sivaprakash
> 

This concept is outdated.  You should set your application to correct isolation 
level to get a consistent state.
  

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

[Ravi Krishna]

"If they enter any data, those data also need to be taken into account in this 
report. "

Pls read on PG's MVCC architecture.  In SQLServer, unless you enabled its bad 
implementation of Snapshot isolation,
you can't achieve the same.  So it makes sense there.  In PG it is easy to 
ensure that your report gets a point in time
consistent view of the data.

Trying to mimic one database in another is not a smart way.

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

[Ireneusz Pluta/wp.pl]

W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com pisze:
I need to prevent other users from entering any transaction till I finish taking reports from my 
application.  All users will be using the same application, from which this report is supposed to 
be printed.



maybe advisory lock is what you need?

https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS

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

[sivapostg...@yahoo.com]

 Hello,
I'm saying isolation will not work out to my requirement.   The steps.
1.  On completion of all entries by all,  say for a day.2.  Lock, so that no 
one enters any other data.3.  Create a report from the entered data.4.  Create 
/ Modify required entries from the values arrived in the report. [ long process 
]5.  Once completed, commit all data.6.  Unlock, so that other users can enter 
data again.  Data entered will be for another date.  Data cannot [should] not 
entered for the processed date.


On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl 
 wrote:  
 
 W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com pisze:
> I need to prevent other users from entering any transaction till I finish 
> taking reports from my 
> application.  All users will be using the same application, from which this 
> report is supposed to 
> be printed.
>
maybe advisory lock is what you need?

https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS



  

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

[Ireneusz Pluta/wp.pl]

W dniu 2020-02-27 o 14:37, sivapostg...@yahoo.com pisze:

Hello,

I'm saying isolation will not work out to my requirement.   The steps.

1.  On completion of all entries by all,  say for a day.
2.  Lock, so that no one enters any other data.
3.  Create a report from the entered data.
4.  Create / Modify required entries from the values arrived in the report. [ 
long process ]
5.  Once completed, commit all data.
6.  Unlock, so that other users can enter data again.  Data entered will be for another date.  
Data cannot [should] not entered for the processed date.


so wouldn't pg_advisory_lock() in step 2, and pg_advisory_unlock() in step 6, make the process work 
in the way you expect?


https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS




On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl 
 wrote:


W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com 
 pisze:

> I need to prevent other users from entering any transaction till I finish 
taking reports from my
> application.  All users will be using the same application, from which this 
report is supposed to
> be printed.

>
maybe advisory lock is what you need?

https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS

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

[sivapostg...@yahoo.com]

 Need to lock around 10 tables.  Let me try with pg_advisory_lock().

On Thursday, 27 February, 2020, 07:39:13 pm IST, Ireneusz Pluta/wp.pl 
 wrote:  
 
 W dniu 2020-02-27 o 14:37, sivapostg...@yahoo.com pisze:
> Hello,
>
> I'm saying isolation will not work out to my requirement.   The steps.
>
> 1.  On completion of all entries by all,  say for a day.
> 2.  Lock, so that no one enters any other data.
> 3.  Create a report from the entered data.
> 4.  Create / Modify required entries from the values arrived in the report. [ 
> long process ]
> 5.  Once completed, commit all data.
> 6.  Unlock, so that other users can enter data again.  Data entered will be 
> for another date.  
> Data cannot [should] not entered for the processed date.
>
so wouldn't pg_advisory_lock() in step 2, and pg_advisory_unlock() in step 6, 
make the process work 
in the way you expect?

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

>
>
> On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl 
>  wrote:
>
>
> W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com 
>  pisze:
>
> > I need to prevent other users from entering any transaction till I finish 
> > taking reports from my
> > application.  All users will be using the same application, from which this 
> > report is supposed to
> > be printed.
>
> >
> maybe advisory lock is what you need?
>
> https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
>
>
>
>


  

Is it safe to rename an index through pg_class update?

[Kouber Saparev]

Hello everybody,

Renaming an index the classical way through "ALTER INDEX xxx RENAME TO yyy"
needs an AccessExclusiveLock over the table holding the index (at least on
9.3 it does). Instead, couldn't I simply:

UPDATE pg_class SET relname = 'yyy' WHERE oid = 'xxx'::regclass;

Are there any risks to corrupt the database or to lose concurrent
transactions? I tried to make some tests with parallel queries and locks
over the table, but I did not discover anything special.

--
Kouber Saparev

Re: unexpected behavior with pglogical -- bug?

[Iban Rodriguez]

Exactly same situation here. If I LISTEN for notifications, no notification
is sent by subscriber when changes are received from replication. However,
any local change makes all pending notifications be sent in addition to the
corresponding to the local change.

I have tested it on Postgres 12.1

El jue., 27 feb. 2020 a las 15:29, Achilleas Mantzios (<
ach...@matrix.gatewaynet.com>) escribió:

> Hello
>
> have you tried the same with logical replication in postgresql >= 10?
>
> On 16/6/19 11:45 μ.μ., Torsten Förtsch wrote:
>
> Hi,
>
> out of curiosity I created the following setup, all with 9.6 and pglogical.
>
> D1 is configured as provider with a replication set that contains only 1
> table. Only inserts are replicated.
>
> D2 is configured as subscriber for that replication set. Replication
> works, all inserts on D2 arrive also on D2.
>
> Now, I add the following always firing trigger to the table:
>
> CREATE OR REPLACE FUNCTION notify.trgfn () RETURNS trigger AS $def$
> BEGIN
> PERFORM pg_notify(NEW.channel, NEW.msg);
> RETURN NULL;
> END
> $def$ LANGUAGE plpgsql;
>
> CREATE TRIGGER trg BEFORE INSERT ON notify.notify
> FOR EACH ROW
> EXECUTE PROCEDURE notify.trgfn();
>
> ALTER TABLE notify.notify ENABLE ALWAYS TRIGGER trg;
>
> As you can see, the trigger function should prevent the actual insert and
> only call pg_notify(). In principle this works but there is a catch.
> Notifications generated this way are only delivered after another
> notification genuinely generated on the subscriber node. The channel of
> this notification does not matter. If I replace PERFORM pg_notify() by RAISE
> NOTICE I see the message immediately in the log.
>
> First I thought this is related to session_replication_role=replica. So,
> I tried the direct insert on D2 with this setting using psql. The
> notification was fired immediately. Also, whether the trigger prevents or
> allows the actual insert does not matter. I tried to create the trigger
> function as SECURITY DEFINER and with a specific search_path. That didn't
> help either.
>
> By now I am thinking there must be something missing in pglogical.
>
> Thanks,
> Torsten
>
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>

Re: Is it safe to rename an index through pg_class update?

[Tom Lane]

Kouber Saparev  writes:
> Renaming an index the classical way through "ALTER INDEX xxx RENAME TO yyy"
> needs an AccessExclusiveLock over the table holding the index (at least on
> 9.3 it does). Instead, couldn't I simply:

> UPDATE pg_class SET relname = 'yyy' WHERE oid = 'xxx'::regclass;

There's a lot of stuff like that that you can probably get away with...
but I'm not sure it's prudent to try it on valuable production data.
If it breaks your database nobody is going to have any sympathy for you.

FWIW, I can't immediately think of a reason this would cause a problem,
at least not on 9.4 and up which use MVCC catalog scans.  If you're
really still on 9.3 then it's notably more risky.  In any case, I've
not had any caffeine yet today, so this doesn't count for much.

regards, tom lane

Re: Error “cache lookup failed for function”

[Adrian Klaver]

On 2/26/20 11:37 AM, Albrecht Dreß wrote:

Sorry for the late reply, I've been on a short vacation…

Am 21.02.20 21:44 schrieb(en) Adrian Klaver:

1) From your original post what does the below mean?:

-- add several db functions


One trigger function, plus two “normal” ones, all (yet) unused.


-- replace a DB function:


This was the

---8<
DROP FUNCTION public.get_result2[…]
CREATE FUNCTION public.get_result2[…]
---8<

sequence triggering the error.


2) What do you see when you do?:

select * from pg_proc where oid = 1821571;


Unfortunately, when I saw that restarting the cluster didn't solve the 
issue, I re-installed it from a dump (which appears to be intact, btw), 
but /without/ taking a copy of the original data folder.  Thus, this 
information is lost, sorry.



3) What is the definition for retrieve_single_result()?


Ancient implementation, somewhat similar to get_result2(), but with less 
security and different error checking approach:


---8<
CREATE FUNCTION public.retrieve_single_result(mytaskid bigint, OUT data 
bytea, OUT metadata jsonb) RETURNS record

     LANGUAGE plpgsql STABLE SECURITY DEFINER
     AS $$
DECLARE
     errortext text;
BEGIN
     SELECT results.data, results.metadata, results.errortext FROM 
results LEFT JOIN tasks ON tasks.resultid = results.resultid WHERE 
taskid = mytaskid LIMIT 1 INTO data, metadata, errortext;

     IF errortext IS NOT NULL THEN
     RAISE EXCEPTION 'error result "%"', errortext;
     END IF;
END;
$$;
---8<


4) What does the below show?:

select proname, prosrc from pg_proc  where proname in 
('retrieve_single_result', 'get_result2');


Same problem as with #2, sorry…


Alright, as Tom said upstream, there is not much that can be done with 
information missing. Hopefully it does not happen again, but if it does 
you know what needs to be captured.





Thanks,
Albrecht.



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

Re: Is it safe to rename an index through pg_class update?

[Kouber Saparev]

На чт, 27.02.2020 г. в 17:52 Tom Lane  написа:

> There's a lot of stuff like that that you can probably get away with...
> but I'm not sure it's prudent to try it on valuable production data.
> If it breaks your database nobody is going to have any sympathy for you.
>
> FWIW, I can't immediately think of a reason this would cause a problem,
> at least not on 9.4 and up which use MVCC catalog scans.  If you're
> really still on 9.3 then it's notably more risky.  In any case, I've
> not had any caffeine yet today, so this doesn't count for much.
>

Ah, 9.3 is not using MVCC for system catalogs?... Ouch. Then most probably
it is really not a good idea. That said, I am not modifying table names,
only index names... and I guess the internals, the planner etc. are not
working with names, but with oids instead?

Thanks and caffeine regards,
--
Kouber Saparev

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

[Ireneusz Pluta/wp.pl]

W dniu 2020-02-27 o 15:26, sivapostg...@yahoo.com pisze:

Need to lock around 10 tables.  Let me try with pg_advisory_lock().


I am not sure why you mention table locks at this point. Just in case: with advisory locks you lock 
an "application flow", not database objects.





On Thursday, 27 February, 2020, 07:39:13 pm IST, Ireneusz Pluta/wp.pl 
 wrote:


W dniu 2020-02-27 o 14:37, sivapostg...@yahoo.com 
 pisze:
> Hello,
>
> I'm saying isolation will not work out to my requirement.   The steps.
>
> 1.  On completion of all entries by all,  say for a day.
> 2.  Lock, so that no one enters any other data.
> 3.  Create a report from the entered data.
> 4.  Create / Modify required entries from the values arrived in the report. [ 
long process ]
> 5.  Once completed, commit all data.
> 6.  Unlock, so that other users can enter data again.  Data entered will be 
for another date.
> Data cannot [should] not entered for the processed date.
>
so wouldn't pg_advisory_lock() in step 2, and pg_advisory_unlock() in step 6, 
make the process work
in the way you expect?

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS

>
>
> On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl > wrote:

>
>
> W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com  
> pisze:


>
> > I need to prevent other users from entering any transaction till I finish 
taking reports from my
> > application.  All users will be using the same application, from which this 
report is supposed to
> > be printed.
>
> >
> maybe advisory lock is what you need?
>
> https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
>
>
>
>

Re: Postgres 12.1 : UPPER() in WHERE clause restarts server

[Marc]

Hello Tom,

To whom do we report our findings regarding this issue ?

I can offer you a Belgian waffle to go with you caffeine.

Kindest Regards,



Marc


On 25 Feb 2020, at 10:35, Nick Renders wrote:


Hi Tom,

1. we used the EDB installer.

2. turning JIT off did make the problem go away. So I guess this was 
causing the Postgres process to crash all along.


Thanks for the help,

Nick


On 24 Feb 2020, at 16:24, Tom Lane wrote:


"Nick Renders"  writes:
We have set up a new test environment running PostgreSQL v12.2 on 
macOS

10.14 and the issue is still there.


Some nearby threads prompt these two questions:

1. Are you using your own build, or is this from EDB's installer?

2. If the latter, does turning JIT off ("set jit = off") make the
problem go away?

There is as yet no "native" support for --with-llvm on macOS,
ie Apple themselves don't provide sufficient support for that.
EDB seem to have hacked up something that sort of works, but
only sort of.

regards, tom lane


ARC - your Apple service partner

Re: Postgres 12.1 : UPPER() in WHERE clause restarts server

[Adrian Klaver]

On 2/27/20 9:08 AM, Marc wrote:

Hello Tom,

To whom do we report our findings regarding this issue ?


Since it is an EDB hack I would try the contact form at the bottom of 
the this page:


https://www.enterprisedb.com/downloads/postgres-postgresql-downloads



I can offer you a Belgian waffle to go with you caffeine.

Kindest Regards,



Marc


On 25 Feb 2020, at 10:35, Nick Renders wrote:

Hi Tom,

1. we used the EDB installer.

2. turning JIT off did make the problem go away. So I guess this was
causing the Postgres process to crash all along.

Thanks for the help,

Nick


On 24 Feb 2020, at 16:24, Tom Lane wrote:

"Nick Renders"  writes:

We have set up a new test environment running PostgreSQL
v12.2 on macOS
10.14 and the issue is still there.

Some nearby threads prompt these two questions:

1. Are you using your own build, or is this from EDB's installer?

2. If the latter, does turning JIT off ("set jit = off") make the
problem go away?

There is as yet no "native" support for --with-llvm on macOS,
ie Apple themselves don't provide sufficient support for that.
EDB seem to have hacked up something that sort of works, but
only sort of.

regards, tom lane



*ARC - your Apple Authorised Service partner*   H.D. Saviolaan 8
B-1700 Dilbeek
Belgium
i...@arcict.com  	www.arcict.com 


tel. : +32 (0)2 466 50 00   fax. : +32 (0)2 466 88 33





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

Statistics and Index Usage Data

[Casey Meijer]

Hello all:

I’m curious if there are any ways to query custom/extended multivariate 
statistics usage?  I tried a few web searches, but can’t find anything there or 
in the docs.   I imagine this simply isn’t implemented (is it expensive to keep 
this data?), but just wanted to double check.

Also, is there any way to query index usage based on type of index (brin, gist, 
btree, etc.)?  I couldn’t see anything obvious to do so here: 
https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_index

[FYI: Many of the links at the bottom of the above page (related to index 
pruning) fail on current versions of postgreSQL.]

Best I could come up with so far was a text search for the USING clause in 
indexdef of pg_indexes.


Thanks,

Casey Meijer

Re: Postgres 12.1 : UPPER() in WHERE clause restarts server

[Tom Lane]

Marc  writes:
> To whom do we report our findings regarding this issue ?

EDB is already on it:

https://www.postgresql.org/message-id/CA%2BOCxoz0bWi%2BR2WpocfkD20Lgrg69z1jQ_SZd-zmdzHW0zt%2Bbg%40mail.gmail.com

regards, tom lane

trouble making PG use my Perl

[Kevin Brannen]

We're trying to upgrade our Pg 9.6 installs up to 12.2. In a break from 
tradition where we grab source and compile our own, I've downloaded the 
community RPMs for Centos 6 and installed them (they default into /usr/pgsql-12 
it seems).

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.

Sadly, I can't find anything about this in the docs. Searching online doesn't 
turn up anything either as the search terms are too generic (or I can't find a 
way to make them specific enough to help). Haven't found any options for 
postgresql.conf either.

So how does one set the Perl binary/library at server start time?
Am I going to have to compile my own Pg again to make this work?

Thanks,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Re: trouble making PG use my Perl

[Tom Lane]

Kevin Brannen  writes:
> 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).

plperl.so will typically have a more or less hard-coded path to
libperl.so, eg

$ ldd ...installdir.../lib/plperl.so 
linux-vdso.so.1 =>  (0x7ffc613cf000)
libperl.so => /usr/lib64/perl5/CORE/libperl.so (0x7fa315d48000)
^^
libpthread.so.0 => /lib64/libpthread.so.0 (0x7fa315b11000)
libc.so.6 => /lib64/libc.so.6 (0x7fa31577d000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x7fa315563000)
libnsl.so.1 => /lib64/libnsl.so.1 (0x7fa315349000)
libdl.so.2 => /lib64/libdl.so.2 (0x7fa315145000)
libm.so.6 => /lib64/libm.so.6 (0x7fa314ec1000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x7fa314c89000)
libutil.so.1 => /lib64/libutil.so.1 (0x7fa314a86000)
/lib64/ld-linux-x86-64.so.2 (0x55646d09b000)
libfreebl3.so => /lib64/libfreebl3.so (0x7fa314883000)

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.

regards, tom lane

Re: Is it safe to rename an index through pg_class update?

[Tom Lane]

Kouber Saparev  writes:
> На чт, 27.02.2020 г. в 17:52 Tom Lane  написа:
>> FWIW, I can't immediately think of a reason this would cause a problem,
>> at least not on 9.4 and up which use MVCC catalog scans.  If you're
>> really still on 9.3 then it's notably more risky.  In any case, I've
>> not had any caffeine yet today, so this doesn't count for much.

> Ah, 9.3 is not using MVCC for system catalogs?... Ouch. Then most probably
> it is really not a good idea. That said, I am not modifying table names,
> only index names... and I guess the internals, the planner etc. are not
> working with names, but with oids instead?

The issue is whether a SnapshotNow scan would find any row at all.
If it reaches the new row version before that's committed good, and
the old one after that's committed dead, you'll get some weird
"cache lookup failed" or similar failure --- just transiently, but
nonetheless a failure.  Pre-9.4 versions were dependent on proper
locking to avoid that issue, and what you propose would bypass that.

regards, tom lane

pg_upgrade custom table locations. Move table locations during upgrade?

[Tory M Blue]

Many decades ago (small fib).

There was a write up of someone stopping the postgres upgrade at a certain
point, editing a file with the table locations and then restarting the
upgrade process.

"Now, what are you trying to do?"

I have version specific directories (good for you), but...

/pgsql/9.5/tablespaces

I am updating to 12 and thus

/pgsql/12/tablespaces (is where I would like them).

Using the -link, it simples creates the PG12, files under
/pgsql/9.5/tablespace

I'd like to , even when using link, to say don't do that, but instead
please link the Ver 12 tables to /pgsql/12/tablespaces.

The information is in the file

pg_upgrade_dump_globals.sql:CREATE TABLESPACE "torquespace" OWNER "cls"
LOCATION '/pgsql/9.5/torque';

but while it's there, I'd like it not to be there (and it's possible that
I'm running into an initial design flaw and the table spaces should really
be under /pgsql and not /pgsql/$VERSION/

However is there a way to say create the new links under /pgsql/12/ vs
/pgsql/9.5/ using the pg_upgrade process?

Thanks
Tory

RE: trouble making PG use my Perl

[Kevin Brannen]

 From: Tom Lane 
>Kevin Brannen  writes:
>> 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).
>
>plperl.so will typically have a more or less hard-coded path to libperl.so, eg
>
>$ ldd ...installdir.../lib/plperl.so
>linux-vdso.so.1 =>  (0x7ffc613cf000)
>libperl.so => /usr/lib64/perl5/CORE/libperl.so (0x7fa315d48000)
>^^
>
>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.
>
>regards, tom lane


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

It'd be nice if we could set Perl at server start time, but I can see how
it might not be possible.

I'll see about making this extension versus just recompiling the whole thing.
I don't recall building Pg to be all that hard, I was just hoping to avoid it.

-OR- I blast it from orbit and rewrite the 2 plperl functions so I don't need 
the
extension. Decisions ... decisions ...

Thanks!
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

A question relative to creating an audit table

[stan]

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?

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

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

[Adrian Klaver]

On 2/27/20 1:28 PM, 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?



Asked and answered:

https://www.postgresql.org/message-id/CAKFQuwY=wxxlzbjmv5ulk4p4b5sf+fnzxwthp-ugere-nxe...@mail.gmail.com

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

Re: pg_upgrade custom table locations. Move table locations during upgrade?

[Tory M Blue]

Finally found it

http://bajis-postgres.blogspot.com/2014/04/anyone-wants-to-change-tablespaces.html

Thanks
Tory

On Thu, Feb 27, 2020 at 12:40 PM Tory M Blue  wrote:

> Many decades ago (small fib).
>
> There was a write up of someone stopping the postgres upgrade at a certain
> point, editing a file with the table locations and then restarting the
> upgrade process.
>
> "Now, what are you trying to do?"
>
> I have version specific directories (good for you), but...
>
> /pgsql/9.5/tablespaces
>
> I am updating to 12 and thus
>
> /pgsql/12/tablespaces (is where I would like them).
>
> Using the -link, it simples creates the PG12, files under
> /pgsql/9.5/tablespace
>
> I'd like to , even when using link, to say don't do that, but instead
> please link the Ver 12 tables to /pgsql/12/tablespaces.
>
> The information is in the file
>
> pg_upgrade_dump_globals.sql:CREATE TABLESPACE "torquespace" OWNER "cls"
> LOCATION '/pgsql/9.5/torque';
>
> but while it's there, I'd like it not to be there (and it's possible that
> I'm running into an initial design flaw and the table spaces should really
> be under /pgsql and not /pgsql/$VERSION/
>
> However is there a way to say create the new links under /pgsql/12/ vs
> /pgsql/9.5/ using the pg_upgrade process?
>
> Thanks
> Tory
>

Re: A question relative to creating an audit table

[Vik Fearing]

On 27/02/2020 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.

There are extensions out there that deal with this for you.  Here is
one: https://github.com/xocolatl/periods/

> Can a function, called by a trigger, determine what table it was called
> for?

Yes.
-- 
Vik Fearing

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

[sivapostg...@yahoo.com]

 Then clearly I've misunderstood what advisory lock could do.   We used to put 
locks in SQL server to avoid deadlock situations.  I thought advisory lock is a 
similar one.   [ New to Postgres ]
The report is arrived from around 10 tables out of 300 tables that are in the 
database.  Once we start this process, we need to ensure that no other user 
could enter any data in those 10 tables, at least for the processing period.  I 
thought the table lock [ those 10 tables ] will ensure no entry.  
We have a menu like this in our application
Purchase EntrySales EntrySales CancellationReport    Processing report

When we enter the Processing report and click process, we need to ensure that 
no one could enter data from Purchase Entry, Sales Entry, Sales Cancellation, 
etc.
Couldn't understand how advisory lock could achieve this?
Happiness AlwaysBKR Sivaprakash
On Thursday, 27 February, 2020, 10:04:12 pm IST, Ireneusz Pluta/wp.pl 
 wrote:  
 
 W dniu 2020-02-27 o 15:26, sivapostg...@yahoo.com pisze:
> Need to lock around 10 tables.  Let me try with pg_advisory_lock().

I am not sure why you mention table locks at this point. Just in case: with 
advisory locks you lock 
an "application flow", not database objects.

>
>
> On Thursday, 27 February, 2020, 07:39:13 pm IST, Ireneusz Pluta/wp.pl 
>  wrote:
>
>
> W dniu 2020-02-27 o 14:37, sivapostg...@yahoo.com 
>  pisze:
> > Hello,
> >
> > I'm saying isolation will not work out to my requirement.   The steps.
> >
> > 1.  On completion of all entries by all,  say for a day.
> > 2.  Lock, so that no one enters any other data.
> > 3.  Create a report from the entered data.
> > 4.  Create / Modify required entries from the values arrived in the report. 
> > [ long process ]
> > 5.  Once completed, commit all data.
> > 6.  Unlock, so that other users can enter data again.  Data entered will be 
> > for another date.
> > Data cannot [should] not entered for the processed date.
> >
> so wouldn't pg_advisory_lock() in step 2, and pg_advisory_unlock() in step 6, 
> make the process work
> in the way you expect?
>
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
>
> >
> >
> > On Thursday, 27 February, 2020, 06:29:00 pm IST, Ireneusz Pluta/wp.pl 
> >  > wrote:
> >
> >
> > W dniu 2020-02-27 o 12:35, sivapostg...@yahoo.com 
> >  
> > pisze:
>
> >
> > > I need to prevent other users from entering any transaction till I finish 
> > > taking reports from my
> > > application.  All users will be using the same application, from which 
> > > this report is supposed to
> > > be printed.
> >
> > >
> > maybe advisory lock is what you need?
> >
> > https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS
> >
> >
> >
> >
>
>


  

table name "unnest" specified more than once

[Guyren Howe]

# select
 * 
from 
unnest(array[array['a', 'b'], array['c', 'c']]), 
unnest(array[array['1', '2'], array['3', '4']]);
ERROR:  42712: table name "unnest" specified more than once

I’m trying to cross-join multiple two-dimensional arrays, expecting to retain 
the inner arrays. I’ve been trying for hours without luck; the ever-esoteric 
SQL syntax foils me at every turn.

It’s a shame I can’t get the unnest function not to just concatenate the inner 
arrays if I just put a bunch of them. This doesn’t strike me as optimal 
behavior.

For more context, I’m trying to make a system of functions to score a Texas 
Hold ‘Em game. So I have a card type consisting of a pair of suit and rank, and 
I’m tossing them about. The cross-join is so I can build all candidate hands 
for scoring. I’m trying to create a function I can call like this:

select
best_hands_with_river(
array[
c('H', 'K'), 
c('D', 'A')
],
array[
c('C', '2'),
c('C', 'K'),
c('S', 'K'),
c('H', 'A'),
c('C', 'A')
])

Here, c is a function that constructs a card type. Card is a ROW(varchar, 
varchar).

So: how do I cross-join three identical arrays of my card type?

Re: table name "unnest" specified more than once

[David G. Johnston]

On Thursday, February 27, 2020, Guyren Howe  wrote:

> # select
>  *
> from
> unnest(array[array['a', 'b'], array['c', 'c']]),
> unnest(array[array['1', '2'], array['3', '4']]);
> ERROR:  42712: table name "unnest" specified more than once
>

This specific error is resolved by using the alias feature of the FROM
clause:

alias

A substitute name for the FROM item containing the alias. An alias is used
for brevity or to eliminate ambiguity for self-joins (where the same table
is scanned multiple times)

>
> I’m trying to cross-join multiple two-dimensional arrays, expecting to
> retain the inner arrays. I’ve been trying for hours without luck; the
> ever-esoteric SQL syntax foils me at every turn.
>
> It’s a shame I can’t get the unnest function not to just concatenate the
> inner arrays if I just put a bunch of them. This doesn’t strike me as
> optimal behavior.
>

Putting unnest in a select-list might get you closer to your goal.  Or the
array-array concatenating operator.


>
> For more context, I’m trying to make a system of functions to score a
> Texas Hold ‘Em game. So I have a card type consisting of a pair of suit and
> rank, and I’m tossing them about. The cross-join is so I can build all
> candidate hands for scoring. I’m trying to create a function I can call
> like this:
>
> select
> best_hands_with_river(
> array[
> c('H', 'K'),
> c('D', 'A')
> ],
> array[
> c('C', '2'),
> c('C', 'K'),
> c('S', 'K'),
> c('H', 'A'),
> c('C', 'A')
> ])
>
> Here, c is a function that constructs a card type. Card is a ROW(varchar,
> varchar).
>
> So: how do I cross-join three identical arrays of my card type?
>

Something like:

Select f1.a, f2.b
>From (select * from unnest(arr)) as f1 (a)
Cross join (select * From unnest(arr)) as f2 (b)
Etc...Maybe with parentheses...

There may be a more succinct way to write this but going verbose until it
works minimizes the amount of syntax you need to deal with.  Though I
personally encourage writing intentional cross join (really, all joins)
using join syntax instead of comma-separated from items.

David J.

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

[rob stone]

Hello,

On Fri, 2020-02-28 at 01:10 +, sivapostg...@yahoo.com wrote:
> Then clearly I've misunderstood what advisory lock could do.   We
> used to put locks in SQL server to avoid deadlock situations.  I
> thought advisory lock is a similar one.   [ New to Postgres ]
> 
> The report is arrived from around 10 tables out of 300 tables that
> are in the database.  Once we start this process, we need to ensure
> that no other user could enter any data in those 10 tables, at least
> for the processing period.  I thought the table lock [ those 10
> tables ] will ensure no entry.  
> 
> We have a menu like this in our application
> 
> Purchase Entry
> Sales Entry
> Sales Cancellation
> Report
> Processing report
> 
> When we enter the Processing report and click process, we need to
> ensure that no one could enter data from Purchase Entry, Sales Entry,
> Sales Cancellation, etc.
> 

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

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

[Rob Sargent]

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?

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

[Pól Ua Laoínecháin]

Hi all,

I'm trying to generate a series of random strings (capital A-Z)
between 2 and 5 characters long (say, 10^6).

I'm using a recursive CTE to achieve this.

A fiddle is available here:

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=206a0c522e853f043c7e633d19852de2

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

Now, the interesting thing is the ARRAY_TO_STRING.

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! You can test this on the fiddle!

Now, I'm puzzled by this, since the MD5 records are ALWAYS different.

I would be grateful if anybody could explain this - I need the
ARRAY_TO_STRING, because an MD5 cannot be guaranteed to have at least
5 letters.

Should you require any further information, please don't hesitate to contact me.

TIA and rgs,


Pól...