Re: [GENERAL] Automate copy - Postgres 9.2

On 6/8/2016 7:04 PM, Patrick B wrote: FOR crtRow in EXECUTE 'select DISTINCT(account_id) from backup_table WHERE migrated = 1 AND account_id IN '|| $1 where and account_id in 21; ? I don't think that's what you want. -- john r pierce, recycling bits in santa cruz

Re: [GENERAL] Automate copy - Postgres 9.2

POn Wednesday, June 8, 2016, Patrick B wrote: > > ERROR: syntax error at or near "||" >> LINE 12: TO '/var/lib/pgsql/' || crtrow.account_id |... > > Which tells me you cannot use an expression as a file name. The docs support this conclusion. You probably need to

Re: [GENERAL] Automate copy - Postgres 9.2

> On Jun 8, 2016, at 8:04 PM, Patrick B wrote: > > > > 2016-06-09 13:58 GMT+12:00 John R Pierce >: > On 6/8/2016 6:47 PM, Patrick B wrote: > > 21 is the number of IDS that I wanna perform that COPY command > >

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 13:58 GMT+12:00 John R Pierce : > On 6/8/2016 6:47 PM, Patrick B wrote: > >> >> 21 is the number of IDS that I wanna perform that COPY command >> > > that didn't answer my question. if you call your function like SELECT > myfunction(21); as you showed, where

Re: [GENERAL] Automate copy - Postgres 9.2

On 6/8/2016 6:47 PM, Patrick B wrote: 21 is the number of IDS that I wanna perform that COPY command that didn't answer my question. if you call your function like SELECT myfunction(21); as you showed, where are those 21 ID's coming from? -- john r pierce, recycling bits in santa

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 13:15 GMT+12:00 John R Pierce : > On 6/8/2016 5:46 PM, Patrick B wrote: > > Single id as you show, a range of numbers or an array of numbers? > > > select function(21); > > Where 21 = Number of ids > > > how do you get the specific ID's from "21" ? > > > 21 is the

Re: [GENERAL] Can SET_VARSIZE cause a memory leak?

On Wed, Jun 8, 2016 at 9:55 PM, Albe Laurenz wrote: > This is safe, and the memory will be freed at the end of the transaction. Yes, palloc takes memory from the current memory context. The case where you would need to use pfree is if there is an allocation continuously

Re: [GENERAL] Unregistered OpenSSL callbacks access violation

On Thu, Jun 9, 2016 at 4:01 AM, Ranier VF wrote: > Postgresql 9.5 > WARNING: Stack unwind information not available. Following frames may be > wrong. > SSLEAY32!SSL_shutdown+0x4 > LIBPQ!PQpingParams+0x1631 > LIBPQ!PQfinish+0x11 > nginx!dbd_pgsql_close+0x106

Re: [GENERAL] Automate copy - Postgres 9.2

On 6/8/2016 5:46 PM, Patrick B wrote: Single id as you show, a range of numbers or an array of numbers? select function(21); Where 21 = Number of ids how do you get the specific ID's from "21" ? -- john r pierce, recycling bits in santa cruz

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 12:13 GMT+12:00 John R Pierce : > On 6/8/2016 4:24 PM, Patrick B wrote: > > > I need to do a file backup for each account_id. > > Example: > > COPY (SELECT * FROM backup_table WHERE id = 1112 AND status = 1) TO > '/var/lib/pgsql/1112.sql'; > > > COPY generates CSV

Re: [GENERAL] Automate copy - Postgres 9.2

2016-06-09 12:19 GMT+12:00 Adrian Klaver : > On 06/08/2016 04:24 PM, Patrick B wrote: > >> Hi guys, >> >> I need to do a file backup for each account_id. >> >> Example: >> >> |COPY (SELECT*FROMbackup_table WHEREid =1112ANDstatus >> =1)TO'/var/lib/pgsql/1112.sql';COPY

Re: [GENERAL] Automate copy - Postgres 9.2

On 06/08/2016 04:24 PM, Patrick B wrote: Hi guys, I need to do a file backup for each account_id. Example: |COPY (SELECT*FROMbackup_table WHEREid =1112ANDstatus =1)TO'/var/lib/pgsql/1112.sql';COPY (SELECT*FROMbackup_table WHEREid =1113ANDstatus =1)TO'/var/lib/pgsql/1113.sql';COPY

Re: [GENERAL] Automate copy - Postgres 9.2

On 6/8/2016 4:24 PM, Patrick B wrote: I need to do a file backup for each account_id. Example: |COPY (SELECT*FROMbackup_table WHEREid =1112ANDstatus =1)TO'/var/lib/pgsql/1112.sql';| COPY generates CSV and similar formats, not .sql. only pg_dump, the command line utility, outputs .SQL

[GENERAL] Automate copy - Postgres 9.2

Hi guys, I need to do a file backup for each account_id. Example: COPY (SELECT * FROM backup_table WHERE id = 1112 AND status = 1) TO '/var/lib/pgsql/1112.sql'; COPY (SELECT * FROM backup_table WHERE id = 1113 AND status = 1) TO '/var/lib/pgsql/1113.sql'; COPY (SELECT * FROM backup_table

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

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 can't simply run an

Re: [GENERAL] Re-sync slave server

On 06/08/2016 01:47 PM, Patrick B wrote: 2016-06-08 11:03 GMT+12:00 Adrian Klaver >: On 06/07/2016 03:22 PM, Patrick B wrote: One more question guys... Does the pg_basebackup re-write the data? or do I have to

Re: [GENERAL] Re-sync slave server

2016-06-08 11:03 GMT+12:00 Adrian Klaver : > On 06/07/2016 03:22 PM, Patrick B wrote: > >> One more question guys... >> >> Does the pg_basebackup re-write the data? or do I have to have free space? >> >> Like.. the DB is 2 TB.. do I have to have more 2TB? or it will use

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

Oh yeah, the table structure will change after this is all done, but not in the middle of it. The view would only last a few minutes and maintain the exact same schema. Thanks for the tip re: deadlocks, I'll keep that in mind! Ben On Wed, Jun 8, 2016 at 1:24 PM, Andy Colson

Re: [GENERAL] Slow join over three tables

Hi!! what happens if you change your query to: SELECT r.id, r.age, r.gender, r.created, a.adverse, d.drug   FROM reports r   INNER JOIN report_drugs d USING (rid)    INNER JOIN report_adverses a USING (rid)   WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific back pain', 'back

[GENERAL] Unregistered OpenSSL callbacks access violation

Hi, Postgresql 9.5Windows client 32 bitsmsvc 2010 32 bits WARNING: Stack unwind information not available. Following frames may be wrong.SSLEAY32!SSL_shutdown+0x4LIBPQ!PQpingParams+0x1631LIBPQ!PQfinish+0x11nginx!dbd_pgsql_close+0x106 [c:\usr\src\dbd\postgresql\dbd_pgsql.c @

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,"

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

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," what risks should I be considering? In

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] Monitoring and insight into NOTIFY queue

I just ran into monitoring this and came up with the following 1 liner for monitoring this in releases < 9.6 through the SQL layer. Once you are at 9.6 Jeff Janes solution is correct. It does make the assumption that the queue size is 8GB. It can misjudge the queue usage by up to one file

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

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 can't simply run an `ALTER TABLE oldthings

Re: [GENERAL] connection pooling, many users, many datasources

On Thu, 9 Jun 2016, 12:50 a.m. Michael McInness, wrote: > I am working with a system that uses JDBC and JNDI-based connection > pooling. There are currently many organizations that use the system. Each > of the organizations has multiple, individual system users. > >

[GENERAL] connection pooling, many users, many datasources

I am working with a system that uses JDBC and JNDI-based connection pooling. There are currently many organizations that use the system. Each of the organizations has multiple, individual system users. Currently, each entity has its own database and a corresponding application-based datasource

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

* Michael Paquier (michael.paqu...@gmail.com) wrote: > On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost wrote: > > * Vik Fearing (v...@2ndquadrant.fr) wrote: > >> On 03/06/16 04:32, Michael Paquier wrote: > >> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar

Re: [GENERAL] Full text search on Chemistry text

On Wed, Jun 8, 2016 at 8:46 AM, Allan Kamau wrote: > I would like to generate tsvectors on documents that contain chemistry > related text. > Is there a synonym dictionary for chemistry terms available? > > -Allan > ​I did a Google search on "chemistry terms dictionary"

[GENERAL] Full text search on Chemistry text

I would like to generate tsvectors on documents that contain chemistry related text. Is there a synonym dictionary for chemistry terms available? -Allan

Re: [GENERAL] Can SET_VARSIZE cause a memory leak?

Николай Бабаджанян wrote: > I didn't find an easy way to convert ucs-2 bytea to utf-8, so I decided to > write a C-function. Since > ucs-2 is has fixed symbol size of 2 bytes the output bytea size may differ. > > I do the following: > > bytea *result= (bytea *)

[GENERAL] [C-function] Can SET_VARSIZE cause a memory leak?

I didn't find an easy way to convert ucs-2 bytea to utf-8, so I decided to write a C-function. Since ucs-2 is has fixed symbol size of 2 bytes the output bytea size may differ. I do the following: bytea *result= (bytea *) palloc0(VARSIZE(in_bytea)); // allocating memory for the

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

On 08.06.2016 12:16, Greg Navis wrote: Would this be a better plan then: 1. Add support for trigram operators. 2. Implement `issimilar(lhs, rhs, threshold)`. 3. Add `issimilar` to the trigram operator classes. I think Tom's proposal with composite type is exelent option. If I understand

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

Thanks for the replies. On Sat, Jun 4, 2016 at 8:48 PM, Jeff Janes wrote: > On Sat, Jun 4, 2016 at 2:50 AM, Greg Navis wrote: > > Thanks for your replies. > > > > Sorry for confusion. Instead of `similarity(lhs, rhs) >= show_limit()`, > > which of

Re: [GENERAL] High availability and load balancing ...

> Hi, > > I need to do the setup for High availability function. > Also want to implement load balancing for 02 nodes. > I think PGPool will be require for that. Can I use PGPool without cost. Yes, you can. Pgpool-II is an open source product. > Can I get the basic steps to do this setup?

Re: [GENERAL] High availability and load balancing ...

> > > > I need to do the setup for High availability function. > > Also want to implement load balancing for 02 nodes. > You will have to build streaming replication which was introduced in PostgreSQL-9.0 > I think PGPool will be require for that. Can I use PGPool without cost. > pgpool-II is

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

On Mon, Jun 6, 2016 at 9:51 PM, Stephen Frost wrote: > * Vik Fearing (v...@2ndquadrant.fr) wrote: >> On 03/06/16 04:32, Michael Paquier wrote: >> > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar >> > wrote: >> >> On Fri, Jun 3, 2016 at 4:30 AM Stephen