Re: [GENERAL] Understanding behavior of SELECT with multiple unnested columns

2013-03-26 Thread Ian Lawrence Barwick
2013/3/27 Ken Tanzer 
>
> I've been working on some queries involving multiple unnested columns.  At 
> first, I expected the number of rows returned would be the product of the 
> array lengths, so that this query would return 4 rows:
>
> SELECT unnest2(array['a','b']),unnest2(array['1','2']);
>
> when in fact it returns 2:
>
>  unnest2 | unnest2
> -+-
>  a   | 1
>  b   | 2
>
> Which is all well and good.  (Better, in fact, for my purposes.)  But then 
> this query returns 6 rows:
>
> SELECT unnest2(array['a','b','c']),unnest2(array['1','2']);
>  unnest2 | unnest2
> -+-
>  a   | 1
>  b   | 2
>  c   | 1
>  a   | 2
>  b   | 1
>  c   | 2
>
> Throw an unnested null column in and you get zero rows, which I also didn't 
> expect:
>
> SELECT 
> unnest2(array['a','b','c']),unnest2(array['1','2']),unnest(NULL::varchar[]);
>  unnest2 | unnest2 | unnest
> -+-+
> (0 rows)
>
>
> After some head scratching, I think I understand what to expect from these 
> unnests, but I'm unclear of the logic behind what is going on.  I'm hoping 
> someone can explain it a bit.

Basically you are getting Cartesian joins on the row output of
unnest() (and presumably
unnest2() - I guess this is a function you defined yourself?)

Effectively you are doing this:

CREATE TABLE t1 (val INT);
INSERT INTO t1 VALUES (1),(2);

CREATE TABLE t2 (val CHAR(1));
INSERT INTO t2 VALUES ('a'),('b'),('c');

CREATE TABLE t3 (val INT);

testdb=# SELECT * from t1, t2;
 val | val
-+-
   1 | a
   1 | b
   1 | c
   2 | a
   2 | b
   2 | c
(6 rows)


testdb=# DELETE FROM t2 where val='c';
DELETE 1
testdb=# SELECT * from t1, t2;
 val | val
-+-
   1 | a
   1 | b
   2 | a
   2 | b
(4 rows)

testdb=# SELECT * from t1, t2, t3;
 val | val | val
-+-+-
(0 rows)


HTH

Ian Barwick


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


[GENERAL] Understanding behavior of SELECT with multiple unnested columns

2013-03-26 Thread Ken Tanzer
I've been working on some queries involving multiple unnested columns.  At
first, I expected the number of rows returned would be the product of the
array lengths, so that this query would return 4 rows:

SELECT unnest2(array['a','b']),unnest2(array['1','2']);

when in fact it returns 2:

 unnest2 | unnest2
-+-
 a   | 1
 b   | 2

Which is all well and good.  (Better, in fact, for my purposes.)  But then
this query returns 6 rows:

SELECT unnest2(array['a','b','c']),unnest2(array['1','2']);
 unnest2 | unnest2
-+-
 a   | 1
 b   | 2
 c   | 1
 a   | 2
 b   | 1
 c   | 2

Throw an unnested null column in and you get zero rows, which I also didn't
expect:

SELECT
unnest2(array['a','b','c']),unnest2(array['1','2']),unnest(NULL::varchar[]);
 unnest2 | unnest2 | unnest
-+-+
(0 rows)


After some head scratching, I think I understand what to expect from these
unnests, but I'm unclear of the logic behind what is going on.  I'm hoping
someone can explain it a bit.  Also, on a practical level, would anyone
know how to query so that SELECT
unnest2(array['a','b','c']),unnest2(array['1','2']) would return three rows
instead of six, like so:

a 1
b 2
c (NULL)

As that would be perfect for my purposes.  Thanks in advance!

Ken




-- 
AGENCY Software
A data system that puts you in control
100% Free Software
*http://agency-software.org/*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing
list
 to
learn more about AGENCY or
follow the discussion.


[GENERAL] pltcl and modules question

2013-03-26 Thread hamann . w

