[GENERAL] next postgres version on Amazon RDS ?

Hello, has anyone some insight on when we can expect a newest 9.4.x Version on RDS ? Or knows a better forum to ask for this? I should upgrade an application DB currently on 9.3 that might go on RDS but I'm a bit reluctant to go only on 9.4.1 ... regards, Marc Mamin

Re: [GENERAL] dblink max per function

A restart of my system solved the matter. 2015-07-05 20:54 GMT+02:00 Peter Kroon : > Hi, > > I've got a function with 5 dblink select statement all to my local server > with the same connection string. > When one more dblink select statement is added the query fails. > Is there some kind of l

Re: [GENERAL] max number of locks

Hi Adrian, thanks for your reply, On 07/03/2015 02:52 PM, Adrian Klaver wrote: > On 07/03/2015 03:14 AM, Fabio Pardi wrote: >> Hi, >> >> >> while experimenting with number of locks, i found something I cannot >> understand. >> >> From what i can read in the documentation, at any one given time,

[GENERAL] Error prone compilation of stored procedure

What's the reason behind very "tolerant" error checking during stored procedure compilation? Why PostgreSQL allows using variable (j_var) that doesn't exists? It isn't column name or isn't declared anywhere. Like in example below: CREATE OR REPLACE FUNCTION test() RETURNS int AS $BODY$ BEGIN s

Re: [GENERAL] Error prone compilation of stored procedure

pinker wrote: > What's the reason behind very "tolerant" error checking during stored > procedure compilation? they are not compiled but interpreted at runtime. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect.

Re: [GENERAL] Error prone compilation of stored procedure

2015-07-06 12:08 GMT+02:00 pinker : > What's the reason behind very "tolerant" error checking during stored > procedure compilation? > Why PostgreSQL allows using variable (j_var) that doesn't exists? It isn't > column name or isn't declared anywhere. Like in example below: > > CREATE OR REPLACE F

Re: [GENERAL] Error prone compilation of stored procedure

Andreas Kretschmer-2 wrote > pinker < > pinker@ > > wrote:> What's the reason behind very "tolerant" error checking > during stored> procedure compilation?they are not compiled but interpreted > at runtime.Andreas Documentation says: during the compilation of a function

Re: [GENERAL] Error prone compilation of stored procedure

Pavel Stehule wrote > PLpgSQL doesn't check a identifiers inside embedded SQL before execution. > In this case j_var can be theoretically some SQL identifiers - the > possibility or impossibility is not know in function validation stage. > > It has some plus and minus points. The plus - there are

Re: [GENERAL] Error prone compilation of stored procedure

On Mon, Jul 06, 2015 at 04:52:52AM -0700, pinker wrote: > Another minus is that my colleagues which use to work on oracle think that > postgresql is at least one league below oracle. I find that people who are used to any one tool always point out how some other tool is deficient, even if they're

Re: [GENERAL] Error prone compilation of stored procedure

And are your colleagues offering to pay for an Oracle license? > On Jul 6, 2015, at 5:52 AM, pinker wrote: > > Pavel Stehule wrote >> PLpgSQL doesn't check a identifiers inside embedded SQL before execution. >> In this case j_var can be theoretically some SQL identifiers - the >> possibility or

Re: [GENERAL] unexpected data beyond EOF in block 260 of relation pg_tblspc

On 07/05/2015 10:15 PM, Mitu Verma wrote: Hi, Below alarm was raised at the system where postgres database was used.. --- BGWPOL22-00:/var/opt/mediation/MMStorage6/Server8/CXC1734739_R8J/storag

Re: [GENERAL] [pg_hba.conf] publish own Python application using PostgreSQL

On July 6, 2015 06:43:53 AM c.bu...@posteo.jp wrote: > On 2015-07-05 15:13 Jan de Visser wrote: > > You could set up a whole new server with a different $PGDATA on a > > different port. > > I (and the user) don't want to setup anything - that is the point. Well, you don't have to setup anything.

Re: [GENERAL] Error prone compilation of stored procedure

lup wrote > And are your colleagues offering to pay for an Oracle license? I would prefer to prove them it's not necessary :) -- View this message in context: http://postgresql.nabble.com/Error-prone-compilation-of-stored-procedure-tp5856699p5856734.html Sent from the PostgreSQL - general mail

Re: [GENERAL] Error prone compilation of stored procedure

2015-07-06 13:52 GMT+02:00 pinker : > Pavel Stehule wrote > > PLpgSQL doesn't check a identifiers inside embedded SQL before execution. > > In this case j_var can be theoretically some SQL identifiers - the > > possibility or impossibility is not know in function validation stage. > > > > It has s

Re: [GENERAL] database-level lockdown

Yes, I've tried to come up with guideline to enumerate tables used in each process, but it's not simple because it's python application calling pgsql functions that use other functions, so it's tricky for a developer re-using existing functions to enumerate the tables used for those. Even if ev

Re: [GENERAL] database-level lockdown

