Re: JDBC connectivity issue

On 03/13/2018 04:46 PM, chris wrote: I'm sorry that took a few days but I am running; Postgresql-9.4 and jre7.jar What we are looking for is the JDBC driver you are using? Thanks in advance. On 03/08/2018 02:30 PM, chris wrote: Given that the syntax looks correct for the url, how

Re: Reindex doesn't eliminate bloat

On 03/13/2018 06:10 PM, Joe Conway wrote: On 03/12/2018 09:16 PM, Ron Johnson wrote: On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote: Those queries from wiki for table and index bloat estimation are for estimation only. In many cases they show very wrong results. Better (yet not ideal)

Re: JDBC connectivity issue

I'm sorry that took a few days but I am running; Postgresql-9.4 and jre7.jar Thanks in advance. On 03/08/2018 02:30 PM, chris wrote: Given that the syntax looks correct for the url, how would we go about debugging that it's not seeing the comma? On 03/08/2018 02:27 PM, Adrian Klaver

Re: Reindex doesn't eliminate bloat

On 03/12/2018 09:16 PM, Ron Johnson wrote: > On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote: >> Those queries from wiki for table and index bloat estimation are for >> estimation only. In many cases they show very wrong results. Better >> (yet not ideal) approach is using pgstattuple extension

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

On Tue, Mar 13, 2018 at 1:47 PM, Melvin Davidson wrote: > > > >> Thank you Melvin, I forgot to mention I've already found your script >> before I asked here, but I didn’t think it was robust enough (please don't >> offend :-). Particularly, it didn't work well on PostgreSQL

Re: query_to_xml() returns invalid XML when query returns no rows

On 3/13/18 15:21, Thomas Kellerer wrote: > I still think it's incorrect to return an empty (=invalid) XML instead of a > NULL value though. This behavior is specified in the SQL standard. While an empty string is not a valid XML "document", it is valid as XML "content". -- Peter Eisentraut

Re: Is there a way to create a functional index that tables tableoid column as an arg?

Ryan Murphy writes: > Hi David! Thanks for the reply. >> tableoid might be an exception to >> this, but it does not really seem like a useful column to index, >> giving it would be indexing the same value for each record in the >> table. > Unless you're using inheritance

Re: Is there a way to create a functional index that tables tableoid column as an arg?

Hi David! Thanks for the reply. > tableoid might be an exception to > this, but it does not really seem like a useful column to index, > giving it would be indexing the same value for each record in the > table. Unless you're using inheritance - then tableoid may vary. That's the case I'm

Re: query_to_xml() returns invalid XML when query returns no rows

Peter Eisentraut schrieb am 12.03.2018 um 23:31: I am not sure if this qualifies as a bug: query_to_xml() returns an empty XML document when the query returns no rows, e.g: select query_to_xml('select 42 where false', false, true, ''); The problem with this is, that if the resulting XML

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

> Thank you Melvin, I forgot to mention I've already found your script > before I asked here, but I didn’t think it was robust enough (please don't > offend :-). Particularly, it didn't work well on PostgreSQL 10. > > Aldrin, I apologize. I just tested and found that the reason it is failing is

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

> On Mar 9, 2018, at 12:15 PM, Melvin Davidson wrote: > On Fri, Mar 9, 2018 at 10:00 AM, Aldrin Martoq Ahumada > > wrote: > Yes, here is the issue: https://github.com/influitive/apartment/issues/532 >

Re: Programmatically duplicating a schema

On 03/12/2018 11:05 PM, matt.f...@internode.on.net wrote: Thanks Adrian, Really appreciate the suggestions. The objective when trying to solve this for the Apartment library itself is to keep it generic (works for any/all database objects - tables, views, stored procedures, 3rd party

Re: UPSERT on a view

Melvin Davidson writes: > On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth wrote: >> Why does the following code raise an error? >> >> CREATE TABLE ttest (x integer); >> CREATE VIEW vtest AS SELECT x FROM ttest; >> CREATE FUNCTION vtest_insert() RETURNS

Re: UPSERT on a view

On Tue, Mar 13, 2018 at 10:02 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson > wrote: > >> On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth >> wrote: >> >>> This code raises the error 'foo',

Re: UPSERT on a view

On Tue, Mar 13, 2018 at 6:47 AM, Melvin Davidson wrote: > On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth > wrote: > >> This code raises the error 'foo', even though the insert says DO NOTHING >> and the error type is unique_violation. Why? >> >> More

Re: Question on corruption (PostgreSQL 9.6.1)

Andy Halsall writes: > db=# select * from x where col_a = 4675635; > col_a | col_b | col_c | col_d | col_e | > last_modified > +-+-+---+---+--- > | |

Re: Programmatically duplicating a schema

On Mon, Mar 12, 2018 at 11:05 PM, wrote: > The reason we'd want to infer the create statements via pg_dump is, so we > don't need to keep database migration files in sync with a 'create new > schema' SQL script. It adds risk that they get out of sync, causing >

Re: UPSERT on a view

On Mon, Mar 12, 2018 at 2:26 PM, Steven Roth wrote: > Why does the following code raise an error? > > CREATE TABLE ttest (x integer); > CREATE VIEW vtest AS SELECT x FROM ttest; > CREATE FUNCTION vtest_insert() RETURNS trigger LANGUAGE plpgsql AS $$ > RAISE 'foo' USING

UPSERT on a view

Why does the following code raise an error? CREATE TABLE ttest (x integer); CREATE VIEW vtest AS SELECT x FROM ttest; CREATE FUNCTION vtest_insert() RETURNS trigger LANGUAGE plpgsql AS $$ RAISE 'foo' USING ERRCODE='unique_violation'; END $$; CREATE TRIGGER vtest_insert INSTEAD OF INSERT ON

Re: Programmatically duplicating a schema

* > What is a reliable way to programmatically & generically populate an empty schema with all the objects in the public schema as a template? The simplest way is just to load the attached clone_schema function. It was originally created by Emanuel '3manuek', which I enhanced. Itnow copies all

Re: Point-in-time recovery after failover

Dylan Luong wrote: > We are on Postgres 9.6 and we have primary/standby wal replication setup for > HA. > > I am trying to perform a point-in-time recovery after a failover has occurred. > > I extracted the base backups (tar files) to the data directory and extracted > the all the archive

Point-in-time recovery after failover

Hi We are on Postgres 9.6 and we have primary/standby wal replication setup for HA. For PITR, we have scheduled nightly base backup and hourly archive backups on the primary server. https://www.postgresql.org/docs/9.6/static/continuous-archiving.html I am trying to perform a point-in-time

Re: Programmatically duplicating a schema

> On 13 Mar 2018, at 4:23, matt.f...@internode.on.net wrote: > > Hi all, > > What is a reliable way to programmatically & generically populate an empty > schema with all the objects in the public schema as a template? > > We are using the multi tenancy ruby gem Apartment ( >

Re: Logical decoding on standby

Am 13.03.2018 um 02:40 schrieb Andres Freund: The subject said logical decoding, not replication. There's a lot of change data capture type workloads where decoding from the standby is quite useful. And the design definitely would work for that, we've explicitly took that into consideration.

Re: Programmatically duplicating a schema

Thanks Adrian,   Really appreciate the suggestions.   The objective when trying to solve this for the Apartment library itself is to keep it generic (works for any/all database objects - tables, views, stored procedures, 3rd party extension objects,