Re: [GENERAL] Proper relational database?

Well, this hits very close to my feelings in several respects. I don't often bring this up, because I don't generally feel like "I loathe SQL" is quite the thing to say in a community called "PostgreSQL". :-) Or, "I really love this project... can we change its direction entirely?" But yeah,

Re: [GENERAL] different empty array syntax requirements

Scott Ribe writes: > How is that one pgsql build (both are 9.5.2) has different casting behavior > for empty arrays: > pedcard=# select (ARRAY[])::text[]; > ERROR: cannot determine type of empty array > LINE 1: select (ARRAY[])::text[]; > ^ > HINT:

Re: [GENERAL] different empty array syntax requirements

Looks like one has the appropiate cast operator, while the other hasn't. Have you tried doing the same, on both server, on an empty database created from template0? Regards, Alvaro Aguayo Jefe de Operaciones Open Comb Systems E.I.R.L. Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 

[GENERAL] different empty array syntax requirements

How is that one pgsql build (both are 9.5.2) has different casting behavior for empty arrays: ericmj=# select ARRAY[]::text[]; array --- {} (1 row) ericmj=# select (ARRAY[])::text[]; array --- {} (1 row) --VS-- pedcard=# select ARRAY[]::text[]; array --- {} (1 row)

Re: [GENERAL] Is it possible to call Postgres directly?

That's OK. I can set things up so that the Thrift or Web servers call the Andl runtime directly, pass generated SQL queries in through libpq, and call the Andl runtime recursively from the plandl handler as needed. It's just one more API to deal with. Thanks for the suggestion, but I already did

Re: [GENERAL] Update field to a column from another table

The problem was a trigger in my DB, when I disabled it the data started to be updated. Lucas

Re: [GENERAL] disable ipv6?

On 04/19/2016 06:35 PM, Jonathan Vanasco wrote: I'm running postgresql on ubuntu. the 9.4 branch from postgresql.org I think the only way to disable ipv6 is to edit postgresql.conf and explicitly state localhost in ipv4 as follows - listen_addresses = 'localhost' + listen_addresses

Re: [GENERAL] disable ipv6?

On 20/04/16 13:35, Jonathan Vanasco wrote: I'm running postgresql on ubuntu. the 9.4 branch from postgresql.org I think the only way to disable ipv6 is to edit postgresql.conf and explicitly state localhost in ipv4 as follows - listen_addresses = 'localhost' + listen_addresses =

Re: [GENERAL] Update field to a column from another table

On 04/21/2016 11:52 AM, drum.lu...@gmail.com wrote: So when I run: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM junk.ja_test t1 WHERE t2.id = t1.id AND t2.time_job IS DISTINCT FROM t1.time_job; I get: UPDATE 2202 So I check the data

Re: [GENERAL] Proper relational database?

Just as a curiosity, what do you think of ANDL? http://www.andl.org/welcome-to-andl/ The developer has been posting some questions here about interfacing it to PostgreSQL. But he doesn't just want to do a "translate the ANDL language to SQL language" On Thu, Apr 21, 2016 at 3:36 PM, Guyren Howe

Re: [GENERAL] Proper relational database?

On Thu, Apr 21, 2016 at 1:36 PM, Guyren Howe wrote: > Anyone familiar with the issue would have to say that the tech world would > be a significantly better place if IBM had developed a real relational > database with an elegant query language rather than the awful camel of a >

Re: [GENERAL] Proper relational database?

On Thu, Apr 21, 2016 at 1:36 PM, Guyren Howe wrote: > If I had a few $million to spend in a philanthropical manner, I would hire > some of the best PG devs to develop a proper relational database server. > Probably a query language that expressed the relational algebra in a >

Re: [GENERAL] zero length string to zero

On 04/20/2016 05:31 AM, Shadi Mal wrote: Hi, I get zero length string in my result but I want to have zero value instead. what can I do? my query is: SELECT unreadmandatory.id , Count(unreadmandatory.course) AS nbrofmandatoryleft FROM

[GENERAL] Proper relational database?

Anyone familiar with the issue would have to say that the tech world would be a significantly better place if IBM had developed a real relational database with an elegant query language rather than the awful camel of a thing that is SQL. If I had a few $million to spend in a philanthropical

[GENERAL] Announcing BedquiltDB: A json doc-store built on PostgreSQL