Hi,

is there any way to use a module within a pltcl script, i.e. have
load /path/to/mystuff.so
or
package require mystuff
in a script.
The reason why I am asking: I have recently converted a fairly slow script
(lots of regex) into one compiled module that basically does all regex at once, 
and critcl
made it fairly easy to build a .so or a tcl package out of the code
 
Similarly, would it be possible to access loaded module from perl script, i.e. 
have
use mystuff;
line

Regards
Wolfgang Hamann





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


Re: [GENERAL] PostgreSQL service terminated by query

2013-03-26 Thread adrian . kitchingman
Thanks for the suggestion Richard.

I dumped the two tables in question and restored them which got the query 
working for a while until it eventually crashed the service again at 
another gid. I'll do a RAM check tonight.
I transferred the whole database to another computer and so far the query 
is working without issues.

Government men in black will be knocking on your door soon. ;-)

Cheers

Adrian




From:   Richard Huxton 
To: adrian.kitching...@dse.vic.gov.au, 
Cc: pgsql-general@postgresql.org
Date:   26/03/2013 10:32 PM
Subject:Re: [GENERAL] PostgreSQL service terminated by query
Sent by:pgsql-general-ow...@postgresql.org



On 26/03/13 05:55, adrian.kitching...@dse.vic.gov.au wrote:
> I'm hoping I can get some info on a query which terminates my PostgreSQL
> service.
> The query is a relatively simple PostGIS query:

> The log text when the service crashes is:

> 2013-03-26 15:49:55 EST LOG: server process (PID 3536) was terminated by
> exception 0xC005
> 2013-03-26 15:49:55 EST HINT: See C include file "ntstatus.h" for a
> description of the hexadecimal value.

> I'm running PostgreSQL 9.1 with PostGIS 2.0 installed on an WinXP SP3:
> 4GB RAM machine. Shared_buffers set at 50MB. Let me know if further info
> needed.

This is a Windows memory-related error. It might be due to a library 
problem, bad RAM or a corrupted pointer in the database table itself.

1. Can you reliably produce the error with this specific gid?
2. Can you dump the database (or perhaps just the tables in question)?

If we can't find any problems in the database itself and you can spare 
the downtime, it may be worth running a RAM checker overnight.

> Notice:
> This email and any attachments may contain information that is personal,
> confidential,
> legally privileged and/or copyright. No part of it should be reproduced,
> adapted or communicated without the prior written consent of the
> copyright owner.

Oh no, too late!

-- 
   Richard Huxton
   Archonet Ltd


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




Notice:
This email and any attachments may contain information that is personal, 
confidential,
legally privileged and/or copyright. No part of it should be reproduced, 
adapted or communicated without the prior written consent of the copyright 
owner. 

It is the responsibility of the recipient to check for and remove viruses.

If you have received this email in error, please notify the sender by return 
email, delete it from your system and destroy any copies. You are not 
authorised to use, communicate or rely on the information contained in this 
email.

Please consider the environment before printing this email.


Re: [GENERAL] Age of the WAL?

2013-03-26 Thread Peter Geoghegan
On 26 March 2013 22:21, Tom Lane  wrote:
> The version recently added to contrib is only meant to work with the
> current server release, AFAIK.  However, it's derived from older
> standalone programs that are out there somewhere --- did you look around
> on pgfoundry?

Actually, I think the version on pgfoundry is unmainted.

I'd look here instead:

https://github.com/snaga/xlogdump/commits/master


-- 
Regards,
Peter Geoghegan


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


Re: [GENERAL] Age of the WAL?

2013-03-26 Thread Tom Lane
Erik Jones  writes:
> On Mar 12, 2013, at 4:13 PM, Tom Lane wrote:
>> Transaction commit and abort records carry timestamps, so you could
>> figure this out with something like pg_xlogdump.  I don't know of any
>> canned solution though.

> Anyway, will pg_xlogdump work with any previous versions of Postgres or will 
> it be only 9.3+?

