Re: [GENERAL] idle in transaction, why

> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer <spam_ea...@gmx.net> wrote: > > Rob Sargent schrieb am 06.11.2017 um 23:09: >> Gosh I wish I could learn to proof-read my posts. >> My support crew graciously set >> >> idle_transaction_timeout = 1 >>

Re: [GENERAL] idle in transaction, why

On 11/07/2017 09:09 AM, Scott Marlowe wrote: On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent <robjsarg...@gmail.com> wrote: On Nov 7, 2017, at 12:16 AM, Thomas Kellerer <spam_ea...@gmx.net> wrote: I would figure values in "minutes" to be more realistic depe

Re: [GENERAL] idle in transaction, why

> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer <spam_ea...@gmx.net> wrote: > > Rob Sargent schrieb am 06.11.2017 um 23:09: >> Gosh I wish I could learn to proof-read my posts. >> My support crew graciously set >> >> idle_transaction_timeout = 1 >>

Re: [GENERAL] idle in transaction, why

On 11/06/2017 02:38 PM, Merlin Moncure wrote: On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent <robjsarg...@gmail.com> wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent <robjsarg...@gmail.com> writes: idle_in_transaction_session_timeout | 0 | default | |

Re: [GENERAL] idle in transaction, why

On 11/06/2017 02:38 PM, Merlin Moncure wrote: On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent <robjsarg...@gmail.com> wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent <robjsarg...@gmail.com> writes: idle_in_transaction_session_timeout | 0 | default | |

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:50 PM, Rob Sargent wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent <robjsarg...@gmail.com> writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I think we're barking up the

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent <robjsarg...@gmail.com> writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I think we're barking up the wrong tree anyway: so far as I ca

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:17 PM, Tom Lane wrote: "David G. Johnston" writes: You should probably login as your application user and do "show idle_in_transaction_session_timeout" to see what a clean session has for a value and then figure out from there where that value is

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:09 PM, David G. Johnston wrote: On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent <robjsarg...@gmail.com <mailto:robjsarg...@gmail.com>>wrote: Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postg

[GENERAL] idle in transaction, why

Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4 The postgresql.conf file has #idle_in_transaction_session_timeout = 0# in milliseconds, 0 is disabled my db url has "?prepareThreshold=0" since I bump into "already

Re: [GENERAL] gin index trouble

On 10/30/2017 10:56 AM, Peter Geoghegan wrote: On Mon, Oct 30, 2017 at 9:45 AM, Rob Sargent <robjsarg...@gmail.com> wrote: Peter, you beat me to the punch. I was just about to say "Having read the referenced message I thought I would add that we never delete from

Re: [GENERAL] query not scaling

On 10/31/2017 03:12 AM, Laurenz Albe wrote: Rob Sargent wrote: I think your biggest problem is the join condition on m.basepos between s.startbase and s.endbase That forces a nested loop join, which cannot be performed efficiently. Agree! 800,000 * 4,000 = 3,200,000,000. It's just

Re: [GENERAL] pg_audit to mask literal sql

On 10/30/2017 03:35 PM, John R Pierce wrote: On 10/30/2017 10:55 AM, rakeshkumar464 wrote: Is there a way in pgaudit to mask literal sqls like the below: insert into table (col1,col2) values(1,2) select * from table where col1 = 1 These sqls are typed by our QA folks using pgadmin. pgaudit

Re: [GENERAL] gin index trouble

On 10/30/2017 10:56 AM, Peter Geoghegan wrote: On Mon, Oct 30, 2017 at 9:45 AM, Rob Sargent <robjsarg...@gmail.com> wrote: Peter, you beat me to the punch. I was just about to say "Having read the referenced message I thought I would add that we never delete from

Re: [GENERAL] gin index trouble

On 10/30/2017 10:32 AM, Peter Geoghegan wrote: On Fri, Oct 27, 2017 at 3:15 PM, Rob Sargent <rsarg...@xmission.com> wrote: I’ve hit this same message Caused by: org.postgresql.util.PSQLException: ERROR: right sibling of GIN page is of different type in a couple of contexts and I’m st

Re: [GENERAL] gin index trouble

