Re: Insert data if it is not existing

[David G. Johnston]

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 two of the markers are for identifiers.
>
>
The count is indeed off but the two markers after the main select are
literals, not identifiers.  As is the one being compared to name.

SELECT 'Scott', 23;

is a valid query.

David J.

Re: Insert data if it is not existing

[Adrian Klaver]

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 EXISTS(SELECT name FROM my_table WHERE name= %s)
''', (Scott', 23, 'Scott'))





Trying to coordinate with Lead Dev about adding Index On The Fly





-- 
Adrian Klaver

adrian.kla...@aklaver.com 


Thank you Master, the name=%s solved it.


Please show the complete example that worked for completeness.

FYI, psql is your friend. When I work out queries I try them in psql 
first and then move up to whatever interface I will be using. This is 
usually done on a dev server so mistakes don't bring things down. If I 
have to work on a production instance then I do:

BEGIN;
some_query;
ROLLBACK;


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[Adrian Klaver]

On 05/23/2018 08:04 PM, tango ward wrote:


On Thu, May 24, 2018 at 10:55 AM, David G. Johnston 
> 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.



What I did is

'''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 two of the markers are for identifiers.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[Adrian Klaver]

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.

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
markers in the query for the parameter values in the tuple to bind
to. So are you swallowing the exception in you code?



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 it a shot


Not tested:
'''
INSERT INTO my_table(%(name)s, %(age)s)
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %(name))
''', {'name': Scott', 'age': 23})
-- 
Adrian Klaver

adrian.kla...@aklaver.com 




Updated my code to this:

curr.pgsql.execute('''
INSERT INTO my_table(name, age)
SELECT %s, %s


The above is not going to work as you cannot use %s to substitute for 
identifiers, in this case the column names name and age.




WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= name)
''', ('Scott', 23))

If I remove SELECT statement, I will get an error message: error : 
psycopg2.ProgrammingError: syntax error at or near "WHERE"

LINE 12: WHERE NOT EXISTS


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 EXISTS(SELECT name FROM my_table WHERE name= %s)
''', (Scott', 23, 'Scott'))





Trying to coordinate with Lead Dev about adding Index On The Fly






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[tango ward]

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 knowledge of the various
> parts that will make up the solution, and have been given more in the rest
> of this thread, and figuring out how they go together is something you will
> either get, or not.
>
> Or wait for a less harsh person to give you the answer and move you
> forward to the next beginner's problem.
>
> David J.
>
>
>
Noted Sir. Thank you.

Re: Insert data if it is not existing

[David G. Johnston]

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 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 in my_table the exists returns true, and the not inverts it to
>> false and the main select returns zero rows.  You have successfully
>> inserted a record that doesn't exist (i.e., you've inserted nothing just
>> like you observe).
>>
>> David J.
>>
>
> Any advice on this Sir? Even adding the FROM statement in SELECT statement
> doesn't insert the data
>
>
> INSERT INTO my_table(name, age)
> SELECT %s, %s
> FROM my_table
> WHERE NOT EXISTS(SELECT name from my_table WHERE name = name)''',
> ('Scott', 23)
>
> I also need to perform the same task but on another table but the data for
> that is from another DB.
>

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 knowledge of the various
parts that will make up the solution, and have been given more in the rest
of this thread, and figuring out how they go together is something you will
either get, or not.

Or wait for a less harsh person to give you the answer and move you forward
to the next beginner's problem.

David J.

Re: Insert data if it is not existing

[tango ward]

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)
>> ''', ('Scott', 23))
>>
>
> So, WHERE name = name is ALWAYS true and so as long as there is at least
> one record in my_table the exists returns true, and the not inverts it to
> false and the main select returns zero rows.  You have successfully
> inserted a record that doesn't exist (i.e., you've inserted nothing just
> like you observe).
>
> David J.
>

Any advice on this Sir? Even adding the FROM statement in SELECT statement
doesn't insert the data


INSERT INTO my_table(name, age)
SELECT %s, %s
FROM my_table
WHERE NOT EXISTS(SELECT name from my_table WHERE name = name)''', ('Scott',
23)

I also need to perform the same task but on another table but the data for
that is from another DB.

Re: Insert data if it is not existing

[David G. Johnston]

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 in my_table the exists returns true, and the not inverts it to
false and the main select returns zero rows.  You have successfully
inserted a record that doesn't exist (i.e., you've inserted nothing just
like you observe).

David J.

Re: Insert data if it is not existing

[tango ward]

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', 23))
>>
>
> Pretty sure this would throw an exception as there are no parameter
> markers in the query for the parameter values in the tuple to bind to. So
> are you swallowing the exception in you code?
>
>
>
>> 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 it a shot
>>
>
> Not tested:
> '''
> INSERT INTO my_table(%(name)s, %(age)s)
> WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %(name))
> ''', {'name': Scott', 'age': 23})
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



Updated my code to this:

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))

