Re: Parameter value from (mb/gb) to bytes

[Raul Kaubi]

Hi

Thanks, you solution works for most of the databases. But now I noticed
that we have single 9.5 version also.
And seems like this function *pg_size_bytes* came from 9.6

# psql -U postgres -Atc "select
> pg_size_bytes(current_setting('shared_buffers'));"
> ERROR:  function pg_size_bytes(text) does not exist
> LINE 1: select pg_size_bytes(current_setting('shared_buffers'));
>^
> HINT:  No function matches the given name and argument types. You might
> need to add explicit type casts.


Do you perhaps happen to know a way how to get this to work in 9.5 also..?

Thanks again.

Regards
Raul

Kontakt Magnus Hagander () kirjutas kuupäeval K, 14.
oktoober 2020 kell 18:38:

>
>
> On Wed, Oct 14, 2020 at 5:23 PM Tom Lane  wrote:
>
>> Magnus Hagander  writes:
>> > On Wed, Oct 14, 2020 at 5:10 PM Tom Lane  wrote:
>> >> It's fairly annoying that this doesn't work:
>> >> regression=# select pg_size_bytes(setting||' '||unit) from pg_settings
>> >> where name = 'shared_buffers';
>>
>> > Actually thinking though, surely *this* particular case can be spelled
>> as:
>> > SELECT  pg_size_bytes(current_setting('shared_buffers'))
>>
>> Yeah, that might be the most recommendable way.
>>
>> > Or if doing it off pg_settings:
>> > SELECT setting::bigint * pg_size_bytes(unit) from pg_settings where
>> > name='shared_buffers'
>>
>> No, because that will fail for any unit other than '8kB', eg
>>
>> regression=# select pg_size_bytes('MB');
>> ERROR:  invalid size: "MB"
>>
>
> Right, but it would certainly work for *this* case using pg_asettings, is
> what I meant.
>
> That said, I think it'd then actually be better to teach pg_size_bytes to
> know that "MB" is the same as "1MB" and parse that. That might be something
> that would actually be useful in other cases as well -- basically as a way
> to get conversion units in general. Basically if the string is "unit only"
> then consider that as "1 unit".
>
> --
>  Magnus Hagander
>  Me: https://www.hagander.net/ 
>  Work: https://www.redpill-linpro.com/ 
>

Meaning of below statement

[Srinivasa T N]

Hi,
   I have the following in my log files:

