Re: [GENERAL] DEFERRABLE NOT NULL constraint

[Albe Laurenz]

Gavan Schneider wrote:
> But I feel I have missed something here.
> 
> Referring to:
> 

> CHECK constraints, NOT NULL constraints and FOREIGN KEY
> constraints all look very deferrable in this definition. If
> that's the case, why are we having this discussion if the
> requested functionality/compliance is already present? (As I
> have said already) I really must have missed something so am
> standing by for the 'gotcha'... please supply :)

Further down on the page you quote, it says:

  DEFERRABLE
  NOT DEFERRABLE

  This controls whether the constraint can be deferred.
  A constraint that is not deferrable will be checked
  immediately after every command. Checking of constraints
  that are deferrable can be postponed until the end of
  the transaction (using the SET CONSTRAINTS command).
  NOT DEFERRABLE is the default. Currently, only UNIQUE,
  PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key)
  constraints accept this clause.
  NOT NULL and CHECK constraints are not deferrable.

Yours,
Laurenz Albe

-- 
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] Visual query builder for PosgreSQL?

[Russell Keane]

> I'm looking for some good visual query builder which can be used by non-tech 
> people for some ETL tasks. Do you have any recommendation?
>
> Now, we're moving our data from Excel to PostgreSQL to deal with large amount 
> of data, and we need to process some ETL tasks, with using JOIN and GROUP BY 
> between tables, up to 10 tables on it.
>
> Of course, I can write ad-hoc queries by myself. However, I'd like to allow 
> non-tech people to issue ad-hoc queries with using some visual query builder.
>
> I have already looked a query builder feature in pgAdminIII, but I'm not sure 
> whether I can use GROUP BY with it.
>
> Do you have any experience or recommendation about visual query builder for 
> PostgreSQL?

You can do joins in the PGAdmin3 query builder but I'm fairly sure you can't do 
group by's.

Apparently, SQL Manager from here:
http://www.sqlmanager.net/en/products/postgresql/manager/
Has a "Visual query builder allowing you to build complicated queries without 
any knowledge of SQL syntax"

I may be shot for even suggesting this (and it is a bit of a hassle) but you 
could recreate your table structure in MS Access and use its graphical query 
builder to generate your SQL. It does allow for group by's, counts, etc.

Regards,

Russell Keane
INPS

Follow us on twitter | visit www.inps.co.uk




-- 
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] DEFERRABLE NOT NULL constraint

[Gavan Schneider]

On Friday, February 8, 2013 at 19:34, Albe Laurenz wrote:

Gavan Schneider wrote:

Referring to:


I really must have missed something so am
standing by for the 'gotcha'... please supply :)



Further down on the page you quote, it says: ...


Thank you, it had to be somewhere. :)


And this leads to a thought. Why is it that in this chapter the 
documentation gives a synopsis which is not correct for the 
current implementation but relies on a negation much further 
down the page to properly describe the actual behaviour?


Mostly the manual follows the pattern of a correct synopsis 
(where correct means what this version will actually do) 
followed by a section setting out the differences from the 
standard and/or other implementations.


While this chapter of the current documentation is not in error 
overall it's a bit misleading.


Of course if anything is going to change my preference would be 
to leave the synopsis in its SQL conformant state and bring the 
implementation up to standard in this area, meaning we can drop 
the contradiction/'correcting' paragraph. And, no, I'm not 
holding my breath on this just now.


Regards
Gavan Schneider



--
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] DEFERRABLE NOT NULL constraint

[Albe Laurenz]

Gavan Schneider wrote:
> And this leads to a thought. Why is it that in this chapter the
> documentation gives a synopsis which is not correct for the
> current implementation but relies on a negation much further
> down the page to properly describe the actual behaviour?

The synopsis gives the syntax diagram, that is, what you must
type to avoid a syntax error.

Not every syntactically correct statement is also correct.

Some examples:

test=> CREATE TABLE test (id integer PRIMARY KEY, val text NOT NULL USING 
DEFERRABLE);
ERROR:  syntax error at or near "USING"
LINE 1: ...E test (id integer PRIMARY KEY, val text NOT NULL USING DEFE...
 ^
A syntax error.

test=> CREATE TABLE test (id integer PRIMARY KEY, val text NOT NULL DEFERRABLE);
ERROR:  misplaced DEFERRABLE clause
LINE 1: ...E test (id integer PRIMARY KEY, val text NOT NULL DEFERRABLE...
 ^
A syntactically correct statement that is nontheless incorrect.

test=> CREATE TABLE test (id integer PRIMARY KEY, val integer DEFAULT 'y');
ERROR:  invalid input syntax for integer: "y"

The same.

Yours,
Laurenz Albe

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

[GENERAL] Decrease the time required function

[Karel Riverón]

Hi everyone,

I have a PL/pgSQL function that it takes 4 seconds to execute. This is 
my function:


CREATE OR REPLACE FUNCTION listarcasosrecuperados(escenario_id integer)
  RETURNS SETOF caso_real AS
$BODY$

DECLARE

criterios CURSOR FOR SELECT * FROM criterio;

casos_reales CURSOR FOR SELECT * FROM caso_real;

sum_impactos NUMERIC DEFAULT 0;

sum_impacto_modulo NUMERIC DEFAULT 0;

impacto NUMERIC DEFAULT 0;

valor_caso_real_criterio NUMERIC DEFAULT 0;

valor_caso_escenario_criterio NUMERIC DEFAULT 0;

s NUMERIC DEFAULT 0.0;

c RECORD;

cr RECORD;

crc RECORD;

cec RECORD;

casos_escenarios_criterios RECORD;

casos_reales_criterios RECORD;

BEGIN

/*
 * RECORRER CURSOR DE CRITERIOS Y
 * SUMATORIA DE LOS IMPACTOS DE LOS CRITERIOS
 */
OPEN criterios;

LOOP FETCH criterios into c;

IF NOT FOUND THEN
EXIT;
ELSE
sum_impactos := sum_impactos + c.impacto;
END IF;

END LOOP;

CLOSE criterios;

/*
 * OBTENER CRITERIOS DEL CASO ESCENARIO PASADO POR PARAMETRO
 */

SELECT * INTO casos_escenarios_criterios FROM caso_escenario_criterio 
WHERE caso_escenario_id = $1;


/*
 * RECORRER CURSOR DE CASOS REALES *
 */
BEGIN
OPEN casos_reales;

LOOP FETCH casos_reales into cr;

IF NOT FOUND THEN
EXIT;
ELSE
sum_impacto_modulo := 0;

impacto := 0;

valor_caso_real_criterio := 0;

valor_caso_escenario_criterio := 0;

/*
* OBTENER CRITERIOS DEL CASO REAL EN CUESTIÓN
*/

SELECT * INTO casos_reales_criterios FROM caso_real_criterio WHERE 
caso_real_id = cr.id;


OPEN criterios;

LOOP FETCH criterios into c;

IF NOT FOUND THEN
EXIT;
ELSE

SELECT c_r_c.id, valor INTO crc
FROM caso_real_criterio c_r_c, caso_real c_r,criterio c_
WHERE c_.id = c_r_c.criterio_id
AND c_.id = c.id
AND c_r_c.caso_real_id = c_r.id
AND c_r.id = cr.id;

valor_caso_real_criterio := crc.valor;

SELECT c_e_c.id, valor INTO cec
FROM caso_escenario_criterio c_e_c, caso_escenario c_e,criterio c_
WHERE c_.id = c_e_c.criterio_id
AND c_.id = c.id
AND c_e_c.caso_escenario_id = c_e.id
AND c_e.id = escenario_id;

valor_caso_escenario_criterio := cec.valor;

impacto := c.impacto;

sum_impacto_modulo := sum_impacto_modulo + impacto * (1 - 
abs(valor_caso_real_criterio - valor_caso_escenario_criterio)/5);


END IF;

END LOOP;

CLOSE criterios;

s := sum_impacto_modulo / sum_impactos;

IF s >= 0.75 THEN
   RETURN NEXT cr;
END IF;

END IF;

END LOOP;

CLOSE casos_reales;

END;

END

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION listarcasosrecuperados(integer)
  OWNER TO postgres;

I need to decrease the time required function. Please, anyone helpme.

Regards, Karel Riverón
Students Scientific Council
Informatics Science University

[GENERAL] Bug, Feature, or what else?

[Andreas Kretschmer]

Hi,

i have created a normal user (no superuser) akretschmer01 and another normal
user ak02. All fine.
The first user is the owner of the db.


As user akretschmer01 i granted:

-bash-4.1$ psql -U akretschmer01 db115150
psql (9.1.8)
Type "help" for help.

db115150=> grant all on schema public to ak02;
GRANT
db115150=> commit;

There are no tables or other objects createt as user ak02.


Now i switched to user postgres (superuser):


-bash-4.1$ psql -U postgres db115150
psql (9.1.8)
Type "help" for help.

db115150=# drop user ak02
db115150-# ;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public

(role ak02 cannot be dropped because some objects depend on it, Detail:
privileges for schema public)

I can revoke all on database from user:

db115150=# revoke all on database db115150 from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public


but still i can't drop the user.


I can do that with 9.1.8 and 9.1.1 too, same problem.


How can i drop a user as SUPERUSER (!) with all privileges?



Regards, Andreas


-- 
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] Bug, Feature, or what else?

[Виктор Егоров]

2013/2/8 Andreas Kretschmer 

> How can i drop a user as SUPERUSER (!) with all privileges?
>

According to the docs:
http://www.postgresql.org/docs/current/interactive/sql-droprole.html

> A role cannot be removed if it is still referenced in any database of the
cluster;
> an error will be raised if so. Before dropping the role, you must drop
all the objects
> it owns (or reassign their ownership) and revoke any privileges the role
has been
> granted. The REASSIGN 
> OWNED
 and DROP 
OWNED
commands
can be useful for this purpose.


-- 
Victor Y. Yegorov

Re: [GENERAL] Decrease the time required function

[Albe Laurenz]

Karel Riverón wrote:
> I have a PL/pgSQL function that it takes 4 seconds to execute. This is my 
> function:
> 
> CREATE OR REPLACE FUNCTION listarcasosrecuperados(escenario_id integer)
>   RETURNS SETOF caso_real AS
[...]
> OPEN criterios;
> LOOP FETCH criterios into c;
> IF NOT FOUND THEN
> EXIT;
> ELSE
> sum_impactos := sum_impactos + c.impacto;
> END IF;
> END LOOP;
> CLOSE criterios;

This is very inefficient.
You should use as much SQL as possible:

SELECT sum(impacto) INTO sum_impactos FROM criterio;

> OPEN casos_reales;
> LOOP FETCH casos_reales into cr;
[...]
> OPEN criterios;
> LOOP FETCH criterios into c;
[...]
> SELECT c_r_c.id, valor INTO crc
> FROM caso_real_criterio c_r_c, caso_real c_r,criterio c_
[...]
> SELECT c_e_c.id, valor INTO cec
> FROM caso_escenario_criterio c_e_c, caso_escenario c_e,criterio c_
[...]
> END LOOP;
[...]
> END LOOP;

I did not study your processing in detail, but it looks
like most of that could be expressed as a single
SQL statement that joins the four tables
caso_real, criterio, caso_real_criterio and
caso_escenario_criterio.

Instead you program a nested loop in PL/pgSQL.
That is going to be inefficient.

> I need to decrease the time required function. Please, anyone helpme.

Write as much of your function as you can in SQL.

Yours,
Laurenz Albe


-- 
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] Bug, Feature, or what else?

[Albe Laurenz]

Andreas Kretschmer wrote:
> i have created a normal user (no superuser) akretschmer01 and another normal
> user ak02. All fine.
> The first user is the owner of the db.
> 
> 
> As user akretschmer01 i granted:

> db115150=> grant all on schema public to ak02;

> There are no tables or other objects createt as user ak02.
> 
> 
> Now i switched to user postgres (superuser):

> db115150=# drop user ak02
> db115150-# ;
> FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL:  Privilegien für Schema public
> 
> (role ak02 cannot be dropped because some objects depend on it, Detail:
> privileges for schema public)
> 
> I can revoke all on database from user:
> 
> db115150=# revoke all on database db115150 from ak02;
> REVOKE
> db115150=# drop user ak02;
> FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL:  Privilegien für Schema public
> 
> 
> but still i can't drop the user.
> 
> 
> I can do that with 9.1.8 and 9.1.1 too, same problem.
> 
> 
> How can i drop a user as SUPERUSER (!) with all privileges?

You have to revoke the permissions that you granted.

There are two options:

db115150=# DROP OWNED BY ak02;
DROP OWNED

or

db115150=# REVOKE ALL ON SCHEMA public FROM ak02;
REVOKE

Then you can drop the user.

Yours,
Laurenz Albe

-- 
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] Bug, Feature, or what else?

[Adrian Klaver]

On 02/08/2013 06:25 AM, Andreas Kretschmer wrote:

Hi,

i have created a normal user (no superuser) akretschmer01 and another normal
user ak02. All fine.
The first user is the owner of the db.


As user akretschmer01 i granted:

-bash-4.1$ psql -U akretschmer01 db115150
psql (9.1.8)
Type "help" for help.

db115150=> grant all on schema public to ak02;
GRANT
db115150=> commit;

There are no tables or other objects createt as user ak02.


Now i switched to user postgres (superuser):


-bash-4.1$ psql -U postgres db115150
psql (9.1.8)
Type "help" for help.

db115150=# drop user ak02
db115150-# ;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public

(role ak02 cannot be dropped because some objects depend on it, Detail:
privileges for schema public)

I can revoke all on database from user:

db115150=# revoke all on database db115150 from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public


but still i can't drop the user.


I can do that with 9.1.8 and 9.1.1 too, same problem.


How can i drop a user as SUPERUSER (!) with all privileges?


I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.

REVOKE ALL ON DATABASE does not mean revoke all privileges for all 
objects in the database. It only applies to DATABASE privileges-CONNECT, 
CREATE, TEMP






Regards, Andreas





--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Bug, Feature, or what else?

[Andreas Kretschmer]

