st_partition_timestamp->time).
Please, answer questions:
1. How many rows SPI_execute returns (value of "ret" variable)?
2. Is last_partition_timestamp != NULL? Where it points to?
3. Try to check SPI_result just after SPI_getbinval. Has it error code?
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 7/29/16, Keith Fiske wrote:
> On Fri, Jul 29, 2016 at 12:53 AM, Vitaly Burovoy
> wrote:
>
>> On 7/28/16, Keith Fiske wrote:
>> > Working on trying to get a C version of the maintenance function for my
>> > pg_partman extension working so I can hopeful
On 7/29/16, Keith Fiske wrote:
> On Fri, Jul 29, 2016 at 11:49 AM, Vitaly Burovoy
> wrote:
>
>> On 7/29/16, Keith Fiske wrote:
>> > On Fri, Jul 29, 2016 at 12:53 AM, Vitaly Burovoy <
>> vitaly.buro...@gmail.com>
>> > wrote:
>> >
>> >
016-09-13'
>
>
>
> But it didn't work... it returns 0 rows but there are rows to be shown:
>
>
> select modified_date from clients ORDER BY modified_date ASC
>
>
> modified_date
>> ---
>> 2015-07-11 17:23:40
it is 2015
ELECT
jsonb_set(
CASE
WHEN DATA ? 'boo'
THEN DATA
ELSE jsonb_set(DATA, array['boo'], '{}')
END,
'{boo,baz}'::text[],
'"newvalue"'
)
FROM (VALUES('{"foo": "bar"}'::jsonb)) AS t(data)
[1] https://www.postgresql.org/docs/devel/static/functions-json.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> On Sep 23, 2016 5:12 PM, "Vitaly Burovoy" wrote:
> On 9/23/16, Deven Phillips wrote:
>> On Fri, Sep 23, 2016 at 10:14 AM, Deven Phillips
>> wrote:
>>> Is there a way to set a nested element for which the parent paths do not
>>> yet exist?
>&
VER()... FROM ins_table_1
) ins ON (...)
Note than CTEs not have indexes and a join process is not fast (for
bigger number of rows).
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
another table you should use
UPDATE ... SET ... FROM ... WHERE ...
clause described in the docs[1] (see example around the sentence "A
similar result could be accomplished with a join:" and note below).
[1] https://www.postgresql.org/docs/devel/static/sql-update.html
--
Best regard
ses supposes updating
rows of the original table to replace entries to references to a
dictionary.
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
1
)t WHERE s._pk_column_=t._pk_column_;
> Unfortunately I can't test on product servers, so Im looking for some
> advice or some one to point me the right direction how I can alter table
> today without clients to notice their query is locked and need to wait.
[1] https://www.pos
ate".
There is no update at all.
ON CONFLICT handling just does not cover all possible ways which can happen.
Normally (without "ON CONFLICT" clause) INSERT raises "duplicate key
value violates unique constraint" and doesn't run to
"ExecCheckHeapTupleVisible" check.
The "ExecInsert" handles constraint checks but not later checks like
ExecCheckHeapTupleVisible.
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 1/4/17, Gwork wrote:
> On 1/5/17 2:22 AM, Vitaly Burovoy wrote:
>> On 1/4/17, Vitaly Burovoy wrote:
>>> On 1/4/17, Gwork wrote:
>>>> Version: Postgresql 9.5
>>>> OS: Debian 8 jessie run on docker
>>>>
>>>> Following this tut
On 1/4/17, Gwork wrote:
> On 1/5/17 2:51 AM, Vitaly Burovoy wrote:
>> On 1/4/17, Gwork wrote:
>>> On 1/5/17 2:22 AM, Vitaly Burovoy wrote:
>>>> On 1/4/17, Vitaly Burovoy wrote:
>>>>> On 1/4/17, Gwork wrote:
>>>>>> V
low).
If you use a condition like " between colA and colB", some sort
of a "timerange" is the best case. Unfortunately there is no such
type, but it is easy to create it by an example[1].
Then you can use btree_gin or btree_gist (depending on a base index
type) extension to us
have any other before and after it.
In the second query subselect selects ALL rows to the "virtual table",
lead and lag fill values and WHERE in the external select gets a
single row filled by subselect.
It is also in the documentation:
"If there is a need to filter or group rows
table, all 13.5M rows):
> -> Seq Scan on "inodes" "t" (cost=0.00..1411147.24 rows=13416537 width=29)
> (actual time=94987.224..94987.224 rows=0 loops=1)
> Filter: ("timezone"('UTC'::"text", "transaction_timestamp"()) >
>
On 1/10/16, Vitaly Burovoy wrote:
> On 1/10/16, Saulo Merlo wrote:
>> I've got a slow query.. I'd like to make it faster.. Make add an index?
>> Query:
>> SELECT
>> <>
>> FROM gorfs.nodes AS f
>> <>
>> WHERE f.file_da
g/docs/9.2/static/sql-createindex.html
> I've rewriten the query as well. Thank you for that!
>
> Thank you
> Lucas
>> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> From: vitaly.buro...@gmail.com
>> To: smerl.
ql-createindex.html
>>
>> > I've rewriten the query as well. Thank you for that!
>> >
>> > Thank you
>> > Lucas
>>
>> >> Date: Sun, 10 Jan 2016 21:23:01 -0800
>> >> Subject: Re: [GENERAL] Slow Query - PostgreSQL 9.2
>> >
fs.nodes is a view.
>> >> > And the schema is: gorfs.inode_segments
>> >> > So... CREATE INDEX index_name ON gorfs.inode_segments(st_ctime)
>> >> > Is that correct? It would be "st_ctime"?
>> >> If "inodes" is an a
not necessarily connected
to a gender of real user who is reading the doc and in most cases such
pronouns are "masculine", that's why we don't concern to replace "he"
to "he/she" or somewhat else.
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general m
onMonthAge . ' months\' ::
INTERVAL)". You have to change the condition the way where one part of
a condition at an optimization part can be simplified to a constant
and the other part of the condition represents a column of an existent
index (as it was written in my first answer).
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
.
> How could I create a timestampandtz index?
> CREATE TABLE gorfs.inode_segments
> (
> <>
> )
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
o the
mailing list.
[1]http://www.postgresql.org/docs/9.2/static/sql-altertable.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
e
> Joomla team with this suggestion. That is as much as I can do to speed
> up PostgreSQL uptake in Joomla for now.
>
> Thanks for providing postgreSQL to us.
You are welcome.
>
> Best wishes,
> Dominic
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing
lue1 and col2 = value2 and ...;
SELECT id, col1, col2, ... FROM t0 INNER NATURAL JOIN temptable;
SELECT col1 FROM t1 INNER JOIN temptable ON (t0_id = temptable.id);
COMMIT;
[1]http://www.postgresql.org/docs/9.5/static/sql-createtable.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-gener
(id) from t0 where col1 = value1 and col2
> = value2 and ...);
I don't think it is a good solution because it leads to copying
columns from the t0 which is wasting net traffic and increasing
complexity at the client side. Moreover it works iff t0 returns only
one row.
>
> --
> Ad
select id, col1, col2, ... from t0 where id = (select max(id) from t0
> where col1 = value1 and col2 = value2 and ...);
> select col1 from t1 where t0_id = (select max(id) from t0 where col1 =
> value1 and col2 = value2 and ...);
> commit;
>
> Best regards Johannes
--
Best regards,
ments?
>
> JDBC. I execute both statements at once and iterate through the resultsets.
>
> Johannes
Hmm. Could you clarify why you don't want to pass id from the first
query to the second one:
select col1 from t1 where t0_id = value_id_from_the_first_query
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 2/8/16, Johannes wrote:
>
> Am 08.02.2016 um 20:32 schrieb Vitaly Burovoy:
>> On 2/8/16, Adrian Klaver wrote:
>>> Based on rough guess of the above, without seeing actual table schemas:
>>>
>>> select id, t0.col1, t1.col1, col2, ... from t0 JOIN t1 ON
On 2/8/16, Johannes wrote:
> Am 08.02.2016 um 21:17 schrieb Vitaly Burovoy:
>> On 2/8/16, Johannes wrote:
>>> Am 08.02.2016 um 20:15 schrieb David G. Johnston:
>>>> On Mon, Feb 8, 2016 at 12:05 PM, Johannes wrote:
>>>>
>>>>> Hi,
>&
ol2)", and the second one returns rows "(val1), (val2), (val3), ..."
(values of the t1.col1).
If you use joining, you get rows:
(id, col1, col2, val1)
(id, col1, col2, val2)
(id, col1, col2, val3)
...
where values of the first three columns are the same.
>
> Alban Hertr
On 2/8/16, Johannes wrote:
> Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy:
>> On 2/8/16, Johannes wrote:
>>> Am 08.02.2016 um 21:17 schrieb Vitaly Burovoy:
>>>> Hmm. Could you clarify why you don't want to pass id from the first
>>>> query to the
ext query again. So the server can compute
>> the result set without external dependencies as fast as possible.
>
> Sounds like what you're really after is a stored procedure, isn't it?
Unfortunately, his case is different, because he needs to get two
different set of rows
://www.postgresql.org/message-id/cakoswnkrb0kfwhcw9cvocrmks8huzrpvflr0kkcjuyn6juk...@mail.gmail.com
[3]http://www.postgresql.org/message-id/CAKOSWN=wX9Myw4q9mpiqESyizU4tWrGivgbw+3Ef=5q60vi...@mail.gmail.com
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 2/9/16, Johannes wrote:
> Am 09.02.2016 um 00:56 schrieb Vitaly Burovoy:
>> On 2/8/16, Johannes wrote:
>>> Am 08.02.2016 um 21:50 schrieb Vitaly Burovoy:
>>>> On 2/8/16, Johannes wrote:
>>>>> increase speed,
>>>>
>>>>
01T00:00:00Z'::timestamptz;
timestamptz
2016-01-01 00:00:00+00
(1 row)
"to_timestamp" is used for some complex cases:
> to_timestamp and to_date exist to handle input formats
> that cannot be converted by simple casting.
> ERROR: invalid val
imestampTZ FROM (VALUES('2016-01-01T00:00:00Z'))t(ts);
ts | ts | ?column?
-++--
2016-01-01 00:00:00 | 2016-01-01 11:00:00+11 | t
(1 row)
If you want to compare using specific time zone, you have to convert
input values to it:
...
WHERE
data->>'id'=$1 AND
($2 AT TIME ZONE 'America/New_York') <= (data->>'timestamp')::timestamptz
AND
(data->>'timetsamp')::timestamptz <= ($3 AT TIME ZONE
'America/New_York') -- "<=" or just "<"?
ORDER BY
...
> http://www.postgresql.org/docs/9.5/interactive/functions-formatting.html
> "Ordinary text is allowed in to_char templates and will be output literally.
> You can put a substring in double quotes to force it to be interpreted as
> literal text even if it contains pattern key words. For example, in '"Hello
> Year "', the will be replaced by the year data, but the single Y in
> Year will not be. In to_date, to_number, and to_timestamp, double-quoted
> strings skip the number of input characters contained in the string, e.g.
> "XX" skips two input characters."
>
>>
>> Thanks in advance!!!
>> Deven
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ck
WHERE
t.id=lck.id
>
> Thank you,
> Nikolai
[1]http://www.postgresql.org/docs/current/static/queries-with.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ndex is
used in an EXPLAIN of any query that use it or not. In the first case
do COMMIT, in the second case just do ROLLBACK and leave old index for
using.
> Thanks!
> Victor
[1]http://www.postgresql.org/docs/devel/static/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
[2]http://www.postgresql.org/d
es are copied
wrongly).
>
> --
> john r pierce, recycling bits in santa cruz
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
user';
END IF;
END
$func$ LANGUAGE plpgsql;
would give "invalid user". NULL works as FALSE at the top of IF expressions.
For more information see[1].
> COALESCE could be wrapped around the check_user() call in the
> IF-statement... but is there maybe a nic
On 3/2/16, Alexander Farber wrote:
> On Wed, Mar 2, 2016 at 11:09 AM, Vitaly Burovoy
> wrote:
>
>> On 3/2/16, Alexander Farber wrote:
>> >
>> > CREATE OR REPLACE FUNCTION check_user(
>> > in_social intege
s 2.8sec! and index scan in ix_notes_jobid_per_type
500rows(loops) * 9.878ms!!! = 4.939sec.
Why does it take so long time?
For example, index scan in ja_customers_pkey is only 0.781 per row...
10 times faster!
What definition of the ix_notes_jobid_per_type? Is it bloated?
> Explain analyze lin
On 3/2/16, drum.lu...@gmail.com wrote:
> On 3 March 2016 at 10:33, Vitaly Burovoy wrote:
>> On 3/2/16, drum.lu...@gmail.com wrote:
>> > Hi all...
>> >
>> > I'm working on a Slow Query. It's faster now (It was 20sec before) but
>> > still
searched but not found a definitive answer, or maybe that should read
> 'one I can understand'.
>
> I'm using PostgreSQL 9.5 on Linux with pgAdmin III on Mac.
>
> --
> Bill
[1] http://www.postgresql.org/docs/9.5/static/functions-json.html
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 4/13/16, Vitaly Burovoy wrote:
> On 4/13/16, sighup wrote:
>> Hi, please excuse either my stupidity or naivety regarding this but I'm
>> a bit confused. Give the following basic table structure :
>>
>> TABLE Data (
>> ID INT NOT NULL,
>>
at's why it can
ignore existing indexes.
--
Best regards,
Vitaly Burovoy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ecision including
random_page_cost and seq_page_cost for tablespaces; fillfactor for
indexes and tables and many more.
You have sent neither table DDL nor EXPLAIN ANALYZE result.
If a query runs fast enough, I would not pay attention to used access method.
[1]
https://www.postgresql.org/message-
was changed by one hour a week ago.
> Maybe postgres didnt recognized it.
>
> Posted also in
>
> http://stackoverflow.com/questions/43090328/how-to-return-correct-local-time-in-postgres
>
> Andrus.
>
Try
SELECT now();
or
SELECT current_timestamp;
--
Best regards,
Vita
On 3/29/17, Vitaly Burovoy wrote:
> On 3/29/17, Andrus wrote:
>>
>> select current_time at time zone 'GMT-2'
>>
>> returns
>>
>> "11:54:40.22045+02"
>>
>> but correct local time in Windows is one hour diff
51 matches
Mail list logo
