Valentine Gogichashvili | 28 Aug 01:28 2013
Picon

BUG #7494: WAL replay speed depends heavily on the shared_buffers size

Hello Andreas, 

it is more then a year now, that I have not returned to that topic. 

As we still have a problem with very slow WAL replay in situations, when we create and drop a lot of temporary tables, I have made a small synthetic test. 
I have build the simplest steaming replication using PostgreSQL 9.3rc1.

My goal was to see, if the recovery process will be able to keep up with this WAL flow. 

Running this sproc on the master: 

CREATE OR REPLACE FUNCTION public.f()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
begin

  CREATE TEMP TABLE temp_table_to_test_replication AS 
    SELECT s.i as id from generate_series(1, 100) as s(i);
  DROP TABLE temp_table_to_test_replication;
  RETURN 1;
end;
$function$

leads to writing of WAL files. Is it an expected behavior? Is it expected that WAL files are filled  when the only thing, that sproc is supposed to do is to create and drop a temporary table. Are these catalog changes?

(I was calling it with: 

seq 1000 | xargs -l -n 1 -P 5 -I x psql -tA -c "select f();" > /dev/null

for that call pg_stat_replication.sent_location moved from 0/21891CB0 to 0/21DFEC74 or 5689284 bytes
)


WAL files are also written when executing a stored procedure, that was writing into an UNLOGGED table:

CREATE OR REPLACE FUNCTION public.s()
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
begin
  INSERT INTO unlogged_test_table SELECT pg_backend_pid() FROM generate_series(1,100) as s(i);
  DELETE FROM unlogged_test_table WHERE id = pg_backend_pid();
  RETURN pg_backend_pid();
end;
$function$

(WAL position moved from 0/21E51894 to 0/21E5B58C for 40184 bytes)

Also as I recreated the same table as a LOGGED one, the WAL diff generated becomes 11668768 that is what I would expect. 

WAL files are not being generated when calling really read-only sprocs like now() :)

Regards,

Valentine Gogichashvili
ajayaksadvance | 27 Aug 10:31 2013
Picon

BUG #8400: DB size changed after restore

The following bug has been logged on the website:

Bug reference:      8400
Logged by:          Ajay
Email address:      ajayaksadvance <at> gmail.com
PostgreSQL version: 9.2.0
Operating system:   ubuntu
Description:        

Hi, 

I am not sure its a bug or not but need some information on this.

I am doing upgrade from 8.4 to 9.2 hence DB dump and then restore.

On postgrse 8.4 , i have DB of size 32 GB.
SELECT pg_size_pretty(pg_database_size('<DBNAME>')) As fulldbsize;

I have done vacummdb before going for dump.

I have first done schema dump and then DB data and all is fine.

But on restore its running successfully but i am getting only size 9213 MB.

Dont know, what exactly happend?

I have checked rows count, random data its seems fine but why there is
difference?

How to make sure restore is done properly?

But ifi do below on 8.4 and 9.2 then i am getting different size and
obviously small on 9.2 .
SELECT pg_size_pretty(pg_total_relation_size('package')) As fulldbsize;

Please suggest.

--

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

bughunt | 27 Aug 11:30 2013

BUG #8402: Bad error message by psql

The following bug has been logged on the website:

Bug reference:      8402
Logged by:          David Tonhofer
Email address:      bughunt <at> gluino.name
PostgreSQL version: 9.2.4
Operating system:   Linux
Description:        

Got a table named

"Communes_du_Luxembourg_en_Polygones"

=> \d+ Communes_du_Luxembourg_en_Polygones

Did not find any relation named "Communes_du_Luxembourg_en_Polygones"

That message is wrong! 

The name of the table is not quoted, so psql is looking for

"communes_du_Luxembourg_en_polygones"

So it should say:

Did not find any relation named "communes_du_luxembourg_en_polygones"
(quoting may be needed to avoid casefolding)

And indeed:

\d+ "Communes_du_Luxembourg_en_Polygones"

works like a charm.

--

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

vincent | 27 Aug 15:53 2013

BUG #8403: installing PostgreSQL breaks future registration of w32tm service

The following bug has been logged on the website:

Bug reference:      8403
Logged by:          Vincent Dautremont
Email address:      vincent <at> searidgetech.com
PostgreSQL version: 9.1.3
Operating system:   Windows 7 Ultimate x64 - Windows Server 2008 x64
Description:        

I discovered this after having installed PostgreSQL 9.1 on a few PCs, then
messed around with my windows time service registry . I decided to type :
w32tm /unregister
then
w32tm /register to reset the service registry to default.

then when I try to start the service I get the err 1290 :
----------------------------
Windows could not start the Windows Time Service on Local Computer.

Error 1290: The service start failed since one or more services in the same
process have an incompatible service SID type setting.  A service with
restricted service SID type can only coexist in the same process with other
services with a restricted SID type. If the service SID type for this
service was just configured, the hosting process must be restarted in order
to start this service.
----------------------------

I discovered that doing the following I was able to get my Windows Time
service starting again :
---------
1) uninstall PostgreSQL from Windows "add/remove programs"
2) reboot
3) run the command: w32tm /unregister
4) reboot
5) run the command: w32tm /register
6) reboot
7) try to start windows time : net start w32time
8) upon success of 7) reinstall PostgreSQL
---------
My idea is that PostgreSQL or its Windows Installer messes around somewhere
with Windows services config.

--

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

borz_off | 27 Aug 11:04 2013
Picon

BUG #8401: weird input accepted for intervals and geometric types

The following bug has been logged on the website:

Bug reference:      8401
Logged by:          Alexey Borzov
Email address:      borz_off <at> cs.msu.su
PostgreSQL version: 9.3rc1
Operating system:   irrelevant
Description:        

For interval, multiple units:

postgres=# select '1 year day second'::interval;
 interval 
----------
 1 year
(1 row)

Geometric, missing comma:

postgres=# select '(1,2)(3,4)'::box, '1,2 3,4'::box;
     box     |     box     
-------------+-------------
 (3,4),(1,2) | (3,4),(1,2)
(1 row)

Geometric, one trailing comma:

postgres=# select '(1,2)(3,4),'::box;
     box     
-------------
 (3,4),(1,2)
(1 row)

Geometric, more trailing commas:

postgres=# select '(1,2)(3,4)(5,6),,'::path;
        path         
---------------------
 ((1,2),(3,4),(5,6))
(1 row)

Geometric, unbalanced delimiters:

postgres=# select '(1,2),3)'::circle;
  circle   
-----------
 <(1,2),3>
(1 row)

Geometric, mismatched delimiters:

postgres=# select '((1,2),3>'::circle;
  circle   
-----------
 <(1,2),3>
(1 row)

Confirmed on 9.0.13 and 9.3rc1, dates waaaaaay back.

--

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

borz_off | 27 Aug 10:18 2013
Picon

BUG #8399: inconsistent input of multidimensional arrays

The following bug has been logged on the website:

Bug reference:      8399
Logged by:          Alexey Borzov
Email address:      borz_off <at> cs.msu.su
PostgreSQL version: 9.3rc1
Operating system:   irrelevant
Description:        

PostgreSQL documentation states:

"Multidimensional arrays must have matching extents for each dimension. A
mismatch causes an error..."

This is not completely true:
postgres=# select cast('{{1,2}, {3}}' as integer[]);
ERROR:  multidimensional arrays must have array expressions with matching
dimensions
LINE 1: select cast('{{1,2}, {3}}' as integer[]);
                    ^
postgres=# select cast('{{1}, {2,3}}' as integer[]);
       int4       
------------------
 {{1,NULL},{2,3}}
(1 row)

Trying to use an array constructor yields an expected error in both cases.

Confirmed on 9.3rc1 and 9.0.13

--

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

martin.kevin | 26 Aug 20:00 2013
Picon

BUG #8398: to_json(''::hstore) gives invalid JSON

The following bug has been logged on the website:

Bug reference:      8398
Logged by:          Kevin Martin
Email address:      martin.kevin <at> gmail.com
PostgreSQL version: 9.3rc1
Operating system:   Ubuntu 10.04
Description:        

to_json() returns an invalid JSON value when called on an empty hstore
value. The first two statements below behave as expected, the third (with an
empty hstore) fails:

config_test=# SELECT to_json(q) FROM (SELECT '' AS v) AS q;
 to_json  
----------
 {"v":""}
(1 row)

config_test=# SELECT to_json(q) FROM (SELECT ARRAY[]::INT[] AS v) AS q;
 to_json  
----------
 {"v":[]}
(1 row)

config_test=# SELECT to_json(q) FROM (SELECT ''::HSTORE AS v) AS q;
 to_json 
---------
 {"v":}
(1 row)

The returned JSON will fail to be parsed by a conformant JSON parser.

--

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

Nick Carenza | 25 Aug 21:01 2013
Picon

uuid.h: present but cannot be compiled

checking ossp/uuid.h usability... no
checking ossp/uuid.h presence... no
checking for ossp/uuid.h... no
checking uuid.h usability... no
checking uuid.h presence... yes
configure: WARNING: uuid.h: present but cannot be compiled
configure: WARNING: uuid.h:     check for missing prerequisite headers?
configure: WARNING: uuid.h: see the Autoconf documentation
configure: WARNING: uuid.h:     section "Present But Cannot Be Compiled"
configure: WARNING: uuid.h: proceeding with the preprocessor's result
configure: WARNING: uuid.h: in the future, the compiler will take precedence
configure: WARNING:     ## ---------------------------------------- ##
configure: WARNING:     ## Report this to pgsql-bugs <at> postgresql.org ##
configure: WARNING:     ## ---------------------------------------- ##
checking for uuid.h... yes

on os x 10.8.4

installing with homebrew


==> ./configure --disable-debug --prefix=/usr/local/Cellar/postgresql/9.2.4 --datadir=/usr/local/Cellar/postgresql/9.2.4/share/postgresql --docdir=/usr/local/Cellar/postgresql/9.2.4/share/doc/postgresql --enable-thread-safety --with-bonjour --with-gssapi --with-krb5 --with-ldap --with-openssl --with-pam --with-libxml --with-libxslt --with-ossp-uuid --with-python --with-perl --with-tcl ARCHFLAGS='-arch x86_64'
paul | 24 Aug 09:15 2013

BUG #8396: Window function results differ when selecting from table and view, with where clause

The following bug has been logged on the website:

Bug reference:      8396
Logged by:          Paul M.
Email address:      paul <at> weotta.com
PostgreSQL version: 9.3rc1
Operating system:   Ubuntu Linux
Description:        

When I select from a view, the where clause in my select statement does not
restrict the rows processed by window functions referenced inside the view
definition. Thus, if window functions are involved, using a where clause
when selecting from a view and using a where clause when selecting directly
from the underlying table produces different results.

Without wanting to speculate on the cause of the differing results, I will
say that this seems to be a case of an issue noted a year ago on Stack
Overflow: "Will Postgres push down a WHERE clause into a VIEW with a Window
Function (Aggregate)?"

http://stackoverflow.com/questions/7533877/

At that time, responder Evan Carroll noted, "I can't think of anyway an
un-referenced Window function can change the result if the WHERE was pushed
down." This seems to be just such a case.

Thanks in advance for looking into this. I've written a test case, which I
hope will be helpful...

create table plant (
  plant_id character varying( 6 )
, city character varying( 25 )
, constraint p_pk_pid primary key ( plant_id )
);

insert into plant
  ( plant_id, city )
values
  ( '14 ST', 'San Francisco' )
, ( 'FOLSOM', 'San Francisco' )
, ( 'CHAVEZ', 'San Francisco' )
, ( 'HEINZ', 'Berkeley' )
;

create view plant_extend as
select
  plant_id as plant_id
, row_number() over ( partition by city order by plant_id ) as plant_number
, count(*) over ( partition by city ) as of_plants_in_city
, city as city
from plant;

-- Despite the where clause, the window functions see all 3 San Francisco
plants:

select
  *
from plant_extend
where
  plant_id = 'FOLSOM'
;

-- But when the query is expressed this way, the window functions see only
the Folsom Street plant: 

select
  plant_id as plant_id
, row_number() over ( partition by city order by plant_id ) as plant_number
, count(*) over ( partition by city ) as of_plants_in_city
, city as city
from plant
where
  plant_id = 'FOLSOM'