2020-11-20 11:20:46.216 IST [38207] LOG: execute S_1/C_2: SELECT
"gid",encode(ST_AsBinary(ST_Simplify(ST_Force2D("shape"),
14.929338247701526, true)),'base64') as "shape" FROM
"ami_smart_new"."aoi_boundary" WHERE ("sectioncode" IN ('4683', '4587') AND
"sectioncode" IS NOT NULL AND "shape" && ST_GeomFromText('POLYGON
((683696.123647752 989199.9990667417, 683696.123647752 1000723.135701899,
708574.8226023088 1000723.135701899, 708574.8226023088 989199.9990667417,
683696.123647752 989199.9990667417))', 32643))
2020-11-20 11:20:46.218 IST [38207] LOG: execute S_3: ROLLBACK

Does it mean that there was an error in "SELECT ..." and hence internally
postgres executed ROLLBACK??

Regards,
Seenu.

Set COLLATE on a session level

[Dirk Mika]

Hello,

we come from the Oracle world and we have an application that, depending on a 
setting, sends the command ALTER SESSION SET NLS_SORT=... when connecting to 
the database.

Is there a similar way to set a COLLATE for a session in PostgreSQL?

I know that I can specify a COLLATE for a SELECT statement in the ORDER BY 
Clause, but then I would have to adjust the statements in the client and 
statements that are automatically generated by the database components used, 
would not be affected.

Regards
Dirk



--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.m...@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika



## INEOS 1:59 memories
## https://www.facebook.com/mikatiming

Re: Locking and postgres_fdw extension

[Tom Lane]

Steve Baldwin  writes:
> However if I want it to fail immediately if unable to obtain the lock, it
> seems nowait is ignored for foreign tables (or at least with my version of
> postgres_fdw).

Hmm ... this seems to be intentional, per the comments in
deparseLockingClause():

 * For now, just ignore any [NO] KEY specification, since (a)
 * it's not clear what that means for a remote table that we
 * don't have complete information about, and (b) it wouldn't
 * work anyway on older remote servers.  Likewise, we don't
 * worry about NOWAIT.

However, it's not super clear whether the concern about NOWAIT is just
that the remote server might be too old to know that keyword, or
whether there's some deeper issue.

(I see your example is also falling foul of the non-implementation
of NO KEY, which might be confusing matters still more.)

regards, tom lane

Re: received immediate shutdown request caused cluster failover

[Tom Lane]

Yi Sun  writes:
> Besides command run(like pg_ctl) can cause "received immediate shutdown
> request"  any other reason can cause this please?

That message indicates that something sent the postmaster process a
SIGQUIT signal (which is all that "pg_ctl stop -m immediate" does).
There's no speculation to that: a look at postmaster.c will convince
you that there is no other way to reach that message.  So you need
to be looking for things that would be sending SIGQUIT unexpectedly.

I don't know much about Patroni, but maybe something in that
environment thinks that SIGQUIT'ing random processes is a good
thing to do.

regards, tom lane

Re: received immediate shutdown request caused cluster failover

[Yi Sun]

Hi guys,

Besides command run(like pg_ctl) can cause "received immediate shutdown
request"  any other reason can cause this please?

This production DB, support colleague said didn't run it

Yi Sun  于2020年11月18日周三 上午11:54写道：

> Hi all
>
> There are 3 nodes in our prd db in patroni cluster, vm01 is leader, vm02
> and vm03 are standby, vm01 received immediate shutdown request  caused
> failover to vm02, after that vm03 received fast shutdown request
>
> As vm03 not in cluster so have to reinit vm03
>
> What's the possible root caused vm01 received immediate shutdown request
> please?
>
> and What's the possible root caused vm03 received fast shutdown request
>
> Below are detail info, please check, if need any other info, I will
> provide, thanks
>
> postgresql version: 11.4
> OS: centos 7
> HA: patroni upgraded from 1.6.3 to 2.0.1 these days
>
> --vm01 pg log
>
> [2020-11-16 12:03:56.592 UTC] p=31485:3@ c=
> pgwatch2@127.0.0.1/eu4_baas_bckp_epmgr:pgwatch2 LOG:  disconnection:
> session time: 0:00:00.037 user=pgwatch2 database=eu4_baas_bckp_epmgr
> host=127.0.0.1 port=36916
> [2020-11-16 12:03:57.005 UTC] p=32103:14@ c=@/: LOG:  received immediate
> shutdown request
> [2020-11-16 12:03:57.017 UTC] p=31189:3@314/0 c=
> atp_si_user@10.253.85.203/eu4_baas_software_inventory:[unknown]
> 
> WARNING:  terminating connection because of crash of another server process
> [2020-11-16 12:03:57.017 UTC] p=31189:4@314/0 c=
> atp_si_user@10.253.85.203/eu4_baas_software_inventory:[unknown]
> 
> DETAIL:  The postmaster has commanded this server process to roll back the
> current transaction and exit, because another server process exited
> abnormally and possibly corrupted shared memory.
> [2020-11-16 12:03:57.017 UTC] p=31189:5@314/0 c=
> atp_si_user@10.253.85.203/eu4_baas_software_inventory:[unknown]
> 
> HINT:  In a moment you should be able to reconnect to the database and
> repeat your command.
> [2020-11-16 12:03:57.017 UTC] p=31183:3@313/0 c=
> atp_pm@10.253.86.62/eu4_baas_atp_pm:[unknown]
>  WARNING:
>  terminating connection because of crash of another server process
> [2020-11-16 12:03:57.017 UTC] p=31183:4@313/0 c=
> atp_pm@10.253.86.62/eu4_baas_atp_pm:[unknown]
>  DETAIL:  The
> postmaster has commanded this server process to roll back the current
> transaction and exit, because another server process exited abnormally and
> possibly corrupted shared memory.
> [2020-11-16 12:03:57.017 UTC] p=31183:5@313/0 c=
> atp_pm@10.253.86.62/eu4_baas_atp_pm:[unknown]
>  HINT:  In a
> moment you should be able to reconnect to the database and repeat your
> command.
> [2020-11-16 12:03:57.017 UTC] p=31182:3@310/281059 c=
> bckp_epmgr@10.253.85.202/eu4_baas_bckp_epmgr:[unknown]
> 
> WARNING:  terminating connection because of crash of another server process
> [2020-11-16 12:03:57.017 UTC] p=31182:4@310/281059 c=
> bckp_epmgr@10.253.85.202/eu4_baas_bckp_epmgr:[unknown]
> 
> DETAIL:  The postmaster has commanded this server process to roll back the
> current transaction and exit, because another server process exited
> abnormally and possibly corrupted shared memory.
> [2020-11-16 12:03:57.017 UTC] p=31182:5@310/281059 c=
> bckp_epmgr@10.253.85.202/eu4_baas_bckp_epmgr:[unknown]
>  HINT:
>  In a moment you should be able to reconnect to the database and repeat
> your command.
>
> --vm01 patroni log
> Nov 16 12:03:56 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
> "2020-11-16 12:03:56,922", "name": "patroni.dcs.consul", "process": 32085,
> "thread": 140350381303616, "level": "WARNING", "message": "Could not
> register service: unknown role type promoted"}
> Nov 16 12:03:56 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
> "2020-11-16 12:03:56,923", "name": "patroni.ha", "process": 32085,
> "thread": 140350381303616, "level": "INFO", "message": "Lock owner:
> eu4-baas-patroni-cluster-vm02; I am eu4-baas-patroni-cluster-vm01"}
> Nov 16 12:03:56 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
> "2020-11-16 12:03:56,923", "name": "patroni.ha", "process": 32085,
> "thread": 140350381303616, "level": "INFO", "message": "does not have lock"}
> Nov 16 12:03:59 eu4-baas-patroni-cluster-vm01 patroni[32085]: {"time":
> "2020-11-16 12:03:59,158", "name": "patroni.dcs.consul", "process": 32085,
> "thread": 140350381303616, "level": "INFO", "message": "Deregister service
> pgcluster11/eu4-baas-patroni-cluster-vm01"}
> Nov 16 

Locking and postgres_fdw extension

[Steve Baldwin]

Hi all,

I have two DB instances - 'online' and 'offline'. In 'offline' I have a
foreign table 'f' imported from 'online'. I want to execute a query from
'offline' to obtain a row lock, and this works fine:

select id from f where id = 1 for no key update;

However if I want it to fail immediately if unable to obtain the lock, it
seems nowait is ignored for foreign tables (or at least with my version of
postgres_fdw). The waiting session blocks until the holding session commits
or rolls back.

My 'workaround' was to create a view in 'online':

create or replace view f_lock as select * from f for no key update nowait;

Then use import foreign schema to bring that view into 'offline'. I can
then execute:

select id from f_lock where id = 1;

and it will fail immediately if the row is already locked.

Is there a better way to do this?

Thanks,

Steve

Re: Upgrade 9.4 to 12 on windows system

[Asya Nevra Buyuksoy]

I set my firewall to off however there is no change.
I think my problem is windows user name tries to login with the wrong name
instead of postgres user name.

2020-11-19 04:06:10 PST FATAL:  role "WIN-EGPKV5AU75R$" does not exist
(pg_log file)

my user name seemed like  WIN-EGPKV5AU75R\postgres
How can I fix this issue?


Adrian Klaver , 19 Kas 2020 Per, 18:17 tarihinde
şunu yazdı:

> On 11/19/20 7:08 AM, Andreas Kretschmer wrote:
> >
> >
> > Am 19.11.20 um 15:05 schrieb Asya Nevra Buyuksoy:
> >>
> >>
> >> connection to database failed: could not connect to server: Connection
> >> refused (0x274D/10061)
> >> Is the server running on host "localhost" (::1) and accepting
> >> TCP/IP connections on port 50432?
> >> could not connect to server: Connection refused (0x274D/10061)
> >> Is the server running on host "localhost" (127.0.0.1) and accepting
> >> TCP/IP connections on port 50432?
> >>
> >
> >
> > have you a firewall installed on the machine? Is port 50432 blocked?
> > Default port for PostgreSQL is 5432, not 50432.
>
> Port 50432 is what pg_upgrade uses to stay clear of existing ports:
>
> https://www.postgresql.org/docs/12/pgupgrade.html
>
> "Obviously, no one should be accessing the clusters during the upgrade.
> pg_upgrade defaults to running servers on port 50432 to avoid unintended
> client connections."
>
>
>
> > Andreas
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

Re: maintenance_work_mem

[Philip Semanchuk]

> On Nov 19, 2020, at 3:05 AM, Atul Kumar  wrote:
> 
> Hi,
> 
> I have below queries:
> 
> 1. How do i check the maintenance_work_mem for current session, before
> setting some other value for this parameter for the same session.
> 
> 2. and How do I set maintenance_work_mem for a session only, and how
> will it be "rollback" once my maintainance work is done, Do I need to
> execute any command for that or just closing the session will rollback
> what I set for the session.

In addition to Andreas’ helpful references to SHOW and SET, there’s also 
pg_settings --

https://www.postgresql.org/docs/13/view-pg-settings.html


Cheers
Philip

Re: Upgrade 9.4 to 12 on windows system

[Adrian Klaver]

On 11/19/20 7:08 AM, Andreas Kretschmer wrote:



Am 19.11.20 um 15:05 schrieb Asya Nevra Buyuksoy:



connection to database failed: could not connect to server: Connection 
refused (0x274D/10061)

Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 50432?
could not connect to server: Connection refused (0x274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 50432?




have you a firewall installed on the machine? Is port 50432 blocked? 
Default port for PostgreSQL is 5432, not 50432.


Port 50432 is what pg_upgrade uses to stay clear of existing ports:

https://www.postgresql.org/docs/12/pgupgrade.html

"Obviously, no one should be accessing the clusters during the upgrade. 
pg_upgrade defaults to running servers on port 50432 to avoid unintended 
client connections."





Andreas





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

Re: Upgrade 9.4 to 12 on windows system

[Andreas Kretschmer]

Am 19.11.20 um 15:05 schrieb Asya Nevra Buyuksoy:



connection to database failed: could not connect to server: Connection 
refused (0x274D/10061)

Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 50432?
could not connect to server: Connection refused (0x274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 50432?




have you a firewall installed on the machine? Is port 50432 blocked? 
Default port for PostgreSQL is 5432, not 50432.



Andreas


--
2ndQuadrant, an EDB company
www.2ndQuadrant.com / www.enterprisedb.com

Re: create type with %type or %rowtype

[Adrian Klaver]

On 11/18/20 10:04 PM, Paul Förster wrote:

Hi,


On 18. Nov, 2020, at 22:08, Post Gresql  wrote:

I might be stupid, but where in the document for create function does it say 
that the return type can be a table?

 From the doc for version 13 
https://www.postgresql.org/docs/13/sql-createfunction.html

"rettype
The return data type (optionally schema-qualified). The return type can be a base, 
composite, or domain type, or can reference the type of a table column."


right in the syntax:

CREATE [ OR REPLACE ] FUNCTION
 name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr
  ] [, ...] ] )
 [ RETURNS rettype
   | RETURNS TABLE ( column_name column_type [, ...] ) ]

"RETURNS TABLE(...)" is probably what you're looking for?


That is a variation on the theme. The OP was looking for declaring a 
table%ROWTYPE in RETURNS rettype. You can do that by using the table 
composite type. Since RETURNS TABLE is essentially an alias for RETURNS 
SETOF you can use it to return a set of the table composite type. Though 
in RETURNS TABLE you can also 'create' your own table that has nothing 
to do with an existing table composite type.




Cheers,
Paul




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

Upgrade 9.4 to 12 on windows system

[Asya Nevra Buyuksoy]

Hello my friends,

I want to upgrade my Postgres DBMS 9.4 to 12 on the Windows system. I
follow these steps on this website. (
https://qasymphony.zendesk.com/hc/en-us/articles/115002398166-09-Upgrade-PostgreSQL-9-4-to-9-5-on-Windows
)
However when everything goes ok, the same error is raising in every cmd
running.

Performing Consistency Checks
-
Checking cluster versions   ok

*failure*
Consult the last few lines of "pg_upgrade_server_start.log" or
"pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: Connection
refused (0x274D/10061)
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 50432?
could not connect to server: Connection refused (0x274D/10061)
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 50432?
could not connect to source postmaster started with the command:
"C:/Program Files/PostgreSQL/9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log"
-D "C:/Program Files/PostgreSQL/9.4/data" -o "-p 50432 -b " start

I ran this upgrade on Windows Server 12 64 bit.  I researched this error
and why it raises but I don't find any proper solution. If anyone relates
this problem, I can share my log files.

Thanks.

Linux package upgrade without dependency conflicts

[Zwettler Markus (OIZ)]

We run Postgres 9.6 + 12 Community Edition on RHEL7 which we install directly 
out of the PGDG channels using RPMs. We also run Patroni installed with RPMs 
provided by Github.

Currently we have major dependency conflicts with each quarterly Linux package 
upgrade (yum upgrade), especially on PostGIS and Patroni.

I was told that there will be no dependency conflicts anymore when we install 
Postgres from sourcecode and Patroni with pip.

Is that correct? Because all Linux packages required by Postgres will continue 
to be updated.

-Markus

Re: Multiple result set to be returned in procedure/function

[Thomas Kellerer]

Daniel Verite schrieb am 19.11.2020 um 13:06:
>>arthur=> select * from get_results();
>>get_results
>>
>> 
>> 
>
> Friendlier names may be used by assigning them in the function,
> i.e. plpgsql does support:
>
>  declare
>   c1 refcursor := 'mycursorname';
>
> Then the caller might simply hardcode the cursor names in the FETCH
> statements rather than building them dynamically at runtime.

Ah, cool.

That is indeed much easier to work with (in case I ever have to)

Thomas

Multiple result set not working

[Muthukumar.GK]

  Hi team,

is it possible to return Multiple results set from procedure/function on
single execution. Please advise me on this. I have written sample code
below and when i execute it i dont get any result set

CREATE OR REPLACE FUNCTION multiResultset(

ref1 refcursor,

ref2 refcursor)

RETURNS SETOF refcursor

AS $$

BEGIN



  open ref1 for

select * from los_approverreassignwhere pk_id= 10;



  open ref2 for

select * from los_approverreassignwhere pk_id= 11;



END;

$$

LANGUAGE 'plpgsql';


[image: image.png]


Regards

Muthu

Re: Multiple result set to be returned in procedure/function

[Daniel Verite]

Thomas Kellerer wrote:

>arthur=> \set AUTOCOMMIT off

Alternatively, start an explicit transaction block with BEGIN.
The point is that the lifespan of the cursor is the transaction block
in which it's instantiated.
 
>arthur=> select * from get_results();
>get_results
>
> 
> 

Friendlier names may be used by assigning them in the function,
i.e. plpgsql does support:

 declare
  c1 refcursor := 'mycursorname';

Then the caller might simply hardcode the cursor names in the FETCH
statements rather than building them dynamically at runtime.

Also it allows to put the server-side code into an anymous DO block
instead of creating a function, because it doesn't have to return
any refcursor when the caller already knows the cursor names.

BEGIN;
DO $$
declare c refcursor := 'c_pgclass';
begin
  open c for select relname from pg_class;
end $$ LANGUAGE plpgsql;

FETCH ALL from c_pgclass;
...
COMMIT;

This form might be closer to how inline blocks of code are
written with some other db engines, when they produce resultsets 
without an explicit cursor interface.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite

Re: Performance degradation with non-null proconfig

[Simon Riggs]

On Thu, 19 Nov 2020 at 09:10, Alastair McKinley
 wrote:
>
> Hi all,
>
> I recently came across a subtle performance issue when working with some 
> compiled UDFs to optimise a workload.
>
> These UDFs accidently had "set search_path = 'public'" in their definition.  
> When profiling with perf, I noticed a large amount of elapsed time spent in 
> the function
>
> voidAtEOXact_GUC(bool isCommit, int nestLevel)
>
> Reading the comments it is called when exiting a function with a custom 
> proconfig, removing it for my case gave me a very large (3x) speedup.
>
> Below is a contrived test case that illustrates this issue (tested on 13.0, 
> but also seen in 12.4).
>
> create table test as
> select r as row_id,
> array_agg(random()::float4) as data
> from generate_series(1,10) r,
> generate_series(1,20) e
> group by r;
>
> create or replace function array_sum(float4[]) returns float4 as
> $$
> select sum(e) from unnest($1) e;
> $$ language sql immutable parallel safe;
>
>
> create or replace function array_sum_public_search_path(float4[]) returns 
> float4 as
> $$
> select sum(e) from unnest($1) e;
> $$ language sql immutable parallel safe set search_path = 'public';
>
> \timing on
> \o /dev/null
> select format($q$ explain (analyze,verbose,buffers) select array_sum(data) 
> from test $q$) from generate_series(1,10);
> \gexec
> select format($q$ explain (analyze,verbose,buffers) select 
> array_sum_public_search_path(data) from test $q$) from generate_series(1,10);
> \gexec
>
>
> Test output:
>
> postgres=# select format($q$ explain (analyze,verbose,buffers) select 
> array_sum(data) from test $q$) from generate_series(1,10);
> Time: 0.940 ms
> postgres=# \gexec
> Time: 745.988 ms
> Time: 677.056 ms
> Time: 653.709 ms
> Time: 651.033 ms
> Time: 650.063 ms
> Time: 647.741 ms
> Time: 650.328 ms
> Time: 651.954 ms
> Time: 655.384 ms
> Time: 650.988 ms
> Time: 0.976 ms
> postgres=# select format($q$ explain (analyze,verbose,buffers) select 
> array_sum_public_search_path(data) from test $q$) from generate_series(1,10);
> Time: 0.774 ms
> postgres=# \gexec
> Time: 871.628 ms
> Time: 853.298 ms
> Time: 856.798 ms
> Time: 857.794 ms
> Time: 861.836 ms
> Time: 858.291 ms
> Time: 861.763 ms
> Time: 850.221 ms
> Time: 851.470 ms
> Time: 858.875 ms
> Time: 1.514 ms
> postgres=#
>
>
>
> I didn't see this discussed anywhere else, it might be worth adding a note to 
> the documentation if it can't easily be addressed.

