date:20120420

Hi all.
I'd like use a temporary view to hide a non-temp one  for some queries.
Later I'd need to drop that view in order to revert to normal operations.
As there is no DROP TEMPORARY VIEW ... I'd be forced to CREATE OR
REPLACE TEMPORARY VIEW ... in order to overwrite the temporary one
with the same code as the non-temporary.
All this sounds quite complex to me. Is there a way to drop temporary
stuff before closing the session?
TIA.

--
Vincenzo Romano - NotOrAnd.IT
Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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_advisory_lock() and row deadlocks

On Fri, Apr 20, 2012 at 10:27 AM, Eliot Gable
egable+pgsql-gene...@gmail.com wrote:
 Is it possible to prevent row deadlocks by using pg_advisory_lock()? For
 example:

 Transaction 1 grabs pg_advisory_lock(1)
 Transaction 1 runs a statement that updates multiple rows on Table A
 Transaction 1 releases pg_advisory_lock(1)
 Transaction 1 continues processing other stuff
 Transaction 1 grabs pg_advisory_lock(2)
 Transaction 1 runs a statement that updates multiple rows on Table B
 Transaction 1 releases pg_advisory_lock(2)
 Transaction 1 continues processing other stuff

 At the same time...

 Transaction 2 grabs pg_advisory_lock(2)
 Transaction 2 runs a statement that deletes multiple rows on Table B
 Transaction 2 releases pg_advisory_lock(2)
 Transaction 2 continues processing other stuff
 Transaction 2 grabs pg_advisory_lock(1)
 Transaction 2 runs a statement that deletes multiple rows on Table A
 Transaction 2 releases pg_advisory_lock(1)
 Transaction 2 continues processing other stuff

 If these two transactions run simultaneously, is there any way that they can
 have a row deadlock given the way the pg_advisory_lock() calls are made?

 My underlying problem is trying to break row deadlocks due to cascading
 deletes on foreign keys in one transaction colliding with updates to rows in
 another transaction.

 If I use pg_advisory_lock(), can I lock and unlock a table multiple times in
 both transactions without ever needing to worry about them getting
 deadlocked on rows? Doing select locks on rows is not an option because they
 last until the end of the transaction and I cannot control the order in
 which both transactions grab locks on the different tables involved, and
 each transaction may have an affect on the same rows as the other
 transaction in one or more of the same tables.

hm, I'm not sure this is going to completely help you.  all that
you're getting from advisory locks is making sure transactions 1 and 2
are not concurrently accessing the same table.  that's helpful in the
sense you don't have to worry about out of order locking, but it looks
like T1 can get locks on A and T2 can get locks on B then T1 and T2
will swap the tables they are about to lock.  unless i'm crazy, this
should deadlock.

in other words as you've written above you're getting protection from
row ordering on each table, but not access to the tables themselves.
if both transactions went in A-B order it might work.

merlin

-- 
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] How to drop a temporary view?

2012-04-20 Thread Rodrigo Gonzalez

On Fri, 20 Apr 2012 17:36:59 +0200
Vincenzo Romano vincenzo.rom...@notorand.it wrote:

 Hi all.
 I'd like use a temporary view to hide a non-temp one  for some
 queries. Later I'd need to drop that view in order to revert to
 normal operations. As there is no DROP TEMPORARY VIEW ... I'd be
 forced to CREATE OR REPLACE TEMPORARY VIEW ... in order to
 overwrite the temporary one with the same code as the non-temporary.
 All this sounds quite complex to me. Is there a way to drop temporary
 stuff before closing the session?
 TIA.

DROP VIEW view

It will destroy the view, temporary or not...

Regards

Rodrigo

 
 --
 Vincenzo Romano - NotOrAnd.IT
 Information Technologies
 NON QVIETIS MARIBVS NAVTA PERITVS
 


-- 
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] How to drop a temporary view?

On Fri, Apr 20, 2012 at 10:36 AM, Vincenzo Romano
vincenzo.rom...@notorand.it wrote:
 Hi all.
 I'd like use a temporary view to hide a non-temp one  for some queries.
 Later I'd need to drop that view in order to revert to normal operations.
 As there is no DROP TEMPORARY VIEW ... I'd be forced to CREATE OR
 REPLACE TEMPORARY VIEW ... in order to overwrite the temporary one
 with the same code as the non-temporary.
 All this sounds quite complex to me. Is there a way to drop temporary
 stuff before closing the session?
 TIA.

you can do it with vanilla DROP VIEW:

postgres=# create temp view t as select 1;
CREATE VIEW
postgres=# drop view t;
DROP VIEW

merlin

-- 
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_advisory_lock() and row deadlocks

2012-04-20 Thread Chris Angelico

