Re: Pgbouncer and postgres

[Fabio Pardi]

Hi Nicola,

I tried to reproduce the problem using pgbouncer 1.8.1 from RPM on CentOS 7 and 
(an almost equal to) your configuration file but I m not able tobring it to the 
same error-state.

Your problem might be tied either to the way you compiled it, or to some 
package installed/reconfigured with the updates (my CentOS is not up to date to 
the latest packages)

What if you try to use the RPM versionof pgbouncer? Can you try that?

How many interfaces to you have on the system?  Did you check if maybe some 
port/interface is in use?  You can stop pgbouncer and then run 'lsof -n | grep 
6543'.No line should return.

Are  you running pgbouncer from systemctl or command line?Maybe is a long shot, 
but one more thing you can try is to remove the line 'user=postgres' and let it 
run as 'pgbouncer'user from root/systemctl.

Is selinux enabled or disabled on the system?


Regards,

fabio pardi



On 17/09/18 14:33, Nicola Contu wrote:
>
> Hello,
>
> I'm trying to get pgbouncer working but I'm getting this error :
>
>
> 2018-09-17 12:20:15.304 87772 NOISE safe_accept(12) = Resource temporarily 
> unavailable
> 2018-09-17 12:20:36.183 87772 NOISE safe_accept(11) = 13 (127.0.0.1:59100 
> )
> 2018-09-17 12:20:36.183 87772 NOISE new fd from accept=13
> 2018-09-17 12:20:36.183 87772 NOISE safe_accept(11) = Resource temporarily 
> unavailable
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'logfile' = 
> '/var/log/pgbouncer.log' ok:1
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'pidfile' = 
> '/home/postgres/pgbouncer.pid'
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'pidfile' = 
> '/home/postgres/pgbouncer.pid' ok:1
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'admin_users' = 'admin'
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'admin_users' = 'admin' 
> ok:1
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'user' = 'postgres'
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'user' = 'postgres' ok:1
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'max_db_connections' = 
> '220'
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'max_db_connections' = 
> '220' ok:1
> 2018-09-17 12:21:14.412 88424 DEBUG parse_ini_file: 'log_connections' = '0'
> 2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_connections' = '0' 
> ok:1
> 2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_disconnections' = '0'
> 2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'log_disconnections' = 
> '0' ok:1
> 2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'verbose' = '3'
> 2018-09-17 12:21:14.413 88424 DEBUG parse_ini_file: 'verbose' = '3' ok:1
> 2018-09-17 12:21:14.413 88424 DEBUG loading auth_file: 
> "/etc/pgbouncer/users.txt"
> 2018-09-17 12:21:14.417 88424 NOISE event: 128, SBuf: 192, PgSocket: 400, 
> IOBuf: 4108
> 2018-09-17 12:21:14.417 88424 LOG File descriptor limit: 1024 (H:4096), 
> max_client_conn: 100, max fds possible: 230
> 2018-09-17 12:21:14.417 88424 DEBUG pktbuf_dynamic(128): 0x25277c0
> 2018-09-17 12:21:14.417 88424 DEBUG make_room(0x25277c0, 4): realloc 
> newlen=256
> 2018-09-17 12:21:14.417 88424 DEBUG pktbuf_dynamic(128): 0x252ebb0
> 2018-09-17 12:21:14.418 88424 NOISE connect(4, unix:/tmp/.s.PGSQL.6543) = 
> Connection refused
> 2018-09-17 12:21:14.418 88424 NOISE safe_close(4) = 0
> 2018-09-17 12:21:14.418 88424 LOG Stale pidfile, removing
> 2018-09-17 12:21:14.419 88424 DEBUG adns_create_context: evdns2
> 2018-09-17 12:21:14.421 88424 DEBUG add_listen: 127.0.0.1:6543 
> 
> 2018-09-17 12:21:14.422 88424 NOISE old TCP_DEFER_ACCEPT on 11 = 0
> 2018-09-17 12:21:14.422 88424 NOISE install TCP_DEFER_ACCEPT on 11
> 2018-09-17 12:21:14.422 88424 LOG listening on 127.0.0.1:6543 
> 
> 2018-09-17 12:21:14.422 88424 DEBUG add_listen: unix:/tmp/.s.PGSQL.6543
> 2018-09-17 12:21:14.422 88424 LOG listening on unix:/tmp/.s.PGSQL.6543
> 2018-09-17 12:21:14.423 88424 LOG process up: pgbouncer 1.8.1, libevent 
> 2.0.21-stable (epoll), adns: evdns2, tls: OpenSSL 1.0.2k-fips  26 Jan 2017
> 2018-09-17 12:21:14.756 88424 DEBUG loading auth_file: 
> "/etc/pgbouncer/users.txt"
> 2018-09-17 12:21:48.917 88424 NOISE safe_accept(12) = 13 (unix:)
> 2018-09-17 12:21:48.917 88424 NOISE new fd from accept=13
> 2018-09-17 12:21:48.917 88424 WARNING tune_socket(13) failed: Operation not 
> supported
> 2018-09-17 12:21:48.917 88424 NOISE safe_close(13) = 0
> 2018-09-17 12:21:48.918 88424 NOISE safe_accept(12) = Resource temporarily 
> unavailable
> 2018-09-17 12:22:14.422 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0 B/s, 
> out 0 B/s, xact 0 us, query 0 us wait time 0 us
> 2018-09-17 12:23:14.423 88424 LOG Stats: 0 xacts/s, 0 queries/s, in 0 B/s, 
> out 0 B/s, xact 0 us, query 0 us wait time 0 us
> 2018-09-17 12:23:26.561 88424 LOG Got SIGINT, shutting down
> 2018-09-17 12:23:26.872 88424 LOG server connections dropped, exiting
> 2018-09-17 12:23:26.872 88424 NOISE safe_close(11) = 0
> 2018-09-17 12:23:

