Re: [GENERAL] pg_sample

On 10/18/2016 08:15 PM, Charles Clavadetscher wrote: Hello On 10/19/2016 04:58 AM, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Patrick B writes: ... However, this new database test server doesn't need to have all the data. I

Re: [GENERAL] pg_sample

Hello On 10/19/2016 04:58 AM, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Patrick B writes: ... However, this new database test server doesn't need to have all the data. I would like to have only the first 100 rows(example) of

Re: [GENERAL] pg_sample

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Patrick B writes: ... > However, this new database test server doesn't need to have all the data. I > would like to have only the first 100 rows(example) of each table in my > database. ... This should do what you

Re: [GENERAL] pg_sample

On Tue, Oct 18, 2016 at 10:21 PM, Adrian Klaver wrote: > On 10/18/2016 06:30 PM, Patrick B wrote: > >> >> >> 2016-10-19 13:39 GMT+13:00 Michael Paquier > >: >> >> On Wed, Oct 19, 2016 at 9:24 AM, Patrick

Re: [GENERAL] pg_sample

On 10/18/2016 06:30 PM, Patrick B wrote: 2016-10-19 13:39 GMT+13:00 Michael Paquier >: On Wed, Oct 19, 2016 at 9:24 AM, Patrick B > wrote: > However, this new

Re: [GENERAL] pg_sample

2016-10-19 13:39 GMT+13:00 Michael Paquier : > On Wed, Oct 19, 2016 at 9:24 AM, Patrick B > wrote: > > However, this new database test server doesn't need to have all the > data. I > > would like to have only the first 100 rows(example) of

Re: [GENERAL] pg_sample

On Wed, Oct 19, 2016 at 9:24 AM, Patrick B wrote: > However, this new database test server doesn't need to have all the data. I > would like to have only the first 100 rows(example) of each table in my > database. > > I'm using pg_sample to do that, but unfortunately it

Re: [GENERAL] json rendering without pretty option (compact)

On Wed, Oct 19, 2016 at 2:00 AM, Nicolas Paris wrote: > I want to minimize postgresql json size when I fetch them. > I translate columnar table to json thought json_build_object/array or even > row_to_jeon. > > While row_to_json do have a "pretty_bool" option, the latter do

[GENERAL] pg_sample

Hi guys, I got a very big database, that I need to export (dump) into a new test server. However, this new database test server doesn't need to have all the data. I would like to have only the first 100 rows(example) of each table in my database. I'm using pg_sample to do that, but

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

Tom Lane schrieb am 18.10.2016 um 16:11: I thought pg_depend only stores the dependency if the the sequence was assigned an owning column (through OWNED BY). No, there will be regular expression dependencies as well. That 'a' dependency is the one that pg_get_serial_sequence() looks for, but

Re: [GENERAL] tablesample performance

On 18 October 2016 at 22:06, Tom Lane wrote: > Simon Riggs writes: >> On 18 October 2016 at 19:34, Tom Lane wrote: >>> If you don't want to have an implicit bias towards earlier blocks, >>> I don't think that either standard

Re: [GENERAL] tablesample performance

Simon Riggs writes: > On 18 October 2016 at 19:34, Tom Lane wrote: >> If you don't want to have an implicit bias towards earlier blocks, >> I don't think that either standard tablesample method is really what >> you want. >> >> The

Re: [GENERAL] tablesample performance

On 18 October 2016 at 19:34, Tom Lane wrote: > Andy Colson writes: >> On 10/18/2016 11:44 AM, Francisco Olarte wrote: >>> This should be faster, but to me it seems it does a different thing. > >> Ah, yes, you're right, there is a bit of a difference

Re: [GENERAL] Problem changing default data_directory in PG 9.6 + CentOS6

Hi, On Mon, 2016-10-17 at 17:38 -0300, Edilmar LISTAS wrote: > I have an env running a changed data_directory fine in a devel machine PG 9.4 > using Fedora23.  > Now, I have a server machine with CentOS where I downloaded the RPMs from > repo

Re: [GENERAL] Dump all the indexes/constraints/roles

On Tue, Oct 18, 2016 at 3:11 PM, Patrick B wrote: > Thank you guys... good to know that pg_dump does all the job for me :) > > So.. If I only dump using the --schema-only option, it will dump all the > schemas, constraints, indexes and tables? > > Because probably, I'll

Re: [GENERAL] Dump all the indexes/constraints/roles

Thank you guys... good to know that pg_dump does all the job for me :) So.. If I only dump using the --schema-only option, it will dump all the schemas, constraints, indexes and tables? Because probably, I'll have to import the data manually. NOt in a single pg_restore I mean. (AWS issue)

