Re: [GENERAL] Need help for constructing query

erformance concerns I'd just use what you have and move on. SQL is very good at getting results but most non-trivial expressions do become complicated. Using views and/or functions can hide of the complexity but it still ends up being present. David J. -Original Message- From: p

Re: [GENERAL] revoke permissions - not working as expected

ng into this myself, using 9.0, so please forgive if some of the above thoughts are not applicable for 8.3 David J. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Susan Cassidy Sent: Wednesday, March 30, 2011 1:32 PM To: pgsql-genera

Re: [GENERAL] Counting records in a child table

= parent.id),0) AS childcount --coalesce may not be necessary FROM parent Window Functions can also give appropriate results. I am not positive whether COUNT(*) excludes NULL during its count but a quick documentation search or just trying it will tell you that. David J. -Original

Re: [GENERAL] Counting records in a child table

to spare to try out a Window based solution; especially since my gut says the LEFT JOIN on the grouped child is likely the best solution anyway. David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Mike Orr Sent: Thursda

Re: [GENERAL] Plpgsql function to compute "every other Friday"

a function parameter. I'll have to leave it to you or others to address the specific way to integrate the modulo operator/function into the algorithm. David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of C. Bense

Re: [GENERAL] unique amount more than one table

You can try restricting all name insertions (on any of the tables) to go through one or more functions that serialize amongst themselves. Basically lock a common table and check the view for the new name before inserting. On Apr 5, 2011, at 18:02, Perry Smith wrote: > I have five tables each

Re: [GENERAL] Using Function returning setof record in JOIN

great and timely value. You should find that both versions work but the "inline view" form most definitely will whereas the "direct" form should but I haven't ever attempted to use that form before so I cannot say for certain. David J. -Original Messa

Re: [GENERAL] what data type to store fixed size integer?

the data type does not need any arithmetic operations (as of integers). You arguably do not have a number but simply a string that looks like a number. Other examples are zip-codes and phone-numbers if you ignore symbols. Thus you should probably use an appropriately sized char/varchar.

Re: [GENERAL] what data type to store fixed size integer?

[mailto:apajooha...@gmail.com] Sent: Tuesday, April 12, 2011 1:32 AM To: David Johnston Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] what data type to store fixed size integer? @Dave On Mon, Apr 11, 2011 at 9:18 PM, David Johnston wrote: >>>> the data type does not need an

Re: [GENERAL] updating rows which have a common value forconsecutive dates

If you have the ability to use Window functions you can group (as necessary), order by last_update, and then use rank() to number each test run sequentially. Then you can limit the results to ( rank() <= 2 AND current_status = 'FAILED' ). David J. -Original Message

Re: [GENERAL] updating rows which have a common value forconsecutive dates

have a hunch you might need a third layer of sub-queries to handle the failure aspect of the requirement properly; possibly as part of a "WITH" CTE. You for sure need to in order to. Properly utilize the rank() function limiting. Dave On Apr 14, 2011, at 0:52, Lonni J Friedman wro

Re: [GENERAL] Question regarding DEALLOCATE pdo_stmt_00000001

> > One more thing that bothers me, why this idle connection can be stayed > idle for 3 days. Is this a zombie process?​ > > ​You said:​ ​"I am using PostgreSQL 9.3.2 and PgBouncer." ​It is PgBouncer that is keeping these connections open and available for immediate usage in its connection pool. David J.

[GENERAL] COPY TO returns "ERROR: could not open file for writing: No such file or directory"

COPY (SELECT * FROM page WHERE "PublishDate" between '2014-03-01' and '2014-04-01') TO '/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy'; gives me: ERROR: could not open file "/home/ygg/sql/backup/pagedump.2014-03-01.to.2014-04-01.copy" for writing: No such file or directory Running

Re: [GENERAL] Re: COPY TO returns "ERROR: could not open file for writing: No such file or directory"

I got it fella's, thanks. It was a really simple oversight on my part. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

tart Tomcat, it may then be fine. Apps that run okay at one time, will exhibit the error when I try to add the others in. Does anybody have any ideas on what I might try? I did a 'yum update' to get the latest, rebooted, recompiled PG and reloaded the DBs, but that made no difference.

Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

On 5/31/2014 11:47 PM, John R Pierce wrote: On 5/31/2014 11:41 PM, David Wall wrote: FATAL: invalid value for parameter "TimeZone": "PST" I'd be using "America/Los_Angeles" as the timezone rather than PST, as the TLA timezones are ambiguous (CST is bot

Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