If I remove SELECT statement, I will get an error message: error :
psycopg2.ProgrammingError: syntax error at or near "WHERE"
LINE 12: WHERE NOT EXISTS

Trying to coordinate with Lead Dev about adding Index On The Fly

Re: Insert data if it is not existing

[Adrian Klaver]

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://www.postgresql.org/docs/10/static/sql-insert.html#SQL-ON-CONFLICT

"index_expression

Similar to index_column_name, but used to infer expressions on 
table_name columns appearing within index definitions (not simple 
columns). Follows CREATE INDEX format. SELECT privilege on any column 
appearing within index_expression is required.

"

I take this to mean something like:

ON CONFLICT UNIQUE INDEX name_idx ON my_table(name)




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)
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 from anywhere, for example:

SELECT name, age FROM some_table.

The second thing I see is why not use ON CONFLICT?



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:

     On 05/23/2018 10:00 AM, David G. Johnston wrote:

         On Wednesday, May 23, 2018, tango ward

         > 
         
>




-- 
Adrian Klaver

adrian.kla...@aklaver.com 





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[David G. Johnston]

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

[Adrian Klaver]

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 
markers in the query for the parameter values in the tuple to bind to. 
So are you swallowing the exception in you code?





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 it a shot


Not tested:
'''
INSERT INTO my_table(%(name)s, %(age)s)
WHERE NOT EXISTS(SELECT name FROM my_table WHERE name= %(name))
''', {'name': Scott', 'age': 23})




On Thu, May 24, 2018 at 8:04 AM, David G. Johnston 
> wrote:


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 either.


I'm doubting your assertion that it doesn't error.   How do you run
that query such that age and name are recognized given the main
query doesn't have a from clause?

David J.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Insert data if it is not existing

[tango ward]

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 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 from anywhere, for example:
>>
>> SELECT name, age FROM some_table.
>>
>> The second thing I see is why not use ON CONFLICT?
>>
>>
>>>
>>> this doesn't give me error but it doesn't insert data either.
>>>
>>> On Thu, May 24, 2018 at 3:35 AM, Adrian Klaver <
>>> adrian.kla...@aklaver.com > wrote:
>>>
>>> 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 answer is yes:
>>>
>>> CREATE TABLE test_t (a varchar, b varchar, c integer);
>>> INSERT INTO test_t
>>> SELECT '1', '2', 3 WHERE false;​ --where false causes the data
>>> to effectively "not exist"
>>>
>>> As for ON CONFLICT: conflicts can only happen between things
>>> that exist.
>>>
>>>
>>> Well that made my day:)
>>>
>>>
>>> David J.
>>>
>>>
>>>
>>> -- Adrian Klaver
>>> adrian.kla...@aklaver.com 
>>>
>>>
>>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>

Re: Insert data if it is not existing

[tango ward]

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)
>> 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 from anywhere, for example:
>
> SELECT name, age FROM some_table.
>
> The second thing I see is why not use ON CONFLICT?
>
>
>>
>> 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:
>>
>> 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 answer is yes:
>>
>> CREATE TABLE test_t (a varchar, b varchar, c integer);
>> INSERT INTO test_t
>> SELECT '1', '2', 3 WHERE false;​ --where false causes the data
>> to effectively "not exist"
>>
>> As for ON CONFLICT: conflicts can only happen between things
>> that exist.
>>
>>
>> Well that made my day:)
>>
>>
>> David J.
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Insert data if it is not existing

[tango ward]

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 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 from anywhere, for example:
>
> SELECT name, age FROM some_table.
>
> The second thing I see is why not use ON CONFLICT?
>
>
>>
>> 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:
>>
>> 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 answer is yes:
>>
>> CREATE TABLE test_t (a varchar, b varchar, c integer);
>> INSERT INTO test_t
>> SELECT '1', '2', 3 WHERE false;​ --where false causes the data
>> to effectively "not exist"
>>
>> As for ON CONFLICT: conflicts can only happen between things
>> that exist.
>>
>>
>> Well that made my day:)
>>
>>
>> David J.
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Insert data if it is not existing

[tango ward]

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 it a shot


On Thu, May 24, 2018 at 8:04 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> 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 either.
>>
>>
> I'm doubting your assertion that it doesn't error.   How do you run that
> query such that age and name are recognized given the main query doesn't
> have a from clause?
>
> David J.
>

Re: Insert data if it is not existing

[David G. Johnston]

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 either.
>
>
I'm doubting your assertion that it doesn't error.   How do you run that
query such that age and name are recognized given the main query doesn't
have a from clause?

