warren_brodie | 30 May 23:15 2013
Picon

BUG #8195: Postgre SQL Database displayed as log in even after running a hide command on terminal

The following bug has been logged on the website:

Bug reference:      8195
Logged by:          Warren Brodie
Email address:      warren_brodie <at> hotmail.com
PostgreSQL version: 9.0.0
Operating system:   OS X 10.8.3
Description:        

Hi there,

I am using a piece of software called poker tracker that uses a Postgre SQL
database. Up until now I have support dealings with them but they are unable
to resolve the issue and have put me onto yourselves I will include all
correspondence with them so you can see the steps I have taken:

Hi Warren -

Something like that would be run from a "terminal" application so do this:

1. In PokerTracker4 click "File -> Backup", put check marks in everything on
the left and remove all those you can from the middle. This is to backup
your database(s) first just in case something goes wrong later.

Save the backup file someplace safe.

2. Open "Applications -> Utilities -> Terminal"

3. Copy/paste this into the new terminal window:

(Continue reading)

vlajos | 30 May 18:51 2013
Picon

BUG #8193: A few cosmetic misspell fixes.

The following bug has been logged on the website:

Bug reference:      8193
Logged by:          Lajos Veres
Email address:      vlajos <at> gmail.com
PostgreSQL version: 9.2.4
Operating system:   Linux
Description:        

Found a few small misspell with this:
https://github.com/vlajos/misspell_fixer

I created a small patch here:
http://www.pastebin.ca/2385261

Lajos

--

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

federico | 30 May 18:43 2013

BUG #8192: On very large tables the concurrent update with vacuum lag the hot_standby replica

The following bug has been logged on the website:

Bug reference:      8192
Logged by:          Federico Campoli
Email address:      federico <at> brandwatch.com
PostgreSQL version: 9.2.4
Operating system:   Debian 6.0
Description:        

/*

Description:

It seems on very large tables the concurrent update with vacuum (or
autovacuum), 
when the slave is in hot standby mode, generates long loops in read on a
single wal segment during the recovery process.

This have two nasty effects.
A massive read IO peak and the replay lag increasing as the recovery process
hangs for long periods on a pointless loop.

PostgreSQL version: PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled
by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

Steps to reproduce the error:
setup an hot standby server.
the error occurs with streaming replication enabled and disabled

*/
(Continue reading)

vishnu.singh | 30 May 13:14 2013

BUG #8190: Issue with slony-I replication on postgres master and slave database

The following bug has been logged on the website:

Bug reference:      8190
Logged by:          sunarc
Email address:      vishnu.singh <at> sunarctechnologies.com
PostgreSQL version: 9.2.4
Operating system:   windows 8 pro
Description:        

I have two system with OS windows 8 and windows XP. I installed the postgres
plus advance server for database replication on both system. I followed this
link for whole process. But there is one issue with me to run the script
written in .sk file extension. I have searched on google and stackoverflow
but I didnot get any proper solution.

Can any one help me out to resolve this issue. If there is any query,
frankly ask.

note:- this question may be duplicate on stackoverflow or any other. I asked
this question on stackoverflow too. so reply as fast as possible.

--

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

hubert depesz lubaczewski | 30 May 13:21 2013

pg_statistics bloat after drop table

Tested on todays HEAD of 9.3.

Steps to reproduce:

$ create table t1 (i int4);
CREATE TABLE

$ insert into t1 (i) values (1);
INSERT 0 1

$ analyze t1;
ANALYZE

$ select count(*) from pg_statistic where starelid = 't1'::regclass;
 count
-------
     1
(1 row)

$ create table t2 () inherits (t1);
CREATE TABLE

$ analyze t1;
ANALYZE

$ select count(*) from pg_statistic where starelid = 't1'::regclass;
 count
-------
     2
(1 row)
(Continue reading)

Amit Kapila | 28 May 16:56 2013

Re: bug in Prepared statement with DELETE RETURNING and rule on view

On Tuesday, May 28, 2013 6:50 PM Brice André wrote:
> Hello Amit,
> 
> Thanks for your answer.
> 
> The reason why it does not work is still not really clear for me .What
> I find very strange is that, if you perform exactly same request, with
> exactly same C++ code, but that you change the database schema so that
> the ON DELETE rule of the view really deletes elements, it works
> properly. The inconsistency between both cases looks very strange to
> me.