even be returning an abbreviation given that Postgres will not accept it as a TimeZone value? Thanks for the extra help, Adrian. It led me to investigate the 35 webapps we deploy on Tomcat and I found 2 rogue apps that set their timezone to "PST". Once I fixed these two, all

[GENERAL] Warm standby (log shipping) from PG 8.3 to 9.3

s our current warm standby but just adding 'wal_level = hot_standby' to the master's postgresql.conf ? Is there anything I can read about moving from 8.3 warm standby to 9.3 hot standby? It's a bit confusing because of the various options for standby mode. Would it be better

Re: [GENERAL] Warm standby (log shipping) from PG 8.3 to 9.3

On 6/10/2014 11:54 AM, hubert depesz lubaczewski wrote: On Tue, Jun 10, 2014 at 8:13 PM, David Wall <mailto:d.w...@computer.org>> wrote: Is it safe to assume that my working PG 8.3 archive command on the master and recovery.conf (using contrib's pg_standby) on the sta

[GENERAL] pg_dump slower than pg_restore

there something that might be wrong about my configuration that the backup is slower than the restore? Thanks, David -- 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] pg_dump slower than pg_restore

On 7/3/2014 10:36 AM, Bosco Rama wrote: If those large objects are 'files' that are already compressed (e.g. most image files and pdf's) you are spending a lot of time trying to compress the compressed data ... and failing. Try setting the compression factor to an intermediate value, or even ze

Re: [GENERAL] pg_dump slower than pg_restore

On 7/3/2014 10:38 AM, Tim Clarke wrote: I'd also check the effect of those other run components; the vacuum's and other things that are only running with the backup and not during the restore. The vacuumlo, vacuum and analyze run before the pg_dump. I am not talking about any of the time t

Re: [GENERAL] pg_dump slower than pg_restore

On 7/3/2014 5:13 PM, Bosco Rama wrote: If you use gzip you will be doing the same 'possibly unnecessary' compression step. Use a similar approach to the gzip command as you would for the pg_dump command. That is, use one if the -[0-9] options, like this: $ pg_dump -Z0 -Fc ... | gzip -[0-9] ...

Re: [GENERAL] pg_dump slower than pg_restore

On 7/3/2014 6:26 PM, Bosco Rama wrote: Hmmm. You are using '--oids' to *include* large objects? IIRC, that's not the intent of that option. Large objects are dumped as part of a DB-wide dump unless you request that they not be. However, if you restrict your dumps to specific schemata and/or ta

Re: [GENERAL] pg_dump slower than pg_restore

On 7/3/2014 10:13 PM, Bosco Rama wrote: Is the issue with S3 or just transfer time? I would expect that 'rsync' with the '--partial' option (or -P if you want progress info too) may help there. Don't know if rsync and S3 work together or what that would mean, but it's not an issue I'm suffer

Re: [GENERAL] pg_dump slower than pg_restore

On 7/3/2014 11:47 AM, Eduardo Morras wrote: No, there's nothing wrong. All transparent compressed objects stored in database, toast, lo, etc.. is transparently decompressed while pg_dump access them and then you gzip it again. I don't know why it doesn't dump the compressed data directly. T

Re: [GENERAL] pg_dump slower than pg_restore

On 7/4/2014 7:19 AM, Tom Lane wrote: You haven't given us much info about the contents of this database. Are there a lot of tables? functions? large objects? How many is "a lot", if so? I'm suspicious that you're paying a penalty associated with pg_dump's rather inefficient handling of metadat

Re: Re : [GENERAL] Query "top 10 and others"

country, state ) , u1 AS ​( SELECT country, state, st_pop FROM QRY ORDER BY st_pop DESC LIMIT 10 ) , u2 AS ( SELECT 'other' AS country, '' AS state, sum(st_pop) FROM QRY WHERE NOT EXISTS ( SELECT 1 FROM u1 WHERE (QRY.country, QRY.state) = (u1.country, u1,state) ) SELECT * FROM u1 UNION ALL SELECT * FROM u2 ; David J.

Re: [GENERAL] pg_dump slower than pg_restore

On 7/4/2014 11:30 AM, Bosco Rama wrote: Random thought: What OS & kernel are you running? Kernels between 3.2.x and 3.9.x were known to have IO scheduling issues. This was highlighted most by the kernel in Ubuntu 12.04 (precise) as shown here:

Re: [GENERAL] pg_dump slower than pg_restore

On 7/4/2014 9:18 PM, Tom Lane wrote: There are only 32 table, no functions, but mostly large objects. Not sure how to know about the LOs, but a quick check from the table sizes I estimate at only 2GB, so 16GB could be LOs. There are 7,528,803 entries in pg_catalog.pg_largeobject. Hmm ... how m

Re: [GENERAL] pg_dump slower than pg_restore

On 7/6/2014 9:06 AM, Tom Lane wrote: David Wall writes: There's one row in pg_largeobject_metadata per large object. The rows in pg_largeobject represent 2KB "pages" of large objects (so it looks like your large objects are averaging only 8KB-10KB apiece). The "metadata

Re: [GENERAL] Referencing serial col's sequence for insert

nd so you can simply pick off the value assigned to "col1" and do what you'd like with it. Combined with a constraint you can remove the entire business rule from user logic and embed it into the database where it cannot be messed up. David J.​

Re: [GENERAL] Re: Feature proposal and discussion: full-fledged column/function equivalence

On Fri, Aug 1, 2014 at 6:22 PM, Chris Travers wrote: > On Fri, Aug 1, 2014 at 12:19 PM, David G Johnston < > david.g.johns...@gmail.com> wrote: > >> Vik Fearing wrote >> >> CREATE testfunction(test) returns int language sql as $$ select 1; $$; &g

Re: [GENERAL] Re: understanding why two nearly identical queries take two different planner routes, one 5s and one 2hr

On Wed, Aug 6, 2014 at 10:08 AM, Jeff Janes wrote: > On Tue, Aug 5, 2014 at 4:30 PM, David G Johnston < > david.g.johns...@gmail.com> wrote: > >> >> Anyway, you should probably experiment with creating a multi-column index >> instead of allowing PostgreSQL to Bit

Re: [GENERAL] Questions on dynamic execution and sqlca

intless. > > > ​Yeah, I probably should have either researched the answer or just left it alone. I am not all that familiar with pgAdmin - I figured it was just a souped up script runner with maybe a couple of features like variables but otherwise allowing only SQL commands. David J.​

Re: [GENERAL] pgcluu

Hello You must install the harness.pm module You may do it via yum yum install perl-Test-Harness or downloaded the tar package and install it http://search.cpan.org/~leont/Test-Harness-3.32/lib/Test/Harness.pm I hope this help you. David On 08/11/2014 08:52 AM, Ramesh T wrote: Hello

Re: [GENERAL] pgcluu

Hello When you install pgcluu, this program will need some perl modules that normally they are not installed in our OS For this reason the installation stop some times. You must search the different modules required in the internet and install them. I hope this help you David On Tue 12

[GENERAL] puzzled by "commit" Logging statement duration

Hoping for a teachable moment :+) With options enabled to log statement execution times in the postgres log file I observe: 2014-09-02T12:47:38.107808-04:00 alabama local0 info postgres[37874]: [702-1] user=ace_db_client,db=ace_db LOG: duration: 0.040 ms statement: BEGIN 2014-09-02T12:47:38