David J.

Re: Insert data if it is not existing

[tango ward]

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:

> On 05/23/2018 10:00 AM, David G. Johnston wrote:
>
>> On Wednesday, May 23, 2018, tango ward  tangowar...@gmail.com>> 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_t
>> SELECT '1', '2', 3 WHERE false;​ --where false causes the data to
>> effectively "not exist"
>>
>> As for ON CONFLICT: conflicts can only happen between things that exist.
>>
>
> Well that made my day:)
>
>
>> David J.
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Renice on Postgresql process

[Joshua D. Drake]

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 database processes take longer to relinquish 
their resources and we won't achieve anything out of renice, So i 
assume renice of the database processes is not at all required ?

Thanks
Ayappan P


Yes, if you make a db process nicer than the db takes longer to answer 
your queries. If the goal is to keep the load down on the db, that is 
usually going to be counterproductive.


Correct or in other words, the problem is bad provisioning. You need to 
optimize your resources whether that be hardware/vm or code.


JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Renice on Postgresql process

[Ben Chobot]

> 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 and we won't achieve anything out of renice, So i assume renice of 
> the database processes is not at all required ?
>  
> Thanks
> Ayappan P
>  

Yes, if you make a db process nicer than the db takes longer to answer your 
queries. If the goal is to keep the load down on the db, that is usually going 
to be counterproductive.

Re: pg_multixact/members growing

[Alvaro Herrera]

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 hundreds MBs.
> 
> This is consistent with the idea that something is holding back the
> oldest-transaction horizon.  (However, I'd think you'd also be having
> serious problems with table bloat if that were true, so it's a bit
> mystifying.)  Did you check for unclosed prepared transactions?

Another option is that you have a standby server with
hot_standby_feedback enabled, and an open transaction there.  I'm not
sure to what extent it is possible for that to cause multixact problems,
but it wouldn't hurt to check.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: pg_multixact/members growing

[Tom Lane]

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 that something is holding back the
oldest-transaction horizon.  (However, I'd think you'd also be having
serious problems with table bloat if that were true, so it's a bit
mystifying.)  Did you check for unclosed prepared transactions?

select * from pg_prepared_xacts;

regards, tom lane

Re: pg_multixact/members growing

[Tiffany Thang]

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 "wraparound prevention"
autovacuums?

Sorry, I failed to follow the calculation. How did you get
“~435 million more members can be created.”?

What happens when no more members can be created? Does the database halt or
shut down?

Thanks.

On Tue, May 22, 2018 at 7:20 PM Thomas Munro 
wrote:

> On Wed, May 23, 2018 at 7:49 AM, Tom Lane  wrote:
> > Tiffany Thang  writes:
> >> Our pg_multixact/members directory has been growing to more than 18GB
> over
> >> the last couple of months. According to the documentation, the files in
> >> there are used to support row locking by multiple transactions and when
> all
> >> tables in all databases are eventually scanned by VACUUM, the older
> >> multixacts are removed. In our case, the files are not removed.
> >
> > Hmm.  What does pg_controldata tell you about NextMultiXactId,
> > NextMultiOffset, oldestMultiXid, oldestMulti's DB?
> > Are pg_clog/ or pg_subtrans/ or pg_multixact/offsets/ getting large?
> > Is there anything at all in pg_twophase/?  Is this system a replication


> > master, and if so are any of its slaves lagging behind?
>
> Some thoughts:
>
> There are MULTIXACT_MEMBERS_PER_PAGE = 1636 members for every 8KB
> page.  The reported directory size implies 18GB / 8KB * 1636 =
> 3,859,808,256 members.  Above MULTIXACT_MEMBER_SAFE_THRESHOLD =
> 2,147,483,647 we should be triggering emergency autovacuums to try to
> reclaim space.  Only ~435 million more members can be created.
>
> Is this system now aggressively running "wraparound prevention"
> autovacuums?
>
> There are MULTIXACT_OFFSETS_PER_PAGE = 2048 multixacts for every 8KB
> page, so the default autovacuum_multixact_freeze_max_age should
> soft-cap the size of pg_multixact/offsets at around 1.5GB ~=
> 400,000,000 / 2048 * 8KB.
>
> Unfortunately autovacuum_multixact_freeze_max_age doesn't impose any
> limit on the number of members.  The totals can be quite explosive
> with high numbers of backends, because when n backends share lock a
> row we make O(n) multixacts and O(n^2) members.  First we make a
> multixact with 2 members, then a new one with 3 members, etc... so
> that's n - 1 multixacts and (n * (n + 1)) / 2 - 1 members.
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>

Re: Insert data if it is not existing