I came up with a simpler test, which doesn't reproduce this

postgres=# create or replace function proconfig1() returns integer
language sql as $$ select 1; $$;
CREATE FUNCTION
postgres=# create or replace function proconfig2() returns integer
language sql as $$ select 1; $$ set search_path = 'public';
CREATE FUNCTION
postgres=# \timing
Timing is on.
postgres=# select sum(proconfig1()) from generate_series(1,1);
  sum
---
 1
(1 row)
Time: 2.818 ms
postgres=# select sum(proconfig2()) from generate_series(1,1);
  sum
---
 1
(1 row)
Time: 41.750 ms

So the overhead would be 4us per call? Other tests show that is
roughly the same situation for PLpgSQL functions.

Can't see anything to document as yet.

-- 
Simon Riggshttp://www.EnterpriseDB.com/

Re: \COPY command and indexes in tables

[Paul Förster]

Hi Jayadevan,

> On 19. Nov, 2020, at 11:07, Jayadevan M  wrote:
> 
> May be you could also make sure that loading actually stopped, by checking 
> the size of the data directory. In another session, you could try 
> watch du -h  -s 

that might be misleading if you have the pg_wal directory inside PGDATA.

Cheers,
Paul

Re: \COPY command and indexes in tables

[Jayadevan M]

