Re: [GENERAL] How To Install Extension Via Script File?

On 08/08/2013 04:57 PM, Don Parris wrote: On Thu, Aug 8, 2013 at 5:45 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: On 08/08/2013 03:13 PM, Don Parris wrote: Hi all, I have a database that uses the ltree extension. I typically create a new database like

Re: [GENERAL] devide and summarize sql result

Yeah, I have written that sort of query too, but with more info on tables and the SQL you are unlikely to get much help Sent from my iPad On Aug 15, 2013, at 2:46 PM, "Janek Sendrowski" wrote: > Hi, > > My sql query results sth. like this: > > user percentage > franz 78% > smith

Re: [GENERAL] regexp idea

On 08/27/2013 12:44 PM, AI Rumman wrote: Hi, I have a string like: Gloucester Catholic vs. St. Augustine baseball, South Jersey Non-Public A final, June 5, 2013 I need to extract date part from the string. I used the follows: regexp_matches(title,'[.* ]+\ (Jul|August|Sep)[, a-zA-Z0-9]+' ) B

Re: [GENERAL] regexp idea

On 08/27/2013 02:04 PM, AI Rumman wrote: Thanks. That's awesome. Do you have any good guide where I may get more knowledge on REGEXP? On Tue, Aug 27, 2013 at 3:57 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: On 08/27/2013 12:44 PM, AI Rumman wrote: Hi,

Re: [GENERAL] question about age()

On 08/29/2013 01:11 PM, Andreas Kretschmer wrote: is there a bug in age()? test=*# select *, age(birthday), age (current_date-1, birthday) from birthday ; id | birthday | age | age ++-+- 1 | 2010-08-29 | 3 years | 2 years 11 mo

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 manually resetting this on

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 have

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 qu

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 subscrip

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

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 postgres

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 (network inet_ops); ALTER TABLE iplo

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 as

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

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

