Re: [GENERAL] PG 10 and perl

On 09/29/2017 09:32 AM, Tom Lane wrote: Andy Colson writes: I started playing with 10, and cannot get it to use the right version of perl. I'll only use the system version: root@firefly:/usr/local/pg10/lib/postgresql# ldd plperl.so libperl.so => /usr/lib64/perl5/CORE/li

Re: [GENERAL] PG 10 and perl

On 9/29/2017 9:28 AM, kbran...@pwhome.com wrote: Andy Colson wrote: In PG 9.5 this worked fine, and compiling and running PG used the 5.26 version of perl: root@firefly:/usr/local/pg95/lib/postgresql# ldd plperl.so libperl.so => /opt/perl5/perls/perl-5.26/lib/5.26.1/x86_64-linux-thr

[GENERAL] PG 10 and perl

I'm not subscribed to the -devel list, so hopefully I can post this here. I'm running slackware 14.2 I have the system perl (5.24) installed, and also perlbrew with perl 5.26 installed. In PG 9.5 this worked fine, and compiling and running PG used the 5.26 version of perl: root@firefly:/usr/l

Re: [GENERAL] pg_test_fsync performance

On 04/25/2017 05:40 AM, Karri Niemelä wrote: Hi. Wondering what sort of results are people seeing on modern servers when running pg_test_fsync? Thanks, Karri 18 sata laptop hd's (some 5400, some 7200), hardware raid 10 12 core Xeon E5645 5 seconds per test O_DIRECT supported on this platfor

Re: [GENERAL] pg_test_fsync performance

On 04/25/2017 05:40 AM, Karri Niemelä wrote: Hi. Wondering what sort of results are people seeing on modern servers when running pg_test_fsync? Thanks, Karri 4 sata HD, hardware raid 10 4 core Xeon E5504 Box is Busy 5 seconds per test O_DIRECT supported on this platform for open_datasync an

Re: [GENERAL] Merging records in a table with 2-columns primary key

