Return Multiple Rows from Store Function

[Brahmam Eswar]

Hi ,

Returning multiple rows from store functions using "RETURNS TABLE" and
RETURN QUERY.  The results set may have more than 50k records. Does it give
any performance issues related to memory? if yes  how to avoid it


CREATE OR REPLACE FUNCTION funcq(COL1 character varying)
)
RETURNS TABLE
( a VARCHAR,
  b VARCHAR,
  c varchar)
 AS $$

BEGIN

  RETURN QUERY SELECT a,b,c from table1 where C= COL1;

END;
$$
LANGUAGE plpgsql;


-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: Return Multiple Rows from Store Function

[Pavel Stehule]

Hi

2018-07-27 11:24 GMT+02:00 Brahmam Eswar :

> Hi ,
>
> Returning multiple rows from store functions using "RETURNS TABLE" and
> RETURN QUERY.  The results set may have more than 50k records. Does it give
> any performance issues related to memory? if yes  how to avoid it
>
>
This command uses tuple store - the result is stored in memory to work_mem
size, and later is materialized (stored to temporary file).

Probably worst effect is invisibility of embedded query for planner. The
default estimation of SRF (set returning function) is 1000 rows. If you run
50K rows, the estimation will be really off, and the plan of query can be
strongly suboptimal.

The best practice is not using similar functions. Functions should not to
supply views. It is antipattern with more than one possible performance
issue.

regards

Pavel


> CREATE OR REPLACE FUNCTION funcq(COL1 character varying)
> )
> RETURNS TABLE
> ( a VARCHAR,
>   b VARCHAR,
>   c varchar)
>  AS $$
>
> BEGIN
>
>   RETURN QUERY SELECT a,b,c from table1 where C= COL1;
>
> END;
> $$
> LANGUAGE plpgsql;
>
>
> --
> Thanks & Regards,
> Brahmeswara Rao J.
>

Postgresql 10.4 installation issues on Ubuntu 14.05

[vardenis pavardenis]

Hello.
I am trying install postgresql 10.4 on Ubuntu 14.05 server (x64).
On Ubuntu i installed only ssh service for remote access.
I am trying install it via Apt Repository  (https://www.postgresql.org/
download/linux/ubuntu/).
I did:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/
$(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

sudo apt-get install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |
sudo apt-key add -
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-10

and get some errors:


Removing obsolete dictionary files:

* No PostgreSQL clusters exist; see "man pg_createcluster"

Processing triggers for ureadahead (0.100.0-16) ...

Setting up postgresql-10 (10.4-2.pgdg14.04+1) ...

Creating new PostgreSQL cluster 10/main ...

/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main
--auth-local peer --auth-host md5

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.



The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".



Data page checksums are disabled.



fixing permissions on existing directory /var/lib/postgresql/10/main ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting dynamic shared memory implementation ... posix

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok



Success. You can now start the database server using:



/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main
-l logfile start



Ver Cluster Port Status OwnerData directory  Log file

10  main5432 down   postgres /var/lib/postgresql/10/main
/var/log/postgresql/postgresql-10-main.log

update-alternatives: using
/usr/share/postgresql/10/man/man1/postmaster.1.gz to provide
/usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode

* Starting PostgreSQL 10 database server
 * Failed to issue method call: Unit postgresql@10-main.service failed
to load: No such file or directory. See system logs and 'systemctl
status postgresql@10-main.service' for details.




 [fail]

invoke-rc.d: initscript postgresql, action "start" failed.

dpkg: error processing package postgresql-10 (--configure):

subprocess installed post-installation script returned error exit status 1

dpkg: dependency problems prevent configuration of postgresql:

postgresql depends on postgresql-10; however:

  Package postgresql-10 is not configured yet.



dpkg: error processing package postgresql (--configure):

dependency problems - leaving unconfigured

Processing triggers for libc-bin (2.19-0ubuntu6.14) ...

No apport report written because the error message indicates its a
followup error from a previous failure.


   Errors were encountered while
processing:

postgresql-10

postgresql

E: Sub-process /usr/bin/dpkg returned an error code (1)


thanks for any reponse.

Re: Postgresql 10.4 installation issues on Ubuntu 14.05

[Adrian Klaver]

On 07/27/2018 08:17 AM, vardenis pavardenis wrote:

Hello.
I am trying install postgresql 10.4 on Ubuntu 14.05 server (x64).
On Ubuntu i installed only ssh service for remote access.
I am trying install it via Apt Repository  
(https://www.postgresql.org/download/linux/ubuntu/ 
).

I did:

sudo sh -c 'echo "debhttp://apt.postgresql.org/pub/repos/apt/ 
  $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'


sudo apt-get install wget ca-certificates
wget --quiet -O -https://www.postgresql.org/media/keys/ACCC4CF8.asc 
  | sudo apt-key add -

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-10

and get some errors:





   Package postgresql-10 is not configured yet.



dpkg: error processing package postgresql (--configure):


dependency problems - leaving unconfigured


Processing triggers for libc-bin (2.19-0ubuntu6.14) ...


No apport report written because the error message indicates its a 
followup error from a previous failure.



   Errors were encountered while processing:


postgresql-10


postgresql


E: Sub-process /usr/bin/dpkg returned an error code (1)


thanks for any reponse.


Best guess, cross contamination with the Ubuntu package(s) for Postgres.

What does:

dpkg -l | grep postgres

show?













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

Re: logical replication snapshots

[Dimitri Maziuk]

On 07/26/2018 07:11 PM, Adrian Klaver wrote:
> On 07/26/2018 04:48 PM, Dimitri Maziuk wrote:
...
>> The publication foopub is at this point fubar I take it? And needs to be
>> re-created on the publisher and reconnected on the subscriber? Complete
>> with with inital resync?
> 
> Not sure. Personally I would try:
> 
> 1) ALTER PUBLICATION DROP TABLE foo|bar;
> 
> 2) ALTER PUBLICATION ADD TABLE foo|bar;
> 
> 3) ALTER SUBSCRIPTION sub_name REFRESH PUBLICATION
> 
> If you get to 3) it will re-sync the data unless you tell it otherwise.
> 
> The above is probably dependent on the size of the publication. If you
> did a publication for ALL it would make more sense to do the above then
> if you did a publication for just foo or bar.