The version recently added to contrib is only meant to work with the
current server release, AFAIK.  However, it's derived from older
standalone programs that are out there somewhere --- did you look around
on pgfoundry?

regards, tom lane


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


Re: [GENERAL] Age of the WAL?

2013-03-26 Thread Erik Jones

On Mar 12, 2013, at 4:13 PM, Tom Lane wrote:

> Erik Jones  writes:
>> What's the best way to determine the age of the current WAL?  Not the 
>> current segment, but the whole thing.  Put another way:  is there a way to 
>> determine a timestamp for the oldest available transaction in the WAL?
> 
> Transaction commit and abort records carry timestamps, so you could
> figure this out with something like pg_xlogdump.  I don't know of any
> canned solution though.


Tom,

Thanks, and sorry for any discontinuity in the rather long time it's taken for 
me to get on this reply (had a vacation).

Anyway, will pg_xlogdump work with any previous versions of Postgres or will it 
be only 9.3+?

For reference, the reason need to be able to do this is this:  Given a set of 
snapshots (each taken with running pg_start_backup before and pg_stop_backup 
after) and a running server, I need to be able to determine whether or not any 
given snapshot will be usable for setting up a new standby.  I know I could 
grab the info returned from pg_start_backup and store that as snapshot 
meta-data but I'm hoping to keep from having to make changes the existing 
snapshotting code, if possible.

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


[GENERAL] Building an invalidation queue in Postgres

2013-03-26 Thread Laurence Rowe
I'd like to cache parts of my database locally on each client. To keep
those caches in sync I'd like to implement an invalidation queue.

A naïve approach would be to simply create a table of (txn_id,
invalidated_object_ids), then have the clients query this table for
txn_ids > last_queried_txn_id. But I suspect this could result in
invalidations being missed for long running transactions due to MVCC
visibility issues.

How might I go about implementing such a queue safely in Postgres?

Laurence


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


Re: [GENERAL] Unexpected behaviour of encode()

2013-03-26 Thread Tom Lane
Bill Moran  writes:
> If I just do:
> SELECT 'can''t'::text;
> I get "can't" which is what I'd expect.  I would then expect
> encode to escape the ' somehow.  Even c-style escaping, like
> "can\'t" would have been less surprising to me.

> If there's something I'm missing, I'm still missing it.

The manual says that 'escape' encoding "merely outputs null bytes as
\000 and doubles backslashes".  (The reason to double backslashes is to
make \000 unambiguous, of course.)  The point of this is to sanitize
bytea data sufficiently to allow it to be transported as text.  If you
want to transport it as a SQL literal, that's a tighter constraint that
would require some other escaping method, or at least passing the result
through something like quote_literal.