What happens when you change ON DELETE rule of the view that really deletes
elements is that command type after applying rule remains same which means
Delete, so it can set the Tag.
Refer Function QueryRewrite().
Setting tag means after sql statement execution, it tells you the number of
elements affected. For example
1. when your rule is such that it internally updates, it will mention after
sql execution as 
DELETE 0
2. when your rule is such that it internally deletes, it will mention after
sql execution as
DELETE 81

Now based on whether you can set the tag or not, ChoosePortalStrategy() will
decide portal strategy (PORTAL_ONE_RETURNING or PORTAL_RUN_MULTI).
When the rule is to do update, in that case it choose PORTAL_RUN_MULTI which
doesn't send tuples.

(Continue reading)

Himanshu Rana | 28 May 14:27 2013

query regardinf pgadmin3


 













> hi,
>
> how i can make plugins icon active in pgadmin3 in ubuntu??
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>  Thanks & Regards,
>
> Himanshu Rana
> Project Officer,
> GIS Department,
> Foundation of Ecological Security,
> Anand, Gujarat- 388001
>



--
Dave Page
PostgreSQL Core Team
http://www.postgresql.org/


bkhamphousone | 28 May 15:36 2013

BUG #8183: field timestamp result to date

The following bug has been logged on the website:

Bug reference:      8183
Logged by:          khamphousone
Email address:      bkhamphousone <at> sopragroup.com
PostgreSQL version: 9.2.3
Operating system:   Linux debian
Description:        

In a SQL script, I've got a create table command with timestamp field
inside.
Instead of creating timestamp without field zone, I get a date.

--

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

sachinthana.anuradha | 28 May 07:10 2013
Picon

BUG #8182: Database name duplicate in one postgres instance

The following bug has been logged on the website:

Bug reference:      8182
Logged by:          Sachinthana Rajapaksha
Email address:      sachinthana.anuradha <at> yahoo.com
PostgreSQL version: Unsupported/Unknown
Operating system:   redhat
Description:        

Hi,

We are having a problem with postgres database version 8.0.14. This is a
production data base and suddenly it has created a database under the same
name in one postgres instance.

            List of databases
        Name        |  Owner   | Encoding 
--------------------+----------+----------
 monitor_cacti      | Monitor  | UNICODE
 template0          | postgres | UNICODE
 template1          | postgres | UNICODE
 *_live 	    | xxx      | UNICODE
 *_live 	    | xxx      | UNICODE
(5 rows)

once we checked the datfrozenxid and datvacuumxid it show like this. 

    oid    |      datname       | datdba | encoding | datistemplate |
datallowconn | datlastsysoid | datvacuumxid | datfrozenxid | dattablespace |
datconfig |         datacl         
-----------+--------------------+--------+----------+---------------+--------------+---------------+--------------+--------------+---------------+-----------+------------------------
  18580764 | *_live 	    |    100 	 |        6 | f             | t          
 |         17228 |          482 |          482 |          1663 |           |

  95000657 | monitor_cacti      |    101 |        6 | f             | t     
      |         17228 |          482 |          482 |          1663 |       
   | 
         1 | template1          |      1 |        6 | t             | t     
      |         17228 |          482 |     482 |          1663 |           |
{postgres=CT/postgres}
     17229 | template0          |      1 |        6 | t             | f     
      |         17228 |          482 |          482 |          1663 |       
   | {postgres=CT/postgres}
 116382901 |*_live 	    |        100 |        6 | f             | t         
  |         17228 |   3261314675 |   3261314675 |          1663 |          
| 
(5 rows)

one of the duplicated database show very large value and other shoe 482.

We cannot take the backups since it duplicate the databases.

I need to know about these.

1. If I get the data folder copied and paste that to a other postgres
instance  can I restore the database?

2. why this kind of duplicate happens and is there workaround to solve this
issue?

3. why datfrozenxid and datvacuumxid shows large valuse?

Since this is a production database appriciate early feedback.

Thanks
Sachinthana

--

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

Amit Kapila | 28 May 14:59 2013

Re: bug in Prepared statement with DELETE RETURNING and rule on view

On Tuesday, May 28, 2013 1:54 PM Brice André wrote:
> On Tuesday, May 28, 2013 1:28 PM Brice André wrote:
> 
> I shall look into it today in later half of the day.
> > Dear Amit,
> >
> > Thanks for your answer.
> >
> > I performed the same test as you and I get the same result (on my
> > linux server, debian, postgresql 8.4).
> >
> > Maybe the problem is related to libpq ?
> >
> > Did you tried the C code provided to see if you can reproduce the
> > problem ?