... but if I did the publication for ALL, I could just use streaming
replication and then drop table/add table would replicate automagically ...

It looks like we probably have to re-think a few of our workflows and
procedures, and until/unless that happens, logical replication won't do
what we want. Which means figuring out this 13-million-files problem
becomes a very low priority for me. Unfortunately: it'd be nice to track
it down and squash it...

Thanks everyone,
-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

Re: logical replication snapshots

[Adrian Klaver]

On 07/27/2018 03:04 PM, Dimitri Maziuk wrote:

On 07/26/2018 07:11 PM, Adrian Klaver wrote:

On 07/26/2018 04:48 PM, Dimitri Maziuk wrote:

...

The publication foopub is at this point fubar I take it? And needs to be
re-created on the publisher and reconnected on the subscriber? Complete
with with inital resync?


Not sure. Personally I would try:

1) ALTER PUBLICATION DROP TABLE foo|bar;

2) ALTER PUBLICATION ADD TABLE foo|bar;

3) ALTER SUBSCRIPTION sub_name REFRESH PUBLICATION

If you get to 3) it will re-sync the data unless you tell it otherwise.

The above is probably dependent on the size of the publication. If you
did a publication for ALL it would make more sense to do the above then
if you did a publication for just foo or bar.


... but if I did the publication for ALL, I could just use streaming
replication and then drop table/add table would replicate automagically ...


Well I was just showing the extremes from a single table publication to 
ALL tables. You can also do subsets of ALL. Remember that binary 
replication(streaming) involves the whole Postgres cluster, e.g. all the 
databases in the cluster no choice in the matter. Also it does not allow 
you to shape what is replicated. In other words what form of DML you 
want replicated e.g. UPDATE, INSERT, DELETE. Last but not least logical 
replication works across major versions and different OS'es, which 
binary replication does not.




It looks like we probably have to re-think a few of our workflows and
procedures, and until/unless that happens, logical replication won't do
what we want. Which means figuring out this 13-million-files problem
becomes a very low priority for me. Unfortunately: it'd be nice to track
it down and squash it...





Thanks everyone,




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

Re: Postgresql 10.4 installation issues on Ubuntu 14.05

[Adrian Klaver]

On 07/27/2018 04:50 PM, vardenis pavardenis wrote:
Please reply to list also.
Ccing list.

FYI, bottom/interleaved posting are the preferred styles on this list:

https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
https://en.wikipedia.org/wiki/Posting_style#Bottom-posting



Hello.
So sorry, looks like i make a "misscut" and paste less info i wanted:



No, you pasted all the below in your previous post. I just cut out a 
bunch in my reply to make it more readable.


In case you did not see all my previous post, I asked :

Best guess, cross contamination with the Ubuntu package(s) for Postgres.

What does:

dpkg -l | grep postgres

show?



Creating config file /etc/postgresql-common/createcluster.conf with new 
version


Building PostgreSQL dictionaries from installed myspell/hunspell packages...

Removing obsolete dictionary files:

* No PostgreSQL clusters exist; see "man pg_createcluster"

Processing triggers for ureadahead (0.100.0-16) ...

Setting up postgresql-10 (10.4-2.pgdg14.04+1) ...

Creating new PostgreSQL cluster 10/main ...

/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main 
--auth-local peer --auth-host md5


The files belonging to this database system will be owned by user 
"postgres".


This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting dynamic shared memory implementation ... posix

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

Success. You can now start the database server using:

     /usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l 
logfile start


Ver Cluster Port Status Owner    Data directory  Log file

10  main    5432 down   postgres /var/lib/postgresql/10/main 
/var/log/postgresql/postgresql-10-main.log


update-alternatives: using 
/usr/share/postgresql/10/man/man1/postmaster.1.gz to provide 
/usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode


* Starting PostgreSQL 10 database server * Failed to issue method call: 
Unit postgresql@10-main.service failed to load: No such file or 
directory. See system logs and 'systemctl status 
postgresql@10-main.service' for details.


   [fail]

invoke-rc.d: initscript postgresql, action "start" failed.

dpkg: error processing package postgresql-10 (--configure):

subprocess installed post-installation script returned error exit status 1

dpkg: dependency problems prevent configuration of postgresql:

postgresql depends on postgresql-10; however:

   Package postgresql-10 is not configured yet.

dpkg: error processing package postgresql (--configure):

dependency problems - leaving unconfigured

Processing triggers for libc-bin (2.19-0ubuntu6.14) ...

No apport report written because the error message indicates its a 
followup error from a previous failure.


   Errors were encountered while processing:

postgresql-10

postgresql

E: Sub-process /usr/bin/dpkg returned an error code (1)


thanks.





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