[David G. Johnston]

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_t
SELECT '1', '2', 3 WHERE false;​ --where false causes the data to
effectively "not exist"

As for ON CONFLICT: conflicts can only happen between things that exist.

David J.

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

[a]

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 team (actually, all queries from them are more than 200 lines).


I may try both of them since I am half way around my original plan. 


To link everything to policy number is my original attempt, the reason I give 
up and apply array is due to the historical transaction data, status update and 
multiple policies in one contract. But surly, by summarizing and reformatting 
the current structure, it will reduce significant number of tables and make it 
relatively easy.


Thank you again for you advice!!


shore




-- Original message --
From: "David G. Johnston"; 
Sendtime: Wednesday, May 23, 2018 10:29 PM
To: "a"<372660...@qq.com>; 
Cc: "Charles Clavadetscher"; "pgsql-general"; 
Subject: Re: RE: RE: How do I select composite array element that 
satisfyspecific 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 of the tasks is to identify those relationships 
even if they seem to be obscure.?  In this case most everything is likely 
related by policy number one way or another.

 
The results would be millions(which means many) of tables lies in database and 
each query is hundreds of lines. It is hard to create new query that target 
your info and it is dangerous to modify any set query.


?I seriously doubt you'd end up with millions of tables...and hundred line 
queries are likely going to happen in spite of your attempts to simplify.  In 
fact I'd say the number of "complex" lines will end up being higher - most of 
the lines in a normal query against a normalized database are verbose but 
simple.


Now my think was to group data into structures so that I can significantly 
decrease the amount of table, and since it can hold array, I can actually put 
historical data into one table for one year, which stops query from multiple 
historical tables and shrink the size of database.

?An array of composites is a table - your just making things difficult by not 
actually creating one up front.?



However, I am new to this and do not have experience, so if you could provide 
any suggestion, it would be extremely grateful from me.


?The scope of this database seems to be a bit much for one's first attempt at 
doing something like this...?


I'd recommend learning and then applying as much technical normalization as you 
can to your model and assume that years of modelling theory is going to be a 
better guide than inexperienced gut instinct.  Starting from a normalized 
position you can selectively de-normalize and add abstraction layers later when 
you come across actual problems that you wish to solve.


David J.

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

[David G. Johnston]

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 of the tasks is to identify those
relationships even if they seem to be obscure.​  In this case most
everything is likely related by policy number one way or another.


> The results would be millions(which means many) of tables lies in database
> and each query is hundreds of lines. It is hard to create new query that
> target your info and it is dangerous to modify any set query.
>

​I seriously doubt you'd end up with millions of tables...and hundred line
queries are likely going to happen in spite of your attempts to simplify.
In fact I'd say the number of "complex" lines will end up being higher -
most of the lines in a normal query against a normalized database are
verbose but simple.

>
> Now my think was to group data into structures so that I can significantly
> decrease the amount of table, and since it can hold array, I can actually
> put historical data into one table for one year, which stops query from
> multiple historical tables and shrink the size of database.
>

​An array of composites is a table - your just making things difficult by
not actually creating one up front.​

However, I am new to this and do not have experience, so if you could
> provide any suggestion, it would be extremely grateful from me.
>

​The scope of this database seems to be a bit much for one's first attempt
at doing something like this...​

I'd recommend learning and then applying as much technical normalization as
you can to your model and assume that years of modelling theory is going to
be a better guide than inexperienced gut instinct.  Starting from a
normalized position you can selectively de-normalize and add abstraction
layers later when you come across actual problems that you wish to solve.

David J.

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

[David G. Johnston]

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?

[a]

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 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 "[1]" there.  The csv file value has to be an array input 
literal with only one element. i.e., something like: {(1,2)}


There is no first element as such when you are creating a new record.  There 
will be however many elelements to supply to the array input.


David J.

Re: found xmin from before relfrozenxid on pg_catalog.pg_authid