On 04/02/2017 09:26 AM, Alexander Farber wrote: Good afternoon, I have prepared a simple test case for my question - CREATE TABLE users ( uid SERIAL PRIMARY KEY, name varchar(255) NOT NULL ); CREATE TABLE reviews ( uid integer NOT NULL CHECK (uid <> author) REFERENCES u

Re: [GENERAL] My humble tribute to psql -- usql v0.5.0

On 04/02/2017 07:55 AM, Kenneth Shaw wrote: Hi All, usql is built in Go, and as of today supports all the major databases (PostgreSQL, MySQL, SQLite3, Microsoft SQL Server, Oracle Database) and more! Additionally, with v0.5.0, released today, usql now has implemented most of the basic, and much

Re: [GENERAL] What is the purpose of PostGIS on PostgreSQL?

On 01/23/2017 11:08 AM, Kased, Razy (Chemistry and Geosciences) wrote: I recently came across this question: "What is the purpose of PostGIS on PostgreSQL?" and wanted to know what this mailing list had to respond with. ​Thanks, PostGIS is to

Re: [GENERAL] Queries on very big table

On 01/02/2017 05:23 AM, Job wrote: Hello guys and very good new year to everybody! We are now approaching some queries and statistics on very big table (about 180 millions of record). The table is partitioned by day (about ~3 Gb of data for every partition/day). We use Postgresql 9.6.1 I am ex

Re: [GENERAL] Abscence of synonym

On 11/7/2016 1:39 PM, Rakesh Kumar wrote: I need some ideas here. Let us say we have a bunch of tables, called a,b,c,d. We will get a batch of data in files which need to be processed. At the end of processing, the tables will get a fresh set of data. The problem we would like to solve is to

Re: [GENERAL] tablesample performance

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

[GENERAL] tablesample performance

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

Re: [GENERAL] PG vs ElasticSearch for Logs

On 8/22/2016 2:39 AM, Thomas Güttler wrote: Am 19.08.2016 um 19:59 schrieb Andy Colson: On 8/19/2016 2:32 AM, Thomas Güttler wrote: I want to store logs in a simple table. Here my columns: Primary-key (auto generated) timestamp host service-on-host loglevel msg json (optional

Re: [GENERAL] PG vs ElasticSearch for Logs

On 8/19/2016 2:32 AM, Thomas Güttler wrote: I want to store logs in a simple table. Here my columns: Primary-key (auto generated) timestamp host service-on-host loglevel msg json (optional) I am unsure which DB to choose: Postgres, ElasticSearch or ...? We don't have high traffi

Re: [GENERAL] pg_logical_slot_get_changes

After testing this more, maybe it does work ok just calling pg_logical_slot_get_changes(). I'm making the assumption that you'd like pg_replication_slots.restart_lsn to be close to pg_current_xlog_location(), correct? The further apart they are, the more pg_xlog you have to store, yes? When

Re: [GENERAL] pglogical cross subscribe

On 8/3/2016 8:27 PM, Andy Colson wrote: Hi all. I have a localdb and a remotedb. A) I'd like to send table1 and table2 from localdb to remotedb. B) I'd like to send table3 from remotedb to localdb. I have PG9.5 and pglogical setup, and (A) works fine. Well, worked. I then tried to

[GENERAL] pglogical cross subscribe

Hi all. I have a localdb and a remotedb. A) I'd like to send table1 and table2 from localdb to remotedb. B) I'd like to send table3 from remotedb to localdb. I have PG9.5 and pglogical setup, and (A) works fine. Well, worked. I then tried to set up (B) which seems to work, but now (A) is bro

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 11:42 AM, Andy Colson wrote: Now that I think about this more, I think you're on to something. I'm trying to get an _exact_ copy of the master db onto the slave. Checking rsync man, it matches only on size and modified time, and I didn't include deletes. I'm

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 11:04 AM, Adrian Klaver wrote: On 07/03/2016 08:49 AM, Andy Colson wrote: On 07/03/2016 10:35 AM, Adrian Klaver wrote: On 07/03/2016 08:06 AM, Andy Colson wrote: Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On 07/03/2016 10:35 AM, Adrian Klaver wrote: On 07/03/2016 08:06 AM, Andy Colson wrote: Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112 Gig, so pg_basebackup is my last resort. I followed the page here: https

Re: [GENERAL] 9.3 to 9.5 upgrade problems

On Sun, Jul 3, 2016 at 11:06 AM, Andy Colson mailto:a...@squeakycode.net>> wrote: Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112 Gig, so pg_basebackup is my last resort. I followed the page here:

[GENERAL] 9.3 to 9.5 upgrade problems

Hi all, I have a master (web1) and two slaves (web2, webserv), one slave is quite far from the master, the db is 112 Gig, so pg_basebackup is my last resort. I followed the page here: https://www.postgresql.org/docs/9.5/static/pgupgrade.html including the rsync stuff. I practiced it _twice_,

Re: [GENERAL] OT hardware recommend

On 06/17/2016 04:39 PM, John R Pierce wrote: On 6/17/2016 2:33 PM, John W Higgins wrote: http://www.ebay.com/itm/2U-24-bay-2-5-Supermicro-Server-X8DTH-iF-2x-Xeon-Quad-Core-32GB-RAM-SAS2-216EL1-/222132081393?hash=item33b81a92f1:g:UzYAAOSwR5dXSQVw With it being 2U you can then pop out the motherb

Re: [GENERAL] OT hardware recommend

On 06/17/2016 09:23 PM, Scott Marlowe wrote: On Fri, Jun 17, 2016 at 2:36 PM, Andy Colson mailto:a...@squeakycode.net>> wrote: Hi all. I have access to quite a few laptop HD's (10 to 15 of them at least), and thought that might make a neat test box that might have some go

Re: [GENERAL] OT hardware recommend

On 06/17/2016 04:33 PM, Gavin Flower wrote: On 18/06/16 08:36, Andy Colson wrote: Hi all. I have access to quite a few laptop HD's (10 to 15 of them at least), and thought that might make a neat test box that might have some good IO speed. Needs to be cheap though, so linux with sof

[GENERAL] OT hardware recommend

