Re: [GENERAL] DEFERRABLE NOT NULL constraint

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

Re: [GENERAL] Visual query builder for PosgreSQL?

> 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

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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, i

Re: [GENERAL] DEFERRABLE NOT NULL constraint

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 sy

[GENERAL] Decrease the time required function

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 S

[GENERAL] Bug, Feature, or what else?

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 ak

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

Re: [GENERAL] Decrease the time required function

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 >

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

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

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

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" fo

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

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

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

> > 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;

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

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 db

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

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

Re: [GENERAL] Visual query builder for PosgreSQL?

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 p

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

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.

Re: [GENERAL] Visual query builder for PosgreSQL?

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 ope

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

> > 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 u

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

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

Re: [GENERAL] pl/java for postgresql 9.2

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 -- Fr

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

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 GRA

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

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=#

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

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 > +--+---

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

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

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

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: Privileg

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

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 > > +

Re: [GENERAL] pg_shdepend vacuum.

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 ca

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

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, a

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

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 REIN

Re: [GENERAL] Decrease the time required function

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

[GENERAL] no implicit cast error in 9.2?

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.

[GENERAL] Swapping volumes under tablespaces: supported?

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 cloni

[GENERAL] Restoring a database dump from 9.0 to 9.2

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 be

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

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 remo

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

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) >

Re: [GENERAL] Restoring a database dump from 9.0 to 9.2

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 < dmpfi

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

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

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

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 i

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

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 w

Re: [GENERAL] Swapping volumes under tablespaces: supported?

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

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

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 t

Re: [GENERAL] Restoring a database dump from 9.0 to 9.2

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

Re: [GENERAL] Restoring a database dump from 9.0 to 9.2

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

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

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. Vers

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

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 th

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

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

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

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 functio

Re: [GENERAL] Swapping volumes under tablespaces: supported?

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

Re: [GENERAL] Restoring a database dump from 9.0 to 9.2

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

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

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 gen