[Jeremy Finzel]

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 relname='pg_authid';
>> > > >  relfrozenxid
>> > > > --
>> > > >2863429136
>>
>> > ​select txid_current();
>> >  txid_current
>> > --
>> >   41995913769
>>
>> So that's an xid of 3341208114, if you leave the epoch out. What's
>> ​​
>> autovacuum_freeze_max_age set to in that cluster?
>>
>
> ​postgres=# show autovacuum_freeze_max_age;
>  autovacuum_freeze_max_age
> ---
>  2
> (default value I think)​
>
>
>
>> Can you show pg_controldata output, and
>> ​​
>> relminmxid from that cluster?
>>
>
> ​postgres@db:~$ /usr/lib/postgresql/9.6/bin/pg_controldata  -D
> /var/lib/postgresql/9.6/main
> pg_control version number:960
> Catalog version number:   201608131
> Database system identifier:   6469368654711450114
> Database cluster state:   in production
> pg_control last modified: Tue 22 May 2018 10:20:14 PM MSK
> Latest checkpoint location:   CCB5/F9C37950
> Prior checkpoint location:CCB0/43F316B0
> Latest checkpoint's REDO location:CCB1/6706BD88
> Latest checkpoint's REDO WAL file:0001CCB10067
> Latest checkpoint's TimeLineID:   1
> Latest checkpoint's PrevTimeLineID:   1
> Latest checkpoint's full_page_writes: on
> Latest checkpoint's NextXID:  9:3341161759
> Latest checkpoint's NextOID:  190071899
> Latest checkpoint's NextMultiXactId:  59416233
> Latest checkpoint's NextMultiOffset:  215588532
> Latest checkpoint's oldestXID:2814548646
> Latest checkpoint's oldestXID's DB:   16400
> Latest checkpoint's oldestActiveXID:  3341161759
> Latest checkpoint's oldestMultiXid:   54264778
> Latest checkpoint's oldestMulti's DB: 16400
> Latest checkpoint's oldestCommitTsXid:2814548646
> Latest checkpoint's newestCommitTsXid:3341161758
> Time of latest checkpoint:Tue 22 May 2018 10:05:16 PM MSK
> Fake LSN counter for unlogged rels:   0/1
> Minimum recovery ending location: 0/0
> Min recovery ending loc's timeline:   0
> Backup start location:0/0
> Backup end location:  0/0
> End-of-backup record required:no
> wal_level setting:replica
> wal_log_hints setting:on
> max_connections setting:  2000
> max_worker_processes setting: 8
> max_prepared_xacts setting:   0
> max_locks_per_xact setting:   64
> track_commit_timestamp setting:   on
> Maximum data alignment:   8
> Database block size:  8192
> Blocks per segment of large relation: 131072
> WAL block size:   8192
> Bytes per WAL segment:16777216
> Maximum length of identifiers:64
> Maximum columns in an index:  32
> Maximum size of a TOAST chunk:1996
> Size of a large-object chunk: 2048
> Date/time type storage:   64-bit integers
> Float4 argument passing:  by value
> Float8 argument passing:  by value
> Data page checksum version:   0
> ​
> postgres=# select datname,datfrozenxid,datminmxid from pg_database order
> by datname;
>   datname  | datfrozenxid | datminmxid
> ---+--+
>  **|   2815939794 |   54265194
>  postgres  |   2863429136 |   54280819
>  template0 |   3148297669 |   59342106
>  template1 |   2816765546 |   59261794
>
>
>
>
>
>>
>> I might be daft here, but it's surely curious that the relfrozenxid from
>> the error and pg_catalog are really different (number of digits):
>> catalog: 2863429136
>> error:248712603
>>
>>
>> > ​About gdb bt - it's tricky because it is mission critical master db of
>> > huge project.
>> > I'll will try promote backup replica and check is issue persist there
>> and
>> > if yes - we will have our playground for a while, but it will require
>> > sometime to arrange.​
>>
>> You should be ok to just bt that in the running cluster, but I
>> definitely understand if you don't want to do that...  I'd appreciate if
>> you set up the a playground, because this seems like something that'll
>> reappear.
>>
>
> ​My gdb kunf-fu exceptionally rusty now, so I better play in sandbox.​
> ​And if error did't exist on fresh promoted replica it will give us useful
> info as well.​
>
> --
> Maxim Boguk
> Senior Postgresql DBA
> http://dataegret.com/ 
>
> Phone RU: +7  985 433 
> Phone UA: +380 99 143 
> Phone AU: +61  45 218 5678
>
> LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b
> Skype: maxim.boguk
>
> "Доктор, вы мне советовали так не делать, но 

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

[a]

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 doing complex structure is sourced from the nature of policy 
information. 


1, For a policy, we would have basic info about insurant, beneficiary, product 
name and so on. 


2, But that's surely not enough, we also need the history of policy status, 
when it started, when it deactivated when it break into payment and so on. 


3, Above that, we also need to have financial information about that, like 
receivable account and cash account. Adjustments and money in/out due to the 
change of policy statues (such as payment);


4, Beside that, I'm in actuary team, so we need to value the actual 
contribution each policy is doing for the company. Therefore, more information 
about cash flow projection, survival rate (regulator will require you to 
perform multiple scenarios so they would have multiple entries that conduct the 
result).


5, furthermore, for profit analysis reason, the expected cash flow and the 
actual cash flow would needed.


That is only by saying, the actual information could be much more, and all of 
them are not in some way, "aligned". 


The results would be millions(which means many) of tables lies in database and 
each query is hundreds of lines. It is hard to create new query that target 
your info and it is dangerous to modify any set query.