Hi all. I have access to quite a few laptop HD's (10 to 15 of them at least), and thought that might make a neat test box that might have some good IO speed. Needs to be cheap though, so linux with software raid, rack mount preferred but not required. Anyone have any experience with anythi

Re: [GENERAL] Using a VIEW as a temporary mechanism for renaming a table

On 6/8/2016 12:57 PM, Ben Buckman wrote: Hello, I would like to rename a table with ~35k rows (on pgsql 9.4), let's say from `oldthings` to `newthings`. Our application is actively reading from and writing to this table, and the code will break if the table name suddenly changes at runtime. So I

Re: [GENERAL] Using a VIEW as a temporary mechanism for renaming a table

On 6/8/2016 2:57 PM, Ben Buckman wrote: Thanks Andy. My understanding, and please correct me if I'm wrong, is that the view will effectively inherit the table's constraints, because writes to the view that can't be written to the table will fail on the table. Re: "will the data be good data," wh

Re: [GENERAL] Using a VIEW as a temporary mechanism for renaming a table

On 6/8/2016 12:57 PM, Ben Buckman wrote: Hello, I would like to rename a table with ~35k rows (on pgsql 9.4), let's say from `oldthings` to `newthings`. Our application is actively reading from and writing to this table, and the code will break if the table name suddenly changes at runtime. So I

Re: [GENERAL] Best approach for multi-database system

please don't top post. > Em 10/03/2016 16:56, Andy Colson escreveu: On 3/10/2016 1:41 PM, Edson Richter wrote: Hi! Using async replication between geographically database servers. I've some 9.3 and some 9.4 servers, so doesn't matter the version (the only reason I've

Re: [GENERAL] Best approach for multi-database system

On 3/10/2016 1:41 PM, Edson Richter wrote: Hi! Using async replication between geographically database servers. I've some 9.3 and some 9.4 servers, so doesn't matter the version (the only reason I've not migrated all to 9.4 yet is because I'm short on time). I've experienced some synchronizatio

Re: [GENERAL] Test CMake build

On 02/13/2016 04:33 AM, Yury Zhuravlev wrote: Andy Colson wrote: cmake and make -j2 fine, but then You can try again I removed some features from CMake 3.x . Realy big thanks for testing! My pleasure. Still didn't work. 'make check' seems to make it through make inst

Re: [GENERAL] Test CMake build

On 2/12/2016 9:47 AM, Yury Zhuravlev wrote: Andy Colson wrote: Is the installcheck important to you? Hello! You can try new make check. Also "make install" started support DESTDIR. cmake and make -j2 fine, but then andy@mapper:~/projects/postgres_cmake/build$ make chec

Re: [GENERAL] Test CMake build

On 2/10/2016 12:09 PM, Yury Zhuravlev wrote: Hello all. Please test build Postgres using cmake. If you are of course interested. Still not everything is ready but most of the work. Assembly instructions as does the repository is on github: https://github.com/stalkerg/postgres_cmake The compilati

Re: [GENERAL] Test CMake build

On 2/11/2016 10:44 AM, Andy Colson wrote: On 2/11/2016 9:49 AM, Yury Zhuravlev wrote: Yury Zhuravlev wrote: I will try to fix soon. I write as a corrected. You can try again. Thanks! That seems better: -- Found Readline: /usr/include -- Found Curses: /usr/lib64/libcurses.so Is this bad

Re: [GENERAL] Test CMake build

On 2/11/2016 9:49 AM, Yury Zhuravlev wrote: Yury Zhuravlev wrote: I will try to fix soon. I write as a corrected. You can try again. Thanks! That seems better: -- Found Readline: /usr/include -- Found Curses: /usr/lib64/libcurses.so Is this bad? -- Check size of int64 -- Check size of int

Re: [GENERAL] Test CMake build

On 2/10/2016 2:50 PM, Andy Colson wrote: On 2/10/2016 2:45 PM, Andy Colson wrote: On 2/10/2016 12:09 PM, Yury Zhuravlev wrote: Hello all. Please test build Postgres using cmake. If you are of course interested. Still not everything is ready but most of the work. Assembly instructions as does