On Sat, Apr 21, 2012 at 1:27 AM, Eliot Gable
egable+pgsql-gene...@gmail.com wrote:
 If I use pg_advisory_lock(), can I lock and unlock a table multiple times in
 both transactions without ever needing to worry about them getting
 deadlocked on rows? Doing select locks on rows is not an option because they
 last until the end of the transaction and I cannot control the order in
 which both transactions grab locks on the different tables involved, and
 each transaction may have an affect on the same rows as the other
 transaction in one or more of the same tables.

You have a Dining Philosophers Problem. Why can you not control the
order in which they acquire their locks? That's one of the simplest
solutions - for instance, all update locks are to be acquired in
alphabetical order of table name, then in primary key order within the
table. Yes, select locks last until the end of the transaction, but
are you really sure you can safely release the locks earlier? By
releasing those advisory locks, you're allowing the transactions to
deadlock, I think. Attempting a manual interleave of these:

Transaction 1 grabs pg_advisory_lock(1)
Transaction 2 grabs pg_advisory_lock(2)
Transaction 1 runs a statement that updates multiple rows on Table A
Transaction 2 runs a statement that deletes multiple rows on Table B
Transaction 1 releases pg_advisory_lock(1)
Transaction 2 releases pg_advisory_lock(2)
Transaction 1 continues processing other stuff
Transaction 2 continues processing other stuff

At this point, Transaction 1 retains the locks on rows of Table A, and
Transaction 2 retains locks on B.

Transaction 1 grabs pg_advisory_lock(2)
Transaction 2 grabs pg_advisory_lock(1)
Transaction 1 runs a statement that updates multiple rows on Table B
-- Lock --
Transaction 2 runs a statement that deletes multiple rows on Table A
-- Deadlock --

Your advisory locks aren't actually doing anything for you here.

ChrisA

-- 
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] How to drop a temporary view?

2012/4/20 Merlin Moncure mmonc...@gmail.com:
 On Fri, Apr 20, 2012 at 10:36 AM, Vincenzo Romano
 vincenzo.rom...@notorand.it wrote:
 Hi all.
 I'd like use a temporary view to hide a non-temp one  for some queries.
 Later I'd need to drop that view in order to revert to normal operations.
 As there is no DROP TEMPORARY VIEW ... I'd be forced to CREATE OR
 REPLACE TEMPORARY VIEW ... in order to overwrite the temporary one
 with the same code as the non-temporary.
 All this sounds quite complex to me. Is there a way to drop temporary
 stuff before closing the session?
 TIA.

 you can do it with vanilla DROP VIEW:

 postgres=# create temp view t as select 1;
 CREATE VIEW
 postgres=# drop view t;
 DROP VIEW

 merlin

Ok. That works. How can I know if there's a temporary view with the
same name in my session?

-- 
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] How to drop a temporary view?

On Fri, Apr 20, 2012 at 10:51 AM, Vincenzo Romano
vincenzo.rom...@notorand.it wrote:
 2012/4/20 Merlin Moncure mmonc...@gmail.com:
 On Fri, Apr 20, 2012 at 10:36 AM, Vincenzo Romano
 vincenzo.rom...@notorand.it wrote:
 Hi all.
 I'd like use a temporary view to hide a non-temp one  for some queries.
 Later I'd need to drop that view in order to revert to normal operations.
 As there is no DROP TEMPORARY VIEW ... I'd be forced to CREATE OR
 REPLACE TEMPORARY VIEW ... in order to overwrite the temporary one
 with the same code as the non-temporary.
 All this sounds quite complex to me. Is there a way to drop temporary
 stuff before closing the session?
 TIA.

 you can do it with vanilla DROP VIEW:

 postgres=# create temp view t as select 1;
 CREATE VIEW
 postgres=# drop view t;
 DROP VIEW

 merlin

 Ok. That works. How can I know if there's a temporary view with the
 same name in my session?

well, arguably you should already know somehow.  but if you don't,
query information_schema.views for a table_name with a table_schema
LIKE 'pg_temp%'.

merlin

-- 
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] How to drop a temporary view?

Merlin Moncure mmonc...@gmail.com writes:
 On Fri, Apr 20, 2012 at 10:51 AM, Vincenzo Romano
 vincenzo.rom...@notorand.it wrote:
 Ok. That works. How can I know if there's a temporary view with the
 same name in my session?

 well, arguably you should already know somehow.  but if you don't,
 query information_schema.views for a table_name with a table_schema
 LIKE 'pg_temp%'.

Not sure that is safe --- won't the info schema also show temp views
of other sessions?

You might be able to use DROP VIEW pg_temp.foo, which will either
drop a temp view of your own session or throw an error if there is none.

regards, tom lane

-- 
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_advisory_lock() and row deadlocks

2012-04-20 Thread Eliot Gable