Hi all, For the last year I’ve been working on building a vaguely mongoldb-inspired API on top of the Postgres jsonb column types. The result is BedquiltDB (http://bedquiltdb.github.io/). The Gist: BedquiltDB is a vaguely mongodb-inspired json store built on top of PostgreSQL's jsonb column

[GENERAL] Background worker with Listen

I am trying to create background worker which listens to notifications and do some work after receiving one. I got 2 problems: - Worker is receiving notifications from every channel not only the registered channel (in my case "foo") - Notifications are not logged in the

[GENERAL] zero length string to zero

Hi, I get zero length string in my result but I want to have zero value instead. what can I do? my query is: SELECT unreadmandatory.id, Count(unreadmandatory.course) AS nbrofmandatoryleft FROM unreadmandatory GROUP BY unreadmandatory.id

[GENERAL] disable ipv6?

I'm running postgresql on ubuntu. the 9.4 branch from postgresql.org I think the only way to disable ipv6 is to edit postgresql.conf and explicitly state localhost in ipv4 as follows - listen_addresses = 'localhost' + listen_addresses = '127.0.0.1' can anyone confirm? -- Sent via

Re: [GENERAL] Update field to a column from another table

On Thursday, April 21, 2016, drum.lu...@gmail.com wrote: > So when I run: > > UPDATE ja_jobs t2 >> SET time_job = t1.time_job >> FROM junk.ja_test t1 >> WHERE t2.id = t1.id >> AND t2.time_job IS DISTINCT FROM t1.time_job; > > > I get: > > UPDATE 2202 > > So I check the data

Re: [GENERAL] Update field to a column from another table

So when I run: UPDATE ja_jobs t2 > SET time_job = t1.time_job > FROM junk.ja_test t1 > WHERE t2.id = t1.id > AND t2.time_job IS DISTINCT FROM t1.time_job; I get: UPDATE 2202 So I check the data by doing: select * FROM public.ja_jobs WHERE id = 14574527 And the "time_job" field is null

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 9:55 AM, Melvin Davidson wrote: > Please, just ONE LOGICAL VALID argument, not speculation. Otherwise, stop > with the nay saying. I think you should look seriously at the suggestion offered of using an event trigger to get what you desire here. I

Re: [GENERAL] Initdb --data-checksums by default

On Thu, Apr 21, 2016 at 9:00 AM, Alex Ignatov wrote: > Ms Windows doesnt have ZFS support. AIX also doesnt. Z/OS also. Any other > commercial Linux distros don't have ZFS support. Yes you can compile it and > use on production but... > But PG runs on the above OS, but

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thursday, April 21, 2016 12:55:00 PM EDT Melvin Davidson wrote: > WHY am I being vilified for making a simple request? How is it that > developers proceed with other enhancements, yet so much negative attention > is being given to my request because of unjustified fear that something bad > will

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 11:59 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Apr 21, 2016 at 8:18 AM, Melvin Davidson > wrote: > >> On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver < >> adrian.kla...@aklaver.com> wrote: >> >>> On 04/21/2016 07:53 AM,

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

On Thu, Apr 21, 2016 at 1:39 PM Geoff Winkless wrote: > On 21 April 2016 at 17:08, David G. Johnston > wrote: > > I have little experience (and nothing practical) with columnar store but > at > > a high level I don't see the point. I would hope

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

On 21 April 2016 at 17:08, David G. Johnston wrote: > I have little experience (and nothing practical) with columnar store but at > a high level I don't see the point. I would hope that anyone interested in > working on a columnar store database would pick an existing

[GENERAL] On the building of a PostgreSQL cluster

Hey guys, I recently gave a talk on building a PostgreSQL cluster, giving accounts of a few stories of how things went wrong. Here's the talk: https://www.youtube.com/watch?v=OzoyRv_7fEk And here are the slides: https://speakerdeck.com/srihari/on-the-building-of-a-postgres-cluster I'd love to

Re: [GENERAL] Update field to a column from another table

Please don't top-post. > > -Original Message- > > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of > > drum.lu...@gmail.com > > Sent: Donnerstag, 21. April 2016 07:10 > > To: Postgres General > > Subject:

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

On Thu, Apr 21, 2016 at 3:08 AM, Bráulio Bhavamitra wrote: > Hi all, > > I'm finally having performance issues with PostgreSQL when doing big > analytics queries over almost the entire database of more than 100gb of > data. > > And what I keep reading all over the web is

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 8:18 AM, Melvin Davidson wrote: > On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver > wrote: > >> On 04/21/2016 07:53 AM, Melvin Davidson wrote: >> >> >>> "Whether that is worthy or not is the point of your request and

Re: [GENERAL] setting time zone in a function

Steve Rogerson writes: > I wonder what counts as a valid time zone http://www.postgresql.org/docs/9.5/static/datatype-datetime.html#DATATYPE-TIMEZONES > ... I wasn't expecting this: > # set timezone = '==2.77'; > SET > # select now(); > now >

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver wrote: > On 04/21/2016 07:53 AM, Melvin Davidson wrote: > > >> "Whether that is worthy or not is the point of your request and really >> depends on more input." >> Correct. And that is what I am looking for. Stating

Re: [GENERAL] setting time zone in a function

On 21/04/16 14:47, Tom Lane wrote: > Steve Rogerson writes: >> I want the time zone if a function a bit like ... >> CREATE OR REPLACE FUNCTION >>short_name (timestamp with time zone, varchar) RETURNS varchar AS $$ >> DECLARE >> ... >> BEGIN >>

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On 04/21/2016 07:53 AM, Melvin Davidson wrote: "Whether that is worthy or not is the point of your request and really depends on more input." Correct. And that is what I am looking for. Stating obscure corner cases does not rule out the need for an enhancement. If it did, there would be no

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 10:44 AM, Adrian Klaver wrote: > On 04/21/2016 07:30 AM, Melvin Davidson wrote: > >> >> >> > >> >> "Just one example of why that assertion does not hold:" >> >> I fail to see your point >> >> "That is easy. Compare dumps of the current schema

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On 04/21/2016 07:30 AM, Melvin Davidson wrote: "Just one example of why that assertion does not hold:" I fail to see your point "That is easy. Compare dumps of the current schema against the official schema." So your solution is to do a dump and then grep for anomalies? How is that

Re: [GENERAL] error while installing auto_explain contrib module

On 04/21/2016 07:13 AM, Sachin Kotwal wrote: On Thu, Apr 21, 2016 at 7:20 PM, Adrian Klaver > wrote: On 04/21/2016 02:23 AM, Sachin Kotwal wrote: Hi Tom, Thanks for reply. On Tue, Apr 19, 2016 at 7:40

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 10:08 AM, Karsten Hilbert wrote: > On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote: > > > "and what about user objects added to a database which is > > then used as a template for creating another DB ?" > > > > This existence of

Re: [GENERAL] error while installing auto_explain contrib module

On Thu, Apr 21, 2016 at 7:20 PM, Adrian Klaver wrote: > On 04/21/2016 02:23 AM, Sachin Kotwal wrote: > >> Hi Tom, >> >> Thanks for reply. >> >> On Tue, Apr 19, 2016 at 7:40 PM, Tom Lane > > wrote: >> >> Sachin Kotwal

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote: > "and what about user objects added to a database which is > then used as a template for creating another DB ?" > > This existence of objects that are part of the default schema is NOT a > problem. Developers and users should

Re: [GENERAL] Invalid data read from synchronously replicated hot standby

On 04/21/2016 01:05 AM, martin.kamp.jen...@schneider-electric.com wrote: Hi, We are getting invalid data when reading from a synchronously replicated hot standby node in a 2-node setup. To better understand the situation, we have created a document that provides an overview. We are hoping that

Re: [GENERAL] Is it possible to call Postgres directly?

writes: > So the question is: Can a C program link to the Postgres DLL and call SPI > directly, rather than through a language function? No, there is no provision for non-server usage of the code. If that's what you're after you might be happier with SQLite or something similar.

Re: [GENERAL] error while installing auto_explain contrib module

On 04/21/2016 02:23 AM, Sachin Kotwal wrote: Hi Tom, Thanks for reply. On Tue, Apr 19, 2016 at 7:40 PM, Tom Lane > wrote: Sachin Kotwal > writes: > On Tue, Apr 19, 2016 at 7:02 PM, Adrian

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

"and what about user objects added to a database which is then used as a template for creating another DB ?" This existence of objects that are part of the default schema is NOT a problem. Developers and users should never have access to a template. The point is to be able to track down rogue

Re: [GENERAL] setting time zone in a function

Steve Rogerson writes: > I want the time zone if a function a bit like ... > CREATE OR REPLACE FUNCTION >short_name (timestamp with time zone, varchar) RETURNS varchar AS $$ > DECLARE > ... > BEGIN > SET LOCAL TIME ZONE $2; Nope, that

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

"Speaking blindly here but given that we now have event triggers I'm even more inclined to simply tell people to setup user-space tables and event triggers to do whatever they want. Is there any reason that combination cannot solve the problems being brought up? I get the desirability of having

Re: [GENERAL] Initdb --data-checksums by default

On 20.04.2016 23:28, Vick Khera wrote: On Wed, Apr 20, 2016 at 3:43 AM, Alex Ignatov > wrote: What do you think about defaulting --data-checksums in initdb? I think that ZFS storing my database files already does this and can

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Thu, Apr 21, 2016 at 3:11 AM, Karsten Hilbert wrote: > On Wed, Apr 20, 2016 at 07:09:24PM -0400, Melvin Davidson wrote: > > > There is also the situation of tables with limitited use. EG: > > history_mm, in which case it would facilitate dropping of tables that >

Re: [GENERAL] Invalid data read from synchronously replicated hot standby

On Thu, 21 Apr 2016 04:05 , wrote: > Hi, > > We are getting invalid data when reading from a synchronously replicated > hot standby node in a 2-node setup. To better understand the situation, we > have created a document that provides an overview. We

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

Hi Bráulio: On Thu, Apr 21, 2016 at 12:08 PM, Bráulio Bhavamitra wrote: > And what I keep reading all over the web is many databases switching to > columnar store (RedShift, Cassandra, cstore_fdw, etc) and having great > performance on queries in general and giant boosts

[GENERAL] setting time zone in a function

I want the time zone if a function a bit like ... CREATE OR REPLACE FUNCTION short_name (timestamp with time zone, varchar) RETURNS varchar AS $$ DECLARE ... BEGIN SET LOCAL TIME ZONE $2; SELECT to_char($1, 'TZ')::varchar ... END $$ LANGUAGE

Re: [GENERAL] error while installing auto_explain contrib module

Hi Tom, Thanks for reply. On Tue, Apr 19, 2016 at 7:40 PM, Tom Lane wrote: > Sachin Kotwal writes: > > On Tue, Apr 19, 2016 at 7:02 PM, Adrian Klaver < > adrian.kla...@aklaver.com> > > wrote: > >> Did you install the 9.3.11 server from source also? > >

Re: [GENERAL] Is it possible to call Postgres directly?

On 4/20/2016 10:59 PM, da...@andl.org wrote: So the question is: Can a C program link to the Postgres DLL and call SPI directly, rather than through a language function? Is there a way to launch a Thrift server or a Web server and call SPI directly? no, those can only be called from the

[GENERAL] Invalid data read from synchronously replicated hot standby

Hi, We are getting invalid data when reading from a synchronously replicated hot standby node in a 2-node setup. To better understand the situation, we have created a document that provides an overview. We are hoping that someone might be able to confirm whether or not the setup makes sense,

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 07:09:24PM -0400, Melvin Davidson wrote: > There is also the situation of tables with limitited use. EG: > history_mm, in which case it would facilitate dropping of tables that > are no longer needed after x amount of time. select * from pg_class where

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 05:17:20PM -0500, Kevin Grittner wrote: > if someone had been allowed to run ad hoc > reports or data cleanup on a database it was a quick way to look > for stray tables they may have generated to keep intermediate > results or exceptions, so we could follow up on

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 03:02:52PM -0700, Adrian Klaver wrote: > No one is arguing that slapping a new column on pg_class is not easy, just > that the implications of doing so requires a good deal of thought. The first > thing that comes to my mind(also in threads on --hackers) is what is the >

Re: [GENERAL] Function PostgreSQL 9.2

On Wed, Apr 20, 2016 at 03:55:50PM -0700, Adrian Klaver wrote: > >If I am following, this duplicates the information in > >companies.client_code_increment, in that they both return the last > >non-user code. Of course this assumes, as David mentioned, that the > >client is not

Re: [GENERAL] Is it possible to call Postgres directly?

> > I would like to be able to execute SQL queries by a direct call into > > Postgres, without going through either (1) the language call handler > > or (2) a 'wire' protocol. > > What would you consider to be "directly"? What advantage would you hope to > gain from a different API? The aim is