Now my think was to group data into structures so that I can significantly 
decrease the amount of table, and since it can hold array, I can actually put 
historical data into one table for one year, which stops query from multiple 
historical tables and shrink the size of database.


However, I am new to this and do not have experience, so if you could provide 
any suggestion, it would be extremely grateful from me.


I will look up json and try on the efficiency when I have time. And again, 
thanks for answering my questions.




-- Original --
From:  "Charles Clavadetscher";;
Date:  May 23, 2018
To:  "a"<372660...@qq.com>; "'pgsql-general'"; 

Subject:  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 know json. All I understand is that 
it is a text format that allow some customization.

 

Java and JSON are not really related. For a simple description of JSON: 
http://www.json.org


 


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 aiming at is to:


 


1, orgnize the data so that it has hierarchy and structrues for people to 
operate.


 


2, all updates, insertion, will be recorded (including who, when, for what 
reason and which element changed from what to what).


currently I wrote a C trigger to dynamically disassemble the complex structure 
and compare them one by one and generate a string that printing out every 
change along with the update user info.


 


since my amount of data are not that big and the trigger is written in C, the 
final efficient is considerablly accepted. Now my question would be if json 
would be helpful on creating a relative efficient mechanism on that..


 

I assume that it is possible and much easier, but this would require more 
knowledge on the data that you want to pack in the json structure. Besides 
that, a basic question would be if it even necessary at all to have such a 
complex structure. In many cases a simpler design is more efficient.

 

The best thing would be to have a look at how JSON works and decide for 
yourself, if it helps in your case. Creating new types and aggregating them in 
array sounds like an overkill, but I may be mistaken.

 

For example your original example in JSONB could look like this:

 

[

  {

"x": 1,

"y": 2

  },

  {

"x": 3,

"y": 4

  }

]

 

In the database:

 

CREATE t (a JSONB);

INSERT INTO t VALUES ('[{"x": 1,"y": 2},{"x": 3,"y": 4}]');

SELECT * FROM (SELECT jsonb_array_elements(a) e FROM t) x WHERE x.e->>'x' = '3';

 

e

--

 {"x": 3, "y": 4}

(1 row)

 

For completeness. The answer to your original question is:

 

SELECT * FROM (SELECT unnest(ay) AS ay FROM b) u WHERE (u.ay).x = 3;

 

  ay

---

 (3,4)

(1 row)

 

Regards

Charles

 


---Original---


From: "Charles Clavadetscher"


Date: Wed, May 23, 2018 

Re: [GENERAL] Postgre compatible version with RHEL 7.5

[Ron]

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 date with 
the latest minor release(9.6.9) is strongly advised. The bigger issue is 
that the repo is going to be at the latest release.


Not supported because only 9.6.9 is supported?

--
Angular momentum makes the world go 'round.

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

[Charles Clavadetscher]

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 know json. All I understand is that 
it is a text format that allow some customization.

 

Java and JSON are not really related. For a simple description of JSON: 
http://www.json.org

 

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 aiming at is to:

 

1, orgnize the data so that it has hierarchy and structrues for people to 
operate.

 

2, all updates, insertion, will be recorded (including who, when, for what 
reason and which element changed from what to what).

currently I wrote a C trigger to dynamically disassemble the complex structure 
and compare them one by one and generate a string
that printing out every change along with the update user info.

 

since my amount of data are not that big and the trigger is written in C, the 
final efficient is considerablly accepted. Now my
question would be if json would be helpful on creating a relative efficient 
mechanism on that..

 

I assume that it is possible and much easier, but this would require more 
knowledge on the data that you want to pack in the json
structure. Besides that, a basic question would be if it even necessary at all 
to have such a complex structure. In many cases a
simpler design is more efficient.

 

The best thing would be to have a look at how JSON works and decide for 
yourself, if it helps in your case. Creating new types and
aggregating them in array sounds like an overkill, but I may be mistaken.

 

For example your original example in JSONB could look like this:

 

[

  {

"x": 1,

"y": 2

  },

  {

"x": 3,

"y": 4

  }

]

 

In the database:

 

CREATE t (a JSONB);

INSERT INTO t VALUES ('[{"x": 1,"y": 2},{"x": 3,"y": 4}]');

SELECT * FROM (SELECT jsonb_array_elements(a) e FROM t) x WHERE x.e->>'x' = '3';

 

e

--

{"x": 3, "y": 4}

(1 row)

 

For completeness. The answer to your original question is:

 

SELECT * FROM (SELECT unnest(ay) AS ay FROM b) u WHERE (u.ay).x = 3;

 

  ay

---

(3,4)

(1 row)

 

Regards

Charles

 

---Original---