On Fri, Apr 20, 2012 at 11:46 AM, Chris Angelico ros...@gmail.com wrote:


 You have a Dining Philosophers Problem. Why can you not control the
 order in which they acquire their locks? That's one of the simplest
 solutions - for instance, all update locks are to be acquired in
 alphabetical order of table name, then in primary key order within the
 table. Yes, select locks last until the end of the transaction, but
 are you really sure you can safely release the locks earlier? By
 releasing those advisory locks, you're allowing the transactions to
 deadlock, I think. Attempting a manual interleave of these:

 Transaction 1 grabs pg_advisory_lock(1)
 Transaction 2 grabs pg_advisory_lock(2)
 Transaction 1 runs a statement that updates multiple rows on Table A
 Transaction 2 runs a statement that deletes multiple rows on Table B
 Transaction 1 releases pg_advisory_lock(1)
 Transaction 2 releases pg_advisory_lock(2)
 Transaction 1 continues processing other stuff
 Transaction 2 continues processing other stuff

 At this point, Transaction 1 retains the locks on rows of Table A, and
 Transaction 2 retains locks on B.

 Transaction 1 grabs pg_advisory_lock(2)
 Transaction 2 grabs pg_advisory_lock(1)
 Transaction 1 runs a statement that updates multiple rows on Table B
 -- Lock --
 Transaction 2 runs a statement that deletes multiple rows on Table A
 -- Deadlock --

 Your advisory locks aren't actually doing anything for you here.

 ChrisA


How do you control the order in which cascading deletes occur across tables
and the order in which they fire the triggers which do the locking?

Within a single function or even within a couple of functions, I can
control the order. But they are called from within triggers on tables on
cascading delete or update operations. How do I control that? Some
functions only need to lock certain tables while other functions need a
large set of the tables locked. All the functions and triggers lock tables
in alphabetical order, and I have rolled the locking out to the furthest
level based on what sub-functions call. However, transaction 1 might call
function1() first and then function2() and transaction 2 might call
function2() first and then function1() and those functions might grab locks
on Table A and B independently, but then when transaction 1 or 2 calls
function3(), it needs to work with both tables, and then they deadlock.
Function1() or function2() might be called in a transaction without ever
calling function3() in that transaction, so it doesn't make sense to lock
all the tables in function1() and function2() that function3() also locks.

Re: [GENERAL] How to drop a temporary view?

2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 Merlin Moncure mmonc...@gmail.com writes:
 On Fri, Apr 20, 2012 at 10:51 AM, Vincenzo Romano
 vincenzo.rom...@notorand.it wrote:
 Ok. That works. How can I know if there's a temporary view with the
 same name in my session?

 well, arguably you should already know somehow.  but if you don't,
 query information_schema.views for a table_name with a table_schema
 LIKE 'pg_temp%'.

 Not sure that is safe --- won't the info schema also show temp views
 of other sessions?

 You might be able to use DROP VIEW pg_temp.foo, which will either
 drop a temp view of your own session or throw an error if there is none.

                        regards, tom lane

Cool! It works, despite my temporary schema should be now pg_temp_29!
It's at chapter 18.11.1 (my fault for not searching enough).

Thanks a lot Tom.

-- 
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] How to drop a temporary view?

2012/4/20 Vincenzo Romano vincenzo.rom...@notorand.it:
 2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 Merlin Moncure mmonc...@gmail.com writes:
 On Fri, Apr 20, 2012 at 10:51 AM, Vincenzo Romano
 vincenzo.rom...@notorand.it wrote:
 Ok. That works. How can I know if there's a temporary view with the
 same name in my session?

 well, arguably you should already know somehow.  but if you don't,
 query information_schema.views for a table_name with a table_schema
 LIKE 'pg_temp%'.

 Not sure that is safe --- won't the info schema also show temp views
 of other sessions?

 You might be able to use DROP VIEW pg_temp.foo, which will either
 drop a temp view of your own session or throw an error if there is none.

                        regards, tom lane

 Cool! It works, despite my temporary schema should be now pg_temp_29!
 It's at chapter 18.11.1 (my fault for not searching enough).

 Thanks a lot Tom.

It works only if you have created at least one temporary object.
Until that the pg_temp meta schema doesn't resolve to a real schema
and the exception is thrown!
It's a cool thing anyway!

-- 
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_advisory_lock() and row deadlocks