Re: [GENERAL] Test CMake build

On 2/10/2016 2:45 PM, Andy Colson wrote: On 2/10/2016 12:09 PM, Yury Zhuravlev wrote: Hello all. Please test build Postgres using cmake. If you are of course interested. Still not everything is ready but most of the work. Assembly instructions as does the repository is on github: https

Re: [GENERAL] Test CMake build

On 2/10/2016 12:09 PM, Yury Zhuravlev wrote: Hello all. Please test build Postgres using cmake. If you are of course interested. Still not everything is ready but most of the work. Assembly instructions as does the repository is on github: https://github.com/stalkerg/postgres_cmake The compilati

Re: [GENERAL] PostgreSQL vs Firebird SQL

On 2/9/2016 10:10 PM, ioan ghip wrote: I have a Firebird SQL database running on one of my servers which has about 50k inserts, about 100k updates and about 30k deletes every day. There are about 4 million records in 24 tables. I have a bunch of stored procedures, triggers, events and views that

Re: [GENERAL] long transfer time for binary data

Am 21.01.2016 um 03:33 schrieb Andy Colson: On 01/20/2016 03:29 PM, Johannes wrote: I noticed transferring a large object or bytea data between client and server takes a long time. For example: An image with a real size of 11 MB could be read on server side (explain analyze) in 81ms. Fine. But

Re: [GENERAL] long transfer time for binary data

On 01/20/2016 03:29 PM, Johannes wrote: I noticed transferring a large object or bytea data between client and server takes a long time. For example: An image with a real size of 11 MB could be read on server side (explain analyze) in 81ms. Fine. But on client side the result was completed after

Re: [GENERAL] regexp_replace

On 1/14/2016 2:06 PM, David G. Johnston wrote: select regexp_replace('71.09.6.01.3', '(\d)[.-](?=\d)', '\1\2', 'g'); Thanks David! -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-genera

Re: [GENERAL] regexp_replace

On 1/14/2016 2:02 PM, John McKown wrote: How about: select regexp_replace('71.09.6.01.3', '(\d)[.-]', '\1', 'g'); match is 1.3 and result is 13 ( 71096.013). If you don't want to eliminate the period or dash unless it is _between_ two digits, try: select regexp_replace('71.09.6.01.3', '(\d)[.-

Re: [GENERAL] regexp_replace

On 1/14/2016 1:59 PM, Tom Lane wrote: Andy Colson writes: This is not doing as I'd expected: select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g'); regexp_replace 71096.013 (1 row) I think regexp_replace

[GENERAL] regexp_replace

Hi all. This is not doing as I'd expected: select regexp_replace('71.09.6.01.3', '(\d)[.-](\d)', '\1\2', 'g'); regexp_replace 71096.013 (1 row) It acts the same with dashes: select regexp_replace('71-09-6-01-3', '(\d)[.-](\d)', '\1\2', 'g'); regexp_replace

Re: [GENERAL] Question -- Session Operations - Feasibility Of Proposed Synchronization Method?

On 01/07/2016 06:30 PM, Steve Petrie, P.Eng. wrote: Thanks to forum members for the four helpful replies, to my earlier message that initiated this thread. The replies expressed concerns, with the feasibility of my proposal to use postgres tables to store short-lived context data, for dialog c

Re: [GENERAL] A unique pairs version of UNNEST() ?

