Re: [GENERAL] Query plan not updated after dropped index

On Thu, Feb 18, 2016 at 11:05 PM, Tom Lane wrote: > Victor Blomqvist writes: > > We just had a major issue on our databases, after a index was replaced a > > user defined function didnt change its query plan to use the new index. > > I'm suspicious that this is some variant of the problem discus

Re: [GENERAL] Exporting a PDF from a bytea column

On Thu, Feb 18, 2016 at 7:42 PM, CS DBA wrote: > > http://www.postgresql.org/message-id/AANLkTi=2darhqprgtxmcx0aus9mahq2sfxxbvihtu...@mail.gmail.com > > Short answer, to avoid the binary blob problem, is to encode the binary > data, export it, then decode it. > > This can be done is psql. If you

Re: [GENERAL] Appending key-value to JSONB tree

Answering my own question here... The gist is that if you need to add a new key-value pair, you use *jsonb_set* on the non-existent key and then provide the value as the final parameter.. The new stored procedure looks like: CREATE OR REPLACE FUNCTION set_user_token(email TEXT, expiration INTERVAL

Re: [GENERAL] Exporting a PDF from a bytea column

On 02/18/2016 07:29 PM, David G. Johnston wrote: On Thursday, February 18, 2016, John R Pierce > wrote: On 2/18/2016 4:44 PM, CS DBA wrote: The system stores PDF's as large objects in bytea columns. Large Objects aka LO's and bytea columns

Re: [GENERAL] Exporting a PDF from a bytea column

On Thursday, February 18, 2016, John R Pierce wrote: > On 2/18/2016 4:44 PM, CS DBA wrote: > >> The system stores PDF's as large objects >> in bytea columns. >> > > Large Objects aka LO's and bytea columns are two completely different > things. > > I'll assume the "column" is the most relevant te

Re: [GENERAL] Exporting a PDF from a bytea column

On 2/18/2016 4:44 PM, CS DBA wrote: The system stores PDF's as large objects in bytea columns. Large Objects aka LO's and bytea columns are two completely different things. bytea columns are regular SQL database columns that contain a binary byte array.large objects (LO) are a whole sep

Re: [GENERAL] Live steraming replication setup issue!

> Currently we have master -> slave -> DR hot standby streaming replication in > current prod environment. Between master and slave server replication running > fine. Between slave and DR server replication is broken and I am trying to fix > it. For DR server, slave server is master server. > > I

[GENERAL] Exporting a PDF from a bytea column

Hi all; we have inherited a legacy database. The system stores PDF's as large objects in bytea columns. Can anyone send me an example of exporting from a bytea column to a PDF file? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to yo

Re: [GENERAL] Live steraming replication setup issue!

> > How do I setup replication between DR server and slave server while slave > server and master server are running? I cannot stop master server. Can > someone please guide with steps? > > > Steps are pretty much similar. You can setup replication between slave and DR by using the backup of Maste

Re: [GENERAL] Multiple databases and shared_buffers

On 02/18/2016 02:15 PM, Rakesh Kumar wrote: aha ok it is clear now. The splitting of buffers for each db is not for the reasons you stated below, but for better management of RAM. In our current RDBMS we allocate BPs for each database based on its usage/size. With that being said, in case of PG

[GENERAL] Live steraming replication setup issue!

Hi, Currently we have master -> slave -> DR hot standby streaming replication in current prod environment. Between master and slave server replication running fine. Between slave and DR server replication is broken and I am trying to fix it. For DR server, slave server is master server. Issue:

Re: [GENERAL] Multiple databases and shared_buffers

On Thu, Feb 18, 2016 at 5:15 PM, Rakesh Kumar wrote: > aha ok it is clear now. > > The splitting of buffers for each db is not for the reasons you stated > below, but for better management of RAM. > In our current RDBMS we allocate BPs for each database based on its > usage/size. With that being

Re: [GENERAL] sslcompression / PGSSLCOMPRESSION not behaving as documented?

On Fri, Jan 16, 2015 at 10:34 AM, Maciek Sakrejda wrote: > Thanks, everyone. That seems to be it. I still haven't gotten it to work (I > tried setting OPENSSL_DEFAULT_ZLIB=true in > /etc/postgresql/9.2/main/environment and restarting the server, then adding > the same env var when connecting with

Re: [GENERAL] Multiple databases and shared_buffers

aha ok it is clear now. The splitting of buffers for each db is not for the reasons you stated below, but for better management of RAM. In our current RDBMS we allocate BPs for each database based on its usage/size. With that being said, in case of PG. having no control on BP is not a big de

Re: [GENERAL] PostgreSQL vs Firebird SQL

On Wed, Feb 10, 2016 at 10:34:53AM +0200, Achilleas Mantzios wrote: > PostgreSQL *is* a reliable DB. > > About checksums in our office master DB that's a fine idea, too bad that > pg_upgrade doesn't cope with them > (and upgrading without pg_upgrade is out of the question) Just to clarify, pg_upg

Re: [GENERAL] Multiple databases and shared_buffers

>What do you mean that the data is actually not stored in the shared buffer. >From the link you provided : >"The “shared_buffers” configuration parameter determines how much memory >is dedicated to PostgreSQL to use for caching data." Again, you misunderstand. Cached data (and queries) is for shar

Re: [GENERAL] Multiple databases and shared_buffers

On 2/18/2016 12:04 PM, Rakesh Kumar wrote: NOTE: It is very important you provide the version of PostgreSQL and O/S when addressing this mail list. Since it is a new project, we are going with: PG: 9.5.1 OS: RHEL 6.4 off topic for this list, but RHEL 6.4 was a 'snapshot' 3 years ago, and i

Re: [GENERAL] Multiple databases and shared_buffers

NOTE: It is very important you provide the version of PostgreSQL and O/S when addressing this mail list. Since it is a new project, we are going with: PG: 9.5.1 OS: RHEL 6.4

Re: [GENERAL] Multiple databases and shared_buffers

What do you mean that the data is actually not stored in the shared buffer. >From the link you provided : "The “shared_buffers” configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data." This seems to be the same like other RDBMS. Typically the termi

Re: [GENERAL] BRIN Usage

it is for reducing index size as the table become huge. sorry for confusion, by timestamp, I meant a time series number, not the sql timestamp type. I need the unique on the column to ensure no duplicate, but the btree index is getting huge so BRIN seems to solve problem but can not ensure unique

[GENERAL] Windows default directory for client certificates

PostgreSQL documentation at http://www.postgresql.org/docs/9.5/static/libpq-ssl.html says that when a client certificate is requested by a server, a windows client psql will use by default the credentials located at %APPDATA%\postgresql\postgresql.crt and %APPDATA%\postgresql\postgresql.key Howev

Re: [GENERAL] Multiple databases and shared_buffers

On Thu, Feb 18, 2016 at 4:23 AM, Rakesh Kumar wrote: > It is a business requirement that we store our clients data in separate > databases. Our sales folks insist it is non negotiable. Our current > technology does > support that and also maintain buffer pools for each db independently. > That's

Re: [GENERAL] Query plan not updated after dropped index

Victor Blomqvist writes: > We just had a major issue on our databases, after a index was replaced a > user defined function didnt change its query plan to use the new index. I'm suspicious that this is some variant of the problem discussed a couple days ago: http://www.postgresql.org/message-id/

Re: [GENERAL] Query plan not updated after dropped index

On 2/18/16, Victor Blomqvist wrote: > Hello! > > We just had a major issue on our databases, after a index was replaced a > user defined function didnt change its query plan to use the new index. At > least this is our theory, since the function in question became much slower > and as a result bro

Re: [GENERAL] Query plan not updated after dropped index

The end goal is to get rid of index bloat. If there is a better way to handle this Im all ears! /Victor On Thu, Feb 18, 2016 at 5:21 PM, Oleg Bartunov wrote: > > > On Thu, Feb 18, 2016 at 11:17 AM, Victor Blomqvist wrote: > >> Hello! >> >> We just had a major issue on our databases, after a in

Re: [GENERAL] [JDBC] JDBC behaviour

On 2/18/2016 12:26 AM, Sridhar N Bamandlapally wrote: If we want transactions in "begin-end" then its fine, but in this case all these transactions are independent with autocommit off, with autocommit OFF, when you issue the first query, jdbc generates a postgresql BEGIN; this starts a post

Re: [GENERAL] Multiple databases and shared_buffers

It is a business requirement that we store our clients data in separate databases. Our sales folks insist it is non negotiable. Our current technology does support that and also maintain buffer pools for each db independently. That's why I brought this up. Personally I don't think this is a seri

Re: [GENERAL] Query plan not updated after dropped index

On Thu, Feb 18, 2016 at 11:17 AM, Victor Blomqvist wrote: > Hello! > > We just had a major issue on our databases, after a index was replaced a > user defined function didnt change its query plan to use the new index. At > least this is our theory, since the function in question became much slowe

Re: [GENERAL] [JDBC] JDBC behaviour

Ok, let me put this way in JDBC we have *setAutoCommit( false ) *, and all dmls are independent transactions and when any transaction fails then the session not allowing next transactions in Java when we do setAutoCommit( false ) its behaving like all transactions in BEGIN-END block, this is not

Re: [GENERAL] [JDBC] JDBC behaviour

> but in this case all these transactions are independent with autocommit off, At database level, there is no "autocommit=off". There's just "begin-end". It is database who forbids .commit, not the JDBC driver. Vladimir -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To m

Re: [GENERAL] [JDBC] JDBC behaviour

If we want transactions in "begin-end" then its fine, but in this case all these transactions are independent with autocommit off, user choice to continue with commit or rollback Thanks Sridhar On Thu, Feb 18, 2016 at 1:43 PM, Vladimir Sitnikov < sitnikov.vladi...@gmail.com> wrote: > >Is it

[GENERAL] Query plan not updated after dropped index

Hello! We just had a major issue on our databases, after a index was replaced a user defined function didnt change its query plan to use the new index. At least this is our theory, since the function in question became much slower and as a result brought our system to a halt. Basically it went: 1

Re: [GENERAL] [JDBC] JDBC behaviour

>Is it a bug or do we have other any alternate way to handle this ? PostgreSQL is strongly against "partial commits to the database". If you think a bit about it, it is not that bad. You got an error, what is the business case to commit the partial transaction then? Exceptions should not be used

Re: [GENERAL] JDBC behaviour

Hi We are facing issue with PostgreSQL JDBC behaviour in Java, under autocommit false mode, 1. In between if any transaction then for next transaction, throws exception saying "current transaction is aborted, commands ignored until end of transaction block" 2. Even if exception is suppressed wi