Issues with compiling libpq 9.1.2 with Visual C++

[Nordström Thomas]

OS: Windows 10
Toolset: vc141 (Visual Studio 2017)

I'm having trouble compiling version 9.1.2 of libpq with Visual C++. The reason 
that I want this particular version is because it's the one included with GDAL 
version 2.1.3 from GISinternals.com. To be specific, I pulled commit 
cfd8cf37d20be715e4bb9e382844c79556516824 from GitHub.

What I'm doing is running a powershell script with the following command (of 
course, I've already run the batch-file included with Visual Studio which sets 
all the relevant PATH environment variables needed to compile with Visual C++):

nmake /f "win32.mak" DEBUG=1 USE_SSL=1 SSL_INC="$opensslDir\include" 
SSL_LIB_PATH="$opensslDir\x86\Debug\lib\"

This first complains that there's no libpq-dist.rc. Through some googling I 
found this page: 
https://pgolub.wordpress.com/2009/04/13/building-postgresql-client-library-using-borland-c-compiler-bcc-under-winxp/
 It says to just rename libpq.rc.in. This at least stops the error message, but 
it then complains about not finding libpqdll.def. That page said that the 
author solved it by simply finding an old version of that file, which to me 
sounds like a bad idea and also I've been unable to do.

If I run the build again it doesn't complain about the lack of libpqdll.def and 
claims that all Win32 files have been built. The static lib *seems* to have 
been built correctly, but the libpq.dll is 0 kB and there's no libpqdll.lib, so 
it clearly fails.

I'm *guessing* that both libpq-dist.rc and libpqdll.def would have been 
generated by running .\configure. However, when I try doing that it only looks 
for gcc and cc, but not cl.exe, so it gives the error that there's "no 
acceptable C compiler found in $PATH". I've found through Googling around that 
a lot of other people have .\configure scripts that look for cl.exe as well.
I'm running .\configure like this, btw:

sh .\configure

"sh" is Git Bash.

So,
How do I get libpq-dist.rc and libpqdll.def? (Through .\configure?)
If I have to use .\configure, how do I get it to look for cl.exe?

Thank you.

Max open files

[Job]

Dear all,

i am using Bind-DLZ with postgresql and i am experiencing some response problem 
with a number of concurrent queries above 1000 (about).
We use named pipe to connect from Bind DLZ.
I noticed that Postgresql 9.6.1 is launched with a max open files.

Could be this the problem?
How can i raise the limit?

The kernel max files is 99
Thank you!
F


[root@cloud-frankfurt-dns01 pgsql]# cat /proc/5336/limits
Limit Soft Limit   Hard Limit   Units
Max cpu time  unlimitedunlimitedseconds
Max file size unlimitedunlimitedbytes
Max data size unlimitedunlimitedbytes
Max stack size8388608  unlimitedbytes
Max core file size0unlimitedbytes
Max resident set  unlimitedunlimitedbytes
Max processes 3120631206processes
Max open files1024 4096 files
Max locked memory 6553665536bytes
Max address space unlimitedunlimitedbytes
Max file locksunlimitedunlimitedlocks
Max pending signals   3120631206signals
Max msgqueue size 819200   819200   bytes
Max nice priority 00
Max realtime priority 00
Max realtime timeout  unlimitedunlimitedus