> > How can i drop a user as SUPERUSER (!) with all privileges?
>
> You have to revoke the permissions that you granted.
>
> There are two options:
>
> db115150=# DROP OWNED BY ak02;
> DROP OWNED
>
> or
>
> db115150=# REVOKE ALL ON SCHEMA public FROM ak02;
> REVOKE
>
> Then you can drop the user.
>
> Yours,
> Laurenz Albe

nice idea, but unfortunately no:

db115150=# drop owned by ak02;
DROP OWNED
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public


Andreas


-- 
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] Bug, Feature, or what else?

[Andreas Kretschmer]

> > How can i drop a user as SUPERUSER (!) with all privileges?
>
> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.


doesn't help:

-bash-4.1$ psql -U postgres db115150
psql (9.1.8)
Type "help" for help.

db115150=# begin;
BEGIN
db115150=# revoke all on schema public from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public
db115150=#


-- 
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] Bug, Feature, or what else?

[Adrian Klaver]

On 02/08/2013 06:58 AM, Andreas Kretschmer wrote:



How can i drop a user as SUPERUSER (!) with all privileges?


I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.



doesn't help:

-bash-4.1$ psql -U postgres db115150
psql (9.1.8)
Type "help" for help.

db115150=# begin;
BEGIN
db115150=# revoke all on schema public from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public
db115150=#




So what does \dn+ public show?

--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Bug, Feature, or what else?

[Andreas Kretschmer]

Adrian Klaver  hat am 8. Februar 2013 um 16:19
geschrieben:
> On 02/08/2013 06:58 AM, Andreas Kretschmer wrote:
> >
> >>> How can i drop a user as SUPERUSER (!) with all privileges?
> >>
> >> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.
> >
> >
> > doesn't help:
> >
> > -bash-4.1$ psql -U postgres db115150
> > psql (9.1.8)
> > Type "help" for help.
> >
> > db115150=# begin;
> > BEGIN
> > db115150=# revoke all on schema public from ak02;
> > REVOKE
> > db115150=# drop user ak02;
> > FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> > DETAIL:  Privilegien für Schema public
> > db115150=#
> >
> >
>
> So what does \dn+ public show?

db115150=# \dn+ public
 List of schemas
  Name  |  Owner   |  Access privileges  |  Description
+--+-+
 public | postgres | postgres=UC/postgres   +| standard public schema
|  | akretschmer01=U*C*/postgres+|
|  | ak02=UC/akretschmer01   |
(1 row)


-- 
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] Visual query builder for PosgreSQL?

[Kevin Grittner]

Satoshi Nagayasu  wrote:

> Of course, I can write ad-hoc queries by myself. However, I'd
> like to allow non-tech people to issue ad-hoc queries with using
> some visual query builder.

You should probably take a look at http://htsql.org/

It is free open source software intended for "accidental
programmers" -- people who want to pull summarized data from a
database without learning SQL or needing rigorous training.  Its
development was partially funded by grants from foundations,
including the National Science Foundation.  It does support
PostgreSQL and most definitely support counts, sums, etc.  In fact,
it can automagically give you pretty summary graphs with the
ability to drill down to supporting detail.

-Kevin



-- 
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] Bug, Feature, or what else?

[Adrian Klaver]

On 02/08/2013 07:23 AM, Andreas Kretschmer wrote:



Adrian Klaver  hat am 8. Februar 2013 um 16:19
geschrieben:

On 02/08/2013 06:58 AM, Andreas Kretschmer wrote:



How can i drop a user as SUPERUSER (!) with all privileges?


I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.



doesn't help:

-bash-4.1$ psql -U postgres db115150
psql (9.1.8)
Type "help" for help.

db115150=# begin;
BEGIN
db115150=# revoke all on schema public from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public
db115150=#




So what does \dn+ public show?


db115150=# \dn+ public
  List of schemas
   Name  |  Owner   |  Access privileges  |  Description
+--+-+
  public | postgres | postgres=UC/postgres   +| standard public schema
 |  | akretschmer01=U*C*/postgres+|
 |  | ak02=UC/akretschmer01   |
(1 row)




If I am following the Notes section correctly in:

http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html

"If a superuser chooses to issue a GRANT or REVOKE command, the command 
is performed as though it were issued by the owner of the affected 
object. Since all privileges ultimately come from the object owner 
(possibly indirectly via chains of grant options), it is possible for a 
superuser to revoke all privileges, but this might require use of 
CASCADE as stated above."



Try:

revoke all on schema public from ak02 cascade;


--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Visual query builder for PosgreSQL?

[Adrian Klaver]

On 02/08/2013 07:33 AM, Kevin Grittner wrote:

Satoshi Nagayasu  wrote:


Of course, I can write ad-hoc queries by myself. However, I'd
like to allow non-tech people to issue ad-hoc queries with using
some visual query builder.


You should probably take a look at http://htsql.org/

It is free open source software intended for "accidental
programmers" -- people who want to pull summarized data from a
database without learning SQL or needing rigorous training.  Its
development was partially funded by grants from foundations,
including the National Science Foundation.  It does support
PostgreSQL and most definitely support counts, sums, etc.  In fact,
it can automagically give you pretty summary graphs with the
ability to drill down to supporting detail.


I second this. I have been trying it out and it is proving quite useful. 
The interesting part is that if you use the HTML interface you can get 
the SQL sent to the server, helps you learn that also.




-Kevin






--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Bug, Feature, or what else?

[Andreas Kretschmer]

>
> If I am following the Notes section correctly in:
>
> http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html
>
> "If a superuser chooses to issue a GRANT or REVOKE command, the command
> is performed as though it were issued by the owner of the affected
> object. Since all privileges ultimately come from the object owner
> (possibly indirectly via chains of grant options), it is possible for a
> superuser to revoke all privileges, but this might require use of
> CASCADE as stated above."
>
>
> Try:
>
> revoke all on schema public from ak02 cascade;

Yeah, i read that before, but doesn't help:



db115150=# revoke all on schema public from ak02 cascade;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public


Strange, isn't it?


Andreas


-- 
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] Bug, Feature, or what else?

[Adrian Klaver]

On 02/08/2013 07:45 AM, Andreas Kretschmer wrote:




If I am following the Notes section correctly in:

http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html

"If a superuser chooses to issue a GRANT or REVOKE command, the command
is performed as though it were issued by the owner of the affected
object. Since all privileges ultimately come from the object owner
(possibly indirectly via chains of grant options), it is possible for a
superuser to revoke all privileges, but this might require use of
CASCADE as stated above."


Try:

revoke all on schema public from ak02 cascade;


Yeah, i read that before, but doesn't help:



db115150=# revoke all on schema public from ak02 cascade;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public


Strange, isn't it?


Well I got left is:

REVOKE PUBLIC FROM ak02;




Andreas





--
Adrian Klaver
adrian.kla...@gmail.com


--
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] pl/java for postgresql 9.2

[Marc Brazeau]

the problem is that there are no 9.2 binaries for pl/java for windows.
Building them for Mac/Unix wasn't too difficult.  I've spent 2 days trying
to get them building on Windows with no success.

Surely someone out there's using pl/java on PostgreSQL 9.2?

