[GENERAL] out-of-order XID insertion in KnownAssignedXids (on 9.5.3)

2016-10-19 Thread Fredrik . HuitfeldtMadsen
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] Selecting records with highest timestamp - for a join

2016-10-19 Thread David G. Johnston
On Wed, Oct 19, 2016 at 11:35 AM, Alexander Farber < alexander.far...@gmail.com> wrote: > I.e. a user can have several records in the above table, but I always use > the most recent one (the one with the highest "stamp") to display that user > in my game. > ​And if the second most recent has a

Re: [GENERAL] Selecting records with highest timestamp - for a join

2016-10-19 Thread Alexander Farber
Adrian, for both player1 and player2 (because I need to display player photos above the game board). SQL join with words_social - yes, but how to take the most recent record from that table? For example there are user infos from Google+, Facebook, Twitter - but the user has used Facebook to

Re: [GENERAL] Selecting records with highest timestamp - for a join

2016-10-19 Thread Adrian Klaver
On 10/19/2016 12:44 PM, Alexander Farber wrote: > Adrian, for both player1 and player2 (because I need to display player > photos above the game board). > > SQL join with words_social - yes, but how to take the most recent record > from that table? > > For example there are user infos from

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

2016-10-19 Thread Hanne Moa
On 2016-10-19 09:28, Thomas Kellerer wrote: > You can use the following statement to find the sequences that a table uses: > > select sn.nspname as sequence_schema, s.relname as sequence_name > from pg_class s > join pg_namespace sn on sn.oid = s.relnamespace > join pg_depend

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

2016-10-19 Thread Thomas Kellerer
Hanne Moa schrieb am 19.10.2016 um 09:06: >> regression=# create table t1 (f1 serial); >> CREATE TABLE >> regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or >> refobjid = 't1_f1_seq'::regclass; >> classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype

Re: [GENERAL] journaled FS and and WAL

2016-10-19 Thread t.dalpo...@gmail.com
So, as for the data content of the WAL file, I see that no more page will be allocated. I wonder if during a crash, strange things can still happen at disk level however, in particular in SSD devices; on these things we have no control, and perhaps journaling helps? As for the metadata, if

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

2016-10-19 Thread Hanne Moa
On 2016-10-18 16:11, Tom Lane wrote: > regression=# create table t1 (f1 serial); > CREATE TABLE > regression=# select * from pg_depend where objid = 't1_f1_seq'::regclass or > refobjid = 't1_f1_seq'::regclass; > classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype >

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Alex Ignatov (postgrespro)
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Thomas Kellerer Sent: Wednesday, October 19, 2016 1:53 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Drop user cascade Alex Ignatov (postgrespro) schrieb am

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

2016-10-19 Thread Thomas Kellerer
Hanne Moa schrieb am 19.10.2016 um 10:31: >> You can use the following statement to find the sequences that a table uses: >> > Thanks. This assumes that there is only one nextval per table though. > While this holds for the database we need this on right now, it's not a > sufficiently generic

Re: [GENERAL] pg_sample

2016-10-19 Thread Karsten Hilbert
On Wed, Oct 19, 2016 at 01:24:10PM +1300, Patrick B wrote: > I'm using pg_sample to do that, but unfortunately it doesn't work well. > It doesn't get the first 100 rows. It gets random 100 rows. > > Do you guys have any idea how could I do this? For any relevant answer to this question you'll

[GENERAL] Drop user cascade

2016-10-19 Thread Alex Ignatov (postgrespro)
Hello! Why we can DROP TABLE CASCADE, DROP VIEW CASCADE, DROP SEQUENCE CASCADE but we can't DROP USER/ROLE CASCADE? Why do Postgres have no such functionality as DROP USER CASCADE? Is there any reasons in that absence? -- Alex Ignatov Postgres Professional:

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Thomas Kellerer
Alex Ignatov (postgrespro) schrieb am 19.10.2016 um 12:26: > Hello! > > Why we can DROP TABLE CASCADE, DROP VIEW CASCADE, DROP SEQUENCE CASCADE but > we can’t DROP USER/ROLE CASCADE? > > Why do Postgres have no such functionality as DROP USER CASCADE? Is there any > reasons in that absence?

Re: [GENERAL] journaled FS and and WAL

2016-10-19 Thread Albe Laurenz
t.dalpo...@gmail.com wrote: > I don't mind about performance but I absolutely mind about reliability, > so I was thinking about the safest setting of linux FS and postgresql I > can use. Sure, use journaling then. I do it all the time. Yours, Laurenz Albe -- Sent via pgsql-general mailing list

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Alex Ignatov (postgrespro)
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane Sent: Wednesday, October 19, 2016 4:31 PM To: Alex Ignatov (postgrespro) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Drop

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Tom Lane
"Alex Ignatov \(postgrespro\)" writes: > Why do Postgres have no such functionality as DROP USER CASCADE? Is there > any reasons in that absence? The short answer is that DROP USER couldn't reach across databases to get rid of owned objects in other databases. See

Re: [GENERAL] journaled FS and and WAL

2016-10-19 Thread Alex Ignatov (postgrespro)
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of t.dalpo...@gmail.com Sent: Wednesday, October 19, 2016 11:01 AM To: Michael Paquier Cc: Albe Laurenz ;

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

2016-10-19 Thread Merlin Moncure
On Tue, Oct 18, 2016 at 12:00 PM, Nicolas Paris wrote: > 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

Re: [GENERAL] Drop user cascade

2016-10-19 Thread Alex Ignatov (postgrespro)
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Melvin Davidson Sent: Wednesday, October 19, 2016 5:35 PM To: Alex Ignatov (postgrespro) Cc: Tom Lane ; pgsql-general@postgresql.org Subject: Re:

[GENERAL] Add trigger to FDW table

2016-10-19 Thread Leonardo M . Ramé
Hi, I wonder if I can add a trigger to a Foreign Data Wrapper table. I can't create the trigger in the foreign database. Regards, -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.:

Re: [GENERAL] Add trigger to FDW table

2016-10-19 Thread Michael Paquier
On Wed, Oct 19, 2016 at 9:29 PM, Leonardo M. Ramé wrote: > Hi, I wonder if I can add a trigger to a Foreign Data Wrapper table. I can't > create the trigger in the foreign database. Creation of triggers on foreign tables is possible since 9.4. They have the same support as

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

2016-10-19 Thread Edilmar LISTAS
Em 18-10-2016 16:53, Devrim Gündüz escreveu: 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] Drop user cascade

2016-10-19 Thread Melvin Davidson
On Wed, Oct 19, 2016 at 10:03 AM, Alex Ignatov (postgrespro) < a.igna...@postgrespro.ru> wrote: > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane > Sent: Wednesday, October 19, 2016 4:31 PM > To: Alex

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

2016-10-19 Thread Bruce Momjian
On Thu, Oct 13, 2016 at 04:35:35PM +0200, Andreas Joseph Krogh wrote: > På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian > >: > > 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] pg_sample

2016-10-19 Thread Greg Sabino Mullane
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I may be overseeing something, but what about dependencies between > tables, sequencies, indexes, etc.? I guess that if one takes the first > 100 rows of a table referenced by another table, there is no guarantee > that in the first 100

Re: [GENERAL] Selecting records with highest timestamp - for a join

2016-10-19 Thread Adrian Klaver
On 10/19/2016 11:35 AM, Alexander Farber wrote: Good evening, I have a question please on which kind of statement to use - In a table I store user info coming from social networks (Facebook, Twitter, ...): CREATE TABLE words_social ( sid varchar(255) NOT NULL, social integer