Re: [GENERAL] PERFORM statement

Ah ok that makes sense. The FAQ wasn't exactly clear about that. On Mon, Jul 8, 2013 at 9:38 PM, Tony Theodore wrote: > > On 09/07/2013, at 2:20 PM, Mike Christensen wrote: > > > PERFORM MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' ); > > I get the error: > > ERROR: syntax error at or near "

Re: [GENERAL] PERFORM statement

On 09/07/2013, at 2:20 PM, Mike Christensen wrote: > > PERFORM MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' ); > > I get the error: > > ERROR: syntax error at or near "PERFORM" > SQL state: 42601 > Character: 1 > > Is the FAQ out of date or was this feature removed? I'm using 9.2.1. Thank

[GENERAL] PERFORM statement

I was reading about Postgres stored procs in the FAQ: https://wiki.postgresql.org/wiki/FAQ#Does_PostgreSQL_have_stored_procedures.3F It claims that an alternative syntax to: SELECT theNameOfTheFunction(arg1, arg2); Is: PERFORM theNameOfTheFunction(arg1, arg2); However, when I try the followin

Re: [GENERAL] Longest Common Subsequence in Postgres - Algorithm Challenge

Dnia 9 lip 2013 o godz. 00:46 Michael Paquier napisał(a): > On Tue, Jul 9, 2013 at 5:04 AM, Robert James wrote: >> On 7/8/13, hubert depesz lubaczewski wrote: >>> On Mon, Jul 08, 2013 at 09:09:26AM -0400, Robert James wrote: I have two relations, where each relation has two fields, one

Re: [GENERAL] domains, case statements, functions: bug?

Adrian Klaver writes: > test=> create function f(t) returns m as $$ select case when true then > $1.c end $$ language sql; > ERROR: return type mismatch in function declared to return m > DETAIL: Actual return type is numeric. pg_typeof is somewhat helpful here: regression=# select pg_type

Re: [GENERAL] domains, case statements, functions: bug?

On 07/08/2013 10:26 AM, Joe Van Dyk wrote: create domain m numeric(5,2); create table t (c m); create function f(t) returns m as $ select case when true then $1.c end $ language sql; psql:/tmp/t1.sql:3: ERROR: return type mismatch in function declared to return m DETAIL: Actual return type is

Re: [GENERAL] Force ssl connection

On 07/08/2013 05:32 PM, Muhammad Bashir Al-Noimi wrote: Howdy, I want to prevent any Postgresql database connection other than SSL but it didn't work and my client can connect without SSL! * How can fix this issue? P.S. To force SSL connection I made the following steps: On server side

[GENERAL] Force ssl connection

Howdy, I want to prevent any Postgresql database connection other than SSL but it didn't work and my client can connect without SSL! * How can fix this issue? P.S. To force SSL connection I made the following steps: On server side (ubuntu 12.10 x64 - Postgresql 9.1) 1) Created server.k

Re: [GENERAL] Longest Common Subsequence in Postgres - Algorithm Challenge

On Tue, Jul 9, 2013 at 5:04 AM, Robert James wrote: > On 7/8/13, hubert depesz lubaczewski wrote: >> On Mon, Jul 08, 2013 at 09:09:26AM -0400, Robert James wrote: >>> I have two relations, where each relation has two fields, one >>> indicating a name and one indicating a position. That is, each

Re: [GENERAL] replication stops working

John DeSoi wrote: I have a 9.2 hot standby setup with replication via rsync. For the second time, it has stopped working with no apparent error on the primary or standby. Last time this happened I fixed it by restarting the primary. Yesterday I started a new base backup around noon and it repl

Re: [GENERAL] Longest Common Subsequence in Postgres - Algorithm Challenge

On 7/8/13, hubert depesz lubaczewski wrote: > On Mon, Jul 08, 2013 at 09:09:26AM -0400, Robert James wrote: >> I have two relations, where each relation has two fields, one >> indicating a name and one indicating a position. That is, each >> relation defines a sequence. >> >> I need to determine

[GENERAL] Dynamically accessing record elements using EXECUTE

Hi, I would like to write a function that can take in a table name and a pk value and make a copy of that row into the same table, but with a new value for the PK. I originally had a working function that would create a temp table like the passed-in table, including defaults, and I found the name

[GENERAL] domains, case statements, functions: bug?

create domain m numeric(5,2); create table t (c m); create function f(t) returns m as $ select case when true then $1.c end $ language sql; psql:/tmp/t1.sql:3: ERROR: return type mismatch in function declared to return m DETAIL: Actual return type is numeric. CONTEXT: SQL function "f"

Re: [GENERAL] Why is NULL = unbounded for rangetypes?