If you can make a test case that (eventually) hits that, we'd be interested to see it ... Any hint(s) on what might trigger this sort of thing? I could duplicate the upload, but I doubt you want the 800K records, 200M input file even if it did regenerate the problem. Would select * from

[GENERAL] gin index trouble

I’ve hit this same message Caused by: org.postgresql.util.PSQLException: ERROR: right sibling of GIN page is of different type in a couple of contexts and I’m starting to get worried. I’ve rebuilt the index, but will that help? Is there a way to see what the ‘different type’ is? Is it

Re: [GENERAL] query not scaling

On 10/26/2017 09:01 AM, Tom Lane wrote: Laurenz Albe writes: Also, to have PostgreSQL inline the function, which would be good for performance, it should be declared IMMUTABLE. Actually, if you hope to have a SQL function be inlined, it's better not to decorate it

Re: [GENERAL] query not scaling

On 10/26/2017 09:01 AM, Tom Lane wrote: Laurenz Albe writes: Also, to have PostgreSQL inline the function, which would be good for performance, it should be declared IMMUTABLE. Actually, if you hope to have a SQL function be inlined, it's better not to decorate it

Re: [GENERAL] query not scaling

> On Oct 26, 2017, at 1:02 AM, Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > Rob Sargent wrote: >> I have a query I cannot tame and I'm wondering if there's an alternative >> to the "between" clause I'm using. Perhaps a custom type could do >&

[GENERAL] query not scaling

I have a query I cannot tame and I'm wondering if there's an alternative to the "between" clause I'm using. Perhaps a custom type could do better? I've tried the "<@" orperator and that changes the query plan significantly but the execution cost/time is not improved. Any suggestion or

Re: [GENERAL] "Shared strings"-style table