(Having said that, I wonder though if "escape" doesn't need more
thought.  The output is only valid text in SQL_ASCII or single-byte
encodings, otherwise there's risk of encoding violations.)

regards, tom lane


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


Re: [GENERAL] Unexpected behaviour of encode()

2013-03-26 Thread Merlin Moncure
On Tue, Mar 26, 2013 at 1:36 PM, Bill Moran  wrote:
> I get "can't" which is what I'd expect.  I would then expect
> encode to escape the ' somehow.

nope -- encode/escape doesn't escape single quotes. it's not designed
to produce output that can be fed directly back into the database (at
least, not without escaping).  yes, it (escape format) is pretty
terrible.

merlin


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


Re: [GENERAL] Unexpected behaviour of encode()

2013-03-26 Thread Bill Moran
In response to Merlin Moncure :

> On Tue, Mar 26, 2013 at 12:47 PM, Bill Moran  wrote:
> >
> > psql -U postgres
> > psql (9.2.3)
> > Type "help" for help.
> >
> > postgres=# select encode('can''t', 'escape');
> >  encode
> > 
> >  can't
> > (1 row)
> >
> > I observed the same behaviour on one of our older systems (8.3.11) as well.
> >
> > Am I missing something?  I expected "can''t" as the output.
> 
> why? that isn't what you passed it.  the input string doubled single
> quotes is converted to single single quote per spec.  it's 'ghetto
> escaping'.

Not sure what you mean by "ghetto secaping" ... but doubling up a '
is the SQL standard escaping method, AFAIK.

If I just do:
SELECT 'can''t'::text;
I get "can't" which is what I'd expect.  I would then expect
encode to escape the ' somehow.  Even c-style escaping, like
"can\'t" would have been less surprising to me.

If there's something I'm missing, I'm still missing it.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


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


Re: [GENERAL] pg_stat_get_last_vacuum_time(): why non-FULL?

2013-03-26 Thread Kevin Grittner
CR Lender  wrote:

> According to the manual (9.1), pg_stat_get_last_vacuum_time() returns
>
> timestamptz | Time of the last non-FULL vacuum initiated by the
> | user on this table
>
> Why are full vacuums excluded from this statistic? It looks like there's
> no way to get the date of the last manual vacuum, if only full vacuums
> are performed.

Because FULL is a bit of a misnomer -- there are important things a
non-FULL vacuum does which a FULL vacuum does not.  In general, a
VACUUM FULL should be followed by a non-FULL vacuum to keep the
database in good shape.  Also, a VACUUM FULL is an extreme form of
maintenance which should rarely be needed; if you find that you
need to run VACUUM FULL, something is probably being done wrong
which should be fixed so that you don't need to continue to do such
extreme maintenance.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [GENERAL] Unexpected behaviour of encode()

2013-03-26 Thread Merlin Moncure
On Tue, Mar 26, 2013 at 12:47 PM, Bill Moran  wrote:
>
> psql -U postgres
> psql (9.2.3)
> Type "help" for help.
>
> postgres=# select encode('can''t', 'escape');
>  encode
> 
>  can't
> (1 row)
>
> I observed the same behaviour on one of our older systems (8.3.11) as well.
>
> Am I missing something?  I expected "can''t" as the output.

why? that isn't what you passed it.  the input string doubled single
quotes is converted to single single quote per spec.  it's 'ghetto
escaping'.

merlin


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


[GENERAL] Unexpected behaviour of encode()

2013-03-26 Thread Bill Moran

psql -U postgres
psql (9.2.3)
Type "help" for help.

postgres=# select encode('can''t', 'escape');
 encode 

 can't
(1 row)

I observed the same behaviour on one of our older systems (8.3.11) as well.

Am I missing something?  I expected "can''t" as the output.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


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


Re: [GENERAL] Why does Postgres allow duplicate (FK) constraints

2013-03-26 Thread Alban Hertroys
On 26 March 2013 17:07, Thomas Kellerer  wrote:

> Is there anything in the standard that actively requires that you can
>>> create two "identical" constraints?
>>
>>
> Because technically it simply doesn't make sense, does it?
>

It can make sense during a maintenance window, if you create a new
(redundant) FK constraint concurrently to replace the existing one. If
you'd first remove the existing constraint, you're allowing FK violations
until the new constraint has finished creating its index.

This happens for example if you want to use a different index algorithm,
say a gist index instead of a btree index, or if the initial index has
gotten corrupt somehow and it needs reindexing.
-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


Re: [GENERAL] Why does Postgres allow duplicate (FK) constraints

2013-03-26 Thread Tom Lane
Thomas Kellerer  writes:
> Tom Lane, 26.03.2013 17:03:
>> If Oracle does that, they're violating the SQL standard --- there is
>> nothing in the standard that supports rejecting an ALTER TABLE ADD
>> CONSTRAINT on the grounds that it's redundant.  The spec only says
>> you can't give two constraints the same name.

> Is there anything in the standard that actively requires that you can create 
> two "identical" constraints?

The lack of any prohibition to the contrary means there is no way to
argue that the code you showed previously violates the spec; thus,
a database that fails to accept it is rejecting spec-compliant DDL.

> Because technically it simply doesn't make sense, does it?

Well, it's redundant, but that doesn't make it wrong.  In any case,
there are lots of ways that things might be redundant.  Should we
reject a unique constraint on (a,b) if there's already one on (b,a)?
Or if there are separate unique constraints on each of a and b?

regards, tom lane


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


Re: [GENERAL] Why does Postgres allow duplicate (FK) constraints

2013-03-26 Thread Thomas Kellerer

Tom Lane, 26.03.2013 17:03:

While I agree that this SQL should not have been written like this
in the first place, I wonder why Postgres doesn't actively prevent
this (like e.g. Oracle).


If Oracle does that, they're violating the SQL standard --- there is
nothing in the standard that supports rejecting an ALTER TABLE ADD
CONSTRAINT on the grounds that it's redundant.  The spec only says
you can't give two constraints the same name.


Is there anything in the standard that actively requires that you can create two 
"identical" constraints?

Because technically it simply doesn't make sense, does it?

Regards
Thomas



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


Re: [GENERAL] Why does Postgres allow duplicate (FK) constraints

2013-03-26 Thread Tom Lane
Thomas Kellerer  writes:
> While I agree that this SQL should not have been written like this in the 
> first place, I wonder why Postgres doesn't actively prevent this (like e.g. 
> Oracle).