Re: [GENERAL] out-of-order XID insertion in KnownAssignedXids

Hi, On 2016-10-18 14:57:52 +0200, fred...@huitfeldt.com wrote: > we are running many postgresql master/slave setups. The slaves are > initialised from a pg_basebackup from the master and are sync > streaming from the master. When we determine the master has failed, > the slave is promoted. Some

Re: [GENERAL] tablesample performance

Andy: On Tue, Oct 18, 2016 at 7:17 PM, Andy Colson wrote: > Ah, yes, you're right, there is a bit of a difference there. > > Speed wise: > 1) select one from ones order by random() limit 1; >> about 360ms > 2) select one from ones tablesample bernoulli(1) limit 1 ; >> about

Re: [GENERAL] tablesample performance

Andy Colson writes: > On 10/18/2016 11:44 AM, Francisco Olarte wrote: >> This should be faster, but to me it seems it does a different thing. > Ah, yes, you're right, there is a bit of a difference there. If you don't want to have an implicit bias towards earlier blocks, I

Re: [GENERAL] tablesample performance

On 10/18/2016 11:44 AM, Francisco Olarte wrote: On Tue, Oct 18, 2016 at 5:06 PM, Andy Colson wrote: I wanted to report an awesome performance boost using tablesample. In my stored function I was getting a random row using: select one into x from ones order by random()

Re: [GENERAL] Problem changing default data_directory in PG 9.6 + CentOS6

Hi, After some tests, really the problem is with original script from PG 9.6 RPM /etc/rc.d/init.d/postgresql-9.6. If I run pg_ctl, start/stop with the new data_directory works fine! Em 18-10-2016 13:49, Edilmar LISTAS escreveu: Em 18-10-2016 11:33, Melvin Davidson escreveu: On Tue, Oct

[GENERAL] json rendering without pretty option (compact)

Hello, I want to minimize postgresql json size when I fetch them. I translate columnar table to json thought json_build_object/array or even row_to_jeon. While row_to_json do have a "pretty_bool" option, the latter do not. Each json object/array I build contains spaces. Is there a workaround ?

Re: [GENERAL] Problem changing default data_directory in PG 9.6 + CentOS6

Em 18-10-2016 11:33, Melvin Davidson escreveu: On Tue, Oct 18, 2016 at 10:20 AM, Edilmar LISTAS > wrote: 1) I changed /etc/rc.d/init.d/postgresql-9.6 like this: PGDATA=/sistemas/sat4/bdpg #PGDATA=/var/lib/pgsql/9.6/data

Re: [GENERAL] tablesample performance

On Tue, Oct 18, 2016 at 5:06 PM, Andy Colson wrote: > I wanted to report an awesome performance boost using tablesample. > In my stored function I was getting a random row using: > select one into x from ones order by random() limit 1; > When the table was smaller it worked

Re: [GENERAL] Dump all the indexes/constraints/roles

On Mon, Oct 17, 2016 at 8:32 PM, Patrick B wrote: > Hi guys, > > I need to export an entire database to another server, for testing purpose. > > Is there any way to export all indexes and constraints ? > Postgres 9.2 > Patrick > By default pg_dump will export the

Re: [GENERAL] Generic way to test input arguments

2016-10-18 16:42 GMT+02:00 Saïd Assemlal : > I am writing database functions with plpgsql. (I am using Postgresql 9.4 > with centos 6) > > Here an example on what I would like to improve: > > CREATE OR REPLACE FUNCTION usp_locking_trial(p_trial_code VARCHAR(50), >

Re: [GENERAL] Generic way to test input arguments

I will try to reproduce this way on postgresql9.4 Thanks Le 2016-10-17 à 3:30 PM, Pavel Stehule a écrit : Hi 2016-10-17 21:09 GMT+02:00 Raymond O'Donnell >: On 17/10/16 16:40, said assemlal wrote: Hello, I am looking for a way to

[GENERAL] tablesample performance

I wanted to report an awesome performance boost using tablesample. In my stored function I was getting a random row using: select one into x from ones order by random() limit 1; When the table was smaller it worked fine, but the performance has slowly gotten worse. This morning I was getting

Re: [GENERAL] Generic way to test input arguments

I am writing database functions with plpgsql. (I am using Postgresql 9.4 with centos 6) Here an example on what I would like to improve: CREATE OR REPLACE FUNCTION usp_locking_trial(p_trial_code VARCHAR(50), p_trial_key VARCHAR(500)) RETURNS TEXT AS $$ DECLARE BEGIN IF

Re: [GENERAL] Problem changing default data_directory in PG 9.6 + CentOS6

On Tue, Oct 18, 2016 at 10:20 AM, Edilmar LISTAS wrote: > 1) I changed /etc/rc.d/init.d/postgresql-9.6 like this: > PGDATA=/sistemas/sat4/bdpg > #PGDATA=/var/lib/pgsql/9.6/data > > 2) I copied postgresql.conf and pg_hba.conf from /var/lib/pgsql/9.6/data > to