Code of Conduct

[Dave Page]

The PostgreSQL Core team are pleased to announce that following a long
consultation process, the project’s Code of Conduct (CoC) has now been
finalised and published at https://www.postgresql.org/about/policies/coc/.

Please take time to read and understand the CoC, which is intended to
ensure that PostgreSQL remains an open and enjoyable project for anyone to
join and participate in.

A Code of Conduct Committee has been formed to handle any complaints. This
consists of the following volunteers:

- Stacey Haysler (Chair)
- Lætitia Avrot
- Vik Fearing
- Jonathan Katz
- Ilya Kosmodemiansky

We would like to extend our thanks and gratitude to Stacey Haysler for her
patience and expertise in helping develop the Code of Conduct, forming the
committee and guiding the work to completion.

-- 
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/

Re: Code of Conduct

[James Keener]

> following a long consultation process

It's not a consultation if any dissenting voice is simply ignored. Don't 
sugar-coat or politicize it like this -- it was rammed down everyone's throats. 
That is core's right, but don't act as everyone's opinions and concerns were 
taken into consideration. There are a good number of folks who are concerned 
that this CoC is overreaching and is ripe for abuse. Those concerns were always 
simply, plainly, and purposely ignored.

> Please take time to read and understand the CoC, which is intended to ensure 
> that PostgreSQL remains an open and enjoyable project for anyone to join and 
> participate in.

I sincerely hope so, and that it doesn't become a tool to enforce social 
ideology like in other groups I've been part of.  Especially since this is the 
main place to come to get help for PostgreSQL and not a social club.

Jim

On September 18, 2018 6:27:56 AM EDT, Dave Page  wrote:
>The PostgreSQL Core team are pleased to announce that following a long
>consultation process, the project’s Code of Conduct (CoC) has now been
>finalised and published at
>https://www.postgresql.org/about/policies/coc/.
>
>Please take time to read and understand the CoC, which is intended to
>ensure that PostgreSQL remains an open and enjoyable project for anyone
>to
>join and participate in.
>
>A Code of Conduct Committee has been formed to handle any complaints.
>This
>consists of the following volunteers:
>
>- Stacey Haysler (Chair)
>- Lætitia Avrot
>- Vik Fearing
>- Jonathan Katz
>- Ilya Kosmodemiansky
>
>We would like to extend our thanks and gratitude to Stacey Haysler for
>her
>patience and expertise in helping develop the Code of Conduct, forming
>the
>committee and guiding the work to completion.
>
>-- 
>Dave Page
>PostgreSQL Core Team
>http://www.postgresql.org/
>

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.

Advice on machine specs for growth

[Rory Campbell-Lange]

[I sent this to the performance list a couple of days ago and received
no replies. Apologies for the cross-post]

We are looking to upgrade our current database server infrastructure so
that it is suitable for the next 3 years or so.

Presently we have two physical servers with the same specs:

- 220GB database partition on RAID10 SSD on HW RAID
- 128GB RAM
- 8 * Xeon E5-2609

(The HW RAID card is a MegaRAID SAS 9361-8i with BBU)

The second server is a hot standby to the first, and we presently have
about 350 databases in the cluster. 

We envisage needing about 800GB of primary database storage in the next
three years, with 1000 databases in the cluster.

We are imagining either splitting the cluster into two and (to have four
main servers) or increasing the disk capacity and RAM in each server.
The second seems preferable from a day-to-day management basis, but it
wouldn't be too difficult to deploy our software upgrades across two
machines rather than one.