If Oracle does that, they're violating the SQL standard --- there is
nothing in the standard that supports rejecting an ALTER TABLE ADD
CONSTRAINT on the grounds that it's redundant.  The spec only says
you can't give two constraints the same name.

regards, tom lane


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


[GENERAL] Why does Postgres allow duplicate (FK) constraints

2013-03-26 Thread Thomas Kellerer

Hi,

I was a bit surprised that the following DDL will work:

create table parent (id integer not null primary key);
create table child (id integer not null primary key, pid integer not null);

alter table child
  add constraint fk_child_parent
  foreign key (pid) references parent(id);
  
alter table child

  add foreign key (pid) references parent(id);
  


It essentially creates the same foreign key constraint twice.

While I agree that this SQL should not have been written like this in the first 
place, I wonder why Postgres doesn't actively prevent this (like e.g. Oracle).

Is there a technical reason, or is it simply a matter of "no one cared enough to 
change this"?

Regards
Thomas



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


Re: [GENERAL] Do "after update" trigger block the current transaction?

2013-03-26 Thread Clemens Eisserer
Hi Richard,

> Might be worth looking at PgQ - a queueing system underlying Londiste. That
> would handle tracking the changes in PostgreSQL leaving you to just handle
> the MySQL end. Timestamps will do the job as long as you are careful to
> allow enough slack to deal with clock updates.

Thanks a lot, PgQ seems to be exactly what I was looking for :-)
Another solution that came to my mind is notifying the "replication
daemon" using NOTIFY/LISTEN.

Thanks again, Clemens


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


Re: [GENERAL] Do "after update" trigger block the current transaction?

2013-03-26 Thread Richard Huxton

On 26/03/13 13:24, Clemens Eisserer wrote:

Hi Richard,


  Will triggers (after
update specifically) cause the execution of SQL-commands to pause
until the trigger-function has returned (at statement execution time
or commit)?


The trigger will block. If it didn't then it couldn't abort the transaction
if it needed to.


Thanks for the clarification.


Why not use one of the established trigger-based replication solutions?


Because the "other" database which I would like to keep in sync is a
MySQL db. Furthermore I do not need a 1:1 replica, but instead just
update a few columns in different tables there.

My inital plan was to add a timestamp-column which is updated at every
Update and to poll for changes every 5-10s. However, the word
"polling" seems to cause an allergic reaction for some poeple ;)


Might be worth looking at PgQ - a queueing system underlying Londiste. 
That would handle tracking the changes in PostgreSQL leaving you to just 
handle the MySQL end. Timestamps will do the job as long as you are 
careful to allow enough slack to deal with clock updates.



--
  Richard Huxton
  Archonet Ltd


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


Re: [GENERAL] effective_io_concurrency on Windows

2013-03-26 Thread Merlin Moncure
On Tue, Mar 26, 2013 at 3:35 AM, Bartosz Dmytrak  wrote:
> Hi all
> is it possible to introduce similar solution for Windows systems in future?
> I am aware it is not available because of lack of posix_fadvise function,
> but I believe there is a way to introduce this feature for Win systems.