On Fri, Apr 20, 2012 at 11:25 AM, Eliot Gable
egable+pgsql-gene...@gmail.com wrote:
 On Fri, Apr 20, 2012 at 11:46 AM, Chris Angelico ros...@gmail.com wrote:


 You have a Dining Philosophers Problem. Why can you not control the
 order in which they acquire their locks? That's one of the simplest
 solutions - for instance, all update locks are to be acquired in
 alphabetical order of table name, then in primary key order within the
 table. Yes, select locks last until the end of the transaction, but
 are you really sure you can safely release the locks earlier? By
 releasing those advisory locks, you're allowing the transactions to
 deadlock, I think. Attempting a manual interleave of these:

 Transaction 1 grabs pg_advisory_lock(1)
 Transaction 2 grabs pg_advisory_lock(2)
 Transaction 1 runs a statement that updates multiple rows on Table A
 Transaction 2 runs a statement that deletes multiple rows on Table B
 Transaction 1 releases pg_advisory_lock(1)
 Transaction 2 releases pg_advisory_lock(2)
 Transaction 1 continues processing other stuff
 Transaction 2 continues processing other stuff

 At this point, Transaction 1 retains the locks on rows of Table A, and
 Transaction 2 retains locks on B.

 Transaction 1 grabs pg_advisory_lock(2)
 Transaction 2 grabs pg_advisory_lock(1)
 Transaction 1 runs a statement that updates multiple rows on Table B
 -- Lock --
 Transaction 2 runs a statement that deletes multiple rows on Table A
 -- Deadlock --

 Your advisory locks aren't actually doing anything for you here.

 ChrisA


 How do you control the order in which cascading deletes occur across tables
 and the order in which they fire the triggers which do the locking?

 Within a single function or even within a couple of functions, I can control
 the order. But they are called from within triggers on tables on cascading
 delete or update operations. How do I control that? Some functions only need
 to lock certain tables while other functions need a large set of the tables
 locked. All the functions and triggers lock tables in alphabetical order,
 and I have rolled the locking out to the furthest level based on what
 sub-functions call. However, transaction 1 might call function1() first and
 then function2() and transaction 2 might call function2() first and then
 function1() and those functions might grab locks on Table A and B
 independently, but then when transaction 1 or 2 calls function3(), it needs
 to work with both tables, and then they deadlock. Function1() or function2()
 might be called in a transaction without ever calling function3() in that
 transaction, so it doesn't make sense to lock all the tables in function1()
 and function2() that function3() also locks.

yes I agree: I can see the point of wrapping the locks in advisory
locks when doing row-order locking is difficult or impossible but:

*) you are serializing all deletes even if they don't bump into each other
*) you still need to go in A-B order in both functions

merlin

-- 
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] How to drop a temporary view?

Vincenzo Romano vincenzo.rom...@notorand.it writes:
 2012/4/20 Vincenzo Romano vincenzo.rom...@notorand.it:
 2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 You might be able to use DROP VIEW pg_temp.foo, which will either
 drop a temp view of your own session or throw an error if there is none.

 It works only if you have created at least one temporary object.
 Until that the pg_temp meta schema doesn't resolve to a real schema
 and the exception is thrown!

I didn't say *which* error would get thrown ;-).  If you are checking
that you'd need to be prepared for both cases.

regards, tom lane

-- 
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] How to drop a temporary view?

On Fri, Apr 20, 2012 at 11:07 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Merlin Moncure mmonc...@gmail.com writes:
 well, arguably you should already know somehow.  but if you don't,
 query information_schema.views for a table_name with a table_schema
 LIKE 'pg_temp%'.

 Not sure that is safe --- won't the info schema also show temp views
 of other sessions?

nope, it works (you only get to see your own temporary views
information_schema).  i guess you could make an arguable case to fold
the temp schema name to a constant to hide the pg_temp_zz
implementation detail.

merlin

-- 
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] How to drop a temporary view?

2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 Vincenzo Romano vincenzo.rom...@notorand.it writes:
 2012/4/20 Vincenzo Romano vincenzo.rom...@notorand.it:
 2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 You might be able to use DROP VIEW pg_temp.foo, which will either
 drop a temp view of your own session or throw an error if there is none.

 It works only if you have created at least one temporary object.
 Until that the pg_temp meta schema doesn't resolve to a real schema
 and the exception is thrown!

 I didn't say *which* error would get thrown ;-).  If you are checking
 that you'd need to be prepared for both cases.

                        regards, tom lane

... WHEN OTHERS THEN NULL

should be pretty safe to DROP a VIEW!

-- 
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] How to drop a temporary view?

2012/4/20 Vincenzo Romano vincenzo.rom...@notorand.it:
 2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 Vincenzo Romano vincenzo.rom...@notorand.it writes:
 2012/4/20 Vincenzo Romano vincenzo.rom...@notorand.it:
 2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 You might be able to use DROP VIEW pg_temp.foo, which will either
 drop a temp view of your own session or throw an error if there is none.

 It works only if you have created at least one temporary object.
 Until that the pg_temp meta schema doesn't resolve to a real schema
 and the exception is thrown!

 I didn't say *which* error would get thrown ;-).  If you are checking
 that you'd need to be prepared for both cases.

                        regards, tom lane

 ... WHEN OTHERS THEN NULL

 should be pretty safe to DROP a VIEW!

There's a somehow weird behavior.
This is what I create (sorry for lowecase):
--
create view timeref as select * from current_timestamp timeref;

create or replace function timeref()
returns void
language plpgsql
volatile
as $l0$
begin
  drop view if exists pg_temp.timeref;
  exception when others then null;
  create or replace temporary view timeref as select
current_timestamp::timestamp with time zone timeref;
end
$l0$;