>
> We load large tables in some customer installation (some millions of rows)
> from file with:
>
> TRUNCATE TABLE tableName ;
> \COPY tableName FROM 'fileName' WITH ( DELIMITER '|' )
>
>
May be you could also make sure that loading actually stopped, by checking
the size of the data directory. In another session, you could try
watch du -h  -s 

Regards,
Jayadevan

Performance degradation with non-null proconfig

[Alastair McKinley]

Hi all,

I recently came across a subtle performance issue when working with some 
compiled UDFs to optimise a workload.

These UDFs accidently had "set search_path = 'public'" in their definition.  
When profiling with perf, I noticed a large amount of elapsed time spent in the 
function

voidAtEOXact_GUC(bool isCommit, int nestLevel)

Reading the comments it is called when exiting a function with a custom 
proconfig, removing it for my case gave me a very large (3x) speedup.

Below is a contrived test case that illustrates this issue (tested on 13.0, but 
also seen in 12.4).

create table test as
select r as row_id,
array_agg(random()::float4) as data
from generate_series(1,10) r,
generate_series(1,20) e
group by r;

create or replace function array_sum(float4[]) returns float4 as
$$
select sum(e) from unnest($1) e;
$$ language sql immutable parallel safe;


create or replace function array_sum_public_search_path(float4[]) returns 
float4 as
$$
select sum(e) from unnest($1) e;
$$ language sql immutable parallel safe set search_path = 'public';

