2015-05-11 19:26 GMT+03:00 Anil Menon :
> manualscan=> select count(*) From public.msgtxt where msgid in (select
> msgid From ver736.courier where org_id=3);
> count
> ---
> 10225
> (1 row)
>
> Please note, there is no msgid col in courier table. Which brings the
> question why does this SQL
7 августа 2016 г., 11:39 пользователь Михаил написал:
> Есть типовая задача. Для сущностей БД хранить наборы файлов. Не столь
> мало - всего от миллионов на данный момент плюс большой рост на
> перспективу, и небольшие, не более 1 Гб, чаще размером десятки-сотни
> Кб.
>
> Ранее такую задачу реали
2016-08-07 22:23 GMT+03:00 Tim Smith :
> create table test (
> when date,
> foo numeric,
> bar numeric,
> alice numeric,
> bob numeric);
>
> insert into test values ('2016-01-01',1,2,3,4);
> insert into test values ('2016-01-02',5,6,7,8);
> insert into test values ('2016-01-03',9,10,11,12);
> inse
I noticed, that 9.5 release notes (beta2) do not mention
commit 7e2a18a9161fee7e67642863f72b51d77d3e996.
I think this one should be added.
--
Victor Y. Yegorov
2016-01-06 9:08 GMT+02:00 Oleg Bartunov :
> I agree with Jim, something is wrong, I see our developers community isn't
> growing and getting older. There is no formal problem to start contribute,
> but steep learning curve and lack of mentoring practice scare people.
I agree, that learning curve
2016-01-24 22:10 GMT+02:00 Adrian Klaver :
> Thought long and hard about this and while it is possible, it is not
> something I feel I should have to do. This conversation in its many threads
> has spun out of control and into areas that a) out of the scope of this
> list b) into conduct that woul
Greetings.
I'd like to understand why Postgres behaves the way it does.
I was not able to find relevant mail thread myself, if one exists — please,
point at it.
Test setup:
PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-16), 64-bit
create table
2016-02-22 18:00 GMT+02:00 Thomas Kellerer :
> I assume that this has to do with the fact that char(n) is blank padded to
> 20 character.
>
Yes, this was my thought too, but I do not understand what is going on in
details.
To be able to correctly compare that to a text value, sn_c has to be cas
2016-03-07 13:38 GMT+02:00 Geoff Winkless :
> # EXPLAIN (ANALYZE,BUFFERS) SELECT MIN(sc_id) FROM legs WHERE scdate
> BETWEEN 20160219 AND 20160221;
>
Will it help if you'll add `count(*)` to your query like this:
SELECT min(sc_id), count(*) FROM legs WHERE scdate BETWEEN 20160219 AND
2016022
2016-03-07 15:01 GMT+02:00 Geoff Winkless :
> Unfortunately the cost of changing all the code that uses MIN() in
> this way would be higher than just adding an extra index :(
>
> I suppose the thought is that for selecting just the MIN() value, by
> traipsing through the index you immediately find
2016-03-08 15:59 GMT+02:00 Alexander Farber :
> Here 1-pass version, if you have improvement suggestions, you are welcome -
>
My variant:
CREATE OR REPLACE FUNCTION arrexcept(anyarray, anyarray) RETURNS anyarray
AS $arrexcept$
SELECT array_agg(un) FROM (
SELECT un, row_number() OVER (PARTI
2016-03-21 16:42 GMT+02:00 Alexander Farber :
> I would prefer to have every stored function in a separate file (which
> would make reading git history easier too) and include them from words.sql.
>
> Is there such a thing for PostgreSQL 9.5.1 available please and if not -
> could you share your a
Greetings.
I cannot get understanding about what different types of blocks means here:
http://www.postgresql.org/docs/current/interactive/pgstatstatements.html
I assume, that `shared` are blocks from the `shared_buffers`
and `temp` are blocks used by temporary files (absence of `dirtied` and
`hi
2016-04-27 2:27 GMT+03:00 Tim van der Linden :
> The plan:
>
> Sort (cost=105773.63..105774.46 rows=333 width=76) (actual
> time=5143.162..5143.185 rows=448 loops=1)
>Sort Key: r.created
>Sort Method: quicksort Memory: 60kB
>-> Nested Loop (cost=1.31..105759.68 rows=333 width=76) (
2016-05-03 22:48 GMT+03:00 Steve Clark :
> select ip_dst as "Receiver" ,sum(bytes) as "RX Bytes" from acct_v9 where
> stamp_inserted >= '2016-04-26' and stamp_inserted <= '2016-04-30' and
> tag=246 group by ip_dst order by "RX Bytes" desc limit 10;
SELECT ip_dst AS "Receiver",
sum(bytes) AS
2016-05-09 11:01 GMT+03:00 Sterpu Victor :
> I went to 2.4 seconds by joining first the tables that produce many rows.
As you're changing your query quite often, it'd be handy, if you could post
both:
- new query version
- it's `EXECUTE (analyze, buffers)` output
If you provide either one or an
2016-05-16 14:47 GMT+03:00 Charles Clavadetscher :
> SQL version by Melvin Davidson:
>
> CREATE TABLE elephant
> (row_num integer NOT NULL,
> row_dat varchar(30) NOT NULL,
> CONSTRAINT elephant_pk PRIMARY KEY (row_num)
> );
>
> INSERT INTO elephant
> (row_num, row_dat)
> VALUES
> ( 1,'+-
2014-07-28 13:19 GMT+03:00 Ramesh T :
> select TZ_OFFSET ('US/Eastern');
>
> its'returning like function tz_offset('us/Eastern') does not exist;
>
SELECT *
FROM pg_catalog.pg_timezone_names
WHERE name='US/Eastern';
--
Victor Y. Yegorov
Greetings.
I'm observing the following on 9.3.5 and also on 9.4beta3:
\set AUTOCOMMIT on
CREATE TYPE enum_type AS ENUM ('bad', 'good');
CREATE TYPE
ALTER TYPE enum_type ADD VALUE 'so-so' AFTER 'bad';
ALTER TYPE
DROP TYPE enum_type;
DROP TYPE;
\set AUTOCOMMIT off
CREATE TYPE enum_type AS ENUM ('b
2014-10-20 21:43 GMT+03:00 Adrian Klaver :
> With \set AUTOCOMMIT off the COMMIT ended one transaction block and
> started another.
I don't think `COMMIT` starts a new transaction block here,
as I can run `VACUUM` after it, and vacuum also cannot be run inside
transaction block.
--
Victor Y.
2013/2/21 Devrim GÜNDÜZ
> Details are here:
>
> http://pglivecd.org
> http://yum.postgresql.org/livecd.php
>
Description mentions PostgreSQL 9.1.3 all around, is this correct?
--
Victor Y. Yegorov
2013/2/21 Devrim GÜNDÜZ
> I released fifth version of PostgreSQL live CD, which is based
> on PostgreSQL 9.2 and CentOS 6.3. It includes many of the PostgreSQL
> related packages that I build for http://yum.postgresql.org , along with
> PostgreSQL 9.2.3.
>
Devrim, image doesn't fit on a 700Mb CD
2013/3/8 Albe Laurenz
> This way you can also force a certain password expiry date
> (PostgreSQL does not have a password life time).
>
What bout ALTER ROLE ... VALID UNTIL 'timestamp' ?
--
Victor Y. Yegorov
2013/3/22 Alexander Farber :
> the result is correctly "true" now,
> but the warning is still there, why?
>
> # select 'axyz' ~ '(.)\\1\\1';
> WARNING: nonstandard use of \\ in a string literal
> LINE 1: select 'axyz' ~ '(.)\\1\\1';
> ^
> HINT: Use the escape s
2013/5/27 Amit Langote
> Although, I suspect the (dropped index + enable_seqscan) causes this,
> is the cost shown in explain output some kind of default max or
> something like that for such abnormal cases?
>
When one uses “enable_” settings to adjust planner behavior, PostgreSQL
just sets real
2013/5/31 Adarsh Sharma
> explain analyze select sum(total_cost)as
> cost,date_trunc('month',analytics_date)as monthDate from tableA
> where inr_id in(select id from tableB where ct_id
> ='4028cb972f1ff337012f1ffa1fee0001') and analytics_date between '2013-01-14
> 00:00:00' and '2013-05-29 00:00:
2013/6/1 David Salisbury
> I would think this would be possible. I'm on 9.0.8
>
Data-Modifying CTEs are available since 9.1:
http://www.depesz.com/2011/03/16/waiting-for-9-1-writable-cte/
Please note, that CTE acts as an optimization fence, therefore you might
experience query slowdown.
--
2013/6/1 Shenli Zhu
> Hi, there are 2 columns, flag(boolean) and num(integer),in a table.
> Table is like
> | flag | num |
> |--+-|
> |1 | 2 | \ 5
> |1 | 3 | /
> |0 | 1 | \ 7
> |0 | 6 | /
> |1 | 4 | \ 9
> |1 | 5 | /
> | ... | ... |
> I want to sum up t
2013/6/19 Jashaswee
> The column value looks like this:
>
> Quantity
> 2000
> -1000
>
> both the quantity values are of a same product.but i want these in a
> single
> line.
> so what i want is a result set that looks like:
> In quantity Out quantity
> ---
> 2000
ation WHERE queue_name='q-2rm';
And restarted worker after that.
--
Victor Yegorov
akes ages to switch to the declarative partitioning the way
things stand now.
Thanks in advance.
--
Victor Yegorov
2017-09-29 20:32 GMT+03:00 Victor Yegorov :
>
> Is it possible to avoid Full Scan here? I have TBs worth of data in
> partitions,
> so it'll takes ages to switch to the declarative partitioning the way
> things stand now.
>
OK, looking at the source code helped — I nee
d more details in this post: http://blog.postgresql-
consulting.com/2017/03/deep-dive-into-postgres-stats_27.html
(You might want to reset 'shared' stats here.)
--
Victor Yegorov
c087d14
And also this query:
SELECT name,version,source FROM pg_settings WHERE source NOT IN
('default','override');
--
Victor Yegorov
2013/10/1 Perry Smith
>
> On Oct 1, 2013, at 12:23 PM, Adrian Klaver
> wrote:
>
> > Assuming you are not doing this in a function, you can. Do UPDATE, then
> SELECT to see your changes or not and then ROLLBACK.
>
> Ah... yes. I forgot you can see the changes within the same transaction.
> Dohh
2013/10/15 Svetlin Manavski
> I have some idle transactions in PostgreSQL 9.2.4 server which never end.
> My application was working fine on version 9.1 (BSD) but the problem
> appeared immediately as we ported it to 9.2.4 on Linux. The idle operations
> would frequently appear as COMMITs but som
Greetings.
I've hit a strange issue. This is a simplified setup.
First, run create.sql to get a couple of tables.
Then, run the update query.
Tested on 9.2.6 and 9.3.2.
Now:
- if I remove the UNION ALL part of the inner query, UPDATE works;
- if I move the `raw` subquery into the CTE, UPDATE wor
Greetings.
I'm looking at a plan: http://explain.depesz.com/s/sPv
And I have a question. Why in the following snippet `Sort` node
reports 128818 rows, although
child node returned only 352?
"-> Sort (cost=37.06..37.94 rows=352 width=20) (actual time=4.441..16.971
rows=128818 loops=1)
" So
2014-03-04 10:19 GMT+02:00 Adarsh Sharma :
>
> PLAN
>
>
> -
> HashAggregate (cost=10651634346.70..10651780073.12 rows
2014-03-05 6:53 GMT+02:00 Adarsh Sharma :
> Anyhow working on finding the root cause.
I would do the following:
1. Check your `default_statistics_target`, like:
SHOW default_statistics_target;
2. Your previously posted `EXPLAIN ANALYZE` with `set enable_seqscan=off`
shows
a signif
Greetings.
I have a partitioned table, that can be reproduced the following way:
CREATE TABLE ara (
ara_id int4,
namevarchar(11),
run_id int4,
set_id int4,
created_at timestamp,
CONSTRAINT p_ara PRIMARY KEY (ara_id)
);
DO $partition$
DECLARE
_ta
2014-05-20 14:26 GMT+03:00 Tom Lane :
> Victor Yegorov writes:
> > How can I enforce pruning to kick in for the initial expressions?
>
> You already found out: use constants. The planner can't remove
> partitions on the basis of clauses involving volatile, or even
>
Check this article:
http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/
2012/9/20 pavithra
> Hi All, I am new to postgresql. I want to send email by using pl pgsql. I
> want to know how to set up the configurations for mail server. Can any one
> help me in solving this?. [hidden
>
Take a look at this part of the documentation:
http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY
The “missing” entries belong to the tuples that you have DELETEd/UPDATEd
and that are no longer visible
to your current session, but still might be for
2012/9/21 Jeff Janes
> To start with, it can be as you say where the ctid and its tuple are
> interesting to someone, but not to you. But eventually the tuple is
> not interesting to anyone, and its space can be reused. But the ctid
> is still needed (to inform stragglers that it's correspondin
45 matches
Mail list logo
