Thanks Tom, I did solve the problem by adding the null constraint for now,
it's a quick solution, and I look forward to the future where this case is
handled appropriately.
On Wed, 20 Feb 2019 at 12:17, Tom Lane wrote:
> I wrote:
> > Try it like
>
> > EXPLAIN SELECT COUNT(*) FROM "user_event" WH
I wrote:
> Try it like
> EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->>
> 'suggestion_id'::text) = '26') AND what = 'suggestion_notification';
> I don't think we assume that CoerceViaIO is strict, and without that
> the deduction that the value couldn't be null doesn't hold.
Hm
Hello, we are almost out of space on our main data directory, and about to
introduce new directory to our cluster. We cannot use multiple physical disks
as a single volume, so we are thinking about creation new tablespace.
Our current data_directory shows as follows:
/data/postgresql/9.6/main
pos
On 2/19/19 1:48 PM, Rob Nikander wrote:
I thought if I had some application logic that needed a certain kind of
transaction (maybe a non-default isolation level), I could hide that
fact in a procedure. App code (Java/Python/whatever) could remain
unaware of transactions (except maybe needing to
I thought if I had some application logic that needed a certain kind of
transaction (maybe a non-default isolation level), I could hide that fact in a
procedure. App code (Java/Python/whatever) could remain unaware of transactions
(except maybe needing to retry after a failure) and simply send `
So, I checked my original code, and I didn't add `::text`, it was actually
added by Postgres:
EXPLAIN for: SELECT "user_event".* FROM "user_event" WHERE
"user_event"."what" = $1 AND (((parameters ->> 'suggestion_id')::integer) =
119 AND ((parameters ->> 'suggestion_id') IS NOT NULL)) [["what",
"su
>
> On Wed, 20 Feb 2019 at 10:14, Tom Lane wrote:
>
>> Samuel Williams writes:
>> > When I do this query:
>>
>> > EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->>
>> > 'suggestion_id'::text)::integer = 26) AND what =
>> 'suggestion_notification';
>>
>> > It's slow. I need to expli
On Tue, Feb 19, 2019 at 1:41 PM Samuel Williams <
space.ship.travel...@gmail.com> wrote:
> Hello
>
> I have a table with ~3 billion events.
>
> Of this, there are a small subset of events which match the following
> query:
>
> CREATE INDEX index_user_event_for_suggestion_notification ON
> public.u
Thanks for the quick reply Tom,
I will try your advice.
The reason why I used ::integer for the INDEX is because I assumed it would
be more efficient both in space and performance.
In the JSONB field, it is actually an integer, i.e. {"location_age": 1,
"suggestion_id": 26}
So, now that I think
Samuel Williams writes:
> When I do this query:
> EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->>
> 'suggestion_id'::text)::integer = 26) AND what = 'suggestion_notification';
> It's slow. I need to explicitly add the NULL constraint:
Try it like
EXPLAIN SELECT COUNT(*) FROM "
Hi,
I have the following configuration.
pg9.6_master --> streaming replica pg9.6_standy
--> pg_logical replication of several schemas to pg9.6_logical
We plan to perform a managed fail over of pg9.6_master to pg9.6_standy.
As part of the fail over, the virtual IP address and hostnam
Hello
I have a table with ~3 billion events.
Of this, there are a small subset of events which match the following query:
CREATE INDEX index_user_event_for_suggestion_notification ON
public.user_event USING btree parameters ->>
'suggestion_id'::text))::integer), what) WHERE ((parameters ->>
On 2/19/19 12:31 PM, Rob Nikander wrote:
Hi,
I’m trying to understand how procedures work with transactions. I tried the
code below - it’s a simple procedure to print some notices and commit a
transaction. If I call it from psql after a `begin`, then it gives an error.
What does that error me
On Tuesday, February 19, 2019, Rob Nikander wrote:
> Are procedures not allowed to commit/rollback if they are called within
> in an outer transaction?
>
https://www.postgresql.org/docs/11/sql-call.html
Also, I tried putting a `start transaction` command in the procedure. I got
> another erro
Hi,
I’m trying to understand how procedures work with transactions. I tried the
code below - it’s a simple procedure to print some notices and commit a
transaction. If I call it from psql after a `begin`, then it gives an error.
What does that error mean? Are procedures not allowed to commit/ro
Greetings,
* Martín Fernández (fmarti...@gmail.com) wrote:
> On Tue, Feb 19, 2019 at 1:37 PM Stephen Frost wrote:
> > * Martín Fernández (fmarti...@gmail.com) wrote:
> > > Thanks for information! I've refactor our migration scripts to follow
> > the suggestions.
> >
> > Please don't top-post on t
1) You can increase it as much as you want but (auto)analyze will take
longer to examine the values of default_stat_target * 300 rows and compute
the most common values and the frequencies of those values. How much
variation does you data actually have? If your data only has 50 distinct
values with
I call pg_ctl -D /var/lib/pgsql/10/data promote to upgrade slave to master
when failover happens
I repoint slave to the master by stopping it, updating recovery.conf and
restarting it. Let me know if I'm doing it wrong.
Thank you for clarifying that contents of WAL files from stream and fetch
meth
Stephen,
@bilby91
On Tue, Feb 19, 2019 at 1:37 PM Stephen Frost wrote:
> Greetings,
>
> * Martín Fernández (fmarti...@gmail.com) wrote:
> > Thanks for information! I've refactor our migration scripts to follow
> the suggestions.
>
> Please don't top-post on these mailing lists.
>
> > One extra
Greetings,
* Martín Fernández (fmarti...@gmail.com) wrote:
> Thanks for information! I've refactor our migration scripts to follow the
> suggestions.
Please don't top-post on these mailing lists.
> One extra question that popped up. As long as we don't start the standby
> (after running rsync
Stephen,
Thanks for information! I've refactor our migration scripts to follow the
suggestions.
One extra question that popped up. As long as we don't start the standby (after
running rsync), we can always `rm -f $PGDATA_10` and promote the standby if
necessary for failover right ? We also ne
Thank you Stephen 👍
El mar., 19 de feb. de 2019 a la(s) 10:05, Stephen Frost (sfr...@snowman.net)
escribió:
> Greetings,
>
> * Hellmuth Vargas (hiv...@gmail.com) wrote:
> > But could you do the following procedure?:
>
> > pg_upgrade of the master
> > rysnc with a hot standby
>
> The above should
Greetings,
* Hellmuth Vargas (hiv...@gmail.com) wrote:
> But could you do the following procedure?:
> pg_upgrade of the master
> rysnc with a hot standby
The above should be alright provided both the primary and the standby
are down and the instructions in the pg_upgrade docs are followed.
> ar
Hi
But could you do the following procedure?:
pg_upgrade of the master
rysnc with a hot standby
arracar master
hot standby start
stop hot standby and rsync the other hot standby with the migrated hot
standby?
El mar., 19 de feb. de 2019 a la(s) 06:12, Stephen Frost (sfr...@snowman.net)
escribió:
On Mon, Feb 18, 2019 at 12:37 PM Andrew Gierth
wrote:
> > "Chuck" == Chuck Martin writes:
>
> Chuck> I am trying to create a query that returns all transactions for
> Chuck> each person who has a balance over a given amount. I thought
> Chuck> HAVING was the answer, but if so, I'm mis-usi
hi
I have PostgreSQL 11.1 installed on MacOS X Mojave and I used brew to do the
install. It appears that the extension plpython was not included in the
default installation and I would like to know how to install it.
Thanks…
Greetings,
* Martín Fernández (fmarti...@gmail.com) wrote:
> After reading the pg_upgrade documentation multiple times, it seems that
> after running pg_upgrade on the primary instance, we can't start it until we
> run rsync from the primary to the standby. I'm understanding this from the
> fol
I will not prefer to use a row trigger on this case for that will be
relatively inefficient.
So can we conclude that a sanity check is essential when using statement
level trigger.
Thanks and regards,
Jiten
Thanks for all your efforts. I appreciate it.
Let us wait and see if someone can enlighten us, or you locate the
conversation.
Thanks once again
Regards,
Jiten
On Tue 19 Feb, 2019, 3:19 AM Adrian Klaver,
wrote:
> On 2/18/19 9:07 AM, Jitendra Loyal wrote:
> > I do understand that the statement
I have gone through the documentation quite a number of times to establish
the understanding. However, I had been wondering about the recursion in the
case I put forth.
Is there a better way to handle this requirement? The point is that the
trigger is being called when no rows are affected.
Thank
I do understand that the statement level trigger will be executed once
before the operation. My point is.. if one does not know the rows, what
kind of use it can be put to. What is the use case? Like in after triggers,
one gets the rows in transition tables, how does one do with vefore trigger.
Th
My bad!
It is a transition table. Consider the following revised definition of
trigger:
CREATE TRIGGER storage_locations_b_u_AS_DML
AFTER UPDATE
ON storage_locations
REFERENCING NEW TABLE AS new_table
OLD TABLE AS old_table
FOR EACH STATEMENT EXECUTE FUNCTION TRG_storage_locations_b_u_AS_DML ();
Unfortunately no! Where can I see those? Will I don't my answer there; I
have referred to the documentation and tried various things.
Thanks and regards,
Ken i
On Mon 18 Feb, 2019, 9:59 PM Adrian Klaver,
wrote:
> On 2/18/19 8:23 AM, Jitendra Loyal wrote:
> > My bad!
> >
> > It is a transition t
Thanks Adrian
I am trying to understand as to how a BEFORE statement-level trigger can be
used. Since it is a trigger, one needs to know which rows are being
affected.
Regards,
Jiten
On Mon 18 Feb, 2019, 9:42 PM Adrian Klaver,
wrote:
> On 2/18/19 4:06 AM, Jitendra Loyal wrote:
> > The behaviou
34 matches
Mail list logo