\timing on
\o /dev/null
select format($q$ explain (analyze,verbose,buffers) select array_sum(data) from 
test $q$) from generate_series(1,10);
\gexec
select format($q$ explain (analyze,verbose,buffers) select 
array_sum_public_search_path(data) from test $q$) from generate_series(1,10);
\gexec

Test output:

postgres=# select format($q$ explain (analyze,verbose,buffers) select 
array_sum(data) from test $q$) from generate_series(1,10);
Time: 0.940 ms
postgres=# \gexec
Time: 745.988 ms
Time: 677.056 ms
Time: 653.709 ms
Time: 651.033 ms
Time: 650.063 ms
Time: 647.741 ms
Time: 650.328 ms
Time: 651.954 ms
Time: 655.384 ms
Time: 650.988 ms
Time: 0.976 ms
postgres=# select format($q$ explain (analyze,verbose,buffers) select 
array_sum_public_search_path(data) from test $q$) from generate_series(1,10);
Time: 0.774 ms
postgres=# \gexec
Time: 871.628 ms
Time: 853.298 ms
Time: 856.798 ms
Time: 857.794 ms
Time: 861.836 ms
Time: 858.291 ms
Time: 861.763 ms
Time: 850.221 ms
Time: 851.470 ms
Time: 858.875 ms
Time: 1.514 ms
postgres=#