Re: [GENERAL] Async IO HTTP server frontend for PostgreSQL

Hi Dmitriy, are you able to say a little about what's driving your quest for async http-to-pg ? I'm curious as to the motivations, and whether they match up with some of my own reasons for wanting to use low-thread-count solutions. Thanks. -- Sent via pgsql-general mailing list (pgsql-gener

[GENERAL] I want the stupidest possible binary export

I've got a some tables with bytea fields that I want to export only the binary data to files. (Each field has a gzipped data file.) I really want to avoid adding overhead to my project by writing a special program to do this, so I'm trying to do it from psql. Omitting the obvious switches for user

Re: [GENERAL] I want the stupidest possible binary export

Adrian Klaver writes: >> psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to >> 'file'" with format binary > > From here: > > http://www.postgresql.org/docs/9.2/static/app-psql.html > > the above should be: > > psql -t -c "\copy (select mybinaryfield from mytable wher

Re: [GENERAL] I want the stupidest possible binary export

Jov writes: > psql can only input／output text string，which can not be binary content。with > 9.2，you can encode bytea to base64，save to file，then use shell command to > decode the file。 > google “amutu.com pg bytea” can get a blog post。 I wondered if I could do that. OK, will try it, thanks. smi

Re: [GENERAL] I want the stupidest possible binary export

Jov writes: > psql can only input／output text string，which can not be binary content。with > 9.2，you can encode bytea to base64，save to file，then use shell command to > decode the file。 This worked, btw. Encoded to base64, piped to sed to fix the newlines, piped to 'base64 -id' and then to file.

[GENERAL] Re: How can i monitor exactly what (partition) tables are accessed by a query?

On Friday, September 19, 2014, Alban Hertroys wrote: > On 19 Sep 2014, at 3:50, Robert Nix > > wrote: > > > Thanks, David. > > > > I have read that page many times but clearly I have forgotten this: > > > > • Constraint exclusion only work

[GENERAL] problem in to_char( ) ?

If I have a select statement where I am formatting both a duration ( interval data type ) and timestamp ( timestamp with tz datatype ) I am getting an odd behavior. Note the extra characters following the seconds in the last_update column. ace_db=# select port_id, to_char(ppa.term_

Re: [GENERAL] Query optimization

List preference is to inline post or, at worse, bottom post. Please do not top post. On Wed, Oct 29, 2014 at 11:06 AM, Jorge Arevalo wrote: > Hello David, many thanks for your responses, > > Sorry for not providing the content of the fill_table3_function, but it > just execu

Re: [GENERAL] Query optimization

s in place because the planner choose to execute the correlated subquery as a standalone query since it realizes that it is going to have to end up processing the entire table anyway due to the lack of a filter on the outer query. In effect executing "table1 JOIN (table2 subquery) ON (f3 = field7)"​. David J.

Re: [GENERAL] Query optimization

​I guess the InitPlan 1 you showed simply scanned table2 and applied the filter which then was fed to InitPlan 2 where the array is built; that array then is inserted into the outer query ~8M​ times... David J.

Re: [GENERAL] PostgreSQL on tablet grade SSD ?

On 10/31/2014 7:31 AM, Merlin Moncure wrote: Can anyone share any experiences with running PostgreSQL on a tablet ? (Surface Pro 3, ASUS Transformer) The SSD in a modern tablet is considerably faster than a hard drive in a high-end server from the era when PG was originally developed so fr

Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7

On Sunday, November 9, 2014, Adrian Klaver wrote: > On 11/09/2014 10:14 AM, David G Johnston wrote: > >> Adrian Klaver-4 wrote >> >>> Thank you for all comments and suggestions. >>>> >>> >>> More comments/suggestions will have to wait unt

Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7

Yes, that is what I was referring to. The Nabble.com website showed them. http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-td5826230.html David J. On Sunday, November 9, 2014, Magnus Hagander wrote: > On Sun, Nov 9, 2014 at 11:37 PM, David Johns

Re: [HACKERS] Re: [GENERAL] Performance issue with libpq prepared queries on 9.3 and 9.4

On Thu, Nov 13, 2014 at 5:47 PM, Tom Lane wrote: > David G Johnston writes: > > Tom Lane-2 wrote > >> In the meantime, I assume that your real data contains a small > percentage > >> of values other than these two? If so, maybe cranking up the statistics > >

Re: [GENERAL] [sfpug] Linuxfest 2015 Call for Papers

Hmm, end of January in Bellingham... Sounds like a great excuse to ski Mt Baker and beyond :D. -David > On Nov 19, 2014, at 12:09 PM, Josh Berkus wrote: > > On 11/19/2014 11:57 AM, Adrian Klaver wrote: >> >> So as not to have lost souls wandering around Seattle in April, L

Re: [GENERAL] Range type bounds

The only place that is >> answered >> is a single example. It should be in the body of the text too. >> > > Well I spend my time in Python for the most part so: > > def test_fnc(a, b=None): > print a, b > > In [12]: test_fnc('a',) > a None > > I w

Re: [GENERAL] Range type bounds

On Wednesday, November 26, 2014, Adrian Klaver wrote: > On 11/26/2014 12:34 PM, David Johnston wrote: > >> >> I guess what is confusing to me is the transition between the text >> mode and the constructor mode is not clear. In particular the page >>

Re: [GENERAL] Converting xml to table with optional elements

?[...] ) LIMIT 1) This will cause either the first array element or NULL set to be the given column's value. Note that I do not believe your example code is going to work. As I mentioned you really want to create a table of documents and NOT try to pair up multiple unnested columns. David J.

