Re: Error on vacuum: xmin before relfrozenxid

Hi! > 24 мая 2018 г., в 0:55, Paolo Crosato написал(а): > > 1) VACUUM FULL was issued after the first time the error occurred, and a > couple of times later. CLUSTER was never run. > 2) Several failovers tests were perfomed before the cluster was moved to > production. However, before the move

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, Adrian Klaver wrote: > >> '''INSERT INTO my_table(name, age) >> SELECT %s, %s >> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name=%s)''', ('Scott', >> 23) >> >> > I doubt that worked, you have three parameter markers(%s) and two > parameter values. Not only that t

Re: Insert data if it is not existing

On 05/23/2018 07:59 PM, tango ward wrote: On Thu, May 24, 2018 at 10:51 AM, Adrian Klaver Try the example I showed previously. If you do not want to use the the named parameters e.g %(name)s then use use %s and a tuple like: ''' INSERT INTO my_table(%s, %s) WHERE NOT E

Re: Insert data if it is not existing

On 05/23/2018 08:04 PM, tango ward wrote: On Thu, May 24, 2018 at 10:55 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Wednesday, May 23, 2018, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: INSERT INTO my_table(%s, %s) WHERE NOT EXISTS(SELEC

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 10:55 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, May 23, 2018, Adrian Klaver > wrote: >> >> INSERT INTO my_table(%s, %s) >> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %s) >> > > INSERT doesn't have a where clause... > > David J.

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 10:51 AM, Adrian Klaver wrote: > On 05/23/2018 06:03 PM, tango ward wrote: > >> >> On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver > > wrote: >> >> On 05/23/2018 05:11 PM, tango ward wrote: >> >> Sorry, i forgot the values. >> >

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, Adrian Klaver wrote: > > INSERT INTO my_table(%s, %s) > WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %s) > INSERT doesn't have a where clause... David J.

Re: Insert data if it is not existing

On 05/23/2018 06:03 PM, tango ward wrote: On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 05/23/2018 05:11 PM, tango ward wrote: Sorry, i forgot the values. curr.pgsql.execute(''' INSERT INTO my_table(name, age)

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 9:33 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > > I advise you fiddle with it some more and see if you can stumble upon a > functioning solution. Maybe step away from the problem for a bit, get some > fresh air, maybe sleep on it. You've demostrated know

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, tango ward wrote: > > On Thu, May 24, 2018 at 9:09 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wednesday, May 23, 2018, tango ward wrote: >> >>> >>> >>> curr.pgsql.execute(''' >>> INSERT INTO my_table(name, age) >>> SELECT %s, %s >>> WHERE NOT

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 9:09 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wednesday, May 23, 2018, tango ward wrote: > >> >> >> curr.pgsql.execute(''' >> INSERT INTO my_table(name, age) >> SELECT %s, %s >> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) >> ''', ('S

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, tango ward wrote: > > > curr.pgsql.execute(''' > INSERT INTO my_table(name, age) > SELECT %s, %s > WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) > ''', ('Scott', 23)) > So, WHERE name = name is ALWAYS true and so as long as there is at least one record

Re: Insert data if it is not existing

On Thu, May 24, 2018 at 8:19 AM, Adrian Klaver wrote: > On 05/23/2018 05:11 PM, tango ward wrote: > >> Sorry, i forgot the values. >> >> curr.pgsql.execute(''' >> INSERT INTO my_table(name, age) >> SELECT name, age >> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) >> ''', ('Scott',

Re: Insert data if it is not existing

On 05/23/2018 05:12 PM, tango ward wrote: Sorry I forgot to mention. The table that I am working on right now doesn't have any unique column. AFAIK, I can only use ON CONFLICT if there's an error for unique column. I have not tried it but I believe you can create an INDEX on the fly: https://

Re: Insert data if it is not existing

If you are going to post so many messages can you please observe the bottom-post and trim convention used of this mailing list. On Wednesday, May 23, 2018, tango ward wrote: > Tried it, but it still I am not inserting data into the table. > tried what? David J.

Re: Insert data if it is not existing

On 05/23/2018 05:11 PM, tango ward wrote: Sorry, i forgot the values. curr.pgsql.execute(''' INSERT INTO my_table(name, age) SELECT name, age WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) ''', ('Scott', 23)) Pretty sure this would throw an exception as there are no parameter ma

Re: Insert data if it is not existing

Tried it, but it still I am not inserting data into the table. On Thu, May 24, 2018 at 8:14 AM, tango ward wrote: > Oh yeah, my bad. I missed that FROM in SELECT. Sorry, i'll update the code > now. > > On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver > wrote: > >> On 05/23/2018 04:58 PM, tango wa

Re: Insert data if it is not existing

Oh yeah, my bad. I missed that FROM in SELECT. Sorry, i'll update the code now. On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver wrote: > On 05/23/2018 04:58 PM, tango ward wrote: > >> Thanks masters for responding again. >> >> I've tried running the code: >> >> INSERT INTO my_table(name, age) >>

Re: Insert data if it is not existing

Sorry I forgot to mention. The table that I am working on right now doesn't have any unique column. AFAIK, I can only use ON CONFLICT if there's an error for unique column. On Thu, May 24, 2018 at 8:04 AM, Adrian Klaver wrote: > On 05/23/2018 04:58 PM, tango ward wrote: > >> Thanks masters for r

Re: Insert data if it is not existing

Sorry, i forgot the values. curr.pgsql.execute(''' INSERT INTO my_table(name, age) SELECT name, age WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) ''', ('Scott', 23)) Sorry, I don't understand, where should I place the from clause? I just saw a sample code like this in SO, so I gave

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, tango ward wrote: > Thanks masters for responding again. > > I've tried running the code: > > INSERT INTO my_table(name, age) > SELECT name, age > WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) > > > this doesn't give me error but it doesn't insert data e

Re: Insert data if it is not existing

On 05/23/2018 04:58 PM, tango ward wrote: Thanks masters for responding again. I've tried running the code: INSERT INTO my_table(name, age) SELECT name, age WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) The first thing I see is that: SELECT name, age is not being selected fro

Re: Insert data if it is not existing

Thanks masters for responding again. I've tried running the code: INSERT INTO my_table(name, age) SELECT name, age WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name) this doesn't give me error but it doesn't insert data either. On Thu, May 24, 2018 at 3:35 AM, Adrian Klaver wrote:

Re: Renice on Postgresql process

On 05/23/2018 04:36 PM, Ben Chobot wrote: On May 7, 2018, at 11:50 PM, Ayappan P2 > wrote: We are doing "renice" on the main Postgresql process to give higher scheduling priority because other critical operations depends on the database. You are saying that the d

Re: Renice on Postgresql process

> On May 7, 2018, at 11:50 PM, Ayappan P2 wrote: > > We are doing "renice" on the main Postgresql process to give higher > scheduling priority because other critical operations depends on the database. > You are saying that the database processes take longer to relinquish their > resources an

Re: pg_multixact/members growing

On 2018-May-23, Tom Lane wrote: > Tiffany Thang writes: > > Where do I find pg_controldata? I could not locate it on the file system. > > Hmm, should be one of the installed PG executables. > > > pg_clog/ or pg_subtrans/ or pg_multixact/offsets/ are getting larger too > > but by only a few hun

Re: pg_multixact/members growing

Tiffany Thang writes: > Where do I find pg_controldata? I could not locate it on the file system. Hmm, should be one of the installed PG executables. > pg_clog/ or pg_subtrans/ or pg_multixact/offsets/ are getting larger too > but by only a few hundreds MBs. This is consistent with the idea th

Re: pg_multixact/members growing

Thanks Tom and Thomas. Where do I find pg_controldata? I could not locate it on the file system. pg_clog/ or pg_subtrans/ or pg_multixact/offsets/ are getting larger too but by only a few hundreds MBs. This is not a replicated system. How do I tell if a system is aggressively running "wraparou

Re: Error on vacuum: xmin before relfrozenxid

2018-05-23 20:32 GMT+02:00 Andres Freund : > On 2018-05-22 16:39:58 -0700, Andres Freund wrote: > > Hi, > > > > On 2018-05-23 00:04:26 +0200, Paolo Crosato wrote: > > > I managed to recover the log of the first time we run into the issue, > the > > > error was the same but on template1: > > > > >

Re: Insert data if it is not existing

On 05/23/2018 10:00 AM, David G. Johnston wrote: On Wednesday, May 23, 2018, tango ward > wrote: I just want to ask if it's possible to insert data if it's not existing yet. This seems more like a philosophical question than a technical one... ​but the ans

Re: Error on vacuum: xmin before relfrozenxid

On 2018-05-22 16:39:58 -0700, Andres Freund wrote: > Hi, > > On 2018-05-23 00:04:26 +0200, Paolo Crosato wrote: > > I managed to recover the log of the first time we run into the issue, the > > error was the same but on template1: > > > > May 8 11:26:46 xxx postgres[32543]: [1154-1] user=,db=,cl

Re: Insert data if it is not existing

On Wednesday, May 23, 2018, tango ward wrote: > I just want to ask if it's possible to insert data if it's not existing > yet. > This seems more like a philosophical question than a technical one... ​but the answer is yes: CREATE TABLE test_t (a varchar, b varchar, c integer); INSERT INTO test_

Re: RE: RE: How do I select composite array element that satisfyspecific conditions.

Thank you so much for you suggestion, it is probably a better way to normalize the data to a policy data an using multiple tables. The millions of table is not true (but there are around 60 database instances), but the hundreds of lines of query are the actual queries provided by current db te

Re: RE: RE: How do I select composite array element that satisfy specific conditions.

On Wed, May 23, 2018 at 6:50 AM, a <372660...@qq.com> wrote: > > That is only by saying, the actual information could be much more, and all > of them are not in some way, "aligned". > ​Not sure what you are getting at here - "related" is generally the better term and usually during modeling one o

Re: How do I copy an element of composite type array into csv file?

On Wed, May 23, 2018 at 7:03 AM, a <372660...@qq.com> wrote: > Thank you so much, did you mean the section 8.15.6?? ​Yes. ​

Re:How do I copy an element of composite type array into csv file?

Thank you so much, did you mean the section 8.15.6?? -- Original message -- From: "David G. Johnston"; Sendtime: Wednesday, May 23, 2018 9:18 PM To: "a"<372660...@qq.com>; Cc: "amul sul"; "pgsql-general"; Subject: How do I copy an element of composite type arra

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

On Tue, May 22, 2018 at 2:42 PM, Maxim Boguk wrote: > > > On Tue, May 22, 2018 at 10:30 PM, Andres Freund > wrote: > >> Hi, >> >> On 2018-05-22 22:18:15 +0300, Maxim Boguk wrote: >> > On Tue, May 22, 2018 at 9:47 PM, Andres Freund >> wrote: >> > > > select relfrozenxid from pg_class where reln

Re:RE: RE: How do I select composite array element that satisfy specific conditions.

Thank you so much, I may discuss more on what I am doing so that it might have a clearer version for me and if you would be so kind to provide your opinions on this. The target industry is insurance industry and the table is used to hold policy data for insurance company. The key reason for

Re: [GENERAL] Postgre compatible version with RHEL 7.5

On 05/23/2018 08:13 AM, Adrian Klaver wrote: On 05/23/2018 03:59 AM, Deepti Sharma S wrote: Hi David, “9.6.6 is compatible but not supported”, what does this means? For details see: https://www.postgresql.org/support/versioning/ Basically it is supported by the community, but keeping up to

Re: How do I select composite array element that satisfy specific conditions.

On Wednesday, May 23, 2018, a <372660...@qq.com> wrote: > > How could I select the element of Ay that satisfy x=3?? > https://www.postgresql.org/docs/10/static/arrays.html#ARRAYS-SEARCHING The note at the end of that section applies here though, you are forcing yourself to fight the nature of th

RE: RE: How do I select composite array element that satisfy specific conditions.

Hello From: a [mailto:372660...@qq.com] Sent: Mittwoch, 23. Mai 2018 14:23 To: Charles Clavadetscher ; pgsql-general Subject: Re: RE: How do I select composite array element that satisfy specific conditions. Thanks for your reply... Honestly I do not use java and don't really kn

How do I copy an element of composite type array into csv file?

On Wednesday, May 23, 2018, a <372660...@qq.com> wrote: > Thank you very much. > > BTW, may I ask if I would like to do the opposite that copy csv file > content into the first element, how should I do it?? > > COPY B(Ay[1]) > from 'E:/products_199.csv' DELIMITER ',' CSV HEADER; > you cannot put

Re: [GENERAL] Postgre compatible version with RHEL 7.5

On 05/23/2018 03:59 AM, Deepti Sharma S wrote: Hi David, “9.6.6 is compatible but not supported”, what does this means? For details see: https://www.postgresql.org/support/versioning/ Basically it is supported by the community, but keeping up to date with the latest minor release(9.6.9) is s

Re: RE: How do I select composite array element that satisfy specific conditions.

Thanks for your reply... Honestly I do not use java and don't really know json. All I understand is that it is a text format that allow some customization. However, as long as it can solve my problem, I'm happy to learn it. now I do have a complex structure of data to store. what I'm aim

RE: How do I select composite array element that satisfy specific conditions.

Hi > -Original Message- > From: a [mailto:372660...@qq.com] > Sent: Mittwoch, 23. Mai 2018 11:43 > To: pgsql-general > Subject: How do I select composite array element that satisfy specific > conditions. > > Hi, say if I have composite type and table > > create type A as( > x floa

RE: [GENERAL] Postgre compatible version with RHEL 7.5

Hi David, “9.6.6 is compatible but not supported”, what does this means? [Ericsson] DEEPTI SHARMA Specialist ITIL 2011 Foundation Certified BDGS, R&D Ericsson 3rd Floor, ASF Insignia - Block B Kings Canyon, Gwal Pahari, Gurgaon, Haryana 122 003, India Phone 0124-624300

Postgresql process aborted on shutdown of filesystem holding the database

Hello,   I have the Postgresql 10.2 database running on AIX Operating System and the database is created and stored on a distributed file-system. There was a test operation which shuts down the file-system. Soon after that, a coredump (abort) of Postgresql process was seen with the following stack

RE: Insert data if it is not existing

From the docs: “ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error.” So if the INSERT part succeeds then the ON CONFLICT part is never executed. If the INSERT fails with due to a violation of the constraint you specified

Re: Help in Postgresql

Hello Moohanad, Did you check for pg_stat_statements https://www.postgresql.org/docs/10/static/pgstatstatements.html ? This is based on postgres hooks and will give you: db,user,query id, query text There are many developments trying to add start /end time, planid, plan text as described in http:

Re: Insert data if it is not existing

thanks for the response Steven. Will ON CONFLICT DO UPDATE/NOTHING if there's no error? On Wed, May 23, 2018 at 5:43 PM, Steven Winfield < steven.winfi...@cantabcapital.com> wrote: > INSERT … ON CONFLICT DO UPDATE … is probably what you want, perhaps using > a specified unique index/constraint:

RE: Insert data if it is not existing

INSERT … ON CONFLICT DO UPDATE … is probably what you want, perhaps using a specified unique index/constraint: https://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT Steve. From: tango ward [mailto:tangowar...@gmail.com] Sent: 23 May 2018 10:04 To: pgsql-generallists.postgresq

How do I select composite array element that satisfy specific conditions.

Hi, say if I have composite type and table create type A as( x float8, y float8 ); create table B( Ay A[] ); insert into B values(array[ (1,2)::A, (3,4)::A] ); How could I select the element of Ay that satisfy x=3?? Thank you so much!! Shore

Insert data if it is not existing

Hi, I just want to ask if it's possible to insert data if it's not existing yet. I was able to play around with UPSERT before but that was when there was an error for duplicate data. In my scenario, no error message is showing. Any suggestion? Thanks, J

Re: How do I copy an element of composite type array into csv file?

Thank you very much. BTW, may I ask if I would like to do the opposite that copy csv file content into the first element, how should I do it?? COPY B(Ay[1]) from 'E:/products_199.csv' DELIMITER ',' CSV HEADER; -- Original -- From: "amul sul";; Send time: W

Re: How do I copy an element of composite type array into csv file?

On Wed, May 23, 2018 at 2:05 PM, a <372660...@qq.com> wrote: > Hi suppose I have composite type and table > > create type A as( > x float8, > y float8 > ); > > create table B( > Ay A[] > ); > > insert into B > values(array[ > (1,2)::A, > (3,4)::B] > ); > > Now I would like to ex

How do I copy an element of composite type array into csv file?

Hi suppose I have composite type and table create type A as( x float8, y float8 ); create table B( Ay A[] ); insert into B values(array[ (1,2)::A, (3,4)::B] ); Now I would like to export the first element of table B into an csv file: COPY B(Ay[1]) to 'E:/products_199.c

Re: Error on vacuum: xmin before relfrozenxid

2018-05-23 1:39 GMT+02:00 Andres Freund : > Hi, > > On 2018-05-23 00:04:26 +0200, Paolo Crosato wrote: > > I managed to recover the log of the first time we run into the issue, the > > error was the same but on template1: > > > > May 8 11:26:46 xxx postgres[32543]: [1154-1] user=,db=,client= ERRO