Resources on the main machines seem to be perfectly adequate at present
but it is difficult to know at what stage queries might start spilling
to disk. We presently occasionally hit 45% CPU utilisation, load average
peaking at 4.0 and we occasionally go into swap in a minor way (although
we can't determine the reason for going into swap). There is close to no
iowait in normal operation.

It also seems a bit incongruous writing about physical machines these
days, but I can't find pricing on a UK data protection compatible cloud
provider that beats physical price amortised over three years (including
rack costs). The ability to more easily "make" machines to help with
upgrades is attractive, though.

Some comments and advice on how to approach this would be very
gratefully received.

Thanks
Rory

Re: Issues with compiling libpq 9.1.2 with Visual C++

[Tom Lane]

=?iso-8859-1?Q?Nordstr=F6m_Thomas?=  writes:
> Toolset: vc141 (Visual Studio 2017)
> I'm having trouble compiling version 9.1.2 of libpq with Visual C++.

That's going to be ... exciting.  I don't think we promise to support
VS2017 even yet; the latest VS patches I can remember seeing were for
VS2015.  And 9.1.2 came out in 2011, so there wasn't support for
anything newer than VS2008 at the time.

> The reason that I want this particular version is because it's the one 
> included with GDAL version 2.1.3 from GISinternals.com. To be specific, I 
> pulled commit cfd8cf37d20be715e4bb9e382844c79556516824 from GitHub.

I would not take that as a good reason to use an ancient version of libpq.
AFAIK we haven't broken backwards compatibility in libpq, so current
versions ought to work just fine with the GDAL version you want to use.

> What I'm doing is running a powershell script with the following command (of 
> course, I've already run the batch-file included with Visual Studio which 
> sets all the relevant PATH environment variables needed to compile with 
> Visual C++):
> nmake /f "win32.mak" DEBUG=1 USE_SSL=1 SSL_INC="$opensslDir\include" 
> SSL_LIB_PATH="$opensslDir\x86\Debug\lib\"

The recommended way to build PG under VS is to use the project files we
supply.  That will wind up building the whole server not just libpq,
but it'll still take less time than you've already wasted fighting
this tooling version skew.

What I'd try doing is building a PG release that claims to support
VS2015 (9.6 or later), using the recommended process:
https://www.postgresql.org/docs/current/static/install-windows.html
Or you could just grab a prebuilt version from our download pages.
Then point GDAL at the libpq library from that.

regards, tom lane

Re: Advice on machine specs for growth

[Laurenz Albe]

Rory Campbell-Lange wrote:
> We are looking to upgrade our current database server infrastructure so
> that it is suitable for the next 3 years or so.
> 
> We envisage needing about 800GB of primary database storage in the next
> three years, with 1000 databases in the cluster.

1000 is a lot, but should still be ok.

> We are imagining either splitting the cluster into two and (to have four
> main servers) or increasing the disk capacity and RAM in each server.
> The second seems preferable from a day-to-day management basis, but it
> wouldn't be too difficult to deploy our software upgrades across two
> machines rather than one.

If you can scale horizontally by splitting the load across several
independent database servers, then do so by all means.

This may be more administration work initially, but scaling will
come easy when you need it.

It is much more difficult to scale a single monolithic database server.