-- Forwarded message --
From: Pavel Stehule 
Date: Fri, Feb 8, 2013 at 12:32 AM
Subject: Re: [GENERAL] pl/java for postgresql 9.2
To: Marc Brazeau 
Cc: pgsql-general@postgresql.org


Hello

2013/2/7 Marc Brazeau :
> Hoping someone can help me here.
>
> I'm trying to get pl/java running under Postgres 9.2.  Doing so was
> relatively easy on the Mac.  But on Windows, its turning out to be quite
> problematic.
>
> There are no recent binaries available, and if I try with the ones on
> pgFoundry, I get the following error:
> ERROR:  incompatible library "C:/Program
> Files/PostgreSQL/9.2/lib/pljava.dll": version mismatch
> DETAIL:  Server is version 9.2, library is version 9.1.

You cannot to mix libraries and extensions for different mayor
PostgreSQL versions

>>>DETAIL:  Server is version 9.2, library is version 9.1.<<<

Regards

Pavel Stehule

Re: [GENERAL] Bug, Feature, or what else?

[Andreas Kretschmer]

Adrian Klaver  hat am 8. Februar 2013 um 16:48
geschrieben:
> On 02/08/2013 07:45 AM, Andreas Kretschmer wrote:
> >
> >>
> >> If I am following the Notes section correctly in:
> >>
> >> http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html
> >>
> >> "If a superuser chooses to issue a GRANT or REVOKE command, the command
> >> is performed as though it were issued by the owner of the affected
> >> object. Since all privileges ultimately come from the object owner
> >> (possibly indirectly via chains of grant options), it is possible for a
> >> superuser to revoke all privileges, but this might require use of
> >> CASCADE as stated above."
> >>
> >>
> >> Try:
> >>
> >> revoke all on schema public from ak02 cascade;
> >
> > Yeah, i read that before, but doesn't help:
> >
> >
> >
> > db115150=# revoke all on schema public from ak02 cascade;
> > REVOKE
> > db115150=# drop user ak02;
> > FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> > DETAIL:  Privilegien für Schema public
> >
> >
> > Strange, isn't it?
>
> Well I got left is:
>
> REVOKE PUBLIC FROM ak02;
>

i think you mean:

db115150=# revoke all on schema public from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public


-- 
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] Bug, Feature, or what else?

[Albe Laurenz]

Andreas Kretschmer wrote:
>>> db115150=# revoke all on schema public from ak02;
>>> REVOKE
>>> db115150=# drop user ak02;
>>> FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
>>> DETAIL:  Privilegien für Schema public
>>
>> So what does \dn+ public show?
> 
> db115150=# \dn+ public
>  List of schemas
>   Name  |  Owner   |  Access privileges  |  Description
> +--+-+
>  public | postgres | postgres=UC/postgres   +| standard public schema
> |  | akretschmer01=U*C*/postgres+|
> |  | ak02=UC/akretschmer01   |
> (1 row)

Seems like you can't revoke privileges you didn't grant,
even as superuser.

Try:

SET SESSION AUTHORIZATION akretschmer01;
REVOKE ALL ON SCHEMA PUBLIC FROM ak02;
RESET SESSION AUTHORIZATION;
DROP USER ak02;

Yours,
Laurenz Albe

-- 
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] Bug, Feature, or what else?

[Tom Lane]

Andreas Kretschmer  writes:
> Adrian Klaver  hat am 8. Februar 2013 um 16:19
>> So what does \dn+ public show?

> db115150=# \dn+ public
>  List of schemas
>   Name  |  Owner   |  Access privileges  |  Description
> +--+-+
>  public | postgres | postgres=UC/postgres   +| standard public schema
> |  | akretschmer01=U*C*/postgres+|
> |  | ak02=UC/akretschmer01   |
> (1 row)

Ah: this shows that you didn't tell us the whole truth to start with.
What you've actually got here is that postgres granted ALL WITH GRANT
OPTION to akretschmer01, and then akretschmer01 used the grant option
to grant rights to ak02.  (I was wondering how it was that a non
superuser would be able to grant anything about schema public...)

Only akretschmer01 can directly drop the grant to ak02.  What postgres
could do is revoke the grant option to akretschmer01, and the cascaded
effect of that would remove the privileges for ak02.

Of course, postgres has other options besides that, of which "DROP OWNED
BY ak02" is probably the most appropriate here.  Or if you really want
to get rid of just that grant, SET ROLE TO akretschmer01 and revoke.

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] Bug, Feature, or what else?

[Adrian Klaver]

On 02/08/2013 08:14 AM, Tom Lane wrote:

Andreas Kretschmer  writes:

Adrian Klaver  hat am 8. Februar 2013 um 16:19

So what does \dn+ public show?



db115150=# \dn+ public
  List of schemas
   Name  |  Owner   |  Access privileges  |  Description
+--+-+
  public | postgres | postgres=UC/postgres   +| standard public schema
 |  | akretschmer01=U*C*/postgres+|
 |  | ak02=UC/akretschmer01   |
(1 row)


Ah: this shows that you didn't tell us the whole truth to start with.
What you've actually got here is that postgres granted ALL WITH GRANT
OPTION to akretschmer01, and then akretschmer01 used the grant option
to grant rights to ak02.  (I was wondering how it was that a non
superuser would be able to grant anything about schema public...)

Only akretschmer01 can directly drop the grant to ak02.  What postgres
could do is revoke the grant option to akretschmer01, and the cascaded
effect of that would remove the privileges for ak02.

Of course, postgres has other options besides that, of which "DROP OWNED
BY ak02" is probably the most appropriate here.  Or if you really want
to get rid of just that grant, SET ROLE TO akretschmer01 and revoke.


The DROP OWNED was tried further up the thread and did not seem to work:

"
nice idea, but unfortunately no:

db115150=# drop owned by ak02;
DROP OWNED
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public

"




regards, tom lane





--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Bug, Feature, or what else?

[Adrian Klaver]

On 02/08/2013 07:57 AM, Andreas Kretschmer wrote:


Well I got left is:

REVOKE PUBLIC FROM ak02;



i think you mean:

db115150=# revoke all on schema public from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER:  kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL:  Privilegien für Schema public



No I was trying to revoke membership in the PUBLIC role for ak02, on the 
chance that ak02 was picking up privileges from it. I do not know if 
that is even possible, but at this point I am grasping at straws.



--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Bug, Feature, or what else?

[Andreas Kretschmer]

Tom Lane  wrote:

> Andreas Kretschmer  writes:
> > Adrian Klaver  hat am 8. Februar 2013 um 16:19
> >> So what does \dn+ public show?
> 
> > db115150=# \dn+ public
> >  List of schemas
> >   Name  |  Owner   |  Access privileges  |  Description
> > +--+-+
> >  public | postgres | postgres=UC/postgres   +| standard public schema
> > |  | akretschmer01=U*C*/postgres+|
> > |  | ak02=UC/akretschmer01   |
> > (1 row)
> 
> Ah: this shows that you didn't tell us the whole truth to start with.
> What you've actually got here is that postgres granted ALL WITH GRANT
> OPTION to akretschmer01, and then akretschmer01 used the grant option
> to grant rights to ak02.  (I was wondering how it was that a non
> superuser would be able to grant anything about schema public...)
> 
> Only akretschmer01 can directly drop the grant to ak02.  What postgres
> could do is revoke the grant option to akretschmer01, and the cascaded
> effect of that would remove the privileges for ak02.
> 
> Of course, postgres has other options besides that, of which "DROP OWNED
> BY ak02" is probably the most appropriate here.  Or if you really want
> to get rid of just that grant, SET ROLE TO akretschmer01 and revoke.
> 
>   regards, tom lane