I didn't see this discussed anywhere else, it might be worth adding a note to 
the documentation if it can't easily be addressed.

Best regards,

Alastair

Re: Multiple result set to be returned in procedure/function

[Thomas Kellerer]

Muthukumar.GK schrieb am 19.11.2020 um 09:27:
> is it possible to return Multiple results set from procedure/function
> on single execution. Please advise me on this. we are planning to
> migrate things from sqlserver to postgresql where my existing sql
> stored procs will return multiple result set. so we need achieve same
> thing in postgresql.


You can achieve something like that, but it's a bit cumbersome to consume/use 
the results:

create function get_results()
  returns setof refcursor
as
$$
declare
   c1 refcursor;
   c2 refcursor;
begin
   open c1 for select * from (values (1,2,3), (4,5,6)) as t(a,b,c);
   return next c1;
   open c2 for select * from (values ('one'),('two'),('three'),('four')) as 
p(name);
   return next c2;
end;
$$
language plpgsql;

You have to turn off autocommit in order to be able to consume the results.

In psql you would get a result with two "unnamed portals" that you need to fetch
manually

arthur=> \set AUTOCOMMIT off

arthur=> select * from get_results();
get_results

 
 

arthur=> fetch all in fetch all in "";
 a | b | c
---+---+---
 1 | 2 | 3
 4 | 5 | 6