;

--

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

harukat | 24 Aug 13:46 2013
Picon

BUG #8397: pg_basebackup -x from new standby server sometimes causes Segmentation fault

The following bug has been logged on the website:

Bug reference:      8397
Logged by:          TAKATSUKA Haruka
Email address:      harukat <at> sraoss.co.jp
PostgreSQL version: 9.2.4
Operating system:   Linux (CentOS6)
Description:        

Hi.

I report a small bug.
pg_basebackup -x from new standby server sometimes causes Segmentation
fault.

(1) create new standby server dir by pg_basebackup without -x
(2) start new standby server
(3) pg_basebackup from new standby server with -x
(!) when new standby has no WAL files in pg_xlog,
    new standby's wal sender crash

new standby server's core file:

Core was generated by `postgres: wal sender process postgres ::1(55210)
sending backup "pg_basebackup'.
Program terminated with signal 11, Segmentation fault.
#0  0x0000003b7368ac66 in __rawmemchr_sse2 () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install
glibc-2.12-1.107.el6.x86_64 libxml2-2.7.6-4.el6.x86_64
zlib-1.2.3-27.el6.x86_64
(gdb) bt
#0  0x0000003b7368ac66 in __rawmemchr_sse2 () from /lib64/libc.so.6
#1  0x0000003b73675990 in _IO_str_init_static_internal () from
/lib64/libc.so.6
#2  0x0000003b73669935 in vsscanf () from /lib64/libc.so.6
#3  0x0000003b736639a8 in sscanf () from /lib64/libc.so.6
#4  0x0000000000622351 in perform_base_backup (opt=0x7fffc2e22300,
    tblspcdir=0xd424c0) at basebackup.c:304
#5  0x0000000000622c50 in SendBaseBackup (cmd=<value optimized out>)
    at basebackup.c:558
#6  0x000000000061f5b0 in HandleReplicationCommand () at walsender.c:482
#7  WalSndHandshake () at walsender.c:257
#8  WalSenderMain () at walsender.c:181
#9  0x0000000000650b12 in PostgresMain (argc=1, argv=<value optimized out>,
    dbname=0xc82a90 "", username=0xc82a70 "postgres") at postgres.c:3715
#10 0x000000000060c4f1 in BackendRun () at postmaster.c:3614
#11 BackendStartup () at postmaster.c:3304
#12 ServerLoop () at postmaster.c:1367
#13 0x000000000060f031 in PostmasterMain (argc=<value optimized out>,
    argv=<value optimized out>) at postmaster.c:1127
#14 0x00000000005ae140 in main (argc=5, argv=0xc80bb0) at main.c:199

./backend/replication/basebackup.c:304
   XLogFromFileName(walFiles[0], &tli, &logid, &logseg);

In this case, nWalFiles = 0 and walFiles[] palloced zero size.

Though pg_basebackup does not have to work in this rare case,
we should insert something like "if (nWalFiles <= 0) ereport(...);".

regards,

--

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

bashtanov | 23 Aug 18:17 2013

BUG #8395: empty aclitem arrays are considered 1-dimensional

The following bug has been logged on the website:

Bug reference:      8395
Logged by:          Alexey Bashtanov
Email address:      bashtanov <at> imap.cc
PostgreSQL version: 9.1.9
Operating system:   Ubuntu linux 12.04
Description:        

Empty aclitem arrays are considered 1-dimensional, but in general empty
arrays are 0-dimensional. It leads to the following problems:

STEPS TO REPRODUCE
1) install fresh postgres, connect to it
2) select relacl, relacl = '{}'::aclitem[], (select aclexplode(relacl)),
array_length(relacl, 1) from pg_class where oid::regclass =
'pg_largeobject'::regclass;
3) select aclexplode('{}'::aclitem[]);

EXPECTED
2) {}, false, null, null
3) no error, zero-lines table

GOT
2)  relacl | ?column? | ?column? | array_length 
--------+----------+----------+--------------
 {}     | f        |          |            0
3) ERROR:  ACL arrays must be one-dimensional

also it can be reproduced on some 9.2 version

--

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