It's not necessary to commit at all costs, it can fail, just not due to serialization.. And the transaction can be something as simple as updating a field or inserting a record (with foreign keys which is one the serialization checks). On Sáb, Jul 4, 2015 at 7:23 , Adrian Klaver wrote: On

Re: [GENERAL] [pg_hba.conf] publish own Python application using PostgreSQL

Jan de Visser wrote: On July 6, 2015 06:43:53 AM c.bu...@posteo.jp wrote: On 2015-07-05 15:13 Jan de Visser wrote: You could set up a whole new server with a different $PGDATA on a different port. I (and the user) don't want to setup anything - that is the point. Well, you don't have to set

[GENERAL] to_tsvector() with hyphens

Hey everyone, I think it's great that the full text search parser breaks hyphenated words into multiple parts. I think this really could help, but something is not right. rasmas_hackathon=> select * from ts_debug( 'gn-foo' ); alias | description | token | dic

Re: [GENERAL] to_tsvector() with hyphens

Brian DeRocher writes: > But why does to_tsquery() AND them? > rasmas_hackathon=> select * from to_tsquery( 'gn-foo | bandage' ); > to_tsquery > > 'gn-foo' & 'gn' & 'foo' | 'bandag' > (1 row) Because what you're looking for is gn-fo

Re: [GENERAL] [pg_hba.conf] publish own Python application using PostgreSQL

On 07/05/2015 09:43 PM, c.bu...@posteo.jp wrote: On 2015-07-05 15:13 Jan de Visser wrote: You could set up a whole new server with a different $PGDATA on a different port. I (and the user) don't want to setup anything - that is the point. Then what you want is an embedded database, in other

[GENERAL] PgBouncer error - psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.643

Hi Guys; Can I please get some assistance: I have postgres server running on the localhost: -bash-4.2$ telnet localhost 5432 Trying ::1... Connected to localhost. Escape character is '^]'. -bash-4.2$ psql -U postgres psql (9.4.4) Type "help" for help. postgres=# \list

Re: [GENERAL] String match function required like utl_match in oracle

Thanks, It is somewhat useful. Jimit Amin On Sat, Jul 4, 2015 at 3:51 PM, Charles Clavadetscher < clavadetsc...@swisspug.org> wrote: > Or maybe even better: > > http://www.postgresql.org/docs/9.4/static/fuzzystrmatch.html > > Search for Levenshtein. > > Bye > Charles > > On 7/4/2015 11:50, Jimi

Re: [GENERAL] PgBouncer error - psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ali Aktar Sent: Monday, July 06, 2015 11:04 AM To: pgsql-general@postgresql.org Subject: [GENERAL] PgBouncer error - psql: could not connect to server: No such file or directory Is the server runni

[GENERAL] Average New Users Per DOW

I'm not sure how to create a result where I get the average number of new users per day of the week. My issues are that days that did not have any new users will not be factored into the average, giving an overinflated result. This is what I started with: WITH userdays AS (SELECT u.created::DAT

Re: [GENERAL] PgBouncer error - psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL

Cursory observations below... On Mon, Jul 6, 2015 at 11:04 AM, Ali Aktar wrote: > Hi Guys; > > Can I please get some assistance: > > I have postgres server running on the localhost: > -bash-4.2$ telnet localhost 5432 > Trying ::1... > Connected to localhost. > Escape character is '^]'. > > -bash

Re: [GENERAL] Average New Users Per DOW

> I'm not sure how to create a result where I get the average number of > new users per day of the week. My issues are that days that did not > have any new users will not be factored into the average This is a pretty common problem with time-series queries when there is sparse data. My go-to so

Re: [GENERAL] Average New Users Per DOW

Thanks Paul, I guess I'm not sure how a generate_series between 0 to 6 would solve this problem. Wouldn't I have to generate a series based on the date range (by day) and then group by DOW _after_ that? Can you give me an example of how I'd do it with a series based on 0 to 6? On Mon, Jul 6, 2015

Re: [GENERAL] Average New Users Per DOW

On Mon, Jul 6, 2015 at 2:04 PM, Robert DiFalco wrote: > Wouldn't I have to generate a series based on the date range (by day) and > then group by DOW _after_ that? > ​You are correct.​ WITH userdays (dow, user_count) AS ( existing_query, more or less ) ​, day_counts (dow, count_of_days) AS ( SE

Re: [GENERAL] Average New Users Per DOW

Thanks Paul, I guess I'm not sure how a generate_series between 0 to 6 would solve this problem. Wouldn't I have to generate a series based on the date range (by day) and then group by DOW _after_ that? Can you give me an example of how I'd do it with a series based on 0 to 6? Looks like David J

Re: [GENERAL] Average New Users Per DOW

Paul, I'm sure I'm missing something but it seems like your approach will not work. It's because the LEFT OUTER JOIN is on the numeric day of the week. So if you had this query going over weeks or months of data wouldn't you have the same issue with the days that had no new users not being factored

Re: [GENERAL] Average New Users Per DOW