(2 rows)

arthur=> fetch all in fetch all in "";
 name
---
 one
 two
 three
 four
(4 rows)


Other SQL clients might make this a bit easier.

How exactly you deal with that in your application depends on the
programming language you use.

I would recommend to take the opportunity of the migration project
and refactor your code so that you don't need this.

Thomas

Multiple result set to be returned in procedure/function

[Muthukumar.GK]

Hi team,

is it possible to return Multiple results set from procedure/function on
single execution. Please advise me on this. we are planning to migrate
things from sqlserver to postgresql where my existing sql stored procs will
return multiple result set. so we need achieve same thing in postgresql.

regards
muthu
9894438403

Re: maintenance_work_mem

[Andreas Schmitz]

https://www.postgresql.org/docs/13/sql-show.html

https://www.postgresql.org/docs/13/sql-set.html


Regards

Andreas


Am 11/19/2020 um 9:05 AM schrieb Atul Kumar:
> Hi,
>
> I have below queries:
>
> 1. How do i check the maintenance_work_mem for current session, before
> setting some other value for this parameter for the same session.
>
> 2. and How do I set maintenance_work_mem for a session only, and how
> will it be "rollback" once my maintainance work is done, Do I need to
> execute any command for that or just closing the session will rollback
> what I set for the session.
>
>
> Please respond me query wise.
>
>
>
> Regards,
> Atul
>
>

maintenance_work_mem

[Atul Kumar]

Hi,

I have below queries:

1. How do i check the maintenance_work_mem for current session, before
setting some other value for this parameter for the same session.

2. and How do I set maintenance_work_mem for a session only, and how
will it be "rollback" once my maintainance work is done, Do I need to
execute any command for that or just closing the session will rollback
what I set for the session.


Please respond me query wise.



Regards,
Atul