Probably the best case scenario would be to make some library routines
that wrap the windows API and emulate posix_fadvise.  This may or may
not be possible, but win32 API does support asynchronous i/o so it
might be.

merlin


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


Re: [GENERAL] Do "after update" trigger block the current transaction?

2013-03-26 Thread Clemens Eisserer
Hi Richard,

>>  Will triggers (after
>> update specifically) cause the execution of SQL-commands to pause
>> until the trigger-function has returned (at statement execution time
>> or commit)?
>
> The trigger will block. If it didn't then it couldn't abort the transaction
> if it needed to.

Thanks for the clarification.

> Why not use one of the established trigger-based replication solutions?

Because the "other" database which I would like to keep in sync is a
MySQL db. Furthermore I do not need a 1:1 replica, but instead just
update a few columns in different tables there.

My inital plan was to add a timestamp-column which is updated at every
Update and to poll for changes every 5-10s. However, the word
"polling" seems to cause an allergic reaction for some poeple ;)

Thanks, Clemens


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


Re: [GENERAL] Bad plan on a huge table query

2013-03-26 Thread Daniel Cristian Cruz
Well, I did it:

explain (analyze, buffers)
select count(*) from turma.aula_confirmacao where
inicio_aula::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP) AND
CURRENT_TIMESTAMP; -- changed name because of a conflict in some queries

http://explain.depesz.com/s/Fzr

And just to update, this is the actual query and plan:

EXPLAIN ANALYZE
SELECT
 aluno_mensal.id_matricula,
 aluno_mensal.id_turma,
 aluno_mensal.turma,
 aluno_mensal.id_produto_educacao,
 aluno_mensal.produto_educacao,
 aluno_mensal.unidade,
 aluno_mensal.unidade_execucao,
 aluno_mensal.modalidade,
 aluno_mensal.id_pessoa,
 aluno_mensal.nome_pessoa,
 presenca.id_diario,
 aula_confirmacao.inicio_aula::date AS data_aula,
 presenca.justificativa_falta,
 SUM(aula_confirmacao.termino_aula - aula_confirmacao.inicio_aula) AS
carga_faltas,
 mensal.ano AS ano_apuracao,
 mensal.mes AS mes_apuracao
FROM indicadores.aluno_mensal
JOIN indicadores.mensal
 ON mensal.id_mensal = aluno_mensal.id_mensal
JOIN turma.presenca
 ON presenca.id_matricula = aluno_mensal.id_matricula
JOIN turma.aula_confirmacao
 ON aula_confirmacao.id_evento = presenca.id_evento
JOIN turma.estudante_periodo
 ON
  estudante_periodo.id_matricula = presenca.id_matricula AND
  estudante_periodo.id_diario = presenca.id_diario AND
  aula_confirmacao.inicio_aula::date BETWEEN estudante_periodo.inicio AND
estudante_periodo.termino
WHERE
 presenca.inicio_aula::DATE BETWEEN DATE_TRUNC('YEAR', CURRENT_TIMESTAMP)