> Resources on the main machines seem to be perfectly adequate at present
> but it is difficult to know at what stage queries might start spilling
> to disk. We presently occasionally hit 45% CPU utilisation, load average
> peaking at 4.0 and we occasionally go into swap in a minor way (although
> we can't determine the reason for going into swap). There is close to no
> iowait in normal operation.

Disable memory overcommit and set swappiness to 0 on database servers.

> It also seems a bit incongruous writing about physical machines these
> days, but I can't find pricing on a UK data protection compatible cloud
> provider that beats physical price amortised over three years (including
> rack costs). The ability to more easily "make" machines to help with
> upgrades is attractive, though.

I think physical machines are cool.
The resulting system becomes simpler with fewer dependencies, and
it is much easier to debug performance problems.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Code of Conduct

[Tomas Vondra]

On 09/18/2018 01:47 PM, James Keener wrote:

 > following a long consultation process

It's not a consultation if any dissenting voice is simply ignored.
Don't sugar-coat or politicize it like this -- it was rammed down
everyone's throats. That is core's right, but don't act as everyone's
opinions and concerns were taken into consideration.


I respectfully disagree.

I'm not sure which dissenting voices you think were ignored, but from 
what I've observed in the various CoC threads the core team took the 
time to respond to all comments. That does not necessarily mean the 
resulting CoC makes everyone happy, but unfortunately that's not quite 
possible. And it does not mean it was not an honest consultation.


IMO the core team did a good job in listening to comments, tweaking the 
wording and/or explaining the reasoning. Kudos to them.



There are a good number of folks who are concerned that this CoC is
overreaching and is ripe for abuse. Those concerns were always
simply, plainly, and purposely ignored.
No, they were not. There were multiple long discussions about exactly 
these dangers, You may dislike the outcome, but it was not ignored.


 > Please take time to read and understand the CoC, which is intended to 
ensure that PostgreSQL remains an open and enjoyable project for anyone 
to join and participate in.


I sincerely hope so, and that it doesn't become a tool to enforce social 
ideology like in other groups I've been part of. Especially since this 
is the main place to come to get help for PostgreSQL and not a social club.




Ultimately, it's a matter of trust that the CoC committee and core team 
apply the CoC in a careful and cautious way. Based on my personal 
experience with most of the people involved in both groups I'm not 
worried about this part.



regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Select into table%ROWTYPE failed

[marcelo]

I´m testing a trigger function in a 9.4 installation.
It´s for bill number assignation, but with a twist: there are various 
numbering ranges. This ranges are defined by a text code, a minimum and 
maximum. Every bill have some code taken from the set defined in a 
specific table (billnumberrange)
The first approach was the obvious "select into" a row instance, using 
table%ROWTYPE as the destination.

That failed, leaving all fields of the  instance as null.
But selecting into the interesting fields works ok. The trigger function 
follows; the initial approach lines are commented.


CREATE FUNCTION nextbillnumber() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  lastnumber integer;
  lowerlimit integer;
  upperlimit integer;
  -- ranger billnumberrange%ROWTYPE;
BEGIN
  if NEW.billnumber = 0 THEN
      select billnumberrange.lowerlimit, billnumberrange.upperlimit 
from billnumberrange
        where billnumberrange.groupcode = NEW.groupcode into 
lowerlimit, upperlimit;

        --where billnumberrange.groupcode = NEW.groupcode into ranger;
        -- RAISE NOTICE 'first select result % % <> %', 
ranger.groupcode, ranger.lowerlimit, ranger.upperlimit; this NOTICE 
showed   <> 
       RAISE NOTICE 'first select result % <> %', lowerlimit, 
upperlimit;-- this shows the expected values

       select max(billnumber) from bill
        where billnumber BETWEEN lowerlimit and upperlimit
        --  where billnumber BETWEEN ranger.lowerlimit and 
ranger.upperlimit

        into lastnumber;
    RAISE NOTICE 'second select result %', FOUND;
    if lastnumber is null THEN
        lastnumber := lowerlimit;
        -- lastnumber := ranger.lowerlimit;
    end if;
    RAISE NOTICE 'lastnumber is %', lastnumber;
    NEW.billnumber = lastnumber + 1;
  end if;
  return NEW;
END;
$$;

What was wrong in the first approach?
TIA


---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: Code of Conduct

[James Keener]

>
>  You may dislike the outcome, but it was not ignored.


I can accept that I don't like the outcome, but I can point to maybe a
dozen people in the last
exchange worried about the CoC being used to further political goals, and
the only response
was "well, the CoC Committee will handle it reasonable" which is not a good
answer, because
that's exactly the situation that we are worried about not happening! These
concerns were never
actually addressed and always just brushed aside -- that's what I found
bothersome and worrisome.

We shouldn't have to expect the rules to be applied fairly in order to
counter actual abuses of the
rules. I've seen it in other groups and have been the target of such
actions. (I had the gall to claim
that hiring practices that require submitting side- or open-source- work
aren't only detrimental to
women because they statistically shoulder more of the housework and
childcare, but also to
husbands and fathers who take an active role in the household and
childcare. It wasn't intended to
diminish the effect this hiring practice has on women, but to suggest that
it's a broader problem than
the conversation at that point was making it out to be. I was subsequently
silenced and eventually
booted from the group for that incident and another, in a social channel,
where a discussion on guns
was taking place and someone said that the discussion is sexist and this is
why there are so few
female programmers, and I had the impertinence to say that I know more
women who hunt and shot
for sport then men (it's ~50-50 in this area). Forgive me for not having a
favourable view of CoCs.)

So, it's not that I don't trust the CoC Committee, but I just really don't
trust most people. The clearer
the rules the better. As it stands, the rules are extremely vague and
overreaching.

Jim

Re: Code of Conduct

[Chris Travers]

On Tue, Sep 18, 2018 at 4:35 PM Tomas Vondra 
wrote:

> On 09/18/2018 01:47 PM, James Keener wrote:
> >  > following a long consultation process
> >
> > It's not a consultation if any dissenting voice is simply ignored.
> > Don't sugar-coat or politicize it like this -- it was rammed down
> > everyone's throats. That is core's right, but don't act as everyone's
> > opinions and concerns were taken into consideration.
>
> I respectfully disagree.
>
> I'm not sure which dissenting voices you think were ignored, but from
> what I've observed in the various CoC threads the core team took the
> time to respond to all comments. That does not necessarily mean the
> resulting CoC makes everyone happy, but unfortunately that's not quite
> possible. And it does not mean it was not an honest consultation.
>
> IMO the core team did a good job in listening to comments, tweaking the
> wording and/or explaining the reasoning. Kudos to them.
>

I said I would stand aside my objections after the last point I mentioned
them but I did not feel that my particular objection and concern with
regard to one specific sentence added got much of a hearing.  This being
said, it is genuinely hard to sort through the noise and try to reach the
signal.  I think the resurgence of the debate about whether we need a code
of conduct made it very difficult to discuss specific objections to
specific wording.  So to be honest the breakdown was mutual.

>
> > There are a good number of folks who are concerned that this CoC is
> > overreaching and is ripe for abuse. Those concerns were always
> > simply, plainly, and purposely ignored.
> No, they were not. There were multiple long discussions about exactly
> these dangers, You may dislike the outcome, but it was not ignored.
>

Also those of us who had specific, actionable concerns were often drowned
out by the noise.  That's deeply unfortunate.

I think those of us who had specific concerns about one specific sentence
that was added were drowned out by those who seemed to be opposed to the
idea of a code of conduct generally.

I would have appreciated at least a reason why the concerns I had about the
fact that the addition a) doesn't cover what it is needs to cover, and b)
will attract complaints that it shouldn't cover was not considered valid.
But I can understand that given the noise-to-signal ratio of the discussion
made such discussion next to impossible.

Again I find that regrettable.

>
> >  > Please take time to read and understand the CoC, which is intended to
> > ensure that PostgreSQL remains an open and enjoyable project for anyone
> > to join and participate in.
> >
> > I sincerely hope so, and that it doesn't become a tool to enforce social
> > ideology like in other groups I've been part of. Especially since this
> > is the main place to come to get help for PostgreSQL and not a social
> club.
> >
>
> Ultimately, it's a matter of trust that the CoC committee and core team
> apply the CoC in a careful and cautious way. Based on my personal
> experience with most of the people involved in both groups I'm not
> worried about this part.
>

I would actually go further than you here.  The CoC committee *cannot*
apply the CoC in the way that the opponents fear.  The fact is, Europe has
anti-discrimination laws regarding social and political ideology (something
the US might want to consider as it would help avoid problems on this list
;-) ).  And different continents have different norms on these sorts of
things.  Pushing a social ideology via the code of conduct would, I
suspect, result in everything from legal action to large emerging markets
going elsewhere.  So I don't think ti is a question of "trust us" but
rather that the community won't let that sort of abuse happen no matter who
is on the CoC committee.

