On 10/18/2017 08:17 PM, Don Seiler wrote:
> On Wed, Oct 18, 2017 at 1:08 PM, Vik Fearing
> <vik.fear...@2ndquadrant.com <mailto:vik.fear...@2ndquadrant.com>> wrote:
>
> On 10/18/2017 05:57 PM, Melvin Davidson wrote:
> >
> > I support the policy
ersions. They
> are often thought of as "bleeding edge" for the reason described by
> David G Johnston. The fact that PostgreSQL 10 was only released this
> month is critical and therefore is should not be a production server. It
> should be used as development, or QA, at best.
No, t
is required for Hibernate and some
other frameworks, you need to create a view with an INSTEAD OF trigger
that inserts into the table, which then get rerouted with your BEFORE
trigger. Then you insert into the view and get the desired result.
--
Vik Fearing
taken to prevent this ?
>
Prevent what? Even if the vacuum could run while the other transaction
had the exclusive lock, it wouldn't be able to do any work.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>
The planner has to choose whether to use an index for filtering or an
index for sorting. If you're always doing prefix searches like in your
two examples, then you want an index which can do both.
CREATE INDEX ON t (szzip text_pattern_ops, uorderid);
I invite you to read the documentation abou
each member of an array.
>
> OK, thanks.
>
> I was trying to avoid to actually change the input list, but apparently
> there is no other way.
>
If you don't want to touch the array, you can do something like this:
select *
from tablename as t
where exists (select fro
ent_timestamp, in_uid, in_gid, in_msg
WHERE length(trim(in_msg)) > 0 AND
EXISTS (SELECT 1 FROM words_games
WHERE gid = in_gid AND
in_uid in (player1, player2))
)
SELECT uid = in_uid, msg
FROM words_chat
WHERE gid = in_gid
ORDER BY created DESC;
> Is it maybe
No; use pg_trgm for this.
> Also a text column is using index when there is no wildcard character,but it
> is also not using if it is present at the end.
Did you declare your index with text_pattern_ops?
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadr
On 17/08/16 10:58, gilad905 wrote:
> Vik, note that your new suggestion for a query might be more
> readable/efficient than Jeff's, but IT STILL DOESN'T SOLVE THE PROBLEM OF
> DETECTING DUPLICATE ROWS.
Yes, it does.
And please don't shout at me.
--
Vi
ndby_feedback?
https://www.postgresql.org/docs/current/static/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailing list (pgsql-g
ze('now'::timestamptz);
pg_column_size | pg_column_size
+
8 | 8
(1 row)
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-genera
> I have proposed a reasonable solution to solve the problem in it's
> entirety. Do you have a better one?
You mean by partitioning? That doesn't really solve any problem, except
that vacfull-ing a partition should be faster than doing the whole
en
planner needs to examine the
CHECK constraints on the children and can't do it if the child is locked
in ACCESS EXCLUSIVE mode.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-gene
On 06/06/16 15:07, Vik Fearing wrote:
> It seems the commitfest link in there doesn't work anymore. I should
> probably bring that up in a separate thread.
It's in the old commitfest app. Here's a new link for it:
https://commitfest-old.postgresql.org/action/patch_view?id=1293
--
Vik F
On 06/06/16 14:50, Richard Tisch wrote:
> Hi there,
>
> I was just wondering about the statement below in another thread:
>
> 2016-06-04 22:58 GMT+09:00 Vik Fearing <v...@2ndquadrant.fr>:
>> There are plans to allow SQL
>> access to the parameters i
accept NULLs for the new
> field?
Yes, that makes a difference. If you add a column that defaults to
NULL, the table will not be rewritten.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent
On 06/06/16 09:54, Masahiko Sawada wrote:
> On Sat, Jun 4, 2016 at 10:58 PM, Vik Fearing <v...@2ndquadrant.fr> wrote:
>> On 02/06/16 15:32, Bertrand Paquet wrote:
>>> Hi,
>>>
>>> On an hot standby streaming server, is there any way to know, in
> Can I not wrap it around another user defined function with SECURITY DEFINER
>> and grant privilege to specific users who can use it?
Yes, as shown above.
> pg_ls_dir() has a check on superuser() embedded in its code.
So what? That's what SECURITY DEFINER is all about.
--
V
little ugly but works, is to create a
view over the parent table with an INSTEAD OF trigger and insert into
the view.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailin
imary_conninfo, but, it can be false.
>
> "The IP" assumes there is only one... hosts can be multihomed,
> postgres can be listening on numerous interfaces, there is no 'the IP'
That's nice, but a standby is only connecting to one.
--
Vik Fearing
hooks for connections and disconnections of the walreceiver,
so it should be possible and fairly simple to write an extension that
remembers and exposes the primary_conninfo in effect.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise,
t this slot to re-become active, you should drop it.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscr
tiny
> custom piece of code ?
That tiny custom piece of code would be this:
http://www.postgresql.org/docs/current/static/app-pg-isready.html
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
On 04/28/2016 08:48 PM, Israel Brewster wrote:
>>
>> On Apr 28, 2016, at 10:39 AM, Vik Fearing <v...@2ndquadrant.fr> wrote:
>>
>> What would be the point of this? Why not just one sequence for all
>> departments?
>
> continuity and appearance,
lookup table somewhere, although I guess I could implement something
> of the sort with triggers.
What would be the point of this? Why not just one sequence for all
departments?
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Exper
ves for
some very, very long reading about "extension templates".
This was (I think) the last thread about it:
www.postgresql.org/message-id/flat/m2bo5hfiqb....@2ndquadrant.fr
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL
Realistically, that can't happen every time. Think of temporary tables
> for example...
Hmm. How are you not the owner of a temporary table?
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
g c.duration but it's usually best to
only select the columns you need.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make cha
EADME.
https://github.com/2ndQuadrant/repmgr/commit/faed8a65f71d476a2a69ec871710dad3f099e439
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.
dom sampling of the data using
various methods.
You're looking for something more like this:
select t.*
from generate_series(1, (select max(id) from t), 100) g
join t on t.id = g;
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr Postgr
l.org/docs/current/static/tsm-system-time.html
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
f that sentence, but you are correct that
BRIN does not help at all with partition dropping. Think of it more as
a Seq Scan optimization.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via p
On 01/23/2016 10:28 AM, John R Pierce wrote:
> On 1/23/2016 12:35 AM, Vik Fearing wrote:
>>> >ok, but it doesn't deal with our use case of needing to bulk delete a 6
>> I can't really parse the end of that sentence, but you are correct that
>> BRIN does not help at
On 01/20/2016 11:41 AM, Nikhil wrote:
> Hello All,
>
>
> What is the timeline for BDR with postgres 9.5 released version.
Currently there are no plans for BDR with 9.5.
https://github.com/2ndQuadrant/bdr/issues/157#issuecomment-172402366
--
s might be relative to your interests:
INSERT INTO tbl (ts, tz)
VALUES ('2016-01-20 00:00', current_setting('TimeZone'));
This will do the right thing regardless of where the client is (unless
it's set to "localtime" and then it's useless).
--
Vik Fearing
ote_literal, quote_ident, ::regclass, || and USING.
> Unfortunately, I have not been able to get anything to work so any
> help would be very much appreciated.
Everything gets easier when you use format(). The following should do
what you want:
EXECUTE format('INSERT INTO bar (baz) VALUES (($1).%
On 01/19/2016 11:14 PM, Vik Fearing wrote:
> On 01/19/2016 11:05 PM, Peter Devoy wrote:
>> As part of the extension I am writing I am trying to create a trigger
>> procedure in which the value of the primary key of the NEW or OLD row
>> is used. The trigger will be fired b
following commit message:
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ca4af308c32d03db5fbacb54d6e583ceb904f268
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Sent via pg
On 08/12/2014 11:49 PM, Daniele Varrazzo wrote:
Hello,
I'm going to add support to the jsonb data type in psycopg2, in order
to have the type behaving like json currently does
(http://initd.org/psycopg/docs/extras.html#json-adaptation).
Is it correct that oid and arrayoid for the type
On 08/07/2014 01:22 PM, Gregory Taylor wrote:
I got this recommendation from someone else, and think that it's
probably the way to go. I've been playing with it unsuccessfully so far,
though. Most certainly because I've got something weirded up. Here's
what I have:
WITH RECURSIVE cte
On 08/01/2014 04:57 PM, Chris Travers wrote:
Hi all;
I had a pleasant surprise today when demonstrating a previous misfeature
in PostgreSQL behaved unexpectedly. In further investigation, there is
a really interesting syntax which is very helpful for some things I had
not known about.
On 08/01/2014 06:28 PM, Vik Fearing wrote:
So with all this in mind, is there any reason why we can't or shouldn't
allow:
CREATE testfunction(test) returns int language sql as $$ select 1; $$;
SELECT testfunction FROM test;
That would allow first-class calculated columns.
I
On 07/07/2014 11:28 AM, Andreas Joseph Krogh wrote:
Hi all.
I'm excited about 9.4's new JSONB and search-performance.
Is it possible to combine tsearch's prefix-search with the new JSONB-format?
Something like this (pseudo-code):
SELECT '{subject: visena}'::jsonb @ '{subject:
On 07/04/2014 06:12 AM, Sameer Kumar wrote:
NOT EXISTS translates to HASH ANTI JOIN and NOT IN translates to NOT
(HASHED) operation.
Given that the columns used in NOT IN clause (for outer as well as
inner) are NOT NULL, should not it translate a NOT IN plan similar to
NOT EXISTS plan?
It
On 06/24/2014 10:15 PM, CS_DBA wrote:
I added a PK constraint on the id column and created this json index:
create index mytest_json_col_idx on mytest ((task-'name'));
However the planner never uses the index...
EXPLAIN SELECT (mytest.task-'name') as name,
On 05/29/2014 09:25 AM, xbzhang wrote:
I want to implement the skip errors for copy from,lik as :
create table A (c int primary key);
copy A from stdin;
1
1
2
\.
copy will failed:
ERROR: duplicate key violates primary key constraint CC_PKEY
CONTEXT: COPY CC, line 2: 1
I want skip the
On 05/18/2014 05:47 PM, Tim Kane wrote:
Oh, I also noticed we don’t support alternate spellings of
MATERIALIZE, as we do for ANALYZE.
I’m not sure if we do this anywhere else, maybe it’s just analyze
being the odd one out.
For the moment, if not forever, ANALYSE is the odd one out.
On 04/30/2014 01:08 PM, David Noel wrote:
For 9.3, you can write that as:
select p.*, s.NoOfSentences
from page p,
lateral (select count(*) as NoOfSentences
from sentence s
where s.PageURL = p.URL) s
where Classification like case ... end
order by
On 04/29/2014 09:44 AM, David Noel wrote:
Ahh, sorry, copied the query over incorrectly. It should read as follows:
select page.*, coalesce((select COUNT(*) from sentence where
sentence.PageURL = page.URL group by page.URL), 0) as
NoOfSentences from page WHERE Classification LIKE CASE WHEN
On 04/13/2014 12:58 PM, Torsten Förtsch wrote:
Hi,
currently, ALTER TABLE VALIDATE CONSTRAINT for foreign key constraints
acquires an AccessExclusiveLock on the referencing table.
Why?
If the constraint is in place but not validated (ADD CONSTRAINT ... NOT
VALID) it already prevents new
On 04/11/2014 10:58 AM, Victor Sterpu wrote:
How would I write sutch a query?
SELECT to_timestamp ('10-10-2013 15:00', 'DD-MM- HH24:MI') +
interval REPLACE('1.30', '.', ':')||' hours'
This gives error at REPLACE.
Thank you.
The way you have interval, it expects a constant. You need to
On 04/09/2014 10:34 PM, Rob Richardson wrote:
I’ve get several processes running that use the same database. My
database log file is filled with these:
2014-04-09 14:16:45 EDT WARNING: invalid value for parameter
search_path: public, operationsplanning, cooling_stands
2014-04-09 14:16:45
On 03/19/2014 08:48 PM, François Beausoleil wrote:
Hi all!
Cross-posted from
https://dba.stackexchange.com/questions/61271/how-to-access-new-or-old-field-given-only-the-fields-name
I'm writing a validation trigger. The trigger must validate that the sum of
an array equals another field.
On 02/25/2014 04:41 AM, Zev Benjamin wrote:
I'm conceptually trying to do
ALTER TABLE foo ADD COLUMN bar boolean NOT NULL DEFAULT False;
without taking any noticeable downtime. I know I can divide the query
up like so:
ALTER TABLE foo ADD COLUMN bar boolean;
UPDATE foo SET bar = False; --
On 02/20/2014 10:29 AM, Daniel Cardno wrote:
How do I go about deleting the user?
I don't know, I don't use Windows.
--
Vik
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 02/20/2014 11:24 AM, Daniel Cardno wrote:
is this not a help line?
On 20 February 2014 09:31, Vik Fearing vik.fear...@dalibo.com
mailto:vik.fear...@dalibo.com wrote:
On 02/20/2014 10:29 AM, Daniel Cardno wrote:
How do I go about deleting the user?
I don't know, I don't
On 02/19/2014 08:01 PM, Daniel Cardno wrote:
Hi,
I have recently changed from HEM2 to PT4, during the changeover i
uninstalled Postgres and HM2, i then went on to install PT4 and
postgres 8.4.
When i know run PT4 and try and install Postgres from there it shows
me the top two below images.
On 02/15/2014 12:22 PM, Clemens Eisserer wrote:
Hi Andreas,
They will lost after a crash, but after a regular shutdown / restart all
data in the table.
Yes, the semantics are clearly stated in the documentation.
What I wonder is whether postgresql will issue flush/fsync operations
when
On 02/11/2014 11:56 PM, Bruce Momjian wrote:
Someone suggested that 'asciidoc'
(http://en.wikipedia.org/wiki/AsciiDoc) would be a good output format
for psql, similar to the existing output formats of html, latex, and
troff.
Would this be useful?
Perhaps, but if we're going to add a text
On 02/06/2014 04:16 AM, Michael Sacket wrote:
Often times I find it necessary to work with table rows in a specific,
generally user-supplied order. It could be anything really that
requires an ordering that can't come from a natural column. Most of
the time this involved manipulating a
On 02/06/2014 06:51 AM, Torsten Förtsch wrote:
On 06/02/14 06:46, Torsten Förtsch wrote:
we decreased wal_keep_segments quite a lot. What is the supposed way to
get rid of the now superfluous files in pg_xlog?
Nothing special. The database did it for me.
It cleans up after a checkpoint. If
On 02/06/2014 10:00 AM, Rémi Cura wrote:
Hey,
I dont understand the difference between this ORDINALITY option and
adding a row_number() over() in the SELECT.
WITH ORDINALITY will give you something to order by. You should never
do row_number() over () because that will give you potentially
On 02/01/2014 02:26 AM, Bruce Momjian wrote:
On Sat, Feb 1, 2014 at 02:25:16AM +0100, Vik Fearing wrote:
OK, thanks for the feedback. I understand now. The contents of the
string will potentially have a larger integer, but the byte length of
the string in the wire protocol doesn't change
On 01/31/2014 06:19 PM, Bruce Momjian wrote:
On Wed, Jul 24, 2013 at 08:08:32PM +0200, Andres Freund wrote:
On 2013-07-24 13:48:23 -0400, Tom Lane wrote:
Vik Fearing vik.fear...@dalibo.com writes:
Also worth mentioning is bug #7766.
http://www.postgresql.org/message-id/e1tlli5-0007tr
On 01/31/2014 10:56 PM, Bruce Momjian wrote:
On Fri, Jan 31, 2014 at 04:38:21PM -0500, Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
On Fri, Jan 31, 2014 at 06:34:27PM +0100, Vik Fearing wrote:
Unfortunately, I gave up on it as being over my head when I noticed I
was changing
On 11/26/2013 06:24 PM, David Rysdam wrote:
I'm not really looking for information on how to speed this query
up. I'm just trying to interpret the output enough to tell me which step
is slow:
Seq Scan on mags (cost=0.00..187700750.56 rows=47476 width=4) (actual
On 11/27/2013 04:56 PM, David Rysdam wrote:
I've got two tables, sigs and mags. It's a one-to-one relationship, mags
is just split out because we store a big, less-often-used field
there. signum is the key field.
Sometimes I want to know if I have any orphans in mags, so I do a query
like
On 11/23/2013 07:41 AM, Ken Tanzer wrote:
OTOH, if there were a very clear and credible page with good
instructions on installing build environment + postgres (for say RHEL,
Ubuntu Fedora) that would install side by side with an existing
installation (and how to remove it all cleanly) it
On 08/18/2013 05:56 AM, Robert James wrote:
I have a slow_function. My table has field f, and since slow_function
is slow, I need to denormalize and store slow_function(f) as a field.
What's the best way to do this automatically? Can this be done with
triggers? (On UPDATE or INSERT, SET
On 08/15/2013 10:16 PM, Robert James wrote:
How can I escape a string for LIKE operations?
I want to do:
SELECT * FROM t WHERE a LIKE b || '%'
But I want be to interpreted literally. If b is 'The 7% Solution', I
don't want that '%' to be wildcard. I can't find an appropriate
function to
On 08/02/2013 10:03 AM, BladeOfLight16 wrote:
So my question is effectively this: Is there an existing, equivalent,
single DDL statement to the following hypothetical SQL?
ALTER TABLE x
ADD COLUMN data2 VARCHAR(10) NOT NULL USING 'foo';
where USING here would indicate the same thing it does
On 07/22/2013 06:20 PM, Jeff Janes wrote:
On Fri, Jul 19, 2013 at 3:20 PM, Natalie Wenz nataliew...@ebureau.com wrote:
Hi all,
I am moving some data from one table to another in 9.2.4, and keep seeing
this strange scenario:
insert into newtable select data from oldtable where proc_date = x
On 07/24/2013 04:04 PM, Vik Fearing wrote:
On 07/22/2013 06:20 PM, Jeff Janes wrote:
On Fri, Jul 19, 2013 at 3:20 PM, Natalie Wenz nataliew...@ebureau.com
wrote:
Hi all,
I am moving some data from one table to another in 9.2.4, and keep seeing
this strange scenario:
insert into newtable
On 06/21/2013 01:07 AM, Jeff Janes wrote:
On Thu, Jun 20, 2013 at 3:18 PM, Jason Long
mailing.li...@octgsoftware.com
mailto:mailing.li...@octgsoftware.com wrote:
Can someone suggest the easiest way to compare the results from two
queries to make sure they are identical?
I am
Whoops, forgot to keep it on the list.
On 06/11/2013 11:51 AM, Alexander Farber wrote:
Hello!
In a PostgreSQL 8.4.13 why doesn't this please
deliver a floating value (a quotient between 0 and 1):
You should upgrade to 8.4.17.
select
id,
count(nullif(nice, false)) -
On 04/23/2013 12:29 AM, John R Pierce wrote:
On 4/22/2013 3:13 PM, Thomas Kellerer wrote:
Abhinav Dwivedi wrote on 22.04.2013 07:12:
select * from district where statecode in (Select districtcode from
state)
Please note that the attribute districtcode is not existent in the
table state and
76 matches
Mail list logo