create or replace function timeref( t text )
returns void
language plpgsql
volatile
as $l0$
begin
  execute format( 'create or replace temporary view timeref as select
%L::timestamp with time zone timeref',t );
end
$l0$;
--

Now the run(s):
--
tmp1=# SELECT * from timeref;
timeref
---
 2012-04-20 18:57:09.340628+02
(1 row)

Time: 0,250 ms
tmp1=# SELECT * from timeref();
 timeref
-

(1 row)

Time: 13,639 ms
tmp1=# SELECT * from timeref();
 timeref
-

(1 row)

Time: 40,494 ms
tmp1=# SELECT * from timeref();
NOTICE:  view timeref does not exist, skipping
CONTEXT:  SQL statement drop view if exists pg_temp.timeref
PL/pgSQL function timeref line 3 at SQL statement
 timeref
-

(1 row)

Time: 12,048 ms
--

As you can see, the third time I get a NOTICE message I don't get the
first two times.
Everything works fine but this strange thing...

-- 
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] How to drop a temporary view?

2012/4/20 Vincenzo Romano vincenzo.rom...@notorand.it:
 2012/4/20 Vincenzo Romano vincenzo.rom...@notorand.it:
 2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 Vincenzo Romano vincenzo.rom...@notorand.it writes:
 2012/4/20 Vincenzo Romano vincenzo.rom...@notorand.it:
 2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 You might be able to use DROP VIEW pg_temp.foo, which will either
 drop a temp view of your own session or throw an error if there is none.

 It works only if you have created at least one temporary object.
 Until that the pg_temp meta schema doesn't resolve to a real schema
 and the exception is thrown!

 I didn't say *which* error would get thrown ;-).  If you are checking
 that you'd need to be prepared for both cases.

                        regards, tom lane

 ... WHEN OTHERS THEN NULL

 should be pretty safe to DROP a VIEW!

 There's a somehow weird behavior.
 This is what I create (sorry for lowecase):
 --
 create view timeref as select * from current_timestamp timeref;

 create or replace function timeref()
 returns void
 language plpgsql
 volatile
 as $l0$
 begin
  drop view if exists pg_temp.timeref;
  exception when others then null;
  create or replace temporary view timeref as select
 current_timestamp::timestamp with time zone timeref;
 end
 $l0$;

 create or replace function timeref( t text )
 returns void
 language plpgsql
 volatile
 as $l0$
 begin
  execute format( 'create or replace temporary view timeref as select
 %L::timestamp with time zone timeref',t );
 end
 $l0$;
 --

 Now the run(s):
 --
 tmp1=# SELECT * from timeref;
            timeref
 ---
  2012-04-20 18:57:09.340628+02
 (1 row)

 Time: 0,250 ms
 tmp1=# SELECT * from timeref();
  timeref
 -

 (1 row)

 Time: 13,639 ms
 tmp1=# SELECT * from timeref();
  timeref
 -

 (1 row)

 Time: 40,494 ms
 tmp1=# SELECT * from timeref();
 NOTICE:  view timeref does not exist, skipping
 CONTEXT:  SQL statement drop view if exists pg_temp.timeref
 PL/pgSQL function timeref line 3 at SQL statement
  timeref
 -

 (1 row)

 Time: 12,048 ms
 --

 As you can see, the third time I get a NOTICE message I don't get the
 first two times.
 Everything works fine but this strange thing...

There's a typo (extra create temporary view), Sorry,

-- 
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] How to drop a temporary view?

Vincenzo Romano vincenzo.rom...@notorand.it writes:
 As you can see, the third time I get a NOTICE message I don't get the
 first two times.
 Everything works fine but this strange thing...

 There's a typo (extra create temporary view), Sorry,

Hm, yeah, the first time would throw an error because pg_temp doesn't
resolve, so if you're using DROP IF EXISTS there would be a visible
difference in behavior between the two cases.

I wonder if DROP IF EXISTS should consider non-existence of the
specified schema (if any) as being a not exists case, and not throw
an error for it.  This isn't specific to the temp schema at all IMO.

regards, tom lane

-- 
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] Unaccent characters

2012-04-20 Thread Peter Eisentraut

On fre, 2012-04-20 at 09:15 +0100, Thom Brown wrote:
 I had a look at the unaccent.rules file and noticed the following
 characters aren't properly converted:
 
 ß (U+00DF)  An eszett represents a double-s SS but this replaces it
 with one S.  Shouldn't this be replace with SS?

Probably, but it certainly shouldn't be upper case.

 Æ (U+00C6) and æ (U+00E6) These doesn't have an accent, diacritic or
 anything added to a single latin character.  It's simply a ligature of
 A and E or a and e.  If someone has the text æther, I would
 imagine they'd be surprised at it being converted to ather instead
 of aether.

