Just had an issue where a prepared query would occasionally choose a
very bad plan in production. The same data set in a different
environment consistently would choose the index scan. As would be
expected, running analyze on that table in production resolved the issue.
However, before I ran
On 02/11/2017 11:36 AM, Adrian Klaver wrote:
On 02/11/2017 09:17 AM, Alexander Farber wrote:
I think ORDER BY RANDOM() has stopped working in 9.6.2:
words=> select unnest(ARRAY['a','b','c','d','e','f']) order by random();
unnest
a
b
c
d
e
f
(6 rows)
postgres=> select
On 12/14/2015 11:55 AM, Benjamin Smith wrote:
Is there a way to set PG field-level read permissions so that a deny doesn't
cause the query to bomb, but the fields for which permission is denied to be
nullified?
In our web-based app, we have a request to implement granular permissions:
jack=# select '1.51 years'::interval = '1.52 years'::interval;
?column?
--
t
(1 row)
This is surprising. Once I looked at the C code for Interval it makes
more sense given that it cannot represent fractional years, months, or
days. Wouldn't it make more sense to raise an invalid
I was recently surprised by changes that were not logged by
log_statement = 'mod'. After changing log_statement to 'all', I found
that the changes were occurring in a writable CTE.
Is there a way to log all statements that update data?
Jack
--
Sent via pgsql-general mailing list
On 05/09/2015 06:33 AM, Stephen Frost wrote:
Temporary tables will be in memory unless they overflow work_mem and
we do support unlogged tables and tablespaces which you could stick
out on a ramdisk if you want.
I would suggest not putting a table space on a ramdisk. According to the
docs this
With PostgreSQL 9.3 I installed plv8 from apt.postgresql.org
(http://www.postgresql.org/download/linux/ubuntu/). It doesn't appear
that it is available for 9.4. Is this no longer offered or has it just
not available yet?
Thanks.
Jack
--
Sent via pgsql-general mailing list
Have you tried putting those components in a common table expression?
I'm not sure if it absolutely forces the materialization or not, but in
practice that has been my experience.
Robert James wrote:
I have a query which, when I materialize by hand some of its
components, runs 10x faster
(
select name, player_id as renamed
from player
order by name
) t;
row_to_json
-
{name:Jack,renamed:1}
(1 row)
But here it didn't.
Is this a bug?
Jack Christensen
Joe Van Dyk wrote:
Perhaps I fat-fingered something somewhere... I tried that and I got
this:
https://gist.github.com/joevandyk/4958906/raw/5561f95ef2b5d82f81ab14913c4d36f6aac3ee0a/gistfile1.txt
The with_filters view uses a different plan.
Interesting. It is avoiding the hash join, but it
Joe Van Dyk wrote:
See
https://gist.github.com/joevandyk/4957646/raw/86d55472ff8b5a4a6740d9c673d18a7005738467/gistfile1.txt
for the code.
I have promotions(id, end_at, quantity) and
promotion_usages(promotion_id).
I have a couple of things I typically want to retrieve, and I'd like
those
, field_b
from ...
order by grouping_field, field_a asc, field_b asc
http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-DISTINCT
--
Jack Christensen
http://jackchristensen.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
your
applications into their own schemas, and you can have cross-schema
foreign keys.
--
Jack Christensen
http://jackchristensen.com/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
c4 = 2 );
rollback;
Mike
Your subquery is correlated with the outer query. So the c2 in the
subquery is referring to table x.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
importance to
developers. A 30 second difference 100's of times per day really can add
up.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
* Apologies if anyone receives this twice. I previously sent it from
another address and it did not appear to go through.
Can someone explain how set returning functions in a select clause work?
It seems that it is doing some sort of implicit cross join.
deliverance_development=# select id,
* Apologies if anyone receives this multiple times. I previously sent it
with a subject that started with Set and it triggered some sort of
admin filter.
Can someone explain how set returning functions in a select clause work?
It seems that it is doing some sort of implicit cross join.
Google
and the PostgreSQL docs, but it appears either I do not know the key
words to search for or it is sparsely documented.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
query, and when I tried to use a non-grouped
column from the outer query I correctly got a ERROR: subquery uses
ungrouped column foo from outer query
Thanks again.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes
.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 10/8/2011 1:21 AM, Craig Ringer wrote:
On 10/08/2011 02:23 AM, Jack Christensen wrote:
Just upgraded a machine from PostgreSQL 9.0 to 9.1. I uninstalled the
old version then installed the new one.
Whenever I try to run a service command to start, stop, or restart the
server it fails.
jackc
* Error: Could not open /proc/2193/comm
[fail]
It seems to be happening in /usr/share/postgresql-common/PgCommon.pm:542
PostgreSQL is actually running fine, but the only way I can make any
changes is to reboot the server (or kill all the postgres processes I
suppose).
--
Jack Christensen
ja
and some copied values?
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
items,
start_time timestamptz NOT NULL,
end_time timestamptz
...
);
CREATE UNIQUE INDEX ON loans(item_id) WHERE end_time IS NULL;
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
-for-8-5-exclusion-constraints/
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
won't.
Thanks everyone for your advice. I think this type of approach will be
very helpful.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
. Validate application side -- this can work well, but it leaves the
hole of a bug in the application or a direct SQL statement going bad.
Anyone have any advice on the best way to handle this?
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general
On 5/5/2011 2:28 PM, Rick Genter wrote:
On Thu, May 5, 2011 at 3:20 PM, Jack Christensen
ja...@hylesanderson.edu mailto:ja...@hylesanderson.edu wrote:
What is the best way to handle multiple table relationships where
attributes of the tables at the ends of the chain must match
On 5/5/2011 2:53 PM, Rick Genter wrote:
On Thu, May 5, 2011 at 3:50 PM, Jack Christensen
ja...@hylesanderson.edu mailto:ja...@hylesanderson.edu wrote:
The trick is there are additional attributes of actions and
achievements such as a category that must match for the link
occurred since the error won't be raised until commit.
Are there any other downsides to just setting all my foreign keys to
initially deferred?
Thanks.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
On 12/15/2010 5:43 PM, Adrian Klaver wrote:
On Wednesday 15 December 2010 1:27:19 pm David Fetter wrote:
On Wed, Dec 15, 2010 at 01:50:54PM -0600, Jack Christensen wrote:
I was just surprised when accidentally selecting a non-existent name
column there was no error -- instead something came
rows get truncated).
I've searched Google and the PG docs but I haven't had any luck.
--
Jack Christensen
ja...@hylesanderson.edu
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
32 matches
Mail list logo