From: "Charles Clavadetscher" >

Date: Wed, May 23, 2018 19:29 PM

To: "'pgsql-general'" >;"'a'"<372660...@qq.com
 >;

Subject: 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 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

I did not really follow this thread, so I am not in clear, why you want to 
complicate your life that much.
You create a custom data type and then use it in an array in a column. A 
complex hierarchical structure.
Why don't you simply use JSON or JSONB? Your example sounds terribly academic 
very much like a school assignment.

Bye
Charles

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

[David G. Johnston]

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 "[1]" there.  The csv file value has to be an array input
literal with only one element. i.e., something like: {(1,2)}

There is no first element as such when you are creating a new record.
There will be however many elelements to supply to the array input.

David J.

Re: [GENERAL] Postgre compatible version with RHEL 7.5

[Adrian Klaver]

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 strongly advised. The bigger issue is 
that the repo is going to be at the latest release.




Ericsson 

*DEEPTI SHARMA *
Specialist
ITIL 2011 Foundation Certified
BDGS, R


*Ericsson*
3rd Floor, ASF Insignia - Block B Kings Canyon,
Gwal Pahari, Gurgaon, Haryana 122 003, India
Phone 0124-6243000
deepti.s.sha...@ericsson.com 
www.ericsson.com 

*From:*David G. Johnston [mailto:david.g.johns...@gmail.com]
*Sent:* Monday, May 21, 2018 6:54 PM
*To:* Deepti Sharma S 
*Cc:* Adrian Klaver ; 
pgsql-gene...@postgresql.org

*Subject:* Re: [GENERAL] Postgre compatible version with RHEL 7.5

On Sun, May 20, 2018 at 10:15 PM, Deepti Sharma S 
> wrote:


Hello Team,

Can you please let us know what postgre version is compatible with
RHEL7.5? We are currently using Postgre version 9.6.6.

​9.6.6 is compatible but not supported - the current supported release 
for that 9.6 version is ​9.6.9 (more generally, the 5 releases that are 
shown on the home page).


David J.




--
Adrian Klaver
adrian.kla...@aklaver.com

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

[a]

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 aiming at is to:


1, orgnize the data so that it has hierarchy and structrues for people to 
operate.


2, all updates, insertion, will be recorded (including who, when, for what 
reason and which element changed from what to what).
currently I wrote a C trigger to dynamically disassemble the complex structure 
and compare them one by one and generate a string that printing out every 
change along with the update user info.


since my amount of data are not that big and the trigger is written in C, the 
final efficient is considerablly accepted. Now my question would be if json 
would be helpful on creating a relative efficient mechanism on that..


 
---Original---
From: "Charles Clavadetscher"
Date: Wed, May 23, 2018 19:29 PM
To: "'pgsql-general'";"'a'"<372660...@qq.com>;
Subject: 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 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

I did not really follow this thread, so I am not in clear, why you want to 
complicate your life that much.
You create a custom data type and then use it in an array in a column. A 
complex hierarchical structure.
Why don't you simply use JSON or JSONB? Your example sounds terribly academic 
very much like a school assignment.

Bye
Charles

Postgresql process aborted on shutdown of filesystem holding the database

[Sangeeth Keeriyadath]

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 :
 
IOT/Abort trap in raise at 0x9000290e2ac
0x9000290e2ac (raise+0x6c) e8410028 ld   r2,0x28(r1)
(dbx) t
raise(??) at 0x9000290e2ac
abort() at 0x9000292cc38
errfinish() at 0x1b848
XLogWrite() at 0x1000d7170
XLogBackgroundFlush() at 0x1000e4aa4
WalWriterMain() at 0x100500fc8
AuxiliaryProcessMain() at 0x1004ce0ec
StartChildProcess() at 0x1000b7000
reaper() at 0x1000bbc6c
__fd_select(??, ??, ??, ??, ??) at 0x90002a3400c
postmaster.do_start_bgworker.ServerLoop() at 0x1000b9220
PostmasterMain() at 0x1000bdb6c
main() at 0x106bc
(dbx)

 
From what i understand from the stack/code it indicates that a possible failure to write to the file-system caused Postgresql process to abort itself.
Could you please let me know if this kind of abort of Postgresql is expected when the file-system goes down ?
 
Or if i need to provide more information, let me know.
Thanks for the help in advance!
 
Regards,Sangeeth Keeriyadath

RE: Insert data if it is not existing

[Steven Winfield]

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 (or 
was implied) then the ON CONFLICT part is executed instead. An UPDATE here can 
raise further errors, of course.
If the INSERT fails for a different reason then the ON CONFLICT part is not 
executed.

Steve.