On 1/4/2016 2:08 PM, Wells Oliver wrote: Hey all, happy new year. I am trying to get unique pairs from an array of N numbered items, usually 5, but possibly 4 or 6. If I just wanted unique values, I could do SELECT UNNEST(list_of_ids) AS id, COUNT(*) FROM table GROUP BY id but in this situation

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 2:39 PM, Andy Colson wrote: On 12/30/2015 2:33 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 2:18 PM, Tom Lane wrote: Maybe something weird about the build you're using? What does pg_config print? [ output ] No smoking gun there either. It might be worthwhi

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 2:33 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 2:18 PM, Tom Lane wrote: Maybe something weird about the build you're using? What does pg_config print? [ output ] No smoking gun there either. It might be worthwhile to update to 9.3.10, just in case the

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 2:18 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 1:55 PM, Tom Lane wrote: Are you using any nondefault planner settings? Anything else unusual about your installation? There are others, but I'll bet its: random_page_cost = 1 Nope... Maybe something weird

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 2:03 PM, Andy Colson wrote: On 12/30/2015 1:55 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 1:07 PM, Tom Lane wrote: it seems like you've got some weird data statistics that are causing a misestimate. Could we see the pg_stats row for that tsvector column? Or

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 1:55 PM, Tom Lane wrote: Andy Colson writes: On 12/30/2015 1:07 PM, Tom Lane wrote: it seems like you've got some weird data statistics that are causing a misestimate. Could we see the pg_stats row for that tsvector column? Or maybe even the actual data? The table exis

Re: [GENERAL] cannot get stable function to use index

Wow thats bad. Here's another link: http://camavision.com/dn/stats.txt -Andy -- 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] cannot get stable function to use index