[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 postg

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 ; pgsql-general@postgresql.org *Subject:* Re: [GENERA

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 postgres (without oracle/mssql > license)? > dump

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 mailto:chris.trav...@gmail.com>>wrote: On Wed, Aug 2, 2017 at 5:44 PM, John McKown mailto:john.archie.mck...@gmail.com>> wrote: Is there a simple way to do bidirectional mapping of a t

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 existing DB I’m using > for the app,

[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] 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 <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 release notes? Postgre

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

Re: [GENERAL] jdbc driver vis Release 10

> On Aug 25, 2017, at 5:55 PM, Dave Cramer wrote: > > There are newer versions out there! > > Dave Cramer > > da...@postgresintl.com > www.postgresintl.com > >> On 25 August 2017 at 19:53, Rob Sargent wrote: >> >> >>> On 08/25/2017 05

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 repli

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 similar

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

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 descripti

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 th

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 is faster. Do you have a rul

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 household

[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 p

Re: [GENERAL] query not scaling

> On Oct 26, 2017, at 1:02 AM, Laurenz Albe 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 >> better?

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 at all --- not with IMMUTAB

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 at all --- not with IMMUTABL

[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 caught/c

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

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 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 starting to get worried. I’ve

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 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 this table." In this particular c

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 r

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] gin index trouble

On 10/30/2017 10:56 AM, Peter Geoghegan wrote: On Mon, Oct 30, 2017 at 9:45 AM, Rob Sargent 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 this table." In this particular c

[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 defin

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 <mailto:robjsarg...@gmail.com>>wrote: Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4 The postgresql.conf

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 coming from. You don't have t

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent 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 can find, there is no error message re

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

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 wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I

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 wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I

Re: [GENERAL] idle in transaction, why

> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer 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 >> >> Now to ponder

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 wrote: On Nov 7, 2017, at 12:16 AM, Thomas Kellerer wrote: I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. A t

Re: [GENERAL] idle in transaction, why

> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer 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 >> >> Now to ponder

Re: [SQL] [GENERAL] Data audit trail techniques in postgresql

tablelog doesn't appear any more lively than the OPs audittrail2. Alvaro Herrera wrote: Nathaniel Smith wrote: What do others use to accomplish this? Do most pg users just write triggers by hand? Or is there some nice auditing module that Google just isn't revealing to me? I think ta

Re: [SQL] [GENERAL] Data audit trail techniques in postgresql

Alvaro Herrera wrote: Rob Sargent escribió: tablelog doesn't appear any more lively than the OPs audittrail2. Perhaps, but I have heard of people using it successfully recently, whereas Nathaniel reported that audittrail2 seems to have obvious bugs. Fair enough. -- Sen

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

On 11/04/2013 10:06 AM, Jeff Amiel wrote: PostgreSQL 9.2.4 on x86_64-pc-solaris2.10, compiled by gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath), 64-bit Have got an annoying scenario that has been creating issues for us for years…. Time to try to figure it out. Essentially, we have a user tabl

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

On 11/04/2013 11:38 AM, Jeff Amiel wrote: to: Rob Sargent The login references have nothing to do with postgres - is simply table/column names being used. I'm sorry, I thought user_profile.login_attempts was being set to zero during login (or perhaps after successfulloginin a two

Re: [GENERAL] table lock when where clause uses unique constraing instead of primary key.

On 11/04/2013 01:56 PM, Adrian Klaver wrote: On 11/04/2013 12:44 PM, Jeff Amiel wrote: On Monday, November 4, 2013 2:25 PM, Adrian Klaver wrote: I grepped the schema (just to be sure) - no foreign keys on columns or table at all. I do have an audit trigger on the table for updates -

Re: [GENERAL] How to change content of xml datatype

On 11/20/2013 10:21 AM, gajendra s v wrote: Hi All, I need to change content of xml ,Please any one suggest me how to do,the xml is below FailValue="0"/>2534Value="1"/>99432Value="2"/>43232Value="3"/>43323Value="4"/>33432Value="5"/>453432 I want xml like below,is there any way '*Pass*Va

Re: [GENERAL] compliance with 21 cfr part 11

On 12/04/2013 04:41 PM, John R Pierce wrote: On 12/4/2013 3:34 PM, e-letter wrote: Can postgresql be deployed with audit trail and data validation capability, to meet compliance with 21 cfr part 11? IMHO, that sort of audit trail should be higher level in your business logic, not in the datab

Re: [GENERAL] Foreign keys

On 12/18/2013 11:02 AM, Dean Gibson (DB Administrator) wrote: I have general question about FOREIGN KEYs: 1. Suppose I have table A with primary key X, and another table B with field Y. 2. When I 'ALTER TABLE "B" ADD FOREIGN KEY( "Y" ) REFERENCES "A" ON UPDATE CASCADE ON DELETE CAS

Re: [GENERAL] authentication failure

On 01/06/2014 02:42 PM, Tom Lane wrote: Jayadevan M writes: I am able to login as postgres with password from the same machine. So it is not an expiry issue (as you too concluded). Output from strace is about 500 lines. I am pasting what I feel may be relevant. I hope this will be useful. Wel

Re: [GENERAL] problem with grant all privileges

On 01/28/2014 10:33 AM, Bruce Momjian wrote: On Mon, Jan 27, 2014 at 07:48:33PM -0500, Tom Lane wrote: Susan Cassidy writes: This is 9.2 I'd bet a very good dinner that it isn't --- maybe your psql is, but your server has to be pre-9.0. Try "select version();" to see the actual version of th

Re: [GENERAL] way to custom sort column by fixed strings, then by field's content

On 02/03/2014 01:22 PM, Adrian Klaver wrote: On 02/03/2014 12:01 PM, Susan Cassidy wrote: CCing the list. This didn't sort the way I want. It ended up as: description| description ---+- 18 cm long wrench | Scene 1 absolute root | blue screwdriver

Re: [GENERAL] way to custom sort column by fixed strings, then by field's content

On 02/03/2014 01:22 PM, Adrian Klaver wrote: On 02/03/2014 12:01 PM, Susan Cassidy wrote: CCing the list. This didn't sort the way I want. It ended up as: description| description ---+- 18 cm long wrench | Scene 1 absolute root | blue screwdriver

Re: [GENERAL] way to custom sort column by fixed strings, then by field's content

On 02/03/2014 01:01 PM, Susan Cassidy wrote: description| description ---+- 18 cm long wrench | Scene 1 absolute root | blue screwdriver | Scene 1 red toolbox | Scene 1 root 3| Scene 1 root 4| Scene 2 root 6

Re: [GENERAL] The timezone oddities

On 02/04/2014 01:21 PM, Alvaro Herrera wrote: Sergey Konoplev escribió: Hi, Gentoo Linux, PostgreSQL 9.2.4. I'm trying to find out why postgres uses a specific time zone that I don't expect to be used, and without any success so far. The situation seems strange to me, but I could probably miss

Re: [GENERAL] Is it reasonable to store double[] arrays of 30K elements

On 02/04/2014 01:52 PM, AlexK wrote: Every row of my table has a double[] array of approximately 30K numbers. I have ran a few tests, and so far everything looks good. I am not pushing the limits here, right? It should be perfectly fine to store arrays of 30k double numbers, correct? -- View

Re: [GENERAL] The timezone oddities

On 02/04/2014 03:44 PM, Tom Lane wrote: Adrian Klaver writes: On 02/04/2014 12:31 PM, Rob Sargent wrote: Perhaps building from source does make a guess at TZ. I am not residing in the Navaho national territory, but is that just Mountain time? Yes: http://en.wikipedia.org/wiki

Re: [GENERAL] Ordering Results by a Supplied Order

On 02/06/2014 11:57 AM, Gavin Flower wrote: On 07/02/14 05:43, Michael Sacket wrote: On Feb 6, 2014, at 2:23 AM, Vik Fearing wrote: On 02/06/2014 04:16 AM, Michael Sacket wrote: Often times I find it necessary to work with table rows in a specific, generally user-supplied order. It could be

Re: [GENERAL] Offending My Tender Sensibilities -OR- OLTP on a Star Schema

On 03/04/2014 01:40 PM, Merlin Moncure wrote: On Tue, Mar 4, 2014 at 2:15 PM, Kevin Grittner wrote: Roy Anderson wrote: We have an OLTP database and no data warehouse. We are currently planning out a build for a data warehouse however (possibly using Hadoop). "X" is recommending that we conv

Re: [GENERAL] Offending My Tender Sensibilities -OR- OLTP on a Star Schema

On 03/06/2014 03:52 PM, Merlin Moncure wrote: On Tue, Mar 4, 2014 at 3:08 PM, Rob Sargent wrote: Do you make a distinction between a key and an index? I'm not picking up on design-by-natural-key and what that entails. Especially the notion that the natural key of a given item might be mu

Re: [GENERAL] another trigger problem

On 03/07/2014 04:06 PM, Adrian Klaver wrote: On 03/07/2014 02:48 PM, Susan Cassidy wrote: I have another problem with a slightly different trigger. It's very weird, because it is exactly the same as the first trigger, that now works, except for the table name. The error is: ERROR: query stri

Re: [GENERAL] Cannot insert to 'path' field using EclipseLink

On 03/12/2014 11:12 AM, Daryl Foster wrote: I've manually entered some data into table to see if I could successfully pull data from the table using EclipseLink. Now the convertDataValueToObjectValue method in my Converter throws the following exception: java.lang.ClassCastException: org.post

Re: [GENERAL] puzzling perl DBI vs psql problem

On 03/13/2014 02:44 PM, Susan Cassidy wrote: No, I don't have 2 instances running. I default the port on the psql command line, and the perl program is using 5432, as normal. Now, I'm discovering that syslog is no longer logging anything. I bounced it, but to no avail. Susan On Thu, Mar

Re: [GENERAL] puzzling perl DBI vs psql problem

On 03/13/2014 03:23 PM, Susan Cassidy wrote: The only one that comes out different is inet_server_addr, via the program, it comes out: ::1 whereas via psql it comes out empty. Yes, I am 100% sure I am using the same schema (which I never specify, so I am using 'public') and the same user and

[GENERAL] char array overhead

I'm angling toward using a very wide char(1) array. Is the one-byte overhead for char(n<126) applied to each element or to the array?

Re: [GENERAL] char array overhead

On 03/31/2014 09:48 AM, Steve Atkins wrote: On Mar 31, 2014, at 8:08 AM, Rob Sargent wrote: I'm angling toward using a very wide char(1) array. Is the one-byte overhead for char(n<126) applied to each element or to the array? Each element, it's a variable length type. There

Re: [GENERAL] SQL works but same function is confused

/CREATE OR REPLACE FUNCTION get_status/ /RETURNS TABLE (category SMALLINT, status TEXT, tool_count BIGINT) AS/ /$BODY$/ // #variable_conflict use_variable /DECLARE/ /change this to "s_status TEXT;"/ I think it's conflicting with the column alias of same // /v_status TEXT;/ /BEGIN/ /

Re: [GENERAL] SQL works but same function is confused

On 04/01/2014 06:10 PM, Bui, Michelle P wrote: Hi all, I have this query that when executed as a SQL statement, it works perfect! The table tools contains many records in a time series, with attributes like category but without the field status. I assign the value of status 'active' or 'inac

[GENERAL] COPY v. java performance comparison

I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm getting. I hope this report isn't too wishy-washy for reasoned comment. One model says a genotype is defined as follows: Table "public.oldstyle"

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 01:56 PM, Steve Atkins wrote: On Apr 2, 2014, at 12:37 PM, Rob Sargent wrote: I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm getting. I hope this report isn't too wishy-was

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM, Steve Atkins wrote: Impatience got the better of me and I killed the second COPY. This time it had done 54% of the file in 6.75 hours, extrapolating to roughly 12 hours to do the

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM, Steve Atkins wrote: Well indeed there are copious LOG/HINT pairs along the lines of

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 04:36 PM, Jeff Janes wrote: On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm g

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 04:36 PM, Jeff Janes wrote: On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm g

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 06:06 PM, Adrian Klaver wrote: On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob Sargent wrote: On 04/02/2014 01:56 PM

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 08:40 PM, Adrian Klaver wrote: On 04/02/2014 05:30 PM, Rob Sargent wrote: On 04/02/2014 06:06 PM, Adrian Klaver wrote: On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM

Re: [GENERAL] COPY v. java performance comparison

On 04/03/2014 09:01 AM, Thomas Kellerer wrote: Rob Sargent, 02.04.2014 21:37: I loaded 37M+ records using jOOQ (batching every 1000 lines) in 12+ hours (800+ records/sec). Then I tried COPY and killed that after 11.25 hours when I realised that I had added on non-unque index on the name fields

Re: [GENERAL] COPY v. java performance comparison

Is the java app cpu bound? > Also watch "vmstat 3" for a minute or two. The last two numbers (wa > & id) (some vmstat's have a steal, ignore that) will tell you if you > are io bound. > > -Andy > > During COPY, with autovaccume off (server restarted, manual vacuum to get things going). Immed

Re: [GENERAL] COPY v. java performance comparison

On 04/03/2014 01:28 PM, Jeff Janes wrote: On Thu, Apr 3, 2014 at 9:04 AM, Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: I have to straighten out my environment, which I admit I was hoping to avoid. I reset checkpoint_segments to 12 and restarted my server. I kick

Re: [GENERAL] check constraint question

On 04/08/2014 02:51 PM, CS_DBA wrote: Hi All we have a table like so: customer ( cust_id integer not null primary key, cust_group_id integer not null, group_account_idinteger not null, cust_name varchar not null, ... ) we want to force the cust_

Re: [GENERAL] check constraint question

On 04/08/2014 03:09 PM, CS_DBA wrote: On 04/08/2014 02:58 PM, Rob Sargent wrote: On 04/08/2014 02:51 PM, CS_DBA wrote: Hi All we have a table like so: customer ( cust_id integer not null primary key, cust_group_id integer not null, group_account_idinteger

Re: [GENERAL] check constraint question

On 04/08/2014 03:26 PM, CS_DBA wrote: On 04/08/2014 03:17 PM, Rob Sargent wrote: On 04/08/2014 03:09 PM, CS_DBA wrote: On 04/08/2014 02:58 PM, Rob Sargent wrote: On 04/08/2014 02:51 PM, CS_DBA wrote: Hi All we have a table like so: customer ( cust_id integer not null

Re: [GENERAL] check constraint question

On 04/08/2014 03:36 PM, CS_DBA wrote: On 04/08/2014 03:31 PM, Rob Sargent wrote: On 04/08/2014 03:26 PM, CS_DBA wrote: On 04/08/2014 03:17 PM, Rob Sargent wrote: On 04/08/2014 03:09 PM, CS_DBA wrote: On 04/08/2014 02:58 PM, Rob Sargent wrote: On 04/08/2014 02:51 PM, CS_DBA wrote: Hi All

Re: [GENERAL] check constraint question

On 04/08/2014 03:53 PM, CS_DBA wrote: On 04/08/2014 03:41 PM, Rob Sargent wrote: On 04/08/2014 03:36 PM, CS_DBA wrote: On 04/08/2014 03:31 PM, Rob Sargent wrote: On 04/08/2014 03:26 PM, CS_DBA wrote: On 04/08/2014 03:17 PM, Rob Sargent wrote: On 04/08/2014 03:09 PM, CS_DBA wrote: On 04

Re: [GENERAL] encrypting data stored in PostgreSQL

On 04/09/2014 02:52 PM, John R Pierce wrote: On 4/9/2014 1:40 PM, CS_DBA wrote: Hi All; We have a client with this requirement: At rest data must be encrypted with a unique client key Any thoughts on how to pull this off for PostgreSQL stored data? encrypt the data in the client application

Re: [GENERAL] About upgrading a (tuple?) lock in a rollback'd sub-transaction

And it also tells you how to stop it --bibtex iirc Sent from my iPhone > On Apr 9, 2014, at 8:41 PM, Amit Langote wrote: > > Hi, > > Currently there is a warning against the following in manual: > > BEGIN; > SELECT * FROM mytable WHERE key = 1 FOR UPDATE; > SAVEPOINT s; > UPDATE mytable SET .

Re: [GENERAL] Stored procedures and schema renames

Code for db functions should be a repository. Easy edit and rerun Sent from my iPhone > On Apr 10, 2014, at 6:19 AM, Florian Weimer wrote: > > I would like to rename schemas without breaking the stored procedures in > them. Currently, this does not work if the stored procedure definition > c

Re: [GENERAL] Is it possible to load two different versions of the JDBC driver in the same application?

On 04/11/2014 03:31 PM, Tecno Brain wrote: I have a Java application that is using Postgres 9.1, and we use the latest 9.1 JDBC driver. The same Java app requires access now to a Redshift cluster. Amazon recommends to use a Postgres 8 JDBC driver for connecting to Redshift. Can I load both dri

Re: [GENERAL] Approach to Data Summary and Analysis

On 04/14/2014 04:22 PM, Robert DiFalco wrote: But then I lose a bunch of data like the TIMESTAMPTZ of the call, answer, connection, etc. Btw, currently these tables never need to be UPDATEd. They are immutable in the current design. And in the end I'm not sure how the proposal of one table and

<    1   2   3   4   >