Abelard Hoffman | 15 Sep 08:17 2014
Picon

Testing truthiness of GUC variables?

If I set a custom GUC variable to a boolean value, such as:

  SET myapp.audit = 'on';

is there a way to test it for truthiness in the same way the standard built-in variables are? IOW, the docs say a boolean can be written as:

Boolean values can be written as onofftruefalseyesno10 (all case-insensitive) or any unambiguous prefix of these.

cowwoc | 15 Sep 07:22 2014

Why isn't Java support part of Postgresql core?

Hi,

Out of curiosity, why is Postgresql's Java support so poor? I am
specifically looking for the ability to write triggers in Java.

I took a look at the PL/Java project and it looked both incomplete and dead,
yet other languages like Javascript are taking off. I would have expected to
see very strong support for Java because it's the most frequently used
language on the server-side.

What's going on? Why isn't this a core language supported alongside SQL,
Perl and Python as part of the core project?

Thanks,
Gili

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Why-isn-t-Java-support-part-of-Postgresql-core-tp5819025.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

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

Sergey Konoplev | 15 Sep 05:25 2014
Picon

PgToolkit 1.0.2 release testing

Hi,

The 1.0.2 release of the PgToolkit is on the way. Some significant
improvements of the tables and indexes bloat reducing tool have been
made.

Testers are very welcome. Use this link to get the testing version

https://github.com/grayhemp/pgtoolkit/branches/v1.0testing

Report bugs and suggestions either to me directly or on the issue page

https://github.com/grayhemp/pgtoolkit/issues.

List of changes:

- Fixed the non working statement timeout in the reindexing process
- Made it use `DROP INDEX CONCURRENTLY` if version is `>=9.2`
- Fixed the randomness of the SET statements order in database
  adapters
- Made it to process TOAST tables and indexes providing bloat
  information and rebuilding instructions
- Set an additional protection against the "incorrect result of
  cleaning" error

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray.ru <at> gmail.com

--

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

cowwoc | 14 Sep 23:01 2014

Feature request: temporary schemas

Hi,

I'd like to propose the ability to create temporary schemas.

Unlike temporary tables, this feature would enable developers to create a
temporary schema once and execute CREATE TABLE statements without the
TEMPORARY parameter.