Re: [GENERAL] Converting xml to table with optional elements

r will do in the presence of more than a single row on temprid. Typically UPDATE table1 ... FROM table2 requires a WHERE clause of the form "WHERE table1.id = table2.id"... David J.

Re: [GENERAL] Converting xml to table with optional elements

lp because you have to return an entire node - xml elements included. The textual representation of that node is then to be stored and all the extraction xpath expression executed against it. David J. ​

[GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

We are developing on and running Postgres 9.3.5 on FreeBsd 10.0-p12. We have been experiencing a intermittent postgres core dump which Seems primarily to be associated with the the 2 functions below. The area of interest is based on the content of the postgres log file which often indicates

Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, December 03, 2014 3:57 PM To: Day, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ? "Day, David" writes: > We are developing on and runnin

Fwd: [GENERAL] Array Comparison

Please send replies to the list. On Friday, December 5, 2014, Ian Harding > wrote: > > > On Fri, Dec 5, 2014 at 5:37 PM, David G Johnston < > david.g.johns...@gmail.com> wrote: > >> Ian Harding wrote >> > On Fri, Dec 5, 2014 at 4:55 PM, Ian Harding < >

Re: FW: [GENERAL] SQL rolling window without aggregation

On Monday, December 8, 2014, Huang, Suya wrote: > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org ] On Behalf Of David G > Johnston > Sent: Monday, December 08, 2014 1:18 PM > To: pgsql-general@postgresql.org

