Re: [GENERAL] Linux Distribution Preferences?

On 14/01/13 16:46, Scott Marlowe wrote: On Sun, Jan 13, 2013 at 4:06 PM, SUNDAY A. OLUTAYO wrote: 4 reasons: 1. One place where I worked Ubuntu was standard, I tried it and found that it lacked at least a couple of desktop features in GNOME 2 that I found very useful into Fedora. F

Re: [GENERAL] Linux Distribution Preferences?

On 14/01/13 22:24, Hendrik Visage wrote: On Sun, Jan 13, 2013 at 8:27 PM, Shaun Thomas mailto:stho...@optionshouse.com>> wrote: Hey guys, I'm not sure the last time I saw this discussion, but I was somewhat curious: what would be your ideal Linux distribution for a nice sol

Re: [GENERAL] INSERT... WHERE

On 15/01/13 09:43, Chris Angelico wrote: On Tue, Jan 15, 2013 at 5:26 AM, Robert James wrote: On 1/13/13, Chris Angelico wrote: On Mon, Jan 14, 2013 at 3:37 PM, Robert James wrote: Thanks. But how do I do that where I have many literals? Something like: INSERT INTO seltest (id, a, b) SELE

Re: [GENERAL] Combine Date and Time Columns to Timestamp

On 20/01/13 04:40, Kevin Grittner wrote: Rich Shepard wrote: On Fri, 18 Jan 2013, Adrian Klaver wrote: test=> SELECT ('2012-10-29 '||'10:19')::timestamp; timestamp - 2012-10-29 10:19:00 Thanks, Adrian. I suspected it was simple but I could not find a reference to the syn

Re: [GENERAL] Yet Another Timestamp Question: Time Defaults