On Mon, 2013-07-08 at 10:19 +0200, Andreas Joseph Krogh wrote: > Hi. > > Both of these queries return TRUE because NULL means "unmounded": > select daterange('2013-07-01' :: DATE, null, '[]') && daterange('2013-07-04' > :: DATE, '2013-07-30' :: DATE, '[]'); > select daterange(null, '2013-08-11'

Re: [GENERAL] odd locking behaviour

Thank you for the responses. Is it a bug? I discovered this because of a db deadlock that shows up in my application logs. I can probably work around it to avoid the deadlock (with some amount of work) but I really don't understand why it behaves as it does. On Thu, Jul 4, 2013 at 8:40 AM, Mosh

Re: [GENERAL] Longest Common Subsequence in Postgres - Algorithm Challenge

On Mon, Jul 08, 2013 at 09:09:26AM -0400, Robert James wrote: > I have two relations, where each relation has two fields, one > indicating a name and one indicating a position. That is, each > relation defines a sequence. > > I need to determine their longest common subsequence. Yes, I can do >

[GENERAL] replication stops working

I have a 9.2 hot standby setup with replication via rsync. For the second time, it has stopped working with no apparent error on the primary or standby. Last time this happened I fixed it by restarting the primary. Yesterday I started a new base backup around noon and it replicated without any p

Re: [GENERAL] backend hangs at sendto() and can't be terminated

n etstat show nothing about the socket of the process,so I think the TCP timeout took effect.so it is really wired. Jov blog: http:amutu.com/blog 2013/7/8 Tom Lane > Merlin Moncure writes: > > On Mon, Jul 8, 2013 at 4:56 AM, Jov wrote: > >> my first post already try t

Re: [GENERAL] backend hangs at sendto() and can't be terminated

Merlin Moncure writes: > On Mon, Jul 8, 2013 at 4:56 AM, Jov wrote: >> my first post already try the pg_terminate_backend but failed: >> pg_terminate_backend return t but the backend still there. > possibly a kernel problem? The backend will keep trying to send data until the kernel informs it

Re: [GENERAL] Longest Common Subsequence in Postgres - Algorithm Challenge

On Mon, Jul 8, 2013 at 6:39 PM, Robert James wrote: > I have two relations, where each relation has two fields, one > indicating a name and one indicating a position. That is, each > relation defines a sequence. > > I need to determine their longest common subsequence. Yes, I can do > this by fe

[GENERAL] Longest Common Subsequence in Postgres - Algorithm Challenge

I have two relations, where each relation has two fields, one indicating a name and one indicating a position. That is, each relation defines a sequence. I need to determine their longest common subsequence. Yes, I can do this by fetching all the data into Java (or any other language) and comput

Re: [GENERAL] backend hangs at sendto() and can't be terminated

On Mon, Jul 8, 2013 at 4:56 AM, Jov wrote: > my first post already try the pg_terminate_backend but failed: >pg_terminate_backend return t but the backend still there. possibly a kernel problem? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] backend hangs at sendto() and can't be terminated

my first post already try the pg_terminate_backend but failed: pg_terminate_backend return t but the backend still there. Jov blog: http:amutu.com/blog 2013/7/8 Bill Mitchell > Hmm.. > In that case, I think that select pg_terminate_backend() might be in order? > > h

Re: [GENERAL] backend hangs at sendto() and can't be terminated

Hmm.. In that case, I think that select pg_terminate_backend() might be in order? http://www.postgresql.org/docs/9.1/static/functions-admin.html regards, Bill On 7/8/13 5:46 AM, Jov wrote: > we do select pg_cancel_backend(8243) several times,but the backend > still hang there. > > Jov > blog: ht

Re: [GENERAL] backend hangs at sendto() and can't be terminated

we do select pg_cancel_backend(8243) several times,but the backend still hang there. Jov blog: http:amutu.com/blog 2013/7/8 Bill Mitchell > You can do select pg_cancel_backend(8243); > > and that should terminate that process that is sending, but still leave > your pos

Re: [GENERAL] backend hangs at sendto() and can't be terminated

You can do select pg_cancel_backend(8243); and that should terminate that process that is sending, but still leave your postgres server healthy. regards, Bill On 7/8/13 5:31 AM, Jov wrote: > one of our pg user send a select * from 10 million table without limit > from psql,before get the return

[GENERAL] backend hangs at sendto() and can't be terminated

one of our pg user send a select * from 10 million table without limit from psql，before get the return data，he realize the mistake and quite from psql.but after 2 hours,the sql still alive: postgres=# SELECT * from pg_stat_activity where procpid = 8243; -[ RECORD 1 ]+--

[GENERAL] Why is NULL = unbounded for rangetypes?

Hi.   Both of these queries return TRUE because NULL means "unmounded": select daterange('2013-07-01' :: DATE, null, '[]') && daterange('2013-07-04' :: DATE, '2013-07-30' :: DATE, '[]'); select daterange(null, '2013-08-11' :: DATE, '[]') && daterange('2013-07-04' :: DATE, '2013-07-30' :: DATE

Re: [GENERAL] Efficiency of materialized views refresh in 9.3

Michael Paquier wrote: > Joe Van Dyk wrote: >> Is refreshing a materialized view in 9.3 basically: >> >>   delete from mat_view; >>   insert into mat_view select * from base_view; > Nope. Here is some documentation: > http://www.postgresql.org/docs/devel/static/rules-materializedviews.html A RE