Re: [GENERAL] Combining two queries

the two people are friends.​ ​"One Query" does not mean you need to do everything​ all-at-once. I suggest you make use of CTEs (WITH) subqueries for each distinct calculation you need then join all of the CTE items together in a final query the outputs the data in the format desired. David J.

[GENERAL] psql feature request: --list-conninfo (dump the config info psql would use to connect)

s is to get a reaction and hopefully, if favorable, get this added to the Wiki ToDo area. Its not something I have the ability to code or desire to directly fund but it does seem like a feature that would contribute to new user friendliness and ease-of-use. Thanks! David J.

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 8:49 AM, Adrian Klaver wrote: > On 12/28/2014 05:04 PM, David G Johnston wrote: > > Adrian Klaver-4 wrote > >> On 12/28/2014 10:06 AM, Viktor Shitkovskiy wrote: > >>> I include my own scripts. Each of them creates some table or makes some &g

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver wrote: > On 12/29/2014 07:59 AM, David Johnston wrote: > >> >> Anyway, the third undocumented bug is that --single-transactions gets to >> send its COMMIT even if ON_ERROR_STOP​ >> ​takes hold before the end of the sc

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 9:49 AM, David Johnston wrote: > On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver > wrote: > >> On 12/29/2014 07:59 AM, David Johnston wrote: >> >>> >>> Anyway, the third undocumented bug is that --single-transactions gets to &g

Re: [GENERAL] Rollback on include error in psql

Copying -bugs to gain broader attention and opinions. On Mon, Dec 29, 2014 at 10:06 AM, Adrian Klaver wrote: > On 12/29/2014 08:49 AM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 9:39 AM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >>

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver wrote: > On 12/29/2014 09:38 AM, David Johnston wrote: > >> >> This is one of those glass half full/empty situations, where it is >> down to the eye of the beholder. I would also say this a perfect >> exampl

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver wrote: > On 12/29/2014 02:28 PM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 3:07 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >> On 12/29/2014 09:38 AM, David Johnston wrote: >&g

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 4:38 PM, Adrian Klaver wrote: > On 12/29/2014 02:55 PM, David Johnston wrote: > >> On Mon, Dec 29, 2014 at 3:37 PM, Adrian Klaver >> mailto:adrian.kla...@aklaver.com>>wrote: >> >> On 12/29/2014 02:28 PM, David Johnston wrote: >&g

Re: [GENERAL] Rollback on include error in psql

On Mon, Dec 29, 2014 at 5:09 PM, Adrian Klaver wrote: > On 12/29/2014 03:56 PM, David Johnston wrote: > >> > ​So you think psql should issue "COMMIT;" even if it is exiting due to >> > "ON_ERROR_STOP"? >> > > I say yes, if it is a