AND CURRENT_TIMESTAMP AND
 NOT presenca.presente AND
 mensal.ano = EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AND
 aula_confirmacao.inicio_aula::DATE BETWEEN DATE_TRUNC('YEAR',
CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP AND
 aula_confirmacao.confirmada AND
 aluno_mensal.id_medicao = 7
GROUP BY
 aluno_mensal.id_matricula,
 aluno_mensal.id_turma,
 aluno_mensal.turma,
 aluno_mensal.id_produto_educacao,
 aluno_mensal.produto_educacao,
 aluno_mensal.unidade,
 aluno_mensal.unidade_execucao,
 aluno_mensal.modalidade,
 aluno_mensal.id_pessoa,
 aluno_mensal.nome_pessoa,
 presenca.id_diario,
 aula_confirmacao.inicio_aula::date,
 presenca.justificativa_falta,
 mensal.ano,
 mensal.mes;

http://explain.depesz.com/s/YfXr

I guess that, there is something with estudante_periodo, because there is
24% with only one row and 50% with 5 or less rows on it:

with distr as (select id_matricula, count(*) from turma.estudante_periodo
group by id_matricula)
select count as rows_on_estudante_periodo, count(*), (100 * count(*) /
sum(count(*)) over ())::numeric(5,2) as percent from distr group by count
order by 1;
 rows_on_estudante_periodo | count | percent
---+---+-
 1 | 24941 |   23.92
 2 |  5720 |5.49
 3 |  5220 |5.01
 4 |  8787 |8.43
 5 |  7908 |7.58
 6 |  7357 |7.06
 7 |  4896 |4.70
 8 |  3076 |2.95
 9 |  2963 |2.84
10 |  2679 |2.57
11 |  6613 |6.34
12 |  8708 |8.35
13 |  4448 |4.27
14 |  1411 |1.35
15 |  2137 |2.05
16 |  1219 |1.17
17 |  2269 |2.18
18 |   627 |0.60
19 |   332 |0.32
20 |   325 |0.31
21 |   213 |0.20
22 |   127 |0.12
23 |   113 |0.11
24 |   144 |0.14
25 |   862 |0.83
26 |   784 |0.75
27 |   131 |0.13
28 |79 |0.08
29 |35 |0.03
30 |   136 |0.13
31 | 1 |0.00
33 | 1 |0.00
36 | 1 |0.00
38 | 1 |0.00
39 | 1 |0.00
40 | 1 |0.00
(36 rows)

After the refactoring, idx_aula_confirmacao_2 became idx_aula_confirmacao_1:

select * from pg_stats where tablename = 'idx_aula_confirmacao_1';
-[ RECORD 1
]--+--
schemaname | turma
tablename  | idx_aula_confirmacao_1
attname| inicio_aula
inherited  | f
null_frac  | 0.996792
avg_width  | 4
n_distinct | 24
most_common_vals   |
most_common_freqs  |
histogram_bounds

Re: [GENERAL] PostgreSQL service terminated by query

2013-03-26 Thread Richard Huxton

On 26/03/13 05:55, adrian.kitching...@dse.vic.gov.au wrote:

I'm hoping I can get some info on a query which terminates my PostgreSQL
service.
The query is a relatively simple PostGIS query:



The log text when the service crashes is:



2013-03-26 15:49:55 EST LOG: server process (PID 3536) was terminated by
exception 0xC005
2013-03-26 15:49:55 EST HINT: See C include file "ntstatus.h" for a
description of the hexadecimal value.



I'm running PostgreSQL 9.1 with PostGIS 2.0 installed on an WinXP SP3:
4GB RAM machine. Shared_buffers set at 50MB. Let me know if further info
needed.


This is a Windows memory-related error. It might be due to a library 
problem, bad RAM or a corrupted pointer in the database table itself.


1. Can you reliably produce the error with this specific gid?
2. Can you dump the database (or perhaps just the tables in question)?

If we can't find any problems in the database itself and you can spare 
the downtime, it may be worth running a RAM checker overnight.



Notice:
This email and any attachments may contain information that is personal,
confidential,
legally privileged and/or copyright. No part of it should be reproduced,
adapted or communicated without the prior written consent of the
copyright owner.


Oh no, too late!

--
  Richard Huxton
  Archonet Ltd


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


Re: [GENERAL] Do "after update" trigger block the current transaction?

2013-03-26 Thread Richard Huxton

On 26/03/13 08:52, Clemens Eisserer wrote:

Hi,

We are currently evaluating the feasibility of executing long-running
scripts written in shell-script (plsh) called by triggers (after
update/insert) to synchronize two databases. Will triggers (after
update specifically) cause the execution of SQL-commands to pause
until the trigger-function has returned (at statement execution time
or commit)?


The trigger will block. If it didn't then it couldn't abort the 
transaction if it needed to.



The other possible approach would be to use polling on some
trigger-updated timestamp-column, which is not pretty but should be
fairly simple.


Why not use one of the established trigger-based replication solutions?

--
  Richard Huxton
  Archonet Ltd


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


Re: [GENERAL] Acess Oracle with dbi-link (PostgreSQL) Error Compile

2013-03-26 Thread Albe Laurenz
Emanuel Araújo wrote:
> I'm having trouble making a base to access Oracle via dbi-link, because when 
> installing DBD::Oracle
> version 1.58 the same mistakes some missing files. Ago as "oci.h", it is 
> being called within the
> oracle.h
> 
> The purpose would be to sync data between two tools for integration.
> 
> Has anyone experienced this?
> Have any solution or suggestion?
> There is another tool that I could be using to make this access?
> 
> The following error returned by Perl when you squeegee "make"
> 
> make
> gcc-c-D_REENTRANT-D_GNU_SOURCE 
> -I/root/perl5/lib/perl5/x86_64-linux-thread-multi/auto/DBI-fno-strict-
> aliasing-pipe-fstack-protector -I/usr/local/include - 
> D_LARGEFILE_SOURCE-D_FILE_OFFSET_BITS = 64-g-O2-
> pipe-Wall-Wp,-D_FORTIFY_SOURCE = 2-fexceptions-fstack-protector - param = 
> ssp-buffer-size = 4-m64-
> mtune = generic-DVERSION = \ "1:58 \ "-DXS_VERSION = \" 1:58 \ "-fPIC" 
> -I/usr/lib64/perl5/CORE "-Wall-
> Won-comment-DUTF8_SUPPORT-DORA_OCI_VERSION = \" 10.2.0.3 \ "-DORA_OCI_102 
> Oracle.c
> In file included from Oracle.xs: 1:0:
> Oracle.h: 37:17: fatal error: oci.h: File or directory not found
> compilation terminated.
> make: ** [Oracle.o] Error 1

You need Oracle's header files.
In Instant Client they are called "SDK", and in the regular Oracle
client installation they are called "Programmer" or some similar
cryptic name.  If in doubt, install everything.

If you have PostgreSQL 9.1 or later, you could consider using
oracle_fdw (http://oracle-fdw.projects.pgfoundry.org/).

Yours,
Laurenz Albe


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


[GENERAL] Do "after update" trigger block the current transaction?

2013-03-26 Thread Clemens Eisserer
Hi,

We are currently evaluating the feasibility of executing long-running
scripts written in shell-script (plsh) called by triggers (after
update/insert) to synchronize two databases. Will triggers (after
update specifically) cause the execution of SQL-commands to pause
until the trigger-function has returned (at statement execution time
or commit)?

The other possible approach would be to use polling on some
trigger-updated timestamp-column, which is not pretty but should be
fairly simple.

Thank you in advance, Clemens


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


[GENERAL] effective_io_concurrency on Windows

2013-03-26 Thread Bartosz Dmytrak
Hi all
is it possible to introduce similar solution for Windows systems in future?
I am aware it is not available because of lack of posix_fadvise function,
but I believe there is a way to introduce this feature for Win systems.


Regards,
Bartek


Re: [GENERAL] help me to clear postgres problem

2013-03-26 Thread dinesh kumar
IIRC, service failure creates some event logs information in windows. And
also you can verify the bit rock installer log files from %TEMP% location.

Regards,
Dinesh
manojadinesh.blogspot.com

On Mon, Mar 25, 2013 at 5:05 PM, jayaram s <123jaya...@gmail.com> wrote:

> Hello
> I have installed PostgreSQL 8.4.1 in my PC. For the requirement of data
> migration I again want to install "PostgreSQL enterprise DB  9.2".
> I couldn't install it because
> I have select option "postgresql compatible" on "configuration mode". So
> prompt wants me to enter "password". I have enter my existing postgres
> password "postgres'. But I couldn't install. An error message displayed as
> * "service user account 'postgres' couldnot be created". Please help me
> to clear the problem*
>
> --
> *With Regards
>
> Jayaram
>
> *
>
>