stored procedure call is not working with "select procedure()" option

Hi , I have a stored procedure name "procedure()". Which I am calling by -- Session sess = (Session)entityManager.getDelegate(); //entityManager is javax.persistent.EntityManager[ Properly Initialise] sess.createSQLQuery("select procedure()"); procedure is containing some t

Re: Connection loosing at some places - caused by firewall

On Mon, 19 Feb 2018 13:02:30 +0100, Durumdara wrote: >2018-02-13 21:21 GMT+01:00 George Neuner : > >> On Tue, 14 Nov 2017 12:09:31 +0100, Durumdara >> wrote: >> >> >*I disabled my firewall at home - the [keepalive] problem vanished!!!* >> >> What firewall are you using? Windows own firewall doe

Performance issues during backup

Hi We perform nighty base backup of our production PostgreSQL instance. We have a script that basically puts the instance into back mode and then backs up (tar) the /Data directory and then takes it out of backup mode. Ie, psql -c "SELECT pg_start_backup('${DATE}');" tar -cvf - ${DATA_DIR} --exc

Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6

On Mon, Feb 19, 2018 at 04:17:18PM -0500, Tom Lane wrote: > Well, as far as I'm concerned the patch that was proposed there is > far too fragile to be acceptable. We need to find out what the > Microsoft-approved way of getting the information is. Assuming that > we know the contents of non-expor

Re: Deadlock with 2 processes waiting on transactionids and locking unrelated tables

Oh I see. Yeah it’s kind of obvious now that you point it out! > Btw, do the transactions use explicit locking? We occasionally use for update so that could be involved. We’ll have a closer look at the code involved. I’m still curious about why the locks are both transaction locks rather tha

Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6

Aditya Nugraha writes: > Found out that the patch that is included in that discussion is > working fine for Visual Studio 2017 15.5.6, i think postgresql > developer should include it on upstream branch or 9.6.x variant. Well, as far as I'm concerned the patch that was proposed there is far too f

Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6

Found out that the patch that is included in that discussion is working fine for Visual Studio 2017 15.5.6, i think postgresql developer should include it on upstream branch or 9.6.x variant. Thanks a lot Tom! @Thomas I haven't checked it out but as above mentioned, the patch is working fine so

Re: pgBackRest backup from standby

El 19/02/18 a las 17:53, David Steele escribió: > On 2/19/18 3:41 PM, Don Seiler wrote: > >> I'm curious, why even copy the pg_log logs? They aren't needed for >> database restore or recovery. > > Also, relocating the log directory is easy using the log_directory > setting, so that's what I recom

Re: pgBackRest backup from standby

On Mon, Feb 19, 2018 at 2:53 PM, David Steele wrote: > > Also, relocating the log directory is easy using the log_directory > setting, so that's what I recommend if it's an issue. Some users do > want to backup their logs. > > That's probably a lot better idea than symlinking anyway. I'll look t

Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6

On Tue, Feb 20, 2018 at 6:23 AM, Tom Lane wrote: > Aditya Nugraha writes: >>When trying to compile postgresql 9.6.7 with Visual Studio 15.5.6, >> i am getting errors at these following line : >> src/backend/utils/adt/pg_locale.c(927): error C2037: left of >> 'locale_name' specifies undefine

Re: pgBackRest backup from standby

On 2/19/18 3:41 PM, Don Seiler wrote: > On Mon, Feb 19, 2018 at 10:17 AM, David Steele > wrote: > > If pg_log is symlinked to PGDATA it will be copied.  pg_xlog is not > copied in any backup. > > > So an external pg_log directory symlinked into $PGDATA will h

Re: pgBackRest backup from standby

On Mon, Feb 19, 2018 at 10:17 AM, David Steele wrote: > If pg_log is symlinked to PGDATA it will be copied. pg_xlog is not > copied in any backup. So an external pg_log directory symlinked into $PGDATA will have its log contents copied? I'm curious, why even copy the pg_log logs? They aren't

Re: Empty ./share/timezone/UTC and failure to set UTC as timezone