Re: [GENERAL] Problem changing default data_directory in PG 9.6 + CentOS6

1) I changed /etc/rc.d/init.d/postgresql-9.6 like this: PGDATA=/sistemas/sat4/bdpg #PGDATA=/var/lib/pgsql/9.6/data 2) I copied postgresql.conf and pg_hba.conf from /var/lib/pgsql/9.6/data to /sistemas/sat4/bdpg 3) I changed postgresql.conf like this: data_directory = '/sistemas/sat4/bdpg' 4)

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

Thomas Kellerer writes: > Tom Lane schrieb am 18.10.2016 um 15:20: >> Personally, I'd try looking in pg_depend to see if the column's default >> expression has a dependency on a relation of type sequence. That avoids >> all the fun of parsing the expression and turns it into

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

Tom Lane schrieb am 18.10.2016 um 15:20: >> Furthermore, what's stored in the column seems to be a string of the >> format "nextval('sequencename'::regclass)". Is there a function to >> parse this, to return just the sequence name, or will the sequence >> name always be without for instance a

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

Hanne Moa writes: > Until now we've been using pg_get_serial_sequence() to discover > which sequence is in use, but can no longer do so due to two tables > needing to share the same sequence (prior to being properly merged. No > duplicate values, luckily). For one of the

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

På tirsdag 18. oktober 2016 kl. 12:39:03, skrev Magnus Hagander < mag...@hagander.net >:     On Thu, Oct 13, 2016 at 7:35 AM, Andreas Joseph Krogh > wrote: På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian

[GENERAL] out-of-order XID insertion in KnownAssignedXids

Hi All, we are running many postgresql master/slave setups. The slaves are initialised from a pg_basebackup from the master and are sync streaming from the master. When we determine the master has failed, the slave is promoted. Some time after that, the old master is again initialised with a

Re: [GENERAL] Collations and codepages

Raimo Jormakka wrote: > In Windows 7, and using PostgreSQL 9.4.5, the collation gets set to > "English_United States.1252" when > I select the "English, United States" locale in the installer. In Linux, the > collation is set to > "en_US.UTF-8". The encoding is set to UTF-8 in both instances. >

Re: [GENERAL] Immutable datastore library?

From: Guyren Howe Sent: Monday, October 17, 2016 1:40 PM I would like to use Postgres as an immutable data store. A subject table would have a timestamp column, and that would be added to what would otherwise be the primary key. Trap updates and turn them into inserts. Have an associated

Re: [GENERAL] pg_upgrade not able to cope with pg_largeobject being in a different tablespace

On Thu, Oct 13, 2016 at 7:35 AM, Andreas Joseph Krogh wrote: > På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian < > br...@momjian.us>: > > On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote: > > I would assume that having pg_largeobject in a

Re: [GENERAL] Getting the currently used sequence for a SERIAL column

> Is this to be regarded as internal API or is it safe to use this to > find the correct sequence? I think it's safe to use. > Furthermore, what's stored in the column seems to be a string of the > format "nextval('sequencename'::regclass)". Is there a function to > parse this, to return just

[GENERAL] Getting the currently used sequence for a SERIAL column

Hi, Until now we've been using pg_get_serial_sequence() to discover which sequence is in use, but can no longer do so due to two tables needing to share the same sequence (prior to being properly merged. No duplicate values, luckily). For one of the tables, pg_get_serial_sequence() won't be

Re: [GENERAL] Dump all the indexes/constraints/roles

On 18-10-2016 06:32, Patrick B wrote: Hi guys, I need to export an entire database to another server, for testing purpose. Is there any way to export all indexes and constraints ? Postgres 9.2 Patrick Hello, pg_dump database is that you need, but if you asking do you can export data +

[GENERAL] Collations and codepages

Hi all, In Windows 7, and using PostgreSQL 9.4.5, the collation gets set to "English_United States.1252" when I select the "English, United States" locale in the installer. In Linux, the collation is set to "en_US.UTF-8". The encoding is set to UTF-8 in both instances. Will these two instances