On 12/30/2015 1:07 PM, Tom Lane wrote: I wrote: This says there's only about a 25% runtime penalty for the partial match, at least on your example, compared to the planner's estimate of 2700x penalty :-(. Definitely need to fix that. I tried to reproduce this behavior with simple generated da

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 10:09 AM, Tom Lane wrote: Andy Colson writes: -> Bitmap Index Scan on search_key (cost=0.00..6.00 rows=1 width=0) (actual time=0.025..0.025 rows=0 loops=1) Index Cond: (search_vec @@ to_tsquery('213 & E & 13 & ST & N'::text))

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 9:55 AM, Andy Colson wrote: On 12/30/2015 9:53 AM, Tom Lane wrote: Andy Colson writes: Here are my results, if there are any others you'd like to see please let me know. Thanks Tom. For comparison, could we see the results for the non-partial case, ie explain analyze s

Re: [GENERAL] cannot get stable function to use index

On 12/30/2015 9:53 AM, Tom Lane wrote: Andy Colson writes: Here are my results, if there are any others you'd like to see please let me know. Thanks Tom. For comparison, could we see the results for the non-partial case, ie explain analyze select * from search where searc

Re: [GENERAL] cannot get stable function to use index

On 12/29/2015 6:35 PM, Tom Lane wrote: Andy Colson writes: I cannot get this sql to use the index: explain analyze select * from search where search_vec @@ to_tsquery_partial('213 E 13 ST N') -- Seq Scan

Re: [GENERAL] cannot get stable function to use index

On 12/29/2015 6:03 PM, Jim Nasby wrote: If I'm reading EXPLAIN ANALYZE correctly, to_tsquery_partial is being simplified out of the query entirely: Filter: (search_vec @@ to_tsquery((array_to_string('{213,E,13,ST,N}'::text[], ' & '::text) || ':*'::text))) Part of this could well be that yo

[GENERAL] cannot get stable function to use index

Hi all, I seem to be missing something. I'm using PG 9.3.9 on Slackware64. My table: create table search ( gid integer, descr text, search_vec tsvector ); create index search_key on search using gin(search_vec); I've put a bunch of data in it, and using to_tsquery use

Re: [GENERAL] Session Identifiers

On 12/20/2015 09:16 AM, oleg yusim wrote: Greetings! I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my questions: From the security standpoint we have to assure that database invalidates session identifiers upon user logout

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

On 11/23/2015 4:41 AM, Chris Withers wrote: Hi All, I wondered if any of you could recommend best practices for using a postgres table as a queue. Roughly speaking, 100-200 workers will vomit rows and rates of a few hundres per second into the table leaving the status as new and then as many wor

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 05:10 PM, David Blomstrom wrote: Just so I understand what's going on, I can create a lookup table by pasting this code... I don't know anything about biology so this data might be laughable, but its based on your original question: http://stackoverflow.com/questions/33402831/

Re: [GENERAL] Selectively Importing Data

On 10/30/2015 07:24 PM, David Blomstrom wrote: First consider the following table: create table taxon ( taxonid serial, descr text ); As I understand it, "serial" means that column will automatically populate with a numerical key. If I want to fill the field 'descr' with a list of scien

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 4:36 PM, Andy Colson wrote: On 10/30/2015 3:47 PM, David Blomstrom wrote: No, I get the same T_FUNCTION error. Someone commented that the function... create function tax_rank(id integer) returns text as $$ select case id when 1 then 'Cl

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 3:47 PM, David Blomstrom wrote: No, I get the same T_FUNCTION error. Someone commented that the function... create function tax_rank(id integer) returns text as $$ select case id when 1 then 'Classes' when 2 then 'Orders' when 3 th

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/29/2015 7:18 PM, David Blomstrom wrote: Can anyone tell me how to write the query described @ http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query ? The answer's very thorough, but I don't know how to string two queries and a function together like that. This

Re: [GENERAL] 9.5 beta pg_upgrade documentation

On 09/01/2015 08:46 PM, Andy Colson wrote: On 09/01/2015 07:00 PM, Bruce Momjian wrote: On Tue, Sep 1, 2015 at 06:56:11PM -0500, Andy Colson wrote: I also added a mention that rsync, not pg_upgrade, will be run on the standbys. You can see all the results of the patch here: http

Re: [GENERAL] 9.5 beta pg_upgrade documentation

On 09/01/2015 07:00 PM, Bruce Momjian wrote: On Tue, Sep 1, 2015 at 06:56:11PM -0500, Andy Colson wrote: I also added a mention that rsync, not pg_upgrade, will be run on the standbys. You can see all the results of the patch here: http://momjian.us/pgsql_docs/pgupgrade.html Thanks

Re: [GENERAL] 9.5 beta pg_upgrade documentation

On 09/01/2015 04:09 PM, Bruce Momjian wrote: On Sun, Aug 23, 2015 at 09:45:50AM -0500, Andy Colson wrote: I think we should add a step 6.5 (before step 7 Stop both servers) with something like: If you are upgrading both a primary and standby, then we need to make sure the standby is caught

Re: [GENERAL] 9.5 beta pg_upgrade documentation

I think we should add a step 6.5 (before step 7 Stop both servers) with something like: If you are upgrading both a primary and standby, then we need to make sure the standby is caught up. If you are wal shipping then on primary run: select pg_switch_xlog(); shut down primary before you shut do

Re: [GENERAL] PostgreSQL Developer Best Practices

On Sat, Aug 22, 2015 at 1:16 PM, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Melvin Davidson mailto:melvin6...@gmail.com>> writes: >> Best Practice would rather be something along the lines: Avoid coding in a way that triggers "WARNING: nonstandard use of escape in a str

Re: [GENERAL] 9.5 beta pg_upgrade documentation

I finished running pg_upgrade on the primary, so far so good, and now I'm at step 9.5 (Verify). On the primary I see: root@test1:/pub/pg95# /usr/local/pg95/bin/pg_controldata -D /pub/pg95|grep "Latest check" Latest checkpoint location: 1D2/3628 I cannot run pg93 pg_controldata be

[GENERAL] 9.5 beta pg_upgrade documentation

Hi All. I setup two test VM's with my PG93 database to test upgrading to PG95. I have a primary and standby using wal shipping. The database is about 150Gig, and the two servers (the real servers) are far apart. The vm's are both running on my desktop. I would love to use pg_upgrade on both

Re: [GENERAL] Extension to rewrite queries before execution

On 8/13/2015 2:49 PM, Jeff Janes wrote: I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one. The context is running a third-party app which issues queries I have no control over. I'd like to in

Re: [GENERAL] could not create shared memory segment: Invalid argument

On Tue, Jul 14, 2015 at 8:59 AM, Andy Colson mailto:a...@squeakycode.net>> wrote: On 7/13/2015 7:08 PM, Ryan King - NOAA Affiliate wrote: Apologies ahead of time for not knowing which group to send to, but I wanted to see if anyone has encountered and resolve

Re: [GENERAL] Index Only Scan vs Cache

On 7/14/2015 1:19 PM, Marc Mamin wrote: On 7/9/2015 12:41 PM, Tom Lane wrote: Andy Colson writes: My question is: Will PG cache only the index (assuming it can always do an Index Only Scan), or will it cache the table as well? I'm not sure that indexes on tiny tables are useful.

Re: [GENERAL] could not create shared memory segment: Invalid argument

On 7/13/2015 7:08 PM, Ryan King - NOAA Affiliate wrote: Apologies ahead of time for not knowing which group to send to, but I wanted to see if anyone has encountered and resolved this type of error. I'm setting up postgresql 9.2 streaming replication on RH and after copying the master data direct

Re: [GENERAL] How to get total count of queries hitting DB per day or per hour?

On 7/9/2015 1:04 PM, Sheena, Prabhjot wrote: Guys Is there a way to get total count of queries hitting Postgresql DB(verison 9.3) per day or per hour ? I cannot turn on log_statement=all coz that’s too much logging for our log file to handle. Is there another way to do that. Than

Re: [GENERAL] Index Only Scan vs Cache

On 7/9/2015 12:41 PM, Tom Lane wrote: Andy Colson writes: My question is: Will PG cache only the index (assuming it can always do an Index Only Scan), or will it cache the table as well? The table blocks would fall out of cache if they're never touched. regards

Re: [GENERAL] regexp_matches for digit

On 7/9/2015 11:24 AM, Ramesh T wrote: Hi, in oracle regexp_like(entered date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i') for postgres i have regexp_matches ,But i need how to match [:digit:] in postgres when we pass date..? any help \d per: http://www.postgresql.org/docs/de

[GENERAL] Index Only Scan vs Cache

Hi All. I have a website db that is 90% read-only. I have 50 (or so) tiny lookup tables, something like: \d m_zone Column | Type | Modifiers -+-+--- code| integer | not null zone_id | text| descr | text| This one has less than 10 rows, others mig

Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)

-Original Message- From: Andy Colson [mailto:a...@squeakycode.net] Sent: Wednesday, June 24, 2015 10:10 AM To: Gauthier, Dave; Postgres General Subject: Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows) On 6/24/2015 8:44 AM, Gauthier, Dav

