W dniu 07.03.2016 o 20:11, Francisco Olarte pisze:
[---]
>
> When in a single process problem like this I normally use an
> second timestamp which I autoincrement if repeated, something
> like:
>
> get_timestamp_for_id() {
> Locked(mutex) {
> now=time();
> if (last
On Mon, Mar 7, 2016 at 11:33 PM, Tom Lane wrote:
> James Sewell writes:
> > Would anyone be able to shed some light on why expression based indexes
> > can't be used for an index only scan?
> > I've found a few comments saying this is the case, and I've proven it is
> > the case in reality - but
James Sewell writes:
> Would anyone be able to shed some light on why expression based indexes
> can't be used for an index only scan?
> I've found a few comments saying this is the case, and I've proven it is
> the case in reality - but I can't seem to find the why.
Well, it would help if you po
Hello,
Would anyone be able to shed some light on why expression based indexes
can't be used for an index only scan?
I've found a few comments saying this is the case, and I've proven it is
the case in reality - but I can't seem to find the why.
Cheers,
James Sewell,
Solutions Architect
__
From: Jan de Visser [mailto:j...@de-visser.net]
You're being pretty oblique about what it is you're trying to achieve.
[dmb>] Sorry you see it that way. I know exactly where I'm trying to get to,
but it would take many pages to explain and I don't want to unduly trouble
other busy people.
To go
On Mon, Mar 7, 2016 at 8:15 PM, Curt Huffman wrote:
> Thanks Rob & David!
>
> I got it to work using the following:
>
>
> String qry = "INSERT INTO event "
> + "(spotid, qid, userid, persid, , "
> + "evtvalue, evtdt, evtjson) "
> + "VALUES(?,?,?,?,?,?,?,?,?,?,to_json(?::json));";
>
>
This is
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jan de Visser
One thing that's probably key here is that pgsql isn't multi-threaded.
Individual connections are handled by forked backends, which share a shared-
memory cache that's not accessible
On 4 March 2016 at 23:02, Alvaro Aguayo Garcia-Rada wrote:
> Hi. I currently have two servers in different geographical locations; both
> of them are replicating with Postgres-BDR, that's OK. However, I need two
> more servers to get a read only replication of only some tables from the
> master o
On March 8, 2016 11:35:00 AM da...@andl.org wrote:
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
>
> this stuff you're loading from the database once, that's just data about
> your language plugin's configuration, or is it user
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
this stuff you're loading from the database once, that's just data about your
language plugin's configuration, or is it user data, or what?
[dmb>] It's the catalog for Andl. It contain
Hi all
I would like to log from within pgsql into table and/or to command
line, something like https://sourceforge.net/projects/plnsqllogger/ .
Does anybody know of an open source solution available?
Kind regards
Thiemo
--
Auf Gelassene Pferde kann man bauen!
+49 (0)1578-772 37 37
+41 (0)
On 3/7/2016 3:28 PM, da...@andl.org wrote:
[dmb>] Thanks. Yes, I knew that. My problem is a strategy to start up the
language engine once (per session), load stuff from the database once, and then
keep the per-call cost as low as possible
this stuff you're loading from the database once,
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jan de Visser
One thing that's probably key here is that pgsql isn't multi-threaded.
Individual connections are handled by forked backends, which share a shared-
memory cache that's not accessible
On 7 March 2016 at 20:23, Jeff Janes wrote:
> PostgreSQL does not (yet) implement "loose" index scans or "skip
> scans", which is what you are asking for. You can roll your own using
> the techniques described here:
> https://wiki.postgresql.org/wiki/Loose_indexscan, which has the
> benefit over
On 2016-03-07 16:37:37 +, Geoff Winkless wrote:
> On 7 March 2016 at 16:02, Tom Lane wrote:
> > In English, what that plan is trying to do is scan the index
> > in sc_id order until it hits a row with scdate in the target range.
> > The first such row, by definition, has the correct min(sc_id)
On Mon, Mar 7, 2016 at 9:35 AM, Geoff Winkless wrote:
> On 7 March 2016 at 16:44, Tom Lane wrote:
>> Geoff Winkless writes:
>>> But as far as I can see, apart from the absolute extremes, the
>>> index-only scan is _always_ going to be quicker than the index+table
>>> scan.
>>
>> Well, that is a
On Mon, Mar 7, 2016 at 8:37 AM, Geoff Winkless wrote:
>
> But as far as I can see, apart from the absolute extremes, the
> index-only scan is _always_ going to be quicker than the index+table
> scan.
If relallvisible is zero, it thinks it gets zero benefit from an index
only scan. It thinks that
On Mon, Mar 7, 2016 at 5:01 AM, Geoff Winkless wrote:
> On 7 March 2016 at 11:48, Victor Yegorov wrote:
>> 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 ad
Be advised, the --section option is only available from 9.2 and up.
On Mon, Mar 7, 2016 at 2:08 PM, Adrian Klaver
wrote:
> On 03/07/2016 10:22 AM, Karsten Hilbert wrote:
>
>> On Mon, Mar 07, 2016 at 11:03:39AM -0700, David G. Johnston wrote:
>>
>> The man page of pg_restore says
Hi Rafal:
On Mon, Mar 7, 2016 at 9:29 AM, Rafal Pietrak wrote:
.
>> be slower ). And you introduce several problems, the naming ones, a
>> very strange foreign-key relationship between kids, the possibility of
>> having a row inserted in the parent.
> No, no. It was OK. the parent table was a
On 03/07/2016 10:22 AM, Karsten Hilbert wrote:
On Mon, Mar 07, 2016 at 11:03:39AM -0700, David G. Johnston wrote:
The man page of pg_restore says
--disable-triggers
This option is relevant only when performing a
data-only restore. It instructs pg_rest
On Mon, Mar 07, 2016 at 11:03:39AM -0700, David G. Johnston wrote:
> > The man page of pg_restore says
> >
> > --disable-triggers
> > This option is relevant only when performing a
> > data-only restore. It instructs pg_restore to execute
> >
On Mon, Mar 7, 2016 at 10:17 AM, Karsten Hilbert
wrote:
> The man page of pg_restore says
>
> --disable-triggers
> This option is relevant only when performing a
> data-only restore. It instructs pg_restore to execute
> commands to temporari
On 7 March 2016 at 16:44, Tom Lane wrote:
> Geoff Winkless writes:
>> But as far as I can see, apart from the absolute extremes, the
>> index-only scan is _always_ going to be quicker than the index+table
>> scan.
>
> Well, that is a different issue: what does the planner think of an
> index-only
On 03/07/2016 09:17 AM, Karsten Hilbert wrote:
The man page of pg_restore says
--disable-triggers
This option is relevant only when performing a
data-only restore. It instructs pg_restore to execute
commands to temporarily disable triggers
I see. Thanks again! I removed the "port" data type from my implementation and just used the ORDER BY... USING clause and the explain output/results is providing what I expect.StevenThat is an editing mistake. I mean> hasegeli=# SELECT port FROM device_port ORDER BY cast_to_port(port);
On Mon, 2016-03-07 at 09:32 -0700, David G. Johnston wrote:
>
> >
> > Hint: You will need to rewrite or cast the expression.
> >
> Take the hint, literally. You never did show the SQL but usually the
> least complex way to solve this is to indeed transfer the data as a
> string/text and then in
The man page of pg_restore says
--disable-triggers
This option is relevant only when performing a
data-only restore. It instructs pg_restore to execute
commands to temporarily disable triggers on the
target tables while the da
Geoff Winkless writes:
> But as far as I can see, apart from the absolute extremes, the
> index-only scan is _always_ going to be quicker than the index+table
> scan.
Well, that is a different issue: what does the planner think of an
index-only scan as compared to a regular index scan. I suspect
On 7 March 2016 at 16:02, Tom Lane wrote:
> In English, what that plan is trying to do is scan the index
> in sc_id order until it hits a row with scdate in the target range.
> The first such row, by definition, has the correct min(sc_id) value.
> The problem is that we're guessing at how soon we'
On 03/07/2016 05:25 AM, Curt Huffman wrote:
Hello!
I’m struggling to insert a JSON object into my postgres v9.4 DB. I have
defined the column called "evtjson" as type json. (not jsonb)
I am trying to use a prepared statement in Java (jdk1.8) to insert a
Json object (built using JEE javax.json li
> pStmt.setString(11, dtlRec.toString());
> pStmt.setObject(11, dtlRec.toString());
>
> Which produce a different error:
>
> Event JSON: {"New MbrID":29}
>
> SQLException: ERROR: column "evtjson" is of type json but expression is of
> type character varying
>
> Hint: You will need to rewrite or cas
Hi!!!
I try to explain my problem...sorry for my english :(
In pgsql 9.5.1 I have a two tables with the next structure:
1. Tabla unlogged «public._gc_cat»
Columna | Tipo | Modificadores
-+--+---
idppicat| integer |
Hello!
I’m struggling to insert a JSON object into my postgres v9.4 DB. I have
defined the column called "evtjson" as type json. (not jsonb)
I am trying to use a prepared statement in Java (jdk1.8) to insert a Json
object (built using JEE javax.json libraries) into the column, but I keep
running i
Geoff Winkless writes:
> On 7 March 2016 at 14:51, Tom Lane wrote:
>> Because the other way is estimated to be cheaper. The estimate is
>> wrong, because it's based on a statistical assumption that's wrong
>> (ie that sc_id and scdate are uncorrelated), but it's what we have
>> to work with at t
On 7 March 2016 at 14:51, Tom Lane wrote:
> Geoff Winkless writes:
>> So it seems that it should in fact be usable after all. So I'm still
>> stumped as to why the (scdate,sc_id) index isn't used :(
>
> Because the other way is estimated to be cheaper. The estimate is
> wrong, because it's based
> Although, I do find your output very strange. You wrote ORDER BY port, which
> is a text type. Why does Postgres order using the ordering operators of the
> "Port" data type rather than the "text" type, even though you haven't
> performed a cast?
That is an editing mistake. I mean
> hasegeli=#
Thank you for your response.
Yes, I was aware of GD and SD. My question is about what facilities Postgres
provides for implementing such a thing. Where is the proper place for the root
of the SD/GD? What does an implementation use to determine that two calls
belong to the same session?
I
Hi,
We are running Streaming replication with Hot standby in our current
production. I do have below script to check replication status and replication
lag. Does anyone have script which runs every 15 mins to check replication
status and send out email if replication is not running or lagging b
On 03/06/2016 10:18 PM, fred...@huitfeldt.com wrote:
HI All,
i would really appreciate any help I can get on this issue.
basically, a pg_basebackup + streaming attach, led to a database that we
could not read from afterwards.
From original post:
http://www.postgresql.org/message-id/14569196
Hi Emre,Thanks for your tips! I think I'll do as you suggest and keep it simple with a single ordering operator.Although, I do find your output very strange. You wrote ORDER BY port, which is a text type. Why does Postgres order using the ordering operators of the "Port" data type rather than the "
Geoff Winkless writes:
> So it seems that it should in fact be usable after all. So I'm still
> stumped as to why the (scdate,sc_id) index isn't used :(
Because the other way is estimated to be cheaper. The estimate is
wrong, because it's based on a statistical assumption that's wrong
(ie that s
On 7 March 2016 at 14:27, I wrote:
> So it seems that it should in fact be usable after all. So I'm still
> stumped as to why the (scdate,sc_id) index isn't used :(
Also, while the index on sc_id will be sorted there's no guarantee
that sc_id values will be in order in the table itself, so you're
On 7 March 2016 at 14:18, I wrote:
> That assumes that I've not completely misunderstood, of course :)
Always a dangerous assumption, I'm rapidly learning.
The very next section:
Constraints on columns to the right of these columns are checked
in the index, so they save visits to the table
On 7 March 2016 at 13:23, Victor Yegorov wrote:
> Your `sc_id` and `scdate` columns are correlated.
Actually not necessarily, although in the majority case that's mostly true.
> Planner has no such knowledge and assumes columns being independent. Your
> `scdate` predicate is
> estimate to return
On March 8, 2016 12:18:08 AM da...@andl.org wrote:
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
>
>
>
> Yes, I was aware of GD and SD. My question is about what facilities Postgres
> provides for implementing such a thing. Wh
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
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Yes, I was aware of GD and SD. My question is about what facilities Postgres
provides for implementing such a thing. Where is the proper place for the root
of the SD/GD? What does a
On 7 March 2016 at 11:48, Victor Yegorov wrote:
> 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),
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
Hi all
Firstly, I appreciate that my index problems are fairly difficult to
debug given that I can't upload the data anywhere (it's commercially
sensitive); I tried creating an equivalent dataset for my last problem
using a lot of random() inserts, but unfortunately, even though the
sizes and inde
On 3/7/2016 1:34 AM, da...@andl.org wrote:
Yes, I was aware of GD and SD. My question is about what facilities
Postgres provides for implementing such a thing. Where is the proper
place for the root of the SD/GD? What does an _implementation_ use to
determine that two calls belong to the same
Thank you for your response.
Yes, I was aware of GD and SD. My question is about what facilities Postgres
provides for implementing such a thing. Where is the proper place for the root
of the SD/GD? What does an implementation use to determine that two calls
belong to the same session?
I
W dniu 05.03.2016 o 19:53, Francisco Olarte pisze:
> Hi Rafal:
>
> On Fri, Mar 4, 2016 at 11:46 PM, Rafal Pietrak wrote:
>> W dniu 04.03.2016 o 18:59, Francisco Olarte pisze:
>>> Make sender_person_id NOT NULL in messages if you want to insure every
>>> message ahs exactly ONE SENDER, leave it
54 matches
Mail list logo
