I'm trying to identify which postgresql.conf file I should be editing, in order
to change the default database files location for Postgres 9.6.6, when
installed on CentOS 7.x/
Is the bet method for changing the default data directory at the time of
database init, to include the $PGDATA
Guys,
New to Postgres - here's my code inside an event trigger:
ELSIF (TG_OP = 'INSERT') THEN
EXECUTE format('INSERT INTO %I SELECT statement_timestamp(),
''INSERT'', $1.*', TG_TABLE_NAME || '_cdc')
USING NEW;
RETURN NEW;
Here's the error I am receiving - when I am
I am new to Postgres and I am trying to build this SQL statement in my SQL
script:
ALTER TABLE listings_cdc ALTER COLUMN table_id SET DEFAULT
nextval('tab_id_seq');
I am trying to build the above-stated command as a dynamic SQL statement:
EXECUTE 'ALTER TABLE listings_cdc ALTER COLUMN table_id
f live queries, but (I think -- I'll have to check!)
that is all
>
> Cheers,
>
> Jeff
>
Robert.
--
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd, 3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no.
_c is timestamped less than 10 minutes after the dump of database_a
Which does not fit with the dump of database_b being given 10 minutes in
which to finish
Have I misunderstood something? Or is Postgres not actually configured the
way I think it is?
Robert.
--
Robert Inder,
Ha guys,
I am new to postgress and I am trying to write my first function to insert,
update or delete and trap errors as a result of the table not existing ,
the columns not exist or if any other error simply pass back the sqlstate
here's my code can you help
CREATE OR REPLACE FUNCTION
All the code for creating and managing partitions is part of the core
Postgres code. What we are interested in looking into is what that work
flow might look like and how that workflow can be supported with a GUI
management tool.
-- Rob
On Thu, May 18, 2017 at 3:21 PM, Melvin Davidson
On Fri, Mar 31, 2017 at 11:29 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Robert Haas <robertmh...@gmail.com> writes:
>> On Thu, Mar 30, 2017 at 4:45 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>>> In short, it seems like this statement in the docs is correctl
e back branches
> should also be changed.
Sounds reasonable, but I don't see much advantage to changing it in
the back-branches.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.or
Thanks, everyone, for your comments.
I think I've got a clearer idea of what's going on now...
Robert.
On 1 December 2016 at 13:55, Robert Inder <rob...@interactive.co.uk> wrote:
> I'm running Postgres9.4 in master/hot-standby mode on a few pairs of servers.
>
> While recove
o I'd looked
in "the obvious places" -- Section 18 (Server configuration), and in
particular 18.2 "File Locations". Could I suggest that the motivation
for doing this, and the consequences for backups, should be discussed
in "the right place" -- in or near the section
I was wondering if anyone might be able to help me out with a table design
question.
A quick intro -- I'm helping a company switch from a mongo database over to
postgresql (yay!). The company is a marketplace app for musicians and
hosts. The basic idea is that a host can book a musician for an
Hello,
If you have jdk 1.8 or above installed go to www.executequery.org and
download the latest jar file. Download the JDBC driver from Postgres and
set it up. It's open source.
It has an ERD generator but obviously depends on having all your foreign
keys declared in order to link tables, etc.
rom the extension afterwards.
Stephen, is this still on your list of things for today? Please
provide a status update.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ects it contain will not be
> dumpable as well.
>
> That's the reason why the PgQ event tables created by
> pgq.create_queue() are not dumped.
That sucks.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-gene
On Mon, 2016-06-20 at 11:43 +0200, Job wrote:
> Hi Andreas,
>
> >I would suggest run only autovacuum, and with time you will see a not
> >more growing table. There is no need for vacuum full.
>
> So new record, when will be pg_bulkloaded, will replace "marked-free"
> location?
Yes, but you
Note that
> I am not suggesting that ordering quals in query by their perceived cost
> is the solution. Keep optimizer informed by setting costs appropriately
> and it will do the right thing more often than not. :)
I think that if the costs are actually identical, the system will
taking?
backend_start| 2016-05-07 11:48:39.218398-03
More than 50 hours.
What is your maintenance_work_mem set to?
maintenance_work_mem = 352MB
2016-05-09 14:34 GMT-03:00 Joshua D. Drake <j...@commandprompt.com>:
> On 05/09/2016 10:32 AM, Robert Anderson wrote:
>
>
| CREATE INDEX CONCURRENTLY index_texto
| ON flip_pagina_edicao
| USING hash
| (texto COLLATE pg_catalog."default");
postgres=#
2016-05-09 14:20 GMT-03:00 Tom Lane <t...@sss.pgh.pa.us>:
> Robert Anderson
>JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
>WHERE w.waiting;
>
>
> On Mon, May 9, 2016 at 11:50 AM, Joshua D. Drake <j...@commandprompt.com>
> wrote:
>
>> On 05/09/2016 05:04 AM, Robert Anderson wrote:
>>
>>> Hi,
>>>
>&g
-1, 0}}, 1) = 0
...
Thanks in advance.
Robert
, you could still
>> accommodate that:
>
> Actually, only if it's a variable that you setup and repeat and you show. A
> bit cumbersome and mixes the parts that are title and those that are present
> only because you are watching.
Ah, come on. This doesn't really seem like
king at it to be nice to the people
who do. Whatever is the consensus is OK with me. I just don't want
to get yelled at later for committing something here, so it would be
nice to see a few votes for whatever we're gonna do here.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgre
tter yet, include the + 50 in title_len, and then you don't need to
reference the number 50 again further down.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
return false;
+}
Instead of repeating the cleanup code, how about making this break;
then, change the return statement at the bottom of the function to
return (res != -1).
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pg
mary
> AND NOT idx.indisunique
> ORDER BY 1, 2, 3;
>
> Then drop any index that shows up!
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
--
Robert McAlpine
DevOps Engineer
Perfecta Federal <http://www.perfectafederal.com/>
6506 Loisdale Road
Springfield, VA 22150
O: 202.888.4949 ext 1005
C: 757.620.3503
r...@pfcta.com
10, 2016 at 7:44 PM, Adrian Klaver <adrian.kla...@aklaver.com>
wrote:
> On 03/10/2016 04:11 PM, Robert McAlpine wrote:
>
>>
>> Postgresql 9.5, Ubuntu 14.04.
>>
>> I broke my ability to access postgres after attemping to install
>> postgres-xc (ironic, sinc
a
correct default postgres role, but my gut tells me that screwing around
with those files is doomed to fail.
I would appreciate any help or thoughts on how to recover access to the
data.
--
Robert McAlpine
r...@pfcta.com
Hi,
I’m reading about the ranking functions [1], and I have a couple questions…
1. Is ts_rank taking proximity of terms into account? It seems like it is, but
the docs suggest that only ts_rank_cd does that.
2. Is there a way to search multiple terms like ‘a | b | c …’ but score higher
when
I have a table something like this:
CREATE TABLE devices (
owner_idBIGINT NOT NULL,
utc_offset_secs INT,
PRIMARY KEY (uid, platform),
FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);
I want to do a query from an application that returns all devices who's
time is
On Fri, Jul 10, 2015 at 9:40 AM, John McKown john.archie.mck...@gmail.com
wrote:
On Fri, Jul 10, 2015 at 11:15 AM, Robert DiFalco robert.difa...@gmail.com
wrote:
I have a table something like this:
CREATE TABLE devices (
owner_idBIGINT NOT NULL,
utc_offset_secs INT
I am fairly certain this does not give you the correct results.
Specifically, the minimum value for each cDate is going to be 1 since
count(*) counts NULLs. count(u) should probably work.
Yes you are right, I forgot to change COUNT(*) to COUNT(id), as you
mention COUNT(u.*) will also
Thanks Paul, I guess I'm not sure how a generate_series between 0 to 6
would solve this problem. Wouldn't I have to generate a series based on the
date range (by day) and then group by DOW _after_ that? Can you give me an
example of how I'd do it with a series based on 0 to 6?
On Mon, Jul 6, 2015
I'm not sure how to create a result where I get the average number of new
users per day of the week. My issues are that days that did not have any
new users will not be factored into the average, giving an overinflated
result.
This is what I started with:
WITH userdays AS
(SELECT
Paul, I'm sure I'm missing something but it seems like your approach will
not work. It's because the LEFT OUTER JOIN is on the numeric day of the
week. So if you had this query going over weeks or months of data wouldn't
you have the same issue with the days that had no new users not being
Hi,
(Maybe my subject line should be: `is not distinct from` and indexes.)
In Postgres 9.4, I’ve got a table of ‘items’ that references a table ‘colors’.
Not all items have colors, so I created a nullable column in items like:
color_id bigint references colors
There is also an index
oldest checkpointed MultiXact 1 does not
exist on disk
**Thank you Robert and all involved for the resolution to this.**
With the fixes introduced in this release, such a situation will result in
immediate emergency autovacuuming until a correct oldestMultiXid value can
be determined
Okay, I
I want to make sure I understand the repercussions of this before making it
a global setting.
As far as I can tell this will put data/referential integrity at risk. It
only means that there is a period of time (maybe 600 msecs) between when a
commit occurs and when that data is safe in the case
, but which is really a different proposal
altogether.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch n...@leadboat.com wrote:
On Thu, Jun 04, 2015 at 05:29:51PM -0400, Robert Haas wrote:
Here's a new version with some more fixes and improvements:
I read through this version and found nothing to change. I encourage other
hackers to study the patch
On Fri, Jun 5, 2015 at 12:00 PM, Andres Freund and...@anarazel.de wrote:
On 2015-06-05 11:43:45 -0400, Tom Lane wrote:
Robert Haas robertmh...@gmail.com writes:
On Fri, Jun 5, 2015 at 2:20 AM, Noah Misch n...@leadboat.com wrote:
I read through this version and found nothing to change. I
On Fri, Jun 5, 2015 at 2:36 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote:
Tom Lane wrote:
Robert Haas robertmh...@gmail.com writes:
There are at least two other known issues that seem like they should
be fixed before we release:
1. The problem that we might truncate an SLRU members
On Fri, Jun 5, 2015 at 2:47 PM, Andres Freund and...@anarazel.de wrote:
On 2015-06-05 14:33:12 -0400, Tom Lane wrote:
Robert Haas robertmh...@gmail.com writes:
1. The problem that we might truncate an SLRU members page away when
it's in the buffers, but not drop it from the buffers, leading
worse, life will suck.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Jun 4, 2015 at 5:29 PM, Robert Haas robertmh...@gmail.com wrote:
- Forces aggressive autovacuuming when the control file's
oldestMultiXid doesn't point to a valid MultiXact and enables member
wraparound at the next checkpoint following the correction of that
problem.
Err, enables
On Thu, Jun 4, 2015 at 12:57 PM, Robert Haas robertmh...@gmail.com wrote:
On Thu, Jun 4, 2015 at 9:42 AM, Robert Haas robertmh...@gmail.com wrote:
Thanks for the review.
Here's a new version. I've fixed the things Alvaro and Noah noted,
and some compiler warnings about set but unused
On Thu, Jun 4, 2015 at 1:27 PM, Andres Freund and...@anarazel.de wrote:
On 2015-06-04 12:57:42 -0400, Robert Haas wrote:
+ /*
+ * Do we need an emergency autovacuum? If we're not sure, assume yes.
+ */
+ return !oldestOffsetKnown ||
+ (nextOffset - oldestOffset
On Thu, Jun 4, 2015 at 9:42 AM, Robert Haas robertmh...@gmail.com wrote:
Thanks for the review.
Here's a new version. I've fixed the things Alvaro and Noah noted,
and some compiler warnings about set but unused variables.
I also tested it, and it doesn't quite work as hoped. If started
);
+ MultiXactState-offsetStopLimit = oldestOffset;
That last line needs s/oldestOffset/offsetStopLimit/, I presume.
Another oops.
Thanks for the review.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general
On Wed, Jun 3, 2015 at 8:24 AM, Robert Haas robertmh...@gmail.com wrote:
On Tue, Jun 2, 2015 at 5:22 PM, Andres Freund and...@anarazel.de wrote:
Hm. If GetOldestMultiXactOnDisk() gets the starting point by scanning
the disk it'll always get one at a segment boundary, right? I'm not sure
in the attached
patch.
Actually, we still need to call DetermineSafeOldestOffset in that
case. Otherwise, if someone goes from lots of multixacts to none, the
stop point won't advance.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general
initialize
nextOffset to 0. That ought to safe us?
That's pretty much betting the farm on the bugs we know about today
being the only ones there are. That seems imprudent.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing
conservative? There's no
safe value in a circular numbering space.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref
On Tue, Jun 2, 2015 at 1:21 AM, Noah Misch n...@leadboat.com wrote:
On Mon, Jun 01, 2015 at 02:06:05PM -0400, Robert Haas wrote:
On Mon, Jun 1, 2015 at 12:46 AM, Noah Misch n...@leadboat.com wrote:
On Fri, May 29, 2015 at 03:08:11PM -0400, Robert Haas wrote:
SetMultiXactIdLimit() bracketed
with
multixacts that are completely filled by the time we've reached
consistency.
That would be a departure from the behavior of every existing release
that includes this code based on, to my knowledge, zero trouble
reports.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise
On Tue, Jun 2, 2015 at 11:27 AM, Andres Freund and...@anarazel.de wrote:
On 2015-06-02 11:16:22 -0400, Robert Haas wrote:
I'm having trouble figuring out what to do about this. I mean, the
essential principle of this patch is that if we can't count on
relminmxid, datminmxid, or the control
.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Jun 2, 2015 at 11:44 AM, Andres Freund and...@anarazel.de wrote:
On 2015-06-02 11:37:02 -0400, Robert Haas wrote:
The exact circumstances under which we're willing to replace a
relminmxid with a newly-computed one that differs are not altogether
clear to me, but there's an if statement
On Mon, Jun 1, 2015 at 12:46 AM, Noah Misch n...@leadboat.com wrote:
Incomplete review, done in a relative rush:
Thanks.
On Fri, May 29, 2015 at 03:08:11PM -0400, Robert Haas wrote:
OK, here's a patch. Actually two patches, differing only in
whitespace, for 9.3 and for master (ha!). I now
to this one.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
commit d33b4eb0167f465edb00bd6c0e1bcaa67dd69fe9
Author: Robert Haas rhaas@postgresql.org
Date: Fri May 29 14:35:53 2015 -0400
foo
diff --git a/src/backend/access/transam/multixact.c b/src
what needs to be removed. It would be
far better to invert that logic: decide what needs to be removed -
presumably, everything from the oldest member that now exists up until
some later point - and then remove precisely that stuff and nothing
else.
--
Robert Haas
EnterpriseDB: http
On Fri, May 29, 2015 at 10:17 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Thomas Munro thomas.mu...@enterprisedb.com writes:
On Fri, May 29, 2015 at 11:24 AM, Robert Haas robertmh...@gmail.com wrote:
B. We need to change find_multixact_start() to fail softly.
Here is an experimental WIP patch
On Fri, May 29, 2015 at 3:08 PM, Robert Haas robertmh...@gmail.com wrote:
It won't fix the fact that pg_upgrade is putting
a wrong value into everybody's datminmxid field, which should really
be addressed too, but I've been working on this for about three days
virtually non-stop and I don't
On Fri, May 29, 2015 at 9:46 PM, Andres Freund and...@anarazel.de wrote:
On 2015-05-29 15:08:11 -0400, Robert Haas wrote:
It seems pretty clear that we can't effectively determine anything
about member wraparound until the cluster is consistent.
I wonder if this doesn't actually hints
On Fri, May 29, 2015 at 12:43 PM, Robert Haas robertmh...@gmail.com wrote:
Working on that now.
OK, here's a patch. Actually two patches, differing only in
whitespace, for 9.3 and for master (ha!). I now think that the root
of the problem here is that DetermineSafeOldestOffset
On Thu, May 28, 2015 at 8:51 AM, Robert Haas robertmh...@gmail.com wrote:
[ speculation ]
OK, I finally managed to reproduce this, after some off-list help from
Steve Kehlet (the reporter), Alvaro, and Thomas Munro. Here's how to
do it:
1. Install any pre-9.3 version of the server and generate
On Thu, May 28, 2015 at 4:06 PM, Joshua D. Drake j...@commandprompt.com wrote:
FTR: Robert, you have been a Samurai on this issue. Our many thanks.
Thanks! I really appreciate the kind words.
So, in thinking through this situation further, it seems to me that
the situation is pretty dire:
1
to the previous discussion?
I mean, the problem we're having right now is that sometimes we have
an offset, but the corresponding member isn't there. So clearly
offsets reference members. Do members also reference offsets? I
didn't think so, but life is full of surprises.
--
Robert Haas
?
Did you by any chance perform an immediate shutdown? Do you have the
actual log messages that were written when the system was shut down
for the upgrade?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql
On Thu, May 28, 2015 at 8:03 AM, Robert Haas robertmh...@gmail.com wrote:
Steve, is there any chance we can get your pg_controldata output and a
list of all the files in pg_clog?
Err, make that pg_multixact/members, which I assume is at issue here.
You didn't show us the DETAIL line from
was interrupted or some such.
There may be bugs in redo, also, but they don't explain what happened to Steve.
Steve, is there any chance we can get your pg_controldata output and a
list of all the files in pg_clog?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, May 28, 2015 at 8:01 AM, Robert Haas robertmh...@gmail.com wrote:
On Wed, May 27, 2015 at 6:21 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Steve Kehlet wrote:
I have a database that was upgraded from 9.4.1 to 9.4.2 (no pg_upgrade, we
just dropped new binaries in place
a valid oldestOffset? If so, until the next
checkpoint happens, autovacuum has no clue whether it needs to worry.
There's got to be a fix for that, but it escapes me at the moment.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql
() to TrimMultiXact() seems like
a good idea. I'm not sure why we didn't do that before.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http
there is something I'm missing or not understanding, can
anyone help? Thanks!
--
Robert
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
When I save a VIEW, Postgres seems to convert it to a different
format, functionally equivalent but unrecognizable (whitespace,
comments, adds lots of casts, etc.)
Is there any simple way to preserve my original code?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To
How do I calculate the sum of a field filtered by multiple windows
defined by another field?
I have table event with fields event_date, num_events, site_id. I can
easily use aggregate SQL to do SELECT SUM(num_events) GROUP BY
site_id.
But I also have another table site with fields site_id,
Version 9.2.4
On 3/15/15, David G. Johnston david.g.johns...@gmail.com wrote:
On Sunday, March 15, 2015, Robert James srobertja...@gmail.com wrote:
How do I calculate the sum of a field filtered by multiple windows
defined by another field?
I have table event with fields event_date
of
making the dump to a ram file system, then filing it as a separate
step, looks simple enough to be worth trying as a stop-gap...
Robert.
--
Robert Inder,
Interactive Information Ltd, 3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
. But, I
realise, that's not a major concern: the delay would only be seen by a
client that
had had a major problem. Everyone else would see service as normal.
I think I'll be doing some experiments to find out:-)
Robert.
--
Robert Inder,
Interactive Information Ltd, 3, Lauriston Gardens
that has changed with Postgres 9?
We're currently running Postgres 8.4.
Is this my specific reason to embark on an upgrade?
Robert.
--
Robert Inder,
Interactive Information Ltd, 3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
the others?
Thanks in advance.
Robert.
--
Robert Inder,
Interactive Information Ltd, 3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
Interactions speak louder than words
--
Sent via pgsql-general mailing list (pgsql
Hometowns get selected and possibly inserted in unpredictable ways even
from multiple concurrent sessions. The only way I could figure out how to
solve it was to force each INSERT hometowns to be in its own transaction.
On Mon, Jan 19, 2015 at 1:56 PM, Robert DiFalco robert.difa...@gmail.com
I don't think an advisory lock would remove the deadlock.
On Sun, Jan 18, 2015 at 10:33 PM, Roxanne Reid-Bennett r...@tara-lu.com
wrote:
On 1/16/2015 2:41 AM, Jim Nasby wrote:
On 1/15/15 10:57 PM, Roxanne Reid-Bennett wrote:
try this: (if you still get deadlocks, uncomment the advisory
understanding is off.
On Mon, Jan 19, 2015 at 11:10 AM, Andreas Kretschmer
akretsch...@spamfence.net wrote:
Robert DiFalco robert.difa...@gmail.com wrote:
I have several tables that I use for logging and real-time stats. These
are not
critical and since they are a bottleneck I want transactions
I have several tables that I use for logging and real-time stats. These are
not critical and since they are a bottleneck I want transactions against
them to always be asynchronous. Is there a way to specify this at a table
level or do I have to make sure to call set synchronous_commit='off' every
The code shown in the Doc (I think) will still give you deadlock in the
case where you have two sessions concurrently trying to insert the same
'hometown'. For example:
INSERT INTO users VALUES('Tom', select_hometown_id('Portland, OR'));
INSERT INTO users VALUES(''Waits',
be picked
up. And there should only be a quick recoverable deadlock.
On Fri, Jan 16, 2015 at 7:49 AM, Daniel Verite dan...@manitou-mail.org
wrote:
Robert DiFalco wrote:
I must be doing something wrong because both of these approaches are
giving
me deadlock exceptions.
Deadlocks
into hometowns (name)
select hometown_name where v_id is null
returning id into v_id;
EXCEPTION WHEN unique_violation
THEN
select id into v_id from hometowns where name = hometown_name;
END;
insert into users (name, hometown_id)
values ('Robert', v_id);
END;
On Tue
hometown_name where v_id is null
returning id into v_id;
EXCEPTION WHEN unique_violation
THEN
select id into v_id from hometowns where name = hometown_name;
END;
insert into users (name, hometown_id)
values ('Robert', v_id);
END;
On Tue, Jan 13, 2015 at 5:06 PM, Robert
(SELECT 1 FROM sel)
RETURNING id
)
INSERT INTO users(name, hometown_id)
VALUES ('Robert', SELECT id FROM ins UNION ALL SELECT id FROM sel);
On Tue, Jan 13, 2015 at 8:50 AM, John McKown john.archie.mck...@gmail.com
wrote:
On Tue, Jan 13, 2015 at 10:42 AM, Robert DiFalco robert.difa
Let's say I have two tables like this (I'm leaving stuff out for
simplicity):
CREATE SEQUENCE HOMETOWN_SEQ_GEN START 1 INCREMENT 1;
CREATE TABLE hometowns (
id INTEGER DEFAULT nextval('HOMETOWN_SEQ_GEN'),
name VARCHAR,
PRIMARY KEY (id),
UNIQUE(name)
);
CREATE SEQUENCE USER_SEQ_GEN START
This CTE approach doesn't appear to play well with multiple concurrent
transactions/connections.
On Tue, Jan 13, 2015 at 10:05 AM, John McKown john.archie.mck...@gmail.com
wrote:
On Tue, Jan 13, 2015 at 11:45 AM, Robert DiFalco robert.difa...@gmail.com
wrote:
Thanks John. I've been seeing
Well, traditionally I would create a LOOP where I tried the SELECT, if
there was nothing I did the INSERT, if that raised an exception I would
repeat the LOOP.
What's the best way to do it with the CTE? Currently I have the following
which gives me Duplicate Key Exceptions when two sessions try
are buying
yourself much (if anything) by using a CTE query instead of something
more traditional here.
The advantages of switching to a CTE would be if this code was all
being done inside of the app code with multiple queries.
On Tue, Jan 13, 2015 at 4:39 PM, Robert DiFalco
robert.difa
Good points. I guess my feeling is that if there can be a race condition on
INSERT then the CTE version is not truly atomic, hence the LOOP.
On Tue, Jan 13, 2015 at 3:11 PM, Brian Dunavant br...@omniti.com wrote:
A very good point, but it does not apply as here (and in my article)
we are not
I have a table called friends with a user_id and a friend_id (both of these
relate to an id in a users table).
For each friend relationship there are two rows. There are currently ONLY
reciprocal relationships. So if user ids 1 and 2 are friends there will be
two rows (1,2) and (2,1).
For 2
Johnston
david.g.johns...@gmail.com wrote:
Robert DiFalco wrote
For 2 arbitrary ids, I need a query to get two pieced of data:
* Are the two users friends?
This seems easy...ROW(u_id, f_id) = ROW(n1, n2)
* How many friends do the two users have in common.
SELECT f_id FROM
cost approach than what I was already doing.
On Thu, Dec 18, 2014 at 2:07 PM, David Johnston david.g.johns...@gmail.com
wrote:
On Thu, Dec 18, 2014 at 3:02 PM, Robert DiFalco robert.difa...@gmail.com
wrote:
Is the intersect any better than what I originally showed? On the ROW
approach, I'm
JOINs vs EXISTS queries and
if there was a better alternative I had not considered.
On Tue, Dec 9, 2014 at 11:44 AM, Arthur Silva arthur...@gmail.com wrote:
On Tue, Dec 9, 2014 at 4:18 PM, Robert DiFalco robert.difa...@gmail.com
wrote:
I'm sorry, I missed a JOIN on the second variation
1 - 100 of 1086 matches
Mail list logo