On 23/01/13 06:30, Gavan Schneider wrote: On 01/21/2013 07:40 PM, Gavan Schneider wrote: [...] (While I suppose some politician somewhere could decide that "fall-back" could cross date boundaries, I am unaware of any place that has ever done something so pathological as to have the same date

Re: [GENERAL] Understanding behavior of SELECT with multiple unnested columns

On 27/03/13 20:36, Ian Lawrence Barwick wrote: 2013/3/27 Ken Tanzer mailto:ken.tan...@gmail.com>> Basically you are getting Cartesian joins on the row output of unnest() Well that's what I expected too. Except look at this example, after you delete c: testdb=# DE

Re: [GENERAL] Understanding behavior of SELECT with multiple unnested columns

On 28/03/13 03:03, Tom Lane wrote: Gavin Flower writes: The rule appears to be, where N_x & N_y are the number of entries returned for x & y: N_result = is the smallest positive integer that has N_x & N_y as factors. Right: if there are multiple set-returning functions in a SELE

Re: [GENERAL] Money casting too liberal?

On 29/03/13 02:28, D'Arcy J.M. Cain wrote: On Thu, 28 Mar 2013 23:43:23 +1100 Gavan Schneider wrote: But it appears that the philosophy does not extend to the "money" type. ... As the original author of the money type I guess I should weigh in. select ',123,456,,7,8.1,0,9'::money; money

Re: [GENERAL] Money casting too liberal?

On 29/03/13 10:13, D'Arcy J.M. Cain wrote: On 28 Mar 2013 20:50:42 GMT Jasen Betts wrote: it actually does that, if you have the locale installed you can set LC_MONETARY to Japan and get no decimals and a Yen symbol or to UAE and get three decimals and their currency symbol. Must have been a

Re: [GENERAL] Using varchar primary keys.

On 02/04/13 08:35, jesusthefrog wrote: On the topic of 'natural' versus 'synthetic' primary keys, I am generally in the camp that an extra ID field won't cost you too much, and while one may not need it for a simple table (i.e. id, name) one might add any number of columns later, and you'll be

Re: [GENERAL] Money casting too liberal?

On 29/03/13 12:39, Jasen Betts wrote: On 2013-03-28, Gavin Flower wrote: Hmm... This should optionally apply to time. e.g. time_i_got_up_in_the_morning should reflect the time zone where I got up - if I got up at 8am NZ time then this should be displayed, not 12pm (12 noon) to someone in Los

Re: [GENERAL] Money casting too liberal?

On 30/03/13 04:08, Gavan Schneider wrote: Some thoughts. The current MONEY type might be considered akin to ASCII. Perfect for a base US centric accounting system where there are cents and dollars and no need to carry smaller fractions. As discussed, there are some details that could be refin

Re: [GENERAL] Money casting too liberal?

On 30/03/13 11:30, Gavan Schneider wrote: On 29/3/13 at 3:32 AM, D'Arcy J.M. Cain wrote: On Fri, 29 Mar 2013 11:46:40 -0400 Tom Lane wrote: Well, this has been discussed before, and the majority view every time has been that MONEY is a legacy thing that most people would rather rip out than si

Re: [GENERAL] Money casting too liberal?

On 30/03/13 08:36, Michael Nolan wrote: On 3/27/13, Steve Crawford wrote: Somewhat more worrisome is the fact that it automatically rounds input (away from zero) to fit. select '123.456789'::money; money - $123.46 So does casting to an integer: select 1.25::integer ; int4 --

Re: [GENERAL] Money casting too liberal?

On 03/04/13 07:16, John R Pierce wrote: On 4/2/2013 12:50 AM, Gavin Flower wrote: In the bad old days when I was a COBOL programmer we always stored money in the COBOL equivalent of an integer (numeric without a fractional part) to avoid round off, but we displayed with a decimal point to

Re: [GENERAL] procedure to contribute this community

Hi Govind, While Postgres obviously does run on Microsoft O/S's, and can be complied there - if you are seriously interested in software development, you should consider Linux. Linux dominates the mobile (Android & eBooks are both based on Linux) and server segments. I also consider Linux a

Re: [GENERAL] procedure to contribute this community

On 08/04/13 09:45, Chris Angelico wrote: On Mon, Apr 8, 2013 at 4:14 AM, Gavin Flower wrote: Not to mention that it appears that Postgres runs better on Linux than on Microsoft. Linux skills are increasingly in demand, while MIcrosoft's market share is dropping (partly as a result o

Re: [GENERAL] how to create materialized view in postgresql 8.3

On 08/04/13 18:58, Zahid Quadri wrote: is it possible to created materialized view in postgresql 8.3 if yes please provide some sample. 8.3 is no longer supported

Re: [GENERAL] Automatic restart while upgrade

On 13/04/13 02:08, Tom Lane wrote: stephane.schildkne...@postgres.fr writes: I discovered that while upgrading PostgreSQL binaries through 'yum update', with PGDG RPMs, the service is automatically restarted. ISTM that this was not the case before 9.2. I dunno whether Devrim's packages acted th

Re: [GENERAL] Optimizing bulk update performance

On 27/04/13 12:14, Yang Zhang wrote: It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1, field2 = constant2, ... WHERE id = constid (We're just overwriting fields of objects identified

Re: [GENERAL] Optimizing bulk update performance

executing these bulk updates in our application.) On Fri, Apr 26, 2013 at 5:27 PM, Gavin Flower wrote: On 27/04/13 12:14, Yang Zhang wrote: It currently takes up to 24h for us to run a large set of UPDATE statements on a database, which are of the form: UPDATE table SET field1 = constant1

Re: [GENERAL] Created a PostgreSQL test, what do you think?

On 02/05/13 02:16, Jsmarterer wrote: Hey gang, We created a PostgreSQL test for database admins to test their knowledge and prove their skills to employers and members of the community. What do you think? By taking the test and flagging any wonky questions, you'll be helping make the test better

Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?

Carlo: Please see note at the bottom... On 02/05/13 04:36, Carlo Stonebanks wrote: There are no client poolers (unless pgtcl has one I don't know about) so this is unlikely. The trigger is an interesting idea to try if it happens again - I can't keep it for long as it is for a massive cache (us

Re: [GENERAL] SPI_execute_with_args call

On 03/05/13 21:19, Yuriy Rusinov wrote: Hello, colleagues ! I have to write random number generator state into database table Table structure is table rand_state { id serial not null primary key, state_rand bytea }; In C-function I do size_t nr_ins = strlen ("insert into rand_state (sta

Re: [GENERAL] Does it make sense to break a large query into separate functions?

On 09/05/13 02:47, Merlin Moncure wrote: On Wed, May 8, 2013 at 9:05 AM, Tom Lane wrote: Seref Arikan writes: I've just noticed that some parts of the autogenerated queries can be functions on their own. Would moving these parts to their own functions help the query planner? It's difficult t

Re: [GENERAL] Does it make sense to break a large query into separate functions?

On 09/05/13 09:35, Merlin Moncure wrote: [...] More oddness -- when I wrap, say, random() with stable function, I get unique value per returned row, but same value across the set when wrapped with immutable. [..] That part I think I know (but, I'm often wrong!). By saying it is immutable, yo

Re: [GENERAL] LONG delete with LOTS of FK's

On 18/05/13 03:06, Larry Rosenman wrote: On 2013-05-16 18:35, David Kerr wrote: - I'll take a look tomorrow, but we WERE seeing Seq Scan's against - multi-million - row tables, so I suspect Tom is right on with the replanning that's in - 9.2 fixing - it, and I'm in the process of validating tha

Re: [GENERAL] Authorization,Authentication issues trying basic commands.

On 22/05/13 14:15, luis redondo wrote: It's my first time using PostgreSQL and I need to do basic things as establishing a new user,create a new database,access psql command line client etc. I tried : su - postgres to access the server but I don't have a valid password,I don't know if the ser

Re: [GENERAL] PostgresQL 9.2 table query - underscores

On 17/07/13 10:04, Victoria S. wrote: Hello: My first post; a Postgres newbie ... I am teaching myself PostgresQL using a trial database, and I am having trouble with underscores: IN the following example, development=# SELECT created_at, username FROM tweets; created_at

Re: [GENERAL] How get column-wise table info from an arbitrary query?

I use 'id' for the primary key, and [tablename]_id for each foreign key, I always qualify my column references in SQL, but I would never use SELECT * when selecting from more than one table. Cheers, Gavin On 30/07/13 21:41, sidthegeek wrote: I really dislike ambiguous column names across tab

Re: [GENERAL] How get column-wise table info from an arbitrary query?

On 31/07/13 09:57, David Johnston wrote: Gavin Flower-2 wrote I use 'id' for the primary key, and [tablename]_id for each foreign key, I always qualify my column references in SQL, but I would never use SELECT * when selecting from more than one table. Cheers, Gavin On 30/0

Re: [GENERAL] Why are stored procedures looked on so negatively?

On 02/08/13 08:24, Kevin Grittner wrote: [...] When working as a consultant, one client was doing everything client-side and engaged me to fix some performance problems. In one case a frequently run query was taking two minutes. As a stored procedure the correct results were returned in two s

Re: [GENERAL] Replication Postgre > Oracle

On 07/08/13 20:24, BOUVARD Aurélien wrote: Hi all, My compagny would like to configure replication between PostgreSQL 9.2.4 and Oracle Database (11g and maybe 12c soon). We saw that/Postgres Plus Database Replication /could be a good solution for us.// // We also thank to develop a soluti

Re: [GENERAL] SORT and Merge Join via Index

On 14/08/13 12:02, Robert James wrote: I noticed that when I have an index on (a,b) of table t, and I do an SELECT * FROM t ORDER BY a ASC, it doesn't use the index. When I create a new index of only a, it does use the index. Why is that? And, more importantly, when I do a query involving a me

Re: [GENERAL] SELECT x'00000000F'::int leading zeros causes "integer out of range"

On 25/02/17 08:39, John McKown wrote: On Fri, Feb 24, 2017 at 1:25 PM, David G. Johnston mailto:david.g.johns...@gmail.com>>wrote: On Friday, February 24, 2017, Tom Lane mailto:t...@sss.pgh.pa.us>> wrote: Justin Pryzby writes: > Is this expected behavior ? >

Re: [GENERAL] Python versus Other Languages using PostgreSQL

Hi Paul, See comments at the end... On 10/05/17 08:00, Paul Hughes wrote: Thank you all for taking the time to answer my questions. I've been out of the programming world for a long time, so I am back to being a newbie. Even if you stay in the game, technology changes - so one has to keep lea

Re: [GENERAL] Top posting....

On 12/05/17 05:04, Francisco Olarte wrote: Slightly unrelated... On Wed, May 10, 2017 at 11:21 PM, Gavin Flower wrote: It is normal on this list not to top post, but rather to add comments at the end (so people can see the context) - though interspersed comments in the body of the text is

Re: [GENERAL] column names and dollar sign

On 18/05/17 11:32, Neil Anderson wrote: "Armand Pirvu (home)" writes: Ran into the following statement CREATE TABLE test( Date$ date, Month_Number$ int, Month$ varchar(10), Year$ int ); A strange naming convention. It has a whiff of Visual Basic Type Characters about it

Re: [GENERAL] column names and dollar sign

On 18/05/17 11:59, John R Pierce wrote: On 5/17/2017 4:51 PM, Gavin Flower wrote: Variables ending in '$' date back to at least the early days of BASIC - long before the spectre of Microsoft loomed large, let alone 'Visual Basic'! I note even INT fields have $ names th

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On 14/09/17 15:29, Yogesh Sharma wrote: Dear All, Thanks in advance. We are using Postgres 8.1.18 version. In Postgres log, we found below logs. –- CONTEXT:writing block 0 of relation 1664/0/1260 ERROR: could not write block 0 of relation 1664/0/1260: Bad address --

Re: [GENERAL] Postgres DB is failed due to pg_Xlog is continues full.

On 14/09/17 16:11, Yogesh Sharma wrote: >>What you could do is copying its contents to a large disk, and then allow it to recover from the crash.  I will copy the PGDATA into large disk. After that it is require to execute some specific command or automatically recovery will start? If any comma

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

On 15/09/17 06:15, Kenneth Marshall wrote: On Thu, Sep 14, 2017 at 07:11:19PM +0200, Rafal Pietrak wrote: 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 UPDATE

Re: [GENERAL] Finally upgrading to 9.6!

On 19/10/17 10:34, Don Seiler wrote: On Wed, Oct 18, 2017 at 4:17 PM, Vik Fearing mailto:vik.fear...@2ndquadrant.com>> wrote: On 10/18/2017 08:17 PM, Don Seiler wrote: > I disagree with this. It isn't my company's business to test the > Postgres software in development, as much as

Re: [GENERAL] Call for design: PostgreSQL mugs

On 04/09/13 22:47, Eduardo Morras wrote: On Wed, 04 Sep 2013 00:08:52 +0200 Andreas 'ads' Scherbaum wrote: PostgreSQL folks! We are looking for the next big thing. Actually, it's a bit smaller: a new design for mugs. So far we had big blue elephants, small blue elephants, frosty elephants, wh

Re: [pgeu-general] [GENERAL] Call for design: PostgreSQL mugs

On 05/09/13 08:40, patrick keshishian wrote: On 9/4/13, Andreas 'ads' Scherbaum wrote: On 09/04/2013 10:17 PM, Stefan Kaltenbrunner wrote: On 09/04/2013 10:13 PM, Marc Balmer wrote: Am 04.09.13 22:02, schrieb Gavin Flower: On 04/09/13 22:47, Eduardo Morras wrote: On Wed, 04 Sep

Re: [GENERAL] Call for design: PostgreSQL mugs

On 09/09/13 01:27, Andreas 'ads' Scherbaum wrote: On 09/04/2013 12:08 AM, Andreas 'ads' Scherbaum wrote: PostgreSQL folks! We are looking for the next big thing. Actually, it's a bit smaller: a new design for mugs. So far we had big blue elephants, small blue elephants, frosty elephants, white

Re: [GENERAL] Call for design: PostgreSQL mugs

On 10/09/13 13:21, John R Pierce wrote: On 9/9/2013 5:39 PM, Sam Hahn wrote: How about * "Postgres -the Linux of Data" (or) * "The Linux of DBs" ugh no.if anything, Mysql is the Linux of data. PostgreSQL is more like the BSD of Data. -- john r pierce

Re: [GENERAL] Forms for entering data into postgresql

On 12/10/13 05:06, Steve Atkins wrote: On Oct 11, 2013, at 8:57 AM, Bret Stern wrote: My interpretation of "Forms these days are written in HTML" means most interfaces are web front ends to the cloud. Not a GUI framework. Yup. But embedding an HTML renderer in your desktop app does allow y

Re: [GENERAL] Preserving the source code of views

On 20/10/13 16:38, Brian Crowell wrote: Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our in-house systems. I've been really impressed with it so far, and I'm eager to try it with our data sets. I've run across one thing that would make a transfer difficult. Postgres doe

Re: [GENERAL] Connection pooling

On 31/10/13 03:13, si24 wrote: I have geoserver connected to a postgres database and so far I have been reading that I would need some sort of connection pooling but how do I go about doing that when it seems most of the connection pooling aplications/programs seem to only run on linux. I have a

Re: [GENERAL] Connection pooling

On 01/11/13 00:10, Rémi Cura wrote: Hey, I might be completly wrong, but when you say "get the connections to close if they are not being used", I'd say that it is a bad client design to not close a connection when it doesn't need it anymore. The client should retrieve the data or close when n

Re: [GENERAL] new line in psotgres

On 10/11/13 02:38, Alban Hertroys wrote: On Nov 9, 2013, at 12:08, abdullatheef wrote: >you can include newline in postgre using literal E > >create table table (text varchar(50)); >insert into Table (text) values (E'This is the first part \\n And this is >the second'); Or like this: developm

Re: [GENERAL] new line in psotgres

On 10/11/13 09:18, Gavin Flower wrote: On 10/11/13 02:38, Alban Hertroys wrote: On Nov 9, 2013, at 12:08, abdullatheef wrote: >you can include newline in postgre using literal E > >create table table (text varchar(50)); >insert into Table (text) values (E'This is the first

Re: [GENERAL] Postgres as In-Memory Database?

On 18/11/13 12:53, Stefan Keller wrote: Hi Martijn 2013/11/17 Martijn van Oosterhout > wrote: > If your dataset fits in memory then the problem is trivial: any decent > programming language provides you with all the necessary tools to deal > with data purely in memory

Re: [GENERAL] to_json(now()) result doesn't have 'T' separator

On 21/12/13 15:27, Joe Van Dyk wrote: On Fri, Dec 20, 2013 at 4:24 PM, Joe Van Dyk > wrote: On Fri, Dec 20, 2013 at 4:18 PM, Joe Van Dyk mailto:j...@tanga.com>> wrote: # select to_json(now()); to_json

Re: [GENERAL] Why is wal_writer_delay limited to 10s?

On 28/12/13 02:01, Clemens Eisserer wrote: Hi, Just to be curious, why is wal_writer_delay limited to 10s? I am using postgresql in an embedded environment where every 10s sensor values are logged and even with "synchronous_commit = off" and wal_writer_delay=1 this burns quite a lot of nand

Re: [GENERAL] help interpreting pg_stat_user_index view values

On 06/01/14 11:08, Sergey Konoplev wrote: [...] An index might be considered as useless when there were no idx scans for the significantly long period. However it might be non-trivial to define this period. Eg. one have a query building an annual report that uses this index and the period here

Re: [GENERAL] Need Help to implement Proximity search feature

Please see the comment at the bottom of this post. On 16/01/14 22:52, itishree sukla wrote: Thanks for your reply, i am totally new to Postgis. At least you've got into it, I keep meaning do do so myself. we have Database, but not ready for Geocode use. what i understood from different blo

Re: [GENERAL] Transparent exchange BDE from Oracle to PostgreSQL

On 03/02/14 01:18, Edson Richter wrote: Em 02/02/2014 04:24, John R Pierce escreveu: On 2/1/2014 4:26 PM, Adrian Klaver wrote: On 02/01/2014 03:26 PM, John R Pierce wrote: On 2/1/2014 3:18 PM, Edson Richter wrote: It is possible to put a PostgreSQL database in substitution of a Oracle databas

Re: [GENERAL] Ordering Results by a Supplied Order

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 anything really that requires an ordering t

Re: [GENERAL] UTF-8 collation on Windows?

On 20/02/14 10:28, Adrian Klaver wrote: On 02/19/2014 01:21 PM, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 2:45 AM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Have you tried it? Note that the locale name is different then the one Linux. On Linux it is en_US. What

Re: [GENERAL] UTF-8 collation on Windows?

On 21/02/14 02:04, Dev Kumkar wrote: On Thu, Feb 20, 2014 at 3:04 AM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: Upgrade servers to Linux? :-P Actually that's not the solution but running away from it. There is a heavy footprint of customers and huge market

Re: [GENERAL] Approach to Data Summary and Analysis

On 16/04/14 13:10, Robert DiFalco wrote: 1. >500K rows per day into the calls table. 2. Very rarely. The only common query is gathering users that have not been called "today" (along with some other qualifying criteria). More analytical queries/reports are done for internal use and it is not e

Re: [GENERAL] Arduino SQL Connector

On 18/04/14 16:27, Steve Spence wrote: On 4/18/2014 12:21 AM, John R Pierce wrote: personal opinion: I don't think a terminal device like a PC or an embedded system should be talking directly to SQL at all. instead, they should be talking to an application server which implements the "busi

Re: [GENERAL] Disable an index temporarily

On 20/04/14 20:09, Torsten Förtsch wrote: On 20/04/14 03:02, Sergey Konoplev wrote: Thanks for you reply. an index can be INVALID (pg_index.indisvalid=false). I want to temporarily disable an index so that it won't be used to access data but will still be updated. Can I simply set pg_index.i

Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

On 12/05/14 06:09, Adrian Klaver wrote: On 05/11/2014 10:17 AM, Ravi Roy wrote: Thanks a lot Tom, it worked by putting off the read only mode to off before changing the password and putting it on again. SET default_transaction_read_only = off; Worked for me.. It works but the point Tom was

Re: [GENERAL] Re: Creating a role with read only privileges but user is allowed to change password

On 12/05/14 08:33, David G Johnston wrote: ​ I​ suggest that you move the password to a separate table (my_role_password) with 2 columns: 1. my_role_id 2. password. This way you can make the my_role table totally unalterable by the user, yet they can change thei

Re: [GENERAL] Search for lists

[...] > I need to search a table to find sets of rows that have a column matching > itself for the whole set and another column matching row for row with a > list I am going to supply. The result I should receive should be value of > the column that matches itself. [...] How about: DROP TAB

Re: [GENERAL] unique across two tables

), is_aliasboolean DEFAULT true NOT NULL, "name" character varying(50) NOT NULL, UNIQUE (country_region_fk, "name") ); Cheers, Gavin Flower

Re: [GENERAL] An amusing MySQL weakness--not!

On 26/06/11 16:44, Michael Nolan wrote: Earlier today I was working on a MySQL database (not by choice, I assure you), and I typed a statement like this: Update tablexyz set field1 = '15' where field2 - 20; The '-' was supposed to be an equal sign, but MySQL executed it anyway. (Field2 is an

Re: [GENERAL] unique across two tables

On 23/06/11 23:28, Tarlika Elisabeth Schmitz wrote: Hello Gavin, On Wed, 22 Jun 2011 20:53:19 +1200 Gavin Flower wrote: [...] This design ensures that: names of towns are unique within a given country and>region. Note you will still need business logic, in a trigger or some such, to ens

Re: [GENERAL] Finding latest record for a number of groups in an INSERT-only table

On 05/07/11 11:48, Daniel Farina wrote: This is basically exactly the same as http://archives.postgresql.org/pgsql-sql/2008-10/msg9.php; I'm just asking again, to see if thinking on the problem has changed: The basic problem, restated, is one has a relation with tuples like this: (key, rece

Re: [HACKERS] [GENERAL] Creating temp tables inside read only transactions

On 08/07/11 18:21, Darren Duncan wrote: Jeff Davis wrote: On Thu, 2011-07-07 at 20:56 -0700, Darren Duncan wrote: When you create a temporary table, PostgreSQL needs to add rows in pg_class, pg_attribute, and probably other system catalogs. So there are writes, which aren't possible in a read

Re: [GENERAL] Programmer ( Postgres), Milwaukee - offsite-Remote - onsite

On 19/07/11 09:58, MS Rao wrote: *Programmer ( Postgres), Milwaukee -- offsite-Remote - onsite* We are looking for Postgres skilled programmer with the following skills: Skills: Strong in Postgres SQl , Set up of database, Linux RDBMS expert and strong in design Possible to work onsite /offsi

Re: [GENERAL] Book

On 21/07/11 10:45, Andrej wrote: Thanks all - book ordered :} I wonder how much Greg has spent in bribes??? :-) More seriously: I intend going through my copy in depth to get a better unbderstanding of pg. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

Re: [GENERAL] replacing a subquery with an outer join?

On 22/07/11 13:20, Chris Curvey wrote: in an earlier thread, the following query was submitted: SELECT COUNT(*) FROM q WHERE NOT EXISTS (SELECT 1 FROM t AS t WHERE t.mid = q.mid); and as part of his answer, David Johnson

Re: [GENERAL] Implementing "thick"/"fat" databases

On 23/07/11 12:05, John R Pierce wrote: On 07/22/11 4:11 PM, Darren Duncan wrote: Karl Nack wrote: I've been following a few blogs (http://database-programmer.blogspot.com/, http://thehelsinkideclaration.blogspot.com/) that make a very compelling argument, in my opinion, to move as much busine

Re: [GENERAL] Implementing "thick"/"fat" databases

On 24/07/11 17:51, Chris Travers wrote: I was thinking similar thoughts, but you not only beat me to it, you made some good points I had not thought of! The only thing I can think of adding: is that it would be good to lock down the database so that only the middleware can access it, everything

Re: [GENERAL] Finding referecing and referenced tables, adaptation from David Fetter's solution

On 30/07/11 10:45, bricklen wrote: [...] CREATE OR REPLACE VIEW table_dependencies AS ( WITH RECURSIVE t AS ( SELECT c.oid AS origin_id, c.oid::regclass::text AS origin_table, c.oid AS referencing_id, c.oid::regclass::text AS referencing_table, c2

Re: [GENERAL] Finding referecing and referenced tables, adaptation from David Fetter's solution

On 30/07/11 10:45, bricklen wrote: A coworker of mine* was looking for a way to quickly and easily be able to tell which tables were referencing particular table(s) she wanted to load (for unit testing). Using the examples from David Fetter**, she submitted a revised version that seems to work qu

Re: [GENERAL] Finding referecing and referenced tables, adaptation from David Fetter's solution

On 30/07/11 10:45, bricklen wrote: A coworker of mine* was looking for a way to quickly and easily be able to tell which tables were referencing particular table(s) she wanted to load (for unit testing). Using the examples from David Fetter**, she submitted a revised version that seems to work qu

Re: [GENERAL] Finding referecing and referenced tables, adaptation from David Fetter's solution

On 31/07/11 21:42, Alban Hertroys wrote: On 30 Jul 2011, at 12:17, Gavin Flower wrote: CREATE OR REPLACE VIEW table_dependencies AS ( WITH RECURSIVE t AS ( SELECT c.oid AS origin_id, c.oid::regclass::text AS origin_table, c.oid AS referencing_id, c.oid

Re: [GENERAL] Finding referecing and referenced tables, adaptation from David Fetter's solution

On 31/07/11 21:36, Alban Hertroys wrote: On 30 Jul 2011, at 13:49, Gavin Flower wrote: On 30/07/11 10:45, bricklen wrote: [...] CREATE OR REPLACE VIEW table_dependencies AS ( WITH RECURSIVE t AS ( SELECT c.oid AS origin_id, c.oid::regclass::text AS origin_table

Re: [GENERAL] Using Postgresql as application server

On 14/08/11 05:12, David Johnston wrote: On Aug 13, 2011, at 11:57, c k wrote: Dear Postgres users, from last few months I am reading and searching for can postgresql used as application server? As postgresql supports many languages like pl/perl, pl/python etc, supports dblink like function

Re: [GENERAL] Has Pg 9.1.0 been released today?

On 12/09/11 20:44, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:40, Gavin Flower wrote: On 12/09/11 20:31, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:10, Toby Corkindale wrote: http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: "Release Date:

Re: [GENERAL] Has Pg 9.1.0 been released today?

On 12/09/11 20:31, Magnus Hagander wrote: On Mon, Sep 12, 2011 at 10:10, Toby Corkindale wrote: http://www.postgresql.org/docs/9.1/static/release-9-1.html contains the line: "Release Date: 2011-09-12" *bounces excitedly* Has the release candidate gone final today? Not yet. But we are planni

Re: [GENERAL] Has Pg 9.1.0 been released today?

On 13/09/11 01:58, Devrim GÜNDÜZ wrote: On Mon, 2011-09-12 at 20:40 +1200, Gavin Flower wrote: Not yet. But we are planning to put it out, and we need to load the website documentation ahead of time. Then how come was put on the download page over 24 hours ago? I already have it installed

Re: [GENERAL] ts_rank error/result of '1e-020'

On 07/10/11 10:56, Henry Drexler wrote: On Thu, Oct 6, 2011 at 4:37 PM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: On 07/10/11 01:40, Henry Drexler wrote: I have a workaround to the error/result, but am wondering what the result of ts_rank of &

Re: [GENERAL] Selecting All Columns Associated With Maximum Value of One Column

On 07/10/11 03:03, Rich Shepard wrote: On Thu, 6 Oct 2011, David Johnston wrote: Missing the FROM before chemistry D'oh! Obviously not yet sufficiently cafinated this morning. [...] You just infringed my patent on making unprovoked stupid mistakes in posts to the Pg mailing lists! I th

Re: [GENERAL] how to find primary key field name?

On 12/10/11 11:54, J.V. wrote: If I have a table name, I know how to find the primary key constraint name, but see no way to find the primary key field name. select constraint_name from information_schema.tabale_constraints where table_name = and constraint_type = 'PRIMARY KEY'; will return

Re: [GENERAL] how to find primary key field name?

On 12/10/11 11:54, J.V. wrote: If I have a table name, I know how to find the primary key constraint name, but see no way to find the primary key field name. select constraint_name from information_schema.tabale_constraints where table_name = and constraint_type = 'PRIMARY KEY'; will return

Re: [GENERAL] Dynamic sql real examples

On 13/10/11 18:44, Gabriel Filipiak wrote: 2011/10/13 Gavin Flower <mailto:gavinflo...@archidevsys.co.nz>> On 13/10/11 18:35, Gabriel Filipiak wrote: Thx Gavin, any other suggestions from others? Gabe 2011/10/13 Gavin Flower mailto:gavinflo...@archidev

Re: [GENERAL] Dynamic sql real examples

On 13/10/11 17:55, Gabriel Filipiak wrote: Hi all, I have lately learned what is dynamic sql and one of the most interesting features of it to me is that we can use dynamic columns names and tables. But I cannot think about useful real life examples. The only one that came into my mind is sta

Re: [GENERAL] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

On 24/05/12 05:09, Lonni J Friedman wrote: On Wed, May 23, 2012 at 9:37 AM, Tom Lane wrote: Lonni J Friedman writes: After banging my head on the wall for a long time, I happened to notice that khugepaged was consuming 100% CPU every time autovacuum was running. I did: echo "madvise"> /sys

Re: [GENERAL] Re: significant performance hit whenever autovacuum runs after upgrading from 9.0 -> 9.1

On 24/05/12 08:18, Lonni J Friedman wrote: On Wed, May 23, 2012 at 12:36 PM, Gavin Flower wrote: On 24/05/12 05:09, Lonni J Friedman wrote: On Wed, May 23, 2012 at 9:37 AM, Tom Lane wrote: Lonni J Friedman writes: After banging my head on the wall for a long time, I happened to notice

Re: [GENERAL] Feature discussion: Should syntax errors abort a transaction?

On 20/06/12 01:35, Rafal Pietrak wrote: On Tue, 2012-06-19 at 19:06 +0800, Craig Ringer wrote: On 06/19/2012 02:20 PM, Tom Lane wrote: So you're suggesting that "SELECT 1/0;" should terminate a transaction, but "SELECT 1//0;" should not? How about "ROLBACK;"? It gets pretty squishy pretty fa

Re: [GENERAL] Smaller data types use same disk space

On 26/07/12 04:09, McGehee, Robert wrote: Very interesting points. Thanks for the documentation link and the point about alignment. As a test of Tom's suggestion to group smallints together to avoid alignment problems, I changed the column order from smallint, date, smallint, integer, real TO

Re: Messy data models (Re: [GENERAL] Visualize database schema)

On 18/08/12 04:33, David Johnston wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Wolfgang Keller Sent: Friday, August 17, 2012 9:08 AM To: pgsql-general@postgresql.org Subject: Messy data models (Re: [GENERAL]

Re: [GENERAL] Schemas vs partitioning vs multiple databases for archiving

On 18/08/12 20:05, Bartel Viljoen wrote: [...] I'm in the design faze of a new GUI and DB layout, what are my options. [...] I think you meant phase! (Spell checkers can be quite stupid!) Cheers, Gavin

Re: [GENERAL] Schemas vs partitioning vs multiple databases for archiving

On 19/08/12 17:50, Chris Travers wrote: On Sat, Aug 18, 2012 at 9:30 PM, Gavin Flower mailto:gavinflo...@archidevsys.co.nz>> wrote: On 18/08/12 20:05, Bartel Viljoen wrote: [...] I’m in the design faze of a new GUI and DB layout, what are my options. [...]

<    1   2   3   >