Thanks. A colleague of me (the author of the wiki-artikel) says:


Thanks for your reply.

Sorry to have been unclear, but yes the grants in question
were created by an intermediate admin.

(cf. http://wiki.postgresql.org/wiki/Shared_Database_Hosting, with
DBMAINUSER=akretschmer01 and DBEXTRAUSER=ak02).

Just as a side note, the user that granted the "ALL WITH
GRANT" to akretschmer01 wasn't actually postgres but
an additional supervisor role with a different name, yet
still it says postgres in the \dn+ output.

Anyway, I get that a non-super-user role may only
revoke permissions that it originally granted. But I
am a bit confused about the following paragraph from
the docs:

"
If a superuser chooses to issue a GRANT or REVOKE command,
the command is performed as though it were issued by the owner
of the affected object. Since all privileges ultimately come 
from the object owner (possibly indirectly via chains of 
grant options), it is possible for a superuser to revoke 
all privileges, but this might require use of CASCADE as stated above.
"

http://www.postgresql.org/docs/9.2/static/sql-grant.html 

And yes we really only want to get rid of the grants
of the DBEXTRAUSER, the rational in a shared hosting
scenario being that DBMAINUSER might have granted a
plethora of rights to DBEXTRAUSER via the psql,
but still should be able to remove a DBEXTRAUSER at
any time with just a click of a button (in the hosting panel)
*without* affecting the existence of any objects.


Basically we are trying to emulate a command
like 

DROP ROLE  ALSO REMOVING ALL GRANTS TO ROLE 
TO ANY OBJECT IN ANY DATABASE.

(after doing a REASSIGN OWNED BY  TO )

Regards,
Thomas






Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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_shdepend vacuum.

[Jeff Janes]

On Thu, Feb 7, 2013 at 3:47 AM, Ranjeet Dhumal  wrote:
> Hi All ,
>
> Am using postgres version 9.0.1 .

You should upgrade to the latest minor release.  A lot of things have
been fixed since 9.0.1, including better logging of autovacuum cancels
(in 9.0.9) which will tell you what is causing the cancel.


> In daily postgres log am getting same kind of log for many time , is it
> because of more table creation and deletion is going on and its trying to
> vaccum pg_shdepend ?
> But its getting canceled every time , why is it so ?

How do you know it is canceled every time?  Unless you have cranked up
your logging, success will not be logged, only cancels.


Cheers,

Jeff


-- 
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] Bug, Feature, or what else?

[Andreas Kretschmer]

Andreas Kretschmer  wrote:

> How can i drop a user as SUPERUSER (!) with all privileges?

Okay, i think, it's time to say 'thank you' to all on this list!

It's great, only a few hours and our observation confirmed.

I (we) think, this is a bug. We will make a workaround for us in the
next days, and we will keep you informed about our solution.


Again, many thanks from /me and my colleague, not only to Tom!


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


-- 
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] REINDEX deadlock - Postgresql -9.1

[Scott Marlowe]

You might want to consider adding a pooler like pgbouncer to the
equation so that the pooler is what runs out of connections and not
the database.  Then you could at least get into it to fix things.

On Thu, Feb 7, 2013 at 9:04 PM, Anoop K  wrote:
> REINDEX was for the whole database. It seems REINDEX was blocked by the
>  process.
>
> What we are not able to explain is how that connection went in to  transaction> state. The app stacktrace confirms that app (JDBC) is trying to
> open a connection. We do close connection after use.
> So can't think how transaction went in to idle state.
>
> Thanks
> Anoop
>
>
> On Fri, Feb 8, 2013 at 12:14 AM, Tom Lane  wrote:
>>
>> Pavan Deolasee  writes:
>> > Sorry, I was going to ask what REINDEX was really indexing ? System
>> > tables ?
>>
>> The stack trace for the REINDEX process includes ReindexDatabase(), so
>> if it was running as a superuser it would be trying to reindex system
>> catalogs too.  We don't actually know that the particular table it's
>> working on at the moment is a system catalog, but that seems like a
>> fairly good guess.  The process that's blocked in startup is definitely
>> blocked on somebody's exclusive lock (or at least exclusive lock
>> request) on a system catalog index, and there are not that many
>> operations besides REINDEX that would take out such a lock.
>>
>> I'm guessing that something holds a lock (maybe only AccessShareLock)
>> on a system catalog index, and REINDEX is blocked trying to get
>> exclusive lock on that index, and then all incoming processes are
>> queuing up behind REINDEX's request, since they'll all be trying
>> to open the same set of catcache-supporting indexes.
>>
>> > ISTM that the idle in transaction connection was holding some
>> > kind of a heavy weight lock on one of the catalog tables and that may
>> > be causing all other transactions to just wait.
>>
>> It doesn't need to have been an exclusive lock to block REINDEX.
>> I suspect this theory is correct otherwise, because if it were a
>> true deadlock the deadlock detector should have noticed it.  If it's
>> just "everybody is blocked behind that idle transaction", the deadlock
>> detector will not think that it should do anything about it.
>>
>> regards, tom lane
>
>



-- 
To understand recursion, one must first understand recursion.


-- 
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] Decrease the time required function

[Kevin Grittner]

Albe Laurenz  wrote:
> Karel Riverón wrote:

>> I have a PL/pgSQL function that it takes 4 seconds to execute.

>> OPEN casos_reales;
>> LOOP FETCH casos_reales into cr;
> [...]
>> OPEN criterios;
>> LOOP FETCH criterios into c;
> [...]
>> SELECT c_r_c.id, valor INTO crc
>> FROM caso_real_criterio c_r_c, caso_real c_r,criterio c_
> [...]
>> SELECT c_e_c.id, valor INTO cec
>> FROM caso_escenario_criterio c_e_c, caso_escenario c_e,criterio c_
> [...]
>> END LOOP;
> [...]
>> END LOOP;
>
> I did not study your processing in detail, but it looks
> like most of that could be expressed as a single
> SQL statement that joins the four tables

+1