From: tango ward [mailto:tangowar...@gmail.com]
Sent: 23 May 2018 10:46
To: Steven Winfield
Cc: pgsql-generallists.postgresql.org
Subject: 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 
> 
wrote:
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.postgresql.org
Subject: 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


This email is confidential. If you are not the intended recipient, please 
advise us immediately and delete this message. The registered name of Cantab- 
part of GAM Systematic is Cantab Capital Partners LLP. See - 
http://www.gam.com/en/Legal/Email+disclosures+EU
 for further information on confidentiality, the risks of non-secure electronic 
communication, and certain disclosures which we are required to make in 
accordance with applicable legislation and regulations. If you cannot access 
this link, please notify us by reply message and we will send the contents to 
you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and 
use information about you in the course of your interactions with us. Full 
details about the data types we collect and what we use this for and your 
related rights is set out in our online privacy policy at 
https://www.gam.com/en/legal/privacy-policy.
 Please familiarise yourself with this policy and check it from time to time 
for updates as it supplements this notice

Re: Help in Postgresql

[legrand legrand]

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://www.postgresql-archive.org/Poc-pg-stat-statements-with-planid-td6014027.html

remark: this extension doen't care about statements finished in error or
timeout
 
Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Insert data if it is not existing

[tango ward]

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:
>
> 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.postgresql.org
> *Subject:* 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
>
> --
>
>
> *This email is confidential. If you are not the intended recipient, please
> advise us immediately and delete this message. The registered name of
> Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See -
> http://www.gam.com/en/Legal/Email+disclosures+EU
>  for further information
> on confidentiality, the risks of non-secure electronic communication, and
> certain disclosures which we are required to make in accordance with
> applicable legislation and regulations. If you cannot access this link,
> please notify us by reply message and we will send the contents to you.GAM
> Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and
> use information about you in the course of your interactions with us. Full
> details about the data types we collect and what we use this for and your
> related rights is set out in our online privacy policy at
> https://www.gam.com/en/legal/privacy-policy
> . Please familiarise yourself
> with this policy and check it from time to time for updates as it
> supplements this notice-- *
>

RE: Insert data if it is not existing

[Steven Winfield]

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.postgresql.org
Subject: 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

This email is confidential. If you are not the intended recipient, please 
advise us immediately and delete this message. 
The registered name of Cantab- part of GAM Systematic is Cantab Capital 
Partners LLP. 
See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information 
on confidentiality, the risks of non-secure electronic communication, and 
certain disclosures which we are required to make in accordance with applicable 
legislation and regulations. 
If you cannot access this link, please notify us by reply message and we will 
send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and 
use information about you in the course of your interactions with us. 
Full details about the data types we collect and what we use this for and your 
related rights is set out in our online privacy policy at 
https://www.gam.com/en/legal/privacy-policy. 
Please familiarise yourself with this policy and check it from time to time for 
updates as it supplements this notice.

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

[a]

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

[tango ward]

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?

[a]

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: Wednesday, May 23, 2018 5:11 PM
To: "a"<372660...@qq.com>; 
Cc: "pgsql-general"; 
Subject:  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 export the first element of table B into an csv file:
>
> COPY B(Ay[1])
> to 'E:/products_199.csv'  DELIMITER ',' CSV HEADER;
>
> The code above reported an syntax error.
>
> How should I do it??
>

Try COPY (SELECT Ay[1] FROM B) to 'E:/products_199.csv'  DELIMITER ','
CSV HEADER;

Regards,
Amul

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

[amul sul]

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 export the first element of table B into an csv file:
>
> COPY B(Ay[1])
> to 'E:/products_199.csv'  DELIMITER ',' CSV HEADER;
>
> The code above reported an syntax error.
>
> How should I do it??
>

Try COPY (SELECT Ay[1] FROM B) to 'E:/products_199.csv'  DELIMITER ','
CSV HEADER;

Regards,
Amul

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

[a]

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.csv'  DELIMITER ',' CSV HEADER;



The code above reported an syntax error.


How should I do it??


Thank you so much!


Shore

Re: Error on vacuum: xmin before relfrozenxid

[Paolo Crosato]

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= ERROR:
> > found xmin 2600758304 from before relfrozenxid 400011439
> > May  8 11:26:46 xxx postgres[32543]: [1154-2] user=,db=,client= CONTEXT:
> > automatic vacuum of table "template1.pg_catalog.pg_authid"
>
> pg_authid (along with a few other tables) is shared between
> databases. So that's just hte same error.  At which rate are you
> creating / updating database users / roles?
>

Once or twice a month. Yesterday I updated some rows on the table with a

update pg_auth_id set rolname=rolname where rolname=...

for some users in the hope to work around the issue, but it didn't work.
That's why there are recent xmin on some rows.

Best Regards,

Paolo Crosato