It depends on what the point of this module is supposed to be.  Doing
unaccenting usefully depends on language and context.  For example, it
would be very reasonable to map æ to ae, but in a Scandinavian context,
æ is equivalent to ä, which is mapped to a, which is itself
questionable.

 Œ (U+0152) and œ (U+0153). Same as above.  This is a ligature of O
 and E or o and e.  Except this time the unaccent module chooses
 the 2nd character instead of the 1st which is confusing.

That certainly seems wrong.  It's also worth noting that while æ is in
some languages considered a separate letter, œ is generally just a
typographical ligature.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_basebackup issues

2012-04-20 Thread Lonni J Friedman

Greetings,
I'm running postgresql-9.1.3 on a Linux-x86_64 (Fedora16, if it
matters) system.  I noticed the existence of pg_basebackup starting in
9.1, and figured I'd try it out and see if it would simplify our
backup  management processes.  I setup a test system (same OS 
postgresql version as production) with a fairly recent snapshot of our
production database, invoked it, and saw the following output:
##
# pg_basebackup -P -v -D backups -Ft -z -U postgres
135717206/135717230 kB (100%), 1/1 tablespace
pg_basebackup: could not get WAL end position from server
##

I wasn't sure what that error meant, so after googling a bit, turns
out that it really means that there were one or more files not owned
by the postgres user (see
http://serverfault.com/questions/312205/pg-basebackup-could-not-get-wal-end-position-from-server
).  Sure enough, the file that wasn't owned by the postgres user was
the backup tarball that pg_basebackup was creating, since I had been
running it as root.  That error is rather cryptic, and it would be
helpful if it was improved to suggest the real cause of the failure.
Anyway, lesson learned, I need to either invoke pg_basebackup as the
same user that runs the database (or is specified with the -U
parameter ?), or write the backup somewhere outside of the directory
structure that is being backed up.

I eventually also found the following entries in the postgresql server log:
FATAL:  could not open directory ./backups: Permission denied
FATAL:  archive member backups/base.tar.gz too large for tar format

What concerns me is the 2nd fatal error.  The tarball that
pg_basebackup created before erroring out is about 12GB:
12393094165  base.tar.gz

I wasn't aware of any 12GB file size limit for tar, so this is a bit
of a mystery to me.  Regardless, I'd be happy to try some other
archiving strategy, but the man page for pg_basebackup suggests that
there are only two formats, tar and basically just copying the
filesystem.  If I copied the filesystem, I'd still have to find some
way to archive them for easy management (copying elsewhere, etc).  Has
anyone come up with a good strategy on how to deal with it?

thanks

-- 
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] How to drop a temporary view?

2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 Vincenzo Romano vincenzo.rom...@notorand.it writes:
 As you can see, the third time I get a NOTICE message I don't get the
 first two times.
 Everything works fine but this strange thing...

 There's a typo (extra create temporary view), Sorry,

 Hm, yeah, the first time would throw an error because pg_temp doesn't
 resolve, so if you're using DROP IF EXISTS there would be a visible
 difference in behavior between the two cases.

 I wonder if DROP IF EXISTS should consider non-existence of the
 specified schema (if any) as being a not exists case, and not throw
 an error for it.  This isn't specific to the temp schema at all IMO.

                        regards, tom lane

The weirdness is that it doesn't produce any notice the first two times.
At the third invocation I see the notice coming out.
The test has been run on an empty database and a fresh new connection.
You can try the code yourself.
(I'm running 9.1.3 on Ubuntu Linux)

-- 
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] How to drop a temporary view?

Vincenzo Romano vincenzo.rom...@notorand.it writes:
 The weirdness is that it doesn't produce any notice the first two times.
 At the third invocation I see the notice coming out.

I'd suggest tweaking the exception handler to print the error it caught;
that would probably clarify what is happening.

regards, tom lane

-- 
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_advisory_lock() and row deadlocks

2012-04-20 Thread Chris Angelico

On Sat, Apr 21, 2012 at 2:25 AM, Eliot Gable
egable+pgsql-gene...@gmail.com wrote:
 How do you control the order in which cascading deletes occur across tables
 and the order in which they fire the triggers which do the locking?

Well, I'd guess that they probably have a well-defined order. However...

 Within a single function or even within a couple of functions, I can control
 the order. But they are called from within triggers on tables on cascading
 delete or update operations. How do I control that? Some functions only need
 to lock certain tables while other functions need a large set of the tables
 locked. All the functions and triggers lock tables in alphabetical order,
 and I have rolled the locking out to the furthest level based on what
 sub-functions call. However, transaction 1 might call function1() first and
 then function2() and transaction 2 might call function2() first and then
 function1() and those functions might grab locks on Table A and B
 independently, but then when transaction 1 or 2 calls function3(), it needs
 to work with both tables, and then they deadlock. Function1() or function2()
 might be called in a transaction without ever calling function3() in that
 transaction, so it doesn't make sense to lock all the tables in function1()
 and function2() that function3() also locks.

... if you honestly can't control this much, then you need to fix your
deadlock problems at a different level.