On a quick look, it seems like someting along these lines might be
what you want (although I had to guess a little at schema, since
you didn't show it):

SELECT cr.*
  FROM (SELECT sum(impactos) AS sum_impactos FROM criterio) si
  CROSS JOIN criterio c
  JOIN caso_real_criterio crc ON (crc.criterio_id = c.id)
  JOIN caso_real cr ON (cr.id = crc.caso_real_id)
  JOIN caso_escenario_criterio cec ON (cec.criterio_id = c.id)
  JOIN caso_escenario ce ON (ce.id = cec.caso_escenario_id)
  WHERE ce.id = escenario_id
  GROUP BY cr.id
  HAVING sum(c.impacto * (1 - abs(crc.valor - cec.valor) / 5))
   / si.sum_impactos >= 0.75
;

You might need to adjust the GROUP BY clause if you're not running
a recent major release.  If you want to keep it as a function, you
can throw out the DECLARE section and everything between the
outermost BEGIN and END, and replace it with RETURN QUERY and the
above query, or turn it into a SQL function to avoid the overhead
of materializing the entire result set.

If you get some form of that to run, please post back with a
comparison of run times.

-Kevin


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

[GENERAL] no implicit cast error in 9.2?

[AI Rumman]

Hi,

I am using
PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6
20120305 (Red Hat 4.4.6-4), 64-bit.

I got a bit confused after installing this version. So far I used to know
that from Postgresql 8.3 implicit casting has been removed and the
following should not work at 8.3 :
create table testtab ( id varchar, id1 int)
insert into testtab values (1,1);

Where it was good to work at 8.1

http://osdir.com/ml/pgsql-general/2011-02/msg00055.html

I also faced the problem earlier.

But in 9.2 it is working perfectly.
Am I missing any release notes?

Please share your opinion. It will be appreciated.

Thanks.

[GENERAL] Swapping volumes under tablespaces: supported?

[Kenneth Tilton]

Currently we refresh our test DB instance by cloning the single production
EC2 volume we use for our entire PG environment and attaching it to the dev
EC2 instance running Postgres. This works well.

But now we are about to add a large quantity of largely static data to our
database. To avoid cloning the static data every time we refresh test
(several times a day), we want to put the new data in its own tablespace
pointing to a different volume, then clone only the default volume to which
the Postgres and default spaces point.

Is that supported?

What if we wanted to replace just the static data? In this case the
unchanged volume would contain the Postgres metadata and we would be
swapping in only the static data volume. Given the important constraint
that nothing can have changed in re the metadata, would that be supported?

My concern is that Postgres might have some internal checksum or something
that would detect our volume swapping and complain on principle. As long as
we are meticulous about not changing the metadata* in the Postgres internal
DBs between volume-swaps, will PG play along?

 * When we do make metadata changes we'll take the hit and clone everything.

-- 
Kenneth Tilton

*Director of Software Development*

*MCNA Dental Plans*
200 West Cypress Creek Road
Suite 500
Fort Lauderdale, FL 33309

954-730-7131 X181 (Office)
954-628-3347 (Fax)
1-800-494-6262 X181 (Toll Free)

ktil...@mcna.net  (Email)

www.mcna.net (Website)
CONFIDENTIALITY NOTICE: This electronic mail may contain information that
is privileged, confidential, and/or otherwise protected from disclosure to
anyone other than its intended recipient(s). Any dissemination or use of
this electronic mail or its contents by persons other than the intended
recipient(s) is strictly prohibited. If you have received this
communication in error, please notify the sender immediately by reply
e-mail so that we may correct our internal records. Please then delete the
original message. Thank you.

[GENERAL] Restoring a database dump from 9.0 to 9.2

[Jay McGaffigan]

Hi,
  I've been trying to restore a fairly sizeable database dump from my
production server onto my dev box.
Recently upgraded to 9.2.2 and wanted to try it out.

So I grabbed a text dump of the database and tried the "Createdb dbname;
psql < dmpfile" way of restoring that's always worked for me before
upgrading my dev box and I'm getting errors on import.  Some of my columns
have 'rich text' (carriage returns, XML and other markup) in it and I
suspect they are causing the issues (basically the errors I'm seeing seem
to imply that the text formatting is getting out of wack I'm suspecting due
to carriage returns embedded in the dump file).This causes lots of
errors as the processing of the file is now out of sync.

I had been able to load this same file under PSql 9.1.

If I get a binary dump file that I need to use something like pg_restore
with .  it runs for over 12 hrs locks up my mac adn uses all system memory
(i've 16G RAM on my system)

This db is like 30G in size.

Any one have any debugging advice?  I'm thinking if I can use the text
based dump that is created with proper escaping then things might work.
 But so far reading documentation I haven't really figured out if this is a
viable path.

If this is not a good way to do it I'm open for any and all suggestions.

Thanks!
Jay

Re: [GENERAL] no implicit cast error in 9.2?

[Adrian Klaver]

On 02/08/2013 12:23 PM, AI Rumman wrote:

Hi,

I am using
PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit.

I got a bit confused after installing this version. So far I used to
know that from Postgresql 8.3 implicit casting has been removed and the
following should not work at 8.3 :
create table testtab ( id varchar, id1 int)
insert into testtab values (1,1);

Where it was good to work at 8.1

http://osdir.com/ml/pgsql-general/2011-02/msg00055.html

I also faced the problem earlier.

But in 9.2 it is working perfectly.
Am I missing any release notes?

Please share your opinion. It will be appreciated.


As I remember implicit casting was not entirely removed and the
text <--> int combination was kept.

To extend your example, you can see it works both ways::

test=# create table testtab ( id varchar, id1 int);
CREATE TABLE
test=# insert into testtab values (1,1);
INSERT 0 1
test=# insert into testtab values (1,'1');
INSERT 0 1
test=# SELECT * from testtab ;
 id | id1
+-
 1  |   1
 1  |   1
(2 rows)


Unless of course you use a non-integer string:

test=# insert into testtab values (1,'one');
ERROR:  invalid input syntax for integer: "one"
LINE 1: insert into testtab values (1,'one');




Thanks.



--
Adrian Klaver
adrian.kla...@gmail.com


--
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] no implicit cast error in 9.2?

[Kevin Grittner]

Adrian Klaver  wrote:
> On 02/08/2013 12:23 PM, AI Rumman wrote:

>> I got a bit confused after installing this version. So far I used to
>> know that from Postgresql 8.3 implicit casting has been removed and the
>> following should not work at 8.3 :
>> create table testtab ( id varchar, id1 int)
>> insert into testtab values (1,1);
>>
>> Where it was good to work at 8.1
>>
>> http://osdir.com/ml/pgsql-general/2011-02/msg00055.html
>>
>> I also faced the problem earlier.
>>
>> But in 9.2 it is working perfectly.
>> Am I missing any release notes?
>>
>> Please share your opinion. It will be appreciated.
>
> As I remember implicit casting was not entirely removed and the
> text <--> int combination was kept.

I think it has more to do with retaining (or adding back, I don't
recall) *assignment* casts which aren't supported as *implicit*
casts.

test=# select '1'::int = '1'::text;
ERROR:  operator does not exist: integer = text
LINE 1: select '1'::int = '1'::text;
    ^
HINT:  No operator matches the given name and argument type(s). You might need 
to add explicit type casts.

-Kevin



-- 
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] Restoring a database dump from 9.0 to 9.2

[Steve Crawford]

On 02/08/2013 12:43 PM, Jay McGaffigan wrote:

Hi,
  I've been trying to restore a fairly sizeable database dump from my 
production server onto my dev box.

Recently upgraded to 9.2.2 and wanted to try it out.

So I grabbed a text dump of the database and tried the "Createdb 
dbname; psql < dmpfile" way of restoring that's always worked for me 
before upgrading my dev box and I'm getting errors on import.  Some of 
my columns have 'rich text' (carriage returns, XML and other markup) 
in it and I suspect they are causing the issues (basically the errors 
I'm seeing seem to imply that the text formatting is getting out of 
wack I'm suspecting due to carriage returns embedded in the dump 
file).  This causes lots of errors as the processing of the file is 
now out of sync.
Posting the actual errors would be a good start. Also, are you using the 
9.2 version of pg_dump or trying to restore a dump you took with 9.0 
tools into 9.2? If you are upgrading, you should always use the dump 
tools from the *new* version.


If I get a binary dump file that I need to use something like 
pg_restore with .  it runs for over 12 hrs locks up my mac adn uses 
all system memory (i've 16G RAM on my system)
Pg_restore from a custom dump *should* be faster - especially if you are 
able to use the parallel restore features. What do you mean "locks up my 
mac"? Are you sure you don't have a hardware issue? (Note: I've had more 
than one server with bad RAM that was not found after a week-long 
burn-in with memtest but caused repeatable crashes running pgbench and 
which was completely fixed by a RAM replacement.)


Cheers,
Steve



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

[GENERAL] var/log/postgresql deletion mystery Ubuntu 12.10

[Andrew Taylor]

Hi,

I have to create this directory each time I want to start the server.
Something is deleting it when I close down or start up my laptop.

Any suggestions as to what could be doing this, or how I could find out? I
presently have version 9.1 installed.

All I can add is "it used to work!". Since then I have upgraded to ubuntu
12.10 from 12.04 and installed mongodb amongst other things.

Regards,

Andy

Re: [GENERAL] var/log/postgresql deletion mystery Ubuntu 12.10

[Tom Lane]

Andrew Taylor  writes:
> I have to create this directory each time I want to start the server.
> Something is deleting it when I close down or start up my laptop.

> Any suggestions as to what could be doing this, or how I could find out? I
> presently have version 9.1 installed.

> All I can add is "it used to work!". Since then I have upgraded to ubuntu
> 12.10 from 12.04 and installed mongodb amongst other things.

Over in Fedora-land they've recently switched to a scheme whereby most
"temporary" directories are deleted at reboot, so that packages need to
arrange for their recreation.  Is it possible something similar is going
on in your newer Ubuntu version?  It would seem to me to be the height
of stupidity to flush log files this way, but it's hard to think of
another reason for the directory to disappear.  If all other stuff under
/var/log is deleted or reset at reboot, then this could be the explanation.

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] no implicit cast error in 9.2?

[Tom Lane]

AI Rumman  writes:
> I got a bit confused after installing this version. So far I used to know
> that from Postgresql 8.3 implicit casting has been removed and the
> following should not work at 8.3 :
> create table testtab ( id varchar, id1 int)
> insert into testtab values (1,1);

No, that will work fine in any version, because you're calling upon an
assignment cast not an implicit cast.  What once worked and no longer
does is cases like

SELECT length(1);

where the integer argument used to be implicitly cast to text.

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] Swapping volumes under tablespaces: supported?

[Tom Lane]

Kenneth Tilton  writes:
> Currently we refresh our test DB instance by cloning the single production
> EC2 volume we use for our entire PG environment and attaching it to the dev
> EC2 instance running Postgres. This works well.

> But now we are about to add a large quantity of largely static data to our
> database. To avoid cloning the static data every time we refresh test
> (several times a day), we want to put the new data in its own tablespace
> pointing to a different volume, then clone only the default volume to which
> the Postgres and default spaces point.

> Is that supported?

No.  XID and table-OID considerations are going to give you headaches.

It's possible you can make it work as long as the static data is really
static (not "largely" static, but absolutely read-only) and you VACUUM
FREEZE all of it and then don't change the catalog entries about it in
the DB image being cloned.  But you shouldn't imagine that we consider
this supported.  If it breaks you get to keep both pieces.

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] no implicit cast error in 9.2?

[Adrian Klaver]

On 02/08/2013 01:50 PM, Kevin Grittner wrote:

Adrian Klaver  wrote:

On 02/08/2013 12:23 PM, AI Rumman wrote:



I got a bit confused after installing this version. So far I used to
know that from Postgresql 8.3 implicit casting has been removed and the
following should not work at 8.3 :
create table testtab ( id varchar, id1 int)
insert into testtab values (1,1);

Where it was good to work at 8.1

http://osdir.com/ml/pgsql-general/2011-02/msg00055.html

I also faced the problem earlier.

But in 9.2 it is working perfectly.
Am I missing any release notes?

Please share your opinion. It will be appreciated.


As I remember implicit casting was not entirely removed and the
text <--> int combination was kept.


I think it has more to do with retaining (or adding back, I don't
recall) *assignment* casts which aren't supported as *implicit*
casts.


Ah, so that is the reason. A distinction I missed:(



--
Adrian Klaver
adrian.kla...@gmail.com


--
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] Restoring a database dump from 9.0 to 9.2

[Tom Lane]

Jay McGaffigan  writes:
>   I've been trying to restore a fairly sizeable database dump from my
> production server onto my dev box.
> Recently upgraded to 9.2.2 and wanted to try it out.

> So I grabbed a text dump of the database and tried the "Createdb dbname;
> psql < dmpfile" way of restoring that's always worked for me before
> upgrading my dev box and I'm getting errors on import.  Some of my columns
> have 'rich text' (carriage returns, XML and other markup) in it and I
> suspect they are causing the issues (basically the errors I'm seeing seem
> to imply that the text formatting is getting out of wack I'm suspecting due
> to carriage returns embedded in the dump file).

If you showed us the exact error messages rather than hand-waving, we
might be able to help, but it's hard to say much with so little detail.

Note that you generally want to look at the first few errors not the
last few, as pg_dump scripts tend to be very prone to cascading-error
syndrome.

Another thing that's often helpful is to see if you can restore a
schema-only dump (pg_dump -s), as that lets you separate schema problems
from data problems, and get any of the former resolved before you deal
with the latter.

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] Restoring a database dump from 9.0 to 9.2

[Ben Madin]

If Tom's suggestion doesn't work, can you do your text dump by schema,
or for a subset of tables, and see if you can isolate the problem
table. (using the -n or -t options)

Have you changed the locale / languages settings between db versions?
If you find a quoting problem in a very large table you can run it
through sed to quote the offending bits

If you can dump from 9.0 and restore into 9.1, can you dump from 9.1
and try restoring it into 9.2?

Cheers

Ben



-- 

Ben Madin

t: +61 8 6102 5535
m: +61 448 887 220

Sent from my iPhone, hence the speling...

On 09/02/2013, at 4:46, Jay McGaffigan  wrote:

> Hi,
>  I've been trying to restore a fairly sizeable database dump from my 
> production server onto my dev box.
> Recently upgraded to 9.2.2 and wanted to try it out.
>
> So I grabbed a text dump of the database and tried the "Createdb dbname; psql 
> < dmpfile" way of restoring that's always worked for me before upgrading my 
> dev box and I'm getting errors on import.  Some of my columns have 'rich 
> text' (carriage returns, XML and other markup) in it and I suspect they are 
> causing the issues (basically the errors I'm seeing seem to imply that the 
> text formatting is getting out of wack I'm suspecting due to carriage returns 
> embedded in the dump file).This causes lots of errors as the processing 
> of the file is now out of sync.
>
> I had been able to load this same file under PSql 9.1.
>
> If I get a binary dump file that I need to use something like pg_restore with 
> .  it runs for over 12 hrs locks up my mac adn uses all system memory (i've 
> 16G RAM on my system)
>
> This db is like 30G in size.
>
> Any one have any debugging advice?  I'm thinking if I can use the text based 
> dump that is created with proper escaping then things might work.  But so far 
> reading documentation I haven't really figured out if this is a viable path.
>
> If this is not a good way to do it I'm open for any and all suggestions.
>
> Thanks!
> Jay


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

[GENERAL] Calling ROUND w/o a numeric cast blowing up all connections (9.1)

[Wells Oliver]

I don't know why this is happening, but it's infuriating. From the psql
prompt:

mydb=# select round(5/2, 1);
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Failed.

This kills any connection to the database. Doing 'round(5/2::numeric, 1)'
works fine.

Version: PostgreSQL 9.1.7 on x86_64-unknown-linux-gnu, compiled by
gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit

Nothing shows up in the log.

Have I broken my cast function? My round function? Have I angered the RDMS
gods? Can anyone give me any pointers?

-- 
Wells Oliver
wellsoli...@gmail.com

Re: [GENERAL] Calling ROUND w/o a numeric cast blowing up all connections (9.1)

[Tom Lane]

Wells Oliver  writes:
> I don't know why this is happening, but it's infuriating. From the psql
> prompt:

> mydb=# select round(5/2, 1);
> SSL SYSCALL error: EOF detected

Huh.  Works for me ...

> Nothing shows up in the log.

Either your logging is broken or you're looking in the wrong log, I
think, because that sure looks like a backend crash.  And the postmaster
would certainly bleat about a backend crash.

> Have I broken my cast function? My round function? Have I angered the RDMS
> gods? Can anyone give me any pointers?

Dunno, have you messed around with either casting or round()?  Can you
reproduce this in a freshly-created database?

FWIW, a stock database ought to have these versions of round():

postgres=# \df round
  List of functions
   Schema   | Name  | Result data type | Argument data types |  Type  
+---+--+-+
 pg_catalog | round | double precision | double precision| normal
 pg_catalog | round | numeric  | numeric | normal
 pg_catalog | round | numeric  | numeric, integer| normal
(3 rows)


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] Calling ROUND w/o a numeric cast blowing up all connections (9.1)

[Wells Oliver]

Ah, bananas. Someone had created a round(double, integer) function in
public that did some shenanigans. Now I've wasted everyone's time.

Though, I do find it odd that it could cause such a crash, bad function or
no.


On Fri, Feb 8, 2013 at 5:13 PM, Tom Lane  wrote:

> Wells Oliver  writes:
> > I don't know why this is happening, but it's infuriating. From the psql
> > prompt:
>
> > mydb=# select round(5/2, 1);
> > SSL SYSCALL error: EOF detected
>
> Huh.  Works for me ...
>
> > Nothing shows up in the log.
>
> Either your logging is broken or you're looking in the wrong log, I
> think, because that sure looks like a backend crash.  And the postmaster
> would certainly bleat about a backend crash.
>
> > Have I broken my cast function? My round function? Have I angered the
> RDMS
> > gods? Can anyone give me any pointers?
>
> Dunno, have you messed around with either casting or round()?  Can you
> reproduce this in a freshly-created database?
>
> FWIW, a stock database ought to have these versions of round():
>
> postgres=# \df round
>   List of functions
>Schema   | Name  | Result data type | Argument data types |  Type
> +---+--+-+
>  pg_catalog | round | double precision | double precision| normal
>  pg_catalog | round | numeric  | numeric | normal
>  pg_catalog | round | numeric  | numeric, integer| normal
> (3 rows)
>
>
> regards, tom lane
>



-- 
Wells Oliver
wellsoli...@gmail.com

Re: [GENERAL] Calling ROUND w/o a numeric cast blowing up all connections (9.1)

[Tom Lane]

Wells Oliver  writes:
> Ah, bananas. Someone had created a round(double, integer) function in
> public that did some shenanigans. Now I've wasted everyone's time.

> Though, I do find it odd that it could cause such a crash, bad function or
> no.

Well, if it was a misdeclared alias for a C function, it's not
surprising at all.  If it was something else, it'd be interesting to see
what.

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] Swapping volumes under tablespaces: supported?

[Gavan Schneider]

On Friday, February 8, 2013 at 10:58, Tom Lane wrote:


If it breaks you get to keep both pieces.

Tom is an optimist. My (unscheduled) attempt at this resulted in 
a lot more than two pieces all of which appeared broken in their 
own right.


If you want to (re)start a conversation about making 
mount/unmount/move tablespace a reality be my guest, but, be 
warned, there seem to be some very fundamental barriers.


Regards
Gavan Schneider



--
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] Restoring a database dump from 9.0 to 9.2

[Tom Lane]

Jay McGaffigan  writes:
> So my first question.
>   If I can't use a psql 9.2.2 instance to create the backup.  Are there
> 'best' practices I should follow in creating the backup.  All my googling
> hasn't really been able to point to the best approach.

We do recommend using the newer pg_dump in an upgrade scenario when you
conveniently can, but 99% of the time the older pg_dump should work
fine.  That recommendation is mainly to forestall issues like
that-particular-older-version-has-a-bug, and at the moment there's no
evidence that that's your problem.

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

[GENERAL] Use case for deferrable check constraints, given inherited tables

[Chris Travers]

Hi everyone;

Given the recent discussions of deferrable not null constraints and my on
manual referential integrity work, I came up with a case where deferrable
check constraints may make a lot of sense, namely with custom referential
integrity handling and table inheritance.  This addresses a generalized and
well-understood object-oriented problem which can occur in the database,
namely object substitutability.  I would expect this sort of problem to
crop up more often with GIS work and the like, particularly with
partitioned tables, but here's a brief theoretical overview.

Suppose I have a table:

CREATE TABLE my_rectangle (
   id serial primary key,
   height numeric not null,
   width numeric not null
);

And I want to have another table which re-uses operations and functions
associated with rectangles:

CREATE TABLE my_square (CHECK (height = width) ) INHERITS (my_rectangle);

In order to enforce uniqueness and referential integrity, I would need
custom check constraints and triggers.  I could do it all via triggers, but
check constraints would be semantically simpler if they would work.

The case occurs when I want to alter a square such that it is no longer a
square, say doubling the height while leaving the width constant.  The
easiest solution would be to "move" the row from my_square to my_rectangle.
 Doing so though poses ordering issues and I either have to defer check
constraints, triggers, or both in order to ensure inheritance-tree-wide
uniqueness.

Keep in mind that check constraints can call functions which can look up
data in other tables.  For this reason there may be the same reasons to
defer them as one would see with triggers.

Best Wishes,
Chris Travers