Hi Tom, thanks for quick response. I have to apologize to you, PG from source is fine. Sorry for wasting your time. It only happens that after I put it into archive (tar czf postgresql-9.6.7-linux-x64-binaries.tar.gz pgsql) and extract it, that it is empty and it looks like it is a bug in java li

Re: pgBackRest backup from standby

On 2/19/18 2:05 PM, Don Seiler wrote: > On Mon, Feb 19, 2018 at 12:39 PM, David Steele > wrote: > > > I read "open it for testing (or backups in this case)" as letting > recovery complete and promoting the cluster to a master before taking > the backup. >

Re: pgBackRest backup from standby

On Mon, Feb 19, 2018 at 12:39 PM, David Steele wrote: > > > I read "open it for testing (or backups in this case)" as letting > recovery complete and promoting the cluster to a master before taking > the backup. > > Don, is that the case? If it is, I think there's a problem with or > without a ti

Re: pgBackRest backup from standby

On 2/19/18 11:29 AM, Simon Riggs wrote: > On 19 February 2018 at 16:17, David Steele wrote: >>> > I did come up with a sort of Rube Goldberg-esque workaround for now >>> > involving using a clone of the prod standby VM from Veeam backup to >>> use >>> > as the backup source (after sto

Re: Empty ./share/timezone/UTC and failure to set UTC as timezone

Sasa Vilic writes: > To double check, I took official 9.6.7 sources, compiled and installed it > locally (/tmp/pgsql) and tried connecting with client again and I again we > got same error message. Hm, what platform and compiler are you using, exactly? > In order to further investigate this, I h

Re: Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6

Aditya Nugraha writes: >When trying to compile postgresql 9.6.7 with Visual Studio 15.5.6, > i am getting errors at these following line : > src/backend/utils/adt/pg_locale.c(927): error C2037: left of > 'locale_name' specifies undefined struct/union '__crt_lo > cale_data' > [C:\Users\namel

Empty ./share/timezone/UTC and failure to set UTC as timezone

Hello, we are using "embedded" PostgreSQL for integration tests (see https://github.com/yandex-qatools/postgresql-embedded) and we have noticed that our (java) clients fail to connect when their timezone is UTC, with PG server giving following error message: invalid value for parameter "TimeZone"

Error when compiling postgresql 9.6.7 with Visual Studio 15.5.6

Hello everyone, When trying to compile postgresql 9.6.7 with Visual Studio 15.5.6, i am getting errors at these following line : "C:\Users\nameless\Documents\Works\Compiling\pgwininstall\builddir\postgresql\postgresql-9.6.7\pgsql.sln" (default targe t) (1) -> "C:\Users\nameless\Documents\Works\

Re: pgBackRest backup from standby

On 19 February 2018 at 16:17, David Steele wrote: >> > I did come up with a sort of Rube Goldberg-esque workaround for now >> > involving using a clone of the prod standby VM from Veeam backup to use >> > as the backup source (after stopping recovery and opening it as a >> > standa

Re: pgBackRest backup from standby

On 2/19/18 10:32 AM, Don Seiler wrote: > On Mon, Feb 19, 2018 at 9:21 AM, David Steele > wrote: > > > Yes, they are typically very small.  The general exception to this rule > is if logs are stored in pg_log.  I recommend storing logs out of the > PGDATA d

Re: pgBackRest backup from standby

On Mon, Feb 19, 2018 at 9:21 AM, David Steele wrote: > > Yes, they are typically very small. The general exception to this rule > is if logs are stored in pg_log. I recommend storing logs out of the > PGDATA dir as they can be quite large and don't really make sense to > restore to another serv

Re: pgBackRest backup from standby

Hi Don, On 2/19/18 10:01 AM, Don Seiler wrote: > On Mon, Feb 19, 2018 at 8:53 AM, David Steele > wrote: > > Anything *not* in global (except pg_control), base, pg_tblspc, > pg_xact/pg_clog, and pg_multixact are copied from the primary. > > For example, pg

Re: pgBackRest backup from standby

On Mon, Feb 19, 2018 at 8:53 AM, David Steele wrote: > Anything *not* in global (except pg_control), base, pg_tblspc, > pg_xact/pg_clog, and pg_multixact are copied from the primary. > > For example, pg_stat is copied from the primary so these stats are > preserved on a standby backup. > So if I

Re: pgBackRest backup from standby

Hi Don, On 2/19/18 9:25 AM, Don Seiler wrote: > On Mon, Feb 19, 2018 at 8:18 AM, David Steele > wrote: > > It copies files that are not replicated from the primary so that a > primary-style backup is created. Anything that is replicated (which is > by far

Re: pgBackRest backup from standby

On Mon, Feb 19, 2018 at 8:23 AM, David Steele wrote: > > Either is fine with me, but as Michael says I might miss postings to > -general. I'm sure somebody else would catch it, though. > OK, I'll make use of the issues tracker going forward. -- Don Seiler www.seiler.us

Re: pgBackRest backup from standby

On Mon, Feb 19, 2018 at 8:18 AM, David Steele wrote: > It copies files that are not replicated from the primary so that a > primary-style backup is created. Anything that is replicated (which is > by far the bulk of the data) is copied from the standby. > OK so all data files would be copied fro

Re: pgBackRest backup from standby

On 2/18/18 10:20 PM, Michael Paquier wrote: > On Sun, Feb 18, 2018 at 06:48:30PM -0600, Don Seiler wrote: >> On Sun, Feb 18, 2018 at 6:43 PM, Michael Paquier wrote: >>> You may want to contact the maintainers directly through github where >>> the project is maintained: >>> https://github.com/pgbac

Re: pgBackRest backup from standby

Hi Don, On 2/18/18 7:34 PM, Don Seiler wrote: > > Looking to use pgBackRest to take a backup from a hot standby. I'm > reading that pgBackRest still needs to connect to the primary and copy > some files. My questions are: > > 1. What files does it need to copy? Config files? WAL files? It copi

Re: shared_buffers 8GB maximum

When we did calculation of some analytic tasks, then increasing shared_buffers had negative impact on speed. Probably hit ration was too low after change, but the maintenance of shared buffers (searching free blocks) was slower. What was the size of shared buffers when slowdown happened (app

Re: shared_buffers 8GB maximum

Yes.  I don't know the exact reason, but reading a buffer from OS cache is quite a bit more expensive than just pinning a buffer already in the buffer_pool, about 5 times more expensive the last time I tested it, which was before Meltdown.  (And just pinning a buffer which is already in the c

Re: Connection loosing at some places - caused by firewall

Hello! 2018-02-13 21:21 GMT+01:00 George Neuner : > On Tue, 14 Nov 2017 12:09:31 +0100, Durumdara > wrote: > > > >*I disabled my firewall at home - the [keepalive] problem vanished!!!* > > What firewall are you using? Windows own firewall doesn't interfere > with keepalive packets. Most comme

Re: Join query

hmidi slim wrote: > I have two tables: establishment which contains these columns: id, name, > longitude, latitude, geom (Geometric column) > Product contains: id, name, establishment_id > First of all I want to select the establishment within a radius. > I run this query: > select e.name, e1.nam

Re: Deadlock with 2 processes waiting on transactionids and locking unrelated tables

My guess is that the transaction doing: update "planscheduleitem" set "planschedule"=$1 where "psi"=$2 updates ticket before reaching that point And update ticket set unread = true where ticketid = $1 updates planscheduleitem before that Does it make sense to you? Btw, do the transactions use

Re: Deadlock with 2 processes waiting on transactionids and locking unrelated tables

Thanks for your response > Does any of the two tables have triggers? Yes the ticket table has a trigger that inserts changes into a ticketstatuslog table when the ticket.status column changes and on insert. ticket_status_insert_trigger AFTER INSERT ON ticket FOR EACH ROW EXECUTE PROCEDURE