Re: [GENERAL] Understanding behavior of SELECT with multiple unnested columns
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
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
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
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?
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?
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?
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
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()
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()
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()
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?
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()
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()
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
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
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
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
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
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?
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?
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
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?
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
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
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?
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
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?
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
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
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 > > * > >