Re: [GENERAL] Optimizing a read-only database
François Battail francois.batt...@sipibox.fr writes: My bad, got it. May be interesting but as I have a lot of indexes it will be hard to test and to choose the best candidate. No idea of how it can affect EWKB data indexed by a GiST (PostGIS) index, but it's something to try just to know. You could also raise the statistics target and re-analyze. This will take some time, but then your query plans might be better. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Documentation bug?
Hello all, I just noticed that you can do something like this (using 9.4.1): select array[1,2,3] - 3 which is doing the same thing as: select array_remove(array[1,2,3],3) but the minus is not documented as an array operator: http://www.postgresql.org/docs/current/static/functions-array.html Is that an oversight in the documentation or isn't the minus supposed to work like that in the first place? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Documentation bug?
Thomas Kellerer wrote: I just noticed that you can do something like this (using 9.4.1): select array[1,2,3] - 3 which is doing the same thing as: select array_remove(array[1,2,3],3) I can't reproduce this on my PostgreSQL 9.4.1: test= select array[1,2,3] - 3; ERROR: operator does not exist: integer[] - integer LINE 1: select array[1,2,3] - 3; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Did you add casts or operators? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Documentation bug?
On Tue, May 19, 2015 at 7:39 PM, Thomas Kellerer spam_ea...@gmx.net wrote: Hello all, I just noticed that you can do something like this (using 9.4.1): select array[1,2,3] - 3 which is doing the same thing as: select array_remove(array[1,2,3],3) but the minus is not documented as an array operator: http://www.postgresql.org/docs/current/static/functions-array.html Is that an oversight in the documentation or isn't the minus supposed to work like that in the first place? You very likely have an intarray extension installed: http://www.postgresql.org/docs/9.4/interactive/intarray.html int[] - int operator documented in the extension documentation as it's a part of the extension but not part of the PostgreSQL core. -- Maxim Boguk Senior Postgresql DBA http://www.postgresql-consulting.ru/ http://www.postgresql-consulting.com/ Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b Skype: maxim.boguk Jabber: maxim.bo...@gmail.com МойКруг: http://mboguk.moikrug.ru/ People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage.
Re: [GENERAL] Documentation bug?
Maxim Boguk schrieb am 19.05.2015 um 13:33: Hello all, I just noticed that you can do something like this (using 9.4.1): select array[1,2,3] - 3 which is doing the same thing as: select array_remove(array[1,2,3],3) but the minus is not documented as an array operator: http://www.postgresql.org/docs/current/static/functions-array.html Is that an oversight in the documentation or isn't the minus supposed to work like that in the first place? You very likely have an intarray extension installed: http://www.postgresql.org/docs/9.4/interactive/intarray.html int[] - int operator documented in the extension documentation as it's a part of the extension but not part of the PostgreSQL core. Ah, right - that was it. Completely forgot about that. Sorry for the noise. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SQL] [GENERAL] Does PG support bulk operation in embedded C
Not sure whether I am understanding this. I checked embedded C and did not find any section which describes what I have asked, that is the ability to do multiple inserts, or updates or deletes in one sql call. For example, if my application does the following BEGIN TRANSACTION INSERT INTO TABLE_A UPDATE TABLE_B INSERT INTO TABLE_C COMMIT TRANSACTION DB2 provides to combine the three sql operations into an array and make a call to DB2 which executes the array (that is all 3 sqls as one single call). I am looking for something similar in PG. thanks Sent:Tuesday, May 19, 2015 at 8:13 PM From:Joshua D. Drake j...@commandprompt.com To:Ravi Krishna srkris...@gmx.com, pgsql-...@postgresql.org Cc:pgsql-general@postgresql.org Subject:Re: [SQL] [GENERAL] Does PG support bulk operation in embedded C On 05/19/2015 04:47 PM, Ravi Krishna wrote: To explain pls refer to this for DB2 http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html Essentially in one single sql call, we can do -- Add new rows -- Update a set of rows where each row is identified by a bookmark -- Delete a set of rows where each row is identified by a bookmark -- Fetch a set of rows where each row is identified by a bookmark This gives tremendous performance benefits as the network round trip is avoided for each sql. I am looking for an equivalent of this in PG and C language. For embedded C, I believe you are looking for: http://www.postgresql.org/docs/9.4/static/ecpg.html Thanks. -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing Im offended is basically telling the world you cant control your own emotions, so everyone else should do it for you. -- Sent via pgsql-sql mailing list (pgsql-...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [GENERAL] pg_xlog Concern
On Mon, May 18, 2015 at 9:44 PM, Sachin Srivastava ssr.teleat...@gmail.com wrote: Hi, I have done below changes in my postgresql.conf. *Checkpoint_segments= 200* and *checkpoint_timeout= 5min* I am also doing archiving and below changes in my postgresql.conf. *Wal_level=archive* *archive_command= cp %p /dbkup/momacpp_213_live/%f* *archive_mode=on* Achieving is going smoothly in /*dbkup/momacpp_213_live* folder. But currently my pg_xlog size is 60 GB and there are 3740 WAL file in this folder and in Last week this was 400 GB(pg_xlog folder) and WAL file were approx. 3. Due to archiving pg_xlog folder size is decreasing now but it’s taking one week to come in normal size. *I have 2 Question*: *First:* When I have given *checkpoint segments=200 (As per my knowledge WAL file should be generated 200*2+3=403 only)* but why it’s generating too much file. MY each WAL file is 16 MB. What do you see in the archive_status directory ? Other way around could be, if you have log_checkpoints parameter set to on, then checkpoints information will be logged into the postgresql logfile. This will help you understand how checkpoints are behaving. *Second:* Why pg_xlog size is increasing too much it should be only (403*16 MB = 6448 MB) and if Production team is not entering data in bulk and if normal production is going then it’s size remain same as per logic. Do you mean to say, pg_xlog is not getting cleared forever ? or is it getting cleared periodically and you are uncertain about the behaviour ? How I handle this case (pg_xlog folder size) when Production people entering the data in bulk, kindly suggest. I am missing something in my postgresql.conf and somewhere else. What is the *archive_timeout* value you have ? Regards, Venkata Balaji N Fujitsu Australia
[GENERAL] Does PG support bulk operation in embedded C
To explain pls refer to this for DB2 http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html Essentially in one single sql call, we can do -- Add new rows -- Update a set of rows where each row is identified by a bookmark -- Delete a set of rows where each row is identified by a bookmark -- Fetch a set of rows where each row is identified by a bookmark This gives tremendous performance benefits as the network round trip is avoided for each sql. I am looking for an equivalent of this in PG and C language. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [SQL] [GENERAL] Does PG support bulk operation in embedded C
On 05/19/2015 05:27 PM, Ravi Krishna wrote: Not sure whether I am understanding this. I checked embedded C and did not find any section which describes what I have asked, that is the ability to do multiple inserts, or updates or deletes in one sql call. For example, if my application does the following BEGIN TRANSACTION INSERT INTO TABLE_A UPDATE TABLE_B INSERT INTO TABLE_C COMMIT TRANSACTION Well PostgreSQL certainly supports the above. DB2 provides to combine the three sql operations into an array and make a call to DB2 which executes the array (that is all 3 sqls as one single call). You can do this with inserts using multivalue. INSERT INTO TABLE_A VALUES (), (), (); I am not sure about UPDATE or DELETE, I know you can use WITH on DELETE which gives you some flexibility. I don't think you will get a one to one comparison but you should be able to get close. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does PG support bulk operation in embedded C
On 05/19/2015 04:47 PM, Ravi Krishna wrote: To explain pls refer to this for DB2 http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0002329.html Essentially in one single sql call, we can do -- Add new rows -- Update a set of rows where each row is identified by a bookmark -- Delete a set of rows where each row is identified by a bookmark -- Fetch a set of rows where each row is identified by a bookmark This gives tremendous performance benefits as the network round trip is avoided for each sql. I am looking for an equivalent of this in PG and C language. For embedded C, I believe you are looking for: http://www.postgresql.org/docs/9.4/static/ecpg.html Thanks. -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does PG support bulk operation in embedded C
On Wed, May 20, 2015 at 8:47 AM, Ravi Krishna srkris...@gmx.com wrote: Essentially in one single sql call, we can do -- Add new rows -- Update a set of rows where each row is identified by a bookmark -- Delete a set of rows where each row is identified by a bookmark -- Fetch a set of rows where each row is identified by a bookmark This gives tremendous performance benefits as the network round trip is avoided for each sql. I am looking for an equivalent of this in PG and C language. What you are looking at could be accomplished with a user-defined function: http://www.postgresql.org/docs/devel/static/xfunc.html Perhaps you are looking for something in C, now it would be less complex to do it for example with pl/pgsql or another language, and call it from a C client with a correct set of arguments satisfying your needs. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