I checked your C code and found the reason why you are not able to get the
tuples returned by "Delete .. Returning .."

Currently it is not supported to return tuples for non-select statements
using PQexecPrepared and the reason is, there is
no provision for Describe to send a RowDescription during this execution.
You can refer function PortalRunMulti() in code, if you want to know
more details.

I could see below way for you to change your application if you want rows
returned by "Delete .. Returning .."
Use PQexec for below sql statements:

prepare t1plan (int,int) AS Delete from v1 where c1 between $1  and $2
returning c1,deleted;
Execute t1plan(10,90);

After preparing once, you can call Execute SQL statement multiple times, it
can save your time of prepare each time of delete statement, which was
your motto for using PQexecPrepared().

>
> >
> > 2013/5/28 Amit Kapila <amit.kapila <at> huawei.com>:
> > > On Tuesday, May 28, 2013 12:39 AM Brice André wrote:
> > >> Dear all,
> > >>
> > >> I found what I really think is a bug in the postgresql 8.4.
> > >>
> > >> I have an sql database structure in which a real table has a
> column
> > >> that is used to mark the entries as deleted without really
> deleting
> > >> them. Then, I have a view that is hiding this to the users, with
> > proper
> > >> rules that perform real actions on the table. So, a ON DELETE rule
> > on
> > >> this view is performing an UPDATE which marks the rows as delete
> > >> without deleting them. The view is hiding the rows tagged as
> > deleted.
> > >>
> > >> This code is working from several years and I have a web-service
> > that
> > >> performs several actions on top of this database. Those actions
> > include
> > >> a "DELETE ... RETURNING ..." command on the view. This web-service
> > was
> > >> implemented by a php script that did not use any prepared
> statement,
> > >> and everything was working properly.
> > >>
> > >> I had performance issue with this solution and I decided to
> rewrite
> > the
> > >> service in C++, and to use prepared statements. The SQL commands
> are
> > >> exactly the same, but they are now executed from a C++ application
> > >> using libpq, and they use prepared statements.
> > >
> > > I had tried in latest 9.3 code with psql using prepared statements
> > and it
> > > worked fine, please see result below.
> > > I shall check your libpq application code as well, but in the mean
> > time can
> > > you please verify whether the below works for you on 8.4 (I don't
> > have 8.4
> > > setup).
> > >
> > >
> > > postgres=> prepare t1plan (int,int) AS Delete from v1 where c1
> > between $1
> > > and $2
> > >  returning c1,deleted;
> > > PREPARE
> > > postgres=> Execute t1plan(10,90);
> > >  c1 | deleted
> > > ----+---------
> > >  10 | t
> > >  11 | t
> > >  12 | t
> > >  13 | t
> > >  14 | t
> > >  15 | t
> > >  16 | t
> > >  17 | t
> > >  18 | t
> > >  19 | t
> > >  20 | t
> > >  21 | t
> > >  22 | t
> > >  23 | t
> > >  24 | t
> > >  25 | t
> > >  26 | t
> > >  27 | t
> > >  28 | t
> > >  29 | t
> > >  30 | t
> > >  31 | t
> > >  32 | t
> > >  33 | t
> > >  34 | t
> > >  35 | t
> > >  36 | t
> > >  37 | t
> > >  38 | t
> > >  39 | t
> > >  40 | t
> > >  41 | t
> > >  42 | t
> > >  43 | t
> > >  44 | t
> > >  45 | t
> > >  46 | t
> > >  47 | t
> > >  48 | t
> > >  49 | t
> > >  50 | t
> > >  51 | t
> > >  52 | t
> > >  53 | t
> > >  54 | t
> > >  55 | t
> > >  56 | t
> > >  57 | t
> > >  58 | t
> > >  59 | t
> > >  60 | t
> > >  61 | t
> > >  62 | t
> > >  63 | t
> > >  64 | t
> > >  65 | t
> > >  66 | t
> > >  67 | t
> > >  68 | t
> > >  69 | t
> > >  70 | t
> > >  71 | t
> > >  72 | t
> > >  73 | t
> > >  74 | t
> > >  75 | t
> > >  76 | t
> > >  77 | t
> > >  78 | t
> > >  79 | t
> > >  80 | t
> > >  81 | t
> > >  82 | t
> > >  83 | t
> > >  84 | t
> > >  85 | t
> > >  86 | t
> > >  87 | t
> > >  88 | t
> > >  89 | t
> > >  90 | t
> > > (81 rows)
> > >
> > >
> > > DELETE 0
> > >
> > > With Regards,
> > > Amit Kapila.
> > >
> 
> 
> 
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs <at> postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--

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