Your advisory locks could be used to solve this problem, but you'd
need to hold them until transaction end. And you would still need to
acquire them in a specific, known order, because otherwise you just
shift your deadlock point.

You may need to majorly rethink your entire locking system. Perhaps
it's easier to acquire stronger locks and reduce concurrency, or
perhaps you can predict which functions are going to be called and
acquire the appropriate locks (either actual table/row locks or
advisory locks) before calling any of them. If you really cannot
control the transactions, then the only recourse is to accept that
you're going to have some rolled back for deadlocks, and retry those
transactions (and accept the performance penalty).

ChrisA

-- 
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] Problem with reading data from standby server ?

On Fri, Apr 20, 2012 at 3:39 AM, Condor con...@stz-bg.com wrote:
 Hello,

 when I read binary replication tutorial
 (http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial) I see on Hot
 Standby: Hot Standby is identical to Warm Standby, except that the Standby
 is available to run read-only queries.
 I setup hot standby server described in tutorial and it's working fine, no
 problem with that. I have a problem when I try to start a script that should
 read whole table, error message from php is:

 PHP Warning:  pg_query(): Query failed: ERROR:  canceling statement due to
 conflict with recovery
 DETAIL:  User query might have needed to see row versions that must be
 removed. in dump.php on line 68
 PHP Warning:  pg_fetch_array() expects parameter 1 to be resource, boolean
 given in dump.php on line 69
 PHP Warning:  pg_query(): Query failed: ERROR:  canceling statement due to
 conflict with recovery
 DETAIL:  User query might have needed to see row versions that must be
 removed. in dump.php on line 235
 PHP Warning:  pg_fetch_array() expects parameter 1 to be resource, boolean
 given in dump.php on line 236


 Script actually start sql query:

 SELECT abs.id, array_accumulate(abs.status) AS status,
 array_accumulate(abs.service) AS service, stb.model FROM statuses abs,
 stb_tbl stb WHERE abs.id = stb.ser
  AND abs.service != 8 AND abs.id LIKE '432%' GROUP BY abs.id, stb.model
 ORDER BY abs.id


 array_accumulate aggregate is:


 CREATE AGGREGATE array_accumulate (
    sfunc = array_append,
    basetype = anyelement,
    stype = anyarray,
    initcond = '{}'
 );


 When data is fetched it's saved into a file after some modifications. This
 script is work a 30-40 min until all data is parsed. Well, I think problem
 is started when master server send new wal file to slave, but how I can
 resolve that problem ?



 Any solutions or some one to can point me how I can resolve this problem ?

The big trade-off with HS/SR is that you have to choose between the
standby being up to date and being able to service long running
queries.  The timeouts (expressed via max_standby_archive_delay and
max_standby_streaming_delay) are the main knob to control which way
you want the replica to behave.  Basically, if your read only data
touched a page that is holding back replication for longer than
$timeout, the query gets bounced.

If your replica is mainly going to serve big reporting queries and/or
dumps, you'll need to significantly relax the timeout or disable it
completely -- just understand that this can cause your replica to be
significantly behind the master.

merlin

-- 
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_basebackup issues

2012-04-20 Thread Magnus Hagander

On Fri, Apr 20, 2012 at 19:51, Lonni J Friedman netll...@gmail.com wrote:
Greetings,
I'm running postgresql-9.1.3 on a Linux-x86_64 (Fedora16, if it
matters) system. I noticed the existence of pg_basebackup starting in
9.1, and figured I'd try it out and see if it would simplify our
backup management processes. I setup a test system (same OS
postgresql version as production) with a fairly recent snapshot of our
production database, invoked it, and saw the following output:
##
# pg_basebackup -P -v -D backups -Ft -z -U postgres
135717206/135717230 kB (100%), 1/1 tablespace
pg_basebackup: could not get WAL end position from server
##

I wasn't sure what that error meant, so after googling a bit, turns
out that it really means that there were one or more files not owned
by the postgres user (see
http://serverfault.com/questions/312205/pg-basebackup-could-not-get-wal-end-position-from-server
). Sure enough, the file that wasn't owned by the postgres user was
the backup tarball that pg_basebackup was creating, since I had been
running it as root. That error is rather cryptic, and it would be
helpful if it was improved to suggest the real cause of the failure.

Yeah, the error message comes from the fact that the backend gives up,
and the real message is in the backend log. We should try to do
something about that.

Anyway, lesson learned, I need to either invoke pg_basebackup as the
same user that runs the database (or is specified with the -U
parameter ?), or write the backup somewhere outside of the directory
structure that is being backed up.

I eventually also found the following entries in the postgresql server log:
FATAL: could not open directory ./backups: Permission denied
FATAL: archive member backups/base.tar.gz too large for tar format

What concerns me is the 2nd fatal error. The tarball that
pg_basebackup created before erroring out is about 12GB:
12393094165 base.tar.gz