Re: [GENERAL] DB access speeds, App(linux)<->PG(linux) vs App(linux) <->MSSql(Windows)

On 6/24/2015 8:44 AM, Gauthier, Dave wrote: Hi: I'm trying to get a 10,000 ft understanding of the difference in DB access speeds for two different scenarios... Scenario 1: Apps are on linux. PG DB is on linux (different server than apps) Scenario 2: Apps are on linux. MSSql DB is on

Re: [GENERAL] Counting the occurences of a substring within a very large text

On 6/24/2015 5:55 AM, Marc Mamin wrote: Hello, I'd like to count the number linebreaks within a string, but I get a memory allocation error when using regexp_matches or regexp_split_to_table. Any idea for an alternative to this problem ? select count(*)-1 from ( select regexp_split_to_tabl

Re: [GENERAL] Reg: BULK COLLECT

On 05/25/2015 07:24 AM, Medhavi Mahansaria wrote: > Hello, > > I am porting my application from Oracle to PostgreSQL. > > We are using BULK COLLECT functionality of oracle. > How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql? > > A

Re: [GENERAL] Reg: BULK COLLECT

On 05/25/2015 07:24 AM, Medhavi Mahansaria wrote: Hello, I am porting my application from Oracle to PostgreSQL. We are using BULK COLLECT functionality of oracle. How can i change the 'BULK COLLECT' fetch of the data from the cursor to make if compatible for pg/plsql? A small example is as be

Re: [GENERAL] Setting up a database for 10000 concurrent users

at 10:08 AM, Andy Colson mailto:a...@squeakycode.net>> wrote: No doubt that would be a problem. Its bad idea. set max_connections to core count * 2, then put pg_pool in front, and set pg_pools max count to 1. -Andy On 05/04/2015 09:22 AM, Melvin Davidson wrote: I s

Re: [GENERAL] Setting up a database for 10000 concurrent users