This would facilitate running unit tests, where developers would like to run
the same creation script for unit tests and production code but do not wish
to parameterize each CREATE TABLE statement (both environments are expected
to execute identical scripts). It further enables the use of temporary
functions, something which is not possible today (apparently you can hack
this too, but there isn't an "official" way of doing so).

See http://dba.stackexchange.com/q/76494/4719 for a related discussion.

Should I move this discussion to a different mailing list or is this the
correct location?

Thanks,
Gili

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Feature-request-temporary-schemas-tp5819001.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

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

Torsten Förtsch | 14 Sep 14:03 2014
Picon
Picon

pg_stat_replication in 9.3

Hi,

I noticed a strange behaviour regarding pg_stat_replication in 9.3. If
called from psql using the \watch command, I see all my replicas. From
time to time one of them drops out and reconnects in a short period of
time, typically ~30 sec.

If I use the same select in plpgsql like:

  FOR r in SELECT application_name,
                  client_addr,
                  flush_location, clock_timestamp() AS lmd
             FROM pg_stat_replication
            ORDER BY application_name, client_addr
  LOOP
    RAISE NOTICE 'aname=%, ca=%, lmd=%, loc=%, cur=%, lag=%',
                 r.application_name, r.client_addr, r.lmd,
                 r.flush_location,
                 pg_current_xlog_location(),
                 pg_size_pretty(
                   pg_xlog_location_diff(
                     pg_current_xlog_location(),
                     r.flush_location
                   )
                 );
  END LOOP;

I see one of the replicas dropping out but never coming back again while
in a parallel session using psql and \watch it indeed does come back.

Is that intended?

Torsten

--

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

Picon

After upgrade from 9.2.9 to 9.3.5 got reduced contention

After a week or so since upgrade from 9.2.9 to 9.3.5 I'm very surprised 
(in a good way) by the gain of performance.

Most noticeable change is the reduced contention for records by 
exclusive/shared locks.

I can see increased numbers of requests fulfilled per minute (but I 
can't be exact on those numbers, just I can see graph going high - which 
is good).

Also, I've noticed that backup time dropped from 44 minutes to 30 
minutes (for about 60Gb in multiple databases) - which is really good 
change (35% decrease) on same hardware.

I still had no time to measure how much gain on improved indexing and 
other topics I know received improvements.

Thanks, PostgreSQL team!

Regards,

Edson

--

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

Picon

Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux?

Any experiences running PostgreSQL 9.3.5 on compressed Btrfs on Linux?

Thanks,

Edson

--

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

Willy-Bas Loos | 12 Sep 18:19 2014
Picon

2 left joins causes seqscan

Hi,

Today i ran into a situation where a second left join on an indexed field would prevent the index from being used, even though the index is clearly more efficient.
Removing either of the 2 joins would cause that the planner will use the index again.
I tested this on postgres 9.1 and 9.3 on my ubuntu (amd64) laptop.

--Here's the test data:

create table a (id serial primary key, field1 text);
create table b (id integer, title text, lang integer);
create index b_title_lowerto on b using btree (lower(title) text_pattern_ops);
vacuum analyze;

with x as (
insert into a
select generate_series(1,40000) as id
returning id
)
insert into b
select id, translate((random()*100*id)::text, '1234567890.', 'abcdefghij'), 1
from x;
update a set field1=translate(id::text, '1234567890.', 'abcdefghij');
insert into b
select b2.id, translate((random()*100*b2.id)::text, '1234567890.', 'abcdefghij'), 2
from b b2;

--Here's the query that doesn't use the index on "b":

select a.field1, b1.title , b2.title
from a
left join b b1 on b1.id = a.id and b1.lang=1
left join b b2 on b2.id = a.id and b2.lang=2
where (lower(b1.title) like'abcd%' or lower(b2.title) like 'abcd%')

--plan:
Hash Right Join  (cost=4298.60..7214.76 rows=8 width=35)
  Hash Cond: (b1.id = a.id)
  Filter: ((lower(b1.title) ~~ 'abcd%'::text) OR (lower(b2.title) ~~ 'abcd%'::text))
  ->  Seq Scan on b b1  (cost=0.00..1510.00 rows=40176 width=19)
        Filter: (lang = 1)
  ->  Hash  (cost=3798.60..3798.60 rows=40000 width=24)
        ->  Hash Right Join  (cost=1293.00..3798.60 rows=40000 width=24)
              Hash Cond: (b2.id = a.id)
              ->  Seq Scan on b b2  (cost=0.00..1510.00 rows=39824 width=19)
                    Filter: (lang = 2)
              ->  Hash  (cost=793.00..793.00 rows=40000 width=9)
                    ->  Seq Scan on a  (cost=0.00..793.00 rows=40000 width=9)



--Here's the query that does use the index on "b":

select a.field1, b1.title
from a
left join b b1 on b1.id = a.id and b1.lang=1
where lower(b1.title) like 'abcd%'
union
select a.field1, b2.title
from a
left join b b2 on b2.id = a.id and b2.lang=2
where lower(b2.title) like 'abcd%'

--plan:
HashAggregate  (cost=98.31..98.39 rows=8 width=20)
  ->  Append  (cost=4.74..98.27 rows=8 width=20)
        ->  Nested Loop  (cost=4.74..49.10 rows=4 width=20)
              ->  Bitmap Heap Scan on b b1  (cost=4.45..15.82 rows=4 width=19)
                    Filter: ((lang = 1) AND (lower(title) ~~ 'abcd%'::text))
                    ->  Bitmap Index Scan on b_title_lowerto  (cost=0.00..4.45 rows=3 width=0)
                          Index Cond: ((lower(title) ~>=~ 'abcd'::text) AND (lower(title) ~<~ 'abce'::text))
              ->  Index Scan using a_pkey on a  (cost=0.29..8.31 rows=1 width=9)
                    Index Cond: (id = b1.id)
        ->  Nested Loop  (cost=4.74..49.10 rows=4 width=20)
              ->  Bitmap Heap Scan on b b2  (cost=4.45..15.82 rows=4 width=19)
                    Filter: ((lang = 2) AND (lower(title) ~~ 'abcd%'::text))
                    ->  Bitmap Index Scan on b_title_lowerto  (cost=0.00..4.45 rows=3 width=0)
                          Index Cond: ((lower(title) ~>=~ 'abcd'::text) AND (lower(title) ~<~ 'abce'::text))
              ->  Index Scan using a_pkey on a a_1  (cost=0.29..8.31 rows=1 width=9)
                    Index Cond: (id = b2.id)


As you can see, the second query is far more efficient, even though it scans both tables twice to combine the results.
Is this some glitch in the query planner?

Cheers,
--
Willy-Bas Loos
Craig Ringer | 12 Sep 07:25 2014

PostgreSQL service account on Windows 7: Use a virtual account

Hi all

(This is really about the EDB installer, but we don't have anywhere
better to discuss it than -general, so):

The PostgreSQL installer now uses the NETWORKSERVICE account on Windows
by default (as of 9.2), instead of creating a "postgres" account with
username and password. Which is a big improvement to usability.

I recently found out that on Windows 7 / win2k8 R2 and newer there's now
a better alternative available: virtual accounts and managed service
accounts. They combine the benefit of avoiding all that password
management cruft with the ability to run services in less-privileged,
better isolated accounts.

See "New Account Types Available with Windows 7 and Windows Server 2008
R2" in
http://msdn.microsoft.com/en-au/library/ms143504.aspx

particularly "virtual accounts".

If that looks a lot like a UNIX "system account", you're not mistaken.

It looks like Microsoft have finally figured out that it'd be nice not
to need a password for a background system service and to have to then
store that password somewhere on the same system.

It may be worth adopting this when the installer detects a Windows 7 /
Win2k8 R2 or newer system - just create an account like:

    NT Service\PostgreSQL$EDB-9.4-x86

(or whatever name will get rid of conflicts) and use that instead of
NETWORK SERVICE.

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

--

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

Iain Mott | 11 Sep 12:49 2014

permission denied for schema topology

Hello, 

The server for my websites was recently changed and upgraded. I have ssh
access to the server and since the upgrade I am no longer able to use
"pg_dump" to perform scripted backups. I've written to the site's
support services, but until now, they've not been able to help (they've
responded saying they will likely need to refer the case on to a
developer - I don't  have any guarantees that it will be resolved).

I've googled for this - but didn't come up with any relevant solutions.
Can someone on this list please make suggestions that I can pass on to
the technical support?

Here's what happens (the important error messages are in English):

[~]# pg_dump mydatabase > dump.sql
Senha:
pg_dump: comando SQL falhou
pg_dump: Mensagem de erro do servidor: ERROR:  permission denied for schema topology
pg_dump: O comando foi: LOCK TABLE topology.topology IN ACCESS SHARE MODE

---------------

I am able to perform dumps of the databases via phpPdAdmin in the
"cpanel" of the server, but this is going very inconvenient - hoping to
use pg_dump

Thanks,

--

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

Daniel Begin | 10 Sep 23:00 2014
Picon

PostgreSQL Portable

First, I am a Newbie regarding PostgreSQL …

 

I just started to look at PostgreSQL to implement a large GIS DB (1Tb).  The data must reside in an external disk with eSATA connection and may be moved to different locations (and Windows desktops/laptops). I was looking to install PostgreSQL and PostGIS extensions on each PC (setting-up the proper PGDATA directory to the external disk) until I read about PostgreSQL and PgAdmin Portable …

 

http://sourceforge.net/projects/pgadminportable/

http://sourceforge.net/projects/postgresqlportable/

 

Is that a viable alternative considering the expected size of the DB? Any comments or proposal would be appreciated J

Daniel


Gmane