Please follow list conventions and either respond inline or bottom-post. On Mon, Jul 6, 2015 at 3:30 PM, Robert DiFalco wrote: > Paul, I'm sure I'm missing something but it seems like your approach will > not work. It's because the LEFT OUTER JOIN is on the numeric day of the > week. So if you h

Re: [GENERAL] Average New Users Per DOW

> > ​I am fairly certain this does not give you the correct results. > Specifically, the minimum value for each cDate is going to be 1 since > count(*) counts NULLs. count(u) should probably work. > ​ > > Yes you are right, I forgot to change COUNT(*) to COUNT(id), as you mention COUNT(u.*) will a

Re: [GENERAL] Average New Users Per DOW

On Mon, Jul 6, 2015 at 4:40 PM, Robert DiFalco wrote: > ​I am fairly certain this does not give you the correct results. >> Specifically, the minimum value for each cDate is going to be 1 since >> count(*) counts NULLs. count(u) should probably work. >> ​ >> >> Yes you are right, I forgot to cha

[GENERAL] Polymorphic queries

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, maybe someone has an idea upon this: I have a table which has quite a few inheriting tables which have inheriting tables etc (it's mirroring a OOP model). As expected, most queries are polymorphic, so they are going against the parent tables.

Re: [GENERAL] Average New Users Per DOW

On 7/6/15, Robert DiFalco wrote: > I'm not sure how to create a result where I get the average number of new > users per day of the week. My issues are that days that did not have any > new users will not be factored into the average, giving an overinflated > result. > > This is what I started wit

Re: [GENERAL] Average New Users Per DOW

On Mon, Jul 6, 2015 at 6:16 PM, Michael Nolan wrote: > > But you can see it wont give correct results since (for example) Monday's > > with no new users will not be counted in the average as 0. > > One way to handle this is to union your query with one that has a > generate_series (0,6) for the D

[GENERAL] Missing space in message

Patch attached. diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 595a609..c8c4eed 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -2461,7 +2461,7 @@ static struct config_int ConfigureNamesInt[] = { {"wal_retrieve_retry_interval",

Re: [GENERAL] Missing space in message

On 7 July 2015 at 10:52, Daniele Varrazzo wrote: > Patch attached. > > > Thanks for the patch. Would you be able to post it to pgsql-hack...@postgresql.org instead? Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL De

Re: [GENERAL] Missing space in message

On Tue, Jul 7, 2015 at 12:08 AM, David Rowley wrote: > On 7 July 2015 at 10:52, Daniele Varrazzo > wrote: >> >> Patch attached. >> >> > > Thanks for the patch. > > Would you be able to post it to pgsql-hack...@postgresql.org instead? Oops, sorry. Got this wrong, for the other errors I've found I

Re: [GENERAL] database-level lockdown

On 07/06/2015 07:10 AM, Filipe Pina wrote: It's not necessary to commit at all costs, it can fail, just not due to serialization.. And the transaction can be something as simple as updating a field or inserting a record (with foreign keys which is one the serialization checks). Not following,

Re: [GENERAL] database-level lockdown

On 07/06/2015 07:15 AM, Filipe Pina wrote: Yes, I've tried to come up with guideline to enumerate tables used in each process, but it's not simple because it's python application calling pgsql functions that use other functions, so it's tricky for a developer re-using existing functions to enumer

Re: [GENERAL] Average New Users Per DOW

On Mon, Jul 6, 2015 at 5:50 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jul 6, 2015 at 6:16 PM, Michael Nolan wrote: > >> > But you can see it wont give correct results since (for example) >> Monday's >> > with no new users will not be counted in the average as 0. >> >> O

Re: [GENERAL] Download PostgreSQL 9.5 Alpha

On 04-07-2015 02:20, Charles Clavadetscher wrote: Hello I also could not find the download on EDB. For Ubuntu 9.5 alpha is available but I was not able to install it using apt-get because it cannot resolve some dependencies. Finally I downloaded and compiled the source code. This worked well

Re: [GENERAL] Average New Users Per DOW

Here's a minor refinement that doesn't require knowing the range of dates in the users table: (select created, created as created2, count(*) as total from users group by 1, 2 union (select generate_series( (select min(created)::timestamp from users), (select max(created)::timestamp from users), '1

Re: [GENERAL] [pg_hba.conf] publish own Python application using PostgreSQL

On 2015-07-05 22:16 John R Pierce wrote: > at a bare minimum, a database administrator needs to create database > roles (users) and databases for an app like yours. The admin don't need to create the db. It is done by the application (sqlalchemy-utils on Python3) itself. But I see. I will go ba

Re: [GENERAL] [pg_hba.conf] publish own Python application using PostgreSQL

On 7/6/2015 9:55 PM, c.bu...@posteo.jp wrote: On 2015-07-05 22:16 John R Pierce wrote: >at a bare minimum, a database administrator needs to create database >roles (users) and databases for an app like yours. The admin don't need to create the db. It is done by the application (sqlalchemy-util