Are you actually storing your backup files *inside* the data
directory? You really shouldn't do that, you're creating a cyclic
dependency where each new backup will include the old one inside it...
You should store the resulting backup file somewhere outside the data
directory.

I wasn't aware of any 12GB file size limit for tar, so this is a bit
of a mystery to me. Regardless, I'd be happy to try some other
archiving strategy, but the man page for pg_basebackup suggests that
there are only two formats, tar and basically just copying the
filesystem. If I copied the filesystem, I'd still have to find some
way to archive them for easy management (copying elsewhere, etc). Has
anyone come up with a good strategy on how to deal with it?

The max file size of a single flie inside a standard tar file is 8Gb,
see e.g. http://en.wikipedia.org/wiki/Tar_(file_format).

I think there are extensions that let you store bigger files, but
since PostgreSQL will never create files that big it's not
implemented in the basebackup system. Because again, the root of your
problem seems to be that you are trying to store the resulting backup
inside the data directory.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

And what about temporary functions? (Was: [GENERAL] How to drop a temporary view?)

2012/4/20 Tom Lane t...@sss.pgh.pa.us:
 Vincenzo Romano vincenzo.rom...@notorand.it writes:
 The weirdness is that it doesn't produce any notice the first two times.
 At the third invocation I see the notice coming out.

 I'd suggest tweaking the exception handler to print the error it caught;
 that would probably clarify what is happening.

                        regards, tom lane

It looks like it works like this:

-- session 1
create or replace function pg_temp.f( out i int )
volatile
language plpgsql
as $l0$
begin
  i := 42;
end;
$l0$;
-- session 2
create or replace function pg_temp.f( out i int )
volatile
language plpgsql
as $l0$
begin
  i := 0;
end;
$l0$;
-- session 1
tmp1=# SELECT * from f();
ERROR:  function f() does not exist
LINE 1: SELECT * from f();
  ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
tmp1=# SELECT * from pg_temp.f();
 i

 42
(1 row)

Time: 0,301 ms

-- session 2
tmp1=# SELECT * from f();
ERROR:  function f() does not exist
LINE 1: SELECT * from f();
  ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
tmp1=# SELECT * from pg_temp.f();
 i
---
 0
(1 row)

Time: 0,252 ms
--

Why not using the implicit pg_temp_nnn as seen in views and tables?

-- 
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] pgstat wait timeout

2012-04-20 Thread Fujii Masao

On Thu, Apr 19, 2012 at 3:32 AM, Efraín Déctor
efraindec...@motumweb.com wrote:
 Hello list:

 Today I started to see this messages on the PostgreSQL log:

 2012-04-18 00:01:05 UTC : @  :WARNING:  01000: pgstat wait timeout
 2012-04-18 00:01:05 UTC : @  :LOCATION:  backend_read_statsfile,
 pgstat.c:3807

 I searched and to fix it it was recommended to disable autovacuum, I did it
 and it worked, but how can I fix it without disabling autovacuum?.

This might be alleviated by setting stats_temp_directory to point to a ramdisk.

Regards,

-- 
Fujii Masao

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: And what about temporary functions? (Was: [GENERAL] How to drop a temporary view?)

Vincenzo Romano vincenzo.rom...@notorand.it writes:
 Why not using the implicit pg_temp_nnn as seen in views and tables?

That's intentional, it was considered too much of a security risk to
let temporary functions mask normal ones.

regards, tom lane

-- 
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] LOCK TABLE is not allowed in a non-volatile function

2012-04-20 Thread Eliot Gable

On Wed, Apr 18, 2012 at 3:47 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Eliot Gable egable+pgsql-gene...@gmail.com writes:
  On Wed, Apr 18, 2012 at 1:01 PM, Tom Lane t...@sss.pgh.pa.us wrote:
  However, there still might be an issue, because the CONTEXT trace that
  you showed certainly seemed to point where you thought it did.

  After re-reading the LOCK modes and realizing that ACCESS SHARE is not
 the
  same as SHARE, I believe you are correct; the only issue seems to be in
 the
  CONTEXT trace failing to point out that the error occurred three function
  calls deeper than what was reported. It seems it reported it in the first
  function where the EXCEPTION handling was set up.

 Oh!  Yes, that's to be expected, because so far as Postgres is concerned
 it's logging the location of the RAISE WARNING command.  You've only
 told it to print the SQLERRM string, and nothing else:

 RAISE WARNING 'An error occurred while trying to rotate the live user
 activity records; code %: %', SQLSTATE, SQLERRM;

 As of 9.2 there is a way to get the context string for the original
 error (GET STACKED DIAGNOSTICS) which you could then include in the
 RAISE message.  That hasn't made it to any released versions
 unfortunately.

regards, tom lane


Is there a way to print out context in 9.0.1 when doing exception handling
and raising a message? Right now, all I get is my error code, description
of the error, and no idea what the specific statement is that is generating
the error in the first place. Am I missing something, or is this just not
possible in 9.0.1?

Re: [GENERAL] Explain verbose query with CTE