>
>
> regards
>
> --
> Tomas Vondra  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>

-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: Select into table%ROWTYPE failed

[Tom Lane]

marcelo  writes:
> What was wrong in the first approach?

plpgsql's "SELECT INTO" expects a one-for-one match between the output
columns of the SELECT and the columns of the INTO destination.  So I'd
expect something like this to work:

DECLARE
  ranger billnumberrange%ROWTYPE;
BEGIN
 SELECT * FROM billnumberrange WHERE ... INTO ranger;

Your example clearly wasn't selecting all the columns, and it
wasn't clear whether you paid any attention to column ordering;
but both of those matter.

regards, tom lane

Re: Code of Conduct

[Stephen Frost]

Greetings,

* Chris Travers (chris.trav...@gmail.com) wrote:
> I said I would stand aside my objections after the last point I mentioned
> them but I did not feel that my particular objection and concern with
> regard to one specific sentence added got much of a hearing.  This being
> said, it is genuinely hard to sort through the noise and try to reach the
> signal.  I think the resurgence of the debate about whether we need a code
> of conduct made it very difficult to discuss specific objections to
> specific wording.  So to be honest the breakdown was mutual.

I would ask that you, and anyone else who has a suggestion for how to
improve or revise the CoC, submit your ideas to the committee by
email'ing c...@postgresql.org.

As was discussed previously, the current CoC isn't written in stone and
it will be changed and amended as needed.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Code of Conduct

[Tom Lane]

Stephen Frost  writes:
> I would ask that you, and anyone else who has a suggestion for how to
> improve or revise the CoC, submit your ideas to the committee by
> email'ing c...@postgresql.org.
> As was discussed previously, the current CoC isn't written in stone and
> it will be changed and amended as needed.

The change process is spelled out explicitly in the CoC document.

I believe though that the current plan is to wait awhile (circa 1 year)
and get some experience with the current version before considering
changes.

regards, tom lane

[GENERAL] optimising the pl/pgsql function

[VENKTESH GUTTEDAR]

Hello,

We are running a pretty big plsql function, we are fetching data from
some tables based on certain conditions. And using that data in a for loop
within the function.
the problem we are facing is when the no of records are more the function
ction is taking too much time, is there a solution to this problem? Can we
replace the for loop with something else?

Re: Select into table%ROWTYPE failed

[marcelo]

Ohh, you are right!
Thank you!