Re: [HACKERS] [GENERAL] ON_ERROR_ROLLBACK

On Tue, Dec 30, 2014 at 8:54 AM, Adrian Klaver wrote: > On 12/30/2014 07:43 AM, David G Johnston wrote: > >> Tom Lane-2 wrote >> >>> Bernd Helmle < >>> >> >> mailings@ >>> >> >> > writes: >>> >>&

Re: [GENERAL] ORDER BY in prepared statements

On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries wrote: > Paul Jungwirth wrote > > I'm not sure how to make a prepared statement that lets you name a > > column when you execute it. Maybe someone else can chime in if that's > > possible. > > David J. res

Re: [GENERAL] ORDER BY in prepared statements

dn't need to do)... Catalog lookups would be expensive to do pro-actively. The goal is to form a safe query for the parser and let the planner deal with any identifiers that end up being invalid either through attempted injection or simply usage errors. David J.

Re: [GENERAL] how to duplicate data for few times by SQL command in PG

an > > Original Table > 23 > 45 > 65 > 22 > > New Table > 23 > 23 > 23 > 45 > 45 > 45 > 65 > 65 > 65 > 65 > 22 > 22 > 22 > 22 > Tables do not have order. Cross join. David J.

[GENERAL] sslmode verify-ca and verify-full: essentialy the same?

ng? How does libpq verify the server's name? Reverse DNS? Other mean? Hoping someone can enlighten me about this, Regards. -- David Guyot Administrateur système, réseau et télécom / Sysadmin Europe Camions Interactive / Stockway Moulin Collot F-88500 Ambacourt 03 29 30 47 85 signat

Re: [GENERAL] sslmode verify-ca and verify-full: essentialy the same?

: > On Tue, Jan 27, 2015 at 2:29 PM, David Guyot > wrote: > Hi, there. > > Firstly, as this is my first post on a PgSQL ML, I hope this > ML is the > good one for my question. > > I'm trying to secure further

Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

It has been some time since we have seen this problem. See earlier message on this subject/thread for the suspect plperl function executing at the time of the core. Someone on our development team suggested it might relate to some build options of perl. In particular MULTIPLICITY or THREADS

Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

segmentation fault puzzling. Regards Dave From: Alex Hunsaker [mailto:bada...@gmail.com] Sent: Thursday, January 29, 2015 12:58 AM To: Day, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ? On Wed, Jan 28, 2015 at 1:23

Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

infrequency of the segmentation fault puzzling. Regards Dave From: Alex Hunsaker [mailto:bada...@gmail.com] Sent: Thursday, January 29, 2015 12:58 AM To: Day, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ? On Wed

Re: [GENERAL] Subselect with no records results in final empty set

are going together are "related" and the join conditions reflect those relationships. I'd suggest using actual tables (or CTE/WITH) with multiple rows of data to try and learn how to write queries. The number of times you are going to join together multiple results each only having a single row is slim. David J.​

Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

Thanks for the inputs, I’ll attempt to apply it and will update when I have some new information. Thanks Dave From: Alex Hunsaker [mailto:bada...@gmail.com] Sent: Thursday, January 29, 2015 3:30 PM To: Day, David Cc: pgsql-general@postgresql.org; Tom Lane Subject: Re: [GENERAL] segmentation

Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

segmentation fault Thanks for your assistance on this matter. Dave From: Alex Hunsaker [mailto:bada...@gmail.com] Sent: Thursday, January 29, 2015 6:10 PM To: Day, David Cc: pgsql-general@postgresql.org; Tom Lane Subject: Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ? On

Re: [GENERAL] Using row_to_json with %ROWTYPE ?

WTYPE; > >> BEGIN > >> select * into strict v_row from my_view where foo=bar; > >> select row_to_json(v_row) from v_row; > > ​A third problem you will hit, when you fix the syntax, is that the SELECT row_to_json(...) command has no target and thus needs to use PERFORM, not SELECT. ​ David J.

Re: [GENERAL] Using row_to_json with %ROWTYPE ?

that: (SELECT v_​row FROM v_row) would give that message but I get a "relation v_row does not exist" error when trying to replicate the scenario. ​It may even be a bug but since you have not provided a self-contained test case, nor the version of PostgreSQL, the assumption is user error.​ David J.

Re: [GENERAL] Using row_to_json with %ROWTYPE ?