Amit Kapila | 28 May 10:23 2013

Re: bug in Prepared statement with DELETE RETURNING and rule on view

On Tuesday, May 28, 2013 1:28 PM Brice André wrote:

I shall look into it today in later half of the day.
> Dear Amit,
> 
> Thanks for your answer.
> 
> I performed the same test as you and I get the same result (on my
> linux server, debian, postgresql 8.4).
> 
> Maybe the problem is related to libpq ?
> 
> Did you tried the C code provided to see if you can reproduce the
> problem ?
> 
> Regards,
> Brice
> 
> 
> 2013/5/28 Amit Kapila <amit.kapila <at> huawei.com>:
> > On Tuesday, May 28, 2013 12:39 AM Brice André wrote:
> >> Dear all,
> >>
> >> I found what I really think is a bug in the postgresql 8.4.
> >>
> >> I have an sql database structure in which a real table has a column
> >> that is used to mark the entries as deleted without really deleting
> >> them. Then, I have a view that is hiding this to the users, with
> proper
> >> rules that perform real actions on the table. So, a ON DELETE rule
> on
> >> this view is performing an UPDATE which marks the rows as delete
> >> without deleting them. The view is hiding the rows tagged as
> deleted.
> >>
> >> This code is working from several years and I have a web-service
> that
> >> performs several actions on top of this database. Those actions
> include
> >> a "DELETE ... RETURNING ..." command on the view. This web-service
> was
> >> implemented by a php script that did not use any prepared statement,
> >> and everything was working properly.
> >>
> >> I had performance issue with this solution and I decided to rewrite
> the
> >> service in C++, and to use prepared statements. The SQL commands are
> >> exactly the same, but they are now executed from a C++ application
> >> using libpq, and they use prepared statements.
> >
> > I had tried in latest 9.3 code with psql using prepared statements
> and it
> > worked fine, please see result below.
> > I shall check your libpq application code as well, but in the mean
> time can
> > you please verify whether the below works for you on 8.4 (I don't
> have 8.4
> > setup).
> >
> >
> > postgres=> prepare t1plan (int,int) AS Delete from v1 where c1
> between $1
> > and $2
> >  returning c1,deleted;
> > PREPARE
> > postgres=> Execute t1plan(10,90);
> >  c1 | deleted
> > ----+---------
> >  10 | t
> >  11 | t
> >  12 | t
> >  13 | t
> >  14 | t
> >  15 | t
> >  16 | t
> >  17 | t
> >  18 | t
> >  19 | t
> >  20 | t
> >  21 | t
> >  22 | t
> >  23 | t
> >  24 | t
> >  25 | t
> >  26 | t
> >  27 | t
> >  28 | t
> >  29 | t
> >  30 | t
> >  31 | t
> >  32 | t
> >  33 | t
> >  34 | t
> >  35 | t
> >  36 | t
> >  37 | t
> >  38 | t
> >  39 | t
> >  40 | t
> >  41 | t
> >  42 | t
> >  43 | t
> >  44 | t
> >  45 | t
> >  46 | t
> >  47 | t
> >  48 | t
> >  49 | t
> >  50 | t
> >  51 | t
> >  52 | t
> >  53 | t
> >  54 | t
> >  55 | t
> >  56 | t
> >  57 | t
> >  58 | t
> >  59 | t
> >  60 | t
> >  61 | t
> >  62 | t
> >  63 | t
> >  64 | t
> >  65 | t
> >  66 | t
> >  67 | t
> >  68 | t
> >  69 | t
> >  70 | t
> >  71 | t
> >  72 | t
> >  73 | t
> >  74 | t
> >  75 | t
> >  76 | t
> >  77 | t
> >  78 | t
> >  79 | t
> >  80 | t
> >  81 | t
> >  82 | t
> >  83 | t
> >  84 | t
> >  85 | t
> >  86 | t
> >  87 | t
> >  88 | t
> >  89 | t
> >  90 | t
> > (81 rows)
> >
> >
> > DELETE 0
> >
> > With Regards,
> > Amit Kapila.
> >

--

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


Gmane