On 18/09/2018 14:10 , Tom Lane wrote:

marcelo  writes:

What was wrong in the first approach?

plpgsql's "SELECT INTO" expects a one-for-one match between the output
columns of the SELECT and the columns of the INTO destination.  So I'd
expect something like this to work:

DECLARE
   ranger billnumberrange%ROWTYPE;
BEGIN
  SELECT * FROM billnumberrange WHERE ... INTO ranger;

Your example clearly wasn't selecting all the columns, and it
wasn't clear whether you paid any attention to column ordering;
but both of those matter.

regards, tom lane




---
El software de antivirus Avast ha analizado este correo electrónico en busca de 
virus.
https://www.avast.com/antivirus

Re: Code of Conduct

[Chris Travers]

On Tue, Sep 18, 2018 at 8:35 PM Tom Lane  wrote:

> Stephen Frost  writes:
> > I would ask that you, and anyone else who has a suggestion for how to
> > improve or revise the CoC, submit your ideas to the committee by
> > email'ing c...@postgresql.org.
> > As was discussed previously, the current CoC isn't written in stone and
> > it will be changed and amended as needed.
>
> The change process is spelled out explicitly in the CoC document.
>
> I believe though that the current plan is to wait awhile (circa 1 year)
> and get some experience with the current version before considering
> changes.
>

My $0.02:

If you are going to have a comment period, have a comment period and
actually deliberate over changes.

If you are going to just gather feedback and wait a year, use some sort of
issue system.

Otherwise, there is no reason to think that feedback gathered now will have
any impact at all in the next revision.

>
> regards, tom lane
>


-- 
Best Wishes,
Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
lock-in.
http://www.efficito.com/learn_more

Re: [GENERAL] optimising the pl/pgsql function

[David G. Johnston]

On Tue, Sep 18, 2018 at 11:37 AM, VENKTESH GUTTEDAR <
venkteshgutte...@gmail.com> wrote:

> Can we replace the for loop with something else?
>

A query probably - depending on what the function does and, in particular,
whether dynamic SQL is required.

David J.

how to know whether query data from memory after pg_prewarm

[jimmy]

I use select pg_prewarm('table1','read','main')  to load data of table1 into 
the memory.
when I use select count(1) from table1 group by aa to query data.
I find the speed of query is not fast, I wonder whether it query data from 
memory.
And it is slower than Oracle, both of Oracle and Postgresql has same table and 
count of data.
when pg_prewarm use 'read' mode,  the data is put into the OS cache, how to 
examine the table which is pg_prewarmed into the OS cache .
I know pg_buffercache ,but it just examine the table in the shared buffer of 
Postgresql, not the table in the OS cache.

Why the sql is not executed in parallel mode

[jimmy]

Why the sql is not executed in parallel mode, does the sql has some problem?
with sql1 as
(select a.*
   from snaps a
  where a.f_date between to_date('2018-03-05', '-MM-dd') and
to_date('2018-03-11', '-MM-dd')
 ),
sql2 as
(select '1' as pId, PM_TO as pValue, type_code as typeCode, version_no as 
versionNo,
bs as bs, l.order_rule as orderRule
   from sql1, qfpl l
  where PM_TO is not null
 and l.pid = 1
 union all
 select '2' as pId,
PRTO as pValue,
type_code as typeCode, version_no as versionNo,
bs as bs, l.order_rule as orderRule
   from sql1, qfpl l
  where PRTO is not null
 and l.pid = 2
 union all
 select '3' as pId,
PRATO as pValue,
type_code as typeCode, version_no as versionNo,
bs as bs, l.order_rule as orderRule
   from sql1, qfpl l
  where PRATO is not null
 and l.pid = 3
 ),
sql4 as (
select typeCode, pId, orderRule, versionNo,
row_number() over(partition by pId, typeCode order by pValue) as rnn
 from sql2
),
sql5 as (
select sql4.typeCode as typeCode,
 sql4.pId as pId,
 sql4.orderRule as orderRule,
 t.pValue as pValue,
 sql4.versionNo as versionNo
from sql4,
(select sql2.typeCode,sql2.pId,sql2.orderRule,
 (case when sql2.orderRule = 1 then
PERCENTILE_DISC(0.05) WITHIN GROUP(ORDER BY sql2.pValue)
  else
PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY sql2.pValue)
end) as pValue,
 (case when sql2.orderRule = 1 then
 (case when round(count(1) * 0.05) - 1 < 0 then 1
 else round(count(1) * 0.05)
 end)
  else
 (case when round(count(1) * 0.95) - 1 < 0 then 1
 else round(count(1) * 0.95)
 end)
  end) as rnn
 from sql2
 group by sql2.typeCode, sql2.pId, sql2.orderRule)  t