d" - how would you expect PostgreSQL to know which one is from the table and which one is from the function? The only other option is to pick one of them but in that case you'd simply get a constant TRUE and every row would be returned. David J.

Re: [GENERAL] Using row_to_json with %ROWTYPE ?

specifier. PostgreSQL does not store that information and so will not prevent a call from passing in a string longer than 64 characters into the function. This applies to any specification in () following a type declaration (say for numeric or timestamptz) David J. ​

Re: [GENERAL] Using row_to_json with %ROWTYPE ?

sion-id passed (via raise notice or similar), but you want to use the "RAISE;" form (i.e., no args) to re-raise the original error. http://www.postgresql.org/docs/9.4/interactive/plpgsql-errors-and-messages.html David J.

[GENERAL] Stability of JSON textual representation

I've noticed that when representing lists as JSON, Postgres 9.4 sometimes outputs spaces after commas, and other times does not. # SELECT array_to_json(array[1, 2, 3]), json_build_array(1, 2, 3); array_to_json | json_build_array ---+-- [1,2,3] | [1, 2, 3] Thi

Re: [GENERAL] Stability of JSON textual representation

On 9 February 2015 at 03:57, Gavin Flower wrote: > > Would using jsonb be more consistent? > Yes, casting to jsonb seems produce consistent output: # SELECT array_to_json(array[1, 2, 3])::jsonb, json_build_array(1, 2, 3)::jsonb; array_to_json | json_build_array ---+

[GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

Update/Information sharing on my pursuit of segmentation faults FreeBSD 10.0-RELEASE-p12 amd64 Postgres version 9.3.5 Below are three postgres core files generated from two different machine ( Georgia and Alabama ) on Feb 11. These cores would not be caused from an environment update issue th

Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

couple of suggestions to follow up on. I’ll keep the thread updated. Thanks, a good start to my Friday the 13th. Regards Dave Day From: Guy Helmer [mailto:ghel...@palisadesystems.com] Sent: Thursday, February 12, 2015 6:19 PM To: Day, David Cc: pgsql-general@postgresql.org Subject: Re

Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ?

13th. Regards Dave Day From: Guy Helmer [mailto:ghel...@palisadesystems.com] Sent: Thursday, February 12, 2015 6:19 PM To: Day, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] segmentation fault postgres 9.3.5 core dump perlu related ? On Feb 12, 2015, at 3:21 PM, Day

Re: [GENERAL] Postgres architecture for multiple instances

of information, but the optimal solution depends on how your customers connect to their database: 1) Entirely through an application or API 2) Directly to the db via psql, PgAdmin, JDBC, etc. Without that information it's hard to give really solid advice. -- - David Steele da...@pgmasters.net signature.asc Description: OpenPGP digital signature

Re: [GENERAL] Row-level Security vs Application-level authz

On 2/23/15 8:16 PM, Stephen Frost wrote: > * David G. Johnston (david.g.johns...@gmail.com) wrote: >> I take it that the table has to be permanent otherwise you would have >> suggested >> and unlogged temporary table as the target... > > A temporary table would have to

Re: [GENERAL] Row-level Security vs Application-level authz

On 2/24/15 3:07 PM, Stephen Frost wrote: > * David Steele (da...@pgmasters.net) wrote: >> On 2/23/15 8:16 PM, Stephen Frost wrote: >>> * David G. Johnston (david.g.johns...@gmail.com) wrote: >>>> I take it that the table has to be permanent otherwise you would have &g

Re: [GENERAL] Row-level Security vs Application-level authz

On 2/24/15 3:31 PM, Stephen Frost wrote: > * David Steele (da...@pgmasters.net) wrote: >> On 2/24/15 3:07 PM, Stephen Frost wrote: >>> The problem with a temporary table is, well, it goes away. :) There are >>> further concerns that, because it's created i

Re: [GENERAL] Locking during UPDATE query with SUBSELECT

like this at the same time or you might get deadlocks. -- - David Steele da...@pgmasters.net signature.asc Description: OpenPGP digital signature

Re: [GENERAL] ANALYZE after CREATE TABLE AS SELECT...

acuum, so it's best to analyze. Bonus tip: the same is true for any temp tables you might create. More so, since vacuum will never see them at all. -- - David Steele da...@pgmasters.net signature.asc Description: OpenPGP digital signature

<    1   2   3   4   5   6   7   8   9   10   >