On 10/13/2017 09:49 AM, Seamus Abshere wrote: hey, In the spreadsheet world, there is this concept of "shared strings," a simple way of compressing spreadsheets when the data is duplicated in many cells. In my database, I have a table with >200 million rows and >300 columns (all the

Re: [GENERAL] Inserting millions of record in a partitioned Table

On 09/20/2017 02:46 PM, Vick Khera wrote: On Wed, Sep 20, 2017 at 10:10 AM, Job > wrote: We noticed that if we import directly into the global table it is really, really slow. Importing directly in the single partition

Re: [GENERAL] looking for a globally unique row ID

On 09/15/2017 12:45 PM, Adam Brusselback wrote: I cannot image a single postgres index covering more than one physical table. Are you really asking for that? While not available yet, that is a feature that has had discussion before. Global indexes are what i've seen it called in

Re: [GENERAL] looking for a globally unique row ID

Isn't this typically handled with an inheritance (parent-children) setup. MasterDocument has id, subtype and any common columns (create date etc) then dependents use the same id from master to complete the data for a given type. This is really common in ORM tools. Not clear from the

Re: [GENERAL] looking for a globally unique row ID

On 09/14/2017 02:39 PM, Rafal Pietrak wrote: W dniu 14.09.2017 o 19:30, Rob Sargent pisze: On 09/14/2017 11:11 AM, Rafal Pietrak wrote: [--] So I'm stuck with seriously incomplete solution. that's why I have an impression, that I'm going into entirely wrong direction here

Re: [GENERAL] looking for a globally unique row ID

On 09/14/2017 11:11 AM, Rafal Pietrak wrote: Not really. As I said, I'm not looking for performance or "fair probability" of planetary-wide uniqueness. My main objective is the "guarantee". Which I've tried to indicate referring to "future UPDATEs". What I mean here is functionality

Re: [GENERAL] pglogical bidirectional replication of sequences

On 09/01/2017 02:29 AM, Peter J. Holzer wrote: TLDR: Don't. I'm currently conducting tests which should eventually lead to a 2 node cluster with working bidirectional logical replication. (Postgresql 9.6.4-1.pgdg90+1, pglogical 2.0.1-1.jessie+1 on Debian 9 (Stretch)) pglogical supports

Re: [GENERAL] jdbc driver vis Release 10

> On Aug 25, 2017, at 5:55 PM, Dave Cramer <p...@fastcrypt.com> wrote: > > There are newer versions out there! > > Dave Cramer > > da...@postgresintl.com > www.postgresintl.com > >> On 25 August 2017 at 19:53, Rob Sargent <robjsarg...@gmail.com&

Re: [GENERAL] jdbc driver vis Release 10

On 08/25/2017 05:34 PM, Dave Cramer wrote: The JDBC driver release is divorced from the server release. Thanks Dave Cramer da...@postgresintl.com <mailto:da...@postgresintl.com> www.postgresintl.com <http://www.postgresintl.com> On 23 August 2017 at 19:33, Rob Sarge

Re: [GENERAL] jdbc driver vis Release 10

On 08/23/2017 06:09 PM, David G. Johnston wrote: On Wed, Aug 23, 2017 at 4:33 PM, Rob Sargent <robjsarg...@gmail.com <mailto:robjsarg...@gmail.com>>wrote: I see no mention of a new jdbc driver on the release notes for Beta 1. Does that mean there isn't one? ​Whose r

[GENERAL] jdbc driver vis Release 10

I see no mention of a new jdbc driver on the release notes for Beta 1. Does that mean there isn't one? -- 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] PostgreSQL with PowerBuilder, and Identity keys (serials)

> On Aug 5, 2017, at 3:12 PM, Dan Cooperstock at Software4Nonprofits > wrote: > > I’m on PostgreSQL 9.6, 64-bit Windows. > > That really is the correct name for the sequence, because I’m not using > SERIAL. (I needed everything to match the naming in my

Re: [GENERAL] bidirectional mapping?

On 08/02/2017 01:35 PM, John McKown wrote: On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers >wrote: On Wed, Aug 2, 2017 at 5:44 PM, John McKown > wrote:

Re: [GENERAL] dump to pg

> On May 31, 2017, at 9:27 AM, David G. Johnston > wrote: > > On Wed, May 31, 2017 at 7:43 AM, Nicolas Paris wrote: > Hi, > > I have dumps from oracle and microsoft sql server (no more details). Is it > possible to load them "directly" into

Re: [GENERAL] all serial type was changed to 1

Every row? On 05/01/2017 05:17 PM, Max Wang wrote: Sorry. I mean all tables’ id column were reset to 1. Thanks. *From:*Melvin Davidson [mailto:melvin6...@gmail.com] *Sent:* Tuesday, 2 May 2017 9:14 AM *To:* Max Wang *Cc:* Adrian Klaver ;

[GENERAL] Cannot spot the difference in two queries

I have three very similar functions, two of which are fast and one is slow and I cannot explain the slowness of the third. All of which, with the correct arguments should return the same "optimal set". I present one of the two fast versions and the slow one, in whole and in part.I'm using

Re: [GENERAL] referential integrity between elements of an array and another table?

On 04/19/2017 01:13 PM, Henry M wrote: I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. -

Re: [GENERAL] referential integrity between elements of an array and another table?

On 04/19/2017 01:13 PM, Henry M wrote: I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. -

Re: [GENERAL] Error During PostGIS Build From Source on Linux

On 04/12/2017 12:01 PM, Adrian Klaver wrote: On 04/12/2017 10:14 AM, Osahon Oduware wrote: Hi All, I am working on a Linux OS (Centos 6.5). I built GDAL successfully from source as below: ./configure --prefix=/path/to/gdal make make install Next I built PostgreSQL successfully from source

Re: [GENERAL] Is there a point to having both a normal gist index and an exclude index?

On 04/05/2017 12:04 PM, Bruno Wolff III wrote: On Wed, Apr 05, 2017 at 00:05:31 -0400, Tom Lane wrote: Bruno Wolff III writes: ... I create both a normal gist index and an exclude index using the following: CREATE INDEX contains ON iplocation USING gist

Re: [GENERAL] Equivalent function not found for ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE().

On 03/27/2017 04:59 AM, MAJUMDER, SAYAN wrote: Hi, I am new to postgresql and presently we are migrating from sql server to postgresql. We have certain functions in sql server such as ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE(). I am unable to find any equivalent functions in

Re: [GENERAL] import CSV file to a table

On 03/08/2017 09:52 AM, Karl Czajkowski wrote: On Mar 08, Rob Sargent modulated: Yes Karl, I agree. I admitted as much. But if it's clean, as in free of quoted commas, life is much more simple. I've lost site of whether or not the OP knows his situation w.r.t. to this. The awk line

Re: [GENERAL] Unable to start postgresql

Meant to ask before, can you show the command you are using to connect? My memory says OP didn't use --host, which often leads to trying the socket. Do we know that's enabled in pg_hba? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] import CSV file to a table

the values. Karl On Mar 08, Rob Sargent modulated: Since bash has been bandied about in this thread I presume awk is available. Here's how I would check just how 'csv'ish the incoming file is. ... Yes Karl, I agree. I admitted as much. But if it's clean, as in free of quoted commas, life

Re: [GENERAL] import CSV file to a table

Since bash has been bandied about in this thread I presume awk is available. Here's how I would check just how 'csv'ish the incoming file is. awk -F"," '{a[$NF]++}END{for(i in a){printf "%d lines have %d fields(columns)\n", a[i], i}}' csvfilename If this doesn't produce one line you

Re: [GENERAL] pg_xlog keeps growing

> On Feb 27, 2017, at 8:33 PM, dhanuj hippie wrote: > > Hi, > > I have a postgres cluster running in hot_standby. I see the pg_xlog is > growing over time (may files of size 16 MB each). The replication lag is very > less ~2kB, and never goes into a bad state. > I'm

Re: [GENERAL] Why is table not found?

You must quite as "Raum" > On Jan 31, 2017, at 6:45 AM, Egon Frerich wrote: > > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I created a db 'Hausrat' with a table "Raum". If I look in pgadmin I > find the the table. In sql field is shown: > >> -- Table: "Raum" >>

Re: [GENERAL] update error with serializable

On 01/20/2017 10:05 AM, Kevin Grittner wrote: On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo wrote: I've two threads countinuously updataing rows in the same table. Each one does: BEGIN, UPDATE,UPDATECOMMIT There can't be two active transactions updating the same

Re: [GENERAL] Materialized view vs. view

> > > "A rose by any other name would still smell as sweet”. Actually there’s no “still” in that line, if you’re quoting Shakespeare. And the full “That which we call a rose …” is truly appropriate here. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] COPY: row is too big

On 01/05/2017 11:46 AM, Adrian Klaver wrote: On 01/05/2017 08:31 AM, Rob Sargent wrote: On 01/05/2017 05:44 AM, vod vos wrote: I finally figured it out as follows: 1. modified the corresponding data type of the columns to the csv file 2. if null values existed, defined the data type

Re: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

On 01/05/2017 10:18 AM, Job wrote: Hello guys, a very strange thing: after upgrading from 8.4.22 to 9.6.1 i noticed, under heavy beanchmarks, a really slowdown of Postgresql 9.6.1, with the machine really "without breath". By replacing Postgresql 8.4.22 evberything returns working fine.

Re: [GENERAL] COPY: row is too big

On 01/05/2017 05:44 AM, vod vos wrote: I finally figured it out as follows: 1. modified the corresponding data type of the columns to the csv file 2. if null values existed, defined the data type to varchar. The null values cause problem too. so 1100 culumns work well now. This problem

Re: [GENERAL] Index impact on update?

On 01/04/2017 09:59 AM, Israel Brewster wrote: Short version: Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing? Details: I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and

Re: [GENERAL] Index impact on update?

On 01/04/2017 09:59 AM, Israel Brewster wrote: Short version: Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing? Details: I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and

Re: [GENERAL] COPY: row is too big

> On Jan 2, 2017, at 10:13 AM, Adrian Klaver wrote: > >> On 01/02/2017 09:03 AM, vod vos wrote: >> You know, the csv file was exported from other database of a machine, so >> I really dont want to break it for it is a hard work. Every csv file >> contains headers and

Re: [GENERAL] Performance PLV8 vs PLPGSQL

I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while. > On Dec 29, 2016, at 5:32 PM, Tim Uckun wrote: > > Honestly I don't even like JS. Having said

Re: [GENERAL] About the MONEY type

> On Dec 18, 2016, at 5:23 PM, Gavin Flower > wrote: > > On 18/12/16 12:25, Bruce Momjian wrote: >> On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote: >>> note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the timezone... >>> rather, it

Re: [GENERAL] huge table occupation after updates

> On Dec 10, 2016, at 10:01 AM, Tom DalPozzo <t.dalpo...@gmail.com> wrote: > > 2016-12-10 16:36 GMT+01:00 Rob Sargent <robjsarg...@gmail.com>: > > > On Dec 10, 2016, at 7:27 AM, Tom DalPozzo <t.dalpo...@gmail.com> wrote: > > > > Hi, > > I'

Re: [GENERAL] huge table occupation after updates

> On Dec 10, 2016, at 7:27 AM, Tom DalPozzo wrote: > > Hi, > I'd like to do that! But my DB must be crash proof! Very high reliability is > a must. > I also use sycn replication. > Regards > Pupillo > > > > > Are each of the updates visible to a user or read/analyzed

Re: [GENERAL] huge table occupation after updates

> On Dec 10, 2016, at 6:25 AM, Tom DalPozzo wrote: > > Hi, > you're right, VACUUM FULL recovered the space, completely. > So, at this point I'm worried about my needs. > I cannot issue vacuum full as I read it locks the table. > In my DB, I (would) need to have a table

Re: [GENERAL] high transaction rate

On 12/07/2016 03:32 PM, John R Pierce wrote: On 12/7/2016 2:23 PM, Rob Sargent wrote: How does your reply change, if at all, if: - Fields not index - 5000 hot records per 100K records (millions of records total) - A dozen machines writing 1 update per 10 seconds (one machine writing every

Re: [GENERAL] high transaction rate

On 12/07/2016 09:58 AM, John R Pierce wrote: On 12/7/2016 8:47 AM, Rob Sargent wrote: Please tell me that in this case, updating 2 (big)integer columns does not generate dead tuples (i.e. does not involve a insert/delete pair). if the fields being updated aren't indexed, and there's free

Re: [GENERAL] FreeBSD 10 => 11: Dump and reload your PostgreSQL database unless you like it broken

On 12/07/2016 02:06 PM, Kevin Grittner wrote: On Wed, Dec 7, 2016 at 7:33 AM, Michael Sheaver wrote: I would like to echo the sentiment on collation and expand it to character sets in general. When issues with them come up, they do take an incredible amount of time and

Re: [GENERAL] high transaction rate

2. Accumulation of dead tuples leading to what should be very short operations taking longer. No idea of that is helpful but where I would probably start Please tell me that in this case, updating 2 (big)integer columns does not generate dead tuples (i.e. does not involve a insert/delete

Re: [GENERAL] Query help

> On Oct 4, 2016, at 9:31 PM, Bret Stern > wrote: > > Good evening, > I'm curious about a way to ask the following question of my vendors > table. > > psuedo1 "select all vendors which exist in BUR and EBC and SNJ" > > and > psuedo2 "select all vendors which

Re: [GENERAL] Incrementally refreshed materialized view

Of course 9.5 is the current release so the answer is Yes, since 9.5 On 09/26/2016 12:29 PM, Rakesh Kumar wrote: *Does PG support INCREMENTAL MV ? Looks like not (until 9.5)*

Re: [GENERAL] Chante domain type - Postgres 9.2

On 09/26/2016 08:14 AM, Adrian Klaver wrote: On 09/26/2016 06:54 AM, Thomas Kellerer wrote: Rakesh Kumar schrieb am 26.09.2016 um 15:08: You sound like you think that varchar(50) is somehow cheaper than text. The biggest impediment to text cols in other RDBMS is no index allowed. If PG

Re: [GENERAL] Request to share information regarding postgresql pg_xlog file.

> On Sep 15, 2016, at 1:20 AM, Yogesh Sharma > wrote: > > Dear John and all, > > >8.1 has been obsolete and unsupported for about 6 years now.8.1.18 was > >released in 2009, the final 8.1.23 release was in 2010, after which it was > >>dropped. > Yes,

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

On 08/23/2016 08:34 AM, Francisco Olarte wrote: On Tue, Aug 23, 2016 at 4:28 PM, Rob Sargent <robjsarg...@gmail.com> wrote: On 08/23/2016 07:44 AM, Francisco Olarte wrote: On Tue, Aug 23, 2016 at 2:26 PM, pinker <pin...@onet.eu> wrote: I am just surprised by the order

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

On 08/23/2016 07:44 AM, Francisco Olarte wrote: Hi pinker: On Tue, Aug 23, 2016 at 2:26 PM, pinker wrote: I am just surprised by the order of magnitude in the difference though. 2 and 27 minutes that's the huge difference... I did another, simplified test, to make sure there

Re: [GENERAL] Detecting if current transaction is modifying the database

On 08/05/2016 02:15 PM, Christian Ohler wrote: On Fri, Aug 5, 2016 at 12:55 PM, Rob Sargent <robjsarg...@gmail.com> wrote: What sort of interface are you looking for. Where/When would you grab the information? Do what with it? Log triggers are the typical pattern here (with package

Re: [GENERAL] Detecting if current transaction is modifying the database

On 08/05/2016 01:48 PM, Christian Ohler wrote: Thanks, fair point. I should have mentioned that I know about triggers but was hoping to find a less invasive mechanism (IIUC, I'd have to install a trigger on every table) – it seems to me that Postgres should just be able to tell me whether

Re: [GENERAL] pasting a lot of commands to psql

If, on the wild chance you're an emacs user, if the section is in a ".sql" buffer Ctrl-C Ctrl-B will send the entire buffer. Not sure if there's a practical limit on the number of line/statements, but I've used this with a couple of "pages" worth of sql. On 07/07/2016 11:48 AM, Dmitry

Re: [GENERAL] pg_dump fundenental question

On 07/05/2016 10:54 AM, David G. Johnston wrote: On Tue, Jul 5, 2016 at 10:54 AM, J. Cassidy >wrote: Hello all, I have hopefully an "easy" question. If I issue the pg_dump command with no switches or options i.e.

Re: [GENERAL] Stored procedure version control

On 07/01/2016 06:17 PM, Jim Nasby wrote: On 6/30/16 9:16 AM, Merlin Moncure wrote: It's not really necessary to create version down scripts. In five years of managing complex database environments we've never had to roll a version back and likely never will; in the event of a disaster it's

Re: [GENERAL] How to pass jsonb and inet arguments to a stored function with JDBC?

> On Jun 14, 2016, at 7:33 AM, Alexander Farber > wrote: > > Dear PostgreSQL users, > > I have a stored procedure defined as: > > CREATE OR REPLACE FUNCTION words_merge_users( > IN in_users jsonb, > IN in_ip inet, > OUT out_uid integer) >

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] 20160417105248.d20dcefed39b5d9031c6b...@potentialtech.com

> On Apr 17, 2016, at 12:41 PM, Sergei Agalakov > wrote: > > I know about DBSteward. I don't like to bring PHP infrastructure only to be > able to compare two dumps, > and to deal with potential bugs in the third party tools. The pg_dump in > other hand is always

Re: [GENERAL] Non-default postgresql.conf values to log

> On Apr 7, 2016, at 8:26 AM, Alex Ignatov wrote: > > > >> On 07.04.2016 16:53, Tom Lane wrote: >> Alex Ignatov writes: >>> My question is: is there any option(s) to log non-default >>> postgresql.conf values to log file? >> No, but you

Re: [GENERAL] Table size for partitioned setup

On 03/28/2016 02:55 PM, Mat Arye wrote: This will run on EC2 (or other cloud service) machines and on ssds. Right now runs on m4.4xlarge with 64GiB of ram. Willing to pay for beefy instances if it means better performance. On Mon, Mar 28, 2016 at 4:49 PM, Rob Sargent <robjsarg...@gmail.

Re: [GENERAL] Table size for partitioned setup

On 03/28/2016 02:41 PM, Mat Arye wrote: Hi All, I am writing a program that needs time-series-based insert mostly workload. I need to make the system scaleable with many thousand of inserts/s. One of the techniques I plan to use is time-based table partitioning and I am trying to figure

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 03:00 PM, Joshua D. Drake wrote: On 03/22/2016 01:50 PM, CS DBA wrote: Understood, was just wondering if there is a way to cause the child table insert results to be returned to the ORM/Application instead of the master/base table insert Insert into the child table directly

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 12:55 PM, Melvin Davidson wrote: Your problem seems strange as it has never been previously reported for anyone else that has _successfully_ set up partioning. Perhaps is you provide just a little bit more detail we might be able to help you. Useful and needed information would

Re: [GENERAL] Let's Do the CoC Right

On 01/22/2016 03:53 PM, Andrew Sullivan wrote: This is why I posted all that stuff about what the IETF does some while ago. There is definitely more than one way to do this. Best regards, A Just a gut feeling, but I think this thread had driven the rest of the regulars to drink at a bar

Re: [GENERAL] Support for BDR in 9.5?

On 01/08/2016 10:39 AM, Andrew Biggs (adb) wrote: Can anyone tell me if PostgreSQL 9.5 supports (either natively or by extension) the BDR functionality? I tried it out and ran into issues, but it could well have been I was doing something wrong. Thanks! Andrew I'm sure those who might

Re: [GENERAL] Convert 2 foreign key values within the same SELECT

On 01/04/2016 12:36 PM, gvim wrote: I have a query which successfully retrieves id values "me" and "you" when 2 planetary values are supplied: SELECT l.me_id AS me, l.you_id AS you, a.l AS left, a.aspect, a.r AS right, l.id AS link_id, c.comment FROM aspects a,

Re: [GENERAL] Generating an XSD file from an existing database

> On Dec 6, 2015, at 2:30 PM, Blake McBride wrote: > > Greetings, > > I need an XML specification for my exiting schema - an XSD file. Reading the > docs I see schema_to_xml but I do not understand: > > A. Will that produce the XSD file I seek? > > B. I do not

Re: [GENERAL] Recursive Arrays 101

On 11/05/2015 11:08 AM, Gavin Flower wrote: On 06/11/15 04:33, Rob Sargent wrote: On 11/05/2015 04:56 AM, Achilleas Mantzios wrote: On 04/11/2015 17:53, Rob Sargent wrote: On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU

Re: [GENERAL] Recursive Arrays 101

On 11/05/2015 04:56 AM, Achilleas Mantzios wrote: On 04/11/2015 17:53, Rob Sargent wrote: On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, what we have been successfully doing

Re: [GENERAL] Recursive Arrays 101

On 11/04/2015 03:03 AM, Achilleas Mantzios wrote: Sorry for being kind of late to the party (I was in 2015.PgConf.EU !!), and not having read most of the replies, what we have been successfully doing for this problem for our app is do it this way : parents int[] -- where parents stores the

Re: [GENERAL] Taking lot time

On 11/03/2015 07:42 AM, Ramesh T wrote: I have a Query it taking a lot of time to fetch results so,explain query gave "Hash Join (cost=55078.00..202405.95 rows=728275 width=418)" " Hash Cond: (itd.tran_id = iad._adj__id)" " -> Seq Scan on inv_detail itd (cost=0.00..40784.18 rows=731029

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:10 PM, David Blomstrom wrote: Just so I understand what's going on, I can create a lookup table by pasting this code... create table taxon ( taxonid serial, descr text ); create table gz_life_mammals ( id serial, taxonid integer, -- use the lookup table parentid

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:10 PM, David Blomstrom wrote: Just so I understand what's going on, I can create a lookup table by pasting this code... create table taxon ( taxonid serial, descr text ); create table gz_life_mammals ( id serial, taxonid integer, -- use the lookup table parentid

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:55 PM, David Blomstrom wrote: The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid.

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:55 PM, David Blomstrom wrote: The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid.

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:38 PM, David Blomstrom wrote: Ah, yes - "Execute SQL." It created the table this time. Awesome. One other question - when I close the SQL window, it asks me if I want to save the file. Is there any special reason for saving it? It looks like it simply saved a copy of the

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 12:29 PM, Eric Schwarzenbach wrote: Thank you! (Slapping head) Your regexp seems to do the trick. On 10/29/2015 01:49 PM, Rob Sargent wrote: On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote: I have created a custom type as a domain based on text, which adds a check constraint

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 04:29 PM, Eric Schwarzenbach wrote: On 10/29/2015 03:44 PM, Alvaro Herrera wrote: segment needing to be filled with zeros to a fixed length.) (Also FWIW, the latest version of this regexp is now '^([0-9]+.)*[0-9]+$') Cheers, Eric So it can start with a dot, but not end

Re: [GENERAL] Domain check constraint not honored?

On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote: I have created a custom type as a domain based on text, which adds a check constraint using a regexp to limit it to containing digits and '.'. However I am finding I can add values with other characters to a column of this type. Is this to be

  1   2   3   4   >