where sql4.typeCode = t.typeCode
and sql4.pId = t.pId
 and sql4.orderRule = t.orderRule
 and sql4.rnn = t.rnn
),
sql6 as (
select sql2.pId, sql2.typeCode as typeCode, count(1) as fCount
from sql2, sql5
   where sql2.pId = sql5.pId
 and sql2.typeCode = sql5.typeCode
 and ((sql2.orderRule = 2 and sql2.pValue >= sql5.pValue) or
 (sql2.orderRule = 1 and sql2.pValue <= sql5.pValue))
 and sql2.pId != '22'
   group by sql2.pId, sql2.typeCode
   union 
   select sql5.pId, sql5.typeCode, 0 as fCount
 from sql5
where sql5.pId = '22'
group by sql5.pId, sql5.typeCode
)
select sql5.pId,
sql5.typeCode,
(case when sql5.pId = '22' then
   (select p.d_chn
  from qlp p
 where p.version_no = sql5.versionNo
   and p.cno = sql5.pValue
   and (p.typeCode = sql5.typeCode or p.typeCode is null))
  else 
sql5.pValue || ''
  end) pValue,
sql6.fCount,
(case when d.delta = 'Y' then d.dy_val
else d.y_val
end) yVal,
(case when d.is_delta = 'Y' then d.dr_val
else d.r_val
end) rVal,
f.p_no pNo,
f.p_name ||(case when f.unit = '' then ''
else '('|| f.unit ||')'
 end) pName,
f.pe_name || (case when f.unit = '' then ''
   else '(' || f.unit || ')'
 end) peName,
c.fp_name fpName,
f.order_rule as orderRule,
f.pflag pFlag,
f.pdesc as pDesc
   from sql5, sql6, qfpl f, qpa d,qfp c
  where sql5.pId = sql6.pId
and sql5.typeCode = sql6.typeCode
and sql5.pId = f.pid||''
and f.deleted = 0
and f.pid = d.pid
and sql5.typeCode = d.typeCode
and f.fp_id = c.fp_id
   order by f.t_sort, c.fp_id,f.p_no

Re: Why the sql is not executed in parallel mode

[Thomas Munro]

On Wed, Sep 19, 2018 at 1:53 PM jimmy  wrote:
>
> Why the sql is not executed in parallel mode, does the sql has some problem?
> with sql1 as

Hello Jimmy,

WITH is the problem.  From the manual[1]:  "The following operations
are always parallel restricted.  Scans of common table expressions
(CTEs). ...".  That means that these CTEs can only be scanned in the
leader process.

If you rewrite the query using sub selects it might do better.  FWIW
there is a project to make WITH work like subselects automatically in
a future release of PostgreSQL:

https://www.postgresql.org/message-id/flat/87sh48ffhb@news-spur.riddles.org.uk

[1] https://www.postgresql.org/docs/10/static/parallel-safety.html

-- 
Thomas Munro
http://www.enterprisedb.com

Re: how to know whether query data from memory after pg_prewarm

[Thomas Munro]

On Wed, Sep 19, 2018 at 1:35 PM jimmy  wrote:
> I use select pg_prewarm('table1','read','main')  to load data of table1 into 
> the memory.
> when I use select count(1) from table1 group by aa to query data.
> I find the speed of query is not fast, I wonder whether it query data from 
> memory.
> And it is slower than Oracle, both of Oracle and Postgresql has same table 
> and count of data.
> when pg_prewarm use 'read' mode,  the data is put into the OS cache, how to 
> examine the table which is pg_prewarmed into the OS cache .
> I know pg_buffercache ,but it just examine the table in the shared buffer of 
> Postgresql, not the table in the OS cache.

This is a quick and dirty hack, but it might do what you want:

https://github.com/macdice/pgdata_mincore

Tested on FreeBSD, not sure how well it'll travel.

-- 
Thomas Munro
http://www.enterprisedb.com

Replicate Tables from SAP (DB2/HANA) to PostgreSQL

[Thomas Güttler]

Hi,

is it possible to replicate some tables from SAP to PostgreSQL?

At the moment there are two underlaying database systems.

Some run DB2 and some run SAP-HANA.

In my case it would be nice, if it would be possible to replicate
only some rows, not all.

The replication should be unidirectional. The data in PostgreSQL
is only needed for reading, not for inserts/updates.

Regards,
  Thomas Güttler

--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines