Re: [BUGS] BUG #5932: CLUSTER doesn't update n_dead_tup

[Bruce Momjian]

Andy Lester wrote:
> 
> On Sep 5, 2011, at 3:19 PM, Bruce Momjian wrote:
> 
> > 
> > This is an interesting bug report from March that got no replies.  In my
> > testing, not only does CLUSTER not update the n_dead_tup statistics, but
> > neither does VACUUM FULL, which internally uses the CLUSTER code
> > (cluster_rel()).  Is this a bug?
> 
> I can't imagine how it NOT be a bug to do something that gets rid of dead 
> tuples and then tell the user there are dead tuples when there are actually 
> no dead tuples.

Well, if you TRUNCATE I don't think it updates the statistics either.  I
think we assume the autovacuum system will analyse the new table soon.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [BUGS] BUG #5932: CLUSTER doesn't update n_dead_tup

[Andy Lester]

On Sep 6, 2011, at 9:04 AM, Bruce Momjian wrote:

> Well, if you TRUNCATE I don't think it updates the statistics either.  I
> think we assume the autovacuum system will analyse the new table soon.


When I brought this up in IRC, I recall that that sentiment was expressed, 
along with the standard "What do you mean you're not running autovacuum?"  

--
Andy Lester => a...@petdance.com => www.petdance.com => AIM:petdance

Re: [BUGS] BUG #5932: CLUSTER doesn't update n_dead_tup

[Bruce Momjian]

Andy Lester wrote:
> 
> On Sep 6, 2011, at 9:04 AM, Bruce Momjian wrote:
> 
> > Well, if you TRUNCATE I don't think it updates the statistics either.  I
> > think we assume the autovacuum system will analyse the new table soon.
> 
> 
> When I brought this up in IRC, I recall that that sentiment was
> expressed, along with the standard "What do you mean you're not running
> autovacuum?"

Well, if you are not running autovacuum you should be running vacuum and
analyze manually as needed.

--
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [BUGS] psql doesn't reuse -p after backend fail

[hubert depesz lubaczewski]

On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
> It's not just the port, it's all the connection parameters ---
> do_connect relies on the PGconn object to remember those, and in this
> case there no longer is a PGconn object.
> 
> We could have psql keep that information separately, but I'm not sure
> it's really worth the trouble.

well, I think it's definitely worth the trouble. If I had datbaase
standing at 5432, it would connect to it, and then I could mistakenly
ran commands to wrong database.
this is clearly not a good thing.

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/

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

Re: [BUGS] psql doesn't reuse -p after backend fail

[Tom Lane]

hubert depesz lubaczewski  writes:
> On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
>> It's not just the port, it's all the connection parameters ---
>> do_connect relies on the PGconn object to remember those, and in this
>> case there no longer is a PGconn object.
>> 
>> We could have psql keep that information separately, but I'm not sure
>> it's really worth the trouble.

> well, I think it's definitely worth the trouble.

[ shrug.. ]  So submit a patch.  Personally I don't think the case comes
up often enough to be worth the trouble, and I'd much rather spend
development time on preventing the server from crashing in the first
place.

regards, tom lane

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

Re: [BUGS] psql doesn't reuse -p after backend fail

[hubert depesz lubaczewski]

On Tue, Sep 06, 2011 at 11:35:43AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski  writes:
> > On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
> >> It's not just the port, it's all the connection parameters ---
> >> do_connect relies on the PGconn object to remember those, and in this
> >> case there no longer is a PGconn object.
> >> 
> >> We could have psql keep that information separately, but I'm not sure
> >> it's really worth the trouble.
> 
> > well, I think it's definitely worth the trouble.
> 
> [ shrug.. ]  So submit a patch.  Personally I don't think the case comes

way above my skillset :(

> up often enough to be worth the trouble, and I'd much rather spend
> development time on preventing the server from crashing in the first
> place.

the reason for the crash is discussed in the pg_upgrade thread on
hackers. ( 8.3 ltree + pg_upgrade to 9.0.5 == backend crash on select).

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/

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

Re: [BUGS] psql doesn't reuse -p after backend fail

[Bruce Momjian]

Tom Lane wrote:
> hubert depesz lubaczewski  writes:
> > On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
> >> It's not just the port, it's all the connection parameters ---
> >> do_connect relies on the PGconn object to remember those, and in this
> >> case there no longer is a PGconn object.
> >> 
> >> We could have psql keep that information separately, but I'm not sure
> >> it's really worth the trouble.
> 
> > well, I think it's definitely worth the trouble.
> 
> [ shrug.. ]  So submit a patch.  Personally I don't think the case comes
> up often enough to be worth the trouble, and I'd much rather spend
> development time on preventing the server from crashing in the first
> place.

Should we document this somewhere?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

[BUGS] BUG #6199: Can't install datatype hstore

[Hadmut]

The following bug has been logged online:

Bug reference:  6199
Logged by:  Hadmut
Email address:  had...@danisch.de
PostgreSQL version: 9.1 rc1
Operating system:   Ubuntu Oneiric
Description:Can't install datatype hstore
Details: 

Hi,

I'd like to run a database application which requires datatype hstore. 

But when trying something like

psql -f /usr/share/postgresql/9.1/extension/hstore--1.0.sql


I get error messages like 

psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:8: NOTICE:  return
type hstore is only a shell
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:8: ERROR:  could
not access file "MODULE_PATHNAME": Datei oder Verzeichnis nicht gefunden
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:13: NOTICE: 
argument type hstore is only a shell
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:13: ERROR:  could
not access file "MODULE_PATHNAME": Datei oder Verzeichnis nicht gefunden
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:18: NOTICE:  return
type hstore is only a shell
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:18: ERROR:  could
not access file "MODULE_PATHNAME": Datei oder Verzeichnis nicht gefunden
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:23: NOTICE: 
argument type hstore is only a shell
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:23: ERROR:  could
not access file "MODULE_PATHNAME": Datei oder Verzeichnis nicht gefunden
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:32: ERROR: 
function hstore_in(cstring) does not exist
psql:/usr/share/postgresql/9.1/extension/hstore--1.0.sql:37: NOTICE: 
argument type hstore is only a shell
 
...


regards

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

Re: [BUGS] BUG #6199: Can't install datatype hstore

[Tom Lane]

"Hadmut"  writes:
> I'd like to run a database application which requires datatype hstore. 

> But when trying something like

> psql -f /usr/share/postgresql/9.1/extension/hstore--1.0.sql

That is not how you install extensions anymore.  Use CREATE EXTENSION.

regards, tom lane

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

Re: [BUGS] BUG #5944: COPY FROM doesn't work with international characters

[Bruce Momjian]

Josh Berkus wrote:
> I have another example of this issue with WIN1252.  This line in a copy
> file:
> 
> 659446  828 1   /6???\bH@^W^Za$H???\b???@\\/No
> valid or unique HTTP objects found in XML response.
> 
> Into this table:
> 
>  Table "public.ep_tests"
> Column  | Type | Modifiers
> -+--+---
> id  | bigint   | not null
> v_id| integer  | not null
> status  | character(1) | not null
> vkey| text |
> details | text |
> 
> Results in this error while loading the data:
> 
> the following error is encounted by the \copy: ERROR:  missing data for
> column "details"
> CONTEXT:  COPY ep_tests, line 1028752: "659446  828 1   /6???\bH@?"
> 
> The dump file was produced by using 9.0.3's pg_dump in text mode to dump
> an 8.2 database, then using 9.0.3's psql to load the file.  Both servers
> are UTF8, locale WIN1252.
> 
> So it looks like we're not successfully escaping characters on WIN1252.
>  The characters in question are also latin characters.
> 
> We've reproduced this on a clean install.

Has this been fixed?

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [BUGS] Can't use WITH in a PERFORM query in PL/pgSQL?

[Bruce Momjian]

Added to TODO:

Improve PERFORM handling of WITH queries or document limitation

---

depst...@alliedtesting.com wrote:
> Update: It has been suggested to wrap perform around a select like this:
> 
> do
> $$begin
> perform(
> with A as (select 1 as foo)
> select foo from A
> );
> end$$;
> 
> This won't work if select returns more than one statement:
> 
> do
> $$begin
> perform(
> with A as (select generate_series(1,3) as foo)
> select foo from A
> );
> end$$;
> 
>ERROR:  more than one row returned by a subquery used as an expression
> 
> So I still say it's broken.
> 
> (Sorry for top-posting: I am forced to use Outlook at work...)
> 
> From: Dmitry Epstein
> Sent: Sunday, March 06, 2011 4:29 PM
> To: 'pgsql-bugs@postgresql.org'
> Cc: Peter Gagarinov; Vladimir Shahov
> Subject: Can't use WITH in a PERFORM query in PL/pgSQL?
> 
> PostgreSQL 9.0.1
> 
> It seems that PostgreSQL doesn't understand the WITH construct when used in a 
> PERFORM query inside PL/pgSQL functions and code blocks:
> 
> Example:
> 
> do
> $$begin
> with A as (select 1 as foo)
> perform foo from A;
> end$$;
> 
> syntax error at or near "perform"
> 
> do
> $$begin
> with A as (select 1 as foo)
> select foo from A;
> end$$;
> 
> query has no destination for result data
> 
> The only workaround that I can think of is to use a dummy variable to capture 
> the query result. This has to be done even when the query doesn't have a 
> result (as when calling a function returning void).
> 
> do
> $$declare
> dummy record;
> begin
> with A as (select 1 as foo)
> select foo into dummy from A;
> end$$;
> 
> 
> Dmitry Epstein | Developer
> 
> Allied Testing
> T + 7 495 544 48 69 Ext 417
> M + 7 926 215 73 36
> 
> www.alliedtesting.com
> We Deliver Quality.
> 

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [BUGS] BUG #5957: createdb with description and md5 auth forces to provide password twice

[Bruce Momjian]

Grzegorz Szpetkowski wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:  5957
> Logged by:  Grzegorz Szpetkowski
> Email address:  gszpetkow...@gmail.com
> PostgreSQL version: 9.0.3
> Operating system:   Ubuntu 10.10
> Description:createdb with description and md5 auth forces to provide
> password twice
> Details: 
> 
> How to reproduce the problem:
> 
> 1.Create new role with (encrypted password):
> createuser -SdRP user
> 2.In PostgreSQL 9.0.3 I found pg_hba.conf with local all all ident, so
> change to local all all md5
> 3.Restart/Reload used cluster
> 4.Execute createdb -U user mydb "My DB Description"
> 
> Output:
> 
> Password: 
> Password: 
> 
> creatdb command prompts password twice and I think it's improper behaviour
> (and documentation is silent about that).

FYI, this will be fixed in Postgres 9.1.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [BUGS] psql doesn't reuse -p after backend fail

[Denish Patel]

Yeah, Definitely it's not *expected* behavior so documentation is the good
starting point unless we fix the code soon. I don't agree with Tom's comment
on we should find out the reason for crash instead. In most of the cases,
reason for the crash is because someone restarted database and user trying
to get connection back.

my 2 cents!


On Tue, Sep 6, 2011 at 11:49 AM, Bruce Momjian  wrote:

> Tom Lane wrote:
> > hubert depesz lubaczewski  writes:
> > > On Mon, Sep 05, 2011 at 02:27:23PM -0400, Tom Lane wrote:
> > >> It's not just the port, it's all the connection parameters ---
> > >> do_connect relies on the PGconn object to remember those, and in this
> > >> case there no longer is a PGconn object.
> > >>
> > >> We could have psql keep that information separately, but I'm not sure
> > >> it's really worth the trouble.
> >
> > > well, I think it's definitely worth the trouble.
> >
> > [ shrug.. ]  So submit a patch.  Personally I don't think the case comes
> > up often enough to be worth the trouble, and I'd much rather spend
> > development time on preventing the server from crashing in the first
> > place.
>
> Should we document this somewhere?
>
> --
>  Bruce Momjian  http://momjian.us
>  EnterpriseDB http://enterprisedb.com
>
>  + It's impossible for everything to be true. +
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>



-- 
Denish Patel,
OmniTi Computer Consulting Inc.
Database Administrator,
Phone: 443.325.1357 x 232

Re: [BUGS] Can't use WITH in a PERFORM query in PL/pgSQL?

[Bruce Momjian]

depst...@alliedtesting.com wrote:
> Update: It has been suggested to wrap perform around a select like this:
> 
> do
> $$begin
> perform(
> with A as (select 1 as foo)
> select foo from A
> );
> end$$;
> 
> This won't work if select returns more than one statement:
> 
> do
> $$begin
> perform(
> with A as (select generate_series(1,3) as foo)
> select foo from A
> );
> end$$;
> 
>ERROR:  more than one row returned by a subquery used as an expression
> 
> So I still say it's broken.

Well, this problem isn't isolated to WITH queries:

test=> do
$$begin
perform(
select 1 UNION ALL select 1
);
end$$;
ERROR:  more than one row returned by a subquery used as an expression

test=> do
$$begin
perform(
select relname from pg_class
);
end$$;
ERROR:  more than one row returned by a subquery used as an expression

perform() can't seem to handle any SELECT that returns more than one
row, but perform replacing the SELECT can:

test=> do
$$begin
perform relname from pg_class;
end$$;
DO

That is certainly unsual, and I have documented this suggestion and
limitation in the attached patch that I have applied to 9.0, 9.1, and
head.

I think the idea that PERFORM will replace one or more SELECTs in a WITH
clause is just totally confusing and probably should not be supported. 
I guess the only bug is that perform() can't handle more than one
returned row, but at least we have documented that and can fix it later
if we want.

I have to say, those Allied Testing people are very good at finding
bugs.

---


> 
> From: Dmitry Epstein
> Sent: Sunday, March 06, 2011 4:29 PM
> To: 'pgsql-bugs@postgresql.org'
> Cc: Peter Gagarinov; Vladimir Shahov
> Subject: Can't use WITH in a PERFORM query in PL/pgSQL?
> 
> PostgreSQL 9.0.1
> 
> It seems that PostgreSQL doesn't understand the WITH construct when used in a 
> PERFORM query inside PL/pgSQL functions and code blocks:
> 
> Example:
> 
> do
> $$begin
> with A as (select 1 as foo)
> perform foo from A;
> end$$;
> 
> syntax error at or near "perform"
> 
> do
> $$begin
> with A as (select 1 as foo)
> select foo from A;
> end$$;
> 
> query has no destination for result data
> 
> The only workaround that I can think of is to use a dummy variable to capture 
> the query result. This has to be done even when the query doesn't have a 
> result (as when calling a function returning void).
> 
> do
> $$declare
> dummy record;
> begin
> with A as (select 1 as foo)
> select foo into dummy from A;
> end$$;
> 
> 
> Dmitry Epstein | Developer
> 
> Allied Testing
> T + 7 495 544 48 69 Ext 417
> M + 7 926 215 73 36
> 
> www.alliedtesting.com
> We Deliver Quality.
> 

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 08c3658..a2482de
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*** PERFORM query
*** 940,945 
--- 940,948 
   result.  Write the query the same
   way you would write an SQL SELECT command, but replace the
   initial keyword SELECT with PERFORM.
+  For WITH queries, use PERFORM and then
+  place the query in parentheses.  (In this case, the query can only
+  return one row.)
   PL/pgSQL variables will be
   substituted into the query just as for commands that return no result,
   and the plan is cached in the same way.  Also, the special variable

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

Re: [BUGS] BUG #6199: Can't install datatype hstore

[Hadmut Danisch]

On 06.09.2011 18:26, Tom Lane wrote:
> Use CREATE EXTENSION.

Yup, that worked. Thanks! :-)

regards
Hadmut


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

Re: [BUGS] Setting timezone: is it bug or intended?

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > Tom Lane wrote:
> >> It already is documented.  See
> >> http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html#DATATYPE-TIMEZONES
> >> specifically the point that POSIX zone names have the opposite sign
> >> convention from ISO-8601.
> >> 
> >> The great thing about standards is there are so many to choose from ;-)
> 
> > What isn't documented is why the sign changes for +0300 but not +03:
> 
> +03:00 is a legal POSIX zone name (hence the sign is different from SQL
> convention).  The other one is evidently being handled by this code path
> in check_timezone:
> 
>   /*
>* Try it as a numeric number of hours (possibly fractional).
>*/
>   hours = strtod(*newval, &endptr);
>   if (endptr != *newval && *endptr == '\0')
>   {
>   /* Here we change from SQL to Unix sign convention */
>   myextra.CTimeZone = -hours * SECS_PER_HOUR;
>   myextra.HasCTZSet = true;
>   }
> 
> which I think is legacy code meant to deal with SQL-standard
> specification of timezone offsets as INTERVAL values.  You get the same
> interpretation of sign when you use the SQL-spec syntax:
> 
> regression=# set time zone interval '+03:00';
> SET
> regression=# select now();
>   now  
> ---
>  2011-04-27 00:44:53.560295+03
> (1 row)
> 
> Like I said, too many standards with their fingers in this pie.

I assume we decided we can't improve this.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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