On 05/04/2015 02:02 AM, sanjeetkamble wrote: Hello, Please let me know how The database server is started with max_connections = 1 ??? I have same issue, but i have a SAN storage where Postgresql is installed. Sanjeet No doubt that would be a problem. Its bad idea. set max_connecti

Re: [GENERAL] COALESCE woes

On 04/24/2015 08:06 AM, Greg Spiegelberg wrote: Hi PG List, I'm missing something or haven't had enough coffee yet. What gives with the COALESCE in the view below? mxl_sqr=# \d users Table "public.users" Column | Type | Modifiers -+-+---

Re: [GENERAL] What constitutes "reproducible" numbers from pgbench?

On 4/23/2015 4:07 AM, holger.friedrich-fa-triva...@it.nrw.de wrote: On Tuesday, April 21, 2015 7:43 PM, Andy Colson wrote: On 4/21/2015 9:21 AM, holger.friedrich-fa-triva...@it.nrw.de wrote: Exactly what constitutes "reproducible" values from pgbench? I keep getting a range betwe

Re: [GENERAL] What constitutes "reproducible" numbers from pgbench?

On 4/21/2015 9:21 AM, holger.friedrich-fa-triva...@it.nrw.de wrote: Hello list, Exactly what constitutes „reproducible“ values from pgbench? I keep getting a range between 340 tps and 440 tps or something like that using the same command line on the same machine. Is that reproducible enough? Th

Re: [GENERAL] Best way to migrate a 200 GB database from PG 2.7 to 3.6

On 4/15/2015 2:03 PM, Filip Lyncker wrote: Dear List, I need to migrate my database from a 2.x to 3.x. Usually Im using pg_basebackup , but this is not possible with different versions. Pg_dump seems to be an option but it is slow like hell and I dont want to stay offline all the time. Is there

Re: [GENERAL] Partial index-based load balancing

On 3/31/2015 3:28 PM, Fabio Ugo Venchiarutti wrote: On 01/04/15 06:12, Andy Colson wrote: On 3/31/2015 1:58 AM, Fabio Ugo Venchiarutti wrote: Greetings I'm working for a startup and our core DB is growing rather fast. Our target scale is large enough that we expect some of our core t

Re: [GENERAL] inputs into query planner costing

On 3/31/2015 10:31 AM, Mike Roest wrote: Hi there, I'm having an issue with query performance between 2 different pgsql environments. Ther first is our current production postgres server with is running 9.3.5 on Centos 5 x64. The second system is Amazon's RDS postgres as a service. On our

Re: [GENERAL] Partial index-based load balancing

On 3/31/2015 1:58 AM, Fabio Ugo Venchiarutti wrote: Greetings I'm working for a startup and our core DB is growing rather fast. Our target scale is large enough that we expect some of our core tables' indexes to grow bigger than the memory on any single node over the next couple years (our cur

Re: [GENERAL] Link Office Word form document with data from PostgreSQL

On 3/30/2015 10:18 AM, John R Pierce wrote: On 3/30/2015 1:20 AM, avpro avpro wrote: Have you ever experienced how to populate fields from a MS Office Word document with a PostgreSQL data available either in a table or view? I haven’t seen anything on the web; only possible with MS products, V

Re: [GENERAL] How does one make the following psql statement sql-injection resilient?

On 3/16/2015 4:45 PM, Andy Colson wrote: On 3/16/2015 4:30 PM, David G. Johnston wrote: psql "$SERVICE" \ --echo-queries \ --set=string_input="${1:-ok_to_return}" \ --set=start="${2:-5}" \ --set=end="${3:-10}" \ <<'SQL&#

Re: [GENERAL] How does one make the following psql statement sql-injection resilient?

On 3/16/2015 4:30 PM, David G. Johnston wrote: psql "$SERVICE" \ --echo-queries \ --set=string_input="${1:-ok_to_return}" \ --set=start="${2:-5}" \ --set=end="${3:-10}" \ <<'SQL' SELECT idx FROM generate_series(1, 20) gs (idx) WHERE 'short-circuit' !

  1   2   3   4   5   >